目录
SELECT WHERE...BETWEEN...AND...范围搜索条件（闭区间）
SELECT WHERE...LIKE...模式匹配（%和_)
COUNT\COUNT（*）\SUM\AVG\MIN\MAX
SELECT - Grouping选择语句 分组GROUP BY
SELECT Multiple Grouping Columns分组列
Subqueries子查询（IN,ANY(SOME),ALL,EXISTS）
Introduction
DDL：data definition language数据定义语言
Creat table建表
Drop table删除表
DML：data manipulation language数据操作语言
Insert 插入、Delete 删除、update 更新、select 选择
DDL数据定义语言
Objects：table
建表Create table：
CREAT TABLE statement
CREAT TABLE Branch；
属性按照创建顺序排序，行（表中的角色））不参与排序
CREAT TABLE 名字（属性名 数据类型[默认值]约束，……）
CREATE TABLE table_name( { column_name data_type[ DEFAULT default_expr ] [column_constraint [, ... ] ] }|table_constraint } [, ... ] )
删除表 DROP Table：
DROP [TEMPORARY] TABLE [IF EXISTS]tbl_name [, tbl_name] ...[RESTRICT | CASCADE]
DML数据操作语言
INSERT插入语句：
INSERT INTO 表名[关键字]数值（数据表）
INSERT INTO TableName [ (columnList) ]VALUES (dataValueList)
dataValueList must match columnList asfollows:数据值列表必须匹配列列表
表中每一项数值相同、位置（顺序）正确、每一项的数据类型和列的数据类型兼容
例：INSERT INTO BRANCH（属性1，属性2……）括号内属性可以不写
VALUES ('B005', '22 Deer Rd', 'London', 'SW1 4EH');单引号或双引号
注：SQL中日期类型（DATE Type）为’YYYY_MM_DD’
SELECT（SELECT*）选择
SELECT A1, A2, …., An
FROM R1, R2, …., Rn
WHERE condition
注：可以用*代替“所有列”的缩写
例：
SELECT staffNo, fName, lName, address,position, sex, DOB, salary, branchNo
FROM Staff;
→ SELECT * FROM Staff;
SELECT DISTINCT消除重复值
Use DISTINCT to eliminate duplicates
SELECT DISTINCT propertyNo
FROM Viewing;
SELECT AS获得计算后数据
Calculated Fields
SELECT staffNo, fName, lName, salary/12
FROM Staff;// 从年薪salary获得月工资
→ SELECT staffNo, fName, lName, salary/12 AS monthlySalary
FROM Staff;
SELECT WHERE比较搜索条件
1.Comparison Search Condition比较搜索条件
SELECT staffNo, fName, lName, position,salary
FROM Staff
WHERE salary > 10000;//列出所有薪水超过一万的员工
SELECT WHERE...OR...复合比较搜索条件
2.Compound Comparison Search Condition复合比较搜索条件
SELECT * FROM Branch
WHERE city = “London” OR city =“Glasgow”;//列出伦敦或格拉斯哥所有分支机构的地址
SELECT WHERE...BETWEEN...AND...范围搜索条件（闭区间）
3.Range Search Condition
SELECT staffNo, fName, lName, position,salary
FROM Staff
WHERE salary BETWEEN 20000 AND 30000;//选择薪水在2w-3w的员工（包括端点）
SELECT WHERE...LIKE...模式匹配（%和_)
SELECT ownerNo, fName, lName, address, telNo
FROM PrivateOwner
WHERE address LIKE “%Glasgow%”//选择地址中有“Glasgow”（的任意长度的）字符串的用户
%:0或多个字符串的序列//_下划线：单个字符
SELECT NULL空搜索条件（IS NOT NULL）
SELECT clientNo, viewDate
FROM Viewing
WHERE propertyNo = “PG4” AND comment IS NULL;//搜索评价为空的信息
SELECT ORDER BY排序（单列、多列）
Single Column Ordering单列排序DESC降序ASC升序
SELECT staffNo, fName, lName, salary
FROM Staff
ORDER BY salary DESC;//列出所有员工工资，按工资降序（descending）排列
Multiple Column Ordering多序排列
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type, rent DESC;//按属性类型排序，并按租金降序（次要放后面）排序
Aggregate functions聚合（五种）
COUNT\COUNT（*）\SUM\AVG\MIN\MAX
COUNT: returns number of values in specified column.指定列中的值（该属性下有几个数）
SUM: returns sum of values in specified column.指定列值的和
AVG: returns average of values in specified column.指定列平均值
MIN: returns smallest value in specified column.指定列最小值
MAX: returns largest value in specified column.指定列最大值
注：1、每个操作对应一个列，并返回一个值
2、SUM和AVG只能用于数字，COUNT、MAX、MIN可以用于数字和非数字
3、除了COUNT（*）之外的函数都要先消除空值，Count（*）计算表的所有行，不管出现空值还是重复值
4、可以先用DISTINCT消除重复，DISTINCT对MIX/MAX无影响，可能对SUM/AVG有影响
SELECT COUNT(*)
SELECT COUNT(*) AS myCount
FROM PropertyForRent
WHERE rent > 350;
SELECT COUNT(DISTINCT)计算不同数
例：SELECT
COUNT(DISTINCT propertyNo) AS myCount
FROM Viewing
WHERE viewDate BETWEEN “2004-05-01”AND “2004-05-31”;//在2004年5月看了多少处不同的房产
SUM&COUNT：
SELECT COUNT(staffNo) AS myCount,
SUM(salary) AS mySum
FROM Staff
WHERE position = “Manager”;
MIN&AVG&MAX：
SELECT MIN(salary) AS myMin,
MAX(salary) AS myMax,
AVG(salary) AS myAvg
FROM Staff;
SELECT - Grouping选择语句 分组GROUP BY
Use GROUP BY clause to get sub-totals
选择列表中每个值必须是单值，选择语句只能包含：列名column names、聚合函数aggregate functions、常数constants、以上三者的组合表达式expression involving combinations of the above
注：1、GROUP BY中，空值认为相等
- 和WHERE一起用的时候，先行使WHERE，然后再GROUP BY
- SELECT列表中所有列名称必须出现在GROUP BY里面，除非名称只出现在聚合函数
SELECT branchNo,
COUNT(staffNo) AS myCount,
SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
ORDER BY branchNo;
SELECT HAVING受限分组的条款
Restricted Groupings – HAVING clause
SELECT branchNo,
COUNT(staffNo) AS myCount,
SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1 //每个超过一人的分公司
ORDER BY branchNo; //求员工超一人的分公司的员工人数和工资总和
SELECT JOIN多表查询
SELECT Simple Join简单连接
SELECT c.clientNo, fName, lName,propertyNo, comment
FROM Client c, Viewing v
WHERE c.clientNo = v.clientNo; //结果显示两个表中相同的值
SELECT Sorting a join排序连接
SELECT s.branchNo, s.staffNo, fName,lName,propertyNo
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
ORDER BY s.branchNo, s.staffNo,propertyNo; //相同值，提取，按照order的顺序排序
SELECT Three Table Join三表连接
SELECT b.branchNo, b.city, s.staffNo, fName,lName,propertyNo
FROM Branch b, Staff s, PropertyForRent p
WHERE b.branchNo = s.branchNo AND
s.staffNo = p.staffNo //每个分行列出相同的值
ORDER BY b.branchNo, s.staffNo, propertyNo;
SELECT Multiple Grouping Columns分组列
例，查找由每个工作人员处理的属性的数量
SELECT s.branchNo, s.staffNo, COUNT(*) ASmyCount
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
GROUP BY s.branchNo, s.staffNo
ORDER BY s.branchNo, s.staffNo;
Computing a Join计算 加入
Procedure for generating results of a join are:
- Form Cartesian product of the tables named inFROM clause. 形成在FROM子句中命名的表的笛卡尔乘积
- If there is a WHERE clause, apply the searchcondition to each row of the product table, retainingthose rows that satisfy the condition. 如果有WHERE子句，则将搜索条件应用于产品表的每一行，保留满足该条件的行。
- For each remaining row, determine value of each item in SELECT list to produce a single row in result table. 对于剩下的每一行，确定选择列表中每个项的值，以在结果表中生成一行。
- If DISTINCT has been specified, eliminate anyduplicate rows from the result table. 如果指定了不同（DISTINCT），请从结果表中删除任何重复的行。
- If there is an ORDER BY clause, sort result table as required.如果存在ORDER BY子句，则根据需要对结果表进行排序。
Subqueries子查询（IN,ANY(SOME),ALL,EXISTS）
Some SQL statements can have a SELECT embedded within them. 一些SQL语句可以在其中嵌入一个选择项
- A subselect can be used in WHERE and HAVING clauses of an outer SELECT, where it is called a subquery or nested query. 子选择可用于其中和包含外部选择的子句，其中它被称为子查询或嵌套查询
- Subselects may also appear in INSERT,UPDATE, and DELETE statements.子选择也可能出现在插入、更新和删除语句中
Subquery with Equality平等子查询
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’); //查找在主街163号分公司工作的员工
先内部选择，在主街163号的分公司，并得到相应的branchNo，然后外选择得到如下：
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo = ‘B003’; //B003为上面语句括号中选择的结果
Subquery with Aggregate复合子查询
SELECT staffNo, fName, lName, position,salary - (SELECT AVG(salary) FROM Staff) As SalDiff
FROM Staff
WHERE salary > (SELECT AVG(salary) FROM Staff);//选择工资超过平均工资的
加粗的地方（是一个运算）必须用括号括起来，如果是数值则不用，如：
SELECT staffNo, fName, lName, position, salary – 17000 As salDiff
FROM Staff
WHERE salary > 17000;
Subquery Rules 子查询规则
- 子查询中不能用ORDER BY（最外层选择中可以用）
- Subquery SELECT list must consist of a single column name or expression, except for subqueries that use EXISTS.子查询SELECT列表中必须包含单个列名或表达式，已存在或已使用的子查询除外
- By default, column names refer to table name in FROM clause of subquery. Can refer to a table in FROM using an alias.列名使用FROM子查询的表名，FROM中可以用别名（alias）
- When subquery is an operand in a comparison, subquery must appear on right-hand side.当子查询是比较中的操作数时，子查询必须显示在右侧。
Subquery IN嵌套子查询
在WEHRE里面规定多个值
SELECT propertyNo, street, city, postcode,type, rooms, rent
FROM PropertyForRent
WHERE staffNo IN
(SELECT staffNo
FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’));
//最外层：IN 在WHERE里面多层选择
//第一层括号：选择branchNo是B330的员工号
//最内层：选择street是中心街163号的branchNo
Subquery ANY&ALL
用于产生一系列数字的子查询
With ALL, condition will only be true if it issatisfied by all values produced by subquery. 子查询所有值满足条件时返回true
With ANY, condition will be true if it is satisfied by any values produced by subquery. 子查询存在值返回true
If subquery is empty, ALL returns true, ANY returns false. 子查询为空ALL返回true，ANY返回false
SOME may be used in place of ANY.语句SOME可以代替ANY
ANY/SOME：
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > SOME (SELECT salary
FROM Staff
WHERE branchNo = ‘B003’);//在分公司中找到薪水至少超过一名员工的人
ALL：
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > ALL
(SELECT salary
FROM Staff
WHERE branchNo = ‘B003’);//在分公司中找到薪水超过所有人的员工
EXISTS/NOT EXISTS
只用于Subquery子查询，结果为true/false，子查询返回的结果表中至少有一行则为true，返回为空时结果是false，NOT EXISTS的结果与上述相反。
As (NOT) EXISTS check only for existence or non-existence of rows in subquery result table, subquery can contain any number of columns.只检查子查询中是否存在，子查询可以包含任意数量的列。
使用方式为（SELECT * ...）
SELECT staffNo, fName, lName, position
FROM Staff s
WHERE EXISTS
(SELECT *
FROM Branch b
WHERE s.branchNo = b.branchNo（这一句很必要）
AND city = ‘London’);//找到所有在伦敦分公司工作的人
等价于JOIN 连接构造
SELECT staffNo, fName, lName, position
FROM Staff s, Branch b
WHERE s.branchNo = b.branchNo AND
city = ‘London’;
SUMMARY
SELECT [DISTINCT | ALL]
{* | [columnExpression [AS newName]] [,...] }
FROM TableName [alias] [, ...]
[WHERE condition]
[GROUP BY columnList]
[HAVING group condition]
[ORDER BY columnList]
UPDATE更新
UPDATE TableName
SET columnName1 = dataValue1
[, columnName2 = dataValue2...]
[WHERE searchCondition]
-- TableName can be name of a base table or an updatable view（TableName可以是基表的名称或可更新视图的名称
-- SET clause specifies names of one or more columns that are to be updated.（SET语句指定要更新的一个或多个列的名称
-- WHERE可选，WHERE clause is optional
– if omitted, named columns are updated for all rows in table;如果省略，则会更新表中所有行的命名列
– if specified, only those rows that satisfy searchCondition are updated.如果指定，则只更新那些满足搜索条件的行
-- 新的数据必须和列属性的数据相同
UPDATE All Rows更新所有行：
例1：Give all staff a 3% pay increase.
UPDATE Staff
SET salary = salary*1.03;
例2：Give all Managers a 5% pay increase.
UPDATE Staff
SET salary = salary*1.05
WHERE position = ‘Manager’;
UPDATE Multiple Columns更新多个列
UPDATE Staff
SET position = ‘Manager’, salary = 18000
WHERE staffNo = ‘SG14’;提拔David Ford(员工号staffNo=‘SG14’)为经理，并将他的薪水改为1.8万英镑
DELETE删除
DELETE FROM TableName
[WHERE searchCondition]
TableName can be name of a base table or an pdatable view.表名称可以是基表的名称或可更新视图的名称
searchCondition(搜索条件） is optional; if omitted, all rows are deleted from table. This does not delete table. If search_condition is specified, only those rows that satisfy condition are deleted.搜索条件可以选，如果省略，会删除所有行但不删除表，如果指定，只删除满足条件的行。
DELETE Specific Rows删除指定行
例1：Delete all viewings that relate to property PG4.删除和PG4属性有缘的所有视图
DELETE FROM Viewing
WHERE propertyNo = ‘PG4’;
例2：Delete all records from the Viewing table.删除Viewing表中的所有记录
DELETE FROM Viewing;
357
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
