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!