MySQL -- 连接查询

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

1.为什么使用连接查询

1. 数据规范化：

   • 数据库设计时通常会将数据拆分到不同的表中，以减少数据冗余和提高数据一致性。这种方法称为规范化。
   • 例如，将用户信息存储在一个表中，将订单信息存储在另一个表中。这时，如果你需要获取某用户的订单信息，就需要使用连接查询将这两个表的数据组合在一起。

2. 提高查询效率：

   • 通过使用连接查询，可以减少重复的数据存储，优化数据的管理和查询效率。
   • 比如，一个表中存储用户ID，另一个表中存储用户详细信息，查询时只需要连接用户ID和详细信息表即可获取完整信息，而不需要在单个表中存储冗余数据。

3. 复杂查询需求：

   • 在实际应用中，很多查询需求都涉及多个表的数据。例如，报表生成、数据分析、统计等都需要从多个表中提取相关数据。
   • 连接查询可以实现这些复杂的查询需求，通过合并相关表的数据来满足业务逻辑。

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_id
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id
UNION
SELECT users.username, orders.order_id
FROM users
RIGHT JOIN orders ON users.user_id = orders.user_id;
users. Username

3.测试连接查询

1. student 表：

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

1. course 表：

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

1. 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连接条件里面，效果是一样的

select a.uid,a.name,a.age,a.sex,c.score from student a
inner join exame c on a.uid=c.uid where c.uid=1 and c.cid=2;

select b.cid,b.cname,b.credit from course b where b.cid=2;

 

select a.uid,a.name,a.age,a.sex,b.cid,b.cname,b.credit,c.score
from exame c
inner join student a on c.uid=a.uid
inner join course b on c.cid=b.cid
where c.uid=1 and c.cid=2;

select a.uid,a.name,a.age,a.sex,b.cid,b.cname,b.credit,c.score
from exame c
inner join student a on c.uid=a.uid
inner join course b on c.cid=b.cid
where c.cid=2 and c.score>=90.0;

select b.cid,b.cname,b.credit,count(*)
from exame c
inner join course b on c.cid=b.cid
where c.score>=90.0
group by c.cid
having c.cid=2;//分组以后的过滤

select b.cid,b.cname,b.credit,count(*) cnt
from exame c
inner join course b on c.cid=b.cid
where c.score>=90.0
group by c.cid
order by cnt;

外连接查询

左连接查询

SELECT a.属性名列表, b.属性名列表 FROM table_name1 a LEFT [OUTER] JOIN table_name2 b on a.id = b.id;

// 把left这边的表所有的数据显示出来，在右表中不存在相应数据，则显示NULL
select a.* from User a left outer join Orderlist b on a.uid=b.uid where
a.orderid is null;

 例子：

select a.*,b.* from student a left join exame b on a.uid=b.uid;

找出没有考过试的

select a.*,b.* from student a left join exame b on a.uid=b.uid where b.cid is null;

内连接结果如下

左连接结果如下

 问题，为啥左连接没有把左表全部信息显示，左连接和内连接结果一样

若把where条件放到连接条件on后面

原因分析：

先用b.cid把b表过滤下

 这个内连接和外连接毫无区别，是一样的。所以外连接要把过滤条件写到on中

外连接查不存在的场景，还带有一定的限制条件，限制条件加到on的连接条件后面，where的过滤条件后面写null判空。

右连接查询

SELECT a.属性名列表, b.属性名列表 FROM table_name1 a LEFT [OUTER] JOIN table_name2 b on a.id = b.id;

// 把right这边的表所有的数据显示出来，在左表中不存在相应数据，则显示NULL
select a.* from User a right outer join Orderlist b on a.uid=b.uid where
b.orderid is null;

select * from student where uid not in (select distinct uid from exame);

 select distinct uid from exame-》会产生一张中间表存储结果供外面的sql来查询

not in 对于索引的命中并不高