千灵

Back
/ Update
43 min
开发

《SQL 基础教程》笔记

《SQL基础教程》笔记：涵盖数据库基础概念、SQL语句分类（DDL/DML/DCL）及基本书写规则。
views | comments

SQL基础教程 / (日) MICK 著 ; 孙淼, 罗勇译. — 2版 — 北京 : 人民邮电出版社, 2017.6

（图灵程序设计丛书）

ISBN 978-7-115-45502-4


第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 的基本书写规则#

  1. SQL 语句要以分号（;）结尾。

  2. SQL 语句不区分大小写，但是插入到表中的数据是区分大小写的。为便于理解，本书使用以下规则书写 SQL 语句：

  • 关键字大写
  • 表名的首字母大写
  • 其余（列名等）小写
  1. 常数的书写方式是固定的。在 SQL 语句中直接书写的字符串、日期或者数字等称为常数。书写方式如下所示：
  • SQL 语句中含有字符串的时候，需要像’abc’这样，使用单引号（‘）将字符串括起来，用来标识这是一个字符串。
  • SQL 语句中含有日期的时候，同样需要使用单引号将其括起来。日期的格式有很多种（‘26 Jan 2010’ 或者 ‘10/01/26’等），本书统一使用’2010-01-26’这种’年-月-日’的格式。
  • 在 SQL 语句中书写数字的时候，不需要使用任何符号标识，直接写成 1000 这样的数字即可。
  1. 单词需要用半角空格或者换行来分隔，不能使用全角空格作为单词的分隔符。

1-4　表的创建#

数据库和表的创建#

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

创建表：

CREATE TABLE <表名>
（<列名1> <数据类型> <该列所需约束>，
<列名2> <数据类型> <该列所需约束>，
<列名3> <数据类型> <该列所需约束>，
<列名4> <数据类型> <该列所需约束>，
……
<该表的约束1>, <该表的约束2>,……）；
sql
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));
sql

命名规则#

  • 数据库名称、表名以及列名只能使用半角英文字母、数字、下划线
  • 名称必须以半角英文字母开头
  • 名称不能重复

数据类型的指定#

数据类型表示数据的种类，包括数字型、字符型和日期型等。

字节是计算机内部的数据单位。

一个字符通常需要 1 到 3 个字节来表示（根据字符的种类和表现方式有所不同）。

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

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

约束的设置#

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

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

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

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

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

第2章　查询基础#

2-1　SELECT语句基础#

从表中选取数据时需要使用SELECT语句，也就是只从表中选出必要数据的意思。通过SELECT语句查询并选取出必要数据的过程称为匹配查询或查询（query）。

SELECT语句是 SQL 语句中使用最多的最基本的 SQL 语句。掌握了SELECT语句，距离掌握 SQL 语句就不远了。

SELECT <列名>, ……
  FROM <表名>；
sql

该SELECT语句包含了SELECT和FROM两个子句（clause） 。

子句：以SELECT或者FROM等作为起始的短语。

SELECT子句中列举了希望从表中查询出的列的名称，而FROM子句则指定了选取出数据的表的名称。 查询结果中列的顺序和SELECT子句中的顺序相同。

查询全部的列：

SELECT *
  FROM <表名>;
sql

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

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

SELECT
* FROM
Product
;
sql

为列设定别名：

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

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

常数的查询：

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

SELECT '商品' AS string, 38 AS number, '2009-02-24' AS date, product_id, product_name
FROM Product;
sql
| 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       | 圆珠笔
sql

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

SELECT DISTINCT product_type "商品种类"
  FROM Product;
sql

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

SELECT <列名>, ……
  FROM <表名>
 WHERE <条件表达式>;
sql
SELECT product_name, product_type
  FROM Product
 WHERE product_type = '衣服';
sql

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

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

单行注释：--

多行注释：/*…*/

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

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

算术运算符#

SQL 语句中可以使用计算表达式。

四则运算所使用的运算符（+、-、*、/）称为算术运算符。

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

SQL 中也可以使用括号()。括号中运算表达式的优先级会得到提升，优先进行运算。

括号的使用并不仅仅局限于四则运算，还可以用在 SQL 语句的任何表达式当中。具体的使用方法今后会慢慢介绍给大家。

NULL 的运算结果都为NULL。

比较运算符#

运算符      含义
=          和～相等
<>         和～不相等
>=         大于等于～
>          大于～
<=         小于等于～
<          小于～
sql
SELECT product_name, product_type, regist_date
  FROM Product
 WHERE regist_date < '2009-09-27';
sql
SELECT product_name, sale_price, purchase_price
  FROM Product
 WHERE sale_price - purchase_price >= 500;
sql

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

1
10
11
2
222
3
sql

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

选取NULL数据。

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price IS NULL;
sql

2-3　逻辑运算符#

NOT 运算符#

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

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

AND 运算符和 OR 运算符#

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

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

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

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

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

逻辑运算符和真值#

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： 求出表中任意列中数据的最小值

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

计算全部数据的行数：

SELECT COUNT(*)
  FROM Product;
sql

此处的输入值称为参数或者 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)*/
sql

计算平均销售价格：

SELECT AVG(sale_price)
  FROM Product;
sql

计算销售价格极差：

SELECT MAX(sale_price) - MIN(sale_price)
  FROM Product;
sql

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

SELECT MAX(regist_date), MIN(regist_date)
  FROM Product;
sql

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

SELECT COUNT(DISTINCT product_type)
  FROM Product;
sql

3-2　对表分组#

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

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

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

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

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

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 <分组结果对应的条件>
sql

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

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

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

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

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

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

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

降序在列名后加上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(*);
sql

第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) VALUES ('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：

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

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

CREATE TABLE ProductIns
(product_id CHAR(4) NOT NULL,
 （略）
 sale_price INTEGER DEFAULT 0, -- 销售单价的默认值设定为0;
 （略）
 PRIMARY KEY (product_id));
sql
INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0007', '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28');
sql

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

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

目前为止学到的各种 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;
sql

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

① DROP TABLE 语句可以将表完全删除

② TRUNCATE <表名>; 删除表中的全部数据，不能指定条件，比 DELETE 快

③ DELETE FROM <表名>; 删除表中的全部数据，可指定条件

指定删除对象：

DELETE FROM <表名>
 WHERE <条件>;
sql
DELETE FROM Product
 WHERE sale_price >= 4000;
sql

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

UPDATE <表名>
   SET <列名> = <表达式>
 WHERE <条件>;
sql
UPDATE Product
   SET regist_date = NULL;
sql
UPDATE Product
   SET sale_price = sale_price * 10,
       purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';
sql

4-4　事务#

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

START TRANSACTION; -- 可以省略
DML语句①;
DML语句②;
DML语句③;
……
COMMIT; / ROLLBACK;
sql
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;
sql

ACID 特性：

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

第5章　复杂查询#

5-1　视图#

视图和表的结果相同，区别在于表中保存的是实际的数据，而视图中保存的是 SELECT 语句（视图本身并不存储数据）。

实际上，在 SQL 语句中并不需要区分哪些是表，哪些是视图，只需要知道在更新时它们之间存在一些不同就可以了。

CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS
<SELECT语句>
sql
SELECT product_type, cnt_product
  FROM ProductSum;
sql

定义视图时不要使用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 = '办公用品';
sql
INSERT INTO ProductJim VALUES ('0009', '印章', '办公用品', 95, 10, '2009-11-30');
sql

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

标量是单一的意思。

标量子查询是返回单一值的子查询，只能返回 1 行 1 列的结果。

几乎所有的地方都可以使用。

选取出销售单价高于全部商品的平均单价的商品：

SELECT product_id, product_name, sale_price
  FROM Product
 WHERE sale_price > (SELECT AVG(sale_price)
                     FROM Product);
sql
SELECT product_id,
       product_name,
       sale_price,
       (SELECT AVG(sale_price)
          FROM Product) AS avg_price
  FROM Product;
sql
SELECT product_type, AVG(sale_price)
  FROM Product
 GROUP BY product_type
HAVING AVG(sale_price) > (SELECT AVG(sale_price)
                            FROM Product);
sql

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

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

6-1　各种各样的函数#

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

算术函数#

绝对值：ABS

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

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

求余：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', '山田');
sql

日期函数#

当前日期：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;
sql

转换函数#

类型转换：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

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

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

SELECT *
  FROM SampleLike
 WHERE strcol LIKE 'abc__';
sql

范围查询：

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

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

判断是否为NULL：

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

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price IS NULL;
sql

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

OR 的简便用法：IN( 值, ……)

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

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price IN (320, 500, 5000);
sql
SELECT product_name, sale_price
  FROM Product
 WHERE product_id NOT IN (SELECT product_id
                            FROM ShopProduct
                           WHERE shop_id = '000A');
sql

存在：EXISTS 谓词

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

作为EXISTS参数的子查询中经常会使用SELECT *。

使用NOT EXISTS替换NOT IN

没有必要勉强使用EXISTS，因为EXISTS拥有IN所不具有的便利性。

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

6-3　CASE表达式#

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

CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     ...
     ELSE <表达式>
END
sql
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;
sql
product_name | abc_product_type
---------------+------------------
T恤衫         | A ：衣服
打孔器        | B ：办公用品
运动T恤       | A ：衣服
菜刀          | C ：厨房用具
高压锅        | C ：厨房用具
叉子          | C ：厨房用具
擦菜板        | C ：厨房用具
圆珠笔        | B ：办公用品
sql

第7章　集合运算#

7-1　表的加减法#

集合在数学领域表示“事物的总和”，在数据库领域表示记录的集合，表、视图和查询的执行结果都是记录的集合。

集合运算符会除去重复的记录，使用ALL选项，可以保留重复行。

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

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

注意事项：

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

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

内联结#

应用最广泛的联结运算。

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

常用=

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

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

使用联结时SELECT子句中的列需要按照<表的别名>.<列名>的格式书写。

外联结#

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

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

TOP 限制查询结果#

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

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

第8章　SQL高级处理#

8-1　窗口函数#

专用窗口函数#

在Oracle和SQL Server中称为分析函数。

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

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

窗口函数大体可以分为：

① 能够作为窗口函数的聚合函数（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;
sql

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 位……

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

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

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

8-2　GROUPING运算符#

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

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

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

《SQL 基础教程》笔记
https://qianling.pw/sql/
Author 千灵
Published at March 17, 2020
Copyright CC BY-NC-SA 4.0
赞赏支持 ☕

《语言学纲要》书摘

《生活化妆》笔记
Comment seems to stuck. Try to refresh?✨