网站所给习题是简单地修改select后面跟着的列名: Find the title of each film ✓ Find the director of each film ✓ Find the title and director of each film ✓ Find the title and year of each film ✓ Find all the information about each film ✓
SELECT title FROM movies;SELECT director FROM movies;SELECT title,director FROM movies;SELECT title,yearFROM movies;SELECT*FROM movies;
2.SQL Lesson 2: Queries with constraints(带约束查询1)
行角度。
使用where子句实现;
where子句常用操作符:=, !=, <, <=, >=, >, <>; between…and…; not between…and…; in(…); not in(…);
运算符示例(假设选择名为num的某列作为约束对象): …where num!=4; …where num between 1 and 10; …where not num between 100 and 10000; …where num in (1,3,9); …where num not in (1,2,3);
练习: Find the movie with a row id of 6 ✓ Find the movies released in the years between 2000 and 2010 ✓ Find the movies not released in the years between 2000 and 2010 ✓ Find the first 5 Pixar movies and their release year ✓
SELECT*FROM movies where id=6;SELECT*FROM movies whereyearbetween2000and2010;SELECT*FROM movies whereyearnotbetween2000and2010;SELECT title,yearFROM movies WHEREyear<=2003;SELECT title,yearFROM movies where id in(1,2,3,4,5);
3.SQL Lesson 3: Queries with constraints (带约束查询2)
文本数据类型也有一些运算符;
除了=,<>,!=,in和not in以外,还有like,not like,%,_;
like的作用是判断文本是否符合一定的规则,%匹配任意长度字符串,_匹配单个字符,比如(选择列名为text_content作为约束对象): …where text_content like ‘张%’,那么“张”字开头的任意字符串都符合条件(如张一,张二三五七八;单个“张”也可以); …where text_content like ‘陈 _’,匹配“陈”字开头的任意两个字符,比如陈真,但是陈心心不行,单个陈字也不行。
练习: Find all the Toy Story movies ✓ Find all the movies directed by John Lasseter ✓ Find all the movies (and director) not directed by John Lasseter ✓ Find all the WALL-* movies ✓
SELECT*FROM movies where title like"Toy Story%";SELECT*FROM movies where director="John Lasseter";SELECT title,director FROM movies where director!="John Lasseter";SELECT title,director FROM movies where director<>"John Lasseter";SELECT title,director FROM movies where title like"WALL-%";
4.SQL Lesson4:Filtering and sorting Query results(过滤&排序)
练习: List all directors of Pixar movies (alphabetically), without duplicates ✓ List the last four Pixar movies released (ordered from most recent to least) ✓ List the first five Pixar movies sorted alphabetically ✓ List the next five Pixar movies sorted alphabetically ✓
SELECTdistinct director FROM movies orderby director asc;SELECT*FROM movies orderbyyeardesclimit4;SELECT*FROM movies orderby title asclimit5;SELECT*FROM movies orderby title asclimit5offset5;
5.SQL Review: Simple SELECT Queries(复习简单的查询语句)
练习: List all the Canadian cities and their populations ✓ Order all the cities in the United States by their latitude from north to south ✓ List all the cities west of Chicago, ordered from west to east ✓ List the two largest cities in Mexico (by population) ✓ List the third and fourth largest cities (by population) in the United States and their population ✓
SELECT city,population FROM north_american_cities where country='Canada';SELECT*FROM north_american_cities
where country='United States'orderby latitude desc;select*from north_american_cities where longitude<(SELECT longitude FROM north_american_cities where city='Chicago')orderby longitude;select*from north_american_cities where country='Mexico'orderby population desclimit2;select city,population from north_american_cities
where country='United States'orderby population desclimit2offset2;
6.SQL Lesson 6: Multi-table queries with JOINs(使用join多表查询)
SELECT m.title,b.domestic_sales,international_sales
FROM movies as m
innerjoin boxoffice as b on b.movie_id=m.id;SELECT m.title,b.domestic_sales,international_sales
FROM movies as m
innerjoin boxoffice as b on b.movie_id=m.id
where b.domestic_sales<b.international_sales;SELECT m.title,b.domestic_sales,international_sales
FROM movies as m
innerjoin boxoffice as b on b.movie_id=m.id
orderby rating desc;
7.SQL Lesson 7: OUTER JOINs(外连接)
inner join返回两个表都包含的数据,但存在不对称(asymmetric)数据的可能,因此需要left join, right join和full join。
备注:left outer join, right outer join和full outer join是兼容SQL-92的,去掉outer也一样。
练习: Find the list of all buildings that have employees ✓ Find the list of all buildings and their capacity ✓ List all buildings and the distinct employee roles in each building (including empty buildings) ✓
SELECTdistinct b.building_name FROM employees as e
innerjoin buildings as b
on e.building=b.building_name;SELECTdistinct building_name,capacity FROM buildings;SELECTdistinct e.role,b.building_name
FROM buildings as b
leftjoin employees as e
on e.building=b.building_name;
如果无法避免null值(比如针对非对称数据进行外连接),可以通过is null或is not null进行判断。
练习: Find the name and role of all employees who have not been assigned to a building ✓ Find the names of the buildings that hold no employees ✓
SELECT name,role FROM employees
where building isnull;select b.building_name from buildings as b
leftjoin employees as e
on e.building=b.building_name
groupby b.building_name
havingcount(e.building)=0;
9.SQL Lesson 9: Queries with expressions(带表达式的查询)
查询结果使用as关键词可以提高可读性;
每一种数据都有自带的数字、字符串和日期运算函数。
练习: List all movies and their combined sales in millions of dollars ✓ List all movies and their ratings in percent ✓ List all movies that were released on even number years ✓
SELECT m.title,(b.domestic_sales+b.international_sales)/1000000as combined_sales
FROM movies as m
innerjoin boxoffice as b
on m.id=b.movie_id;SELECT m.title,b.rating*10as rating_percentage
FROM movies as m
innerjoin boxoffice as b
on m.id=b.movie_id;SELECT*FROM movies
whereyear%2=0;
10.SQL Lesson 10: Queries with aggregates(聚合表达式1)
常用的聚合函数主要有:count(), min(), max(), avg(), sum();
其中,count(*)和count(column)的区别是,指定列名之后会舍弃null值;
聚合函数通常和group by连用(单独使用也可以)。
练习: Find the longest time that an employee has been at the studio ✓ For each role, find the average number of years employed by employees in that role ✓ Find the total number of employee years worked in each building ✓
练习: Find the number of Artists in the studio (without a HAVING clause) Find the number of Employees of each role in the studio Find the total number of years employed by all Engineers
SELECTcount(role)FROM employees
where role='Artist';SELECT role,count(*)FROM employees
groupby role;SELECT role,sum(years_employed)FROM employees
groupby role
having role='Engineer';
12.SQL Lesson 12: Order of execution of a Query(执行顺序)
首先,执行from和join语句(包括子句中的子查询),决定请求的数据集范围;
其次,执行where语句过滤掉部分数据;
再次,执行group by语句,对数据进行分组(分组结果行数和唯一值个数一致);
随后,如果存在having,则对分组结果进行过滤;
然后,执行select中的表达式;
之后,执行distinct去除包含重复值的记录;
随后,执行order by对结果进行排序;
最后,limit…offset语句被执行对结果做最后的筛选。
练习: Find the number of movies each director has directed Find the total domestic and international sales that can be attributed to each director
SELECT director,count(*)FROM movies
groupby director;SELECT m.director,sum(b.domestic_sales+b.international_sales)as total_sales
FROM movies as m
innerjoin boxoffice as b
on b.movie_id=m.id
groupby m.director;
练习: Add the studio’s new production, Toy Story 4 to the list of movies (you can use any director) Toy Story 4 has been released to critical acclaim! It had a rating of 8.7, and made 340 million domestically and 270 million internationally. Add the record to the BoxOffice table.
insertinto movies(title,director,year)values('Toy Story 4','John Lasseter',2020);insertinto boxoffice(movie_id,rating,domestic_sales,international_sales)values(15,8.7,340,270);
14.SQL Lesson 14: Updating rows(更新数据)
更新数据时要非常小心,因为一旦修改了就难以恢复;
因此,可以先用select…where查询一下再操作。
练习: The director for A Bug’s Life is incorrect, it was actually directed by John Lasseter The year that Toy Story 2 was released is incorrect, it was actually released in 1999 Both the title and director for Toy Story 8 is incorrect! The title should be “Toy Story 3” and it was directed by Lee Unkrich
UPDATE movies
SET director ="John Lasseter"WHERE id =2;UPDATE movies
SETyear=1999WHERE id =3;UPDATE movies
SET title ="Toy Story 3", director ="Lee Unkrich"WHERE id =11;
15.SQL Lesson 15: Deleting rows(删除数据)
与update一样,删除之前可以先查询一下确保无误。
练习; This database is getting too big, lets remove all movies that were released before 2005. ✓ Andrew Stanton has also left the studio, so please remove all movies directed by him. ✓
deleteFROM movies whereyear<2005;deleteFROM movies where director="Andrew Stanton";
练习: Create a new table named Database with the following columns: – Name A string (text) describing the name of the database – Version A number (floating point) of the latest version of this database – Download_count An integer count of the number of times this database was downloaded This table has no constraints. ✓
0.来源提前挂一下:推荐几个SQL在线学习网站;来源是: SQLBolt-Learn SQL with simple, interactive exercises.网站内容为英文,通过浏览器即可学习。0.Introduction to SQL(SQL的介绍)需知:SQL(结构化查询语言)有一般意义上的基本标准,但是各大关系型数据库(SQLite,Mysql,SqlServer等)在...