Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Understanding SQL CREATE, ALTER and DROP Statements

By: FYIcenter.com

Part:   1  2   3  4  5 

(Continued from previous part...)

How To Get a List of Columns in an Existing Table?

If you have an existing table, but you don't remember what are the columns used in the table, you can use the "SHOW COLUMNS FROM tableName" command to get a list of all columns of the specified table. You can also use the "DESCRIBE tableName" command, which gives you the same output as "SHOW COLUMNS" command. The following tutorial script shows you a good example:

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    |       
+-------------+--------------+------+-----+---------+-------
4 rows in set (0.04 sec)

How To See the CREATE TABLE Statement of an Existing Table?

If you want to know how an existing table was created, you can use the "SHOW CREATE TABLE" command to get a copy of the "CREATE TABLE" statement back on an existing table. The following tutorial script shows you a good example:

mysql> SHOW CREATE TABLE tip;

+-------+-------------------------------
| Table | Create Table
+-------+-------------------------------
| tip   | CREATE TABLE `tip` (
  `id` int(11) NOT NULL,
  `subject` varchar(80) NOT NULL,
  `description` varchar(256) NOT NULL,
  `create_date` date default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------
1 row in set (0.38 sec)

Comparing with the original "CREATE TABLE" statement used in the previous tutorial, the output tells you that:

• INTEGER data type was replaced by "int(11)".
• Default database engine "MyISAM" was used for the table.
• Default character set "latin1" was used for the table.

How To Create a New Table by Selecting Rows from Another Table?

Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the "CREATE TABLE ... SELECT" statement. The tutorial script below gives you a good example:

mysql> INSERT INTO tip VALUES (1, 'Learn MySQL', 
   'Visit dev.fyicenter.com','2006-07-01');
Query OK, 1 row affected (0.62 sec)

mysql> CREATE TABLE tipBackup SELECT * FROM tip;
Query OK, 1 row affected (0.49 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tipBackup;
+----+-------------+-------------------------+-------------+
| id | subject     | description             | create_date |
+----+-------------+-------------------------+-------------+
|  1 | Learn MySQL | Visit dev.fyicenter.com | 2006-07-01  |
+----+-------------+-------------------------+-------------+
1 row in set (0.00 sec)

As you can see, this SQL script created a table called "tipBackup" using the same column definitions as the "tip" table and copied all data rows into "tipBackup".

(Continued on next part...)

Part:   1  2   3  4  5