文章目录
一、数据库
-1.概述
用来存储数据和管理数据,最终形成类似于表格的样子,有行有列(字段)
分类: 关系型数据库(数据的紧密关系) 和 非关系型数据库(数据的松散关系)
关系型数据库的产品有很多: Mysql(免费) Oracle SqlServer
非关系型数据库的产品有很多: Redis
-2.Mysql数据库的安装
本质就是一些软件,需要下一步下一步的安装
服务器的安装: 端口号默认使用3306 , 字符集默认使用了latin1(最好改成u8) , 密码可以设置成root
客户端的安装: sqlYog软件
检验: 从开始菜单里找到MySQL/Mariadb
-3.Mysql数据库的使用
使用客户端连接服务器 , 操作服务器里的数据
客户端有两种: DOS窗口 / 可视化的工具
数据库的结构: 数据库 -> 表 -> 记录(行 和列/字段 组成的)
-4.操作数据库的语言
SQL全称是结构化查询语言,对数据库里的数据进行各种操作,常见操作就是增删改查CRUD
SQL的分类:
DML 数据操作语言 , 对数据进行CRUD
DDL 数据库定义语言 , 创建库 , 创建表
DCL 数据库控制语言 , 细粒度的管理操作数据库的权限…
DQL 数据库查询语言,对数据发起查询需求
二、常见的操作
-1.数据库的常用操作
查看所有的库: show databases ;
mysql> show databases;
创建库: create database 库名 default character set utf8;
mysql> create database cgb2106;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
删除库: drop database 库名;
mysql> drop database cgb2106;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
使用库：use 库名;
mysql> use cgb2106;
Database changed
-2.表的常用操作
查看所有的表:show tables;
mysql> show tables;
Empty set (0.00 sec)
创建表：create table 表名(字段名1 类型(长度), 字段名2 类型(长度)…);
mysql> create table user(
-> id int,
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.24 sec)
修改表：alter table 表名 add column 字段名 字段类型(长度);
mysql> alter table user add column adder varchar(30);
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除表：drop table 表名;
mysql> drop table user;
Query OK, 0 rows affected (0.19 sec)
查看表结构：desc 表名;
mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| adder | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
-3.表记录的常用操作
插入记录：insert into 表名 values(字段1的值,字段2的值,字段3的值…);
mysql> insert into user values(1,"张三","北京市");
Query OK, 1 row affected (0.09 sec)
查询记录：select * from 表名 where id = ?;
mysql> select * from user;
+------+--------+-----------+
| id | name | adder |
+------+--------+-----------+
| 1 | 张三 | 北京市 |
+------+--------+-----------+
1 row in set (0.00 sec)
修改记录：update 表名 set 字段名 = 新值 where id = ?;
mysql> update user set name = "泡泡" where id = 1;
Query OK, 0 rows affected (0.14 sec)
删除记录：delete from 表名 where id = ?;
mysql> delete from user where id = 1;
Query OK, 1 row affected (0.07 sec)
排序：select * from 表名 order by 字段名 desc(降序);
mysql> select * from user order by id desc;
+------+----------+-----------+
| id | name | adder |
+------+----------+-----------+
| 3 | 李四 | 成都市 |
| 2 | zhangsan | 定西市 |
| 1 | 泡泡 | 北京市 |
+------+----------+-----------+
3 rows in set (0.00 sec)
记录总数：select count(*) from 表名;
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
三、数据类型
-1.命名规则
字段名必须以字母开头，尽量不要使用拼音
长度不能超过30个字符（不同数据库，不同版本会有不同）
不能使用SQL的保留字，如where,order,group
Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name
多个单词用下划线隔开，而非java语言的驼峰规则
-2.字符
char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间
varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。
-3.日期
date - 年月日
time - 时分秒
datetime - 年月日和时分秒
timestamp - 时间戳，不是日期，是从1970年1月1日到指定日期的毫秒数
-4.图片
只存储其访问路径
四、字段约束
-1.主键约束 — primary key
主键约束：给表里的某个字段设计约束。
主键的特点是字段值必须唯一且不能为null。
表的设计原则：表里必须有主键，作为每条记录的唯一约束。
当主键为数值类型时，为了方便维护，可以设置主键自增策略(auto_increment)
- -1.1.主键自增策略 — auto_increment
主键自增策略：主键的值交给数据库维护管理(从1开始，++)
设置了主键自增策略后，数据库会在表中保存一个"AUTO_INCREMENT"变量值，初始值为1；
当需要id值，不需要我们指定值，由数据库负责从"AUTO_INCREMENT"获取一个id值，作为主键值插入到表中；
每次用完"AUTO_INCREMENT"值，都会自增1. AUTO_INCREMENT=1
CREATE TABLE a (id INT PRIMARY KEY AUTO_INCREMENT);
-2.非空约束 — not null
非空约束：字段的值不能为null，但可以重复。
a表里有两个字段，那就必须赋两个值，只是第一个id的值不必维护。
CREATE TABLE a (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(5) NOT NULL
);
INSERT INTO a VALUES(NULL,"张三");
-3.唯一约束 — unique
唯一约束：值不能相同，但可以为null。
CREATE TABLE a (
id INT PRIMARY KEY AUTO_INCREMENT, #主键约束
NAME VARCHAR(5) NOT NULL, #非空约束,值不能为null
age INT UNIQUE #唯一约束，值不能重复
);
五、基础函数
-1.概述
Mysql数据提供了丰富的函数
常见的：lower upper length concat substr replace ifnull round & ceil & floor now year & month & day
upper(str) :全转大写
lower(str)：全转小写
SELECT
ename,
UPPER(ename),# 全转大写
LOWER(ename)# 全转小写
FROM emp;
length(str):求长度 — str是字段名
一个字母算一个字符，一个汉字算三个字符
SELECT
ename,
LENGTH(ename)# 求长度
FROM emp;
concat(str1,str2…)：拼接字符串 — str1是字段名，str2…是要拼接的内容
SELECT
ename,
CONCAT(ename,'hello',100)# 拼接hello和100
FROM emp;
substr(str,2,3)：截取字符串 — str是字段名，2是开始位置，3是总长度
SELECT
dname,
SUBSTR(dname,3),# 从第3个字符开始截取，截取到字符串末尾
SUBSTR(dname,3,6)# 从第3个字符开始截取，截取到长度为6为止
FROM dept;
replace(str,2,3)：替换 — str是字段名，2是要被替换的原数据，3是新的数据
SELECT
dname,
REPLACE(dname,'o','666')# 把o全部替换成666
FROM dept;
ifnull(expr1,expr2)：判断是否为null — 如果是null，就替换；expr1是字段名，expr2是要替换null的新值(null不能做加减，只有替换成0之后才做加减)
SELECT
comm,
IFNULL(comm,0),# 如果comm的值为null，则用0替换null
IFNULL(comm,0)+sal,# null不能做加减，只有把null转换成0之后才能做加减
(sal+IFNULL(comm,0))*12 年薪# 求年薪
FROM emp;
round & ceil & floor
round(str)：四舍五入
cell(str)：向上取整
floor(str)：向下取整
SELECT
comm,
ROUND(comm),# 四舍五入
CEIL(comm),# 向上取整
FLOOR(comm)# 向下取整
FROM emp;
now() & crudate() & curtime()
now()：年-月-日 时:分:秒
crudate()：年-月-日
curtime()：时:分:秒
SELECT NOW();# 2021-07-27 14:28:00
SELECT CURDATE();# 2021-07-27
SELECT CURTIME();# 14:28:00
year() & month() & day()
hour() & minute() & second()
SELECT YEAR(NOW());# 2021
SELECT YEAR('2021-7-27');# 2021
SELECT MONTH(NOW());# 7
SELECT MONTH('2021-7-27');# 7
SELECT DAY(NOW());# 27
SELECT DAY('2021-7-27');# 27
SELECT HOUR(NOW());# 14
SELECT MINUTE(NOW());# 28
SELECT SECOND(NOW());# 00
转义字符：" \ "
SELECT 'xi'an' -- 单引号是一个SQL语句的特殊字符
SELECT 'xi\'an' --数据中有单引号时,用一个\转义变成普通字符
六、条件查询
-1.distinct — 去重
使用distinct关键字，去除重复的记录行
SELECT DISTINCT loc
FROM dept;
-2.where — 条件
满足条件的才查出来
where中不能使用列别名!!
where语句中不能出现聚合函数
SELECT *
FROM dept
WHERE
# loc = '二区';
# dname = 'research' OR loc = "二区";# 或者关系
dname = 'research' AND loc = "二区";# 并且关系
SELECT *
FROM emp
WHERE
# empno = 100 or empno = 300 or empno = 500;
empno IN (100,300,500);# 作用意义同上(条件满足其中一个就打印输出)
-3.like — 模糊查询
通配符%代表0到n个字符，通配符下划线_代表1个字符
SELECT *
FROM emp
WHERE
# ename LIKE 'a%';# 以a开头的
# ename LIKE '%a';# 以a结束的
# ename LIKE '%a%';# 包含a的
ename LIKE 'a\_\_';# a后面有两个字符的，_代表一个字符位置
-4.null — 空
SELECT *
FROM emp
WHERE
# mgr IS NULL;# 过滤字段值为空的
mgr IS NOT NULL;# 过滤字段值不为空的
-5.between A and B — 在A和B之间(包含A和B)
SELECT *
FROM emp
WHERE
# sal > 3000 AND sal < 10000;
sal BETWEEN 3000 AND 10000;
-6.limit — 分页查询(限制一页的记录有多少条)
分数最高的记录：按分数排序后，limit n，返回前n条。
Oracle做的很笨，实现繁琐，后期有介绍
mysql做的很棒，语法简洁高效。在mysql中，通过limit进行分页查询
SELECT *
FROM emp
# LIMIT 3;# 只查前3条
LIMIT 0,3;# 第1个数字是开始位置(从n+1开始)，第2个数字是总条数
-7.order by — 排序
SELECT *
FROM emp
ORDER BY
sal ASC;# 默认升序
# sal DESC;# 降序
# hiredate;# 按照日期的数字升序
# job;# 拿着每个汉字对应的数字(utf-8)进行升序排列
# ename;# a-z升序
七、聚合函数
count max/min sum/avg
-1.count() — 求个数
SELECT
# COUNT(comm),# 不统计comm是null的 - 低效
# COUNT(1),# 都统计 - 高效
COUNT(*)# 都统计
FROM emp;
-2.max/min
SELECT sal
FROM emp
ORDER BY
sal DESC,# (求最大值)先降序，再分页，显示第一条数据
sal# (求最小值)先升序，再分页，显示第一条数据
LIMIT 1;# 显示第一条数据
SELECT MAX(sal),# 求最大值MIN(sal)# 求最小值
FROM emp;
-3.sum/avg
SELECT SUM(sal),# 求和AVG(sal)# 求平均数
FROM emp;
八、分组group
用于对查询的结果进行分组统计
group by表示分组, having 子句类似where过滤返回的结果
group by 分组前的过滤用where，分组后的过滤用having
什么时候必须分组？
---- 当查询结果中同时出现聚合列和非聚合列时，必须分组
按照什么分组合理？
---- 通常按照非聚合列分组
-1.group by
SELECT empno,AVG(sal)
FROM emp
GROUP BY empno;# 按照非聚合列进行分组
-2.having
having的作用：用来对分组后的内容，进一步过滤
#执行顺序： 2 3 4 1 5
SELECT job,MAX(sal)
FROM emp
#WHRER MAX(sal) > 8000 # 报错，虽然先过滤，再分组高效，但是where中不能使用聚合函数
GROUP BY job
HAVING MAX(sal) > 8000 # 不能改成where，因为后面出现了聚合函数
ORDER BY MAX(sal);
SELECT COUNT(*), YEAR(hiredate)
FROM emp
WHERE YEAR(hiredate) > 2017 #高效，只是不能出现聚合函数
GROUP BY YEAR(hiredate)
# having YEAR(hiredate) > 2017 #报错，having中使用的过滤条件必须是能查到结果的
***小结
char和varchar有什么区别？
char为定长字符串，char(n)，n最大为255
varchar为不定长字符串，varchar(n)，n最大长度为65535
char(10)和varchar(10)存储abc，那它们有什么差别呢？
char保存10个字符，abc三个，其它会用空格补齐；而varchar只用abc三个位置。
datetime和timestamp有什么区别？
date 是年月日
time 是时分秒
datetime 是年月日时分秒，存储和显示是一样的
timestamp 是时间戳，存储的不是个日期，而是从1970年1月1日到指定日期的毫秒数
中文乱码
如果在dos命令下执行insert插入中文数据，数据有乱码，那现在sqlYog客户端执行下面命令：
set names utf8;
set names gbk;
设置客户端字符集和服务器端相同。如果不知道它到底用的什么编码？就两个都尝试下，哪个最后操作完成，查询数据库不乱码，就用哪个。
那为何会造成乱码呢？
Mysql数据库默认字符集是lantin1，以后网页中遇到的ISO8859-1，它是英文字符集，不支持存放中文。我们创建库时，可以指定字符集：
create database yhdb charset utf8;
但这样很容易造成服务器和客户端编码集不同，如服务器端utf8，客户端ISO8859-1。mysql和客户端工具都有习惯的默认编码设置，好几个地方，要都统一才可以保证不乱码。
我们只要保证创建数据库时用utf8，使用可视化工具一般就基本正确。
主键、外键、唯一索引的区别？
- Primary Key 主键约束，自动创建唯一索引
- Foreign Key 外键约束，外键字段的内容是引用另一表的字段内容，不能瞎写
- Unique Index 唯一索引，唯一值但不是主键
drop、delete和truncate之间的区别？
drop删除库或者表，数据和结构定义
delete和truncate只是删除表的数据
delete可以指定where条件，删除满足条件的记录，tuncate删除所有记录
对于自增字段的表，delete不会把自增值清零，而truncate是把表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数
九、事务 transaction
-1.什么是事务
事务就是保证一堆sql(增删改)语句要么全都执行，要么全都不执行
数据库事务(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’;
-2.事务的4个特征（ACID）
事务是必须满足4个条件（ACID）：
原子性（Atomicity，或称不可分割性）
一致性（Consistency）
隔离性（Isolation，又称独立性）
持久性（Durability）
- 原子性：是指多条SQL语句是一个原子，密不可分。如果都正确，就操作了数据；如果有错误的都会发生回滚，回到事务执行之前。
- 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏(保证了数据的一致性和完整性)。
- 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。(保证多线程并发时的数据安全,多个操作之间是被隔离的)
- 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
-3.隔离级别
事务隔离分为不同级别，包括
读未提交（Read uncommitted） 安全性最差,可能发生并发数据问题,性能最好
读提交（read committed） Oracle默认的隔离级别
可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高,不能并发
-4.事务处理
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的
MySQL默认数据库的事务是开启的，执行SQL后自动提交。
MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交。
- -4.1测试事务
先开启事务，然后执行一批SQL语句，再结束事务。
#事务管理：保证一批SQL执行要么全成功，要么全失败
#在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。
#如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。
#如果想要一批SQL使用一个事务，需要我们手动管理事务
#手动管理事务：在开启事务+结束事务(提交commit或回滚rollback)
START TRANSACTION;# 开启事务
INSERT INTO dept VALUES(10,'java开发部','北京');
INSERT INTO dept VALUES(11,'php开发部','北京');
COMMIT;# 结束事务
SELECT * FROM dept;
十、表强化：6种约束
非空约束：字段的值不能为null，但可以重复。
唯一约束：值不能相同，但可以为null。
默认约束：给指定字段设置默认值
检查约束：给指定字段设置合法值的检查规则
主键约束：给表里的某个字段设计约束。
外键约束：
外键：通过指定的SQL语句描述两张表的关系
约束：子表添加记录时，id必须取自主表
删除主表时，必须没有被子表使用着
怎么确定谁是子表或者谁是主表？ -- 子表中有外键的SQL
主键约束：primary key
外键约束：foreign key
默认约束：default
非空约束：not null
检查约束：check
唯一约束：unique
CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(10) NOT NULL UNIQUE, #非空，唯一索引
sex VARCHAR(3) DEFAULT '男', #默认值
age INT,
CHECK (age>0 AND age<=200)
);
CREATE TABLE tb_user_address (
user_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
address VARCHAR(20),
# 表明和哪张表得哪个字段有关系
# foreign key(本表的主键) references 关联表(关联表的主键)
FOREIGN KEY(user_id) REFERENCES tb_user(id)
);
十一、索引
-1.概述
索引：为了加速表中数据行的检索而创建的一种分散的数据结构。可以把索引类比成书的目录，有目录的肯定比没有目录的书，更方便查找
为什么要用索引：
为了提高数据库的查询效率，可以使用索引。
给哪些字段设计索引?
----查询需要量比较大，字段值比较大的时候...
好处：索引事先对数据进行了排序，大大提高了SQL的查询效率
坏处：索引本身也是一张表，表里的数据和真正的表里的数据是重复的，浪费了空间。
-2.分类
- 单值索引：一个索引只包括一个列
- 唯一索引：索引列的值不能重复
- 复合索引：一个索引包括多个列
-3.创建索引
- -3.1查看索引
show index from 表名;
SHOW INDEX FROM emp;
- -3.2创建 单值索引
create index 索引名字 on 表名(字段名);
#创建 单值索引:一个索引包含了一个列
CREATE INDEX job_index ON emp(job);
#使用索引
EXPLAIN
#用来观察SQL的执行计划,主要看有没有用索引
#(观察两列的结果:key和possible_keys)
SELECT * FROM emp WHERE job='经理';#背后会用job索引
- -3.3创建 唯一索引
alter table 表名 add unique(字段名);
#创建 唯一索引
#alter table emp add unique(deptno)报错,deptno的值重复啦
ALTER TABLE emp ADD UNIQUE(ename);
#使用索引
EXPLAIN # 用来观察SQL的执行计划,主要看有没有用索引
SELECT * FROM emp WHERE ename='jack';#背后会用唯一索引
唯一约束和唯一索引的区别：
- 概念上不同，约束是为了保证数据的完整性，索引是为了辅助查询；
- 创建唯一约束时，会自动的创建唯一索引
- 在理论上，不一样，在实际使用时，基本没有区别。
- -3.4创建 复合索引
当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，
相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则，也称为最左特性。
alter table 表名 add index(字段名1,字段名2...);
#创建 复合索引:最左特性(要先执行最左边的字段名，然后执行右边的)
ALTER TABLE emp ADD INDEX many_index(ename,job,hiredate);
#使用索引
EXPLAIN # 用来观察SQL的执行计划,主要看有没有用索引
SELECT * FROM emp WHERE ename='jack' AND job='经理';#复合索引生效
EXPLAIN
SELECT * FROM emp WHERE hiredate='2010-1-1';#复合索引失效
EXPLAIN
SELECT * FROM emp WHERE job='经理' AND hiredate='2010-1-1';#复合索引失效
- -3.5删除索引
alter table 表名 drop index 索引名;
ALTER TABLE dept DROP INDEX many_index;
十二、多表联查
多表查询是指基于两个和两个以上的表的查询。
-1.笛卡尔积
# 方式1：笛卡儿积
SELECT *
FROM emp ,dept;# 查出了所有数据
SELECT *
FROM emp e,dept d
WHERE e.deptno = d.deptno# 两张表的关联关系
AND d.dname = 'accounting';
上面这种查询两个表的方式称为：笛卡尔积(Cartesian product)，又称直积。
一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。
-2.连接查询 join
- - 2.1内连接查询
SELECT *
FROM emp e
JOIN dept d # 两边都满足条件的交集
ON e.deptno = d.deptno # 描述字段
WHERE d.dname = 'accounting'; # 具体的过滤条件
- - 2.2外连接查询
- - - 2.2.1左外连接 — (工作中最常用)
小表 left join 大表，小表驱动大表。
左连接：显示左边所有的和右边满足条件了的数据
SELECT *
FROM emp e
LEFT JOIN dept d # 左边的所有和右边满足条件了的
ON e.deptno = d.deptno # 描述字段
WHERE d.dname = 'accounting'; # 具体的过滤条件
- - - 2.2.2右外连接
右连接：显示右边所有的和左边满足条件了的数据
SELECT *
FROM emp e
RIGHT JOIN dept d # 右边的所有和左边满足条件了的
ON e.deptno = d.deptno # 描述字段
WHERE d.dname = 'accounting'; # 具体的过滤条件
- -2.3内连接和外连接的区别
-3.子查询
子查询：把上次的查询结果再次作为结果查询
# 方式3：子查询：把上次的查询结果作为结果再次查询
# 查询部门名称是accounting的所有信息
# 根据部门名称查询部门编号dept
# 把查询到的编号作为条件，查员工信息emp
SELECT *
FROM emp
WHERE deptno =
(SELECT deptno
FROM dept
WHERE dname = 'accounting');
-4.练习
#练习1:查询办公地址在一区的员工信息
#子查询:根据地址查编号,再根据编号查员工
SELECT *
FROM emp
WHERE deptno =
(SELECT deptno
FROM dept
WHERE loc = '一区');
#笛卡尔积
SELECT *
FROM dept d,emp e
WHERE d.deptno = e.deptno
AND dept.loc = '一区';
#join
SELECT *
FROM dept d
LEFT JOIN emp e
ON d.deptno = e.deptno
WHERE d.loc = '一区';
#练习2:查询办公地址在二区的员工信息
#子查询:先根据地址查编号,再根据编号查员工信息
SELECT deptno
FROM dept
WHERE loc = '二区';# 2 3
SELECT *
FROM emp
WHERE deptno IN (2,3); #in查询
SELECT *
FROM emp
WHERE deptno IN (
SELECT deptno
FROM dept
WHERE loc='二区');
#笛卡尔积
SELECT *
FROM dept d, emp e
WHERE d.deptno = e.deptno
AND d.loc = '二区';
#join
SELECT *
FROM dept a
INNER JOIN emp b
ON a.deptno = b.deptno
WHERE a.loc = '二区';
十三、视图
视图：由数据库中的一个表或多个表导出的虚拟表，是一种虚拟存在的表，方便用户对数据的操作。
可以把sql的查询结果缓存起来，存入视图中
好处：简化了SQL的编写
坏处：没法做SQL的优化，占用空间
视图是一个虚拟表，是从数据库中一个或多个表中导出来的表，其内容由查询定义。同真实表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。
视图是存储在数据库中的查询的SQL语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等；另一个原因是可使复杂的查询易于理解和使用。
十四、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 /* IF NOT EXISTS*/`mysql-db` /* 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');
-1.查询SQL尽量不要使用select *，而是具体字段
反例：SELECT * FROM student
正例：SELECT id,NAME FROM student
理由：
字段较多时，大表能达到100多个字段甚至达200多个字段
只取需要的字段，节省资源、减少网络开销
select * 进行查询时，很可能不会用到索引，就会造成全表扫描
-2.避免在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条件，索引还是可能失效的
-3.使用varchar代替char
理由：
varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间
char按声明大小存储，不足补空格
其次对于查询来说，在一个相对较小的字段内搜索，效率更高
-4.尽量使用数值替代字符串类型
主键（id）：primary key优先使用数值类型int，tinyint
性别（sex）：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint
支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
服务状态（state）：1-开启、2-暂停、3-停止
商品状态（state）：1-上架、2-下架、3-删除
-5.查询尽量避免返回大量数据
如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。
同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。
通常采用分页，一页习惯10/20/50/100条。
-6.使用explain分析SQL执行计划
SQL很灵活，一个需求可以很多实现，那哪个最优呢？
SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。
EXPLAIN
SELECT * FROM student WHERE id=1
-7.是否使用了索引及其扫描类型
type：
ALL 全表扫描，没有优化，最慢的方式
index 索引全扫描
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
null MySQL不访问任何表或索引，直接返回结果
key：
真正使用的索引方式
-8.创建name字段的索引
ALTER TABLE student ADD INDEX index_name (NAME)
-9.字符串怪现象
反例：
#未使用索引
EXPLAIN
SELECT * FROM student WHERE NAME=123
正例：
#使用索引
EXPLAIN
SELECT * FROM student WHERE NAME='123'
理由:
为什么第一条语句未加单引号就不走索引了呢？
这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较
-10.索引不宜太多，一般5个以内
索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率
索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间
索引表的数据是要排序的，排序要花时间
insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序
-11.索引不适合建在有大量重复数据的字段上
如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。
-12.where限定查询的数据
数据中假定就一个男的记录
反例：
SELECT id,NAME FROM student WHERE sex='男'
正例：
SELECT id,NAME FROM student WHERE id=1 AND sex='男'
理由：
需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销
-13.避免在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子句中使用!=或<>操作符
-14.应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。
反例：
EXPLAIN
SELECT * FROM student WHERE salary!=3000
EXPLAIN
SELECT * FROM student WHERE salary<>3000
理由：
使用!=和<>很可能会让索引失效
-15.去重distinct过滤字段要少
#索引失效
EXPLAIN
SELECT DISTINCT * FROM student
#索引生效
EXPLAIN
SELECT DISTINCT id,NAME FROM student
EXPLAIN
SELECT DISTINCT NAME FROM student
理由：
带distinct的语句占用cpu时间高于不带distinct的语句。
因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，如cpu时间
-16.where中使用默认值代替null
#修改表，增加age字段，类型int，非空，默认值0
ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;
-17.批量插入性能提升
大量数据提交，上千，上万，批量性能非常快，mysql独有
多条提交：
INSERT INTO student (id,NAME) VALUES(4,'齐雷');
INSERT INTO student (id,NAME) VALUES(5,'刘昱江');
批量提交：
INSERT INTO student (id,NAME) VALUES(4,'齐雷'),(5,'刘昱江');
理由：
默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交。自然速度飞升。
数据量小体现不出来
-18.批量删除优化
避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。
反例：
#一次删除10万或者100万+？
delete from student where id <100000;
#采用单一循环操作，效率低，时间漫长
for（User user:list）{
delete from student;
}
正例：
//分批进行删除，如每次500
for(User user:list){
delete student where id<500;
}
delete student where id>=500 and id<1000;
理由：
一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作
-19.伪删除设计
商品状态（state）：1-上架、2-下架、3-删除
理由：
这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查
同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。
通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用
操作速度快，特别数据量很大情况下
-20提高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;
-21.复合索引最左特性
创建复合索引，也就是多个字段
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优化器有关的
-22.排序字段创建索引
什么样的字段才需要创建索引呢？原则就是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
-23.删除冗余和重复的索引
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 ;
-24.不要有超过5个以上的表连接
关联的表个数越多，编译的时间和开销也就越大
每次关联内存中都生成一个临时表
应该把连接表拆开成较小的几个执行，可读性更高
如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
阿里规范中，建议多表联查三张表以下
inner join 、left join、right join，优先使用inner join
三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小
inner join 内连接，只保留两张表中完全匹配的结果集
left join会返回左表所有的行，即使在右表中没有匹配的记录
right join会返回右表所有的行，即使在左表中没有匹配的记录
理由：
如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点
同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优
-25.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就远不如程序实现，特别当数据量巨大时。
理由：
数据库最费劲的就是程序链接的释放。假设链接了两次，每次做上百万次的数据集查询，查完就结束，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，就会额外花费很多实际，这样系统就受不了了，慢，卡顿
-26.尽量使用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
-27.优化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%'
十六、优化总结
尽量使用字段名代替*
做表设计时,字段的类型最好是varchar代替char
字段里的值,最好用数字代替字符串
尽量把过滤条件精细,能用and不用or
索引的设计,最多5个,不能太多
模糊查询,尽量要确定开始元素,让索引生效
数据库对数字没有严格的要求,name=123索引失效,name='123’索引生效
无用的索引最好及时删除
扫一扫
553
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
