#1.自行创建测试数据
create database homework; use homework;
# 年级表->老师表->课程表->班级表->学生表->成绩表->班级任职表
#------------------------------------------------------#
# 年级表
create table class_grade ( gid int PRIMARY KEY auto_increment, gname varchar(10) not null );
insert into class_grade values (1,'一年级'), (2,'二年级'), (3,'三年级');
#------------------------------------------------------#
create table teacher ( tid int PRIMARY KEY auto_increment, tname varchar(10) not null );
insert into teacher values (1,'张三'), (2,'李四'), (3,'王五');
#------------------------------------------------------#
create table course( cid int primary key auto_increment, cname varchar(10) not null unique , teacher_id int not null , foreign key (teacher_id) references teacher(tid) on delete cascade on update cascade );
insert into course values (1,'生物',1), (2,'体育',1), (3,'物理',2);
#------------------------------------------------------#
create table class ( cid int primary key auto_increment, caption varchar(10) not null unique , grade_id int not null, FOREIGN KEY (grade_id) REFERENCES class_grade(gid) on delete cascade on update cascade );
insert into class values (1,'一年一班',1), (2,'二年一班',2), (3,'三年二班',3);
#------------------------------------------------------#
create table student( sid int primary key auto_increment, sname varchar (10) not null , gender enum('男', '女') not null default '男', class_id int not null , FOREIGN KEY (class_id) REFERENCES class(cid) on delete cascade on update cascade );
insert into student values (1,'乔丹','女',1), (2,'艾弗森','女',1), (3,'科比','男',2);
#------------------------------------------------------#
create table score( sid int primary key auto_increment, student_id int not null, course_id int not null, score int not null , FOREIGN KEY (student_id) REFERENCES student(sid) on delete cascade on update cascade , FOREIGN KEY (course_id) REFERENCES course(cid) on delete cascade on update cascade );
insert into score values (1,1,1,60), (2,1,2,59), (3,2,2,99);
#------------------------------------------------------#
create table teacher2cls( tcid int primary key auto_increment, tid int not null , cid int not null, FOREIGN key (tid) REFERENCES teacher(tid) on delete cascade on update cascade , FOREIGN key (cid) REFERENCES class(cid) on delete cascade on update cascade );
insert into teacher2cls values (1,1,1), (2,1,2), (3,2,1), (4,3,2);
#########################################################
select count(sid) as '学生总人数' from student;
#########################################################
select sname,sid from student where sid in (select student_id from score inner join course on course_id = cid where course.cname in ('生物','物理') and score.score >= 60 group by score.student_id having count(course_id) = 2 );
#########################################################
select gname from class_grade inner join (select grade_id,count(cid) as '班级数' from class group by grade_id order by '班级数' limit 3) as t1 on class_grade.gid = t1.grade_id;
#########################################################
create view tmp as ( select * from student inner join (select avg(score) as avg_score ,student_id from score group by student_id) as t1 on student.sid = t1.student_id ); select sid,sname,avg_score from tmp where tmp.avg_score =(select avg_score from tmp order by avg_score desc limit 1) or tmp.avg_score =(select avg_score from tmp order by avg_score limit 1);
#########################################################
select gname,count(sid) as student_num from class inner join class_grade on class.grade_id = class_grade.gid inner join (select sid,cid,student.sname from class inner join student on class.cid = student.class_id) as t1 on t1.cid = class.cid group by sid;
#########################################################
select sid,student.sname,choose_num,avg_score from student inner join (select student.sname,count(student_id) as choose_num,avg(score) as avg_score from score inner join student on student.sid = score.student_id group by student_id) as t1 on t1.sname = student.sname;
#########################################################
select sname,cname,score from (select sid,sname,cname,score from student inner join (select student_id,cname,score from score inner join course on course_id = cid) as t1 on sid = t1.student_id where sid = 2 and score in ((select max(score) from score),(select min(score)from(score))))as t2;
#########################################################
create view tmp1 as (select tname,caption from class inner join (select tname,cid from teacher2cls inner join (select tid,tname,count(tname)as count_num from teacher where tname like '李%' group by tid) as t1 on t1.tid = teacher2cls.tid)as t2 on t2.cid = class.cid); select count(tname)as '姓李个数',count(caption)as '带班级个数' from tmp1 ;
#########################################################
select gid,gname from class_grade inner join class on class.grade_id = class_grade.gid group by grade_id having count(caption)<5;
#########################################################
select class.cid as '班级id', class.caption as '班级名称', class_grade.gname as '年级', case when class_grade.gid in (1,2) then '低' when class_grade.gid in (3,4) then '中' when class_grade.gid in (5,6) then '高'else 0 end as '年级级别' from class left join class_grade on class.grade_id = class_grade.gid;
#########################################################
select sid,sname from student where sid in ( select score.student_id from score left join course on score.course_id = course.cid where course.teacher_id in ( select tid from teacher where tname = '张三' ) group by student_id having count(course.cid) >2 );
#########################################################
select tid,tname from teacher inner join course on teacher.tid = course.teacher_id group by teacher_id having count(cid)>2;
#########################################################
select sid,sname from student where sid in (select student_id from score where course_id in (1,2) group by student_id having count(student_id) >=2);
#########################################################
select tid,tname from teacher where tid not in (select tid from teacher2cls left join class on teacher2cls.cid = class.cid where grade_id in (5,6) );
#########################################################
SELECT DISTINCT sid,sname from student inner JOIN (select student_id from score LEFT JOIN (select tid,cid from course LEFT JOIN teacher on tid = teacher_id where tname = '张三') as t on cid = course_id) as t1 on sid = t1.student_id;
#########################################################
select t.tid,tname from teacher t INNER JOIN teacher2cls t_c on t_c.tid = t.tid GROUP BY t_c.tid HAVING count(cid)>2;
#########################################################
SELECT sid,sname from student where sid in (select t1.student_id from (SELECT student_id,score from score where course_id = 2) as t1, (SELECT student_id,score from score where course_id = 1) as t2 where t1.student_id = t2.student_id and t1.score<t2.score);
#########################################################
select t.tid,tname from teacher t INNER JOIN (select tid,max(count_class) as max_class from (select tid,count(cid) as count_class from teacher2cls GROUP BY tid) as t1) as t2 on t.tid = t2.tid;
#########################################################
SELECT st.sid,sname from student st INNER JOIN score sc on st.sid = sc.student_id where score <60;
#########################################################
select sid,sname from student where sid not IN (SELECT student_id from score GROUP BY student_id HAVING count(course_id) = (select count(cid) from course));
#########################################################
SELECT st.sid,sname from student st where st.sid in (select t2.sid from (select s.sid,sname,course_id from student s INNER JOIN score on s.sid = student_id where s.sid = 1) as t1, (select s.sid,sname,course_id from student s INNER JOIN score on s.sid = student_id where s.sid <> 1) as t2 where t2.course_id = t1.course_id);
#########################################################
SELECT st.sid,sname from student st INNER JOIN score sc on st.sid = sc.student_id where st.sid <> 1 and course_id in (SELECT course_id from student st INNER JOIN score sc on st.sid = sc.student_id where st.sid = 1);
#########################################################
select sid,sname from student where sid in (select t2.student_id from -- 查询2号所学课程 (SELECT course_id,count(course_id) as course_num from score where student_id = 2 GROUP BY course_id) as t1, -- 查询非2号所学课程 (SELECT student_id,course_id,count(course_id) as course_num from score where student_id <> 2 GROUP BY course_id) as t2 where t1.course_id = t2.course_id and t1.course_num = t2.course_num);
#########################################################
delete from score where score.course_id in (select cid from course LEFT JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = '张三');
#########################################################
insert into score(student_id,course_id,score) -- ①没有上过编号“2”课程的同学学号; SELECT t1.sid,t1.course_id,t2.score from (SELECT student.sid,course_id from student LEFT JOIN score on student.sid = score.sid where course_id <>2) as t1, -- ②插入“2”号课程的平均成绩; (SELECT AVG(score) as score from score where course_id = 2) as t2;
#########################################################
select cname,avg_score from course LEFT JOIN (select course_id,avg(score) as avg_score from score GROUP BY course_id) as t1 on course.cid = t1.course_id WHERE cname in ('语文','数学','英语') ORDER BY avg_score;
#########################################################
select cid as '课程ID',max(score) as '最高分',min(score) as '最低分' from score RIGHT JOIN course on course.cid = score.course_id GROUP BY course_id ORDER BY '课程ID';
#########################################################
SELECT course_id,AVG(score) as avg_score, concat(round(sum(case when score.score >= 60 then 1 else 0 end) / count(1) * 100,2),'%') as percent from score group by course_id ORDER BY avg_score,percent desc;
#########################################################
select tname,avg_score from teacher INNER JOIN (select avg(score) as avg_score,teacher_id from score LEFT JOIN course on course.cid = score.course_id GROUP BY course_id ORDER BY avg_score desc)as t1 ON teacher.tid = t1.teacher_id;
#########################################################
# 未实现
#########################################################
SELECT cname,count(score.student_id) as count_s from course left JOIN score on course.cid = score.course_id GROUP BY course.cid;
#########################################################
SELECT student.sid,sname from student INNER JOIN score ON score.student_id = student.sid GROUP BY score.student_id HAVING count(course_id) >2;
#########################################################
SELECT gender,count(gender) as count_sex from student GROUP BY gender;
#########################################################
select sid,sname,gender from student where sname like '张%';
#########################################################
SELECT sname,count(sname) as count_name from student GROUP BY sname HAVING count(sname) >=2;
#########################################################
SELECT course_id,avg(score) as avg_score from score GROUP BY course_id ORDER BY avg_score,course_id desc;
#########################################################
select sname,score from student st inner JOIN (select s.student_id,cname,score from score s LEFT JOIN course c ON c.cid = s.course_id where c.cname = '数学' and s.score <60) as t1 on st.sid = t1.student_id;
#########################################################
SELECT sid,sname from student WHERE sid IN (SELECT student_id from score where course_id = 3 and score>80 );
#########################################################
SELECT course_id,count(student_id)as count_s from score GROUP BY course_id;
#########################################################
-- 最高成绩 SELECT sname,score from student st INNER JOIN (SELECT s.student_id,score from score s where s.course_id in (SELECT cid from course c inner JOIN teacher t on c.teacher_id = t.tid where tname = '王五' ORDER BY score desc ))as t1 ON st.sid = t1.student_id LIMIT 1; -- 最低成绩 SELECT sname,score from student st INNER JOIN (SELECT s.student_id,score from score s where s.course_id in (SELECT cid from course c inner JOIN teacher t on c.teacher_id = t.tid where tname = '王五' ORDER BY score ))as t1 ON st.sid = t1.student_id LIMIT 1;
#########################################################
SELECT cname,count(student_id) as count_student from course inner JOIN score on course.cid = score.course_id GROUP BY course_id;
#########################################################
SELECT s1.student_id,s2.student_id, s1.course_id,s2.course_id, s1.score,s2.score from score as s1,score as s2 where s1.course_id <> s2.course_id and s1.score = s2.score and s1.student_id =s2.student_id;
#########################################################
1.查所有的课程及成绩;2.取前2个?;3.和学生表连表
#########################################################
SELECT student_id from score GROUP BY student_id HAVING count(course_id) >=2;
#########################################################
SELECT cid,cname from course where cid not in (SELECT course_id from score group by course_id);
#########################################################
SELECT tid,tname from teacher where tid not in (select tid from teacher2cls GROUP BY tid);
#########################################################
select student_id,avg(score) as avg_score from score where score >80 group by student_id having count(course_id) >2;
#########################################################
SELECT student_id from score where course_id = 3 and score <60 order by score desc;
#########################################################
DELETE from score where sid = (select sid from score where student_id = 2 and course_id=1);
#########################################################
SELECT sid,sname from student where sid in (SELECT student_id from score LEFT JOIN course on course_id = cid where cname = '物理' and cname ='生物');