• What have we achieved so far using SELECT ? — Retrieve data from all the rows and columns (whole table) — Retrieve data from all the rows and select columns — Retrieve data from select rows and columns • Sometimes we want to re-format the output from SELECT : — e.g., for reports... • Examples of additional processing: — Eliminate duplicates — Sort and/or group the results — Rename column headings — Calculate totals, averages, etc. (often called aggregates) — Combine results from different tables 使用SELECT可以实现读取所有和特定数据 还可以消除重复数据 对结果排序分组 重新命名列标题 计算总数平均数（聚合） 合并表

 Use of DISTINCT • List the property numbers of all properties that have been viewed列出已查看过的的房产编号 • Query1: SELECT propertyNo FROM Viewing; • Query2: SELECT DISTINCT propertyNo FROM Viewing;DISTINCT用于消除重复数据

ORDER BY • Produce a list of salaries for all staff, arranged in descending order of salary 输出按薪水降序排序的清单

通过ORDER BY 以特定列数据为基准默认降序排列

Calculated Fields • Produce a list of monthly salaries for all staff, showing the staff number, the first and last names, and the salalry details 计算 生成月薪清单

可以在SELECT 对应列名称进行计算来处理相关数据

Renaming Columns • When new fields are calculated we can name them using AS 重命名列

使用AS重命名列 “列原名 AS 列现名”

SQL Aggregate Functions • We do not want to just retrieve data • We also want to summarise data • Aggregate functions compute summarization (or aggregation) of data • Aggregate functions — SUM — AVG — MIN — MAX — COUNT SQL聚合数据 通过聚合函数计算汇总数据

SUM, MIN, MAX, AVG • Find the minimum, maximum, average and sum of staff salary找出最小最大均值和总值

COUNT(*) • Counts the number of rows in a table — Including the rows that have duplicates and nulls 统计表中所有行数 包括重复和空值行

SELECT列表时不能引用其他列  

• count(列名) 返回列名指定列的行数

GROUP BY • Aggregate functions help us to summarise the whole column(s) of data into one row • Sometimes we want to group data before applying aggregate functions — This gives us ‘subtotals’ rather than ‘overall total’ • GROUP BY is used to achieve that • Find the number of staff working in each branch and the sum of their salaries 分组求和

 GROUP BY 列名

HAVING 用于筛选分组后数据

Adding Data to a Table using INSERT

Modifying & Deleting Data • Changing specific values in a table更改特定值: UPDATE Staff SET Salary = 1.05 * Salary WHERE Position = 'Director'; • Deleting specific rows删除特定行: DELETE FROM Staff WHERE Fname IS NULL; • Deleting all rows of a table删除表所有行: DELETE FROM Staff; • Completely removing a table is a DDL operation删表: DROP TABLE Staff;

A NULL value can be used to represent several situations: • Don’t care; Don’t know; Don’t know yet; Used to know! • SQL has special rules and logic to handle NULLs: SELECT * FROM Staff WHERE Fname IS NULL; • NB. WHERE Colname = 'NULL' does not work! • Can also say: WHERE Colname IS NOT NULL • NULLs can be useful, difficult, or dangerous • Use NULLs wisely! 对于空值 有特殊的处理方式

合并结果表

• Some DBMSs use MINUS instead of EXCEPT • For set operations, the tables must be union-compatible — i.e., have the same number and types of columns

对于集合操作必须联合兼容即列数量类型相同

List the cities with both a branch office & a property for rent:

Cities with branch office OR a property for rent: UNION Cities with branch office but NO props for rent: EXCEPT      交为INTERSECT 或为UNION 有1无2为EXCEPT

Using Subqueries - Nested SELECTs • If we know that a SELECT statement will produce a single value, • we can use this value directly in a simple predicate • Example: find all members of staff who earn more than Smith:使用子查询嵌套

• If the SELECT statement is expected to produce a list of values, we can use the IN , ANY , ALL or EXISTS keywords to operate on the list • Example: find those members of staff who are not managers but earn more than at least one manager:

ANY 任意一个• ALL : Predicate is true if test is true for all list elements 全对为真• IN : Predicate is true if the test element is in the list存在为真 • EXISTS : Predicate is true if list is non-empty非空为真

Summary So Far... • SQL is a powerful but “quirky” query language • SQL is not like other programming languages (no variables) • You can answer many kinds of question... • You can build up complex queries from simpler sub-queries • It often requires thought to compose complex queries... • Approach: Break problem into sub-parts, and then build up final query... • Next lecture: queries using multiple tables (joins)