Sql 简明教程

SQL - Using Sequences

SQL 中的 Sequences 是生成唯一整数值序列的数据库对象。它们经常在数据库中使用，因为很多应用程序要求表中的每一行都包含唯一值，而序列提供了一种简单的方法来生成它们。

Sequences in SQL are database objects that generate a sequence of unique integer values. They are frequently used in databases because many applications require that each row in a table must contain unique values and sequences provide an easy way to generate them.

序列是很多 SQL 数据库管理系统（例如 Oracle、PostgreSQL、SQL Server 和 IBM DB2）的一项功能。

Sequences are a feature of many SQL database management systems, such as Oracle, PostgreSQL, SQL server, and IBM DB2.

Sequences in MySQL

在 MySQL 中，我们可以使用 AUTO_INCREMENT 属性为列生成唯一整数值（序列）。默认情况下，序列从初始值 1 开始，并为每一行递增 1。

In MySQL, we use the AUTO_INCREMENT attribute to generate unique integer values (sequences) for a column. By default, the sequence starts with an initial value of 1 and increments by 1 for each new row.

Syntax

以下是 MySQL 中 AUTO_INCREMENT 属性的语法：

Following is the syntax of AUTO_INCREMENT attribute in MySQL −

CREATE TABLE table_name (
	column1 datatype AUTO_INCREMENT,
	column2 datatype,
	column3 datatype,
	...
	columnN datatype
);

Example

在以下示例中，我们创建了一个名为 CUSTOMERS 的表。此外，我们在表的 ID 列上定义 AUTO_INCREMENT。

In the following example, we are creating a table named CUSTOMERS. In addition to that, we are defining AUTO_INCREMENT on ID column of the table.

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

在此，我们向上述创建的表添加一些记录：

Here, we are adding some records into the above created table −

INSERT INTO CUSTOMERS VALUES
(NULL, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(NULL, 'Khilan', 25, 'Delhi', 1500.00),
(NULL, 'Kaushik', 23, 'Kota', 2000.00),
(NULL, 'Chaitali', 25, 'Mumbai', 6500.00),
(NULL, 'Hardik', 27, 'Bhopal', 8500.00),
(NULL, 'Komal', 22, 'Hyderabad', 4500.00),
(NULL, 'Muffy', 24, 'Indore', 10000.00);

该表将按如下方式创建：

The table will be created 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	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

正如我们在上述表中看到的，ID 列中的值是自动递增的。

As we can see in the above table, the values in the ID column are auto incremented.

Starting a Sequence at a Particular Value in MySQL

默认情况下，MySQL 序列从 1 开始。若要从一个不同的值开始序列，我们需要将 AUTO_INCREMENT 与 ALTER 语句结合使用。

By default, MySQL sequences start from 1. To start a sequence with a different value, we use the AUTO_INCREMENT in combination with the ALTER statement.

Syntax

以下是使用不同的值开始序列的语法：

Following is the syntax to start the sequence with different value −

ALTER TABLE table_name AUTO_INCREMENT = value;

在以下查询中，我们在 ID 列上定义 AUTO_INCREMENT 来创建名为 BUYERS 的表。

In the following query, we are creating a table named BUYERS with AUTO_INCREMENT defined on the ID column.

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

在此，我们在使用 ALTER 语句使序列从 100 开始，如下所示：

Here, we are making the sequence start with 100 using the ALTER Statement as shown below −

ALTER TABLE BUYERS AUTO_INCREMENT=100;

现在，我们使用 INSERT INTO 语句在 BUYERS 表中添加记录：

Now, we are adding records into the BUYERS table using the INSERT INTO statement −

INSERT INTO BUYERS VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

该表将被创建为：

The table will be created as −

ID	NAME	AGE	ADDRESS	SALARY
100	Ramesh	32	Ahmedabad	2000.00
101	Khilan	25	Delhi	1500.00
102	Kaushik	23	Kota	2000.00
103	Chaitali	25	Mumbai	6500.00
104	Hardik	27	Bhopal	8500.00
105	Komal	22	Hyderabad	4500.00
106	Muffy	24	Indore	10000.00

正如在上述表中观察到的，"ID" 列中的值从 100 开始，而不是 1。

As observed in the table above, the values in the "ID" column begin with 100 instead of 1.

Sequences in SQL Server

在 SQL Server 中，可以使用 CREATE SEQUENCE 语句创建序列。此语句指定序列的名称、开始值、递增值以及序列的其他属性。

In SQL server, a sequence can be created using the CREATE SEQUENCE statement. The statement specifies the name of the sequence, the starting value, the increment, and other properties of the sequence.

Syntax

以下是创建 SQL 序列的语法：

Following is the syntax to create a sequence in SQL −

CREATE SEQUENCE Sequence_Name
START WITH Initial_Value
INCREMENT BY Increment_Value
MINVALUE Minimum_Value
MAXVALUE Maximum_Value
CYCLE|NOCYCLE;

在此，

Here,

Sequence_Name − This specifies the name of the sequence.
Initial_Value − This specifies the starting value from where the sequence should start.
Increment_Value − This specifies the value by which the sequence will increment by itself. This can be valued positively or negatively.
Minimum_Value − This specifies the minimum value of the sequence.
Maximum_Value − This specifies the maximum value of the sequence.
Cycle − When the sequence reaches its Maximum_Value, it starts again from the beginning.
Nocycle − An exception will be thrown if the sequence exceeds the Maximum_Value.

Example

首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表——

First of all, let us create a table named CUSTOMERS using the following query −

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

我们在以上创建的表中使用 INSERT INTO 语句插入一些记录，如下面查询所示——

We are inserting some records in the above-created table using INSERT INTO statement as shown in the query below −

INSERT INTO CUSTOMERS VALUES
(NULL, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(NULL, 'Khilan', 25, 'Delhi', 1500.00),
(NULL, 'Kaushik', 23, 'Kota', 2000.00),
(NULL, 'Chaitali', 25, 'Mumbai', 6500.00),
(NULL, 'Hardik', 27, 'Bhopal', 8500.00),
(NULL, 'Komal', 22, 'Hyderabad', 4500.00),
(NULL, 'Muffy', 24, 'Indore', 10000.00 );

该表在 SQL 数据库中成功创建。

The table is successfully created in the SQL database.

NAME

AGE

ADDRESS

SALARY

NULL

Ramesh

Ahmedabad

2000.00

NULL

Khilan

Delhi

1500.00

NULL

Kaushik

Kota

2000.00

NULL

Chaitali

Mumbai

6500.00

NULL

Hardik

Bhopal

8500.00

NULL

Komal

Hyderabad

4500.00

NULL

Muffy

Indore

10000.00

现在，使用以下查询创建一个序列——

Now, create a sequence using the following query −

CREATE SEQUENCE My_Sequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 7
CYCLE;

在以上查询中，序列名为 My_Sequence ，它从值 1 开始，每生成一个值递增 1。该序列的最大值为 5，当它达到最大值时循环回到起始值。

In the above query, the sequence is named My_Sequence and it starts with the value 1 and increments by 1 each time a value is generated. The sequence has a maximum value of 5 and cycles back to the starting value when it reaches the maximum value.

创建序列后，可用其生成唯一的整数值。现在，让我们使用以下查询更新 CUSTOMERS 表中的 ID 列数据——

Once the sequence is created, it can be used to generate unique integer values. Now, let us update the data in the ID column of the CUSTOMERS table using the following query −

UPDATE CUSTOMERS SET ID = NEXT VALUE FOR my_Sequence;

Output

执行以上查询后，将获取如下输出——

When you execute the above query, the output is obtained as follows −

(7 rows affected)

Verification

让我们使用以下查询验证该序列是否更新了表中的 ID 列——

Let us verify whether is sequence is updated in the ID column of the table or not using the following query −

SELECT * FROM CUSTOMERS;

将显示以下形式的表 −

The table will be displayed as −

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