MOCKSTACKS
EN
Questions And Answers

More Tutorials









MYSQL Creating databases

Create database, users, and grants


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:

SELECT user,host,password from mysql.user where user in ('John123','John456');
+---------+------+-------------------------------------------+
| user | host | password |
+---------+------+-------------------------------------------+
| John123 | % | *E6531C342ED87 .................... |
| John456 | % | *B04E11FAAAE9A .................... |
+---------+------+-------------------------------------------+

Note that at this point, the users have been created, but without any permissions to use the Baseball database.

Work with permissions for users and databases. Grant rights to user John123 to have full privileges on the Baseball database, and just SELECT rights for the other user:

GRANT ALL ON Baseball.* TO 'John123'@'%';
GRANT SELECT ON Baseball.* TO 'John456'@'%';

Verify the above:

SHOW GRANTS FOR 'John123'@'%';
+-------------------------------------------------------------------------------------------------
-------+
| Grants for John123@%
|
+-------------------------------------------------------------------------------------------------
-------+
| GRANT USAGE ON *.* TO 'John123'@'%' IDENTIFIED BY PASSWORD '*E6531C342ED87 ....................
|
| GRANT ALL PRIVILEGES ON `baseball`.* TO 'John123'@'%'
|
+-------------------------------------------------------------------------------------------------
-------+
SHOW GRANTS FOR 'John456'@'%';
+-------------------------------------------------------------------------------------------------
-------+
| Grants for John456@%
|
+-------------------------------------------------------------------------------------------------
-------+
| GRANT USAGE ON *.* TO 'John456'@'%' IDENTIFIED BY PASSWORD '*B04E11FAAAE9A ....................
|
| GRANT SELECT ON `baseball`.* TO 'John456'@'%'
|
+-------------------------------------------------------------------------------------------------
-------+


Note that the GRANT USAGE that you will always see means simply that the user may login. That is all that that means.

Conclusion

In this page (written and validated by ) you learned about MYSQL Creating databases . What's Next? If you are interested in completing MYSQL tutorial, your next topic will be learning about: MYSQL Using Variables.



Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue: mockstacks@gmail.com.


Share On:


Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.