第一章 对象导论

1、基本特性

普适性 组成程序 可互相嵌套 有对应类型 可替代性

2、观点：任何程序都是程序员所设计系统的一种仿真

3、对象提供服务

视作对象的优点：提高对象的内聚性

• 高内聚：各方面组合的很好

4、隐藏的具体实现

• 类创建者 / 客户端程序员：访问控制

  • 边界：public private protected

5、复用的具体实现

• 组合 / 聚合（动态的组合）：创建成员对象

• 先考虑组合，后继承：前者较简单灵活

6、继承

• 可构建类型层次结构

• 类型等价性：导出类和基类拥有相同的类型

  • 修改：为导出类添加新方法 / 覆盖（纯粹替代 or 扩展）

7、多态

• 泛化：视作基类而非对象

• 后期绑定：直到运行才可确定代码地址

• 向上转型 up cast：一个基类在继承图中向上移动

8、单根继承结构

• 终极基类：Object

• 相对 C++ 可实现垃圾回收

9、容器

• 定义上近似等同于集合，如 C++ 中的 STL 容器

• 提供不同类型的接口和外部行为

• 对同一操作不同容器效率可能不同

• 参数化类型（范型）：向下转型（不安全）

1
2

// 创建一个存储Shape的顺序表
ArrayList<shape> shapes = new.ArrayList<shape>();

10、对象的创建和生命期

• 基本逻辑假设：对象复杂化 - 查找和释放空间的开销不会对创建对象造成过大冲击

• Java 是在堆栈中存储和释放空间的动态内存分配方式

  • 反观 C++：需要在编程时就确定生命周期，牺牲了部分灵活性，侧重效率控制

• 使用垃圾回收器，自发发现不再被使用的对象，有效避免内存泄漏

11、必须编写异常处理的代码

高级操作 第 22 - 30 章

视图

1、def：虚拟表，仅包含使用时动态检索的一个 SQL 查询

2、规则

• 必须唯一命名

• 需要访问权限

• 视图可嵌套

• 视图中的 ORDER BY 语句会被检索中的 ORDER BY（如果有）覆盖

• 不可添加索引或触发器、默认值，但可与表一同使用

3、使用

• 创建：CREATE VIEW

• 查看所创建视图：SHOW CREATE VIEW viewname

• 删除视图：DROP VIEW viewname

• 过滤视图、简化计算字段

4、重新格式化检索出的数据

把多次需要的结果通过转化为视图存放

1
2
3
4
5

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name),'(', RTrim(vend_country),')')
		AS vend_title
FROM vendors
ORDER BY vend_name;

再次检索出数据：

1
2

SELECT *
FROM vendorlocations;

5、更新视图：CREATE OR REPLACE VIEW

• 以下情况视图不可更新：

  • 存在 GRUOP BY 分组
  • 联结、子查询、并
  • 聚集函数、DISTINCT
  • 导出计算列

存储过程

1、使用原因

• 封装处理，简化操作、提高性能

• 无需反复建立处理步骤，保证了数据完整性

• 简化变动管理 - 安全性

2、执行存储过程：CALL

1
2
3

CALL productpricing(@pricelow,
                   @pricehigh,
                   @priceaverage);

• mysql 中所有变量名都必须以 @开始

• mysql 中注释以–开头

3、创建存储过程：CREATE PROCEDURE

创建一个新的存储过程，没有返回数据：

1
2
3
4
5

CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;

• DELEMITER：指定新的语句分隔符，避免；被 mysql 命令行实用程序解释为存储过程的成分

4、删除存储过程：DROP PROCEDURE

5、使用参数

• 存储过程的参数允许的数据类型与表中一致

  • 记录集不是允许的类型。记录集：从指定数据库中检索到的数据的集合

• 创建布尔值：IN 参数名 BOOLEAN

• IF / ELSEIF / ELSE 子句

6、检查存储过程：SHOW CREATE PROCEDURE

• 获取详细信息的存储过程列表：SHOW PROCUDURE STATUS

游标 cursor

1、使用游标

• 必须先声明再打开，结束后关闭

• 创建与访问：DECLARE / FETCH

• 打开与关闭：OPEN / CLOSE CURSOR

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders
END;

-- 打开游标
OPEN ordernumbers;
-- 关闭游标
CLOSE ordernumbers;
-- 检索order_num列到一名为o的局部变量中（不处理检索出的数据）
FETCH ordernumbers INTO o;

• UNTIL done END REPEAT：反复执行直到布尔值 done 为真

  • 手动退出：LEAVE

• CONTINUE HANDLER：条件出现时才被执行

1
2

DECLARE done BOOLEAN DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done = 1;

说明在 SQLSTATE ‘02000’出现时，把 done 的值从默认值 0 设置为 1

2、DECLARE 的次序

• 定义顺序：DECLARE 定义的局部变量 - 游标 - 句柄

  • 句柄（Handle）：用来标识对象或者项目的标识符

触发器

1、需给出信息：

• 最好每个数据库中触发器名称唯一

• 关联表

• 应响应活动与执行时机

2、创建：CREATE TRIGGER

• 相应以下任意语句：DELETE INSERT UPDATE

1
2

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added'

• 仅表可用触发器，视图不行

  • 每张表最多支持 6 个触发器（3 种语句 * 2 个时机）
  • 某时机前触发 BEFORE 失败：则若有 AFTER 触发器，默认不执行

3、删除：DROP TRIGGER

4、使用

• 可引用 NEW 虚拟表，以访问被插入行

  • 可在 BEFORE INSERT 触发器中更新 NEW 表中的值
  • 对于 AUTO_INCREMENT，INSERT 前置 0，后为新值

• DELETE 触发器可引用 OLD 访问被删除行

  • 仅只读，不可再更新
  • BEFORE DELETE 相对 AFTER ~ 优点：不可存档，DELETE 本身将被放弃

1
2
3
4
5
6

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW 
BEGIN
	INSERT INTO archive_orders(order_num, order_date, cust_id);
	VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

• UPDATE 触发器可用 NEW、OLD

  • BEFORE UPDATE 触发器中，NEW 中可改动的 UPDATE 值可被更新
  • OLD 表仅可读，不可更新

• 触发器是自动执行的，可能需要特殊权限

• 不支持在触发器中使用 CALL 语句

5、展望

• 内容仍较初级，有诸多改进空间

• 可用于保证数据一致性、创建审计跟踪

事务处理 transaction processing

1、一些概念

• 事务：一组 SQL 语句

• 回退 rollback：撤销指定语句的过程

• （隐含）提交 commit：（自动）将未存储语句写入表

• 保留点 savepoint：设置的临时占位符（placeholder），可回退

2、使用事务处理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 回退
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

-- 提交（例中两个DELETE都返回成功才COMMIT）
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

-- 使用保留点
SAVEPOINT delete1;
ROLLBACK TO delete1;

• 回退后不再显示空结果

• 三种操作执行流结束后事务均会自动关闭

• 保留点个数越多越好，灵活回退

3、更改默认的提交行为

• 仅针对每个连接，不针对服务器

1
2

-- 此时不自动提交
SET autocommit =  0;

* 全球化与本地化

1、使用

• 显示字符集、校对顺序：SHOW CHARACTER SET / COLLATION；

• 显示特定字符集：SHOW VARIABLES LIKE ‘character%’；

• 与创建表不同的校对顺序查询

1
2

SELECT * FROM customers;
ORDER BY lastname, firstname, CLLATE latin1_general_cs;

2、串在字符集间的转换：Cast () / Conver () 函数

安全管理

1、访问控制

• 谨慎使用 root 登录

• 给予用户适当的访问权

2、管理用户

• 获取所用用户列表

1
2

USE mysql；
SELECT user FROM user；

• 创建用户账号

  • IDENTIFIED BY：指定纯文本口令
  • 或使用 GRANT / INSERT 语句（不好）

1

CREATE USER bob IDENTIFIED BY 'mypassword'

• 重命名：RENAME USER

• 删除：DROP USER

• 设置管理权限：SHOW GRANTS FOR 用户名

  • * 表示无权限

3、GRANT / REVOKE 语句

• 需给出的信息：需授予权限 所需库或表 用户名

1
2
3
4
5
6

-- 给予权限
SHOW GRANTS FOR bob;
GRANT SELECT ON crashcourse.* to bob;
-- 撤销权限
REVOKE GRANTS FOR bob;
REVOKE SELECT ON crashcourse.* to bob;

• 可更改权限（P202）

• 可提前设置授权：对所涉及对象，可不存在就设定权限

• 简化多次授权

1

GRANT SELECT, SELECT ON crashcourse.* TO bob;

4、更改口令：SET PASSWORD

1

SET PASSWORD FOR bob = PASSWORD('newpassword');

* 数据库维护

1、备份

• 使用 mysqldump 转储到外部文件

• 使用 mysqlhotcopy 复制到另一数据库

• 使用 BACKUP TABLE / SELECT INTO OUTFILE 转储

  • 复原：RESTORE TABLE
  • 刷新：FLUSH TABLES，保证所有数据及索引被写入磁盘

2、维护

• ANALYZE TABLE：检查表键是否正确

• CHECK TABLE：检查各类问题

  • CHANGED：最后一次检查以来改动的表
  • ENTENDED：最彻底检查
  • FAST：未正常关闭表
  • MEDIUM：检验所有删除链接及键检验
  • QUICK：快扫

3、诊断启动问题

–safe-mode

–help、–verbose：显示全文本消息

4、日志文件

• 错误日志：hostname.err

• 查询日志：hostname.log

• 缓慢查询日志：hostname-slow.log

改善性能

1、EXPLAIN 语句：使 mysql 解释如何解释一条 SELECT 语句

2、DELAYED 关键字：把控制立即返回给调用程序

核心操作 第 14 - 21 章

子查询 subquery

1、处理顺序：从内向外

1
2
3
4
5
6
7

SELECT cust-name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                     FROM orderitems
                                     WHERE prod_id = 'TNT2'));

2、格式化：分解多行 适当缩进

3、基本思想：将硬编码转换为子查询，减少代码量

• 列必须匹配：SELECT 子句和 WHERE 子句中的列数量相同

• 最常见：用于 WHERE 子句的 IN 操作符和填充计算列

4、相关子查询 correlated subquery

• 使用完全限定列名，指定调用 customers 表中该列名（否则默认自身比较，相当于自检）；若非完全限定引用一个具有二义性的列名，将返回错误

1

WHERE orders.cust_id = customers.cust.id;

• 注意限制有歧义性的列名

联结表

1、关系

• 主键 primary key：唯一标识

• 外键 foreign key：某个表的一列，包含另一表主键值

2、可伸缩性 scale well：可适应不断增加的工作量并良好运作

3、创建联结（等值联结 equijoin）

1
2
3
4

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend.id = products.vend.id
ORDER BY vend_name, prod_name;

• 笛卡尔积：由没有联结条件的表关系返回的结果（def 离散）

  • 因而需保证所有联结都有 WHERE 子句
  • 交叉联结 cross join

4、* 内部联结

1
2
3

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend.id = products.vend_id;

• 联结条件：使用特定的 ON 子句给出

5、实例：相对子查询优化效率（具体问题具体分析）

1
2
3
4
5

SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
	AND orderitems.order_num = orders.order_num
	AND prod_id = 'TNT2';

高级联结

1、使用表别名

1
2
3
4

SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS
vend_title
FROM vendors
ORDER BY vend_name;

作用：

• 缩短语句

• 允许单条 SELECT 多次使用相同表，如：

1
2
3
4
5

SELECT cust_name, cust_contact
FROM customers AS a, orders AS i, orderitems AS ai
WHERE a.cust_id = i.cust_id
	AND ai.order_num = i.order_num
	AND prod_id = 'TNT2';

2、自联结

• 作为外部语句来替代从相同表中检索使用的子查询

1
2
3
4

SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
	AND p2.prod_id = 'DTNTR';

注：实际问题，效率需具体分析，不一定优于子查询

1
2
3
4
5

SELECT p1.prod_id, p1.prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                FROM products
                WHERE prod_id = 'DTNTR');

3、自然联结

• 排除多次出现的列，仅使每个列返回一次（仅选择唯一列）

  • 通常对表使用通配符 SELECT *

1
2
3
4
5
6

SELECT c.*, o.order_num, o.order_date,
		oi.prod_id, oi.quantity, OI.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND prod_id = 'FB';

4、外部联结 OUTER JOIN （较少使用）

• 联结包含了没有关联行的行

• 类型：左外部联结 右外部联结

  • 使用时需要用 RIGHT / LEFT 关键字指定行范围（从右 / 左侧的表中选择所有行）
    • mysql 不支持 *= 等一系列简化操作符！
  • 可通过颠倒 FROM / WHERE 子句互换使用

5、带聚集函数的联结

组合查询

1、并 union：执行多个查询，将结果作为单个查询结果集返回

• 使用情景：单个查询从多个表返回类似数据；对单个表多个查询，按单个返回

• 多个 WHERE 子句的单条查询可以达到相同的作用

• 使用于较为复杂过滤条件

2、UNION 规则

• 由两条及以上 SELECT 语句构成

• 每个查询包含相同表达式

• 列数据类型兼容

3、功能

• 自动去重（默认）

  • 返回所有匹配行：UNION ALL

• 与 ORDER BY 排序：必须出现在最后一条 SELECT 语句后

1
2
3
4
5
6
7
8

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN(1001, 1002)
ORDER BY vend_id, prod_price;

全文本搜索

1、支持的数据库引擎：MyISAM（InnoDB 不支持）

2、使用全文本搜索：随数据改变不断索引被搜索的列

• FULLTEXT ()：索引列

  • 不要在导入数据使用，降低导入速率

• Match ()：指定被搜索的列

  • 使用值必须与 FULLTEXT () 中定义相同
  • 多个列索引，其次序与 FULLTEXT () 中保持一致

• Against ()：指定要使用的搜索表达式

  • 文本中，靠前的行的给定词等级值高于后继
  • 对多个搜索项，包含更多匹配词的行等级值更高

• 索引的搜索速度相当快

3、查询扩展

1
2
3

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

• 步骤

  • 先进行全文本搜索
  • 检查匹配行，判定有用词（与给定词相关词）
  • 运用有用词和原给定词再次进行全文本搜索

• 文本行数越多，查询扩展结果越好

4、布尔文本搜索

1
2
3

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE)

匹配词 heavy，但排除任何包含以 rope 开始的词

• 布尔方式 boolean mode

  • 提供：匹配词 排斥词 排列提示 表达式分组 etc.
  • 没有定义 FULLTEXT 索引也可使用

• 全文本布尔操作符（P128）

  • 如 + /- ：包含 / 排除
  • < >：包含，并减少 / 增加等级值
  • -：取消一个
  • *：词尾通配符

5、默认注意事项

• 短词（<=3 个字符的词）从索引中被排除

• 内建的非用词（stopword）被忽略

• 出现行数高于 50% 的词作为非用词忽略

• 少于三行的文本不返回结果

• 忽略词中的单引号

• 中文、日文等不具有词分隔符的语言无法正确返回结果

数据插入 INSERT

1、插入完整行

• 每组值用一对圆括号括起来，以逗号分隔

• 多使用 cust_id 明确插入，可在表结构改变后继续发挥作用

• INSERT 一般无输出结果

• 必须给出 VALUES 的正确数目；不提供列名则必须对每列给出一个值

1
2
3
4
5
6

INSERT INTO customers(cust_name,
                     cust_contact,
                     cust_country)
VALUES('Peter',
      NULL,
      'UK');

• 省略列：NULL

  • 表的定义中给出默认值：不给定值时使用

• 提高整体性能：用 INSERT LOW_PRIORITY INTO 降低插入的优先级

2、插入多行

• 使用多条 INSERT 语句（可一次性提交）

3、插入检索所得数据

1
2
3
4
5
6
7
8
9
10

INSERT INTO customers(cust_name,
                     cust_contact,
                     cust_country)
VALUES('Peter',
      NULL,
      'UK');
SELECT cust_name,
		cust_contact, 
		cust_country
FROM custnew;

其中，列名不一定匹配，仅作顺序填充

• 可包含 WHERE 子句以过滤插入数据

更新和删除数据

1、更新：UPDATE 语句

• 不省略 WHERE 语句，避免更新表中所有行

• 语句组成：要更新的表名 列名及其新值 要更新行的条件

1
2
3
4

UPDATE IGNORE customers
SET cust_email = 'balabala@qq.com',
	cust_name = 'bob'
WHERE cust_id = 10005;

• 更新多个列：逗号分隔（最后一行除外）

• IGNORE 关键字：在发生错误时依旧更新

• 设为 NULL：删除某列值

2、删除：DELETE 语句

• 不省略 WHERE 语句

• 语句组成：要删除的表名 要删除行的条件

  • 注：即便删除表中所有行，DELETE 不执行删除表操作
  • 删除所有行使用 TRUNCATE TABLE

注：

• 两者使用前最好先用 SELECT 语句测试，保证过滤的记录正确

• 在使用强行引用完整数据库的更删时，仅可操作与其他表无关的数据行

创建和操纵表

1、创建：CREATE TABLE 语句

• 需给出信息：无冲突表名 列名及定义

• NULL、NOT NULL：允许 / 阻止插入没有值的列

  • 空串‘ ’视为一有效值

• 主键可在创建时或创建后定义

• AUTO_INCREMENT：自动增量

  • 每表仅一个列允许，以作为主键值

• DEFAULT 数值：指定默认值

  • 仅可为常量，mysql 不支持函数

• ENGINE=InnoDB（MyISAM，etc.）：指定所用引擎

  • 外键不可跨引擎调取

2、更新表：ALTER TABLE

• 常用于定义外键

1
2
3

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_order
FOREIGN KEY (order_num) REFERENCES orders (order_num);

• 更改表步骤

  • 用新布局创建新表
  • INSERT SELECT 复制数据
  • 检验新表，重命名或删除旧表，命名新表
  • 重新创建触发器、索引等

3、删除表：DROP TABLE

4、重命名表：RENAME TABLE

1

RENAME TABLE table1 TO table2

进阶操作 第 9-13 章

正则表达式

• 作用：匹配文本

• 基本字符匹配：SELECT 列名 FROM 表名 WHERE 列名 REGEXP ‘值’ ORDER BY 子句名

  • . 特殊字符表匹配任意一个字符，如‘.000’可匹配 1000 和 2000，etc
  • 与 LIKE 匹配的区别：出现的匹配文本，其相应行将被 REGEXP 返回
  • 默认匹配不区分大小写，使用 BINARY 可区分大小写

• OR 匹配：SELECT 列名 FROM 表名 WHERE 列名 REGEXP ‘值 1 | 值 2’ ORDER BY 子句名

  • 匹配一组特定字符：SELECT 列名 FROM 表名 WHERE 列名 REGEXP ‘[值 1 值 2 值 3]’ ORDER BY 子句名

• 匹配范围：SELECT 列名 FROM 表名 WHERE 列名 REGEXP ‘[值 1 - 值 2]’ ORDER BY 子句名

• 匹配特殊字符：SELECT 列名 FROM 表名 WHERE 列名 REGEXP ‘\.’ ORDER BY 子句名

  • 引用元字符：\f 换页 \n \r 回车 \t \v 横、纵向制表
  • 匹配反斜杠：\\
    • 解释：第一个反斜杠由 Mysql 解释，第二个才是正则表达式解释

• 匹配字符类 character class（P58）

• 匹配多个实例：如结尾 s 可选用？

• 匹配特定位置文本：定位符

  • ^ 文本开始 $ 文本结尾 :<: 词的开始 :>: 词的结尾
    • 注意 ^ 的另一种用法是集合中作 [] 取和
    • 可以配套使用 ^$，使用法同 LIKE

• 正则表达式的测试：使用 SELECT，返回 1 匹配，反之不匹配

  • 优点：无需调用数据库表

计算字段

1、字段 field：意义同列，用于计算字段的连接

2、拼接 concatenate：联结值成为一个单个值

• 实际操作：在 SELECT 中使用 Concat () 函数

  • SELECT Concat (值 1，值 2) FROM 表名 ORDER BY 子句名
  • 或使用 RTrim () / LTrim () 函数，可删除数据右侧 / 左侧多余空格来整理数据

3、别名 alias / 导出列 derived column：字段或值的替换名

• SELECT Concat (值 1，值 2) AS 别名 FROM 表名 ORDER BY 子句名

4、基本计算：四则运算符

• 测试计算：省略 FROM 子句

数据处理函数

1、可移植性：多数函数不同 SQL 存在差异，做好注释

2、文本处理（P69）

• Left / Right ()：返回左 / 右侧字符

• Length ()：返回串长

• Locate ()：找出某子串

• SubString ()：返回子串字符

• Lower/Upper ()：返回小 / 大写

• LTrim/Rtrim ()：去掉左 / 右侧空格

• Soundex ()：将任何文本串转换为描述其语音表示的字母数字模式

  • 对串进行发音比较（输入纠正，匹配输入项读音接近字串）

3、时间和日期处理（P71）

• Date ()：返回日期

• Time ()：返回时间

4、数值处理（P74）

• Abs ()：返回绝对值

• Sin() / Cos() / Tan()

• Exp() / Sqrt()

• Mod()

• Pi ()：返回圆周率

• Rand ()：返回一个随机数

汇总数据

1、聚集函数 aggregate function

• def：行组上计算和返回单个值的函数

• AVG ()：SELECT AVG (列名) FROM 表名

  • 仅用于确定特定数列，且列名必须以函数参数给出
  • 忽略值为 NULL 的行

• COUNT ()：SELECT COUNT (*) AS 别名 FROM 表名，统计行个数

  • COUNT (*) 统计 NULL 和非空值
  • COUNT（列名）统计特定值，忽略值为 NULL 的行

• MAX ()：SELECT MAX (列名) AS 别名 FROM 表名

  • 可作返回任意列的最大值，按列排序的文本数据返回尾行
  • 忽略值为 NULL 的行

• MIN ()：SELECT MIN (列名) AS 别名 FROM 表名

  • 可作返回任意列的最小值，按列排序的文本数据返回首行
  • 忽略值为 NULL 的行

• SUM ()：SELECT SUM (列名) AS 别名 FROM 表名

  • Sum (列名) 返回各行该列数据之和
  • 忽略值为 NULL 的行

• 指定聚集范围 ALL / DISTINCT

• 组合聚集函数：SELECT COUNT (*) AS 别名， MIN (列名) AS 别名，MAX (列名) AS 别名，……，FROM 表名

分组数据

1、创建分组：SELECT COUNT (*) AS 别名 FROM 表名 GROUP BY 列名

• 对每个组的结果分别进行聚集，所有列结果一同计算

  • 可包含任意数目的列
  • 每列可为检索列或有效表达式，不能为聚集函数
  • SELECT 使用表达式 - GROUP BY 必须使用相同表达式，不能使用别名

• WITH ROLLUP：得到每个分组及其汇总级别的值

  • SELECT 列名，COUNT (*) AS 别名 FROM 表名 GROUP BY 列名 WITH ROLLUP

2、过滤分组：HAVING（WHERE 仅可过滤行）

• 适用所有 WHERE 句法

• 在数据分组后进行

  • WHERE 在数据过滤前进行，因此分组中不包含 WHERE 排除的行

3、分组和排序

保证数据正确排序：GROUP BY + ORDER BY

运输层服务

1、工作位置：端系统

2、分类：用户数据报协议 UDP 传输控制协议 TCP

3、IP 服务：尽力而为交付（best-effort delivery server）的不可靠模型

多路复用与多路分解

1、多路分解

• def：将运输层报文段中的数据交付到正确的套接字

  • 每个套接字都有唯一确定的标识符
  • 每个报文段需有特殊字段来指示该报文段所需交付到的套接字

• 端口字段

  • 长度：32 比特
  • 包含信息：源端口号字段 目的端口号字段
    • 端口号：16 比特的数，大小在 0~65535
    • * 周知端口号：0~1023（受限）

2、多路复用

• def：从不同套接字收集数据块、封装首部信息生成报文段并传递到网络层的一系列工作

3、无连接的多路复用与多路分解

• 创建 UDP 套接字

  • 分配方式：自动 or bind () 关联特定端口
  • 组成：二元组，包括 目的 IP 地址 和 目的端口号
  • 源端口号和目的端口号的反转：服务器使用 recvfrom () 方法

4、面向连接的多路复用与多路分解

• 创建 TCP 套接字

  • 组成：四元组， 源 IP 地址、源端口号；目的 IP 地址、目的端口号

• 不同源 IP 地址 / 端口号，其定位到的套接字不同

*5、顺序端口扫描：nmap

无连接运输：UDP

1、特征

• 无连接传输：发送和接收方的运输层实体之间没有握手

2、UDP 的优劣

优点

• 精细控制、立即交付

• 无需连接建立：不引入连接时延

• 无连接状态：不维护连接状态，不跟踪对应参数

• 分组首部开销小：仅需 8 字节（TCP：20 字节）

缺点

• 缺乏拥塞控制带来的高丢包率

• 引起并击垮发送方的 TCP 会话

3、UDP 报文段结构（P132）

• 组成：源端口号 目的端口号 长度 检验和 应用数据

  • 端口号：使得目的主机可执行分解功能
  • 长度字段：指示报文段的字节数（首部 + 数据）
  • 检验和
    • 功能：用于确定传输过程中比特是否改变
    • 操作：反码运算，溢出回卷（P133）
    • 遵循原则：端到端原则

• 仅提供差错检测，无差错恢复机制

可靠数据传输原理

1、特征

• 数据通过一条可靠信道传输，为上层实体提供服务

• 可将较低层直接视为不可靠的点对点信道

• 假设底层信道不会对分组重排序，交付分组可能丢失

2、构造

• 理想模型 rdt1.0

  • 有限状态机 Finite-State Machine，FSM：发送端与接收端各自的机器
  • 所有分组从发送方流向接收方
  • 接收端不提供任何反馈

• 具有比特差错信道 rdt2.0

  • 本质：停等协议
  • 功能：查错检测 接收方反馈 重传（产生冗余数据分组）
  • 自动重传协议 Automatic Repeat reQuest，ARQ：基于肯定确认（ACK）和否定确认（NAK）的重传机制的可靠数据传输协议
  • 处理 ACK / NAK 受损情况：发送方发送加入数据分组序号的新字段
  • 改进
    • rdt2.1：加入接收方到发送方的肯定和否定确认
    • rdt2.2：使用 ACK 0 或 ACK 1 的确认信息

• 具有比特差错的丢包信道 rdt3.0

  • 本质：比特交替协议
  • 倒计数计时器 countdown timer：发送分组时启动、响应中断、终止

3、流水线（pipelining）可靠数据传输协议

• 问题：低利用率的网络协议可能限制底层网络硬件所提供的能力

• 解决：不以停等方式运行，允许发送多个分组而无需等待确认

• 差错恢复方法

  • 回退 N 步（GBN 协议，P145）

    • 本质：滑动窗口协议

    • 设基序号为 base，nextseqnum 位下一个最小的未使用序号：

      • 序号范围 [0, base - 1]：已发送并确认的分组
      • 序号范围 [base, nextseqnum - 1]：发送但未确认的分组
      • [nextseqnum, base + N - 1]：若有上层数据将被立即发送的分组
      • >= base + N：当前流水线不可被使用的分组

      其中 N 为窗口长度，分组的序号范围是 [0, 2^k - 1]，k 为比特数

    • 注：涉及序号运算一律使用模 2^k 运算

  • 选择重传（SR 协议，P150）

    • 每个分组拥有自己的逻辑定时器，超时后发送单个分组
    • 接收到分组的 ACK 后，标记当前窗口基序号的分组为已接收，并且接收方需重新确认已收到过的序号小于当前窗口基序号的分组；若该分组序号等于 send_base，则移动窗口基序号到具有最小序号的未确认分组处

  • 因特网下的重新排序问题：假定分组的最大存活时间不会超过某个固定最大时间量（默认大约 3min）来避免信道中的冗余分组

面向连接的运输：TCP

1、建立连接：三次握手，客户发送 - 服务器响应 - 客户响应

• 第三个报文段承载有效载荷

• 组成：一对主机上的缓存、变量及与进程连接的套接字

• 辨析：其间的网络元素不参与分配任何缓存和变量

2、最大报文段长度 Max Segment Size，MSS

• 辨析：仅报文段中应用层数据的最大长度，而非包括首部

• 由本地发送主机的最大链路层帧长度（最大传输单元 Max Transmission Unit，MTU）设置

• 要求：保证适合单个链路层帧

• 典型值大小：1460 字节（以太网与链路层协议中都具有 1500 字节的 MTU）

3、TCP 报文段结构（P154）

• 序号字段和确认号字段：各 32 比特，用于实现可靠数据传输

  • 可靠传输服务的关键部分
  • 序号是该报文段首字节的字节流编号，确认号是主机期望从另一主机收到的下一字节的序号
  • 功能：提供累积确认，仅确认到流中至第一个丢失字节为止的字节

• 首部长度字段：4 比特，指示首部的长度

• 选项字段：可选和变长

• 标志字段：ACK 确认 RST、SYN、FIN 建立和拆除连接 PSH 提交给上层 URG 指示紧急数据

4、实例：Telnet

• 回显 echo back：远程主机回送每个字符的副本给用户，并显示在 Telnet 用户的屏幕上

• 被捎带 piggybacked：指 对客户 to 服务器的数据的确认 被装载在一个承载服务器 to 客户的数据的报文段中

5、往返时间的估计与超时

• 往返时间：仅为传输一次的报文段测量 SampleRTT

  • 指数加权移动平均 EWMA：更新估计往返时间（EstimatedRTT）

  $$EstimatedRTT = （1 — α）* EstimatedRTT + α * SampleRTT$$

  其中，[RFC 6298] 给出的 α 推荐值为 0.125。

  • DevRTT：RTT 偏差，用于衡量测量往返时间偏离估计往返时间的程度

  $$DevRTT = (1 - β) * DevRTT + β * |SampleRTT - EstimatedRTT|$$

  其中，β 的推荐值为 0.25。

• 超时间隔

$$TimeoutInterval = EstimatedRTT + 4 * DevRTT$$

​ 其中，TimeoutInterval 的推荐初始值为 1s。

6、可靠数据传输

• TCP 服务是在 IP 服务的尽力而为服务上创建的一种可靠数据传输服务

• 超时间隔加倍：避免链路拥塞

• 收到三个冗余 ACK 后执行快速重传

• 差错恢复机制：GBN 与 SR 的结合体，可有选择地确认失序报文段，而非累积确认最后一个正确接收的有序报文段

7、流量控制（P165）

• 辨析：拥塞控制是为防止因 IP 网络拥塞而被遏制的情形；而流量控制是为消除发送方使接收方缓存溢出的可能性，是一个速度匹配服务

• 方式：一对发送方各自维护一个接收窗口（rwnd）

• 定义变量

  • LastByteRead：另一主机读出流中的最后一个字节的编号
  • LastByteRevd：放入接收缓存流中的最后一个字节的编号

$$LastByteRevd - LastByteRead \le RcvBuffer$$

$$rwnd = RcvBuffer - [LastByteRevd - LastByteRead]$$

• 注：当接收主机空间耗尽时，由于发送方尽在有数据或有确认要发送是才会发送报文段，因此当接收主机的 rwnd = 0 时，主机将发送仅一个字节数据的报文段以清空接收主机 RcvBuffer，并返回一个包含非 0 的 rwnd 值

8、连接管理

• 3 次握手

  • 客户端发送特殊报文段 to 服务器端，首部的 SYN 比特置 1
  • SYNACK 报文段：服务器端 SYN 比特被置为 1，确认号字段替换为客户端 + 1，服务器选择自己的初始序号
  • 客户端分配缓存和变量，连接建立，SYN 比特被置为 0

• TCP 状态（P168）

  • SYN cookie：不为未经确认的请求分配资源以杜绝洪泛攻击

拥塞控制原理

1、不同情形

• 理想情况，两个发送方、无限缓存的路由器：分组到达速率接近链路容量时，分组经历巨大的排队时延

• 两个发送方、有限缓存的路由器：还附加执行重传以补偿因缓存溢出而丢失的分组

• 4 个发送方与有限缓存路由器的多跳路径：被丢弃分组，其上游路由器转发到丢弃过程中使用的传输容量被浪费

2、方法

• 端到端：超时或三次冗余 ACK 确认

• 网络辅助：路由器向发送方提供显式的网络拥塞状态反馈信息

  • 发送阻塞分组（choke pocket）
  • 标记或更新某个字段以指示

TCP 拥塞控制

1、拥塞窗口 congestion window，cwnd

$$LastByteSent - LastByteRead \le min\{cwnd, rwnd\}$$

其中，发送速率随 cwnd 变化，约为 cwnd / RTT byte/s。

2、特性：自计时 self-clocking，得名于 TCP 使用确认来触发增大 cwnd

3、指导性原则

• 丢失报文段时，适当降低 TCP 发送方的速率

  • 从拥塞控制角度看，应着手减少 cwnd 以降低发送速率

• 当对先前收到的未确认的报文段的确认到达时，可以增加发送方的速率

  • 表明此时的 TCP 链路是畅通的，报文段可顺利交付

• 带宽探测：当未出现丢包前，不断增加速率以响应到达的 ACK；否则才减小速率

  • “得寸进尺，受挫尚退”

4、TCP 拥塞控制算法 TCP congestion control algorithm

• 慢启动 slow-start（P179）

  • def：首次以 1 个 MSS 开始，其后以 2 的幂指数递增，即每过一个 RTT 翻番
  • 何时结束增长？
    • 丢包：重新慢启动，设置 cwnd = 1，慢启动阈值 ssthresh = cwnd / 2
    • 到达或超过慢启动阈值：结束慢启动，启动拥塞避免模式
    • 检测到三个冗余 ACK：执行快速重传，进入快速恢复状态

• 拥塞避免

  • def：到达或超过慢启动阈值时，与其再翻番 cwnd，采用保守方法，仅自增 cwnd
  • 方法：同慢启动，设置 cwnd = 1，置慢启动阈值 ssthresh = cwnd / 2

• 快速恢复

  • 对于每一个冗余 ACK，cwnd 增加 1；当对丢失报文段 ACK 到达后，再降低 cwnd 进入拥塞避免状态

• 特性：加性增 乘性减（Additive-Increase， Multiplicative-Decrease，AIMD P181）

  • def：每个 RTT 内 cwnd 线性（加性）增加 1MSS，3 个冗余 ACK 减半（乘性减）

• TCP 吞吐量

  • 宏观模型

$$avg_{单条连接的吞吐量} = \frac {0.75 * W}{RTT}$$

​ 其中，W 为丢包时间发生时，窗口长度的字节数。

• 高带宽路径

$$avg_{单条连接的吞吐量} = \frac {1.22 * MSS}{RTT * \sqrt {L}}$$

​ 其中，L 为链路的丢包率。

5、实例：TCP 分岔

• 目的：优化云服务，改善用户体验

• 手段：部署临近用户的前端服务器 或 利用 TCP 分岔（TCP splitting）来分裂 TCP 连接

• 公式

$$4 * RTT_{FE} + RTT_{BE} + 处理时间$$

其中 FE、BE 分别为客户 to 前端服务器、前端服务器到数据中心（后端服务器）的往返时间

• 效果：降低时延到原来的约 1/4

6、公平性

• UDP 源可能压制 TCP 流量

• 并行 TCP 连接占用较大带宽而不常见

7、明确拥塞通告 Explicit Congestion Notification，ECN

• def：允许网络明确向 TCP 发送方和接收方发送显式拥塞信号

• 好处：无需再由发送方通过观察分组丢失来推断拥塞

• 过程：接收主机收到 ECN 拥塞指示 -> 设置 ECN（拥塞通告回显）-> 通知发送方 -> 收到拥塞指示，减半拥塞窗口、设置 CWR 比特（拥塞窗口缩减）

• * 利用网络层发送 ECN 信号：数据包拥塞控制协议 DCCP、数据中心 TCP DCTCP