本文已参与「新人创作礼」活动，一起开启掘金创作之路。
SQL中的数据类型
Characters
- CHAR(n) --- fixed length
- VARCHAR2(n) --- variable length (VARCHAR是标准SQL，VARCHAR2是Oracle提供的)
Numbers
- INTEGER --- integers
- NUMBER --- real numbers
Times and dates
- DATE
- TIMESTAMP
超长String
- Long
SQL中的表(Tables)
- A tuple: a record
- A Table: a set of tuples
- schema: the table name and its attributes
- Product(PName, Price, Category, Manfacture)
- Primary Key: an attribute (or a set of attributes) whose value is unique
简单的SQL查询
#target list选择表里的属性
SELECT {T1.Attribute 1, ...T2.Attribute 2, T3.Attribute 3....}
#relation-list 计算表之间的cross product
FROM {relation} T1, {relation} T2...
#qulification 按照条件筛选record
WHERE {predicates}
简单SQL查询的执行步骤
- 计算一个SQL查询分为三步:
- 计算relation-list的corss-product
- 按照qulification筛选数据
- 删除不在target-list里的属性
- 上面的策略是最基础的，也是计算查询效率最低的方法
- optimizer可以提高效率
Example：
SELECT PName, Price, Manufacture
FROM Product
WHERE Price > 100
在查询中链接多个表
Example 1: Product
| PName | Price | Category | Manufacturer |
|---|---|---|---|
| Gizmo | $19.99 | Gadgets | GizmoWorks |
| Powergizmo | $29.99 | Gadgets | GizmoWorks |
| SingleTouch | $149.99 | Photography | Canon |
| MultiTouch | $203.99 | Household | Hitachi |
Company
| Cname | StockPrice | Country |
|---|---|---|
| GizmoWorks | 25 | USA |
| Canon | 65 | Japan |
| Hitachi | 15 | Japan |
SELECT pname, price
FROM Product, Company
WHERE manufacturer=cname AND country=‘Japan’
计算cross product
FROM Product, Company
| PName | Price | Category | Manufacturer | Cname | StockPrice | Country |
|---|---|---|---|---|---|---|
| Gizmo | $19.99 | Gadgets | GizmoWorks | GizmoWorks | 25 | USA |
| Gizmo | $19.99 | Gadgets | GizmoWorks | Canon | 65 | Japan |
| Gizmo | $19.99 | Gadgets | GizmoWorks | Hitachi | 15 | Japan |
| Powergizmo | $29.99 | Gadgets | GizmoWorks | GizmoWorks | 25 | USA |
| Powergizmo | $29.99 | Gadgets | GizmoWorks | Canon | 65 | Japan |
| Powergizmo | $29.99 | Gadgets | GizmoWorks | Hitachi | 15 | Japan |
| SingleTouch | $149.99 | Photography | Canon | GizmoWorks | 25 | USA |
| SingleTouch | $149.99 | Photography | Canon | Canon | 65 | Japan |
| SingleTouch | $149.99 | Photography | Canon | Hitachi | 15 | Japan |
| MultiTouch | $203.99 | Household | Hitachi | GizmoWorks | 25 | USA |
| MultiTouch | $203.99 | Household | Hitachi | Canon | 65 | Japan |
| MultiTouch | $203.99 | Household | Hitachi | Hitachi | 15 | Japan |
根据WHERE条件进行筛选
manufacturer=cname AND country=‘Japan’
| PName | Price | Category | Manufacturer | Cname | StockPrice | Country |
|---|---|---|---|---|---|---|
| SingleTouch | $149.99 | Photography | Canon | Canon | 65 | Japan |
| MultiTouch | $203.99 | Household | Hitachi | Hitachi | 15 | Japan |
删除不在target list里的属性
SELECT pname, price
| PName | Price |
|---|---|
| SingleTouch | $149.99 |
| MultiTouch | $203.99 |
Example 2: Product
| PName | Price | Category | Manufacturer |
|---|---|---|---|
| Gizmo | $19.99 | Gadgets | GizmoWorks |
| Powergizmo | $29.99 | Gadgets | GizmoWorks |
| SingleTouch | $149.99 | Photography | Canon |
| MultiTouch | $203.99 | Household | Hitachi |
Company
| Cname | StockPrice | Country |
|---|---|---|
| GizmoWorks | 25 | USA |
| Canon | 65 | Japan |
| Hitachi | 15 | Japan |
SELECT country
FROM Product, Company
WHERE manufacturer=cname AND category=‘Gadgets’
Answer: 会出现两个USA
| Country |
|---|
| USA |
| USA |
所以需要加上DISTINCT
SELECT DISTINCT country
FROM Product, Company
WHERE manufacturer=cname AND category=‘Gadgets’
Example 3:
Product (pname, price, category, manufacturer) Purchase (buyer, seller, store, product) Person(persname, phoneNumber, city)
- Find names of people living in Seattle that bought some product in the ‘Gadgets’ category, and the names of thestores they bought such product from
SELECT DISTINCT persname, store
FROM Person, Purchase, Product
#persname对应buyer， pname对应product
WHERE persname=buyer AND product = pname AND
city=‘Seattle’ AND category=‘Gadgets’
Example 4:
Person(pname, address, worksfor) Company(cname, address)
SELECT DISTINCT pname, address #Person和Company中都有Address, 是哪个？
FROM Person, Company
WHERE worksfor = cname
修改为
SELECT DISTINCT Person.pname, Company.address
FROM Person, Company
WHERE Person.worksfor = Company.cname
复杂SQL查询的执行步骤
- The cross products of the tables in the FROM clause are evaluated.
- Rows not satisfying the WHERE clause are eliminated.
- The remaining rows are grouped in accordance with the GROUP BY clause.
- Groups not satisfying the HAVING clause are then eliminated.
- The expressions in the SELECT list are evaluated.
- If the keyword DISTINCT is present, duplicate rows are now eliminated.
- Evaluate UNION, INTERSECT and EXCEPT for Subqueries up to this point.
- Finally, the set of all selected rows is sorted if the ORDER BY is present.
Banking Example
branch (branch-id, branch-city, assets)
customer (customer-id, customer-name, customer-city)
account (account-number, branch-id, balance)
loan (loan-number, branch-id, amount)
depositor (customer-id, account-number)
borrower (customer-id, loan-number)
EXISTS Example 1: “Select all the account balances where the account has been opened in a branch in Fairfax”
SELECT A.account-balance
FROM account A
WHERE EXISTS (SELECT *
FROM branch B
WHERE B.branch-city=‘Fairfax’
AND B.branch-id=A.branch-id)
EXISTS Example 2: “Select all the account balances where the account has not been opened in a Fairfax branch ”
SELECTA.account-balance
FROM account A
WHERE NOT EXISTS (SELECT *
FROM branch B
WHERE B.branch-city=‘Fairfax’
AND B.branch-id=A.branch-id)
NOT EXISTS Example 3: “Find customers who opened accounts in all branches in Fairfax”
SELECT C.customer-id
FROM customer C
WHERE NOT EXISTS (SELECT B.branch-id
FROM branch B
WHERE B.branch-city=‘Fairfax’
EXCEPT
SELECT A.branch-id
FROM depositor D, account A
WHERE D.customer-id = C.customer-id AND
D.account-number =A.account-number)
