MySQL实验六：多表连接和子查询复习

在数据库管理和操作中，多表连接和子查询是两种非常强大的技术，它们可以帮助我们从多个表中提取和处理复杂的数据。以下是一些实验性的操作，包括创建数据库和表、插入数据以及执行各种查询。

1. 创建数据库和表

首先，我们需要创建一个名为 student 的数据库，并在其中创建三个表：student_info、curriculum 和 grade。

USE student;
SHOW DATABASES;

CREATE TABLE student_info (
    学号 CHAR(4) NOT NULL PRIMARY KEY,
    姓名 CHAR(8) NOT NULL,
    性别 CHAR(2),
    出生日期 DATE,
    家庭住址 VARCHAR(50)
);

CREATE TABLE curriculum (
    课程编号 CHAR(4) NOT NULL PRIMARY KEY,
    课程名称 VARCHAR(50),
    学分 INT
);

CREATE TABLE grade (
    学号 CHAR(4) NOT NULL,
    课程编号 CHAR(4) NOT NULL,
    分数 INT,
    PRIMARY KEY (学号, 课程编号)
);

2. 插入数据

接下来，我们向这三个表中插入一些示例数据。

-- 插入 student_info 表数据
INSERT INTO student_info (学号, 姓名, 性别, 出生日期, 家庭住址) VALUES
('0001', '张青平', '男', '2000-10-01', '衡阳市东风路77号'),
('0002', '刘东阳', '男', '1998-12-09', '东阳市八一北路33号'),
('0003', '马晓夏', '女', '1995-05-12', '长岭市五一路763号'),
('0004', '钱忠理', '男', '1994-09-23', '滨海市洞庭大道279号'),
('0005', '孙海洋', '男', '1995-04-03', '长岛市解放路27号'),
('0006', '郭小斌', '男', '1997-11-10', '南山市红旗路113号'),
('0007', '肖月玲', '女', '1996-12-07', '东方市南京路11号'),
('0008', '张玲珑', '女', '1997-12-24', '滨江市新建路97号');

-- 插入 curriculum 表数据
INSERT INTO curriculum (课程编号, 课程名称, 学分) VALUES
('0001', '计算机应用基础', 2),
('0002', 'C语言程序设计', 2),
('0003', '数据库原理及应用', 2),
('0004', '英语', 4),
('0005', '高等数学', 4);

-- 插入 grade 表数据
INSERT INTO grade (学号, 课程编号, 分数) VALUES
('0001', '0001', 80),
('0001', '0002', 91),
('0001', '0003', 88),
('0001', '0004', 85),
('0001', '0005', 77),
('0002', '0001', 73),
('0002', '0002', 68),
('0002', '0003', 80),
('0002', '0004', 79),
('0002', '0005', 73),
('0003', '0001', 84),
('0003', '0002', 92),
('0003', '0003', 81),
('0003', '0004', 82),
('0003', '0005', 75);

3. 执行查询

实验六：多表连接和子查询

在本实验中，我们将通过一系列SQL查询来探索和操作student数据库中的三个表：student_info、curriculum和grade。这些查询将帮助我们理解如何使用SQL进行数据检索、过滤和连接操作。

1. 查询“C语言程序设计”课程分数大于该课程平均分数的学生信息

SQL代码：

SELECT s.学号, s.姓名, g.分数
FROM student_info s
INNER JOIN grade g ON s.学号 = g.学号
INNER JOIN curriculum c ON g.课程编号 = c.课程编号
WHERE c.课程名称 = 'C语言程序设计'
AND g.分数 > (SELECT AVG(g2.分数) FROM grade g2 WHERE g2.课程编号 = '0002');

解释：
此查询首先通过内连接将学生信息、成绩和课程信息关联起来。然后，它使用子查询计算“C语言程序设计”课程的平均分数，并筛选出分数高于这个平均值的学生。

2. 使用左外连接查询所有学生的姓名及选修的课程名称和分数

SQL代码：

SELECT s.姓名, c.课程名称, g.分数
FROM student_info s
LEFT JOIN grade g ON s.学号 = g.学号
LEFT JOIN curriculum c ON g.课程编号 = c.课程编号;

解释：
这个查询使用左外连接来确保即使学生没有选修任何课程，他们的姓名也会被显示出来。通过连接学生信息、成绩和课程信息，我们可以获取每个学生的课程名称和分数。

3. 使用子查询查找张青平同学选修的课程名称

SQL代码：

SELECT c.课程名称
FROM curriculum c
WHERE c.课程编号 IN (
    SELECT g.课程编号
    FROM grade g
    WHERE g.学号 = (
        SELECT s.学号
        FROM student_info s
        WHERE s.姓名 = '张青平'
    )
);

解释：
这个查询首先通过子查询找到张青平的学号，然后在另一个子查询中找到他选修的所有课程编号，最后通过这些课程编号获取课程名称。

4. 统计0001号课程最高分人数

SQL代码：

SELECT COUNT(*) AS 最高分人数
FROM grade g1
WHERE g1.分数 = (
    SELECT MAX(g2.分数)
    FROM grade g2
    WHERE g2.课程编号 = '0001'
);

解释：
这个查询使用子查询来确定课程0001的最高分数，然后统计有多少学生达到了这个最高分数。

5. 查询选修0001号课程的学生姓名，并按分数从大到小排列

SQL代码：

SELECT s.姓名
FROM student_info s
INNER JOIN grade g ON s.学号 = g.学号
WHERE g.课程编号 = '0001'
ORDER BY g.分数 DESC;

解释：
这个查询通过内连接将学生信息和成绩信息关联起来，筛选出选修了课程0001的学生，然后按照分数从高到低排序。

实验思考

1. 在查询的FROM子句中实现表与表之间的连接有几种方式？对应的关键字分别是什么？

在SQL中，表与表之间的连接主要有两种方式：

• 内连接（INNER JOIN）：这是最常用的连接类型，它返回两个表中匹配的行。只有当两个表中都存在匹配的行时，内连接才会返回结果。关键字是INNER JOIN。

  SELECT columns
  FROM table1
  INNER JOIN table2 ON table1.common_field = table2.common_field;
  

• 外连接（OUTER JOIN）：外连接可以进一步分为左外连接（LEFT OUTER JOIN 或 LEFT JOIN）和右外连接（RIGHT OUTER JOIN 或 RIGHT JOIN）。外连接返回左表或右表的所有行，即使右表或左表中没有匹配的行，也会返回NULL值。关键字是LEFT JOIN或LEFT OUTER JOIN和RIGHT JOIN或RIGHT OUTER JOIN。

  -- 左外连接
  SELECT columns
  FROM table1
  LEFT JOIN table2 ON table1.common_field = table2.common_field;
  
  -- 右外连接
  SELECT columns
  FROM table1
  RIGHT JOIN table2 ON table1.common_field = table2.common_field;
  

2. 内连接与外连接有什么区别？

• 内连接（INNER JOIN）：只返回两个表中具有匹配值的行。如果一个表中的行在另一个表中没有匹配的行，则不包括在结果集中。

• 外连接（OUTER JOIN）：返回左表或右表的所有行，无论另一个表中是否有匹配的行。如果左表中的行在右表中没有匹配的行，那么右表的列将显示为NULL。同样，如果右表中的行在左表中没有匹配的行，那么左表的列将显示为NULL。

3. “=”与IN在什么情况下作用相同？

“=”与IN在以下情况下作用相同：

• 当子查询返回单个值时，它们的作用相同。例如，如果子查询保证只返回一个值，那么使用“=”或IN都可以达到相同的效果。

  -- 使用“=”
  SELECT * FROM table WHERE column = (SELECT single_value FROM subquery);
  
  -- 使用IN
  SELECT * FROM table WHERE column IN (SELECT single_value FROM subquery);
  

• 但是，IN通常用于子查询返回多个值的情况，而“=”则用于比较单个值。

  -- 使用IN，因为子查询可能返回多个值
  SELECT * FROM table WHERE column IN (SELECT multiple_values FROM subquery);
  

在实际应用中，选择使用“=”还是IN取决于子查询返回的结果集。如果子查询可能返回多个值，那么应该使用IN。如果子查询返回单个值，那么“=”和IN都可以使用，但IN在语义上更清晰，因为它明确表示列的值可以是子查询结果集中的任何一个值。