MySQL: Quick Reference

Introduction:

MySQL cheat sheet is a one-page document that contains the most regularly used MySQL commands and statements to help you operate with MySQL.

MySQL CLI Commands

To create user and grant privilege

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGE ON database.* TO 'username'@'host';
mysql> FLUSH PRIVILEGES;

To connect MySQL server using mysql command-line client with a username and password

mysql -u [username] -p;

With a specified database

mysql -u [username] -p [database];

To exit MySQL CLI

musql> exit;

Export data using mysqldump

mysqldump -u [username] -p [database] > data_backup.sql;

Import data to database

mysql -u [username] -p [database] < data_backup.sql;

Working with database

To Create database

mysql> CREATE DATABASE [IF NOT EXISTS] database_name;

To Change database

mysql> USE database_name;

To Delete database

mysql> DROP DATABASE [IF EXISTS] database_name;

To List all databases

mysql> SHOW DATABASE;

Working with tables

Show all tables in a selected database.

mysql> SHOW TABLES;

To create new table

mysql> CREATE TABLE [IF NOT EXISTS] table_name(column_list);

To add a new column into a table

mysql> ALTER TABLE table ADD [COLUMN] column_name;

To add primary key into a table

mysql> ALTER TABLE table_name ADD PRIMARY KEY (column_name,...);

To remove the primary key of a table:

mysql> ALTER TABLE table_name DROP PRIMARY KEY;

To drop a table

mysql> DROP TABLE [IF EXISTS] table_name;

To show the columns of a table

mysql> DESCRIBE table_name;