SQL(Teradata版)

TeradataSQL基础教程第一章数据定义语言DDL(DataDefinitionLanguage)Databases数据库CREATEDATABASE创建数据库MODIFYDATABASE修改数据库定义DROPDATABASE删除数据库Users用户CREATEUSER创建用户MODIFYUSER修改用户DROPUSER删除用户Tables表CREATETABLE创建表ALTERTABLE修改表定义DROPTABLE删除表Indexes索引CREATEINDEX创建索引DROPINDEX删除索引1.1创建表CREATETABLE语句创建新表，定义新表的列、索引和其他属性。新表创建后，表结构定义存放在Teradata的数据字典中。CREATETABLE语句的语法如下：CREATESET/MULTISETTABLETableNameCreateTableOptionsColumnDefinitionsTable-levelConstraintsIndexDefinitions;其中：CreateTableOptions表选项定义表的物理属性FallbackJournalingFreespaceDatablocksizeColumnDefinitions字段定义定义表的各个字段Table-levelConstraints表级约束定义约束PrimarykeyUniqueCHECK条件ForeignkeyIndexDefinitions索引定义定义表索引1.1.1创建表的可选项(CreateTableOptions)TeradataDDL允许在创建表时指定表的物理属性，包括：是否允许重复记录：SET不允许记录重复（默认）CREATESETTABLEtable1MULTISET允许记录重复（建议：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当字段无数据时用默认值来替代NULLWITHDEFAULT用字段的系统默认值替换NULLFORMAT缺省的显示格式TITLE缺省的列标题NOTNULL不允许空值CASESPECIFIC字母大小写敏感UPPERCASE字母大小写不敏感，内部用大写字母存储数据存储属性。包括下面各项：COMPRESS压缩值为NULL的字段存储空间为0COMPRESSNULL同上COMPRESSconstant压缩值为NULL和指定值的字段存储空间为0字段约束定义。Teradata支持字段级约束，即限制字段的值满足某些条件，如某个字段取值是否唯一、是否为主键或外键等。对字段的约束总结如下：CONSTRAINTname约束名称--可选PRIMARYKEY非空,无重复值UNIQUE无重复值CHECK布尔条件指定合法值的范围REFERENCES与其他字段的相关性(外键)例：CREATETABLEemployee_badge(emp_idINTEGERNOTNULLCONSTRAINTprimary_1PRIMARYKEY,id_badge_numberINTEGERCONSTRAINTunique_1UNIQUE,salaryINTEGERCONSTRAINTcheck_1CHECK(salary0),job_codeINTEGERCONSTRAINTref_1REFERENCESjob(job_code));最后一项定义了在EMPLOYEE_BADGE表中的JOB_CODE必须和JOB表中的JOB_CODE对应，即前一个表中该字段的值必须在第二个表中有对应的项。这实际上是一种所谓的参照完整性。另外要注意的是，具有主键(PrimaryKey)约束的字段一定要定义为非空(NOTNULL)。1.1.3表级约束定义表级约束意义唯一性定义[CONSTRAINTname]约束名[UNIQUE]所指定的多个字段的组合值在表中不能重复[PRIMARYKEY]这些列将用作主索引或次索引参照定义[CONSTRAINTname]约束名，配合外键的定义FOREIGNKEY(col_list)col_list所列举的字段为外键，它对应于另一个表(父表)中相同的字段REFERENCEStablename(primarykeylist)定义父表或引用表中的主键所包含的字段Check定义[CONSTRAINTname]约束名CHECK布尔条件对表中指定字段的值进行约束表级约束与字段级约束的主要区别是：在表级约束中可以指定当前表的多个字段或其组合，而字段级约束只能引用当前字段。我们来看一个表级约束的例子。CREATETABLEemployee_badge(emp_idINTEGERNOTNULL,id_badge_numINTEGERNOTNULL,salaryINTEGER,job_codeINTEGER,CONSTRAINTprimary_1PRIMARYKEY(emp_id),CONSTRAINTunique_1UNIQUE(id_badge_num),CONSTRAINTcheck_1CHECK(salary0ANDjob_codeBETWEEN100000AND499999),CONSTRAINTref_1FOREIGNKEY(job_code)REFERENCESjob(job_code));比较字段级约束和表级约束的例子,可以看到:字段级约束必须写在每个字段定义的后面，而表级约束是在字段定义结束后再进行的。在表级约束中，一个约束可以同时定义多个字段。1.1.4索引定义PKPrimaryKey主键PIPrimaryIndex主索引UPIUniquePrimaryIndex唯一性主索引NUPINonUniquePrimaryIndex非唯一性主索引USIUniqueSecondaryIndex唯一性次索引索引可以在CREATETABLE时就加以定义，同时还可以定义主键。如果创建表时不定义主索引，Teradata就按照下面的规则缺省来建立主索引，因为没有主索引的话，Teradata就无法进行数据的分配。没有在CREATETABLE时指定PI:IF定义了PK，THENPK=UPIELSEIF存在定义为UNIQUE的字段，THEN第一个NIQUE的字段为UPIELSE表中定义的第一个字段作为NUPICREATETABLE时指定了PI:IF定义了PK，THENPK作为USIAND为每一个定义为UNIQUE的字段建立一个USI下面是一个比较复杂的创建表的例子，注意学习。创建该表后用SHOWTABLE观察一下内部的表达方式。CREATEMULTISETTABLEemp_data,FALLBACK,NOBEFOREJOURNAL,NOAFTERJOURNAL,FREESPACE=30,DATABLOCKSIZE=10000BYTES(employee_numberINTEGERNOTNULL,department_numberSMALLINTCONSTRAINTdep_checkCHECK(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_keyPRIMARYKEY(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_dataADDeduc_levelCHAR(1),ADDinsure_typeSMALLINT;ALTERTABLEemp_dataDROPeduc_level,DROPinsure_type;例：修改已有字段的属性ALTERTABLEemp_dataADDbirthdateFORMAT'mmmBdd,Byyyy'';例：对没有FALLBACK的表建立FALLBACK保护ALTERTABLEemp_data,FALLBACK;例：同时修改表的多个属性ALTERTABLEemp_data,NOFALLBACKDROPinsure_type,ADDeduc_levelCHAR(1);例：修改约束定义增加约束ALTERTABLEemp_dataADDCONSTRAINTCHECK(sex='F'ORsex='M');修改约束:ALTERTABLEemp_dataMODIFYCONSTRAINTsal_rangeCHECK(salary_amount0ANDs注意：表中已有数据如果不符合新的约束条件，约束的增加或修改不能成功。删除约束：ALTERTABLEemp_dataDROPCONSTRAINTsal_range;alary_amount1000000);1.4次索引前面已经讨论过索引，并且说明，创建表时就应定义主索引，同时也可以定义次索引。事实上，次索引也可以使用单独的CREATEINDEX语句来定义。换言之，主索引只能在CREATETABLE时定义，而次索引既可以在创建表时定义，也可以使用CREATEINDEX来定义。例：为雇员表创建下面两个次索引。为雇员名字建立命名的唯一次索引USICREATEUNIQUEINDEX