MySQL Programming

If you have an existing table with existing data rows, and want to add a new column to that table, you can use the "ALTER TABLE ... ADD COLUMN" statement. The tutorial script below shows you a good example:

mysql> ALTER TABLE tip ADD COLUMN author VARCHAR(40);

Query OK, 1 row affected (0.18 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM tip;

+-------------+--------------+------+-----+---------+-------

| Field  | Type  | Null | Key | Default | Extra 

+-------------+--------------+------+-----+---------+-------

| id  | int(11) | NO | PRI |  |  

| subject  | varchar(80) | NO |  |  |  

| description | varchar(256) | NO |  |  |  

| create_date | date  | YES |  | NULL |  

| author | varchar(40) | YES |  | NULL |  

+-------------+--------------+------+-----+---------+-------

5 rows in set (0.01 sec)

This SQL script added a new column called "author" to the "tip" table. NULL values were added to this column on all existing data rows.

If you have an existing column in a table and you want to change the column name, you can use the "ALTER TABLE ... CHANGE" statement. This statement allows you to change the name of a column, and its definition. The tutorial script below gives you a good example:

mysql> ALTER TABLE tip CHANGE COLUMN subject

title VARCHAR(60);

Query OK, 1 row affected (0.51 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM tip;

+-------------+--------------+------+-----+---------+-------

| Field  | Type  | Null | Key | Default | Extra 

+-------------+--------------+------+-----+---------+-------

| id  | int(11) | NO | PRI |  |  

| title  | varchar(60) | YES |  | NULL |  

| description | varchar(256) | NO |  |  |  

| author | varchar(40) | YES |  | NULL |  

+-------------+--------------+------+-----+---------+-------

4 rows in set (0.02 sec)

If you want to see the index you have just created for an existing table, you can use the "SHOW INDEX FROM tableName" command to get a list of all indexes in a given table. The tutorial script below shows you a nice example:

mysql> SHOW INDEX FROM TIP;

+------------+-------------+--------------+-------------+...

| Non_unique | Key_name | Seq_in_index | Column_name |...

+------------+-------------+--------------+-------------+...

|  0 | PRIMARY  |  1 | id  |...

|  1 | tip_subject |  1 | subject  |...

+------------+-------------+--------------+-------------+...

2 rows in set (0.03 sec)

It's interesting to see that there is a default index for the primary key column.

If you have an existing column in a table and you do not need that column any more, you can delete it with "ALTER TABLE ... DROP COLUMN" statement. Here is a tutorial script to delete an existing column:

mysql> ALTER TABLE tip DROP COLUMN create_date;

Query OK, 1 row affected (0.48 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM tip;

+----+-------------+-------------------------+--------+

| id | subject  | description   | author |

+----+-------------+-------------------------+--------+

| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | NULL |

+----+-------------+-------------------------+--------+

1 row in set (0.00 sec)

As you can see the column "create_date" is gone.

You can create a new view based on one or more existing tables by using the "CREATE VIEW viewName AS selectStatement" statement as shown in the following script:

mysql> CREATE TABLE comment (faqID INTEGER,

message VARCHAR(256));

Query OK, 0 rows affected (0.45 sec)

mysql> INSERT INTO comment VALUES (1, 'I like it');

Query OK, 1 row affected (0.00 sec)

mysql> CREATE VIEW faqComment AS SELECT f.id, f.title,

f.description, c.message FROM faq f, comment c

WHERE f.id = c.faqID;

Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM faqComment;

+----+-------------+-------------------------+-----------+

| id | title  | description   | message |

+----+-------------+-------------------------+-----------+

| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | I like it |

+----+-------------+-------------------------+-----------+

1 row in set (0.07 sec)