teradataSQL学习笔记.docx
- 文档编号:12450298
- 上传时间:2023-04-19
- 格式:DOCX
- 页数:22
- 大小:25.88KB
teradataSQL学习笔记.docx
《teradataSQL学习笔记.docx》由会员分享,可在线阅读,更多相关《teradataSQL学习笔记.docx(22页珍藏版)》请在冰豆网上搜索。
teradataSQL学习笔记
13.15.23.24.25.26.28.29
什么时候要在表名前加数据库的名字,为什么要加
不加的时候当前默认数据库为什么是DWMART_BWCM
A:
这是可以设置的。
FORMAT不好用
A:
这是可以设置的。
TITLEvsAS
A:
能用哪个用哪个。
单引号vs双引号
A:
都用单引号,保险。
P118.数据存储属性。
包括下面各项:
COMPRESS
压缩值为NULL的字段存储空间为0
COMPRESSNULL
同上
COMPRESS
压缩值为NULL和指定值的字段存储空间为0
A:
压缩比如10000条记录某一属性都为1,字符型,那么所占存储空间就应该是1000字节;经过compress之后,可能就变成2字节,或者远远比原来所占存储空间更小的内存。
但是!
compress的过程是消耗系统资源的,也就是会对执行效率有一定的影响。
所以是否使用compress就是效率和空间的一个权衡过程。
-Ch10内连接-
✪INNERJOIN。
。
。
ON后面的条件应该用主键连接,为避免连接结果中数据重复。
eg.
selb.DIVIDEID,b.REPASSETTYPEID_ON,b.REPASSETTYPEID_ON_DESC,count(*)
fromdwmart_bwcm.N_exposuresa
innerjoindwmart_rwa.fc_check_exposure_divide_315b
ona.ACCOUNTREFCD=b.ACCOUNTREFCD
wherea.timeid=20130630
anda.SOURCEID=9
anda.ASSETTYPEID=20
anda.ACCOUNTING_SUBJECTin('3125','3126')
andb.timeid=20130630
andb.DIVIDEID=9
groupby1,2,3
orderby1,2,3;
selab.DIVIDEID,ab.REPASSETTYPEID_ON,ab.REPASSETTYPEID_ON_DESC,count(*)
fromdwmart_rwa.fc_check_exposure_divide_315ab
wheretimeid=20130630
andab.ACCOUNTREFCDin
(selA.ACCOUNTREFCD
fromdwmart_bwcm.N_exposuresa
wherea.timeid=20130630
anda.SOURCEID=9
anda.ASSETTYPEID=20
anda.ACCOUNTING_SUBJECTin('3125','3126')
groupby1)
andab.DIVIDEID=9
groupby1,2,3
orderby1,2,3;
解释:
两个查询结果的COUNT(*)不一样,因为第一个语句块中的内连接字段ACCOUNTREFCD不是表的主键,该字段内存在重复数据,所以内连接的结果有重复数据,导致计数结果不一致。
✪SELFJOIN
什么时候要用自连接?
【一般能看到的例子就是以下两种】
1.要把所有姓Brown的雇员及其经理找出来
SELECTemp.first_name(TITLE'Emp//FirstName')
emp.last_name(TITLE'Emp//LastName')
mgr.first_name(TITLE'Mgr//FirstName')
mgr.last_name(TITLE'Mgr//LastName')
FROMemployeeemp
INNERJOINemployeemgr
ONemp.manager_employee_number=mgr.employee_number
WHEREemp.last_name='Brown';
2.查找所有在1978年1月1日以前加入公司的雇员,显示其雇员编号、部门代码以及其部门经理的姓名,将结果按部门编号排序
SELe.employee_number,
e.department_number,
m.last_name
FROMemployeee
INNERJOINemployeem
ONe.manager_employee_number=m.employee_number
WHEREe.hire_date<780101
ORDERBY2;
思考:
当一个表内存在两列属性性质相同的时候(如员工号和经理的员工号都是员工号),可能需要将该属性作为连接条件对表进行自连接。
-Ch11数据定义DDL-
1.定义------------------------------------------------------------------------------------------------------------
具有主键(PrimaryKey)约束的字段一定要定义为非空(NOTNULL)。
表级约束与字段级约束的主要区别是:
在表级约束中可以指定当前表的多个字段或其组合,而字段级约束只能引用当前字段。
字段级约束必须写在每个字段定义的后面,而表级约束是在字段定义结束后再进行的。
在原来的DDL中有关主键和UNIQUE的约束在Teradata内部表示为主索引和次索引。
当定义好约束后,可以使用
HELPCONSTRAINTdatabase_name.table_name.contraint_came
的方法来观察约束的有关信息。
索引可以在CREATETABLE时就加以定义,同时还可以定义主键。
如果创建表时不定义主索引,Teradata就按照下面的规则缺省来建立主索引,因为没有主索引的话,Teradata就无法进行数据的分配。
CREATETABLE时没有指定PI
●IF定义了PK,THENPK=UPI
●ELSEIF存在定义为UNIQUE的字段,THEN第一个NIQUE的字段为UPI
●ELSE表中定义的第一个字段作为NUPI
CREATETABLE时指定了PI
●IF定义了PK,THENPK作为USI
●AND为每一个定义为UNIQUE的字段建立一个USI
2.删除------------------------------------------------------------------------------------------------------------
DROPTABLEtable_name;--删除表定义
DELETEFROMtable_name;--删除表数据
DELETEtable_name;
3.修改------------------------------------------------------------------------------------------------------------
ALTER修改表定义、约束定义
删除FALLBACK的方法
ALTERTABLEtable_name,NOFALLBACK;
表中已有数据如果不符合新的约束条件,约束的增加或修改不能成功。
4.索引------------------------------------------------------------------------------------------------------------
创建表时就应定义主索引,同时也可以定义次索引。
主索引只能在CREATETABLE时定义,而次索引既可以在创建表时定义,也可以使用CREATEINDEX来定义。
次索引可以命名,也可以不命名。
定义好索引或次索引后,可以利用HELPINDEX<表名>来显示指定表的所有索引定义,如果索引是未命名的,索引名称显示为NULL。
当次索引创建后,也可以利用DROPINDEX来删除它们。
注意,只有次索引可以被删除,主索引是不能被删除的。
当删除命名索引时,可以只指定索引名称,也可以指定索引定义。
而删除未命名索引时,必须指定索引定义。
--删除命名索引
DROPINDEXFullNameONemp_data;
--删除未命名索引
DROPINDEX(job_code)ONemp_data;
-------------------╮(╯_╰)╭偶系面无表情的分割线-------------------
代码块1:
SELXXX,XXX,XXX
FROMtable_1
INNERJOINtable_2
ONconditions;
代码块2:
SELXXX,XXX,XXX
FROMtable_1
CROSSJOINtable_2
WHEREconditions;
1.CROSSJOIN不可以和ON搭配使用,只能用WHERE或者不用;
2.当conditions与两表连接无关的时候,代码块1==代码块2;
3.INNERJOIN一定要和ON搭配使用,否则语法错误
-Ch12数据操作DML-
1.INSERT
数据操作完成对数据库中单个表(或视图)记录的添加、修改和删除,所使用的SQL命令为SELECT、INSERT、UPDATE和DELETE。
INSERT语句用于向表中添加一行或多行记录。
插入一行记录的命令格式为:
INSERTINTO<表名>(列名1,列名2,...,列名n)
VALUES(列值表达式1,列值表达式2,...,列值表达式n);
Teradat对INSERT作了扩充,增加了一个称为INSERT-SELECT的功能。
它以子查询的的方式将一个表的数据抽取并插入到另一个表中。
前提是这两个表的结构相同
INSERTINTOtable_copy
SELECT*FROMemp;
2.UPDATE
如果UPDATE语句中没有WHERE子句,则更新表中的所有记录。
3.DELETE
如果DELETE语句中没有WHERE语句,则删除表中的所有行。
交易完整性
在Teradata缺省模式下,以分号结束的每个SQL语句都是一个完整的交易,也可以使用BT(BeginTransaction)和ET(EndTransaction)来显示地定义一个交易。
●缺省方式
.LOGON
INSERTrow1;(txn#1)
INSERTrow2;(txn#2)
.LOGOFF
●用BT和ET显示定义交易
.LOGON
BT;
INSERTrow1;(txn#1)
INSERTrow2;
COMMITWORK;
ET;
.LOGOFF
第一部分中有两个SQL语句,用分号结束,表示两个交易,任何一个失败不会影响另一个的执行。
而第二部分用BT和ET显示地规定:
在BT和ET之间的所有SQL是一个交易,只有最后的COMMITWORK执行成功后,才会真正地更新数据库。
执行过程中任何一个SQL语句失败,都会使整个交易失败,系统将自动进行恢复(Rollback)处理。
-Ch14分组与聚合-
1.聚合操作用来完成对一组指定数据进行聚合计算,完成聚合计算的函数主要有:
●MIN求最小值,计算中忽略空值。
●MAX求最大值,计算中忽略空值。
●SUM求合计,计算中忽略空值。
●COUNT返回个数,计算中包括空值。
●AVG求平均值,计算中忽略空值。
2.利用GROUPBY和聚合函数可以实现分组累计。
注意:
在SELECT子句中不作分组累计的所有字段必须出现在GROUPBY子句中,否则会返回如下出错信息:
ERROR:
3504Selectednon-aggregatevaluesmustbepartoftheassociatedgroup.
WHERE子句和GROUPBY子句同时使用时,GROUPBY只对符合WHERE限制的数据记录进行分组聚合计算。
换言之,在作真正的聚合计算之前,WHERE子句将不符合条件的数据记录剔除了。
当GROUPBY中有多个字段时,它只能产生一个级别的汇总,而且是按照最后一个字段来进行汇总。
如果聚合函数使用的是SUM(),而在处理过程中需要求平均值,那么就要把SUM()的结果除以一个总数,也就是COUNT(*),这里的*可以用聚合字段中的任意一个代替,结果都是一样的。
也就是说COUNT()所计数的对象是每一个分组。
eg1.
当对多个字段进行分组统计时,GROUPBY只能产生一个级别的汇总。
例如:
对部门401和403按照工作代码分组统计薪水。
SELECTdepartment_number
job_code
SUM(salary_amount)
FROMemployee
WHEREdepartment_numberIN(401,403)
GROUPBYdepartment_number,job_code
ORDERBY1,2;
结果:
department_numberjob_codeSUM(salary_amount)
40141110037850.00
401412101107825.00
40141210256800.00
40141320143100.00
40343110031200.00
403432101201800.00
●如果上例改成GROUPBY2,1,那么结果也是一样的。
eg2.以下三段SQL结果相同。
SELECTdepartment_number
job_code
SUM(salary_amount)/COUNT(*)(TITLE'avg_salary')
FROMemployee
WHEREdepartment_numberIN(401,403)
GROUPBYdepartment_number,job_code
ORDERBY1,2;
SELECTdepartment_number
job_code
SUM(salary_amount)/COUNT(job_code)(TITLE'avg_salary')
FROMemployee
WHEREdepartment_numberIN(401,403)
GROUPBYdepartment_number,job_code
ORDERBY2,1;
SELECTdepartment_number
job_code
SUM(salary_amount)/COUNT(department_number)(TITLE'avg_salary')
FROMemployee
WHEREdepartment_numberIN(401,403)
GROUPBYdepartment_number,job_code
ORDERBY2,1;
HAVING条件子句是和GROUP一起使用的,用来对分组统计的结果进行限定,只返回满足其条件的分组统计结果。
在进行分组聚合操作时,要特别注意以下各点:
●WHERE:
用来限定参与分组聚合运算的表的数据记录,只有满足条件的数据记录才会被选中参与分组聚合。
●GROUPBY:
将符合WHERE条件子句的记录进行分组
●HAVING:
用来限定可以返回的分组聚合的结果
●ORDERBY:
用来指定结果的输出顺序
习题14-1报错
因为“在SELECT子句中不作分组累计的所有字段必须出现在GROUPBY子句中”,而且字段salary_amount并没有全部作分组累计,所以也应该出现在groupby子句里面。
-Ch16集合操作-
集合操作主要包括:
合并操作(UNION)、相交操作(INTERSECT)和排外操作(EXCEPT)
合并操作——合并所有SELECT语句的结果,重复记录在结果集中只显示一次
UNION连接两个或更多的查询,虽然每个查询的字段名可以不相同,但必须具有相同的字段数和数据类型。
使用UNION的基本规则:
1.所有的SELECT语句:
●必须要有同样多的表达式数目
●相关表达式的域必须兼容
2.第一个SELECT语句:
●决定输出的格式(FORMAT)
●决定输出的标题(TITLE)
3.最后一个SELECT语句:
●包含整个结果集的ORDERBY选项(如果有的话)
●ORDERBY后面的列最好用数字顺序表示
做UNION操作的两张表结构通常是一模一样的
相交操作——返回在每一个SELECT语句结果中都存在的记录。
INTERSECT用来连接两个SELECT查询,这两个查询所返回的数据集必须具有相同的字段数和数据类型。
经相交操作后,只有在两个查询中完全相同的数据行才会返回。
排他操作——返回第一个SELECT语句结果中除第二个SELECT语句结果中以外的所有记录。
EXCEPT连接的两个SELECT查询所返回的字段名可以不同,但数目和数据类型必须一致。
在有些数据库系统中,排它操作也称为相减操作(MINUS)。
-Ch17视图-
利用CREATEVIEWview_nameAS…命令可以创建视图。
需要注意的是,视图是基于一些表来实现的,如果这些表的定义发生了变化,将不会反映到视图的定义中。
此时将根据需要进行手工的同步更新或维护。
可以利用视图来更新记录
利用视图可以对列进行重新改名。
CREATEVIEWshortcut(emp,dept,last,first,sal)
ASSELECTemployee_number
department_number
last_name
first_name
salary_amount
FROMemployee
WHEREdepartment_number=201;
视图的限制:
●不能基于视图来建立索引,因为视图只是一个定义,本身没有任何数据
●视图中不能包含ORDERBY子句
●派生和聚合的列必须要有一个AS子句指定列名
●视图不能被UPDATE,如果它包含:
❍数据来自多个表(JOINVIEW)
❍两次同样的列
❍派生的列
❍包含DISTINCT子句
❍包含GROUPBY子句
视图的作用和特点:
●提供了一个额外的安全、授权层次
●帮助控制读和修改权利
●如果基表增加了列,该列对视图无影响。
●如果列从基表中删除,视图也不受影响,除非删除跟该列相关的视图。
●简单化了用户存取
根据视图的特点,对于生产系统,我们建议:
●对每一个基表建立至少一个视图
●根据需要建立视图查询
内连接的时候要是select的列中有innerjoin的两个表都同时含有的列,则一定要指明该列是属于哪个表的,即写明table_name.column_name。
-Ch18字符串函数-
●利用SUBSTRING析取字符串
●利用串联符号"||"合并字符串
●利用INDEX定位字符串的开始位置
1.SUBSTRING函数用来从字符串中析取一个子字符串
其格式为:
SUBSTRING(<字符串表达式>FROM<开始位置>[FOR<长度>])
✧第一个字符的位置标号为1
✧开始位置可为负值
✧没有长度默认为到字符串结束位置
当SUBSTRING函数作用于整型数据类型时,SUBSTRING函数先将整型函数转化为字符串。
对于不同的整型数据类型,它们的长度是不一样的,归纳为:
✧BYTEINT加上符号后4个字符长eg.+127
✧SMALLINT加上符号后6个字符长eg.+00213
✧INTEGER加上符号后11个字符长eg.+005554134
当整型数转换为字符类型时,数字向右对齐,不足位补零,最前面为符号位。
2.字符串合并的符号是"||",它把两个字符串串联成一个字符串。
其基本格式为:
<字符串1>||<字符串2>
注意,BYTE数据类型仅能跟BYTE数据类型进行串联。
3.INDEX用来在一个字符串中定位一个子串的开始位置。
INDEX(被查的字符串,子串)
如果字符串中不存在子串就返回0
COALESCE?
[P21721.4]
18-3答案少了一对括号
如果没有用coalesce的话ext为null会使整个字段为null
Teradata的Null显示为问号?
A:
没错,这是可以设置的。
18-4?
-Ch19外连接-
在左边的永远是主表!
这里要区分二个概念:
即无效的值和空值。
如果在雇员表中某个雇员的部门号为100,而100这个值在部门表中不存在,它就是一个无效的值(不一致);如果雇员表中某个雇员的部门号未输入,它就是一个空值(即NULL)。
只取有效数据用INNERJOIN
同时取有效数据和无效数据用OUTERJOIN
在进行多表外连接操作时应该记住两点:
1、ON子句的顺序规定了处理连接的顺序
2、每个连接操作形成一个临时表,此临时表再与后续的表时行进一步的连接操作。
-*Ch20相关子查询和导出表-
相关子查询是指子查询(内层查询)中引用了外层查询所引用表的字段,因此外层查询处理每一条记录时都必须执行一次子查询,因为子查询中引用的字段的值发生了变化。
P203和P204例子结果不一样
Notin得不出应该得到的正确结果
Notexists可以得出正确结果!
(Lab20-3)
导出表就是一个命名的临时表,它在整个SQL语句中自动创建和删除。
在定义导出表的整个SQL语句中都可以通过导出表名来引用它。
这个导出表在整个SQL语句中都可以引用。
临时表不用看
导出表一般由FROM子句导出表,语法为:
FROM(子查询)[AS]<导出表名称>[(列表)]
●导出表实际上还是一种临时表,如果把临时表的操作集成在一条SQL语句中,就是导出表。
●出表在整个SQL语句范围内有效。
●完成指定的SQL操作后导出表将自动被删除。
-Ch21CASE表达式-
CASE表达式被用来根据搜索的条件返回可选的值。
CASE表达式有两种格式:
●Valued(基于值)
●Searched(基于搜索)
基于值
CASEvalue-exprWHENexpr1THENresult1
WHENexpr2THENresult2
:
ELSEresult
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- teradataSQL 学习 笔记