PostgreSQL学习记录-创建、授权、修改...

创建表

CREATE TABLE "public"."mall_sys_label" (

"id" int8 NOT NULL,
"label_code" char(100) NOT NULL ,
"label_name" char(64) NOT NULL,
"label_field" char(64) NULL,
"label_group_code" char(64) NOT NULL,
"label_group_name" char(64) NOT NULL,
"label_type" char(50) NOT NULL,
"label_order" int4 NULL,
"label_format" text NOT NULL,
"label_require" int4 NOT NULL,
"label_remark" text NULL,
"create_code" char(20),
"create_name" char(100),
"create_time" timestamp(6),
"changed_code" char(20),
"changed_name" char(100),
"changed_time" timestamp(6),
"del_flag" char(10) NOT NULL,
CONSTRAINT "mall_sys_label_pkey" PRIMARY KEY ("id")
)
WITH (OIDS=FALSE)
;

赋予所属者
ALTER TABLE "public"."mall_sys_label" OWNER TO "postgres";

注释标签

comment on column mall_sys_label_group.id is '标签组ID';
comment on column mall_sys_label_group.label_group_code is '标签组编码';
comment on column mall_sys_label_group.label_group_name is '标签组名称';
comment on column mall_sys_label_group.label_group_status is '标签组状态';
comment on column mall_sys_label_group.label_group_order is '标签组排序';
comment on column mall_sys_label_group.label_group_remark is '备注';
comment on column mall_sys_label_group.create_code is '创建人';
comment on column mall_sys_label_group.create_name is '创建人名称';
comment on column mall_sys_label_group.create_time is '创建时间';
comment on column mall_sys_label_group.changed_code is '最后修改人';
comment on column mall_sys_label_group.changed_name is '最后修改人名称';
comment on column mall_sys_label_group.changed_time is '最后修改时间';
comment on column mall_sys_label_group.del_flag is '删除标记 1 -已删除 0 -未删除';

删除表：
drop table student2;

删除表数据：

delete from public.student；

删除表、select、update 基本与mysql相同
查询
and, or, and & or, not ,like,in ,not in ,between，is null， is not null，别名 AS 与mysql 相同

视图：

在PostgreSQL中，视图(VIEW)是一个伪表。 它不是物理表，而是作为普通表选择查询。
视图也可以表示连接的表。 它可以包含表的所有行或来自一个或多个表的所选行。

视图便于用户执行以下操作：
它以自然和直观的方式构建数据，并使其易于查找。
它限制对数据的访问，使得用户只能看到有限的数据而不是完整的数据。
它归总来自各种表中的数据以生成报告。
语法：创建视图
CREATE [TEMP | TEMPORARY] VIEW view_name AS  
SELECT column1, column2.....  
FROM table_name  
WHERE [condition];
例：
CREATE VIEW current_employees AS  
SELECT NAME, ID, SALARY 
FROM EMPLOYEES;
例：
create view labelView as
SELECT id,label_code,ss.label_name,ss.label_field,ss.label_group_code,
ss.label_group_name FROM public.mall_sys_label ss
ORDER BY ss.create_time desc
删除视图
DROP VIEW view_name;

左连接：left OUTER JOIN
左表(EMPLOYEES)全部列出来，而右表(DEPARTMENT)没有匹配上的项全留为空值
右连接：RIGHT OUTER JOIN
右表(DEPARTMENT)全部列出来，而左表(EMPLOYEES)没有匹配上的项全留为空值。
全连接：FULL OUTER JOIN 
左表(EMPLOYEES)和右表(DEPARTMENT)没有匹配上的项全留为空值。
跨连接：CROSS JOIN 笛卡尔积；
PostgreSQL跨连接(CROSS JOIN)将第一个表的每一行与第二个表的每一行相匹配。 它也被称为笛卡儿积分。 
如果table1具有“x”列，而table2具有“y”列，则所得到的表将具有(x + y)列。


函数与触发器





索引
CREATE INDEX语句用于创建PostgreSQL索引。
语法
CREATE INDEX index_name ON table_name;
单列索引
CREATE INDEX index_name ON table_name (column_name);
多列索引
CREATE INDEX index_name ON table_name (column1_name, column2_name);
唯一索引（像主键，创建后所在列不能重复）
CREATE UNIQUE INDEX index_name on table_name (column_name);
删除索引
DROP INDEX index_name;
注意：
什么时候应该避免使用索引？
应该避免在小表上使用索引。
不要为具有频繁，大批量更新或插入操作的表创建索引。
索引不应用于包含大量NULL值的列。
不要在经常操作(修改)的列上创建索引。


时间和日期 相关函数
age()  减去参数
current date/time()  它指定当前日期和时间
date_part()  获取子字段（相当于提取）
exract()  获得子字段
isfinite  测试有限的日期，时间和间隔（非+/-无穷大）
justify   调整间隔


UNIONS
PostgreSQL UNION子句/运算符用于组合两个或多个SELECT语句的结果，而不返回任何重复的行。
要使用UNION，每个SELECT必须具有相同的列数，相同数量的列表达式，相同的数据类型，并且具有相同的顺序，但不一定要相同。
UNION ALL运算符用于组合两个SELECT语句(包括重复行)的结果。 适用于UNION的相同规则也适用于UNION ALL运算符。


ALTER TABLE
alter table 表名 rename to 新表名
PostgreSQL ALTER TABLE命令用于添加，删除或修改现有表中的列。您还可以使用ALTER TABLE命令在现有表上添加和删除各种约束。
ALTER TABLE table_name ADD column_name datatype;  添加新列
ALTER TABLE table_name DROP COLUMN column_name;   删除字段
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;    更改字段类型
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;   向列添加not null约束
ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);   添加唯一约束
ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);   添加主键约束
ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;   删除约束
ALTER TABLE table_name DROP CONSTRAINT MyPrimaryKey; 删除主键约束
PostgreSQL TRUNCATE TABLE table_name;   命令用于从现有表中删除完整的数据。

//修改表约束
alter table bu_clientfile_attribute alter COLUMN format drop not null;


事务性质：ACID
原子性、一致性、隔离性、持久性
事务控制：
BEGIN TRANSACTION：开始事务。
COMMIT：保存更改，或者您可以使用END TRANSACTION命令。
ROLLBACK：回滚更改。
事务控制命令仅用于DML命令INSERT，UPDATE和DELETE


锁


子查询
带INSERT语句的子查询
INSERT INTO COMPANY_BKP
     SELECT * FROM COMPANY
     WHERE ID IN (SELECT ID FROM COMPANY) ;


PostgreSQL自动递增
PostgreSQL具有数据类型smallserial，serial和bigserial; 这些不是真正的类型，而只是在创建唯一标识符列的标志以方便使用。 这些类似于一些其他数据库支持的AUTO_INCREMENT属性。
如果您希望某列具有唯一的约束或是主键，则必须使用其他数据类型进行指定。
类型名称serial用于创建整数列。 类型名称bigserial创建一个bigint类型的列。 如果您期望在表的使用期限内使用超过2^31个标识符，则应使用bigserial。 类型名称smallserial创建一个smallint列。


权限
GRANT 语法
GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }

privilege值可以是：SELECT，INSERT，UPDATE，DELETE，RULE，ALL。
object：要向其授予访问权限的对象的名称。 可能的对象是：表，视图，序列
PUBLIC：表示所有用户的简短形式。
GROUP group：授予权限的组。
username：授予权限的用户的名称。 PUBLIC是表示所有用户的简短形式。

REVOKE 撤销权限，语法同 grant


创建序列：
CREATE SEQUENCE bu_clientfile_attribute_seq
     INCREMENT 1
     MINVALUE 1
     MAXVALUE 9223372036854775807
     START 1
    CACHE 1;

表赋予序列

alter table bu_clientfile_attribute alter column attribute_id set default nextval('bu_clientfile_attribute_seq');