第讲数据定义

会计学1第讲数据定义3.1SQL概述SQL的产生和发展1974年Boyce和Chamberlin提出1975～1979年在IBM的SystemR上实现1986年ANSI的数据库委员会批准了SQL作为关系数据库语言的美国标准1987年ISO也通过该标准第1页/共46页3.1SQL概述SQL的特点1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方法5.语言简洁，易学易用第2页/共46页第3页/共46页SQL的基本概念支持SQL的RDBMS支持关系数据库的三级模式结构。第4页/共46页3.2学生－课程数据库包括以下三个表：学生表：Student(Sno,Sname,Ssex,Sage,Sdept)课程表：Course(Cno,Cname,Cpno,Ccredit)选课表：SC(Sno,Cno,Grade)各个表的数据示例参见课本P82第5页/共46页3.3数据定义

SQL的数据定义功能包括对基本表(table)、视图(view)、和索引(index)的定义。第6页/共46页

<列名><数据类型>[列级完整性约束条件][，<列名><数据类型>[列级完整性约束条件]]

…[，<表级完整性约束条件>]<表名>：所要定义的基本表的名字<列名>：组成该表的各个属性（列）<列级完整性约束条件>：涉及相应属性列的完整性约束条件<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件1定义基本表的一般格式为：一、基本表的定义、修改与删除CREATETABLE

<表名>（）；第7页/共46页1.整数数据类型：依整数数值的范围大小，有BIT,INT,SMALLINT。2.精确数值类型：用来定义可带小数部分的数字，有NUMERIC和DECIMAL，如：123.0、8000.563.近似浮点数值数据类型：当数值的位数太多时，可用此数据类型取其近似值，用FLOAT和REAL两种。如：1.23E+10SQL支持的数据类型

不同的DBMS支持的数据类型不完全相同，其中使用最多的是字符型数据和数值型数据。例如，SQLSERVER中常用的数据类型有：第8页/共46页SQL支持的数据类型4.字符串数据类型：用来表示字符串的字段。包括：CHAR,VARCHAR,TEXT三种5.日期时间数据类型：用来表示日期与时间，依时间范围与精确程度可分为DATETIME与SMALLDATETIME两种。如：1998-06-0815:30:00第9页/共46页常用完整性约束实体完整性：PRIMARYKEY参照完整性：FOREIGNKEY…

REFERENCES…用户定义完整性：NOTNULL:列值非空UNIQE：列值唯一CHECK：检查列值是否满足一个布尔表达式第10页/共46页例1

建立一个“学生”表Student，Student(Sno,Sname,Ssex,Sage,Sdept)其中学号不能为空，值是唯一的，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(10)UNIQUE，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20))；第11页/共46页CREATETABLESC(SNOCHAR(5)，CNOCHAR(5)，SCORESMALLINT,PRIMARYKEY(SNO,CNO)，FOREIGNKEYSNOREFERENCESS(SNO),FOREIGNKEYCNOREFERENCESC(CNO));练习：解释如下基本表的定义.第12页/共46页例2建立一个课程表Course,并标明主码和外码。Course(Cno,Cname,Cpno,Ccredit)CREATETABLECourse(CnoCHAR(5)primarykey,CnameCHAR(20),CpnoCHAR(5),CcreditSMALLINT,ForeignkeyCpnoreferencesCourse(Cno));第13页/共46页2修改基本表ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][MODIFY<列名><数据类型>]；<表名>：要修改的基本表ADD子句：增加新列DROP子句：删除指定的完整性约束条件MODIFY子句：用于修改列的数据类型第14页/共46页例3向Student表增加“入学时间”列，其数据类型为日期型。

ALTER

TABLEStudent

ADDScomeDATE；例4将Student表年龄的数据类型改为短整数。ALTERTABLEStudentMODIFYSageSMALLINT；第15页/共46页DROPTABLE<表名>[RESTRICT\CASCADE];RESTRICT(缺省):该表的删除有限制条件。如，该表不能被其他表的约束所引用，不能有视图、触发器、存储过程。CASCADE:该表的删除没有限制条件。删除基本表时，相关依赖对象一起被删除。

3删除基本表例5删除Student表

DROPTABLEStudent;不同数据库产品在遵循SQL标准的基础上具体实现细节和处理策略会与标准有差别。例如，P89第16页/共46页第17页/共46页二、索引的建立与删除索引是基本表的目录，建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引

PRIMARYKEYUNIQUE维护索引

DBMS自动完成

使用索引

DBMS自动选择是否使用索引以及使用哪些索引第18页/共46页语句格式CREATE[UNIQUE][CLUSTER]

INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)；用<表名>指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用<次序>指定索引表中索引值的排列次序，升序：ASC，降序：DESC。缺省值为ASC。UNIQUE:唯一性索引。不允许索引列有同值；若已有同值，则不允许建立此索引。CLUSTER：聚簇索引。表中元组按索引项的值排序并物理地聚集在一起。1.建立索引第19页/共46页[例6]为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；第20页/共46页练习：解释如下索引的含义：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放第21页/共46页2.删除索引DROPINDEX<索引名>；删除索引时，系统会从数据字典中删去有关该索引的描述。[例7]删除Student表的Stusname索引。

DROPINDEXStusname；第22页/共46页索引的有关说明可以动态地定义索引，即可以随时建立和删除索引；一个表上可建多个索引；不允许用户在数据操作中引用索引，索引如何使用完全由系统决定；应在使用频率高、经常进行连接的列上建立索引；索引可提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率。第23页/共46页3.4数据查询

3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5小结第24页/共46页示例数据库

学生-课程数据库e.g.P82学生表：Student(Sno，Sname，Ssex，Sage，Sdept)课程表：Course(Cno，Cname，Cpno，Ccredit)

学生选课表：SC(Sno，Cno，Grade)第25页/共46页概述

一般格式SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；

第26页/共46页3.4.1单表查询

查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组

第27页/共46页1.查询指定列[例1]查询全体学生的学号与姓名。

[例2]查询全体学生的姓名、学号、所在系。SELECTSno，SnameFROMStudent；SELECTSname，Sno，SdeptFROMStudent；——考虑语句的执行过程一、选择表中的若干列第28页/共46页2.查询全部列[例3]查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；

或SELECT*FROMStudent；第29页/共46页3.查询经过计算的值[例4]查全体学生的姓名及其出生年份。SELECTSname，2010-SageFROMStudent；

输出结果：

Sname2010-Sage----------------------

李勇1985刘晨1986王名1987张立1986SELECT子句的<目标列表达式>可以是属性列，也可以表达式，如算术表达式、字符串常量、函数等第30页/共46页[例5]查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname，'YearofBirth:'，2010-Sage,ISLOWER(Sdept)FROMStudent；输出结果：

Sname'YearofBirth:'2010-SageISLOWER(Sdept)----------------------------------------------

李勇YearofBirth:1985cs

刘晨YearofBirth:1986is

王名YearofBirth:1987ma

张立YearofBirth:1986is第31页/共46页使用列别名改变查询结果的列标题SELECTSnameNAME，'YearofBirth:’BIRTH，2010-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent输出结果：

NAMEBIRTHBIRTHDAYDEPARTMENT-----------------------------------------------------

李勇YearofBirth:1985cs

刘晨YearofBirth:1986is

王名YearofBirth:1987ma

张立YearofBirth:1986is第32页/共46页假设SC表中有下列数据

SnoCnoGrade---------------------9500119295001285950013889500229095002380二、选择表中的若干元组

[例6]查询选修了课程的学生学号。结果：Sno-------9500195001950019500295002

SELECTSnoFROMSC;第33页/共46页SELECTDISTINCTSnoFROMSC

结果：

Sno----------95001950021.消除取值重复的行第34页/共46页2.查询满足条件的元组WHERE子句常用的查询条件第35页/共46页(1)比较大小在WHERE子句的<比较条件>中使用比较运算符=，>，<，>=，<=，!=或<>逻辑运算符NOT+比较运算符[例8]查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage<20；第36页/共46页(2)确定范围使用谓词BETWEEN…AND…NOTBETWEEN…AND…[例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。[例11]查询年龄不在20~23岁之间的学生姓名、系别和年龄。第37页/共46页(3)确定集合：谓词IN用来查找属性值属于指定集合的元组。使用谓词IN<值表>

NOTIN<值表>其中，<值表>：用逗号分隔的一组取值[例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。[例13]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。第38页/共46页(4)字符串匹配:查找指定的属性列值与匹配串相匹配的元组。[NOT]LIKE

‘<匹配串>’[ESCAPE

‘<换码字符>’]通配符：%(百分号)代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_(下横线)代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串第39页/共46页1)匹配串为固定字符串[例14]查询学号为9500