【数据库学习】Postgres（PG）概览与PostgreSQL基本语法_pg数据库

1. pg安装与运维
2. Postgres原理及底层实现
3. 基础语法
4. SQL优化
5. 中文文档

1，数据类型

1. 数据类型获取

--类型获取：pg_typeof
select pg_typeof(id) from ...
1
2

2. 类型转换：
   cast或者::

select '5'::int, '2014-09-09'::date;
select int '5', date '2014-09-09';
1
2

3. 常见操作符：

# + - * / %
3 ^ 3  # 幂
|/36.0 #平方根
||/8.0 #立方根
5!   或 !!5 #阶乘，结果都是120
@-5.0  #绝对值
# & (and)  |(or)  #(XOR) ~(not) << >>
1
2
3
4
5
6
7

4. 三值逻辑： UNKNOWN
   MySQL 、PG 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN。
   任何值与null做比较结果都是Unknow，即select * from a <> 1，不会查出来a is null 的数据。

1）布尔（boolean）

常用操作符：is、and、or、not

2）数值

1>int

2>decimal（numeric，精确类型的小数）

DECIMAL(p,s) 或 NUMERIC(p,s) 
存储精确的数值
精度（p）：精度可达1000
刻度（s）
1
2
3
4

1. 适用于货币金额等要求精度的场合。
2. 会影响运算速度。

3>float（非精确类型的浮点小数）

1. 浮点数（float）特殊值：Infinity（正无穷大）、-Infinity、NaN（不是一个数字），sql操作：

update table set x='Infinity'   #不区分大小写
1

另外两个浮点数值做相等性比较时可能不符合预期。

4>money（8字节，货币类型）

完全保证精度，不同国家其输出格式不同。

show lc_monetary;  #en_US.UTF-8输出就是$1.22
select '1.22' :: money;

set lc_monetary = 'zh_CN.UTF-8'; #通过这个修改，可输出￥1.22
1
2
3
4

5>serial(自动递增整数)

pg中的自增字段，通过序列（sequence）来实现的。

create table t (
	id serial
);
1
2
3

等价于：

create sequence t_id_seq;
create table t(
  id integer not null default nextval('t_id_seq')
);
alter sequence t_id_seq owned by t.id;
1
2
3
4
5

6>常用函数

1. 进制转换

-- 平台：mysql
-- 十进制转换为二进制
SELECT BIN(2), conv(2,10,2); 
-- 十六进制
SELECT HEX(10), conv(10,10,16);
-- 八进制
SELECT OCT(9);
-- 进制转换 conv(值,值的进制,目标进制)
SELECT conv(0010,2,10);


-- 平台：pg
-- 十进制转换为二进制
SELECT 2::bit(4);
-- 十进制转换为十六进制
SELECT to_hex(10);
-- 十六进制转换为十进制
SELECT x'A'::int;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3）字符（ varchar(n)、char(n)、text ）

在大多数数据库中，定长char(n)有一定的性能优势；但在pg中，定长变长无区别。建议使用varchar(n)、text。

（2^n）-1 是好的磁盘或内存块对齐，对齐块更快。当今“块”的大小更大，内存和磁盘足够快，可以忽略对齐。

1>常用函数说明

1. 字符串拼接

--Access和 SQL Server使用 + 号。
--pg、DB2、Oracle、SQLite和Open Office Base 使用 ||。
select 'a' || '_' || 'b'
1
2
3

2. 其他

4）二进制（bytea）

用于存储大型二进制对象（比如图形）的原始二进制数据。
使用的存储空间是 4 字节加上二进制串的长度。

5）位串( bit(n)、bit varying(n) )

0和1组成的字符串。相比二进制类型，在位操作方面更方便。

6）日期和时间（date、time、timestamp）

time、timestamp根据是否包括时区又分为两种类型。interval 表示时间间隔。

1. pg中世纪可以精确到毫秒。

1>日期函数

--按照当前事务开始的时间返回数据。在整个事务中值不变。
select current_time; --带时区。current_date，current_time, current_timestamp, current_time,current_timestamp(precision) 
select localtime; -- 10:13:42.334327，不带时区。localTimestamp, localTime(precision)...
select now(); --2021-01-20 10:14:37.50845+08  包括时区，秒也保留到了6位小数 
select now()::timestamp(0)without time zone; --2021-01-20 10:22:41
select transaction_timestamp(); --当前事务开始的时间戳

--计算日期差值
select daterange_subdiff(
	(select latest_timestamp :: date from user limit 1), 
	(select latest_timestamp :: date from user limit 1 offset 1)
)

--返回实时时间，不受事务影响：
statement_timestamp()  --当前语句开始时的时间戳
clock_timestamp()  --实时时间戳，同一条sql中也可能不同
timeofday()      --类似clock_timestamp，但返回值为text字符串

select age(timestamp '2011-05-02', timestamp '1980-05-01') ;--31 years 1 day
select age(timestamp '2011-05-02') ;--9 years 8 mons 18 days, 相当于age(current_date, timestamp '2011-05-02')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2>日期运算

1. 比较：date、timestamp、long可以通过>=、between and来比较
2. 计算

WHERE
	create_time BETWEEN (
		CURRENT_TIMESTAMP - INTERVAL '3 hour'  --日期计算，可以是+或者-
	)
AND CURRENT_TIMESTAMP

select date'2020-04-02' + integer'7'; --2020-04-09  date
select date'2020-04-02' + interval'1 hour'; --2020-04-02 01:00:00  timestamp
select date'2020-04-02' + time'3:00'; --2020-04-02 03:00:00 timestamp
select time'3:00' + interval'1 hour'; --04:00:00   time
select -interval'1 hour';  ---01:00:00  interval
select date'2020-04-02' - date'2020-04-01';  --1

--overlaps 两个时间戳重叠为true
select (date '2020-04-02', date '2021-04-02') overlaps
			 (date '2020-06-02', date '2022-04-02');   --true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

3>日期特殊值

7）枚举

mysql也有枚举类型，用法稍微不同。

create type week as enum ('sun','mon','wed');
create table lwh_duty(person text, weekday week);
insert into lwh_duty values ('mary','sun');
insert into lwh_duty values ('mary','Sun'); --报错，字符串不在枚举类型内。区分大小写。
select * from lwh_duty where weekday = 'Sun'; --报错

--查看枚举类型的定义
select * from pg_enum; 

--常见函数
select enum_first('wed'::week);--sun，返回第一个枚举类型。wed可以是任意枚举值即可。同理还有enum_last
select enum_range('wed'::week); --{sun,mon,wed},wed可以是任意枚举值即可。以有序数组的形式返回所有枚举类型

--删除枚举 删除之前要清除所有依赖
drop type enum_lwh_test;
--枚举创建 表字段类型为枚举名即可
create type enum_lwh_test as enum ('confirmed','unconfirmed');
--枚举重命名 会一并修改相关依赖
alter type enum_lwh_test rename to enum_lwh;
--插入新值
ALTER TYPE enum_lwh ADD VALUE 'orange' AFTER 'unconfirmed';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

8）几何（pg特有类型）

包括类型：点（point）、直线（line）、线段（lseg）、路径（path）、多边形（polygon）、圆（cycle）。

9）网络地址（cidr、inet、macaddr，pg特有类型）

--macaddr支持多种格式
select '00e005664477'::macaddr;
select '00E005:664477'::macaddr;--不区分大小写
select '00-E0-05-66-44-77'::macaddr;
select '00E005-664477'::macaddr;
select '00E0.0566.4477'::macaddr;

--运算
select inet'192.0.0.1' < inet '192.0.0.2';-- true。大于等于、不等于<>
select inet'192.0.0.3' << inet '192.0.0.0/24';-- true。包含于<<、包含>>
select ~inet'0.0.0.255'; --255.255.255.0 位非
select inet'0.0.0.255' & inet'0.0.0.255'; --0.0.0.255  位与&，位或|
select inet'192.0.0.50' + 1; --192.0.0.51  + -
select inet'192.0.0.50' - inet'192.0.0.0'; --50

--其它函数……
host(inet) --取主机地址
hostmask(inet) --主机掩码地址
netmask(inet) --子网掩码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

10）数组（pg特有类型）

数组的类型可以是数据库内建的类型、用户自定义的类型、枚举或者组合类型。

1>定义

--数组可以定义长度、也可以不给。也可以定义多维数组。但定义长度和多维数组在实际使用中是无效的。如下面两种定义是等价的。
create table lwh01(id int, col1 int[], col2 text[][]);
create table lwh01(id int, col1 int[10], col2 text[]);
1
2
3

2>插入

--插入
--数组通过单引号+大括号来表示，具体分隔符通过以下sql来查询，大部分情况下使用的是逗号分隔（box通过分号分隔，其它用逗号）。
select typname,  typdelim from pg_type where typname in ('int4','box');
insert into lwh01 values(1, '{1,2,3}', '{1,2,3}');
insert into lwh01 values(2, '{{1,2,3},{1,2,3}}', NULL);
--通过ARRAY关键字使用数组构造器也可以输入数组
insert into lwh01 values(3, ARRAY[1,2,3], ARRAY['1','2','3']);
--二维数组 array输入
insert into lwh01 values(4, ARRAY[[1,2],[2,3]], NULL);
--数组下标输入。默认从1开始，可以手动指定以0开始输入。
insert into lwh01 values(5, '[0:2]={1,2,3}', NUll);
1
2
3
4
5
6
7
8
9
10
11

3>查询

--pg数组下标从1开始（也可以指定下标开始值）
select id, col1, col1[0],col1[1],col1[2],col1[3],col1[4], col1[1][2], col1[1:2], col1[1][1:2] from lwh01;
1
2

4>常用操作符

在jpa中，两个数组取交集，即&&运算这样使用：

select ids && '{1}' :: int[] from book_info;  --jpa中，sql的::
1

5>常用函数

11）复合类型（用户自定义类型）

类似于C的结构体，

12）xml类型

可以存储xml类型，自动校验是否合法。

13）json/jsonb

postgresql支持两种json数据类型：json和jsonb。与text不同，会自动校验格式是否合法。

1>概念

主要区别在于效率：json类型存储快，使用慢；jsonb类型存储稍慢，使用较快。
json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。
jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。

json类型和pg类型映射：

2>常用函数和操作符

json和jsonb的操作与函数基本通用。

①数组操作

json和jsonb通用：

--如果用操作符->>，取出数据会自动转换为text
select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2; --{"c":"baz"}    索引从0开始，从数组中取出index=2的对象
select ('{"f2":"1"}'::jsonb) ->> 'f2'; --取出字符串值 1
select ('{"f2":"1"}'::jsonb) -> 'f2';  --取出字符串值 "1"

select '["a", "b"]'::jsonb ?& array['a', 'b'];  --true   array中是否包含某些键（top-level keys，这些键是一个数组）
select '["a", "b"]'::jsonb || '["c", "d"]'::jsonb; --["a", "b", "c", "d"]    两个数组求并集
select '["a", "b"]'::jsonb || '["c", "d"]'::jsonb; --两个数组求并集
select (ARRAY[11] || (select type from field where label = '名称') )
select '["a", "b"]'::jsonb - 1;  --["a"]  数组去除某个索引
select '["a", {"b":1}]'::jsonb #- '{1,b}';  --["a", {}]  数组去除某个路径的键  

--各种格式生成array
select json_build_array(1,2,'3',4,5);  --[1, 2, "3", 4, 5]    
select array_to_json('{{1,5},{99,100}}'::int[]); --[[1,5],[99,100]] 

select jsonb_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]'); --5   数组长度
select * fromb jsonb_array_elements('[1,true, [2,false]]');--取出数组中所有元素 json_array_elements_text返回text;json_array_elements处理json
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

②map操作

json和jsonb通用：

--如果用操作符->>，取出数据会自动转换为text，此时结果没有双引号
select '{"a": {"b":"foo"}}'::json->'a';  --{"b":"foo"}   从map中取出key为a的值
--如果用操作符#>>，取出数据会自动转换为text
select '{"a": {"b":"foo"}}'::json #>'{a,b}';   --"foo"   map输出特定路径对象
select '{"a": {"b":"foo"}}'::json #>>'{a,b}';   --foo

select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb;  --true    map中是否包含某个键值对 同理还有<@

select '{"a":1, "b":2}'::jsonb ? 'b'; --true  map是否包含某个键（top-level keys）

select '["a", "b", "c"]'::jsonb ?| array['b', 'd']; --t 数组是否有交集
select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd']; -- t key值是否有交集 

select '{"a": "b"}'::jsonb - 'a';  --{}   map剔除某个键
select '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[];  --{}  map剔除某些键

--各种格式生成map
select row_to_json(row(1,'foo','1'));  --{"f1":1,"f2":"foo","f3":"1"}
select json_build_object('foo',1,'bar',2);   --{"foo" : 1, "bar" : 2}   
select json_object('{a, 1, b, "def", c, 3.5}');  --{"a" : "1", "b" : "def", "c" : "3.5"} 
select json_object('{{a, 1},{b, "def"},{c, 3.5}}');
select json_object('{a, b}', '{1,2}'); --{"a" : "1", "b" : "2"}

select * from json_each('{"a":"foo", "b":"bar"}'); --把jsonMap转换为键值对  json_each_text返回text

--通过键路径获取值 json_extract_path_text返回text
select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4'); --{"f5":99,"f6":"foo"} 
select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4','f6');--"foo"
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

③常用函数

select to_json('Fred said "Hi."'::text);  --"Fred said \"Hi.\""    string2json 
select json_typeof('-123.4'); --number 

--修改jsonb   jsonb_set
jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)
-- attributes为jsonb类型字段（对象转成的json）
-- 第一个参数表示jsonb对象，第二个参数表示路径text[],第三个参数表示value（jsonb对象），第四个值表示无此值是否新建（默认为true）
原值：{"a":"1"}
update user_test set attributes = jsonb_set(attributes,'{a}','"0"'::jsonb, false) where id = '8888';
执行后：{"a":"0"}
1
2
3
4
5
6
7
8
9
10

3>索引

json类型没办法直接建索引，但是可以建函数索引。
jsonb类型的列上可以建索引，GIN索引可以高效的从jsonb内部的key/value对中搜索数据。（BTree索引效率较低）

GIN索引创建方式：

--使用默认的操作符jsonb_ops创建GIN索引：
--每个key和value都作为一个单独的索引项
--如：{“foo”：{“bar”：“baz”}}，会创建3个索引项：“foo” “bar” “baz”
create index idx_name on table_name using gin(index_col);
--使用jsonb_path_ops操作符建GIN索引：(推荐）
--为每个value创建一个索引项
--如：{“foo”：{“bar”：“baz”}}，会创建1个索引项：“foo” “bar” “baz”组合成一个Hash值作为索引项。因为索引相对较小，带来性能的提升。
create index idx_name on table_name using gin(index_col jsonb_path_ops);
1
2
3
4
5
6
7
8

14）range（范围类型，pg特有类型）

主要用于范围快速搜索。
直接通过库里的开始值和结束值进行范围搜索效率较低，而使用range类型，通过创建空间索引的方式来执行范围搜索会大大提高效率。

15）对象标识符

16）伪类型

不能作为pg的字段，主要用于声明函数的参数和结果类型。

17）其他类型

2，库操作SQL

--建库
create database children;
--删库
drop database children;
--重命名
ALTER DATABASE old_database_name MODIFY NAME = new_database_name;  
--查看数据库
show databases;
--调用数据库
use children;
1
2
3
4
5
6
7
8
9
10

查询当前数据库：

select current_database();
1

查询当前用户：

select user;  或者：select current_user;
1

3，模式SQL

1. pg不指定模式的话默认public模式。

--创建
CREATE SCHEMA schema_name;
1
2

4，表操作

1）建表

--创建public模式下的表
CREATE TABLE if not exists public.stu_info(   
    FOREIGN KEY (ID) REFERENCES people_info (ID),  --单个外键，一般情况下不建议增加这种强约束
    id int8 PRIMARY KEY,    --系统会自动为主键创建一个隐含的索引  primary key(Sno,Cno)组合主键
    address VARCHAR (255) UNIQUE NOT NULL,
    birthday TIMESTAMP NOT NULL,  
    age int default 15,  --默认值，影响后续插入值。但对旧数据没有影响。
   CONSTRAINT student2_pkey PRIMARY KEY (id),
   CONSTRAINT ck_age CHECK(age<18), --检查约束，约束某些字段需要满足的要求。NULL被认为满足条件。
   CONSTRAINT uk_tbl_unique_a_b unique(id ,address) --唯一约束。唯一键中可以写入任意多个NULL！即可以存在多组 1，null  
)
WITH (
  OIDS=FALSE
);
ALTER TABLE myschema.tb_test
  OWNER TO postgres;

--重命名表
alter table tableName RENAME TO newName;--pg
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

2）表约束

1. 主键
   不允许null、重复。

--添加主键(有些DBMS不允许在建表之后修改主键）
ALTER TABLE tableName ADD PRIMARY KEY(fieldName) ; 
1
2

2. 外键
   允许null。
   一般情况下，不建议建立外键这种强约束。
3. 检查约束

--添加约束
alter table tableName add check (age<16);--pg 增加检查约束，约束名为：tableName_age_check

--删除约束
--pg 根据约束名删除检查约束、唯一约束
alter table tableName drop constraint IF EXISTS constraintName;
1
2
3
4
5
6

4. 唯一约束

--pg 增加唯一约束
--由于add constraint无法和if exists连用，所以先删后加。
alter table tableName add constraint uk_tbl_unique_a_b unique (a,b);
1
2
3

5. 非NULL约束。

--pg 增加非空约束
alter table tableName alter column fieldName set NOT NULL;

--pg 删除非空约束（非空约束没有约束名）
alter table tableName alter column fieldName drop NOT NULL;
1
2
3
4
5

3）删

修改、删除、使用与常规sql语法一致。。

4）改

1>属性默认值

--修改默认值
alter table tableName alter column age set DEFAULT 15;--pg
--删除默认值
alter table tableName alter column age drop DEFAULT 15;--pg
1
2
3
4

2>修改表字段

--增加列\添加一个字段
--column 可加可不加
alter table tableName add column if not exists columnName varchar(30) default 'a' not null; 

--删除列（会连同字段上的约束一并删除）
--column 可加可不加
alter table tableName drop column columnName; 

--修改列名：
--pg、oracle中
alter table tableName rename column fieldName TO fieldNameNew;
--在sqlserver
exec sp_rename '[表名].[列名]‘，’[表名].[新列名]'
--mysql
ALTER TABLE 表名 CHANGE 列名 新列名 列类型

--修改字段类型或长度:
alter table tableName modify column 字段名 类型;
--pg修改字段数据类型。仅在当前数据都可以隐式转换为新类型时才可以执行成功
alter table tableName alter column fieldName TYPE text;
--将NAME最大列宽增加到10个字符
ALTER TABLE CARD ALTER COLUMN NAME varchar(10) 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

5）查

pg表也支持TOAST（跨页存储的大字段，需要将行外数据存储到TOAST表）。

--获取数据库中所有表
select * from pg_tables where schemaname='public';
1
2

6）表分区SQL

1>概念

表分区是逻辑上把大表分割成物理上几块，如按时区分、按类型分等。

优点

1. 删除更快。如按时间分区，删除历史数据直接删除对应分区表。
2. 查询更快。如按时间分区，较早时间的表几乎很少查询，各个分区表有各自的索引，使用频率高的分区表的索引就可以完全缓存到内存中。
3. 修改更快。访问具体分区表，而不是全表扫描。
4. 节省资源。根据访问频率把不常访问的数据存储到不同的物理介质上。

2>表继承分区

注意：通过触发器创建表，主表的唯一索引可以创建但是不起作用！！！

--表继承
create table sales_detail(...sale_date...);--父表，不存数据
create table sales_detail_y2014m01 (  
	check (sale_date >= DATE'2014-01-01' and sale < DATE'2014-02-01')  --check约束
)INHERITS(sales_detail); --子表继承父表

--在各个分区表的分区键上建立索引
create index sales_detail_y2014m01_sale_date on sales_detail_y2014m01 (sale_date);

--建立规则或触发器，把对主表的数据插入重定向到具体分表。
create RULE sales_detail_insert_y2014m01 AS 
ON insert to sales_detail where 
  (sale_date >= DATE'2014-01-01' and sale < DATE'2014-02-01')
DO INSTEAD 
  insett into sales_detail_y2014m01 values(NEW.*);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

分表通过规则或触发器优缺点对比：

1. 相比触发器，规则开销很大。批量插入的情况下，规则效率更高。
2. copy插入数据不会触发规则，但会触发触发器。
3. 分表之外的数据，再次插入父表时，触发器会报错，但规则会把这些规则之外的数据插入主表。

3>声明式分区

pg内部通过表继承来实现分区表。pg10.x通过声明式分区直接创建分区表，但其内部原理依然是表继承。

相比表继承分区，不需要在父表创建各种触发器（降低维护成本），对父表的DML操作会自动路由到相应分区。

目前仅支持范围分区和列表分区。

--范围分区 
--创建主表
1. 创建全局主键必须携带分区字段
2. 不允许全局创建索引，可以在分表上创建索引。
3. 删除主表不会删除子表，通过级联删除可以一起删除。
4. 查询主表时若在where条件中携带分区字段（如日期）,可直接去分区表检索，提高检索速度。注意：这个字段不包含运算，否则失效。
create table sales_detail(
  ...
  sale_date date not null,
  ...
) PARTITION BY RANGE(sale_date); --通过PARTITION BY来支持分区。不能给没有分区表的分区插入数据。

--创建分区表（分区表需要手动进行创建，可以用定时任务一次创建多个）
create table sales_detail_y2014m01 PARTITION OF sales_detail  --通过PARTITION OF指定分区表分区。
FOR VALUES FROM ('2014-01-01') TO ('2014-02-01'); 

--查看所有的分区表
select relname from pg_catalog.pg_class where relispartition = 't';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

--列表分区
create table test_list_part(id int, state boolean) PARTITION BY list(state);
create table test_list_part_t partition of test_list_part for values in ('t');
create table test_list_part_f partition of test_list_part for values in ('f');
 
insert into test_list_part values ('1','t');
insert into test_list_part values ('1','f');
 
create index on test_list_part_f (state);
create index on test_list_part_t (state);
1
2
3
4
5
6
7
8
9
10

④优化

1.打开约束排除（postgresql.conf中的constraint_exclusion设置为on）。
sql查询中，where语句的过滤条件与分区表的check条件进行对比，直接跳过不需要扫描的分区表。
注意：分区字段的where语句如果包含计算，可能会扫描全表，需要解释执行确认。

select count(*) from sales_detail where sale_date >= DATE'2014-12-01';--同各个分区表上的check条件进行对比，可知只需要扫描主表和分表sales_detail_y2014m12。
1

⑤主键

可能我的navicat版本太低了看不见主键，通过以下sql可以查询到主键。

SELECT
	pg_attribute.attname AS colname,
	pg_type.typname AS typename,
	pg_constraint.conname AS pk_name 
FROM
	pg_constraint
	INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
	INNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid 
	AND pg_attribute.attnum = pg_constraint.conkey [ 2 ]
	INNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid 
WHERE
	pg_class.relname = 'tbl_alarm_judge_conclusion' 
	AND pg_constraint.contype = 'p';
	--pg_constraint.contype : c = 检查约束， f = 外键约束， p = 主键约束， u = 唯一约束 t = 约束触发器 x = 排斥约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14

7）序列SQL

序列对象（也叫序列生成器）就是用CREATE SEQUENCE 创建的特殊的单行表。一个序列对象通常用于为行或者表生成唯一的标识符。通常用于表的主键自增。

mysql中的序列有以下两个限制，但pg中的序列没有。

1. 自增长只能用于表中的一个字段；
2. 自增长只能被分配给固定表的固定的某一个字段，不能被多个表使用。

①创建

1> serial

将id类型设置为：serial，pg会自动创建一个序列，同时将列设置为INT，默认值设置为nextval(‘序列’)。serial8会将列设置为int8(long)

2> sequence（序列）

--[TEMPORARY | TEMP] 临时序列，在会话结束时自动删除；除非用模式修饰，否则同一个会话中同名的非临时序列是不可见的。
create [TEMPORARY | TEMP] sequence seq_name;  

--nextval('seq_name') 表示递增序列seq_name并返回新值。字符串会自动转换为regclass类型
--setval('seq_name', bigint) 设置序列当前数值
create table test (id int default nextval('seq_name'), info text);  
1
2
3
4
5
6

CREATE SEQUENCE
IF NOT EXISTS PUBLIC .role_id_seq   --序列名
    START WITH 1          --指定序列的起点，缺省初始值对于递增序列为minvalue， 对于递减序列为maxvalue。
    INCREMENT BY 1    --递增量，缺省为1；递减为负数
    NO MINVALUE         --指定序列的最小值， 如：minvalue 1
    NO MAXVALUE        --指定序列的最大值，递减序列最大值为-1
    CACHE 1                 --为快速访问而在内存里预先存储多少个序列号，缺省值为1，表示一次只能生成一个值，也就是说没有缓存。
    OWNED BY {table.column | NONE}   --将序列关联到一个特定的表字段上，在删除该字段或其所在的表时将自动删除绑定的序列；NONE表示无关联。
    CYCLE --默认不添加cycle选项，创建出来的序列满时会报错；添加cycle时，序列会从开始值重新开始。
    ;
1
2
3
4
5
6
7
8
9
10

② 给已有的字段创建添加自增，并且自增值从最大的id+1开始

--将下一次的自增值设置成最大id+1
select setval('user_info_id_seq',(select max(id)+1 from user_info));
1
2

③修改序列

alter sequence serial
increment by 3
restart with 10;   --是一个可选选项，它改变序列的当前值
 
select nextval('serial');
1
2
3
4
5

④删除序列

DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
--IF EXISTS     如果指定的序列不存在，那么发出一个 notice 而不是抛出一个错误。
--CASCADE     级联删除依赖序列的对象。
--RESTRICT    如果存在任何依赖的对象，则拒绝删除序列。这个是缺省。

drop sequence serial;
1
2
3
4
5
6

⑤序列和事务

事务回滚不会影响序列。

begin;
select netval('seqtest01'); --3
rollback;
select currval('seqtest01'); --3
1
2
3
4

⑥cache

如果cache大于1，当序列被用于多会话时，每个会话在每次访问序列对象的过程中都会分配并缓存随后的序列值，会话结束时丢失没有使用的数字，从而导致序列出现空洞。
多个会话的情况下，如果cache大于1，那么只能保证nextval值是唯一的，缺不按顺序生成。比如cache=10，会话A保留了1……10并且返回nextval=1，会话B保留了11……20。会话A返回nextval=1之前会话B可能先返回nextval=11。

8）表继承

表继承是pg特有的。

create table persons_lwh(
  name text,
  age int,
  sex boolean
);
create table students_lwh(
  class_no int
)INHERITS(persons_lwh);  --继承。
--父表的检查约束、非空约束会被子表继承，但唯一、主键、外键约束子表不会继承。
--一个子表可以继承多个父表，多个相同字段必须类型一致，融合后在子表中只有一个字段。

insert into students_lwh values('Mary',15,true,1); --向子表插入数据，父子表都有数据
insert into persons_lwh values('Bob',15,true);--向父表插入数据，只有父表有数据
select * from persons_lwh;
select * from students_lwh;
select * from only persons_lwh; --添加only，只获取直接插入父表的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

9）表空间

pg中的表空间就是为表一指定个存储目录，主要用于把表存放到不同的存储目录。

5，表查询

select * from student;
select 1+2; 
1
2

1. 关键字规避

--sqlServer通过加中括号规避关键字
select [select] from 表名;
--pg通过加单引号规避
select 'select' from 表名;
1
2
3
4

1）条件查询

2）排序

pg：表查询没有指定查询顺序时，按插入顺序进行排序。

#多重排序：
-- 先按字段5排序，再按字段6排序
order by 字段5,字段6 asc  
1
2
3

3）分页查询

1. limit 和offset后面都要跟整数（单一变量），不涉及计算。

--pg：page从1开始
select * from tbl_
limit size offset (page-1)*size;
1
2
3

2. limit 100 offset 0 和limit 100 offset 100000000比较？
   前者远远快于后者，原因：深度分页问题，limit m offset n表示限制查询M个，跳过前n个。

4）group by、having

大部分的where可被having代替，不同的是where过滤行，而having过滤分组，用在group by之后。

select class,avg(age) as age from student 
group by class 
having avg(age)>23 /*要求平均年龄大于23*/
1
2
3

where肯定在group by 之前
where后的条件表达式里不允许使用聚合函数，而having可以。

5）聚合函数

通过case-when语句可以合并多个聚合查询为一条sql执行。

6）联表查询

luo_persons表：

luo_orders表：

要求输出：谁订购了产品，并且他们订购了什么产品？

1>嵌套查询

2>关联查询（等值联结）

等值联结明确指定联结类型可转换为inner join。

SELECT
	a.last_name, a.first_name, b.order_no
FROM
	luo_persons a,
	luo_orders b 
WHERE
	a.id_p = b.id_p	
1
2
3
4
5
6
7

3>join查询

SELECT
	last_name,
	first_name,
	order_no 
FROM
	luo_persons
	INNER JOIN luo_orders ON luo_persons.id_p = luo_orders.id_p
1
2
3
4
5
6
7

4>union查询（复合查询、并查询）

用于合并两个或多个 SELECT 语句的结果集。

1. UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作。
2. UNION ALL 不会再对结果集进行去重操作。
   优先使用UNION ALL替代UNION 操作。
3. UNION的两个结果集列必须可对应。
   相同数量的列、相似的数据类型（可互相转换）、表达式或聚集函数。
   列顺序也必须一致。

	select id_p from luo_persons 
	union 
	SELECT id_p from luo_orders
1
2
3

5>EXCEPT（或MINUS）

检索在第一个表中存在而在第二个表中不存在的行；

select  customer_id from customers except select customer_id from orders ;
1

6>INTERSECT

检索两个表交集，类似inner join。
区别：inner join只能操作表，intersect 可以操作结果集。

select  customer_id from customers 
intersect 
select customer_id from orders 
order by customer_id as
1
2
3
4

7>with as，初步表过滤

适用于pg、mysql，通过with as将表初步过滤一遍再在此基础上进行查询。

with
cr as
( 
    select CountryRegionCode from person.CountryRegion where Name like 'C%'
) 
select * from person.StateProvince where CountryRegionCode in (select * from cr);
1
2
3
4
5
6

7）判断查询

1>case-when-then

pgsql和mysql都支持。

--简单case函数
case sex
  when '1' then '男'
  when '2' then '女'
  else '其他' end;

--case搜索函数
case when sex = '1' then '男'
     when sex = '2' then '女'
     else '其他' end  
1
2
3
4
5
6
7
8
9
10

提升：通过case-when合并多个聚合查询为一条sql。

对于一些统计，比如我们要统计家庭地址address分别在a、b、c区的数量，最简单的sql怎么写呢？

select 
sum(case when (address = 'a') then 1 else 0 end) as aTotal,
sum(case when (address = 'b') then 1 else 0 end) as bTotal,
sum(case when (address = 'c') then 1 else 0 end) as cTotal
from user
group by name;
1
2
3
4
5
6

sum可以被max 等替换：

select
    tname as '姓名' ,     
    max(case ttype when '语文' then tscor else 0 end) '语文',     
    max(case ttype when '数学' then tscor else 0 end) '数学',     
    max(case ttype when '英语' then tscor else 0 end) '英语'     
from testScore     
group by tname

1
2
3
4
5
6
7
8

2>判空 COALESCE(sql, NULL)

pg函数，判断sql结果，如果sql执行不存则返回null；

n）系统属性查询

--查询一个表是否存在
select count(*) from pg_class where relname = 'tablename';
--查询所有的表
SELECT * FROM pg_tables;
--查询所有的视图
SELECT * FROM pg_views;
--查询表结构
SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull,b.description AS comment FROM pg_class c,pg_attribute a LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,pg_type t WHERE c.relname = 'person_wechat_label' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY a.attnum;
--从数据库中导出数据到文件
\copy (select * from "user") to '/tmp/1.txt';
1
2
3
4
5
6
7
8
9
10

a）similar to（pg特色）

类似like的用法，在like的基础上又增加了与POSIX正则表达式相同的模式匹配元字符。

select label FROM classify where label similar to '(36)+%' ;--检索以36开头或3636……开头的数据
select label FROM classify where label like '(36)+%' ; --检索(36)+开头的数据
1
2

b)POSIX 正则表达式

pg中有两种正则表达式：

POSIX 正则表达式：

--下面两种语句功能一致：
select 'osdba' ~'a'; --t  
select 'osdba' similar to '%a%'; --t

select 'osdba' ~'b|a';--t

1
2
3
4
5
6

c) substring函数（pg特色）
这个函数功能强大，可以使用正则表达式。

6，插入修改删除

1）upsert

更新插入一体
当主键或者unique key发生冲突时，什么都不做（同时也适用于多个字段的唯一性约束）：

INSERT INTO test.upsert_test(id, "name")
VALUES(1, 'm'),(2, 'n'),(4, 'c')
ON conflict(id)  DO NOTHING;
1
2
3

upsert方法，冲突则更新，否则插入：

insert into "public"."user"
(id, name)
values(1,'a')
ON conflict(id)
DO UPDATE SET 
name = 'b';								
1
2
3
4
5
6

批量update:

UPDATE test_user 
SET age = temp.age 
FROM
	( VALUES ( 1, '张三', 11 ), ( 3, '王五', 22 ), ( 2, '李四', 33 ) ) AS temp ( id, username, age ) 
WHERE
	test_user.id = temp.id
1
2
3
4
5
6

2）insert

--批量插入
insert into tableName(no,name) values ('1','kate'),('2','kate');

--按表中列的顺序，但如果表结构发生了变化那么对应 sql也要改。不推荐，禁止！！
insert into product values('001','001','N','N');
1
2
3
4
5

1. 有自增序列可以不插入、插入null或者0，这样mysql、pg会自己填充值；
2. 大量数据批量写（CRUD)，分批操作。
   pg中大量update会导致磁盘告急；
   大批量写回造成严重的主从延迟；
   Binlog 日志为 row 格式时会产生大量的日志；
   大事务容易阻塞；

3） insert select

将select结果插入表中，一般用于可重复执行的sql。
注：
1.insert select语句中，如果select返回多行，那么会insert多行数据。

INSERT INTO "public"."vendors"("vend_name", "vend_id") select 'vend_name1', 1 
WHERE NOT EXISTS (select 1  FROM "public"."vendors" WHERE vend_id = 1);


INSERT INTO "public"."classInfo"("studentName", "teacher") 
SELECT name, "Ms"
from student;
1
2
3
4
5
6
7

4）select into

从一个表中选取数据，然后把数据插入另一个表中。

1. 场景：创建表的备份复件、对记录进行存档。
2. select into 可以从多个表中检索数据，但只能插入到一个表中。

函数里面，把一个查询出来的值存入临时变量：

SELECT LastName,FirstName
INTO _lName,_fName  FROM Persons
1
2

也可以存入临时表中：

SELECT *
INTO Persons_backup
FROM Persons
1
2
3

5）update

update tableName set name = 'Tom' where name='kate';
update tableName set age = age + 1;
1
2

6）数据删除 delete、drop、truncate

1>delete from

删除表中所有行，保留表、不释放空间。所删除的每行记录都会进日志，可以回滚。

DELETE FROM Person WHERE LastName = 'Wilson';
DELETE FROM table_name;

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id
1
2
3
4
5
6
7

2>drop

删除表：删除内容和定义，释放空间

drop table user;    
--可重复执行sql
DROP TABLE IF EXISTS "public"."role_relation"; 
1
2
3

3>truncate

删除表中所有数据，保留表、同时释放空间（速度比delete快，但是无法撤回，日志里面只记录页释放）：

truncate table book;
1

truncate是DDL语句（Data Definition，数据定义语句），相当于用重新定义一个新表的方法把原表的内容直接丢弃了，所以执行起来很快。delete语句是DML语句（Data Manipulation，数据操作语句），把数据一条一条的删除，所以删除多行数据执行较慢。

7，索引

1）概念

pg采用堆表结构，查询时不需要回表。

2）创建索引

1>普通索引和并发索引

pg创建索引有2种方式：

1. 普通方式：
   扫表一次，会锁表，阻塞增删改、可查询；

--默认创建类型为BTree类型。desc缺省为asc。NULLS FIRST可缺省，表示空值排在非空值前面，也可以选NULLS LAST.
create [UNIQUE] INDEX indexName on tableName(fieldName desc NULLS FIRST);
--pg中针对array或json、jsonb字段创建索引，指定GIN类型索引。
create INDEX indexName on tableName using gin(fieldName);
1
2
3
4

2. 并发创建：
   扫表2次，不会锁表，更耗时。

--pg并发创建索引 启用CONCURRENTLY选项。
--pg中重建索引不支持CONCURRENTLY，要进行并行更新索引一般是先通过CONCURRENTLY创建新索引，然后删除旧索引。
--注意，使用CONCURRENTLY创建索引如果强行取消会残留无效索引，这个索引会引起更新速度变慢、唯一索引还会有唯一约束。需要手动删除这个无效索引。
create index CONCURRENTLY indexName on tableName(fieldName);
1
2
3
4

2>表达式索引创建

可以实现简单唯一约束无法实现的一些约束，如：

create unique index my_test_idx on tb_test (lower(note)); --禁止向note列插入只是大小写有区别二内容完全两天的数据行
1

3> 部分索引创建

设置一个部分索引以排除普通数值，能大大提高查询效率：

--场景：内网ip数量少但是访问频率很高，需要统计外网ip的访问
create index access_log_client_ip_ix on access_log(client_ip)
where not(client_ip > inet '192.168.100.0' and
          client_ip < inet '192.168.100.255');
1
2
3
4

4>前缀索引创建

1. 对文本的前几个字符建立索引；
2. 优点：索引更小，查询效率更快！
3. 前缀索引的比较严格按字符比较而不是根据特定于区域设置的排序规则进行比较的。
4. 场景：
   1）字段长度很长的等式比较。不适用于：<，<=，> 或 >= 比较。
   2）字段数据量大且查询频繁。

--平台：pg
--支持类型：text
CREATE INDEX test_index ON test_table (col text_pattern_ops);
--支持类型：varchar
CREATE INDEX test_index ON test_table (col varchar_pattern_ops);
--支持类型：char的B-tree索引
CREATE INDEX test_index ON test_table (col bpchar_pattern_ops);
1
2
3
4
5
6
7

3）索引 删、改

alter INDEX indexName RENAME TO newName;--索引重命名
alter INDEX indexName set TABLESPACE tableSpaceName;--表空间修改

--[CASCADE | RESTRICT]缺省默认为RESTRICT,表示有依赖会删除失败。CASCADE表示有依赖索引的对象一并删除依赖对象。
drop index if exists indexName [CASCADE | RESTRICT]; --if exists可省略
1
2
3
4
5

4）索引查询

select * from pg_indexes where tablename like 'tbl_%' and indexname like 'index_%';
1

8，函数

1. 支持函数重载
   pg允许多个函数具有相同的名称，只要参数不同即可。

1）创建

--[OR REPLACE]:是可选的，它允许修改/替换现有函数
CREATE [OR REPLACE] FUNCTION function_name (arguments)   
RETURNS return_datatype AS $variable_name$  
  DECLARE  --DECLARE:定义参数(参数名写在前面 类型写在后面)
    declaration;  
    [...]  
  BEGIN  --BEGIN~END: 在中间写方法主体
    < function_body >  
    [...]  
    --RETURN:指定要从函数返回的数据类型(它可以是基础，复合或域类型，或者也可以引用表列的类型)。
    RETURN { variable_name | value }  
    --LANGUAGE:它指定实现该函数的语言的名称。 可以是SQL，PL/pgSQL，C， Python等。
  END; LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
9
10
11
12
13

1>参数说明

参数使用例子：

 CREATE OR REPLACE FUNCTION hi_lo(
 IN a NUMERIC, 
 IN b NUMERIC,
     OUT c NUMERIC, 
     OUT hi NUMERIC,
         INOUT lo NUMERIC)
AS $$
BEGIN
 c:= GREATEST(a,b);
 hi:= LEAST(a,b);
 lo:=GREATEST(a,b);
END; $$
 
LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2>举例

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    # i 定义变量接收返回值
    DECLARE ans INT DEFAULT NULL;  
    # ii 执行查询语句，并赋值给相应变量
    SELECT 
        DISTINCT salary INTO ans
    FROM 
        (SELECT 
            salary, @r:=IF(@p=salary, @r, @r+1) AS rnk,  @p:= salary 
        FROM  
            employee, (SELECT @r:=0, @p:=NULL)init 
        ORDER BY 
            salary DESC) tmp
    WHERE rnk = N;
    # iii 返回查询结果，注意函数名中是 returns，而函数体中是 return
    RETURN ans;
END
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2）OBJECT_ID

A. 返回指定对象的对象 ID

USE master;
GO
SELECT OBJECT_ID(N'AdventureWorks.Production.WorkOrder') AS 'Object ID';
GO
1
2
3
4

B. 验证对象是否存在

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
GO
1
2
3
4
5

N是显式的将非unicode字符转成unicode字符，它来自 SQL-92 标准中的 National（Unicode）数据类型，用于扩展和标准化，在这里可以不用，写作object_id(PerPersonData)。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
      SELECT salary FROM employee；
  );
END
1
2
3
4
5
6
7

3）块结构

一个 PostgreSQL 函数由块（block）进行组织。

[ DECLARE
    声明 ]  --可以没有
BEGIN
    主体;   --必需
 ...
END;

--声明部分中的每个语句都以分号（;）结尾。 主体部分中的每个语句也以分号（;）结尾。
1
2
3
4
5
6
7
8

4）循环

在begin-end中：

  --单客户数据量(3万)
    FOR num IN 1..30000 LOOP
  --客户数量（500）
      FOR cust IN 100..600 LOOP
         ...
	  end loop;
	end loop;
1
2
3
4
5
6
7
8

    for role in (select * from role where is_delete = false) loop
       ...
    end loop;
1
2
3

9，触发器（Trigger）

1. 定义：由事件（增删改操作）自动触发执行的特殊的存储过程。
2. 场景：加强数据的完整性约束和业务规则上的约束，不推荐使用。
3. 执行顺序：
   如果有多个相同类型的触发器定义了相同的事件，他们将被触发名称是按字母顺序排列。

1>创建触发器

语句触发：（修改0行的操作依然会触发。按语句触发，而不管这条语句操作了多少行）

CREATE TRIGGER stu_trigger 
AFTER INSERT OR DELETE OR UPDATE ON stu_info   --AFTER 可以换成BEFORE，在语句执行前触发。行级别的AFTER触发器在任何语句级别的AFTER触发器之前触发。
FOR STATEMENT EXECUTE PROCEDURE stu_trigger_function (); 
1
2
3

针对字段触发：

CREATE TRIGGER stu_trigger 
AFTER INSERT OR UPDATE OF birthday OR DELETE ON stu_info   
FOR EACH ROW EXECUTE PROCEDURE stu_trigger_function (); --stu_trigger_function 为触发函数
1
2
3

行触发：(如果执行的语句没有更新实际的行，那么不会+触发)

CREATE TRIGGER example_trigger 
AFTER INSERT OR DELETE OR UPDATE ON COMPANY 
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
1
2
3

2>删除

触发器删除不会删除对应的触发函数；但删除表时，表上的触发器会一并删除。

DROP TRIGGER if exists stu_trigger ON stu_info [CASCADE | RESTRRICT]; --中括号中的可省略。CASCADE 级联删除依赖此触发器的对象；RESTRRICT是默认值，有依赖对象存在就拒绝删除。
1

3>触发函数

触发函数有返回值。
对于BEFORE和INSTEAD OF这类行级触发器来说：

1. 返回NULL，表示忽略当前行的操作。也不会触发其它触发器。
2. 返回非NULL：对于INSERT和UPDATE操作来说，返回的行将成为被插入的行或者将要更新的行。
3. 每个触发器返回的行将成为下一个触发器的输入。
   对于AFTER这类行级触发器：返回值会被忽略。

触发函数常用特殊变量：

NEW: 
1. INSERT/UPDATE 行级触发器中新的数据行，数据类型是RECORD；
2. 语句级别触发器、DELETE操作触发器 中此变量未分配。

OLD:
3. UPDATE/DELETE 行级触发器中原有的数据行，数据类型是RECORD；
4. 语句级别触发器、INSERT操作触发器 中此变量未分配。

TG_NAME： 触发器名。数据类型：name。
TG_WHEN： 是 BEFORE/AFTER 触发器。
TG_LEVEL：是 ROW/STATEMENT 触发器。
TG_OP：是 INSERT/UPDATE/DELETE/TRUNCATE 之一的字符串，表示DML语句类型。
TG_TABLE_NAME：触发器所在表的名称。同理有TG_TABLE_SCHEMA（模式）。
1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE
OR REPLACE FUNCTION stu_trigger_function () RETURNS TRIGGER AS
$BODY$
DECLARE
_birthday TIMESTAMP;
_id int;
BEGIN
    IF TG_OP = 'INSERT' THEN
        
        SELECT birthday INTO _birthday FROM stu_info WHERE name= NEW .name AND class_id= NEW .class_id;
        IF _birthday is NULL THEN
            INSERT INTO stu_info ( ID, name, birthday , class_id) VALUES ( NEW . ID, NEW .name, NEW .first_occur_time, NEW .class_id) ;
            ELSE IF NEW .birthday < _birthday THEN
                UPDATE stu_info SET ID = NEW . ID, birthday = NEW .birthday ;
            END IF ;
        END IF ;
        ELSE IF TG_OP = 'DELETE' THEN
            DELETE FROM stu_info A WHERE A . ID = OLD . ID ; RETURN OLD ;   
            ELSE IF TG_OP = 'UPDATE' THEN
                if UPDATE(birthday ) THEN
                    SELECT ID,birthday INTO _id, _birthday FROM stu_info WHERE class_id= OLD .class_id AND name= OLD .name;        
                    IF ( _id != 0 AND _birthday > OLD .birthday ) THEN
                        UPDATE stu_info SET ID = _id, birthday = _birthday ;
                    END IF ;
                END IF;
            END IF ;
        END IF ;
    END IF ;
    RETURN NEW;  --语句级触发函数可以显示的写：RETURN NULL
END ;
$BODY$
LANGUAGE plpgsql;


--删除触发函数
drop FUNCTION add_stu_trigger()；
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

4>事件触发器

pg9.3开始支持Event Trigger，主要用于弥补pg以前版本不支持DDL触发器的不足。

10，存储过程（Stored Procedure）

定义：是一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

1）优点

1. 执行效率高
   存储过程因为SQL 语句已经预编译过了，因此运行的速度比较快。
2. 降低了客户机和服务器之间的通信
   调用时传参即可，不需要传送sql语句，提高了io效率。
3. 方便实施企业规则（提高了可维护性、安全性）
   可以把企业规则的运算程序写成存储过程放入数据库服务器中，由RDBMS管理，既有利于集中控制，又能够方便地进行维护。
   当用户规则发生变化时，只要修改存储过程，无须修改其他应用程序。
   允许模块化程序设计，就是说只需要创建一次过程，以后在程序中就可以调用该过程任意次，类似方法的复用。
   增强了使用的安全性，充分利用系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。程序员直接调用存储过程，根本不知道表结构是什么，有什么字段，没有直接暴露表名以及字段名给程序员。
4. 安全性高
   可设定只有某些用户才具有对指定存储过程的使用权。

2）缺点

调试麻烦（至少没有像开发程序那样容易），可移植性不灵活（因为存储过程是依赖于具体的数据库）。

3）场景（不推荐使用存储过程）

当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程；
过多的使用存储过程会降低系统的移植性。

4）使用

1. 创建

use test1
set ansi_nulls on
go
set quoted_identifier on
go
create procedure procedure_student
	-- add the parameters for the stored procedure here
	@gradeid int,
	@gradename varchar(10) --传入的参数
as
begin
	--计算内容
end
go
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2. 执行

exec dbo.procedure_student 1,'g'
1

11，视图

1）特性

1. 虚拟表、临时表，每次调用视图时生成；
2. 只读；
3. 不能提高检索效率。
4. 不支持索引，但视图可以基于索引生成
5. 支持规则和触发器，可通过创建触发器或rule实现视图更新；

2）场景

1. 重用SQL语句；
2. 简化复杂SQL操作（生成视图），重用查询且不需要知道基本查询细节。
3. 保护数据。用户有表的部分权限。
4. 更改数据格式和表示。视图可返回与底层表不同的表示和格式。

3）操作

--获取数据库中所有view
select * from pg_views where schemaname='public';
1
2

--创建视图
CREATE OR REPLACE VIEW view_name(studentName, studentAge)  --(studentName, studentAge) 可以去掉，加上是重命名列名
AS 
SELECT user_info.name, user_info.age from user_info;

--删除视图
DROP VIEW view_name;
1
2
3
4
5
6
7

12，物化视图（实体视图）

经笔者测试，物化视图创建比单纯的select语句快接近10倍。

支持平台：mysql、pg9.3、orcle。

1）特性

1. 将结果集持久化在表中、支持索引、不支持触发器和rule创建；
   占据数据库的物理空间，和基础表一样创建时会注册 “tableoid” 、“cmax”、“xmax”、“cmin”、“xmin”、“ctid”系统字段。
2. 一个物化视图对应一个SQL语句，查询时去对应的结果集表查询；
   和视图不同，不查询创建实体化视图的基表，而是直接查询对应的结果表。
3. 通过定期的刷新机制来更新数据；

2）场景

1. 减轻网络负担
   通过实体化视图将数据从一个数据库分发到多个不同的数据库上，通过对多个数据库访问来减轻对单个数据库的网络负担。
2. 搭建分发环境
   通过从一个中央数据库将数据分发到多个节点数据库，达到分发数据的目的。
3. 复制数据子集
   实体化视图可以进行行级/列级的筛选，这样可以复制需要的那一部分数据。
4. 数据汇总、预计算、复制或分发数据
   可以提高涉及到的SUM,COUNT,AVG,MIN,MAX等的表的查询的速度。
5. 统计数据和下钻列表保持一致。

3）操作

1>创建物化视图

-- pg
CREATE MATERIALIZED VIEW my_view 
AS
  SELECT PART_ID, MAX(ID)  MAX_ID
  FROM PART_DETAIL GROUP BY PART_ID; 
1
2
3
4
5

2>创建索引

-- 如果刷新时不带CONCURRENTLY则无需创建唯一索引
CREATE UNIQUE INDEX my_view_unique ON my_view(PART_ID);
1
2

3>数据刷新

1. pg: 全量刷新
   刷新过程中会对该物化视图的所有的select操作阻塞；
   刷新效率快；

REFRESH MATERIALIZED VIEW my_view;
1

2. pg：增量刷新（快速刷新）
   必须具备唯一索引；
   靠唯一索引对比基表查询的数据，只要有一个字段不同就填充差量数据；所有数据刷新完成后提交事务；
   刷新慢，对比全量刷新非常慢，笔者对比了30w数据，全量刷新需要1s，增量筛选要12s。
   不会阻塞物化视图的select操作。

-- pgsql命令行操作：利用watch命令每120s刷新一次物化视图
-- sql不支持定时命令，可通过java定时任务去调度
REFRESH MATERIALIZED VIEW CONCURRENTLY my_view; \watch 120;
1
2
3

4>删除

pg:

DROP MATERIALIZED VIEW PUBLIC."my_view" ;
1

13，用户及权限管理

pg中角色和用户没区别。超级用户为postgres。

14，规则（Rule）

规则系统更准确地说是查询重写规则系统，使用时也可以被函数和触发器替代，但原理和使用场景不同（这在上文讲分表的时候就提到过）。对于批量操作，规则比触发器效率更高。

1>SELECT规则

create RULE "_RETURN" as on select to myview Do instead select * from mytable;
#相当于create table myview (same column list as mytab);
1
2

2>更新规则

create [or replace] RULE name as on event  #event 值为SELECT/INSERT/UPDATE/DELETE
to tableName [where condition] 
do [also | instead] {nothing | command}  # also表示执行原操作后还执行一些附加操作 如：also insert into mytab_log...; instead表示把原操作替换为后面的command操作
1
2
3

3>权限

规则从属于表和视图

15，事务

获取当前事务id：

select txid_current();
1

事务提交：

begin;
--sql语句。单条执行的sql语句也是一个事务。
commit;
1
2
3

事务回滚：

begin;
--sql语句
rollback;
1
2
3

16，好用工具

1）PgBouncer

为pg提供的一个轻量级连接池工具。

2）Slony-I

基于触发器的两个pg的逻辑同步。

3）Bucardo

双向同步工具，可以实现pg的双（多）master方案。

4）pgpool-II

pg和客户端之间的中间件。

5）Postgres-XC

Postgres-XC是基于pg实现的真正的数据水平拆分的分布式数据库。