Module 03 · MySQL and PostgreSQL

Hands-on Lab: Getting Started with PostgreSQL Command Line动手实验：PostgreSQL 命令行入门

In this lab, you will use the PostgreSQL command line interface (CLI) to create a database and restore the structure and contents of its tables. Then, you will learn how to explore and query tables. Finally, you will learn how to dump/backup tables from a database.
在这个实验中，你将使用 PostgreSQL 命令行界面（CLI）来创建数据库，并恢复其表的结构和内容。然后，你将学习如何探索和查询表。最后，你将学习如何从数据库中转储/备份表。

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

Software used in this lab 本实验使用的软件

In this lab, you will use a PostgreSQL Database. PostgreSQL is a relational database management system (RDBMS) designed to store, manipulate, and retrieve data efficiently.

To complete this lab, you will utilize the PostgreSQL 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.

Database used in this lab 本实验使用的数据库

The Sakila database used in this lab comes from the following source: https://dev.mysql.com/doc/sakila/en/ under New BSD license [Copyright 2021 - Oracle Corporation].
本实验中使用的 Sakila 数据库来源于以下地址：https://dev.mysql.com/doc/sakila/en/，遵循新 BSD 许可协议 [版权所有 2021 - Oracle Corporation]。

You will use a modified version of the database for the lab. To follow the lab instructions successfully, please use the database provided by the lab rather than the database from the source.

The following entity relation diagram (ERD) shows the structure of the schema of the Sakila database:
以下实体关系图（ERD）显示了 Sakila 数据库模式的结构：

Objectives 目标

After completing this lab, you will be able to use the PostgreSQL command line to:
完成此实验后，您将能够使用 PostgreSQL 命令行来：

Create a database 创建数据库
Restore the structure and data of a table
恢复表的结构和数据
Explore and query tables 探索和查询表格
Dump/backup tables from a database
从数据库中导出/备份表格

Lab structure 实验室结构

In this exercise, you will go through several subtasks where you will use the PostgreSQL command line interface (CLI) to create a database and restore the structure and contents of tables. Then, you will learn how to explore and query tables. Finally, you will learn how to dump/backup tables from a database.
在本练习中，你将通过几个子任务，使用 PostgreSQL 命令行界面（CLI）来创建数据库，并恢复表格的结构和内容。然后，你将学习如何探索和查询表格。最后，你将学习如何从数据库中导出/备份表格。

Task A: Create a database 任务 A：创建数据库

To get started with this lab, launch PostgreSQL using the Cloud IDE. You can do this by following these steps:
开始这个实验，请使用云 IDE 启动 PostgreSQL。您可以通过以下步骤进行操作：

Click the Skills Network extension button on the left side of the window.
点击窗口左侧的 Skills Network 扩展按钮。
Open the DATABASES menu and click PostgreSQL.
打开 DATABASES 菜单并点击 PostgreSQL。
Click Create. PostgreSQL may take a few moments to start.
点击创建。PostgreSQL 可能需要几秒钟才能启动。
Open a new command terminal by clicking New Terminal.
点击新建终端打开一个新的命令终端。
Copy the command below by clicking the little copy button on the right of the code block and then paste it into the terminal using Ctrl + V (Mac: ⌘ + V) to fetch the sakila_pgsql_dump.sql file to the Cloud IDE.
```
wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DB0110EN-SkillsNetwork/datasets/sakila/sakila_pgsql_dump.sql
```
Now, open the PostgreSQL Command Line Interface (CLI) by clicking PostgreSQL CLI.
Create a new database named sakila using the following command in the terminal:
```
create database sakila;
```
Note: You are using the create database command to create a new database within the PostgreSQL CLI. To create a new database named sakila outside the command line interface, you can use the following command directly in a terminal window: createdb --username=postgres --host=postgres --password sakila after quitting the psql command prompt session with the command \q.

Task B: Restore the structure and data of a table 任务 B：恢复表格的结构和数据

To connect to the newly created empty sakila database, use the following command in the terminal and enter your PostgreSQL service session password:
```
\connect sakila;
```
Restore the sakila PostgreSQL dump file (containing the sakila database table definitions and data) to the newly created empty sakila database by using the following command in the terminal:
```
\include sakila_pgsql_dump.sql;
```
Note: You are using the \include command to restore the database dump file within the PostgreSQL CLI. To restore the database dump file outside of the Command Line Interface, you can use the command pg_restore --username=postgres --host=postgres --password --dbname=sakila < sakila_pgsql_dump.tar after quitting the CLI prompt session with the command \q. Non-text format .tar dumps are restored using the pg_restore command. So, before using the pg_restore command, first, fetch the .tar version of this dump file using the command wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DB0110EN-SkillsNetwork/datasets/sakila/sakila_pgsql_dump.tar
Repeat Step 1 to reconnect to the sakila database after restoring the dump file.

Task C: Explore and query tables 任务 C：探索和查询表格

To list all the table names from the sakila database, use the following command in the terminal:
列出 sakila 数据库中所有表名，请在终端中使用以下命令：
```
\dt
```
Explore the structure of the store table using the following command in the terminal:
使用以下命令在终端中探索 store 表的结构：
```
\d store;
```
Retrieve all the records from the store table using the following command in the terminal:
使用以下命令在终端中检索 store 表中的所有记录：
```
SELECT * FROM store;
```
Quit the PostgreSQL command prompt session using the following command in the terminal.
使用以下命令在终端中退出 PostgreSQL 命令提示符会话。
```
\q
```

Task D: Dump/backup tables from a database 任务 D：从数据库导出/备份表

Finally, to dump/backup the store table from the database, use the following command in the terminal and enter your PostgreSQL service session password:
最后，要从数据库中导出/备份存储表，请在终端中输入以下命令并输入您的 PostgreSQL 服务会话密码：
```
pg_dump --username=postgres --host=postgres --password --dbname=sakila --table=store --format=plain > sakila_store_pgsql_dump.sql
```

Note: To only dump/backup the table store from the database in non-text format .tar, you can use the command pg_dump --username=postgres --host=postgres --password --dbname=sakila --table=store --format=tar > sakila_store_pgsql_dump.tar
注意：若要仅以非文本格式 .tar 导出/备份数据库中的存储表，您可以使用命令 pg_dump --username=postgres --host=postgres --password --dbname=sakila --table=store --format=tar > sakila_store_pgsql_dump.tar

To view the dump file within the terminal, use the following command:
要在终端中查看导出文件，请使用以下命令：
```
cat sakila_store_pgsql_dump.sql
```

Conclusion 结论

Congratulations! You have completed this lab, and now you have learned how to create a database, restore the structure and data of a table, explore and query tables, and dump/backup tables from a database.
恭喜！您已完成此实验，现在您已经学会了如何创建数据库、恢复表的结构和数据、探索和查询表以及从数据库中导出/备份表。