MySQL常用命令

最新推荐文章于 2023-08-11 21:13:22 发布

cjm_success

最新推荐文章于 2023-08-11 21:13:22 发布

阅读量1.6k

点赞数 1

文章标签： mysql sql 数据库

原文链接：https://www.mysql.com/

版权

MySQL常用命令

文章目录

MySQL常用命令

1. 数据库基础管理

0.0 进入MySQL

# 在linux终端输入:
mysql -u root -p
# 然后输入密码

1.1 查看已有的数据库

# 显示已有的数据库
show databases;

MySQL命令终止符为分号 ;

1.2 创建新数据库

# 创建数据库
create database 库名 [character set utf8];	# []中括号表示选填内容

# 例: 创建一个test001数据库和一个Test_002数据库, 字符编码为utf-8
create database test001 character set utf8;
create database Test_002 charset=utf8;

数据库命名注意:

可以使用数字, 字母, 下划线, 但不能使用纯数字
库名区分大小写
不可使用特殊字符和MySQL的语法关键字

1.3 切换/使用某数据库

# 使用/切换至某数据库
use 库名;

# 例: 切换/使用名为test001数据库
use test001;

1.4 查看现在所在的数据库

# 查看现在所在的数据库
select database();

# 查看数据库信息:
status;

1.5 删除数据库

# 删除数据库
drop database 库名;

# 例: 删除名为Test_002的数据库
drop databases Test_002;

2. MySQL基本数据类型

2.1 数字类型

类型	名称	大小(Byte)	取值范围(有符号)	取值范围(无符号)	用途
整数	TINYINT	1	-128 ~ 127	0 ~ 255	小整数
整数	SMALLINT	2	-32,768 ~ 32,767	0 ~ 65,535	小整数
整数	MEDIUMINT	3	-8,388,608 ~ 8,388,607	0 ~ 16,777,215	中整数
整数	INT	4	-2,147,483,648 ~ 2,147,483,647	0 ~ 4,294,967,295	大整数
整数	BIGINT	8	很大	很大	超大整数
浮点数	FLOAT	4	很长	很长	单精度浮点数
浮点数	DOUBLE	8	很长	很长	双精度浮点数
浮点数	DECIMAL	不确定	依赖于对M和D的取值	依赖于对M和D的取值	可以自定义默认 D 值为 0、M 值为 10
比特值	BIT	1	0 or 1	0 or 1	真假布尔判断

关于DECIMAL:

对于准确性要求比较高的东西，比如money，用decimal类型减少存储误差。声明语法是DECIMAL(M,D)。M是数字的最大数字位数，D是小数点右侧数字的位数。比如 DECIMAL(6,2)最多存6位数字，小数点后占2位,取值范围-9999.99到9999.99.
浮点数建议使用DECIMAL

关于zerofill:

zerofill 类似一个显示属性, 它会将为满足位数的数字前面加0补充完位数
例: 用int(3) zerofill创建一个字段后, 在该字段内输入10 (二位数, 不满足三位), 字段实际会显示010 (前面加0, 补充完整)

2.2 字符串类型

a) 普通字符串

名称	大小(字节)	用途
CHAR	0 ~ 255	定长字符串
VARCHAR	0 ~ 65,536	不定长字符串

char：定长，即指定存储字节数后，无论实际存储了多少字节数据，最终都占指定的字节大小。默认只能存1字节数据。存取效率高。一般用于固定长度的表单提交数据存储；例如：身份证号，手机号，电话，密码等

varchar：不定长，效率偏低，但是节省空间，实际占用空间根据实际存储数据大小而定, 必须要指定存储大小 varchar(50)。5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放20个，最大大小是 65532字节

在UTF-8编码下, 每个汉字占3字节

b) 文本内容

名称	大小(字节)	用途
TINYTEXT	0 ~ 255	短文本
TEXT	0 ~ 65,536	文本
MEDIUMTEXT	0 ~ 16,777,215	中文本
LONGTEXT	0 ~ 4,294,967,295	大文本

c) 二进制数据

名称	大小(字节)	用途
TINYBLOB	0 ~ 255	不超过255字符的二进制字符串
BLOB	0 ~ 65,536	二进制字符串
MEDIUMBLOB	0 ~ 16,777,215	大长度的二进制数据
LONGBLOB	0 ~ 4,294,967,295	极大长度的二进制数据

d) 选择型数据

名称	选项数量	用途
ENUM	0 ~ 65,535	单选
SET	0 ~ 64	多选

# 创建ENUM类型字段时的用法
字段名 ENUM('值1','值2','值3'...'值n')

# 创建SET类型字段时的用法
字段名 SET('值1','值2','值3'...,'值n')

2.3 日期类型

名称	大小(字节)	格式	范围	用途
DATE	3	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	日期值
TIME	3	HH:MM:SS	-838:59:59 ~ 838:59:59	时间长度
YEAR	1	YYYY	1901 ~ 2155	年份值
DATETIME	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	日期和时间
TIMESTAMP	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 ~ 2038	时间戳

datetime ：以系统时间存储

timestamp ：以标准时间存储但是查看时转换为系统时区，所以表现形式和datetime相同

# 例: 在test表中插入新字段register_time,类型为DATETIME, 默认值为服务器当前时间
alter table test add register_time datetime default now();
# 插入字段的方法看4.5.1; 所有register_time的数据会以插入字段的时间赋值

a) 日期时间函数

# 返回服务器当前日期时间,格式对应datetime类型
now() 
# 返回当前日期，格式对应date类型
curdate()
# 返回当前时间，格式对应time类型
curtime()

b) 日期时间的比较操作

时间类型数据可以进行比较和排序等操作，因此在写时间字符串时尽量按照标准格式书写

# 例: 从test表中取出所有register_time < now()的数据
select * from test where register_time < now();

3. 表的基本操作

3.1 查看数据库内的表

# 查看本数据库内的表
show tables；

# 查看某数据库内的表
show tables from 数据库名;

3.2 创建表

# 创建表
create table 表名(字段名 数据类型 约束, 字段名 数据类型 约束, ... 字段名 数据类型 约束);

# 例: 创建一个名为test_1的表, 建立"id"(主键), "姓名", "年龄", "性别"(单选), "分数" 字段
create table test_1 (id int primary key auto_increment,name varchar(32) not null,age tinyint unsigned not null, sex enum('w','m'),score float unsigned default 0.0);

# 例: 创建一个名为test_2的表, 建立"id"(主键), "姓名", "兴趣"(多选), "等级", "价格"(xxxx.xx), "备注" 字段; 设置字符编码为utf-8
create table test_2 (id int auto_increment, name varchar(32) not null, hobby set('sing','dance','draw'), level char not null, price unsigned decimal(6,2), remark text, PRIMARY KEY (id))CHARSET=utf8;

字段约束:

unsigned:

使数字无符号 (不能为负数)

auto_increment (自增):

默认开始值是 1, 每条新记录递增 1
要让 AUTO_INCREMENT 序列以其他的值起始，使用下列 SQL 语法：
# 例: 创建一个名为test_1的表, 建立"id", "姓名", "年龄", "性别", "分数" 字段; 其中"id"字段从100开始自增长
create table test_1 (id int primary key auto_increment, name varchar(32) not null, age tinyint unsigned not null, sex enum('w','m'), score float default 0.0)auto_increment=100;
NOT NULL (非空约束):

指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

DEFAULT (默认值):

用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。
语法: <字段名> <数据类型> DEFAULT <默认值>

PRIMARY KEY 主键

每个表只能定义一个主键字段。
主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。这是唯一性原则。
一个字段名只能在联合主键字段表中出现一次。
联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。

3.3 查看表信息 & 数据

# 查看表结构
desc 表名;

# 查看数据表创建信息
show create table 表名;
# 例: 输入 show create table test_1; 会显示出刚刚test_1表的建表信息:
test_1 | CREATE TABLE `test_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  `sex` enum('w','m') DEFAULT NULL,
  `score` float unsigned DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3.4 删除表

# 删除表,方法1:
drop table 表名;

# 例: 删除test_2表;
drop table test_2;

# 删除表(如果存在则删除,不存在也不会报错),方法2:
DROP TABLE IF EXISTS 数据库名.表名;

# 例: 删除test库中的lala表
drop table if exists test.lala;

3.5 表重命名

# 表重命名语法
alter table 表名 rename 新表名;

# 例: 将test_1表重命名为test
alter table test_1 rename test;

3.6 表的复制

1、表能根据实际需求复制数据
2、复制表时不会把KEY属性复制过来

# 复制表的语法
create table 表名 select 查询命令;

# 例: 将test表中所有20岁以上的人的信息复制出来
create table copy_test select * from test where age>20;

4. 表数据的基本操作

4.1 插入/添加数据

# 方法1:
insert into 表名 values(值1...),(值2...),...;

# 例: 给test表中插入多组数据(1, 'Kevin', 18, 'm', 99.0); (2, 'Lily', 16, 'w', 98.5)....
insert into test values (1, 'Kevin', 18, 'm', 99.0), (2, 'Lily', 16, 'w', 98.5);
insert into test values ('Bon', 21, 'm', 89.0), ('Dali', 23, 'w', 90.1);

注意, 用这种位置参数传参的方式, 必须要指定所有不能为空的字段

例: 不能不给出id字段的数据, 即使它是自动增长的

# 方法2:
insert into 表名(字段1,...) values(值1),...;

# 例: 给test表中插入数据('Nana', 18, 'w', 97.0)
insert into test(name, age, sex, score) values('Nana', 18, 'w', 97.0);
# 例: 给test表中插入多组数据('Tony', 'm', 98.5).....
insert into test(name, age, score) values('Tony', 20, 98.5);

用这种方法, 可以跳过那些可以为空的字段, 以及自增长的字段

4.2 查询表内数据

4.2.1 查看数据

# 查看表内所有数据
select * from 表名;
# 例: 查看test表中的所有数据:
select * from test;

# 格式化输出表内所有数据
select * from 表名\G;

# 查看表内某特定字段的数据
select 字段名, 字段名... from 表名;

# 例: 在test表中取出所有的name数据
select name from test;
# 例: 在test表中选取所有name和age的数据
select name, age from test;

4.2.2 条件查询表数据

# 按条件输出表内数据
select * from 表名 where 条件;

# 例: 查看test表中所有name='Tony'的数据
select * from test where name='Tony';

where子句

where子句在SQL语句中扮演了重要角色，主要通过一定的运算条件进行数据的筛选，在查询，删除，修改中都有使用。

a) 比较运算符

运算符	作用
=	等于
<=>	安全的等于 (不返回NULL值)
<> 或者 !=	不等于
<=	小于等于
>=	大于等于
>	大于
IS NULL 或者 ISNULL	判断一个值是否为空
IS NOT NULL	判断一个值是否不为空
BETWEEN 值1 AND 值2	判断一个值是否落在两个值之间
NOT BETWEEN 值1 AND 值2	判断一个值是否不在在两个值之间
IN (值1, 值2 …)	判断一个值是否在一个集合中
NOT IN (值1, 值2 …)	判断一个值是否不在一个集合中
LIKE	模糊匹配
REGEXP 或者 RLIKE	正则表达式匹配
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值

# 例:
select * from test where age > 18;
select * from test where score between 90 and 98;
select * from test where age in (18,19);
比较运算符的返回值为1或0, 相等的话返回 1，不相等的话返回 0
等于运算符 " = " :

NULL 不能用于 = 比较; 若有一个或两个操作数为 NULL，则比较运算的结果为 NULL
若一个操作数为字符串，另一个操作数为数字，则 MySQL 可以自动将字符串转换为数字

安全等于运算符 " <=>" :

与等于运算符相似, 但是用来判断 NULL 值
当两个操作数均为 NULL 时，其返回值为 1；当只有一个操作数为 NULL 时，其返回值为 0
LEAST 与 GREAST 的用法:
select greatest(11,2,4,6); 			# 返回值为11
select greatest(11,2,4,6,null);		# 返回值为null
select greatest(11,2,4,6,'z'); 		# 返回值为11

select least(11,2,4,6); 			# 返回值为2
select least(11,2,4,6,null);		# 返回值为null
select least(11,2,4,6,'z'); 		# 返回值为0
模糊匹配 " LIKE " :
LIKE 通常与一些通配符一同使用，类似于一个元字符的搜索;
% : 匹配0个或任意多个字符; 例: 在test表中查找所有分数以98开头的数据
select * from test where score like '98%';
_ : 匹配任意一个字符; 例: 在test_1表中查找所有分数含两位整数和一位小数的数据
select * from test where score like '__._';
escape ：转义字符，可匹配%和_
正则表达式 “RLIKE” / "REGEXP’ 的常用通配符:

. : 匹配任意单个字符
* ：匹配0个或多个前一个得到的字符
[] : 匹配任意一个[]内的字符，[ab]*可匹配空串、a、b、或者由任意个a和b组成的字符串
^ : 匹配开头，如^s匹配以s或者S开头的字符串
$ : 匹配结尾，如s$匹配以s结尾的字符串
{n} : 匹配前一个字符反复n次
关于LIKE和RLIKE的注意点:

在MySQL中，like、rlike和regexp都不区分大小写，如果需要区分，可以在WHERE后添加关键字段binary
like是完全匹配。rlike和regexp是不完全匹配

b) 算术运算符

运算符	作用
+	加法
-	减法
*	乘法
/ 或 DIV	除法
% 或 MOD	取余

例: 查找test_1表中所有年龄为偶数的数据, 并格式化输出:
select * from test where age % 2 = 0\G;

c) 逻辑运算符

运算符	作用
NOT 或者 !	逻辑非
AND 或者 &&	逻辑与
OR 或者 \|\|	逻辑或
XOR	逻辑异或

# 例:
select * from test where sex='m' and age>19;
select * from test where sex='m' or age>19;

d) 位运算符

位运算符	说明
\|	按位或
&	按位与
^	按位异或
<<	按位左移
>>	按位右移
~	按位取反，反转所有比特

位运算符是在二进制数上进行计算的运算符，先将操作数变成二进制数，进行位运算，然后再将计算结果从二进制数变回十进制数

…不常用…

e) 运算符的优先级

优先级由低到高排列	运算符
1 最低优先级	=(赋值运算）、:=
2	II、OR
3	XOR
4	&&、AND
5	NOT
6	BETWEEN、CASE、WHEN、THEN、ELSE
7	=(比较运算）、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN
8	\|
9	&
10	<<、>>
11	-(减号）、+
12	*、/、%
13	^
14	-(负号）、〜（位反转）
15 最高优先级	!

4.3 更新表中数据

# 更新表数据
update 表名 set 字段1=值1,字段2=值2,... where 条件;
# 注意:update语句后如果不加where条件,所有记录全部更新

# 例: 将test表中name="kevin"的数据, score字段改为100
update test set score=100 where name='Kevin';

# 例: 将test表中所有的age减1
update test set age=age-1;

4.4 删除表中数据

# 删除表数据语法
delete from 表名 where 条件;
# 如果不加where条件, 则会把表中的数据全部清空

# 删除test表中, 所有年龄大于20的数据
delete from test where age > 20;

4.5 表字段的操作

alter table 表名 需要执行的动作;

4.5.1 添加字段

# 添加字段的语法
alter table 表名 add 字段名 数据类型;				# 在表尾添加字段
alter table 表名 add 字段名 数据类型 first;			# 在表头添加字段
alter table 表名 add 字段名 数据类型 after 字段名;	  # 在特定字段后面添加字段

# 例: 在test表尾添加class字段, 默认值为1
alter table test add class tinyint default 1;
# 例: 在test表头添加height字段
alter table test add height tinyint first;
# 例: 在test表中的name字段后面添加一个tel字段
alter table test add tel char(11) after name;

4.5.2 删除字段

# 删除字段的语法
alter table 表名 drop 字段名;

# 删除test表中的height字段
alter table test drop height;

4.5.3 修改字段数据类型

# 修改数据类型语法
alter table 表名 modify 字段名 新数据类型;

# 例: 将test表中的tel字段数据类型改为int(11), 设置默认值为0
alter table test modify tel int(11) default 0;

注意, 当你在修改字段属性/类型时, 如果需要将字段属性设置为not null, 那么原字段内容必须不存在NULL的数据, 否则报错

4.5.4 修改字段名

# 修改字段名的语法
alter table 表名 change 旧字段名 新字段名 新数据类型;

# 例: 将test表中的sex字段修改为gender,数据类型不变
alter table test change sex gender enum('w','m');

5. 高级查询语句

5.1 模糊查询

LIKE用于在where子句中进行模糊查询

详情可看4.2.2.a)

5.2 正则查询

mysql中对正则表达式的支持有限，只支持部分正则元字符:

详情可看4.2.2.a)

5.3 AS重命名

用于给字段或者表重命名; 常常在子查询或复杂查询中使用

# 例: 从test表中选取name字段和age字段数据, 并重命名为'姓名'和'年龄'
select name as 姓名, age as 年龄 from test;

# 例: 从test表中选取所有年龄大于19的信息, 并将表重命名为'班级'
select * from test as 班级 where age > 18;

5.4 数据排序

用ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果

ASC表示升序，DESC表示降序 (ASC为默认排序方式)

#例: 从test表中选取所有name和age字段数据, 并按照age从小到大排序
select name, age from test order by age;

# 例: 从test表中选取所有的name和score字段数据, 并按照score从大到小排序
select name, score from test order by score desc;

复合排序, order by可以对多个字段进行排序, 即当第一排序项相同时按照第二排序项排序

# 例: 从test表中选取所有的name, age, 和score字段数据, 并按照age从小到大排序, age相同时按score从大到小排序
select name, age, score from test order by age, score desc;

5.5 数据数量限制

LIMIT 子句用于限制由 SELECT 语句返回的数据数量或者 UPDATE,DELETE语句的操作数量

# 语法
SELECT 字段... FROM 表名 WHERE 条件语句 LIMIT 数量

# 例: 从test表中选取成绩最好的数据
select * from test order by score desc limit 1;
# 例: 从test表中选取年龄最小的两个人的name
select name from test order by age limit 2;

5.6 联合查询

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。

UNION后跟 DISTINCT表示删除结果集中重复的数据。
如果UNION后跟ALL则返回所有结果集, 包含重复数据。
注意! column的数量必须一样, 否则报错

# 语法
SELECT 字段... FROM 表名 WHERE 条件语句 UNION ALL或DISTINCT SELECT 字段... FROM 表名 WHERE 条件语句;

# 例: 从test表中选择所有性别为M的数据, 以及从test表中选取所有score>98的数据, 数据不重复
select * from test where gender='m' union distinct select * from test where score>98;
# 例: 从test表中选择所有age>17的数据, 以及从test表中选取所有score<99的数据, 数据可以重复
select * from test where age>17 union all select * from test where score<99;

5.7 子查询

当一个select语句中包含另一个select 查询语句，则称之为有子查询的语句

子查询出现的位置：

from 之后，此时子查询的内容作为一个新的表内容，再进行外层select查询

需要将子查询结果集重命名一下，方便where子句中的引用操作

where子句中，此时select查询到的内容作为外层查询的条件值

子句结果作为一个值使用时，返回的结果需要一个明确值，不能是多行或者多列

如果子句结果作为一个集合使用，即where子句中是in操作，则结果可以是一个字段的多个记录

# 子查询在from之后的语法格式
select 字段 from (select 字段 from 表名 where 条件) as 重命名 where 条件;

# 例: 在test表中找到, 所有gender='m'的数据中成绩最好的
select * from (select * from test where gender='m') as t order by t.score desc limit 1;
# 例: 在test表中找出成绩在95分以上的所有女生的名字和成绩
select name, score from (select * from test where gender='w') as t where t.score>95;

# 子查询在where子句中的语法格式
select 字段名 from 表名 where 条件 =或in (select 字段 from 表名 where 条件);

# 明确值的案例: 在test表中找到所有和Tony年龄一样的数据 (子查询语句内的数据只能有一个)
select * from test where age = (select age from test where name='Tony');
# 数据集的案例: 用子查询语句, 在test表中找到所有成绩小于99的数据 (子查询语句内的数据可以有多个,也可以是一个)
select * from test where score in (select score from test where score<99);

6. 聚合函数

聚合操作指的是在数据查找基础上对数据的进一步整理筛选行为，实际上聚合操作也属于数据的查询筛选范围。

常常, 我们需要的数据(总和，平均数，最大最小值等)并不在表中。但是, 我们可以通过执行聚合函数来获取它

函数	功能
avg(字段名)	该字段的平均值
max(字段名)	该字段的最大值
min(字段名)	该字段的最小值
sum(字段名)	该字段所有记录的和
count(字段名)	统计该字段记录的个数

# 例: 用聚合函数,在test表中找到最好的score是多少
select max(score) from test;
# 例: 用聚合函数,找到在test表中有多少数据
select count(id) from test;
# 例: 用聚合函数, 找到在test表中的平均成绩
select avg(score) from test;

6.1 聚合分组

# 关键语句
group by

给查询的结果进行分组

# 例: 在test表中, 计算不同性别的平均年龄
select gender, avg(age) from test group by gender;

# 例: 在test表中,查询所有不同性别的人数
select gender,count(*) from test group by gender;

# 例: 在test表中, 查询每个班的女生数量
select class,count(id) as number from test where gender='W' group by class;

6.2 聚合筛选

# 关键语句
having

对分组聚合后的结果进行进一步筛选

注意

having语句必须与group by联合使用。
having语句的存在弥补了where关键字不能与聚合函数联合使用的不足 (where只能操作表中实际存在的字段)

# 例: 从test表中, 找出平均分大于95分的性别
select gender, avg(score) from test group by gender having avg(score)>95;

# 例: 从test表中找到平均年龄大于15岁的班级,并按平均成绩从小到大排列
select class, avg(age), avg(score) from test group by class having avg(age)>15 order by avg(score);

6.3 去重语句

# 关键语句
distinct

不显示字段重复值

# 例: test表中有哪些班级
select distinct class from test;

# 例: 计算出有多少班级
select count(distinct class) from test;

6.4 聚合运算

查询表记录时做数学运算

运算符： + - * / %

# 例: 输出所有姓名&成绩字段信息, 并将成绩字段+1输出
select name, score+1 from test;

# 例: 更新test表class=1中所有年龄+1
update test set age = age+1 where class=1;

7. MySQL的索引

7.1 概述

定义

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。
如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。

优缺点

优点：加快数据检索速度,提高查找效率
缺点：占用数据库物理存储空间，当对表中数据更新时,索引需要动态维护,降低数据写入效率

使用场景

通常我们只在经常进行查询操作的字段上创建索引
对于数据量很少的表或者经常进行写操作而不是查询操作的表不适合创建索引

索引分类

普通 (MUL): 字段值无约束,KEY标志为 MUL
```
# 语法关键字: index
```
唯一索引 (UNI): 字段值不允许重复,但可为 NULL,KEY标志为 UNI
```
# 语法关键字:  unique key 或 unique index
```
主键索引 (PRI): 一个表中只能有一个主键字段, 主键字段不允许重复, 且不能为NULL, KEY标志为PRI. 通常设置记录编号字段id, 能唯一锁定一条记录
```
# 语法关键字:  primary key
```

KEY是什么

key 是数据库的物理结构，它包含两层意义和作用，
1. 约束（偏重于约束和规范数据库的结构完整性）约束作用, 规范数据的唯一性; 索引作用,
2. 索引（辅助查询用的）在这个key上建立了一个唯一索引
包括primary key, unique key, foreign key 等
- primary key: 见主键索引的介绍, 主键索引的语法关键字就是 > primary key
- unique key:
  - UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证
  - 每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束
- foreign key:
  - 建立外部关系约束, 以及索引 (详情看8.1)

index vs key

index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。
索引要分类的话，分为前缀索引、全文本索引等；
索引只是索引，它不会去约束索引的字段的行为（那是key要做的事情）

7.2 创建索引

在创建表时直接创建索引

# 创建索引的语法
create table 表名(字段名1 数据类型1，字段名2 数据类型2, 字段名3 数据类型3, index 索引名(字段名1), unique index 索引名(字段名2), primary key 索引名(字段名3));

# 例: 创建一个名为class的表, 包含class_id, class_num, class_name, num_of_stu四个字段, 然后给clss_id赋为主键索引, class_num赋为唯一索引, class_name赋为普通索引
create table class(class_id int(3) zerofill, class_num int(3), class_name char(4), num_of_stu int(3), index name(class_name), unique index num(class_num), primary key id(class_id));

在已有的表中创建索引

# 在已有的表中创建索引
create {unique index 或 index} 索引名 on 表名(字段名);
# 将某字段设置为主键索引
alter table 表名 add primary key(字段名);

# 例: 在test表中将name设为普通索引
create index name on test(name);

7.3 查看索引

# 方法1
desc 表名;

# 方法2
show index from 表名;

7.4 删除索引

# 删除唯一索引 或 普通索引
drop index 索引名 on 表名;

# 例: 删除class表中的class_num的索引
# 先查看索引名:
show index from class;
# 再删除索引
drop index num on class;

# 删除主键索引 
alter table 表名 drop primary key;

# 例: 删除class表中的主键索引
alter table class drop primary key;
# 将class_id设为主键索引
alter table class add primary key(class_id);

8. 外键约束和表关联

8.1 外键约束

约束 : 约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性 (常用KEY关键字来做约束)

foreign key: 建立表与表之间的某种约束的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强

主表和从表：若同一个数据库中，B表的外键与A表的主键相对应，则A表为主表，B表为从表。

如果在父表中找不到候选键,则不允许在子表上进行insert/update

在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的on update/on delete子句, InnoDB支持５种方式：RESTRICT 或 CASCADE 或 SET NULL 或 NO ACTION

cascade方式

在父表上update/delete记录时，同步update/delete掉子表的匹配记录

set null方式

在父表上update/delete记录时，将子表上匹配记录的列设为null

要注意子表的外键列不能为not null

No action方式

如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

Restrict方式

同no action, 都是立即检查外键约束

MySQL有两种常用的引擎类型：MyISAM和InnoDB。目前只有InnoDB引擎类型支持外键约束。

8.1.1 创建外键约束

# 创建外键约束的语法
CONSTRAINT 约束名 FOREIGN KEY 外键名 (外键字段名) REFERENCES 外表名 (要关联的外表字段名)
	# 可选参数1: 删除关联关系
	ON DELETE {RESTRICT 或 CASCADE 或 SET NULL 或 NO ACTION}
	# 可选参数2: 更新关联关系
	ON UPDATE {RESTRICT 或 CASCADE 或 SET NULL 或 NO ACTION}

该语法在 CREATE TABLE 和 ALTER TABLE 时都可以使用

# 在创建表时添加外键关联

# 例: 创建一个student列表, 包含stu_id,name,age,gender,class字段, class字段为外键关联class表中的class_id字段, 并设置删除关系为set null
CREATE TABLE student(
    id int PRIMARY KEY AUTO_INCREMENT, 
    name varchar(32) NOT NULL, 
    age tinyint DEFAULT 0, 
    sex enum('m','w','o') DEFAULT 'o', 
    class int(3) zerofill,
    constraint fk foreign key c_fk(class) references class(class_id) 
    ON DELETE CASCADE ON UPDATE CASCADE);

# 给已有的表添加外键关联

# 例: 给test表的class字段设置外键字段为class表的class_id
# 因为test表的class字段与class字段数据不同, 所以需要先修改test表的class字段数据类型
alter table test modify class int(3) unsigned zerofill;
# 因为test表中的class字段已经存在数据(001, 002),所以需要往class表中添加数据
insert into class values(001, 1, 'C班', 4),(002, 2, 'A班', 2);
# 设置外键和关联
alter table test add constraint c_fk foreign key(class) references class(class_id);

注意!!!

表的外键字段数据类型与指定的主表主键字段数据类型应该完全相同~

8.1.2 查看外键约束

# 如何查看某表的外键约束? 可以用show create table方法:
show create table 表名

8.1.3 删除外键约束

通过外键名称解除外键约束

# 删除外键约束的语法 (通过show create table查看外键名)
alter table 表名 drop foreign key 外键名;

# 例: 删除test表中的class字段的外键关系
# 先查看constraint的名字
show create table test;
alter table test drop foreign key c_fk;

8.2 表关联设计

当我们应对复杂的数据关系的时候，数据表的设计就显得尤为重要，认识数据之间的依赖关系是更加合理创建数据表关联性的前提。常见的数据关系如下：

8.2.1 一对一关系

一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。
举例 : 学生信息和学籍档案，一个学生对应一个档案，一个档案也只属于一个学生

# 创建student_1表, 有id,name字段
create table student_1(id int primary key auto_increment, name varchar(50) not null);

# 创建recod_1表, 有id,comment字段, 并设置st_id字段外键关联student_1表中的id字段
create table record_1(id int primary key auto_increment,
                      comment int unique,
                      constraint st_fk foreign key(id) references student(id) 
                      on delete cascade on update cascade);

# 用主键关联主键的方法, 使recode_1表中的id必然关联一个student_1表的id, 并且student_1表的id只可能有一个recode_1表中的id数据关联, 因为主键不能重复

MySQL不包含任何用于定义一对一关系的语法，但是，如果要强制限制表关系为一对一，则可以将一个主键中的外键添加到另一个主键中，通过这样做，两个表将自动具有一对一的关系。

8.2.2 一对多关系

一张表中有一条记录可以对应另外一张表中的多条记录；但是反过来，另外一张表的一条记录只能对应第一张表的一条记录，这种关系就是一对多或多对一

举例：一个班级可以有很多不同的学生, 但是一个学生只能在一个班级。

# 创建class_2表, 有cid,name字段
create table class_2(cid int primary key auto_increment, name varchar(3));

# 创建student_2表, 有sid,name,class字段, 并设置class字段外键关联class_2表的cid字段
create table student_2(sid int primary key auto_increment, 
                       name varchar(50) not null, 
                       class int,
                       constraint s_fk foreign key(sid) references class_2(cid));

使用普通的外键关联, 既是一对多的关系

8.2.3 多对多关系

一对表中（A）的一条记录能够对应另外一张表（B）中的多条记录；同时B表中的一条记录也能对应A表中的多条记录
多对多的关系, 需要创建第三表作为关联表

举例：一个老师可以带不同的学生, 一个学生也可以有多个老师.

# 创建teacher_1表, 有tid,name字段
create table teacher_1(tid int primary key auto_increment, name varchar(10));

# 创建course表, 有id, tid, sid, name字段, 其中tid关联teacher_1表, sid关联student_2表
create table course(id int primary key auto_increment, 
                    tid int, sid int, name varchar(10),
                    constraint fk1 foreign key(tid) references teacher_1(tid), 
                    constraint fk2 foreign key(sid) references student_2(sid));

多对多表, 需要一张关联表来容纳不同的关联关系

8.3 多表联合查询

如果多个表存在一定关联关系，可以多表在一起进行查询操作，其实表的关联整理与外键约束之间并没有必然联系，但是基于外键约束设计的具有关联性的表往往会更多使用关联查询查找数据

# 多表查询语法
select  字段1,字段2... from 表1,表2... where 条件;

# 例: 显示test表中的学生姓名和该学生所在的班级名称
select test.name, class.class_name from test, class where test.class = class.class_id;

8.3.1 内连接

内连接查询只会查找到符合条件的记录，其实结果和表关联查询是一样的, 官方更推荐使用内连接查询。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sNUYkH7N-1596082206661)(C:\Users\cjm_s\AppData\Roaming\Typora\typora-user-images\image-20200729200416662.png)]

# 内连接查询语法
SELECT 字段名 FROM 表1名 INNER JOIN 表2名 ON 表1名.字段名 = 表2名.字段名 where 条件;

# 用内连接的方法, 查询所有C班的学生姓名
select name, class.class_name from test inner join class on test.class = class.class_id 
where class.class_name = 'C班';

8.3.2 左连接

左表为主表，显示右表中与左表匹配的项

LEFT JOIN 语法左边的表名即为主表 (即下图的A表)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OLYiLJ7k-1596082206665)(C:\Users\cjm_s\AppData\Roaming\Typora\typora-user-images\image-20200729200431298.png)]

# 左连接查询语法
SELECT 字段名 FROM 表1名 LEFT JOIN 表2名 ON 表1名.字段名 = 表2名.字段名 where 条件;

# 在test表中插入新数据,不写入class字段信息
insert into test(name, age, score) values('TT', 20, 98.5);

# 用左连接方法, 查询学生姓名和班级名称

# 写法1:
select test.name, class.class_name from test left join class on test.class = class.class_id;
# 可见class_name字段信息为null的学生姓名

# 写法2:
select test.name, class.class_name from class left join test on test.class = class.class_id;
# 可见所有教室名信息, 但是没有新添加的学生名

8.3.3 右连接

右表为主表，显示左表中与左表匹配的项

RIGHT JOIN 右边的表名即为主表 (即下图中的B表)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aENbPc6k-1596082206667)(C:\Users\cjm_s\AppData\Roaming\Typora\typora-user-images\image-20200729200448522.png)]

# 右连接查询语法
SELECT 字段名 FROM 表1名 RIGHT JOIN 表2名 ON 表1名.字段名 = 表2名.字段名 where 条件;

# 在class表中插入新数据
insert into class(class_id, class_name) values(3, 'D班');

# 用右连接方法, 查询学生姓名和班级名称

# 写法1:
select test.name, class.class_name from test right join class on test.class = class.class_id;
# 可见D班名, 但是对应的学生名为NULL

# 写法2:
select test.name, class.class_name from class right join test on test.class = class.class_id;
# 可见所有的学生姓名, 但是没有显示新添加的D班

8.3.4 笛卡尔积

笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录

# 语法:
select 字段名 from 表1名 inner join 表2名;

9. 视图

视图是存储的查询语句, 当调用的时候产生结果集, 视图充当的是虚拟表的角色。

其实视图可以理解为一个表或多个表中导出来的表，作用和真实表一样，包含一系列带有行和列的数据

视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录

视图可以使用户操作方便，并保障数据库系统安全，如果原表改名或者删除则视图也失效

缺点
视图的性能相对较差，从数据库视图查询数据可能会很慢

9.1 创建视图

# 创建视图的语法
{CREATE 或 REPLACE} VIEW 视图名 AS 完整的SELECT语句;
# REPLACE: 如果添加原来有同名视图的情况下会覆盖掉原有视图

# 例: 创建一个视图class_info, 内有学生名和所在的班级名
create view class_info as select name, class.class_name from test inner join class on test.class = class.class_id;

# 查看视图内容:
select * from class_info;
# 视图作为独立的表存在于数据库中
show tables; 	# 可见class_info存在

9.2 视图表内数据的操作

视图的增删改查操作与一般表的操作相同，使用insert, update, delete, select即可，但是原数据表的约束条件仍然对视图产生作用

9.3 视图表的操作

9.3.1 查看现有视图

# 查看现有视图的语法
show full tables in 数据库名 where table_type like 'VIEW';

9.3.2 修改视图

参考创建视图，将create关键字改为alter

# 修改视图的语法
ALTER VIEW 视图名 AS 完整的SELECT语句;

# 例: 将class_info视图的字段内容修改为id, name, age, class_name
alter view class_info as select id, name, age, class_name from test left join class on test.class = class.class_id;

9.3.3 删除视图

# 删除视图语法
drop view if exists 视图名;

IF EXISTS 表示如果存在，这样即使没有指定视图也不会报错

10. MySQL的函数&存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合，调用存储过程和函数可以简化应用开发工作，提高数据处理的效率。

10.1 定义函数

10.1.1 创建自定义函数

# 创建自定义函数的语法

delimiter $$　　			# $$是自定义符号
create function 函数名(参数1|类型1, 参数2|类型2 ....) returns 返回类型　　	# 注意是retruns, 只能是一种类型
begin　　　　			 
return 自定义返回语句		 # 自定义函数只能由一个返回值
end  $$
delimiter ;

释义：

delimiter 自定义符号是为了在函数内些语句方便，制定除了;之外的符号作为函数书写结束标志,一般用$$或者//
形参列表：形参名类型类型为mysql支持类型
返回类型: 函数返回的数据类型,mysql支持类型即可
函数体：若干sql语句组成，如果只有一条语句也可以不写delimiter和begin,end
return: 返回指定类型返回值

# 创建无参数函数

# 例: 创建一个自定义函数, 返回test表中最好的成绩
delimiter $$
create function best_score() returns int
begin
return (select score from test order by score desc limit 1);
end $$
delimiter ;

# 调用自定义函数:
select best_score();

# 自定义函数可以作为查询语句的一个部分来调用
# 例: 返回test表中成绩最好的人的所有数据
select * from test where score=best_score();

# 创建有参数函数

# 例: 创建一个自定义函数, 输入学生id, 输出学生姓名
delimiter $$
create function find_stu(sid int(11)) returns varchar(32)
begin
return (select name from test where id=sid);
end $$
delimiter ;

# 调用自定义函数:
select find_stu(4);

10.1.2 查看自定义函数

若要修改自定义函数的内容，则需要先删除该自定义函数，然后重新创建。

# 查看自定义函数的定义过程
show create function 函数名称;

# 例: 查看find_stu函数的定义过程
show create function find_stu;

# 查看库内所有的自定义函数
select name from mysql.proc where db='数据库名' and type='function';

# 例: 查看maoyandb数据库中的所有自定义函数名
select name from mysql.proc where db='maoyandb' and type='function';

10.1.3 删除自定义函数

# 删除自定义函数
DROP FUNCTION IF EXISTS 函数名;

# 删除名为find_stu的函数
drop function if exists find_stu;

10.2 自定义存储过程

自定义存储过程和自定义函数相似，也是一组完成特定功能的SQL语句集合。把复杂或频繁调用的SQL提前写好并指定一个名称。待到要使用时，直接调用即可。

函数和存储过程区别

函数有且只有一个返回值 (只能输出一个数据)，而存储过程不能有返回值 (可以输出多个数据, 但是不能计算出结果)
函数只能有输入参数，而存储过程可以有in, out, inout多个类型参数
存储过程中的语句功能更丰富，实现更复杂的业务逻辑，可以理解为一个按照预定步骤调用的执行过程，而函数中不能展示查询结果集语句，只是完成查询的工作后返回一个结果，功能针对性比较强。
存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。
用select调用自定义函数, 用call调用自定义存储过程

10.2.1 创建自定义存储过程

# 创建自定义存储过程的语法
delimiter $$　
create procedure 存储过程名(形参列表)
begin
存储过程　　　　		# 存储过程语句集,set @a 定义变量
end  $$
delimiter ;

释义：

delimiter 自定义符号是为了在函数内些语句方便，制定除了;之外的符号作为函数书写结束标志
形参列表：[ IN | OUT | INOUT ] 形参名类型
in 输入，out 输出，inout 可以输入也可以输出
存储过程：若干sql语句组成，如果只有一条语句也可以不写delimiter和begin,end

# 创建无参数的自定义存储过程

# 例: 创建一个存储过程, 输出学生的name和score信息, 以score从大到小排列
delimiter $$
create procedure st() 
begin 
select name, score from test order by score desc; 
end $$
delimiter ;

# 调用自定义存储过程
call st();

# 创建有参数的自定义存储过程

# IN类型参数
delimiter $$
create procedure p_out ( IN num int )
begin
select num;		# 输出外部传入的num的值
set num=100;	# 将num修改为100
select num;		# 输出num=100
end $$
delimiter ;
# 调用自定义存储过程
set @num=10;
call p_out(@num);	# 输出两次, 第一次num=10; 第二层次num=100

# OUT类型参数
delimiter $$
create procedure p_out ( OUT num int )
begin
select num;		# 输出num=null
set num=100;	# 设置num为100
select num;		# 输出num=100
end $$
delimiter ;
# 调用自定义存储过程
set @num=10;
call p_out(@num);	# 输出两次, 第一次num=null; 第二层次num=100

# INOUT类型参数
delimiter $$
create procedure p_out ( INOUT num int )
begin
select num;			# 输出外部传入的num的值
set num=100;		# 将num修改为100
select num;			# 输出num=100
end $$
delimiter ;
# 调用自定义存储过程
set @num=10;
call p_out(@num);	# 输出两次, 第一次num=10; 第二层次num=100

存储过程三个参数的区别

IN 类型参数可以接收变量也可以接收常量，传入的参数在存储过程内部使用即可，但是在存储过程内部的修改无法传递到外部。
OUT 类型参数只能接收一个变量，接收的变量不能够在存储过程内部使用（内部为NULL），但是可以在存储过程内对这个变量进行修改。因为定义的变量是全局的，所以外部可以获取这个修改后的值。
INOUT类型参数同样只能接收一个变量，但是这个变量可以在存储过程内部使用。在存储过程内部的修改也会传递到外部

10.2.2 查看自定义存储过程

若要修改自定义函数的内容，则需要先删除该自定义函数，然后重新创建。

# 查看自定义存储过程的定义过程
show create procedure 函数名称;

# 例: 查看p_out存储过程的定义过程
show create procedure p_out;

# 查看库内所有的自定义存储过程
select name from mysql.proc where db='数据库名' and type='procedure';

# 例: 查看maoyandb数据库中的所有自定义存储过程
select name from mysql.proc where db='maoyandb' and type='procedure';

10.2.3 删除自定义存储过程

# 删除自定义函数
DROP procedure IF EXISTS 存储过程名;

# 删除名为find_stu的函数
drop procedure if exists p_out;

11. MySQL的事务机制

MySQL 事务主要用于处理操作量大，复杂度高的数据。

比如说，在人员管理系统中，你删除一个人员，既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，如果操作就必须同时操作成功，如果有一个不成功则所有数据都不动。这时候数据库操作语句就构成一个事务。事务主要处理数据的增删改操作。

定义

一件事从开始发生到结束的过程

作用

确保数据操作过程中的安全

11.1 事务的操作

# 使用事务的语法1: 所有语句执行成功
begin;
....
要执行的MySQL增删改语句
....
commit;		# 事务中SQL命令都执行成功,提交到数据库,结束!

# 使用事务的语法2: 有语句执行失败
begin;
....
要执行的MySQL增删改语句
....
rollback;	# 取消SQL命令的执行,回滚到初始状态,结束!

# 例: 用事务执行test表中添加数据的操作
begin;
insert into test(name, age, score) values('Tom', 25, 68);
commit;
# 用selct查看数据, 核实添加成功
select * from test;

# 例: 用事务执行test表中添加数据的操作, 并回滚到初始状态, 使添加不超过
begin;
insert into test(name, age, score) values('Amy', 27, 98);
rollback;
# 用selct查看数据, 核实添加不成功
select * from test;

11.2 事务四大特性

原子性（atomicity）
- 整个事务中的所有操作要么全部提交成功，要么全部失败回滚
- 一个事务必须视为一个不可分割的最小工作单元，对于一个事务来说，不可能只执行其中的一部分操作
一致性（consistency）
- 事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏。
隔离性（isolation）
- 数据库允许多个并发事务同时对其数据进行读写和修改的能力，而多个事务相互独立。
- 隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
持久性（durability）
- 一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

11.3 事务的隔离级别

事务四大特性中的隔离性是在使用事务时最为需要注意的特性，因为隔离级别不同带来的操作现象也有区别

读未提交：read uncommitted
- 事物A和事物B，事物A未提交的数据，事物B可以读取到
- 这里读取到的数据叫做“脏数据”
- 这种隔离级别最低，这种级别一般是在理论上存在，一般数据库隔离级别都高于该级别
读已提交：read committed
- 事物A和事物B，事物A提交的数据，事物B才能读取到
- 这种隔离级别高于读未提交
- 换句话说，对方事物提交之后的数据，我方的事物才能读取到
- 这种级别可以避免“脏数据”
- 这种隔离级别会导致“不可重复读取”
可重复读：repeatable read
- 事务A和事务B，事务A提交之后的数据，事务B读取不到
- 事务B是可重复读取数据
- 这种隔离级别高于读已提交
- MySQL默认级别
- 虽然可以达到可重复读取，但是会导致“幻像读”
串行化：serializable
- 事务A和事务B，事务A在操作数据库时，事务B只能排队等待
- 这种隔离级别很少使用，吞吐量太低，用户体验差
- 这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

12. 数据库的优化

12.1 数据库设计范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

各种范式呈递次规范，越高的范式数据库冗余越小。但是范式越高也意味着表的划分更细，一个数据库中需要的表也就越多，此时多个表联接在一起的花费是巨大的，尤其是当需要连接的两张或者多张表数据非常庞大的时候，表连接操作几乎是一个噩梦，这严重地降低了系统运行性能。所以通常数据库设计遵循第一第二第三范式，以避免数据操作异常,又不至于表关系过于复杂。

第一范式：
- 数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等组合的数据项。简单来说要求数据库中的表示二维表，每个数据元素不可再分。
- 例如：在国内的话通常理解都是姓名是一个不可再拆分的单位，这时候就符合第一范式；但是在国外的话还要分为FIRST NAME和LAST NAME，这时候姓名这个字段就是还可以拆分为更小的单位的字段，就不符合第一范式了。
- 不符合第一范式的表设计:
- 符合第一范式的表设计
第二范式：
- 第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分，所有属性依赖于主属性。
- 即选取一个能区分每个实体的属性或属性组，作为实体的唯一标识，每个属性都能被主属性筛选。其实简单理解要设置一个区分各个记录的主键就好了。
- 不符合第二范式的表设计
- 符合第二范式的表设计
第三范式：
- 在第二范式的基础上属性不传递依赖，即每个属性不依赖其他非主属性。
- 要求一个表中不包含已在其它表中包含的非主关键字信息。其实简单来说就是合理使用外键，使不同的表中不要有重复的字段就好了。
- 符合第三范式的表设计

12.2 MySQL存储引擎

mysql数据库管理系统中用来处理表的处理器

# 1、查看所有存储引擎
show engines;

# 2、查看已有表的存储引擎
show create table 表名;

# 3、创建表指定存储引擎
create table 表名(...)engine=MyISAM;

# 4、给已有表指定存储引擎
alter table 表名 engine=InnoDB;

12.2.1 常用存储引擎介绍

InnoDB
1. 支持行级锁,仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。
2. 支持外键、事务、事务回滚
3. 表字段和索引同存储在一个文件中
  - 表名.frm: 表结构
  - 表名.ibd: 表记录及索引文件
MyISAM
1. 支持表级锁, 在锁定期间, 其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许
2. 表字段和索引分开存储
  - 表名.frm ：表结构
  - 表名.MYI : 索引文件(my index)
  - 表名.MYD : 表记录(my data)

12.2.2 如何选择存储引擎

1、执行查操作多的表用 MyISAM(使用InnoDB浪费资源)
2、执行写操作多的表用 InnoDB

12.3 字段数据类型的选择

优先程度:
- 数字 > 时间日期 > 字符串
同一级别的数据类型
- 占用空间小的 > 占用空间多的
对数据存储精确不要求
- int > float > decimel
如果很少被查询可以用:
- TIMESTAMP（时间戳实际是整形存储）

字符串在查询比较排序时数据处理慢
占用空间少，数据库占磁盘页少，读写处理就更快

12.4 键的设置

Innodb如果不设置主键也会自己设置隐含的主键，所以最好自己设置
尽量设置占用空间小的字段为主键
外键的设置用于保持数据完整性，但是会降低数据导入和操作效率，特别是高并发情况下，而且会增加维护成本
虽然高并发下不建议使用外键约束，但是在表关联时建议在关联键上建立索引，以提高查找速度

12.5 EXPLAIN语句

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的: 看看这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描等等。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

通过explain命令可以得到:

id: 表的读取顺序
- id相同时，执行顺序由上至下
select_type: 查询中每个select子句的类型
- SIMPLE (简单SELECT, 不使用UNION或子查询等)
- PRIMARY (查询中若包含任何复杂的子部分, 最外层的select被标记为PRIMARY)
- UNION (UNION中的第二个或后面的SELECT语句)
- DEPENDENT UNION (UNION中的第二个或后面的SELECT语句, 取决于外面的查询)
- UNION RESULT (UNION的结果)
- SUBQUERY (子查询中的第一个SELECT)
- DEPENDENT SUBQUERY (子查询中的第一个SELECT, 取决于外面的查询)
- DERIVED (派生表的SELECT, FROM子句的子查询)
- UNCACHEABLE SUBQUERY (一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
table: 显示这一行的数据是关于哪张表的
type: 数据读取操作的操作类型 (从上到下，性能从差到好)
- ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
- index: Full Index Scan，index与ALL区别为index类型只遍历索引树
- range: 只检索给定范围的行，使用一个索引来选择行
- ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
- eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
- const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
- NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys: 哪些索引可以使用, 但不一定被查询使用
key: 哪些索引被实际使用
key_len: 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度; 不损失精确性的情况下，长度越短越好
ref: 表之间的引用
rows: 每张表有多少行被优化器查询

# 例:
explain select test.name, class.class_name from test, class where test.class = class.class_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GeXFWLZv-1596082206670)(C:\Users\cjm_s\AppData\Roaming\Typora\typora-user-images\image-20200730114600621.png)]

12.6 表的拆分

垂直拆分：表中列太多，分为多个表，每个表是其中的几个列。将常查询的放到一起，blob或者text类型字段放到另一个表

水平拆分：减少每个表的数据量，通过关键字进行划分然后拆成多个表

12.7 常用的MySQL优化方法

尽量选择数据类型占空间少的
在where ，group by，order by中出现的频率高的字段建立索引
尽量避免使用 select * from … 用具体字段名代替 * , 不要返回用不到的任何字段
少使用like %查询，否则会全表扫描
控制使用自定义函数
单条查询最后添加 LIMIT 1，停止全表扫描
where子句中不使用 != ,否则放弃索引全表扫描
尽量避免 NULL 值判断, 否则会放弃索引, 进行全表扫描
```
# 优化前:
select number from t1 where number is null;
# 优化后：
select number from t1 where number=0;
```
在number列上设置默认值0,确保number列无NULL值

尽量避免 or 连接条件,否则会放弃索引进行全表扫描，可以用union代替

# 优化前：
select id from t1 where id=10 or id=20;
# 优化后： 
select id from t1 where id=10 union all select id from t1 where id=20;

尽量避免使用 in 和 not in,否则会全表扫描

# 优化前：
select id from t1 where id in(1,2,3,4);
# 优化后：
select id from t1 where id between 1 and 4;

cjm_success

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫