vlambda博客
学习文章列表

8、查询班级有多少种性别

select distinct gender from student;

9、查询“生物”课程比“物理”课程成绩高的所有学生的学号（自己的成绩）

(1)查询出学习生物的学生信息

select student_id,number as sw_score from score

  left join course on score.corse_id = course.cid where course.cname = '生物';

(2)查询出学习物理的学生信息

select student_id,number as wl_score from score

  left join course on score.corse_id = course.cid where course.cname = '物理';

(3)比较生物成绩高于物理成绩的学生信息 -- 将两个表连接起来

select A.student_id,sw_score,wl_score from

  (select student_id,number as sw_score from score

    left join course on score.corse_id = course.cid where course.cname = '生物') as A

  left join

    (select student_id,number as wl_score from score

      left join course on score.corse_id = course.cid where course.cname = '物理') as B

   on A.student_id = B.student_id where sw_score > wl_score or wl_score is null;

另一种写法：

SELECT a.sid,b.number AS sw_score,c.number AS wl_score FROM student AS a

  INNER JOIN score AS b ON a.sid=b.student_id AND b.corse_id=(select cid from course where cname='生物')

    LEFT JOIN score AS c ON a.sid=c.student_id AND c.corse_id=(select cid from course where cname='物理')

      WHERE b.number>c.number OR c.number is NULL;

-- 查找学生的学号和成绩，查找学号，首先想到用到学生表，（先假装知道生物和物理的课程号）直接用学生表连接两张表，然后再用子查询语句，确定生物和物理的课程号

10、查询平均成绩大于60分的同学的学号和平均成绩

select student_id,avg(number) from score 

    group by student_id having avg(number) > 60;

（1）增加显示学生名

select student_id,name,avg(number) from score

  inner join student on score.student_id = student.sid

    group by student_id having avg(number) > 60;

11、查询所有同学的学号、姓名、选课数、总成绩（包含语句的进化过程）

(1)查询所有同学的学号、姓名

select sid,name from student;

(2)查询所有同学的选课数、总成绩 -- 都用score表

select student_id,count(corse_id),sum(number) from score 

    group by student_id;

(3)将前两步合并，用连接语句

select a.sid,a.name,count(corse_id) as num_corse,sum(number) as sum_score from student as a

  inner join score as b on a.sid = b.student_id

    group by student_id;

12、查询姓“赵”的老师的个数

SELECT count(tname) from teacher where tname like '赵%';  -- 模糊查询

SELECT count(tname) from teacher where tname rlike '赵';  -- 正则表达式