数据库从入门到精通03

太过爱你忘了你带给我的痛 2021-07-16 23:35 504阅读 0赞

##  ##

**目录**

事务 transaction

什么是事务

事务4个特性ACID

隔离级别

查询mysql的隔离级别

事务处理

提交 commit

回滚 rollback

表强化：6约束 constraints

非空约束 not null

唯一约束 unique

主键约束 primary key

外键约束 foreign key

默认约束 default

检查约束 check

索引 index

定义

分类

创建索引

索引扫描类型

最左特性

为何索引快？

小结

表关联 association

概念

考试系统mysql版

表设计

创建数据库

创建表

插入测试数据

多表联查 join

笛卡尔积 Cartesian product

三种连接 join

案例：列出research部门下的所有员工的信息

案例：怎么用内链接 INNER JOIN 实现上面的需求？

案例：列出tony的扩展信息

inner join、left join、right join的区别？

SQL面试题

SQL的执行顺序

SQL优化

创建mysql-db库

准备student表

准备tb\_dept表

准备tb\_user表

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

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

使用varchar代替char

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

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

使用explain分析你SQL执行计划

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

创建name字段的索引

优化like语句

字符串怪现象

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

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

where限定查询的数据

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

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

去重distinct过滤字段要少

where中使用默认值代替null

批量插入性能提升

批量删除优化

伪删除设计

提高group by语句的效率

复合索引最左特性

排序字段创建索引

删除冗余和重复的索引

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

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

in子查询的优化

尽量使用union all替代union

--------------------

## 事务 transaction ##

### 什么是事务 ###

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

下面以银行转账为例，A转100块到B的账户，这至少需要两条SQL语句：

*  给A的账户减去100元；

update 账户表 set money=money\*\*-100\*\* where name='A';

*  给B的账户加上100元。

update 账户表 set money=money\*\*+100\*\* where name='B';

如果在第一条SQL语句执行成功后，在执行第二条SQL语句之前，程序被中断了（可能是抛出了某个异常，也可能是其他什么原因），那么B的账户没有加上100元，而A却减去了100元，在现实生活中这肯定是不允许的。

如果在转账过程中加入事务，则整个转账过程中执行的所有SQL语句会在一个事务中，而事务中的所有操作，要么全都成功，要么全都失败，不可能存在成功一半的情况。

也就是说给A的账户减去100元如果成功了，那么给B的账户加上100元的操作也必须是成功的；否则，给A减去100元以及给B加上100元都是失败的。

### 事务4个特性ACID ###

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

*  原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
 *  一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
 *  隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
 *  持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

### 隔离级别 ###

事务隔离分为不同级别，包括

*  读未提交（Read uncommitted） 安全性最差,可能发生并发数据问题,性能最好
 *  读提交（read committed） Oracle默认的隔离级别
 *  可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
 *  串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高,不能并发

### 查询mysql的隔离级别 ###

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。

*  开启事务：start transaction；
 *  结束事务：commit（提交事务）或rollback（回滚事务）。

在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，**commit**表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者**rollback**，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！

SELECT @@tx\_isolation;

Repeatable Read（可重读）

MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

### 事务处理 ###

*  在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
 *  事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
 *  事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的
 *  MySQL默认数据库的事务是开启的，执行SQL后自动提交。
 *  MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交。

### 提交 commit ###

\#多条语句时，批量执行，事务提交

\#有了事务，多步操作就形成了原子性操作，高并发下也不会引起数据错乱

\#mysql的事务默认就是开启的 -- 多条语句一起操作时,要么一起成功要么一起失败

BEGIN; \#关闭事务的自动提交

INSERT INTO user (id) VALUES(25);\#成功

INSERT INTO user (id) VALUES(5);\#已经存在5了，会失败

COMMIT; \#手动提交事务

### 回滚 rollback ###

\#多条语句，批量执行，insert插入重复的主键导致失败时，事务回滚

BEGIN;

INSERT INTO user (id) VALUES(15);

INSERT INTO user (id) VALUES(35);\#存在了

ROLLBACK;\#事务回滚，就不会再提交了

## 表强化：6约束 constraints ##

### 非空约束 not null ###

DROP TABLE IF EXISTS tb\_user; \#如果表存在则删除，慎用会丢失数据

CREATE TABLE tb\_user(

id INT AUTO\_INCREMENT,

NAME VARCHAR(30) UNIQUE NOT NULL,

age INT,

phone VARCHAR(20) UNIQUE NOT NULL,

email VARCHAR(30) UNIQUE NOT NULL,

PRIMARY KEY (id)

);

DESC tb\_user;

\#id为自增主键，null值无效，数据库会自动用下一个id值替代

\#age因为运行为null，所以可以设置为null

INSERT INTO tb\_user (id,age) VALUES(NULL,NULL);

### 唯一约束 unique ###

Name字段创建了唯一约束，插入数据时数据库会进行检查，如果插入的值相同，就会检查报错：

DROP TABLE IF EXISTS tb\_user; \#如果表存在则删除，慎用会丢失数据

CREATE TABLE tb\_user(

id INT,

NAME VARCHAR(30) UNIQUE NOT NULL,

phone VARCHAR(20) UNIQUE NOT NULL,

email VARCHAR(30) UNIQUE NOT NULL,

PRIMARY KEY (id)

);

DESC tb\_user;

INSERT INTO tb\_user (id,NAME) VALUES(1,'tony');

INSERT INTO tb\_user (id,NAME) VALUES(2,'tony');

执行上面语句出错：

Query : INSERT INTO tb\_user (id,NAME) VALUES(2,'tony')

Error Code : 1062

Duplicate entry 'tony' for key 'name'

展示表结构：

DESC tb\_user;

### 主键约束 primary key ###

主键是一条记录的唯一标识，具有唯一性，不能重复

DROP TABLE IF EXISTS tb\_user; \#如果表存在则删除，慎用会丢失数据

CREATE TABLE tb\_user(

id INT,

NAME VARCHAR(30),

PRIMARY KEY (id)

);

INSERT INTO tb\_user (id,NAME) VALUES(1,'tony');

INSERT INTO tb\_user (id,NAME) VALUES(1,'hellen');

第二句插入就会报错：

Query : INSERT INTO tb\_user (id,NAME) VALUES(1,'hellen')

Error Code : 1062

Duplicate entry '1' for key 'PRIMARY'

提示主键1的值已经存在，重复了

### 外键约束 foreign key ###

DROP TABLE IF EXISTS tb\_user\_address; \#如果表存在则删除，慎用会丢失数据

DROP TABLE IF EXISTS tb\_user; \#如果表存在则删除，慎用会丢失数据

CREATE TABLE tb\_user (

id INT PRIMARY KEY NOT NULL AUTO\_INCREMENT, \#自增主键

NAME VARCHAR(50) NOT NULL UNIQUE, \#非空，唯一索引

sex CHAR(2) DEFAULT '男', \#默认值

phone CHAR(18),

age INT,

CHECK (age>0 AND age<=200),

);

CREATE TABLE tb\_user\_address (

user\_id INT PRIMARY KEY NOT NULL,

address VARCHAR(200),

foreign key(user\_id) REFERENCES tb\_user(id)

);

DESC tb\_user;

tb\_user\_address中user\_id字段录入tb\_user表不存在的主键值，将报错

### 默认约束 default ###

默认值

DROP TABLE IF EXISTS tb\_user; \#如果表存在则删除，慎用会丢失数据

CREATE TABLE tb\_user (

id INT PRIMARY KEY NOT NULL AUTO\_INCREMENT, \#自增主键

NAME VARCHAR(50) NOT NULL UNIQUE, \#非空，唯一索引

sex CHAR(2) DEFAULT '男', \#默认值

phone CHAR(18),

age INT,

createdTime DATE DEFAULT NOW()

);

DESC tb\_user;

### 检查约束 check ###

很少使用，了解即可，录入age超过200将报错

DROP TABLE IF EXISTS tb\_user; \#如果表存在则删除，慎用会丢失数据

CREATE TABLE tb\_user (

id INT PRIMARY KEY NOT NULL AUTO\_INCREMENT, \#自增主键

NAME VARCHAR(50) NOT NULL UNIQUE, \#非空，唯一索引

sex CHAR(2) DEFAULT '男', \#默认值

phone CHAR(18),

age INT,

CHECK (age>0 AND age<=200),

createdTime DATE DEFAULT NOW()

);

DESC tb\_user;

## 索引 index ##

### 定义 ###

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

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

### 分类 ###

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

### 创建索引 ###

\#查看索引，主键会自动创建索引

show index from dept;

\#创建索引

#create index 索引名字 on 表名(字段名); #创建索引
    
    create index loc_index on dept(loc); #创建索引

\#修改表结构,添加普通索引

alter table dept add index loc_index(loc)

\# 创建唯一索引

alter table dept add unique(loc) #创建唯一索引--索引列的值必须唯一

\# 创建复合索引

alter table dept add index fuhe_index(dname,loc);

\# 创建复合唯一索引

alter table dept add unique fuhe_index(dname,loc)

\# 删除索引

alter table dept drop index fuhe_index

### 索引扫描类型 ###

type：

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

### 最左特性 ###

explain
    
    select * from dept where loc='二区' #使用了loc索引
    
    explain
    
    select * from dept where dname='研发部'#使用了dname索引
    
    explain
    
    select * from dept where dname='研发部' and loc='二区' #使用了dname索引

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

### 为何索引快？ ###

明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。

其过程如下图，先到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。

*  排序，tree结构，类似二分查找
 *  索引表小

### 小结 ###

**优点:**

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

**缺点:**

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

##  表关联 association ##

### 概念 ###

表table代表了生活中一个主体，如部门表dept，员工表emp。表关联则代表了表之间的关系，如：部门和员工，商品和商品分类，老师和学生，教室和学生。

同时，也要知道，表并不都有关系，它们形成自己的小圈子。如商品和商品详情一圈，部门和员工一圈，出圈就可能没关系了，如商品和员工无关，商品和学生无关。

下面我们讨论表的关系分为四种：

*  一对一 one to one QQ和QQ邮箱，员工和员工编号
 *  一对多 one to many 最常见，部门和员工，用户和订单
 *  多对一 many to one 一对多反过来，员工和部门，订单和用户
 *  多对多 many to many 老师和学生，老师和课程

## 考试系统mysql版 ##

### 表设计 ###

*  外键：由子表出发向主表拖动鼠标，到达主表后松手，PD会自动添加外键字段
 *  讲师表和课程表：一对多，两张表。关联关系体现：子表存储主表的主键，称外键
 *  课程表和学生表：多对多，三张表。关联关系体现：子表无法存储主表的多条关联信息，只能再创建一张表来存储其信息
 *  中间表：存储两张表各自的主键，某一张表的主键无法标识记录的唯一性，两个一起才可以标识唯一，这种主键为多个字段的称为复合主键

### 创建数据库 ###

### 创建表 ###

表设计特点：

*  表都以s结束，标识复数
 *  字段多以表的首字母作为开头，在多表联查时，方便标识出是哪个表的字段

/*==============================================================*/
    
    /* DBMS name: MySQL 5.0 */
    
    /* Created on: 2020 */
    
    /*==============================================================*/
    
    drop table if exists courses;
    
    drop table if exists scores;
    
    drop table if exists students;
    
    drop table if exists teachers;
    
    /*==============================================================*/
    
    /* Table: courses */
    
    /*==============================================================*/
    
    create table courses
    
    (
    
    cno varchar(5) not null,
    
    cname varchar(10) not null,
    
    tno varchar(3) not null,
    
    primary key (cno)
    
    );
    
    /*==============================================================*/
    
    /* Table: scores */
    
    /*==============================================================*/
    
    create table scores
    
    (
    
    sno varchar(3) not null,
    
    cno varchar(5) not null,
    
    degree numeric(10,1) not null,
    
    primary key (sno, cno)
    
    );
    
    /*==============================================================*/
    
    /* Table: students */
    
    /*==============================================================*/
    
    create table students
    
    (
    
    sno varchar(3) not null,
    
    sname varchar(4) not null,
    
    ssex varchar(2) not null,
    
    sbirthday datetime,
    
    class varchar(5),
    
    primary key (sno)
    
    );
    
    /*==============================================================*/
    
    /* Table: teachers */
    
    /*==============================================================*/
    
    create table teachers
    
    (
    
    tno varchar(3) not null,
    
    tname varchar(4),
    
    tsex varchar(2),
    
    tbirthday datetime,
    
    prof varchar(6),
    
    depart varchar(10),
    
    primary key (tno)
    
    );
    
    alter table courses add constraint FK_Reference_3 foreign key (tno)
    
    references teachers (tno) on delete restrict on update restrict;
    
    alter table scores add constraint FK_Reference_1 foreign key (sno)
    
    references students (sno) on delete restrict on update restrict;
    
    alter table scores add constraint FK_Reference_2 foreign key (cno)
    
    references courses (cno) on delete restrict on update restrict;

### 插入测试数据 ###

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
    
    INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
    
    INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
    
    INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
    
    INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
    
    INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);
    
    INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'易天','男','1958-12-02','副教授','计算机系');
    
    INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'王旭','男','1969-03-12','讲师','电子工程系');
    
    INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'李萍','女','1972-05-05','助教','计算机系');
    
    INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'陈冰','女','1977-08-14','助教','电子工程系');
    
    INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
    
    INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
    
    INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'模拟电路' ,856);
    
    INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'概率论' ,831);
    
    INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,831);
    
    INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
    
    INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
    
    INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
    
    INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
    
    INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
    
    INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
    
    INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
    
    INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
    
    INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
    
    INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
    
    INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
    
    INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

## 多表联查 join ##

### 笛卡尔积 Cartesian product ###

多表查询是指基于两个和两个以上的表的查询。在实际应用中，查询单个表可能不能满足你的需求，如显示员工表emp中不只显示deptno，还要显示部门名称，而部门名称dname在dept表中。

\#把两个表的数据都拼接起来

SELECT * FROM dept,emp

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

这点很值得注意，实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，现在内存中构建一个大大的结果集，然后再进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。

这就是阿里规范中禁止3张表以上的联查的原因：

### 三种连接 join ###

*  内连接 inner join
 *  左（外）连接 left join
 *  右（外）连接 right join

### 案例：列出research部门下的所有员工的信息 ###

SELECT * FROM emp
    
    WHERE deptno = ( SELECT deptno FROM dept WHERE dname='research' )

### 案例：怎么用内链接 INNER JOIN 实现上面的需求？ ###

SELECT d.dname,e.ename,e.job
    
    FROM emp e INNER JOIN dept d
    
    ON e.deptno=d.deptno
    
    WHERE d.dname='research'
    
    换成left join和right join，看看有什么不同呢？

### 案例：列出tony的扩展信息 ###

SELECT *
    
    FROM emp e INNER JOIN empext t
    
    ON e.empno=t.empno
    
    WHERE e.ename='tony'
    
    换成left join和right join，看看有什么不同呢？

### inner join、left join、right join的区别？ ###

![2021070213285975.png][]

*  INNER JOIN两边都对应有记录的才展示，其他去掉
 *  LEFT JOIN左边表中的数据都出现，右边没有数据以NULL填充
 *  RIGHT JOIN右边表中的数据都出现，左边没有数据以NULL填充

## 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

## 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; 分页

## SQL优化 ##

### 创建mysql-db库 ###

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

### 准备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');

### 准备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');

### 准备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');

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

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

### 避免在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条件，索引还是可能失效的

### 使用varchar代替char ###

反例：`deptname` char(100) DEFAULT NULL COMMENT '部门名称'
    
    正例：`deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'
    
    理由：
    
    varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间
    char按声明大小存储，不足补空格
    其次对于查询来说，在一个相对较小的字段内搜索，效率更高

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

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

### 查询尽量避免返回大量数据 ###

如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。

通常采用分页，一页习惯10/20/50/100条。

### 使用explain分析你SQL执行计划 ###

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

EXPLAIN
    
    SELECT * FROM student WHERE id=1

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

type：

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

key：

*  真正使用的索引方式

### 创建name字段的索引 ###

ALTER TABLE student ADD INDEX index_name (NAME)

### 优化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%'

### 字符串怪现象 ###

反例：

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

正例：

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

理由：

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

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

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

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

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

### where限定查询的数据 ###

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

反例：

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

正例：

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

理由：

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

### 避免在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解析时，如果字段相关的是表达式就进行全表扫描
 *  ### 避免在where子句中使用!=或<>操作符 ###

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

反例：

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

理由：

*  使用!=和<>很可能会让索引失效
 *  ### 去重distinct过滤字段要少 ###

\#索引失效

EXPLAIN
    
    SELECT DISTINCT * FROM student

\#索引生效

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

理由：

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

### where中使用默认值代替null ###

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

ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;

### 批量插入性能提升 ###

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

多条提交：

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

批量提交：

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

理由：

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

### 批量删除优化 ###

避免同时修改或删除过多数据，因为会造成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的错误，所以建议分批操作

### 伪删除设计 ###

商品状态（state）：1-上架、2-下架、3-删除

理由：

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

### 提高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;

### 复合索引最左特性 ###

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

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优化器有关的

### 排序字段创建索引 ###

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

\#使用\*，包含了未索引的字段，导致索引失效

EXPLAIN

SELECT \* FROM student ORDER BY NAME;

EXPLAIN

SELECT \* FROM student ORDER BY NAME,salary

\#name字段有索引

EXPLAIN

SELECT id,NAME FROM student ORDER BY NAME

\#name和salary复合索引

EXPLAIN

SELECT id,NAME FROM student ORDER BY NAME,salary

EXPLAIN

SELECT id,NAME FROM student ORDER BY salary,NAME

\#排序字段未创建索引，性能就慢

EXPLAIN

SELECT id,NAME FROM student ORDER BY sex

### 删除冗余和重复的索引 ###

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 ;

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

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

### inner join 、left join、right join，优先使用inner join ###

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

*  inner join 内连接，只保留两张表中完全匹配的结果集
 *  left join会返回左表所有的行，即使在右表中没有匹配的记录
 *  right join会返回右表所有的行，即使在左表中没有匹配的记录

理由：

*  如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点
 *  同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优

### 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就远不如程序实现，特别当数据量巨大时。

理由：

*  数据库最费劲的就是程序链接的释放。假设链接了两次，每次做上百万次的数据集查询，查完就结束，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，就会额外花费很多实际，这样系统就受不了了，慢，卡顿

### 尽量使用union all替代union ###

反例：

SELECT \* FROM student

UNION

SELECT \* FROM student

正例：

SELECT \* FROM student

UNION ALL

SELECT \* FROM student

理由：

*  union和union all的区别是，union会自动去掉多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复
 *  union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
 *  union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION

[2021070213285975.png]: /images/20210716/a83094da497e480e8f1427231473d085.png