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号课程的平均成绩;