写在前面
鉴于全网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语句包括：
***mit：提交事务
rollback：回滚事务
savepoint：设置保存点
MySQL常用命令
- 查看mysql数据库的版本号：
select version();
- 启动MySQL服务：（基于Windows）
*** start mysql
- 停止MySQL服务：
*** stop mysql
- 登录MySQL数据库：
mysql -u 用户名 -p
其中，用户名是MySQL用户名，接着根据系统提示输入密码即可
- 如果安装MySQL时没有设置root密码，可以使用以下命令登录：
mysql -u root
- 退出 MySQL 命令行界面：
exit或者quit
- 显示所有的数据库名：
show databases;
- 创建数据库：
create database 数据库名;
- 切换到特定的数据库：
use 数据库名;
- 查看当前使用的数据库名：
select database();
- 删除数据库：
drop database 数据库名;
- 显示数据库中的表：
show tables;
- 创建表：
create table 表名 (
列名1 数据类型,
列名2 数据类型,
...
);
- 删除表：
drop table 表名;
- 向表中插入数据：
insert into 表名(列名1, 列名2, ...)
VALUES (具体值1, 具体值2, ...);
- 在表中查询特定的数据：
select 列名1, 列名2, ...
from 表名
where 查询条件;
不需要查询条件时：
select 列名1, 列名2, ...
from 表名;
- 获取某表的所有数据：
select * from 表名
- 获取某表的结构：
desc 表名;
也可使用
describe 表名;
- 更改表中某列的名字：
alert table 表名 rename column 原始列名 to 新列名;
- 为表中某列指定一个别名（并不改变表结构中列的名称）
select 原始列名 as 新列名 from 表名;
MySQL数学运算语句
- 加法：使用
+运算符执行加法运算。
select column1 + column2 as sum from 表名; //执行加法运算后的结果列指定别名为sum
select column1 + 10 from 表名; //也可以不指定别名
- 减法：使用
-运算符执行减法运算。
select column1 - column2 as reduce from 表名;
- 乘法：使用
*运算符执行乘法运算。
select column1 * column2 as ride from 表名;
select column1 * 10 as ride from 表名; //数值扩大十倍
- 除法：使用
/运算符执行除法运算。
select column1 / column2 as division from 表名;
- 取余：使用
%运算符计算两个数的余数。
select column1 % column2 as remainder from 表名;
- 其它运算：
POWER()函数用于计算幂次方，SQRT()函数用于计算平方根，ABS()函数用于获取绝对值等等。
MySQL条件查询
MySQL 中的条件查询是通过使用 WHERE 子句来筛选满足特定条件的数据。
MySQL 中条件查询的基本语法：
SELECT column1, column2, ... 。。列名
FROM table_name //表名
WHERE condition; //筛选条件
示例：
- 等于 (
=) 运算符：
SELECT * FROM table_name WHERE column_name = value;
//也可指定特定的列
- 不等于 (
<>或!=) 运算符：
SELECT * FROM table_name WHERE column_name <> value;
- 大于 (
>) 运算符：
SELECT * FROM table_name WHERE column_name > value;
- 小于 (
<) 运算符：
SELECT * FROM table_name WHERE column_name < value;
- 大于等于 (
>=) 运算符：
SELECT * FROM table_name WHERE column_name >= value;
- 小于等于 (
<=) 运算符：
SELECT * FROM table_name WHERE column_name <= value;
- 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
- 模糊匹配 (
LIKE) 运算符：
SELECT * FROM table_name WHERE column_name LIKE pattern;
条件查询还可以通过逻辑运算符（例如 `AND`, `OR`, `NOT`）组合多个条件。
- NULL运算符
SELECT * FROM table_name WHERE column_name IS NULL;
这将返回所有 column_name 列中值为 NULL 的行。
- IN运算符
IN 运算符用于在查询中匹配多个值。它允许您指定一个值列表，并检查某个列是否与该列表中的任何值匹配。
SELECT * FROM table_name WHERE column_name IN (value1, value2, value3);
例如：
SELECT * FROM users WHERE country IN ('USA', 'Canada', 'UK');
这将返回所有国家为 “USA”、“Canada” 或 “UK” 的用户。
注意：IN后跟的是具体的值，并不是一个区间。
-
AND运算符：AND运算符将多个条件连接起来，并要求所有条件都满足才能返回结果。如果任何一个条件不满足，那么整个条件将被视为不满足。例如：
SELECT * FROM table_name WHERE condition1 AND condition2;
在上述查询中，只有当 condition1 和 condition2 都满足时，才会返回满足条件的行。
-
OR运算符：OR运算符将多个条件连接起来，并要求至少一个条件满足才能返回结果。如果所有条件都不满足，那么整个条件将被视为不满足。例如：
SELECT * FROM table_name WHERE condition1 OR condition2;
在上述查询中，只要 condition1 或者 condition2 中至少一个满足，就会返回满足条件的行。
在MySQL中，AND 运算符的优先级高于OR运算符。这意味着当查询中同时存在AND和OR运算符时，AND运算符的条件会首先进行计算，然后再计算OR运算符的条件。
例如，对于以下查询：
SELECT * FROM table_name WHERE condition1 AND condition2 OR condition3;
根据运算符优先级，条件 condition1 和 condition2 会首先进行计算，然后再计算 condition3。所以上述查询可以等价地写为：
SELECT * FROM table_name WHERE (condition1 AND condition2) OR condition3;
可以以使用括号来明确指定优先级，以控制运算符的组合方式。例如：
SELECT * FROM table_name WHERE (condition1 OR condition2) AND condition3;
在上述查询中，condition1 或者 condition2 至少满足一个，并且同时还要满足 condition3 才会返回满足条件的行。
- %运算符：
在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” 等。
- _运算符：
在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）进行排序。
- 单列排序：
SELECT * FROM table_name ORDER BY column_name ASC;
上述查询将按照 column_name 列的升序对结果进行排序。
- 多列排序：
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;
上述查询将首先按照 column1 列的升序进行排序，对于相同的值，则按照 column2 列的降序进行排序。
- 如果在查询中使用了列别名，则可以通过别名进行排序：
SELECT column_name AS alias_name FROM table_name ORDER BY alias_name ASC;
指定了要选择的列 column_name，并使用 AS 关键字将其命名为 alias_name，并按照 alias_name 列进行升序排序。
默认情况下，排序是升序的（ASC）。如果要进行降序排序，可以使用 DESC 关键字。示例如下：
SELECT * FROM table_name ORDER BY column_name DESC;
注意，ORDER BY子句应该位于查询语句的末尾。如果查询中使用了LIMIT子句，则结果将首先根据排序顺序进行排序，然后再进行限制。
- 数字排序：
SELECT * FROM table_name ORDER BY 1(数字);
排序时将结果集中在第一个字段
举个例子：
假设有一个名为 employees 的表，其中包含以下字段：id和name。
SELECT * FROM employees ORDER BY 1 ASC;
上述查询将按照结果集中的第一个列（即 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, ... 表示要分组的列，可以指定一个或多个列。在 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;
这个查询将根据 product_id 列将订单表中的数据进行分组，并使用 SUM() 聚合函数计算每个产品的总销售数量。结果将包含每个产品的 product_id 和对应的总销售数量。
当存在group by与order by关键字时，执行顺序如下：
SELECT ...
FROM ...
WHERE...
GROUP BY ...
ORDER BY ...;
首先，根据 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;
在这个查询中们首先使用 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;
对于每个邮箱地址，只会返回一条记录，重复的记录会被去除。
注意，如果在 SELECT 语句中选择多列，并且希望根据多列进行唯一性判断和去重，只需将多列包含在 SELECT DISTINCT 子句中。
SELECT DISTINCT column1, column2
FROM table;
这样将根据 column1 和 column2 的组合来做为判断唯一性的依据，返回唯一的记录。
MySQL连接查询
连接查询用于在多个表之间建立关系，并检索满足特定条件的数据。
常见的连接类型如下：
- 内连接（INNER JOIN）：返回两个表中满足连接条件的匹配行。只有在两个表中都存在匹配的值时，才会返回结果。
SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
- 等值连接（Equi Join）：一种基于相等条件进行连接的连接类型。在等值连接中，两个表通过一个或多个列的值相等来建立连接。
假设我们有两个表：employees 和 departments，它们分别包含员工的信息和部门的信息。这两个表中都有一个共同的列 department_id，我们可以使用该列来进行等值连接，以获取每个员工所在的部门。
SELECT *
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
在这个查询中，我们使用了 INNER JOIN 进行连接，连接条件是 employees.department_id = departments.department_id。这意味着只有在 employees 表中的 department_id 值等于 departments 表中的 department_id 值时，才会返回匹配的行。
- 非等值连接（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;
在这个查询中，使用了 WHERE 子句来指定连接条件，即 orders.total_amount > discounts.discount_amount。这表示只有在 orders 表中的 total_amount 值大于 discounts 表中的 discount_amount 值时，才会返回匹配的行。
- 自连接（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;
在这个查询中，我们使用了别名 e 和 m 来表示同一个表 employees 的两个实体。我们通过连接条件 e.manager_id = m.employee_id 来建立员工与其直接上级员工的连接关系。
- 左连接（LEFT JOIN）：返回左表的所有行，以及与右表满足连接条件的匹配行。如果右表中没有与左表匹配的行，则对应的右表列将显示为 NULL。
SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
- 右连接（RIGHT JOIN）：返回右表的所有行，以及与左表满足连接条件的匹配行。如果左表中没有与右表匹配的行，则对应的左表列将显示为 NULL。
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
- 全连接（FULL JOIN）：返回左表和右表的所有行，并将它们组合在一起。如果某行在其中一个表中有匹配，而在另一个表中没有匹配，对应的列将显示为 NULL。
SELECT *
FROM table1
FULL JOIN table2 ON table1.column = table2.column;
上述1、2、3、4均为内连接，5、6、7均为外连接。
内连接与外连接的区别是：
- 匹配方式不同：内连接（Inner Join）基于连接条件匹配两个表中的行，只返回满足连接条件的匹配行。而外连接（Outer Join）则返回符合连接条件的匹配行，以及未能匹配到的行。
- 结果集不同：内连接仅返回连接条件匹配的行，因此结果集中只包含共有的行。外连接则可以返回连接条件匹配的行，也可以返回某个表中没有匹配项的行。
- 表的顺序：内连接和外连接的表的顺序可以影响结果集。对于内连接来说，调换连接顺序不会改变最终结果。而对于外连接来说，左外连接和右外连接的表的顺序会影响返回结果。
- 使用场景不同：内连接适用于需要获取两个表中相互匹配的数据的情况，例如获取员工和部门信息的内连接查询。而外连接常用于需要显示关联表中所有数据，包括没有匹配的数据的情况，例如获取所有员工及其薪水信息的左外连接查询。
MySQL子查询
子查询（Subquery）是指在一个查询语句中嵌套另一个完整的查询语句。子查询可以在主查询的条件、列列表或者其他子查询中使用，用于提供额外的数据或者限制结果集。
- WHERE子句中的子查询：
在WHERE子句中使用子查询可以作为条件来过滤主查询的结果集。子查询通常会返回一个值或者一组值，用于与主查询的条件进行比较。
例如，我们可以使用子查询来找出超过平均年龄的员工：
SELECT employee_name, age
FROM employees
WHERE age > (SELECT AVG(age) FROM employees);
在这个查询中，子查询 (SELECT AVG(age) FROM employees) 返回了员工表中年龄的平均值，然后将其与主查询中的每个员工的年龄进行比较，以筛选出年龄超过平均值的员工。
- 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;
在这个查询中，内部的子查询 (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) 返回了每个客户的订单数量。然后将该子查询作为临时表 o，并与 customers 表进行连接操作。
- select列表中的子查询：
可以在SELECT列表中使用子查询来检索特定的数据，并将其作为一个列返回。
例如，我们可以使用子查询来计算每个客户的订单数量，并将其作为一个新的列返回：
SELECT customer_id, (SELECT COUNT(*) FROM orders WHERE customer_id = customers.customer_id) AS order_count
FROM customers;
在这个查询中，子查询 (SELECT COUNT(*) FROM orders WHERE customer_id = customers.customer_id) 会计算每个客户的订单数量，并将其作为名为 order_count 的新列返回。
MySQL部分操作符
- Union：
UNION 是一个用于合并两个或多个 SELECT 语句结果集的操作符。它将多个 SELECT 查询的结果合并为一个单一的结果集，且去除重复的行。
合并两个结果集：
可以使用 UNION 操作符合并两个具有相同列数和数据类型的结果集。
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
这个查询将会合并 table1 和 table2 的结果集，并返回一个包含两个表的唯一行的结果集。
去除重复行：
UNION 操作符会自动去除重复的行，只返回唯一的行。
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
这个查询将会合并 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;
这个查询会将 table1 和 table2 的结果集合并，并使用别名来调整列的顺序和进行数据类型转换。
注意： 使用 UNION 操作符时，需要确保两个或多个 SELECT 查询的结果集具有相同的列数、列的顺序和数据类型。同时，需要 UNION 操作是对结果集进行去重的，如果需要包含重复行，可以使用 UNION ALL 操作符。
- Limit：
LIMIT 关键字用于限制 SQL 查询返回的结果行数量。它可以与 SELECT 语句一起使用，以便从结果集中选择指定数量的行。
返回前几行：
使用 LIMIT 可以返回查询结果中的前几行。
SELECT column1, column2 FROM table1 LIMIT 5;
这个查询会返回 table1 中的前 5 行数据。
跳过前几行并返回接下来的行：
除了限制返回的行数，还可以通过使用 OFFSET 子句来跳过前几行。
SELECT column1, column2 FROM table1 LIMIT 10 OFFSET 5;
这个查询会跳过 table1 的前 5 行，并返回接下来的 10 行数据。
简化的写法：
可以直接使用逗号分隔的两个参数，而不是使用 OFFSET 子句。
SELECT column1, column2 FROM table1 LIMIT 5, 10;
这个查询与前一个示例中的查询是等效的，会跳过 table1 的前 5 行，并返回接下来的 10 行数据。
含有操作符的SQL查询语句模板：
select
...
from
...
where
...
group by
...
having
...
order by
...
limit
...
MySQL之增删改查
表的创建：
CREATE TABLE 语句用于在数据库中创建新的数据表，并定义表的列以及相关的属性。
CREATE TABLE table_name (
column1 data_type,
column2 data_type,
column3 data_type,
...
PRIMARY KEY (column_name)
);
其中，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)
);
上述语句将在数据库中创建一个名为 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;
其中，table_name 是要删除的表的名称。
INSERT插入数据：
INSERT INTO 语句用于将新行或记录插入到数据库表中。
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
其中，table_name 是要插入数据的表的名称。在括号中，指定要插入数据的列名（如果不是插入所有列）和对应的值。
以下是一个示例，向名为 customers 的表插入数据：
INSERT INTO customers (name, email, phone) VALUES ('John Doe', 'john@example.***', '1234567890');
上述语句将向 customers 表中插入一条新记录，包含 name、email 和 phone 列的数据。
如果要一次性插入多行数据，可以使用多个 VALUES 子句，如下所示：
INSERT INTO customers (name, email, phone) VALUES ('John Doe', 'john@example.***', '1234567890'), ('Jane Smith', 'jane@example.***', '9876543210');
上述语句将向 customers 表中同时插入两条新记录。
**注意：**插入日期类型（DATE）的数据时，不可使用单引号将日期值括起来。相反，应该按照特定的日期格式直接提供日期值。
以下是一个示例，向名为 orders 的表插入日期数据：
INSERT INTO orders (order_date) VALUES (2023-01-11);
在上述示例中，我们将日期值 2023-01-11 直接插入到 order_date 列中。不需要使用单引号将日期值括起来。
update更新语句：
UPDATE语句用于更新数据库表中的数据。它允许您指定要更新的表、列和新值，以及一个可选的WHERE子句来限制更新的行。
UPDATE 表名
SET 列名1 = 新值1, 列名2 = 新值2, ...
WHERE 条件;
其中，表名 是要更新的表的名称，列名1, 列名2, ... 是要更新的列的名称，新值1, 新值2, ... 是对应列的新值，条件 是可选的WHERE子句，用于限制更新的行。
例如，假设我们有一个名为 customers 的表，其中包含列 name、age 和 email。如果要将名为 “John” 的客户的年龄更新为 30 岁，可以使用以下UPDATE语句：
UPDATE customers
SET age = 30
WHERE name = 'John';
delete删除语句：
DELETE语句用于从数据库表中删除行。它允许您指定要删除的表和一个可选的WHERE子句来限制删除的行。
DELETE FROM 表名
WHERE 条件;
其中，表名 是要删除的表的名称，条件 是可选的WHERE子句，用于限制删除的行。
例如，假设我们有一个名为 customers 的表，其中包含列 name、age 和 email。如果要删除名为 “John” 的客户的记录，可以使用以下DELETE语句：
DELETE FROM customers
WHERE name = 'John';
上述语句将在 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)
);
使用INSERT INTO和SELECT语句的组合，从原始表 employees 中选择数据并将其插入到新的目标表 employees_copy 中：
INSERT INTO employees_copy (id, name, department, salary)
SELECT id, name, department, salary
FROM employees;
MySQL约束
在数据库中，约束（constraints）用于定义和强制表中数据的完整性规则。它们可以确保插入、更新或删除数据时的数据一致性和有效性。
以下是几种常见的约束类型：
- 主键约束（Primary Key Constraint）：主键约束用于唯一标识表中的每一行，并防止重复数据。它要求指定一个或多个列作为主键，并保证其唯一性和非空性。
当某两行的记录是完全一致时，可通过主键值来区分它们。
-
唯一约束（Unique Constraint）：唯一约束确保指定的列或列组合中的值是唯一的，但允许空值。
-
外键约束（Foreign Key Constraint）：外键约束用于定义表与其他表之间的关系。它要求一个或多个列的值必须匹配关联表中的主键或唯一键值。
假设我们有两个表：orders（订单）和 customers（客户）。每个订单都关联到特定的客户，我们可以使用外键约束来确保订单表中的 customer_id 列的值必须在客户表中存在。
首先，我们需要在客户表中定义主键约束，以唯一标识每个客户的记录：
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50)
);
接下来，在订单表中，我们使用外键约束来指定 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)
);
上述示例中，外键约束指定了 orders 表的 customer_id 列引用了 customers 表的主键 customer_id。这意味着当我们在订单表中插入一条新记录时，数据库会验证 customer_id 的值是否存在于客户表中。如果不存在，插入操作将被拒绝。
- 非空约束（Not Null Constraint）：非空约束要求指定的列在插入或更新时不能包含空值。
- 默认约束（Default Constraint）：默认约束定义了当未明确提供值时，应该使用的默认值。
- 检查约束（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)
);
在上述示例中，students 表具有一个名为 id 的主键列，name 列被定义为非空的，email 列具有唯一约束，age 列使用检查约束确保其值大于等于0。
- 联合唯一约束：要求指定的多个列的组合值在表中是唯一的。
示例：
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)
);
上述示例中的 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：开始一个事务。这个语句标志着一个新的事务的开始。
***MIT：提交事务。这个语句将所有对数据库的修改保存到数据库中，并结束当前的事务。
ROLLBACK：回滚事务。这个语句将撤销事务中的所有修改，并结束当前的事务。
SAVEPOINT：设置一个保存点。这个语句允许在事务中设置一个保存点，以便在后续的操作中能够回滚到这个保存点。
RELEASE SAVEPOINT：释放保存点。这个语句用于释放一个事务中设置的保存点。
START TRANSACTION;：执行一系列的数据库操作
事务具有四个基本的特性，通常被称为 ACID 特性：
- 原子性（Atomicity）：事务被视为一个不可分割的原子单位。事务中的所有操作要么全部成功执行，要么全部回滚到事务开始前的状态，保证数据库的一致性。
- 一致性（Consistency）：事务在执行之前和执行之后，数据库都必须保持一致的状态。这意味着事务必须遵循预定义的规则和约束，以确保数据的完整性和有效性。
- 隔离性（Isolation）：多个事务同时执行时，每个事务都应该与其他事务相互隔离，互不干扰。隔离性确保了每个事务都能访问被其它事务修改之前的数据，并防止并发操作引起的数据不一致问题。
- 持久性（Durability）：一旦事务提交成功，其所做的修改将被永久保存到数据库中，即使出现系统崩溃或断电等故障情况，也能够保证数据的持久性。
隔离性涉及到事务隔离级别：
事务隔离级别是指多个并发事务之间相互隔离的程度，用于控制并发操作可能导致的数据不一致性和并发冲突的问题。关系型数据库通常支持以下四个标准的事务隔离级别：
- 读未提交（Read Un***mitted）：最低级别的隔离级别。在该级别下，一个事务可以读取到另一个事务尚未提交的数据。这样会导致脏读（Dirty Read）问题，即读取到了未经验证的、可能被回滚的数据。该级别的并发性高，但数据完整性较差。
- 读已提交（Read ***mitted）：在该级别下，一个事务只能读取到已经提交的数据。这样避免了脏读问题，但依然存在不可重复读（Non-repeatable Read）问题。不可重复读指的是，在同一事务内，对同一记录进行多次读取，而得到的结果却不一致。因为在读取期间，其他事务可能已经修改了同一条记录。
- 可重复读（Repeatable Read）：在该级别下，事务执行期间，保证多次读取同一记录时，结果始终一致。为达到该目的，数据库会对读取的数据进行加锁，防止其他事务对其进行修改。这样解决了不可重复读问题，但仍存在幻读（Phantom Read）问题。幻读指的是在同一事务中，对同一范围的查询，但得到的结果集却不一致。幻读通常是由于其他事务插入或删除了符合查询条件的数据造成的。
- 串行化（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, ...)
UNIQUE关键字可选，表示创建唯一索引，保证索引列的值唯一。
index_name为索引的名称，可以根据实际需要进行命名。
table_name为要创建索引的表名。
column_name1, column_name2, …为要创建索引的字段名。
索引的删除：
使用DROP INDEX语句删除索引。语法如下：
DROP INDEX index_name
ON table_name
index_name为要删除的索引名称。
table_name为索引所属的表名。
假设有一个名为"users"的表，包含以下字段：
id (主键)、name、email、age
现在我们想要对"email"字段创建一个索引。
我们可以使用以下语句在"users"表的"email"字段上创建一个非唯一索引：
CREATE INDEX idx_email ON users (email);
这将在"users"表的"email"字段上创建名为"idx_email"的索引。
如果我们想要删除刚刚创建的索引，可以使用以下语句进行操作：
DROP INDEX idx_email ON users;
这将删除"users"表上名为"idx_email"的索引。
索引在以下情况下可能会失效：
- 不满足索引字段的顺序：
当查询条件中的索引字段不按照索引的顺序出现时，索引可能无法被利用，导致失效。
例如，如果索引是 (first_name, last_name)，但查询条件中只提供了 last_name，那么索引将无法发挥作用。
- 使用函数或表达式对索引字段进行操作：
如果查询中对索引字段使用函数、表达式或计算操作，索引可能无法被利用。
例如，如果索引是 LOWER(email)，但查询中使用了 WHERE UPPER(email) = 'ABC'，索引就无法被使用。
- 模糊查询（通配符开头的查询）：
以通配符 % 开头的模糊查询（如 LIKE '%abc'）通常无法利用索引，因为无法确定索引的起始点。
另外，如果模糊查询的通配符位于字符串的开始位置（如 LIKE 'abc%'），某些情况下也无法使用索引。
MySQL视图
视图（View）是在数据库中基于查询结果定义的虚拟表，其内容由一个或多个基本表（或其他视图）的查询结果决定。视图并不直接存储数据，而是根据查询的定义在需要时动态生成结果。
通过创建视图，可以隐藏底层表结构的复杂性，并提供一种简化和抽象的方式来访问和操作数据。以下是一些使用视图的好处：
- 简化复杂查询：通过创建一个视图，可以对多个表进行联接、过滤、排序等操作，将复杂的查询逻辑封装到视图中，使用户可以使用简单的查询来获取所需数据。
- 数据安全性：视图可以用来限制对表的访问权限，只向用户提供必要的数据，保护敏感信息的安全性。通过授权机制，可以控制用户对视图的查询和修改权限，而无需直接访问底层表。
- 重用和模块化：通过创建视图，可以将常用查询的定义保存下来，方便在其他查询中复用。这样可以减少代码冗余，提高开发效率和可维护性。
- 数据逻辑独立性：视图可以对表结构进行抽象和封装，当底层表的结构发生变化时，只需要修改视图的定义，而无需修改依赖于视图的应用程序或查询。
创建视图：
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table1
WHERE condition;
通过以上语句，可以创建名为 view_name 的视图，该视图基于 table1 表，并根据指定的条件筛选出需要的数据列。
删除视图：
DROP VIEW [IF EXISTS] view_name;
其中：
view_name 是要删除的视图的名称。
IF EXISTS 是可选的关键字，用于在视图不存在时避免产生错误。
MySQL中导入和导出数据库：
导出数据库：
- 导出整个数据库：
mysqldump -u username -p database_name > dump_file.sql
这将导出名为database_name的整个数据库，并将其保存到名为dump_file.sql的SQL脚本文件中。在运行此命令后，系统会要求输入密码。
- 导出特定表：
mysqldump -u username -p database_name table1 table2 > dump_file.sql
这将只导出数据库database_name中的table1和table2两个表，并将结果保存到dump_file.sql文件中。
导入数据库：
- 导入整个数据库：
mysql -u username -p database_name < dump_file.sql
这将从dump_file.sql文件中读取SQL命令，并执行这些命令以导入整个数据库。在运行此命令后，系统会要求输入密码。
- 导入特定表：
如果只想导入特定的表，而不是整个数据库，可以使用以下命令：
mysql -u username -p database_name < dump_file.sql --tables table1 table2
这将只导入dump_file.sql文件中指定的table1和table2两个表。
username为MySQL用户名，password为MySQL密码，database_name是要导入或导出的数据库的名称，而dump_file.sql是指定导入或导出的SQL脚本文件的名称。
MySQL设计的三个范式
数据库设计中的三个范式，通常称为关系数据库的三个范式，用于规范化数据库模式以减少冗余和数据异常。
第一范式（1NF）：确保每个列都具有原子性值，不可再分割。
每个表中的每个字段都包含一个原子值，不可再分割成更小的部分。
避免在同一列中存储多个值，使用多个字段或新表来分隔重复的数据。
第二范式（2NF）：确保非主键列完全依赖于主键而不是部分依赖。
数据库表必须符合第一范式。
非主键列必须完全依赖于主键，而不是仅依赖于主键的部分。
如果一个表中存在组合主键，那么非主键列必须依赖于全部组合主键，而不仅仅是其中的一部分。
第三范式（3NF）：确保非主键列之间没有传递依赖关系。
数据库表必须符合第二范式。
非主键列之间不能存在传递依赖关系，即不能通过其他非主键列推导出某个非主键列的值。
如果存在传递依赖关系，应该将其移到单独的表中。
举个例子，假设有以下两个实体：学生（Students）和课程（Courses）。
第一范式（1NF）：
在第一范式中，我们需要确保每个字段都包含原子值。假设每个学生可以选择多门课程，并且每门课程可能有多个学分和多个教师。在这种情况下，我们可以将学生和课程的关系拆分成以下表格：
学生表（Students）：
| 学生ID | 姓名 | 年龄 |
|---|---|---|
| 1 | 张三 | 20 |
| 2 | 李四 | 19 |
| 3 | 王五 | 21 |
课程表（Courses）：
| 课程ID | 课程名称 | 学分 | 教师 |
|---|---|---|---|
| 1 | 数学 | 4 | 张老师 |
| 2 | 英语 | 3 | 王老师 |
| 3 | 物理 | 5 | 李老师 |
第二范式（2NF）：
在第二范式中，非主键列必须完全依赖于主键，而不是部分依赖。在上述示例中，假设我们的主键是学生ID和课程ID，并且每个学生在每门课程中的成绩是独立存储的。那么我们可以拆分成以下表格：
学生表（Students）：
| 学生ID | 姓名 | 年龄 |
|---|---|---|
| 1 | 张三 | 20 |
| 2 | 李四 | 19 |
| 3 | 王五 | 21 |
课程表（Courses）：
| 课程ID | 课程名称 | 学分 | 教师 |
|---|---|---|---|
| 1 | 数学 | 4 | 张老师 |
| 2 | 英语 | 3 | 王老师 |
| 3 | 物理 | 5 | 李老师 |
成绩表（Grades）：
| 学生ID | 课程ID | 成绩 |
|---|---|---|
| 1 | 1 | 90 |
| 1 | 2 | 85 |
| 2 | 1 | 95 |
| 3 | 2 | 88 |
| 3 | 3 | 92 |
第三范式（3NF）：
在第三范式中，我们需要确保非主键列之间没有传递依赖关系。假设每位学生的年龄决定了学费折扣的等级。那么我们可以进一步拆分成以下表格：
学生表（Students）：
| 学生ID | 姓名 | 年龄 | 学费折扣等级 |
|---|---|---|---|
| 1 | 张三 | 20 | 2 |
| 2 | 李四 | 19 | 1 |
| 3 | 王五 | 21 | 3 |
课程表（Courses）：
| 课程ID | 课程名称 | 学分 | 教师 |
|---|---|---|---|
| 1 | 数学 | 4 | 张老师 |
| 2 | 英语 | 3 | 王老师 |
| 3 | 物理 | 5 | 李老师 |
成绩表（Grades）：
| 学生ID | 课程ID | 成绩 |
|---|---|---|
| 1 | 1 | 90 |
| 1 | 2 | 85 |
| 2 | 1 | 95 |
| 3 | 2 | 88 |
| 3 | 3 | 92 |
学费折扣表（FeeDiscounts）：
| 学费折扣等级 | 折扣率 |
|---|---|
| 1 | 0.8 |
| 2 | 0.7 |
| 3 | 0.6 |
写在最后
本文内容均为重点知识点，是学习MySQL的不二选择。学习不是一蹴而就的过程，切勿囫囵吞枣。
我是秋说，我们下次见。
