PostgreSQL学习笔记(上)
1、基础知识
SQL语言包含4个部分：
数据定义语言(DDL)：DROP、CREATE、ALTER等语句。
数据操作语言(DML)：INSERT(插入)、UPDATE(修改)、DELETE(删除)语句。
数据查询语言(DQL)：SELECT语句。
数据控制语言(DCL)：GRANT、REVOKE、COMMIT、ROLLBACK等语句。
2、数据库的基本操作
2.1 创建数据库
# 创建数据库
create database sample_db;
2.2 修改数据库
# 语法
ALTER DATABASE name [ [WITH] option [...] ]
# 修改拥有者
ALTER DATABASE sample_db OWNER TO pg_database_owner;
# 修改名字
ALTER DATABASE sample_db RENAME TO sample_db_alter;
# 修改数据库的连接最大值为10
ALTER DATABASE sample_db WITH CONNECTION LIMIT=10;
2.3 删除数据库
# 语法
DROP DATABASE database_name[,...n];
# 删除数据库
DROP DATABASE sample_db;
并不是所有数据库在任何时候都可以被删除，只有处于正常状态下的数据库，才能使用DROP语句删除。当数据库
处于以下状态时不能被删除：数据库正在使用；数据库正在恢复；数据库包含用于复制的对象。
2.4 关闭数据库
先找到指定数据库的pid：
SELECT datname,pid FROM pg_stat_activity;
关闭数据库连接：
SELECT pg_terminate_backend(14788);
其实可以一步完成：
SELECT CAST(pg_terminate_backend(pid) AS VARCHAR(10)) FROM pg_stat_activity WHERE datname='sample_db';
3、数据表的基本操作
3.1 创建数据表
# 语法
CREATE TABLE <表名>
(
字段名1 数据类型 [列级别约束条件] [默认值],
字段名2 数据类型 [列级别约束条件] [默认值],
...
[表级别约束条件]
);
使用CREATE TABLE创建表时，必须指定以下信息：
-
要创建的表的名称不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。
-
数据表中如果创建多个列，每一个列(字段)的名称和数据类型都要用逗号隔开。
# 创建表
CREATE TABLE tb_emp1
(
id INT,
name VARCHAR(25),
deptId INT,
salary FLOAT
);
3.1.1 使用主键约束
1、单字段主键
定义列的同时使用主键：
# 语法
字段名 数据类型 PRIMARY KEY
CREATE TABLE tb_emp2
(
id INT PRIMARY KEY,
name VARCHAR(25),
deptId INT,
salary FLOAT
);
在定义完所有列之后使用主键：
# 语法
[CONSTRAINT<约束名>] PRIMARY KEY [字段名]
CREATE TABLE tb_emp3
(
id INT,
name VARCHAR(25),
deptId INT,
salary FLOAT,
PRIMARY KEY(id)
);
2、多字段联合主键
# 语法
PRIMARY KEY[字段1,字段2,···,字段n]
CREATE TABLE tb_emp4
(
name VARCHAR(25),
deptId INT,
salary FLOAT,
PRIMARY KEY(name,deptId)
);
3.1.2 使用外键约束
# 语法
[CONSTRAINT <外键名>] FOREIN KEY 字段名1 [,字段名2,···]
REFERENCES <主表名> 主键列1 [,主键列2,···】
CREATE TABLE tb_dept1
(
id INT PRIMARY KEY,
name VARCHAR(22) NOT NULL,
location VARCHAR(50)
);
CREATE TABLE tb_emp5
(
id INT PRIMARY KEY,
name VARCHAR(25),
deptId INT,
salary FLOAT,
CONSTRAINT fk_emp_deptl FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);
3.1.3 使用非空约束
# 语法
字段名 数据类型 NOT NULL
CREATE TABLE tb_emp6
(
id INT PRIMARY KEY,
name VARCHAR(25) NOT NULL,
deptId INT,
salary FLOAT,
CONSTRAINT fk_emp_dept2 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);
3.1.4 使用唯一性约束
1、在定义完列之后使用
# 语法
字段名 数据类型 UNIQUE
CREATE TABLE tb_dept2
(
id INT PRIMARY KEY,
name VARCHAR(22) UNIQUE,
location VARCHAR(50)
);
2、在定义完所有列之后指定唯一约束
# 语法
[CONSTRAINT<约束名>]UNIQUE(<字段名>)
CREATE TABLE tb_dept3
(
id INT PRIMARY KEY,
name VARCHAR(22),
location VARCHAR(50),
CONSTRAINT STH UNIQUE(name)
);
UNIQUE和PRIMARY KEY的区别：一个表中可以有多个字段声明为UNIQUE，但只能有一个PRIMARY KEY 声明；
声明为PRIMAY KEY 的列不允许有空值，但是声明为UNIQUE 的字段允许空值(NULL)的存在。
3.1.5 使用默认约束
# 语法
字段名 数据类型 DEFAULT 默认值
CREATE TABLE tb_emp7
(
id INT PRIMARY KEY,
name VARCHAR(25)NOT NULL,
deptId INT DEFAULT 1111,
salary FLOAT,
CONSTRAINT fk_emp_dept3 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);
3.2 修改表数据
3.2.1 修改表名
# 语法
ALTER TABLE<旧表名>RENAME TO<新表名>;
ALTER TABLE tb_dept3 RENAME TO tbdepartment3;
3.2.2 修改字段的数据类型
# 语法
ALTER TABLE<表名> ALTER COLUMN <字段名> TYPE <数据类型>
ALTER TABLE tb_dept1 ALTER COLUMN name TYPE VARCHAR(30);
3.2.3 修改字段名
# 语法
ALTER TABLE <表名> RENAME <旧字段名> TO <新字段名> <新数据类型>;
ALTER TABLE tb_dept1 RENAME location TO loc;
3.2.4 添加字段
# 语法
ALTER TABLE <表名> ADD COLUMN <新字段名> <数据类型>
1、添加无完整约束条件的字段
ALTER TABLE tb_dept1 ADD COLUMN managerId INT;
2、添加有完整约束条件的字段
ALTER TABLE tb_dept1 ADD COLUMN column1 VARCHAR(12) NOT NULL;
3.2.5 删除字段
# 语法
ALTER TABLE<表名>DROP<字段名>;
ALTER TABLE tb_dept1 DROP column1;
3.2.6 删除表的外键约束
# 语法
ALTER TABLE<表名> DROP CONSTRAINT <外键约束名>
CREATE TABLE tb_emp9
(
id INT PRIMARY KEY,
name VARCHAR(25),
deptId INT,
salary FLOAT,
CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);
ALTER TABLE tb_emp9 DROP CONSTRAINT fk_emp_dept;
3.2.7 修改约束
ALTER TABLE tb_emp9 ALTER COLUMN name SET NOT NULL;
4.3 删除数据表
4.3.1 删除没有被关联的表
# 语法
DROP TABLE[IF EXISTS]表1,表2,表n;
DROP TABLE IF EXISTS tb_dept2;
4.3.2 删除被其它表关联的主表
CREATE TABLE tb_dept2
(
id INT PRIMARY KEY,
name VARCHAR(22),
location VARCHAR(50)
);
CREATE TABLE tb_emp
(
id INT PRIMARY KEY,
name VARCHAR(25),
deptId INT,
salary FLOAT,
CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES tb_dept2(id)
);
# 有关联无法删除
DROP TABLE IF EXISTS tb_dept2;
ALTER TABLE tb_emp DROP CONSTRAINT fk_emp_dept;
DROP TABLE tb_dept2;
4、数据类型和运算符
4.1 数据类型介绍
4.1.1 整数类型
PostgreSQL 主要提供的整数类型有 SMALLINT、INT(INTEGER)和BIGINT。
| 类型名称 | 说明 | 存储需求 | 存储范围 |
|---|---|---|---|
SMALLINT | 小范围的整数 | 2个字节 | -2^15~2^15-1 |
INT(INTEGER) | 普通大小的整数 | 4个字节 | -2^31~2^31-1 |
BIGINT | 大整数 | 8个字节 | -2^63~2^63-1 |
CREATE TABLE tmp1(X SMALLINT,y INT,z BIGINT );
4.1.2 浮点数类型
PostgreSQL 中使用浮点数来表示小数。浮点类型有两种：REAL 和 DOUBLE PRECISION。
| 类型名称 | 说明 | 存储需求 | 存储范围 | 精度 |
|---|---|---|---|---|
REAL | 6位十进制数字精度 | 4 个字节 | 1E-37~1E+37 | 至少6位 |
DOUBLE PRECISION | 15 位十进制数字精度 | 8个字节 | 1E-307~1E+308 | 至少15位 |
PostgreSQL 也支持SQL 标准表示法，float 和 float(p)用于声明非精确的数值类型。其中，p声明以二进制位
表示的最低可接受精度。在选取 REAL 类型的时候，PostgreSQL 接受 float(1)到float(24)，在选取
DOUBLE PRECISION 的时候，接受float(25)到float(53)。在允许范围之外的p值将导致一个错误,没有声明精
度的 float 将被当作 DOUBLE PRECISION。
CREATE TABLE tmp2(x FLOAT(5),y REAL,z DOUBLE PRECISION);
在 PostgreSQL中，浮点类型有几个特殊值。其中，Infinity表示正无穷大，-Infinity表示负无穷大，NaN
表示不是一个数字。
4.1.3 任意精度类型
在PostgreSQL中，NUMERIC表示数值是任意精度的类型，使用 NUMERIC (M,N)来表示。其中，M称为精度，表
示总的位数；N称为标度，表示小数的位数。例如563.186中，精度为6，标度为3。
NUMERIC的有效的取值范围由M和N的值决定。如果改变M而固定N，则其取值范围将随M的变大而变大。另外如
果用户数据的精度超出指定精度，则会四舍五入进行处理。
CREATE TABLE tmp3(x NUMERIC(5,1),y NUMERIC(5,2));
INSERT INTO tmp3 VALUES(9.12,9.15);
SELECT * FROM tmp3;
x |y |
---+----+
9.1|9.15|
4.1.4 日期与时间类型
PostgreSQL 中有多种表示日期的数据类型,主要有：TIME、DATE、TIMESTAMP 和INTERVAL。每一个类型都有
合法的取值范围，当指定确实不合法的值时，系统将“零”值插入到数据库中。
| 类型名称 | 含义 | 存储需求 |
|---|---|---|
TIME | 只用于一日内时间 | 8字节 |
DATE | 只用于日期 | 4字节 |
TIMESTAMP | 日期和时间 | 8字节 |
另外，对于TIME和TIMESTAMP类型，默认情况下为without time zone(不带时区)，如果
需要，可以设置为带时区(with time zone)。
1、TIME
TIME 类型用于只需要时间信息的值，在存储时需要8个字节。格式为：HH:MM:SS。HH 表示小时；MM 表示分
钟；SS表示秒。TIME类型的取值范围为00:00:00~24:00:00。
CREATE TABLE tmp4(t TIME);
INSERT INTO tmp4 values('10:05:05'),('23:23');
SELECT * FROM tmp4;
t |
--------+
10:05:05|
23:23:00|
INSERT INTO tmp4 values('101112');
SELECT*FROM tmp4;
t |
--------+
10:05:05|
23:23:00|
10:11:12|
ALTER TABLE tmp4 ALTER COLUMN t TYPE time without time zone;
DELETE FROM tmp4;
INSERT INTO tmp4 values(CURRENT_TIME),(NOW());
SELECT * FROM tmp4;
t |
--------+
15:39:11|
15:39:11|
2、Date
DATE 类型用在仅需要日期值时，没有时间部分，在存储时需要4 个字节，日期格式为YYYY-MM-DD。其中，YYYY
表示年；MM 表示月；DD表示日。在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插
入，只要符合DATE 的日期格式即可，具体如下：
-
以
YYYY-MM-DD或者YYYYMMDD字符串格式表示的日期。例如输入2012-12-31或者20121231，插入数据库的日期都为
2012-12-31。 -
以
YY-MM-DD或者YYMMDD字符串格式表示的日期，在这里，YY表示两位的年值。包含两位年值的日期会令人模糊，因为不知道世纪。PostgreSQL 使用以下规则解释两位年值：
00~69范围的年值转换为2000~2069；70~99范围的年值转换为1970~1999。例如输入12-12-31，插入数据库的日期为2012-12-31；输入981231，插入数据的日期为1998-12-31。 -
使用 CURRENT DATE或者 NOWO插入当前系统日期。
CREATE TABLE tmp5(d DATE);
INSERT INTO tmp5 values('1998-08-08'),('19980808'),('20101010');
SELECT * FROM tmp5;
d |
----------+
1998-08-08|
1998-08-08|
2010-10-10|
DELETE FROM tmp5;
INSERT INTO tmp5 values('99-09-09'),('990909'),('000101'),('121212');
SELECT * FROM tmp5;
d |
----------+
1999-09-09|
1999-09-09|
2000-01-01|
2012-12-12|
DELETE FROM tmp5;
INSERT INTO tmp5 values(NOW());
SELECT * FROM tmp5;
d |
----------+
2022-09-11|
NOWO函数返回日期和时间值，在保存到数据库时，只保留了其日期部分。
3、TIMESTAMP
TIMESTAMP的日期格式为YYYY-MM-DD HH:MM:SS。在存储时需要8个字节，因此在插入数据时，要保证在合法的
取值范围内。
CREATE TABLE tmp7(ts TIMESTAMP);
INSERT INTO tmp7 values('1996-02-02 02:02:02'),(NOW());
SELECT * FROM tmp7;
ts |
-----------------------+
1996-02-02 02:02:02.000|
2022-09-11 15:53:41.421|
4、创建带时区的日期和时间类型
CREATE TABLE tmp7h(th TIME with time zone);
INSERT INTO tmp7h values('10:05:05 PST'),('10:05:05');
SELECT * FROM tmp7h;
th |
--------------+
02:05:05 +0800|
10:05:05 +0800|
4.1.5 字符串类型
字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其他数据，比如图片和声音的二进制
数据。字符串可以进行区分或者不区分大小写的串比较，另外，还可以进行模式匹配查找。PostgreSQL中字符串
类型指CHAR、VARCHAR和TEXT。
| 类型名称 | 说明 |
|---|---|
CHAR(n)/CHARACTER(n) | 固定长度非二进制字符串，不足补空白 |
VARCHAR(n)/CHARACTER VARYING(n) | 变长非二进制字符串，有长度限制 |
TEXT | 变长非二进制字符串，无长度限制 |
1、CHARACTER(n)和 CHARACTER VARYING(n)
其中，n是一个正整数。CHARACTER(n)和CHARACTER VARYING(n)都可以存储最多n个字符的字符串。试图存储
更长的字符串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字符串将被截断
为最大长度。如果要存储的字符串比声明的长度短，类型为CHARACTER的数值将会用空白填满；而类型为
CHARACTER VARYING的数值将只是存储较短的字符串。
CREATE TABLE tmp8(
ch CHARACTER(4),vch CHARACTER VARYING(4)
);
INSERT INTO tmp8 VALUES('ab','ab'),('abcd','abcd'),('ab','ab');
SELECT concat('(',ch,')'),concat('(',vch,')') FROM tmp8;
concat|concat|
------+------+
(ab )|(ab) |
(abcd)|(abcd)|
(ab )|(ab) |
CHARACTER类型中填充的空白是无意义的。例如在比较两个CHARACTER值时，填充的空白都会被忽略，在转换
成其他字符串类型的时候，CHARACTER 值里面的空白会被删除。请注意，在CHARACTER VARYING和TEXT数值
里，结尾的空白是有意义的。
# 会报错,长度超出限制
INSERT INTO tmp8 VALUES('abcde','abcde');
2、TEXT 类型
在PostgreSQL中，TEXT 类型可以存储任何长度的字符串。尽管TEXT 不是SQL 标准类型，但是其他许多SQL 数据
库系统中也包括它。
CREATE TABLE tmp9(te TEXT);
INSERT INTO tmp9 VALUES('ab'),('abcd'),('ab');
SELECT concat('(',te,')') FROM tmp9;
concat|
------+
(ab) |
(abcd)|
(ab) |
4.1.6 二进制类型
PostgreSQL 支持两类字符型数据：文本字符串和二进制字符串，前面讲解了存储文本的字符串类型，这一节将讲
解 PostgreSQL 中存储二进制数据的数据类型。PostgreSQL 提供了 BYTEA 类型，用于存储二进制字符串。BYTEA
类型存储空间为4字节加上实际的二进制字符串。
CREATE TABLE tmp10(b BYTEA);
INSERT INTO tmp10 VALUES(E'\\000');
SELECT * FROM tmp10;
b|
-+
|
4.1.7 布尔类型
PostgreSQL 提供了 BOOLEAN 布尔数据类型。BOOLEAN用一个字节来存储，提供了 TRUE(真)和FALSE(假)两个
值。另外，用户可以使用其他有效文本值替代TRUE和FALSE。替代 TRUE的文本值为:‘t’、‘true’、‘y’、‘yes’和1’；
替代FALSE的文本值为:'f、‘false’、‘n’、‘no’和’0’。
CREATE TABLE tmp11(b BOOLEAN);
INSERT INTO tmp11 VALUES(TRUE),
(FALSE),
('y'),
('no'),
('0');
SELECT * FROM tmp11;
b |
-----+
true |
false|
true |
false|
false|
4.1.8 数组类型
PostgreSQL 允许将字段定义成定长或变长的一维或多维数组。数组类型可以是任何基本类型或用户定义类型。
1、声明数组
在PostgreSQL中，一个数组类型是通过在数组元素类型名后面附加中括号来命名的。
numb INT[];
xuehao TEXT[][];
zuoye TEXT[4][4];
其中，numb字段为一维INT数组；xuehao字段为二维TEXT数组；zuoye字段为二维TEXT数组，并且声明了数组
的长度。不过，目前 PostgreSQL 并不强制数组的长度，所以声明长度和不声明长度是一样的。
另外对于一维数组，也可以使用SQL 标准声明，SQL 语句如下：
PAY_BY_QUARTER INT ARRAY[5];
此种声明方式，仍然不强制数组的长度。
2、插入数组数值
插入数组元素时，用大括号把数组元素括起来并且用逗号将它们分开。
CREATE TABLE tmp12(bt int[]);
INSERT INTO tmp12 VALUES('{{1,1,1},{2,2,2},{3,3,3}}');
SELECT * FROM tmp12;
bt |
-------------------------+
{{1,1,1},{2,2,2},{3,3,3}}|
4.2 如何选择数据类型
PostgreSQL 提供了大量的数据类型，为了优化存储，提高数据库性能，在任何情况下均应使用最精确的类型。即
在所有可以表示该列值的类型中，该类型使用的存储空间最少。
4.2.1 整数和浮点数
如果不需要小数部分，则使用整数来保存数据；如果需要表示小数，则使用浮点数类型。对于浮点数据列，存入的
数值会被该列定义的小数位进行四舍五入。例如，如果列的值的范围为1~99999，若使用整数，则INT是最好的类
型；若需要存储小数，则使用浮点数类型。
4.2.2 日期与时间类型
PostgreSQL 对于不同种类的日期和时间有很多的数据类型，比如 TIME 和DATE。如果只记录时间，则使用 TIME
类型即可；如果只记录日期，只需使用DATE 类型。如果同时需要记录日期和时间，则可以使用 TIMESTAMP 类
型。默认情况下，当插入一条记录但并没有指定 TIMESTAMP 这个列值时，PostgreSQL 会把TIMESTAMP 列设为
当前的时间，因此当需要插入记录时、同时插入当前时间，使用 TIMESTAMP是方便的。
4.2.3 CHAR与VARCHAR 之间的特点与选择
CHAR 和VARCHAR的区别：CHAR是固定长度字符，VARCHAR是可变长度字符。对于插入数据长度不够时，CHAR
会自动填充插入数据的尾部空格，VARCHAR则不会。
由于CHAR是固定长度，所以它的处理速度比 VARCHAR 要快，但缺点就是浪费存储空间。所以对于存储量不大，
但在速度上有要求的数据可以使用CHAR 类型，反之可以使用 VARCHAR类型来实现。
4.3 常见运算符
运算符是用来告诉 PostgreSQL 执行特定算术或逻辑操作的符号。PostgreSQL的内部运算符很丰富，主要有四大
类，具体如下。
4.3.1 算术运算符
用于各类数值运算。包括加(+)、减(-)、乘(*)、除(/)、求余(或称模运算，%)。
CREATE TABLE tmp14(num INT);
INSERT INTO tmp14 VALUES(64);
SELECT num, num+10, num-10,num+5-3,num+36.5 FROM tmp14;
num|?column?|?column?|?column?|?column?|
---+--------+--------+--------+--------+
64| 74| 54| 66| 100.5|
SELECT num,num *2,num/2,num/3,num%3 FROM tmp14;
num|?column?|?column?|?column?|?column?|
---+--------+--------+--------+--------+
64| 128| 32| 21| 1|
# 报错
SELECT num, num /0, num %0 FROM tmp14;
4.3.2 比较运算符
用于比较运算。包括大于(>)、小于(<)、等于(=)、大于等于(>=)、小于等于(<=)、不等于(<> 或者!=)、以及
IN、BETWEEN AND、GREATEST、LEAST、LIKE等。
1、等于运算符=
SELECT 1=0,'2'=2,2=2,'b'='b',(1+3)=(2+1),NULL=NULL;
?column?|?column?|?column?|?column?|?column?|?column?|
--------+--------+--------+--------+--------+--------+
false |true |true |true |false | |
等于运算符在进行数值比较时有如下规则：
-
若有一个或两个参数为 NULL，则比较运算的结果为空。
-
若同一个比较运算中的两个参数都是字符串，则按照字符串进行比较。
-
若两个参数均为整数，则按照整数进行比较。
-
若一个字符串和数字进行相等判断，则 PostgreSQL可以自动将字符串转换为数字。
2、不等于运算符<>或者!=
SELECT 'good'<>'god',1<>2,4!=4,5.5!=5,(1+3)!=(2+1),NULL<>NULL;
?column?|?column?|?column?|?column?|?column?|?column?|
--------+--------+--------+--------+--------+--------+
true |true |false |true |true | |
3、小于等于运算符<=
SELECT 'good'<='god',1<=2,4<=4,5.5<=5,(1+3)<=(2+1),NULL<=NULL;
?column?|?column?|?column?|?column?|?column?|?column?|
--------+--------+--------+--------+--------+--------+
false |true |true |false |false | |
4、小于运算符<
SELECT 'good'<'god',1<2,4<4,5.5<5,(1+3)<(2+1),NULL<NULL;
?column?|?column?|?column?|?column?|?column?|?column?|
--------+--------+--------+--------+--------+--------+
false |true |false |false |false | |
5、大于等于运算符>=
SELECT 'good'>='god',1>=2,4>=4,5.5>=5,(1+3)>=(2+1),NULL>=NULL;
?column?|?column?|?column?|?column?|?column?|?column?|
--------+--------+--------+--------+--------+--------+
true |false |true |true |true | |
6、大于运算符>
SELECT 'good'>'god',1>2,4>4,5.5>5,(1+3)>(2+1),NULL>NULL;
?column?|?column?|?column?|?column?|?column?|?column?|
--------+--------+--------+--------+--------+--------+
true |false |false |true |true | |
7、BETWEEN AND运算符
BETWEEN AND：判断一个值是否落在两个值之间。
SELECT 4 BETWEEN 2 AND 5,4 BETWEEN 4 AND 6,12 BETWEEN 9 AND 10;
?column?|?column?|?column?|
--------+--------+--------+
true |true |false |
SELECT 'x' BETWEEN 'f' AND 'g','b' BETWEEN 'a' AND 'c';
?column?|?column?|
--------+--------+
false |true |
8、Last运算符
LEAST：在有两个或多个参数时，返回最小值。
SELECT least(2,0),least(20.0,3.0,100.5),least('a','c','b'),least(10,NULL);
least|least|least|least|
-----+-----+-----+-----+
0| 3.0|a | 10|
9、GREATEST运算符
GREATEST：当有两个或多个参数时，返回最大值。
SELECT greatest(2,0),greatest(20.0,3.0,100.5),greatest('a','c','b'),
greatest(10,NULL);
greatest|greatest|greatest|greatest|
--------+--------+--------+--------+
2| 100.5|c | 10|
10、IN、NOT IN运算符
IN：判断一个值是否是IN列表中的任意一个值。
SELECT 2 IN(1,2,5,8),3 IN(1,2,5,9);
?column?|?column?|
--------+--------+
true |false |
SELECT 2 NOT IN(1,2,5,8),3 NOT IN(1,2,5,9);
?column?|?column?|
--------+--------+
false |true |
SELECT NULL IN(1,3,5),10 IN(1,3,NULL);
?column?|?column?|
--------+--------+
| |
SELECT NULL IN(1,3,5),10 IN(1,3,10,NULL);
?column?|?column?|
--------+--------+
|true |
11、LIKE
LIKE：通配符匹配。
-
%，匹配任何数目的字符，甚至包括零字符。 -
_：只能匹配一个字符。
SELECT 'stud' LIKE 'stud','stud' LIKE 'stu','stud' LIKE '8d','stud' LIKE 't_','s' LIKE NULL;
?column?|?column?|?column?|?column?|?column?|
--------+--------+--------+--------+--------+
true |false |false |false | |
4.3.3 逻辑运算符
逻辑运算符的求值所得结果均为t(TRUE)、f(FALSE)，这类运算符有逻辑非(NOT)、逻辑与(AND)、逻辑或(OR)。
1、NOT
SELECT NOT '1', NOT 'y', NOT '0', NOT NULL, NOT 'n';
?column?|?column?|?column?|?column?|?column?|
--------+--------+--------+--------+--------+
false |false |true | |true |
2、AND
SELECT '1' AND 'y','1' and '0','1' AND NULL,'0' AND NULL;
?column?|?column?|?column?|?column?|
--------+--------+--------+--------+
true |false | |false |
3、OR
SELECT '1' OR 't' OR '0','1' OR 'y','1' OR NULL,'0' OR NULL,NULL OR NULL;
?column?|?column?|?column?|?column?|?column?|
--------+--------+--------+--------+--------+
true |true |true | | |
4.3.4 位运算符
参与运算的操作数,按二进制位进行运算。包括位与(&)、位或(|)、位非(~)、位异或(^)、左移(<<)、右移(>>)6
种。还有#位异或，||连接。
-
连接运算：
10001l|011结果为10001011。 -
位与运算：
10001&01101结果为00001。 -
位或运算：
10001|01101结果为11101。 -
位异或运算：
10001#01101结果为11100。 -
位非运算：~
10001结果为01110。 -
位左移运算：
10001<<3结果为01000。 -
位右移运算：
10001>>2结果为00100。
SELECT '1000100'||'1010110';
?column? |
--------------+
10001001010110|
4.3.5 运算符的优先级
| 优先级 | 运算符 |
|---|---|
| 最低 | =(赋值运算)，:= |
| 中间 | |、OR |
| 中间 | AND |
| 中间 | NOT |
| 中间 | BETWEEN、CASE、WHEN、THEN、ELSE |
| 中间 | =(比较运算)、>=、>、<=、<、<>、!=、IS、LIKE、IN |
| 中间 | -、+ |
| 中间 | *、/(DIV)、%(MOD) |
| 中间 | -(负号) |
| 最高 | ! |
5、PostgreSQL函数
5.1 数学函数
5.1.1 绝对值函数ABS(x)和返回圆周率的函数PI()
SELECT ABS(2),ABS(-3.3),ABS(-33);
abs|abs|abs|
---+---+---+
2|3.3| 33|
SELECT pi();
pi |
-----------------+
3.141592653589793|
5.1.2 平方根函数SQRT(x)和求余函数MOD(x,y)
SELECT SQRT(9),SQRT(40);
sqrt|sqrt |
----+-----------------+
3.0|6.324555320336759|
SELECT MOD(31,8),MOD(234,10),MOD(45.5,6);
mod|mod|mod|
---+---+---+
7| 4|3.5|
5.1.3 获取整数的函数 CEIL(x)、CEILING(x)和 FLOOR(x)
SELECT CEIL(-3.35),CEILING(3.35);
ceil|ceiling|
----+-------+
-3| 4|
SELECT FLOOR(-3.35),FLOOR(3.35);
floor|floor|
-----+-----+
-4| 3|
CEIL(X)和 CEILING(x)意义相同，返回不小于x的最小整数值，返回值转化为一个 BIGINT。
5.1.4 四舍五入函数 ROUND(x)和 ROUND(x,y)
SELECT ROUND(-1.14),ROUND(-1.67),ROUND(1.14),ROUND(1.66);
round|round|round|round|
-----+-----+-----+-----+
-1| -2| 1| 2|
SELECT ROUND(1.38,1),ROUND(1.38,0),ROUND(232.38,-1),ROUND(232.38,-2);
round|round|round|round|
-----+-----+-----+-----+
1.4| 1| 230| 200|
ROUND(x,y)返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。
5.1.5 符号函数 SIGN(x)
SELECT SIGN(-21),SIGN(0),SIGN(21);
sign|sign|sign|
----+----+----+
-1.0| 0.0| 1.0|
SIGN(x)返回参数的符号，x的值为负、零或正时，返回结果依次为-1、0或1。
5.1.6 幂运算函数POW(x,y)、 POWER(x,y)和EXP(x)
SELECT POW(2,2),POWER(2,2),POW(2,-2), POWER(2,-2);
pow|power|pow |power|
---+-----+----+-----+
4.0| 4.0|0.25| 0.25|
SELECT EXP(3),EXP(-3),EXP(0);
exp |exp |exp|
------------------+--------------------+---+
20.085536923187668|0.049787068367863944|1.0|
POW(x,y)或者 POWER(x,y)函数返回x的y次乘方的结果值。
EXP(x)返回e的x乘方后的值。
5.1.7 对数运算函数LOG(x)
SELECT LOG(3);
log |
-------------------+
0.47712125471966244|
LOG(x)返回x的自然对数，x相对于基数e的对数。对数定义域不能为负数,否则将会弹出错误信息。
5.1.8 角度与弧度相互转换的函数 RADIANS(x)和 DEGREES(x)
SELECT RADIANS(90),RADIANS(180);
radians |radians |
------------------+-----------------+
1.5707963267948966|3.141592653589793|
SELECT DEGREES(PI()),DEGREES(PI()/ 2);
degrees|degrees|
-------+-------+
180.0| 90.0|
RADIANS(x)将参数x由角度转化为弧度。
DEGREES(x)将参数x由弧度转化为角度。
5.1.9 正弦函数SIN(x)和反正弦函数 ASIN(x)
SELECT SIN(1),ROUND(SIN(PI()));
sin |round|
------------------+-----+
0.8414709848078965| 0.0|
# 报错
SELECT ASIN(0.8414709848078965),ASIN(3);
SIN(x)返回x的正弦，其中，x为弧度值。
ASIN(x)返回x的反正弦，即正弦为x的值。若x不在-1到1的范围之内，则会弹出错误信息：“输入超出范围”。
5.1.10 余弦函数COS(x)和反余弦函数ACOS(x)
SELECT COS(0),COS(PI()),COS(1);
cos|cos |cos |
---+----+------------------+
1.0|-1.0|0.5403023058681398|
SELECT ACOS(1),ACOS(0),ROUND(ACOS(0.54030230586814));
acos|acos |round|
----+------------------+-----+
0.0|1.5707963267948966| 1.0|
COS(x)返回x的余弦，其中x为弧度值。
ACOS(x)返回x的反余弦，即余弦是x的值。若x不在-1到1的范围之内，则会弹出错误信息。
5.1.11 正切函数、反正切函数和余切函数
SELECT TAN(0.3),ROUND(TAN(PI()/4));
tan |round|
-------------------+-----+
0.30933624960962325| 1.0|
SELECT ATAN(0.30933624960962325),ATAN(1);
atan|atan |
----+------------------+
0.3|0.7853981633974483|
SELECT COT(0.3),1/TAN(0.3),COT(PI()/4);
cot |?column? |cot |
------------------+------------------+------------------+
3.2327281437658275|3.2327281437658275|1.0000000000000002|
TAN(x)返回x的正切,其中x为给定的弧度值。
ATAN(x)返回x的反正切，即正切为x的值。
COT(x)返回x的余切。
5.2 字符串函数
5.2.1 计算字符串字符数的函数和字符串长度的函数
SELECT CHAR_LENGTH('date'),CHAR_LENGTH('egg');
char_length|char_length|
-----------+-----------+
4| 3|
SELECT LENGTH('date'),LENGTH('egg');
length|length|
------+------+
4| 3|
CHAR_LENGTH(str)返回值为字符串 str所包含字符的个数。一个多字节字符算作一个单字符。
LENGTH(str)返回值为字符串的字节长度，使用utf8 编码字符集时，一个汉字是3个字节，一个数字或字母算作一
个字节。
5.2.2 合并字符串函数 CONCAT(s1,s2,…)、CONCAT_WS(x,s1,s2,…)
SELECT CONCAT('PostgreSQL','9.15'),CONCAT('Postgre',NULL,'SQL');
concat |concat |
--------------+----------+
PostgreSQL9.15|PostgreSQL|
SELECT CONCAT WS('-','ist','2nd','3rd'),CONCAT_WS('*','1st',NULL,'3rd');
concat_ws |concat_ws|
-----------+---------+
ist-2nd-3rd|1st*3rd |
CONCAT(sl,s2,*··)返回结果为连接参数产生的字符串。如果有任何一个参数为NULL，则返回值为NULL；如果
所有参数均为非二进制字符串，则结果为非二进制字符串；如果自变量中含有任何一个二进制字符串，则结果为一
个二进制字符串。
CONCAT_WS(x,sl,s2,"·)，其中，CONCAT_WS代表 CONCAT With Separator，是CONCATO的特殊形式。第一
个参数x是其他参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是
其他参数。如果分隔符为NULL,则结果为NULL。函数会忽略任何分隔符参数后的 NULL值。
5.2.3 获取指定长度的字符串函数LEFT(s,n)和 RIGHT(s,n)
SELECT LEFT('football',5);
left |
-----+
footb|
SELECT RIGHT('football',4);
right|
-----+
ball |
LEFT(s,n)返回字符串s开始的最左边n个字符。
RIGHT(s,n)返回字符串s最右边n个字符。
5.2.4 填充字符串的函数LPAD(s1,len,s2)和 RPAD(s1,len,s2)
SELECT LPAD('hello',4,'?'),LPAD('hello',10,'?');
lpad|lpad |
----+----------+
hell|?????hello|
SELECT RPAD('hello',4,'?'),RPAD('hello',10,'?');
rpad|rpad |
----+----------+
hell|hello?????|
RPAD(sl,len,s2)返回字符串s1，其右边被字符串s2 填充至len 字符长度。假如sl的长度大于len，则返回值被缩
短到与 len 字符相同长度。
LPAD(sl,len,s2)返回字符串s1，其左边由字符串s2填充，填充至len字符长度。假如sl的长度大于len，则返回
值被缩短至len 字符。
5.2.5 删除空格的函数 LTRIM(s)、RTRIM(s)和 TRIM(s)
SELECT'(book )',CONCAT('(',LTRIM('book'),')');
?column?|concat|
--------+------+
(book ) |(book)|
SELECT '(book )',CONCAT('(', RTRIM(' book'),')');
?column?|concat |
--------+-------+
(book ) |( book)|
SELECT '(book )',CONCAT('(',TRIM('book '),')');
?column?|concat|
--------+------+
(book ) |(book)|
LTRIM(s)返回字符串s，字符串左侧空格字符被删除。
RTRIM(s)返回字符串s，字符串右侧空格字符被删除。
TRIM(s)删除字符串s两侧的空格。
5.2.6 删除指定字符串的函数 TRIM(s1 FROMs)
SELECT TRIM('xy'FROM'xyboxyokxyxy');
btrim |
------+
boxyok|
TRIM(s1 FROMs)删除字符串s中两端所有的子字符串sl。s1为可选项，在未指定情况下，删除空格。
5.2.7 重复生成字符串的函数REPEAT(s,n)
SELECT REPEAT('PostgreSQL',3);
repeat |
------------------------------+
PostgreSQLPostgreSQLPostgreSQL|
REPEAT(s,n)返回一个由重复的字符串s组成的字符串，n表示重复生成的次数。若n<=0，则返回一个空字符串。
若s或n为NULL，则返回NULL。
5.2.8 替换函数 REPLACE(s,s1,s2)
SELECT REPLACE('xxx.PostgreSQL.com','x','w');
replace |
------------------+
www.PostgreSQL.com|
REPLACE(s,sl,s2)使用字符串 s2替代字符串s中所有的字符串sl。
5.2.9 获取子串的函数 SUBSTRING(s,n,len)
SELECT SUBSTRING('breakfast',5) AS coll,SUBSTRING('breakfast',5,3) AS col2,SUBSTRING('lunch',-3) AS col3;
coll |col2|col3 |
-----+----+-----+
kfast|kfa |lunch|
SUBSTRING(s,n,len)表示从字符串s返回一个长度为len 的子字符串，起始于位置n。也可能对n使用一个负值。
假若这样，则子字符串的为整个字符串。
5.2.10 匹配子串开始位置的函数 POSITION(str1 IN str)
SELECT POSITION('ball'IN'football');
position|
--------+
5|
POSITION(strl IN str)函数的作用是返回子字符串 strl 在字符串 str 中的开始位置。
5.2.11 字符串逆序的函数 REVERSE(s)
SELECT REVERSE('abc');
reverse|
-------+
cba |
REVERSE(s)将字符串s反转，返回的字符串的顺序和s字符顺序相反。
5.2.12 小写函数LOWER
SELECT LOWER('GEEKSFORGEEKS');
lower |
-------------+
geeksforgeeks|
5.3 时间和日期函数
5.3.1 获取当前日期的函数和获取当前时间的函数
SELECT CURRENT_DATE;
current_date|
------------+
2022-09-12|
SELECT CURRENT_TIME;
current_time |
--------------+
09:15:07 +0800|
SELECT LOCALTIME;
localtime|
---------+
09:17:57|
CURRENT_DATE函数的作用是将当前日期按照YYYY-MM-DD格式的值返回，具体格式根据函数用在字符串或是数字
语境中而定。
CURRENT_TIME函数的作用是将当前时间以HH:MM:SS的格式返回，具体格式根据函数用在字符串或是数字语境中
而定。
LOCALTIME函数的作用是将当前时间以HH:MM:SS的格式返回，唯一和 CURRENT_TIME函数不同的是，返回的是
不带时区的值。
5.3.2 获取当前日期和时间的函数
SELECT CURRENT_TIMESTAMP,LOCALTIMESTAMP,NOW();
current_timestamp |localtimestamp |now |
-----------------------------+-----------------------+-----------------------------+
2022-09-12 09:19:16.374 +0800|2022-09-12 09:19:16.374|2022-09-12 09:19:16.374 +0800|
CURRENT_TIMESTAMP、LOCALTIMESTAMP和 NOW()3个函数的作用相同，即返回当前日期和时间值，格式为
YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS，具体格式根据函数是否用在字符串或数字语境而定。
5.3.3 获取日期指定值的函数 EXTRACT(type FROM d)
SELECT EXTRACT(DAY FROM TIMESTAMP '2012-09-10 10:18:40');
extract|
-------+
10|
SELECT EXTRACT(MONTH FROM TIMESTAMP '2012-09-10 10:18:40');
extract|
-------+
9|
SELECT EXTRACT(YEAR FROM TIMESTAMP '2012-09-10 10:18:40');
extract|
-------+
2012|
# 使用EXTRACT函数查询指定日期是一年中的第几天
SELECT EXTRACT(DOY FROM TIMESTAMP '2012-09-10 10:18:40');
extract|
-------+
254|
# 使用EXTRACT函数查询指定日期是一周中的星期几
SELECT EXTRACT(DOW FROM TIMESTAMP '2012-09-10 10:18:40');
extract|
-------+
1|
# 使用EXTRACT函数查询指定日期是该年的第几季度(1~4)
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2012-09-10 10:18:40');
extract|
-------+
3|
EXTRACT(type FROM date)函数从日期中提取其部分，而不是执行日期运算。
5.3.4 日期和时间的运算操作
SELECT DATE '2012-09-28' + integer '10';
?column? |
----------+
2012-10-08|
SELECT DATE '2012-09-28' + interval ' 3 hour';
?column? |
-----------------------+
2012-09-28 03:00:00.000|
SELECT DATE '2012-09-28' + time '06:00';
?column? |
-----------------------+
2012-09-28 06:00:00.000|
SELECT TIMESTAMP '2012-09-28 02:00:00' + interval '10 hours';
?column? |
-----------------------+
2012-09-28 12:00:00.000|
SELECT date '2012-11-01' - date '2012-09-10';
?column?|
--------+
52|
SELECT DATE '2012-09-28' - integer '10';
?column? |
----------+
2012-09-18|
SELECT 15 * interval '2 day';
?column?|
--------+
30 days|
SELECT 50 * interval '2 second';
?column?|
--------+
00:01:40|
SELECT interval '1 hour'/ integer '2';
?column?|
--------+
00:30:00|
5.4 条件判断函数
5.4.1 CASE value WHEN v1 THEN r1[WHEN v2 THEN r2][ELSE rn]END
SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
case|
----+
two |
该函数表示，如果 value 值等于某个vn，则返回对应位置 THEN 后面的结果；如果与所有值都不相等，则返回
ELSE后面的rn。
5.4.2 CASE WHEN v1 THEN r1[WHEN v2 THENr2]ELSE rn]END
SELECT CASE WHEN 1<0 THEN 'true' ELSE 'false' END;
case |
-----+
false|
该函数表示，某个vn值为TRUE时，返回对应位置 THEN 后面的结果；如果所有值都不为TRUE，则返回ELSE 后的
rn。
5.5 系统信息函数
PostgreSQL 中的系统信息有：数据库的版本号、当前用户名和链接数、系统字符集、最后一个自动生成的ID值
等。
5.5.1 获取PostgreSQL版本号
SELECT VERSION();
version |
------------------------------+
PostgreSQL 14.5, compiled by Visual C++ build 1914, 64-bit|
VERSION()返回指示PostgreSQL服务器版本的字符串。这个字符串使用utf8字符集。
5.5.2 获取用户名的函数
SELECT USER,CURRENT_USER;
user |current_user|
--------+------------+
postgres|postgres |
USER和 CURRENT_USER 函数返回当前被 PostgreSQL服务器验证的用户名。这个值符合当前登录用户存取权限的
PostgreSQL账户。一般情况下，这两个函数的返回值是相同的。
5.6 加密函数
5.6.1 加密函数 MD5(str)
SELECT MD5 ('mypwd');
md5 |
--------------------------------+
318bcb4be908d0da6448a0db76908d78|
MD5(str)为字符串算出一个MD5128比特检查和。该值以32位十六进制数字的二进制字符串的形式返回，若参数
为NULL则会返回NULL。
5.6.2 加密函数 ENCODE(str,pswd_str)
SELECT ENCODE('secret','hex'),LENGTH(ENCODE('secret','hex'));
encode |length|
------------+------+
736563726574| 12|
ENCODE(str,pswd_str)使用 pswd_str 作为加密编码，来加密str。常见的加密编码包括：base64、hex 和
escape。
5.6.3 解密函数 DECODE(crypt_str,pswd_str)
SELECT DECODE(ENCODE('secret','hex'),'hex');
decode|
------+
secret|
DECODE(crypt_str,pswd_str)使用 pswd_str 作为密码，解密加密字符串 crypt_str，crypt_str是由ENCODEO
返回的字符串。
5.7 改变数据类型的函数
SELECT CAST(100 AS CHAR(2));
bpchar|
------+
10 |
CAST(x,AS type)函数将一个类型的值转换为另一个类型的值。
6、插入、删除与更新数据
6.1 插入数据
6.1.1 为表的所有字段插入数据
# 语法
INSERT INTO table name(column_list) VALUES(value_list);
CREATE TABLE person
(
id INT NOT NULL,
name CHAR(40) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0,
info CHAR(50) NULL,
PRIMARY KEY(id)
);
INSERT INTO person (id,name,age, info) VALUES(1,'Green',21,'Lawyer');
SELECT * FROM person;
id|name |age|info |
--+----------------------------------------+---+----------------------+
1|Green | 21|Lawyer |
INSERT INTO person (age,name,id,info) VALUES(22,'Suse',2,'dancer');
SELECT * FROM person;
id|name |age|info |
--+----------------------------------------+---+----------------------+
1|Green | 21|Lawyer |
2|Suse | 22|dancer |
INSERT INTO person VALUES(3,'Mary',24,'Musician');
SELECT * FROM person;
id|name |age|info |
--+----------------------------------------+---+----------------------+
1|Green | 21|Lawyer |
2|Suse | 22|dancer |
3|Mary | 24|Musician |
6.1.2 为表的指定字段插入数据
INSERT INTO person(id,name,age )VALUES(4,'Laura',25);
SELECT * FROM person;
id|name |age|info |
--+----------------------------------------+---+----------------------+
1|Green | 21|Lawyer |
2|Suse | 22|dancer |
3|Mary | 24|Musician |
4|Laura | 25| |
6.1.3 同时插入多条记录
# 语法
INSERT INTO 表名 (属性列表) VALUES(取值列表1),(取值列表2)...,(取值列表n);
INSERT INTO person(id,name,age,info) VALUES(5,'Evans',27,'secretary'),
(6,'Dale',22,'cook'), (7,'Edison',28,'singer');
SELECT * FROM person;
id|name |age|info |
--+----------------------------------------+---+----------------------+
1|Green | 21|Lawyer |
2|Suse | 22|dancer |
3|Mary | 24|Musician |
4|Laura | 25| |
5|Evans | 27|secretary |
6|Dale | 22|cook |
7|Edison | 28|singer |
INSERT INTO person VALUES (8,'Harry',21,'magician'),(9,'Harriet',19,'pianist');
SELECT * FROM person;
id|name |age|info |
--+----------------------------------------+---+----------------------+
1|Green | 21|Lawyer |
2|Suse | 22|dancer |
3|Mary | 24|Musician |
4|Laura | 25| |
5|Evans | 27|secretary |
6|Dale | 22|cook |
7|Edison | 28|singer |
8|Harry | 21|magician |
9|Harriet | 19|pianist |
向 PostgreSQL的表中插入多条记录时，可以使用多个INSERT语句逐条插入记录，也可以使用一个INSERT语句插
入多条记录。相比而言，对于大量的插入记录，使用一个 INSERT 语句的速度比较快。所以在插入多条记录时，最
好选择使用单条 INSERT语句的插入方式。
6.1.4 将查询结果插入到表中
# 语法
INSERT INTO table name1(column_listl) SELECT(column_list2) FROM table name2 WHERE(condition)
CREATE TABLE person_old
(
id INT NOT NULL,
name CHAR(40) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0,
info CHAR(50) NULL,
PRIMARY KEY(id)
);
INSERT INTO person_old VALUES(10,'Harry',20,'student'), (11,'Beckham',31,'police');
SELECT * FROM person_old;
id|name |age|info |
--+----------------------------------------+---+----------------------+
10|Harry | 20|student |
11|Beckham | 31|police |
INSERT INTO person(id,name,age,info) SELECT id,name,age,info FROM person_old;
SELECT * FROM person;
id|name |age|info |
--+----------------------------------------+---+----------------------+
1|Green | 21|Lawyer |
2|Suse | 22|dancer |
3|Mary | 24|Musician |
4|Laura | 25| |
5|Evans | 27|secretary |
6|Dale | 22|cook |
7|Edison | 28|singer |
8|Harry | 21|magician |
9|Harriet | 19|pianist |
10|Harry | 20|student |
11|Beckham | 31|police |
6.2 更新数据
# 语法
UPDATE table name SET column_namel= valuel,column_name2=value2,..,column_namen=valuen
WHERE(condition);
SELECT * FROM person WHERE id=10;
id|name |age|info |
--+----------------------------------------+---+----------------------+
10|Harry | 20|student |
UPDATE person SET age=15,name='LiMing' WHERE id=10;
SELECT * FROM person WHERE id=10;
id|name |age|info |
--+----------------------------------------+---+----------------------+
10|LiMing | 15|student |
SELECT * FROM person WHERE age BETWEEN 19 AND 22;
id|name |age|info |
--+----------------------------------------+---+----------------------+
1|Green | 21|Lawyer |
2|Suse | 22|dancer |
6|Dale | 22|cook |
8|Harry | 21|magician |
9|Harriet | 19|pianist |
UPDATE person SET info='student' WHERE age BETWEEN 19 AND 22;
SELECT * FROM person WHERE age BETWEEN 19 AND 22;
id|name |age|info |
--+----------------------------------------+---+----------------------+
1|Green | 21|student |
2|Suse | 22|student |
6|Dale | 22|student |
8|Harry | 21|student |
9|Harriet | 19|student |
6.3 删除数据
# 语法
# 如果没有WHERE子句,DELETE 语句将删除表中的所有记录
DELETE FROM table name[WHERE<condition>];
SELECT * FROM person WHERE id=10;
id|name |age|info |
--+----------------------------------------+---+----------------------+
10|LiMing | 15|student |
DELETE FROM person WHERE id=10;
SELECT * FROM person WHERE id=10;
id|name|age|info|
--+----+---+----+
SELECT * FROM person WHERE age BETWEEN 19 AND 22;
id|name |age|info |
--+----------------------------------------+---+----------------------+
1|Green | 21|student |
2|Suse | 22|student |
6|Dale | 22|student |
8|Harry | 21|student |
9|Harriet | 19|student |
DELETE FROM person WHERE age BETWEEN 19 AND 22;
SELECT * FROM person WHERE age BETWEEN 19 AND 22;
id|name|age|info|
--+----+---+----+
SELECT * FROM person;
id|name |age|info |
--+----------------------------------------+---+----------------------+
3|Mary | 24|Musician |
4|Laura | 25| |
5|Evans | 27|secretary |
7|Edison | 28|singer |
11|Beckham | 31|police |
DELETE FROM person;
SELECT * FROM person;
id|name|age|info|
--+----+---+----+
7、数据查询
7.1 基本查询语句
PostgreSQL从数据表中查询数据的基本语句为SELECT语句。SELECT语句的基本格式是：
SELECT
{ * | <字段列表> }
[
FROM <表1>,<表2>...
[ WHERE <表达式> ]
[ GROUP BY <group by definition>]
[ HAVING <expression> [{<operator><expression>}...]]
[ ORDER BY <order by definition>]
[ LIMIT [<offset>,] <row count>]
]
SELECT [字段1,字段2,.,字段 n]
FROM [表或视图]
WHERE [查询条件];
CREATE TABLE fruits
(
f_id char(10) NOT NULL,
s_id INT NOT NULL,
f_name char(255) NOT NULL,
f_price decimal(8,2) NOT NULL,
PRIMARY KEY(f_id)
);
INSERT INTO fruits(f_id,s_id,f_name,f_price)
VALUES('al',101,'apple',5.2),
('bl',101,'blackberry',10.2),
('bsl',102,'orange',11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana',10.3),
('t2',102,'grape',5.3),
('o2',103,'coconut',9.2),
('c0',101,'cherry',3.2),
('a2',103,'apricot',2.2),
('12',104,'lemon',6.4),
('b2',104,'berry',7.6),
('m1',106,'mango',15.6),
('m2',105,'xbabay',2.6),
('t4',107,'xbababa',3.6),
('m3',105,'xxtt',11.6),
('b5',107,'xxxx', 3.6);
SELECT f_id,f_name FROM fruits;
f_id |f_name |
----------+---------------+
al |apple |
bl |blackberry |
bsl |orange |
bs2 |melon |
t1 |banana |
t2 |grape |
o2 |coconut |
c0 |cherry |
a2 |apricot |
12 |lemon |
b2 |berry |
m1 |mango |
m2 |xbabay |
t4 |xbababa |
m3 |xxtt |
b5 |xxxx |
7.2 单表查询
7.2.1 查询所有字段
1、在select语句中使用星号“*”通配符查询所有字段
# 语法
SELECT * FROM 表名;
SELECT * FROM fruits;
f_id |s_id|f_name |f_price|
----------+----+---------------+
al | 101|apple | 5.20|
bl | 101|blackberry | 10.20|
bsl | 102|orange | 11.20|
bs2 | 105|melon | 8.20|
t1 | 102|banana | 10.30|
t2 | 102|grape | 5.30|
o2 | 103|coconut | 9.20|
c0 | 101|cherry | 3.20|
a2 | 103|apricot | 2.20|
12 | 104|lemon | 6.40|
b2 | 104|berry | 7.60|
m1 | 106|mango | 15.60|
m2 | 105|xbabay | 2.60|
t4 | 107|xbababa | 3.60|
m3 | 105|xxtt | 11.60|
b5 | 107|xxxx | 3.60|
2、在SELECT 语句中指定所有字段
SELECT f_id,s_id,f_name,f_price FROM fruits;
f_id |s_id|f_name |f_price|
----------+----+---------------+
al | 101|apple | 5.20|
bl | 101|blackberry | 10.20|
bsl | 102|orange | 11.20|
bs2 | 105|melon | 8.20|
t1 | 102|banana | 10.30|
t2 | 102|grape | 5.30|
o2 | 103|coconut | 9.20|
c0 | 101|cherry | 3.20|
a2 | 103|apricot | 2.20|
12 | 104|lemon | 6.40|
b2 | 104|berry | 7.60|
m1 | 106|mango | 15.60|
m2 | 105|xbabay | 2.60|
t4 | 107|xbababa | 3.60|
m3 | 105|xxtt | 11.60|
b5 | 107|xxxx | 3.60|
7.2.2 查询指定字段
1、查询单个字段
# 语法
SELECT 列名 FROM 表名;
SELECT f_name FROM fruits;
f_name |
---------------+
apple |
blackberry |
orange |
melon |
banana |
grape |
coconut |
cherry |
apricot |
lemon |
berry |
mango |
xbabay |
xbababa |
xxtt |
xxxx |
2、查询多个字段
# 语法
SELECT 字段名1,字段名2,..,字段名n FROM 表名;
SELECT f_name,f_price FROM fruits;
f_name |f_price|
---------------+
apple | 5.20|
blackberry | 10.20|
orange | 11.20|
melon | 8.20|
banana | 10.30|
grape | 5.30|
coconut | 9.20|
cherry | 3.20|
apricot | 2.20|
lemon | 6.40|
berry | 7.60|
mango | 15.60|
xbabay | 2.60|
xbababa | 3.60|
xxtt | 11.60|
xxxx | 3.60|
7.2.3 查询指定记录
# 语法
SELECT 字段名1,字段名2,...,字段名n
FROM 表名
WHERE 查询条件
| 操作符 | 说明 |
|---|---|
| = | 相等 |
| != | 不相等 |
| < | 小于 |
| <= | 小于或者等于 |
| > | 大于 |
| >= | 大于或者等于 |
| BETWEEN | 位于两值之间 |
SELECT f_name, f_price FROM fruits WHERE f_price= 10.2;
f_name |f_price|
---------------+
blackberry | 10.20|
SELECT f_name, f_price FROM fruits WHERE f_name = 'apple';
f_name |f_price|
---------------+
apple | 5.20|
SELECT f_name,f_price FROM fruits WHERE f_price<10;
f_name |f_price|
---------------+
apple | 5.20|
melon | 8.20|
grape | 5.30|
coconut | 9.20|
cherry | 3.20|
apricot | 2.20|
lemon | 6.40|
berry | 7.60|
xbabay | 2.60|
xbababa | 3.60|
xxxx | 3.60|
7.2.4 带IN关键字的查询
SELECT s_id,f_name,f_price FROM fruits WHERE s_id IN(101,102) ORDER BY f_name;
s_id|f_name |f_price|
----+---------------+
101|apple | 5.20|
102|banana | 10.30|
101|blackberry | 10.20|
101|cherry | 3.20|
102|grape | 5.30|
102|orange | 11.20|
7.2.5 带 BETWEEN AND的范围查询
SELECT f_name,f_price FROM fruits WHERE f_price BETWEEN 2.00 AND 10.20;
f_name |f_price|
---------------+
apple | 5.20|
blackberry | 10.20|
melon | 8.20|
grape | 5.30|
coconut | 9.20|
cherry | 3.20|
apricot | 2.20|
lemon | 6.40|
berry | 7.60|
xbabay | 2.60|
xbababa | 3.60|
xxxx | 3.60|
SELECT f_name,f_price FROM fruits WHERE f_price NOT BETWEEN 2.00 AND 10.20;
f_name |f_price|
---------------+
orange | 11.20|
banana | 10.30|
mango | 15.60|
xxtt | 11.60|
7.2.6 带like的字符串匹配
SELECT f_id, f_name FROM fruits WHERE f_name LIKE'b%';
f_id |f_name |
----------+---------------+
bl |blackberry |
t1 |banana |
b2 |berry |
SELECT f_id, f_name FROM fruits WHERE f_name LIKE'%g%';
f_id |f_name |
----------+---------------+
bsl |orange |
t2 |grape |
m1 |mango |
7.2.7 控制查询
CREATE TABLE customers
(
c_id char(10) PRIMARY KEY,
c_name varchar(255) NOT NULL,
c_email varchar(50) NULL
);
INSERT INTO customers (c_id,c_name,c_email) VALUES
('10001','RedHook','LMing@163.com'),
('10002','Stars','Jerry@hotmail.com'),
('10003','RedHook',NULL),
('10004','JOTO','sam@hotmail.com');
SELECT c_id,c_name,c_email FROM customers WHERE c_email IS NULL;
c_id |c_name |c_email|
----------+-------+-------+
10003 |RedHook| |
SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NOT NULL;
c_id |c_name |c_email |
----------+-------+-----------------+
10001 |RedHook|LMing@163.com |
10002 |Stars |Jerry@hotmail.com|
10004 |JOTO |sam@hotmail.com |
7.2.8 带and的多条件查询
SELECT s_id,f_price,f_name FROM fruits WHERE s_id='101' AND f_price>=5;
s_id|f_price|f_name |
----+-------+---------------+
101| 5.20|apple |
101| 10.20|blackberry |
SELECT f_id,f_price,f_name FROM fruits WHERE s_id IN('101','102') AND f_price >=5 AND f_name='apple';
f_id |f_price|f_name |
----------+-------+---------------+
al | 5.20|apple |
7.2.9 带or的多条件查询
SELECT s_id,f_name,f_price FROM fruits WHERE s_id=101 OR s_id = 102;
s_id|f_name |f_price|
----+---------------+
101|apple | 5.20|
101|blackberry | 10.20|
102|orange | 11.20|
102|banana | 10.30|
102|grape | 5.30|
101|cherry | 3.20|
SELECT s_id,f_name,f_price FROM fruits WHERE s_id IN(101,102);
s_id|f_name |f_price|
----+---------------+
101|apple | 5.20|
101|blackberry | 10.20|
102|orange | 11.20|
102|banana | 10.30|
102|grape | 5.30|
101|cherry | 3.20|
7.2.10 查询结果不重复
# 语法
SELECT DISTINCT 字段名 FROM 表名;
SELECT DISTINCT s_id FROM fruits;
s_id|
----+
105|
107|
101|
102|
103|
106|
104|
7.2.11 对查询结果排序
1、单列排序
SELECT f_name FROM fruits;
f_name |
---------------+
apple |
blackberry |
orange |
melon |
banana |
grape |
coconut |
cherry |
apricot |
lemon |
berry |
mango |
xbabay |
xbababa |
xxtt |
xxxx |
SELECT f_name FROM fruits ORDER BY f_name;
f_name |
---------------+
apple |
apricot |
banana |
berry |
blackberry |
cherry |
coconut |
grape |
lemon |
mango |
melon |
orange |
xbababa |
xbabay |
xxtt |
xxxx |
2、多列排序
SELECT f_name,f_price FROM fruits ORDER BY f_name,f_price;
f_name |f_price|
---------------+
apple | 5.20|
apricot | 2.20|
banana | 10.30|
berry | 7.60|
blackberry | 10.20|
cherry | 3.20|
coconut | 9.20|
grape | 5.30|
lemon | 6.40|
mango | 15.60|
melon | 8.20|
orange | 11.20|
xbababa | 3.60|
xbabay | 2.60|
xxtt | 11.60|
xxxx | 3.60|
3、指定排序方向
SELECT f_name, f_price FROM fruits ORDER BY f_price DESC;
f_name |f_price|
---------------+
mango | 15.60|
xxtt | 11.60|
orange | 11.20|
banana | 10.30|
blackberry | 10.20|
coconut | 9.20|
melon | 8.20|
berry | 7.60|
lemon | 6.40|
grape | 5.30|
apple | 5.20|
xxxx | 3.60|
xbababa | 3.60|
cherry | 3.20|
xbabay | 2.60|
apricot | 2.20|
SELECT f_price,f_name FROM fruits ORDER BY f_price DESC,f_name;
f_price|f_name |
-------+---------------+
15.60|mango |
11.60|xxtt |
11.20|orange |
10.30|banana |
10.20|blackberry |
9.20|coconut |
8.20|melon |
7.60|berry |
6.40|lemon |
5.30|grape |
5.20|apple |
3.60|xbababa |
3.60|xxxx |
3.20|cherry |
2.60|xbabay |
2.20|apricot |
7.2.12 分组查询
# 语法
[GROUP BY 字段][HAVING<条件表达式>]
1、创建分组
SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;
s_id|total|
----+-----+
105| 3|
107| 2|
101| 3|
102| 3|
103| 2|
106| 1|
104| 2|
2、使用 HAVING 过滤分组
SELECT s_id,COUNT(f_name) FROM fruits GROUP BY s_id HAVING COUNT(f_name)>1;
s_id|count|
----+-----+
105| 3|
107| 2|
101| 3|
102| 3|
103| 2|
104| 2|
3、GROUP BY和 ORDER BY一起使用
SELECT s_id,COUNT(f_name) FROM fruits GROUP BY s_id HAVING COUNT(f_name)>1
ORDER BY COUNT(f_name);
s_id|count|
----+-----+
107| 2|
103| 2|
104| 2|
105| 3|
101| 3|
102| 3|
7.2.13 用LIMIT限制查询结果的数量
# 语法
LIMIT行数[位置偏移量,]
SELECT * From fruits;
f_id |s_id|f_name |f_price|
----------+----+---------------+
al | 101|apple | 5.20|
bl | 101|blackberry | 10.20|
bsl | 102|orange | 11.20|
bs2 | 105|melon | 8.20|
t1 | 102|banana | 10.30|
t2 | 102|grape | 5.30|
o2 | 103|coconut | 9.20|
c0 | 101|cherry | 3.20|
a2 | 103|apricot | 2.20|
12 | 104|lemon | 6.40|
b2 | 104|berry | 7.60|
m1 | 106|mango | 15.60|
m2 | 105|xbabay | 2.60|
t4 | 107|xbababa | 3.60|
m3 | 105|xxtt | 11.60|
b5 | 107|xxxx | 3.60|
SELECT * From fruits LIMIT 4;
f_id |s_id|f_name |f_price|
----------+----+---------------+
al | 101|apple | 5.20|
bl | 101|blackberry | 10.20|
bsl | 102|orange | 11.20|
bs2 | 105|melon | 8.20|
SELECT * From fruits LIMIT 3 OFFSET 4;
f_id |s_id|f_name |f_price|
----------+----+---------------+
t1 | 102|banana | 10.30|
t2 | 102|grape | 5.30|
o2 | 103|coconut | 9.20|
7.3 集合函数查询
| 函数 | 作用 |
|---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值的和 |
7.3.1 COUNT()函数
SELECT COUNT(*) AS cust_num FROM customers;
cust_num|
--------+
4|
SELECT COUNT(c_email) AS email_num FROM customers;
email_num|
---------+
3|
SELECT s_id,COUNT(f_name) FROM fruits GROUP BY s_id;
s_id|count|
----+-----+
105| 3|
107| 2|
101| 3|
102| 3|
103| 2|
106| 1|
104| 2|
7.3.2 SUM()函数
SELECT SUM(f_price) As price_total FROM fruits WHERE s_id=101;
price_total|
-----------+
18.60|
SELECT s_id,SUM(f_price) AS price_total FROM fruits GROUP BY S_id;
s_id|price_total|
----+-----------+
105| 22.40|
107| 7.20|
101| 18.60|
102| 26.80|
103| 11.40|
106| 15.60|
104| 14.00|
7.3.3 AVG()函数
SELECT AVG(f_price) AS avg_price FROM fruits WHERE S_id = 103;
avg_price |
------------------+
5.7000000000000000|
SELECT s_id,AVG(f_price) AS avg_price FROM fruits GROUP BY s_id;
s_id|avg_price |
----+-------------------+
105| 7.4666666666666667|
107| 3.6000000000000000|
101| 6.2000000000000000|
102| 8.9333333333333333|
103| 5.7000000000000000|
106|15.6000000000000000|
104| 7.0000000000000000|
7.3.4 MAX()函数
SELECT MAX(f_price) AS max_price FROM fruits;
max_price|
---------+
15.60|
SELECT s_id,MAX(f_price) AS max_price FROM fruits GROUP BY S_id;
s_id|max_price|
----+---------+
105| 11.60|
107| 3.60|
101| 10.20|
102| 11.20|
103| 9.20|
106| 15.60|
104| 7.60|
SELECT MAX(f_name) FROM fruits;
max
------------------------------------------------------------------
xxxx
7.3.5 MIN()函数
SELECT MIN(f_price) AS min_price FROM fruits;
min_price|
---------+
2.20|
SELECT s_id,MIN(f_price) AS min_price FROM fruits GROUP BY s_id;
s_id|min_price|
----+---------+
105| 2.60|
107| 3.60|
101| 3.20|
102| 5.30|
103| 2.20|
106| 15.60|
104| 6.40|
7.4 连接查询
7.4.1 内连接查询
CREATE TABLE suppliers
(
s_id INT PRIMARY KEY,
s_name varchar(50) NOT NULL,
s_city varchar(50) NOT NULL
);
INSERT INTO suppliers(s_id,s_name,s_city)
VALUES(101,'FastFruit Inc','Tianjin'),
(102,'LT Supplies','shanghai'),
(103,'ACME','beijing'),
(104,'FNK Inc','zhengzhou'),
(105,'Good Set','xinjiang'),
(106,'Just Eat Ours','yunnan'),
(107,'JOTO meoukou','guangdong');
select * from suppliers;
s_id|s_name |s_city |
----+-------------+---------+
101|FastFruit Inc|Tianjin |
102|LT Supplies |shanghai |
103|ACME |beijing |
104|FNK Inc |zhengzhou|
105|Good Set |xinjiang |
106|Just Eat Ours|yunnan |
107|JOTO meoukou |guangdong|
select * from fruits;
f_id |s_id|f_name |f_price|
----------+----+---------------+
al | 101|apple | 5.20|
bl | 101|blackberry | 10.20|
bsl | 102|orange | 11.20|
bs2 | 105|melon | 8.20|
t1 | 102|banana | 10.30|
t2 | 102|grape | 5.30|
o2 | 103|coconut | 9.20|
c0 | 101|cherry | 3.20|
a2 | 103|apricot | 2.20|
12 | 104|lemon | 6.40|
b2 | 104|berry | 7.60|
m1 | 106|mango | 15.60|
m2 | 105|xbabay | 2.60|
t4 | 107|xbababa | 3.60|
m3 | 105|xxtt | 11.60|
b5 | 107|xxxx | 3.60|
SELECT suppliers.s_id,s_name,f_name,f_price FROM fruits,suppliers WHERE fruits.s_id=suppliers.s_id;
s_id|s_name |f_name |f_price|
----+-------------+---------------+
101|FastFruit Inc|cherry | 3.20|
101|FastFruit Inc|blackberry | 10.20|
101|FastFruit Inc|apple | 5.20|
102|LT Supplies |grape | 5.30|
102|LT Supplies |banana | 10.30|
102|LT Supplies |orange | 11.20|
103|ACME |apricot | 2.20|
103|ACME |coconut | 9.20|
104|FNK Inc |berry | 7.60|
104|FNK Inc |lemon | 6.40|
105|Good Set |xxtt | 11.60|
105|Good Set |xbabay | 2.60|
105|Good Set |melon | 8.20|
106|Just Eat Ours|mango | 15.60|
107|JOTO meoukou |xxxx | 3.60|
107|JOTO meoukou |xbababa | 3.60|
SELECT suppliers.s_id,s_name,f_name, f_price
FROM fruits INNER JOIN suppliers
ON fruits.s_id= suppliers.s_id;
s_id|s_name |f_name |f_price|
----+-------------+---------------+
101|FastFruit Inc|cherry | 3.20|
101|FastFruit Inc|blackberry | 10.20|
101|FastFruit Inc|apple | 5.20|
102|LT Supplies |grape | 5.30|
102|LT Supplies |banana | 10.30|
102|LT Supplies |orange | 11.20|
103|ACME |apricot | 2.20|
103|ACME |coconut | 9.20|
104|FNK Inc |berry | 7.60|
104|FNK Inc |lemon | 6.40|
105|Good Set |xxtt | 11.60|
105|Good Set |xbabay | 2.60|
105|Good Set |melon | 8.20|
106|Just Eat Ours|mango | 15.60|
107|JOTO meoukou |xxxx | 3.60|
107|JOTO meoukou |xbababa | 3.60|
SELECT fl.f_id,fl.f_name
FROM fruits AS fl, fruits AS f2
WHERE fl.s_id=f2.s_id AND f2.f_id='al';
f_id |f_name
----------+--------------
al |apple
bl |blackberry
c0 |cherry
7.4.2 外连接查询
连接查询将查询多个表中相关联的行，内连接时，返回查询结果集合中的仅是符合查询条件和连接条件的行，但有
时需要包含没有关联的行中数据，即返回的查询结果集合中，不仅包含符合连接条件的行，而且还包括左表(左外
连接或左连接)、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。外连接分为左外连接和右外连
接：
LEFT JOIN(左连接)，返回包括左表中的所有记录和右表中连接字段相等的记录。
RIGHT JOIN(右连接)，返回包括右表中的所有记录和左表中连接字段相等的记录,
1、LEFT JOIN 左连接
左连接的结果包括LEFT OUTER JOIN关键字左边连接表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行
在右表中没有匹配行，则在相关联的结果集行中右表的所有选择表字段均为空值。
CREATE TABLE orders
(
o_num INT NULL,
o_date DATE NOT NULL,
c_id varchar(50) NOT NULL
);
INSERT INTO orders (o_num,o_date,c_id) VALUES
(30001,'2008-09-01 00:00:00','10001'),
(30002,'2008-09-12 00:00:00','10003'),
(30003,'2008-09-30 00:00:00','10004'),
(NULL,'2008-10-03 00:00:00','10002'),
(30004,'2008-10-03 00:00:00', 'NULL'),
(30005,'2008-10-08 00:00:00','10001');
select * from orders;
o_num|o_date |c_id |
-----+----------+-----+
30001|2008-09-01|10001|
30002|2008-09-12|10003|
30003|2008-09-30|10004|
|2008-10-03|10002|
30004|2008-10-03|NULL |
30005|2008-10-08|10001|
select * from customers;
c_id |c_name |c_email |
----------+-------+-----------------+
10001 |RedHook|LMing@163.com |
10002 |Stars |Jerry@hotmail.com|
10003 |RedHook| |
10004 |JOTO |sam@hotmail.com |
SELECT customers.c_id,orders.o_num FROM customers LEFT OUTER JOIN orders
ON customers.c_id=orders.c_id;
c_id |o_num|
----------+-----+
10001 |30001|
10003 |30002|
10004 |30003|
10002 | |
10001 |30005|
2、RIGHT JOIN 右连接
右连接是左连接的反向连接。将返回RIGHT OUTER JOIN 关键字右边的表中的所有行。如果右表的某行在左表中
没有匹配行，左表将返回空值。
SELECT customers.c_id,orders.o_num FROM customers RIGHT OUTER JOIN orders
ON Customers.c_id= orders.c_id;
c_id |o_num|
----------+-----+
10001 |30001|
10003 |30002|
10004 |30003|
10002 | |
|30004|
10001 |30005|
7.4.3 复合条件连接查询
复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确。
SELECT customers.c_id,orders.o_num FROM customers INNER JOIN orders
ON customers.c_id=orders.c_id AND customers.c_id='10001';
c_id |o_num|
----------+-----+
10001 |30001|
10001 |30005|
SELECT suppliers.s_id,s_name,f_name,f_price FROM fruits INNER JOIN suppliers
ON fruits.s_id= suppliers.s_id ORDER BY fruits.s_id;
s_id|s_name |f_name |f_price|
----+-------------+---------------+
101|FastFruit Inc|cherry | 3.20|
101|FastFruit Inc|blackberry | 10.20|
101|FastFruit Inc|apple | 5.20|
102|LT Supplies |grape | 5.30|
102|LT Supplies |banana | 10.30|
102|LT Supplies |orange | 11.20|
103|ACME |apricot | 2.20|
103|ACME |coconut | 9.20|
104|FNK Inc |berry | 7.60|
104|FNK Inc |lemon | 6.40|
105|Good Set |xxtt | 11.60|
105|Good Set |xbabay | 2.60|
105|Good Set |melon | 8.20|
106|Just Eat Ours|mango | 15.60|
107|JOTO meoukou |xxxx | 3.60|
107|JOTO meoukou |xbababa | 3.60|
7.5 子查询
子查询是指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 PostgreSQL 4.1开始引入。在 SELECT
子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。子查询中常
用的操作符有 ANY(SOME)、ALL、IN、EXISTS。子查询可以添加到SELECT、UPDATE 和 DELETE 语句中，而且可
以进行多层嵌套。子查询中也可以使用比较运算符，如<、<=、>、>=和!=等。
7.5.1 带ANY、SOME关键字的子查询
ANY和SOME关键字是同义词，表示满足其中任一条件。它们允许创建一个表达式对子查询的返回值列表进行比
较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。
CREATE table tbl1(numl INT NOT NULL);
CREATE table tbl2(num2 INT NOT NULL);
INSERT INTO tbl1 values(1),(5),(13),(27);
INSERT INTO tbl2 values(6),(14),(11),(20);
SELECT numl FROM tbl1 WHERE numl>ANY(SELECT num2 FROM tbl2);
numl|
----+
13|
27|
7.5.2 带ALL关键字的子查询
ALL 关键字与 ANY 和SOME 不同，使用ALL时需要同时满足所有内层查询的条件。
SELECT numl FROM tbl1 WHERE numl>ALL(SELECT num2 FROM tbl2);
numl|
----+
27|
7.5.3 带EXISTS关键字的子查询
EXISTS 关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一
行，那么EXISTS的结果为TRUE，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的
结果是FALSE，此时外层语句将不进行查询。
SELECT * FROM fruits WHERE EXISTS (SELECT s_name FROM suppliers WHERE s_id=107);
f_id |s_id|f_name |f_price|
----------+----+-----------------------+
al | 101|apple | 5.20|
bl | 101|blackberry | 10.20|
bsl | 102|orange | 11.20|
bs2 | 105|melon | 8.20|
t1 | 102|banana | 10.30|
t2 | 102|grape | 5.30|
o2 | 103|coconut | 9.20|
c0 | 101|cherry | 3.20|
a2 | 103|apricot | 2.20|
12 | 104|lemon | 6.40|
b2 | 104|berry | 7.60|
m1 | 106|mango | 15.60|
m2 | 105|xbabay | 2.60|
t4 | 107|xbababa | 3.60|
m3 | 105|xxtt | 11.60|
b5 | 107|xxxx | 3.60|
SELECT * FROM fruits WHERE f_price>10.20 AND EXISTS (SELECT s_name FROM suppliers WHERE s_id=107);
f_id |s_id|f_name |f_price|
----------+----+-----------------------+
bsl | 102|orange | 11.20|
t1 | 102|banana | 10.30|
m1 | 106|mango | 15.60|
m3 | 105|xxtt | 11.60|
SELECT * FROM fruits WHERE NOT EXISTS (SELECT S_name FROM suppliers WHERE s_id = 107);
f_id|s_id|f_name|f_price|
----+----+------+-------+
EXISTS和 NOT EXISTS的结果只取决于是否会返回行，而不取决于这些行的内容,所以这个子查询输入列表通常是无
关紧要的。
7.5.4 带IN关键字的子查询
IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较
操作。
SELECT o_num FROM orders WHERE c_id IN (SELECT c_id FROM customers WHERE C_name='RedHook');
o_num|
-----+
30001|
30002|
30005|
SELECT C_id FROM customers WHERE c_name='RedHook';
c_id |
----------+
10001 |
10003 |
SELECT o_num FROM orders WHERE C_id IN('10001','10003');
o_num|
-----+
30001|
30002|
30005|
SELECT o_num FROM orders WHERE c_id NOT IN (SELECT C_id FROM customers WHERE C_name='RedHook');
o_num|
-----+
30003|
|
30004|
7.5.5 带比较运算符的子查询
在前面介绍带 ANY、ALL 关键字的子查询时，使用了>比较运算符，子查询时还可以使用其他比较运算符，
如<、<=、=、>=和!=等。
SELECT sl.s_id FROM suppliers AS sl WHERE sl.s_city='Tianjin';
s_id|
----+
101|
SELECT s_id, f_name FROM fruits WHERE s_id = (SELECT sl.s_id FROM suppliers AS sl WHERE sl.s_city='Tianjin');
s_id|f_name |
----+-------------------------+
101|apple |
101|blackberry |
101|cherry |
SELECT s_id,f_name FROM fruits WHERE s_id <> (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city='Tianjin');
s_id|f_name |
----+--------------------------+
102|orange |
105|melon |
102|banana |
102|grape |
103|coconut |
103|apricot |
104|lemon |
104|berry |
106|mango |
105|xbabay |
107|xbababa |
105|xxtt |
107|xxxx |
7.6 合并查询结果
利用UNION关键字，可以给出多条 SELECT 语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列
数和数据类型必须相同。各个SELECT 语句之间使用 UNION 或UNION ALL关键字分隔。UNION 不使用关键字
ALL，执行的时候删除重复的记录，所有返回的行都是唯一的；使用关键字ALL的作用是不删除重复行、也不对结
果进行自动排序。基本语法格式如下:
SELECT column,...FROM tablel
UNION[ALL]
SELECT column,...FROM table2
SELECT s_id, f_name,f_price FROM fruits WHERE f_price<9.0
UNION
SELECT s_id,f_name, f_price FROM fruits WHERE s_id IN(101,103);
s_id|f_name |f_price|
----+-------------------+
101|blackberry | 10.20|
101|apple | 5.20|
103|apricot | 2.20|
107|xxxx | 3.60|
104|berry | 7.60|
103|coconut | 9.20|
105|melon | 8.20|
102|grape | 5.30|
107|xbababa | 3.60|
105|xbabay | 2.60|
104|lemon | 6.40|
101|cherry | 3.20|
SELECT s_id, f_name, f_price FROM fruits WHERE f_price<9.0;
s_id|f_name |f_price|
----+------------------+
101|apple | 5.20|
105|melon | 8.20|
102|grape | 5.30|
101|cherry | 3.20|
103|apricot | 2.20|
104|lemon | 6.40|
104|berry | 7.60|
105|xbabay | 2.60|
107|xbababa | 3.60|
107|xxxx | 3.60|
SELECT s_id,f_name,f_price FROM fruits WHERE s_id IN(101,103);
s_id|f_name |f_price|
----+-------------------+-------+
101|apple | 5.20|
101|blackberry | 10.20|
103|coconut | 9.20|
101|cherry | 3.20|
103|apricot | 2.20|
SELECT s_id, f_name,f_price FROM fruits WHERE f_price<9.0
UNION ALL
SELECT s_id, f_name, f_price FROM fruits WHERE s_id IN(101,103);
s_id|f_name |f_price|
----+-----------------+-------+
101|apple | 5.20|
105|melon | 8.20|
102|grape | 5.30|
101|cherry | 3.20|
103|apricot | 2.20|
104|lemon | 6.40|
104|berry | 7.60|
105|xbabay | 2.60|
107|xbababa | 3.60|
107|xxxx | 3.60|
101|apple | 5.20|
101|blackberry | 10.20|
103|coconut | 9.20|
101|cherry | 3.20|
103|apricot | 2.20|
UNION和UNION ALL：使用 UNION ALL的功能是不删除重复行，加上ALL 关键字语句执行时所需要的资源少，所
以尽可能使用它，因此知道有重复行但是想保留这些行，确定查询结果中不会有重复数据或者不需要去掉重复数据
的时候，应当使用UNION ALL 以提高查询效率。
7.7 为表和字段取别名
7.7.1 为表取别名
# 语法
表名[AS]表别名
SELECT * FROM orders AS o WHERE o.o_num=30001;
o_num|o_date |c_id |
-----+----------+-----+
30001|2008-09-01|10001|
SELECT c.c_id,o.o_num FROM customers AS C LEFT OUTER JOIN orders AS O
ON c.c_id=o.c_id;
c_id |o_num|
----------+-----+
10001 |30001|
10003 |30002|
10004 |30003|
10002 | |
10001 |30005|
SELECT fl.f_id,fl.f_name FROM fruits AS fl,fruits AS f2 WHERE fl.s_id = f2.s_id AND f2.f_id = 'al';
f_id |f_name |
----------+--------------+
al |apple |
bl |blackberry |
c0 |cherry |
7.7.2 为字段取别名
列名[AS]列别名
SELECT f1.f_name AS fruit_name,f1.f_price AS fruit_price FROM fruits AS f1 WHERE f1.f_price<8;
fruit_name |fruit_price|
------------------+-----------+
apple | 5.20|
grape | 5.30|
cherry | 3.20|
apricot | 2.20|
lemon | 6.40|
berry | 7.60|
xbabay | 2.60|
xbababa | 3.60|
xxxx | 3.60|
SELECT CONCAT(RTRIM(S_name),'(',RTRIM(s_city),')') FROM suppliers ORDER BY S_name;
concat |
-----------------------+
ACME(beijing) |
FastFruit Inc(Tianjin) |
FNK Inc(zhengzhou) |
Good Set(xinjiang) |
JOTO meoukou(guangdong)|
Just Eat Ours(yunnan) |
LT Supplies(shanghai) |
SELECT CONCAT(RTRIM(S_name),'(',RTRIM(s_city),')') AS suppliers_title FROM suppliers
ORDER BY s_name;
suppliers_title |
-----------------------+
ACME(beijing) |
FastFruit Inc(Tianjin) |
FNK Inc(zhengzhou) |
Good Set(xinjiang) |
JOTO meoukou(guangdong)|
Just Eat Ours(yunnan) |
LT Supplies(shanghai) |
7.8 正则表达式查询
正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊
字符串。正则表达式强大而且灵活，可以应用于非常复杂的查询。
PostgreSQL中正则表达式的操作符使用方法如下。
-
~：匹配正则表达式，区分大小写。 -
~*：匹配正则表达式，不区分大小写。 -
!~：不匹配正则表达式，区分大小写。 -
!~*：不匹配正则表达式，不区分大小写。
PostgreSQL中使用指定正则表达式的字符匹配模式：
| 选项 | 说明 | 例子 | 匹配值示例 |
|---|---|---|---|
^ | 匹配文本的开始字符 | ^b匹配以字母b开头的字符串 | book、big、banana、bike |
. | 匹配任何单个字符 | b.t匹配任何b和t之间有一个字符的字符串 | bit、bat、but、bite |
* | 匹配零个或多个在它前面的字符 | f*n匹配字符n前面有任意个字符f的字符串 | fn、fan、faan、abcn |
+ | 匹配前面的字符1次或多次 | ba+匹配以b开头后面紧跟至少有一个a的字符串 | ba、bay、bare、battle |
| <字符串> | 匹配包含指定的字符串的文本 | fa | fan、afa、faad |
| [字符集合] | 匹配字符集合中的任何一个字符 | [xz]匹配任何包括x或者y的字符串 | dizzy、zebra、x-ray、 extra |
[^] | 匹配不在括号中的任何字符 | [^abc]匹配任何不包含a、b或c的字符串 | desk、fox、f8ke |
| 字符串{n,} | 匹配前面的字符串至少n次 | b{2}匹配两个或更多的b | bbb、bbbb、bbbbbbb |
| 字符串{n,m} | 匹配前面的字符串至少n次,至多m次。如果n为0,此参数为可选参数 | b{2,4}匹配最少两个,最多4个b | bb、bbb、bbbb |
7.8.1 查询以特定字符或字符串开头的记录
字符^匹配以特定字符或者字符串开头的文本。
SELECT * FROM fruits WHERE f_name ~ '^b';
f_id |s_id|f_name |f_price|
----------+----+----------------+-------+
bl | 101|blackberry | 10.20|
t1 | 102|banana | 10.30|
b2 | 104|berry | 7.60|
SELECT * FROM fruits WHERE f_name ~ '^be';
f_id |s_id|f_name |f_price|
----------+----+--------------+-------+
b2 | 104|berry | 7.60|
7.8.2 查询以特定字符或字符串结尾的记录
SELECT * FROM fruits WHERE f_name ~ 't';
f_id |s_id|f_name |f_price|
----------+----+----------------+-------+
o2 | 103|coconut | 9.20|
a2 | 103|apricot | 2.20|
m3 | 105|xxtt | 11.60|
SELECT * FROM fruits WHERE f name ~ 'rry';
f_id |s_id|f_name |f_price|
----------+----+---------------+-------+
bl | 101|blackberry | 10.20|
c0 | 101|cherry | 3.20|
b2 | 104|berry | 7.60|
7.8.3 用字符.来替代字符串中的任意一个字符
字符.匹配任意一个字符。
SELECT * FROM fruits WHERE f_name ~ 'a.g';
f_id |s_id|f_name |f_price|
----------+----+-----------------+-------+
bsl | 102|orange | 11.20|
m1 | 106|mango | 15.60|
7.8.4 使用*和+来匹配多个字符
星号*匹配前面的字符任意多次，包括0次；加号+匹配前面的字符至少一次。
SELECT * FROM fruits WHERE f_name ~ '^ba*';
f_id |s_id|f_name |f_price|
----------+----+---------------+-------+
bl | 101|blackberry | 10.20|
t1 | 102|banana | 10.30|
b2 | 104|berry | 7.60|
SELECT * FROM fruits WHERE f_name ~ '^ba+';
f_id |s_id|f_name |f_price|
----------+----+-----------------+-------+
t1 | 102|banana | 10.30|
7.8.5 匹配指定字符串
正则表达式可以匹配指定字符串，只要这个字符串在查询文本中即可，如要匹配多个字符串，多个字符串之间使用
分隔符|隔开。
SELECT * FROM fruits WHERE f_name ~ 'on';
f_id |s_id|f_name |f_price|
----------+----+----------------------+-------+
bs2 | 105|melon | 8.20|
o2 | 103|coconut | 9.20|
12 | 104|lemon | 6.40|
SELECT * FROM fruits WHERE f_name ~ 'on|ap';
f_id |s_id|f_name |f_price|
----------+----+-------------+-------+
al | 101|apple | 5.20|
bs2 | 105|melon | 8.20|
t2 | 102|grape | 5.30|
o2 | 103|coconut | 9.20|
a2 | 103|apricot | 2.20|
12 | 104|lemon | 6.40|
SELECT * FROM fruits WHERE f_name LIKE 'on';
f_id|s_id|f_name|f_price|
----+----+------+-------+
7.8.6 匹配指定字符中的任意一个
方括号[]指定一个字符集合，只匹配其中任意一个字符，即为所查找的文本。
SELECT * FROM fruits WHERE f_name ~ '[ot]';
f_id |s_id|f_name |f_price|
----------+----+------------------+-------+
bsl | 102|orange | 11.20|
bs2 | 105|melon | 8.20|
o2 | 103|coconut | 9.20|
a2 | 103|apricot | 2.20|
12 | 104|lemon | 6.40|
m1 | 106|mango | 15.60|
m3 | 105|xxtt | 11.60|
7.8.7 匹配指定字符以外的字符
[字符集合]匹配不在指定集合中的任何字符。
SELECT * FROM fruits WHERE f_id !~ '[a-e1-2]';
f_id |s_id|f_name |f_price|
----------+----+----------------+-------+
t4 | 107|xbababa | 3.60|
m3 | 105|xxtt | 11.60|
7.8.8 使用{M}或者{M,N}来指定字符串连续出现的次数
字符串{M,}表示至少匹配M次前面的字符。字符串{M,N}表示匹配前面的字符串不少于N次，不多于M次。
SELECT * FROM fruits WHERE f_name ~ 'x{2,}';
f_id |s_id|f_name |f_price|
----------+----+-------------------+-------+
m3 | 105|xxtt | 11.60|
b5 | 107|xxxx | 3.60|
SELECT * FROM fruits WHERE f_name ~ 'ba{1,3}';
f_id |s_id|f_name |f_price|
----------+----+------------------+-------+
t1 | 102|banana | 10.30|
m2 | 105|xbabay | 2.60|
t4 | 107|xbababa | 3.60|
扫一扫
598
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
