sqlite gui
Solving problems is one of the best ways to learn and reinforce new concepts. In this case, we’ll be applying the most common clauses of the Structured Query Language (SQL), using a relational database (i.e. SQLite), and a graphical user interface (GUI) such as TablePlus to solve business-related questions.
解决问题是学习和强化新概念的最佳方法之一。 在这种情况下，我们将使用关系数据库(即SQLite)和图形用户界面(GUI)(如TablePlus)来应用结构化查询语言(SQL)的最常见子句，以解决与业务相关的问题。
Before we get started, there are a couple of housekeeping items we need to address.
在开始之前，我们需要解决几个内务处理项目。
Download the sample SQLite database. 下载示例SQLite数据库。 Install TablePlus or another compatible GUI. 安装TablePlus或另一个兼容的GUI。Download a sample SQLite database
下载示例SQLite数据库
The database we’ll be using is a sample from the SQLite tutorial website.
我们将使用的数据库是SQLite教程网站中的示例。
This is a visual schema of the sample database. 这是示例数据库的可视模式。By taking a closer look at the schema, a blueprint of the construction of a database, we are able to gain a visual sense of what a database contains. SQLite is an example of a relational database, and we can see relations by observing the connections formed between the keys of different tables. I’ll explain this in a little more detail later on, however for now, the most important thing is to grasp some visual understanding of what the database is comprised of (i.e. Tables that hold varying columns of different data types and relationships between tables.).
通过仔细查看架构(数据库构建的蓝图)，我们可以直观地了解数据库包含的内容。 SQLite是关系数据库的一个示例，我们可以通过观察不同表的键之间形成的连接来查看关系。 我将在稍后更详细地解释这一点，但是现在，最重要的是要对数据库的组成有一些直观的了解(即，表包含不同数据类型的不同列以及表之间的关系。 )。
The download of the sample database I’ll be using can be found here. About halfway through, you should find a link for downloading the sample SQLite database. Click it when you’ve found it.
我将使用的示例数据库的下载可在此处找到。 大约过了一半，您应该找到下载示例SQLite数据库的链接。 找到它后，单击它。
After downloading the database, you’ll notice that it’s in a ZIP format. If you don’t already have a preferred software for unzipping a file, 7-zip is a free service recommended by the SQLite Tutorial website.
下载数据库后，您会注意到它为ZIP格式。 如果您还没有首选的软件来解压缩文件，则7-zip是SQLite教程网站推荐的免费服务。
Once you’ve unzipped the file, it’s helpful to move it to a place that’s easy to access. For example, I chose to move the unzipped file to my desktop.
解压缩文件后，将其移动到易于访问的位置很有帮助。 例如，我选择将解压缩的文件移动到桌面上。
Install TablePlus or another compatible GUI
安装TablePlus或其他兼容的GUI
From here we need the assistance of a program that will help us open and connect to the SQLite database. I’ve chosen to use TablePlus, a modern GUI tool for relational databases. TablePlus is available for download on both Windows and Mac devices, and offers a free tier with plenty of functionality for our purposes. After downloading and opening TablePlus on your device, a window with the below information should appear.
从这里开始，我们需要一个程序的帮助，该程序将帮助我们打开并连接到SQLite数据库。 我选择使用TablePlus ，这是一种用于关系数据库的现代GUI工具。 TablePlus可在Windows和Mac设备上下载，并提供免费层，其中包含用于我们目的的大量功能。 在设备上下载并打开TablePlus后，将出现一个包含以下信息的窗口。
(Note: There may be some slight design differences between the Mac and Windows TablePlus applications. I happen to be using a Windows device.)
(注意：Mac和Windows TablePlus应用程序之间可能会有一些细微的设计差异。我恰好在使用Windows设备。)
If you’re using TablePlus for the first time, your window should look just like the above. You’ll see that there aren’t any preexisting connections which is to be expected. That’s all about to change though. 😃
如果您是第一次使用TablePlus，则窗口应与上面的一样。 您会看到没有任何预先存在的连接是可以预期的。 但这一切都会改变。 😃
We now have our two primary housekeeping items complete.
现在，我们完成了两个主要的客房整理项目。
✔ Downloaded SQLite database.
✔下载SQLite数据库。
✔ Installed compatible GUI (i.e. TablePlus).
✔安装了兼容的GUI(即TablePlus)。
Connect the SQLite database using TablePlus
使用TablePlus连接SQLite数据库
Given that the database is SQLite, we’ll want to select the SQLite option when creating a new connection within TablePlus.
鉴于数据库是SQLite，在TablePlus中创建新连接时，我们将要选择SQLite选项。
Select SQLite when creating a new connection in TablePlus. 在TablePlus中创建新连接时，选择SQLite。It’s now time to pick a name and choose the location your database file is stored in. A password is optional, however I do recommend testing the connection by clicking “Test”. After verifying that the connection is okay, select “Save”.
现在是时候选择一个名称并选择数据库文件的存储位置了。密码是可选的，但是我建议通过单击“测试”来测试连接。 确认连接正常后，选择“保存”。
Once you’ve selected the type of database you’re using, it’s time to name and create a connection to the database. 选择了要使用的数据库类型后，就可以命名并创建与数据库的连接了。Looking at the window below, we see that the database has been successfully saved.
在下面的窗口中，我们看到数据库已成功保存。
Double click the newly created database to connect to it. The below window should appear within TablePlus.
双击新创建的数据库以连接到它。 下面的窗口应出现在TablePlus中。
On the left hand side, all of the tables within the database are displayed. You may recognize some of these from the visual schema. 在左侧，显示数据库中的所有表。 您可能会从视觉模式中识别出其中一些。Relational Tables and Common SQL Clauses
关系表和通用SQL子句
Before introducing questions, I want to circle back on something I briefly mentioned earlier. We know that the database is comprised of tables and that each table contains varying types of data. For example, take a closer look at the structure of the “artists” table. What columns and data types do you see? It has two columns, ArtistId and Name, both of which have two different data types. ArtistId is an integer data type while Name is a string of up to 120 various characters.
在介绍问题之前，我想回顾一下我之前简要提到的内容。 我们知道数据库由表组成，并且每个表都包含不同类型的数据。 例如，仔细查看“艺术家”表的结构。 您看到哪些列和数据类型？ 它有两列，ArtistId和Name，它们都有两种不同的数据类型。 ArtistId是整数数据类型，而Name是最多120个不同字符的字符串。
This is the structure of the “artists” table. 这是“艺术家”表的结构。Earlier, I also mentioned that the tables within the database have relationships that can be seen throughout the visual schema. To illustrate, I’ve zoomed in on a relationship between the “artists” and “albums” tables.
之前，我还提到了数据库中的表具有在整个可视化架构中都可以看到的关系。 为了说明，我放大了“艺术家”和“专辑”表之间的关系。
The relationship between the “artists” and “albums” tables is an example of a one-to-many relationship, as each artist can produce 0, 1 or more albums. “艺术家”表与“专辑”表之间的关系是一对多关系的一个示例，因为每个艺术家可以制作0张，1张或更多张专辑。The “artists” table contains what is called a primary key (That’s what the gold key next to the column name signifies.). A primary key is used to uniquely identify each row in a table and can be thought of as the street address of each observation or row. As we look more closely at the relationship between these two tables, we see that the primary key in the “artists” table, ArtistId, is related to ArtistId in the “albums” table. ArtistId in the “albums” table represents what we call a foreign key which simply means that it is a primary key in another table. In this case, it’s the primary key in the “artists” table. Table structure, data types and relationships are all things to keep in mind when thinking about how to use the contents of a database to approach solving questions.
“艺术家”表包含所谓的主键 (这就是列名旁边的金钥匙所表示的含义)。 主键用于唯一标识表中的每一行，并且可以将其视为每个观察值或行的街道地址。 当我们仔细查看这两个表之间的关系时，我们看到“艺术家”表中的主键ArtistId与“相册”表中的ArtistId相关。 “相册”表中的ArtistId表示我们所谓的外键 ，这仅表示它是另一个表中的主键。 在这种情况下，它是“艺术家”表中的主键。 在考虑如何使用数据库的内容来解决问题时，表结构，数据类型和关系都是要牢记的。
One last thing to note are some of the most common clauses used to write SQL queries. Depending on how new you are in your SQL journey, you may or may not have seen some of these before. Regardless, through continued practice you’ll come to discover how user friendly and powerful SQL is. It’s the use of clauses like the ones below that enable us to interact with and extract insights from data in meaningful ways.
最后要注意的是用于编写SQL查询的一些最常见的子句。 根据您在SQL历程中的最新情况，您以前可能会也可能没有见过其中一些。 无论如何，通过不断的练习，您将发现用户友好和强大SQL是如何的。 正是使用下面的条款，使我们能够以有意义的方式与数据进行交互并从中提取见解。
SELECTFROMJOINWHEREGROUP BYORDER BYAs we answer different questions, we’ll start to grasp and see more specifically what each of these clauses do.
当我们回答不同的问题时，我们将开始理解并更具体地了解这些条款中的每一项。
Let’s get started!
让我们开始吧！
Assuming that you still have TablePlus open and running, select the button highlighted in green to open the SQL Editor. The editor is where we will be writing our SQL queries.
假设仍在打开并运行TablePlus，请选择以绿色突出显示的按钮以打开SQL编辑器。 编辑器是我们编写SQL查询的地方。
Once you’ve opened the SQL Editor, your window should look like what’s below.
打开SQL编辑器后，您的窗口应如下图所示。
Introducing the SELECT and FROM Clauses
SELECT和FROM子句介绍
The SELECT clause determines what columns will be returned by a query, and it is also the only clause that can be used without the use of another clause. This means that it can be used by itself. An example of a query that demonstrates this is:
SELECT子句确定查询将返回哪些列，它也是唯一可以在不使用另一个子句的情况下使用的子句。 这意味着它可以单独使用。 演示此情况的查询示例为：
SELECT "Lauren" as name, 24 as age, 1*5 as favorite_number(Note: I used 1*5 to represent a favorite number for the purpose of demonstrating that SQL can perform arithmetic operations. In addition, we can alias a column name by using as.)
(注意：为了说明SQL可以执行算术运算，我使用1 * 5表示喜欢的数字。此外，我们可以使用 as作为 列名的别名 。)
Enter the query into the editor and click “Run Current”.
将查询输入编辑器，然后单击“运行当前”。
The output of the query is highlighted in purple. 查询的输出以紫色突出显示。We can see that the names of the columns we aliased in the query in addition to the specific values we included for each column are represented in the ouput. Again, we did all of this using just the SELECT clause.
我们可以看到，除了我们为每列包括的特定值之外，在输出中还表示了我们在查询中别名的列的名称。 同样，我们仅使用SELECT子句完成了所有这些操作。
If we now wanted to view all columns from a particular table in the sample database, we would need to use both the SELECT and FROM clauses. We know that SELECT dictates what columns we’ll see in our output, but what does the FROM clause do? You may have intuitively guessed it. The FROM clause allows us to specify the particular tables we’re looking to output data from.
如果现在要查看示例数据库中特定表的所有列，则需要同时使用SELECT和FROM子句。 我们知道SELECT决定了我们将在输出中看到哪些列，但是FROM子句做什么？ 您可能已经凭直觉猜到了。 FROM子句允许我们指定要从中输出数据的特定表。
Here, we’ll use SELECT and FROM to view all columns from the “albums” table within the database.
在这里，我们将使用SELECT和FROM从数据库中的“相册”表中查看所有列。
SELECT *FROM albums(Note: The asterisk (*) denotes all columns.)
(注意：星号(*)表示所有列。)
From the output, we see that the “albums” table consists of three columns, AlbumId, Title and ArtistId.
从输出中，我们看到“相册”表由三列组成，即AlbumId，Title和ArtistId。
The results set displays all columns included in the “albums” table and consists of 347 rows. 结果集显示“相册”表中包括的所有列，并由347行组成。Use SQL to Answer Business Related Questions
使用SQL回答与业务相关的问题
Although not necessary, I suggest opening the visual schema in another tab. Having the visual schema easily accessible will not only serve as a useful reference but also help deepen familiarity and understanding of the database composition. Click here to open the schema.
尽管不是必需的，但我建议在另一个选项卡中打开可视模式。 使视觉模式易于访问不仅可以作为有用的参考，还可以帮助加深对数据库组成的熟悉和理解。 单击此处打开架构。
Now that we have some initial knowledge to lean on and somewhat of an intuitive sense of the database and SQL, we’re officially ready to tackle some questions! 🙌
现在我们有了一些初步的知识可以依靠，并且对数据库和SQL有了直观的认识，我们已经准备好解决一些问题！ 🙌
Question 1. What is the full name of each customer?
问题1.每个客户的全名是什么？
This is a great time to look at the schema and think about what table contains data that’s most relevant to answering the question. Given that we’re interested in customer data, the “customers” table is a great place to start.
这是查看架构并考虑哪个表包含与回答问题最相关的数据的好时机。 鉴于我们对客户数据感兴趣，因此“ customers”表是一个很好的起点。
Looking at the columns of the “customers” table, the information we need is found in the CustomerID, FirstName and LastName columns.
查看“客户”表的列，我们在CustomerID，FirstName和LastName列中找到所需的信息。
Query:
查询：
SELECT CustomerId, FirstName, LastNameFROM customersAfter running the query, we now not only have the full names of customers but also know that we have 59 customers in total (based on the number of rows the query returned).
运行查询后，我们现在不仅拥有客户的全名，而且知道我们总共有59个客户(基于查询返回的行数)。
We can verify the number of unique customers by using an aggregate function, in this case COUNT. It’s important to specify “distinct” to ensure that only unique values are returned. From the output of the query below, we can confirm that there are 59 unique customers.
我们可以使用汇总函数(在本例中为COUNT)来验证唯一身份客户的数量。 重要的是指定“ distinct”以确保仅返回唯一值。 从以下查询的输出中，我们可以确认有59个唯一客户。
This query confirms that there are 59 unique customers. 此查询确认有59个唯一客户。Question 2. What are the phone numbers and email addresses of customers who live in the USA?
问题2.居住在美国的客户的电话号码和电子邮件地址是什么？
A good portion of the previous query can be used to answer this. In addition to the columns we selected above, we’ll want to include Phone, Email and Country. We now know what columns and the specific table we need to reference, however, how do we return results that only include customers in the USA?
上一个查询的很大一部分可以用来回答这个问题。 除了我们在上面选择的列之外，我们还要包含“电话”，“电子邮件”和“国家/地区”。 现在，我们知道需要参考哪些列和特定的表格，但是，如何返回仅包含美国客户的结果？
By using the WHERE clause, we’re able to filter the country from which customers live. The WHERE clause filters the data by row through the use of logic.
通过使用WHERE子句，我们可以筛选客户居住的国家/地区。 WHERE子句通过使用逻辑逐行过滤数据。
Query:
查询：
SELECT CustomerID, FirstName, LastName, Phone, Email, CountryFROM customersWHERE Country = "USA"The results of this query include the full names, phone numbers and email addresses of customers who live in the USA.
该查询的结果包括居住在美国的客户的全名，电话号码和电子邮件地址。
There are 13 customers who live in the USA. 有13个居住在美国的客户。Question 4. How many customers are located in each country?
问题4.每个国家有多少客户？
From the question above, we know that the “customers” table contains the country from which a customer is located. By selecting Country and counting the number of unique customers (Just like we did as an extension of question 1, however this time using a different name for the column.), we can use the GROUP BY clause to group rows that have the same value. A way to think about what GROUP BY is doing here is to view each row as a summary of customers who live in a given country.
通过上面的问题，我们知道“客户”表包含客户所在的国家/地区。 通过选择国家/地区并计算唯一客户的数量(就像我们对问题1的扩展一样，但是这次使用不同的列名)，我们可以使用GROUP BY子句对具有相同值的行进行分组。 思考GROUP BY在这里做什么的一种方法是将每一行看作是居住在给定国家/地区的客户的摘要。
Question 5. What three countries have the most customers?
问题5.哪三个国家的客户最多？
Using the same query above, we can include the use of the ORDER BY clause to organize the total customers in each country. We want this to be in descending order so that the largest values are listed first. From here, using LIMIT will enable us to reduce the number of rows returned. We’re looking for the top three countries. Therefore, the limit is 3.
使用上面的相同查询，我们可以包括使用ORDER BY子句来组织每个国家/地区的总客户。 我们希望它以降序排列，以便最大的值首先列出。 从这里开始，使用LIMIT将使我们能够减少返回的行数。 我们正在寻找排名前三的国家。 因此，限制为3。
The top three countries are the USA, Canada and Brazil. 前三个国家是美国，加拿大和巴西。(Note: To answer the questions above, we only needed to reference one table in the database. These next questions will require that we reference more than one table. To accomplish this, we’ll be implementing the final common clause from the list mentioned earlier.)
(注意：要回答上述问题，我们只需要引用数据库中的一个表即可。接下来的问题将要求我们引用多个表。要实现此目的，我们将实现上述列表中的最终公共子句之前)。
Question 6. What is the name of the artist of each album?
问题6.每张专辑的歌手叫什么名字？
After reading this question and referencing the schema, there are two tables we’ll need to answer this question. The “artists” table, and the “albums” table.
在阅读了这个问题并引用了架构之后，我们需要两个表来回答这个问题。 “艺术家”表和“相册”表。
Let’s break down this question into parts to most clearly lay out a way for thinking through it.
让我们将这个问题分解为几个部分，以最清晰地提出思考的方法。
Again, we know that the album information we need is in the “albums” table. To see the number of albums we’re trying to find artists for run the query below.
同样，我们知道所需的专辑信息在“专辑”表中。 要查看专辑的数量，我们正在尝试寻找艺术家以运行以下查询。
There are 347 albums in total. 总共有347张专辑。Next, to get artist names, we’ll need information from the “artists” table. We can confirm this by looking at the schema. Notice that there is a column within the “artists” table entitled Name.
接下来，要获取艺术家姓名，我们将需要“艺术家”表中的信息。 我们可以通过查看架构来确认这一点。 请注意，“艺术家”表中有一列名为“名称”的列。
You may recall that I highlighted the relationship between the “artists” and “albums” tables, and we learned that both tables include an ArtistId column. This is a critical piece for being able to perform the last common clause, JOIN.
您可能还记得我强调了“艺术家”和“专辑”表之间的关系，并且我们了解到这两个表都包含ArtistId列。 这是能够执行最后一个公共子句JOIN的关键部分。
(Note: With continued learning of SQL, you’ll discover that there are different types of joins. For the purpose of this article however, I’ll just be covering the type of join that addresses the specific question.)
(注意：通过继续学习SQL，您会发现联接的类型不同。但是，出于本文的目的，我将仅介绍解决特定问题的联接类型。)
Thinking back to question 6, we know that there are 347 albums that we need artist names for. We also know that we can get artist names by accessing the Name column in the “artists” table. To combine the information from these two tables, we’ll apply what’s called an INNER JOIN. An INNER JOIN returns data that is shared or matching between tables. To successfully run an INNER JOIN, given that we’re now working with more than one table, we’ll need to reference the specific table and the columns from that table we want to include. Take a look below.
回想问题6，我们知道有347张专辑需要我们的歌手姓名。 我们也知道，我们可以通过访问“艺术家”表中的“名称”列来获得艺术家的名称。 为了合并来自这两个表的信息，我们将应用所谓的INNER JOIN。 INNER JOIN返回表之间共享或匹配的数据。 鉴于我们现在正在使用多个表，因此要成功运行INNER JOIN，我们需要引用特定的表以及该表中要包括的列。 看看下面。
The output of the query includes each album with an artist name. 查询的输出包括每个带有艺术家姓名的专辑。Question 7. How many albums has each artist produced?
问题7.每位艺术家制作了几张专辑？
For the last question, we’ll implement a little bit of what we’ve learned from each previous question. Because we identified that the “artists” and “albums” table share a one-to-many relationship (an artist can produce 0, 1 or more albums), we want to ensure that we return the results of all artists regardless of whether or not they have produced an album. This is what makes a LEFT JOIN a good choice for solving our final question.
对于最后一个问题，我们将实施从先前每个问题中学到的一些知识。 因为我们确定“艺术家”和“专辑”表之间存在一对多的关系(一个艺术家可以制作0张，1张或更多专辑)，所以我们希望确保我们返回所有艺术家的结果，无论是否不是他们制作了专辑。 这就是使LEFT JOIN成为解决我们最后一个问题的好选择的原因。
We now have a total number of albums that each artist has produced. 现在，我们拥有每位艺术家制作的专辑总数。Bonus Question. What are the names of the Top 10 highest producing artists (based on number of albums)?
奖金问题。 排名前十位的歌手的名字是什么(基于专辑数量)？
(Note: I’ve chosen not to include a query, however, I can assure you that all the information you need to solve this question you’ve applied by working through this article. I have included the correct output you should get after querying for reference.)
(注意：我选择不包括查询，但是，我可以向您保证，通过阅读本文，您可以解决该问题所需要的所有信息。我提供了查询后应该获得的正确输出以供参考。)
These are the Top 10 artists and the number of albums each has produced. 这些是前10名歌手，以及每人制作的专辑数量。· Use an SQLite database.
·使用SQLite数据库。
· Learn to use a GUI like TablePlus.
·学习使用TablePlus之类的GUI。
· Answer business related questions with SQL.
·使用SQL回答与业务相关的问题。
Thank you for reading this article! Should you have any questions or comments, please leave a response. I’d also love to connect! The best place to find me is on LinkedIn or Twitter. :)
感谢您阅读本文！ 如果您有任何问题或意见，请留下答复。 我也很想联系！ 找到我的最佳地点是在LinkedIn或Twitter上 。 :)
翻译自: https://medium.com/swlh/getting-started-with-sql-by-answering-business-related-questions-using-an-sqlite-database-and-gui-e5f642075d4b
sqlite gui
相关资源：微信小程序源码-合集6.rar