MySQL基础

1. 启动关闭mysql服务器
        方式1:可以通过服务窗口可视化操作
        方式2:通过cmd窗口执行命令
                以管理员身份运行(否则就会报错,拒绝访问5)
                启动: net start mysql
                关闭: net stop mysql
2. 登录默认登陆的是本地的3306端口对应mysql服务器
        mysql -host -username -password
        mysql -u用户名 -p密码
        mysql -u用户名 -p直接回车
       例如: mysql -uroot -p1234
        登陆指定主机的mysql: mysql -h主机或者ip地址 -u用户名 -p密码
3. 退出
        exit

2.SQL

Structured Query Language：结构查询语言，通过SQL可以操作数据库中的数据。

2.1SQL书写规范

sql的一条语句可以在一行上编写,中间也可以换行至少有一个空格多个空格不影响语法
sql语句的结束符是; 每一句sql语句最后需要分号结尾
sql语句关键词不区分大小写
-- 是注释 #是注释 /**/ 是注释

2.2SQL的分类

官方定义四种

DDL 数据定义语言 (体现于结构性操作)

操作的对象主要是数据库和表

主要的关键词: create alter drop

DML 数据操作语言 ★ ( 体现于数据层面增删改 )

操作的对象主要是记录

主要的关键词: insert update delete

DCL 数据控制语言 ,一般是DBA(数据库管理员)操作

操作的对象主要是用户和权限

主要的关键词: create drop grant(授权) revoke(撤销权限)

TCL 事务控制语言

操作的对象是事务

主要的关键词: commit(提交事务) rollback(回滚)

DQL 数据查询语言,不是官方定义,是程序员定义的.★ ( 不是官方定义的标准 )

操作的对象就是记录

主要的关键词: select

3.DDL

3.1操作数据库

show databases; 展示数据库

创建数据库：

create database 数据库名称;

当数据库不存在的时候才创建：
create database if not exists 数据库名;

指定字符集：
create database 数据库名 character set gbk;

修改数据库：

修改数据库的字符集
alter database 数据库名字 character set 新的字符集;

删除数据库：

格式:
drop database 数据库名字;

其他命令：

查看某个数据库的建库语句:
show create database 数据库名;

查看全部数据库:
show databases;

使用或者切换数据库 :
use 数据库名字;

查看当前工作在那个数据库下 :
select database();

3.2操作表

前提:在指定数据库中进行操作。

创建表：

格式:
create table 表名(
字段1名字字段1数据类型,
字段2名字字段2数据类型
);

例如:
create table user(
id int,
name varchar(8)
);

创建一张和user表结构一模一样的表
create table 新表名 like 表名;
例如 :
create table user1 like user;

修改表：

格式:
alter table 表名操作

往表中添加字段添加一个password字段：
alter table user add column password int;

修改表中字段的类型将password的类型修改为varchar：
alter table user modify password varchar(8);

修改表中字段的名字将password 修改为 pwd：
alter table user change password pwd varchar(8);

删除字段删除pwd字段：
alter table user drop pwd;

修改表名将user1改名为user11：
alter table user1 rename to user11;

删除表：

格式：
drop table 表名;

例如:
drop table user11;

其他命令：

查看当前库下所有的表 :
show tables;

查询某个表的建表语句 :
show create table 表名;

查看表结构(描述表) :
desc 表名;

MySQL中常用的数据类型：

类型	描述
int	整数类型
varchar（n）	字符类型（长度可变，n即为最大长度）
char（n）	字符类型（长度不可变，n即为长度）
float	单精度浮点数类型
double（m,n）	双精度浮点数类型（m总位数，n小数位数）
decimal（m,d）	浮点数类型（没有精度丢失，m表示长度，d表示小数位）
date	日期类型（格式yyyy-MM-dd）
datetime	日期类型（格式yyyy-MM-dd HH:mm:ss）占用8字节
timestamp	日期类型（格式yyyy-MM-dd HH:mm:ss）占用4字节
text	文本

4.DML

数据操作语言, 目标增删改数据。

添加记录：

格式1:给指定的字段赋值
insert into 表名 (字段1,字段2) values (值1,值2);

格式2:给全部字段赋值
insert into 表名 values(值1,值2,...值n);

注意事项:
1.给指定字段赋值的时候,前面括号中有几个字段,后面括号中就有几个值 .且数据类型也要对应上。
2.给全部字段赋值的时候,value中值的顺序要和表结构中的字段的顺序一致.且数据类型也要对应上。
3.插入数据的时候应该使用引号将值引起来.建议使用''或"" ,若值的类型为数字的话,引号可以省略不写。

修改记录：

格式1:修改全部记录
update 表名 set 字段1=值1,字段2=值2 ;

格式2:按条件修改记录
update 表名 set 字段1=值1,字段2=值2 where 条件语句;

删除记录：

格式1:删除全部记录
delete from 表名;

格式2:删除满足条件的记录
delete from 表名 where 条件语句;

5.DQL

query 查询 , 在数据库中官方定义没有DQL语言 , 是开发程序者自己声明的一种语言

DQL比较复杂

基础语法: select * from 表名其他的任意查询都是在这个基础上进行修改

5.1基本查询

基本查询格式：
select * from 表名;-- 查询全部的字段
select 字段1,字段2 from 表名;-- 查询指定的字段

去重查询：（去重:去掉重复 , 多字段完全一致去重）
SELECT distinct 字段,字段,字段... FROM 表名;

别名查询：
SELECT 字段 as 别名,字段 as 别名 ,字段别名... FROM 表名; as可省略

ifnull函数：数据里自带函数
ifnull(列名 , 如果为null 替换成什么)

5.2条件查询

基本格式：
select *|字段 from 表名 where 条件语句;
条件语句支持:关系运算符,逻辑运算符,模糊查询等.

关系运算符 > < >= <= = != <>

逻辑运算符 and or not

between 最小值 and 最大值含头含尾

in 取值为区间范围字段 in (数据1，数据2，数据3)，表示字段等于括号里的值时

模糊查询,支持以下两种写法：
   % 匹配任意字符
   _ 匹配一个字符
格式
   where 字段名 like '匹配规则';

5.3排序

格式1:
select * from 表 [where 条件] order by 排序字段名排序方式;

格式2:
select * from 表 [where 条件] order by 主排序字段名排序方式 ,次排序字段名排序方式;

排序方式: asc(升序,默认值)和desc(降序)

5.4聚合函数

对字段(列)进行统计，会自动忽略null，置于查询字段的位置，不能跟在where后

count(字段名):计数

sum(字段名):求和

max(字段名):最大值

min(字段名):最小值

avg(字段名):平均值

例如：

select sum(math) from student;

5.5分组

格式：
select 分组的字段,聚合函数,聚合函数2 from 表名 [where 分组前的筛选条件] group by 分组字段 [having 分组后的条件筛选]

where和having区别

where 是对分组前的数据进行筛选,having是对分组后的数据进行筛选
where后面不能使用聚合函数,having后面可以使用聚合函数

5.6分页

mysql中是通过limit关键字实现的.mysql的方言。

select * | 字段 from 表 where 条件 [order by 排序字段] limit m,n

m : 开始的索引

n : 向后查询的条数

6.数据库的约束

6.1概述

对表中的数据进行限定，保证数据的正确性、有效性和完整性。

约束的分类:

主键约束:一条记录的唯一标识
唯一约束:字段在该列中唯一
非空约束:字段不能为null
外键约束:多表关系才用
默认值:在不具体指定数据的时候使用的值就是默认值

6.2实现

主键约束

限定某一列的值非空且唯一，主键就是表中记录的唯一标识。

一张表中只能有一个主键.但是主键可以修饰多个字段.

主键的分类:

自然主键:使用实体中一个有具体业务含义的字段作为主键.例如:身份证号,手机号
代理主键:使用实体中没有具体业务含义的字段作为主键.一般会在每张表中引入一个id字段作为主键.

方式1:在建表的时候,在字段名后面通过primary key 声明

create table pk1(
   id int primary key,
name varchar(8)
);

方式2:在建表的时候,在约束区域通过primary key 声明

create table pk2(
   id int,
name varchar(8),
primary key(id)
);

方式3:通过修改表结构,添加主键约

create table pk3(
   id int,
name varchar(8)
);

alter table pk3 add primary key (id);

联合主键：

create table pk4(
   id int,
name varchar(8),
sex varchar(1),
primary key(name,sex) -- 联合主键也可以使用方式3添加主键
);

唯一约束

限定某一列中的值不能重复,对null不起作用。

方式1:在建表的时候,在字段名后面通过unique 声明

create table un1(
   id int,
username varchar(8) unique
);

方式2:在建表的时候,在约束区域通过unique 声明

create table un2(
   id int,
username varchar(8),
unique(username)
);

方式3:通过修改表结构,添加unique 约束

create table un3(
   id int,
username varchar(8)
);

alter table un3 add unique(username);

非空约束

限定某一列的值不能为null。

在建表的时候,在字段名后面通过not null声明：

create table nn(
id int,
username varchar(8) not null
);

外键约束

外键：用于描述表和表之间的关系

外键约束：约束规范外键字段的值应该如何书写 , 保证外键中的数据的有效性和完整性

格式1:通过修改表结构添加外键约束

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

格式2:在创建表的时候,声明外键约束

create table 表名(
字段名称类型,
字段名称类型,
foreign key (外键字段名称1) references 一表1名称(一表主键名称),
foreign key (外键字段名称2) references 一表2名称(一表主键名称)
);

外键约束的特点:

主表中不能删除已被子表引用的数据
从表中不能添加主表中不存在的数据
外键的值可以为null

删除数据的时候需要注意: 先删从表再删主表

删除从表中的数据随便删
删除主表中的数据的时候
- 若从表中没有引用此数据,可以直接删除
- 若从表中引用了此数据,不能删除(会报错)
  - 先把从表中引用的数据删除掉,然后再删主表中的数据
  - 先把从表中引用的数据的外键置为null,然后再删主表中的数据

默认值

限定某一列的默认值，再没有指定的情况下所有列的默认值为null。

字段名字段类型 default '默认值'：

create table person(
id int,
name varchar(8),
country varchar(64) default '中国'
);

7.主键自增

一般作用在主键上,auto_increment,必须保证键的类型支持自增.

create table ai(
id int primary key auto_increment,
name varchar(8)
);

8.truncate的使用

truncate 表名; 删除表,然后再创建一张结构一模一样的表

truncate 表名 和 delete from 表名 区别是什么?

truncate是DDL语句,对表的操作;delete from 是DML语句,对记录的操作
truncate是把之前的表删除,然后创建一张结构一模一样的表. delete from只是清除记录而已
若清除表中大量数据的话 truncate的效率高于delete from

9.蠕虫复制

快速的将一张表数据备份到另一张表中(作备份使用)

1.创建一张一样的表
CREATE TABLE 备份表 LIKE 原表;

2.将数据快速复制过去
INSERT INTO 备份表 SELECT * FROM 原表;

10.表关系

现实生活中，实体与实体之间肯定是有关系的，那么我们在设计表的时候，就应该体现出表与表之间的这种关系！

表与表之间的关系

一对多(多对一):班级和学生,部门和员工,用户和订单
多对多:学生和课程,订单和商品
一对一:公司和注册地,法人和公司,夫妻,公民和身份证号

表与表之间的关系我们也需要存储起来。

11.多表查询

将多个表的数据同时获取出来，并需要将关系对应上。

多表查询的常见分类:

内连接(隐式和显式查询的结果是一样的)
- 隐式内连接
- 显式内连接
外连接
- 左外连接
- 右外连接

11.1笛卡尔积查询

多表查询时左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔积。

SELECT * FROM 表1,表2...表n

11.2内连接

在笛卡尔积的基础上筛选除满足条件的数据。

显式内连接：
SELECT * | 字段
FROM a
[INNER] JOIN b ON a和b关联条件
[INNER] JOIN c ON c和上面结果(ab的结果)的关联条件
WHERE 其他条件

隐式内连接：
SELECT * | 字段
FROM a,b,c
WHERE 关联条件和其他查询条件

11.3外连接

左外连接 : 先展示left左边表中的所有数据,然后根据关联条件去查询右表中的数据,若满足条件,右表中的数据就会展示,否则就会以null展示
SELECT * | 字段
FROM a
LEFT [OUTER] JOIN b ON a和b的关联条件
WHERE 其他查询条件

右外连接 : 先展示right 右边表中的所有数据,然后根据关联条件去查询左表中的数据,若满足条件则展示,否则以null展示
SELECT * | 字段
FROM a
RIGHT [OUTER] JOIN bON a和b的关联条件
WHERE 其他查询条件

12.子查询

当一个查询需要依赖于另外一个查询的结果的时候，我们就把另外的这个查询称之为子查询。

子查询的查询结果:

单行单列的值:一般作为条件使用
多行单列的值:一般作为条件使用
多行多列的值:作为临时表使用

子查询结果为单值：
SELECT * FROM a WHERE 字段 = (SELECT 字段 FROM b WHERE条件)

子查询结果为单列多行

SELECT * FROM a WHERE 字段 IN (SELECT 字段 FROM b WHERE条件) ;

子查询结果为多列多行

SELECT * FROM (SELECT * FROM a WHERE条件) e
INNER JOIN b ON 关联条件

13.自关联

关联查询的表还是本身，这种情况我们称之为自关联。

SELECT a.字段,b.字段 FROM a a
LEFT JOIN a b ON 关联条件

14.SQL执行顺序

SELECT [DISTINCT] 字段1,字段2 | [聚合函数]
FROM 表1
[JOIN 表2 ON 连接条件]
[JOIN 表3 ON 连接条件]
WHERE 查询条件
   [GROUP BY 分组字段 HAVING[分组后条件筛选]]
   [ORDER BY 排序字段排序方式]
   [LIMIT m,n]

先执行from，确定从哪些表查询数据。
join操作。
执行where筛选动作。
group by进行分组，分组执行了聚合函数就执行了。
having筛选动作。
select决定展示哪些数据。
distinct去重操作。
order by对剩下的数据进行排序。
limit进行分页。

15.事务安全TCL

15.1概述

事务：如果一个业务包含多个步骤的操作，那么这些操作要么同时成功，要么同时失败。

例如在张三给李四转账的过程中，张三转出了100元，这个时候网络故障了，李四没有收到钱，可是张三已经转出去了。这就是事务安全的问题。所以在这种业务情境下，这些操作要么成功，要么失败，不允许一方转钱出去了，另一方却没有收到钱。

查看数据库的自动提交事务是否打开：

输入 show variables like '%commit%'; 若为on即为打开，否则关闭。

在MySQL数据库当中，事务默认是自动提交的，每条SQL语句默认使用一个事务。

有些时候，我们应该手动开启事务，然后做操作，若成功了把这些操作都提交(持久化到数据库中)，否则就回滚(刚才的操作都不算数)。

开启事务
执行一条sql语句
提交事务

15.2操作

操作的方式:

方式1:手动开启一个事务，控制事务。

方式2:关闭自动提交，变成手动提交。

手动提交事务

开启事务: start transaction;

提交事务: commit; 提交事务并关闭当前事务

回滚事务: rollback; 回滚事务并关闭当前事

关闭自动提交事务

set autocommit = 0; 或者 set autocommit = off;

15.3事务的原理

所有的操作先放在临时的事务日志中，当commit的时候将日志中的信息写入数据库中；若rollback，会把事务日志中的信息相当于清空操作。

15.4回滚点

当事务开启后，一部分sql执行成功，添加一个回滚点；后续操作报错了，直接到回滚点，保证之前的操作可以成功提交。
涉及到两个命令
- savepoint 回滚点名字;
- rollback to 回滚点名字;

15.5事务的特性

A：原子性，atomicity；一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复到事务开始前的状态，就像这个事务从来没有执行过一样。

C：一致性，consistency；在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。从一个一致性状态改变为另一个一致性状态，一致性和原子性密切相关的。

I：隔离性，isolation；数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

D：持久性，durability；事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

如果不考虑隔离性会产生的问题：

脏读：一个事务读取到另外一个事务未提交的数据。
不可重复读：不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。
幻读(虚读)：在一个事务中，两次统计的结果不一致，是因为某些事务新增或者删除了这些数据导致的。

15.6事务的隔离级别

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	有	有	有
2	读已提交	read committed	无	有	有	Oracle和SQL Server
3	可重复读	repeatable read	无	无	有	MySQL,mysql默认处理了幻读
4	串行化	serializable	无	无	无	单事务

读未提交安全性最低，效率是最高的。

串行化安全性最高，效率是最低的。

15.7设置事务的隔离级别

1. 查看当前数据库隔离级别
select @@tx_isolation;

show variables like '%tx_isolation%;

2. 临时修改隔离级别(只在当前窗口有效)
set session transaction isolation level 级别字符串;

16.DCL

作用:创建或者销毁用户，给用户授权或者撤销权限。

创建用户：

create user '用户名'@'域名或者ip' identified by '密码';

授权：

grant 权限列表(使用','隔开)或者all on 哪个数据库.哪个表 to '用户名'@'主机名'

查看用户有哪些权限：

SHOW GRANTS FOR 'tom'@'localhost';

撤销权限：

revoke 权限列表(使用','隔开)或者all on 哪个数据库.哪个表 from '用户名'@'主机名'

修改登录密码：

不用登录mysql,直接在cmd中输入
mysqladmin -uroot -p password 新密码
根据提示输入原始密码即可

登录mysql
set password for '用户'@'域名或者ip' = '新密码'

删除用户：

drop user '用户名'@'域名或者ip'

17.函数

为了简化操作，mysql提供了大量的函数给程序员使用（比如你想输入当前时间，可以调用now()函数）
函数可以出现的位置：插入语句的values()中，更新语句中，删除语句中，查询语句及其子句中。

17.1日期函数

1. 函数：NOW() | CURDATE() | CURTIME()
   描述：获取系统当前日期时间、日期、时间
   实例：SELECT NOW();

2. 函数：YEAR(DATE) | MONTH(DATE) | DAY(DATE)
   描述：从日期中选择出年、月、日
   实例：SELECT YEAR(NOW());

3. 函数：LAST_DAY(DATE)
   描述：返回月份的最后一天
   实例：SELECT LAST_DAY(NOW());

4. 函数：ADDDATE(DATE,n) | SUBDATE(DATE,n)
   描述：计算起始日期 DATE 加（减） n 天的日期
   实例：SELECT ADDDATE(NOW(),10);

5. 函数：QUARTER(DATE)
   描述：返回日期 DATE 是第几季节，返回 1 到 4
   实例：SELECT QUARTER(NOW());

6. 函数：DATEDIFF(d1,d2)
   描述：计算日期 d1->d2 之间相隔的天数
   实例：SELECT DATEDIFF('2019-08-01','2019-07-01');

7. 函数：DATE_FORMAT(d,f)
   描述：按表达式 f的要求显示日期 d
   实例：SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

17.2数学函数

1. 函数：ABS(x)
   描述：返回 x 的绝对值　　
   实例：SELECT ABS(-1);

2. 函数：CEIL(x) | FLOOR(x)
   描述：向上（下）取整
   实例：SELECT CEIL(1.5);

3. 函数：MOD(x,y)
   描述：返回x mod y的结果，取余
   实例：SELECT MOD(5,4);

4. 函数：RAND()
   描述：返回 0 到 1 的随机数
   实例：SELECT RAND();

5. 函数：ROUND(x) round(x,y) 保留x的y位小数且四舍五入
   描述：四舍五入
   实例：SELECT ROUND(1.23456);

6. 函数：TRUNCATE(x,y) 舍尾法
   描述：返回数值 x 保留到小数点后 y 位的值
   实例：SELECT TRUNCATE(1.23456,3);

17.3字符串函数

1. 函数：CONCAT(s1,s2...sn)
   描述：字符串 s1,s2 等多个字符串合并为一个字符串
   实例：SELECT CONCAT("传", "智", "播", "客");

2. 函数：CHAR_LENGTH(str)
   描述：返回字符串 str 的字符数
   实例：SELECT CHAR_LENGTH("传智播客");

3. 函数：LENGTH(str)
   描述：返回字符串 s 的字节数,中文在utf8占3个字节,在gbk下占2个字节
   实例：SELECT LENGTH("传智播客") ;

4. 函数：UCASE(s) | UPPER(s)
   描述：将字符串转换为大写
   实例：SELECT UCASE("itcast");

5. 函数：LCASE(s) | LOWER(s)
   描述：将字符串转换为小写
   实例：SELECT LCASE("ITCAST");

6. 函数：LOCATE(s1,s)
   描述：从字符串 s 中获取 s1 的开始位置(从1开始)
   实例：SELECT LOCATE('he','itheima');

7. 函数：TRIM(str) | LTRIM(str) | RTRIM(str)
   描述：字符串去空格
   实例：SELECT TRIM(" 传智人");

8. 函数：REPLACE(s,s1,s2)
   描述：将字符串 s2 替代字符串 s 中的字符串 s1
   实例：SELECT REPLACE('abc','a','x');

9. 函数：SUBSTR(s, start, length)
   描述：从字符串 s 的 start 位置截取长度为 length 的子字符串
   实例：SELECT SUBSTR("itcast", 2, 3);

10. 函数：STRCMP(str1,str2)
   描述：比较字符串大小,左大于右时返回1，左等于右时返回0，，左小于于右时返回-1，
   实例：SELECT STRCMP("a","b");

17.4case表达式

语法：
SELECT
        CASE [字段,值]
                WHEN 判断条件1
                THEN 希望的到的值1
                WHEN 判断条件2
                THEN 希望的到的值2
                ELSE 前面条件都没有满足情况下得到的值
        END
FROM table_name;

17.5if表达式

语法：
SELECT IF(1 > 0,'true','false') from table_name;

18.MySQL的性能

18.1慢日志查询

18.1.1查看慢日志查询相关配置

show variables like '%query%'

18.1.2开启慢日志查询

set global slow_query_log = on;

18.1.3设置慢查询日志时间节点（阈值）

全局设置
set global long_query_time=3;

会话设置
set long_query_time=3;

18.2MySQL索引

18.2.1索引的概念

索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。

18.2.2索引的特点

优点：

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

缺点：

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

18.2.3索引的分类

单列索引（给一列字段增加索引） id

组合索引（给多列字段增加索引） id +name

分类：

普通索引：仅加速查询 index
唯一索引：加速查询 + 列值唯一（可以有null） unique (既是约束也是索引)
主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个 primary key (既是约束也是索引)
组合（联合）索引：多列值组成一个索引

18.2.4索引的语法

直接创建索引：

创建普通索引
create index 索引名 on 表名(列名);

创建唯一索引
create unique index 索引名 on 表名(列名);

创建普通组合索引
create index 索引号 on 表名(列名1,列名2);

创建唯一组合索引
create unique index 索引号 on 表名(列名1,列名2);

修改表时指定索引：

添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(列名1,列名2)

添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique(列名1,列名2)

添加普通索引，索引值可以出现多次。
alter table 表名 add index(列名1,列名2)

创建表时一同创建索引：

CREATE TABLE i3(
   id INT,
   username VARCHAR(20),
   email VARCHAR(20),

   PRIMARY KEY(id),
   UNIQUE(email),
   INDEX(username)
);

删除索引：

直接删除
drop index 索引名 on 表名;

修改表时删除【掌握】
alter table 表名 drop index 索引名字;

删除主键
alter table 表名 drop primary key;

18.2.5创建索引的原则

字段内数据的辨识度不能低于70%
在经常需要搜索的列上建索引，这样会大大加快查找速度。
在经常需要连接的列上建索引(外键字段)，可以加快连接的速度。
在经常需要排序的列上建索引，因为索引已经是排过序的，这样一来可以利用索引的排序，加快排序查询速度。
那是不是在数据库表字段中尽量多建索引呢?肯定不是的。因为索引的建立和维护都是需要耗时的
创建表时需要通过数据库去维护索引，添加记录、更新、修改时，也需要更新索引，会间接影响数据库的效率。

18.2.6常见的索引失效情况

1.模糊查询的时候，若在索引列上的左边加上了"%" ,索引失效

where username like 'jack123%' -- 索引有效

where username like '%jack123%' -- 索引失效

2.使用or查询,其中一个条件上没有索引也会降低查询效率

select * from user where id = 123456 or sex = 'female';

3.在索引列上进行计算的,索引失效

select * from user where id + 1 = 1234567;

4. 使用 !=、 <> 、is not null、not 等也会失效尽量不要出现null

5.组合索引要注意最左匹配原则(必须按照顺序出现)，否则也会失效

给user表添加了组合索引 (id,name,email).就相当于添加了三个索引

        id

        id,name

        id,name,email

若查询的时候

select * from user where id = 1;-- 有效

select * from user where id = 1 and name like 'jack%';-- 有效

select * from user where id = 1 and name like 'jack%' and email like '%jack';-- 有效

select * from user where name like 'jack%';-- 无效

select * from user where id = 1 and email like 'jack%';-- 无效

select * from user where name like 'jack%' and email like 'jack%';-- 无效

6. 类型的自动提升导致索引失效 : 尽量保证类型一致

7.where 执行条件的顺序条件1 and 条件2 and 条件3 条件的顺序影响着查询的效率

18.3索引的数据结构

索引是帮助MySQL高效获取数据排好序的数据结构。

索引 = 排序后的数据结构

在没有索引的情况下我们执行一条sql语句，那么是表进行全局遍历，磁盘寻址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。

为了加快的查找效率，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

索引的数据结构：

1.二叉树

一个节点最多有两个子节点，若使用满二叉树存储1kw数据的话，这棵树需要有24层
左节点的数据<右节点的数据
二叉树有可能会变成一个链表

2.红黑树（平衡二叉树） : 树的高度无法控制

可以通过左旋和右旋达到一个自平衡,不会形成一个链表
依然解决不了存储大量数据的问题
jdk1.8 的map < 8 的时候使用链表， > 8使用的是红黑树

3.Hash

存取数据的时候,需要先对数据进行哈希运算，得出一个值，此值就是对应的hash桶
hash桶中的数据结构为链表或者红黑树
- jdk8中hashmap桶中的数据结构：当桶中的数据超过8的时候就由链表转为红黑树；当数据小于6的时候就会由红黑树转为链表
若使用hash做索引的话，查询的速度是比较快,尤其是查询单值查询.但是若是范围查询的话速度就慢了

4.B-Tree (多路搜索树)

一个节点上可以存放更多的数据，一个节点可以拥有更多的子节点
每个节点上保存的数据既有数据内容，还有索引值和指针值
mysql中每个索引节点大小默认值为16k；例如：索引值大小固定(6Byte)，指针值大小固定(8Byte)，数据内容的大小为(1010B)，一个数据加起来正好1kb，一个节点上最多可以存储16个数据，一个节点上可以有17个子节点，只需要6层就可以将1kw数据存储起来

5.B+Tree【MySQL使用】

对B Tree的改进版
- 每个非叶子节点上只保存索引值和指针值，只有叶子节点才保存数据
- 所有的叶子节点使用链表串起来
mysql中每个索引节点大小默认值为16k，一个节点上可以存放1170个元素，只需要3层就可以将1kw数据存储起来；并且做范围查询的时候，只需在叶子节点上直接通过链表的形式就可以获取
mysql为了更快的查询，还会将根节点的索引值预先加载到内存中；假如原先是3层的话，现在只需要2次io就可以找到指定的数据了

查看mysql索引节点大小
show global status like 'innodb_page_size';

MySQL中的 B+Tree 索引结构示意图：

18.4数据库存储引擎

MySQL存储引擎的不同，那么索引文件保存的方式也有所不同，常见的有二种存储引擎MyISAM和InnoDB。

myisam：msyql5.5版本之前的使用，不支持事务。

innodb：mysql5.5(含)之后的默认使用，支持事务。

1.聚集索引

innodb中使用的索引
一张表中只能有一个聚集索引
- 百科上说法：逻辑地址和物理地址是一致的。
- b+tree中叶子节点存放的索引和数据
- 聚集索引一般是主键索引；若没有主键，就找表中的第一个唯一索引；若也没有唯一索引，mysql会默认添加一列，我们看不到。
- 聚集索引一般都是主键索引，主键索引的叶子节点存放了数据。表中其他索引就不再存储数据了，其他索引的叶子节点中存放的主键。若搜索的是其他索引，先找到主键，在通过主键索引找具体的值。

2.非聚集索引