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

MySQL FAQs - Understanding SQL CREATE, ALTER and DROP Statements

By: FYIcenter.com

Part:   1   2  3  4  5 

A collection of 17 FAQs on Oracle SQL DDL statements. Clear answers are provided with tutorial exercises on creating, altering and dropping tables, indexes, and views; adding, renaming, and dropping table columns. Topics included in this FAQ are:

  1. What Are DDL Statements?
  2. How To Create a New Table?
  3. What Happens If You No CREATE Privilege in a Database?
  4. How To Get a List of All Tables in a Database?
  5. How To Get a List of Columns in an Existing Table?
  6. How To See the CREATE TABLE Statement of an Existing Table?
  7. How To Create a New Table by Selecting Rows from Another Table?
  8. How To Add a New Column to an Existing Table?
  9. How To Delete an Existing Column in a Table?
  10. How To Rename an Existing Column in a Table?
  11. How To Rename an Existing Table?
  12. How To Drop an Existing Table?
  13. How To Create a Table Index?
  14. How To Get a List of Indexes of an Existing Table?
  15. How To Drop an Existing Index?
  16. How To Create a New View?
  17. How To Drop an Existing View?

Please note that all answers and tutorials are based on MySQL 5.0. Tutorial exercises should be executed with "mysql" or other MySQL client programs. It is also assumed that you have a MySQL user account and a predefined database with enough privileges.

Some sample scripts requires database tables created by other samples in the beginning of the collection.

What Are DDL Statements?

DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are 3 primary DDL statements:

  • CREATE - Creating a new database object.
  • ALTER - Altering the definition of an existing data object.
  • DROP - Dropping an existing data object.

How To Create a New Table?

If you want to create a new table, you can use the "CREATE TABLE" statement. The following tutorial script shows you how to create a table called "tip":

mysql> CREATE TABLE tip (id INTEGER PRIMARY KEY,
   subject VARCHAR(80) NOT NULL,
   description VARCHAR(256) NOT NULL,
   create_date DATE NULL);
   
Query OK, 0 rows affected (0.08 sec)

This scripts creates a testing table called "tip" with 4 columns in the current database.

What Happens If You No CREATE Privilege in a Database?

In order to create tables in a database, your user account must have the CREATE privilege for that database. Otherwise you will get an error as shown in the following tutorial exercise:

>cd \mysql\bin
>mysql -u guest -ppub

mysql> use fyi;
Database changed

mysql> CREATE TABLE test (id integer);
ERROR 1142 (42000): CREATE command denied to user 
   'guest'@'localhost' for table 'test'

If you get this error, you need to see the DBA to obtain the CREATE privilege.

How To Get a List of All Tables in a Database?

If you want to see the table you have just created, you can use the "SHOW TABLES" command to get a list of all tables in database. The tutorial script gives you a good example:

mysql> SHOW TABLES;
+---------------+
| Tables_in_fyi |
+---------------+
| links         |
| tip           |
+---------------+
3 rows in set (0.00 sec)

(Continued on next part...)

Part:   1   2  3  4  5 


Selected Developer Jobs:

More...