4.9 MySQL的基本使用


数据库是每个后端程序员都必须扎实掌握的一项基本技能,而做为最主流的关系型数据库 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中将数据类型分成了三大类: 数值类型, 字符串类型时间日期类型

image0

4.1 数值型

4.1.1 整数型

整数型有符号之分(正负)

image1

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

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

4.1.2 小数型

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

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

浮点型

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

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

浮点的使用方式

  • 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

image5

timestamp默认是自动更新当前时间的(在记录创建或更新时更新时间) image6

插入数据

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

  • year:可以使用2位数插入(>=70的为1970-1999,<=69的为2000-2069),也可以使用4位数

image7

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)

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

4.3.3 文本字符串

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

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

4.4 枚举字符串

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

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

image9

插入数据
image10

作用之一

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

作用之二

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

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

4.5 集合字符串

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

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

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

为什么会很这样? 98是什么东西?3为什么表示(篮球,足球)?

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

五、列属性

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

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

5.1 空属性

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

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

5.2 列描述

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

5.3 默认值

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

默认值关键字: default

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

5.4 主键

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

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

优点:简单直接

缺点:只能使用一个字段作为主键 image21

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

image22

方案三

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

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

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

5.4.2删除主键

alter table 表名 drop primary key;

image24

5.4.3 更新主键

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

5.5 自动增长

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

5.5.1 自增长的特点:

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

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

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

5.5.2 如何触发自增长

image25

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

5.5.3 修改自增长

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

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

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

$ show variables like ‘auto_increment%’;

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

image28

5.5.4 删除自增长

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

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

image29

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 外部表(主键字段)

image30

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

image31

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;

image32

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

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%';

image35

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

解决办法

set names gbk;

或者(二者等价)

set character_set_client = gbk;
set character_set_results = gbk;

关注公众号,获取最新干货!