在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;

来源：https://www.cnblogs.com/wenjie2000/p/15468498.html
图文来源于网络，如有侵权请联系删除。