mysql学习笔记

数据库分为关系型数据库和非关系型数据库

关系型数据库

• 指采用了关系模型（二维表格模型）来组织数据的数据库。
• 关系型数据库都支持事务，事务必须具备ACID特性，ACID分别是Atomic原子性，Consistency一致性，Isolation隔离性，Durability持久性。
• 常用软件：Mysql、Oracle、SQL Server等

非关系型数据库

• 以键值对存储，通常支持多种数据结构，支持分布式，且一般不保证遵循ACID原则的数据存储系统。
• 内存数据库：Redis、Memcached等
• 文档数据库：MongoDB等
• 搜索数据库：Elasticsearch，Splunk，Solr等

关系数据库基本概念

• 表和表之间建立“一对多”，“多对一”和“一对一”的关系，以便对应业务处理。
• 表的每一行称为记录（Record）
• 表的每一列称为字段（Column）

常见数据类型：整型、浮点型、字符串、日期、NULL（通常禁止允许为NULL，可以减少程序处理）

主键：能够通过某个字段唯一区分出不同的记录，通常使用id，不能使用业务类字段（哪怕是身份证，邮箱）防止主键冲突。

• 常用的有int、bigint类型作为主键，推荐使用bigint。（如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。）

联合主键：两个或更多的字段都设置为主键，这种主键被称为联合主键。（不推荐，关系表复杂度太高）

外键：A表数据与B表数据关联，通过定义外键约束，关系数据库可以保证无法插入无效的数据，同时节省空间。

DQL（数据查询语言）：查询语句，select
DML（数据操作语言）：数据级别增删改，insert、update、delete
DDL（数据定义语言）：表级别create、drop、alert
TCL（事务控制语言）：commit（事务提交）、rollback（事务回滚）
DCL（数据控制语言）：grant（授权）、revoke（撤销权限）

Mysql逻辑架构图

Client —> Server层（连接、分析、优化、执行） —> 存储引擎层
查询缓存：弊大于利，只要该表有数据更新，则会清空该表所有缓存。适用于配置表等长期不修改的情况使用。

Mysql常见存储引擎

mysql不同版本支持的存储引擎不同。show engines查看支持的存储引擎。

InnoDB：默认存储引擎，支持事务和回滚，最注重安全，哪怕断电数据也不会丢失

MyISAM：支持压缩，可以转换为只读，节省空间

MEMORY：数据和索引存储在内存中，效率最高，但数据不安全。以前被称为HEAP引擎。

常用查询

sql语句的执行顺序

select 
...
from
...
where 
...
group by
...
having
...
order by
...
limit
...

1、from
2、where
3、group by
4、having
5、select
6、order by（排序最后执行）
7、limit

查询数据库版本

select version()

查询当前数据库

select database()

列别名as

select DNAME as 'deptname' from DEPT;  // 其中as可以使用空格替代，效果一样

条件查询where和运算符

+-*/ > < >= <= <>(不等于!=) 
and 和 or （and的优先级高于or）
in（包含，相当于多个or）
not（取非）

select ename, sal+1000 as '年薪' from EMP;

between ... and ... 等价于 >= and <=
select ename, sal from EMP where sal BETWEEN 2450 and 4000

查询值为NULL需要使用is，不能用=号衡量，不是一个值
select ename, sal, comm from EMP  where comm is NULL;

查询工资大于2500且部门编号为10或20
select * from EMP  where sal > 2500 and (deptno = 10 or deptno = 20)

模糊查询like

like（模糊查询，其中 %表示任意多个，下划线 _表示任意一个）
查询名字中包含字母O
select * from EMP  where ename like '%O%'
查询名字以T结尾
select * from EMP  where ename like '%T'
查询名字以T开头
select * from EMP  where ename like 'T%'
查询第2个字母是O
select * from EMP  where ename like '_O%'
查询第3个字母是O
select * from EMP  where ename like '__O%'

\转义字符

排序order by

以sal工资字段排序，默认升序asc，降序desc
select * from EMP ORDER BY sal desc

多个字段排序，先按照sal排序，当sal相同，再按照ename排序
select ename, sal from EMP ORDER BY sal asc, ename asc;

数据处理函数

单行处理函数

输入多少行就返回多少行

trim 去空格
length 长度
lower 小写
upper 大写
select lower(ename) as ename from EMP;

substr 截取字符串（被截取的字符串，起始下标，截取长度）
查询员工第一个字母是A的员工信息
select ename from EMP WHERE SUBSTR(ename,1,1) = 'A' 

concat 字符串拼接
首字母转小写拼接字符串
select CONCAT(LOWER(SUBSTR(ename, 1 ,1)) ,SUBSTR(ename, 2 ,LENGTH(ename))) from EMP

round 四舍五入（字段，进位，1保留1位小数，2保留2位小数，-1保留到十进位......以此类推）
select ROUND(sal, 1) as result from EMP // 查询sal工资字段，保留1位小数

rand() 生成随机数
select ROUND(rand()*100) as result from EMP // 生成100以内随机数

ifnull（数据，转换为什么值） 可以将 null 转换为一个具体值。（有 null 参与的数学运算其结果都为null）
计算年薪，如果结果为null则转为0
select ename, (sal+ifnull(comm,0)) * 12 as result from EMP

str_to_data 将字符串转化为日期
data_format 格式化日期

case...when...then...when...then...else...end
当员工岗位是manager工资上调10%，当岗位是salesman工资上调20%
select ename, job, sal as old_sal,
case job 
when 'MANAGER'
then sal * 1.1
when 'SALESMAN'
then sal * 1.2
else sal
end
as new_sal
from EMP

多行处理函数（分组函数）

定义：输入多行最终返回一行

• 使用分组函数必须先进行分组
• 没有分组默认整张表为一组
• 分组函数：count、sum、avg、max、min
• 分组函数自动忽略NULL（不需要额外处理）
• count(*)和count(字段)区别：count(字段)会统计所有不为NULL的总数
• 分组函数不能直接使用在where子句中
• 所有分组函数可以同时使用

查询平均工资
select avg(sal) from EMP
查询总工资
select sum(sal) from EMP
同时使用分组函数
select SUM(sal), count(*), avg(sal) from EMP

分组查询

• 先分组，再对每一组数据进行操作。
• 在select的group by中，select后只能跟参加分组的字段和分组函数，跟其它的无意义
• 使用having可对group by 后的内容进行过滤
• where和having优先使用where，where不行再用having

1. 按照工作岗位分组，再对工资求和
select job,SUM(sal) from emp GROUP BY job
语句执行顺序解析：
先根据emp表中job字段进行分组
再对工资求和

2. 按照部门编号分组，求每一组的最大值
select MAX(sal),deptno from emp group by DEPTNO

3. 求每个部门不同工作岗位的最高工资。（两个字段联合分组）
select  job, deptno, max(sal) from emp group by deptno,job

4. 找出每个岗位最高工资
select deptno, max(sal) from emp where sal > 3000 group by deptno
select deptno, max(sal) from emp group by deptno having MAX(sal) > 3000
使用having可对group by后的内容进行过滤，where的效率要高于having

5. 找出每个部门平均薪资，显示平均薪资高于2500的
求“平均”这种情况下无法使用where，因为where不能使用分组函数，故使用having
select deptno, avg(sal) from emp group by deptno HAVING avg(sal) > 2000

6. 找出每个岗位平均薪资，显示平均薪资大于2000的，除了MANAGER岗位外，并按照平均薪资降序排列
select job, avg(sal) as avgsal from emp where job <> 'MANAGER' group by job  HAVING avg(sal) > 2000 order by avgsal

去重DISTINCT

distinct只能放在所有字段的最前方。

select DISTINCT job, deptno from emp

连接查询（多表查询）

当两张表进行连接查询，没有任何限制条件时，最终查询结果为两张表的乘积，这种现象被称为笛卡尔积现象。

• 连接时加条件可以避免笛卡尔积现象
• 减少表的连接数量可以提高速度

连接语法分类

sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。
sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where.

SQL99语法：

select 
	...
from
	a
join
	b
on
	a和b的连接条件
where
	筛选条件

连接方式分类

内连接

等值连接

查询每个员工所在部门名称，显示员工名和部门名？
select 
	e.ename,d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno;

非等值连接

找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？
select 
	e.ename,e.sal, s.GRADE 
from 
	emp e 
join 
	salgrade s 
on 
	e.sal > s.LOSAL and e.sal < s.HISAL 
	或者 e.sal BETWEEN  s.LOSAL and s.HISAL 

自连接，一张表看成两张表

查询员工的上级领导，要求显示员工名和对应的领导名？
select 
	e.ename, e1.ename 
from 
	emp e 
join 
	emp e1 
on 
	e.MGR = e1.empno // 员工的领导编号 = 

外连接

左连接
右连接

内连接查询 
select e.ename, d.dname from emp e join dept d on e.DEPTNO = d.DEPTNO

如下图，因为两表地位相等，会将完全匹配的信息查询到，值为NULL的查询不到

外连接查询
select 
	e.ename, d.dname 
from 
	emp e 
right join // right表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。在外连接当中，两张表连接，产生了主次关系。
	dept d 
on 
	e.DEPTNO = d.DEPTNO

左连接换个位置效果一样
select e.ename, d.dname from dept d 
left join 
emp e  on e.DEPTNO = d.DEPTNO

如下图，因为rigth右是主表的，故会查询所有主表数据，非主表ename最后一个字段即使是NULL也会查询出

内连接和外连接的区别

内连接AB两张表地位相当（查询结果会精确匹配结果，会过滤NULL）
外连接一张表是主表，用 rigth 则右边是主表，用 left 则左边是主表（查询结果包含主表所有结果，附带查询非主表信息，即使为NULL也会查询到）

多表联查（大于两张表）

语法：
select 
	...
from
	a
join
	b
on
	a和b的连接条件
join
	c
on
	a和c的连接条件
right join
	d
on
	a和d的连接条件

一条SQL中内连接和外连接可以混合。都可以出现！

例：
找出每个员工的部门名称以及工资等级，
要求显示员工名、部门名、薪资、薪资等级？
SELECT
e.ename, d.DNAME, e.SAL, s.GRADE
FROM
EMP e
JOIN
DEPT d 
on 
e.DEPTNO = d.DEPTNO
JOIN
SALGRADE s 
on 
e.SAL BETWEEN s.LOSAL AND s.HISAL

例：
找出每个员工的部门名称以及工资等级，还有上级领导，
要求显示员工名、领导名、部门名、薪资、薪资等级？
SELECT
e.ename, d.DNAME, e.SAL, s.GRADE, e1.ENAME
FROM
EMP e
JOIN
DEPT d 
on 
e.DEPTNO = d.DEPTNO
JOIN
SALGRADE s 
on 
e.SAL BETWEEN s.LOSAL AND s.HISAL
LEFT JOIN 
EMP e1 
ON
e.MGR = e1.EMPNO

子查询（查询嵌套）

where子句中的子查询

查询比最低工资高的员工姓名和工资
SELECT
	ename, SAL 
FROM
	EMP
WHERE
SAL > (SELECT MIN(SAL) FROM EMP)

from子句中的子查询

from后的子查询可以将子查询的查询结果作为一张临时表

例：找出每个岗位的平均工资的薪资等级。
SELECT
	t.*,
	s.GRADE 
FROM
	(
	SELECT
		job,
		AVG( sal ) AS avgsal # 平均值需要起别名
		
	FROM
		EMP 
	GROUP BY
		job 
	) AS t # 子查询sql别名
	JOIN SALGRADE s ON t.avgsal BETWEEN s.LOSAL 
	AND s.HISAL

union合并查询

union的效率高。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，使用union可以减少匹配的次数完成结果集连接，如下：

	a 连接 b 连接 c
	a 10条记录
	b 10条记录
	c 10条记录
	匹配次数是：1000

	a 连接 b一个结果：10 * 10 --> 100次
	a 连接 c一个结果：10 * 10 --> 100次
	使用union的话是：100次 + 100次 = 200次。（union把乘法变成了加法运算）

例：
原写法
select ename,job from emp where job in('MANAGER','SALESMAN');

使用union写法
select ename, job from emp where job = 'MANAGER'
union
select ename, job from emp WHERE job = 'SALESMAN'

union使用建议：

1. 两个结果集的列数相同
2. 结果集合并时列和列的数据类型也要一致

limit

limit作用：将查询结果集的一部分取出来。通常使用在分页查询当中。
完整用法：limit startIndex, length
startIndex是起始下标，length是长度。起始下标从0开始。

分页
每页显示pageSize条记录
第pageNum页：limit (pageNum - 1) * pageSize , pageSize

创建/删除表

建表属于DDL语句，DDL包括：create drop alter
create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型);

• 表名建议以t_ 或者 tbl_开始，可读性强。

drop table if exists 表名;

mysql中常见数据类型

varchar(最长255)

可变长度的字符串
比较智能，节省空间。
会根据实际的数据长度动态分配空间。

优点：节省空间
缺点：需要动态分配空间，速度慢。

char(最长255)

定长字符串
不管实际的数据长度是多少。
分配固定长度的空间去存储数据。
使用不恰当的时候，可能会导致空间的浪费。

优点：不需要动态分配空间，速度快。
缺点：使用不当可能会导致空间的浪费。

varchar和char我们应该怎么选择？
	性别字段你选什么？因为性别是固定长度的字符串，所以选择char。
	姓名字段你选什么？每一个人的名字长度不同，所以选择varchar。

int(最长11)

数字中的整数型。等同于java的int。

bigint

数字中的长整型。等同于java中的long。

float

单精度浮点型数据

double

双精度浮点型数据

date

短日期类型，只包括年月日信息。标准格式：1990-01-01

datetime

长日期类型，包括年月日时分秒信息。函数：now()

clob

字符大对象
最多可以存储4G的字符串。
比如：存储一篇文章，存储一个说明。
超过255个字符的都要采用CLOB字符大对象来存储。
Character Large OBject:CLOB

blob

二进制大对象
Binary Large OBject
专门用来存储图片、声音、视频等流媒体数据。
往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，
你需要使用IO流才行。

insert多条记录

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

快速创建（复制）表

create table emp2 as select * from emp;
原理：查询结果当作一张表新建出来。

快速插入数据
insert into emp2 select * from emp;

delete(DML)和truncate(DDL)删除的区别

delete属于逻辑删除，实际删除的内容依然保存在磁盘上
缺点：速度慢
优点：可通过rollback回滚数据

truncate是物理删除，表被截断，数据从磁盘上直接删除
优点：速度快
缺点：无法rollback回滚，无法删除单条记录，只能用于清空表

约束（表字段）

约束是为了保证表中的数据有效

1. 非空约束：not null
2. 唯一性约束：unique（具有唯一性，可以为NULL）
3. 主键约束：primary key（PK）
4. 外键约束：foreign key（FK）
5. 检查约束：check（oracle支持）

非空且唯一约束（自动成为主键）

drop table if exists t;
create table t (id int, name varchar(255) not null unique); # 非空且唯一约束字段自动成为主键字断
insert into t (id, name) values (1, ‘zs’)

两个字段联合唯一约束

drop table if exists t;
create table t (id int, name varchar(255), email varchar(255), unique (name, email));
insert into t (id, name, email) values (1, ‘zs’,‘zs@qq.com’);
insert into t (id, name, email) values (1, ‘zs’,‘zhangs@qq.com’);

主键（pk）

主键通常是数字，定长的，不建议使用varchar
单一主键
create table t (id int primary key auto_increment, name varchar(255));
auto_increment表示自增

复合主键（不建议，复杂且无意义，相当于1个）
create table t (id int, name varchar(255), primary key(id, name));
自然主键：不重复即可
业务主键：身份证、银行卡等

外键（fk）

create table t_class(classnum int primary key, classname varchar(255));
子表cno外键引用父表classnum字段
create table t_student(num int primary key auto_increment, name varchar(255), cno int, foreign key (cno) references t_class(classnum));
insert into t_class(classnum, classname) values (101, '一高');
insert into t_class(classnum, classname) values (102, '二高');
insert into t_student(name, cno) values ('zs', 101);
insert into t_student(name, cno) values ('ls', 102);
insert into t_student(name, cno) values ('ww', 103); // 失败，因为没有关联外键id

外键可以为NULL。
外键必须具有唯一性unique，可以不为主键。

事务Transaction）

DML支持事务：insert、delete、update。
事务就是一个完整的业务逻辑（多条DML语句），是最小的工作单元。要么同时成功、要么同时失败。
例如转账：a减少100元（update），b增加100元（update）是一个完整的业务逻辑。
InnoDB存储引擎：提供一组用来记录事务性活动的日志文件。
在事务执行过程中，每一条DML语句都会记录到日志文件中
在事务执行过程中，可以提交事务，也可以回滚事务

提交事务

清空事务性活动文件，将数据持久化到数据库表，成功结束

回滚事务

将之前所有的DML操作撤销，并清空事务性活动文件，失败结束

mysql默认情况下每执行一条sql语句会自动提交事务

开启事务：start transaction
提交事务：commit
回滚事务：rollback（回滚到上一次的提交点）

事务的四个特性

A（原子性）：事务是最小单元，不可再分
C（一致性）：要么同时成功，要么同时失败
I（隔离性）：AB事务之间具有一定的隔离性，如果AB事务同时操作一张表时如何？
D（持久性）：事务提交后，数据会持久化到磁盘上

事务隔离级别

1.读未提交read uncommitted（最低隔离级别）

数据未提交就可以读取到，出现脏读现象。

2.读已提交read committed

提交之后才能读取到，oracle默认是此档
这种隔离级别可以解决脏读现象
存在问题是不可重复读取数据，当事务没有结束时，每次读取到的数据条数可能不同。比如前一秒查询时是2条数据，后一秒查询就变成了3条

3.可重复读repeated read

永远读取的都是事务刚开始时的数据，mysql默认是此档
出现幻读现象。早上9点开启事务，只要事务不结束，到晚上9点，读取到的数据还是那样，读取到的是假象，不够真实。
银行总账查询：下午1点开始查询一直持续到3点，这个期间的交易不会查询到，只会查询截止到1点的数据

4.序列化serializable（最高隔离级别）

效率最低，事务串行处理，不能并发，但最安全。

事务隔离测试

参考大佬：https://www.cnblogs.com/wyaokai/p/10921323.html

查看当前事务
select @@tx_isolation
修改事务隔离模式
set global transaction isolation level read uncommitted;
set global transaction isolation level read committed;
set global transaction isolation level repeated read;
set global transaction isolation level serializable;

索引（Index）

索引的目的是为了缩小扫描范围，快速定位。可以在单个字段上添加索引、也可以多个字段创建联合索引。
索引是需要排序的，在mysql当中索引是一个B-Tree数据结构，底层是一个自平衡的二叉树。遵循左小右大原则存放。采用中序遍历方式遍历取数据。
在实际中，汉语字典前面的目录是排序的，按照a b c d e f…排序，为什么排序呢？因为只有排序了才会有区间查找这一说！（缩小扫描范围其实就是扫描某个区间！）

mysql查询有两种方式

1. 全表扫描
2. 索引扫描

索引的实现原理（B-Tree查询）

1. 在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象。
2. 在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
3. 在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）

什么条件下，我们会考虑给字段添加索引呢？

1. 数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
2. 该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
3. 该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。
建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

索引创建/删除

创建索引：
create index emp_ename_index on emp(ename);
给emp表的ename字段添加索引，起名：emp_ename_index
	
删除索引：
drop index emp_ename_index on emp;
将emp表上的emp_ename_index索引对象删除。

查看一个SQL语句是否使用了索引进行检索？

explain select * from emp where ename = 'KING';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
rows是14，扫描14条记录：说明没有使用索引。type=ALL

create index emp_ename_index on emp(ename);

explain select * from emp where ename = 'KING';
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | emp_ename_index | emp_ename_index | 33      | const |    1 | Using where |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
rows是1，扫描1条记录，索引生效。type=ref

索引失效几种常见情况

1. 模糊匹配当中以“%”开头
explain select * from emp where ename like '%T';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

	
2. 使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引。
explain select * from emp where ename = 'KING' or job = 'MANAGER';
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys   | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | emp_ename_index | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
	
3.使用复合索引的时候，没有使用左侧的列查找，索引失效	
create index emp_job_sal_index on emp(job,sal);
explain select * from emp where job = 'MANAGER';
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | emp_job_sal_index | emp_job_sal_index | 30      | const |    3 | Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
		
4.在where当中索引列参加了运算，索引失效。
create index emp_sal_index on emp(sal);
explain select * from emp where sal+1 = 800;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

5.在where当中索引列使用了函数
explain select * from emp where lower(ename) = 'smith';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

视图（View）

站在不同角度看待同一份数据

创建/删除视图对象

注意：只有DQL语句才能以view的形式创建。
create view view_name as 这里的语句必须是DQL语句;

创建视图对象：
create view dept2_view as select * from dept2;

删除视图对象：
drop view dept2_view;

用视图在实际工作中的作用

假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。
每一次使用这个sql语句的时候都需要重新编写，这种情况下可以使用视图对象。简化开发并且利于后期的维护，修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。

使用视图和使用table一样，可以对视图进行增删改查等操作。对视图对象的增删改查，会导致原表被操作！

// 创建视图对象
create view 
	emp_dept_view
as
	select 
		e.ename,e.sal,d.dname
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno;

// 查询视图对象
mysql> select * from emp_dept_view;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| CLARK  | 2450.00 | ACCOUNTING |
| KING   | 5000.00 | ACCOUNTING |
| MILLER | 1300.00 | ACCOUNTING |
| SMITH  |  800.00 | RESEARCH   |
| JONES  | 2975.00 | RESEARCH   |
| SCOTT  | 3000.00 | RESEARCH   |
| ADAMS  | 1100.00 | RESEARCH   |
| FORD   | 3000.00 | RESEARCH   |
| ALLEN  | 1600.00 | SALES      |
| WARD   | 1250.00 | SALES      |
| MARTIN | 1250.00 | SALES      |
| BLAKE  | 2850.00 | SALES      |
| TURNER | 1500.00 | SALES      |
| JAMES  |  950.00 | SALES      |
+--------+---------+------------+

// 面向视图更新
update emp_dept_view set sal = 1000 where dname = 'ACCOUNTING';

// 原表数据被更新
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 1000.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 1000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1000.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

数据库设计三范式

目的：避免表中数据的冗余、空间的浪费

• 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

• 第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。

• 第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

第一范式案例

学生编号 学生姓名 联系方式
------------------------------------------
1001		张三		zs@gmail.com,1359999999
1002		李四		ls@gmail.com,13699999999
1001		王五		ww@163.net,13488888888
以上表设计不满足第一范式，其一没有主键、其二contact可以再分成email和phone

第二范式案例

id		name		t_id		t_name
----------------------------------------------------
1001			张三		001		王老师
1002			李四		002		赵老师
1003			王五		001		王老师
1001			张三		002		赵老师
以上表设计不满足第二范式，属于典型的多对多关系，最佳实践是使用3张表来表示，如下

学生表
学生编号(pk)		学生名字
------------------------------------
1001					张三
1002					李四
1003					王五

教师表
教师编号(pk)		教师姓名
--------------------------------------
001					王老师
002					赵老师

学生教师关系表
id(pk)			学生编号(fk)			教师编号(fk)
------------------------------------------------------
1						1001						001
2						1002						002
3						1003						001
4						1001						002

第三范式案例

id（PK） name class_id  class_name
---------------------------------------------------------
1001				张三		01			一年一班
1002				李四		02			一年二班
1003				王五		03			一年三班
1004				赵六		03			一年三班
以上表设计不满足第三范式，class_id 和 class_name 中一年一班依赖01，
产生了传递依赖，属于1对多的关系，最佳实践是采用2张表来表示，如下：

班级表：一
班级编号(pk)				班级名称
----------------------------------------
01								一年一班
02								一年二班
03								一年三班

学生表：多
学生编号（PK） 学生姓名 班级编号(fk)
-------------------------------------------
1001				张三			01			
1002				李四			02			
1003				王五			03			
1004				赵六			03		

总结表的设计

一对多，两张表，多的表加外键
多对多，三张表，关系表两个外键
一对一，外键唯一(fk+unique)

一对一情况案例
在实际的开发中，可能存在一张表字段太多，太庞大。这个时候要拆分表。
没有拆分表之前：一张表
t_user
id		login_name		login_pwd		real_name		email				address........
---------------------------------------------------------------------------
1			zhangsan		123				张三				zhangsan@xxx
2			lisi			123				李四				lisi@xxx
...
	
这种庞大的表建议拆分为两张：
t_login 登录信息表
id(pk)		login_name		login_pwd	
---------------------------------
1				zhangsan		123			
2				lisi			123			

t_user 用户详细信息表
id(pk)		real_name		email				address........	login_id(fk+unique)
-----------------------------------------------------------------------------------------
100			张三				zhangsan@xxx								1
200			李四				lisi@xxx										2

数据库设计三范式是理论上的。实践和理论有的时候有偏差。最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。
因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）
有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低。

LOAD DATA LOCAL INFILE（数据从文件导入）

参考官网：https://dev.mysql.com/doc/refman/5.6/en/load-data.html

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

csv文件案例

Name,VolumeId,VolumeType,VolumeSize,VolumeState,InstanceId,InstanceState,cluster,project,owner,environment,component,datatime,year,month,day
haha,vol-01,gp3,100,in-use,i-01,running,haha,devops,devops,prod,prometheus,2021-10-22,2021,10,22
heihei,vol-02,gp3,60,in-use,i-02,running,heihei,devops,devops,prod,backend,2021-10-22,2021,10,22

表结构

-- `aws-instance`.volumes definition

CREATE TABLE `volumes` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `VolumeId` varchar(255) DEFAULT NULL,
  `VolumeType` varchar(255) DEFAULT NULL,
  `VolumeSize` int(11) DEFAULT NULL,
  `VolumeState` varchar(255) DEFAULT NULL,
  `InstanceId` varchar(255) DEFAULT NULL,
  `InstanceState` varchar(255) DEFAULT NULL,
  `cluster` varchar(255) DEFAULT NULL,
  `project` varchar(255) DEFAULT NULL,
  `owner` varchar(255) DEFAULT NULL,
  `environment` varchar(255) DEFAULT NULL,
  `component` varchar(255) DEFAULT NULL,
  `datatime` date DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `month` int(11) DEFAULT NULL,
  `day` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8191 DEFAULT CHARSET=utf8mb4;

python案例

def csv_to_my():
    file_path = "volumes.csv"
    table_name = "volumes"
    try:
        con = pymysql.connect(user="root",
                              passwd="123456",
                              db="aws-instance",
                              host="localhost",
                              port=3307,
                              local_infile=1)
        con.set_charset('utf8')
        cur = con.cursor()
        cur.execute("set names utf8")
        cur.execute("SET character_set_connection=utf8;")

        with open(file_path, 'r', encoding='utf8') as f:
            reader = f.readline()
            # 做成列表
            devide = reader.split(',')
            # 去除最后的换行符
            devide[-1] = devide[-1].rstrip('\n')
		# 默认导入会将id字段覆盖，所以需要将字段名显示的指定出来。
        data = 'LOAD DATA LOCAL INFILE \'' + file_path + '\'REPLACE INTO TABLE ' + table_name + \
               ' CHARACTER SET UTF8 FIELDS TERMINATED BY \',\' LINES TERMINATED BY \'\n\' IGNORE 1 LINES (Name,VolumeId,VolumeType,VolumeSize,VolumeState,InstanceId,InstanceState,cluster,project,owner,environment,component,datatime,year,month,day);'

        cur.execute(data.encode('utf8'))
        print(cur.rowcount)
        con.commit()
    except:
        print("发生错误")
        con.rollback()

    finally:
        cur.close()
        con.close()