《SQL 基础教程》笔记

发表于 2020-03-17 更新于 2020-04-21 分类于计算机科学阅读次数评论数

SQL基础教程 / (日) MICK 著 ; 孙淼, 罗勇译. -- 2版 -- 北京 : 人民邮电出版社, 2017.6
（图灵程序设计丛书）
ISBN 978-7-115-45502-4

下载 SQL基础教程（第2版）.pdf

第0章　绪论——搭建SQL的学习环境

切断与 SQL 的连接

\q
exit
关闭窗口

第1章　数据库和SQL

1-1　数据库是什么

数据库：Database, DB
数据库管理系统：Database Management System, DBMS
关系数据库管理系统：Relational Database Management System, RDBMS

大到银行账户的管理，小到手机的电话簿，社会的所有系统中都有数据库的身影。

我们通过计算机管理数据的时候，通常使用文本文件或者 Excel 那样的电子制表软件就可以完成了，非常简单，但也有不足：

无法多人共享数据：当某个用户购买商品的时候，其他用户就无法购买了
无法提供操作大量数据所需的格式：不能瞬间从上百万数据中获取想要的数据
实现读写自动化需要编程能力
无法应对突发事故

1-2　数据库的结构

二维表：类似 Excel 工作表、由行和列组成，用于管理数据。
字段：表的列，它代表了保存在表中的数据项目。
记录：表的行，它相当于一条数据。
单元格：行和列交汇的方格，一个单元格中只能输入一个数据。

关系数据库以行为单位读写数据。

为了防止重要数据被窃读或篡改，RDBMS 只允许注册用户接触数据库。这里的用户并不是指 Windows 等操作系统的注册用户，而是只能用于 RDBMS 的用户。RDBMS 允许注册多个用户。

1-3　SQL概要

SQL语句及其种类

SQL 用关键字、表名、列名等组合而成的一条语句来描述操作的内容。

关键字：指那些含义或使用方法已事先定义好的英语单词，存在包含“对表进行查询”或者“参考这个表”等各种意义的关键字。

SQL 根据功能不同可以分为三类：
DDL：Data Definition Language，数据定义语言。用来创建或者删除存储数据用的数据库以及数据库中的表等对象。

CREATE：创建数据库和表等对象
DROP：删除数据库和表等对象
ALTER：修改数据库和表等对象的结构

DML：Data Manipulation Language，数据操纵语言。用来查询或者变更表中的记录。

SELECT：查询表中的数据
INSERT：向表中插入新数据
UPDATE：更新表中的数据
DELETE：删除表中的数据

DCL：Data Control Language，数据控制语言。用来确认或者取消对数据的变更。除此之外，还可以对 RDBMS 的用户有权限操作数据库中的对象（数据库表等）进行设定。

COMMIT：确认对数据库中的数据进行的变更
ROLLBACK：取消对数据库中的数据进行的变更
GRANT：赋予用户操作权限
REVOKE：取消用户的操作权限

实际使用的 SQL 语句有 90% 属于 DML，本书以 DML 为中心讲解。

SQL的基本书写规则

SQL 语句要以分号（;）结尾。
SQL 语句不区分大小写，但是插入到表中的数据是区分大小写的。为便于理解，本书使用以下规则书写 SQL 语句：

关键字大写
表名的首字母大写
其余（列名等）小写

常数的书写方式是固定的。在 SQL 语句中直接书写的字符串、日期或者数字等称为常数。书写方式如下所示：

SQL 语句中含有字符串的时候，需要像’abc’这样，使用单引号（’）将字符串括起来，用来标识这是一个字符串。
SQL 语句中含有日期的时候，同样需要使用单引号将其括起来。日期的格式有很多种（’26 Jan 2010’ 或者’10/01/26’等），本书统一使用’2010-01-26’这种’年-月-日’的格式。
在 SQL 语句中书写数字的时候，不需要使用任何符号标识，直接写成 1000 这样的数字即可。

单词需要用半角空格或者换行来分隔，不能使用全角空格作为单词的分隔符。

1-4　表的创建

数据库和表的创建

创建数据库：CREATE DATABASE <数据库名称>;
创建表：

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

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 个字节来表示（根据字符的种类和表现方式有所不同）。

学习 SQL 的时候，使用最基本的数据类型就足够了：

INTEGER 型：用来指定存储整数的列的数据类型（数字型），不能存储小数。
CHAR 型：CHARACTER（字符）的缩写，是用来指定存储字符串的列的数据类型（字符型）。可以像CHAR(10)这样，指定该列可以存储的字符串的长度（最大长度）;字符串以定长字符串的形式存储在被指定为 CHAR 型的列中。所谓定长字符串，就是当列中存储的字符串长度达不到最大长度的时候，使用半角空格补足。向CHAR(8)类型的列中输入'abc'的时候，会以 abc 后面有 5 个半角空格的形式保存起来。
VARCHAR 型：VAR 是 VARING（可变的）的缩写。同 CHAR 类型一样，但该类型的列是以可变长字符串的形式来保存字符串的，即使字符数未达到最大长度，也不会用半角空格补足。
DATE 型：用来指定存储日期（年月日）的列的数据类型（日期型）。

约束的设置

约束是除了数据类型之外，对列中存储的数据进行限制或者追加条件
的功能。Product 表中设置了两种约束。

1
2
3

product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,

NULL是代表空白（无记录）的关键字。在NULL之前加上了表示否定的NOT，就是给该列设置了不能输入空白，也就是必须输入数据的约束（如果什么都不输入就会出错）。

PRIMARY KEY (product_id)
这是用来给product_id列设置主键约束的。所谓键，就是在指定特定数据时使用的列的组合。键种类多样，主键（primary key）就是可以特定一行数据的列。也就是说，如果把product_id列指定为主键，就可以通过该列取出特定的商品数据了。

1-5　表的删除和更新

表的删除：DROP TABLE <表名>；

表定义的更新:

添加列ALTER TABLE <表名> ADD COLUMN <列名> <数据类型> <该列所需约束>；
删除列ALTER TABLE <表名> DROP COLUMN <列名>；

向表中插入数据：INSERT INTO <表名> (可选字段名, …) VALUES (<value1>, …);　字段顺序不必与表顺序一致。如字段有默认值，可不写。

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;

开头的BEGIN TRANSACTION语句是开始插入行的指令语句，结尾的COMMIT语句是确定插入行的指令语句。这些指令语句将会在第 4 章详细介绍，不必急于记住这些语句。

变更表名：RENAME TABLE <变更前的名称> to <变更后的名称>;

第2章　查询基础

2-1　SELECT语句基础

　　从表中选取数据时需要使用SELECT语句，也就是只从表中选出必要数据的意思。通过SELECT语句查询并选取出必要数据的过程称为匹配查询或查询（query）。
　　SELECT语句是 SQL 语句中使用最多的最基本的 SQL 语句。掌握了SELECT语句，距离掌握 SQL 语句就不远了。

1 2	SELECT <列名>, …… FROM <表名>；

　　该SELECT语句包含了SELECT和FROM两个子句（clause）。
　　子句：以SELECT或者FROM等作为起始的短语。
　　SELECT子句中列举了希望从表中查询出的列的名称，而FROM子句则指定了选取出数据的表的名称。
　　查询结果中列的顺序和SELECT子句中的顺序相同。

　　查询全部的列：

1 2	SELECT * FROM <表名>;

　　如果使用星号的话，就无法设定列的显示顺序了。这时就会按照CREATE TABLE语句的定义对列进行排序。

　　SQL 语句使用换行符或者半角空格来分隔单词，在任何位置进行分隔都可以，即使像下面这样通篇都是换行符也不会影响SELECT语句的执行。但是这样可能会由于看不清楚而出错。原则上希望大家能够以子句为单位进行换行（子句过长时，为方便起见可以换行）。另外，插入空行（无任何字符的行）会造成执行错误。

SELECT
* FROM
Product
;

　　为列设定别名：

SELECT product_id AS id,
product_name AS name,
purchase_price AS price
FROM Product;

　　AS可省略，使用中文时需要用双引号（”）括起来

　　常数的查询：
　　SELECT子句中不仅可以书写列名，还可以书写常数。下列代码SELECT子句中的第一列'商品'是字符串常数，第 2 列38是数字常数，第 3 列'2009-02-24'是日期常数，它们将与product_id列和product_name列一起被查询出来。

1 2	SELECT '商品' AS string, 38 AS number, '2009-02-24' AS date, product_id, product_name FROM Product;

| string | number | date       | product_id | product_name
+--------+--------+------------+------------+--------------
| 商品   |     38 | 2009-02-24 | 0001       | T恤衫
| 商品   |     38 | 2009-02-24 | 0002       | 打孔器
| 商品   |     38 | 2009-02-24 | 0003       | 运动T恤
| 商品   |     38 | 2009-02-24 | 0004       | 菜刀
| 商品   |     38 | 2009-02-24 | 0005       | 高压锅
| 商品   |     38 | 2009-02-24 | 0006       | 叉子
| 商品   |     38 | 2009-02-24 | 0007       | 擦菜板
| 商品   |     38 | 2009-02-24 | 0008       | 圆珠笔

　　删除重复行：想知道表中保存了哪些商品种类。

1 2	SELECT DISTINCT product_type "商品种类" FROM Product;

　　选择记录：选取出满足某些条件的数据。

1
2
3

SELECT <列名>, ……
  FROM <表名>
 WHERE <条件表达式>;

1
2
3

SELECT product_name, product_type
  FROM Product
 WHERE product_type = '衣服';

　　首先通过WHERE子句查询出符合指定条件的记录，然后再选取出SELECT语句指定的列。

　　SQL 中子句的书写顺序是固定的，不能随意更改。

　　单行注释：--
　　多行注释：/*…*/

SELECT DISTINCT product_id, purchase_price
/* 本SELECT语句，
   会从结果中删除重复行。*/
  FROM Product;

2-2　算术运算符和比较运算符

算术运算符

　　SQL 语句中可以使用计算表达式。
　　四则运算所使用的运算符（+、-、*、/）称为算术运算符。

1
2
3

SELECT product_name, sale_price,
       sale_price * 2 AS "sale_price_x2"
  FROM Product;

　　SQL 中也可以使用括号()。括号中运算表达式的优先级会得到提升，优先进行运算。
　　括号的使用并不仅仅局限于四则运算，还可以用在 SQL 语句的任何表达式当中。具体的使用方法今后会慢慢介绍给大家。

　　NULL 的运算结果都为NULL。

比较运算符

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

1
2
3

SELECT product_name, product_type, regist_date
  FROM Product
 WHERE regist_date < '2009-09-27';

1
2
3

SELECT product_name, sale_price, purchase_price
  FROM Product
 WHERE sale_price - purchase_price >= 500;

　　字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。以相同字符开头的单词比不同字符开头的单词更相近。

　　比较运算符结果中没有NULL数据。

　　选取NULL数据。

1
2
3

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price IS NULL;

2-3　逻辑运算符

NOT 运算符

1
2
3

SELECT product_name, product_type, sale_price
  FROM Product
 WHERE NOT sale_price >= 1000;

　　效果等价于 < 1000，但不直观，它用来否定某一条件，不能滥用。

AND 运算符和 OR 运算符

AND 运算符在其两侧的查询条件都成立时整个查询条件才成立，其意思相当于“并且”。
OR 运算符在其两侧的查询条件有一个成立时整个查询条件都成立，其意思相当于“或者”。

SELECT product_name, purchase_price
  FROM Product
 WHERE product_type = '厨房用具'
    OR sale_price >= 3000;

　　文氏图：将集合（事物的聚集）的关系通过更加容易理解的图形进行可视化展示。

　　AND 运算符的优先级高于 OR 运算符。想要优先执行 OR 运算符时可以使用括号。

SELECT product_name, product_type, regist_date
  FROM Product
 WHERE product_type = '办公用品'
   AND (   regist_date = '2009-09-11'
        OR regist_date = '2009-09-20');

逻辑运算符和真值

　　NOT、AND 和 OR 称为逻辑运算符。这里所说的逻辑就是对真值进行操作的意思。真值就是值为真（TRUE）或假（FALSE）其中之一的值。
　　上一节介绍的比较运算符会把运算结果以真值的形式进行返回。比较结果成立时返回真（TRUE），比较结果不成立时返回假（FALSE）。例如，对于sale_price >= 3000这个查询条件来说，由于product_name列为'运动T恤'的记录的sale_price列的值是2800，因此会返回假（FALSE），而product_name列为’高压锅’的记录的sale_price列的值是5000，所以返回真（TRUE）。
　　逻辑运算符对比较运算符等返回的真值进行操作。AND 运算符两侧的真值都为真时返回真，除此之外都返回假。OR 运算符两侧的真值只要有一个不为假就返回真，只有当其两侧的真值都为假时才返回假。NOT 运算符只是单纯的将真转换为假，将假转换为真。
　　使用 AND 运算符进行的逻辑运算称为逻辑积，使用 OR 运算符进行的逻辑运算称为逻辑和。

第3章　聚合与排序

3-1　对表进行聚合查询

聚合函数

　　通过 SQL 对数据进行某种操作或计算时需要使用函数。

COUNT：计算表中的记录数（行数）
SUM：计算表中数值列中数据的合计值
AVG：计算表中数值列中数据的平均值
MAX：求出表中任意列中数据的最大值
MIN：求出表中任意列中数据的最小值

　　用于汇总的函数称为聚合函数。聚合，就是将多行汇总为一行。

　　计算全部数据的行数：

1 2	SELECT COUNT(*) FROM Product;

　　此处的输入值称为参数或者 parameter，输出值称为返回值。
　　
　　COUNT 函数的结果根据参数的不同而不同。COUNT(*)会得到包含 NULL 的数据行数，而COUNT(<列名>)会得到 NULL 之外的数据行数。
　　其他函数并不能将星号作为参数，该特性是 COUNT 函数所特有的。
　　聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。

　　计算毛利润：

SELECT SUM(sale_price - purchase_price)
  FROM Product;
/* 下面的语句会因 NULL 值造成误差
SUM(sale_price) - SUM(purchase_price)*/

　　计算平均销售价格：

1 2	SELECT AVG(sale_price) FROM Product;

　　计算销售价格极差：

1 2	SELECT MAX(sale_price) - MIN(sale_price) FROM Product;

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

1 2	SELECT MAX(regist_date), MIN(regist_date) FROM Product;

　　使用聚合函数删除重复值，计算值的种类：

1 2	SELECT COUNT(DISTINCT product_type) FROM Product;

3-2　对表分组

1
2
3

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

　　按照商品种类统计数据行数：

1
2
3

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;

　　GROUP BY 子句中指定的列称为聚合键或者分组列，能够决定表的切分方式。

SELECT purchase_price, COUNT(*)
  FROM Product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;

　　GROUP BY 和 WHERE 并用时SELECT 语句的执行顺序：FROM → WHERE → GROUP BY → SELECT

　　在使用 COUNT 这样的聚合函数时，SELECT 子句中的元素有严格
的限制，只能存在以下三种元素：

常数
聚合函数
GROUP BY 子句中指定的列名（也就是聚合键）

　　通过某个聚合键将表分组之后，结果中的一行数据就代表一组。例如，使用进货单价将表进行分组之后，一行就代表了一个进货单价。问题就出在这里，聚合键和商品名并不一定是一对一的。
　　例如，进货单价是 2800 日元的商品有“运动 T 恤”和“菜刀”两种，但是 2800 日元这一行应该对应哪个商品名呢？像这样与聚合键相对应的、同时存在多个值的列出现在 SELECT 子句中的情况，理论上是不可能的。

　　GROUP BY 子句中不能使用别名。
　　GROUP BY 子句结果的显示是无序的。
　　只有 SELECT 子句、HAVING 子句、ORDER BY 子句中能使用聚合函数。

3-3　为聚合结果指定条件

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……
HAVING <分组结果对应的条件>

　　HAVING 子句必须写在 GROUP BY 子句之后。

　　查询销售单价的平均值大于等于 2500 日元的商品种类

SELECT product_type, AVG(sale_price)
  FROM Product
 GROUP BY product_type
HAVING AVG(sale_price) >= 2500;

　　HAVING 子句中能够使用的 3 种要素：

常数
聚合函数
GROUP BY 子句中指定的列名（也就是聚合键）

3-4　对查询结果进行排序

1
2
3

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 ORDER BY <排序基准列1>, <排序基准列2>, ……

　　降序在列名后加上DESC关键字。
　　在ORDER BY子句中可以使用SELECT子句中定义的别名。
　　使用HAVING子句时SELECT语句的顺序
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

　　在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type
 ORDER BY COUNT(*);

第4章　数据更新

4-1　数据的插入（INSERT语句的使用方法）

INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUE ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
　　字符型、日期型需要用单引号括起来。
　　将列名和值用逗号隔开，分别括在（）内，这种形式称为清单。代码的 INSERT 语句包含如下两个清单：
　　列清单→(product_id, product_name, product_type, sale_price, purchase_price, regist_date)
　　值清单→('0001', 'T恤衫', '衣服', 1000, 500,'2009-09-20')

　　全列 INSERT 时，可以省略表名后的列清单。这时 VALUES 子句的值会默认按照从左到右的顺序赋给每一列。

　　多行 INSERT：

1
2
3

INSERT INTO ProductIns VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
                              ('0003', '运动T恤', '衣服', 4000, 2800, NULL),
                              ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

　　插入默认值：设定默认值，就可以在 INSERT 语句中自动为列赋值了。

CREATE TABLE ProductIns
(product_id CHAR(4) NOT NULL,
 （略）
 sale_price INTEGER DEFAULT 0, -- 销售单价的默认值设定为0;
 （略）
 PRIMARY KEY (product_id));

1	INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0007', '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28');

　　从其它表中复制数据：可以在需要备份数据时使用。

INSERT INTO ProductCopy (product_id, product_name, product_type, 
sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, 
purchase_price, regist_date
FROM Product;

　　目前为止学到的各种 SELECT 语句都可以使用。

INSERT INTO ProductType (product_type, sum_sale_price, sum_purchase_price)
SELECT product_type, SUM(sale_price), SUM(purchase_price)
  FROM Product
 GROUP BY product_type;

4-2　数据的删除（DELETE语句的使用方法）

① DROP TABLE 语句可以将表完全删除
② TRUNCATE <表名>; 删除表中的全部数据，不能指定条件，比 DELETE 快
③ DELETE FROM <表名>; 删除表中的全部数据，可指定条件

　　指定删除对象：

1 2	DELETE FROM <表名> WHERE <条件>;

1 2	DELETE FROM Product WHERE sale_price >= 4000;

4-3　数据的更新（UPDATE语句的使用方法）

1
2
3

UPDATE <表名>
   SET <列名> = <表达式>;
 WHERE <条件>;

1 2	UPDATE Product SET regist_date = NULL;

UPDATE Product
   SET sale_price = sale_price * 10,
       purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';

4-4　事务

　　事务（transaction）是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务，可以对数据库中的数据更新处理的提交和取消进行管理。

START TRANSACTION; -- 可以省略
DML语句①;
DML语句②;
DML语句③;
……
COMMIT; / ROLLBACK;

START TRANSACTION;
    -- 将运动T恤的销售单价降低1000日元
    UPDATE Product
       SET sale_price = sale_price - 1000
     WHERE product_name = '运动T恤';
    
    -- 将T恤衫的销售单价上浮1000日元
    UPDATE Product
       SET sale_price = sale_price + 1000
     WHERE product_name = 'T恤衫';

COMMIT;

　　ACID 特性：

原子性（Atomicity）：原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有。
一致性（Consistency）：一致性也称为完整性，指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者 NOT NULL 约束等。
隔离性（Isolation）：保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。即使某个事务向表中添加了记录，在没有提交之前，其他事务也是看不到新添加的记录的。
持久性（Durability）：在事务结束后，DBMS 能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段恢复。

第5章　复杂查询

5-1　视图

　　视图和表的结果相同，区别在于表中保存的是实际的数据，而视图中保存的是 SELECT 语句（视图本身并不存储数据）。
　　实际上，在 SQL 语句中并不需要区分哪些是表，哪些是视图，只需要知道在更新时它们之间存在一些不同就可以了。

1
2
3

CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS
<SELECT语句>

1 2	SELECT product_type, cnt_product FROM ProductSum;

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

　　可以更新的视图：既没有聚合又没有结合的 SELECT 语句

CREATE VIEW ProductJim (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
AS
SELECT *
  FROM Product
 WHERE product_type = '办公用品';

1	INSERT INTO ProductJim VALUES ('0009', '印章', '办公用品', 95, 10, '2009-11-30');

5-2　子查询

　　子查询就是一次性视图（SELECT语句），在 SELECT 语句执行完毕之后消失。
　　尽量避免使用多层嵌套的子查询。
　　子查询必须设定名称，AS关键字可省略。

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;

　　标量是单一的意思。
　　标量子查询是返回单一值的子查询，只能返回 1 行 1 列的结果。
　　几乎所有的地方都可以使用。
　　选取出销售单价高于全部商品的平均单价的商品：

SELECT product_id, product_name, sale_price
  FROM Product
 WHERE sale_price > (SELECT AVG(sale_price)
                     FROM Product);

SELECT product_id,
       product_name,
       sale_price,
       (SELECT AVG(sale_price)
          FROM Product) AS avg_price
  FROM Product;

选取按商品种类计算的销售单价高于全部商品平均销售单价的商品种类

SELECT product_type, AVG(sale_price)
  FROM Product
 GROUP BY product_type
HAVING AVG(sale_price) > (SELECT AVG(sale_price)
                            FROM Product);

5-3　关联子查询

　　使用关联子查询时，需要在表所对应的列名之前加上表的别名，<表名>.<列名>

通过关联子查询按照商品种类对平均销售单价进行比较

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

第6章　函数、谓词、CASE表达式

6-1　各种各样的函数

　　所谓函数，就是输入某一值得到相应输出结果的功能，输入值称为参数（parameter），输出值称为返回值。

算数函数

绝对值：ABS
　　绝对值（absolute value）不考虑数值的符号，表示一个数到原点的距离。

1
2
3

SELECT m,
       ABS(m) AS abs_col
  FROM SampleMath;

求余：MOD（modulo 的缩写）
四舍五入：ROUND(<列名>, 四舍五入位数)

字符串函数

拼接：CONCAT(字符串1, 字符串2)
计算字符串长度：LENGTH 和 CHAR_LENGTH（推荐）
小写转换：LOWER
大写转换：UPPER
替换：REPLACE(对象字符串，替换前的字符串，替换后的字符串)
字符串截取：SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）　截取的起始位置从字符串最左侧开始计算。

SELECT str1,
       UPPER(str1) AS up_str
  FROM SampleStr
 WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');

日期函数

当前日期：SELECT CURRENT_DATE;
当前时间：SELECT CURRENT_TIME
当前日期和时间：SELECT CURRENT_TIMESTAMP;
截取日期元素：EXTRACT(日期元素 FROM 日期)

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

转换函数

类型转换：CAST（转换前的值 AS 想要转换的数据类型）
SELECT CAST('2009-12-14' AS DATE) AS date_col;
将 NULL 转换为其他值：COALESCE(数据1，数据2，数据3……)　会返回参数中从左数第 1 个非 NULL 的值。
SELECT COALESCE(NULL, 1);

6-2　谓词

　　谓词就是返回值为真值的函数。
　　通常的函数返回值有可能是数字、字符串或者日期等，但是谓词的返回值全都是真值（TRUE/FALSE/UNKNOWN）
　　=、<、>、<>等比较运算符，其正式的名称就是比较谓词。

模式匹配：
　　LIKE 谓词比 = 更模糊，需要字符串部分一致时使用。
　　部分一致大体可以分为前方一致、中间一致和后方一致三种类型。
　　%是代表“0 字符以上的任意字符串”的特殊符号。
　　前方一致：ddd%
　　中间一致：%ddd%
　　后方一致：%ddd

查询以 ddd 开头的所有字符串

1
2
3

SELECT *
  FROM SampleLike
 WHERE strcol LIKE 'ddd%';

　　还可以使用_（下划线）来代替%，代表任意一个字符。

1
2
3

SELECT *
  FROM SampleLike
 WHERE strcol LIKE 'abc_ _';

范围查询：
　　BETWEEN a AND b　等于[a, b]，不想要临界值，就只能用? > a AND ? < b

选取销售单价为 100 ～ 1000 日元的商品

1
2
3

SELECT product_name, sale_price
  FROM Product
 WHERE sale_price BETWEEN 100 AND 1000;

判断是否为NULL：
　　为了选取出某些值为NULL的列的数据，不能使用=，而只能使用特
定的谓词IS NULL

选取出进货单价为NULL的商品

1
2
3

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price IS NULL;

　　想要选取NULL以外的数据时，需要使用IS NOT NULL

OR 的简便用法：IN( 值, ……)
　　选取“进货单价不是 x、x”的商品时，可以使用否定形式NOT IN( 值, ……)

选取出进货单价为 320 日元、500 日元、5000 日元的商品。

1
2
3

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price IN (320, 500, 5000);

SELECT product_name, sale_price
  FROM Product
 WHERE product_id NOT IN (SELECT product_id
                            FROM ShopProduct
                           WHERE shop_id = '000A');

存在：EXIST 谓词
　　通常指定关联子查询作为EXIST的参数。
　　作为EXIST参数的子查询中经常会使用SELECT *。
　　使用NOT EXIST替换NOT IN
　　没有必要勉强使用EXIST，因为EXIST拥有IN所不具有的便利性。

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

6-3　CASE表达式

　　CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支

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

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

product_name | abc_product_type
---------------+------------------
T恤衫         | A ：衣服
打孔器        | B ：办公用品
运动T恤       | A ：衣服
菜刀          | C ：厨房用具
高压锅        | C ：厨房用具
叉子          | C ：厨房用具
擦菜板        | C ：厨房用具
圆珠笔        | B ：办公用品

第7章　集合运算

7-1　表的加减法

　　集合在数学领域表示“事物的总和”，在数据库领域表示记录的集合，表、视图和查询的执行结果都是记录的集合。
　　集合运算符会除去重复的记录，使用ALL选项，可以保留重复行。

表的加法：UNION（并集），包含重复行使用UNION ALL

SELECT product_id, product_name
  FROM Product
UNION
SELECT product_id, product_name
  FROM Product2;

注意事项：

列数必须相同
列的类型必须一致
可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次

7-2　联结（以列为单位对表进行联结）

内联结

　　应用最广泛的联结运算。
　　所谓联结运算，就是“以 A 中的列作为桥梁，将 B 中满足同样条件的列汇集到同一结果之中”。
　　常用=

1
2
3

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;

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

　　INNER可省略。
　　SP和P分别是这两张表的别名，并不是必需的。但由于表名太长会影响语句的可读性，因此还是希望大家能够习惯使用别名。
　　ON是专门用来指定联结条件的，它能起到与WHERE相同的作用。需要指定多个键时，同样可以使用AND、OR。
　　联结条件也可以使用“=”来记述。还可以使用<=和BETWEEN等谓词。但因为实际应用中九成以上都可以用“=”进行联结，所以开始时只要记住使用“=”就可以了。
　　使用联结时SELECT子句中的列需要按照<表的别名>.<列名>的格式书写。

外联结

　　外联结中使用LEFT、RIGHT来指定主表。使用二者所得到的结果完全相同。

1
2
3

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;

TOP 限制查询结果

　　显示结果前几行：SELECT TOP n [percent] [WITH TIES]　百分之 n，包含重复行 WITH TIES必须使用 ORDER BY

1
2
3

SELECT TOP 3 WITH TIES sname, sage, sdept
  FROM Students
 ORDER BY sage DESC;

第8章　SQL高级处理

8-1　窗口函数

专用窗口函数

　　在Oracle和SQL Server中称为分析函数。
　　窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。

1 2	<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)

窗口函数大体可以分为：
① 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）
② RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数

SELECT product_name, product_type, sale_price,
       RANK () OVER (PARTITION BY product_type
                         ORDER BY sale_price) AS ranking
 FROM Product;

　　PARTITION BY能够设定排序的对象范围。本例中，为了按照商品种类进行排序，指定了product_type，分组后的记录集合称为窗口，窗口代表范围。
　　ORDER BY能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，指定了sale_price
　　窗口函数兼具分组和排序两种功能
　　使用窗口函数时起到关键作用的是PARTITION BY和GROUP BY。其中，PARTITION BY并不是必需的，即使不指定也可以正常使用窗口函数。
　　由于专用窗口函数无需参数，因此通常括号中都是空的
　　窗口函数只能在SELECT子句中使用

　　RANK 函数
　　例）有3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
　　DENSE_RANK 函数
　　例）有3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
　　ROW_NUMBER 函数
　　例）有3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……

作为窗口函数使用的聚合函数

　　所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同。

计算各个时间的销售额总额

1
2
3

SELECT product_id, product_name, sale_price,
     SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
  FROM Product;

8-2　GROUPING运算符

第九章　通过应用程序连接数据库

9-1　数据库世界和应用程序世界的连接

　　说到“driver”这个词，大家可能会一下子想到螺丝刀。英语中这两者确实是同一个单词。实际上，螺丝刀也是用来把两个部件连接在一起的，从广义上来说也能叫桥梁。在计算机的世界里，那些用来将打印机、键盘和鼠标等连接到电脑上的程序也被称为“驱动”，它同样肩负着“连接不同机器”的使命。

第0章 绪论——搭建SQL的学习环境

第1章 数据库和SQL

1-1 数据库是什么

1-2 数据库的结构

1-3 SQL概要

SQL语句及其种类

SQL的基本书写规则

1-4 表的创建

数据库和表的创建

命名规则

数据类型的指定

约束的设置

1-5 表的删除和更新

第2章 查询基础

2-1 SELECT语句基础

2-2 算术运算符和比较运算符

算术运算符

比较运算符

2-3 逻辑运算符

NOT 运算符

AND 运算符和 OR 运算符

逻辑运算符和真值

第3章 聚合与排序

3-1 对表进行聚合查询

聚合函数

3-2 对表分组

3-3 为聚合结果指定条件

3-4 对查询结果进行排序

第4章 数据更新

4-1 数据的插入（INSERT语句的使用方法）

4-2 数据的删除（DELETE语句的使用方法）

4-3 数据的更新（UPDATE语句的使用方法）

4-4 事务

第5章 复杂查询

5-1 视图

5-2 子查询

5-3 关联子查询

第6章 函数、谓词、CASE表达式

6-1 各种各样的函数