SQL Part 2 ---常用关键字

常用关键字(Keywords)

ORDER BY, DESC

• 结果根据所选择的属性进行排序, 字符串类型根据大小写排序
• 默认是升序(asceding), 使用DESC关键字可以变为降序

SELECT Y.Yelp_ID, Y.DOB as birthday
FROM YELP_USER Y
ORDER BY birthday

• 如果ORDER BY后面有多个属性，则是在保证前一个属性排序的情况下对当前属性进行排序
• 对下表进行 ORDER BY type, number

ID	type	number
4	2	3
9	0	3
3	0	2
9	0	4
5	2	4

• 结果为

ID	type	number
3	0	2
9	0	3
9	0	4
4	2	3
5	2	4

AS

元组变量 Tuple Variable

SELECT 列名
FROM 表名 AS tuble variable;

属性别名

SELECT 列名 AS 别名
FROM 表名

Example:

SELECT name
FROM Product
WHERE price > 100

# 以上的查询系统自动转换成
SELECT Product.name
FROM Product AS Product
WHERE Product.price > 100

#或者可以自定义
SELECT P.name
FROM Product AS P
WHERE Product.price > 100

Like, %, _

• 对字符串的模糊查询
• % 代表一个或多个字符
• _ 代表一个字符

SELECT B.BUSINESS_ID
FROM BUSINESS B
WHERE B.BusinessName LIKE '%Coffee%';

SELECT B.BUSINESS_ID
FROM BUSINESS B
WHERE B.BusinessName LIKE 'Coffee_';

#sailors whose names begin and end with B and contain at
#least three characters

SELECT S.age, age1=S.age-1, 2*S.age AS age2
FROM Sailors S
WHERE S.sname LIKE ‘B_%B’ //%可以表示0个字符,所以 B_%B 最少是三个字符

星号符 *

• 代表一张表的所有属性

SELECT* 
FROM (
	SELECT B.Business_ID, B.BusinessName, COUNT(B.Business_ID) as count, AVG(R.Rating)
	FROM BUSINESS B, REVIEWS R
	WHERE B.City = 'San Jose' AND B.StateName = 'CA' AND B.OPENED = 'T' AND R.Business_ID = B.Business_ID
	GROUP BY B.Business_ID, B.BusinessName
	ORDER BY count DESC
) 
WHERE ROWNUM <= 7;

DISTINCT

• 可以筛掉属性相同的记录

category	Value
A	$1600
B	$12
B	$20
C	$1

SELECT category
FROM Product

category
A
B
B
C

SELECT DISTINCT category
FROM Product

category
A
B
C

Union

Sailors

sid	sname	rating	age
22	Dustin	7	45.0
29	Brutus	1	33.0
31	Lubber	8	55.5
32	Andy	8	25.5
58	Rusty	10	35.0
64	Horatio	7	35.0

Reserves

sid	bid	day
22	101	10/10/04
22	102	10/10/04
22	103	10/08/04
22	104	10/07/04
31	102	11/10/04

Boats

bid	bname	Color
101	Interlake	blue
102	Interlake	red
103	Clipper	green
104	Marine	red

• Find the names of sailors who have reserved a red or a green boat

一般写法：

SELECT S.sname
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND (B.color=‘red’ OR B.color=‘green’);

转换成Union:

SELECT S.sname
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘red’
UNION
SELECT S.sname
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘green’;

INTERSECT

• Find the names of sailors who have reserved a red and a green boat

SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘red’
INTERSECT
SELECT S2.sid
FROM Sailors S2, Boats B2, Reserves R2
WHERE S2.sid=R2.sid AND R2.bid=B2.bid
AND B2.color=‘green’;

EXCEPT

• Find the sids of all sailors who have reserved red boats but
  not green boats:

SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’
EXCEPT
SELECT S2.sid
FROM Sailors S2, Boats B2, Reserves R2
WHERE S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color=‘green’;

嵌套查询 Nested Query ----- IN，NOT IN

Sailors

sid	sname	rating	age
22	Dustin	7	45.0
29	Brutus	1	33.0
31	Lubber	8	55.5
32	Andy	8	25.5
58	Rusty	10	35.0
64	Horatio	7	35.0

Reserves

sid	bid	day
22	101	10/10/04
22	102	10/10/04
22	103	10/08/04
22	104	10/07/04
31	102	11/10/04

Boats

bid	bname	Color
101	Interlake	blue
102	Interlake	red
103	Clipper	green
104	Marine	red

在WHERE中使用 ---- IN， NOT IN

Find names of sailors who have reserved boat 103

SELECT S.sname
FROM Sailors S
WHERE S.sid IN ( SELECT R.sid
				 FROM Reserves R
				 WHERE R.bid=103)

Find names of sailors who have not reserved boat 103

SELECT S.sname
FROM Sailors S
WHERE S.sid NOT IN ( SELECT R.sid
					 FROM Reserves R
					 WHERE R.bid=103 )

Find the names of sailors who have reserved a red boat

SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
				FROM Reserves R
				WHERE R.bid IN (SELECT B.bid
								FROM Boats B
								WHERE B.color = ‘red’));

Find names of sailors who’ve reserved a red and a green boat

SELECT S.sname FROM Sailor S
WHERE S.sid IN (SELECT R.sid
				FROM Boats B, Reserves R
				WHERE R.bid=B.bid AND B.color=‘red’
				INTERSECT
				SELECT R2.sid
				FROM Boats B2, Reserves R2
				WHERE R2.bid=B2.bid AND B2.color=‘green’);

在 FROM 中使用嵌套查询

SELECT* 
FROM (
	SELECT B.Business_ID, B.BusinessName, COUNT(B.Business_ID) as count, AVG(R.Rating)
	FROM BUSINESS B, REVIEWS R
	WHERE B.City = 'San Jose' AND B.StateName = 'CA' AND B.OPENED = 'T' AND R.Business_ID = B.Business_ID
	GROUP BY B.Business_ID, B.BusinessName
	ORDER BY count DESC
) 
WHERE ROWNUM <= 7;

ANY, ALL

• Find sailors whose rating is better than some sailor named Horatio(可能有很多个Horatio)

SELECT S.sid
FROM Sailors S
WHERE S.rating > ANY (SELECT S2.rating
					  FROM Sailors S2
					  WHERE S2.sname=‘Horatio’);

Find sailors whose rating is better than every sailor named Horatio
SELECT S.sid

FROM Sailors S
WHERE S.rating > ALL(SELECT S2.rating
					 FROM Sailors S2
					 WHERE S2.sname=‘Horatio’);

Correlation Nested Query — EXISTS and NOT EXISTS

We can make the inner subquery depend on the outer query. This is called correlation.可以使用EXISTS 和 NOT EXISTS

EXISTS and NOT EXISTS

• The EXISTS predicate is TRUE if and only if the Subquery
  returns a non-empty set.
• the NOT EXISTS predicate is TRUE if and only if the
  Subquery returns an empty set

• Find names of sailors who have reserved boat 103

SELECT S.sname
FROM Sailors S
WHERE EXISTS (SELECT * 
			  FROM Reserves R
			  WHERE R.bid=103 AND R.sid=S.sid); //注意这里是R.sid = S.sid和外层查询相关

For finding sailors who have not reserved boat 103

SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (SELECT * 
   		  FROM Reserves R
   		  WHERE R.bid=103 AND R.sid=S.sid);