Genarel SQL Syntax Rules

1. SQL statements can span single or multiple lines, terminating with a semicolon
2. Whitespace and indentation improve readability without affecting execution
3. MySQL is case-insensitive for keywords (though uppercase is conventional)
4. Comment syntax:
   • Single-line: -- comment or # comment (MySQL-specific)
   • Multi-line: /* comment */

SQL Command Categories

• DDL: Data Definition Language (database/table/column operations)
• DML: Data Manipulation Language (data insertion/modification)
• DQL: Data Query Language (data retrieval)
• DCL: Data Control Language (user permissions)

Database Operations (DDL)

-- List all databases
SHOW DATABASES;

-- Show current database
SELECT DATABASE();

-- Create new database
CREATE DATABASE [IF NOT EXISTS] db_name 
  [CHARACTER SET charset] [COLLATE collation];

-- Delete database
DROP DATABASE [IF EXISTS] db_name;

-- Select database
USE db_name;

Table Operasions (DDL)

-- List tables in current database
SHOW TABLES;

-- View table structure
DESCRIBE table_name;

-- Show table creation statement
SHOW CREATE TABLE table_name;

-- Create table
CREATE TABLE table_name (
  column1 datatype [COMMENT 'note'],
  column2 datatype [COMMENT 'note'],
  ...
) [COMMENT 'table_note'];

Data Types

Numeric:

• TINYINT (1 byte), SMALLINT (2), INT/INTEGER (4), BIGINT (8)
• FLOAT (4), DOUBLE (8), DECIMAL (precision-based)

String:

• CHAR (fixed), VARCHAR (variable)
• TEXT variants (TINYTEXT, TEXT, LONGTEXT)
• BLOB variants (binary data)

Temporal:

• DATE (YYYY-MM-DD), TIME (HH:MM:SS)
• DATETIME, TIMESTAMP (both store date+time)

Table Modification (DDL)

-- Add column
ALTER TABLE table_name ADD column_name datatype [COMMENT 'note'];

-- Modify column type
ALTER TABLE table_name MODIFY column_name new_datatype;

-- Rename column
ALTER TABLE table_name CHANGE old_name new_name datatype;

-- Remove column
ALTER TABLE table_name DROP column_name;

-- Rename table
ALTER TABLE table_name RENAME TO new_name;

-- Delete table
DROP TABLE [IF EXISTS] table_name;

-- Truncate table (delete all data)
TRUNCATE TABLE table_name;

Data Manipulation (DML)

-- Insert data
INSERT INTO table_name (col1, col2) VALUES (val1, val2);
INSERT INTO table_name VALUES (val1, val2, ...);

-- Batch insert
INSERT INTO table_name VALUES 
  (val1, val2), 
  (val3, val4);

-- Update records
UPDATE table_name SET col1=val1, col2=val2 [WHERE condition];

-- Delete records
DELETE FROM table_name [WHERE condition];

Data Query (DQL)

-- Basic query
SELECT col1, col2 FROM table_name;
SELECT * FROM table_name;

-- Aliases
SELECT col1 AS alias1, col2 AS alias2 FROM table_name;

-- Distinct values
SELECT DISTINCT column FROM table_name;

-- Conditional query
SELECT columns FROM table_name WHERE conditions;

-- Aggregation functions
SELECT COUNT(*), AVG(col1), MAX(col2) FROM table_name;

-- Grouping
SELECT col1, COUNT(*) 
FROM table_name 
WHERE conditions 
GROUP BY col1 
HAVING group_conditions;

-- Sorting
SELECT columns FROM table_name ORDER BY col1 [ASC|DESC], col2 [ASC|DESC];

-- Pagination
SELECT columns FROM table_name LIMIT offset, count;

User Management (DCL)

-- List users
USE mysql;
SELECT * FROM user;

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

-- Change password
ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'new_password';

-- Remove user
DROP USER 'username'@'host';

-- Grant permissions
GRANT permission_list ON database.table TO 'user'@'host';

-- Revoke permissions
REVOKE permission_list ON database.table FROM 'user'@'host';

-- View permissions
SHOW GRANTS FOR 'user'@'host';