PostgreSQL学习笔记

PostgreSQL教程
PostgreSQL语法汇总

创建数据库：

create database testdb;

删除数据库：

drop database testdb;

查看数据库

\l

查看语法帮助：

\help <command_name>

进入数据库：

\c testdb

创建表格：

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   JOIN_DATE      DATE
);

查看所有表格：

\d

查看表格信息：

\d tablename

删除表格：

drop table department, company;

创建模式

create schema myschema;

删除一个空的模式（其中所有对象已经被删除)：

DROP SCHEMA myschema;

删除一个模式以及其中包含的所有对象：

DROP SCHEMA myschema CASCADE;

在表格中插入数据：

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');

在表格中插入多行数据：

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');

读取表中的所有数据：

SELECT * FROM table_name;

读取SALARY字段大于50000的数据：

SELECT * FROM COMPANY WHERE SALARY > 50000;

读取 SALARY 字段不为空的数据：

SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;

查询 COMPANY 表的记录总数

SELECT COUNT(*) AS "RECORDS" FROM COMPANY;

在 COMPANY 表中找出 NAME(名字) 字段中以 Pa 开头的的数据：

SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';

列出AGE(年龄)字段为 25 或 27 的数据：

SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

列出 AGE(年龄) 字段在 25 到 27 的数据：

SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

使用SQL子查询，子查询语句读取SALARY字段大于65000的数据，然后通过EXISTS运算符判断它是否返回行，如果有返回行则读取所有的AGE字段

SELECT AGE FROM COMPANY
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

读取 AGE 字段大于 25 且 SALARY 字段大于等于 65000 的所有记录：

SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

更新 COMPANY 表中 id 为 3 的 salary 字段值：

UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;

同时更新所有 salary 字段和 address 字段的值：

UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;

删除 ID 为 2 的数据：

DELETE FROM COMPANY WHERE ID = 2;

找出 SALARY 字段中在第二和第三个位置上有 00 的数据:

select * from company where salary::text like '_00%';

找出 SALARY 字段中以 2 开头的字符长度大于 3 的数据

select * from company where salary::text like '2%%';

只读取 4 条COMPANY数据:

select * from company limit 4;

从第三位开始提取 3 个COMPANY数据：

select * from company limit 3 offset 2;

对结果根据 AGE 字段值进行升序排列：

SELECT * FROM COMPANY ORDER BY AGE ASC;

对结果根据NAME字段值进行降序排列：

SELECT * FROM COMPANY ORDER BY NAME DESC;

根据 NAME 字段值进行分组，找出每个人的工资总额：

SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

根据 name 字段值进行分组，并且名称的计数大于 1 数据：注意HAVING字句必须放在GROUP BY子句后面，ORDER BY子句前面

SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;

数据表中选择name字段，且重复的只出现一次：

SELECT DISTINCT name FROM COMPANY;

PostgreSQL的约束
NOT NULL: 指示某列不能存储NULL值，NULL代表未知的数据
UNIQUE: 确保某列的值都是唯一的
PRIMARY key: NOT NULL和UNIQUE的结合。确保某列（或多个列的组合）有唯一标识，有助于更快找到表中的一个特定的记录
FOREIGN key: 保证一个表中的数据匹配另一个表中的值的参照完整性
CHECK: 保证列中的值符合指定的条件
EXCLUSION: 排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回false或空值

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00,
   SALARY1         REAL    CHECK(SALARY > 0)
);
CREATE TABLE DEPARTMENT1(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      references COMPANY6(ID)
);

删除约束：

ALTER TABLE table_name DROP CONSTRAINT some_name;

使用交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行

SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

内连接（INNER JOIN）根据连接为此结合两个表(table1和table2)的列值来创建一个新的结果表。查询会把table1的每一行与table2中的每一行进行比较，找到所有满足连接谓词的行的匹配对

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

左外连接（ LEFT OUTER JOIN ）首先执行一个内连接。然后，对于表 T1 中不满足表 T2 中连接条件的每一行，其中 T2 的列中有 null 值也会添加一个连接行。因此，连接的表在 T1 中每一行至少有一行

SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

右外连接（ RIGHT OUTER JOIN）首先执行内部连接。然后，对于表T2中不满足表T1中连接条件的每一行，其中T1列中的值为空也会添加一个连接行。这与左联接相反;对于T2中的每一行，结果表总是有一行

SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

外连接：首先，执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中

SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

UNION操作符合并两个或多个SELECT语句的结果，注意每个SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个SELECT语句的列的顺序必须相同。UNION会自动消除重复的行，如果允许重复使用UNION ALL

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
    ON COMPANY.ID = DEPARTMENT.EMP_ID;

使用别名

SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE  C.ID = D.EMP_ID;

创建单列索引：只基于表的一个列上创建的索引

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);

局部索引：在表的子集上构建的索引，子集由一个条件表达式上定义。索引只包含满足条件的行

CRAETE INDEX index_name on table_name (conditional_expression);

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

列出数据库中所有索引：

\di

删除索引：

DROP INDEX index_name;

在一张已存在的表上添加列：

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;

给表中某列添加NOT NULL约束：

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

给表中某列添加UNIQUE约束：

ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE (column1, column2...);

给表中添加CHECK约束：

ALTER TABLE table_name ADD CONSTRAINT MyCheckConstraint CHECK (CONDITION);

给表添加主键：

ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

删除约束：

ALTER TABLE table_name DROP CONSTRAINT MyConstraint;

mysql删除约束：

ALTER TABLE table_name DROP INDEX MyConstraint;

删除主键：

ALTER TABLE table_name DROP CONSTRAINT MyPrimaryKey;

mysql删除主键：

ALTER TABLE table_name DROP PRIMARY KEY;

删除表数据但不删除表结构：

TRUNCATE TABLE table_name;

从COMPANY表中选取及列创建视图：

CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM COMPANY;

然后就可以select * from company_view;

删除视图：

DROP VIEW view_name;

事务的四个标准属性ACID是什么？
原子性(Atomicity):事务作为一个整体被执行，包含在其中的对数据库的操作要么被全部执行，要么都不执行
一致性(Consistency):事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束
隔离性(Isolation):多个事务并发执行时，一个事务的执行不应影响其它事务的执行
持久性(Durability):已被提交的事务对数据库的修改应该永久保存在数据库中

开始一个事务：

BEGIN;
或
BEGIN TRANSACTION;

把事务调用的更改保存到数据库中的事务命令，即确认事务

COMMIT;
或
END TRANSACTION;

撤销尚未保存到数据库的事务命令，即回滚事务

ROLLBACK;

子查询的几个规则：
1.子查询必须用括号括起来
2.子查询在SELECT子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较
3.ORDER BY不能用在子查询中，虽然主查询可以使用ORDER BY。可以在子查询中使用GROUP BY，功能与ORDER BY相同
4.子查询返回多于一行，只能与多值运算符一起使用，如IN运算符
5.BETWEEN运算符不能与子查询一起使用，但是，BETWEEN可在子查询内使用

子查询语法：

SELECT column_name [, column_name] FROM table1 [, table2] WHERE column_name OPERATOR (SELECT column_name, [, column_name] FROM table1 [, table2] [WHERE])

SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 4500);

GRANT语法：

GRANT privilege [, ...] ON object [, ...] TO {PUBLIC | GROUP groupname |username};

privilege: 值可以为SELECT，INSERT，UPDATE，DELETE， RULE，ALL
object: 要授予访问权限的对象名称，可以有：table， view，sequence
PUBLIC: 表示所有用户
username: 要授予权限的用户名。PUBLIC是所有用户的简短形式