数据库sql基础知识大全课件.ppt

数据库原理,主讲：赵海霞,河南科技大学电信学院,第三章关系数据库语言SQL,概述SQL的数据定义SQL的数据操纵SQL的视图定义SQL的数据控制嵌入式SQL,第三章关系数据库语言SQL,概述,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页）,SQL语言的特点,综合统一集DDL、DML、DCL为一体实体和联系都是关系，因此每种操作只需一种操作符高度非过程化面向集合的操作方式以同一种语法结构提供两种使用方式（交互式和嵌入式）语言简捷，易学易用数据定义CREATE、DROP、ALTER数据查询SELECT数据更新INSERT、UPDATE、DELETE数据控制GRANT、REVOKE,SQL语言的特点,支持三级模式结构视图外模式基本表（的集合）模式存储文件和索引内模式,SQL数据库体系结构,SQL的组成,数据定义语言(TheDataDefinitionLanguage,DDL)数据操作语言(TheDataManipulationLanguage,DML)嵌入式和动态SQL事务管理安全性管理触发器和高级完整性约束客户服务器执行和远程数据库存取高级特性,SQL的数据定义,定义和修改基本表（定义模式中的关系）：CREATETABLEDROPTABLEALTERTABLE定义视图（定义外模式）：CREATEVIEWDROPVIEW定义索引（定义内模式）：CREATEINDEXDROPINDEX,基本表的定义,基本格式CREATETABLE（，）；：所要定义的基本表的名字：组成该表的各个属性（列）：涉及相应属性列的完整性约束条件：涉及一个或多个属性列的完整性约束条件,例1建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15)；,基本表的定义,基本表的定义,说明：SQL支持空值的概念。允许空值的列未输入数据时系统自动置为空值。SQL支持的数据类型随系统不同而有所差异，但一般都有：全字长整型、半字长整型、定点实型、浮点实型、CHAR（n）、VARCHAR（n）、TEXT、DATE,基本表的定义,常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束FOREIGNKEYcheck约束思考：PRIMARYKEY与UNIQUE的区别？Primarykey在建立的时候会默认地建立此field的索引，且此primarykey可以作为作为另外的表的foreignkey；再者primarykey跟unique的区别是Primarykey一定是notnull，而unique则没有此限制,SQLServer2005中的数据类型,精确数字,近似数字,日期和时间,字符串,Unicode字符串,二进制字符串,其他数据类型,例2建立一个“课程”表C，它由课程号Cno，课程名称Cname，教师Teacher组成，其中Cno为主码。CREATETABLEC(CnoCHAR(5),CnameCHAR(10),TeacherCHAR(10),Primarykey(Cno);或像书上写的将Primarykey直接定义在属性列后。,基本表的定义,例3建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),Gradeint,Primarykey(Sno,Cno),FOREIGNKEY(Sno)REFERENCESS(Sno),FOREIGNKEY(Cno)REFERENCESC(Cno);,基本数据类型,(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）,基本表的删除,基本格式DROPTABLE表名；示例DROPTABLES；,基本表的修改,基本格式ALTERTABLEADD完整性约束DROPALTERCOLUMN；：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件ALTERCOLUMN子句：用于修改列名和数据类型,基本表的修改,增加列基本格式ALTERTABLE表名ADD列名类型；示例ALTERTABLESADDADDRESSVARCHAR(30)修改列基本格式ALTERTABLE表名ALTERCOLUMN列名类型；ALTERTABLESALTERCOLUMNSASMALLINT；删除列基本格式ALTERTABLE表名DROP列名；ALTERTABLESDROPUNIQUE(Sn)；,例子修改表,ALTERTABLEstudentADDsnoCHAR(5)NULL-向student表中添加一列snoALTERTABLEstudent-修改sno的定义保障此列不为空MODIFYsnoCHAR(5)NOTNULLALTERTABLEstudent-向student表添加主键约束ADDPRIMARYKEY(sno)ALTERTABLEstudent-从student表删除列sexDROPCOLUMNsex,例子修改表,-向study表中添加外键约束ALTERTABLEstudyADDCONSTRAINTfkcnoFOREIGNKEY(cno)REFERENCEScourse(cno)ONDELETECASCADEONUPDATECASCADE-向study表中添加外键约束ALTERTABLEstudyADDCONSTRAINTfksnoFOREIGNKEY(sno)REFERENCESstudent(sno)ONDELETECASCADEONUPDATECASCADE,视图的定义和修改,基本操作CREATEVIEWDROPVIEW后面详细讨论,索引的建立和删除,建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引PRIMARYKEYUNIQUE维护索引DBMS自动完成使用索引DBMS自动选择是否使用索引以及使用哪些索引,索引的建立和删除,索引的结构RDBMS中，索引一般采用B+树或HASH索引实现，具体由RDBMS决定。B+树具有动态平衡的优点HASH索引查找速度快。索引是关系数据库的内部实现技术，属内模式范畴。用户创建索引时，可创建唯一索引、非唯一索引或聚簇索引。,在SQL86和SQL89标准中，基本表没有关键码概念，可以用索引机制来弥补。索引属于物理存储的路径概念，而不是逻辑的概念。在定义基本表时，还要定义索引，就把数据库的物理结构和逻辑结构混在一块了。因此在SQL2中引入了主码（主键）的概念，用户在创建基本表时用主码子句Primarykey直接定义主码。但至今大多数DBMS仍使用索引机制，有索引创建和撤销语句，其功能仅限于查询时起作用。,索引的建立,索引建立的基本格式,聚簇索引,Unique索引表明此索引的每个索引值只对应唯一的数据记录Cluster索引又称聚簇索引，必须维护表中行的物理存储顺序和索引顺序一致，经常进行更新操作的表不宜建立聚簇索引。,索引的建立,例、为学生-课程数据库中的S，C，SC三个表建立索引。其中S表按学号升序建唯一索引，C表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXSsnoONS(Sno)；CREATEUNIQUEINDEXCcnoONC(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；,索引的建立,唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束,索引的建立,聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATECLUSTERINDEXSsnameONS(SN)；在S表的SN（姓名）列上建立一个聚簇索引，而且S表中的记录将按照SN值的升序存放,索引的建立,在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可以提高查询效率聚簇索引的适用范围很少对基表进行增删操作很少对其中的变长列进行修改操作,索引的删除,索引删除的基本格式DROPINDEX索引名删除索引时，系统会从数据字典中删去有关该索引的描述。,SQL的数据操纵,SQL的数据查询（检索）SELECTSQL的数据更新（增、删、改）INSERT，DELETE，UPDATE,SQL的数据查询,一般格式,SELECT目标列FROM基本表（或视图）WHERE条件表达式GROUPBY列名1HAVING内部函数表达式ORDERBY列名2,SQL的数据查询,常用格式(SELECT-FROM-WHERE句型)SELECTA1,A2,AnFROMR1,R2,RmWHEREF其中，条件表达式F可使用下列操作符：算术比较运算符（,=,=,）逻辑运算符（AND,OR,NOT）集合运算符（UNION,INTERSECT,EXCEPT）集合成员资格运算符（IN,NOTIN）谓词(EXISTS,ALL,SOME,UNIQUE)聚合函数(AVG,MIN,MAX,SUM,COUNT)嵌套的SELECT语句,示例数据库,学生-课程数据库学生表：Student(Sno，Sname，Ssex，Sage，Sdept)课程表：Course(Cno，Cname，Cpno，Ccredit)学生选课表：SC(Sno，Cno，Grade),一、单表查询,查询仅涉及一个表，是一种最简单的查询操作选择表中的若干列选择表中的若干元组对查询结果排序使用集函数对查询结果分组,查询指定列,例1查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；例2查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；,查询全部列,例3查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；,2019/12/14,39,可编辑,查询经过计算的值,例4查全体学生的姓名及其出生年份。SELECTSname，2009-SageFROMStudent；输出结果：Sname2009-Sage-李勇1976刘晨1977王名1978张立1978,查询经过计算的值,例5查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname，YearofBirth:，2009-Sage，ISLOWER(Sdept)FROMStudent；,查询经过计算的值,输出结果：SnameYearofBirth:2009-SageISLOWER(Sdept)-李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is,使用列别名改变查询结果的列标题,SELECTSnameNAME，YearofBirth:BIRTH，2009-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent；输出结果：NAMEBIRTHBIRTHDAYDEPARTMENT-李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is,选择表中的若干元组,消除取值重复的行查询满足条件的元组,1.消除取值重复的行,在SELECT子句中使用DISTINCT短语假设SC表中有下列数据SnoCnoGrade-9500119295001285950013889500229095002380,ALL与DISTINCT,例6查询选修了课程的学生学号。(1)SELECTSnoFROMSC;或(默认ALL)SELECTALLSnoFROMSC;结果：Sno-9500195001950019500295002,(2)SELECTDISTINCTSnoFROMSC;结果：Sno-9500195002,ALL与DISTINCT,注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确写法SELECTDISTINCTCno，GradeFROMSC;,2.查询满足条件的元组,比较大小确定范围确定集合字符串匹配涉及空值的查询多重条件查询,比较大小,在WHERE子句的中使用比较运算符=，=，!，!=20；,确定范围,使用谓词BETWEENANDNOTBETWEENAND例10查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；,相当于若干AND的缩写,确定范围,例11查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；,确定集合,使用谓词IN,NOTIN：用逗号分隔的一组取值例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN(IS，MA，CS);,相当与若干OR的缩写,字符串匹配,NOTLIKEESCAPEDB2中，下划线_表示匹配任何单个字符百分号%表示匹配任何字符串当用户要查询的字符串本身含有%或_时，要使用ESCAPE短语对通配符进行转义,字符串匹配,匹配模板为固定字符串例14查询学号为95001的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE95001；等价于：SELECT*FROMStudentWHERESno=95001；,字符串匹配,匹配模板为含通配符的字符串例15查询所有（不）姓刘学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMStudentWHERESname（NOT）LIKE刘%；,匹配模板为含通配符的字符串（续）例16查询姓欧阳且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameLIKE欧阳_；,字符串匹配,匹配模板为含通配符的字符串（续）例17查询名字中第2个字为阳字的学生的姓名和学号。SELECTSname，SnoFROMStudentWHERESnameLIKE_阳%；,字符串匹配,使用换码字符将通配符转义为普通字符例19查询DB_Design课程的课程号和学分。SELECTCno，CcreditFROMCourseWHERECnameLIKEDB_DesignESCAPE,涉及空值的查询,使用谓词ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替例21某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECTSno，CnoFROMSCWHEREGradeISNULL；,涉及空值的查询,例22查所有有成绩的学生学号和课程号。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；,多重条件查询,用逻辑运算符AND和OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词NOTINNOTBETWEENAND,多重条件查询,例23查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept=CSANDSage3；,使用HAVING短语,例32查询有3门以上课程是90分以上的学生的学号及（90分以