MySQL Cheat Sheet

MySQL is one of the most popular relational database management systems (RDBMS) in the world. Whether you’re just starting out or you’re a seasoned developer, this cheat sheet will help you navigate MySQL more efficiently. This guide covers a broad spectrum of MySQL commands, functions, and tips that are essential for day-to-day database management.

Basic MySQL Commands

Create a Database

CREATE DATABASE db_name;

Select a Database

USE db_name;

Show Databases

SHOW DATABASES;

Drop a Database

DROP DATABASE db_name;

Show Tables in a Database

SHOW TABLES;

Describe the Structure of a Table

DESCRIBE tableName;

Table Operations

Create a Table

CREATE TABLE tableName (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    email VARCHAR(100)
);

Show the Table Schema

SHOW CREATE TABLE tableName;

Drop a Table

DROP TABLE tableName;

Rename a Table

RENAME TABLE old_tableName TO newTableName;

Alter a Table

• Add a column:

  ALTER TABLE tableName ADD COLUMN columnName data_type;
  

• Rename a column:

  ALTER TABLE tableName RENAME COLUMN oldcolumnName TO newcolumnName;
  

• Modify a column:

  ALTER TABLE tableName MODIFY COLUMN columnName new_data_type;
  

• Drop a column:

  ALTER TABLE tableName DROP COLUMN columnName;
  

Truncate a Table

TRUNCATE TABLE tableName;

CRUD Operations

create, read, update and delete.

Insert Data

INSERT INTO tableName (column1, column2, column3) VALUES (value1, value2, value3);

Insert Multiple Rows

INSERT INTO tableName (column1, column2) VALUES 
(value1_1, value2_1),
(value1_2, value2_2),
(value1_3, value2_3);

Select Data

• Select all columns:

  SELECT * FROM tableName;
  

• Select specific columns:

  SELECT column1, column2 FROM tableName;
  

Update Data

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

Delete Data

DELETE FROM tableName WHERE condition;

Querying Data

WHERE Clause

SELECT * FROM tableName WHERE columnName = value;

AND, OR, NOT

SELECT * FROM tableName WHERE column1 = value1 AND (column2 = value2 OR column3 = value3);

ORDER BY Clause

SELECT * FROM tableName ORDER BY columnName ASC;
SELECT * FROM tableName ORDER BY columnName DESC;

LIMIT Clause

SELECT * FROM tableName LIMIT number_of_rows;

BETWEEN Clause

SELECT * FROM tableName WHERE columnName BETWEEN value1 AND value2;

LIKE and Wildcards

• Find values that start with ‘a’:

  SELECT * FROM tableName WHERE columnName LIKE 'a%';
  

• Find values that end with ‘a’:

  SELECT * FROM tableName WHERE columnName LIKE '%a';
  

• Find values that contain ‘a’:

  SELECT * FROM tableName WHERE columnName LIKE '%a%';
  

IN Clause

SELECT * FROM tableName WHERE columnName IN (value1, value2, value3);

DISTINCT Clause

SELECT DISTINCT columnName FROM tableName;

Advanced MySQL Queries

JOINS

• Inner Join:

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

• Left Join:

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

• Right Join:

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

GROUP BY

SELECT columnName, COUNT(*) FROM tableName
GROUP BY columnName;

HAVING Clause

SELECT columnName, COUNT(*) FROM tableName
GROUP BY columnName
HAVING COUNT(*) > 1;

Subqueries

• Subquery in SELECT:

  SELECT columnName, (SELECT COUNT(*) FROM another_table WHERE condition) 
  FROM tableName;
  

• Subquery in WHERE:

  SELECT columnName FROM tableName
  WHERE columnName IN (SELECT columnName FROM another_table WHERE condition);
  

UNION

SELECT columnName FROM table1
UNION
SELECT columnName FROM table2;

Indexes and Keys

Create an Index

CREATE INDEX index_name ON tableName (columnName);

Drop an Index

DROP INDEX index_name ON tableName;

Unique Index

CREATE UNIQUE INDEX index_name ON tableName (columnName);

Primary Key

ALTER TABLE tableName ADD PRIMARY KEY (columnName);

Foreign Key

ALTER TABLE tableName
ADD CONSTRAINT fk_name FOREIGN KEY (columnName) REFERENCES other_table(columnName);

Transactions

Start a Transaction

START TRANSACTION;

Commit a Transaction

COMMIT;

Rollback a Transaction

ROLLBACK;

Savepoints

SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;

User Management and Security

Create a User

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

Grant Privileges

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

Show Grants

SHOW GRANTS FOR 'username'@'host';

Revoke Privileges

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

Drop a User

DROP USER 'username'@'host';

Change User Password

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

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

Backup Specific Tables

mysqldump -u username -p database_name table1 table2 > backup.sql

Backup All Databases

mysqldump -u username -p --all-databases > all_databases_backup.sql

Optimization and Maintenance

Check Table for Errors

CHECK TABLE tableName;

Repair a Table

REPAIR TABLE tableName;

Optimize a Table

OPTIMIZE TABLE tableName;

Analyze a Table

ANALYZE TABLE tableName;

Viewing Server Status

SHOW STATUS;

Show Current Users

SELECT user, host FROM mysql.user;

MySQL Functions

String Functions

• Concatenate Strings:

  SELECT CONCAT('Hello', ' ', 'World!');
  

• Length of a String:

  SELECT LENGTH('Hello World!');
  

• Substring:

  SELECT SUBSTRING('Hello World!', 1, 5);
  

Date Functions

• Current Date:

  SELECT CURDATE();
  

• Current Time:

  SELECT CURTIME();
  

• Add Date:

  SELECT DATE_ADD('2024-08-10', INTERVAL 10 DAY);
  

Aggregate Functions

• COUNT:

  SELECT COUNT(*) FROM tableName;
  

• SUM:

  SELECT SUM(columnName)
  

  Continuing from where we left off:

SELECT SUM(columnName) FROM tableName;

• AVG:

  SELECT AVG(columnName) FROM tableName;
  

• MIN:

  SELECT MIN(columnName) FROM tableName;
  

• MAX:

  SELECT MAX(columnName) FROM tableName;
  

Stored Procedures and Functions

Creating a Stored Procedure

DELIMITER //
CREATE PROCEDURE procedure_name (IN param1 datatype, OUT param2 datatype)
BEGIN
    -- SQL statements
    SELECT columnName INTO param2 FROM tableName WHERE condition;
END //
DELIMITER ;

Executing a Stored Procedure

CALL procedure_name(param1, @param2);
SELECT @param2;

Dropping a Stored Procedure

DROP PROCEDURE procedure_name;

Creating a Stored Function

DELIMITER //
CREATE FUNCTION function_name (param1 datatype) RETURNS datatype
BEGIN
    -- SQL statements
    RETURN expression;
END //
DELIMITER ;

Dropping a Stored Function

DROP FUNCTION function_name;

Triggers

Creating a Trigger

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tableName FOR EACH ROW
BEGIN
    -- SQL statements
END;

Dropping a Trigger

DROP TRIGGER trigger_name;

Views

Creating a View

CREATE VIEW view_name AS
SELECT columns FROM tableName WHERE condition;

Selecting from a View

SELECT * FROM view_name;

Dropping a View

DROP VIEW view_name;

Replication and High Availability

Setting Up Master-Slave Replication

1. On the Master:

   • Edit the MySQL configuration file (usually my.cnf or my.ini):

     [mysqld]
     server-id=1
     log-bin=mysql-bin
     

   • Restart the MySQL server:

     sudo systemctl restart mysql
     

   • Grant Replication Privileges:

     GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%' IDENTIFIED BY 'password';
     FLUSH PRIVILEGES;
     

   • Get the binary log file and position:

     SHOW MASTER STATUS;
     

2. On the Slave:

   • Edit the MySQL configuration file:

     [mysqld]
     server-id=2
     

   • Restart the MySQL server:

     sudo systemctl restart mysql
     

   • Configure the Slave:

     CHANGE MASTER TO
     MASTER_HOST='master_ip',
     MASTER_USER='replica_user',
     MASTER_PASSWORD='password',
     MASTER_LOG_FILE='mysql-bin.000001',
     MASTER_LOG_POS=12345;
     

   • Start the Slave:

     START SLAVE;
     

Checking Replication Status

SHOW SLAVE STATUS\G;

Stopping and Starting Replication

• Stop the Slave:

  STOP SLAVE;
  

• Start the Slave:

  START SLAVE;
  

MySQL Best Practices

Use Consistent Naming Conventions

• Stick to lowercase letters, underscores for spaces, and singular names for tables (e.g., user, order_item).

Normalize Your Database

• Ensure your database is normalized to at least the third normal form (3NF) to reduce redundancy and improve data integrity.

Backup Regularly

• Set up automated backups and always test your backup recovery process.

Monitor Performance

• Use tools like mysqltuner to monitor and optimize the performance of MySQL database.

Secure Your MySQL Installation

• Run mysql_secure_installation after setting up MySQL to enhance security.

Index Appropriately

• Index columns that are frequently used in WHERE, JOIN, and ORDER BY clauses, but avoid over-indexing as it can slow down INSERT, UPDATE, and DELETE operations.

Regularly Update MySQL

• Keep MySQL version up to date with the latest security patches and performance improvements.

Conclusion

This MySQL cheat sheet provides a solid foundation for managing databases efficiently. Whether you’re working on a small project or a large-scale application, understanding these commands, functions, and best practices will help you get the most out of MySQL. Keep this guide handy as you develop and maintain your databases to ensure smooth and effective database management.