MYSQL

leslie..

已于 2024-12-05 17:31:46 修改

阅读量533

点赞数 4

文章标签： mysql 数据库

于 2024-05-18 10:02:46 首次发布

本文链接：https://blog.csdn.net/2301_76557773/article/details/132194889

版权

MySQL概述

什么是数据库?什么是数据库管理系统?什么是SQL?他们之间的关系是什么?

数据库:

英文单词bataBase,简称DB。按照一定的格式存储数据的组合.

顾名思义:存储数据的仓库,实际上就是一顿文件.这些文件中存储了具有特定格式的数据

数据库管理系统:

DataBaseManagement,简称DBMS。

数据库管理系统是专门用来管理数据库中数据的，数据库管理系统也可以对数据库当中的数据进行增删改查。

常见的数据库管理系统：

MySQL，Oracle，MS sqlserver ，DB2，sybase等....

SQL：结构化查询语言

程序员需要学习SQL语句，程序员通过编写SQL语句，然后DEMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。

SQL是一套标准，程序员主要学习就是SQL语句，这个SQL在MySQL中可以使用，同时在oracle中也可以使用，在DB2中也可以使用。

三者之间的关系？

数据库 ----执行-----> SQL语句 ------操作-----> 数据库(DB)

MySQL的下载安装以及启动

1.下载地址

下载地址:https://dev.mysql.com/downloads/windows/installer/8.0.html

安装教程: https://blog.csdn.net/hou_6006/article/details/122514756

2.MySQL启动和停止

net start 服务名称

net stop 服务名称

3.MySQL客户端连接

第一种方式 MySQL自带的客户端命令行

第二种打开cmd 命令行 win+R 输入cmd回车

在黑框中输入:mysql -uroot -p123456

-u user 的缩写

-p password

退出

exit

quit

SQL通用语法及分类

SQL语句语法:

1.SQL语句可以单行或多行书写，(默认)以分号结尾

2.SQL语句可以使用空格/缩进来增强语句的可读性

3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

4.注释:

单行注释:-- 注释内容或 # 注释内容(MySQL特有)

多行注释: /* 注释内容 */

SQL分类:

DDL:数据库操作

创建

直接指定数据库名进行创建默认数据库字符集为：latin1

create database 数据库名;

指定数据库名称，指定数据库的字符集一般都指定为 utf8;

create database 数据库名 character set 字符集;

create database 数据库名 charset utf8mb4;

为了避免错误

creat database if not exists 数据库名 default charset 字符集 collate

删除

drop database 数据库名

drop database if exists 数据库名

修改数据库字符集

alter database 数据库名 character 字符集;

查询

查询所有数据库

show databases;

选择查询当前数据库

select database();

查看指定表的建表语句

show create database 数据库名

使用/切换

use 数据库名

DDL:表操作

查询表

查询当前数据库所有表

show tables;

查询表结构

desc 表名

查询指定表的建表语句

show create table 表名;

创建表

create table 表名(

字段名数据类型 comment "注释别名",

字段名数据类型 comment "注释别名"

)comment "注释表名";

修改表名

rename table 旧表名 to 新表名

删除表

drop table 表名;

DDL:表-数据类型

常见数据类型

int 整型

double 浮点型

varchar 字符串型

char 字符型

date 时间

详细的数据类型（了解即可）

注意：MySQL中的 char类型与 varchar类型，都对应了 Java中的字符串类型，

区别在于：

char类型是固定长度的：根据定义的字符串长度分配足够的空间。

varchar类型是可变长度的：只使用字符串长度所需的空间

DDL:操作表的字段-修改&删除&添加

添加

向表中添加新的字段，关键字 add

alter table 表名 add 字段名字段类型 (长度)

删除

删除字段关键字 drop

alter table 表名 drop 字段名;

修改

修改表的字符集

alter able 表名 characterset 字符集

修改字段的数据类型关键字 modify

alter table 表名 modify 字段名新数据类型 (长度)

修改字段名和字段类型关键字 change

alter table 表名 change 旧字段新字段数据类型(长度)

DML:操作表中数据

DML主要是对数据进行增（insert）删（delete）改（update）

添加数据

第一种:按着表格写好的固定的字段写值

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

第二种:一次插入多个数据

insert into 表名 values(值1，值2...),(值3，值4...);

第三种:制定全部的字段和写值自定义

insert into 表名（字段名1，字段名2...） values(值1，值2...);

第四种:可以插入一部分数据不用全部

insert into 表名 (字段1,字段2) values(值1,值2);

删除数据

删除当前表格中的数据

delete from 表名;

删除表中某个数据

delete form 表名 where 字段名='值';

清空表中所有的数据,然后再重新创建一张一模一样的表

truncate table 表名

修改数据

修改一个数据要加条件 =不然就修改了所有

update 表名 set 要修改的那个字段 =新的值 where 字段名 ='值';

修改多个数据

update 表名 set 要修改的那个字段 =新的值 ,

要修改的那个字段 =新的值 where 字段名 ='值';

DQL:查询表中数据

DQL：查询语法

SELECT 
    字段列表(想要查询的字段)
FROM 
    表名列表 （来源表） 
WHERE 
    条件列表 
GROUP BY 男/女
    分组字段  
HAVING
    分组后条件
ORDER BY
    排序字段
LIMIT 
    分页限定

数据案例:

#创建员工表
  表名 emp
  表中字段：
  eid 员工id，int
  ename 姓名，varchar
  sex 性别，char
  salary 薪资，double
  hire_date 入职时间，date
  dept_name 部门名称，varchar
  
# 删除emp表
drop table if exists emp;
#创建员工表
CREATE TABLE emp(
  eid INT,
  ename VARCHAR(20),
  sex CHAR(1),
  salary DOUBLE,
  hire_date DATE,
  dept_name VARCHAR(20)
);
#添加数据
INSERT INTO emp VALUES(1,'孙悟空','男',7200,'2013-02-04','教学部');
INSERT INTO emp VALUES(2,'猪八戒','男',3600,'2010-12-02','教学部');
INSERT INTO emp VALUES(3,'唐僧','男',9000,'2022-09-08','教学部');
INSERT INTO emp VALUES(4,'白骨精','女',5000,'2022-10-07','市场部');
INSERT INTO emp VALUES(5,'蜘蛛精','女',5000,'2022-09-14','市场部');
INSERT INTO emp VALUES(6,'玉兔精','女',200,'2022-03-14','市场部');
INSERT INTO emp VALUES(7,'林黛玉','女',10000,'2019-10-07','财务部');
INSERT INTO emp VALUES(8,'黄蓉','女',3500,'2022-09-14','财务部');
INSERT INTO emp VALUES(9,'吴承恩','男',20000,'2022-03-14',NULL);
INSERT INTO emp VALUES(10,'孙悟饭','男', 10,'2020-03-14','财务部');
INSERT INTO emp VALUES(11,'兔八哥','女', 300,'2022-03-14','财务部');

简单查询:

查询当前表中所有的数据 *代表所有

select * from 表名;

查询指定字段

select 字段名 ,字段名 from 表名;

起别名关键字 as

select 字段名 as '别名' , 字段名 '别名 ' from 表名;

查询去重关键字 distinct

select distinct 字段名 from 表名;

查询指定的字段做简单运算使要某个字段+上多少多少

select 字段名,(比如工资的字段名+2000) as '别名' from 表名;

条件查询:

格式:

select * from 表名 where 字段名='值';

案例:

模糊查询:

模糊查询使用like关键字，可以使用通配符进行占位:

（1）_ : 代表单个任意字符

（2）% : 代表任意个数字符

查询含有所有信息以中间

select * from 表名 where 字段名 like '%值%';

查询开头

select * from 表名 where 字段名 like '值%';

查询第二个字

select * from 表名 where 字段名 like '_值%';

查询某个字段不为空的数据

select * from 表名 where 字段名 is not null

查询某个字段为空的数据

select * from 表名 where 字段名 is null;

排序查询

语法格式: 关键词 order by

select * from 表名 order by 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;

上述语句中的排序方式有两种，分别是：

ASC ：升序排列 （默认值）不写默认是ASC
DESC ：降序排列

注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

-- 默认升序排序 ASC

select * from 表名 order by 字段名 ;

降序排序

select * from 表名 order by 字段名 desc;

组合排序

select * from 表名 order by 字段名1 排序方式1 , 字段名2 排序方式2;

聚合函数

概念

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

聚合函数分类

SELECT
count(case when o.`status`=14 then 0 end )+
count(case when og.`status`=14 then 0 end ) as status

 from orders o
 LEFT JOIN order_goods og
   on o.id=og.order_id

聚合函数语法

select 函数名(字段名) from 表

练习

#1 查询员工的总数
#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
#3 查询薪水大于4000员工的个数
#4 查询部门为'教学部'的所有员工的个数
#5 查询部门为'市场部'所有员工的平均薪水

分组查询

语法:

select 字段列表 from 表名 [where分组前条件限定] group by 分组字段名[ having 分组后条件过滤];

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

练习

#1.查询所有部门信息 
#2.查询每个部门的平均薪资 
#3.查询每个部门的平均薪资, 部门名称不能为null

#1. 查询有几个部门 
SELECT dept_name AS '部门名称' FROM emp GROUP BY dept_name; 
#2.查询每个部门的平均薪资 
SELECT dept_name AS '部门名称', AVG(salary) AS '平均薪资' FROM emp GROUP BY dept_name;

#3.查询每个部门的平均薪资, 部门名称不能为null 
SELECT dept_name AS '部门名称', 
AVG(salary) AS '平均薪资' 
FROM emp 
WHERE dept_name IS NOT NULL GROUP BY dept_name;

# 查询平均薪资大于6000的部门.

分析:

1) 需要在分组后,对数据进行过滤,使用关键字 hiving

2) 分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

# 查询平均薪资大于6000的部门 
-- 需要在分组后再次进行过滤,使用 having 
SELECT dept_name , AVG(salary) FROM emp 
WHERE dept_name IS NOT NULL GROUP BY dept_name 
HAVING AVG(salary) > 6000 ;

where 和 having 区别：

执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

分页查询

大家在很多网站都见过类似的效果，如京东、百度、淘宝等。分页查询是将数据一页一页的展示给用户看，用户也可以通过点击查看下一页的数据。

接下来我们先说分页查询的语法。

语法

SELECT 字段1,字段2... FROM 表名 LIMIT offset , length; 


# limit offset , length; 关键字可以接受一个 或者两个 为0 或者正整数的参数 
# offset 起始行数, 从0开始记数, 如果省略 则默认为 0.
# length 返回的行数

注意：上述语句中的起始索引是从0开始

练习

# 查询emp表中的前5条数据
-- 参数1 起始值,默认是0 , 参数2 要查询的条数

select * from 表名 limit 5;

# 查询表中 从第4条开始,查询6条
-- 起始值默认是从0开始的.

select * from 表名 limit 3,6;

# 分页操作每页显示3条数据

select * from 表名 limit 0,3;

select * from 表名 limit 3,3;

select * from 表名 limit 6,3;

MySQL高级

1.约束

概念

1) 约束的作用:

对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性. 违反约束的不正确数据,将无法插入到表中

2) 常见的约束

分类

主键约束

学号唯一表示

身份证号唯一

不可重复唯一非空，用来表示数据库中的每一条记录

语法格式

字段名字段类型 primary key

# 方式1 创建一个带主键的表

create table 表名(

字段名类型 primary key,

字段名类型 (长度),

....

);

删除

drop table 表名;

-- 方式2 创建一个带主键的表

create table 表名(

字段名类型(长度),

...

-- 指定主键字段名

primary key (字段名)

);

-- 方式3 创建一个带主键的表

create table 表名(

字段名类型,

字段名类型(长度),

....

);

-- 创建的时候不指定主键,然后通过 DDL语句进行设置

alter table 表名 add primary key (字段名);

查看表结构

desc 表名;

测试主键的唯一性非空性

#正常插入一条数据

#插入一条数据,主键为空

# 插入一条数据,主键重复正常插入的主键

哪些字段可以作为主键 ?

通常针对业务去设计主键,每张表都设计一个主键id
主键是给数据库和程序使用的,跟最终的客户无关,所以主键没有意义没有关系,只要能够保证不重复就好,比如身份证就可以作为主键.

删除主键约束

删除表中的主键约束 (了解)

-- 使用DDL语句 删除表中的主键
alter table 表名 drop primary key ;
show 表名;

主键的自增

注: 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值.

关键字:
auto_increment 表示自动增长(字段类型必须是整数类型)

1.创建主键自增的表

-- 创建主键自增的表 
 create table 表名(
	-- 关键字 AUTO_INCREMENT,主键类型必须是整数类型 
	字段名1 int primary auto_increment,
	字段名2 类型(长度),
	字段名3 类型(长度)
);

2.添加数据观察主键的自增

insert into 表名 (字段名2,字段名3)values(值2,值3);

insert into 表名 (字段名2,字段名3)values(值2,值3);

insert into 表名 (字段名2,字段名3)values(值2,值3);

insert into 表名 (字段名2,字段名3)values(值2,值3);

修改主键自增的起始值

默认地 auto_increment 的开始值是 1，如果希望修改起始值,请使用下面的方式

-- 创建主键自增的表,自定义自增其实值 
create table 表名(
    字段1 int primary key auto_increment,
    字段2 类型 (长度),
    字段3 类型(长度)
)AUTO_INCREMENT=100;


-- 插入数据,观察主键的起始值
insert into 表名 (字段名2,字段名3)values(值2,值3);
insert into 表名 (字段名2,字段名3)values(值2,值3);

delete 和 truncate对自增长的影响

删除表中所有数据有两种方式

清空表数据的方式

特点

delete

只是删除表中所有数据,对自增没有影响

truncate

truncate 是将整个表删除掉,然后创建一个新的表自增的主键,

重新从 1开始

测试1: delete 删除表中所有数据

-- 目前最后的主键值是 101 
select * from 表名;
-- delete 删除表中数据,对自增没有影响 
delete from 表名;
-- 插入数据 查看主键
insert into 表名 (字段名2,字段名3)values(值2,值3);
insert into 表名 (字段名2,字段名3)values(值2,值3);

测试2: truncate删除表中数据

-- 使用 truncate 删除表中所有数据, 
truncate table 表名;
-- 插入数据 查看主键
insert into 表名 (字段名2,字段名3)values(值2,值3);
insert into 表名 (字段名2,字段名3)values(值2,值3);

非空约束

非空约束的特点: 某一列不允许为空

语法格式

字段名类型 not null

需求1: 为 ename 字段添加非空约束

# 非空约束
create table 表名(
	字段名 int primary key auto_increment,
	-- 添加非空约束, ename字段不能为空 
	ename varchar(20) not null,
	字段名 类型(长度)
);

唯一约束

唯一约束的特点: 表中的某一列的值不能重复( 对null不做唯一的判断 )

语法格式

字段名类型 unique

1.添加唯一约束

#创建emp3表 为ename 字段添加唯一约束 
create table emp3(
    eid int primary KEY AUTO_INCREMENT,
    ename varchar(20) unique,
    sex char(1)
);

2.测试唯一约束

-- 测试唯一约束 添加一条数据
insert into emp3 (ename,sex) values('张百万','男');


-- 添加一条 ename重复的 数据
-- Duplicate entry '张百万' for key 'ename' ename不能重复 
insert into emp3 (ename,sex) values('张百万','女');

主键约束与唯一约束的区别:

主键约束唯一且不能够为空
唯一约束,唯一但是可以为空
一个表中只能有一个主键 , 但是可以有多个唯一约束

默认约束

默认值约束用来指定某列的默认值

语法格式

字段名字段类型 default 默认值

1.创建emp4表

-- 创建带有默认值的表 
create table emp4(
	eid int primary KEY AUTO_INCREMENT, 
	-- 为ename 字段添加默认值
	ename varchar(20) default '奥利给', 
	sex char(1)
);

2.测试添加数据使用默认值

-- 添加数据 使用默认值
insert into emp4(ename,sex) values(default,'男'); 
insert into emp4(sex) values('女');


-- 不使用默认值
insert into emp4(ename,sex)values('艳秋','女');

外键约束

foreign key 表示外键约束，外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。

-- 创建表时添加外键约束 
create table 表名 ( 字段 数据类型, … [constraint] [外
键名称] foreign key(外键列名) references 主表(主表列名) );

-- 建完表后添加外键约束 
alter table 表名 add constraint 外键名称 foreign KEY (外键字段名称)
 references 主表名称(主表列名称);

删除外键约束

alter table 表名 drop foreign key 外键名称;

-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;


-- 部门表
CREATE TABLE dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,


	-- 添加外键 dep_id,关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)	
);

根据上述语法创建员工表和部门表，并添加上外键约束

添加数据

-- 添加 2 个部门
insert into dept(dep_name,addr) values
('研发部','郑州'),('销售部', '北京');


-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (NAME, age, dep_id) VALUES 
('张三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);

此时删除 研发部 这条数据，会发现无法删除。

删除外键

alter table emp drop foreign key fk_emp_dept;

重新添加外键

alter table emp add constraint fk_emp_dept foreign key(dep_id) references dept(id);

2.数据库设计

数据库设计简介

数据库设计概念
- 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
- 建立数据库中的==表结构==以及表与表之间的关联关系的过程。
- 有哪些表？表里有哪些字段？表和表之间有什么关系？
数据库设计的步骤
- 需求分析（数据是什么? 数据具有哪些属性? 数据与属性的特点是什么）

逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）

- ER图（entity relationship diagram) 实体关系图：提供了实体类型、属性和关系的方法，用来描述现实世界的概念模型
- 如下图就是ER(Entity/Relation)图：
- 实体用矩形表示，属性用椭圆表示，主键学号需要加下划线

绘图软件:Office visio,word,亿图图示，Enterprise Architect(EA),在线网站processon
表关系
- 一对一
  - 如：用户（用户名字密码）和用户详情 ()
  - 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
- 一对多
  - 如：部门和员工
  - 一个部门对应多个员工，一个员工对应一个部门。
- 多对多
  - 如：商品和订单
  - 一个商品对应多个订单，一个订单包含多个商品。

表关系(一对多)

一对多
- 如：部门和员工
- 一个部门对应多个员工，一个员工对应一个部门。

实现方式

==在多的一方建立外键，指向一的一方的主键==

案例

我们还是以 员工表 和 部门表 举例:

经过分析发现，员工表属于多的一方，而部门表属于一的一方，此时我们会在员工表中添加一列（dep_id），指向于部门表的主键（id）：

建表语句如下：

查看表结构模型图：

-- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;

-- 部门表
CREATE TABLE tb_dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE tb_emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

	-- 添加外键 dep_id,关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)	
);

表关系(多对多)

多对多
- 如：商品和订单
- 一个商品对应多个订单，一个订单包含多个商品

实现方式

==建立第三张中间表，中间表至少包含两个外键，分别关联两方主键==

案例

我们以 订单表 和 商品表 举例：

经过分析发现，订单表和商品表都属于多的一方，此时需要创建一个中间表，在中间表中添加订单表的外键和商品表的外键指向两张表的主键：

建表语句如下：

-- 删除表 
DROP TABLE IF EXISTS tb_order_goods; 
DROP TABLE IF EXISTS tb_order; 
DROP TABLE IF EXISTS tb_goods;  
-- 订单表 
CREATE TABLE tb_order(     
id int primary key auto_increment,     
payment double(10,2),     
payment_type TINYINT,     
status TINYINT );  
-- 商品表
CREATE TABLE tb_goods(     
id int primary key auto_increment,     
title varchar(100),    
 price double(10,2) ); 
-- 订单商品中间表 
CREATE TABLE tb_order_goods(     
id int primary key auto_increment,     
order_id int,     
goods_id int,     
count int );  
-- 建完表后，添加外键 
alter table tb_order_goods add 
CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id); 
alter table tb_order_goods add 
CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

表关系(一对一)

一对一
- 如：用户和用户详情
- 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

实现方式

==在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)==

案例

我们以 用户表 举例：

而在真正使用过程中发现 id、photo、nickname、age、gender 字段比较常用，此时就可以将这张表查分成两张表。

建表语句如下：

create table tb_user_desc (
     id int primary key auto_increment,
     city varchar(20),
     edu varchar(10),
     income int,
     status char(2),
     des varchar(100)
 );
 
 create table tb_user (
     id int primary key auto_increment,
     photo varchar(100),
     nickname varchar(50),
     age int,
     gender char(1),
     desc_id int unique,
     -- 添加外键
     CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)    
 );

3.多表查询

一般是要把每个列的名字写出来而不是用,这里方便一下用的来代替

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

比如我们要查询家电分类下都有哪些商品,那么我们就需要查询分类与商品这两张表

1.创建 NN 数据库

-- 创建 NN 数据库,指定编码
create database NN char set utf8mb4;

# 使用数据库
use NN;

2.创建分类表与商品表

#分类表 (一方 主表) 
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
);

#商品表 (多方 从表) 
CREATE TABLE products(
	pid VARCHAR(32) PRIMARY KEY ,
	pname VARCHAR(50),
	price INT,
	flag VARCHAR(2), #是否上架标记为:1表示上架、0表示下架 
	category_id VARCHAR(32),
	-- 添加外键约束
  	FOREIGN KEY (category_id) REFERENCES category (cid)
);

3.插入数据

#分类数据
INSERT INTO category(cid,cname) VALUES('c001','家电'); 
INSERT INTO category(cid,cname) VALUES('c002','鞋服'); 
INSERT INTO category(cid,cname) VALUES('c003','化妆品'); 
INSERT INTO category(cid,cname) VALUES('c004','汽车');

#商品数据
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','小米电视 机',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','格力空调',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','美的冰箱',4500,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','篮球鞋',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','运动裤',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','T恤',300,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','冲锋衣',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','神仙水',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','大宝',200,'1','c003');

笛卡尔积

交叉连接查询,因为会产生笛卡尔积,所以基本不会使用

语法格式

select 字段名 from 表1, 表2;

使用交叉连接查询商品表与分类表

select*from category , products;

内连接查询

通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示

隐式内连接

from子句后面直接写多个表名使用where指定连接条件的这种连接方式是隐式内连接.

使用where条件过滤无用的数据

语法格式

SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;

查询所有商品信息和对应的分类信息

# 隐式内连接
SELECT * FROM products,category WHERE category_id = cid;

查询商品表的商品名称和价格,以及商品的分类信息

可以通过给表起别名的方式, 方便我们的查询(有提示)

SELECT
    p.`pname`,
    p.`price`,
    c.`cname`
FROM products p , category c WHERE p.`category_id` = c.`cid`;

查询格力空调是属于哪一分类下的商品

#查询 格力空调是属于哪一分类下的商品
SELECT p.`pname`,c.`cname` 
FROM products p , category c 
WHERE p.`category_id` = c.`cid` AND p.`pid` = 'p002';

显式内连接

使用 inner join …on 这种方式, 就是显式内连接

语法格式

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件 
-- inner 可以省略

查询所有商品信息和对应的分类信息

# 显式内连接查询
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid;

查询鞋服分类下,价格大于500的商品名称和价格

# 查询鞋服分类下,价格大于500的商品名称和价格
-- 我们需要确定的几件事
-- 1.查询几张表 products & category
-- 2.表的连接条件 从表.外键 = 主表的主键
-- 3.查询的条件 cname = '鞋服' and price > 500 -- 4.要查询的字段 pname price
SELECT
	p.pname,
	p.price
FROM products p 
INNER JOIN category c ON p.category_id = c.cid 
WHERE p.price > 500 AND cname = '鞋服';

外连接查询

左外连接

左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略
左外连接的特点
- 以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
- 如果匹配不到, 左表中的数据正常展示, 右边的展示为null.

语法格式

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

-- 左外连接查询
SELECT * 
FROM category c 
LEFT JOIN products p ON c.`cid`= p.`category_id`;

左外连接, 查询每个分类下的商品个数

# 查询每个分类下的商品个数 
/*
	1.连接条件: 主表.主键 = 从表.外键 
	2.查询条件: 每个分类 需要分组 
	3.要查询的字段: 分类名称, 分类下商品个数
*/
SELECT
	c.`cname` AS '分类名称', 
	COUNT(p.`pid`) AS '商品个数'
FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`
GROUP BY c.`cname`;

右外连接

右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略
右外连接的特点
- 以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据
- 如果匹配不到，右表中的数据正常展示, 左边展示为null

语法格式

select 字段名 from 左表 right [outer]join 右表 on 条件

-- 右外连接查询
SELECT * 
FROM products p
RIGHT JOIN category c ON p.`category_id` = c.`cid`;

子查询

子查询概念
- 一条select 查询语句的结果, 作为另一条 select 语句的一部分
子查询的特点
- 子查询必须放在小括号中
- 子查询一般作为父查询的查询条件使用
子查询常见分类
- where型子查询: 将子查询的结果, 作为父查询的比较条件
- from型子查询 : 将子查询的结果, 作为一张表,提供给父层查询使用
- exists型子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果

子查询的结果作为查询条件

语法格式

select 查询字段 from 表 where 字段 = (子查询);

1.通过子查询的方式, 查询价格最高的商品信息

# 通过子查询的方式, 查询价格最高的商品信息 
-- 1.先查询出最高价格
SELECT MAX(price) FROM products;

-- 2.将最高价格作为条件,获取商品信息
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);

2.查询化妆品分类下的商品名称商品价格

#查询化妆品分类下的 商品名称 商品价格 
-- 先查出化妆品分类的 id
SELECT cid FROM category WHERE cname = '化妆品';

-- 根据分类id ,去商品表中查询对应的商品信息 
SELECT
	p.`pname`,
    p.`price`
FROM products p
WHERE p.`category_id` = (SELECT cid FROM category WHERE cname = '化妆品');

3.查询小于平均价格的商品信息

-- 1.查询平均价格
SELECT AVG(price) FROM products; -- 1866

-- 2.查询小于平均价格的商品
SELECT * FROM products
WHERE price < (SELECT AVG(price) FROM products);

子查询的结果作为一张表

语法格式

select 查询字段 from(子查询) 表别名 where 条件;

查询商品中,价格大于500的商品信息,包括商品名称商品价格商品所属分类名称

-- 1. 先查询分类表的数据 
SELECT * FROM category;

-- 2.将上面的查询语句 作为一张表使用 
SELECT
    p.`pname`,
    p.`price`,
    c.cname
FROM products p
-- 子查询作为一张表使用时 要起别名 才能访问表中字段
INNER JOIN (SELECT * FROM category) c
ON p.`category_id` = c.cid WHERE p.`price` > 500;

注意: 当子查询作为一张表的时候，需要起别名，否则无法访问表中的字段。

子查询结果是单列多行

子查询的结果类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果

语法格式

SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);

查询价格小于两千的商品,来自于哪些分类(名称)

# 查询价格小于两千的商品,来自于哪些分类(名称) 
-- 先查询价格小于2000 的商品的,分类ID
SELECT DISTINCT 
	category_id 
FROM products 
WHERE price < 2000;

-- 在根据分类的id信息,查询分类名称
-- 报错: Subquery returns more than 1 row
-- 子查询的结果 大于一行
SELECT * FROM category
WHERE cid = (SELECT DISTINCT category_id 
FROM products WHERE price < 2000);

使用in函数, in( c002, c003 )

-- 子查询获取的是单列多行数据
SELECT * FROM category
WHERE 
	cid IN 
(SELECT DISTINCT category_id FROM products WHERE price < 2000);

查询家电类与鞋服类下面的全部商品信息

# 查询家电类 与 鞋服类下面的全部商品信息
-- 先查询出家电与鞋服类的 分类ID
SELECT cid FROM category WHERE cname IN ('家电','鞋服');

-- 根据cid 查询分类下的商品信息
SELECT * FROM products
WHERE 
	category_id IN 
(SELECT cid FROM category WHERE cname IN ('家电','鞋服'));

子查询总结

子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用.
子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名).

练习

环境准备：

DROP TABLE IF EXISTS emp;
 DROP TABLE IF EXISTS dept;
 DROP TABLE IF EXISTS job;
 DROP TABLE IF EXISTS salarygrade;
 
 -- 部门表
 CREATE TABLE dept (
   did INT PRIMARY KEY, -- 部门id
   dname VARCHAR(50), -- 部门名称
   loc VARCHAR(50) -- 部门所在地
 );
 
 -- 职务表，职务名称，职务描述
 CREATE TABLE job (
   id INT PRIMARY KEY,
   jname VARCHAR(20),
   description VARCHAR(50)
 );
 
 -- 员工表
 CREATE TABLE emp (
   id INT PRIMARY KEY, -- 员工id
   ename VARCHAR(50), -- 员工姓名
   job_id INT, -- 职务id
   mgr INT , -- 上级领导
   joindate DATE, -- 入职日期
   salary DECIMAL(7,2), -- 工资
   bonus DECIMAL(7,2), -- 奖金
   dept_id INT, -- 所在部门编号
   CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
   CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (did)
 );
 -- 工资等级表
 CREATE TABLE salarygrade (
   grade INT PRIMARY KEY,   -- 级别
   losalary INT,  -- 最低工资
   hisalary INT -- 最高工资
 );
                 
 -- 添加4个部门
 INSERT INTO dept(did,dname,loc) VALUES 
 (10,'教研部','北京'),
 (20,'学工部','上海'),
 (30,'销售部','广州'),
 (40,'财务部','深圳');
 
 -- 添加4个职务
 INSERT INTO job (id, jname, description) VALUES
 (1, '董事长', '管理整个公司，接单'),
 (2, '经理', '管理部门员工'),
 (3, '销售员', '向客人推销产品'),
 (4, '文员', '使用办公软件');
 
 
 -- 添加员工
 INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
 (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
 (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
 (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
 (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
 (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
 (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
 (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
 (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
 (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
 (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
 (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
 (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
 (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
 (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
 
 
 -- 添加5个工资等级
 INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
 (1,7000,12000),
 (2,12010,14000),
 (3,14010,20000),
 (4,20010,30000),
 (5,30010,99990);

需求

查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

/*
	分析：
		1. 员工编号，员工姓名，工资 信息在emp 员工表中
		2. 职务名称，职务描述 信息在 job 职务表中
		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
*/
-- 方式一 ：隐式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description
FROM
	emp,
	job
WHERE
	emp.job_id = job.id;

-- 方式二 ：显式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description
FROM
	emp
INNER JOIN job ON emp.job_id = job.id;

查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

/*
	分析：
		1. 员工编号，员工姓名，工资 信息在emp 员工表中
		2. 职务名称，职务描述 信息在 job 职务表中
		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

		4. 部门名称，部门位置 来自于 部门表 dept
		5. dept 和 emp 一对多关系 dept.id = emp.dept_id
*/

-- 方式一 ：隐式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc
FROM
	emp,
	job,
	dept
WHERE
	emp.job_id = job.id
	and dept.did = emp.dept_id
;

-- 方式二 ：显式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc
FROM
	emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.did = emp.dept_id

查询员工姓名，工资，工资等级

 /*
	分析：
		1. 员工姓名，工资 信息在emp 员工表中
		2. 工资等级 信息在 salarygrade 工资等级表中
		3. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
SELECT
	emp.ename,
	emp.salary,
	t2.*
FROM
	emp,
	salarygrade t2
WHERE
	emp.salary >= t2.losalary
AND emp.salary <= t2.hisalary

查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

/*
	分析：
		1. 员工编号，员工姓名，工资 信息在emp 员工表中
		2. 职务名称，职务描述 信息在 job 职务表中
		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

		4. 部门名称，部门位置 来自于 部门表 dept
		5. dept 和 emp 一对多关系 dept.id = emp.dept_id
		6. 工资等级 信息在 salarygrade 工资等级表中
		7. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc,
	t2.grade
FROM
	emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id
INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;

查询出部门编号、部门名称、部门位置、部门人数

/*
	分析：
		1. 部门编号、部门名称、部门位置 来自于部门 dept 表
		2. 部门人数: 在emp表中 按照dept_id 进行分组，然后count(*)统计数量
		3. 使用子查询，让部门表和分组后的表进行内连接
*/
-- 根据部门id分组查询每一个部门id和员工数
select dept_id, count(*) from emp group by dept_id;

SELECT
	dept.id,
	dept.dname,
	dept.loc,
	t1.count
FROM
	dept,
	(
		SELECT
			dept_id,
			count(*) count
		FROM
			emp
		GROUP BY
			dept_id
	) t1
WHERE
	dept.id = t1.dept_id

4.事务

概述

数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。

事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令==要么同时成功，要么同时失败==。

事务是一个不可分割的工作逻辑单元。

这些概念不好理解，接下来举例说明

张三和李四账户中各有1000块钱，现李四需要转换500块钱给张三，具体的转账操作为

第一步：查询李四账户余额
第二步：从李四账户金额 -500
第三步：给张三账户金额 +500

现在假设在转账过程中第二步完成后出现了异常第三步没有执行，就会造成李四账户金额少了500，而张三金额并没有多500；这样的系统是有问题的。如果解决呢？使用事务可以解决上述问题

语法

开启事务

start transaction; 或者 begin;

提交事务

commit;

回滚事务

rollback;

模拟转转操作

-- 创建账户表
CREATE TABLE account(
	-- 主键
	id INT PRIMARY KEY AUTO_INCREMENT, 
	-- 姓名
	NAME VARCHAR(10),
	-- 余额
	money DOUBLE
);
-- 添加两个用户
INSERT INTO account (NAME, money) VALUES ('tom', 1000), ('jack', 1000);

模拟tom 给 jack 转 500 元钱，一个转账的业务操作最少要执行下面的 2 条语句:

-- tom账户 -500元
UPDATE account SET money = money - 500 WHERE NAME = 'tom';

-- jack账户 + 500元
UPDATE account SET money = money + 500 WHERE NAME = 'jack';

假设当tom 账号上 -500 元,服务器崩溃了。jack 的账号并没有+500 元，数据就出现问题了。我们要保证整个事务执行的完整性,要么都成功, 要么都失败. 这个时候我们就要学习如何操作事务.

Mysql事务操作

MYSQL 中可以有两种方式进行事务的操作:
- 手动提交事务
- 自动提交事务

手动提交事务

功能	语句
开启事务	start transaction; 或者 BEGIN;
提交事务	commit;
回滚事务	rollback;

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


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


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

手动提交事务流程

执行成功的情况: 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
执行失败的情况: 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚

案例演示

模拟张三给李四转 500 元钱

1) 开启事务

start transaction;

2) tom账户 -500

update account set money = money -500 where name ='tom'

3) jack账户 +500

update account set money = money +500 where name ='jack';

注：由于未提交事务，此时数据并未变化

4) 在控制台执行 commit 提交事务

commit;

查看autocommit状态

SHOW VARIABLES LIKE 'autocommit';
-- on :自动提交
-- off : 手动提交
SELECT @@autocommit;

把 autocommit 改成 off;

SET @@autocommit=off;
-- 1表示自动 0 表示手动
set @@autocommit = 0;

-- 修改数据
update account set money = money - 500 where name = 'jack';
-- 手动提交 
commit;

事务的四大特征(ACID)

原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态
隔离性（Isolation） :多个事务之间，操作的可见性
持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

事务的隔离级别（了解）

MySQL是一个客户端／服务器架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称之为一个会话（Session）。每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。

事务并发执行遇到的问题

脏读(Dirty Read)

如果一个事务读到了另一个未提交事务修改过的数据，那就意味着发生了脏读

如上图，Session A和Session B各开启了一个事务，Session B中的事务先将number列为1的记录的name列更新为'关羽'，然后Session A中的事务再去查询这条number为1的记录，如果读到列name的值为'关羽'，而Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据，这种现象就称之为脏读。

不可重复读（ Non-Repeatable Read）

如果一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那就意味着发生了不可重复读

幻读（Phantom）

如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，那就意味着发生了幻读

如上图，Session A中的事务先根据条件number > 0这个条件查询表hero，得到了name列值为'刘备'的记录；之后Session B中提交了一个隐式事务，该事务向表hero中插入了一条新记录；之后Session A中的事务再根据相同的条件number > 0查询表hero，得到的结果集中包含Session B中的事务新插入的那条记录，这种现象也被称之为幻读。

有的同学会有疑问，那如果Session B中是删除了一些符合number > 0的记录而不是插入新记录，那Session A中之后再根据number > 0的条件读取的记录变少了，这种现象算不算幻读呢？明确说一下，这种现象不属于幻读，幻读强调的是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录。

tips:

那对于先前已经读到的记录，之后又读取不到这种情况，算啥呢？其实这相当于对每一条记录都发生了不可重复读的现象。幻读只是重点强调了读取到了之前读取没有获取到的记录

SQL标准中的四种隔离级别

根据以上严重程度，做一个排序

脏读 > 不可重复读 > 幻读

根据以上问题，SQL设置了隔离级别

READ UNCOMMITTED：未提交读。
READ COMMITTED：已提交读。 -- mysql 
REPEATABLE READ：可重复读。 -- oracle
SERIALIZABLE：可串行化。

存在的问题

隔离级别	脏读	不可重复读	幻读
读未提交(READ UNCOMMITTED)	√	√	√
读已提交(READ COMMITTED)	×	√	√
可重复读(REPEATABLE READ)	×	×	√
可串行化(SERIALIZABLE)	×	×	×

也就是说

READ UNCOMMITTED 隔离级别下，可能发生脏读、不可重复读和幻读问题。
READ COMMITTED 隔离级别下，可能发生不可重复读和幻读问题，但是不可以发生脏读问题。
REPEATABLE READ 隔离级别下，可能发生幻读问题，但是不可以发生脏读和不可重复读的问题。
SERIALIZABLE隔离级别下，各种问题都不可以发生。

5.数据库设计三大范式

第一范式（1NF）: 确保每一列的原子性（做到每列不可拆分）
第二范式（2NF）:在第一范式的基础上，每一行必须可以唯一的被区分，因此需要为表加上主键列
第三范式（3NF）:在第二范式的基础上，一个表中不包含已在其他表中包含的非主关键字信息（外键）

反范式:

有时候为了兼顾效率，可以不遵循范式，设计冗余字段，如订单（总价）和订单项（单价）

6.数据库的备份和还原

备份

mysqldump -u用户名 -p密码 数据库名 > 保存的路径
mysqldump -uroot -p123456 db1 > d://a.sql

还原

drop database db1;
create database db1;
use db1;
source d://a.sql

7.MySQL存储引擎：

什么是存储引擎：

sql ----> 解析器 ------>优化器 ----->存储引擎

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

查看当前mysql默认引擎:

查看mysql支持哪些引擎：

修改默认存储引擎

如果修改本次会话的默认存储引擎(重启后失效)，只对本会话有效，其他会话无效：

修改全局会话默认存储引擎(重启后失效)，对所有会话有效

InnoDB

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。

InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择：

优点:

更新密集的表。 InnoDB存储引擎特别适合处理多重并发的更新请求。
事务。 InnoDB存储引擎是支持事务的标准MySQL存储引擎。
自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
外键约束。 MySQL支持外键的存储引擎只有InnoDB。
支持自动增加列AUTO_INCREMENT属性。
从5.5开始innodb存储引擎成为默认的存储引擎。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

InnoDB是一个健壮的事务型存储引擎,这种存储引擎已经被很多互联网公司使用,为用户操作非常大的数据存储提供了一个强大的解决方案。

InnoDB还引入了行级锁定和外键约東,在以下场合下,使用 InnoDB是最理想的选择

优点

Innodb引擎提供了对数据库ACID事务的支持,并且实现了SQL标准的四种隔离级别
支持多版本并发控制的行级锁,由于锁粒度小,写操作和更新操作并发高、速度快
支持自增长列。
支持外键
适合于大容量数据库系统,
支持自动灾难恢复

缺点

它没有保存表的行数 SELECT COUNT(*) FROM TABLE 时需要扫描全表

应用场景

当需要使用数据库事务时,该引擎当然是首选。由于锁的粒度更小,写操作不会锁定全表,所以在并发较高时,使用 Innodb引擎会提升效率。更新密集的表, InnoDB.存储引擎特别适合处理多重并发的更新请求

MyISAM

MyISam引擎不支持事务、也不支持外键,优势是访问速度快,对事务完整性没有要求或者以 select, Insert为主的应用基本上可以用这个引擎来创建表·

优点

MyISAM表是独立于操作系统的,这说明可以轻松地将其从 windows服务器移植到Liux服务器
MyISAM存储引擎在查询大量数据时非常迅速,这是它最突出的优点
另行大批量插入操作时执行速度也比较快

缺点

间隙锁

行锁

表锁

MyISAM表没有提供对数据库事务的支持。
不支持行级锁和外键。
不适合用于经常 UPDATE(更新)的表

应用场景

以读为主的业务,例如:图片信息数据库,博客数据库,商品库等业务
对数据一致性要求不是非常高的业务(不支持事务)
硬件资源比较差的机器可以用 MyISAM(占用资源少)

MEMORY

MEMORY的特点是将表中的数据放在内存中,适用于存储临时数据的临时表和数据仓库中的纬度表

优点

memory类型的表访问非常的快,因为它的数据是放在内存中的

缺点

一旦服务关闭,表中的数据就会丢失掉
只支持表锁,并发性能差,不支持TEXT和BLOB列类型,存储 varchar时是按照char的方式

应用场景

目标数据较小,而且被非常频繁地访问。
如果数据是临时的,而且要求必须立即可用,那么就可以存放在内存表中。
存储在 Memory表中的数据如果突然丢失,不会对应用服务产生实质的负面影响

存储引擎的选择

不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：

功能	MYISAM	Memory	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事务	No	No	Yes	No
支持全文索引	Yes	No	No	No
支持数索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	No	No
支持数据缓存	No	N/A	Yes	No
支持外键	No	No	Yes	No

如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive

使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。

Mysql索引数据结构 B-树和B+树

索引类型:

哈希索引
- 只能做等值比较，不支持范围查找
- 无法利用索引完成排序
- 如果存在大量重复键值的情况下，哈希索引效率会比较低，可能存在哈希碰撞
B-树/B+树
- B 代表 balance 平衡的意思，是一种多路自平衡的搜索树
- InnoDB 引擎默认使用B+树，Memory默认使用 B树
- B树所有节点都储存数据，B+树只在叶子节点储存，为了降低树的高度，每一层可以保存多个节点（>2个）
- 为了支持范围查询，B+树在叶子节点之间加上了双向链表，减少IO次数

8.遇到的一些问题

group by严格不让用

找到这个my.ini在哪个配置

C:\ProgramData\MySQL\MySQL Server 5.7

记得重启mysql服务

win+R ，输入services.msc，打开服务

找到mysql服务重启

索引以及优化

1. 存储引擎

MySQL体系结构

1. 连接层

连接层:

主要是接收客户端的连接,然后完成一些连接的处理,以及认证授权的相关操作以及相关的一些安全方案,以及检查是否超过最大连接数等等

举例:

像我们在连接mysql服务器的时候,输入用户名和密码之后,在连接层就要校验我们输入的用户名和密码,这个过程就是授权认证的过程,以及我们授权认证完成之后,它还要去校验每一个客户端,它所具有的权限,它能操作哪些数据库,哪些表都是在这连接层处理

2. 服务层

服务层:

绝大部分的核心功能都是在服务层完成的,像sql接口,以及查询解析器,查询优化器,查询缓存都是在服务层完成的,而所有的跨存储引擎的实现也都是在服务层实现的,比如DML、DDL语句的封装,还有存储过程、视图、触发器也是在服务层

3. 引擎层

存储引擎层:

这一个个蓝色的圆筒就是存储引擎,在MySQL中它给我们提供了很多的存储引擎供我们选择,而且这些存储引擎不能满足我们的需求,我们还可以在其基础上进行扩展,所以被称为"可插拔存储引擎",也就是说如果用到哪个存储引擎就可以加进来,不用就可以拔掉,存储引擎它控制的就是我们MySQL当中,数据的存储和提出的方式,服务器会通过存储引擎来进行通信进行交互

重点关注Index索引是在存储引擎层实现的,也就意味着不同的存储引擎索引的结构是不一样的

InnoDB引擎是MySQL5.5版本之后默认的存储引擎

4. 存储层

存储层:

最后一个是存储层,我们提到存储引擎控制的是数据库的数据该如何来存和取,如何来进行组织,而具体的数据库的数据最终是存在磁盘当中的,最后的存储层主要就是来存储数据库相关数据,这个里面包含了一系列的日志:Redo、Undo、数据(Data)、索引(Index)、二进制值(Binary)、错误日志(Error)、慢查询日志(Query and Slow)

总结

存储引擎简介

1. 查询建表语句

如果在建表的时候没有指定存储引擎,那么在MySQL5.5之后默认是InnoDB存储引擎

AUTO_INCREMENT 表示id是递增的,当插入下一条数据是3

CHARSET表示当前表的字符集

2. 在创建表时,指定存储引擎

关键字 ENGINE = 索引类型

CREATE TABLE 表名(

字段1 字段1类型 [COMMENT 字段1注释] ,

......

字段n 字段n类型 [COMMENT 字段n注释]

)ENGINE = INNODB [COMMENT 表注释] ;

3. 查看当前数据库支持的存储引擎

show engines

Engine : 代表的有哪些存储引擎

Support: YES表示引擎受支持且处于活动状态，NO表示不支持，DEFAULT表示默认存储引擎。DISABLED表示支持引擎但已将其禁用。

Comment: 注释

Transactions: 是否支持事务

XA: 存储引擎是否支持XA事务。

Savepoints: 存储引擎是否支持回滚点(标记点)。

4.常见的存储引擎:

InnoDB: 默认的存储引擎,支持事务,行级锁以及外键.

MyISAM: mysql早期的默认存储引擎

MEMORY: 它的数据存储到内存当中,适合做临时表记缓存

5.练习

创建表 my_myisam,并指定myISAM存储引擎

create table my_myisam(
id int ,
name VARCHAR(10)
)engine = MyISAM

创建表 my_memory,并指定MEMORY存储引擎

create table my_memory(
id int,
name varchar(10)
)engine = memory

存储引擎特点

InnoDB

介绍

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySOL5.5之后，InnoDB是默认的 MySOL 存储引擎。

特点

DML(操作表的数据:增删改) 操作遵循ACID(事务的四大特性)模型，支持事务;

行级锁，提高并发访问性能;
支持外键 FOREIGN KEY约束，保证数据的完整性和正确性;

文件

xxx.ibd:xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构(frm、sdi)、数据和索引。

参数:innodb_file_per_table

mysql存放目录:C盘--->ProgramData--->MySQL--->MySQL Server 版本号--->Data--->选择你的数据库

ibd的文件是不能直接打开的,因为是二进制文件

逻辑存储结构

ibd文件就属于表空间,上图表示这包含关系

一个表空间包含着若干个段,一个段包含若干个区,一个区包含若干个页,一个页包含若干个行

这里的一行一行的数据包含在页当中的,而一行包含着最后操作事务的id(Trx id),它的一些指针(Roll pointer),一个一个字段(coll)

MyISAM

介绍

MyISAM是MySQL早期的默认存储引擎

特点

不支持事务,不支持外键

支持表锁,不支持行锁

访问速度快

文件

sdi: 存放的是表结构的信息

myd: 存放着表中存放的数据

myi: 存放着索引

Memory

介绍

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题就会丢失，只能将这些表作为临时表或缓存使用。

特点

memory类型的表访问非常的快,因为它的数据是放在内存中的

支持hash索引(默认)

文件

xxx.sdi: 存储表结构信息

特点

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
B+tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	支持(5.6版本之后)	支持	-
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	-	-

存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

InnoDB:是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

MyISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。---->(被Mongdb 代替)

MEMORY:将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。---->(被redis 代替)

总结

1.体系结构

连接层、服务层、引擎层、存储层

2.存储引擎简介

SHOW ENGINES -->(查看当前数据库支持的存储引擎)
CREATE TABLE XXXX(...) ENGINE=INNODB --->指定存储引擎

3.存储引擎特点

INNODB 与 MyISAM:事务、外键、行级锁

4.存储引擎应用

INNODB:存储业务系统中对于事务、数据完整性要求较高的核心数据。

MyISAM:存储业务系统的非核心事务

2.索引

2.1安装在虚拟机下面有详细介绍

liux-centos7_cns33设置ip-CSDN博客

2.2索引概述

索引 (index) 是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)原始数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

如上图表,要查年龄(age)等于45的,那么会一条一条往下找,找到了7号用户灭绝,但是找到后它不会停止,会继续往下匹配,因为不保证就只有一个45岁的年龄,这就是无索引的情况,称之为全表扫描(性能极低)

如果要对age建立索引,那么就比如维护一个二叉树数据结构.

当插入数据时,就需要维护二叉树的节点,节点是指向这一行的地址.

流程是先拿到45,跟36节点比较,比36大走右边,然后跟48节点比较,比48小走左边,最终找到了45,45直接指向灭绝这一行的数据的地址,有索引的情况只需要匹配三次就定位到了对应的数据,这个效率非常高效

备注:上述二叉树索引结构的只是一个示意图，并不是真实的索引结构

优缺点

优势	劣势
提高数据检索的效率，降低数据库的IO成本(存放在磁盘当中就涉及到IO)	索引列也是要占用空间的。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

2.3索引结构介绍

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种:

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持 B+树索引
Hash索引	底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询
R-tree(空间索引)	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)	是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

索引在不同引擎当中的支持情况

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
Hash 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

2.3.1数据结构-Btree

1.二叉树与红黑树

二叉树,一个节点下面包含两个子节点.

在这幅图中36是跟节点,左侧的节点都比36小,右侧所有的节点都比36大

当定位一个数据比如17,先从根节点对比,比36跟节点小走左侧,接着比22比小走左侧,就这样持续下去找到17

二叉树缺点:顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。

红黑树:大数据量情况下，层级较深，检索速度慢。

2.B-Tree(多路平衡查找树)

以一颗最大度数(max-degree)为5(5阶)的b-tree为例(每个节点最多存储4个key，5个指针):

知识小贴士: 树的度数指的是一个节点的子节点个数。

插入100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 数据为例。

数据化结构可视化的网站:

Data Structure Visualization Data Structure Visualization

找到B Trees

中间元素向上裂变

3.B+Tree

以一颗最大度数(max-degree)为4(4阶)的b+tree为例

插入100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268250 数据为例。

相对于B-Tree区别:
所有的数据都会出现在叶子节点

叶子节点形成一个单向链表

2.3.2索引结构

1.B+Tree

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

2.Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

在图中id是主键,我们需要为name字段创建hash索引的数据结构

在操作的时候首先会算出这张表每一行数据的hash值,比如金庸的哈希值是58dda

再拿到name字段的所有值,再根据name的所有值,通过它内部的hash函数去计算每一个name值应该落在哪个hash表的槽位上,

001-101- ....一直到最后都是hash表中的各个槽位

经过hash算法算出键值,它的hash值是多少就会落到对应的槽位上,

比如"金庸"进行hash运算,算出它的槽位置是005

此时在这005这个槽位当中就会去存储"金庸"这个key以及58dda

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突(也称为hash碰撞)，可以通过链表来解决。

如杨逍的hash进行hash运算,算出它的槽位置也是005,那么就通过链表来解决。再有想同的继续往后添加链表

hash索引特点

hash索引只能对用于对等比较(= , in),不支持范围查询(between, >, <, ...)
无法利用索引完成排序操作
查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引,这里用了两个通常是因为(如果出现hash碰撞,检索还需要去链表当中对比查找对应的元素)

存储引擎支持

在MySQL中,支持hash索引的是Memory引擎,而InnoDB中具有自适应hash功能,hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。(自适应hash功能指的是,MySQL会根据我们的查询条件,在指定的条件下,会自动的将B+Tree索引构建为hash索引,这个过程是自动化进行的)

2.3.3 思考:

为什么InnoDB存储引擎选择使用B+tree索引结构?

而没有采用二叉树或者红黑树或者B-tree再或者Hash索引结构

相对于二叉树，层级更少，搜索效率高
对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。(而如果采用的是B+tree索引结构就会发现,我不管查找哪个数据,都要去叶子节点当中才能找到对应的数据,此时搜索效率稳定。而且在B+tree的结构当中,叶子节点形成了一个双向链表,这是便于范围搜索和排序)
相对Hash索引，B+tree支持范围匹配及排序操作

2.4.索引分类

2.4.1分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建,只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种:

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

我们在创建InnoDB引擎的表结构的时候,如果没有指定聚集索引和二级索引,没有指定索引,那么最终会发现我们的数据也存进去了,这个时候谁到底是聚集索引,哪个是二级索引

2.4.2聚集索引选取规则:

如果存在主键，主键索引就是聚集索引
如果不在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

2.4.3示例

像上图有小钥匙id就是这张表的主键,如果一张表有主键,那么主键构建的这个主键索引就是聚集索引,构建的就是上面的B+Tree,聚集索引的结构就是它的叶子结点挂着数据就是这一行的数据

比如5叶子节点下面挂的row就是id5这一行的数据

8下面挂的row指的是id8这一行的数据

如果针对name字段建立索引,就是二级索引,因为聚集索引只能有一个,name字段以及其他索引都称为二级索引,结构还是B+Tree,但是二级索引的叶子节点存放的不是这一行的数据,而是对应的id,如果存放这一行的数据就冗余了

比如Arm叶子节点挂的就是id10

Dawn叶子节点挂的就是id15

select * from user where name = 'Arm';

上面sql语句是根据name字段去查

它不会走上面的聚合索引,因为聚合索引字段是id,所以会走下面name字段的二级索引

首先定位到Lee,Arm和Lee一对比,A在L之前会走左侧的指针

此时定位到Geek,Geek再与Arm对比,Arm在Geek之前再走左侧

定位到Arm,此时拿到了Arm和id值10

但是sql语句要得是 * ,获取的是这一行数据的所有字段

此时拿到10之后,就会到上面的聚集索引当中再去找这一行的数据

10和15一比,比15小走左侧指针,和10一比大于等于10走中间的指针

最终定位到10,拿到了这一行的数据

这个过程称之为回表查询,回表查询指的是先走二级索引找到对应的主键值,再根据主键值到聚集索引当中拿到这一行的行数据

2.4.4 思考

1.以下SQL语句，那个执行效率高?为什么?

select * from user where id=10;
select * from user where name ='Arm';
备注: id为主键，name字段创建的有索引;

第一条效率更高

因为第一个是id,只需要走聚集索引

而第二个还需要去name字段的二级索引找到对应的id值,再拿着id值去聚集索引,需要扫描两次索引

2.InnoDB主键索引的B+tree高度为多高呢?

一行数据大小为1k，一页中可以存储16行这样的数据。

InnoDB的指针占用6个字节的空间，取决于主键的类型,主键为int占用4个字节,主键即使为bigint，占用字节数为8个字节

假设高度为2:
n*8+(n+1)*6=16*1024,算出n约为1170

n指的是当前这个节点存储key的数量,这个8指的是8字节,就以bigint来计算

n+1指的就是指针的数量,因为指针比key多一个

6指的是指针的字节(指针占用的数量是6个字节)

16指的是一页的大小是16k,然后乘以1024个字节

最终算出来的每一个节点key的个数大概为1170,那么指针就有1171个指针

那么再用指针 1171*16=18736,因为一行有16个数据所以计算出数据最终是18736

高度为3:
1171*1171*16=21939856

2.5 索引语法

2.5.1 创建索引

create [unique| fulltext] index 起的索引名 on 关联的表名 (表中字段名,表中字段名....);

create index idx_user_name_sex on table_user (name,sex)

在INDEX之前可以加UNIQUE和FULLTEXT

UNIQUE代表的是创建一个唯一索引,那么就要求字段是不能重复的

FULLTEXT代表的是创建一个全文索引,这是一个可选项

如果不加这两个参数表示常规索引

一个索引是可以关联多个字段,如果索引只关联一个字段称为单列索引,多个字段称为联合索引

2.5.2 查看索引

show index from 要查看的表名 ;

show index from table_user

2.5.3 删除索引

drop index 要删除的索引名 on 关联的表名;

drop index idx_user_name_sex on table_user

2.5.4 案例

1.eame字段为姓名字段，该字段的值可能会重，为该字段创建索引。
2.phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
3.为sex、salary、dept_name创建联合索引。
4.为email建立合适的索引来提升查询效率。
#查看索引
show index from emp
#常规索引
create index idx_emp_name on emp (ename)
#唯一索引
create unique index idx_emp_phone on emp (phone)
#联合索引
create index idx_emp_sex_sly_dname on emp (sex,salary,dept_name)
#常规索引
create index idx_email on emp (email)
#删除索引
drop index idx_email on emp

表结构

查看索引,没有索引会自动创建一个主键索引(主键索引)

为ename字段创建索引(常规索引)

查看索引会看到结构还是BTREE,没有指定索引,InooDB默认就是B+Tree数据结构

为phone创建唯一索引(唯一索引)

为sex、salary、dept_name创建索引(联合索引)

为email建立合适的索引来提升查询效率(常规索引)

删除email的索引再次查看就会没有了

2.6 SQL性能分析

2.6.1 SQL执行频率

MYSQL客户端连接成功后，通过show [session | global ] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:

语法

show status like 'Com_______';

session 级（当前连接）的计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是"session"。 (注意是7个下划线,代表着7个字符)

参数	含义
Com_select	执行 select 操作的次数，一次查询只累加 1。
Com_insert	执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update	执行 UPDATE 操作的次数。
Com_delete	执行 DELETE 操作的次数。
Innodb_rows_read	select 查询返回的行数。
Innodb_rows_inserted	执行 INSERT 操作插入的行数。
Innodb_rows_updated	执行 UPDATE 操作更新的行数。
Innodb_rows_deleted	执行 DELETE 操作删除的行数。
Connections	试图连接 MySQL 服务器的次数。
Uptime	服务器工作时间。
Slow_queries	慢查询的次数。

当执行查询操作再查看时就会增加

2.6.2 慢查询日志

定位哪些SQL语句执行效率比较低,从而对SQL语句优化

慢查询日志记录了所有执行时间超过指定参数、(long query_time，单位: 秒，默认10秒)的所有SOL语句的日志。

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息

1.查看是否开启慢sql

第一种

#查看是否开启慢sql
show variables like 'slow_query_log' ;

第二种

show global variables like '%query%';

2.开启MySQL慢日志查询开关,Windos下默认是打开,liux下是关闭通用命令如下

第一种通用方法临时

# 开启MySQL慢日志查询开关window和liux通用命令如下 
set global slow_query_log=on;

第二种

1.进入配置文件
vi /etc/my.cnf

2.再按大写的G进入最后一行

G

3.i进入编辑命令

i

4.加入最后一行配置如下
#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为1秒，SOL语句执行时间超过1秒，就会视为慢查询，记录慢查询日志
long_query_time=1
5.Esc退出编辑

6. :wq 或 :x 退出保存

7. 重启mysql

systemctl restart mysqld

8.重启后找到日志,进入以下目录

cd /var/lib/mysql

9.找到你当前主机的主机名+slow.log

10.tail -f master-slow.log 去查看这个慢日志文件尾部实时输出的内容(只有慢sql就会显示)

11.当有一个千万表的数据执行以下sql就会很慢,执行了13.35秒

慢sql日志就会显示出来

2.6.3 profile详情

慢日志解决不了的慢SQL,使用show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySOL是否支持

1. profile操作:

先查看是否支持,支持yes,不支持no

select @@have_profiling;

默认profiling操作是关闭的，可以通过set语句在session/qlobal级别开启profiling:

#查看profling是否开启
select @@profiling;

打开profiling开关再次查看

#开启profiling 开关；

set profiling=1;

2. 执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时:

#查看每一条SQL的耗时基本情况

show profiles;

#查看指定query_id的SQL语句各个阶段的耗时情况

show profile for query query_id;

#查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

当执行了一些sql命令后使用show profiles; 可以查看sql语句耗时

通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

show profile for query 2;

TIP ：

Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，

而不仅仅是返回个客户端。由于在Sending data状态下，

MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间：

show profile cpu for query 2;

字段	含义
Status	sql 语句执行的状态
Duration	sql 执行过程中每一个步骤的耗时
CPU_user	当前用户占有的cpu
CPU_system	系统占有的cpu

2.6.4 explain执行计划(重要)

上面的都是通过时间的层面来评判一条sql语句的性能,执行时间短,那就说明sql语句的性能高,执行时间长,就说明sql语句执行的性能低,只是粗略的判定,不能真正真正评判一条sql语句的性能,还需要借助explain来看sql语句的执行计划

EXPLAIN 或者 DESC命令可以获取 MySQL如何执行 SELECT语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

语法:

#直接在select语句之前加上关键字 explain/desc

explain select 字段列表名 from 表名 where 条件;


desc select * from emp where eid = 1;

explain  select * from emp where eid = 1;

介绍

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。(id相同，执行顺序从上到下;id不同，值越大，越先执行):
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为( system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------> all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

1. 环境准备

2. explain 之 id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id 情况有2种：

1） id 相同表示加载表的顺序是从上到下。

explain select * from t_role r, t_user u, t_role_user ru where r.rid = ru.rid and u.id = ru.uid ;

2） id 不同id值越大，优先级越高，越先被执行。

查询角色为员工的角色信息 ;(子查询)

#先查询员工的id为3
select id from t_role r WHERE r.name = "员工"
#再查询中间表为员工3的用户id 1,2
select uid from t_role_user ru where ru.rid=3
#最后查询用户id为1和2的用户信息
select * from t_user u where id in(1,2)


#合成一条SQL
explain select * from t_user u where id in(
select uid from t_role_user where rid = (select id from t_role where name = "员工") 
)

注意是值越大优先级越高,越先被执行

3. explain 之 select_type

表示 SELECT 的类型，常见的取值，如下表所示：

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在SELECT 或 WHERE 列表中包含了子查询
DERIVED	在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
UNION	若第二个SELECT出现在UNION之后，则标记为UNION ；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为： DERIVED
UNION RESULT	从UNION表获取结果的SELECT

4. explain 之 type (重要)

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果
system	表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const	表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常亮。const于将 "主键" 或 "唯一" 索引的所有部分与常量值进行比较
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range	只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
index	index 与 ALL的区别为 index 类型只是遍历了索引树，通常比ALL 快， ALL 是遍历数据文件。
all	将遍历全表以找到匹配的行

结果值从最好到最坏以此是：

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

system > const > eq_ref > ref > range > index > ALL

一般来说，我们需要保证查询至少达到 range 级别，最好达到ref 。

5. explain 之 key

possible_keys

显示可能应用在这张表的索引，一个或多个。

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

key_len

表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

6. explain 之 rows

MYSQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

7.explain 之 filtered

表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

8. explain 之 extra

其他的额外的执行计划信息，在该列展示。

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为 “文件排序”, 效率低。 where 子句和order by 不在一个列上
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by；效率低
using index	表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错。

2.6.5 trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。

打开trace ，设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

执行SQL语句：

select * from t_user ;

最后，检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的：

select * from information_schema.optimizer_trace\G;

 select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
                            QUERY: select * from t_user
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t_user`.`uid` AS `uid`,`t_user`.`uname` AS `uname`,`t_user`.`upwd` AS `upwd`,`t_user`.`rid` AS `rid`,`t_user`.`idcard` AS `idcard`,`t_user`.`tel` AS `tel`,`t_user`.`rzdate` AS `rzdate`,`t_user`.`leavedate` AS `leavedate`,`t_user`.`deptid` AS `deptid`,`t_user`.`nowaddress` AS `nowaddress`,`t_user`.`address` AS `address`,`t_user`.`imgpath` AS `imgpath`,`t_user`.`status` AS `status` from `t_user`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "`t_user`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": "`t_user`",
                "table_scan": {
                  "rows": 5,
                  "cost": 1
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t_user`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 5,
                      "access_type": "scan",
                      "resulting_rows": 5,
                      "cost": 2,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 5,
                "cost_for_plan": 2,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t_user`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`t_user`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

2.7 索引的使用

2.7.1 验证索引效率

在未建立索引之前，执行如下SQL语句，查看SQL的耗时:

根据ID查询

select * from t_user where id=1000000 \G;

查看SQL语句的执行计划

explain select * from t_user where id=1000000 \G;

根据名字进行精确查询

select * from t_user where name='yyl1000000';

查看sql计划

explain select * from t_user where name='yyl1000000' \G;

id和name查询会发现name查询时间久是因为id默认有主键索引索引id比较快

如果想要提高sql语句的效率，需要给name添加索引

create index 索引名 on 表名(字段名);

show index from 表名;

最后再次根据名字进行精确查询查看消耗时间和查看索引计划

2.7.2 最左前缀法则

索引关联了多个字段(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的字段。如果跳过某一字段，索引将部分失效(后面的字段索引失效)。索引最左的字段位置前后顺序无关,但是必须要存在

1.环境准备

create table `tb_seller` (
    `sellerid` varchar (100),
    `name` varchar (100),
    `nickname` varchar (50),
    `password` varchar (60),
    `status` varchar (1),
    `address` varchar (100),
    `createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('dwtl','大威天龙技有限公司','大威天龙','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('mamimami','妈咪妈咪哄有限公司','哄','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

2. 创建复合索引(联合索引)

create index idx_name_sta_addr on tb_seller(name,status,address);

查看索引顺序 name字段最左,status中间 address最后

2.7.3 避免索引失效(最左前缀法则)

最左前缀法则

指的是查询从索引的最左前列开始并且不跳过索引中的列。

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

1、system > const > eq_ref > ref > range > index > All。一般而言，我们要保证查询至少达到ranag级别，最好能到达到ref。

2、possible_keys和key的值说明语句使用了索引index_nit。

3、ref值是const，即某个常量被用于查找索引列上的值。

全值匹配

对索引中所有列都指定具体值。

条件使用的是索引值 =

该情况下，索引生效，执行效率高。

explain select * from tb_seller where name='小米科技' and status='1' and address='西安市';

索引最左边的字段name存在,也没有跳过某一列,所以索引生效

 explain select * from tb_seller where name ="小米科技" and status = "1" ;

索引最左边的字段name存在,符合最左前缀生效

explain select * from tb_seller where name ="小米科技" ;

由此结论匹配最左前缀法则，索引的长度：

一个索引生效的时候值是403

两个索引都生效的时候值是 410

三个索引全部生效的时候值是813

索引部分失效

最左边的name字段存在,但是发现只走了一个索引,name走了索引,status没走索引,索引部分失效

原因:name和address中间还有一个字段是status,如果跳过了中间字段status,那么后面的索引就会失效

 explain select * from tb_seller where name ="小米科技" and  address="西安市";

索引失效

走了全表扫描,因为不满足最左法则,最左边的列必须存在,最左边的列是name,而不是status和address,最左边的列name不存在就全部失效

分析最左法则(重要理解)

把最左字段放在最后会发现,执行了所有的索引字段,为什么三个字段都会用上了呢,最左法则指的是索引最左边的字段必须存在,跟你放的位置是没有关系的,放在第一个位置第二个位置第三个位置也没有影响,但是这个索引最左的字段必须存在

explain select * from tb_seller where status ="1" and address ="西安市" and name="小米科技";

放在第二个位置再次查看也是走了全部的索引字段

 explain select * from tb_seller where status ="1" and name="小米科技"  and address ="西安市" ;

2.7.4 索引失效情况

除了上面的最左法则失效和部分索引失效外还有一些常见的会导致索引失效

(1)范围查询

联合索引中，出现范围查询(>，<)，范围查询右侧的列索引失效

范围查询右侧的列索引失效举例

执行了下面的执行计划,410表示之生效了两个索引字段,是最左的name和status字段索引,后面的就都不会执行

 explain select * from tb_seller where name ="小米科技" and status > "0" and address ="西安市";

去掉后面的字段发现还是410,证明了范围查询右侧的列索引失效

 explain select * from tb_seller where name ="小米科技" and status > "0" ;

解决办法

在使用复合索引时,尽量使用 >=

explain select * from tb_seller where name ="小米科技" and status >= "0" and address ="西安市";

(2)索引列运算

不要在索引列上进行运算操作，索引将失效。

此时我们先查看SQL语句,substring(name,3,2) substring表示截取(字段名,从哪儿截,截几位)

select * from tb_seller where substring(name,3,2) = '科技';

查看执行计划发现索引失效

 explain  select * from tb_seller where substring(name,3,2) = '科技';

(3)字符串不加单引号，造成索引失效。

字符串类型字段使用时，不加引号，索引将失效

注意: status字段设计的是varchar类型,不是int类型,虽然不加引号也能查出来,但是会让当前索引和后面的索引全部失效

长度403表示只有一个索引生效,是最左字段,而status字段和之后的字段全部失效

原因: 在查询时,没有对字符串加引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

explain select * from tb_seller where name ="小米科技" and status =1 and address ="西安市";

(4)模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

模糊查询头部失效案例

explain select * from tb_seller where name like '%小米%';

(5) or连接的条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。只有两侧都有索引才会生效。

先查看索引

show index from tb_seller;

用or执行另一个没有索引的字段

会发现索引没有生效,是因为只有两侧都有索引才会生效。

 explain select * from tb_seller where sellerid ="xiaomi" or address = "火星";

加索引

create index idx_adder on  tb_seller(address);

再次查询发现走了索引

(6) 数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

先查看表数据

此时测试发现>="2"的走了索引而>="1"的没有走索引

因为1大多数满足,mysql觉得走全表扫描比较快

而为2的大多数是不满足的,所以mysql判定走索引比全表扫描快

explain select * from tb_seller where status >="1";

(7) is null 和 is not null

原理同数据分布影响一样,is not null 大多数满足不走索引,is null 大多数不满足走索引

注意:并不是 is not null 就不走索引而 is null就走索引,如果name字段的数据大多数为空,那么反之is null 不走索引,is not null 走索引

explain select * from tb_seller where name is not null;

2.7.5 SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

比如我有两个索引,一个是联合索引,一个是单例索引,都有sex这个索引的字段,那么如果我查询sex的字段会用到哪个索引

查看执行计划发现mysql评判可能会用到复合索引和单例索引这两个索引,最后key用的是复合索引,那这种情况下我有多个索引,我想让mysql执行我指定的索引来查询,这种行为被称为sql提示

(1) use index(用哪个索引)

注意这只是给mysql建议用推荐的索引,至于mysql接不接受建议,mysql还要权衡一下指定的索引快不快

explain select * from emp use index(idx_emp_sex) where sex = "男";

(2)ignore index(不让用哪个索引)

表示忽略哪个索引

explain select * from emp ignore index(idx_emp_sex) where sex="男";

(3)force index(必须用哪个索引)

表示强制用哪个索引

 explain select * from emp force index(idx_emp_sex) where sex="男";

2.7.6 复合索引产生覆盖索引(重要)

尽量使用覆盖索引(查询使用了索引，并且只返回需要的列，在该索引中已经全部能够找到)，减少selec*。

先查看索引

覆盖索引：查询的列刚好与创建的索引列的列名及顺序全部匹配或者部分匹配

如果查询列，超出索引列，也会降低性能。

知识小贴士:

注意mysql版本不同,Extra出现的内容也不同

using where;using index:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据,性能高

using index condition:查找使用了索引，但是需要回表查询数据,性能低,回表可以去看2.4的

根据id走聚集索引,

拿到id2,

跟5比较小与5走左侧,

找到2, * 就是全部数据刚好id下面挂着就是这一行的数据

根据名字走辅助索引(二级索引)

拿到name的值Arm

跟Lee比走左侧找到Arm和id2

查询返回的字段就是id和name,直接返回就行,不需要回表

还是走二级索引,因为返回的字段也有gender,辅助索引没有,就拿着id去聚集索引找,找到id2拿到那一行的数据,我们称之为回表查询

一张表,有四个字段(id,username,password,status)由于数据量大，需要对以下SOL语句进行优化,该如何进行才是最优方案:

select id,username,password from tb user where username = '你好' ,

因为返回的字段也有password,不为加索引会回表,所以也要加上索引

create index idx_user_id_uname_pwd on user(id,username,password);

2.7.7 前缀索引

当字段类型为字符串(varchar,text,longtext,等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘io，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

(1)语法

create index 索引名 on 表名 (column(n));

(2)前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高,唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

 select count(distinct email) / count(*) from emp ;

create index idx_email_5 on emp(email(5));

select count(distinct substring(email,1,5)) / count(*) from emp ;

2.7.8 单列索引与联合索引

单列索引:即一个索引只包含单个列。
联合索引:即一个索引包含了多个列。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

先查看索引有phone和ename两个单例索引

只用到了phone的索引,另外一个没走索引,会有覆盖索引回表查询

explain select eid,phone,ename from emp where phone = 8888 and ename = "黄蓉";

解决:创建复合索引并指定索引

创建索引后发现可能用到三个索引,但实际还是只用到了单例索引phone,这是mysql自己选择的结果

 create unique index idx_phone_ename on emp(phone,ename);

指定索引

 explain select eid,phone,ename from emp  use index(idx_phone_ename)  where phone = 8888 and ename = "黄蓉";

2.7.9 索引设计原则

针对于数据量较大，且查询比较频繁的表建立索引。
针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能存储NULL值，请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

3.sql优化

3.1 insert优化

3.1.1 批量插入

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

示例，优化前方式为：

insert into tb_user_2 values(1,'Tom');
insert into tb_user_2 values(2,'Cat');
insert into tb_user_2 values(3,'Jerry');

优化后的方案为：

mybatis 进行批量添加的时候

insert into tb_user_2 values(4,"Tom1"),(5,"Tom2"),(6,"Tom3");

3.1.2 手动提交事务

start transaction;
insert into tb_user_2 values(1,"Tom1"),(2,"Tom2"),(3,"Tom3");
insert into tb_user_2 values(4,"Tom1"),(5,"Tom2"),(6,"Tom3");
insert into tb_user_2 values(7,"Tom1"),(8,"Tom2"),(9,"Tom3");
commit;

3.1.3 主键按顺序插入

优化前

start transaction;
insert into tb_user_2 values(1,"Tom1"),(9,"Tom2"),(8,"Tom3");
insert into tb_user_2 values(6,"Tom1"),(5,"Tom2"),(4,"Tom3");
insert into tb_user_2 values(7,"Tom1"),(3,"Tom2"),(2,"Tom3");
commit;

优化后

start transaction;
insert into tb_user_2 values(1,"Tom1"),(2,"Tom2"),(3,"Tom3");
insert into tb_user_2 values(4,"Tom1"),(5,"Tom2"),(6,"Tom3");
insert into tb_user_2 values(7,"Tom1"),(8,"Tom2"),(9,"Tom3");
commit;

3.1.4 大批量插入数据

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MVSOL数据库提供的load指令进行插入。操作如下:

#客户端连接服务器时,加上参数 --local-infile

mysql --local-infile -u root -p

#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关

select @@local_infile;

set global local_infile =1;

#执行load指令将准备好的数据，加载到表结构中

load data local infile '/root/你文件的名字' into table 要导入的表名 fields terminated by ',' lines terminated by '\n';

1.登录

2.创建一个库

3.查看数据库

4.查看是否开启

5.设置开启并再次查看

6.选择数据库创建表

7.上传脚本

8.执行 load指令

load data local infile '/root/你文件的名字' into table 要导入的表名 fields terminated by ',' lines terminated by '\n';

MYSQL

MySQL概述

MySQL的下载安装以及启动

1.下载地址

2.MySQL启动和停止

3.MySQL客户端连接

SQL通用语法及分类

SQL语句语法:

SQL分类:

DDL:数据库操作

DDL:表操作

DDL:表-数据类型

DDL:操作表的字段-修改&删除&添加

DML:操作表中数据

添加数据

删除数据

修改数据

DQL:查询表中数据

数据案例:

简单查询:

条件查询:

案例:

模糊查询:

排序查询

聚合函数

分组查询

分页查询

MySQL高级

1.约束

分类

主键约束

非空约束

唯一约束

默认约束

外键约束

2.数据库设计

数据库设计简介

表关系(一对多)

表关系(多对多)

表关系(一对一)

3.多表查询

一般是要把每个列的名字写出来而不是用*,这里方便一下用的*来代替

笛卡尔积

内连接查询

外连接查询

子查询

练习

4.事务

概述

语法

模拟转转操作

Mysql事务操作

事务的四大特征(ACID)

事务的隔离级别（了解）

SQL标准中的四种隔离级别

5.数据库设计三大范式

6.数据库的备份和还原

7.MySQL存储引擎：

什么是存储引擎：

InnoDB

MyISAM

MEMORY

Mysql索引数据结构 B-树和B+树

8.遇到的一些问题

group by严格不让用

索引以及优化

1. 存储引擎

MySQL体系结构

1. 连接层

2. 服务层

3. 引擎层

4. 存储层

总结

存储引擎简介

1. 查询建表语句

2. 在创建表时,指定存储引擎

3. 查看当前数据库支持的存储引擎

4.常见的存储引擎:

5.练习

存储引擎特点

一般是要把每个列的名字写出来而不是用,这里方便一下用的来代替