Module 01 · Getting Started with SQL
Hands-on Lab: Simple SELECT Statements动手实验：简单的 SELECT 语句
In this lab, you will learn one of the most commonly used statements of SQL (Structured Query Language), the SELECT statement. The SELECT statement is used to select data from a database.
To launch the lab, check the box below indicating "I agree to use this app responsibly.", and then click on the "Launch App" button. This will open up the lab environment in a new browser tab.
Estimated time needed: 20 minutes
预计所需时间：20 分钟
In this lab, you will learn one of the most commonly used statements of SQL (Structured Query Language), the SELECT statement. The SELECT statement is used to select data from a database.
在本实验中，你将学习 SQL（结构化查询语言）中最常用的语句之一——SELECT 语句。SELECT 语句用于从数据库中选择数据。
Objectives 目标
After completing this lab, you will be able to:
完成此实验后，您将能够：
- Query a database to obtain a response as a result set
查询数据库以获得结果集作为响应 - Retrieve all or selected columns of a dataset
检索数据集的所有或选定列 - Apply criteria commands to filter the result set
将标准命令应用于过滤结果集
Software Used in this Lab 本实验中使用的软件
In this lab, you will use Datasette, an open source-tool for exploring and publishing data. You can visit the Datasette GitHub repository here.
在这个实验中，你将使用 Datasette，一个用于探索和发布数据的开源工具。你可以在这里访问Datasette GitHub仓库。
Working with Datasette 使用 Datasette 进行操作
The Datasette tool offers a platform to input and execute SQL queries. By clicking the Submit query button, you can execute the SQL query.
数据集工具提供了一个输入和执行 SQL 查询的平台。通过点击提交查询按钮，您可以执行 SQL 查询。
Database Used in this Lab 本次实验使用的数据库
The database used in this lab comes from the following dataset source: Film Locations in San Francisco under a PDDL: Public Domain Dedication and License.
本实验使用的数据库来源于以下数据集：San Francisco的 Film Locations，根据 PDDL：Public Domain的奉献和许可发布。
Exploring the Database 探索数据库
Let's first explore the SanFranciscoFilmLocations database using the Datasette tool:
让我们首先探索SanFranciscoFilmLocations database using the Datasette工具：
- If the first statement listed below is not already in the Datasette textbox on the right, then copy the code below by clicking on the little copy button on the bottom right of the code block below and then paste it into the textbox of the Datasette tool using either Ctrl+V or right-click in the text box and choose Paste.
如果下面列出的第一个声明尚未出现在右侧的 Datasette 文本框中，则通过点击下方代码块右下角的复制按钮复制下面的代码，并将其粘贴到 Datasette 工具的文本框中，可以使用Ctrl+V 或右键点击文本框并选择粘贴。
SELECT * FROM FilmLocations;
- Click Submit Query. 点击提交查询。
- Now, you can scroll down the table and explore all the columns and rows of the FilmLocations table to get an overall idea of the table contents.
现在，您可以向下滚动表格，探索 FilmLocations 表的所有列和行，以获得表格内容的整体概念。
- These are the column attribute descriptions from the FilmLocations table:
以下是 FilmLocations 表的列属性描述：
FilmLocations(Title: titles of the films,ReleaseYear: time of public release of the films,Locations: locations of San Francisco where the films were shot,FunFacts: funny facts about the filming locations,ProductionCompany: companies who produced the films,Distributor: companies who distributed the films,Director: people who directed the films,Writer: people who wrote the films,Actor1: person 1 who acted in the films,Actor2: person 2 who acted in the films,Actor3: person 3 who acted in the films)
Using SELECT statement 使用 SELECT 语句
Now, let's go through some examples of SELECT queries.
现在，让我们来看一些 SELECT 查询的例子。
- Suppose we want to retrieve details of all the films from the FilmLocations table. The details of each film record should contain all the columns. The query statement for this is:
假设我们想要从 FilmLocations 表中检索所有电影的信息。每条电影记录的详细信息应包含所有列。用于此查询的语句是：
SELECT * FROM FilmLocations;
Copy the solution code above and paste it to the textbox of the Datasette tool. Then click Submit query.
将上面的解决方案代码复制并粘贴到 Datasette 工具的文本框中。然后点击提交查询。
Your output resultset should match like the image below:
您的输出结果集应与下图类似：
- We want to retrieve the film names and director and writer names. The query now would be:
我们想要检索电影名称、导演和编剧的姓名。当前的查询将是：
SELECT Title, Director, Writer FROM FilmLocations;
Copy the solution code above and paste it to the textbox of the Datasette tool. Then click Submit query.
将上述解决方案代码复制并粘贴到 Datasette 工具的文本框中。然后点击提交查询。
Your output resultset should match the image below:
您的输出结果集应与下图匹配：
- We want to retrieve film names along with filming locations and release years. But we also want to restrict the output resultset to retrieve only the film records released in 2001 and onwards (release years after 2001, including 2001).
我们希望检索电影名称、拍摄地点和发行年份。但我们还希望限制输出结果集，仅检索在2001及之后的电影记录（发行年份在2001之后，包括2001）。
SELECT Title, ReleaseYear, Locations FROM FilmLocations WHERE ReleaseYear>=2001;
Copy the solution code above and paste it to the textbox of the Datasette tool. Then click Submit query.
将上述解决方案代码复制并粘贴到 Datasette 工具的文本框中。然后点击提交查询。
Your output resultset should match the image below:
您的输出结果集应与下图匹配：
Practice exercises on the SELECT statement SELECT 语句的练习练习
- Retrieve the fun facts and filming locations of all films.
获取所有电影的有趣事实和拍摄地点。
Click here for a hint
点击此处获取提示
Follow example 2 of SELECT, where records containing details of some particular columns have been retrieved.
参考 SELECT 的示例 2，其中检索了包含某些特定列详细信息的记录。
Click here for the solution
点击此处获取答案
SELECT Locations, FunFacts FROM FilmLocations;
Click here for the output
点击此处获取输出
- Retrieve the names of all films released in the 20th century and before (release years before 2000 including 2000), along with filming locations and release years.
检索 20 世纪及以前发行的所有电影名称（发行年份在2000之前且不晚于2000），以及拍摄地点和发行年份。
Click here for a hint
点击此处获取提示
Follow example 3 of SELECT, where we restricted the output resultset to retrieve only the film records with certain release years. Use WHERE clause comparison operator <=, which means Less than or equal to.
参考 SELECT 的示例 3，我们限制了输出结果集以仅检索具有特定发行年份的电影记录。使用 WHERE 子句比较运算符 <= ，表示小于或等于。
Click here for the solution
点击此处获取答案
SELECT Title, ReleaseYear, Locations FROM FilmLocations WHERE ReleaseYear<=2000;
Click here for the output
点击此处获取输出
- Retrieve the names, production company names, filming locations, and release years of the films not written by James Cameron.
检索未由James Cameron编写的电影的名称、制作公司名称、拍摄地点和发行年份。
Click here for a hint
点击此处获取提示
Use WHERE clause comparison operator <>, which means Not equal to.
使用 WHERE 子句比较运算符 <> ，表示不等于。
Click here for the solution
点击此处获取答案
SELECT Title, ProductionCompany, Locations, ReleaseYear FROM FilmLocations WHERE Writer<>"James Cameron";
Click here for the output
点击此处获取输出
Conclusion 结论
Congratulations on completing this lab!
恭喜您完成此实验！
You are now able to:
现在您可以：
- Query a database using SELECT statements
使用 SELECT 语句查询数据库 - Retrieve all or selected columns of data
检索所有或选定列的数据 - Filter the query response to meet a defined criteria
对查询响应进行过滤以符合定义的标准