MySQL练习

练习一

表名: 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
SQL Lesson1: SELECT 查询

  1. 【简单查询】找到所有电影的名称Title

    SELECT Title FROM Movies;

  2. 【简单查询】找到所有电影的导演

    SELECT Director FROM Movies;

  3. 【简单查询】找到所有电影的名称和导演

    SELECT Title,Director FROM Movies;

  4. 【简单查询】找到所有电影的名称和上映年份

    SELECT Title,Year FROM movies;

  5. 【简单查询】找到所有电影的所有信息

    SELECT * FROM Movies;

  6. 【简单查询】找到所有电影的名称,Id和播放时长

    SELECT Title,Id,Length_minutes FROM Movies;
SQL Lesson2-3: 条件查询(constraints)

  1. 【简单条件】找到Id为6的电影

    SELECT * FROM Movies WHERE Id=6;

  2. 【简单条件】找到在2000-2010年间Year上映的电影

    SELECT * FROM Movies WHERE Year BETWEEN 2000 AND 2010;

  3. 【简单条件】找到不是在2000-2010年间Year上映的电影

    SELECT * FROM Movies WHERE Year NOT BETWEEN 2000 AND 2010;

  4. 【简单条件】找到头5部电影

    SELECT * FROM Movies WHERE Id<6;

  5. 【简单条件】找到2010（含）年之后的电影里片长小于两个小时的片子

    SELECT * FROM Movies WHERE Year>=2010 AND Length_minutes<120;

  6. 【复杂条件】找到所有Toy Story系列电影

    SELECT * FROM Movies WHERE Title LIKE 'Toy Story%';

  7. 【复杂条件】找到所有John Lasseter导演的电影

    SELECT * FROM Movies WHERE Director='John Lasseter';

  8. 【复杂条件】找到所有不是John Lasseter导演的电影

    SELECT * FROM Movies WHERE Director NOT IN ('John Lasseter');

  9. 【复杂条件】找到所有电影名为"WALL-"开头的电影

    SELECT * FROM Movies WHERE Title LIKE 'WALL-%';

  10. 【复杂条件】有一部98年电影中文名《虫虫危机》请给我找出来

    SELECT * FROM Movies WHERE Year="1998" AND Title="A Bug's Life";
SQL Lesson4: 查询结果Filtering过滤和sorting排序

  1. 【结果排序】按导演名排重列出所有电影(只显示导演)，并按导演名正序排列

    SELECT DISTINCT Director FROM Movies ORDER BY Director ASC;

  2. 【结果排序】列出按上映年份最新上线的4部电影

    SELECT DISTINCT * FROM Movies ORDER BY Year DESC LIMIT 4;

  3. 【结果排序】按电影名字母序升序排列，列出前5部电影

    SELECT * FROM Movies ORDER BY Title ASC LIMIT 5;

  4. 【结果排序】按电影名字母序升序排列，列出上一题之后的5部电影

    SELECT * FROM Movies ORDER BY Title ASC LIMIT 5 OFFSET 5;

  5. 【结果排序】如果按片长排列，John Lasseter导演导过片长第3长的电影是哪部，列出名字即可

    SELECT Title FROM Movies WHERE Director="John Lasseter" ORDER BY Length_minutes LIMIT 1 OFFSET 2;

练习二

表名: North_american_cities

小贴士?
在这个数据表中，你需要熟悉一下latitudes（纬度）和 longitudes（经度）的概念， latitudes在赤道以北是正数，以南是负数；longitudes在子午线东部是正数，以西是负数， 在查询中需要注意 经纬度和东西南北方向的对应关系。
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
SQL Lesson5: SELECT综合条件查询

  1. 【复习】列出所有加拿大人的Canadian信息(包括所有字段)

    SELECT * FROM North_american_cities WHERE Country="Canada";

  2. 【复习】列出所有在Chicago西部的城市，从西到东排序(包括所有字段)

    SELECT * FROM North_american_cities WHERE Longitude< -87.629798 ORDER BY Longitude ASC;

  3. 【复习】用人口数population排序,列出墨西哥Mexico最大的2个城市(包括所有字段)

    SELECT * FROM North_american_cities WHERE Country="Mexico" ORDER BY Population DESC LIMIT 2;

  4. 【复习】列出美国United States人口3-4位的两个城市和他们的人口(包括所有字段)

    SELECT * FROM North_american_cities WHERE Country="United States" ORDER BY Population DESC LIMIT 2 OFFSET 2;

  5. 【复习】请按国家名字母序从A-Z再按人口从多到少排列看下前10位的城市(包括所有字段)

    SELECT * FROM North_american_cities ORDER BY Country,Population DESC LIMIT 10;

练习三多表查询(内连接)

练习9也用这里的表格|ctrl+左键跳转

练习12也用这里的表格|ctrl+左键跳转

表一 : Movies (Read-Only)

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 (Read-Only)

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
SQL Lesson6: 用JOINs进行多表联合查询

  1. 【联表】找到所有电影的国内Domestic_sales和国际销售额

    SELECT * FROM Movies INNER JOIN Boxoffice ON Movies.Id=Boxoffice.Movie_id;

  2. 【联表】找到所有国际销售额比国内销售大的电影

    SELECT * FROM Movies INNER JOIN Boxoffice ON Movies.id=Boxoffice.Movie_id WHERE International_sales>Domestic_sales;

  3. 【联表】找出所有电影按市场占有率rating倒序排列

    SELECT * FROM Movies INNER JOIN Boxoffice ON Movies.Id=Boxoffice.Movie_id ORDER BY Rating DESC;

  4. 【联表】每部电影按国际销售额比较,排名最靠前的导演是谁,国际销量多少

    SELECT Director,International_sales FROM Movies INNER JOIN Boxoffice ON Movies.Id=Boxoffice.Movie_id ORDER BY International_sales DESC LIMIT 1;

练习四多表查询(外连接)

练习10-11也用这里的表格|ctrl+左键跳转

  • 左连接LEFT JOIN,右连接RIGHT JOIN 和 全连接FULL JOIN

表一 : Employees (Read-Only)

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 (Read-Only)

Building_name Capacity
1e 24
1w 32
2e 16
2w 20
SQL Lesson 7-8:外连接（OUTER JOINs）和关键字NULL
注意：因为我们这个练习DB的限制,只可以用 LEFT JOIN来解决问题.

  1. 【复习】找到所有有雇员的办公室(buildings)名字

    SELECT DISTINCT Employees.Building FROM Employees LEFT JOIN Buildings ON Employees.Building=Buildings.Building_name WHERE Building!="null";

  2. 【复习】找到所有办公室里的所有角色（包含没有雇员的）,并做唯一输出(DISTINCT)

    SELECT DISTINCT Buildings.Building_name,Employees.Role FROM Buildings LEFT JOIN Employees ON Buildings.Building_name=Employees.Building;

  3. 【难题】找到所有有雇员的办公室(buildings)和对应的容量

    SELECT DISTINCT Buildings.Building_name,Buildings.Capacity FROM Buildings LEFT JOIN Employees ON Buildings.Building_name=Employees.Building WHERE Employees.Building IS NOT NULL;

  4. 【复习】找到雇员里还没有分配办公室的(列出名字和角色就可以)

    SELECT Role,Name FROM Employees LEFT JOIN Buildings ON Employees.Building=Buildings.Building_name WHERE Employees.Building IS NULL;

  5. 【难题】找到还没有雇员的办公室

    SELECT Building_name FROM Buildings LEFT JOIN Employees ON Employees.Building=Buildings.Building_name WHERE Name IS NULL;
SQL Lesson 9: 在查询中使用表达式

Movies和Boxoffice表

  1. 【计算】列出所有的电影ID,名字和销售总额(以百万美元为单位计算)

    SELECT Movie_Id,Title,(Domestic_sales+International_sales)/1000000 AS Sales FROM Movies INNER JOIN Boxoffice ON Movies.Id=Boxoffice.Movie_id;

  2. 【计算】列出所有的电影ID,名字和市场指数(Rating的10倍为市场指数)

    SELECT Movie_id,Title,Rating*10 AS Ratings FROM Movies INNER JOIN Boxoffice ON Movies.Id=Boxoffice.Movie_id;

  3. 【计算】列出所有偶数年份的电影，需要电影ID,名字和年份

    SELECT Movie_id,Title,Year FROM Movies INNER JOIN Boxoffice ON Movies.Id=Boxoffice.Movie_id WHERE Movies.Year%2=0;

  4. 【难题】John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以

    SELECT Title,(International_sales+Domestic_sales)/Length_minutes AS cost FROM Movies INNER JOIN Boxoffice ON Movies.Id=Boxoffice.Movie_id WHERE Director="John Lasseter" ORDER BY  cost DESC LIMIT 3;
SQL Lesson 10-11: 在查询中进行统计I

Employees表

  1. 【统计】找出就职年份最高的雇员(列出雇员名字+年份）

    SELECT Name,MAX(Years_employed) FROM Employees;

  2. 【分组】按角色(Role)统计一下每个角色的平均就职年份

    SELECT Role,AVG(Years_employed) FROM Employees GROUP BY Role;

  3. 【分组】按办公室名字总计一下就职年份总和

    SELECT Building,SUM(Years_employed) FROM Employees WHERE Building IS NOT NULL GROUP BY Building;

  4. 【难题】每栋办公室按人数排名,不要统计无办公室的雇员

    SELECT Building,COUNT(Building) FROM Employees WHERE Building IS NOT NULL GROUP BY Building ORDER BY COUNT(Building) DESC;

  5. 【统计】统计一下Artist角色的雇员数量

    1.HAVING
SELECT COUNT(Role) FROM Employees GROUP BY Role HAVING Role="Artist";
2.WHERE
SELECT COUNT(Role) FROM Employees WHERE Role="Artist";

  6. 【分组】按角色统计一下每个角色的雇员数量

    SELECT Role,COUNT(Role) FROM Employees GROUP BY Role;

  7. 【分组】算出Engineer角色的就职年份总计

    SELECT SUM(Years_employed) AS Total FROM Employees WHERE Role="Engineer" GROUP BY Role;

  8. 【难题】按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计）

    SELECT Role,COUNT(),Building IS NOT NULL AS Have FROM Employees GROUP BY Role,Have;
SQL Lesson 12: 查询执行顺序
  1. FROM 和 JOINs

FROM 或 JOIN会第一个执行，确定一个整体的数据范围. 如果要JOIN不同表，可能会生成一个临时Table来用于 下面的过程。总之第一步可以简单理解为确定一个数据源表（含临时表)

  1. WHERE

我们确定了数据来源 WHERE 语句就将在这个数据源中按要求进行数据筛选，并丢弃不符合要求的数据行，所有的筛选col属性 只能来自FROM圈定的表. AS别名还不能在这个阶段使用，因为可能别名是一个还没执行的表达式

  1. GROUP BY

如果你用了 GROUP BY 分组，那GROUP BY 将对之前的数据进行分组，统计等，并将是结果集缩小为分组数.这意味着 其他的数据在分组后丢弃.

  1. HAVING

如果你用了 GROUP BY 分组, HAVING 会在分组完成后对结果集再次筛选。AS别名也不能在这个阶段使用.

  1. SELECT

确定结果之后，SELECT用来对结果col简单筛选或计算，决定输出什么数据.

  1. DISTINCT

如果数据行有重复DISTINCT 将负责排重.

  1. ORDER BY

在结果集确定的情况下，ORDER BY 对结果做排序。因为SELECT中的表达式已经执行完了。此时可以用AS别名.

  1. LIMIT / OFFSET

最后 LIMIT 和 OFFSET 从排序的结果中截取部分数据.

Movies和Boxoffice表

  1. 【复习】统计出每一个导演的电影数量（列出导演名字和数量）

    SELECT DISTINCT Director,COUNT(Title) AS Nums FROM Movies GROUP BY Director;

  2. 【复习】统计一下每个导演的销售总额(列出导演名字和销售总额)

    SELECT DISTINCT Director,SUM(Domestic_sales+International_sales) AS Sales FROM Movies INNER JOIN Boxoffice ON Movies.Id=Boxoffice.Movie_id GROUP BY Director;

  3. 【难题】按导演分组计算销售总额,求出平均销售额冠军（统计结果过滤掉只有单部电影的导演，列出导演名，总销量，电影数量，平均销量)

    SELECT Director,SUM(Domestic_sales+International_sales) AS Sales,COUNT(Title),SUM(Domestic_sales+International_sales)/COUNT(Title) AS Avges FROM Movies INNER JOIN Boxoffice ON Movies.Id=Boxoffice.Movie_id GROUP BY Director HAVING COUNT(Title)>1 ORDER BY Avges DESC LIMIT 1;

  4. 【变态难】找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额

    SELECT Title,(SELECT MAX(Domestic_sales+International_sales) FROM Boxoffice) -(Domestic_sales+International_sales) AS Gap FROM Movies INNER JOIN Boxoffice ON Movies.ID=Boxoffice.Movie_id;
posted @ 2022-01-27 22:32  灰色灰  阅读(608)  评论(0)    收藏  举报