mysql博客园day_MySql学习day3

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号； SELECT

A.student_id

FROM

( SELECT score.student_id, score.num FROM score LEFT JOIN course ON score.course_id= course.cid WHERE cname = "生物") AS A

INNER JOIN ( SELECT score.student_id, score.num FROM score LEFT JOIN course ON score.course_id= course.cid WHERE cname = "物理" ) AS B ON A.student_id =B.student_id

WHERE

A.num> B.num 3、查询平均成绩大于60分的同学的学号和平均成绩； SELECT

B.student_id AS"学号",

student.sname AS"姓名",

B.avg_socre AS"平均成绩"FROM

( SELECT student_id, avg( num ) AS avg_socre FROM score GROUP BY student_id HAVING avg( num )> 60) AS B

LEFT JOIN student ON student.sid= B.student_id 4、查询所有同学的学号、姓名、选课数、总成绩； SELECT

score.student_id AS"学号",

student.sname AS"姓名",

count(1 ) AS "选课数",

sum( score.num ) AS"总成绩"FROM

score

LEFT JOIN student ON student.sid=score.student_id

GROUP BY

score.student_id5、查询姓“李”的老师的个数； SELECT

count(1)

FROM

teacher

WHERE

tname LIKE"李%" 6、查询没学过“李平”老师课的同学的学号、姓名； SELECT

sid,

sname

FROM

student

WHERE

sid NOT IN (

SELECT

student_id

FROM

score

WHERE

course_id IN ( SELECT cid FROM course WHERE teacher_id IN ( SELECT tid FROM teacher WHERE tname= "李平老师") )

GROUP BY

student_id

)7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； SELECT

student.sid,

student.sname

FROM

student

LEFT JOIN (

SELECT

score.student_id AS id

FROM

score

WHERE

course_id= 1OR course_id= 2GROUP BY

student_id

HAVING

count(1 ) > 1) AS B ON student.sid= B.id 8、查询学过“李平”老师所教的所有课的同学的学号、姓名； SELECT

sid,

sname

FROM

student

WHERE

sid IN (

SELECT

student_id

FROM

score

WHERE

course_id IN ( SELECT course.cid FROM teacher LEFT JOIN course ON teacher.tid= course.teacher_id WHERE teacher.tname = "李平老师")

GROUP BY

student_id

HAVING

count(1 ) = ( SELECT count( 1 ) FROM teacher LEFT JOIN course ON teacher.tid = course.teacher_id WHERE teacher.tname = "李平老师")

)9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； SELECT

sid,

sname

FROM

student

WHERE

sid IN (

SELECT

A.student_id

FROM

( SELECT student_id, num FROM score WHERE course_id= 1) AS A

INNER JOIN ( SELECT student_id, num FROM score WHERE course_id= 2 ) AS B ON A.student_id =B.student_id

WHERE

A.num>B.num

)10、查询有课程成绩小于60分的同学的学号、姓名； SELECT

sid,

sname

FROM

student

WHERE

sid IN ( SELECT DISTINCT student_id FROM score WHERE num< 60 ) 11、查询没有学全所有课的同学的学号、姓名； SELECT

sid,

sname

FROM

student

WHERE

sid IN ( SELECT student_id FROM score GROUP BY student_id HAVING count(1 ) < ( SELECT count( 1) FROM course ) );12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名； SELECT

sid,

sname

FROM

student

WHERE

sid IN ( SELECT student_id FROM score WHERE course_id IN ( SELECT course_id FROM score WHERE student_id= 1 ) GROUP BY student_id ) 13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名； SELECT

sid,

sname

FROM

student

WHERE

sid IN ( SELECT student_id FROM score WHERE student_id!= 1 AND course_id IN ( SELECT course_id FROM score WHERE student_id = 1 ) GROUP BY student_id ) 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名； SELECT*FROM

score

WHERE

student_id= 2SELECT

A.student_id

FROM

(

SELECT

score.student_id,

score.course_id

FROM

score

WHERE

student_id IN (

SELECT

student_id

FROM

score

WHERE

student_id!= 2GROUP BY

student_id

HAVING

count(1 ) = ( SELECT count( 1 ) FROM score WHERE student_id = 2)

)

) AS A

WHERE

course_id IN ( SELECT course_id FROM score WHERE student_id= 2)

GROUP BY

student_id

HAVING

count( A.student_id )= ( SELECT count( 1 ) FROM score WHERE student_id = 2 ) 15、删除学习“李平”老师课的 SCORE表记录； DELETE

FROM

score

WHERE

course_id IN ( SELECT cid FROM course WHERE teacher_id= ( SELECT teacher.tid FROM teacher WHERE tname = "李平老师" ) ) 16、向 SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“ 002”课程的同学学号；②插入“002”号课程的平均成绩； INSERT INTO score ( student_id, course_id, num ) SELECT

student_id,2,

( SELECT avg( num ) FROM score WHERE course_id= 2)

FROM

score

WHERE

student_id NOT IN ( SELECT student_id FROM score WHERE course_id= 2)

GROUP BY

student_id17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生 ID,语文,数学,英语,有效课程数,有效平均分； 注 :因例子里没有课程，换为生物、物理、体育 SELECT*,

(

IF

( ISNULL( D.生物 ), 0, D.生物 )+IF

( ISNULL( D.物理 ), 0, D.物理 )+IF

( ISNULL( D.体育 ), 0, D.体育 )

)/D.有效课程数 AS 有效平均分

FROM

(

SELECT*,

IF

( ISNULL( C.生物 ), 0,1 ) +IF

( ISNULL( C.物理 ), 0,1 ) +IF

( ISNULL( C.体育 ), 0,1) AS 有效课程数

FROM

(

SELECT

s1.student_id,

(

SELECT

num

FROM

score AS s2

WHERE

s1.student_id=s2.student_id

AND course_id= ( SELECT cid FROM course WHERE cname = "生物")

) AS 生物,

(

SELECT

num

FROM

score AS s2

WHERE

s1.student_id=s2.student_id

AND course_id= ( SELECT cid FROM course WHERE cname = "物理")

) AS 物理,

(

SELECT

num

FROM

score AS s2

WHERE

s1.student_id=s2.student_id

AND course_id= ( SELECT cid FROM course WHERE cname = "体育")

) AS 体育

FROM

score AS s1

GROUP BY

s1.student_id

) AS C

) AS D18、查询各科成绩最高和最低的分：以如下形式显示：课程 ID，最高分，最低分； SELECT

course_id,

max( num ),

min( num )

FROM

score

GROUP BY

course_id19、按各科平均成绩从低到高和及格率的百分数从高到低顺序； SELECT

course_id,

avg( num ),

sum( CASE WHEN num< 60 THEN 0 ELSE 1 END ) / sum( 1) AS jgl

FROM

score

GROUP BY

course_id

ORDER BY

avg( num ) ASC,

jgl DESC;20、课程平均分从高到低显示(显示任课老师)； SELECT

score.course_id,

course.cname,

teacher.tname,

avg( IF ( ISNULL( score.num ), 0, score.num ) ) AS course_avg

FROM

score

LEFT JOIN course ON score.course_id=course.cid

LEFT JOIN teacher ON course.teacher_id=teacher.tid

GROUP BY

score.course_id

ORDER BY

course_avg DESC21、查询各科成绩前三名的记录 : ( 不考虑成绩并列情况 ) SELECT

student_id,

course_id,

num,

s3.first_num,

s3.third_num

FROM

score AS s4

LEFT JOIN (

SELECT

sid,

(

SELECT

s2.num

FROM

score AS s2

WHERE

s2.course_id=s1.course_id

ORDER BY

s2.num DESC

LIMIT 0,1) AS first_num,

(

SELECT

s2.num

FROM

score AS s2

WHERE

s2.course_id=s1.course_id

ORDER BY

s2.num DESC

LIMIT2,1) AS third_num

FROM

score AS s1

) AS s3 ON s3.sid=s4.sid

WHERE

s4.num>=s3.third_num

AND s4.num<= s3.first_num 22、查询每门课程被选修的学生数； SELECT

course_id,

count(1)

FROM

score

GROUP BY

course_id23、查询出只选修了一门课程的全部学生的学号和姓名； SELECT

sid,

sname

FROM

student

WHERE

sid IN ( SELECT student_id FROM score GROUP BY student_id HAVING count(1 ) = 1 ) 24、查询男生、女生的人数； SELECT

gender,

count(1)

FROM

student

GROUP BY

gender25、查询姓“张”的学生名单； SELECT

sid,

sname

FROM

student

WHERE

sname LIKE"张%";26、查询同名同姓学生名单，并统计同名人数； SELECT

sname,

count(1)

FROM

student

GROUP BY

sname

HAVING

count(1 ) > 1 27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列； SELECT

course_id,

avg( num )

FROM

score

GROUP BY

course_id

ORDER BY

avg( num ) ASC,

course_id DESC28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩； SELECT

score.student_id,

student.sname,

avg( score.num )

FROM

score

LEFT JOIN student ON score.student_id=student.sid

GROUP BY

student_id

HAVING

avg( score.num )> 85 29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；因没有数学，使用生物代替 SELECT

student_id,

num

FROM

score

WHERE

course_id= ( SELECT cid FROM course WHERE cname = "生物")

AND num< 60 30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； SELECT student_id, num FROM score WHERE course_id = 3 AND num > 80 31、求选了课程的学生人数 SELECT

count(1)

FROM

( SELECT student_id FROM score GROUP BY student_id ) AS A32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩； 因没有杨艳老师，使用刘海燕老师代替 SELECT

student.sname,

score.num

FROM

score

LEFT JOIN student ON score.student_id=student.sid

WHERE

course_id IN ( SELECT cid FROM course WHERE teacher_id= ( SELECT tid FROM teacher WHERE tname = "刘海燕老师") )

AND score.num=(

SELECT

score.num

FROM

score

WHERE

course_id IN ( SELECT cid FROM course WHERE teacher_id= ( SELECT tid FROM teacher WHERE tname = "刘海燕老师") )

ORDER BY

score.num DESC

LIMIT 0,1)33、查询各个课程及相应的选修人数； SELECT

score.course_id,

course.cname,

count(1)

FROM

score

LEFT JOIN course ON score.course_id=course.cid

GROUP BY

course_id34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩； SELECT

A1.student_id,

A1.course_id,

A1.num

FROM

score AS A1,

score AS A2

WHERE

A1.sid!=A2.sid

AND A1.course_id!=A2.course_id

AND A1.num= A2.num 35、查询每门课程成绩最好的前两名； SELECT*FROM

(

SELECT*,

(

SELECT

A2.num

FROM

score AS A2

WHERE

A2.course_id=A1.course_id

ORDER BY

A2.num DESC

LIMIT 0,1) AS first_num,

(

SELECT

A2.num

FROM

score AS A2

WHERE

A2.course_id=A1.course_id

ORDER BY

A2.num DESC

LIMIT1,1) AS second_num

FROM

score AS A1

) AS A3

WHERE

A3.num>=A3.second_num

AND A3.num<= A3.first_num 36、检索至少选修两门课程的学生学号； SELECT student_id, count( 1 ) FROM score GROUP BY student_id HAVING count( 1 ) > 1 37、查询全部学生都选修的课程的课程号和课程名； SELECT

score.course_id,

course.cname,

count(1)

FROM

score

LEFT JOIN course ON score.course_id=course.cid

GROUP BY

course_id

HAVING

count(1 ) = ( SELECT count( 1 ) FROM student ) 38、查询没学过“李平”老师讲授的任一门课程的学生姓名； SELECT

sname

FROM

student

WHERE

sid NOT IN (

SELECT

student_id

FROM

score

WHERE

course_id IN ( SELECT cid FROM course WHERE teacher_id= ( SELECT tid FROM teacher WHERE tname = "李平老师") )

)39、查询两门以上不及格课程的同学的学号及其平均成绩； SELECT

A2.student_id,

avg( A2.num )

FROM

score AS A2

INNER JOIN ( SELECT student_id, count(1 ) FROM score WHERE num < 60 GROUP BY student_id HAVING count( 1 ) > 1 ) AS A1 ON A1.student_id =A2.student_id

GROUP BY

A2.student_id40、检索“004”课程分数小于60，按分数降序排列的同学学号； SELECT

student_id,

num

FROM

score

WHERE

course_id= 4AND num< 60ORDER BY

num DESC41、删除“002”同学的“001”课程的成绩； DELETE

FROM

score

WHERE

student_id= 2AND course_id=1