MySQL 基础（二）

原创

落幕

发布于 2025-05-27 17:45:04

1390

MySQL 基础（二）

1.多表关联查询

1.1 表之间的关系

1. 关联关系

一对一关系（One To One ）
一对多关系（One To Many）
多对多关系（Many To Many）

2. 一对多关系

产品类别表（tb_category）

cid(编号)	cname(产品类别名称)
1	手机
2	电脑

产品表（ tb_product）

id(编号)	name(产品名称)	price(产品价格)	cid(产品所属类别编号)
1	华为 Mate60	4528	1
2	联想小新	4999	2
3	Redmi K70	1902	1
4	联想拯救者	24999	2

3. 多对多关系

学生表（tb_student）

id(学生编号)	name(学生名称)
x_1	张三
x_2	李四

课程表（tb_course）

id(课程编号)	name 课程名称)
k_1	数学
k_2	语文

选修课表（tb_electives）

id(课程编号)	x_id(学生编号)	k_id(课程编号)
1	x_1	k_1
2	x_1	k_2
3	x_2	k_1
4	x_2	k_2

多对多关系中一定有一个中间关系表，记录两张表之间的数据联系

1.2. 外键与外键约束

外键（Foreign Key）的定义

外键是数据库表中的一个字段（或字段组合），用于建立两个表之间的逻辑关联。它指向另一个表的主键（或唯一键），确保引用的数据在关联表中存在，从而维护数据的完整性和一致性。

作用

引用完整性
级联操作
防止孤立数据

1.创建外键约束

# 创建表时定义外键
constraint  foreign key(外键字段) references 主表名(主键)

# 创建产品类别表
CREATE TABLE tb_category (
  cid VARCHAR(50) PRIMARY KEY, -- 分类id（约束唯一）
  name VARCHAR(100) --  产品类别名称
);
#添加tb_category（主表）数据
insert into tb_category values (1, '手机');  -- 1
insert into tb_category values (2, '电脑');  -- 2

# 数据
+-----+--------+
| cid | name   |
+-----+--------+
| 1   | 手机 |
| 2   | 电脑 |
+-----+--------+

# 创建商品表
CREATE TABLE tb_product (
  id VARCHAR(50) PRIMARY KEY, -- 产品id（约束唯一）
  name VARCHAR(100),--  产品名称
  price DOUBLE,
  cid VARCHAR(50) , -- 分类id 外键

   # constraint 约束
   # references 引用
   # tb_category 是主表
    constraint  foreign key(cid) references  tb_category(cid) -- 外键约束
);
# tb_product添加数据
insert into tb_product values (1, '雷神鼠标', 200, 3);  -- 报错

/**
*ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`api_development`.`tb_product`, CONSTRAINT `tb_product_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `tb_category` (`cid`))
**/

# tb_product添加数据
insert into tb_product values (1, '华为Mate 60', 4528, 1);-- 成功
insert into tb_product values (2, '联想小新', 4999, 2);-- 成功

# 查询数据
mysql> select * from tb_product;
+----+---------------+-------+------+
| id | name          | price | cid  |
+----+---------------+-------+------+
| 1  | 华为Mate 60 |  4528 | 1    |
| 2  | 联想小新  |  4999 | 2    |
+----+---------------+-------+------+
2 rows in set (0.00 sec)

2. 查看外键约束

# 查看表的约束
SHOW CREATE  TABLE 表名；
# 查看表 tb_product
SHOW CREATE  TABLE  tb_product;

Table	Create Table
tb_product	CREATE TABLE `tb_product` ( `id` varchar(50) COLLATE utf8mb4_general_ci NOT NULL, `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL, `price` double DEFAULT NULL, `cid` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`), KEY `cid` (`cid`), CONSTRAINT `tb_product_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `tb_category` (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

Table

Create Table

tb_product

CREATE TABLE tb_product ( id varchar(50) COLLATE utf8mb4_general_ci NOT NULL, name varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL, price double DEFAULT NULL, cid varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (id), KEY cid (cid), CONSTRAINT tb_product_ibfk_1 FOREIGN KEY (cid) REFERENCES tb_category (cid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

3. 删除外键约束

# 删除外键约束
ALTER   TABLE 表名  DROP FOREIGN KEY  外键约束名;

# 删除tb_product外键约束

ALTER   TABLE tb_product  DROP FOREIGN KEY  tb_product_ibfk_1;
# 查看表 tb_product
SHOW CREATE  TABLE  tb_product;

Table	Create Table
tb_product	CREATE TABLE `tb_product` ( `id` varchar(50) COLLATE utf8mb4_general_ci NOT NULL, `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL, `price` double DEFAULT NULL, `cid` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`), KEY `cid` (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

1.3 关联查询

期望查询结果

id(编号)	name(产品名称)	price(产品价格)	cid(产品所属类别编号)	cName(类别名称)
1	华为 Mate60	4528	1	手机
2	联想小新	4999	2	电脑
3	Redmi K70	1902	1	手机
4	联想拯救者	24999	2	电脑

1.笛卡尔积（交叉连接）

特点：

无条件连接：不指定任何关联字段（如 ON 条件），直接对两个表进行全量组合。
结果行数：表 A 行数 × 表 B 行数

# 语法
SELECT  * FROM 表1 JOIN 表2;
SELECT  * FROM 表1，表2;
# 交叉连接查询
SELECT  * FROM tb_product JOIN tb_category;
# 产品表
mysql> select * from tb_product;
+----+---------------+-------+------+
| id | name          | price | cid  |
+----+---------------+-------+------+
| 1  | 华为Mate 60 |  4528 | 1    |
| 2  | 联想小新  |  4999 | 2    |
+----+---------------+-------+------+
2 rows in set (0.00 sec)
# 产品类别表
mysql> select * from tb_category;
+-----+--------+
| cid | name   |
+-----+--------+
| 1   | 手机 |
| 2   | 电脑 |
+-----+--------+
2 rows in set (0.00 sec)
#  交叉连接查询
mysql> SELECT  * FROM tb_product JOIN tb_category;
+----+---------------+-------+------+-----+--------+
| id | name          | price | cid  | cid | name   |
+----+---------------+-------+------+-----+--------+
| 2  | 联想小新  |  4999 | 2    | 1   | 手机 |
| 1  | 华为Mate 60 |  4528 | 1    | 1   | 手机 |
| 2  | 联想小新  |  4999 | 2    | 2   | 电脑 |
| 1  | 华为Mate 60 |  4528 | 1    | 2   | 电脑 |
+----+---------------+-------+------+-----+--------+

2. 有条件连接

经常需要在连接时跟上一个限制的连接条件，满足条件的数据行才能进行连接，这就是有条件连接。

解决交叉连接问题

# 语法

SELECT  * FROM 表1 JOIN 表2 ON 连接条件;
/**
*对于有条件连接，共分为 4 种不同的连接方式
* 1. 内连接（INNER JOIN）
* 2. 左连接（LEFT JOIN）
* 3. 右连接（RIGHT JOIN）
* 4. 全连接（FULL JOIN）
**/

# 语法
SELECT  * FROM tb_product (INNER|LEFT|RIGHT|FULL) JOIN tb_category ON 连接条件;

1. 内连接

内连接：INNER JOIN，简写为 JOIN

返回两张表都满足连接条件的部分(交集).
左右两表关联时，满足关联条件的数据，才会出现在最终的关联结果中

# tb_product添加数据
insert into tb_product values (3, 'Redmi K70 ', 4528, 1);-- 成功
insert into tb_product values (4, '联想拯救者', 4999, 2);-- 成功

# 语法：
SELECT
tb_c.cid AS cid, -- 类别id（别名 cid）
tb_c.name AS cname, -- 类别名称（别名 cname
tb_p.id AS pid,-- 产品id（别名 pid）
tb_p.name AS pname -- 产品名称（别名 pname）
FROM tb_category tb_c -- 左表 （别名 tb_c）
INNER JOIN tb_product tb_p  -- 右表（别名  tb_p）
ON tb_c.cid =tb_p.cid;
/**
*其他注意事项
*1. 字段别名唯一性
*2. 表别名一致性
**/

# 连贯
 SELECT tb_c.cid AS cid,tb_c.name AS cname, tb_p.id AS pid,tb_p.name AS pname
 FROM tb_category tb_c INNER JOIN tb_product tb_p ON tb_c.cid =tb_p.cid;
 #查询
+-----+--------+-----+-----------------+
| cid | cname  | pid | pname           |
+-----+--------+-----+-----------------+
| 1   | 手机 | 1   | 华为Mate 60   |
| 1   | 手机 | 3   | Redmi K70       |
| 2   | 电脑 | 2   | 联想小新    |
| 2   | 电脑 | 4   | 联想拯救者 |
+-----+--------+-----+-----------------+

2. 左外连接

左外连接：LEFT OUTER JOIN，简写为 LEFT JOIN

左侧+交集部分
右两表关联时，除满足关联条件的数据会出现在最终的关联结果中，左表中不能和右边表联的数据也会出现，右表侧自动填充为 NULL

# tb_category添加数据
insert into tb_category values (3, '衣服');  -- 3
#  左外连接
SELECT tb_c.cid AS cid,tb_c.name AS cname, tb_p.id AS pid,tb_p.name AS pname
  FROM tb_category tb_c LEFT JOIN tb_product tb_p ON tb_c.cid =tb_p.cid;

 #  左外连接查询
 +-----+--------+------+-----------------+
| cid | cname  | pid  | pname           |
+-----+--------+------+-----------------+
| 1   | 手机 | 1    | 华为Mate 60   |
| 1   | 手机 | 3    | Redmi K70       |
| 2   | 电脑 | 2    | 联想小新    |
| 2   | 电脑 | 4    | 联想拯救者 |
| 3   | 衣服 | NULL | NULL            |
+-----+--------+------+-----------------+
5 rows in set (0.00 sec)

3. 右外连接

右外连接：RIGHT OUTER JOIN，简写为 RIGHT JOIN

右侧+交集部分
左右两表关联时，除满足关联条件的数据会出现在最终的关联结果中，右表中不能和左表关联的数据也会出现，左表侧自动填充为 NULL

# 右外连接
 SELECT tb_c.cid AS cid,tb_c.name AS cname, tb_p.id AS pid,tb_p.name AS pname
FROM tb_category tb_c RIGHT JOIN tb_product tb_p ON tb_c.cid =tb_p.cid;

# 右外连接 查询
+------+--------+-----+-----------------+
| cid  | cname  | pid | pname           |
+------+--------+-----+-----------------+
| 1    | 手机 | 1   | 华为Mate 60   |
| 2    | 电脑 | 2   | 联想小新    |
| 1    | 手机 | 3   | Redmi K70       |
| 2    | 电脑 | 4   | 联想拯救者 |
+------+--------+-----+-----------------+

4. 全外连接

全外连接：FULL OUTER JOIN，简写为 FULL JOIN

左侧 + 交集部分 + 右则
左右两表关联时，除满足关联条件的数据会出现在最终的关联结果中，左右两表不能相互关联的数据也都会出现，对应侧自动填充为 NULL
MySQL 数据库不支持全连接，需要将左连接和右连接的结果利用 UNION 关键字组合实现全连接的效果。

mysql 不支持 FULL JOIN，需要使用 UNION 将左右连接的结果进行合并。

2.自关联查询

1. 自关联表设计(地区表)

如果要设计数据表，保存中国的省、市、区县的数据，该设计几张表？

表结构设计：

province 表，保存省级行政区域的数据，结构如下：

字段名	数据类型	描述
id	INT	主键，自动增长
name	VARCHAR(50)	省的名称
code	VARCHAR(10)	省的行政区划代码

id	name	code
1	浙江省	330000
2	广东省	440000
3	北京市	110000

city 表保存市级行政区域的数据，结构如下

字段名	数据类型	描述
id	INT	主键，自动增长
name	VARCHAR(50)	市的名称
code	VARCHAR(10)	市的行政区划代码
province_id	INT	外键，关联 `province` 表

id	name	code	province_id
1	杭州市	330100	1
2	温州市	330300	1
3	广州市	440100	2
4	深圳市	440300	2
5	北京市	110100	3

district 表保存区县级行政区域的数据

字段名	数据类型	描述
id	INT	主键，自动增长
name	VARCHAR(50)	区县的名称
code	VARCHAR(10)	区县的行政区划代码
city_id	INT	外键，关联 `city` 表

id	name	code	city_id
1	上城区	330102	1
2	西湖区	330103	1
3	南山区	440305	4
4	福田区	440304	4
5	海淀区	110108	5

外键关系：

city.province_id 外键关联 province.id，表示每个市属于一个省。
district.city_id 外键关联 city.id，表示每个区县属于一个市。

数据表之间的层级关系：

省包含多个市；
市包含多个区县；
每个区县属于一个市，每个市属于一个省。

# 1. province 表（省级表）
CREATE TABLE `province` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,   -- 主键，自动增长
    `name` VARCHAR(50) NOT NULL,           -- 省的名称
    `code` VARCHAR(10) NOT NULL          -- 省的行政区划代码
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 插入数据
INSERT INTO `province` (`name`, `code`)
VALUES
    ('浙江省', '330000'),
    ('广东省', '440000'),
    ('北京市', '110000');


#2. city 表（市级表）
CREATE TABLE `city` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,   -- 主键，自动增长
    `name` VARCHAR(50) NOT NULL,           -- 市的名称
    `code` VARCHAR(10) NOT NULL,           -- 市的行政区划代码
    `province_id` INT NOT NULL ,        -- 外键，关联省的 `id`
    # 指定外键 province_id 引用父表 province 的主键字段 id
    # ON DELETE CASCADE 定义当父表 province 中的某条记录被删除时
    FOREIGN KEY (`province_id`) REFERENCES `province`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 插入数据
INSERT INTO `city` (`name`, `code`, `province_id`)
VALUES
    ('杭州市', '330100', 1),  -- 省ID为1（浙江省）
    ('温州市', '330300', 1),  -- 省ID为1（浙江省）
    ('广州市', '440100', 2),  -- 省ID为2（广东省）
    ('深圳市', '440300', 2),  -- 省ID为2（广东省）
    ('北京市', '110100', 3);  -- 省ID为3（北京市）

# 3. district 表（区县级表）
CREATE TABLE `district` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,   -- 主键，自动增长
    `name` VARCHAR(50) NOT NULL,           -- 区县的名称
    `code` VARCHAR(10) NOT NULL,           -- 区县的行政区划代码
    `city_id` INT NOT NULL,                -- 外键，关联市的 `id`

    FOREIGN KEY (`city_id`) REFERENCES `city`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 插入数据
INSERT INTO `district` (`name`, `code`, `city_id`)
VALUES
    ('上城区', '330102', 1),  -- 市ID为1（杭州市）
    ('西湖区', '330103', 1),  -- 市ID为1（杭州市）
    ('南山区', '440305', 4),  -- 市ID为4（深圳市）
    ('福田区', '440304', 4),  -- 市ID为4（深圳市）
    ('海淀区', '110108', 5);  -- 市ID为5（北京市）

2. 地区表自关联查询

什么是自连接

数据都在一张表(省、市、区)，数据与数据存在存在层级关系。如何查询我们所需的数据，就需要自己关联、

# 语法 （两个表进行关联时，如果左表和右边是同一张表，这就是自关联）
SELECT FROM 表1 a  JOIN 表1 b ON 关联条件;-- 左右表是同一张表，自关联时，表必须起别名
# 创建hubei表
CREATE  TABLE HuBei (
 id char(6) primary key,
  title  varchar(20),
    pid char(6)
)DEFAULT CHARSET=utf8mb4;

#  省，市，区都在一张表。
INSERT INTO `HuBei` VALUES ('420000', '湖北省', NULL);
INSERT INTO `HuBei` VALUES ('420100', '武汉市', '420000');
INSERT INTO `HuBei` VALUES ('420102', '江岸区', '420100');
INSERT INTO `HuBei` VALUES ('420103', '江汉区', '420100');
INSERT INTO `HuBei` VALUES ('420104', '硚口区', '420100');
INSERT INTO `HuBei` VALUES ('420105', '汉阳区', '420100');
INSERT INTO `HuBei` VALUES ('420106', '武昌区', '420100');
INSERT INTO `HuBei` VALUES ('420107', '青山区', '420100');
INSERT INTO `HuBei` VALUES ('420111', '洪山区', '420100');
INSERT INTO `HuBei` VALUES ('420112', '东西湖区', '420100');
INSERT INTO `HuBei` VALUES ('420113', '汉南区', '420100');
INSERT INTO `HuBei` VALUES ('420114', '蔡甸区', '420100');
INSERT INTO `HuBei` VALUES ('420115', '江夏区', '420100');
INSERT INTO `HuBei` VALUES ('420116', '黄陂区', '420100');
INSERT INTO `HuBei` VALUES ('420117', '新洲区', '420100');
INSERT INTO `HuBei` VALUES ('420200', '黄石市', '420000');
INSERT INTO `HuBei` VALUES ('420202', '黄石港区', '420200');
INSERT INTO `HuBei` VALUES ('420203', '西塞山区', '420200');
INSERT INTO `HuBei` VALUES ('420204', '下陆区', '420200');
INSERT INTO `HuBei` VALUES ('420205', '铁山区', '420200');
INSERT INTO `HuBei` VALUES ('420222', '阳新县', '420200');
INSERT INTO `HuBei` VALUES ('420281', '大冶市', '420200');
INSERT INTO `HuBei` VALUES ('420300', '十堰市', '420000');
INSERT INTO `HuBei` VALUES ('420302', '茅箭区', '420300');
INSERT INTO `HuBei` VALUES ('420303', '张湾区', '420300');
INSERT INTO `HuBei` VALUES ('420321', '郧县', '420300');
INSERT INTO `HuBei` VALUES ('420322', '郧西县', '420300');
INSERT INTO `HuBei` VALUES ('420323', '竹山县', '420300');
INSERT INTO `HuBei` VALUES ('420324', '竹溪县', '420300');
INSERT INTO `HuBei` VALUES ('420325', '房县', '420300');
INSERT INTO `HuBei` VALUES ('420381', '丹江口市', '420300');
INSERT INTO `HuBei` VALUES ('420500', '宜昌市', '420000');
INSERT INTO `HuBei` VALUES ('420502', '西陵区', '420500');
INSERT INTO `HuBei` VALUES ('420503', '伍家岗区', '420500');
INSERT INTO `HuBei` VALUES ('420504', '点军区', '420500');
INSERT INTO `HuBei` VALUES ('420505', '猇亭区', '420500');
INSERT INTO `HuBei` VALUES ('420506', '夷陵区', '420500');
INSERT INTO `HuBei` VALUES ('420525', '远安县', '420500');
INSERT INTO `HuBei` VALUES ('420526', '兴山县', '420500');
INSERT INTO `HuBei` VALUES ('420527', '秭归县', '420500');
INSERT INTO `HuBei` VALUES ('420528', '长阳土家族自治县', '420500');
INSERT INTO `HuBei` VALUES ('420529', '五峰土家族自治县', '420500');
INSERT INTO `HuBei` VALUES ('420581', '宜都市', '420500');
INSERT INTO `HuBei` VALUES ('420582', '当阳市', '420500');
INSERT INTO `HuBei` VALUES ('420583', '枝江市', '420500');
INSERT INTO `HuBei` VALUES ('420600', '襄阳市', '420000');
INSERT INTO `HuBei` VALUES ('420602', '襄城区', '420600');
INSERT INTO `HuBei` VALUES ('420606', '樊城区', '420600');
INSERT INTO `HuBei` VALUES ('420607', '襄州区', '420600');
INSERT INTO `HuBei` VALUES ('420624', '南漳县', '420600');
INSERT INTO `HuBei` VALUES ('420625', '谷城县', '420600');
INSERT INTO `HuBei` VALUES ('420626', '保康县', '420600');
INSERT INTO `HuBei` VALUES ('420682', '老河口市', '420600');
INSERT INTO `HuBei` VALUES ('420683', '枣阳市', '420600');
INSERT INTO `HuBei` VALUES ('420684', '宜城市', '420600');
INSERT INTO `HuBei` VALUES ('420700', '鄂州市', '420000');
INSERT INTO `HuBei` VALUES ('420702', '梁子湖区', '420700');
INSERT INTO `HuBei` VALUES ('420703', '华容区', '420700');
INSERT INTO `HuBei` VALUES ('420704', '鄂城区', '420700');
INSERT INTO `HuBei` VALUES ('420800', '荆门市', '420000');
INSERT INTO `HuBei` VALUES ('420802', '东宝区', '420800');
INSERT INTO `HuBei` VALUES ('420804', '掇刀区', '420800');
INSERT INTO `HuBei` VALUES ('420821', '京山县', '420800');
INSERT INTO `HuBei` VALUES ('420822', '沙洋县', '420800');
INSERT INTO `HuBei` VALUES ('420881', '钟祥市', '420800');
INSERT INTO `HuBei` VALUES ('420900', '孝感市', '420000');
INSERT INTO `HuBei` VALUES ('420902', '孝南区', '420900');
INSERT INTO `HuBei` VALUES ('420921', '孝昌县', '420900');
INSERT INTO `HuBei` VALUES ('420922', '大悟县', '420900');
INSERT INTO `HuBei` VALUES ('420923', '云梦县', '420900');
INSERT INTO `HuBei` VALUES ('420981', '应城市', '420900');
INSERT INTO `HuBei` VALUES ('420982', '安陆市', '420900');
INSERT INTO `HuBei` VALUES ('420984', '汉川市', '420900');
INSERT INTO `HuBei` VALUES ('421000', '荆州市', '420000');
INSERT INTO `HuBei` VALUES ('421002', '沙市区', '421000');
INSERT INTO `HuBei` VALUES ('421003', '荆州区', '421000');
INSERT INTO `HuBei` VALUES ('421022', '公安县', '421000');
INSERT INTO `HuBei` VALUES ('421023', '监利县', '421000');
INSERT INTO `HuBei` VALUES ('421024', '江陵县', '421000');
INSERT INTO `HuBei` VALUES ('421081', '石首市', '421000');
INSERT INTO `HuBei` VALUES ('421083', '洪湖市', '421000');
INSERT INTO `HuBei` VALUES ('421087', '松滋市', '421000');
INSERT INTO `HuBei` VALUES ('421100', '黄冈市', '420000');
INSERT INTO `HuBei` VALUES ('421102', '黄州区', '421100');
INSERT INTO `HuBei` VALUES ('421121', '团风县', '421100');
INSERT INTO `HuBei` VALUES ('421122', '红安县', '421100');
INSERT INTO `HuBei` VALUES ('421123', '罗田县', '421100');
INSERT INTO `HuBei` VALUES ('421124', '英山县', '421100');
INSERT INTO `HuBei` VALUES ('421125', '浠水县', '421100');
INSERT INTO `HuBei` VALUES ('421126', '蕲春县', '421100');
INSERT INTO `HuBei` VALUES ('421127', '黄梅县', '421100');
INSERT INTO `HuBei` VALUES ('421181', '麻城市', '421100');
INSERT INTO `HuBei` VALUES ('421182', '武穴市', '421100');
INSERT INTO `HuBei` VALUES ('421200', '咸宁市', '420000');
INSERT INTO `HuBei` VALUES ('421202', '咸安区', '421200');
INSERT INTO `HuBei` VALUES ('421221', '嘉鱼县', '421200');
INSERT INTO `HuBei` VALUES ('421222', '通城县', '421200');
INSERT INTO `HuBei` VALUES ('421223', '崇阳县', '421200');
INSERT INTO `HuBei` VALUES ('421224', '通山县', '421200');
INSERT INTO `HuBei` VALUES ('421281', '赤壁市', '421200');
INSERT INTO `HuBei` VALUES ('421300', '随州市', '420000');
INSERT INTO `HuBei` VALUES ('421302', '曾都区', '421300');
INSERT INTO `HuBei` VALUES ('421381', '广水市', '421300');
INSERT INTO `HuBei` VALUES ('422800', '恩施土家族苗族自治州', '420000');
INSERT INTO `HuBei` VALUES ('422801', '恩施市', '422800');
INSERT INTO `HuBei` VALUES ('422802', '利川市', '422800');
INSERT INTO `HuBei` VALUES ('422822', '建始县', '422800');
INSERT INTO `HuBei` VALUES ('422823', '巴东县', '422800');
INSERT INTO `HuBei` VALUES ('422825', '宣恩县', '422800');
INSERT INTO `HuBei` VALUES ('422826', '咸丰县', '422800');
INSERT INTO `HuBei` VALUES ('422827', '来凤县', '422800');
INSERT INTO `HuBei` VALUES ('422828', '鹤峰县', '422800');
INSERT INTO `HuBei` VALUES ('429000', '省直辖行政单位', '420000');
INSERT INTO `HuBei` VALUES ('429004', '仙桃市', '429000');
INSERT INTO `HuBei` VALUES ('429005', '潜江市', '429000');
INSERT INTO `HuBei` VALUES ('429006', '天门市', '429000');
INSERT INTO `HuBei` VALUES ('429021', '神农架林区', '429000');

# 查询'湖北省'下的所有市的信息
# 市级地区id、市级地区名称、父级地区id、父级地区名称
SELECT
    p.id AS province_id, -- 省id
    p.title AS province_name,  -- 省名
    c.id   AS city_id, -- 市id
    c.title AS city_Name  -- 市名称
FROM
    HuBei p -- 省级地区表 HuBei，别名为 p
JOIN
    HuBei c -- 市级地区表 HuBei，别名为 c
ON p.id = c.pid -- 省id 和 市的 pid 进行匹配
where p.title = '湖北省'; -- 过滤出湖北省的记录

where p.title = '湖北省';
# 查询结果
province_id	province_name	city_id	city_Name
420000	       湖北省	        420100	武汉市
420000	       湖北省	        420200	黄石市
420000	       湖北省	        420300	十堰市
420000	       湖北省	        420500	宜昌市
420000	       湖北省	        420600	襄阳市
420000	       湖北省	        420700	鄂州市
420000	       湖北省	        420800	荆门市
420000	       湖北省	        420900	孝感市
420000	       湖北省	        421000	荆州市
420000	       湖北省	        421100	黄冈市
420000	       湖北省	        421200	咸宁市
420000	       湖北省	        421300	随州市
420000	       湖北省	        422800	恩施土家族苗族自治州
420000	       湖北省	        429000	省直辖行政单位


# 省、市、区 三级联动 area

SELECT
    p.id AS province_id,   -- 省id
    p.title AS province_name,  -- 省名
    c.id AS city_id,  -- 市id
    c.title AS city_name,  -- 市名
    a.id AS area_id,  -- 区id
    a.title AS area_name  -- 区名称
FROM
    HuBei p  -- 省级地区表 HuBei，别名为 p
JOIN
    HuBei c  -- 市级地区表 HuBei，别名为 c
ON
    p.id = c.pid  -- 省id 和 市的 pid 进行匹配
JOIN
    HuBei a  -- 区表 HuBei，别名为 a
ON
    c.id = a.pid  -- 市id 和 区的 pid 进行匹配
WHERE
    p.title = '湖北省';  -- 过滤出省名为湖北省的记录

3. 子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个 select 语句则称为主查询

主查询和子查询的关系:

子查询是嵌入到主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源(数据表)
子查询是可以独立存在的语句,是一条完整的 select 语句

3.1 子查询充当查询条件

如何查询价格大于所有商品平均价的商品数据

# 创建一个商品表 products
CREATE TABLE products (
    id INT PRIMARY KEY,  -- 商品id
    name VARCHAR(100), -- 商品名称
    price DECIMAL(10, 2) -- 商品价格
);
# 插入5条商品数据
INSERT INTO products (id, name, price) VALUES
(1, '商品A', 150.00),
(2, '商品B', 200.00),
(3, '商品C', 100.00),
(4, '商品D', 250.00),
(5, '商品E', 180.00);

# 计算平均价格(176.00 )
SELECT AVG(price) AS avg_price FROM products;

# 查询价格高于平均价的商品
SELECT
    id,
    name,
    price
FROM
    products
WHERE
    price > (SELECT AVG(price) FROM products);

+----+--------+--------+
| id | name   | price  |
+----+--------+--------+
| 2  | 商品B  | 200.00 |
| 4  | 商品D  | 250.00 |
| 5  | 商品E  | 180.00 |
+----+--------+--------+

3.2 子查询结果充当查询条件

查询价格最高的商品（价格最高的可能不只一个）

-- 创建表
CREATE TABLE products1 (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2)
);

-- 插入数据（包含多个相同最高价）
INSERT INTO products1 (id, name, price) VALUES
(1, '商品A', 150.00),
(2, '商品B', 250.00),
(3, '商品C', 200.00),
(4, '商品D', 250.00),
(5, '商品E', 180.00);

-- 查询价格最高的所有商品
SELECT
    id,
    name,
    price
FROM
    products1
WHERE
    price = (SELECT MAX(price) FROM products);

  +----+---------+--------+
| id | name    | price  |
+----+---------+--------+
|  2 | 商品B | 250.00 |
|  4 | 商品D | 250.00 |
+----+---------+--------+

3.3 子查询结果充当查询字段

计算每个学生的分数和整体平均分的差值

# 创建学生成绩
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50) NOT NULL,
    score DECIMAL(10, 2) NOT NULL  -- 学生分数
);

# 插入数据
INSERT INTO students (student_name, score) VALUES
('张三', 85.5),
('李四', 92.0),
('王五', 76.5),
('赵六', 88.0),
('陈七', 81.5);

# 子查询
SELECT AVG(score) FROM students  -- 返回全体平均分（86.2）

# 查询 子查询结果充当查询字段
SELECT
    id,
    student_name,
    score,
    (score - (SELECT AVG(score) FROM students)) AS score_diff
FROM students;
+----+--------------+-------+------------+
| id | student_name | score | score_diff |
+----+--------------+-------+------------+
|  1 | 张三       | 85.50 |   0.800000 |
|  2 | 李四       | 92.00 |   7.300000 |
|  3 | 王五       | 76.50 |  -8.200000 |
|  4 | 赵六       | 88.00 |   3.300000 |
|  5 | 陈七       | 81.50 |  -3.200000 |
+----+--------------+-------+------------+

4. 窗口函数

窗口函数（Window Functions）是 MySQL 8.0 及以上版本引入的核心功能，支持在数据集的特定“窗口”（即一组相关行）上执行计算，同时保留原始数据的每一行。这使得它非常适合处理排名、累计值、移动平均等复杂分析需求，而无需使用复杂的自连接或子查询。

1.窗口函数核心特点

保留原始行：窗口函数不会合并行（与 GROUP BY 不同），而是为每一行生成计算结果。
灵活定义窗口：通过 OVER() 子句控制计算范围，支持分区、排序、滑动窗口等。
高效计算：相比自连接或子查询，性能更高，尤其适合大数据量场景。

2.常见窗口函数分类

类型	函数示例	用途
排名函数	`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `NTILE()`	为数据分配排名或分组编号
聚合函数	`SUM()`, `AVG()`, `COUNT()`, `MIN()`, `MAX()`	在窗口内执行聚合计算（如累计、移动平均）
分布函数	`PERCENT_RANK()`, `CUME_DIST()`	计算相对排名或累积分布
前后行函数	`LAG()`, `LEAD()`	访问当前行前后指定偏移量的数据
首尾值函数	`FIRST_VALUE()`, `LAST_VALUE()`, `NTH_VALUE()`	获取窗口内第一行、最后一行或第 N 行的值

3. OVER 关键字

OVER(...)：作用就是设置每一行数据关联的一组数据范围，OVER()时，每行关联的数据范围都是整张表的数据。
<window function>: 表示使用窗口函数，函数可以使用聚合函数、

#案例
SELECT 字段,  ..., <window function> OVER(...) FROM 表名；


# 查询 子查询 计算每个学生的分数和整体平均分的差值
SELECT
    id,
    student_name,
    score,
    (score - (SELECT AVG(score) FROM students)) AS score_diff
FROM students;

# 查询 窗口函数 计算每个学生的分数和整体平均分的差值
SELECT
    id,
    student_name,
    score,
    (score - AVG(score) OVER ()) AS score_diff
FROM students;

AVG(score) OVER ()：这是一个窗口函数，它会计算所有学生成绩的平均值。OVER () 表示窗口函数没有分区，意味着它会对整个数据集进行操作
score - AVG(score) OVER ()：计算当前学生成绩与所有学生成绩平均值之间的差异

#  计算每个学生的Score分数占所有学生分数之和的百分比

# students表
SELECT
id,
student_name,
score,
score * 100.0 / SUM(score) OVER () AS score_percentage
FROM students

+----+--------------+-------+------------------+
| id | student_name | score | score_percentage |
+----+--------------+-------+------------------+
|  1 | 张三       | 85.50 |       20.1889020 |
|  2 | 李四       | 92.00 |       21.7237308 |
|  3 | 王五       | 76.50 |       18.0637544 |
|  4 | 赵六       | 88.00 |       20.7792208 |
|  5 | 陈七       | 81.50 |       19.2443920 |
+----+--------------+-------+------------------+

4. PARTITION BY

PARTITION BY 列名, ...：按照指定列的值对整张表的数据进行分区，OVER()中没有 PARITION 时，整张表就是一个分区。
分区之前后，在处理每行数据，<window function>是作用在改行数据关联的分区商，不在是整张表

# 基本语法
SELECT  字段， ...，<window function> OVER(PARTITION BY 字段，...) FROM 表名；

# 计算每个学生的 Score 分数和同性别学生平均分的差值
#创建 students1 标签
CREATE TABLE students1 (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    gender VARCHAR(10),
    score INT
);

#
INSERT INTO students1 (id, name, gender, score) VALUES
(1, '张三', '男', 85),
(2, '李四', '女', 90),
(3, '王五', '男', 80),
(4, '赵六', '女', 70),
(5, '孙七', '男', 75);

#  SQL 查询
SELECT
    id,
    name,
    gender,
    score,
    AVG(score) OVER (PARTITION BY gender) AS avg_gender_score,   -- 同性别学生的平均分
    score - AVG(score) OVER (PARTITION BY gender) AS difference   -- 分数与同性别学生平均分的差值
FROM students1;

#
+----+--------+--------+-------+------------------+------------+
| id | name   | gender | score | avg_gender_score | difference |
+----+--------+--------+-------+------------------+------------+
|  2 | 李四 | 女    |    90 |          80.0000 |    10.0000 |
|  4 | 赵六 | 女    |    70 |          80.0000 |   -10.0000 |
|  1 | 张三 | 男    |    85 |          80.0000 |     5.0000 |
|  3 | 王五 | 男    |    80 |          80.0000 |     0.0000 |
|  5 | 孙七 | 男    |    75 |          80.0000 |    -5.0000 |
+----+--------+--------+-------+------------------+------------+

5. GROUP BY 和 PARTITION BY 区别

使用场景不同

GROUP BY 分组是为了聚合，分组聚合属于：多进一出。
PARTITION BY 分区是为了窗口函数运算。窗口函数属于：一进一出。

结果集的变化

GROUP BY：
- 对查询结果进行分组后，每个组返回一行，通常会进行聚合计算。
- 结果集会减少，只保留每个分组的聚合值。
PARTITION BY：
- 数据仍然会显示每一行，每行都计算一个窗口函数的值，但分区计算是基于分区内的数据。
- 结果集的行数不会发生变化，每个分区的窗口函数的值会被计算并添加到每一行。

# GROUP BY查询
SELECT
    gender,
    AVG(score) AS avg_score
FROM students1
GROUP BY gender;

mysql>
+--------+-----------+
| gender | avg_score |
+--------+-----------+
| 男    |   80.0000 |
| 女    |   80.0000 |
+--------+-----------+

# PARTITION BY查询
-- 计算每个性别的平均分，同时保留所有原始行
SELECT
    id,
    name,
    gender,
    score,
    AVG(score) OVER (PARTITION BY gender) AS avg_score
FROM students1;

+----+--------+--------+-------+-----------+
| id | name   | gender | score | avg_score |
+----+--------+--------+-------+-----------+
|  2 | 李四 | 女    |    90 |   80.0000 |
|  4 | 赵六 | 女    |    70 |   80.0000 |
|  1 | 张三 | 男    |    85 |   80.0000 |
|  3 | 王五 | 男    |    80 |   80.0000 |
|  5 | 孙七 | 男    |    75 |   80.0000 |
+----+--------+--------+-------+-----------+
5 rows in set (0.00 sec)

6. 排名函数

1.ORDER BY

OVER（）中可以指定 ORDER BY 按照指定列对每个分区内的数据进行排名。
<ranking function> 排名函数用于分区的每行数据产生一个排名序号。

RANK DENSE_RANK ROW_NUMBER

RANK(): 有并列的情况出现序号会重复不连续。
DENSE_RANK：有并列的情况时序会重复单但连续。
ROW_NUMBER()：返回连续唯一的行号，序号不会重复。

   #  语法：
   SELECT 字段，  ... ,< ranking  function> OVER (ORDER 列名， ...)
   
# 1scores 表
CREATE TABLE scores (
    student_name VARCHAR(50),
    course VARCHAR(50),
    score INT
);
# 插入数据
INSERT INTO scores (student_name, course, score) VALUES
('张三', '数学', 85),
('张三', '英语', 92),
('李四', '数学', 88),
('李四', '英语', 79),
('王五', '数学', 80),
('王五', '英语', 85),
('赵六', '数学', 88),
('赵六', '英语', 95);

# SQL 查询
SELECT
    student_name AS name,
    course,
    score,
    RANK() OVER (PARTITION BY course ORDER BY score DESC) AS my_rank,           -- RANK 排名
    DENSE_RANK() OVER (PARTITION BY course ORDER BY score DESC) AS my_dense_rank, -- DENSE_RANK 排名
    ROW_NUMBER() OVER (PARTITION BY course ORDER BY score DESC) AS my_row_number  -- ROW_NUMBER 排名
FROM scores;

+--------+--------+-------+---------+---------------+---------------+
| name   | course | score | my_rank | my_dense_rank | my_row_number |
+--------+--------+-------+---------+---------------+---------------+
| 李四 | 数学 |    88 |       1 |             1 |             1 |
| 赵六 | 数学 |    88 |       1 |             1 |             2 |
| 张三 | 数学 |    85 |       3 |             2 |             3 |
| 王五 | 数学 |    80 |       4 |             3 |             4 |
| 赵六 | 英语 |    95 |       1 |             1 |             1 |
| 张三 | 英语 |    92 |       2 |             2 |             2 |
| 王五 | 英语 |    85 |       3 |             3 |             3 |
| 李四 | 英语 |    79 |       4 |             4 |             4 |

2. 求指定排名

获取指定排名数据时，注意不能直接在排名结果后面加 WHERE 筛选指定排名的数据。
获取指定排名的步骤
先使用排名函数进行排名
将排名的结果作为一张临时表，筛选指定排名的数据

# 获取每个科目，排名第二的学生信息

SELECT *
FROM (
    SELECT *, DENSE_RANK() OVER (
        -- 按照科目进行分区
        PARTITION BY course
         -- 安装成绩从高到低排序
        ORDER BY score DESC
    ) AS my_dense_rank
    FROM scores
) AS ranked_scores
WHERE my_dense_rank = 2;

+--------------+--------+-------+---------------+
| student_name | course | score | my_dense_rank |
+--------------+--------+-------+---------------+
| 张三       | 数学 |    85 |             2 |
| 张三       | 英语 |    92 |             2 |
+--------------+--------+-------+---------------+

3. CTE 公用表表达式

CTE (Common Table Expression，公共表表达式) 是 MySQL 8.0 引入的重要特性，它允许在查询中创建临时命名结果集，提高复杂查询的可读性和可维护性（类似子查询充当数据源，相当于一张临时表）。

# 基本语法
WITH some_name1 AS (
 --SELECT 语句

)，some_name2 AS（
--SELECT 语句
）
... SELECT
... FROM  上面的临时表；

some_name1，some_name2 就相当于临时表的名称
最后的 SELECT 可以继续上面的临时表，继续查询。

# CTE查询
WITH RankedScores AS (
    SELECT *,
           DENSE_RANK() OVER (PARTITION BY course ORDER BY score DESC) AS my_dense_rank
    FROM scores
)
SELECT *
FROM RankedScores
WHERE my_dense_rank = 2;

+--------------+--------+-------+---------------+
| student_name | course | score | my_dense_rank |
+--------------+--------+-------+---------------+
| 张三       | 数学 |    85 |             2 |
| 张三       | 英语 |    92 |             2 |
+--------------+--------+-------+---------------+

4. NTILE 排名函数使用

NTILE() 是一个窗口函数，它将结果集划分成指定数量的“桶”，并为每个记录分配一个桶的编号。它可以用来进行分组或排名，将数据平均分配到不同的组中。

作用

将每个分区的数据均匀的分成 X 组，返回每行在分区内对应的组号。
如果每组的数量不能平均，前面某些组的数量会比后面多一条。

NTILE
(number_of_buckets)  -- 指定将数据划分为多少个桶
OVER (
    PARTITION BY column_name -- 根据指定的列进行分区（可选）
      ORDER BY column_name  --指定排序规则，决定如何将数据分配到桶中
)

# 创建
CREATE TABLE student_grades (
  id INT PRIMARY KEY AUTO_INCREMENT,
  student_name VARCHAR(50) NOT NULL,
  subject VARCHAR(50) NOT NULL,
  score INT CHECK(score BETWEEN 0 AND 100)
);
# 插入数据
INSERT INTO student_grades (student_name, subject, score) VALUES
('Alice', 'Math', 95),
('Bob', 'Math', 88),
('Cathy', 'Math', 75),
('David', 'Math', 92),
('Eva', 'Math', 80),
('Frank', 'English', 85),
('Grace', 'English', 90),
('Henry', 'English', 78),
('Ivy', 'Physics', 82),
('Jack', 'Physics', 68);

# 将数据分为4个分组（按科目分组，分数降序）
SELECT
  student_name,
  subject,
  score,
  -- 将数据分为4个分组（按科目分组，分数降序）
  NTILE(4) OVER (PARTITION BY subject ORDER BY score DESC) AS ntile_4
FROM student_grades
ORDER BY subject, score DESC;

+--------------+---------+-------+---------+
| student_name | subject | score | ntile_4 |
+--------------+---------+-------+---------+
| Grace        | English |    90 |       1 |
| Frank        | English |    85 |       2 |
| Henry        | English |    78 |       3 |
| Alice        | Math    |    95 |       1 |
| David        | Math    |    92 |       1 |
| Bob          | Math    |    88 |       2 |
| Eva          | Math    |    80 |       3 |
| Cathy        | Math    |    75 |       4 |
| Ivy          | Physics |    82 |       1 |
| Jack         | Physics |    68 |       2 |

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

数据库

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

数据库

MySQL 基础（二）

MySQL 基础（二）

MySQL 基础（二）

1.多表关联查询

1.1 表之间的关系

1. 关联关系

2. 一对多关系

3. 多对多关系

1.2. 外键与外键约束

1.创建外键约束

2. 查看外键约束

3. 删除外键约束

1.3 关联查询

1.笛卡尔积（交叉连接）

2. 有条件连接

1. 内连接

2. 左外连接

3. 右外连接

4. 全外连接

2.自关联查询

1. 自关联表设计(地区表)

2. 地区表自关联查询

3. 子查询

3.1 子查询充当查询条件

3.2 子查询结果充当查询条件

3.3 子查询结果充当查询字段

4. 窗口函数

1.窗口函数核心特点

2.常见窗口函数分类

3. OVER 关键字

4. PARTITION BY

5. GROUP BY 和 PARTITION BY 区别

6. 排名函数

1.ORDER BY

2. 求指定排名

3. CTE 公用表表达式

4. NTILE 排名函数使用

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐