MySql cheat sheet 🎉🎊🥳

Here’s a quick rundown of frequently used MySQL commands along with examples and explanations:

1. Connecting to a MySQL Server

mysql -u [username] -p  # MySQL will prompt for the password
mysql -u [username] -p [database]  # To connect to a specific database
mysql -h [hostname] -u [username] -p # To specify host
mysql -u username -p -h hostname -P port_number # To specify port
mysql --socket=/path/to/socket -u username -p # To Specify Socket File
mysql -u username -p database_name # Specific database

2. Creating and Displaying Databases

CREATE DATABASE database_name; # Creates a new database with the specified name
SHOW DATABASES; # Lists all databases on the MySQL server
USE database_name; # Selects a specific database to work with
DROP DATABASE database_name; # Deletes a database and all its tables permanently

3. Working with Tables:

CREATE TABLE [IF NOT EXISTS] table_name(column_list);  # Create a new table
ALTER TABLE table ADD [COLUMN] column_name;  # Add a new column to a table
ALTER TABLE table_name DROP [COLUMN] column_name;  # Drop a column from a table
ALTER TABLE table ADD INDEX name;  # Add an index with a specific name to a table on a column
ALTER TABLE table_name ADD PRIMARY KEY (column_name,...);  # Add primary key into a table
ALTER TABLE table_name DROP PRIMARY KEY;  # Remove the primary key of a table
DROP TABLE [IF EXISTS] table_name;  # Drop a table
DESCRIBE table_name;  # Show the columns of a table
DESCRIBE table_name column_name;  # Show the information of a column in a table

4. Select

SELECT * FROM table_name; -- Selects all columns from the specified table

SELECT column1, column2 FROM table_name; -- Selects specific columns from the specified table

SELECT column1 AS alias_name FROM table_name; -- Assigns an alias to the selected column

SELECT column1 + column2 AS calculation_result FROM table_name; -- Performs calculations on selected columns

SELECT * FROM table_name WHERE condition; -- Selects rows based on specified conditions

SELECT * FROM table_name ORDER BY column_name ASC/DESC; -- Orders the result set based on specified column(s)

SELECT * FROM table_name LIMIT num_rows; -- Limits the number of rows returned

SELECT * FROM table_name LIMIT num_rows OFFSET offset_value; -- Limits the number of rows returned with an offset

SELECT DISTINCT column_name FROM table_name; -- Selects unique/distinct values from the specified column

SELECT COUNT(*), AVG(column_name), SUM(column_name), MIN(column_name), MAX(column_name) FROM table_name; -- Performs aggregate functions on selected columns

SELECT column_name1, COUNT(*) FROM table_name GROUP BY column_name1; -- Groups the result set based on specified column

5. Joins

SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; -- INNER JOIN: Returns records that have matching values in both tables

SELECT * FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; -- LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and the matched records from the right table

SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; -- RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and the matched records from the left table

SELECT * FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name; -- FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table

SELECT * FROM table1 CROSS JOIN table2; -- CROSS JOIN: Returns the Cartesian product of the two tables, i.e., all possible combinations of rows

SELECT * FROM table1 t1 INNER JOIN table1 t2 ON t1.column_name = t2.column_name; -- SELF JOIN: Joins a table to itself, useful for hierarchical data or comparing rows within the same table

6. Insert

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); -- Inserts into a table with specified columns

INSERT INTO table_name VALUES (value1, value2, ...); -- Inserts into a table with all columns

INSERT INTO table_name (column1, column2, ...) VALUES
(value1_row1, value2_row1, ...),
(value1_row2, value2_row2, ...),
...; -- Inserts multiple rows into a table

INSERT INTO table_name (column1, column2, ...)
SELECT value1, value2, ...
FROM another_table
WHERE condition; -- Inserts from a SELECT statement

INSERT INTO table_name DEFAULT VALUES; -- Inserts with default values

7. Update

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; -- Updates specific columns in a table based on a condition

UPDATE table_name SET column1 = value1, column2 = value2; -- Updates all rows in a table with specified values for specific columns

UPDATE table_name SET column1 = column1 + 1; -- Updates values by performing calculations

UPDATE table_name SET column1 = (SELECT value FROM another_table WHERE condition); -- Updates values using a subquery

UPDATE table1 INNER JOIN table2 ON table1.column_name = table2.column_name SET table1.column1 = table2.column2; -- Updates values using a join

8. Delete

DELETE FROM table_name WHERE condition; -- Deletes specific rows from a table based on a condition

DELETE FROM table_name; -- Deletes all rows from a table

DELETE table1, table2 FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name WHERE condition; -- Deletes rows from multiple tables based on a condition

TRUNCATE TABLE table_name; -- Removes all rows from a table (cannot be rolled back)

9. Where

SELECT * FROM table_name WHERE condition; -- Selects rows from a table based on a condition

SELECT * FROM table_name WHERE column_name = value; -- Selects rows where the column equals a specific value

SELECT * FROM table_name WHERE column_name <> value; -- Selects rows where the column does not equal a specific value

SELECT * FROM table_name WHERE column_name > value; -- Selects rows where the column is greater than a specific value

SELECT * FROM table_name WHERE column_name < value; -- Selects rows where the column is less than a specific value

SELECT * FROM table_name WHERE column_name >= value; -- Selects rows where the column is greater than or equal to a specific value

SELECT * FROM table_name WHERE column_name <= value; -- Selects rows where the column is less than or equal to a specific value

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2; -- Selects rows where the column is within a range of values

SELECT * FROM table_name WHERE column_name IN (value1, value2, ...); -- Selects rows where the column matches any value in a list

SELECT * FROM table_name WHERE column_name NOT IN (value1, value2, ...); -- Selects rows where the column does not match any value in a list

SELECT * FROM table_name WHERE column_name LIKE 'pattern'; -- Selects rows where the column matches a specific pattern

SELECT * FROM table_name WHERE column_name IS NULL; -- Selects rows where the column is NULL

SELECT * FROM table_name WHERE column_name IS NOT NULL; -- Selects rows where the column is not NULL

SELECT * FROM table_name WHERE condition1 AND condition2; -- Selects rows where both conditions are true

SELECT * FROM table_name WHERE condition1 OR condition2; -- Selects rows where either condition is true

SELECT * FROM table_name WHERE NOT condition; -- Selects rows where the condition is not true

10. Export import

mysqldump -u username -p database_name > backup_file.sql # Backup a Single Database

mysqldump -u username -p --all-databases > backup_file.sql # Backup All Databases

mysqldump -u username -p database_name table_name > backup_file.sql # Backup a Single Table

mysql -u username -p database_name < backup_file.sql # Restore a Database

mysql -u username -p database_name < backup_file.sql # Restore a Single Table

mysql -u username -p --all-databases < backup_file.sql # Restore All Databases

11. Compress

mysqldump -u username -p database_name | gzip > backup_file.sql.gz # Compress Backup

ssh user@remote_host "mysqldump -u username -p database_name" > backup_file.sql # Backup Remote Database

mysqldump -u username -p database_name > backup_$(date +"%Y%m%d_%H%M%S").sql # Backup with Timestamp

12. Group by

SELECT column1, COUNT(*) AS count FROM table_name GROUP BY column1; -- Basic Grouping

SELECT column1, column2, COUNT(*) AS count FROM table_name GROUP BY column1, column2; -- Grouping with Multiple Columns

SELECT column1, SUM(column2) AS sum_column2 FROM table_name GROUP BY column1; -- Grouping with Aggregate Functions

SELECT column1, COUNT(*) AS count FROM table_name WHERE condition GROUP BY column1; -- Grouping with Filtering

SELECT column1, COUNT(*) AS count FROM table_name GROUP BY column1 HAVING count > 1; -- Grouping with HAVING Clause

SELECT column1, COUNT(*) AS count FROM table_name GROUP BY column1 ORDER BY count DESC; -- Grouping with ORDER BY

13. Searching

SELECT * FROM table_name WHERE column_name = 'value'; -- Basic Search

SELECT * FROM table_name WHERE column_name LIKE 'pattern%'; -- Search with Wildcards

SELECT * FROM table_name WHERE LOWER(column_name) = LOWER('value'); -- Search with Case-Insensitive

SELECT * FROM table_name WHERE condition1 AND condition2; -- Search with Multiple Conditions

SELECT * FROM table_name WHERE column_name LIKE '%pattern%'; -- Search with Partial Matching

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2; -- Search with Range

SELECT * FROM table_name WHERE column_name IS NULL; -- Search with NULL Values

SELECT * FROM table_name WHERE column_name IS NOT NULL; -- Search with NOT NULL Values

SELECT * FROM table_name WHERE column_name IN ('value1', 'value2', ...); -- Search with IN Operator

SELECT * FROM table_name WHERE column_name NOT IN ('value1', 'value2', ...); -- Search with NOT IN Operator

SELECT * FROM table_name WHERE condition ORDER BY column_name ASC/DESC; -- Search with ORDER BY

SELECT * FROM table_name WHERE condition LIMIT num_rows; -- Search with LIMIT

14. Data Type

-- Numeric Types:
INT(10)          -- Integer type with a display width of 10 digits
TINYINT(4)       -- Small integer type with a display width of 4 digits
SMALLINT(5)      -- Medium-sized integer type with a display width of 5 digits
MEDIUMINT(7)     -- Medium-sized integer type with a display width of 7 digits
BIGINT(20)       -- Large integer type with a display width of 20 digits
FLOAT(8,2)       -- Single-precision floating-point type with a total width of 8 digits, including 2 decimal places
DOUBLE(10,4)     -- Double-precision floating-point type with a total width of 10 digits, including 4 decimal places
DECIMAL(10,5)    -- Fixed-point type with a total width of 10 digits, including 5 decimal places

-- Date and Time Types:
DATE             -- Date value in 'YYYY-MM-DD' format
TIME             -- Time value in 'HH:MM:SS' format
DATETIME         -- Date and time value in 'YYYY-MM-DD HH:MM:SS' format
TIMESTAMP        -- Timestamp value, typically 'YYYY-MM-DD HH:MM:SS'
YEAR(4)          -- Year value in 4-digit format

-- String Types:
CHAR(50)         -- Fixed-length string type with a length of 50 characters
VARCHAR(255)     -- Variable-length string type with a maximum length of 255 characters
TEXT             -- Variable-length string with a maximum length
BINARY(10)       -- Fixed-length binary type with a length of 10 bytes
VARBINARY(20)    -- Variable-length binary type with a maximum length of 20 bytes
BLOB             -- Binary large object type
ENUM('A', 'B')   -- Enumeration type with allowed values 'A' or 'B'
SET('X', 'Y')    -- Set type with allowed values 'X', 'Y', or both

-- Other Types:
BOOL             -- Boolean type, often represented as 0 or 1
BOOLEAN          -- Alias for BOOL
BIT(8)           -- Bit field type with a length of 8 bits
JSON             -- JSON data type (added in MySQL 5.7.8)

15. Alter Table

ALTER TABLE table_name ADD column_name datatype; -- Add a new column

ALTER TABLE table_name ADD column_name datatype DEFAULT default_value; -- Add a new column with a default value

ALTER TABLE table_name ADD column1 datatype1, ADD column2 datatype2; -- Add multiple columns

ALTER TABLE table_name DROP COLUMN column_name; -- Drop a column

ALTER TABLE table_name MODIFY column_name new_datatype; -- Modify a column data type

ALTER TABLE table_name MODIFY column_name new_datatype constraint; -- Modify a column data type with a constraint

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name; -- Rename a column

ALTER TABLE table_name ADD INDEX index_name (column_name); -- Add an index

ALTER TABLE table_name DROP INDEX index_name; -- Drop an index

ALTER TABLE old_table_name RENAME TO new_table_name; -- Rename a table

16. View

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; -- Create a view
CREATE VIEW view_name (alias1, alias2, ...) AS SELECT column1 AS alias1, column2 AS alias2, ... FROM table_name WHERE condition; -- Create a view with aliases
CREATE VIEW view_name AS SELECT t1.column1, t2.column2, ... FROM table1 t1 JOIN table2 t2 ON t1.join_column = t2.join_column WHERE condition; -- Create a view from multiple tables
DROP VIEW view_name; -- Drop a view
SHOW CREATE VIEW view_name; -- Show the definition of a view
CREATE OR REPLACE VIEW view_name AS SELECT ...; -- Update a view (replaces the view definition)
INSERT INTO view_name (column1, column2, ...) VALUES (value1, value2, ...); -- Insert into a view (if updatable)
UPDATE view_name SET column1 = value1, column2 = value2 WHERE condition; -- Update a view (if updatable)
DELETE FROM view_name WHERE condition; -- Delete from a view (if updatable)

17. Trigger

-- Create a view:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- Create a view with aliases:
CREATE VIEW view_name (alias1, alias2, ...) AS
SELECT column1 AS alias1, column2 AS alias2, ...
FROM table_name
WHERE condition;

-- Create a view from multiple tables:
CREATE VIEW view_name AS
SELECT t1.column1, t2.column2, ...
FROM table1 t1
JOIN table2 t2 ON t1.join_column = t2.join_column
WHERE condition;

-- Drop a view:
DROP VIEW view_name;

-- Show the definition of a view:
SHOW CREATE VIEW view_name;

-- Update a view (replaces the view definition):
CREATE OR REPLACE VIEW view_name AS
SELECT ...

-- Insert into a view (if updatable):
INSERT INTO view_name (column1, column2, ...)
VALUES (value1, value2, ...);

-- Update a view (if updatable):
UPDATE view_name
SET column1 = value1, column2 = value2
WHERE condition;

-- Delete from a view (if updatable):
DELETE FROM view_name WHERE condition;

Conclusion:

In conclusion, this cheatsheet serves as a valuable resource for MySQL users, providing a concise compilation of essential commands and operations for efficient database management. By organizing commands into categories and providing clear descriptions and examples, the cheatsheet facilitates quick reference and easy comprehension, making it an indispensable tool for database administrators, developers, and anyone working with MySQL databases.

Whether you need to create databases, manipulate tables, query data, or manage user privileges, this cheatsheet has you covered with the necessary commands and usage guidelines. With this cheatsheet at your fingertips, you can streamline your MySQL workflow, troubleshoot common issues, and enhance your overall productivity in database management tasks.

If there are any additional items you’d like to include, please comment.💕💕💕