Postgresql逻辑结构整理
|
数据库：创建数据库：create database name [with owner = user_name --指定新建数据库属于哪个用户，不指定则属于当前执行命令的用户； template = template --模板名，不指定则默认使用 template1. encoding = encoding --数据库使用的字符编码， 编码与区域设置必须与模板相一致。template0公认不包含任何受字符集编码或排序影响的数据或索引，故可作为任意字符集数据库的模板。 lc_collate = lc_collate lc_ctype - lc_ctype tablespace tablespace --指定和新数据库关联的表空间的名字 connection limit = connlimit] -数据库可以接受多少并发连接数，默认为-1，表示没有限制。 日常使用中很少指定数据库的字符集，因为Postgresql数据库服务端不支持通常的字符集“GBK”,“GB18030”,所以一般都使用“UTF8”字符集来支持中文 修改数据库：ALTER DATABASE name rename TO new_name; ALTER DATABASE name owner TO new_owner; ALTER DATABASE name SET tablespase 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; --修改数据库最大连接数 postgres=# alter database testdb2 connection limit 100; ALTER DATABASE postgres=# drop database [IF EXISTS] name ; 如果还有人连接该库，将不能删除； 常见问题： 1.能否在事务块中创建或删除数据库？ 答：不能 2.能否在数据库中修改数据库 答：能 ------------------------------------------------------------------------- 模式模式（schema）：可理解为一个命名空间或目录，不同的模式下可以有相同名称的表、函数等对象且不冲突。 允许多个用户在使用同一个数据库时彼此互不干扰； 把数据库对象放在不同模式下，然后组织成逻辑组，便于管理； 第三方的应用可以放在不同的模式中，就不会和其他对象名字冲突了。 --创建 CREATE SCHEMA schemaname ; postgres=# c testdb2; You are now connected to database "testdb2" as user "postgres". testdb2=# dn; List of schemas Name | Owner --------+---------- public | postgres (1 row) testdb2=# create schema test; CREATE SCHEMA testdb2=# dn List of schemas Name | Owner --------+---------- public | postgres test | postgres (2 rows) testdb2=# --删除 postgres=# drop schema test_schema; DROP SCHEMA postgres=# --下面的命令为用户osdba建的模式，名字也定为osdba; create schema authorization osdba; 公共模式schema_name.table_name; 通常创建和访问表的时候都不用指定模式，默认访问的是“public”模式。 模式搜索路径 public模式总是在搜索路径中，所以public为建表的默认模式。 testdb2=# show search_path; search_path ----------------- "$user",public (1 row) 模式权限默认情况下，用用户无法访问不属于他们的对象。若要访问，模式的所有者必须在模式上赋予他们“USAGE”权限。为了让用户使用模式中的对象，可能需要赋予适合该对象的额外权限。 默认情况下，每个人在“public”模式上都有“create"和”usage“权限。如果撤销该权限： revoke create on schema public from PUBLIC; 第一个public是模式名称，第二个PUBLIC是所有用户意思。 模式移植性： 因其它数据库没有模式概念，需考虑数据库的兼容； ------------------------------------------------------------------ ------------------------------------------------------------------- 表主键约束 一般的表都有主键，如果由一个字段组成，直接在字段定义后加上“primary key”关键字； create table test1(id int primary key,note varchar(20)); 如果为两个以上字段，则为复合主键，语法为： create table test02(id1 int,id2 int,note vachar(20),CONSTRAINT pk_test02 primary key(id1,id2)); 唯一约束 create table test03(id1 int,id3 int,CONSTRAINT pk_test03 primary key(id1,id2),CONSTRAINT uk_test03_id3 UNIQUE(id3)); 检查约束 create table child(name varchar(20),age int,note text,CONSTRAINT ch_child_age CHECK(age >0 and age<18)); 复杂表结构：create table baby(like child); 注意：没有把源表上的约束复制过来，如果要复制，需加上“INCLUDING”,可用的“INCLUDES”选项为： INCLUDING DEFAULT INCLUDING CONSTRAINT INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS INCLUDING ALL 也可用create table as 来建表 create table baby as select * from child with no data; 临时表：会话级临时表：会话的生命周期 事务级临时表：事务的生命周期 testdb2=# create temporary table tmp_t1(id int primary key,note text); CREATE TABLE testdb2=# d List of relations Schema | Name | Type | Owner -----------+-----------+-------+---------- pg_temp_7 | tmp_t1 | table | postgres public | card_test | table | postgres public | date_test | table | postgres public | jtest01 | table | postgres public | jtest02 | table | postgres public | jtest03 | table | postgres public | t_number | table | postgres public | t_user | table | postgres public | test | table | postgres public | testtab05 | table | postgres public | testtab6 | table | postgres (11 rows) 临时表在schema下生成一个特殊的表，这个schema的名称为pg_tmp_XX","XX"代表一个数字，不同的sseion这个数字是不同的。 testdb2=# insert into tmp_t1 values(1,'111'); INSERT 0 1 testdb2=# insert into tmp_t1 values(2,'122'); INSERT 0 1 testdb2=# select * from tmp_t1; id | note ----+------ 1 | 111 2 | 122 (2 rows) 默认情况下，创建的临时表是会话级别的，如果想创建事务级的临时表，可加上“ON COMMIT DELETE ROWS” ------------------------------------------------------------------ 默认值字段设置默认值 testdb2=# create table student(no int,name varchar(20),age int default 15); CREATE TABLE testdb2=# insert into student (no,name) values(1,'张三'); INSERT 0 1 testdb2=# insert into student (no,name) values(2,'张将'); INSERT 0 1 testdb2=# select * from student; no | name | age ----+------+----- 1 | 张三 | 15 2 | 张将 | 15 (2 rows) --可使用DEFAULT来代替默认值。如果没有声明默认值，则为null. testdb2=# update student set age = 16; UPDATE 2 testdb2=# select * from student; no | name | age ----+------+----- 1 | 张三 | 16 2 | 张将 | 16 (2 rows) testdb2=# update student set age = DEFAULT where no = 2; UPDATE 1 testdb2=# select * from student; no | name | age ----+------+----- 1 | 张三 | 16 2 | 张将 | 15 (2 rows) testdb2=# create table blog(id int,title text,created_date timestamp default now()); CREATE TABLE testdb2=# insert into blog values(1,'Postgresql创建临时表'); INSERT 0 1 testdb2=# select * from blog; id | title | created_date ----+----------------------+---------------------------- 1 | Postgresql创建临时表 | 2017-10-07 18:20:47.706399 (1 row) testdb2=# ---------------------------------------------------------------------------------------- 约束（1）检查约束 create table persons( name varchar(40), age int CHECK(age >=0 and age <=150), sex boolean ); create table books( book_no integer, name text, price numeric CHECK (price >0), discounted_price numeric CHECK(discounted_price > 0),--字段约束 CHECK(price > discounted_price) --表约束 ); （2）非空约束 create table books( book_no integer not null, price numeric ); （3）唯一约束 create table books( book_no integer unique, price numeric ); （4）外键约束 表之间关系的一种约束，参照完整性。 create table class( class_no int primary key, class_name varchar(40) ); create table student( student_no int primary key, student_name varchar(40), age int, class_no int references class(class_no) ); ---------------------------------------------------------------------------- 修改表：增加字段： alter table class add column class_teacher varchar(40); alter table class add column class_teacher varchar(40) CHECK (class_teacher <> ''); 删除字段： alter table class drop column class_teacher; alter table class drop column class_no CASCADE;--会把引用class_no的外键也删除掉 增加约束： alter table student add CHECK(age < 19); alter table class ADD CONSTRAINT unique_class_teacher UNIQUE (class_teacher); alter table student alter column student_name set not null; --非空约束 删除约束： alter table student drop CONSTRAINT constraint_name; --删除前需要知道约束名称，一般用 “d”把约束名称查出来，再删除； 修改默认值： alter table student alter column set default 13;--不会影响现有的行数据，只将将来的insert命令改变默认值。 删除默认值： alter table student alter column age drop default; 修改字段类型： alter table student alter column student_name TYPE text;--只有在字段里现有的每个项都可以隐式地转换成新类型时，才能成功； 重命名字段： alter table books RENAME COLUMN book_no TO book_id; 重命名表： alter table class RENAME to classes; ------------------------------------------------------------------------------ 表的继承create table persons( name text, sex boolean ); create table students( class_no int )INHERITS(persons); insert into students values('张三',15,true,1); insert into students values('张花',16,false,2); select * from persons; postgres=# select * from persons; name | age | sex ------+-----+----- 张三 | 15 | t 张花 | 16 | f (2 rows) postgres=# select * from students; name | age | sex | class_no ------+-----+-----+---------- 张三 | 15 | t | 1 张花 | 16 | f | 2 (2 rows) --更改students表中数据，通过persons表也可看到变化： update students set age = 13 where name='张三'; postgres=# update students set age = 13 where name='张三'; UPDATE 1 postgres=# select * from persons; name | age | sex ------+-----+----- 张花 | 16 | f 张三 | 13 | t (2 rows) --往persons表中插入一条数据，查询student表是看不到这条数据的； 查询父表会把子表的数据显示出来，反过来不行 如果想查询父表的数据，在表名前加上only 所有父表的检查约束和非空约束会自动被子表继承，其它类型的约束不会 一个子表可以从多个父表继承，它将拥有所有父表字段的总和，并且子表中的字段也会加入。 如果一个字段出现在多个父表，或出现在父表和子表中，这些字段将进行融合，只保留一个。 ------------------------------------------------------------------------------------ 分区表表分区就是把逻辑上的一个大表分割成物理上的几个小块。分区可提供若干好处： 1.删除历史数据，通过分区 快； 2.查询同一个分区内数据 快； 3.用到较少的历史数据通过分区存放到其它物理介质上。 什么时候使用分区表？表的大小超过了数据库服务器物理内存的大小 使用继承实现分区表时，一般会让父表为空，数据存储在子表中。 建分区表步骤： 1.创建父表，所有分区都从它继承。这个表没有数据，没有任何检查约束，除非想约束所有的分区。 2.创建几个子表，每个都是从主表上继承的，通常不会增加任何字段，我们把这些子表称为分区，实际上它们就是普通的Postgresl表。 3.给分区表增加约束，定义每个分区允许的键值 4.对于每个分区，在关键字字段上创建一个索引，和想创建的其它索引。如果关键字唯一，则创建唯一约束或主键约束。 5.定义一个规则或触发器，把对主表的数据插入重定向到合适的分区表。 6.确保constraint_exclusion里的配置参数postgresql.conf是打开的。打开后，如果查询中where子句的过滤条件与分区的约束条件匹配，则这个查询只查询这个分区，而不会查询其它分区。 分区表示例：假设一张销售明细表： create table sales_detail( product_id int not null, price numeric(12,2), amount int not null, sale_date date not null, buyer varchar(40), buyer_contact text ); 建分区表： create table sales_detail_y2017m07 (check (sale_date >= DATE '2017-07-01' and sale_date < DATE '2017-08-01')) inherits(sales_detail); create table sales_detail_y2017m08 (check (sale_date >= DATE '2017-08-01' and sale_date < DATE '2017-09-01')) inherits(sales_detail); create table sales_detail_y2017m09 (check (sale_date >= DATE '2017-09-01' and sale_date < DATE '2017-10-01')) inherits(sales_detail); create table sales_detail_y2017m10 (check (sale_date >= DATE '2017-10-01' and sale_date < DATE '2017-11-01')) inherits(sales_detail); 分区表上建的检查约束，只允许插入本月数据； 一般情况下，还可以在分区键“sale_date”上建索引： create index sale_detail_y2017m07_sale_date ON sales_detail_y2017m07 (sale_date); create index sale_detail_y2017m08_sale_date ON sales_detail_y2017m08 (sale_date); create index sale_detail_y2017m09_sale_date ON sales_detail_y2017m09 (sale_date); create index sale_detail_y2017m10_sale_date ON sales_detail_y2017m10 (sale_date); 如果有需要，还可以在其它字段上建索引。 自动且正确的把数据插入到正确的分区：使用触发器 create or replace function sale_detail_insert_trigger() RETURNS TRIGGER AS $$ begin if(NEW.sale_date >= DATE'2017-07-01' and NEW.sale_date < DATE '2017-08-01') then insert into sales_detail_y2017m07 values(NEW.*); elsif(NEW.sale_date >= DATE'2017-08-01' and NEW.sale_date < DATE '2017-09-01') then insert into sales_detail_y2017m08 values(NEW.*); elsif(NEW.sale_date >= DATE'2017-09-01' and NEW.sale_date < DATE '2017-10-01') then insert into sales_detail_y2017m09 values(NEW.*); elsif(NEW.sale_date >= DATE'2017-10-01' and NEW.sale_date < DATE '2017-11-01') then insert into sales_detail_y2017m10 values(NEW.*); else raise exception 'Date out of range. Fix the sale_detail_insert_trigger() function!'; END IF; RETURN NULL; end; $$ language plpgsql; create trigger insert_sale_detail_trigger before insert on sales_detail for each row execute procedure sale_detail_insert_trigger(); -------------------------------------------------------------------------- 该部分是因为建的触发器有问题 testdb2=# insert into sales_detail values(1,43.12,1,date'20170-09-14','四远方','杭州钱塘江区小碗城'); ERROR: Date out of range. Fix the sale_detail_insert_trigger() function! CONTEXT: PL/pgSQL function sale_detail_insert_trigger() line 12 at RAISE testdb2=# insert into sales_detail values(1,date'2017-09-14','杭州钱塘江区小 碗城'); ERROR: "sale_detail_y2017m09_sale_date" is an index LINE 1: insert into sale_detail_y2017m09_sale_date values(NEW.*) ^ QUERY: insert into sale_detail_y2017m09_sale_date values(NEW.*) CONTEXT: PL/pgSQL function sale_detail_insert_trigger() line 8 at SQL statement ----------------------------------------------------------------------------- testdb2=# insert into sales_detail values(1,'杭州钱塘江区小 碗城'); INSERT 0 0 testdb2=# testdb2=# insert into sales_detail values(13,56.02,31,date'2017-10-14','王思聪','上海浦东新区万达大酒店'); INSERT 0 0 testdb2=# testdb2=# insert into sales_detail values(13,date'2017-11-14','上海浦东新区万达大酒店'); ERROR: Date out of range. Fix the sale_detail_insert_trigger() function! CONTEXT: PL/pgSQL function sale_detail_insert_trigger() line 12 at RAISE testdb2=# testdb2=# select * from sales_detail ; product_id | price | amount | sale_date | buyer | buyer_contact ------------+-------+--------+------------+--------+------------------------ 1 | 43.12 | 1 | 2017-09-14 | 四远方 | 杭州钱塘江区小碗城 13 | 56.02 | 31 | 2017-10-14 | 王思聪 | 上海浦东新区万达大酒店 (2 rows 分区的优化技巧：打开约束排除（constraint_exclusion）是一种查询优化技巧； 在sql查询中将where语句中的过滤条件与表上的check条件进行对比，就可得知不需要扫描的分区，从而跳过相应的分区表，性能也得到提高，如下： testdb2=# explain select count(*) from sales_detail where sale_date >= Date'2017-09-01'; QUERY PLAN ------------------------------------------------------------------------------------ Aggregate (cost=33.04..33.05 rows=1 width=8) -> Append (cost=0.00..32.29 rows=300 width=0) -> Seq Scan on sales_detail (cost=0.00..1.54 rows=14 width=0) Filter: (sale_date >= '2017-09-01'::date) -> Seq Scan on sales_detail_y2017m09 (cost=0.00..15.38 rows=143 width=0) Filter: (sale_date >= '2017-09-01'::date) -> Seq Scan on sales_detail_y2017m10 (cost=0.00..15.38 rows=143 width=0) Filter: (sale_date >= '2017-09-01'::date) (8 rows) 如果把参数 "constraint_exclusion"设置成off,则会扫描每张分区子表，如下： testdb2=# set constraint_exclusion='off'; SET testdb2=# explain select count(*) from sales_detail where sale_date >= Date'2017-09-01'; QUERY PLAN ------------------------------------------------------------------------------------ Aggregate (cost=64.50..64.51 rows=1 width=8) -> Append (cost=0.00..63.04 rows=586 width=0) -> Seq Scan on sales_detail (cost=0.00..1.54 rows=14 width=0) Filter: (sale_date >= '2017-09-01'::date) -> Seq Scan on sales_detail_y2017m07 (cost=0.00..15.38 rows=143 width=0) Filter: (sale_date >= '2017-09-01'::date) -> Seq Scan on sales_detail_y2017m08 (cost=0.00..15.38 rows=143 width=0) Filter: (sale_date >= '2017-09-01'::date) -> Seq Scan on sales_detail_y2017m09 (cost=0.00..15.38 rows=143 width=0) Filter: (sale_date >= '2017-09-01'::date) -> Seq Scan on sales_detail_y2017m10 (cost=0.00..15.38 rows=143 width=0) Filter: (sale_date >= '2017-09-01'::date) (12 rows) testdb2=# ------------------------------------------------------------------------------------------- 触发器是一种由事件自动触发执行的特殊的存储过程，这些事件可以是insert,update,delete等。 经常用于加强数据的完整性约束和业务规则上的约束。 建触发器步骤 先为触发器建一个执行函数，此函数的返回类型为触发器类型；然后即可创建触发器。 CREATE [CONSTRAINT] TRIGGER name {BEFORE | AFTER | INSTEAD OF} {EVENT [OR ...]} ON table_name [FROM referenced_table_name] {NOT DEFERRABLE | [DEFERRABLE] {INITIALLY IMMEDIATE | INITIALLY DEFERRED}} [FOR [EACH] {ROW | STATEMENT}] [WHEN (condition)] EXECUTE PROCEDURE function_name (arguments) 示例： create table student_info( student_no int primary key, age int ); create table score_info( student_no int, chinese_score int, math_score int, test_date date ); 如果想删除学生表中一条记录时，把学生在成绩表（score_info)中的成绩记录也删除，就可以使用触发器。先建触发器的执行函数： CREATE OR REPLACE FUNCTION student_delete_trigger() RETURNS TRIGGER AS $$ begin delete from score_info where student_no = OLD.student_no; return OLD; end; $$ language plpgsql; 再创建触发器： create trigger delete_student_trigger after delete on student_info for each row execute procedure student_delete_trigger(); 插入测试数据： insert into student_info values(1,'xx狗1',14); insert into student_info values(2,'xx狗2',15); insert into student_info values(3,'xx狗3',16); insert into student_info values(4,'xx狗4',17); insert into score_info values(1,84,75,date'2017-05-22'); insert into score_info values(1,81,73,date'2017-06-22'); insert into score_info values(2,87,date'2017-07-22'); insert into score_info values(2,64,85,date'2017-04-22'); insert into score_info values(3,94,25,date'2017-05-22'); insert into score_info values(3,55,88,date'2017-07-22'); insert into score_info values(4,44,53,date'2017-09-22'); insert into score_info values(4,23,83,date'2017-04-22'); 把学生好为3的学生删除掉： testdb2=# delete from student_info where student_no = 3; DELETE 1 再查下score_info表： ^ testdb2=# select * from score_info ; student_no | chinese_score | math_score | test_date ------------+---------------+------------+------------ 1 | 84 | 75 | 2017-05-22 1 | 81 | 73 | 2017-06-22 2 | 87 | 75 | 2017-07-22 2 | 64 | 85 | 2017-04-22 4 | 44 | 53 | 2017-09-22 4 | 23 | 83 | 2017-04-22 (6 rows) testdb2=# 语句级触发器指执行每个sql时，只执行一次； 行级触发器指每行就会执行一次； 一个修改0行的操作仍会导致语句级触发器被执行。 create table log_student( update_time timestamp,--操作时间 db_user varchar(40),--操作的数据库用户名 opr_type varchar(6) --操作类型：insert、delete、update ); 创建记录log的触发器函数，如下： create function log_student_trigger() returns trigger as $$ begin insert into log_studnet values(now(),user,TG_OP); return null; end; $$ language "plpgsql"; 在student上创建一个语句级触发器： create trigger log_student_trigger after insert or delete or update on student for statement execute procedure log_student_trigger(); testdb2=# insert into student values(1,'张生',14),(2,'水生','25'); INSERT 0 2 testdb2=# select * from log_student; update_time | db_user | opr_type ----------------------------+----------+---------- 2017-10-11 13:18:46.732591 | postgres | INSERT (1 row) testdb2=# 从上可看出：虽然插入了2行，但只有一条语句，所以在log_studnet上只记录了一次操作。 testdb2=# update student set age = 18; UPDATE 4 testdb2=# select * from log_student; update_time | db_user | opr_type ----------------------------+----------+---------- 2017-10-11 13:18:46.732591 | postgres | INSERT 2017-10-11 13:21:22.631776 | postgres | UPDATE (2 rows) testdb2= 虽然update更新了4行，但log_student只记录了一条记录。语句级触发器是按语句进行触发的，不管实际操作了多少行。 下面的更新，实际没有更新任何数据，也会被触发： testdb2=# update student set age = 19 where no = 3333; UPDATE 0 testdb2=# select * from log_student; update_time | db_user | opr_type ----------------------------+----------+---------- 2017-10-11 13:18:46.732591 | postgres | INSERT 2017-10-11 13:21:22.631776 | postgres | UPDATE 2017-10-11 13:24:13.225804 | postgres | UPDATE (3 rows) testdb2=# 行级触发器： 先清掉log_student表； testdb2=# delete from log_student; DELETE 3 testdb2=# create trigger log_student_trigger2 after insert or delete or update on student for row execute procedure log_student_trigger(); 插入数据： testdb2=# insert into student values(11,(22,'25'); INSERT 0 2 testdb2=# select * from log_student; update_time | db_user | opr_type ----------------------------+----------+---------- 2017-10-11 13:42:03.097678 | postgres | INSERT 2017-10-11 13:42:03.097678 | postgres | INSERT 2017-10-11 13:42:03.097678 | postgres | INSERT (3 rows) 插入了2条数据，应该是2条日志才对，但实际得到了3条，不知道怎么回事。 更改数据： testdb2=# update student set age = 19 ; UPDATE 6 testdb2=# select * from log_student; update_time | db_user | opr_type ----------------------------+----------+---------- 2017-10-11 13:42:03.097678 | postgres | INSERT 2017-10-11 13:42:03.097678 | postgres | INSERT 2017-10-11 13:42:03.097678 | postgres | INSERT 2017-10-11 13:44:36.990037 | postgres | UPDATE 2017-10-11 13:44:36.990037 | postgres | UPDATE 2017-10-11 13:44:36.990037 | postgres | UPDATE 2017-10-11 13:44:36.990037 | postgres | UPDATE 2017-10-11 13:44:36.990037 | postgres | UPDATE 2017-10-11 13:44:36.990037 | postgres | UPDATE 2017-10-11 13:44:36.990037 | postgres | UPDATE (10 rows) testdb2=# 更改了6条，却又7条更改记录。 删除触发器： drop trigger [if exists] name on table [cascade | restrict]; if exists:如果指定的触发器不存在，则发出一个notice而不是抛出一个错误。 cascade:级联操作依赖此触发器的对象 restirct:默认值，如果有依赖对象的存在，拒绝删除； drop trigger user_new_name_student_trigger on student; 删除触发器时，触发器的函数不会被删除； 当表删除时，表上的触发器也会被删除； 触发器函数都有返回值，语句级触发器应该总返回null,即必须显式地在触发器函数中写上“return null”,如果没有写，将报错。 触发器函数中的特殊变量： NEW：该变量为insert/update操作触发的行级触发器中存储的新的数据行，数据类型是record。 OLD: 该变量为delete/update操作触发的行级触发器中存储的旧的数据行，数据类型是record。 TG_NAME: 数据类型是name，包含实际触发的触发器名。 TG_WHEN: 内容为 "BEFORE"或"AFTER"的字符串用于指定是BEFORE触发器还是AFTER触发器 TG_LEVEL: 内容为 "ROW"或"STATEMENT"的字符串用于指定是语句触发器还是行级触发器 TG_OP: 内容为"INSERT","UPDATE","DELETE","TRUNCATE"之一的字符串，用于指定DML语句类型。 TG_RELLD: 触发器所在的表的OID TG_RELNAME: TG_TABLE_NAME: 触发器所在的表的名称。 TG_TABLE_SCHEMA: 触发器所在的表的模式 TG_NARGS: 在create trigger语句里赋予触发器过程的参数个数。 TG_ARGV[]:为 text类型的一个数组，是create trigger语句里的参数。 表空间：表空间实际上是为表指定一个存储的目录。 在创建数据库的时候可以为数据库指定默认表空间。 创建表和索引时可以指定表空间，这样表和索引就可以存储到表空间对应的目录下。 创建表空间示例： CREATE TABLESPACE tbs_data location '/data/pgdata'; 创建数据库时指定默认表空间： create database db01 tablespace tbs_data; 改变一个数据库的默认表空间： alter database db01 set TABLESPACE tbs_data; 注意： 1.该操作时，必须没有人连接到这个库，否则报错 is being accessed by other users.detail:there is 1 other session using the database. 2.改变默认表空间时，数据库中已有表的表空间不会改变。 创建表时指定表空间： create table test01(id int,note text) tablespace tbs_data; 创建索引时指定表空间： create index idx_test01_id on test01(id) tablespace tbs_data; 增加主键时，指定主键索引的表空间，如下： alter table test01 add constraint pk_test01_id primary key (id) using index tablespace tbs_data; 把表从一个表空间移动到另一个表空间：(会锁表) alter table test01 set tablespace pg_default; ---------------------------------------------------------- 视图：查询语句定义的虚拟表 1.可使复杂的查询易于理解和使用； 2.安全，隐藏敏感神经； 3.把函数的返回结果映射成视图； ------------------------------------------------------------- 索引索引的好处与代价 索引的分类：B-tree：适合处理等值查询和范围查询； Hash：只能处理简单的等值查询； GiST：不是一种单独的索引类型，而是一种架构，可在这种架构上实现很多不同的索引策略。 SP-GiST:即空间分区GiST索引，提高GiST索引在某些情况下特性。 GIN：反转索引。可以处理包含多个键的值，如数组等。 创建索引一般在建索引过程中，会把表的数据读一遍，这个过程所用的时间由表的大小决定，对于比较大的表，可能会花很久的时间。 建索引过程中，对表的查询可正常进行，对表的增删改需要等索引建完后才能进行； create [UNIQUE] index [CONCURRENTLY] on table_name [USING method] ({column_name | (expression)} [COLLATE collation] [opclass] [ASC | DESC] [NULLS {FIRST | LAST})] ) [with (storage_parameter = value [,...])] 示例： create table contacts( id int primary key, name varchar(40), phone varchar(32)[], address text ); --name上建B-tree索引 create index idx_contacts_name on contacts(name); --phone上建GIN索引 create index idx_contacts_phone on contacts using gin(phone); select * from contacts where phone @> array['132323443;::varchar(32)]; 注意： @> 是数组的操作符，表示“包含”的意思，GIN索引能在 “@>”上起作用。 创建索引时可以指定存储参数“WITH (storage_parameter=value)”,常用的参数为FILLFACTOR.如： create index idx_contacts_name on contacts(name) with (FILLFACTOR=50); 可按降序建索引： create index idx_contacts_name on contacts(name desc); 如果name中有空值，可在建索引时，指定空值排在非空值前面： create index idx_contacts_name on contacts(name desc nulls first); 或空值排在非空值后面： create index idx_contacts_name on contacts(name desc nulls last); 并发创建索引鉴于创建索引过程中，不能对表做更新操作，当表数据量大时，耗时很长，Postgresql支持不长时间阻塞更新的情况下创建索引，这是通过在create index中加concurrently(并发创建索引)选项来实现的。当该选项被使用时，postgresql会执行表的两次扫描，因此需要更长一些的时间来建索引。 一个表经过频繁更新后，若要重新建索引： postgresql支持在同个字段上建两个索引，可使用“CONCURRENTLY”选项建一个新的索引，然后把旧的索引删掉，这样相当于重建。 并发创建索引的时候，如果索引在创建的过程中被强行取消，可能会留下一个无效索引，这个索引仍会导致更新变慢。 如果创建的是一个唯一索引，这个无效的索引还会导致插入重复值失败。此时，手工删除此索引就可以了。 修改索引--给索引改名 ALTER INDEX name RENAME TO new_name; --把索引移到表空间下 ALTER INDEX name SET TABLESPACE tablespace_name; --把索引的填充因子（filfactor）设置为50 ALTER INDEX name SET (storage_parameter = value); ALTER INDEX name SET (filfactor = 50); --把填充因子重置为默认值 ALTER INDEX name RESET (storage_parameter); ALTER INDEX name RESET (filfactor); 删除索引DROP INDEX [IF EXISTS] name [cascade | restrict]; 删除索引时默认使用选项“restrict”,所以不加关键字“restrict”都是一样，如果有依赖对象依赖这个索引，则会删除失败。而使用“cascade”，表示当有依赖于这个索引的对象时，一并把这些对象删掉，如外键约束。 用户及权限管理在postgresql中，用户与角色是没有区别的，在整个数据库实例中都是全局的，且在同一个实例中的不同数据库中，看到的用户也都是相同的。 安装数据库的时候，预定义的超级用户与初始化该数据库的操作系统用户名相同，如果数据库建立在系统用户“postgres”下的，这个数据库超级用户就是“postgres”。用这个用户连接数据库，然后创建更多的用户。 创建用户和角色：create role name [[WITH] option [...]] --默认没有 login 权限 或 create user name [[WITH] option [...]] --默认有 login 权限 option内容如下： superuser|nosuperuser:表示创建出的用户是否为超级用户，只有超级用户才能创建超级用户。 createdb|nocreatedb:创建的用户是否有 create database权限； createrole|nocreaterole: createruser|nocreateruser: inherit|noinherit: 创建的用户拥有某一个或某几个角色，若指定inherit表示用户自动继承相应角色的权限，否则没有该角色的权限。 login|nologin:是否有login权限 CONNECTION LIMIT connlimit: 用户可以使用并发连接数量，默认为-1,表示没有限制。 [ENCRYPTED|UNENCRYPTED] PASSWORD 'password':用于控制存储在系统表里的口令是否加密。 VALID UNTIL 'timestamp':密码失效时间，如果不指定，口令将永远有效； 权限管理：删除一个对象及任意修改它的权力都不赋予别人，它是所有者固有的，不能被赋予和撤销。 一个用户的权限分为两类： 一类是在创建用户时就指定的权限，如： 超级用户权限； 创建数据库权限； 是否允许LOGIN的权限； 这些权限可使用alter role命令来修改。 还有一类是用grant和revoke来管理的，如下： 创建模式； 创建临时表； 连接某个数据库； 创建数据库对象，如：表，视图，函数等； 对表做select,insert,delete操作； 对序列进行查询、使用、更新等； 在声明表上创建触发器； 把表和索引建到指定表空间等； alter role_name [[WITH] option [...]] grant some_privileges on database_object_type object_name TO role_name; postgredsql只允许超级用户使用PL语言写函数； ---------------------------------------------------------------------------------------- 锁锁模式死锁及防范两个及两个以上事务在执行过程中由于资源竞争造成相互等待。 防范：破坏死锁的条件； 表级锁命令： LOCK TABLE LOCK TABLE name [IN lockmode MODE] [NOWAIT] name:表名 lockmode:几种表级锁模式 nowait:如果没有nowait这个关键字，当无法获取锁时，会一直等待；加上nowait后，无法获取锁时，会立即退出并发出一个错误信息。 行级锁命令 select ... for {update|share} [OF table_name [...]] [nowait] 锁的查看查询系统视图pg_locks testdb2=# d pg_locks locktype | text | database | oid | relation | oid | page | integer | tuple | smallint | virtualxid | text | transactionid | xid | classid | oid | objid | oid | objsubid | smallint | virtualtransaction | text | pid | integer | mode | text | granted | boolean | fastpath | boolean | select locktype, relation::regclass as rel, virtualxid as vxid, transactionid as xid, virtualtransaction as vxid2, pid, mode, granted from pg_locks where pid = 12373; locktype | rel | vxid | xid | vxid2 | pid | mode | granted ------------+------+-------+-----+-------+-------+---------------------+--------- virtualxid | | 3/378 | | 3/378 | 12373 | ExclusiveLock | t relation | test | | | 3/378 | 12373 | AccessExclusiveLock | t (2 rows) select locktype, granted from pg_locks ; locktype | rel | vxid | xid | vxid2 | pid | mode | granted ------------+----------+-------+-----+-------+-------+---------------------+--------- relation | pg_locks | | | 5/118 | 14468 | AccessShareLock | t virtualxid | | 5/118 | | 5/118 | 14468 | ExclusiveLock | t virtualxid | | 3/378 | | 3/378 | 12373 | ExclusiveLock | t relation | test | | | 3/378 | 12373 | AccessExclusiveLock | t （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |