MySQL Resources | resources for mysql admins and developers who are squealing for help

quick links

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.	DROP TABLE Use this MySQL statement to delete a MySQL table from a database, including its data.
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.	DROP VIEW This MySQL statement deletes a view. Multiple views may be given in a comma-separated list.
ALTER SERVER Use this MySQL statement with the FEDERATED storage engine to change the connection parameters of a server created with `CREATE SERVER`.	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.
ALTER TABLE After you've created a table, use this MySQL statement to change the structure of a table.	RENAME TABLE If for some reason you want to rename an existing MySQL table, use this MySQL statement. It's the easiest way.
ALTER VIEW Use this MySQL statement to change a view, which are created with `CREATE VIEW`, of course.	SHOW CHARACTER SET This MySQL statement will show all of the character sets (e.g., latin1, utf8) installed on the server.
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.	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.
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.	SHOW COLUMNS With this MySQL statement you can display the columns for a table: their datatype, whether they're indexed, default value, etc..
CREATE SCHEMA This MySQL statement is synonymous with `CREATE DATABASE`.	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.
CREATE SERVER You can use this MySQL statement with the FEDERATED storage engine to set the connection parameters.	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.
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.	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.
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.	SHOW DATABASES This MySQL statement displays the list of databases on the server. The keyword `DATABASE` has become synomymous with `SCHEMA`.
DESCRIBE This MySQL statement displays information about the columns of a given MySQL table.	SHOW INDEXES This MySQL statement displays information about the indexes for a MySQL table.
DROP DATABASE Use this MySQL statement to delete a given database along with all its tables and data.	SHOW TABLES This MySQL statement displays a list of MySQL tables and views. To distinguish between MySQL tables and views, add the `FULL` keyword.
DROP INDEX This MySQL statement deletes a given index from a MySQL table. It's synonymous with the MySQL statement `ALTER TABLE...DROP INDEX...`.	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.
DROP SERVER This MySQL statement can be used with the FEDERATED storage engine to delete a given server that was created with `CREATE SERVER`.

Every where I look, I see columns (Dublin, Ireland)

MySQL in a Nutshell

If you work with MySQL regularly and need a book on the topic with an easy to follow writing style and helpful examples, please consider buying MySQL in a Nutshell. If you already have the first edition, the second edition recently came out: it’s much better than the first edition and has had some great reviews. Click on the cover art work here and you’ll be taken to Amazon’s site where you can purchase a copy and have it mailed to you.