SQL statements are self-increasing(国外英文资料).doc

SQL statements are self-increasing(国外英文资料) SQL AUTO INCREMENT field The Previous PageNext pageauto-increment generates a unique number when the new record is inserted into the table. AUTO INCREMENT field We usually want to automatically create the primary key field value every time we insert a new record. We can create an auto-increment field in the table. Syntax for MySQL The following SQL statement defines the P_Id column in the Persons table as the auto-increment primary key: The CREATE TABLE Persons ( P_Id int NOT NULL AUTO_INCREMENT, LastName varchar (255) NOT NULL, FirstName varchar (255). Address varchar (255), City varchar (255), PRIMARY KEY (P_Id) ) MySQL USES the AUTO_INCREMENT keyword to perform the auto-increment task. By default, the start value of AUTO_INCREMENT is 1, and each new record increments 1. To start the AUTO_INCREMENT sequence with other values, use the following SQL syntax: ALTER TABLE Persons AUTO_INCREMENT = 100 to insert a new record in the Persons TABLE, we dont have to add a single value to the P_Id column (which automatically adds a unique value) : INSERT INTO Persons (FirstName, LastName) VALUES ( Bill , Gates) The SQL statement above will insert a new record in the Persons table. P_Id will be given a unique value. FirstName will be set to Bill and LastName column will be set to Gates. Syntax for SQL Server The following SQL statement defines the P_Id column in the Persons table as the auto-increment primary key: The CREATE TABLE Persons ( P_Id int PRIMARY KEY IDENTITY, LastName varchar (255) NOT NULL, FirstName varchar (255). Address varchar (255), City varchar (255). ) MS SQL USES the IDENTITY keyword to perform the auto-increment task. By default, the start value of IDENTITY is 1, and each new record increments 1. To specify P_Id column in 20 and increment 10, change identity to identity (20, 10) To insert a new record in the Persons table, we do not need to set the P_Id column value (which automatically adds a unique value) : IN