Sql 简明教程

SQL - Quick Guide

SQL - Overview

SQL 是一种操作数据库的语言；它包括创建数据库、删除数据库、获取行、修改行等。SQL 是 ANSI （美国国家标准学会）标准语言，但 SQL 语言存在许多不同版本。

SQL is a language to operate databases; it includes database creation, deletion, fetching rows, modifying rows, etc. SQL is an ANSI (American National Standards Institute) standard language, but there are many different versions of the SQL language.

What is SQL?

SQL 是结构化查询语言，是一种用于存储、操作和检索存储在关系数据库中的数据的计算机语言。

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database.

SQL 是关系数据库系统的标准语言。所有关系数据库管理系统 (RDMS) 如 MySQL、MS Access、Oracle、Sybase、Informix、Postgres 和 SQL Server 都使用 SQL 作为其标准数据库语言。

SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.

此外，它们还使用不同的方言，例如：

Also, they are using different dialects, such as −

MS SQL Server using T-SQL,
Oracle using PL/SQL,
MS Access version of SQL is called JET SQL (native format) etc.

Why SQL?

SQL 广受欢迎，因为它提供了以下优势：

SQL is widely popular because it offers the following advantages −

Allows users to access data in the relational database management systems.
Allows users to describe the data.
Allows users to define the data in a database and manipulate that data.
Allows to embed within other languages using SQL modules, libraries & pre-compilers.
Allows users to create and drop databases and tables.
Allows users to create view, stored procedure, functions in a database.
Allows users to set permissions on tables, procedures and views.

A Brief History of SQL

1970 − Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational databases. He described a relational model for databases.
1974 − Structured Query Language appeared.
1978 − IBM worked to develop Codd’s ideas and released a product named System/R.
1986 − IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software which later came to be known as Oracle.

SQL Process

当你为任何 RDBMS 执行 SQL 命令时，系统会确定执行你的请求的最佳方式，而 SQL 引擎会弄清楚如何解释该任务。

When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task.

此过程中包含多个组件。

There are various components included in this process.

这些组件包括：

These components are −

Query Dispatcher
Optimization Engines
Classic Query Engine
SQL Query Engine, etc.

经典的查询引擎处理所有非 SQL 查询，但 SQL 查询引擎不会处理逻辑文件。

A classic query engine handles all the non-SQL queries, but a SQL query engine won’t handle logical files.

以下是一个简单的图表，显示了 SQL 架构：

Following is a simple diagram showing the SQL Architecture −

SQL Commands

与关系数据库交互的标准 SQL 命令包括 CREATE、SELECT、INSERT、UPDATE、DELETE 和 DROP。根据其特性，可以将这些命令归类为以下组：

The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into the following groups based on their nature −

DDL - Data Definition Language

Sr.No.

Command & Description

CREATE Creates a new table, a view of a table, or other object in the database.

ALTER Modifies an existing database object, such as a table.

DROP Deletes an entire table, a view of a table or other objects in the database.

DML - Data Manipulation Language

Sr.No.	Command & Description
1	SELECT Retrieves certain records from one or more tables.
2	INSERT Creates a record.
3	UPDATE Modifies records.
4	DELETE Deletes records.

DCL - Data Control Language

Sr.No.

Command & Description

GRANT Gives a privilege to user.

REVOKE Takes back privileges granted from user.

SQL - RDBMS Concepts

What is RDBMS?

RDBMS 代表 *R*elational *D*atabase *M*anagement *S*ystem。RDBMS 是 SQL 和所有现代数据库系统（例如 MS SQL Server、IBM DB2、Oracle、MySQL 和 Microsoft Access）的基础。

RDBMS stands for *R*elational *D*atabase *M*anagement *S*ystem. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

关系数据库管理系统 (RDBMS) 是一个数据库管理系统 (DBMS)，其基于 E. F. Codd 引入的关系模型。

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

What is a table?

RDBMS 中的数据存储在称为 tables 的数据库对象中。此表基本上是相关数据项的集合，它由多个列和行组成。

The data in an RDBMS is stored in database objects which are called as tables. This table is basically a collection of related data entries and it consists of numerous columns and rows.

请记住，表是关系型数据库中供数据存储的最基本和最常见的形式。下列程序是 CUSTOMERS 表的一个示例 −

Remember, a table is the most common and simplest form of data storage in a relational database. The following program is an example of a CUSTOMERS table −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

What is a field?

每个表都分解为更小的实体，称为字段。CUSTOMERS 表中的字段包括 ID、NAME、AGE、ADDRESS 和 SALARY。

Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY.

字段是表中专门用于维护表中每条记录的特定信息的列。

A field is a column in a table that is designed to maintain specific information about every record in the table.

What is a Record or a Row?

记录也称为数据行，是表中存在的每个单独条目。例如，上方的 CUSTOMERS 表中有 7 条记录。以下是 CUSTOMERS 表中的一行数据或记录：

A record is also called as a row of data is each individual entry that exists in a table. For example, there are 7 records in the above CUSTOMERS table. Following is a single row of data or record in the CUSTOMERS table −

+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
+----+----------+-----+-----------+----------+

记录是表中的水平实体。

A record is a horizontal entity in a table.

What is a column?

列是表中的垂直实体，包含与表中特定字段关联的所有信息。

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

例如，CUSTOMERS 表中的一列是 ADDRESS，表示位置描述，如下所示 −

For example, a column in the CUSTOMERS table is ADDRESS, which represents location description and would be as shown below −

+-----------+
| ADDRESS   |
+-----------+
| Ahmedabad |
| Delhi     |
| Kota      |
| Mumbai    |
| Bhopal    |
| MP        |
| Indore    |
+----+------+

What is a NULL value?

表中的 NULL 值是字段中看似空白的值，这意味着带有 NULL 值的字段是没有值。

A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.

了解 NULL 值与零值或包含空格的字段之间的不同非常重要。带有 NULL 值的字段是记录创建期间留空的字段。

It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is the one that has been left blank during a record creation.

SQL Constraints

约束是对表上的数据列实施的规则。它们用于限制可以进入表的类型。这确保了数据库中数据的准确性和可靠性。

Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

约束可以是列级别或表级别。列级别约束仅应用于一列，而表级别约束应用于整个表。

Constraints can either be column level or table level. Column level constraints are applied only to one column whereas, table level constraints are applied to the entire table.

以下是 SQL 中一些最常用的约束：

Following are some of the most commonly used constraints available in SQL −

NOT NULL Constraint − Ensures that a column cannot have a NULL value.
UNIQUE Constraint − Ensures that all the values in a column are different.
PRIMARY Key − Uniquely identifies each row/record in a database table.
FOREIGN Key − Uniquely identifies a row/record in any another database table.
INDEX − Used to create and retrieve data from the database very quickly.

Data Integrity

每个 RDBMS 都有以下数据完整性类别 −

The following categories of data integrity exist with each RDBMS −

Entity Integrity − There are no duplicate rows in a table.
Domain Integrity − Enforces valid entries for a given column by restricting the type, the format, or the range of values.
Referential integrity − Rows cannot be deleted, which are used by other records.
User-Defined Integrity − Enforces some specific business rules that do not fall into entity, domain or referential integrity.

Database Normalization

数据库范式化是有效组织数据库中数据的过程。此范式化过程有以下两个原因 −

Database normalization is the process of efficiently organizing data in a database. There are two reasons of this normalization process −

Eliminating redundant data, for example, storing the same data in more than one table.
Ensuring data dependencies make sense.

这两个原因都是有价值的目标，因为它们减少了数据库消耗的空间，并确保数据以逻辑方式存储。范式化包含一系列指南，可帮助您创建良好的数据库结构。

Both these reasons are worthy goals as they reduce the amount of space a database consumes and ensures that data is logically stored. Normalization consists of a series of guidelines that help guide you in creating a good database structure.

范式化准则分为范式；将范式视为数据库结构的格式或布局方式。范式旨在组织数据库结构，以便符合第一范式的规则，然后是第二范式，最后是第三范式。

Normalization guidelines are divided into normal forms; think of a form as the format or the way a database structure is laid out. The aim of normal forms is to organize the database structure, so that it complies with the rules of first normal form, then second normal form and finally the third normal form.

您选择更进一步，进入第四范式、第五范式等，但通常情况下，第三范式已经足够了。

It is your choice to take it further and go to the fourth normal form, fifth normal form and so on, but in general, the third normal form is more than enough.

SQL - RDBMS Databases

有很多流行的 RDBMS 可供使用。本教程简要概述了一些最流行的 RDBMS。这将帮助您比较其基本功能。

There are many popular RDBMS available to work with. This tutorial gives a brief overview of some of the most popular RDBMSâ€™s. This would help you to compare their basic features.

MySQL

MySQL 是一个开源 SQL 数据库，由一家瑞典公司开发 − MySQL AB。MySQL 的发音为“my ess-que-ell”，与“sequel”的发音“sequel”形成对比。

MySQL is an open source SQL database, which is developed by a Swedish company â€“ MySQL AB. MySQL is pronounced as "my ess-que-ell," in contrast with SQL, pronounced "sequel."

MySQL 支持许多不同的平台，包括 Microsoft Windows、主要的 Linux 发行版、UNIX 和 Mac OS X。

MySQL is supporting many different platforms including Microsoft Windows, the major Linux distributions, UNIX, and Mac OS X.

MySQL 具有免费和付费版本，具体取决于其用法（非商业/商业）和特性。MySQL 附带一个非常快速、多线程、多用户且稳定的 SQL 数据库服务器。

MySQL has free and paid versions, depending on its usage (non-commercial/commercial) and features. MySQL comes with a very fast, multi-threaded, multi-user and robust SQL database server.

History

Development of MySQL by Michael Widenius & David Axmark beginning in 1994.
First internal release on 23rd May 1995.
Windows Version was released on the 8th January 1998 for Windows 95 and NT.
Version 3.23: beta from June 2000, production release January 2001.
Version 4.0: beta from August 2002, production release March 2003 (unions).
Version 4.1: beta from June 2004, production release October 2004.
Version 5.0: beta from March 2005, production release October 2005.
Sun Microsystems acquired MySQL AB on the 26th February 2008.
Version 5.1: production release 27th November 2008.

Features

High Performance.
High Availability.
Scalability and Flexibility Run anything.
Robust Transactional Support.
Web and Data Warehouse Strengths.
Strong Data Protection.
Comprehensive Application Development.
Management Ease.
Open Source Freedom and 24 x 7 Support.
Lowest Total Cost of Ownership.

MS SQL Server

MS SQL Server是由Microsoft Inc.开发的关系型数据库管理系统。其主要查询语言为-

MS SQL Server is a Relational Database Management System developed by Microsoft Inc. Its primary query languages are −

T-SQL
ANSI SQL

History

1987 - Sybase releases SQL Server for UNIX.
1988 - Microsoft, Sybase, and Aston-Tate port SQL Server to OS/2.
1989 - Microsoft, Sybase, and Aston-Tate release SQL Server 1.0 for OS/2.
1990 - SQL Server 1.1 is released with support for Windows 3.0 clients.
Aston - Tate drops out of SQL Server development.
2000 - Microsoft releases SQL Server 2000.
2001 - Microsoft releases XML for SQL Server Web Release 1 (download).
2002 - Microsoft releases SQLXML 2.0 (renamed from XML for SQL Server).
2002 - Microsoft releases SQLXML 3.0.
2005 - Microsoft releases SQL Server 2005 on November 7th, 2005.

Features

High Performance
High Availability
Database mirroring
Database snapshots
CLR integration
Service Broker
DDL triggers
Ranking functions
Row version-based isolation levels
XML integration
TRY…CATCH
Database Mail

ORACLE

它是一个非常庞大的多用户基于数据库管理系统。Oracle 是由“Oracle 公司”开发的关系数据库管理系统。

It is a very large multi-user based database management system. Oracle is a relational database management system developed by 'Oracle Corporation'.

Oracle 能够高效管理其资源，在网络中发送和请求数据的众多客户中的一个信息数据库。

Oracle works to efficiently manage its resources, a database of information among the multiple clients requesting and sending data in the network.

它是一款适用于客户端/服务器计算的优秀数据库服务器选择。Oracle 支持适用于客户端和服务器的所有主要操作系统，包括 MSDOS、NetWare、UnixWare、OS/2 和大多数 UNIX 版本。

It is an excellent database server choice for client/server computing. Oracle supports all major operating systems for both clients and servers, including MSDOS, NetWare, UnixWare, OS/2 and most UNIX flavors.

History

Oracle 于 1977 年创立，在业内庆祝其 32 个美好的年头（从 1977 年到 2009 年）。

Oracle began in 1977 and celebrating its 32 wonderful years in the industry (from 1977 to 2009).

1977 - Larry Ellison, Bob Miner and Ed Oates founded Software Development Laboratories to undertake development work.
1979 - Version 2.0 of Oracle was released and it became first commercial relational database and first SQL database. The company changed its name to Relational Software Inc. (RSI).
1981 - RSI started developing tools for Oracle.
1982 - RSI was renamed to Oracle Corporation.
1983 - Oracle released version 3.0, rewritten in C language and ran on multiple platforms.
1984 - Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency, etc.
1985 - Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency, etc.
2007 - Oracle released Oracle11g. The new version focused on better partitioning, easy migration, etc.

Features

Concurrency
Read Consistency
Locking Mechanisms
Quiesce Database
Portability
Self-managing database
SQL*Plus
ASM
Scheduler
Resource Manager
Data Warehousing
Materialized views
Bitmap indexes
Table compression
Parallel Execution
Analytic SQL
Data mining
Partitioning

MS ACCESS

这是最流行的 Microsoft 产品之一。Microsoft Access 是一款入门级数据库管理软件。MS Access 数据库不仅价格低廉，而且是小规模项目的强大数据库。

This is one of the most popular Microsoft products. Microsoft Access is an entry-level database management software. MS Access database is not only inexpensive but also a powerful database for small-scale projects.

MS Access 使用 Jet 数据库引擎，它采用特定的 SQL 语言方言（有时称为 Jet SQL）。

MS Access uses the Jet database engine, which utilizes a specific SQL language dialect (sometimes referred to as Jet SQL).

MS Access 随附在 MS Office 套件专业版中。MS Access 具有易于使用的直观图形界面。

MS Access comes with the professional edition of MS Office package. MS Access has easyto-use intuitive graphical interface.

1992 - Access version 1.0 was released.
1993 - Access 1.1 released to improve compatibility with inclusion the Access Basic programming language.
The most significant transition was from Access 97 to Access 2000.
2007 - Access 2007, a new database format was introduced ACCDB which supports complex data types such as multi valued and attachment fields.

Features

Users can create tables, queries, forms and reports and connect them together with macros.
Option of importing and exporting the data to many formats including Excel, Outlook, ASCII, dBase, Paradox, FoxPro, SQL Server, Oracle, ODBC, etc.
There is also the Jet Database format (MDB or ACCDB in Access 2007), which can contain the application and data in one file. This makes it very convenient to distribute the entire application to another user, who can run it in disconnected environments.
Microsoft Access offers parameterized queries. These queries and Access tables can be referenced from other programs like VB6 and .NET through DAO or ADO.
The desktop editions of Microsoft SQL Server can be used with Access as an alternative to the Jet Database Engine.
Microsoft Access is a file server-based database. Unlike the client-server relational database management systems (RDBMS), Microsoft Access does not implement database triggers, stored procedures or transaction logging.

SQL - Syntax

SQL 遵循一组称为语法的规则和指南。本教程通过列出所有基本的 SQL 语法快速入门 SQL。

SQL is followed by a unique set of rules and guidelines called Syntax. 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 (;).

这里需要特别注意的一点是，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.

Various Syntax in SQL

SQL SELECT Statement

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

SQL DISTINCT Clause

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

SQL WHERE Clause

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

SQL AND/OR Clause

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

SQL IN Clause

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

SQL BETWEEN Clause

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

SQL LIKE Clause

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

SQL ORDER BY Clause

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

SQL GROUP BY Clause

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

SQL COUNT Clause

SELECT COUNT(column_name)
FROM   table_name
WHERE  CONDITION;

SQL HAVING Clause

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

SQL CREATE TABLE Statement

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

SQL DROP TABLE Statement

DROP TABLE table_name;

SQL CREATE INDEX Statement

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

SQL DROP INDEX Statement

ALTER TABLE table_name
DROP INDEX index_name;

SQL DESC Statement

DESC table_name;

SQL TRUNCATE TABLE Statement

TRUNCATE TABLE table_name;

SQL ALTER TABLE Statement

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

SQL ALTER TABLE Statement (Rename)

ALTER TABLE table_name RENAME TO new_table_name;

SQL INSERT INTO Statement

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

SQL UPDATE Statement

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

SQL DELETE Statement

DELETE FROM table_name
WHERE  {CONDITION};

SQL CREATE DATABASE Statement

CREATE DATABASE database_name;

SQL DROP DATABASE Statement

DROP DATABASE database_name;

SQL USE Statement

USE database_name;

SQL COMMIT Statement

COMMIT;

SQL ROLLBACK Statement

ROLLBACK;

SQL - Data Types

SQL 数据类型是一种属性，它指定任何对象的数据类型。在 SQL 中，每列、变量和表达式都具有相关的数据类型。在创建表时可以使用这些数据类型。您可以根据要求为表列选择数据类型。

SQL Data Type is an attribute that specifies the type of data of any object. Each column, variable and expression has a related data type in SQL. You can use these data types while creating your tables. You can choose a data type for a table column based on your requirement.

SQL Server 为您提供了六类数据类型，如下所示：

SQL Server offers six categories of data types for your use which are listed below −

Exact Numeric Data Types

DATA TYPE	FROM	TO
bigint	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
int	-2,147,483,648	2,147,483,647
smallint	-32,768	32,767
tinyint	0	255
bit	0	1
decimal	-10^38 +1	10^38 -1
numeric	-10^38 +1	10^38 -1
money	-922,337,203,685,477.5808	+922,337,203,685,477.5807
smallmoney	-214,748.3648	+214,748.3647

Approximate Numeric Data Types

DATA TYPE

FROM

float

-1.79E + 308

1.79E + 308

real

-3.40E + 38

3.40E + 38

Date and Time Data Types

DATA TYPE

FROM

datetime

Jan 1, 1753

Dec 31, 9999

smalldatetime

Jan 1, 1900

Jun 6, 2079

date

Stores a date like June 30, 1991

time

Note − 在此处，datetime 的精度为 3.33 毫秒，而 smalldatetime 的精度为 1 分钟。

Note − Here, datetime has 3.33 milliseconds accuracy where as smalldatetime has 1 minute accuracy.

Character Strings Data Types

Sr.No.	DATA TYPE & Description
1	char Maximum length of 8,000 characters.( Fixed length non-Unicode characters)
2	varchar Maximum of 8,000 characters.(Variable-length non-Unicode data).
3	varchar(max) Maximum length of 2E + 31 characters, Variable-length non-Unicode data (SQL Server 2005 only).
4	text Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

Unicode Character Strings Data Types

Sr.No.	DATA TYPE & Description
1	nchar Maximum length of 4,000 characters.( Fixed length Unicode)
2	nvarchar Maximum length of 4,000 characters.(Variable length Unicode)
3	nvarchar(max) Maximum length of 2E + 31 characters (SQL Server 2005 only).( Variable length Unicode)
4	ntext Maximum length of 1,073,741,823 characters. ( Variable length Unicode )

Binary Data Types

Sr.No.	DATA TYPE & Description
1	binary Maximum length of 8,000 bytes(Fixed-length binary data )
2	varbinary Maximum length of 8,000 bytes.(Variable length binary data)
3	varbinary(max) Maximum length of 2E + 31 bytes (SQL Server 2005 only). ( Variable length Binary data)
4	image Maximum length of 2,147,483,647 bytes. ( Variable length Binary Data)

Misc Data Types

Sr.No.	DATA TYPE & Description
1	sql_variant Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
2	timestamp Stores a database-wide unique number that gets updated every time a row gets updated
3	uniqueidentifier Stores a globally unique identifier (GUID)
4	xml Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only).
5	cursor Reference to a cursor object
6	table Stores a result set for later processing

SQL - Operators

What is an Operator in SQL?

运算符是主要在 SQL 语句的 WHERE 子句中用来执行操作（例如比较和算术运算）的保留字或字符。这些运算符用来指定 SQL 语句中的条件以及充当语句中多个条件的连接。

An operator is a reserved word or a character used primarily in an SQL statement’s WHERE clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

Arithmetic operators
Comparison operators
Logical operators
Operators used to negate conditions

SQL Arithmetic Operators

假设 'variable a' 保存 10，而 'variable b' 保存 20，那么-

Assume 'variable a' holds 10 and 'variable b' holds 20, then −

Show Examples

Operator

Description

Example

+ (Addition)

Adds values on either side of the operator.

a + b will give 30

- (Subtraction)

Subtracts right hand operand from left hand operand.

a - b will give -10

* (Multiplication)

Multiplies values on either side of the operator.

a * b will give 200

/ (Division)

Divides left hand operand by right hand operand.

b / a will give 2

% (Modulus)

Divides left hand operand by right hand operand and returns remainder.

b % a will give 0

SQL Comparison Operators

假设 'variable a' 保存 10，而 'variable b' 保存 20，那么-

Assume 'variable a' holds 10 and 'variable b' holds 20, then −

Show Examples

Operator

Description

Example

Checks if the values of two operands are equal or not, if yes then condition becomes true.

(a = b) is not true.

Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.

(a != b) is true.

Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.

(a <> b) is true.

Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.

(a > b) is not true.

Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.

(a < b) is true.

Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.

(a >= b) is not true.

⇐

Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.

(a ⇐ b) is true.

Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.

(a !< b) is false.

Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.

(a !> b) is true.

SQL Logical Operators

以下是 SQL 中所有可用逻辑运算符的列表。

Here is a list of all the logical operators available in SQL.

Show Examples

Sr.No.	Operator & Description
1	ALL The ALL operator is used to compare a value to all values in another value set.
2	AND The AND operator allows the existence of multiple conditions in an SQL statement’s WHERE clause.
3	ANY The ANY operator is used to compare a value to any applicable value in the list as per the condition.
4	BETWEEN The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
5	EXISTS The EXISTS operator is used to search for the presence of a row in a specified table that meets a certain criterion.
6	IN The IN operator is used to compare a value to a list of literal values that have been specified.
7	LIKE The LIKE operator is used to compare a value to similar values using wildcard operators.
8	NOT The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
9	OR The OR operator is used to combine multiple conditions in an SQL statement’s WHERE clause.
10	IS NULL The NULL operator is used to compare a value with a NULL value.
11	UNIQUE The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

SQL - Expressions

表达式是一个或多个值、运算符和计算为值的 SQL 函数的组合。这些 SQL 表达式类似于公式，并且是用查询语言编写的。您还可以使用它们对数据库进行查询以获取一组特定数据。

An expression is a combination of one or more values, operators and SQL functions that evaluate to a value. These SQL EXPRESSIONs are like formulae and they are written in query language. You can also use them to query the database for a specific set of data.

Syntax

考虑以下 SELECT 语句的基本语法：

Consider the basic syntax of the SELECT statement as follows −

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION|EXPRESSION];

SQL 表达式有不同类型，如下所述：

There are different types of SQL expressions, which are mentioned below −

Boolean
Numeric
Date

让我们现在详细讨论每一个这些。

Let us now discuss each of these in detail.

Boolean Expressions

SQL 布尔表达式基于匹配单个值来获取数据。以下为语法：

SQL Boolean Expressions fetch the data based on matching a single value. Following is the syntax −

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHING EXPRESSION;

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
7 rows in set (0.00 sec)

下表是一个简单的示例，显示了各种 SQL 布尔表达式的用法：

The following table is a simple example showing the usage of various SQL Boolean Expressions −

SQL> SELECT * FROM CUSTOMERS WHERE SALARY = 10000;
+----+-------+-----+---------+----------+
| ID | NAME  | AGE | ADDRESS | SALARY   |
+----+-------+-----+---------+----------+
|  7 | Muffy |  24 | Indore  | 10000.00 |
+----+-------+-----+---------+----------+
1 row in set (0.00 sec)

Numeric Expression

这些表达式用于在任何查询中执行任何数学运算。以下为语法：

These expressions are used to perform any mathematical operation in any query. Following is the syntax −

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name
WHERE CONDITION] ;

此处，numerical_expression 用于数学表达式或任何公式。以下是一个简单的示例，显示了 SQL 数值表达式的用法：

Here, the numerical_expression is used for a mathematical expression or any formula. Following is a simple example showing the usage of SQL Numeric Expressions −

SQL> SELECT (15 + 6) AS ADDITION
+----------+
| ADDITION |
+----------+
|       21 |
+----------+
1 row in set (0.00 sec)

有几个内置函数，如 avg()、sum()、count() 等，用于执行已知的针对表或特定表列的聚合数据计算。

There are several built-in functions like avg(), sum(), count(), etc., to perform what is known as the aggregate data calculations against a table or a specific table column.

SQL> SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS;
+---------+
| RECORDS |
+---------+
|       7 |
+---------+
1 row in set (0.00 sec)

Date Expressions

日期表达式返回当前系统日期和时间值：

Date Expressions return current system date and time values −

SQL>  SELECT CURRENT_TIMESTAMP;
+---------------------+
| Current_Timestamp   |
+---------------------+
| 2009-11-12 06:40:23 |
+---------------------+
1 row in set (0.00 sec)

另一个日期表达式如下所示：

Another date expression is as shown below −

SQL>  SELECT  GETDATE();;
+-------------------------+
| GETDATE                 |
+-------------------------+
| 2009-10-22 12:07:18.140 |
+-------------------------+
1 row in set (0.00 sec)

SQL - CREATE Database

SQL CREATE DATABASE 语句用于创建新的 SQL 数据库。

The SQL CREATE DATABASE statement is used to create a new SQL database.

Syntax

此 CREATE DATABASE 语句的基本语法如下：

The basic syntax of this CREATE DATABASE statement is as follows −

CREATE DATABASE DatabaseName;

数据库名称在 RDBMS 中应该始终唯一。

Always the database name should be unique within the RDBMS.

Example

如果你想创建一个新数据库 <testDB>，则 CREATE DATABASE 语句如下所示：

If you want to create a new database <testDB>, then the CREATE DATABASE statement would be as shown below −

SQL> CREATE DATABASE testDB;

在创建任何数据库之前，请确保你拥有管理员权限。一旦创建一个数据库，你可以按照如下方式在数据库列表中查看：

Make sure you have the admin privilege before creating any database. Once a database is created, you can check it in the list of databases as follows −

SQL> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| AMROOD             |
| TUTORIALSPOINT     |
| mysql              |
| orig               |
| test               |
| testDB             |
+--------------------+
7 rows in set (0.00 sec)

SQL - DROP or DELETE Database

SQL DROP DATABASE 语句用于删除 SQL 模式中的现有数据库。

The SQL DROP DATABASE statement is used to drop an existing database in SQL schema.

Syntax

DROP DATABASE 语句的基本语法如下：

The basic syntax of DROP DATABASE statement is as follows −

DROP DATABASE DatabaseName;

数据库名称在 RDBMS 中应该始终唯一。

Always the database name should be unique within the RDBMS.

Example

如果你想删除现有数据库 <testDB>，则 DROP DATABASE 语句如下所示：

If you want to delete an existing database <testDB>, then the DROP DATABASE statement would be as shown below −

SQL> DROP DATABASE testDB;

NOTE - 在使用此操作之前请小心，因为删除现有数据库会导致丢失存储在数据库中的完整信息。

NOTE − Be careful before using this operation because by deleting an existing database would result in loss of complete information stored in the database.

在删除任何数据库之前，请确保你拥有管理员权限。一旦删除一个数据库，你可以按照如下方式在数据库列表中查看：

Make sure you have the admin privilege before dropping any database. Once a database is dropped, you can check it in the list of the databases as shown below −

SQL> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| AMROOD             |
| TUTORIALSPOINT     |
| mysql              |
| orig               |
| test               |
+--------------------+
6 rows in set (0.00 sec)

SQL - SELECT Database, USE Statement

如果你的 SQL 模式中有多个数据库，那么在开始操作之前，你需要选择一个要执行所有操作的数据库。

When you have multiple databases in your SQL Schema, then before starting your operation, you would need to select a database where all the operations would be performed.

SQL USE 语句用于在 SQL 模式中选择任何现有数据库。

The SQL USE statement is used to select any existing database in the SQL schema.

Syntax

USE 语句的基本语法如下所示：

The basic syntax of the USE statement is as shown below −

USE DatabaseName;

数据库名称在 RDBMS 中应该始终唯一。

Always the database name should be unique within the RDBMS.

Example

你可以按照如下方式查看可用数据库：

You can check the available databases as shown below −

SQL> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| AMROOD             |
| TUTORIALSPOINT     |
| mysql              |
| orig               |
| test               |
+--------------------+
6 rows in set (0.00 sec)

现在，如果你想使用 AMROOD 数据库，那么你可以执行以下 SQL 命令并开始使用 AMROOD 数据库。

Now, if you want to work with the AMROOD database, then you can execute the following SQL command and start working with the AMROOD database.

SQL> USE AMROOD;

SQL - CREATE Table

创建基本表涉及命名表并定义其列和每个列的数据类型。

Creating a basic table involves naming the table and defining its columns and each column’s data type.

SQL CREATE TABLE 语句用于创建新表。

The SQL CREATE TABLE statement is used to create a new table.

Syntax

CREATE TABLE 语句的基本语法如下：

The basic syntax of the CREATE TABLE statement is as follows −

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

CREATE TABLE 是一个关键字，它告诉数据库系统你要做什么。在这种情况下，你想创建一个新表。表的唯一名称或标识符位于 CREATE TABLE 语句后面。

CREATE TABLE is the keyword telling the database system what you want to do. In this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement.

然后在括号中列出定义表中每个列的内容以及其数据类型是什么。通过以下示例，语法会变得更加清晰。

Then in brackets comes the list defining each column in the table and what sort of data type it is. The syntax becomes clearer with the following example.

可以使用 CREATE TABLE 语句和 SELECT 语句的组合来创建现有表的副本。你可以在 Create Table Using another Table. 处查看完整详细信息

A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. You can check the complete details at Create Table Using another Table.

Example

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

The following code block is an example, which creates a CUSTOMERS table 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 −

SQL> 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 命令 −

You can verify if your table has been created successfully by looking at the message displayed by the SQL server, otherwise you can use the DESC command as follows −

SQL> DESC CUSTOMERS;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID      | int(11)       | NO   | PRI |         |       |
| NAME    | varchar(20)   | NO   |     |         |       |
| AGE     | int(11)       | NO   |     |         |       |
| ADDRESS | char(25)      | YES  |     | NULL    |       |
| SALARY  | decimal(18,2) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

现在，你的数据库中已经有了 CUSTOMERS 表，可以使用此表来存储与客户相关的信息。

Now, you have CUSTOMERS table available in your database which you can use to store the required information related to customers.

SQL - DROP or DELETE Table

SQL DROP TABLE 语句用于移除表定义及该表的所有数据、索引、触发器、约束和权限规范。

The SQL DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for that table.

NOTE − 使用此命令时应非常小心，因为一旦某个表被删除，该表中可用的所有信息也将永远丢失。

NOTE − You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever.

Syntax

此 DROP TABLE 语句的基本语法如下 −

The basic syntax of this DROP TABLE statement is as follows −

DROP TABLE table_name;

Example

让我们首先验证 CUSTOMERS 表，然后按如下所示将其从数据库中删除 −

Let us first verify the CUSTOMERS table and then we will delete it from the database as shown below −

SQL> DESC CUSTOMERS;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID      | int(11)       | NO   | PRI |         |       |
| NAME    | varchar(20)   | NO   |     |         |       |
| AGE     | int(11)       | NO   |     |         |       |
| ADDRESS | char(25)      | YES  |     | NULL    |       |
| SALARY  | decimal(18,2) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

这意味着 CUSTOMERS 表在数据库中可用，因此现在我们丢弃它，如下所示。

This means that the CUSTOMERS table is available in the database, so let us now drop it as shown below.

SQL> DROP TABLE CUSTOMERS;
Query OK, 0 rows affected (0.01 sec)

现在，如果您尝试使用 DESC 命令，便会收到以下错误 −

Now, if you would try the DESC command, then you will get the following error −

SQL> DESC CUSTOMERS;
ERROR 1146 (42S02): Table 'TEST.CUSTOMERS' doesn't exist

此处，TEST 是我们用于示例的数据库名称。

Here, TEST is the database name which we are using for our examples.

SQL - INSERT Query

SQL INSERT INTO 语句用于将表中添加新行数据到数据库中。

The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.

Syntax

INSERT INTO 语句有两个基本语法，如下所示。

There are two basic syntaxes of the INSERT INTO statement which are shown below.

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

此处，column1、column2、column3、…columnN 是要向其插入数据的表中的列名。

Here, column1, column2, column3,…columnN are the names of the columns in the table into which you want to insert the data.

如果您要为表的所有列添加值，则可能不需要在 SQL 查询中指定列名。但请确保值的顺序与表中的列顺序相同。

You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table.

SQL INSERT INTO 语法如下所示 −

The SQL INSERT INTO syntax will be as follows −

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Example

以下语句将在 CUSTOMERS 表中创建六条记录。

The following statements would create six records in the CUSTOMERS table.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

您可以通过使用以下所示的第二个语法在 CUSTOMERS 表中创建记录。

You can create a record in the CUSTOMERS table by using the second syntax as shown below.

INSERT INTO CUSTOMERS
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );

以上所有语句都会在 CUSTOMERS 表中生成以下记录，如下所示。

All the above statements would produce the following records in the CUSTOMERS table as shown below.

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Populate one table using another table

您可以通过某一表的 select 语句将数据填充到另一张表中；前提是该另一张表具有第一张表所填充字段的一组字段。

You can populate the data into a table through the select statement over another table; provided the other table has a set of fields, which are required to populate the first table.

以下是语法 −

Here is the syntax −

INSERT INTO first_table_name [(column1, column2, ... columnN)]
   SELECT column1, column2, ...columnN
   FROM second_table_name
   [WHERE condition];

SQL - SELECT Query

SQL SELECT 语句用于从数据库表中获取数据，该数据将以结果表的形式返回。这些结果表被称作结果集。

The SQL SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result-sets.

Syntax

SELECT 语句的基本语法如下所示 −

The basic syntax of the SELECT statement is as follows −

SELECT column1, column2, columnN FROM table_name;

此处，column1、column2… 是您想要获取其值的表字段。如果您想获取字段中可用所有字段，则可以使用以下语法。

Here, column1, column2… are the fields of a table whose values you want to fetch. If you want to fetch all the fields available in the field, then you can use the following syntax.

SELECT * FROM table_name;

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下代码是一个示例，它将获取 CUSTOMERS 表中可用客户的 ID、Name 和 Salary 字段。

The following code is an example, which would fetch the ID, Name and Salary fields of the customers available in CUSTOMERS table.

SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS;

这将产生以下结果 -

This would produce the following result −

+----+----------+----------+
| ID | NAME     | SALARY   |
+----+----------+----------+
|  1 | Ramesh   |  2000.00 |
|  2 | Khilan   |  1500.00 |
|  3 | kaushik  |  2000.00 |
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

如果您要获取 CUSTOMERS 表的所有字段，则应使用以下查询。

If you want to fetch all the fields of the CUSTOMERS table, then you should use the following query.

SQL> SELECT * FROM CUSTOMERS;

这将产生如下所示的结果。

This would produce the result as shown below.

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

SQL - WHERE Clause

SQL WHERE 子句用于在从单个表中获取数据或通过与多个表进行联接时指定条件。如果满足给定的条件，则只会从表中返回特定值。您应使用 WHERE 子句来筛选记录并仅获取必要的记录。

The SQL WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. You should use the WHERE clause to filter the records and fetching only the necessary records.

WHERE 子句不仅用于 SELECT 语句，还用于 UPDATE、DELETE 语句等，我们将在后续章节中对其进行介绍。

The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement, etc., which we would examine in the subsequent chapters.

Syntax

带 WHERE 子句的 SELECT 语句的基本语法如下所示。

The basic syntax of the SELECT statement with the WHERE clause is as shown below.

SELECT column1, column2, columnN
FROM table_name
WHERE [condition]

您可以使用 comparison or logical operators 之类 >、<、=、 LIKE, NOT 等条件来指定条件。以下示例将阐明此概念。

You can specify a condition using the comparison or logical operators like >, <, =, LIKE, NOT, etc. The following examples would make this concept clear.

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下代码是一个示例，它将从 CUSTOMERS 表中获取 ID、Name 和 Salary 字段，其中薪水大于 2000 −

The following code is an example which would fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than 2000 −

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

这将产生以下结果 -

This would produce the following result −

+----+----------+----------+
| ID | NAME     | SALARY   |
+----+----------+----------+
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

以下查询是一个示例，它将从 CUSTOMERS 表中获取 ID、Name 和 Salary 字段，其中客户姓名为 Hardik 。

The following query is an example, which would fetch the ID, Name and Salary fields from the CUSTOMERS table for a customer with the name Hardik.

SQL> SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE NAME = 'Hardik';

这将产生以下结果 -

This would produce the following result −

+----+----------+----------+
| ID | NAME     | SALARY   |
+----+----------+----------+
|  5 | Hardik   |  8500.00 |
+----+----------+----------+

SQL - AND and OR Conjunctive Operators

SQL AND 和 OR 运算符用于组合多个条件，以缩小 SQL 语句中的数据范围。这两个运算符称为连接运算符。

The SQL AND & OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called as the conjunctive operators.

这些运算符提供了一种在同一 SQL 语句中使用不同运算符进行多次比较的方法。

These operators provide a means to make multiple comparisons with different operators in the same SQL statement.

The AND Operator

AND 运算符允许在 SQL 语句的 WHERE 子句中存在多个条件。

The AND operator allows the existence of multiple conditions in an SQL statement’s WHERE clause.

Syntax

AND 运算符与 WHERE 子句组合后的基本语法如下 −

The basic syntax of the AND operator with a WHERE clause is as follows −

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

您可以使用 AND 运算符组合 N 个条件。对于 SQL 语句执行的操作（无论是事务还是查询），AND 分隔的所有条件都必须为 TRUE。

You can combine N number of conditions using the AND operator. For an action to be taken by the SQL statement, whether it be a transaction or a query, all conditions separated by the AND must be TRUE.

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下是一个示例，它将从 CUSTOMERS 表中获取 ID、Name 和 Salary 字段，其中薪水大于 2000 且年龄小于 25 岁 −

Following is an example, which would fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than 2000 and the age is less than 25 years −

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

这将产生以下结果 -

This would produce the following result −

+----+-------+----------+
| ID | NAME  | SALARY   |
+----+-------+----------+
|  6 | Komal |  4500.00 |
|  7 | Muffy | 10000.00 |
+----+-------+----------+

The OR Operator

OR 运算符用于组合 SQL 语句的 WHERE 子句中的多个条件。

The OR operator is used to combine multiple conditions in an SQL statement’s WHERE clause.

Syntax

OR 运算符与 WHERE 子句的基本语法如下 -

The basic syntax of the OR operator with a WHERE clause is as follows −

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

您可以使用 OR 运算符组合 N 个条件。对于 SQL 语句执行的操作（无论是事务还是查询），由 OR 分隔的所有条件中只需要满足一个条件为 TRUE。

You can combine N number of conditions using the OR operator. For an action to be taken by the SQL statement, whether it be a transaction or query, the only any ONE of the conditions separated by the OR must be TRUE.

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下代码块包含一个查询，它将从 CUSTOMERS 表中获取 ID、Name 和 Salary 字段，其中薪水大于 2000 或年龄小于 25 岁。

The following code block hasa query, which would fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than 2000 OR the age is less than 25 years.

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

这将产生以下结果 -

This would produce the following result −

+----+----------+----------+
| ID | NAME     | SALARY   |
+----+----------+----------+
|  3 | kaushik  |  2000.00 |
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

SQL - UPDATE Query

SQL UPDATE 查询用于修改表中的现有记录。您可以将 WHERE 子句与 UPDATE 查询一起使用来更新选定的行，否则将影响所有行。

The SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.

Syntax

带有 WHERE 子句的 UPDATE 查询的基本语法如下：

The basic syntax of the UPDATE query with a WHERE clause is as follows −

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

您可以使用 AND 或 OR 运算符组合 N 个条件。

You can combine N number of conditions using the AND or the OR operators.

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下查询将更新表中 ID 号为 6 的客户的 ADDRESS。

The following query will update the ADDRESS for a customer whose ID number is 6 in the table.

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

现在，CUSTOMERS 表将拥有以下记录：

Now, the CUSTOMERS table would have the following records −

+----+----------+-----+-----------+----------+
| 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 | Pune      |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

如果您要修改 CUSTOMERS 表中所有 ADDRESS 和 SALARY 列的值，则无需使用 WHERE 子句，因为 UPDATE 查询就足够了，如下面代码块所示。

If you want to modify all the ADDRESS and the SALARY column values in the CUSTOMERS table, you do not need to use the WHERE clause as the UPDATE query would be enough as shown in the following code block.

SQL> UPDATE CUSTOMERS
SET ADDRESS = 'Pune', SALARY = 1000.00;

现在，CUSTOMERS 表将拥有以下记录：

Now, CUSTOMERS table would have the following records −

+----+----------+-----+---------+---------+
| ID | NAME     | AGE | ADDRESS | SALARY  |
+----+----------+-----+---------+---------+
|  1 | Ramesh   |  32 | Pune    | 1000.00 |
|  2 | Khilan   |  25 | Pune    | 1000.00 |
|  3 | kaushik  |  23 | Pune    | 1000.00 |
|  4 | Chaitali |  25 | Pune    | 1000.00 |
|  5 | Hardik   |  27 | Pune    | 1000.00 |
|  6 | Komal    |  22 | Pune    | 1000.00 |
|  7 | Muffy    |  24 | Pune    | 1000.00 |
+----+----------+-----+---------+---------+

SQL - DELETE Query

SQL DELETE 查询用于从表中删除现有记录。

The SQL DELETE Query is used to delete the existing records from a table.

可以将 WHERE 子句与 DELETE 查询一起使用来删除选定的行，否则所有记录都会被删除。

You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.

Syntax

使用 WHERE 子句的 DELETE 查询的基本语法如下：

The basic syntax of the DELETE query with the WHERE clause is as follows −

DELETE FROM table_name
WHERE [condition];

可以使用 AND 或 OR 运算符组合 N 个条件。

You can combine N number of conditions using AND or OR operators.

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

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

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

SQL> DELETE FROM CUSTOMERS
WHERE ID = 6;

现在，CUSTOMERS 表将具有以下记录。

Now, the CUSTOMERS table would have the following records.

+----+----------+-----+-----------+----------+
| 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 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

如果要从 CUSTOMERS 表中 DELETE 所有记录，则无需使用 WHERE 子句，DELETE 查询如下所示：

If you want to DELETE all the records from the CUSTOMERS table, you do not need to use the WHERE clause and the DELETE query would be as follows −

SQL> DELETE FROM CUSTOMERS;

现在，CUSTOMERS 表将没有任何记录。

Now, the CUSTOMERS table would not have any record.

SQL - LIKE Clause

SQL LIKE 子句用于使用通配符运算符将某一值与类似值进行比较。有两种通配符与 LIKE 运算符联合使用。

The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator.

The percent sign (%)
The underscore (_)

百分号表示零个、一个或多个字符。下划线表示单个数字或字符。这些符号可以组合使用。

The percent sign represents zero, one or multiple characters. The underscore represents a single number or character. These symbols can be used in combinations.

Syntax

% 和 _ 的基本语法如下：

The basic syntax of % and _ is as follows −

SELECT FROM table_name
WHERE column LIKE 'XXXX%'

or

SELECT FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX_'

可以使用 AND 或 OR 运算符组合 N 个条件。此处，XXXX 可以是任何数字或字符串值。

You can combine N number of conditions using AND or OR operators. Here, XXXX could be any numeric or string value.

Example

下表列出了一些示例，其中 WHERE 部分具有带有 '%' 和 '_' 运算符的不同 LIKE 子句：

The following table has a few examples showing the WHERE part having different LIKE clause with '%' and '_' operators −

Sr.No.	Statement & Description
1	WHERE SALARY LIKE '200%' Finds any values that start with 200.
2	WHERE SALARY LIKE '%200%' Finds any values that have 200 in any position.
3	WHERE SALARY LIKE '_00%' Finds any values that have 00 in the second and third positions.
4	WHERE SALARY LIKE '2_%_%' Finds any values that start with 2 and are at least 3 characters in length.
5	WHERE SALARY LIKE '%2' Finds any values that end with 2.
6	WHERE SALARY LIKE '_2%3' Finds any values that have a 2 in the second position and end with a 3.
7	WHERE SALARY LIKE '2___3' Finds any values in a five-digit number that start with 2 and end with 3.

让我们举一个实际的例子，考虑 CUSTOMER 表，其中记录如下所示。

Let us take a real example, consider the CUSTOMERS table having the records as shown below.

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下是一个示例，它将显示 CUSTOMERS 表中的所有记录，SALARY 以 200 开头。

Following is an example, which would display all the records from the CUSTOMERS table, where the SALARY starts with 200.

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

这将产生以下结果 -

This would produce the following result −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
+----+----------+-----+-----------+----------+

SQL - TOP, LIMIT or ROWNUM Clause

SQL TOP 子句用于从表中提取前 N 条记录或 X% 的记录。

The SQL TOP clause is used to fetch a TOP N number or X percent records from a table.

Note − 并非所有数据库都支持 TOP 子句。例如，MySQL 支持 LIMIT 子句来提取有限数量的记录，而 Oracle 使用 ROWNUM 命令提取有限数量的记录。

Note − All the databases do not support the TOP clause. For example MySQL supports the LIMIT clause to fetch limited number of records while Oracle uses the ROWNUM command to fetch a limited number of records.

Syntax

TOP 子句与 SELECT 语句的基本语法如下所示。

The basic syntax of the TOP clause with a SELECT statement would be as follows.

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE [condition]

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下查询是 SQL Server 上的一个示例，它将从 CUSTOMERS 表中提取前 3 条记录。

The following query is an example on the SQL server, which would fetch the top 3 records from the CUSTOMERS table.

SQL> SELECT TOP 3 * FROM CUSTOMERS;

这将产生以下结果 -

This would produce the following result −

+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
+----+---------+-----+-----------+---------+

如果您使用的是 MySQL 服务器，则这里有一个等效示例 −

If you are using MySQL server, then here is an equivalent example −

SQL> SELECT * FROM CUSTOMERS
LIMIT 3;

这将产生以下结果 -

This would produce the following result −

+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
+----+---------+-----+-----------+---------+

如果您使用的是 Oracle 服务器，则以下代码块有一个等效示例。

If you are using an Oracle server, then the following code block has an equivalent example.

SQL> SELECT * FROM CUSTOMERS
WHERE ROWNUM <= 3;

这将产生以下结果 -

This would produce the following result −

+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
+----+---------+-----+-----------+---------+

SQL - ORDER BY Clause

ORDER BY SQL 子句用于根据一列或多列按升序或降序对数据进行排序。某些数据库默认情况下按升序对查询结果进行排序。

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default.

Syntax

ORDER BY 从句的基本语法如下：

The basic syntax of the ORDER BY clause is as follows −

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

您可以在 ORDER BY 子句中使用多列。确保用于对该列进行排序的任何列都应位于列列表中。

You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort that column should be in the column-list.

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下代码块有一个示例，它将按 NAME 和 SALARY 按升序对结果进行排序 −

The following code block has an example, which would sort the result in an ascending order by the NAME and the SALARY −

SQL> SELECT * FROM CUSTOMERS
   ORDER BY NAME, SALARY;

这将产生以下结果 -

This would produce the following result −

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

以下代码块有一个示例，它将按 NAME 按降序对结果进行排序。

The following code block has an example, which would sort the result in the descending order by NAME.

SQL> SELECT * FROM CUSTOMERS
   ORDER BY NAME DESC;

这将产生以下结果 -

This would produce the following result −

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

SQL - Group By

SQL GROUP BY 子句与 SELECT 语句联合使用，以将相同的数据分组。此 GROUP BY 子句跟随 SELECT 语句中的 WHERE 子句，并位于 ORDER BY 子句之前。

The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

Syntax

GROUP BY 子句的基本语法在以下代码块中显示。GROUP BY 子句必须遵循 WHERE 子句中的条件，并且如果使用，则必须位于 ORDER BY 子句之前。

The basic syntax of a GROUP BY clause is shown in the following code block. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

Example

考虑 CUSTOMERS 表具有以下记录 −

Consider the CUSTOMERS table is having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

如果您想了解每个客户的总工资，则 GROUP BY 查询如下所示。

If you want to know the total amount of the salary on each customer, then the GROUP BY query would be as follows.

SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
   GROUP BY NAME;

这将产生以下结果 -

This would produce the following result −

+----------+-------------+
| NAME     | SUM(SALARY) |
+----------+-------------+
| Chaitali |     6500.00 |
| Hardik   |     8500.00 |
| kaushik  |     2000.00 |
| Khilan   |     1500.00 |
| Komal    |     4500.00 |
| Muffy    |    10000.00 |
| Ramesh   |     2000.00 |
+----------+-------------+

现在，让我们来看一张表，其中 CUSTOMERS 表具有以下带有重复名称的记录 −

Now, let us look at a table where the CUSTOMERS table has the following records with duplicate names −

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

现在，如果您想再次了解每个客户的总工资，则 GROUP BY 查询如下所示 −

Now again, if you want to know the total amount of salary on each customer, then the GROUP BY query would be as follows −

SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
   GROUP BY NAME;

这将产生以下结果 -

This would produce the following result −

+---------+-------------+
| NAME    | SUM(SALARY) |
+---------+-------------+
| Hardik  |     8500.00 |
| kaushik |     8500.00 |
| Komal   |     4500.00 |
| Muffy   |    10000.00 |
| Ramesh  |     3500.00 |
+---------+-------------+

SQL - Distinct Keyword

SQL DISTINCT 关键字结合 SELECT 语句一起使用，可用于消除所有重复记录，并仅获取唯一记录。

The SQL DISTINCT keyword is used in conjunction with the SELECT statement to eliminate all the duplicate records and fetching only unique records.

在表中可能存在多个重复记录的情况。在获取此类记录的同时，获取仅那些唯一记录比获取重复记录更有意义。

There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only those unique records instead of fetching duplicate records.

Syntax

用于消除重复记录的 DISTINCT 关键字的基本语法如下所述 −

The basic syntax of DISTINCT keyword to eliminate the duplicate records is as follows −

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

首先，让我们看看以下 SELECT 查询如何返回重复的工资记录。

First, let us see how the following SELECT query returns the duplicate salary records.

SQL> SELECT SALARY FROM CUSTOMERS
   ORDER BY SALARY;

这将产生以下结果，其中工资（2000）出现两次，它是来自原始表的重复记录。

This would produce the following result, where the salary (2000) is coming twice which is a duplicate record from the original table.

+----------+
| SALARY   |
+----------+
|  1500.00 |
|  2000.00 |
|  2000.00 |
|  4500.00 |
|  6500.00 |
|  8500.00 |
| 10000.00 |
+----------+

现在，让我们将 DISTINCT 关键字与上面的 SELECT 查询一起使用，然后查看结果。

Now, let us use the DISTINCT keyword with the above SELECT query and then see the result.

SQL> SELECT DISTINCT SALARY FROM CUSTOMERS
   ORDER BY SALARY;

这将产生以下结果，其中我们没有任何重复条目。

This would produce the following result where we do not have any duplicate entry.

+----------+
| SALARY   |
+----------+
|  1500.00 |
|  2000.00 |
|  4500.00 |
|  6500.00 |
|  8500.00 |
| 10000.00 |
+----------+

SQL - SORTING Results

ORDER BY SQL 子句用于根据一列或多列按升序或降序对数据进行排序。某些数据库默认情况下按升序对查询结果进行排序。

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default.

Syntax

将用于按升序或降序对结果进行排序的 ORDER BY 子句的基本语法如下：

The basic syntax of the ORDER BY clause which would be used to sort the result in an ascending or descending order is as follows −

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

你可以在 ORDER BY 子句中使用多列。确保你用于排序的任何列都应位于列列表中。

You can use more than one column in the ORDER BY clause. Make sure that whatever column you are using to sort, that column should be in the column-list.

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下是按 NAME 和 SALARY 升序对结果进行排序的示例。

Following is an example, which would sort the result in an ascending order by NAME and SALARY.

SQL> SELECT * FROM CUSTOMERS
   ORDER BY NAME, SALARY;

这将产生以下结果 -

This would produce the following result −

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

以下代码块有一个示例，该示例将按 NAME 降序排列结果。

The following code block has an example, which would sort the result in a descending order by NAME.

SQL> SELECT * FROM CUSTOMERS
   ORDER BY NAME DESC;

这将产生以下结果 -

This would produce the following result −

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

要按自己的首选顺序提取行，将使用以下 SELECT 查询：

To fetch the rows with their own preferred order, the SELECT query used would be as follows −

SQL> SELECT * FROM CUSTOMERS
   ORDER BY (CASE ADDRESS
   WHEN 'DELHI' 	 THEN 1
   WHEN 'BHOPAL' 	 THEN 2
   WHEN 'KOTA' 	 THEN 3
   WHEN 'AHMEDABAD' THEN 4
   WHEN 'MP' 	THEN 5
   ELSE 100 END) ASC, ADDRESS DESC;

这将产生以下结果 -

This would produce the following result −

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

这会首先按 ownoOrder 中的 ADDRESS 对客户进行排序，再按各个地址的自然顺序进行排序。此外，还会按地址的字母倒序对剩余的地址进行排序。

This will sort the customers by ADDRESS in your ownoOrder of preference first and in a natural order for the remaining addresses. Also, the remaining Addresses will be sorted in the reverse alphabetical order.

SQL - Constraints

约束是针对表的数据列强制执行的规则。这些规则用于限制可以添加到表中的数据类型。这可确保数据库中的数据准确且可靠。

Constraints are the rules enforced on the data columns of a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

约束可以是列级别或表级别的。列级别约束仅应用于一列，而表级别约束则应用于整个表。

Constraints could be either on a column level or a table level. The column level constraints are applied only to one column, whereas the table level constraints are applied to the whole table.

以下是 SQL 中一些最常用的约束。这些约束已经在 SQL - RDBMS Concepts 章节中进行了讨论，但在此时重新审阅它们很有价值。

Following are some of the most commonly used constraints available in SQL. These constraints have already been discussed in SQL - RDBMS Concepts chapter, but itâ€™s worth to revise them at this point.

NOT NULL Constraint − Ensures that a column cannot have NULL value.
UNIQUE Constraint − Ensures that all values in a column are different.
PRIMARY Key − Uniquely identifies each row/record in a database table.
FOREIGN Key − Uniquely identifies a row/record in any of the given database table.
INDEX − Used to create and retrieve data from the database very quickly.

可以在使用 CREATE TABLE 语句创建表时指定约束，或者可以在表创建后使用 ALTER TABLE 语句创建约束。

Constraints can be specified when a table is created with the CREATE TABLE statement or you can use the ALTER TABLE statement to create constraints even after the table is created.

Dropping Constraints

可以使用带 DROP CONSTRAINT 选项的 ALTER TABLE 命令删除所定义的任何约束。

Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.

例如，要删除 EMPLOYEES 表中的主键约束，可以使用以下命令。

For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following command.

ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;

某些实现可能提供用于删除某些约束的快捷方式。例如，要删除 Oracle 中表的主键约束，可以使用以下命令。

Some implementations may provide shortcuts for dropping certain constraints. For example, to drop the primary key constraint for a table in Oracle, you can use the following command.

ALTER TABLE EMPLOYEES DROP PRIMARY KEY;

某些实现允许禁用约束。与其永久从数据库中删除约束，您可能希望暂时禁用约束，然后稍后启用它。

Some implementations allow you to disable constraints. Instead of permanently dropping a constraint from the database, you may want to temporarily disable the constraint and then enable it later.

Integrity Constraints

完整性约束用于确保关系数据库中数据准确且一致。数据完整性通过参照完整性概念在关系数据库中得以处理。

Integrity constraints are used to ensure accuracy and consistency of the data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity.

有许多类型的完整性约束在 Referential Integrity (RI) 方面发挥了作用。这些约束包括主键、外键、唯一性约束和其他在上面提到的约束。

There are many types of integrity constraints that play a role in Referential Integrity (RI). These constraints include Primary Key, Foreign Key, Unique Constraints and other constraints which are mentioned above.

SQL - Using Joins

SQL Joins 子句用于组合数据库中两个或更多表中的记录。JOIN 是一种通过使用每张表中常见的值组合两张表中的字段的方法。

The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

考虑以下两张表 −

Consider the following two tables −

Table 1 − CUSTOMERS 表

Table 1 − CUSTOMERS Table

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Table 2 − ORDERS 表

Table 2 − ORDERS Table

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

现在，我们像下面所示在我们的 SELECT 语句中连接这两张表。

Now, let us join these two tables in our SELECT statement as shown below.

SQL> SELECT ID, NAME, AGE, AMOUNT
   FROM CUSTOMERS, ORDERS
   WHERE  CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

这会产生以下结果。

This would produce the following result.

+----+----------+-----+--------+
| ID | NAME     | AGE | AMOUNT |
+----+----------+-----+--------+
|  3 | kaushik  |  23 |   3000 |
|  3 | kaushik  |  23 |   1500 |
|  2 | Khilan   |  25 |   1560 |
|  4 | Chaitali |  25 |   2060 |
+----+----------+-----+--------+

这里，值得注意的是，在 WHERE 子句中执行连接。可以使用多种运算符来连接表，例如 =、<、>、<>、⇐、>=、!=、BETWEEN、LIKE 和 NOT；它们都可以用于连接表。然而，最常见的运算符是等号符号。

Here, it is noticeable that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, ⇐, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal to symbol.

SQL 中有不同类型的连接可用 −

There are different types of joins available in SQL −

INNER JOIN − returns rows when there is a match in both tables.
LEFT JOIN − returns all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN − returns all rows from the right table, even if there are no matches in the left table.
FULL JOIN − returns rows when there is a match in one of the tables.
SELF JOIN − is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

现在让我们详细讨论每个连接。

Let us now discuss each of these joins in detail.

SQL - UNIONS CLAUSE

SQL UNION 子句/运算符用于组合两个或更多 SELECT 语句的结果，而不返回任何重复行。

The SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.

要使用此 UNION 子句，每个 SELECT 语句都必须具有

To use this UNION clause, each SELECT statement must have

The same number of columns selected
The same number of column expressions
The same data type and
Have them in the same order

但它们不必具有相同的长度。

But they need not have to be in the same length.

Syntax

UNION 子句的基本语法如下 −

The basic syntax of a UNION clause is as follows −

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

此处，给定的条件可以是基于您的要求的任何给定表达式。

Here, the given condition could be any given expression based on your requirement.

Example

考虑以下两个表。

Consider the following two tables.

Table 1 − CUSTOMER 表如下。

Table 1 − CUSTOMERS Table is as follows.

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Table 2 − ORDER 表如下。

Table 2 − ORDERS Table is as follows.

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

现在，让我们在 SELECT 语句中将这两个表连接起来，如下所示 −

Now, let us join these two tables in our SELECT statement as follows −

SQL> SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   LEFT JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
   SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   RIGHT JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

这将产生以下结果 -

This would produce the following result −

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
|    1 | Ramesh   |   NULL | NULL                |
|    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
|    5 | Hardik   |   NULL | NULL                |
|    6 | Komal    |   NULL | NULL                |
|    7 | Muffy    |   NULL | NULL                |
+------+----------+--------+---------------------+

The UNION ALL Clause

UNION ALL 运算符用于合并两个 SELECT 语句的结果，包括重复行。

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.

适用于 UNION 子句的相同规则也适用于 UNION ALL 运算符。

The same rules that apply to the UNION clause will apply to the UNION ALL operator.

Syntax

UNION ALL 的基本语法如下。

The basic syntax of the UNION ALL is as follows.

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

此处，给定的条件可以是基于您的要求的任何给定表达式。

Here, the given condition could be any given expression based on your requirement.

Example

考虑以下两个表，

Consider the following two tables,

Table 1 − CUSTOMER 表如下。

Table 1 − CUSTOMERS Table is as follows.

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Table 2 − ORDERS 表如下。

Table 2 − ORDERS table is as follows.

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

现在，让我们在 SELECT 语句中将这两个表连接起来，如下所示 −

Now, let us join these two tables in our SELECT statement as follows −

SQL> SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   LEFT JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION ALL
   SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   RIGHT JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

这将产生以下结果 -

This would produce the following result −

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
|    1 | Ramesh   |   NULL | NULL                |
|    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
|    5 | Hardik   |   NULL | NULL                |
|    6 | Komal    |   NULL | NULL                |
|    7 | Muffy    |   NULL | NULL                |
|    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

还有两个其他子句（即运算符），类似于 UNION 子句。

There are two other clauses (i.e., operators), which are like the UNION clause.

SQL INTERSECT Clause − This is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.
SQL EXCEPT Clause − This combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.

SQL - NULL Values

SQL NULL 是用于表示缺失值一词。表中 NULL 值是字段中看似为空的值。

The SQL NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank.

具有 NULL 值的字段是没有任何值的字段。非常重要的一点是要了解 NULL 值不同于零值或包含空格的字段。

A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.

Syntax

在创建表时 NULL 的基本语法。

The basic syntax of NULL while creating a table.

SQL> 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)
);

这里， NOT NULL 表示列始终应接受给定数据类型的一个显式值。有两列我们没有使用 NOT NULL，这意味着这些列可能是 NULL。

Here, NOT NULL signifies that column should always accept an explicit value of the given data type. There are two columns where we did not use NOT NULL, which means these columns could be NULL.

具有 NULL 值的字段是在记录创建期间留空的字段。

A field with a NULL value is the one that has been left blank during the record creation.

Example

在选择数据时 NULL 值可能会导致问题。然而，当将一个未知值与任何其他值进行比较时，结果始终是未知的，并且不包括在结果中。您必须使用 IS NULL 或 IS NOT NULL 运算符来检查 NULL 值。

The NULL value can cause problems when selecting data. However, because when comparing an unknown value to any other value, the result is always unknown and not included in the results. You must use the IS NULL or IS NOT NULL operators to check for a NULL value.

考虑如下具有记录的 CUSTOMERS 表。

Consider the following CUSTOMERS table having the records as shown below.

+----+----------+-----+-----------+----------+
| 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 | MP        |          |
|  7 | Muffy    |  24 | Indore    |          |
+----+----------+-----+-----------+----------+

现在，以下是 IS NOT NULL 运算符的使用方法。

Now, following is the usage of the *IS NOT NULL*operator.

SQL> SELECT  ID, NAME, AGE, ADDRESS, SALARY
   FROM CUSTOMERS
   WHERE SALARY IS NOT NULL;

这将产生以下结果 -

This would produce the following result −

+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+

现在，以下是 IS NULL 运算符的使用方法。

Now, following is the usage of the IS NULL operator.

SQL> SELECT  ID, NAME, AGE, ADDRESS, SALARY
   FROM CUSTOMERS
   WHERE SALARY IS NULL;

这将产生以下结果 -

This would produce the following result −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  6 | Komal    |  22 | MP        |          |
|  7 | Muffy    |  24 | Indore    |          |
+----+----------+-----+-----------+----------+

SQL - Alias Syntax

您可以通过指定另一个称为 Alias 的名称来临时重命名表或列。表别名的用途是在特定 SQL 语句中重命名表。重命名是一个临时更改，数据库中的实际表名不会更改。列别名用于为了特定 SQL 查询的目的而重命名表的列。

You can rename a table or a column temporarily by giving another name known as Alias. The use of table aliases is to rename a table in a specific SQL statement. The renaming is a temporary change and the actual table name does not change in the database. The column aliases are used to rename a table’s columns for the purpose of a particular SQL query.

Syntax

table 别名的基本语法如下。

The basic syntax of a table alias is as follows.

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

column 别名的基本语法如下。

The basic syntax of a column alias is as follows.

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

Example

考虑以下两个表。

Consider the following two tables.

Table 1 − CUSTOMER 表如下。

Table 1 − CUSTOMERS Table is as follows.

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Table 2 − ORDER 表如下。

Table 2 − ORDERS Table is as follows.

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

现在，以下代码块显示了 table alias 的用法。

Now, the following code block shows the usage of a table alias.

SQL> SELECT C.ID, C.NAME, C.AGE, O.AMOUNT
   FROM CUSTOMERS AS C, ORDERS AS O
   WHERE  C.ID = O.CUSTOMER_ID;

这会产生以下结果。

This would produce the following result.

+----+----------+-----+--------+
| ID | NAME     | AGE | AMOUNT |
+----+----------+-----+--------+
|  3 | kaushik  |  23 |   3000 |
|  3 | kaushik  |  23 |   1500 |
|  2 | Khilan   |  25 |   1560 |
|  4 | Chaitali |  25 |   2060 |
+----+----------+-----+--------+

以下是 column alias 的用法。

Following is the usage of a column alias.

SQL> SELECT  ID AS CUSTOMER_ID, NAME AS CUSTOMER_NAME
   FROM CUSTOMERS
   WHERE SALARY IS NOT NULL;

这会产生以下结果。

This would produce the following result.

+-------------+---------------+
| CUSTOMER_ID | CUSTOMER_NAME |
+-------------+---------------+
|           1 | Ramesh        |
|           2 | Khilan        |
|           3 | kaushik       |
|           4 | Chaitali      |
|           5 | Hardik        |
|           6 | Komal         |
|           7 | Muffy         |
+-------------+---------------+

SQL - Indexes

索引是 special lookup tables ，数据库搜索引擎可以使用它来加快数据检索速度。简单地说，索引是一个指向表中数据的指针。数据库中的索引非常类似于书籍后面的索引。

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

例如，如果您要引用书中讨论某个主题的所有页面，则首先要参考索引，其中按字母顺序列出了所有主题，然后引用一个或多个特定页码。

For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and are then referred to one or more specific page numbers.

索引有助于加快 SELECT 查询和 WHERE 子句的速度，但会使用 UPDATE 和 INSERT 语句减慢数据输入速度。索引可以在不影响数据的情况下创建或删除。

An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.

创建索引涉及 CREATE INDEX 语句，它允许您命名索引，指定表以及要索引的列或哪些列，并指出索引是按升序还是按降序。

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order.

索引也可以是唯一的，就像 UNIQUE 约束一样，因为索引可以防止在具有索引的列或列组合中出现重复的条目。

Indexes can also be unique, like the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there is an index.

The CREATE INDEX Command

CREATE INDEX 的基本语法如下。

The basic syntax of a CREATE INDEX is as follows.

CREATE INDEX index_name ON table_name;

Single-Column Indexes

单列索引只基于一个表列创建。基本语法如下。

A single-column index is created based on only one table column. The basic syntax is as follows.

CREATE INDEX index_name
ON table_name (column_name);

Unique Indexes

唯一索引不仅用于性能，还用于数据完整性。唯一索引不允许插入任何重复值到表中。基本语法如下。

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows.

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Composite Indexes

复合索引是对表中两列或更多列编制的索引。其基本语法如下。

A composite index is an index on two or more columns of a table. Its basic syntax is as follows.

CREATE INDEX index_name
on table_name (column1, column2);

无论创建单列索引还是复合索引，都要考虑在查询的 WHERE 子句中可能非常频繁地用作筛选条件的列。

Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query’s WHERE clause as filter conditions.

如果只使用了一列，则应该选择单列索引。如果在 WHERE 子句中经常将两列或更多列用作筛选条件，那么复合索引将是最佳选择。

Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the composite index would be the best choice.

Implicit Indexes

隐式索引是对象创建时由数据库服务器自动创建的索引。对于主键约束和唯一约束会自动创建索引。

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

The DROP INDEX Command

可以使用 SQL DROP 命令删除索引。删除索引时应小心，因为性能可能会变慢或提升。

An index can be dropped using SQL DROP command. Care should be taken when dropping an index because the performance may either slow down or improve.

基本语法如下 −

The basic syntax is as follows −

DROP INDEX index_name;

可以查看 INDEX Constraint 章节来查看有关索引的一些实际示例。

You can check the INDEX Constraint chapter to see some actual examples on Indexes.

When should indexes be avoided?

尽管索引旨在增强数据库的性能，但有时应避免使用索引。

Although indexes are intended to enhance a database’s performance, there are times when they should be avoided.

以下准则指示应何时重新考虑使用索引。

The following guidelines indicate when the use of an index should be reconsidered.

Indexes should not be used on small tables.
Tables that have frequent, large batch updates or insert operations.
Indexes should not be used on columns that contain a high number of NULL values.
Columns that are frequently manipulated should not be indexed.

SQL - ALTER TABLE Command

SQL ALTER TABLE 命令用于添加、删除或修改现有表中的列。还应该使用 ALTER TABLE 命令来添加和删除现有表上的各种约束。

The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table. You should also use the ALTER TABLE command to add and drop various constraints on an existing table.

Syntax

在现有表中添加 New Column 的 ALTER TABLE 命令的基本语法如下。

The basic syntax of an ALTER TABLE command to add a New Column in an existing table is as follows.

ALTER TABLE table_name ADD column_name datatype;

在现有表中 DROP COLUMN 的 ALTER TABLE 命令的基本语法如下。

The basic syntax of an ALTER TABLE command to DROP COLUMN in an existing table is as follows.

ALTER TABLE table_name DROP COLUMN column_name;

更改表中某一列的 DATA TYPE 的 ALTER TABLE 命令的基本语法如下。

The basic syntax of an ALTER TABLE command to change the DATA TYPE of a column in a table is as follows.

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

将 NOT NULL 约束添加到表中某一列的 ALTER TABLE 命令的基本语法如下。

The basic syntax of an ALTER TABLE command to add a NOT NULL constraint to a column in a table is as follows.

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

ALTER TABLE 的基本语法为 ADD UNIQUE CONSTRAINT 表如下。

The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows.

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

ALTER TABLE 命令的基本语法为 ADD CHECK CONSTRAINT 表如下。

The basic syntax of an ALTER TABLE command to ADD CHECK CONSTRAINT to a table is as follows.

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

ALTER TABLE 命令的基本语法为 ADD PRIMARY KEY 表的约束如下。

The basic syntax of an ALTER TABLE command to ADD PRIMARY KEY constraint to a table is as follows.

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

ALTER TABLE 命令的基本语法为 DROP CONSTRAINT 表如下。

The basic syntax of an ALTER TABLE command to DROP CONSTRAINT from a table is as follows.

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

如果使用的是 MySQL，代码如下 −

If you’re using MySQL, the code is as follows −

ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;

ALTER TABLE 命令的基本语法为 DROP PRIMARY KEY 表的约束如下。

The basic syntax of an ALTER TABLE command to DROP PRIMARY KEY constraint from a table is as follows.

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

如果使用的是 MySQL，代码如下 −

If you’re using MySQL, the code is as follows −

ALTER TABLE table_name
DROP PRIMARY KEY;

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下是为现有表 ADD New Column 的示例：

Following is the example to ADD a New Column to an existing table −

ALTER TABLE CUSTOMERS ADD SEX char(1);

现在，CUSTOMERS 表已经更改，并且以下内容将从 SELECT 语句输出。

Now, the CUSTOMERS table is changed and following would be output from the SELECT statement.

+----+---------+-----+-----------+----------+------+
| ID | NAME    | AGE | ADDRESS   | SALARY   | SEX  |
+----+---------+-----+-----------+----------+------+
|  1 | Ramesh  |  32 | Ahmedabad |  2000.00 | NULL |
|  2 | Ramesh  |  25 | Delhi     |  1500.00 | NULL |
|  3 | kaushik |  23 | Kota      |  2000.00 | NULL |
|  4 | kaushik |  25 | Mumbai    |  6500.00 | NULL |
|  5 | Hardik  |  27 | Bhopal    |  8500.00 | NULL |
|  6 | Komal   |  22 | MP        |  4500.00 | NULL |
|  7 | Muffy   |  24 | Indore    | 10000.00 | NULL |
+----+---------+-----+-----------+----------+------+

以下是从现有表中 DROP sex 列的示例：

Following is the example to DROP sex column from the existing table.

ALTER TABLE CUSTOMERS DROP SEX;

现在，CUSTOMERS 表已被更改，以下是 SELECT 语句的输出结果：

Now, the CUSTOMERS table is changed and following would be the output from the SELECT statement.

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

SQL - TRUNCATE TABLE Command

SQL TRUNCATE TABLE 命令用于删除现有表中的全部数据。

The SQL TRUNCATE TABLE command is used to delete complete data from an existing table.

还可以使用 DROP TABLE 命令删除完整表，但这会从数据库中删除完整表结构，并且如果你希望存储一些数据，则需要重新创建该表。

You can also use DROP TABLE command to delete complete table but it would remove complete table structure form the database and you would need to re-create this table once again if you wish you store some data.

Syntax

TRUNCATE TABLE 命令的基本语法如下所示。

The basic syntax of a TRUNCATE TABLE command is as follows.

TRUNCATE TABLE  table_name;

Example

考虑具有以下记录的 CUSTOMERS 表 −

Consider a CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下是在删表命令示例。

Following is the example of a Truncate command.

SQL > TRUNCATE TABLE CUSTOMERS;

现在，CUSTOMERS 表被截断，并且 SELECT 语句的输出将如以下代码块所示：

Now, the CUSTOMERS table is truncated and the output from SELECT statement will be as shown in the code block below −

SQL> SELECT * FROM CUSTOMERS;
Empty set (0.00 sec)

SQL - Using Views

视图只不过是存储在数据库中且具有关联名称的 SQL 语句。视图实际上是以预定义 SQL 查询形式组成的表。

A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query.

视图可以包含表的所有行或从表中选择行。视图可以从一张或多张表创建，具体取决于用于创建视图的书面 SQL 查询。

A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view.

视图是一种虚拟表，允许用户执行以下操作 −

Views, which are a type of virtual tables allow users to do the following −

Structure data in a way that users or classes of users find natural or intuitive.
Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
Summarize data from various tables which can be used to generate reports.

Creating Views

使用 CREATE VIEW 语句创建数据库视图。视图可以从单表、多表或其他视图创建。

Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables or another view.

若要创建视图，用户必须根据具体实现具有适当的系统权限。

To create a view, a user must have the appropriate system privilege according to the specific implementation.

基本 CREATE VIEW 语法如下 −

The basic CREATE VIEW syntax is as follows −

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

可以在 SELECT 语句中包含多张表，就像在普通的 SQL SELECT 查询中使用它们一样。

You can include multiple tables in your SELECT statement in a similar way as you use them in a normal SQL SELECT query.

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下是如何从 CUSTOMERS 表创建视图的示例。此视图将用于从 CUSTOMERS 表中获取客户姓名和年龄。

Following is an example to create a view from the CUSTOMERS table. This view would be used to have customer name and age from the CUSTOMERS table.

SQL > CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM  CUSTOMERS;

现在，您可以像查询实际表一样查询 CUSTOMERS_VIEW。以下是示例。

Now, you can query CUSTOMERS_VIEW in a similar way as you query an actual table. Following is an example for the same.

SQL > SELECT * FROM CUSTOMERS_VIEW;

这会产生以下结果。

This would produce the following result.

+----------+-----+
| name     | age |
+----------+-----+
| Ramesh   |  32 |
| Khilan   |  25 |
| kaushik  |  23 |
| Chaitali |  25 |
| Hardik   |  27 |
| Komal    |  22 |
| Muffy    |  24 |
+----------+-----+

The WITH CHECK OPTION

WITH CHECK OPTION 是一个 CREATE VIEW 语句选项。WITH CHECK OPTION 的目的是确保所有 UPDATE 和 INSERT 满足视图定义中的条件。

The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the view definition.

如果它们不满足条件，UPDATE 或 INSERT 将返回错误。

If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.

以下代码块演示了使用 WITH CHECK OPTION 创建相同视图 CUSTOMERS_VIEW 的示例。

The following code block has an example of creating same view CUSTOMERS_VIEW with the WITH CHECK OPTION.

CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM  CUSTOMERS
WHERE age IS NOT NULL
WITH CHECK OPTION;

在这种情况下，WITH CHECK OPTION 应拒绝在视图的 AGE 列中输入任何 NULL 值，因为视图是由 AGE 列中没有 NULL 值的数据定义的。

The WITH CHECK OPTION in this case should deny the entry of any NULL values in the view’s AGE column, because the view is defined by data that does not have a NULL value in the AGE column.

Updating a View

视图可以在满足以下特定条件下更新：

A view can be updated under certain conditions which are given below −

The SELECT clause may not contain the keyword DISTINCT.
The SELECT clause may not contain summary functions.
The SELECT clause may not contain set functions.
The SELECT clause may not contain set operators.
The SELECT clause may not contain an ORDER BY clause.
The FROM clause may not contain multiple tables.
The WHERE clause may not contain subqueries.
The query may not contain GROUP BY or HAVING.
Calculated columns may not be updated.
All NOT NULL columns from the base table must be included in the view in order for the INSERT query to function.

因此，如果视图满足所有上述规则，您就可以更新该视图。以下代码块演示了更新 Ramesh 年龄的示例。

So, if a view satisfies all the above-mentioned rules then you can update that view. The following code block has an example to update the age of Ramesh.

SQL > UPDATE CUSTOMERS_VIEW
   SET AGE = 35
   WHERE name = 'Ramesh';

这最终会更新基本表 CUSTOMERS，并且会反映在视图本身中。现在，尝试查询基本表，SELECT 语句将产生以下结果。

This would ultimately update the base table CUSTOMERS and the same would reflect in the view itself. Now, try to query the base table and the SELECT statement would produce the following result.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Inserting Rows into a View

数据行可以插入到视图中。适用于 UPDATE 命令的规则也适用于 INSERT 命令。

Rows of data can be inserted into a view. The same rules that apply to the UPDATE command also apply to the INSERT command.

我们无法在 CUSTOMERS_VIEW 中插入行，因为我们没有在此视图中包含所有 NOT NULL 列，否则你可以像在表中那样以类似方式在视图中插入行。

Here, we cannot insert rows in the CUSTOMERS_VIEW because we have not included all the NOT NULL columns in this view, otherwise you can insert rows in a view in a similar way as you insert them in a table.

Deleting Rows into a View

可以从视图中删除数据行。适用于 UPDATE 和 INSERT 命令的相同规则也适用于 DELETE 命令。

Rows of data can be deleted from a view. The same rules that apply to the UPDATE and INSERT commands apply to the DELETE command.

以下是一个针对 AGE = 22 的记录进行删除的示例：

Following is an example to delete a record having AGE = 22.

SQL > DELETE FROM CUSTOMERS_VIEW
   WHERE age = 22;

这最终会从基本表 CUSTOMERS 中删除一行，并且此行也会反映在视图本身中。现在，尝试查询基本表，SELECT 语句会生成以下结果。

This would ultimately delete a row from the base table CUSTOMERS and the same would reflect in the view itself. Now, try to query the base table and the SELECT statement would produce the following result.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | 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 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Dropping Views

显然，如果你有一个视图，你需要一种方法来删除它，如果它不再需要。语法非常简单，如下所示：

Obviously, where you have a view, you need a way to drop the view if it is no longer needed. The syntax is very simple and is given below −

DROP VIEW view_name;

以下是一个从 CUSTOMERS 表中删除 CUSTOMERS_VIEW 的示例。

Following is an example to drop the CUSTOMERS_VIEW from the CUSTOMERS table.

DROP VIEW CUSTOMERS_VIEW;

SQL - Having Clause

HAVING Clause 允许你指定筛选哪些组结果出现在结果中的条件。

The HAVING Clause enables you to specify conditions that filter which group results appear in the results.

WHERE 子句对所选列设置条件，而 HAVING 子句对 GROUP BY 子句创建的组设置条件。

The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax

以下代码块显示 HAVING 子句在查询中的位置。

The following code block shows the position of the HAVING Clause in a query.

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

HAVING 子句必须在查询中位于 GROUP BY 子句后面，并且也必须在 ORDER BY 子句（如果使用）之前。以下代码块具有包括 HAVING 子句的 SELECT 语句的语法：

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following code block has the syntax of the SELECT statement including the HAVING clause −

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

Example

可以将 CUSTOMERS 表视为包含以下记录。

Consider the CUSTOMERS table having the following records.

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下是一个示例，它将显示相似年龄大于或等于 2 的记录。

Following is an example, which would display a record for a similar age count that would be more than or equal to 2.

SQL > SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;

这将产生以下结果 -

This would produce the following result −

+----+--------+-----+---------+---------+
| ID | NAME   | AGE | ADDRESS | SALARY  |
+----+--------+-----+---------+---------+
|  2 | Khilan |  25 | Delhi   | 1500.00 |
+----+--------+-----+---------+---------+

SQL - Transactions

事务是对数据库执行的一个工作单元。事务是按照逻辑顺序完成的工作单元或序列，无论是由用户手动完成还是由某种数据库程序自动完成。

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

事务是将一个或多个更改传播到数据库。例如，如果要创建记录或更新记录或从表中删除记录，那么你正在对此表执行事务。控制这些事务以确保数据完整性和处理数据库错误非常重要。

A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table, then you are performing a transaction on that table. It is important to control these transactions to ensure the data integrity and to handle database errors.

实际上，您会将许多 SQL 查询合并到一个组中，并将它们全部作为一个事务的一部分一起执行。

Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction.

Properties of Transactions

事务具有以下四个标准属性，通常用缩写词 ACID 表示。

Transactions have the following four standard properties, usually referred to by the acronym ACID.

Atomicity − ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.
Consistency − ensures that the database properly changes states upon a successfully committed transaction.
Isolation − enables transactions to operate independently of and transparent to each other.
Durability − ensures that the result or effect of a committed transaction persists in case of a system failure.

Transaction Control

以下命令用于控制事务。

The following commands are used to control transactions.

COMMIT − to save the changes.
ROLLBACK − to roll back the changes.
SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.
SET TRANSACTION − Places a name on a transaction.

Transactional Control Commands

事务控制命令仅与 DML Commands （如 INSERT、UPDATE 和 DELETE）一起使用。在创建或删除表时无法使用它们，因为这些操作会自动在数据库中提交。

Transactional control commands are only used with the DML Commands such as - INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.

The COMMIT Command

COMMIT 命令是用于将由事务调用的更改保存到数据库的事务命令。

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.

COMMIT 命令是用于将由事务调用的更改保存到数据库的事务命令。COMMIT 命令会将自上一次 COMMIT 或 ROLLBACK 命令以来执行的所有事务保存到数据库中。

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database. The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command.

COMMIT 命令的语法如下。

The syntax for the COMMIT command is as follows.

COMMIT;

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下是一个示例，它将删除表中 age = 25 的记录，然后 COMMIT 对数据库的更改。

Following is an example which would delete those records from the table which have age = 25 and then COMMIT the changes in the database.

SQL> DELETE FROM CUSTOMERS
   WHERE AGE = 25;
SQL> COMMIT;

因此，表中的两行将被删除，并且 SELECT 语句会生成以下结果。

Thus, two rows from the table would be deleted and the SELECT statement would produce the following result.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The ROLLBACK Command

ROLLBACK 命令是用于撤消尚未保存到数据库的事务的事务命令。该命令只能用于自上次发出 COMMIT 或 ROLLBACK 命令以来撤消事务。

The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

ROLLBACK 命令的语法如下：

The syntax for a ROLLBACK command is as follows −

ROLLBACK;

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下是一个示例，它将从表中删除那些年龄=25的记录，然后 ROLLBACK 数据库中的更改。

Following is an example, which would delete those records from the table which have the age = 25 and then ROLLBACK the changes in the database.

SQL> DELETE FROM CUSTOMERS
   WHERE AGE = 25;
SQL> ROLLBACK;

因此，delete 操作不会影响该表，并且 SELECT 语句会生成以下结果。

Thus, the delete operation would not impact the table and the SELECT statement would produce the following result.

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The SAVEPOINT Command

SAVEPOINT 是事务中的一个点，当您可以将事务还原到某个点而不还原整个事务时。

A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.

SAVEPOINT 命令的语法如下所示。

The syntax for a SAVEPOINT command is as shown below.

SAVEPOINT SAVEPOINT_NAME;

该命令的作用仅限于在所有事务语句中创建 SAVEPOINT。ROLLBACK 命令用于撤消一组事务。

This command serves only in the creation of a SAVEPOINT among all the transactional statements. The ROLLBACK command is used to undo a group of transactions.

回滚到 SAVEPOINT 的语法如下所示。

The syntax for rolling back to a SAVEPOINT is as shown below.

ROLLBACK TO SAVEPOINT_NAME;

以下是一个计划从 CUSTOMERS 表删除三个不同记录的示例。你希望在每次删除前创建一个保存点，以便你能随时回滚到任何保存点，将其相应的数据返回到其初始状态。

Following is an example where you plan to delete the three different records from the CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state.

Example

可以将 CUSTOMERS 表视为包含以下记录。

Consider the CUSTOMERS table having the following records.

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下代码块中包含了一系列的操作。

The following code block contains the series of operations.

SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.

在完成这三个删除操作后，假设你改变了想法，并决定回滚到标识为 SP2 的保存点。由于 SP2 是在第一次删除后创建的，因此会撤销最后两次删除操作 -

Now that the three deletions have taken place, let us assume that you have changed your mind and decided to ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was created after the first deletion, the last two deletions are undone −

SQL> ROLLBACK TO SP2;
Rollback complete.

请注意，由于您已回滚到 SP2，因此只发生了第一次删除。

Notice that only the first deletion took place since you rolled back to SP2.

SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.

The RELEASE SAVEPOINT Command

RELEASE SAVEPOINT 命令用于删除您创建的 SAVEPOINT。

The RELEASE SAVEPOINT command is used to remove a SAVEPOINT that you have created.

RELEASE SAVEPOINT 命令的语法如下。

The syntax for a RELEASE SAVEPOINT command is as follows.

RELEASE SAVEPOINT SAVEPOINT_NAME;

一旦释放了保存点，就无法再使用 ROLLBACK 命令来撤消自上次保存点执行的事务。

Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to undo transactions performed since the last SAVEPOINT.

The SET TRANSACTION Command

可以使用 SET TRANSACTION 命令来启动数据库事务。此命令用于为后续事务指定特征。例如，你可以将事务指定为只读或可读写。

The SET TRANSACTION command can be used to initiate a database transaction. This command is used to specify characteristics for the transaction that follows. For example, you can specify a transaction to be read only or read write.

SET TRANSACTION 命令的语法如下。

The syntax for a SET TRANSACTION command is as follows.

SET TRANSACTION [ READ WRITE | READ ONLY ];

SQL - Wildcard Operators

我们已经讨论过 SQL LIKE 运算符，该运算符用于使用通配符运算符将值与相似的值进行比较。

We have already discussed about the SQL LIKE operator, which is used to compare a value to similar values using the wildcard operators.

SQL 支持两个通配符运算符与 LIKE 运算符结合使用，具体说明如下表所示。

SQL supports two wildcard operators in conjunction with the LIKE operator which are explained in detail in the following table.

Sr.No.

Wildcard & Description

The percent sign (%) Matches one or more characters. Note − MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character.

The underscore () Matches one character. Note − MS Access uses a question mark (?) instead of the underscore () to match any one character.

百分号表示零、一个或多个字符。下划线表示一个数字或一个字符。这些符号可以组合使用。

The percent sign represents zero, one or multiple characters. The underscore represents a single number or a character. These symbols can be used in combinations.

Syntax

“%”和“_”运算符的基本语法如下所示。

The basic syntax of a '%' and a '_' operator is as follows.

SELECT * FROM table_name
WHERE column LIKE 'XXXX%'

or

SELECT * FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT * FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT * FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT * FROM table_name
WHERE column LIKE '_XXXX_'

您可以使用 AND 或 OR 运算符组合 N 个条件。此处，XXXX 可以是任何数字或字符串值。

You can combine N number of conditions using the AND or the OR operators. Here, XXXX could be any numeric or string value.

Example

下表列举了许多示例，展示了包含带有 “%”和 “_”运算符的不同 LIKE 子句的 WHERE 部分。

The following table has a number of examples showing the WHERE part having different LIKE clauses with '%' and '_' operators.

Sr.No.	Statement & Description
1	WHERE SALARY LIKE '200%' Finds any values that start with 200.
2	WHERE SALARY LIKE '%200%' Finds any values that have 200 in any position.
3	WHERE SALARY LIKE '_00%' Finds any values that have 00 in the second and third positions.
4	WHERE SALARY LIKE '2_%_%' Finds any values that start with 2 and are at least 3 characters in length.
5	WHERE SALARY LIKE '%2' Finds any values that end with 2.
6	WHERE SALARY LIKE '_2%3' Finds any values that have a 2 in the second position and end with a 3.
7	WHERE SALARY LIKE '2___3' Finds any values in a five-digit number that start with 2 and end with 3.

让我们举一个实际的例子，假设 CUSTOMERS 表中有以下记录。

Let us take a real example, consider the CUSTOMERS table having the following records.

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下代码块是一个示例，它将显示 CUSTOMERS 表中工资以 200 开头的所有记录。

The following code block is an example, which would display all the records from the CUSTOMERS table where the SALARY starts with 200.

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

这会产生以下结果。

This would produce the following result.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
+----+----------+-----+-----------+----------+

SQL - Date Functions

下表列出了通过 SQL 提供的所有重要日期和时间相关函数。RDBMS 支持其他各种函数。给定的列表基于 MySQL RDBMS。

The following table has a list of all the important Date and Time related functions available through SQL. There are various other functions supported by your RDBMS. The given list is based on MySQL RDBMS.

Sr.No.	Function & Description
1	ADDDATE()Adds dates
2	ADDTIME()Adds time
3	CONVERT_TZ()Converts from one timezone to another
4	CURDATE()Returns the current date
5	CURRENT_DATE(), CURRENT_DATESynonyms for CURDATE()
6	CURRENT_TIME(), CURRENT_TIMESynonyms for CURTIME()
7	CURRENT_TIMESTAMP(), CURRENT_TIMESTAMPSynonyms for NOW()
8	CURTIME()Returns the current time
9	DATE_ADD()Adds two dates
10	DATE_FORMAT()Formats date as specified
11	DATE_SUB()Subtracts two dates
12	DATE()Extracts the date part of a date or datetime expression
13	DATEDIFF()Subtracts two dates
14	DAY()Synonym for DAYOFMONTH()
15	DAYNAME()Returns the name of the weekday
16	DAYOFMONTH()Returns the day of the month (1-31)
17	DAYOFWEEK()Returns the weekday index of the argument
18	DAYOFYEAR()Returns the day of the year (1-366)
19	EXTRACTExtracts part of a date
20	FROM_DAYS()Converts a day number to a date
21	FROM_UNIXTIME()Formats date as a UNIX timestamp
22	HOUR()Extracts the hour
23	LAST_DAYReturns the last day of the month for the argument
24	LOCALTIME(), LOCALTIMESynonym for NOW()
25	LOCALTIMESTAMP, LOCALTIMESTAMP()Synonym for NOW()
26	MAKEDATE()Creates a date from the year and day of year
27	MAKETIMEMAKETIME()
28	MICROSECOND()Returns the microseconds from argument
29	MINUTE()Returns the minute from the argument
30	MONTH()Return the month from the date passed
31	MONTHNAME()Returns the name of the month
32	NOW()Returns the current date and time
33	PERIOD_ADD()Adds a period to a year-month
34	PERIOD_DIFF()Returns the number of months between periods
35	QUARTER()Returns the quarter from a date argument
36	SEC_TO_TIME()Converts seconds to 'HH:MM:SS' format
37	SECOND()Returns the second (0-59)
38	STR_TO_DATE()Converts a string to a date
39	SUBDATE()When invoked with three arguments a synonym for DATE_SUB()
40	SUBTIME()Subtracts times
41	SYSDATE()Returns the time at which the function executes
42	TIME_FORMAT()Formats as time
43	TIME_TO_SEC()Returns the argument converted to seconds
44	TIME()Extracts the time portion of the expression passed
45	TIMEDIFF()Subtracts time
46	TIMESTAMP()With a single argument this function returns the date or datetime expression. With two arguments, the sum of the arguments
47	TIMESTAMPADD()Adds an interval to a datetime expression
48	TIMESTAMPDIFF()Subtracts an interval from a datetime expression
49	TO_DAYS()Returns the date argument converted to days
50	UNIX_TIMESTAMP()Returns a UNIX timestamp
51	UTC_DATE()Returns the current UTC date
52	UTC_TIME()Returns the current UTC time
53	UTC_TIMESTAMP()Returns the current UTC date and time
54	WEEK()Returns the week number
55	WEEKDAY()Returns the weekday index
56	WEEKOFYEAR()Returns the calendar week of the date (1-53)
57	YEAR()Returns the year
58	YEARWEEK()Returns the year and week

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

当使用第二个参数的 INTERVAL 形式时，ADDDATE() 是 DATE_ADD() 的同义词。相关函数 SUBDATE() 是 DATE_SUB() 的同义词。有关 INTERVAL 单位参数的信息，请参见 DATE_ADD() 的讨论。

When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see the discussion for DATE_ADD().

mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY)                 |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| ADDDATE('1998-01-02', INTERVAL 31 DAY)                  |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

当使用第二个参数的 days 形式时，MySQL 将其视为要添加到 expr 中的天数整数。

When invoked with the days form of the second argument, MySQL treats it as an integer number of days to be added to expr.

mysql> SELECT ADDDATE('1998-01-02', 31);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY)                 |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

ADDTIME(expr1,expr2)

ADDTIME() 将 expr2 添加到 expr1 并返回结果。expr1 是时间或 datetime 表达式，而 expr2 是时间表达。

ADDTIME() adds expr2 to expr1 and returns the result. The expr1 is a time or datetime expression, while the expr2 is a time expression.

mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59.999999','1 1:1:1.000002') |
+---------------------------------------------------------+
| 1998-01-02 01:01:01.000001                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CONVERT_TZ(dt,from_tz,to_tz)

此命令将由 from_tz 给出的时区中的 datetime 值 dt 转换为 by to_tz 给出的时区，并返回结果值。如果参数无效，此函数将返回 NULL。

This converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value. This function returns NULL if the arguments are invalid.

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','GMT','MET')           |
+---------------------------------------------------------+
| 2004-01-01 13:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00')     |
+---------------------------------------------------------+
| 2004-01-01 22:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CURDATE()

返回值的格式为“YYYY-MM-DD”或 YYYYMMDD，具体取决于函数是在字符串还是数值上下文中使用。

Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or in a numeric context.

mysql> SELECT CURDATE();
+---------------------------------------------------------+
| CURDATE()                                               |
+---------------------------------------------------------+
| 1997-12-15                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CURDATE() + 0;
+---------------------------------------------------------+
| CURDATE() + 0                                           |
+---------------------------------------------------------+
| 19971215                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CURRENT_DATE and CURRENT_DATE()

CURRENT_DATE 和 CURRENT_DATE() 与 CURDATE() 同义。

CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE()

CURTIME()

返回值的格式为“HH:MM:SS”或 HHMMSS，具体取决于函数是在字符串还是数值上下文中使用。此值按当前时区表示。

Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or in a numeric context. The value is expressed in the current time zone.

mysql> SELECT CURTIME();
+---------------------------------------------------------+
| CURTIME()                                               |
+---------------------------------------------------------+
| 23:50:26                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME() + 0;
+---------------------------------------------------------+
| CURTIME() + 0                                           |
+---------------------------------------------------------+
| 235026                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CURRENT_TIME and CURRENT_TIME()

CURRENT_TIME 和 CURRENT_TIME() 与 CURTIME() 同义。

CURRENT_TIME and CURRENT_TIME() are synonyms for CURTIME().

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP 和 CURRENT_TIMESTAMP() 与 NOW() 同义。

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().

DATE(expr)

提取日期或日期时间表达式 expr 的日期部分。

Extracts the date part of the date or datetime expression expr.

mysql> SELECT DATE('2003-12-31 01:02:03');
+---------------------------------------------------------+
| DATE('2003-12-31 01:02:03')                             |
+---------------------------------------------------------+
|  2003-12-31                                             |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DATEDIFF(expr1,expr2)

DATEDIFF() 返回从一个日期到另一个日期的 expr1 . expr2，形式为天数。expr1 和 expr2 都是日期或日期时间表达式。计算中只使用值的日期部分。

DATEDIFF() returns expr1 . expr2 expressed as a value in days from one date to the other. Both expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
+---------------------------------------------------------+
| DATEDIFF('1997-12-31 23:59:59','1997-12-30')            |
+---------------------------------------------------------+
| 1                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

这些函数执行日期算法。 date 是指定起始日期的 DATETIME 或 DATE 值。 expr 是指定要从起始日期添加或减去的间隔值。expr 是一个字符串；它可能以“-”开头，表示负间隔。

These functions perform date arithmetic. The date is a DATETIME or DATE value specifying the starting date. The expr is an expression specifying the interval value to be added or subtracted from the starting date. The expr is a string; it may start with a '-' for negative intervals.

unit 是指定应该如何解释表达式的关键字。

A unit is a keyword indicating the units in which the expression should be interpreted.

INTERVAL 关键字和单位说明符不区分大小写。

The INTERVAL keyword and the unit specifier are not case sensitive.

下表显示了 expr 参数对于每个单位值的预期形式。

The following table shows the expected form of the expr argument for each unit value.

unit Value

Expected exprFormat

MICROSECOND

MICROSECONDS

SECOND

SECONDS

MINUTE

MINUTES

HOUR

HOURS

DAY

DAYS

WEEK

WEEKS

MONTH

MONTHS

QUARTER

QUARTERS

YEAR

YEARS

SECOND_MICROSECOND

'SECONDS.MICROSECONDS'

MINUTE_MICROSECOND

'MINUTES.MICROSECONDS'

MINUTE_SECOND

'MINUTES:SECONDS'

HOUR_MICROSECOND

'HOURS.MICROSECONDS'

HOUR_SECOND

'HOURS:MINUTES:SECONDS'

HOUR_MINUTE

'HOURS:MINUTES'

DAY_MICROSECOND

'DAYS.MICROSECONDS'

DAY_SECOND

'DAYS HOURS:MINUTES:SECONDS'

DAY_MINUTE

'DAYS HOURS:MINUTES'

DAY_HOUR

'DAYS HOURS'

YEAR_MONTH

'YEARS-MONTHS'

MySQL 5.0.0 版本中提供了 QUARTER 和 WEEK 这些值。

The values QUARTER and WEEK are available from the MySQL 5.0.0. version.

mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
   -> INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59', INTERVAL...             |
+---------------------------------------------------------+
| 1998-01-01 00:01:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
+---------------------------------------------------------+
| DATE_ADD('1999-01-01', INTERVAL 1 HOUR)                 |
+---------------------------------------------------------+
| 1999-01-01 01:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DATE_FORMAT(date,format)

此命令根据指定格式字符串格式化日期值。

This command formats the date value as per the format string.

以下说明符可在格式字符串中使用。格式说明符字符前需要加“%”字符。

The following specifiers may be used in the format string. The '%' character is required before the format specifier characters.

Sr.No.	Specifier & Description
1	%a Abbreviated weekday name (Sun..Sat)
2	%b Abbreviated month name (Jan..Dec)
3	%c Month, numeric (0..12)
4	%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, .)
5	%d Day of the month, numeric (00..31)
6	%e Day of the month, numeric (0..31)
7	%f Microseconds (000000..999999)
8	%H Hour (00..23)
9	%h Hour (01..12)
10	%I Hour (01..12)
11	%i Minutes, numeric (00..59)
12	%j Day of year (001..366)
13	%k Hour (0..23)
14	%l Hour (1..12)
15	%M Month name (January..December)
16	%m Month, numeric (00..12)
17	%p AM or PM
18	%r Time, 12-hour (hh:mm:ss followed by AM or PM)
19	%S Seconds (00..59)
20	%s Seconds (00..59)
21	%T Time, 24-hour (hh:mm:ss)
22	%U Week (00..53), where Sunday is the first day of the week
23	%u Week (00..53), where Monday is the first day of the week
24	%V Week (01..53), where Sunday is the first day of the week; used with %X
25	%v Week (01..53), where Monday is the first day of the week; used with %x
26	%W Weekday name (Sunday..Saturday)
27	%w Day of the week (0=Sunday..6=Saturday)
28	%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
29	%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
30	%Y Year, numeric, four digits
31	%y Year, numeric (two digits)
32	%% A literal .%. character
33	%x x, for any.x. not listed above

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y')          |
+---------------------------------------------------------+
| Saturday October 1997                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00'
   -> '%H %k %I %r %T %S %w');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00.......                 |
+---------------------------------------------------------+
|  22 22 10 10:23:00 PM 22:23:00 00 6                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DATE_SUB(date,INTERVAL expr unit)

这与 DATE_ADD() 函数类似。

This is similar to the DATE_ADD() function.

DAY(date)

DAY() 是 DAYOFMONTH() 函数的同义词。

The DAY() is a synonym for the DAYOFMONTH() function.

DAYNAME(date)

返回指定日期的星期名称。

Returns the name of the weekday for date.

mysql> SELECT DAYNAME('1998-02-05');
+---------------------------------------------------------+
| DAYNAME('1998-02-05')                                   |
+---------------------------------------------------------+
| Thursday                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DAYOFMONTH(date)

返回指定日期的当月第几天，范围为 0 至 31。

Returns the day of the month for date, in the range 0 to 31.

mysql> SELECT DAYOFMONTH('1998-02-03');
+---------------------------------------------------------+
| DAYOFMONTH('1998-02-03')                                |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DAYOFWEEK(date)

返回指定日期的星期索引（1 = 周日、2 = 周一、……、7 = 周六）。这些索引值与 ODBC 标准相对应。

Returns the weekday index for date (1 = Sunday, 2 = Monday, ., 7 = Saturday). These index values correspond to the ODBC standard.

mysql> SELECT DAYOFWEEK('1998-02-03');
+---------------------------------------------------------+
|DAYOFWEEK('1998-02-03')                                  |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DAYOFYEAR(date)

返回指定日期的当年的第几天，范围为 1 至 366。

Returns the day of the year for date, in the range 1 to 366.

mysql> SELECT DAYOFYEAR('1998-02-03');
+---------------------------------------------------------+
| DAYOFYEAR('1998-02-03')                                 |
+---------------------------------------------------------+
| 34                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

EXTRACT(unit FROM date)

EXTRACT() 函数使用与 DATE_ADD() 或 DATE_SUB() 相同类型的单位说明符，但不是执行日期算术，而是从一个日期中提取部件。

The EXTRACT() function uses the same kinds of unit specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic.

mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');
+---------------------------------------------------------+
| EXTRACT(YEAR FROM '1999-07-02')                         |
+---------------------------------------------------------+
| 1999                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');
+---------------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03')          |
+---------------------------------------------------------+
| 199907                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

FROM_DAYS(N)

针对指定的第 N 天，返回一个 DATE 值。

Given a day number N, returns a DATE value.

mysql> SELECT FROM_DAYS(729669);
+---------------------------------------------------------+
| FROM_DAYS(729669)                                       |
+---------------------------------------------------------+
| 1997-10-07                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Note − 在旧日期中应谨慎使用 FROM_DAYS()。它不适用于格里高利历（1582 年）之前的日期。

Note − Use FROM_DAYS() with caution on old dates. It is not intended for use with values that precede the advent of the Gregorian calendar (1582).

FROM_UNIXTIME(unix_timestamp)

FROM_UNIXTIME(unix_timestamp,format)

以“YYYY-MM-DD HH:MM:SS”或“YYYYMMDDHHMMSS”格式，返回 unix_timestamp 参数的表示，具体取决于函数是在字符串中使用还是在数字上下文中使用。该值以当前时区表示。unix_timestamp 参数是内部时间戳值，该值由 UNIX_TIMESTAMP() 函数生成。

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or in a numeric context. The value is expressed in the current time zone. The unix_timestamp argument is an internal timestamp values, which are produced by the UNIX_TIMESTAMP() function.

如果提供了格式，则结果将根据格式字符串进行格式化，这种格式的使用方式与 DATE_FORMAT() 函数所列的使用方式相同。

If the format is given, the result is formatted according to the format string, which is used in the same way as is listed in the entry for the DATE_FORMAT() function.

mysql> SELECT FROM_UNIXTIME(875996580);
+---------------------------------------------------------+
| FROM_UNIXTIME(875996580)                                |
+---------------------------------------------------------+
| 1997-10-04 22:23:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

HOUR(time)

返回时间的小时。对于当天时间值，返回值范围为 0 到 23。不过，TIME 值的范围实际上大得多，因此 HOUR 可以返回值大于 23。

Returns the hour for time. The range of the return value is 0 to 23 for time-of-day values. However, the range of TIME values actually is much larger, so HOUR can return values greater than 23.

mysql> SELECT HOUR('10:05:03');
+---------------------------------------------------------+
| HOUR('10:05:03')                                        |
+---------------------------------------------------------+
| 10                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LAST_DAY(date)

接受一个日期或 datetime 值，并返回该月的最后一天的相应值。如果参数无效，则返回 NULL。

Takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL if the argument is invalid.

mysql> SELECT LAST_DAY('2003-02-05');
+---------------------------------------------------------+
| LAST_DAY('2003-02-05')                                  |
+---------------------------------------------------------+
| 2003-02-28                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LOCALTIME and LOCALTIME()

LOCALTIME 和 LOCALTIME() 是 NOW() 的同义词。

LOCALTIME and LOCALTIME() are synonyms for NOW().

LOCALTIMESTAMP and LOCALTIMESTAMP()

LOCALTIMESTAMP 和 LOCALTIMESTAMP() 是 NOW() 的同义词。

LOCALTIMESTAMP and LOCALTIMESTAMP() are synonyms for NOW().

MAKEDATE(year,dayofyear)

返回一个日期，给定年份和年份内日期的值。年份内日期值必须大于 0，否则结果将为 NULL。

Returns a date, given year and day-of-year values. The dayofyear value must be greater than 0 or the result will be NULL.

mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);
+---------------------------------------------------------+
| MAKEDATE(2001,31), MAKEDATE(2001,32)                    |
+---------------------------------------------------------+
| '2001-01-31', '2001-02-01'                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MAKETIME(hour,minute,second)

根据小时、分钟和秒参数返回一个时间值。

Returns a time value calculated from the hour, minute and second arguments.

mysql> SELECT MAKETIME(12,15,30);
+---------------------------------------------------------+
| MAKETIME(12,15,30)                                      |
+---------------------------------------------------------+
| '12:15:30'                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MICROSECOND(expr)

将时间或 datetime 表达式（expr）中的微秒数作为 0 到 999999 范围内的数字进行返回。

Returns the microseconds from the time or datetime expression (expr) as a number in the range from 0 to 999999.

mysql> SELECT MICROSECOND('12:00:00.123456');
+---------------------------------------------------------+
| MICROSECOND('12:00:00.123456')                          |
+---------------------------------------------------------+
| 123456                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MINUTE(time)

返回时间中的分钟，范围为 0 到 59。

Returns the minute for time, in the range 0 to 59.

mysql> SELECT MINUTE('98-02-03 10:05:03');
+---------------------------------------------------------+
| MINUTE('98-02-03 10:05:03')                             |
+---------------------------------------------------------+
| 5                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MONTH(date)

返回日期中的月份，范围为 0 到 12。

Returns the month for date, in the range 0 to 12.

mysql> SELECT MONTH('1998-02-03')
+---------------------------------------------------------+
| MONTH('1998-02-03')                                     |
+---------------------------------------------------------+
| 2                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MONTHNAME(date)

返回日期中月份的全名。

Returns the full name of the month for a date.

mysql> SELECT MONTHNAME('1998-02-05');
+---------------------------------------------------------+
| MONTHNAME('1998-02-05')                                 |
+---------------------------------------------------------+
| February                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

NOW()

以 'YYYY-MM-DD HH:MM:SS' 或 YYYYMMDDHHMMSS 格式（取决于函数在字符串或数字上下文中使用）返回当前日期和时间值。该值以当前时区表示。

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. This value is expressed in the current time zone.

mysql> SELECT NOW();
+---------------------------------------------------------+
| NOW()                                                   |
+---------------------------------------------------------+
| 1997-12-15 23:50:26                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

PERIOD_ADD(P,N)

在一段时期 P（采用 YYMM 或 YYYYMM 格式）中增加 N 个月。以 YYYYMM 格式返回一个值。请注意，时期参数 P 不是一个日期值。

Adds N months to a period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note that the period argument P is not a date value.

mysql> SELECT PERIOD_ADD(9801,2);
+---------------------------------------------------------+
| PERIOD_ADD(9801,2)                                      |
+---------------------------------------------------------+
| 199803                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

PERIOD_DIFF(P1,P2)

返回时期 P1 与 P2 之间的月份数。这些时期 P1 和 P2 应当为 YYMM 或 YYYYMM 格式。请注意，时期参数 P1 和 P2 不是日期值。

Returns the number of months between periods P1 and P2. These periods P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values.

mysql> SELECT PERIOD_DIFF(9802,199703);
+---------------------------------------------------------+
| PERIOD_DIFF(9802,199703)                                |
+---------------------------------------------------------+
| 11                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

QUARTER(date)

返回日期中一年的季度，范围为 1 到 4。

Returns the quarter of the year for date, in the range 1 to 4.

mysql> SELECT QUARTER('98-04-01');
+---------------------------------------------------------+
| QUARTER('98-04-01')                                     |
+---------------------------------------------------------+
| 2                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SECOND(time)

返回时间中的秒，范围为 0 到 59。

Returns the second for time, in the range 0 to 59.

mysql> SELECT SECOND('10:05:03');
+---------------------------------------------------------+
| SECOND('10:05:03')                                      |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SEC_TO_TIME(seconds)

将秒参数转换为小时、分钟和秒，然后以 'HH:MM:SS' 或 HHMMSS 格式（取决于函数在字符串或数字上下文中使用）返回一个值。

Returns the seconds argument, converted to hours, minutes and seconds, as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context.

mysql> SELECT SEC_TO_TIME(2378);
+---------------------------------------------------------+
| SEC_TO_TIME(2378)                                       |
+---------------------------------------------------------+
| 00:39:38                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

STR_TO_DATE(str,format)

这是 DATE_FORMAT() 函数的逆函数。它使用一个字符串 str 和格式化字符串 format。如果格式化字符串包含日期和时间部分，则 STR_TO_DATE() 函数返回一个 DATETIME 值。否则，如果字符串只包含日期或时间部分，则它会返回一个 DATE 或 TIME 值。

This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. The STR_TO_DATE() function returns a DATETIME value if the format string contains both date and time parts. Else, it returns a DATE or TIME value if the string contains only date or time parts.

mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
+---------------------------------------------------------+
| STR_TO_DATE('04/31/2004', '%m/%d/%Y')                   |
+---------------------------------------------------------+
| 2004-04-31                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SUBDATE(date,INTERVAL expr unit) and SUBDATE(expr,days)

当使用第二个参数的 INTERVAL 形式调用时，SUBDATE() 是 DATE_SUB() 的同义词。有关 INTERVAL 单位参数的信息，请参阅 DATE_ADD() 的讨论。

When invoked with the INTERVAL form of the second argument, SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see the discussion for DATE_ADD().

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY)                 |
+---------------------------------------------------------+
| 1997-12-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| SUBDATE('1998-01-02', INTERVAL 31 DAY)                  |
+---------------------------------------------------------+
| 1997-12-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SUBTIME(expr1,expr2)

SUBTIME() 函数返回 expr1 . expr2，以与 expr1 相同格式的值表示。expr1 值是一个时间或 datetime 表达式，而 expr2 值是一个时间表达式。

The SUBTIME() function returns expr1 . expr2 expressed as a value in the same format as expr1. The expr1 value is a time or a datetime expression, while the expr2 value is a time expression.

mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999',
   -> '1 1:1:1.000002');
+---------------------------------------------------------+
| SUBTIME('1997-12-31 23:59:59.999999'...                 |
+---------------------------------------------------------+
| 1997-12-30 22:58:58.999997                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SYSDATE()

以“YYYY-MM-DD HH:MM:SS”或YYYYMMDDHHMMSS格式返回当前日期和时间，具体取决于该函数是在字符串还是数字上下文中使用。

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or in a numeric context.

mysql> SELECT SYSDATE();
+---------------------------------------------------------+
| SYSDATE()                                               |
+---------------------------------------------------------+
| 2006-04-12 13:47:44                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIME(expr)

提取时间或日期时间表达式 expr 的时间部分并将其作为字符串返回。

Extracts the time part of the time or datetime expression expr and returns it as a string.

mysql> SELECT TIME('2003-12-31 01:02:03');
+---------------------------------------------------------+
| TIME('2003-12-31 01:02:03')                             |
+---------------------------------------------------------+
| 01:02:03                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIMEDIFF(expr1,expr2)

TIMEDIFF()函数返回expr1。expr2表示为时间值。这些expr1和expr2值是时间或日期和时间表达式，但它们都必须是同类型的。

The TIMEDIFF() function returns expr1 . expr2 expressed as a time value. These expr1 and expr2 values are time or date-and-time expressions, but both must be of the same type.

mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001',
   -> '1997-12-30 01:01:01.000002');
+---------------------------------------------------------+
| TIMEDIFF('1997-12-31 23:59:59.000001'.....              |
+---------------------------------------------------------+
|  46:58:57.999999                                        |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIMESTAMP(expr), TIMESTAMP(expr1,expr2)

对于具有单个参数的函数，则返回日期或日期时间表达式expr作为日期时间值。对于具有两个参数的函数，它会将时间表达式expr2添加到日期或日期时间表达式 expr1 中并将结果作为日期时间值返回。

With a single argument, this function returns the date or datetime expression expr as a datetime value. With two arguments, it adds the time expression expr2 to the date or datetime expression expr1 and returns the result as a datetime value.

mysql> SELECT TIMESTAMP('2003-12-31');
+---------------------------------------------------------+
| TIMESTAMP('2003-12-31')                                 |
+---------------------------------------------------------+
| 2003-12-31 00:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIMESTAMPADD(unit,interval,datetime_expr)

此函数会将整数表达式interval添加到日期或日期时间表达式 datetime_expr 中。interval的单位由unit参数提供，它应该是以下值之一−

This function adds the integer expression interval to the date or datetime expression datetime_expr. The unit for interval is given by the unit argument, which should be one of the following values −

FRAC_SECOND
SECOND, MINUTE
HOUR, DAY
WEEK
MONTH
QUARTER or
YEAR

可以使用显示的一个关键字或SQL_TSI_前缀指定单位值。

The unit value may be specified using one of the keywords as shown or with a prefix of SQL_TSI_.

例如，DAY和SQL_TSI_DAY都是合法的。

For example, DAY and SQL_TSI_DAY both are legal.

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
+---------------------------------------------------------+
| TIMESTAMPADD(MINUTE,1,'2003-01-02')                     |
+---------------------------------------------------------+
| 2003-01-02 00:01:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

返回日期或日期时间表达式datetime_expr1和datetime_expr2之间的整数差。结果的单位由unit参数给出。unit的合法值与TIMESTAMPADD()函数的描述中列出的值相同。

Returns the integer difference between the date or datetime expressions datetime_expr1 and datetime_expr2. The unit for the result is given by the unit argument. The legal values for the unit are the same as those listed in the description of the TIMESTAMPADD() function.

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
+---------------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01')          |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIME_FORMAT(time,format)

此函数的用法与DATE_FORMAT()函数类似，但是格式字符串只能包含小时、分钟和秒的格式说明符。

This function is used like the DATE_FORMAT() function, but the format string may contain format specifiers only for hours, minutes and seconds.

如果时间值包含大于23的小时部分，那么%{s3}和%{s4}小时格式说明符会生成大于通常的0到23范围的值。其他小时格式说明符会生成12的模数小时值。

If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0 to 23. The other hour format specifiers produce the hour value modulo 12.

mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
+---------------------------------------------------------+
| TIME_FORMAT('100:00:00', '%H %k %h %I %l')              |
+---------------------------------------------------------+
| 100 100 04 04 4                                         |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIME_TO_SEC(time)

返回已转换为秒的时间参数。

Returns the time argument converted to seconds.

mysql> SELECT TIME_TO_SEC('22:23:00');
+---------------------------------------------------------+
| TIME_TO_SEC('22:23:00')                                 |
+---------------------------------------------------------+
| 80580                                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TO_DAYS(date)

如果给定日期，则返回一个日号（自0年以来的天数）。

Given a date, returns a day number (the number of days since year 0).

mysql> SELECT TO_DAYS(950501);
+---------------------------------------------------------+
| TO_DAYS(950501)                                         |
+---------------------------------------------------------+
| 728779                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

如果在没有参数的情况下调用此函数，则它会以无符号整数的形式返回一个Unix时间戳（自“1970-01-01 00:00:00”UTC以来的秒数）。如果使用日期参数调用UNIX_TIMESTAMP()，则它会将参数的值以自“1970-01-01 00:00:00”UTC以来的秒数返回。日期可以是DATE字符串、DATETIME字符串、TIMESTAMP或YYMMDD或YYYYMMDD格式的数字。

If called with no argument, this function returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD.

mysql> SELECT UNIX_TIMESTAMP();
+---------------------------------------------------------+
| UNIX_TIMESTAMP()                                        |
+---------------------------------------------------------+
| 882226357                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
+---------------------------------------------------------+
| UNIX_TIMESTAMP('1997-10-04 22:23:00')                   |
+---------------------------------------------------------+
| 875996580                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

UTC_DATE, UTC_DATE()

以“YYYY-MM-DD”或YYYYMMDD格式的值返回当前UTC日期，具体取决于该函数是在字符串还是数字上下文中使用。

Returns the current UTC date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
+---------------------------------------------------------+
| UTC_DATE(), UTC_DATE() + 0                              |
+---------------------------------------------------------+
| 2003-08-14, 20030814                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

UTC_TIME, UTC_TIME()

以“HH:MM:SS”或HHMMSS格式的值返回当前UTC时间，具体取决于该函数是在字符串还是数字上下文中使用。

Returns the current UTC time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context.

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
+---------------------------------------------------------+
| UTC_TIME(), UTC_TIME() + 0                              |
+---------------------------------------------------------+
| 18:07:53, 180753                                        |
+---------------------------------------------------------+
1 row in set (0.00 sec)

UTC_TIMESTAMP, UTC_TIMESTAMP()

以“YYYY-MM-DD HH:MM:SS”或YYYYMMDDHHMMSS格式的值返回当前UTC日期和时间，具体取决于该函数是在字符串还是数字上下文中使用。

Returns the current UTC date and time as a value in 'YYYY-MM-DD HH:MM:SS' or in a YYYYMMDDHHMMSS format, depending on whether the function is used in a string or in a numeric context.

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
+---------------------------------------------------------+
| UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0                    |
+---------------------------------------------------------+
| 2003-08-14 18:08:04, 20030814180804                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

WEEK(date[,mode])

此函数返回日期的星期数。WEEK()的双参数形式允许您指定星期从星期日还是星期一开始，以及返回值是否应该在0到53或从1到53的范围内。如果省去了mode参数，则会使用default_week_format系统变量的值

This function returns the week number for date. The two-argument form of WEEK() allows you to specify whether the week starts on a Sunday or a Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used

Mode	First Day of week	Range	Week 1 is the first week.
0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with more than 3 days this year
2	Sunday	1-53	with a Sunday in this year
3	Monday	1-53	with more than 3 days this year
4	Sunday	0-53	with more than 3 days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	1-53	with more than 3 days this year
7	Monday	1-53	with a Monday in this year

mysql> SELECT WEEK('1998-02-20');
+---------------------------------------------------------+
| WEEK('1998-02-20')                                      |
+---------------------------------------------------------+
| 7                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

WEEKDAY(date)

返回日期的星期索引（0 = 星期一，1 = 星期二，. 6 = 星期日）。

Returns the weekday index for date (0 = Monday, 1 = Tuesday, . 6 = Sunday).

mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
+---------------------------------------------------------+
| WEEKDAY('1998-02-03 22:23:00')                          |
+---------------------------------------------------------+
| 1                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

WEEKOFYEAR(date)

返回日期的日历周，范围为 1 到 53 的数字。WEEKOFYEAR() 是一个兼容性函数，相当于 WEEK(date,3)。

Returns the calendar week of the date as a number in the range from 1 to 53. WEEKOFYEAR() is a compatibility function that is equivalent to WEEK(date,3).

mysql> SELECT WEEKOFYEAR('1998-02-20');
+---------------------------------------------------------+
| WEEKOFYEAR('1998-02-20')                                |
+---------------------------------------------------------+
| 8                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

YEAR(date)

返回日期的年份，范围为 1000 到 9999，或者 .zero.date 的 0。

Returns the year for date, in the range 1000 to 9999, or 0 for the .zero. date.

mysql> SELECT YEAR('98-02-03');
+---------------------------------------------------------+
| YEAR('98-02-03')                                        |
+---------------------------------------------------------+
| 1998                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

YEARWEEK(date), YEARWEEK(date,mode)

返回日期的年份和周数。mode 参数的工作方式与 WEEK() 函数的 mode 参数完全相同。对于今年的第一周和最后一周，结果中的年份可能与日期参数中的年份不同。

Returns the year and the week for a date. The mode argument works exactly like the mode argument to the WEEK() function. The year in the result may be different from the year in the date argument for the first and the last week of the year.

mysql> SELECT YEARWEEK('1987-01-01');
+---------------------------------------------------------+
| YEAR('98-02-03')YEARWEEK('1987-01-01')                  |
+---------------------------------------------------------+
| 198653                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Note - 对于可选参数 0 或 1（那时 WEEK() 会返回给定年份中的一周），周数不同于 WEEK() 函数返回的周数 (0)。

Note − The week number is different from what the WEEK() function would return (0) for optional arguments 0 or 1, as WEEK() then returns the week in the context of the given year.

SQL - Temporary Tables

What are Temporary Tables?

有些 RDBMS 支持临时表。临时表是一项极好的功能，使您可以 store and process intermediate results 使用与在典型 SQL Server 表中可以使用相同的筛选、更新与联接功能。

There are RDBMS, which support temporary tables. Temporary Tables are a great feature that lets you store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables.

在某些情况下，临时表可能对保存临时数据非常有用。应该了解关于临时表的最重要的事情是，在当前客户端会话终止时，这些表将被删除。

The temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be known for temporary tables is that they will be deleted when the current client session terminates.

MySQL 3.23 及更高版本中提供了临时表。如果您使用的是比 3.23 更旧版本的 MySQL，那么您不能使用临时表，但可以使用 heap tables 。

Temporary tables are available in MySQL version 3.23 onwards. If you use an older version of MySQL than 3.23, you can’t use temporary tables, but you can use heap tables.

如前所述，临时表只会持续到会话结束为止。如果您在 PHP 脚本中运行代码，那么临时表将在脚本执行完毕后自动销毁。如果您通过 MySQL 客户端程序连接到 MySQL 数据库服务器，那么该临时表将一直存在到您关闭客户端或手动销毁表为止。

As stated earlier, temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.

Example

以下示例展示了临时表的使用方法。

Here is an example showing you the usage of a temporary table.

mysql> CREATE TEMPORARY TABLE SALESSUMMARY (
   -> product_name VARCHAR(50) NOT NULL
   -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
   -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
   -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SALESSUMMARY
   -> (product_name, total_sales, avg_unit_price, total_units_sold)
   -> VALUES
   -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SALESSUMMARY;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

如果您发出 SHOW TABLES 命令，那么您的临时表不会列在列表中。现在，如果您注销 MySQL 会话并发出 SELECT 命令，那么您将发现数据库中没有可用数据。您的临时表甚至都不会存在。

When you issue a SHOW TABLES command, then your temporary table will not be listed out in the list. Now, if you log out of the MySQL session and then issue a SELECT command, you will find no data available in the database. Even your temporary table will not be existing.

Dropping Temporary Tables

默认情况下，数据库连接终止时，MySQL 会删除所有临时表。如果您仍然想要在区间内删除临时表，那么您可以通过发出 DROP TABLE 命令来实现此目的。

By default, all the temporary tables are deleted by MySQL when your database connection gets terminated. Still if you want to delete them in between, then you can do so by issuing a DROP TABLE command.

下面是一个删除临时表的示例。

Following is an example on dropping a temporary table.

mysql> CREATE TEMPORARY TABLE SALESSUMMARY (
   -> product_name VARCHAR(50) NOT NULL
   -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
   -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
   -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SALESSUMMARY
   -> (product_name, total_sales, avg_unit_price, total_units_sold)
   -> VALUES
   -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SALESSUMMARY;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SALESSUMMARY;
mysql>  SELECT * FROM SALESSUMMARY;
ERROR 1146: Table 'TUTORIALS.SALESSUMMARY' doesn't exist

SQL - Clone Tables

有时您可能需要一个精确的数据表副本，而 CREATE TABLE … 或 SELECT… 命令无法达到您的目的，因为副本必须包含相同的索引、默认值等等。

There may be a situation when you need an exact copy of a table and the CREATE TABLE … or the SELECT… commands does not suit your purposes because the copy must include the same indexes, default values and so forth.

如果您使用的是 MySQL RDBMS，那么您可以按照下列步骤来处理这种情况：

If you are using MySQL RDBMS, you can handle this situation by adhering to the steps given below −

Use SHOW CREATE TABLE command to get a CREATE TABLE statement that specifies the source table’s structure, indexes and all.
Modify the statement to change the table name to that of the clone table and execute the statement. This way you will have an exact clone table.
Optionally, if you need the table contents copied as well, issue an INSERT INTO or a SELECT statement too.

Example

尝试使用以下示例为 TUTORIALS_TBL 创建一个克隆表，其结构如下 −

Try out the following example to create a clone table for TUTORIALS_TBL whose structure is as follows −

Step 1 − 获取表中所有结构。

Step 1 − Get the complete structure about the table.

SQL> SHOW CREATE TABLE TUTORIALS_TBL \G;
*************************** 1. row ***************************
      Table: TUTORIALS_TBL
Create Table: CREATE TABLE 'TUTORIALS_TBL' (
  'tutorial_id' int(11) NOT NULL auto_increment,
  'tutorial_title' varchar(100) NOT NULL default '',
  'tutorial_author' varchar(40) NOT NULL default '',
  'submission_date' date default NULL,
  PRIMARY KEY  ('tutorial_id'),
  UNIQUE KEY 'AUTHOR_INDEX' ('tutorial_author')
) TYPE = MyISAM
1 row in set (0.00 sec)

Step 2 − 重命名此表并创建另一个表。

Step 2 − Rename this table and create another table.

SQL> CREATE TABLE `CLONE_TBL` (
  -> 'tutorial_id' int(11) NOT NULL auto_increment,
  -> 'tutorial_title' varchar(100) NOT NULL default '',
  -> 'tutorial_author' varchar(40) NOT NULL default '',
  -> 'submission_date' date default NULL,
  -> PRIMARY KEY  (`tutorial_id'),
  -> UNIQUE KEY 'AUTHOR_INDEX' ('tutorial_author')
-> ) TYPE = MyISAM;
Query OK, 0 rows affected (1.80 sec)

Step 3 − 执行步骤 2 后，您将在数据库中克隆一个表。如果您想从一张旧表中复制数据，那么您可以通过使用 INSERT INTO… SELECT 语句来完成。

Step 3 − After executing step 2, you will clone a table in your database. If you want to copy data from an old table, then you can do it by using the INSERT INTO… SELECT statement.

SQL> INSERT INTO CLONE_TBL (tutorial_id,
   ->                        tutorial_title,
   ->                        tutorial_author,
   ->                        submission_date)
   -> SELECT tutorial_id,tutorial_title,
   ->        tutorial_author,submission_date,
   -> FROM TUTORIALS_TBL;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

最后，您将拥有与您想要的一模一样的克隆表。

Finally, you will have an exact clone table as you wanted to have.

SQL - Sub Queries

一个子查询或内部查询或一个嵌套查询是在另一个 SQL 查询中的查询，并且嵌入在 WHERE 子句中。

A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause.

一个子查询用于返回在主查询中的某个条件中将被用于进一步限制要检索的数据的数据。

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，以及 =、<、>、>=、⇐、IN、BETWEEN 等运算符一起使用。

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, ⇐, IN, BETWEEN, etc.

有一些规则，子查询必须遵守 −

There are a few rules that subqueries must follow −

Subqueries must be enclosed within parentheses.
A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery.
Subqueries that return more than one row can only be used with multiple value operators such as the IN operator.
The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
A subquery cannot be immediately enclosed in a set function.
The BETWEEN operator cannot be used with a subquery. However, the BETWEEN operator can be used within the subquery.

Subqueries with the SELECT Statement

子查询最常与SELECT语句一起使用。基本语法如下所示：

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows −

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
   (SELECT column_name [, column_name ]
   FROM table1 [, table2 ]
   [WHERE])

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

现在让我们使用一个 SELECT 语句检查以下子查询。

Now, let us check the following subquery with a SELECT statement.

SQL> SELECT *
   FROM CUSTOMERS
   WHERE ID IN (SELECT ID
         FROM CUSTOMERS
         WHERE SALARY > 4500) ;

这会产生以下结果。

This would produce the following result.

+----+----------+-----+---------+----------+
| ID | NAME     | AGE | ADDRESS | SALARY   |
+----+----------+-----+---------+----------+
|  4 | Chaitali |  25 | Mumbai  |  6500.00 |
|  5 | Hardik   |  27 | Bhopal  |  8500.00 |
|  7 | Muffy    |  24 | Indore  | 10000.00 |
+----+----------+-----+---------+----------+

Subqueries with the INSERT Statement

子查询也可以与 INSERT 语句一起使用。INSERT 语句使用从子查询返回的数据插入另一张表中。子查询中的所选数据可以使用任何字符、时间或数字函数来修改。

Subqueries also can be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date or number functions.

基本语法如下。

The basic syntax is as follows.

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

Example

考虑一张 CUSTOMERS_BKP 表，其结构与 CUSTOMERS 表相似。现在要将完整的 CUSTOMERS 表复制到 CUSTOMERS_BKP 表，您可以使用以下语法。

Consider a table CUSTOMERS_BKP with similar structure as CUSTOMERS table. Now to copy the complete CUSTOMERS table into the CUSTOMERS_BKP table, you can use the following syntax.

SQL> INSERT INTO CUSTOMERS_BKP
   SELECT * FROM CUSTOMERS
   WHERE ID IN (SELECT ID
   FROM CUSTOMERS) ;

Subqueries with the UPDATE Statement

子查询可以与 UPDATE 语句联合使用。在使用 UPDATE 语句与子查询时，可以更新表中的单个或多个列。

The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.

基本语法如下。

The basic syntax is as follows.

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Example

假设我们有一个可用的 CUSTOMERS_BKP 表，该表是 CUSTOMERS 表的备份。以下示例为 CUSTOMERS 表中 AGE 大于或等于 27 的所有客户将 SALARY 更新为 0.25 倍。

Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS table. The following example updates SALARY by 0.25 times in the CUSTOMERS table for all the customers whose AGE is greater than or equal to 27.

SQL> UPDATE CUSTOMERS
   SET SALARY = SALARY * 0.25
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
      WHERE AGE >= 27 );

这会影响两行，最终 CUSTOMERS 表将包含以下记录。

This would impact two rows and finally CUSTOMERS table would have the following records.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | Ahmedabad |   125.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  2125.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Subqueries with the DELETE Statement

与上面提到的其他任何语句一样，子查询可与 DELETE 语句一起使用。

The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.

基本语法如下。

The basic syntax is as follows.

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Example

假设我们有一个可用的 CUSTOMERS_BKP 表，该表是 CUSTOMERS 表的备份。以下示例会删除 CUSTOMERS 表中 AGE 大于或等于 27 的所有客户的记录。

Assuming, we have a CUSTOMERS_BKP table available which is a backup of the CUSTOMERS table. The following example deletes the records from the CUSTOMERS table for all the customers whose AGE is greater than or equal to 27.

SQL> DELETE FROM CUSTOMERS
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
      WHERE AGE >= 27 );

这会影响两行，最终 CUSTOMERS 表将包含以下记录。

This would impact two rows and finally the CUSTOMERS table would have the following records.

+----+----------+-----+---------+----------+
| ID | NAME     | AGE | ADDRESS | SALARY   |
+----+----------+-----+---------+----------+
|  2 | Khilan   |  25 | Delhi   |  1500.00 |
|  3 | kaushik  |  23 | Kota    |  2000.00 |
|  4 | Chaitali |  25 | Mumbai  |  6500.00 |
|  6 | Komal    |  22 | MP      |  4500.00 |
|  7 | Muffy    |  24 | Indore  | 10000.00 |
+----+----------+-----+---------+----------+

SQL - Using Sequences

序列是一组按顺序生成的整数 1、2、3、……。序列经常在数据库中使用，因为许多应用程序要求表中的每行都包含一个唯一的值，而序列提供了一种简单的方法来生成这些值。

A sequence is a set of integers 1, 2, 3, … that are generated in order on demand. Sequences are frequently used in databases because many applications require each row in a table to contain a unique value and sequences provide an easy way to generate them.

本章介绍如何在 MySQL 中使用序列。

This chapter describes how to use sequences in MySQL.

Using AUTO_INCREMENT column

在 MySQL 中使用序列的最简单方法是将列定义为 AUTO_INCREMENT，其余部分让 MySQL 处理。

The simplest way in MySQL to use sequences is to define a column as AUTO_INCREMENT and leave the rest to MySQL to take care.

Example

试用以下示例。这将创建一个表，然后在该表中插入几行，其中不需要提供记录 ID，因为 MySQL 会自动增量这个 ID。

Try out the following example. This will create a table and after that it will insert a few rows in this table where it is not required to give a record ID because its auto-incremented by MySQL.

mysql> CREATE TABLE INSECT
   -> (
   -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   -> PRIMARY KEY (id),
   -> name VARCHAR(30) NOT NULL, # type of insect
   -> date DATE NOT NULL, # date collected
   -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO INSECT (id,name,date,origin) VALUES
   -> (NULL,'housefly','2001-09-10','kitchen'),
   -> (NULL,'millipede','2001-09-10','driveway'),
   -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM INSECT ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

Obtain AUTO_INCREMENT Values

LAST_INSERT_ID( ) 是一个 SQL 函数，因此你可以从任何了解如何发出 SQL 语句的客户端中使用它。否则，PERL 和 PHP 脚本会提供专属函数来检索最后一条记录的自动增量值。

The LAST_INSERT_ID( ) is an SQL function, so you can use it from within any client that understands how to issue SQL statements. Otherwise PERL and PHP scripts provide exclusive functions to retrieve auto-incremented value of last record.

PERL Example

使用 mysql_insertid 属性获取查询生成的 AUTO_INCREMENT 值。此属性可通过数据库句柄或语句句柄进行访问，具体取决于你如何发出查询。以下示例通过数据库句柄对此进行引用。

Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value generated by a query. This attribute is accessed through either a database handle or a statement handle, depending on how you issue the query. The following example references it through the database handle.

$dbh->do ("INSERT INTO INSECT (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

PHP Example

在发出生成 AUTO_INCREMENT 值的查询后，通过调用 mysql_insert_id( ) 函数检索该值。

After issuing a query that generates an AUTO_INCREMENT value, retrieve the value by calling the mysql_insert_id( ) function.

mysql_query ("INSERT INTO INSECT (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

Renumbering an Existing Sequence

在某些情况下，你可能从表中删除了许多记录，并且希望对所有记录重新排序。这可以使用一个简单的技巧来实现，但你应该非常小心地执行此操作，并检查你的表是否与另一个表有联接。

There may be a case when you have deleted many records from a table and you want to re-sequence all the records. This can be done by using a simple trick, but you should be very careful to do this and check if your table is having a join with another table or not.

如果你确定对 AUTO_INCREMENT 列重新排序是不可避免的，则执行此操作的方法是从表中删除该列，然后重新添加。

If you determine that resequencing an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the column from the table, then add it again.

以下示例演示如何使用此技术对 insect 表中的 id 值重新编号。

The following example shows how to renumber the id values in the insect table using this technique.

mysql> ALTER TABLE INSECT DROP id;
mysql> ALTER TABLE insect
   -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
   -> ADD PRIMARY KEY (id);

Starting a Sequence at a Particular Value

默认情况下，MySQL 将从 1 开始序列，但你也可以在创建表时指定任何其他数字。

By default, MySQL will start the sequence from 1, but you can specify any other number as well at the time of table creation.

以下代码块有一个示例，其中 MySQL 将从 100 开始序列。

The following code block has an example where MySQL will start sequence from 100.

mysql> CREATE TABLE INSECT
   -> (
   -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
   -> PRIMARY KEY (id),
   -> name VARCHAR(30) NOT NULL, # type of insect
   -> date DATE NOT NULL, # date collected
   -> origin VARCHAR(30) NOT NULL # where collected
);

或者，你可以创建表，然后使用 ALTER TABLE 设置初始序列值。

Alternatively, you can create the table and then set the initial sequence value with ALTER TABLE.

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

SQL - Handling Duplicates

在表中，你可能会遇到重复记录的情况。在获取这些记录时，只获取唯一记录比获取重复记录更有意义。

There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records.

我们已经讨论过的 SQL DISTINCT 关键字，它与 SELECT 语句结合使用，可以消除所有重复记录，只获取唯一记录。

The SQL DISTINCT keyword, which we have already discussed is used in conjunction with the SELECT statement to eliminate all the duplicate records and by fetching only the unique records.

Syntax

消除重复记录的 DISTINCT 关键字的基本语法如下。

The basic syntax of a DISTINCT keyword to eliminate duplicate records is as follows.

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

Example

可以将 CUSTOMERS 表视为包含以下记录。

Consider the CUSTOMERS table having the following records.

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

首先，让我们看看下面的 SELECT 查询如何返回重复的薪酬记录。

First, let us see how the following SELECT query returns duplicate salary records.

SQL> SELECT SALARY FROM CUSTOMERS
   ORDER BY SALARY;

这将产生以下结果，其中 2000 元的薪酬出现两次，这是从原始表中重复的记录。

This would produce the following result where the salary of 2000 is coming twice which is a duplicate record from the original table.

+----------+
| SALARY   |
+----------+
|  1500.00 |
|  2000.00 |
|  2000.00 |
|  4500.00 |
|  6500.00 |
|  8500.00 |
| 10000.00 |
+----------+

现在，让我们在上面的 SELECT 查询中使用 DISTINCT 关键字，看看结果。

Now, let us use the DISTINCT keyword with the above SELECT query and see the result.

SQL> SELECT DISTINCT SALARY FROM CUSTOMERS
   ORDER BY SALARY;

这将产生以下结果，其中我们没有任何重复条目。

This would produce the following result where we do not have any duplicate entry.

+----------+
| SALARY   |
+----------+
|  1500.00 |
|  2000.00 |
|  4500.00 |
|  6500.00 |
|  8500.00 |
| 10000.00 |
+----------+

SQL - Injection

如果你通过网页获取用户输入并将其插入 SQL 数据库，那么你很可能会让自己处于一种称为 SQL Injection 的安全问题之中。本章将教你如何帮助防止这种情况的发生以及帮助你在服务器端脚本（例如 PERL 脚本）中保护脚本和 SQL 语句。

If you take a user input through a webpage and insert it into a SQL database, there is a chance that you have left yourself wide open for a security issue known as the SQL Injection. This chapter will teach you how to help prevent this from happening and help you secure your scripts and SQL statements in your server side scripts such as a PERL Script.

通常当您要求用户输入姓名时，会出现注入，而用户不提供姓名，而是提供将在数据库中不知不觉运行的 SQL 语句。切勿信任用户提供的数据，只有在验证后才处理此数据；根据规则，这可以通过 Pattern Matching 完成。

Injection usually occurs when you ask a user for input, like their name and instead of a name they give you a SQL statement that you will unknowingly run on your database. Never trust user provided data, process this data only after validation; as a rule, this is done by Pattern Matching.

在下例中， name 仅限于字母数字字符加上下划线，并且长度在 8 到 20 个字符之间（根据需要修改这些规则）。

In the example below, the name is restricted to the alphanumerical characters plus underscore and to a length between 8 and 20 characters (modify these rules as needed).

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)) {
   $result = mysql_query("SELECT * FROM CUSTOMERS
      WHERE name = $matches[0]");
} else {
   echo "user name not accepted";
}

为了说明问题，请考虑以下摘录 −

To demonstrate the problem, consider this excerpt −

// supposed input
$name = "Qadir'; DELETE FROM CUSTOMERS;";
mysql_query("SELECT * FROM CUSTOMSRS WHERE name='{$name}'");

该函数调用应该从 CUSTOMERS 表中检索一条记录，其中 name 列与用户指定的名称匹配。在正常情况下， $name 仅包含字母数字字符，或许还有空格，例如字符串 ilia。但是在这里，通过将一个全新的查询附加到 $name，对数据库的调用变成了灾难；注入的 DELETE 查询从 CUSTOMERS 表中删除了所有记录。

The function call is supposed to retrieve a record from the CUSTOMERS table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces, such as the string ilia. But here, by appending an entirely new query to $name, the call to the database turns into disaster; the injected DELETE query removes all records from the CUSTOMERS table.

幸运的是，如果你使用 MySQL， mysql_query() 函数不允许在单个函数调用中查询堆栈或执行多个 SQL 查询。如果你尝试堆叠查询，调用将失败。

Fortunately, if you use MySQL, the mysql_query() function does not permit query stacking or executing multiple SQL queries in a single function call. If you try to stack queries, the call fails.

但是，其他 PHP 数据库扩展，例如 SQLite 和 PostgreSQL 会愉快地执行堆积的查询，执行在一个字符串中提供的查询并创建严重的安全问题。

However, other PHP database extensions, such as SQLite and PostgreSQL happily perform stacked queries, executing all the queries provided in one string and creating a serious security problem.

Preventing SQL Injection

您可以在 PERL 和 PHP 等脚本语言中巧妙地处理所有转义字符。PHP 的 MySQL 扩展提供了 mysql_real_escape_string() 函数来转义对 MySQL 来说特殊的输入字符。

You can handle all escape characters smartly in scripting languages like PERL and PHP. The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL.

if (get_magic_quotes_gpc()) {
   $name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM CUSTOMERS WHERE name='{$name}'");

The LIKE Quandary

为了解决 LIKE 难题，自定义转义机制必须将用户提供的 '%' 和 '_' 字符转换为文本。使用 addcslashes() ，此函数允许您指定要转义的字符范围。

To address the LIKE quandary, a custom escaping mechanism must convert user-supplied '%' and '_' characters to literals. Use addcslashes(), a function that lets you specify a character range to escape.

$sub = addcslashes(mysql_real_escape_string("%str"), "%_");
// $sub == \%str\_
mysql_query("SELECT * FROM messages
   WHERE subject LIKE '{$sub}%'");

SQL - Database Tunning

成为一名数据库专家或专家数据库管理员需要时间。所有这些都伴随着各种数据库设计和良好的培训的丰富经验。

It takes time to become a Database Expert or an expert Database Administrator. This all comes with lot of experience in various database designs and good trainings.

但以下列表可能有助于初学者以出色的方式应用数据库 -

But the following list may be helpful for the beginners to have a nice database performance −

Use 3BNF database design explained in this tutorial in RDBMS Concepts chapter.
Avoid number-to-character conversions because numbers and characters compare differently and lead to performance downgrade.
While using SELECT statement, only fetch whatever information is required and avoid using * in your SELECT queries because it would load the system unnecessarily.
Create your indexes carefully on all the tables where you have frequent search operations. Avoid index on the tables where you have less number of search operations and more number of insert and update operations.
A full-table scan occurs when the columns in the WHERE clause do not have an index associated with them. You can avoid a full-table scan by creating an index on columns that are used as conditions in the WHERE clause of an SQL statement.
Be very careful of equality operators with real numbers and date/time values. Both of these can have small differences that are not obvious to the eye but that make an exact match impossible, thus preventing your queries from ever returning rows.
Use pattern matching judiciously. LIKE COL% is a valid WHERE condition, reducing the returned set to only those records with data starting with the string COL. However, COL%Y does not further reduce the returned results set since %Y cannot be effectively evaluated. The effort to do the evaluation is too large to be considered. In this case, the COL% is used, but the %Y is thrown away. For the same reason, a leading wildcard %COL effectively prevents the entire filter from being used.
Fine tune your SQL queries examining the structure of the queries (and subqueries), the SQL syntax, to discover whether you have designed your tables to support fast data manipulation and written the query in an optimum manner, allowing your DBMS to manipulate the data efficiently.
For queries that are executed on a regular basis, try to use procedures. A procedure is a potentially large group of SQL statements. Procedures are compiled by the database engine and then executed. Unlike an SQL statement, the database engine need not optimize the procedure before it is executed.
Avoid using the logical operator OR in a query if possible. OR inevitably slows down nearly any query against a table of substantial size.
You can optimize bulk data loads by dropping indexes. Imagine the history table with many thousands of rows. That history table is also likely to have one or more indexes. When you think of an index, you normally think of faster table access, but in the case of batch loads, you can benefit by dropping the index(es).
When performing batch transactions, perform COMMIT at after a fair number of records creation in stead of creating them after every record creation.
Plan to defragment the database on a regular basis, even if doing so means developing a weekly routine.

Built-In Tuning Tools

Oracle 有许多用于管理 SQL 语句性能的工具，但其中两个非常流行。这两个工具是：

Oracle has many tools for managing SQL statement performance but among them two are very popular. These two tools are −

Explain plan − tool identifies the access path that will be taken when the SQL statement is executed.
tkprof − measures the performance by time elapsed during each phase of SQL statement processing.

如果只想衡量 Oracle 中查询的耗时，可以使用 SQL*Plus 命令 SET TIMING ON。

If you want to simply measure the elapsed time of a query in Oracle, you can use the SQL*Plus command SET TIMING ON.

查看 RDBMS 文档，了解上述工具和数据库碎片整理的更多详细信息。

Check your RDBMS documentation for more detail on the above-mentioned tools and defragmenting the database.