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

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’@‘%’。
  

综合练习

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

复制代码

2.2 DDL

创建数据库
CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8;
   在 SQL 里，DEFAULT CHARACTER SET 是一个选项，用于设置数据库或表默认使用的字符集。而 utf8 是一种可变长度的字符编码，它能对天下上大部分字符举行编码，涵盖了浩繁语言的字符。因此，DEFAULT CHARACTER SET utf8 意思是将默认字符集设定为 utf8，这意味着后续在该数据库或表中存储数据时，会按照 utf8 编码规则来存储字符。
  删除数据库
DROP DATABASE 数据库名;
选择数据库
USE 数据库名;
创建表

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

复制代码

ENGINE=InnoDB 这个是指定引擎操作
常见数据类型
分类数据类型占用空间取值范围用途示例数值类型TINYINT1 字节有符号：-128 到 127
无符号：0 到 255状态标识（如 0 或 1）SMALLINT2 字节有符号：-32768 到 32767
无符号：0 到 65535较小数目统计INT4 字节有符号：-2147483648 到 2147483647
无符号：0 到 4294967295用户 ID、订单编号BIGINT8 字节更大范围整数大型网站浏览量统计FLOAT4 字节约 7 位十进制精度商品近似价格DOUBLE8 字节约 15 位十进制精度科学盘算数据存储DECIMAL(M,D)取决于 M 和 D精确小数货币金额存储日期和时间类型DATE3 字节‘1000 - 01 - 01’ 到 ‘9999 - 12 - 31’员工入职日期TIME3 字节‘-838:59:59’ 到 ‘838:59:59’会议开始时间DATETIME8 字节‘1000 - 01 - 01 00:00:00’ 到 ‘9999 - 12 - 31 23:59:59’订单创建时间TIMESTAMP4 字节‘1970 - 01 - 01 00:00:01’ UTC 到 ‘2038 - 01 - 19 03:14:07’ UTC数据最后修改时间YEAR1 字节4 位：1901 到 2155
2 位：70 到 69（代表 1970 到 2069）产品生产年份字符串类型CHAR(N)N 字节（N 为 1 - 255）长度固定为 N身份证号码VARCHAR(N)L + 1 字节（L 为实际字符串长度，N 为 1 - 65535）可变长度，最大 N用户昵称、文章标题TINYTEXTL + 1 字节（L < 2^8）可变长度短文本内容TEXTL + 2 字节（L < 2^16）可变长度文章内容、批评MEDIUMTEXTL + 3 字节（L < 2^24）可变长度较长文本内容LONGTEXTL + 4 字节（L < 2^32）可变长度非常长的文本内容二进制类型BINARY(N)N 字节（N 为 1 - 255）固定长度二进制数据固定长度二进制编码VARBINARY(N)L + 1 字节（L 为实际数据长度，N 为 1 - 65535）可变长度二进制数据可变长度二进制编码TINYBLOBL + 1 字节（L < 2^8）可变长度二进制数据小二进制文件（如小图标）BLOBL + 2 字节（L < 2^16）可变长度二进制数据图片、音频片断MEDIUMBLOBL + 3 字节（L < 2^24）可变长度二进制数据中等巨细二进制文件LONGBLOBL + 4 字节（L < 2^32）可变长度二进制数据大型二进制文件（如高清视频）JSON 类型JSON取决于存储的 JSON 数据巨细有效 JSON 数据存储半结构化数据（如用户偏好设置）   一个汉字通常占用3 个字节。不过，一些生僻字可能会占用 4 个字节
  删除表

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

复制代码

选delete就行了 由于支持回滚
2.3 DML

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

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

复制代码

删除
DELETE FROM table_name [WHERE Clause];、

1. CREATE TABLE students (
2.     id INT PRIMARY KEY,
3.     name VARCHAR(50),
4.     age INT
5. );
7. INSERT INTO students (id, name, age) VALUES
8. (1, '张三', 20),
9. (2, '李四', 22),
10. (3, '王五', 21);
11. -- 删除行
12. DELETE FROM students WHERE age = 22;
14. --删除列
15. ALTER TABLE table_name
16. 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
条件查询

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

复制代码

使用where 做判断
查询后排序

1. -- 关键字：order by field, asc:升序, desc:降序
2. SELECT * FROM `score` ORDER BY `num` ASC;
3. -- 按照多个字段排序
4. 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) 忽略 NULLSUM()盘算指定列中数值的总和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])字符类型数据常用于将分组后的字符串类型数据毗连起来 分组查询

1. -- 可以把查询出来的结果根据某个条件来分组显示
2. SELECT `gender` FROM `student` GROUP BY `gender`;
3. -- 分组加聚合
4. SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender`;
5. -- 分组加条件
6. 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;

左 右 内毗连

1. -- 创建 orders 表
2. CREATE TABLE orders (
3.     order_id INT PRIMARY KEY,
4.     customer_id INT,
5.     order_amount DECIMAL(10, 2)
6. );
8. -- 插入数据
9. INSERT INTO orders (order_id, customer_id, order_amount) VALUES
10. (1, 101, 200.00),
11. (2, 102, 300.00);
13. -- 创建 customers 表
14. CREATE TABLE customers (
15.     customer_id INT PRIMARY KEY,
16.     customer_name VARCHAR(50)
17. );
19. -- 插入数据
20. INSERT INTO customers (customer_id, customer_name) VALUES
21. (101, '张三'),
22. (103, '李四');
24. -- 查询语句
25. SELECT o.order_id, c.customer_name, o.order_amount
26. FROM orders o
27. JOIN customers c ON o.customer_id = c.customer_id
28. 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 中，创建一个视图来体现学生的基本信息和成绩。

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

复制代码

由于是假造表

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

复制代码

3.2 存储过程(有参函数)

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

1. CREATE PROCEDURE get_student_grades(IN student_id INT)
2. BEGIN//开始
3.     SELECT grade FROM student_grades WHERE student_id = student_id;
4. 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

1. -- 创建 orders 表
2. CREATE TABLE orders (
3.     order_id INT AUTO_INCREMENT PRIMARY KEY,
4.     product_name VARCHAR(100),
5.     quantity INT
6. );
8. -- 创建 order_logs 表
9. CREATE TABLE order_logs (
10.     log_id INT AUTO_INCREMENT PRIMARY KEY,
11.     order_id INT,
12.     action VARCHAR(20),
13.     log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
14. );
16. -- 创建触发器
17. DELIMITER //
18. CREATE TRIGGER after_order_insert
19. AFTER INSERT ON orders
20. FOR EACH ROW
21. BEGIN
22.     INSERT INTO order_logs (order_id, action)
23.     VALUES (NEW.order_id, 'INSERT');
24. END //
25. DELIMITER ;
27. -- 测试插入数据
28. INSERT INTO orders (product_name, quantity) VALUES ('手机', 10);
30. -- 查询 order_logs 表验证触发器
31. 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，且必须是唯一的。
作用：包管数据的唯一性和完整性，同时可以加快数据库的查询速率，由于数据库通常会为主键自动创建索引。

1. -- 在创建表时定义主键
2. CREATE TABLE students (
3.     student_id INT PRIMARY KEY,
4.     student_name VARCHAR(50),
5.     age INT
6. );
8. -- 或者使用复合主键（由多个列组成）
9. CREATE TABLE orders (
10.     order_id INT,
11.     product_id INT,
12.     quantity INT,
13.     PRIMARY KEY (order_id, product_id)
14. );

复制代码

4.2 UNIQUE KEY（唯一键约束）

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

1. -- 在创建表时定义唯一键
2. CREATE TABLE employees (
3.     employee_id INT,
4.     employee_email VARCHAR(100),
5.     UNIQUE (employee_email)
6. );
8. -- 也可以在已有表上添加唯一键
9. ALTER TABLE employees
10. ADD UNIQUE (employee_id);

复制代码

4.3 FOREIGN KEY（外键约束）

界说：FOREIGN KEY 用于建立表与表之间的关联关系，一个表中的外键指向另一个表的主键。外键列的值必须是关联表中主键列的有效值，大概为 NULL（假如答应为空）。
作用：包管数据的引用完整性，确保相关表之间的数据一致性。

1. -- 创建主表
2. CREATE TABLE departments (
3.     department_id INT PRIMARY KEY,
4.     department_name VARCHAR(50)
5. );
7. -- 创建从表，并定义外键
8. CREATE TABLE employees (
9.     employee_id INT PRIMARY KEY,
10.     employee_name VARCHAR(50),
11.     department_id INT,
12.     FOREIGN KEY (department_id) REFERENCES departments(department_id)
13. );

复制代码

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

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

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

复制代码

4.5 NOT NULL（非空约束）

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

1. -- 在创建表时定义非空约束
2. CREATE TABLE customers (
3.     customer_id INT PRIMARY KEY,
4.     customer_name VARCHAR(50) NOT NULL,
5.     email VARCHAR(100)
6. );
8. -- 插入记录时，customer_name 列必须有值
9. INSERT INTO customers (customer_id, customer_name, email) VALUES (1, '张三', '[email protected]');

复制代码

5 索引

5.1 索引的界说和作用

界说：索引是一种特殊的数据结构，它存储了表中某些列的值以及这些值对应的行在磁盘上的物理位置（或逻辑位置）。通过索引，数据库可以避免全表扫描，直接定位到包罗所需数据的行，从而大大进步查询效率。
作用
加快查询速率：这是索引最重要的作用。比方，在一个包罗大量记录的表中，假如要查找某个特定值的记录，没有索引时数据库必要逐行扫描整个表；而有了索引，数据库可以根据索引快速定位到包罗该值的行。
5.2 常见的索引类型

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

1. CREATE TABLE students (
2.     student_id INT PRIMARY KEY,
3.     student_name VARCHAR(50),
4.     age INT
5. );

复制代码

唯一索引：确保索引列的值是唯一的，但可以包罗 NULL 值。一张表可以有多个唯一索引。比方：

1. CREATE UNIQUE INDEX idx_email ON employees (email);

复制代码

平常索引：最基本的索引类型，它没有唯一性的限制，重要用于进步查询效率。比方：
CREATE INDEX idx_age ON students (age);
组合索引
对表上的多个列举行索引

1. INDEX idx(key1,key2[,...]);
2. **加粗样式**UNIQUE(key1,key2[,...]);
3. 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等列。现在有一个查询需求：查询每个客户迩来一次订单的日期和总金额。

1. SELECT customer_id, MAX(order_date), total_amount
2. FROM orders
3. 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可能无法使用索引。