Module 03 · Intermediate SQL

Hands-on Lab: Built-in functions动手实验：内置函数

Estimated time needed: 20 minutes
预计所需时间：20 分钟

In SQL, you can access many built-in functions that may be used to get more variety in our data analysis. These functions include aggregation functions (like MAX, MIN, SUM, and AVG), string functions (like LENGTH, UCASE, and LCASE), scalar functions (like ROUND), and a variety of date functions as well. In this tab, you'll get hands-on practice on how to use all of them.
在 SQL 中，您可以访问许多内置函数，这些函数可用于使我们的数据分析更加多样化。这些函数包括聚合函数（如 MAX 、 MIN 、 SUM 和 AVG ）、字符串函数（如 LENGTH 、 UCASE 和 LCASE ）、标量函数（如 ROUND ），以及各种日期函数。在这个选项卡中，您将获得如何使用它们的实践练习。

Software Used in this Lab 本实验中使用的软件

In this lab, you will use MySQL. MySQL is a Relational Database Management System (RDBMS) designed to store, manipulate, and retrieve data efficiently.
在这个实验室中，您将使用 MySQL。MySQL 是一个关系型数据库管理系统（RDBMS），设计用于高效地存储、操作和检索数据。

To complete this lab, you will use MySQL relational database service available as part of IBM Skills Network Labs (SN Labs) Cloud IDE. SN Labs is a virtual lab environment used in this course.
要完成这个实验室，您将使用 IBM Skills Network Labs（SN Labs）云 IDE 提供的 MySQL 关系型数据库服务。SN Labs 是本课程中使用的虚拟实验室环境。

Objectives 目标

After completing this lab, you will be able to compose queries in phpMyAdmin with MySQL using:
完成此实验后，您将能够使用 MySQL 在 phpMyAdmin 中编写查询，包括：

Aggregation Functions 聚合函数
Scalar Functions 标量函数
String Functions 字符串函数
Date Functions 日期函数

Create the database 创建数据库

Click on Skills Network Toolbox. In the Database section, click MySQL.
点击 Skills Network 工具箱。在数据库部分，点击 MySQL。

To start the MySQL, click Create.
要启动 MySQL，请点击创建。

Once MySQL has started, click the phpMyAdmin button to open phpMyAdmin in the same window. Alternatively, click the toggle button next to the phpMyAdmin button to open phpMyAdmin in a new browser tab.
一旦 MySQL 启动，点击 phpMyAdmin 按钮在同一窗口中打开 phpMyAdmin。或者，点击 phpMyAdmin 按钮旁边的切换按钮在新浏览器标签页中打开 phpMyAdmin。

You will see the phpMyAdmin GUI tool.
您将看到 phpMyAdmin 图形界面工具。

In the tree view, click New to create a new empty database. Then, enter Mysql_Learners as the name of the database and click Create.
在树形视图中，点击“新建”创建一个新的空数据库。然后，将数据库名称输入为 Mysql_Learners，并点击“创建”。

Leave the default utf8 encoding. UTF-8 is the most commonly used character encoding for content or data.
保留默认的 utf8 编码。UTF-8 是用于内容或数据最常用的字符编码。

Create the PETRESCUE table 创建 PETRESCUE 表

Rather than create the table manually by typing the DDL commands in the SQL editor, you will execute a script containing the create table command.
与其在 SQL 编辑器中手动输入 DDL 命令来创建表格，您将执行一个包含创建表命令的脚本。

Download the script file PETRESCUE-CREATE.sql
下载脚本文件 PETRESCUE-CREATE.sql

Note: To download, right-click on the link above and click on Save As or Save Link As depending on your browser. Remember to save the file as a .sql file and not HTML.
注意：要下载，请右键点击上方链接，根据您的浏览器选择“另存为”或“保存链接为”。请记住将文件保存为.sql 文件，而不是 HTML 文件。

Next, load the .sql file to your database using the Import option as shown in the image below.
接下来，使用 Import 选项将 .sql 文件加载到您的数据库中，如下面的图片所示。

Upon execution, the table PETRESCUE will be created in the Mysql_Learners database and loaded with a set of values as well. The attributes of the PETRESCUE table are:
执行后，将在 Mysql_Learners 数据库中创建 PETRESCUE 表，并加载一组值。PETRESCUE 表的属性如下：

Column Name 列名	Data Type 数据类型	Description 描述
ID	INTEGER	ID of the entry 条目的 ID
ANIMAL	VARCHAR(20)	Type of animal 动物类型
QUANTITY	INTEGER	Number of animals 动物数量
COST	DECIMAL(6,2)	Cost incurred 发生成本
RESCUEDATE	DATE	Date of Rescue 救援日期

Once the table is loaded, you may open the sql editor to start executing the queries.
一旦表被加载，您可以打开 SQL 编辑器来开始执行查询。

Aggregation Functions 聚合函数

Write a query that calculates the total cost of all animal rescues in the PETRESCUE table.
编写一个查询，计算 PETRESCUE 表中所有动物救援的总成本。

For this query, we will use the function SUM(COLUMN_NAME). The output of this query will be the total value of all elements in the column. The query for this question can be written as:
对于此查询，我们将使用函数 SUM(COLUMN_NAME) 。该查询的输出将是该列中所有元素的总值。此问题的查询可以写成：

SELECT SUM(COST) FROM PETRESCUE;

You can further assign a label to the query SUM_OF_COST.
您还可以为查询 SUM_OF_COST 分配一个标签。

SELECT SUM(COST) AS SUM_OF_COST FROM PETRESCUE;

Write a query that displays the maximum quantity of animals rescued (of any kind).
编写一个查询，显示拯救的动物的最大数量（任何种类）。

For this query, we will use the function MAX(COLUMN_NAME). The output of this query will be the maximum value of all elements in the column. The query for this question can be written as:
对于此查询，我们将使用 MAX(COLUMN_NAME) 函数。该查询的输出将是该列中所有元素的最大值。此问题的查询可以编写为：

SELECT MAX(QUANTITY) FROM PETRESCUE;

The query can easily be changed to display the minimum quantity using the MIN function instead.
查询可以轻松地更改为使用 MIN 函数来显示最小数量。

SELECT MIN(QUANTITY) FROM PETRESCUE;

Write a query that displays the average cost of animals rescued.
编写一个查询，显示被救助动物的平均成本。

For this query, we will use AVG(COLUMN_NAME). The output of this query will be the average of all elements in the column. The query for this question can be written as
对于此查询，我们将使用 AVG(COLUMN_NAME) 。此查询的输出将是该列中所有元素的平均值。此问题的查询可以编写为：

SELECT AVG(COST) FROM PETRESCUE;

Scalar Functions and String Functions 标量函数和字符串函数

Write a query that displays the rounded integral cost of each rescue.
编写一个查询，显示每个救援的舍入后的整数成本。

For this query, we will use the function ROUND(COLUMN_NAME, NUMBER_OF_DECIMALS). The output of this query will be the value of each element in the column rounded to the specified number of decimal places. Note that the second argument is optional and, if omitted, results in rounding to an integer value. The query for this question can be written as:
对于此查询，我们将使用函数 ROUND(COLUMN_NAME, NUMBER_OF_DECIMALS) 。该查询的输出将是该列中每个元素值四舍五入到指定的位数。请注意，第二个参数是可选的，如果省略，则结果将四舍五入为整数。此问题的查询可以写成：

SELECT ROUND(COST) FROM PETRESCUE;

The query could also be written as:
查询也可以这样编写：

SELECT ROUND(COST, 0) FROM PETRESCUE;

In case the question was to round the value to 2 decimal places, the query would change to:
如果问题是将值四舍五入到 2 位小数，查询将更改为：

SELECT ROUND(COST, 2) FROM PETRESCUE;

Write a query that displays the length of each animal name.
编写一个查询，显示每个动物名称的长度。

For this query, we will use the function LENGTH(COLUMN_NAME). The output of this query will be the length of each element in the column. The query for this question can be written as:
对于此查询，我们将使用 LENGTH(COLUMN_NAME) 函数。此查询的输出将是列中每个元素的长度的值。此问题的查询可以写为：

SELECT LENGTH(ANIMAL) FROM PETRESCUE;

Write a query that displays the animal name in each rescue in uppercase.
编写一个查询，显示每次救助中的动物名称为大写。

For this query, we will use the function UCASE(COLUMN_NAME). The output of this query will be each element in the column in upper case letters. The query for this question can be written as:
对于此查询，我们将使用 UCASE(COLUMN_NAME) 函数。此查询的输出将是该列中的每个元素以大写字母显示。此问题的查询可以编写为：

SELECT UCASE(ANIMAL) FROM PETRESCUE;

Just as easily, the user could ask for a lower case representation, and the query would be changed to:
同样轻松，用户可以要求一个小写表示形式，查询将更改为：

SELECT LCASE(ANIMAL) FROM PETRESCUE;

Date Functions 日期函数

Write a query that displays the rescue date.
编写一个查询，显示救援日期。

For this query, we will use the function DAY(COLUMN_NAME). The output of this query will be only the DAY part of the date in the column. The query for this question can be written as:
对于此查询，我们将使用函数 DAY(COLUMN_NAME) 。该查询的输出将仅是该列中日期的 DAY 部分。此问题的查询可以写成：

SELECT DAY(RESCUEDATE) FROM PETRESCUE;

In case the query was asking for MONTH of rescue, the query would change to:
如果查询是要求 MONTH 的救援，查询将变为：

SELECT MONTH(RESCUEDATE) FROM PETRESCUE;

In case the query was asking for YEAR of rescue, the query would change to:
如果查询是要求 YEAR 的救援，查询将变为：

SELECT YEAR(RESCUEDATE) FROM PETRESCUE;

Animals rescued should see the vet within three days of arrival. Write a query that displays the third day of each rescue.
获救的动物应在到达后的三天内去看兽医。编写一个查询，显示每次获救的第三天。

For this query, we will use the function DATE_ADD(COLUMN_NAME, INTERVAL Number Date_element). Here, the quantity in Number and in Date_element will combine to form the interval to be added to the date in the column. For the given question, the query would be:
对于此查询，我们将使用 DATE_ADD(COLUMN_NAME, INTERVAL Number Date_element) 函数。在这里， Number 和 Date_element 中的数量将结合形成要添加到列中日期的间隔。对于给定的问题，查询将是：

SELECT DATE_ADD(RESCUEDATE, INTERVAL 3 DAY) FROM PETRESCUE

If the question was to add 2 months to the date, the query would change to:
如果问题是将日期加 2 个月，查询将变为：

SELECT DATE_ADD(RESCUEDATE, INTERVAL 2 MONTH) FROM PETRESCUE

Similarly, we can retrieve a date before the one given in the column by a given number using the function DATE_SUB. By modifying the same example, the following query would provide the date 3 days before the rescue.
类似地，我们可以使用 DATE_SUB 函数检索列中给定日期之前的日期。通过修改相同的示例，以下查询将提供救援日期前 3 天的日期。

SELECT DATE_SUB(RESCUEDATE, INTERVAL 3 DAY) FROM PETRESCUE

Write a query that displays the length of time the animals have been rescued, for example, the difference between the current date and the rescue date.
编写一个查询，显示动物被救助的时间长度，例如当前日期与救助日期之间的差值。

For this query, we will use the function DATEDIFF(Date_1, Date_2). This function calculates the difference between the two given dates and gives the output in number of days. For the given question, the query would be:
对于此查询，我们将使用函数 DATEDIFF(Date_1, Date_2) 。该函数计算两个给定日期之间的差值，并以天数的形式输出。对于给定的问题，查询将是：

SELECT DATEDIFF(CURRENT_DATE, RESCUEDATE) FROM PETRESCUE

CURRENT_DATE is also an inbuilt function that returns the present date as known to the server.
CURRENT_DATE 也是一个内置函数，它返回服务器所知的当前日期。

To present the output in a YYYY-MM-DD format, another function FROM_DAYS(number_of_days)can be used. This function takes a number of days and returns the required formatted output. The query above would thus be modified to
为了以 YYYY-MM-DD 格式输出，可以使用另一个函数 FROM_DAYS(number_of_days) 。该函数接受天数作为参数，并返回所需的格式化输出。因此，上述查询应修改为

SELECT FROM_DAYS(DATEDIFF(CURRENT_DATE, RESCUEDATE)) FROM PETRESCUE

Practice Problems 练习题

Write a query that displays the average cost of rescuing a single dog. Note that the cost per dog would not be the same in different instances.
编写一个查询，显示救援一只狗的平均成本。请注意，每只狗的成本在不同情况下可能不同。