sql数据库查询
mysql练习题:https://www.cnblogs.com/majj/articles/8946156.html
练习题答案:https://www.cnblogs.com/majj/articles/9167308.html
-- 2 select A.student_id,A.num as 生物,B.num as 物理 from (select student_id,num,cname from score left join course on course_id = cid where cname = '生物') as A left join (select student_id,num,cname from score left join course on course_id = cid where cname = '物理') as B on A.student_id = B.student_id where A.num > if(isnull(B.num),0,B.num ) -- 3 -- select student_id as 学号,avg(num) as 平均成绩 from score group by student_id having avg(num) >60; -- 4 有问题的代码,不知道什么问题 select student_id,count(course_id),group_concat(sname) from (select * from score left join student on score.student_id = student.sid) as A group by A.student_id 正确代码 select student_id,sname,课程数,总成绩 from (select student_id,count(course_id) as 课程数,sum(num) as 总成绩 from score group by student_id) as A left join student on A.student_id = student.sid -- 5 -- select count(tid) from teacher where tname like '李%' -- 6 select student_id,GROUP_CONCAT(DISTINCT sname),GROUP_CONCAT(DISTINCT tname) from (select * from (select student_id,course_id,num,sname from score left join student on score.student_id = student.sid) as A left join course on A.course_id = course.cid) as B left join teacher on B.teacher_id = teacher.tid group by student_id HAVING not FIND_IN_SET('李平老师',GROUP_CONCAT(DISTINCT tname)); -- 7 select student_id,sname from (select student_id,GROUP_CONCAT(course_id) as group_course from score group by student_id HAVING FIND_IN_SET(1,group_course) and FIND_IN_SET(2,group_course)) as A left JOIN student on A.student_id = student.sid -- 8 -- 方法1,有问题,不能自己写李平的课程是2 4 select student_id,GROUP_CONCAT(DISTINCT sname),GROUP_CONCAT( tname),GROUP_CONCAT( DISTINCT course_id) from (select * from (select student_id,course_id,num,sname from score left join student on score.student_id = student.sid) as A left join course on A.course_id = course.cid) as B left join teacher on B.teacher_id = teacher.tid group by student_id HAVING FIND_IN_SET('2',GROUP_CONCAT(DISTINCT course_id)) and FIND_IN_SET('4',GROUP_CONCAT(DISTINCT course_id)) 方法2 SELECT * from (select student_id,GROUP_CONCAT(course_id ORDER BY course_id ASC) as course_student,GROUP_CONCAT(DISTINCT sname),GROUP_CONCAT(DISTINCT tname) as tname from (select * from (select student_id,course_id,num,sname from score left join student on score.student_id = student.sid) as A left join course on A.course_id = course.cid) as B left join teacher on B.teacher_id = teacher.tid WHERE tname = '李平老师' GROUP BY student_id) as C left JOIN (select tname,GROUP_CONCAT(cid ORDER BY cid ASC) as course_teacher from course INNER JOIN teacher on course.teacher_id = teacher.tid where tname = '李平老师' GROUP BY tname) as D on C.tname = D.tname WHERE course_student = course_teacher 方法3 select score.student_id,student.sname,count(score.course_id) as 选课总数,GROUP_CONCAT(DISTINCT tc2_tw) as 讲课总数 from score inner join student inner join (select teacher.tname as tc2_tn,course.cid as tc2_tcid,tc.td as tc2_tw from course inner join teacher inner join (select teacher.tname as tn,count(course.cid) as td from course inner join teacher on course.teacher_id = teacher.tid group by tn having tn='李平老师') as tc on course.teacher_id = teacher.tid and tc.tn = teacher.tname) as tc2 on score.course_id = tc2.tc2_tcid and score.student_id = student.sid group by score.student_id HAVING 选课总数 = 讲课总数 方法三拆分1 李平老师教的课程数 select teacher.tname as tn,count(course.cid) as td from course inner join teacher on course.teacher_id = teacher.tid group by tn having tn='李平老师' and tc.tn = teacher.tname 方法三拆分2 李平老师教的课程号和课程数 select teacher.tname as tc2_tn, course.cid as tc2_tcid, tc.td as tc2_tw from course inner join teacher inner join (select teacher.tname as tn,count(course.cid) as td from course inner join teacher on course.teacher_id = teacher.tid group by tn having tn='李平老师') as tc on course.teacher_id = teacher.tid and tc.tn = teacher.tname -- 9 select student_id,sname from (select A.student_id from (SELECT * from score where course_id = 1) as A left JOIN (SELECT * from score where course_id = 2) as B on A.student_id = B.student_id where A.num > B.num) as C left JOIN student on C.student_id = student.sid -- 10 SELECT student_id,sname from (select student_id from score where num <60 GROUP BY student_id) as A left JOIN student on A.student_id = student.sid -- 11 select student_id,sname from (select * from (SELECT student_id,GROUP_CONCAT(course_id ORDER BY course_id) as 选的课程 from score GROUP BY student_id) as A inner JOIN (select GROUP_CONCAT(cid ORDER BY cid ASC) as 全部课程 from course) as B where 选的课程 != 全部课程) as C left JOIN student on C.student_id = student.sid -- 12 有问题的代码 SELECT score.student_id from (select student_id,GROUP_CONCAT(course_id)as a from score GROUP BY student_id HAVING student_id = 1) as A inner JOIN score where course_id in (1,2,4) GROUP BY score.student_id -- 这里的a 是字符串 ,而in方法只能用列表,mysql 又不能将字符串转化为列表,不得不说这个mysql是真难用 -- where a = '1,2,4' -- where course_id in (1,2,4) SELECT student_id,sname from (SELECT student_id from (select course_id as A_course from score where student_id = 1) as A inner JOIN score where course_id = A_course GROUP BY student_id) as B LEFT JOIN student on B.student_id = student.sid -- 13 不知道是不是审题错了,和12题基本一样 SELECT student_id,sname from (SELECT student_id from (select course_id as A_course from score where student_id = 1) as A inner JOIN score where course_id = A_course GROUP BY student_id) as B LEFT JOIN student on B.student_id = student.sid where student_id !=1 -- 14 SELECT student_id,sname from (SELECT student_id from (select student_id as a_id, GROUP_CONCAT(course_id ORDER BY course_id ASC)as a from score GROUP BY student_id HAVING student_id = 2) as A inner JOIN (select student_id,GROUP_CONCAT(course_id ORDER BY course_id ASC) as b from score GROUP BY student_id) as B where a = b and student_id != 2) as C LEFT JOIN student on C.student_id = student.sid -- 15 不知道这样写为什么不行,难道是主键不能删除 delete from score where sid in (select sid from score left join (select * from course left join teacher on course.teacher_id = teacher.tid) as A on score.course_id = A.cid where tname = '李平老师') delete from score where course_id in ( select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.tname = '李平老师' ) -- 16 INSERT into score(student_id,course_id,num) SELECT student_id,2,AVG(num) from score where student_id not in (select student_id from score where course_id = 2) GROUP BY student_id -- insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2) -- from student where sid not in ( -- select student_id from score where course_id = 2 -- ) -- 17 select A.student_id as 学号,生物,物理,体育,美术,平均分 from (select student_id,num as 生物 from score inner JOIN course on score.course_id = course.cid where cname = '生物') as A inner JOIN (select student_id,num as 物理 from score inner JOIN course on score.course_id = course.cid where cname = '物理') as B inner join (select student_id,num as 体育 from score inner JOIN course on score.course_id = course.cid where cname = '体育') as C inner join (select student_id,num as 美术 from score inner JOIN course on score.course_id = course.cid where cname = '美术') as D inner join (select student_id,AVG(num) as 平均分 from score GROUP BY student_id) as E on A.student_id = B.student_id and A.student_id = C.student_id and A.student_id = D.student_id and A.student_id = E.student_id ORDER BY 平均分 ASC -- 18 -- select course_id,max(num),min(num) from score group by course_id -- 19 SELECT C.course_id,平均分,及格率 from (select course_id,avg(num) as 平均分,GROUP_CONCAT(num) from score group by course_id) as C LEFT JOIN (select A.course_id,及格, 总数,及格/总数 as 及格率 from (SELECT course_id,count(num) as 及格 from score where num >=60 GROUP BY course_id) as A left JOIN (SELECT course_id,count(num) as 总数 from score GROUP BY course_id) as B on A.course_id = B.course_id) as D on C.course_id = D.course_id ORDER BY 平均分 ASC, 及格率 DESC -- 20 select 平均分,tname from (select course_id,avg(num) as 平均分 from score GROUP BY course_id ORDER BY 平均分 DESC ) as A LEFT JOIN (select * from course LEFT JOIN teacher on course.teacher_id = teacher.tid ) as B on A.course_id = B.cid ORDER BY 平均分 DESC -- 21 -- 还没做出来,有问题 -- select course_id,GROUP_CONCAT(num),GROUP_CONCAT(student_id ORDER BY num DESC ) from score GROUP BY course_id -- 22 -- select course_id,COUNT(student_id) from score GROUP BY course_id -- 23 select A.student_id,sname from (select student_id from score GROUP BY student_id HAVING count(course_id) = 1) as A LEFT JOIN student on A.student_id = student.sid -- 24 方法一 select * from (select count(1) as man from student where gender = '男') as A, (select count(1) as feman from student where gender = '女') as B -- 方法二 -- select gender,count(1) from student group by gender -- 25 -- select * from student where sname like '张%' -- 26 -- select sname,count(1) from student GROUP BY sname -- 27 -- select course_id,avg(if(isnull(num), 0 ,num)) as avg from score GROUP BY course_id ORDER BY avg ASC,course_id DESC -- 28 SELECT student_id,sname,avg from (select student_id,avg(if(isnull(num),0,num)) as avg from score GROUP BY student_id HAVING avg > 85) as A LEFT JOIN student on A.student_id = student.sid -- 29 select student_id,sname,num from (select * from score left JOIN course on score.course_id = course.cid where cname = '生物' and num < 60) as A left join student on A.student_id = student.sid -- 30 SELECT A.student_id,sname from (select student_id from score where course_id = 3 and num >80) as A LEFT JOIN student on A.student_id = student.sid -- 31 select count(1) from (select student_id,count(1) from score GROUP BY student_id) as A -- 32 select student_id,sname from (select student_id,num from (select * from score LEFT JOIN course on score.course_id = course.cid) as A LEFT JOIN teacher on A.teacher_id = teacher.tid where tname = '刘海燕老师' ORDER BY num desc LIMIT 1) as B LEFT JOIN student on B.student_id = student.sid -- 33 select cname,count from (select course_id,count(1) as count from score GROUP BY course_id) as A left join course on A.course_id = course.cid -- 34 -- select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id; -- 35 -- 有问题 -- select course_id,GROUP_CONCAT(sid ORDER BY num desc) from score GROUP BY course_id -- 36 -- select student_id from score GROUP BY student_id HAVING count(course_id) > 1 -- 37 select course_id,GROUP_CONCAT(student_id ORDER BY student_id) as st from score GROUP BY course_id HAVING st= (select GROUP_CONCAT( DISTINCT student_id ORDER BY student_id) from score ) -- 38 SELECT A.student_id,sname from (select * from score where student_id not in (select DISTINCT student_id from score INNER JOIN course INNER JOIN teacher on score.course_id = course.cid AND course.teacher_id = teacher.tid WHERE tname = '李平老师') ) as A LEFT JOIN student on A.student_id = student.sid -- 39 select A.student_id,avg from (select student_id ,count(1) as count from score where num<60 GROUP BY student_id HAVING count >1) as A LEFT JOIN (select student_id,avg(num) as avg from score GROUP BY student_id) as B on A.student_id = B.student_id -- 40 -- SELECT student_id from score where course_id= 4 and num < 60 ORDER BY num desc -- 41 -- delete from score where course_id = 1 and student_id = 2 21 35题 一个问题,没有解决,没做出来