基础入门学习SQL语句网站https://sqlbolt.com/
由于网站都是全英文,可以使用网页翻译对照学习。以下代码不区分大小写,双引号或者单引号里面的要区分大小写。(答案不唯一)
SQL Lesson 1: SELECT queries 101
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 * FROM movies where id=6;(查询的内容只显示title也是正确的)
SELECT * FROM movies where year between 2000 and 2010;
SELECT * FROM movies where year not between 2000 and 2010;
SELECT * FROM movies title limit 5;
SQL Lesson 3: Queries with constraints (Pt. 2)
SELECT * FROM movies where title like 'Toy Story%';
SELECT * FROM movies where director like "John Lasseter";(Like或者=都可以,使用双引号和单引号都可以)
SELECT * FROM movies where director not like "John Lasseter";
SELECT * FROM movies where title like "WALL-%";
SQL Lesson 4: Filtering and sorting Query results
SELECT distinct director FROM movies order by director asc;
SELECT * FROM movies order by year desc limit 4;
SELECT * FROM movies order by title asc limit 5;
SELECT * FROM movies order by title asc limit 5 offset 5;
SQL Review: Simple SELECT Queries
SELECT city,population FROM north_american_cities where country="Canada";
SELECT * FROM north_american_cities where country="United States" order by latitude desc;
SELECT * FROM north_american_cities where longitude<(SELECT longitude FROM north_american_cities where city='Chicago') 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 * FROM movies inner join Boxoffice on movies.id=Boxoffice.Movie_id;
SELECT * FROM movies inner join Boxoffice on movies.id=Boxoffice.Movie_id where domestic_sales<International_sales;
SELECT * FROM movies inner join Boxoffice on movies.id=Boxoffice.Movie_id order by rating desc;
SQL Lesson 7: OUTER JOINs
SELECT distinct building FROM employees;
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
SELECT Name,Role FROM employees where building is null;
SELECT Building_name FROM Buildings left join Employees on Buildings.Building_name=Employees.Building where name is null;
SQL Lesson 9: Queries with expressions
SELECT title,(Domestic_sales+International_sales)/1000000.0 as total FROM movies as m left join Boxoffice as b on m.id=b.Movie_id;
SELECT title,rating*10 as total FROM movies as m left join Boxoffice as b on m.id=b.Movie_id;
SELECT * FROM movies where year%2==0;
SQL Lesson 10: Queries with aggregates (Pt. 1)
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 count(Role) FROM employees where Role="Artist";
SELECT role,count(*) FROM employees group by Role;
SELECT sum(Years_employed) FROM employees where role="Engineer";
或者SELECT sum(Years_employed) FROM employees group by Role having role="Engineer";
SQL Lesson 12: Order of execution of a Query
SELECT director,count(title) FROM movies group by director;
SELECT Director,sum(Domestic_sales+International_sales) FROM movies inner join Boxoffice on movies.id= Boxoffice.Movie_id group by director;
SQL Lesson 13: Inserting rows
INSERT INTO Movies(id,title,director,year,length_minutes) VALUES(4,'Toy Story 4','John Lasseter',2010,99);
INSERT INTO Boxoffice(Movie_id,Rating,Domestic_sales,International_sales) VALUES(4,8.7,270000000,340000000)
SQL Lesson 14: Updating rows
UPDATE Movies SET Director='John Lasseter' where title="A Bug's Life";
UPDATE Movies SET Year=1999 where Title='Toy Story 2';
UPDATE Movies SET Title='Toy Story 3',Director='Lee Unkrich' where id=(SELECT id FROM Movies where title='Toy Story 8')
SQL Lesson 15: Deleting rows
DELETE FROM Movies where Year<2005;
DELETE FROM Movies where Director='Andrew Stanton';
SQL Lesson 16: Creating tables
CREATE TABLE IF NOT EXISTS database(Name TEXT,Version float,Download_count INTEGER);
SQL Lesson 17: Altering tables
ALTER TABLE movies ADD Aspect_ratio FLOAT;
ALTER TABLE movies ADD Language TEXT DEFAULT English;
SQL Lesson 18: Dropping tables
DROP TABLE movies;
DROP TABLE BoxOffice;