Altering Database Tables
|
|
|
/*
mysql> CREATE TABLE Employee (
-> Name VARCHAR(50) PRIMARY KEY NOT NULL,
-> PhoneNo VARCHAR(15) DEFAULT 'Unknown Phone',
-> Age INT CHECK (Age BETWEEN 20 and 30));
Query OK, 0 rows affected (0.12 sec)
mysql> Describe Employee;
+---------+-------------+------+-----+---------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------------+-------+
| Name | varchar(50) | | PRI | | |
| PhoneNo | varchar(15) | YES | | Unknown Phone | |
| Age | int(11) | YES | | NULL | |
+---------+-------------+------+-----+---------------+-------+
3 rows in set (0.00 sec)
mysql> Select * from Employee;
+----------+---------------+------+
| Name | PhoneNo | Age |
+----------+---------------+------+
| John Doe | Unknown Phone | 31 |
+----------+---------------+------+
1 row in set (0.00 sec)
mysql> ALTER TABLE Employee ADD (EMail VARCHAR(25), ICQ VARCHAR(15));
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> Describe Employee;
+---------+-------------+------+-----+---------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------------+-------+
| Name | varchar(50) | | PRI | | |
| PhoneNo | varchar(15) | YES | | Unknown Phone | |
| Age | int(11) | YES | | NULL | |
| EMail | varchar(25) | YES | | NULL | |
| ICQ | varchar(15) | YES | | NULL | |
+---------+-------------+------+-----+---------------+-------+
5 rows in set (0.00 sec)
mysql> Select * from Employee;
+----------+---------------+------+-------+------+
| Name | PhoneNo | Age | EMail | ICQ |
+----------+---------------+------+-------+------+
| John Doe | Unknown Phone | 31 | NULL | NULL |
+----------+---------------+------+-------+------+
1 row in set (0.01 sec)
*/
Drop TABLE Employee;
CREATE TABLE Employee (
Name VARCHAR(50) PRIMARY KEY NOT NULL,
PhoneNo VARCHAR(15) DEFAULT 'Unknown Phone',
Age INT CHECK (Age BETWEEN 20 and 30));
Describe Employee;
INSERT INTO Employee (Name, Phone, Age) VALUES ('Joe Yin', '666 2323', 26);
INSERT INTO Employee (Name, Age) VALUES ('John Doe', 31);
Select * from Employee;
ALTER TABLE Employee ADD (EMail VARCHAR(25), ICQ VARCHAR(15));
Describe Employee;
Select * from Employee;
|
|
|
|
|
Related Scripts with Example Source Code in same category :
-
-
-
-
-
-
-
|
|
