SQL Basic Syntax

After mastering basic database concepts, now let's learn SQL basic syntax. This chapter introduces core SQL syntax rules, including statement structure, comments, identifiers, etc.

Basic Structure of SQL Statements

Standard Format

SQL statements typically consist of the following parts:

SELECT column1, column2  -- Select columns to query
FROM table_name           -- Specify table to query
WHERE condition          -- Add filter condition
GROUP BY column          -- Group
HAVING condition         -- Filter after grouping
ORDER BY column          -- Sort
LIMIT number;            -- Limit number of results

Statement Features

• Case Insensitive: SELECT and select are the same
• Ends with Semicolon: Each SQL statement ends with ;
• Multi-line: SQL statements can be written across multiple lines
• Indentation: Proper indentation improves readability

SQL Statement Categories

1. DDL (Data Definition Language)

Used to define database structure:

-- Create database
CREATE DATABASE mydb;

-- Create table
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Modify table
ALTER TABLE users ADD COLUMN age INT;

-- Delete table
DROP TABLE users;

2. DML (Data Manipulation Language)

Used to manipulate data in tables:

-- Query data
SELECT * FROM users;

-- Insert data
INSERT INTO users (id, name) VALUES (1, 'Zhang San');

-- Update data
UPDATE users SET name = 'Li Si' WHERE id = 1;

-- Delete data
DELETE FROM users WHERE id = 1;

3. DCL (Data Control Language)

Used to control access permissions:

-- Grant permission
GRANT SELECT ON users TO 'user1'@'localhost';

-- Revoke permission
REVOKE SELECT ON users FROM 'user1'@'localhost';

4. TCL (Transaction Control Language)

Used to manage transactions:

-- Start transaction
START TRANSACTION;

-- Commit transaction
COMMIT;

-- Rollback transaction
ROLLBACK;

SQL Comments

Single-line Comments

Use -- or #:

-- This is a single-line comment
SELECT * FROM users;

# This is also a single-line comment
SELECT name FROM users;

Multi-line Comments

Use /* ... */:

/*
This is a multi-line comment
Can contain multiple lines of text
Used to explain complex SQL statements
*/
SELECT id, name, age
FROM users
WHERE age > 18;

Comment Best Practices

-- Query all users over 18 years old
SELECT
    id,           -- User ID
    name,         -- Username
    age,          -- Age
    city          -- City
FROM users
WHERE age > 18;  -- Filter condition

SQL Identifiers

Identifier Rules

Identifiers are names of database objects (table names, column names, etc.) and must follow these rules:

• Can contain letters, numbers, and underscores
• Must start with a letter or underscore
• Cannot use reserved words
• Case sensitive (depends on database configuration)

Naming Recommendations

-- Good naming
user_id
order_date
product_name
total_amount

-- Not recommended naming
userId          -- Camel case
order-date       -- Using hyphens
123_column       -- Starts with number
table            -- Reserved word

Using Quotes for Special Names

If you need to use reserved words or names containing special characters, use quotes:

-- MySQL uses backticks
SELECT `order` FROM `table`;

-- SQL Server uses square brackets
SELECT [order] FROM [table];

-- PostgreSQL uses double quotes
SELECT "order" FROM "table";

Common Keywords

Query Related

Keyword	Description	Example
SELECT	Select data	SELECT * FROM users
FROM	Specify table	FROM users
WHERE	Filter condition	WHERE age > 18
GROUP BY	Group	GROUP BY city
HAVING	Filter groups	HAVING COUNT(*) > 10
ORDER BY	Sort	ORDER BY name
LIMIT	Limit results	LIMIT 10

Data Definition Related

Keyword	Description	Example
CREATE	Create	CREATE TABLE users
ALTER	Modify	ALTER TABLE users
DROP	Delete	DROP TABLE users
TRUNCATE	Clear table	TRUNCATE TABLE users

Data Operation Related

Keyword	Description	Example
INSERT	Insert	INSERT INTO users VALUES
UPDATE	Update	UPDATE users SET name = '...'
DELETE	Delete	DELETE FROM users

Constraint Related

Keyword	Description	Example
PRIMARY KEY	Primary key	id INT PRIMARY KEY
FOREIGN KEY	Foreign key	FOREIGN KEY (user_id)
UNIQUE	Unique	email VARCHAR(100) UNIQUE
NOT NULL	Not null	name VARCHAR(50) NOT NULL
DEFAULT	Default value	age INT DEFAULT 0

SQL Statement Execution Order

Logical Execution Order

The logical execution order of SQL statements is different from the writing order:

SELECT  -- 5. Select columns
FROM    -- 1. Specify table
WHERE   -- 2. Filter rows
GROUP BY -- 3. Group
HAVING  -- 4. Filter groups
ORDER BY -- 6. Sort
LIMIT   -- 7. Limit count

Example

-- Execution order
SELECT city, COUNT(*) as user_count  -- 5. Calculate user count per city
FROM users                         -- 1. From users table
WHERE age >= 18                     -- 2. Filter 18 and above
GROUP BY city                       -- 3. Group by city
HAVING user_count > 5               -- 4. Only show cities with more than 5 users
ORDER BY user_count DESC            -- 6. Sort by user count descending
LIMIT 5;                            -- 7. Only show first 5

SQL Operators

Arithmetic Operators

-- Basic operations
SELECT price * quantity  -- Multiplication
SELECT price + discount  -- Addition
SELECT price - discount  -- Subtraction
SELECT price / quantity  -- Division
SELECT price % 2         -- Modulo

Comparison Operators

-- Comparison operations
WHERE age = 18           -- Equal to
WHERE age != 18          -- Not equal to
WHERE age > 18           -- Greater than
WHERE age < 18           -- Less than
WHERE age >= 18          -- Greater than or equal to
WHERE age <= 18          -- Less than or equal to

Logical Operators

-- Logical operations
WHERE age > 18 AND city = 'Beijing'   -- Logical AND
WHERE age > 18 OR city = 'Beijing'    -- Logical OR
WHERE NOT (age > 18)                  -- Logical NOT

Range and Set Operators

-- Range operations
WHERE age BETWEEN 18 AND 30           -- Range query
WHERE city IN ('Beijing', 'Shanghai')  -- Set query
WHERE name LIKE 'Zhang%'              -- Fuzzy matching
WHERE email IS NULL                   -- Check for null
WHERE email IS NOT NULL               -- Check for not null

String Processing

String Concatenation

-- MySQL uses CONCAT
SELECT CONCAT(name, ' - ', city) FROM users;

-- PostgreSQL uses ||
SELECT name || ' - ' || city FROM users;

-- SQL Server uses +
SELECT name + ' - ' + city FROM users;

String Case

-- Convert to uppercase
SELECT UPPER(name) FROM users;

-- Convert to lowercase
SELECT LOWER(name) FROM users;

String Length

-- Get string length
SELECT LENGTH(name) FROM users;

String Substring

-- Substring
SELECT SUBSTRING(name, 1, 3) FROM users;  -- Start from 1st character, take 3 characters

Date/Time Processing

Current Date/Time

-- Get current date/time
SELECT NOW();           -- MySQL
SELECT CURRENT_TIMESTAMP; -- PostgreSQL
SELECT GETDATE();       -- SQL Server

Date Calculation

-- MySQL
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);     -- 7 days later
SELECT DATE_SUB(NOW(), INTERVAL 7 DAY);     -- 7 days earlier

-- PostgreSQL
SELECT NOW() + INTERVAL '7 days';           -- 7 days later
SELECT NOW() - INTERVAL '7 days';           -- 7 days earlier

Date Formatting

-- MySQL
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');     -- 2024-01-15

-- PostgreSQL
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');      -- 2024-01-15

NULL Value Handling

NULL Characteristics

• NULL represents "unknown" or "does not exist"
• NULL is not equal to any value, including NULL
• In mathematical operations, NULL + any value = NULL

NULL Checking

-- Check for NULL
WHERE email IS NULL

-- Check for NOT NULL
WHERE email IS NOT NULL

NULL Operations

-- COALESCE function: returns first non-NULL value
SELECT COALESCE(email, 'Not filled') FROM users;

-- NULLIF function: returns NULL if two parameters are equal
SELECT NULLIF(price, 0) FROM products;

-- Handle NULL in mathematical operations
SELECT price * quantity * COALESCE(discount, 1) FROM orders;

Data Type Conversion

CAST Function

-- Convert data types
SELECT CAST('123' AS INT);           -- String to integer
SELECT CAST(123 AS VARCHAR(10));     -- Integer to string
SELECT CAST('2024-01-15' AS DATE);  -- String to date

Type Conversion Functions

-- MySQL
SELECT CONVERT('123', INT);

-- PostgreSQL
SELECT '123'::INT;

-- SQL Server
SELECT CONVERT(INT, '123');

SQL Statement Best Practices

1. Use Meaningful Aliases

-- Good
SELECT
    u.name AS user_name,
    o.order_date,
    COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON u.id = o.user_id;

-- Not good
SELECT
    a.name,
    b.order_date,
    COUNT(b.id)
FROM users a
JOIN orders b ON a.id = b.user_id;

2. Use Indentation Appropriately

-- Good formatting
SELECT
    name,
    age,
    city
FROM users
WHERE age > 18
ORDER BY name;

-- Not good formatting
SELECT name, age, city FROM users WHERE age > 18 ORDER BY name;

3. Add Comments

-- Query order information for active users
SELECT
    u.username,
    o.order_id,
    o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
  AND o.order_date > '2024-01-01';

Summary

This chapter introduced SQL basic syntax:

• Statement Structure: SELECT, FROM, WHERE and other basic structures
• Statement Categories: DDL, DML, DCL, TCL
• Comment Syntax: Single-line and multi-line comments
• Identifier Rules: Naming rules and quote usage
• Common Keywords: Query, definition, operation, constraint
• Execution Order: Logical execution order of SQL statements
• Operators: Arithmetic, comparison, logical operators
• NULL Handling: NULL characteristics and handling methods

Mastering these basic syntaxes is the foundation for writing SQL statements. In the following chapters, we will learn in depth how to use these syntaxes to query and manipulate data.

Next Step: Learn SELECT Query to start retrieving data from databases.