python36 mysql_Python学习日记(三十六) Mysql数据库篇 四

MySQL作业分析

五张表的增删改查:

完成所有表的关系创建

创建教师表(tid为这张表教师ID,tname为这张表教师的姓名)

create tableteacherTable(

tidint auto_increment primary key,

tnamevarchar(20)

)engine=innodb default charset=utf8;

创建班级表(cid为这张表班级ID,caption为这张表班级门号)

create tableclassTable(

cidint auto_increment primary key,

captionvarchar(20) -)engine=innodb default charset=utf8;

创建课程表(cid为这张表课程ID,cname为课程名称,teacher_id为任课教师的ID)

create tablecourseTable(

cidint auto_increment primary key,

cnamevarchar(30),

teacher_idint,constraint fk_course_teacher foreign key (teacher_id) referencesteacherTable(tid)

)engine=innodb default charset=utf8;

创建学生表(sid为这张表的学生ID,sname为学生姓名,gender为学生性别,class_id为对应的学生班级)

create tablestudentTable(

sidint auto_increment primary key,

snamevarchar(30),

gendervarchar(10) default '男',

class_idint,constraint fk_stu_class foreign key(class_id) referencesclassTable(cid)

)engine=innodb default charset=utf8;

创建成绩表(sid为这张表对应的成绩ID,student_id为这个成绩所对应的学生ID，course_id为这个成绩对应的课程ID,number为成绩)

create tablescoreTable(

sidint auto_increment primary key,

student_idint,

course_idint,number int,constraint fk_score_student foreign key (student_id) referencesstudentTable(sid),constraint fk_score_course foreign key (course_id) referencescourseTable(cid)

)engine=innodb default charset=utf8;

增加表内资料

增加教师表资料

insert into teacherTable(tname) values('叶平'),('孔子'),('杨艳'),('沈梦溪'),('百奇'),('郭德'),('阿尔戈');

增加班级表资料

insert into classTable(caption) values('一年三班'),('一年二班'),('一年五班'),('一年六班');insert into classTable(caption) values('二年一班'),('二年二班'),('二年四班');insert into classTable(caption) values('三年二班'),('三年三班');

增加课程表资料

insert into courseTable(cname,teacher_id) values('数学',1);insert into courseTable(cname,teacher_id) values('语文',2),('哲学',2),('思想品德',2);insert into courseTable(cname,teacher_id) values('化学',3),('毒理学',3);insert into courseTable(cname,teacher_id) values('地理学',4);insert into courseTable(cname,teacher_id) values('英文',5);insert into courseTable(cname,teacher_id) values('相声',6);insert into courseTable(cname,teacher_id) values('心理学',7),('经济学',7);

增加学生表资料

--增加男生数据

insert into studentTable(sname,class_id) values('郭飞',3),('秦桧',6),('岳飞',4),('张廉洁',4),('张成章',7);insert into studentTable(sname,class_id) values('林建儿',8),('章护',6),('冯雪',7),('李萌',9),('李梅',5);

#insert into studentTable(sname,class_id) values('林卡',1),('陈晨',3),('蒋磊',4);--增加女生数据

insert into studentTable(sname,gender,class_id) values('秦雪','女',1),('王小蒙','女',2),('林薇','女',9),('张佳节','女',8),('张雪儿','女',4);insert into studentTable(sname,gender,class_id) values('褚天一','女',2),('张顺乐','女',2),('钟声扬','女',5),('蔡子恒','女',5),('林金仔','女',7);insert into studentTable(sname,gender,class_id) values('高玩','女',5),('倪气焊','女',6)

增加成绩表资料

insert into scoreTable(student_id,course_id,number) values(1,2,68),(1,6,38),(1,7,23),(1,8,95),(1,9,68),(1,10,94),(1,11,56);insert into scoreTable(student_id,course_id,number) values(2,1,99),(2,3,45),(2,8,66),(2,9,78),(2,11,96);insert into scoreTable(student_id,course_id,number) values(3,4,98),(3,5,66),(3,8,96),(3,11,98);insert into scoreTable(student_id,course_id,number) values(4,1,60),(4,5,98),(4,7,100),(4,10,94),(4,11,93);insert into scoreTable(student_id,course_id,number) values(5,1,13),(5,2,86),(5,7,98);insert into scoreTable(student_id,course_id,number) values(6,6,78),(6,8,85);insert into scoreTable(student_id,course_id,number) values(7,7,77),(7,9,84);insert into scoreTable(student_id,course_id,number) values(8,3,35),(8,2,88);insert into scoreTable(student_id,course_id,number) values(9,4,35),(9,6,55),(9,8,66);insert into scoreTable(student_id,course_id,number) values(10,2,45),(10,7,100),(10,8,69),(10,9,94),(10,11,23);insert into scoreTable(student_id,course_id,number) values(11,1,10),(11,6,25);insert into scoreTable(student_id,course_id,number) values(12,2,78),(12,3,99),(12,11,99);insert into scoreTable(student_id,course_id,number) values(13,3,46),(13,8,79),(13,9,64);insert into scoreTable(student_id,course_id,number) values(14,4,55),(14,5,69),(14,6,98),(14,9,100),(14,10,64),(14,11,87);insert into scoreTable(student_id,course_id,number) values(15,6,78),(15,7,87),(15,8,91),(15,11,20);insert into scoreTable(student_id,course_id,number) values(16,1,98),(16,2,87),(16,3,47);insert into scoreTable(student_id,course_id,number) values(17,2,98),(17,3,87);insert into scoreTable(student_id,course_id,number) values(18,4,66),(18,6,78),(18,7,98);insert into scoreTable(student_id,course_id,number) values(19,6,23),(19,8,78),(19,10,100);insert into scoreTable(student_id,course_id,number) values(20,7,91),(20,8,98),(20,9,100),(20,10,87),(20,1,86),(20,4,98);insert into scoreTable(student_id,course_id,number) values(21,1,85),(21,3,84),(21,4,82),(21,6,94);insert into scoreTable(student_id,course_id,number) values(22,5,84),(22,6,47),(22,9,36);insert into scoreTable(student_id,course_id,number) values(23,3,47),(23,9,85);insert into scoreTable(student_id,course_id,number) values(24,4,96),(24,6,97),(24,8,68);insert into scoreTable(student_id,course_id,number) values(25,7,82),(25,8,96),(25,10,100);

1.查找scoretable中大于等于60分的成绩;

select * from scoretable where number >= 60;

2.查找每个老师的任课数;

select count(cname),teacher_id from coursetable group by teacher_id;

3.查找每个课程对应的老师;

select coursetable.cid,coursetable.cname,teachertable.tname from coursetable left join teachertable on coursetable.teacher_id = teachertable.tid;

4.查找每个学生对应的班级;

select studenttable.sid,studenttable.sname,classtable.caption from studenttable left join classtable on studenttable.class_id = classtable.cid;

5.求男生和女生的个数;

select gender as 性别,count(gender) as 人数 from studenttable group by gender;

6.找到平均成绩大于等于70的学生的ID、名字、平均分;

当语句中存在一个聚合函数时要把它改成另外一个别名

select T.student_id,studenttable.sname,T.avg_n from (select student_id,avg(number) as avg_n from scoretable group by student_id having avg(number) >= 70) as

T left join studenttable on T.student_id = studenttable.sid;

7.查询所有同学的学号、姓名、选课数、总成绩;

select scoretable.student_id as 学号,studenttable.sname as 姓名,count(number) as 修课数,sum(number) as 总分 from scoretable left join studenttable on

scoretable.student_id = studenttable.sid group by scoretable.student_id;

8.查询姓杨老师的个数;

select tname as 教师姓名,count(tname) from teachertable group by tname having tname like '杨%';

9.查找没有修杨艳老师的同学姓名和学号;

首先拿到杨艳老师的ID:

select coursetable.cid from coursetable left join teachertable on coursetable.teacher_id=teachertable.tid where teachertable.tname = '杨艳';

最后拿到结果:

select studenttable.sid,studenttable.sname from studenttable where sid not in(select student_id from scoretable where course_id in (select coursetable.cid

from coursetable left join teachertable on coursetable.teacher_id=teachertable.tid where teachertable.tname = '杨艳') group bystudent_id );

10.查询心理学课程比经济学课程分数高的学生ID;

select A.student_id from(select scoretable.sid,scoretable.student_id,coursetable.cname,scoretable.number from scoretable left join coursetable on

scoretable.course_id = coursetable.cid where coursetable.cname = '心理学') asAinner join(select scoretable.sid,scoretable.student_id,coursetable.cname,

scoretable.number from scoretable left join coursetable on scoretable.course_id = coursetable.cid where coursetable.cname = '经济学') asBon

A.student_id =B.student_idwhere A.number > B.number;

11.查询修了课程11和课程9的同学学号和姓名;

select scoretable.student_id,studenttable.sname from scoretable left join studenttable on scoretable.student_id=studenttable.sid where course_id = 9 or

course_id = 11 group by student_id having count(course_id)>1;

12.查询所有学过阿尔戈老师所有所教的课的同学的学号和姓名;

select T.student_id,studenttable.sname from (select scoretable.student_id from scoretable where scoretable.course_id in (select coursetable.cid from

coursetable left join teachertable on coursetable.teacher_id = teachertable.tid where teachertable.tname = '阿尔戈') group by student_id having

count(course_id) = (select count(coursetable.cid) from coursetable left join teachertable on coursetable.teacher_id = teachertable.tid where

teachertable.tname = '阿尔戈')) as T left join studenttable on T.student_id = studenttable.sid;

13.查询课程编号11的成绩比课程编号8的成绩低的同学的学号、姓名;

select C.student_id,studenttable.sname from (select A.student_id from(select scoretable.student_id,scoretable.number from scoretable left join coursetable

on scoretable.course_id = coursetable.cid where coursetable.cid = 11) asAinner join(select scoretable.student_id,scoretable.number from scoretable

left join coursetable on scoretable.course_id = coursetable.cid where coursetable.cid = 10) asBon A.student_id = B.student_id where A.number < B.number) as

C left join studenttable on C.student_id=studenttable.sid;

14.查询有课程成绩小于60的同学的学号和姓名;

方法一:

select T.student_id as ID,studenttable.sname as 名字 from (select student_id from scoretable where number < 60 group by student_id)as T left join studenttable

on T.student_id = studenttable.sid;

方法二:

select sid,sname from studenttable where sid in (select distinct student_id from scoretable where number < 60);

15.查询没有学全所有课程的同学学号、姓名;

select studenttable.sid,studenttable.sname from studenttable where sid in (select student_id from scoretable group by student_id having count(1) <

(select count(1) from coursetable));

16.查询至少有一门课与学号5的同学相同的同学学号和姓名;

select T.student_id,studenttable.sname from (select student_id from scoretable where student_id != 5 and course_id in (select course_id from scoretable where

student_id = 5) group by student_id) as T left join studenttable on T.student_id = studenttable.sid;

17.查询和8号同学学习的课完全相同的同学学号和姓名;

select T.student_id,studenttable.sname from(select student_id from scoretable where student_id in (select student_id from scoretable where student_id != 8

group by student_id having count(1) = (select count(1) from scoretable where student_id = 8)) and course_id in (select course_id from scoretable where

student_id = 8) group by student_id having count(1) = (select count(1) from scoretable where student_id = 8)) as T left join studenttable on

T.student_id=studenttable.sid;

18.查询至少学过7号同学的所有课程的同学的学号和姓名;

也就是说找到的同学学的课和他一样或者比他多

select T.student_id,studenttable.sname from (select student_id,count(1) from scoretable where student_id != 7 and course_id in (select course_id from

scoretable where student_id = 7) group by student_id having count(1) = (select count(1) from scoretable where student_id = 7))as T left join studenttable

on T.student_id=studenttable.sid;

19.删除学习'孔子'老师课的scoretable的记录;

delete from scoretable where course_id in (select cid from coursetable left join teachertable on coursetable.teacher_id = teachertable.tid where

teachertable.tname = '孔子');

20.向score表中插入一些记录,这些记录符合以下条件:没有上过编号2课程的同学学号,插入2号课程的平均成绩;

insert into scoretable (student_id,course_id,number) select sid,2,(select avg(number) from scoretable where course_id = 2) from studenttable where sid not in(select student_id from scoretable where course_id = 2);

21.按平均成绩从低到高显示所有学生的'毒理学''经济学''心理学'三门的课程成绩,按如下形式显示:学生ID、毒理学、经济学、心理学、有效课程数、有效平均分;

selectsc.student_id,

(select number from scoretable left join coursetable on scoretable.course_id = coursetable.cid where coursetable.cname = '毒理学' and scoretable.student_id

= sc.student_id) as '毒理学成绩',

(select number from scoretable left join coursetable on scoretable.course_id = coursetable.cid where coursetable.cname = '经济学' and scoretable.student_id

= sc.student_id) as '经济学成绩',

(select number from scoretable left join coursetable on scoretable.course_id = coursetable.cid where coursetable.cname = '心理学' and scoretable.student_id

= sc.student_id) as '心理学成绩',count(sc.course_id) as '有效课程数',avg(sc.number) as '有效平均分'

from scoretable as sc group by student_id desc;

22.查询各科的最高和最低的分,显示方式:课程ID,最高分,最低分;

select course_id,max(number) as '最高分',min(number) as '最低分' from scoretable group by course_id;

23.按各科平均成绩从低到高和及格率的百分数从高到低顺序排序;

select course_id,avg(number) as '平均分',sum(case when scoretable.number > 60 then 1 else 0 END)/count(1)*100 as '及格率' from scoretable group by course_id;

24.课程平均分从高到低显示;

select scoretable.course_id as '课程ID',coursetable.cname as '课程',avg(if(isnull(scoretable.number),0,scoretable.number)) as '平均分',teachertable.tname as

'教师姓名' fromscoretableleft join coursetable on scoretable.course_id =coursetable.cidleft join teachertable on teachertable.tid =coursetable.teacher_idgroup by scoretable.course_id order by avg(number) desc;

25.查询每门课程成绩最好的前两名;

select scoretable.sid,scoretable.course_id,scoretable.number,T.first_number,T.second_number from scoretable left join(selectsid,

(select number from scoretable as s2 where s2.course_id = s1.course_id order by number desc limit 0,1) asfirst_number,

(select number from scoretable as s2 where s2.course_id = s1.course_id order by number desc limit 1,1) assecond_numberfromscoretableass1

)asTon scoretable.sid =T.sidwhere scoretable.number <= T.first_number and scoretable.number >= T.second_number;

26.查询每门课程被选修的学生数;

select course_id,count(course_id) as '选修人数' from scoretable group by course_id;

27.查询被9个以上的同学选的热门课程;

select coursetable.cid as '课程ID',coursetable.cname as '热门课程名称' from coursetable where cid in (select course_id from scoretable group by course_id having

count(course_id) >= 9);

28.查询只选了两门课程的学生;

select studenttable.sid as '学生ID',studenttable.sname as '学生姓名' from studenttable where studenttable.sid in (select student_id from scoretable group by

student_id having count(student_id) = 2);

29.查询姓张学生的名单;

select studenttable.sname from studenttable where studenttable.sname like '张%';

30.查询同名同姓学生名单,并统计同名人数;

select sname,count(1) from studenttable group by sname;

31.查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;

select course_id as '课程ID',avg(if(isnull(number),0,number)) as '课程平均分' from scoretable group by course_id order by '课程平均分' asc,'课程ID' desc;

32.查询平均成绩大于85的所有学生的学号、姓名和平均成绩;

SELECTT.student_idAS '学生ID',

studenttable.snameAS '学生姓名',

T.avg_scoreAS '平均分'

FROM(SELECTstudent_id,avg( IF ( isnull( number ), 0, number ) ) ASavg_scoreFROMscoretableGROUP BYstudent_idHAVINGavg_score> 85)ASTLEFT JOIN studenttable ON T.student_id = studenttable.sid;

33.查询课程名称为'数学',且分数低于60的学生姓名和分数;

SELECTstudenttable.sname,

scoretable.number

FROMscoretableLEFT JOIN studenttable ON scoretable.student_id =studenttable.sidLEFT JOIN coursetable ON scoretable.course_id =coursetable.cidWHEREcoursetable.cname= '数学'

AND scoretable.number < 60;

34.查询课程编号为11的课程成绩在80分以上的学生和姓名;

SELECTstudenttable.sidAS学生 ID,

studenttable.snameAS学生姓名FROMscoretableLEFT JOIN coursetable ON scoretable.course_id =coursetable.cidLEFT JOIN studenttable ON scoretable.student_id =studenttable.sidWHEREcoursetable.cid= 11

AND scoretable.number > 80;

35.求选了课程的学生数;

SELECTstudent_idFROMscoretableGROUP BYstudent_id;

36.查询选修'杨艳'老师所授课程的学生中,成绩最高的学生姓名及他的成绩;

SELECTstudenttable.sname,

scoretable.number

FROMscoretableLEFT JOIN studenttable ON scoretable.student_id =studenttable.sidWHEREscoretable.course_idIN ( SELECT cid FROM coursetable LEFT JOIN teachertable ON coursetable.teacher_id = teachertable.tid WHERE teachertable.tname='杨艳')ORDER BY

number DESCLIMIT1;

37.查询各个课程及相应的选修人数;

SELECTcoursetable.cnameAS '课名',count( 1 ) AS '人数'

FROMscoretableLEFT JOIN coursetable ON scoretable.course_id =coursetable.cidGROUP BYcourse_id;

38.查询不同课程但成绩相同的学生的学号、课程号、学生成绩;

SELECTs1.student_idAS '学号',

s1.course_idAS '课程号',

s1.number AS '学生成绩'

FROMscoretableASs1,

scoretableASs2WHEREs1.course_id!=s2.course_idAND s1.sid !=s2.sidAND s1.number = s2.number;

39.查询至少选修了4门课程的学生;

SELECTstudent_idAS '学号'

FROMscoretableLEFT JOIN coursetable ON scoretable.course_id =coursetable.cidGROUP BYstudent_idHAVING

count( 1 ) >= 4;

40.查询全部学生都选修的课程的课程号和课程名(就是找所有学生的必修课);

SELECTcourse_id,count( 1)FROMscoretableGROUP BYcourse_idHAVING

count( 1 ) = ( SELECT count( 1 ) FROM studenttable );

41.查询没学过'百奇'老师讲授的任意一门课程的学生姓名;

SELECTscoretable.student_idFROMscoretableGROUP BYscoretable.student_idHAVINGscoretable.student_idNOT IN(SELECTstudent_idFROMscoretableLEFT JOIN studenttable ON scoretable.student_id =studenttable.sidWHEREscoretable.course_idIN ( SELECT coursetable.cid FROM coursetable LEFT JOIN teachertable ON coursetable.teacher_id = teachertable.tid WHERE

teachertable.tname = '百奇')

);

42.查询两门以上不及格课程的同学的学号及其平均成绩;

SELECTstudent_idAS '学生ID',avg( number ) AS '平均分'

FROMscoretableWHERE

number < 60 GROUP BY student_id HAVING count( 1 ) >= 2;

43.查询课程编号8的课程小于60分的同学,将显示结果按分数降序进行排列的同学学号;

SELECTscoretable.student_idFROMscoretableWHEREscoretable.course_id= 4

AND scoretable.number < 60

ORDER BY

number DESC;

44.删除学号2的同学的课程ID为1的课程成绩;

DROP

FROMscoretableWHEREscoretable.student_id= 2

AND scoretable.course_id = 1;