Data Definition Statements

Part:   1  2  3  

(Continued from previous part...)

Output:

First show index                                                        
First show index                                                        
Table	Non_unique Key_name	Seq_in_index	Column_name	
User	0          PRIMARY	1	        ID	
User	0          Password	1	        Password
User	1          LoginIndex	1	        Login	
Second show index                                                       
Second show index                                                       
Table	Non_unique Key_name	Seq_in_index	Column_name	
User	0	   PRIMARY	1	        ID	
User	0	   Password	1	        Password
User	1	   LoginIndex	1	        Login
User	1	   EmailIndex	1	        Email

Note that:

• An index is automatically created for the primary key column.
• An index is automatically created for each column that is defined as unique.

Alter Table Statements

An alter table statement allows you to change the structure of an existing table. It has a number of syntax formats:

1. To add a new column:

ALTER TABLE tbl_name ADD COLUMN col_name col_type col_options;

2. To add a new index:

ALTER TABLE tbl_name ADD INDEX idx_name (idx_column,...);

3. To drop an existing column:

ALTER TABLE tbl_name DROP COLUMN col_name;

4. To drop an existing index:

ALTER TABLE tbl_name DROP INDEX idx_name;

5. To modify the type or options of an existing column:

ALTER TABLE tbl_name MODIFY COLUMN col_name col_type col_options;

6. To reset the next value of the auto incremented column:

ALTER TABLE tbl_name AUTO_INCREMENT = value;

Note that in many cases, an alter table statement will cause the system to copy the original table into temporary copy, and perform the changes on the copy. If the table contains a huge amount of data, it will take a long time to execute an alter table statement.

Here is an example code of various alter table statements:

-- AlterTable.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
CREATE TABLE IF NOT EXISTS User (ID INT PRIMARY KEY AUTO_INCREMENT,
   Login CHAR(8) NOT NULL, Password CHAR(8) UNIQUE, 
   Email CHAR(32) DEFAULT 'info@hy.com',
   INDEX LoginIndex (Login));
INSERT INTO User VALUES (null,'herong','8IS3KOXW','herong@hy.comm');
INSERT INTO User (Login, Password, Email) 
   VALUES ('mike','PZ0JG',null);
INSERT INTO User (Login, Password) VALUES ('mike','GZDN');
SELECT 'Show index #1:';
SHOW INDEX FROM User;
ALTER TABLE User ADD COLUMN Phone CHAR(10);
ALTER TABLE User DROP COLUMN Password;
ALTER TABLE User ADD INDEX EmailIndex (Email);
ALTER TABLE User DROP INDEX LoginIndex;
ALTER TABLE User AUTO_INCREMENT = 1000;
INSERT INTO User (Login, Phone) VALUES ('bill','8008008000');
SELECT 'Show data';
SELECT * FROM User;
SELECT 'Show index #2:';
SHOW INDEX FROM User;
DROP TABLE User;

Output:

Show index #1:
Show index #1:
Table   Non_unique   Key_name   Seq_in_index   Column_name
User    0            PRIMARY    1              ID 
User    0            Password   1              Password 
User    1            LoginIndex 1              Login 
Show data
Show data
ID      Login   Email           Phone
1       herong  herong@hy.comm  NULL
2       mike    NULL            NULL
3       mike    info@hy.com     NULL
1000    bill    info@hy.com     8008008000
Show index #2:
Show index #2:
Table   Non_unique   Key_name   Seq_in_index   Column_name
User    0            PRIMARY    1              ID
User    1            EmailIndex 1              Email

Note that:

• Adding a column and dropping a coclumn worked correctly.
• Adding an index and dropping an index worked correctly too.
• Changing the next value of the auto incremented column is easy.

Part:   1  2  3