SQL 学习总结
1 SQL 语法基础
SQL 语言按照功能划分成以下的 4 个部分:
- DDL,英文叫做 Data Definition Language,也就是数据定义语言,它用来定义我们的数据库对象,包括数据库、数据表和列。通过使用 DDL,可以创建,删除和修改数据库和表结构。
- DML,英文叫做 Data Manipulation Language,数据操作语言,我们用它操作和数据库相关的记录,比如增加、删除、修改数据表中的记录。
- DCL,英文叫做 Data Control Language,数据控制语言,用它来定义访问权限和安全级别。
- DQL,英文叫做 Data Query Language,数据查询语言,我们用它查询想要的记录,它是 SQL 语言的重中之重。在实际的业务中,绝大多数情况下都是在和查询打交道。
1.1 相关命令
mysql> select @@profiling
; 查看 profiling 是否打开,0 为没打开, 设置
mysql> set profiling=1
; 这样就可以查看语句运行时间。
查看所有的 profiles
mysql> show profiles
;
查看上一条 proflie,
mysql> show profile
;
1.2 DDL、DML
DDL 的英文全称是 Data Definition Language,中文是数据定义语言。它定义了数据库的结构和数据表的结构。
数据库
CREATE DATABASE nba
; // 创建一个名为nba的数据库
DROP DATABASE nba
; // 删除一个名为nba的数据库
表
CREATE TABLE [table_name](字段名 数据类型,......)
;
CREATE TABLE player (
player_id int(11) NOT NULL AUTO_INCREMENT,
player_name varchar(255) NOT NULL
);
添加字段:ALTER TABLE player ADD (age int(11))
;
修改字段名:ALTER TABLE player RENAME COLUMN age to player_age;
修改字段数据类型:ALTER TABLE player MODIFY (player_age float(3,1))
;
删除字段:ALTER TABLE player DROP COLUMN player_age;
插入:
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
唯一性约束和普通索引(NORMAL INDEX)之间是有区别的。唯一性约束相当于创建了一个约束和普通索引,目的是保证字段的正确性,而普通索引只是提升数据检索的速度,并不对字段的唯一性进行约束
1.3 DSL
SELECT
查询所有列 SQL:SELECT * FROM heros;
起别名:
SELECT name AS n, hp_max AS hm, mp_max AS mm, attack_max AS am, defense_max AS dm FROM heros;
查询中增加列:
SQL:SELECT '王者荣耀' as platform, name FROM heros;
去重复行:SELECT DISTINCT attack_range, name FROM heros;
排序检索数据:SELECT name, hp_max FROM heros ORDER BY hp_max DESC;
限制返回数量:SELECT name, hp_max FROM heros ORDER BY hp_max DESC LIMIT 5;
不同的DBMS 关键字可能不同,SQL Server 和 Access,需要使用 TOP 关键字:
SQL:SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC;
关键字顺序:SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...
执行顺序(MySQL\Oracle):
FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT
例如:
SELECT DISTINCT player_id, player_name, count(*) as num #顺序5
FROM player JOIN team ON player.team_id = team.team_id #顺序1
WHERE height > 1.80 #顺序2
GROUP BY player.team_id #顺序3
HAVING num > 2 #顺序4
ORDER BY num DESC #顺序6
LIMIT 2 #顺序7
WHERE
对空值进行查询:SELECT name, hp_max FROM heros WHERE hp_max IS NULL;
SELECT name, role_main, role_assist, hp_max, mp_max, birthdate
FROM heros
WHERE (role_main IN ('法师', '射手') OR role_assist IN ('法师', '射手'))
AND DATE(birthdate) NOT BETWEEN '2016-01-01' AND '2017-01-01'
ORDER BY (hp_max + mp_max) DESC
通配符:
想要匹配任意字符串出现的任意次数,需要使用(%)通配符;
(_)只代表一个字符;
SELECT name FROM heros WHERE name LIKE '_%太%;'
。
消耗数据库更长的时间来进行匹配。即使你对 LIKE 检索的字段进行了索引,索引的价值也可能会失效。如果要让索引生效,那么 LIKE 后面就不能以(%)开头,比如使用LIKE '%太%'或LIKE '%太’的时候就会对全表进行扫描。如果使用LIKE ‘太%’,同时检索的字段进行了索引的时候,则不会进行全表扫描
1.4 SQL 函数
从函数定义的角度出发,我们可以将函数分成内置函数和自定义函数。
1.4.1 内置函数
算数函数
SELECT ROUND(37.25,1),运行结果 37.3。
** 字符串函数**
SELECT CONCAT('abc', 123)
,运行结果为 abc123。
日期函数
SELECT EXTRACT(YEAR FROM ‘2019-04-03’),运行结果为 2019。
转换函数
SELECT CAST(123.123 AS DECIMAL(8,2)),运行结果为 123.12。
SELECT COALESCE(null,1,2),运行结果为 1。
返回最大值:SELECT MAX(hp_max) FROM heros;
结合其他信息:SELECT name, hp_max FROM heros WHERE hp_max = (SELECT MAX(hp_max) FROM heros);
SELECT name, EXTRACT(YEAR FROM birthdate) AS birthdate FROM heros WHERE birthdate is NOT NULL
或者
SELECT name, YEAR(birthdate) AS birthdate FROM heros WHERE birthdate is NOT NULL
日期比较: SELECT * FROM heros WHERE DATE(birthdate)>'2016-10-01';
SQL: SELECT AVG(hp_max), AVG(mp_max), MAX(attack_max) FROM heros WHERE DATE(birthdate)>'2016-10-01'
1.4.2 聚集函数
SQL中聚集函数把一组数据聚集成一条数据,一共 5 个:
查询不同的生命最大值的英雄数量是多少,SQL: SELECT COUNT(DISTINCT hp_max) FROM heros;
分组
对数据进行分组,需要使用 GROUP BY 子句
SQL: SELECT COUNT(*), role_main FROM heros GROUP BY role_main;
当我们创建出很多分组的时候,有时候就需要对分组进行过滤。你可能首先会想到 WHERE 子句,实际上过滤分组使用的是 HAVING。HAVING 的作用和 WHERE 一样,都是起到过滤的作用,只不过 WHERE 是用于数据行,而 HAVING 则作用于分组。
关键字顺序:
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...
对 GROUP BY 的理解,聚集函数会配合 GROUP BY 使用,使得 GROUP BY 后的分组能够聚集成一条数据。
SELECT COUNT(*) as num, role_main, role_assist FROM heros WHERE hp_max > 6000 GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC
在执行顺序上,SELECT字段在GROUP BY和HAVING之后,不过在SELECT字段之前,已经计算了聚集函数,也就是COUNT(*) as num。聚集函数的计算在GROUP BY之后,HAVING之前。
1.5 子查询
子查询从数据表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条件进行执行,那么这样的子查询叫做非关联子查询。同样,如果子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查询,然后再将结果反馈给外部,这种嵌套的执行方式就称为关联子查询。
1.5.1 关联子查询
如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重新计算一次,这样的子查询就称之为关联子查询
关联子查询通常会和 EXISTS 一起来使用,EXISTS 子查询用来判断条件是否满足,满足的话为 True , 不满足为 False.
SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
SELECT player_id, team_id, player_name FROM player WHERE NOT EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
1.5.2 非关联子查询
集合比较子查询
集合比较子查询的作用是与另一个查询结果集进行比较,我们可以在子查询中使用 IN、ANY、ALL 和 SOME 操作符,
SELECT player_id, team_id, player_name FROM player WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002)
ANY、ALL 关键字必须与一个比较操作符一起使用。
1.6 连接
1.6.1 SQL92
笛卡尔积
笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。
等值连接
两张表的等值连接就是用两张表中都存在的列进行连接。
SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_id
可以使用别名进行代替。
SELECT player_id, a.team_id, player_name, height, team_name FROM player AS a, team AS b WHERE a.team_id = b.team_id
注意,在 SELECT 中也需要使用别名,不然出错。
非等值连接
当我们进行多表查询的时候,如果连接多个表的条件是等号时,就是等值连接,其他的运算符连接就是非等值查询。
SELECT p.player_name, p.height, h.height_level
FROM player AS p, height_grades AS h
WHERE p.height BETWEEN h.height_lowest AND h.height_highest
外连接
一张是主表,另一张是从表。如果是多张表的外连接,那么第一张表是主表,即显示全部的行,而剩下的表则显示对应连接的信息。
左外连接,就是指左边的表是主表,需要显示左边表的全部行,而右侧的表是从表,(+)表示哪个是从表。
SELECT * FROM player, team where player.team_id = team.team_id(+)
SQL99:
SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id
右外连接,指的就是右边的表是主表,需要显示右边表的全部行,而左侧的表是从表
SELECT * FROM player, team where player.team_id(+) = team.team_id
SQL99 :
SELECT * FROM player RIGHT JOIN team on player.team_id = team.team_id
**思考:**左外连接和右外连接,如果把右外连接的两个表的顺序换一下就可以用左外连接了,区分左右外连接还有什么更深的意义?
表可能是经过一系列语句形成的,不方便与更改表的顺序
外连接重点在为主表的全展示,而另外一张表匹配不上的为 NULL
自连接
自连接可以对多个表进行操作,也可以对同一个表进行操作。也就是说查询条件使用了当前表的字段。
# 自连接,白天气温比 a 城市高的城市 id
SELECT b.weather_id FROM wea_forecast_d AS a, wea_forecast_d AS b WHERE a.weather_id = 101010100 AND a.templ_day < b.templ_day;
思考:
以下语句行不通的原因?
SELECT a.weather_id FROM wea_forecast_d AS a WHERE templ_day > (SELECT templ_day FROM wea_forecast_d AS b WHERE b.weather_id = 101010100 LIMIT 1)
查询执行顺序:
FROM > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT
1.6.2 SQL99
交叉连接
交叉连接实际上就是 SQL92 中的笛卡尔乘积,这里采用的是 CROSS JOIN
SELECT a.weather_id, a.a_province, b.station FROM wea_alarm AS a CROSS JOIN wea_api_stat AS b;
多张表进行连接
SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3
自然连接
相当于 SQL92 标准中的等值连接,会自动查询两张表中所有相同的字段,然后进行等值连接。
SELECT a.weather_id, a.a_province, a.a_name, b.city_name FROM wea_alarm AS a NATURAL JOIN wea_city_info as b;
ON 连接
用它可以实现自然连接的功能
SELECT a.weather_id, a.a_province, a.a_name, b.city_name FROM wea_alarm AS a JOIN wea_city_info as b ON a.weather_id = b.weather_id;
USING 连接
进行连接的时候,可以用 USING 指定数据表里的同名字段进行等值连接
使用 USING 相对简洁,并且可以指定字段进行连接
SELECT a.weather_id, a.a_province, a.a_name, b.city_name FROM wea_alarm AS a JOIN wea_city_info as b USING(weather_id);
外连接
- 1 左外连接:LEFT JOIN 或 LEFT OUTER JOIN
- 2 右外连接:RIGHT JOIN 或 RIGHT OUTER JOIN
- 3 全外连接:FULL JOIN 或 FULL OUTER JOIN
全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。MySQL、Access、SQLite、MariaDB 等数据库软件不支持全外连接。
自连接
# 自连接,白天气温比 a 城市高的城市 id SQL92
SELECT b.weather_id FROM wea_forecast_d AS a, wea_forecast_d AS b WHERE a.weather_id = 101010100 AND a.templ_day < b.templ_day;
# 自连接,白天气温比 a 城市高的城市 id SQL99
SELECT b.weather_id FROM wea_forecast_d AS a JOIN wea_forecast_d AS b ON a.weather_id = 101010100 AND a.templ_day < b.templ_day;
JOIN 就是默认的 INNER JOIN,LEFT JOIN是左连接
1.6.3 SQL92、SQL99 区别
连接操作基本分三种情况:
- 内连接:将多个表之间满足连接条件的数据行查询出来。它包括了等值连接、非等值连接和自连接。
- 外连接:会返回一个表中的所有记录,以及另一个表中匹配的行。它包括了左外连接、右外连接和全连接。
- 交叉连接:也称为笛卡尔积,返回左表中每一行与右表中每一行的组合。在 SQL99 中使用的 CROSS JOIN。
多表连接使用 SQL99 层次性更强,可读性更高。
SELECT ...
FROM table1
JOIN table2 ON table1和table2的连接条件
JOIN table3 ON table2和table3的连接条件
嵌套逻辑类似:
for t1 in table1:
for t2 in table2:
if condition1:
for t3 in table3:
if condition2:
output t1 + t2 + t3
不同 DBMS 使用注意点
别名:为了让 SQL 查询语句更简洁,经常会使用表别名 AS,不过在 Oracle 中是不存在 AS 的,使用表别名的时候,直接在表名后面写上表别名即可,比如 player p
,而不是 player AS p
。
SQLLite 只支持左连接:SQLite 是一款轻量级的数据库软件,在外连接上只支持左连接,不支持右连接,不过如果你想使用右连接的方式,比如table1 RIGHT JOIN table2,在 SQLite 你可以写成table2 LEFT JOIN table1,这样就可以得到相同的效果。
1.7 视图
视图也即虚拟表,封装了底层与数据表的接口。它相当于是一张表或多张表的数据结果集。本身不具有数据,无法对底层数据进行修改。
1.7.1 基础语法
创建视图
语法:
CREATE VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition
实质上是在 SQL 查询语句的基础上封装了 VIEW, 这样就会基于 SQL 语句的结果形成一张虚拟表。
# 高于平均气温的城市 ID
CREATE VIEW observe_above_avg_templ AS
SELECT weather_id, templ
FROM wea_observe
WHERE templ > (SELECT AVG(templ) FROM wea_observe);
可以使用嵌套视图。
修改视图
ALTER VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition
如在视图中增加一个字段:
# 高于平均气温的城市 ID
CREATE VIEW observe_above_avg_templ AS
SELECT weather_id, templ,humidity
FROM wea_observe
WHERE templ > (SELECT AVG(templ) FROM wea_observe);
SQLite 不支持视图的修改,仅支持只读视图,也就是说你只能使用 CREATE VIEW 和 DROP VIEW,如果想要修改视图,就需要先 DROP 然后再 CREATE
删除视图
语法:
DROP VIEW view_name
1.7.2 视图作用
利用视图完成复杂的连接
例如有一张显示温度等级的表 templ_grades,里面三个字段:温度等级 templ_level,等级对应的最高温度 templ_highest,等级对应的最低温度 templ_lowest。
# observe 表中的气温属于哪个等级
CREATE VIEW observe_templ_grades AS
SELECT o.weather_id, o.templ, t.templ_grades, o.create_date
FROM wea_observe AS o JOIN templ_grades AS t
ON templ BETWEEN t.templ_highest AND t.templ_lowest;
之后可以直接使用视图进行查询。
利用视图对数据进行格式化
计算字段
很多统计的需求可以通过视图来完成。
加强安全性
针对不同的用户开放不同的查询权限,某些敏感字段可以不包括在视图中。
思考
视图的存活期?
一个视图其实是SELECT语句的集合,执行时会提前编译好,可以反复使用。在底层执行顺序的时候和SELECT语句是一样:
1、FROM子句组装数据
2、WHERE子句进行条件筛选
3、GROUP BY分组
4、使用聚集函数进行计算;
5、HAVING筛选分组;
6、计算所有的表达式;
7、SELECT 的字段;
8、ORDER BY排序
9、LIMIT筛选
1.8 存储过程 Stored Procedure
视图是虚拟表,通常不对底层数据表直接操作,而存储过程是程序化的 SQL,可以直接操作底层数据表,相比于面向集合的操作方式,能够实现一些更复杂的数据处理。
定义存储过程:
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
需要执行的语句
END
如定义一个累加运算:
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
DECLARE i INT;
DECLARE sum INT;
SET i = 1;
SET sum = 0;
WHILE i <= n DO
SET sum = sum + i;
SET i = i +1;
END WHILE;
SELECT sum;
END
使用运算的时候就可以用 CALL add_num(20);
在 MySQL 中需要将运算当作一个整体:
DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
DECLARE i INT;
DECLARE sum INT;
SET i = 1;
SET sum = 0;
WHILE i <= n DO
SET sum = sum + i;
SET i = i +1;
END WHILE;
SELECT sum;
END //
DELIMITER ;
1.9 事务
1.9.1 事务处理
事务的四个特性 ACID
- A,也就是原子性(Atomicity)。原子的概念就是不可分割,你可以把它理解为组成物质的基本单位,也是我们进行数据处理操作的基本单位。
- C,就是一致性(Consistency)。一致性指的就是数据库在进行事务操作后,会由原来的一致状态,变成另一种一致的状态。也就是说当事务提交后,或者当事务发生回滚后,数据库的完整性约束不能被破坏。
- I,就是隔离性(Isolation)。它指的是每个事务都是彼此独立的,不会受到其他事务的执行影响。也就是说一个事务在提交之前,对其他事务都是不可见的。
- D,指的是持久性(Durability)。事务提交之后对数据的修改是持久性的,即使在系统出故障的情况下,比如系统崩溃或者存储介质发生故障,数据的修改依然是有效的。因为当事务完成,数据库的日志就会被更新,这时可以通过日志,让系统恢复到最后一次成功的更新状态。
事务常用控制语句:
- START TRANSACTION 或者 BEGIN,作用是显式开启一个事务。
- COMMIT:提交事务。当提交事务后,对数据库的修改是永久性的。
- ROLLBACK 或者 ROLLBACK TO [SAVEPOINT],意为回滚事务。意思是撤销正在进行的所有没有提交的修改,或者将事务回滚到某个保存点。
- SAVEPOINT:在事务中创建保存点,方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
- RELEASE SAVEPOINT:删除某个保存点。
- SET TRANSACTION,设置事务的隔离级别。
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
SET @@completion_type = 1;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;
MySQL 中 completion_type 参数的作用:
- completion=0,这是默认情况。也就是说当我们执行 COMMIT 的时候会提交事务,在执行下一个事务时,还需要我们使用 START TRANSACTION 或者 BEGIN 来开启。
- completion=1,这种情况下,当我们提交事务后,相当于执行了 COMMIT AND CHAIN,也就是开启一个链式事务,即当我们提交事务之后会开启一个相同隔离级别的事务(隔离级别会在下一节中进行介绍)。
- completion=2,这种情况下 COMMIT=COMMIT AND RELEASE,也就是当我们提交后,会自动与服务器断开连接。
1.9.2 事务隔离
SQL-92 标准中对三种异常进行了定义,这些异常情况级别分别为脏读(Dirty Read)、不可重复读(Nnrepeatable Read)和幻读(Phantom Read)。
- 脏读:读到了其他事务还没有提交的数据。
- 不可重复读:对某数据进行读取,发现两次读取的结果不同,也就是说没有读到相同的内容。这是因为有其他事务对这个数据同时进行了修改或删除。
- 幻读:事务 A 根据条件查询得到了 N 条数据,但此时事务 B 更改或者增加了 M 条符合事务 A 查询条件的数据,这样当事务 A 再次进行查询的时候发现会有 N+M 条数据,产生了幻读。
SQL-92 标准定义了 4 种隔离级别来解决这些异常情况。
可串行化,将事务进行串行化,也就是在一个队列中按照顺序执行,可串行化是最高级别的隔离等级,可以解决事务读取中所有可能出现的异常情况,但是它牺牲了系统的并发性。
1.10 游标
游标实际上是面向过程的思维方式,与面向集合的思维方式不同的地方在于,游标更加关注“如何执行”。我们可以通过游标更加精细、灵活地查询和管理想要的数据行。
2 SQL 优化
2.1 服务器资源使用监控
2.1.1 数据控内部状况监控
活动会话(Active Session)监控
参考: