ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 转载Teradata SQL 笔记

转载Teradata SQL 笔记

原创 Linux操作系统 作者:jack198409 时间:2013-04-23 16:02:55 0 删除 编辑
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%%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 ()只能去除后端的空格或二进制零。


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 ( , )
       规则是:
           如果表达式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 ( , [, ] )

例:从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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10768286/viewspace-759106/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Salary Negotiation
请登录后发表评论 登录
全部评论

注册时间:2007-12-13

  • 博文量
    135
  • 访问量
    285044