数据库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