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

介绍

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

准备

安装

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

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

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

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

创建

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

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

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

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

// 删除 postgres 数据库
dropdb postgres

连接

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

// 使用默认的用户名(即当前登陆的账号)连接
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 基础教程·第二版》