测试数据

  1. --建表
  2. --学生表
  3. CREATE TABLE `Student`(
  4.     `s_id` VARCHAR(20),
  5.     `s_name` VARCHAR(20) NOT NULL DEFAULT '',
  6.     `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
  7.     `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
  8.     PRIMARY KEY(`s_id`)
  9. );
  10. --课程表
  11. CREATE TABLE `Course`(
  12.     `c_id`  VARCHAR(20),
  13.     `c_name` VARCHAR(20) NOT NULL DEFAULT '',
  14.     `t_id` VARCHAR(20) NOT NULL,
  15.     PRIMARY KEY(`c_id`)
  16. );
  17. --教师表
  18. CREATE TABLE `Teacher`(
  19.     `t_id` VARCHAR(20),
  20.     `t_name` VARCHAR(20) NOT NULL DEFAULT '',
  21.     PRIMARY KEY(`t_id`)
  22. );
  23. --成绩表
  24. CREATE TABLE `Score`(
  25.     `s_id` VARCHAR(20),
  26.     `c_id`  VARCHAR(20),
  27.     `s_score` INT(3),
  28.     PRIMARY KEY(`s_id`,`c_id`)
  29. );
  30. --插入学生表测试数据
  31. insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
  32. insert into Student values('02' , '钱电' , '1990-12-21' , '男');
  33. insert into Student values('03' , '孙风' , '1990-05-20' , '男');
  34. insert into Student values('04' , '李云' , '1990-08-06' , '男');
  35. insert into Student values('05' , '周梅' , '1991-12-01' , '女');
  36. insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
  37. insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
  38. insert into Student values('08' , '王菊' , '1990-01-20' , '女');
  39. --课程表测试数据
  40. insert into Course values('01' , '语文' , '02');
  41. insert into Course values('02' , '数学' , '01');
  42. insert into Course values('03' , '英语' , '03');
  44. --教师表测试数据
  45. insert into Teacher values('01' , '张三');
  46. insert into Teacher values('02' , '李四');
  47. insert into Teacher values('03' , '王五');
  49. --成绩表测试数据
  50. insert into Score values('01' , '01' , 80);
  51. insert into Score values('01' , '02' , 90);
  52. insert into Score values('01' , '03' , 99);
  53. insert into Score values('02' , '01' , 70);
  54. insert into Score values('02' , '02' , 60);
  55. insert into Score values('02' , '03' , 80);
  56. insert into Score values('03' , '01' , 80);
  57. insert into Score values('03' , '02' , 80);
  58. insert into Score values('03' , '03' , 80);
  59. insert into Score values('04' , '01' , 50);
  60. insert into Score values('04' , '02' , 30);
  61. insert into Score values('04' , '03' , 20);
  62. insert into Score values('05' , '01' , 76);
  63. insert into Score values('05' , '02' , 87);
  64. insert into Score values('06' , '01' , 31);
  65. insert into Score values('06' , '03' , 34);
  66. insert into Score values('07' , '02' , 89);
  67. insert into Score values('07' , '03' , 98);

习题

1、查询”01”课程比”02”课程成绩高的学生的信息及课程分数

  1. SELECT
  2.     a.*,
  3.     b.s_score AS 01Score,
  4.     c.s_score AS 02Score 
  5. FROM
  6.     Student a
  7.     LEFT JOIN Score b ON a.s_id = b.s_id 
  8.     AND b.c_id = '01'
  9.     LEFT JOIN Score c ON a.s_id = c.s_id 
  10.     AND c.c_id = '02' 
  11.     OR c.c_id = NULL 
  12. WHERE
  13.     b.s_score < c.s_score

image.png
2、查询”01”课程比”02”课程成绩低的学生的信息及课程分数

  1. SELECT
  2.     a.*,
  3.     b.s_score AS 01_score,
  4.     c.s_score AS 02_score 
  5. FROM
  6.     student a
  7.     LEFT JOIN score b ON a.s_id = b.s_id 
  8.     AND b.c_id = '01' 
  9.     OR b.c_id =
  10.     NULL JOIN score c ON a.s_id = c.s_id 
  11.     AND c.c_id = '02' 
  12. WHERE
  13.     b.s_score < c.s_score

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

  1. SELECT
  2.     a.s_id,
  3.     a.s_name,
  4.     c.avgs 
  5. FROM
  6.     student a
  7.     JOIN ( SELECT b.s_id, AVG( b.s_score ) AS avgs FROM score b GROUP BY b.s_id ) c ON a.s_id = c.s_id 
  8. WHERE
  9.     c.avgs > 60
  11. --思路二
  12. SELECT
  13.     b.s_id,
  14.     b.s_name,
  15.     ROUND( AVG( a.s_score ), 2 ) AS avg_score 
  16. FROM
  17.     student b
  18.     JOIN score a ON b.s_id = a.s_id 
  19. GROUP BY
  20.     b.s_id,
  21.     b.s_name 
  22. HAVING
  23.     avg_score >= 60;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)

  1. SELECT
  2.     b.s_id,
  3.     b.s_name,
  4.     ROUND( AVG( a.s_score ), 2 ) AS avg_score 
  5. FROM
  6.     student b
  7.     LEFT JOIN score a ON b.s_id = a.s_id 
  8. GROUP BY
  9.     b.s_id,
  10.     b.s_name 
  11. HAVING
  12.     avg_score < 60 UNION
  13. SELECT
  14.     a.s_id,
  15.     a.s_name,
  16.     0 AS avg_score 
  17. FROM
  18.     student a 
  19. WHERE
  20.     a.s_id NOT IN ( SELECT DISTINCT s_id FROM score );

image.png
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

  1. SELECT
  2.     a.s_id,
  3.     a.s_name,
  4.     count( b.c_id ) AS sum_course,
  5.     sum( b.s_score ) AS sum_score 
  6. FROM
  7.     student a
  8.     LEFT JOIN score b ON a.s_id = b.s_id 
  9. GROUP BY
  10.     a.s_id,
  11.     a.s_name;

image.png
6、查询”李”姓老师的数量 

  1. SELECT
  2.     COUNT( t.t_id ) 
  3. FROM
  4.     teacher t 
  5. WHERE
  6.     t.t_name LIKE '李%'

7、查询学过”张三”老师授课的同学的信息 

  1. SELECT
  2.     a.* 
  3. FROM
  4.     student a
  5.     JOIN score b ON a.s_id = b.s_id 
  6. WHERE
  7.     b.c_id IN (
  8.     SELECT
  9.         c_id 
  10.     FROM
  11.         course 
  12.     WHERE
  13.         t_id =(
  14.         SELECT
  15.             t_id 
  16.         FROM
  17.             teacher 
  18.         WHERE
  19.         t_name = '张三' 
  20.     ));

image.png

8、查询没学过”张三”老师授课的同学的信息

  1. SELECT
  2.     * 
  3. FROM
  4.     student c 
  5. WHERE
  6.     c.s_id NOT IN (
  7.     SELECT
  8.         a.s_id 
  9.     FROM
  10.         student a
  11.         JOIN score b ON a.s_id = b.s_id 
  12. WHERE
  13.     b.c_id IN ( SELECT a.c_id FROM course a JOIN teacher b ON a.t_id = b.t_id WHERE t_name = '张三' ));

9、查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息

  1. SELECT
  2.     a.* 
  3. FROM
  4.     student a,
  5.     score b,
  6.     score c 
  7. WHERE
  8.     a.s_id = b.s_id 
  9.     AND a.s_id = c.s_id 
  10.     AND b.c_id = '01' 
  11.     AND c.c_id = '02';

10、查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息