关系数据库标准语言SQL重点

关系数据库标准语言SQL2020/2/22第2页要点SQL语言简介SQL语言及其使用方式独立式SQL嵌入式SQL2020/2/22第3页3.1SQL概述StructuredQueryLanguage,1974年提出关系数据库的国际标准语言：大多数数据库均用SQL作为共同的数据存取语言和标准接口，实现不同数据库系统之间的互操作目前仍被不断扩充介于关系代数和关系演算之间，三者可相互转换2020/2/22第4页SQL的特点综合统一：集DDL、DML、DCL功能于一体，可独立完成数据库生命周期中的全部活动，语言风格统一高度非过程化：用户只需提出“做什么”，而无需指明“怎么做”面向集合的操作方式：操作对象、查询结果、更新数据均可以是元组的集合提供两种使用方式：自含式、嵌入式语言简捷，易学易用SQL功能关键动词数据查询SELECT数据定义CREATE,DROP,ALTER数据操纵INSERT,UPDATE,DELETE数据控制GRANT,REVOKE2020/2/22第5页SQL语言的基本概念SQL支持关系数据库的三级模式结构存储文件：组成关系数据库的内模式，对用户透明基表(BaseTable)：组成关系数据库的模式，一个关系对应一个基表，一或多个基表对应一个存储文件视图(View)：组成关系数据库的外模式，从一个或多个基表中导出，不独立存储在数据库中SQL视图1视图2基本表1基本表2基本表3基本表4存储文件1存储文件2外模式模式内模式2020/2/22第6页3.2数据定义SQL的数据定义语言(DDL)操作对象操作方式创建删除修改基表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW索引CREATEINDEXDROPINDEX2020/2/22第7页定义基表数据类型：不同的数据库系统有自己的数据类型规定，但一般都包括INTEGER,FLOAT,CHAR(n),VARCHAR(n)等完整性约束条件列级完整性约束条件：涉及表的某一列如对数据类型的约束，对数据格式的约束，对取值范围或集合的约束，对空值NULL(空值，不知道或不能用的值)的约束，对取值的唯一性UNIQUE约束，对列的排序说明等表级完整性约束条件：涉及表的一个或多个列如订货关系中规定发货量不得超过订货量CREATETABLE表名(列名数据类型[列级完整性约束][,列名数据类型[列级完整性约束]]…[,表级完整性约束]);2020/2/22第8页SQL支持的数据类型SMALLINT半字长的整数INT全字长的整数FLOAT浮点数CHAR(n)长度为n的定长字符串VARCHAR(n)最大长度为n的变长字符串DEC(p,q)十进制数，共p位，小数点后有q位DATE日期型,格式YYYY-MM-DDTIME时间型，格式为HH.MM.SSTIMESTAMP日-时戳（日期加时间）2020/2/22第9页完整性约束CREATETABLE的完整性约束NOTNULL属性值禁止为空UNIQUE取值唯一PRIMARYKEY(A1,...,An)主码--若干属性列CHECK(P)P为条件表达式2020/2/22第10页例：建立学生管理的相关基表CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE,SnameCHAR(20)UNIQUE,SsexCHAR(1),SageINTEGER,SdeptCHAR(15));CREATETABLESC(SnoCHAR(5)NOTNULLUNIQUE,CnoCHAR(8)NOTNULLUNIQUE,GradeINTEGER);CREATETABLECourse(CnoCHAR(8)NOTNULLUNIQUE,CnameCHAR(20),CpnoINTEGER,CcreditINTEGER);2020/2/22第11页例：建立图书管理的相关基表CREATETABLEBorrows(CardNoINTEGERNOTNULLUNIQUE,NameCHAR(10),DeptCHAR(20));CREATETABLEBooks(BookNoINTEGERNOTNULLUNIQUE,SortNoCHAR(10),TitleCHAR(30),AuthorCHAR(12),PriceFLOAT,LoanNoINTEGER);CREATETABLELoans(CardNoINTEGERNOTNULLUNIQUE,BookNoINTEGERNOTNULLUNIQUE,TitleCHAR(30),DateCHAR(10));2020/2/22第12页例：完整性约束CREATETABLEbranch(branch-namechar(15)notnull,branch-citychar(30),assetsinteger)；CREATETABLEbranch(branch-namechar(15),branch-citychar(30),assetsInteger,PRIMARYKEY(branch-name),CHECK(assets=0))；2020/2/22第13页修改基表ALTERTABLE表名[ADD列名数据类型[列级完整性约束]][DROP完整性约束名][MODIFY列名数据类型];例ALTERTABLELoansMODIFYCardnoSMALLINT;ALTERTABLELoansDROPUNIQUE(BookNo);ALTERTABLELoansADDXXINT;2020/2/22第14页删除基本表DROPTABLE表名;例DROPTABLELoans;2020/2/22第15页建立索引加快检索速度UNIQUE表示索引的每一个索引值只对应唯一的数据记录CLUSTER：建聚簇索引，即索引项顺序与表中记录的物理顺序一致，一个基表只能建一个聚簇索引ASC(升序，缺省)、DESC(降序)索引建立后由系统使用和维护，不需用户干预CREATE[UNIQUE][CLUSTER]INDEX索引名ON表名(列名[ASC|DESC][,列名[ASC|DESC]]…);CREATEUNIQUEINDEXIBONBorrows(CardNo);例CREATEUNIQUEINDEXISONStudent(Sno);CREATEUNIQUEINDEXSCnoONSC(SnoASC,CnoDESC);2020/2/22第16页删除索引DROPINDEX索引名;•例DROPINDEXIB;DROPINDEXSCno;提示：在Access中用DROPINDEXSCnoONSC;2020/2/22第17页3.3查询数据库查询是数据库的核心操作，SQL提供了基于集合和关系的查询操作，具有丰富的功能和灵活的使用方式一个SQL查询的结果是一个关系查询可分为单表查询：查询只涉及一个表连接查询：查询同时涉及两个以上的表嵌套查询：一个查询块嵌套在另一个查询块中视图查询：在视图基础上的查询2020/2/22第18页SQL查询语句的格式查询语句的典型格式这个查询与下面的关系代数表达式等价：A1,A2,...,An(P(r1xr2x...xrm))SELECT[ALL|DISTINCT]目标列表达式[,目标列表达式]…FROM表名或视图名[,表名或视图名]…[WHERE条件表达式][GROUPBY列名1[HAVING条件表达式]][ORDERBY列名2[ASC|DESC]];selectA1,A2,...,Anfromr1,r2,...,rmwhereP在做每个SQL查询时都试着写出其关系代数表达式2020/2/22第19页SELECT*FROMStudent;3.3.1单表查询一、选择表中若干列查询指定列：在目标列表达式中指定预查属性查询全部列：在目标列表达式中使用*查询经计算的值：在目标列表达式中可使用常量、表达式、函数等SELECTSname,‘YearofBirthis’,2002-SageFROMStudent;SELECTSno,SnameFROMStudent;2020/2/22第20页查询实例SELECTSname,‘YearofBirthis’,2002-Sage,ISLOWER(Sdept)FROMStudent;结果为：Sname‘yearofBirth:’2002-SageISLOWER(Sdept)————————————————————————李勇YearofBirht:1982cs刘晨YearofBirht:1983is王敏YearofBirht:1984ma张立YearofBirht:1983is2020/2/22第21页定义别名用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。例如对于上例，可以定义如下列别名：SELECTSnameNAME,‘YearofBirth:’BIRTH,1996-SageYEAR,ISLOWER(Sdept)DEPARTMENTFROMStudent;结果为：NAMEBIRTHYEARDEPARTMENT——————————————————-----——————李勇YearofBirth:1982cs刘晨YearofBirth:1983if王敏YearofBirth:1984ma张立YearofBirth:1983if2020/2/22第22页ACCESS数据库中SQL例别名表示：字段as别名注：ISLOWER()函数在access中无法识别SELECTSnameASNAME,'YearofBirth:'ASBIRTH,1996-SageASYEAR,SdeptASDEPARTMENTFROMStudent;2020/2/22第23页二、选择表中若干元组取消取值重复的列：指定DISTINCT短语查询满足条件的元组：在WHERE子句中指定条件对查询结果排序：使用ORDERBY使用集函数：COUNT,SUM,AVG,MAX,MIN对查询结果分组：使用GROUPBY，HAVING2020/2/22第24页DISTINCT短语SQL允许重复的元组/行存在，如果需要去掉重复的元组/行，必须指定DISTINCT短语，缺省为ALL例:查询选修了课程的学生学号结果为：结果为：SnoSno——————95001950019500195002950019500295002SELECTDISTINCTSnoFROMSC;SELECTSnoFROMSC;或SELECTALLSnoFROMSC;2020/2/22第25页WHERE子句查询满足条件的元组：在WHERE子句中指定条件WHERE子句常用的查询条件:查询条件谓词比较=,,,=,=,!=,,!,!;NOT+上述比较运算符确定范围BETWEENAND,NOTBETWEENAND确定集合IN,NOTIN字符匹配LIKE,NOTLIKE空值ISNULL,ISNOTNULL多重条件AND,OR2020/2/22第26页Where子句－比较大小SELECTSnameFROMStudentWHERESdept=‘CS’;SELECTDISTINCTSnoFROMSCWHEREGrade60;例查询计算机系全体学生的名单例查询考试成绩有不及格的学生的学号例查询所有年龄在20岁以下的学生姓名及其年龄SELECTSname,SageSELECTSname,SageFROMStudentFROMStudentWHERESage20;WHERENOTSage=20;2020/2/22第27页Where子句－确定范围SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND40;例查