PostgreSQL入门(1)

StarMoon2023/12/02databasedatabase SQL

一、为什么使用 PostgreSQL

Postgres是一个对象关系数据库，与MySQL一样快，更接近SQL标准，并且在并发性方面表现出色。Postgres在避免数据损坏方面也有优势。Postgres还提供了更高级的数据类型，并允许创建自定义类型、操作符和索引类型。

当扩展性、可伸缩性和数据完整性对你来说最重要时，Postgres通常是最好的选择。

与MySQL对比：

MySQL与PostgreSQL哪个更好？ – 标点符 (biaodianfu.com)open in new window

二、Windows 安装教程：

Windows 10平台安装PostgreSQL 14.2详细教程 - 知乎 (zhihu.com)open in new window

三、PostgreSQL 语法

INFO

关系型数据库的语法大都一样，如果你会其他的sql如MySQL，那么你会很快上手PostgreSQL。

3.1.数据类型

数值类型

数值类型由 2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成。

下表列出了可用的数值类型。

名字	存储长度	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	常用的整数	-2147483648 到 +2147483647
bigint	8 字节	大范围整数	-9223372036854775808 到 +9223372036854775807
decimal	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
numeric	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
real	4 字节	可变精度，不精确	6 位十进制数字精度
double precision	8 字节	可变精度，不精确	15 位十进制数字精度
smallserial	2 字节	自增的小范围整数	1 到 32767
serial	4 字节	自增整数	1 到 2147483647
bigserial	8 字节	自增的大范围整数	1 到 9223372036854775807

字符类型

下表列出了 PostgreSQL 所支持的字符类型：

序号	名字 & 描述
1	character varying(n), varchar(n)变长，有长度限制
2	character(n), char(n)f定长,不足补空白
3	text变长，无长度限制

日期/时间类型

下表列出了 PostgreSQL 支持的日期和时间类型。

名字	存储空间	描述	最低值	最高值	分辨率
timestamp [ (p) ] [ without time zone ]	8 字节	日期和时间(无时区)	4713 BC	294276 AD	1 毫秒 / 14 位
timestamp [ (p) ] with time zone	8 字节	日期和时间，有时区	4713 BC	294276 AD	1 毫秒 / 14 位
date	4 字节	只用于日期	4713 BC	5874897 AD	1 天
time [ (p) ] [ without time zone ]	8 字节	只用于一日内时间	00:00:00	24:00:00	1 毫秒 / 14 位
time [ (p) ] with time zone	12 字节	只用于一日内时间，带时区	00:00:00+1459	24:00:00-1459	1 毫秒 / 14 位
interval [ fields ] [ (p) ]	12 字节	时间间隔	-178000000 年	178000000 年	1 毫秒 / 14 位

布尔类型

PostgreSQL 支持标准的 boolean 数据类型。

boolean 有"true"(真)或"false"(假)两个状态，第三种"unknown"(未知)状态，用 NULL 表示。

名称	存储格式	描述
boolean	1 字节	true/false

3.2.创建表格

PostgreSQL 使用 CREATE TABLE 语句来创建数据库表格。

语法

CREATE TABLE 语法格式如下：

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

CREATE TABLE 是一个关键词，用于告诉数据库系统将创建一个数据表。

表名字必需在同一模式中的其它表、序列、索引、视图或外部表名字中唯一。

CREATE TABLE 在当前数据库创建一个新的空白表，该表将由发出此命令的用户所拥有。

表格中的每个字段都会定义数据类型，如下：

实例

以下创建了一个表，表名为 customer表格，主键为 id，NOT NULL 表示字段不允许包含 NULL 值：

CREATE TABLE customer(
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    email VARCHAR(60) NOT NULL,
    company VARCHAR(60) NULL,
    street VARCHAR(50) NOT NULL,
    city VARCHAR(40) NOT NULL,
    state CHAR(2) NOT NULL DEFAULT 'PA',
    zip INTEGER NOT NULL,
    phone VARCHAR(20) NOT NULL,
    birth_date DATE NULL,
    sex CHAR(1) NOT NULL,
    date_entered TIMESTAMP NOT NULL,
    id SERIAL PRIMARY KEY
);

再来一个：

CREATE TABLE sales_item(
    item_id INTEGER REFERENCES item(id),
    sales_order_id INTEGER REFERENCES sales_order(id),
    quantity INTEGER NOT NULL,
    discount NUMERIC(3,2) NULL DEFAULT 0,
    taxable BOOLEAN NOT NULL DEFAULT FALSE,
    sales_tax_rate NUMERIC(5,2) NOT NULL DEFAULT 0,
    id SERIAL PRIMARY KEY
);

3.3. INSERT INTO 语句

PostgreSQL INSERT INTO 语句用于向表中插入新记录。

我们可以插入一行也可以同时插入多行。

语法

INSERT INTO 语句语法格式如下：

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

column1, column2,...columnN 为表中字段名。
value1, value2, value3,...valueN 为字段对应的值。

在使用 INSERT INTO 语句时，字段列必须和数据值数量相同，且顺序也要对应。

如果我们向表中的所有字段插入值，则可以不需要指定字段，只需要指定插入的值即可：

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

实例

在 customer 表中插入以下数据：

INSERT INTO customer (first_name, last_name, email, company, street, city, state, zip, phone, birth_date, sex, date_entered) VALUES 
('Matthew', 'Martinez', 'matthewmartinez@ge.com', 'GE', '602 Main Place', 'Fontana', 'CA', '92336', '117-997-7764', '1931-09-04', 'M', '2015-01-01 22:39:28'), 
('Melissa', 'Moore', 'melissamoore@aramark.com', 'Aramark', '463 Park Rd', 'Lakewood', 'NJ', '08701', '269-720-7259', '1967-08-27', 'M', '2017-10-20 21:59:29');

在 sales_item 表中插入以下数据：

INSERT INTO sales_item VALUES 
(24, 70, 2, 0.11, false, 0.0), 
(8, 37, 2, 0.16, false, 0.0), 
(24, 90, 2, 0.06, false, 0.0), 
(34, 83, 2, 0.13, false, 0.0);

3.4. ALTER TABLE 语句

在 PostgreSQL 中，ALTER TABLE 命令用于添加，修改，删除一张已经存在表的列。

另外你也可以用 ALTER TABLE 命令添加和删除约束。

语法

用 ALTER TABLE 在一张已存在的表上添加列的语法如下：

ALTER TABLE table_name ADD column_name datatype;

在一张已存在的表上 DROP COLUMN（删除列），语法如下：

ALTER TABLE table_name DROP COLUMN column_name;

修改表中某列的名称，语法如下：

ALTER TABLE table_name ALTER COLUMN old_name TO new_name;

修改表中某列的 DATA TYPE（数据类型），语法如下：

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

给表中某列添加 NOT NULL 约束，语法如下：

ALTER TABLE table_name ALTER column_name datatype SET NOT NULL;

实例

重命名表格

ALTER TABLE transaction_type RENAME TO transaction;

创建基于单列的索引

CREATE INDEX transaction_id ON transaction(name)

删除表的数据

TRUNCATE TABLE transaction

删除整张表

DROP TABLE transaction

3.5. SELECT 语句

PostgreSQL SELECT 语句用于从数据库中选取数据。

结果被存储在一个结果表中，称为结果集。

语法

SELECT 语句语法格式如下：

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

column1, column2,...columnN 为表中字段名。
table_name 为表名。

如果我们想读取表中的所有数据可以使用以下 SQL 语句：

SELECT * FROM table_name;

运算符是一种告诉编译器执行特定的数学或逻辑操作的符号。

3.6.运算符

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

常见的运算符有：

算术运算符
比较运算符
逻辑运算符

算术运算符

假设变量 a 为 2，变量 b 为 3，则：

运算符	描述	实例
+	加	a + b 结果为 5
-	减	a - b 结果为 -1
*	乘	a * b 结果为 6
/	除	b / a 结果为 1
%	模（取余）	b % a 结果为 1

比较运算符

假设变量 a 为 10，变量 b 为 20，则：

运算符	描述	实例
=	等于	(a = b) 为 false。
!=	不等于	(a != b) 为 true。
<>	不等于	(a <> b) 为 true。
>	大于	(a > b) 为 false。
<	小于	(a < b) 为 true。
>=	大于等于	(a >= b) 为 false。
<=	小于等于	(a <= b) 为 true。

逻辑运算符

PostgreSQL 逻辑运算符有以下几种：

序号	运算符 & 描述
1	AND逻辑与运算符。如果两个操作数都非零，则条件为真。PostgresSQL 中的 WHERE 语句可以用 AND 包含多个过滤条件。

2	NOT逻辑非运算符。用来逆转操作数的逻辑状态。如果条件为真则逻辑非运算符将使其为假。PostgresSQL 有 NOT EXISTS, NOT BETWEEN, NOT IN 等运算符。
3	OR逻辑或运算符。如果两个操作数中有任意一个非零，则条件为真。PostgresSQL 中的 WHERE 语句可以用 OR 包含多个过滤条件。

AND 实例

SELECT time_order_taken
FROM sales_order
WHERE time_order_taken > '2018-12-01' AND time_order_taken < '2018-12-31';
 
-- You can use BETWEEN to get the same results
 
SELECT time_order_taken
FROM sales_order
WHERE time_order_taken BETWEEN '2018-12-01' AND '2018-12-31';

PostgreSQL入门(1)

目录列表