Python學習日記(三十六) Mysql資料庫篇四

MySQL作業分析五張表的增刪改查: 完成所有表的關係創建創建教師表(tid為這張表教師ID,tname為這張表教師的姓名) 創建班級表(cid為這張表班級ID,caption為這張表班級門號) 創建課程表(cid為這張表課程ID,cname為課程名稱,teacher_id為任課教師的ID) 創 ...

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

Python學習日記(三十六) Mysql資料庫篇 四

Python學習日記(三十六) Mysql資料庫篇四