MySQL知识点全面整理

1.SQL语言分类

• DDL 数据库定义语言
• DML 数据库操作语言
• DCL 数据库控制语言
• DQL 数据库查询语言

2.数据库设计范式

数据库设计只需要满足第三范式：

• 第一范式
  表中每一列都具有原子性，不可分割；eg：不能在联系方式列中同时存邮箱和电话。

• 第二范式
  在第一范式的基础上，需要满足：(1)如果表是单主键，那么主键以外的列必须完全依赖于主键；（2）如果表是联合主键，那么主键以外的列必须完全依赖于联合主键。
  eg：订单ID和商品ID作为联合主键。

• 第三范式
  在第二范式的基础上，表中的非主键列必须和主键列直接相关，即非主键列之间不能相关依赖。

3.MySQL数据类型

• 整数类型
  TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT

• 浮点数类型和定点数类型
  FLOAT 和 DOUBLE；DECIMAL

• 字符串类型
  CHAR(m)：m是字符串最大长度，不管插入实际值是多少，所占存储空间都是m个字节。
  VARCHAR(m)：数据所占用的字节数为实际长度加1。

• 文本类型
  TINYTEXT，MEDIUMTEXT，TEXT，LONGTEXT表示大文本数据

• 日期和时间类型
  YEAR、DATE、TIME、DATETIME和TIMESTAMP

• 二进制类型
  常用BLOB存储二进制类型的数据，图片，pdf文档等。

4.数据库和数据表的基本操作

4.1. 数据库相关操作：

# 删除数据库
drop database  db1;
# 创建数据库
create database db1;
# 显示数据库
show create database db1 ;
# 修改数据库字符集
alter database db1 character set gbk;
# 切换数据库
use db1;

4.2. 表的约束–表中数据的限制条件

• 主键约束primary key

# 设置主键
学号 VARCHAR(100) primary key auto_increment,

# 设置联合主键
primary key(学号,姓名),

# alter设置主键约束
alter table student add primary key(学号);

• 外键约束foreign key

# 设置非空约束
alter table 从表名 add constraint 外键名(从表外键字段) references 主表(主键字段);

alter table 从表名 add constraint 外键名 foreign key(从表外键字段) references 主表(主键字段);
 

• 非空约束not

# 设置非空约束
姓名 VARCHAR(100) not null,

• 唯一性约束

# 设置唯一性约束
学号 VARCHAR(100) unique,

• 默认值约束

# 设置默认值约束
性别 VARCHAR(100) default '女',

4.3. 数据表相关操作：创建表修改表

# 切换数据库
use mybatisdatabase;

# 创建表
drop table if exists student;
create table student(
	学号 VARCHAR(100),
	姓名 VARCHAR(100),
	出生日期 VARCHAR(100),
	性别 VARCHAR(100)
);

# 更新数据
update student(学号,姓名,出生日期,性别) values ('0001' , '一一' , '1989-01-01' , '男');
insert into student(学号,姓名,出生日期,性别) values ('0002' , '二二' , '1990-12-21' , '女');

# 查看表
show create table student;
# 查看表的字段
describe student;

# 修改表的字符集
alter table student character set utf8;

# 修改表名
alter table student rename to 学生表;
alter table 学生表 rename to student;

# 修改字段名
alter table student change 学号 id  varchar(100);
alter table student change id 学号  varchar(100);

# 修改字段数据类型
alter table student modify 学号 int;
alter table student modify 学号 varchar(100);

# 新增字段
alter table student add 新字段 int after 姓名;
# 删除字段
alter table student drop 新字段;

4.3. 增删改

# 插入数据
insert into student(学号,姓名,出生日期,性别) values ('0001' , '一一' , '1989-01-01' , '男');
insert into student(学号,姓名,出生日期,性别) values ('0002' , '二二' , '1990-12-21' , '女');
insert into student(学号,姓名,出生日期,性别) values ('0003' , '三三' , '1995-01-01' , '男');
insert into student(学号,姓名,出生日期,性别) values ('0004' , '四四' , '1996-01-01' , '女');

# 更新一条记录
update student set 姓名='aa',性别='女' where 学号='0001';
# 更新所有记录
update student set 性别 = '男';

# 删除数据，delete和truncate的区别在于，delete可以使用where子句删除部分记录
# truncate语句只能用于删除表中所有记录；delete是DML语句，truncate属于DDL语言
# 删除记录
delete from student where 学号='0001';
# 删除表中所有记录
delete from student;

# 删除表所有记录
truncate table student;

4.4 聚合函数–多入一出

所谓聚合，就是将多行汇总成一行；其实，所有聚合函数均如此——输入多行，输出一行。
例如，统计某个字段的最大值、最小值、平均值等。为此，MySQL中提供了聚合函数来实现这些功能。

• count()
  统计表中数据的行数或列值不为null的个数，*代表数据表中所有的行与列。

# 统计表中值不为null的数据个数
select count(*) from student;

• max()、min()、sum()、avg()
  计算指定列的最大值，最小值，数值和，平均值

# 统计表中age字段的最大值
select max(age) from student;
# 统计表中age字段的最小值
select min(age) from student;
# 统计表中age字段的最小值
select sum(age) from student;
# 统计表中age字段的最小值
select avg(age) from student;

• 其他函数
  时间函数，字符串函数，数学函数

4.5 单表查询

• 简单查询：

# 简单查询
select 学号,性别 from student where 姓名='一一';
select * from student;

# 从查询结果中删除重复数据，只作用于一个列名
select distinct 性别 from student;

• 条件查询：where子句

# 条件
select 学号,性别 from student where 姓名='一一';

• in关键字：判断字段是否在指定集合中

select 学号,性别 from student where 学号 in ('0001','0002','0003');
# 判断字段是否不在指定集合中
select 学号,性别 from student where 学号 not in ('0001','0002','0003');

• BETWEEN AND关键字：判断字段是否在指定范围

select 学号,性别,姓名 from student where 学号 between '0001' and '0003';

• AND 和 OR关键字

# 条件与
select 学号,性别,姓名 from student where 学号>'0002' and 性别='女';
# 条件或
select 学号,性别,姓名 from student where 学号>'0002' or 性别='女';

• LIKE关键字：判断两个字符串是否相匹配

# 匹配整个字符串
select 学号,性别,姓名,出生日期 from student where 姓名 like '一一';

# %用于匹配任意长度的字符串，匹配以'一'结尾的姓名
select 学号,性别,姓名,出生日期 from student where 姓名 like '%一';

# _用于匹配单个字符
select 学号,性别,姓名,出生日期

• LIMIT限制查询结果的数量

# 查询学生表中前5位学生
select * from student limit 5;

# 查询学生表中年纪最小的3位学生
select * from student order by age asc limit 3;

• GROUP BY分组查询
  GROUP BY的数据不再是一行一行的单条数据，而是一个组的概念！

# 统计男女的数量
select count(*) 性别 from student group by 性别;

• ORDER BY对查询结果排序

# 查询学号大于'0002'的学生并将结果升序排列
select * from student where 学号>'0002' order by 学号 asc;

# 查询学号大于'0002'的学生并将结果降序排列
select * from student where 学号>'0002' order by 学号 desc;

4.6 多表连接查询

4.6.1 内连接(Inner Join)

内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据，组合成新的记录。内连接查询中只有满足条件的记录才能出现在查询结果中。

select 字段 from 主表 inner join 从表 on 主表和从表的查询条件 ;


# 带限制条件的内连接
select employee.name,employee.eage,department.dname from department inner join employee on department.did = employee.departmentid where employee.eage>20 order by employee.eage desc;

4.6.2 左外连接(left Join)

返回包括左表中的所有记录和右表中符合连接条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为空。
eg：查询每个部门的部门id，部门名称以及该部门的所有员工姓名：

select department.did, department.dname, employee.ename from department left join employee on department.did = employee.departmentid;

4.6.3 右外连接(right Join)

返回包括右表中的所有记录和左表中符合连接条件的记录。如果右表中的某条记录在坐标中不存在则在右表中显示为空。
eg:查询每个部门的id，部门名称以及该部门所有员工的名字：

select department.did,department.name,employee.ename from department right join employee on department.did = employee.departmentid;

4.6.4 多对多查询

例如，老师表teacher和学生表student之间可以建立一个老师学生关系表，通过这个中间表，进行多对多查询。
eg：查询名叫luck的学生有几位老师：

select * from teacher where teacherid in (select from teacher_student_relation where sid = (select studentid from student where studentname='lucy'));

4.6.5 子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询；

• 带比较运算符的子查询

# 查询yiyi同学所在班级的信息
select * from class where cid = (select classid from student where sname='yiyi');

• 带[NOT] IN关键字的子查询

# 查询年纪大小为21的学生所在班级信息
select * from class where cid in(select classid from student where sage=21)

• 带exists关键字的子查询
  EXISTS关键字后面的参数可以是任意一个子查询， 它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会执行。

# 假若yiyi同学在student表中，则从班级表中查询所有信息
select * from class where exists (select classid from student where sname='yiyi')

• 带any关键字的子查询
  any关键字表示子查询满足其中任意一个条件就返回一个结果作为外层查询条件。

# 查询比任一学生所属班级号还大的班级编号
select * from class where cid > any (select classid from student)

• 带all关键字的子查询
  ALL关键字表示子査询需同时满足所有内层査询条件。

# 查询比任一学生所属班级号还大的班级编号
select * from class where cid > all (select classid from student)

5. 数据库事务及其隔离级别

事务是针对数据库的一组操作(由一条或多条SQL语句组成)进行管控。事务中的语句要么都执行，要么都不执行。

5.1 事务的特性ACID

• 原子性
  事务具有原子性，事务中的语句要么都执行，要么都不执行。

• 一致性
  事务执行前后，数据库的一致性状态保持不变，比如，用户A和用户B账户的钱一共1000，经过多次转账，事务结束后，用户A和用户B账户的钱总和仍是1000。

• 隔离性
  多个并发事务之间要隔离，即多个并发事务之间必须同步执行。不能同时操作同一个表。

• 持久性
  事务一旦提交，其所作的修改就会被持久到数据库中。

5.2 事务的隔离级别

通常情况下数据库是被多线程并发访问的，所以很容易出现多个线程同时开启事务的情况。在这种情况下，可能出现脏读，重复读，幻读。

• 脏读：事务A对数据库进行了修改，事务B读到了修改的数据，但事务A之后进行了回滚。
• 不可重复读：在事务A中，多次读同一数据，事务B在事务A两次读取数据之间更新了数据，导致事务A前后两次读到的数据不相同。不可重复读是指事务中两次査询的结果不一致，原因是在査询的过程中其它事务做了更新等操作。
• 幻读：事务A第一次读取数据后，事务B对数据进行了新增或者删除，事务A再次读取事务，发现和前面读取的不相符，就产生了幻读。
• 不可重复读与幻读之间的区别在于：不可重复读的重点是数据的修改，幻读的重点是数据的新增或者删除。

为了避免这些情况，需要为事务设置隔离级别。MySQL有4种隔离级别。

1. 读未提交(事务最低级别)
   该级别下，事务可以读到另一个事务未提交的数据，即脏读。该级别太低，所以在实际开发中极少使用。
2. 读已提交
   大多数数据库(Oracle)的默认事务隔离级别，该级别下的事务只能读取其他事务已经提交的内容，可以避免脏读但不能避免不可重复读和幻读。
3. 可重复读
   MySQL的默认事务隔离级别，可以避免脏读和不可重复读，避免幻读。
4. 可串行化
   执行完一个事务后才可以执行其他事物，事务之间是同步执行的。这个级别可以解决脏读，不可重复读，幻读，但是会存在大量的锁竞争和超时，实际中很少使用。

6. 存储过程及游标

6.1 存储过程

实际开发中，会遇到经常需要重复使用某一功能的情况，MySQL中引入存储过程，存储过程是指一条或多条SQL语句的集合，存储过程将多条SQL语句封装为一个代码块，便于重复使用，提高开发效率。

SQL语句需要先编译然后执行，存储过程时将完成特定功能的SQL语句集编译后存储在数据库中，用户可以通过传参的方式对存储过程进行调用，存储过程看起来像是函数。

编写存储过程：

• in：表示输入参数，调用时必须输入
• out ： 表示输出参数，可在存储过程中改变后返回
• 存储过程中，可使用流程块。

delimiter //
# 创建存储过程
create procedure procedureHelloWorld(in sage int)
# begin和end之间是存储过程体
begin
select * from student where age>sage;
end
delimiter ;


# 调用存储过程
call procedureHelloWorld(15);

利用**DELIMITER //**把分隔符还原为默认分隔符; 。使得编译器不把存储过程识别为普通SQL语句进行编译。

6.2 游标–从结果集中每次提取一条

游标提供了一种对从表中检索出的数据进行操作的灵活手段。就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

# 定义游标
declare studentCursor cursor for select * from student;
# 打开游标
open studentCursor;
# 使用游标获取列的值
Fetch next from studentCursor into studentID,studentName,studentAge;
# 显示结果
select studentID,studentName,studentAge,studentGender;
# 关闭游标
close studentCursor;
end

6.3 视图–虚拟的表

视图是从一个或多个表中导出的表，是一种虚拟存在的表，可对视图进行表操作，通过视图更新数据时其实是在更新基本表中的数据。相对于直接操作基本表，视图具有以下优点：

• 简化查询语句
• 安全：有助于限制对特定用户的数据访问
• 逻辑数据独立性，屏蔽了真实表结构。

6.3.1 在单表上建立视图

AS：表示指定视图要执行的操作。

# 创建视图
create view if exists view_student;
create view view_student as select math,chinese from student;

# 查看视图
select * from view_student;

6.3.2 在多表上建立视图

AS：表示指定视图要执行的操作。

# 创建视图
drop view if exists view_student_studentInfo;

create view view_student_studentInfo(id,name,province,fname.mname) as select student.sid,student.sname,studentInfo.province,studentInfo.fatherName,studentInfo.motherName from student inner join studentInfo on student.sid=studentInfo.sid;
# 查看视图
select * from view_student_studentInfo;

7 索引

数据库查询分为顺序查询和索引查询，索引查询需要建立索引，加快数据表的查询和排序，但存储索引需要占据一定磁盘空间，创建和维护索引消耗的时间随着数据量的增加而增加。

7.1 MySQL索引分类

• 普通索引
  由KEY或INDEX定义，它可以创建在任何数据类型的字段上。
• 唯一性索引
  由UNIQUE定义，该索引所在字段的值必须是唯一的。
• 全文索引
  由FULLTEXT定义，它只能创建在CHAR、VARCHAR或 TEXT类型的字段上。
• 单列索引
  单列索引指的是在表中单个字段上创建索引，它可以是普通索引、唯一性索引或者全文索引，只要保证该索引只对应表中一个字段即可。
• 多列索引
  多列索引指的是在表中多个字段上创建索引，只有在查询条件中使用了这些字段中的第一个字段时，该索引才会被使用。例如，在student表的id、name和score字段上创建一个多列索引；那么，只有查询条件中使用了 id字段时该索引才会被使用。
• 空间索引
  空间索引由SPATIAL定义，它只能创建在空间数据类型的字段上。 MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和 POLYGON。请注意：必须将创建空间索引的字段声明为NOT NULL，并且空间索引只能在存储引擎为MyISAM的表中创建。

drop table if exists student;
create table student(
	学号 VARCHAR(100),
	姓名 VARCHAR(100),
	出生日期 VARCHAR(100),
	性别 VARCHAR(100),
	# 建立普通索引
	index(学号)
	# 创建唯一性索引
	unique index uniqueindex(学号),
	# 单列索引
	unique singleIndex(学号（20）),
	# 多列索引
	index mulIndex(学号，姓名(20))
);

给已有表创建索引：

create index bookindex on book(bookid);


alter table book add index bookIndex(bookid);

删除索引：为了避免影响数据库性能应该及时删除不再使用的索引。

alter table book drop index bookIndex;


drop index bookIndex on book;

7.2 Mysql存储引擎

有MyISAM，InnoDB，MEMORY，MERGE等，这里只说MyISAM和InnoDB

1. MyISAM

• 适合表中绝大多数都是查询的情况
• 不支持事务
• 不支持外键
• 支持全文索引

2. InnoDB

• 适合更新请求密集的表
• 支持事务
• 自动从灾难中恢复
• 支持自动增加列AUTO_INCREMENT操作

MyISAM和InnoDB均采用b+树的索引结构

为什么用b+树呢:

• b树查找不稳定，b+树因为都在叶子节点所以稳定
• 相同数据量的情况下,b+树比b树更矮胖，因此查询io也要少，真实场景一般高度为3
• b+树叶子节点有链表，所以范围查询更简单

7.3 索引用法

什么地方使用索引：

• 经常搜索的列
• 主键列
• 连接列
• 范围搜索列，因为索引进行了排序
• where子句列

索引注意事项：

• 查询很少的列不创建索引
• 列值较少（性别）的列不创建索引
• image,bit数据类型的列不创建索引
• 修改性能远远大于索引性能的列，索引会提高检索性能但会降低修改性能。
• 只要列中包含有NULL值都将不会被包含在索引中，所以我们在数据库设计时不要让字段的默认值为NULL。
• 对字符串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
• like “%aaa%” 不会使用索引而like “aaa%”可以使用索引,以%开头不会利用到索引，结尾可以。
• 不要在索引列上进行运算,我们可以吧 id - 2 = 1改成id = 1 + 2 的形式
• 不使用NOT IN和<>操作
• 符合最左前缀原则

8 触发器

触发器(TRIGGER)是MySQL的数据库对象之一，该对象与编程语言中的函数非常类似，都需要声明、执行等。但是触发器的执行不是由程序调用也不是由工程师手工启动，而是由事件来触发、 激活从而得以执行。

create trigger triggerName
after triggerEvent on  tableName for each row
begin
triggerSTMT
end