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

標籤：MySQL

上一篇：SSM框架之mybatis簡單使用

下一篇：從批處理檔案執行Rscript-無法正確處理“Umlaute”（?、?、ü）