access 第3章 数据查询与SQL命令

1第3章数据查询与SQL命令2本章主要内容3.1查询对象概述3.2通过向导或设计视图创建查询3.3使用SQL命令查询3.4操作查询3思考问题：哪位导师带了哪些研究生？导师年龄是否能自动更新？将“考古学”专业的研究生入学分数增加15%以降低入学难度？研究生信息自动转存为毕业生信息？43.1查询对象概述查询对象的实质是SQL命令，本身不包含数据。屏幕所显示的数据是查询对象在执行时从表中“抽取”的。生成查询对象的途径：查询向导、查询设计视图和SQL视图53.1查询对象概述查询能实现的功能：根据某种规则查找部分数据同时显示分布在若干个表或查询中的数据对表中的数据进行计算、分类、汇总将显示结果转换成数据表成批更新、删除表中的数据将筛选出的数据追加到另一个表63.2通过向导或设计视图创建查询3.2.1用简单查询向导生成查询3.2.2通过设计视图编辑或创建查询3.2.3条件查询3.2.4查询的有序输出3.2.5使用通配符查询3.2.6使用计算字段3.2.7交叉表查询73.2.1用简单查询向导生成查询83.2.1用简单查询向导生成查询[例3-1]基于“导师”表生成“导师情况表”查询,只显示姓名、职称和系编号字段。93.2.1用简单查询向导生成查询说明：（1）作为查询对象，“导师情况表”查询只是一条SQL命令，并不包含数据。数据来自“导师”表。（2）如果打开查询对象时对数据进行更新、添加，则数据的变化实际发生在数据源(“导师”表)中。（3）数据源如果被删除，则查询无法打开，并显示出错信息103.2.1用简单查询向导生成查询[例3-2]基于“导师”和“研究生”两个表生成“导师-研究生”查询。前提：“导师”表和“研究生”表已建立1:M关系显示导师的编号、姓名、职称及所带研究生的学号、姓名和入学分数113.2.1用简单查询向导生成查询说明：查询对象的字段名一般沿用数据表中的字段名，如果出现相同的字段名，系统自动在字段名前加上表的名字以示区别123.2.2通过设计视图编辑或创建查询打开已有查询的设计视图：单击“设计”按钮133.2.2通过设计视图编辑或创建查询[例3-3]：为例3-2查询添加“博导”列（位于“职称”的右侧）选中“学号”列，选择“插入/列”菜单，在学号前插入一空白列143.2.2通过设计视图编辑或创建查询在设计视图中创建查询153.2.2通过设计视图编辑或创建查询[例3-4]查询各系的系名导师姓名和研究生姓名思考：如果“系”与“导师”表之间、“导师”与“研究生”表之间没有建立关系，将得到什么结果？163.2.2通过设计视图编辑或创建查询[例3-5]按系名显示各研究生的姓名“导师”表是“系”表与“研究生”表之间的联系“桥梁”173.2.3条件查询条件查询的作用是对所选择的记录作进一步的限定[例3-6]显示女研究生的全部信息“=”号可以省略不显示以避免数据重复183.2.3条件查询[例3-7]列出入学分数在340-360之间的所有研究生的姓名、性别和入学分数And关系：两边的条件同时成立，整个表达式才成立。193.2.3条件查询[例3-8]选出所有入学分数在340-360之间的男研究生的姓名、性别、入学分数几个条件左右并列，表明是And关系(并且)。203.2.3条件查询[例3-9]选出研究生方向为考古学或会计学的所有研究生的姓名、性别、入学分数和研究方向'考古学'or'会计学'213.2.3条件查询[例3-10]显示导师“马腾跃”所带的女研究生或者入学分数超过320分的男研究生的信息上下并列的两个条件，表示or关系223.2.3条件查询[例3-11]找出所有没有导师编号的研究生姓名没有导师编号233.2.4查询的有序输出[例3-12]按性别的升序和导师编号的降序显示记录如果有若干个列是排序关键字段，Access按从左到右的顺序依次排序243.2.4查询的有序输出[例3-13]按性别的升序和导师编号的降序显示记录，输出字段顺序为：姓名、导师编号、性别和入学分数输出字段顺序排序顺序253.2.5使用通配符查询如果想查找某个姓“马”的导师的信息，该如何给出查询条件呢？通配符可以用作其它字符的占位符，用以实现在仅知部分内容的情况下完成对文本、数值等类型字段的查询263.2.5使用通配符查询（P62表3.1）273.2.5使用通配符查询[例3-14]找出研究方向的第3个字为“医”的所有姓“马”的研究生的信息。所有姓“马”的第3个字为“医”注意：应用通配符时不能用“=”，要用“like”283.2.5使用通配符查询[例3-15]找出年龄为50余岁的导师，要求其所带的研究生中没有姓马或赵的学生。年龄为50余岁排除姓“马”、“赵”的293.2.6使用计算字段计算字段的值由表达式计算而得，不保存在表中若表达式中引用的字段或值发生了变化，必须重新执行查询[例3-16]输出导师的编号、姓名、出生年份和性别。计算字段，“出生年份”为别名2009-[年龄]改成：Year(Now())-[年龄]303.2.7交叉表查询交叉表用于显示表中某个字段的汇总值，包括总和、计数和平均等，如查询研究生表中不同性别的不同研究方向的学生人数。1.用向导生成交叉表313.2.7交叉表查询[例3-17]生成一个交叉表，显示不同性别不同研究方向各导师所带研究生的平均分缺点：(1)标题“总计入学分数”含义不清(2)平均数的小数点位数太多分类汇总的依据可以有多个323.2.7交叉表查询2.在设计视图中完善【3-17】交叉表原为：总计入学分数修改这两栏的属性333.2.7交叉表查询3.引用两个表的字段生成交叉表[例3-18]显示不同系不同性别的导师人数先建立一个查询，包含两个表中的所需字段根据所建查询用向导创建交叉表343.3使用SQL命令查询查询的实质是SQL命令353.3使用SQL命令查询3.3.1SQL语言概述3.3.2基于单一记录源的查询3.3.3基于多个记录源的查询3.3.4合计、汇总与计算3.3.5嵌套查询363.3.1SQL语言概述SQL(StructuredQueryLanguage)意为结构化查询语言，可用于定义、查询、更新、管理关系型数据库系统。SQL语言易学易用，语句由近似自然语言的英语单词组成.例如，Select学号,姓名,入学分数,研究方向From研究生Where性别='男'显示“研究生”表中所有男生的学号、姓名、入学分数和研究方向373.3.1SQL语言概述SQL语言不涉及数据库内部细节，通用性好。不能设计出与用户交互的图形界面，需用VB、VC等程序语言作为客户端开发工具，同时在程序中嵌入SQL命令。SQL命令的所有子句既可以写在同一行上，也可以分行书写,大小写字母的含义相同；命令用分号“;”结束(也可以不写)。383.3.1SQL语言概述SQL的查询语句格式（SELECT查询又称选择查询）SELECT字段列表[INTO新表]FROM记录源[WHERE条件表达式][GROUPBY分组表达式][HAVING条件表达式][ORDERBY字段列表[ASC|DESC]][]中的内容为可选项393.3.2基于单一记录源的查询单一记录源：被查询的对象是一个表或查询进入SQL视图：先进入空白查询设计视图(不选择表)403.3.2基于单一记录源的查询1.选取记录源的全部字段或部分字段[例3-19]输出“导师”表的全部字段*表示记录源的全部字段413.3.2基于单一记录源的查询[例3-20]以例[3-19]所建立的“导师SQL”查询为记录源，显示其中的“导师编号”、“姓名”和“职称”字段SELECT导师编号,姓名,职称FROM导师SQL423.3.2基于单一记录源的查询说明：（1）字段名之间的逗号“,”必须是英文字符（2）如果重命名查询“导师SQL”，则本查询中引用的记录源名字将自动更新。（3）作为记录源的“导师SQL”查询不能删除（4）当真正的记录源“导师”表中的数据更新时，查询的执行结果也自动更新。433.3.2基于单一记录源的查询2.用DISTINCT消除重复记录[例3-21]显示“导师”表中所有“职称”名称SELECTDISTINCT职称FROM导师SELECT职称FROM导师443.3.2基于单一记录源的查询3.用TOP显示前面的若干条记录语法格式：TOPn（n为指定记录数）[例3-22]显示“导师”表中的前4条记录SELECTTOP4*FROM导师453.3.2基于单一记录源的查询4.用WHERE子句对记录进行选择语法格式：WHERE表达式关系运算符表达式（关系运算符的名称和含义见表3.2）463.3.2基于单一记录源的查询4.用WHERE子句对记录进行选择[例3-23]找年龄不低于60岁的导师，显示部分字段SELECT姓名,性别,年龄FROM导师WHERE年龄=60473.3.2基于单一记录源的查询可用逻辑运算符形成逻辑表达式，见表3.3。逻辑运算符的优先级依次为：NOTANDOR运算符名称含义NOT非取反，若表达式成立，则取反后不成立，反之则成立AND与若干个关系表达式同时成立才成立，否则不成立OR或若干个关系表达式中，只要一个成立，逻辑表达式就成立483.3.2基于单一记录源的查询[例3-24]找出所有年龄不低于60岁的女导师，显示其姓名、性别和年龄。SELECT姓名,性别,年龄FROM导师WHERE导师.年龄=60AND性别='女'493.3.2基于单一记录源的查询[例3-25]输出所有入学分数在350以上的女研究生，以及所有考古学研究方向的研究生姓名、性别、入学分数、研究方向。SELECT姓名,性别,入学分数,研究方向FROM研究生WHERE入学分数350AND性别='女'OR研究方向='考古学'503.3.2基于单一记录源的查询思考与练习：[例3-26]请写出SQL语句：输出例[3-25]规则以外的全部研究生的姓名、性别、入学分数、研究方向。513.3.2基于单一记录源的查询5.用特殊运算符过滤记录例如:“入学分数BETWEEN320AND360”指入学分数在320~360分之间，包括320分和360分者；而“入学分数IN(320,360)”指入学分数等于320或者360。523.3.2基于单一记录源的查询[例3-27]找出所有入学分数在320-360之间的“考古学”和“会计学”方向的研究生姓名、性别、入学分数、研究方向。SELECT姓名,性别,入学分数,研究方向FROM研究生WHERE研究方向IN('考古学','会计学')AND入学分数BETWEEN320AND360533.3.2基于单一记录源的查询[例3-28]找出所有没有导师的研究生，显示他们的所有信息。SELECT*FROM研究生WHERE导师编号ISNULL导师编号为空543.3.2基于单一记录源的查询[例3-29]找出所有姓马或者姓名中含有“国”字的研究生，显示他们的所有信息。SELECT*FROM研究生WHERE姓名LIKE'马*'OR姓名LIKE'*国*'姓“马”或姓名中含有“国”553.3.2基于单一记录源的查询[例3-30]显示学号尾数不在0-4范围中的研究生。SELECT*FROM研究生WHERE学号LIKE'*[!0-4]'学号尾数不在0~4条件也可写成：WHERE学号LIKE'*[5-9]'563.3.2基于单一记录源的查询6.用ORDERBY子句将记录排序输出排序的方式：升序、降序[例3-31]按性别的升序和入学分数的降序，输出入学分数在340分以上的研究生的全部信息。573.3.2基于单一记录源的查询SELECT*FROM研究生WHERE入学分数340ORDERBY性别,入学分数DESC性别升序、入学分数降序583.3.3基于多个记录源的查询数据源－多表建立表之间的关系－公共属性连接数据表的方式：WHERE子句JOIN子句：内连接、左外连接和右外连接593.3.3基于多个记录源的查询1.用WHERE实现表间关系