Python学习日记(三十六) Mysql数据库篇 四
MySQL作业分析
五张表的增删改查:
完成所有表的关系创建
创建教师表(tid为这张表教师ID,tname为这张表教师的姓名)
create table teacherTable( tid int auto_increment primary key, tname varchar(20) )engine=innodb default charset=utf8;
创建班级表(cid为这张表班级ID,caption为这张表班级门号)
create table classTable( cid int auto_increment primary key, caption varchar(20) - )engine=innodb default charset=utf8;
创建课程表(cid为这张表课程ID,cname为课程名称,teacher_id为任课教师的ID)
create table courseTable( cid int auto_increment primary key, cname varchar(30), teacher_id int, constraint fk_course_teacher foreign key (teacher_id) references teacherTable(tid) )engine=innodb default charset=utf8;
创建学生表(sid为这张表的学生ID,sname为学生姓名,gender为学生性别,class_id为对应的学生班级)
create table studentTable( sid int auto_increment primary key, sname varchar(30), gender varchar(10) default '男', class_id int, constraint fk_stu_class foreign key(class_id) references classTable(cid) )engine=innodb default charset=utf8;
创建成绩表(sid为这张表对应的成绩ID,student_id为这个成绩所对应的学生ID，course_id为这个成绩对应的课程ID,number为成绩)
create table scoreTable( sid int auto_increment primary key, student_id int, course_id int, number int, constraint fk_score_student foreign key (student_id) references studentTable(sid), constraint fk_score_course foreign key (course_id) references courseTable(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 by student_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 = '心理学') as A inner 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 = '经济学') as B on
A.student_id = B.student_id where 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) as A inner join (select scoretable.student_id,scoretable.number from scoretable
left join coursetable on scoretable.course_id = coursetable.cid where coursetable.cid = 10) as B on 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、毒理学、经济学、心理学、有效课程数、有效平均分;
select sc.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
'教师姓名' from scoretable left join coursetable on scoretable.course_id = coursetable.cid left join teachertable on teachertable.tid = coursetable.teacher_id group 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 ( select sid, (select number from scoretable as s2 where s2.course_id = s1.course_id order by number desc limit 0,1) as first_number, (select number from scoretable as s2 where s2.course_id = s1.course_id order by number desc limit 1,1) as second_number from scoretable as s1 ) as T on scoretable.sid = T.sid where 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的所有学生的学号、姓名和平均成绩;
SELECT T.student_id AS '学生ID', studenttable.sname AS '学生姓名', T.avg_score AS '平均分' FROM ( SELECT student_id, avg( IF ( isnull( number ), 0, number ) ) AS avg_score FROM scoretable GROUP BY student_id HAVING avg_score > 85 ) AS T LEFT JOIN studenttable ON T.student_id = studenttable.sid;
33.查询课程名称为'数学',且分数低于60的学生姓名和分数;
SELECT studenttable.sname, scoretable.number FROM scoretable LEFT JOIN studenttable ON scoretable.student_id = studenttable.sid LEFT JOIN coursetable ON scoretable.course_id = coursetable.cid WHERE coursetable.cname = '数学' AND scoretable.number < 60;
34.查询课程编号为11的课程成绩在80分以上的学生和姓名;
SELECT studenttable.sid AS 学生 ID, studenttable.sname AS 学生姓名 FROM scoretable LEFT JOIN coursetable ON scoretable.course_id = coursetable.cid LEFT JOIN studenttable ON scoretable.student_id = studenttable.sid WHERE coursetable.cid = 11 AND scoretable.number > 80;
35.求选了课程的学生数;
SELECT student_id FROM scoretable GROUP BY student_id;
36.查询选修'杨艳'老师所授课程的学生中,成绩最高的学生姓名及他的成绩;
SELECT studenttable.sname, scoretable.number FROM scoretable LEFT JOIN studenttable ON scoretable.student_id = studenttable.sid WHERE scoretable.course_id IN ( SELECT cid FROM coursetable LEFT JOIN teachertable ON coursetable.teacher_id = teachertable.tid WHERE teachertable.tname='杨艳') ORDER BY number DESC LIMIT 1;
37.查询各个课程及相应的选修人数;
SELECT coursetable.cname AS '课名', count( 1 ) AS '人数' FROM scoretable LEFT JOIN coursetable ON scoretable.course_id = coursetable.cid GROUP BY course_id;
38.查询不同课程但成绩相同的学生的学号、课程号、学生成绩;
SELECT s1.student_id AS '学号', s1.course_id AS '课程号', s1.number AS '学生成绩' FROM scoretable AS s1, scoretable AS s2 WHERE s1.course_id != s2.course_id AND s1.sid != s2.sid AND s1.number = s2.number;
39.查询至少选修了4门课程的学生;
SELECT student_id AS '学号' FROM scoretable LEFT JOIN coursetable ON scoretable.course_id = coursetable.cid GROUP BY student_id HAVING count( 1 ) >= 4;
40.查询全部学生都选修的课程的课程号和课程名(就是找所有学生的必修课);
SELECT course_id, count( 1 ) FROM scoretable GROUP BY course_id HAVING count( 1 ) = ( SELECT count( 1 ) FROM studenttable );
41.查询没学过'百奇'老师讲授的任意一门课程的学生姓名;
SELECT scoretable.student_id FROM scoretable GROUP BY scoretable.student_id HAVING scoretable.student_id NOT IN ( SELECT student_id FROM scoretable LEFT JOIN studenttable ON scoretable.student_id = studenttable.sid WHERE scoretable.course_id IN ( SELECT coursetable.cid FROM coursetable LEFT JOIN teachertable ON coursetable.teacher_id = teachertable.tid WHERE
teachertable.tname = '百奇' ) );
42.查询两门以上不及格课程的同学的学号及其平均成绩;
SELECT student_id AS '学生ID', avg( number ) AS '平均分' FROM scoretable WHERE number < 60 GROUP BY student_id HAVING count( 1 ) >= 2;
43.查询课程编号8的课程小于60分的同学,将显示结果按分数降序进行排列的同学学号;
SELECT scoretable.student_id FROM scoretable WHERE scoretable.course_id = 4 AND scoretable.number < 60 ORDER BY number DESC;
44.删除学号2的同学的课程ID为1的课程成绩;
DROP FROM scoretable WHERE scoretable.student_id = 2 AND scoretable.course_id = 1;
