Difference between revisions of "Creating Databases and Tables Using SQL Commands"
| Line 1: | Line 1: | ||
In the previous chapter ([[Creating New Databases and Tables with MySQL Administrator]]) we created a new database and table using a graphical tool that did not rquire us to use any SQL commands. In this chapter we will learn how to create new databases and tables by issuing SQL commands using the ''mysql'' client. | In the previous chapter ([[Creating New Databases and Tables with MySQL Administrator]]) we created a new database and table using a graphical tool that did not rquire us to use any SQL commands. In this chapter we will learn how to create new databases and tables by issuing SQL commands using the ''mysql'' client. | ||
| − | This chapter assumes that the ''mysql'' tool is running and connected to the the MySQL database server. If this is not the case and you are unsure as to how to achieve this refer to [[The mysql Command-Line Tool]] chapter. Alternatively, the SQL commands outlined in that chapter may be executed in the [[The MySQL Query Browser | MySQL Query Browser]] | + | This chapter assumes that the ''mysql'' tool is running and connected to the the MySQL database server. If this is not the case and you are unsure as to how to achieve this refer to [[The mysql Command-Line Tool]] chapter. Alternatively, the SQL commands outlined in that chapter may be executed in the [[The MySQL Query Browser | MySQL Query Browser]]. |
== Creating a New MySQL Database == | == Creating a New MySQL Database == | ||
| + | |||
| + | A new database is created using the ''CREATE DATABASE'' SQL statement followed by the name of the database to be created. The ''CREATE SCHEMA'' statement may also be used for this purpose. For example, to create a new database called MySampleDB the following statement would be entered at the ''mysql>'' prompt: | ||
| + | |||
| + | <pre> | ||
| + | CREATE DATABASE MySampleDB; | ||
| + | </pre> | ||
| + | |||
| + | If successful, the command will generate output similar to the following: | ||
| + | |||
| + | <pre> | ||
| + | Query OK, 1 row affected (0.00 sec) | ||
| + | </pre> | ||
| + | |||
| + | If the database name specified conflicts with an existing database MySQL will display and error message reporting this fact: | ||
| + | |||
| + | <pre> | ||
| + | ERROR 1007 (HY000): Can't create database 'MySampleDB'; database exists | ||
| + | </pre> | ||
| + | |||
| + | In this situation, a different database name should be selected, or the ''IF NOT EXISTS'' option, which only creates the database if it does not already exists, and does not report and error if it does: | ||
| + | |||
| + | <pre> | ||
| + | CREATE DATABASE IF NOT EXISTS MySampleDB; | ||
| + | </pre> | ||
Revision as of 15:55, 28 September 2007
In the previous chapter (Creating New Databases and Tables with MySQL Administrator) we created a new database and table using a graphical tool that did not rquire us to use any SQL commands. In this chapter we will learn how to create new databases and tables by issuing SQL commands using the mysql client.
This chapter assumes that the mysql tool is running and connected to the the MySQL database server. If this is not the case and you are unsure as to how to achieve this refer to The mysql Command-Line Tool chapter. Alternatively, the SQL commands outlined in that chapter may be executed in the MySQL Query Browser.
Creating a New MySQL Database
A new database is created using the CREATE DATABASE SQL statement followed by the name of the database to be created. The CREATE SCHEMA statement may also be used for this purpose. For example, to create a new database called MySampleDB the following statement would be entered at the mysql> prompt:
CREATE DATABASE MySampleDB;
If successful, the command will generate output similar to the following:
Query OK, 1 row affected (0.00 sec)
If the database name specified conflicts with an existing database MySQL will display and error message reporting this fact:
ERROR 1007 (HY000): Can't create database 'MySampleDB'; database exists
In this situation, a different database name should be selected, or the IF NOT EXISTS option, which only creates the database if it does not already exists, and does not report and error if it does:
CREATE DATABASE IF NOT EXISTS MySampleDB;
