teradata基础精简.docx
《teradata基础精简.docx》由会员分享，可在线阅读，更多相关《teradata基础精简.docx（36页珍藏版）》请在冰豆网上搜索。
teradata基础精简
SQL编码规范
1）.缩进
对于存储过程文件，缩进为8个空格
对于C#里的SQL字符串，不可有缩进，即每一行字符串不可以空格开头
2）.换行
1>.Select/From/Where/Orderby/Groupby等子句必须另其一行写
2>.Select子句内容如果只有一项，与Select同行写
3>.Select子句内容如果多于一项，每一项单独占一行，在对应Select的基础上向右缩进8个空格（C#无缩进）
4>.From子句内容如果只有一项，与From同行写
5>.From子句内容如果多于一项，每一项单独占一行，在对应From的基础上向右缩进8个空格（C#无缩进）
6>.Where子句的条件如果有多项，每一个条件占一行，以AND开头，且无缩进
7>.（Update）Set子句内容每一项单独占一行，无缩进
8>.Insert子句内容每个表字段单独占一行，无缩进；values每一项单独占一行，无缩进
9>.SQL文中间不允许出现空行
10>.C#里单引号必须跟所属的SQL子句处在同一行，连接符（"+"）必须在行首
3）.空格
1>.SQL内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔
2>.逗号之后必须接一个空格
3>.关键字、保留字和左括号之间必须有一个空格
BASE
WhatisAMPs?
AMP,acronymfor"AccessModuleProcessor,"isthetypeofvprocusedtomanagethedatabase,handlefiletasksandandmanipulatethedisksubsysteminthemulti-taskingandpossiblyparallel-processingenvironmentoftheTeradataDatabase.
WhatisBTEQ?
BTEQisaTeradatanativequerytoolforDBAandprogrammers.BTEQ（BasicTEradataQuery）isacommand-drivenutilityusedto1）accessandmanipulatedata,and2）formatreportsforbothprintandscreenoutput.
Teradata的帮助系统主要由三条命令组成，一条是HELP，一条是SHOW，另一条是EXPLAIN。
HELP命令可以提供有关数据库中各种目标的信息，SHOW命令则
用来显示这些目标的结构，包括创建这些目标的DDL语句。
EXPLAIN命令以英文文字的方式显示了系统处理一个SQL交易请求的执行过程。
HELP命令可以提供有关数据库和用户以及这些对象的信息
HELPDATABASE/USER
HELPTABLE/VIEW/MACRO
HELPSESSION:
上面谈到的HELP命令提供了对数据库中各种对象的帮助信息，也可以提供SQL语法的联机帮助。
如果要显示数据库中各种对象的结构，换言之，要知道这些数据库对象是用什么样的DDL命令创建的，则要使用SHOW命令。
SHOWCONTROL，这是一个附加的SQLSHOW命令。
它能够格式化显示当前BTEQ会话的信息。
利用EXPLAIN命令，可以了解Teradata执行一个SQL交易请求的详细过程和计划，这对于更进一步地理解Teradata的查询处理机制有很大的帮助。
另一方面，对于复杂SQL交易的调试来说，这也是不可缺少的一个工具。
系统返回的信息包括：
!
提供完整的由分解器对SQL语句进行分解和优化后的AMP执行步。
!
这种执行计划是基于当前的数据分布情况而作出的，因此当数据分布发生变化时，同样SQL语句产生的执行步可能不相同。
!
EXPLAIN还会产生执行每个SQL步骤大致所需要的时间，但需要注意的是，这个时间由于是根据早期版本的CPU处理时间来计算，因此往往和实际情况相差很多，仅能作参考而已。
当操作数是字符时，ANSI标准是区分大小写的，而Teradata中缺省不区分大小写，但可以通过CASESPECIFIC/NOTCASESPECIFIC来明确定义是否区分。
如：
SELECT...
WHERElast_name（CASESPECIFIC）;
SELECT...
WHERElast_name（NOTCASESPECIFIC）;
在使用LIKE进行字符串匹配时，要特别注意字符的大小写。
ANSI标准中是区分大小写的，如果不要区分大小写，可以使用UPPER函数将其转换成大写字母来进行匹配。
Teradata缺省不区分大小写，如果要区分，可以使用其扩展参数CASESPECIFIC。
1.在Teradata缺省模式下
SELECTfirst_name
last_name
FROMemployee
WHERElast_name（CASESPECIFIC）LIKE'%Ra%';
2.在ANSI缺省模式下
SELECTfirst_name
last_name
FROMemployee
WHERElast_nameLIKE'%Ra%';NULL是SQL的一个关键字，在数据库的操作中有很重要的作用，下面是一些关于NULL的说明：
!
NULL显示没有数据的字段
!
NULL表示不存在或未发现的值
!
NULL既不是数字类型也不是字符类型
!
具有NULL值的字段可以被压缩，不占任何空间
NULL也可以参与运算，其运算规则为：
!
NULL在算术运算中产生的结果为NULL（空）
!
NULL在比较运算中产生的结果为False
!
UNKNOWNDATA,MISSINGDATA和NULL是同样的含义
!
当进行升序排列时,NULL在数字列排列在负数前，在字符列排列在空格前。
NOT既可以否定操作符，也可以否定条件表达式。
例；查找部门号不是301的员工姓名及其代码。
否定操作符:
SELECTfirst_name
last_name
employee_number
FROMemployee
WHEREdepartment_numberNOT=301;
WHERENOT（department_number=301）;
字符型数据
在ANSI标准中关于字符型数据定义了两类：
CHAR和VARCHAR。
其中CHAR表示固定长度的字符串，VARCHAR表示可变长度的字符串。
Teradata除了上述两类基本字符型数据外，还扩展了LONGVARCHAR类型，它等同于VARCHAR（64000），是最长的字符串。
二进制数据
二进制数据类型是Teradata的扩展，ANSI标准没有此类型。
Teradata支持两类二进制数据BYTE和VARBYTE。
其中BYTE表示固定长度的二进制串，VARCHAR表示可变长度的二进制串。
数字型数据
在ANSI标准中关于数字型数据定义了四类：
SMALLINT、INTEGER、FLOAT、DECIMAL。
Teradata除以上四类外，还有两类扩展的数字型数据，即BYTEINT和DATE。
1.BYTEINT表示不包括零的有符号整数，占一个字节存储空间。
2.DATE用来表示日期，但内部以整数形式存储，其公式为：
（year-1900）*10000+（month*100）+day
Teradata定义的DATE和ANSI定义的DATE在内部表示上是不兼容的。
ANSI日期格式是YYYY-MM-DD，在Teradata中也推荐使用这种格式来进行表达，这样可以避免由于使用两位数字表示年而带来的2000年问题。
图形数据
Teradata扩展了图形数据类型，如：
GRAPHIC、VARGRAPHIC、LONGVARGRAPHIC。
图形数据类型主要是用来支持双字节字符的，如日文。
当把系统设置成支持双字节字符时，数据库中表、视图等对象的名字、字段等都可以使用双字节字符。
如果系统设置成不支持双字节字符，则数据库对象名、字段等只能使用.61.
英文字母类的单字节字符，此时，前端工具仍然可以采用汉化界面，而且利用前面提到的CHAR和BYTE类型数据类型同样可以存储图形数据。
Teradata算术运算符
（）括号内算术运算优先级高
*乘
/除
+加（或正号）
-减（或负号）
**幂
MOD模（余数）
与日期有关的数据函数
1.EXTRACT
ANSI标准中EXTRACT函数允许选取日期和时间中任意字段或任意间隔的值，Teradata中EXTRACT函数支持日期数据中选取年、月、日，从时间数据中选取小时、分钟和秒。
SELECTDATE;96/11/07
SELECTEXTRACT（YEARFROMDATE）;1996
SELECTEXTRACT（MONTHFROMDATE+30）;12
SELECTEXTRACT（DAYFROMDATE+2）;09
2.ADD_MONTHS
ADD_MONTHS表示从某日期增加或减少指定月份的日期。
它考虑了大小月问题，所以计算日期是准确的。
SELECTADD_MONTHS（DATE,2）;1996-10-07
SELECTADD_MONTHS（DATE,12*14）;2010-08-07
SELECTADD_MONTHS（DATE,-11）;1995-09-07
SELECTADD_MONTHS（‘'1996-07-31'’,2）;1996-09-30
SELECTADD_MONTHS（‘'1995-12-31'’,2）;1996-02-29
SELECTADD_MONTHS（‘'1995-12-31'’,14）;1997-02-28
ANSI标准中利用CAST函数将一种数据类型转换成另一种数据类型。
SELECTCAST（salary_amountASINTEGER）
FROMemployee;
ValueResult
50500.7550500
SELECTCAST（salary_amountASDEC（6,0））
FROMemployee;
ValueResult
50500.7550501.
SELECTCAST（last_nameASCHAR（5））
FROMemployee
WHEREdepartment_number=401;
SELECTCAST（last_nameASCHAR（5）UPPERCASE）
简单的宏
宏（Macro）的基本特征是：
!
可以包含一条或多条SQL语句
!
可以包含多个BTEQ语句
!
可以包含注解
!
存储在数据字典中
CREATEMACROmacronameAS（...）;定义宏
EXECUTEmacroname;执行宏语句
SHOWMACROmacroname;显示宏定义
REPLACEMACROmacronameAS（...）;改变宏定义
DROPMACROmacroname;从字典中删除宏定义
EXPLAINEXECmacroname;显示宏执行的解释
子查询
=ANY等于IN
NOT=ALL等于NOTIN
=SOME等于IN
EXISTS在子查询中的使用
EXISTS可以使用在子查询中，用来表示查询至少返回一行。
如果前面加上否定词NOT，则表示查询时无记录存在。
EXISTS可以代替IN，而NOTEXISTS可以代替NOTIN。
SELECT'YES'
WHEREEXISTS
（SELECTdepartment_numberFROMdepartment
WHEREdepartment_numberNOTIN
（SELECTdepartment_number
FROMemployee））;
SELECTTRUEWHEREEXISTS
（SELECT*FROMemployee
WHEREdepartment_number=600）;
子查询时的一些基本规则。
!
子查询必须用括号括起来
!
子查询可以是IN或NOTIN子句的操作目标
!
也可以是EXISTS或NOTEXISTS子句的操作目标
!
支持限定词ALL,ANY,SOME
!
支持LIKE或NOTLIKE
!
子查询中可以指定匹配多个字段
!
子查询结果均为唯一值，即自动去除重复记录，相当于自动加上
DISTINCT关键词
!
ORDERBY不能用于子查询内
!
子查询中最多可以指定64个表或视图
属性和函数
表达式属性
列和表达式的属性可以定义标题和格式，表达式属性主要包括AS（NAMED）、TITLE和FORMAT。
SELECTlast_name
first_name
salary_amount/12（TITLE'MONTHLY//SALARY'）/*换行*/
FROMemployee
WHEREdepartment_number=401
ORDERBY3;
SELECT...
CAST（salary_amount/12ASTITLE''MONTHLY//SALARY''）
FROM...
CHARACTERS函数
CHARACTERS函数也是Teradata的扩展，用于计算VARCHAR型数据字段的实际字符串长度。
CHARACTERS函数可以简写成CHARACTER、CHARS或者CHAR。
TRIM函数
ANSI标准的TRIM函数用于去除字符数据中前头或后端的空格或者二进制数据（BYTE与VARBYTE）中前头或后端的零
ANSI
TRIM（）去除字符数据中前后端的空格或者二进制数据中前后头的零
TRIM（BOTHFROM）同上
TRIM（TRAILINGFROM）去除后端的空格或二进制零
TRIM（LEADINGFROM）去除前端的空格或二进制零
在Teradata缺省模式下，TRIM（）只能去除后端的空格或二进制零。
查询姓是由四个字符组成的员工有哪些？
SELECTfirst_name,last_name（TITLE'last'）
FROMemployee
WHERECHAR（TRIM（TRAILINGFROMlast_name））=4;
FORMAT
SELECTsalary_amount（FORMAT''$$$,$$9.99''）;
SELECTCAST（salary_amountASFORMAT''$$$,$$9.99''）;
FORMAT短语中可以使用的格式化字符主要为：
!
$美元标识符
!
9数字位
!
Z将数字中的前缀零去除
!
在指定位置插入逗号
!
.指定小数点位置
!
-在指定位置插入连字号
!
/在指定位置插入斜线
!
%在指定位置插入百分号
!
X字符数据，每个X代表一个字符
!
G图形数据.一个G代表一个逻辑字符（双字节）
!
B在指定位置插入空格
日期的格式化处理
YYYY/MM/DD'
YYYY-MM-DD'
YYYY.DDD'
DBMMMBYYYY'
MMBDD,BYYYY'
YYYYBMMMBDD'
YY/MM/DD'
D-MM-YY'
YBDDD
MM'
SELECTlast_name,first_name,birthdate（FORMAT'mmdd'）ASbirthday,birthdateASwhole_date
FROMemployee
WHEREdepartment_number=401
ORDERBY3;
SELECTlast_name,first_name,birthdateMOD10000（FORMAT''9999''）ASbirthday,birthdateASwhole_date
FROMemployee
WHEREdepartment_number=401
ORDERBY3;
对字符数据的截取
1.利用FORMAT
SELECTlast_name,first_name,first_name（FORMAT'X'）
FROMemployee
WHERElast_name='Brown'
ORDERBY3;
使用数据类型的转换
SELECTlast_name,first_name,CAST（first_nameASCHAR
（1））
FROMemployee
WHERElast_name='Brown'
ORDERBY3;
SELECTDISTINCTTYPE（job_code）FROMjob;INTEGER
SELECTDISTINCTTITLE（job_code）FROMjob;job_code
SELECTDISTINCTFORMAT（job_code）FROMjob;（10）9
SELECTDISTINCTNAMED（job_code）FROMjob;job_code
SELECTDISTINCTCHARACTERS（job_code）FROMjob;11
第十章内连接
FROMemployee,department
WHEREemployee.department_number=department.department_number;
FROMemployeeINNERJOINdepartment
ONemployee.department_number=department.department_number;
交叉连接（CrossJoin）
如果在一个连接中没有连接条件，那这个连接就变成了交叉连接（CrossJoin），也称为乘积连接（ProductJoin）。
完全没有任何限制的交叉连接称为笛卡尔乘积（CartesianProduct）
一般来讲，N个表的内连接操作需要定义N-1个连接条件。
在子查询中，返回的结果集必须来自一个表（图中的表1），而另一个表（图中的表2）只是对前一个表的数据作一些限制，看哪些是满足条件的，哪些不满足。
如果返回的结果来自两个或更多的表，则必须使用连接操作。
至于哪些数据可以返回，必须看连接条件的定义，只有匹配连接条件的数据记录才会返回。
第十一章数据定义
TABLE
CREATETABLE
;
SET不允许记录重复,MULTISET
数据保护要结合FALLBACK和JOURNAL（流水或日志）。
FALLBACK是Teradata的一种数据保护机制，数据表的每一条记录都同时存放两份，而且位于不同的AMP所控制的存储单元中；当数据发生问题或者AMP失败时，可以利用存放在其他AMP上的数据保证对数据表的访问。
-FALLBACK使用FALLBACK保护机制
-NOFALLBACK不使用FALLBACK保护机制
CREATEMULTISETTABLEtable_1,FALLBACK,NOJOURNAL,FREESPACE=10PERCENT,DATABLOCKSIZE=16384BYTES
（field1INTEGER）;
字段定义
1.字段名
2.字段数据类型
3.字段数据类型属性
DEFAULT当字段无数据时用默认值来替代NULL
WITHDEFAULT用字段的系统默认值替换NULL
FORMAT缺省的显示格式
TITLE缺省的列标题
NOTNULL不允许空值
CASESPECIFIC字母大小写敏感
UPPERCASE字母大小写不敏感，内部用大写字母存储
4.数据存储属性
COMPRESS压缩值为NULL的字段存储空间为0
COMPRESSNULL同上
COMPRESS压缩值为NULL和指定值的字段存储空间为0
字段约束定义
PRIMARYKEY非空,无重复值
UNIQUE无重复值
CHECK<布尔条件>指定合法值的范围
REFERENCES与其他字段的相关性（外键）---CONSTRAINTref_1REFERENCESjob（job_code）
表级约束定义
唯一性定义
[CONSTRAINTname]
[UNIQUE]所指定的多个字段的组合值在表中不能重复[PRIMARYKEY]这些列将用作主索引或次索引
参照定义
[CONSTRAINTname]
约束名，配合外键的定义
FOREIGNKEY（）所列举的字段为外键，它对应于另一个表（父表）中相同的字段
REFERENCES（）
定义父表或引用表中的主键所包含的字段
Check定义
[CONSTRAINTname]
约束名
CHECK<布尔条件>对表中指定字段的值进行约束
索引定义
PKPrimaryKey主键
PIPrimaryIndex主索引
UPIUniquePrimaryIndex唯一性主索引
NUPINonUniquePrimaryIndex非唯一性主索引
USIUniqueSecondaryIndex唯一性次索引
例：
修改已有字段的属性
ALTERTABLEemp_dataADDbirthdateFORMAT'mmmBdd,Byyyy'';
ALTERTABLEemp_dataMODIFYCONSTRAINTsal_rangeCHECK（salary_amount>0ANDsalary_amount<1000000）;
注意：
表中已有数据如果不符合新的约束条件，约束的增加或修改不能成功。
主索引只能在CREATETABLE时定义，而次索引既可以在创建表时定义，也可以使用CREATEINDEX来定义。
当次索引创建后，也可以利用DROPINDEX来删除它们。
注意，只有次索引可以被删除，主索引是不能被删除的。
当删除命名索引时，可以只指定索引名称，也可以指定索引定义。
而删除未命名索引时，必须指定索引定义。
数据操作DML
DELETEFROM
INSE
升级会员 