基本SQL命令-您應該知道的數據庫查詢和語句列表

SQL stands for Structured Query Language. SQL commands are the instructions used to communicate with a database to perform tasks, functions, and queries with data.

SQL代表結構化查詢語言。 SQL命令是用于與數據庫通信以執行任務，功能和數據查詢的指令。

SQL commands can be used to search the database and to do other functions like creating tables, adding data to tables, modifying data, and dropping tables.

SQL命令可用于搜索數據庫并執行其他功能，例如創建表，向表中添加數據，修改數據和刪除表。

Here is a list of basic SQL commands (sometimes called clauses) you should know if you are going to work with SQL.

這是基本SQL命令(有時稱為子句)的列表，您應該知道是否要使用SQL。

選擇和從 (SELECT and FROM)

The SELECT part of a query determines which columns of the data to show in the results. There are also options you can apply to show data that is not a table column.

查詢的SELECT部分確定要在結果中顯示數據的哪些列。您還可以應用其他選項來顯示不是表列的數據。

The example below shows three columns SELECTed FROM the “student” table and one calculated column. The database stores the studentID, FirstName, and LastName of the student. We can combine the First and the Last name columns to create the FullName calculated column.

下例顯示了FROM “學生”表中SELECT三列和一個計算所得的列。該數據庫存儲該學生的studentID，FirstName和LastName。我們可以組合名字和姓氏列來創建FullName計算列。

SELECT studentID, FirstName, LastName, FirstName + ' ' + LastName AS FullName
FROM student;

+-----------+-------------------+------------+------------------------+
| studentID | FirstName         | LastName   | FullName               |
+-----------+-------------------+------------+------------------------+
|         1 | Monique           | Davis      | Monique Davis          |
|         2 | Teri              | Gutierrez  | Teri Gutierrez         |
|         3 | Spencer           | Pautier    | Spencer Pautier        |
|         4 | Louis             | Ramsey     | Louis Ramsey           |
|         5 | Alvin             | Greene     | Alvin Greene           |
|         6 | Sophie            | Freeman    | Sophie Freeman         |
|         7 | Edgar Frank "Ted" | Codd       | Edgar Frank "Ted" Codd |
|         8 | Donald D.         | Chamberlin | Donald D. Chamberlin   |
|         9 | Raymond F.        | Boyce      | Raymond F. Boyce       |
+-----------+-------------------+------------+------------------------+
9 rows in set (0.00 sec)

創建表 (CREATE TABLE)

CREATE TABLE does just what it sounds like: it creates a table in the database. You can specify the name of the table and the columns that should be in the table.

CREATE TABLE功能聽起來很像：它在數據庫中創建一個表。您可以指定表的名稱以及應在表中的列。

CREATE TABLE table_name (column_1 datatype,column_2 datatype,column_3 datatype
);

更改表 (ALTER TABLE)

ALTER TABLE changes the structure of a table. Here is how you would add a column to a database:

ALTER TABLE更改ALTER TABLE的結構。這是將列添加到數據庫的方法：

ALTER TABLE table_name
ADD column_name datatype;

檢查 (CHECK)

The CHECK constraint is used to limit the value range that can be placed in a column.

CHECK約束用于限制可以放置在列中的值范圍。

If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

如果在單個列上定義CHECK約束，則該列僅允許某些值。如果在表上定義CHECK約束，則可以基于行中其他列中的值來限制某些列中的值。

The following SQL creates a CHECK constraint on the “Age” column when the “Persons” table is created. The CHECK constraint ensures that you can not have any person below 18 years.

創建“人員”表時，以下SQL在“年齡”列上創建CHECK約束。 CHECK約束確保您不能有18歲以下的任何人。

CREATE TABLE Persons (ID int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Age int,CHECK (Age>=18)
);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

若要命名CHECK約束，并在多個列上定義CHECK約束，請使用以下SQL語法：

CREATE TABLE Persons (ID int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Age int,City varchar(255),CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);

哪里 (WHERE)

(AND, OR, IN, BETWEEN, and LIKE)

( AND ， OR ， IN ， BETWEEN和LIKE )

The WHERE clause is used to limit the number of rows returned.

WHERE子句用于限制返回的行數。

As an example, first we will show you a SELECT statement and results without a WHERE statement. Then we will add a WHERE statement that uses all five qualifiers above.

作為示例，首先我們將向您顯示SELECT語句和不帶WHERE語句的結果。然后，我們將添加一個使用上面所有五個限定詞的WHERE語句。

SELECT studentID, FullName, sat_score, rcd_updated FROM student;

+-----------+------------------------+-----------+---------------------+
| studentID | FullName               | sat_score | rcd_updated         |
+-----------+------------------------+-----------+---------------------+
|         1 | Monique Davis          |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez         |       800 | 2017-08-16 15:34:50 |
|         3 | Spencer Pautier        |      1000 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey           |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene           |      1200 | 2017-08-16 15:34:50 |
|         6 | Sophie Freeman         |      1200 | 2017-08-16 15:34:50 |
|         7 | Edgar Frank "Ted" Codd |      2400 | 2017-08-16 15:35:33 |
|         8 | Donald D. Chamberlin   |      2400 | 2017-08-16 15:35:33 |
|         9 | Raymond F. Boyce       |      2400 | 2017-08-16 15:35:33 |
+-----------+------------------------+-----------+---------------------+
9 rows in set (0.00 sec)

Now, we'll repeat the SELECT query but we'll limit the rows returned using a WHERE statement.

現在，我們將重復執行SELECT查詢，但是將限制使用WHERE語句返回的行。

STUDENT studentID, FullName, sat_score, recordUpdated
FROM student
WHERE (studentID BETWEEN 1 AND 5 OR studentID = 8)ANDsat_score NOT IN (1000, 1400);

+-----------+----------------------+-----------+---------------------+
| studentID | FullName             | sat_score | rcd_updated         |
+-----------+----------------------+-----------+---------------------+
|         1 | Monique Davis        |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez       |       800 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey         |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene         |      1200 | 2017-08-16 15:34:50 |
|         8 | Donald D. Chamberlin |      2400 | 2017-08-16 15:35:33 |
+-----------+----------------------+-----------+---------------------+
5 rows in set (0.00 sec)

更新 (UPDATE)

To update a record in a table you use the UPDATE statement.

要更新表中的記錄，請使用UPDATE語句。

Use the WHERE condition to specify which records you want to update. It is possible to update one or more columns at a time. The syntax is:

使用WHERE條件指定要更新的記錄。可以一次更新一個或多個列。語法為：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Here is an example updating the Name of the record with Id 4:

這是一個使用ID 4更新記錄名稱的示例：

UPDATE Person
SET Name = “Elton John”
WHERE Id = 4;

You can also update columns in a table by using values from other tables. Use the JOIN clause to get data from multiple tables. The syntax is:

您還可以通過使用其他表中的值來更新表中的列。使用JOIN子句從多個表中獲取數據。語法為：

UPDATE table_name1
SET table_name1.column1 = table_name2.columnAtable_name1.column2 = table_name2.columnB
FROM table_name1
JOIN table_name2 ON table_name1.ForeignKey = table_name2.Key

Here is an example updating Manager of all records:

這是所有記錄的更新管理器的示例：

UPDATE Person
SET Person.Manager = Department.Manager
FROM Person
JOIN Department ON Person.DepartmentID = Department.ID

通過...分組 (GROUP BY)

GROUP BY allows you to combine rows and aggregate data.

GROUP BY使您可以合并行并匯總數據。

Here is the syntax of GROUP BY:

這是GROUP BY的語法：

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;

擁有 (HAVING)

HAVING allows you to filter the data aggregated by the GROUP BY clause so that the user gets a limited set of records to view.

HAVING允許您過濾GROUP BY子句聚合的數據，以便用戶獲得一組有限的記錄以供查看。

Here is the syntax of HAVING:

這是HAVING的語法：

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;

AVG() (AVG())

“Average” is used to calculate the average of a numeric column from the set of rows returned by a SQL statement.

“平均值”用于從SQL語句返回的行集中計算數字列的平均值。

Here is the syntax for using the function:

這是使用該函數的語法：

SELECT groupingField, AVG(num_field)
FROM table1
GROUP BY groupingField

Here’s an example using the student table:

這是使用學生表的示例：

SELECT studentID, FullName, AVG(sat_score) 
FROM student 
GROUP BY studentID, FullName;

如 (AS)

AS allows you to rename a column or table using an alias.

AS允許您使用別名重命名列或表。

SELECT user_only_num1 AS AgeOfServer, (user_only_num1 - warranty_period) AS NonWarrantyPeriod FROM server_table

This results in output as below.

結果如下。

+-------------+------------------------+
| AgeOfServer | NonWarrantyPeriod      | 
+-------------+------------------------+
|         36  |                     24 |
|         24  |                     12 | 
|         61  |                     49 |
|         12  |                      0 | 
|          6  |                     -6 |
|          0  |                    -12 | 
|         36  |                     24 |
|         36  |                     24 | 
|         24  |                     12 | 
+-------------+------------------------+

You can also use AS to assign a name to a table to make it easier to reference in joins.

您還可以使用AS為表分配名稱，以使其更易于在聯接中引用。

SELECT ord.product, ord.ord_number, ord.price, cust.cust_name, cust.cust_number FROM customer_table AS custJOIN order_table AS ord ON cust.cust_number = ord.cust_number

This results in output as below.

結果如下。

+-------------+------------+-----------+-----------------+--------------+
| product     | ord_number | price     | cust_name       | cust_number  |
+-------------+------------+-----------+-----------------+--------------+
|     RAM     |   12345    |       124 | John Smith      |  20          |
|     CPU     |   12346    |       212 | Mia X           |  22          |
|     USB     |   12347    |        49 | Elise Beth      |  21          |
|     Cable   |   12348    |         0 | Paul Fort       |  19          |
|     Mouse   |   12349    |        66 | Nats Back       |  15          |
|     Laptop  |   12350    |       612 | Mel S           |  36          |
|     Keyboard|   12351    |        24 | George Z        |  95          |
|     Keyboard|   12352    |        24 | Ally B          |  55          |
|     Air     |   12353    |        12 | Maria Trust     |  11          |
+-------------+------------+-----------+-----------------+--------------+

訂購 (ORDER BY)

ORDER BY gives us a way to sort the result set by one or more of the items in the SELECT section. Here is an SQL sorting the students by FullName in descending order. The default sort order is ascending (ASC) but to sort in the opposite order (descending) you use DESC.

ORDER BY提供了一種方法，可以按SELECT部分中的一個或多個項目對結果集進行排序。這是一個按FullName降序對學生進行排序SQL。默認的排序順序是升序( ASC )，但要使用相反的順序(降序)，請使用DESC 。

SELECT studentID, FullName, sat_score
FROM student
ORDER BY FullName DESC;

計數 (COUNT)

COUNT will count the number of rows and return that count as a column in the result set.

COUNT將對行數進行計數，并將該計數作為列返回到結果集中。

Here are examples of what you would use COUNT for:

以下是將COUNT用于以下用途的示例：

Counting all rows in a table (no group by required)
計算表中的所有行(不需要按組)
Counting the totals of subsets of data (requires a Group By section of the statement)
計算數據子集的總數(需要語句的“分組依據”部分)

This SQL statement provides a count of all rows. Note that you can give the resulting COUNT column a name using “AS”.

該SQL語句提供所有行的計數。請注意，您可以使用“ AS”為所得的COUNT列命名。

SELECT count(*) AS studentCount FROM student;

刪除 (DELETE)

DELETE is used to delete a record in a table.

DELETE用于刪除表中的記錄。

Be careful. You can delete all records of the table or just a few. Use the WHERE condition to specify which records you want to delete. The syntax is:

小心。您可以刪除表中的所有記錄或僅刪除一些記錄。使用WHERE條件指定要刪除的記錄。語法為：

DELETE FROM table_name
WHERE condition;

Here is an example deleting from the table Person the record with Id 3:

這是從表Person中刪除ID為3的記錄的示例：

DELETE FROM Person
WHERE Id = 3;

內部聯接 (INNER JOIN)

JOIN, also called Inner Join, selects records that have matching values in two tables.

JOIN (也稱為內部聯接)選擇兩個表中具有匹配值的記錄。

SELECT * FROM A x JOIN B y ON y.aId = x.Id

左聯接 (LEFT JOIN)

A LEFT JOIN returns all rows from the left table, and the matched rows from the right table. Rows in the left table will be returned even if there was no match in the right table. The rows from the left table with no match in the right table will have null for right table values.

LEFT JOIN返回左側表中的所有行，以及右側表中的匹配行。即使右表中沒有匹配項，也將返回左表中的行。左表中沒有匹配項的行在右表中將為null對于右表值。

SELECT * FROM A x LEFT JOIN B y ON y.aId = x.Id

正確加入 (RIGHT JOIN)

A RIGHT JOIN returns all rows from the right table, and the matched rows from the left table. Opposite of a left join, this will return all rows from the right table even where there is no match in the left table. Rows in the right table that have no match in the left table will have null values for left table columns.

RIGHT JOIN返回右側表中的所有行，以及左側表中的匹配行。與左聯接相反，這將返回右表中的所有行，即使左表中沒有匹配項也是如此。右表中與左表不匹配的行的左表列將具有null值。

SELECT * FROM A x RIGHT JOIN B y ON y.aId = x.Id

全外連接 (FULL OUTER JOIN)

A FULL OUTER JOIN returns all rows for which there is a match in either of the tables. So if there are rows in the left table that do not have matches in the right table, those will be included. Also, if there are rows in the right table that do not have matches in the left table, those will be included.

FULL OUTER JOIN返回所有表中都匹配的所有行。因此，如果左表中的行與右表中的行不匹配，則將這些行包括在內。另外，如果右表中的行與左表中的行不匹配，則將這些行包括在內。

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName

插 (INSERT)

INSERT is a way to insert data into a table.

INSERT是一種將數據插入表中的方法。

INSERT INTO table_name (column_1, column_2, column_3) 
VALUES (value_1, 'value_2', value_3);

喜歡 (LIKE)

LIKE ?is used in a WHERE or HAVING (as part of the GROUP BY) to limit the selected rows to the items when a column has a certain pattern of characters contained in it.

LIKE在WHERE或HAVING (作為GROUP BY一部分)用于在列中包含某些特定字符模式的情況下，將選定的行限制為項目。

This SQL will select students that have FullName starting with “Monique” or ending with “Greene”.

這個SQL將選擇具有學生FullName開頭“莫妮克”或“格林”的結局。

SELECT studentID, FullName, sat_score, rcd_updated
FROM student 
WHERE FullName LIKE 'Monique%' OR FullName LIKE '%Greene';

+-----------+---------------+-----------+---------------------+
| studentID | FullName      | sat_score | rcd_updated         |
+-----------+---------------+-----------+---------------------+
|         1 | Monique Davis |       400 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene  |      1200 | 2017-08-16 15:34:50 |
+-----------+---------------+-----------+---------------------+
2 rows in set (0.00 sec)

You can place NOT before LIKE to exclude the rows with the string pattern instead of selecting them. This SQL excludes records that contain “cer Pau” and “Ted” in the FullName column.

你可以把NOT前LIKE與字符串模式排除行，而不是選擇他們的。該SQL排除FullName列中包含“ cer Pau”和“ Ted”的記錄。

SELECT studentID, FullName, sat_score, rcd_updated
FROM student 
WHERE FullName NOT LIKE '%cer Pau%' AND FullName NOT LIKE '%"Ted"%';

+-----------+----------------------+-----------+---------------------+
| studentID | FullName             | sat_score | rcd_updated         |
+-----------+----------------------+-----------+---------------------+
|         1 | Monique Davis        |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez       |       800 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey         |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene         |      1200 | 2017-08-16 15:34:50 |
|         6 | Sophie Freeman       |      1200 | 2017-08-16 15:34:50 |
|         8 | Donald D. Chamberlin |      2400 | 2017-08-16 15:35:33 |
|         9 | Raymond F. Boyce     |      2400 | 2017-08-16 15:35:33 |
+-----------+----------------------+-----------+---------------------+
7 rows in set (0.00 sec)