# MySQL
## 简介
### 数据库
数据库：DataBase，简称 DB，用于存储和管理数据的仓库，它的存储空间很大，可以存放百万上亿条数据。
数据库的优势：
- 可以持久化存储数据
- 方便存储和管理数据
- 使用了统一的方式操作数据库 SQL
数据库、数据表、数据的关系介绍：
- 数据库
- 用于存储和管理数据的仓库
- 一个库中可以包含多个数据表
- 数据表
- 数据库最重要的组成部分之一
- 它由纵向的列和横向的行组成（类似 excel 表格）
- 可以指定列名、数据类型、约束等
- 一个表中可以存储多条数据
- 数据：想要永久化存储的数据
参考视频：https://www.bilibili.com/video/BV1zJ411M7TB
参考文章：https://time.geekbang.org/column/intro/139
参考书籍：https://book.douban.com/subject/35231266/
***
### MySQL
MySQL 数据库是一个最流行的关系型数据库管理系统之一
关系型数据库是将数据保存在不同的数据表中，而且表与表之间可以有关联关系，提高了灵活性。
缺点：数据存储在磁盘中，导致读写性能差，而且数据关系复杂，扩展性差
MySQL 所使用的SQL语句是用于访问数据库最常用的标准化语言。
MySQL 配置：
* MySQL 安装：https://www.jianshu.com/p/ba48f1e386f0
* MySQL 配置：
* 修改 MySQL 默认字符集：安装 MySQL 之后第一件事就是修改字符集编码
```mysql
vim /etc/mysql/my.cnf
添加如下内容：
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
```
* 启动 MySQL 服务：
```shell
systemctl start/restart mysql
```
* 登录 MySQL：
```shell
mysql -u root -p 敲回车，输入密码
初始密码查看：cat /var/log/mysqld.log
在root@localhost: 后面的就是初始密码
```
* 查看默认字符集命令：
```mysql
SHOW VARIABLES LIKE 'char%';
```
* 修改MySQL登录密码：
```mysql
set global validate_password_policy=0;
set global validate_password_length=1;
set password=password('密码');
```
* 授予远程连接权限（MySQL 内输入）：
```mysql
-- 授权
grant all privileges on *.* to 'root' @'%' identified by '密码';
-- 刷新
flush privileges;
```
* 修改 MySQL 绑定 IP：
```shell
cd /etc/mysql/mysql.conf.d
sudo chmod 666 mysqld.cnf
vim mysqld.cnf
#bind-address = 127.0.0.1注释该行
```
* 关闭 Linux 防火墙
```shell
systemctl stop firewalld.service
放行3306端口
```
***
### 常用工具
#### mysql
mysql 不是指 mysql 服务，而是指 mysql 的客户端工具
```sh
mysql [options] [database]
```
* -u --user=name：指定用户名
* -p --password[=name]：指定密码
* -h --host=name：指定服务器IP或域名
* -P --port=#：指定连接端口
* -e --execute=name：执行SQL语句并退出，在控制台执行SQL语句，而不用连接到数据库执行
示例：
```sh
mysql -h 127.0.0.1 -P 3306 -u root -p
mysql -uroot -p2143 db01 -e "select * from tb_book";
```
***
#### admin
mysqladmin 是一个执行管理操作的客户端程序，用来检查服务器的配置和当前状态、创建并删除数据库等
通过 `mysqladmin --help` 指令查看帮助文档
```sh
mysqladmin -uroot -p2143 create 'test01';
```
***
#### binlog
服务器生成的日志文件以二进制格式保存，如果需要检查这些文本，就要使用 mysqlbinlog 日志管理工具
```sh
mysqlbinlog [options] log-files1 log-files2 ...
```
* -d --database=name：指定数据库名称，只列出指定的数据库相关操作
* -o --offset=#：忽略掉日志中的前n行命令。
* -r --result-file=name：将输出的文本格式日志输出到指定文件。
* -s --short-form：显示简单格式， 省略掉一些信息。
* --start-datatime=date1 --stop-datetime=date2：指定日期间隔内的所有日志。
* --start-position=pos1 --stop-position=pos2：指定位置间隔内的所有日志。
***
#### dump
##### 命令介绍
mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移，备份内容包含创建表，及插入表的SQL语句
```sh
mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A
```
连接选项：
* -u --user=name：指定用户名
* -p --password[=name]：指定密码
* -h --host=name：指定服务器IP或域名
* -P --port=#：指定连接端口
输出内容选项：
* --add-drop-database：在每个数据库创建语句前加上 Drop database 语句
* --add-drop-table：在每个表创建语句前加上 Drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)
* -n --no-create-db：不包含数据库的创建语句
* -t --no-create-info：不包含数据表的创建语句
* -d --no-data：不包含数据
* -T, --tab=name：自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件，相当于select into outfile
示例：
```sh
mysqldump -uroot -p2143 db01 tb_book --add-drop-database --add-drop-table > a
mysqldump -uroot -p2143 -T /tmp test city
```
***
##### 数据备份
命令行方式：
* 备份命令：mysqldump -u root -p 数据库名称 > 文件保存路径
* 恢复
1. 登录MySQL数据库：`mysql -u root p`
2. 删除已经备份的数据库
3. 重新创建与备份数据库名称相同的数据库
4. 使用该数据库
5. 导入文件执行：`source 备份文件全路径`
图形化界面：
* 备份
* 恢复
***
#### import
mysqlimport 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件
```sh
mysqlimport [options] db_name textfile1 [textfile2...]
```
示例：
```sh
mysqlimport -uroot -p2143 test /tmp/city.txt
```
导入 sql 文件，可以使用 MySQL 中的 source 指令 :
```mysql
source 文件全路径
```
***
#### show
mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引
```sh
mysqlshow [options] [db_name [table_name [col_name]]]
```
* --count：显示数据库及表的统计信息（数据库，表 均可以不指定）
* -i：显示指定数据库或者指定表的状态信息
示例：
```sh
#查询每个数据库的表的数量及表中记录的数量
mysqlshow -uroot -p1234 --count
#查询test库中每个表中的字段书，及行数
mysqlshow -uroot -p1234 test --count
#查询test库中book表的详细情况
mysqlshow -uroot -p1234 test book --count
```
***
## 体系结构
### 整体架构
体系结构详解：
* 第一层：网络连接层
* 一些客户端和链接服务，包含本地 socket 通信和大多数基于客户端/服务端工具实现的 TCP/IP 通信，主要完成一些类似于连接处理、授权认证、及相关的安全方案
* 在该层上引入了连接池 Connection Pool 的概念，管理缓冲用户连接，线程处理等需要缓存的需求
* 在该层上实现基于 SSL 的安全链接，服务器也会为安全接入的每个客户端验证它所具有的操作权限
- 第二层：核心服务层
* 查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，所有的内置函数（日期、数学、加密函数等）
* Management Serveices & Utilities：系统管理和控制工具，备份、安全、复制、集群等
* SQL Interface：接受用户的 SQL 命令，并且返回用户需要查询的结果
* Parser：SQL 语句分析器
* Optimizer：查询优化器
* Caches & Buffers：查询缓存，服务器会查询内部的缓存，如果缓存空间足够大，可以在大量读操作的环境中提升系统性能
* 所有**跨存储引擎的功能**在这一层实现，如存储过程、触发器、视图等
* 在该层服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询顺序，是否利用索引等， 最后生成相应的执行操作
* MySQL 中服务器层不管理事务，**事务是由存储引擎实现的**
- 第三层：存储引擎层
- Pluggable Storage Engines：存储引擎接口，MySQL 区别于其他数据库的重要特点就是其存储引擎的架构模式是插件式的（存储引擎是基于表的，而不是数据库）
- 存储引擎**真正的负责了 MySQL 中数据的存储和提取**，服务器通过 API 和存储引擎进行通信
- 不同的存储引擎具有不同的功能，共用一个 Server 层，可以根据开发的需要，来选取合适的存储引擎
- 第四层：系统文件层
- 数据存储层，主要是将数据存储在文件系统之上，并完成与存储引擎的交互
- File System：文件系统，保存配置文件、数据文件、日志文件、错误文件、二进制文件等
***
### 执行流程
#### 连接器
池化技术：对于访问数据库来说，建立连接的代价是比较昂贵的，频繁的创建关闭连接比较耗费资源，有必要建立数据库连接池，以提高访问的性能
首先连接到数据库上，这时连接器发挥作用，连接完成后如果没有后续的动作，这个连接就处于空闲状态，通过指令查看连接状态
SHOW PROCESSLIST：查看当前 MySQL 在进行的线程，可以实时地查看 SQL 的执行情况，其中的 Command 列显示为 Sleep 的这一行，就表示现在系统里面有一个空闲连接
| 参数 | 含义 |
| ------- | ------------------------------------------------------------ |
| ID | 用户登录 mysql 时系统分配的 connection_id，可以使用函数 connection_id() 查看 |
| User | 显示当前用户，如果不是 root，这个命令就只显示用户权限范围的 sql 语句 |
| Host | 显示这个语句是从哪个 ip 的哪个端口上发的，可以用来跟踪出现问题语句的用户 |
| db | 显示这个进程目前连接的是哪个数据库 |
| Command | 显示当前连接的执行的命令，一般取值为休眠 Sleep、查询 Query、连接 Connect 等 |
| Time | 显示这个状态持续的时间，单位是秒 |
| State | 显示使用当前连接的 sql 语句的状态，以查询为例，需要经过 copying to tmp table、sorting result、sending data等状态才可以完成 |
| Info | 显示执行的 sql 语句，是判断问题语句的一个重要依据 |
连接建立 TCP 以后需要做**权限验证**，验证成功后可以进行执行 SQL。如果这时管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限，只有再新建的连接才会使用新的权限设置
客户端如果太长时间没动静，连接器就会自动断开，这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：`Lost connection to MySQL server during query`
数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接；短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。为了减少连接的创建，推荐使用长连接，但是过多的长连接会造成 OOM，解决方案：
* 定期断开长连接，使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
* MySQL 5.7 版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源，这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态
***
#### 查询缓存
##### 工作流程
当执行完全相同的 SQL 语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存
查询过程：
1. 客户端发送一条查询给服务器
2. 服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果（一般是 K-V 键值对），否则进入下一阶段
3. 分析器进行 SQL 分析，再由优化器生成对应的执行计划
4. MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询
5. 将结果返回给客户端
大多数情况下不建议使用查询缓存，因为查询缓存往往弊大于利
* 查询缓存的**失效非常频繁**，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能费力地把结果存起来，还没使用就被一个更新全清空了，对于更新压力大的数据库来说，查询缓存的命中率会非常低
* 除非业务就是有一张静态表，很长时间才会更新一次，比如一个系统配置表，那这张表上的查询才适合使用查询缓存
***
##### 缓存配置
1. 查看当前的 MySQL 数据库是否支持查询缓存：
```mysql
SHOW VARIABLES LIKE 'have_query_cache'; -- YES
```
2. 查看当前MySQL是否开启了查询缓存：
```mysql
SHOW VARIABLES LIKE 'query_cache_type'; -- OFF
```
参数说明：
* OFF 或 0：查询缓存功能关闭
* ON 或 1：查询缓存功能打开，查询结果符合缓存条件即会缓存，否则不予缓存；可以显式指定 SQL_NO_CACHE 不予缓存
* DEMAND 或 2：查询缓存功能按需进行，显式指定 SQL_CACHE 的 SELECT 语句才缓存，其它不予缓存
```mysql
SELECT SQL_CACHE id, name FROM customer; -- SQL_CACHE:查询结果可缓存
SELECT SQL_NO_CACHE id, name FROM customer;-- SQL_NO_CACHE:不使用查询缓存
```
3. 查看查询缓存的占用大小：
```mysql
SHOW VARIABLES LIKE 'query_cache_size';-- 单位是字节 1048576 / 1024 = 1024 = 1KB
```
4. 查看查询缓存的状态变量：
```mysql
SHOW STATUS LIKE 'Qcache%';
```
| 参数 | 含义 |
| ----------------------- | ------------------------------------------------------------ |
| Qcache_free_blocks | 查询缓存中的可用内存块数 |
| Qcache_free_memory | 查询缓存的可用内存量 |
| Qcache_hits | 查询缓存命中数 |
| Qcache_inserts | 添加到查询缓存的查询数 |
| Qcache_lowmen_prunes | 由于内存不足而从查询缓存中删除的查询数 |
| Qcache_not_cached | 非缓存查询的数量（由于 query_cache_type 设置而无法缓存或未缓存） |
| Qcache_queries_in_cache | 查询缓存中注册的查询数 |
| Qcache_total_blocks | 查询缓存中的块总数 |
5. 配置 my.cnf：
```sh
sudo chmod 666 /etc/mysql/my.cnf
vim my.cnf
# mysqld中配置缓存
query_cache_type=1
```
重启服务既可生效，执行 SQL 语句进行验证 ，执行一条比较耗时的 SQL 语句，然后再多执行几次，查看后面几次的执行时间；获取通过查看查询缓存的缓存命中数，来判定是否走查询缓存
***
##### 缓存失效
查询缓存失效的情况：
* SQL 语句不一致的情况，要想命中查询缓存，查询的 SQL 语句必须一致
```mysql
select count(*) from tb_item;
Select count(*) from tb_item; -- 不走缓存，首字母不一致
```
* 当查询语句中有一些不确定查询时，则不会缓存，比如：now()、current_date()、curdate()、curtime()、rand()、uuid()、user()、database()
```mysql
SELECT * FROM tb_item WHERE updatetime < NOW() LIMIT 1;
SELECT USER();
SELECT DATABASE();
```
* 不使用任何表查询语句：
```mysql
SELECT 'A';
```
* 查询 mysql、information_schema、performance_schema 等系统表时，不走查询缓存：
```mysql
SELECT * FROM information_schema.engines;
```
* 在跨存储引擎的存储过程、触发器或存储函数的主体内执行的查询，缓存失效
* 如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除，包括使用 MERGE 映射到已更改表的表的查询，比如：INSERT、UPDATE、DELETE、ALTER TABLE、DROP TABLE、DROP DATABASE
***
#### 分析器
没有命中查询缓存，就开始了 SQL 的真正执行，分析器会对 SQL 语句做解析
```sql
SELECT * FROM t WHERE id = 1;
```
* 先做**词法分析**，输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。从输入的 select 这个关键字识别出来这是一个查询语句；把字符串 t 识别成 表名 t，把字符串 id 识别成列 id
* 然后做**语法分析**，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果你的语句不对，就会收到 `You have an error in your SQL syntax` 的错误提醒
***
#### 优化器
##### 成本分析
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
* 根据搜索条件找出所有可能的使用的索引
* 成本分析，执行成本由 I/O 成本和 CPU 成本组成，计算全表扫描和使用不同索引执行 SQL 的代价
* 找到一个最优的执行方案，用最小的代价去执行语句
在数据库里面，扫描行数是影响执行代价的因素之一，扫描的行数越少意味着访问磁盘的次数越少，消耗的 CPU 资源越少，优化器还会结合是否使用临时表、是否排序等因素进行综合判断
***
##### 统计数据
MySQL 中保存着两种统计数据：
* innodb_table_stats 存储了表的统计数据，每一条记录对应着一个表的统计数据
* innodb_index_stats 存储了索引的统计数据，每一条记录对应着一个索引的一个统计项的数据
MySQL 在真正执行语句之前，并不能精确地知道满足条件的记录有多少条，只能根据统计信息来估算记录，统计信息就是索引的区分度,一个索引上不同的值的个数（比如性别只能是男女，就是 2 ），称之为基数（cardinality），基数越大说明区分度越好
通过**采样统计**来获取基数，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数
在 MySQL 中，有两种存储统计数据的方式，可以通过设置参数 `innodb_stats_persistent` 的值来选择：
* ON：表示统计信息会持久化存储（默认），采样页数 N 默认为 20，可以通过 `innodb_stats_persistent_sample_pages `指定，页数越多统计的数据越准确，但消耗的资源更大
* 设置为 off 时，表示统计信息只存储在内存，采样页数 N 默认为 8，也可以通过系统变量设置（不推荐，每次重新计算浪费资源）
数据表是会持续更新的，两种更新方式：
* 设置 `innodb_stats_auto_recalc` 为 1，当发生变动的记录数量超过表大小的 10% 时，自动触发重新计算，不过是**异步进行**
* 调用 `ANALYZE TABLE t` 手动更新统计信息，只对信息做重新统计（不是重建表），没有修改数据，这个过程中加了 MDL 读锁并且是同步进行，所以会暂时阻塞系统
EXPLAIN 执行计划在优化器阶段生成，如果 explain 的结果预估的 rows 值跟实际情况差距比较大，可以执行 analyze 命令重新修正信息
***
##### 错选索引
采样统计本身是估算数据，或者 SQL 语句中的字段选择有问题时，可能导致 MySQL 没有选择正确的执行索引
解决方法：
* 采用 force index 强行选择一个索引
```sql
SELECT * FROM user FORCE INDEX(name) WHERE NAME='seazean';
```
* 可以考虑修改 SQL 语句，引导 MySQL 使用期望的索引
* 新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引
***
#### 执行器
开始执行的时候，要先判断一下当前连接对表有没有执行查询的权限，如果没有就会返回没有权限的错误，在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。如果有权限，就打开表继续执行，执行器就会根据表的引擎定义，去使用这个引擎提供的接口
****
### 数据空间
#### 数据存储
系统表空间是用来放系统信息的，比如数据字典什么的，对应的磁盘文件是 ibdata，数据表空间是一个个的表数据文件，对应的磁盘文件就是表名.ibd
表数据既可以存在共享表空间里，也可以是单独的文件，这个行为是由参数 innodb_file_per_table 控制的：
* OFF：表示表的数据放在系统共享表空间，也就是跟数据字典放在一起
* ON ：表示每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中（默认）
一个表单独存储为一个文件更容易管理，在不需要这个表时通过 drop table 命令，系统就会直接删除这个文件；如果是放在共享表空间中，即使表删掉了，空间也是不会回收的
***
#### 数据删除
MySQL 的数据删除就是移除掉某个记录后，该位置就被标记为可复用，如果有符合范围条件的数据可以插入到这里。符合范围条件的意思是假设删除记录 R4，之后要再插入一个 ID 在 300 和 600 之间的记录时，就会复用这个位置
InnoDB 的数据是按页存储的如果删掉了一个数据页上的所有记录，整个数据页就可以被复用了，如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用
删除命令其实只是把记录的位置，或者**数据页标记为了可复用，但磁盘文件的大小是不会变的**，这些可以复用还没有被使用的空间，看起来就像是空洞，造成数据库的稀疏，因此需要进行紧凑处理
***
#### 空间收缩
重建表就是按照主键 ID 递增的顺序，把数据一行一行地从旧表中读出来再插入到新表中，重建表时 MySQL 会自动完成转存数据、交换表名、删除旧表的操作，重建命令：
```sql
ALTER TABLE A ENGINE=InnoDB
```
工作流程：新建临时表 tmp_table B（在 Server 层创建的），把表 A 中的数据导入到表 B 中，操作完成后用表 B 替换表 A，完成重建
重建表的步骤需要 DDL 不是 Online 的，因为在导入数据的过程有新的数据要写入到表 A 的话，就会造成数据丢失
MySQL 5.6 版本开始引入的 Online DDL，重建表的命令默认执行此步骤：
* 建立一个临时文件 tmp_file（InnoDB 创建），扫描表 A 主键的所有数据页
* 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中
* 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态
* 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3
* 用临时文件替换表 A 的数据文件
Online DDL 操作会先获取 MDL 写锁，再退化成 MDL 读锁。但 MDL 写锁持有时间比较短，所以可以称为 Online； 而 MDL 读锁，不阻止数据增删查改，但会阻止其它线程修改表结构
问题：想要收缩表空间，执行指令后整体占用空间增大
原因：在重建表后 InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新使用。表在未整理之前页已经占用 15/16 以上，收缩之后需要保持数据占用空间在 15/16，所以文件占用空间更大才能保持
注意：临时文件也要占用空间，如果空间不足会重建失败
****
#### inplace
DDL 中的临时表 tmp_table 是在 Server 层创建的，Online DDL 中的临时文件 tmp_file 是 InnoDB 在内部创建出来的，整个 DDL 过程都在 InnoDB 内部完成，对于 Server 层来说，没有把数据挪动到临时表，是一个原地操作，这就是 inplace
两者的关系：
* DDL 过程如果是 Online 的，就一定是 inplace 的
* inplace 的 DDL，有可能不是 Online 的，截止到 MySQL 8.0，全文索引（FULLTEXT）和空间索引 (SPATIAL ) 属于这种情况
参考文章：https://time.geekbang.org/column/article/72388
***
## 单表操作
### SQL
- SQL
- Structured Query Language：结构化查询语言
- 定义了操作所有关系型数据库的规则，每种数据库操作的方式可能会存在不一样的地方，称为“方言”
- SQL 通用语法
- SQL 语句可以单行或多行书写，以**分号结尾**。
- 可使用空格和缩进来增强语句的可读性。
- MySQL 数据库的 SQL 语句不区分大小写，**关键字建议使用大写**。
- 数据库的注释：
- 单行注释：-- 注释内容 #注释内容(mysql特有)
- 多行注释：/* 注释内容 */
- SQL 分类
- DDL（Data Definition Language）数据定义语言
- 用来定义数据库对象：数据库，表，列等。关键字：create、drop,、alter 等
- DML（Data Manipulation Language）数据操作语言
- 用来对数据库中表的数据进行增删改。关键字：insert、delete、update 等
- DQL（Data Query Language）数据查询语言
- 用来查询数据库中表的记录(数据)。关键字：select、where 等
- DCL（Data Control Language）数据控制语言
- 用来定义数据库的访问权限和安全级别，及创建用户。关键字：grant， revoke等
***
### DDL
#### 数据库
* R(Retrieve)：查询
* 查询所有数据库：
```mysql
SHOW DATABASES;
```
* 查询某个数据库的创建语句
```sql
SHOW CREATE DATABASE 数据库名称; -- 标准语法
SHOW CREATE DATABASE mysql; -- 查看mysql数据库的创建格式
```
* C(Create)：创建
* 创建数据库
```mysql
CREATE DATABASE 数据库名称;-- 标准语法
CREATE DATABASE db1; -- 创建db1数据库
```
* 创建数据库（判断，如果不存在则创建）
```mysql
CREATE DATABASE IF NOT EXISTS 数据库名称;
```
* 创建数据库，并指定字符集
```mysql
CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称;
```
* 例如：创建db4数据库、如果不存在则创建，指定字符集为gbk
```mysql
-- 创建db4数据库、如果不存在则创建，指定字符集为gbk
CREATE DATABASE IF NOT EXISTS db4 CHARACTER SET gbk;
-- 查看db4数据库的字符集
SHOW CREATE DATABASE db4;
```
* U(Update)：修改
* 修改数据库的字符集
```mysql
ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;
```
* 常用字符集：
```mysql
--查询所有支持的字符集
SHOW CHARSET;
--查看所有支持的校对规则
SHOW COLLATION;
-- 字符集: utf8,latinI,GBK,,GBK是utf8的子集
-- 校对规则: ci 大小定不敏感，cs或bin大小写敏感
```
* D(Delete)：删除
* 删除数据库：
```mysql
DROP DATABASE 数据库名称;
```
* 删除数据库(判断，如果存在则删除)：
```mysql
DROP DATABASE IF EXISTS 数据库名称;
```
* 使用数据库：
* 查询当前正在使用的数据库名称
```mysql
SELECT DATABASE();
```
* 使用数据库
```mysql
USE 数据库名称； -- 标准语法
USE db4; -- 使用db4数据库
```
***
#### 数据表
- R(Retrieve)：查询
- 查询数据库中所有的数据表
```mysql
USE mysql;-- 使用mysql数据库
SHOW TABLES;-- 查询库中所有的表
```
- 查询表结构
```mysql
DESC 表名;
```
- 查询表字符集
```mysql
SHOW TABLE STATUS FROM 库名 LIKE '表名';
```
- C(Create)：创建
- 创建数据表
```mysql
CREATE TABLE 表名(
列名1 数据类型1,
列名2 数据类型2,
....
列名n 数据类型n
);
-- 注意：最后一列，不需要加逗号
```
- 复制表
```mysql
CREATE TABLE 表名 LIKE 被复制的表名; -- 标准语法
CREATE TABLE product2 LIKE product; -- 复制product表到product2表
```
- 数据类型
| 数据类型 | 说明 |
| --------- | ------------------------------------------------------------ |
| INT | 整数类型 |
| DOUBLE | 小数类型 |
| DATE | 日期，只包含年月日：yyyy-MM-dd |
| DATETIME | 日期，包含年月日时分秒：yyyy-MM-dd HH:mm:ss |
| TIMESTAMP | 时间戳类型，包含年月日时分秒：yyyy-MM-dd HH:mm:ss
如果不给这个字段赋值或赋值为null，则默认使用当前的系统时间 |
| VARCHAR | 字符串
name varchar(20):姓名最大20个字符：zhangsan8个字符,张三2个字符 |
`INT(n)`：n 代表位数
* 3：int（9）显示结果为 000000010
* 3：int（3）显示结果为 010
`varchar(n)`：n 表示的是字符数
- 例如：
```mysql
-- 使用db3数据库
USE db3;
-- 创建一个product商品表
CREATE TABLE product(
id INT, -- 商品编号
NAME VARCHAR(30), -- 商品名称
price DOUBLE, -- 商品价格
stock INT, -- 商品库存
insert_time DATE -- 上架时间
);
```
- U(Update)：修改
- 修改表名
```mysql
ALTER TABLE 表名 RENAME TO 新的表名;
```
- 修改表的字符集
```mysql
ALTER TABLE 表名 CHARACTER SET 字符集名称;
```
- 添加一列
```mysql
ALTER TABLE 表名 ADD 列名 数据类型;
```
- 修改列数据类型
```mysql
ALTER TABLE 表名 MODIFY 列名 新数据类型;
```
- 修改列名称和数据类型
```mysql
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
```
- 删除列
```mysql
ALTER TABLE 表名 DROP 列名;
```
- D(Delete)：删除
- 删除数据表
```mysql
DROP TABLE 表名;
```
- 删除数据表(判断，如果存在则删除)
```mysql
DROP TABLE IF EXISTS 表名;
```
***
### DML
#### INSERT
* 新增表数据
* 新增格式 1：给指定列添加数据
```mysql
INSERT INTO 表名(列名1,列名2...) VALUES (值1,值2...);
```
* 新增格式 2：默认给全部列添加数据
```mysql
INSERT INTO 表名 VALUES (值1,值2,值3,...);
```
* 新增格式 3：批量添加数据
```mysql
-- 给指定列批量添加数据
INSERT INTO 表名(列名1,列名2,...) VALUES (值1,值2,...),(值1,值2,...)...;
-- 默认给所有列批量添加数据
INSERT INTO 表名 VALUES (值1,值2,值3,...),(值1,值2,值3,...)...;
```
* 字符串拼接
```mysql
CONCAT(string1,string2,'',...)
```
* 注意事项
- 列名和值的数量以及数据类型要对应
- 除了数字类型，其他数据类型的数据都需要加引号(单引双引都可以，推荐单引)
***
#### UPDATE
* 修改表数据语法
* 标准语法
```mysql
UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,... [where 条件];
```
* 修改电视的价格为1800、库存为36
```mysql
UPDATE product SET price=1800,stock=36 WHERE NAME='电视';
SELECT * FROM product;-- 查看所有商品信息
```
* 注意事项
- 修改语句中必须加条件
- 如果不加条件，则将所有数据都修改
***
#### DELETE
* 删除表数据语法
```mysql
DELETE FROM 表名 [WHERE 条件];
```
* 注意事项
* 删除语句中必须加条件
* 如果不加条件，则将所有数据删除
***
### DQL
#### 查询语法
数据库查询遵循条件在前的原则
```mysql
SELECT DISTINCT