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.
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:
Another example, using a column name in an UPDATE expression:
1
UPDATEdishesSETprice=price*2
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:
1
2
3
4
; CHANGEthespicySTATUSOFEggplantWITHChiliSauceUPDATEdishesSETis_spicy=1WHEREdish_name='Eggplant with Chili Sauce'; DecreasethepriceOFGeneralTso's Chicken
UPDATE dishes SET price = price - 1 WHERE dish_name = 'GeneralTso\'s Chicken'
Another example: I wanted to replace words in MediaWiki (source by syntaxhighlight) for updating Geshi (Syntax Highlight), here are the commands I used:
To sort in descending order, add DESC after the column to sort:
1
SELECTdish_nameFROMdishesORDERBYpriceDESC
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.
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 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.
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: