SQL(Teradata版).doc

Teradata SQL基础教程第一章 数据定义语言DDL(Data Definition Language)Databases数据库CREATE DATABASE创建数据库MODIFY DATABASE修改数据库定义DROP DATABASE删除数据库Users 用户CREATE USER创建用户MODIFY USER修改用户DROP USER删除用户Tables 表CREATE TABLE创建表ALTER TABLE修改表定义DROP TABLE删除表Indexes 索引CREATE INDEX 创建索引DROP INDEX 删除索引1.1 创建表CREATE TABLE语句创建新表，定义新表的列、索引和其他属性。新表创建后，表结构定义存放在Teradata的数据字典中。CREATE TABLE语句的语法如下：CREATE TABLE ;其中：Create Table Options表选项定义表的物理属性FallbackJournalingFreespaceDatablocksizeColumn Definitions字段定义定义表的各个字段Table-level Constraints表级约束定义约束Primary keyUniqueCHECK条件Foreign keyIndex Definitions索引定义定义表索引1.1.1 创建表的可选项(Create Table Options)Teradata DDL允许在创建表时指定表的物理属性，包括：是否允许重复记录：SET 不允许记录重复（默认）CREATE SET TABLE table1 MULTISET 允许记录重复（建议：Teradata中都用 MultiSet）CREATE MULTISET TABLE table1 数据保护：数据保护要结合FALLBACK和JOURNAL (流水或日志)。FALLBACK是Teradata的一种数据保护机制，数据表的每一条记录都同时存放两份。FALLBACK 使用FALLBACK保护机制NO FALLBACK 不使用FALLBACK保护机制日志有BEFORE和AFTER两种，分别保存了一条记录变化前后的状态。当系统出错时，可以利用日志进行恢复。1.1.2 字段定义Teradata的表可定义多达256个字段，每个字段的定义包括如下五项：字段名，在同一数据库中必须唯一。字段数据类型。例： CREATE TABLE emp_data(employee_number INTEGER NOT NULL,last_name CHAR(20) NOT NULL WITH DEFAULT,street_address VARCHAR (30) TITLE Address,city CHAR (15) DEFAULT Boise,state CHAR (2) WITH DEFAULT,birthdate DATE FORMATmm/dd/yyyy,salary_amount DEC(10,2),sex CHAR (1) UPPERCASE);字段数据类型属性。可定义如下属性：DEFAULT 当字段无数据时用默认值来替代NULLWITH DEFAULT 用字段的系统默认值替换NULLFORMAT 缺省的显示格式TITLE 缺省的列标题NOT NULL 不允许空值CASESPECIFIC 字母大小写敏感UPPERCASE 字母大小写不敏感，内部用大写字母存储数据存储属性。包括下面各项：COMPRESS 压缩值为NULL的字段存储空间为0COMPRESS NULL 同上COMPRESS 压缩值为NULL和指定值的字段存储空间为0字段约束定义。Teradata支持字段级约束，即限制字段的值满足某些条件，如某个字段取值是否唯一、是否为主键或外键等。对字段的约束总结如下：CONSTRAINT name 约束名称-可选PRIMARY KEY非空,无重复值UNIQUE无重复值CHECK 指定合法值的范围REFERENCES与其他字段的相关性(外键)例： CREATE TABLE employee_badge(emp_id INTEGER NOT NULLCONSTRAINT primary_1 PRIMARY KEY,id_badge_number INTEGERCONSTRAINT unique_1 UNIQUE,salary INTEGERCONSTRAINT check_1 CHECK (salary0),job_code INTEGERCONSTRAINT ref_1 REFERENCES job (job_code);最后一项定义了在EMPLOYEE_BADGE表中的JOB_CODE必须和JOB表中的JOB_CODE对应，即前一个表中该字段的值必须在第二个表中有对应的项。这实际上是一种所谓的参照完整性。另外要注意的是，具有主键(Primary Key)约束的字段一定要定义为非空(NOT NULL)。1.1.3 表级约束定义表级约束 意义唯一性定义CONSTRAINT name约束名UNIQUE所指定的多个字段的组合值在表中不能重复PRIMARY KEY这些列将用作主索引或次索引参照定义CONSTRAINT name约束名，配合外键的定义FOREIGN KEY ()所列举的字段为外键，它对应于另一个表(父表)中相同的字段REFERENCES ()定义父表或引用表中的主键所包含的字段Check定义CONSTRAINT name约束名CHECK 对表中指定字段的值进行约束表级约束与字段级约束的主要区别是：在表级约束中可以指定当前表的多个字段或其组合，而字段级约束只能引用当前字段。我们来看一个表级约束的例子。CREATE TABLE employee_badge(emp_id INTEGER NOT NULL,id_badge_num INTEGER NOT NULL,salary INTEGER,job_code INTEGER,CONSTRAINT primary_1 PRIMARY KEY (emp_id),CONSTRAINT unique_1 UNIQUE (id_badge_num),CONSTRAINT check_1 CHECK (salary 0 ANDjob_code BETWEEN 100000 AND 499999),CONSTRAINT ref_1 FOREIGN KEY (job_code)REFERENCES job (job_code);比较字段级约束和表级约束的例子,可以看到:字段级约束必须写在每个字段定义的后面，而表级约束是在字段定义结束后再进行的。在表级约束中，一个约束可以同时定义多个字段。 1.1.4 索引定义PK Primary Key 主键PI Primary Index 主索引UPI Unique Primary Index唯一性主索引NUPI Non Unique Primary Index非唯一性主索引USI Unique Secondary Index 唯一性次索引索引可以在CREATE TABLE时就加以定义，同时还可以定义主键。如果创建表时不定义主索引，Teradata就按照下面的规则缺省来建立主索引，因为没有主索引的话，Teradata就无法进行数据的分配。没有在CREATE TABLE时指定PI:IF 定义了PK，THEN PK = UPIELSE IF 存在定义为UNIQUE的字段，THEN 第一个NIQUE的字段为UPIELSE 表中定义的第一个字段作为NUPICREATE TABLE时指定了PI:IF 定义了PK，THEN PK作为USI AND为每一个定义为UNIQUE的字段建立一个USI下面是一个比较复杂的创建表的例子，注意学习。创建该表后用SHOW TABLE观察一下内部的表达方式。CREATE MULTISET TABLE emp_data,FALLBACK,NO BEFORE JOURNAL,NO AFTER JOURNAL,FREESPACE = 30,DATABLOCKSIZE=10000 BYTES (employee_number INTEGER NOT NULL,department_number SMALLINTCONSTRAINT dep_checkCHECK (department_number BETWEEN 100 AND 999)REFERENCES Department (department_number),job_code INTEGER COMPRESS,last_name CHAR(20) NOT NULL,first_name VARCHAR (20),street_address VARCHAR (30) TITLE Address,city CHAR (15) DEFAULT BoiseCOMPRESS Boise,state CHAR (2) WITH DEFAULT,birthdate DATE FORMAT mm/dd/yyyy,salary_amount DECIMAL (10,2),sex CHAR (1) UPPERCASE,CONSTRAINT emp_keyPRIMARY KEY (employee_number) INDEX (department_number);1.2 删除表可以使用DROP TABLE语句删除表，该语句将删除表中的所有数据和在数据字典中的表结构定义。例：删除前面例子中创建的雇员数据表。DROP TABLE emp_data; 删除了表emp_data中的所有数据，并删除了emp_data在数据字典中的定义。如果希望再使用这个表，必须重新创建。例：DELETE FROM emp_data;或DELETE emp_data;删除了表emp_data中的所有数据。表定义仍然存在，可以增加数据。1.3 修改表当一个表已经创建后，可以使用ALTER TABLE语句来修改其定义。表定义的一些属性是不可修改的(如PI)，如果要改变这些属性，常用方法是建立一个满足新属性的新表，然后使用Insert-Select语句把数据从原来的表转移到新表，然后再修改新表的名称。ALTER TABLE完整的语法可参见Teradata SQL手册，这里通过几个例子来加以说明。例：增加或删除字段 ALTER TABLE emp_dataADD educ_level CHAR(1), ADD insure_type SMALLINT;ALTER TABLE emp_dataDROP educ_level, DROP insure_type;例：修改已有字段的属性 ALTER TABLE emp_dataADD birthdate FORMAT mmmBdd,Byyyy;例：对没有FALLBACK的表建立FALLBACK保护ALTER TABLE emp_data, FALLBACK;例：同时修改表的多个属性 ALTER TABLE emp_data, NO FALLBACKDROP insure_type, ADD educ_level CHAR(1);例：修改约束定义 增加约束 ALTER TABLE emp_dataADD CONSTRAINTCHECK (sex = F OR sex = M);修改约束: ALTER TABLE emp_dataMODIFY CONSTRAINT sal_rangeCHECK ( salary_amount 0 AND s注意：表中已有数据如果不符合新的约束条件，约束的增加或修改不能成功。 删除约束： ALTER TABLE emp_dataDROP CONSTRAINT sal_range; alary_amount 1000000);1.4 次索引前面已经讨论过索引，并且说明，创建表时就应定义主索引，同时也可以定义次索引。事实上，次索引也可以使用单独的CREATE INDEX语句来定义。换言之，主索引只能在CREATE TABLE时定义，而次索引既可以在创建表时定义，也可以使用CREATE INDEX来定义。例：为雇员表创建下面两个次索引。为雇员名字建立命名的唯一次索引USI CREATE UNIQUE INDEX fullname (last_name, first_name) ON emp_data;为工作代码建立非唯一性次索引NUSI，不命名NUSICREATE INDEX (job_code) ON emp_data;从这个例子可以看到，次索引可以命名，如第一个USI的名字为FULLNAME；也可以不命名，如第二个NUSI就没有取名。定义好索引或次索引后，可以利用HELP INDEX 来显示指定表的所有索引定义，如果索引是未命名的，索引名称显示为NULL。当次索引创建后，也可以利用DROP INDEX来删除它们。注意，只有次索引可以被删除，主索引是不能被删除的。当删除命名索引时，可以只指定索引名称，也可以指定索引定义。而删除未命名索引时，必须指定索引定义。例：删除雇员表的所有次索引 删除命名索引 DROP INDEX FullName ON emp_data;删除未命名索引 DROP INDEX (job_code) ON emp_data;第二章 数据操作语言(Data Manipulation Language)2.1 INSERTINSERT语句用于向表中添加一行或多行记录。插入一行记录的命令格式为：INSERT INTO (列名1，列名2,.,列名n)VALUES (列值表达式1，列值表达式2, .,列值表达式n);例：在雇员表中添加一新雇员信息：INSERT INTO employee (last_name, first_name, hire_date, birthdate,salary_amount, employee_number)VALUES( arcia, aria,861027,541110,76500.00,1291);如果添加整条记录，即给每个字段都有相应的值，则表名后的字段名可以省略。如上面的例子可以改写成：INSERT INTO employeeVALUES (1210,NULL,401,41201, mith, ames,890303,460421,41000);Teradata对INSERT作了扩充，增加了一个称为INSERT-SELECT的功能。它以子查询的方式将一个表的数据抽取并插入到另一个表中。举例来说，假设表emp_copy与表emp的结构相同，下面的语句可以把表emp的所有行添加到表emp_copy中，即复制表emp。INSERT INTO emp_copySELECT * FROM emp;INSERT-SELECT也可以将不同结构表的记录添加到目标表中。例我们创建一张雇员生日表：CREATE TABLE birthdays(empno INTEGER NOT NULL, lname CHAR(20) NOT NULL, fname VARCHAR(30), birth DATE)UNIQUE PRIMARY INDEX(empno);然后，我们从雇员表中提取生日信息添加到生日表中。 INSERT INTO birthdaysSELECT employee_number ,last_name, first_name, birthdateFROM employee;2.2 UPDATEUPDATE语句用来更新表内满足条件的数据记录，基本语法为：UPDATE SET =, = , .,=WHERE ;如果UPDATE语句中没有WHERE子句，则更新表中的所有记录。下面的例句是将编号为1010的雇员的部门编号修改为403，工作编号修改为432101，经理的雇员编号修改为1005。UPDATE employeeSET department_number = 403,job_code = 432101,manager_employee_number = 1005WHERE employee_number = 1010;Teradata SQL允许在WHERE子句中使用子查询和联接，因此同样的工作可以通过子查询或联接来实现。考虑如下的情况，我们要给支援部门的人员加薪10%，如果使用子查询的方式，可以写成：UPDATEemployeeSET salary_amount = salary_amount * 1.10WHERE department_number IN(SELECT department_numberFROM departmentWHERE department_name LIKE %Support%);如果使用联接的方式，可以写成：UPDATE employeeSET salary_amount = salary_amount * 1.10WHERE employee.department_number =department.department_numberAND department_name LIKE %Support%;2.3 DELETEDELETE删除表中满足条件的记录，基本语法为：DELETE FROM WHERE ;如果DELETE语句中没有WHERE语句，则删除表中的所有行。例：删除雇员表中编号为301的员工：DELETE FROM employee WHERE department_number = 301;删除雇员表的所有数据，可使用：DELETE FROM employee;与UPDATE语句相似，DELETE语句也支持子查询和联接操作。假设在雇员表中，删除所在部门名称为None的所有雇员信息，使用子查询的语句为：DELETE FROM employeeWHERE department_number IN(SELECT department_numberFROM departmentWHERE department_name = None);使用连接的语句为：DELETE FROM employeeWHERE employee.department_number = department.dempartment_numberAND department.department_name = None;第三章 逻辑与条件表达式3.1 逻辑表达式运算符逻辑表达式由运算符和操作数两部分组成，其结果是一个布尔值(True/False)。这种表达式可以用在WHERE子句的条件表达式中。标准的逻辑表达式运算符种类如表5-1所示：表5-1 逻辑表达式运算符分类运算符种类符号含义比较运算符=等于不等于大于=大于或等于=小于或等于NOT BETWEEN AND 介于a和b之间或不介于a和b之间NOT INNOT IN属于或不属于某个集合IS NOT NULL IS NOT NULL一个数值是空值或不是空值NOT EXISTSNOT EXISTS一个查询至少返回一行或不返回任何行LIKELIKE与某个数值匹配Teradata还提供了下面一组扩展符号，可以与标准的比较运算符相互替换。Teradata扩展EQNEGTLTGELEANSI标准=3.2 逻辑表达式3.2.1 NOT BETWEEN.ANDBETWEEN AND 表示某列数值(数字型或字符型)介于a和b之间，且包括a和b。举例来说，在雇员表中把所有工作代码以43开头的员工的姓名及其经理的员工代码找出来。可以使用下面的SQL语句，其中第二个查询就使用了BETWEEN .AND操作符。SELECT first_name,last_name,manager_employee_numberFROM employeeWHERE job_code = 430000 ANDjob_code = 439999;SELECT first_name,last_name,manager_employee_numberFROM employeeWHERE job_code BETWEEN 430000 AND 439999;需要注意的是，当操作数是字符时，ANSI标准是区分大小写的，而Teradata中缺省不区分大小写，但可以通过CASESPECIFIC/NOT CASESPECIFIC来明确定义是否区分。如：SELECT .WHERE last_name (CASESPECIFIC);SELECT .WHERE last_name (NOT CASESPECIFIC);查询姓以字母R开头的员工，可以使用下面的SQL语句：SELECT last_name FROM employeeWHERE last_name BETWEEN r AND s;由于Teradata的缺省方式不区分大小写，因此，Ryan、Roger等都将作为结果返回。如果显示区分大小写，上面的表达式中必须使用大写字母R和S。3.2.2 集合操作符NOT INNOT IN表示某列数值属于或不属于某个集合。对于显示列举的数据集合，IN和OR可以相互替代使用。例如:在员工表中查找部门代码在401和403之间的员工姓名和相应的部门代码，可以使用下面两个SQL语句，它们的作用是等同的。显然，当要列举的数据值很多时，用OR就不如用IN方便和简洁。SELECT first_name,last_name,department_numberFROM employeeWHERE department_number = 401OR department_number = 403;SELECT first_name,last_name,department_numberFROM employeeWHERE department_number IN (401, 403);反过来，如果查找部门代码不属于401和403的员工姓名和部门代码，则可以使用下面的SQL语句，它们也是等价的。SELECT first_name,last_name,department_numberFROM employeeWHERE NOT (department_number = 401OR department_number = 403);SELECT first_name,last_name,department_numberFROM employeeWHERE department_number NOT IN (401, 403);3.2.3 匹配符LIKELIKE用来进行字符串数据的模式匹配。用作匹配的字符串中可以包含下面的通配符。%：表示除了NULL外的零个或多个字符组成的字符串。_：表示任何单个字符位置。从下面的例子可以清楚地了解这两个通配符的使用方法：表达式 含义LIKE JO% JO在开始位置的任意字符串LIKE %JO% JO在任何位置的字符串LIKE %HN HN在结束位置的任意字符串LIKE %H_H在倒数第二个位置时的任意字符串LIKE _HN% HN分别在第三和第四位置的任意字符串在使用LIKE进行字符串匹配时，要特别注意字符的大小写。ANSI标准中是区分大小写的，如果不要区分大小写，可以使用UPPER函数将其转换成大写字母来进行匹配。Teradata缺省不区分大小写，如果要区分，可以使用其扩展参数CASESPECIFIC。例如：查询员工姓中有Ra字符的员工，可以根据是否区分大小写而分别使用下面的SQL语句：不区分大小写1.在Teradata缺省模式下SELECT first_name,last_nameFROM employeeWHERE last_name LIKE %Ra%;2.在ANSI缺省模式下SELECT first_name,last_nameFROM employeeWHERE UPPER (last_name) LIKE UPPER (%Ra%);区分大小写1.在Teradata缺省模式下SELECT first_name,last_nameFROM employeeWHERE last_name (CASESPECIFIC) LIKE %Ra%;2.在ANSI缺省模式下SELECT first_name,last_nameFROM employeeWHERE last_name LIKE %Ra%;LIKE中限定词的使用利用一些限定词可以扩充LIKE在字符串匹配方面的功能。可以使用的限定词包含：限定词 含义ANY与一个或多个数值匹配SOME (ANY的同义词) 同上ALL 与列举的所有数值匹配例如：查询员工姓中任意位置有字母E和S的员工，可以使用下面的SQL语句。SELECT first_name,last_nameFROM employeeWHERE last_name LIKE ALL (%E%, %S%);如果改变一下上面的问题，要求查找员工姓中任意位置有字母E或者S的员工，则应使用ANY或SOME。SELECT first_name,last_nameFROM employeeWHERE last_name LIKE ANY (%E%, %S%);通配符作为一般字符使用前面谈到，在LIKE结构的字符串中，%和_可以作为通配符使用，但是如果需要匹配这些字符本身(比如查找95%)，即把它们作为一般字符时使用，该如何区分呢？我们可以通过定义ESCAPE字符来达到这个目的，紧跟在ESCAPE字符后的%和_作为一般字符看待。模式 含义_ (下划线)作为一个字符% (百分号)作为一个字符ESCAPE字符本身例： LIKE %A%AAA_ ESCAPE A在这个表达式中，将字母A定义为ESCAPE字符，其中：第一个%为通配符；第一个A和其后的%联合表示字符%；第三个%为通配符；第二个A和其后的A联合表示字符A；第四个A和其后的_联合表示字符_；最后一个_为通配符。例如，下面的SQL语句可用来查找Teradata RDBMS数据字典中对象名称中第二个字符为_的所有对象。SELECT tvmnameFROM dbc.tvmWHERE tvmname LIKE _Z_% ESCAPE Z;3.3 NULL的使用NULL是SQL的一个关键字，在数据库的操作中有很重要的作用，下面是一些关于NULL的说明：NULL显示没有数据的字段NULL表示不存在或未发现的值NULL既不是数字类型也不是字符类型具有NULL值的字段可以被压缩，不占任何空间NULL也可以参与运算，其运算规则为：NULL在算术运算中产生的结果为NULL(空)NULL在比较运算中产生的结果为FalseUNKNOWN DATA, MISSING DATA和NULL是同样的含义当进行升序排列时,NULL在数字列排列在负数前，在字符列排列在空格前。下面给出了一些实例： 、功能例子结果+10+NULLNULL-10-NULLNULL* 10*NULLNULL/10/NULLNULLNULL也可以用在SQL的SELECT中，如找出没有或未知分机号码的员工，使用下面的SQL语句：SELECT employee_numberFROM employee_phoneWHERE extension IS NULL;反过来，找出有分机号码的员工，则可以使用：SELECT employee_numberFROM employee_phoneWHERE extension IS NOT NULL;3.4 条件表达式条件表达式是可以通过逻辑运算符来构造的，系统中的逻辑运算符为：运算符 含义AND 所有条件都必须成立OR 其中任何一个条件成立即可NOT 否定,即对条件求反使用一个或多个逻辑运算符可以表现一些复杂的关系。3.4.1 ANDAND连接两个或两个以上条件表达式。如果所有条件同时为真，则表达式结果为真；否则表达式结果为假。例：查找收入小于35000并且部门号为403的员工姓名，可以使用下面的SQL语句：SELECT first_name,last_name,employee_numberFROM employeeWHERE salary_amount 35000.00AND department_number = 403;3.4.2 OROR连接两个或两个以上条件表达式。如果任何一个条件为真，则表达式结果为真；如果所有条件均为假，则表达式结果为假。 例：查找收入小于35000或部门号为403的员工姓名。SELECT first_name,last_name,employee_numberFROM employeeWHERE salary_amount 35000.00OR department_number = 403;3.4.3 多个AND . . . OR例：查找部门号是403或401，并且工作号为412101或432101的员工姓、部门号和工作号。SELECT last_name,department_number,job_codeFROM employeeWHERE (department_number = 401OR department_number = 403)AND ( job_code = 412101OR job_code = 432101);3.4.5 NOTNOT既可以否定操作符，也可以否定条件表达式。例；查找部门号不是301的员工姓名及其代码。否定操作符:SELECT first_name,last_name,employee_numberFROM employeeWHERE department_number NOT = 301;否定条件：SELECT first_name,last_name,employee_numberFROM employeeWHERE NOT (department_number = 301);第四章 数据转换和计算4.1 数据类型4.1.1 字符型数据在ANSI标准中关于字符型数据定义了两类：CHAR和VARCHAR。其中CHAR表示固定长度的字符串，VARCHAR表示可变长度的字符串。Teradata除了上述两类基本字符型数据外，还扩展了LONG VARCHAR类型，它等同于VARCHAR(64000)，是最长的字符串。表4-1 Teradata字符数据类型字符型数据类型意义例CHAR (size)固定长度的字符串最大长度：64000字节last_name CHAR(20)Ryan_VARCHAR (size)CHAR VARYING (size)CHARACTER VARYING (size)可变长度字符串最大长度：64000字节first_nameVARCHAR (30)LorettaLONG VARCHAR等同VARCHAR(64000)4.1.2 二进制数据二进制数据类型是Teradata的扩展，ANSI标准没有此类型。Teradata支持两类二进制数据BYTE和VARBYTE。其中BYTE表示固定长度的二进制串，VARCHAR表示可变长度的二进制串。表4-2 Teradata二进制数据类型类型意义BYTE (size)固定长度的二进制字串 默认值：(1) 最大值：64000字节VARBYTE (size)可变长的二进制字串 默认值：(1) 最大值：64000字节4.1.3 数字型数据在ANSI标准中关于数字型数据定义了四类：SMALLINT、INTEGER、FLOAT、DECIMAL。Teradata除以上四类外，还有两类扩展的数字型数据，即BYTEINT和DATE。表4-3 ANSI标准的数字型数据：数据类型描述范例SM