数据库课件完整版本.ppt

1 第3章关系数据库语言3 1Access数据库查询3 2关系数据库语言SQL3 3简单的SELECT语句3 4SELECT子句 2 知识目标 了解关系数据库语言SQL的基本功能 掌握SELECT语句的基本格式 了解SQL数据定义 数据查询 数据更新及数据控制的基本含义 3 能力目标 学会使用问答式方式查询数据库方法 掌握使用SQL命令进行数据定义 数据查询 数据更新方法 熟练掌握使用SQL命令进行关系数据库的投影 选择运算 掌握使用SQL命令完成数据分组 统计 排序等数据处理功能 4 重点SELECT语句使用难点SELECT嵌套查询 重点与难点 5 1 使用查询向导建立查询建立查询有两种方法 一种是使用查询设计视图 另一种是使用查询向导 简单查询向导交叉表查询向导显示来源于表中某个字段的总计值 求和 平均或其他计算 并将它们分组 一组列在数据表的左侧 一组列在数据表的上部 例3 1 制作一个能显示每个学生的所选课程的平均成绩及每门课成绩的查询 3 1Access数据库查询 6 2 使用设计视图建立查询 例3 2 查询 计算机应用 专业的 女 学生的 成绩 75分 的选课成绩单 要求显示学号 姓名 课程名 成绩 3 使用查询进行数据统计 例3 3 查询求每个学生的平均成绩和最高成绩 7 4 其他查询操作查询通过查询进行数据的增 删 改操作 参数查询运行查询时可输入不同参数进行查询 SQL查询 8 第3章关系数据库语言3 1Access数据库查询3 2关系数据库语言SQL3 3简单的SELECT语句3 4SELECT子句 9 3 2 1SQL数据定义功能定义基本表定义视图定义索引命令CREATE DROPTABLECREATE DROPVIEWCREATE DROPINDEX 3 2关系数据库语言SQL 10 学生选课系统的关系数据模型包括以下三个关系模式 学生 学号 姓名 专业名 性别 出生日期 总学分 课程 课程号 课程名 开课学期 学时 学分 选课 学号 课程号 成绩 11 createtable 列级完整性约束条件 列级完整性约束条件 表级完整性约束条件 1 定义基本表 如果完整性约束条件涉及到该表的多个属性列 则必须定义在表级上 否则既可以定义在列级也可以定义在表级 12 列级完整性约束条件 该列上数据必须符合的条件 包含在列定义中 直接跟在该列的其他定义之后 用空格分隔 不必指定列名最常见的有 notnull该列值不能为空null该列值可以为空default该列上未定义值时的默认值unique不能有重复值primarykey定义为主键foreignkey定义为外键check检查列值所允许的范围 13 例3 4 创建学生表createtable学生 学号char 6 primarykey 姓名char 8 notnull 专业名char 10 性别char 2 default 男 出生日期date 总学分int 14 createtable选课 学号char 6 课程号char 3 notnull 成绩int constraintevent key primarykey 学号 课程号 foreignkey 学号 references学生 学号 foreignkey 课程号 references课程 课程号 15 ALTERTABLE ADD 完整性约束 ALTERCOLUMN DROP DROPCOLUMN 2 修改基本表 16 例3 5 在学生表中增加 说明 列 修改 性别 列及删除 说明 列 ALTERTABLE学生ADD说明CHAR 50 ALTERTABLE学生ALTERCOLUMN性别CHAR 1 ALTERTABLE学生DROPCOLUMN说明 17 3 删除基本表DROPTABLE 表名 例3 6 删除学生表 DROPTABLE学生 18 建立索引的目的 加快查询速度谁可以建立索引DBA或表的属主 即建立表的人 DBMS一般会自动建立以下列上的索引PRIMARYKEYUNIQUE谁维护索引DBMS自动完成使用索引DBMS自动选择是否使用索引以及使用哪些索引 4 索引的建立与删除 19 CREATEINDEXON DROPINDEX 用指定索引值的排列次序 升序 ASC 缺省值 降序 DESC 格式如下 20 1 建立索引 例3 7 为基本表学生建立一个按学号升序的索引 名为XSXH CREATEINDEXXSXHON学生 学号ASC 2 删除索引 例3 8 删除索引XSXH DROPINDEXXSXH 21 SQL的查询语句也称SELECT命令 其基本形式是SELECT FROM WHERE查询块 多个查询块可以逐层嵌套执行 SELECT命令是SQL结构化查询语言最具特色的核心语句 使用方便 查询速度快 3 2 2SQL数据查询 22 1 单表查询 例3 9 查找所有学生的全部信息 SELECT FROM学生 例3 10 查找学生马伟的专业和出生日期 SELECT姓名 专业名 出生日期FROM学生WHERE姓名 马伟 23 2 连接查询同时涉及多个表的查询称为连接查询 例3 11 查找所有选修了课程的学生姓名及专业名 SELECTDISTINCT姓名 专业名FROM学生 选课WHERE学生 学号 选课 学号 24 例3 12 找出张小雨所选课程的课程名和成绩 SELECT 张小雨所选的课程 课程名 成绩FROM学生 课程 选课WHERE学生 学号 选课 学号AND选课 课程号 课程 课程号AND姓名 张小雨 25 3 嵌套查询一个SELECT FROM WHERE语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询 26 例3 13 找出选修 数据库原理 课程的学生及专业 SELECT姓名 专业名FROM学生WHERE学号IN SELECT学号FROM选课WHERE课程号IN SELECT课程号FROM课程WHERE课程名 数据库原理 谓词IN 查找属性值属于指定集合的元组 27 1 插入数据 例3 14 向课程基本表中新加一个元组 INSERTINTO课程VALUES 205 VB程序设计 3 70 4 例3 15 向选课基本表中插入一个元组的部分字段 INSERTINTO选课 学号 课程号 VALUES 030102 203 3 2 3SQL数据更新 28 在修改命令中可以用WHERE子句限定条件 对满足条件的元组进行更新修改 若不写条件 则对所有元组更新 例3 16 将课程表中 数据库原理 改为 数据库技术与应用 UPDATE课程SET课程名 数据库技术与应用 WHERE课程名 数据库原理 例3 17 将所有课程的学时数减少4课时 UPDATE课程SET学时 学时 4 2 修改数据 29 3 删除数据删除单位是元组 不是元组的部分属性 一次可以删除一个 几个元组 以致于整个表删成空表 只保留表的结构定义 例3 18 删除 离散数学 课程 DELETEFROM课程WHERE课程名 离散数学 30 3 2 4SQL数据控制 数据控制 通过对数据库各种权限的授予或回收来管理数据库系统 权限 select alter insert delete update all授权语句的格式如下 grant权限表ontable表名to用户名 withgrantoption 语义 将对指定表的指定操作权限授予指定的用户 31 发出权限DBA数据库对象创建者 即属主Owner 拥有该权限的用户接受权限一个或多个具体用户public 全体用户 32 withgrantoption子句指定 可以再授予没有指定 不能传播不允许循环授权 33 例3 19 将查询学生表中数据的权限授予所有用户 grantselectontable学生topublic 例3 20 把查询Student表和修改学生学号的权限授给用户U4 grantupdate Sno selectontableStudenttoU4 对属性列的授权时必须明确指出相应属性列名 34 2 回收权限格式如下 revoke权限表ontable表名from用户 例3 20 回收用户liming和wangwei对基本表 学生 的更新权限 revokeupdateontable学生fromliming wangwei 35 第3章关系数据库语言3 1Access数据库查询3 2关系数据库语言SQL3 3简单的SELECT语句3 4SELECT子句 36 3 3 1select语句的基本格式 3 3简单的SELECT语句 select all distinct into新表名 from where groupby having orderby asc desc 37 select子句 指定要显示的属性列into子句 指定结果存入新表from子句 指定查询对象 表或视图 where子句 指定查询条件groupby子句 对查询结果按指定列的值分组 该属性列值相等的元组为一个组having子句 指定分组统计条件orderby子句 对查询结果表按指定列值的升序或降序排序 38 select语句的投影运算通过select语句的select子句选择表中的列组成结果表 格式 select all distinct 目标列表达式目标列表达式可为 count distinct all 属性列名表达式属性列名表达式 由属性列 聚集函数以及属性列和常量的任意算术运算 组成的运算公式 聚集函数 count sum avg max min 3 3 2select语句的投影运算 39 1 选择一个表中指定的列 例3 21 查询学生选课数据库的XS表中各个学生的姓名 专业名和总学分 select姓名 专业名 总学分fromXS 例3 22 查询XS表中专业为 计算机应用 的所有列 select fromXSwhere专业名 计算机应用 等价于 40 2 修改查询结果中的列标题 例3 23 查询XS表中 网络与通信 专业学生的学号 姓名和总学分 结果中各列的标题分别为number name和mark select学号asnumber 姓名asname 总学分asmarkfromXSwhere专业名 网络与通信 注意 当自定义的列标题中含有空格时 必须使用单引号将标题括起来 41 3 表达式及函数使用 例3 24 对XS数据表进行统计人数 求最高 低 总学分 总学分之和 总学分平均数 selectcount as学生人数 max 总学分 as最高总学分 min 总学分 as最低总学分 sum 总学分 as总学分之和 avg 总学分 as总学分的平均数fromxs 42 sum和avg函数语法格式如下 sum avg all distinct expression expression 常量 列 函数或表达式 其数据类型只能是数字型 sum avg忽略null值 例3 25 求选修101课程的学生的平均成绩 selectavg 成绩 as课程101平均成绩fromXKwhere课程号 101 43 max和min函数语法格式如下 max min expression expression 常量 列 函数或表达式 其数据类型可以是数字 字符和时间日期 例3 26 求选修101课程的学生的最高 低 分 selectmax 成绩 as课程101的最高分 min 成绩 as课程101的最低分fromXKwhere课程号 101 44 count函数 统计组中满足条件的行数或总行数语法格式如下 count all distinct 统计元组个数count all distinct 列名 统计一列中值的个数count忽略null值 例3 27 求选修了课程的学生总人数 selectcount distinct学号 fromXK 45 4 限制结果集返回行数如果select语句返回的结果集的行数非常多 可以在select子句中使用top选项限制其返回的行数 top选项的基本格式如下 topn percent n 正整数 返回查询结果集的前n行 percent 返回结果集的前n 行 例3 28 显示输出学生选课数据库的XK表的前5行 selecttop5 fromXK 46 Access数据库中 通过在select语句中使用where子句指定搜索条件来实现关系的选择运算 在搜索条件中可以使用比较运算符和逻辑运算符构成的条件 比较运算符用于比较两个表达式值 分别是 逻辑运算符用于比较两个逻辑表达式的值 分别是 not and or 3 3 3select语句的选择运算 47 例3 29 查询学生选课数据库XS表中总学分在50及以上的计算机应用专业的学生信息 select fromXSwhere总学分 50and专业名 计算机应用 48 select的查询对象由from子句指定 其格式为 fromtable source 其中table source指出了要查询的表或视图 格式为 table name astable alias view name astable alias rowset function astable alias derived tableastable alias column alias 3 3 4指定数据表或视图 49 例3 30 查找选修了101号课程的学生的学号和成绩 selectdistinctcj101 学号 姓名 课程号 成绩fromXS XKascj101where课程号 101 andcj101 学号 XS 学号 50 例3 31 在XS表中查找男学生的姓名 专业名和出生日期 selectm 姓名 m 专业名 m 出生日期 m 性别from select fromXSwhere性别 男 asm 51 第3章关系数据库语言3 1Access数据库查询3 2关系数据库语言SQL3 3简单的SELECT语句3 4SELECT子句 52 3 4select的子句 1 groupby子句将查询结果按某一列或多列的值分组 值相等的为一组 其格式为 groupby列名分组的目的 为了细化聚集函数的作用对象未对查询结果分组 聚集函数将作用于整个查询结果 分组后聚集函数将作用于每一个组 即每一组都有一个聚集函数 使用groupby子句后 select子句中只能包含groupby中指出的列或聚集函数 53 例3 32 将学生选课数据库中各专业名输出select专业名fromXSgroupby专业名 例3 33 求学生选课数据库中各专业的学生数select专业名 count as学生数fromXSgroupby专业名 54 例3 34 求被选修的各门课程的平均成绩和选修该课程的人数 select课程号 avg 成绩 as平均成绩 count 学号 as选修人数fromXKgroupby课程号 例3 35 在学生选课数据库上产生一个结果集 包括每个专业的男生人数 女生人数 select专业名 性别 count as人数fromXSgroupby专业名 性别 55 2 having短语如果使用groupby子句和聚合函数对数据进行分组后 还要求按一定的条件对这些组进行筛选 最终只输出满足指定条件的组 则可以使用having短语指定筛选条件 having子句的格式为 having条件表达式条件表达式与where子句的条件表达式类似 并且可以使用聚集函数 56 例3 36 查找学生选课数据库中平均成绩在80分以上的学生的学号和平均成绩 select学号 avg 成绩 as平均成绩fromXKgroupby学号havingavg 成绩 80 57 例3 37 查找选修课程至少2门且成绩都在75分以上的