PostgreSQL 学习笔记

PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)

object–relational database management system (ORDBMS)

• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• 外键： 外键用于关联两个表。
• 索引： 使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

PostgreSQL 数据类型

数值类型

货币类型

字符类型

日期/时间类型

布尔类型

枚举类型

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';

几何类型

网络地址类型

位串类型

文本搜索类型

UUID 类型

UUID 被写成一个小写十六进制数字的序列，由分字符分成几组， 特别是一组8位数字+3组4位数字+一组12位数字，总共 32 个数字代表 128 位， 一个这种标准的 UUID 例子如下：a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

XML 类型

JSON 类型

数组类型

复合类型

范围类型

对象标识符类型

伪类型

创建数据库

CREATE DATABASE dbname;
-- createdb 是一个 SQL 命令 CREATE DATABASE 的封装。
-- createdb 命令语法格式如下：
createdb [option...] [dbname [description]]
-- 使用 \l 用于查看已经存在的数据库：
-- 使用 \c + 数据库名 来进入数据库：
\c dbname;
-- 连接数据库
psql -h localhost -p 5432 -U postgres dbname
-- 删除数据库
DROP DATABASE [ IF EXISTS ] name

创建表

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 一个或多个列 )
);

创建 2 张表

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

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

使用 \d 命令来查看表格是否创建成功，使用 \d tablename 查看表格信息。

-- 删除表
DROP TABLE table_name;

INSERT INTO 语句

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
-- 在使用 INSERT INTO 语句时，字段列必须和数据值数量相同，且顺序也要对应。
-- 如果我们向表中的所有字段插入值，则可以不需要指定字段，只需要指定插入的值即可：
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

SELECT 语句

SELECT 语句用于从数据库中选取数据。结果被存储在一个结果表中，称为结果集。

SELECT column1, column2,...columnN FROM table_name;
SELECT * FROM table_name;

运算符

运算符是一个保留关键字或字符，一般用在 WHERE 语句中，作为过滤条件。

常见的运算符有：算术运算符、比较运算符、逻辑运算符、按位运算符

-- 读取 SALARY 字段不等于 20000 的数据：
SELECT * FROM COMPANY WHERE SALARY != 20000;
SELECT * FROM COMPANY WHERE SALARY <> 20000;
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 6500;
SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 6500;
SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;

表达式

表达式是由一个或多个的值、运算符、PostgresSQL 函数组成的。PostgreSQL 表达式类似一个公式，可以将其应用在查询语句中，用来查找数据库中指定条件的结果集。

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];
SELECT COUNT(*) AS "RECORDS" FROM COMPANY;

WHERE 子句

SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';
SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

子查询

-- 子查询语句中读取 SALARY(薪资) 字段大于 65000 的数据，然后通过 EXISTS 运算符判断它是否返回行，如果有返回行则读取所有的 AGE(年龄) 字段。
SELECT AGE FROM COMPANY
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
-- 子查询语句中读取 SALARY(薪资) 字段大于 65000 的 AGE(年龄) 字段数据，然后用  >  运算符查询大于该 AGE(年龄)  字段数据：
SELECT * FROM COMPANY
        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

ORDER BY 语句

ORDER BY 用于对一列或者多列数据进行升序（ASC）或者降序（DESC）排列。

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
-- 您可以在 ORDER BY 中使用一列或者多列，但是必须保证要排序的列必须存在。
-- **ASC** 表示升序，**DESC** 表示降序。
SELECT * FROM COMPANY ORDER BY AGE ASC;
SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;
SELECT * FROM COMPANY ORDER BY NAME DESC;

GROUP BY 语句

GROUP BY 语句和 SELECT 语句一起使用，用来对相同的数据进行分组。GROUP BY 在一个 SELECT 语句中，放在 WHRER 子句的后面，ORDER BY 子句的前面。

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
-- GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

约束

以下是在 PostgreSQL 中常用的约束。

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

删除约束

删除约束必须知道约束名称，已经知道名称来删除约束很简单，如果不知道名称，则需要找到系统生成的名称，使用 \d 表名 可以找到这些信息。

通用语法如下：

ALTER TABLE table_name DROP CONSTRAINT some_name;

连接(JOIN)

JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

JOIN 有五种连接类型：

• CROSS JOIN ：交叉连接
• INNER JOIN：内连接
• LEFT OUTER JOIN：左外连接
• RIGHT OUTER JOIN：右外连接
• FULL OUTER JOIN：全外连接

-- 内连接（INNER JOIN）
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

参考资料