Cheat Sheet

1. Basic Commands

Connecting to MySQL

mysql -u username -p / mysql -u root -p

You'll be prompted to enter the password.

Show Databases

SHOW DATABASES;

Create a Database

CREATE DATABASE database_name;

Use a Database

USE database_name;

Show Tables

SHOW TABLES;

Describe a Table

DESCRIBE table_name;

DESC table_name;

Drop Database

DROP DATABASE database_name;

Drop Table

DROP TABLE table_name;

2. Data Types

Numeric Types

INT – Integer
DECIMAL(p, s) – Fixed-point
FLOAT – Floating-point
DOUBLE – Double precision

String Types

VARCHAR(n) – Variable-length string
CHAR(n) – Fixed-length string
TEXT – Large text
BLOB – Binary Large Object

Date and Time Types

DATE – 'YYYY-MM-DD'
DATETIME – 'YYYY-MM-DD HH:MM:SS'
TIMESTAMP – Automatic timestamp
TIME – 'HH:MM:SS'
YEAR – 'YYYY'

3. CRUD Operations

Create (INSERT)

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

Example:

INSERT INTO employees (first_name, last_name, age)
VALUES ('John', 'Doe', 30);

Read (SELECT)

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT number;

Examples:

Select all columns:
```
SELECT * FROM employees;
```

Select with condition:

SELECT first_name, last_name FROM employees WHERE age > 25;

Ordering and limiting:

SELECT * FROM employees ORDER BY last_name ASC LIMIT 10;

Update (UPDATE)

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

Example:

UPDATE employees
SET age = 31
WHERE first_name = 'John' AND last_name = 'Doe';

Delete (DELETE)

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM employees
WHERE age < 20;

4. Advanced Queries

JOINs

INNER JOIN

Returns records with matching values in both tables.

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

LEFT JOIN (LEFT OUTER JOIN)

Returns all records from the left table and matched records from the right table.

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

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all records from the right table and matched records from the left table.

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

FULL OUTER JOIN

MySQL doesn’t support FULL OUTER JOIN directly, but it can be simulated:

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

GROUP BY and HAVING

GROUP BY

Aggregates data based on one or more columns.

SELECT column, COUNT(*)
FROM table_name
GROUP BY column;

HAVING

Filters groups based on aggregate functions.

SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > 5;

ORDER BY

Sorts the result set.

SELECT columns
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

LIMIT

Restricts the number of rows returned.

SELECT columns
FROM table_name
LIMIT offset, count;

SELECT columns
FROM table_name
LIMIT count OFFSET offset;

5. Indexes

Create an Index

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Create a Unique Index

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

Drop an Index

DROP INDEX index_name
ON table_name;

View Indexes

SHOW INDEX FROM table_name;

6. Constraints

Primary Key

CREATE TABLE table_name (
    id INT NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

Foreign Key

CREATE TABLE child_table (
    id INT,
    parent_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);

Unique Constraint

CREATE TABLE table_name (
    id INT,
    email VARCHAR(100),
    UNIQUE (email)
);

Not Null Constraint

CREATE TABLE table_name (
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL
);

Default Value

CREATE TABLE table_name (
    id INT,
    status VARCHAR(20) DEFAULT 'active'
);

7. Functions

Aggregate Functions

COUNT(column)
SUM(column)
AVG(column)
MIN(column)
MAX(column)

Example:

SELECT COUNT(*), AVG(age)
FROM employees;

String Functions

CONCAT(str1, str2, ...) – Concatenates strings
SUBSTRING(str, pos, length) – Extracts a substring
LENGTH(str) – Returns the length of a string
UPPER(str) – Converts to uppercase
LOWER(str) – Converts to lowercase

Example:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

Date Functions

NOW() – Current date and time
CURDATE() – Current date
DATE_ADD(date, INTERVAL value unit) – Adds interval to date
DATEDIFF(date1, date2) – Difference in days

Example:

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week;

Numeric Functions

ROUND(number, decimals) – Rounds a number
CEIL(number) – Rounds up
FLOOR(number) – Rounds down

Example:

SELECT ROUND(AVG(age), 2) AS average_age
FROM employees;

8. Views

Create a View

CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;

Select from a View

SELECT * FROM view_name;

Drop a View

DROP VIEW view_name;

9. Transactions

Start a Transaction

START TRANSACTION;

BEGIN;

Commit a Transaction

COMMIT;

Rollback a Transaction

ROLLBACK;

10. User Management

Create a User

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

Grant Privileges

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

Or specific privileges:

GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'host';

Flush Privileges

FLUSH PRIVILEGES;

Revoke Privileges

REVOKE SELECT ON database_name.table_name FROM 'username'@'host';

Drop a User

DROP USER 'username'@'host';

11. Backup and Restore

Backup Database

mysqldump -u username -p database_name > backup.sql

Restore Database

mysql -u username -p database_name < backup.sql

12. Miscellaneous

Set Character Set

SET NAMES 'utf8mb4';

Change Password

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

Check Current Database

SELECT DATABASE();

Check Current User

SELECT USER();

13. Common Shortcuts

Select all columns:
```
SELECT * FROM table_name;
```

Insert multiple rows:

INSERT INTO table_name (column1, column2)
VALUES 
  (value1a, value2a),
  (value1b, value2b),
  ...;

Update with condition:

UPDATE table_name
SET column = value
WHERE condition;

Delete with condition:
```
DELETE FROM table_name
WHERE condition;
```

14. Performance Tips

Use Indexes: Improve query performance by indexing columns used in WHERE, JOIN, and ORDER BY clauses.
*Avoid SELECT : Specify only the columns you need.

Use EXPLAIN: Analyze query execution plans.

EXPLAIN SELECT * FROM table_name WHERE condition;

Optimize Joins: Ensure joined columns are indexed.
Limit Results: Use LIMIT to reduce the amount of data processed.

15. Security Best Practices

Use Strong Passwords: Ensure all MySQL users have strong, unique passwords.
Least Privilege Principle: Grant only necessary permissions to users.
Regular Backups: Keep regular backups and store them securely.
Keep MySQL Updated: Regularly update to the latest stable version to patch vulnerabilities.
Use SSL/TLS: Encrypt connections between MySQL and clients.

PreviousModuleNotFoundError: No module named 'pip'NextDjango-summernote

Last updated 5 months ago