数据类型和索引和表

数据类型和索引和表第1页，课件共84页，创作于2023年2月Oracle数据库中常用的基本数据类型第2页，课件共84页，创作于2023年2月Sqlserver数据库中常用的数据类型数据类型指定列、存储过程参数及局部变量的数据特性数据按照数据类型存储在列中系统数据类型：系统数据类型是SQLServer支持的内置数据类型用户定义的数据类型：用户根据系统数据类型自己定义的数据类型第3页，课件共84页，创作于2023年2月系统数据类型(1)字符型描述所需空间char固定长度的字符数据，最大长度为8,000个字符。0到8000字节varchar可变长度的数据，最大长度为8000个字符输入数据的实际长度text可变长度的数据，最大长度为2^31-1个字符16字节的指针，每个初始化列至少外加2000字节第4页，课件共84页，创作于2023年2月系统数据类型(2)Unicode类型描述所需空间nchar固定长度的Unicode数据，最大长度为4000个字符0到4000字节，具体取决于定义nvarchar可变长度的Unicode数据，最大长度为4000个字符存储大小是输入数据的实际长度ntext可变长度的Unicode数据，最大长度为2^30-1个字符16字节的指针，每个初始化列至少外加2000字节第5页，课件共84页，创作于2023年2月系统数据类型(3)日期类型描述所需空间smalldatetime从1900.1.1到2079.6.6，精确到1分钟4字节datetime从1753.1.1到9999.12.31，精确到三百分之一秒，即3.33毫秒。8字节。第6页，课件共84页，创作于2023年2月系统数据类型(4)数字类型描述所需空间int整型数据，从–2^31到2^31-14字节smallint整型数据，从-2^15到2^15-12字节tinyint整型数据，从0到2551字节decimal带定点精度和小数位数的十进制数字类型。5-17字节(由精度决定)numericdecimal的同义词5-17字节(由精度决定)bit整型数据，值为1或01字节第7页，课件共84页，创作于2023年2月系统数据类型(5)浮点类型描述所需空间float浮点精度数字数据，从–1.79E+308到1.79E+3088字节real浮点精度数字数据，从–3.40E+38到3.40E+384字节。第8页，课件共84页，创作于2023年2月系统数据类型(6)货币类型描述所需空间money从–2^63到2^63-1，精确到每个货币单位的万分之一8字节smallmoney从–2^31到2^31-1，精确到每个货币单位的万分之一4字节第9页，课件共84页，创作于2023年2月系统数据类型(7)二进制类型描述所需空间varbinary可变长度的二进制数据，最大长度为8000字节输入数据的长度再加4字节binary固定长度的二进制数据，最大长度为8,000字节0到8000字节，具体取决于定义image可变长度的二进制数据，最大长度为2^31-1字节16字节的指针每个初始化列至少外加2000字节第10页，课件共84页，创作于2023年2月系统数据类型(8)timestamp整个数据库范围内的唯一数字8字节特殊类型描述所需空间uniqueidentifier产生全局唯一的标识符（GUID）16字节第11页，课件共84页，创作于2023年2月用户定义的数据类型特性使用系统数据类型定义具有预定义长度指定其中是否可存储null值指定是否必须赋予默认值指定是否要应用现有规则第12页，课件共84页，创作于2023年2月管理用户定义的数据类型(1)sp_addtype[@typename=]type,

[@phystype=]system_data_type

[,[@nulltype=]'null_type']第13页，课件共84页，创作于2023年2月管理用户定义的数据类型(2)参数：[@typename=]type

用户定义数据类型的名称。数据类型名称必须遵循标识符规则，并且在每个数据库中必须是唯一的。type是sysname，无默认值。[@phystype=]system_data_type

物理或SQLServer提供的数据类型（decimal、int等），用户定义的数据类型即基于该类型。system_data_type是sysname，无默认值。[@nulltype=]‘null_type’

指定必须如何处理null值。null_type是varchar(8)，设置值为‘NULL’（默认）、‘NOTNULL’

或‘NONULL’

第14页，课件共84页，创作于2023年2月Access数据库常用数据类型第15页，课件共84页，创作于2023年2月Access2000中字段可用的数据类型有以下几种：文本(Text)：文本、文本与数字、不必计算的数字，最长255个字符。备注(Memo)：较长的文本，最长64,000字符。数字(Number)型：用于算术运算的数字。可在“字段大小”中分字节、整型、单精度等子类型日期及时间(Date/Time)：从100到9999的日期及时间值。可进行日期及时间的计算。8字节货币(Currency)：货币值。使用货币类型可避免计算时四舍五入。8字节自动编号(AutoNumber)：添加记录时的自动序号

递增：添一条记录，字段值增1；

随机：添一条记录，字段值为另一随机数。

2.字段的数据类型第16页，课件共84页，创作于2023年2月是/否(Yes/No)：取两种值之一，如Yes/No、True/False、On/Off。大小为1位。OLE对象(Object)：可链接或嵌入其他使用OLE协议的程序所创建的对象，如Word文档、图象、声音等。最多1G。对象可链接或嵌入到Access表中。但只能在窗体或报表中使用绑定对象框来显示。超链接(Hyperlink)：超文本，用作超链接地址。超链接最多包含三部分：

显示文本：在字段或控件中显示的文本；

地址：到文件（UNC）或页面（URL）的路径；

子地址：在文件或页面中的地址。查阅向导(LookupWizard)：由查阅向导创建。字段的值来自另一个表或数据列表中，可在列表框或组合框中选择一个数据作为其值。

第17页，课件共84页，创作于2023年2月备注型、超链接型和OLE对象型字段不能索引。若在表中输入数据后更改字段的数据类型，则 在保存表时，由于进行大量数据转换，时间较长； 若新的“数据类型”属性的设置与段原数据类型冲突， 可能会丢失某些数据。Number、Date/Time、Currency和Yes/No类型具有预定义格式。可设置格式，但OLE对象型除外

5.选择数据类型时应注意的问题第18页，课件共84页，创作于2023年2月MySQL数据类型数据类型种类数值列类型字符串列类型日期和时间列类型第19页，课件共84页，创作于2023年2月数据类型种类数值型

分为整型和浮点型整型：

可以由十进制和十六进制表示 整数由数字序列组成，如：1，100。 由十六进制表示方法：0x且后面加1—9和 A—F的任意数字或字母，并且0x中的X不 能大写。浮点型:

浮点数由一个数字加一个小数点再加上一 个数字组成。两个数字序列不能同时为空。

第20页，课件共84页，创作于2023年2月字符串:

由单引号或者双引号括起来的字符或者数字。 如：”abc”,‘abc10’字符串中要用转义字符才能表示的特殊符号第21页，课件共84页，创作于2023年2月日期和时间值 是存储如“2005-1-1”

或者“12:00:00”这样的数值的值。在MySQL中日期是按”年-月-日”的顺序。NULL值 是一种无类型的值，表示“空，什么也没有”。第22页，课件共84页，创作于2023年2月数值列类型

MySQL为除了NULL值外的所有通用数据类型提供了列类型。列类型是一种手段，通过这种手段可以描述表的列可以包含什么样类型的值。数值列类型 所有数值列类型的类型名及其说明和所占的字节数见下表：第23页，课件共84页，创作于2023年2月数值列类型包括整型和浮点型

说明：TINYINT,SAMLLINT,MEDIUMINT,INT,BIGINT每个数据类型的取值范围不同，故分别可以表示不同的数值范围。在定义整型列时，可以指定可选的显示尺寸M(见上表),M是从1到255的值，它表示显示列中值的字符数。第24页，课件共84页，创作于2023年2月

字符串列类型所有字符串列类型及其说明和所占的字节数见下表：第25页，课件共84页，创作于2023年2月说明：对于可变长的字符串类型，其长度取决于实际存放在列中的值的长度。此长度在上表中用L来表示。附加若干额外字节，用来记录字符串长度，也就是说总长度等于L＋n（n＝1、2、3、4、8）第26页，课件共84页，创作于2023年2月CHAR和VARCHAR类型

CHAR类型和VARCHAR类型长度范围都是0~255之间的大小。他们之间的差别在于MySQL处理存储的方式： CHAR把这个大小视为值的准确大小（用空格填补比较短的值）。 VARCHAR类型把它视为最大值并且只使用了存储字符串实际上需要的字节数（增加了一个额外的字节记录长度）。因而，较短的值当被插入一个语句为VARCHAR类型的字段时，将不会用空格填补（然而，较长的值仍然被截短）。第27页，课件共84页，创作于2023年2月BLOB和TEXT类型

BLOB是二进制大对象，如果想存储二进制数BLOB将是最佳选择，而TEXT与之相同，只是BOLOB按照二进制编码排序（区分大小写），TEXT按照字符文本模式排序（不区分大小写）。ENUM和SET类型 是特殊的串类型，其列值必须从固定的串集中选择，二者差别为前者必须是只能选择其中的一个值，而后者可以多选。（不推荐使用）第28页，课件共84页，创作于2023年2月日期和时间列类型所有时间和日期列类型的类型名及其说明和所占的字节数见下表 ：第29页，课件共84页，创作于2023年2月第30页，课件共84页，创作于2023年2月说明：每个时间和日期列类型都有一个零值，当插入非法数值时就用零值来添加表示日期时必须先按：年，月，日的顺序给出DATE,TIME,DATETIME分别是存储日期，时间与日期和时间的组合，其格式为“YYYY-MM-DD”，

“hh:mm:ss”和“YYYY-MM-DDhh:mm:ss”，对于DATETIME类型，日期和时间部分都需要TIMESTAMP

时间戳列类型以YYYYMMDDhhmmss的格式来表示值，其取值范围是19700101000000到2037年的某个时间，主要用于记录更改或创建某个记录第31页，课件共84页，创作于2023年2月总结：常用数据类型分类备注和说明数据类型说明二进制数据类型存储非子符和文本的数据BLOB可用来存储图像文本数据类型字符数据包括任意字母、符号或数字字符的组合char固定长度的非Unicode字符数据varchar可变长度非Unicode数据text存储长文本信息日期和时间日期和时间在单引号内输入time时间date日期datetime日期和时间数值型数据该数据仅包含数字，包括正数、负数以及浮点数intsmallint整数floatdouble浮点数货币数据类型用于财务数据decimal定点数Bit数据类型表示是/否的数据bit存储布尔数据类型第32页，课件共84页，创作于2023年2月3.3.3索引的建立和删除建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下的索引

PRIMARYKEYUNIQUE第33页，课件共84页，创作于2023年2月一、建立索引语句格式CREATE[UNIQUE]INDEX<索引名>ON<表名>(<字段>[<次序>][,<字段>[<次序>]]…)； 用<表名>指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录第34页，课件共84页，创作于2023年2月例题

[例14]为学生-课程数据库中的Student，Course两个表建立索引。其中Student表按姓名升序建索引，Course表按课程名称降序建唯一索引。CREATEINDEXstu_sname_indexONStudent(S_name)；CREATEUNIQUEINDEXcourse_cname_indexONCourse(C_namedesc)；第35页，课件共84页，创作于2023年2月㈡删除索引DROPINDEX<索引名>；删除索引时，系统会从数据字典中删去有关该索引的描述。[例15]删除Student表的stu_sname_index索引。

DROPINDEXstu_sname_index；注意：主键索引不能被删除；删除主键约束时，主键索引会被自动删除。第36页，课件共84页，创作于2023年2月3.4.1单表查询查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、orderby子句四、聚集函数（aggregatefunctions）五、groupby子句第37页，课件共84页，创作于2023年2月一、选择表中的若干列1.查询指定列[例1]查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；

[例2]查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；第38页，课件共84页，创作于2023年2月[例3]查询全体学生的详细记录。SELECTSno，Sname，Ssex，SBirthday，SdeptFROMStudent；或SELECT*FROMStudent；2.查询全部列

第39页，课件共84页，创作于2023年2月SELECT子句的<目标列表达式>为表达式算术表达式字符串常量函数列别名等3.查询经过计算的值

第40页，课件共84页，创作于2023年2月3.查询经过计算的值

[例4]查全体学生的姓名及其年龄。SELECTSname，Ceil((SysDate-Sbirthday)/365)FROMStudent；

输出结果：

SnameCeil((SysDate-Sbirthday)/365)----------------------

李勇20

刘晨21

王名19

张立19第41页，课件共84页，创作于2023年2月3.查询经过计算的值[例5]查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname,‘age:‘,Ceil((SysDate-Sbirthday)/365),LOWER(Sdept)FROMStudent；

第42页，课件共84页，创作于2023年2月例题（续）输出结果：Sname‘age:‘Ceil((SysDate-Sbirthday)/365)LOWER(Sdept)---------------------------------------------------------李勇age:20cs刘晨age:21is王名age:19ma张立age:19is第43页，课件共84页，创作于2023年2月[例6]使用列别名改变查询结果的列标题SELECTSname姓名,Ceil((SysDate-Sbirthday)/365)

年龄,LOWER(Sdept)所在系FROM

Student；输出结果：

姓名年龄所在系

-----------------------------

李勇20cs

刘晨21is

王名19ma

张立19is第44页，课件共84页，创作于2023年2月二、选择表中的若干元组消除取值重复的行查询满足条件的元组第45页，课件共84页，创作于2023年2月1.消除取值重复的行在SELECT子句中使用DISTINCT短语假设SC表中有下列数据

SnoCnoGrade---------------------9500119295001285950013889500229095002380第46页，课件共84页，创作于2023年2月ALL与

DISTINCT

[例6]查询选修了课程的学生学号。(1)SELECTSnoFROMSC;

或(默认ALL)SELECTALLSnoFROMSC;

结果：Sno-------9500195001950019500295002第47页，课件共84页，创作于2023年2月例题（续）(2)SELECTDISTINCTSnoFROMSC;

结果：

Sno-------9500195002第48页，课件共84页，创作于2023年2月例题（续）注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确的写法

SELECTDISTINCTCno，GradeFROMSC;

第49页，课件共84页，创作于2023年2月2.查询满足条件的元组WHERE子句常用的查询条件第50页，课件共84页，创作于2023年2月(1)比较大小在WHERE子句的<比较条件>中使用比较运算符=，>，<，>=，<=，!=或<>，!>，!<，逻辑运算符NOT+比较运算符[例8]查询所有年龄在20岁以下的学生姓名及其年龄。

SELECTSname，Sage

FROMStudentWHERESage<20；或SELECTSname，SageFROMStudentWHERENOTSage>=20；

第51页，课件共84页，创作于2023年2月(2)确定范围使用谓词BETWEEN…AND…NOTBETWEEN…AND…[例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；

第52页，课件共84页，创作于2023年2月例题（续）[例11]查询年龄不在20~23岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；第53页，课件共84页，创作于2023年2月(3)确定集合使用谓词IN<值表>,NOTIN<值表>

<值表>：用逗号分隔的一组取值[例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');第54页，课件共84页，创作于2023年2月(3)确定集合[例13]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');第55页，课件共84页，创作于2023年2月(4)字符串匹配

[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词用!=或<>运算符取代NOTLIKE谓词第56页，课件共84页，创作于2023年2月通配符%(百分号)代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_(下横线)代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串第57页，课件共84页，创作于2023年2月ESCAPE短语：当用户要查询的字符串本身就含有%或_时，要使用ESCAPE'<换码字符>'短语对通配符进行转义。第58页，课件共84页，创作于2023年2月例题1)匹配模板为固定字符串[例14]查询学号为95001的学生的详细情况。

SELECT*FROMStudentWHERESnoLIKE'95001'；等价于：

SELECT*FROMStudentWHERESno='95001'；第59页，课件共84页，创作于2023年2月例题（续）2)匹配模板为含通配符的字符串[例15]查询所有姓刘学生的姓名、学号和性别。

SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘刘%’；第60页，课件共84页，创作于2023年2月例题（续）匹配模板为含通配符的字符串（续）[例16]查询姓"欧阳"且全名为三个汉字的学生的姓名。

SELECTSnameFROMStudentWHERESnameLIKE'欧阳_'；第61页，课件共84页，创作于2023年2月例题（续）匹配模板为含通配符的字符串（续）[例17]查询名字中第2个字为"阳"字的学生的姓名和学号。

SELECTSname，SnoFROMStudentWHERESnameLIKE'_阳%'；第62页，课件共84页，创作于2023年2月例题（续）匹配模板为含通配符的字符串（续）[例18]查询所有不姓刘的学生姓名。

SELECTSnameFROMStudentWHERESnameNOTLIKE'刘%'；第63页，课件共84页，创作于2023年2月例题（续）使用换码字符将通配符转义为普通字符(续)[例20]查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。

SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；第64页，课件共84页，创作于2023年2月(5)涉及空值的查询

使用谓词ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替[例21]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECTSno，CnoFROMSCWHEREGradeISNULL；第65页，课件共84页，创作于2023年2月例题(续)

[例22]查所有有成绩的学生学号和课程号。

SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；第66页，课件共84页，创作于2023年2月(6)多重条件查询用逻辑运算符AND和OR来联结多个查询条件

AND的优先级高于OR

可以用括号改变优先级可用来实现多种其他谓词

[NOT]IN[NOT]BETWEEN…AND…第67页，课件共84页，创作于2023年2月例题[例23]查询计算机系年龄在20岁以下的学生姓名。

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；第68页，课件共84页，创作于2023年2月改写[例12][例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS')可改写为：SELECTSname，SsexFROMStudentWHERESdept='IS'ORSdept='MA'ORSdept='CS'；第69页，课件共84页，创作于2023年2月改写[例10][例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；可改写为：

SELECTSname，Sdept，SageFROMStudentWHERESage>=20ANDSage<=23；第70页，课件共84页，创作于2023年2月三、orderby子句 使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示第71页，课件共84页，创作于2023年2月Orderby子句（续）[例24]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；第72页，课件共84页，创作于2023年2月查询结果

SnoGrade--------------9501095024950079295003829501082950097595014619500255第73页，课件共84页，创作于2023年2月Orderby子句（续）[例25]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT*FROMStudentORDERBYSdept，SageDESC；第74页，课件共84页，创作于2023年2月四、聚集函数5类主要集函数计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和SUM（[DISTINCT|ALL]<列名>） 计算平均值AVG（[DISTINCT|ALL]<列名>）第75页，课件共84页，创作于2023年2月聚集函数（续）求最大值MAX（[DISTINCT|ALL]<列名>）

求最小值MIN（[DISTINCT|ALL]<列名>） DISTINCT短语：在计