SQL语句

DDL（Data Definition Language）

数据定义语言，用来定义数据库对象(数据库，表，字段)

数据库操作

1
2
3
4
5
6
7
8
9
10

#查询所有数据库
SHOW DATABASES;		
#查询当前数据库
SELECT DATABASE;	
#创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];	
#删除
DROP DATABASE[IF EXISTS] 数据库名;
#使用
USE 数据库名；

表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

#查询当前数据库所有表
SHOW TABLES;
#查询表结构
DESC 表名；
#查看指定表的建表语句；
SHOW CREATE 表名；

#创建表
CREATE TABLE 表名（
	字段1 字段1类型 [COMMENT 字段1注释(用单引号括起来)]，
	字段2 字段2类型 [COMMENT 字段2注释]，
	字段3 字段3类型 [COMMENT 字段3注释]，
	...
	字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释];

#添加字段
ALTER TABLE 表名 ADD 字段名 类型（长度）[COMMENT 注释][约束];

#修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）;
#修改字段名和数据类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新类型（长度）[COMMENT 注释][约束];

#删除字段
ALTER TABLE 表名 DROP 字段名;
#删除表
DROP TABLE[IF EXISTS] 表名;
#删除指定表，并重新创建该表
TRUNCATE TABLE 表名；

DML（Data Manipulation Language）

数据操作语言，用来对数据库表中的数据进行增删改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

#添加数据
#1、给指定字段添加数据
INSERT INTO 表名（字段名1，字段名2，...） VALUES(值1，值2，...);
#2、给全部字段添加数据
INSERT INTO 表名 VALUES(值1，值2，...);
#3、批量添加数据
INSERT INTO 表名（字段名1，字段名2，...） VALUES(值1，值2，...),(值1，值2，...),(值1，值2，...);
INSERT INTO 表名 VALUES (值1，值2，...),(值1，值2，...),(值1，值2，...);
#字符串和日期型数据应该包含在引号中，插入的数据大小应该在字段的规定范围内，括号要加

#修改数据
UPDATE 表名 SET 字段名1=值1，字段名2=值2,...[WHERE 条件];
#修改语句的条件可以有，也可以没有，如果没有，则会修改整张表的所有数据

#删除数据
DELETE FROM 表名 [WHERE 条件];
#条件可以有也可以没有，如果没有则会删除整张表的所有数据
#DELETE不能删除某一个字段的值（可以用UPDATE)

DQL（Data Query Language）

数据查询语言，用来查询数据库中表的记录

基本查询

1
2
3
4
5
6
7
8

#基本查询
#1、查询多个字段
SELECT 字段1，字段2，字段3，...FROM 表名；
SELECT * FROM 表名；
#2、设置别名
SELECT 字段1[AS 别名1],字段2[AS 别名2] ...FROM 表名；
#3、去掉重复记录
SELECT DISTINCT 字段列表 FROM 表名；

条件查询

1
2
3

#条件查询
#1、语法
SELECT 字段列表 FROM 表名 WHERE 条件列表；

条件查询的条件列表

分组查询

聚合函数

将一列数据作为整体，进行纵向计算

1
2

#语法
SELECT 聚合函数（字段列表） FROM 表名；

分组查询

1
2

#语法
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]；

这里where和having的区别

• 执行时机不同：where是分组前进行过滤，having是对分组后的数据进行过滤
• 判断条件不同：where不能对聚合函数判断（即不能使用聚合函数），having可以

执行顺序：where > 聚合函数 > having

分组之后，查询的字段一般为聚合函数和分组字段，查询其它字段无意义

排序查询

1
2

#语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2;

排序方式

1. ASC ：升序（默认）
2. DESC：降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段排序

分页查询

1
2

#语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数；

注意：

• 起始索引从0开始，起始索引=（查询页码-1）*每页记录数
• 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

DQL的执行顺序

编写顺序如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT					#----第四步
	字段列表
FROM					#----第一步
	表名列表
WHERE					#----第二步
	条件列表
GROUP BY				#----第三步
	分组字段列表
HAVING
	分组后条件列表
ORDER BY				#----第五步
	排序字段列表
LIMIT					#----第六步
	分页参数

DCL（Data Control Language）

数据控制语言，用来创建数据库用户、控制数据库的访问权限

管理用户

1
2
3
4
5
6
7
8
9
10
11
12

#查询用户
USE mysql;
SELECT * FROM user;

#创建用户
CREATE USER '用户名'@'主机名‘ IDENTIFIED BY '密码'；

#修改用户密码
ALTER USER '用户名'@'主机名’ IDENTIFIED WITH mysql_native_password BY '新密码'；

#删除用户
DROP USER '用户名'@'主机名’；

权限控制

1
2
3
4
5
6
7
8

#查询权限
SHOW GRANTS FOR '用户名'@‘主机名’；

#授予权限
GRANT 权限列表 ON 数据库.表名 TO '用户名'@‘主机名’；

#撤销权限
REVOKE 权限列表 ON 数据库.表名 FROM '用户名'@‘主机名’；

• 多个权限之间，用逗号分隔
• 授权时，数据库名和表名可以用*进行通配

函数

字符串函数

数值函数

日期函数

流程函数

可以在SQL语句中实现条件筛选，从而提高语句的效率

约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据

1
2
3
4
5
6
7
8
9
10
11

#语法一、创建的时候指定外键约束
CREATE TABLE 表名（
	字段名 数据类型
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
#语法二、对于已创建的表来修改外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);

#删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称；

删除/更新行为

1
2

#语法，只需在后面加上更新和删除的方式
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) ON UPDATE CASCADE ON DELETE CASCADE;

多表查询

多表关系

• 一对多（多对一）
• 多对多
• 一对一

内连接

查询两张表的交集部分

1
2
3
4
5

#隐式内连接
SELECT 字段列表 FROM 表1，表2 WHERE 条件...；

#显式内连接  （和隐式没有区别，仅是写法的不同而已）[INNER 可有可无
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...；

外连接

左外连接和右外连接

左：查询左表的所有数据和左右表交集部分的数据

右：查询右表的所有数据和左右表交集部分的数据

1
2
3
4
5

#语法------左 [OUTER可有可无
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

#语法------右
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

右外可改成左外。左外会写就行

自连接

1
2

#语法
SELECE 字段列表 FROM 表A 别名A JOIN 表A 表名B ON 条件...;

自连接查询，可以是内连接也可以是外连接

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

1
2
3
4

#语法
SELECT 字段列表 FROM 表A ...
UNION [ALL]			#带ALL不会对查询结果去重
SELECT 字段列表 FROM 表B ...;

使用条件：两个语句的字段列表（数量/类型）要保持一致

子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。子查询要用（）括起来

子查询外部的语句可以是INSERT/UPADTE/DELETE/SELECT 中的任何一个

标量子查询

子查询结果为单个值

常用的操作符：=，<>,>,>=,<,<=

列子查询

子查询结果是一列

常用的操作符：IN , NOT IN , ANY(满足子查询列表的一个即可) , SOME(与ANY同等) ,ALL(必须全部满足)

行子查询

子查询结果是一行

常用的操作符： = ，<> , IN , NOT IN

表子查询

子查询结果是多行多列

常用的操作符： IN

事务

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务操作

1
2
3
4
5

#开启事务
START TRANSATION

#提交/回滚事务
COMMIT / ROLLBACK;

*事务四大特性ACID

• 原子性（Atomicity） ：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性（Consistency） ：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
• 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

*脏读、不可重复读、幻读

• 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

  例子:小明读取到小红提交的100数据.但是小红异常回滚了数据,100变成了90,这个时候小明还是100,但实际是90(脏读)

• 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

  例子:小明多次读取A数据,小红在小明读取数据时,每次读取都修改了数据并提交,小明多次读到的数据不一致

• 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

  例子:小明修改了A,B数据,小红同时又插入了一条C数据,小明会感觉自己明明数据都改了,突然多出来一条,以为出现了幻觉自己漏了一条

*事务的隔离级别

级别越高，安全性越高，执行效率越低

1
2
3
4
5

#查看事务隔离级别
SELECE @@TRANSATION_ISOLATION;

#设置事务隔离级别
SET [SESSION|GLOBAL] TRANSATION ISOLATION LEVEL {级别}

引擎

存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

MySQL存储引擎MyISAM与InnoDB区别

• Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。
• MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。

存储引擎选择

如果没有特别的需求，使用默认的Innodb即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

索引

索引介绍

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

索引的优缺点

优点

• 使用索引可以大大加快数据的检索速度

缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间。

索引结构

索引分类

从数据结构角度

• 树索引
• Hash索引

从物理存储角度

• 聚集索引

  • • 聚簇索引是一种索引组织方式，它将索引和数据行存储在一起，即数据行按照索引的顺序存储在磁盘上。
    • 聚簇索引的叶子节点保存的是完整的数据行，因此不需要进行额外的查找操作就可以获取到所需的数据。
    • InnoDB 存储引擎的主键索引就是一个聚簇索引，如果表没有显式地定义主键，InnoDB 会选择一个唯一的非空索引作为聚簇索引。

• 非聚集索引

  • • 非聚簇索引是一种索引组织方式，它将索引和数据行分开存储，即索引保存了指向数据行的指针（通常是行的物理地址或主键值）。
    • 非聚簇索引的叶子节点保存的是指向数据行的引用，当查询需要获取数据时，首先根据索引查找到相应的行指针，然后再通过行指针获取数据行。
    • 在MySQL中，MyISAM 存储引擎的索引通常是非聚簇索引。

总的来说，聚簇索引和非聚簇索引的区别在于索引和数据行的存储方式。聚簇索引将索引和数据行存储在一起，而非聚簇索引将索引和数据行分开存储。

对于非聚簇索引，一定会回表查询吗？

答：不一定。试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。 这种情况就称之为索引覆盖。

索引覆盖：如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。

这里值得注意的是不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。

从逻辑角度

• 普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。
  • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
• 唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
• 主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键。
• 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
  • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

索引语法

SQL性能分析

SQL优化

插入数据

load指令一次插入大批量数据

主键优化

• 满足业务需求的情况下，尽量降低主键的长度
• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
• 尽量不要使用UUID做主键或者是其它自然主键，如身份证号
• 业务操作时，避免对主键的修改

order by 优化

group by优化