MySQL cheat sheet to help you with the most common tasks

Basic Commands

Connect to MySQL:

  mysql -u username -p

Show all databases:

  SHOW DATABASES;

Use a database:

  USE database_name;

Show all tables in a database:

  SHOW TABLES;

Show table structure:

  DESCRIBE table_name;

Data Types

String Types:
- CHAR(size)
- VARCHAR(size)
- TEXT
Numeric Types:
- INT(size)
- FLOAT(size, d)
- DOUBLE(size, d)
Date and Time Types:
- DATE
- DATETIME
- TIMESTAMP
- TIME

Table Management

Create a table:

  CREATE TABLE table_name (
      column1 datatype,
      column2 datatype,
      column3 datatype,
      PRIMARY KEY (column1)
  );

Drop a table:

  DROP TABLE table_name;

Alter a table:

  ALTER TABLE table_name
  ADD column_name datatype;

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;

Queries

Select data:

  SELECT column1, column2
  FROM table_name
  WHERE condition;

Select all data:

  SELECT * FROM table_name;

Order by:

  SELECT column1, column2
  FROM table_name
  ORDER BY column1 ASC | DESC;

Group by:

  SELECT column1, COUNT(*)
  FROM table_name
  GROUP BY column1;

Joins

Inner join:

  SELECT a.column1, b.column2
  FROM table1 a
  INNER JOIN table2 b ON a.common_column = b.common_column;

Left join:

  SELECT a.column1, b.column2
  FROM table1 a
  LEFT JOIN table2 b ON a.common_column = b.common_column;

Right join:

  SELECT a.column1, b.column2
  FROM table1 a
  RIGHT JOIN table2 b ON a.common_column = b.common_column;

Indexes

Create an index:

  CREATE INDEX index_name
  ON table_name (column1, column2);

Drop an index:

  DROP INDEX index_name ON table_name;

Backup and Restore

Backup a database:

  mysqldump -u username -p database_name > backup.sql

Restore a database:

  mysql -u username -p database_name < backup.sql

User Management

Create a user:

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

Grant privileges:

  GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

Flush privileges:

  FLUSH PRIVILEGES;

Drop a user:

  DROP USER 'username'@'host';

Feel free to ask if you need more details on any specific topic!

Blog

MySQL cheat sheet to help you with the most common tasks

Abdul Haseeb

Basic Commands

Data Types

Table Management

Data Manipulation

Queries

Joins

Indexes

Backup and Restore

User Management

Join Our Newsletter. No Spam, Only the good stuff.

Related