SQLTeradata版word文档良心出品.docx

资源描述

SQLTeradata版word文档良心出品.docx

《SQLTeradata版word文档良心出品.docx》由会员分享，可在线阅读，更多相关《SQLTeradata版word文档良心出品.docx（110页珍藏版）》请在冰点文库上搜索。

SQLTeradata版word文档良心出品.docx

SQLTeradata版word文档良心出品

TeradataSQL基础教程

第一章数据定义语言DDL（DataDefinitionLanguage）

Databases数据库

CREATEDATABASE

创建数据库

MODIFYDATABASE

修改数据库定义

DROPDATABASE

删除数据库

Users用户

CREATEUSER

创建用户

MODIFYUSER

修改用户

DROPUSER

删除用户

Tables表

CREATETABLE

创建表

ALTERTABLE

修改表定义

DROPTABLE

删除表

Indexes索引

CREATEINDEX

创建索引

DROPINDEX

删除索引

1.1创建表

CREATETABLE语句创建新表，定义新表的列、索引和其他属性。

新表创建后，表结构定义存放在Teradata的数据字典中。

CREATETABLE语句的语法如下：

CREATETABLE

;

其中：

CreateTableOptions

表选项

定义表的物理属性

Fallback

Journaling

Freespace

Datablocksize

ColumnDefinitions字段定义

定义表的各个字段

Table-levelConstraints

表级约束

定义约束

Primarykey

Unique

CHECK条件

Foreignkey

IndexDefinitions索引定义

定义表索引

1.1.1创建表的可选项（CreateTableOptions）

TeradataDDL允许在创建表时指定表的物理属性，包括：

是否允许重复记录：

SET不允许记录重复（默认）

CREATESETTABLEtable1

MULTISET允许记录重复（建议：

Teradata中都用MultiSet）

CREATEMULTISETTABLEtable1

数据保护：

数据保护要结合FALLBACK和JOURNAL（流水或日志）。

FALLBACK是Teradata的一种数据保护机制，数据表的每一条记录都同时存放两份。

FALLBACK使用FALLBACK保护机制

NOFALLBACK不使用FALLBACK保护机制

日志有BEFORE和AFTER两种，分别保存了一条记录变化前后的状态。

当系统出错时，可以利用日志进行恢复。

1.1.2字段定义

Teradata的表可定义多达256个字段，每个字段的定义包括如下五项：

字段名，在同一数据库中必须唯一。

字段数据类型。

例：

CREATETABLEemp_data

（employee_numberINTEGERNOTNULL

last_nameCHAR（20）NOTNULLWITHDEFAULT

street_addressVARCHAR（30）TITLE‘Address’

cityCHAR（15）DEFAULT‘Boise’

stateCHAR

（2）WITHDEFAULT

birthdateDATEFORMAT’mm/dd/yyyy’

salary_amountDEC（10,2）

sexCHAR

（1）UPPERCASE

）;

字段数据类型属性。

可定义如下属性：

DEFAULT

当字段无数据时用默认值来替代NULL

WITHDEFAULT

用字段的系统默认值替换NULL

FORMAT

缺省的显示格式

TITLE

缺省的列标题

NOTNULL

不允许空值

CASESPECIFIC

字母大小写敏感

UPPERCASE

字母大小写不敏感，内部用大写字母存储

数据存储属性。

包括下面各项：

COMPRESS

压缩值为NULL的字段存储空间为0

COMPRESSNULL

同上

COMPRESS

压缩值为NULL和指定值的字段存储空间为0

字段约束定义。

Teradata支持字段级约束，即限制字段的值满足某些条件，如某个字段取值是否唯一、是否为主键或外键等。

对字段的约束总结如下：

CONSTRAINTname

约束名称--可选

PRIMARYKEY

非空,无重复值

UNIQUE

无重复值

CHECK<布尔条件>

指定合法值的范围

REFERENCES

与其他字段的相关性（外键）

例：

CREATETABLEemployee_badge

（emp_idINTEGERNOTNULL

CONSTRAINTprimary_1PRIMARYKEY

id_badge_numberINTEGER

CONSTRAINTunique_1UNIQUE

salaryINTEGER

CONSTRAINTcheck_1CHECK（salary>0）

job_codeINTEGER

CONSTRAINTref_1REFERENCESjob（job_code）

）;

最后一项定义了在EMPLOYEE_BADGE表中的JOB_CODE必须和JOB表中的JOB_CODE对应，即前一个表中该字段的值必须在第二个表中有对应的项。

这实际上是一种所谓的参照完整性。

另外要注意的是，具有主键（PrimaryKey）约束的字段一定要定义为非空（NOTNULL）。

1.1.3表级约束定义

表级约束

意义

唯一性定义[CONSTRAINTname]

约束名

[UNIQUE]

所指定的多个字段的组合值在表中不能重复

[PRIMARYKEY]

这些列将用作主索引或次索引

参照定义[CONSTRAINTname]

约束名，配合外键的定义

FOREIGNKEY（）

所列举的字段为外键，它对应于另一个表（父表）中相同的字段

REFERENCES（）

定义父表或引用表中的主键所包含的字段

Check定义[CONSTRAINTname]

约束名

CHECK<布尔条件>

对表中指定字段的值进行约束

表级约束与字段级约束的主要区别是：

在表级约束中可以指定当前表的多个字段或其组合，而字段级约束只能引用当前字段。

我们来看一个表级约束的例子。

CREATETABLEemployee_badge

（emp_idINTEGERNOTNULL

id_badge_numINTEGERNOTNULL

salaryINTEGER

job_codeINTEGER

CONSTRAINTprimary_1PRIMARYKEY（emp_id）

CONSTRAINTunique_1UNIQUE（id_badge_num）

CONSTRAINTcheck_1CHECK（salary>0AND

job_codeBETWEEN100000AND499999）

CONSTRAINTref_1FOREIGNKEY（job_code）

REFERENCESjob（job_code））;

比较字段级约束和表级约束的例子,可以看到:

字段级约束必须写在每个字段定义的后面，而表级约束是在字段定义结束后再进行的。

在表级约束中，一个约束可以同时定义多个字段。

1.1.4索引定义

PKPrimaryKey

主键

PIPrimaryIndex

主索引

UPIUniquePrimaryIndex

唯一性主索引

NUPINonUniquePrimaryIndex

非唯一性主索引

USIUniqueSecondaryIndex

唯一性次索引

索引可以在CREATETABLE时就加以定义，同时还可以定义主键。

如果创建表时不定义主索引，Teradata就按照下面的规则缺省来建立主索引，因为没有主索引的话，Teradata就无法进行数据的分配。

没有在CREATETABLE时指定PI:

IF定义了PK，THENPK=UPI

ELSEIF存在定义为UNIQUE的字段，

THEN第一个NIQUE的字段为UPI

ELSE表中定义的第一个字段作为NUPI

CREATETABLE时指定了PI:

IF定义了PK，THENPK作为USIAND为每一个定义为UNIQUE的字段建立一个USI

下面是一个比较复杂的创建表的例子，注意学习。

创建该表后用SHOWTABLE观察一下内部的表达方式。

CREATEMULTISETTABLEemp_data

FALLBACK

NOBEFOREJOURNAL

NOAFTERJOURNAL

FREESPACE=30

DATABLOCKSIZE=10000BYTES（

employee_numberINTEGERNOTNULL

department_numberSMALLINT

CONSTRAINTdep_check

CHECK（department_numberBETWEEN100AND999）

REFERENCESDepartment（department_number）

job_codeINTEGERCOMPRESS

last_nameCHAR（20）NOTNULL

first_nameVARCHAR（20）

street_addressVARCHAR（30）TITLE'Address'

cityCHAR（15）DEFAULT'Boise'

COMPRESS'Boise'

stateCHAR

（2）WITHDEFAULT

birthdateDATEFORMAT'mm/dd/yyyy'

salary_amountDECIMAL（10,2）

sexCHAR

（1）UPPERCASE

CONSTRAINTemp_key

PRIMARYKEY（employee_number）

）INDEX（department_number）;

1.2删除表

可以使用DROPTABLE语句删除表，该语句将删除表中的所有数据和在数据字典中的表结构定义。

例：

删除前面例子中创建的雇员数据表。

DROPTABLEemp_data;删除了表emp_data中的所有数据，并删除了emp_data在数据字典中的定义。

如果希望再使用这个表，必须重新创建。

例：

DELETEFROMemp_data;或DELETEemp_data;删除了表emp_data中的所有数据。

表定义仍然存在，可以增加数据。

1.3修改表

当一个表已经创建后，可以使用ALTERTABLE语句来修改其定义。

表定义的一些属性是不可修改的（如PI），如果要改变这些属性，常用方法是建立一个满足新属性的新表，然后使用Insert-Select语句把数据从原来的表转移到新表，然后再修改新表的名称。

ALTERTABLE完整的语法可参见TeradataSQL手册，这里通过几个例子来加以说明。

例：

增加或删除字段

ALTERTABLEemp_data

ADDeduc_levelCHAR

（1）,ADDinsure_typeSMALLINT;

ALTERTABLEemp_data

DROPeduc_level,DROPinsure_type;

例：

修改已有字段的属性

ALTERTABLEemp_data

ADDbirthdateFORMAT'mmmBdd,Byyyy'';

例：

对没有FALLBACK的表建立FALLBACK保护

ALTERTABLEemp_data,FALLBACK;

例：

同时修改表的多个属性

ALTERTABLEemp_data

NOFALLBACK

DROPinsure_type

ADDeduc_levelCHAR

（1）;

例：

修改约束定义

增加约束

ALTERTABLEemp_data

ADDCONSTRAINT

CHECK（sex='F'ORsex='M'）;

修改约束:

ALTERTABLEemp_data

MODIFYCONSTRAINTsal_range

CHECK（salary_amount>0ANDs注意：

表中已有数据如果不符合新的约束条件，约束的增加或修改不能成功。

删除约束：

ALTERTABLEemp_data

DROPCONSTRAINTsal_range;

alary_amount<1000000）;

1.4次索引

前面已经讨论过索引，并且说明，创建表时就应定义主索引，同时也可以定义次索引。

事实上，次索引也可以使用单独的CREATEINDEX语句来定义。

换言之，主索引只能在CREATETABLE时定义，而次索引既可以在创建表时定义，也可以使用CREATEINDEX来定义。

例：

为雇员表创建下面两个次索引。

为雇员名字建立命名的唯一次索引USI

CREATEUNIQUEINDEXfullname（last_name,first_name）ONemp_data;

为工作代码建立非唯一性次索引NUSI，不命名NUSI

CREATEINDEX（job_code）ONemp_data;

从这个例子可以看到，次索引可以命名，如第一个USI的名字为FULLNAME；也可以不命名，如第二个NUSI就没有取名。

定义好索引或次索引后，可以利用HELPINDEX<表名>来显示指定表的所有索引定义，如果索引是未命名的，索引名称显示为NULL。

当次索引创建后，也可以利用DROPINDEX来删除它们。

注意，只有次索引可以被删除，主索引是不能被删除的。

当删除命名索引时，可以只指定索引名称，也可以指定索引定义。

而删除未命名索引时，必须指定索引定义。

例：

删除雇员表的所有次索引

删除命名索引

DROPINDEXFullNameONemp_data;

删除未命名索引

DROPINDEX（job_code）ONemp_data;

第二章数据操作语言（DataManipulationLanguage）

2.1INSERT

INSERT语句用于向表中添加一行或多行记录。

插入一行记录的命令格式为：

INSERTINTO<表名>（列名1，列名2,...,列名n）

VALUES（列值表达式1，列值表达式2,...,列值表达式n）;

例：

在雇员表中添加一新雇员信息：

INSERTINTOemployee（last_name,first_name,hire_date,birthdate,

salary_amount,employee_number）

VALUES（arcia',aria',861027,541110,76500.00,1291）;

如果添加整条记录，即给每个字段都有相应的值，则表名后的字段名可以省略。

如上面的例子可以改写成：

INSERTINTOemployee

VALUES（1210,NULL,401,41201,mith',ames',890303,460421,41000）;

Teradata对INSERT作了扩充，增加了一个称为INSERT-SELECT的功能。

它以子查询的方式将一个表的数据抽取并插入到另一个表中。

举例来说，假设表emp_copy与表emp的结构相同，下面的语句可以把表emp的所有行添加到表emp_copy中，即复制表emp。

INSERTINTOemp_copy

SELECT*FROMemp;

INSERT-SELECT也可以将不同结构表的记录添加到目标表中。

例我们创建一张雇员生日表：

CREATETABLEbirthdays

（empnoINTEGERNOTNULL

lnameCHAR（20）NOTNULL

fnameVARCHAR（30）

birthDATE）

UNIQUEPRIMARYINDEX（empno）;

然后，我们从雇员表中提取生日信息添加到生日表中。

INSERTINTObirthdays

SELECTemployee_number,last_name,first_name,birthdate

FROMemployee;

2.2UPDATE

UPDATE语句用来更新表内满足条件的数据记录，基本语法为：

UPDATE<表名>

SET<列名1>=<列值表达式1>

<列名2>=<列值表达式2>

...

<列名n>=<列值表达式n>

WHERE<条件子句>;

如果UPDATE语句中没有WHERE子句，则更新表中的所有记录。

下面的例句是将编号为1010的雇员的部门编号修改为403，工作编号修改为432101，经理的雇员编号修改为1005。

UPDATEemployee

SETdepartment_number=403

job_code=432101

manager_employee_number=1005

WHEREemployee_number=1010;

TeradataSQL允许在WHERE子句中使用子查询和联接，因此同样的工作可以通过子查询或联接来实现。

考虑如下的情况，我们要给支援部门的人员加薪10%，如果使用子查询的方式，可以写成：

UPDATEemployee

SETsalary_amount=salary_amount*1.10

WHEREdepartment_numberIN

（SELECTdepartment_number

FROMdepartment

WHEREdepartment_nameLIKE'%Support%'）;

如果使用联接的方式，可以写成：

UPDATEemployee

SETsalary_amount=salary_amount*1.10

WHEREemployee.department_number=

department.department_number

ANDdepartment_nameLIKE'%Support%';

2.3DELETE

DELETE删除表中满足条件的记录，基本语法为：

DELETEFROM<表名>

WHERE<条件子句>;

如果DELETE语句中没有WHERE语句，则删除表中的所有行。

例：

删除雇员表中编号为301的员工：

DELETEFROMemployeeWHEREdepartment_number=301;

删除雇员表的所有数据，可使用：

DELETEFROMemployee;

与UPDATE语句相似，DELETE语句也支持子查询和联接操作。

假设在雇员表中，删除所在部门名称为'None'的所有雇员信息，使用子查询的语句为：

DELETEFROMemployee

WHEREdepartment_numberIN

（SELECTdepartment_number

FROMdepartment

WHEREdepartment_name='None'）;

使用连接的语句为：

DELETEFROMemployee

WHEREemployee.department_number=department.dempartment_number

ANDdepartment.department_name='None';

第三章逻辑与条件表达式

3.1逻辑表达式运算符

逻辑表达式由运算符和操作数两部分组成，其结果是一个布尔值（True/False）。

这种表达式可以用在WHERE子句的条件表达式中。

标准的逻辑表达式运算符种类如表5-1所示：

表5-1逻辑表达式运算符分类

运算符种类

符号

含义

比较运算符

等于

不等于

大于

小于

大于或等于

小于或等于

[NOT]BETWEENAND

一个查询至少返回一行或不返回任何行

LIKE

与某个数值匹配

Teradata还提供了下面一组扩展符号，可以与标准的比较运算符相互替换。

Teradata扩展

ANSI标准

3.2逻辑表达式

3.2.1[NOT]BETWEEN...AND

BETWEENAND表示某列数值（数字型或字符型）介于a和b之间，且包括a和b。

举例来说，在雇员表中把所有工作代码以43开头的员工的姓名及其经理的员工代码找出来。

可以使用下面的SQL语句，其中第二个查询就使用了BETWEEN...AND操作符。

SELECTfirst_name

last_name

manager_employee_number

FROMemployee

WHEREjob_code>=430000AND

job_code<=439999;

SELECTfirst_name

last_name

manager_employee_number

FROMemployee

WHEREjob_codeBETWEEN430000AND439999;

需要注意的是，当操作数是字符时，ANSI标准是区分大小写的，而Teradata中缺省不区分大小写，但可以通过CASESPECIFIC/NOTCASESPECIFIC来明确定义是否区分。

如：

SELECT...

WHERElast_name（CASESPECIFIC）;

SELECT...

WHERElast_name（NOTCASESPECIFIC）;

查询姓以字母R开头的员工，可以使用下面的SQL语句：

SELECTlast_nameFROMemployee

WHERElast_nameBETWEEN'r'AND's';

由于Teradata的缺省方式不区分大小写，因此，Ryan、Roger等都将作为结果返回。

如果显示区分大小写，上面的表达式中必须使用大写字母R和S。

3.2.2集合操作符[NOT]IN

[NOT]IN表示某列数值属于或不

展开阅读全文