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;