浏览 5736 次
锁定老帖子 主题:Teradata SQL 笔记
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2010-04-09
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%%AAA__'' 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 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |
发表时间:2010-05-20
LZ是Teradata的吗?呵呵,看你写了这么多。
|
|
返回顶楼 | |