[PSQL] SQL基础教程读书笔记（Chapter1-4）

wy_hhxx

于 2022-07-30 11:11:15 发布

阅读量379

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/wy_hhxx/article/details/126025500

版权

SQL基础教程读书笔记 MICK

示例程序下载 http://www.ituring.com.cn/book/1880

说明：如下笔记中的测试基于postgresql14

命令行连接本地PSQL： psql -U <username> -d <dbname> -h 127.0.0.1 -W

# psql -U postgres -d shop -h 127.0.0.1 -W
Password:
psql (14.1)
Type "help" for help.

shop=#

数据库的创建（CREATE DATABASE语句）

表定义的更新（ALTER TABLE语句）

使用聚合函数删除重复值（关键字DISTINCT）

3-2 对表进行分组

GROUP BY子句

聚合键中包含NULL的情况

使用WHERE子句时GROUP BY的执行结果

与聚合函数和GROUP BY子句有关的常见错误

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

HAVING子句

HAVING子句的构成要素

相对于HAVING子句，更适合写在WHERE子句中的条件

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

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

DELETE语句的基本语法

指定删除对象的DELETE语句（搜索型DELETE）

删除和舍弃

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

UPDATE语句的基本语法

指定条件的UPDATE语句（搜索型UPDATE）

1 数据库和SQL

1-3 SQL概要

SQL语句及其种类

SQL 语句可以分为以下三类，实际使用的SQL语句当中有90%属于DML

DDL（Data Definition Language，数据定义语言）用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令，
CREATE：创建数据库和表等对象
DROP：删除数据库和表等对象
ALTER：修改数据库和表等对象的结构

DML（Data Manipulation Language，数据操纵语言）用来查询或者变更表中的记录。DML 包含以下几种指令，
SELECT：查询表中的数据
INSERT：向表中插入新数据
UPDATE：更新表中的数据
DELETE：删除表中的数据

DCL（Data Control Language，数据控制语言）用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令。
COMMIT：确认对数据库中的数据进行的变更
ROLLBACK：取消对数据库中的数据进行的变更
GRANT：赋予用户操作权限
REVOKE：取消用户的操作权限

1-4 表的创建

数据库的创建（CREATE DATABASE语句）

语法1-1　创建数据库的CREATE DATABASE语句
CREATE DATABASE <数据库名称>;

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

CREATE DATABASE shop;

表的创建（CREATE TABLE语句）

语法1-2　创建表的CREATE TABLE语句
CREATE TABLE <表名>
（<列名1> <数据类型> <该列所需约束>，
<列名2> <数据类型> <该列所需约束>，
...
<该表的约束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));

数据类型的指定

CHAR型（定长字符串）
CHAR(10) 或者 CHAR(200) ，在括号中指定该列可以存储的字符串的长度（最大长度）
当列中存储的字符串长度达不到最大长度的时候，使用半角空格进行补足

VARCHAR型（可变长字符串）
可以通过括号内的数字来指定字符串的长度（最大长度）
即使字符数未达到最大长度，也不会用半角空格补足

约束的设置

Product 表中设置了两种约束
Product 表的 product_id 列、product_name 列和product_type 列设置了 NOT NULL 约束
PRIMARY KEY (product_id) 是用来给 product_id 列设置主键约束的

1-5 表的删除和更新

表的删除（DROP TABLE语句）

语法1-3　删除表时使用的DROP TABLE语句
DROP TABLE <表名>；

表定义的更新（ALTER TABLE语句）

语法1-4　添加列的ALTER TABLE语句
ALTER TABLE <表名> ADD COLUMN <列的定义>;

代码清单1-4　添加一列可以存储100位的可变长字符串的product_name_pinyin列

ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);

语法1-5　删除列的ALTER TABLE语句
ALTER TABLE <表名> DROP COLUMN <列名>;

代码清单1-5　删除product_name_pinyin列

ALTER TABLE Product DROP COLUMN product_name_pinyin;

补充：

添加NOT NULL约束
ALTER TABLE <表名> ALTER COLUMN <列名> SET NOT NULL;

去除NOT NULL约束
ALTER TABLE <表名> ALTER COLUMN <列名> DROP NOT NULL;

shop=# ALTER TABLE Product ALTER COLUMN product_type DROP NOT NULL;
ALTER TABLE
shop=# \d Product
                          Table "public.product"
     Column     |          Type          | Collation | Nullable | Default
----------------+------------------------+-----------+----------+---------
 product_id     | character(4)           |           | not null |
 product_name   | character varying(100) |           | not null |
 product_type   | character varying(32)  |           |          |
 sale_price     | integer                |           |          |
 purchase_price | integer                |           |          |
 regist_date    | date                   |           |          |
Indexes:
    "product_pkey" PRIMARY KEY, btree (product_id)

shop=#
shop=# ALTER TABLE Product ALTER COLUMN product_type SET NOT NULL;
ALTER TABLE
shop=# \d Product
                          Table "public.product"
     Column     |          Type          | Collation | Nullable | Default
----------------+------------------------+-----------+----------+---------
 product_id     | character(4)           |           | not null |
 product_name   | character varying(100) |           | not null |
 product_type   | character varying(32)  |           | not null |
 sale_price     | integer                |           |          |
 purchase_price | integer                |           |          |
 regist_date    | date                   |           |          |
Indexes:
    "product_pkey" PRIMARY KEY, btree (product_id)

shop=#

添加主键
ALTER TABLE <表名> ADD PRIMARY KEY (<列名>);

去除主键
ALTER TABLE <表名> DROP CONSTRAINT <主键名>;

shop=# ALTER TABLE Product ADD PRIMARY KEY  (product_name);
ERROR:  multiple primary keys for table "product" are not allowed
shop=# ALTER TABLE Product DROP CONSTRAINT product_pkey;
ALTER TABLE
shop=# ALTER TABLE Product ADD PRIMARY KEY  (product_name);
ALTER TABLE
shop=# \d Product
……
Indexes:
    "product_pkey" PRIMARY KEY, btree (product_name)

shop=# ALTER TABLE Product DROP CONSTRAINT product_pkey;
ALTER TABLE
shop=# ALTER TABLE Product ADD CONSTRAINT product_pkey PRIMARY KEY(product_name);
ALTER TABLE
shop=# \d Product
……
Indexes:
    "product_pkey" PRIMARY KEY, btree (product_name)

shop=#

更改列名
ALTER TABLE <表名> RENAME COLUMN <列名> TO <新列名>;

shop=# ALTER TABLE Product RENAME COLUMN product_name TO product_label;
ALTER TABLE
shop=# \d Product
                          Table "public.product"
     Column     |          Type          | Collation | Nullable | Default
----------------+------------------------+-----------+----------+---------
 product_id     | character(4)           |           | not null |
 product_label  | character varying(100) |           | not null |
 product_type   | character varying(32)  |           | not null |
...

shop=#

更改列类型
ALTER TABLE <表名> ALTER COLUMN <列名> TYPE <数据类型>;

shop=# ALTER TABLE Product
shop-# ALTER COLUMN product_label TYPE Varchar(30),
shop-# ALTER COLUMN product_type TYPE Varchar(30);
ALTER TABLE
shop=# \d Product
                         Table "public.product"
     Column     |         Type          | Collation | Nullable | Default
----------------+-----------------------+-----------+----------+---------
 product_id     | character(4)          |           | not null |
 product_label  | character varying(30) |           | not null |
 product_type   | character varying(30) |           | not null |
...

shop=#

添加默认值
ALTER TABLE <表名> ALTER COLUMN <列名> SET DEFAULT <默认值>;

shop=# ALTER TABLE Product ALTER COLUMN product_type SET DEFAULT 'other type';
ALTER TABLE
shop=# \d Product;
                                     Table "public.product"
     Column     |         Type          | Collation | Nullable |             Default
----------------+-----------------------+-----------+----------+---------------------------------
 product_id     | character(4)          |           | not null |
 product_label  | character varying(30) |           | not null |
 product_type   | character varying(30) |           | not null | 'other type'::character varying
...

shop=#

去除默认值
ALTER TABLE <表名> ALTER COLUMN <列名> DROP DEFAULT;

shop=# ALTER TABLE Product ALTER COLUMN product_type DROP DEFAULT;
ALTER TABLE
shop=# \d Product;
                         Table "public.product"
     Column     |         Type          | Collation | Nullable | Default
----------------+-----------------------+-----------+----------+---------
 product_id     | character(4)          |           | not null |
 product_label  | character varying(30) |           | not null |
 product_type   | character varying(30) |           | not null |
...

shop=#

向Product表中插入数据

代码清单1-6　向Product表中插入数据的SQL语句

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

说明：在MySQL中运行时，需要把 BEGIN TRANSACTION; 改写成 START TRANSACTION;

2 查询基础

2-1 SELECT语句基础

为列设定别名

代码清单2-4　为列设定别名

shop=# SELECT product_id AS id,
shop-#  product_name AS name,
shop-#  purchase_price AS price
shop-#  FROM Product;
  id  |  name   | price
------+---------+-------
 0001 | T恤     |   500
 0002 | 打孔器  |   320
 0003 | 运动T恤 |  2800
 0004 | 菜刀    |  2800
 0005 | 高压锅  |  5000
 0006 | 叉子    |
 0007 | 擦菜板  |   790
 0008 | 圆珠笔  |
(8 rows)

shop=#

别名可以使用中文，使用中文时需要用双引号（"）括起来。

shop=# SELECT product_id AS "商品编号",
shop-#  product_name AS "商品名称",
shop-#  purchase_price AS "进货单价"
shop-#  FROM Product;
 商品编号 | 商品名称 | 进货单价
----------+----------+----------
 0001     | T恤      |      500
 0002     | 打孔器   |      320
 0003     | 运动T恤  |     2800
 0004     | 菜刀     |     2800
 0005     | 高压锅   |     5000
 0006     | 叉子     |
 0007     | 擦菜板   |      790
 0008     | 圆珠笔   |
(8 rows)

shop=#

常数的查询

代码清单2-6　查询常数

shop=# SELECT '商品' AS string, 38 AS number, '2009-02-24' AS date,
shop-#  product_id  FROM Product;
 string | number |    date    | product_id
--------+--------+------------+------------
 商品   |     38 | 2009-02-24 | 0001
 商品   |     38 | 2009-02-24 | 0002
...
 商品   |     38 | 2009-02-24 | 0008
(8 rows)

shop=#

从结果中删除重复行

代码清单2-7　使用DISTINCT删除product_type列中重复的数据

shop=# SELECT DISTINCT product_type  FROM Product;
 product_type
--------------
 衣服
 办公用品
 厨房用具
(3 rows)

shop=#

在使用 DISTINCT 时，NULL 也被视为一类数据。
NULL 存在于多行中时，也会被合并为一条 NULL 数据。
代码清单2-8　对含有NULL数据的列使用DISTINCT关键字

shop=# SELECT DISTINCT purchase_price FROM Product;
 purchase_price
----------------

            320
            500
           2800
           5000
            790
(6 rows)

shop=#

DISTINCT 在多列之前使用，会将多个列的数据进行组合，将重复的数据合并为一条。

代码清单2-9　在多列之前使用DISTINCT

shop=# SELECT DISTINCT product_type, regist_date FROM Product;
 product_type | regist_date
--------------+-------------
 衣服         |
 厨房用具     | 2008-04-28
 衣服         | 2009-09-20
 办公用品     | 2009-11-11
 厨房用具     | 2009-01-15
 办公用品     | 2009-09-11
 厨房用具     | 2009-09-20
(7 rows)

shop=#

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

注释的书写方法

一行注释书写在 "--" 之后，只能写在同一行。
多行注释书写在 "/*" 和 "*/" 之间，可以跨多行。

2-2 算数运算符与比较运算符

需要注意NULL

(1)包含NULL的运算，其结果也是NULL。
(2)不能对NULL使用比较运算符，判断是否为 NULL 使用 IS NULL 运算符

代码清单2-29 选取不为NULL的记录

shop=# SELECT product_name, purchase_price
shop-#  FROM Product
shop-#  WHERE purchase_price IS NULL;
 product_name | purchase_price
--------------+----------------
 叉子         |
 圆珠笔       |
(2 rows)

shop=#

2-3 逻辑运算符

含有NULL时的真值

Product表中设置NOT NULL约束的原因
原本只有 4 行的真值表，如果要考虑 NULL 的话就会像表 2-6 那样增加为3×3=9 行，考虑 NULL 时的条件判断也会变得异常复杂，数据库领域的有识之士们达成了“尽量不
使用 NULL”的共识。

3 聚合与排序

3-1 对表进行聚合查询

聚合函数

SQL中用于汇总的函数，eg: COUNT, SUM, AVG, MAX, MIN

计算NULL之外的数据的行数

COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。

计算合计值

对于 SUM 函数来说，即使包含 NULL，也可以计算出合计值。
从结果上说，所有的聚合函数，如果以列名为参数，那么在计算之前就已经把NULL 排除在外了。因此，无论有多少个 NULL 都会被无视。

计算平均值

平均值的情况与 SUM 函数相同，会事先删除 NULL 再进行计算，注意分母也会去除 NULL 的行。NULL 被排除，这与“等价为 0”并不相同。

使用聚合函数删除重复值（关键字DISTINCT）

代码清单3-10　计算去除重复数据后的数据行数

shop=# SELECT COUNT(DISTINCT product_type) FROM Product;
 count
-------
     3
(1 row)

shop=# SELECT DISTINCT product_type FROM Product;
 product_type
--------------
 衣服
 办公用品
 厨房用具
(3 rows)

shop=#

注意：这时 DISTINCT 必须写在括号中。

不仅限于 COUNT 函数，所有的聚合函数都可以使用 DISTINCT。

3-2 对表进行分组

GROUP BY子句

语法3-1　使用GROUP BY子句进行汇总
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……

代码清单3-13　按照商品种类统计数据行数

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

执行结果：

 product_type | count
--------------+-------
 衣服         |     2
 办公用品     |     2
 厨房用具     |     4
(3 rows)

GROUP BY 子句中指定的列称为聚合键或者分组列。

GROUP BY 子句也和 SELECT 子句一样，可以通过逗号分隔指定多列。

shop=# SELECT product_type,regist_date, COUNT(*) FROM Product GROUP BY product_type, regist_date;
 product_type | regist_date | count
--------------+-------------+-------
 衣服         |             |     1
 厨房用具     | 2008-04-28  |     1
 衣服         | 2009-09-20  |     1
 办公用品     | 2009-11-11  |     1
 厨房用具     | 2009-01-15  |     1
 办公用品     | 2009-09-11  |     1
 厨房用具     | 2009-09-20  |     2
(7 rows)

shop=#

▲ 子句的书写顺序（暂定） 1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

聚合键中包含NULL的情况

聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来。

使用WHERE子句时GROUP BY的执行结果

语法3-2　使用WHERE子句和GROUP BY子句进行汇总处理
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
WHERE <条件表达式>
GROUP BY <列名1>, <列名2>, <列名3>, ……;

GROUP BY 和 WHERE 并用时，SELECT 语句的执行顺序如下所示。

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

与聚合函数和GROUP BY子句有关的常见错误

常见错误① ——在SELECT子句中书写了多余的列
实际上，使用聚合函数时，SELECT 子句中只能存在以下三种元素。
- 常数
- 聚合函数
- GROUP BY子句中指定的列名（也就是聚合键）

常见错误② ——在GROUP BY子句中写了列的别名

代码清单3-17　GROUP BY子句中使用列的别名可能会引发错误

shop=# SELECT product_type AS pt, COUNT(*)
shop-#  FROM Product
shop-#  GROUP BY pt;
    pt    | count
----------+-------
 衣服     |     2
 办公用品 |     2
 厨房用具 |     4
(3 rows)

shop=#

SELECT 子句在 GROUP BY 子句之后执行。在执行 GROUP BY 子句时，SELECT 子句中定义的别名，DBMS 还并不知道。

PostgreSQL 执行上述 SQL 语句并不会发生错误，但是这样的写法在其它 DBMS 中并不通用。

常见错误③——GROUP BY子句的结果能排序吗 --> 随机的

常见错误④——在WHERE子句中使用聚合函数 --> 使用HAVING

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

重点：

- 使用COUNT函数等对表中数据进行汇总操作时，为其指定条件的不是WHERE子句，而是HAVING子句。
- 聚合函数可以在SELECT子句、HAVING子句和ORDER BY子句中使用。
- HAVING子句要写在GROUP BY子句之后。
- WHERE子句用来指定数据行的条件，HAVING子句用来指定分组的条件。

HAVING子句

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

▲ 使用 HAVING 子句时 SELECT 语句的顺序 SELECT → FROM → WHERE → GROUP BY → HAVING

代码清单3-20　从按照商品种类进行分组后的结果中，取出“包含的数据行数为2 行”的组

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

HAVING子句的构成要素

HAVING 子句中能够使用的 3 种要素：
- 常数
- 聚合函数
- GROUP BY子句中指定的列名（即聚合键）

相对于HAVING子句，更适合写在WHERE子句中的条件

有些条件既可以写在 HAVING 子句当中，又可以写在 WHERE 子句当中。这些条件就是聚合键所对应的条件。

代码清单3-25　将条件书写在HAVING子句中的情况

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

代码清单3-26　将条件书写在WHERE子句中的情况

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

建议写在 WHERE 子句之中。

（1）WHERE 子句和 HAVING 子句的作用不同。
WHERE 子句 = 指定行所对应的条件
HAVING 子句 = 指定组所对应的条件

（2）通常情况下，为了得到相同的结果，将条件写在 WHERE 子句中要比写在 HAVING 子句中的处理速度更快，返回结果所需的时间更短。
使用 COUNT 函数等对表中的数据进行聚合操作时，DBMS 内部就会进行排序处理。通过 WHERE子句指定条件时，由于排序之前就对数据进行了过滤，因此能够减少排序的数据量。通过 HAVING 子句是在排序之后才对数据进行分组的。

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

ORDER BY子句

语法3-4　ORDER BY子句
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
ORDER BY <排序基准列1>, <排序基准列2>, ……

▲ 子句的书写顺序 1. SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 → 5. HAVING 子句 → 6. ORDER BY 子句

指定升序或降序

代码清单3-29　按照销售单价由高到低（降序）进行排列

SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price DESC;

ASC 和 DESC 这两个关键字是以列为单位指定的，可以同时指定一个列为升序，指定其他列为降序;默认使用升序进行排序。

指定多个排序键

规则是优先使用左侧的键，如果该列存在相同值的话，再接着参考右侧的键。

NULL的顺序

使用含有 NULL 的列作为排序键时，NULL 会在结果的开头或末尾汇总显示。

在排序键中使用显示用的别名

代码清单3-32　ORDER BY子句中可以使用列的别名

SELECT product_id AS id, product_name, sale_price AS sp, purchase_price
FROM Product
ORDER BY sp, id;

不能在 GROUP BY 子句中使用的别名，为什么可以在 ORDER BY子句中使用呢？
▲使用 HAVING 子句时 SELECT 语句的顺序
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
这只是一个粗略的总结，具体的执行顺序根据 DBMS 的不同而不同
SELECT 子句的执行顺序在 GROUP BY 子句之后，ORDER BY 子句之前。因此，在执行 GROUP BY 子句时，SELECT 语句中定义的别名无法被识别。

ORDER BY子句中可以使用的列

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

代码清单3-34　ORDER BY子句中也可以使用聚合函数

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

4 数据更新

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

重点：
- 使用INSERT语句可以向表中插入数据（行）。原则上，INSERT语句每次执行一行数据的插入。
- 将列名和值用逗号隔开，分别括在（）内，这种形式称为清单。
- 对表中所有列进行INSERT操作时可以省略表名后的列清单。
- 插入NULL时需要在VALUES子句的值清单中写入NULL。
- 可以为表中的列设定默认值（初始值），默认值可以通过在CREATE TABLE语句中为列设置DEFAULT约束来设定。
- 插入默认值可以通过两种方式实现，即在INSERT语句的VALUES子句中指定DEFAULT关键字（显式方法），或省略列清单（隐式方法）。
- 使用INSERT…SELECT可以从其他表中复制数据。

INSERT语句的基本语法

语法4-1　INSERT语句
INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);

ProductIns建表语句

CREATE TABLE ProductIns
(product_id     CHAR(4)      NOT NULL,
 product_name   VARCHAR(100) NOT NULL,
 product_type   VARCHAR(32)  NOT NULL,
 sale_price     INTEGER      DEFAULT 0,
 purchase_price  INTEGER      ,
 regist_date    DATE         ,
 PRIMARY KEY (product_id));

列清单的省略

对表进行全列 INSERT 时，可以省略表名后的列清单。

-- 包含列清单
INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
-- 省略列清单
INSERT INTO ProductIns VALUES ('0006', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');

插入NULL

INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES子句的值清单中写入 NULL。

代码清单4-4　向purchase_price列中插入NULL

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0007', '叉子',  '厨房用具', 500, NULL, '2009-09-20');

注意：想要插入 NULL 的列一定不能设置 NOT NULL 约束

插入默认值

可以通过在创建表的CREATE TABLE 语句中设置 DEFAULT 约束来设定默认值。

代码清单4-5　创建ProductIns表的CREATE TABLE语句（节选）

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

如果在创建表的同时设定了默认值，就可以在 INSERT 语句中自动为列赋值了。
（1）通过显式方法插入默认值

代码清单4-6　通过显式方法设定默认值

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

（2）通过隐式方法插入默认值
插入默认值时也可以不使用 DEFAULT 关键字，只要在列清单和VALUES 中省略设定了默认值的列就可以了。
代码清单4-7　通过隐式方法设定默认值

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

执行结果：

shop=# SELECT * FROM ProductIns;
 product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
 0005       | 高压锅       | 厨房用具     |       6800 |           5000 | 2009-01-15
 0006       | 高压锅       | 厨房用具     |       6800 |           5000 | 2009-01-15
 0007       | 叉子         | 厨房用具     |        500 |                | 2009-09-20
 0008       | 擦菜板       | 厨房用具     |          0 |            790 | 2009-04-28
 0009       | 擦菜板       | 厨房用具     |          0 |            790 | 2009-04-28
(5 rows)

shop=#

从其它表中复制数据

要插入数据，除了使用 VALUES 子句指定具体的数据之外，还可以从其它表中复制数据。

代码清单4-9　创建ProductCopy表的CREATE TABLE语句

-- 用来插入数据的商品复制表
CREATE TABLE ProductCopy
(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));

代码清单4-10　INSERT ... SELECT语句

-- 将商品表中的数据复制到商品复制表中
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;

INSERT语句的SELECT语句中，可以使用WHERE子句或者GROUP BY子句等SQL语法

代码清单4-11　创建ProductType表的CREATE TABLE语句
该表是用来存储根据商品种类（product_type）计算出的销售单价合计值以及进货单价合计值的表

-- 根据商品种类进行汇总的表 ；
CREATE TABLE ProductType
(product_type VARCHAR(32) NOT NULL,
 sum_sale_price INTEGER ,
 sum_purchase_price INTEGER ,
 PRIMARY KEY (product_type));

代码清单4-12　插入其他表中数据合计值的INSERT ... 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;

执行结果：

shop=# SELECT * FROM ProductType;
 product_type | sum_sale_price | sum_purchase_price
--------------+----------------+--------------------
 衣服         |           5000 |               3300
 办公用品     |            600 |                320
 厨房用具     |          11180 |               8590
(3 rows)

shop=#

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

DELETE语句的基本语法

语法4-2　保留数据表，仅删除全部数据行的DELETE语句
DELETE FROM <表名>;

指定删除对象的DELETE语句（搜索型DELETE）

语法4-3　删除部分数据行的搜索型DELETE
DELETE FROM <表名> WHERE <条件>;

删除和舍弃

标准 SQL 中用来从表中删除数据的只有 DELETE 语句。但是，很多数据库产品中还存在另外一种被称为 TRUNCATE 的语句。

语法4-A　只能删除表中全部数据的TRUNCATE语句
TRUNCATE <表名>;
与 DELETE 不同的是，TRUNCATE 只能删除表中的全部数据。其处理速度比 DELETE 要快得多。因此需要删除全部数据行时，使用 TRUNCATE 可以缩短执行时间。

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

UPDATE语句的基本语法

语法4-4　改变表中数据的UPDATE语句
UPDATE <表名> SET <列名> = <表达式>;

指定条件的UPDATE语句（搜索型UPDATE）

语法4-5　更新部分数据行的搜索型UPDATE
UPDATE <表名> SET <列名> = <表达式> WHERE <条件>;

使用NULL进行更新

使用 UPDATE 也可以将列更新为 NULL（该更新俗称为 NULL 清空）。

代码清单4-17　将商品编号为0008的数据（圆珠笔）的登记日期更新为NULL

UPDATE Product SET regist_date = NULL WHERE product_id = '0008';

注意：只有未设置 NOT NULL 约束和主键约束的列才可以清空为NULL。

多列更新

方法①：代码清单4-19

-- 使用逗号对列进行分隔排列
UPDATE Product
SET sale_price = sale_price * 10,
purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';

方法②：代码清单4-20

-- 将列用()括起来的清单形式
UPDATE Product
SET (sale_price, purchase_price) = (sale_price * 10, 
purchase_price / 2)
WHERE product_type = '厨房用具';

4-4 事务

事务就是需要在同一个处理单元中执行的一系列更新处理的集合。

创建事务

如果想在 DBMS 中创建事务，可以按照如下语法结构编写 SQL 语句。
语法4-6　事务的语法

事务开始语句;
 DML语句①;
 DML语句②;
 DML语句③;
 . . .
事务结束语句（COMMIT或者ROLLBACK）;

代码清单4-21　更新商品信息的事务
SQL Server PostgreSQL

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

MySQL

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;

Oracle DB2

-- 将运动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;

COMMIT 提交处理
ROLLBACK 取消处理

ACID特性

原子性（Atomicity）
原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有。

一致性（Consistency）
一致性也称为完整性。一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者 NOT NULL 约束等。例如，设置了 NOT NULL 约束的列是不能更新为 NULL 的，试图插入违反主键约束的记录就会出错，无法执行。对事务来说，这些不合法的 SQL 会被回滚。

隔离性（Isolation）
隔离性指的是保证不同事务之间互不干扰的特性。

持久性（Durability）
持久性也可以称为耐久性，指的是在事务（不论是提交还是回滚）结束后，DBMS 能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复。
保证持久性的方法根据实现的不同而不同，其中最常见的就是将事务的执行记录保存到硬盘等存储介质中（该执行记录称为日志）。当发生故障时，可以通过日志恢复到故障发生前的状态。

附：

法则 2-1 星号（*）代表全部列的意思。
法则 2-2 设定汉语别名时需要使用双引号（"）括起来。
法则 2-3 在SELECT语句中使用DISTINCT可以删除重复行。
法则 2-4 WHERE子句要紧跟在FROM子句之后。
法则 2-8 字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。
法则 2-9 希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。
法则 2-11 多个查询条件进行组合时，需要使用AND运算符或者OR运算符。
法则 2-13 AND运算符的优先级高于OR运算符。想要优先执行OR运算符时可以使用括号。

法则 3-1 COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。
法则 3-2 聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。
法则 3-3 MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。
法则 3-4 想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。
法则 3-5 在聚合函数的参数中使用DISTINCT，可以删除重复数据。
法则 3-6 GROUP BY就像是切分表的一把刀。
法则 3-7 SQL子句的顺序不能改变，也不能互相替换。
法则 3-8 聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来。
法则 3-9 使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名。
法则 3-10 在GROUP BY子句中不能使用SELECT子句中定义的别名。
法则 3-11 GROUP BY子句结果的显示是无序的。
法则 3-12 只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。
法则 3-13 HAVING子句要写在GROUP BY子句之后。
法则 3-14 聚合键所对应的条件不应该书写在HAVING子句当中，而应该书写在WHERE子句当中。
法则 3-15 ORDER BY子句通常写在SELECT语句的末尾。
法则 3-16 未指定ORDER BY子句中排列顺序时会默认使用升序进行排列。
法则 3-17 排序键中包含NULL时，会在开头或末尾进行汇总。
法则 3-18 在ORDER BY子句中可以使用SELECT子句中定义的别名。
法则 3-19 在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。

法则 4-2 省略INSERT语句中的列名，就会自动设定为该列的默认值（没有默认值时会设定为NULL）。
法则 4-3 INSERT语句的SELECT语句中，可以使用WHERE子句或者GROUP BY子句等任何 SQL语法（但使用ORDER BY子句并不会产生任何效果）。
法则 4-4 DELETE语句的删除对象并不是表或者列，而是记录（行）
法则 4-6 使用UPDATE语句可以将值清空为NULL（但只限于未设置NOT NULL约束的列）。
法则 4-7 事务是需要在同一个处理单元中执行的一系列更新处理的集合。
法则 4-8 虽然我们可以不清楚事务开始的时间点，但是在事务结束时一定要仔细进行确认。