阶段三-Day04-MySQL_TCL_索引_DCL

一、子查询(Sub Query)

1. 什么是子查询

1. 子查询概念

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

2. 子查询的特点

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

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

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

2. 单行子查询

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

3. 多行子查询

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

• in : 等于任意一个

  使用方式:

  in(值1，值2 ...)

• all: 所有

  使用方式:

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

? ? ? ?也可以使用单行查询(大于最大值 | 小于最小值)

• any: 任意一个

  使用方式

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

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

???????也可以使用单行查询(大于最小值 | 小于最大值)

注意事项:

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

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

use lsh04;
#分类表 (一方 主表)
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表示下架
	cid varchar(32),
	-- 添加外键约束
	foreign key (cid) references category (cid)
);

#分类数据
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 values('p001','小米电视机',5000,'1','c001');
insert into products values('p002','格力空调',3000,'1','c001');
insert into products values('p003','美的冰箱',4500,'1','c001');
insert into products values('p004','篮球鞋',800,'1','c002');
insert into products values('p005','运动裤',200,'1','c002');
insert into products values('p006','T恤',300,'1','c002');
insert into products values('p007','冲锋衣',2000,'1','c002');
insert into products values('p008','神仙水',800,'1','c003');
insert into products values('p009','大宝',200,'1','c003');


select * from category;
select * from products;

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

#2 查询化妆品分类下的 商品名称 商品价格
-- 1.查询化妆品分类的cid
select cid from category where cname = '化妆品';
-- 2.查询化妆品的商品名称和价格
select pname,price from products where cid = (select cid from category where cname = '化妆品');

#3 查询小于平均价格的商品信息
-- 1.获取平均价格
select avg(price) from products;
-- 2.查询小于平均价格的商品信息
select * from products where price < (select avg(price) from products);

-- 多行子查询
#1 查询化妆品类别中的商品价格和鞋服类别中的商品价格一样的商品信息
-- 1.查询化妆品中的商品价格
select price from products where cid = 'c003';
-- 2.查询鞋服中的商品价格
select price from products where cid = 'c002';
-- 3.进行判断
select * from products where cid = 'c003' and price in
(select price from products where cid = 'c002');

#2 查询价格比所有鞋服类别中商品的价格都高的商品信息
-- 1.查询鞋服商品的商品价格
select price from products where cid = 'c002';
-- 2.查询
select * from products where price > all(select price from products where cid = 'c002');

#3 查询价格比任意一个鞋服类别中商品的价格高的商品信息
-- 1.查询鞋服商品的价格
select price from products where cid = 'c002';
-- 2.判断
select * from products where price > any
(select price from products where cid = 'c002');

二、存储引擎

1. 概述

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

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

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

在MySQL中默认支持的存储引擎有8个。 federated 不支持。主要用来提供对远程MySQL服务器上面的数据的访问接口。

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

2. 各个引擎的介绍

1. InnoDB 默认的存储引擎，也是所有存储引擎中唯一支持事务、XA协议的存储引擎。

2. MyISAM 基于ISAM（Indexed Sequential Access Method目前已经废弃）的存储引擎，特点是查询效率较高。但不支持事务和容错性。

3. MEMORY 纯内存型型存储引擎。所有数据都在内存中，硬盘只存储.frm文件。所以当MySQL宕机或非法关闭时只生效表结构。当然了，由于所有数据都在内存上，所以相对来说性能较高。

4. MRG_MYISAM 以前也叫MERGE，简单理解就是对MyISAM表做了特殊的封装，对外提供单一访问入口，减少程序的复杂性。

5. ARCHIVE存储引擎主要用于通过较小的存储空间来存放过期的很少访问的历史数据。ARCHIVE表不支持索引，通过一个.frm的结构定义文件，一个.ARZ的数据压缩文件还有一个.ARM的meta信息文件。由于其所存放的数据的特殊性，ARCHIVE表不支持删除，修改操作，仅支持插入和查询操作。

6. BLACKHOLE 俗称“黑洞”存储引擎。是一个非常有意思的存储引擎。所有的数据都是有去无回。

7. CSV存储引擎实际上操作的就是一个标准的CSV文件，他不支持索引。起主要用途就是大家有些时候可能会需要通过数据库中的数据导出成一份报表文件，而CSV文件是很多软件都支持的一种较为标准的格式，所以我们可以通过先在数据库中建立一张CSV表，然后将生成的报表信息插入到该表，即可得到一份CSV报表文件了。

8. PERFORMANCE_SCHEMA 从MySQL 5.6新增的存储引擎。主要用于收集一些系统参数

官方文档参考地址：MySQL :: MySQL 8.0 Reference Manual :: 16 Alternative Storage Engines ?

三、数据库事务控制(TCL)

1. 什么是事务

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

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

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

比如: 银行的转账业务,张三给李四转账500元 ，至少要操作两次数据库，张三 -500，李四 + 500,这中 间任何一步出现问题,整个操作就必须全部回滚，这样才能保证用户和银行都没有损失。

2. 模拟转账操作

-- 创建数据库表
-- 创建账户表
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，完成转账的操作至少需要以下两步
-- 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 元，数据就出现问题了

jack并没有收到500

3. MySQL事务操作

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

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

2. 手动提交事务

3.1 手动提交事务

语法格式:

?1.start transaction或者begin开启事务

?2.commit 提交事务,将数据更新写道磁盘中

?3.rollback撤销事务,在事务运行过程中事务发生故障,系统将事务中对数据库的所有操作全部撤销

3.2 手动提交事务的流程

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

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

3.3 手动控制事务的转账案例

• 手动开启事务 start transaction

  手动开启事务后，事务不再是自动提交

• 提交事务 commit

  没有出现任何问题，提交事务 数据就会持久化到本地磁盘

• 回滚事务 rollback

  出现问题回滚事务，回滚事务要在提交事务之前操作,一旦事务完成了提交，就不能 再回滚. 如出现宕机等情况事务会自动回滚

-- 开启事务(事务开启后必须提交或者回滚,否则事务不会关闭)
start transaction;

-- tom账户 -500
update account set money = money-500 where name = 'tom';
-- jack账户 +500
update account set money = money+500 where name = 'jack';

-- 提交事务(不提交就不会对本地数据进行修改)
commit;
-- 回滚事务(出现问题回滚，
-- 如果出现宕机等原因事务会自动回滚)
rollback;

注意:

不提交事务的后果

如果MySQL事务不进行提交，那么在这个事务中做的一系列操作将不会真正被执行。这意味着，添加、更新、删除等许多重要的操作都将被忽略。换而言之，您会无意中破坏数据的完整性，从而导致计算机系统的异常。

不回滚事务的后果

不回滚MySQL事务可能会更加严重，因为当你不进行回滚时，MySQL大概率会保持这个事务所做的所有更改，甚至后续操作都会受到影响。这将导致数据错误、系统崩溃或者其他严重问题。

3.4 事务的四大特性 ACID

4. MySQL事务的

4.1 数据并发访问

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

4.2 并发访问产生的问题

事务在操作时的理想状态：所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据，可能引发并发访问的问题。

?

4.3 四种隔离级别

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

? 会出现问题 ? 不会出现问题

注意事项:

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

MySQL中:

采用MVCC多版本控制机制，读取时采用快照读，相当与复制一份，查询快照版本,其他修改原本的数据,解决不可了重复读，和部分幻读。

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

4.4 隔离级别相关命令

1. 查看隔离级别

select @@transaction_isolation;

? ? ?2.设置隔离级别(只是本次窗口改变隔离等级,退出后返回默认值)

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

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

MySQL默认是repeatable read(可重复读),可以 通过修改MySQL配置文件改变默认隔离等级

四、索引

索引是一种数据结构，通过索引可以快速的找到需要查询的内容。

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

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

2. 索引的结构

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

2.1 HASH结构

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

缺点:

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

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

2.2 BTREE结构

BTree分为BTree(B-Tree)和B+Tree，MySQL数据库索引采用的B+Tree，B+Tree是在BTree

(B-Tree)上做了优化改造。

2.2.1 B-Tree结构

• 索引值和data(数据)分布在整棵树结构中

• 每个节点可以存放多个索引值以及对应的data(数据)

• 树节点中的多个索引值从左到右升序排列

缺点:

所有的节点都存放数据，数据会占用空间，导致存放的索引变少。大量数据时会使树变得越来越高

2.2.2 B+Tree结构

• 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值

• 叶子节点包含了所有的索引值和data数据

• 叶子节点用指针连接，提高区间的访问性能

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

2.2.3 其它树结构回顾

树结构	优点	缺点
二叉树	可以进行范围查询	树会失去平衡，在进行数据查询的时候需要比较N多次才行
AVL数	可以通过算法自动的平衡	AVL树对平衡的要求比较严格，稍微有一点失衡直接就使用算法进行平衡，这个过程是比较耗费时间
红黑树	相当于二叉树和AVL树的中间产物，红黑树会追求绝对的平衡，所以不会有很多次的裂变	树特别深的话，比较起来还是很耗时的

3. 索引的优点

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

• 可以加速表和表之间的连接，特别是在实现数据的完整性方面特别有意义。

• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

• 通过使用索引，可以在查询的过程中 ，使用查询优化器，提高系统的性能。

4. 索引的缺点

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

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

1. 在经常需要搜索的列上，可以加快搜索的速度；

2. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

3. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

6. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

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

1.对于那些在查询中很少使用或者参考的列不应该创建索引。

2.对于那些只有很少数据值的列也不应该增加索引。

3.对于那些定义为text，image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

4.当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

7. 索引分类

7.1 介绍

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

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

7.2 单列索引

单列索引又分为：主键索引、普通索引和唯一索引。

7.2.1 主键索引

特点:

• 它是一种特殊的唯一索引，不允许有空值

• 在创建或修改表时添加主键约束即可，添加了主键约束就会自动创建主键索引

• 每个表只能有一个主键约束，所以一张表只能有一个主键索引

7.2.2?唯一索引

特点:

索引字段的值必须唯一，但允许有空值。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引

7.2.3?普通索引

特点:

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

-- 1.主键索引(创建主键:3种方式)
create table t1(
	id int primary key,
	tname varchar(10)
);
create table t2(
	id int ,
	tname varchar(10),
	primary key(id)
)

create table t3(
	id int ,
	tname varchar(10)
)
alter table t3 add primary key(id);
-- 展示索引信息
show index from t3;

-- 创建唯一索引
-- 1.唯一约束创建唯一索引
create table t4(
	id int primary key,
	tname varchar(10),
	email varchar(15) unique
);
show index from t4;#主键索引,唯一索引

-- 2.创建完表后添加唯一索引
create table t5(
	id int primary key,
	tname varchar(10),
	email varchar(15)
)
show index from t5;
-- aa索引名可以省略
alter table t5 add unique index aa (tname);
-- bb索引名不可以省略
create unique index bb on t5(email)

-- 创建普通索引
create table t6(
	id int primary key,
	tname varchar(10),
	email varchar(15) unique,
	age int,
	sex char(1)
)

show index from t6;
-- aaa可以省略
alter table t6 add index aaa (age);
-- bbb不能省略
create index bbb on t6(sex);

7.3 组合索引

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

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

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

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

create index index3 on demo(col1,col2)

此时相当于创建col1、col1-col2、col1-col3、col1-col2-col3四个索引。

7.4 全文索引

7.4.1 介绍

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

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

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

全文索引对中文支持不好，如果搜索中文就只能按照最

左对照进行搜索。如果是英文就可以匹配中间。后续会使用字符专用拆字器

7.4.2 中文拆词器

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

两种方法:

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

ngram_token_size=2

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

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

use lsh04;
-- 全文索引
-- 1.创建表时创建
drop table tb;
create table tb(
		id int(11) primary key auto_increment,
		tname varchar(100),
    address varchar(200),
    fulltext index_name (tname)
);
show index from tb;

-- 创建完表后创建
alter table tb add fulltext aa(address);

alter table tb drop index aa;
-- 全文索引的使用
-- 默认按照英文的空格进行拆分
insert into tb values(1,'tony','beijing yizhuang');
insert into tb values(2,'kevin','beijing yizhuang jingkaiqu');
insert into tb values(3,'jordan','beijing daxing');

select * from tb where match(address) against
('yizhuang');
-- against后面有三种情况
-- 1.自然语言模式
-- 必须严格匹配其中的内容
select * from tb where match(address) against
('yizh');-- 找不到

-- 2.布尔模式
-- 支持特殊符号,查询时必须从最左开始查询
-- *万用字 daxin*指:daxin后面是啥都行
select * from tb where match(address) against('daxin*' in boolean mode);

-- 3.扩展模式
-- 发现和条件有关系的内容都查询出来
insert into tb values(4,'kelvin','hebei baoding');
select * from tb where match(address) against('daxing' with query expansion);
-- bejing daxing 包含这里的所有都能查询出来
-- 扩展模式 带有daxing,beijing都可以查询出来

-- 中文拆词器
drop table tb1;
create table tb1(
	id int(11) primary key auto_increment,
	name varchar(100),
  address varchar(200),
  FULLTEXT index_name (name) with parser ngram
);
show index from tb1;
insert into tb1 values(default, '你多大了呀', 'sh');
insert into tb1 values(default, '多大了呀', 'sh');
insert into tb1 values(default, '你多大了呀啊', 'sh');
insert into tb1 values(default, '大了呀', 'sh');

select * from tb1 where match(name) against("多大");
select * from tb1 where match(name) against("你多"); -- 自然模式
select * from tb1 where match(name) against("多*" in boolean mode); -- 布尔模式
select * from tb1 where match(name) against("你多" with query expansion); -- 拓展模式

8. 聚集索引和非聚集索引

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

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

五、count(*),count(列),count(1)的区别

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

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

count(列)列中有索引,则count(列)和count(*)效率一样,如果没有索引,count(1)找到了索引,则count(1)更快,如果都没有索引,则一样块

六、索引优化

1. 介绍

索引的缺点

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

2. 优化案例(SQL调优后面会讲)

使用短索引（前缀索引）

对与长字符仅对前几个字符添加索引,且要保证唯一

索引列排序

like语句操作

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

不要在列上进行运算

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。先筛选,之后再计算,否则每一行都会调用year()方法

范围列可以使用索引

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

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

类型转换会导致索引无效

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

3. 总结

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

1. 不要在where后的条件中进行列的运算和函数操作

2. 模糊查询时，like‘%xxx%’，放弃使用索引二进行全表扫描。like‘xxx%’，可以使用索引索引级别为range。

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

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

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

七、视图

1. 什么是视图

1. 视图是一种虚拟表

2. 视图建立在已有表的基础上，视图建立依赖的这些表称为基表

3. 向视图提供数据内容的语句为 SELECT 语句，可以将视图理解为存储起来的 SELECT 语句

4. 视图向用户提供基表数据的另一种表现形式

2. 视图的作用

简化复杂的查询

1. 视图本身就是一条查询SQL，我们可以将一次复杂的查询构建成一张视图，用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)，可以理解为查询视图就相当于将创建视图的SQL再次执行一次

2. 视图主要就是为了简化复杂查询

3. 视图的使用

use lsh04;
-- 创建视图
-- 将id>2的创建视图
create view vie as 
select name from tb1 where id > 2;
-- 查询视图和表一样
-- 删除视图
drop view vie;

4. 视图与表的区别

• 视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示

• 通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列 经过计算得到的结果,不允许 更新）

• 删除视图，表不受影响，而删除表，视图不再起作用

八、存储过程(和java中的函数很像)

1. 存储过程介绍

存储过程就是数据库中保存(Stored)的一系列SQL命令（Procedure）的集合。也可以将其看作相互之间有关系的SQL命令组织在一起形成的一个方法。

2. 存储过程的优点

• 提高执行性能。存储过程执行效率之所高，在于普通的SQL语句，每次都会对语法分析，编译，执行，而存储过程只是在第一次执行语法分析，编译，执行，以后都是对结果进行调用。

• 可减轻网络负担。使用存储过程，复杂的数据库操作也可以在数据库服务器中完成。只需要从客户端(或应用程序)传递给数据库必要的参数就行，比起需要多次传递SQL命令本身，这大大减轻了网络负担。

• 可将数据库的处理黑匣子化。应用程序中完全不用考虑存储过程的内部详细处理，只需要知道调用哪个存储过程就可以了。

3. 使用

use 
-- 存储过程
-- 定义一个不带放回置的存储过程
create procedure mypro1(in name varchar(10))
begin
		if name is null or name = '' then
				select * from emp;
		else
				select * from emp where ename like concat('%',name,'%');
				end if;
end;
-- 调用方法
call myproc1('');
call myproc1('好');
-- 删除存储过程
drop procedure mypro1;

-- 定义存储过程，有返回值
create procedure mypro2(in name varchar(10), out num int)
begin
		if name is null or name = '' then
		select * from emp;
	else
		select * from emp where ename like concat('%',name,'%');
		end if;
		-- 返回值
		select found_rows() into num;
end;

-- 运行存储过程
-- @表示局部变量 @@表示系统变量
call myproc2('好',@num);
select @num;

说明：

1. 分隔符

   MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码；“DELIMITER ;”的意思是把分隔符还原。

2. 局部变量

   在mysql中声明局部变量使用@+变量名。(两个@是系统变量)

九、数据控制语言(DCL)

1. 用户管理

MySQL中可创建不同的用户，并分配不同的权限，保证MySQL中数据的安全性

MySQL用户主要包括两种：

1. root用户为超级管理员

拥有MySQL提供的所有权限

?????2.普通用户:

权限取决于该用户在创建时被赋予的权限有哪些. 没有赋予任何权限，只有可以登录mysql的权限

用户表存在于mysql库中的user表中，要先选择mysql库再进行操作'

2. 权限管理

• MySQL通过权限管理机制可以给不同的用户授予不同的权限，从而确保数据库中数据的安全性

• 只能给存在的用户授权

• 新创建的用户只有登录的权限(USAGE)

3. 角色管理

MySQL 数据库中通常都会出现多个拥有相同权限集合的用户，在之前版本中只有分别向用户授予权限和撤销权限. 在用户数量比较多的时候，这样的操作是非常耗时的。

MySQL 8.0 为了用户权限管理更容易，提供了一个角色管理的新功能。角色是指定的权限集合，和用户帐户一样可以对角色进行权限的授予和撤销。

如果用户被授予角色，则该用户就拥有了该角色的权限。

-- 数据控制语言DCL
-- 1.查询所有用户
use mysql;
select * from user;

-- 创建用户zs
create user 'zs'@'localhost' identified by '123';
-- 修改密码
alter user 'zs'@'localhost' identified by '456';
-- 删除用户
drop user 'zs'@'localhost';

-- 权限管理
-- 为某个用户授权
-- 1.授予数据库lsh04中表tb1的权限
grant select on lsh04.tb1 to 'zs'@'localhost';
-- 2.给用户zs授予所有库所有表的权限
grant all on *.* to 'zs'@'localhost';
-- 3.刷新权限(授权操作之后一定要刷新权限)
flush privileges;
-- 4.查看用户的权限
show grants for 'zs'@'localhost';

-- 给角色设置权限,再把角色赋予用户,提高效率
-- 1.创建角色
create role 'role1';
-- 2.为角色赋予权限
grant select on lsh04.tb1 to 'role1';
grant all on *.* to 'role1';
-- 3.查看角色权限
show grants for 'role1';
-- 4.用户分配角色
grant 'role1' to 'zs'@'localhost';
-- 5.查看用户的权限以及角色
show grants for 'zs'@'localhost';
-- 6.使角色在账号登录后自动激活
set global activate_all_roles_on_login=ON;

4. 开启远程连接

# 创建用户
create user 'cy'@'%' identified by '123456';

#授权
#grant all on *.* to 'cy'@'%';
grant select on *.* to 'cy'@'%';

#刷新权限
flush privileges;

十、数据库设计

1. 数据库三范式(空间最省)

概念: 三范式就是设计数据库的规则，是符合某一种设计要求的总结

1. 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据 库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式

2. 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的 称为第二范式（2NF） ， 其余范式以此类推。一般说来，数据库只需满足第三范式(3NF）就 行了

2. 第一范式 1NF

概念: 原子性，做到列不可拆分

第一范式是最基本的范式。数据库表里面字段都是单一属性的，不可再分，如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式

示例:

地址信息表中，contry这一列,还可以继续拆分,不符合第一范式。 ?

3. 第二范式 2NF

概念:

1. 在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关

2. 一张表只能描述一件事

示例: 学员信息表中其实在描述两个事物 ，一个是学员的信息,一个是课程信息 如果放在一张表中,会导致数据的冗余,如果删除学员信息，成绩的信息也被删除了。

4. 第三范式 3NF

概念:

• 消除传递依赖

• 表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放

示例: 通过number 与 price字段就可以计算出总金额,不要在表中再做记录(空间最省)。