Appendix A: SQL Keywords Glossary
September 28, 2025 ยท View on GitHub
Data Definition Language (DDL)
ALTER - Modify database structure
ALTER TABLE users ADD COLUMN age INT;
CREATE - Create database objects
CREATE TABLE users (id INT PRIMARY KEY);
DROP - Delete database objects
DROP TABLE old_data;
RENAME - Rename database objects
RENAME TABLE old_name TO new_name;
TRUNCATE - Remove all rows from table
TRUNCATE TABLE temp_data;
Data Manipulation Language (DML)
DELETE - Remove rows
DELETE FROM users WHERE id = 1;
INSERT - Add new rows
INSERT INTO users (name) VALUES ('John');
REPLACE - Insert or update
REPLACE INTO users (id, name) VALUES (1, 'Jane');
SELECT - Retrieve data
SELECT * FROM users;
UPDATE - Modify existing rows
UPDATE users SET name = 'Jane' WHERE id = 1;
Data Query Language (DQL)
FROM - Specify tables
SELECT * FROM users;
WHERE - Filter conditions
SELECT * FROM users WHERE age > 18;
GROUP BY - Group rows
SELECT city, COUNT(*) FROM users GROUP BY city;
HAVING - Filter groups
SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 10;
ORDER BY - Sort results
SELECT * FROM users ORDER BY name ASC;
LIMIT - Restrict result count
SELECT * FROM users LIMIT 10;
Join Keywords
INNER JOIN - Matching rows from both tables
SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id;
LEFT JOIN - All rows from left table
SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id;
RIGHT JOIN - All rows from right table
SELECT * FROM users RIGHT JOIN orders ON users.id = orders.user_id;
CROSS JOIN - Cartesian product
SELECT * FROM colors CROSS JOIN sizes;
NATURAL JOIN - Join on same-named columns
SELECT * FROM users NATURAL JOIN profiles;
Set Operations
UNION - Combine unique results
SELECT name FROM customers UNION SELECT name FROM suppliers;
UNION ALL - Combine all results
SELECT name FROM customers UNION ALL SELECT name FROM suppliers;
Subquery Keywords
EXISTS - Check for row existence
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);
IN - Value in list
SELECT * FROM users WHERE id IN (1, 2, 3);
ANY/SOME - Compare to any value
SELECT * FROM products WHERE price > ANY (SELECT price FROM discounted_products);
ALL - Compare to all values
SELECT * FROM products WHERE price > ALL (SELECT price FROM competitor_products);
Transaction Control
BEGIN/START TRANSACTION - Start transaction
START TRANSACTION;
COMMIT - Save changes
COMMIT;
ROLLBACK - Undo changes
ROLLBACK;
SAVEPOINT - Set rollback point
SAVEPOINT sp1;
SET TRANSACTION - Set transaction properties
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Data Control Language (DCL)
GRANT - Give permissions
GRANT SELECT, INSERT ON mydb.* TO 'user'@'localhost';
REVOKE - Remove permissions
REVOKE INSERT ON mydb.* FROM 'user'@'localhost';
FLUSH PRIVILEGES - Reload permissions
FLUSH PRIVILEGES;
Constraints
PRIMARY KEY - Unique identifier
CREATE TABLE users (id INT PRIMARY KEY);
FOREIGN KEY - Reference to another table
FOREIGN KEY (user_id) REFERENCES users(id);
UNIQUE - No duplicate values
CREATE TABLE users (email VARCHAR(100) UNIQUE);
NOT NULL - Required field
CREATE TABLE users (name VARCHAR(100) NOT NULL);
CHECK - Validate values
CREATE TABLE users (age INT CHECK (age >= 18));
DEFAULT - Default value
CREATE TABLE users (status VARCHAR(20) DEFAULT 'active');
Index Keywords
INDEX/KEY - Create index
CREATE INDEX idx_email ON users(email);
FULLTEXT - Full-text search index
CREATE FULLTEXT INDEX idx_content ON articles(title, body);
SPATIAL - Geographic index
CREATE SPATIAL INDEX idx_location ON stores(coordinates);
Operators
AND - Logical AND
WHERE age > 18 AND status = 'active';
OR - Logical OR
WHERE category = 'electronics' OR category = 'computers';
NOT - Logical NOT
WHERE NOT status = 'deleted';
BETWEEN - Range check
WHERE price BETWEEN 10 AND 100;
LIKE - Pattern matching
WHERE name LIKE 'John%';
IS NULL - Null check
WHERE phone IS NULL;
Functions Keywords
AS - Alias
SELECT name AS customer_name FROM users;
CASE - Conditional logic
CASE WHEN age < 18 THEN 'minor' ELSE 'adult' END;
DISTINCT - Unique values
SELECT DISTINCT city FROM users;
CAST - Type conversion
SELECT CAST(price AS CHAR) FROM products;
Window Functions (MySQL 8.0+)
OVER - Define window
SELECT name, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM players;
PARTITION BY - Window groups
RANK() OVER (PARTITION BY category ORDER BY price);
ROW_NUMBER() - Sequential number
ROW_NUMBER() OVER (ORDER BY id);
RANK() - Ranking with gaps
RANK() OVER (ORDER BY score DESC);
DENSE_RANK() - Ranking without gaps
DENSE_RANK() OVER (ORDER BY score DESC);
Common Table Expressions (MySQL 8.0+)
WITH - Define CTE
WITH cte AS (SELECT * FROM users WHERE active = 1)
SELECT * FROM cte;
RECURSIVE - Recursive CTE
WITH RECURSIVE cte AS (...)
Miscellaneous
AUTO_INCREMENT - Auto-generate values
id INT AUTO_INCREMENT PRIMARY KEY;
BINARY - Case-sensitive comparison
WHERE BINARY name = 'John';
COLLATE - Set collation
ORDER BY name COLLATE utf8mb4_bin;
DESCRIBE/DESC - Show table structure
DESCRIBE users;
EXPLAIN - Show query execution plan
EXPLAIN SELECT * FROM users WHERE id = 1;
SHOW - Display information
SHOW TABLES;
SHOW DATABASES;
USE - Select database
USE mydb;