MySQL学习笔记_蔡建伟博客

# SQL入门
结构化查询语言（Structured Query Language）

## 根据要操作的对象不同的分类
- 数据定义语言【DDL】：对保存数据的格式进行定义
- 数据操作（管理）语言【DML】：对数据进行操作
	- 查询【DQL】：获得数据
	- 管理【DML】：增加、删除、修改数据
- 数据库管理（控制）语言【DCL】：对数据库软件服务进行操作

## 标识符命名规则
1. 大小写取决于操作系统（因为每创建一个数据库，在data文件夹下面就会创建一个同名文件夹）为考虑兼容性，就认为其区分大小写
2. 取一个有意义的名字，见名知意，推荐使用下划线分割方式
3. 原则上可以使用任意字符、数字、符号、甚至是中文（要求客户端编码），但是一些特殊的组合，例如纯数字组合、特殊符号、包括mysql内部关键字，应该使用标识符限定符\`\`来包裹

> 其他说明:在命令行客户端执行sql语句的时候，要求使用语句结束符;来结束

# 库操作（DDL）
## 创建数据库（create）
语法：`Create database[  if not exists] 数据库名[ 数据库选项]`
说明：
- if not exists：容错方式，如果不存在才创建数据库
- 数据库选项：
	1. 字符集
	2. 校对规则（注：如果没有指定数据库的字符集，则使用数据库安装时默认的字符集）

## 数据库查询
- 查看当前存在的数据库：`show databases;`
- 查看数据库的创建语句：`show create database 数据库名;`

## 删除数据库
语法：`drop database 数据库名;`【如果数据库不存在，报错】

## 修改数据库
- 修改数据库信息
	1. 语法：alter database 数据库名[修改指令]
	2. 指令：数据库属性的修改
- 修改数据库名称
	1. 修改存在data文件夹中的相应文件夹名称
	2. 将数据库内容全部导出，新建一个数据库，将内容导入，删除旧数据库
	3. 创建一个新数据库，将旧数据库内的表，都移动（重命名）到新数据库内

# 表操作（DDL）
## 表的操作说明
数据库是表的容器，表必须属于某个容器，因此要操作哪个表，必须指明该表所属的数据库，有两种方式:
1. 进行表操作之前，指定当前默认的数据库：use 数据库名;//如果任何的标识符出现了特殊字符，需要使用\`\`
2. 每次操作表的时候都使用表的全称：数据库名.表名

> 表前缀的概念：为了区分相同逻辑表名的不同应用，给逻辑表名增加前缀，形成真实表名，前缀并不是MySQL的知识，对于MySQL前缀知识数据表名的一部分，所以表前缀是逻辑的

## 创建表
语法：`Create table 表名(列结构)[表选项]`
创建表的前提：先分析需要保存的实体数据拥有哪些属性，这些属性应该如何保存

列结构：
- 规则：列名 列的数据类型[ 列的属性（约束）]
- 多个列之间用,分割

## 查看表
> 如是数据较多，为了排版好看，可以使用\G作为结束符代替;

### 查看当前数据库中有哪些数据表
语法：`show tables[ like 条件字符串]`
> 说明：表示只获得符合条件的表名

条件字符串要用通配符占位，sql有两种通配符：
1. %：可以匹配任意长度的字符串，包括空字符串
2. _：可以匹配任意一个字符，不包括空字符串

### 查看数据表的创建信息
语法：`show create table 表名;`

### 查看表结构（描述表结构）
语法：`desc/describe 表名;`

## 删除表
语法：`drop table 表名;`【如果表不存在，报错】

## 修改表
### 修改表名【rename】
语法：`rename table 旧表名 to 新表名;`
说明：
- 支持同时修改多个表，所以可以用于交换表名：rename table 表名1 to 临时表名，表名2 to表名1，临时表名 to表名2;
- 支持跨数据库重命名，实际上相当于移动（剪切粘贴），故而可以利用数据表重命名完成数据库重命名操作

### 修改列定义
语法：`alter table 表名 子命令1,子命令2,.....;`
修改包括以下的情况（子命令）:
1. 增加一个字段（列）：`add 新列的定义`，如`add name varchar(20);`增加一个name字段
2. 删除一个字段（列）：`drop 要删除的列`，如`drop name;`删除name字段
3. 修改一个字段（列）属性：`modify 要修改的列的新定义`，如`modify name varchar(40);`将name字段的最大长度修改为40个字符
4. 重命名一个字段（列）：`change 要修改的列名 新列定义`，如`change name s_name varchar(20);`将name字段重命名为s_name

> change 可以重命名，也包括了修改属性，比modify 多了一个重命名功能

### 修改表选项
语法：alter table 表名 新的表选项;

## 复制表
- `create table 新表名 like 被复制的表名;`：复制表结构
- `create table 新表名 select * from 被复制的表名;`：复制表结构和表的数据

# 数据操作（DML）
## 创建数据（插入数据）
语法：`insert into 表名(字段列表) values(值的列表);`
说明：
- 如果插入全部数据，可以省略字段列表，即`insert into 表名 values(值的列表);`
- 可以一次性插入多行数据，`insert into 表名(字段列表) values(值的列表),(值的列表)…;`

## 获得数据（查询数据）
语法：`select 字段列表 from 表名 [查询条件] [子句];`
说明：
- 如果查询全部数据，可以使用\*代替字段列表
- 查询条件可以省略，表示获得所有的记录，相当于where 1

## 删除数据【不可逆，谨慎操作】
语法：`delete from 表名[ 条件];`
说明：
- 虽然条件是可选，但在删除语句的时候必须写条件，否则会删除全部数据，即使真想删除全部数据，也建议使用where 1
- 为了安全性，建议在删除之前，先使用该条件将数据查询出来，确认无误在删除

## 修改数据【不可逆，谨慎操作】
语法：`update 表名 set 字段=新值,字段=新值…[ 条件];`
说明：虽然条件可选，但在修改语句的时候必须写条件，否则会对全部数据进行修改，即使真想修改全部数据，也建议使用where 1

# MySQL操作之琐碎知识点总结

## 字符集
如果通过客户端操作服务器，那么客户端与服务器之间进行数据通信， 要保证编码一致。可以将互相发送的数据，转换为目标可以接收的编码
可以通过`show variables like 'character_set%';`来查看当前的字符集编码设置

|  字段名  | 含义   |
| ------------ | ------------ |
| character_set_client  | 连接使用【发送数据】的字符集编码   |
| character_set_connection | 连接使用【转换数据】的字符集编码   |
| character_set_database   | 当前MySQL数据库的默认的字符集编码   |
| character_set_filesystem   | 文件系统的二进制编码   |
| character_set_results    |  连接使用【返回数据】的字符集编码  |
| character_set_server    | 服务器字符集编码，在配置文件my.ini中有配置：character-set-server=utf8，可以直接修改文件，重启后生效  |
| character_set_system  |  系统编码，一般认为是数据库标识符（数据库名、表名、字段名）的编码  |
| character_sets_dir     | --  |

设置字符集编码，可以使用`set 变量名=值;`来设置，还可以使用`set names 值;`来设置连接使用的字符集编码（client、connection、results ），如果情况复杂，需要分开设置
转换的过程：client=>connection=>[服务器内部编码]=>result

## 校验规则
每一套字符集都有与之相关的校对规则（排序的规则）
### 什么是校对规则
校对规则就是在当前编码下，字符之间的比较顺序

### 校对规则分类
每个字符集都提供一个或者多个校对规则，常见的命名规则是：字符集\_地区名\_ci|cs|bin
- ci：不区分大小写
- cs：区分大小写
- bin：二进制编码比较【可以认为区分大小写，按照字节比较】

### 查看当前所有校对规则
`show collation;`
> 查看某个字符集的校对集，使用like

### 校对规则的设置
- 校对规则是依赖于字符集的
- 在设置字符集的时候，可以设置当前字符集所采用的校对规则
- 每个字符集都有一个默认的校对规则，如果不设置校对规则，则使用默认的校对规则
- 语法：character set 字符集 collate 校对规则

### utf8与gbk的选择
- 字节来说，utf8一个汉字占用3个字节，gbk一个汉字占用2个字节
- 排序来说，gbk对汉字的排序是按照拼音的排序

> 如果只是使用汉语，优先考虑gbk的编码

## 列类型
列的所有类型（在字段名后面定义，必写）
![](https://statics.caijw.com/uploads/2019/12/YWKFjJ63834kzM1mWi0vzifL6GcJNE5aySZYpEOY.png)

### 数值类型之整型
| 类型   | 字节  | 最小值（有符号/无符号）  | 最大值（有符号/无符号）  |
| ------------ | ------------ | ------------ | ------------ |
| TINYINT   | 1  |  -128/0  | 127/255   |
| SMALLINT   |  2 | -32768/0   |  32767/65535  |
|  MEDIUMINT  |  3 |  -8388608/0   |  8388607/16777215  |
|  INT/INTEGE  | 4  | -2147483648/0   | 2147483647/4294967295   |
|  BIGINT  | 8  | -9223372036854775808/0   |  9223372036854775807/18446744073709551615  |

说明：
- 可以在定义时，使用unsigned标识没有符号，如果不写就认为有符号，如：id tinyint unsigned;
- 可以在类型后面使用(M)，M标识显示的最小宽度（不影响存储范围，存储范围已经由类型决定了）
	- 当位数不足的时候，如果后面有zerofill，则前导零填充，如：id tinyint(2) zerofill;
	- 如果位数超出，不做限制，因为是最小宽度

### 数值类型之小数
| 类型(浮点型)    | 字节  | 最小值（理论）   | 最大值（理论）   | 有效精度 |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| FLOAT    | 4  | -3.402823466\*10^38    | 3.402823466\*10^38    | 6-7位  |
| DOUBLE    | 8  | -1.7976931348623157\*10^308    | 1.7976931348623157\*10^308    | 16-17位  |

说明：
- 浮点数支持数值的控制，type(M,D)，可以控制范围和小数点后有几位
	- M表示所有的数值位数，不包括小数点和正负号
	- D表示允许的小数位数
	> 注：M和D都是最大值，小数位最多允许D位，整数位最多允许(M-D)位，如double(8,3)，存入123456.5是报错的，因为整数位超出
- 浮点数支持科学计数法：1.235\*10^5的科学计数法是：1.235E5

| 类型(定点数)    | 字节  |
| ------------ | ------------ |
| DECIMAL    | 变长，大致每9个数字，采用四个字节存储，整数和分数分开计算   |

说明：
- 定点数支持数值的控制，type(M,D)，可以控制范围和小数点后有几位
- M表示所有的数值位数，不包括小数点和正负号，默认为10
- D表示允许的小数位数，默认为0

注：
- M是最大宽度，当M的值大于总的数值位数的时候，如果后面有zerofill，则前导零填充
- D是固定值，但如果小数位数超过该值，不会报错，而是四舍五入

> 小数（浮点、定点）支持无符号unsigned，但是范围不会增加，所以很少使用无符号小数

### 时间和日期类型
| 类型   | 显示格式   | 取值   | 存储空间   | 零值   |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| datetime   | YYYY-MM-DD HH:MM:SS   | 从1000-01-01 00:00:00到9999-12-31 23:59:59   | 8  | 0000-00-00 00:00:00   |
| timestamp   | YYYY-MM-DD HH:MM:SS   | 从1970-01-01 00:00:00到2038-01-19 03:14:07   | 4  | 0000-00-00 00:00:00   |
| date   | YYYY-MM-DD   | 从1000-01-01到9999-12-31   | 3  | 0000-00-00  |
|  time  | HH:MM:SS   | 从-838:59:59到838:59:59   | 3  | 00:00:00   |
|  year  | YYYY   | 从1901到2155   | 1  | 0000   |

说明：
- datetime：年月日时分秒
	- 支持两位年份，是通过【70-69】加以区分：70-99是19**，而0到69是20**，但理解起来容易有歧义，不建议使用
	- 如果只给年月日，没有给时分秒，时分秒取0值
	- 存在0年0月0日（存的时候存数字0，而不是字符串0）为了处理比较特殊的需求：如2014-04-00表示4月整个月（逻辑）
- timestamp：时间戳
	- 存储是整型，显示的时候类似datetime类型
	- select查询的时候，在时间戳字段后面+0，会去掉分隔符，如`select timestamp+0 from php_class`
- date：年月日，是datetime的一部分，datetime的年月日部分的规则同样适用于date
- time：时分秒，也是datetime的一部分，但是time可以表示两个含义
	- 可以表示一天中的时间
	- 可以表示一段时间间隔，所以时间的取值范围比正常时间大（但也有范围限制，35天之内，不到35天），在插入的时候可以按天插入，如插入：'5 12:34:11'（天数和小时用空格分隔），在数据库中会计算显示，是132:34:11
- year：年类型，只占用一个字节，所以表示范围是1901-2155共255年

### 字符类型
#### char与varchar
- 相同点：
	- 在使用时，后面要加上(M)
	- 当数据真实长度超过M，会报错
- 不同点：
	- char的M是定义该字段的固定长度，varchar的M是用于限定允许的最大长度
	- char实际占用长度为M，varchar的实际占用长度为数据真实占用长度+1/2(当varchar字符超过255，需要两个字节保存长度，否则只需要一个)
	- char：M的最大值是255，varchar的最大长度是65532/65533，
> M的单位是字符，而不是字节，但最大长度的单位是字节

#### text系列属性
tinytext(最大2^8+1)、text(最大2^16+1，与varchar一样、常用)、mediumtext(最大2^24+1)、longtext(最大2^32+1)
text系列属性用法几乎和varchar一样，但有以下区别
- text的长度可以完全存放数据，既无需额外字节保存长度，又不受记录最大长度的限制
- 在定义的时候无需指定长度M值，由系统计算

#### enum（枚举，单选），set（集合，不定项选择，不选到全选的各种组合）
- 在定义的时候，在enum/set后面的()中，列出允许的值列表，用,分隔
- 看似是字符串类型，实际保存的是整型（一个或者两个字节），所以插入的时候，可以直接插入字符串形式的数字
- enum内部保存的是当前选择了第几个项（2个字节，最多65535种选项），而set是做了位运算之后保存的（最多8个字节，选择是按存储的，用0/1表示是否选中，多少项就是多少位，所以最多是64项）
- set在插入的时候，要使用字符串，多个项之间用,分隔

> 字符类型总结说明：上面字符类型的最大长度并不是实际占用空间的大小，实际大小是通过传入的M值控制的

### 开发中常用的列类型
tinyint、int、float、decimal、char、varchar、text，虽然enum和set也很好用，但是是站在数据库层面上的，实际开发中并不常有

### 列类型如何选择
- 尽量精确，占用空间小
- 考虑应用语言，例如常常将日期保存为整型（unix时间戳）便于计算
- 考虑兼容性
-separator-