SQL
SQL stands for Structured Query Language. It enables access and manipulation of databases. SQL became a standard of the American National Standards Institute (ANSI) and International Organization for Standardization (ISO) in 1986, 1987 subsequently. There are different versions of the SQL language with proprietary extensions. But they all support the SQL standards with similar core functions.
SQL Syntax
SQL keywords are NOT case sensitive, but it is good practice and convention to use ALL UPPERCASE for KEYWORDS. It is typical to use semicolon at the end of each SQL statement.
Important SQL commands:
SELECT
SELECT selects columns from a table.
Example 1:
SELECT column1, column2, ...
FROM table_name;
SELECT * selects all columns from a table.
Example 2:
SELECT *
FROM table_name;
SELECT DISTINCT
SELECT DISTINCT selects only the DISTINCT values from columns.
Example 1:
SELECT DISTINCT column1, column2, ...
FROM table_name;
Example 2:
SELECT COUNT(DISTINCT Country)
FROM Customers;
WHERE
WHERE clause is used to filter records.
Example:
SELECT
column1, column2, ...
FROM table_name
WHERE condition;
Operators in The WHERE Clause:
=
<
>
<=
>=
<> not equal
BETWEEN between a range
LIKE search for a pattern
IN to specify multiple possible values for a column
AND, OR and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators.
Example 1:
SELECT * FROM Customers
WHERE Country='China' AND (City='Beijing' OR City='Xian');
Example 2:
SELECT * FROM Customers
WHERE NOT Country='Russia' AND NOT Country='USA';
ORDER BY
ORDER BY sorts the results in ascending order by default. Use DESC if we want to sort in descending order.
Example 1:
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
INSERT INTO
INSERT INTO inserts new records into a table.
Two ways to write the INSERT INTO statement:
- Specify both column names and the values for each column:
- To add values for all columns in the table, we do not need to specify the column names. But we need to make sure the order of the values inserted matches the order of the columns in the table.
Syntax 1:
INSERT INTO table_name(column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Syntax 2:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
NULL
A field with NULL value is a field with missing values.
To test for NULL values, use IS NULL, IS NOT NULL.
UPDATE
Use UPDATE to modify existing records in a table.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
DELETE
DELETE deletes existing records in a table.
Syntax:
DELETE FROM table_name WHERE condition;
Delete All Records
It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:
DELETE FROM table_name;
Resources for learning SQL: