数据库MySQL练习

–1.主键约束，外键约束

–姓名不重名，年龄不为空，性别缺省值为‘男’，成绩在0~100之间

CREATE TABLE T

(TNO INT(3)PRIMARY KEY,

TN VARCHAR(8),

TD VARCHAR(10)

);

CREATE TABLE C

(CNO INT(2)PRIMARY KEY,

CN VARCHAR(20),

TNO INT(3),

FOREIGN KEY T_Fore(TNO) REFERENCES T(TNO)

);

CREATE TABLE SC

(SNO INT(4) ,

CNO INT(2),

GRADE INT(3)CHECK(Grade>=0 and Grade<=100),

FOREIGN KEY S_Fore(SNO)REFERENCES S(SNO)

);

CREATE TABLE S

(SNO INT(4)PRIMARY KEY,

SN VARCHAR(8)UNIQUE,

SAGE INT(2)NOT NULL,

SEX VARCHAR(2) DEFAULT’男’

);

–2.插入数据

INSERT INTO T(TNO,TN,TD)

VALUES (‘203′,’刘备’,’数学’),

(‘301′,’曹操’,’物理’),

(‘411′,’孙权’,’外语’),

(‘504′,’董卓’,’计算机’),

(‘507′,’司马懿’,’计算机’);

INSERT INTO C(CNO,CN,TNO)

VALUES (’21’,’高等数学’,’203′),

(’31’,’普通物理’,’301′),

(’41’,’英语’,’411′),

(’51’,’微机原理’,’504′),

(’52’,’软件基础’,’507′);

INSERT INTO S(SNO,SN,SAGE,SEX)

VALUES (‘9031′,’张飞’,’21’,’男’),

(‘9032′,’关羽’,’23’,’男’),

(‘9033′,’葛优’,’23’,’男’),

(‘9034′,’貂蝉’,’18’,’女’),

(‘9035′,’小乔’,’16’,’女’),

(‘9036′,’诸葛亮’,’22’,’男’),

(‘9037′,’周瑜’,’21’,’男’);

INSERT INTO SC(SNO,CNO,GRADE)

VALUES (‘9031′,’21’,’95’),

(‘9031′,’41’,’90’),

(‘9032′,’21’,’83’),

(‘9032′,’41’,’76’),

(‘9032′,’52’,’73’),

(‘9033′,’21’,’82’),

(‘9033′,’31’,’91’);

–3.查询选修了计算机教师所授课程的学生姓名和成绩

select sn,grade

from sc, s

where ((sc.sno=s.sno)and cno in

(select cno

 from c

 where tno in(select tno from t where(td=’计算机’))

));

–4.查询未选修“高等数学”的学生的学号和姓名

select sno,sn

from s

where(sno in

(select sno

 from sc

 where cno in(select cn from c where(cn<>’高等数学’))

));

–5.查询不是计算机老师讲授课程的cn和cno

select cn,cno

from c

where(tno in

(select tno from t where(td<>’计算机’))

);

–6查询选修了“高等数学”或“普通物理”且名字中带有葛字的学生姓名,in的用法

select sn

from s

where(sn like’葛%’and sno in

(select sno

 from sc

 where(cno in (select cno from c where(cn=’普通物理’or cn=’高等数学’)))));

 –7查询至少选修课程为21和41两门课程的学生学号

 –可以用两个exists替代

 select sno

 from sc

 where sno in (select distinct sno from sc where cno = ’41’) and cno=’21’

 group by sno

 having(count(*)>=2)

 order by sno;

 –8查询每门课程的学生选修人数（只输出超过十人的课程）

 –输出课程号和选修人数，查询结果按降序排列，若人数相同，按课程号排列

 –不用distinct也可以

 select count(*),cno

 from sc

 where cno in(select cno from sc group by cno having(count(*)>=10)  )

 order by count(*),cno desc;

 

 –9在t表tname上创建唯一索引

 create unique index tname on t(tn)

 –10查询至少讲授两门课程的教师姓名和所在系

 select tn,td

 from t

 where tno in(select tno from c group by tno having(count(*)>=2) );

 –11将每一门课成绩均大于80的学生学号、姓名、性别插入到已存在的STU(sno,sn,sex)

 create table stu

 select sno,sn,sex

 from s

 where exists

 (select *

 from sc where sc.sno=s.sno and not exists(select * from sc where s.sno=sc.sno and grade<80))

 –12将低于平均成绩的女同学的成绩提高5%

update sc

set grade=grade*1.05

where sno in (select sno from s where sex=”女”)

and grade<(select avg_grade  from(select avg(grade) as avg_grade from sc)as avg_G);

 –13创建视图v_fail(sn,cn,grade),反应成绩不及格的学生

 create view v_fail(sn,cn,grade)

 as select sn,cn,grade from s,c,sc where ((sc.sno=s.sno)and(c.cno=sc.cno)and grade<=60);

 

最后两条不知道为什么会报错，按照老师代码写的

–14创建用户wang对数据库tea的s表查询、更新的权限，并且允许wang把权限授予其他用户

 create  user  ‘wang’@’%’   identified   by   ‘123’;

 grant select,update on tea.s to’wang’@’%’

 FLUSH PRIVILEGES

 grant select on tea.s to ‘wang’@’%’ with grant option

 FLUSH PRIVILEGES;

 –15撤销wang的所有权限

 revoke all on*.* from ‘wang’@’%’

 FLUSH PRIVILEGES

文章知识点与官方知识档案匹配，可进一步学习相关知识MySQL入门技能树数据库组成表31756 人正在系统学习中

来源：小爱同学ya