数据库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 基本表的修改 基本格式ALTERTABLE ADD 完整性约束 DROP ALTERCOLUMN 要修改的基本表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列名1 HAVING内部函数表达式 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 查询经过计算的值 例4 查全体学生的姓名及其出生年份 SELECTSname 2009 SageFROMStudent 输出结果 Sname2009 Sage 李勇1976刘晨1977王名1978张立1978 查询经过计算的值 例5 查询全体学生的姓名 出生年份和所有系 要求用小写字母表示所有系名 SELECTSname YearofBirth 2009 Sage ISLOWER Sdept FROMStudent 查询经过计算的值 输出结果 Sname YearofBirth 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 确定范围 使用谓词BETWEEN AND NOTBETWEEN AND 例10 查询年龄在20 23岁 包括20岁和23岁 之间的学生的姓名 系别和年龄 SELECTSname Sdept SageFROMStudentWHERESageBETWEEN20AND23 相当于若干 AND 的缩写 确定范围 例11 查询年龄不在20 23岁之间的学生姓名 系别和年龄 SELECTSname Sdept SageFROMStudentWHERESageNOTBETWEEN20AND23 确定集合 使用谓词IN NOTIN 用逗号分隔的一组取值 例12 查询信息系 IS 数学系 MA 和计算机科学系 CS 学生的姓名和性别 SELECTSname SsexFROMStudentWHERESdeptIN IS MA CS 相当与若干 OR 的缩写 字符串匹配 NOT LIKE ESCAPE DB2中 下划线 表示匹配任何单个字符百分号 表示匹配任何字符串当用户要查询的字符串本身含有 或 时 要使用ESCAPE 短语对通配符进行转义 字符串匹配 匹配模板为固定字符串 例14 查询学号为95001的学生的详细情况 SELECT FROMStudentWHERESnoLIKE 95001 等价于 SELECT FROMStudentWHERESno 95001 字符串匹配 匹配模板为含通配符的字符串 例15 查询所有 不 姓刘学生的姓名 学号和性别 SELECTSname Sno SsexFROMStudentWHERESname NOT LIKE 刘 匹配模板为含通配符的字符串 续 例16 查询姓 欧阳 且全名为三个汉字的学生的姓名 SELECTSnameFROMStudentWHERESnameLIKE 欧阳 字符串匹配 匹配模板为含通配符的字符串 续 例17 查询名字中第2个字为 阳 字的学生的姓名和学号 SELECTSname SnoFROMStudentWHERESnameLIKE 阳 字符串匹配 使用换码字符将通配符转义为普通字符 例19 查询DB Design课程的课程号和学分 SELECTCno CcreditFROMCourseWHERECnameLIKE DB Design ESCAPE 涉及空值的查询 使用谓词ISNULL或ISNOTNULL ISNULL 不能用 NULL 代替 例21 某些学生选修课程后没有参加考试 所以有选课记录 但没有考试成绩 查询缺少成绩的学生的学号和相应的课程号 SELECTSno CnoFROMSCWHEREGradeISNULL 涉及空值的查询 例22 查所有有成绩的学生学号和课程号 SELECTSno CnoFROMSCWHEREGradeISNOTNULL 多重条件查询 用逻辑运算符AND和OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词 NOT IN NOT BETWEEN AND 多重条件查询 例23 查询计算机系年龄在20岁以下的学生姓名 SELECTSnameFROMStudentWHERESdept CS ANDSage 20 对查询结果排序 使用ORDERBY子句可以按一个或多个属性列排序升序 ASC 降序 DESC 缺省值为升序当排序列含空值时ASC 排序列为空值的元组最后显示DESC 排序列为空值的元组最先显示 对查询结果排序 例24 查询选修了3号课程的学生的学号及其成绩 查询结果按分数降序排列 SELECTSno GradeFROMSCWHERECno 3 ORDERBYGradeDESC 查询结果 SnoGrade 9501095024950079295003829501082950097595014619500255 使用集函数 COUNT对一列中的值的个数记数COUNT 计算记录个数SUM对一列求和AVG对一列求平均值MAX对一列求最大值MIN对一列求最小值 使用集函数 SELECTCOUNT DISTINCTSno FROMSC 例25 求选修了课程的学生人数 重复的只记一个 对查询结果分组 使用GROUPBY子句分组细化集函数的作用对象未对查询结果分组 集函数将作用于整个查询结果对查询结果分组后 集函数将分别作用于每个组 对查询结果分组 例30 求各个课程号及相应的选课人数 SELECTCno COUNT Sno FROMSCGROUPBYCno 结果CnoCOUNT Sno 122234344433548 将表按列的值分组 列的值相同的分在一组 GROUPBY常和库函数一起使用 用于分组统计 对查询结果分组 GROUPBY子句的作用对象是查询的中间结果表分组方法 按指定的一列或多列值分组 值相等的为一组使用GROUPBY子句后 SELECT子句的列名列表中只能出现分组属性和集函数 使用HAVING短语 例31