sql基本命令_开始使用sql所需的基本命令

sql基本命令

程式设计(Programming)

Getting the fundamentals right is a MUST to mastering SQL.

掌握正确的基础知识是掌握SQL的必须。

SQL is one of the most commonly used tools that you will encounter on your Data Science journey. Before actually working on real-life projects, I had very little respect for SQL’s abilities(out of ignorance) and did not focus much on it but when I entered the real world of “Data Science”, I realized how important SQL is for extracting, transforming and loading data from databases particularly LARGE datasets with a ton of tables.

SQL是您在数据科学之旅中会遇到的最常用的工具之一。 在实际工作于现实项目之前，我几乎没有尊重SQL的能力(出于无知)，也没有过多地关注它，但是当我进入“数据科学”的现实世界时，我意识到SQL对提取的重要性，转换并从数据库(尤其是具有大量表的LARGE数据集)中加载数据。

In this article, I will be going over some basic yet VERY important SQL commands which everyone should be aware of to get a strong start.

在本文中，我将介绍一些基本但非常重要SQL命令，每个人都应了解这些命令以开始一个良好的起点。

I will be writing my queries on Dbeaver, which is a Universal database tool available for download here but you can use any database tool of your choice as long as the concept is clear. Let us get started

我将在Dbeaver上编写查询，Dbeaver是一个通用数据库工具，可在此处下载，但是只要您清楚概念，就可以使用任何选择的数据库工具。 让我们开始吧

1)选择和从(1) SELECT and FROM)

I am assuming you guys have an SQL database tool ( Dbeaver, pgAdmin) open in front of you. The first two commands I would like to go over are SELECT and FROM. SELECT, as the name suggests, selects all columns or column(s) of our choice FROM the table we are working on. For example

我假设你们面前有一个SQL数据库工具(Dbeaver，pgAdmin)。 我想跳过的前两个命令是SELECT和FROM。 顾名思义，SELECT会从我们正在处理的表中选择我们选择的所有列或所有列。 例如

I have three columns visible here such as AlbumId, Title, and ArtistId. If I go to my query editor and type:

我在这里看到三列，例如AlbumId，Title和ArtistId。 如果我转到查询编辑器并输入：

SELECT AlbumId,Title
FROM Album

Where Album is the table, I will get only the AlbumId and Title back. Let us see

在“专辑”表的位置，我将仅获得“专辑ID”和“标题”。 让我们看看

You can try this with any column and table of your choice. Now, you guys might be thinking, What if I want to return all of my columns? Well, it’s simple,

您可以使用任何选择的列和表来尝试此操作。 现在，你们可能在想，如果我想返回所有列怎么办？ 好吧，很简单，

Just type:

只需输入：

SELECT *
FROM Album

Here, * denotes all of the columns

这里，*表示所有列

2)LIMIT(2) LIMIT)

The Limit command as the name suggests “limits” the output of the table i.e We can limit the number of rows we would like to see. Continuing from our previous example, let us say, I would like to see only the first four rows. Using Limit, We can do that.

顾名思义，Limit命令建议“限制”表的输出，即我们可以限制想要查看的行数。 继续前面的示例，让我们说，我只希望看到前四行。 使用限制，我们可以做到这一点。

SELECT AlbumId,Title
FROM Album
LIMIT 4

and I get:

我得到：

Moving forward.

向前进。

3)订购 (3) ORDER BY)

ORDER BY rearranges the entire column of our choice both alphabetically and numerically if the values are numerical. This restructures the entire table. Let us give it a try

如果值是数字，则ORDER BY会按字母顺序和数字顺序重新排列我们选择的整个列。 这将重组整个表。 让我们尝试一下

SELECT *
FROM Album
ORDER BY Title
LIMIT 9

The result:

结果：

As you might have noticed, The Title now appears alphabetically in order. Starting from A. And the AlbumId and ArtistId appear to have adjusted accordingly.

您可能已经注意到，“标题”现在按字母顺序显示。 从A开始。并且AlbumId和ArtistId似乎已进行了相应调整。

4)在哪里 (4)WHERE)

The WHERE command is used to apply conditional statements to our table. For example, If I wanted to return only those rows which had an ArtistId of 90, I would use the WHERE command. Let us give it a try.

WHERE命令用于将条件语句应用于我们的表。 例如，如果我只想返回ArtistId为90的那些行，则可以使用WHERE命令。 让我们尝试一下。

SELECT *
FROM Album
WHERE ArtistId = 90
ORDER BY Title
LIMIT 9

Pretty simple no? There are other applications of WHERE as well, such as selecting only those values with specific characters in them or only those where the starting alphabet is A or B. Lets take a look

很简单不？ WHERE也有其他应用程序，例如仅选择其中带有特定字符的值或仅选择起始字母为A或B的值。让我们来看一下

5)IN和喜欢(5) IN and LIKE)

The IN and LIKE operators are extremely effective when trying to select specific rows. For example, If I wanted to return only those rows where the Title started with the Alphabet A, How would I do that? Let us see

尝试选择特定的行时，IN和LIKE运算符非常有效。 例如，如果我只想返回标题以字母A开头的那些行，我该怎么做？ 让我们看看

SELECT *
FROM Album
WHERE Title LIKE 'A%'

i get:

我得到：

As you can see, All of my Titles start with the alphabet’s A. The ‘A%’ character in my LIKE statement denotes that the Title should start with A. There are other cases to this as well, such as:

如您所见，我的所有标题都以字母A开头。我的LIKE语句中的“ A％”字符表示标题应以A开头。此外，还有其他一些情况，例如：

‘%A’ all rows where Titles end with A

'％A'标题以A结尾的所有行

‘%A%’ All rows that have A anywhere in the Title name

'％A％'标题名称中任意位置有A的所有行

I would recommend you guys practice on the above statements to get a good grip.

我建议你们对以上陈述进行练习，以更好地掌握。

The IN command works similarly to the LIKE command but it takes a more general approach. What do I mean by that? For example, I wanted to return all the rows where the Title of the band is Facelift. Now, I could do that by using the LIKE command but there is a better way of doing this.

IN命令的工作方式与LIKE命令类似，但是采用了更通用的方法。 那是什么意思例如，我想返回乐队标题为Facelift的所有行。 现在，我可以使用LIKE命令来做到这一点，但是有一种更好的方法。

SELECT *
FROM Album
WHERE Title IN ('Facelift')

Running the above query gives me:

运行上面的查询给我：

I can use the IN command to apply multiple conditions and return multiple values. For example:

我可以使用IN命令来应用多个条件并返回多个值。 例如：

SELECT *
FROM Album
WHERE Title IN ('Facelift','Big Ones','Audioslave')

6)AND＆OR (6) AND & OR)

AND and OR are widely used when you are trying to apply multiple conditions and are extremely handy. AND will return values when BOTH the conditions you’re applying are met whereas OR will return all rows for either one of the conditions being met. For example:Let us say, I would like to return rows where the Title is Audioslave and the AlbumId is 5. Let us give it a try.

当您尝试应用多个条件时，AND和OR被广泛使用，并且非常方便。 当同时满足您要应用的条件时，AND将返回值，而OR将返回满足条件之一的所有行。 例如：让我们说，我想返回标题为Audioslave且AlbumId为5的行。让我们尝试一下。

SELECT *
FROM Album
WHERE Title IN ('Audioslave') AND AlbumId = 5

The above query returns no rows and rightfully so because there is a row with the Title being Audioslave AND AlbumId being 5.

上面的查询没有返回任何行，并且正确地返回了行，因为有一行标题为Audioslave且AlbumId为5。

Let us give the OR command a try.

让我们尝试一下OR命令。

SELECT *
FROM Album
WHERE Title IN ('Audioslave') OR AlbumId = 5

The OR command does return rows because any of the conditions can be met to generate the results.

OR命令确实返回行，因为可以满足任何条件来生成结果。

结论 (Conclusion)

So this was a basic introduction to getting started with SQL queries on Dbeaver(or any other database). I went over some very BASIC yet extremely IMPORTANT concepts that are a Must know to get started with SQL. Hopefully, you guys will find this article useful. If you do, feel free to share it with your peers.

因此，这是Dbeaver(或任何其他数据库)上SQL查询入门的基本介绍。 我介绍了一些非常基础但非常重要的概念，这些概念对于SQL入门是必不可少的。 希望你们会发现本文有用。 如果您愿意，可以随时与同行分享。

[1]: Moeed Lodhi.. Introduction to SQL on Dbeaverhttps://www.youtube.com/watch?v=TGb7ImXsAfo&feature=youtu.be

[1]：Moeed Lodhi .. Dbeaver上SQL简介https://www.youtube.com/watch?v=TGb7ImXsAfo&feature=youtu.be

翻译自: https://medium.com/towards-artificial-intelligence/the-basic-commands-you-need-to-know-to-get-started-with-sql-f50eed7a42f4

sql基本命令