SQL语句（入门）

最新推荐文章于 2024-08-29 21:47:26 发布

紫苏Sun

最新推荐文章于 2024-08-29 21:47:26 发布

阅读量369

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/CSDNsue/article/details/89930659

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章目录

Chapter 0
Chapter 1
Chapter 5
- - 语法5-1　创建视图的CREATE VIEW语句
  - 语法5-2　删除视图的DROP VIEW语句
Chapter 6
- 子查询和视图
Chapter 7
Chapter 8

Chapter 0

D:\PostgreSQL\9.5\bin\psql.exe -U postgres -d shop
1830191026

Chapter 1

语法1-1　创建数据库的CREATE DATABASE语句

CREATE DATABASE <数据库名称>;

代码清单1-1  创建数据库shop的CREATE DATABASE语句
CREATE DATABASE shop;

语法1-2　创建表的CREATE TABLE语句

CREATE TABLE <表名>
（<列名1> <数据类型> <该列所需约束>，
<列名2> <数据类型> <该列所需约束>，
<列名3> <数据类型> <该列所需约束>，
<列名4> <数据类型> <该列所需约束>，
.
.
.
<该表的约束1>， <该表的约束2>，……）；

代码清单1-2　创建Product表的CREATE TABLE语句
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));

语法1-3　删除表时使用的DROP TABLE语句

DROP TABLE <表名>；

代码清单1-3　删除Product表
DROP TABLE Product;

语法1-4　添加列的ALTER TABLE语句

ALTER TABLE <表名> ADD COLUMN <列的定义>；

代码清单1-4　添加一列可以存储100位的可变长字符串的product_name_pinyin列
DB2 PostgreSQL MySQL
ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);
Oracle
ALTER TABLE Product ADD (product_name_pinyin VARCHAR2(100));
SQL Server
ALTER TABLE Product ADD product_name_pinyin VARCHAR(100);

语法1-5　删除列的ALTER TABLE语句

ALTER TABLE <表名> DROP COLUMN <列名>；

代码清单1-5　删除product_name_pinyin列
SQL Server DB2 PostgreSQL MySQL
ALTER TABLE Product DROP COLUMN product_name_pinyin;
Oracle
ALTER TABLE Product DROP (product_name_pinyin);

代码清单1-6　向Product表中插入数据的SQL语句
SQL Server PostgreSQL
-- 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;

代码清单1-A　变更表名
Oracle PostgreSQL
ALTER TABLE Poduct RENAME TO Product;
DB2
RENAME TABLE Poduct TO Product;
SQL Server
sp_rename 'Poduct', 'Product';
MySQL
RENAME TABLE Poduct to Product;

Chapter 5

语法5-1　创建视图的CREATE VIEW语句

CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS
<SELECT语句>
请大家一定不要省略第 2 行的关键字 AS。这里的 AS 与定义别
名时使用的 AS 并不相同，如果省略就会发生错误。虽然很容易混淆，但
是语法就是这么规定的，所以还是请大家牢记。

代码清单5-2　ProductSum视图
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;

代码清单5-3　使用视图
SELECT product_type, cnt_product
FROM ProductSum;

代码清单5-4　视图ProductSumJim
CREATE VIEW ProductSumJim (product_type, cnt_product)
AS
SELECT product_type, cnt_product
FROM ProductSum
WHERE product_type = '办公用品';
-- 确认创建好的视图
SELECT product_type, cnt_product
FROM ProductSumJim;

定义视图时不能用order by子句，这是因为视图和表一样，数据行都是没有顺序的。
-- 不能像这样定义视图 CREATE VIEW ProductSum (product_type, cnt_product) AS SELECT product_type, COUNT(*) FROM Product GROUP BY product_type ORDER BY product_type;

代码清单5-5　可以更新的视图
CREATE VIEW ProductJim (product_id, product_name, product_type, 
sale_price, purchase_price, regist_date)
AS
SELECT *
FROM Product
WHERE product_type = '办公用品';

使用CASCADE选项来删除关联视图
DROP VIEW ProductSum CASCADE;

语法5-2　删除视图的DROP VIEW语句

DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……)

Chapter 6

D:\PostgreSQL\9.5\bin\psql.exe -U postgres -d shop
1830191026

代码清单6-1　创建SampleMath表
-- DDL ：创建表
CREATE TABLE SampleMath
(m NUMERIC (10,3),
n INTEGER,
p INTEGER);
SQL Server PostgreSQL
-- DML ：插入数据
BEGIN TRANSACTION; ①
INSERT INTO SampleMath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7, 3);

子查询和视图

代码清单5-8　视图ProductSum和确认用的SELECT语句
-- 根据商品种类统计商品数量的视图
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;

代码清单5-9　子查询
SQL Server DB2 PostgreSQL MySQL
-- 在FROM子句中直接书写定义视图的SELECT语句
SELECT product_type, cnt_product
FROM ( SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type ) AS ProductSum;

代码清单5-10　尝试增加子查询的嵌套层数
SQL Server DB2 PostgreSQL MySQL
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;

语法6-1　ABS函数

ABS(数值)

代码清单6-2　计算数值的绝对值
SELECT m,
ABS(m) AS abs_col
FROM SampleMath;

语法6-2　MOD函数

MOD(被除数，除数)

只有SQL Server 不支持该函数，SQL Server用“%”计算余数

代码清单6-3　计算除法（n ÷ p）的余数
Oracle DB2 PostgreSQL MySQL
SELECT n, p,
MOD(n, p) AS mod_col
FROM SampleMath;

SQL Server
SELECT n, p,
n % p AS mod_col
FROM SampleMath;

语法6-3　ROUND函数

ROUND(对象数值，保留小数的位数)

SELECT m, n,
ROUND(m, n) AS round_col
FROM SampleMath;

代码清单6-5　创建SampleStr表
-- DDL ：创建表
CREATE TABLE SampleStr
(str1 VARCHAR(40),
str2 VARCHAR(40),
str3 VARCHAR(40));
SQL Server PostgreSQL
-- DML ：插入数据
BEGIN TRANSACTION; ①
INSERT INTO SampleStr (str1, str2, str3) VALUES ('opx' ,
'rt',NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc' ,
'def' ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('山田' ,
'太郎' ,'是我');

SELECT * FROM SampleStr;

语法6-4　||函数 (拼接)

字符串1||字符串2

|| 函数在 SQL Server 和 MySQL 中无法使用
SQL Server使用“+”运算符（函数）来连接字符串A。 MySQL使用CONCAT函数来完成字符串的拼接。
另外，在SQL Server 2012及其之后的版本中也可以使用CONCAT函数。

代码清单6-6　拼接两个字符串（str1+str2）
Oracle DB2 PostgreSQL
SELECT str1, str2,
str1 | | str2 AS str_concat
FROM SampleStr;

代码清单6-7　拼接三个字符串（str1+str2+str3）
Oracle DB2 PostgreSQL
SELECT str1, str2, str3,
str1 | | str2 | | str3 AS str_concat
FROM SampleStr
WHERE str1 = '山田';

语法6-5　LENGTH函数

LENGTH(字符串)

SQL Server使用LEN函数来计算字符串的长度。
使用 MySQL 中的
LENGTH 这样以字节为单位的函数进行计算时，“LENGTH( 山田 )”的返回结果是4。同样是 LENGTH 函数，不同 DBMS 的执行结果也不尽相同

代码清单6-8　计算字符串长度
Oracle DB2 PostgreSQL MySQL
SELECT str1,
LENGTH(str1) AS len_str
FROM SampleStr;

语法6-6　LOWER函数

LOWER(字符串)
UPPER(字符串) —转大写

代码清单6-9　大写转换为小写
SELECT str1,
LOWER(str1) AS low_str
FROM SampleStr
WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');

语法6-7　REPLACE函数

REPLACE(对象字符串，替换前的字符串，替换后的字符串)
REPLACE(A,B,C) —在A中找到B，把B替换成C

代码清单6-10　替换字符串的一部分
SELECT str1, str2, str3,
REPLACE(str1, str2, str3) AS rep_str
FROM SampleStr;

语法6-8　SUBSTRING函数（PostgreSQL/MySQL专用语法）

SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
特定的SQL

SQL Server（语法6-8b）。
语法6-8a　SUBSTRING函数（SQL Server专用语法）
SUBSTRING(对象字符串，截取的起始位置，截取的字符数)
Oracle和DB2
语法6-8b　SUBSTR函数（Oracle/DB2专用语法）
SUBSTR(对象字符串，截取的起始位置，截取的字符数)

代码清单6-11　截取出字符串中第3位和第4位的字符
PostgreSQL MySQL
SELECT str1,
SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
FROM SampleStr;

语法6-9　UPPER函数

UPPER(字符串) —只针对英文字母

语法6-10　CURRENT_DATE函数

CURRENT_DATE —返回SQL执行日期

特定的SQL

SQL Server使用如下的CURRENT_TIMESTAMP（后述）函数来获得当前日期。
SQL Server 使用CAST（后述）函数将CURRENT_TIMESTAMP转换为日期类型
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;

代码清单6-13　获得当前日期
PostgreSQL MySQL
SELECT CURRENT_DATE;

语法6-11　CURRENT_TIME函数

CURRENT_TIME —当前时间

代码清单6-14　取得当前时间
PostgreSQL MySQL
SELECT CURRENT_TIME;

语法6-12　CURRENT_TIMESTAMP函数

CURRENT_TIMESTAMP

代码清单6-15　取得当前日期和时间
SQL Server PostgreSQL MySQL
SELECT CURRENT_TIMESTAMP;

语法6-13　EXTRACT函数

EXTRACT(日期元素 FROM 日期)

代码清单6-16　截取日期元素
PostgreSQL MySQL
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;

语法6-14　CAST函数

CAST（转换前的值 AS 想要转换的数据类型）

代码清单6-17　将字符串类型转换为数值类型
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;
DB2
SELECT CAST('0001' AS INTEGER) AS int_col
FROM SYSIBM.SYSDUMMY1;

代码清单6-18　将字符串类型转换为日期类型
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;

语法6-15　COALESCE函数

COALESCE(数据1，数据2，数据3……) —将null转换为其他值

代码清单6-19　将NULL转换为其他值
SQL Server PostgreSQL MySQL
SELECT COALESCE(NULL, 1) AS col_1,
COALESCE(NULL, 'test', NULL) AS col_2,
COALESCE(NULL, NULL, '2009-11-01') AS col_3;
Oracle
SELECT COALESCE(NULL, 1) AS col_1,
COALESCE(NULL, 'test', NULL) AS col_2,
COALESCE(NULL, NULL, '2009-11-01') AS col_3
FROM DUAL;

代码清单6-20　使用SampleStr表中的列作为例子
SELECT COALESCE(str2, 'NULL')
FROM SampleStr;

代码清单6-21　创建SampleLike表
-- DDL ：创建表
CREATE TABLE SampleLike
( strcol VARCHAR(6) NOT NULL,
PRIMARY KEY (strcol));
SQL Server PostgreSQL
-- DML ：插入数据
BEGIN TRANSACTION; ①
INSERT INTO SampleLike (strcol) VALUES ('abcddd');
INSERT INTO SampleLike (strcol) VALUES ('dddabc');
INSERT INTO SampleLike (strcol) VALUES ('abdddc');
INSERT INTO SampleLike (strcol) VALUES ('abcdd');
INSERT INTO SampleLike (strcol) VALUES ('ddabc');
INSERT INTO SampleLike (strcol) VALUES ('abddc');
COMMIT;

代码清单6-22　使用LIKE进行前方一致查询
SELECT *
FROM SampleLike
WHERE strcol LIKE 'ddd%';

其中的 % 是代表“0 字符以上的任意字符串”的特殊符号，本例中代表“以 ddd 开头的所有字符串”
此外，我们还可以使用 _（下划线）来代替 %，与 % 不同的是，它代表了“任意 1 个字符”

代码清单6-23　使用LIKE进行中间一致查询
SELECT *
FROM SampleLike
WHERE strcol LIKE '%ddd%';

BETWEEN谓词——范围查询

代码清单6-27　选取销售单价为100～1000日元的商品
SELECT product_name, sale_price
FROM Product
WHERE sale_price BETWEEN 100 AND 1000;

如果不想哟临界值就的使用 <、>符号

代码清单6-28　选取出销售单价为101 ～999日元的商品
SELECT product_name, sale_price
FROM Product
WHERE sale_price > 100
AND sale_price < 1000;

IS NULL、 IS NOT NULL——判断是否为NULL

代码清单6-29　选取出进货单价（purchase_price）为NULL的商品
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IS NULL;

代码清单6-31　通过OR指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;

IN谓词——OR的简便用法

在使用IN 和 NOT IN 时是无法选取出 NULL 数据的

代码清单6-32　通过IN来指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IN (320, 500, 5000);

代码清单6-33　使用NOT IN进行查询时指定多个排除的进货单价进行查询
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price NOT IN (320, 500, 5000);

使用子查询作为IN谓词的参数

in 和 not in 谓词函数

代码清单6-34　创建ShopProduct（商店商品）表的CREATE TABLE语句
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));

代码清单6-35　向ShopProduct表中插入数据的INSERT语句
SQL Server PostgreSQL
BEGIN TRANSACTION; ①
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;

两表拼接
1．从 ShopProduct 表中选取出在大阪店（shop_id = ‘000C’）中销
售的商品（product_id）
2．从 Product 表中选取出上一步得到的商品（product_id）的销售单价
（sale_price）

代码清单6-36　使用子查询作为IN的参数
-- 取得“在大阪店销售的商品的销售单价”
SELECT product_name, sale_price
FROM Product
WHERE product_id IN (SELECT product_id
FROM ShopProduct
WHERE shop_id = '000C');

代码清单6-37　使用子查询作为NOT IN的参数
SELECT product_name, sale_price
FROM Product
WHERE product_id NOT IN (SELECT product_id
FROM ShopProduct
WHERE shop_id = '000A');

EXIST谓词

我们继续使用前一节“IN 和子查询”中的示例，使用 EXIST 选取出“大阪店（000C）在售商品（product_id）的销售单价（sale_price）”

代码清单6-38　使用   EXIST选取出“大阪店在售商品的销售单价”
SQL Server DB2 PostgreSQL MySQL
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);

由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号（shop_id）为 ‘000C’，商品（Product）表和商店商品（ShopProduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真（TRUE）。

代码清单6-39　这样的写法也能得到与代码清单6-38相同的结果
SQL Server DB2 PostgreSQL MySQL
SELECT product_name, sale_price
FROM Product AS P ①
WHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数
FROM ShopProduct AS SP ②
WHERE SP.shop_id = '000C'
AND SP.product_id = P.product_id);

not exists

代码清单6-40　使用NOT EXIST读取出“东京店在售之外的商品的销售单价”
SQL Server DB2 PostgreSQL MySQL
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);

语法6-16　搜索CASE表达式

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

else 语句可以省略，但是end 语句不可以省略；
由于 CASE 表达式是标准 SQL 所承认的功能，因此在任何 DBMS 中都可以
执行。但是，有些 DBMS 还提供了一些特有的 CASE 表达式的简化函数，例如
Oracle 中的 DECODE、MySQL 中的 IF 等。

代码清单6-41　通过CASE表达式将A ～ C的字符串加入到商品种类当中
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;

代码清单6-42　通常使用GROUP BY也无法实现行列转换
SELECT product_type,
SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type;

代码清单6-43　使用CASE表达式进行行列转换
-- 对按照商品种类计算出的销售单价合计值进行行列转换
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;

NOT IN 的参数中不能包含 NULL。不仅仅是指定 NULL 的情况，使用子查询作为 NOT IN 的参数时，该子查询的返回值也不能是 NULL。请大家一定要遵守这一规定。

Chapter 7

表的加法——UNION

代码清单7-1　创建表Product2（商品2）
CREATE TABLE Product2
(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));

代码清单7-2　将数据插入到表Product2（商品2）中
SQL Server PostgreSQL
BEGIN TRANSACTION; ①
INSERT INTO Product2 VALUES ('0001', 'T恤衫' ,'衣服', 1000, 500, 
'2008-09-20');
INSERT INTO Product2 VALUES ('0002', '打孔器', '办公用品', 500, 
320, '2009-09-11');
INSERT INTO Product2 VALUES ('0003', '运动T恤', '衣服', 4000, 
2800, NULL);
INSERT INTO Product2 VALUES ('0009', '手套', '衣服', 800, 500, NULL);
INSERT INTO Product2 VALUES ('0010', '水壶', '厨房用具', 2000, 
1700, '2009-09-20');
COMMIT;

UNION直接连接两个select语句

代码清单7-3　使用UNION对表进行加法运算
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2;

代码清单7-4　ORDER BY子句只在最后使用一次
SELECT product_id, product_name
FROM Product
WHERE product_type = '厨房用具'
UNION
SELECT product_id, product_name
FROM Product2
WHERE product_type = '厨房用具'
ORDER BY product_id;

包含重复行的集合运算——ALL选项

代码清单7-5　保留重复行
SELECT product_id, product_name
FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;

选取表中公共部分——INTERSECT

代码清单7-6　使用INTERSECT选取出表中公共部分
Oracle SQL Server DB2 PostgreSQL
SELECT product_id, product_name
FROM Product
INTERSECT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;

代码清单7-7　使用EXCEPT对记录进行减法运算
SQL Server DB2 PostgreSQL
SELECT product_id, product_name
FROM Product
EXCEPT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;

代码清单7-8　被减数和减数位置不同，得到的结果也不同
SQL Server DB2 PostgreSQL
-- 从Product2的记录中除去Product中的记录
SELECT product_id, product_name
FROM Product2
EXCEPT
SELECT product_id, product_name
FROM Product
ORDER BY product_id;

内联结——INNER JOIN

别名简化
INNER JOIN
联结键
ON 不可省略，可以有and和or

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子句结合使用

代码清单7-10　内联结和WHERE子句结合使用
SQL Server DB2 PostgreSQL MySQL
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';

外联结——OUTER JOIN

外联结允许有记录的某属性为空，内联结要求所有记录完整
外联结还有一点非常重要，那就是要把哪张表作为主表。指定主表的关键字是 LEFT 和 RIGHT
使用 LEFT 时 FROM 子句中写在左侧的表是主表，使用 RIGHT时右侧的表是主表。

代码清单7-11　将两张表进行外联结
SQL Server DB2 PostgreSQL MySQL
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;

3张以上的表的联结

代码清单7-13　创建InventoryProduct表并向其中插入数据
-- DDL ：创建表
CREATE TABLE InventoryProduct
( inventory_id CHAR(4) NOT NULL,
product_id CHAR(4) NOT NULL,
inventory_quantity INTEGER NOT NULL,
PRIMARY KEY (inventory_id, product_id));
SQL Server PostgreSQL
-- DML ：插入数据
BEGIN TRANSACTION; ①
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P001', '0001', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P001', '0002', 120);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P001', '0003', 200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P001', '0004', 3);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P001', '0005', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P001', '0006', 99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P001', '0007', 999);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P001', '0008', 200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P002', '0001', 10);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P002', '0002', 25);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P002', '0003', 34);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P002', '0004', 19);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P002', '0005', 99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P002', '0006', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P002', '0007', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) 
VALUES ('P002', '0008', 18);
COMMIT;
表示下一行接续本行，只是由于版面所限而换

FROM 中先INNER JOIN 两张表，再 ON,再INNER JOIN 另一张表，再ON

代码清单7-14　对3张表进行内联结
SQL Server DB2 PostgreSQL MySQL
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';

交叉联结——CROSS JOIN

代码清单7-15　将两张表进行交叉联结
SQL Server DB2 PostgreSQL MySQL
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
FROM ShopProduct AS SP CROSS JOIN Product AS P;

除法

代码清单7-A　创建Skills/EmpSkills表并插入数据
-- DDL ：创建表
CREATE TABLE Skills
(skill VARCHAR(32),
PRIMARY KEY(skill));
CREATE TABLE EmpSkills
(emp VARCHAR(32),
skill VARCHAR(32),
PRIMARY KEY(emp, skill));
SQL Server PostgreSQL
-- DML ：插入数据
BEGIN TRANSACTION; ①
INSERT INTO Skills VALUES('Oracle');
INSERT INTO Skills VALUES('UNIX');
INSERT INTO Skills VALUES('Java');
INSERT INTO EmpSkills VALUES('相田', 'Oracle');
INSERT INTO EmpSkills VALUES('相田', 'UNIX');
INSERT INTO EmpSkills VALUES('相田', 'Java');
INSERT INTO EmpSkills VALUES('相田', 'C#');
INSERT INTO EmpSkills VALUES('神崎', 'Oracle');
INSERT INTO EmpSkills VALUES('神崎', 'UNIX');
INSERT INTO EmpSkills VALUES('神崎', 'Java');
INSERT INTO EmpSkills VALUES('平井', 'UNIX');
INSERT INTO EmpSkills VALUES('平井', 'Oracle');
INSERT INTO EmpSkills VALUES('平井', 'PHP');
INSERT INTO EmpSkills VALUES('平井', 'Perl');
INSERT INTO EmpSkills VALUES('平井', 'C++');
INSERT INTO EmpSkills VALUES('若田部', 'Perl');
INSERT INTO EmpSkills VALUES('渡来', 'Oracle');
COMMIT;

CLALESCE将null转为其他值

Chapter 8

语法8-1　窗口函数

<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)
可以作为窗口函数的有：
① 能够作为窗口函数的聚合函数（SUM、 AVG、 COUNT、 MAX、 MIN）
② RANK、 DENSE_RANK、 ROW_NUMBER 等专用窗口函数

PARTITION BY 非必须

代码清单8-1　根据不同的商品种类，按照销售单价从低到高的顺序创建排序表
Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (PARTITION BY product_type
ORDER BY sale_price) AS ranking
FROM Product;

代码清单8-2　不指定PARTITION BY
Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product;

rank
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
dense_rank函数
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
row_number函数
赋予唯一的连续位次。
例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位

代码清单8-3　比较RANK、 DENSE_RANK、 ROW_NUMBER的结果
Oracle SQL Server DB2 PostgreSQL
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;

窗口函数只能写在SELECT语句之后，不能用于WHERE子句和GROUP BY 子句

代码清单8-4　将SUM函数作为窗口函数使用
Oracle SQL Server DB2 PostgreSQL
SELECT product_id, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;

代码清单8-5　将AVG函数作为窗口函数使用
Oracle SQL Server DB2 PostgreSQL
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id) AS current_avg
FROM Product;

计算移动平均

如果将条件中的数字变为“ROWS 5 PRECEDING”，就是“截止到之前 5 行”（最靠近的 6 行）的意思。
使用关键字 FOLLOWING（“之后”）替换 PRECEDING，就可以指定“截止到之后 ~ 行”作为框架了（图 8-3）。-

代码清单8-6　指定“最靠近的3行”作为汇总对象
Oracle SQL Server DB2 PostgreSQL
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg
FROM Product;

-将当前记录的前后行作为汇总对象

代码清单8-7　将当前记录的前后行作为汇总对象
Oracle SQL Server DB2 PostgreSQL
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;

代码清单8-8　无法保证如下SELECT语句的结果的排列顺序
Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product;

代码清单8-9　在语句末尾使用ORDER BY子句对结果进行排序
Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product
ORDER BY ranking;

GROUPING运算符

代码清单8-10　使用GROUP BY无法得到合计行
SELECT product_type, SUM(sale_price)
FROM Product
GROUP BY product_type;

常用合计（比较繁琐）

代码清单8-11　分别计算出合计行和汇总结果再通过UNION ALL进行连接
SELECT '合计' AS product_type, SUM(sale_price)
FROM Product
UNION ALL
SELECT product_type, SUM(sale_price)
FROM Product
GROUP BY product_type;

运算符包括三种
ROLLUP、CUBE、GROUPING SETS

代码清单8-12　使用ROLLUP同时得出合计和小计
Oracle SQL Server DB2 PostgreSQL
SELECT product_type, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type);