MySQL Tutorial
Auto Increment is a field used to generate a unique number for every new record added into a table.
MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.
CREATE TABLE table_name
(
column1 data_type(size) NOT NULL AUTO_INCREMENT,
column2 data_type(size) ,
column3 data_type(size) ,
....
); To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:
ALTER TABLE table_name AUTO_INCREMENT='Increment By'; The following SQL ensures that the "ROLL_NO" columns to be an AUTO INCREMENT value when the "Students" table is created:
CREATE TABLE Students
(
ROLL_NO int(3) NOT NULL AUTO_INCREMENT,
NAME varchar(20) NOT NULL ,
SUBJECT varchar(20) ,
); The above example creates an empty table Students with three columns name 'ROLL_NO, NAME, SUBJECT .
The following MySQL statement inserts a new record in the "students" table.
INSERT INTO STUDENTS (NAME, SUBJECT)
VALUES (’Rim’,’Java’ );
INSERT INTO STUDENTS (NAME, SUBJECT)
VALUES ('Micheal’,’SQL’);
INSERT INTO STUDENTS (NAME, SUBJECT)
VALUES (’Lara’,’SQL’);
Value of ROLL_NO will increment by '1' startinf from '1'.
The table students will now look like:
| ROLL_NO | NAME | SUBJECT | 1 | Rim | Java |
|---|---|---|
| 2 | Micheal | SQL |
| 3 | Lara | SQL |