1.1 Database and Table Operations

Data Definition Language is used for defining database structures

1.1.1 Database Operations

• Display all databases: SHOW DATABASES;
• Display database creation details: SHOW CREATE DATABASE database_name;
• Create a database: CREATE DATABASE database_name;
• Create database with existence check: CREATE DATABASE IF NOT EXISTS database_name;
• Create database with specific character set: CREATE DATABASE database_name CHARACTER SET gbk;
• Modify database character set: ALTER DATABASE database_name CHARACTER SET utf8;
• Delete a database: DROP DATABASE database_name; (or DROP DATABASE IF EXISTS database_name;)
• Switch to a database: USE database_name;
• Check current database: SELECT DATABASE();

1.1.2 Table Operations

CREATE TABLE products (
  product_id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(50) NOT NULL,
  category VARCHAR(20) NOT NULL,
  price DECIMAL(10,2) NOT NULL
) AUTO_INCREMENT=1000;

• Display all tables in a database: SHOW TABLES;
• Display table structure: DESC table_name;
• Create a table: CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
• Copy table structure: CREATE TABLE new_table LIKE original_table;
• Clear table data: DELETE FROM table_name;
• Delete a table: DROP TABLE IF EXISTS table_name;

1.1.3 Column Operations

• Rename table: ALTER TABLE table_name RENAME TO new_table_name;
• Display table creation details: SHOW CREATE TABLE table_name;
• Modify table character set: ALTER TABLE table_name CHARACTER SET charset_name;
• Add a column: ALTER TABLE table_name ADD column_name datatype;
• Change column name: ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;
• Modify column datatype: ALTER TABLE table_name MODIFY column_name new_datatype;
• Delete a column: ALTER TABLE table_name DROP column_name;

1.2 Data Manipulation Language (DML)

DML is used for inserting, updating, and deleting data

• Insert data: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
• Delete data with condition: DELETE FROM table_name WHERE condition;
• Delete all records: DELETE FROM table_name;
• Truncate table (more efficient): TRUNCATE TABLE table_name;
• Update data: UPDATE table_name SET column1=value1, column2=value2 WHERE condition;

2. Data Query Language (DQL)

2.1 Basic Query Syntax

SELECT column1, column2, ...
FROM table1, table2, ...
WHERE condition1, condition2, ...
GROUP BY grouping_column
HAVING grouping_condition
ORDER BY sort_column
LIMIT offset, row_count;

2.2 Basic Query Statements

• Query all information: SELECT * FROM table_name;
• Remove duplicates: SELECT DISTINCT column_name FROM table_name;
• Range query (inclusive): SELECT * FROM students WHERE age BETWEEN 20 AND 30;
• Column alias: SELECT column_name AS new_column_name FROM table_name WHERE condition;
• NULL handling: SELECT * FROM students WHERE id IS NULL; or IS NOT NULL
• Pattern matching: SELECT * FROM table_name WHERE column_name LIKE pattern; (% for multiple chars, _ for single char)
• Sorting: SELECT * FROM students ORDER BY math DESC, english ASC;

2.3 Aggregate Functions

• Count function: SELECT COUNT(IFNULL(name,0)) FROM students;
• Count all rows: SELECT COUNT(*) FROM students;
• Max/Min/Sum/Avg: SELECT MAX(math) FROM students;
• String case conversion: LOWER(string) or UPPER(string)
• Current date: CURDATE();
• Current time: CURTIME();
• Current datetime: NOW();

Grouping

Basic syntax: SELECT column_list FROM table_name WHERE conditions GROUP BY grouping_column;

Example: SELECT gender, AVG(math_score) FROM students GROUP BY gender;

Pagination

SELECT * FROM students LIMIT 3; (returns 3 rows)

WHERE vs HAVING

WHERE filters before grouping, HAVING filters after grouping. WHERE cannot use aggregate functions, HAVING can.

2.4 Join Operations

-- Implicit inner join
SELECT * FROM employees, departments WHERE departments.id = employees.dept_id;

-- Explicit inner join
SELECT * FROM employees INNER JOIN departments ON employees.dept_id = departments.id;

Outer Joins

Outer joins include all rows from one table and matching rows from the other, filling NULL for non-matches.

• Left outer join: SELECT e.*, d.name FROM employees AS e LEFT JOIN departments AS d ON e.dept_id = d.id;
• Right outer join: SELECT e.*, d.name FROM employees AS e RIGHT JOIN departments AS d ON e.dept_id = d.id;

3. Data Control Language (DCL)

• Create user: CREATE USER 'username'@'host' IDENTIFIED BY 'password';
• Delete user: DROP USER 'username'@'host';
• Change password: SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');
• Check user privileges: SHOW GRANTS FOR 'username'@'host';
• Grant privileges: GRANT privilege_list ON database.table TO 'username'@'host';
• Grant all privileges: GRANT ALL ON *.* TO 'username'@'host';
• Revoke privileges: REVOKE privilege_list ON database.table FROM 'username'@'host';

4. Constraints

Constraints ensure data integrity and validity

• Primary key (special): ALTER TABLE table_name DROP PRIMARY KEY;
• NOT NULL constraint: ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
• Unique constraint: ALTER TABLE table_name DROP INDEX column_name;
• Auto-increment: id INT PRIMARY KEY AUTO_INCREMENT
• Remove auto-increment: ALTER TABLE students MODIFY id INT;
• Foreign key: CONSTRAINT fk_name FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_column)

5. Transactions

Transactions ensure that multiple operations either all succeed or all fail

• Start transaction: START TRANSACTION;
• Commit: COMMIT;
• Rollback: ROLLBACK;

Transaction properties:

• Atomicity: All-or-nothing execution
• Durability: Changes persist after commit/rollback
• Isolation: Transactions are independent
• Consistency: Data remains valid

6. Indexes

Indexes are data structures that improve query performance, similar to a book's index

Advantages: Faster data retrieval, reduced I/O operations, improved sorting performance

Disadvantages: Additional disk space, slower write operations

6.1 Index Operations

• Create index: CREATE INDEX index_name ON table_name(column_name);
• Delete index: DROP INDEX index_name;
• View indexes: SHOW INDEX FROM table_name;
• Unique index: CREATE UNIQUE INDEX index_name ON table_name(column_name);
• Bitmap index (for low cardinality data): CREATE BITMAP INDEX index_name ON table_name(column_name);