目录
前段时间查日志的时候，XLC跟我说有时间学习下SQL，日志平台支持了SQL语句，比较好用，我说好的，可直到现在我也没有去平台看过。最近做了一个一键复原VPS脚本，作用是根据之前的备份，通过执行bash脚本，完成之前环境的完全恢复，包括nginx，jekyll，sql(for cloudreve&nextcloud)，php以及各种文件配置等。备份恢复SQL的时候只是备份的对应数据库，发现用户总是没能备份恢复成功，所以想系统的学习下SQL这门语言，想找到其中的原因，顺便更新下自己的技能树，为了更好地搬砖~
一、MySQL概述
和MySQL相关的观念基本有三个：数据库，数据库管理系统以及SQL。数据库（database，DB）是存储数据的仓库，在其中，数据是有组织的存储的；数据库管理系统（Database Management System，DBMS）是操纵和管理数据库的大型软件；SQL（Structured Query Language）则是操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准。目前主流的关系型数据库管理系统可以从DB-ENGINES查询到，22年6月份的情况如下：
无论是Oracle还是MySQL，无论是国外的还是国内各种数据库，只要是关系型数据库，我们都可以通过SQL语言去操作它。关系型数据库指的是数据建立在关系模型的基础上，由多张相互连接的二维表组成的，其特点是：
- 使用表结构存储数据，格式统一，便于维护
- 使用SQL语言进行操作，标准统一，使用方便
非关系型数据库：不通过表结构存储数据的数据库
SQL是一种编程语言，只不过得益于设计，语法相对简单和智能，和其他编程语言一样，SQL也有其通用的语法规则：
- SQL语句可以单行或者多行书写，以分号结尾
- SQL语句可以通过空格/缩进来增强语句的可读性
- MySQL数据库中的SQL语句不区分大小写，关键字建议大写
- 注释又分为如下
- 单行注释：-- 注释内容，或者，# 注释内容（MySQL特有）
- 多行注释：/*注释内容*/
二、数据定义语言 -- DDL -- Data Defination Language
DDL是数据定义语言，用来定义（新建，删除和修改）数据库对象，比如数据库，表，字段等。
sql-- ############### 数据库相关 ###############
-- 创建数据库
CREATE DATABASE [IF EXISTS] db_name [DEFAULT CHARSET charset] [COLLATE seqReg];
-- 删除数据库
DROP DATABASE [IF EXISTS] db_name;
-- 切换使用数据库
USE db_name;
-- 目前MySQL不支持直接修改数据库名称，但是可以通过备份恢复，或者将旧数据库的表移动到新数据库中实现
CREATE DATABASE New_db;
mysqldump -uroot -p123456 Old_db > /tmp/old_db.sql;
mysql -uroot -p123456 new_db < /tmp/old_db.sql
-- -----------
CREATE DATABASE New_db;
RENAME TABLE Old_db.tb TO New_db.tb;
...
DROP DATABASE Old_db;
-- ############### 表相关 ###############
-- 创建表结构
CREATE TABLE tb_name(
column-1 datatype constraint [COMMENT "comment for column-1"],
column-2 datatype constraint [COMMENT "comment for column-2"],
......
column-n datatype constraint [COMMENT "comment for column-n"]
)[COMMENT "comment for tb_name"];
-- 添加表的字段和约束
ALTER TABLE tb_name ADD column datatype [constraint] [COMMENT comment];
-- 删除表的字段
ALTER TABLE tb_name DROP column;
-- 修改字段数据类型，默认不会修改约束
ALTER TABLE tb_name MODIFY COLUMN column datatype [constraint] [COMMENT comment];
-- 修改表的字段名，类型以及约束，默认不会修改约束
ALTER TABLE tb_name CHANGE old_column new_column new_datatype [constraint] [COMMENT comment];
-- 添加约束
ALTER TABLE tb_name ADD [CONSTRAINT cons_name] PRIMARY KEY (column);
ALTER TABLE tb_name-1 ADD [CONSTRAINT cons_name] FOREIGN KEY (column-1) REFERENCES tb_name-2 (column-2);
ALTER TABLE tb_name ADD [CONSTRAINT cons_name] UNIQUE KEY (column);
-- 删除约束，当唯一约束没有约束名的时候，可以通过`SHOW CREATE TABLE tb_name;`来查看名称
ALTER TABLE tb_name DROP PRIMARY KEY;
ALTER TABLE tb_name DROP CONSTRAINT cons_name;
-- 修改表的名称
ALTER TABLE old_tb_name RENAME TO new_tb_name;
-- 删除表，TRUNCATE方法是删除后重新创建该表，两种方法都会丢失数据
DROP TABLE [IF EXISTS] tb_name;
TRUNCATE TABLE tb_name;
三、数据操作语言 -- DML -- Data Manipulation Language
DML是数据操作语言，用来对数据库表中的数据记录进行增(INSERT)删(DELETE)改(UPDATE)，这几个操作还是比较简单的，但是也比较危险，一定要注意条件的设定！！！
sql-- 添加表的一个记录（行），可以是指定字段赋值，也可以是全部字段赋值，没有指定值的字段取默认值
INSERT INTO tb_name (column-1, column-2) VALUES (value-1,value-2);
INSERT INTO tb_name VALUES (value-1,value-2,..., value-n);
INSERT INTO tb_name (column-1,column-2) VALUES (value-1,value-2), (value-1,value-2);
INSERT INTO tb_name VALUES (value-1,value-2,..., value-n), (value-1,value-2,..., value-n);
-- 修改表的记录（行），当不指定WHERE的时候，会修改整张表对的字段值
UPDATE tb_name SET column-1 = value-1, column-2 = value-2, ... [WHERE condition];
-- 删除表的记录，WHERE不指定的话则会删除整张表的数据
DELETE FROM tb_name [WHERE condition];
四、数据查询语言 -- DQL -- Data Query Language ★
DQL是数据查询语言，用来查询数据库表中的各种数据，也是日常使用最多的SQL类型。如实是先对查询语句的整理做了一个优先级的排序，然后根据不同的关键字做了实例拆分。
sql-- SELECT
-- 字段列表 ---------- 第四步执行
-- FROM
-- 表名列表 ---------- 第一步执行
-- WHERE
-- 条件列表 ---------- 第二步执行
-- GROUP BY
-- 分组字段列表 ------ 第三步执行（包括HAVING）
-- HAVING
-- 分组后条件列表
-- ORDER BY
-- 排序字段列表 ------ 第五步执行
-- LIMIT
-- 分页参数 ---------- 第六步执行
-- ############### 拆分实例--SELECT ###############
SELECT column-1, column-2... FROM tb_name;
SELECT column-1 [AS alias], column-2 [AS alias]... FROM tb_name;
SELECT DISTINCT column_name FROM tb_name;
-- ############### 拆分实例--WHERE ###############
SELECT column_name FROM tb_name WHERE condition;
-- ############### 拆分实例--聚合函数 ###############
-- 聚合函数都是作用在表中的某一字段（列），NULL值不参与计算
SELECT COUNT(*) FROM tb_name; /*记录（行）数量*/
SELECT AVG(age) FROM tb_name; /*求平均*/
SELECT MAX(age) FROM tb_name; /*求最大值*/
SELECT MIN(age) FROM tb_name; /*求最小值*/
SELECT SUM(age) FROM tb_name WHERE condition; /*条件求和*/
-- ############### 拆分实例--GROUP BY ###############
-- 需要注意的是WHERE是分组前过滤，HAVING是分组之后过滤
SELECT column_name FROM tb_name [WHERE condition] GROUP BY column_name [HAVING condition]
-- ############### 拆分实例--ORDER BY ###############
-- 排序方式有两种：ASC升序和DESC降序，字段1相同则按照字段2排序
SELECT column_name FROM tb_name ORDER BY column_name1 method-1, column_name2 method-2, ...
-- ############### 拆分实例--LIMIT ###############
SELECT column_name FROM tb_name LIMIT start, num_of_page;
-- 起始索引从0开始计数，起始索引=（页码数-1）*页面记录数
-- 分页查询是数据库的方言，不同数据库有不同的实现，MySQL是LIMIT
-- 如果分页查询的是第一页的数据，起始索引可以省略，简写为LIMIT 10
五、数据控制语言 -- DCL -- Data Control Language
DCL数据控制语言，用来创建管理数据库用户，管理控制数据库用户的访问权限。
sql-- ############### 创建管理用户 ###############
-- 查询所有用户，所有用户都存在与mysql这个数据库中！！！★
USE mysql
SELECT * FROM user;
-- 创建用户
CREATE USER 'username'@'hostname' IDENTIFIED BY 'PASSWD';
-- 修改用户密码
ALTER USER 'username'@'hostname' IDENTIFIED WITH mysql_native_password BY 'NEW PASSWD';
-- 删除用户
DROP USER 'username'@'hostname';
-- ############### 权限控制 ###############
-- 查询权限
SHOW GRANTS FOR 'username'@'hostname';
-- 授予权限
GRANT ALL PRIVILEGES ON db_name.tb_name TO 'username'@'hostname';
GRANT SELECT, UPDATE ON db_name.tb_name TO 'username'@'hostname';
-- 撤销权限
REVOKE priv_lists ON db_name.tb_name FROM 'username'@'hostname';
-- 权限刷新
FLUSH PRIVILEGES;
| 权限 | 说明 |
|---|---|
| ALL, ALL PRIVILEGES | 完全权限，所有权限 |
| SELECT | 查询数据权限 |
| INSERT | 新增插入数据的权限 |
| UPDATE | 修改数据的权限 |
| DELETE | 删除数据的权限 |
| ALTER | 修改数据库表，或者用户信息的权限 |
| DROP | 删除数据库或者用户的权限 |
| CREATE | 创建数据库或者表的权限 |
六、函数
MySQL 函数会对传递进来的参数进行处理，并返回一个处理结果，也就是返回一个值。MySQL 包含了一些常用的函数，剩余的可以到「MySQL 官网」查询。可以对 MySQL 常用函数进行简单的分类，大概包括聚合函数、字符串型函数、数值型函数、日期时间函数以及流程控制类函数等。
1. 聚合函数
| 函数 | 功能 |
|---|---|
| MIN | 查询指定列的最小值 |
| MAX | 查询指定列的最大值 |
| AVG | 求平均值，返回指定列数据的平均值 |
| COUNT | 统计查询结果的行数 |
| SUM | 求和，返回指定列的总和 |
2. 字符串函数
| 函数 | 功能 |
|---|---|
| concat | 将两个或多个字符串组合成一个字符串 |
| length | 以字节获取字符串的长度 |
| char_length | 以字符获取字符串的长度 |
| left | 获取指定长度的字符串的左边部分 |
| right | 获取指定长度的字符串的右边部分 |
| replace | 搜索并替换字符串中的子字符串 |
| substring | 从具有特定长度的位置开始提取一个子字符串 |
| trim | 从字符串中删除多余的指定字符，可以根据参数指定左右两端，以及字符信息 |
| ltrim | 从字符串左边删除多余的指定字符，可以根据参数指定字符信息 |
| rtrim | 从字符串右边删除多余的指定字符，可以根据参数指定字符信息 |
| format | 格式化具有特定区域设置的数字，舍入到小数位数 |
| upper | 将字符串内容转换成全部大写 |
| lower | 将字符串内容转换成全部小写 |
| soundex | 在WHERE条件中，根据发音规则来匹配选项 |
3. 数值计算函数
| 函数 | 功能 |
|---|---|
| abs | 取绝对值 |
| ceil | 向上取整 |
| div | 整除运算 |
| floor | 向下取整 |
| greatest | 返回列表中最大值 |
| least | 返回列表中最小值 |
| mod | 取模运算 |
| pi | 返回π的值 |
| pow | 返回x的y次方 |
| rand | 返回0-1之间的随机数 |
| round | 四舍五入取整 |
| sqrt | 计算平方根 |
| truncate | 返回数值x保留到小数点后y位的值，与ROUND最大的区别是不会进行四舍五入 |
4. 日期时间类函数
| 函数 | 功能 |
|---|---|
| curdate | 返回当前日期 |
| sysdate | 返回当前日期 |
| datediff | 计算两个DATE值之间的天数 |
| timediff | 计算两个TIME或DATETIME值之间的差值 |
| timestampdiff | 计算两个TIME或DATETIME值之间的差值 |
| week | 返回一个日期的星期数值 |
| weekday | 返回一个日期表示为工作日/星期几的索引 |
| dayofweek | 返回日期的工作日索引 |
| extract | 提取日期的一部分 |
| day | 获取指定日期月份的天(日) |
| month | 返回一个表示指定日期的月份的整数 |
| year | 返回日期值的年份部分 |
| now | 返回当前日期和时间 |
| date_add | 将时间值添加到日期值 |
| date_sub | 从日期值中减去时间值 |
| date_format | 根据指定的日期格式格式化日期值 |
| dayame | 获取指定日期的工作日的名称 |
| str_to_date | 将字符串转换为基于指定格式的日期和时间值 |
5. 流程控制类函数
| 函数 | 功能 |
|---|---|
| case | 如果满足WHEN分支中的条件，则返回THEN分支中的相应结果，否则返回ELSE分支中的结果 |
| if | 根据给定的条件返回一个值 |
| ifnull | 如果第一个参数不为NULL，则返回第一个参数，否则返回第二个参数 |
| nullif | 如果第一个参数等于第二个参数，则返回NULL，否则返回第一个参数 |
七、约束
在MySQL中，约束是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。例如，在数据表中存放年龄的值时，如果存入200、300这些无效的值就毫无意义了。因此，使用约束来限定表中的数据范围是很有必要的。MySQL主要支持如下几种约束：
- 主键约束
- 外键约束
- 唯一约束
- 检查约束
- 非空约束
- 默认值约束
这些约束可以在创建表的时候添加，也可以在创建完成之后修改，建议后者。另外，在修改完表约束之后，需要再次修改表约束的时候，需要注意，需要一次性指定所有的约束，而不能一个个指定，因为每次修改表约束，实际上是在重写覆盖。
sql-- 创建表结构时，指定约束类型
CREATE TABLE Infos (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`age` tinyint NOT NULL,
`gender` char(6) NOT NULL DEFAULT 'MALE',
`city` varchar(30) NULL,
`addr` varchar(90) NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(city) REFERENCES Workers(city),
CHECK(age < 120),
UNIQUE KEY(name, addr)
)COMMENT infos of students;
-- 创建表结构后，修改字段约束类型
-- 可以参考ALTER的语法，完全相同
八、多表查询
TBD. About in October, this part will be completed
联结 组合
九、事务
TBD. About in October, this part will be completed
十、其他补充
1. 数据类型
MySQL支持的数据类型很多，最常用的主要分为三类：数值类型，字符串类型以及日期时间类型，汇总如下
| 数值类型 | 大小 | 描述 |
|---|---|---|
| TINYINT | 1 byte | (-128, 127)或(0, 255) |
| SMALLINT | 2 bytes | (-32768, 32767)或(0, 65535) |
| MEDIUMINT | 3 bytes | (-8388608, 8388607)或(0, 16777215) |
| INT | 4 bytes | (-2147483648, 2147483647)或(0, 4294967295) |
| BIGINT | 8 bytes | (-2^63, 2^63-1)或(0, 2^64-1) |
| FLOAT | 4 bytes | (-3.4E+38, 3.4E+38)或(0, 1.75E-38~3.4E+38) |
| DOUBLE | 8 bytes | (-1.8E+308, 1.8E+308)或(0, 2.2E-308~1.8E+308) |
| DECIMAL | M精度,D标度 | 当使用小数的时候，M表示整体位数，D表示小数位数 |
| 字符串类型 | 大小 | 描述 |
| CHAR | 0~255 bytes | 定长字符串 |
| VARCHAR | 0~65535 bytes | 不定长字符串 |
| TINYBLOB | 0~255 bytes | 小型二进制形式的数据 |
| TINYTEXT | 0~255 bytes | 小型文本类型数据 |
| BLOB | 0~65535 bytes | 二进制形式的数据 |
| TEXT | 0~65535 bytes | 文本类型数据 |
| MEDIUMBLOB | 0~16777215 bytes | 中等规模二进制形式的数据 |
| MEDIUMTEXT | 0~16777215 bytes | 中等规模文本类型数据 |
| LONGBLOB | 0~4294967295 bytes | 极大规模二进制形式的数据 |
| LONGTEXT | 0~4294967295 bytes | 极大规模文本类型数据 |
| 时间类型 | 大小 | 格式 |
| DATE | 3 bytes | YYYY-MM-DD |
| TIME | 3 bytes | HH:MM |
| YEAR | 1 byte | YYYY |
| DATETIME | 8 bytes | YYYY-MM-DD HH:MM |
| TIMESTAMP | 4 bytes | YYYY-MM-DD HH:MM |
数字类型可以使用UNSIGNED或者SIGNED修饰符，表示有符号或者无符号；定长字符串指的是固定长度，比如char(10)和varchar(10)同时存储"hello"的时候，前者还是使用10个字符，而后者只会使用5个字符空间，另外在MySQL中，汉字也是只占用1个字符的；时间类型的DATETIME和TIMESTAMP的范围不同，后者只能到2038年。
2. 运算符
| 比较运算符 | 功能 |
|---|---|
| > | 判断大于 |
| >= | 判断大于等于 |
| < | 判断小于 |
| <= | 判断小于等于 |
| = | 判断等于 |
| <>或者!= | 判断不等于 |
| BETWEEN...AND... | 在某个闭区间范围内选择 |
| IN(...) | 选择IN之后的列表当中的一个 |
| LIKE 占位符 | 模糊匹配，_匹配单个字符，%匹配任一个字符 |
| IS NULL | 是NULL |
| 逻辑运算符 | 功能 |
| AND或者&& | 与，多个条件同时成立 |
| OR或者|| | 或者，多个条件任意成立一个 |
| NOT或者! | 非，不是 |
3. WHERE VS HAVING
WHERE和HAVING都是条件过滤，其执行顺序是WHERE最先执行，聚合函数其次，最后是HAVING，但是也有区别：
- 执行时机不同：WHERE是分组之前过滤，HAVING是分组之后过滤
- 判断条件不同：WHERE不能对聚合函数做判断，而HAVING可以
- 分组之后SELECT查询的一般是分组字段或者聚合函数，查询其他字段无意义
4. 备份与恢复
对于日常非生产环境，备份恢复还是挺随意的，使用mysqldump和mysql命令就可以了，SQL的备份方式还是很有讲究的，如下方式可能不适用于生产环境，但对于个人用户感觉是足够了。需要注意的是，无论使用哪种方法去备份，一定要备份sys库，因为这里面存储了用户信息，如果了解是具体哪几个表，也可以只备份那几个表。
sql-- 备份方法-1
mysql -e "show databases;" -u root | grep -Ev "Database|information_schema|performance_schema" | xargs mysqldump --skip-lock-tables -uroot --databases | gzip > backups.sql
-- 备份方法-2
mysqldump --skip-lock-tables -uroot --databases 数据库名 > backups.sql
-- 恢复方法
mysql -u root -p passwd < backups.sql
5. 书写规则
通常情况下SQL语句不区分大小写，因此SELECT和select是相同的，甚至SeLect也是可以起作用，但是为了规范书写以及让所写的代码更有可读性和可维护性，一般建议使用如下规则：
- 关键字：一般全部大写
- 数据库名：一般全部大写，使用一个单词或者多个单词以下划线连接组合
- 表名：一般首字母大写，使用一个单词或者多个单词以下划线连接组合
- 字段名：一般小写，使用一个单词或者多个单词以下划线连接组合
- 值：一般小写，看情况使用，无特殊规定
- 注释：大小写混用，看情况使用
6. 通配符
通配符一般用在WHERE子语句中做条件过滤，类似于各种语言中的正则表达式或者Bash中的通配符的概念。MySQL中的通配符是比较简单，相关的符号有如下几种：
- %: 任意字符出现任意次数，除了NULL
- _: 匹配单个字符，除了NULL
- []: 指定一个字符集，必须匹配指定位置的一个字符
- ^: 在方括号内开头位置表示取补集
【END】参考文档
本文作者:Manford Fan
本文链接:
版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！