MySQL Cheatsheet

Cheatsheet Content

MySQL Basics What is MySQL? An open-source relational database management system (RDBMS). Client-Server Model: MySQL server manages databases; clients connect to perform operations. SQL: Structured Query Language, used to communicate with the database. SQL Command Types DDL (Data Definition Language): Defines database structure. CREATE : Create databases, tables, views, etc. ALTER : Modify existing database objects. DROP : Delete database objects. TRUNCATE : Remove all records from a table, but not the table structure. DML (Data Manipulation Language): Manages data within objects. SELECT : Retrieve data. INSERT : Add new data. UPDATE : Modify existing data. DELETE : Remove data. DCL (Data Control Language): Manages permissions. GRANT : Give user access privileges. REVOKE : Remove user access privileges. TCL (Transaction Control Language): Manages transactions. COMMIT : Save changes. ROLLBACK : Undo changes. SAVEPOINT : Set a point within a transaction to which you can later roll back. Database & Table Management (DDL) Database Operations Create Database: CREATE DATABASE database_name; Use Database: USE database_name; Drop Database: DROP DATABASE database_name; Show Databases: SHOW DATABASES; Table Operations Create Table: CREATE TABLE table_name ( column1 datatype CONSTRAINTS, column2 datatype CONSTRAINTS, ... PRIMARY KEY (column_name) ); Common Data Types: INT : Integer VARCHAR(size) : Variable-length string TEXT : Long text data DATE : Date (YYYY-MM-DD) DATETIME : Date and time (YYYY-MM-DD HH:MI:SS) BOOLEAN / TINYINT(1) : True/False DECIMAL(P,S) : Exact numeric value Common Constraints: NOT NULL : Column cannot store NULL values. UNIQUE : All values in a column are different. PRIMARY KEY : Unique identifier for each record (NOT NULL + UNIQUE). FOREIGN KEY : Links two tables. DEFAULT value : Sets a default value for a column. AUTO_INCREMENT : Automatically increments value for new records. Alter Table: Add Column: ALTER TABLE table_name ADD column_name datatype; Drop Column: ALTER TABLE table_name DROP COLUMN column_name; Modify Column: ALTER TABLE table_name MODIFY COLUMN column_name new_datatype; Rename Table: ALTER TABLE old_table_name RENAME TO new_table_name; Drop Table: DROP TABLE table_name; Truncate Table: TRUNCATE TABLE table_name; Show Tables: SHOW TABLES; Describe Table: DESCRIBE table_name; (or DESC table_name; ) Data Manipulation (DML) SELECT Statement Select All Columns: SELECT * FROM table_name; Select Specific Columns: SELECT column1, column2 FROM table_name; Distinct Values: SELECT DISTINCT column_name FROM table_name; Alias Columns/Tables: SELECT column_name AS alias_name FROM table_name AS t; WHERE Clause (Filtering): SELECT * FROM table_name WHERE condition; Operators: $=, <>, , =, BETWEEN, LIKE, IN, IS NULL, AND, OR, NOT$ Examples: WHERE age > 18 WHERE city = 'New York' WHERE name LIKE 'A%' (starts with A) WHERE name LIKE '%a' (ends with a) WHERE name LIKE '%app%' (contains app) WHERE id IN (1, 3, 5) WHERE date BETWEEN '2023-01-01' AND '2023-12-31' WHERE email IS NULL ORDER BY (Sorting): SELECT * FROM table_name ORDER BY column_name [ASC|DESC]; LIMIT (Pagination): SELECT * FROM table_name LIMIT count; (or LIMIT offset, count; ) Aggregate Functions: COUNT(column_name) : Number of rows. SUM(column_name) : Sum of values. AVG(column_name) : Average value. MIN(column_name) : Minimum value. MAX(column_name) : Maximum value. GROUP BY: Groups rows that have the same values into summary rows. SELECT column1, COUNT(*) FROM table_name GROUP BY column1; HAVING Clause: Filters groups based on aggregate functions. SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 5; INSERT Statement Insert All Columns: INSERT INTO table_name VALUES (value1, value2, ...); Insert Specific Columns: INSERT INTO table_name (column1, column2) VALUES (value1, value2); Insert Multiple Rows: INSERT INTO table_name VALUES (val1a, val1b), (val2a, val2b); UPDATE Statement Update Data: UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; DELETE Statement Delete Data: DELETE FROM table_name WHERE condition; Delete All Data: DELETE FROM table_name; (Careful, this removes all rows!) Joins Used to combine rows from two or more tables based on a related column. INNER JOIN: Returns rows when there is a match in both tables. SELECT T1.col, T2.col FROM Table1 T1 INNER JOIN Table2 T2 ON T1.id = T2.fk_id; LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table, and the matching rows from the right table. If no match, NULLs for right table columns. SELECT T1.col, T2.col FROM Table1 T1 LEFT JOIN Table2 T2 ON T1.id = T2.fk_id; RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table, and the matching rows from the left table. If no match, NULLs for left table columns. SELECT T1.col, T2.col FROM Table1 T1 RIGHT JOIN Table2 T2 ON T1.id = T2.fk_id; FULL JOIN (FULL OUTER JOIN): Returns rows when there is a match in one of the tables. (MySQL simulates this using UNION of LEFT and RIGHT JOINs). SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.id = Table2.fk_id UNION SELECT * FROM Table1 RIGHT JOIN Table2 ON Table1.id = Table2.fk_id WHERE Table1.id IS NULL; SELF JOIN: A table joined to itself. SELECT A.column_name, B.column_name FROM Table1 A, Table1 B WHERE A.some_column = B.other_column; Indexes Used to retrieve data from the database more quickly. Create Index: CREATE INDEX index_name ON table_name (column_name); Create Unique Index: CREATE UNIQUE INDEX index_name ON table_name (column_name); Drop Index: DROP INDEX index_name ON table_name; Views A virtual table based on the result-set of an SQL query. Create View: CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition; Drop View: DROP VIEW view_name; Transactions (TCL) Ensures data integrity (ACID properties). Start Transaction: START TRANSACTION; or BEGIN; Commit: COMMIT; (Saves changes permanently) Rollback: ROLLBACK; (Undoes all changes since last COMMIT/BEGIN) Savepoint: SAVEPOINT savepoint_name; ROLLBACK TO savepoint_name; User Management (DCL) Create User: CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; Grant Privileges: GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost'; Common privileges: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER Revoke Privileges: REVOKE SELECT ON database_name.* FROM 'username'@'localhost'; Flush Privileges: FLUSH PRIVILEGES; (Reloads the grant tables) Drop User: DROP USER 'username'@'localhost'; Flow Diagram: Basic MySQL Interaction START Connect to MySQL Server Select/Use Database Input SQL Query (DDL/DML/DCL/TCL) MySQL Server Processes Query Is Query DML (SELECT)? Yes Return Result Set No Return Status/Affected Rows END