SQL 学习笔记

基础

SELECT

书写顺序

  1. SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 →
  2. HAVING 子句 → 6. ORDER BY 子句

执行顺序

FROM → INNER JOIN/LEFT OUTER JOIN/RIGHT OUTER JOIN/CROSS JOIN → WHERE → GROUP BY → HAVING → SELECT → UNION( ALL)/INTERSECT( ALL)/EXCEPT → ORDER BY

DELETE

DELETE 与 TRUNCATE

DELETE FROM <表名>;TRUNCATE <表名>; 都可以清空表的所有记录,它们的不同之处(MySQL 5.7):

  • 后者执行效率更高
  • 后者会重置自增值
  • 前者属于 DML,可以回滚,可以激发触发器;后者属于 DDL,与事务无关,不会激发触发器

NULL 处理函数 COALESCE

SELECT COALESCE(NULL, 1)  AS col_1,
       COALESCE(NULL, 'test', NULL)  AS col_2,
       COALESCE(NULL, NULL, '2009-11-01') AS col_3;

执行结果为:

col_1 col_2 col_3
1 test 2009-11-01

CASE 表达式

语法

CASE WHEN < 求值表达式 > THEN < 表达式 >
     WHEN < 求值表达式 > THEN < 表达式 >
     WHEN < 求值表达式 > THEN < 表达式 >
       .
       .
       .
     ELSE < 表达式 >
END

其中 <求值表达式> 就是类似“列=值”这样,返回值为真值(TRUE/FALSE/UNKNOWN)的表达式。我们也可以将其看作使用 =、!= 或者 LIKE、BETWEEN 等谓词编写出来的表达式。

<表达式> 则会返回一个值,作为 CASE 表达式的最终值。

CASE 表达式会从对最初的 WHEN 子句中的“<求值表达式>”进行求值开始执行。当返回值为 TRUE 时,中止执行,并返回 THEN 子句中的“<表达式>”。如果以上“<求值表达式>”均不为 TRUE,返回 ELSE 子句中的“<表达式>”。

用法示例

假设有如下一张表,名为 Product(DDL 见本文结尾):

product_id product_name product_type sale_price purchase_price regist_date
0001 T恤衫 衣服 1000 500 2009-09-20
0002 打孔器 办公用品 500 320 2009-09-11
0003 运动T恤 衣服 4000 2800 NULL
0004 菜刀 厨房用具 3000 2800 2009-09-20
0005 高压锅 厨房用具 6800 5000 2009-01-15
0006 叉子 厨房用具 500 NULL 2009-09-20
0007 擦菜板 厨房用具 880 790 2008-04-28
0008 圆珠笔 办公用品 100 NULL 2009-11-11
-- 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price
                ELSE 0
            END) AS sum_price_clothes,
       SUM(CASE WHEN product_type = '厨房用具' THEN sale_price
                ELSE 0
            END) AS sum_price_kitchen,
       SUM(CASE WHEN product_type = '办公用品' THEN sale_price
                ELSE 0
            END) AS sum_price_office
FROM Product;

执行结果为:

sum_price_clothes sum_price_kitchen sum_price_office
5000 11180 600

窗口函数

MySQL 5.7 还不支持窗口函数,本节使用 MariaDB 10.3.7 进行测试。

语法

<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <列清单>)

其中,<窗口函数> 可以使用:

  • 聚合函数:SUM、AVG、COUNT、MAX、MIN
  • 专用窗口函数:RANK、DENSE_RANK、ROW_NUMBER 等

假设有如下一张表,名为 Product(DDL 见本文结尾):

product_id product_name product_type sale_price purchase_price regist_date
0001 T恤衫 衣服 1000 500 2009-09-20
0002 打孔器 办公用品 500 320 2009-09-11
0003 运动T恤 衣服 4000 2800 NULL
0004 菜刀 厨房用具 3000 2800 2009-09-20
0005 高压锅 厨房用具 6800 5000 2009-01-15
0006 叉子 厨房用具 500 NULL 2009-09-20
0007 擦菜板 厨房用具 880 790 2008-04-28
0008 圆珠笔 办公用品 100 NULL 2009-11-11

聚合函数

-- 累计平均值
  SELECT product_id, product_name, sale_price,
         AVG(sale_price) OVER (ORDER BY product_id) AS current_avg
    FROM Product
ORDER BY product_id;
/* 结果为
   product_id | product_name | sale_price | current_avg
   0001       | T恤          | 1000       | 1000          // (1000) / 1
   0002       | 打孔器       | 500        | 750           // (1000 + 500) / 2
   0003       | 运动T恤      | 4000       | 1833.3333     // (1000 + 500 + 4000) / 3
   0004       | 菜刀         | 3000       | 2125          // (1000 + 500 + 4000 + 3000) / 4
   0005       | 高压锅       | 6800       | 3060          // ...
   0006       | 叉子         | 500        | 2633.3333
   0007       | 擦菜板       | 880        | 2382.8571
   0008       | 圆珠笔       | 100        | 2097.5
*/

-- 滚动平均值(当前行和前 1 行)
  SELECT product_id, product_name, sale_price,
         AVG(sale_price) OVER (ORDER BY product_id ROWS 1 PRECEDING) AS moving_avg
    FROM Product
ORDER BY product_id;
/* 结果为
   product_id | product_name | sale_price | moving_avg
   0001       | T恤          | 1000       | 1000          // (1000) / 1
   0002       | 打孔器       | 500        | 750           // (1000 + 500) / 2
   0003       | 运动T恤      | 4000       | 2250          // (500 + 4000) / 2
   0004       | 菜刀         | 3000       | 3500          // (4000 + 3000) / 2
   0005       | 高压锅       | 6800       | 4900          // ...
   0006       | 叉子         | 500        | 3650
   0007       | 擦菜板       | 880        | 690
   0008       | 圆珠笔       | 100        | 490
*/

-- 滚动平均值(当前行、前 1 行和后 1 行)
  SELECT product_id, product_name, sale_price,
         AVG(sale_price) OVER (ORDER BY product_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
    FROM Product
ORDER BY product_id;
/* 结果为
   product_id | product_name | sale_price | moving_avg
   0001       | T恤          | 1000       | 750           // (1000 + 500) / 2
   0002       | 打孔器       | 500        | 1833.3333     // (1000 + 500 + 4000) / 3
   0003       | 运动T恤      | 4000       | 2500          // (500 + 4000 + 3000) / 3
   0004       | 菜刀         | 3000       | 4600          // (4000 + 3000 + 6800) / 3
   0005       | 高压锅       | 6800       | 3433.3333     // ...
   0006       | 叉子         | 500        | 2726.6667
   0007       | 擦菜板       | 880        | 493.3333
   0008       | 圆珠笔       | 100        | 490
*/

由执行结果可以看出:

  • 默认:聚合累计的数据
  • ROWS n PRECEDING:聚合当前行和前 n 行的数据
  • ROWS BETWEEN n PRECEDING AND m FOLLOWING:聚合当前行、前 n 行和后 m 行的数据

RANK、DENSE_RANK、ROW_NUMBER

-- 按照销售单价从低到高的顺序排序
   SELECT product_name, product_type, sale_price,
          RANK() OVER (ORDER BY sale_price) AS ranking,
          DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking,
          ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num
     FROM Product
 ORDER BY row_num;

执行结果为:

product_name product_type sale_price ranking dense_ranking row_num
圆珠笔 办公用品 100 1 1 1
叉子 厨房用具 500 2 2 2
打孔器 办公用品 500 2 2 3
擦菜板 厨房用具 880 4 3 4
T恤 衣服 1000 5 4 5
菜刀 厨房用具 3000 6 5 6
运动T恤 衣服 4000 7 6 7
高压锅 厨房用具 6800 8 7 8

由执行结果可以看出:

  • RANK():排序遇到相同值时,名次相同,但保留名次数目(擦菜板为 4)
  • DENSE_RANK():排序遇到相同值时,名次相同,不保留名次数目(擦菜板为 3)
  • ROW_NUMBER():排序遇到相同值时,名次也正常递增

事务

事务处理何时开始

  1. 每条 SQL 语句就是一个事务(自动提交模式)

MySQL 默认开启自动提交模式,要想查询自动提交模式是否开启:

-- 会话属性(当前会话生效)
SHOW SESSION VARIABLES LIKE 'autocommit';

-- 全局属性(当前 MySQL 实例生效,新会话会继承全局属性,但已建立的会话不受影响)
SHOW GLOBAL VARIABLES LIKE 'autocommit';

ACID 特性

DBMS 的事务具有原子性(Atomicity)一致性(Consistency)隔离性(Isolation)持久性(Durability)四种特性。通常将这四种特性的首字母结合起来,统称为 ACID 特性

  • 原子性(Atomicity)
    原子性是指在事务结束时,其中所包含的更新处理要么全部执行,要么完全不执行,也就是要么占有一切要么一无所有。
  • 一致性(Consistency)
    一致性指的是事务中包含的处理要满足数据库提前设置的约束,如主键约束或者 NOT NULL 约束等。
    一致性也称为完整性。
  • 隔离性(Isolation)
    隔离性指的是保证不同事务之间互不干扰的特性。
  • 持久性(Durability)
    持久性也可以称为耐久性,指的是在事务(不论是提交还是回滚)结束后,DBMS 能够保证该时间点的数据状态会被保存的特性。

笔试题

问答题

  1. 在使用聚合函数时,SELECT 子句中可以使用的元素种类:

    • 常数
    • 聚合函数
    • GROUP BY 子句中指定的列名(也就是聚合键)

    把聚合键之外的列名书写在 SELECT 子句之中是不允许的

    只有 MySQL 支持在 SELECT 子句中使用聚合键之外的列名,其它的 DBMS 均不支持这样的语法。因此不建议使用。

  2. 在 GROUP BY 子句中是否可以使用 SELECT 子句中 AS 关键字指定的别名?

    不可以。因为 SQL 在 DBMS 内部的执行顺序是:SELECT 子句在 GROUP BY 子句之后执行。在执行 GROUP BY 子句时, SELECT 子句中定义的别名,DBMS 还并不知道。

    只有 PostgreSQLMySQL 支持在 GROUP BY 中使用别名。但是这样的写法在其他 DBMS 中并不是通用的,因此不建议使用。

编程题

假设有如下一张表,名为 Product(DDL 见本文结尾):

product_id product_name product_type sale_price purchase_price regist_date
0001 T恤衫 衣服 1000 500 2009-09-20
0002 打孔器 办公用品 500 320 2009-09-11
0003 运动T恤 衣服 4000 2800 NULL
0004 菜刀 厨房用具 3000 2800 2009-09-20
0005 高压锅 厨房用具 6800 5000 2009-01-15
0006 叉子 厨房用具 500 NULL 2009-09-20
0007 擦菜板 厨房用具 880 790 2008-04-28
0008 圆珠笔 办公用品 100 NULL 2009-11-11

还有如下一张表,名为 ShopProduct(DDL 见本文结尾):

shop_id shop_name product_id quantity
000A 东京 0001 30
000A 东京 0002 50
000A 东京 0003 15
000B 名古屋 0002 30
000B 名古屋 0003 120
000B 名古屋 0004 20
000B 名古屋 0006 10
000B 名古屋 0007 40
000C 大阪 0003 20
000C 大阪 0004 50
000C 大阪 0006 90
000C 大阪 0007 70
000D 福冈 0001 100
-- 计算商品种类的个数
SELECT COUNT(DISTINCT product_type)
  FROM Product;
-- 结果为 3

-- 销售单价高于全部商品平均销售(2097.5)的商品
SELECT product_type, product_name, sale_price
  FROM Product
 WHERE sale_price > (SELECT AVG(sale_price)
                       FROM Product);
/* 结果为
   product_type | product_name | sale_price
   衣服         | 运动T恤      | 4000
   厨房用具     | 菜刀         | 3000
   厨房用具     | 高压锅       | 6800
*/

-- 各商品种类的平均销售单价
  SELECT product_type, AVG(sale_price)
    FROM Product
GROUP BY product_type;
/* 结果为
   product_type | AVG(sale_price)
   办公用品     | 300
   厨房用具     | 2795
   衣服         | 2500
*/

-- 销售单价高于商品种类平均销售销售单价的商品
SELECT product_type, product_name, sale_price
  FROM Product AS P1
 WHERE sale_price > (SELECT AVG(sale_price)
                       FROM Product AS P2
                      WHERE P1.product_type = P2.product_type);
/* 结果为
   product_type | product_name | sale_price
   办公用品     | 打孔器       | 500
   衣服         | 运动T恤      | 4000
   厨房用具     | 菜刀         | 3000
   厨房用具     | 高压锅       | 6800
*/

-- 000A 号店出售的商品名称和价格
SELECT product_name, sale_price
  FROM Product AS P 
       JOIN ShopProduct AS SP
         ON P.product_id = SP.product_id
            AND SP.shop_id = '000A';
-- ------------- 多解分隔线 -------------
SELECT product_name, sale_price
  FROM Product
 WHERE product_id IN (SELECT product_id
                        FROM ShopProduct
                       WHERE shop_id = '000A');
-- ------------- 多解分隔线 -------------
SELECT product_name, sale_price
  FROM Product AS P
 WHERE EXISTS (SELECT *
                 FROM ShopProduct AS SP
                WHERE P.product_id = SP.product_id
                      AND shop_id = '000A');
/* 结果为
   product_name | sale_price
   T恤          | 1000
   打孔器       | 500
   运动T恤      | 4000
*/

假设有如下一张表,名为 Skills(DDL 见本文结尾):

skill
Java
Oracle
UNIX

还有如下一张表,名为 EmpSkills(DDL 见本文结尾):

emp skill
平井 C++
平井 Oracle
平井 Perl
平井 PHP
平井 UNIX
渡来 Oracle
相田 C#
相田 Java
相田 Oracle
相田 UNIX
神崎 Java
神崎 Oracle
神崎 UNIX
若田部 Perl
-- 掌握 Skills 表中所有三个技术的员工名称
SELECT DISTINCT ES1.emp
  FROM EmpSkills AS ES1
 WHERE NOT EXISTS(SELECT S.*
                    FROM Skills AS S
                   WHERE NOT EXISTS(SELECT ES2.*
                                      FROM EmpSkills AS ES2
                                     WHERE S.skill = ES2.skill
                                           AND ES2.emp = ES1.emp));
-- -------------------------- 多解分隔线 --------------------------
SELECT DISTINCT emp
  FROM EmpSkills ES1
 WHERE NOT EXISTS(SELECT skill
                    FROM Skills
                  EXCEPT
                  SELECT skill
                    FROM EmpSkills ES2
                   WHERE EP1.emp = ES2.emp);
/* 结果为
   emp
   相田
   神崎
*/

附录

Product 表 DDL

-- MySQL 5.7

CREATE TABLE Product
(product_id      CHAR(4)      NOT NULL,
 product_name    VARCHAR(100) NOT NULL,
 product_type    VARCHAR(32)  NOT NULL,
 sale_price      INTEGER ,
 purchase_price  INTEGER ,
 regist_date     DATE ,
 PRIMARY KEY (product_id));
 
START TRANSACTION;

INSERT INTO Product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');

COMMIT;

ShopProduct 表 DDL

-- MySQL 5.7

-- DDL:创建表
CREATE TABLE ShopProduct
(shop_id    CHAR(4)       NOT NULL,
 shop_name  VARCHAR(200)  NOT NULL,
 product_id CHAR(4)       NOT NULL,
 quantity   INTEGER       NOT NULL,
 PRIMARY KEY (shop_id, product_id));

-- DML:插入数据

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A',  '东京',       '0001', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A',  '东京',       '0002', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A',  '东京',       '0003', 15);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',  '名古屋',  '0002', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',  '名古屋',  '0003', 120);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',  '名古屋',  '0004', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',  '名古屋',  '0006', 10);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',  '名古屋',  '0007', 40);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C',  '大阪',       '0003', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C',  '大阪',       '0004', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C',  '大阪',       '0006', 90);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C',  '大阪',       '0007', 70);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D',  '福冈',       '0001', 100);

COMMIT;

推荐阅读更多精彩内容