We don’t pick a hammer and look for nails — that would be an unusual way of solving problems. The usual way of doing business is to identify the problem first, then look for appropriate tools.

我們不用錘子找釘子，那是解決問題的不尋常方式。 做生意的通常方法是先確定問題，然后尋找合適的工具。

I’ve seen over and over again people learn SQL by picking a SQL statement and then learn how to use it. In my opinion, this tool-based mindset is an inefficient way of learning things, and on the other hand, flipping this mindset can make a huge difference. Problems first, tools to follow!

我已經一遍又一遍地看到人們通過選擇一條SQL語句來學習SQL，然后學習如何使用它。 我認為，這種基于工具的思維方式是一種學習事物的低效方式，另一方面，翻轉這種思維方式可能會產生巨大的變化。 問題第一，工具跟隨！

If you are into data science, you know the capabilities of pandas andtidyversetin filtering, sorting, grouping, merging — all sorts of data handling operations. With SQL you will do similar things, but in a database environment and using a different language.

如果您對數據科學tidyverset ，那么您將了解pandas和tidyverset在過濾，排序，分組和合并(各種數據處理操作)中的功能。 使用SQL，您將執行類似的操作，但是要在數據庫環境中并使用另一種語言。

The purpose of this article is to demonstrate how to solve data handling problems in SQL taking a similar approach that data scientists typically follow in a programming environment. You will not learn everything under the sun on SQL, rather the objective is to show “how to” learn.

本文的目的是演示如何使用數據科學家通常在編程環境中遵循的類似方法來解決SQL中的數據處理問題。 您不會在SQL的基礎上學到所有東西，而是要展示“如何”學習。

適用于您的實踐SQL編輯器 (SQL editor for your practice)

If you have a relational database management system installed on your computer, fire it up. If not, w3schools has an online SQL editor that you can use right away on your browser.

如果您的計算機上安裝了關系數據庫管理系統，請啟動它。 如果沒有，則w3schools有一個在線SQL編輯器，您可以在瀏覽器上立即使用它。

You’ll also notice there are quite a few datasets on the right side of the screen that you can use and practice along.

您還將注意到屏幕右側有很多數據集，您可以使用和實踐。

Now let’s get into “how to” solve actual data handling problems using SQL.

現在讓我們進入“如何”使用SQL解決實際數據處理問題的過程。

了解數據 (Understanding data)

Just like what you do with your favorite programming library such aspandas, the first thing you need to do is loading the dataset in the SQL environment.

就像您對喜歡的編程庫(如pandas所做的一樣，您需要做的第一件事是在SQL環境中加載數據集。

And like basic exploratory data analysis (EDA) in a typical data science project, you are able to check out the first few rows, count the total number of rows, see column names, data types etc. Below are a few commands.

與典型數據科學項目中的基本探索性數據分析(EDA)一樣，您可以簽出前幾行，計算行的總數，查看列名，數據類型等。以下是一些命令。

# import data into editor
SELECT * # import all columns with *, else specify column name
FROM table_name
LIMIT 10 #to show 10 rows# import and save data as a separate table
SELECT *
INTO new_table_name
FROM table_name# count number of rows in the dataset
SELECT 
COUNT(*)
FROM table_name# count unique values of a single column
SELECT 
COUNT(DISTINCT column_name) 
FROM table_name

使用列 (Working with columns)

Databases are often quite large, it can take a long time to run queries. So if you know what specific columns you are interested in, you could just make a subset of the data by selecting those columns.

數據庫通常很大，運行查詢可能需要很長時間。 因此，如果您知道感興趣的特定列，則可以通過選擇這些列來構成數據的子集。

You might also want to perform column operations such as renaming, creating new columns etc.

您可能還需要執行列操作，例如重命名，創建新列等。

# select two columns from a multi-column dataset
SELECT column1, column2
FROM tableName# rename a column
SELECT
ProductName AS name
FROM productTable# new conditional column (similar to if statment)
SELECT ProductName, Price,(CASE
WHEN Price > 20 AND Price <41 THEN 'medium '
WHEN Price >40 THEN 'high'
ELSE 'low'
END) AS newColNameFROM Products

篩選行 (Filtering rows)

Filtering rows is probably the most important task you will do frequently with SQL. From a large dataset you’ll often filter rows based on product type, range of values etc.

過濾行可能是您將經常使用SQL進行的最重要的任務。 在大型數據集中，您經常會根據產品類型，值范圍等來過濾行。

If you are learning SQL you should devote a substantial amount of time learning the many different ways to filter data and the SQL statements you’ll need.

如果您正在學習SQL，則應該花大量時間學習各種不同的過濾數據和所需SQL語句的方法。

# select all records that starts with the letter "S"
SELECT * FROM Products
WHERE ProductName like 'S%'# select all records that end at "S"
SELECT * FROM Products
WHERE ProductName like '%S'# select all records that does NOT start at "S"
SELECT * FROM Products
WHERE ProductName like '[^S]%'# filter rows with specific value
SELECT * FROM table_nameWHERE firstName = 'Pilu'
OR lastName != 'Milu'
AND income <= 100
AND city IN ('Arlington', 'Burlington', 'Fairfax')# filter rows within a range of numbers
SELECT *
FROM tableName
WHERE income BETWEEN 100 AND 200 # filter null values
SELECT * FROM tableName
WHERE columnName IS NULL # opposite "IS NOT NULL"

聯接數據集 (Joining datasets)

You are using SQL in a relational database management system (RDBMS), which means you will be working with multiple tables at a time, so they need to be joined before you are able to do advanced modeling.

您正在關系數據庫管理系統(RDBMS)中使用SQL，這意味著您將一次處理多個表，因此在進行高級建模之前需要將它們連接在一起。

There are basically four ways to join data — left, right, inner, full outer joins — and you need to google a little bit to see how each works, but I’m giving all the codes below to perform these joins.

基本上有四種連接數據的方法-左，右，內部，完全外部聯接-您需要用一點點Google來了解每種方法的工作原理，但是我在下面提供了所有代碼來執行這些聯接。

# inner join (for matching records only)
SELECT * FROM
table1 INNER JOIN table2
ON table1.ID = tbale2.ID# full outer join (all left + all right)
SELECT * FROM
table1 FULL OUTER JOIN table2
ON table1.ID = tbale2.ID# left join (all records from left + matching records from right)
SELECT * FROM
table1 LEFT JOIN table2
ON table1.ID = tbale2.ID# left join (matching records from left + all records from right)
SELECT * FROM
table1 RIGHT JOIN table2
ON table1.ID = tbale2.ID

進行計算 (Doing calculations)

Creating summary statistics, mathematical operations and building models is what you do every day as a data scientist. SQL is not the right tool for much of that, however, if you need to create a quick summary statistics you can use aggregate functions to calculate column mean, totals, min/max values etc.

創建匯總統計信息，數學運算和構建模型是您作為數據科學家每天要做的事情。 SQL并不是大多數情況下的正確工具，但是，如果您需要創建快速匯總統計信息，則可以使用聚合函數來計算列平均值，總計，最小/最大值等。

# new calculated column
SELECT Price,
(Price * 2) AS NewCol
FROM Products# aggregation by group
SELECT CategoryID, SUM(Price) 
FROM Products
GROUP BY CategoryID# min/max values of a column
SELECT ProductName, MIN(Price)
FROM Products

最后的筆記 (Final notes)

The purpose of this article was to introduce some basic SQL concepts and statements for querying data from a relational database management system. But the primary objective was to show how to learn SQL as a data scientist with a mindset to solve a particular problem rather than focusing on SQL statements.

本文的目的是介紹一些基本SQL概念和語句，用于從關系數據庫管理系統中查詢數據。 但是主要目的是展示如何以解決特定問題的思維方式來學習數據科學家，而不是專注于SQL語句。