PostgreSql SQL 入门_pgsql入门

一、概述

  SQL（Structured Query Language）中文全称为”结构化查询语句“，在 1986 年成为 ANSI（American National Standards Institute 美国国家标准化组织）的一项标准，在 1987 年成为国际标准化组织（ISO）标准。

  SQL 是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。简单理解就是对我们数据库和数据库中的表进行”增删改查“操作的编程语言。虽然 SQL 是一门标准的计算机语言，但由于数据库类型繁多，存在着多种不同版本的 SQL 语言。为了与 ANSI 标准相兼容，它们必须以相似的方式共同地来支持一些主要的命令（比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等），下列为 postgresql 数据库操作示例。

  按照其功能，主要分为以下几种类型：

• DQL(Data QueryLanguage)：数据查询语句。SELECT。
• DML(Data Manipulation Language)：数据操纵语句。INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE。
• DDL(Data Definition Languages)：数据库定义语句。CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。
• TCL(Transaction Control Language)：事务控制语句。GRANT,REVOKE。

二、DQL

2.1 简单查询

• SQL 使用 SELECT 和 FROM 查询表中的数据，* 表示查询全部字段。
• PostgreSQL 提供了无表查询的扩展，使用 AS 指定别名;
• 使用 DISTINCT 去除查询结果中的重复值。
• PostgreSQL 支持两种格式的代码注释。
• PostgreSQL 使用 WHERE 子句过滤表中的数据。
• PostgreSQL 提供了以下比较运算符: =、!=、<>、>、>=、<、<=、BETWEEN、IN 等。

--转义
select E'I\'m student';
select $$I'm student$$;

select * from employees;

select version() as "数据库版本";

select distinct department_id,department_name from departments;

select first_name as "名字",last_name "姓氏" --我是单行注释
/*我是
多行
注释*/ from employees;

select * from employees where employee_id >= 150;

select * from employees where employee_id between 150 and 160;
select * from employees where employee_id in (150,160,170);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

2.2 字符串模糊匹配

• PostgreSQL 中的 LIKE 运算符可以实现字符串的模糊匹配。
• 百分号(%)匹配任意多个字符，下划线(_)匹配一个任意字符。
• ESCAPE 选项用于指定转义字符，反斜线()为默认转义字符。
• NOT LIKE 执行 LIKE 相反的匹配，ILIKE不区分大小写。

select last_name from employees where last_name like 'S%';

select last_name from employees where last_name like 'S_i%';

select last_name from employees where last_name like 'S#%%' escape '#';
select last_name from employees where last_name like 'S\%%';

select last_name from employees where last_name not like 'S%';
select last_name from employees where last_name not ilike '%S%';
1
2
3
4
5
6
7
8
9

2.3 空值判断

• 空值使用 NULL 表示，代表了未知或者缺失的数据。
• SQL使用 IS[NOT] NULL 运算符判断是否为空值。
• PostgreSQL 还支持 ISNULL 和 NOTNULL 运算符。
• IS[NOT] DISTINCT FROM 运算符支持空值的比较。

select * from employees where commission_pct is null;
select * from employees where commission_pct is not null;

select * from employees where commission_pct isnull;
select * from employees where commission_pct notnull;

select * from employees where commission_pct is distinct from null;
select * from employees where commission_pct is not distinct from null;
1
2
3
4
5
6
7
8

2.4 复杂查询条件

• 逻辑运算符 AND、OR、NOT 可以构造复杂查询条件。
• AND 和 OR 使用短路运算。
• 比较运算符 (=、>、< 等)的优先级比逻辑运算符高。
• NOT 比 AND 优先级高，AND 比 OR 优先级高，括号可以改变优先级。

select * from employees where first_name = 'Steven' and last_name = 'King';

select 1=1 or 1/0=1;
select 1=2 and 1/0=1;

select * from employees where (hire_date > '2008-01-01' or salary < 5000) and commission_pct is null;
1
2
3
4
5
6

2.5 数据的排序显示

• ORDER BY 用于指定数据的排序字段。
• ASC(默认值)表示升序排序，DESC 表示降序排序。
• PostgreSQL 排序时 NULL 被看作最大的值。
• PostgreSQL 可以使用 NULLS FIRST 或者 NULLS LAST 指定空值的位置。

select employee_id,first_name,last_name,hire_date,salary,manager_id from employees order by first_name;

select employee_id,first_name,last_name,hire_date,salary,manager_id from employees order by first_name asc;
select employee_id,first_name,last_name,hire_date,salary,manager_id from employees order by first_name desc;

select employee_id,first_name,last_name,hire_date,salary,manager_id from employees order by manager_id desc;

select employee_id,first_name,last_name,hire_date,salary,manager_id from employees order by manager_id nulls first;
select employee_id,first_name,last_name,hire_date,salary,manager_id from employees order by manager_id nulls last;
1
2
3
4
5
6
7
8
9

2.6 限制返回结果数量

• SQL提供了 FETCH…OFFSET 子限制返回结果的数量。
• PostgreSQL 还提供了扩展的 OFFSET …LIMIT 子句。
• 这两种语法都可以实现Top-N查询和分页查询。

--标椎sql获取前10条记录
select first_name,last_name,salary from employees order by salary fetch first 10 rows only;

--扩展 limit 获取前10条记录
select first_name,last_name,salary from employees order by salary limit 10;

--获取前10条数据中有相同数据时
select first_name,last_name,salary from employees order by salary fetch first 10 rows with ties;

--标椎sql分页
select first_name,last_name,salary from employees order by salary offset 0 rows fetch first 10 rows only;
select first_name,last_name,salary from employees order by salary offset 10 rows fetch first 10 rows only;
select first_name,last_name,salary from employees order by salary offset 20 rows fetch first 10 rows only;
……

--扩展 limit 分页
select first_name,last_name,salary from employees order by salary offset 0 limit 10;
select first_name,last_name,salary from employees order by salary offset 10 limit 10;
select first_name,last_name,salary from employees order by salary offset 20 limit 10;
……

--查找 employees 表中月薪第二高的所有员工
select first_name,last_name,salary from employees where salary != (select max(salary) from employees) order by salary desc fetch first 1 rows with ties;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

2.7 数据汇总

• 聚合函数用于针对一组数据行进行运算，并且返回一个结果。
• PostgreSQL 常见聚合函数包括 COUNT、AVG、SUM、MAX、MIN、STRING_AGG 等。
• PostgreSQL 聚合函数会忽略 NULL 值计算，COUNT(*) 除外。
• PostgreSQL 聚合函数可同去重（DISTINCT）,排序（ORDER BY）等共用。

select count(*),count(manager_id) from employees;

--逗号(,)为分隔符聚合字符串，并去重倒序显示
select string_agg(distinct first_name,',' order by first_name desc) from employees;
1
2
3
4

2.8 分组统计

• GROUP BY 子句可以将数据按照某些字段进行分组。
• 分组字段中的多个 NULL 值将会被分为一个组。
• HAVING 子句可以对分组结果进行过滤。

select department_id,count(*) from employees group by department_id order by department_id;

select commission_pct,count(*) from employees group by commission_pct order by commission_pct;

select department_id,count(*) from employees group by department_id having count(*) > 10 order by department_id;

--哪些部门有超过2人的员工工资大于10000
select department_id,count(*) from employees where salary > 10000 group by department_id having count(*) > 2;
1
2
3
4
5
6
7
8

2.9 高级分组

• 除了 GROUP BY 分组之外，PostgreSQL 还支持3种高级的分组选项。
• COALESCE 当字段值为空时，现在自定义信息。
• ROLLUP 选项可以生成按照层级进行汇总的小计、合计和总计。
• CUBE 选项用于产生多维度的交叉统计。
• GROUPING SETS 选项可以用于指定自定义的分组集。
• GROUPING 函数可以用于区分源数据中的空值和分组产生的空值。

--对所有产品，所有年份进行汇总小计，并按产品进行所有年份合计总计。
select coalesce(item,'所有产品') "产品",coalesce(year,'所有年份') "年份",sum(quantity) from sales group by rollup (item,year);
select coalesce(item,'所有产品') "产品",coalesce(year,'所有年份') "年份",sum(quantity) from sales group by grouping sets((item,year),(item),());

--对所有产品，所有年份进行汇总小计，并按产品进行所有年份合计总计，按年份进行所有产品合计总计。
select coalesce(item,'所有产品') "产品",coalesce(year,'所有年份') "年份",sum(quantity) from sales group by cube (item,year);
select coalesce(item,'所有产品') "产品",coalesce(year,'所有年份') "年份",sum(quantity) from sales group by grouping sets((item,year),(item),(year),());

--区分源空值及分组空值
select item "产品",year "年份",sum(quantity),grouping(item),grouping(year) from sales group by cube (item,year);
1
2
3
4
5
6
7
8
9
10

2.10 多表连接查询

• 连接查询可以从多个表中获取相关的数据。
• 内连接用于返回两个表中匹配的数据行，使用 [INNER] JOIN 表示。
• 左外连接返回左表中所有的数据行，对于右表，如果没有匹配的数据，显示为空值，使用 LEFT [OUTER] JOIN 表示。
• 右外连接返回右表中所有的数据行，对于左表，如果没有匹配的数据，显示为空值。使用 RIGHT [OUTER] JOIN 表示。
• 全外连接等效于左外连接加上右外连接，使用 FULL [OUTER] JOIN 表示。
• 当连接查询没有指定任何连接条件时，就称为交叉连接。交叉连接使用关键字 CROSS JOIN 表示，也称为笛卡尔积 (Cartesian product)。
• 如果连接条件是等值连接，并且连接字段的名称和类型相同，可以使用 USING 替代 ON 子句，如果所有连接字段名称也相同，可以使用 NATURAL JOIN。
• 自连接是指连接操作两边都是同一个表，即把一个表和它自己进行连接。

--内连接
select d.department_id,e.first_name,d.department_name from employees e join departments d on e.department_id = d.department_id;

--左外连接
select d.department_id,e.first_name,d.department_name from employees e left join departments d on e.department_id = d.department_id;

--右外连接
select d.department_id,e.first_name,d.department_name from employees e right join departments d on e.department_id = d.department_id;

--全外连接
select d.department_id,e.first_name,d.department_name from employees e full join departments d on e.department_id = d.department_id;

--交叉连接
select d.department_id,e.first_name,d.department_name from employees e cross join departments d;
--交叉连接打印九九乘法表
select concat(t1,'*',t2,'=',t1*t2) from generate_series(1,9) t1 cross join generate_series(1,9) t2;

--USING 替代 ON 子句
select d.department_id,e.first_name,d.department_name from employees e join departments d using(department_id);

--自然连接
select d.department_id,e.first_name,d.department_name from employees e natural join departments d;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2.11 条件表达式

• CASE 表达式的作用就是为 SQL 语增加类似于 IF-THEN-ELSE 的逻辑处理功能，可以根据不同的条件返回不同的结果。
• PostgreSQL 支持简单 CASE 表达式和搜索 CASE 表达式。
• PostgreSQL COUNT 支持 FILTER 过滤条件。
• PostgreSQL 还提供了两个缩写形式的 CASE 表达式(函数) NULLIF 和 COALESCE。

--case 条件及等值
select employee_id,first_name,last_name,
case 
when salary < 5000 then '低收入'
when salary between 5000 and 10000 then '中收入'
else '高收入'
end as "薪资",
case department_id 
when 110 then '管理' 
when 100 then '研发'
else '其他' 
end as "部门"
from employees;

--行转列两种写法
select count(case department_id when 10 then 1 end) as "部门10",
       count(case department_id when 20 then 1 end) as "部门20",
       count(case department_id when 30 then 1 end) as "部门30",
       count(case department_id when 40 then 1 end) as "部门40",
	   count(*) filter (where department_id=50) as "部门50"
from employees ;

--部门编号为50的则显示null，其他部门编号正常显示，销售提成为 null 的显示 999
select employee_id,first_name,last_name,nullif(department_id,50),coalesce(commission_pct,999)  from employees;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

2.12 常用数学函数

• 数学函数
• 字符函数
• 日期时间函数
• 类型转换函数

--算数运算符
select 2+1 "加",2-1 "减",2*1 "乘",4/2 "除",4%3 "求余",2^3 "求幂",|/9 "平方根",||/8 "立方根",3! as "阶乘",!!3 "阶乘2",@ -5 "绝对值";

--绝对值函数
select abs(-5);

--取整函数
select ceil(-12.3) "向上取整",floor(-12.3) "向下取整",round(-12.5) "四舍五入取整",trunc(-12.3) "向零取整";

--乘方开方函数
select power(2,3) "乘方",sqrt(4) "平方根",cbrt(8) "立方根";

--整数商余数函数
select div(7,3) "整数商",mod(7,3) "余数";

--π
select pi();

--随机数函数(0-1)
select random();
select first_name from employees order by random() limit 1;

--字符串连接
select concat(t1,'*',t2,'=',t1*t2) from generate_series(1,9) t1 cross join generate_series(1,9) t2;

select t1||'*'||t2||'='||t1*t2 from generate_series(1,9) t1 cross join generate_series(1,9) t2;

--字符串拼接(自定义分隔符)
select concat_ws('#',1,2,3,4);

--字符串长度
select bit_length('瀚高') "比特数",length('瀚高') "字符数",octet_length('瀚高') "字节数";

--大小写转换
select lower('HighgGo db') "小写",upper('HighgGo db') "大写",initcap('HighgGo db') "首字母大写";

--字符串反转
select reverse('上海自来水');

--获取当前时间
select current_date,current_time,current_timestamp,clock_timestamp(),localtimestamp,now(),statement_timestamp();

--to_date 字符串转时间函数
select to_date('2023/01/10','YYYY/MM/DD');

--to_char 其他类型转字符串函数
select to_char(current_timestamp,'HH24:MI:SS'),to_char(interval '5h 12m 30s','HH12:MI:SS'),to_char(-125.8,'999D99');

--to_number 字符串转数字函数
select to_number('￥125.8','L999D9');

--隐式转换
select 1+'2','todo: '||current_timestamp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

2.13 子查询

• 查询临时结果集。
• IN/ALL/ANY(SOME) 运算符。
• 关联子查询，关联外部表，遍历查询，不可单独查询。
• 横向子查询，LATERAL 可让子查询引用外部表字段。
• EXISTS 运算符。

--子查询
select * from employees where salary >= (select avg(salary) from employees);

--IN
select * from departments where department_id in (select distinct department_id from employees where hire_date >= '2008-01-01');

--ALL
select * from departments where department_id in (select distinct department_id from employees where hire_date >= '2008-01-01');
等价
select * from employees where salary > (select max(salary) from employees where department_id = 80);

--ANY(SOME)
select * from employees where salary > any(select salary from employees where department_id = 80);
select * from employees where salary > some(select salary from employees where department_id = 80);
等价
select * from employees where salary > (select min(salary) from employees where department_id = 80);

--查询每个部门的总工资（关联子查询）
select d.department_name,(select sum(salary) from employees where department_id = d.department_id) from departments d;

--查询大于所在部门平均公司的员工信息（关联子查询）
select * from employees e where salary > (select avg(salary) from employees where department_id = e.department_id);

--横向子查询
select d.department_name,t.sum_sal from departments d join lateral (select sum(salary) sum_sal from employees where department_id = d.department_id) t on true;

--EXTSTS
select * from departments d where exists(select 1 from employees where department_id = d.department_id and hire_date > date '2008-01-01');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

2.14 集合

• 并集，union all 不去重，union 去重。
• 交集，intersect all 不去重，intersect 去重。
• 差集，except all 不去重，except 去重。
• intersect 优先级高于 union，括号()，可改变优先级。

--18 和 19 年的所有优秀员工（并集）
select emp_id from excellent_emp where year = 2018
union all
select emp_id from excellent_emp where year = 2019;

--18 和 19 年都是优秀员工的员工（交集）
select emp_id from excellent_emp where year = 2018
intersect all
select emp_id from excellent_emp where year = 2019;

--19 年新晋优秀员工（差集）
select emp_id from excellent_emp where year = 2019
except all
select emp_id from excellent_emp where year = 2018;

--优先级
select * from (values(1)) t1
union all
select * from (values(1)) t2
intersect
select * from (values(1)) t3;

(select * from (values(1)) t1
union all
select * from (values(1)) t2)
intersect
select * from (values(1)) t3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

2.15 通用表表达式

• WITH 表达式定义临时表。
• WITH RECURSIVE 递归查询。

--查询部门平均薪资
with department_avg(department_id,avg_sal) as (
     select department_id,avg(salary) avg_sal
     from employees group by department_id
)
select d.department_name,da.avg_sal
from departments d
join department_avg da
using(department_id);

--查询部门组织架构
with recursive emp_path(emp_id,emp_name,path) as (
	select employee_id,first_name||','||last_name,first_name||','||last_name
	from employees
	where manager_id is null
	union all
	select employee_id,first_name||','||last_name,path||'->'||first_name||','||last_name
	from employees e
	join emp_path p on(e.manager_id = p.emp_id)
)
select * from emp_path;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

2.16 窗口函数

• 分组窗口函数
  OVER(PARTITION BY … [ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW])
• 聚合窗口函数
  AVG() OVER(),SUM() OVER()……
• 移动平均值
  AVG() OVER(PARTITION BY … ORDER BY … ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
• 排序窗口函数
  ROW_NUMBER() 顺序排序，1234……。
  RANK() 跳跃排序，1224……。
  DENSE_RANK() 紧凑排序，1223……。
  PERCENT_RANK() 百分比排序，0,0.1,0.2……1。
  CUME_DIST() 每行数据与之前数据的比率，取值 0-1。
  NTILE() 切片。
• 取值窗口函数
  FIRST_VALUE，返回窗口内第一行的数据。
  LAST_VALUE，返回窗口内最后一行的数据。
  NTH_VALUE，返回窗口内第 N 行的数据。
  LAG，返回分区中当前行之前的第 N 行的数据。
  LEAD，返回分区中当前行之后第 N 行的数据。

--显示每个部门员工信息及其平均薪资
select employee_id,first_name,last_name,department_id,salary,avg(salary) over(partition by department_id) from employees;

--移动平均值
select employee_id,first_name,last_name,department_id,salary,avg(salary) over(partition by department_id rows between 1 preceding and 1 following) from employees;

--薪资排序
select employee_id,first_name,last_name,department_id,salary,
	row_number() over(order by salary desc),
	rank() over(order by salary desc),
	dense_rank() over(order by salary desc),
	percent_rank() over(order by salary desc),
	cume_dist() over(order by salary desc),
	ntile(5) over(order by salary desc)
	from employees;
等价
select employee_id,first_name,last_name,department_id,salary,
	row_number() over w,
	rank() over w,
	dense_rank() over w,
	percent_rank() over w,
	cume_dist() over w,
	ntile(5) over w
	from employees window w as (order by salary desc);

--取值窗口函数
select employee_id,first_name,last_name,department_id,salary,
	first_value(salary) over w,
	last_value(salary) over (order by salary desc rows between unbounded preceding and unbounded following),
	nth_value(salary,3) over (order by salary desc rows between unbounded preceding and unbounded following),
	lag(salary,1) over w,
	lead(salary,1) over w
	from employees window w as (order by salary desc);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

三、DML

3.1 插入

• 单行插入。
• 多行插入。
• 复制数据。
• 返回插入数据。

--单行插入
insert into dept(department_id,department_name) values(1,'Adminstration');
insert into dept values(2,'Marketing');

--多行插入
insert into emp values
	(200,'Jennifer','Whalen','2020-01-01',4400.00,NULL,1),
	(201,'Michael','Hartstein','2020-02-02',13000.00,NULL,2),
	(202,'Pat','Fay',default,6000.00,201,2);

--复制数据
create table emp1 (like emp);
insert into emp1 select * from emp;

--返回插入数据
insert into dept values (30,'Purchasing') returning department_id,department_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

3.2 更新

• 单表更新。
• 跨表更新。
• 返回更新后的数据。

--单表更新
update emp set salary = salary + 1000,department_id = 2 where employee_id = 200;

--跨表更新
update emp1 set 
	salary = emp.salary,
	department_id = emp.department_id,
	manager_id = emp.manager_id
from emp where emp1.employee_id = emp.employee_id;

--返回更新后的数据
update emp set salary = salary + 1000,department_id = 2 where employee_id = 200 
returning first_name,last_name,salary;
1
2
3
4
5
6
7
8
9
10
11
12
13

3.3 删除

• 单表删除。
• 跨表删除。
• 返回被删除的数据。

--单表删除
delete from emp1 where employee_id = 201;

--跨表删除
delete from emp1 using emp where emp1.employee_id = emp.employee_id;
等价
delete from emp1 where emp1.employee_id in (select employee_id from emp);

--返回被删除的数据
delete from emp1 returning *;
1
2
3
4
5
6
7
8
9
10

3.4 合并数据

• 冲突时不做任何操作。
• 冲突时更新目标数据。

--主键冲突不做处理
insert into emp values (200,'Jennifer','Whalen','2020-01-01',4400.00,NULL,1) 
	on conflict (employee_id) do nothing;

--主键冲突更新目标数据
insert into emp values (200,'Jennifer','Whalen','2020-01-01',4400.00,NULL,1)
	on conflict on constraint emp_pkey
	do update set 
	first_name = EXCLUDED.first_name,
	last_name = EXCLUDED.last_name,
	hire_date = EXCLUDED.hire_date,
	salary = EXCLUDED.salary,
	manager_id =EXCLUDED.manager_id,
	department_id = EXCLUDED.department_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.5 通用表达式

• 可以将数据修改操作影响的结果作为一个临时表，然后在其他语句中使用。

--deletes 通用表达式
with deletes as (
delete from employees
where employee_id = 206
returning *
)
insert into employees_history
select * from deletes;

table employees_history;

--insert 通用表达式
with inserts as (
insert into employees
values
(206,'William','Gietz','WGIETZ','515.123.8181','2002-06-07','AC_ACCOUNT',8800.00,NULL,205,110)
returning *
)
insert into employees_history
select * from inserts;

table employees_history;

--update 通用表达式
delete from employees_history;  -- 清除历史记录

with updates as (
update employees
set salary = salary + 500 where employee_id = 206
returning *
)
insert into employees_history
select * from employees where employee_id = 206;

table employees_history;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

四、DDL

4.1 数据库操作

CREATE DATABASE name;
DROP DATABASE name;
ALTER DATABASE name ALLOW_CONNECTIONS true|false;
ALTER DATABASE name CONNECTION LIMIT connlimit;
ALTER DATABASE name CONNECTION IS_TEMPLATE true|false;
ALTER DATABASE name RENAME TO new_name;
ALTER DATABASE name OWNER TO new_owner;
ALTER DATABASE name SET TABLESPACE new_tablespace;
ALTER DATABASE name SET configuration_parameter {TO|=} {value|DEFAULT};
ALTER DATABASE name SET configuration_parameter FROM CURRENT;
ALTER DATABASE name RESET configuration_parameter;
ALTER DATABASE name RESET ALL;
1
2
3
4
5
6
7
8
9
10
11
12

4.2 用户角色操作

CREATE ROLE|USER role_name;
DROP ROLE|USER role_name;
ALTER ROLE|USER role_name WITH PASSWORD 'password';
ALTER ROLE|USER role_name WITH PASSWORD NULL;
ALTER ROLE|USER role_name VALID UNTIL 'May 4 12:00:00 2015 +1';
ALTER ROLE|USER role_name VALID UNTIL 'infinity';
ALTER ROLE|USER role_name CREATEROLE CREATEDB;
ALTER ROLE|USER role_name SET maintenance_work_mem = 100000;
ALTER ROLE|USER role_name {IN DATABASE name} SET configuration_parameter {TO|=} {value|DEFAULT};
ALTER ROLE|USER role_name {IN DATABASE name} SET configuration_parameter FROM CURRENT;
ALTER ROLE|USER role_name {IN DATABASE name} RESET configuration_parameter;
ALTER ROLE|USER role_name {IN DATABASE name} RESET ALL;
1
2
3
4
5
6
7
8
9
10
11
12

4.3 模式操作

CREATE SCHEMA schema_name;
DROP SCHEMA schema_name;
ALTER SCHEMA schema_name RENAME TO new_name;
ALTER SCHEMA schema_name OWNER TO new_owner;
1
2
3
4

4.4 表操作

CREATE TABLE table_name;
DROP TABLE table_name;
ALTER TABLE table_name RENAME TO new_name;
ALTER TABLE table_name SET SCHEMA new_schema;
ALTER TABLE table_name OWNER TO role_name;
ALTER TABLE table_name SET TABLESPACE tablespace_name;
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP column_name;
ALTER TABLE table_name ALTER column_name TYPE datatype;
ALTER TABLE table_name RENAME column_name TO new_column_name;
ALTER TABLE table_name ALTER column_name {SET|DROP} NOT NULL;
ALTER TABLE table_name ALTER column_name SET DEFAULT expression;
ALTER TABLE table_name RENAME CONSTRAINT constraint_name TO new_constraint_name;
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(id);
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(id);
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(id > 100);
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY(id) REFERENCES reftable(id);
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

……
……

五、TCL

5.1 授权操作

--授权cc用户连接数据库syd
grant connect on database syd to cc;
--授权cc访问syd模式权限
grant usage on schema syd to cc;
--把syd用户的权限授予用户cc
grant syd to cc;
--授权syd用户可以访问syd模式下的syd表
grant select,insert,update,delete on syd.syd to syd;
--授权syd用户可以访问syd模式下的所有表
grant select,insert,update,delete on all tables in schema syd to syd;
--授权syd用户可以更新syd模式下的syd表的name列
grant update (name) on syd.syd to syd;
--授予syd用户对syd模式下的syd表，查看更新name、age字段，插入name字段的权限
grant select (name,age),update (name,age),insert(name) on syd.syd to syd;
--授权syd用户可以使用syd模式下的seq_id_seq序列
grant select,update on sequence syd.seq_id_seq to syd;
--授权syd用户可以使用syd模式下的所有序列
grant select,update on all sequences in schema syd to syd;
--授权默认表权限
alter default privileges in schema syd grant select,insert,update,delete on tables to syd
--授权默认自增序列权限
alter default privileges in schema syd grant select,update on sequences to syd;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

5.2 撤销授权操作

--回收cc用户连接数据库syd的权限
revoke connect on database syd from cc;
--回收cc访问syd模式的权限
revoke usage on schema syd from cc;
--回收cc用户的syd权限 
revoke syd from cc;
--回收syd用户可以访问syd模式下的syd表的权限
revoke select,insert,update,delete on syd.syd from syd;
--回收syd用户可以访问syd模式下的所有表的权限
revoke select,insert,update,delete on all tables in schema syd from syd;
--回收syd用户可以更新syd模式下的syd表的name列的权限
revoke update (name) on syd.syd from syd;
--回收syd用户对syd模式下的syd表，查看更新name、age字段，插入name字段的权限
revoke select (name,age),update (name,age),insert(name) on syd.syd from syd;
--回收syd用户可以使用syd模式下的seq_id_seq序列的权限
revoke select,update on sequence syd.seq_id_seq from syd;
--回收syd用户可以使用syd模式下的所有序列的权限
revoke select,update on all sequences in schema syd from syd;
--回收默认表权限
alter default privileges in schema syd revoke select,insert,update,delete on tables from syd
--回收默认自增序列权限
alter default privileges in schema syd revoke select,update on sequences from syd;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22