- 2.1. The Server SQL Mode
- 2.2. MySQL Identifier Syntax and Naming Rules
- 2.3. Case Sensitivity in SQL Statements
- 2.4. Character Set Support
- 2.5. Selecting, Creating, Dropping, and Altering Databases
- 2.6. Creating, Dropping, Indexing, and Altering Tables
- 2.7. Obtaining Database Metadata
- 2.8. Performing Multiple-Table Retrievals with Joins
- 2.9. Performing Multiple-Table Retrievals with Subqueries
- 2.10. Performing Multiple-Table Retrievals with UNION
- 2.11. Multiple-Table Deletes and Updates
- 2.12. Performing Transactions
- 2.13. Foreign Keys and Referential Integrity
- 2.14. Using FULLTEXT Searches
This chapter is from the book
This chapter is from the book
This chapter is from the book
2.5. Selecting, Creating, Dropping, and Altering Databases
MySQL provides several database-level statements: USE for selecting a default database, CREATE DATABASE for creating databases, DROP DATABASE for removing them, and ALTER DATABASE for modifying global database characteristics.
The keyword SCHEMA is a synonym for DATABASE in any statement where the latter occurs.
2.5.1. Selecting Databases
The USE statement selects a database to make it the default (current) database for a given session with the server:
USE db_name;
You must have some access privilege for the database or an error occurs.
It is not strictly necessary to select a database explicitly. You can refer to tables in a database without selecting it first by using qualified names that identify both the database and the table. For example, to retrieve the contents of the president table in the sampdb database without making it the default database, write the query like this:
SELECT * FROM sampdb.president;
Selecting a database doesn’t mean that it must be the default for the duration of the session. You can issue USE statements as necessary to switch between databases. Nor does selecting a database limit you to using tables only from that database. While one database is the default, you can refer to tables in other databases by qualifying their names with the appropriate database identifier.
When you disconnect from the server, any notion by the server of which database was the default for the session disappears. If you connect to the server again, it doesn’t remember what database you had selected previously.
2.5.2. Creating Databases
To create a database, use a CREATE DATABASE statement:
CREATE DATABASE db_name;
The database must not already exist, and you must have the CREATE privilege for it.
CREATE DATABASE supports several optional clauses. The full syntax is as follows:
CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset] [COLLATE collation];
By default, an error occurs if you try to create a database that already exists. To suppress this error and create a database only if it does not already exist, add an IF NOT EXISTS clause:
CREATE DATABASE IF NOT EXISTS db_name;
By default, the server character set and collation become the database default character set and collation. To set these database attributes explicitly, use the CHARACTER SET and COLLATE clauses. For example:
CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_icelandic_ci;
If CHARACTER SET is given without COLLATE, the character set default collation is used. If COLLATE is given without CHARACTER SET, the first part of the collation name determines the character set.
The character set must be one of those supported by the server, such as latin1 or sjis. The collation should be a legal collation for the character set. For further discussion of character sets and collations, see Section 2.4, “Character Set Support.”
When you create a database, the MySQL server creates a directory under its data directory that has the same name as the database. The new directory is called the database directory. The server also creates a db.opt file in the database directory for storing attributes such as the database character set and collation. When you create a table in the database later, the database defaults become the table defaults if the table definition does not specify its own default character set and collation.
To see the definition for an existing database, use a SHOW CREATE DATABASE statement:
mysql> SHOW CREATE DATABASE mydb\G
*************************** 1. row ***************************
Database: mydb
Create Database: CREATE DATABASE `mydb`
/*!40100 DEFAULT CHARACTER SET utf8
COLLATE utf8_icelandic_ci */
2.5.3. Dropping Databases
Dropping a database is as easy as creating one, assuming that you have the DROP privilege for it:
DROP DATABASE db_name;
The DROP DATABASE statement is not something to use with wild abandon. It removes the database and all its contents (tables, stored routines, and so forth), which are therefore gone forever unless you have been making regular backups.
A database is represented by a directory under the data directory, and the directory is intended for storage of objects such as tables, views, and triggers. If a DROP DATABASE statement fails, the reason most likely is that the database directory contains files not associated with database objects. DROP DATABASE will not delete such files, and as a result will not delete the directory, either. This means that the database directory continues to exist and will show up if you issue a SHOW DATABASES statement. To really drop the database if this occurs, manually remove any extraneous files and subdirectories from the database directory, then issue the DROP DATABASE statement again.
2.5.4. Altering Databases
The ALTER DATABASE statement changes a database’s global attributes, if you have the ALTER privilege for it. Currently, the only such attributes are the default character set and collation:
ALTER DATABASE [db_name] [CHARACTER SET charset] [COLLATE collation];
The earlier discussion for CREATE DATABASE describes the effect of the CHARACTER SET and COLLATE clauses, at least one of which must be given.
If you omit the database name, ALTER DATABASE applies to the default database.