SQLBolt 学习笔记 Lesson1-5 附练习详解

Lesson 1

项目概括：

Exercise：

解决方案：

Lesson 2 Queries with constraints (Pt. 1)

项目概括：

Exercise：

解决方案：

Lesson 3: Queries with constraints (Pt. 2)

项目概括：

Exercise：

解决方案：

Lesson 4: Filtering and sorting Query results

项目概括：

Exercise：

解决方案：

Review: Simple SELECT Queries

项目概括：

Exercise：

解决方案：

总结

---

Lesson 1

项目概括：

本节介绍了sql查询语句中基础语句 select xxxx from [table name]

---

Exercise：

---

解决方案：

1. select title from movies;

2. select director from movies;

3. select title,director from movies;
// 查询多个列 直接用逗号分割即可
4. select title,year from movies;

5. select * from movies;
// *  指查询所有数据

---

Lesson 2 Queries with constraints (Pt. 1)

项目概括：

本节主要介绍了 where 查询语句的初级部分，也就是简单的套用条件查询数据库内容

Exercise：

解决方案：

1. SELECT * FROM movies
        where id='6';

2. SELECT * FROM movies
    where year between 2000 and 2010;
//一定要加上 year ！！！  然后用between划定范围

3.SELECT * FROM movies
    where year not between 2000 and 2010;

4.SELECT * FROM movies
    where id between 1 and 5;
找到前五个电影，id已经标明顺序，用between即可

---

Lesson 3: Queries with constraints (Pt. 2)

项目概括：

本节主要讲述了利用between查询语句进行更为复杂的查询方法

熟练掌握表格内容即可

Exercise：

解决方案：

本题答案不唯一！！！有很多查询方法，欢迎大家在评论区补充！！！
1. SELECT * FROM movies
    where title like 'toy%';
//where 匹配 ‘toy%’ 的项目即可

2.SELECT * FROM movies
    where director like 'joh%';

3. SELECT * FROM movies
    where director not like 'joh%';

4. SELECT * FROM movies
    where title  like 'wa%';
wa% wall%都可，只要筛选出合适的即可

Lesson 4: Filtering and sorting Query results

项目概括：

本节介绍了distinct，order by，limit offset的用法

distinct即去掉重复的（但是是无规律去重复，有很强的随机性）

order by 是排序的

limit offset规定展示查询表格数据的数量，以及查询起始点

Exercise：

解决方案：

1. SELECT distinct director FROM movies
    order  by director asc;
    
2. SELECT  title FROM movies
    order  by year desc
    limit 4;

3. SELECT  title FROM movies
    order  by title asc
    limit 5;
 
4. SELECT  title FROM movies
    order  by title asc
    limit 5 offset 5;
    
    

---

Review: Simple SELECT Queries

项目概括：

复习课撒~

Exercise：

解决方案：

1. SELECT * FROM north_american_cities
    where  country like 'can%';
//where 筛选即可

2.SELECT * FROM north_american_cities
    where  country like 'uni%'
    order by latitude desc;
//where筛选+Order by排序

3. SELECT * FROM north_american_cities
    where  longitude < -87.629798
    order by longitude asc;
//where筛选+Order by排序

4. SELECT * FROM north_american_cities
    where  country like 'me%'
    order by population desc
    limit 2;
//where筛选+Order by排序+limit 展示数据

5. SELECT * FROM north_american_cities
    where  country like 'un%'
    order by population desc
    limit 2 offset 2;
//where筛选+Order by排序+limit 展示数据

总结

基础查询格式
SELECT query
SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;

以上就是本文的全部内容，师傅们如果有好的建议/意见欢迎评论区交流~

Powered By M0nH1N