MySQL Cheat Sheet - Quick Reference Guide

Basics

• Start MySQL server: sudo service mysql start
• Stop MySQL server: sudo service mysql stop
• Connect to MySQL: mysql -u [username] -p

Database Operations

• Show databases: SHOW DATABASES;
• Create database: CREATE DATABASE [database_name];
• Select/Use database: USE [database_name];
• Drop database: DROP DATABASE [database_name];

Table Operations

• Show tables: SHOW TABLES;
• Create table:

  CREATE TABLE [table_name] (
      column1 datatype constraints,
      column2 datatype constraints,
      ...
  );

• Describe table: DESCRIBE [table_name];
• Drop table: DROP TABLE [table_name];
• Alter table (add column): ALTER TABLE [table_name] ADD COLUMN [column_name] datatype constraints;
• Alter table (drop column): ALTER TABLE [table_name] DROP COLUMN [column_name];
• Alter table (rename column): ALTER TABLE [table_name] CHANGE [old_column_name] [new_column_name] datatype constraints;

Data Manipulation

• Insert data:

  INSERT INTO [table_name] (column1, column2, ...)
  VALUES (value1, value2, ...);

• Update data:

  UPDATE [table_name]
  SET column1 = value1, column2 = value2, ...
  WHERE condition;

• Delete data:

  DELETE FROM [table_name]
  WHERE condition;

• Select data:

  SELECT column1, column2, ...
  FROM [table_name]
  WHERE condition
  GROUP BY column_name
  HAVING condition
  ORDER BY column_name ASC|DESC
  LIMIT number;

Joining Tables

• INNER JOIN:

  SELECT columns
  FROM table1
  INNER JOIN table2 ON table1.column = table2.column
  WHERE condition;

• LEFT JOIN:

  SELECT columns
  FROM table1
  LEFT JOIN table2 ON table1.column = table2.column
  WHERE condition;

• RIGHT JOIN:

  SELECT columns
  FROM table1
  RIGHT JOIN table2 ON table1.column = table2.column
  WHERE condition;

• FULL OUTER JOIN (MySQL doesn't support FULL OUTER JOIN, but you can achieve the same result using UNION):

  SELECT columns
  FROM table1
  LEFT JOIN table2 ON table1.column = table2.column
  WHERE condition
  UNION
  SELECT columns
  FROM table1
  RIGHT JOIN table2 ON table1.column = table2.column
  WHERE condition;

Indexes

• Create index: CREATE INDEX [index_name] ON [table_name] (column_name);
• Drop index: ALTER TABLE [table_name] DROP INDEX [index_name];

User Management

• Create user: CREATE USER '[username]'@'localhost' IDENTIFIED BY '[password]';
• Grant privileges: GRANT [privileges] ON [database_name].[table_name] TO '[username]'@'localhost';
• Revoke privileges: REVOKE [privileges] ON [database_name].[table_name] FROM '[username]'@'localhost';
• Show user privileges: SHOW GRANTS FOR '[username]'@'localhost';
• Delete user: DROP USER '[username]'@'localhost';

Backup and Restore

• Backup (mysqldump): mysqldump -u [username] -p[password] [database_name] > backup.sql
• Restore (mysql): mysql -u [username] -p[password] [database_name] < backup.sql
• Backup a single table: mysqldump -u [username] -p[password] [database_name] [table_name] > backup_table.sql
• Restore a single table: mysql -u [username] -p[password] [database_name] < backup_table.sql

References

• MySQL :: MySQL Documentation