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.
CREATE DATABASEThis 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 COLUMNSWith this MySQL statement you can display the columns for a table: their datatype, whether they're indexed, default value, etc.. |
ALTER DATABASEThis MySQL statement may be used to change the contents of a db.opt file, which contains the default CHARACTER SET and COLLATE values. |
CREATE INDEXUse 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. |
RENAME DATABASEThis MySQL statement does what it says: it will rename a database to whatever name you give it, provided it doesn't already exist. |
DROP INDEXThis MySQL statement deletes a given index from a MySQL table. It's synonymous with the MySQL statement ALTER TABLE...DROP INDEX.... |
DROP DATABASEUse this MySQL statement to delete a given database along with all its tables and data. |
SHOW INDEXESThis MySQL statement displays information about the indexes for a MySQL table. |
SHOW DATABASESThis MySQL statement displays the list of databases on the server. The keyword DATABASE has become synomymous with SCHEMA. |
CREATE VIEWUse 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 CREATE DATABASEThis MySQL statement just displays the CREATE DATABASE statement for creating the MySQL database given—which isn't very useful for most developers. |
ALTER VIEWUse this MySQL statement to change a view, which are created with CREATE VIEW, of course. |
CREATE SCHEMAThis MySQL statement is synonymous with CREATE DATABASE. |
DROP VIEWThis MySQL statement deletes a view. Multiple views may be given in a comma-separated list. |
ALTER SCHEMAThis MySQL statement may be used to change the contents of a db.opt file, which contains the default CHARACTER SET and COLLATE values. |
SHOW VIEWSIf 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. |
CREATE TABLEThis 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 VIEWUse 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. |
ALTER TABLEAfter you've created a table, use this MySQL statement to change the structure of a table. |
CREATE SERVERYou can use this MySQL statement with the FEDERATED storage engine to set the connection parameters. |
RENAME TABLEIf for some reason you want to rename an existing MySQL table, use this MySQL statement. It's the easiest way. |
ALTER SERVERUse this MySQL statement with the FEDERATED storage engine to change the connection parameters of a server created with CREATE SERVER. |
DROP TABLEUse this MySQL statement to delete a MySQL table from a database, including its data. |
DROP SERVERThis MySQL statement can be used with the FEDERATED storage engine to delete a given server that was created with CREATE SERVER. |
SHOW TABLESThis MySQL statement displays a list of MySQL tables and views. To distinguish between MySQL tables and views, add the FULL keyword. |
SHOW CHARACTER SETThis MySQL statement will show all of the character sets (e.g., latin1, utf8) installed on the server. |
SHOW CREATE TABLEThis 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 COLLATIONUse 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. |
DESCRIBEThis MySQL statement displays information about the columns of a given MySQL table. |
Need Math Examples
Although we did well in math classes all through school, we have very little experience with the Math Functions of MySQL very little. As a result, we don't have very good examples for them. If you work in science or engineering and have some ideas for easy to understand examples that we can add, please tell us.
MySQL Replication
Whether you’re new to MySQL replication or have been using it for some time, you will find this new book useful. It will help you actually to understand MySQL replication so you can keep it running. It’s especially useful when replication stops and you can’t get it started again.
