MySQL总结练习

下面是一个学生与课程的数据库，三个关系表为：

学生表S（Sid，SNAME,AGE,SEX）

成绩表SC（Sid，Cid，GRADE）

课程表C（Cid，CNAME，TEACHER）

其中Sid为学号，SNAME为学生名字，AGE为年龄，SEX为性别，

Cid为课程号，GRADE为成绩，CNAME为课程名字，TEACHER为教师姓名。

SQL脚本如下：

学生表#create table s(sid int(5),sname char(20),age int(5),sex int(5));

#成绩表#create table sc(sid int(1),cid char(20),grade int(5);

#课程表#create table c(cid char(5),cname char(20),teacher char(20));

insert into s(sid,sname,age,sex )values(1111,'david',28,1);

insert into s(sid,sname,age,sex )values(1112,'dany',23,0);

insert into s(sid,sname,age,sex)values(1113,'tom',38,1);

insert into s(sid,sname,age,sex)values(1114,'lily',20,0);

insert into sc(sid,cid,grade)values(1113,'c1','38');

insert into sc(sid,cid,grade)values(1112,'c2','80');

insert into sc(sid,cid,grade)values(1112,'c3','67');

insert into sc(sid,cid,grade)values(1111,'c4','88');

insert into sc(sid,cid,grade)values(1114,'c4','66');

insert into c(cid,cname,teacher)values('c4','maths','zhangsan');

insert into c(cid,cname,teacher)values('c3','english','lisi');

insert into c(cid,cname,teacher)values('c2','chinese','wangwu');

insert into c(cid,cname,teacher)values('c1','computer','zhaoliu');

请用SQL完成以下的操作：

[size=10.5000pt]1. 查询出学习成绩及格以上的学生姓名与成绩；

select s.sname,sc.grade from s,sc where sc.sid=s.sid and sc.grade>60;

2. 查询姓名以ny结尾的学生姓名及其任课老师姓名；

select s.sname,c.teacher from s,c,sc where s.sid=sc.sid and c.cid=sc.cid and s.sname like "%ny";

3. 选修课名为Maths的学生学号与姓名;

select s.sid,s.sname from s,c,sc where s.sid=sc.sid and c.cid=sc.cid and c.cname='maths';

4. 选修课号为C2和C4的学生学号；

select sid from sc where cid='c2' or cid='c4';

5. 请问没有参加考试的学生的姓名及其学号；

select sname,sid from s where sid not in(select sid from sc);

6. 请问总分在80分以上的学生姓名；

select s.sname from s,sc where s.sid=sc.sid group by s.sname having sum(sc.grade)>80;

7. 请问考试不及格的考生姓名以及科目名称；

select s.sname,c.cname from s,c,sc where s.sid=sc.sid and sc.cid=c.cid and sc.grade<60;

8. 找出Dany的所有考试后成绩中最高的科目的任课老师姓名；

select c.teacher from sc,c,s where sc.cid=c.cid and sc.sid=s.sid and sc.grade=(select max(grade) from s,sc where s.sid=sc.sid and s.sname='dany')and s.sname='dany';

9. 各个科目的平均成绩各是多少；

select c.cname,avg(grade) from sc,c where sc.cid=c.cid group by c.cname;

10. 由于录入员的失误，导致TOM同学有成绩不及格的科目，请帮忙将其合理修正；

update s LEFT JOIN sc on sc.sid=s.sid set sc.grade=60 where s.sname='tom' and sc.grade<60;

11.显示出参加考试的学生的学号和姓名；

select sid,sname from s where sid in(select sid from sc);

12.请问TOM一共参加了几门科目的考试；

select count(*) from s,c,sc where s.sid=sc.sid and c.cid=sc.cid and s.sname='tom';

[size=10.5000pt]13. 期末，DBA发现学生表中没有班级字段，请帮忙将其合理修正；

alter table s add class varchar(10);

14. 学校新招了1位美术老师叫“白雪”，请在课程表中增加一条相应的记录，课程编号为C5；

insert into c values('c5','meisu','baixue');

15. 请求出TOM同学所有任课老师的名字及其所教的课程名；

select c.teacher,c.cname from s,c,sc where s.sid=sc.sid and c.cid=sc.cid and s.sname='tom';

16. 赵姓的老师很早就离职了，然而课程表中却还有其上课的记录，请帮忙删除；

delete from c where teacher like "zhao%";

17. 求每个同学的各科成绩，并以每一个人的成绩的先大后小的顺序显示；

select s.sname,c.cname,sc.grade from s,c,sc where s.sid=sc.sid and c.cid=sc.cid  order by sc.grade desc;

18. 请给学生学号与姓名创建一个普通索引：

create index dcs18 on s(sid,sname);

19.请问张老师教什么课程，以及他班上所有学生的姓名；

select c.cname,s.sname from s,c,sc where s.sid=sc.sid and c.cid=sc.cid and c.teacher like "zhang%";

[size=10.5000pt]20. 请给学生的学号、姓名、成绩创建一个视图，并简述视图与表的关系：

create view dcs18 as(select s.sid,s.sname,sc.grade from s,sc where s.sid=sc.sid);

21. 由于目前学校数据表中女生的数学成绩不足20条，校长希望作为DBA的你能帮忙添加且要求添加的分数在原来女生数学成绩最高分数的基础上逐次加1，同时显示出女生的数学的总分；

drop PROCEDURE if EXISTS dcs;

CREATE PROCEDURE dcs(n int)

BEGIN

#i女生数学人数

DECLARE i int(5)default(select count(*) from s,c,sc where s.sid=sc.sid and c.cid=sc.cid and s.sex=0 and c.cname='maths');

#mx 数学最高分

DECLARE mx int(5)default(select max(grade) from s,c,sc where s.sid=sc.sid and c.cid=sc.cid and s.sex=0 and c.cname='maths');

#最大学号

DECLARE bh int(5)default(select max(sid) from s);

while i<n DO

set i=i+1;

set bh=bh+1;

set mx=mx+1;

insert into sc(sid,cid,grade)values(bh,'c4',mx);

insert into s(sid,sname,age,sex)values(bh,(concat('user',i)),18,0);

end while;

select sum(grade) from sc,s,c where sc.sid=s.sid and c.cid=sc.cid and s.sex=0 and c.cname='maths';

END

call dcs(20)