Skip to main content

SQL 参考

前提​

创建数据库​

CREATE DATABASE shop;

创建表​

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));

插入数据​

-- DML ：插入数据
BEGIN 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;

基础查询​

列查询​

-- 从Product表中输出3列
SELECT product_id,
       product_name,
       purchase_price
FROM Product;
product_idproduct_namepurchase_price
0001T恤衫500
0002打孔器320
0003运动T恤2800
0004菜刀2800
0005高压锅5000
0006叉子
0007擦菜板790
0008圆珠笔
-- 输出Product表中全部的列
SELECT *
FROM Product;
product_idproduct_nameproduct_typesale_pricepurchase_priceregist_date
0001T恤衫衣服10005002009-09-20
0002打孔器办公用品5003202009-09-11
0003运动T恤衣服40002800
0004菜刀厨房用具300028002009-09-20
0005高压锅厨房用具680050002009-01-15
0006叉子厨房用具5002009-09-20
0007擦菜板厨房用具8807902008-04-28
0008圆珠笔办公用品1002009-11-11
tip

星号 * 代表全部列的意思。

别名​

SELECT product_id AS id,
       product_name AS name,
       purchase_price AS price
FROM Product;
idnameprice
0001T恤衫500
0002打孔器320
0003运动T恤2800
0004菜刀2800
0005高压锅5000
0006叉子
0007擦菜板790
0008圆珠笔
-- 中文别名
SELECT product_id AS "商品编号",
       product_name AS "商品名称",
       purchase_price AS "进货单价"
FROM Product;
商品编号商品名称进货单价
0001T恤衫500
0002打孔器320
0003运动T恤2800
0004菜刀2800
0005高压锅5000
0006叉子
0007擦菜板790
0008圆珠笔

常数​

SELECT '商品' AS string,
       38 AS number,
       '2009-02-24' AS date,
       product_id,
       product_name
FROM Product;
stringnumberdateproduct_idproduct_name
商品382009-02-240001T恤衫
商品382009-02-240002打孔器
商品382009-02-240003运动T恤
商品382009-02-240004菜刀
商品382009-02-240005高压锅
商品382009-02-240006叉子
商品382009-02-240007擦菜板
商品382009-02-240008圆珠笔

DISTINCT 结果去重​

SELECT DISTINCT product_type
FROM Product;
product_type
厨房用具
衣服
办公用品
tip

在使用 DISTINCT 时， NULL 也被视为一类数据。

-- 对含有NULL数据的列使用DISTINCT关键字
SELECT DISTINCT purchase_price
FROM Product;
product_price
[NULL]
320
500
2800
5000
790
-- 在多列之前使用DISTINCT
SELECT DISTINCT product_type,
                regist_date
FROM Product;
product_typeregist_date
衣服2009-09-20
办公用品2009-09-11
办公用品2009-11-11
衣服
厨房用具2009-09-20
厨房用具2009-01-15
厨房用具2008-04-28
info

product_type 列为 厨房用具 同时 regist_date 列为 2009-09-20 的两条数据被合并成了一条。

caution

DISTINCT 关键字只能用在第一个列名之前。

条件过滤​

SELECT product_name,
       product_type
FROM Product
WHERE product_type = '衣服';
product_nameproduct_type
T恤衫衣服
运动T恤衣服
-- 也可以不选取出作为查询条件的列
SELECT product_name
FROM Product
WHERE product_type = '衣服';
product_name
T恤衫
运动T恤
caution

SQL 中子句的书写顺序是固定的，不能随意更改。 WHERE 子句必须紧跟在 FROM 子句之后。

算术运算符​

SELECT product_name,
       sale_price,
       sale_price * 2 AS "sale_price_x2"
FROM Product;
product_namesale_pricesale_price_x2
T恤衫10002000
打孔器5001000
运动T恤40008000
菜刀30006000
高压锅680013600
叉子5001000
擦菜板8801760
圆珠笔100200
caution

所有包含 NULL 的计算，结果肯定是 NULL。

比较运算符​

SELECT product_name,
       product_type
FROM Product
WHERE sale_price = 500;
product_nameproduct_type
打孔器办公用品
叉子厨房用具
SELECT product_name,
       product_type
FROM Product
WHERE sale_price <> 500;
product_nameproduct_type
T恤衫衣服
运动T恤衣服
菜刀厨房用具
高压锅厨房用具
擦菜板厨房用具
圆珠笔办公用品
info

常见比较运算符：

运算符含义
=和~相等
<>和~不相等
>=大于等于~
>大于~
<=小于等于~
<小于~
caution

希望选取 NULL 记录时，需要在条件表达式中使用 IS NULL 运算符。希望选取不是 NULL 的记录时，需要在条件表达式中使用 IS NOT NULL 运算符。

逻辑运算符​

-- NOT 运算符
SELECT product_name,
       product_type,
       sale_price
FROM Product
WHERE NOT sale_price >= 1000;
product_nameproduct_typesale_price
打孔器办公用品500
叉子厨房用具500
擦菜板厨房用具880
圆珠笔办公用品100
-- AND 运算符
SELECT product_name,
       purchase_price
FROM Product
WHERE product_type = '厨房用具'
  AND sale_price >= 3000;
product_namepurchase_price
菜刀2800
高压锅5000
-- OR 运算符
SELECT product_name,
       purchase_price
FROM Product
WHERE product_type = '厨房用具'
  OR sale_price >= 3000;
product_namepurchase_price
运动T恤2800
菜刀2800
高压锅5000
叉子
擦菜板790
tip

AND 运算符优先于 OR 运算符。

-- 通过使用括号让OR运算符先于AND运算符执行
SELECT product_name,
       product_type,
       regist_date
FROM Product
WHERE product_type = '办公用品'
  AND (regist_date = '2009-09-11'
       OR regist_date = '2009-09-20');
product_nameproduct_typeregist_date
打孔器办公用品2009-09-11

聚合​

聚合函数​

-- 计算全部数据的行数
SELECT COUNT(*)
FROM Product;
count
8
-- 计算销售单价的合计值
SELECT SUM(sale_price)
FROM Product;
sum
16780
SELECT SUM(sale_price),
       SUM(purchase_price)
FROM Product;
sumsum
1678012210
tip

所有的聚合函数，如果以列名为参数，那么在计算之前就已经把 NULL 排除在外了。因此，无论有多少个 NULL 都会被无视。

-- 计算销售单价的平均值
SELECT AVG(sale_price)
FROM Product;
avg
2097.5000000000000000
SELECT AVG(sale_price),
       AVG(purchase_price)
FROM Product;
avgavg
2097.50000000000000002035.0000000000000000
-- 计算销售单价的最大值和进货单价的最小值
SELECT MAX(sale_price),
       MIN(purchase_price)
FROM Product;
maxmin
6800320
tip

MAX/MIN 函数几乎适用于所有数据类型的列。 SUM/AVG 函数只适用于数值类型的列。

聚合去重​

-- 计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
FROM Product;
count
3
caution

这时 DISTINCT 必须写在括号中。

-- 使不使用DISTINCT时的动作差异（ SUM函数）
SELECT SUM(sale_price),
       SUM(DISTINCT sale_price)
FROM Product;
sumsum
1678016280
tip

所有的聚合函数都可以使用 DISTINCT。

分组​

-- 按照商品种类统计数据行数
SELECT product_type,
       COUNT(*)
FROM Product
GROUP BY product_type;
product_typecount
衣服2
办公用品2
厨房用具4
子句的书写顺序
  1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
-- 同时使用WHERE子句和GROUP BY子句
SELECT purchase_price,
       COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY purchase_price;
purchase_pricecount
5001
28001
GROUP BY 和 WHERE 并用时 SELECT 语句的执行顺序

FROM → WHERE → GROUP BY → SELECT

聚合条件​

-- 从按照商品种类进行分组后的结果中，取出“包含的数据行数为2行”的组
SELECT product_type,
       COUNT(*)
FROM Product
GROUP BY product_type HAVING COUNT(*) = 2;
product_typecount
衣服2
办公用品2
使用 HAVING 子句时 SELECT 语句的书写顺序

SELECT → FROM → WHERE → GROUP BY → HAVING

排序​

-- 按照销售单价由低到高（升序）进行排列
SELECT product_id,
       product_name,
       sale_price,
       purchase_price
FROM Product
ORDER BY sale_price;
product_idproduct_namesale_pricepurchase_price
0008圆珠笔100
0006叉子500
0002打孔器500320
0007擦菜板880790
0001T恤衫1000500
0004菜刀30002800
0003运动T恤40002800
0005高压锅68005000
书写顺序
  1. SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 → 5.HAVING 子句 → 6. ORDER BY 子句
-- 降序
SELECT product_id,
       product_name,
       sale_price,
       purchase_price
FROM Product
ORDER BY sale_price DESC;
product_idproduct_namesale_ pricepurchase_ price
0005高压锅68005000
0003运动T恤40002800
0004菜刀30002800
0001T恤衫1000500
0007擦菜板880790
0002打孔器500320
0006叉子500
0008圆珠笔100
-- 指定多个排序键
SELECT product_id,
       product_name,
       sale_price,
       purchase_price
FROM Product
ORDER BY sale_price,
         product_id;
product_idproduct_namesale_pricepurchase_price
0008圆珠笔100
0002打孔器500320
0006叉子500
0007擦菜板880790
0001T恤衫1000500
0004菜刀30002800
0003运动T恤40002800
0005高压锅68005000
tip

ORDER BY子句中可以使用列的别名。

SELECT product_id AS id,
       product_name,
       sale_price AS sp,
       purchase _price
FROM Product
ORDER BY sp,
         id;
idproduct_namesppurchase_price
0008圆珠笔100
0002打孔器500320
0006叉子500
0007擦菜板880790
0001T恤衫1000500
0004菜刀30002800
0003运动T恤40002800
0005高压锅68005000
语句执行顺序

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

-- SELECT子句中未包含的列也可以在ORDER BY子句中使用
SELECT product_name,
       sale_price,
       purchase_price
FROM Product
ORDER BY product_id;
product_namesale_pricepurchase_price
T恤衫1000500
打孔器500320
运动T恤40002800
菜刀30002800
高压锅68005000
叉子500
擦菜板880790
圆珠笔100
-- ORDER BY子句中也可以使用聚合函数
SELECT product_type,
       COUNT(*)
FROM Product
GROUP BY product_type
ORDER BY COUNT(*);
product_typecount
衣服2
办公用品2
厨房用具4

视图​

-- 创建视图
CREATE VIEW ProductSum (product_type, cnt_product) AS
SELECT product_type,
       COUNT(*)
FROM Product
GROUP BY product_type;

-- 使用视图
SELECT product_type,
       cnt_product
FROM ProductSum;
product_typecnt_product
衣服2
办公用品2
厨房用具4
caution

定义视图时不要使用ORDER BY子句。

-- 删除视图
DROP VIEW ProductSum;

子查询​

SELECT product_type,
       cnt_product
FROM
  (SELECT product_type,
          COUNT(*) AS cnt_product
   FROM Product
   GROUP BY product_type) AS ProductSum;
product_typecnt_product
衣服2
办公用品2
厨房用具4
info

子查询作为内层查询会首先执行。

-- 增加子查询的嵌套层数
SELECT product_type,
       cnt_product
FROM
  (SELECT *
   FROM
     (SELECT product_type,
             COUNT(*) AS cnt_product
      FROM Product
      GROUP BY product_type) AS ProductSum
   WHERE cnt_product = 4) AS ProductSum2;
product_typecnt_product
厨房用具4

标量子查询​

标量子查询则有一个特殊的限制，那就是必须而且只能返回 1 行 1 列的结果。

info

标量子查询就是返回单一值的子查询。

-- 计算平均销售单价的标量子查询
SELECT AVG(sale_price)
FROM Product;
avg
2097.5000000000000000
SELECT product_id,
       product_name,
       sale_price
FROM Product
WHERE sale_price >
    (SELECT AVG(sale_price)
     FROM Product);
product_idproduct_namesale_price
0003运动T恤4000
0004菜刀3000
0005高压锅6800
标量子查询的书写位置

标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说， 能够使用常数或者列名的地方，无论是 SELECT 子句、 GROUP BY 子句、 HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

-- 在SELECT子句中使用标量子查询
SELECT product_id,
       product_name,
       sale_price,
  (SELECT AVG(sale_price)
   FROM Product) AS avg_price
FROM Product;
product_idproduct_namesale_priceavg_price
0001T恤衫10002097.5000000000000000
0002打孔器5002097.5000000000000000
0003运动T恤40002097.5000000000000000
0004菜刀30002097.5000000000000000
0005高压锅68002097.5000000000000000
0006叉子5002097.5000000000000000
0007擦菜板8802097.5000000000000000
0008圆珠笔1002097.5000000000000000
-- 在HAVING子句中使用标量子查询
SELECT product_type,
       AVG(sale_price)
FROM Product
GROUP BY product_type HAVING AVG(sale_price) >
  (SELECT AVG(sale_price)
   FROM Product);
product_typeavg
衣服2500.0000000000000000
厨房用具2795.0000000000000000
caution

标量子查询绝对不能返回多行结果。

关联子查询​

-- 通过关联子查询按照商品种类对平均销售单价进行比较
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
     GROUP BY product_type);
product_typeproduct_namesale_price
办公用品打孔器500
衣服运动T恤4000
厨房用具菜刀3000
厨房用具高压锅6800

函数​

算术函数​

-- 表准备
SELECT * FROM SampleMath;
mnp
500.0000
-180.0000
73
52
4
8.0003
2.2701
5.5552
1
8.760

ABS 绝对值​

-- 计算数值的绝对值
SELECT m,
       ABS(m) AS abs_col
FROM SampleMath;
mabs_col
500.000500.000
-180.000180.000
8.0008.000
2.2702.270
5.5555.555
8.7608.760

MOD 求余​

-- 计算除法（ n ÷ p）的余数
SELECT n,
       p,
       MOD(n, p) AS mod_col
FROM SampleMath;
npmod_col
0
0
731
521
4
3
1
2
1

ROUND 四舍五入​

-- 对m列的数值进行n列位数的四舍五入处理
SELECT m,
       n,
       ROUND(m, n) AS round_col
FROM SampleMath;
mnround_col
500.0000500
-180.0000-180
7
5
4
8.000
2.27012.3
5.55525.56
1
8.760
info

四舍五入在英语中称为 round。如果指定四舍五入的位数为 1，那么就会对小数点第 2 位进行四舍五入处理。如果指定位数为 2，那么就会对第 3 位进行四舍五入处理。

字符串函数​

-- 表准备
SELECT * FROM SampleStr;
str1str2str3
opxrt
abcdef
山田太郎是我
aaa
xyz
@!#$%
ABC
aBC
abc太郎abcABC
abcdefabcabcABC
micmiciI

|| 拼接​

-- 拼接两个字符串（ str1+str2）
SELECT str1,
       str2,
       str1 || str2 AS str_concat
FROM SampleStr;
str1str2str_concat
opxrtopxrt
abcdefabcdef
山田太郎山田太郎
aaa
xyz
@!#$%
ABC
aBC
abc太郎abcabc太郎abc
abcdefabcabcabcdefabcabc
micmicimicmaci
caution

进行字符串拼接时，如果其中包含 NULL，那么得到的结果也是 NULL。

-- 拼接三个字符串（ str1+str2+str3）
SELECT str1,
       str2,
       str3,
       str1 || str2 || str3 AS str_concat
FROM SampleStr
WHERE str1 = '山田';
str1str2str3str_concat
山田太郎是我山田太郎是我
tip

MySQL 使用 CONCAT 函数来完成字符串的拼接。

SELECT str1,
       str2,
       str3,
       CONCAT(str1, str2, str3) AS str_concat
FROM SampleStr;

LENGTH 字符串长度​

-- 计算字符串长度
SELECT str1,
       LENGTH(str1) AS len_str
FROM SampleStr;
str1len_str
opx3
abc3
山田2
aaa3
@!#$%5
ABC3
aBC3
abc太郎5
abcdefabc9
micmic6

LOWER 小写转换​

-- 大写转换为小写
SELECT str1,
       LOWER(str1) AS low_str
FROM SampleStr
WHERE str1 IN ('ABC',
               'aBC',
               'abc',
               '山田');
str1low_str
abcabc
山田山田
ABCabc
aBCabc
tip

UPPER 就是大写转换函数。

REPLACE 字符串的替换​

-- 替换字符串的一部分
SELECT str1,
       str2,
       str3,
       REPLACE(str1, str2, str3) AS rep_str
FROM SampleStr;
str1str2str3rep_str
opxrt
abcdef
山田太郎是我山田
aaa
xyz
@!#$%
ABC
aBC
abc太郎abcABCABC太郎
abcdefabcabcABCABCdefABC
micmiciImIcmIc

SUBSTRING 字符串的截取​

SELECT str1,
       SUBSTRING(str1
                 FROM 3
                 FOR 2) AS sub_str
FROM SampleStr;
str1sub_str
opxx
abcc
山田
aaaa
@!#$%#$
ABCC
aBCC
abc太郎c太
abcdefabccd
micmiccm
tip

现在只有 PostgreSQL 和 MySQL 支持该语法。

日期函数​

CURRENT_DATE 当前日期​

SELECT CURRENT_DATE;
current_date
2022-07-08

CURRENT_TIME 当前时间​

SELECT CURRENT_TIME;
current_time
23:53:50 +0800

CURRENT_TIMESTAMP 当前日期和时间​

SELECT CURRENT_TIMESTAMP;
current_timestamp
2022-07-08 23:51:36.236 +0800

EXTRACT 截取日期元素​

SELECT CURRENT_TIMESTAMP,
       EXTRACT(YEAR
               FROM CURRENT_TIMESTAMP) AS YEAR,
       EXTRACT(MONTH
               FROM CURRENT_TIMESTAMP) AS MONTH,
       EXTRACT(DAY
               FROM CURRENT_TIMESTAMP) AS DAY,
       EXTRACT(HOUR
               FROM CURRENT_TIMESTAMP) AS hour,
       EXTRACT(MINUTE
               FROM CURRENT_TIMESTAMP) AS MINUTE,
       EXTRACT(SECOND
               FROM CURRENT_TIMESTAMP) AS SECOND;
current_timestampyearmonthdayhourminutesecond
2022-07-08 23:55:35.709 +0800202278235535.709275

转换函数​

CAST 类型转换​

-- 将字符串类型转换为数值类型
-- SQL Server PostgreSQL
SELECT CAST('0001' AS INTEGER) AS int_col;
-- MySQL
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
-- Oracle
SELECT CAST('0001' AS INTEGER) AS int_col FROM DUAL;
int_col
1
-- 将字符串类型转换为日期类型
-- SQL Server PostgreSQL MySQL
SELECT CAST('2009-12-14' AS DATE) AS date_col;
-- Oracle
SELECT CAST('2009-12-14' AS DATE) AS date_col FROM DUAL;
date_col
2009-12-14

谓词​

LIKE​

-- 数据
SELECT * FROM SAMPLELIKE;
strcol
abcddd
dddabc
abdddc
abcdd
ddabc
abddc
-- 以 ddd 开头的所有字符串
SELECT *
FROM SampleLike
WHERE strcol LIKE 'ddd%';
strcol
dddabc
tip

其中的 % 是代表 “0 字符以上的任意字符串” 的特殊符号，本例中代表 “以 ddd 开头的所有字符串”。

-- 包含 ddd 的字符串
SELECT *
FROM SampleLike
WHERE strcol LIKE '%ddd%';
strcol
abcddd
dddabc
abdddc
-- 以字符串 ddd 结尾的记录
SELECT *
FROM SampleLike
WHERE strcol LIKE '%ddd';
strcol
abcddd
tip

_（下划线）代表了 “任意 1 个字符”。

-- 选取出 strcol 列的值为 abc + 任意 2 个字符的记录
SELECT *
FROM SampleLike
WHERE strcol LIKE 'abc__';
strcol
abcdd
-- 查询 abc + 任意 3 个字符的字符串
SELECT *
FROM SampleLike
WHERE strcol LIKE 'abc___';
strcol
abcddd

BETWEEN​

-- 选取销售单价为100～ 1000日元的商品
SELECT product_name,
       sale_price
FROM Product
WHERE sale_price BETWEEN 100 AND 1000;
product_namesale_price
T恤衫1000
打孔器500
叉子500
擦菜板880
圆珠笔100
tip

BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值；如果不想让结果中包含临界值，那就必须使用 < 和 >。

是否为NULL​

-- 选取出进货单价（ purchase_price）为 NULL 的商品
SELECT product_name,
       purchase_price
FROM Product
WHERE purchase_price IS NULL;
product_namepurchase_price
叉子
圆珠笔
-- 选取进货单价（ purchase_price）不为NULL的商品
SELECT product_name,
       purchase_price
FROM Product
WHERE purchase_price IS NOT NULL;
product_namepurchase_price
T恤衫500
打孔器320
运动T恤2800
菜刀2800
高压锅5000
擦菜板790

IN​

--  通过IN来指定多个进货单价进行查询
SELECT product_name,
       purchase_price
FROM Product
WHERE purchase_price IN (320,
                         500,
                         5000);
product_namepurchase_price
T恤衫500
打孔器320
高压锅5000
-- 使用NOT IN进行查询时指定多个排除的进货单价进行查询
SELECT product_name,
       purchase_price
FROM Product
WHERE purchase_price NOT IN (320,
                             500,
                             5000);
product_namepurchase_price
运动T恤2800
菜刀2800
擦菜板790

IN和子查询​

-- 商店商品数据
SELECT * FROM SHOPPRODUCT;
shop_idshop_nameproduct_idquantity
000A东京000130
000A东京000250
000A东京000315
000B名古屋000230
000B名古屋0003120
000B名古屋000420
000B名古屋000610
000B名古屋000740
000C大阪000320
000C大阪000450
000C大阪000690
000C大阪000770
000D福冈0001100
-- 在大阪店销售的商品的销售单价
SELECT product_name,
       sale_price
FROM Product
WHERE product_id IN
    (SELECT product_id
     FROM ShopProduct
     WHERE shop_id = '000C');
product_namesale_price
叉子500
运动T恤4000
菜刀3000
擦菜板880
-- 在东京店（000A）以外销售的商品
SELECT product_name,
       sale_price
FROM Product
WHERE product_id NOT IN
    (SELECT product_id
     FROM ShopProduct
     WHERE shop_id = '000A');
product_namesale_price
菜刀3000
高压锅6800
叉子500
擦菜板880
圆珠笔100

EXIST​

-- 大阪店在售商品的销售单价
SELECT product_name,
       sale_price
FROM Product AS P
WHERE EXISTS
    (SELECT *
     FROM ShopProduct AS SP
     WHERE SP.shop_id = '000C'
       AND SP.product_id = P.product_id);
product_namesale_price
叉子500
运动T恤4000
菜刀3000
擦菜板880
tip

通常指定关联子查询作为EXIST的参数。

-- 东京店在售之外的商品的销售单价
SELECT product_name,
       sale_price
FROM Product AS P
WHERE NOT EXISTS
    (SELECT *
     FROM ShopProduct AS SP
     WHERE SP.shop_id = '000A'
       AND SP.product_id = P.product_id);
product_namesale_price
菜刀3000
高压锅6800
叉子500
擦菜板880
圆珠笔100

CASE表达式​

SELECT product_name,
       CASE
           WHEN product_type = '衣服' THEN 'A ： ' || product_type
           WHEN product_type = '办公用品' THEN 'B ： ' || product_type
           WHEN product_type = '厨房用具' THEN 'C ： ' || product_type
           ELSE NULL
       END AS abc_product_type
FROM product;
product_nameabc_product_type
T恤衫A ：衣服
打孔器B ：办公用品
运动T恤A ：衣服
菜刀C ：厨房用具
高压锅C ：厨房用具
叉子C ：厨房用具
擦菜板C ：厨房用具
圆珠笔B ：办公用品
简写
-- 使用简单CASE表达式的情况

SELECT product_name,
       CASE product_type
           WHEN '衣服' THEN 'A ： ' || product_type
           WHEN '办公用品' THEN 'B ： ' || product_type
           WHEN '厨房用具' THEN 'C ： ' || product_type
           ELSE NULL
       END AS abc_product_type
FROM Product;

集合运算​

UNION​

-- 使用UNION对表进行加法运算
SELECT product_id,
       product_name
FROM Product
UNION
SELECT product_id,
       product_name
FROM Product2;
product_idproduct_name
0001T恤衫
0002打孔器
0003运动T恤
0004菜刀
0005高压锅
0006叉子
0007擦菜板
0008圆珠笔
0009手套
0010水壶
info

UNION 集合运算符会除去重复的记录。

info

通过 UNION 进行并集运算时可以使用任何形式的 SELECT 语句，之前学过的 WHERE、 GROUP BY、 HAVING 等子句都可以使用。但是ORDER BY 只能在最后使用一次。

ALL 选项​

-- 保留重复行
SELECT product_id,
       product_name
FROM Product
UNION ALL
SELECT product_id,
       product_name
FROM Product2;
product_idproduct_name
0001T恤衫
0002打孔器
0003运动T恤
0004菜刀
0005高压锅
0006叉子
0007擦菜板
0008圆珠笔
0001T恤衫
0002打孔器
0003运动T恤
0009手袋
0010水壶
tip

在集合运算符中使用 ALL 选项，可以保留重复行。

INTERSECT​

-- 选取出表中公共部分
SELECT product_id,
       product_name
FROM Product 
INTERSECT
SELECT product_id,
       product_name
FROM Product2
ORDER BY product_id;
product_idproduct_name
0001T恤衫
0002打孔器
0003运动T恤

EXCEPT​

-- 减法运算
SELECT product_id,
       product_name
FROM Product 
EXCEPT
SELECT product_id,
       product_name
FROM Product2
ORDER BY product_id;
product_idproduct_name
0004菜刀
0005高压锅
0006叉子
0007擦菜板
0008圆珠笔

联结​

INNER JOIN​

SELECT SP.shop_id,
       SP.shop_name,
       SP.product_id,
       P.product_name,
       P.sale_price
FROM ShopProduct AS SP
INNER JOIN Product AS P ON SP.product_id = P.product_id;
shop_idshop_nameproduct_idproduct_namesale_price
000A东京0002打孔器500
000A东京0003运动T恤4000
000A东京0001T恤衫1000
000B名古屋0007擦菜板880
000B名古屋0002打孔器500
000B名古屋0003运动T恤4000
000B名古屋0004菜刀3000
000B名古屋0006叉子500
000C大阪0007擦菜板880
000C大阪0006叉子500
000C大阪0003运动T恤4000
000C大阪0004菜刀3000
000D福冈0001T恤衫1000
-- 内联结和WHERE子句结合使用
SELECT SP.shop_id,
       SP.shop_name,
       SP.product_id,
       P.product_name,
       P.sale_price
FROM ShopProduct AS SP
INNER JOIN Product AS P ON SP.product_id = P.product_id
WHERE SP.shop_id = '000A';
shop_idshop_nameproduct_idproduct_namesale_price
000A东京0001T恤衫1000
000A东京0002打孔器500
000A东京0003运动T恤4000

OUTER JOIN​

SELECT SP.shop_id,
       SP.shop_name,
       SP.product_id,
       P.product_name,
       P.sale_price
FROM ShopProduct AS SP
RIGHT OUTER JOIN Product AS P ON SP.product_id = P.product_id;
shop_idshop_nameproduct_idproduct_namesale_price
000A东京0002打孔器500
000A东京0003运动T恤4000
000A东京0001T恤衫1000
000B名古屋0006叉子500
000B名古屋0002打孔器500
000B名古屋0003运动T恤4000
000B名古屋0004菜刀3000
000B名古屋0007擦菜板880
000C大阪0006叉子500
000C大阪0007擦菜板880
000C大阪0003运动T恤4000
000C大阪0004菜刀3000
000D福冈0001T恤衫1000
0005高压锅6800
0008圆珠笔100
tip

内联结只能选取出同时存在于两张表中的数据，相反，对于外联结来说，只要数据存在于某一张表当中，就能够读取出来。

tip

外联结还有一点非常重要，那就是要把哪张表作为主表。最终的结果中会包含主表内所有的数据。指定主表的关键字是 LEFT 和 RIGHT。

多表联结​

-- 对3张表进行内联结
SELECT SP.shop_id,
       SP.shop_name,
       SP.product_id,
       P.product_name,
       P.sale_price,
       IP.inventory_quantity
FROM ShopProduct AS SP
INNER JOIN Product AS P ON SP.product_id = P.product_id
INNER JOIN InventoryProduct AS IP ON SP.product_id = IP.product_id
WHERE IP.inventory_id = 'P001';
shop_idshop_nameproduct_idproduct_namesale_priceinventory_quantity
000A东京0002打孔器500120
000A东京0003运动T恤4000200
000A东京0001T恤衫10000
000B名古屋0007擦菜板880999
000B名古屋0002打孔器500120
000B名古屋0003运动T恤4000200
000B名古屋0004菜刀30003
000B名古屋0006叉子50099
000C大阪0007擦菜板880999
000C大阪0006叉子50099
000C大阪0003运动T恤4000200
000C大阪0004菜刀30003
000D福冈0001T恤衫10000