《数据库技术》课件-第4章关系数据库标准语言

第4章关系数据库标准语言能够使用DM_SQL实现数据查询和数据更新能够使用达梦（DM）数据库的索引和视图能够使用达梦（DM）数据库管理系统的常用工具了解达梦（DM）SQL的语言的特点了解DM_SQL语言常用的数据类型、运算符和表达式学习目标4.1数据定义4.1.1数据类型1数据类型是可表示值的集。值的逻辑表示是<字值>。值的物理表示依赖于实现。DM系统具有SQL-92的绝大部分数据类型，以及部分SQL-99和SQLServer2000的数据类型。4.1数据定义4.1.1数据类型11.字符数据类型（1）CHAR类型语法：CHAR[(长度)]功能：CHAR数据类型指定定长字符串。在基表中，定义CHAR类型的列时，可以指定一个不超过32767的正整数作为字节长度。例如：CHAR(50)4.1数据定义4.1.1数据类型1（2）CHARACTER类型语法：CHARACTER[(长度)]功能：与CHAR类型相同。例如：CHARACTER[(100)]4.1数据定义4.1.1数据类型1（3）VARCHAR类型/VARCHAR2类型语法：VARCHAR[(长度[CHAR])]功能：VARCHAR数据类型指定变长字符串，用法类似CHAR数据类型，可以指定一个不超过32767的正整数作为字节或字符长度。例如：VARCHAR(100)指定100字节长度；VARCHAR(100CHAR)指定100字符长度。如果未指定长度，缺省为8188字节。4.1数据定义4.1.1数据类型1（4）ROWID类型语法：ROWID功能：ROWID类型数据由18位字符组成，用来表示ROWID数据。18位字符由“4位站点号+6位分区号+8位物理行号”组成。ROWID类型数据可通过SF_BUILD_ROWID()构造而来。表中的ROWID类型字段，可以用于排序或创建索引。但是ROWID类型不支持作为分区字段和自定义类型的属性数据类型。ROWID字段与字符类型一样，支持MAX，MIN等集函数，不支持SUM，AVG等集函数。4.1数据定义4.1.1数据类型1例如：创建一个含有ROWID类型的数据库表。先构造ROWID数据。假定站点号为1，分区号为2，物理行号为10。使用SF_BUILD_ROWID函数构造出一个ROWID类型数据。SELECTSF_BUILD_ROWID(1,2,10);查询结果如下：AAABAAAAACAAAAAAAK其中，AAAB为站点号、AAAAAC为分区号、AAAAAAAK为ROWID值。创建含有ROWID类型的表，并插入数据。CREATETABLETEST1(C1INT,C2ROWID);INSERTINTOTEST1VALUES(6,'AAABAAAAACAAAAAAAK');SELECTC1,C2,ROWIDFROMTEST1;C2列为插入的值，ROWID为当前数据行的伪列ROWID。4.1数据定义4.1.1数据类型12.数值数据类型（1）NUMERIC类型语法：NUMERIC[(精度[,标度])]功能：NUMERIC数据类型用于存储零、正负定点数。其中：精度是一个无符号整数，定义了总的数字数，精度范围是1至38。标度定义了小数点右边的数字位数。一个数的标度不应大于其精度，如果实际标度大于指定标度，那么超出标度的位数将会四舍五入省去。例如：NUMERIC(4,1)定义了小数点前面3位和小数点后面1位，共4位的数字，范围在-999.9到999.9。所有NUMERIC数据类型，如果其值超过精度，DM会返回一个出错信息，如果超过标度，则多余的位会被截断。如果不指定精度和标度，缺省精度为38，标度无限定。4.1数据定义4.1.1数据类型1（2）DECIMAL类型语法：DECIMAL[(精度[,标度])]功能：与NUMERIC相似。（3）DEC类型语法：DEC[(精度[,标度])]功能：与DECIMAL类型相同。（4）NUMBER类型语法：NUMBER[(精度[,标度])]功能：与NUMERIC类型相同。4.1数据定义4.1.1数据类型1（5）INTEGER类型语法：INTEGER功能：用于存储有符号整数，精度为10，标度为0。取值范围为：-2147483648(-2^31)～+2147483647(2^31-1)。（6）INT类型语法：INT功能：与INTEGER类型相同。（7）BIGINT类型语法：BIGINT功能：用于存储有符号整数，精度为19，标度为0。取值范围为：-9223372036854775808(-2^63)～9223372036854775807(2^63-1)。4.1数据定义4.1.1数据类型1（8）TINYINT类型语法：TINYINT功能：用于存储有符号整数，精度为3，标度为0。取值范围为：-128～+127。（9）BYTE类型语法：BYTE功能：与TINYINT相似，精度为3，标度为0。（10）SMALLINT类型语法：SMALLINT功能：用于存储有符号整数，精度为5，标度为0。取值范围为：-32768(-2^15)～+32767(2^15-1)。4.1数据定义4.1.1数据类型1（11）BINARY类型语法：BINARY[(长度)]功能：BINARY数据类型用来存储定长二进制数据。在基表中，定义BINARY类型的列时，其最大存储长度由数据库页面大小决定，可以指定一个不超过其最大存储长度的正整数作为列长度，缺省长度为1个字节。最大存储长度见表4-1。BINARY类型在表达式计算中的长度上限为32767。BINARY常量以0x开始，后面跟着数据的十六进制表示，例如：0x2A3B4058。（12）VARBINARY类型语法：VARBINARY[(长度)]功能：VARBINARY数据类型用来存储变长二进制数据，用法类似BINARY数据类型，可以指定一个不超过32767的正整数作为数据长度。缺省长度为8188个字节。VARBINARY数据类型的实际最大存储长度由数据库页面大小决定，具体最大长度算法与VARCHAR类型的相同，其在表达式计算中的长度上限也与VARCHAR类型相同，为32767。4.1数据定义4.1.1数据类型1（13）RAW类型语法：RAW[(长度)]功能：与VARBINARY类型相同。（14）FLOAT类型语法：FLOAT[(精度)]功能：FLOAT是带二进制精度的浮点数，精度范围（1~126）。当精度小于等于24时，DM将其转换为标准C语言中的REAL类型；当精度大于24时，转换为标准C语言中的DOUBLE类型。FLOAT取值范围-1.7*10^308～1.7*10^308。4.1数据定义4.1.1数据类型1（15）DOUBLE类型语法：DOUBLE[(精度)]功能：DOUBLE是带二进制精度的浮点数。DOUBLE类型的设置是为了移植的兼容性。该类型直接使用标准C语言中DOUBLE。精度与取值范围与FLOAT一样。（16）REAL类型语法：REAL功能：REAL是带二进制精度的浮点数，但它不能由用户指定使用的精度，系统指定其二进制精度为24，十进制精度为7。取值范围-3.4*10^38～3.4*10^38。4.1数据定义4.1.1数据类型1（17）DOUBLEPRECISION类型语法：DOUBLEPRECISION[(精度)]功能：该类型指明双精度浮点数。DOUBLEPRECISION类型的设置是为了移植的兼容性。该类型直接使用标准C语言中DOUBLE。精度与取值范围与FLOAT一样。4.1数据定义4.1.1数据类型13.位串数据类型BIT类型语法：BIT功能：BIT类型用于存储整数数据1、0或NULL，只有0才转换为假，其他非空、非0值都会自动转换为真，可以用来支持ODBC和JDBC的布尔数据类型。DM的BIT类型与SQLSERVER2000的BIT数据类型相似。功能与ODBC和JDBC的BOOL类型相同。4.1数据定义4.1.1数据类型14.日期时间数据类型日期时间数据类型分为一般日期时间数据类型、时间间隔数据类型和时区数据类型三类，用于存储日期、时间和它们之间的间隔信息。一般日期时间数据类型包括：DATE类型、TIME类型、TIMESTAMP类型。（1）DATE类型语法：DATE功能：DATE类型包括年、月、日信息，定义了'-4712-01-01'和'9999-12-31'之间任何一个有效的格里高利日期。DATE值的书写方式有两种：一是DATE'年月日'；二是'年月日'。4.1数据定义4.1.1数据类型1其中，年月日之间可以使用分隔符或者没有分隔符。分隔符是指除大小写字母、数字以及双引号之外的所有单字节字符且是可打印的。例如:空格、回车键、tab键、-/,.:*等标点符号。年月日中第一个非0数值前的0亦可省略，例如：'0001-01-01'等价于'1-1-1'。例如：CREATETABLETEST2(C1DATE,C2DATE,C3DATE);INSERTINTOTEST2VALUES(DATE'2023-10-01','2023/10/01','2023.10.01');4.1数据定义4.1.1数据类型1（2）TIME类型语法：TIME[(小数秒精度)]功能：TIME类型包括时、分、秒信息，定义了一个在'00:00:00.000000'和'23:59:59.999999'之间的有效时间。TIME类型的小数秒精度规定了秒字段中小数点后面的位数，取值范围为0～6，如果未定义，缺省精度为0。TIME值的书写方式有两种：一是TIME'时:分:秒'；二是'时:分:秒'。例如：CREATETABLETEST3(C1TIME(2),C2TIME,C3TIME);INSERTINTOTEST3VALUES(TIME'08:30:20.20','11:15:22','19:10:25.49');4.1数据定义4.1.1数据类型1（3）TIMESTAMP类型语法：TIMESTAMP[(小数秒精度)]功能：TIMESTAMP类型包括年、月、日、时、分、秒信息，定义了一个在'-4712-01-0100:00:00.000000000'和'9999-12-3123:59:59.999999999'之间的有效格里高利日期时间。TIMESTAMP类型的小数秒精度规定了秒字段中小数点后面的位数，取值范围为0～9，如果未定义，缺省精度为6。TIMESTAMP值的书写方式有两种：一是TIMESTAMP'<DATE值><TIME值>'；二是'<DATE值><TIME值>'。语法中，TIMESTAMP也可以写为DATETIME。4.1数据定义4.1.1数据类型1例如：CREATETABLETEST4(C1TIMESTAMP,C2DATETIME,C3TIMESTAMP,C4DATETIME,C5TIMESTAMP);INSERTINTOTEST4VALUES(TIMESTAMP'2021-01-0108:30:25',TIMESTAMP'2022-02-0208:30:25','2002/12/1208:30:25','2023.11.1108:30:25',DATETIME'2024-12-1208:30:25');DM支持两类十三种时间间隔类型（详见表4-2）。4.1数据定义4.1.1数据类型15.时区数据类型DM支持两种时区类型：标准时区类型和本地时区类型。标准时区类型：带时区的TIME类型和带时区的TIMESTAMP类型。（1）TIMEWITHTIMEZONE类型语法：TIME［(小数秒精度)］WITHTIMEZONE功能：描述一个带时区的TIME值，其定义是在TIME类型的后面加上时区信息。TIMEWITHTIMEZONE值的书写方式有两种：一是TIME'时分秒<时区>'；二是'时分秒<时区>'。例如：CREATETABLETEST5(C1TIME(2)WITHTIMEZONE,C2TIME(2)WITHTIMEZONE);INSERTINTOTEST5VALUES(TIME'09:10:21+8:00','09:10:21-11:00');4.1数据定义4.1.1数据类型1（2）TIMESTAMPWITHTIMEZONE类型语法：TIMESTAMP［(小数秒精度)］WITHTIMEZONE功能：描述一个带时区的TIMESTAMP值，其定义是在TIMESTAMP类型的后面加上时区信息。TIMESTAMPWITHTIMEZONE值的书写方式有两种：一是TIMESTAMP'<DATE值><TIME值><时区>'；二是'<DATE值><TIME值><时区>'。语法中，TIMESTAMP也可以写为DATETIME。例如：CREATETABLETEST6(C1TIMESTAMP(2)WITHTIMEZONE,C2TIMESTAMP(2)WITHTIMEZONE,C3TIMESTAMP(2)WITHTIMEZONE,C4TIMESTAMP(2)WITHTIMEZONE);INSERTINTOTEST6VALUES(TIMESTAMP'2023-12-1209:10:21+8:00','2023-12-129:10:21+9:00','2023/12/1209:10:21-10:00','2023.12.1209:10:21-5:00');4.1数据定义4.1.1数据类型1（3）TIMESTAMPWITHLOCALTIMEZONE类型语法：TIMESTAMP［(小数秒精度)］WITHLOCALTIMEZONE功能：描述一个本地时区的TIMESTAMP值，能够将标准时区类型TIMESTAMPWITHTIMEZONE类型转化为本地时区类型，如果插入的值没有指定时区，则默认为本地时区。TIMESTAMPWITHLOCALTIMEZONE值的书写方式有两种：一是TIMESTAMP'<DATE值><TIME值><时区>'；二是'<DATE值><TIME值><时区>'。例如：CREATETABLETEST7(C1TIMESTAMP(3)WITHLOCALTIMEZONE,C2TIMESTAMP(3)WITHLOCALTIMEZONE);INSERTINTOTEST7VALUES(TIMESTAMP'2023-12-1209:10:21+8:00',TIMESTAMP'2023-12-1209:10:21');SELECT*FROMTEST7;4.1数据定义4.1.1数据类型16.多媒体数据类型多媒体数据类型的字值有两种格式：一是字符串，例如：'ABCD'二是BINARY，例如：0x61626364TEXT、LONG、LONGVARCHAR、CLOB只支持字符串。BFILE不适用上面两种格式。BFILE指明的文件只能只读访问。BINARY格式的多媒体数据类型以及BFILE数据类型不支持比较。不支持为多媒体数据类型的字段指定精度。（1）TEXT类型语法：TEXT功能：TEXT为变长字符串类型。其字符串的长度最大为100G-1字节。DM利用它存储长的文本串。4.1数据定义4.1.1数据类型1（2）LONG、LONGVARCHAR(又名TEXT)类型语法：LONG/LONGVARCHAR功能：与TEXT类型相同。（3）IMAGE类型语法：IMAGE功能：IMAGE用于指明多媒体信息中的图像类型。图像由不定长的象素点阵组成，长度最大为100G-1字节。该类型除了存储图像数据之外，还可用于存储任何其它二进制数据。（4）LONGVARBINARY(又名IMAGE)类型语法：LONGVARBINARY功能：与IMAGE类型相同。4.1数据定义4.1.1数据类型1（5）BLOB类型语法：BLOB功能：BLOB类型用于指明变长的二进制大对象，长度最大为100G-1字节。（6）CLOB类型语法：CLOB功能：与TEXT类型相同。（7）BFILE类型语法：BFILE功能：BFILE用于指明存储在操作系统中的二进制文件，文件存储在操作系统而非数据库中，仅能进行只读访问。4.1数据定义4.1.2基本表的定义、删除和修改2用户数据库建立后，就可以定义基表来保存用户数据的结构。DM数据库的表可以分为两类，分别为数据库内部表和外部表，数据库内部表由数据库管理系统自行组织管理，而外部表在数据库的外部组织，是操作系统文件。其中内部表包括：数据库基表、HUGE表和水平分区表。下面对数据库基表的创建与使用进行详细描述。4.1数据定义4.1.2基本表的定义、删除和修改21．表定义语句（1）定义数据库基表用户数据库建立后，就可以定义基表来保存用户数据的结构。需指定如下信息：表名、表所属的模式名；列定义；完整性约束。语法：CREATE[[GLOBAL]TEMPORARY]TABLE[IFNOTEXISTS]<表名定义><表结构定义>;4.1数据定义4.1.2基本表的定义、删除和修改2完整性约束含义NULL指明指定列可以包含空值，为缺省选项NOTNULL非空约束，指明指定列不可以包含空值UNIQUE唯一性约束，指明指定列作为唯一关键字PRIMARYKEY主键约束，指明指定列作为基表的主关键字，INI参数PK_WITH_CLUSTER控制该约束默认为CLUSTER或NOTCLUSTERCLUSTERPRIMARYKEY主键约束，指明指定列作为基表的聚集索引（也叫聚簇索引）主关键字NOTCLUSTERPRIMARYKEY主键约束，指明指定列作为基表的非聚集索引主关键字CLUSTERKEY指定列为聚集索引键，但是是非唯一的CLUSTERUNIQUEKEY指定列为聚集索引键，并且是唯一的USINGINDEXTABLESPACE<表空间名>指定索引存储的表空间REFERENCES指明指定列的引用约束。引用约束要求引用对应列类型必须基本一致。所谓基本，是因为CHAR与VARCHAR，BINARY与VARBINARY，TINYINT、SMALLINT与INT在此被认为是一致的。如果有WITHINDEX选项，则为引用约束建立索引，否则不建立索引，通过其他内部机制保证约束正确性CHECK检查约束，指明指定列必须满足的条件NOTVISIBLE列不可见，当指定某列不可见时，使用SELECT*进行查询时将不添加该列作为选择列。使用INSERT无显式指定列列表进行插入时，值列表不能包含隐藏列的值列级完整性约束4.1数据定义4.1.2基本表的定义、删除和修改2表级完整性约束完整性约束含义UNIQUE唯一性约束，指明指定列或列的组合作为唯一关键字PRIMARYKEY主键约束，指明指定列或列的组合作为基表的主关键字。指明CLUSTER，表明是主关键字上聚集索引；指明NOTCLUSTER，表明是主关键字上非聚集索引；也可通过INI参数PK_WITH_CLUSTER控制该约束默认为CLUSTER或NOTCLUSTERUSINGINDEXTABLESPACE<表空间名>指定索引存储的表空间FOREIGNKEY指明表级的引用约束，如果使用WITHINDEX选项，则为引用约束建立索引，否则不建立索引，通过其他内部机制保证约束正确性CHECK检查约束，指明基表中的每一行必须满足的条件4.1数据定义4.1.2基本表的定义、删除和修改2例如：创建客户信息表Customer语句如下：CREATETABLECustomer(CustomerIDINTIDENTITY(1,1),UserNameVARCHAR(50)NOTNULL,CustomerPWDVARCHAR(50)NOTNULL,CustomerTypeVARCHAR(10),CustomerNameVARCHAR(20),SexCHAR(4),PhoneVARCHAR(50),EmailVARCHAR(50),CityVARCHAR(50),AddressVARCHAR(200),PRIMARYKEY(CustomerID),);4.1数据定义4.1.2基本表的定义、删除和修改22．表修改语句语法：ALTERTABLE[<模式名>.]<表名><修改表定义子句>对表的修改操作包括：修改一列的数据类型、精度、刻度，设置列上的DEFAULT、NOTNULL、NULL、VISIBLE、可见用户等；增加一列及该列上的列级约束；重建表上的聚集索引数据，消除附加列；删除一列；增加、删除表上的约束；启用、禁用表上的约束；表名/列名的重命名；修改触发器状态；修改表的最大存储空间限制；修改外部表的文件路径；修改超长记录（变长字符串）存储方式；增加、删除表上记录物理逻辑日志的功能；将表移动到目标表空间。4.1数据定义4.1.2基本表的定义、删除和修改22．表修改语句例如：先创建一个商品信息表Product，再给这个表增加一个是否上架IsOn字段。语句如下：CREATETABLEProduct(ProductIDINTIDENTITY(1,1),ProductNameVARCHAR(50),CategoryIDINT,ProductCityVARCHAR(50),ProductPriceFLOAT,ProductCountINT,ProductPicVARCHAR(100),ProductDescriptionVARCHAR(200));ALTERTABLEProductADDIsOnBIT4.1数据定义4.1.2基本表的定义、删除和修改23．表删除语句DM系统允许用户随时从数据库中删除基表。语法：DROPTABLE[IFEXISTS][<模式名>.]<表名>[RESTRICT|CASCADE];例如：商品信息表ProductDROPTABLEProduct执行该语句后，Product表已经被删除。4.1数据定义4.1.2基本表的定义、删除和修改24．基表数据删除语句DM可以从表中删除所有记录。语法：TRUNCATETABLE[<模式名>.]<表名>[PARTITION<分区名>|(<分区名>)][DROPSTORAGE|REUSESTORAGE][CASCADE];使用说明：TRUNCATE命令一次性删除指定表的所有记录，比用DELETE命令删除记录快。但TRUNCATE不会触发表上的DELETE触发器；如果TRUNCATE删除的表上有被引用关系，且未指定CASCADE，则此语句失败；如果TRUNCATE删除的表上有被引用关系，且指定CASCADE，若所有引用表的引用约束选项指定为“ONDELETECASCADE”，则系统将级联删除所有引用表中的数据，否则将报错；TRUNCATE不同于DROP命令，因为它保留了表结构及其上的约束和索引信息；TRUNCATE命令只能用来删除表的所有的记录，而DELETE命令可以只删除表的部分记录。例如：删除Customer表中的所有记录。TRUNCATETABLECustomer4.2数据查询4.2.1基本查询11．单表查询SELECT语句仅从一个表/视图中检索数据，称单表查询。即<FROM子句>中<普通表>使用的是[<模式名>.]<基表名|视图名>。（1）简单查询例如：查询商品信息表Product中的所有商品，包括商品名称、商品产地、商品价格、库存数量等字段。SELECTDISTINCTProductName,ProductCity,ProductPrice,ProductCount,ProductDescriptionFROMProduct;其中，DISTINCT保证重复的行将从结果中去除。若允许有重复的元组，改用ALL来替换DISTINCT，或直接去掉DISTINCT即可。4.2数据查询4.2.1基本查询1当用户需要查出所有列的数据，且各列的显示顺序与基表中列的顺序也完全相同时，为了方便用户提高工作效率，SQL语言允许用户将SELECT后的<值表达式>省略为*。SELECT*FROMProduct;等价于：SELECTProductID,ProductName,CategoryID,ProductCity,ProductPrice,ProductCount,ProductPic,ProductDescription,IsOnFROMProduct;4.2数据查询4.2.1基本查询1（2）带条件查询带条件查询是指在指定表中查询出满足条件的元组。该功能是在查询语句中使用WHERE子句实现的。WHERE子句常用的查询条件由谓词和逻辑运算符组成。谓词指明了一个条件，该条件求解后，结果为一个布尔值：真、假或未知。逻辑运算符有：AND，OR，NOT。谓词包括比较谓词(＝、＞、＜、＞＝、＜＝、＜＞)，BETWEEN谓词、IN谓词、LIKE谓词、NULL谓词、EXISTS谓词。4.2数据查询4.2.1基本查询1使用比较谓词的查询当使用比较谓词时，数值数据根据它们代数值的大小进行比较，字符串的比较则按序对同一顺序位置的字符逐一进行比较。若两字符串长度不同，短的一方应在其后增加空格，使两串长度相同后再作比较。例如：查询价格在3000～5000元之间的所有商品。SELECT*FROMProductWHEREProductPrice>=3000ANDProductPrice<=5000;4.2数据查询4.2.1基本查询1使用BETWEEN谓词的查询例如：查询价格在3000～5000元之间的所有商品。SELECT*FROMProductWHEREProductPriceBETWEEN3000AND5000;此例查询与上例完全等价。在BETWEEN谓词前面可以使用NOT，以表示否定。SELECT*FROMProductWHEREProductPriceNOTBETWEEN3000AND5000;4.2数据查询4.2.1基本查询1使用IN谓词的查询谓词IN可用来查询某字段值属于指定集合的元组。例如：查询所在城市为北京、上海、成都的客户信息。SELECT*FROMCustomerWHERECityIN('北京','上海','成都');4.2数据查询4.2.1基本查询1使用LIKE谓词的查询LIKE谓词一般用来进行字符串的匹配。我们先用实例来说明LIKE谓词的使用方法。例如：查询地址中有“成都市”的客户。SELECTCustomerName,Sex,Phone,Email,AddressFROMCustomerWHEREAddressLIKE'%成都市%';4.2数据查询4.2.1基本查询1使用NULL谓词的查询空值是未知的值。当列的类型为数值类型时，NULL并不表示0；当列的类型为字符串类型时，NULL也并不表示空串。因为0和空串也是确定值。NULL只能是一种标识，表示它在当前行中的相应列值还未确定或未知，对它的查询也就不能使用比较谓词而须使用NULL谓词。例如：查询邮箱为NULL的所有客户。SELECTCustomerName,Sex,PhoneFROMCustomerWHEREEmailISNULL;在NULL谓词前，可加NOT表示否定。4.2数据查询4.2.1基本查询1组合逻辑可以用逻辑算符(AND，OR，NOT)与各种谓词相组合生成较复杂的条件查询。例如：查询库存数量小于10或者未上架的全部商品。SELECTProductName,ProductCount,IsOnFROMProductWHEREProductCount<10ORIsOn=0;4.2数据查询4.2.2分组查询2GROUPBY子句主要用法是分类汇总。GROUPBY子句一般与聚集函数一起使用，常见聚集函数包括COUNT、SUM、AVG、MAX和MIN等。4.2数据查询24.2.2分组查询函数类别函数名称及用法计数函数COUNT(*)相异集函数AVG|MAX|MIN|SUM|COUNT(DISTINCT<列名>)完全集函数AVG|MAX|MIN|COUNT|SUM([ALL]<值表达式>)方差集函数VAR_POP、VAR_SAMP、VARIANCE、STDDEV_POP、STDDEV_SAMP、STDDEV；协方差函数COVAR_POP、COVAR_SAMP、CORR首行函数FIRST_VALUE求区间范围内最大值集函数AREA_MAXFIRST/LAST集函数AVG|MAX|MIN|COUNT|SUM([ALL]<值表达式>)KEEP(DENSE_RANKFIRST|LASTORDERBY子句)；ORDERBY子句语法参考[4.7ORDERBY子句](#4.7ORDERBY子句)字符串集函数LISTAGG/LISTAGG2、WM_CONCAT、COLLECT求中位数函数MEDIAN线性回归相关REGR集函数REGR_COUNT、REGR_AVGX、REGR_AVGY、REGR_SLOPE、REGR_INTERCEPT、REGR_R2、REGR_SXX、REGR_SYY、REGR_SXY聚集函数4.2数据查询24.2.2分组查询1．GROUPBY子句的使用GROUPBY子句是SELECT语句的可选项部分。它定义了分组表。语法：<GROUPBY子句>::=GROUPBY<group_by项>例如：统计商品信息表中的商品种类。SELECTCategoryID,COUNT(*)FROMProductGROUPBYCategoryID;4.2数据查询24.2.2分组查询使用GROUPBY要注意以下问题：分组列不能为集函数表达式或者在SELECT子句中定义的别名；当分组列值包含空值时，则空值作为一个独立组；当分组列包含多个列名时，则按照GROUPBY子句中列出现的顺序进行分组；GROUPBY子句中至多可包含512个分组项；ROLLUP\CUBE\GROUPINGSETS组合不能超过8个。4.2数据查询24.2.2分组查询2．HAVING子句的使用HAVING子句是SELECT语句的可选项部分，它也定义了一个分组表。语法：<HAVING子句>::=HAVING<搜索条件>HAVING子句定义了一个分组表，其中只含有搜索条件为TRUE的那些组，且通常跟随一个GROUPBY子句。HAVING子句与组的关系正如WHERE子句与表中行的关系。WHERE子句用于选择表中满足条件的行，而HAVING子句用于选择满足条件的组。例如：统计出同一子类别的商品数量大于1的子类别名称、数量，并按数量从小到大的顺序排列。语句如下：SELECTT2.CategoryName,COUNT(*)FROMProductT1,CategoryT2WHERET1.CategoryID=T2.CategoryIDGROUPBYT2.CategoryNameHAVINGCOUNT(*)>1;4.2数据查询24.2.2分组查询3．ORDERBY子句ORDERBY子句可以选择性地出现在<查询表达式>之后，它规定了当行由查询返回时应具有的顺序。语法：<ORDERBY子句>::=ORDER[SIBLINGS]BY<order_by_list>由于ORDERBY只能在最终结果上操作，不能将其放在查询中；如果ORDERBY后面使用集函数，则必须使用GROUPBY分组，且GROUPBY分组中必须包含查询列中所有列；ORDERBY子句中至多可包含255个排序列。例如：将商品信息表Product中的全部商品按价格从高到低的顺序排列。SELECT*FROMProductORDERBYProductPriceDESC;等价于：SELECT*FROMProductORDERBY5DESC;该语句中的5即是Product表的第5个字段ProductPrice。4.2数据查询4.2.3连接查询3如果一个查询包含多个表(>=2)，则称这种方式的查询为连接查询。即<FROM子句>中使用的是<连接表>。DM的连接查询方式包括：交叉连接（crossjoin）、自然连接（naturaljoin）、内连接（inner）、外连接（outer）。4.2数据查询31．交叉连接（1）无过滤条件对连接的两张表记录做笛卡尔集，产生最终结果输出。例如：将客户信息表Customer和客户分类表CustomerType通过交叉连接查询。SELECTT1.CustomerName,T2.TypeNameFROMCustomerT1CROSSJOINCustomerTypeT2;客户信息表Customer表中有8条记录，客户分类表CustomerType中有4条记录，两表做笛卡尔集后有4*8=32条记录。（2）有过滤条件对连接的两张表记录做笛卡尔集，根据WHERE条件进行过滤，产生最终结果输出。例如：查询A类客户的姓名、性别、联系电话。SELECTCustomerName,Sex,Phone,TypeNameFROMCustomerT1,CustomerTypeT2WHERET1.CustomerTypeID=T2.CustomerTypeIDANDTypeName='A类客户';4.2.3连接查询4.2数据查询32．自然连接把两张连接表中的同名列作为连接条件，进行等值连接，我们称这样的连接为自然连接。自然连接具有以下特点：连接表中存在同名列；如果有多个同名列，则会产生多个等值连接条件；如果连接表中的同名列类型不匹配，则报错处理。例如：查询所有客户的姓名、性别、联系电话、客户类型。SELECTCustomerName,Sex,Phone,TypeNameFROMCustomerT1NATURALJOINCustomerTypeT2;4.2.3连接查询4.2数据查询33．JOIN…USING这是自然连接的另一种写法，JOIN关键字指定连接的两张表，USING指明连接列。要求USING中的列存在于两张连接表中。例如：查询客户的姓名、联系电话及订单金额。SELECTCustomerName,Phone,TotalPriceFROMCustomerJOINProductOrderUSING(CustomerID);4.2.3连接查询4.2数据查询34．JOIN…ON这是一种连接查询的常用写法，说明是一个连接查询。JOIN关键字指定连接的两张表，ON子句指定连接条件表达式，其中不允许出现ROWNUM。具体采用何种连接方式，由数据库内部分析确定。例如：查询客户的姓名、订单金额。SELECTT1.CustomerName,T2.TotalPriceFROMCustomerT1JOINProductOrderT2ONT1.CustomerID=T2.CustomerID;4.2.3连接查询4.2数据查询35．自连接数据表与自身进行连接，我们称这种连接为自连接。自连接查询至少要对一张表起别名，否则，服务器无法识别要处理的是哪张表。例如：对商品分类表Category进行自连接查询。SELECTT1.CategoryID,T2.CategoryNameFROMCategoryT1,CategoryT2WHERET1.CategoryID=T2.CategoryID;4.2.3连接查询4.2数据查询36．内连接(INNERJOIN)根据连接条件，结果集仅包含满足全部连接条件的记录，我们称这样的连接为内连接。例如：从客户信息表Customer和订单表ProductOrder中查询客户姓名和快递单号。SELECTT1.CustomerName,T2.ExpressNOFROMCustomerT1INNERJOINProductOrderT2ONT1.CustomerID=T2.CustomerID;4.2.3连接查询4.2数据查询37．外连接(OUTERJOIN)外连接对结果集进行了扩展，会返回一张表的所有记录，对于另一张表无法匹配的字段用NULL填充返回。DM数据库支持三种方式的外连接：左外连接、右外连接、全外连接。外连接中常用到的术语：左表、右表。根据表所在外连接中的位置来确定，位于左侧的表，称为左表；位于右侧的表，称为右表。例如：SELECT*FROMT1LEFTJOINT2ONT1.C1=T2.D1，T1表为左表，T2表为右表。返回所有记录的表根据外连接的方式而定。4.2.3连接查询4.2数据查询3左外连接：返回左表所有记录；右外连接：返回右表所有记录；全外连接：返回两张表所有记录。处理过程为分别对两张表进行左外连接和右外连接，然后合并结果集。在左外连接和右外连接中，如果需要对未能匹配的缺失数据进行填充，可以使用分区外连接(PARTITIONOUTERJOIN)，分区外连接通常用于处理稀疏数据以得到分析报表。下面举例说明。例如：使用左外连接，从客户信息表Customer和订单表ProductOrder中查询所有客户的姓名和快递单号。SELECTT1.CustomerName,T2.ExpressNOFROMCustomerT1LEFTOUTERJOINProductOrderT2ONT1.CustomerID=T2.CustomerID;4.2.3连接查询4.2数据查询3例如：使用右外连接，从客户信息表Customer和订单表ProductOrder中查询所有客户的姓名和快递单号。SELECTT1.CustomerName,T2.ExpressNOFROMCustomerT1RIGHTOUTERJOINProductOrderT2ONT1.CustomerID=T2.CustomerID;4.2.3连接查询4.2数据查询3例如：使用全外连接，从客户信息表Customer和订单表ProductOrder中查询所有客户的姓名和快递单号。SELECTT1.CustomerName,T2.ExpressNOFROMCustomerT1FULLOUTERJOINProductOrderT2ONT1.CustomerID=T2.CustomerID;4.2.3连接查询4.2数据查询3外连接还有一种写法，在连接条件或WHERE条件中，在列后面增加(+)指示左外连接或者右外连接。如果表A和表B连接，连接条件或者where条件中，A的列带有(+)后缀，则认为是BLEFTJOINA。如果用户的(+)指示引起了外连接环，则报错。4.2.3连接查询4.2数据查询4.2.4其他查询41．子查询在DM_SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块，如果在一个查询块中嵌套一个或多个查询块，我们称这种查询为子查询。子查询会返回一个值（标量子查询）或一个表（表子查询）。它通常采用（SELECT…）的形式嵌套在表达式中。子查询语法如下：<子查询>::=(<查询表达式>)即子查询是嵌入括弧的<查询表达式>，而这个<查询表达式>通常是一个SELECT语句。它有下列限制：子查询允许TEXT类型与CHAR类型值比较。比较时，取出TEXT类型字段的最多32767字节与CHAR类型字段进行比较；在子查询中允许嵌套子查询。按子查询返回结果的形式，DM子查询可分为两大类：标量子查询：只返回一行一列；表子查询：可返回多行多列。4.2数据查询4.2.4其他查询42．标量子查询标量子查询是一个普通SELECT查询，它只应该返回一行一列记录。如果返回结果多于一行则会提示单行子查询返回多行，返回结果多于一列则会提示SELECT语句列数超长。下面是一个标量子查询的例子：SELECT'客户地址：',(SELECTAddressFROMCustomerWHERECustomerID=1);4.2数据查询4.2.4其他查询43．表子查询和标量子查询不同的是，表子查询的查询结果可以是多行多列。一般情况下，表子查询类似标量子查询，单列构成了表子查询的选择清单，但它的查询结果允许返回多行。可以从上下文中区分出表子查询：在其前面始终有一个只对表子查询的算符：<比较算符>ALL、<比较算符>ANY(或是其同义词<比较算符>SOME)、IN和EXISTS。其中，在IN/NOTIN表子查询的情况下，DM支持查询结果返回多列。例如：查询B类客户的姓名、联系电话。SELECTCustomerName,PhoneFROMCustomerWHERECustomerTypeIDIN(SELECTCustomerTypeIDFROMCustomerTypeWHERETypeName='B类客户');4.2数据查询4.2.4其他查询44．派生表子查询派生表子查询是一种特殊的表子查询。所谓派生表是指FROM子句中的查询表达式，可以以别名对其进行引用。在SELECT语句的FROM子句中可以包含一个或多个派生表。派生表嵌套层次不能超过60层。例如：查询所有商品分类的编号、名称和对应子类的数量，并按数量递减排列。查询每个目录的编号、名称和对应的子目录的数量，并按数量递减排列。SELECTT1.CategoryID,T1.CategoryName,T2.NUMFROMCategoryT1,(SELECTCategoryID,COUNT(SubCategoryID)FROMSubCategoryGROUPBYCategoryID)AST2(CategoryID,NUM)WHERET1.CategoryID=T2.CategoryIDORDERBYT2.NUMDESC;4.2数据查询4.2.4其他查询45．带EXISTS谓词的子查询带EXISTS谓词的子查询语法如下：<EXISTS谓词>::=[NOT]EXISTS<表子查询>EXISTS判断是对非空集合的测试并返回TRUE或FALSE。若表子查询返回至少一行，则EXISTS返回TRUE，否则返回FALSE。若表子查询返回0行，则NOTEXISTS返回TRUE，否则返回FALSE。例如：查询客户“张平”的订单编号、快递单号和下单时间。SELECTT1.OrderID,T1.ExpressNO,T1.OrderTimeFROMProductOrderT1WHEREEXISTS(SELECT*FROMCustomerT2WHERET2.CustomerID=T1.CustomerIDANDT2.CustomerName='张平');4.2数据查询4.2.4其他查询46．多列表子查询为了满足应用需求，DM数据库扩展了子查询功能，目前支持多列IN/NOTIN子查询。子查询可以是值列表或者查询块。例如：查询“类型编号”为6且“是否上架”为1的商品名称、价格和库存。SELECTProductName,ProductPrice,ProductCountFROMProductWHERE(CategoryID,IsOn)IN((6,1));4.3数据更新DM_SQL语言的数据更新语句包括：数据插入、数据修改和数据删除三种语句，其中数据插入和修改两种语句使用的格式要求比较严格。在使用时要求对相应基表的定义，如列的个数、各列的排列顺序、数据类型及关键约束、唯一性约束、引用约束、检查约束的内容均要了解得很清楚，否则就很容易出错。下面将分别对这三种语句进行讨论。在讨论中，如不特别说明，各例均使用示例库BOOKSHOP，用户均为建表者SYSDBA。4.3数据更新4.3.1插入数据1数据插入语句用于向已定义好的表中插入单个或成批的数据。INSERT语句有两种形式。一种形式是值插入，即构造一行或者多行，并将它们插入到表中；另一种形式为查询插入，它通过<查询表达式>返回一个查询结果集以构造要插入表的一行或多行。例如：向客户信息表Customer中插入数据。INSERTINTOCustomerVALUES('zhangpin','123456',1,'张平','男',,'zhangpin@','北京','北京市朝阳区八里庄街道');INSERTINTOCustomerVALUES('wangmin','123456',2,'王敏','女',,'wangmin@163.com','上海','上海市黄浦区外滩街道');INSERTINTOCustomerVALUES('lilin','123456',3,'李玲','女',,'lilin@','广州','广州市海珠区新港街道');INSERTINTOCustomerVALUES('zhaoyifei','123456',4,'赵一飞','男',,'zhaoyifei@139.com','深圳','深圳市福田区梅林街道');INSERTINTOCustomerVALUES('yanghua','123456',1,'杨华','男',,'yanghua@126.com','成都','成都市锦江区春熙路街道');INSERTINTOCustomerVALUES('songxiaoyan','123456',2,'宋晓艳','女',,'songxiaoyan@','北京','北京市朝阳区三里屯街道');INSERTINTOCustomerVALUES('liuyike','123456',3,'刘亦可','女',,'liuyike@','上海','上海市黄浦区南京东路街道');INSERTINTOCustomerVALUES('luobin','123456',4,'罗斌','男',,'luobin@263.com','成都','成都市锦江区书院街街道');4.3数据更新4.3.2修改数据2数据修改语句用于修改表中已存在的数据。例如：修改商品子分类表SubCategory中相应分类的父类编号。执行如下语句：UPDATESubCategorySETCategoryID=1WHERESubCategoryID=1;UPDATESubCategorySETCategoryID=1WHERESubCategoryName='戴尔台式机';UPDATESubCategorySETCategoryID=1WHERESubCategoryName='惠普笔记本';UPDATESubCategorySETCategoryID=2WHERESubCategoryNameIN('小说','教材','儿童读物');UPDATESubCategorySETCategoryID=3WHERESubCategoryNameIN('办公文具','学生文具');UPDATESubCategorySETCategoryID=4WHERESubCategoryNameIN('男装','女装','童装');4.3数据更新4.3.3删除数据2数据删除语句用于删除表中已存在的数据，支持单表删除和多表联合删除。例如：删除订单表ProductOrder中的所有数据。DELETEFROMProductOrder;删除客户信息表Customer中的所有D类客户。DELETEFROMCustomerWHERECustomerTypeID=(SELECTCustomerTypeIDFROMCustomerTypeWHERETypeName='D类客户');4.4索引和视图4.4.1索引的创建和删除1索引是为了快速检索和定位数据行而创建的一种数据结构。索引是由表中索引列数据进行排序后的集合和指向这些值的物理标识（例如：ROWID等聚集索引键）共同组成。在DM中，除了位图索引、位图连接索引、全文索引和空间索引外，索引数据都采用B+树结构进行存储，。索引和表一样需要存储空间。4.4索引和视图1从不同的角度可将索引分为五大类。聚集索引和非聚集索引功能索引虚索引和实索引单列索引和复合索引全局索引和局部索引4.4.1索引的创建和删除4.4索引和视图11．创建索引（1）显式地创建索引可以用CREATEINDEX语句显式地创建索引。例如：在客户信息表Customer的UserName列上创建一个名为customer_userName的索引，该索引使用表空间usersCREATEINDEXcustomer_usernameONCustomer(UserName)STORAGE(INITIAL50,NEXT50,ONUSERS);上述语句为该索引显式地指定了几个存储设置和一个表空间。如果没有给索引指定存储选项，则INITIAL和NEXT等存储选项会自动使用表空间的默认存储选项。4.4.1索引的创建和删除4.4索引和视图1创建聚集索引DM8中表（列存储表和堆表除外）都是使用B树索引结构管理的，每一个普通表都有且仅有一个聚集索引，数据都通过聚集索引键排序，根据聚集索引键可以快速查询任何记录。当建表语句未指定聚集索引键时，DM8的默认聚集索引键是ROWID。若指定索引键，表中数据都会根据指定索引键排序。建表后，DM8也可以用创建新聚集索引的方式来重建表数据，并按新的聚集索引排序。例如：对Customer表以UserName列新建聚集索引CREATECLUSTERINDEXclu_customer_userNameONCustomer(UserName);4.4.1索引的创建和删除4.4索引和视图1创建唯一索引索引可以是唯一的或非唯一的。唯一索引可以保证表上不会有两行数据在键列上具有相同的值，非唯一索引不会在键列上施加这个限制。可用CREATEUNIQUEINDEX语句来创建唯一索引。例如：在客户信息表Customer上创建一个唯一索引CREATEUNIQUEINDEXcustomer_unique_indexONdept(dname)STORAGE(ONusers);4.4.1索引的创建和删除4.4索引和视图1创建基于函数的索引基于函数的索引促进了限定函数或表达式的返回值的查询，该函数或表达式的值被预先计算出来并存储在索引中。正确使用函数索引，可以带来以下好处：创建更强有力的分类，例如可以用UPPER和LOWER函数执行区分大小写的分类；预先计算出计算密集的函数的值，并在索引中将其分类。可以在索引中存储要经常访问的计算密集的函数，当需要访问值时，该值已经计算出来了。因此，极大地改善了查询的执行性能；增加了优化器执行范围扫描而不是全表扫描的情况的数量。例如：考虑如下WHERE子句中的表达式CREATEINDEXidxONexample_tab(column_a+column_b);SELECT*FROMexample_tabWHEREcolumn_a+column_b<10;4.4.1索引的创建和删除4.4索引和视图1创建位图索引位图索引主要针对含有大量相同值的列而创建。位图索引被广泛引用到数据仓库中，创建方式和普通索引一致，对低基数（不同的值很少）的列创建位图索引，能够有效提高基于该列的查询效率。且执行查询语句的where子句中带有AND和OR谓词时，效率更加明显。例如：创建一个位图索引CREATEBITMAPINDEXbm1ONProductOrder(CustomerID);4.4.1索引的创建和删除4.4索引和视图1创建位图连接索引位图连接索引是一种提高通过连接实现海量数据查询效率的有效方式，主要用于数据仓库环境中。区别于上一节所说的建立在单表上的位图索引，位图连接索引是针对两个或者多个表的连接而建立的位图索引，同时保存了连接的位图结果。对于索引列中的每一个值，位图连接索引在索引表中保存了对应行的ROWID。例如：创建一个位图连接索引CREATEBITMAPINDEXCustomer_idxonProductOrder(ProductOrder.CustomerID)FROMCustomer,ProductOrderWHERECustomer.CustomerID=ProductOrder.CustomerID;4.4.1索引的创建和删除4.4索引和视图1（2）隐式地创建索引隐式创建方式只支持创建唯一索引。DM8通过在唯一键或主键上创建一个唯一索引来在表上实施UNIQUEKEY或PRIMARYKEY完整性约束。当启用约束时DM8自动创建该索引。例如：创建PRIMARYKEY约束时，系统会隐式地自动在表Customer的CustomerID列上创建一个唯一索引DROPTABLECustomer;CREATETABLECustomer(CustomerIDINTIDENTITY(1,1),UserNameVARCHAR(50)NOTNULL,CustomerPWDVARCHAR(50)NOTNULL,CustomerTypeIDINT,CustomerNameVARCHAR(20),SexCHAR(4),PhoneVARCHAR(50),EmailVARCHAR(50),CityVARCHAR(50),AddressVARCHAR(200),PRIMARYKEY(CustomerID));4.4.1索引的创建和删除4.4索引和视图12．重建索引当一个表经过大量的增删改操作后，表的数据在物理文件中可能存在大量碎片，从而影响访问速度。另外，当删除表的大量数据后，若不再对表执行插入操作，索引所处的段可能占用了大量并不使用的簇，从而浪费了存储空间。可以使用重建索引来对索引的数据进行重组，使数据更加紧凑，并释放不需要的空间，从而提高访问效率和空间效率。DM8提供的重建索引的系统函数为：SP_REBUILD_INDEX(SCHEMA_NAMEvarchar(256),INDEX_IDint);SCHEAM_NAME为索引所在的模式名，INDEX_ID为索引ID。4.4.1索引的创建和删除4.4索引和视图13．删除索引例如：下面的语句删除customer_userName索引DROPINDEXcustomer_userName;删除不存在的索引会报错。若指定IFEXISTS关键字，删除不存在的索引，不会报错，例如：DROPINDEXIFEXISTScustomer_userName;然而，不能直接删除与已启用的UNIQUEKEY键或PRIMARYKEY键约束相关的索引。要删除一个与约束相关的索引，必须停用或删除该约束本身。例如：下面的语句删除主键约束pk_customer_userName，同时删除其对应的索引ALTERTABLECustomerDROPCONSTRAINTpk_customer_userName;除了删除普通索引，DM8还提供删除聚集索引，只要其聚集索引是通过CREATECLUSTERINDEX明确建立的。例如：下面的语句删除emp表的聚集索引clu_emp_nameDROPINDEXclu_customer_userName;删除聚集索引其实是使用ROWID作为索引列重建聚集索引，即跟新建聚集索引一样会重建这个表以及其所有索引。删除表就自动删除了所有与其相关的索引。4.4.1索引的创建和删除4.4索引和视图14．查看索引信息创建索引后，可以通过INDEXDEF系统函数查看索引的定义。INDEXDEF(INDEX_IDint,PREFLAGint);INDEX_ID为索引ID，PREFLAG表示返回信息中是否增加模式名前缀。4.4.1索引的创建和删除4.4索引和视图4.4.2视图2视图是从一个