​

SQL 语句的类型介绍

SQL 语句的语法

一个 SQL 语句通常包含了关键字、标识符（字段）、常量、特殊符号等，下面是一个简单的 SQL 语句：

SELECT id,name FROM test

数据定义语言 DDL

1. CREATE

DDL 就是我们在创建表时用到的一些 SQL 语句。例如：CREATE、ALTER、DROP、TRUNCATE等。DDL 主要是用在定义表或者改变表的结构、数据类型、表之间的连接和约束等初始化操作上。

• 创建用户

语法： CREATE USER 名称[[WITH]选项[ … ]]

例：创建 test_user 用户并设置用户密码为 test_user。

create user test_user password ‘test_user’;

• 创建数据库

语法： CREATE DATABASE <数据库名>;

例：

CREATE DATABASE test_db OWER test_user;

• 创建表
  CREATE TABLE 是一个关键字，用于告诉数据库系统将创建一个数据表，表名字必须在统一模式中的其他表、序列、索引、视图或外部表名字中唯一。
  语法：

CREATE TABLE <表名>(
字段 1 数据类型[约束],
字段 2 数据类型[约束],
… …
字段 n 数据类型[约束]
); --注：除最后一个字段外，每个字段都需要用逗号隔开

例：创建表时指定主键

CREATE TABLE t_test1 ( id int PRIMARY KEY NOT NULL,
name varchar(50),
age int NOT NULL
);

• 创建模式
  模式可以看做是一个表的集合。一个模式可以包含视图、索引、数据类型、函数和操作符等。相同的对象名称可以被用于不同的模式中而不会出现冲突。
  使用模式的优势：
  • 允许多个用户使用一个数据库并且不会相互干扰。
  • 将数据库对象组织成逻辑组以便更容易管理。
  • 第三方应用的对象可以放在独立的模式中，这样他们就不会与其他对象的名称发生冲突。
    模式类似于操作系统层的目录，但是模式不能嵌套。
    语法：

CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] 
[schema_element [...]]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element 
[...]]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION 
role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification
--这里的 role_specification 可以是 user_name|CURRENT_USER|SESSION_USER

例 1：创建一个名为 my_schema 的模式

CREATE SCHEMA IF NO EXISTS my_schema;

例 2：创建一个与当前用户名同名的模式

CREATE SCHEMA IF NO EXISTS AUTHORIZATION current_user;

• 创建索引
  索引是加速搜索引擎检索数据的一种特殊表查询。简单地说，索引是一个指向表中数据的指针。
  索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时
  的数据输入。索引可以创建或删除，但不会影响数据。
  使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指
  示索引是升序排列。索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重
  复条目。
  创建索引的语法如下：

CREATE INDEX index_name ON table_name;
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] 
ON [ ONLY ] table_name [ USING method ]
 ( { column_name | ( expression ) } [ COLLATE collation ] 
[ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] 
[, ...] )
 [ INCLUDE ( column_name [, ...] ) ]
 [ WITH ( storage_parameter = value [, ... ] ) ]
 [ TABLESPACE tablespace_name ]
 [ WHERE predicate ]
UNIQUE：创建唯一索引
CONCURRENTLY ：并行创建索引，在构建索引时不阻塞写操作
Method：索引方法，默认是 Btree 索引
COLLATE collation：索引的排序规则
ASC：升序排序（默认）
DESC：下降排序
NULLS FIRST：空值排序在非空值前面。当指定 DESC 时这是默认行为
NULLS LAST：空值排序在非空值后面。当没有指定 DESC 时这是默认行为

• 单列索引：
  单列索引是一个只基于表的一个列上创建的索引，基本语法如下：

CREATE INDEX index_name 
ON table_name(column_name);

• 组合索引
  组合索引也叫多列索引，是基于表的多列上创建的索引。只有 Btree、GiST、GIN、BRIN 类
  型的索引支持建多列索引；一个多列索引最多支持 32 个字段字段；Btree 类型的多列索引，
  支持从左向右的顺序匹配各个索引字段。基本语法如下：

CREATE INDEX index_name 
ON table_name(column1_name, column2_name);

不管是单列索引还是组合索引，该索引必须是在 WHERE 子句的过滤条件中使用非常频繁的
列。如果只有一列被使用到，就选择单列索引，如果有多列就是用组合索引。

• 唯一索引
  使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插到表里。唯一索引可以保证某个字段值是唯一的，也可以保证多个字段组合值是唯一的，其中字段中的 null 值被认为是不同的值。
  唯一索引是 Btree 类型的索引，对于表中有唯一性约束或主键约束的字段（或字段组合）来说。
  基本语法如下：

CREATE UNIQUE INDEX index_name 
ON table_name(column_name);

• 局部索引
  局部索引是在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行。基本语法如下：

CREATE INDEX index_name 
ON table_name(conditional_expression);

• 表达式索引：
  表达式索引可以使用一个函数或表达式的计算结果作为索引的字段。被索引的函数或表达式要用圆括号括起来。表达式索引适用于查询速度远比插入更新速度要求高的场景，不适用于插入更新频繁的情况。
  例：

CREATE INDEX name_low_idx ON employee(LOWER(name));
EXPLAIN ANALYZE SELECT * FROM employee WHERE LOWER(name)='abc100';

• 隐式索引
  隐式索引是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

2. ALTER

• 修改用户
  语法：

ALTER USER CURRENT_USER|SESSION_USER 的名称 [WITH]选项[ … ]
ALTER USER 名称 RENAME TO 新的名称
ALTER USER {role_specification | ALL} [ IN DATABASE 数据库名称 ] SET 
配置参数{ TO | =} {值 | DEFAULT}
ALTER USER {role_specification | ALL} [ IN DATABASE 数据库名称 ] SET 
配置参数 FROM CURRENT
ALTER USER {role_specification | ALL} [ IN DATABASE 数据库名称 ] 
RESET 配置参数
ALTER USER {role_specification | ALL} [ IN DATABASE 数据库名称 ] 
RESET ALL

例 1：修改用户密码

ALTER USER test_user PASSWORD ‘test123’;

例 2：修改用户权限

ALTER USER test_user SUPERUSER;

将用户 test_user 的权限修改为超级用户权限
例 3：修改用户密码有效期

ALTER USER test_user valid until’2020-04-17 00:00:00’;

• 修改数据库
  语法：

 ALTER DATABASE 名称 [[WITH] 选项 […]]

例 1：修改数据库名

ALTER DATABASE test_db RENAME TO t_db;

例 2：修改数据库属主

ALTER DATABASE t_db OWNER TO postgres; 

将数据库 t_db 的属主修改为 postgres（对比例一查看，属主由 test_user 重置为 postgres）

• 修改表

语法：ALTER TABLE [IF EXISTS] [ONLY] 名称[*] 操作[, …]
``
例 1：修改表名
```sql
ALTER TABLE t_test_1 RENAME TO t_test;

例 2：新增字段

ALTER TABLE t_test ADD COLUMN code numeric;

给表 t_test 新增一个名为 code，类型为 numeric 的表字段

• 修改模式

语法：ALTER SCHEMA 名称 RENAME TO 新的名称
ALTER SCHEMA 名称 OWNER TO {新的属主 | CURRENT_USER | SESSION_USER}

例 1：修改模式名

ALTER SCHEMA my_schema RENAME TO test_schema;
将名为 my_schema 的模式名重命名为 test_schema。

例 2：修改模式属主

ALTER SCHEMA test_schema OWNER TO postgres;

将模式 test_schema 的属主修改为 postgres 用户。

3. DROP

• 删除用户

语法：DROP USER [IF EXISTS] 名称 [, …]

例：若存在用户 test1_user 则删除该用户

DROP USER IF EXISTS test1_user;

• 删除数据库

DROP DATABASE [IF EXISTS] 名称

例：

DROP DATABASE IF EXISTS t_db;

• 删除表

语法：DROP TABLE [IF EXISTS] 名称 [, …] [CASCADE | RECASCADE]

例：

DROP TABLE IF EXISTS t_test;

• 删除模式
  语法：

DROP SCHEMA [IF EXISTS]名称[, …] [CASCADE | RECASCADE]

例：

DROP SCHEMA IF EXISTS test_schema;

4. TRUNCATE

删除表中数据的方式有 DELETE 和 TRUNCATE，其中 TRUNCATE TABLE 用于删除表中所有行，而不记录单个行删除操作。TRUNCATE TABLE 与没有 WHERE 子句的DELETE 语句类似；
但是，TRUNCATE TABLE 速度更快，使用的系统资源和事务日志资源更少。
TRUNCATE 是一个能够快速清空资料表内所有资料的 SQL 语法，并且能针对具有自动递增的字段，做计数重置归零重新计算的作用。
语法：

TRUNCATE TABLE table_name;

TRUNCATE TABLE table_name 立即释放磁盘空间。

TRUNCATE 使用注意事项：

1. TRUNCATE TABLE 功能上与不带 WHERE 子句的 DELETE 语句相同；二者均删除表中的
   全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
2. DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE
   TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释
   放。
3. TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行
   标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要
   删除表定义及其数据，请使用 DROP TABLE 语句。
4. 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE
   子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
5. TRUNCATE TABLE 不能用于参与了索引视图的表。
6. 对用 TRUNCATE TABLE 删除数据的表上增加数据时，要使用 UPDATE STATISTICS 来维
   护索引信息。
7. 如果有 ROLLBACK 语句，DELETE 操作将被撤销，但 TRUNCATE 不会撤销。

​