sql入门练习一

数据表

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

 

练习

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;

7.找到id为6的电影
SELECT * FROM movies where Id=6;

8.找到在2000-2010年间year上映的电影
SELECT * FROM movies where Year between 2000 and 2010;

9.找到不是在2000-2010年间year上映的电影
SELECT * FROM movies where Year not between 2000 and 2010;

10.找到头5部电影 
SELECT * FROM movies limit 0,5;

11.找到2010（含）年之后的电影里片长小于两个小时的片子
SELECT * FROM movies where Year>=2010 and Length_minutes<120;

12.找到所有Toy Story系列电影
SELECT * FROM movies where Title like '%Toy Story%';

13.找到所有John Lasseter导演的电影 
SELECT * FROM movies where Director ='John Lasseter';

14.找到所有不是John Lasseter导演的电影
SELECT * FROM movies where Director !='John Lasseter';

15.找到所有电影名为 "WALL-" 开头的电影
SELECT * FROM movies where Title like'WALL-%';

16.有一部98年电影中文名《虫虫危机》请给我找出来
SELECT * FROM movies where Year=1998;

17.按导演名排重列出所有电影(只显示导演)，并按导演名正序排列
SELECT DISTINCT Director FROM movies order by Director asc;

18.列出按上映年份最新上线的4部电影 
SELECT DISTINCT * FROM movies order by Year desc limit 0,4;

19.按电影名字母序升序排列，列出前5部电影
SELECT DISTINCT * FROM movies order by Title asc limit 0,5;

20.按电影名字母序升序排列，列出上一题之后的5部电影
SELECT DISTINCT * FROM movies order by Title asc limit 5,5;

21.如果按片长排列，John Lasseter导演导过片长第3长的电影是哪部，列出名字即可 
SELECT Title FROM movies where Director='John Lasseter' order by Length_minutes desc limit 2,1