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
1
// 安装 PostgreSQL
2
brew install postgres
3
// 完成后初始化
4
initdb /usr/local/var/postgres

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

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

创建

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

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

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

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

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

连接

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

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

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

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

基础操作

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

数据库

创建数据库

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

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

删除数据库

1
-- 语法
2
DROP DATABASE <数据库名>;
3
-- 示例
4
DROP DATABASE shop;

更改数据库名

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

表

创建表

1
-- 语法
2
CREATE TABLE <表名>(
3
    <列名1> <数据类型> <该列所需约束>,
4
    <列名2> <数据类型> <该列所需约束>,
5
                ...
6
    <该表的约束1>, <该表的约束2>, ...)
7
);
8


9
-- 示例
10
CREATE TABLE Product(
11
    product_id     CHAR(4)      NOT NULL,
12
    product_name   VARCHAR(100) NOT NULL,
13
    product_type   VARCHAR(32)  NOT NULL,
14
    sale_price     INTEGER,
15
    purchase_prise INTEGER,
16
    regist_date    DATE,
17
    PRIMARY KEY (product_id)
18
);
  • 数据类型（必须指定）
    • INTEGER：整数；
    • CHAR：字符串，可用括号 (42) 指定长度；
    • VARCHAR：可变字符串；
    • DATE：日期；

删除表

1
-- 语法
2
DROP TABLE <表名>;
3
-- 示例
4
DROP TABLE Product;

更改表名

1
-- Oracle PostgreSQL
2
ALTER TABLE <原表名> RENAME TO <新表名>;
3
-- DB2
4
RENAME TABLE <原表名> TO <新表名>;
5
-- MySQL
6
RENAME TABLE <原表名> to <新表名>;
7
-- SQL Server
8
sp_rename <'原表名'>, <'新表名'>;

列

新增列

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

删除列

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

更改列名

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

数据

新增数据

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


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


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

同时添加多行

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

从其他表复制数据

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

删除数据

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


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

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

更新数据

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


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

查询数据

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


8
-- 查询所有的列
9
SELECT *
10
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 子句

设定别名

1
-- 设定别名，无需单引号
2
SELECT product_id AS id,
3
       product_name AS name,
4
       purchase_price AS price
5
FROM   Product;
6


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


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

删除重复行

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

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

算术运算符

1
SELECT product_name,
2
       sale_price,
3
       sale_price * 2 AS sale_price_x2
4
FROM   Product;

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

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

聚合函数

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

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

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

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

WHERE 子句

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

1
-- 语法
2
SELECT <列名>, ...
3
FROM   <表名>
4
WHERE  <条件>;
5
-- 查询某列中指定数据
6
SELECT product_name, product_type
7
FROM   Product
8
WHERE  product_type = '衣服';

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

比较运算符

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

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

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

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

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

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

逻辑运算符

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

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

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

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

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

GROUP BY 子句

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

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

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

使用 WHERE 子句进行过滤

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

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

HAVING 子句

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

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

ORDER BY 子句

1
-- 语法
2
SELECT   <列名>, ...
3
FROM     <表名>
4
ORDER BY <基准列>, ...;
5
-- 示例，按商品类型进行排序，默认升序
6
SELECT   product_type, product_name
7
FROM     Product
8
ORDER BY product_type;
9
-- 可以使用多个基准列
10
SELECT   product_type, sale_price
11
FROM     Product
12
ORDER BY product_type, sale_price;

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

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

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

事务

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

1
-- 语法 SQL Server、PostgreSQL
2
BEGIN TRANSACTION
3
    <语句1>
4
    <语句2>
5
      ...
6
COMMIT;
7
-- 语法 MySQL
8
START TRANSACTION
9
    <语句1>
10
    <语句2>
11
      ...
12
COMMIT;

事务特性：

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

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

信息

版本

  • PostgreSQL 11.5_1

参考

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