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

Spring,2009.*数据库原理主讲：赵海霞河南科技大学电信学院Spring,2009.*第三章关系数据库语言SQL 概述 SQL的数据定义 SQL的数据操纵 SQL的视图定义 SQL的数据控制 嵌入式SQL第三章关系数据库语言SQLSpring,2009.*概述 SQL（StructuredQueryLanguage）是1974年由Boyde和Chamberlin提出的 1974年IBM的SystemR 1979年Oracle 1982年IBM的DB2 1984年Sybase 1986年10月ANSI公布SQL-86 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 1989年ISO公布SQL-89标准（120页） 1992年公布SQL-92标准（622页） 1999年公布SQL-99标准（1700页） 2003年公布SQL-2003标准（3600页）*ANSI:美国国家标准局；ISO：国际标准化组织。Spring,2009.*SQL语言的特点 综合统一 集DDL、DML、DCL为一体 实体和联系都是关系，因此每种操作只需一种操作符 高度非过程化 面向集合的操作方式 以同一种语法结构提供两种使用方式（交互式和嵌入式） 语言简捷，易学易用 数据定义CREATE、DROP、ALTER 数据查询SELECT 数据更新INSERT、UPDATE、DELETE 数据控制GRANT、REVOKESpring,2009.*SQL语言的特点 支持三级模式结构 视图外模式 基本表（的集合）模式 存储文件和索引内模式Spring,2009.*SQL数据库体系结构Spring,2009.*SQL的组成数据定义语言(TheDataDefinitionLanguage,DDL)数据操作语言(TheDataManipulationLanguage,DML)嵌入式和动态SQL事务管理安全性管理触发器和高级完整性约束客户服务器执行和远程数据库存取高级特性Spring,2009.*SQL的数据定义 定义和修改基本表（定义模式中的关系）： CREATETABLE DROPTABLE ALTERTABLE 定义视图（定义外模式）： CREATEVIEW DROPVIEW 定义索引（定义内模式）： CREATEINDEX DROPINDEXSpring,2009.*基本表的定义 基本 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 CREATETABLE<表名>（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；<表名>：所要定义的基本表的名字<列名>：组成该表的各个属性（列）<列级完整性约束条件>：涉及相应属性列的完整性约束条件<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件Spring,2009.*[例1]建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15))；基本表的定义Spring,2009.*基本表的定义 说明： SQL支持空值的概念。允许空值的列未输入数据时系统自动置为空值。 SQL支持的数据类型随系统不同而有所差异，但一般都有： 全字长整型、半字长整型、定点实型、浮点实型、CHAR（n）、 VARCHAR（n）、TEXT、DATE*用户可使用“CREATEDOMAIN”语句定义新的域Spring,2009.*基本表的定义 常用完整性约束 主码约束：PRIMARYKEY 唯一性约束：UNIQUE 非空值约束：NOTNULL 参照完整性约束FOREIGNKEY check约束思考：PRIMARYKEY与UNIQUE的区别？ Primarykey在建立的时候会默认地建立此field的索引，且此primarykey可以作为作为另外的表的foreignkey； 再者primarykey跟unique的区别是Primarykey一定是notnull，而unique则没有此限制Spring,2009.*SQLServer2005中的数据类型精确数字近似数字日期和时间字符串Unicode 字符串二进制字符串其他数据类型 bigint decimal int numeric smallint money tinyint smallmoney bit   float real datetime smalldatetime char text varchar   nchar ntext nvarchar   binary image varbinary   cursor timestamp sql_variant uniqueidentifier table xmlSpring,2009.* [例2]建立一个“课程”表C，它由课程号Cno，课程名称Cname，教师Teacher组成，其中Cno为主码。 CREATETABLEC( CnoCHAR(5), CnameCHAR(10), TeacherCHAR(10), Primarykey(Cno)); 或像书上写的将Primarykey直接定义在属性列后。Spring,2009.*基本表的定义 [例3]建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。 CREATETABLESC( SnoCHAR(5), CnoCHAR(3), Gradeint, Primarykey(Sno,Cno), FOREIGNKEY(Sno)REFERENCESS(Sno), FOREIGNKEY(Cno)REFERENCESC(Cno) );Spring,2009.*基本数据类型 (1)数值型（DB2) SMALLINT半字长二进制整数 INTEGER全字长二进制整数 DECIMAL(P[,q]）或者DEC（p[,q]) 压缩十进制数，共p位，其中小数点后q位 FLOAT双字长浮点数 （2)字符串型 CHARTER(n)或CHAR(n) VARCHAR(n) (3)时间型 DATE TIME (4)位串型 BIT（n）Spring,2009.*基本表的删除 基本格式DROPTABLE表名； 示例DROPTABLES；Spring,2009.*基本表的修改 基本格式ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][ALTERCOLUMN<列名><数据类型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件ALTERCOLUMN子句：用于修改列名和数据类型Spring,2009.*基本表的修改 增加列基本格式ALTERTABLE表名ADD列名类型； 示例ALTERTABLESADDADDRESSVARCHAR(30) 修改列基本格式 ALTERTABLE表名ALTERCOLUMN列名类型； ALTERTABLESALTERCOLUMNSASMALLINT； 删除列基本格式 ALTERTABLE表名DROP列名； ALTERTABLESDROPUNIQUE(Sn)；Spring,2009.*例子修改表ALTERTABLEstudentADDsnoCHAR(5)NULL--向student表中添加一列sno ALTERTABLEstudent--修改sno的定义保障此列不为空 MODIFYsnoCHAR(5)NOTNULL ALTERTABLEstudent--向student表添加主键约束 ADDPRIMARYKEY(sno) ALTERTABLEstudent--从student表删除列sex DROPCOLUMNsexSpring,2009.*例子修改表--向study表中添加外键约束ALTERTABLEstudyADDCONSTRAINTfkcnoFOREIGNKEY(cno)REFERENCEScourse(cno)ONDELETECASCADEONUPDATECASCADE--向study表中添加外键约束ALTERTABLEstudy ADDCONSTRAINTfksnoFOREIGNKEY(sno)REFERENCESstudent(sno)ONDELETECASCADEONUPDATECASCADESpring,2009.*视图的定义和修改 基本操作 CREATEVIEW DROPVIEW 后面详细讨论Spring,2009.*索引的建立和删除 建立索引是加快查询速度的有效手段 建立索引 DBA或表的属主（即建立表的人）根据需要建立 有些DBMS自动建立以下列上的索引 PRIMARYKEY UNIQUE 维护索引 DBMS自动完成  使用索引 DBMS自动选择是否使用索引以及使用哪些索引Spring,2009.*索引的建立和删除 索引的结构RDBMS中，索引一般采用B+树或HASH索引实现，具体由RDBMS决定。B+树具有动态平衡的优点HASH索引查找速度快。索引是关系数据库的内部实现技术，属内模式范畴。用户创建索引时，可创建唯一索引、非唯一索引或聚簇索引。Spring,2009.* 在SQL86和SQL89标准中，基本表没有关键码概念，可以用索引机制来弥补。 索引属于物理存储的路径概念，而不是逻辑的概念。 在定义基本表时，还要定义索引，就把数据库的物理结构和逻辑结构混在一块了。 因此在SQL2中引入了主码（主键）的概念，用户在创建基本表时用主码子句Primarykey直接定义主码。 但至今大多数DBMS仍使用索引机制，有索引创建和撤销语句，其功能仅限于查询时起作用。Spring,2009.*索引的建立 索引建立的基本格式聚簇索引Unique索引表明此索引的每个索引值只对应唯一的数据 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 Cluster索引又称聚簇索引，必须维护表中行的物理存储顺序和索引顺序一致，经常进行更新操作的表不宜建立聚簇索引。Spring,2009.*索引的建立例、为学生-课程数据库中的S，C，SC三个表建立索引。其中S表按学号升序建唯一索引，C表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXSsnoONS(Sno)；CREATEUNIQUEINDEXCcnoONC(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；Spring,2009.*索引的建立 唯一值索引 对于已含重复值的属性列不能建UNIQUE索引 对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束 Spring,2009.*索引的建立 聚簇索引 建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATECLUSTERINDEXSsnameONS(SN)；在S表的SN（姓名）列上建立一个聚簇索引，而且S表中的记录将按照SN值的升序存放Spring,2009.*索引的建立 在一个基本表上最多只能建立一个聚簇索引 聚簇索引的用途：对于某些类型的查询，可以提高查询效率 聚簇索引的适用范围 很少对基表进行增删操作 很少对其中的变长列进行修改操作Spring,2009.*索引的删除 索引删除的基本格式DROPINDEX索引名 删除索引时，系统会从数据字典中删去有关该索引的描述。Spring,2009.*SQL的数据操纵 SQL的数据查询（检索）SELECT SQL的数据更新（增、删、改）INSERT，DELETE，UPDATESpring,2009.*SQL的数据查询 一般格式SELECT目标列FROM基本表（或视图）[WHERE条件表达式][GROUPBY列名1[HAVING内部函数表达式]][ORDERBY列名2]Spring,2009.*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语句Spring,2009.*示例数据库学生-课程数据库 学生表：Student(Sno，Sname，Ssex，Sage，Sdept) 课程表：Course(Cno，Cname，Cpno，Ccredit) 学生选课表：SC(Sno，Cno，Grade)Spring,2009.*一、单表查询 查询仅涉及一个表，是一种最简单的查询操作 选择表中的若干列 选择表中的若干元组 对查询结果排序 使用集函数 对查询结果分组Spring,2009.* 查询指定列[例1]查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；  [例2]查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；Spring,2009.* 查询全部列[例3]查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；Spring,2009.***可编辑Spring,2009.* 查询经过计算的值[例4]查全体学生的姓名及其出生年份。SELECTSname，2009-SageFROMStudent； 输出结果：Sname2009-Sage----------------------李勇1976刘晨1977王名1978张立1978Spring,2009.* 查询经过计算的值[例5]查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。 SELECTSname，'YearofBirth:'，2009-Sage， ISLOWER(Sdept) FROMStudent；Spring,2009.* 查询经过计算的值输出结果：Sname'YearofBirth:'2009-SageISLOWER(Sdept)----------------------------------------------李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977isSpring,2009.* 使用列别名改变查询结果的列标题SELECTSnameNAME，'YearofBirth:’BIRTH，2009-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent；输出结果：NAMEBIRTHBIRTHDAYDEPARTMENT------------------------------------------------------李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977isSpring,2009.*选择表中的若干元组 消除取值重复的行 查询满足条件的元组Spring,2009.*1.消除取值重复的行 在SELECT子句中使用DISTINCT短语假设SC表中有下列数据 SnoCnoGrade---------------------9500119295001285950013889500229095002380Spring,2009.*ALL与DISTINCT[例6]查询选修了课程的学生学号。(1)SELECTSnoFROMSC; 或(默认ALL)SELECTALLSnoFROMSC; 结果：Sno-------9500195001950019500295002(2)SELECTDISTINCTSnoFROMSC;  结果：Sno-------9500195002Spring,2009.*ALL与DISTINCT 注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩 错误写法SELECTDISTINCTCno，DISTINCTGradeFROMSC; 正确写法SELECTDISTINCTCno，GradeFROMSC; Spring,2009.*2.查询满足条件的元组比较大小确定范围确定集合字符串匹配涉及空值的查询多重条件查询Spring,2009.* 比较大小在WHERE子句的<比较条件>中使用比较运算符 =，>，<，>=，<=，!=或<>，!>，!<， 逻辑运算符NOT+比较运算符[例8]查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage<20；或 SELECTSname，Sage FROMStudent WHERENOTSage>=20；Spring,2009.* 确定范围使用谓词BETWEEN…AND…NOTBETWEEN…AND…[例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；相当于若干´AND´的缩写Spring,2009.* 确定范围[例11]查询年龄不在20~23岁之间的学生姓名、系别和年龄。 SELECTSname，Sdept，Sage FROMStudent WHERESageNOTBETWEEN20AND23；Spring,2009.* 确定集合使用谓词IN<值表>,NOTIN<值表><值表>：用逗号分隔的一组取值[例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');相当与若干´OR´的缩写Spring,2009.* 字符串匹配 [NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’] DB2中，下划线´_´表示匹配任何单个字符百分号´%´表示匹配任何字符串 当用户要查询的字符串本身含有%或_时，要使用ESCAPE'<换码字符>'短语对通配符进行转义Spring,2009.* 字符串匹配匹配 模板 个人简介word模板免费下载关于员工迟到处罚通告模板康奈尔office模板下载康奈尔 笔记本 模板 下载软件方案模板免费下载 为固定字符串[例14]查询学号为95001的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE'95001'；等价于： SELECT*FROMStudentWHERESno='95001'；Spring,2009.* 字符串匹配匹配模板为含通配符的字符串[例15]查询所有（不）姓刘学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMStudentWHERESname（NOT）LIKE‘刘%’；Spring,2009.*匹配模板为含通配符的字符串（续）[例16]查询姓"欧阳"且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameLIKE'欧阳__'；Spring,2009.* 字符串匹配匹配模板为含通配符的字符串（续）[例17]查询名字中第2个字为"阳"字的学生的姓名和学号。SELECTSname，SnoFROMStudentWHERESnameLIKE'__阳%'；Spring,2009.* 字符串匹配使用换码字符将通配符转义为普通字符[例19]查询DB_Design课程的课程号和学分。SELECTCno，CcreditFROMCourseWHERECnameLIKE'DB\_Design'ESCAPE'\'Spring,2009.* 涉及空值的查询 使用谓词ISNULL或ISNOTNULL “ISNULL”不能用“=NULL”代替[例21]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECTSno，CnoFROMSCWHEREGradeISNULL；Spring,2009.* 涉及空值的查询[例22]查所有有成绩的学生学号和课程号。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；Spring,2009.* 多重条件查询用逻辑运算符AND和OR来联结多个查询条件 AND的优先级高于OR 可以用括号改变优先级可用来实现多种其他谓词 [NOT]IN [NOT]BETWEEN…AND…Spring,2009.* 多重条件查询[例23]查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；Spring,2009.*对查询结果排序使用ORDERBY子句 可以按一个或多个属性列排序 升序：ASC；降序：DESC；缺省值为升序当排序列含空值时 ASC：排序列为空值的元组最后显示 DESC：排序列为空值的元组最先显示Spring,2009.*对查询结果排序[例24]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；Spring,2009.*查询结果SnoGrade--------------9501095024950079295003829501082950097595014619500255Spring,2009.*使用集函数 COUNT对一列中的值的个数记数 COUNT（*）计算记录个数 SUM对一列求和 AVG对一列求平均值 MAX对一列求最大值 MIN对一列求最小值Spring,2009.*使用集函数SELECTCOUNT（DISTINCTSno）FROMSC；[例25]求选修了课程的学生人数。重复的只记一个Spring,2009.*对查询结果分组使用GROUPBY子句分组 细化集函数的作用对象 未对查询结果分组，集函数将作用于整个查询结果 对查询结果分组后，集函数将分别作用于每个组Spring,2009.*对查询结果分组[例30]求各个课程号及相应的选课人数。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；  结果 CnoCOUNT(Sno) 122 234 344 433 548将表按列的值分组，列的值相同的分在一组。GROUPBY常和库函数一起使用，用于分组统计。Spring,2009.*对查询结果分组 GROUPBY子句的作用对象是查询的中间结果表 分组方法：按指定的一列或多列值分组，值相等的为一组 使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数Spring,2009.*使用HAVING短语[例31]查询选修了3门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3；Spring,2009.*使用HAVING短语[例32]查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数SELECTSno,COUNT(*)FROMSCWHEREGrade>=90GROUPBYSnoHAVINGCOUNT(*)>=3;HAVING是选择分组的条件，且必须和GROUPBY一起使用WHERE是选择记录的条件；Spring,2009.*使用HAVING短语 只有满足HAVING短语指定条件的组才输出 HAVING短语与WHERE子句的区别：作用对象不同 WHERE子句作用于基表或视图，从中选择满足条件的元组 HAVING短语作用于组，从中选择满足条件的组Spring,2009.*使用聚合函数对数据进行汇总和统计 聚合函数常见的几种用法： SUM([ALL|DISTINCT]expression)返回表达式中所有值的和，或只返回DISTINCT值。SUM只能用于数字列。空值将被忽略。 COUNT([ALL|DISTINCT]expression]|*)返回组中项目的数量。 COUNT(*)返回组中项目的数量，这些项目包括NULL值和重复值。 COUNT(ALLexpression)对组中的每一行都计算相应的expression的值，并返回非空值的expression的数量。 COUNT(DISTINCTexpression)对组中的每一行都计算expression并返回唯一的非空值的数量。Spring,2009.*使用聚合函数对数据进行汇总和统计 AVG([ALL|DISTINCT]expression)返回组中值的平均值。空值将被忽略。 MAX([ALL|DISTINCT]expression)返回表达式的最大值 MIN([ALL|DISTINCT]expression)返回表达式的最小值。聚合函数一般仅能出现在SELECT和HAVING子句中。不同的DBMS系统对聚合函数有不同的扩充。使用聚合函数之后，SELECT子句中所出现的列名或列名表达式必须出现在GROUPBY子句中或者为聚合函数表达式。Spring,2009.***可编辑*ANSI:美国国家标准局；ISO：国际标准化组织。*用户可使用“CREATEDOMAIN”语句定义新的域