1.SQL is a very-high-level language, in which the programmer is able to avoid specifying a lot of data-manipulation details that would be necessary in languages like C++.
2.What makes SQL viable is that its queries are “optimized” quite well, yielding efficient query executions.
1. Single-relation queries2. Multi-relation queries3. Subqueries4. Grouping and Aggregation
（1）SELECT - FROM - WHERE statements
SELECT ... (desired attributes)
From ... （one or more tables）
WHERE ...（ condition about tuples of the tables）
EX: Using Beers(name, manf), what beers are made by Busch?
SELECT name,FROM Beers,WHERE manf = 'Busch'
（2）When there is one relation in the FROM clause, SELECT * clause stands for “all attributes of this relation.”
（3）If you want the result to have different attribute names, use “AS <new name>” to rename an attribute.
EX:Example based on Beers(name, manf):
SELECT name AS beername, manfFROM BeersWHERE manf = ‘Busch’
（4）SQL allows duplicates in relations as well as in query results.
To force the elimination of duplicates, insert the keyword distinct after select.
Find the names of all branches in the loan relations, and remove duplicates
select distinct branch_namefrom loan
The keyword all specifies that duplicates not be removed.
select all branch_namefrom loan
(5)Any expression that makes sense can appear as an element of a SELECT clause.
EX: from Sells(bar, beer, price):
SELECT bar, beer, price * 6 AS priceInYuanFROM Sells;
SELECT Sname，2006-Sage AS 'Year of Birth: ' ，LOWER(Sdept)FROM Student；
(7)What we can use in select clause :
（8）What you can use in WHERE:
attribute names of the relation(s) used in the FROM.
comparison operators: =, <>, <, >, <=, >=, between, in
apply arithmetic operations: stockprice*2
operations on strings (e.g., “||” for concatenation).
Lexicographic order on strings.
Pattern matching: s LIKE p
Special stuff for comparing dates and times.
（9）Range comparison: between
谓词： BETWEEN … AND … NOT BETWEEN … AND …
SELECT Sname，Sdept，SageFROM StudentWHERE Sage BETWEEN 20 AND 23；
（10）Set operator: in
使用谓词： IN <值表>, NOT IN <值表>
SELECT Sname，SsexFROM StudentWHERE Sdept IN ( 'IS'，'MA'，'CS' );
WHERE clauses can have conditions in which a string is compared with a pattern, to see if it matches.
General form: <Attribute> LIKE <pattern> or <Attribute> NOT LIKE <pattern>
Pattern is a quoted string with %
= “any string”; _
= “any character.”
s LIKE p: pattern matching on strings
p may contain two special symbols:
% = any sequence of characters
_ = any single character
When the string contains ‘%’ or ‘_’, you need to use ESCAPE character
（14）Ordering the Display of Tuples
Use ‘Order by
’ clause to specify the alphabetic order of the query result
select distinct customer_name from borrower order by customer_name
We may specify desc for descending order or asc for ascending order, for each attribute; ascending order is the default.
Example: order by customer_name desc
Note: Order by can only be used as the last part of select statement
SELECT *FROM StudentORDER BY Sdept，Sage DESC；
To understand how AND, OR, and NOT work in 3-valued logic, think of TRUE = 1, FALSE = 0, and UNKNOWN = ½.AND = MIN; OR = MAX, NOT(x) = 1-x.
TRUE AND (FALSE OR NOT(UNKNOWN)) = MIN(1, MAX(0, (1 - ½ ))) = MIN(1, MAX(0, ½ ) = MIN(1, ½ ) = ½.
（17）If x=Null then 4*(3-x)/7 is still NULL
If x=Null then x=“Joe” is UNKNOWNThree boolean values:
FALSE = 0
UNKNOWN = 0.5
TRUE = 1
SELECT *FROM PersonWHERE age < 25 OR age >= 2
Some Persons are not included !
（19）Testing for Null
Can test for NULL explicitly:
x IS NULL
x IS NOT NULL
SELECT *FROM PersonWHERE age < 25 OR age >= 25 OR age IS NULL
Now it includes all Persons
SUM, AVG, COUNT, MIN, and MAX can be applied to a column in a SELECT clause to produce that aggregation on the column.
Also, COUNT(*) counts the number of tuples.
计数 COUNT（[DISTINCT|ALL] *） COUNT（[DISTINCT|ALL] <列名>）
计算总和 SUM（[DISTINCT|ALL] <列名>）
计算平均值 AVG（[DISTINCT|ALL] <列名>）
求最大值 MAX（[DISTINCT|ALL] <列名>）
求最小值 MIN（[DISTINCT|ALL] <列名>）
EX:From Sells(bar, beer, price), find the average price of Bud:
SELECT AVG(price)FROM SellsWHERE beer = ‘Bud’;
（21）Eliminating Duplicates in an AggregationDISTINCT
inside an aggregation causes duplicates to be eliminated before the aggregation.
Example: find the number of different prices charged for Bud:
SELECT COUNT(DISTINCT price)FROM SellsWHERE beer = ‘Bud’;
（22）NULL’s Ignored in Aggregation
NULL never contributes to a sum, average, or count, and can never be the minimum or maximum of a column.
But if there are no non-NULL values in a column, then the result of the aggregation is NULL.
We may follow a SELECT-FROM-WHERE expression by GROUP BY
and a list of attributes.
The relation that results from the SELECT-FROM-WHERE is grouped according to the values of all those attributes, and any aggregation is applied only within each group.
EX： From Sells(bar, beer, price), find the average price for each beer:
SELECT beer, AVG(price)FROM SellsGROUP BY beer;
（24）Restriction on SELECT Lists With Aggregation
If any aggregation is used, then each element of the SELECT list must be either:
2.An attribute on the GROUP BY list.
（25）Illegal Query Example
You might think you could find the bar that sells Bud the cheapest by:
SELECT bar, MIN(price)FROM SellsWHERE beer = ‘Bud’;
But this query is illegal in SQL.
Why? Note bar is neither aggregated nor on the GROUP BY list.
HAVING <condition> may follow a GROUP BY clause.
If so, the condition applies to each group, and groups not satisfying the condition are eliminated.