数据库sql基础知识大全.pptx
第三章 关系数据库语言SQL概述SQL的数据定义SQL的数据操纵SQL的视图定义SQL的数据控制嵌入式SQL第三章关系数据库语言SQL第1页/共74页 概述SQL（StructuredQueryLanguage）是1974年由Boyde和Chamberlin提出的1974年IBM的SystemR1979年Oracle1982年IBM的DB21984年Sybase1986年10月ANSI公布SQL-86标准1989年ISO公布SQL-89标准（120页）1992年公布SQL-92标准（622页）1999年公布SQL-99标准（1700页）2003年公布SQL-2003标准（3600页）第三章关系数据库语言SQL3.1 概述第2页/共74页SQL语言的特点综合统一集DDL、DML、DCL为一体实体和联系都是关系，因此每种操作只需一种操作符高度非过程化面向集合的操作方式以同一种语法结构提供两种使用方式（交互式和嵌入式）语言简捷，易学易用数据定义CREATE、DROP、ALTER数据查询SELECT数据更新INSERT、UPDATE、DELETE数据控制GRANT、REVOKE第三章关系数据库语言SQL3.1 概述第3页/共74页SQL语言的特点支持三级模式结构视图外模式基本表（的集合）模式存储文件和索引内模式第三章关系数据库语言SQL3.1 概述第4页/共74页SQL数据库体系结构用户2用户3用户4视图V1视图V2基本表B1基本表B2基本表B3基本表B4存储文件S1存储文件S2存储文件S3存储文件S4用户1SQLSQL用户用户视图视图基本表基本表存储文件存储文件第三章关系数据库语言SQL3.1 概述第5页/共74页SQL的组成数据定义语言(The Data Definition Language,DDL)数据操作语言(The Data Manipulation Language,DML)嵌入式和动态SQL事务管理安全性管理触发器和高级完整性约束客户服务器执行和远程数据库存取高级特性第三章关系数据库语言SQL3.1 概述第6页/共74页 SQL的数据定义定义和修改基本表（定义模式中的关系）：CREATETABLEDROPTABLEALTERTABLE定义视图（定义外模式）：CREATEVIEWDROPVIEW定义索引（定义内模式）：CREATEINDEXDROPINDEX第三章关系数据库语言SQL3.2 SQL的数据定义第7页/共74页基本表的定义基本格式CREATETABLE（，）；：所要定义的基本表的名字：组成该表的各个属性（列）：涉及相应属性列的完整性约束条件：涉及一个或多个属性列的完整性约束条件第三章关系数据库语言SQL3.2 SQL的数据定义第8页/共74页例1建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15)；基本表的定义第三章关系数据库语言SQL3.2 SQL的数据定义第9页/共74页基本表的定义说明：SQL支持空值的概念。允许空值的列未输入数据时系统自动置为空值。SQL支持的数据类型随系统不同而有所差异，但一般都有：全字长整型、半字长整型、定点实型、浮点实型、CHAR（n）、VARCHAR（n）、TEXT、DATE第三章关系数据库语言SQL3.2 SQL的数据定义第10页/共74页基本表的定义常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束FOREIGNKEYcheck约束思考：PRIMARYKEY与UNIQUE的区别？Primarykey在建立的时候会默认地建立此field的索引，且此primarykey可以作为作为另外的表的foreignkey；再者primarykey跟unique的区别是Primarykey一定是notnull，而unique则没有此限制第三章关系数据库语言SQL3.2 SQL的数据定义第11页/共74页SQL Server 2005中的数据类型精确数字精确数字bigintdecimalintnumericsmallintmoneytinyintsmallmoneybit近似数字近似数字floatreal日期和时间日期和时间datetimesmalldatetime字符串字符串chartextvarcharUnicode字符串字符串ncharntextnvarchar二进制字符串二进制字符串binaryimagevarbinary其他数据类型其他数据类型cursortimestampsql_variantuniqueidentifiertablexml第12页/共74页例2建立一个“课程”表C，它由课程号Cno，课程名称Cname，教师Teacher组成，其中Cno为主码。CREATETABLEC(CnoCHAR(5),CnameCHAR(10),TeacherCHAR(10),Primarykey(Cno);或像书上写的将Primarykey直接定义在属性列后。第13页/共74页基本表的定义例3建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),Gradeint,Primarykey(Sno,Cno),FOREIGNKEY(Sno)REFERENCESS(Sno),FOREIGNKEY(Cno)REFERENCESC(Cno);第三章关系数据库语言SQL3.2 SQL的数据定义第14页/共74页基本数据类型(1)数值型（DB2)SMALLINT半字长二进制整数INTEGER全字长二进制整数DECIMAL(P,q）或者DEC（p,q)压缩十进制数，共p位，其中小数点后q位FLOAT 双字长浮点数（2)字符串型CHARTER(n)或CHAR(n)VARCHAR(n)(3)时间型DATETIME(4)位串型BIT（n）第15页/共74页基本表的删除基本格式DROPTABLE表名；示例DROPTABLES；第三章关系数据库语言SQL3.2 SQL的数据定义第16页/共74页基本表的修改基本格式ALTERTABLE ADD 完整性约束 DROP ALTER COLUMN ；：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件ALTERCOLUMN子句：用于修改列名和数据类型第三章关系数据库语言SQL3.2 SQL的数据定义第17页/共74页基本表的修改增加列基本格式ALTERTABLE 表名ADD列名类型；示例ALTERTABLESADDADDRESSVARCHAR(30)修改列基本格式ALTERTABLE 表名ALTERCOLUMN列名类型；ALTERTABLES ALTERCOLUMNSASMALLINT；删除列基本格式ALTERTABLE 表名DROP列名；ALTERTABLESDROPUNIQUE(Sn)；第三章关系数据库语言SQL3.2 SQL的数据定义第18页/共74页例子 修改表ALTERTABLEstudentADDsnoCHAR(5)NULL-向student表中添加一列snoALTERTABLEstudent-修改sno的定义保障此列不为空MODIFYsnoCHAR(5)NOTNULLALTERTABLEstudent-向student表添加主键约束ADDPRIMARYKEY(sno)ALTERTABLEstudent-从student表删除列sexDROPCOLUMNsex第19页/共74页例子 修改表-向study表中添加外键约束ALTERTABLEstudyADDCONSTRAINTfkcnoFOREIGNKEY(cno)REFERENCEScourse(cno)ONDELETECASCADEONUPDATECASCADE-向study表中添加外键约束ALTERTABLEstudyADDCONSTRAINTfksnoFOREIGNKEY(sno)REFERENCESstudent(sno)ONDELETECASCADEONUPDATECASCADE第20页/共74页视图的定义和修改基本操作CREATEVIEWDROPVIEW后面详细讨论第三章关系数据库语言SQL3.2 SQL的数据定义第21页/共74页索引的建立和删除建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引PRIMARYKEYUNIQUE维护索引DBMS自动完成使用索引DBMS自动选择是否使用索引以及使用哪些索引第三章关系数据库语言SQL3.2 SQL的数据定义第22页/共74页索引的建立和删除索引的结构RDBMS中，索引一般采用B+树或HASH索引实现，具体由RDBMS决定。B+树具有动态平衡的优点HASH索引查找速度快。索引是关系数据库的内部实现技术，属内模式范畴。用户创建索引时，可创建唯一索引、非唯一索引或聚簇索引。第23页/共74页在SQL86和SQL89标准中，基本表没有关键码概念，可以用索引机制来弥补。索引属于物理存储的路径概念，而不是逻辑的概念。在定义基本表时，还要定义索引，就把数据库的物理结构和逻辑结构混在一块了。因此在SQL2中引入了主码（主键）的概念，用户在创建基本表时用主码子句Primarykey直接定义主码。但至今大多数DBMS仍使用索引机制，有索引创建和撤销语句，其功能仅限于查询时起作用。第24页/共74页索引的建立索引建立的基本格式ASCDESC，列名，列名ASCDESC）；）；一个索引项对应一个记录一个索引项对应一个记录升序或降序升序或降序CREATEUNIQUECLUSTERINDEX索引名索引名ON表名（列名表名（列名第三章关系数据库语言SQL3.2 SQL的数据定义聚簇索引UniqueUnique索引索引表明此索引的每个索引值只对应唯一的数据记录ClusterCluster索引索引又称聚簇索引，必须维护表中行的物理存储顺序和索引顺序一致，经常进行更新操作的表不宜建立聚簇索引。第25页/共74页索引的建立例、为学生-课程数据库中的S，C，SC三个表建立索引。其中S表按学号升序建唯一索引，C表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXSsnoONS(Sno)；CREATEUNIQUEINDEXCcnoONC(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；第三章关系数据库语言SQL3.2 SQL的数据定义第26页/共74页索引的建立唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束第三章关系数据库语言SQL3.2 SQL的数据定义第27页/共74页索引的建立聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATECLUSTERINDEXSsnameONS(SN)；在S表的SN（姓名）列上建立一个聚簇索引，而且S表中的记录将按照SN值的升序存放第三章关系数据库语言SQL3.2 SQL的数据定义第28页/共74页索引的建立在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可以提高查询效率聚簇索引的适用范围很少对基表进行增删操作很少对基表进行增删操作 很少对其中的变长列进行修改操作很少对其中的变长列进行修改操作第三章关系数据库语言SQL3.2 SQL的数据定义第29页/共74页索引的删除索引删除的基本格式DROPINDEX索引名删除索引时，系统会从数据字典中删去有关该索引的描述。第三章关系数据库语言SQL3.2 SQL的数据定义第30页/共74页 SQL的数据操纵SQL的数据查询（检索）SELECTSQL的数据更新（增、删、改）INSERT，DELETE，UPDATE第三章关系数据库语言SQL3.3 SQL的数据操纵第31页/共74页 SQL的数据查询一般格式SELECT目标列目标列FROM基本表（或视图）基本表（或视图）WHERE条件表达式条件表达式GROUPBY列名列名1HAVING内部函数表达式内部函数表达式ORDERBY列名列名2ASCDESC相当于投影相当于投影相当于选择或连接相当于选择或连接被查询的关系被查询的关系对查询结果排序对查询结果排序分组分组统计统计第三章关系数据库语言SQL3.3 SQL的数据操纵第32页/共74页SQL的数据查询n常用格式常用格式(SELECT-FROM-WHERE句型句型)SELECTA1,A2,AnFROMR1,R2,RmWHEREF其中，条件表达式其中，条件表达式F可使用下列操作符：可使用下列操作符：F算术比较运算符（算术比较运算符（,=,=,）F逻辑运算符（逻辑运算符（AND,OR,NOT）F集合运算符集合运算符（UNION,INTERSECT,EXCEPT）F集合成员资格运算符集合成员资格运算符（IN,NOTIN）F谓词谓词(EXISTS,ALL,SOME,UNIQUE)F聚合函数聚合函数(AVG,MIN,MAX,SUM,COUNT)F嵌套的嵌套的SELECT语句语句第三章关系数据库语言SQL3.3 SQL的数据操纵第33页/共74页示例数据库学生-课程数据库学生表：Student(Sno，Sname，Ssex，Sage，Sdept)课程表：Course(Cno，Cname，Cpno，Ccredit)学生选课表：SC(Sno，Cno，Grade)第三章关系数据库语言SQL3.3 SQL的数据操纵第34页/共74页一、单表查询查询仅涉及一个表，是一种最简单的查询操作选择表中的若干列选择表中的若干元组对查询结果排序使用集函数对查询结果分组第三章关系数据库语言SQL3.3 SQL的数据操纵第35页/共74页查询指定列例1查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；例2查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；第三章关系数据库语言SQL3.3 SQL的数据操纵第36页/共74页查询全部列例3查询全体学生的详细记录。SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student；或SELECT *FROM Student；第三章关系数据库语言SQL3.3 SQL的数据操纵第37页/共74页查询经过计算的值例4查全体学生的姓名及其出生年份。SELECTSname，2009-SageFROMStudent；输出结果：Sname2009-Sage-李勇1976刘晨1977王名1978张立1978第三章关系数据库语言SQL3.3 SQL的数据操纵第38页/共74页查询经过计算的值例5查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname，YearofBirth:，2009-Sage，ISLOWER(Sdept)FROMStudent；第三章关系数据库语言SQL3.3 SQL的数据操纵第39页/共74页查询经过计算的值输出结果：SnameYearofBirth:2009-SageISLOWER(Sdept)-李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is第三章关系数据库语言SQL3.3 SQL的数据操纵第40页/共74页使用列别名改变查询结果的列标题SELECTSnameNAME，YearofBirth:BIRTH，2009-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent；输出结果：NAMEBIRTHBIRTHDAYDEPARTMENT-李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is第三章关系数据库语言SQL3.3 SQL的数据操纵第41页/共74页选择表中的若干元组消除取值重复的行查询满足条件的元组第三章关系数据库语言SQL3.3 SQL的数据操纵第42页/共74页1.消除取值重复的行在SELECT子句中使用DISTINCT短语假设SC表中有下列数据SnoCnoGrade-9500119295001285950013889500229095002380第三章关系数据库语言SQL3.3 SQL的数据操纵第43页/共74页ALL 与 DISTINCT例6查询选修了课程的学生学号。(1)SELECTSnoFROMSC;或(默认ALL)SELECTALLSnoFROMSC;结果：Sno-9500195001950019500295002(2)SELECTDISTINCTSnoFROMSC;结果：结果：Sno-9500195002 第三章关系数据库语言SQL3.3 SQL的数据操纵第44页/共74页ALL 与 DISTINCT注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确写法SELECTDISTINCTCno，GradeFROMSC;第三章关系数据库语言SQL3.3 SQL的数据操纵第45页/共74页2.查询满足条件的元组F比较大小F确定范围F确定集合F字符串匹配F涉及空值的查询F多重条件查询第三章关系数据库语言SQL3.3 SQL的数据操纵第46页/共74页比较大小在WHERE子句的中使用比较运算符=，=，=，!=或，!，!，逻辑运算符NOT+比较运算符例8查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage=20；第三章关系数据库语言SQL3.3 SQL的数据操纵第47页/共74页确定范围使用谓词BETWEENANDNOTBETWEENAND例10查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；相当于若干相当于若干AND的缩写的缩写第三章关系数据库语言SQL3.3 SQL的数据操纵第48页/共74页确定范围例11查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；第三章关系数据库语言SQL3.3 SQL的数据操纵第49页/共74页确定集合使用谓词IN,NOTIN：用逗号分隔的一组取值例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN(IS，MA，CS);相当与若干相当与若干OR的缩写的缩写第三章关系数据库语言SQL3.3 SQL的数据操纵第50页/共74页字符串匹配NOTLIKEESCAPEDB2中，下划线_表示匹配任何单个字符百分号%表示匹配任何字符串当用户要查询的字符串本身含有%或_时，要使用ESCAPE短语对通配符进行转义第三章关系数据库语言SQL3.3 SQL的数据操纵第51页/共74页字符串匹配匹配模板为固定字符串例14查询学号为95001的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE95001；等价于：SELECT*FROMStudentWHERESno=95001；第三章关系数据库语言SQL3.3 SQL的数据操纵第52页/共74页字符串匹配匹配模板为含通配符的字符串例15查询所有（不）姓刘学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMStudentWHERESname（NOT）LIKE刘%；第三章关系数据库语言SQL3.3 SQL的数据操纵第53页/共74页匹配模板为含通配符的字符串（续）例16查询姓欧阳且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameLIKE欧阳_；第三章关系数据库语言SQL3.3 SQL的数据操纵第54页/共74页字符串匹配匹配模板为含通配符的字符串（续）例17查询名字中第2个字为阳字的学生的姓名和学号。SELECTSname，SnoFROMStudentWHERESnameLIKE_阳%；第三章关系数据库语言SQL3.3 SQL的数据操纵第55页/共74页字符串匹配使用换码字符将通配符转义为普通字符例19查询DB_Design课程的课程号和学分。SELECTCno，CcreditFROMCourseWHERECnameLIKEDB_DesignESCAPE第三章关系数据库语言SQL3.3 SQL的数据操纵第56页/共74页涉及空值的查询使用谓词ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替例21某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECTSno，CnoFROMSCWHEREGradeISNULL；第三章关系数据库语言SQL3.3 SQL的数据操纵第57页/共74页涉及空值的查询例22查所有有成绩的学生学号和课程号。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；第三章关系数据库语言SQL3.3 SQL的数据操纵第58页/共74页多重条件查询用逻辑运算符AND和OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词NOTINNOTBETWEENAND第三章关系数据库语言SQL3.3 SQL的数据操纵第59页/共74页多重条件查询例23查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept=CSANDSage3；第三章关系数据库语言SQL3.3 SQL的数据操纵第69页/共74页使用HAVING短语例32查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数SELECTSno,COUNT(*)FROMSCWHEREGrade=90GROUPBYSnoHAVINGCOUNT(*)=3;HAVING是选择分组的条件，且是选择分组的条件，且必须和必须和GROUPBY一起使用一起使用WHERE是选择记录的条件；是选择记录的条件；第三章关系数据库语言SQL3.3 SQL的数据操纵第70页/共74页使用HAVING短语只有满足HAVING短语指定条件的组才输出HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组HAVING短语作用于组，从中选择满足条件的组第三章关系数据库语言SQL3.3 SQL的数据操纵第71页/共74页使用聚合函数对数据进行汇总和统计n聚合函数常见的几种用法：n nSUMSUM(ALL|DISTINCTexpression)返回表达式中所有值的和，或只返回DISTINCT值。SUM只能用于数字列。空值将被忽略。n nCOUNTCOUNT(ALL|DISTINCTexpression|*)返回组中项目的数量。n nCOUNT(*)COUNT(*)返回组中项目的数量，这些项目包括NULL值和重复值。n nCOUNT(ALLexpression)COUNT(ALLexpression)对组中的每一行都计算相应的expression的值，并返回非空值的expression的数量。n nCOUNT(DISTINCTexpression)COUNT(DISTINCTexpression)对组中的每一行都计算expression并返回唯一的非空值的数量。第72页/共74页使用聚合函数对数据进行汇总和统计 AVGAVG(ALL|DISTINCTexpression)返回组中值的平均值。空值将被忽略。MAXMAX(ALL|DISTINCTexpression)返回表达式的最大值 MINMIN(ALL|DISTINCTexpression)返回表达式的最小值。聚合函数一般仅能出现在SELECT和HAVING子句中。不同的DBMS系统对聚合函数有不同的扩充。使用聚合函数之后，SELECT子句中所出现的列名或列名表达式必须出现在GROUPBY子句中或者为聚合函数表达式。第73页/共74页感谢您的观看！第74页/共74页
