SQL学习笔记

时间：2024-03-02 15:21:27

作者： Grey

原文地址：SQL学习笔记

SQL基础

注：本文中的SQL语句如果用到了特定方言，都是基于MySQL数据库。

关于DDL

DDL 的英文全称是 Data Definition Language，中文是数据定义语言。它定义了数据库的结构和数据表的结构。在 DDL 中，我们常用的功能是增删改，分别对应的命令是 CREATE、DROP 和 ALTER。

需要注意的是：在执行 DDL 的时候，不需要 COMMIT，就可以完成执行任务。

排序规则是utf8_general_ci，代表对大小写不敏感，如果设置为utf8_bin，代表对大小写敏感。

DISTINCT

DISTINCT 其实是对后面所有列名的组合进行去重

SELECT DISTINCT attack_range, name FROM heros

其实是对(attack_range,name)这个组合去重。

LIMIT

另外在查询过程中，我们可以约束返回结果的数量，使用 LIMIT 关键字。

SELECT name, hp_max FROM heros ORDER BY hp_max DESC LIMIT 5

在不同的 DBMS 中使用的关键字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关键字，而且需要放到 SELECT 语句的最后面。如果是 SQL Server 和 Access，需要使用 TOP 关键字，比如：

SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC

如果是 DB2，使用FETCH FIRST 5 ROWS ONLY这样的关键字：

SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY

如果是 Oracle，你需要基于 ROWNUM 来统计行数：

SELECT name, hp_max FROM heros WHERE ROWNUM <=5 ORDER BY hp_max DESC

需要说明的是，这条语句是先取出来前 5 条数据行，然后再按照 hp_max 从高到低的顺序进行排序, 如果这样写：

SELECT name, hp_max FROM (SELECT name, hp_max FROM heros ORDER BY hp_max) WHERE ROWNUM <=5

就表示先执行查询结果，再来过滤结果中的前五条。

WHERE语句中 AND 和 OR优先级

WHERE 子句中同时出现 AND 和 OR 操作符的时候，你需要考虑到执行的先后顺序，也就是两个操作符执行的优先级。一般来说 () 优先级最高，其次优先级是 AND，然后是 OR。

SQL中的命名规范

MySQL 在 Linux 的环境下，数据库名、表名、变量名是严格区分大小写的，而字段名是忽略大小写的。
而 MySQL 在 Windows 的环境下全部不区分大小写。

SQL编写的一个规范：

  • 数据库名、表名、表别名、字段名、字段别名等都小写
  • SQL保留字、函数名、绑定变量等都大写
  • 数据表的字段名推荐采用下划线命名
  • SQL语句必须以分号结尾

COUNT(字段) , COUNT( * ) 和 COUNT(1)

COUNT(字段)会忽略字段值值为 NULL 的数据行，而 COUNT( * ) 和 COUNT(1) 只是统计数据行数，不管某个字段是否为 NULL。

AVG、MAX、MIN 等聚集函数会自动忽略值为 NULL 的数据行。

关联子查询和非关联子查询

可以依据子查询是否执行多次，从而将子查询划分为关联子查询和非关联子查询。子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做非关联子查询。同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为关联子查询

一个非关联子查询的例子：

SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player)

一个关联子查询的例子：

SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)

(NOT) EXISTS子查询

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)

IN VS EXISTS

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)

SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)

实际上在查询过程中，在我们对 cc 列建立索引的情况下，我们还需要判断表 A 和表 B 的大小。在这里例子当中，表 A 指的是 player 表，表 B 指的是 player_score 表。如果表 A 比表 B 大，那么 IN 子查询的效率要比 EXIST 子查询效率高，因为这时 B 表中如果对 cc 列进行了索引，那么 IN 子查询的效率就会比较高。同样，如果表 A 比表 B 小，那么使用 EXISTS 子查询效率会更高，因为我们可以使用到 A 表中对 cc 列的索引，而不用从 B 中进行 cc 列的查询。
当 A 小于 B 时，用 EXISTS。因为 EXISTS 的实现，相当于外表循环，实现的逻辑类似于：

 for i in A
     for j in B
         if j.cc == i.cc then ...

当 B 小于 A 时用 IN，因为实现的逻辑类似于：

 for i in B
     for j in A
         if j.cc == i.cc then ...

哪个表小就用哪个表来驱动，A 表小就用 EXISTS，B 表小就用 IN。

其他一些子查询的关键字：EXISTS、IN、ANY、ALL 和 SOME

函数

一个简单的函数例子：Leetcode 177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N - 1;
  RETURN (
      SELECT DISTINCT Salary FROM Employee GROUP BY Salary
      ORDER BY Salary DESC LIMIT 1 OFFSET N
  );
END

Employee表数据如下：

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

函数调用：

SELECT getNthHighestSalary(3);

结果：

+------------------------+
| getNthHighestSalary(3) |
+------------------------+
| 100                    |
+------------------------+

存储过程

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 ;

调用

CALL add_num(10);

另一个例子


CREATE PROCEDURE `get_hero_scores`(
       OUT max_max_hp FLOAT,
       OUT min_max_mp FLOAT,
       OUT avg_max_attack FLOAT,
       s VARCHAR(255)
       )
BEGIN
       SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END

调用

CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

自动提交（autocommit）

set autocommit =0;  //关闭自动提交

set autocommit =1;  //开启自动提交

CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
BEGIN;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;

"张飞"这条记录，如果数据库未开启自动提交，则不会入库，如果开启了自动提交，则第二个”张飞“输入会回滚不插入，但是第一条”张飞“数据依然会插入。

completion_type

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 这个参数有 3 种可能：

  • completion=0，这是默认情况。也就是说当我们执行 COMMIT 的时候会提交事务，在执行下一个事务时，还需要我们使用 START TRANSACTION 或者 BEGIN 来开启。
  • completion=1，这种情况下，当我们提交事务后，相当于执行了 COMMIT AND CHAIN，也就是开启一个链式事务，即当我们提交事务之后会开启一个相同隔离级别的事务（隔离级别会在下一节中进行介绍）。
  • completion=2，这种情况下 COMMIT=COMMIT AND RELEASE，也就是当我们提交后，会自动与服务器断开连接。

事务并发可能产生的异常

  1. 脏读（Dirty Read）

读到了其他事务还没有提交的数据。

  1. 不可重复读（Nnrepeatable Read）

对某数据进行读取，发现两次读取的结果不同，也就是说没有读到相同的内容。这是因为有其他事务对这个数据同时进行了修改或删除。

  1. 幻读（Phantom Read）

事务 A 根据条件查询得到了 N 条数据，但此时事务 B 更改或者增加了 M 条符合事务 A 查询条件的数据，这样当事务 A 再次进行查询的时候发现会有 N+M 条数据，产生了幻读。

SQL-92 标准还定义了 4 种隔离级别来解决这些异常情况。这些隔离级别能解决的异常情况如下表所示：

脏读 不可重复读 幻读
读未提交（READ UNCOMMITTED） 允许 允许 允许
读已提交（READ COMMITTED） 禁止 允许 允许
可重复读（REPEATABLE READ） 禁止 禁止 允许
可串行化（SERIALIZABLE 禁止 禁止 禁止
  1. 读未提交，也就是允许读到未提交的数据，这种情况下查询是不会使用锁的，可能会产生脏读、不可重复读、幻读等情况。
  2. 读已提交就是只能读到已经提交的内容，可以避免脏读的产生，属于 RDBMS 中常见的默认隔离级别（比如说 Oracle 和 SQL Server），但如果想要避免不可重复读或者幻读，就需要我们在 SQL 查询的时候编写带加锁的 SQL 语句
  3. 可重复读，保证一个事务在相同查询条件下两次查询得到的数据结果是一致的，可以避免不可重复读和脏读，但无法避免幻读。MySQL 默认的隔离级别就是可重复读。
  4. 可串行化，将事务进行串行化，也就是在一个队列中按照顺序执行，可串行化是*别的隔离等级，可以解决事务读取中所有可能出现的异常情况，但是它牺牲了系统的并发性。

查看隔离级别

SHOW VARIABLES LIKE 'transaction_isolation';

配置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

如何使用游标

CREATE PROCEDURE `calc_hp_max`()
BEGIN
       -- 创建接收游标的变量
       DECLARE hp INT;
       -- 创建总数变量 
       DECLARE hp_sum INT DEFAULT 0;
       -- 创建结束标志变量
     DECLARE done INT DEFAULT false;
       -- 定义游标
       DECLARE cur_hero CURSOR FOR SELECT hp_max FROM heros;
       -- 指定游标循环结束时的返回值
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

       OPEN cur_hero;
       read_loop:LOOP
       FETCH cur_hero INTO hp;
       -- 判断游标的循环是否结束
       IF done THEN
                     LEAVE read_loop;
       END IF;
       SET hp_sum = hp_sum + hp;
       END LOOP;
       CLOSE cur_hero;
       SELECT hp_sum;
END

更复杂的一个例子

CREATE PROCEDURE `alter_attack_growth`()
BEGIN
       -- 创建接收游标的变量
       DECLARE temp_id INT;
       DECLARE temp_growth, temp_max, temp_start, temp_diff FLOAT;
       -- 创建结束标志变量
       DECLARE done INT DEFAULT false;
       -- 定义游标
       DECLARE cur_hero CURSOR FOR SELECT id, attack_growth, attack_max, attack_start FROM heros;
       -- 指定游标循环结束时的返回值
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

       OPEN cur_hero;
       FETCH cur_hero INTO temp_id, temp_growth, temp_max, temp_start;
       REPEAT
                     IF NOT done THEN
                            SET temp_diff = temp_max - temp_start;
                            IF temp_growth < 5 THEN
                                   IF temp_diff > 200 THEN
                                          SET temp_growth = temp_growth * 1.1;
                                   ELSEIF temp_diff >= 150 AND temp_diff <=200 THEN
                                          SET temp_growth = temp_growth * 1.08;
                                   ELSEIF temp_diff < 150 THEN
                                          SET temp_growth = temp_growth * 1.07;
                                   END IF;
                            ELSEIF temp_growth >=5 AND temp_growth <=10 THEN
                                   SET temp_growth = temp_growth * 1.05;
                            END IF;
                            UPDATE heros SET attack_growth = ROUND(temp_growth,3) WHERE id = temp_id;
                     END IF;
       FETCH cur_hero INTO temp_id, temp_growth, temp_max, temp_start;
       UNTIL done = true END REPEAT;

       CLOSE cur_hero;
END

关于数据库调优

image

导图引用自20丨当我们思考数据库调优的时候，都有哪些维度可以选择？

关于各种范式

1NF 指的是数据库表中的任何属性都是原子性的，不可再分。

2NF 指的数据表里的非主属性都要和这个数据表的候选键有完全依赖关系。所谓完全依赖不同于部分依赖，也就是不能仅依赖候选键的一部分属性，而必须依赖全部属性。

一个没有满足 2NF 的例子，

一张球员比赛表 player_game，里面包含球员编号、姓名、年龄、比赛编号、比赛时间和比赛场地等属性，
这里候选键和主键都为:

（球员编号，比赛编号），

我们可以通过候选键来决定如下的关系：

(球员编号, 比赛编号) → (姓名, 年龄, 比赛时间, 比赛场地，得分)

上面这个关系说明球员编号和比赛编号的组合决定了球员的姓名、年龄、比赛时间、比赛地点和该比赛的得分数据。
但是这个数据表不满足第二范式，因为数据表中的字段之间还存在着如下的对应关系：

(球员编号) → (姓名，年龄)
(比赛编号) → (比赛时间, 比赛场地)

也就是说候选键中的某个字段决定了非主属性。
插入异常：如果我们想要添加一场新的比赛，但是这时还没有确定参加的球员都有谁，那么就没法插入。
删除异常：如果我要删除某个球员编号，如果没有单独保存比赛表的话，就会同时把比赛信息删除掉。
更新异常：如果我们调整了某个比赛的时间，那么数据表中所有这个比赛的时间都需要进行调整，否则就会出现一场比赛时间不同的情况。

3NF 在满足 2NF 的同时，对任何非主属性都不传递依赖于候选键。也就是说不能存在非主属性 A 依赖于非主属性 B，非主属性 B 依赖于候选键的情况。比如：
image
你能看到球员编号决定了球队名称，同时球队名称决定了球队主教练，非主属性球队主教练就会传递依赖于球员编号，因此不符合 3NF 的要求。

索引的分类

功能上分：普通索引，唯一索引，主键索引，全文索引

物理结构上：聚集索引(顺序)和非聚集索引(非顺序)，类比链表和数组的区别。

字段上分：单一索引和联合索引（最左匹配原则）

  • 聚集索引的叶子节点存储的就是我们的数据记录，非聚集索引的叶子节点存储的是数据位置。非聚集索引不会影响数据表的物理存储顺序。

  • 一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，也就是多个索引目录提供数据检索。

  • 使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。实验 3：使用聚集索引和非聚集

参考文档

MySQL实战45讲

SQL必知必会

相关文章