数据库基础与核心操作：从概念到实战的全面解析

1 基本概念

数据库定义
数据库按照数据结构来组织、存储和管理数据的仓库；是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。
关系型数据库

关系模型定义：关系模型由埃德加・科德（Edgar F. Codd）在 1970 年提出。它将数据组织成二维表格的形式，由行和列组成，每一行代表一个记录，每一列代表一个属性。这种表格结构使得数据的组织和管理更加规范和有序。
表与表之间的关系：关系型数据库中，表与表之间可以通过关联关系来建立联系，从而实现数据的整合和查询。常见的关系有一对一、一对多和多对多。通过外键来实现表之间的关联，外键是一个表中的字段，它引用了另一个表的主键，以此来建立表与表之间的联系。例如，在 “订单” 表和 “客户” 表中，“订单” 表可能有一个 “客户 ID” 字段作为外键，关联到 “客户” 表的主键 “客户 ID”，这样就可以通过 “客户 ID” 来查询某个客户的所有订单信息，实现了两张表之间的数据关联。

OLTP
OLTP（online transaction processing）翻译为联机事务处理；主要对数据库增删改查；

OLTP主要用来记录某类业务事件的发生；数据会以增删改的方式在数据库中进行数据的更新处理
操作，要求实时性高、稳定性强、确保数据及时更新成功；

-OLAP
OLAP（On-Line Analytical Processing）翻译为联机分析处理；主要对数据库查询；

当数据积累到一定的程度，我们需要对过去发生的事情做一个总结分析时，就需要把过去一段时间
内产生的数据拿出来进行统计分析，从中获取我们想要的信息，为公司做决策提供支持，这时候就
是在做OLAP了；

-SQL
结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。SQL是关系数据库系统的标准语言
分为 DQL(Data Query Language),DML(Data Manipulate Language),DDL(Data Control Language ),TCL(Transaction Control Language)

基本构成

从一个select的命令执行流程讲一下这个图

• 连接层
  客户端通过如 Native C API、JDBC、ODBC 等 Connectors 建立与 MySQL Server 的连接，连接进入 Connection Pool 。在此会进行身份验证（Authentication）、线程复用（Thread Reuse ）等操作，满足连接限制（Connection Limits ）等规则后，才被允许进入下一步。
• SQL 接口层
  SELECT 语句先进入 SQL Interface，在这里进行初步处理，判断它属于数据操作语言（DQL ）。
• 解析层
  随后语句进入 Parser（解析器），进行词法和语法分析，将 SELECT 语句拆解成一个个词法单元，并检查语法是否正确。同时进行语义分析，检查语句中涉及的表、列等对象是否存在，以及用户权限等。
• 优化层
  经过解析的语句进入 Optimizer（优化器），优化器依据数据库中的统计信息（Access Paths, Statistics ），尝试找出执行 SELECT 查询的最佳计划，比如选择合适索引、确定表连接顺序和连接算法等。
• 存储引擎层
  确定执行计划后，MySQL 通过 Pluggable Storage Engines（可插拔存储引擎，如 MyISAM、InnoDB 等 ）从底层文件系统（File System ，如 NTFS、NFS 等 ）读取数据。不同存储引擎在内存、索引及存储管理上各有特点。

其实还有一个查询缓存，但是由于考虑到缓存变化，在mysql8.0取消了这个机制

三范式（用时间换空间）

• 第一范式（1NF）：字段值不能再拆分，得是最小的原子单元 。比如 “地址” 字段不能既包含省市区又包含详细街道，得拆开成多个字段 ，保证每列数据的独立性。
• 第二范式（2NF）：在满足第一范式基础上，要求非主键字段完全依赖主键 。不能出现部分依赖，像订单表中 “订单编号 + 产品编号” 是联合主键，“产品价格” 依赖这俩没问题，但 “下单时间” 只依赖 “订单编号”，就不符合，得把 “下单时间” 移到只以 “订单编号” 为主键的订单主表 。
• 第三范式（3NF）：满足第二范式前提下，消除非主键字段间的传递依赖 。比如员工表中 “员工编号→部门编号→部门负责人”，“部门负责人” 间接依赖 “员工编号”，不符合，得把部门相关信息拆成独立部门表 。

反范式
范式设计将相关信息分散到不同逻辑表，虽减少冗余，但多表查询（如 JOIN 操作）开销大。反范式通过允许少量冗余，用空间换时间，减少表连接等复杂操作，加快查询速度 。

2 基本操作

2.1 DCL

创建用户
以管理员（通常是 root ）身份连接到 MySQL 数据库，命令为mysql -u root -p ，输入密码后进入命令行。
使用CREATE USER命令创建新用户，语法是CREATE USER 'username'@'host' IDENTIFIED BY 'password' 。

其中，username是新用户名 ，host指定用户允许从哪个主机连接（localhost表示本地，%表示任意 IP ），password是用户密码 。比如创建用户testuser ，允许从任意主机连接，密码为123abc ，命令是CREATE USER ‘testuser’@‘%’ IDENTIFIED BY ‘123abc’ 。引号

授予权限
权限有数据库级、表级、列级等 ，常见权限包括SELECT（查询）、INSERT（插入）、UPDATE（更新）、DELETE（删除） 等。使用GRANT命令授予权限，语法为GRANT privileges ON database.table TO 'username'@'host' 。

• 授予特定权限：若要给testuser授予对testdb数据库中所有表的查询和插入权限，命令是GRANT SELECT,INSERT ON testdb.* TO ‘testuser’@‘%’ 。

• 授予所有权限：若希望testuser拥有对testdb数据库的所有权限，命令是GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'%' ；若要授予对所有数据库和表的完全访问权限，则是GRANT ALL PRIVILEGES ON . TO ‘testuser’@‘%’。

综合练习

-- 创建用户
CREATE USER 'hello'@'%' IDENTIFIED BY 'password123';
-- 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'hello'@'%';
//grant create on *.*to "hello"@"host"; 给与创造权限
-- 刷新权限
FLUSH PRIVILEGES;
-- 查询用户
select user host from mysql.user 
-- 退出 MySQL
exit;

2.2 DDL

创建数据库
CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8;

在 SQL 里，DEFAULT CHARACTER SET 是一个选项，用于设置数据库或表默认使用的字符集。而 utf8 是一种可变长度的字符编码，它能对世界上大部分字符进行编码，涵盖了众多语言的字符。因此，DEFAULT CHARACTER SET utf8 意思是将默认字符集设定为 utf8，这意味着后续在该数据库或表中存储数据时，会按照 utf8 编码规则来存储字符。

删除数据库
DROP DATABASE 数据库名;
选择数据库
USE 数据库名;

创建表

CREATE TABLE  `project` (
`id` INT UNSIGNED AUTO_INCREMENT ,
`course` VARCHAR(100) NOT NULL ,
`teacher` VARCHAR(40) NOT NULL ,
`price` DECIMAL(8,2) NOT NULL ,
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

ENGINE=InnoDB 这个是指定引擎操作

常见数据类型

分类	数据类型	占用空间	取值范围	用途示例
数值类型	TINYINT	1 字节	有符号：-128 到 127 无符号：0 到 255	状态标识（如 0 或 1）
	SMALLINT	2 字节	有符号：-32768 到 32767 无符号：0 到 65535	较小数量统计
	INT	4 字节	有符号：-2147483648 到 2147483647 无符号：0 到 4294967295	用户 ID、订单编号
	BIGINT	8 字节	更大范围整数	大型网站浏览量统计
	FLOAT	4 字节	约 7 位十进制精度	商品近似价格
	DOUBLE	8 字节	约 15 位十进制精度	科学计算数据存储
	DECIMAL(M,D)	取决于 M 和 D	精确小数	货币金额存储
日期和时间类型	DATE	3 字节	‘1000 - 01 - 01’ 到 ‘9999 - 12 - 31’	员工入职日期
	TIME	3 字节	‘-838:59:59’ 到 ‘838:59:59’	会议开始时间
	DATETIME	8 字节	‘1000 - 01 - 01 00:00:00’ 到 ‘9999 - 12 - 31 23:59:59’	订单创建时间
	TIMESTAMP	4 字节	‘1970 - 01 - 01 00:00:01’ UTC 到 ‘2038 - 01 - 19 03:14:07’ UTC	数据最后修改时间
	YEAR	1 字节	4 位：1901 到 2155 2 位：70 到 69（代表 1970 到 2069）	产品生产年份
字符串类型	CHAR(N)	N 字节（N 为 1 - 255）	长度固定为 N	身份证号码
	VARCHAR(N)	L + 1 字节（L 为实际字符串长度，N 为 1 - 65535）	可变长度，最大 N	用户昵称、文章标题
	TINYTEXT	L + 1 字节（L < 2^8）	可变长度	短文本内容
	TEXT	L + 2 字节（L < 2^16）	可变长度	文章内容、评论
	MEDIUMTEXT	L + 3 字节（L < 2^24）	可变长度	较长文本内容
	LONGTEXT	L + 4 字节（L < 2^32）	可变长度	非常长的文本内容
二进制类型	BINARY(N)	N 字节（N 为 1 - 255）	固定长度二进制数据	固定长度二进制编码
	VARBINARY(N)	L + 1 字节（L 为实际数据长度，N 为 1 - 65535）	可变长度二进制数据	可变长度二进制编码
	TINYBLOB	L + 1 字节（L < 2^8）	可变长度二进制数据	小二进制文件（如小图标）
	BLOB	L + 2 字节（L < 2^16）	可变长度二进制数据	图片、音频片段
	MEDIUMBLOB	L + 3 字节（L < 2^24）	可变长度二进制数据	中等大小二进制文件
	LONGBLOB	L + 4 字节（L < 2^32）	可变长度二进制数据	大型二进制文件（如高清视频）
JSON 类型	JSON	取决于存储的 JSON 数据大小	有效 JSON 数据	存储半结构化数据（如用户偏好设置）

一个汉字通常占用3 个字节。不过，一些生僻字可能会占用 4 个字节

删除表

DROP TABLE `table_name`;
TRUNCATE TABLE `table_name`; -- 截断表 以页为单位（至少有两行数据），有自增索引的话，
从初始值开始累加
DELETE TABLE `table_name`; -- 逐行删除，有自增索引的话，从之前值继续累加

选delete就行了 因为支持回滚

2.3 DML

增
INSERT INTO table_name (field1, field2, ..., fieldn) VALUES (value1, value2, ..., valuen);

-- 常规做法 自增列会自己加载
insert into `project` (`course`,`teacher`,`price`)values ("sh","张山",23.2);
-- 不指定列 直接放
insert into `project` values (1,"英语","张山",23.2);

删除
DELETE FROM table_name [WHERE Clause];、

CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(50),age INT
);INSERT INTO students (id, name, age) VALUES
(1, '张三', 20),
(2, '李四', 22),
(3, '王五', 21);
-- 删除行
DELETE FROM students WHERE age = 22;--删除列
ALTER TABLE table_name
DROP COLUMN column_name;

** 更改**
UPDATE table_name SET field1=new_value1, field2=new_value2 [, fieldn=new_valuen]

UPDATE 0voice_tblSETname = 'Mark' WHERE id = 2;

2.4 DQL(高级查询)

展现数据库和表
show databases/tables
展现表的内容 基础查询
SELECT field1, field2,...fieldN FROM table_name
条件查询

-- 查询姓名为 邓洋洋 的学生信息
SELECT * FROM `student` WHERE `name` = '邓洋洋';
-- 查询性别为 男，并且班级为 2 的学生信息
SELECT * FROM `student` WHERE `gender`="男" AND `class_id`=2;

利用where 做判断

查询后排序

-- 关键字：order by field, asc:升序, desc:降序
SELECT * FROM `score` ORDER BY `num` ASC;
-- 按照多个字段排序
SELECT * FROM `score` ORDER BY `course_id` DESC, `num` DESC;

聚合查询
以下是关于常见聚合查询函数的表格总结：

函数名称	功能描述	语法示例	适用数据类型	备注
COUNT()	统计记录的数量，COUNT(*) 统计所有记录数（包含 NULL），COUNT(column_name) 统计指定列非 NULL 的记录数	COUNT(*) COUNT(column_name)	所有数据类型（COUNT(*)）； 一般用于数值、字符等列（COUNT(column_name)）	NULL 值处理方式不同，COUNT(*) 包含 NULL，COUNT(column_name) 忽略 NULL
SUM()	计算指定列中数值的总和	SUM(column_name)	数值类型（如 INT、FLOAT、DECIMAL 等）	仅对数值列有效，忽略 NULL 值
AVG()	计算指定列中数值的平均值	AVG(column_name)	数值类型（如 INT、FLOAT、DECIMAL 等）	仅对数值列有效，忽略 NULL 值
MAX()	找出指定列中的最大值	MAX(column_name)	数值、日期、字符串等数据类型	忽略 NULL 值
MIN()	找出指定列中的最小值	MIN(column_name)	数值、日期、字符串等数据类型	忽略 NULL 值
GROUP_CONCAT()	将分组后的某列值连接成一个字符串，可指定分隔符、去重、排序等	GROUP_CONCAT([DISTINCT] column_name [ORDER BY column_name ASC/DESC] [SEPARATOR separator])	字符类型数据	常用于将分组后的字符串类型数据连接起来

分组查询

-- 可以把查询出来的结果根据某个条件来分组显示
SELECT `gender` FROM `student` GROUP BY `gender`;
-- 分组加聚合
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender`;
-- 分组加条件
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender` HAVING num

±-------+
| gender |
±-------+
| 男 |
| 女 |
±-------+

±-------±----+
| gender | num |
±-------±----+
| 男 | 10 |
| 女 | 6 |
±-------±----+

±-------±----+
| gender | num |
±-------±----+
| 男 | 10 |
±-------±----+

关于执行顺序

SQL 查询语句各子句的执行顺序如下：
FROM：这是最先执行的子句，它的作用是从指定的表或者视图中选取数据。在这个阶段，数据库会根据 FROM 子句中指定的表名，从存储介质中读取相应的数据，并将这些数据加载到内存中，为后续的操作做准备。
JOIN：如果查询中使用了 JOIN 操作（如 INNER JOIN、LEFT JOIN 等），数据库会在 FROM 子句选取的数据基础上，根据 JOIN 条件将多个表中的数据进行关联。通过 JOIN 操作，可以将不同表中相关的数据组合在一起，形成一个新的结果集。
WHERE：该子句用于对 FROM 和 JOIN 操作得到的结果集进行过滤。数据库会根据 WHERE 子句中指定的条件，对每一行数据进行判断，只保留满足条件的数据行，将不满足条件的行过滤掉。
GROUP BY：GROUP BY 子句用于对经过 WHERE 子句过滤后的结果集进行分组。数据库会根据 GROUP BY 子句中指定的列，将具有相同值的行分为一组。分组操作通常与聚合函数（如 SUM、AVG、COUNT 等）一起使用，以便对每个组的数据进行统计分析。
HAVING：HAVING 子句用于对分组后的结果进行过滤。它与 WHERE 子句类似，但 WHERE 子句是在分组之前对行进行过滤，而 HAVING 子句是在分组之后对组进行过滤。HAVING 子句通常与聚合函数一起使用，用于筛选满足特定条件的组。
SELECT：该子句用于从经过前面各子句处理后的结果集中选取指定的列。数据库会根据 SELECT 子句中指定的列名，从结果集中提取相应的数据，并将这些数据作为最终结果的一部分。
DISTINCT：如果 SELECT 子句中使用了 DISTINCT 关键字，数据库会对 SELECT 子句选取的结果进行去重处理，只保留唯一的行。
ORDER BY：ORDER BY 子句用于对最终结果集进行排序。数据库会根据 ORDER BY 子句中指定的列，按照升序（ASC）或降序（DESC）对结果集进行排序，使结果集按照指定的顺序呈现

联表查询
在 SQL 中，INNER JOIN、LEFT JOIN 和 RIGHT JOIN 是用于合并多个表数据的连接操作，它们的主要区别在于处理不匹配行的方式。以下是它们的区别对比表格：

连接类型	关键字	操作原理	结果特点	示例（假设表 A 和表 B 通过列 col 连接）
内连接	INNER JOIN	只返回两个表中连接列匹配的行，即只有当表 A 中的某行与表 B 中的某行在连接列上有相同的值时，才会将这两行组合并包含在结果集中。	结果集只包含两个表中连接列值匹配的行，不包含任何不匹配的行。	SELECT * FROM A INNER JOIN B ON A.col = B.col;
左连接	LEFT JOIN（部分数据库支持 LEFT OUTER JOIN）	以左表（FROM 子句中指定的第一个表）为基础，返回左表中的所有行，对于右表，只返回与左表连接列匹配的行。如果左表中的某行在右表中没有匹配的行，则右表的列值将显示为 NULL。	结果集包含左表的所有行，右表中匹配的行正常显示数据，不匹配的行对应列显示为 NULL。	SELECT * FROM A LEFT JOIN B ON A.col = B.col;
右连接	RIGHT JOIN（部分数据库支持 RIGHT OUTER JOIN）	与左连接相反，以右表为基础，返回右表中的所有行，对于左表，只返回与右表连接列匹配的行。如果右表中的某行在左表中没有匹配的行，则左表的列值将显示为 NULL。	结果集包含右表的所有行，左表中匹配的行正常显示数据，不匹配的行对应列显示为 NULL。	SELECT * FROM A RIGHT JOIN B ON A.col = B.col;

左 右 内连接

-- 创建 orders 表
CREATE TABLE orders (order_id INT PRIMARY KEY,customer_id INT,order_amount DECIMAL(10, 2)
);-- 插入数据
INSERT INTO orders (order_id, customer_id, order_amount) VALUES
(1, 101, 200.00),
(2, 102, 300.00);-- 创建 customers 表
CREATE TABLE customers (customer_id INT PRIMARY KEY,customer_name VARCHAR(50)
);-- 插入数据
INSERT INTO customers (customer_id, customer_name) VALUES
(101, '张三'),
(103, '李四');-- 查询语句
SELECT o.order_id, c.customer_name, o.order_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_amount > 150;

这个查询的执行顺序如下：
FROM：从 orders 表（别名 o）和 customers 表（别名 c）中选取数据。
JOIN（包含 ON）：按照 ON 子句的条件 o.customer_id = c.customer_id，把 orders 表和 customers 表的数据进行关联，生成一个临时的结果集。
WHERE：对关联后的结果集进行过滤，只保留 order_amount 大于 150 的行。
SELECT：从过滤后的结果集中选取 order_id、customer_name 和 order_amount 列。

子查询
也是生成一个虚拟表
select * from course where teacher_id = (select tid from teacher where tname = '谢小二老师')

3 高级功能

3.1 视图（无参函数）

视图
定义：是一个虚拟的表，它是基于一个或多个实际表的查询结果。视图并不实际存储数据，而是在查询时动态地从基础表中获取数据。
作用：可以简化复杂的查询，提供数据的不同视角，并且可以对用户隐藏敏感信息。例如，创建一个视图只显示学生的姓名和成绩，而不显示其他敏感信息，如身份证号码等。
语法示例：在 MySQL 中，创建一个视图来显示学生的基本信息和成绩。

CREATE VIEW student_view AS
SELECT s.student_name, g.grade
FROM students s
JOIN student_grades g ON s.student_id = g.student_id;

由于是虚拟表

SELECT * FROM sales_employees_view;
-- 或者指定列查询
SELECT employee_name, salary FROM sales_employees_view;

3.2 存储过程(有参函数)

定义：是一组预编译的 SQL 语句集合，它可以接受参数、执行一系列的操作，并返回结果。存储过程在数据库服务器上存储和执行，客户端可以通过调用存储过程来执行相应的操作。
作用：可以提高数据库的性能和安全性，减少网络传输开销，并且方便对数据库操作进行集中管理和维护。例如，将复杂的查询逻辑封装在存储过程中，通过传递不同的参数来执行不同的查询操作。
语法示例：在 MySQL 中，创建一个存储过程来查询学生的成绩。

CREATE PROCEDURE get_student_grades(IN student_id INT)
BEGIN//开始SELECT grade FROM student_grades WHERE student_id = student_id;
END;//结束 

IN ：参数的值必须在调用存储过程时指定，0在存储过程中修改该参数的值不能被返回，可以设
置默认值
OUT ：该值可在存储过程内部被改变，并可返回
INOUT ：调用时指定，并且可被改变和返回

使用 call get_student_grades(23);

3.3 触发器

触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行DML操作（ insert ， delete ， update ）时就会激活它执行。

监视对象： table
监视事件： insert 、 update 、 delete
触发时间： before ， after
触发事件： insert 、 update 、 delete

-- 创建 orders 表
CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY,product_name VARCHAR(100),quantity INT
);-- 创建 order_logs 表
CREATE TABLE order_logs (log_id INT AUTO_INCREMENT PRIMARY KEY,order_id INT,action VARCHAR(20),log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);-- 创建触发器
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGININSERT INTO order_logs (order_id, action)VALUES (NEW.order_id, 'INSERT');
END //
DELIMITER ;-- 测试插入数据
INSERT INTO orders (product_name, quantity) VALUES ('手机', 10);-- 查询 order_logs 表验证触发器
SELECT * FROM order_logs; 

假设存在 products 表存储产品信息，product_price_logs 表记录产品价格的更新日志。当 products 表中的产品价格更新时，触发一个触发器记录更新信息。

在 INSERT 型触发器中， NEW 用来表示将要（ BEFORE ）或已经（ AFTER ）插入的新数据；
在 DELETE 型触发器中， OLD 用来表示将要或已经被删除的原数据；
在 UPDATE 型触发器中， OLD 用来表示将要或已经被修改的原数据， NEW 用来表示将要或已经修
改为的新数据；

4 约束

为了实现数据的完整性，对于innodb，提供了以下几种约束，primary key，unique key，foreign key， default, not null

4.1 主键约束

定义：PRIMARY KEY 用于唯一标识表中的每一行记录，一张表只能有一个主键。主键列的值不能为 NULL，且必须是唯一的。
作用：保证数据的唯一性和完整性，同时可以加快数据库的查询速度，因为数据库通常会为主键自动创建索引。

-- 在创建表时定义主键
CREATE TABLE students (student_id INT PRIMARY KEY,student_name VARCHAR(50),age INT
);-- 或者使用复合主键（由多个列组成）
CREATE TABLE orders (order_id INT,product_id INT,quantity INT,PRIMARY KEY (order_id, product_id)
);

4.2 UNIQUE KEY（唯一键约束）

定义：UNIQUE KEY 用于确保列或列组合的值在表中是唯一的，但与主键不同的是，唯一键列可以包含 NULL 值，并且一张表可以有多个唯一键。
作用：保证数据的唯一性，防止重复数据的插入，但不强制要求非空。

-- 在创建表时定义唯一键
CREATE TABLE employees (employee_id INT,employee_email VARCHAR(100),UNIQUE (employee_email)
);-- 也可以在已有表上添加唯一键
ALTER TABLE employees
ADD UNIQUE (employee_id);

4.3 FOREIGN KEY（外键约束）

定义：FOREIGN KEY 用于建立表与表之间的关联关系，一个表中的外键指向另一个表的主键。外键列的值必须是关联表中主键列的有效值，或者为 NULL（如果允许为空）。
作用：保证数据的引用完整性，确保相关表之间的数据一致性。

-- 创建主表
CREATE TABLE departments (department_id INT PRIMARY KEY,department_name VARCHAR(50)
);-- 创建从表，并定义外键
CREATE TABLE employees (employee_id INT PRIMARY KEY,employee_name VARCHAR(50),department_id INT,FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

建立关联关系与保证数据完整性
维护数据一致性：外键用于确保相关表之间的数据一致性。通过在一个表中设置外键，使其指向另一个表的主键，数据库可以保证外键列中的值在关联表的主键列中存在，或者为空（如果允许为空）。这可以防止出现孤立数据，即子表中的记录引用了父表中不存在的主键值，从而保证了数据的完整性和一致性。例如，在员工表和部门表的关联中，员工表中的部门 ID 作为外键，保证了每个员工所属的部门在部门表中是存在的，不会出现员工属于一个不存在的部门的情况。
实现级联操作：外键还可以实现级联操作，如级联删除和级联更新。当在父表中删除或更新一条记录时，通过外键的级联设置，可以自动在子表中进行相应的删除或更新操作，以确保数据的一致性。例如，当删除一个部门时，可以通过外键的级联设置，自动删除该部门下的所有员工记录

4.4 DEFAULT（默认值约束）

定义：DEFAULT 用于为列指定一个默认值，当插入记录时，如果没有为该列提供值，则使用默认值。
作用：简化数据插入操作，确保列始终有一个合理的值。
语法示例：

-- 在创建表时定义默认值
CREATE TABLE products (product_id INT PRIMARY KEY,product_name VARCHAR(100),price DECIMAL(10, 2) DEFAULT 0.00
);-- 插入记录时，如果不指定 price 列的值，将使用默认值
INSERT INTO products (product_id, product_name) 

4.5 NOT NULL（非空约束）

定义：NOT NULL 用于确保列的值不能为空，插入记录时必须为该列提供一个有效的值。
作用：保证数据的完整性，防止插入空值导致数据不完整。
语法示例：

-- 在创建表时定义非空约束
CREATE TABLE customers (customer_id INT PRIMARY KEY,customer_name VARCHAR(50) NOT NULL,email VARCHAR(100)
);-- 插入记录时，customer_name 列必须有值
INSERT INTO customers (customer_id, customer_name, email) VALUES (1, '张三', 'zhangsan@example.com');

5 索引

5.1 索引的定义和作用

定义：索引是一种特殊的数据结构，它存储了表中某些列的值以及这些值对应的行在磁盘上的物理位置（或逻辑位置）。通过索引，数据库可以避免全表扫描，直接定位到包含所需数据的行，从而大大提高查询效率。
作用
加快查询速度：这是索引最主要的作用。例如，在一个包含大量记录的表中，如果要查找某个特定值的记录，没有索引时数据库需要逐行扫描整个表；而有了索引，数据库可以根据索引快速定位到包含该值的行。

5.2 常见的索引类型

主键索引：一种特殊的唯一索引，每个表只能有一个主键索引，用于唯一标识表中的每一行记录。主键索引的列值不能为 NULL，并且在创建表时可以直接指定某列为主键，数据库会自动为主键创建索引。
主键索引：一种特殊的唯一索引，每个表只能有一个主键索引，用于唯一标识表中的每一行记录。主键索引的列值不能为 NULL，并且在创建表时可以直接指定某列为主键，数据库会自动为主键创建索引。例如：

CREATE TABLE students (student_id INT PRIMARY KEY,student_name VARCHAR(50),age INT
);

唯一索引：确保索引列的值是唯一的，但可以包含 NULL 值。一张表可以有多个唯一索引。例如：

CREATE UNIQUE INDEX idx_email ON employees (email);

普通索引：最基本的索引类型，它没有唯一性的限制，主要用于提高查询效率。例如：
CREATE INDEX idx_age ON students (age);
组合索引
对表上的多个列进行索引

INDEX idx(key1,key2[,...]);
**加粗样式**UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

CREATE INDEX idx_multiple_columns ON table_name (column1, column2);

5.3 索引实现

使用b+实现，b+树的优势

查询快：B+ 树是多路平衡的，节点可有多子节点，树矮，查数据时磁盘 I/O 次数少。而且节点内用二分查找定位，速度快。
范围查询强：叶子节点连成有序链表，做范围查询时，找到起始点后沿链表遍历到结束点就行，不用多次从根节点查。
磁盘读写优：节点大小和磁盘块适配，一次 I/O 能读写一个完整节点，减少磁盘碎片化，提升读写效率。
维护成本低：插入和删除数据时，通过分裂、合并等操作自动保持平衡，操作简单，不用复杂重构。
数据有序：索引键有序存，排序查询可直接用这个顺序，不用额外排序。

每一个索引都有一个b+树

5.4 高级分类

聚集索引：一个表只能有一个聚集索引，它决定了表中数据的物理存储顺序。通常，主键索引就是聚集索引，数据记录按照主键的顺序存储在磁盘上。

直接可以找到数据
辅助索引
叶子节点不包含行记录的全部数据；辅助索引的叶子节点中，除了用来排序的 key 还包含一个bookmark ；该书签存储了聚集索引的 key

回表过程
当使用普通索引进行查询时，如果查询的列不在该普通索引中，就需要进行回表操作。具体步骤如下：
第一步：通过普通索引定位主键值：数据库首先根据普通索引的 B + 树结构，找到满足查询条件的索引键，并获取对应的主键值。
第二步：根据主键值在聚集索引中查找数据记录：使用第一步得到的主键值，在聚集索引的 B + 树中进行查找，最终定位到实际的数据记录

联合 B + 树索引（组合索引） 最左匹配原则
联合 B + 树索引是基于多个列创建的索引。例如，对于一个包含(col1, col2, col3)的联合索引，B + 树的索引键是这三个列值的组合。
索引顺序：在联合索引中，列的顺序非常重要。B + 树会首先按照第一列的值进行排序，当第一列的值相同时，再按照第二列的值排序，以此类推。例如，对于联合索引(col1, col2)，查询条件WHERE col1 = ‘value1’ AND col2 = 'value2’可以充分利用该索引；而如果查询条件只有WHERE col2 = ‘value2’，则可能无法使用该联合索引。
范围查询：联合索引对于范围查询也很有用。例如，对于联合索引(col1, col2)，查询条件WHERE col1 = ‘value1’ AND col2 > 'value2’可以利用索引进行高效的范围查询。

覆盖索引
覆盖索引是指一个索引包含了查询语句中所有需要引用的列。当查询可以完全通过索引来满足，而不需要访问表中的数据行时，就可以避免回表操作，从而提高查询性能。

假设有一个orders表，包含order_id（主键）、customer_id、order_date、total_amount等列。现在有一个查询需求：查询每个客户最近一次订单的日期和总金额。

SELECT customer_id, MAX(order_date), total_amount
FROM orders
GROUP BY customer_id;

CREATE INDEX idx_customer_order_date_amount ON orders (customer_id, order_date DESC, total_amount);

5.5 索引失效

对索引列做计算或函数操作
在 SQL 查询里，要是对索引列使用了计算或者函数，索引就会失效。比如WHERE age + 1 = 20，这里对索引列age做了加法运算，数据库无法直接用索引快速定位数据，只能全表扫描。
模糊查询以通配符开头
当用LIKE进行模糊查询时，如果以通配符%开头，像WHERE name LIKE ‘%张三’，索引也会失效。因为索引是按顺序存储数据的，以%开头时，数据库没办法利用索引快速定位。
类型不匹配
要是查询条件里索引列的数据类型和查询值的数据类型不一致，也会导致索引失效。例如，索引列id是整数类型，而查询写成WHERE id = ‘123’，数据库可能不会使用索引。
索引列使用了OR
如果在查询条件中，索引列使用了OR连接多个条件，并且其中部分条件无法使用索引，那么整个索引可能会失效。比如WHERE id = 1 OR name = ‘张三’，若name列没有索引，可能导致id列的索引也无法发挥作用。
范围查询后使用索引列
在范围查询（如>、<、BETWEEN）之后，后续的索引列可能无法使用索引。例如，对于联合索引(col1, col2)，查询WHERE col1 > 10 AND col2 = 20，在col1做了范围查询后，col2可能无法使用索引。