条件查询
查询movies表
movies表中的数据
Id | Title | Director | Year | Length_minutes |
---|---|---|---|---|
1 | Toy Story | John Lasseter | 1995 | 81 |
2 | A Bug’s Life | John Lasseter | 1998 | 95 |
3 | Toy Story 2 | John Lasseter | 1999 | 93 |
4 | Monsters, Inc. | Pete Docter | 2001 | 92 |
5 | Finding Nemo | Finding Nemo | 2003 | 107 |
6 | The Incredibles | Brad Bird | 2004 | 116 |
7 | Cars | John Lasseter | 2006 | 117 |
8 | Ratatouille | Brad Bird | 2007 | 115 |
9 | WALL-E | Andrew Stanton | 2008 | 104 |
10 | Up | Pete Docter | 2009 | 101 |
11 | Toy Story 3 | Lee Unkrich | 2010 | 103 |
12 | Cars 2 | John Lasseter | 2011 | 120 |
13 | Brave | Brenda Chapman | 2012 | 102 |
14 | Monsters University | Dan Scanlon | 2013 | 110 |
part1
找到所有电影的名称title
select title
from movies;
找到所有电影的导演
select director
from movies;
找到所有电影的名称和导演
select title,director
from movies;
找到所有电影的名称和上映年份
select title,year
from movies;
找到所有电影的所有信息
select *
from movies;
找到所有电影的名称,Id和播放时长
select title,id,length_minutes
from movies;
part2
找到id
为6的电影
select *
from movies
where id = 6;
找到在2000-2010年间year
上映的电影
select *
from movies
where year >= 2000 and year <=2010;
找到不是在2000-2010年间year
上映的电影
select *
from movies
where year < 2000 or year > 2010;
找到头5部电影
select *
from movies
limit 5 ;
找到2010(含)年之后的电影里片长小于两个小时的片子
select *
from movies
where year >= 2010 and length_minutes < 120;
part3
找到所有Toy Story
系列电影
select *
from movies
where title like '%Toy Story%';
找到所有John Lasseter
导演的电影
select *
from movies
where director like '%John Lasseter%';
找到所有不是John Lasseter
导演的电影
select *
from movies
where director not like '%John Lasseter%';
找到所有电影名为"WALL-"
开头的电影
select *
from movies
where title like 'WALL-%';
有一部98年电影中文名《虫虫危机》请给我找出来
select *
from movies
where year = 1998;
part4
按导演名排重
列出所有电影(只显示导演),并按导演名正序排列
select distinct(director)
from movies
order by director
列出按上映年份最新
上线的4部电影
select *
from movies
order by year desc
limit 4;
按电影名字母序升序
排列,列出前5部电影
select *
from movies
order by title
limit 5;
按电影名字母序升序排列,列出上一题之后
的5部电影
select *
from movies
order by title
limit 5,5;
如果按片长排列,John Lasseter导演导过片长第3长的电影是哪部,列出名字即可
select title
from movies
where director like 'John Lasseter'
order by length_minutes desc
limit 2,1;
part5
下面在North_american_cities表中查询
City | Country | Population | Latitude | Longitude |
---|---|---|---|---|
Guadalajara | Mexico | 1500800 | 20.659699 | -103.349609 |
Toronto | Canada | 2795060 | 43.653226 | -79.383184 |
Houston | United States | 2195914 | 29.760427 | -95.369803 |
New York | United States | 8405837 | 40.712784 | -74.005941 |
Philadelphia | United States | 1553165 | 39.952584 | -75.165222 |
Havana | Cuba | 2106146 | 23.05407 | -82.345189 |
Mexico City | Mexico | 8555500 | 19.432608 | -99.133208 |
Phoenix | United States | 1513367 | 33.448377 | -112.074037 |
Los Angeles | United States | 3884307 | 34.052234 | -118.243685 |
Ecatepec de Morelos | Mexico | 1742000 | 19.601841 | -99.050674 |
Montreal | Canada | 1717767 | 45.501689 | -73.567256 |
Chicago | United States | 2718782 | 41.878114 | -87.629798 |
提示:在这个数据表中,你需要熟悉一下latitudes(纬度)和 longitudes(经度)的概念, latitudes在赤道以北是正数,以南是负数;longitudes在子午线东部是正数,以西是负数, 在查询中需要注意 经纬度和东西南北方向的对应关系。
列出所有加拿大人的Canadian
信息(包括所有字段)
select *
from north_american_cities
where country like 'Canada';
列出所有在Chicago
西部的城市,从西到东排序(包括所有字段)
select *
from north_american_cities
where longitude < (
select Longitude
from north_american_cities
where city like 'Chicago')
order by longitude;
用人口数population
排序,列出墨西哥Mexico
最大的2个城市(包括所有字段)
select *
from north_american_cities
where country like 'Mexico'
order by population desc
limit 2;
列出美国United States
人口3-4位的两个城市和他们的人口(包括所有字段)
select *
from north_american_cities
where country like 'United States'
order by population desc
limit 2,2;
多表查询
part1(内连接)
下面用到Movies和Boxoffice表查询
Movies表
Id | Title | Director | Year | Length_minutes |
---|---|---|---|---|
1 | Toy Story | John Lasseter | 1995 | 81 |
2 | A Bug’s Life | John Lasseter | 1998 | 95 |
3 | Toy Story 2 | John Lasseter | 1999 | 93 |
4 | Monsters, Inc. | Pete Docter | 2001 | 92 |
5 | Finding Nemo | Finding Nemo | 2003 | 107 |
6 | The Incredibles | Brad Bird | 2004 | 116 |
7 | Cars | John Lasseter | 2006 | 117 |
8 | Ratatouille | Brad Bird | 2007 | 115 |
9 | WALL-E | Andrew Stanton | 2008 | 104 |
10 | Up | Pete Docter | 2009 | 101 |
11 | Toy Story 3 | Lee Unkrich | 2010 | 103 |
12 | Cars 2 | John Lasseter | 2011 | 120 |
13 | Brave | Brenda Chapman | 2012 | 102 |
14 | Monsters University | Dan Scanlon | 2013 | 110 |
Boxoffice表
Movie_id | Rating | Domestic_sales | International_sales |
---|---|---|---|
5 | 8.2 | 380843261 | 555900000 |
14 | 7.4 | 268492764 | 475066843 |
8 | 8 | 206445654 | 417277164 |
12 | 6.4 | 191452396 | 368400000 |
3 | 7.9 | 245852179 | 239163000 |
6 | 8 | 261441092 | 370001000 |
9 | 8.5 | 223808164 | 297503696 |
11 | 8.4 | 415004880 | 648167031 |
1 | 8.3 | 191796233 | 170162503 |
7 | 7.2 | 244082982 | 217900167 |
10 | 8.3 | 293004164 | 438338580 |
4 | 8.1 | 289916256 | 272900000 |
2 | 7.2 | 162798565 | 200600000 |
13 | 7.2 | 237283207 | 301700000 |
找到所有电影的国内Domestic_sales
和国际销售额
select *
from movies
inner join Boxoffice
on id = movie_id;
找到所有国际销售额比国内销售大的电影
SELECT *
from movies
inner join Boxoffice
on id = movie_id
where International_sales > Domestic_sales;
找出所有电影按市场占有率rating
倒序排列
SELECT *
from movies
inner join Boxoffice
on id = movie_id
order by rating desc;
每部电影按国际销售额比较,排名最靠前的导演是谁,国际销量多少
SELECT director,International_sales
from movies
inner join Boxoffice
on id = movie_id
order by International_sales desc
limit 1;
part2(外连接)
下面用两个雇员表 Employees 和 办公室表 Buildings.
Employees表
Role | Name | Building | Years_employed |
---|---|---|---|
Engineer | Becky A. | 1e | 4 |
Engineer | Dan B. | 1e | 2 |
Engineer | Sharon F. | 1e | 6 |
Engineer | Dan M. | 1e | 4 |
Engineer | Malcom S. | 1e | 1 |
Artist | Tylar S. | 2w | 2 |
Artist | Sherman D. | 2w | 8 |
Artist | Jakob J. | 2w | 6 |
Artist | Lillia A. | 2w | 7 |
Artist | Brandon J. | 2w | 7 |
Manager | Scott K. | 1e | 9 |
Manager | Shirlee M. | 1e | 3 |
Manager | Daria O. | 2w | 6 |
Engineer | Yancy I. | null | 0 |
Artist | Oliver P. | null | 0 |
Buildings表
Building_name | Capacity |
---|---|
1e | 24 |
1w | 32 |
2e | 16 |
2w | 20 |
找到所有有雇员的办公室(buildings
)名字
select distinct(building)
from employees
left join buildings
on building = building_name
where building is not null;
找到所有办公室里的所有角色(包含没有雇员的),并做唯一输出(DISTINCT
)
select distinct(building_name),role
from buildings
left join employees
on building = building_name;
找到所有有雇员的办公室(buildings
)和对应的容量
select distinct(building_name),capacity
from employees
left join buildings
on building = building_name
where building is not null;
找到雇员里还没有分配办公室的(列出名字和角色就可以)
select name,role
from employees
where building is null;
找到还没有雇员的办公室
select building_name
from buildings
left join employees
on building = building_name
where building is null
函数
part1
下面用到movies表和boxoffice表
movies表
Id | Title | Director | Year | Length_minutes |
---|---|---|---|---|
1 | Toy Story | John Lasseter | 1995 | 81 |
2 | A Bug’s Life | John Lasseter | 1998 | 95 |
3 | Toy Story 2 | John Lasseter | 1999 | 93 |
4 | Monsters, Inc. | Pete Docter | 2001 | 92 |
5 | Finding Nemo | Finding Nemo | 2003 | 107 |
6 | The Incredibles | Brad Bird | 2004 | 116 |
7 | Cars | John Lasseter | 2006 | 117 |
8 | Ratatouille | Brad Bird | 2007 | 115 |
9 | WALL-E | Andrew Stanton | 2008 | 104 |
10 | Up | Pete Docter | 2009 | 101 |
11 | Toy Story 3 | Lee Unkrich | 2010 | 103 |
12 | Cars 2 | John Lasseter | 2011 | 120 |
13 | Brave | Brenda Chapman | 2012 | 102 |
14 | Monsters University | Dan Scanlon | 2013 | 110 |
boxoffice表
Movie_id | Rating | Domestic_sales | International_sales |
---|---|---|---|
5 | 8.2 | 380843261 | 555900000 |
14 | 7.4 | 268492764 | 475066843 |
8 | 8 | 206445654 | 417277164 |
12 | 6.4 | 191452396 | 368400000 |
3 | 7.9 | 245852179 | 239163000 |
6 | 8 | 261441092 | 370001000 |
9 | 8.5 | 223808164 | 297503696 |
11 | 8.4 | 415004880 | 648167031 |
1 | 8.3 | 191796233 | 170162503 |
7 | 7.2 | 244082982 | 217900167 |
10 | 8.3 | 293004164 | 438338580 |
4 | 8.1 | 289916256 | 272900000 |
2 | 7.2 | 162798565 | 200600000 |
13 | 7.2 | 237283207 | 301700000 |
列出所有的电影ID,名字和销售总额(以百万美元为单位计算)
select id,title,(domestic_sales+international_sales)/1000000 as sum
from movies
left join boxoffice
on id = movie_id
列出所有的电影ID,名字和市场指数(Rating
的10倍为市场指数)
select id,title,rating*10
from movies
left join boxoffice
on id = movie_id
列出所有偶数年份的电影,需要电影ID,名字和年份
select id,title,year
from movies
where year%2 = 0
John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以
select title,
(domestic_sales+international_sales)/Length_minutes value
from movies
left join boxoffice
on id = movie_id
where director like 'John Lasseter'
order by value desc
limit 3;
part2
下面练习基于Employees表
Role | Name | Building | Years_employed |
---|---|---|---|
Engineer | Becky A. | 1e | 4 |
Engineer | Dan B. | 1e | 2 |
Engineer | Sharon F. | 1e | 6 |
Engineer | Dan M. | 1e | 4 |
Engineer | Malcom S. | 1e | 1 |
Artist | Tylar S. | 2w | 2 |
Artist | Sherman D. | 2w | 8 |
Artist | Jakob J. | 2w | 6 |
Artist | Lillia A. | 2w | 7 |
Artist | Brandon J. | 2w | 7 |
Manager | Scott K. | 1e | 9 |
Manager | Shirlee M. | 1e | 3 |
Manager | Daria O. | 2w | 6 |
Engineer | Yancy I. | null | 0 |
Artist | Oliver P. | null | 0 |
找出就职年份最高的雇员(列出雇员名字+年份)
SELECT name,max(years_employed)
FROM employees;
按角色(Role
)统计一下每个角色的平均就职年份
SELECT role,avg(years_employed)
FROM employees
GROUP BY role;
按办公室名字总计一下就职年份总和
SELECT building,sum(years_employed)
FROM employees
GROUP BY building;
每栋办公室按人数排名,不要统计无办公室的雇员
SELECT building,COUNT(name) count
FROM employees
WHERE building IS NOT null
GROUP BY building
ORDER BY count desc;
part3
下面练习基于Employees表
Role | Name | Building | Years_employed |
---|---|---|---|
Engineer | Becky A. | 1e | 4 |
Engineer | Dan B. | 1e | 2 |
Engineer | Sharon F. | 1e | 6 |
Engineer | Dan M. | 1e | 4 |
Engineer | Malcom S. | 1e | 1 |
Artist | Tylar S. | 2w | 2 |
Artist | Sherman D. | 2w | 8 |
Artist | Jakob J. | 2w | 6 |
Artist | Lillia A. | 2w | 7 |
Artist | Brandon J. | 2w | 7 |
Manager | Scott K. | 1e | 9 |
Manager | Shirlee M. | 1e | 3 |
Manager | Daria O. | 2w | 6 |
Engineer | Yancy I. | null | 0 |
Artist | Oliver P. | null | 0 |
统计一下Artist角色的雇员数量
SELECT count(*)
FROM employees
GROUP BY role
HAVING role like 'Artist';
按角色统计一下每个角色的雇员数量
SELECT role,count(*)
FROM employees
GROUP BY role;
算出Engineer角色的就职年份总计
SELECT sum(years_employed)
FROM employees
GROUP BY role
HAVING role like 'Engineer';
按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色,数量,有无办公室,注意一个角色如果部分有办公室,部分没有需分开统计)
SELECT COUNT(*),
role,
CASE WHEN building IS null THEN 0 ELSE 1 END as rn
FROM employees
GROUP BY role,rn
ORDER BY role,rn;
part4
这是 SELECT
查询的最后一部分,基于movies表和boxoffice表查询
movies表
Id | Title | Director | Year | Length_minutes |
---|---|---|---|---|
1 | Toy Story | John Lasseter | 1995 | 81 |
2 | A Bug’s Life | John Lasseter | 1998 | 95 |
3 | Toy Story 2 | John Lasseter | 1999 | 93 |
4 | Monsters, Inc. | Pete Docter | 2001 | 92 |
5 | Finding Nemo | Finding Nemo | 2003 | 107 |
6 | The Incredibles | Brad Bird | 2004 | 116 |
7 | Cars | John Lasseter | 2006 | 117 |
8 | Ratatouille | Brad Bird | 2007 | 115 |
9 | WALL-E | Andrew Stanton | 2008 | 104 |
10 | Up | Pete Docter | 2009 | 101 |
11 | Toy Story 3 | Lee Unkrich | 2010 | 103 |
12 | Cars 2 | John Lasseter | 2011 | 120 |
13 | Brave | Brenda Chapman | 2012 | 102 |
14 | Monsters University | Dan Scanlon | 2013 | 110 |
boxoffice表
Movie_id | Rating | Domestic_sales | International_sales |
---|---|---|---|
5 | 8.2 | 380843261 | 555900000 |
14 | 7.4 | 268492764 | 475066843 |
8 | 8 | 206445654 | 417277164 |
12 | 6.4 | 191452396 | 368400000 |
3 | 7.9 | 245852179 | 239163000 |
6 | 8 | 261441092 | 370001000 |
9 | 8.5 | 223808164 | 297503696 |
11 | 8.4 | 415004880 | 648167031 |
1 | 8.3 | 191796233 | 170162503 |
7 | 7.2 | 244082982 | 217900167 |
10 | 8.3 | 293004164 | 438338580 |
4 | 8.1 | 289916256 | 272900000 |
2 | 7.2 | 162798565 | 200600000 |
13 | 7.2 | 237283207 | 301700000 |
统计出每一个导演的电影数量(列出导演名字和数量)
SELECT director,count(title)
FROM movies
GROUP BY director;
统计一下每个导演的销售总额(列出导演名字和销售总额)
SELECT director,sum(Domestic_sales+International_sales) as sum
FROM movies
LEFT JOIN boxoffice
on id = movie_id
GROUP BY director;
按导演分组计算销售总额,求出平均销售额冠军(统计结果过滤掉只有单部电影的导演,列出导演名,总销量,电影数量,平均销量)
SELECT director,
sum(Domestic_sales+International_sales) as sum,
count(title),
avg(Domestic_sales+International_sales) as avg
FROM movies
LEFT JOIN boxoffice
on id = movie_id
GROUP BY director
HAVING COUNT(title) > 1
ORDER BY avg DESC
LIMIT 1;
找出每部电影和单部电影销售冠军之间的销售差,列出电影名,销售额差额
WITH topmovie AS (
select movie_id,Domestic_sales+International_sales as topsale
FROM boxoffice
ORDER BY topsale DESC
limit 1
),
moviesales AS (
select m.title,b.Domestic_sales+b.International_sales as totalsales
FROM movies m
LEFT JOIN boxoffice b
on id = movie_id
)
SELECT ms.title,tm.topsale-ms.totalsales
FROM moviesales ms
JOIN topmovie tm;