Skip to main content

MySQL

Introduction​

This cheat sheet provides a quick reference for some common MySQL commands and concepts. MySQL is an open-source relational database management system (RDBMS) commonly used for storing and managing data.

Installation​

To use MySQL, you need to install it on your system. Installation methods vary depending on your operating system. Refer to the official MySQL documentation for installation instructions.

MySQL Concepts​

Databases​

  • Create a new database:

    CREATE DATABASE database_name;

  • List all databases:

    SHOW DATABASES;

  • Switch to a specific database:

    USE database_name;

  • Delete a database (Be cautious, this will remove all data):

    DROP DATABASE database_name;

Tables​

  • Create a new table:

    CREATE TABLE table_name (
      column1 datatype,
      column2 datatype,
      ...
    );

  • List all tables in the current database:

    SHOW TABLES;

  • Describe the structure of a table:

    DESCRIBE table_name;

  • Delete a table:

    DROP TABLE table_name;

CRUD Operations​

  • Insert a new record into a table:

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

  • Select data from a table:

    SELECT * FROM table_name WHERE condition;

  • Update data in a table:

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

  • Delete data from a table:

    DELETE FROM table_name WHERE condition;

Indexes​

  • Create an index on a table column:

    CREATE INDEX index_name ON table_name (column_name);

  • List all indexes on a table:

    SHOW INDEXES FROM table_name;

Joins​

  • Perform an INNER JOIN between two tables:
    SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

Functions​

  • Use built-in MySQL functions:
    SELECT COUNT(*) FROM table_name;

Users and Permissions​

  • Create a new MySQL user:

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

  • Grant privileges to a user:

    GRANT privilege_type ON database_name.table_name TO 'username'@'hostname';

  • Revoke privileges from a user:

    REVOKE privilege_type ON database_name.table_name FROM 'username'@'hostname';

MySQL Command-Line Tool​

  • Log in to MySQL using the command-line tool:

    mysql -u username -p

  • Execute SQL commands in batch mode:

    mysql -u username -p < script.sql

Conclusion​

This cheat sheet covers some common MySQL commands and concepts. MySQL offers a wide range of features and functionality; refer to the MySQL documentation for more in-depth information and advanced usage.