SQL 基础知识点

1. 数据库相关术语

• 数据库（database）：保存有组织的数据的容器（通常是一个文件或一组文件）。
• 数据表（table） ：某种特定类型数据的结构化清单。
• 模式（schema）：关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。数据库和表都有模式。
• 列（column）：表中的一个字段。所有表都是由一个或多个列组成的。
• 行（row）：表中的一个记录。
• 主键（primary key）：一列（或一组列），其值能够唯一标识表中每一行。

2. 增删改查

2.1 插入数据

INSERT INTO 语句用于向表中插入新记录。

--插入完整的行
INSERT INTO user VALUES (10, 'root', 'root', 'xxxx@163.com');

--插入行的一部分
INSERT INTO user(username, password, email) VALUES ('admin', 'admin', 'xxxx@163.com');

--插入查询出来的数据
INSERT INTO user(username) SELECT name FROM account;

2.2 更新数据

UPDATE 语句用于更新表中的记录。

UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';

2.3 删除数据

DELETE 语句用于删除表中的记录。

TRUNCATE TABLE 可以清空表，也就是删除所有行。

--删除表中的指定数据
DELETE FROM user
WHERE username = 'robot';

--清空表中的数据
TRUNCATE TABLE user;

2.4 查询数据

SELECT 语句用于从数据库中查询数据。

SELECT
    {* | <字段列名>}
    [
        FROM <表 1>, <表 2>…
        [WHERE <表达式>
        [GROUP BY <group by definition>
        [HAVING <expression> [{<operator> <expression>}…]]
        [ORDER BY <order by definition>]
        [LIMIT[<offset>,] <row count>]
    ]

• select 查询结果，如：[学号，平均成绩：组函数 avg（成绩）]
• from 从哪张表中查找数据，如：[涉及到成绩：成绩表 score]
• where 查询条件，如：[b. 课程号='0003' and b. 成绩>80]
• group by 分组，如：[每个学生的平均：按学号分组]
• having 对分组结果指定条件，如：[大于 60 分]
• order by 对查询结果排序，如：[增序：成绩 ASC / 降序：成绩 DESC];
• limit 使用 limit 子句返回 topN（对应这个问题返回的成绩前两名），如：[ limit 2 ==>从 0 索引开始读取 2 个]limit==>从 0 索引开始 [0,N-1]

3. 子查询

子查询是嵌套在较大查询中的 SQL 查询。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。

• 子查询可以嵌套在 SELECT，INSERT，UPDATE 或 DELETE 语句内或另一个子查询中。
• 子查询通常会在另一个 SELECT 语句的 WHERE 子句中添加。
• 您可以使用比较运算符，如 >，<，或 =。比较运算符也可以是多行运算符，如 IN，ANY 或 ALL。
• 子查询必须被圆括号 () 括起来。
• 子查询首先在其父查询之前执行，以便可以将子查询的结果传递给父查询。

--子查询的子查询
SELECT cust_name, cust_contact
    FROM customers
    WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                      FROM orderitems
                                      WHERE prod_id = 'RGAN01'));

4. 多表连接

• 如果一个 JOIN 至少有一个公共字段并且它们之间存在关系，则该 JOIN 可以在两个或多个表上工作。

• 连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

• JOIN 保持基表（结构和数据）不变。

• JOIN 有两种连接类型：内连接和外连接。

• 内连接又称等值连接，使用 INNER JOIN 关键字。在没有条件语句的情况下返回笛卡尔积。

  • 自连接可以看成内连接的一种，只是连接的表是自身而已。

• 自然连接是把同名列通过 = 测试连接起来的，同名列可以有多个。

内连接 vs 自然连接？

内连接提供连接的列，而自然连接自动连接所有同名列。

• 外连接返回一个表中的所有行，并且仅返回来自次表中满足连接条件的那些行，即两个表中的列是相等的。外连接分为左外连接、右外连接、全外连接（Mysql 不支持）。

  • 左外连接就是保留左表没有关联的行。
  • 右外连接就是保留右表没有关联的行。

连接 vs 子查询？

连接可以替换子查询，并且比子查询的效率一般会更快。

--内连接（INNER JOIN）
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

--自连接
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers c1, customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

--自然连接（NATURAL JOIN）
SELECT *
FROM Products
NATURAL JOIN Customers;

--左连接（LEFT JOIN）
SELECT customers.cust_id, orders.order_num
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id;

--右连接（RIGHT JOIN）
SELECT customers.cust_id, orders.order_num
FROM customers RIGHT JOIN orders
ON customers.cust_id = orders.cust_id;

5. UNION

UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行。

UNION 基本规则：

• 所有查询的列数和列顺序必须相同。

• 每个查询中涉及表的列的数据类型必须相同或兼容。

• 通常返回的列名取自第一个查询。

• 默认会去除相同行，如果需要保留相同行，使用 UNION ALL。

• 只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

  应用场景：

• 在一个查询中从不同的表返回结构数据。

• 对一个表执行多个查询，按一个查询返回数据。

SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state IN ('LI', 'WANG', 'LIU')
UNION
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'MY4All';

6. 分组

GROUP BY：

• GROUP BY 子句将记录分组到汇总行中。
• GROUP BY 为每个组返回一个记录。
• GROUP BY 可以按一列或多列进行分组。
• 分组相关函数：sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数。

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

注意：

1. sum、avg 一般处理数值型，而 max、min、count 可以处理任何类型。
2. 分组函数都忽略 null 值
3. 可以和 distinct 关键字搭配，去重。
4. count(*) 一般用来统计行数。
5. 和分组函数一同查询的字段要求是 group by 后的字段

7. 常用函数

不同数据库的函数往往各不相同，因此不可移植。主要以 MySQL 的函数为例。

7.1 字符函数

函数名称	作 用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

7.2 日期函数

函数名称	作 用
CURDATE 和 CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW 和 SYSDATE	两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP	获取 UNIX 时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME	将 UNIX 时间戳转换为时间格式，与 UNIX_TIMESTAMP 互为反函数
MONTH	获取指定日期中的月份
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
DAYOFWEEK	获取指定日期对应的一周的索引位置值
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0?52 或 1?53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是 1~366
DAYOFMONTH	获取指定日期是一个月中是第几天，返回值范围是 1~31
YEAR	获取年份，返回值范围是 1970?2069
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与 TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE	两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE	两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME	时间加法运算，在原始时间上添加指定的时间
SUBTIME	时间减法运算，在原始时间上减去指定的时间
DATEDIFF	获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
WEEKDAY	获取指定日期在一周内的对应的工作日索引

7.3 数字函数

函数名称	作 用
ABS	求绝对值
SQRT	求二次方根
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个 BIGINT
RAND	生成一个 0~1 之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号
POW 和 POWER	两个函数的功能相同，都是所传参数的次方的结果值
SIN	求正弦值
ASIN	求反正弦值，与函数 SIN 互为反函数
COS	求余弦值
ACOS	求反余弦值，与函数 COS 互为反函数
TAN	求正切值
ATAN	求反正切值，与函数 TAN 互为反函数
COT	求余切值

8. SQL 优化

• 优化成本：硬件>系统配置>数据库表结构>SQL 及索引。

• 优化效果：硬件<系统配置<数据库表结构<SQL 及索引。

对于 MySQL 层优化一般遵从五个原则：

1. 减少数据访问： 设置合理的字段类型，启用压缩，通过索引访问等减少磁盘 IO

2. 返回更少的数据： 只返回需要的字段和数据分页处理 减少磁盘 IO 及网络 IO

3. 减少交互次数： 批量 DML 操作，函数存储等减少数据连接次数

4. 减少服务器 CPU 开销： 尽量减少数据库排序操作以及全表查询，减少 CPU 内存占用

5. 利用更多资源： 使用表分区，可以增加并行操作，更大限度利用 CPU 资源

总结到 SQL 优化中，就三点：

• 最大化利用索引；
• 尽可能避免全表扫描；
• 减少无效数据的查询；

8.1 查询时尽量使用索引

8.1.1 尽量避免使用 in 和 not in，会导致引擎走全表扫描

SELECT * FROM t WHERE id IN (1,2,3)

优化方式：如果是连续数值，可以用 between 代替。如下：

select * from t where id between 1 and 3

如果是子查询，可以用 exists 代替。

-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);

8.1.2?尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHEREid = 1ORid = 3

优化方式：可以用 union 代替 or。如下

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

8.1.3 尽量避免进行 null 值的判断，会导致数据库引擎放弃索引进行全表扫描。

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值 0，对 0 值进行判断。如下：

SELECT * FROM t WHERE score = 0

8.1.4 隐式类型转换造成不使用索引

如下 SQL 语句由于索引对列类型为 varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

select col1 from table where col_varchar=123;

8.2 SELECT 语句其他优化

8.2.1?避免出现 select *

首先，select * 操作在任何类型数据库中都不是一个好的 SQL 编写习惯。

使用 select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的 I/O, 内存和 CPU 消耗。

建议提出业务实际需要的列数，将指定列名以取代 select *。

8.2.2?多表关联查询时，小表在前，大表在后

在 MySQL 中，执行 from 后的表关联查询是从左往右执行的（Oracle 相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前 100 行就符合返回条件并 return 了。

例如：表 1 有 50 条数据，表 2 有 30 亿条数据；如果全表扫描表 2，你品，那就先去吃个饭再说吧是吧。

8.2.3?用 where 字句替换 HAVING 字句

避免使用 HAVING 字句，因为 HAVING 只会在检索出所有记录之后才对结果集进行过滤，而 where 则是在聚合前刷选记录，如果能通过 where 字句限制记录的数目，那就能减少这方面的开销。HAVING 中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在 where 字句中。

where 和 having 的区别：where 后面不能使用组函数

8.2.4?调整 Where 字句中的连接顺序

MySQL 采用从左往右，自上而下的顺序解析 where 子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

8.3 增删改 DML 语句优化

8.3.1?大批量插入数据

如果同时执行大量的插入，建议使用多个值的 INSERT 语句（方法二）。这比使用分开 INSERT 语句快（方法一），一般情况下批量插入效率有几倍的差别。

方法一：

insert into T values(1,2);
insert into T values(1,3);
insert into T values(1,4);

方法二：

Insert into T values(1,2),(1,3),(1,4);

选择后一种方法的原因有三。

• 减少 SQL 语句解析的操作，MySQL 没有类似 Oracle 的 share pool，采用方法二，只需要解析一次就能进行数据的插入操作；
• 在特定场景可以减少对 DB 连接次数
• SQL 语句较短，可以减少网络传输的 IO。

8.3.2?适当使用 commit

适当使用 commit 可以释放事务占用的资源而减少消耗，commit 后能释放的资源如下：

• 事务占用的 undo 数据块；
• 事务在 redo log 中记录的数据块；
• 释放事务施加的，减少锁争用影响性能。特别是在需要使用 delete 删除大量数据的时候，必须分解删除量并定期 commit。

8.4 查询条件优化

8.4.1 对于复杂的查询，可以使用中间临时表暂存数据

8.4.2?优化 group by 语句

默认情况下，MySQL 会对 GROUP BY 分组的所有值进行排序，如 “GROUP BY col1，col2，....;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，...;” 如果显式包括一个包含相同的列的 ORDER BY 子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。

如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL 禁止排序。例如：

SELECT col1 , col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

8.4.3?优化 join 语句

MySQL 中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接 (JOIN).. 替代。

例子：假设要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

如果使用连接 (JOIN).. 来完成这个查询工作，速度将会有所提升。尤其是当 salesinfo 表中对 CustomerID 建有索引的话，性能将会更好，查询如下：

SELECT col1 FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID
WHERE salesinfo.CustomerID IS NULL

连接 (JOIN).. 之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

8.4.4 拆分复杂 SQL 为多个小 SQL，避免大事务

• 简单的 SQL 容易使用到 MySQL 的 QUERY CACHE；
• 减少锁表时间特别是使用 MyISAM 存储引擎的表；
• 可以使用多核 CPU。

8.5 建表优化

8.5.1 表中建立索引，优先考虑 where、order by 使用到的字段

8.5.2 尽量使用数字型字段

尽量使用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。? 这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

8.5.3 用 varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。? 不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL 也包含在内），都是占用 100 个字符的空间的，如果是 varchar 这样的变长字段， null 不占用空间。

9. 数据库三大范式

第一范式：确保每列保持原子性，数据表中的所有字段值都是不可分解的原子值。

这是第一范式只有一张表 ，七个字段在业务中不可再拆分：

订单 id	商品 id	用户 id	商品名称	商品数量	用户地址	用户电话
1	1	1	Java	1	杭州	110
1	2	2	C++	1	上海	120
2	2	3	C++	1	北京	114

第二范式：确保表中的每列都和主键相关。

第二范式分成 2 表 订单明细表和用户表 ，一张表只描述一个事件，第一范式中有两件事，一个用户，一个订单。

订单 id	商品 id	用户 id	商品名称	商品数量
1	1	1	Java	1
1	2	2	C++	1
2	2	3	C++	1

用户 id	用户地址	用户电话
1	杭州	110
2	上海	120
3	北京	114

第三范式：确保每列都和主键列直接相关而不是间接相关。

第三范式分成 3 表 继续拆分订单明细表，添加商品表，一张表中非主键字段只依赖主键，不传递依赖，商品名称只依赖商品 ID，不依赖订单 ID。

订单 id	商品 id	用户 id	商品数量
1	1	1	1
1	2	2	1
2	2	3	1

商品 id	商品名称
1	C++
2	C++
1	1
1	2
2	2

最后提下，三大范式在实际中也不是必须严格遵循的，根据业务可以灵活调整，并不是一成不变的。

10. SQL 语句分类

• 数据据定义语言 DDL（Data Definition Language）：主要有 CREATE，DROP，ALTER 等对逻辑结构有操作的，包括表结构、视图和索引。
• 数据库查询语言 DQL（Data Query Language）：主要以 SELECT 为主(DQL 不在官方说明中)
• 数据操纵语言 DML（Data Manipulation Language）：主要包括 INSERT，UPDATE，DELETE
• 数据控制功能 DCL（Data Control Language）：主要是权限控制能操作，包括 GRANT，REVOKE，COMMIT，ROLLBACK 等。