Teradata 学习笔记3

视图：

视图是一张虚拟表。它可以是表中数据记录的子集，也可以是
表中某几个字段形成的子集。视图可以参照一张或多张基表。

视图并不另外单独存储和复制数据，它只是一个结构。视图定义存储在数据
字典中，而不是在用户自己的空间里。

CREATE VIEW emp_403
AS SELECT employee_number
,last_name
,salary_amount
FROM employee
WHERE department_number = 403;
 
读取此视图的方法和读取普通表无异，如：
SELECT *
FROM emp_403;

也可以利用视图来更新记录，如：
UPDATE emp_403

SET salary_amount = 100000
WHERE last_name = 'Villegas';

视图中的连接：

CREATE VIEW employee_call
AS SELECT employee.employee_number
,last_name
,first_name
,call_number
,call_status_code
,assigned_date
,assigned_time
,finished_date
,finished_time
FROM employee
INNER JOIN call_employee
ON call_employee.employee_number
= employee.employee_number;

利用视图重命名列：

利用视图可以对列进行重新改名。参考下面的例子：
CREATE VIEW shortcut (emp, dept, last, first, sal)
AS SELECT employee_number
,department_number
,last_name
,first_name
,salary_amount
FROM employee
WHERE department_number = 201;

改变视图定义
利用REPLACE语句可修改视图定义。如，改变上例中shortcut视图，使之仅包
含部门301。我们采用下面的步骤：
 
1)从数据字典中显示当前的视图定义
.EXPORT REPORT FILE = shortcut.ddl
SHOW VIEW shortcut ;

2)视图定义被输出到shortcut.ddl文件，内容为：
CREATE VIEW shortcut (emp, dept, last, first, sal)
AS SELECT employee_number
,department_number
,last_name
,first_name
,salary_amount
FROM employee
WHERE department_number = 201;

3)利用REPLACE语句改变视图定义：
REPLACE VIEW shortcut (emp, dept, last, first, sal)

AS SELECT employee_number
,department_number
,last_name
,first_name
,salary_amount
FROM employee
WHERE department_number = 301;
 另外，利用视图还可以对列标题重新命名和对列进行格式化。如下面创建了
一个基于部门201的视图，它具有较好的格式化输出。
CREATE VIEW Report AS
SELECT employee_number AS Emp (FORMAT '9999')
,department_number AS dept (FORMAT '999')
,last_name AS Last (TITLE 'Name')
,first_name AS First (TITLE '')
,salary_amount / 12 AS Monthly_Salary (FORMAT '$$$,$$9.99')
FROM employee
WHERE department_number = 201;

视图输出结果：
SELECT *
FROM report
ORDER BY monthly_salary;
 
Emp Dept Name Monthly_Salary
1025 201 Short Michael $2,891.67
1021 201 Morrissey James $3,229.17

聚合视图(Aggregate View)
聚合视图是指视图的列通过聚合计算而得到的视图。
例：建立一个视图依部门汇总工资信息。
CREATE VIEW deptsals
AS SELECT department_number AS department
,SUM (salary_amount) AS salary_total
,AVG (salary_amount) AS salary_average
,MAX (salary_amount) AS salary_max
,MIN (salary_amount) AS salary_min
FROM employee
GROUP BY department_number;

从这个视图的定义可以看出，聚合或派生出来的列必须要给一个名字。

使用HAVING的聚合视图
利用HAVING子句，修改上述deptsals视图，使之仅包含平均工资小于$36,000
的部门，则语句为：
REPLACE VIEW deptsals
AS SELECT department_number AS department
,SUM (salary_amount) AS salary_total
,AVG (salary_amount) AS salary_average
,MAX (salary_amount) AS salary_max
,MIN (salary_amount) AS salary_min
FROM employee
GROUP BY department_number
HAVING AVG(salary_amount) < 36000 ;

视图的限制和总结
视图的限制：
不能基于视图来建立索引，因为视图只是一个定义，本身没有任何数据
视图中不能包含ORDER BY 子句
派生和聚合的列必须要有一个AS子句指定列名
视图不能被UPDATE，如果它包含：

❍ 数据来自多个表(JOIN VIEW)
❍ 两次同样的列
❍ 派生的列
❍ 包含DISTINCT 子句
❍ 包含GROUP BY 子句
 
视图的作用和特点：
提供了一个额外的安全、授权层次
帮助控制读和修改权利
如果基表增加了列，该列对视图无影响。
如果列从基表中删除，视图也不受影响，除非删除跟该列相关的视图。
简单化了用户存取

根据视图的特点，对于生产系统，我们建议：
对每一个基表建立至少一个视图
根据需要建立视图查询

字符串函数：

SUBSTRING函数用来从字符串中析取一个子字符串，其格式为：
SUBSTRING (<字符串表达式> FROM <开始位置> [ FOR <长度> ])
 
如：
SELECT SUBSTRING('catalog' FROM 5 FOR 3);
结果为log。

下面通过一些例子说明了SUBSTRING的使用方法：
SUBSTRING                                             结果

SUBSTRING('catalog' FROM 5 FOR 4) log
SUBSTRING('catalog' FROM 0 FOR 3) ca
SUBSTRING('catalog' FROM -1 FOR 3) c
SUBSTRING('catalog' FROM 8 FOR 3) 长度为0的字符串
SUBSTRING('catalog' FROM 1 FOR 0) 长度为0的字符串
SUBSTRING('catalog' FROM 5 FOR -2) error
SUBSTRING('catalog' FROM 0) catalog
SUBSTRING('catalog' FROM 10) 长度为0的字符串
SUBSTRING('catalog' FROM -1) catalog
SUBSTRING('catalog' FROM 3) talog

字符串合并
字符串合并的符号是"||"，它把两个字符串串联成一个字符串。其基本格式
为：
<字符串1> || <字符串2>
 
注意，BYTE数据类型仅能跟BYTE数据类型进行串联。
例如：’bc’ ||  ’yz’的结果为’bcyz’

INDEX (字符串定位函数)
INDEX用来在一个字符串中定位一个子串的开始位置。如下面的例子：
SELECT INDEX('abc', 'b'); 返回结果2

SELECT INDEX('abc', 'ab'); 返回结果1
SELECT INDEX('abc', 'd'); 返回结果0

又如：在下列所示的Department表中，列出所有部门名包含"SUPPORT"的部
门，并指出"SUPPORT"位于该部门名的起始位置。
SELECT department_name
,INDEX(department_name,'SUPPORT')
FROM department
WHERE INDEX (department_name,'SUPPORT') > 0
ORDER BY department_number;

同时使用SUBSTRING和INDEX的例子：
利用CONTACT表显示表中每个人的first name和last name，使用SUBSTRING
和INDEX函数的方法为：
SELECT
SUBSTRING (contact_name, FROM INDEX (contact_name,', ') +2)
|| ' ' || SUBSTRING (contact_name FROM 1
FOR INDEX (contact_name, ',') -1)
(TITLE 'Contact Names')
FROM contact;

外连接
在数据库中，由于各种原因有可能产生数据的不一致或不完整。如在公司的
雇员表中，有可能出现某个雇员没有有效的部门号这种情况

外连接主要有三种，即所谓的左外连接(Left Outer Join)、右外连接(Right Outer
Join)和全外连接(Full Outer Join )。我们用图19-1来说明会更形象些。

图中表示表A和表B进行连接，显然，如果没有进一步的WHERE条件子句的限
制，那传统的内连接操作所选择的就是图中表A和表B的交集所表示的数据，即Part
3。如果要返回Part 2加上Part 3的数据集合，就应该使用表A对表B的左外连接。这
时，在返回的数据中，对应表B的字段的值一定是NULL值。同理，如果要返回Part
3加上Part 1的结果，就要使用表A对表B的右外连接；在返回的数据中，对应表A的
字段的值也是NULL值。
从图中可以看出，表A对表B的左外连接与表B对表A的右外连接是完全相同
的。换言之，交换进行左外连接两个表的位置，就变成了右外连接。

外连接的完整语法可以描述为：
SELECT cname [, cname ,
FROM tname [aname]
{
LEFT [OUTER]
RIGHT [OUTER]
FULL [OUTER]
}
JOIN tname [aname]
ON condition ;

SELECT last_name AS Employee
,department_name AS Dept
,description AS Job
FROM Department D
RIGHT OUTER JOIN Employee E
ON D.Department_Number = E.Department_Number
LEFT OUTER JOIN Job J
ON E.Job_Code = J.Job_Code;

当多个表进行外连接时，处理顺序是按照ON子句的先后位置来进行的，放在
前面的ON子句先处理。每处理一个ON子句，即完成一次连接操作，此时实际上是
形成了一个临时表，此临时表再与下一个表按照后续ON子句的定义来进行进一步
的连接操作。

在多个表参与外连接的情况下，也可以将表的连接操作关系全部定义在FROM
子句中，然后将ON条件按照连接的顺序写出来。如将前面的SQL语句写成：
SELECT last_name AS Employee
,department_name AS Dept
,description AS Job
FROM Department D
RIGHT OUTER JOIN Employee E
LEFT OUTER JOIN Job J
ON D.Department_Number = E.Department_Number
ON E.Job_Code = J.Job_Code;

加上括号后正确的写法应该是：
SELECT last_name AS Employee
,department_name AS Dept
,description AS Job
FROM Job J
LEFT OUTER JOIN
(Department D
RIGHT OUTER JOIN Employee E
ON D.Department_Number = E.Department_Number )
ON E.Job_Code = J.Job_Code;

提示：在进行多表外连接操作时应该记住两点：
1、ON子句的顺序规定了处理连接的顺序
2、每个连接操作形成一个临时表，此临时表再与后续的表时行进一步的连接
操作。

相关子查询与导出表：

相关子查询：

例：找出每个部门中薪水最高的雇员，显示姓和薪水。
SELECT last_name
,salary_amount
FROM employee ee
WHERE salary_amount =
(SELECT MAX (salary_amount)
FROM employee em
WHERE ee.department_number = em.department_number);

再举一个例子：查找薪水高于部门平均薪水的所有员工，显示姓和薪水。
SELECT last_name
,salary_amount (Format ''$$$,$99.99'')
FROM employee ee
WHERE salary_amount >
(SELECT AVG (salary_amount)
FROM employee em
WHERE ee.department_number = em.department_number);

显示在所属部门薪水最高的经理的雇员编号、部门编号和薪水。
SELECT d.manager_employee_number (TITLE 'mgr_emp_# ')
,d.department_number
,e.salary_amount
FROM department d
INNER JOIN employee e
ON e.employee_number = d.manager_employee_number
WHERE e.salary_amount =
(SELECT MAX (salary_amount)
FROM employee em
WHERE d.department_number = em.department_number);

使用临时表
相关子查询的执行次数依赖于所引用的外部查询的记录行数，实际上我们可
以利用临时表来减少子查询的执行次数而提高查询效率。
例：显示所有高于所属部门平均薪水的雇员信息。对此问题，前面已经用相
关子查询解决过，它的子查询执行次数为雇员表中的雇员数量。如果雇员数量很
多，则对数据库引擎而言是很大的负载。考虑到部门的平均薪水在部门内是固定
的，我们可以利用临时表来存储所有部门的平均薪水，从而减少部门薪水的计算次
数。具体步骤如下：
1.首先创建一个临时表：
CREATE TABLE my_temp (avgsal DECIMAL (10,2));
 
2.在临时表中加入部门平均工资：

INSERT INTO my_temp
SELECT  AVG (salary_amount) FROM employee;

3.执行查询：
SELECT e.last_name
,e.salary_amount (FORMAT '$,$$$,$99.99')
,t.avgsal (FORMAT ''$,$$$,$99.99')
FROM employee e
,my_temp t
WHERE e.salary_amount>t.avgsal
ORDER BY 2 DESC;
 
4.删除临时表：
DROP TABLE my_temp;

导出表
临时表需要用单独的SQL语句来创建和维护，我们也可以使用导出表代替临时
表。事实上，导出表就是一个命名的临时表，它在整个SQL语句中自动创建和删
除。在定义导出表的整个SQL语句中都可以通过导出表名来引用它。
导出表一般由FROM子句导出表，语法为：
FROM (子查询) [AS] <导出表名称> [(列表)]

再来看上一节讨论的问题，即找出所有薪水高于公司平均薪水的员工。在上
一节我们使用临时表，需要四个步骤。如果使用导出表的方法，可以写成下面的方
式：
SELECT last_name
,salary_amount (FORMAT '$,$$$,$99.00')
,avgsal (FORMAT '$,$$$,$99.00')
FROM (SELECT AVG (salary_amount)
FROM employee) my_temp (avgsal)
,employee
WHERE salary_amount > avgsal
ORDER BY 2 DESC;

这里了名为my_temp的导出表，它只有一个字段即平均薪水。这个导出表在整
个SQL语句中都可以引用，如在SELECT部分引用了导出表的字段，在WHERE条
件子句中也引用了导出表的字段。
研究这个SQL查询，可以得出这样的结论：

! 导出表实际上还是一种临时表，如果把临时表的操作集成在一条SQL语句
中，就是导出表。
! 导出表在整个SQL语句范围内有效。
! 完成指定的SQL操作后导出表将自动被删除。

在导出表中使用分组(GROUP)
在Where子句中不能直接使用聚集函数，如WHERE AVG (salary_amount) >
salary_amount。我们可以通过导出表实现Where子句中对聚集函数的引用。

例如，显示所有薪水高于部门平均薪水的雇员信息。
SELECT last_name AS last
,salary_amount (FORMAT '$,$$$,$99.99') AS sal
,department_number AS dep
,avgsal (FORMAT '$,$$$,$99.99')
FROM (SELECT AVG (salary_amount)
,department_number
FROM employee
GROUP BY department_number)
my_temp (avgsal, deptno)
,employee ee
WHERE sal > avgsal
AND dep = deptno
ORDER BY 2 DESC;

在WHERE子句不能直接使用聚合函数AVG，因此我们把它放在导出表中，由
导出表字段AVGSAL来获得平均薪水，再在WHERE条件子句中进行比较。
前边使用相关子查询实现过相同功能，但相关子查询不能显示部门平均薪水
而且需多次执行子查询。而使用临时表需要多个单独的步骤。因此在解决这类问题
时，使用导出表的效率更高。