MySQL cheatsheet

Sometimes I derp out and forget some essential commands on MySQL. This is for me to reference if that ever happens. :D

Commands

If you need to create a new user:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Now to create a specific database:

CREATE DATABASE [IF EXISTS]* new_database;

Showing ALL the databases on MySQL:

SHOW DATABASES;

To grant ALL the privileges on the table to a specific user:

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

You can also choose which privilege to give to a user when assigning a table, which include the following:

ALL PRIVILEGES - as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system).
CREATE - allows them to create new tables or databases.
DROP - allows them to them to delete tables or databases.
DELETE - allows them to delete rows from tables.
INSERT - allows them to insert rows into tables.
SELECT - allows them to use the Select command to read through databases.
UPDATE - allow them to update table rows.
GRANT OPTION - allows them to grant or remove other users' privileges.

Format if you choose to give specific permissions:

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

Revoke any permissions which might have been given to a user for a specific database:

REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;

Removing a database for whatever reason you need to:

DROP DATABASE [IF EXISTS]* new_database;

If at any time you change a setting related to users or databases, make sure to flush for new changes:

FLUSH PRIVILEGES;

It's fairly easy to add tables and individual data but is not the scope of this entry.

• [IF EXISTS] will avoid an error relating to said functions already existing.