PostgreSQL——数据查询

一、基本查询语句

SELECT{* | <字段列表>}[FROM <表1>, <表2>...[WHRER <表达式>][GROUP BY <broup by definition>][HAVING <expression> [{<operator> <expression>}...]][ORDER BY <order by definition>][LIMIT [<offset>,] <row count>]]
SELECT [字段1, 字段2, ..., 字段n]
FROM [表或视图]
WHERE [查询条件];

• {* |<字段列表>}包含星号通配符和选字段列表，“*”表示查询所有的字段，“字段列表”表示查询指定的字段。字段列至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不要加逗号。
• FROM<表1>，<表2>...：表1和表2表示查询数据的来源，可以是单个或者多个。
• WHERE子句是可选项，如果选择该项，[查询条件]将限定查询行必须满足的查询条件
• [GROUP BY<字段>]该子句告诉PostgreSQL如何显示查询出来的数据，并按照指定的字段分组
• [ORDER BY<字段>]该子句告诉PostgreSQL按什么样的顺序显示查询出来的数据，可以进行的排序有：升序(ASC)、降序(DESC)
• [LIMIT[<offset>,]<row count>]该子句告诉PostgreSQL每次显示查询出来的数据条数。
• SELECT的可选参数比较多

-- 准备数据
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 ('a1', 101, 'apple', 5.2),
('b1', 101, 'blackberry', 20.2),
('bs1', 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),
('l2', 104, 'lemon', 6.4),
('b2', 1.4, '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 * from fruits;

二、单表查询

2.1、查询所有字段

1. 在SELECT语句中使用星号“*”通配符查询所有字段：

select * from fruits;

2. 在SELECT语句中指定所有字段：

select f_id, s_id, f_name, f_price from fruits;

2.2、查询指定字段

1. 查询单个字段：

select f_name from fruits;

2. 查询多个字段：

select f_name, f_price from fruits;

2.3、查询指定记录

-- 语法 
select 字段名1, 字段名2, ..., 字段名n
from 表名
where 查询条件

操作符	说明
=	相等
<>, !=	不想等
<	小于
<=	小于或者等于
>	大于
>=	大于或者等于
BETWEEN	位于两值之间

-- 查询价格为10.2元的水果的名称
select f_name, f_price
from fruits
where f_price = 10.2;-- 查找名称为'apple'的水果的价格
select f_name, f_price
from fruits
where f_name = 'appale';-- 查询价格小于10的水果的名称
select f_name, f_price
from fruits
where f_price < 10;

2.4、带IN关键字

N操作符用来查询满足指定条件范围内的记录，使用N操作符时，将所有检索条件用括号括起来，检索条件用逗号分隔开，只要满足条件范围内的一个值即为匹配项。

-- 查询sid为101和102的记录
select s_id, f_name, f_price
from fruits
where s_id in (101, 102)
order by f_name;-- 查询所有sid不等于101、也不等于102的记录
select s_id, f_name, f_price
from fruits
where s_id not in (101, 102)
order by f_name;

2.5、带BETWEEN AND的范围查询

BETWEEN AND用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果记录的字段值满足指定的范围查询条件，则这些记录被返回。

-- 查询价格在2.00元到10.20元之外的水果名称和价格
select f_name, f_price
from fruits
where f_price not between 2.00 and 10.20;

2.6、带LIKE的字符匹配查询

-- 查找所有以b字母开头的水果
select f_id, f_name
from fruits
where f_name like 'b%';-- 查询f name中包含字母'g'的记录
select f_id, f_name
from fruits
where f_name like '%g%';

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 ('1001', 'RedHook', 'LMing@163.com'),
('10002', 'Stars', 'Jerry@hotmail.com'),
('10003', 'RedHook', NULL),
('10004', 'JOTO', ' sam@hotmail.com ');-- 查询customers表中c email为空的记录所对应的cid、c name和c_email字段值
select c_id, c_name, c_email 
from customers 
where c_email is null;-- 查询customers表中c email不为空的记录所对应的cid、c_name和c_email字段值
select c_id, c_name, c_email
from customers
where c_email is not null;

2.8、带AND的多条件查询

-- 在fruits表中查询sid=l0l,并且f price大于5的记录价格和名称
select s_id, f_price, f_name
from fruits
where s_id ='101' and f_price >= 5;-- 在fruits表中查询sid=101或者102，fprice大于5并且名称为apple的记录
select f_id, f_price, f_name 
from fruits
where s_id in ('101', '102') and f_price >= 5 and f_name = 'apple';

2.9、带OR的多条件查询

-- 查询sid=101或者sid-l02的水果供应商的f price和f name
select s_id, f_name, f_price
from fruits
where s_id = 101 OR s_id = 102;-- 查询sid-l01或者sid=102的水果供应商的f price和f name
select s_id, f_name, f_price 
from fruits
where s_id IN (101, 102);

2.10、查询结果不重复

-- 语法
SELECT DISTINCT 字段名
FROM 表名

-- 查询fruits表中sid字段的值，并使返回的sid字段值不得重复
select distinct s_id
from fruits

2.11、对查询结果排序

1. 单列排序：

-- 查询fruits表中的f name字段值，并对其进行排序
select f_name
from fruits
order by f_name;

2. 多列排序：

-- 查询fruits表中的fname和f price字段，先按f name排序，再按f price排序
select f_name, f_price
from fruits
order by f_name, f_price;

3. 指定排序方向：

-- 查询fruits表，先按f price降序排序，再按f name字段升序排序
select f_price, f_name
from fruits
order by f_price desc, f_name;

2.12、分组查询

-- 语法
[GROUP BY 字段] [HAVING <条件表达式>]

1. 创建分组：

GROUP BY关键字通常和集合函数一起使用，例如：MAX()、MN()、COUNT()、SUM()、AVG()。例如，要返回每个水果供应商提供的水果种类，这时就要在分组过程中用到COUNT()函数，把数据分为多个逻辑组，并对每个组进行集合计算。

-- 根据sid对fruits表中的数据进行分组
select s_id, count(*) as total
from fruits
group by s_id;

2. 使用HAVING过滤分组：

GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。

-- 根据sid对fruits表中的数据进行分组，并显示水果种类大于1的分组信息
select s_id, count(f_name)
from fruits
group by s_id 
having count(f_name) > 1;

3. GROUP BY和ORDER BY一起使用：

-- 根据sid对fruits表中的数据进行分组，显示水果种类大于1的分组信息，并按照水果种类排序
select s_id, count(f_name)
from fruits
group by s_id 
having count(f_name) > 1
order by count(f_name);

2.13、用LIMIT限制查询结果数量

SELECT将返回所有匹配的行，可能是表中所有的行，如仅仅需要返回第一行或者前几行，使用LIMIT关键字，基本语法格式如下：

LIMIT 行数 [位置偏移量,]

其中，“位置偏移量”参数指示PostgreSQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1…依次类推)：“行数”参数指示返回的记录条数。

-- 显示fruits表查询结果的前4行
select *
from fruits
limit 4;-- 在fruits表中，使用LIMIT子句，返回从第5个记录开始的，行数长度为3的记录
select *
from fruits
limit 3 offset 4;

三、集合函数查询

3.1、COUNT()函数

COUNTO函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。其使用方法有两种：

• COUNT()计算表中总的行数，不管某列是否有数值或者为空值。
• COUNT(字段名)计算指定列下总的行数，计算时将忽略字段值为空值的行。

-- 查询customers表中总的行数
select count(*) as cust_num
from customers;-- 查询customers表中有电子邮箱的顾客的总数
select count(c_email) as email_num
from customers;-- 在fruits表中，使用COUNTO函数统计不同的sid的水果种类
select s_id, count(f_name)
from fruits
group by s_id;

3.2、SUM()函数

SUMO是一个求总和的函数，返回指定列值的总和。

-- 在fruits表中查询sid=l01的水果价格总和
select sum(f_price) as price_total
from fruits
where s_id = 10;-- 在fruits表中查询不同sid的水果价格总和
select s_id, sum(f_price) as price_total
from fruits
group by s_id;

3.3、AVG()函数

AVGO函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

-- 在fruits表中，查询sid=103的供应商的水果价格的平均值
select avg(f_price) as avg_price
from fruits
where s_id = 103;-- 在fruits表中，查询每一个供应商的水果价格的平均值
select s_id, avg(f_price) as avg_price
from fruits
group by s_id;

3.4、MAX()函数

MAXO返回指定列中的最大值。

-- 在fruits表中查找市场上价格最高的水果
select max(f_price) as max_price 
from fruits;-- 在fruits表中查找不同供应商提供的价格最高的水果
select s_id, max(f_price) as max_price
from fruits
group by s_id;-- 在fruits表中查找f name的最大值
select max(f_name)
from fruits;

3.5、MIN()函数

MNO返回查询列中的最小值。

-- 在fruits表中查找市场上水果的最低价格
select min(f_price) as min_price
from fruits;-- 在ruits表中查找不同供应商提供的价格最低的水果
select s_id, min(f_price) as min_price
from fruits
group by s_id;

四、连接查询

4.1、内连接查询

内连接(NNER JOIN)使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。也就是说，在内连接查询中，只有满足条件的记录才能出现在结果列表中。

-- 准备数据
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', 'xingjiang'),
(106, 'Just Eat Ours', 'yunnan'),
(107, 'JOTO meoukou', 'guangdong');

fruits表和suppliers表中都有相同数据类型的字段sid,两个表通过sid字段建立联系。接下来从fruits表中查询f_name、f_price字段，从suppliers表中查询s_id、s_name,SQL语句如下：

select suppliers.s_id, s_name, f_name, f_price
from fruits, suppliers
where fruits.s_id = suppliers.s_id;-- 在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询
select suppliers.s_id, s_name, f_name, f_price
from fruits inner join suppliers
on fruits.s_id = suppliers.s_id;

这里的查询语句中，两个表之间的关系通过NNER JOIN指定，在使用这种语法时，连接的条件使用ON子句给出而不是WHERE,ON和WHERE后面指定的条件相同。

如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两表。

-- 查询fid='a1'的水果供应商提供的水果种类
select f1.f_id, f1.f_name
from fruits as f1, fruits as f2
where f1.s_id = f2.s_id and f2-f_id = 'a1'

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

-- 在customers表和orders表中，查询所有客户，包括没有订单的客户
select customers.c_id, orders.o_num
from customers left outer join orders
on customers.c_id = orders.c_id;

2. RIGHT JOIN右连接：

右连接是左连接的反向连接。将返回RIGHT OUTER JOIN关键字右边的表中的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值。

-- 在customer表和orders表中，查询所有订单，包括没有客户的订单
select customers.c_id, orders.o_num
from customers right outer join orders
on customers.c_id = orders.c_id;

4.3、符合条件连接查询

-- 在customers表和orders表中，使用NNER JOIN语法查询customers表中ID为'IO001'的客户订单信息
select customers.c_id, orders.o_num
from customers inner join orders
on customers.c_id = orders.c_id and customers.c_id = '10001';-- 在fruits表和suppliers表之间使用INNER JOIN语法进行内连接查询，并对查询结排序
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;

五、子查询

5.1、带ANY、SOME关键字的子查询

ANY和SOME关键字是同义词，表示满足其中任一条件。它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

-- 准备数据
CREATE TABLE tb11 
(num1 INT NOt NULL
);CREATE TABLE tb12
(num2 INT NOT NULL
);INSERT INTO tb11 values(1), (5), (13), (27);
INSERT INTO tb12 values(6), (14), (11), (20);

ANY关键字接在一个比较操作符的后面，表示与子查询返回的任何值比较为TRUE,则返回TRUE。

-- 返回tbl2表的所有num2列，然后将tbl1中的numl的值与之进行比较，只要大于num2的任何值为符合查询条件的结果。
select num1
from tb11
where num1 > any (select num2from tb12
)

5.2、带ALL关键字的子查询

ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。例如，修改前面的例子，使用ALL操作符替换ANY操作符。

ALL关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE时，则返回TRUE。

-- 返回tbl1表中比tbl2表num2列所有值都大的值
select num1
from tb11
where num1 > all (select num2from tb12
)

5.3、带EXISTS关键字的子查询

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为TRUE,此时外层查询语句将进行查询：如果子查询没有返回任何行，那么EXISTS返回的结果是FALSE,此时外层语句将不进行查询。

-- 查询suppliers表中是否存在s_id=I07的供应商，如果存在则查询fruits表中的记录
select *
from fruits
where exists (select s_namefrom supplierswhere s_id = 107
)-- 查询suppliers表中是否存在sid=lO7的供应商，如果存在则查询fruits表中f_price大于10.20的记录
select *
from fruits
where f_price > 10.20 and exists (select s_namefrom supplierswhere s_id = 107
)

NOT EXISTS与EXISTS使用方法相同，返回的结果相反。子查询如果至少返回一行，那么NOT EXISTS的结果为FALSE,此时外层查询语句将不进行查询：如果子查询没有返回任何行，那么NOT EXISTS返回的结果是TRUE,此时外层语句将进行查询。

-- 查询suppliers表中是否存在sid=l07的供应商，如果不存在则查询fruits表中的记录
select * 
from fruits
where not exists (select s_name from supplierswhere s_id = 107
)

5.4、带IN关键字的子查询

N关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。

-- 在customers表中查询c name-RedHook'的客户ID(cid),，并根据cid查询订单号o_num
select o_num
from orders
where c_id in (select c_idfrom customerswhere c_name = 'RedHook'
)

SELECT语句中可以使用NOT IN关键字，其作用与IN正好相反。

-- 与前一个例子类似，只是在SELECT语句中使用NOTN操作符
select o_num
from orders
where c_id not in (select c_idfrom customerswhere c_name = 'RedHook'
)

5.5、带比较运算符的子查询

-- 在suppliers表中查询s city等于Tianjin'的供应商sid,然后在fruits表中查询所有该供应商提供的水果的种类
select s_id, f_name
from fruits
where s_id = (select s1.s_idfrom suppliers as s1where s1.s_city = 'Tianjin'
)

-- 在suppliers表中查询s city等于Tianjin'的供应商sid,然后在fruits表中查询所有非该供应商提供的水果的种类
select s_id, f_name
from fruits
where s_id != (select s1.s_idfrom suppliers as s1where s1.s_city = 'Tianjin'
)

5.6、合并查询结果

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

• UNION不使用关键字ALL,执行的时候删除重复的记录，所有返回的行都是唯一的
• 使用关键字ALL的作用是不删除重复行、也不对结果进行自动排序。基本语法格式如下：

SELECT column, ... FROM table1
UNION [ALL]
SELECT column, ... FROM table2

-- 查询所有价格小于9的水果的信息，查询sid等于101和103的所有水果的信息，使用UNION连接查询结果
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);

使用UNION ALL包含重复的行，前面的例子中，分开查询时，两个返回结果中有相同的记录，UNION从查询结果集中自动去除了重复的行：如果要返回所有匹配行，则不进行删除，可以使用UNION ALL。

-- 查询所有价格小于9的水果的信息，查询sid等于101和103的所有水果的信息，使用UNION ALL连接查询结果
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);

5.7、为表和字段取别名

5.7.1、为表取别名

当表名字很长或者执行一些特殊查询时，为了方便操作或者需要多次使用相同的表时，可以为表指定别名，用这个别名替代表原来的名称。为表取别名的基本语法格式为：

表名 [AS] 表别名

-- 为orders表取别名o,查询30001订单的下单日期
select *
from orders as o
where o.o_num = 30001-- 为customers表和orders表分别取别名，并进行连接查询
select c.c_id, o,o_num
from customers as c left outer join orders as o
on c.c_id = o.c_id

5.6.2、为字段取别名

使用SELECT语句显示查询结果时，PostgreSQL会显示每个SELECT后面指定的输出列，在有些情况下，显示的列的名称会很长或者名称不够直观，PostgreSQL可以指定列别名、替换字段或表达式。为字段取别名的基本语法格式如下：

列名 [AS] 列别名

-- 查询fruits表，为f name取别名fruit name,f price取别名fruit price,为fruits表取别名fI,查询表中f price<8的水果的名称
select f1.f_name as fruit_name, f1.f_price as fruit_price
from fruits as f1
where f1.f_price < 8

-- 查询suppliers表中字段s name和sciy,使用CONCAT函数连接这两个字段值，并取列别名为suppliers_title。
select concat(rtrim(s_name), ' (', rtrim(s_city), ')')
from suppliers
order by s_name

5.8、正则表达式查询

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，可以应用于非常复杂的查询。

PostgreSQL中正则表达式的操作符使用方法如下：

• ~：匹配正则表达式，区分大小写
• ~*：匹配正则表达式，不分区大小写
• !~：不匹配正则表达式，区分大小写
• !~*：不匹配正则表达式，不分区大小写

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	^b匹配以字母b开头的字符串	book、big、bannana、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、bbbbbbbb
字符串{n,m}	匹配前面的字符串至少n次，至多m次。如果n为0，此参数为可选参数	b{2,4}匹配最少两个，最多4geb	bb、bbb、bbbb

5.8.1、查询以特定字符或字符串开头的记录

-- 在fruits表中，查询f name字段以字母b'开头的记录
select *
from fruits
where f_name ~ '^b';-- 在fruits表中，查询f name字段以'be'开头的记录
select *
from fruits
where f_name ~ '^be';

5.8.2、查询以特定字符或字符串结尾的记录

-- 在fruits表中，查询f name字段以字母t'结尾的记录
select *
from fruits
where f_name ~ 't';-- 在fruits表中，查询f name字段以字符串'rry结尾的记录
select *
from fruits
where f_name ~ 'rry';

5.8.3、用字符.来替代字符串中任意一个字符

-- 在fruits表中，查询f name字段值包含字母'a与'g且两个字母之间只有一个字母的
select *
from fruits
where f_name ~ 'a.g';

5.8.4、使用*和+来匹配多个字符

-- 在fruits表中，查询f name字段值以字母b'开头，且b后面出现字母'a'任意次的记录
select *
from fruits
where f_name ~ '^ba*';-- 在fruits表中，查询f name字段值以字母b'开头，且b后面出现字母'a'至少一次的记录
select *
from fruits
where f_name ~ '^ba+';

5.8.5、匹配指定字符串

-- 在fruits表中，查询f_name字段值包含字符串'on'的记录
select *
from fruits
where f_name ~ 'on';-- 在fruits表中，查询f name字段值包含字符串'on'或者'ap的记录
select *
from fruits
where f_name ~ 'on|ap';

5.8.6、匹配指定字符中的任意一个

-- 在fruits表中，查找f_name字段中包含字母o'或者t的记录
select *
from fruits
where f_name ~ '[ot]'

5.8.7、匹配指定字符以外的字符

-- 在fruits表中，查询fid字段不包含字母a~e或数字1~2的字符的记录
select *
from fruits
where f_id !~ '[a-e1-2]'

5.8.8、匹配字符串出现次数

-- 在fruits表中，查询f_name字段值出现字母'x'至少2次的记录
select *
from fruits
where f_name ~ 'x{2,}'-- 在fruits表中，查询f name字段值出现字符串ba最少1次，最多3次的记录
select *
from fruits
where f_name ~ 'ba{1,3}'

六、案例

准备数据：

create table dept(d_no int not null primary key,d_name varchar(50),d_location varchar(100)
);create table employee(e_no int not null primary key,e_name varchar(50) not null,e_gender char(2) not null,dept_no int not null,e_job varchar(50) not null,e_salary smallint not null,hireDate date,constraint dno_fk foreign key(dept_no) references dept(d_no)
);INSERT INTO dept
VALUES (10,'ACCOUNTING','ShangHai'),
(20,'RESEARCH','BeiJing'),
(30,'SALES ','ShenZhen '),
(40,'OPERATIONS ', 'FuJian ');INSERT
INTO employee
VALUES (1001,'SMITH','m',20,'CLERK',800,'2005-11-12'),
(1002,'ALLEN','f',30,'SALESMAN',1600,'2003-05-12'),
(1003,'WARD','f',30,'SALESMAN',1250,'2003-05-12'),
(1004,'JONES','m',20,'MANAGER',2975,'1998-05-18'),
(1005,'MARTIN','m',30,'SALESMAN',1250,'2001-06-12'),
(1006,'BLAKE','E',30,'MANAGER',2850,'1997-02-15'),
(1007,'CLARK','m',10,'MANAGER',2450,'2002-09-12'),
(1008,'SCOTT','m',20,'ANALYST',3000,'2003-05-12'),
(1009,'KING','f',10,'PRESIDENT',5000,'1995-01-01'),
(1010,'TURNER','f',30,'SALESMAN',1500,'1997-10-12'),
(1011,'ADAMS','m',20,'CLERK',1100,'1999-10-05'),
(1012,'JAMES','m',30,'CLERK',950,'2008-06-15');

在employee表中，查询所有记录的e_no、e_name和e_salary字段值：

select e_no, e_name, e_salary
from employee;

在employee表中，查询dept_no等于10和20的所有记录：

select *
from employee
where dept_no in (10, 20);

在employee表中，查询工资范围在800到2500之间的员工信息：

select *
from employee
where e_salary between 800 and 2500;

在employee表中，查询部门编号为20的部门中的员工信息：

select *
from employee
where dept_no = 20;

在employee表中，查询每个部门最高工资的员工信息：

select dept_no, max(e_salary)
from employee
group by dept_no;

查询员工BLAKE所在部门和部门所在地：

select d_no, d_location
from dept 
where d_no = (select dept_nofrom employeewhere e_name = 'BLAKE'
);

使用连接查询，查询所有员工的部门和部门信息：

select e_no, e_name, dept_no, d_name, d_location
from employee, dept
where dept.d_no = employee.dept_no;

在employee表中，计算每个部门各有多少名员工：

select dept_no, count(*)
from employee
group by dept_no;

在employee表中，计算不同类型职工的总工资数：

select e_job, sum(e_salary)
from employee
group by e_job;

在employee表中，计算不同部门的平均工资：

select * 
from employee
where e_salary < 1500;

在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列：

select e_name, dept_no, e_salary
from employee
order by dept_no desc, e_salary desc;

在employee表中，查询员工姓名以字母A或S开头的员工信息：

select *
from employee
where e_name ~ '^[AS]';