1、初识数据库

1.1、什么是数据库

数据库：DB（DataBase）

概念：数据仓库，软件，安装在操作系统之上

作用：存储数据，管理数据

1.2、数据库分类

关系型数据库：SQL（Structured Query Language）

• MySQL、Oracle、Sql Server、DB2、SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储
• 通过外键关联来建立表与表之间的关系

非关系型数据库：NoSQL（Not Only SQL）

• Redis、MongoDB
• 指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

1.3、相关概念

DBMS（数据库管理系统）

• 数据库的管理软件，科学有效的管理、维护和获取我们的数据
• MySQL就是数据库管理系统

1.4、基本命令

所有的语句都要以分号结尾

show databases;	--查看当前所有的数据库
use 数据库名;	--打开指定的数据库
show tables;	--查看所有的表
describe/desc 表名;			--显示表的信息
create database 数据库名;	--创建一个数据库
exit						--退出连接--			--单行注释
#			--单行注释
/*...*/		--多行注释

2、操作数据库

2.1、操作数据库

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名;

2、删除数据库

DROP DATABASE [if EXISTS] 数据库名;

3、使用数据库

--如果表名或者字段名是特殊字符，则需要带``
use 数据库名;

4、查看数据库

SHOW DATABASES;

2.2、数据库的数据类型

null

• 没有值，未知
• 不要使用NULL值进行计算

2.3、数据库的字段属性

UnSigned

无符号的

声明了该列不能为负数

ZEROFILL

0填充的

不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement

通常理解为自增，自动在上一条记录的基础上默认+1

通常用来设计唯一的主键，必须是整数类型

可定义起始值和步长

• 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
• SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL 和 NOT NULL

默认为NULL , 即没有插入该列的数值

如果设置为NOT NULL , 则该列必须有值

DEFAULT

默认的

用于设置默认值

例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

---

拓展：每一个表，都必须存在以下五个字段：

名称	描述
id	主键
version	乐观锁
deleted_at	伪删除
created_at	创建时间
updated_at	修改时间

2.4、创建数据库表

CREATE TABLE IF NOT EXISTS `student`(`id` INT(4)	NOT NULL AUTO_INCREMENT COMMENT '学号',`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

注意点：

• 表名和字段尽量使用``括起来

• AUTO_INCREMENT 代表自增

• 所有的语句后面加逗号，最后一个不加

• 字符串使用单引号括起来

• 主键的声明一般放在最后，便于查看

• 不设置字符集编码的话，会使用MySQL默认的字符集编码Latin1，不支持中文，可以在my.ini里修改

格式：

CREATE TABLE IF NOT EXISTS `student`('字段名' 列类型 [属性] [索引] [注释],'字段名' 列类型 [属性] [索引] [注释],......'字段名' 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]# 常用命令：
SHOW CREATE DATABASE 数据库名;-- 查看创建数据库的语句
SHOW CREATE TABLE 表名;-- 查看表的定义语句
DESC 表名;-- 显示表的具体结构

2.5、数据库存储引擎

INNODB

默认使用，安全性高，支持事务的处理，多表多用户操作

MYISAM

早些年使用，节约空间，速度较快

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为2倍

数据库文件存在的物理空间位置：

MySQL数据表以文件方式存放在磁盘中

包括表文件 , 数据文件 , 以及数据库的选项文件

位置 : Mysql安装目录\data\（目录名对应数据库名 , 该目录下文件名对应数据表）

MySQL在文件引擎上区别：

INNODB数据库文件类型就包括**.frm**、.ibd以及在上一级目录的ibdata1文件

MYISAM存储引擎，数据库文件类型就包括

.frm：表结构定义文件
.MYD：数据文件
.MYI：索引文件

2.6、修改数据库

修改表名 : ALTER TABLE 旧表名 RENAME AS 新表名

添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]

修改字段 :

• ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
• ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

删除字段 :ALTER TABLE 表名 DROP 字段名

-- 修改表名
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teachers;-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teachers ADD age INT(11);-- 修改表的字段(重命名，修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 [列属性];
ALTER TABLE teachers MODIFY age VARCHAR(11);-- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 [列属性];
ALTER TABLE teachers CHANGE age age1 INT(1);-- 字段重命名-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teachers DROP age1;

语法：DROP TABLE [IF EXISTS] 表名

IF EXISTS为可选 , 判断是否存在该数据表

如删除不存在的数据表会抛出错误

-- 删除表(如果存在再删除)
DROP TABLE IF EXISTS teachers;

所有的创建和删除尽量加上判断，以免报错~

3、MySQL数据管理

3.1、外键

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。

外键作用：

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

---

目标：学生表（student）的gradeid字段 要去引用年级表（grade）的 gradeid字段

创建外键

方式一：在创建表的时候增加约束

-- 创建年级表
CREATE TABLE `grade`(`gradeid` INT(10) NOT NULL COMMENT '年级id',`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;/*1. 定义外键key2. 给外键添加约束（执行引用）references 引用
*/
CREATE TABLE IF NOT EXISTS `student`(`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',`sex` VARCHAR(2)	NOT NULL DEFAULT '女' COMMENT '性别',`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',`gradeid` INT(10) NOT NULL COMMENT '学生的年级',PRIMARY KEY (`id`),KEY `FK_gradeid` (`gradeid`),CONSTRAINT `FK_student_grade` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

---

方法二：创建表成功后，添加外键约束

/*1. 定义外键key2. 给外键添加约束（执行引用）references 引用
*/
CREATE TABLE IF NOT EXISTS `student`(`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',`sex` VARCHAR(2)	NOT NULL DEFAULT '女' COMMENT '性别',`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',`gradeid` INT(10) NOT NULL COMMENT '学生的年级',PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);-- 创建年级表
CREATE TABLE `grade`(`gradeid` INT(10) NOT NULL COMMENT '年级id',`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

创建顺序

以上的操作都是物理外键，数据库级别的外键，不建议使用！避免数据库过多造成困扰！

最佳实践

数据库就是用来单纯的表，只用来存数据，只有行（数据）和列（属性）
我们想使用多张表的数据，使用外键，用程序去实现

创建外键的方法

首先谈一下外键的作用，一是节省空间，二是约束含外键的列中的值，含外键列的值必须为参考列中的id。

创建数据表时，创建外键方法

create table department(id bigint auto_increment primary key,title char(15)
)engine=innodb default charset=utf8;create table userinfo(uid bigint auto_increment primary key,  # 创建列uid ，bigint类型，自增，主键name varchar(32),                       #  创建列name  varchar类型，32位department_id bigint,                                         xx_id int,CONSTRAINT fk_userinfo_department foreign key (department_id) references department(id)   #创建外键
)engine=innodb default charset=utf8;

创建外键语法如下

constraint fk_userinfo_department foreign key (department_id) references department(id)
constraint 外键名字  foreign key （添加外键的列名）references 参考表的名称（参考表中的列名）

外键也可以是两列，但是前提条件是，被参考的表的两列均是主键

CREATE TABLE t5 (nid int(11) NOT NULL AUTO_INCREMENT,pid int(11) not NULL,num int(11),primary key(nid,pid)) ENGINE=InnoDB DEFAULT CHARSET=utf8;create table t6(id int auto_increment primary key,name char(10),id1 int,id2 int,CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t5(nid,pid))engine=innodb default charset=utf8;

MySQL报错cannot add foreign key constraint解决方法

原因分析：

创建外键错误的原因大概有一下几个原因：

1、关联的两个字段的字段的类型不一致
2、设置外键删除时set null
3、两张表的引擎不一致

3.2、DML语言

数据库的意义：数据存储，数据管理

Data Manipulation Luaguge：数据操作语言

1. 添加 insert

-- 普通用法
INSERT INTO `student`(`name`) VALUES ('zsr');-- 插入多条数据
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('zsr','200024','男'),('gcc','000421','女');-- 省略字段
INSERT INTO `student` VALUES (5,'Bareth','123456','男','2000-02-04','武汉','1412@qq.com',1); 

语法：

INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];

注意：

1、字段和字段之间使用英文逗号隔开
2、字段是可以省略的，但是值必须完整且一一对应
3、可以同时插入多条数据，VALUES后面的值需要使用逗号隔开

2. 修改 update

-- 修改学员名字,指定条件
UPDATE `student` SET `name`='zsr204' WHERE id=1;-- 不指定条件的情况,会改动所有表
UPDATE `student` SET `name`='zsr204';-- 修改多个属性
UPDATE `student` SET `name`='zsr',`address`='湖北' WHERE id=1;-- 通过多个条件定位数据
UPDATE `student` SET `name`='zsr204' WHERE `name`='zsr' AND `pwd`='200024';

语法：

UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];

关于WHERE条件语句：

操作符	含义
=	等于
<>或!=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN…AND…	闭合区间
AND	和
OR	或

3. 删除 delete

-- 删除数据(避免这样写,会全部删除)
DELETE FROM `student`;-- 删除指定数据
DELETE FROM `student` WHERE id=1;

语法：

DELETE FROM 表名 [WHERE 条件]

关于DELETE删除的问题，重启数据库现象：

INNODB 自增列会从1开始（存在内存当中，断电即失）
MYISAM 继续从上一个子增量开始（存在内存当中，不会丢失）

TRUNCATE 作用：完全清空一个数据库表，表的结构和索引约束不会变！

DELETE和TRUNCATE 的区别：

• DELETE可以条件删除（where子句），而TRUNCATE只能删除整个表
• TRUNCATE 重新设置自增列，计数器会归零，而DELETE不会影响自增
• DELETE是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollback segment中，可以被回滚；而TRUNCATE是数据定义语言（DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚。

CREATE TABLE `test`(`id` INT(4) NOT NULL AUTO_INCREMENT,`coll` VARCHAR(20) NOT NULL,PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');-- 不会影响自增
DELETE FROM `test`;-- 会影响自增
TRUNCATE TABLE `test`;

4、DQL查询数据

Data QueryLanguage 数据查询语言

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias][left | right | inner join table_name2]  -- 联合查询[WHERE ...]  -- 指定结果需满足的条件[GROUP BY ...]  -- 指定结果按照哪几个字段来分组[HAVING]  -- 过滤分组的记录必须满足的次要条件[ORDER BY ...]  -- 指定查询记录按一个或多个条件排序[LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条

前提配置

-- 创建学校数据库
CREATE DATABASE IF NOT EXISTS `school`;-- 用school数据库
USE `school`;-- 创建年级表grade表
CREATE TABLE `grade`(`GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',`GradeName` VARCHAR(50) NOT NULL COMMENT '年纪名称',PRIMARY KEY	(`GradeID`)
)ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- 给grade表插入数据
INSERT INTO `grade`(`GradeID`,`GradeName`) 
VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四');-- 创建成绩result表
CREATE TABLE `result`(`StudentNo` INT(4) NOT NULL COMMENT '学号',`SubjectNo` INT(4) NOT NULL COMMENT '考试编号',`ExamDate` DATETIME NOT NULL COMMENT '考试日期',`StudentResult` INT(4) NOT NULL COMMENT '考试成绩',KEY `SubjectNo` (`SubjectNo`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;-- 给result表插入数据
INSERT INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) 
VALUES (1000,1,'2019-10-21 16:00:00',97),(1001,1,'2019-10-21 16:00:00',96),
(1000,2,'2019-10-21 16:00:00',87),(1001,3,'2019-10-21 16:00:00',98);-- 创建学生表student
CREATE TABLE `student`(	`StudentNo` INT(4) NOT NULL COMMENT '学号',`LoginPwd` VARCHAR(20) DEFAULT NULL,`StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',`Sex` TINYINT(1) DEFAULT NULL COMMENT '性别,取值0或1',`GradeID` INT(11) DEFAULT NULL COMMENT '年级编号',`Phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空,即可选输入',`Adress` VARCHAR(255) NOT NULL COMMENT '地址,允许为空,即可选输入',`BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',`Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号,允许为空,即可选输入',`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',PRIMARY KEY (`StudentNo`),UNIQUE KEY `IdentityCard` (`IdentityCard`),KEY `Email` (`Email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;-- 给学生表插入数据
INSERT INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeID`,`Phone`,`Adress`,`BornDate`,`Email`,`IdentityCard`) 
VALUES (1000,'1241','dsaf',1,2,'24357','unknow','2000-09-16 00:00:00','1231@qq.com','809809'),
(1001,'1321','dfdj',0,2,'89900','unknow','2000-10-16 00:00:00','5971@qq.com','908697');-- 创建科目表
CREATE TABLE `subject`(`SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',`SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',`ClassHour` INT(4) DEFAULT NULL COMMENT '学时',`GradeID` INT(4) DEFAULT NULL COMMENT '年级编号',PRIMARY KEY (`SubjectNo`)
)ENGINE=INNODB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;-- 给科目表subject插入数据
INSERT INTO `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) 
VALUES(1,'高数','96',2),(2,'大物','112',2),(3,'程序设计',64,3);SELECT 字段 FROM 表;

4.1、基础查询

查询列表可以是：表中的（一个或多个）字段，常量，变量，表达式，函数
查询结果是一个虚拟的表格

-- 查询全部学生
SELECT * FROM student;-- 查询指定的字段
SELECT `LoginPwd`,`StudentName` FROM student;-- 别名 AS(可以给字段起别名,也可以给表起别名)
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS 学生表;-- 函数 CONCAT(str1,str2,...)
SELECT CONCAT('姓名',`StudentName`) AS 新名字 FROM student;-- 查询系统版本(函数)
SELECT VERSION();-- 用来计算(计算表达式)
SELECT 100*53-90 AS 计算结果;-- 查询自增步长(变量)
SELECT @@auto_increment_increment;-- 查询有哪写同学参加了考试,重复数据要去重
SELECT DISTINCT `StudentNo` FROM result;

4.2、条件查询

where 条件字句：检索数据中符合条件的值

语法：

select 查询列表 from 表名 where 筛选条件;

-- 查询考试成绩在95~100之间的
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100;-- &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100;-- BETWEEN AND
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`BETWEEN 95 AND 100;-- 查询除了1000号以外的学生
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNo`!=1000;-- NOT
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT `StudentNo`=1000;-- 查询名字含d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d%';-- 查询名字倒数第二个为d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d_';-- 查询1000,1001学员
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentNo` IN (1000,1001);

4.3、分组查询

语法：

select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

区别：

	使用关键字	筛选的表	位置
分组前筛选	where	原始表	group by的前面
分组后筛选	having	分组后的结果	group by 的后面

-- 查询不同科目的平均分、最高分、最低分且平均分大于90
-- 核心：根据不同的课程进行分组
SELECT SubjectName,AVG(StudentResult),MAX(`StudentResult`),MIN(`StudentResult`)
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
GROUP BY r.SubjectNo
HAVING AVG(StudentResult)>90;

4.4、连接查询

-- 查询学员所属的年级（学号，学生姓名，年级名称）
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID;-- 查询科目所属的年级
SELECT `SubjectName`,`GradeName`
FROM `subject` s
INNER JOIN `grade` g
ON s.GradeID=g.GradeID;-- 查询列参加程序设计考试的同学信息（学号，姓名，科目名，分数）
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN result r
on s.StudentNo=r.StudentNo
INNER JOIN `subject` sub
on r.SubjectNo=sub.SubjectNo
where SubjectName='课程设计';

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

-- 创建一个表
CREATE TABLE `course` (
`courseid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程id',
`pid` INT(10) NOT NULL COMMENT '父课程id',
`courseName` VARCHAR(50) NOT NULL COMMENT '课程名',
PRIMARY KEY (`courseid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8-- 插入数据
INSERT INTO `course` (`courseid`, `pid`, `courseName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

得到下表：

将该表进行拆分：

pid（父课程id）	courseid（课程id）	courseName（课程名）
1	2	信息技术
1	3	软件开发
1	5	美术设计

pid（父课程id）	courseid（课程id）	courseName（课程名）
2	8	办公信息
3	4	数据库
3	6	web开发
5	7	ps技术

操作：查询父类对应的子类关系

父类	子类
信息技术 2	办公信息 4
软件开发 3	数据库 4、web开发 6
美术设计 5	ps技术 7

SELECT a.`courseid` AS '父课程',b.`courseid` AS '子课程'
FROM course AS a,course AS b
WHERE a.`courseid`=b.`pid`;

4.5、排序和分页

排序

语法：

select 查询列表
from 表
where 筛选条件
order by 排序列表 asc/desc

order by 的位置一般放在查询语句的最后（除limit语句之外）

SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID
ORDER BY `StudentNo` DESC;

分页

语法：

select 查询列表
from 表
limit offset,pagesize;

offset 代表的是起始的条目索引，默认从0开始
size 代表的是显示的条目数
offset=(n-1)*pagesize

-- 第一页 limit 0 5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第n页  limit (n-1)*pagesize,pagesize
-- pagesize:当前页面大小
-- (n-1)*pagesize:起始值
-- n:当前页面
-- 数据总数/页面大小=总页面数
-- limit n 表示从0到n的页面

4.6、子查询

本质：在 where子句中嵌套一个子查询语句

-- 查询‘课程设计’的所有考试结果（学号，科目编号，成绩）降序排列-- 方式一:使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM result r
INNER JOIN `subject` s
on r.StudentNo=s.SubjectNo
WHERE SubjectName='课程设计'
ORDER BY StudentResult DESC;-- 方式二:使用子查询（由里到外）
SELECT StudentNo,SubjectNo,StudentResult
from result
WHERE SubjectNo=(SELECT SubjectNo FROM `subject`WHERE SubjectName='课程设计'
)

4.7、MySQL函数

1. 常用函数

-- 数学运算
SELECT ABS(-8); -- 绝对值
SELECT CEIL(5.1); -- 向上取整
SELECT CEILING(5.1); -- 向上取整
SELECT RAND(); -- 返回0~1之间的一个随机数
SELECT SIGN(-10); -- 返回一个数的符号;0返回0;正数返回1;负数返回-1-- 字符串函数
SELECT CHAR_LENGTH('我喜欢你'); -- 字符串长度
SELECT CONCAT('我','喜欢','你'); -- 拼接字符串
SELECT INSERT('我喜欢',1,1,'超级') -- INSERT(str,pos,len,newstr) 从str的pos位置开始替换为长度为len的newstr
SELECT UPPER('zsr'); -- 转大写
SELECT LOWER('ZSR'); -- 转小写
SELECT INSTR('zsrs','s'); -- 返回第一次出现字串索引的位置
SELECT REPLACE('加油就能胜利','加油','坚持'); -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是胜利',3,6); -- 返回指定的字符串(源字符串,截取位置,截取长度)
SELECT REVERSE('rsz'); -- 反转字符串-- 时间日期函数
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE(); -- 获取当前日期
SELECT now(); -- 获取当前时间
SELECT LOCALTIME(); -- 本地时间
SELECT SYSDATE(); -- 系统时间SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());-- 系统信息
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();

2. 聚合函数

函数	描述
max	最大值
min	最小值
sum	和
avg	平均值
count	计算个数

SELECT COUNT(StudentName) FROM student; 
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;SELECT SUM(`StudentResult`) FROM result;
SELECT AVG(`StudentResult`) FROM result;
SELECT MAX(`StudentResult`) FROM result;
SELECT MIN(`StudentResult`) FROM result;

select count(*)和select count(1)的区别

1、 一般情况下，Select Count (*)和Select Count(1)两着返回结果是一样的

2、 假如表沒有主键(Primary key), 那么count(1)比count(*)快，

3、 如果有主键的話，那主键作为count的条件时候count(主键)最快

4、 如果你的表只有一个字段的话那count(*)就是最快的

5、count(*) 跟 count(1) 的结果一样，都包括对NULL的统计，而count(column) 是不包括NULL的统计

5、数据库级别的MD5加密

MD5信息摘要算法（MD5 Message-Digest Algorithm）

MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性

MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密前的值

CREATE TABLE `testMD5`(`id` INT(4) NOT NULL,`name` VARCHAR(20) NOT NULL,`pwd` VARCHAR(50) NOT NULL,PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET =utf8;-- 明文密码
INSERT INTO `testMD5` VALUES(1,'zsr','200024'),
(2,'gcc','000421'),(3,'bareth','123456');-- 加密
UPDATE `testMD5` SET `pwd`=MD5(pwd) WHE RE id=1;
UPDATE `testMD5` SET `pwd`=MD5(pwd); -- 加密全部的密码-- 插入的时候加密
INSERT INTO `testMD5` VALUES(4,'barry',MD5('654321'));-- 如何校验:将用户传递进来的密码，进行MD5加密，然后对比加密后的值
SELECT * FROM `testMD5` WHERE `name`='barry' AND `pwd`=MD5('654321');

6、事务

要么都成功，要么都失败。

将一组SQL放在一个批次中去执行

例如银行转账：

只有A转账成功且B成功到账，该事件才算结束，如果一方不成功，则该事务不成功

6.1、事务原则：ACID

名称	描述
原子性（Atomicity）	原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）	事务前后数据的完整性必须保持一致。
隔离性（Isolation）	事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）	事务一旦被提交则不可逆，被持久化到数据库中，接下来即使数据库发生故障也不应该对其有任何影响

原子性

针对同一个事务

这个过程包含两个步骤

A： 800 - 200 = 600
B: 200 + 200 = 400

原子性表示，这两个步骤一起成功，或者一起失败，不能只发生其中一个动作。

一致性（Consistency）

针对一个事务操作前与操作后的状态一致

操作前A：800，B：200
操作后A：600，B：400

一致性表示事务完成后，符合逻辑运算

持久性（Durability）

表示事务结束后的数据不随着外界原因导致数据丢失

操作前A：800，B：200
操作后A：600，B：400

如果在操作前（事务还没有提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为

A：800，B：200

如果在操作后（事务已经提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为

A：600，B：400

隔离性（Isolation）

针对多个用户同时操作，主要是排除其他事务对本次事务的影响

事务一）A向B转账200
事务二）C向B转账100

两个事务同时进行，其中一个事务读取到另外一个事务还没有提交的数据，执行步骤如图所示，按照数字顺序执行

6.2、事务并发导致的问题

名称	描述
脏读	指一个事务读取了另外一个事务未提交的数据。
不可重复读	在一个事务内读取表中的某一行数据，多次读取结果不同。
虚读(幻读)	是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

6.3、隔离级别

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别

读未提交：

一个事务读取到其他事务未提交的数据；这种隔离级别下，查询不会加锁，一致性最差，会产生脏读、不可重复读、幻读的问题

读已提交：

一个事务只能读取到其他事务已经提交的数据；该隔离级别避免了脏读问题的产生，但是不可重复读和幻读的问题仍然存在；

读提交事务隔离级别是大多数流行数据库的默认事务隔离级别，比如 Oracle，但是不是 MySQL 的默认隔离界别

可重复读：

事务在执行过程中可以读取到其他事务已提交的新插入的数据，但是不能读取其他事务对数据的修改，也就是说多次读取同一记录的结果相同；该个里级别避免了脏读、不可重复度的问题，但是仍然无法避免幻读的问题

可重复读是MySQL默认的隔离级别

串行化：

事务串行化执行，事务只能一个接着一个地执行,、，并且在执行过程中完全看不到其他事务对数据所做的更新；缺点是并发能力差，最严格的事务隔离，完全符合ACID原则，但是对性能影响比较大

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

四种隔离级别设置

数据库

set transaction isolation level 设置事务隔离级别

select @@tx_isolation 查询当前事务隔离级别

设置	描述
Serializable	可避免脏读、不可重复读、虚读情况的发生。（串行化）
Repeatable read	可避免脏读、不可重复读情况的发生。（可重复读）
Read committed	可避免脏读情况发生（读已提交）。
Read uncommitted	最低级别，以上情况均无法保证。(读未提交)

REPEATABLE-READ 此为默认事务隔离级别。

6.4、事务演示

进入MySQL

mysql -h localhost -uroot -p

数据测试准备

create database db_test;
use db_test;CREATE TABLE `amount` (
`id`  varchar(10) NULL,
`money`  numeric NULL
);insert into amount(id,money) values('A', 800);
insert into amount(id,money) values('B', 200);
insert into amount(id,money) values('C', 1000);
insert into amount(id,money) values('D', 1000);

mysql事务隔离性相关语句说明

--  查询当前控制台（客户端）当前事务级别
SELECT @@session.tx_isolation;   
SELECT @@tx_isolation;  --  设置当前控制台（客户端）当前事务级别
SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted;  
SET SESSION TRANSACTION ISOLATION LEVEL read committed;  
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;  
SET SESSION TRANSACTION ISOLATION LEVEL serializable;  --开启事务
start transaction;--提交事务，同时事务结束
commit;--回退事务，回退操作，同时事务结束
rollback

案列1：脏读演示，读到其他事务未提交的数据

操作步骤

1、事务一（右）：A向B转200，不提交事务
2、事务二（左）：查看B金额
3、事务一（右）：回滚事务
4、事务二（左）：查看B金额

改变事务二（左）事务级别为READ-UNCOMMITTED

两种方式

gif演示

转账SQL

update amount set money = money - 200 where id = 'A';
update amount set money = money + 200 where id = 'B';

其他事务级别请自行测试（与理论预期一致）

• 案列2，将事务二（左）在事务设置为REPEATABLE-READ，再看看操作结果（操作步骤同案例1，步骤三改为提交事务-commit）
• 案列3，将事务二（左）在事务设置为REPEATABLE-READ，再看看操作结果（操作步骤同案例1，步骤三改为提交事务-commit）
• 案列4，将事务二（左）在事务设置为SERIALIZABLE，再看看操作结果（操作步骤同案例1）

这里说明一下，SERIALIZABLE事务是串行执行，演示的时候只能由其中一个客户端操作，直到提交事务，另外一个客户端才能操作。

6.5、执行事务的过程

1️⃣ 关闭自动提交

SET autocommit=0; 

2️⃣ 事务开启

START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内 

3️⃣ 成功则提交，失败则回滚

-- 提交：持久化（成功）
COMMIT-- 回滚：回到原来的样子（失败）
ROLLBACK

4️⃣ 事务结束

SET autocommit=1; -- 开启自动提交

​5️⃣ 其他操作

SAVEPOINT 保存点名; -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名; -- 回滚到保存点
RELEASE SAVEPOINT 保存点名; -- 撤销保存点

7、索引

索引（Index）是帮助MySQL高效获取数据的数据结构。

1、提高查询速度
2、确保数据的唯一性
3、可以加速表和表之间的连接 , 实现表与表之间的参照完整性
4、使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
5、全文检索字段进行搜索优化

• NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
• UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
• PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
• FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
• CHECK: 用于控制字段的值范围。

7.1、索引的分类

-- 创建学生表student
CREATE TABLE `student`(	`StudentNo` INT(4) NOT NULL COMMENT '学号',`LoginPwd` VARCHAR(20) DEFAULT NULL,`StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',`Sex` TINYINT(1) DEFAULT NULL COMMENT '性别,取值0或1',`GradeID` INT(11) DEFAULT NULL COMMENT '年级编号',`Phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空,即可选输入',`Adress` VARCHAR(255) NOT NULL COMMENT '地址,允许为空,即可选输入',`BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',`Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号,允许为空,即可选输入',`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',PRIMARY KEY (`StudentNo`),UNIQUE KEY `IdentityCard` (`IdentityCard`),KEY `Email` (`Email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

主键索引（PRIMARY KEY）

唯一的标识，主键不可重复，只有一个列作为主键

最常见的索引类型，不允许为空值
确保数据记录的唯一性
确定特定数据记录在数据库中的位置

-- 创建表的时候指定主键索引
CREATE TABLE tableName(......PRIMARY INDEX (columeName)
)-- 修改表结构添加主键索引
ALTER TABLE tableName ADD PRIMARY INDEX (columnName)

普通索引（KEY / INDEX）

默认的，快速定位特定数据

index 和 key 关键字都可以设置常规索引
应加在查询找条件的字段
不宜添加太多常规索引,影响数据的插入,删除和修改操作

-- 直接创建普通索引
CREATE INDEX indexName ON tableName (columnName)-- 创建表的时候指定普通索引
CREATE TABLE tableName(......INDEX [indexName] (columeName)
)-- 修改表结构添加普通索引
ALTER TABLE tableName ADD INDEX indexName(columnName)

唯一索引（UNIQUE KEY）

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值

与主键索引的区别：主键索引只能有一个、唯一索引可以有多个

-- 直接创建唯一索引
CREATE UNIQUE INDEX indexName ON tableName(columnName)-- 创建表的时候指定唯一索引
CREATE TABLE tableName(  ......UNIQUE INDEX [indexName] (columeName)  
);  -- 修改表结构添加唯一索引
ALTER TABLE tableName ADD UNIQUE INDEX [indexName] (columnName)

全文索引（FULLText）

快速定位特定数据（百度搜索就是全文索引）

在特定的数据库引擎下才有：MyISAM
只能用于CHAR , VARCHAR , TEXT数据列类型
适合大型数据集

-- 增加一个全文索引
ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('d'); -- 全文索引

7.2、索引的使用

1. 索引的创建

在创建表的时候给字段增加索引

CREATE TABLE 表名 (字段名1 数据类型 [完整性约束条件…],字段名2 数据类型 [完整性约束条件…],[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名] (字段名[(长度)] [ASC |DESC])
);

创建完毕后，增加索引

-- 方法一：CREATE在已存在的表上创建索引CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名ON 表名 (字段名[(长度)] [ASC |DESC]) ;-- 方法二：ALTER TABLE在已存在的表上创建索引ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;

2. 索引的删除

-- 删除索引DROP INDEX 索引名 ON 表名;
-- 删除主键索引ALTER TABLE 表名 DROP PRIMARY KEY;

3. 显示索引信息

SHOW INDEX FROM 表名;

4. explain分析sql执行的情况

-- 增加一个全文索引
ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('d'); -- 全文索引

7.3、测试索引

建表 app_user：

CREATE TABLE `app_user` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(50) DEFAULT '' COMMENT '用户昵称',`email` varchar(50) NOT NULL COMMENT '用户邮箱',`phone` varchar(20) DEFAULT '' COMMENT '手机号',`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',`password` varchar(100) NOT NULL COMMENT '密码',`age` tinyint(4) DEFAULT '0' COMMENT '年龄',`create_time` datetime DEFAULT CURRENT_TIMESTAMP,`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

批量插入数据：100w

-- 1418错解决方案(创建函数前执行此语句)
set global log_bin_trust_function_creators=true;-- 插入100万条数据
DELIMITER $$	-- 写函数之前要写的标志
CREATE FUNCTION mock_data()	-- 创建mock_data()函数
RETURNS INT
BEGINDECLARE num INT DEFAULT 1000000;DECLARE i INT DEFAULT 0;WHILE i < num DOINSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));SET i = i + 1;END WHILE;RETURN i;
END;-- 执行函数
SELECT mock_data();

执行步骤

-- 开启创建函数的功能
mysql> set global log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)-- 查看是否开启创建函数的功能
mysql> show variables like '%func%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.01 sec)-- 开启创建函数的功能
mysql> set global log_bin_trust_function_creators=true;
Query OK, 0 rows affected (0.00 sec)

在执行函数

在执行

删除已存在的函数
DROP FUNCTION mock_data;

TRUNCATE app_user; 清空表

测试查询速度

-- 查询用户名为'用户9999'性能分析
EXPLAIN SELECT * FROM app_user where name='用户99999'

增加索引后测试

-- 给name列创建常规索引
CREATE INDEX id_app_user_name ON app_user(`name`)
-- 再测试
EXPLAIN SELECT * FROM app_user where name='用户99999'

对比两次结果，速度有了很大的提升

7.4、索引原则

索引不是越多越好，小数据量的表不需要加索引

不要对经常变动的数据增加索引

索引一般加在经常要查询的列上

8、explain关键字

1、表的读取顺序
2、数据读取操作的操作类型
3、哪些索引可以使用
4、哪些索引被实际使用
5、表之间的引用
6、每张表有多少行被优化器查询

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。

explain执行计划包含的信息

其中最重要的字段为：id、type、key、rows、Extra

explain用法
explain+SQL语句即可！

explain 执行计划包含的信息

信息	描述
id	查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序 **两种情况** id相同，执行顺序从上往下 id不同，id值越大，优先级越高，越先执行
select_type	查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询 1、simple ——简单的select查询，查询中不包含子查询或者UNION 2、primary ——查询中若包含任何复杂的子部分，最外层查询被标记 3、subquery——在select或where列表中包含了子查询 4、derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中 5、union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived 6、union result:UNION 的结果
table	输出的行所引用的表
type	显示联结类型，显示查询使用了何种类型，按照从最佳到最坏类型排序 1、system：表中仅有一行（=系统表）这是const联结类型的一个特例。 2、const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量 3、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描 4、ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体 5、range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描 6、index:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。 7、all：遍历全表以找到匹配的行 注意:一般保证查询至少达到range级别，最好能达到ref。
possible_keys	指出MySQL能使用哪个索引在该表中找到行
key	显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。
key_len	表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。
ref	显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值
rows	根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra	包含不适合在其他列中显示，但是十分重要的额外信息 1、Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序” 2、Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。 3、Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。 4、Using where :表明使用where过滤 5、using join buffer:使用了连接缓存 6、impossible where:where子句的值总是false，不能用来获取任何元组 7、select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 8、distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

三种情况：

1、id相同：执行顺序由上至下

2、id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

3、id相同又不同（两种情况同时存在）：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type

查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询

1、SIMPLE：简单的select查询，查询中不包含子查询或者union
2、PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary
3、SUBQUERY：在select 或 where列表中包含了子查询

4、DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在零时表里

5、UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived

6、UNION RESULT：从union表获取结果的select

type

访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，好的sql查询至少达到range级别，最好能达到ref

1、system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计

2、const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const

3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。

注意：ALL全表扫描的表记录最少的表如t1表

4、ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体

5、range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引

6、index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）

7、ALL：Full Table Scan，遍历全表以找到匹配的行

possible_keys

查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引，如果为NULL，则没有使用索引。

查询中如果使用了覆盖索引，则该索引仅出现在key列表中

key_len

表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的

ref

显示索引的那一列被使用了，如果可能，是一个常量const。

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

不适合在其他字段中显示，但是十分重要的额外信息

1、Using filesort ：
mysql对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”

由于索引是先按email排序、再按address排序，所以查询时如果直接按address排序，索引就不能满足要求了，mysql内部必须再实现一次“文件排序”

2、Using temporary：
使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 和 group by

3、Using index：
表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高。

如果同时出现Using where，表明索引被用来执行索引键值的查找（参考上图）

如果没用同时出现Using where，表明索引用来读取数据而非执行查找动作

覆盖索引（Covering Index）：

也叫索引覆盖。就是select列表中的字段，只用从索引中就能获取，不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。

注意：

a、如需使用覆盖索引，select列表中的字段只取出需要的列，不要使用select *
b、如果将所有字段都建索引会导致索引文件过大，反而降低crud性能

4、Using where ：
使用了where过滤

5、Using join buffer ：
使用了链接缓存

6、Impossible WHERE：
where子句的值总是false，不能用来获取任何元祖

7、select tables optimized away：

在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化

8、distinct：

优化distinct操作，在找到第一个匹配的元祖后即停止找同样值得动作

9、权限管理和备份

9.1、用户管理

方式一：可视化管理

-- 创建用户
CREATE USER zsr IDENTIFIED BY '123456'-- 删除用户
DROP USER zsr-- 修改当前用户密码
SET PASSWORD = PASSWORD('200024')-- 修改指定用户密码
SET PASSWORD FOR zsr = PASSWORD('200024')-- 重命名
RENAME USER zsr to zsr2-- 用户授权(授予全部权限,除了给其他用户授权)
GRANT ALL	PRIVILEGES on *.* TO zsr2-- 查询权限
SHOW GRANTS FOR zsr
-- 查看root用户权限
SHOW GRANTS FOR root@localhost-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM zsr

9.2、数据库备份

保证重要的数据不丢失、数据转义

方式一：直接拷贝物理文件，MySQL数据表以文件方式存放在磁盘中

包括表文件 , 数据文件 , 以及数据库的选项文件

位置 : Mysql安装目录\data\（目录名对应数据库名 , 该目录下文件名对应数据表）

方式二：可视化管理

Navicat打开要备份的数据库，然后点击新建备份

方式三：可视化管理

方式四：命令mysqldump导出

# mysqldump -h主机 -u用户名 -p密码 数据库 [表1 表2 表3] >物理磁盘位置/文件名# 导出school数据库的cource grade student表到D:/school.sql
mysqldump -hlocalhost -uroot -p200024 school course grade student >D:/school.sql

source命令导入

10、三大范式

规范化理论：

改造关系模式，通过分解关系模式来消除其中不合适的数据依赖，以解决插入异常、删除异常、更新异常和数据冗余的问题。

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定规范化理论。

在关系型数据库中这种规则就称为范式

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由，比如性能，事实上，我们经常会为了性能而妥协数据库的设计。

三大范式的通俗理解

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

而通常我们用的最多的就是第一范式（1NF）、第二范式（2NF）、第三范式（3NF），也就是本文要讲的“三大范式”。

第一范式：每个列不可再拆分
第二范式：在第一范式的基础上，非主键完全依赖于主键，而不是依赖于主键的一部分
第三范式：在第二范式的基础上，非主键只依赖于主键，不依赖于其他非主键

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项

举例说明：

在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：

可见，调整后的每一列都是不可再分的，因此满足第一范式（1NF）；

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

举例说明：

在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，

但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，

这样就不满足第二范式的要求，调整如下，需分成两个表：

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

举例说明：

上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，

而不是主键“学号”，所以需做如下调整：

这样以来，就满足了第三范式的要求。

ps:如果把上表中的班主任姓名改成班主任教工号可能更确切，更符合实际情况，不过只要能理解就行。

11、MyISAM和InnoDB区别

	MyISAM	InnoDB
存储结构	frm-表格定义、MYD（MYData）-数据文件、MYI（MYINDEX）-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或是独立的表空间文件）表大小受限于操作系统文件的大小，一般为2GB
存储空间	可被压缩，存储空间小	表需要更多的存储空间，他会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复	由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式	*.MYI、 *.MYD	表结构存在*.frm文件，数据和索引都是集中存储的*.ibd
外键	不支持	支持
事务	不支持	支持
锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的）	表级锁定	行级锁定、表级锁定，锁定力度小 并发能力高
SELECT	MyISAM更优
INSERT、UPDATE、DELETE		InnoDB更优
索引的实现方式	B+树索引，MyISAM表	B+树索引、InnoDB是索引组织表
哈希索引	不支持	支持
全文索引	不支持	支持
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
select count(*)	MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

索引	InnoDB	MyISAM	Memory
BTREE	支持	支持	支持
HASH	不支持	不支持	支持
R-tree	不支持	支持	不支持
Full-test	5.6版本后支持	支持	不支持

聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree树（多路搜索树，不一定是二叉的）索引，统称索引

存储引擎	表级锁	行级锁	页面锁
MyISAM	支持	不支持	不支持
InnoDB	支持	支持	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持