PostgreSQL11快速入门(六)-SQL语言基础

PostgreSQL

作为一名研发人员 , 熟练掌握 SQL 语言是与数据库交互的基础。有了前面几个章节的铺垫，这个章节将开始介绍 PostgreSQL 中的 SQL 语言 , 包括数据定义语言(DDL)、数据操纵语言(DML)、事务控制等内容。

1. 数据定义语言(DDL)

数据定义语言 ( Data Definition Language , DDL )用于定义和管理数据库对象 , 如数据库、模式、表、视图、索引等。下面来介是几个常用的 DDL 语句。

小提示： 大部分数据执行 DDL 是不需要执行 commit 的，记住这个可以更好的区分 DDL 跟 DML 的定义 (后面章节涉及)。

CREATE 语句用于创建数据库对象。例如 , 创建一个新的数据库:

CREATE DATABASE mydb;

创建一个新的表:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) UNIQUE,
  age INTEGER CHECK (age >= 18)
);

这里我们定义了一个 users 表 , 包含 id、name、email、age 四个字段 , 并设置了主键、唯一约束、检查约束等。

ALTER 语句用于修改已有的数据库对象。例如,给 users 表添加一个新的字段:

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

修改字段的数据类型:

ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

DROP 语句用于删除数据库对象。例如,删除 users 表:

DROP TABLE users;

删除 mydb 数据库:

DROP DATABASE mydb;

数据操纵语言(Data Manipulation Language, DML)用于操作和查询数据库中的数据。主要包括 INSERT、UPDATE、DELETE、SELECT 四种语句。

小提示： 大部分数据执行 DML 是需要执行 commit 的，记住这个可以更好的区分 DDL 跟 DML 的定义 ( DDL 在大部分数据库实现中是不需要 commit 的 )。

INSERT 语句用于向表中插入新的行。例如,向 users 表插入一条记录:

INSERT INTO users (name, email, age) 
VALUES ('Tom', 'tom@qq.com', 25);

我们也可以一次插入多条记录:

INSERT INTO users (name, email, age)
VALUES 
    ('Jack', 'jack@example.com', 30),
    ('Rose', 'rose@example.com', 28);

UPDATE 语句用于修改数据库表中的现有数据。例如将 Tom 的年龄更新为 26 岁:

UPDATE users SET age = 26 WHERE name = 'Tom';

这里的 WHERE 子句指定了需要更新的行的条件。如果省略 WHERE 子句,则会更新表中的所有行。

注意： 更新数据操作务必谨慎，大部分时候应该要指定更新条件，否则很危险！

DELETE 语句用于删除表中的行。例如删除 name 为 ‘Tom’ 的记录:

DELETE FROM users WHERE name = 'Tom';

同样,WHERE 子句指定了需要删除的行的条件。如果省略 WHERE 子句,则会删除表中的所有行。

注意： 删除数据操作务必谨慎，大部分时候应该要指定删除条件，否则数据删除后恢复比较麻烦！

SELECT 语句用于查询表中的数据。它是 SQL 中最常用、最强大的语句，也是我们在实际项目开发中写的最多的 SQL 语句。例如 , 查询 users 表中的所有数据:

SELECT * FROM users;

这里的 * 表示查询所有字段。我们也可以指定需要查询的字段:

SELECT name, email FROM users;

通过 WHERE 子句,我们可以指定查询条件:

SELECT * FROM users WHERE age > 25;

除了基本的查询外 , SELECT 语句还支持各种高级查询 , 如连接查询、嵌套查询、分组查询、排序等。这个将在后面的章节中详细介绍，如果要详细展开的话可以写好几个章节。

小提示： 实际项目中，尽量少使用 select * from xxx 语句，应该用到哪些列名显示写出来，比如 select user_name, pass_word from xxxx ，这样可以降低网络传输开销以及数据读取开销。

事务控制语言（Transaction Control Language , TCL）说的其实就是事务。

事务 (Transaction) 是数据库操作的基本单位。一个事务中可以包含多个 SQL 语句,要么全部执行成功,要么全部回滚。

PG 支持完整的 ACID 特性 , 保证了事务的可靠性和一致性，ACID 特性如下：

原子性 ( Atomicity ) : 原子性要求事务中的所有操作要么全部完成,要么全部不完成。如果事务执行过程中发生错误或异常,数据库会自动回滚 ( Rollback ) 事务,撤销已经执行的操作,使数据恢复到事务开始前的状态。
一致性 ( Consistency ) : 一致性要求事务执行前后,数据库始终保持一致的状态。这里的一致性包括数据的完整性约束,如主键、外键、非空、唯一、检查等。事务不能破坏这些约束条件,否则就会导致数据不一致。
隔离性 ( Isolation ): 隔离性要求多个事务并发执行时,每个事务看到的数据状态是一致的,不受其他事务的影响。也就是说,事务只能看到已经提交的数据 , 而不能看到未提交的数据变更。如果不同的事务看到了不同的数据状态,就会导致数据的不一致。
持久性 (Durability) : 事务一旦提交,其结果就会被永久保存到数据库中,即使系统崩溃或重启,已提交的数据也不会丢失。

PG 中的事务控制主要通过以下语句来实现:

例如 , 在一个事务中执行两条 SQL 语句:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2; 
COMMIT;

如果在执行过程中发生了错误,我们可以通过 ROLLBACK 来回滚事务:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 某些错误发生
ROLLBACK;

这个章节主要是讲解了 PG 中的 SQL 基础知识 , 主要包括: