【2.5w字吐血总结 | 新手必看】全网最详细MySQL笔记

写在前面

鉴于全网MySQL知识点的总结分散难懂、良莠不齐，为了避免初学者少走弯路，更好更快地掌握MySQL知识，博主特地将自己所学的笔记分享出来。

如果想深度理解掌握MySQL，欢迎订阅专栏：MySQL进阶之路【秋说】，本专栏注重实践和案例应用，带你成为优质后端程序员。

---

MySQL基础概念

数据库： 用于存储和组织结构化数据的系统。它是一个电子化的数据仓库，可以方便地管理、访问和更新大量数据。在数据库中，数据按照一定的规则和结构进行组织和存储，以便于对数据进行检索、处理和管理。数据库通常由多个表格（或称为关系）组成，每个表格包含了一组相关的数据字段（列）和数据记录（行）。

数据库管理系统： 用于管理和操作数据库的软件系统。它提供了一系列的工具和功能，用于创建、访问、维护和控制数据库。

常见的数据库管理系统（DBMS）包括： 关系型数据库（如MySQL、Oracle、SQL Server）、非关系型数据库（如MongoDB、Redis）等

表： 在关系型数据库中，表是一种数据结构，用于存储和组织数据。它由行和列组成。表可以被看作是一个二维的数据表格，其中每一列代表一个字段，每一行代表一条记录。

如下图所示：

---

​

SQL语句的分类

• 数据定义语言（Data Definition Language，DDL）：用于创建、修改和删除数据库对象，如数据库、表、视图、索引等。常见的DDL语句包括：

create：创建数据库对象

alert：修改数据库对象的结构

drop：删除数据库对象

• 数据操作语言（Data Manipulation Language，DML）：用于查询、插入、更新和删除数据。常见的DML语句包括：

select：查询数据

insert：插入数据

update：更新数据

delete：删除数据

• 数据查询语言（Data Query Language，DQL）：专门用于查询数据，是DML的子集，用于从数据库中检索数据。
• 数据控制语言（Data Control Language，DCL）：用于授权和权限管理。常见的DCL语句包括：

grant：授予用户权限

revoke：撤销用户权限

• 事务控制语言（Transaction Control Language，TCL）：用于管理数据库中的事务。常见的TCL语句包括：

commit：提交事务

rollback：回滚事务

savepoint：设置保存点

---

MySQL常用命令

• 查看mysql数据库的版本号：

select version();
1

• 启动MySQL服务：（基于Windows）

net start mysql
1

• 停止MySQL服务：

net stop mysql
1

• 登录MySQL数据库：

mysql -u 用户名 -p
1

其中，用户名是MySQL用户名，接着根据系统提示输入密码即可

• 如果安装MySQL时没有设置root密码，可以使用以下命令登录：

mysql -u root
1

• 退出 MySQL 命令行界面：

exit或者quit
1

• 显示所有的数据库名：

show databases;
1

• 创建数据库：

create database 数据库名;
1

• 切换到特定的数据库：

use 数据库名;
1

• 查看当前使用的数据库名：

select database();
1

• 删除数据库：

drop database 数据库名;
1

• 显示数据库中的表：

show tables;
1

• 创建表：

create table 表名 (
  列名1 数据类型,
  列名2 数据类型,
  ...
);
1
2
3
4
5

• 删除表：

drop table 表名;
1

• 向表中插入数据：

insert into 表名(列名1, 列名2, ...)
VALUES (具体值1, 具体值2, ...);
1
2

• 在表中查询特定的数据：

select 列名1, 列名2, ...
from 表名
where 查询条件;
1
2
3

不需要查询条件时：

select 列名1, 列名2, ...
from 表名;
1
2

• 获取某表的所有数据：

select * from 表名 
1

• 获取某表的结构：

desc 表名;  

也可使用   

describe 表名;
1
2
3
4
5

• 更改表中某列的名字：

alert table 表名 rename column 原始列名 to 新列名;
1

• 为表中某列指定一个别名（并不改变表结构中列的名称）

select 原始列名 as 新列名 from 表名;
1

---

MySQL数学运算语句

1. 加法：使用 + 运算符执行加法运算。

select column1 + column2 as sum from 表名;  //执行加法运算后的结果列指定别名为sum

select column1 + 10 from 表名;  //也可以不指定别名
1
2
3

2. 减法：使用 - 运算符执行减法运算。

select column1 - column2 as reduce from 表名;
1

3. 乘法：使用 * 运算符执行乘法运算。

select column1 * column2 as ride from 表名;

select column1 * 10 as ride from 表名;   //数值扩大十倍
1
2
3

4. 除法：使用 / 运算符执行除法运算。

select column1 / column2 as division from 表名;
1

5. 取余：使用 % 运算符计算两个数的余数。

select column1 % column2 as remainder from 表名;
1

6. 其它运算： POWER() 函数用于计算幂次方，SQRT() 函数用于计算平方根，ABS() 函数用于获取绝对值等等。

---

MySQL条件查询

MySQL 中的条件查询是通过使用 WHERE 子句来筛选满足特定条件的数据。

MySQL 中条件查询的基本语法：

SELECT column1, column2, ...  。。列名
FROM table_name   //表名
WHERE condition;  //筛选条件
1
2
3

示例：

1. 等于 (=) 运算符：

SELECT * FROM table_name WHERE column_name = value;
//也可指定特定的列
1
2

2. 不等于 (<> 或 !=) 运算符：

SELECT * FROM table_name WHERE column_name <> value;
1

3. 大于 (>) 运算符：

SELECT * FROM table_name WHERE column_name > value;
1

4. 小于 (<) 运算符：

SELECT * FROM table_name WHERE column_name < value;
1

5. 大于等于 (>=) 运算符：

SELECT * FROM table_name WHERE column_name >= value;
1

6. 小于等于 (<=) 运算符：

SELECT * FROM table_name WHERE column_name <= value;
1

7. BETWEEN运算符

//第一种方式
SELECT * FROM table_name WHERE column_name >= value1 AND column_name <= value2;

//第二种方式
SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
//value1必须小于value2
1
2
3
4
5
6

8. 模糊匹配 (LIKE) 运算符：

SELECT * FROM table_name WHERE column_name LIKE pattern;

条件查询还可以通过逻辑运算符（例如 `AND`, `OR`, `NOT`）组合多个条件。
1
2
3

9. NULL运算符

SELECT * FROM table_name WHERE column_name IS NULL;
1

这将返回所有 column_name 列中值为 NULL 的行。

10. IN运算符

IN 运算符用于在查询中匹配多个值。它允许您指定一个值列表，并检查某个列是否与该列表中的任何值匹配。

SELECT * FROM table_name WHERE column_name IN (value1, value2, value3);
1

例如：

SELECT * FROM users WHERE country IN ('USA', 'Canada', 'UK');
1

这将返回所有国家为 “USA”、“Canada” 或 “UK” 的用户。

注意：IN后跟的是具体的值，并不是一个区间。

11. AND 运算符：AND 运算符将多个条件连接起来，并要求所有条件都满足才能返回结果。如果任何一个条件不满足，那么整个条件将被视为不满足。例如：

SELECT * FROM table_name WHERE condition1 AND condition2;
1

在上述查询中，只有当 condition1 和 condition2 都满足时，才会返回满足条件的行。

12. OR 运算符：OR 运算符将多个条件连接起来，并要求至少一个条件满足才能返回结果。如果所有条件都不满足，那么整个条件将被视为不满足。例如：

SELECT * FROM table_name WHERE condition1 OR condition2;
1

在上述查询中，只要 condition1 或者 condition2 中至少一个满足，就会返回满足条件的行。

在MySQL中，AND 运算符的优先级高于OR运算符。这意味着当查询中同时存在AND和OR运算符时，AND运算符的条件会首先进行计算，然后再计算OR运算符的条件。

例如，对于以下查询：

SELECT * FROM table_name WHERE condition1 AND condition2 OR condition3;
1

根据运算符优先级，条件 condition1 和 condition2 会首先进行计算，然后再计算 condition3。所以上述查询可以等价地写为：

SELECT * FROM table_name WHERE (condition1 AND condition2) OR condition3;
1

可以以使用括号来明确指定优先级，以控制运算符的组合方式。例如：

SELECT * FROM table_name WHERE (condition1 OR condition2) AND condition3;
1

在上述查询中，condition1 或者 condition2 至少满足一个，并且同时还要满足 condition3 才会返回满足条件的行。

13. %运算符：

在MySQL中，% 运算符是用于模糊匹配的通配符。它通常与 LIKE 关键字一起使用，用于在查询中匹配特定的模式。

% 表示零个或多个字符的任意序列。当放置在模式字符串的开头、中间或结尾时，它可以匹配任意长度和任意字符的序列。

以特定字符开头的模式：pattern%

例如，SELECT * FROM table_name WHERE column_name LIKE 'abc%' 会匹配 column_name 列中以 “abc” 开头的任何字符串，如 “abcd”、“abc123” 等。

以特定字符结尾的模式：%pattern

例如，SELECT * FROM table_name WHERE column_name LIKE '%xyz' 会匹配 column_name 列中以 “xyz” 结尾的任何字符串，如 “abcxyz”、“123xyz” 等。

包含特定字符的模式：%pattern%

例如，SELECT * FROM table_name WHERE column_name LIKE '%abc%' 会匹配 column_name 列中包含 “abc” 的任何字符串，如 “abc”, “abcdef”, “xyzabcxyz” 等。

14. _运算符：

在MySQL中，下划线_运算符是用于模糊匹配的通配符之一，通常与LIKE关键字一起使用。它表示匹配任意单个字符的位置。

例如，SELECT * FROM table_name WHERE column_name LIKE 'a_b' 可以匹配 column_name 列中以 “a” 开头，然后是任意一个字符，最后是 “b” 结尾的字符串，如 “aab”、“acb” 等。

---

MySQL排序

在MySQL中使用 ORDER BY 子句来对结果进行排序。ORDER BY 子句允许指定一个或多个列作为排序依据，并指定升序（ASC）或降序（DESC）进行排序。

1. 单列排序：

SELECT * FROM table_name ORDER BY column_name ASC;
1

上述查询将按照 column_name 列的升序对结果进行排序。

2. 多列排序：

SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;
1

上述查询将首先按照 column1 列的升序进行排序，对于相同的值，则按照 column2 列的降序进行排序。

3. 如果在查询中使用了列别名，则可以通过别名进行排序：

SELECT column_name AS alias_name FROM table_name ORDER BY alias_name ASC;
1

指定了要选择的列 column_name，并使用 AS 关键字将其命名为 alias_name，并按照 alias_name 列进行升序排序。

默认情况下，排序是升序的（ASC）。如果要进行降序排序，可以使用 DESC 关键字。示例如下：

SELECT * FROM table_name ORDER BY column_name DESC;
1

注意，ORDER BY子句应该位于查询语句的末尾。如果查询中使用了LIMIT子句，则结果将首先根据排序顺序进行排序，然后再进行限制。

4. 数字排序：

SELECT * FROM table_name ORDER BY 1(数字);
1

排序时将结果集中在第一个字段

举个例子：

假设有一个名为 employees 的表，其中包含以下字段：id和name。

SELECT * FROM employees ORDER BY 1 ASC;
1

上述查询将按照结果集中的第一个列（即 id 列）进行升序排序。即按照员工的身份证号进行由小到大排序。

---

MySQL数据处理函数

（1）单行处理函数：指在SQL中用于处理单个值的函数。它们接受一个或多个输入参数，并返回一个单一的结果。

以下是一些常见的 SQL 单行处理函数：

UPPER()：将字符串转换为大写。例如：SELECT UPPER('hello') 将返回 'HELLO'。

LOWER()：将字符串转换为小写。例如：SELECT LOWER('WORLD') 将返回 'world'。

LENGTH()：返回字符串的长度。例如：SELECT LENGTH('abcde') 将返回 5。

SUBSTRING()：提取字符串的一个子串。例如：SELECT SUBSTRING('Hello,World!', 1, 5) 将返回 'Hello'。

如果我们将参数 1 改为 7，即 SELECT SUBSTRING(‘Hello, World!’, 7, 5)，结果将返回子串 ‘World’，因为它从第 7 个字符开始提取，长度为 5 个字符。

CONCAT()：将多个字符串连接起来。例如：SELECT CONCAT('Hello', ' ', 'World') 将返回 'Hello World'。

TRIM()：去除字符串前后的空格。例如：SELECT TRIM(' hello ') 将返回 'hello'。

ROUND()：对数值进行四舍五入。例如：SELECT ROUND(3.14159, 2) 将返回 3.14。

ABS()：返回数值的绝对值。例如：SELECT ABS(-10) 将返回 10。

COALESCE()：返回参数列表中第一个非空的值。例如：SELECT COALESCE(NULL, 'default') 将返回 'default'。

NOW()：返回当前日期和时间。例如：SELECT NOW() 将返回当前的日期和时间。

（2）多行处理函数：指在 SQL 中用于处理多个值的函数。它们接受多个输入参数，并返回一个结果集，可以包含多行和多列。

以下是一些常见的 SQL 多行处理函数：

COUNT()：计算满足条件的行数。例如：SELECT COUNT(*) FROM employees 将返回 employees 表中的行数。

对于查询 SELECT COUNT(employee_id) FROM employees，它将返回 employees 表中 employee_id 列中非空值的行数。

SUM()：计算数值列的总和。例如：SELECT SUM(salary) FROM employees 将返回 employees 表中所有员工的薪资总和。

AVG()：计算数值列的平均值。例如：SELECT AVG(salary) FROM employees 将返回 employees 表中所有员工的薪资平均值。

MIN() 和 MAX()：分别返回数值列的最小值和最大值。例如：SELECT MIN(salary), MAX(salary) FROM employees 将返回 employees 表中员工的最低和最高薪资。

GROUP_CONCAT()：将多行值连接为一个字符串。例如：SELECT GROUP_CONCAT(name) FROM employees 将返回 employees 表中所有员工姓名的逗号分隔列表。

GROUP BY 子句：按照指定的列对结果进行分组。例如：SELECT department, AVG(salary) FROM employees GROUP BY department 将按部门计算员工薪资的平均值，并对结果进行分组。

HAVING 子句：在 GROUP BY 分组结果上进行过滤。例如：SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 5000 将返回薪资平均值大于 5000 的部门。

---

MySQL分组查询

分组查询是一种在 SQL 中使用 GROUP BY 子句对结果进行分组的操作。它结合了聚合函数，可以对数据集按照一个或多个列进行分组，并对每个分组应用聚合函数计算。

SELECT 列1, 列2, ..., 聚合函数(column)
FROM 表名
GROUP BY 列1, 列2, ...
1
2
3

列1, 列2, ... 表示要分组的列，可以指定一个或多个列。在 SELECT 语句中，除了分组列外，还可以选择其他列。而 聚合函数(column) 是对数据进行聚合计算的函数，可以是 SUM()、COUNT()、AVG()、MAX()、MIN() 等聚合函数。

例如，假设我们有一个 orders 表，其中包含 order_id、product_id 和 quantity 列。我们可以使用以下查询来计算每个产品的总销售数量：

SELECT product_id, SUM(quantity) AS total_quantity
FROM orders
GROUP BY product_id;
1
2
3

这个查询将根据 product_id 列将订单表中的数据进行分组，并使用 SUM() 聚合函数计算每个产品的总销售数量。结果将包含每个产品的 product_id 和对应的总销售数量。

当存在group by与order by关键字时，执行顺序如下：

SELECT ...
FROM ...
WHERE...
GROUP BY ...
ORDER BY ...;
1
2
3
4
5

首先，根据 FROM 和 WHERE 子句的条件从表中选择相关的行。
然后，根据 GROUP BY 子句中指定的列将结果集分成不同的分组。
接下来，对每个分组进行聚合计算，例如使用 SUM()、COUNT()、AVG() 等聚合函数。
最后，使用 SELECT 子句选择要返回的列，并在必要时进行别名定义。

如果存在 ORDER BY 子句，会在最后对结果进行排序。

HAVING子句：

HAVING 子句用于在 GROUP BY 子句之后对分组进行条件过滤。

假设我们有一个 orders 表包含订单信息，其中包括 product_id、quantity 和 price 列。我们想要找出销售数量大于 100 且总金额大于 5000 的产品。

SELECT product_id, SUM(quantity) AS total_quantity, SUM(quantity * price) AS total_amount
FROM orders
GROUP BY product_id
HAVING total_quantity > 100 AND total_amount > 5000;
1
2
3
4

在这个查询中们首先使用 GROUP BY 子句将 orders 表按照 product_id 进行分组。然后，使用 SUM() 函数计算每个产品的总销售数量（命名为 total_quantity）和总金额（命名为 total_amount）。

接下来，使用 HAVING 条件 total_quantity > 100 AND total_amount > 5000，即只返回销售数量大于 100 且总金额大于 5000 的产品。

注意，HAVING子句是在GROUP BY分组之后进行条件过滤，与WHERE子句不同，WHERE子句是在分组之前对原始数据进行条件过滤的。

---

MySQL查询结果去重

DISTINCT 关键字用于从结果集中选择唯一的记录，并去除重复。

假设我们有一个 customers 表，包含客户的信息，其中包括 customer_id 和 email 列，部分用户用的邮箱地址可能是一致的，均为公司邮箱。我们希望获取所有唯一的邮箱地址。

SELECT DISTINCT email
FROM customers;
1
2

对于每个邮箱地址，只会返回一条记录，重复的记录会被去除。

注意，如果在 SELECT 语句中选择多列，并且希望根据多列进行唯一性判断和去重，只需将多列包含在 SELECT DISTINCT 子句中。

SELECT DISTINCT column1, column2
FROM table;
1
2

这样将根据 column1 和 column2 的组合来做为判断唯一性的依据，返回唯一的记录。

---

MySQL连接查询

连接查询用于在多个表之间建立关系，并检索满足特定条件的数据。

常见的连接类型如下：

1. 内连接（INNER JOIN）：返回两个表中满足连接条件的匹配行。只有在两个表中都存在匹配的值时，才会返回结果。

SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
1
2
3

2. 等值连接（Equi Join）：一种基于相等条件进行连接的连接类型。在等值连接中，两个表通过一个或多个列的值相等来建立连接。

假设我们有两个表：employees 和 departments，它们分别包含员工的信息和部门的信息。这两个表中都有一个共同的列 department_id，我们可以使用该列来进行等值连接，以获取每个员工所在的部门。

SELECT *
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
1
2
3

在这个查询中，我们使用了 INNER JOIN 进行连接，连接条件是 employees.department_id = departments.department_id。这意味着只有在 employees 表中的 department_id 值等于 departments 表中的 department_id 值时，才会返回匹配的行。

3. 非等值连接（Non-Equi Join）：一种基于不相等条件进行连接的连接类型。在非等值连接中，两个表根据不相等的条件来建立连接。

假设我们有两个表：orders 和 discounts，分别包含订单信息和折扣信息。我们想要找出订单总额大于折扣金额的订单。

SELECT orders.order_id, orders.total_amount, discounts.discount_amount
FROM orders, discounts
WHERE orders.total_amount > discounts.discount_amount;
1
2
3

在这个查询中，使用了 WHERE 子句来指定连接条件，即 orders.total_amount > discounts.discount_amount。这表示只有在 orders 表中的 total_amount 值大于 discounts 表中的 discount_amount 值时，才会返回匹配的行。

4. 自连接（Self Join）：指在同一个表内进行连接操作。在自连接中，我们将表视为两个不同的实体，并使用相同表的别名来建立连接关系。

假设我们有一个表 employees，其中包括员工的姓名和直接上级的员工ID。我们想要获取每个员工及其直接上级的姓名。

SELECT e.employee_name, m.employee_name AS manager_name
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id;
1
2
3

在这个查询中，我们使用了别名 e 和 m 来表示同一个表 employees 的两个实体。我们通过连接条件 e.manager_id = m.employee_id 来建立员工与其直接上级员工的连接关系。

5. 左连接（LEFT JOIN）：返回左表的所有行，以及与右表满足连接条件的匹配行。如果右表中没有与左表匹配的行，则对应的右表列将显示为 NULL。

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
1
2
3

6. 右连接（RIGHT JOIN）：返回右表的所有行，以及与左表满足连接条件的匹配行。如果左表中没有与右表匹配的行，则对应的左表列将显示为 NULL。

SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
1
2
3

7. 全连接（FULL JOIN）：返回左表和右表的所有行，并将它们组合在一起。如果某行在其中一个表中有匹配，而在另一个表中没有匹配，对应的列将显示为 NULL。

SELECT *
FROM table1
FULL JOIN table2 ON table1.column = table2.column;
1
2
3

上述1、2、3、4均为内连接，5、6、7均为外连接。

内连接与外连接的区别是：

1. 匹配方式不同：内连接（Inner Join）基于连接条件匹配两个表中的行，只返回满足连接条件的匹配行。而外连接（Outer Join）则返回符合连接条件的匹配行，以及未能匹配到的行。
2. 结果集不同：内连接仅返回连接条件匹配的行，因此结果集中只包含共有的行。外连接则可以返回连接条件匹配的行，也可以返回某个表中没有匹配项的行。
3. 表的顺序：内连接和外连接的表的顺序可以影响结果集。对于内连接来说，调换连接顺序不会改变最终结果。而对于外连接来说，左外连接和右外连接的表的顺序会影响返回结果。
4. 使用场景不同：内连接适用于需要获取两个表中相互匹配的数据的情况，例如获取员工和部门信息的内连接查询。而外连接常用于需要显示关联表中所有数据，包括没有匹配的数据的情况，例如获取所有员工及其薪水信息的左外连接查询。

---

MySQL子查询

子查询（Subquery）是指在一个查询语句中嵌套另一个完整的查询语句。子查询可以在主查询的条件、列列表或者其他子查询中使用，用于提供额外的数据或者限制结果集。

1. WHERE子句中的子查询：
   在WHERE子句中使用子查询可以作为条件来过滤主查询的结果集。子查询通常会返回一个值或者一组值，用于与主查询的条件进行比较。

例如，我们可以使用子查询来找出超过平均年龄的员工：

SELECT employee_name, age
FROM employees
WHERE age > (SELECT AVG(age) FROM employees);
1
2
3

在这个查询中，子查询 (SELECT AVG(age) FROM employees) 返回了员工表中年龄的平均值，然后将其与主查询中的每个员工的年龄进行比较，以筛选出年龄超过平均值的员工。

2. FROM子句中的子查询：
   在FROM子句中使用子查询可以将子查询作为临时表或视图来处理。子查询会被执行，并将其结果作为一个临时表供主查询使用。

假设我们有两个表：orders（包含订单信息）和 customers（包含客户信息）。我们想要找出每个客户的订单数量。

SELECT c.customer_id, c.customer_name, o.order_count
FROM customers c
JOIN (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) o ON c.customer_id = o.customer_id;
1
2
3
4
5
6
7

在这个查询中，内部的子查询 (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) 返回了每个客户的订单数量。然后将该子查询作为临时表 o，并与 customers 表进行连接操作。

3. select列表中的子查询：

可以在SELECT列表中使用子查询来检索特定的数据，并将其作为一个列返回。

例如，我们可以使用子查询来计算每个客户的订单数量，并将其作为一个新的列返回：

SELECT customer_id, (SELECT COUNT(*) FROM orders WHERE customer_id = customers.customer_id) AS order_count
FROM customers;
1
2

在这个查询中，子查询 (SELECT COUNT(*) FROM orders WHERE customer_id = customers.customer_id) 会计算每个客户的订单数量，并将其作为名为 order_count 的新列返回。

---

MySQL部分操作符

1. Union：

UNION 是一个用于合并两个或多个 SELECT 语句结果集的操作符。它将多个 SELECT 查询的结果合并为一个单一的结果集，且去除重复的行。

合并两个结果集：
可以使用 UNION 操作符合并两个具有相同列数和数据类型的结果集。

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
1
2
3

这个查询将会合并 table1 和 table2 的结果集，并返回一个包含两个表的唯一行的结果集。

去除重复行：
UNION 操作符会自动去除重复的行，只返回唯一的行。

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
1
2
3

这个查询将会合并 table1 和 table2 的结果集，并返回一个包含唯一值的结果集。

结果集列的顺序和数据类型：
UNION 操作要求两个 SELECT 查询的结果集具有相同的列数、列的顺序和数据类型。如果不满足这些要求，可以使用显式的列别名来调整列的顺序或进行数据类型转换。

SELECT column1 AS name, column2 AS age FROM table1
UNION
SELECT column3 AS name, CAST(column4 AS INT) AS age FROM table2;
1
2
3

这个查询会将 table1 和 table2 的结果集合并，并使用别名来调整列的顺序和进行数据类型转换。

注意： 使用 UNION 操作符时，需要确保两个或多个 SELECT 查询的结果集具有相同的列数、列的顺序和数据类型。同时，需要 UNION 操作是对结果集进行去重的，如果需要包含重复行，可以使用 UNION ALL 操作符。

2. Limit：

LIMIT 关键字用于限制 SQL 查询返回的结果行数量。它可以与 SELECT 语句一起使用，以便从结果集中选择指定数量的行。

返回前几行：
使用 LIMIT 可以返回查询结果中的前几行。

SELECT column1, column2 FROM table1 LIMIT 5;
1

这个查询会返回 table1 中的前 5 行数据。

跳过前几行并返回接下来的行：
除了限制返回的行数，还可以通过使用 OFFSET 子句来跳过前几行。

SELECT column1, column2 FROM table1 LIMIT 10 OFFSET 5;
1

这个查询会跳过 table1 的前 5 行，并返回接下来的 10 行数据。

简化的写法：
可以直接使用逗号分隔的两个参数，而不是使用 OFFSET 子句。

SELECT column1, column2 FROM table1 LIMIT 5, 10;
1

这个查询与前一个示例中的查询是等效的，会跳过 table1 的前 5 行，并返回接下来的 10 行数据。

含有操作符的SQL查询语句模板：

select
...
from
...
where
...
group by
...
having
...
order by
...
limit
...
1
2
3
4
5
6
7
8
9
10
11
12
13
14

---

MySQL之增删改查

表的创建：

CREATE TABLE 语句用于在数据库中创建新的数据表，并定义表的列以及相关的属性。

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    column3 data_type,
    ...
    PRIMARY KEY (column_name)
);
1
2
3
4
5
6
7

其中，table_name 是要创建的表的名称，column1, column2, column3 是表中的列名，data_type 是列的数据类型。

如果某一列是主键，可以使用 PRIMARY KEY 子句指定，例如 PRIMARY KEY (column_name)。

以下是一个示例，创建一个名为 customers 的表，具有 id、name 和 email 三个列，并将 id 列作为主键：

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);
1
2
3
4
5

上述语句将在数据库中创建一个名为 customers 的表，该表包含一个整数类型的 id 列、一个最大长度为 50 的字符串类型的 name 列和一个最大长度为 100 的字符串类型的 email 列，并将 id 列设为主键。

常见的数据类型：

INT（整数）：用于表示整数值，如 1、-5、100。

FLOAT（浮点数）：用于表示带有小数的数值，如 3.14、-0.5、2.0。

DECIMAL（高精度小数）：用于表示具有高精度小数的数值，适用于需要更精确计算的场景。

VARCHAR（可变长度字符串）：用于表示可变长度的字符串，可以存储不同长度的文本数据。

CHAR（固定长度字符串）：用于表示固定长度的字符串，存储时会将字符串补齐到指定长度，适用于长度固定的数据。

BOOLEAN（布尔值）：用于表示真或假的值，通常用于存储逻辑判断结果。

DATE（日期）：用于表示日期， 它通常以 YYYY-MM-DD 的格式表示。 如 ‘2023-01-11’。

TIME（时间）：用于表示时间，如 ‘15:30:00’。

DATETIME：用于表示精确的时间， 它通常以 YYYY-MM-DD HH:MM:SS 的格式表示。 如’2023-01-11 15:30:00’。

DATETIME/TIMESTAMP（日期时间戳）：用于表示日期和时间的组合，包括日期、时间和时区。

BLOB（二进制大对象）：用于存储二进制数据，如图像、音频、视频等非文本数据。

表的删除：

DROP TABLE 语句用于从数据库中永久删除现有的表。

DROP TABLE table_name;
1

其中，table_name 是要删除的表的名称。

INSERT插入数据：

INSERT INTO 语句用于将新行或记录插入到数据库表中。

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
1

其中，table_name 是要插入数据的表的名称。在括号中，指定要插入数据的列名（如果不是插入所有列）和对应的值。

以下是一个示例，向名为 customers 的表插入数据：

INSERT INTO customers (name, email, phone) VALUES ('John Doe', 'john@example.com', '1234567890');
1

上述语句将向 customers 表中插入一条新记录，包含 name、email 和 phone 列的数据。

如果要一次性插入多行数据，可以使用多个 VALUES 子句，如下所示：

INSERT INTO customers (name, email, phone) VALUES ('John Doe', 'john@example.com', '1234567890'), ('Jane Smith', 'jane@example.com', '9876543210');
1

上述语句将向 customers 表中同时插入两条新记录。

**注意：**插入日期类型（DATE）的数据时，不可使用单引号将日期值括起来。相反，应该按照特定的日期格式直接提供日期值。

以下是一个示例，向名为 orders 的表插入日期数据：

INSERT INTO orders (order_date) VALUES (2023-01-11);
1

在上述示例中，我们将日期值 2023-01-11 直接插入到 order_date 列中。不需要使用单引号将日期值括起来。

update更新语句：

UPDATE语句用于更新数据库表中的数据。它允许您指定要更新的表、列和新值，以及一个可选的WHERE子句来限制更新的行。

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

其中，表名 是要更新的表的名称，列名1, 列名2, ... 是要更新的列的名称，新值1, 新值2, ... 是对应列的新值，条件 是可选的WHERE子句，用于限制更新的行。

例如，假设我们有一个名为 customers 的表，其中包含列 name、age 和 email。如果要将名为 “John” 的客户的年龄更新为 30 岁，可以使用以下UPDATE语句：

UPDATE customers
SET age = 30
WHERE name = 'John';
1
2
3

delete删除语句：

DELETE语句用于从数据库表中删除行。它允许您指定要删除的表和一个可选的WHERE子句来限制删除的行。

DELETE FROM 表名
WHERE 条件;
1
2

其中，表名 是要删除的表的名称，条件 是可选的WHERE子句，用于限制删除的行。

例如，假设我们有一个名为 customers 的表，其中包含列 name、age 和 email。如果要删除名为 “John” 的客户的记录，可以使用以下DELETE语句：

DELETE FROM customers
WHERE name = 'John';
1
2

上述语句将在 customers 表中找到名为 “John” 的行，并将其删除。

注意： 如果没有指定WHERE子句，DELETE语句将会删除表中的所有行，导致表被完全清空。

表的复制：

流程如下：使用SELECT语句，选择要复制的原始表中的数据，并将其插入到新创建的表中。
使用CREATE TABLE语句创建一个新的目标表，定义列和数据类型。
使用INSERT INTO语句和SELECT语句的组合，从原始表中选择数据并将其插入到新的目标表中。

例如，假设我们有一个名为 employees 的表，并且我们想要创建一个名为 employees_copy 的表来复制所有员工的信息。我们可以按照以下步骤进行操作：

使用CREATE TABLE语句创建新的目标表 employees_copy，与原始表 employees 相同的列和数据类型：

CREATE TABLE employees_copy (
  id INT,
  name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10, 2)
);
1
2
3
4
5
6

使用INSERT INTO和SELECT语句的组合，从原始表 employees 中选择数据并将其插入到新的目标表 employees_copy 中：

INSERT INTO employees_copy (id, name, department, salary) 
SELECT id, name, department, salary 
FROM employees;
1
2
3

---

MySQL约束

在数据库中，约束（constraints）用于定义和强制表中数据的完整性规则。它们可以确保插入、更新或删除数据时的数据一致性和有效性。

以下是几种常见的约束类型：

1. 主键约束（Primary Key Constraint）：主键约束用于唯一标识表中的每一行，并防止重复数据。它要求指定一个或多个列作为主键，并保证其唯一性和非空性。

当某两行的记录是完全一致时，可通过主键值来区分它们。

2. 唯一约束（Unique Constraint）：唯一约束确保指定的列或列组合中的值是唯一的，但允许空值。

3. 外键约束（Foreign Key Constraint）：外键约束用于定义表与其他表之间的关系。它要求一个或多个列的值必须匹配关联表中的主键或唯一键值。

假设我们有两个表：orders（订单）和 customers（客户）。每个订单都关联到特定的客户，我们可以使用外键约束来确保订单表中的 customer_id 列的值必须在客户表中存在。

首先，我们需要在客户表中定义主键约束，以唯一标识每个客户的记录：

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(50)
);
1
2
3
4

接下来，在订单表中，我们使用外键约束来指定 customer_id 列引用了客户表的主键 customer_id

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_date DATE,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
1
2
3
4
5
6

上述示例中，外键约束指定了 orders 表的 customer_id 列引用了 customers 表的主键 customer_id。这意味着当我们在订单表中插入一条新记录时，数据库会验证 customer_id 的值是否存在于客户表中。如果不存在，插入操作将被拒绝。

1. 非空约束（Not Null Constraint）：非空约束要求指定的列在插入或更新时不能包含空值。
2. 默认约束（Default Constraint）：默认约束定义了当未明确提供值时，应该使用的默认值。
3. 检查约束（Check Constraint）：检查约束定义了在插入或更新操作中所允许的值范围或条件。

这些约束可以在创建表时指定，也可以通过ALTER TABLE语句添加到现有表中。例如，以下是一个示例，展示了如何在创建表时定义主键约束和唯一约束：

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  age INT,
  CONSTRAINT check_age CHECK (age >= 0)
);
1
2
3
4
5
6
7

在上述示例中，students 表具有一个名为 id 的主键列，name 列被定义为非空的，email 列具有唯一约束，age 列使用检查约束确保其值大于等于0。

4. 联合唯一约束：要求指定的多个列的组合值在表中是唯一的。

示例：

CREATE TABLE employees (
  id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department VARCHAR(50),
  CONSTRAINT uq_employee_name_dept UNIQUE (first_name, last_name, department)
);
1
2
3
4
5
6
7

上述示例中的 employees 表定义了一个联合唯一约束 uq_employee_name_dept，它包含了 first_name、last_name 和 department 这三个字段。这表示在表中，不允许相同的 first_name、last_name 和 department 组合出现多次。

注意： 每个字段单独的值可以不是唯一的，但是它们的组合值必须是唯一的。

---

MySQL事务

事务是数据库管理系统中的一种机制，用于确保数据库操作的一致性和完整性。它将一系列的数据库操作（例如插入、更新或删除）作为一个单独的执行单元，要么全部成功地执行，要么全部回滚到事务开始前的状态，从而保证了数据的一致性。

举个例子：当一个用户从银行账户A转账给账户B时，涉及到多个数据库操作，比如减少账户A的余额和增加账户B的余额。这个转账过程可以作为一个事务来处理。

在这个例子中，事务的执行过程如下：

开始事务。
查询账户A的余额并检查是否足够支付转账金额。
如果足够支付，更新账户A的余额，减去转账金额。
查询账户B的余额。
更新账户B的余额，增加转账金额。
提交事务，将所有操作永久保存到数据库中。

如果在执行过程中出现任何错误，比如账户余额不足或数据库连接失败，事务将回滚到开始前的状态。也就是说，账户A的余额不会减少，账户B的余额也不会增加。这样可以确保转账操作要么完全成功，要么完全失败，避免了数据不一致的情况。

常用的事务语句包括：

BEGIN/START TRANSACTION：开始一个事务。这个语句标志着一个新的事务的开始。

COMMIT：提交事务。这个语句将所有对数据库的修改保存到数据库中，并结束当前的事务。

ROLLBACK：回滚事务。这个语句将撤销事务中的所有修改，并结束当前的事务。

SAVEPOINT：设置一个保存点。这个语句允许在事务中设置一个保存点，以便在后续的操作中能够回滚到这个保存点。

RELEASE SAVEPOINT：释放保存点。这个语句用于释放一个事务中设置的保存点。

START TRANSACTION;：执行一系列的数据库操作

事务具有四个基本的特性，通常被称为 ACID 特性：

1. 原子性（Atomicity）：事务被视为一个不可分割的原子单位。事务中的所有操作要么全部成功执行，要么全部回滚到事务开始前的状态，保证数据库的一致性。
2. 一致性（Consistency）：事务在执行之前和执行之后，数据库都必须保持一致的状态。这意味着事务必须遵循预定义的规则和约束，以确保数据的完整性和有效性。
3. 隔离性（Isolation）：多个事务同时执行时，每个事务都应该与其他事务相互隔离，互不干扰。隔离性确保了每个事务都能访问被其它事务修改之前的数据，并防止并发操作引起的数据不一致问题。
4. 持久性（Durability）：一旦事务提交成功，其所做的修改将被永久保存到数据库中，即使出现系统崩溃或断电等故障情况，也能够保证数据的持久性。

隔离性涉及到事务隔离级别：

事务隔离级别是指多个并发事务之间相互隔离的程度，用于控制并发操作可能导致的数据不一致性和并发冲突的问题。关系型数据库通常支持以下四个标准的事务隔离级别：

1. 读未提交（Read Uncommitted）：最低级别的隔离级别。在该级别下，一个事务可以读取到另一个事务尚未提交的数据。这样会导致脏读（Dirty Read）问题，即读取到了未经验证的、可能被回滚的数据。该级别的并发性高，但数据完整性较差。
2. 读已提交（Read Committed）：在该级别下，一个事务只能读取到已经提交的数据。这样避免了脏读问题，但依然存在不可重复读（Non-repeatable Read）问题。不可重复读指的是，在同一事务内，对同一记录进行多次读取，而得到的结果却不一致。因为在读取期间，其他事务可能已经修改了同一条记录。
3. 可重复读（Repeatable Read）：在该级别下，事务执行期间，保证多次读取同一记录时，结果始终一致。为达到该目的，数据库会对读取的数据进行加锁，防止其他事务对其进行修改。这样解决了不可重复读问题，但仍存在幻读（Phantom Read）问题。幻读指的是在同一事务中，对同一范围的查询，但得到的结果集却不一致。幻读通常是由于其他事务插入或删除了符合查询条件的数据造成的。
4. 串行化（Serializable）：最高级别的隔离级别。该级别下事务完全串行执行，避免了脏读、不可重复读和幻读的问题。虽然能够确保最高的数据一致性，但并发性最差，通常会导致较低的系统性能。

---

MySQL索引

索引是数据库中用于提高数据检索效率的数据结构。它类似于书籍的目录，通过创建特定的数据结构来快速定位和访问数据库中的数据。

索引可以在一个或多个列上创建，并根据特定的算法和数据结构存储索引数据。当执行查询时，数据库系统可以使用索引来快速定位符合查询条件的数据，而不必扫描整个数据库表。

假设我们有一个名为"学生"的数据库表，其中包含了学生的信息，如学生ID、姓名、年龄和成绩等字段。我们需要频繁地根据学生ID进行查询学生的信息。

如果没有创建任何索引，每次执行这样的查询时，数据库系统将会扫描整个"学生"表来找到相应的学生记录。当数据量庞大时，这将会消耗大量的时间和资源。

但是，如果我们在"学生ID"字段上创建了一个B树索引，数据库系统将会在创建索引时对学生ID进行排序，并构建一颗平衡的B树结构。这样，当执行查询时，系统只需在B树中搜索对应的学生ID，就能快速定位到相关的学生记录，而不必扫描整个表。

常见的索引类型包括：

B树索引（B-Tree Index）：是最常见和广泛使用的索引类型。B树索引适用于等值查找和范围查找，可以提供较好的查询性能。它对索引列进行排序并构建一颗平衡的B树，支持高效的数据插入、更新和删除操作。

哈希索引（Hash Index）：基于哈希函数的索引结构。哈希索引适用于等值查找，将索引列的值哈希到索引桶中，可以直接定位到对应的数据。但是哈希索引不适合范围查找或排序操作。

全文索引（Full-Text Index）：针对文本内容的索引。全文索引可以对文本数据进行快速的全文搜索，支持模糊匹配、关键字搜索和语义查询等功能。

空间索引（Spatial Index）：用于处理包含地理位置或空间数据的索引。空间索引可以加速地理位置相关的查询，如范围查找、邻近搜索和交集计算等。

索引的创建：

选择要创建索引的表和字段，通常是根据查询频率较高或经常用于连接操作的字段。

使用CREATE INDEX语句创建索引。语法如下：

CREATE [UNIQUE] INDEX index_name
ON table_name (column_name1, column_name2, ...)
1
2

UNIQUE关键字可选，表示创建唯一索引，保证索引列的值唯一。

index_name为索引的名称，可以根据实际需要进行命名。

table_name为要创建索引的表名。

column_name1, column_name2, …为要创建索引的字段名。

索引的删除：

使用DROP INDEX语句删除索引。语法如下：

DROP INDEX index_name
ON table_name
1
2

index_name为要删除的索引名称。

table_name为索引所属的表名。

假设有一个名为"users"的表，包含以下字段：

id (主键)、name、email、age

现在我们想要对"email"字段创建一个索引。
我们可以使用以下语句在"users"表的"email"字段上创建一个非唯一索引：

CREATE INDEX idx_email ON users (email);
1

这将在"users"表的"email"字段上创建名为"idx_email"的索引。
如果我们想要删除刚刚创建的索引，可以使用以下语句进行操作：

DROP INDEX idx_email ON users;
1

这将删除"users"表上名为"idx_email"的索引。

索引在以下情况下可能会失效：

1. 不满足索引字段的顺序：

当查询条件中的索引字段不按照索引的顺序出现时，索引可能无法被利用，导致失效。

例如，如果索引是 (first_name, last_name)，但查询条件中只提供了 last_name，那么索引将无法发挥作用。

2. 使用函数或表达式对索引字段进行操作：

如果查询中对索引字段使用函数、表达式或计算操作，索引可能无法被利用。

例如，如果索引是 LOWER(email)，但查询中使用了 WHERE UPPER(email) = 'ABC'，索引就无法被使用。

3. 模糊查询（通配符开头的查询）：

以通配符 % 开头的模糊查询（如 LIKE '%abc'）通常无法利用索引，因为无法确定索引的起始点。

另外，如果模糊查询的通配符位于字符串的开始位置（如 LIKE 'abc%'），某些情况下也无法使用索引。

---

MySQL视图

视图（View）是在数据库中基于查询结果定义的虚拟表，其内容由一个或多个基本表（或其他视图）的查询结果决定。视图并不直接存储数据，而是根据查询的定义在需要时动态生成结果。

通过创建视图，可以隐藏底层表结构的复杂性，并提供一种简化和抽象的方式来访问和操作数据。以下是一些使用视图的好处：

1. 简化复杂查询：通过创建一个视图，可以对多个表进行联接、过滤、排序等操作，将复杂的查询逻辑封装到视图中，使用户可以使用简单的查询来获取所需数据。
2. 数据安全性：视图可以用来限制对表的访问权限，只向用户提供必要的数据，保护敏感信息的安全性。通过授权机制，可以控制用户对视图的查询和修改权限，而无需直接访问底层表。
3. 重用和模块化：通过创建视图，可以将常用查询的定义保存下来，方便在其他查询中复用。这样可以减少代码冗余，提高开发效率和可维护性。
4. 数据逻辑独立性：视图可以对表结构进行抽象和封装，当底层表的结构发生变化时，只需要修改视图的定义，而无需修改依赖于视图的应用程序或查询。

创建视图：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table1
WHERE condition;
1
2
3
4

通过以上语句，可以创建名为 view_name 的视图，该视图基于 table1 表，并根据指定的条件筛选出需要的数据列。

删除视图：

DROP VIEW [IF EXISTS] view_name;
1

其中：

view_name 是要删除的视图的名称。

IF EXISTS 是可选的关键字，用于在视图不存在时避免产生错误。

MySQL中导入和导出数据库：

导出数据库：

• 导出整个数据库：

mysqldump -u username -p database_name > dump_file.sql
1

这将导出名为database_name的整个数据库，并将其保存到名为dump_file.sql的SQL脚本文件中。在运行此命令后，系统会要求输入密码。

• 导出特定表：

mysqldump -u username -p database_name table1 table2 > dump_file.sql
1

这将只导出数据库database_name中的table1和table2两个表，并将结果保存到dump_file.sql文件中。

导入数据库：

• 导入整个数据库：

mysql -u username -p database_name < dump_file.sql
1

这将从dump_file.sql文件中读取SQL命令，并执行这些命令以导入整个数据库。在运行此命令后，系统会要求输入密码。

• 导入特定表：
  如果只想导入特定的表，而不是整个数据库，可以使用以下命令：

mysql -u username -p database_name < dump_file.sql --tables table1 table2
1

这将只导入dump_file.sql文件中指定的table1和table2两个表。

username为MySQL用户名，password为MySQL密码，database_name是要导入或导出的数据库的名称，而dump_file.sql是指定导入或导出的SQL脚本文件的名称。

---

MySQL设计的三个范式

数据库设计中的三个范式，通常称为关系数据库的三个范式，用于规范化数据库模式以减少冗余和数据异常。

第一范式（1NF）：确保每个列都具有原子性值，不可再分割。

每个表中的每个字段都包含一个原子值，不可再分割成更小的部分。
避免在同一列中存储多个值，使用多个字段或新表来分隔重复的数据。

第二范式（2NF）：确保非主键列完全依赖于主键而不是部分依赖。

数据库表必须符合第一范式。
非主键列必须完全依赖于主键，而不是仅依赖于主键的部分。
如果一个表中存在组合主键，那么非主键列必须依赖于全部组合主键，而不仅仅是其中的一部分。

第三范式（3NF）：确保非主键列之间没有传递依赖关系。
数据库表必须符合第二范式。
非主键列之间不能存在传递依赖关系，即不能通过其他非主键列推导出某个非主键列的值。
如果存在传递依赖关系，应该将其移到单独的表中。

举个例子，假设有以下两个实体：学生（Students）和课程（Courses）。

第一范式（1NF）：
在第一范式中，我们需要确保每个字段都包含原子值。假设每个学生可以选择多门课程，并且每门课程可能有多个学分和多个教师。在这种情况下，我们可以将学生和课程的关系拆分成以下表格：

学生表（Students）：

课程表（Courses）：

第二范式（2NF）：
在第二范式中，非主键列必须完全依赖于主键，而不是部分依赖。在上述示例中，假设我们的主键是学生ID和课程ID，并且每个学生在每门课程中的成绩是独立存储的。那么我们可以拆分成以下表格：

学生表（Students）：

课程表（Courses）：

成绩表（Grades）：

第三范式（3NF）：
在第三范式中，我们需要确保非主键列之间没有传递依赖关系。假设每位学生的年龄决定了学费折扣的等级。那么我们可以进一步拆分成以下表格：

学生表（Students）：

课程表（Courses）：

成绩表（Grades）：

学费折扣表（FeeDiscounts）：

---

写在最后

本文内容均为重点知识点，是学习MySQL的不二选择。学习不是一蹴而就的过程，切勿囫囵吞枣。

我是秋说，我们下次见。