Create a DATABASE. Note that the shortened word SCHEMA can be used as a synonym.
CREATE DATABASE Baseball; -- creates a database named Baseball
If the database already exists, Error 1007 is returned. To get around this error, try:
CREATE DATABASE IF NOT EXISTS Baseball;
Similarly,
DROP DATABASE IF EXISTS Baseball; -- Drops a database if it exists, avoids Error 1008
DROP DATABASE xyz; -- If xyz does not exist, ERROR 1008 will occur
Due to the above Error possibilities, DDL statements are often used with IF EXISTS
.
One can create a database with a default CHARACTER SET and collation. For example:
CREATE DATABASE Baseball CHARACTER SET utf8 COLLATE utf8_general_ci;
SHOW CREATE DATABASE Baseball;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| Baseball | CREATE DATABASE `Baseball` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
See your current databases:
SHOW DATABASES;
+---------------------+
| Database |
+---------------------+
| information_schema |
| ajax_stuff |
| Baseball |
+---------------------+
Set the currently active database, and see some information:
USE Baseball; -- set it as the current database
SELECT @@character_set_database as cset,@@collation_database as col;
+------+-----------------+
| cset | col |
+------+-----------------+
| utf8 | utf8_general_ci |
+------+-----------------+
The above shows the default CHARACTER SET and Collation for the database.
Create a user:
CREATE USER 'John123'@'%' IDENTIFIED BY 'OpenSesame';
The above creates a user John123, able to connect with any hostname due to the %
wildcard. The Password for the user is set to ‘OpenSesame’ which is hashed.
And create another:
CREATE USER 'John456'@'%' IDENTIFIED BY 'somePassword';
Show that the users have been created by examining the special mysql
database: