SQL 练习

SQL一直不是很好，在做了一些练习后将一些有价值的记录下来

SQL 模版：

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;
1
2
3
4
5

INNER JOIN:C=A∩B 取交集

SELECT column, another_table_column, …
FROM mytable （主表）
INNER JOIN another_table （要连接的表）
    ON mytable.id = another_table.id (想象一下刚才讲的主键连接，两个相同的连成1条)
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;
1
2
3
4
5
6
7

LEFT JOIN, RIGHT JOIN, FULL JOIN和 INNER JOIN 的语法一样，区别在原理：左连接保留 A 的所有行，右连接则保留 B 的所有行，全连接则不管有没有匹配上，同时保留两者。

JOIN 优化：分为两种情况：数据规模小和数据规模大的情况，数据规模小就直接放入内存连接，数据规模大的可以通过**增加索引（最有效直接）**来优化join 语句的执行速度，可以通过缓存来减少join 的次数，尽量减少表连接的次数，一个 SQL 语句表连接的次数不应超过 5 次；在执行 join 语句的时候必然要有一个比较的过程，所以尽量将其放到内存块中来提高执行效率，以 MySQL 的 innodb 为例，可以通过调节它的内存区域：show variables like '%buffer%'可以找到一个 join_buffer_size 的变量名，它的大小决定了我们 join 语句的性能。但是有个大前提，任何项目终究要上线，而产生的数据规模不会小，大部分数据最终要保存到硬盘上，以文件的形式存储。有索引的话直接读取索引树就可以很快，而没有的话现在一般采用 block 块比较，就是取一块到内存中进行比较；

HAVING：可以对分组之后的数据再做 SELECT 查询。having 和 where 的语法一样，只不过作用的结果集不一样。在大数据时很有用。

【难题】找到还没有雇员的办公室
SELECT b.building_name FROM buildings b
left join employees e
on e.building=b.building_name 
where e.building is null;

【难题】John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以 ✓
SELECT title, (domestic_sales+international_sales)/length_minutes as sale_value FROM movies m,boxoffice b
where director="John Lasseter" and m.id = b.movie_id 
order by (domestic_sales+international_sales)/length_minutes desc
limit 3;

【难题】每栋办公室按人数排名,不要统计无办公室的雇员
SELECT building, count(building) count FROM employees 
where building is not null group by building ;

【难题】按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计） ✓
SELECT count(name) count,role,
case when building is not null then '1' else '0' end as bn
FROM employees  
group by role ,bn ;

这才是完整的SELECT查询
SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;
  
【难题】按导演分组计算销售总额,求出平均销售额冠军（统计结果过滤掉只有单部电影的导演，列出导演名，总销量，电影数量，平均销量) ✓
SELECT director,sum(domestic_sales+international_sales) sum_sale,
count(id),
sum(domestic_sales+international_sales)/count(id) avg_sale FROM movies m
left join boxoffice b
on m.id = b.movie_id
group by director
having count(id) > 1
order by avg_sale desc
limit 1;

【变态难】找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额
SELECT Title, (SELECT MAX(Domestic_sales+International_sales) 
FROM Boxoffice) - SUM(Domestic_sales+International_sales) 
AS Diff FROM Movies INNER JOIN Boxoffice 
ON Movies.ID = Boxoffice.Movie_id 
GROUP BY Title; 

# sqlzoo

# 找出所有國家,其名字以 C 作開始,ia 作結尾。
SELECT name FROM world
  WHERE name LIKE 'C%%ia'
# 找出所有國家,其名字包括三個或以上的a。
SELECT name FROM world where name like '%a%a%a%'
# 找出所有國家,其名字以t作第二個字母。
SELECT name FROM world WHERE name LIKE '_t%' ORDER BY name
# 找出所有國家,其名字都是 4 個字母的。
SELECT name FROM world WHERE name LIKE '____'
# 顯示所有國家名字,其首都是國家名字加上”City”。
SELECT name FROM world WHERE  capital = concat(name,' City')
# 找出所有首都和其國家名字,而首都要有國家名字中出現。
select capital, name from world where capital like concat('%',name,'%')
# 找出所有首都和其國家名字,而首都是國家名字的延伸。你应显示 Mexico City,因它比其國家名字 Mexico 長。你不应显示 Luxembourg,因它的首都和國家名相是相同的
select name ,capital from world where capital like concat('%',name,'_%') 

#  列出1984年的获奖者名字和奖项，按奖项和获奖者名字排序，化学奖和物理学奖放在最后;
SELECT winner, subject
  FROM nobel
 WHERE yr=1984
 ORDER BY subject IN ('physics','chemistry'),subject,winner -- 表达式subject IN ('physics','chemistry')可以作值：0 或 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

ALL 运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较，ALL 和 ANY 用法类似。语法：where 列名 比较符 ALL(子查询)

# 查找世界上最大的国家（以人口计算）
SELECT name
  FROM world
 WHERE population >= ALL(SELECT population
                           FROM world
                          WHERE population>0)
                          
# 找出比欧洲所有的国家 GDP 都高的国家
select name from world 
where gdp > ALL(select gdp from world where continent = 'Europe' and gdp > 0)
# 在每一个洲中找出面积最大的国家
select name,continent,area from world x
	where area >= ALL(select area from world y where x.continent = y.continent and area > 0)
# 列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)
select continent,name from world x 
where name <= ALL(select name from world y where x.continent= y.continent)
# 找出洲份，其中全部国家都有不大于25000000的人口，在这些洲份中列出国家名称、洲份和人口
SELECT name, continent, population
  FROM world 
WHERE continent IN (SELECT DISTINCT continent FROM world x
                     WHERE 25000000 >= (SELECT MAX(population) FROM world y
                                        WHERE x.continent = y.continent));
# 有些国家的人口是同洲份的所有其他国家的3倍或以上。列出这些国家的名称和洲份
select name,continent from world x 
where x.population/3 >=  ALL(select population from world y where x.name != y.name and population > 0 and x.continent=y.continent)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

COALESCE, CASE 和 IFNULL

# COALESCE:要替换结果集中的 null 值
SELECT 
    customerName, city, COALESCE(state, 'N/A'), -- 如果state值为null，则将其替换为'N/A'
    country
FROM
    customers;

SELECT 
    id, title, COALESCE(excerpt, LEFT(body, 150)),-- 当excerpt值为null时，将另一列（body）的值取前150个字符替换
    published_at
FROM
    articles;

# IFNULL 函数只能接受两个参数，如果不为null返回第一个参数，否则返回第二个；而 COALESE 则可以为n个，根据条件返回第一个非null值，如果参数都为null，则返回null

# CASE 和 COLESCE 相比可以实现相同功能，只是代码更多
select t.name,case when t.dept ='1' or t.dept = '2' then 'Sci'
                   when t.dept='3' then 'Art'
                   else 'None' end
 from teacher t 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

显示从Craiglockhart 到 Haymarket 的选择：

SELECT DISTINCT a.name, b.name
  FROM stops a JOIN route z ON a.id=z.stop
  JOIN route y ON y.num = z.num
  JOIN stops b ON y.stop=b.id
 WHERE a.name='Craiglockhart' AND b.name ='Haymarket'
1
2
3
4
5