MySQL in a Nutshell [50]
Statements and Clauses in Alphabetical Order
The following is a list of MySQL statements and clauses related to database and table schema, in alphabetical order. To understand how this book presents SQL syntax and describes SQL statements, as well as for information related to examples, please see the introduction to Part II. Many of the examples in this particular chapter involve the activities of the departments of a fictitious company: its human resources department and employee data, its sales department and client contact information, and its internal IT department with user work requests.
Name
ALTER DATABASE
Synopsis
ALTER {DATABASE|SCHEMA} database
[DEFAULT] CHARACTER SET character_set |
[DEFAULT] COLLATE collation
Use this statement to alter settings for a database. Version 4.1.1 of MySQL introduced this function and added a file named db.opt containing the database settings to the database directory. Currently, two options are available: CHARACTER SET and COLLATE. Here are the contents of a typical db.opt file:
default-character-set=latin1
default-collation=latin1_swedish_ci
Although an administrator can edit the file manually, it may be more robust to use the ALTER DATABASE statement to change the file. It’s synonymous with ALTER SCHEMA as of version 5.0.2 of MySQL. The ALTER privilege is necessary for this statement.
The CHARACTER SET option can set the first line shown, which specifies the default database character set that will be used. The COLLATE option can set the second line, which specifies the default database collation (how the character data is alphabetized). Here’s an example of the use of this statement:
ALTER DATABASE human_resources
CHARACTER SET latin2_bin
COLLATE latin2_bin;
Notice that both options may be given in one SQL statement. The DEFAULT keyword is unnecessary, but it is offered for compatibility with other database systems. Beginning with version 4.1.8 of MySQL, if the name of the database is omitted from this SQL statement, the current database will be assumed. To determine the current database, use the DATABASE( ) function:
SELECT DATABASE();
+--------------+
| DATABASE() |
+--------------+
| workrequests |
+--------------+
See the explanations for the SHOW CHARACTER SET and SHOW COLLATION SQL statements later in this chapter for more information on character sets and collations.
Name
ALTER SCHEMA
Synopsis
ALTER {DATABASE|SCHEMA} database
[DEFAULT] CHARACTER SET character_set |
[DEFAULT] COLLATE collation
This statement is synonymous with ALTER DATABASE. See the description of that statement previously for more information and examples.
Name
ALTER SERVER
Synopsis
ALTER SERVER server
OPTIONS (
{ HOST host, |
DATABASE database, |
USER user, |
PASSWORD password, |
SOCKET socket, |
OWNER character, |
PORT port }
)
Use this SQL statement with the FEDERATED storage engine to change the connection parameters of a server created with CREATE SERVER. The values given are stored in the server table of the mysql database. Options are given in a comma-separated list. Option values must be specified as character or numeric literals (UTF-8; maximum length of 64 characters). This statement was introduced in version 5.1.15 of MySQL and requires SUPER privileges:
ALTER SERVER server1
OPTIONS (USER 'test_user', PASSWORD 'testing123', PORT 3307);
This example changes the values of an existing server, the username, the password, and the port to be used for connecting to the server.
Name
ALTER TABLE
Synopsis
ALTER [IGNORE] TABLE table changes[, ...]
Use this statement to change an existing table’s structure and other properties. A table may be altered with this statement in the following ways:
Add a new column (see the ALTER