MySQL数据库从入门到精通

一、SQL语句的分类：

• DML（Data Manipulation Language）数据操纵语言
  如：insert，delete，update，select（插入、删除、修改、检索）简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete

• DDL（Data Definition Language）数据库定义语言
  如：create table之类

• DCL（Data Control Language）数据库控制语言
  如：grant、deny、revoke等，只有管理员才有相应的权限

• DQL（Data Query Language）数据库查询语言
  如: select 语法

二、SQL基本语句

1、数据库操作

1.1创建数据库

创建数据库user，并设置编码格式为UTF-8

create database school default character set utf8;
create database school default charset utf8;

1.2删除数据库

 drop database school ;

1.3查询所有数据库

 show databases;

1.4切换数据库

use school ;

2、数据库表的操作

2.1创建表

语法：create table 表名(字段名 类型(长度));多个字段用逗号分割
创建学生信息表(student)，学生编号(id)、学生姓名(name)、年龄(age)

create table Student(id int primary key auto_increment,
 name varchar(100),
 age int
 );

2.2更新表

语法：alter table 表名 add column 字段名 类型(长度);
在学生信息表(student)里添加 学生密码（pwd）

alter table student add column pwd varchar(20);

2.3删除表

删除学生信息表(student)

drop table student;

2.4查看表结构/设计表

查看学生信息表(student)的表结构

desc student;
DESCRIBE table_name;

3、表记录的操作

3.1查询表信息

查询学生信息表(student)的全部信息

select * from student;

3.2添加数据

语法：insert into 表名 values(值1,值2,值3)
添加多条数据：
insert into 表名 (字段1,字段2,...字段n)
values(值1,值2,...值n),
(值1,值2,...值n),
...
(值1,值2,...值n);
中文编码异常时：set name gbk;
向学生表(student)中添加数据

insert into student values(2019001,'张三',18,135123456);
insert into student values(2019002,'李四',25,135666666);
insert into student values(2019003,'王五',20,135888888);
 insert into user(id,name,age,pwd)
    values(2019004,"小四","123456",22),
    	  (2019005,"小五","123456",22),
   		  (2019006,"小六","123456",22);

3.3修改数据

语法：update 表名 set 字段名=值，字段名=值 where 子句;
将张三的的age修改为20，pwd修改为123456789

update student set age=20,pwd='123456789' where id=2019001;

结果：

3.4删除数据

语法:delete from 表名where 字段名=值;
delete from 表名 where 字段名 in (值1,值2,值3);
删除学生表(student)的信息

delete from student where id=2019003;
delete from student where id in (2019006,2019005,2019004);

三、数据类型

3.1字符

• char定长字符串 长度固定，不足时用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间
• varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2
  一个汉字在UTF-8下占2个字节
  注：不同数据库版本长度限制可能会有不同

3.2 数字

• tinyint,int整数类型
• float,double小数类型
• numeric(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
• decimal和numeric表示精确的整数数字

3.3日期

• date 包含年月日
• time时分秒
• datetime包含年月日和时分秒
• timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

3.4图片、音频、视频

• blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多 份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

四、字段约束

部门信息表dept

员工信息表emp

4.1唯一约束

唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。
unique–唯一约束

create table Student(id int,
 name varchar(100) unique ,
 age int
 );

4.2非空约束

非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。
not null —非空约束

create table Student(id int,
 name varchar(100) unique not null,
 age int
 );

4.3主键约束

主键约束：如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。通常情况下，每张表都会有主键。
primary key—主键约束
主键自增策略当主键为数值类型时，为了方便维护，可以设置主键自增策略（auto_increment），设置了主键自增策略后，数据库会在表中保存一个auto_increment变量值，初始值为1，当需要id值，不需要我们指定值，由数据库负责从auto_increment获取一个id值，作为主键值插入到表中。而且每次用完auto_increment值，都会自增1.
auto_increment—自增

create table Student(id int primary key auto_increment,
 name varchar(100) unique not null,
 age int
 );
 insert into student values(null);

4.4外键约束

语法：foreign key(子表的主键)references 主表名(主表的主键)

-- 约束1：子表的主键值必须取自主表
-- 约束2：主表的记录想要删除，必须保证子表没用过
create table tb_user(
  id int primary key auto_increment ,
  name varchar(10),
  age int  
);
create table tb_user_address(
  user_id int primary key ,
  address varchar(100),
  -- 创建外键：描述和tb_user的关系
  -- 语法：foreign key(当前表的主键)references 对方表名(对方主键)
  foreign key (user_id) references tb_user(id) 
);

4.5默认约束

默认约束：为字段设置默认值。插入数据时仍需要设置具体值
default 默认值—默认约束

drop table x1;
create table x1(
  id int primary key auto_increment,
  sex char(3) default '男'-- 设置默认值
);

4.6检查约束

检查约束：用于限制列中的值的范围。
check–检查约束

create table x2(
  id int primary key auto_increment,
  age int 
  check(age<150)-- 检查age的值是否小于150
);

五、基础函数

upper

upper(字段名/字符串)转大写

select ename,upper('adc'),upper(ename) from emp;

lower

lower(字段名/字符串)转小写

select ename,lower('XYZ'),lower(ename) from emp;

lenght

lenght(字段名/字符串)求长度，一个字母或数字长度为1，一个汉字长度为3

select ename,length(ename),length(job) from emp;

substr

substr(字段名,开始位置,截取长度);截取字符串

select ename,substr(ename,2,2) from emp;

concat

concat(字段名,拼接值1,拼接值2,拼接值.......);拼接

-- 给ename的数据拼接aaa123
select ename,concat(ename,'aaa','123') from emp;

replace

replace(字段名,被替换的值,替换成的值);

-- 将ename字段中a替换成b
select ename,replace(ename,'a','b') from emp;

ifnull

ifnull(字段名,值1)将字段数据为空的值替换成值1

-- ifnull(a,b)如果是null就替换掉，a是字段名  b是要把null替换成b
select comm ,ifnull(comm,'1000000') from emp;

小数

round(字段名)将字段名数据，四舍五入取整数
round(字段名,值1)将字段名数据，四舍五入，保留值1位
ceil(字段名)将字段名数据，向上取整
floor(字段名)将字段名数据，向下取整

-- round(字段名);四舍五入取整数
select comm,round(comm) from emp;
-- round(字段名,保留小数位数);四舍五入,保留X位
select comm,round(comm,1) from emp;
-- ceil(字段名);向上取整
select comm,ceil(comm) from emp;
-- floor(字段名);向下取整
select comm,floor(comm) from emp;

日期

now()获取系统当前时间
year()，month()，day()，hour()，minutr()，second()获取年\月\日\时\分\秒

select now(),year(now()),month(now()),day(now()),
	hour(now()),minute(now()),second(now());-- 获取当前时间的年,月,日,时,分,秒
select year('2001-02-28') 出生年份,month('2001-02-28')出生月份;

条件查询

distinct

distinct去重

select distinct loc from dept;

where

条件1 and 条件2 同时满足两个条件的数据
条件1 or 条件2 满足其中1个条件的数据
字段名 in (值1, 值2) 字段值满足值1 或者值2的数据

-- 语法：select 字段名 from 表名 where 字段名=值;
select * from dept where loc='一区';
select * from dept where loc='二区'and deptno=2;
select dname 二区办公部门 from dept where loc='二区';
select dname 编号小于3的部门 from dept where deptno<3;
-- 查询编号是1或2的部门
select dname from dept where deptno=1 or deptno=2;
select dname from dept where deptno in (1,2);
-- 查询地址在一区或编号是3的部门
select dname from dept where deptno=3 or loc='一区';
-- 修改3号用户的密码
update user set pwd="xyz" where id=3;
-- 修改密码是123，age为20的用户名
update user set name='缘结神' where pwd='123'and age=20;
-- 删除5号用户
delete from user where id=5;

like

like模糊查询
%通配符(0-n个字符)
_通配符(1个字符)

-- 查询名字是以t开头的员工姓名和岗位
select ename,job from emp where ename like 't%';
-- 查询名字包含o的员工姓名和编号
select ename,empno from emp where ename like '%o%';

null

字段名 is null字段值为null
字段名 is not null字段值不为null

-- 查询没有奖金的员工
select * from emp where comm is null;
-- 查询有奖金的员工
select * from emp where comm is not null;

between and

字段名 between 值1 and 值2字段数据在值1 和 值2 的数据，包含值1 值2

-- 查询工资在5000—10000的员工 
select * from emp where sal>=5000 and sal<=10000;
select * from emp where sal between 5000 and 10000;-- [5000,10000]
-- 查询在19年入职的员工
select * from emp where hiredate 
	between '2019-01-01' and '2019-12-31';
select * from emp where year(hiredate)=2019;

limit

分页
limit n展示前 n 条数据
limit n , m展示从第n+1条数据开始的m条数据

-- 展示前两个员工信息
select * from emp limit 2;
-- limit(n,m); n代表n+1条处开始取  m代表展示条数 
select * from emp limit 1,3;

order by

按照字典进行排序
order by 字段名对字段进行升序排序默认升序
order by 字段名 desc对字段进行降序排序desc降序

select * from emp order by sal;
select * from emp order by hiredate desc;
-- 中文排序的规则：拿着汉字去查utf8里面的对应的编码，对编码进行排序
-- 最高薪的员工
select * from emp order by sal desc limit 1;

六、聚合函数

max()–最大值
min()–最小值
sum()–求和
avg()–求平均值
count()–求个数

-- 获取最大值
select max(sal) from emp;
-- 获取最小值
select min(sal) from emp;
-- 求工资的总和
select sum(sal) from emp;
-- 求平均工资
select avg(sal) from emp;
-- 求个数
select count(comm) from emp;#不推荐!!不统计null的,低效
select count(sal) from emp;#不推荐!!
select count(*) from emp;#高效
select count(1) from emp;#高效

-- 统计2019年以前入职的员工人数
select count(1) from emp where year(hiredate)<2019;
-- 统计2019年以前入职的员工的平均工资
select avg(sal) from emp where year(hiredate)<2019;
-- 统计2号部门的最高薪
select max(sal) from emp where deptno=2;
-- 统计岗位是员工的平均工资
select avg(sal) from emp where job='员工';

七、分组

分组使用group by 字段名
当查询中出现了混合列（聚合列和非聚合列）的时候，必须分组
聚合列：使用了聚合函数
非聚合列：没有使用聚合函数

having 条件过滤
where关键字无法与聚合函数一起使用。
having子句可以让我们筛选分组后的各组数据。

-- 统计每个岗位的平均薪资
select job,avg(sal) from emp 
group by job; #按照岗位分组
-- 统计每个岗位的员工人数并排序
select job,count(1) a from emp
group by job#分组
order by a desc;#降序,默认是升序
-- 统计每个部门的最高薪
select depyno,max(sal) from emp group by deptno;
-- 统计每个部门的总人数
select deptno,count(1) from emp group by deptno;
-- 统计每年入职的总人数
select year(hiredate),count(1) from emp group by year(hiredate);
-- 统计每年入职的工资总支出
select year(hiredate) a,sum(sal)*12 from emp group by a;

-- 分组后过滤having
-- 统计每年入职的工资总支出，只要15年以后的
select year(hiredate) a,sum(sal)*12 from emp group by a having a>2015;
-- 统计每个部门的总人数，只要人数>2的
select deptno,count(1) from emp group by deptno having count(1)>2;

八、事务

8.1概述

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
一般来说，事务需要满足4个条件（ACID）：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）
mysql默认自动提交事务

8.1.1 原子性

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

8.1.2 一致性

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

8.1.3 隔离性

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

8.1.4 持久性

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

8.1.5 隔离级别:

1,read uncommitted:读未提交, 安全性最差,但是效率高
2,read committed:读已提交, 安全性较好,但是效率较差,也是Oracle的默认级别
3,repeatable read:可重复读,安全性适中,但是效率一般,也是MySQL的默认级别
4,serializable:串行化,安全性最高,但是效率太差

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能
InnoDB默认是可重复读级别的

• 脏读: 脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
• 不可重复读:是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
• 幻读:第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样，幻读是数据行记录变多了或者少了。

简单点总结下他们的区别：脏读是指读取了未修改完的记录，不可重复读指因为被其它事务修改了记录导致某事务两次读取记录不一致，而幻读是指因为其它事务对表做了增删导致某事务两次读取的表记录数不一致问题。

8.2 事务处理

1、用 begin,rollback, commit来实现

   begin/start transaction开始一个事务
   rollback 事务回滚
   commit 事务确认

2、直接用 set来改变 MySQL 的自动提交模式:

   set AUTOCOMMIT=0 -- 禁止自动提交
   set AUTOCOMMIT=1 -- 开启自动提交

8.3 测试

mysql> use school;
Database testclass ;
mysql> CREATE TABLE student( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from student;
Empty set (0.01 sec)
 
mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into student value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into student value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from student;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into student values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from student;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

九、关联表查询

在一般的业务情况下，我们大致将 Mysql多表连接 分为如下几种：

• 内连接：inner join – 可简写为 join；
• 左外连接：left outer join – 可简写为left join ；
• 右外连接：right outer join – 可简写为 right join；
• 全连接：使用 union 完成；
• 交叉连接：cross join – 也称为 笛卡儿乘积连接，基本不使用；

9.1笛卡尔积

笛卡尔积 Cartesian product

select * from dept,emp;

上面这种查询两个表的方式称为：笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。

9.2连接查询

原则：小表驱动大表

9.2.1 内连接

从左表中取出每一条记录,去右表中与所有的记录进行匹配: 匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留.（取交集）
多表连接之间用join来描述，用on描述两张表的关系，用where表示条件
select * from 表1 join 表2 on 表1.字段名1=表2.字段名1
当两个表中存在相同意义的字段的时候，就可以通过该字段来连接查询这两个表，当该字段的值相同时就可以查出该记录。

-- 查询在一区办公的员工名字
select emp.ename from emp
join dept-- 连接的表
on dept.deptno=emp.deptno-- 描述连接关系
where dept.loc='一区';-- 条件

图例：

9.2.2外连接

以某张表为主,取出里面的所有记录, 然后每条与另外一张表进行连接: 不管能不能匹配上条件,最终都会保留: 能匹配,正确保留; 不能匹配,其他表的字段都置空NULL。

左外连接

left join:左外连接、以左表为主表
基本语法：select * from 左表 join 右表 on 左表.字段名=右表.字段名
左表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留; 若不能匹配,右表的字段都置NULL。

-- 左外连接，取到左表的所有和右表满足条件的 不满足用null填充
select * from dept left join emp
on dept.deptno=emp.deptno

图例：

右外连接

right join:右外连接、以右表为主表
基本语法：select * from 左表 join 右表 on 左表.字段名=右表.字段名
右表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留; 若不能匹配,右表的字段都置NULL。

-- 右外连接，取到右表的所有和左表满足条件的 不满足用null填充
select * from emp right join dept
on dept.deptno=emp.deptno

图例：

9.2.3 全连接

全连接 – union：

事实上由于 Mysql 不支持full join ，所以我们将使用union 来完成 全连接；

select * from dept left join emp
on dept.deptno=emp.deptno
union
select * from emp right join dept
on dept.deptno=emp.deptno;

解释：
全连接 是 左右外连接的并集，连接表包含被连接的表的所有记录，如果缺少匹配的记录,，则以 null 取代；
图例：

9.3 子查询 subquery

9.3.1概念

子查询是指嵌入在其他select语句中的select语句，也叫嵌套查询。子查询执行效率低慎用。记录少时效率影响不大、图方便直接使用，记录多时最好使用其它方式替代。

9.3.2单行子查询 =

-- 查询accounting部门的员工信息
-- 第一次查部门编号
select deptno from dept where dname='accounting';
-- 第二次根据部门编号查询员工信息
select * from emp where deptno=1;
-- 嵌套
select * from emp where deptno=(select deptno from dept where dname='accounting');

9.3.3多行子查询 in

-- 查询在二区办公的员工姓名
select ename from emp where deptno=(select deptno from dept where  loc='二区' limit 1) 
or deptno=(select deptno from dept where  loc='二区' limit 1,1);

select ename from emp where deptno in (select deptno from dept where  loc='二区');

9.4 SQL练习

查询所有记录
select * from emp
 
只查询指定列
SELECT id,ename,sal from emp
 
查询id为100的记录
select * from emp where id=100
 
模糊查询记录
select * from emp where ename like 'j%' #以j开头的记录
 
select * from emp where ename like '%k' #以k结束的记录
 
select * from emp where ename like '%a%' #包含a的记录
 
select * from emp where ename not like 'j%' #不 以j开头的记录
 
查询之间范围之间的所有记录
select * from emp where sal between 8000 and 20000 #[8000,20000]
 
select * from emp where sal>8000 and sal<20000 #(8000,20000)
 
查询满足两个条件的记录
SELECT * from user where age=19 or age=20 #或者关系
 
SELECT * from user where age in (19,20)
 
SELECT * from user where age=20 and name='xiongda' #并且关系
 
查询用户住址
SELECT distinct addr from user
 
查询19岁人的名字
SELECT distinct name from user where age=19
 
按age升序查询记录
SELECT * from user order by age asc #升序,默认
 
SELECT * from user order by age desc #降序
 
以name升序、age降序查询记录
SELECT * from user order by name asc,age desc #name升序,age降序
 
查询总人数
SELECT count(*) from user
 
SELECT count(1) from user
 
SELECT count(id) from user
 
查询各个城市的人数
select addr,count(addr) from user group by addr #聚合函数以外的列要分组
 
查询至少有2人的地址
SELECT addr,count(name) from user GROUP BY addr
 
SELECT addr,count(name) X from user GROUP BY addr having X>2 #条件过滤
 
查询记录中最年长和最年轻
select max(age),min(age) from user
 
查询大于平均年龄的记录
select * from user where age > (select avg(age) from user)
 
查询年龄最大的用户信息
select * from user where age = (select max(age) from user)
 
查询各部门的最高薪
select id,name,sal,max(sal) from emp GROUP BY deptno
 
查询各科的平均工资
select avg(comm) from emp
 
select ROUND(avg(comm),1) from emp #保留一位小数
 
SELECT * from emp where comm > (select avg(comm) from emp)
 
查询id是100或200的记录
select * from emp where id=100
 
select * from emp where id=200
 
select * from emp where id=100 or id=200
 
select * from emp where id in(100,200)
 
select * from emp where id=200
 
#UNION #合并重复内容
 
union all #不合并重复内容
 
select * from emp where id=200
 
查询存在部门的员工信息
select * from emp where deptno in (select id from dept)
 
查询没划分部门的员工信息
select * from emp where deptno not in(select id from dept)
 
查询同名的员工记录
select * from emp WHERE ename in (
 
select ename from emp GROUP BY ename HAVING count(ename)>1
 
)
 
全部学生按出生年月排行
select * from students order by sbirthday #数值从小到大，年龄就是大到小了
 
每个班上最小年龄的学员
select sname,class,max(sbirthday) from students group by class #数字最大，年龄是最小的
 
查询学生的姓名和年龄
select sname,year(now())-year(sbirthday) age from students
 
查询男教师及其所上的课程
SELECT * from teachers a inner JOIN courses b on a.tno=b.tno AND a.tsex='男'
 
SELECT * from teachers a,courses b where a.tno=b.tno AND a.tsex='男'
 
查询每个老师教的课程
SELECT c.cname,t.tname,t.prof,t.depart
 
FROM teachers t
 
LEFT JOIN courses c ON t.tno = c.tno
 
查询女老师的信息
SELECT *
 
FROM teachers t
 
LEFT JOIN courses c ON t.tno = c.tno
 
where t.tsex='女'
第一种先连接数据后过滤数据，假如数据量很大，第一种中间过程要构建巨大的临时表。而第二种方式先过滤数据，构建的中间结果集自然就变的很小。所占内存，所加工的时间所网络传输的时间都变少了，所以效率高。
 
查询得分前3名的学员信息
select * from scores order by degree desc limit 3 #前三条
 
select * from scores order by degree desc limit 1,3
 
#从1位置(第二条)开始,总共取3条
 
查询课程是“计算机导论”的，得分前3名的学员信息
select * from scores where cno = (select cno from courses where cname='计算机导论')
 
order by degree desc limit 3
 
课程号“3-105”的倒数最后3名学员排行
select * from scores where cno='3-105' order by degree limit 3

9.5 SQL执行顺序

(1) FROM [left_table] 选择表
 
(2) ON <join_condition> 链接条件
 
(3) <join_type> JOIN <right_table> 链接
 
(4) WHERE <where_condition> 条件过滤
 
(5) GROUP BY <group_by_list> 分组
 
(6) AGG_FUNC(column or expression),... 聚合
 
(7) HAVING <having_condition> 分组过滤
 
(8) SELECT (9) DISTINCT column,... 选择字段、去重
 
(9) ORDER BY <order_by_list> 排序
 
(10) LIMIT count OFFSET count; 分页

十、索引

10.1 概述

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。

10.2 分类

1. 单值索引：一个索引只包括一个列，一个表可以有多个列
2. 唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
3. 复合索引：一个索引同时包括多列

10.3 语法

查看索引
show index from 表名;
创建索引
create index 索引名 on 表名(字段名);
查看SQL的执行性能/计划
explain
创建唯一索引
create unique index 索引名 on 表名(字段名);
创建复合索引
create index 索引名 on 表名(字段名1,字段名2);

#查询索引，表里的主键已经默认创建了索引
show index from dept;
show index from emp;

#创建索引
#不是所有字段都适合加索引，可以给经常查的字段加索引
#语法：create index 索引名 on 表名(字段名)
create index index_ename on emp(ename);
create index index_dname on dept(dname);
show index from emp;
show index from dept;

#使用索引
select * from dept where dname='research';

#查看SQL的执行性能/计划,观察key的值
explain
select * from dept where dname='research';
explain 
select * from emp where  ename='jack';

#创建唯一约束-----优先使用的是唯一索引
#语法：create unique index 索引名 on 表名(字段名)
create unique index index_name on dept(dname);
show index from dept;

#使用
explain
select * from dept where dname='research';

#创建复合索引
#语法：create  index 索引名 on 表名(字段名1,字段名2)
create index index_fuhe on emp(job,deptno);
show index from emp;

#删除索引
-- 语法：alter table 表名 drop index 索引名
alter table emp drop index index_fuhe;

10.4 索引扫描类型

type：

1. ALL 全表扫描，没有优化，最慢的方式
2. index 索引全扫描，其次慢的方式
3. range 索引范围扫描，常用语<，<=，>=，between等操作
4. ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
5. eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
6. const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况
7. null MySQL不访问任何表或索引，直接返回结果

10.5 最左特性

当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)、(k1,k3)和(k1,k2,k3)索引，这就是最左匹配原则，也称为最左特性。

-- 使用-----：最左特性（必须包含着最左变的元素job）
explain select * from emp where job='员工' and deptno =2;
explain select * from emp where job='员工';
explain select * from emp where deptno =2;-- 复合索引失效
explain select * from emp where deptno =2 and job='员工';
explain select * from emp where job='员工' or deptno =2;-- 复合索引失效

10.6 小结

优点:

1. 索引是数据库优化
2. 表的主键会默认自动创建索引
3. 每个字段都可以被索引
4. 大量降低数据库的IO磁盘读写成本，极大提高了检索速度
5. 索引事先对数据进行了排序，大大提高了查询效率

缺点:

1. 索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
2. 索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
3. 虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
4. 随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

十一、视图

11.1 概念

可视化的表，视图当做是一个特殊的表，是指，把sql执行的结果，直接缓存到了视图中。
下次还要发起相同的sql，直接查视图。现在用的少,了解即可.
使用： 1，创建视图 2，使用视图

11.2 语法

创建视图
create view 视图名 as SQL语句
优点
视图：优点是避免了次次写复杂的SQL语句，屏蔽了业务表的复杂性，被所有用户共享

#1、创建视图
-- 语法：create view 视图名 as SQL语句
-- 查询名字里包含a的员工信息
create view ename_view as
select * from emp where ename like '%a%';
-- 查询视图
select * from ename_view;

十二、SQL优化

1.创建mysql-db库

CREATE DATABASE /*!32312 IF NOT EXISTS*/mysql-db /*!40100 DEFAULT CHARACTER SET utf8 */;     
USE mysql-db ;

2.准备student表

DROP TABLE IF EXISTS `student`;
     
CREATE TABLE `student` (  
id varchar(4) NOT NULL,   
NAME varchar(20) DEFAULT NULL,
sex char(2) DEFAULT NULL,
birthday date DEFAULT NULL,
salary decimal(7,2) DEFAULT NULL,
PRIMARY KEY (id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     
insert into student(id,NAME,sex,birthday,salary) 
values ('1','张慎政','男','2020-01-01','10000.00'),
('2','刘沛霞','女','2020-01-02','10000.00'),
('3','刘昱江','男','2020-01-03','10000.00'),
('4','齐雷','男','2020-01-04','20000.00'),
('5','王海涛','男','2020-01-05','20000.00'),
('6','董长春','男','2020-01-06','10000.00'),
('7','张久军','男','2020-01-07','20000.00'),
('8','陈子枢','男','2020-10-11','3000.00');

3.准备tb_dept表

DROP TABLE IF EXISTS `tb_dept`;
 
CREATE TABLE tb_dept (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
parent_id int(11) DEFAULT NULL,
sort int(11) DEFAULT NULL,
note varchar(100) DEFAULT NULL,
created timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
updated timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (id) 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
     
insert into tb_dept(id,name,parent_id,sort,note,created,updated) 
values (1,'集团',0,1,'集团总部','2018-10-02 09:15:14','2018-09-27 16:35:54'),
(2,'财务部',1,2,'财务管理','2018-09-27 16:35:52','2018-09-27 16:34:15'),
(3,'软件部',1,3,'开发软件、运维','2018-09-27 16:35:54','2018-09-27 16:34:51');

4.准备tb_user表

DROP TABLE IF EXISTS tb_user;

CREATE TABLE tb_user (
id  int(11) NOT NULL AUTO_INCREMENT,
dept_id  int(11) DEFAULT NULL,
username  varchar(50) DEFAULT NULL,
password  varchar(100) DEFAULT NULL,
salt  varchar(50) DEFAULT NULL,
email  varchar(100) DEFAULT NULL,
mobile  varchar(100) DEFAULT NULL,
valid  tinyint(4) DEFAULT NULL,
created  timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
updated  timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY ( id )
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
insert into  tb_user ( id , dept_id , username , password , salt , email , mobile , valid , created , updated ) 
values (1,1,'陈集团','123456',NULL,'tony@sina.com','13572801415',1,'2018-09-30 09:32:18','2018-09-30 09:32:18'),
(2,3,'牛软件','567890',NULL,'niu@sina.com','13208737172',0,'2018-10-02 09:23:19','2018-09-20 09:32:18');

5.查询SQL尽量不要使用select *，而是具体字段

反例：

SELECT * FROM student

正例：

SELECT id,NAME FROM student

理由：
字段多时，大表能达到100多个字段甚至达200多个字段
只取需要的字段，节省资源、减少网络开销
select * 进行查询时，很可能不会用到索引，就会造成全表扫描

5.避免在where子句中使用or来连接条件

反例：SELECT * FROM student WHERE id=1 OR salary=30000
正例：
# 分开两条sql写
SELECT * FROM student WHERE id=1
SELECT * FROM student WHERE salary=30000

理由：

使用or可能会使索引失效，从而全表扫描
对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的

6.使用varchar代替char

反例：deptname char(100) DEFAULT NULL COMMENT '部门名称'
正例：deptname varchar(100) DEFAULT NULL COMMENT '部门名称'

理由：
varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间
char按声明大小存储，不足补空格
其次对于查询来说，在一个相对较小的字段内搜索，效率更高

7.尽量使用数值替代字符串类型

主键（id）：primary key优先使用数值类型int，tinyint
性别（sex）：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint
支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
服务状态（state）：1-开启、2-暂停、3-停止
商品状态（state）：1-上架、2-下架、3-删除

8.查询尽量避免返回大量数据

如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。
通常采用分页，一页习惯10/20/50/100条。

9.使用explain分析你SQL执行计划

SQL很灵活，一个需求可以很多实现，那哪个最优呢？SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。

EXPLAIN
SELECT * FROM student WHERE id=1

10.是否使用了索引及其扫描类型

type：
ALL 全表扫描，没有优化，最慢的方式
index 索引全扫描
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
null MySQL不访问任何表或索引，直接返回结果
key：

1. 真正使用的索引方式

11.创建name字段的索引

ALTER TABLE student ADD INDEX index_name (NAME)

12.优化like语句

模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效
反例：

EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE '%1'
EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE '%1%'

正例：

 EXPLAIN
 SELECT id,NAME FROM student WHERE NAME LIKE '1%'

13.字符串怪现象

反例：

#未使用索引 
EXPLAIN
SELECT * FROM student WHERE NAME=123

正例：

#使用索引
EXPLAIN
SELECT * FROM student WHERE NAME='123'

理由：
为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较

14.索引不宜太多，一般5个以内

1. 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率
2. 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间
3. 再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯定要
4. insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定
5. 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要

15.索引不适合建在有大量重复数据的字段上

如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

16.where限定查询的数据

数据中假定就一个男的记录
反例：

SELECT id,NAME FROM student WHERE sex='男'

正例：

SELECT id,NAME FROM student WHERE id=1 AND sex='男'

理由：
需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销

17.避免在where中对字段进行表达式操作

反例：

EXPLAIN
SELECT * FROM student WHERE id+1-1=+1

正例：

EXPLAIN
SELECT * FROM student WHERE id=+1-1+1
EXPLAIN
SELECT * FROM student WHERE id=1

理由：
SQL解析时，如果字段相关的是表达式就进行全表扫描

18.避免在where子句中使用!=或<>操作符

应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。
反例：

EXPLAIN
SELECT * FROM student WHERE salary!=3000
EXPLAIN
SELECT * FROM student WHERE salary<>3000

理由：
使用!=和<>很可能会让索引失效

19.去重distinct过滤字段要少

#索引失效

EXPLAIN
SELECT DISTINCT * FROM student

#索引生效

EXPLAIN
SELECT DISTINCT id,NAME FROM student
EXPLAIN
SELECT DISTINCT NAME FROM student

理由：
带distinct的语句占用cpu时间高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，如cpu时间

20.where中使用默认值代替null

#修改表，增加age字段，类型int，非空，默认值0

ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;

21.批量插入性能提升

大量数据提交，上千，上万，批量性能非常快，mysql独有
多条提交：

INSERT INTO student (id,NAME) VALUES(4,'齐雷');
INSERT INTO student (id,NAME) VALUES(5,'刘昱江');

批量提交：

INSERT INTO student (id,NAME) VALUES(4,'齐雷'),(5,'刘昱江');

理由：
默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交。自然速度飞升
数据量小体现不出来

22.批量删除优化

避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。
反例：

#一次删除10万或者100万+？
delete from student where id <100000;
#采用单一循环操作，效率低，时间漫长
    for（User user:list）{
     
    delete from student;
     
    }

正例：

//分批进行删除，如每次500
    for(){
     
    delete student where id<500;
     
    }
     
    delete student where id>=500 and id<1000;

理由：
一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作

23.伪删除设计

商品状态（state）：1-上架、2-下架、3-删除
理由：
这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查
同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。
通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用
操作速度快，特别数据量很大情况下

24.提高group by语句的效率

可以在执行到该语句前，把不需要的记录过滤掉
反例：先分组，再过滤

select job，avg（salary） from employee
group by job
having job ='president' or job = 'managent';

正例：先过滤，后分组

select job，avg（salary） from employee
where job ='president' or job = 'managent'
group by job;

25.复合索引最左特性

创建复合索引，也就是多个字段

ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)

满足复合索引的左侧顺序，哪怕只是部分，复合索引生效

 EXPLAIN
 SELECT * FROM student WHERE NAME='陈子枢'

没有出现左边的字段，则不满足最左特性，索引失效

EXPLAIN
SELECT * FROM student WHERE salary=3000

复合索引全使用，按左侧顺序出现 name,salary，索引生效

EXPLAIN
SELECT * FROM student WHERE NAME='陈子枢' AND salary=3000

虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化

EXPLAIN
SELECT * FROM student WHERE salary=3000 AND NAME='陈子枢'

理由：
复合索引也称为联合索引
当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则
联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的

26.排序字段创建索引

什么样的字段才需要创建索引呢？原则就是where和order by中常出现的字段就创建索引。

#使用 *，包含了未索引的字段，导致索引失效
EXPLAIN 
SELECT * FROM student ORDER BY NAME;
#name字段有索引
    EXPLAIN
     
    SELECT id,NAME FROM student ORDER BY NAME
#排序字段未创建索引，性能就慢
    EXPLAIN
     
    SELECT id,NAME FROM student ORDER BY sex

27.删除冗余和重复的索引

SHOW INDEX FROM student
#创建索引index_name
ALTER TABLE student ADD INDEX index_name (NAME)
#删除student表的index_name索引
DROP INDEX index_name ON student ;
#修改表结果，删除student表的index_name索引
ALTER TABLE student DROP INDEX index_name ;
#主键会自动创建索引，删除主键索引
ALTER TABLE student DROP PRIMARY KEY ;

28.不要有超过5个以上的表连接

1. 关联的表个数越多，编译的时间和开销也就越大
2. 每次关联内存中都生成一个临时表
3. 应该把连接表拆开成较小的几个执行，可读性更高
4. 如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
5. 阿里规范中，建议多表联查三张表以下

29.inner join 、left join、right join，优先使用inner join

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小

• inner join 内连接，只保留两张表中完全匹配的结果集
• left join会返回左表所有的行，即使在右表中没有匹配的记录
• right join会返回右表所有的行，即使在左表中没有匹配的记录
  理由：
  如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点
  同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。
  这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优

30.in子查询的优化

日常开发实现业务需求可以有两种方式实现：
一种使用数据库SQL脚本实现
一种使用程序实现
如需求：查询所有部门的所有员工：

#in子查询
SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);
#这样写等价于：
#先查询部门表
SELECT id FROM tb_dept
#再由部门dept_id，查询tb_user的员工
SELECT * FROM tb_user u,tb_dept d WHERE u.dept_id = d.id
假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下程序实现，可以抽象成这样的一个嵌套循环：
    List<> resultSet;
     
    for(int i=0;i<B.length;i++) {
    for(int j=0;j<A.length;j++)  
    if(A[i].id==B[j].id) {
     
    resultSet.add(A[i]);
     
    break;
		   } 
    }
 }

上面的需求使用SQL就远不如程序实现，特别当数据量巨大时。
理由：
数据库最费劲的就是程序链接的释放。假设链接了两次，每次做上百万次的数据集查询，查完就结束，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，就会额外花费很多实际，这样系统就受不了了，慢，卡顿

十三、数据库的三范式

13.1 概述

简言之就是，数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式,也是作为数据库 设计的一些规则.
关系型数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。范式越高，冗余最低，一般到三范式，再往上，表越多，可能导致查询效率下降。所以有时为了提高运行效率，可以让数据冗余.
1NF的定义为：符合1NF的关系中的每个属性都不可再分

2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖,也就是说,表里的每个字段都要依赖于主键
第一步：找出数据表中所有的码。
第二步：根据第一步所得到的码，找出所有的主属性。
第三步：数据表中，除去所有的主属性，剩下的就都是非主属性了。
第四步：查看是否存在非主属性对码的部分函数依赖

3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖
就是指没个属性都跟主键有直接关系而不是间接关系。
像：a–>b–>c 属性之间含有这样的关系，是不符合第三范式的。
比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）
这样一个表结构，就存在上述关系。 学号–> 所在院校 --> (院校地址，院校电话)
这样的表结构，我们应该拆开来，如下。
（学号，姓名，年龄，性别，所在院校）–（所在院校，院校地址，院校电话）

13.2总结

三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。