Data Definition Language (DDL)

Database Operations

Creating Databases

-- Create a new database
CREATE DATABASE db_name;

-- Create database only if it doesn't exist
CREATE DATABASE IF NOT EXISTS db_name;

-- Create database with specific character set
CREATE DATABASE db_name CHARACTER SET charset_name;

-- Example: Create db4 with gbk charset if not exists
CREATE DATABASE IF NOT EXISTS db4 CHARACTER SET gbk;

Querying Databases

-- Show all databases
SHOW DATABASES;

-- Show database creation details
SHOW CREATE DATABASE db_name;

Modifying Databases

-- Change database character set
ALTER DATABASE db_name CHARACTER SET new_charset;

Deleting Databases

-- Delete a database
DROP DATABASE db_name;

-- Delete database only if it exists
DROP DATABASE IF EXISTS db_name;

Using Databases

-- Check current database
SELECT DATABASE();

-- Switch to a specific database
USE db_name;

Table Operations

Creating Tables

-- Basic table creation
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    columnN datatype
);

-- Data types
-- INT: Integer values
-- DOUBLE: Decimal values (DOUBLE(5,2) for 5 digits total, 2 after decimal)
-- DATE: Date only (YYYY-MM-DD)
-- DATETIME: Date and time (YYYY-MM-DD HH:MM:SS)
-- TIMESTAMP: Time stamp (YYYY-MM-DD HH:MM:SS), defaults to current system time if null
-- VARCHAR: Variable length strings

-- Example student table
CREATE TABLE student (
    student_id INT,
    full_name VARCHAR(32),
    age INT,
    grade_point DOUBLE(4,1),
    birth_date DATE,
    entry_time TIMESTAMP
);

-- Copy table structure
CREATE TABLE new_table LIKE existing_table;

Querying Tables

-- Show all tables in database
SHOW TABLES;

-- Display table structure
DESCRIBE table_name;

Modifying Tables

-- Rename table
ALTER TABLE table_name RENAME TO new_table_name;

-- Change table character set
ALTER TABLE table_name CHARACTER SET charset_name;

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

-- Modify column name and type
ALTER TABLE table_name CHANGE old_column_name new_column_name new_datatype;

-- Modify column datatype only
ALTER TABLE table_name MODIFY column_name new_datatype;

-- Drop column
ALTER TABLE table_name DROP column_name;

Deleting Tables

-- Delete table
DROP TABLE table_name;

-- Delete table only if it exists
DROP TABLE IF EXISTS table_name;

Table Constraints

NOT NULL Constraint

-- Add NOT NULL constraint during table creation
CREATE TABLE student (
    student_id INT,
    full_name VARCHAR(20) NOT NULL
);

-- Add NOT NULL constraint after creation
ALTER TABLE student MODIFY full_name VARCHAR(20) NOT NULL;

-- Remove NOT NULL constraint
ALTER TABLE student MODIFY full_name VARCHAR(20);

UNIQUE Constraint

-- Add UNIQUE constraint during creation
CREATE TABLE student (
    student_id INT,
    email VARCHAR(50) UNIQUE
);

-- Note: MySQL allows multiple NULL values in UNIQUE columns

-- Remove UNIQUE constraint
ALTER TABLE student DROP INDEX email;

-- Add UNIQUE constraint after creation
ALTER TABLE student MODIFY email VARCHAR(50) UNIQUE;

PRIMARY KEY Constraint

-- Characteristics: NOT NULL and UNIQUE, only one per table
-- Add during creation
CREATE TABLE student (
    student_id INT PRIMARY KEY,
    full_name VARCHAR(20)
);

-- Remove primary key
ALTER TABLE student DROP PRIMARY KEY;

-- Add after creation
ALTER TABLE student MODIFY student_id INT PRIMARY KEY;

-- Auto-increment
CREATE TABLE student (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(20)
);

-- Remove auto-increment
ALTER TABLE student MODIFY student_id INT;

-- Add auto-increment
ALTER TABLE student MODIFY student_id INT AUTO_INCREMENT;

FOREIGN KEY Constraint

-- Add during creation
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Remove foreign key
ALTER TABLE orders DROP FOREIGN KEY fk_customer;

-- Add after creation
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

-- Cascading operations
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) 
REFERENCES customers(customer_id) ON UPDATE CASCADE ON DELETE CASCADE;

Data Manipulation Language (DML)

Inserting Data

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

-- Insert into all columns
INSERT INTO table_name VALUES (value1, value2, ...);

-- Notes:
-- 1. Column names must match values in order and type
-- 2. Non-numeric values must be quoted (single or double)
-- 3. All columns can be targeted without specifying names

Deleting Data

-- Delete with condition
DELETE FROM table_name WHERE condition;

-- Delete all records (not recommended)
DELETE FROM table_name;

-- Efficient way to delete all records
TRUNCATE TABLE table_name;

Updating Data

-- Update with conditions
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

-- Note: Without WHERE clause, all records will be updated

Data Query Language (DQL)

Basic Query Structure

SELECT
    column_list
FROM
    table_list
WHERE
    conditions
GROUP BY
    grouping_columns
HAVING
    group_conditions
ORDER BY
    sorting_columns
LIMIT
    pagination;

Basic Queries

-- Multiple columns
SELECT column1, column2 FROM table_name;

-- All columns
SELECT * FROM table_name;

-- Remove duplicates
SELECT DISTINCT column1 FROM table_name;

-- Calculated columns
SELECT column1 + column2 AS total FROM table_name;

-- Handling NULL values
SELECT IFNULL(column1, 0) AS safe_column FROM table_name;

-- Column aliases
SELECT column1 AS alias1 FROM table_name;

Conditional Queries

-- Comparison operators
SELECT * FROM table_name WHERE column > value;

-- Range queries
SELECT * FROM table_name WHERE column BETWEEN value1 AND value2;

-- IN operator
SELECT * FROM table_name WHERE column IN (value1, value2, value3);

-- LIKE for pattern matching
-- _ for single character, % for multiple characters
SELECT * FROM table_name WHERE column LIKE 'pattern';

-- NULL handling
SELECT * FROM table_name WHERE column IS NULL;

-- Logical operators
SELECT * FROM table_name WHERE condition1 AND condition2;
SELECT * FROM table_name WHERE condition1 OR condition2;
SELECT * FROM table_name WHERE NOT condition;

Sorting Queries

-- Basic sorting
SELECT * FROM table_name ORDER BY column ASC;
-- ASC is default, can be omitted

-- Multiple sorting criteria
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;

Aggregate Functions

-- Count records
SELECT COUNT(*) FROM table_name;

-- Maximum value
SELECT MAX(column) FROM table_name;

-- Minimum value
SELECT MIN(column) FROM table_name;

-- Sum values
SELECT SUM(column) FROM table_name;

-- Average value
SELECT AVG(column) FROM table_name;

-- Note: Aggregate functions ignore NULL values
-- Solution: Use IFNULL or select non-null columns

Grouping Queries

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

-- Rules:
-- 1. Only grouping columns and aggregate functions in SELECT
-- 2. WHERE filters before grouping, HAVING filters after

-- WHERE vs HAVING
-- WHERE: Filters before grouping, cannot use aggregate functions
-- HAVING: Filters after grouping, can use aggregate functions

Pagination

-- Basic pagination
SELECT * FROM table_name LIMIT offset, count;

-- Formula: offset = (current_page - 1) * items_per_page

-- Note: LIMIT is MySQL-specific

Data Control Language (DCL)

User Management

-- Create user
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

-- Delete user
DROP USER 'username'@'hostname';

-- Change password
UPDATE USER SET PASSWORD = PASSWORD('new_password') WHERE USER = 'username';

-- Alternative password change
SET PASSWORD FOR 'username'@'hostname' = PASSWORD('new_password');

-- Check users
USE mysql;
SELECT * FROM USER;

-- Note: % wildcard allows login from any host

Privilege Management

-- Show privileges
SHOW GRANTS FOR 'username'@'hostname';

-- Grant privileges
GRANT privilege_list ON database.table TO 'username'@'hostname';

-- Revoke privileges
REVOKE privilege_list ON database.table FROM 'username'@'hostname';

Database Backup and Restoration

Backup Process

-- Backup syntax
mysqldump -u username -p password database_name > backup_path

Restoration Process

-- Steps:
1. Login to MySQL
2. Create database
3. Select database
4. Execute backup file
   source /path/to/backup/file

Multi-Table Queries

Inner Joins

-- Implicit inner join
SELECT * FROM table1, table2 WHERE table1.id = table2.id;

-- Explicit inner join
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
-- INNER keyword can be omitted

Outer Joins

-- Left outer join (all left table records + matches)
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

-- Right outer join (all right table records + matches)
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

Subqueries

-- Single row, single column result
SELECT * FROM table1 WHERE column = (SELECT MAX(column) FROM table2);

-- Multiple row, single column result
SELECT * FROM table1 WHERE column IN (SELECT column FROM table2 WHERE condition);

-- Multiple row, multiple column result (as virtual table)
SELECT * FROM table1 t1, (SELECT * FROM table2 WHERE condition) t2 WHERE t1.id = t2.id;

Transactions

Transaction Basics

-- Transaction operations:
-- Start transaction: START TRANSACTION;
-- Rollback: ROLLBACK;
-- Commit: COMMIT;

-- MySQL defaults to auto-commit
-- Oracle defaults to manual commit

-- Check auto-commit status
SELECT @@autocommit;

-- Change auto-commit
SET @@autocommit = 0; -- 0 for manual, 1 for auto

Transaction Properties

• Atomicity: All operations succeed or none do
• Consistency: Database remains valid after transaction
• Isolation: Concurrent transactions don't interfere
• Durability: Committed changes persist

Transaction Isolation Levels

-- Check current isolation level
SELECT @@tx_isolation;

-- Set isolation level
SET GLOBAL TRANSACTION ISOLATION LEVEL level;

-- Levels (from least to most restrictive):
-- READ UNCOMMITTED: Dirty reads, non-repeatable reads, phantom reads
-- READ COMMITTED: Non-repeatable reads, phantom reads
-- REPEATABLE READ: Phantom reads (MySQL default)
-- SERIALIZABLE: No issues (highest safety, lowest performance)