SQL 学习记·上

2019-10-07

最近在学数据库，主要看的是《SQL 基础教程》，书中介绍了操作关系型数据库的 SQL 语言的使用方法。虽然书不是很厚（320 页）但为了避免文章过长便把笔记分为上下两部分，这篇主要记的是前四章的学习笔记。另外，在 Life of Py 中有做汇总。

介绍

SQL 是结构化查询语言，全称 Structured Query Language，用于访问和操作关系型数据库中的数据，是关系型数据库管理系统（RDSMS）标准语言，支持的主要数据库有: MySQL、PostgreSQL、SQLServer、Oracle、DB2，书中主要使用 PostgreSQL 讲解。

准备

安装

书里面讲的是在 Windows 的安装方法，但我用的是 MacBook Pro，所以这里仅记录 macOS 的安装过程。

Terminal window
// 安装 PostgreSQL
brew install postgres
// 完成后初始化
initdb /usr/local/var/postgres

初始化完毕之后，可以启动数据库开始使用：

Terminal window
// 启动
pg_ctl -D /usr/local/var/postgres start
// 关闭
pg_ctl -D /usr/local/var/postgres stop

创建

如果不想使用默认数据库，那么在连接之前可以先创建数据库：

Terminal window
// 创建新数据库
createdb <数据库名> -e
// 查看已创建数据库
psql -l

上面的操作会创建一个指定名称的数据库，-e 的作用是显示数据库的操作过程。

另外如果想删除数据库，可以使用命令：

Terminal window
// 删除 postgres 数据库
dropdb postgres

连接

直接在终端中操作，只要知道连接的数据库名称即可：

Terminal window
// 使用默认的用户名（即当前登陆的账号）连接
psql postgres
// 指定主机和用户名
psql -h <主机> -p <端口> -U <用户名> -d <数据库名>

如果需要连接其他数据库管理软件，默认的信息：

  • 主机：localhost / 127.0.0.1
  • 端口：5432
  • 初始化数据库：postgres
  • 用户名：计算机用户名
  • 密码：电脑密码

基础操作

对数据库、表、列及数据进行增删改查。

数据库

创建数据库

-- 语法
CREATE DATABASE <数据库名称>;
-- 示例
CREATE DATABASE shop;

数据库、表和列的名称只能使用英文、数字和下划线 _，而且必须以英文开头，此外数据库的名称不能重复。

删除数据库

-- 语法
DROP DATABASE <数据库名>;
-- 示例
DROP DATABASE shop;

更改数据库名

-- 不能修改当前登陆的数据库名
ALTER DATABASE <数据库名> RENAME TO <新数据库名>;

表

创建表

-- 语法
CREATE TABLE <表名>(
    <列名1> <数据类型> <该列所需约束>,
    <列名2> <数据类型> <该列所需约束>,
                ...
    <该表的约束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_prise INTEGER,
    regist_date    DATE,
    PRIMARY KEY (product_id)
);
  • 数据类型（必须指定）
    • INTEGER：整数；
    • CHAR：字符串，可用括号 (42) 指定长度；
    • VARCHAR：可变字符串；
    • DATE：日期；

删除表

-- 语法
DROP TABLE <表名>;
-- 示例
DROP TABLE Product;

更改表名

-- Oracle PostgreSQL
ALTER TABLE <原表名> RENAME TO <新表名>;
-- DB2
RENAME TABLE <原表名> TO <新表名>;
-- MySQL
RENAME TABLE <原表名> to <新表名>;
-- SQL Server
sp_rename <'原表名'>, <'新表名'>;

列

新增列

-- 语法
ALTER TABLE <表名> ADD COLUMN <列名> <数据类型>;
-- Oracle 和 SQL Server 无需 COLUMN
ALTER TABLE <表名> ADD <列名> <数据类型>;
-- Oracle 支持多列
ALTER TABLE <表名> ADD (<列名> <数据类型>, ...);

删除列

-- 语法
ALTER TABLE <表名> DROP COLUMN <列名>;
-- 示例
ALTER TABLE Product DROP COLUMN product_name_pinyin;

更改列名

-- 语法
ALTER TABLE <表名> RENAME COLUMN <列名> TO <新列名>;
-- 示例
ALTER TABLE Product RENAME COLUMN regist_date TO date;

数据

新增数据

-- 语法
INSERT INTO <表名> (列1, 列2, ...) VALUES (值1, 值2, ...);
-- 实际可以省略列名清单
INSERT INTO <表名> VALUES (值1, 值2, ...);


-- 示例，NULL 可以直接插入
INSERT INTO Product VALUES ('0001', '衣服', 1000, NULL, '2019-10-24');


-- 如果有默认值可以用 DEFAULT，或者直接省略
INSERT INTO <表名> VALUES (值1, DEFAULT, ...);

同时添加多行

-- 不支持 Oracle
INSERT INTO <表名> VALUES (值1, 值2, ...),
                          (值1, 值2, ...), ...;

从其他表复制数据

-- 语法
INSERT INTO <表名> (列1, 列2, ...)
SELECT              列1, 列2, ...
FROM        <被复制表名>;

删除数据

-- 删除数据会保留表
DELETE FROM <表名>;


-- 用 WHERE 指定条件删除
DELETE FROM <表名>
WHERE       <条件>;

DELETE 不能使用 GROUP BY、HAVING 和 ORDER BY 子句，只能使用 WHERE 子句；

更新数据

-- 语法，将会同时更改指定列的所有值
UPDATE <表名>
SET    <列名> = <值>, ...;


-- 用 WHERE 指定条件更改
UPDATE <表名>
SET    <列名> = <值>
WHERE  <条件>;

查询数据

-- 语法，最后一个列名不需要逗号
SELECT <列名1>, <列名2>, ...
FROM   <表名>;
-- 示例
SELECT product_id, product_name, purchase_price
FROM   Product;


-- 查询所有的列
SELECT *
FROM   <表名>;

子句

子句（clause）是 SQL 语句的组成要素，一般以 SELECT 或者 FROM 等作为起始的短语。

  • 书写顺序：SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY
  • 执行顺序：FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

注意：

  1. 别名只能在 SELECT 和 ORDER BY 中使用；
  2. 聚合函数只能在 SELECT、HAVING 和 ORDER BY 中使用；
  3. 子句的书写顺序固定，不能随意更改；

SELECT 子句

设定别名

-- 设定别名，无需单引号
SELECT product_id AS id,
       product_name AS name,
       purchase_price AS price
FROM   Product;


-- 设定中文别名，需用双引号
SELECT product_id AS "编号",
       product_name AS "名称",
       product_price AS "单价"
FROM   Product;


-- 使用常数，需用单引号包裹
-- 如果不设置别名，默认为 ?column?
SELECT '商品' AS string,
       product_name AS name,
       purchase_price AS price
FROM   Product;

删除重复行

使用 DISTINCT 关键字可以从结果中删除重复数据。

-- DISTINCT 只能用在第一个列名之前；
SELECT DISTINCT <列名>
FROM            <表名>;

算术运算符

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

上面的操作将会对第三列的数据乘以二的操作，并设定列名为 sale_price_x2，如果未指定列名，默认为 ?column?。

同样可以使用加减乘除对数据进行操作，以及使用括号提升优先级。另外需要注意的两点是，算术运算符只能对数字类型进行操作，包含 NULL 的计算，结果为 NULL。

聚合函数

用于汇总的函数称为聚合函数，所谓聚合，就是将多行汇总为一行，常用的函数：

  • COUNT：计算表中的行数；
  • SUM：计算表中数值列中数据的合计值；
  • AVG：计算表中数值列中数据的平均值；
  • MAX：求出表中任意列中数据的最大值；
  • MIN：求出表中任意列中数据的最小值；
-- 查看全部数据的行数，包含 NULL
SELECT COUNT(*)
FROM   Product;
-- 使用 AVG 计算平均价格
SELECT AVG(sale_price)
FROM   Product;
-- 使用关键字 DISTINCT 删除重复后使用聚合函数
SELECT COUNT(DISTINCT product_type)
FROM   Product;

在计算平均价格时，默认会排除 NULL，也就是说如果 10 条数据中有两条 NULL，则会除以 8，而非 10。

使用关键字 DISTINCT 时必须写在括号中（所有的聚合函数均可使用）。

WHERE 子句

WHERE 子句可以查询指定数据的条件。

-- 语法
SELECT <列名>, ...
FROM   <表名>
WHERE  <条件>;
-- 查询某列中指定数据
SELECT product_name, product_type
FROM   Product
WHERE  product_type = '衣服';

上面是查询 product_type 列中类型为衣服的数据，WHERE 中的列名并不一定要包含在 SELECT 的列名中，因为是先执行 WHERE 子句查询符合指定条件的记录，然后再选取 WHERE 语句指定的列。

比较运算符

除了前面使用的 = 号比较运算符之外，还有：

  • 相等：= /
  • 不等：<>
  • 大于：>
  • 小于：<
  • 大于等于：>=
  • 小于等于：<=

此外，在对数据进行比较之前，还可以使用算术运算符进行计算。

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

上面的语句将筛选出销售单价 (sale_price) 比进货单价 (purchase_price) 高出 500（含 500） 以上的数据。

另外，当对字符串进行比较的时候，将会按照字典顺序进行排序，而非数字大小。

逻辑运算符

使用逻辑运算符可以将多个查询条件进行组合；

  • NOT：表示否定；
  • AND：两边为真，才成立；
  • OR：一变为真，即为真
-- 选出类型为非衣服的所有数据
SELECT    product_name, product_type
FROM      Product
WHERE NOT product_type = '衣服';

上面的语句本来选取的是类型为衣服的数据，但使用 NOT 之后，选取的是类型中所有非衣服的数据。

-- 选出类型为厨房用具，并且价格大于等于 3000 的数据
SELECT product_name, product_type, sale_price
FROM   Product
WHERE  product_type = '厨房用具'
AND    sale_price >= 3000;

上面的语句将筛选出同时满足两个条件的数据，OR 的使用方法和 AND 类似，区别在于只要满足其中一个条件即可。

GROUP BY 子句

GROUP BY 子句可以对数据进行汇总。

-- 语法，GROUP BY 中的列需要和 SELECT 保持一致
SELECT   <列名>...
FROM     <表名>
GROUP BY <列名>...;
-- 示例
SELECT   product_type, count(*)
FROM     Product
GROUP BY product_type;

即使数据为 NULL，GROUP BY 同样可以进行汇总。

使用 WHERE 子句进行过滤

使用 WHERE 子句进行汇总处理时,会先根据 WHERE 子句指定的条件进行过滤，然后再进行汇总处理。

-- 语法
SELECT   <列名>...
FROM     <表名>
WHERE    <条件>
GROUP BY <列名>...;
-- 示例，汇总售价 1000 以上的商品类型
SELECT   product_type, count(*)
FROM     Product
WHERE    sale_price >= 1000
GROUP BY product_type;

HAVING 子句

HVAING 子句可以对集合的数据进行筛选。

-- 语法
SELECT   <列名>, ...
FROM     <表名>
GROUP BY <列名>, ...
HAVING   <分组条件>
-- 示例，将取出汇总数据为 2的分组
SELECT   product_type, COUNT(*)
FROM     Product
GROUP BY product_type
HAVING   COUNT(*) = 2;

ORDER BY 子句

-- 语法
SELECT   <列名>, ...
FROM     <表名>
ORDER BY <基准列>, ...;
-- 示例，按商品类型进行排序，默认升序
SELECT   product_type, product_name
FROM     Product
ORDER BY product_type;
-- 可以使用多个基准列
SELECT   product_type, sale_price
FROM     Product
ORDER BY product_type, sale_price;

虽然上面的代码 ORDER BY 语句出现的列，均包含在 SELECT 中，但实际上 ORDER BY 可以使用表中的任意列，不一定要在 SELECT 出现。

此外在 GROUP BY 中可以使用别名，因为它的执行顺序在 SELECT 之后：

SELECT   product_type AS "类型", product_name AS Name
FROM     Product
ORDER BY "类型";

事务

事务是需要在同一个处理单元中执行的一系列操作的集合，类似于编程中的函数。

-- 语法 SQL Server、PostgreSQL
BEGIN TRANSACTION
    <语句1>
    <语句2>
      ...
COMMIT;
-- 语法 MySQL
START TRANSACTION
    <语句1>
    <语句2>
      ...
COMMIT;

事务特性：

DBMS 的事务遵循的四种特性，四种特性的首字母结合起来统称为 ACID 特性：

  1. 原子性 (Atomicity)：指在事务结束时，其中的操作要么全部执行，要么完全不执行；
  2. 一致性 (Consistency)：也称完整性，指在事务中包含的处理要满足数据库提前设置的约束；
  3. 隔离性 (Isolation)：指保证不同事务之间互不干扰，保证事务之间不会互相嵌套；
  4. 持久性 (Durability：也称耐久性，指在事务结束后，能够保证该时间点的数据状态会被保存。

信息

版本

  • PostgreSQL 11.5_1

参考

  • 《SQL 基础教程·第二版》
# SQL
# Database
# 数据库