MySQL cheat sheet to help you with the most common tasks

This content originally appeared on DEV Community and was authored by Abdul Haseeb

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!

This content originally appeared on DEV Community and was authored by Abdul Haseeb

Print Share Comment Cite Upload Translate Updates

APA

Abdul Haseeb | Sciencx (2024-08-07T05:33:02+00:00) MySQL cheat sheet to help you with the most common tasks. Retrieved from https://www.scien.cx/2024/08/07/mysql-cheat-sheet-to-help-you-with-the-most-common-tasks/

MLA

" » MySQL cheat sheet to help you with the most common tasks." Abdul Haseeb | Sciencx - Wednesday August 7, 2024, https://www.scien.cx/2024/08/07/mysql-cheat-sheet-to-help-you-with-the-most-common-tasks/

HARVARD

Abdul Haseeb | Sciencx Wednesday August 7, 2024 » MySQL cheat sheet to help you with the most common tasks., viewed ,<https://www.scien.cx/2024/08/07/mysql-cheat-sheet-to-help-you-with-the-most-common-tasks/>

VANCOUVER

Abdul Haseeb | Sciencx - » MySQL cheat sheet to help you with the most common tasks. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2024/08/07/mysql-cheat-sheet-to-help-you-with-the-most-common-tasks/

CHICAGO

" » MySQL cheat sheet to help you with the most common tasks." Abdul Haseeb | Sciencx - Accessed . https://www.scien.cx/2024/08/07/mysql-cheat-sheet-to-help-you-with-the-most-common-tasks/

IEEE

" » MySQL cheat sheet to help you with the most common tasks." Abdul Haseeb | Sciencx [Online]. Available: https://www.scien.cx/2024/08/07/mysql-cheat-sheet-to-help-you-with-the-most-common-tasks/. [Accessed: ]

rf:citation

» MySQL cheat sheet to help you with the most common tasks | Abdul Haseeb | Sciencx | https://www.scien.cx/2024/08/07/mysql-cheat-sheet-to-help-you-with-the-most-common-tasks/ |

Please log in to upload a file.

There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.