MySQL笔记

MySQL基础笔记

一、初识MySQL

1、什么是数据库

数据库是一个有组织的数据集合，用于存储、管理和检索信息。它通过结构化的方式（如表、行和列）来存储数据，便于高效地进行查询、更新和管理。

2、什么是数据库管理系统（DBMS）

数据库管理系统（DBMS）是用于创建和管理数据库的软件，常见的如MySQL、PostgreSQL和Oracle。

3、MySQL的简介

MySQL是一个开源的关系数据库管理系统（RDBMS），由Oracle公司维护。它以其高性能、可靠性和灵活性而广受欢迎，广泛应用于各种数据密集型应用。

主要特点：

1. 关系型数据库：支持SQL（结构化查询语言）进行数据操作和管理。
2. 开源：可自由使用和修改，拥有活跃的开发者社区。
3. 高性能：优化的存储引擎和查询处理，适合处理大规模数据。
4. 可扩展性：支持从小型应用到大型企业应用的各种规模。
5. 安全性：提供多种认证和访问控制机制，确保数据安全。

应用场景：

• 网站和在线应用的后端数据库
• 数据仓库和分析应用
• 企业应用程序的数据存储

MySQL因其易用性、强大的功能和良好的社区支持，成为许多开发者和企业的首选数据库解决方案。

4、MySQL连接方式

• 1.安装默认端口3306

• 2.以管理员身份启动cmd：

  #开启mysql
  net start mysql80
  #关闭mysql
  net stop mysql80
  

• 3.客户端连接

  #方式一
  MySQL command line client命令窗口
  #方式二 cmd 输入
  mysql -u root -p  
  

  配置MySQLpath环境变量

  mysql安装目录：C:\Program Files\MySQL\MySQL Server 8.0\bin\添加到环境变量中

二、基础操作

名称	解释	命令
DDL	定义和管理数据库对象，如数据库、数据表等	CREATE, DROP, ALTER
DML	用于操作数据库对象中所包含的数据	INSERT, UPDATE, DELETE
DQL	用于查询数据库数据	SELECT
DCL	用于管理数据库的语言，包括管理权限及数据更改	GRANT, COMMIT, ROLLBACK

1、数据库操作

1.1、创建数据库

CREATE DATABASE [IF NOT EXISTS] westos;

解释：

• CREATE DATABASE：这是一个DDL（数据定义语言）命令，用于创建一个新的数据库。
• [IF NOT EXISTS]：可选部分。如果指定，只有在数据库不存在时才会创建该数据库，从而避免错误。
• westos：这是要创建的数据库的名称。

1.2、删除数据库

DROP DATABASE [IF EXISTS] 数据库名;

解释：

• DROP DATABASE：这是一个DDL命令，用于删除一个现有的数据库及其所有数据。
• [IF EXISTS]：可选部分。如果指定，只有在数据库存在时才会删除该数据库，从而避免错误。
• 数据库名：这是要删除的数据库的名称，用户需要替换为实际的数据库名。

1.3、使用数据库

USE `school`;

解释：

• USE：这是一个DQL（数据查询语言）命令，用于选择一个数据库，以便后续的SQL命令在该数据库上下文中执行。
• school：指定要使用的数据库名称。如果数据库名包含特殊字符或空格，必须用反引号（```）包围。

1.4、查看数据库

SHOW DATABASES;

解释：

• SHOW DATABASES：这是一个DQL命令，用于列出当前服务器上所有可用的数据库。这有助于用户查看和选择要操作的数据库。

2、数据表操作

2.1、 创建表

CREATE TABLE [IF NOT EXISTS] 表名 (
    列名1 数据类型 [约束],
    列名2 数据类型 [约束],
    ...
);

• 解释：
  • CREATE TABLE：DDL 命令，用于创建新表。
  • [IF NOT EXISTS]：可选部分，如果指定，只有在表不存在时才会创建。
  • 表名：要创建的表的名称。
  • 列名1 数据类型 [约束]：指定列的名称和数据类型，以及可选的约束（如 NOT NULL, UNIQUE, PRIMARY KEY 等）。

2. 2、删除表

DROP TABLE [IF EXISTS] 表名;

• 解释：
  • DROP TABLE：DDL 命令，用于删除现有表及其所有数据。
  • [IF EXISTS]：可选部分，只有在表存在时才会删除该表。
  • 表名：要删除的表的名称。

2.3、查看表结构

DESCRIBE 表名;

• 解释：
  • DESCRIBE：用于查看指定表的结构，包括列名、数据类型、是否可以为 NULL、键等信息。
  • 表名：要查看的表的名称。

2.4、插入数据

INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);

• 解释：
  • INSERT INTO：DML 命令，用于向表中插入新记录。
  • 表名：要插入数据的表的名称。
  • (列名1, 列名2, ...)：指定要插入的列名。
  • VALUES (值1, 值2, ...)：提供对应列的值。

2.5、 更新数据

UPDATE 表名 SET 列名1 = 新值1, 列名2 = 新值2 WHERE 条件;

• 解释：
  • UPDATE：DML 命令，用于更新表中的现有记录。
  • SET 列名1 = 新值1, 列名2 = 新值2：指定要更新的列及其新值。
  • WHERE 条件：指定更新的条件，只有满足条件的记录会被更新。

2.6、 删除数据

DELETE FROM 表名 WHERE 条件;

• 解释：
  • DELETE FROM：DML 命令，用于删除表中的记录。
  • 表名：要删除记录的表的名称。
  • WHERE 条件：指定删除的条件，只有满足条件的记录会被删除。

2.7、 查询数据

SELECT 列名1, 列名2, ... FROM 表名 WHERE 条件;

• 解释：
  • SELECT：DQL 命令，用于从表中查询数据。
  • 列名1, 列名2, ...：要查询的列名。
  • FROM 表名：指定要查询的表。
  • WHERE 条件：可选部分，限制查询的条件。

2.8、 添加新列

ALTER TABLE 表名 ADD 列名 数据类型 [约束];

• 解释：
  • ALTER TABLE：DDL 命令，用于修改现有表的结构。
  • ADD 列名 数据类型 [约束]：指定要添加的新列及其数据类型和可选约束。

2.9、 删除列

ALTER TABLE 表名 DROP 列名;

• 解释：
  • DROP 列名：用于删除指定的列。

2.10、 修改列

ALTER TABLE 表名 MODIFY 列名 新数据类型 [约束];

• 解释：
  • MODIFY 列名 新数据类型 [约束]：用于修改指定列的数据类型和约束。

3、数据库权限操作

3.1、授予权限

GRANT 权限1, 权限2 ON 数据库名.表名 TO '用户名'@'主机名' IDENTIFIED BY '密码';

解释：

• GRANT：用于授予权限。
• 权限1, 权限2：要授予的权限类型，可以是 SELECT、INSERT、UPDATE、DELETE 等。
• ON 数据库名.表名：指定要授予权限的数据库和表。如果要授予整个数据库的权限，可以使用 数据库名.*。
• TO '用户名'@'主机名'：指定授予权限的用户及其主机。'%' 表示允许从任何主机访问。
• IDENTIFIED BY '密码'：可选部分，指定用户的密码。

3.2、取消权限

REVOKE 权限1, 权限2 ON 数据库名.表名 FROM '用户名'@'主机名';

​ 解释：

• REVOKE：用于撤销权限。
• 权限1, 权限2：要撤销的权限类型。
• ON 数据库名.表名：指定要撤销权限的数据库和表。
• FROM '用户名'@'主机名'：指定要撤销权限的用户及其主机。

3.3、查看权限

SHOW GRANTS FOR '用户名'@'主机名';

​ 解释：

• SHOW GRANTS：用于查看指定用户的权限。
• FOR '用户名'@'主机名'：指定要查看权限的用户

3.4、创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

解释：

• CREATE USER：用于创建新用户。
• '用户名'@'主机名'：指定新用户的名称及其主机。
• IDENTIFIED BY '密码'：指定用户的密码。

3.5、删除用户

DROP USER '用户名'@'主机名';

解释：

• DROP USER：用于删除现有用户。
• '用户名'@'主机名'：指定要删除的用户。

3.6、刷新权限

FLUSH PRIVILEGES;

解释：

• FLUSH PRIVILEGES：用于重新加载权限表，以使更改立即生效。

4、数据类型

4.1、整数类型

数据类型	描述	使用场景
TINYINT	存储范围为 -128 到 127 (或 0 到 255) 的小整数	存储小范围的数字，如用户等级或状态标志
SMALLINT	存储范围为 -32,768 到 32,767 的小整数	存储较小范围的数字，如分类ID
MEDIUMINT	存储范围为 -8,388,608 到 8,388,607 的中等整数	存储中等范围的数字，如订单编号
INT	存储范围为 -2,147,483,648 到 2,147,483,647 的整数	存储常用的整型数据，如用户ID
BIGINT	存储范围为 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 的大整数	存储大范围的数字，如计数器或大数据ID

---

4.2、浮点数类

数据类型	描述	使用场景
FLOAT	单精度浮点数，适合存储不需要高精度的小数	存储价格或测量值，如商品价格
DOUBLE	双精度浮点数，适合存储需要高精度的小数	存储科学计算结果或金融数据
DECIMAL	定点数，精确控制数字的总位数和小数位数	存储货币金额，如银行账户余额

---

4.3、字符串类型

数据类型	描述	使用场景
CHAR	定长字符串，最大长度为 255 字符	存储固定长度的字符串，如国家代码
VARCHAR	可变长度字符串，最大长度为 65,535 字符	存储可变长度的字符串，如用户名或邮箱
TEXT	最大长度为 65,535 字符的长文本	存储大段文本，如文章内容
MEDIUMTEXT	最大长度为 16,777,215 字符的中长文本	存储较长文本，如博客文章或评论
LONGTEXT	最大长度为 4,294,967,295 字符的超长文本	存储非常长的文本，如视频描述或电子书

---

4.4、日期和时间类型

数据类型	描述	使用场景
DATE	存储日期，格式为 ‘YYYY-MM-DD’	存储生日、注册日期等日期信息
TIME	存储时间，格式为 ‘HH:MM’	存储事件发生的具体时间
DATETIME	存储日期和时间，格式为 ‘YYYY-MM-DD HH:MM’	存储事件的时间戳，如订单创建时间
TIMESTAMP	存储时间戳，通常用于记录数据的修改时间	自动记录数据的创建和修改时间
YEAR	存储年份，格式为 1901 到 2155 的年份	存储年份数据，如生产年份

三、查询语言DQL

DQL（Data Query Language）是用于从数据库中检索数据的SQL子集，它最核心的语句是SELECT。通过SELECT语句，可以从表中查询数据并对数据进行筛选、排序、分组等操作

1、基础语句

SELECT column1, column2, ... FROM table_name;

column1, column2,...：表示要查询的列。

table_name：表示要查询的表

SELECT * FROM table_name;

*:表示所有的列

2、条件查询

• WHERE 子句用于指定筛选条件，只返回满足条件的行。

SELECT column1, column2, ... FROM table_name WHERE condition;

condition：查询条件

条件查询操作符

操作符	说明	示例
=	等于	SELECT * FROM employees WHERE salary = 5000;
!= 或 <>	不等于	SELECT * FROM employees WHERE salary != 5000;
>	大于	SELECT * FROM employees WHERE salary > 5000;
<	小于	SELECT * FROM employees WHERE salary < 5000;
>=	大于等于	SELECT * FROM employees WHERE salary >= 5000;
<=	小于等于	SELECT * FROM employees WHERE salary <= 5000;
BETWEEN AND	在某个范围内	SELECT * FROM employees WHERE salary BETWEEN 4000 AND 6000;
LIKE	匹配某种模式（使用通配符 % 表示任意字符或 _表示一个字符）	SELECT * FROM employees WHERE emp_name LIKE 'J%';
IN	在指定的集合中	SELECT * FROM employees WHERE department IN ('HR', 'IT', 'Sales');
IS NULL	为空	SELECT * FROM employees WHERE department IS NULL;
IS NOT NULL	不为空	SELECT * FROM employees WHERE department IS NOT NULL;

3、聚合函数

SELECT COUNT(emp_id) FROM employees;

统计员工的数量

• 常见的聚合函数

聚合函数	说明	示例
COUNT()	计算列中的行数或记录的数量	SELECT COUNT(*) FROM employees;
SUM()	计算数值列的总和	SELECT SUM(salary) FROM employees;
AVG()	计算数值列的平均值	SELECT AVG(salary) FROM employees;
MAX()	返回指定列中的最大值	SELECT MAX(salary) FROM employees;
MIN()	返回指定列中的最小值	SELECT MIN(salary) FROM employees;

4、分组查询

GROUP BY 子句用于将结果集按一个或多个列进行分组。通常与聚合函数一起使用，如：COUNT()、SUM()、AVG()、MAX()、MIN()。

SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 [HAVING condition];

[HAVING condition]:表示对分组后的查询结果进行过滤

• having和where的区别

特性	WHERE	HAVING
作用时机	在分组前过滤记录	在分组后过滤记录
适用范围	用于行级过滤，作用于原始数据	用于分组级过滤，作用于聚合后的数据
作用对象	不能作用于聚合函数（如 SUM()、COUNT()）	能够作用于聚合函数
使用场景	过滤单个记录或行的数据	过滤分组后的结果集
SQL示例	SELECT * FROM employees WHERE salary > 5000;	SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;

5、排序查询

ORDER BY 子句用于对查询结果进行排序，默认是升序(ASC)，可以使用DESC指定降序。

SELECT column1, column2, ... FROM table_name ORDER BY column_name [ASC|DESC];

• **注意：**如果是都字段排序，在第一个字段相同时，才根据第二字段排序

6、分页查询

LIMIT 子句用于限制查询结果的行数

SELECT 列名 
FROM 表名
LIMIT 数量 OFFSET 偏移量;

• 实例

SELECT * 
FROM employees
LIMIT 5 OFFSET 5;

LIMIT 5 OFFSET 5：跳过前 5 行，返回接下来的 5 行。

7、sql的执行顺序

1. FROM：确定要从哪个表中查询数据。
2. JOIN：如果有多个表需要连接，在这里执行表连接。
3. WHERE：筛选满足条件的记录。
4. GROUP BY：根据一个或多个字段对数据进行分组。
5. HAVING：在分组后的结果上进行条件过滤。
6. SELECT：选择要返回的列或计算字段。
7. DISTINCT：去除重复行。
8. ORDER BY：对结果集进行排序。
9. LIMIT：限制返回的结果条数。

四、函数

1、字符串函数

函数名	功能描述	示例	结果
CONCAT()	将多个字符串连接为一个字符串	SELECT CONCAT('a', 'b', 'c');	abc
LENGTH()	返回字符串的长度（字节数）	SELECT LENGTH('MySQL');	5
LOWER()	将字符串转换为小写	SELECT LOWER('HELLO');	hello
UPPER()	将字符串转换为大写	SELECT UPPER('hello');	HELLO
SUBSTRING()	返回字符串的子字符串	SELECT SUBSTRING('MySQL', 2, 3);	ySQ
REPLACE()	替换字符串中的某部分	SELECT REPLACE('abc', 'b', 'd');	adc
TRIM()	去除字符串两端的空格或指定字符	SELECT TRIM(' MySQL ');	MySQL
LPAD()	在字符串左侧填充指定字符到指定长度	SELECT LPAD('abc', 5, 'x');	xxabc
RPAD()	在字符串右侧填充指定字符到指定长度	SELECT RPAD('abc', 5, 'x');	abcxx

---

2、数值函数

函数名	功能描述	示例	结果
ABS()	返回绝对值	SELECT ABS(-10);	10
CEIL()	向上取整	SELECT CEIL(10.2);	11
FLOOR()	向下取整	SELECT FLOOR(10.8);	10
ROUND()	四舍五入到指定小数位	SELECT ROUND(123.456, 2);	123.46
MOD()	返回除法的余数	SELECT MOD(10, 3);	1
POW()	返回指定数字的幂值	SELECT POW(2, 3);	8
SQRT()	返回数字的平方根	SELECT SQRT(16);	4
RAND()	返回0到1之间的随机数	SELECT RAND();	例如 0.8365

---

3、日期函数

函数名	功能描述	示例	结果
NOW()	返回当前日期和时间	SELECT NOW();	2024-09-30 12:00:00
CURDATE()	返回当前日期	SELECT CURDATE();	2024-09-30
CURTIME()	返回当前时间	SELECT CURTIME();	12:00:00
YEAR()	返回指定日期的年份	SELECT YEAR('2024-09-30');	2024
MONTH()	返回指定日期的月份	SELECT MONTH('2024-09-30');	9
DAY()	返回指定日期的天数	SELECT DAY('2024-09-30');	30
DATE()	返回日期时间中的日期部分	SELECT DATE(NOW());	2024-09-30
DATEDIFF()	计算两个日期之间的差值（天数）	SELECT DATEDIFF('2024-12-01', '2024-09-30');	62

---

4、窗口函数

函数名	功能描述	示例	结果
ROW_NUMBER()	为结果集中的每一行分配一个唯一的顺序号	SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary) AS row_num FROM employees;	1, 2, 3, …
RANK()	为结果集中的每一行分配一个排名，如果有相同值则排名相同	SELECT name, salary, RANK() OVER (ORDER BY salary) AS rank FROM employees;	1, 1, 3, 4
DENSE_RANK()	与RANK()相似，但相同值的排名不会有间隔	SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary) AS dense_rank FROM employees;	1, 1, 2, 3
NTILE(n)	将结果集分为n个桶并返回每行所在的桶号	SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;	1, 2, 3, 4
SUM()	计算指定列的累计和	SELECT name, salary, SUM(salary) OVER (ORDER BY salary) AS running_total FROM employees;	累计薪资
AVG()	计算指定列的累计平均值	SELECT name, salary, AVG(salary) OVER (PARTITION BY department) AS avg_salary FROM employees;	部门内平均薪资
LEAD()	获取当前行之后指定行的值	SELECT name, salary, LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary FROM employees;	下一行薪资
LAG()	获取当前行之前指定行的值	SELECT name, salary, LAG(salary, 1) OVER (ORDER BY salary) AS previous_salary FROM employees;	上一行薪资

• 窗口函数实例

以下是一些窗口函数的具体实例：

1. ROW_NUMBER()：为每个员工分配一个基于薪水的唯一顺序号

   SELECT name, salary, 
          ROW_NUMBER() OVER (ORDER BY salary) AS row_num 
   FROM employees;
   

2. RANK()：为每个员工分配一个基于薪水的排名

   SELECT name, salary, 
          RANK() OVER (ORDER BY salary) AS rank 
   FROM employees;
   

3. DENSE_RANK()：与RANK()相似，但排名不留间隔

   SELECT name, salary, 
          DENSE_RANK() OVER (ORDER BY salary) AS dense_rank 
   FROM employees;
   

4. NTILE(4)：将员工薪水分为四个桶

   SELECT name, salary, 
          NTILE(4) OVER (ORDER BY salary) AS quartile 
   FROM employees;
   

5. SUM()：计算薪水的累计总和

   SELECT name, salary, 
          SUM(salary) OVER (ORDER BY salary) AS running_total 
   FROM employees;
   

6. AVG()：计算每个部门内的平均薪水

   SELECT name, salary, 
          AVG(salary) OVER (PARTITION BY department) AS avg_salary 
   FROM employees;
   

7. LEAD()：获取下一个员工的薪水

   SELECT name, salary, 
          LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary 
   FROM employees;
   

8. LAG()：获取上一个员工的薪水

   SELECT name, salary, 
          LAG(salary, 1) OVER (ORDER BY salary) AS previous_salary 
   FROM employees;
   

5、流程函数

函数	功能
IF(value, t, f)	如果value为true，则返回t，否则返回f。
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2。
CASE WHEN [val1] THEN [res1] ... ELSE [default] END	如果val1为true，返回res1，否则返回default默认值。
CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END	如果expr的值等于val1，返回res1，否则返回default默认值。

以下是对每个函数的具体实例，展示如何在MySQL中使用它们：

IF 函数

语法： IF(condition, true_value, false_value)

示例：

SELECT name, IF(score >= 60, '及格', '不及格') AS result
FROM students;

解释： 如果学生的分数 (score) 大于或等于60，则返回"及格"，否则返回"不及格"。

IFNULL 函数

语法： IFNULL(value1, value2)

示例：

SELECT name, IFNULL(email, '无邮箱') AS email_address
FROM users;

解释： 如果用户的邮箱 (email) 为NULL，则返回"无邮箱"，否则返回邮箱地址。

CASE WHEN 函数

语法：

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

示例：

SELECT name, 
       CASE 
           WHEN score >= 90 THEN '优秀'
           WHEN score >= 75 THEN '良好'
           WHEN score >= 60 THEN '及格'
           ELSE '不及格'
       END AS grade
FROM students;

解释： 根据分数 (score) 的不同区间，返回相应的等级（优秀、良好、及格、不及格）。

CASE 函数

语法：

CASE expr 
    WHEN val1 THEN result1
    WHEN val2 THEN result2
    ...
    ELSE default_result
END

示例：

SELECT name, 
       CASE department_id
           WHEN 1 THEN '人事部'
           WHEN 2 THEN '财务部'
           WHEN 3 THEN '技术部'
           ELSE '其他'
       END AS department_name
FROM employees;

解释： 根据员工的部门ID (department_id) 返回相应的部门名称（人事部、财务部、技术部或其他）。

这些实例展示了如何在实际查询中使用这些流程函数，以实现条件判断和数据处理。

五、约束

约束是在数据库表中强加的一些规则，用于确保数据的准确性和完整性。在MySQL中，约束可以在创建表时指定，或者在表创建之后使用 ALTER TABLE 语句添加。常见的约束类型包括：NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK、DEFAULT 等。

1、NOT NULL 约束

• 定义：确保列不能存储 NULL 值。
• 作用：强制字段必须有值，即不能为空。
• 语法：

CREATE TABLE Employees (
    employee_id INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50)
);

在上面的例子中，employee_id 和 first_name 列不能存储空值，而 last_name 则可以为空。

2. UNIQUE 约束

• 定义：确保列中的所有值都是唯一的。
• 作用：防止列中出现重复值，除非列中允许 NULL 值，多个 NULL 值是允许的。
• 语法：

CREATE TABLE Employees (
    employee_id INT NOT NULL,
    email VARCHAR(100) UNIQUE
);

在上面的例子中，email 列中的值必须是唯一的，不能重复。

3. PRIMARY KEY 约束

• 定义：一种组合了 NOT NULL 和 UNIQUE 的约束。每个表只能有一个主键，主键列不能包含 NULL 值。
• 作用：用来唯一标识表中的每一行。
• 语法：

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

在上面的例子中，employee_id 是主键，因此它的值必须唯一，且不能为 NULL。

4. FOREIGN KEY 约束

• 定义：用来在两个表之间建立关系，确保一个表中的值必须存在于另一个表的指定列中。
• 作用：用于维护数据的参照完整性。
• 语法：

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

在上面的例子中，customer_id 是 Orders 表的外键，它引用 Customers 表的 customer_id 列。

5. CHECK 约束

• 定义：用于确保列中的值满足特定的条件（MySQL 8.0.16及以后支持）。
• 作用：强制数据符合定义的条件，保证数据的合法性。
• 语法：

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    age INT CHECK (age >= 18)
);

在上面的例子中，age 列的值必须大于或等于 18。

6. DEFAULT 约束

• 定义：为列设置默认值，当插入记录时，如果没有为该列指定值，则会自动插入默认值。
• 作用：减少手动插入数据时的重复输入，确保字段有默认数据。
• 语法：

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    hire_date DATE DEFAULT CURRENT_DATE
);

在上面的例子中，hire_date 列在没有指定值时，默认使用当前日期。

9. 约束的修改与删除

• 添加约束：可以使用 ALTER TABLE 命令向现有表中添加约束。
• 删除约束：可以使用 ALTER TABLE 命令删除指定的约束。

示例：添加外键

ALTER TABLE Orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id);

示例：删除外键

ALTER TABLE Employees
DROP PRIMARY KEY;

• 总结

约束类型	中文名称	定义	作用
NOT NULL	非空约束	确保列不能存储 NULL 值	强制字段必须有值，不能为空
UNIQUE	唯一约束	确保列中的所有值都是唯一的	防止列中出现重复值
PRIMARY KEY	主键约束	唯一标识每一行数据，不能有 NULL 值，同时具备 UNIQUE 和 NOT NULL 约束	唯一标识表中的每一行数据
FOREIGN KEY	外键约束	建立两个表之间的关系，确保一个表中的值存在于另一个表中	维护表之间的参照完整性
CHECK	检查约束	确保列中的值满足特定的条件	强制数据满足某些条件
DEFAULT	默认值约束	为列提供默认值	插入数据时如果没有指定值，则使用默认值

六、多表查询

多表查询是指通过在 SQL 查询中连接多个表，来获取不同表之间相关联的数据。通常在数据库设计中，数据会被分布在多个表中，涉及到主表和从表之间的关系，比如一对多、多对多等。为了能够从这些表中获取所需的整合数据，我们需要使用多表查询。

1、多表关系

• 一对一关系

案例：用户和用户详情

关系：一对一多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提高效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）

• 一对多关系

案例：部门与员工的关系

关系：一个部门对应多个员工，一个员工对应一个部门

实现：在多的一方建立外键，指向一的一方的主键

• 多对多关系

案例：学生与课程的关系

关系：一个学生可以选修多们课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两个主键

2、内连接(INNER JOIN)

内连接返回两个表中符合连接条件的匹配数据行。它会过滤掉没有匹配的行。

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses
ON students.course_id = courses.course_id;

解释：这条语句返回每个学生及其所选的课程。

3、左外连接 (LEFT JOIN)

左外连接返回左表中的所有数据行，即使右表中没有与之匹配的记录，右表没有匹配时返回 NULL。

SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses
ON students.course_id = courses.course_id;

解释：这条语句返回所有学生的数据，不论他们是否选了课程。如果某个学生没有选课，courses.course_name 将显示 NULL。

4、右外连接 (RIGHT JOIN)

右外连接和左外连接相反，它返回右表中的所有行，即使左表中没有与之匹配的行。

SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses
ON students.course_id = courses.course_id;

解释：这条语句返回所有课程以及相应选课的学生。如果某门课程没有学生选，students.name 将显示 NULL。

5、自连接

自连接是一种特殊的多表查询，其中表本身会与自己进行连接，通常用于比较同一表中不同记录的数据。

SELECT a.column1, b.column1
FROM table a, table b
WHERE a.id = b.parent_id;

解释：从同一个表中查询记录，比较父子关系的 id 字段。

6、全连接 (FULL JOIN)

FULL JOIN 返回左表和右表的所有记录。如果某行在其中一个表中没有匹配，则显示 NULL。MySQL 中不直接支持 FULL JOIN，可以通过 UNION 来模拟。

SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.id = b.id
UNION
SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.id = b.id;

7、联合查询

联合查询（Union Query） 是在 SQL 中将多个 SELECT 查询的结果组合到一起的操作。通过 UNION 操作符，可以将两个或多个 SELECT 查询的结果集联合起来。常见的联合查询操作有两种：UNION 和 UNION ALL。

• union

UNION 操作会合并两个或多个 SELECT 语句的结果集，并自动去除重复的行。因此，结果集中的每一行都是唯一的。

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

解释：将 employees 表和 customers 表中的 name 字段合并，并去除重复的名称。

• union all

UNION ALL 与 UNION 类似，但不会去除重复的行。因此，结果集中可能包含相同的记录，效率上也会比 UNION 更高。

SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

解释：合并多个查询的结果集。不去除重复行，结果集中可能包含重复值。

使用联合查询的规则：

列数和数据类型一致：在进行 UNION 或 UNION ALL 时，每个 SELECT 语句的列数必须相同，且列的数据类型必须兼容。

结果排序：可以使用 ORDER BY 对最终结果进行排序，但 ORDER BY 应在所有 SELECT 语句之后。

8、子查询

MySQL子查询 是指一个查询嵌套在另一个查询中的情况，通常被称为嵌套查询（Nested Query）或内查询（Inner Query）。子查询可以用于 SELECT、INSERT、UPDATE、DELETE 等语句中，帮助完成复杂的查询任务。

• 标量子查询

标量子查询返回单个值，并且可以出现在 SELECT 列表中或 WHERE、HAVING 子句中。

常用操作符：=、<>、 >、 >= 、< 、<=

SELECT name, (SELECT MAX(salary) FROM employees) AS highest_salary
FROM employees

解释：此查询返回 employees 表中的 name 列，并显示 employees 表中最高的工资作为一个列。

• 行子查询

行子查询返回多列，通常用作比较多个字段的条件。

常用操作符：=、<>、in、not in

SELECT name, salary
FROM employees
WHERE (name, salary) = (SELECT name, MAX(salary) FROM employees);

• 表子查询

表子查询可以返回多个值（即多个行和列），并且通常出现在 FROM 子句中。

常用的操作符：in

SELECT e1.name, e1.salary
FROM (SELECT name, salary FROM employees WHERE salary > 5000) AS e1;

• 列子查询

列子查询：子查询返回结果是一列(一列可以有多行)

常用操作符：

• in：在指定集合范围之内，多选一
• not in：不在指定的集合范围之内
• any：子查询返回列表中，有任意一个满组即可 例如：where 条件字段 > any/some ()
• some：与any等同，使用some的地方都可以使用any
• all：子查询返回列表的所有值都必须满足 例如：比所有人都大 where 条件字段 > all ()

#例题1：查询市场部和销售部所有员工的信息，部门表(dept)和员工表(emp)通过外键部门ID(dept_id)关联
select * from emp where dept_id in (select id from dept where name="市场部" or name="销售部")
#例题2：查询比财务部所有员工工资高的员工信息
select * from emp where salary > all(select salary from emp where dept_id in (select id from dept where name="财务部"))

• 子查询中常用的操作符

操作符	作用
IN	判断值是否在子查询的结果集中
NOT IN	判断值是否不在子查询的结果集中
EXISTS	检查子查询是否返回至少一条记录
NOT EXISTS	检查子查询是否不返回任何记录
比较操作符	比较子查询返回的单个值，如 =、>、<、<=、<>
ANY / SOME	检查外部查询中的值是否与子查询返回的任意一个值匹配
ALL	检查外部查询中的值是否与子查询返回的所有值匹配
BETWEEN	检查外部查询中的值是否在子查询返回的结果范围内

七、事务

1、什么是事务

事务是指一组操作，要么全部执行成功，要么全部执行失败，是数据库操作的最小执行单元。事务的主要作用是保证数据的一致性和完整性。它通常由多个 SQL 语句组成，具备“ACID”特性。

2、 事务的四大特性（ACID）

ACID 是事务的四个重要属性，它们分别是：

• A（Atomicity，原子性）： 事务中的操作要么全部成功，要么全部失败回滚，不会出现部分成功、部分失败的情况。
• C（Consistency，一致性）： 事务的执行必须使数据库从一个一致的状态转移到另一个一致的状态。事务开始之前和结束之后，数据库的完整性约束没有被破坏。
• I（Isolation，隔离性）： 并发的事务彼此之间不能互相干扰，一个事务不能看到其他事务正在进行的操作，只有当事务提交后，其他事务才能看到其结果。
• D（Durability，持久性）： 一旦事务提交，其对数据库的修改就会被永久保存，即使系统崩溃也不会丢失。

3、事务的控制语句

在 MySQL 中，可以使用以下命令控制事务：

• START TRANSACTION 或 BEGIN：开始一个事务。
• COMMIT：提交事务，将所有对数据库的更改永久保存。
• ROLLBACK：回滚事务，撤销事务的所有操作，使数据库回到事务开始前的状态。
• SAVEPOINT：设置事务中的保存点，可以回滚到指定的保存点。
• RELEASE SAVEPOINT：删除一个保存点。
• ROLLBACK TO SAVEPOINT：回滚事务到指定的保存点，但不会终止事务。
• SET AUTOCOMMIT：自动提交模式，默认为 1，即每条 SQL 语句都被当作一个单独的事务并自动提交。可以通过设置为 0 禁用自动提交。

4、事务的隔离级别

事务的隔离级别决定了一个事务中所做的更改是否对其他事务可见。在 MySQL 中，有四种隔离级别，每种隔离级别对并发事务的操作有不同的影响。隔离级别越高，并发性越差，但数据的一致性越高。四种隔离级别如下：

• READ UNCOMMITTED（读取未提交）： 最低的隔离级别，一个事务可以读取到其他事务尚未提交的数据（脏读）。这种隔离级别几乎不会被实际使用。
• READ COMMITTED（读取已提交）： 一个事务只能读取到其他事务已经提交的数据。避免了脏读，但可能出现不可重复读的情况，即同一事务内两次读取同一数据，结果可能不同。
• REPEATABLE READ（可重复读）： 保证在同一个事务中多次读取同一数据，结果是一样的，避免了脏读和不可重复读。但是可能会出现幻读的情况。MySQL 的默认隔离级别。
• SERIALIZABLE（可串行化）： 最高的隔离级别，通过强制事务顺序执行，完全避免脏读、不可重复读、幻读的问题，但性能较差。

5、事务中的并发问题

在多个事务并发执行时，可能会出现以下几类问题：

• 脏读（Dirty Read）： 一个事务读取了另一个事务尚未提交的数据。
• 不可重复读（Non-repeatable Read）： 在同一个事务中，两次读取同一行数据，结果却不同。可能是因为另一个事务在两次读取之间修改了数据。
• 幻读（Phantom Read）： 在一个事务中，两次执行相同的查询，但结果集不同。通常是因为另一个事务在两次查询之间插入或删除了数据。

6、事务的使用场景

事务通常用于执行多步数据库操作时，确保所有操作要么完全执行，要么在出错时撤销所有操作。以下是一些常见的使用场景：

• 转账操作：银行账户之间的转账，如果扣除金额成功，但未能增加到另一个账户，则需要回滚。
• 订单系统：在订单处理过程中，必须确保库存减少、订单记录插入、付款成功，这些操作要么全部完成，要么都不执行。
• 数据更新：批量更新数据时，如某些更新失败，则回滚到更新之前的状态。

7、事务的例子

示例1：转账操作

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

如果某个操作失败，可以通过 ROLLBACK 回滚。

示例2：保存点的使用

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

SAVEPOINT savepoint1;

UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- 如果第二个更新操作失败，则回滚到保存点
ROLLBACK TO savepoint1;

COMMIT;

通过保存点，可以灵活控制事务中的部分操作回滚，而不是全部操作都回滚。

8、MySQL 事务引擎

MySQL 支持多种存储引擎，但并非所有引擎都支持事务。常用的事务引擎是 InnoDB，它支持完整的 ACID 特性和事务控制。

• InnoDB：MySQL 中默认的存储引擎，支持事务、外键和行级锁。
• MyISAM：不支持事务，但查询速度更快，适合以读操作为主的应用。

八、视图

视图（View） 是一种虚拟表，它通过SQL查询生成的结果集构成，存储在数据库中。视图本身并不存储数据，而是存储查询逻辑，数据来自于基础表（Base Table）。它使得复杂的查询变得简洁，并能够提高数据的安全性。下面详细介绍视图的特点、创建和使用方法，以及其优点和限制。

1、视图的特点

虚拟表：视图是一张虚拟表，不会占用存储空间，其数据来源于基础表。

动态更新：视图的数据随基础表的数据更新而变化，当基础表的数据变化时，视图的数据也随之变化。

只读：默认情况下，视图的数据是只读的，不能通过视图直接更新基础表的数据，但可以通过某些设置使得视图可更新。

安全性：视图可以通过限制访问部分列或行，提高数据访问的安全性。

2、视图的操作

2.1 创建视图

创建视图时，使用SQL查询定义其数据内容。常见的语法格式如下：

CREATE VIEW 视图名称 AS
SELECT 查询字段
FROM 表名称
[WHERE 条件];

示例：

假设有一张员工表 employees，我们可以创建一个只包含姓名和部门的视图：

CREATE VIEW employee_view AS
SELECT name, department
FROM employees;

创建后的视图 employee_view 相当于一个虚拟表，包含员工的姓名和部门信息。

2.2 查看视图

可以使用 SHOW CREATE VIEW 查看视图的定义：

SHOW CREATE VIEW employee_view;

也可以使用 SELECT 语句查询视图中的数据：

SELECT * FROM employee_view;

2.3 更新视图

默认情况下，视图的数据是不可更新的，但在某些条件下可以允许更新。可以通过 WITH CHECK OPTION 约束保证更新符合视图的定义条件：

CREATE VIEW employee_view AS
SELECT name, department
FROM employees
WHERE department = 'IT'
WITH CHECK OPTION;

示例：

UPDATE employee_view
SET department = 'HR'
WHERE name = 'Alice';

此更新操作将失败，因为它违反了 WITH CHECK OPTION 的条件（department = 'IT'）。

2.4 修改视图

可以通过 ALTER VIEW 修改视图的定义：

ALTER VIEW employee_view AS
SELECT name, salary
FROM employees;

2.5 删除视图

视图可以通过 DROP VIEW 来删除：

DROP VIEW employee_view;

3 、视图的作用

• 简化复杂查询：视图可以封装复杂的SQL查询，使得用户可以像查询普通表一样查询视图，从而简化复杂的查询操作。
• 数据安全性：视图可以隐藏表的某些列或行，限制用户对特定数据的访问。例如，可以通过视图屏蔽掉敏感的数据信息，只展示部分数据。
• 数据隔离：通过视图提供数据的逻辑视图，改变表结构时不需要改变视图，减少应用程序依赖表结构的耦合性。
• 可复用性：视图中的SQL查询可以复用，避免重复书写复杂的查询逻辑。

4、视图的更新

默认情况下，视图是不可更新的，因为它是基于SQL查询的结果集构建的，而不是实际存储数据的表。然而，在某些条件下，视图可以更新基础表的数据。更新视图的操作包括 INSERT、UPDATE 和 DELETE，这些操作最终会反映到视图的基础表上。

4.1、可更新视图的条件

一个视图能否被更新，主要取决于视图的定义以及它与基础表之间的关系。MySQL中的视图如果满足以下条件，通常是可更新的：

• 视图只基于一个表：视图只引用一个基础表，且没有复杂的查询逻辑。
• 没有使用聚合函数：视图中不能包含聚合函数（如 SUM、COUNT、AVG 等），否则视图将不可更新。
• 没有使用 DISTINCT 或 GROUP BY：视图中如果使用了 DISTINCT 或 GROUP BY，则视图不可更新。
• 没有使用子查询或联合查询：视图的定义中不能包含子查询、UNION 等操作。
• 视图中的所有列直接对应基础表的列：视图中的列必须是基础表中的实际列，不能是计算列或表达式。

4.2、 WITH CHECK OPTION 约束

当创建视图时，可以使用 WITH CHECK OPTION 来确保更新操作符合视图的定义条件。WITH CHECK OPTION 的作用是限制对视图的更新操作，使得更新后的数据仍符合视图的查询条件。

CREATE VIEW dept_view AS
SELECT id, name, department
FROM employees
WHERE department = 'Sales'
WITH CHECK OPTION;

此视图 dept_view 只显示属于 Sales 部门的员工。如果尝试通过该视图将某个员工的部门更改为其他部门，如 HR，更新操作将失败，因为它违反了 WITH CHECK OPTION 的约束。

4.3 、视图更新的实际操作

• INSERT 操作：在可更新的视图中，可以插入数据到视图，从而将数据插入到基础表中。

  示例：

  INSERT INTO dept_view (id, name, department)
  VALUES (10, 'Alice', 'Sales');
  

  这将向 employees 表中插入一条新记录。

• UPDATE 操作：通过视图更新数据，实际上是更新基础表中的数据。

  示例：

  UPDATE dept_view
  SET name = 'John'
  WHERE id = 10;
  

• DELETE 操作：可以从视图中删除数据，这将删除基础表中的记录。

  示例：

  DELETE FROM dept_view
  WHERE id = 10;
  

5、视图和同义词的区别

• 同义词 (Synonym)

同义词 是数据库对象（如表、视图、序列等）的别名。同义词的主要作用是为用户提供对数据库对象的别名或简化复杂对象的访问路径。与视图不同，同义词并不会修改查询的结果，它仅仅是一个指向对象的名称。

• 特点：

• 别名：同义词是数据库对象的别名，指向实际的表、视图或其他对象。
• 简化访问：它可以帮助简化数据库对象的访问路径，尤其是在跨数据库或跨模式访问时。
• 不修改数据：同义词不会影响或改变底层对象的结构或数据，它只是提供了一种访问方式。
• 不支持查询修改：同义词本身不提供查询功能，也不能修改查询，它只是对原始对象的别名。

示例：

-- 创建表
CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(50),
    department VARCHAR(50)
);

-- 为表创建同义词
CREATE SYNONYM emp_synonym FOR employees;

-- 使用同义词查询数据
SELECT * FROM emp_synonym;

在这个例子中，emp_synonym 是 employees 表的同义词。用户可以使用 emp_synonym 代替 employees 表进行查询，但它仍然引用原表。

• 总结

区别	视图 (View)	同义词 (Synonym)
定义	视图是基于查询的虚拟表，用于简化复杂查询或隐藏数据。	同义词是数据库对象的别名，用于简化对象的访问路径。
功能	视图可以用于查询、过滤和某些情况下的数据更新。	同义词仅作为对象的别名，不能对数据进行操作。
存储	视图不存储数据，动态获取基础表中的数据。	同义词不存储数据，也不提供动态查询。
可更新性	在一定条件下，视图可以通过 INSERT、UPDATE、DELETE 操作更新基础表。	同义词本质上是别名，不支持更新数据。
复杂性	视图可以封装复杂的查询逻辑，简化用户查询。	同义词不涉及查询逻辑，只是简化对象名称的使用。
用途	用于对复杂查询进行封装、数据隔离、提高安全性。	用于简化跨模式、跨数据库的访问路径，或提供别名。