Basic MySQL Queries

SHOW DATABASES; lists all the databases available for use to the current user. Note: All mysql queries has to end with semicolon(;)


mysql> SHOW DATABASES; +-----------------------+ : Database : +-----------------------+ : dba : : test : +-----------------------+

CREATE DATABASE NAME_OF_THE_NEW_DATABASE; creates a database.

mysql> CREATE DATABASE db_test;
mysql> SHOW DATABASES; +-----------------------+ : Database : +-----------------------+ : dba : : db_test : : test : +-----------------------+

USE Database_Name; selects the database to run queries on.

Creating a database user

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; Creates a user with the credentials username and password at Localhost.

Granting privileges to a user at a database

GRANT ALL ON Database_name.* TO 'user'@'localhost'; Grants the user all the priviliges on all tables (*) of the Database_name.

Creating a database user and granting privileges at the same time

GRANT ALL PRIVILEGES ON Database_name.*TO'user'@'localhost'IDENTIFIED BY 'password';

example

mysql>GRANT ALL PRIVILEGES ON db_test.*TO'adamsmith'@'localhost'IDENTIFIED BY 'wealthofnations';