原文: Basic SQL Commands - The List of Database Queries and Statements You Should Know
SQL 是 Structured Query Language(结构化查询语言)的缩写。SQL 命令是用于与数据库通信的指令,以执行任务、功能和对数据的查询。
SQL 命令可用于搜索数据库和执行其他功能,如创建表、向表添加数据、修改数据和删除表。
下面是一个基本的 SQL 命令(有时称为子句)的列表,如果你要使用 SQL,你应该知道这些命令。
SELECT 和 FROM
查询的 SELECT
部分决定了在结果中显示哪些列的数据。还有一些选项,你可以应用来显示不是表列的数据。
下面的例子显示了从 “student” 表中选取的三列和一个计算列。数据库存储了学生的 studentID、FirstName 和 LastName。我们可以结合 First 和 Last name 列来创建 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
就像它听起来那样:它在数据库中创建一个表。你可以指定表的名称和应该在表中出现的列。
CREATE TABLE table_name (
column_1 datatype,
column_2 datatype,
column_3 datatype
);
ALTER TABLE
ALTER TABLE
改变一个表的结构。下面是你如何在数据库中增加一个列:
ALTER TABLE table_name
ADD column_name datatype;
CHECK
CHECK
约束是用来限制可以放在一列中的数值范围。
如果你在一个单列上定义 CHECK
约束,对于这个列它只允许某些值。如果你在一个表上定义一个 CHECK
约束,它可以根据行中其他列的值来限制某些列的值。
下面的 SQL 语句在创建 “Persons” 表时在 “Age” 列上创建了一个 CHECK
约束。这个 CHECK
约束确保不能有任何低于 18 岁的人。
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
为了允许 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
和 LIKE
)
WHERE
子句是用来限制返回的行数的。
作为一个例子,首先我们将向你展示一个没有 WHERE
语句的 SELECT
语句和结果。然后我们将添加一个使用上述所有五个限定词的 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)
现在,我们将重复 SELECT
查询,但我们将使用 WHERE
语句限制返回的行。
STUDENT studentID, FullName, sat_score, recordUpdated
FROM student
WHERE (studentID BETWEEN 1 AND 5 OR studentID = 8)
AND
sat_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
要更新一个表中的记录,你需要使用 UPDATE
语句。
使用 WHERE
条件来指定你要更新的记录,可以一次更新一个或多个列,语法是:
UPDATE table_name
SET column1 = value1,
column2 = value2, ...
WHERE condition;
下面的例子更新 Id 为 4 的记录的名称:
UPDATE Person
SET Name = “Elton John”
WHERE Id = 4;
你也可以通过使用其他表的值来更新表中的列。使用 JOIN
子句可以从多个表中获取数据,语法是:
UPDATE table_name1
SET table_name1.column1 = table_name2.columnA
table_name1.column2 = table_name2.columnB
FROM table_name1
JOIN table_name2 ON table_name1.ForeignKey = table_name2.Key
下面的例子是更新所有记录的经理:
UPDATE Person
SET Person.Manager = Department.Manager
FROM Person
JOIN Department ON Person.DepartmentID = Department.ID
GROUP BY
GROUP BY
允许你合并行和汇总数据。
下面是 GROUP BY
的语法:
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;
HAVING
HAVING
允许你过滤由 GROUP BY
子句聚合的数据,这样用户就可以得到有限的记录集来查看。
这是 HAVING
的语法:
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;
AVG()
“Average” 用于计算由 SQL 语句返回的多行中的数字列的平均值。
下面是该函数的语法:
SELECT groupingField, AVG(num_field)
FROM table1
GROUP BY groupingField
下面是一个使用学生表的例子:
SELECT studentID, FullName, AVG(sat_score)
FROM student
GROUP BY studentID, FullName;
AS
AS
允许你使用别名重新命名一个列或表。
SELECT user_only_num1 AS AgeOfServer, (user_only_num1 - warranty_period) AS NonWarrantyPeriod FROM server_table
输出结果如下:
+-------------+------------------------+
| AgeOfServer | NonWarrantyPeriod |
+-------------+------------------------+
| 36 | 24 |
| 24 | 12 |
| 61 | 49 |
| 12 | 0 |
| 6 | -6 |
| 0 | -12 |
| 36 | 24 |
| 36 | 24 |
| 24 | 12 |
+-------------+------------------------+
你也可以使用 AS
为表指定一个名称,使其在连接中更容易引用。
SELECT ord.product, ord.ord_number, ord.price, cust.cust_name, cust.cust_number FROM customer_table AS cust
JOIN order_table AS ord ON cust.cust_number = ord.cust_number
输出结果如下:
+-------------+------------+-----------+-----------------+--------------+
| 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
为我们提供了一种方法,通过 SELECT
部分中的一个或多个项目对结果集进行排序。下面是一个按 FullName 对学生进行降序排序的 SQL。默认的排序顺序是升序(ASC
),但如果要以相反的顺序(降序)排序,你可以使用 DESC
。
SELECT studentID, FullName, sat_score
FROM student
ORDER BY FullName DESC;
COUNT
COUNT
将计算行数,并在结果集中以列的形式返回该计数。
下面是使用 COUNT
的例子:
- 计算一个表中的所有行数(不需要分组)
- 计算数据子集的总数(需要在语句中加入
Group By
部分)
这个 SQL 语句提供了一个所有行的计数。注意,你可以用 AS
给产生的 COUNT
列一个名称。
SELECT count(*) AS studentCount FROM student;
DELETE
DELETE
是用来删除一个表中的记录。
请注意,你可以删除该表的所有记录,也可以只删除几条。使用 WHERE
条件来指定你要删除哪些记录,语法是:
DELETE FROM table_name
WHERE condition;
下面是一个从表 Person 中删除 Id 为 3 的记录的例子:
DELETE FROM Person
WHERE Id = 3;
INNER JOIN
JOIN
,也叫 Inner Join,在两个表中选择具有匹配值的记录。
SELECT * FROM A x JOIN B y ON y.aId = x.Id
LEFT JOIN
LEFT JOIN
返回左表的所有记录,以及右表的匹配记录。即使右表中没有匹配的记录,左表中的记录也会被返回。左表中的行如果在右表中没有匹配,那么右表的值将为 null
。
SELECT * FROM A x LEFT JOIN B y ON y.aId = x.Id
RIGHT JOIN
RIGHT JOIN
返回右表的所有记录,以及左表的匹配记录。与 LEFT JOIN
相反,这将返回右表的所有记录,即使在左表中没有匹配的记录。右表中的行如果在左表中没有匹配,那么左表的列就会有 null
。
SELECT * FROM A x RIGHT JOIN B y ON y.aId = x.Id
FULL OUTER JOIN
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
是一种向表中插入数据的方法。
INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, 'value_2', value_3);
LIKE
LIKE
在 WHERE
或 HAVING
中使用(作为 GROUP BY
的一部分),在包含某种模式的字符的列中选出行。
这个 SQL 语句将选择 FullName
以 “Monique” 开始或以 “Greene” 结束的学生。
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)
你可以在 LIKE
之前放置 NOT
,以排除具有字符串模式的记录,而不是选择它们。这个 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)