Set AUTO_INCREMENT in a table while creating it in MySQL?

Let us first create a table. We have used AUTO_INCREMENT while creating the table to set auto increment for StudentId −

mysql> create table DemoTable
   -> (
   -> StudentId int NOT NULL AUTO_INCREMENT,
   -> StudentFirstName varchar(100),
   -> StudentLastName varchar(100),
   -> StudentAge int,
   -> StudentCountryName varchar(100),
   -> PRIMARY KEY(StudentId)
   -> )AUTO_INCREMENT=30;
Query OK, 0 rows affected (0.69 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(StudentFirstName,StudentLastName,StudentAge,StudentCountryName) values('John','Smith',21,'US');
Query OK, 1 row affected (0.17 sec)

mysql> insert into DemoTable(StudentFirstName,StudentLastName,StudentAge,StudentCountryName) values('Chris','Brown',20,'AUS');
Query OK, 1 row affected (0.19 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

Output

+-----------+------------------+-----------------+------------+--------------------+
| StudentId | StudentFirstName | StudentLastName | StudentAge | StudentCountryName |
+-----------+------------------+-----------------+------------+--------------------+
| 30        | John             | Smith           | 21         | US                 |
| 31        | Chris            | Brown           | 20         | AUS                |
+-----------+------------------+-----------------+------------+--------------------+
2 rows in set (0.00 sec)

Kumar Varma

Published on 09-Jul-2019 12:26:52

• Related Questions & Answers
• Set DEFAULT values for columns while creating a table in MySQL
• Can we use {} while creating a MySQL table?
• Set options while creating a MySQL table. Display the same options as well
• What is MySQL GENERATED COLUMN and how to use it while creating a table?
• Set a custom value for AUTO_INCREMENT while creating a table and use ZEROFILL. What will happen now when nothing is inserted while using INSERT statement?
• Error occurs when table name “references” is set while creating a table
• Insertion in a MySQL table with only a single column set as auto_increment?
• Set special characters for password while creating a new MySQL user?
• Creating a Table in MySQL to set current date as default
• While creating a MySQL table use the reserved keyword ‘Key’
• Creating a table with MySQL - Hibernate
• Creating a MySQL table using Node.js
• Fix Error in MySQL syntax while creating a table column with name “index”?
• View the auto_increment value for a table in MySQL without using SHOW TABLE?
• MySQL INSERT INTO SELECT into a table with AUTO_INCREMENT