1 MySQL数据库的基本操作

1.1 DDL

1.1.1 什么是DDL

DDL(Data Definition Language)，数据定义语言，该语言部分包括以下内容：

• 对数据库的常用操作

• 对表结构的常用操作

• 修改表结构

1.1.2 对数据库的常用操作

show databases;     -- 查看所有的数据库
create database [if not exists] mydb1 [charset=utf8]    -- 创建数据库
use mydb1       -- 切换(选择要操作的)数据库
drop database [if exists] mydb1;    -- 删除数据库
alter database mydb1 character set utf8;    -- 修改数据库编码

1.1.3 对表结构的常用操作

1.1.3.1 创建表

create table [if not exists]表名(
  字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
  字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
  字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的一些设置];

例如

use mydb1;
create table if not exists student(
    sid int,
    name varchar(20),
    gender varchar(20),
    age int,
    birth date, 
    address varchar(20),
    score double
);

1.1.3.2 修改表结构格式

• 修改表添加列

alter table 表名 add 列名 类型(长度) [约束];

例如

# 为student表添加一个新的字段为：系别 dept 类型为 varchar(20)
ALTER TABLE student ADD dept VARCHAR(20); 

• 修改列名和类型

alter table 表名 change 旧列名 新列名 类型(长度) 约束; 

例如

# 为student表的dept字段更换为department varchar(30)
ALTER TABLE student change dept department VARCHAR(30); 

• 修改表删除列

alter table 表名 drop 列名;

例如

# 删除student表中department这列
ALTER TABLE student DROP department;

• 修改表名

rename table 表名 to 新表名;

例如

#将表student改名成 stu
rename table student to stu;

1.1.3.3 其他操作

show tables;    -- 查看当前数据库的所有表名称
show create table 表名；   -- 查看指定某个表的创建语句
desc 表名;        -- 查看表结构
drop table 表名;      -- 删除表

1.2 DML

1.2.1 什么是DML

DML是指数据操作语言，英文全称是Data Manipulation Language，用来对数据库中表的数据记录进行更新。关键字：

• 插入insert

• 删除delete

• 更新update

1.2.2 数据插入

insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)； //向表中插入某些
insert into 表 values (值1,值2,值3...);     //向表中插入所有列

例如

# 插入一条数据
insert into student(sid,name,gender,age,birth,address,score) 
                values(1001,'张三','男',18,'1996-12-23','北京',83.5);
# 插入一条部分数据
insert into student(sid) values(1005)
# 插入多条数据
insert into student(sid,name,gender,age,birth,address,score) 
                values(1002,'李四','男',19,'1995-01-16','广东',84.5),
                      (1003,'王五','男',18,'1996-11-02','天津',87.5);
# 插入一条数据 需要所有的列都有数据 
insert into student values(1004,'某某','女',17,'1997-06-07','上海',89.5);

1.2.3 数据修改

update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值... where 条件;

例如

# 将所有学生的地址修改为重庆 
update student set address = '重庆';

# 将id为1004的学生的地址修改为北京
update student set address = '北京' where sid = 1004; 

# 将id为1005的学生的地址修改为北京，成绩修成绩修改为100
update student set address = '广州',score=100 where sid = 1005;

1.2.4 数据删除

delete from 表名 [where 条件];
truncate table  表名 或者 truncate 表名

例如

-- 1.删除sid为1004的学生数据
delete from student where sid  = 1004;
-- 2.删除表所有数据
delete from student;
-- 3.清空表数据
truncate table student;
truncate student;

delete只删除内容，而truncate类似于drop table ，可以理解为是将整个表删除，然后再创建该表

1.3 约束

1.3.1 什么是约束

约束(constraint)实际上就是表中数据的限制条件，表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些列的值（手机号）不能为空，有些列的值（身份证号）不能重复，可以分为下面几类

• 主键约束(primary key) PK

• 自增长约束(auto_increment)

• 非空约束(not null)

• 唯一性约束(unique)

• 默认约束(default)

• 零填充约束(zerofill)

• 外键约束(foreign key) FK

1.3.2 主键约束

1.3.2.1 主键约束的概念

MySQL主键约束(primary key)是一个列或者多个列的组合，其值能唯一地标识表中的每一行,方便在RDBMS中尽快的找到某一行。主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。每个表最多只允许一个主键，当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

1.3.2.2 操作——添加单列主键

创建单列主键有两种方式，一种是在定义字段的同时指定主键，一种是定义完字段之后指定主键

方法1

-- 在 create table 语句中，通过 PRIMARY KEY 关键字来指定主键。
-- 在定义字段的同时指定主键，语法格式如下：
create table 表名(
   ...
   <字段名> <数据类型> primary key -- 这个字段非空且唯一
   ...
)

例如

create table emp1(
    eid int primay key,
    name VARCHAR(20),
    deptId int,
    salary double
);

方法2

-- 在定义字段之后再指定主键，语法格式如下：
create table 表名(
   ...
   [constraint <约束名>] primary key [字段名]
);

例如

create table emp2(
    eid INT,
    name VARCHAR(20),
    deptId INT,
    salary double,
    constraint pk1 primary key(eid)	-- constraint pk1 可以省略
 );

1.3.2.3 操作——添加多列主键(联合主键)

所谓的联合主键，就是这个主键是由一张表中多个字段组成的。

当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。

一张表只能有一个主键，联合主键也是一个主键

create table 表名(
   ...
   primary key （字段1，字段2，…,字段n)
);

例如

create table emp3( 
  name varchar(20), 
  deptId int, 
  salary double, 
  primary key(name,deptId) 
);

联合主键的每列都不能为空，且联合内容不能完全一样

1.3.2.4 通过修改表结构添加主键

主键约束不仅可以在创建表的同时创建，也可以在修改表时添加。

create table 表名(
   ...
);
alter table <表名> add primary key（字段列表);

例如

-- 添加单列主键
create table emp4(
  eid int, 
  name varchar(20), 
  deptId int, 
  salary double, 
);
alter table emp4 add primary key（eid);

1.3.2.5 删除主键约束

一个表中不需要主键约束时，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。

alter table <数据表名> drop primary key;

1.3.3 自增长约束

1.3.3.1 自增长约束的概念

在 MySQL 中，当主键定义为自增长(auto_increment)后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

• 默认情况下，auto_increment的初始值是 1，每新增一条记录，字段值自动加 1。

• 一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。

• auto_increment约束的字段必须具备 NOT NULL 属性。

• auto_increment约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等。

• auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效。

1.3.3.2 自增长约束的赋予

字段名 数据类型 auto_increment

例如

create table t_user1( 
  id int primary key auto_increment, 
  name varchar(20) 
);

1.3.3.3 指定自增字段初始值

如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。例如，如果表中插入的第一条记录的 id 值设置为 5，那么再插入记录时，id 值就会从 5 开始往上增加

-- 方式1，创建表时指定
create table t_user2 ( 
  id int primary key auto_increment, 
  name varchar(20)
)auto_increment=100;

-- 方式2，创建表之后指定
create table t_user3 ( 
  id int primary key auto_increment, 
  name varchar(20)
);

alter table t_user2 auto_increment=100;

删除表delete和truncate在删除后自增列的变化

• delete数据之后自动增长从断点开始

• truncate数据之后自动增长从默认起始值开始

1.3.4 非空约束

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

# 方式1：
<字段名><数据类型> not null;

# 方式2：
alter table 表名 modify 字段 类型 not null;

例如

-- 方式1，创建表时指定
create table t_user6 ( 
  id int , 
  name varchar(20) not null, 
  address varchar(20) not null 
);

-- 方式2，创建表后指定
create table t_user7 ( 
  id int , 
  name varchar(20) , -- 指定非空约束 
  address varchar(20) -- 指定非空约束 
); 
alter table t_user7 modify name varchar(20) not null; 
alter table t_user7 modify address varchar(20) not null;

删除非空约束

-- alter table 表名 modify 字段 类型 
alter table t_user7 modify name varchar(20) ; 
alter table t_user7 modify address varchar(20) ;

1.3.5 唯一约束

唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。

# 方式1：
<字段名> <数据类型> unique
# 方式2： 
alter table 表名 add constraint 约束名 unique(列);

注意：NULL和任何值都不相同，甚至NULL和NULL也不相同

例如

-- 创建表时指定
create table t_user8 ( 
 id int , 
 name varchar(20) , 
 phone_number varchar(20) unique -- 指定唯一约束 
);

-- 创建表后指定
create table t_user9 ( 
  id int , 
  name varchar(20) , 
  phone_number varchar(20) -- 指定唯一约束 
); 
alter table t_user9 add constraint unique_ph unique(phone_number);

删除唯一约束：

-- alter table <表名> drop index <唯一约束名>;
alter table t_user9 drop index unique_ph;

1.3.6 默认约束

MySQL 默认值约束(default)用来指定某列的默认值。

# 方式1： 
<字段名> <数据类型> default <默认值>;
# 方式2:
alter table 表名 modify 列名 类型 default 默认值; 

例如

# <字段名> <数据类型> default <默认值>;
create table t_user10 ( 
  id int , 
  name varchar(20) , 
  address varchar(20) default ‘北京’ -- 指定默认约束 
);

# alter table 表名 modify 列名 类型 default 默认值; 
create table t_user11 ( 
  id int , 
  name varchar(20) , 
  address varchar(20)  
);
alter table t_user11 modify address varchar(20) default  ‘北京’;

删除默认约束

-- alter table <表名> modify column <字段名> <类型> default null; 

alter table t_user11 modify column address varchar(20) default null;

1.3.7 零填充约束(zerofill)

插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0。zerofill默认为int(10)，当使用zerofill 时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，

create table t_user12 ( 
  id int zerofill , -- 零填充约束
  name varchar(20)   
);

删除零填充约束

alter table t_user12 modify id int;

1.4 DQL

数据库管理系统一个重要功能就是数据查询，数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进行筛选以及确定数据以什么样的格式显示。MySQL提供了功能强大、灵活的语句来实现这些操作。MySQL数据库使用select语句来查询数据。

1.4.1 DQL的基本语法

select 
  [all|distinct]
  <目标列的表达式1> [别名],
  <目标列的表达式2> [别名]...
from <表名或视图名> [别名],<表名或视图名> [别名]...
[where<条件表达式>]
[group by <列名> 					-- 分组
[having <条件表达式>]]			  -- 分组后的结果再筛选
[order by <列名> [asc|desc]]		-- 排序
[limit <数字或者列表>];			 -- 对最终的结果筛选

简化版：

select *|列名 from 表 where 条件

1.4.2 简单查询

例子

-- 1.查询所有的商品.  
select *  from product;
-- 2.查询商品名和商品价格. 
select pname,price from product;
-- 3.别名查询.使用的关键字是as（as可以省略的）.  
-- 3.1表别名: 
select * from product as p;
-- 3.2列别名：
select pname as pn from product; 
-- 4.去掉重复值.  
select distinct price from product;
-- 5.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
select pname,price+10 from product;

1.4.3 条件查询

例子

-- 查询商品名称为“海尔洗衣机”的商品所有信息：
select * from product where pname = '海尔洗衣机';
-- 查询价格为800商品
select * from product where price = 800;
-- 查询价格不是800的所有商品
select * from product where price != 800;
select * from product where price <> 800;
select * from product where not(price = 800);
-- 查询商品价格大于60元的所有商品信息
select * from product where price > 60;
-- 查询商品价格在200到1000之间所有商品
select * from product where price >= 200 and price <=1000;
select * from product where price between 200 and 1000;

1.4.4 运算符查询

例子

-- 查询商品价格是200或800的所有商品
select * from product where price = 200 or price = 800;
select * from product where price in (200,800);
-- 查询含有‘裤'字的所有商品
select * from product where pname like '%裤%';		-- 百分号匹配任意字符
-- 查询以'海'开头的所有商品
select * from product where pname like '海%';
-- 查询第二个字为'蔻'的所有商品
select * from product where pname like '_蔻%';		-- 下划线匹配单个字符
-- 查询category_id为null的商品
select * from product where category_id is null;
-- 查询category_id不为null分类的商品
select * from product where category_id is not null;
-- 使用least求最小值
select least(10, 20, 30); -- 10
select least(10, null , 30); -- null
-- 使用greatest求最大值
select greatest(10, 20, 30);
select greatest(10, null, 30); -- null

1.4.5 排序查询

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 order by 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

select 
 字段名1，字段名2，……
from 表名
order by 字段名1 [asc|desc]，字段名2[asc|desc]……		-- 以字段名1为主，字段名2为辅

• asc代表升序，desc代表降序，如果不写默认升序

• order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名

• order by子句，放在查询语句的最后面。LIMIT子句除外

例如

-- 1.使用价格排序(降序)
select * from product order by price desc;
-- 2.在价格排序(降序)的基础上，以分类排序(降序)
select * from product order by price desc,category_id asc;
-- 3.显示商品的价格(去重复)，并排序(降序)
select distinct price from product order by price desc;

1.4.6 聚合查询

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

例如

-- 1 查询商品的总条数
select count(*) from product;
-- 2 查询价格大于200商品的总条数
select count(*) from product where price > 200;
-- 3 查询分类为'c001'的所有商品的总和
select sum(price) from product where category_id = 'c001';
-- 4 查询商品的最大价格
select max(price) from product;
-- 5 查询商品的最小价格
select min(price) from product;
-- 6 查询分类为'c002'所有商品的平均价格
select avg(price) from product where category_id = 'c002';

1.4.7 分组查询

分组查询是指使用group by字句对查询信息进行分组。

select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;

• 分组之后对统计结果进行筛选的话必须使用having，不能使用where

• where子句用来筛选 FROM 子句中指定的操作所产生的行

• group by 子句用来分组 WHERE 子句的输出。

• having 子句用来从分组的结果中筛选行

例如

-- 1 统计各个分类商品的个数
select category_id ,count(*) from product group by category_id ;
-- 2.统计各个分类商品的个数,且只显示个数大于1的信息
select category_id ,count(*) from product group by category_id having count(*) > 1;

1.4.8 分页查询

分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。

-- 方式1-显示前n条
select 字段1，字段2... from 表明 limit n
-- 方式2-分页显示
select 字段1，字段2... from 表明 limit m,n
-- m: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
-- n: 整数，表示查询多少条数据

例如

-- 查询product表的前5条记录 
select * from product limit 5 
-- 从第4条开始显示，显示5条 
select * from product limit 3,5

1.4.9 其他

• INSERT INTO SELECT语句

将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT语句 。

insert into Table2(field1,field2,…) select value1,value2,… from Table1 或者：
insert into Table2 select * from Table1

要求目标表Table2必须存在

• SELECT INTO FROM语句

将一张表的数据导入到另一张表中，有两种选择 SELECT INTO 和 INSERT INTO SELECT 。

SELECT vale1, value2 into Table2 from Table1

要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。

1.4.10 正则表达式

正则表达式(regular expression)描述了一种字符串匹配的规则，正则表达式本身就是一个字符串，使用这个字符串来描述、用来定义匹配规则，匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常被用来检索、替换那些符合某个规则的文本。

MySQL通过REGEXP关键字支持正则表达式进行字符串匹配。

例子

-- ^ 在字符串开始处进行匹配
-- 显示以海开头的所有商品
SELECT  * FROM product WHERE pname REGEXP '^海';

2 MySQL的多表操作

MySQL多表之间的关系可以概括为：一对一、一对多/多对一关系，多对多

2.1 多表关系

2.1.1 一对一关系

• 一个学生只有一张身份证；一张身份证只能对应一学生。

• 在任一表中添加唯一外键，指向另一方主键，确保一对一关系。

• 一般一对一关系很少见，遇到一对一关系的表最好是合并表。

2.1.2 一对多/多对一关系

部门和员工

分析：一个部门有多个员工，一个员工只能对应一个部门

实现原则：在多的一方建立外键，指向一的一方的主键

2.1.3 多对多关系

学生和课程

分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

原则：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键

2.1.4 什么是外键约束

MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。比如，一个水果摊，只有苹果、桃子、李子、西瓜等 4 种水果，那么，你来到水果摊要买水果就只能选择苹果、桃子、李子和西瓜，其它的水果都是不能购买的。

定义一个外键时，需要遵守下列规则：

• 主表必须已经存在于数据库中，或者是当前正在创建的表。

• 必须为主表定义主键。

• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这 个外键的内容就是正确的。

• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。

• 外键中列的数目必须和主表的主键中列的数目相同。

• 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

2.1.5 创建外键约束

方式1-在创建表时设置外键约束

[constraint <外键名>] foreign key 字段名 [，字段名2，…] references <主表名> 主键列1 [，主键列2，…]

例如

create database mydb3; 
use mydb3;
-- 创建部门表
create table if not exists dept(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);

create table if not exists emp(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20),  -- 员工所属部门
  constraint emp_fk foreign key (dept_id) references dept (deptno) -- 外键约束
);

方式2-在创建表时设置外键约束

外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

alter table <数据表名> add constraint <外键名> foreign key(<列名>) references <主表名> (<列名>);

例如

-- 创建部门表
create table if not exists dept2(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);
-- 创建员工表
create table if not exists emp2(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20)  -- 员工所属部门
 
);
-- 创建外键约束
alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2 (deptno);

在外键约束下的数据操作必须先给主表添加数据，给从表添加数据时，外键列的值不能随便写，必须依赖主表的主键列

主表的数据被从表依赖时，不能删除，否则可以删除，从表的数据可以随便删除

2.1.6 删除外键约束

当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系

alter table <表名> drop foreign key <外键约束名>;

2.1.7 多对多关系下的外键约束

-- 学生表和课程表(多对多)
  -- 1 创建学生表student(左侧主表)
   create table if not exists student(
    sid int primary key auto_increment,
    name varchar(20),
    age int,
    gender varchar(20)
   );
  -- 2 创建课程表course(右侧主表)
  create table course(
   cid  int primary key auto_increment,
   cidname varchar(20)
  );
  -- 3创建中间表student_course/score(从表)
  create table score(
    sid int,
    cid int,
    score double
  );
    
-- 4建立外键约束(2次)
 
alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);

2.2 多表操作

多表查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候,需要显示的数据来自多张表。多表查询有以下分类：

• 交叉连接查询

select * from A,B;  

• 内连接查询(使用的关键字 inner join -- inner可以省略)

# 隐式内连接（SQL92标准）：
select * from A,B where 条件;
# 显示内连接（SQL99标准）：
select * from A inner join B on 条件;

• 外连接查询(使用的关键字 outer join -- outer可以省略)

# 左外连接：left outer join
select * from A left outer join B on 条件;
# 右外连接：right outer join
select * from A right outer join B on 条件;
# 满外连接: full outer join
select * from A full outer join B on 条件;

• 子查询：select的嵌套

• 表自关联：将一张表当成多张表来用

2.2.1 交叉连接查询

• 交叉连接查询返回被连接的两个表所有数据行的笛卡尔积、

• 笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配

• 假如A表有m行数据，B表有n行数据，则返回m*n行数据

• 笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

/* 
格式：
select * from 表1,表2,表3….; 
*/
-- 交叉连接查询
select * from dept3,emp3;

2.2.2 内链接查询

内连接查询求多张表的交集

/*
格式：
隐式内连接（SQL92标准）：select * from A,B where 条件; 
显示内连接（SQL99标准）：select * from A inner join B on 条件;
*/
-- 查询每个部门的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;

2.2.3 外链接查询

外连接分为左外连接（left outer join）、右外连接(right outer join)，满外连接(full outer join)。

注意：oracle里面有full join,可是在mysql对full join支持的不好。我们可以使用union来达到目的。

/*
格式：
左外连接：left outer join
        select * from A left outer join B on 条件;
右外连接：right outer join
        select * from A right outer join B on 条件;
  满外连接: full outer join
        select * from A full outer join B on 条件;
*/
-- 外连接查询
-- 查询哪些部门有员工，哪些部门没有员工
use mydb3;
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
-- 查询哪些员工有对应的部门，哪些没有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id
union 
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;

2.2.4 子查询

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询。子查询可以返回的数据类型一共分为四种：

• 单行单列：返回的是一个具体列的内容，可以理解为一个单值数据；

• 单行多列：返回一行数据中多个列的内容；

• 多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围；

• 多行多列：查询返回的结果是一张临时表

-- 查询年龄最大的员工信息，显示信息包含员工号、员工名字，员工年龄
select eid,ename,age from emp3 where age = (select max(age) from emp3);
-- 查询年研发部和销售部的员工信息，包含员工号、员工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno,name from dept3 where name = '研发部' or name = '销售部') ;
-- 查询研发部20岁以下的员工信息,包括员工号、员工名字，部门名字
select eid,age,ename,name from (select * from dept where name = '研发部 ')t1,(select * from emp3 where age <20)t2

1. 子查询关键字-ALL

select …from …where c > all(查询语句)
-- 等价于：
select ...from ... where c > result1 and c > result2 and c > result3

• ALL: 与子查询返回的所有值比较为true 则返回true

• ALL可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

• ALL表示指定列中的值必须要大于子查询集的每一个值，即必须要大于子查询集的最大值；如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。

例如

-- 查询年龄大于‘1003’部门所有年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003');
-- 查询不属于任何一个部门的员工信息 
select * from emp3 where dept_id != all(select deptno from dept3); 

2. 子查询关键字-ANY和SOME

select …from …where c > any(查询语句)
--等价于：
select ...from ... where c > result1 or c > result2 or c > result3

• ANY:与子查询返回的任何值比较为true 则返回true

• ANY可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何一个数据。

• 表示制定列中的值要大于子查询中的任意一个值，即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况

• SOME和ANY的作用一样，SOME可以理解为ANY的别名

例如

-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003’);

3. 子查询关键字-IN

select …from …where c in(查询语句)
--等价于：
select ...from ... where c = result1 or c = result2 or c = result3

• IN关键字，用于判断某个记录的值，是否在指定的集合中

• 在IN关键字前边加上not可以将条件反过来

例如

-- 查询研发部和销售部的员工信息，包含员工号、员工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部') ;

4. 子查询关键字-EXISTS

select …from …where exists(查询语句)

• 该子查询如果“有数据结果”(至少返回一行数据)， 则该EXISTS() 的结果为“true”，外层查询执行

• 该子查询如果“没有数据结果”（没有任何数据返回），则该EXISTS()的结果为“false”，外层查询不执行

• EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时 where条件成立

• 注意，EXISTS关键字，比IN关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用EXISTS关键字

例如

-- 查询公司是否有大于60岁的员工，有则输出
select * from emp3 a where exists(select * from emp3 b where a.age > 60);
-- 查询有所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);

2.2.5 自关联查询

MySQL有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。注意自关联时表必须给表起别名。

select 字段列表 from 表1 a , 表1 b where 条件;
# 或者 
select 字段列表 from 表1 a [left] join 表1 b on 条件;

例如

-- 创建表,并建立自关联约束
create table t_sanguo(
    eid int primary key ,
    ename varchar(20),
    manager_id int,
 foreign key (manager_id) references t_sanguo (eid)  -- 添加自关联约束
);

-- 进行关联查询
select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;

3 MySQL的函数

在MySQL中，为了提高代码重用性和隐藏实现细节，MySQL提供了很多函数。函数可以理解为别人封装好的模板代码

在MySQL中，函数非常多，主要可以分为以下几类:

• 聚合函数

• 数学函数

• 字符串函数

• 日期函数

• 控制流函数

• 窗口函数

3.1 聚合函数

在MySQL中，聚合函数主要由：count,sum,min,max,avg,这些聚合函数我们之前都学过，不再重复。这里我们学习另外一个函数:group_concat(），该函数用户实现行的合并

group_concat()函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果。

group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])

• 使用distinct可以排除重复值；

• 如果需要对结果中的值进行排序，可以使用order by子句；

• separator是一个字符串值，默认为逗号。

例如

-- 将所有员工的名字合并成一行 
select group_concat(emp_name) from emp;
-- 指定分隔符合并 
select department,group_concat(emp_name separator ';' ) from emp group by department; 
-- 指定排序方式和分隔符 
select department,group_concat(emp_name order by salary desc separator ';' ) from emp group by department;

3.2 数学函数

3.3 字符串函数

3.4 日期函数

3.5 控制流函数

• if逻辑判断语句

• case when语句

CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END

CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。

例如

select 
*  ,
case 
  when payType=1 then '微信支付' 
    when payType=2 then '支付宝支付' 
    when payType=3 then '银行卡支付' 
    else '其他支付方式' 
end  as payTypeStr
from orders;
-- 方式2
select 
*  ,
case payType
  when 1 then '微信支付' 
    when 2 then '支付宝支付' 
    when 3 then '银行卡支付' 
    else '其他支付方式' 
end  as payTypeStr
from orders;

3.6 窗口函数

MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数，与Oracle 窗口函数类似，属于MySQL的一大特点.

非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

window_function ( expr ) OVER ( 
  PARTITION BY ... 
  ORDER BY ... 
  frame_clause 
)

其中，window_function 是窗口函数的名称；expr 是参数，有些函数不需要参数；OVER子句包含三个选项：

• 分2区（PARTITION BY）:PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算

• 排序（ORDER BY）:OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似

• 窗口大小（frame_clause）:frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

3.6.1 序号函数

序号函数有三个：ROW_NUMBER()、RANK()、DENSE_RANK()，可以用来实现分组排序，并添加序号。

row_number()|rank()|dense_rank() over ( 
  partition by ... 		-- 按照字段分组
  order by ... 			-- 组内排序
)

例如

-- 对每个部门的员工按照薪资排序，并给出排名 rank
select 
dname,
ename,
salary,
rank() over(partition by dname order by salary desc) as rn 
-- row_number() over(partition by dname order by salary desc) as rn 
-- dense_rank() over(partition by dname order by salary desc) as rn 
from employee;

3.6.2 开窗聚合函数

在窗口中每条记录动态地应用聚合函数（SUM()、AVG()、MAX()、MIN()、COUNT()），可以动态计算在指定的窗口内的各种聚合函数值。

select  
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate rows between 3 preceding and current row) as c1 
from employee;

3.6.3 分布函数

• CUME_DIST

用途：分组内小于、等于当前rank值的行数 / 分组内总行数

应用场景：查询小于等于当前薪资（salary）的比例

select  
 dname,
 ename,
 salary,
 cume_dist() over(order by salary) as rn1, -- 没有partition语句 所有的数据位于一组
 cume_dist() over(partition by dept order by salary) as rn2 
from employee;

• PERCENT_RANK

用途：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数

select 
 dname,
 ename,
 salary,
 rank() over(partition by dname order by salary desc ) as rn,
 percent_rank() over(partition by dname order by salary desc ) as rn2
from employee;

3.6.4 前后函数

用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值

可以加第三个参数表示默认值，如果没有前/后n行则用默认值代替，不填默认null

-- lag的用法
select 
 dname,
 ename,
 hiredate,
 salary,
 lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
 lag(hiredate,2) over(partition by dname order by hiredate) as last_2_time 
from employee;

3.6.5 头尾函数

用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值

-- 注意,  如果不指定ORDER BY，则进行排序混乱，会出现错误的结果
select
  dname,
  ename,
  hiredate,
  salary,
  first_value(salary) over(partition by dname order by hiredate) as first,
  last_value(salary) over(partition by dname order by  hiredate) as last 
from  employee;

3.6.6 其他函数

• NTH_VALUE(expr,n)

用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名

-- 查询每个部门截止目前薪资排在第二和第三的员工信息
select 
  dname,
  ename,
  hiredate,
  salary,
  nth_value(salary,2) over(partition by dname order by hiredate) as second_score,
  nth_value(salary,3) over(partition by dname order by hiredate) as third_score
from employee

• NTILE(n)

用途：将分区中的有序数据分为n个等级，记录等级数

-- 根据入职日期将每个部门的员工分成3组
select 
  dname,
  ename,
  hiredate,
  salary,
ntile(3) over(partition by dname order by  hiredate  ) as rn 
from employee;

4 MySQL的视图

视图（view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

视图的作用有

• 简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用。

• 安全原因，如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图

4.1 视图的创建

创建视图的语法为

create [or replace] [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]
​
/*
参数说明：
（1）algorithm：可选项，表示视图选择的算法。
（2）view_name ：表示要创建的视图名称。
（3）column_list：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
（4）select_statement：表示一个完整的查询语句，将查询记录导入视图中。
（5）[with [cascaded | local] check option]：可选项，表示更新视图时要保证在该视图的权限范围之内。
*/

4.2 修改视图

修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过CREATE OR REPLACE VIEW语句和ALTER VIEW语句来修改视图。

alter view 视图名 as select语句

4.3 更新视图

某些视图是可更新的。也就是说，可以在UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。如果视图包含下述结构中的任何一种，那么它就是不可更新的：

• 聚合函数（SUM(), MIN(), MAX(), COUNT()等）

• DISTINCT

• GROUP BY

• HAVING

• UNION或UNION ALL

• 位于选择列表中的子查询

• JOIN

• FROM子句中的不可更新视图

• WHERE子句中的子查询，引用FROM子句中的表。

• 仅引用文字值（在该情况下，没有要更新的基本表）

视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。

4.4 重命名和删除视图

• 重命名视图　　

-- rename table 视图名 to 新视图名; 
rename table view1_emp to my_view1

• 删除视图

-- drop view 视图名[,视图名…];
drop view if exists view_student;

删除视图时，只能删除视图的定义，不会删除数据。

5 MySQL的存储过程

简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；存储过就是数据库 SQL 语言层面的代码封装与重用。

存储过程的特性：

• 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；

• 函数的普遍特性：模块化，封装，代码复用；

• 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

5.1 存储过程的使用

其格式为

delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
begin
  sql语句
end 自定义的结束符合
delimiter ;

-- 调用存储过程
call 储存名(); 

5.2 变量定义

5.2.2.1 局部变量

用户自定义，在begin/end块中有效

-- 声明变量 
-- declare var_name type [default var_value]; 
declare nickname varchar(32);
-- 变量赋值
set nickname = ‘zhangsan’;  

MySQL 中还可以使用 SELECT..INTO 语句为变量赋值。其基本语法如下：

select col_name [...] into var_name[,...] 
from table_name wehre condition 
/*
其中：
col_name 参数表示查询的字段名称；
var_name 参数是变量的名称；
table_name 参数指表的名称；
condition 参数指查询条件。
注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。
*/

5.2.2.2 用户变量

用户自定义，当前会话（连接）有效。类比java的成员变量 不需要提前声明，使用即声明

-- 语法： 
@var_name

例如

delimiter $$
create procedure proc04()
begin
    set @var_name01  = 'ZS';
end $$
delimiter;
call proc04() ;

5.2.2.3 系统变量

系统变量又分为全局变量与会话变量

• 全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。

• 会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。

也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。

全局变量的语法：

@@global.var_name

-- 查看全局变量 
show global variables; 
-- 查看某全局变量 
select @@global.auto_increment_increment; 
-- 修改全局变量的值 
set global sort_buffer_size = 40000; 
set @@global.sort_buffer_size = 40000;

会话变量的语法

@@session.var_name

-- 查看会话变量
show session variables;
-- 查看某会话变量 
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000; 
set @@session.sort_buffer_size = 50000 ;

5.3 存储过程传参

• in 表示传入的参数， 可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。

• out 表示从存储过程内部传值给调用者

• inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）

5.4 流程控制

5.4.1 判断

IF语句包含多个条件判断，根据结果为TRUE、FALSE执行语句，与编程语言中的if、else if、else语法类似，其语法格式如下

-- 语法
if search_condition_1 then statement_list_1
    [elseif search_condition_2 then statement_list_2] ...
    [else statement_list_n]
end if

CASE是另一个条件判断的语句，类似于编程语言中的switch语法

-- 语法一（类比java的switch）：
case case_value
    when when_value then statement_list
    [when when_value then statement_list] ...
    [else statement_list]
end case
-- 语法二：
case
    when search_condition then statement_list
    [when search_condition then statement_list] ...
    [else statement_list]
end case

5.4.2 循环

循环是一段在程序中只出现一次,但可能会连续运行多次的代码，循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环

循环的类别包括

• while

• repeat

• loop

-- 类似于while
[标签:] while 循环条件 do
    循环体;
end while[标签];

循环控制包括

• leave 类似于 break，跳出，结束当前所在的循环

• iterate类似于 continue，继续，结束本次循环，继续下一次

-- 类似于do while
[标签:] repeat 
 循环体;
until 条件表达式
end repeat [标签];

[标签:] loop
  循环体;
  if 条件表达式 then 
     leave [标签]; 
  end if;
end loop;

5.5 游标

游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE.

-- 声明语法
declare cursor_name cursor for select_statement
-- 打开语法
open cursor_name
-- 取值语法
fetch cursor_name into var_name [, var_name] ...
-- 关闭语法
close cursor_name

5.6 异常处理

MySql存储过程也提供了对异常处理的功能：通过定义HANDLER来完成异常声明的实现.

官方文档：https://dev.mysql.com/doc/refman/5.7/en/declare-handler.html

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

/*
handler_action: {
    CONTINUE	-- 继续执行
  | EXIT		-- 退出程序
  | UNDO		-- 不支持，暂时不使用
}
 
condition_value: {
    mysql_error_code		-- MySQL的条件码
  | condition_name			-- MySQL的条件名
  | SQLWARNING				
  | NOT FOUND				
  | SQLEXCEPTION	
*/

在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

5.7 存储函数

MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。

存储函数与存储过程的区别在于

• 存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。

• 存储函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。

• 存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；

• 存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。

• 存储过程可以调用存储函数。但函数不能调用存储过程。

• 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.

在MySQL中，创建存储函数使用create function关键字，其基本形式如下：

create function func_name ([param_name type[,...]])
returns type
[characteristic ...] 
begin
    routine_body
end;

/*
func_name ：存储函数的名称。
param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。
RETURNS type：指定返回值的类型。
characteristic：可选项，指定存储函数的特性。
routine_body：SQL代码内容。
*/

6 MySQL的触发器

触发器，就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动条用。在MySQL中，只有执行insert,delete,update操作时才能触发触发器的执行，触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。可以使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

6.1 创建触发器

• 创建只有一个执行语句的触发器

create trigger 触发器名 before|after 触发事件
on 表名 for each row 
执行语句;

• 创建有多个执行语句的触发器

create trigger 触发器名 before|after  触发事件 
on 表名 for each row
begin
     执行语句列表
end;

6.2 NEW与OLD

MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：

6.3 查看和删除触发器

• 查看触发器

show triggers;

• 删除触发器

-- drop trigger [if exists] trigger_name 
drop trigger if exists trigger_test1;

1. MYSQL中触发器中不能对本表进行 insert ,update ,delete 操作，以免递归循环触发

2. 尽量少使用触发器，假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。

3. 触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。

7 MySQL的索引

索引是通过某种算法，构建出一个数据模型，用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

索引的优点：

• 大大加快数据的查询速度

• 使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间

• 创建唯一索引，能够保证数据库表中每一行数据的唯一性

• 在实现数据的参考完整性方面，可以加速表和表之间的连接

索引的缺点

• 创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加

• 索引需要占据磁盘空间

• 对数据表中的数据进行增加，修改，删除时，索引也要动态的维护，降低了维护的速度

创建索引的原则

• 更新频繁的列不应设置索引

• 数据量小的表不要使用索引

• 重复数据多的字段不应设为索引，一般来说：重复的数据超过百分之15就不该建索引

• 首先应该考虑对where 和 order by 涉及的列上建立索引

7.1 索引的分类

索引是存储引擎用来快速查找记录的一种数据结构，按照实现的方式类分，主要有Hash索引和B+Tree索引

按照功能划分，索引划为以下分类:

• 单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引;

  • 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

  • 唯一索引：唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

  • 主键索引：每张表一般都会有自己的主键，当我们在创建表时，MySQL会自动在主键列上建立一个索引，这就是主键索引。主键是具有唯一性并且不允许为NULL，所以他是一种特殊的唯一索引。

• 组合索引：组合索引也叫复合索引，指的是我们在建立索引的时候使用多个字段，例如同时使用身份证和手机号建立索引，同样的可以建立为普通索引或者是唯一索引。复合索引的使用复合最左原则。

• 全文索引：全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，基于相似度的查询，而不是简单的where语句的参数匹配。

• 空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

7.2 单列索引的操作

7.2.1 普通索引

7.2.1.1 创建索引

-- 方式1-创建表的时候直接指定
create  table student(
    name varchar(20),
    -- 省略部分代码
    index index_name(name) -- 给name列创建索引
);

-- 方式2-直接创建
create index indexname on tablename(columnname); 

-- 方式3-修改表结构(添加索引)
alter table tablename add index indexname(columnname)

7.2.1.2 查看索引

-- 1、查看数据库所有索引 
select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名'; 

-- 2、查看表中所有索引 
select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名' and a.table_name like '%表名%'; 

-- 3、查看表中所有索引 
show index from table_name; 

7.2.1.3 删除索引

-- 方法1
drop index 索引名 on 表名 

-- 方法2
alter table 表名 drop index 索引名 

7.2.2 唯一索引

7.2.2.1 创建索引

-- 方式1-创建表的时候直接指定
create  table student2(
    card_id varchar(20),
    -- 省略部分代码
    unique index_card_id(card_id) -- 给card_id列创建索引
);

-- 方式2-直接创建
create unique index 索引名 on 表名(列名) 

-- 方式3-修改表结构(添加索引)
alter table 表名 add unique [索引名] (列名)

7.2.2.2 删除索引

-- 方法1
drop index index_card_id on student2 

-- 方法2
alter table student2 drop index index_phone_num

7.3 组合索引

-- 创建索引的基本语法-- 普通索引
create index indexname on table_name(column1(length),column2(length)); 

-- 操作-删除索引
 drop index index_phone_name on student; 
 
-- 创建索引的基本语法-- 唯一索引
create  unique index index_phone_name on student(phone_num,name); 

7.4 全文索引

全文索引的关键字是fulltext，MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。这两个的默认值可以使用以下命令查看:

show variables like '%ft%';

参数解释如下表

操作如下

-- 修改表结构添加全文索引
alter table t_article add fulltext index_content(content)
 
-- 直接添加全文索引
create fulltext index index_content on t_article(content);

使用全文索引使用 match 和 against 关键字，格式:

match (col1,col2,...)  against(expr [search_modifier])

7.5 空间索引

• MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型

• 空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

• MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。

• 创建空间索引的列，必须将其声明为NOT NULL。

例如：

create table shop_info (
  id  int  primary key auto_increment comment 'id',
  shop_name varchar(64) not null comment '门店名称',
  geom_point geometry not null comment '经纬度',
  -- 空间索引
  spatial key geom_index(geom_point)
);

8 MySQL的存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎

可以使用 SHOW ENGINES 命令 可以查看Mysql的所有执行引擎我们 可以看到默认的执行引擎是innoDB，支持事务，行级锁定和外键。

• MyISAM：Mysql 5.5之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务

• InnoDB：事务型速记的首选引擎，支持ACID事务，支持行级锁定，MySQL5.5成为默认数据库引擎

• Memory： 所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在MYSQL重新启动是会丢失。

• Archive ：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive 拥有高效的插入速度，但其对查询的支持相对较差

• Federated ：将不同的 MySQL 服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用

• CSV ：逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .csv 文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV 存储引擎不支持索引。

• BlackHole： 黑洞引擎，写入的任何数据都会消失，一般用于记录 binlog 做复制的中继

• ERFORMANCE_SCHEMA存储引擎该引擎主要用于收集数据库服务器性能参数。

• Mrg_Myisam Merge存储引擎，是一组MyIsam的组合，也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作。

一些引擎用的操作如下：

-- 查询当前数据库支持的存储引擎：
show engines;
 
-- 查看当前的默认存储引擎：
show variables like ‘%storage_engine%’;

-- 查看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): 
show create table student; 
 
-- 创建新表时指定存储引擎：
create table(...) engine=MyISAM;
 
-- 修改数据库引擎
alter table student engine = INNODB;
alter table student engine = MyISAM;

9 MySQL的事物

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎

可以使用 SHOW ENGINES 命令 可以查看Mysql的所有执行引擎我们 可以到 默认的执行引擎是innoDB 支持事务，行级锁定和外键。

在MySQL中的事务（Transaction）是由存储引擎实现的，在MySQL中，只有InnoDB存储引擎才支持事务。事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。事务用来管理 DDL、DML、DCL 操作，比如 insert,update,delete 语句，默认是自动提交的。

9.1 事物的操作

• 开启事务：Start Transaction

  • 任何一条DML语句(insert、update、delete)执行，标志事务的开启

  • 命令：BEGIN 或 START TRANSACTION

• 提交事务：Commit Transaction

  • 成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步

  • 命令：COMMIT

• 回滚事务：Rollback Transaction

  • 失败的结束，将所有的DML语句操作历史记录全部清空

  • 命令：ROLLBACK

之前的所有SQL操作其实也有事务，只是MySQL自动帮我们完成的，每执行一条SQL时MySQL就帮我们自动提交事务，因此如果想要手动控制事务，则必须关闭MySQL的事务自动提交。在MySQL中直接用 SET 来改变 MySQL 的自动提交模式:

select @@autocommit;
set autocommit=0 禁止自动提交 
set autocommit=1 开启自动提交 

9.2 事物的隔离级别

• 读未提交(Read uncommitted)：一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证,会造成脏读。

• 读已提交(Read committed)：一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生，会造成不可重复读。

• 可重复读(Repeatable read)：就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生，但是会造成幻读。

• 串行(Serializable)：是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

Mysql的默认隔离级别是Repeatable read。

-- 查看隔离级别 
show variables like '%isolation%'; 

-- 设置隔离级别
/*
set session transaction isolation level 级别字符串
级别字符串：read uncommitted、read committed、repeatable read、serializable
*/
-- 设置read uncommitted
set session transaction isolation level read uncommitted;
 
-- 设置read committed
set session transaction isolation level read committed;
 
-- 设置repeatable read
set session transaction isolation level repeatable read;
 
-- 设置serializable
set session transaction isolation level serializable;

事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据 称为幻读

如果事务A 按一定条件搜索， 期间事务B删除了符合条件的某一条数据，导致事务A 再次读取时数据少了一条。这种情况归为不可重复读

10 MySQL的锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁有以下几种分类

1. 从对数据操作的粒度分 ：

• 表锁：操作时，会锁定整个表。

• 行锁：操作时，会锁定当前操作行

2. 从对数据操作的类型分：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

• 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

10.1 MySQL锁的特点

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

MySQL锁的特性可大致归纳如下 ：

仅从锁的角度来说：

• 表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；

• 行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

10.2 MyISAM表锁

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁

-- 加读锁 ： 
lock table table_name read; 
-- 加写锁 ： 
lock table table_name write；

表锁特点：

• 对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

• 对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；

此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

10.3 InnoDB行锁

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是 采用了行级锁。InnoDB 实现了以下两种类型的行锁：

• 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

• 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；而对于普通SELECT语句，InnoDB不会加任何锁；可以通过以下语句显示给记录集加共享锁或排他锁 。

-- 共享锁（S）：
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 
-- 排他锁（X) ：
SELECT * FROM table_name WHERE ... FOR UPDATE