数据库课件第四章查询精.ppt

《数据库课件第四章查询精.ppt》由会员分享，可在线阅读，更多相关《数据库课件第四章查询精.ppt（78页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Database Application (Access)Database Application (Access)首都经济贸易大学 信息学院 计算机基础课程管理组信息学院Information CollageInformation Collage数据库课件第四章查询第1页，本讲稿共78页2建立数据库的目的是更好地管理和使用其中的数据，建立数据库的目的是更好地管理和使用其中的数据，获取有用的信息。获取有用的信息。4.1 认识查询认识查询筛选筛选/统计统计数据维护数据维护(增删改增删改)第2页，本讲稿共78页34.1 认识查询认识查询查询的要素：查询的要素：查询的内容查询的内容(结果列结果列)数

2、据源：存放数据的表数据源：存放数据的表查询条件查询条件查询的结果往往也是一个数据记录的集合，即关系查询的结果往往也是一个数据记录的集合，即关系“李涛”的考试成绩？男女同学的人数？第3页，本讲稿共78页44.1 认识查询认识查询ACCESSACCESS查询类型：查询类型：选择查询选择查询操作查询（删除、追加、更新、生成表）操作查询（删除、追加、更新、生成表）交叉表查询交叉表查询参数查询参数查询SQLSQL查询查询第4页，本讲稿共78页54.2 选择查询选择查询ACCESSACCESS查询方法：查询方法：使用查询向导使用查询向导使用查询设计器自定义查询使用查询设计器自定义查询步骤：步骤：1 1。添

3、加数据源。添加数据源-表：表：菜单：菜单：“查询查询”-“显示表显示表”2 2。选内容。选内容-字段字段3 3。填条件（可选）。填条件（可选）4 4。设置排序（可选）。设置排序（可选）第5页，本讲稿共78页64.2 选择查询选择查询数据源查询内容条件步骤：步骤：1 1。添加数据源。添加数据源-表：表：菜单：菜单：“查询查询”-“显示表显示表”2 2。选内容。选内容-字段字段3 3。填条件（可选）。填条件（可选）4 4。设置排序（可选）。设置排序（可选）第6页，本讲稿共78页74.2 选择查询选择查询查询的三种视图：查询的三种视图：（见（见“视图视图”菜单）菜单）设计视图设计视图数据表视图数据表

4、视图SQLSQL视图视图第7页，本讲稿共78页84.2 选择查询选择查询设置查询条件：设置查询条件：运算符运算符算术运算符：算术运算符：+-*/+-*/关系运算符：关系运算符：=逻辑运算符：逻辑运算符：and or not and or not 其他：其他：Between and Between and like like inin第8页，本讲稿共78页94.2 选择查询选择查询设置查询条件：设置查询条件：一般条件（与上一章有效性规则中介绍的相同）一般条件（与上一章有效性规则中介绍的相同）多个准则：同行相与，异行相或多个准则：同行相与，异行相或使用标识符：使用标识符：学生学生！姓名姓名 （引用

5、学生表中的姓名字段）（引用学生表中的姓名字段）使用函数使用函数(left/right/mid/year/month/dateleft/right/mid/year/month/date等等)使用使用null(is null/is not null)null(is null/is not null)使用通配符使用通配符(?*#(?*#字符表字符表 等等)第9页，本讲稿共78页10示例示例1.选修选修“西方经济学西方经济学”的学生的学生第10页，本讲稿共78页11示例示例2.选修了课程的的学生选修了课程的的学生(除除“西方经济学西方经济学”)第11页，本讲稿共78页12示例示例3.选修选修“西方经

6、济学西方经济学”的女学生的女学生第12页，本讲稿共78页13示例示例4.05级学生的成绩级学生的成绩第13页，本讲稿共78页14示例示例5.没有邮箱的学生没有邮箱的学生空值的概念空值(null),未定的值第14页，本讲稿共78页15示例示例6.姓姓“李李”的学生的学生第15页，本讲稿共78页164.2 选择查询选择查询自定计算：自定计算：观察观察“西方经济学西方经济学”成绩加成绩加10分后的效果分后的效果查询结果中的列不仅是字段,也可以是自定义的表达式点击生成器按钮 输入表达式第16页，本讲稿共78页174.2 选择查询选择查询统计查询：统计查询：（点（点按钮出按钮出“总计总计”行）行）一般统

7、计：一般统计：求和求和sumsum计数计数countcount求平均值求平均值avgavg求最大值求最大值maxmax求最小值求最小值minmin第17页，本讲稿共78页184.2 选择查询选择查询统计所有学生的总分之和统计所有学生的总分之和第18页，本讲稿共78页194.2 选择查询选择查询统计女生人数统计女生人数非null字段均可第19页，本讲稿共78页204.2 选择查询选择查询分组计算：分组计算：Group byGroup by各门课程的平均分、最高分、最低分各门课程的平均分、最高分、最低分第20页，本讲稿共78页214.2 选择查询选择查询包含函数的自定义计算列：包含函数的自定义计算

8、列：ExpressionExpression各门课程的平均分加各门课程的平均分加10分分第21页，本讲稿共78页224.2 选择查询选择查询在药品销售清单中，统计：在药品销售清单中，统计：1 1。总销售额。总销售额2 2。每一天的总销售额。每一天的总销售额3 3。平均每天的销售额。平均每天的销售额4 4。所有业务员的销售排名。所有业务员的销售排名5 5。若按销售额的。若按销售额的5%5%提成，每个业务提成，每个业务员提成员提成第22页，本讲稿共78页234.2 选择查询选择查询由多步实现的查询由多步实现的查询(例例4-10)4-10)复杂的选择查询复杂的选择查询第23页，本讲稿共78页244.

9、2 选择查询选择查询带有子查询的选择查询带有子查询的选择查询(例例4-31)4-31)复杂的选择查询复杂的选择查询第24页，本讲稿共78页254.3 参数查询参数查询可在查询时给查询条件现赋值，如输入学生的姓名后查询该学生的成绩第25页，本讲稿共78页264.3 参数查询参数查询可就任意成绩区间进行查询的参数查询第26页，本讲稿共78页274.4 交叉表查询交叉表查询行行标标题题列标题列标题值值学学生生表表.姓姓名名成绩表成绩表.课程名课程名成绩表成绩表.成绩成绩第27页，本讲稿共78页284.4 交叉表查询交叉表查询使用向导先作出下图的选择查询，以此查询为数据源使用向导作出交叉表“新建新建”

10、按钮，选按钮，选“交叉表交叉表”向导向导第28页，本讲稿共78页294.4 交叉表查询交叉表查询使用查询设计器行行标标题题列标题列标题值值学学生生表表.姓姓名名成绩表成绩表.课程名课程名成绩表成绩表.成绩成绩第29页，本讲稿共78页304.4 交叉表查询交叉表查询练习统计各班级男女人数统计各班级男女人数第30页，本讲稿共78页314.4 交叉表查询交叉表查询增加一个Where列，设置条件（见下图）设置交叉表的查询条件第31页，本讲稿共78页324.5 操作查询操作查询追加追加删除删除更新更新生成表生成表广义上，广义上，ACCESSACCESS查询还可实现对数据库的追加、删除、查询还可实现对数据

11、库的追加、删除、更新等维护工作更新等维护工作见“查询”菜单第32页，本讲稿共78页334.5 操作查询操作查询生成不及格学生名单单击“查询”菜单，选“生成表查询”，输入新表名生成表生成表第33页，本讲稿共78页344.5 操作查询操作查询可将某张表中的数据追加到另一表中将符合条件的记录删除追加追加删除删除第34页，本讲稿共78页354.5 操作查询操作查询更新更新可修改符合条件的记录将将“C C程序设计程序设计”的成绩加的成绩加1010分分第35页，本讲稿共78页364.6 重复、不匹配项查询重复、不匹配项查询重复项查询重复项查询不匹配项查询不匹配项查询使用向导还可创建第36页，本讲稿共78页

12、374.7 结构化查询语言结构化查询语言SQLStructured Query Language 结构化查询语言结构化查询语言功能：数据的功能：数据的查询、定义、操纵、控制查询、定义、操纵、控制特点：转特点：转P32问题：是否有一种标准化语言能够实现对各种关系数据库的访问？第37页，本讲稿共78页382.点击这里转到SQL视图查询的SQL视图3.输入SQL命令.注意所有的标点均为半角符号1.单击”新建”选项卡上的“查询设计”按钮,进入查询设计器，关闭“显示表”窗口.4.单击”运行”按钮执行查询.第38页，本讲稿共78页394.7 结构化查询语言结构化查询语言SQL命令：命令：SELECT (S

13、ELECT (找什么即结果列）找什么即结果列）FROM FROM （从哪儿）从哪儿）WHERE WHERE （条件）条件）1.1.简单查询简单查询无条件有条件第39页，本讲稿共78页404.7 结构化查询语言结构化查询语言SQL短语短语：DISTINCTDISTINCT和和ALL:ALL:是否去掉重复的元组是否去掉重复的元组ORDER BY:ORDER BY:对结果按指定的字段排序对结果按指定的字段排序(DESCDESC降降,ASCASC升升)BETWEEN AND:(BETWEEN AND:(小在前，大在后）小在前，大在后）IN:(IN:(条件包含在其后面指定的集合中条件包含在其后面指定的集

14、合中)LIKE:LIKE:字符串模式匹配（使用通配符时必须加字符串模式匹配（使用通配符时必须加likelike）AS:AS:指定查询结果的自定义别名指定查询结果的自定义别名Top:Top:返回特定数目或百分比的记录（须有返回特定数目或百分比的记录（须有ORDER BYORDER BY ）第40页，本讲稿共78页41示例示例2.显示学生表显示学生表“李红李红”的记录的记录1.显示学生表中的学号、姓名和性别三个字段显示学生表中的学号、姓名和性别三个字段SELECT 学号学号,姓名姓名,性别性别FROM 学生学生;SELECT 学号学号,姓名姓名,性别性别FROM 学生学生WHERE 姓名姓名=“李

15、红李红”;第41页，本讲稿共78页42示例示例4.显示授课表中所有教师的编号，每位教师只显示显示授课表中所有教师的编号，每位教师只显示1次次3.显示学生表所有团员（显示学生表所有团员（*代表所有字段）代表所有字段）SELECT*FROM 学生学生WHERE 是否团员是否团员;SELECT distinct 教师编号教师编号FROM 授课授课;第42页，本讲稿共78页43示例示例5.显示学生表所有记录，并按年龄从小到大排序显示学生表所有记录，并按年龄从小到大排序SELECT*FROM 学生学生ORDER by 出生日期出生日期 DESC;第43页，本讲稿共78页44示例示例6.显示成绩表中的成绩

16、在显示成绩表中的成绩在70-80之间的记录之间的记录SELECT*FROM 成绩成绩WHERE 成绩成绩 BETWEEN 70 AND 80ORDER by 成绩成绩 DESC;第44页，本讲稿共78页45示例示例7.显示教师表中的显示教师表中的“教授教授”和和“副教授副教授”。SELECT*FROM 教师教师 WHERE 职称职称 in(“教授教授”,”副教授副教授”);第45页，本讲稿共78页46示例示例8.显示学生表所有姓显示学生表所有姓“张张”的学生的学生SELECT*FROM 学生学生WHERE 姓名姓名 like“张张*”;第46页，本讲稿共78页47示例示例9.显示学生的姓名和是

17、否团员，并将显示学生的姓名和是否团员，并将“是否团员是否团员”显示为显示为“政治面目政治面目”SELECT 姓名姓名,是否团员是否团员 as 政治面目政治面目FROM 学生学生;第47页，本讲稿共78页48示例示例10.显示年龄最大的三位学生显示年龄最大的三位学生SELECT top 3*FROM 学生学生ORDER BY 出生日期出生日期;第48页，本讲稿共78页494.7 结构化查询语言结构化查询语言SQL1.1.联接查询联接查询（基于二个或二个以上表）普通联接查询联接条件置于WHERE后当多个表的字段名相同时，应表名.字段名多表的联接第49页，本讲稿共78页50示例示例11.显示学生的学

18、号、姓名、课程号和成绩显示学生的学号、姓名、课程号和成绩SELECT 学生学生.学号学号,姓名姓名,课程号课程号,成绩成绩FROM 学生学生,成绩成绩WHERE 学生学生.学号学号=成绩成绩.学号学号;第50页，本讲稿共78页51示例示例12.显示女学生的学号、姓名、课程号和成绩显示女学生的学号、姓名、课程号和成绩SELECT 学生学生.学号学号,姓名姓名,课程号课程号,成绩成绩FROM 学生学生,成绩成绩WHERE 学生学生.学号学号=成绩成绩.学号学号 and 性别性别=“女女”;第51页，本讲稿共78页52示例示例13.显示学生的学号、姓名、课程名和成绩显示学生的学号、姓名、课程名和成绩

19、SELECT 学生学生.学号学号,姓名姓名,课程名课程名,成绩成绩FROM 学生学生,成绩成绩,课程课程WHERE 学生学生.学号学号=成绩成绩.学号学号 and 成绩成绩.课程号课程号=课程课程.课程号课程号;第52页，本讲稿共78页534.7 结构化查询语言结构化查询语言SQL库函数：库函数：Count(Count(计数计数)Sum(Sum(求和求和)Avg(Avg(求平均值求平均值)MaxMax（求最大值求最大值)MinMin（求最小值求最小值)统计查询统计查询也可以利用SQL的库函数完成求和、求平均值等统计检索例：统计学生总数例：计算“C程序设计”的平均分例：求“李红”同学的总分第53

20、页，本讲稿共78页54示例示例14.统计学生总数统计学生总数SELECT count(*)as 学生总数学生总数FROM 学生学生;第54页，本讲稿共78页55示例示例15.计算计算“C程序设计程序设计”的平均分的平均分SELECT avg(考试成绩考试成绩)FROM 课程课程,成绩成绩WHERE 课程课程.课程号课程号=成绩成绩.课程号课程号 and 课课程名程名=“C程序设计程序设计”;第55页，本讲稿共78页56示例示例16.求求“黄群黄群”同学的总分同学的总分SELECT sum(考试成绩考试成绩)FROM 学生学生,成绩成绩WHERE 学生学生.学号学号=成绩成绩.学号学号 and

21、姓名姓名=“李李红红;公司2008年的图书总销售额?第56页，本讲稿共78页574.7 结构化查询语言结构化查询语言SQL4.4.分组查询分组查询先分组，再统计先分组，再统计例：统计每位同学的总分例：统计每位同学的总分 统计统计2门以上课程不及格的学生名单门以上课程不及格的学生名单Group By 说明按什么分组说明按什么分组(Having)可对分组后各组记录设置筛选条件可对分组后各组记录设置筛选条件第57页，本讲稿共78页58示例示例17.统计每位同学的总分统计每位同学的总分SELECT 学号学号,sum(总评成绩总评成绩)FROM 成绩成绩 GROUP BY 学号学号;各门课程的最高分/最

22、低分/平均分?第58页，本讲稿共78页59示例示例18.统计统计2门以上课程不及格的学生名单门以上课程不及格的学生名单SELECT 学号学号,count(总评成绩总评成绩)FROM 成绩成绩 GROUP BY 学号学号HAVING count(总评成绩总评成绩)=2;请找出错误!第59页，本讲稿共78页604.7 结构化查询语言结构化查询语言SQLSELECT FROM WHERE （SELECT FROM WHERE）5.5.嵌套查询嵌套查询一个查询建立在另一个查询的基础上例：找出和李红同时上课的同学第60页，本讲稿共78页614.7 结构化查询语言结构化查询语言SQL5.5.嵌套查询嵌套查

23、询思路：先找出李红选修的课程号；（子查询）再以子查询的结果为条件查找SELECT 课程号FROM 学生,成绩 WHERE 学生.学号=成绩.学号 and 姓名=“李红;SELECT 学生学生.学号学号,姓名姓名FROM 学生学生,成绩成绩WHERE 学生学生.学号学号=成绩成绩.学号学号 and 课程号课程号 ININ第61页，本讲稿共78页624.7 结构化查询语言结构化查询语言SQL5.5.嵌套查询嵌套查询例：找出没有选课的同学例：找出没有选课的同学第62页，本讲稿共78页63示例示例19.找出和黄群同时上课的同学找出和黄群同时上课的同学SELECT 学生学生.学号学号,姓名姓名FROM

24、学生学生,成绩成绩WHERE 学生学生.学号学号=成绩成绩.学号学号 and 课程号课程号 IN (SELECT 课程号课程号 FROM 学生学生,成绩成绩 WHERE 学生学生.学号学号=成绩成绩.学号学号 and 姓名姓名=“李红李红);第63页，本讲稿共78页64示例示例20.20.找出没有选课的同学找出没有选课的同学SELECT 学生学生.学号学号,姓名姓名FROM 学生学生WHERE 学号学号 not IN (SELECT 学号学号 FROM 成绩成绩);”西方经济学”超过该课程平均分的同学?第64页，本讲稿共78页65示例示例SELECT 学生学生.学号学号,姓名姓名,考试成绩考试

25、成绩FROM 学生学生,成绩成绩,课程课程WHERE 学生学生.学号学号=成绩成绩.学号学号 and 课程课程.课程代码课程代码=成绩成绩.课程代码课程代码 and 课程名称课程名称=高等数学高等数学 and 考试成绩考试成绩=(select avg(考试成绩考试成绩)from 成绩成绩,课程课程 where 课程课程.课程代码课程代码=成绩成绩.课程代码课程代码 and 课程课程名称名称=高等数学高等数学);也可以使用也可以使用=,=,=等连接子查询等连接子查询21.21.”高等数学高等数学”超过该课程平均分的同学超过该课程平均分的同学?第65页，本讲稿共78页664.7 结构化查询语言结构

26、化查询语言SQL1.1.SQLSQL定义功能定义功能表的定义表的定义Create Table Create Table 表名（字段名表名（字段名1(1(类型）类型），字段名字段名2(2(类型）类型）,）注注：可用该命令完成第三章表设计器完成的所有功能。：可用该命令完成第三章表设计器完成的所有功能。请创建”教室”表教室(教室编号,人数)第66页，本讲稿共78页674.7 结构化查询语言结构化查询语言SQL表的删除表的删除Drop Table Drop Table 表名表名 修改表结构修改表结构Alter Table Alter Table 表名表名;Add/AlterAdd/Alter第67页，

27、本讲稿共78页684.6 结构化查询语言结构化查询语言SQL2.SQL2.SQL操纵功能操纵功能插入插入Insert into Insert into 表名（字段名表名（字段名1 1，字段名，字段名2 2，)Values(Values(表达表达式式1 1，表达式，表达式2,2,）将所在教室插入到”教室”表中第68页，本讲稿共78页694.6 结构化查询语言结构化查询语言SQL2.SQL2.SQL操纵功能操纵功能更新更新Update Update 表名表名 set set 字段名字段名=WhereWhere将”教室”表中本教室的人数更新为80第69页，本讲稿共78页704.6 结构化查询语言结构

28、化查询语言SQL2.SQL2.SQL操纵功能操纵功能删除删除Delete from Delete from 表名表名 WhereWhere删除”教室”表中本教室的信息第70页，本讲稿共78页714.7 结构化查询语言结构化查询语言SQL 小结：小结：SQL功能命令动词数据查询SELECT数据定义CREATE/DROP/ALTER数据操纵INSERT/UPDATE/DELETE数据控制GRANT/REVOKE一体化一体化(查询定义操纵和控制均由查询定义操纵和控制均由SQL一种语言实现一种语言实现)非过程化非过程化(只需指出做什么只需指出做什么,怎么做由计算机自己完成怎么做由计算机自己完成)简洁简

29、洁有交互式和嵌入式有交互式和嵌入式第71页，本讲稿共78页724.7 结构化查询语言结构化查询语言SQLACCESS中的联接表达式内联接 INNER JOIN ON左联接 LEFT JOIN ON右联接 RIGHT JOIN ON第72页，本讲稿共78页734.8 创建创建SQL查询查询联合查询联合查询传递查询传递查询数据定义查询数据定义查询第73页，本讲稿共78页744.9 对查询的再认识对查询的再认识p对内：查询对内：查询/维护等（广义）维护等（广义）p对外：将部分数据呈现给用户，方便用户，同对外：将部分数据呈现给用户，方便用户，同时，也使得数据库更安全时，也使得数据库更安全p作为窗体、报

30、表、页的数据源作为窗体、报表、页的数据源p查询的结果是一个虚表，定义下来的查询只是相应查询的结果是一个虚表，定义下来的查询只是相应的的T-SQL命令，数据仍在原数据表中，所以查询的命令，数据仍在原数据表中，所以查询的结果会随数据表中数据的改变而改变。结果会随数据表中数据的改变而改变。第74页，本讲稿共78页75书面作业书面作业P112 习题习题第75页，本讲稿共78页76课堂练习课堂练习1.制作交叉表制作交叉表,查询各个出版社各查询各个出版社各类图书销售的总数量类图书销售的总数量.第76页，本讲稿共78页77课堂练习课堂练习(请书写请书写SQL命令命令)2.查询查询2009年以来的订单年以来的

31、订单,并且按照日期的并且按照日期的先后排列先后排列.3.查询查询2009年以来年以来”人民邮电出版社人民邮电出版社”的订单的订单,并并且按照日期的先后排列且按照日期的先后排列.第77页，本讲稿共78页78讨论讨论:1.在数据库在数据库”学生管理学生管理”中增加一个表中增加一个表”教室教室”,如图如图.按照我校的实际情况按照我校的实际情况,设置输入掩码和有效设置输入掩码和有效性规则性规则.2.为了避免同一间教室排课重复为了避免同一间教室排课重复,应该怎样做应该怎样做?3.在在“教室教室”表和现有的表之间建立起适当的关系表和现有的表之间建立起适当的关系.4.查询查询”张三张三”的课程表的课程表第78页，本讲稿共78页