SQL ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
-----------
alter table person add column name varchar(50) not null after id
alter table person drop column name
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;
alter table orders add returneddate date after shippeddate ;
alter table orders drop returneddate ;
alter table orders modify returneddate year;
---------------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use classimodels;
ERROR 1049 (42000): Unknown database 'classimodels'
mysql> use classimodel;
ERROR 1049 (42000): Unknown database 'classimodel'
mysql> use classicmodels;
Database changed
mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| accounts |
| customers |
| employees |
| offices |
| orderdetails |
| orders |
| payments |
| productlines |
| products |
| users |
+-------------------------+
10 rows in set (0.04 sec)
mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber | int | NO | PRI | NULL | |
| orderDate | date | NO | | NULL | |
| requiredDate | date | NO | | NULL | |
| shippedDate | date | YES | | NULL | |
| status | varchar(15) | NO | | NULL | |
| comments | text | YES | | NULL | |
| customerNumber | int | NO | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
7 rows in set (0.19 sec)
mysql> alter table orders add returneddate date ;
Query OK, 0 rows affected (2.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber | int | NO | PRI | NULL | |
| orderDate | date | NO | | NULL | |
| requiredDate | date | NO | | NULL | |
| shippedDate | date | YES | | NULL | |
| status | varchar(15) | NO | | NULL | |
| comments | text | YES | | NULL | |
| customerNumber | int | NO | MUL | NULL | |
| returneddate | date | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> select * from orders limit 2;
+-------------+------------+--------------+-------------+---------+------------------------+----------------+--------------+
| orderNumber | orderDate | requiredDate | shippedDate | status | comments | customerNumber | returneddate |
+-------------+------------+--------------+-------------+---------+------------------------+----------------+--------------+
| 10100 | 2003-01-06 | 2003-01-13 | 2003-01-10 | Shipped | NULL | 363 | NULL |
| 10101 | 2003-01-09 | 2003-01-18 | 2003-01-11 | Shipped | Check on availability. | 128 | NULL |
+-------------+------------+--------------+-------------+---------+------------------------+----------------+--------------+
2 rows in set (0.00 sec)
mysql> select * from orders limit 2 /g;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/g' at line 1
mysql> select * from orders limit 2 \g;
+-------------+------------+--------------+-------------+---------+------------------------+----------------+--------------+
| orderNumber | orderDate | requiredDate | shippedDate | status | comments | customerNumber | returneddate |
+-------------+------------+--------------+-------------+---------+------------------------+----------------+--------------+
| 10100 | 2003-01-06 | 2003-01-13 | 2003-01-10 | Shipped | NULL | 363 | NULL |
| 10101 | 2003-01-09 | 2003-01-18 | 2003-01-11 | Shipped | Check on availability. | 128 | NULL |
+-------------+------------+--------------+-------------+---------+------------------------+----------------+--------------+
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> select * from orders limit 2 \G;
*************************** 1. row ***************************
orderNumber: 10100
orderDate: 2003-01-06
requiredDate: 2003-01-13
shippedDate: 2003-01-10
status: Shipped
comments: NULL
customerNumber: 363
returneddate: NULL
*************************** 2. row ***************************
orderNumber: 10101
orderDate: 2003-01-09
requiredDate: 2003-01-18
shippedDate: 2003-01-11
status: Shipped
comments: Check on availability.
customerNumber: 128
returneddate: NULL
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> alter table orders drop returneddate ;
Query OK, 0 rows affected (2.75 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber | int | NO | PRI | NULL | |
| orderDate | date | NO | | NULL | |
| requiredDate | date | NO | | NULL | |
| shippedDate | date | YES | | NULL | |
| status | varchar(15) | NO | | NULL | |
| comments | text | YES | | NULL | |
| customerNumber | int | NO | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> alter table orders add returneddate date after shippeddate ;
Query OK, 0 rows affected (3.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber | int | NO | PRI | NULL | |
| orderDate | date | NO | | NULL | |
| requiredDate | date | NO | | NULL | |
| shippedDate | date | YES | | NULL | |
| returneddate | date | YES | | NULL | |
| status | varchar(15) | NO | | NULL | |
| comments | text | YES | | NULL | |
| customerNumber | int | NO | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> alter table orders alter returneddate year;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'year' at line 1
mysql> alter table orders modify returneddate year;
Query OK, 326 rows affected (2.69 sec)
Records: 326 Duplicates: 0 Warnings: 0
mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber | int | NO | PRI | NULL | |
| orderDate | date | NO | | NULL | |
| requiredDate | date | NO | | NULL | |
| shippedDate | date | YES | | NULL | |
| returneddate | year | YES | | NULL | |
| status | varchar(15) | NO | | NULL | |
| comments | text | YES | | NULL | |
| customerNumber | int | NO | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
8 rows in set (0.05 sec)
mysql> alter table orders modify shippeddate year;
ERROR 1264 (22003): Out of range value for column 'shippeddate' at row 1
mysql> alter table orders modify status varchar(29);
Query OK, 0 rows affected (2.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber | int | NO | PRI | NULL | |
| orderDate | date | NO | | NULL | |
| requiredDate | date | NO | | NULL | |
| shippedDate | date | YES | | NULL | |
| returneddate | year | YES | | NULL | |
| status | varchar(29) | YES | | NULL | |
| comments | text | YES | | NULL | |
| customerNumber | int | NO | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> alter table orders modify status varchar(2);
ERROR 1265 (01000): Data truncated for column 'status' at row 1
mysql> alter table orders drop comments;
Query OK, 0 rows affected (2.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber | int | NO | PRI | NULL | |
| orderDate | date | NO | | NULL | |
| requiredDate | date | NO | | NULL | |
| shippedDate | date | YES | | NULL | |
| returneddate | year | YES | | NULL | |
| status | varchar(29) | YES | | NULL | |
| customerNumber | int | NO | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
7 rows in set (0.02 sec)
mysql>