Modify column definitions a)Data Type b)Size c)Default Value d) NOT NULL column constraint e) Order of column

mysql4.txt

a)Alter table- Add column

mysql> select *from student3;
+--------+----------+-------+-------+
| rollno | Name     | CLASS | MARKS |
+--------+----------+-------+-------+
|    101 | student1 |    12 |    90 |
|    102 | student2 |    12 |    89 |
|    103 | student3 |    12 |    88 |
|    104 | student4 |    12 |    87 |
+--------+----------+-------+-------+
4 rows in set (0.00 sec)

mysql> alter table student3
    -> add mobile int;
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select *from student3;
+--------+----------+-------+-------+--------+
| rollno | Name     | CLASS | MARKS | mobile |
+--------+----------+-------+-------+--------+
|    101 | student1 |    12 |    90 |   NULL |
|    102 | student2 |    12 |    89 |   NULL |
|    103 | student3 |    12 |    88 |   NULL |
|    104 | student4 |    12 |    87 |   NULL |
+--------+----------+-------+-------+--------+
4 rows in set (0.03 sec)

b) Alter table-Add column in the selected place

mysql> alter table student3
    -> add Board char(10) after class;
Query OK, 0 rows affected (1.92 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select *from student3;
+--------+----------+-------+-------+-------+--------+
| rollno | Name     | CLASS | Board | MARKS | mobile |
+--------+----------+-------+-------+-------+--------+
|    101 | student1 |    12 | NULL  |    90 |   NULL |
|    102 | student2 |    12 | NULL  |    89 |   NULL |
|    103 | student3 |    12 | NULL  |    88 |   NULL |
|    104 | student4 |    12 | NULL  |    87 |   NULL |
+--------+----------+-------+-------+-------+--------+
4 rows in set (0.00 sec)

c)Alter table-Add an integrity constraint(NOT NULL,UNIQUE,PRIMARY KEY,REferences,DEFAULT and CHECK)

mysql> alter table student3
    -> add loc char(10) not null;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select *from student3;
+--------+----------+-------+-------+-------+--------+-----+
| rollno | Name     | CLASS | Board | MARKS | mobile | loc |
+--------+----------+-------+-------+-------+--------+-----+
|    101 | student1 |    12 | NULL  |    90 |   NULL |     |
|    102 | student2 |    12 | NULL  |    89 |   NULL |     |
|    103 | student3 |    12 | NULL  |    88 |   NULL |     |
|    104 | student4 |    12 | NULL  |    87 |   NULL |     |
+--------+----------+-------+-------+-------+--------+-----+
4 rows in set (0.03 sec)

mysql> alter table student3
    -> add School char(10) default "GVKCV" after board;
Query OK, 0 rows affected (1.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select *from student3;
+--------+----------+-------+-------+--------+-------+--------+-----+
| rollno | Name     | CLASS | Board | School | MARKS | mobile | loc |
+--------+----------+-------+-------+--------+-------+--------+-----+
|    101 | student1 |    12 | NULL  | GVKCV  |    90 |   NULL |     |
|    102 | student2 |    12 | NULL  | GVKCV  |    89 |   NULL |     |
|    103 | student3 |    12 | NULL  | GVKCV  |    88 |   NULL |     |
|    104 | student4 |    12 | NULL  | GVKCV  |    87 |   NULL |     |
+--------+----------+-------+-------+--------+-------+--------+-----+
4 rows in set (0.00 sec)


Modify column definitions
a)Data Type b)Size c)Default Value d) NOT NULL column constraint e) Order of column

a)Data Type and size
Change char to varchar and change size 10 to 15

mysql> desc student3;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| rollno | int      | NO   |     | NULL    |       |
| NAME   | char(10) | NO   |     | NULL    |       |
| CLASS  | int      | YES  |     | NULL    |       |
| MARKS  | int      | YES  |     | NULL    |       |
| mobile | int      | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
5 rows in set (0.77 sec)

mysql> alter table student3
    -> modify Name varchar(15);
Query OK, 4 rows affected (3.19 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc student3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| rollno | int         | NO   |     | NULL    |       |
| Name   | varchar(15) | YES  |     | NULL    |       |
| CLASS  | int         | YES  |     | NULL    |       |
| MARKS  | int         | YES  |     | NULL    |       |
| mobile | int         | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

e)Alter table-Modify order of column

mysql> alter table student3
    -> modify mobile int after loc;
Query OK, 0 rows affected (0.75 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select *from student3;
+--------+----------+-------+-------+--------+-------+-----+--------+
| rollno | Name     | CLASS | Board | School | MARKS | loc | mobile |
+--------+----------+-------+-------+--------+-------+-----+--------+
|    101 | student1 |    12 | NULL  | GVKCV  |    90 |     |   NULL |
|    102 | student2 |    12 | NULL  | GVKCV  |    89 |     |   NULL |
|    103 | student3 |    12 | NULL  | GVKCV  |    88 |     |   NULL |
|    104 | student4 |    12 | NULL  | GVKCV  |    87 |     |   NULL |
+--------+----------+-------+-------+--------+-------+-----+--------+
4 rows in set (0.00 sec)

f)Alter table-Modify default value

mysql> desc student3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| rollno | int         | NO   |     | NULL    |       |
| Name   | varchar(15) | YES  |     | NULL    |       |
| CLASS  | int         | YES  |     | NULL    |       |
| board  | char(10)    | YES  |     | NULL    |       |
| School | char(10)    | YES  |     | GVKCV   |       |
| MARKS  | int         | YES  |     | NULL    |       |
| loc    | char(10)    | YES  |     | Nellore |       |
| mobile | int         | YES  |     | 1       |       |
+--------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> alter table student3
    -> modify board char(10) default "CBSE";
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| rollno | int         | NO   |     | NULL    |       |
| Name   | varchar(15) | YES  |     | NULL    |       |
| CLASS  | int         | YES  |     | NULL    |       |
| board  | char(10)    | YES  |     | CBSE    |       |
| School | char(10)    | YES  |     | GVKCV   |       |
| MARKS  | int         | YES  |     | NULL    |       |
| loc    | char(10)    | YES  |     | Nellore |       |
| mobile | int         | YES  |     | 1       |       |
+--------+-------------+------+-----+---------+-------+
8 rows in set (0.04 sec)

g)Alter table-Modify NOT NULL column constraint

mysql> alter table student3
    -> modify name varchar(15) not null;
Query OK, 0 rows affected (1.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| rollno | int         | NO   |     | NULL    |       |
| name   | varchar(15) | NO   |     | NULL    |       |
| CLASS  | int         | YES  |     | NULL    |       |
| board  | char(10)    | YES  |     | CBSE    |       |
| School | char(10)    | YES  |     | GVKCV   |       |
| MARKS  | int         | YES  |     | NULL    |       |
| loc    | char(10)    | YES  |     | Nellore |       |
| mobile | int         | YES  |     | 1       |       |
+--------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

h)Alter table-Change Column name

mysql> select *from student3;
+--------+----------+-------+-------+--------+-------+------+--------+
| rollno | name     | CLASS | board | School | MARKS | loc  | mobile |
+--------+----------+-------+-------+--------+-------+------+--------+
|    101 | student1 |    12 | NULL  | GVKCV  |    90 |      |   NULL |
|    102 | student2 |    12 | NULL  | GVKCV  |    89 |      |   NULL |
|    103 | student3 |    12 | NULL  | GVKCV  |    88 |      |   NULL |
|    104 | student4 |    12 | NULL  | GVKCV  |    87 |      |   NULL |
+--------+----------+-------+-------+--------+-------+------+--------+
4 rows in set (0.00 sec)

mysql> alter table student3
    -> change name new_name varchar(10);
Query OK, 4 rows affected (2.55 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select *from student3;
+--------+----------+-------+-------+--------+-------+------+--------+
| rollno | new_name | CLASS | board | School | MARKS | loc  | mobile |
+--------+----------+-------+-------+--------+-------+------+--------+
|    101 | student1 |    12 | NULL  | GVKCV  |    90 |      |   NULL |
|    102 | student2 |    12 | NULL  | GVKCV  |    89 |      |   NULL |
|    103 | student3 |    12 | NULL  | GVKCV  |    88 |      |   NULL |
|    104 | student4 |    12 | NULL  | GVKCV  |    87 |      |   NULL |
+--------+----------+-------+-------+--------+-------+------+--------+
4 rows in set (0.00 sec)

i)Alter table -Drop column

mysql> alter table student3
    -> drop mobile;
Query OK, 0 rows affected (1.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select *from student3;
+--------+----------+-------+-------+--------+-------+------+
| rollno | new_name | CLASS | board | School | MARKS | loc  |
+--------+----------+-------+-------+--------+-------+------+
|    101 | student1 |    12 | NULL  | GVKCV  |    90 |      |
|    102 | student2 |    12 | NULL  | GVKCV  |    89 |      |
|    103 | student3 |    12 | NULL  | GVKCV  |    88 |      |
|    104 | student4 |    12 | NULL  | GVKCV  |    87 |      |
+--------+----------+-------+-------+--------+-------+------+
4 rows in set (0.00 sec)