理学建表单表查询

AnIntroductiontoDatabaseSystemSQL语言

1974年由Boyce和Chamberlin提出SQL全称—StructuredQueryLanguage原名：SEQUEL(StructuredEnglishQUEryLanguage)前身：SQUARE(SpecifyingQUEriesasRelationalExpressions)1975年～1979年IBM公司在SystemR原型系统上实现是关系数据库的标准语言，是数据库领域中一个主流语言3.1SQL概述AnIntroductiontoDatabaseSystem3.1SQL概述（续）SQL标准

SQL-86第一个SQL标准由美国国家标准局（AmericanNationalStandardInstitute，简称ANSI）公布1987年国际标准化组织（InternationalOrganizationforStandardization，简称ISO）通过

SQL-89，SQL1SQL-92，SQL2SQL-99，SQL3SQL-2003AnIntroductiontoDatabaseSystem3.1SQL概述（续）SQL的特点1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方法5.语言简洁，易学易用AnIntroductiontoDatabaseSystem1.综合统一SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体非关系模型的数据语言模式数据定义语言（模式DDL）外模式数据定义语言（外模式DDL或子模式DDL）与数据存储有关的描述语言（DSDL）数据操纵语言（DML）AnIntroductiontoDatabaseSystem2.高度非过程化用户只需提出“做什么”，而不必指明“怎么做”存取路径的选择以及SQL语句的操作过程由系统自动完成。大大减轻了用户负担，而且有利于提高数据独立性。AnIntroductiontoDatabaseSystem3.面向集合的操作方式SQL语言采用集合操作方式操作对象、查找结果可以是元组的集合一次插入、删除、更新操作的对象可以是元组的集合非关系数据模型采用的是面向记录的操作方式，操作对象是一条记录。AnIntroductiontoDatabaseSystem4.同一种语法结构提供两种使用方式自含式语言（交互式）能够独立地用于联机交互的使用方式嵌入式语言（宿主式）能够嵌入到高级语言（例如C，COBOL，FORTRAN，PL/1）程序中，供程序员设计程序时使用。两种不同使用方式下，SQL语言的语法结构基本一致AnIntroductiontoDatabaseSystem5.语言简捷，易学易用AnIntroductiontoDatabaseSystemSQL语言的基本概念SQL语言支持关系数据库三级模式结构AnIntroductiontoDatabaseSystem用户用SQL语言对基本表和视图进行操作基本表本身独立存在的表，在SQL中一个关系就对应一个表一个（或多个）基本表对应一个存储文件一个表可以带若干索引，索引也存放在存储文件中存储文件存储文件的逻辑结构组成了关系数据库的内模式存储文件的物理结构是任意的，对用户是透明的视图从一个或几个基本表或视图导出的表是虚表，只存放视图的定义而不存放对应数据AnIntroductiontoDatabaseSystem3.2数据定义SQL的数据定义功能定义表(模式)

创建表删除表修改表定义定义视图(外模式)

创建视图删除视图间接修改视图定义：删除+创建定义索引(内模式)

创建索引删除索引间接修改索引定义：删除+创建AnIntroductiontoDatabaseSystem3.2数据定义(续)AnIntroductiontoDatabaseSystem3.2.1定义、删除与修改基本表一、定义基本表(CREATETABLE)二、修改基本表(ALTERTABLE)三、删除基本表(DROPTABLE)AnIntroductiontoDatabaseSystem一、定义基本表语句格式CREATETABLE<表名>

（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；<表名>：所要定义的基本表的名字<列名>：组成该表的各个属性（列）<列级完整性约束条件>：涉及相应属性列的完整性约束条件<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件AnIntroductiontoDatabaseSystem一个简单例子 [例1]建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。。

CREATETABLEStudent(SnoCHAR(5)PRIMARYKEY，

SnameCHAR(20)，

SsexCHAR(1)，

SageINT，

SdeptCHAR(15))；AnIntroductiontoDatabaseSystem例题（续）AnIntroductiontoDatabaseSystem数据类型CHAR(n)VARCHAR(n)INTSMALLINTNUMERIC(p,s)FLOAT(n)DATETIMEAnIntroductiontoDatabaseSystem定义基本表（续）不同的数据库系统支持的数据类型不完全相Transact-SQL含有以下基本数据类型：biginttinyintbitintSmallintrealDecimalfloatmoneysmallmoneycharNcharntextnvarcharVarcharsmalldatetimetimestampdatetimeimageVarbinarytextBinary

uniqueidentifierAnIntroductiontoDatabaseSystem常用完整性约束实体完整性约束：PRIMARYKEY参照完整性约束：FOREIGNKEY用户定义的完整性约束唯一性约束：UNIQUE非空值约束：NOTNULL检查性约束：CHECKAnIntroductiontoDatabaseSystem实体完整性定义[例]建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),Gradeint,

PRIMARYKEY(Sno,Cno));AnIntroductiontoDatabaseSystem参照完整性定义CREATETABLESC(SnoCHAR(5)REFERENCESStudent(Sno),CnoCHAR(3)REFERENCESCourse(Cno),Gradeint,PRIMARYKEY(Sno,Cno));AnIntroductiontoDatabaseSystem破坏参照完整性的四种情况参照表——拒绝插入一个元组修改外码的值被参照表——拒绝，级联操作，置空值，置缺省值删除一个元组修改主码的值被参照表Sno参照表Student(Sno,……)←SC(Sno,Cno,Grade)AnIntroductiontoDatabaseSystem违约处理策略拒绝操作 NOACTION级联操作 CASCADE置空值 SETNULL 置缺省值 SETDEFAULTAnIntroductiontoDatabaseSystemCREATETABLESC(SnoCHAR(5)REFERENCES

Student(Sno),CnoCHAR(3),Gradeint,PRIMARYKEY(Sno,Cno), FOREIGNKEYCnoREFERENCES

Course(Cno) ONDELETENOACTION ONUPDATECASCADE);AnIntroductiontoDatabaseSystem用户定义的完整性例：CREATETABLEStudent(SnoCHAR(5)UNIQUE，

SnameCHAR(20)NOTNULL，

SsexCHAR(1)CHECK(SsexIN(‘F’,’M’))，

SageINTDEFAULT18，

SdeptCHAR(15))；AnIntroductiontoDatabaseSystem用户定义的完整性例：CreateTableRCDA (编号 Char(8)PrimaryKey,

性别 Char(2),

出生日期 Datetime,

工资现状 Decimal(10,2)

Default 1000

Check (工资现状<10000) )AnIntroductiontoDatabaseSystem二、修改基本表语句格式ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][MODIFY<列名><数据类型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件MODIFY子句：用于修改列名和数据类型AnIntroductiontoDatabaseSystem例题[例2]向Student表增加“入学时间”列，其数据类型为日期型。

ALTERTABLEStudentADDScomeDATE；不论基本表中原来是否已有数据，新增加的列一律为空值。

[例3]将年龄的数据类型改为半字长整数。

ALTERTABLEStudentMODIFYSageSMALLINT；修改原有的列定义有可能会破坏已有数据。[例4]删除学生姓名必须取唯一值的约束。

ALTERTABLEStudentDROPUNIQUE(Sname)；AnIntroductiontoDatabaseSystem语句格式（续）删除属性列间接删除把表中要保留的列及其内容复制到一个新表中删除原表再将新表重命名为原表名直接删除属性列:(新)

例：ALTERTABLEStudentDropScome；AnIntroductiontoDatabaseSystem三、删除基本表 DROPTABLE<表名>;

[例5]删除Student表

DROPTABLE

Student;基本表删除 数据、表上的索引都删除 表上的视图往往仍然保留，但 无法引用AnIntroductiontoDatabaseSystem3.2.2建立与删除索引建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引

PRIMARYKEYUNIQUE维护索引

DBMS自动完成

使用索引

DBMS自动选择是否使用索引以及使用哪些索引AnIntroductiontoDatabaseSystemAnIntroductiontoDatabaseSystem一、建立索引语句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)；

<表名>指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引AnIntroductiontoDatabaseSystem例题[例6]为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；

AnIntroductiontoDatabaseSystem建立索引（续）唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束AnIntroductiontoDatabaseSystem建立索引（续）聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致AnIntroductiontoDatabaseSystem例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放

AnIntroductiontoDatabaseSystem建立索引（续）在一个基本表上最多只能建立一个聚簇索引在最常查询的列上建立聚簇索引，可以提高查询效率对于经常更新的列不宜建立聚簇索引AnIntroductiontoDatabaseSystem二、删除索引 DROPINDEX<索引名>；删除索引时，系统会从数据字典中删去有关该索引的描述。[例7]删除Student表的Stusname索引。

DROPINDEXStusname；AnIntroductiontoDatabaseSystem3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结AnIntroductiontoDatabaseSystem3.3.1概述语句格式SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM

<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；

AnIntroductiontoDatabaseSystem语句格式SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序或降序排序AnIntroductiontoDatabaseSystem示例数据库学生-课程数据库学生表：Student(#Sno，Sname，Ssex，Sage，Sdept)课程表：Course(#Cno，Cname，Cpno，Ccredit)学生选课表：SC(#Sno，#Cno，Grade)AnIntroductiontoDatabaseSystem3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结AnIntroductiontoDatabaseSystem3.3.2单表查询

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

AnIntroductiontoDatabaseSystem1.查询指定列[例1]查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；

[例2]查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；AnIntroductiontoDatabaseSystem2.查询全部列[例3]查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；AnIntroductiontoDatabaseSystem3.查询经过计算的值SELECT子句的<目标列表达式>为表达式算术表达式字符串常量函数列别名等AnIntroductiontoDatabaseSystem3.查询经过计算的值（续）[例4]查全体学生的姓名及其出生年份。SELECTSname，2007-SageFROMStudent；

输出结果：

Sname2007-Sage----------------------

李勇1976

刘晨1977

王名1978

张立1978AnIntroductiontoDatabaseSystem3.查询经过计算的值（续）[例5]查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。

SELECTSname，'YearofBirth:'，2007-Sage，

ISLOWER(Sdept)FROMStudent；

AnIntroductiontoDatabaseSystem例题（续）输出结果：

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

李勇YearofBirth:1976cs

刘晨YearofBirth:1977is

王名YearofBirth:1978ma

张立YearofBirth:1977isAnIntroductiontoDatabaseSystem[例5.1]使用列别名改变查询结果的列标题SELECTSnameNAME，'YearofBirth:’

BIRTH，2007-Sage

BIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent；输出结果：

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

李勇YearofBirth:1976cs

刘晨YearofBirth:1977is

王名YearofBirth:1978ma

张立YearofBirth:1977isAnIntroductiontoDatabaseSystem二、选择表中的若干元组消除取值重复的行查询满足条件的元组AnIntroductiontoDatabaseSystem1.消除取值重复的行在SELECT子句中使用DISTINCT短语假设SC表中有下列数据

SnoCnoGrade---------------------950011929500128595001388

9500229095002380AnIntroductiontoDatabaseSystemALL与

DISTINCT

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

或(默认ALL)SELECTALLSnoFROMSC;

结果：Sno-------9500195001950019500295002AnIntroductiontoDatabaseSystem例题（续）(2)SELECTDISTINCTSnoFROMSC;

结果：

Sno-------9500195002AnIntroductiontoDatabaseSystem2.查询满足条件的元组表3.3常用的查询条件查

询

条

件谓

词比

较=，>，<，>=，<=，!=，<>，!>，!<；NOT

+上述比较运算符确定范围BETWEENAND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件AND，ORWHERE子句常用的查询条件AnIntroductiontoDatabaseSystem(1)比较大小在WHERE子句的<比较条件>中使用比较运算符=，>，<，>=，<=，!=或<>，!>，!<，逻辑运算符NOT+比较运算符[例8]查询所有年龄在20岁以下的学生姓名及其年龄。

SELECTSname，SageFROMStudentWHERESage<20；或SELECTSname，SageFROMStudentWHERENOTSage>=20；AnIntroductiontoDatabaseSystem(2)确定范围使用谓词BETWEEN…AND…NOTBETWEEN…AND…[例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；AnIntroductiontoDatabaseSystem例题（续）[例11]查询年龄不在20~23岁之间的学生姓名、系别和年龄。

SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；AnIntroductiontoDatabaseSystem(3)确定集合使用谓词IN<值表>,NOTIN<值表><值表>：用逗号分隔的一组取值[例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECTSname，Ssex FROMStudent WHERESdeptIN('IS'，'MA'，'CS');AnIntroductiontoDatabaseSystem(3)确定集合[例13]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。

SELECTSname，Ssex FROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');AnIntroductiontoDatabaseSystem(4)字符串匹配[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]搜索与指定模式匹配的字符串、日期或时间值。<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词，用!=或<>运算符取代NOTLIKE谓词AnIntroductiontoDatabaseSystem通配符%(百分号)

代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_(下横线)

代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串AnIntroductiontoDatabaseSystemESCAPE短语：当用户要查询的字符串本身就含有%或_时，要使用ESCAPE'<换码字符>'短语对通配符进行转义。AnIntroductiontoDatabaseSystem例题1)匹配模板为固定字符串[例14]查询学号为95001的学生的详细情况。

SELECT*FROMStudentWHERESnoLIKE'95001'；等价于：

SELECT*FROMStudentWHERESno='95001'；AnIntroductiontoDatabaseSystem例题（续）2)匹配模板为含通配符的字符串

[例15]查询所有姓刘学生的姓名、学号和性别。

SELECTSname，Sno，Ssex FROMStudent WHERESnameLIKE‘刘%’；AnIntroductiontoDatabaseSystem例题（续）[例16]查询姓"欧阳"且全名为三个汉字的学生的姓名。

SELECTSname FROMStudent WHERESnameLIKE'欧阳__'；AnIntroductiontoDatabaseSystem例题（续）[例17]查询名字中第2个字为"阳"字的学生的姓名和学号。

SELECTSname，Sno FROMStudent WHERESnameLIKE'__阳%'；AnIntroductiontoDatabaseSystem例题（续）[例18]查询所有不姓刘的学生姓名。

SELECTSname，Sno，Ssex FROMStudent WHERESnameNOTLIKE'刘%'；AnIntroductiontoDatabaseSystem例题（续）3)使用换码字符将通配符转义为普通字符

[例19]查询DB_Design课程的课程号和学分。

SELECTCno，Ccredit FROMCourse WHERECnameLIKE'DB\_Design'ESCAPE'\‘

当转义符置于通配符之前时，该通配符就解释为普通字符。AnIntroductiontoDatabaseSystem例题（续）[例20]查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。

SELECT* FROMCourse WHERECnameLIKE‘DB\_%i__’ESCAPE‘\’；AnIntroductiontoDatabaseSystem(5)涉及空值的查询

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

SELECTSno，CnoFROMSCWHEREGradeISNULL；AnIntroductiontoDatabaseSystem例题(续)[例22]查所有有成绩的学生学号和课程号。

SELECTSno，Cno FROMSC WHEREGradeISNOTNULL；AnIntroductiontoDatabaseSystem(6)多重条件查询用逻辑运算符AND和OR来联结多个查询条件

AND的优先级高于OR

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

[NOT]IN[NOT]BETWEEN…AND…AnIntroductiontoDatabaseSystem例题[例23]查询计算机系年龄在20岁以下的学生姓名。

SELECTSname FROMStudent WHERESdept='CS'ANDSage<20；AnIntroductiontoDatabaseSystem改写[例12][例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECTSname，Ssex FROMStudent WHERESdeptIN('IS'，'MA'，'CS')可改写为：

SELECTSname，Ssex FROMStudent WHERESdept='IS'ORSdept='MA'ORSdept='CS'；AnIntroductiontoDatabaseSystem改写[例10][例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECTSname，Sdept，Sage FROMStudent WHERESageBETWEEN20AND23；可改写为：

SELECTSname，Sdept，Sage FROMStudent WHERESage>=20ANDSage<=23；AnIntroductiontoDatabaseSystem三、对查询结果排序使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示AnIntroductiontoDatabaseSystem对查询结果排序（续）[例24]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

SELECTSno，Grade FROMSC WHERECno='3'

ORDERBYGradeDESC；AnIntroductiontoDatabaseSystem查询结果

SnoGrade--------------9501095024950079295003829501082950097595014619500255AnIntroductiontoDatabaseSystem对查询结果排序（续）[例25]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT* FROMStudent

ORDERBYSdept，SageDESC；AnIntroductiontoDatabaseSystem四、使用集函数5类主要集函数计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和SUM（[DISTINCT|ALL