MySQL多表查询实验
1.数据准备
-- 以下语句用于创建 students 表，该表存储学生的基本信息
-- 定义表名为 students
CREATE TABLE students (-- 定义学生的唯一标识符，类型为整数，作为主键，且支持自动递增student_id INT PRIMARY KEY AUTO_INCREMENT,-- 定义学生的姓名，类型为可变长度字符串，最大长度为 50student_name VARCHAR(50),-- 定义学生的年龄，类型为整数age INT
);-- 以下语句用于创建 courses 表，该表存储课程的相关信息以及选课的学生信息
-- 定义表名为 courses
CREATE TABLE courses (-- 定义课程的唯一标识符，类型为整数，作为主键，且支持自动递增course_id INT PRIMARY KEY AUTO_INCREMENT,-- 定义课程的名称，类型为可变长度字符串，最大长度为 50course_name VARCHAR(50),-- 定义选课学生的标识符，类型为整数，用于关联 students 表中的学生student_id INT,-- 定义外键约束，将 student_id 字段关联到 students 表的 student_id 字段FOREIGN KEY (student_id) REFERENCES students(student_id)
);-- 以下语句用于向 students 表中插入学生的基本信息
-- 插入学生的姓名和年龄数据
INSERT INTO students (student_name, age) VALUES
-- 插入名为 Alice，年龄为 20 的学生信息
('Alice', 20),
-- 插入名为 Bob，年龄为 21 的学生信息
('Bob', 21),
-- 插入名为 Charlie，年龄为 22 的学生信息
('Charlie', 22);-- 以下语句用于向 courses 表中插入课程信息以及选课的学生信息
-- 插入课程名称和选课学生的标识符
INSERT INTO courses (course_name, student_id) VALUES
-- 插入课程名为 Math，选课学生标识符为 1 的信息
('Math', 1),
-- 插入课程名为 Physics，选课学生标识符为 1 的信息
('Physics', 1),
-- 插入课程名为 Chemistry，选课学生标识符为 2 的信息
('Chemistry', 2),
-- 插入课程名为 Biology，选课学生标识符为 3 的信息
('Biology', 3); 多表查询
多表查询用于从多个表中获取相关的数据。常见的多表查询类型有内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全外连接（FULL OUTER JOIN，MySQL 不直接支持，可通过 UNION 模拟）。
1.内连接（INNER JOIN）
内连接返回两个表中匹配的行。例如，我们要查询每个学生所选的课程：
SELECT students.student_name, courses.course_name
FROM students
INNER JOIN courses ON students.student_id = courses.student_id;
这个查询会返回 students 表和 courses 表中 student_id 匹配的行，即每个学生所选的课程信息。
2.左连接（LEFT JOIN）
左连接返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则返回 NULL。例如，我们要查询所有学生及其所选的课程，如果学生没有选课，课程信息显示为 NULL：
SELECT students.student_name, courses.course_name
FROM students
LEFT JOIN courses ON students.student_id = courses.student_id;
3.索引
索引是一种数据结构，用于提高数据库查询的速度。在 MySQL 中，常见的索引类型有主键索引、唯一索引、普通索引等。
3.1创建索引
以下是创建索引的示例：
1.普通索引
如果你经常根据 students 表的 student_name 字段进行查询，可以为该字段创建一个普通索引：
CREATE INDEX idx_student_name ON students (student_name);
测试:
mysql> create index cl_na_in on class1(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings:
2.唯一索引
如果你希望 courses 表的 course_name 字段的值是唯一的，可以为该字段创建一个唯一索引：
CREATE UNIQUE INDEX idx_course_name ON courses (course_name);
3.查看索引
可以使用以下语句查看表的索引信息：
SHOW INDEX FROM students;
4.删除索引
如果不再需要某个索引，可以使用以下语句删除它：
DROP INDEX idx_student_name ON students;
5.查询测试索引
SELECT * FROM stux.class1 WHERE name = 'zhangsan';
6使用 EXPLAIN 分析查询语句
在你原本的查询语句前加上 EXPLAIN 关键字，例如，你之前想要查询 name 为 ‘zhangsan’ 的记录，可执行如下语句：
EXPLAIN SELECT * FROM stux.class1 WHERE name = 'zhangsan';
执行该语句后，MySQL 会返回一个结果集，其中包含了查询执行计划的相关信息
---------------------------------------------
案例实操测试:
mysql> select * from crs;
+------+---------+-------+
| c_id | c_name | st_id |
+------+---------+-------+
| 1 | jsj | 1 |
| 2 | english | 1 |
| 3 | sx | 2 |
| 4 | ty | 3 |
+------+---------+-------+
4 rows in set (0.01 sec)mysql> select * from class1;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 20 |
| 2 | wanger | 21 |
| 3 | xxw | 25 |
+----+----------+------+
3 rows in set (0.00 sec)mysql> select class1.name,crs.c_name-> from class1-> INNER JOIN crs ON class1.id = crs.st_id;
+----------+---------+
| name | c_name |
+----------+---------+
| zhangsan | jsj |
| zhangsan | english |
| wanger | sx |
| xxw | ty |
+----------+---------+
4 rows in set (0.00 sec)mysql> select class1.name,crs.c_name from class1 LEFT JOIN
crs ON class1.id = crs.st_id;
+----------+---------+
| name | c_name |
+----------+---------+
| zhangsan | jsj |
| zhangsan | english |
| wanger | sx |
| xxw | ty |
+----------+---------+
4 rows in set (0.00 sec)mysql> insert into class1(name,age) values('zhaoyun',98);
Query OK, 1 row affected (0.00 sec)mysql> select class1.name,crs.c_name from class1 LEFT JOIN
crs ON class1.id = crs.st_id;
+----------+---------+
| name | c_name |
+----------+---------+
| zhangsan | jsj |
| zhangsan | english |
| wanger | sx |
| xxw | ty |
| zhaoyun | NULL |
+----------+---------+
5 rows in set (0.00 sec)