The AUTO_INCREMENT attribute can be used to generate a unique identity or ID for newly inserted rows.
CREATE TABLE employee (
emp_id MEDIUMINT NOT NULL AUTO_INCREMENT,
emp_name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO employee (emp_name) VALUES
('john'),('jolly'),('raj'),
('wlodeck'),('james'),('tyler');
SELECT * FROM employee;
+--------+------------+
| emp_id | emp_name |
+--------+------------+
| 1 | john |
| 2 | jolly |
| 3 | raj |
| 4 | wlodeck |
| 5 | james |
| 6 | tyler |
+--------+------------+
mysql> use my_db;
Database changed
mysql> create table stud ( sid int, sname varchar(20), age int,
-> email varchar(40)
-> ) ;
Query OK, 0 rows affected (0.11 sec)
mysql> desc stud;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid | int(11) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| email | varchar(40) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table stud
-> modify sid char(4);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stud;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid | char(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| email | varchar(40) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table stud
-> add mobile bigint;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stud;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid | char(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| email | varchar(40) | YES | | NULL | |
| mobile | bigint(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table stud add saddress varchar(40) after sname;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stud;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sid | char(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| saddress | varchar(40) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| email | varchar(40) | YES | | NULL | |
| mobile | bigint(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table stud
-> drop column mobile;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stud;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sid | char(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| saddress | varchar(40) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| email | varchar(40) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table stud
-> change email emailID varchar(30);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stud;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sid | char(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| saddress | varchar(40) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| emailID | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select * from stud;
Empty set (0.00 sec)
mysql> insert into stud values('s001','tom','Blr',30,'aa@gmail.com');
Query OK, 1 row affected (0.00 sec)
mysql> select * from stud;
+------+-------+----------+------+--------------+
| sid | sname | saddress | age | emailID |
+------+-------+----------+------+--------------+
| s001 | tom | Blr | 30 | aa@gmail.com |
+------+-------+----------+------+--------------+
1 row in set (0.00 sec)
mysql> mysql> alter table stud
-> add dob date;
Query OK, 1 row affected (0.09 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from stud;
+------+-------+----------+------+--------------+------+
| sid | sname | saddress | age | emailID | dob |
+------+-------+----------+------+--------------+------+
| s001 | tom | Blr | 30 | aa@gmail.com | NULL |
+------+-------+----------+------+--------------+------+
1 row in set (0.00 sec)
mysql> insert into stud values('s002','jerry','Blr',30,'aa@gmail.com',
'1998/12/20');
Query OK, 1 row affected (0.00 sec)
mysql> select * from stud;
+------+-------+----------+------+--------------+------------+
| sid | sname | saddress | age | emailID | dob |
+------+-------+----------+------+--------------+------------+
| s001 | tom | Blr | 30 | aa@gmail.com | NULL |
| s002 | jerry | Blr | 30 | aa@gmail.com | 1998-12-20 |
+------+-------+----------+------+--------------+------------+
2 rows in set (0.00 sec)
mysql> insert into stud values('s003','jerry','Blr',30,'aa@gmail.com',
'1998/19/20');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings; -- this will help you to see warnings in details
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'dob' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show errors; -- this will help you to see errors in details
Empty set (0.00 sec)
mysql> insert into stud(sid,sname,age) values('s005','tom',30);
Query OK, 1 row affected (0.00 sec)
mysql> insert into stud(sid,sname,age) values('s005','tom',30);
Query OK, 1 row affected (0.00 sec)
mysql> insert into stud(sid,sname,age) values('s005','tom',30);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stud;
+------+-------+----------+------+---------+------+
| sid | sname | saddress | age | emailID | dob |
+------+-------+----------+------+---------+------+
| s005 | tom | NULL | 30 | NULL | NULL |
| s005 | tom | NULL | 30 | NULL | NULL |
| s005 | tom | NULL | 30 | NULL | NULL |
+------+-------+----------+------+---------+------+
3 rows in set (0.00 sec)
mysql> truncate table stud;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table stud
-> modify sid int primary key auto_increment;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stud;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | YES | | NULL | |
| saddress | varchar(40) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| emailID | varchar(30) | YES | | NULL | |
| dob | date | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> insert into stud(sname,age) values('tom',30);
Query OK, 1 row affected (0.00 sec)
mysql> insert into stud(sname,age) values('tom',30);
Query OK, 1 row affected (0.00 sec)
mysql> insert into stud(sname,age) values('tom',30);
Query OK, 1 row affected (0.00 sec)
mysql> insert into stud(sname,age) values('jery',50);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stud;
+-----+-------+----------+------+---------+------+
| sid | sname | saddress | age | emailID | dob |
+-----+-------+----------+------+---------+------+
| 1 | tom | NULL | 30 | NULL | NULL |
| 2 | tom | NULL | 30 | NULL | NULL |
| 3 | tom | NULL | 30 | NULL | NULL |
| 4 | jery | NULL | 50 | NULL | NULL |
+-----+-------+----------+------+---------+------+
4 rows in set (0.00 sec)
mysql> delete from stud; -- this will not reset the auto_increment
Query OK, 4 rows affected (0.00 sec)
mysql> select * from stud;
Empty set (0.00 sec)
mysql> insert into stud(sname,age) values('jery',50);
Query OK, 1 row affected (0.00 sec)
mysql> insert into stud(sname,age) values('jery',50);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stud; -- the auto_increment started from 5
+-----+-------+----------+------+---------+------+
| sid | sname | saddress | age | emailID | dob |
+-----+-------+----------+------+---------+------+
| 5 | jery | NULL | 50 | NULL | NULL |
| 6 | jery | NULL | 50 | NULL | NULL |
+-----+-------+----------+------+---------+------+
2 rows in set (0.00 sec)
mysql> truncate table stud; -- this will reset the auto_increment
Query OK, 0 rows affected (0.00 sec)
mysql> select * from stud;
Empty set (0.00 sec)
mysql> insert into stud(sname,age) values('jery',50);
Query OK, 1 row affected (0.00 sec)
mysql> insert into stud(sname,age) values('jery',50);
Query OK, 1 row affected (0.00 sec)
mysql> insert into stud(sname,age) values('jery',50);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stud; -- the auto_increment started from 1
+-----+-------+----------+------+---------+------+
| sid | sname | saddress | age | emailID | dob |
+-----+-------+----------+------+---------+------+
| 1 | jery | NULL | 50 | NULL | NULL |
| 2 | jery | NULL | 50 | NULL | NULL |
| 3 | jery | NULL | 50 | NULL | NULL |
+-----+-------+----------+------+---------+------+
3 rows in set (0.00 sec)
Recent Comments