4.9 MySQL的基本使用

image0

数据库是每个后端程序员都必须扎实掌握的一项基本技能，而做为最主流的关系型数据库 MySQL，上手也极为容易，这里是我几年前自学 MySQL 时做下的笔记，现整理出来，一共两篇，这是第一篇。

在这篇文章里，我就介绍了一些 MySQL 的基本操作（增删改查）及表结构的解析。

一、库操作

1.1 新增数据库

【语法】：create database 数据库名字 [库选项];

一、数据库名字
1. 要避免使用MySQL关键字和保留字，若非要用，必须用反引号括起来，比如`database`
2. 要避免使用中文，大部分情况下直接设为中文会出错（字符集错误），若非要用，得先执行 set names gbk，告诉服务器你当前使用哪种字符集。

二、库选项（可选）
字符集：charset/character set 具体字符集(数据存储的编码格式): 常用字符集: GBK和UTF8
校对集：collate 具体校对集(数据比较的规则)，一般只需指定字符集，校对集会自动生成对应的。

库选项是可选的，默认值是utf-8
其值可以在对应数据库文件夹下的 db.opt 文件中看到。如下：第一行字符集，第二行校对集

default-character-set=utf8
default-collation=utf8_general_ci

也就是说，下面这两种写法等价：
create database mydatabase;
create database mydatabase charset utf8 collate utf8_general_ci;

1.2 查看数据库

# 查询全部
show databases;

# 查询部分（模糊查询）
show databases like '匹配规则';

%：匹配多个字符
_：匹配单个字符（也因此，如要匹配_本身，需用\进行转义）

1.3 更新数据库

数据库的名字不可更改
更改仅限库选项：字符集和校对集

【语法】：alter database 数据库名字 库选项;

举例：
alter database mydatabase charset gbk collate gbk_chinese_ci;

1.4 删除数据库

drop database 数据库名字;

删除数据库是不可逆的，要谨之又慎。

1.5 选定数据库

use 数据库名字;

二、表操作

2.1 新增数据表

【语法】：
Create table [if not exists] 表名/数据库名.表名(
字段名字 数据类型,
字段名字 数据类型   -- 最后一行不需要逗号
)[表选项];

【说明】
If not exists: 如果表名不存在,那么就创建,否则不执行创建代码。

表选项:
    字符集: charset/character set 具体字符集;   -- 保证表中数据存储的字符集
    校对集: collate 具体校对集;
    存储引擎: engine 具体的存储引擎(innodb和myisam)

当我们创建表后，会生成.frm和.idb后缀文件。.frm表结构文件，.idb是数据文件。

2.2 查看数据表

# 查看全部
show tables;

# 查看部分（模糊查询）
show like '匹配规则';

%：匹配多个字符
_：匹配单个字符（也因此，如要匹配_本身，需用\进行转义）

# 查看创建语句
show create table 表名;

# 查看表结构：字段信息
desc/describe 表名;
show columns from 表名;

2.3 修改数据表

可以修改表名和表选项，和字段

【修改表名】
rename table 老表名 to 新表名;

【修改表选项】
Alter table 表名 表选项 [=] 值;

2.4 修改字段

【新增】
Alter table 表名 add [column] 字段名 数据类型 [列属性] [位置];
位置:
    First: 第一个位置
    After: 在哪个字段之后: after 字段名; 默认的是在最后一个字段之后

【修改】
通常修改数据类型和属性、位置
Alter table 表名 modify 字段名 数据类型 [属性] [位置];

【重命名】
这个其实也可以更改数据类型和属性和位置
Alter table 表名 change 旧字段名 新字段名 数据类型 [属性] [位置];

【删除】
alter table 表名 drop 字段名;

2.5 删除数据表

【语法】
Drop table 表名1,表名2...;  -- 可以一次性删除多张表

三、数据操作

3.1 新增数据

【语法】
1. 按顺序插入（插入所有字段信息）
Insert into 表名 values(值列表)[,(值列表)]; -- 可以一次性插入多条记录

2. 按字段插入（插入部分字段）
Insert into 表名 (字段列表) values (值列表)[,(值列表)];

3.2 查看数据

【语法】
Select */字段列表 from 表名 [where条件];

3.3 更新数据

【语法】
Update 表名 set 字段 = 值 [where条件]; -- 建议都有where: 要不是更新全部

3.4 删除数据

【语法】
Delete from 表名 [where条件];

四、数据类型

数据类型: 对数据进行统一的分类, 从系统的角度出发为了能够使用统一的方式进行管理: 能更好的利用有限的空间，除此之外也能在一定程度上防止数据插入错误。

SQL中将数据类型分成了三大类: 数值类型, 字符串类型和时间日期类型

image1

4.1 数值型

4.1.1 整数型

整数型有符号之分（正负）

image2

创建表或新增字段的时候，如未指定，默认是有符号的。

那么如何指定呢？
只要在建表或新增字段的时候，指定unsigned
image3
显示宽度
显示宽度，最终显示的位数。
比如123，是三位，-123，就是四位了。
零填充+显示宽度的意义: 保证数据格式
不足显示宽度的话，需要在前面增加前导0来满足宽度（需要设定zerofill） image4

4.1.2 小数型

小数型: 带有小数点或者范围超出整型的数值类型.

SQL中:，将小数型细分成两种: 浮点型和定点型
浮点型: 小数点浮动, 精度有限,而且会丢失精度
定点型: 小数点固定, 精度固定, 不会丢失精度

浮点型

浮点型：超出指定范围之后, 会丢失精度(自动四舍五入)

分为两种精度
Float: 单精度, 占用4个字节存储数据, 精度范围大概为7位左右
Double: 双精度,占用8个字节存储数据, 精度范围大概为15位左右
image5

浮点的使用方式

  • float表示没有小数部分

  • float(M,D): M代表总长度,D代表小数部分长度, 整数部分长度为M-D

浮点型数据的插入: 整型部分是不能超出长度的,但是小数部分可以超出长度(系统会自动四舍五入)

定点型

定点型: 绝对的保证整数部分不会被四舍五入(不会丢失精度),小数部分有可能(理论小数部分也不会丢失精度，因为如果不超过长度，自然不丢失，超过长度就插入失败)

定点型的使用方式

  • decimal(M,D)：M最大65，D最大30

4.2 时间日期类型

Datetime: 时间日期, 格式是YYYY-mm-dd HH:ii:ss,表示的范围是从1000到9999年,有0值: 0000-00-00 00:00:00
Date: 日期,就是datetime中的date部分
Time: 时间(段), 指定的某个区间之间, -时间到+时间
Timestamp: 时间戳, 并不是时间戳,只是从1970年开始的YYYY-mm-dd HH:ii:ss格式与datetime完全一致
Year: 年份,两种形式, year(2)和year(4): 1901-2156

image6

timestamp默认是自动更新当前时间的（在记录创建或更新时更新时间） image7

插入数据

  • time：可以是负数,而且可以是很大的负数

  • year：可以使用2位数插入（>=70的为1970-1999，<=69的为2000-2069）,也可以使用4位数

image8

4.3 字符串类型

在SQL中,将字符串类型分成了6类: char,varchar,text , blob, enum和set.

4.3.1 定长字符串

定长字符串: char, 磁盘(二维表)在定义结构的时候,就已经确定了最终数据的存储长度.

Char(L): L代表length, 可以存储的长度, 单位为字符, 最大长度值可以为255.
Char(4): 在UTF8 环境下,需要4 * 3 = 12个字节

4.3.2 变长字符串

变长字符串: varchar, 在分配空间的时候, 按照最大的空间分配, 但是实际上最终用了多少,是根据具体的数据来确定.

varchar(L): L表示字符长度 理论长度是65536个字符,但是会多处1到2个字节来确定存储的实际长度: 但是实际上如果长度超过255,既不用定长也不用变长, 使用文本字符串text。 varchar(10): 的确存了10个汉字, utf8环境, 10 * 3 + 1 = 31(bytes)，存储了3个汉字: 3 * 3 + 1 = 10(bytes)

image9 从上图来看，如果长度比较固定，譬如身份证，手机号码等，还是选用定长，因为定长相对变长效率高。
如果长度是浮动的，那么就要选择变长，可以在一定长度节省空间。

4.3.3 文本字符串

如果数据量非常大, 通常说超过255个字节就会使用文本字符串

文本字符串根据存储的数据的格式进行分类: text和blob Text: 存储文字(二进制数据实际上都是存储路径)
Blob: 存储二进制数据(通常不用)

4.4 枚举字符串

枚举: enum, 事先将所有可能出现的结果都设计好, 实际上存储的数据必须是规定好的数据中的一个.

枚举的使用方式
定义: enum(可能出现的元素列表); //如enum(‘男’,’女’,’不男不女’,’保密’);
使用: 存储数据,只能存储上面定义好的数据

image10

插入数据
image11

作用之一

规范数据格式: 数据只能是规定的数据中的其中一个

作用之二

节省存储空间：枚举实际存储的是数值而不是字符串本身.

证明字段存储的数据是数值: 将数据取出来 + 0 就可以判断出原来的数据存的到底是字符串还是数值: 如果是字符串最终结果永远为0, 否则就是其他值.
image12 因为枚举实际存储的是数值,所以可以直接插入数值. image13

4.5 集合字符串

集合跟枚举很类似: 实际存储的也是数值,而不是字符串(区别是集合是多选)

集合使用方式: 定义: Set(元素列表)
使用: 可以使用元素列表中的元素(多个), 使用逗号分隔

创建集合字段 image14 插入数据：可以使用多个元素字符串组合, 也可以直接插入数值 image15 查询结果 image16

为什么会很这样？ 98是什么东西？3为什么表示（篮球，足球）？

原来在数据库内部，set是用二进制表示的。每个元素都对应一个二进制位。
image17

五、列属性

列属性: 真正约束字段的是数据类型, 但是数据类型的约束很单一. 需要有一些额外的约束, 来更加保证数据的合法性.

列属性有很多: NULL/NOT NULL, default, Primary key, unique key, auto_increment,comment

5.1 空属性

两个值: NULL(默认的)和NOT NULL(不为空)

image18 在实际应用过程中，应尽量保证数据不为空，空是没有任何意义的。并且不能参与运算。很有可能会出错。

5.2 列描述

列描述: comment, 描述, 没有实际含义: 是专门用来描述字段,会根据表创建语句保存: 用来给程序猿(数据库管理员)来进行了解的.
image19

5.3 默认值

默认值: 某一种数据会经常性的出现某个具体的值, 可以在一开始就指定好: 在需要真实数据的时候,用户可以选择性的使用默认值.

默认值关键字: default

生效：只要插入数据的时候，不给值，就会自动赋予默认值 image20 如果是全字段插入数据，那我们没法跳过，且又不知道默认值是什么？就可以使用default image21

5.4 主键

主键对应的字段中的数据不允许重复: 一旦重复,数据操作失败(增和改)
#### 5.4.1 增加主键 SQL操作中有多种方式可以给表增加主键: 大体分为三种. 方案一

在创建表的时候,直接在字段之后,跟primary key关键字(主键本身不允许为空)

优点：简单直接

缺点：只能使用一个字段作为主键 image22

方案二 在创建表的时候, 在所有的字段之后, 使用primary key(主键字段列表)来创建主键(如果有多个字段作为主键,可以是复合主键)

image23

方案三

当表已经创建好之后, 额外追加主键: 可以通过修改表字段属性, 也可以直接追加.

Alter table 表名 add primary key(字段列表);

前提: 表中字段对应的数据本身是独立的(不重复) image24

5.4.2删除主键

alter table 表名 drop primary key;

image25

5.4.3 更新主键

无法直接更新，主键必须先删除,才能增加.

5.5 自动增长

自增长: 当对应的字段,不给值,或者说给默认值,或者给NULL的时候, 会自动的被系统触发, 系统会从当前字段中已有的最大值再进行+1操作,得到一个新的在不同的字段. ​
自增长通常是跟主键搭配.

5.5.1 自增长的特点：

  1. 任何一个字段要做自增长必须前提是本身是一个索引(key一栏有值，不管是主键还是唯一键)

  2. 自增长字段必须是数字(整型)，字符串等其他类型无法自增长

  3. 一张表最多只能有一个自增长

5.5.2 如何触发自增长

image26

如何确定下一次是什么自增长呢? 可以通过查看表创建语句看到.
image27

5.5.3 修改自增长

由于一张表只能有一个自增长字段，所以要改变自增长字段，需先删除再增加。

修改下次自增长的值。必须大于当前自增长数字的最大值，小于不生效。
image28

修改起始值和步长 查看自增长对应的变量:

$ show variables like ‘auto_increment%’;

# 修改起始值和步长
$ set auto_increment_increment = 5
$ set auto_increment_offset = 10

image29

5.5.4 删除自增长

自增长是字段的一个属性：参见文章。可以通过重新覆盖字段新属性来实现（注意，要是本身有主键，不用再覆盖。因为主键是必须要drop才能新增的，就算是自身的主键也需要遵循）

alter table 表名 modify 字段 字段类型;
# 不写自增长属性就行

image30

5.6 唯一键

一张表往往有很多字段需要具有唯一性,数据不能重复: 但是一张表中只能有一个主键: 唯一键(unique key)就可以解决表中有多个字段需要唯一性约束的问题.

唯一键的本质与主键差不多: 唯一键默认的允许自动为空,而且可以多个为空(空字段不参与唯一性比较)

唯一键和主键的区别：　 主键：一张表中，只能有一个 唯一键：一张表中，可以有多个

5.6.1 新增/更新唯一键

新增的方法和主键是完全一致的。也有三种方案。可参见主键。
由于唯一键可以有多个，所以可以直接新增，无需删除再新增。

5.6.2 删除唯一键

也由于唯一键有多个，所以和主键的删除方法有所不同

Alter table 表名 drop unique key;      -- 错误: 唯一键有多个
Alter table 表名 drop index 索引名字;  -- 唯一键默认的使用字段名作为索引名字

5.7 外键

外键: foreign key, 外面的键(键不在自己表中): 如果一张表中有一个字段(非主键)指向另外一张表的主键,那么将该字段称之为外键.

5.7.1 增加外键

外键可以在创建表的时候或者创建表之后增加(但是要考虑数据的问题). 一张表可以有多个外键.

创建表的时候增加外键: 在所有的表字段之后,使用
foreign key(外键字段) references 外部表(主键字段)

image31

在新增表之后增加外键: 修改表结构
Alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段);

image32

5.7.2 更新/删除外键

外键不可修改: 只能先删除后新增.

删除外键语法
Alter table 表名 drop foreign key 外键名; -- 一张表中可以有多个外键,但是名字不能相同

5.7.3 外键的作用

外键的作用，可以分为两个：

  • 对子表约束: 子表数据进行写操作(增和改)的时候, 如果对应的外键字段在父表找不到对应的匹配: 那么操作会失败.(约束子表数据操作)

  • 对父表约束：父表的主键如果已经被子表引用，那么父表对应的记录就不允许删和改。要实现删和改，必须先将子表删或改。使得父子表字段失去联系。

5.7.4 外键的条件

满足以下条件，外键才能使用/生效
1. 外键要存在: 首先必须保证表的存储引擎是innodb(默认的存储引擎): 如果不是innodb存储引擎,那么外键可以创建成功,但是没有约束效果. 2. 外键字段的字段类型(列类型)必须与父表的主键类型完全一致. 3. 一张表中的外键名字不能重复. 4. 增加外键的字段(数据已经存在),必须保证数据与父表主键要求对应.

5.7.5 外键的约束

外键的约束，说的其实就是外键的作用。

外键的约束有三种模式：
>District: 严格模式(默认的), 父表不能删除或者更新一个已经被子表数据引用的记录 >Cascade: 级联模式: 父表的操作, 对应子表关联的数据也跟着被删除 >Set null: 置空模式: 父表的操作之后,子表对应的数据(外键字段)被置空

前面讲的外键的作用是默认的采用严格模式。

通常的一个合理的做法(约束模式): 删除的时候子表置空, 更新的时候子表级联操作 指定模式的语法

# 在建表的时候指定
Foreign key(外键字段) references 父表(主键字段) on delete set null on update cascade;

# 在修改建表后指定，使用新增外键（必须先删除外键，再新增）
alter table 表名 drop foreign key 外键名;
alter table 表名 add foreign key 外键名 references 父表(主键字段) on delete set null on update cascade;

image33

来实例操作一下，级联模式和置空模式是怎样的。 更新操作: 级联更新 image34 删除操作: 置空 image35

5.8 索引

何为索引：系统根据某种算法，将已存在的数据，单独建一个文件，使得能够快速匹配数据和查询。

索引的意义

  1. 提升查询数据的效率

  2. 约束数据的有效性(唯一性等)

索引文件，很大可能比数据文件还大，比较浪费磁盘空间。

什么情况下使用索引

  1. 一个字段经常查询，为了提高查询效率。设为索引

  2. 一个字段需要做唯一性约束。设为索引

MySQL中索引类型

  1. 主键索引: primary key

  2. 唯一索引: unique key

  3. 全文索引: fulltext index

  4. 普通索引: index

全文索引: 针对文章内部的关键字进行索引

全文索引最大的问题: 在于如何确定关键字
英文很容易: 英文单词与单词之间有空格
中文很难: 没有空格, 而且中文可以各种随意组合(分词: sphinx)

六、中文乱码问题

乱码问题的根本原因是字符集冲突。

问题剖析：

我们在windows的终端，采用的是ANSI编码，即GBK编码。服务器接收来自客户端的编码是UTF8，矛盾出现

SO，如果是提交的是英文的话，不会有冲突。因为都是一个字节。 但是，如果有中文的话，GBK的中文是一个汉字两个字节，而UTF8是一个汉字三个字节，如果传过去两个汉字（服务器解析：一汉字+一字节，不够会出错），如果刚好字节数够，那就会出现乱码了。

这里要介绍一个命令。 查看服务器的相关字符集。

show variables like 'character_set%';

image36

图上这个字符集编码，就不会出错，我们可以正常的插入中文数据。 如果发现character_set_client和character_set_results是utf8，那很有可能会出错。

解决办法

set names gbk;

或者（二者等价）

set character_set_client = gbk;
set character_set_results = gbk;

image37