MySQL -- 连接查询

科技颠覆者 · 2024-8-31 10:39:59

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？立即注册

x

MySQL利用连接查询（JOIN）是为了从多个相干表中获取数据。连接查询是一种强盛且常用的操作，可以根据某些条件将两张或多张表中的数据组合在一起，返回一个联合结果集。
1.为什么利用连接查询

数据规范化：
- 数据库设计时通常会将数据拆分到差别的表中，以淘汰数据冗余和提高数据同等性。这种方法称为规范化。
- 例如，将用户信息存储在一个表中，将订单信息存储在另一个表中。这时，假如你需要获取某用户的订单信息，就需要利用连接查询将这两个表的数据组合在一起。
提高查询效率：
- 通过利用连接查询，可以淘汰重复的数据存储，优化数据的管理和查询效率。
- 好比，一个表中存储用户ID，另一个表中存储用户具体信息，查询时只需要连接用户ID和具体信息表即可获取完整信息，而不需要在单个表中存储冗余数据。
复杂查询需求：
- 在实际应用中，很多查询需求都涉及多个表的数据。例如，报表生成、数据分析、统计等都需要从多个表中提取相干数据。
- 连接查询可以实现这些复杂的查询需求，通过归并相干表的数据来满足业务逻辑。

2.主要范例的连接查询

INNER JOIN：

仅返回两个表中满足连接条件的记录。

SELECT users.username, orders.order_id
FROM users
INNER JOIN orders ON users.user_id = orders.user_id;

复制代码

说明：这将返回所有用户和他们的订单，条件是用户和订单在两个表中都有匹配。

LEFT JOIN (或 LEFT OUTER JOIN)：

返回左表中的所有记录，即使右表中没有匹配的记录。假如右表没有匹配，则结果为NULL。

SELECT users.username, orders.order_id
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id;

复制代码

说明：这将返回所有用户以及他们的订单。假如某个用户没有订单，则相应的订单ID将为NULL。

RIGHT JOIN (或 RIGHT OUTER JOIN)：

返回右表中的所有记录，即使左表中没有匹配的记录。假如左表没有匹配，则结果为NULL。

SELECT users.username, orders.order_id
FROM users
RIGHT JOIN orders ON users.user_id = orders.user_id;
users. Username

复制代码

说明：这将返回所有订单以及对应的用户。假如某个订单没有匹配的用户，则相应的用户名将为NULL。

FULL JOIN (或 FULL OUTER JOIN)：

返回左表和右表中的所有记录，假如没有匹配则返回NULL。MySQL不直接支持FULL JOIN，可以通过UNION实现。

SELECT users.username, orders.order_idFROM usersLEFT JOIN orders ON users.user_id = orders.user_idUNIONSELECT users.username, orders.order_id
FROM users
RIGHT JOIN orders ON users.user_id = orders.user_id;
users. Username

复制代码

3.测试连接查询

student 表：

存储学生的基本信息。
uid：学生的唯一标识（主键），自动递增。
name：学生姓名。
age：学生年事。
sex：学生性别，利用枚举范例，值可以是'M'或'W'。

course 表：

存储课程的基本信息。
cid：课程的唯一标识（主键），自动递增。
cname：课程名称。
credit：课程学分。

exame 表：

存储考试信息，包括学生、课程和考试结果。
uid：学生ID，对应student表中的uid。
cid：课程ID，对应course表中的cid。
time：考试时间。
score：考试结果。
PRIMARY KEY(uid,cid)：联合主键，确保每个学生在每门课程中只有一个结果记录。

CREATE TABLE student(
uid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age TINYINT UNSIGNED NOT NULL,
sex ENUM('M','W') NOT NULL);
CREATE TABLE course(
cid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
cname VARCHAR(50) NOT NULL,
credit TINYINT UNSIGNED NOT NULL);
CREATE TABLE exame(
uid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
time DATE NOT NULL,
score FLOAT NOT NULL,
PRIMARY KEY(uid,cid));

复制代码

插入一些数据

-- 插入数据到 student 表
INSERT INTO student (name, age, sex) VALUES
('Alice', 20, 'W'),
('Bob', 21, 'M'),
('Charlie', 22, 'M'),
('David', 20, 'M'),
('Eve', 21, 'W');
-- 插入数据到 course 表
INSERT INTO course (cname, credit) VALUES
('Mathematics', 3),
('Physics', 4),
('Chemistry', 3),
('Biology', 2),
('Computer Science', 3);
-- 插入数据到 exame 表
INSERT INTO exame (uid, cid, time, score) VALUES
(1, 1, '2024-01-10', 85.5),
(1, 2, '2024-01-11', 90.0),
(2, 1, '2024-01-9', 88.0),
(2, 3, '2024-01-10', 92.5),
(3, 4, '2024-01-12', 75.0),
(3, 5, '2024-01-9', 80.0),
(4, 2, '2024-01-12', 85.0),
(4, 3, '2024-01-12', 87.5),
(5, 1, '2024-01-11', 89.0),
(5, 5, '2024-01-11', 95.0);

复制代码

内连接

SELECT a.属性名1,a.属性名2,...,b,属性名1,b.属性名2... FROM table_name1 a inner join table_name2 b on a.id = b.id where a.属性名满足某些条件;

预置条件：uid:1 cid:2
select score from exame where uid=1 and cid=2;
select a.uid,a.name,a.age,a.sex from student a where a.uid=1;
select c.score from exame c where c.uid=1 and c.cid=2;

复制代码

连接两张表查询
// on a.uid=c.uid 区分大表和小表，按照数据量来区分，小表永远是整表扫描，然后去大表搜索 // 从student小表中取出所有的a.uid，然后拿着这些uid去exame大表中搜索 // 对于inner join内连接，过滤条件写在where的后面和on连接条件里面，结果是一样的