day41 mysql 学习 练习题 重要*****
MySQL 练习题【二1.表如下:
收获和注意点：*****
#1 GROUP by 可以放到where s_id in ()条件局后边 GROUP BY s_id having 详见题12 #2 做查询要学会拆分要求，找出他们之间的联系 然后将拆分的要求以新的列表名字（as A）的格式放到from 后边 然后在where 后匹配相关的点 #3 count(1) count（）括号中的内容写成１就可以 实际上是计数的一个东西，就是记算行总 在gruop by 语句中使用起到计算每以个组的内容的行数 #４ 找没有学过谁的课程的学生ｉｄ 可以反向找 先找到学过的 然后再 not in ( 学过的 ) 这样就可以找到符合条件的 #5 最好不要用 join 相关的语句，不好用 还不如用 select * from ren A,menpai B where A.S_ID =B.s_id #行转列中要找好行与列之间的关系 如地16题中加了一句and sc1.s_id=sc.s_id 这句话让它们产生了关系，在引用完后后边给他命一个新的名字 as 'pytron' 或省去 as #6 行转列中打印每个分数段的人数，成绩id, 计算每个分数段 的人数用这种放法 sum(CASE WHEN sc.s_score BETWEEN 85 and 100 then 1 else 0 end ) as '[100-85]', #详见题20
2.下面:开始你的表演
1、查询课程编号“001”比课程编号“002” 成绩高的所有学生的学号****
#把两个表同时放过来（并且都命名一个新的名字）一起查，
#1先找出所有包含学科为1 和学科为2 的学生 以便后续引用 select * from score where c_id='1'; select * from score where c_id='2' #2 正式的代码 select A.s_id from (select * from score where c_id='1')as A, #同时引入两个表，并给表命名一个新的昵称 A 昵称B (select * from score where c_id='2') as B where A.s_id=B.s_id and A.s_score>B.s_score # 当满足即学学科1又学学科2 并且学科1的成绩大于学科二的成绩时，就显示出来
2、查询平均成绩大于60分的同学的学号和平均成绩;
SELECT s_id,avg(s_score) from score GROUP BY s_id having avg(s_score)>60 #group by 语句前边不能写where3、查询所有同学的学号、姓名、选课数、总成绩；
SELECT s_id,count(SC.c_id),sum(s_score) from score SC,student ST WHERE ST.s_id1=SC.s_id GROUP BY s_id #两表联合查询要让这两个表的学生id 关联起来4、查询含有"子"的老师的个数；
SELECT count(1) from teacher where t_name like'%子%' #count(1) 相当与给后边查出来的数据计数5、查询没学过“老子”老师课的同学的学号、姓名；
##SELECT c_id from course co,teacher te where co.t_id=te.t_id and te.t_name='老子' #找到老子老师交的课程 SELECT DISTINCT st.s_id1,st.s_name from score S,student st where S.s_id not in (SELECT s_id from score where c_id in (SELECT c_id from course co,teacher te where co.t_id=te.t_id
and te.t_name='老子')) and st.s_id1=S.s_id #注意distinct的用法 可以去重 #先找出学过老子课程的学生，然后从总score表中查找（不属于学过老子课程的学生）没有学老子课程的学生 然后再关联到学生的表格中6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
#1 先找到学过1 的和学过2 的学生ID**** SELECT s_id from score where c_id='1'; SELECT s_id from score where c_id='2' SELECT distinct s_id,A.s_name from score,student A where s_id in (SELECT s_id from score where c_id='1') and s_id in(SELECT s_id from score where c_id='2') and s_id=A.s_id1 ## 上边这句话 可以找出只在 in (*****) 里边的内容 没有的不给出 精确性找出即学过1也学过2的 学生ID 和学生姓名
7、查询学过“老子”老师所教的所有课的同学的学号、姓名；
SELECT s_id,S.s_name from score,student S WHERE c_id in (SELECT c_id from teacher te,course
where te.t_name='老子' and c_id= te.t_id) and s_id=S.s_id18、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
1.先找出学两个课程的学生 SELECT s_id from score where c_id ='1'; SELECT s_id from score where c_id ='2' 2再找出学两个课程而且课程A的成绩比课程2的成绩高的学生姓名学号 SELECT A.s_id,s_name from (SELECT * from score where c_id ='1')A,(SELECT * from score where c_id ='2')B,student where A.s_score>B.s_score and A.s_id=B.s_id=s_id1
9、查询有课程成绩小于60分的同学的学号、姓名；
SELECT distinct st.s_id1,s_name from score sc,student st where s_score>'60' and st.s_id1=sc.s_id
10、查询没有学全所有课的同学；
SELECT s_id1,s_name from student where s_id1 in (SELECT s_id from score GROUP BY s_id HAVING count(1) != (SELECT count(1) from course))11、查询至少有一门课与学号为“002”的同学所学相同的同学的学号和姓名；
select sc.c_id from score sc,student st where sc.s_id =st.s_id and sc.s_id ='2'
select DISTINCT st.s_id,st.s_name from score sc,student st where
st.s_id =sc.s_id and sc.c_id in(select sc.c_id from score sc where sc.s_id ='2')12、查询学过 学号为“002”同学全部课程 的其他同学的学号和姓名；
select sc.c_id from score sc where sc.s_id = '2' select st.s_id,st.s_name from score sc,student st where sc.s_id =st.s_id and st.s_id !='2' and sc.c_id in (select sc.c_id from score sc where sc.s_id = '2') GROUP BY sc.s_id HAVING count(1) = (select count(1) from score sc where sc.s_id = '2') ##学生学得课程在学生2的课程里 且查找出来的学生学的课程数量与学生2 学的课程数量一致
13、查询和“002”号的同学学习的课程完全相同的,其他同学学号和姓名；
14、把“score”表中“老子”老师教的课的成绩都更改为此课程的平均成绩；
15、删除学习“墨子”老师课的score表记录；
16、按平均成绩从高到低显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示： 学生ID,python,java,linux,有效课程数,有效平均分
#1先找到那些内容是Python课程的成绩 -- select sc.s_score pyton from score sc,course c where sc.c_id=c.c_id and c.c_name ='python'; #2 在将行转列放到表中 select sc1.s_id, (select sc.s_score from score sc,course c where sc.c_id = c.c_id and c.c_name = 'python' and sc1.s_id =sc.s_id ) as 'python', #注意这里又加了一个and sc1.s_id=sc.s_id 这个很重要 sc1相当于一个全局变量，内部可以引用，并且这是让成绩与学生关联的一个条件，必须加上 (select sc.s_score from score sc,course c where sc.c_id = c.c_id and c.c_name = 'java' and sc1.s_id =sc.s_id) as 'java', (select sc.s_score from score sc,course c where sc.c_id = c.c_id and c.c_name = 'linux' and sc1.s_id =sc.s_id) as 'linux', count(1) as '课程数', avg(sc1.s_score) as '平均分' from score sc1 GROUP BY sc1.s_id
17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select c_id,max(s_score) '最高分',min(s_score)'最低分' from score GROUP BY c_id
18、按各科平均成绩从低到高和及格率的百分数从高到低顺序
19、查询老师所教课程平均分从高到低显示,并显示老师的名称及课程名称
20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
-- 20、统计列印各科成绩,各分数段人数, -- 显示:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] SELECT ss.c_id,cc.c_name,sum(CASE when ss.s_score BETWEEN 85 and 100 then 1 else 0 end) '[85-100]', sum(CASE when ss.s_score BETWEEN 70 and 84 then 1 else 0 end) '[70-84]', (CASE when ss.s_score < 60 then 1 else 0 end) '[<60]' from score ss,course cc where cc.c_id=ss.c_id GROUP BY ss.c_id #分组分行查找 命名出字段名，将字段打印出来， #sum(CASE when ss.s_score BETWEEN 70 and 84 then 1 else 0 end) '[70-84]' #这是一种经典的写法 算出分数在70-84之间的人的个数 用到了 case when 判断语句 then 1 else 0(如果符合就加一，不符合就记0） end ) -- 在最后 命名好这个的字段名
21、查询每门课程被选修的学生数.
22、查询出只选修了一门课程的学生的学号和姓名
23、查询学生表中男生、女生人数
24、查询姓“张”的学生名单
25、查询同名学生名单，并统计同名人数
26、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
27、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
28、查询课程名称为“python”，且分数低于60的学生姓名和分数
29、查询所有学生的选课情况,显示 学生编号,学生姓名,所选课程名称
30、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
31、查询不及格的课程，并按课程号从大到小排列
32、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
33、求 已选课程的学生人数
34、查询选修“老子”老师所授课程的学生中，成绩最高的学生姓名及其成绩
35、查询各个课程及相应的选修人数
36、查询不同课程但成绩相同的学生的学号、课程号、学生成绩
37、检索至少选修两门课程的学生学号
38、查询全部学生都选修的课程的课程号和课程名
39、查询没学过“老子”老师讲授的任一门课程的学生姓名
40、查询两门以上不及格课程的同学的学号及其平均成绩
41、检索“003”课程分数小于60，按分数降序排列的同学学号
42、删除“002”同学的“001”课程的成绩
