Introduction to SQL
Introduction
Structured Query Language (SQL) is a standardized and normalized pseudo-language (query type), designed to query or manipulate a relational database with:
- A Data Definition Language (DDL)
- A Data Manipulation Language (DML), the most common and visible part of SQL
- A Data Control Language (DCL)
- A Transaction Control Language (TCL)
- And other modules designed to write routines (procedures, functions, or triggers) and interact with external languages.
SQL is part of the same family as SEQUEL (of which it is a descendant), QUEL, or QBE (Zloof) languages.
Create
The INSERT command adds a row to a database table:
The number of columns appearing in parentheses before VALUES must match the number of values in the parentheses after. To insert a row that contains values for only certain columns, simply indicate the relevant columns and their corresponding values:
The column list can be omitted if you are inserting values for all columns in a row:
UPDATE
The UPDATE command modifies data already present in a table:
Here's an example of initializing a column with a string or number:
Another example, using a column name in an UPDATE expression:
All UPDATE queries we have just presented modify each row in the dishes table. To have UPDATE change only certain rows, simply add a WHERE clause which is a logical expression that specifies which rows to modify (in this example). Here's the use of a WHERE clause with UPDATE:
Another example: I wanted to replace words in MediaWiki (source by syntaxhighlight) for updating Geshi (Syntax Highlight), here are the commands I used:
DELETE
The DELETE command removes rows from a table:
Without a WHERE clause, DELETE removes all rows from the table:
Deleting specific rows from a table:
As there is no UNDELETE command in SQL, be careful when using DELETE!
SELECT
- Data retrieval:
- Retrieving dish_name and price:
The * symbol is a shortcut that refers to all columns in the table.
- Using * in a SQL query:
- Constraints on rows returned by SELECT:
- Retrieving specific dishes:
- SQL Operators for the WHERE clause:
Operator | Description |
---|---|
= | Equal to (like == in PHP) |
<> | Not equal to (like != in PHP) |
> | Greater than |
< | Less than |
>= | Greater than or equal to |
<= | Less than or equal to |
AND | Logical AND (like && in PHP) |
OR | Logical OR (like || in PHP) |
() | Grouping |
ORDER BY and LIMIT
- Sort rows returned by a SELECT query:
- To sort in descending order, add DESC after the column to sort:
You can sort by multiple columns: if 2 rows have the same value for the first column, they will be sorted by the value of the second, etc. The query in the example below sorts the rows in the dishes table by descending price; if rows have the same price, they will then be sorted alphabetically by name.
- Sorting by multiple columns:
It is sometimes convenient to retrieve only a certain number of rows: you may want to know the cheapest dish, or display only 10 results for example.
- Limiting the number of rows returned by SELECT:
- Limiting the number of rows returned by SELECT:
As a general rule, you should only use LIMIT in queries with an ORDER BY clause, because otherwise, the database system can return rows in any order: "the first" row of a query result can then be different from that returned by the same query at another time.
Wildcards or SQL Regex
Wildcards or Regex characters allow you to search for strings matching certain patterns: you can find strings ending with .edu or containing @. SQL has 2 wildcard characters: _ corresponds to one and only one character, while % corresponds to a sequence of characters of any length (possibly empty). These wildcards are active when they are in strings used with the LIKE operator in a WHERE clause.
- Using wildcards with SELECT:
Wildcards can also be used in WHERE clauses of UPDATE and DELETE commands. The query in the example below doubles the price of all dishes containing "chili" in their names:
- Using wildcards with UPDATE:
- Using wildcards with DELETE:
To literally use a % or _ character with the LIKE operator, precede it with a backslash ().