SQL Syntax Reference
This section provides a comprehensive reference for SQL syntax, covering common statements, clauses, and expressions used in database management.
Basic Structure
SQL queries typically follow a structured format. The most common statement is SELECT, used for retrieving data from one or more tables.
The SELECT Statement
The SELECT statement is used to query the database and retrieve records that match specified criteria.
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Clauses in SELECT
SELECT: Specifies the columns to retrieve. Use*to select all columns.FROM: Specifies the table(s) from which to retrieve data.WHERE: Filters records based on a specified condition.GROUP BY: Groups rows that have the same values in specified columns into summary rows.HAVING: Filters groups based on a specified condition (used withGROUP BY).ORDER BY: Sorts the result set in ascending (ASC) or descending (DESC) order.LIMIT/TOP: Restricts the number of rows returned. (Syntax varies by SQL dialect).
INSERT Statement
The INSERT statement is used to add new records to a table.
INSERT INTO table_name (column1, column2, ...)
VALUES ('value1', 'value2', ...);
Alternatively, you can insert multiple rows at once:
INSERT INTO table_name (column1, column2)
VALUES
('value1a', 'value2a'),
('value1b', 'value2b');
UPDATE Statement
The UPDATE statement is used to modify existing records in a table.
UPDATE table_name
SET column1 = 'new_value1', column2 = 'new_value2', ...
WHERE condition;
Warning: Omitting the WHERE clause in an UPDATE statement will update all rows in the table.
DELETE Statement
The DELETE statement is used to remove records from a table.
DELETE FROM table_name
WHERE condition;
Warning: Omitting the WHERE clause in a DELETE statement will delete all rows from the table.
Data Definition Language (DDL)
DDL statements are used to define, modify, and delete database objects.
CREATE TABLE
Creates a new table in the database.
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
PRIMARY KEY (column1)
);
ALTER TABLE
Modifies an existing table (e.g., adding, deleting, or modifying columns).
ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE table_name
DROP COLUMN column_name;
DROP TABLE
Deletes an existing table from the database.
DROP TABLE table_name;
Warning: DROP TABLE permanently deletes the table and all its data.
Common Operators and Expressions
Comparison Operators
=: Equal to<>or!=: Not equal to<: Less than>: Greater than<=: Less than or equal to>=: Greater than or equal to
Logical Operators
AND: Combines multiple conditions, all must be true.OR: Combines multiple conditions, at least one must be true.NOT: Reverses the truth value of a condition.
BETWEEN Operator
Selects values within a range. The range includes the specified values.
WHERE column_name BETWEEN value1 AND value2;
LIKE Operator
Searches for a specified pattern in a column.
%: Represents zero, one, or multiple characters._: Represents a single character.
WHERE column_name LIKE 'A%'; -- Starts with 'A'
WHERE column_name LIKE '%a'; -- Ends with 'a'
WHERE column_name LIKE '%r%'; -- Contains 'r'
WHERE column_name LIKE '_r%'; -- Second character is 'r'
IN Operator
Allows you to specify multiple values in a WHERE clause.
WHERE column_name IN ('value1', 'value2', ...);
IS NULL
Tests for null values.
WHERE column_name IS NULL;
Data Manipulation Language (DML) - Advanced
JOIN Clauses
JOIN clauses are used to combine rows from two or more tables based on a related column between them.
INNER JOIN: Returns records that have matching values in both tables.LEFT JOIN(orLEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table.RIGHT JOIN(orRIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table.FULL JOIN(orFULL OUTER JOIN): Returns all records when there is a match in either left or right table.
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;