目录
Lesson 6: Multi-table queries with JOINs
Lesson 8: A short note on NULLs
Lesson X: To infinity and beyond!
Lesson 6: Multi-table queries with JOINs
项目概括:
在多个表上使用 INNER JOIN 选择查询
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;
语法熟记,inner join 对应好行、列即可
Exercise:
解决方案:
1. SELECT title,domestic_sales,international_sales FROM boxoffice
inner join movies on boxoffice.movie_id=movies.id;
2. SELECT title,domestic_sales,international_sales FROM boxoffice
inner join movies on boxoffice.movie_id=movies.id
where domestic_sales <international_sales;
3. SELECT title,domestic_sales,international_sales FROM boxoffice
inner join movies on boxoffice.movie_id=movies.id
order by rating desc;
Lesson 7: OUTER JOINs
项目概括:
在多个表上使用 LEFT/RIGHT/FULL JOIN 选择查询
SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table
ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;
进一步阐述:
When joining table A to table B, a LEFT JOIN
simply includes rows from A regardless of whether a matching row is found in B. 显化为语法就是
SELECT column, another_column, …A
LEFT JOIN B ON A.id = B.matching_id
一定要区分好A,B表格
Exercise:
解决方案:
1. SELECT distinct building_name FROM buildings
left join employees on building_name=employees.building
WHERE building IS NOT NULL;
或者:
SELECT distinct building FROM employees;
2. select * from buildings;
3. SELECT DISTINCT building_name, role FROM buildings
left join employees on building_name=employees.building;
Lesson 8: A short note on NULLs
项目概括:
本节主要讲了null的使用 ,我认为是 where筛选 这一节的内容,用于筛选哪些数据是/ 不是空行
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:
解决方案:
1. select * from employees
where building is null;
2. select * from buildings
left join employees on buildings.building_name=employees.building
where role is null;
Lesson 13: Inserting rows
项目概括:
本节主要介绍了插入行,掌握插入行的格式即可
Insert statement with values for all columns
INSERT INTO mytable
VALUES (value_or_expr, another_value_or_expr, …),
(value_or_expr_2, another_value_or_expr_2, …),
…;
Exercise:
解决方案:
1. insert into movies values
(4,"Toy Story 4","m0nh1n",2021,87)
2. insert into boxoffice values
(4,8.7,340000000,270000000);
Lesson 14: Updating rows
项目概括:
本节介绍了怎么更新行的数据,也就是更新某一实例的方法,一定要应用where查询筛选数据!!
构造时需要额外小心!!!
Update statement with values
UPDATE mytable
SET column = value_or_expr,
other_column = another_value_or_expr,
…
WHERE condition;
Exercise:
解决方案:
1. update movies
set director="John Lasseter"
where title="A Bug's Life";
2. update movies
set year=1999
where title="Toy Story 2";
3. update movies
set director="Lee Unkrich",
title="Toy Story 3"
where title="Toy Story 8";
Lesson 15: Deleting rows
项目概括:
本节讲述了删除行的方法,一定要应用where查询筛选数据!!构造时需要额外小心!!!
Delete statement with condition
DELETE FROM mytable
WHERE condition;
Exercise:
解决方案:
1. delete from movies
where year < 2005;
2. delete from movies
where director="Andrew Stanton";
Lesson 16: Creating tables
项目概括:
本节主要介绍了创建表格的方法,IF NOT EXISTS mytable是可选项,创建新的表时查询一下是否重名即可
CREATE TABLE IF NOT EXISTS mytable (
column DataType TableConstraint DEFAULT default_value,
another_column DataType TableConstraint DEFAULT default_value,
…
);
Exercise:
解决方案:
1. create table Database(
Name text,
Version double,
Download_count real
);
Lesson 17: Altering tables
项目概括:
本节主要讲了对表格内容的操作
Altering table to add new column(s)
ALTER TABLE mytable
ADD column DataType OptionalTableConstraint
DEFAULT default_value;
Altering table to remove column(s)
ALTER TABLE mytable
DROP column_to_be_deleted;
Altering table name
ALTER TABLE mytable
RENAME TO new_table_name;
Exercise:
解决方案:
1. alter table movies
add "Aspect_ratio" float;
2. alter table movies
add "Language" text
default English;
Lesson 18: Dropping tables
项目概括:
这一节教你删表跑路。
Drop table statement
DROP TABLE IF EXISTS mytable;
Exercise:
解决方案:
有点不忍心删除陪我这么几天,供我练手的表格,这节内容较简单,就没有答案咯~
Lesson X: To infinity and beyond!
You've finished the tutorial!
sqlbolt的课程到此结束啦~
本系列(sqlbolt)的博客到这里就结束啦~欢迎大家有新的想法观点可以在评论区及时提出,有不妥的地方欢迎各位师傅批评指正~