MySQL知识点总结

已于 2023-09-09 15:17:26 修改

阅读量507

点赞数 1

文章标签： mysql 数据库

于 2023-09-04 21:02:48 首次发布

本文链接：https://blog.csdn.net/c15531440023/article/details/132676796

版权

使用命令行和MySQL默认的客户端登陆时需要的参数：

1.SQL基本概念

SQL（Structured Query Language）是结构化查询语言的简称，它是一种数据库查询和程序设计语言，同时也是目前使用最广泛的关系型数据库操作语言。在数据库管理系统中，使用SQL语言来实现数据的存取、更新等功能。

2.SQL作用

是所有关系型数据库的统一操作规范，不同关系型数据库都支持SQL
所有的关系型数据库都可以使用SQL
不同数据库之间的SQL有一些区别（方言）

3.SQL语法规范

SQL语句可以单行或者多行书写，以；结尾（Navicat中可以不写；）
可以使用空格或者缩进增加语句的可读性
MySQL中使用SQL不区分大小写，一般数据库名，表名，列名小写

4.SQL分类

数据查询语言（Data Query Language，DQL）：

DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。

select
from
where
group by
having
order by
limit

执行顺序：

from---->where---->group by---->having---->select---->order by---->limit

数据操作语言（Data Manipulation Language，DML）：

DML主要用于对数据增加、修改和删除的操作。主要包括：

INSERT: 增加数据
UPDATE: 修改数据
DELETE: 删除数据

数据定义语言（Data Definition Language，DDL）：

DDL主要用针对是数据库对象（数据库、表、索引、视图）进行创建, 修改和删除操作。主要包括：

CREATE: 创建
ALTER: 修改
DROP: 删除

数据控制语言（Data Control Language，DCL）：

DCL用来授予或回收访问数据库的权限。

主要包括：

GRANT: 授予用户权限
REVOKE: 回收授予的权限。

事务控制语言（Transaction Control Language，TCL）：

TCL用于数据库的事务管理。

主要包括：

START TRANSACTION: 开启事务
COMMIT: 提交事务
ROLLBACK: 回滚事务

注意：

数据操纵语言DML（insert、update、delete）针对表中的数据；

而数据定义语言DDL（create、alter、drop）针对数据库对象，比如数据库database、表table、索引index、视图view、存储过程procedure；

5.常用SQL语句的举例

SQL：结构化查询语言。
关系型数据库的操作标准。

SQL中常见的数据类型：

        1.整数类型：
           int(长度限制)：
               长度限制可指定也可不指定
               指定了长度限制，超过指定的限制，自动扩充

       2.浮点类型：
           double(m,n)：
               长度限制需要指定
               指定了长度限制，不可以超过指定的限制
               m整体的长度
               n小数的长度

       3.字符类型：
           char(长度限制)：
               长度限制需要指定
               指定了长度限制，不可以超过指定的限制
               直接分配指定的空间
           varchar(长度限制)
               长度限制需要指定
               指定了长度限制，不可以超过指定的限制
               根据内容动态分配空间

       4.日期与时间类型：
               date：日期
               datetime：日期+时间
               timestamp：日期+时间

6.DDL:数据定义语言

操作数据库：

创建数据库—— create database 库名;

删除库—— drop database 库名;

选择库—— use 库名;

操作表:

                创建表
                   creat table 表名(
                       字段名(列名) 数据类型,

                       ...
                   )

               删除表
                   drop table 表名;

               操作表
                   1.修改表名
                       rename table 原表名 to 新表名;
                   2.修改字段类型
                       alter table 表名 modify 字段名新数据类型;
                   3.修改字段名
                       alter table 表名 change 原字段名新字段名新数据类型;
                   4.添加字段
                       alter table 表名 add 字段名数据类型; -- 添加到末尾
                       alter table 表名 add 字段名数据类型 first; -- 添加到头部
                       alter table 表名 add 字段名数据类型 after 字段名; -- 添加指定字段后
                   5.删除字段
                       alter table 表名 drop 字段名
                   6.查看所有表
                       show tables;
                   7.查看表结构
                       desc 表名;
                   8.查看建表语句
                       show create table 表名;

7.DML:数据操作语言

插入数据：

-- 语法格式：
-- 1. 插入部分或全部字段
insert into 表名(字段名1,字段名2,...,字段名n) values(值1,值2,...,值n);
-- 2. 插入全部字段
insert into 表名 values(值1,值2,...,值n);

删除数据：

-- 语法格式
delete from 表名 where 条件;
delete from 表名; -- 慎用：删除表中的所有数据
truncate table 表名; -- 慎用：删除表中的所有数据

修改数据：

-- 语法格式
update 表名 set 字段1=值1,字段2=值2,...,字段n=值n where 条件;
update 表名 set 字段1=值1,字段2=值2,...,字段n=值n; -- 慎用：不加条件会修改所有的数据

8.表的约束

约束的作用

可以对表中的数据做一些限制, 从而保证数据的正确性, 有效性, 和完整性。

违反约束的不正确数据是无法插入到表中的。

约束分类：

约束名	关键字	作用
主键	primary key	不可重复, 不能为空，即唯一 + 非空
唯一	unique	不可重复
非空	not null	不能为空
默认值	default	赋予默认值
检查	check	取值范围
外键	foreign key	表与表之间的关系

1.添加主键约束

-- 语法格式：
字段名 字段类型 primary key

-- 案例1：创建一个带有主键的emp表 字段 eid int  ename varchar(10)  esex char(1)
-- 方式一
create table emp(
	eid int primary key,  -- 设置主键 非空唯一
	ename varchar(10),
	sex char(1)
);

-- 方式二

create table emp(
	eid int,
	ename varchar(10),
	sex char(1),
	primary key(eid)  -- 指定主键为eid字段
);

-- 方式三

create table emp(
	eid int primary key,
	ename varchar(10),
	sex char(1)
);
-- 创建表时不指定主键, 然后通过DDL语句进行设置
alter table emp add primary key(eid);

2.删除主键约束

-- 使用DDL 删除表中的主键约束
alter table emp drop primary key;

-- 查看表结构
desc emp;

-- 删除唯一约束(了解)
-- 添加了唯一约束为 eid int not null, 通过设置字段属性删除唯一约束 
alter table emp modify eid int;

3.主键自增

-- 语法格式: 
关键字 auto_increment	 表示自增长(字段类型必须为整数类型)

-- 案例：为emp表eid字段添加主键约束, 并设置为自增
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10),
	sex char(1)
);
-- 测试主键自增
insert into emp values(null,'张三','男');
insert into emp values(default,'李四','男');
insert into emp(ename, esex) values('王五', '男');

4.非空约束

-- 语法格式: 字段名 字段类型 not null

-- 案例：创建emp表，eid为主键约束,自增，ename为非空约束
-- 删除存在的emp表 
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	sex char(1)
);
-- 测试非空约束
insert into emp values(default,null,'n');
-- 会出现异常 Column 'ename' cannot be null  ename不能为空

5.唯一约束

-- 语法格式： 
字段名 字段类型 unique

-- 案例：emp表 eid 主键约束,自增  ename 非空约束 esex唯一约束
drop table emp; -- 删除存在的emp表 
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	sex char(1) unique
);
-- 测试唯一约束 添加两个性别为男
insert into emp values(default,'zs','男');
insert into emp values(default,'lisi','男');
--出现异常 Duplicate entry 'n' for key 'emp.esex'  esex重复

6.默认值约束

-- 语法格式： 字段名 字段类型 default '值'

-- 案例：emp表 eid 主键约束,自增  ename 非空约束 esex默认值'男'
-- 删除存在的emp表 
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	sex char(1) default '男'
);
-- 查看emp表结构 
desc emp;
-- 测试默认值约束
insert into emp values(default,'zs',default);

-- 错误用法
insert into emp values(default,'zs',null);

7.检查约束

字段名 字段类型 check(字段='值' or 字段='值')
字段名 字段类型 check(字段>n or 字段<m)
字段名 字段类型 check(字段>n and 字段<m)

9.DQL查询表中数据

数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。

1.简单查询

select 字段名, 字段名, ... from 表名;
       注意：
           1.查询所有字段可以使用*代替所有字段
           2.查询部分字段，必须手动指定查询的字段
           3.查询的字段可以进行数学计算，函数操作，...

       为字段定义别名，仅改变展示出来的字段名，数据库中的字段名不会改变。

2.条件查询

select 字段名, 字段名, ... from 表名 where 条件;
       执行顺序：
           1.从哪个表查询 from
           2.按照指定条件过滤 where
           3.指定返回的字段值 select
       条件：
                   > < >= <= != <>
                   and && 多个条件同时成立
                   between ... and ... 区间值（前后包含）
                   or || 多个条件成立任意一个即可
                   in(值,值,...) 等于任意一个值即可
                   注意：
                       值为null，判断时，is null is not null

通配符	说明
%	表示任意多个字符
_(下划线)	表示任意一个字符

例如：

#1 查询含有'八'字的员工信息
select * from emp where ename like '%八%';
 
#2 查询以'孙'字开头的员工信息 
select * from emp where ename like '孙%';

#3 查询第二个字为'兔'的员工信息 
select * from emp where ename like '_兔%';

#4 查询没有部门的员工信息 
select * from emp where dept_name is null;

#5 查询有部门的员工信息
select * from emp where dept_name is not null;

3.排序

--通过order by子语句, 可以将查询出的结果进行排序(排序只是显示效果, 并不会影响真实的数据）。
-- 语法格式：
select 字段名 from 表名 [where 条件] order by 字段名[asc | desc];
-- asc  升序(默认)
-- desc 降序

3.1单列排序

只按照某一个字段进行排序。

案例:

查询所有的员工信息, 使用salary进行排序

-- 升序排序(默认 asc)
select * from emp order by salary;

-- 降序排序(desc)
select * from emp order by salary desc;

3.2组合排序

同时对多个字段进行排序, 如果第一个字段相同, 就按照第二个字段排序, 以此类推。

案例:

查询所有的员工信息, 使用salary升序排列,salary相同按照入职日期降序排序。

-- 组合排序
select * from emp order by salary, hire_date desc;

4.函数

4.1单行函数

字符处理函数

#1 查询emp表所有数据, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x
select concat('编号:', eid), concat('姓名:', ename), concat('性别:', sex) from emp;

#2 查询emp表所有数据, 将ename第二个字符都换为 某
select eid, insert(ename, 2, 1, 某'), sex from emp;

#3 查询emp表所有数据, 显示ename的长度
select eid, ename, length(ename), sex from emp;

#4 查询emp表所有数据, 将 ename有英文的改为都是大写
select eid, ename, upper(ename), sex from emp;

#5 查询emp表所有数据, 将 ename有英文的改为都是小写
select eid, ename, lower(ename), sex from emp;

#6 查询emp表所有数据, ename只显示姓
select eid, ename, substring(ename, 1, 1), sex from emp;

#1 查询emp表所有数据, 薪资 >= 10000 高工资  其他 低工资
select eid, ename, salary, if(salary >= 10000, '高工资', '低工资') from emp;

#2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k)
select eid, ename, salary, salary * 12+30000 '年薪' from emp;  -- 需要考虑null
select eid, ename, salary, ifnull(salary, 0) * 12+30000 '年薪' from emp;

#3 查询emp表所有数据, 薪资 >=3000 加把劲  >=5000 加油哦  >=9000 坚持住 >= 15000 优秀  其他 不及格
select eid, ename, salary, 
	case
		when salary >= 15000 then '优秀'
		when salary >= 9000 then '坚持住'
		when salary >= 5000 then '加油哦'
		when salary >= 3000 then '加把劲'
		else '努力奋斗吧骚年'
	end
from emp;

4.2多行函数

#1 查询员工的总数 
-- 使用某个字段查询，聚合函数会忽略null, 需要注意为null的字段
select count(字段名) from 表名;  
-- 所有字段匹配查询
select count(*) form 表名; 
-- 增加一列
select 1 from 表名;	
-- 效率更高推荐使用		  
select count(1) from 表名;

#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值, 显示为总薪水, ... 
select 
	sum(salary) '总薪水',
	max(salary) '最高薪水',
	min(salary) '最小薪水',
	avg(salary) '平均薪水'
from 表名;

#3 查询薪水大于4000员工的个数 
select count(1) from 表名 where salary > 4000;

#4 查询部门为'教学部'的所有员工的个数 
select count(1) from 表名 where dept_name = '教学部';

#5 查询部门为'市场部'所有员工的平均薪水
select avg(salary) from emp where dept_name = '市场部';

#6 查询部门的个数
select dept_name from emp;  -- 9个
select count(dept_name) from emp; -- 8个
-- 部门去重之后， 统计个数
select count(distinct dept_name) from emp; -- 3个

5.分组

分组查询指的是使用group by语句, 对查询的信息进行分组, 相同数据作为一组。

--语法格式
select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件];

# 按照性别分组查询
select * from emp group by sex;    -- 能查到结果, 但是没有意义
select sex from emp group by sex;  -- 正确操作

注意事项:

分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作

查询其他字段没有意义

6.过滤方式（where和having的区别）

7.limit关键字

作用:

limit是限制的意思, 限制返回的查询结果的函数(通过limit函数，控制查询返回多少行数据)
limit 语法是 MySql的方言, 用来完成分页

语法格式：
select 字段1, 字段2 ... from 表名 limit offset, length;

参数说明1) offset 起始行数, 从0开始, 如果省略则默认从0开始, 0代表MySQL中第一条数据2) length 返回的行数

8.经典面试题 ---MySQL行列转置/MySQL行列转换(笔试题)

数据准备
create table student(
	id int(11) primary key auto_increment,
	name varchar(20),
	subject varchar(20),
	score double
);

insert into student values(1,'张三','语文',20);
insert into student values(2,'张三','数学',30);
insert into student values(3,'张三','英语',40);
insert into student values(4,'李四','语文',50);
insert into student values(5,'李四','数学',60);
insert into student values(6,'李四','英语',70);

方式一：使用if(表达式，值1，值2)函数。如果表达式成立返回值1，否则返回值2。其中值可以是列。

select 
	max(if(subject='语文',score,0)) 语文,
	max(if(subject='数学',score,0)) 数学,
	max(if(subject='英语',score,0)) 英语,
	name 
from student group by name;

方式二：使用case 条件 when 条件取值 then 结果 ... else 结果 end

select 
	max(case subject when '语文' then score else 0 end) 语文,
	max(case subject when '数学' then score else 0 end)  数学,
	max(case subject when '英语' then score else 0 end)  英语,
	name 
from student group by name;

10.SQL语句执行流程

10.1 执行流程图示

10.2 各个组件介绍

连接管理与安全验证：MySQL有连接池（Connection Pool）管理客户端的连接。客户端连接后会验证用户名、密码、主机信息等
缓存（Cache&Buffer）：缓存中存储了SQL命令的HASH，直接比对SQL命令的HASH和缓存中key是否对应，如果对应，直接返回结果，不再执行其他操作。由于缓存的是SQL的HASH，所以根据Hash特性SQL中空格等内容必须完全一样。缓存里面包含表缓存、记录缓存、权限缓存等。查询语句执行完成后会把查询结果缓存到缓存中。在MySQL中查询缓存默认不开启。考虑到查询缓存性能瓶颈问题，从MySQL8开始已经不支持查询缓存了。
解析器（Parser）主要作用是解析SQL命令。将SQL命令分解成数据结构（解析树），后续的操作都是基于这个结构的。如果在分解过程中遇到错误，出现SQL解析错误。解析时主要检查SQL中关键字，检查关键字是否正确、SQL中关键字顺序是否正确、引号是否对应是否正确等。
预处理器：根据解析器的解析树，进一步检查表是否存在、列是否存在、名字和别名是否有歧义等。
优化器（Optimizer）：根据官网说明在执行SQL查询时会根据开销自动选择最优查询方案。采用“选择-投影-连接”的策略。先选择where中的行数。同时先选择要选择的列，而不是全部列，最后把内容合并到一起。
执行器：包含执行SQL命令。获取返回结果。生成执行计划等。
存储引擎：访问物理文件的媒介

10.3 执行流程详细说明

客户端向服务器端发送SQL命令和连接参数
服务器端连接模块连接并验证
缓存模块解析SQL为Hash并与缓存中Hash表对应。如果有结果直接返回结果，如果没有对应继续向下执行。如果是MySQL 8 是没有查询缓存的。
解析器解析SQL为解析树，检查关键字相关问题，如果出现错误，报SQL解析错误。如果正确，继续执行
预处理器对解析树继续处理检查表、列别名等，处理成功后生成新的解析树。
优化器根据开销自动选择最优执行计划，生成执行计划
执行器执行执行计划，访问存储引擎接口
存储引擎访问物理文件并返回结果
如果开启查询缓存，缓存管理器把结果放入到查询缓存中。
返回结果给客户端

11.多表

11.1 概述

实际开发中, 一个项目通常需要很多张表才能完成。

11.2 单表存在的问题

冗余, 同一个字段出现大量重复的数据。例如：

11.3 解决方案

设计为两张表

1) 多表方式设计 employee 员工表: eid ename age

department 部门表: depid, dep_name, dep_location

11.4 多表上的设计问题

当我们在员工表的dep_id里面输入了不存在的部门编号, 数据依然可以添加, 显然这是不合理的。

例如:

  插入一条 不存在部门的数据 
  insert into employee values(default, '张亿万', 20, 100),

应该保证员工表所添加的dep_id, 必须在部门表dep_id中存在。

解决方案:

使用外键约束, 约束员工表中的dep_id必须在部门表dep_id中存在。

11.5 外键约束

外键

外键指的是在主表中与从表的主键对应的的那个字段, 如员工表的dep_id, 就是外键。
外键约束

使用外键约束可以让两张表之间产生一个对应关系, 从而保证主从表数据的完整性。

11.6 创建外键约束

语法格式:

1. 新建表时添加外键约束
  constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段); 
2. 为已创建好的表添加外键约束
 alter table 从表名 add constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段名);

11.7 删除外键约束

语法格式:

    alter table 从表名 drop foreign key 外键约束的名称;

11.8 外键约束的注意事项

1) 从表的外键类型必须和主表的主键类型保持一致

2) 添加从表数据时

从表中添加的外键值, 必须在主表的主键中存在

3) 删除和变更数据主表数据时

先删除从表中的数据或将外键设置为null, 再删除主表中的数据

4) 通过navicat设置外键约的束变更和删除的级联操作

12.多表关系设计

注意事项

①一对多建表原则

        在从表(多方)创建一个字段, 该字段作为外键指向主表的主键

②多对多关系建表原则

        多对多的关系不能直接处理, 需要创建第三张表, 也称为中间表, 中间表至少两个字段,这两个字段分别作为外键指向各自一方的主键, 实际就是将多对多拆分为两个一对多。

③一对一关系

        在实际的开发过程中应用不多, 因为一对一的关系可以设计为一张表。

13.多表查询

13.1 什么是多表查询

DQL: 查询多张表, 获取到需要的数据

13.2 笛卡尔积

交叉连接（CROSS JOIN）是对两个或者多个表进行笛卡儿积操作，所谓笛卡儿积就是关系代数里的一个概念，表示两个表中的每一行数据任意组合的结果。

笛卡尔积不管是否匹配，都连接。没有实际意义，有理论意义。

笛卡尔积便于理解连接查询的原理。

语法格式：

    select 字段名 from 表1 cross join 表2;

例如：使用交叉连接, 查询分类表与商品表:

select * from products cross join category;

13.3 多表查询的分类

内连接：通过条件匹配两张表中的数据，能匹配就显示，不能匹配不显示。
               SQL92：select 字段,... from 表1,表2 where 条件;
               SQL99：elect 字段,... from 表1 [inner] join 表2 on 条件;
               实现：
                   1.不同的表进行内连接
                   2.同一张表(自连接)进行内连接
                   3.判断条件为等值判断，等值内连接
                           SQL92：select 字段,... from 表1,表2 where 字段1=字段2;
                           SQL99：select 字段,... from 表1 join 表2 on 字段1=字段2;
                   4.判断条件为非等值判断，非等值内连接
                           SQL92：select 字段,... from 表1,表2 where 字段1 between 字段2 and 字段3;
                           SQL99：select 字段,... from 表1 join 表2 on 字段1 between 字段2 and 字段3;

外连接：1.左外连接：以左表为主，左表中的数据全部显示，右表没有匹配的数据以null填充。
                   select 字段,... from 表1 left [outter] join 表2 on 条件;
                   实现：
                   1.不同的表进行左外连接
                   2.同一张表(自连接)进行左外连接
                   3.判断条件为等值判断，等值左外连接
                           SQL99：select 字段,... from 表1 left join 表2 on 字段1=字段2;
                   4.判断条件为非等值判断，非等值左外连接
                          SQL99：select 字段,... from 表1 left join 表2 on 字段1 between 字段2 and 字段3;
               2.右外连接：以右表为主，右表中的数据全部显示，左表没有匹配的数据以null填充。
                   select 字段,... from 表1 right [outter] join 表2 on 条件;
                   实现：
                   1.不同的表进行右外连接
                   2.同一张表(自连接)进行右外连接
                   3.判断条件为等值判断，等值右外连接
                       SQL99：select 字段,... from 表1 right join 表2 on 字段1=字段2;
                   4.判断条件为非等值判断，非等值右外连接
                      SQL99：select 字段,... from 表1 right join 表2 on 字段1 between 字段2 and 字段3;
               3.MySQL不支持全外连接
                   左右表中数据都展示，没有匹配填充null
                   左外连接
                   union
                   右外连接
                   将左外连接的结果和右外连接的结果进行合并，合并时去重。

例如：


-- 创建表
create table emp1(
	empno int primary key auto_increment comment '员工编号',
	ename varchar(10) comment '员工姓名',
	mgr int comment '领导编号',
	hiredate date comment '入职日期',
	sal double(7,2) comment '月薪'
);


-- 插入数据
INSERT INTO `emp1` VALUES (1, '宋江', 0, '2021-12-28', 9000.00);
INSERT INTO `emp1` VALUES (2, '林冲', 1, '2021-10-08', 3500.00);
INSERT INTO `emp1` VALUES (3, '李逵', 1, '2022-01-05', 2500.00);
INSERT INTO `emp1` VALUES (4, '孙二娘', 2, '2021-12-30', 2000.00);
INSERT INTO `emp1` VALUES (5, '时迁', 2, '2021-12-29', 7800.00);

-- 自连接
-- 查询所有的员工及员工的领导
-- 自连接的内连接
select e2.empno, e2.ename, e1.empno, e1.ename from emp1 e1, emp1 e2 where e1.empno = e2.mgr;
select e2.empno, e2.ename, e1.empno, e1.ename from emp1 e1 join emp1 e2 on e1.empno = e2.mgr;

-- 自连接的右外连接
select e1.empno, e1.ename, e2.empno, e2.ename from emp1 e1 right join emp1 e2 on e1.empno = e2.mgr;

-- 创建工资等级表
create table salgrade(
	grade char(1) primary key comment '等级',
	losal double comment '此等级的最低工资',
	hisal double comment '此等级的最高工资'
);

-- 插入数据
INSERT INTO `salgrade` VALUES ('A', 0, 3000);
INSERT INTO `salgrade` VALUES ('B', 3001, 8000);
INSERT INTO `salgrade` VALUES ('C', 8001, 10000);
INSERT INTO `salgrade` VALUES ('D', 10001, 50000);


-- 内连接，非等值连接
select * from emp1, salgrade where sal between losal and hisal;
select * from emp1 join salgrade on sal between losal and hisal;

-- 左外连接，非等值连接
select * from emp1 left join salgrade on sal between losal and hisal;

-- SQL99 实现两个不同表的内连接，左外连接，右外连接
-- 内连接：匹配就显示，不匹配不显示
select * from category c inner join products p on c.cid=p.cid;
select * from category c join products p on c.cid=p.cid;

-- 左外连接：左表数据全部显示,右表没有匹配的数据以null填充
select * from category c left outer join products p on c.cid=p.cid;
select * from category c left join products p on c.cid=p.cid;

-- 右外连接：右表数据全部显示,左表没有匹配的数据以null填充
select * from category c right outer join products p on c.cid=p.cid;
select * from category c right join products p on c.cid=p.cid;


-- 全外连接
select * from category c left join products p on c.cid=p.cid
union
select * from category c right join products p on c.cid=p.cid;

14.子查询

1.子查询概念

一条select查询语句的结果作为另一条select语句的一部分

2. 子查询的特点

子查询一般作为查询条件使用

使用子查询，必须将子查询放在小括号中使用

一条SQL语句含有多个select，一般是先执行子查询，再执行外查询

3. 单行子查询

单行子查询: 查询出的结果为一列一行(一个数据) 如: 最高，最低，平均等，可以使用判断符号如: > | < | = | != 等

 语法格式:
select 字段 from 表 where 字段 判断符号 (子查询);


例如：
#1 查询价格最高的商品信息
-- 1.查询出商品最高的价格
select max(price) from products;  -- 5000
select * from products where price = 5000;
-- 2.将查询出的最高价格作为条件查询，获取商品信息 
select * from products where price = (select max(price) from products);

4.多行子查询

多行子查询: 查询出的结果为一列多行(多个数据) 如: 化妆品类别下的商品pid，可以使用判断符号如: in all any

in : 等于任意一个

使用方式:

in(值1，值2 ...)
all: 所有

使用方式:

如: 字段 > | < all(值1，值2 ...) 大于所有的值
any: 任意一个

使用方式

如: 字段 > | < any(值1，值2 ...) 大于任意一个值

如: 字段 = any(值1，值2 ...) 等于任意一个值效果等同于 in

语法格式：
select 字段 from 表 where 字段 判断符号(in | any | all) (子查询);

例如：
#1 查询化妆品类别中的商品价格和鞋服类别中的商品价格一样的商品信息
-- 1.查询化妆品类别下所有商品的价格
select price from products where cid = 'c003'; -- 800 200
-- 2.查询鞋服类别下所有商品的价格
select price from products where cid = 'c002'; -- 800 200 300 2000
-- 3.化妆品中商品的价格和鞋服中商品的价格进行条件判断
select * from products 
where cid = 'c003' and price in (select price from products where cid = 'c002');

注意事项:

单行子查询: 查询出的结果为一列一行(一个数据)，可以使用 > < = !=

多行子查询: 查询出的结果为一列多行(多个数据)，需要使用in all any

15.存储引擎

数据库存储引擎：是数据库管理系统中的重要组成部分。数据库管理系统（DBMS）使用存储引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。

MySQL的核心就是插件式存储引擎。

MySQL 可以通过 show engines 查看所有支持的存储引擎。

在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。上面Support列的值表示某种引擎是否能启用：YES表示已经启用、NO表示没有启用、DEFAULT表示该引擎为当前默认的存储引擎。

各个引擎的介绍

InnoDB 默认的存储引擎，也是所有存储引擎中唯一支持事务、XA协议的存储引擎。
MyISAM 基于ISAM（Indexed Sequential Access Method目前已经废弃）的存储引擎，特点是查询效率较高。但不支持事务和容错性。
MEMORY 纯内存型型存储引擎。所有数据都在内存中，硬盘只存储.frm文件。所以当MySQL宕机或非法关闭时只生效表结构。当然了，由于所有数据都在内存上，所以相对来说性能较高。
MRG_MYISAM 以前也叫MERGE，简单理解就是对MyISAM表做了特殊的封装，对外提供单一访问入口，减少程序的复杂性。
ARCHIVE存储引擎主要用于通过较小的存储空间来存放过期的很少访问的历史数据。ARCHIVE表不支持索引，通过一个.frm的结构定义文件，一个.ARZ的数据压缩文件还有一个.ARM的meta信息文件。由于其所存放的数据的特殊性，ARCHIVE表不支持删除，修改操作，仅支持插入和查询操作。
BLACKHOLE 俗称“黑洞”存储引擎。是一个非常有意思的存储引擎。所有的数据都是有去无回。
CSV存储引擎实际上操作的就是一个标准的CSV文件，他不支持索引。起主要用途就是大家有些时候可能会需要通过数据库中的数据导出成一份报表文件，而CSV文件是很多软件都支持的一种较为标准的格式，所以我们可以通过先在数据库中建立一张CSV表，然后将生成的报表信息插入到该表，即可得到一份CSV报表文件了。
PERFORMANCE_SCHEMA 从MySQL 5.6新增的存储引擎。主要用于收集一些系统参数。

16.数据库事务控制(TCL)

16.1 事务的概念

常用的存储引擎有InnoDB（MySQL5.5以后默认的存储引擎）和MyISAM（MySQL5.5之前默认的存储引擎），其中InnoDB支持事务处理机制，而MyISAM不支持。

事务是一个整体，由一条或者多条SQL语句组成，这些SQL语句要么都执行成功，要么就失败，只要有一条SQL出现异常，整个操作就会回滚。

回滚: 就是事务运行的过程中发生了某种故障，或者SQL出现了异常，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部取消，回滚到事务开始时的状态。

16.2 MySQL中的事务操作

MySQL 中可以有两种方式进行事务的操作：

1) 自动提交事务(MySQL默认)

2) 手动提交事务

16.2.1 手动提交事务

1) START TRANSACTION 这个语句显式地标记一个事务的起始点。

2) COMMIT 表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。

3) ROLLBACK 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态。

16.2.2 手动提交事务的流程

1) 执行成功的情况：开启事务 -> 执行多条 SQL 语句 -> 成功提交事务执行

2) 失败的情况：开启事务 -> 执行多条 SQL 语句 -> 事务的回滚

16.3 事务的四大特性 ACID

原子性（Atomicity）一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性
一致性（Consistency）指事物必须是数据库从一个一致性状态到另一个一致性状态。也就是说一个事物执行之前和执行之后都必须处于一致性状态
隔离性（Isolation）事务的隔离性是指在并发环境中，并发的事务时相互隔离的，一个事务的执行不被其他事务干扰。即并发执行的各个事务之间不相互干扰
持久性（Durability）一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。即使发生系统崩溃或机器宕机等故障，只要数据库能够重新启动，那么一定能够将其恢复到事务成功结束的状态

16.3 MySQL事务的数据并发访问

一个数据库可能拥有多个访问客户端，这些客户端都可以并发方式访问数据库. 数据库的相同数据可能被多个事务同时访问，如果不采取隔离措施，就会导致各种问题，破坏数据的完整性。

16.3.1 并发访问产生的问题

16.3.2 四种隔离级别

通过设置隔离级别,可以防止上面的三种并发问题. MySQL数据库有四种隔离级别上面的级别最低，下面的级别最高。

✔ 会出现问题

✘ 不会出现问题

注意事项:

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

MVCC多版本控制机制，读取时采用快照读，读取的内容来源于快照，解决不可重复读，幻读。

多次读取过程中例如变更全部数据，读取最新的数据，造成幻读。

16.3.3 隔离级别相关命令

查看隔离级别
select @@transaction_isolation;
-- 设置隔离级别语法格式
set session transaction isolation level 隔离级别名称;
-- 如: 设置为读未提交
set session transaction isolation level read uncommitted;

 read uncommitted 	读未提交 
 read committed   	读已提交 
 repeatable read  	可重复读 
 serializable      	串行化

17.索引

17.1索引介绍

索引类似图书的目录，一种数据结构，通过索引可以快速的找到需要查询的内容。

MySQL官方文档中说明MySQL在500W~800W数据以上时查询性能可能下降，所以在大量数据时建立索引提升查询性能是非常有必要的。

InnoDB引擎：索引和数据都是存储在表名.idb文件中。

MyISAM引擎：索引和数据存储在不同的文件中，表名.MYD 存储数据，表名.MYI存储索引。

17.2 索引的结构

索引在数据库底层有两种结构：BTREE和HASH。默认使用的是BTREE。

17.3 HASH结构

Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。

缺点:

1) 哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表

2) 适合于精确的查找，也不适合范围查询

17.4 BTREE结构

BTree分为B-Tree和B+Tree，MySQL数据库索引采用的B+Tree，B+Tree是在B-Tree上做了优化改造。

17.4.1 B-Tree结构

索引值和data(数据)分布在整棵树结构中
每个节点可以存放多个索引值以及对应的data(数据)
树节点中的多个索引值从左到右升序排列

缺点:

所有的节点都存放数据，数据会占用空间，导致存放的索引变少。

17.4.2 B+Tree结构

非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
叶子节点包含了所有的索引值和data数据
叶子节点用指针连接，提高区间的访问性能

相比B-树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B-树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

17.5 索引的优点

为什么要创建索引？这是因为，创建索引可以大大提高系统的查询性能。

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
可以加速表和表之间的连接，特别是在实现数据的完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用查询优化器，提高系统的性能。

17.6 索引的缺点

虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面:

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

17.7 什么样的字段适合创建索引

一般来说，应该在具备下述特性的列上创建索引：

在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

建立索引，一般按照select的where条件来建立，

比如： select的条件是where f1 and f2，那么如果我们在字段f1或字段f2上建立索引是没有用的，只有在字段f1和f2上同时建立索引才有用等。

17.8 什么样的字段不适合创建索引

对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
对于那些定义为text，image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

17.9 索引分类

索引分为单列索引，组合索引，全文索引。

单列索引就是只是给某个列加索引；组合索引是给表中大于等于两个列添加索引。

17.9.1 单列索引

①主键索引

特点:

它是一种特殊的唯一索引，不允许有空值
在创建或修改表时添加主键约束即可，添加了主键约束就会自动创建主键索引
每个表只能有一个主键约束，所以一张表只能有一个主键索引

创建主键索引的方式:

 1. 创建表时指定主键约束
create table 表名(
   字段1 类型 primary key,
   ...
);

2. 为创建好表，但是并没有指定主键约束的表添加主键约束
alter table 表名 add primary key(字段名);

3. 查看某张表中的所有索引
show index from 表名;

②普通索引

特点:

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

普通字段: 非主键约束，非外键约束，非唯一约束

创建索引的方式:

1. 创建表时创建普通索引
create table 表名(
   字段1 类型,
   ...,
   index [索引名称](字段名) -- 不指定索引名称，自动生成
);

 2. 为创建好的表添加普通索引
alter table 表名 add index [索引名称](字段名); -- 不指定索引名称，自动生成

3. 为创建好的表添加普通索引
create index <索引名称> on 表 (字段名); -- 必须指定索引名称

③唯一索引

特点:

与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引

创建索引的方式:

 创建表时指定唯一约束，会自动创建唯一索引
create table 表名(
   字段1 类型 unique,
   ...
);

 为创建好的表添加唯一索引
alter table 表名 add unique index [索引名称](字段名);

 为创建好的表添加唯一索引
create unique index <索引名称> on 表名(字段名);

17.9.2 组合索引

给表中大于等于两个列添加索引。

但是需要满足最左前缀，创建组合索引相当于创建了多个索引，一般把最常用的放在最左边。

-- 语法格式：
create index 索引名 on 表名(列1，列2...)

create index index3 on demo(col1,col2,col3)

create index index3 on demo(col1,col2)

17.9.3 全文索引

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引在MySQL5.6之前仅可用于 MyISAM 表，在MySQL5.7后InnoDB也支持，MySQL8中InnoDB也支持；

他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。

对于较大的数据集，将你的数据输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把数据输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

全文索引对中文支持不好，如果搜索中文就只能按照最左对照进行搜索。如果是英文就可以匹配中间。

①全文索引创建方式

-- 创建表
create table tb_fulltext(
	id int(11) primary key auto_increment,
	name varchar(100),
    address varchar(200),
    FULLTEXT index_name (name)
);
-- 方式2
ALTER TABLE table_name ADD FULLTEXT index_name(column);

②使用

创建好的全文索引需要配合match(列，列) against(‘内容’)使用。

match中列必须和创建全文索引的列一样。例如创建全文索引是(id,name)，match(name)无法使用全文索引，必须单独建立name列的全文索引。

insert into tb_fulltext values(1,'tony','beijing yizhuang');
insert into tb_fulltext values(2,'kevin','beijing yizhuang jingkaiqu');
insert into tb_fulltext values(3,'jordan','beijing daxing');

explain select * from tb_fulltext where match(name,address) against('yizhuang');
select * from tb_fulltext where match(name) against('yizhuang');// 执行报错

③against中内容有三种模式:

自然语言模式：IN NATURAL LANGUAGE MODE
布尔模式：IN BOOLEAN MODE
查询扩展模式：WITH QUERY EXPANSION

自然语言模式：拆分出来的关键字必须严格匹配。例如beijing只能通过beijing搜索，不能通过bei搜索

drop index ft on tb_fulltext;
create fulltext index ft on tb_fulltext(address);
select * from tb_fulltext where match(address) against('daxing');

布尔模式：支持特殊符号。即使对没有全文索引的列也可以进行搜索，但是非常慢。查询时必须从最左开始查询，例如：北京昌平，按照昌平无法查询。

# daxin* 可以搜索到内容。 daxin 不能搜索到内容
select * from tb_fulltext where match(address) against('daxin*' in boolean mode);

查询扩展：查询时进行扩展查询，发现和条件有关系的内容都查询出来

insert into tb_fulltext values(4,'kelvin','hebei baoding');
# 结果查询到三行，id为4的没有被查询出来。因为daxing相关的词条有beijing、所有包含beijing的都被查询出来了。
explain select * from tb_fulltext where match(address) against('daxing' with query expansion);

②④ 中文拆词器

由于中文是没有空格的，MySQL 从5.7.6开始内置ngram中文分词插件。可以设置把整个中文按照指定大小进行拆词。设置步骤如下：

在my.ini中[mysqld]下添加参数，设置拆词长度（根据自己的需求进行完成即可）

ngram_token_size=2

2.给address创建全文索引。注意后面的with parser ngram

create fulltext index index3 on ft(address)  with parser ngram;

17.9.3 聚集索引和非聚集索引

Innodb存储引擎：（索引和数据在同一个文件中）

       聚集索引 | 聚簇索引：并不是索引的分类，索引值和行数据存储在一起，数据会按照索引的顺序进行存储。
                                          主键索引为聚集索引的一种，也可以自定义聚集索引（很少自定义）。
                                          表中没有主键索引，自动找一个唯一非空的索引作为聚集索引，自动创建一个隐藏的字段作为聚集索引。

       非聚集索引 | 非聚簇索引 | 二级索引 | 辅助索引：并不是索引的分类，索引值和主键值存储在一起，根据索引值找到主键，
       根据主键找到行数据（回表查询）。

MyISAN存储引擎：（索引和数据在不同的文件中）
       非聚集索引：索引值和行数据的地址存储在一起。

17.10 索引优化

①为什么要优化索引

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点。

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE次数大于查询次数时，放弃索引。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

②优化案例

1.使用短索引（前缀索引）

对串列进行索引，如果可能应该指定一个前缀长度。

例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

CREATE  INDEX  index_name  ON  table_name (column(length));

2.索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

3.like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”（非前导模糊查询）可以使用索引。使用后，优化到range级别。

explain select * from teacher where address like '%oracle%';

4.不要在列上进行运算

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。

应该把计算放在业务代码上完成，而不是交给数据库。

5.范围列可以使用索引

范围条件有：<、<=、>、>=、between等。

范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。所以where中把最主要的查询条件放在第一个。

alter table teacher add column (age int(3));
alter table teacher add column (weight int(3));
select * from teacher;
update teacher set age=10,weight=90 where id = 1;
update teacher set age=20,weight=100 where id = 2;
update teacher set age=30,weight=110 where id = 3;

create index age_index on teacher(age);
create index weight_index on teacher(weight);

select * from teacher where age between 10 and 20 and weight between 90 and 100;

6.类型转换会导致索引无效

当列是文本类型时，把数值类型当作列的条件会弃用索引。

explain select * from teacher where name = 20;

总结：

索引的级别：const(主键查询) > ref > range > index(扫描全部索引) > all(全表扫描)

不要在where后的条件中进行列的运算和函数操作
模糊查询时，like‘%xxx%’，放弃使用索引二进行全表扫描。like‘xxx%’，可以使用索引索引级别为range。
类型转换会导致索引无效

MySQL只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的。

建议：一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

官方文档参考地址：MySQL :: MySQL 8.0 Reference Manual :: 8.3 Optimization and Indexes

18.count(*),count(列),count(1)的区别

该问题是一个常见的面试题目，大家了解区别即可。

对于InnoDB引擎count(*)和count(1)没有性能的区别，都是自动寻找主键列或唯一索引且非空约束。统计的时候都是统计包含null的行，所以效果都是返回表中数据的行数。

而count(列) 统计列中包含多少行数据，不包含NULL值。

统计说明：

在InnoDB引擎中count(*)和count(1)性能没有什么差别
count(列)需要看列和count(*)优化后的列情况，如果count(列)使用了非索引列，而表中包含索引列则count(*)更快。如果count（列）和count(*)优化后的是同一个列则性能没有什么差别。如果表中没有索引则count（列）和count(*)性能也没有什么差别。

19.视图