在线客服 联系我们 返回顶部
企业动态 技术分享 行业动态

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

2019-10-08

五张表的增删改查:

完成所有表的关系创建

创建教师表

create table teachertable
)engine=innodb default charset=utf8;

创建班级表

create table classtable -
)engine=innodb default charset=utf8;

创建课程表

create table coursetable,
 teacher_环亚ag8818id int,
 constraint fk_course_teacher foreign key  references teachertable
)engine=innodb default charset=utf8;

创建学生表

create table studenttable,
 gender varchar default '男',
 class_id int,
 constraint fk_stu_class foreign key references classtable
)engine=innodb default charset=utf8;

创建成绩表

create table scoretable references studenttable,
 constraint fk_score_course foreign key  references coursetable
)engine=innodb default charset=utf8;

增加表内资料

增加教师表资料

insert into teachertable values,,,,,,;

增加班级表资料

insert into classtable values,,,;
insert into classtable values,,;
insert into classtable values,;

增加课程表资料

insert into coursetable values;
insert into coursetable values,,;
insert into coursetable values,;
insert into coursetable values;
insert into coursetable values;
insert into coursetable values;
insert into coursetable values,;

增加学生表资料

-- 增加男生数据
insert into studenttable values,,,,;
insert into studenttable values,,,,;
#insert into studenttable values,,;
-- 增加女生数据
insert into studenttable values,,,,;
insert into studenttable values,,,,;
insert into studenttable values,

增加成绩表资料

insert into scoretable values,,,,,,;
insert into scoretable values,,,,;
insert into scoretable values,,,;
insert into scoretable values,,,,;
insert into scoretable values,,;
insert into scoretable values,;
insert into scoretable values,;
insert into scoretable values,;
insert into scoretable values,,;
insert into scoretable values,,,,;
insert into scoretable values,;
insert into scoretable values,,;
insert into scoretable values,,;
insert into scoretable values,,,,,;
insert into scoretable values,,,;
insert into scoretable values,,;
insert into scoretable values,;
insert into scoretable values,,;
insert into scoretable values,,;
insert into scoretable values,,,,,;
insert into scoretable values,,,;
insert into scoretable values,,;
insert into scoretable values,;
insert into scoretable values,,;
insert into scoretable values,,;

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

select * from scoretable where number = 60;

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

select count,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 as 人数 from studenttable group by gender; 

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

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

select t.student_id,studenttable.sname,t.avg_n from  as avg_n from scoretable group by student_id having avg = 70) as 
t left join studenttable on t.student_id = studenttable.sid;

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

select scoretable.student_id as 学号,studenttable.sname as 姓名,count as 修课数,sum as 总分 from scoretable left join studenttable on 
scoretable.student_id = studenttable.sid group by scoretable.student_id;

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

select tname as 教师姓名,count 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 group by student_id );

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

select a.student_id from  as a inner join  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

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

select t.student_id,studenttable.sname from  group by student_id having 
count = 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  as a inner join  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 as t left join studenttable 
on t.student_id = studenttable.sid;

方法二:

select sid,sname from studenttable where sid in ;

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

select studenttable.sid,studenttable.sname from studenttable where sid in  
from coursetable));

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

select t.student_id,studenttable.sname from  group by student_id) as t left join studenttable on t.student_id = studenttable.sid;

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

select t.student_id,studenttable.sname from  =  from scoretable where student_id = 8)) and course_id in  group by student_id having count =  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  from scoretable where student_id != 7 and course_id in  group by student_id having count =  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 ;

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

 

在线咨询
Copyright © 2018 环亚ag88环亚ag88-环亚ag8818-环亚ag8866官网 All Rights Reserved