mysql 练习题

表与题均来源自网络

1. 建表语句

--建表
--学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

2. 语句练习

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

SELECT 
  a.*,
  b.`s_score` AS 语文,
  c.`s_score` AS 数学 
FROM
  student a,
  score b,
  score c
WHERE a.`s_id` = b.`s_id` 
  AND a.`s_id` = c.`s_id` 
  AND b.`c_id` = '01' 
  AND c.`c_id` = '02' 
  AND b.`s_score` > c.`s_score` ;

SELECT 
  a.*,
  b.`s_score` 语文,
  c.`s_score` 数学 
FROM
  student a 
  LEFT JOIN score b 
    ON a.`s_id` = b.`s_id` 
    AND b.`c_id` = '01' 
  LEFT JOIN score c 
    ON a.`s_id` = c.`s_id` 
    AND c.`c_id` = '02' 
WHERE IFNULL(b.`s_score`,0) > IFNULL(c.`s_score`,0) ;

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

SELECT 
  a.`s_id`,
  a.`s_name`,
  ROUND(AVG(b.`s_score`),2) 平均成绩 
FROM
  student a 
  LEFT JOIN score b 
    ON a.`s_id` = b.`s_id` 
GROUP BY a.`s_id` 
HAVING AVG(b.`s_score`) >= 60 ;

3. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

包括有成绩和无成绩的

SELECT 
  a.`s_id`,
  a.`s_name`,
  ROUND(AVG(b.`s_score`)) avg_score 
FROM
  student a 
  LEFT JOIN score b 
    ON a.`s_id` = b.`s_id` 
GROUP BY a.`s_id` 
HAVING AVG(b.`s_score`) < 60 
UNION
SELECT 
  a.`s_id`,
  a.`s_name`,
  0 AS avg_score 
FROM
  student a 
WHERE a.`s_id` NOT IN 
  (SELECT DISTINCT 
    s_id 
  FROM
    score) ;

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

SELECT 
  a.`s_id`,
  a.`s_name`,
  COUNT(b.`c_id`) sum_course,
  SUM(b.`s_score`) sum_score 
FROM
  student a 
  LEFT JOIN score b 
    ON a.`s_id` = b.`s_id` 
GROUP BY a.`s_id`;

5. 查询李姓老师的数量

SELECT 
  COUNT(t_id) 
FROM
  teacher 
WHERE t_name LIKE '李%' ;

6. 查询上过张三老师的课的同学的信息

SELECT 
  a.* 
FROM
  student a 
  LEFT JOIN score b 
    ON a.`s_id` = b.`s_id` 
  LEFT JOIN course c 
    ON b.`c_id` = c.`c_id` 
  LEFT JOIN teacher d 
    ON c.`t_id` = d.`t_id` 
WHERE d.`t_name` = '张三' ;

SELECT 
  a.* 
FROM
  student a 
  LEFT JOIN score b 
    ON a.`s_id` = b.`s_id` 
WHERE b.`c_id` IN 
  (SELECT 
    c_id 
  FROM
    course 
  WHERE t_id IN 
    (SELECT 
      t_id 
    FROM
      teacher 
    WHERE t_name = '张三')) ;

7. 查询没学过张三老师课程的同学的信息

SELECT 
  * 
FROM
  student a 
WHERE a.`s_id` NOT IN 
  (SELECT 
    a.`s_id` 
  FROM
    student a 
    JOIN score b 
      ON a.`s_id` = b.`s_id` 
  WHERE b.`c_id` IN 
    (SELECT 
      c.`c_id` 
    FROM
      course c 
      LEFT JOIN teacher d 
        ON c.`t_id` = d.`t_id` 
    WHERE d.`t_name` = '张三')) ;

8. 查询学过编号为01且也学过编号为02的课程的同学的信息

SELECT 
  a.* 
FROM
  student a,
  score b,
  score c 
WHERE a.`s_id` = b.`s_id` 
  AND a.`s_id` = c.`s_id` 
  AND b.`c_id` = '01' 
  AND c.`c_id` = '02' ;

SELECT 
  a.* 
FROM
  student a 
  INNER JOIN score b 
    ON a.`s_id` = b.`s_id` 
GROUP BY a.`s_id` 
HAVING SUM(
    IF(
      b.`c_id` = '01' 
      OR b.`c_id` = '02',
      1,
      0
    )
  ) > 1 ;

9. 查询学过编号为01但是没学过编号为02的课程的同学的信息

SELECT 
  a.* 
FROM
  student a 
WHERE a.`s_id` IN 
  (SELECT 
    s_id 
  FROM
    score 
  WHERE c_id = '01') 
  AND a.`s_id` NOT IN 
  (SELECT 
    s_id 
  FROM
    score 
  WHERE c_id = '02') ;

10. 查询没有学过全部课程的同学的信息

SELECT 
  a.* 
FROM
  student a 
  LEFT JOIN score b 
    ON a.`s_id` = b.`s_id` 
GROUP BY a.`s_id` 
HAVING COUNT(b.`c_id`) < 
  (SELECT 
    COUNT(*) 
  FROM
    course) ;

11. 查询至少有一门课与学号为‘01’的同学所学相同的同学的信息

SELECT 
  a.* 
FROM
  student a 
WHERE a.`s_id` IN 
  (SELECT DISTINCT 
    b.`s_id` 
  FROM
    score b 
  WHERE b.`c_id` IN 
    (SELECT 
      c.`c_id` 
    FROM
      score c 
    WHERE c.`s_id` = '01'));

12.  查询和‘01’号同学学习课程完全相同的其他同学的信息

SELECT 
  c.* 
FROM
  (SELECT 
    s_id,
    GROUP_CONCAT(c_id ORDER BY c_id) 总课程 
  FROM
    score 
  WHERE s_id != '01' 
  GROUP BY s_id) a 
  JOIN 
    (SELECT 
      GROUP_CONCAT(c_id ORDER BY c_id) 总课程 
    FROM
      score 
    WHERE s_id = '01' 
    GROUP BY s_id) b 
    ON a.总课程 = b.总课程 
  JOIN student c 
    ON a.s_id = c.s_id ;