MySQL 经典练习

创建数据库和表

数据库

drop database if exists mysql_test cascade;
create database mysql_test;
use mysql_test;

学生表 student

创建学生表 student

create table student (
	s_id int,
	s_name varchar(8),
	s_birth date,
	s_sex varchar(4)
);

插入学生数据

insert into student values
(1,'赵雷','1990-01-01','男'),
(2,'钱电','1990-12-21','男'),
(3,'孙风','1990-05-20','男'),
(4,'李云','1990-08-06','男'),
(5,'周梅','1991-12-01','女'),
(6,'吴兰','1992-03-01','女'),
(7,'郑竹','1989-07-01','女'),
(8,'王菊','1990-01-20','女');

课程表 course

创建课程表 course

create table course (
	c_id int,
	c_name varchar(8),
	t_id int
);

插入课程数据

insert into course values
(1,'语文',2),
(2,'数学',1),
(3,'英语',3);

教师表 teacher

创建教师表 teacher

create table teacher (
	t_id int,
	t_name varchar(8)
);

插入教师数据

insert into teacher values
(1,'张三'),
(2,'李四'),
(3,'王五');

成绩表 score

创建成绩表 score

create table score (
	s_id int,
	c_id int,
	s_score int
);

插入成绩数据

insert into score values
(1,1,80),
(1,2,90),
(1,3,99),
(2,1,70),
(2,2,60),
(2,3,65),
(3,1,80),
(3,2,80),
(3,3,80),
(4,1,50),
(4,2,30),
(4,3,40),
(5,1,76),
(5,2,87),
(6,1,31),
(6,3,34),
(7,2,89),
(7,3,98);

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

-- 答案一：
SELECT
  s.s_name,
  s.s_id 
FROM
  student s,
  score s2 
WHERE
  s.s_id = s2.s_id 
  AND s2.c_id IN ( SELECT s3.c_id FROM student s2, score s3 WHERE s2.s_id = s3.s_id AND s2.s_id = 1 ) 
  AND s.s_id <> '01' 
GROUP BY
  s.s_name,
  s.s_id;

-- 答案二：
SELECT
  * 
FROM
  student 
WHERE
  s_id IN (
  SELECT DISTINCT
    s.s_id 
  FROM
    score s
    INNER JOIN ( SELECT s1.c_id FROM score s1 WHERE s1.s_id = 1 ) t ON s.c_id = t.c_id 
  ) 
  AND s_id <> '01';

-- 答案三：
SELECT DISTINCT
  st.* 
FROM
  student st
  INNER JOIN score sc ON st.s_id = sc.s_id
  INNER JOIN ( SELECT c_id FROM score WHERE s_id = '01' ) t ON sc.c_id = t.c_id 
WHERE
  st.s_id <> '01';

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    1 | 赵雷   | 1990-01-01 | 男    |
|    2 | 钱电   | 1990-12-21 | 男    |
|    3 | 孙风   | 1990-05-20 | 男    |
|    4 | 李云   | 1990-08-06 | 男    |
|    5 | 周梅   | 1991-12-01 | 女    |
|    6 | 吴兰   | 1992-03-01 | 女    |
|    7 | 郑竹   | 1989-07-01 | 女    |
+------+--------+------------+-------+

 2.查询和"01"号的同学学习的课程完全相同的其他同学的信息

-- 答案一:
SELECT
  * 
FROM
  student 
WHERE
  s_id IN (
  SELECT 
    s.s_id 
  FROM
    score s
    INNER JOIN ( SELECT s1.c_id FROM score s1 WHERE s1.s_id = 1 ) t ON s.c_id = t.c_id 
  GROUP BY s.s_id 
  HAVING COUNT(1) = (SELECT COUNT(1) FROM score s1 WHERE s1.s_id = 1 )
  ) 
  AND s_id <> '01';

-- 答案二:

SELECT 
  st.s_name,st.s_birth,st.s_sex 
FROM
  student st
  INNER JOIN score sc ON st.s_id = sc.s_id
  INNER JOIN ( SELECT c_id FROM score WHERE s_id = '01' ) t ON sc.c_id = t.c_id 
WHERE
  st.s_id <> '01'
GROUP BY  st.s_name,st.s_birth,st.s_sex 
  HAVING COUNT(st.s_name) = (SELECT COUNT(1) FROM score s1 WHERE s1.s_id = 1 );

-- 答案三:
SELECT
  s.s_name,
  s.s_id 
FROM
  student s,
  score s2 
WHERE
  s.s_id = s2.s_id 
  AND s2.c_id IN ( SELECT s3.c_id FROM student s2, score s3 WHERE s2.s_id = s3.s_id AND s2.s_id = 1 ) 
  AND s.s_id <> '01' 
GROUP BY
  s.s_name,
  s.s_id 
HAVING
  COUNT( s.s_id ) = ( SELECT COUNT(*) FROM score t WHERE t.s_id = 1 );

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    2 | 钱电   | 1990-12-21 | 男    |
|    3 | 孙风   | 1990-05-20 | 男    |
|    4 | 李云   | 1990-08-06 | 男    |
+------+--------+------------+-------+

题目借鉴：https://blog.csdn.net/GodSuzzZ/article/details/106930311