Database & Table Schema Statements

This section of my documentation of MySQL explains SQL statements in MySQL related to databases and table schema: creating, altering, and deleting databases, tables, and columns, as well as display information related to databases, tables, and columns. The statements in this section pertain to information about these data structures, not the manipulation of data within them; statements that affect the data are covered in the Data Manipulation Statements section. In essence, this section covers the SQL statements used when one is in the mode of creating database structures. This mode is a fairly distinct mindset and sometimes the responsibility of different persons from those who manipulate the data itself.

Below is a listing of related SQL statements followed by related clauses, with brief explanations of each one's purpose. Click on an SQL statement to see a more detailed explanation including examples of the statement's use.

ALTER DATABASE

This MySQL statement may be used to change the contents of a db.opt file, which contains the default CHARACTER SET and COLLATE values.

ALTER SCHEMA

This MySQL statement may be used to change the contents of a db.opt file, which contains the default CHARACTER SET and COLLATE values.

ALTER SERVER

Use this MySQL statement with the FEDERATED storage engine to change the connection parameters of a server created with CREATE SERVER.

ALTER TABLE

After you've created a table, use this MySQL statement to change the structure of a table.

ALTER VIEW

Use this MySQL statement to change a view, which are created with CREATE VIEW, of course.

CREATE DATABASE

This MySQL statement creates a new database with the name given. There's not much to it. It basically creates a directory in the data directory of MySQL for you to subsequently create tables and then add data.

CREATE INDEX

Use this MySQL statement to add an index to a MySQL table after it has been created. This is an alias of relevant clauses of the MySQL statement ALTER TABLE that add indexes to a MySQL table.

This MySQL statement is synonymous with CREATE DATABASE.

CREATE SERVER

You can use this MySQL statement with the FEDERATED storage engine to set the connection parameters.

CREATE TABLE

This is one of the primary MySQL statements that a MySQL developer will use when first setting up a database. It's used to create MySQL tables, to which data will later be added.

CREATE VIEW

Use this MySQL statement to create a view, which is a preset query, stored in a database. In certain situations a view can be useful for improved security. Views are available as of version 5.0.2 of MySQL.

DESCRIBE

This MySQL statement displays information about the columns of a given MySQL table.

DROP DATABASE

Use this MySQL statement to delete a given database along with all its tables and data.

DROP INDEX

This MySQL statement deletes a given index from a MySQL table. It's synonymous with the MySQL statement ALTER TABLE...DROP INDEX....

DROP SERVER

This MySQL statement can be used with the FEDERATED storage engine to delete a given server that was created with CREATE SERVER.

DROP TABLE

Use this MySQL statement to delete a MySQL table from a database, including its data.

DROP VIEW

This MySQL statement deletes a view. Multiple views may be given in a comma-separated list.

RENAME DATABASE

This MySQL statement does what it says: it will rename a database to whatever name you give it, provided it doesn't already exist.

RENAME TABLE

If for some reason you want to rename an existing MySQL table, use this MySQL statement. It's the easiest way.

SHOW CHARACTER SET

This MySQL statement will show all of the character sets (e.g., latin1, utf8) installed on the server.

SHOW COLLATION

Use this MySQL statement to list all of the collation character sets. This is related to SHOW CHARACTER SET in that collation is the ordering (i.e., alphabetizing) of the characters of the character set.

SHOW COLUMNS

With this MySQL statement you can display the columns for a table: their datatype, whether they're indexed, default value, etc..

SHOW CREATE DATABASE

This MySQL statement just displays the CREATE DATABASE statement for creating the MySQL database given—which isn't very useful for most developers.

SHOW CREATE TABLE

This MySQL statement displays an SQL statement that can be used to create a MySQL table like the one named. The results may be copied and used with another MySQL database.

SHOW CREATE VIEW

Use this MySQL statement to display an SQL statement that can be used to create a view like the one named. The results may be copied and used to recreate the, but either with a different name or in a different database.

SHOW DATABASES

This MySQL statement displays the list of databases on the server. The keyword DATABASE has become synomymous with SCHEMA.

SHOW INDEXES

This MySQL statement displays information about the indexes for a MySQL table.

SHOW TABLES

This MySQL statement displays a list of MySQL tables and views. To distinguish between MySQL tables and views, add the FULL keyword.

SHOW VIEWS

If you were looking for this MySQL statement and can't find it anywhere else, I'm not surprise. Click on this one and you'll see why.