在SQLBolt网站上学习入门SQL

文章源地址：(https://lucasmai.gitee.io/2020/02/25/%E5%9C%A8SQLBolt%E7%BD%91%E7%AB%99%E4%B8%8A%E5%AD%A6%E4%B9%A0%E5%85%A5%E9%97%A8MYSQL%E7%AC%94%E8%AE%B0/)

在sqlbolt上学习SQL

在SQLBolt网站上自学SQL，贴上网站网址 https://sqlbolt.com/ 并推荐大家入门学习

课程大纲如下，在这个网站学习感觉很不错，最重要还可以在线实操，能够快速入门，体验学习的乐趣。

All Lessons
Introduction to SQL
SQL Lesson 1: SELECT queries 101
SQL Lesson 2: Queries with constraints (Pt. 1)
SQL Lesson 3: Queries with constraints (Pt. 2)
SQL Lesson 4: Filtering and sorting Query results
SQL Review: Simple SELECT Queries
SQL Lesson 6: Multi-table queries with JOINs
SQL Lesson 7: OUTER JOINs
SQL Lesson 8: A short note on NULLs
SQL Lesson 9: Queries with expressions
SQL Lesson 10: Queries with aggregates (Pt. 1)
SQL Lesson 11: Queries with aggregates (Pt. 2)
SQL Lesson 12: Order of execution of a Query
SQL Lesson 13: Inserting rows
SQL Lesson 14: Updating rows
SQL Lesson 15: Deleting rows
SQL Lesson 16: Creating tables
SQL Lesson 17: Altering tables
SQL Lesson 18: Dropping tables
SQL Lesson X: To infinity and beyond!

SQL Lesson 1: SELECT queries 101

练习用到Table: Movies

Exercise 1 — Tasks
1.Find the title of each film
2.Find the director of each film
3.Find the title and director of each film
4.Find the title and year of each film
5.Find all the information about each film

Select语句的使用，非常简单，语法为
SELECT column, another_column, …
FROM mytable;

SELECT title
FROM movies;

SELECT director
FROM movies;

SELECT title, director
FROM movies;

SELECT title, year
FROM movies;

SELECT *
FROM movies;

SQL Lesson 2: Queries with constraints (Pt. 1)

练习用到Table: Movies

Exercise 2 — Tasks
1.Find the movie with a row id of 6
2.Find the movies released in the years between 2000 and 2010
3.Find the movies not released in the years between 2000 and 2010
4.Find the first 5 Pixar movies and their release year

带有限制性语句的查询方式，语法格式为
SELECT column, another_column, …
FROM mytable
WHERE condition
AND/OR another_condition
AND/OR …;

常用的一些限制性条件语句：

Operator	Condition	SQL Example
=, !=, < <=, >, >=	Standard numerical operators	col_name != 4
BETWEEN … AND …	Number is within range of two values (inclusive)	col_name BETWEEN 1.5 AND 10.5
NOT BETWEEN … AND …	Number is not within range of two values (inclusive)	col_name NOT BETWEEN 1 AND 10
IN (…)	Number exists in a list	col_name IN (2, 4, 6)
NOT IN (…)	Number does not exist in a list	col_name NOT IN (1, 3, 5)

SELECT id, title FROM movies
WHERE id = 6;

SELECT title, year FROM movies
WHERE year BETWEEN 2000 AND 2010;

SELECT title, year FROM movies
WHERE year < 2000 OR year > 2010;
或
SELECT Title, Year FROM movies WHERE Year NOT Between 2000 and 2010;

SELECT Title, Year FROM movies WHERE Year LIMIT 5;

SQL Lesson 3: Queries with constraints (Pt. 2)

字符串模糊匹配可以使用Like,配合’%’，"_"，使用，以下表格解释的十分清楚。

练习用到Table: Movies

Exercise 3 — Tasks
1.Find all the Toy Story movies
2.Find all the movies directed by John Lasseter
3.Find all the movies (and director) not directed by John Lasseter
4.Find all the WALL-* movies

第一题需要找到Toy Story系列的电影信息，而Toy Story系列的电影有，Toy Story 1，Toy Story 2 … ,所以限制条件可以写为title LIKE "Toy Story%";

SELECT title, director FROM movies
WHERE title LIKE "Toy Story%";

SELECT title, director FROM movies
WHERE director = "John Lasseter";

SELECT title, director FROM movies
WHERE director != "John Lasseter";

SELECT * FROM movies WHERE Title LIKE "WALL-%";

SQL Lesson 4: Filtering and sorting Query results

原文：When an ORDER BY clause is specified, each row is sorted alpha-numerically based on the specified column’s value. In some databases, you can also specify a collation to better sort data containing international text.
指定ORDER BY子句后，每行将根据指定列的值按字母数字顺序排序。在某些数据库中，您还可以指定排序规则以更好地对包含国际文本的数据进行排序。
Another clause which is commonly used with the ORDER BY clause are the LIMIT and OFFSET clauses, which are a useful optimization to indicate to the database the subset of the results you care about.
The LIMIT will reduce the number of rows to return, and the optional OFFSET will specify where to begin counting the number rows from.
另一个通常与ORDER BY子句一起使用的子句是LIMIT和OFFSET子句，这是一个有用的优化，用于向数据库指示您关注的结果的子集。 LIMIT将减少要返回的行数，而可选的OFFSET将指定从何处开始计算行数。
ASC是顺序排序，DESC是逆序排序

练习用到Table: Movies

Exercise 4 — Tasks
1.List all directors of Pixar movies (alphabetically), without duplicates
列出皮克斯电影的所有导演（按字母顺序），没有重复

SELECT DISTINCT director FROM movies
ORDER BY director ASC;

2.List the last four Pixar movies released (ordered from most recent to least)
列出最近发布的四部皮克斯电影（从最近到最少订购）

SELECT title, year FROM movies
ORDER BY year DESC
LIMIT 4;

3.List the first five Pixar movies sorted alphabetically

SELECT title FROM movies
ORDER BY title ASC
LIMIT 5;

4.List the next five Pixar movies sorted alphabetically

SELECT title FROM movies
ORDER BY title ASC
LIMIT 5 OFFSET 5;

SQL Review: Simple SELECT Queries

North_american_cities 表

Review 1 — Tasks
1.List all the Canadian cities and their populations
2.Order all the cities in the United States by their latitude from north to south
3.List all the cities west of Chicago, ordered from west to east
4.List the two largest cities in Mexico (by population)
5.List the third and fourth largest cities (by population) in the United States and their population
注：Latitude 纬度， Longitude 经度

SELECT city, population FROM north_american_cities
WHERE country = "Canada";

SELECT city, latitude FROM north_american_cities
WHERE country = "United States"
ORDER BY latitude DESC;

-- Chicago 以西的城市从西到东排。经纬度为42°35'00.00"N，87°49'00.00"W
SELECT * FROM north_american_cities WHERE Longitude < -87.5 ORDER BY Longitude ASC;

SELECT * FROM north_american_cities WHERE Country='Mexico' ORDER BY Population DESC LIMIT 2;

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

SQL Lesson 6: Multi-table queries with JOINs

练习用表：

两表连接语法：
Select query with INNER JOIN on multiple tables
SELECT column, another_table_column, …
FROM mytable
INNER JOIN another_table
ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;
两表连接需要找到他们的共同属性，如上面两表中的movies.id 和 boxoffice.movie_id

Exercise 6 — Tasks
1.Find the domestic and international sales for each movie
2.Show the sales numbers for each movie that did better internationally rather than domestically
3.List all the movies by their ratings in descending order

SELECT title, domestic_sales, international_sales
FROM movies
INNER JOIN boxoffice
	ON movies.id = boxoffice.movie_id;

SELECT *
FROM movies
INNER JOIN boxoffice
	ON movies.id = boxoffice.movie_id
WHERE international_sales > domestic_sales;

SELECT title, rating
FROM movies
INNER JOIN boxoffice
	ON movies.id = boxoffice.movie_id
ORDER BY rating DESC;

SQL Lesson 7: OUTER JOINs

Depending on how you want to analyze the data, the INNER JOIN we used last lesson might not be sufficient because the resulting table only contains data that belongs in both of the tables.
根据查询任务不同，我们会经常发现 INNER JOIN 不够用的情况，因为它只能取两个表之间共有的行。
If the two tables have asymmetric data, which can easily happen when data is entered in different stages, then we would have to use a LEFT JOIN, RIGHT JOIN or FULL JOIN instead to ensure that the data you need is not left out of the results.
在现实实际乱七八糟的数据，各表格之间数据往往都是不对称的，这时候就得用上 LEFT JOIN、RIGHT JOIN、FULL JOIN 这些了。语法和前面的很类似

Exercise 7 — Tasks
1.Find the list of all buildings that have employees
2.Find the list of all buildings and their capacity
3.List all buildings and the distinct employee roles in each building (including empty buildings)

SELECT DISTINCT building_name
FROM buildings
LEFT JOIN employees
	ON buildings.building_name = employees.building
WHERE building IS NOT NULL;

SELECT *
FROM buildings;

SELECT DISTINCT building_name, role
FROM buildings
LEFT JOIN employees
	ON buildings.building_name = employees.building;

SQL Lesson 8: A short note on NULLs

Exercise 8 — Tasks
1.Find the name and role of all employees who have not been assigned to a building
2.Find the names of the buildings that hold no employees

SELECT name, role FROM employees
WHERE building IS NULL;

SELECT DISTINCT building_name
FROM buildings
  LEFT JOIN employees
    ON building_name = building
WHERE role IS NULL;

SQL Lesson 9: Queries with expressions

语法：
Select query with constraints on NULL values
SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …;

Exercise 9 — Tasks
1.List all movies and their combined sales in millions of dollars
列出所有电影及其总销售额（以百万美元计）
2.List all movies and their ratings in percent
列出所有电影及其收视率（使用百分比）
3.List all movies that were released on even number years
(domestic_sales + international_sales)表示国内外销售总额
SELECT title, (domestic_sales + international_sales) / 1000000 AS gross_sales_millions
FROM movies
JOIN boxoffice
ON movies.id = boxoffice.movie_id;

SELECT title, rating * 10 AS rating_percent
FROM movies
  JOIN boxoffice
ON movies.id = boxoffice.movie_id;

-- 判断奇数偶数可使用%取余运算，如7 % 3 = 1，6 % 2 = 0
SELECT title, year
FROM movies
WHERE year % 2 = 0;

SQL Lesson 10: Queries with aggregates (Pt. 1)

常见的聚合函数：

Function	Description
COUNT(*), COUNT(column)	A common function used to counts the number of rows in the group if no column name is specified. Otherwise, count the number of rows in the group with non-NULL values in the specified column.
MIN(column)	Finds the smallest numerical value in the specified column for all rows in the group.
MAX(column)	Finds the largest numerical value in the specified column for all rows in the group.
AVG(column)	Finds the average numerical value in the specified column for all rows in the group.
SUM(column)	Finds the sum of all numerical values in the specified column for the rows in the group.

练习用表：employees表

Exercise 10 — Tasks
1.Find the longest time that an employee has been at the studio
2.For each role, find the average number of years employed by employees in that role
3.Find the total number of employee years worked in each building

SELECT MAX(years_employed) as Max_years_employed
FROM employees;

SELECT role, AVG(years_employed) as Average_years_employed
FROM employees
GROUP BY role;

SELECT building, SUM(years_employed) as Total_years_employed
FROM employees
GROUP BY building;

SQL Lesson 11: Queries with aggregates (Pt. 2)

练习用表：employees表

Exercise 11 — Tasks
1.Find the number of Artists in the studio (without a HAVING clause)
2.Find the number of Employees of each role in the studio
3.Find the total number of years employed by all Engineers

SELECT role, COUNT(*) as Number_of_artists
FROM employees
WHERE role = "Artist";

SELECT role, COUNT(*)
FROM employees
GROUP BY role;

SELECT role, SUM(years_employed)
FROM employees
GROUP BY role
HAVING role = "Engineer";

SQL Lesson 12: Order of execution of a Query

知识总结：

1. FROM and JOINs
   The FROM clause, and subsequent JOINs are first executed to determine the total working set of data that is being queried. This includes subqueries in this clause, and can cause temporary tables to be created under the hood containing all the columns and rows of the tables being joined.
2. WHERE
   Once we have the total working set of data, the first-pass WHERE constraints are applied to the individual rows, and rows that do not satisfy the constraint are discarded. Each of the constraints can only access columns directly from the tables requested in the FROM clause. Aliases in the SELECT part of the query are not accessible in most databases since they may include expressions dependent on parts of the query that have not yet executed.
3. GROUP BY
   The remaining rows after the WHERE constraints are applied are then grouped based on common values in the column specified in the GROUP BY clause. As a result of the grouping, there will only be as many rows as there are unique values in that column. Implicitly, this means that you should only need to use this when you have aggregate functions in your query.
4. HAVING
   If the query has a GROUP BY clause, then the constraints in the HAVING clause are then applied to the grouped rows, discard the grouped rows that don’t satisfy the constraint. Like the WHERE clause, aliases are also not accessible from this step in most databases.
5. SELECT
   Any expressions in the SELECT part of the query are finally computed.
6. DISTINCT
   Of the remaining rows, rows with duplicate values in the column marked as DISTINCT will be discarded.
7. ORDER BY
   If an order is specified by the ORDER BY clause, the rows are then sorted by the specified data in either ascending or descending order. Since all the expressions in the SELECT part of the query have been computed, you can reference aliases in this clause.
8. LIMIT / OFFSET
   Finally, the rows that fall outside the range specified by the LIMIT and OFFSET are discarded, leaving the final set of rows to be returned from the query.
   Conclusion
   Not every query needs to have all the parts we listed above, but a part of why SQL is so flexible is that it allows developers and data analysts to quickly manipulate data without having to write additional code, all just by using the above clauses.

Exercise 12 — Tasks
1.Find the number of movies each director has directed
2.Find the total domestic and international sales that can be attributed to each director

SELECT director, COUNT(id) as Num_movies_directed
FROM movies
GROUP BY director;

SELECT director, SUM(domestic_sales + international_sales) as Cumulative_sales_from_all_movies
FROM movies
    INNER JOIN boxoffice
        ON movies.id = boxoffice.movie_id
GROUP BY director;

SQL Lesson 13: Inserting rows

Exercise 13 — Tasks
1.Add the studio’s new production, Toy Story 4 to the list of movies (you can use any director)
2.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.

INSERT INTO movies VALUES (4, "Toy Story 4", "El Directore", 2015, 90);

INSERT INTO boxoffice VALUES (4, 8.7, 340000000, 270000000);

SQL Lesson 14: Updating rows

Exercise 14 — Tasks
1.The director for A Bug’s Life is incorrect, it was actually directed by John Lasseter
2.The year that Toy Story 2 was released is incorrect, it was actually released in 1999
3.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
SET year = 1999
WHERE id = 3;

UPDATE movies
SET title = "Toy Story 3", director = "Lee Unkrich"
WHERE id = 11;

SQL Lesson 15: Deleting rows

Exercise 15 — Tasks
1.This database is getting too big, lets remove all movies that were released before 2005.
2.Andrew Stanton has also left the studio, so please remove all movies directed by him.

DELETE FROM movies
where year < 2005;

DELETE FROM movies
where director = "Andrew Stanton";

SQL Lesson 16: Creating tables

Exercise 16 — Tasks
1.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.

CREATE TABLE Database (
    Name TEXT,
    Version FLOAT,
    Download_count INTEGER
);

SQL Lesson 17: Altering tables

Exercise 17 — Tasks
1.Add a column named Aspect_ratio with a FLOAT data type to store the aspect-ratio each movie was released in.
2.Add another column named Language with a TEXT data type to store the language that the movie was released in. Ensure that the default for this language is English.

ALTER TABLE Movies
  ADD COLUMN Aspect_ratio FLOAT DEFAULT 2.39;

ALTER TABLE Movies
  ADD COLUMN Language TEXT DEFAULT "English";

SQL Lesson 18: Dropping tables

Exercise 18 — Tasks
1.We’ve sadly reached the end of our lessons, lets clean up by removing the Movies table
2.And drop the BoxOffice table as well

DROP TABLE Movies;
DROP TABLE BoxOffice;

SQL Lesson X: To infinity and beyond!

到此，这个网站的入门教程结束啦！You’ve finished the tutorial!