数据库原理及应用5

1、2022-5-912 5.1.1 查询的概念 查询提供了对数据浏览、数据重组、统计分析、编辑修改、输查询提供了对数据浏览、数据重组、统计分析、编辑修改、输入输出等操作。入输出等操作。 为了减少数据的冗余，不同类别数据放到不同的表中。“查询”的字段来自很多互相之间有“关系”的表，这些字段组合成一个新的数据表视图，但它并不存储任何的数据但它并不存储任何的数据 (视图)优点: 1、节省空间 2、同步更新。查询得到的是当前表中的实际数据。 31 1、 从一个或者多个表中抽取数据。最常见2 2、 以 行列形式（二维表格）分组汇总数据。类似于电子表格。3、 在查寻时弹出对话框，输入必要的参数，动态地进行查

2、询。4、 允许在一个查询操作中更改许多表中的数据。 删除查询、更新查询、追加查询和生成表查询。5、 使用SQL语句，进行查询。 41. 1. 查询向导查询向导 “使用向导创建查询”，选择表，字段。可以从不同的表、字段中生成查询。 数据来源：不同的表和已有的查询 查询的数据表视图看起来像表 在查询数据表中在查询数据表中 无法加入或删除列，无法加入或删除列，而且不能修改查询字段的字段名不能修改查询字段的字段名。这是因为由查询所生成的数据值并不是真正存在的值，而是动态地从表对象中调来的，是表中数据的一个镜像 可更改数据！55.2.1使用简单查询向导数据来源：已有的表和查询6创建子查询创建子查询 统计

3、与汇总分组7将多个表（查询）中的数据集合在一起。抽取数据的方式取决于表与表之间的关系。例： 学号，姓名、课程名称、分数将一个表一个表或一个查询一个查询中的某些字段中的数据作为新的字段，按照另外一种方式察看数据的查询。类似电子表格的格式中查看计算值。在行与列的交叉处可以对数据进行各种计算，包括求和、平均值、最大、最小值、记数等。交叉表查询向导 新的输出格式81. 选择选择含有交叉表数据的一个表表（或查询）的名称。 例如：成绩查询2. 选择选择在交叉表中哪些字段的值用来做。最多只能选择三个字段。 例如：姓名3. 选择选择在交叉表中哪些字段的值用来做。 例如：课程名4. 选择在表中的交叉点计算出什么

4、数值。 例如：求平均Avg5. 为新建的查询取名，并单击“完成”按钮。 可以改变显示字段的属性，如“总计 分数: 分数”字段仅仅是为了计算汇总，不要9如果多表，先建立多表查询。如果多表，先建立多表查询。交叉表查询功能方便数据查看核算汇总值。10 5.2.1 查询设计器 表表/查询显示窗口查询显示窗口 显示查询所用到的数据来源 示例查询设示例查询设计窗口计窗口 显示查询中所用到的查询字段和查询准则 11 查询设计视图中的工具条查询设计视图中的工具条 进入查询设计视图后, 菜单、工具栏都会发生了变化。1、字段：查询工作表中所使用的字段名称2、表：该字段所属的数据表，可以不同的表3、排序：该字段是否

5、排序、按什么方式排序4、显示：该字段是否在查询表中显示5、条件：条件：指定该字段的查询条件指定该字段的查询条件, 限制条件限制条件6、或： 提供多个查询条件12 1 1、设计视图、设计视图 2 2、数据表视图、数据表视图 3 3、SQLSQL视图视图用来显示和编辑SQL语句。 13例：不及格成绩查询 14一般查询中的准则是不变的，如果希望每次查询都要改变准则一般查询中的准则是不变的，如果希望每次查询都要改变准则参数查询可以在运行查询的过程中自动修改查询的参数用户在执行参数查询时会显示一个输入对话框以提示用户输入信息 当需要对某个字段进行参数查询时，首先切换到这个查询的设计视图，然后在作为参数使

6、用的字段下的“准则”单元格中，在在键入相应的提示文本。例如：在分数字段的准则中输入在分数字段的准则中输入 “ “ 分数从分数从 And And ”、“”、“” 例： “ =34 ” 表示当某个值等于34时才算满足这个条件。 “”北京“ 表示当某个值不等于字符串“北京” “And”、“Or”、“Not” 如： “ 100 And 300 ”, “ 300” , “Not 100 ” 逻辑运算符逻辑运算符关系运算符关系运算符18 字串模糊查询满足条件为真191 1检索其中值检索其中值 介于介于 、 =、= = 或或 之间指定值的记录之间指定值的记录 通过使用 Between.And 运算符或比较运

7、算符（ 、=）来识别范围 表达式表达式意义意义80大于 80 的数BetweenBetween #2/2/93# AndAnd #12/1/93#日期由 2-Feb-93 到 1-Dec-93=Callahan大于等于 “Callahan” 字串20 使用 NotNot 运算符的表达式。 例如，可以在“公司名称”字段的“准则”单元格输入表达式 Not T*，以查找名称不是以 T 开始的全部客户，或在“运货商ID”字段输入 Not 2 查找 ID 不是 2 的运货商。 输入“Not NullNot Null”或“Is Not NullIs Not Null”表达式。 例如，要从包含供应商信息的表

8、中的供应商列表，请在“传真”字段的“准则”单元格中输入“Not Null”或“Is Not Null”。 4 4检索不包含值的记录检索不包含值的记录 输入“Is NullIs Null”表达式。 例如，要从包含供应商信息的表中的供应商列表，请“传真号码”字段的“准则”单元格输入“Is Null”。 21 当前日期是 从计算机系统时钟取得的系统日期 Date()，括号中没有空格。 表达式表达式说明说明Between Date( ) And DateAdd(m, 3, Date( )使用 Between.AndBetween.And 运算符和 DateAddDateAdd 和 DateDate 函

9、数，以显示在某个日期之后的三个月内所要求极其交付的订单。 Date( ) - 30使用 Date 函数以显示 30 天之前的旧订单。Year(订购日期) = 2012使用 YearYear 函数以显示 2012 年的订单。DatePart(q, 订购日期) = 4使用 DatePartDatePart 函数以显示第四季度的订单。DateSerial(Year (订购日期), Month(订购日期)+1, 1)-1使用 DateSerialDateSerial、YearYear 和 MonthMonth 函数以显示每个月最后一天的订单。Year(订购日期)=Year(Now() And Mont

10、h(订购日期)=Month(Now()使用 YearYear 和 MonthMonth 函数和 AndAnd 运算符以显示当前年、月的订单。当月的加班合计22 输入使用 InIn 运算符的表达式。 例如，查找在法国、德国或日本的供应商，在供应商表的国家字段的“准则”单元格中输入下列表达式： InIn(法国,德国,日本) ,或者： 法国 OrOr 德国 OrOr 日本 23 使用 LeftLeft 、RightRight 或 MidMid 函数。 Left (stringexpr , n) Right (stringexpr , n) Mid (stringexpr , start , n) s

11、tringexpr 参数可以是字段名（在方括号中）或文本表达式，n 是要抽取的字符数，start 是要抽取的第一个字符的位置。 如果如果 Part IDPart ID中的值为中的值为表达式表达式返回值返回值BA-7893-R12Left(PartID,2)BABA-7893-R12Right(PartID,3)R12BA-7893-R12Mid(PartID,4,4)7893例： 从学号“A10120201”中取出班级代码2425 5.5.1创建总计字段 单击查询设计工具栏的“总计按钮，增加一个总计行：包含总和、平均值、计数、最大/最小值、标准偏差、方差等。计算能力有限计算能力有限26 选定选

12、定计算计算 字段值的总和。字段的平均值。字段的最小值。字段的最大值。字段值的数量，不包括 Null（空）值。字段的标准偏差值。 字段的方差值。27 函数，返回表中第一个记录的字段值 函数，返回表中最后一个记录的字段值。 选定选定用于用于定义要执行计算的组。例如，如果要按类别显示销售额总计，请对“类别名称”字段选定 Group By。创建表达式中包含合计函数的计算字段。通常在表达式中使用多个函数时，将创建计算字段。指定不用于分组的字段准则。如果选定这个字段选项，Microsoft Access 将清除“显示”复选框，隐藏查询结果中的这个字段。28注意事项： 汇总函数是否有效取决与数据类型数据类型

13、。例如文本字段不能”求和“、”平均“等，但可以”计数“。 汇总函数不能对空的值（NULL）进行运算。分组 Group By 汇总计算经常要进行分组操作汇总计算经常要进行分组操作。对某个字段分组，相同的值进行统计29用一个或多个字段的值，进行数值、日期和文字的计算创建计算字段的方法：将表达式直接输入到“字段”格中例如：工资表中的加班合计：加班合计: Sum(加班工资表!加班时数*加班工资表!单位工时报酬) 按月份的加班合计2月份的加班合计例 ：工资表中的实际发放工资注意：总计中的：分组、总计、平均、最小、最大、计数、标准差、方差、第一条记录、最后一条记录、表达式、条件的使用方法30操作查询能够改

14、变已有表中的数据，或者创建一个新表。一般同时加上参数选择 要求表的关系有要求表的关系有“实施参照完实施参照完整性整性”和和“级联删除相关记级联删除相关记录录”，保证删除一个表的记录，保证删除一个表的记录时，相关联的其它表中的相应时，相关联的其它表中的相应记录也会同步删除。记录也会同步删除。例：创建“毕业生删除查询” 调出“查询设计器” ，选择表，然后在查询工具中选择“删除查询”，从已有表中删除符合准则的所有记录31加入“学号”字段。 字段的准则中输入：， 表示当记录的学号起始2位与输入的数字相等时，删除该条记录。DELETEDELETE 学生简况.学号FROMFROM 学生简况WHEREWHE

15、RE (输入要删除的学号起始2位)=Left(学号,2);32 例如将毕业生的记录追加到专门存放毕业生的“毕业生”表中。l 先创建“毕业生”表（学号, 姓名, 性别, 照片, 简历）l 调出“查询设计器”（设计视图），选择表，然后在查询工具中选择“追加查询”。l 加入追加的字段（学号, 姓名, 性别, 照片, 简历）l 选择要追加的目标的表。在学号字段的准则中 ：INSERT INTOINSERT INTO 毕业生 ( 学号, 姓名, 性别, 照片, 简历 )SELECTSELECT 学生简况.学号, 学生简况.姓名, 学生简况.性别, 学生简况.照片, 学生简况.简历FROMFROM 学生简

16、况WHEREWHERE (输入要追加的学号起始2位)=Left(学号,2);33 调出“查询设计器”（设计视图），选择表，然后在查询工具中选择“更新查询”。例如，对学生成绩表的所有不及格分数更改成60分。UPDATEUPDATE 学生成绩 SETSET 学生成绩.分数 = 60 WHERE (学生成绩.分数)60); 34l 调出“查询设计器”（设计视图），选择表，然后在查询工具中选择“生成表查询”。 l 输入要生成的表名。 l 在相应字段输入准则例如，生成不及格表（学号例如，生成不及格表（学号, 姓名，课程，成绩）姓名，课程，成绩） 从学生简况、课程名称、学生成绩表从学生简况、课程名称、学生

17、成绩表SELECTSELECT 学生简况.姓名, 课程名称.课程名称, 学生成绩.分数 INTOINTO 不及格FROMFROM 学生简况 INNER JOININNER JOIN (课程名称 INNER JOIN 学生成绩 ON 课程名称.课程编号 = 学生成绩.课程编号) ON 学生简况.学号 = 学生成绩.学号WHERE (学生成绩.分数)60); 35 Access所有的查询都可以认为是一个所有的查询都可以认为是一个SQL查查询询 Access查询就是以查询就是以SQL语句为基础来实现查语句为基础来实现查询的功能询的功能 ACCESS中所有的数据库操作都是由SQL语言构成的， ACCE

18、SS只是在其上增加了更加方便的操作向导和可视化设计36 SELECT语句构成了SQL数据库语言的核心, 主要被用来对数据库进行查询并返回符合用户查询标准的结果数据。它的语法包括5个主要子句，分别是FORM、WHERE、GROUP BY、HAVING、ORDER BY子句。SELECT语句的结构:37例例select student.* , SC.*FORM student, SCWhere Student.学号学号=SC.学号；学号；如果需要从多个表中获得数据表名前缀表名前缀.字段名字段名连接条件不能省连接条件不能省38 有INNER、LEFT、RIGHT 三种JOIN操作 用来组合两个表中的

19、记录用来组合两个表中的记录，只要公共字段之中有相符的值即可 例如：SELECT 学生简况.姓名, 学生成绩.课程编号, 学生成绩.分数 学生简况 INNER JOIN 学生成绩学生成绩 ON 学生简况学生简况.学号学号 = 学生成绩学生成绩.学号学号; 将学生简况和 学生成绩 表，通过相等的学号字段联接起来。39如有三个表：表1、表2、表3，现在将三个表联接起来： 这样这三个表就联接起来了。例如：40LEFT JOIN 将LEFT左边的表名1中的所有记录全部保留，而将右边的表名2中的字段B与表名1.字段A相对应的记录显示出来。 SELECT 学生简况.姓名, 学生成绩.课程编号, 学生成绩.分

20、数FROM 学生简况 LEFT JOIN 学生成绩 ON 学生简况.学号 = 学生成绩.学号; 而RIGHT JOIN 和LEFT JOIN 相反。SELECT 学生简况.姓名, 学生成绩.课程编号, 学生成绩.分数FROM 学生简况 LEFT JOIN 学生成绩 ON 学生简况.学号 = 学生成绩.学号; 41字段别名可以帮助我们有效的组织查询的输出结果 ,相当于查询设计视图中字段属性的“标题” 在在SQL中可以用中可以用“AS”操作来实现定义新的字段别名操作来实现定义新的字段别名例在新建的查询中将“表1”中的“字段1”的内容对应到新字段“新字段1”中：别名都直接放置在各自对应名称的后面，中

21、间用空格分开。SELECT 学生成绩.学号, Sum(学生成绩.分数) AS 总分总分FROM 学生成绩GROUP BY 学生成绩.学号ORDER BY Sum(学生成绩.分数) DESC; 42 使用insert语句向数据库表格中插入或添加新的数据行 例如： 毕业生 ( 学号, 姓名, 性别, 简历 ) (22000102, 李扬,0, Beijing”); 毕业生 ( 学号, 姓名, 性别, 照片, 简历 ) 学生简况.学号, 学生简况.姓名, 学生简况.性别, 学生简况.照片, 学生简况.简历 学生简况 (输入要追加的学号起始2位)=Left(学号,2);如果把不及格的加到不及格表中？4

22、3 例如：UPDATE 学生成绩SET 学生成绩.分数 = 60WHERE (学生成绩.分数)60);使用update语句时，关键一点就是要设定好用于进行判断的where条件从句。如果要求2003年之前进厂，工资不到800的加到800 ?44 例如：DELETE 学生简况.学号FROM 学生简况WHERE (输入要删除的学号起始2位)=Left(学号,2);当需要删除某一行或某个记录时，在delete from关键词之后输入表格名称，然后在where从句中设定删除记录的判断条件。注意，如果用户在使用如果用户在使用delete语句时不设定语句时不设定where从句，则表格中的所有记录从句，则表格

23、中的所有记录将全部被删除将全部被删除。 45 创建新表格时，在关键词create table后面加入所要建立的表格的名称，然后在括号内顺次设定各列的名称，数据类型，以及可选的限制条件等。CREAT TABLE employee (firstname varchar(15), lastname varchar(20), age number(3), address varchar(30), city varchar(20); 46 例如：drop table employee; Drop table 命令的作用与删除表格中的所有记录不同。删除表格中的全部记录之后，该表格仍然存在，而且表格中列的信

24、息不会改变。而使用使用drop table命令则会将整个数据库表格的所有信息全部删除命令则会将整个数据库表格的所有信息全部删除。 47除了在数据库中使用SQL命令之外，还可以通过编程语言例如VB、VBA、C、VC、ASP、PHP 来实现SQL操作，完成精细、复杂的查询任务。 ADO（ActiveX Data Objects,微软的一种新的数据访问模型）。主要的ADOADO对象有三个：ConnectionConnection、CommandCommand和RecordsetRecordset。ConnectionConnection对象能够建立数据与应用程序之间的联系，在建立联系后，Comman

25、dCommand和RecordsetRecordset对象用来查询、存储或更新数据。Command Command 对象主要担任数据库查询(Database Query)的角色。RecordsetRecordset对象则是三者之中最为特别，功能亦最为强大，由数据取得由数据取得的数据集合就存储于的数据集合就存储于RecordsetRecordset中中。编程编程48例如:ASP中 增加一个新注册账号 = Server.CreateObject(ADODB.Connection) driver=Microsoft Access Driver (*.mdb);dbq= MyDB.MDB在guestb

26、ook数据表中利用SQL语句增加一个数据记录，然后把其中的各字段 (姓名, 电话, Email, 主题, 留言)分别设置成从留言表单上取下的对应的数据。INSERT INTO tablename (first_column,.last_column)VALUES (first_value,.last_value); sql = Insert Into GuestBookInsert Into GuestBook ( (姓名姓名, , 电话电话, Email, , Email, 主题主题, , 留言留言) Values() Values( sql = sql & SqlStr(Name)

27、 & , sql = sql & SqlStr(Tel) & , sql = sql & SqlStr(Email) & , sql = sql & SqlStr(Subject) & , sql = sql & SqlStr(Memo) & )执行sql指令。写入数据库49 在教学管理数据库中，创建以下查询。(1) 教师任课查询，包括字段、姓名、性别、课程名。(2) 电信学院教师任课查询，包括姓名(电信学院)、性别、课程名。 在教学管理数据库中，创建“各班学生平均成绩”查询，其中包括学号、班级和平均成绩。 用查询设计器修改例5-4（教学管理数据库中）创建的成绩 查询_交叉表”查询，要求添加计算每个学生的“平均分”的字段。 在罗斯文示例数据库中，创建“产品库存”查询，其中包括产品ID、产品名称、单价、库存量、金额，其中“金额”字段为“单价”和“库