MySQL笔记

1 条件查询执行顺序

语法格式select from where
执行顺序：先from 然后where，最后select

以下执行顺序，重要！

先from表，order by 一般是靠近后面执行的，limit最后执行

2 查询时括号的使用

当运算符优先级不确定的时候加小括号
3 查询时in的用法

in等同于or，in括号后面的值不是区间，是具体的值。相等于并列的值，用or连接。
not in 不在这几个值当中

4 like模糊查询

在模糊查询中，必须掌握两个特殊的符号，一个是%，一个是_。
%代表任意多个字符，_代表任意一个字符。

找出名字中含有O的：

找出名字中第二个字母是A的：

5 order by 排序

按照工资升序排序：

select ename , sal from emp order by sal;

默认是升序，asc表示升序，desc表示降序。

可指定多个排序字段，如按照工资降序排序，工资相同时按照姓名升序排序：

找出工作岗位是SALESMAN的员工，并且按照薪资的降序排列：

select ename, job, sal from emp where job = 'SALESMAN' order by sal, desc;

执行顺序：先执行from，再执行where，再执行select，最后排序输出。

order by 一般是最后执行的。

6 分组函数

count 计数
sum 求和
avg 求平均数
max 求最大值
min 求最小值

分组函数自动忽略NULL

记住：所有的分组函数都是对某一组数据进行操作的。
分组函数还有一个名字：多行处理函数
即：输入多行，输出一行

单行处理函数：输入一行，输出一行。
ifnull(可能为NULL的数据，被当作什么处理)
ifnull(comm, 0) 如果comm字段数据为NULL，当成0处理。

找出工资高于平均工资的员工：

select ename, sal from emp where sal > avg(sal);

出错！where子句中不可以使用分组函数

错误原因分析：

因为group by 是在where执行之后才执行的，而分组函数又是在group by函数分组之后才执行，所以，语法错误。

如下图：非常重要的执行顺序：

解决方法：
分两步查询：子查询，第二步使用第一步的结果：

查询每个岗位的最高工资：

select max(sal), job from emp group by job;

注意：记住一个规则：

当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。
select其他字段没有意义。

7 count函数

count(*) ：所有记录的数量
count(某个字段)：该字段不为NULL的记录数量

8 分组查询group by

group by：按照某个字段或某些字段进行分组查询
having： having是对分组之后的数据进行再次过滤

找出每个工作岗位的最高薪资：

select max(sal) from emp group by sal;

注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
并且任何一个分组函数（count，sum，avg，min，max）都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话，整张表的数据会自成一组。

9 having和where区别

where:

where是一个约束声明,使用where来约束来自数据库的数据;
where是在结果返回之前起作用的;
where中不能使用聚合函数。

having:

having是一个过滤声明;
在查询返回结果集以后，对查询结果进行的过滤操作;
在having中可以使用聚合函数。 

where和having的执行顺序

where 早于 group by 早于 having
where子句在聚合前先筛选记录，也就是说作用在group by 子句和having子句前，而 having子句在聚合后对组记录进行筛选

where不能使用聚合（分组）函数、having中可以使用聚合（分组）函数

例子1：

找出每个部门的平均薪资，要求显示薪资大于2000的数据(这个时候要用having，用where搞不定)：

selec deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;	
//正确

selec deptno, avg(sal) from emp where avg(sal) > 2000 group by deptno ;	
//错误，where后面不能使用分组（聚合）函数

例子2：

找出每个部门的最大薪资，要求显示薪资大于2900的数据（这个时候可以使用where提前过滤掉小于2900的数据，使用having计算量会变大）：

select max(sal), deptno from emp where sal > 2900 group by deptno ;	
//正确，效率高，提前使用where选择大于2900的数据，分组只针对这些大于2900的数据

select max(sal), deptno from emp group by deptno having sal > 2900;	
//正确,但是效率低，带着所有数据进行分组，过滤

https://blog.csdn.net/yajie_china/article/details/80089553

10 外连接
内连接：
等值连接：（利用等号，将两个表满足等号的查询出来）
非等值连接：（利用范围，将两个表满足范围的查询出来）
自连接：（同一个表当成两张表进行连接）
外连接：
左外连接：（左边的这张表是主表）
右外连接：（右边的这张表是主表）

外连接最大的特点是：主表的数据无条件的查询出来。
相反，采用内连接，会导致数据丢失，只保留满足条件的数据

11 子查询

被嵌套的select语句是子查询

where子句中使用子查询：

找出高于平均薪资的员工信息

select * from emp where sal > (select avg(sal) from emp);

12 from后面嵌套子查询

将select语句查询结果当作一个临时表：

13 limit以及通用分页SQL

limit用于取结果集中的部分数据

语法机制：

limit startIndex length
startIndex表示起始位置，从0开始，0表示第一个数据
length表示取几个

案例：取出工资前五名的员工

执行顺序：

代码中使用分页查询：

14 表的创建

char和varchar的区别

https://blog.csdn.net/qq_20264581/article/details/83755789

15 往表中插入数据

insert语句插入数据

16 update修改数据

使用update修改表中数据，注意，使用where条件选择要修改的的数据，否则会整张表数据全部更新。

17 删除数据

delete删除数据，注意：没有where的话全部删除

18 约束

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证数据的合法性、有效性、完整性。

常见的约束有：

	非空约束（not null）
	唯一约束（unique）
	主键约束（primary key）
	外键约束（foreign key）

唯一约束：唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL。（多个NULL不认为重复）

主键约束：不能为NULL，且不能重复

主键作用：作为每一行记录的唯一标识，只要主键不一样，则是不同记录。

一张表的主键约束只能有一个。

主键分类：根据主键字段的字段数量来划分：

单一主键 
复合主键（多个字段联合起来添加一个主键约束）
（复合主键不建议使用，违背三范式中的第二范式）

外键约束

外键用来约束子表的当前字段的值只能是父表中的字段里的值。

学生表的classno添加外键约束，依赖于班级表中的cno：

父表与子表的顺序要求：

设置外键约束语句：

foreign key(classno) references t_class(cno);

19 存储引擎

存储引擎本质上是表的存储方式。

mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。

MYISAM：
使用三个文件表示一张表：


InnoDB：

优点：支持事务，行级锁，外键等

20 事务

一个事务是一个完整的业务逻辑单元，不可再分。

事务原理：

首先开启事务，然后执行事务，事务中的DML语句记录为历史操作，保存在缓存中，当提交事务时，情况历史记录，同步到硬盘文件。回滚则不会同步到硬盘文件。

事务的特性：ACID

A 原子性：事务是最小的工作单元，不可再分
C 一致性：事务必须保证多条DML语句同时成功或者同时失败
I 隔离性： 事务之间具有隔离
D 持久性： 最终数据必须持久化到磁盘文件中，事务才算成功的结束

事务的隔离级别：

MySQL默认隔离级别是可重复读

可重复读：

一个事务A开启后，它运行期间每次读取的结果都是一样的，直到事务结束。即使其他事务如B等已经对要查询的表t_user的数据进行了更新甚至删除，并不会对运行中的A事务产生影响，实际上事务A运行期间读到的数据是表t_user的备份数据。

串行化读：

串行化是 4 种事务隔离级别中隔离效果最好的，解决了脏读、可重复读、幻读的问题，但是效果最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，它就是相当于单线程，后一个事务的执行必须等待前一个事务结束才能执行。

串行化读时，实际测试中，事务A没有结束时，事务B的操作会卡住，直到事务A结束，事务B才会进行，显示结果。

21 索引

1） 什么是索引，有什么用？

抓紧主键和具有unique约束的字段自动会添加索引。

索引就相当于一本书的目录，通过目录可以快速的找到资源。
在数据库方面，查询一张表的时候有两种检索方式：

全表扫描
根据索引检索（效率很高）

索引的根本原理是缩小了扫描的范围。

添加索引是给某一个字段，或者说某些字段添加索引

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引是需要维护成本的。比如，表中的数据经常被修改，就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

2） 创建索引
为工资表的sal字段添加索引

create index emp_sal_index on emp(sal);

3） 什么时候考虑给字段添加索引（满足什么条件）

数据流庞大
较少的DML语句（插入，更新，删除）
该字段经常出现在where子句中

4） 主键和具有unique约束的字段会自动添加索引
因此，根据主键查询效率较高，尽量根据主键检索。

5） 查看sql语句执行计划

explain select ename , sal from emp where sal = 5000;

6） 索引的分类

单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加一个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段上会自动添加索引

7 ）索引的失效（非常重要！！！！！！！！）

select ename from emp where ename like '%A%';

模糊查询，当第一个字母是通配符%，无法根据索引做定位查询，全表扫描。

22 视图（view）

什么是视图：
站在不同的角度去看数据，（同一张表的数据，通过不同的角度去看待）

视图创建：

create view myview as select empno, ename from emp;

23 三范式

根据三范式设计的表，不会产生数据冗余

第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分
第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。
（主要是联合主键的情况，有的字段依赖于主键里的A字段，有的依赖主键里的B字段）（拆分为，学生表，教师表，关系表）
第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。