八. 查询语句
首先定义数据表，
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)
);
为了演示如何使用SELECT语句，需要插入数据，现插入如下数据：
INSERT INTO fruits (f_id, s_id, f_name, f_price)
VALUES('a1', 101,'apple',5.2),
('b1',101,'blackberry', 10.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',104,'berry', 8.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);
8.1 单表查询
1、查询所有字段
【例8.1】从fruits表中检索所有字段的数据，SQL语句如下：
SELECT * FROM fruits;
2、查询指定字段
【例8.2】查询当前表中f_name列所有水果名称，SQL语句如下：
SELECT f_name FROM fruits;
【例8.3】例如，从fruits表中获取f_name和f_price两列，SQL语句如下：
SELECT f_name, f_price FROM fruits;
3、查询指定记录
【例8.4】查询价格为10.2元的水果的名称，SQL语句如下：
SELECT f_name, f_price
FROM fruits
WHERE f_price = 10.2;
【例8.5】查找名称为“apple”的水果的价格，SQL语句如下：
SELECT f_name, f_price
FROM fruits
WHERE f_name = 'apple';
【例8.6】查询价格小于10的水果的名称，SQL语句如下：
SELECT f_name, f_price
FROM fruits
WHERE f_price < 10;
4、带IN关键字的查询
【例8.7】查询s_id为101和102的记录，SQL语句如下：
SELECT s_id,f_name, f_price
FROM fruits
WHERE s_id IN (101,102)
ORDER BY f_name;
【例8.8】查询所有s_id不等于101也不等于102的记录，SQL语句如下：
SELECT s_id,f_name, f_price
FROM fruits
WHERE s_id NOT IN (101,102)
ORDER BY f_name;
5、带between and 的范围查询
【例8.9】查询价格在2.00元到10.5元之间水果名称和价格，SQL语句如下：
SELECT f_name, f_price
FROM fruits
WHERE f_price BETWEEN 2.00 AND 10.20;
【例8.10】查询价格在2.00元到10.5元之外的水果名称和价格，SQL语句如下：
SELECT f_name, f_price
FROM fruits
WHERE f_price NOT BETWEEN 2.00 AND 10.20;
6、带like的字符匹配查询
【例8.11】查找所有以‘b’字母开头的水果，SQL语句如下：
SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE 'b%';
【例8.12】在fruits表中，查询f_name中包含字母‘g’的记录，SQL语句如下：
SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE '%g%';
7、查询空值
【例8.13】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值，SQL语句如下：
SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NULL;
【例8.14】查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值，SQL语句如下：
SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NOT NULL;
8、带and的多条件查询
【例8.15】在fruits表中查询s_id = 101，并且f_price大于5的记录价格和名称，SQL语句如下：
SELECT s_id, f_price, f_name
FROM fruits
WHERE s_id = '101' AND f_price >=5;
【例8.16】在fruits表中查询s_id = 101或者102，并且f_price大于5，并且f_name=‘apple’的记录的价格和名称，SQL语句如下：
SELECT f_id, f_price, f_name FROM fruits
WHERE s_id IN('101', '102') AND f_price >= 5 AND f_name = 'apple';
9、带or的多条件查询
【例8.17】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：
SELECT s_id,f_name, f_price
FROM fruits
WHERE s_id = 101 OR s_id = 102;
【例8.18】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：
SELECT s_id,f_name, f_price FROM fruits WHERE s_id IN(101,102);
10、查询结果不重复
【例8.19】查询fruits表中s_id字段的值，并返回s_id字段值不得重复，SQL语句如下：
SELECT DISTINCT s_id FROM fruits;
11、对查询结果排序
【例8.20】查询fruits表的f_name字段值，并对其进行排序，SQL语句如下：
SELECT f_name FROM fruits ORDER BY f_name;
【例8.21】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序，SQL语句如下：
SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price;
【例8.22】查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序，SQL语句如下：(ASC升序排序是默认的排序方式，加不加都可以)
SELECT f_name, f_price FROM fruits ORDER BY f_price DESC;
【例8.23】查询fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL语句如下：
SELECT f_price, f_name FROM fruits ORDER BY f_price DESC, f_name;
12、分组查询
【例8.24】根据s_id对fruits表中的数据进行分组，SQL语句如下：
SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;
【例8.25】根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息，SQL语句如下：
SELECT s_id,COUNT(f_name)
FROM fruits
GROUP BY s_id HAVING COUNT(f_name)>1;
【例8.26】根据s_id对fruits表中的数据进行分组，显示水果种类大于1的分组信息，并按照水果的种类排序，SQL语句如下：
SELECT s_id,COUNT(f_name)
FROM fruits
GROUP BY s_id HAVING COUNT(f_name)>1
ORDER BY COUNT(f_name);
【例8.27】显示fruits表查询结果的前4行。
SELECT * From fruits LIMIT 4;
【例8.28】在fruits表中，使用LIMIT子句，返回从第5个记录开始的，行数长度为3的记录，SQL语句如下：
SELECT * From fruits LIMIT 3 OFFSET 4;
8.2 使用集合函数查询
1、COUNT()函数
COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回的列中包含的数据行数。其使用方法有两种：
⑴ COUNT(*) 计算表中总的行数，不管某列有数值或者为空值。
⑵ COUNT(字段名)计算指定列下总的行数，计算时将忽略字段值为空值的行。
【例8.29】查询customers表中总的行数，SQL语句如下：
SELECT COUNT(*) AS cust_num FROM customers;
【例8.30】查询customers表中有电子邮箱的顾客的总数，SQL语句如下：
SELECT COUNT(c_email) AS email_num FROM customers;
【例8.31】在fruits表中，使用COUNT()函数统计不同的s_id的水果种类，然后计算每个分组中的总记录数。
SELECT s_id,COUNT(f_name)
FROM fruits
GROUP BY s_id;
【例8.32】在fruits表中查询s_id=101的水果价格总和。SQL语句如下：
SELECT SUM(f_price) AS price_total
FROM fruits
WHERE s_id=101;
2、SUM()函数
【例8.33】在fruits表中查询不同s_id的水果价格总和。SQL语句如下：
SELECT s_id,SUM(f_price) AS price_total
FROM fruits
GROUP BY s_id;
3、AVG()函数
【例8.34】在fruits表中，查询s_id=103的供应商的水果价格的平均值，SQL语句如下：
SELECT AVG(f_price) AS avg_price
FROM fruits
WHERE s_id = 103;
【例8.35】在fruits表中，查询每一个供应商的水果价格的平均值，SQL语句如下：
SELECT s_id,AVG(f_price) AS avg_price
FROM fruits
GROUP BY s_id;
4、MAX()函数
【例8.36】在fruits表中查找市场上价格最高的水果，SQL语句如下：
SELECT MAX(f_price) AS max_price FROM fruits;
【例8.37】在fruits表中查找不同供应商提供的价格最高的水果，SQL语句如下：
SELECT s_id, MAX(f_price) AS max_price
FROM fruits
GROUP BY s_id;
【例8.38】在fruits表中查找f_name的最大值，SQL语句如下：
SELECT MAX(f_name) FROM fruits;
5、MIN()函数
【例8.39】在fruits表中查找市场上水果的最低价格，SQL语句如下：
SELECT MIN(f_price) AS min_price FROM fruits;
【例8.40】在fruits表中查找不同供应商提供的价格最低的水果，SQL语句如下：
SELECT s_id, MIN(f_price) AS min_price
FROM fruits
GROUP BY s_id;
8.3 连接查询
1、内连接查询（inner join）
【例8.41】在fruits表和suppliers表之间使用内连接查询。
查询之前，在数据库中创建数据表suppliers，SQL语句如下：
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', '*'),
(106,'Just Eat Ours', 'yunnan'),
(107,'JOTO meoukou', 'guangdong');
fruits表和suppliers表中都有相同数据类型的字段s_id，两个表通过s_id字段建立联系。接下来从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;
【例8.42】在fruits表和suppliers表之间使用INNER JOIN语法进行内连接查询，SQL语句如下：
SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits INNER JOIN suppliers
ON fruits.s_id = suppliers.s_id;
【例8.43】查询f_id=’a1’的水果供应商提供的其他水果种类，SQL语句如下：
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';
2、外连接查询
left join(左连接)：返回包括左表的所有记录和右表中连接字段相等的记录；
right join(左连接)：返回包括右表的所有记录和左表中连接字段相等的记录；
在做外连接查询的案例之前，在数据库中创建数据表orders，SQL语句如下：
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');
【例8.44】在customers表和orders表中，查询所有客户，包括没有订单的客户，SQL语句如下：
SELECT customers.c_id, orders.o_num
FROM customers LEFT OUTER JOIN orders
ON customers.c_id = orders.c_id;
【例8.45】在customers表和orders表中，查询所有订单，包括没有客户的订单，SQL语句如下：
SELECT customers.c_id, orders.o_num
FROM customers RIGHT OUTER JOIN orders
ON customers.c_id = orders.c_id;
3、复合条件连接查询
【例8.46】在customers表和orders表中，使用INNER JOIN语法查询customers表中ID为10001的客户的订单信息，SQL语句如下：
SELECT customers.c_id, orders.o_num
FROM customers INNER JOIN orders
ON customers.c_id = orders.c_id AND customers.c_id ='10001';
【例8.47】在fruits表和suppliers表之间使用INNER JOIN语法进行内连接查询，并对查询结果排序，SQL语句如下：
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;
8.4 子查询
1、带any、some关键字的子查询
下面定义两个表tb1和tb2
CREATE table tbl1 ( num1 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);
ANY关键字接在一个比较操作符的后面，表示与子查询返回的任何值比较为TRUE，则返回TRUE。
【例8.48】返回tbl2表的所有num2列，然后将tbl1中的num1的值与之进行比较，只要大于num2的任何值为符合查询条件的结果。
SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);
2、带ALL关键字的子查询
【例8.49】返回tbl1表的中比tbl2表num2 列所有值都大的值，SQL语句如下：
SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);
3、带exists关键字的子查询
【例8.50】查询表suppliers表中是否存在s_id=107的供应商，如果存在则查询fruits表中的记录，SQL语句如下：
SELECT * FROM fruits
WHERE EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);
【例8.51】查询表suppliers表中是否存在s_id=107的供应商，如果存在则查询fruits表中的f_price大于10.20的记录，SQL语句如下：
SELECT * FROM fruits
WHERE f_price>10.20 AND EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);
【例8.52】查询表suppliers表中是否存在s_id=107的供应商，如果不存在则查询fruits表中的记录，SQL语句如下：
SELECT * FROM fruits
WHERE NOT EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);
4、带in关键字的子查询
【例8.53】在customers表中查询c_name=“RedHook”的客户ID(c_id)，并根据c_id查询订单号o_num，SQL语句如下：
SELECT o_num FROM orders WHERE c_id IN
(SELECT c_id FROM customers WHERE c_name='RedHook');
【例8.54】与前一个例子语句类似，但是在SELECT语句中使用NOT IN操作符，SQL语句如下：
SELECT o_num FROM orders WHERE c_id NOT IN
(SELECT c_id FROM customers WHERE c_name='RedHook');
5、带比较运算符的子查询（>,<,<=,=,>=,!=）
【例8.55】在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有该供应商提供的水果的种类，SQL语句如下：
SELECT s_id, f_name FROM fruits
WHERE s_id =
(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');
该嵌套查询首先在suppliers表中查找s_city等于Tianjin的供应商的s_id，单独执行子查询查看s_id的值，执行下面的操作过程：
SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin';
然后在外层查询时，在fruits表中查找s_id等于101的供应商提供的水果的种类，SQL语句如下：
SELECT s_id, f_name FROM fruits
WHERE s_id =
(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');
【例8.56】在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有非该供应商提供的水果的种类，SQL语句如下：
SELECT s_id, f_name FROM fruits
WHERE s_id <>
(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');
8.5 合并查询结果
利用UNION关键字，可以给出多条select语句，并将它们的结果组合成单个结果集。
【例8.57】查询所有价格小于9的水果的信息，查询s_id等于101和103所有的水果的信息，使用UNION连接查询结果，SQL语句如下：
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将多个SELECT语句的结果组合成一个结果集合。可以分开查看每个SELECT语句的结果。第1个SELECT语句如下：
SELECT s_id, f_name, f_price FROM fruits
WHERE f_price < 9.0;
第2个SELECT语句如下：
SELECT s_id, f_name, f_price FROM fruits
WHERE s_id IN(101,103);
【例8.58】查询所有价格小于9的水果的信息，查询s_id等于101和103所有的水果的信息，使用UNION ALL连接查询结果，SQL语句如下：
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);
8.6 为表和字段取别名
1、为表取别名
【例8.59】为orders表取别名o，查询订30001订单的下单日期，SQL语句如下：
SELECT * FROM orders AS o WHERE o.o_num=30001;
【例8.60】为customers和orders表分别取别名，并进行连接查询，SQL语句如下：
SELECT c.c_id, o.o_num
FROM customers AS c LEFT OUTER JOIN orders AS o
ON c.c_id = o.c_id;
在前面介绍内连接查询时指出自连接是一种特殊的内连接，在连接查询中的两个表都是同一个表，其查询语句如下：
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';
2、为字段取别名
【例8.61】查询fruits表，为f_name取别名fruit_name，f_price取别名fruit_price，为fruits表取别名f1，查询表中f_price < 8的水果的名称，SQL语句如下：
SELECT f1.f_name AS fruit_name, f1.f_price AS fruit_price
FROM fruits AS f1
WHERE f1.f_price < 8;
【例8.62】查询suppliers表中字段s_name和s_city，使用CONCAT函数连接这个两个字段值，并取列别名为suppliers_title。
如果没有对连接后的值取别名，其显示列名称将会不够直观，SQL语句如下：
SELECT CONCAT(RTRIM(s_name) , ' (', RTRIM(s_city), ')')
FROM suppliers
ORDER BY s_name;
由结果可以看到，显示结果的列名称为SELECT子句后面的计算字段，实际上计算之后的列是没有名字的，这样的结果让人很不容易理解，如果为字段取一个别名，将会使结果清晰，SQL语句如下，
SELECT CONCAT(RTRIM(s_name) , ' (', RTRIM(s_city), ')')
AS suppliers_title
FROM suppliers
ORDER BY s_name;
8.7 使用正则表达式查询
PostgreSQL中正则表达式的操作符使用方法如下：
- ~ 匹配正则表达式，区分大小写；
- ~* 匹配正则表达式，不区分大小写；
- !~ 不匹配正则表达式，区分大小写；
- ！~* 不匹配正则表达式，不区分大小写。
1、查询以特定字符或字符串开头的记录
【例8.63】在fruits表中，查询f_name字段以字母‘b’开头的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name ~ '^b';
【例8.64】在fruits表中，查询f_name字段以“be”开头的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name ~ '^be';
2、查询以特定字符或字符串结尾的记录
【例8.65】在fruits表中，查询f_name字段以字母‘t’结尾的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name ~ 't';
fruits表中有3条记录的f_name字段值是以字母‘t’结尾，返回结果有3条记录。
【例8.66】在fruits表中，查询f_name字段以字符串“rry”结尾的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name ~ 'rry';
3、用符号“.”来替代字符串中任意一个字符
【例8.67】在fruits表中，查询f_name字段值包含字母‘a’与‘g’且两个字母之间只有一个字母的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name ~ '';
星号‘*’匹配前面的字符任意多次，包括0次。加号‘+’匹配前面的字符至少一次。
4、使用“*”或者“+”来匹配多个字符
【例8.68】在fruits表中，查询f_name字段值以字母‘b’开头，且‘b’后面出现字母‘a’的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name ~ '^ba*';
【例8.69】在fruits表中，查询f_name字段值以字母‘b’开头，且‘b’后面出现字母‘a’至少一次的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name ~ '^ba+';
5、匹配指定字符串
【例8.70】在fruits表中，查询f_name字段值包含字符串“on”的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name ~ 'on';
【例8.71】在fruits表中，查询f_name字段值包含字符串“on”或者“ap”的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name ~ 'on|ap';
【例8.72】在fruits表中，使用LIKE运算符查询f_name字段值为“on”的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name LIKE 'on';
6、匹配指定字符串的任意一个
【例8.73】在fruits表中，查找f_name字段中包含字母‘o’或者‘t’的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name ~ '[ot]';
7、匹配指定字符以外的字符
【例8.74】在fruits表中，查询f_id字段不包含字母a到e或数字1到2的字符的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_id !~ '[a-e1-2]';
8、使用{M}或者{M,N}来指定字符串连续出现的次数
【例8.75】在fruits表中，查询f_name字段值出现字母‘x’至少2次的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name ~ 'x{2,}';
【例8.76】在fruits表中，查询f_name字段值出现字符串“ba”最少1次，最多3次的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name ~ 'ba{1,3}';