Sql 简明教程

SQL - Syntax

What is SQL Syntax?

SQL 语法是一组独特的规则和准则，在编写 SQL 语句时需要遵循。本教程通过列出所有基本的 SQL 语法，让你快速入门 SQL。

SQL syntax is a unique set of rules and guidelines to be followed while writing SQL statements. This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax.

所有 SQL 语句都以 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP、CREATE、USE、SHOW 等关键字开头，并且所有语句都以分号 (;) 结尾。

All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;).

Case Sensitivity

这里需要指出的最重要的一点是 SQL 不区分大小写，这意味着 SELECT 和 Select 在 SQL 语句中具有相同的含义。但是，MySQL 对表名有所不同。因此，如果你使用 MySQL，那么你需要按照表在数据库中存在的形式给出表名。

The most important point to be noted here is that SQL is case insensitive, which means SELECT and Select have same meaning in SQL statements. Whereas, MySQL makes difference in table names. So, if you are working with MySQL, then you need to give table names as they exist in the database.

SQL Table

我们考虑一下下面所示名为 CUSTOMERS 的表，并使用它作为引用，在同一表上演示所有 SQL 语句。

Let us consider a table with the name CUSTOMERS shown below, and use it as a reference to demonstrate all the SQL Statements on the same.

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

SQL Statements

本教程列举了各种 SQL 语句。其中大多数与 MySQL、Oracle、Postgres 和 SQL Server 数据库兼容。

This tutorial lists down various SQL statements. Most of them are compatible with MySQL, Oracle, Postgres and SQL Server databases.

所有 SQL 语句的末尾都要以 semicolon (;) 结尾。分号是分割不同 SQL 语句的标准方法，它允许在一行中包含多个 SQL 语句。

All the SQL statements require a semicolon (;) at the end of each statement. Semicolon is the standard way to separate different SQL statements which allows to include multiple SQL statements in a single line.

SQL CREATE DATABASE Statement

要在数据库内存储数据，首先需要创建该数据库。有必要为属于组织的数据定制个性化服务。

To store data within a database, you first need to create it. This is necessary to individualize the data belonging to an organization.

可以使用以下语法创建数据库 −

You can create a database using the following syntax −

CREATE DATABASE database_name;

让我们尝试使用 CREATE DATABASE 语句在 SQL 中创建一个 sampleDB 样本数据库 −

Let us try to create a sample database sampleDB in SQL using the CREATE DATABASE statement −

CREATE DATABASE sampleDB

SQL USE Statement

创建数据库后，需要使用它来开始相应地存储数据。以下是将当前位置更改为所需数据库的语法 −

Once the database is created, it needs to be used in order to start storing the data accordingly. Following is the syntax to change the current location to required database −

USE database_name;

我们可以使用 SQL 中的 USE 语句将先前创建的 sampleDB 设置为默认数据库 −

We can set the previously created sampleDB as the default database by using the USE statement in SQL −

USE sampleDB;

SQL DROP DATABASE Statement

如果不再需要一个数据库，也可以将其删除。要删除/丢弃数据库，请使用以下语法 −

If a database is no longer necessary, you can also delete it. To delete/drop a database, use the following syntax −

DROP DATABASE database_name;

你还可以使用 SQL 中的 DROP DATABASE 语句删除 sampleDB 数据库 −

You can also drop the sampleDB database by using the DROP DATABASE statement in SQL −

DROP DATABASE sampleDB;

SQL CREATE TABLE Statement

在 SQL 驱动的数据库中，数据以结构化方式存储，即以表的形式。要创建表，请使用以下语法 −

In an SQL driven database, the data is stored in a structured manner, i.e. in the form of tables. To create a table, following syntax is used −

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

以下代码块是一个示例，它创建了上面给出的 CUSTOMERS 表，其中 ID 是主键，NOT NULL 是约束，表明创建此表中的记录时，这些字段不能为 NULL −

The following code block is an example, which creates a CUSTOMERS table given above, with an ID as a primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table −

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

SQL DESC Statement

数据库中的每个表都有自己的结构。要显示数据库表的结构，我们使用 DESC 语句。以下是语法 −

Every table in a database has a structure of its own. To display the structure of database tables, we use the DESC statements. Following is the syntax −

DESC table_name;

然而，DESC 语句仅在少数 RDBMS 系统中起作用；因此，让我们看看在 MySQL 服务器中使用 DESC 语句的一个示例 −

The DESC Statement, however, only works in few RDBMS systems; hence, let us see an example by using DESC statement in the MySQL server −

DESC CUSTOMERS;

SQL INSERT INTO Statement

SQL INSERT INTO 语句用于将数据插入数据库表中。以下是语法 −

The SQL INSERT INTO Statement is used to insert data into database tables. Following is the syntax −

INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);

以下示例语句将在空的 CUSTOMERS 表中创建七条记录。

The following example statements would create seven records in the empty CUSTOMERS table.

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500),
(3, 'kaushik', 23, 'Kota', 2000),
(4, 'Chaitali', 25, 'Mumbai', 6500),
(5, 'Hardik', 27, 'Bhopal', 8500),
(6, 'Komal', 22, 'Hyderabad', 4500),
(7, 'Muffy', 24, 'Indore', 10000);

SQL SELECT Statement

为了从数据库表中检索存储数据的 result-sets，我们使用了 SELECT 语句。以下是语法 −

In order to retrieve the result-sets of the stored data from a database table, we use the SELECT statement. Following is the syntax −

SELECT column1, column2....columnN FROM table_name;

要从 CUSTOMERS 表中检索数据，我们使用如下所示的 SELECT 语句。

To retrieve the data from CUSTOMERS table, we use the SELECT statement as shown below.

SELECT * FROM CUSTOMERS;

SQL UPDATE Statement

当数据库表中的存储数据已过期并且需要在不删除表的情况下进行更新时，我们使用 UPDATE 语句。以下是语法 −

When the stored data in a database table is outdated and needs to be updated without having to delete the table, we use the UPDATE statement. Following is the syntax −

UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE  CONDITION ];

举例来说，以下查询将更新表中 ID 号为 6 的客户 ADDRESS。

To see an example, the following query will update the ADDRESS for a customer whose ID number is 6 in the table.

UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6;

SQL DELETE Statement

无需从数据库中删除整个表，您还可以通过应用条件来删除特定数据部分。这是使用 DELETE FROM 语句来完成的。以下是语法：

Without deleting the entire table from the database, you can also delete a certain part of the data by applying conditions. This is done using the DELETE FROM statement. Following is the syntax −

DELETE FROM table_name WHERE  {CONDITION};

下面的代码包含一个查询，该查询将 DELETE 一个其 ID 为 6 的客户。

The following code has a query, which will DELETE a customer, whose ID is 6.

DELETE FROM CUSTOMERS WHERE ID = 6;

SQL DROP TABLE Statement

当不再需要某个表时，要让它从数据库中被完全删除，使用以下语法：

To delete a table entirely from a database when it is no longer needed, following syntax is used −

DROP TABLE table_name;

此查询会将 CUSTOMERS 表从数据库中删除。

This query will drop the CUSTOMERS table from the database.

DROP TABLE CUSTOMERS;

SQL TRUNCATE TABLE Statement

SQL 中实现了 TRUNCATE TABLE 语句，用于删除表数据，但不是表本身。当使用此 SQL 语句时，该表将像空表一样保留在数据库中。以下是语法：

The TRUNCATE TABLE statement is implemented in SQL to delete the data of the table but not the table itself. When this SQL statement is used, the table stays in the database like an empty table. Following is the syntax −

TRUNCATE TABLE table_name;

以下查询将删除 CUSTOMERS 表的所有记录：

Following query delete all the records of the CUSTOMERS table −

TRUNCATE TABLE CUSTOMERS;

SQL ALTER TABLE Statement

ALTER TABLE 语句用于更改表的结构。例如，您可以使用此语句来添加、删除和修改列的数据。以下是语法：

The ALTER TABLE statement is used to alter the structure of a table. For instance, you can add, drop, and modify the data of a column using this statement. Following is the syntax −

ALTER TABLE table_name
{ADD|DROP|MODIFY} column_name {data_type};

以下是使用 ALTER TABLE 命令向 CUSTOMERS 表中 ADD 一个新列的示例：

Following is the example to ADD a New Column to the CUSTOMERS table using ALTER TABLE command −

ALTER TABLE CUSTOMERS ADD SEX char(1);

SQL ALTER TABLE Statement (Rename)

ALTER TABLE 语句还用于更改表名。使用以下语法：

The ALTER TABLE statement is also used to change the name of a table as well. Use the syntax below −

ALTER TABLE table_name RENAME TO new_table_name;

以下是使用 ALTER TABLE 命令来 RENAME CUSTOMERS 表的示例：

Following is the example to RENAME the CUSTOMERS table using ALTER TABLE command −

ALTER TABLE CUSTOMERS RENAME TO NEW_CUSTOMERS;

SQL DISTINCT Clause

数据库中的 DISTINCT 子句用于识别列中的非重复数据。使用 SELECT DISTINCT 语句，您可以从列中检索不同的值。以下是语法：

The DISTINCT clause in a database is used to identify the non-duplicate data from a column. Using the SELECT DISTINCT statement, you can retrieve distinct values from a column. Following is the syntax −

SELECT DISTINCT column1, column2....columnN FROM   table_name;

作为一个示例，我们使用 DISTINCT 关键字以及一个 SELECT 查询。重复的薪资 2000.00 将只被检索一次，而忽略其他记录。

As an example, let us use the DISTINCT keyword with a SELECT query. The repetitive salary 2000.00 will only be retrieved once and the other record is ignored.

SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

SQL WHERE Clause

WHERE 子句用于通过应用条件从表中筛选行。以下是检索表中经过筛选的行时的语法：

The WHERE clause is used to filter rows from a table by applying a condition. Following is the syntax to retrieve filtered rows from a table −

SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION;

以下查询是一个示例，用于使用 SELECT 语句从 CUSTOMERS 表中获取所有薪资大于 2000 的记录：

The following query is an example to fetch all the records from CUSTOMERS table where the salary is greater than 2000, using the SELECT statement −

SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE SALARY > 2000;

SQL AND/OR Operators

AND/OR 运算符用于在 WHERE 子句中应用多个条件。以下是语法：

The AND/OR Operators are used to apply multiple conditions in the WHERE clause. Following is the syntax −

SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION-1 {AND|OR} CONDITION-2;

以下查询是一个示例，用于使用 SELECT 语句从 CUSTOMERS 表中获取所有薪资大于 2000 并且年龄小于 25 的记录：

The following query is an example to fetch all the records from CUSTOMERS table where the salary is greater than 2000 AND age is less than 25, using the SELECT statement −

SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 AND age < 25;

SQL IN Clause

IN 运算符用于使用 WHERE 子句来检查数据是否存在于列中。以下是语法：

The IN Operator is used to check whether the data is present in the column or not, using the WHERE clause. Following is the syntax −

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name IN (val-1, val-2,...val-N);

例如，我们要使用 IN 运算符来显示 NAME 等于“Khilan”、“Hardik”和“Muffy”（字符串值）的记录，如下所示：

For an example, we want to display records with NAME equal to 'Khilan', 'Hardik' and 'Muffy' (string values) using IN operator as follows −

SELECT * FROM CUSTOMERS
WHERE NAME IN ('Khilan', 'Hardik', 'Muffy');

SQL BETWEEN Clause

BETWEEN 运算符用于使用 WHERE 子句从表中检索介于某个范围内的值。以下是语法：

The BETWEEN Operator is used to retrieve the values from a table that fall in a certain range, using the WHERE clause. Following is the syntax −

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name BETWEEN val-1 AND val-2;

让我们尝试使用 BETWEEN 运算符来检索 AGE 介于 20 到 25 之间的 CUSTOMERS 记录。

Let us try to the BETWEEN operator to retrieve CUSTOMERS records whose AGE is between 20 and 25.

SELECT * FROM CUSTOMERS WHERE AGE BETWEEN 20 AND 25;

SQL LIKE Clause

LIKE 运算符用于使用 WHERE 子句从表中检索与特定模式匹配的值。以下是语法 −

The LIKE Operator is used to retrieve the values from a table that match a certain pattern, using the WHERE clause. Following is the syntax −

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name LIKE { PATTERN };

作为一个示例，让我们尝试显示 CUSTOMERS 表中的所有记录，其中 SALARY 以 200 开头。

As an example, let us try to display all the records from the CUSTOMERS table, where the SALARY starts with 200.

SELECT * FROM CUSTOMERS WHERE SALARY LIKE '200%';

SQL ORDER BY Clause

ORDER BY 子句用于按给定/指定顺序排列列值。以下是语法 −

The ORDER BY Clause is used to arrange the column values in a given/specified order. Following is the syntax −

SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION
ORDER BY column_name {ASC|DESC};

在以下示例中，我们尝试按客户名称的字母顺序将结果按升序排列 −

In the following example we are trying to sort the result in an ascending order by the alphabetical order of customer names −

SELECT * FROM CUSTOMERS ORDER BY NAME ASC;

SQL GROUP BY Clause

GROUP BY 子句用于将列值分组在一起。以下是语法 −

The GROUP BY Clause is used to group the values of a column together. Following is the syntax −

SELECT SUM(column_name)
FROM   table_name
WHERE  CONDITION
GROUP BY column_name;

我们尝试按客户的年龄对客户进行分组，并使用以下查询计算每个年龄组的平均工资 −

We are trying to group the customers by their age and calculate the average salary for each age group using the following query −

SELECT ADDRESS, AGE, SUM(SALARY)
AS TOTAL_SALARY FROM CUSTOMERS
GROUP BY ADDRESS, AGE;

SQL COUNT Function

COUNT 函数提供了指定列中存在的非空值的数目。以下是语法 −

The COUNT Function gives the number of non-null values present in the specified column. Following is the syntax −

SELECT COUNT(column_name)
FROM   table_name
WHERE  CONDITION;

让我们看一个示例——

Let us see an example −

SELECT AGE, COUNT(Name) FROM CUSTOMERS GROUP BY AGE;

SQL HAVING Clause

HAVING 子句还用于通过应用条件来筛选一组行。以下是语法 −

The HAVING clause is also used to filter a group of rows by applying a condition. Following is the syntax −

SELECT SUM(column_name)
FROM   table_name
WHERE  CONDITION
GROUP BY column_name
HAVING (arithematic function condition);

在以下示例中，我们尝试从 CUSTOMERS 表中检索所有记录，其中其工资总和大于 5000 −

In the following example, we are trying to retrieve all records from the CUSTOMERS table where the sum of their salary is greater than 5000 −

SELECT ADDRESS, AGE, SUM(SALARY) AS
TOTAL_SALARY FROM CUSTOMERS GROUP BY
ADDRESS, AGE HAVING TOTAL_SALARY >=5000
ORDER BY TOTAL_SALARY DESC;

SQL CREATE INDEX Statement

为了在数据库表上创建索引，SQL 提供了 CREATE INDEX 语句。以下是语法 −

To create an index on a database table, SQL provides the CREATE INDEX statement. Following is the syntax −

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);

让我们使用以下查询为现有 CUSTOMERS 表中名为 'NAME' 的列创建索引 −

Let us create an index for the column named 'NAME' in the existing CUSTOMERS table using the following query −

CREATE INDEX sample_index on CUSTOMERS(NAME);

SQL DROP INDEX Statement

DROP INDEX 语句用于从表中删除索引。以下是语法 −

The DROP INDEX statement is used to drop an index from a table. Following is the syntax −

DROP INDEX index_name ON table_name;

让我们使用以下查询删除先前为现有 CUSTOMERS 表中名为 'NAME' 的列创建的索引 −

Let us drop the index we created previously for the column named 'NAME' in the existing CUSTOMERS table using the following query −

DROP INDEX sample_index on CUSTOMERS;