MySQL Database Cheat Sheet

Basic Commands

Login

mysql -u root -p

Create Users

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

Drop User

DROP USER 'username'@'localhost';

Show Users

SELECT User, Host FROM mysql.user;

Show Grants

SHOW GRANTS FOR 'username'@'localhost';

Grant All Priveleges On All Databases

GRANT ALL PRIVILEGES ON * . * TO 'someuser'@'localhost';
FLUSH PRIVILEGES;

Remove Grants

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'localhost';

Exit

exit;

Database Operations

Show Database

SHOW DATABASES

Create Database

CREATE DATABASE testDB;

Select Database

USE testDB;

Delete Database

DROP DATABASE testDB;

Table Operations

Show Tables

SHOW TABLES;

Create Table

CREATE TABLE testTable(
 id INT AUTO_INCREMENT,
 name VARCHAR(20),
 email VARCHAR(20),
 password VARCHAR(20),
 student TINYINT(1),
 date DATETIME,
 PRIMARY KEY(id)
);

Drop/Delete Table

DROP TABLE tablename;