sql_select_queries实操

Lesson 1

基本语法

1
select col,another col from tables;

练习

idtitledirectoryearlength_minutes
1Toy StoryJohn Lasseter199581
2A Bug’s LifeJohn Lasseter199895
3Toy Story 2John Lasseter199993
4Monsters, Inc.Pete Docter200192
5Finding NemoAndrew Stanton2003107
6The IncrediblesBrad Bird2004116
7CarsJohn Lasseter2006117
8RatatouilleBrad Bird2007115
9WALL-EAndrew Stanton2008104
10UpPete Docter2009101
11Toy Story 3Lee Unkrich2010103
12Cars 2John Lasseter2011120
13BraveBrenda Chapman2012102
14Monsters UniversityDan Scanlon2013110

1、Find the title of each film

1
SELECT title FROM movies;

2、Find the director of each film

1
SELECT director FROM movies;

3、Find the title and director of each film

1
SELECT title,director FROM movies;

4、Find the title and year of each film

1
SELECT title,year FROM movies;

5、Find all the information about each film

1
SELECT * FROM movies;

Lesson 2

基本语法

1
2
select col from tables
where some conditions;

练习

idtitledirectoryearlength_minutes
1Toy StoryJohn Lasseter199581
2A Bug’s LifeJohn Lasseter199895
3Toy Story 2John Lasseter199993
4Monsters, Inc.Pete Docter200192
5Finding NemoAndrew Stanton2003107
6The IncrediblesBrad Bird2004116
7CarsJohn Lasseter2006117
8RatatouilleBrad Bird2007115
9WALL-EAndrew Stanton2008104
10UpPete Docter2009101
11Toy Story 3Lee Unkrich2010103
12Cars 2John Lasseter2011120
13BraveBrenda Chapman2012102
14Monsters UniversityDan Scanlon2013110

1、Find the movie with a row id of 6

1
SELECT * FROM movies where id=6;

2、Find the movies released in the years between 2000 and 2010\

1
SELECT * FROM movies where year between 2000 and 2010;

3、Find the movies not released in the years between 2000 and 2010

1
SELECT * FROM movies where year not between 2000 and 2010;

4、Find the first 5 Pixar movies and their release year

1
SELECT * FROM movies where id in (1,2,3,4,5);

Lesson 3

练习



idtitledirectoryearlength_minutes
1Toy StoryJohn Lasseter199581
2A Bug’s LifeJohn Lasseter199895
3Toy Story 2John Lasseter199993
4Monsters, Inc.Pete Docter200192
5Finding NemoAndrew Stanton2003107
6The IncrediblesBrad Bird2004116
7CarsJohn Lasseter2006117
8RatatouilleBrad Bird2007115
9WALL-EAndrew Stanton2008104
10UpPete Docter2009101
11Toy Story 3Lee Unkrich2010103
12Cars 2John Lasseter2011120
13BraveBrenda Chapman2012102
14Monsters UniversityDan Scanlon2013110
87WALL-GBrenda Chapman204297

1、Find all the Toy Story movies

1
SELECT * FROM movies where title like "%Toy Story%";

2、Find all the movies directed by John Lasseter

1
SELECT * FROM movies where director = "John Lasseter";

3、Find all the movies (and director) not directed by John Lasseter

1
SELECT * FROM movies where director != "John Lasseter";

4、Find all the WALL-* movies

1
SELECT * FROM movies where title like "%WALL-%";

Lesson 4

基本语法

1
2
3
4
5
6
7
8
9
10
11
SELECT DISTINCT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;

//DISTINCT 在查询结果中删除重复的行
//ASC 升序
//DESC 降序
//limit 表示限制查询后显示的数量
//offset 表示从哪一行开始计数

练习

idtitledirectoryearlength_minutes
1Toy Story 2John Lasseter199993
2RatatouilleBrad Bird2007115
3Toy StoryJohn Lasseter199581
4WALL-EAndrew Stanton2008104
5The IncrediblesBrad Bird2004116
6Monsters UniversityDan Scanlon2013110
7Finding NemoAndrew Stanton2003107
8Cars 2John Lasseter2011120
9A Bug’s LifeJohn Lasseter199895
10CarsJohn Lasseter2006117
11UpPete Docter2009101
12BraveBrenda Chapman2012102
13Toy Story 3Lee Unkrich2010103
14Monsters, Inc.Pete Docter200192

1、List all directors of Pixar movies (alphabetically), without duplicates

1
2
3
SELECT distinct director FROM movies 
order by director ASC
;

2、List the last four Pixar movies released (ordered from most recent to least)

1
2
3
4
SELECT * FROM movies 
order by year DESC
limit 4
;

3、List the first five Pixar movies sorted alphabetically

1
2
3
4
SELECT * FROM movies 
order by title ASC
limit 5
;

4、List the next five Pixar movies sorted alphabetically

1
2
3
4
SELECT * FROM movies 
order by title ASC
limit 5 offset 5
;

Lesson 5

基本语法

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

练习

citycountrypopulationlatitudelongitude
GuadalajaraMexico150080020.659699-103.349609
TorontoCanada279506043.653226-79.383184
HoustonUnited States219591429.760427-95.369803
New YorkUnited States840583740.712784-74.005941
PhiladelphiaUnited States155316539.952584-75.165222
HavanaCuba210614623.05407-82.345189
Mexico CityMexico855550019.432608-99.133208
PhoenixUnited States151336733.448377-112.074037
Los AngelesUnited States388430734.052234-118.243685
Ecatepec de MorelosMexico174200019.601841-99.050674
MontrealCanada171776745.501689-73.567256
ChicagoUnited States271878241.878114-87.629798

1、List all the Canadian cities and their populations

1
2
3
SELECT * FROM north_american_cities
where country="Canada"
;

2、Order all the cities in the United States by their latitude from north to south

1
2
3
4
SELECT * FROM north_american_cities
where country="United States"
order by Latitude DESC
;

3、List all the cities west of Chicago, ordered from west to east

1
2
3
4
SELECT * FROM north_american_cities
where longitude < -87.629798
order by longitude ASC
;

4、List the two largest cities in Mexico (by population)

1
2
3
4
5
SELECT * FROM north_american_cities
where country = "Mexico"
order by population DESC
limit 2
;

5、List the third and fourth largest cities (by population) in the United States and their population

1
2
3
4
5
SELECT * FROM north_american_cities
where country = "United States"
order by population DESC
limit 2 offset 2
;

-------------本文结束感谢您的阅读-------------
0%