基础语法

// 增
INSERT INTO 表名称 VALUES (值1, 值2,....)
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

// 删
DELETE FROM 表名称 WHERE 列名称 = 值

// 改
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

// 查
SELECT 列名称 FROM 表名称
SELECT * FROM 表名称

// 查找去重（使用DISTINCT）
SELECT DISTINCT 列名称 FROM 表名称

// 查找增加筛选条件（逻辑子句可以使用AND或OR结合多条子句）
SELECT 列名称 FROM 表名称 WHERE 逻辑子句

// 查找结果集排序（结尾用ASC或DESC选择正序排还是逆序排）
SELECT 列名称 FROM 表名称 ORDER BY 列名称 [ASC DESC]

// 查找结果集分组
SELECT 列名称 FROM 表名称 GROUP BY 列名称

// 查找结果集分组后，按条件筛选
SELECT 列名称 FROM 表名称 GROUP BY 列名称 HAVING 条件

// 查找符合范围内的数据（判断范围包含LOW和HIGH的边界值）
SELECT 列名称 FROM 表名称 WHERE 条件数据列名 BETWEEN LOW AND HIGH;

常用函数

时间函数

now()

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-02-16 18:31:56 |
+---------------------+
1 row in set (0.01 sec)

sysdate()

sysdate()日期时间函数跟now()一样获取系统时间，不同之处在于：now()在执行开始时值就得到了， sysdate()在函数执行时动态得到值。

mysql> select now(),sleep(2),sysdate(),now();
+---------------------+----------+---------------------+---------------------+
| now()               | sleep(2) | sysdate()           | now()               |
+---------------------+----------+---------------------+---------------------+
| 2023-02-16 18:37:53 |        0 | 2023-02-16 18:37:55 | 2023-02-16 18:37:53 |
+---------------------+----------+---------------------+---------------------+
1 row in set (2.00 sec)

current_timestamp

DEFAULT CURRENT_TIMESTAMP 表示当插入数据的时候，该字段默认值为当前时间

ON UPDATE CURRENT_TIMESTAMP 表示每次更新这条数据的时候，该字段都会更新成当前时间

这两个操作是mysql数据库本身在维护，所以可以根据这个特性来生成【创建时间】和【更新时间】两个字段，且不需要代码来维护
```
CREATE TABLE `test` (
    `text` varchar(255) DEFAULT '' COMMENT '内容',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

date_format(date,format)、time_format(time,format) 日期、时间转字符串函数，str_to_date(str, format) 字符串转日期函数

mysql> select date_format(now(), '%Y%m%d %H:%i:%s');
+---------------------------------------+
| date_format(now(), '%Y%m%d %H:%i:%s') |
+---------------------------------------+
| 20230217 03:56:33                     |
+---------------------------------------+
1 row in set (0.00 sec)

#str_to_date() 在使用时format中使用的分隔符要与字符串使用的分隔符匹配
mysql> select str_to_date('2022-12-12', '%Y-%m-%d');
+---------------------------------------+
| str_to_date('2022-12-12', '%Y-%m-%d') |
+---------------------------------------+
| 2022-12-12                            |
+---------------------------------------+
1 row in set (0.00 sec)

unix_timestamp() 日期转时间戳，from_unixtime() 时间戳转日期

#默认会返回当前日期的时间戳
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1676577902 |
+------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1676577911);
+---------------------------+
| from_unixtime(1676577911) |
+---------------------------+
| 2023-02-17 04:05:11       |
+---------------------------+
1 row in set (0.00 sec)

date_add()、date_sub() 在日期上增加或删除时间

mysql> select date_add(now(), interval 1 day);
+---------------------------------+
| date_add(now(), interval 1 day) |
+---------------------------------+
| 2023-02-18 04:12:41             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(), interval -1 day);
+----------------------------------+
| date_add(now(), interval -1 day) |
+----------------------------------+
| 2023-02-16 04:12:51              |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select date_sub(now(), interval 1 day);
+---------------------------------+
| date_sub(now(), interval 1 day) |
+---------------------------------+
| 2023-02-16 04:13:00             |
+---------------------------------+
1 row in set (0.00 sec)

两个日期间的运算

mysql> select datediff('2023-3-1', '2021-4-1');
+----------------------------------+
| datediff('2023-3-1', '2021-4-1') |
+----------------------------------+
|                              699 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select timediff('2023-3-1 23:14:57', '2021-4-1 10:13:15');
+----------------------------------------------------+
| timediff('2023-3-1 23:14:57', '2021-4-1 10:13:15') |
+----------------------------------------------------+
| 838:59:59                                          |
+----------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

时间戳转换、增、减函数

mysql> select timestamp(now());
+---------------------+
| timestamp(now())    |
+---------------------+
| 2023-02-19 04:32:02 |
+---------------------+
1 row in set (0.00 sec)

mysql> select timestamp(now(), '10 04:00:00');
+---------------------------------+
| timestamp(now(), '10 04:00:00') |
+---------------------------------+
| 2023-03-01 08:29:26             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select timestampdiff(year, now(), '20201201');
+----------------------------------------+
| timestampdiff(year, now(), '20201201') |
+----------------------------------------+
|                                     -2 |
+----------------------------------------+
1 row in set (0.01 sec)

mysql> select timestampdiff(day, now(), '20201201');
+---------------------------------------+
| timestampdiff(day, now(), '20201201') |
+---------------------------------------+
|                                  -810 |
+---------------------------------------+
1 row in set (0.00 sec)

时区转换函数（timezone）

mysql> select convert_tz(now(), '+08:00', '+00:00');
+---------------------------------------+
| convert_tz(now(), '+08:00', '+00:00') |
+---------------------------------------+
| 2023-02-18 20:36:40                   |
+---------------------------------------+
1 row in set (0.00 sec)

统计函数

count() 数量统计
avg() 求平均数
sum() 求和
max()、min() 取最大值、最小值
abs() 取绝对值
pow(x, y) 求x的y次方
truncate(x, y) x保留y位小数
mod(x, y) x除y取余

字符串函数

char_length(s) 返回字符串s的长度
concat(s1, s2, …, sn) 将多个字符串合并，concat_ws(x, s1, s2, …, sn) 合并多个字符串并用x做分隔符
locate(s1, s) 在字符串s中获取字符串s1的开始位置
lower(s) 将字符串中所有字母变成小写，upper(s) 将字符串中所有字母变成大写
substr(s, n, len) 在字符串s中，从n位置开始截取长度为len的子字符串
replace(s, s1, s2) 在字符串s中，用s2替换所有s1
trim(s) 清除字符串s中所有空格

其他函数

# if函数, 遵守非0为真0为假
mysql> select if(0, 'true', 'false');
+--------------------+
| if(0, true, false) |
+--------------------+
| 0                  |
+--------------------+
1 row in set (0.00 sec)

mysql> select if(1, 'true', 'false');
+------------------------+
| if(1, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+
1 row in set (0.00 sec)

mysql> select if(1>0, 'true', 'false');
+----------------------+
| if(1>0, true, false) |
+----------------------+
| 1                    |
+----------------------+
1 row in set (0.01 sec)

# isnull 判断参数是否为空
mysql> select isnull(null);
+--------------+
| isnull(null) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> select isnull(1);
+-----------+
| isnull(1) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)


# ifnull(a, b) a为null返回b，否则返回a
mysql> select ifnull(null, 1);
+-----------------+
| ifnull(null, 1) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)


# database() 返回当前数据库名
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)


# current_user() 返回当前用户
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.01 sec)

进阶语法

通配符与模糊查询

“%” 百分号通配符：表示任何字符出现任意次数（可以是0次）
““ 下划线通配符：表示只能匹配单个字符,不能多也不能少,就是一个字符。当然，也可以like “陈___“，数量不限
like操作符：LIKE作用是指示 mysql 后面的搜索模式是利用通配符而不是直接相等匹配进行比较；但如果like后面没出现通配符，则在SQL执行优化时将 like 默认为 “=”执行

# 模糊查询语法，column_name(s)表示需要查询的列名，table_name表示需要查询的表名，pattern表示查询条件，可以使用通配符进行模糊匹配
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

注意：
- 匹配前需要检查数据库是否配置了区分大小写
- 匹配字符尾部空格要清除，否则会匹配不上
- 通配符无法匹配到NULL
- MySQL中的模糊查询可能会降低查询的效率，因为需要对所有可能匹配的记录进行扫描。因此，应该尽量避免使用通配符在查询条件的开头处，以提高查询效率

条件集查询（in）

嵌套 SQL 可以使用子查询或者联结查询的方式在一个查询语句中进行多层嵌套。如果需要在查询结果中再次查找数据，可以将原始查询语句作为子查询，然后在子查询的结果中进行再次查询。

例如，假设有两个表格：订单表格和商品表格，其中订单表格中包含订单编号和商品编号，商品表格中包含商品编号和商品名称。如果要查询订单表格中所有包含特定商品的订单，可以使用如下的 SQL 语句：

SELECT * FROM orders WHERE product_id IN (
    SELECT product_id FROM products WHERE product_name = '特定商品'
)

查询数据集排前的数据

// mysql
SELECT TOP 5 * FROM 表

// oracle（不支持top语法）
SELECT * FROM 表 WHERE ROWNUM <= 5

限制查询数量（常用于数据分页）

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

OFFSET后面为偏移量。OFFSET内容为可选项，当不填OFFSET内容时，默认输出设置数量的表内头部数据；当填入OFFSET偏移量，输出（数量~数量+偏移量）之间的数据。上边际不包含该数量位置的值，从+1开始记，例。

select * from table_name limit 10 offset 10;

// 不包含第10条数据，返回第11条到第20条，共计10条数据。也可以简写成如下

select * from table_name limit 10, 10;

联表查询

在MySQL中，连接（Join）是指将多个表中的数据进行组合的操作。MySQL支持多种连接方式，包括内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和外连接（FULL OUTER JOIN）。

内连接（INNER JOIN）：内连接是最常用的连接方式，它只返回两个表中共同符合查询条件的记录。内连接使用JOIN关键字或直接使用表名进行连接，例如：
```
  SELECT * FROM table1 
  JOIN table2 
  ON table1.column = table2.column;
```
左连接（LEFT JOIN）：左连接返回左边表中所有记录以及与之匹配的右边表中的记录。如果右边表中没有与之匹配的记录，则返回NULL值。左连接使用LEFT JOIN关键字进行指定，例如：
```
  SELECT * FROM table1 
  LEFT JOIN table2 
  ON table1.column = table2.column;
```
右连接（RIGHT JOIN）：右连接返回右边表中所有记录以及与之匹配的左边表中的记录。如果左边表中没有与之匹配的记录，则返回NULL值。右连接使用RIGHT JOIN关键字进行指定，例如：
```
  SELECT * FROM table1 
  RIGHT JOIN table2 
  ON table1.column = table2.column;
```
外连接（FULL OUTER JOIN）：外连接返回左边表和右边表中的所有记录，如果某个表中没有与之匹配的记录，则返回NULL值。MySQL不支持全外连接，但可以使用左外连接和右外连接的联合查询来模拟实现，例如：
```
  SELECT * FROM table1 
  LEFT JOIN table2 ON table1.column = table2.column 
  UNION 
  SELECT * FROM table1 
  RIGHT JOIN table2 ON table1.column = table2.column;
```

存储过程

// 基本语法（IN/OUT 表示输入输出参数）
CREATE PROCEDURE `存储过程名称`(IN/OUT `参数名` 类型)
BEGIN
  // do something
END

// 执行存储过程
CALL 存储过程名称(参数);

// 删除存储过程，IF EXISTS 指定这个关键字，用于防止因删除不存在的存储过程而引发的错误
DROP PROCEDURE [ IF EXISTS ] <存储过程名称>

// 查看全部存储过程
SHOW PROCEDURE STATUS;

// 查看存储过程内容
SHOW CREATE PROCEDURE 存储过程名称;

// 存储过程不支持修改内容

存储过程支持if、case、while、repeat、loop、ITERATE：

if 条件 then ... else ... end if;

case 变量 when 结果1 then ...; when 结果2 then ...; else ...; end case;

while 循环条件 do ... end while;

repeat ... until 循环条件 end repeat;

LOOP_LABLE:loop ... leave LOOP_LABLE; end loop;  // leave LOOP_LABLE 指跳出循环，此外还有 iterate LOOP_LABLE 用于循环语句的迭代，即立即开始下次循环

需要注意的是，存储过程在使用时需要考虑其性能和安全性，并且需要避免过度依赖存储过程，否则可能会导致数据库性能下降和维护困难。

库表操作

建库

CREATE DATABASE 数据库名;

修改库属性

// 修改数据库名称
ALTER DATABASE db_name RENAME TO new_db_name;

// 修改数据库字符集
ALTER DATABASE db_name CHARACTER SET utf8mb4;

// 修改数据库排序规则
ALTER DATABASE db_name COLLATE utf8mb4_general_ci;

// 修改数据库存储引擎
ALTER DATABASE db_name ENGINE=InnoDB;

有些属性的修改可能会导致数据库中的数据丢失或发生不可逆的改变，一定要记得做好备份工作

删库

DROP DATABASE <数据库名>;

建表

CREATE TABLE IF NOT EXISTS '表名' (
  'xxx_id' INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  'xxx_name' VARCHAR(10) NOT NULL,
  'xxx_role' VARCHAR(4) NOT NULL DEFAULT('user'),
  'xxx_age' INT UNSIGNED NOT NULL CHECK ('xxx_age' BETWEEN 0 and 120),
  'xxx_DATE' DATE,
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

// 创建外键
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(id)
);

字段的增删改

// 增加字段
ALTER TABLE table_name ADD column_name column_type;

// 修改字段类型
ALTER TABLE table_name MODIFY column_name new_column_type;

// 修改字段名称
ALTER TABLE table_name CHANGE old_column_name new_column_name column_type;

// 删除字段
ALTER TABLE table_name DROP column_name;

修改表字段可能会导致数据丢失，修改前需要备份数据

修改表属性

// 修改表名称
ALTER TABLE old_table_name RENAME TO new_table_name;

// 修改表的存储引擎
ALTER TABLE table_name ENGINE = new_engine;

// 添加索引
ALTER TABLE table_name ADD INDEX index_name (column_name);

// 删除索引
ALTER TABLE table_name DROP INDEX index_name;

修改表属性可能会导致数据丢失，修改前需要备份数据

索引

// 创建普通索引，索引值可出现多次
CREATE INDEX index_name ON table_name (column_name);

// 创建唯一索引，除了NULL外，NULL可能会出现多次
CREATE UNIQUE INDEX index_name ON table_name (column_name);

// 创建主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

// 创建全文索引
CREATE FULLTEXT INDEX index_name ON table_name (column_name);

// 删除索引
DROP INDEX [indexName] ON mytable;

// 删除主键索引，删除主键索引表明将允许值为NULL或值重复，所以建议先将逐渐索引改为唯一索引，以此保证原数据完整性
ALTER TABLE table_name DROP PRIMARY KEY;

删表

DROP TABLE [ IF EXISTS ] 表名;

导入导出

// 导出数据，不加表名表示导出整个库，加'-d'表示仅导出表结构，加'--databases'表示要导出多个数据库
mysqldump -h IP地址 -P 端口 -u 用户名 -p [-d] [--databases] 数据库名 [表名] > 导出的文件名

// 导入数据，导入前需要先创建同名数据库，在库中导入
create database 库名;
use 库名;
source sql文件;

备份

备份涉及到很多问题，比如物理备份、逻辑备份、增量备份，还可以借助第三方软件实现热备份。

逻辑备份比较好理解，使用mysqldump直接导出sql，打包备份就行，逻辑备份的缺点是不如物理备份速度快。

物理备份是指直接拷贝数据库目录中的文件转移到别的机器上，缺点是文件体积大，不够灵活。

MySQL 的增量备份是在全量备份的基础上，仅备份自上一次备份后新增或修改的数据。这种备份方式可以节省备份时间和存储空间，并且可以更快地恢复数据。

增量备份之前，需要在 MySQL 服务器上启用二进制日志功能。在备份之前，先使用 FLUSH LOGS 命令生成新的二进制日志文件，然后备份从上次备份之后产生的所有二进制日志文件。可以使用 mysqlbinlog 工具来转换二进制日志为 SQL 语句，然后将这些 SQL 语句应用到备份的数据库中。具体操作如下：

# 启用二进制日志功能
vi /etc/mysql/mysql.conf.d/mysqld.cnf
# 在 [mysqld] 部分中添加以下行
log-bin=/var/log/mysql/mysql-bin.log

# 重启 MySQL 服务器
sudo systemctl restart mysql

# 生成新的二进制日志文件
mysqladmin flush-logs

# 备份从上次备份以来产生的所有二进制日志文件
sudo cp /var/log/mysql/mysql-bin.* /path/backup/directory

# 将二进制日志转换为 SQL 语句并应用到备份的数据库中
mysqlbinlog /path/backup/directory/mysql-bin.* | mysql -u root -p