UPDATE A
FROM TableA A, TableB B
SET DepartmentId = B.DepartmentId
WHERE A.CustomerId = B.CustomerId
HELP 帮助用户了解数据库中各种对象的结构
SHOW 帮助用户了解某种对象的定义,即返回其DDL语句
EXPLAIN 返回一个SQL语句经优化处理后的执行步骤,注意并未真正执行
FALLBACK 对数据加以保护的一种方式,是冗余的备份
RENAME 对表重命名
NULLIFZERO 对数据作累计处理时,忽略零值
ZEROIFNULL 对数据作累计处理时,将空值作零处理
WITH...BY 对详细数据记录作分类统计(Sub-Total)时有用
MODIFY USER/DATABASE 对用户/数据库对象作动态修改而无需数据库重组
HELP 命令 参数 说明
HELP DATABASE databasename; 可以显示一个指定数据库所包含的所有对象
HELP USER username; 显示某个用户中所包含对象的信息
HELP TABLE tablename; 显示某张表的信息
HELP VIEW viewname; 显示某个视图的信息
HELP MACRO macroname; 显示某个宏的信息
HELP COLUMN table or viewname.*; 显示表/视图的各列的信息
HELP COLUMN table or viewname.colname . . ., colname; 显示表/视图某几列的信息
HELP INDEX tablename; 显示某个表中的所有索引定义
HELP STATISTICS tablename; 显示表的统计
HELP CONSTRAINT table or viewname.constraintname; 显示定义在某个表/视图上的约束定义
HELP JOIN INDEX join_indexname; 显示连接索引的定义
HELP TRIGGER triggername; 显示触发器的信息
HELP PROCEDURE procedurename; 显示存储过程的信息
HELP PROCEDURE procedurename ATTRIBUTES;
HELP 'SQL'; 得到所有SQL命令的列表信息
HELP 'SQL sqlcommand'; 得到某个特定SQL命令的使用方法
如要知道这些数据库对象是用什么样的DDL命令创建的,则要使用SHOW命令:
SHOW命令 参数
SHOW TABLE Tablename ;
SHOW VIEW Viewname;
SHOW MACRO Macroname;
SHOW INDEX Tablename;
SHOW JOIN INDEX join_indexname;
SHOW TRIGGER Triggername;
SHOW PROCEDURE Procedurename;
EXPLAIN命令:利用EXPLAIN命令,可以了解Teradata执行一个SQL交易请求的详细过程和计划,这对于更进一步地理解Teradata的查询处理机制有很大的帮助。另一方面,对于复杂SQL交易的调试来说,这也是不可缺少的一个工具。
利用EXPLAIN解释一个SQL交易的方法很简单,就是在原来SQL语句的前面加上EXPLAIN即可,其它完全不变。
在LIKE结构的字符串中,'%'和'_'可以作为通配符使用,但是如果需要匹配这些字符本身(比如查找95%),即把它们作为一般字符时使用,我们可以通过定义ESCAPE字符来达到这个目的,紧跟在ESCAPE字符后的’%’和’_’作为一般字符看待。
例:
LIKE ''%A%�A__'' ESCAPE ''A''
在这个表达式中,将字母A定义为ESCAPE字符,其中:
! 第一个%为通配符;
! 第一个A和其后的%联合表示字符%;
! 第三个%为通配符;
! 第二个A和其后的A联合表示字符A;
! 第四个A和其后的’_’联合表示字符_;
! 最后一个’_’为通配符。
对于表达式的操作数如果是字符,ANSI标准中是区分大小写的,如果不要区分大小写,可以使用UPPER函数将其转换成大写字母来进行匹配。Teradata缺省不区分大小写,如果要区分,可以使用其扩展参数CASESPECIFIC。
NULL的使用
NULL的说明:
! NULL显示没有数据的字段
! NULL表示不存在或未发现的值
! NULL既不是数字类型也不是字符类型
! 具有NULL值的字段可以被压缩,不占任何空间
NULL也可以参与运算,其运算规则为:
! NULL在算术运算中产生的结果为NULL(空)
! NULL在比较运算中产生的结果为False
! UNKNOWN DATA, MISSING DATA和NULL是同样的含义
! 当进行升序排列时,NULL在数字列排列在负数前,在字符列排列在空格
前。
宏
CREATE MACRO macroname AS ( . . . ); 定义宏
EXECUTE macroname; 执行宏语句
SHOW MACRO macroname; 显示宏定义
REPLACE MACRO macroname AS ( . . . ); 改变宏定义
DROP MACRO macroname; 从字典中删除宏定义
EXPLAIN EXEC macroname; 显示宏执行的解释
EXISTS在子查询中的使用
EXISTS可以使用在子查询中,用来表示查询至少返回一行。如果前面加上否定词NOT,则表示查询时无记录存在。EXISTS可以代替IN,而NOT EXISTS可以代替NOT IN。
CHARACTERS函数
CHARACTERS函数也是Teradata的扩展,用于计算VARCHAR型数据字段的实际字符串长度。CHARACTERS函数可以简写成CHARACTER、CHARS或者CHAR。
TRIM函数
ANSI标准的TRIM函数用于去除字符数据中前头或后端的空格或者二进制数据(BYTE与VARBYTE)中前头或后端的零。在Teradata缺省模式下,TRIM (<expression>)只能去除后端的空格或二进制零。
SELECT CAST (salary_amount AS FORMAT ''$$$,$$9.99'');
SELECT (1000/salary_amount) * 100
(FORMAT 'ZZ9%')
(TITLE 'Increase Percentage')
FROM employee
WHERE employee_number = 1004;
SELECT (CAST (1000/salary_amount) * 100 AS FORMAT 'ZZ9%'
TITLE 'Increase Percentage') FROM ...
FORMAT短语中可以使用的格式化字符主要为:
$ 美元标识符
9 数字位
Z 将数字中的前缀零去除
, 在指定位置插入逗号
. 指定小数点位置
- 在指定位置插入连字号
/ 在指定位置插入斜线
% 在指定位置插入百分号
X 字符数据,每个X代表一个字符
G 图形数据.一个G代表一个逻辑字符(双字节)
B 在指定位置插入空格
对日期的格式化处理
在Teradata中,日期数据的缺省输出格式是:YY/MM/DD,这和ANSI标准是一样的。而ANSI标准建议的日期显示格式是:YYYY-MM-DD。
其它一些常用的日期显示格式列举如下,其中的B表示空格。
YYYY/MM/DD'
YYYY-MM-DD'
YYYY.DDD'
DBMMMBYYYY'
MMBDD,BYYYY'
YYYYBMMMBDD'
YY/MM/DD'
D-MM-YY'
YBDDD'
MM'
下面是一些对日期进行格式化的例子。
句法 结果
FORMAT 'YYYY/MM/DD' 1996/03/27
FORMAT 'DDbMMMbYYYY' 27 Mar 1996
FORMAT 'mmmBdd,Byyyy' Mar 27, 1996
FORMAT 'DD.MM.YYYY' 27.03.1996
FORMAT 'MM/DD/YY' 03/27/96
FORMAT 'MMM.DD.YY' Mar.27.96
FORMAT 'yy -- mm -- dd' 96 -- 03 -- 27
FORMAT 'DDDYY' 08696
SELECT last_name,first_name,hire_date (FORMAT 'mmmBdd,Byyyy')
FROM employee
ORDER BY last_name;
利用FORMAT短语,可以将字符字段或表达式进行截取处理,这种处理只影响显示格式,而不会影响数据的内部存储格式。
Teradata的属性函数
属性函数 返回信息说明
TYPE 数据类型
TITLE 标题短语
FORMAT 格式短语
NAMED NAMED子句
CHARACTERS 字符个数
Teradata DDL允许在创建表时指定表的物理属性,包括:
A. SET 不允许记录重复,例:CREATE SET TABLE table1 ...
B. MULTISET 允许记录重复,例:CREATE MULTISET TABLE table1 ...
C. 数据保护要结合FALLBACK和JOURNAL (流水或日志)。
FALLBACK是Teradata的一种数据保护机制,数据表的每一条记录都同时存放两份,而且位于不同的AMP所控制的存储单元中;当数据发生问题或者AMP失败时,可以利用存放在其他AMP上的数据保证对数据表的访问。
- FALLBACK 使用FALLBACK保护机制
- NO FALLBACK 不使用FALLBACK保护机制
日志有BEFORE和AFTER两种,分别保存了一条记录变化前后的状态。当系统出错时,可以利用日志进行恢复。
D. 存储空间选项
DATABLOCKSIZE用来指定数据块大小,最小的数据块为6144字节,最大的数据块是32256字节。
FREESPACE用来定义在每个磁盘柱面上保留的空间(0-75%)。
例:
CREATE MULTISET TABLE table_1
, FALLBACK, NO JOURNAL
, FREESPACE = 10 PERCENT
, DATABLOCKSIZE = 16384 BYTES
(field1 INTEGER);
虽然TD的DDL里有primary key,但是实际上存储在TD数据字典里的只有index:
A. 没有在CREATE TABLE时指定PI
IF 定义了PK,THEN PK = UPI
ELSE IF 存在定义为UNIQUE的字段,
THEN 第一个NIQUE的字段为UPI
ELSE 表中定义的第一个字段作为NUPI
B. CREATE TABLE时指定了PI
IF 定义了PK,THEN PK作为USI
AND为每一个定义为UNIQUE的字段建立一个USI
新建一张表TB1,表结构与TB2一样,不需要数据。
Create table TB1 as TB2 with no data;
使用子查询创建表,并选择所需的列。
CREATE TABLE emp1 AS(SELECT employee_number, department_number, salary_amount FROM employee) WITH NO DATA;
往TB1表中装入TB2表的数据(也可以是有选择性的,需指定字段)
INSERT INTO TB1 SELECT * from TB2;
交易完整性
在Teradata中,系统将保证一个交易的完整。怎样才算是一个交易呢,在Teradata中,根据其所处方式的不同在处理时也有所不同。在 Teradata缺省模式下,以分号结束的每个SQL语句都是一个完整的交易,也可以使用BT (Begin Transaction)和ET (End Transaction)来显示地定义一个交易。下面看一个例子:
例:
缺省方式
.LOGON
INSERT row1; (txn #1)
INSERT row2; (txn #2)
.LOGOFF
用BT和ET显示定义交易
.LOGON
BT;
INSERT row1; (txn #1)
INSERT row2;
COMMIT WORK;
ET;
.LOGOFF
第一部分中有两个SQL语句,用分号结束,表示两个交易,任何一个失败不会影响另一个的执行。而第二部分用BT和ET显示地规定:在BT和ET之间的所有 SQL是一个交易,只有最后的COMMIT WORK执行成功后,才会真正地更新数据库。执行过程中任何一个SQL语句失败,都会使整个交易失败,系统将自动进行恢复(Rollback)处理。
在ANSI方式下,必须进行显示地提交才能完成一个交易。
利用WITH BY进行数据小计
WITH BY的主要特点包括:
A. 它为明细数据表创建分类小计。
B. 跟GROUP BY不同的是,WITH BY没有剔除明细记录,而是在明细记录后面按照分类增加小计行。
C. 可以允许多于一个字段进行小计,即小计当中可以嵌套小计。
D. 输出结果将根据BY后面的所有字段自动进行排序。
E. 它是Teradata的一个扩展特性。
举例:
1. WITH BY, WITH和ORDER BY的联合使用:
SELECT last_name AS NAME
,salary_amount AS SALARY
,department_number AS DEPT
FROM employee
WITH SUM (SALARY) BY DEPT
WITH SUM (SALARY) (TITLE 'GRAND TOTAL')
ORDER BY NAME;
结果如下:
NAME SALARY DEPT
Kanieski 29250.00 301
Stein 29450.00 301
-------------
Sum (SALARY) 58700.00
Johnson 36300.00 401
Trader 37850.00 401
-------------
Sum (SALARY) 74150.00
Ryan 31200.00 403
Villegas 49700.00 403
-------------
Sum (SALARY) 80900.00
-------------
GRAND TOTAL 213750.00
2. WITH和GROUP BY的联合使用:
SELECT department_number (TITLE 'dept_no')
,SUM (salary_amount)
,AVG (salary_amount)
FROM employee
GROUP BY department_number
WITH SUM (salary_amount) (TITLE 'GRAND TOTAL')
,AVG (salary_amount) (TITLE '')
ORDER BY department_number;
结果如下:
dept_no SUM (salary_amount) AVG (salary_amount)
301 58700.00 29350.00
401 74150.00 37075.00
403 80900.00 40450.00
--------------------- ----------------------
GRAND TOTAL 213750.00 35635.00
集合操作
集合操作主要包括:合并操作(UNION)、相交操作(INTERSECT)和排外操作(EXCEPT)
Teradata的集合操作与标准ANSI集合操作的不同之出在于返回结果的重复记录处理上。在ANSI标准中集合操作将重复记录自动剔除,而Teradata增加了ALL关键词,ALL关键词允许保留重复记录。
我们将有关集合操作的一些补充规则列举如下:
1. 在子查询中不能使用集合操作
2. 在定义视图时不能使用集合操作
3. 不能包含WITH或WITH BY子句
4. 集合操作的优先级为:INTERSECT第一,其后分别为UNION和 EXCEPT,从左到右。可以使用括号改变优先级。
5. 每一个SELECT语句必须有一个FROM <表名>的子句
6. 每个单独的SELECT语句中可以使用GROUP BY
7. Group By不能用于或影响整个返回结果集
8. 重复记录将会抛弃,除非使用ALL选项
字符串函数
SUBSTRING函数:用来从字符串中析取一个子字符串,其格式为:
SUBSTRING (<字符串表达式> FROM <开始位置> [ FOR <长度> ])
如:
SELECT SUBSTRING('catalog' FROM 5 FOR 3);
结果为log。
字符串合并:字符串合并的符号是"||",它把两个字符串串联成一个字符串。其基本格式为:
<字符串1> || <字符串2>
INDEX (字符串定位函数):INDEX用来在一个字符串中定位一个子串的开始位置。如下面的例子:
SELECT INDEX('abc', 'b'); 返回结果2
CASE 表达式
A. 基于值(Valued)的CASE语句,例如:
SELECT SUM(
CASE department_number
WHEN 401 THEN salary_amount
ELSE 0
END) / SUM(salary_amount)
FROM employee;
B. 基于搜索(Searched)的CASE语句,例如:
SELECT last_name,
CASE
WHEN salary_amount < 30000
THEN 'Under $30K'
WHEN salary_amount < 40000
THEN 'Under $40K'
WHEN salary_amount < 50000
THEN 'Under $50K'
ELSE
'Over $50K'
END
FROM employee
ORDER BY salary_amount;
C. NULLIF表达式
NULLIF实际上用来作为CASE语句在某种情况下的缩写,其格式为:
NULLIF ( <expression1> , <expression2> )
规则是:
如果表达式1等于表达式2,则返回NULL
如果表达式1不等于表达式2,则返回表达式1的值。
例:
SELECT call_number
,labor_hours (TITLE 'ACTUAL HOURS')
,NULLIF (labor_hours, 0)
(TITLE 'NULLIF ZERO HOURS')
FROM call_employee
ORDER BY labor_hours;
注意:在除法表达式中,如果被除数有可能为零值,经常使用NULLIF来避免除零错。
D. COALESCE(接合)表达式
COALESCE实际上也是CASE语句在某种特殊情况下的宿写。COALESCE将返回第一个非NULL表达式的值。其格式为:
COALESCE ( <expression1> , <expression2> [, <expressionX> ] )
例:从phone_table表中,列出姓名和电话号码,如果办公室电话存在则列出办公室电话,否则列出家里电话。
SELECT name
,COALESCE (office_phone, home_phone)
FROM phone_table;
例:转换可能的NULL值为零:
SELECT course_name
,COALESCE (num_students, 0)
(TITLE '# Students')
FROM class_schedule;
简单排队RANK
排队函数(RANK)的语法是:
RANK(colname)
这里,colname表示排队的列名,其结果降序排列。
问题
显示商店1001的产品销售额排队。
解答
SELECT storeid, prodid, sales, RANK(sales)
FROM salestbl
WHERE storeid = 1001;
结果
storeid prodid sales Rank
1001 F 150000.00 1
1001 A 100000.00 2
1001 C 60000.00 3
1001 D 35000.00 4
如上所示,列Rank的最大值代表最低的销售额。
带限定的排队
QUALIFY子句限制排队输出的最终结果。QUALIFY子句与HAVING子句类似,使输出限制在一定范围内。
问题
按商店得到销售前3名的产品。
解答
SELECT storeid, prodid, sales, rank(sales)
FROM salestbl
GROUP BY storeid
QUALIFY rank(sales) <= 3;
结果
storeid prodid sales Rank
1001 A 100000.00 1
1001 C 60000.00 2
1001 D 35000.00 3
1002 A 40000.00 1
1002 C 35000.00 2
1002 D 25000.00 3
1003 B 65000.00 1
1003 D 50000.00 2
1003 A 30000.00 3