MySQL数据库基本用法(万字总结，一步到位)

一、概念知识

（一）数据库分类

1.关系型数据库

采用了关系模型来组织数据的存储，以行和列的形式存储数据并记录数据与数据之间的关系

MySQL 免费
Oracle 收费
PostgreSQL
SQL Server
Access
Sybase
SQLite

2.非关系型数据库

采用键值对的模型来存储数据，只完成数据的记录，不会记录数据与数据之间的关系。

面向检索的列式存储 Column-Oriented
	HaBase （Hadoop子系统）
	BigTable （Google）
面向高并发的缓存存储Key-Value
	Redis
	MemcacheDB
面向海量数据访问的文档存储 Document-Oriented
	MongoDB
	CouchDB

（二）三大范式（实际有五个）

1.定义与作用

是创建数据库与表的标准与规范，能够指导我们正确合理创建数据库与表，从而保证程序的正确运行。

2.1-5NF

1）1NF

创建的表的字段不可再分，简称为字段不可分

如：联系人表（姓名，性别，电话）
如果电话又分为
家庭电话，公司电话
这样就不满足了

2）2NF

满足第一范式外，还要求每一条记录要由一个主键字段来惟一完全标识（确定），不能是部分标识（确定）

如：选课表（学号，姓名，年龄，课程号，成绩，学分）
（学号，课程号）->（姓名，年龄，成绩，学分）
但是又存在
（学号）->（姓名，年龄）
（课程号）->（学分）
非主属性不能依赖全部主键

解决方法：
学生：Student(学号,姓名,年龄);
课程：Course(课程名称,学分);
选课关系：SelectCourse(学号,课程名称,成绩);

3）3NF

满足第二范式外，创建的表中的字段不能有传递性依赖

如：学生表（学号，姓名，年龄，学院编号,学院地点，学院电话）
学号->学院编号->学院地址

解决方法：
学生：(学号,姓名,年龄,学院编号);
学院：(学院编号,学院地点,学院电话);

4）BCNF

在第三范式的基础上进一步消除主属性对于码的部分函数依赖和传递依赖。BCNF需要符合3NF，并且，主属性不依赖于主属性

如：仓库管理表(仓库ID,存储物品ID,管理员ID,数量)且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品
(仓库ID,存储物品ID) -> (管理员ID,数量)
(管理员ID,存储物品ID) -> (仓库ID,数量)
但存在关键字决定关键字
(仓库ID) -> (管理员ID)
(管理员ID) -> (仓库ID)

解决方法：
仓库管理：(仓库ID,管理员ID);
仓库：(仓库ID,存储物品ID,数量);

5）4NF

表中字段要消除非平凡函数依赖

6）5NF/完美范式/最终范式

消除了4NF的连接依赖，解决正确分表的问题，根据生活经验或项目经验分表

（三）各种键与码

1.码=超键

不能为空

能唯一标识一条记录的属性或属性集

码=超键=候选键+[其他属性]

2.候选键=候选码

能够唯一标识一条记录的最小属性集

任一候选键的任何真子集都不能唯一标识一个记录（比如在成绩表中（学号,课程号）是一个候选键，单独的学号，课程号都不能决定一条记录）

不能为空

候选键是没有多余属性的超键举例：学生ID是候选码，那么含有候选码的都是码。

3.主键=主码

某个能够唯一标识一条记录的最小属性集（是从候选码里人为挑选的一条）

一个数据表只能有一个主键

不能为空

人为的选取某个候选码为主码（也就是说有好多候选码，选一个定它为主键）

4.主属性/非主属性

主属性 包含在任一候选码中的属性称主属性。简单来说，主属性是候选码所有属性的并集

非主属性 不包含在候选码中的属性称为非主属性。非主属性是相对于主属性来定义的。

5.外键

子数据表中出现的父数据表的主键，称为子数据表的外键。

6.全码

当所有的属性共同构成一个候选码时，这时该候选码为全码。

7.代理键

当不适合用任何一个候选键作为主键时（如数据太长等），添加一个没有实际意义的键作为主键，这个键就是代理键。（如常用的序号1、2、3）

8.自然键

自然生活中唯一能够标识一条记录的键（如身份证）

9.举例说明

学生成绩信息表中有（学号、姓名、性别、年龄、系别、专业等）

超键/码：
  由于学号能确定一个学生，因此学生表中含有学号的任意组合都为此表的超键。如：（学号）、（学号，姓名）、（学号，性别）等

 若我们假设学生的姓名唯一，没有重名的现象。

      学号唯一，所以是一个超键
      姓名唯一，所以是一个超键
     （姓名，性别）唯一，所以是一个超键
     （姓名，年龄）唯一，所以是一个超键
     （姓名，性别，年龄）唯一，所以是一个超键

候选键：
   学号唯一，而且没有多余属性，所以是一个候选键

   姓名唯一，而且没有多余属性，所以是一个候选键

  （姓名，性别）唯一，但是单独姓名一个属性就能确定这个人是谁，所以性别这个属性就是多余属性，所以（姓名，性别）不是候选键
  （姓名，年龄），（姓名，性别，年龄）同上，也不是候选键

主键：
  主键就是候选键里面的一个，是人为规定的，例如学生表中，我们通常会让“学号”做主键，学号能唯一标识这一个元组。

外键：
   外键就很简单了，假如我们还有一个教师表，每个教师都有自己的编号，假设老师编号在教师表中是主键，在学生表中它就是外键。

二、语言分类

分类	名称	用途	代表关键字
DDL (Data Definition Language)	数据定义语言	用来定义数据库、表及其它对象的结构	CREATE、DROP、ALTER
DML (Data Manipulation Language)	数据操作语言	用来增加、修改、删除表中的数据	INSERT、DELETE、UPDATE
DQL (Data Query Language)	数据查询语言	用来查询表中的数据	SELECT、FROM、WHERE、ORDER BY 、GROUP BY、HAVING
DCL (Data Control Language)	数据控制语言	用来授予和收回权限	GRANT、REVOKE
TCL (Transaction Control Language)	事务处理语言	用来对数据进行提交和回滚	COMMIT、ROLLBACK

（一）DDL

1.创建

创建数据库方式
1、直接创建数据库
CREATE  DATABASE  数据库名;

2、判断数据库是否存在，如果存在则不创建，不存在则创建。
CREATE  DATABASE  IF  NOT  EXISTS  数据库名;

3、创建数据库并指定字符集。
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
create database 库名 character set 字符集 collate 排序编码;

4、创建表
CREATE TABLE 表名(
列名1  数据类型 ,
列名2  数据类型 ,
…
);

5、CREATE TABLE IF NOT EXISTS 表名(
列名1  数据类型 ,
列名2  数据类型 ,
…
);

字符集	一个字符占字节长度	说明
GBK	2	支持中文，但是不是国际通用字符集
UTF-8	3	支持中英文混合场景，是国际通用字符集
latin1	1	MySQL8版本之前的默认字符集
utf8mb4	4	MySQL8版本的默认字符集，mb4是most bytes 4的缩写。UTF-8的超级，完全兼容UTF-8，用4个字节存储一个字符，因此能存储更多的字符

2.查看

查看数据库方式
1、查看所有的数据库
SHOW  DATABASES;

2、查看某个数据库的定义信息
SHOW  CREATE  DATABASE  数据库名;

3、查看某个数据库中的所有表
SHOW TABLES;

4、查看表结构
DESC 表名;

5、查看创建表的SQL语句
SHOW CREATE TABLE 表名;

3.修改与添加

修改数据库方式
1、修改数据库字符集
ALTER  DATABASE  数据库名  DEFAULT  CHARACTER  SET  字符集;
ALTER  DATABASE  数据库名  DEFAULT  CHARACTER  SET  字符集 collate 排序编码;

修改表结构
1、为已经存在的表添加新列。
ALTER TABLE 表名 ADD 列名 类型;

2、修改已经存在的列的数据类型
ALTER  TABLE  表名  MODIFY  列名 新的类型;

3、修改列名
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;

4、删除列
ALTER TABLE 表名 DROP 列名;

5、修改表名
RENAME TABLE 表名 TO 新表名;

6、修改字符集
ALTER TABLE 表名 character set 字符集;

4.删除

# drop 删除数据库中的对象 数据库、数据表、列、存储过程、视图、触发器、索引....
# delete 删除数据表中的数据

删除数据库方式
1、删除数据库
DROP  DATABASE  数据库名;

删除表
1、直接删除表，语法如下：
DROP TABLE 表名;

2、判断表是否存在并删除表，语法如下：
DROP TABLE  IF  EXISTS 表名;
（表不存在也不会报错）

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

5.切换

切换数据库方式
1、查看正在使用的数据库
SELECT DATABASE();

2、使用/切换数据库
USE 数据库名;

6.复制表

复制表
1、仅复制表结构，语法如下：
CREATE TABLE 新表名 LIKE 旧表名;
(有主键)

2、同时复制表结构和数据，语法如下：
CREATE TABLE 新表名 查询语句;
（没有主键，最好还是自己键表）

7.截断

截断表
截断表的作用是把原来的表摧毁，重新创建一个结构和原来一模一样的新表，语法如下：
TRUNCATE TABLE 表名;(truncate)

TRUNCATE和DELETE区别
1、TRUNCATE是DDL命令，使用ROLLBACK不可以回滚。而DELETE是DML命令，使用ROLLBACK可以回滚。
2、DELETE可以通过指定删除条件实现部分删除，TRUNCATE不能指定条件。

8.重置/修改id

alter table 表名 auto_increment=1000;

9.字段约束

主键约束（primary key）：非空+唯一，能够唯一标识数据表中的一条数据

非空约束（not null）：限制此列的值必须提供，不能为null

唯一约束（unique）：在表中的多条数据，此列的值不能重复

外键约束（foreign key）：建立不同表之间的关联关系

默认约束（default）：该数据的默认值

主键
create table books(
book_isbn char(4) primary key,
book_name varchar(10) not null,
book_author varchar(6)
);
或
create table books(
book_isbn char(4),
book_name varchar(10) not null,
book_author varchar(6),
primary key(book_isbn)
);

删除
alter table books drop primary key;

建表后添加
alter table books modify book_isbn char(4) primary key;

外键
foreing key 字段 references 外表(字段)

（二）DQL

1.比较运算符

=
！=	或	<>
>
<
>=
<=
-- 数据在这两者之间包含等于
between 数据一 and 数据二
-- 从后面的数据中选择符合的
in(值1, 值2, ...)
-- 判断是否为空
is 这个常常与 null 一起用

2.逻辑运算符

and
or
-- NOT运算符还可以和BETWEEN…AND、IN、LIKE、IS NULL一起使用
not

3.算数运算符

+、-、*、/、% mod(取余)

4.模糊查询（不建议多用会扫描整个表）

like
_ 代替一个字符
% 代替多个字符

5.distinct消除重复行

select distinct 字段1,字段2,字段3 from 表;

6.聚合函数/分组函数

sum(); -- 求和
avg(); -- 求平均值
count(); -- 计数
max() / min(); -- 最大/最小值
abs(); -- 求绝对值
sqrt; -- 开方

1）空值处理方法

除了COUNT（*）之外，其它所有分组函数都会忽略列中的空值，然后再进行运算

IFNULL(值1,值2)
值1为需要判断的字段
值2为如果为空进行代替的字段（通常为0）

SELECT AVG(IFNULL(comm,0)) , COUNT(IFNULL(comm,0)) FROM emp;

7.日期函数

获取：
now()、sysdate() 当前系统时间 年月日时分秒
curdae() 当前日期 年月日
curtime()	当前时间 时分秒

设置
date();
date('2001-1-23');
time();
time('9:20:18')

date_format(日期数据， 输出格式)

时间戳
timestamp();

计算年龄
TIMESTAMPDIFF(YEAR, birth, NOW())

8.字符串函数

concat(字段1,字段2,字段3):将多个字段串成一个大的字符串
lower(字段):将字段的英文字符都转成小写
upper(字段):将字段的英文字符都转成大写
substring(字段,起始位置,长度):从大的字符串截取一部分作为一个子串
substr()同 substring

9.排序

SELECT  [ DISTINCT ]  * | 列名 | 表达式 [别名] [,...] 
FROM    表名
[WHERE  条件]
[ORDER BY 列名1  |  列别名1  | 列序号1 [ ASC | DESC ],   列名2  |  列别名2  |  列序号2 [ ASC | DESC ];


order by 列名 [ASC|DESC]; -- 升序降序,默认升序
-- 可以按照3种方式进行排序：分别是按列名排序、按列别名排序、按列序号排序（下标从1开始）
-- 多个用,隔开
-- 放在DQL语句最后

-- 按列序号排序, 按照 deptno 排序
SELEC ename, deptno, sal 
FROM emp 
ORDER BY  2 ;

10.分组

GROUP BY 字段1,字段2,...;
通常和聚合函数一起用
对于分组后的数据要用 having 进行筛选

-- 当有GROUP BY子句时，SELECT子句后面只能写：被分组的列、分组函数，这两类元素才有实际意义。

-- 示例部门编号,工作,工资
SELECT deptno, job, sum(sal) 
FROM emp 
GROUP BY deptno, job;

11.分页

SELECT 字段列表
FROM 数据源
LIMIT [start,]length;

如果只写一个参数的话就是0页几条
两个参数的话
start：代表从第几条开始
length：代表一页有几条数据

-- start的下标是从0开始的

12.关联查询

1）等值查询

等号连接

2）连接查询

①inner join 内连接

select ... from 表1 inner join 表2 on 匹配条件 [where 筛选条件];

# 不建议使用，where设置过滤条件：先生成笛卡尔积再从笛卡尔积中过滤数据（效率很低）
select * from students INNER JOIN classes where students.cid = classes.class_id;
# 建议使用，ON设置连接查询条件：先判断连接条件是否成立，如果成立两张表的数据进行组合生成一条结果记录
select * from students INNER JOIN classes ON students.cid = classes.class_id;

②left join 左连接

# 语法
select * from leftTabel LEFT JOIN rightTable ON 匹配条件 [where 条件];
# 左连接 : 显示左表中的所有记录，右表中的不匹配的显示null
select * from students LEFT JOIN classes ON students.cid = classes.class_id;

③right join 右连接

# 右连接 ：显示右表中的所有记录，左表中的不匹配的显示null
select * from students RIGHT JOIN classes ON students.cid = classes.class_id;

13.子查询/嵌套查询

1）子查询返回单个值（单行单列）

如果子查询返回的结果是一个值（单列单行），条件可以直接使用关系运算符（= != ....）
select * from students where cid = (select class_id from classes where class_name='Java2305');

2）子查询返回多个值（多行单列）

如果子查询返回的结果是多个值（单列多行），条件使用

IN / NOT IN
ANY
ALL

①IN等于其中之一就行

select * from students where cid IN (select class_id from classes where class_name LIKE 'Java%');

②ANY

ANY：表示和子查询的任意一行结果进行比较，有一个满足条件即可。

< ANY：表示小于子查询结果集中的任意一个，即小于最大值就可以。

> ANY：表示大于子查询结果集中的任意一个，即大于最小值就可以。

= ANY：表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN。

③ALL

ALL：表示和子查询的所有行结果进行比较，每一行必须都满足条件。

< ALL:表示小于子查询结果集中的所有行，即小于最小值。

>ALL:表示大于子查询结果集中的所有行，即大于最大值。

= ALL :表示等于子查询结果集中的所有行,即等于所有值，通常无意义。

3）子查询返回多个值（多行多列）

先查询所需信息，将这些信息作为一个整体虚拟表(多行多列)，再基于这个虚拟表查询（‘虚拟表’需要别名）
select * from (select * from students where cid=1) t where t.stu_gender='男';

（三）DML

1.插入数据

1）插入一条

insert into 表名(列名1, 列名2....) values(值1,值2....);

-- 如果插入全部字段可以省略表名后的字段
insert into 表名 values(值1,值2....);

2）插入多条

INSERT INTO 表名 (列名1, 列名2, 列名3…) 
VALUES (值1, 值2, 值3),
(值1, 值2, 值3),
....
(值1, 值2, 值3);

3）通过子查询插入

INSERT INTO 表名 (列名1, 列名2, 列名3…) 查询语句;

-- 不必书写values子句
-- 列名列表中列的位置、列的数量、数据类型，必须和子查询中列的位置、数量和类型相匹配

2.删除数据

delete from 表名 [where 条件];

3.修改数据

update 表名 set 列名1=值1，列名2=值2 [where 条件];

（四）DCL

1.创建用户

CREATE USER 用户名@地址 IDENTIFIED BY '密码';

--创建一个user1用户，密码123456
CREATE USER user1@localhost IDENTIFIED BY '123456';

2.给用户授予权限

GRANT 权限1, … , 权限n ON 数据库.对象  TO 用户名;

--将test数据库中所有对象(表、视图、存储过程，触发器等。*表示所有对象)的--create,alter,drop,insert,update,delete,select赋给user1 用户

grant create,alter,drop,insert,update,delete,select on test.* to user1@localhost;

3.撤销授权

REVOKE权限1, … , 权限n ON 数据库.对象 FORM 用户名;

--将test数据库中所有对象的create,alter,drop权限撤销

revoke create,alter,drop on test.* to user1@localhost;

4.查看用户权限

SHOW GRANTS FOR 用户名;

 --查看testuser的用户权限
 
show grants for user1@localhost;

5.修改用户密码

USE mysql;

UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’;
FLUSH PRIVILEGES;

--将user1的密码改为123123
update user set password=password('123123') where user='user1' and host=’localhost’;
FLUSH PRIVILEGES;

6.删除用户

DROP USER 用户名;

--删除user1用户

drop user user1@localhost;

（五）TCL/事务管理

简单的来说就是想让同一件事里的操作方法写在一块，要么一块都运行，要不就都不运行

1.事务特性（ACID）

原子性（Atomicity）：一个事务中的多个DML操作，要么同时执行成功，要么同时执行失败
一致性（Consistency）：事务执行之前和事务执行之后，数据库中的数据是一致的，完整性和一致性不能被破坏
隔离性（Isolation）：数据库允许多个事务同时执行（张三借Java书的同时允许李四借Java书），多个必行的事务之间不能相互影响
持久性（Durability）：事务完整之后，对数据库的操作是永久的

2.事务的应用

1）提交方式

在MySQL中，默认DML指令的执行时自动提交的，当我们执行一个DML指令之后，自动同步到数据库中

①设置手动/自动提交

SET autocommit = 0;   -- 0设置手动提交，1设置自动提交

②查看是否自动提交

SHOW VARIABLES LIKE  'autocommit';

2）事务管理

开启事务就关闭了自动提交

在开始事务第一个操作之前，执行 start transaction 开启事务

依次执行事务中的每个DML操作

如果在执行的过程中的任何位置出现异常，则执行 rollback 回滚事务

如果事务中所有的DML操作都执行成功，则在最后执行 commit 提交事务

### 借书业务
# 【开启事务】（关闭自动提交---手动提交）
start transaction;

# 操作1：在借书记录表中添加记录
insert into records(snum,bid,borrow_num,is_return,borrow_date)
values('1007',4,2,0,sysdate());

# 操作2：修改图书库存
update books set book_stock=book_stock-2 where book_id=4;
# 【提交事务】（将连接缓存中的操作写入数据文件）
commit;

# 【事务回滚】（清除连接缓存中的操作,撤销当前事务已经执行的操作）
# rollback;

3）特殊情况讲解

START TRANSACTION;
# 第一个修改语句
UPDATE a SET money = money + 100 WHERE name = 'abc';
# 第一个查询语句
SELECT * FROM a;
# 第二个修改语句
# 这句话错了money,少写了一个y
UPDATE a SET money = mone + 100 WHERE name = 'cdd';
# 第二个查询语句
SELECT * FROM a;
# 第三个修改语句
UPDATE a SET money = money + 100 WHERE name = 'ddd';
# 第三个查询语句
SELECT * FROM a;
COMMIT;

# 运行这一块的代码你会发现,第一个查询语句进行了查询,并且输出了我们想要的结果，二三结果因为二的错误并未执行,以为这样就对了并不是
# 实际上第一个修改语句也未真正执行成功,只是在缓存上执行了,查询也是查询的缓存上的内容
# 同时这段代码并未结束,因为未提交,可以再次执行 commit; 这样修改的内容才会写入磁盘
# 如果再次运行这段代码，会发现数据库内容也进行了修改，原因是start transaction;这段代码一执行会把之前的事务进行提交，变相的进行了 commit;

3.事务隔离级别

数据库允许多个事务并行，多个事务之间是隔离的、相互独立的；如果事务之间不相互隔离并且操作同一数据时，可能会导致数据的一致性被破坏。

1）读未提交

读未提交（read uncommitted）：T2可以读取T1执行但未提交的数据；可能会导致出现脏读。脏读：一个事务读取到了另一个事务中未提交的数据

2）读已提交

读已提交（read committed）:T2只能读取T1已经提交的数据；避免了脏读，但可能会导致不可重复度（虚读）。不可重复度（虚读）: 在同一个事务中，两次查询操作读取到数据不一致。例如：T2进行第一次查询之后在第二次查询之前，T1修改并提交了数据，T2进行第二次查询时读取到的数据和第一次查询读取到数据不一致。

3）可重复读

可重复读（repeatable read）：T2执行第一次查询之后，在事务结束之前其他事务不能修改对应的数据；避免了不可重复读(虚读)，但可能会导致幻读。幻读，T2对数据表中的数据进行修改然后查询，在查询之前T1向数据表中新增了一条数据，就导致T2以为修改了所有数据，但却查询出了与修改不一致的数据（T1事务新增的数据）

4）串行化

串行化(serializable)：同时只允许一个事务对数据表进行操作；避免了脏读、虚读、幻读问题

4.设置数据库事务隔离级别

可以通过设置数据库默认的事务隔离级别来控制事务之间的隔离性

可以通过客户端与数据库连接设置来设置事务间的隔离性

MySQL数据库默认的隔离级别为可重复读

1）查看MySQL数据库默认的隔离级别

# 在MySQL8.0.3 之前
select @@tx_isolation;
# 在MySQL8.0.3 之后
select @@transaction_isolation;

2）设置MySQL默认隔离级别

set session transaction isolation level <read committed>;

三、数据类型

MySQL包含3大类数据类型，分别是数值类型、日期和时间类型、字符串类型。

（一）数值类型：

数据类型	描述
BIT	位字段类型，取值范围是1~64，默认为1
TINYINT	很小的整数类型。带符号的范围是-128～127，无符号的范围是0～255
BOOL，BOOLEAN	布尔类型，是TINYINT(1)的同义词。zero值被视为假。非zero值被视为真
SMALLINT	小的整数类型，带符号的范围是-32768～32767。无符号的范围是0～65535
MEDIUMINT	中等大小的整数类型。带符号的范围是-8388608～8388607，无符号的范围是0～16777215
INT	普通大小的整数类型。带符号的范围是-2147483648～2147483647，无符号的范围是0～4294967295
INTEGER	与INT的含义相同
BIGINT	大整数类型，带符号的范围是-9223372036854775808～9223372036854775807。无符号的范围是0～18446744073709551615
FLOAT	单精度浮点类型
DOUBLE	双精度浮点类型
DECIMAL	定点数类型
DEC	与DECIMAL的含义相同

（二）日期和时间类型

数据类型	描述
DATE	日期类型，例如'2012-01-01'
DATETIME	日期和时间类型，例如'2012-01-01 12:00:00'
TIMESTAMP	时间戳类型，TIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间
TIME	时间类型
YEAR	两位或四位的年份类型，默认为四位年份类型

（三）字符串类型

数据类型	描述
CHAR(M)	固定长度字符串，M为存储长度
VARCHAR(M)	可变长度的字符串，M为最大存储长度，实际存储长度为输入字符的实际长度
BINARY(M)	BINARY类型类似于CHAR类型，但保存二进制字节字符串而不是非二进制字符串。M为存储长度
VARBINARY(M)	VARBINARY类型类似于VARCHAR类型，但保存二进制字节字符串而不是非二进制字符串。M为存储长度
BLOB	二进制大对象，包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 等4种BLOB类型
TEXT	大文本类型，包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT等4种TEXT类型
ENUM	枚举类型
SET	集合类型

四、MySQL索引

（一）概念

索引，就是用来提高数据表中数据的查询效率的。是将数据表中某一列/某几列的值取出来构造成便于查找的结构进行存储，生成数据表的目录。

当我们进行数据查询的时候，则先在目录中进行查找得到对应的数据的地址，然后再到数据表中根据地址快速的获取数据记录，避免全表扫描。

（二）索引分类

主键索引：在数据表的主键字段创建的索引，这个字段必须被primary key修饰，每张表只能有一个主键

唯一索引：在数据表中的唯一列创建的索引(unique)，此列的所有值只能出现一次，可以为NULL

普通索引：在普通字段上创建的索引，没有唯一性的限制

组合索引：两个及以上字段联合起来创建的索引

（三）索引的应用

1.创建索引

1）唯一索引

# 创建唯一索引: 创建唯一索引的列的值不能重复
# create unique index <index_name> on 表名(列名);
create unique index index_test1 on tb_testindex(tid);
# 创建索引时,根据索引字段排序
create unique index index_test1 on tb_testindex(tid desc);

2）普通索引

# 创建普通索引: 不要求创建索引的列的值的唯一性
# create index <index_name> on 表名(列名 排序)，根据索引字段升序排序
create index index_test2 on tb_testindex(name asc);

3）组合索引

# 创建组合索引
# create index <index_name> on 表名(列名1,列名2...);
create index index_test3 on tb_testindex(tid,name);

4）全文索引

MySQL 5.6 版本新增的索引，可以通过此索引进行全文检索操作，因为MySQL全文检索不支持中文，因此这个全文索引不被开发者关注，在应用开发中通常是通过搜索引擎（数据库中间件）实现全文检索

一般不建议使用，只用MyISAM引擎支持，innoDB5.7后支持

create fulltext index <index_name> on 表名(字段名);

2.使用索引

索引创建完成之后无需调用，当根据创建索引的列进行数据查询的时候，会自动使用索引；

组合索引需要根据创建索引的所有字段进行查询时触发。

在命令行窗口中可以查看查询语句的查询规划：

--例如：
explain select * from tb_testindex where tid=250000;

3.查看索引

# 命令行
show create table tb_testindex;

# 查询数据表的索引
show indexes from tb_testindex;

# 查询索引
show keys from tb_testindex;

4.删除索引

# 删除索引：索引是建立在表的字段上的，不同的表中可能会出现相同名称的索引，因此删除索引时需要指定表名
drop index 索引名 on 表名;

（四）注意事项

优点

索引大大降低了数据库服务器在执行查询操作时扫描的数据，提高查询效率

索引可以避免服务器排序、将随机IO编程顺序IO

缺点

索引是根据数据表列的创建的，当数据表中数据发生DML操作时，索引页需要更新

索引文件也会占用磁盘空间

注意

1.数据表中数据不多时，全表扫面可能更快吗，不要使用索引
2.数据量大但是DML操作很频繁时，不建议使用索引
3.不要在数据重复读高的列上创建索引（性别）
4.创建索引之后，要注意查询SQL语句的编写，避免索引失效

五、MySQL视图

（一）概念

视图，就是由数据库中一张表或者多张表根据特定的条件查询出得数据构造成得虚拟表

（二）视图作用

安全性：如果我们直接将数据表授权给用户操作，那么用户可以CRUD数据表中所有数据，加入我们想要对数据表中的部分数据进行保护，可以将公开的数据生成视图，授权用户访问视图；用户通过查询视图可以获取数据表中公开的数据，从而达到将数据表中的部分数据对用户隐藏。

简单性：如果我们需要查询的数据来源于多张数据表，可以使用多表连接查询来实现；我们通过视图将这些连表查询的结果对用户开放，用户则可以直接通过查询视图获取多表数据，操作更便捷。

（三）视图的应用

1.创建视图

create view 视图名字
AS
查询语句

2.查询视图

# 查询视图结构
desc 视图名字;

3.修改视图

这个跟重新创建没什么区别

# 方式1
create OR REPLACE view view_test1
AS
select * from students where stu_gender='女';
# 方式2
alter view view_test1
AS
select * from students where stu_gender='男';

增删

INSERT INTO
DELETE FROM
# 这个只适合单表生成的视图，如果多表无法进行修改

改

UPDATE SET
# 无论是单表生成的还是多表生成的都可以进行修改

（四）视图特性

视图是虚拟表，查询视图的数据是来源于数据表的。当对视图进行操作时，对原数据表中的数据是否由影响呢？

# 查询操作：如果在数据表中添加了新的数据，而且这个数据满足创建视图时查询语句的条件，通过查询视图也可以查询出新增的数据；当删除原表中满足查询条件的数据时，也会从视图中删除。

# 新增数据：如果在视图中添加数据，数据会被添加到原数据表

# 删除数据：如果从视图删除数据，数据也将从原表中删除

# 修改操作：如果通过修改数据，则也将修改原数据表中的数据

六、MySQL存储过程

个人理解：就是一个函数，被调用就执行

（一）存储过程的介绍

1.作用

将能够完成特定功能的SQL指令进行封装（SQL指令集），编译之后存储在数据库服务器上，并且为之取一个名字，客户端可以通过名字直接调用这个SQL指令集，获取执行结果。

2.优点

SQL指令无需客户端编写，通过网络传送，可以节省网络开销，同时避免SQL指令在网络传输过程中被恶意篡改保证安全性

存储过程经过编译创建并保存在数据库中的，执行过程无需重复的进行编译操作，对SQL指令的执行过程进行了性能提升

存储过程中多个SQL指令之间存在逻辑关系，支持流程控制语句（分支、循环），可以实现更为复杂的业务

存储过程中可以使用事务管理，避免了数据的不一致或错误的问题

3.缺点

存储过程是根据不同的数据库进行编译、创建并存储在数据库中；当我们需要切换到其他的数据库产品时，需要重写编写针对于新数据库的存储过程

存储过程受限于数据库产品，如果需要高性能的优化会成为一个问题

在互联网项目中，如果需要数据库的高（连接）并发访问，使用存储过程会增加数据库的连接执行时间（因为我们将复杂的业务交给了数据库进行处理）

（二）存储过程的应用

1.创建存储过程

# 语法:
create procedure 存储过程名称 ([IN / OUT / INOUT args])
begin
-- SQL
end;

2.调用存储过程

# 调用存储过程
# 定义变量@m
set @m = 0;
-- 调用存储过程，将3传递给a，将2传递给b，将@m传递给c
call proc_test1(3,2,@m);
-- 显示变量值
select @m from dual;

3.存储过程中变量的使用

1）定义局部变量

局部变量：定义在存储过程中的变量，只能在存储过程内部使用

# 局部变量要定义在存储过程中，而且必须定义在存储过程开始
declare <attr_name> <type> [default value];


# 样例
create procedure proc_test2(IN a int,OUT r int)
begin
declare x int default 0; -- 定义x int类型，默认值为0
declare y int default 1; -- 定义y
set x = a*a;
set y = a/2;
set r = x+y;
end;

2）定义用户变量

用户变量：相当于全局变量，定义的用户变量可以通过 select @attrName from dual 进行查询

因为用户变量相当于全局变量，可以在SQL指令以及多个存储过程中共享，在开发中建议尽量少使用用户变量，用户变量过多会导致程序不易理解、难以维护。

# 用户变量会存储在mysql数据库的数据字典中（dual）
# 用户变量定义使用set关键字直接定义，变量名要以@开头
set @n=1;

3）将查询结果赋值给变量

在存储过程中使用select..into..给变量赋值

# 查询学生数量
create procedure proc_test3(OUT c int)
begin
select count(stu_num) INTO c from students; -- 将查询到学生数量赋值给c
end;

# 调用存储过程
call proc_test3(@n);
select @n from dual;

4.存储过程中的参数

1）IN 输入参数

输入参数：在调用存储过程中传递数据给存储过程的参数

# 创建存储过程：添加学生信息
create procedure proc_test4(IN snum char(8),IN sname varchar(20), IN gender
char(2), IN age int, IN cid int, IN remark varchar(255))
begin
insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark)
values(snum,sname,gender,age,cid,remark);
end;


call proc_test4('20210108','小丽','女',20,1,'aaa');

2）OUT 输出参数

输出参数：将存储过程中产生的数据返回给过程调用者，相当于Java方法的返回值，但不同的是一个存储过程可以有多个输出参数

# 创建存储过程，根据学生学号，查询学生姓名
create procedure proc_test5(IN snum char(8),OUT sname varchar(20))
begin
select stu_name INTO sname from students where stu_num=snum;
end;

set @name='';
call proc_test5('20210108',@name);
select @name from dual;

3）INOUT 输入输出参数

既输入又输出

create procedure proc_test6(INOUT str varchar(20))
begin
select stu_name INTO str from students where stu_num=str;
end;

set @name='20210108';
call proc_test6(@name);
select @name from dual;

5.存储过程中的流程控制

1）分支语句

①if-then-else

# 单分支：如果条件成立，则执行SQL
if conditions then
-- SQL
end if;

# 如果参数a的值为1，则添加一条班级信息
create procedure proc_test7(IN a int)
begin
    if a=1 then
    	insert into classes(class_name,remark) values('Java2109','test');
    end if;
end;



# 双分支：如果条件成立则执行SQL1，否则执行SQL2
if conditions then
-- SQL1
else
-- SQL2
end if;

# 如果参数a的值为1，则添加一条班级信息；否则添加一条学生信息
create procedure proc_test7(IN a int)
begin
    if a=1 then
        insert into classes(class_name,remark) values('Java2109','test');
    else
        insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark)
        values('20210110','小花','女',19,1,'...');
    end if;
end;

②case

# case
create procedure proc_test8(IN a int)
begin
    case a
        when 1 then
        -- SQL1 如果a的值为1 则执行SQL1
        insert into classes(class_name,remark) values('Java2110','wahaha');
        when 2 then
        -- SQL2 如果a的值为2 则执行SQL2
        insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark)
        values('20210111','小刚','男',21,2,'...');
    else
        -- SQL (如果变量的值和所有when的值都不匹配，则执行else中的这个SQL)
        update students set stu_age=18 where stu_num='20210110';
    end case;
end;

2）循环语句

①while

# while
create procedure proc_test9(IN num int)
begin
    declare i int;
    set i = 0;
    while i<num do
        -- SQL
        insert into classes(class_name,remark) values( CONCAT('Java',i)
        ,'....');
        set i = i+1;
    end while;
end;

call proc_test9(4);

②repeat

# repeat
create procedure proc_test10(IN num int)
begin
    declare i int;
    set i = 1;
    repeat
        -- SQL
        insert into classes(class_name,remark) values( CONCAT('Python',i),'....');
        set i = i+1;
	until i > num end repeat;
end;
    
call proc_test10(4);

③loop

# loop
create procedure proc_test11(IN num int)
begin
    declare i int ;
    set i =0;
    myloop:loop
        -- SQL
        insert into classes(class_name,remark) values( CONCAT('HTML',i),'....');
        set i = i+1;
        if i=num then
            leave myloop;
        end if;
    end loop;
end;

call proc_test11(5);

6.查询存储过程

存储过程是属于某个数据库的，也就是说当我们将存储过程创建在某个数据库之后，只能在当前数据库中调用此存储过程。

# 根据数据库名，查询当前数据库中的存储过程
show procedure status where db='数据库名';
# 查询存储过程的创建细节
show create procedure 数据库名.表名;

7.修改存储过程

修改存储过程指的是修改存储过程的特征/特性

alter procedure <proc_name> 特征1 [特征2 特征3 ....]

存储过程的特征参数：

CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句

NO SQL 表示子程序中不包含 SQL 语句

READS SQL DATA 表示子程序中包含读数据的语句

MODIFIES SQL DATA 表示子程序中包含写数据的语句

SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行

DEFINER 表示只有定义者自己才能够执行

INVOKER 表示调用者可以执行

COMMENT 'string' 表示注释信息

alter procedure proc_test1 READS SQL DATA;

8.删除存储过程

# 删除存储过程

drop procedure proc_test1;

9.游标

1）游标的概念

游标可以用来依次取出查询结果集中的每一条数据——逐条读取查询结果集中的记录

2）游标的使用步骤

①声明游标

declare mycursor cursor for select book_name,book_author,book_price from books;

②打开游标

open mycursor;

③使用游标，提取游标当前指向的记录（提取之后，游标自动下移）

fetch mycursor into bname,bauthor,bprice;#定义新的属性名

④关闭游标

close mycursor;

3）示例

# 游标使用案例
create procedure proc_test2(OUT result varchar(200))
begin
    declare bname varchar(20);
    declare bauthor varchar(20);
    declare bprice decimal(10,2);
    
    declare num int;
    declare i int;
    declare str varchar(50);
    
    -- 此查询语句执行之后返回的是一个结果集（多条记录），使用游标可以来遍历查询结果集
    declare mycursor cursor for select book_name,book_author,book_price
    from books;
    
    select count(1) INTO num from books;
    -- 打开游标
    open mycursor;
        -- 使用游标要结合循环语句
        set i=0;
        while i<num do
            -- 使用游标：提取游标当前指向的记录（提取之后，游标自动下移）
            FETCH mycursor INTO bname,bauthor,bprice;
            set i=i+1;
            -- set str=concat_ws('~',bname,bauthor,bprice);
            select concat_ws('~',bname,bauthor,bprice) INTO str;
            set result = concat_ws(',',result,str);
        end while;
    -- 关闭游标
    close mycursor;
end;

# 案例测试
set @r = '';
call proc_test2(@r);
select @r from dual;

七、MySQL触发器

触发器，就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动调用。

在MySQL，只有执行insert\delete\update操作才能触发触发器的执行。(select写上去会报错)

（一）触发器的使用

1.创建触发器

create trigger tri_name
<before|after> -- 定义触发时机
<insert|delete|update> -- 定义DML类型
ON <table_name> -- 表名主动表
for each row -- 声明为行级触发器（只要操作一条记录就触发触发器执行
BEGIN
	sql_statement -- 触发器操作DML语句，不可写select语句
END;

# 只有一条触发应该是可以忽略  BEGIN 与 END

2.查看触发器

show triggers;
or
SHOW TRIGGERs FROM 数据库;

3.删除触发器

drop trigger 触发器名字;

4.案例：

# 学生信息表
create table students(
stu_num char(4) primary key,
stu_name varchar(20) not null,
stu_gender char(2) not null,
stu_age int not null
);

# 学生信息操作日志表
create table stulogs(
id int primary key auto_increment,
time TIMESTAMP,
log_text varchar(200)
);

# 当向students表中添加学生信息时，同时要在 stulogs表中添加一条操作日志
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1004','夏利','女',20);

# 手动进行记录日志
insert into stulogs(time,log_text) values(now(),'添加1004学生信息');

# 创建触发器：当学生信息表发生添加操作时，则向日志信息表中记录一条日志
create trigger tri_test1
after insert on students
for each row
insert into stulogs(time,log_text) values(now(), concat('添加',NEW.stu_num,'学生信息'));

# 测试1：添加一个学生信息，触发器执行了一次
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1005','小明','男',20);
# 测试2：一条SQL指令添加了2条学生信息，触发器就执行了2次
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1006','小刚','男',20),('1007','李磊','男',20);

（二）NEW与OLD

触发器用于监听对数据表中数据的insert、delete、update操作，在触发器中通常处理一些DML的关联操作；我们可以使用 NEW 和 OLD 关键字在触发器中获取触发这个触发器的DML操作的数据

NEW : 在触发器中用于获取insert操作添加的数据、update操作修改后的记录

OLD：在触发器中用于获取delete操作删除前的数据、update操作修改前的数据

1.NEW

1）insert操作中：NEW表示添加的新记录

create trigger tri_test1
after insert on students
for each row
insert into stulogs(time,log_text) values(now(), concat('添加',NEW.stu_num,'学生信息'));

2）update操作中：NEW 表示修改后的数据

# 创建触发器 : 在监听update操作的触发器中，可以使用NEW获取修改后的数据
create trigger tri_test2
after update on students for each row
insert into stulogs(time,log_text) values(now(), concat('修改学生信息为：',NEW.stu_num,NEW.stu_name));

2.OLD

1）delete操作中：OLD表示删除的记录

create trigger tri_test3
after delete on students for each row
insert into stulogs(time,log_text) values(now(), concat('删除',OLD.stu_num,'学生信息'));

2）update操作中：OLD表示修改前的记录

create trigger tri_test2
after update on students for each row
insert into stulogs(time,log_text) values(now(), concat('将学生姓名从【',OLD.stu_name,'】修改【',NEW.stu_name,'】'));