在sqlbolt上学习SQL

该网站能够学习sql基础，并且能在网页中直接输入sql语句进行查询。
学习网站原网址https://sqlbolt.com/ (！部分指令该网站不支持，且存在一些bug！)
该文为SQLBolt学习者提供答案参考

SQL Lesson 1: SELECT queries 101

查询表格中的特定列

mysql
SELECT 列1, 列2,… 
FROM 表;

查询所有列

SELECT * 
FROM 表;

Exercise1 — Tasks

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)

带约束的选择查询

SELECT 列1, 列2, … FROM 表 
	WHERE 条件1 
		AND/OR 条件2
		AND/OR …;

Exercise 2 — Tasks

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 id <= 5;

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

带约束的选择查询

SELECT 列1, 列2, … 
FROM 表 
WHERE 条件1
	AND/OR 条件2 
	AND/OR …;

Exercise 3 — Tasks

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

唯一结果的选择查询

SELECT DISTINCT 列1, 列2, … 
FROM 表
WHERE condition(s);

带有有序结果的选择查询

SELECT 列1, 列2, … 
FROM 表 WHERE 条件 
ORDER BY 列 ASC/DESC;#ASC升序/DESC降序

限制行的选择查询

SELECT 列1, 列2, …
FROM 表
WHERE 条件
ORDER BY 列 ASC/DESC
LIMIT 数量 OFFSET 开始位置;

Exercise 4 — Tasks

SELECT DISTINCT director FROM movies
ORDER BY director ASC;

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

SELECT title FROM movies
ORDER BY title ASC
LIMIT 5;

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

SQL Review: Simple SELECT Queries

选择查询

SELECT 列1, 列2, …
FROM 表
WHERE 条件
ORDER BY 列 ASC/DESC 
LIMIT 显示数量 OFFSET 开始位置;

Review 1 — Tasks

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;

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

在多个表上使用INNER JOIN选择查询

SELECT 列1, 列2, …
FROM 表1
INNER JOIN 表2 
    ON 表1.id = 表2.id
WHERE 条件
ORDER BY 列, … ASC/DESC
LIMIT 显示数量 OFFSET 开始位置;

Exercise 6 — Tasks

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

在多个表上使用左/右/全连接选择查询

SELECT 列1, 列2, …
FROM 表1
INNER/LEFT/RIGHT/FULL JOIN 表2 
    ON 表1.id = 表2.matching_id
WHERE 条件
ORDER BY 列1, … ASC/DESC
LIMIT 数量 OFFSET 开始位置;

Exercise 7 — Tasks

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

对NULL值有约束的选择查询

SELECT 列1, 列2, …
FROM 表
WHERE 列 IS/IS NOT NULL
AND/OR 条件2
AND/OR …;

Exercise 8 — Tasks

SELECT name, role FROM employees
WHERE building IS NULL;

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

SQL Lesson 9: Queries with expressions

带有表达式的查询示例

SELECT particle_speed / 2.0 AS half_particle_speed
FROM physics_data
WHERE ABS(particle_position) * 10.0 > 500;

带表达式的别名选择查询

SELECT col_expression AS expr_description, …
FROM mytable;

具有列和表别名的查询示例

SELECT column AS better_column_name, …
FROM a_long_widgets_table_name AS mywidgets
INNER JOIN widget_sales
  ON mywidgets.id = widget_sales.widget_id;

Exercise 9 — Tasks

SELECT title,(domestic_sales + international_sales)/1000000 AS million_dollars
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;

SELECT title, year
FROM movies
WHERE year % 2 = 0;

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

Select query with aggregate functions over all rows

SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression;

Exercise 10 — Tasks

SELECT MAX(years_employed)
FROM employees;

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

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

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

选择具有约束的查询

SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM 表名
WHERE 条件
GROUP BY 列名
HAVING 组条件;

Exercise 11 — Tasks

SELECT role, COUNT(*)
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

完整的SELECT查询

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

Exercise 12 — Tasks

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

包含所有列的值的插入语句

INSERT INTO 表名
VALUES (value_or_expr, another_value_or_expr, …),
       (value_or_expr_2, another_value_or_expr_2, …),
       …;

插入特定列的语句

INSERT INTO 表名
(列名1, 列名2, …)
VALUES (value_or_expr, another_value_or_expr, …),
      (value_or_expr_2, another_value_or_expr_2, …),
      …;

Exercise 13 — Tasks

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

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

SQL Lesson 14: Updating rows

跟新表数据

UPDATE mytable
SET 列1 = value_or_expr, 
    列2 = another_value_or_expr, 
    …
WHERE condition;

Exercise 14 — Tasks

UPDATE movies
SET director = "John Lasseter"
WHERE Title = "A Bug"s Life";

UPDATE Movies
SET Year = 1999
WHERE Title="Toy Story 2";

SQL Lesson 15: Deleting rows

带条件的删除语句

DELETE FROM 表 
WHERE 条件;

Exercise 15 — Tasks

DELETE FROM movies 
WHERE year<2005;

DELETE FROM movies
WHERE director = "Andrew Stanton";

SQL Lesson 16: Creating tables

创建带有可选表格约束和默认值的表格语句

CREATE TABLE IF NOT EXISTS 表 ( 
	列名1 数据类型 可选表格约束 DEFAULT 缺省值,
    列名2 数据类型 可选表格约束 DEFAULT 缺省值,
    … 
);

Table data types

Table constraints

An example

Movies table schema
CREATE TABLE movies (
    id INTEGER PRIMARY KEY,
    title TEXT,
    director TEXT,
    year INTEGER, 
    length_minutes INTEGER
);

Exercise 16 — Tasks

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

SQL Lesson 17: 修改表

添加列

修改表以添加新列

ALTER TABLE 表名 
ADD 列名 *数据类型*  可选表格约束 
DEFAULT 缺省值;

移除列

修改表以删除列

ALTER TABLE 表名 
DROP 列名;

重命名表

更改表名

ALTER TABLE 表名 
RENAME TO 新表名;

Exercise 17 — Tasks

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

Drop table statement

DROP TABLE IF EXISTS mytable;

Exercise 18 — Tasks

DROP TABLE Movies;
DROP TABLE BoxOffice;