SQL之查询

因为同一SQL语句，不同厂商有不同的实现方式，因此同一SQL语句不一定在所有的数据库编辑器上能够正确运行，这里采用的是华为的openGauss。

基本格式为：

SELECT	desired attributes
FROM	one or more tables
WHERE	condition about tuples of the tables

数据库的表格示例如下：

Beers(name, manf)

Bars(name, addr, license)

Drinkers(name, addr, phone)

Likes(drinker, beer)

Sells(bar, beer, price)

Frequents(drinker, bar)

如：找出HardRock酒吧卖的Bud啤酒的价格

SELECT price
FROM Sells
WHERE bar = 'HardRock' AND beer = 'Bud';

1.LIKE匹配

<Attribute> LIKE <pattern> or <Attribute> NOT LIKE <pattern>

其中pattern有

% = “any string”
_ = “any character.”

如：查询位于上海的酒吧

SELECT name
FROM Bars
WHERE addr LIKE '%上海%';

如：查询啤酒名为两个字符的啤酒

SELECT *
FROM Beers
WHERE name LIKE '__';

2.查询NULL值

对于数据库中某些值可能没有赋值，则默认为NULL，如果要查询出这些NULL值，则

如：查询喝啤酒的人的地址为NULL的人

SELECT *
FROM Drinkers
WHERE addr is NULL;

注：任何值与NULL值相比较结果都是UNKNOWN。

3.查询结果排序

排序用到ORDER BY，升序asc，降序desc，默认为升序。

如：查询3DArtBar酒吧，按照价格的降序排列

SELECT *
FROM Sells
WHERE bar='3DArtBar'
ORDER BY price DESC;  --结果按照价格降序排列

注：可以按照多个属性进行排列，即首先根据第一个属性进行排列，第一个属性一样，则按照第二个属性进行排列，以此类推。

4.逻辑与或非

1.查询Lynn Conway喜欢的啤酒并且价格大于40的啤酒

(SELECT beer FROM Likes
WHERE drinker='Lynn Conway')
INTERSECT  --与操作
(SELECT beer FROM Sells
WHERE price>40);

2.查询Lynn Conway喜欢的啤酒或价格大于40的啤酒

(SELECT beer FROM Likes
WHERE drinker='Lynn Conway')
UNION  --或操作
(SELECT beer FROM Sells
WHERE price>40);

3.查询Lynn Conway喜欢的啤酒并且价格大于40的啤酒

(SELECT beer FROM Likes
WHERE drinker='Lynn Conway')
EXCEPT  --非操作
(SELECT beer FROM Sells
WHERE price<=40);

5.子查询

如：查询被至少一个经常光顾HardRock酒吧的人喜欢的啤酒

SELECT beer
FROM Likes, (SELECT drinker
			 FROM Frequents
			 WHERE bar = 'HardRock')HD  --HD即为一个子查询
WHERE Likes.drinker = HD.drinker;

子查询除了可以放在FROM语句中，也可以放在WHERE语句中

如：查询卖Bud啤酒并且啤酒价格等于3DArtBar酒吧卖的嘉士伯啤酒的价格的酒吧

SELECT bar
FROM Sells
WHERE beer = 'Bud' AND
      price = (SELECT price
			   FROM Sells
			   WHERE bar = '3DArtBar'
			   AND beer = '嘉士伯');

6.IN

基本格式：

<tuple> IN (<subquery>)
--相反操作
<tuple> NOT IN (<subquery>)

IN语句用在WHERE语句中

如：查询Tony Hoare喜欢的啤酒的生产商

SELECT * FROM Beers
WHERE name IN (SELECT beer
  	     	   FROM Likes
	 		   WHERE drinker = 'Tony Hoare');

区别

SELECT a
FROM R, S
WHERE R.b = S.b;

SELECT a
FROM R
WHERE b IN (SELECT b FROM S);

针对以上两种查询语句的区别：查询结果都是正确的，能够得到相同的结果；主要是查询的过程的不同，第一种方式的过程为首先从R中取出一个b属性的值，然后从S中依次遍历S中的b，判断是否相等，里面共有两层循环；第二种方式的过程为首先将S中的所有b取出来构成一个元组，然后从R中取出一个b属性的值，再判断从R取出的那个b是否与这个元组中的某一个值相等，里面只有一层循环。因此第二种的效率更高一点。

7.EXISTS

基本格式为：

EXISTS(<subquery>)
--相反操作
NOT EXISTS(<subquery>)

如：查询Tony Hoare喜欢的啤酒名及其生产商

SELECT * FROM Beers
WHERE EXISTS (SELECT * 
              FROM Likes
			  WHERE drinker = 'Tony Hoare' 
       		  AND Likes.beer = Beers.name);

注意：EXISTS后跟的子查询中的SELECT语句中要查询的内容可以任意，即上例中的*号可改成drinker或者beer，都没问题，因为子查询返回的是一个bool值，即True或False。

8.ANY

基本格式为：

x = ANY(<subquery>)

如：查询和另一种啤酒在同一酒吧卖出的价格一样的啤酒

SELECT bar, beer, price FROM Sells S
WHERE price = ANY (SELECT price FROM Sells
				   WHERE S.beer <> beer 
				   AND S.bar= bar);

注意：IN和ANY可以等价。

9.ALL

基本格式为：

x <> ALL(<subquery>)

如：查询售价最高的啤酒

SELECT beer
FROM Sells
WHERE price >= ALL(SELECT price FROM Sells);

10.DISTINCT去重

因为正常的SQL查询操作的查询结果都有可能会导致一个信息出现多次，如下列操作，假设有3中以上的啤酒价格是一样的，则该价格会重复出现。

SELECT price
FROM Sells;

这是因为为了提高查询的效率，SQL默认只把符合条件的信息提取出来，而不进行去重操作，如果需要去重可以加上DISTINCT关键字。

SELECT DISTINCT price
FROM Sells;

11.JOIN

1.自然连接（natural join）

自然连接将表中具有相同名称的列自动进行匹配，自然连接不必指定任何同等连接条件也不能认为指定哪些列需要被匹配，自然连接得到的结果表中，两表中名称相同的列只出现一次。

SELECT * 
FROM Likes NATURAL JOIN Sells;

总共显示4列，相同的一列为beer。以下使用openGauss实现

2.内连接（inner join）

内连接查询能将左表和右表中能关联起来的数据连接后返回，返回的结果就是两个表中所有相匹配的数据。

SELECT * 
FROM Likes INNER JOIN sells ON likes.beer = sells.beer;

3.外连接（outer join）

内连接是要显示两张表的内存，而外连接不要求如此，外连接可以依据连接表保留左表，右表或全部表的行为而分为左外连接右外连接和全连接。

select * from TableA as A left(right/full) join TableB as B on A.PA = B.PK;

4.交叉连接（cross join）

又称笛卡尔连接，交叉连接返回两个集合的笛卡尔积。

SELECT * 
FROM Likes CROSS JOIN Sells;

总共显示5列。如下图所示，行显示的是21*50=1050行，列显示的是2+3=5列。

12.聚合函数

聚合函数有SUM、AVG、COUNT、MAX、MIN等。

1.MAX()

如：找出价格最高的啤酒

SELECT MAX(price)
FROM Sells;

2.MIN()

使用方法同上

3.AVG()

如：找出Bud啤酒的平均价格

SELECT AVG(price)
FROM Sells
WHERE beer = 'Bud';

4.COUNT()

如：查询在上海的酒吧数量

SELECT COUNT(*) 	--Bar
FROM Bars
WHERE addr LIKE '%上海%';

如：查询Bud啤酒不同价格的数量

SELECT COUNT(DISTINCT price)
FROM Sells
WHERE beer = 'Bud';

13.GROUP BY

如：查询每一种啤酒的平均价格

SELECT beer, AVG(price)
FROM Sells
GROUP BY beer;

注意：如上例所示，SELECT语句中price用了聚合函数，而beer没有用，因此必须对beer进行GROUP BY，否则会报错，即如果SELECT语句中有一个元素使用了聚合函数，则SELECT中的其他元素要么也用聚合函数，要么就放入GROUP BY语句中；上例的实现为首先对beer进行分类，然后再对每个类别中的price求平均。

HAVING

HAVING语句用在GROUP BY后面，实现对数据的筛选。

如：查询生产商为嘉士伯或者被至少三个酒吧销售的啤酒的平均价格

SELECT beer, AVG(price)
FROM Sells
GROUP BY beer
HAVING COUNT(bar) >= 3 
OR beer IN (SELECT name
		    FROM Beers
		    WHERE manf = '嘉士伯');

HAVING和WHERE的区别

作用对象不同：WHERE语句作用于表和视图；而HAVING作用于组

作用时间不同：WHERE在分组和聚集计算之前选取输入行，即它控制了哪些行进行聚集计算；而HAVING在分组和聚集计算之后选取分组的行。因此WHERE语句不能包含聚合函数，而HAVING一般总是包含聚合函数（因为如果HAVING语句中不用聚合函数，那就和WHERE函数没有区别）。