Chap3_数据查询与SQL命令

1、： 根据某种规则查找部分数据 如查找1班的男生 同时显示分布在若干个表或查询中的数据 如显示学生信息的同时显示其关联的导师姓名、职称等信息 对表中的数据进行计算、分类、汇总 如统计各班学生的平均分 转置表并进行分类统计 相当于Excel的透视表 查询结果转换成表 输出查询的结果到新表中 成批更新、删除表中的数据，或将筛选出的数据追加到另一个表运算符运算符含义含义举例举例结果结果指数运算指数运算23236 6-负数负数-5 5-5-5* , /乘法和除法乘法和除法2 2* *3/53/51.21.2 整除整除65651 1Mod求余运算求余运算6 Mod 56 Mod 51 1+ , -加法和减

2、法加法和减法3+4-13+4-16 6&字符串连接字符串连接李李&丽丽李丽李丽运算符运算符含义含义备注=等于成绩=60 性别=女大于成绩60=大于等于工资=3000小于年龄30=小于等于入学成绩=550不等于成绩99 性别男运算符名称含义NOT非取值与表达式相反AND与前后表达式需要同时满足OR或前后表达式满足之一即可 NOT(研究方向研究方向=考古学考古学) 成绩成绩=330 AND 成绩成绩 关系运算符关系运算符 逻辑运算符逻辑运算符 当表达式有多种运算符时，先处理算术运算符，接着处理比较运算符，然后再处理逻辑运算符。所有关系运算符有相同的优先级，即按它们出现的顺序从左到右进行处理。算术运

3、算符和逻辑运算符按下列优先级进行处理：算术运算符算术运算符-* , / Mod+ , -&逻辑运算符：逻辑运算符：NOT AND ORNOT AND OR 入学分数入学分数500 AND 性别性别=女女 OR 专业专业=工商工商入学分数在入学分数在500以上的所有女生以上的所有女生 及及 所有工商专业的学生。所有工商专业的学生。（ ）字符字符含义含义示例示例可能结果可能结果* *任何数量的字符任何数量的字符刘刘* *刘宝、刘方、刘守航、刘壮刘宝、刘方、刘守航、刘壮? ?单个字符单个字符刘？刘？刘宝、刘方、刘壮刘宝、刘方、刘壮 之内的任何字符之内的任何字符工工程商程商1工程工程1、工商、工商1-

4、 -一定范围内的字符一定范围内的字符1a-c21a2、1b2、1c2! !被排除的字符被排除的字符1!a-x21y2、1z2、112 # #单个数字单个数字A#BA1B、 A2B 运算符运算符含义含义示例示例BETWEEN定义一个区间范围定义一个区间范围(包括等于包括等于)成绩成绩 BETWEEN 320 AND 360LIKE匹配字符串匹配字符串姓名姓名 LIKE 马马* *IN检查是否属于给定范围检查是否属于给定范围专业专业 IN (工程工程,工商工商)IS NULL是否为空是否为空导师编号导师编号 IS NULLIS NOT NULL是否不为空是否不为空导师编号导师编号 IS NOT N

5、ULL 本查询为哪种数据库专门的关系运算本查询为哪种数据库专门的关系运算?投投 影影说明： （1）作为查询对象，“导师情况表”只是一条SQL命令，本身并不包含数据。数据来自“导师”表。 （2）如果打开查询对象时对数据进行更新、添加，则数据的变化实际发生在数据源(“导师”表)中。 （3）数据源如果被删除，则查询无法打开，并显示出错信息 前提：前提：“导师导师”表和表和“研究生研究生”表已建立表已建立1:M关系关系说明：说明： 查询对象的字段名一般沿用数据表中的字段名，如果出现相查询对象的字段名一般沿用数据表中的字段名，如果出现相同的字段名，系统自动在字段名前加上表的名字以示区别同的字段名，系统自

6、动在字段名前加上表的名字以示区别 方式一：打开已有查询的设计视图：单击方式一：打开已有查询的设计视图：单击 “设计设计”按钮按钮 方式二：点击在设计视图中创建查询方式二：点击在设计视图中创建查询 例例3-33-3 为例为例3-2添加博导列（位于添加博导列（位于“职称职称”右侧）右侧） 思考：如果思考：如果“系系”与与“导师导师”表之间、表之间、“导师导师”与与“研究生研究生”表之间没有建立表之间没有建立关系，将得到什么结果？关系，将得到什么结果？ 例例3-53-5 按系名显示各研究生姓名按系名显示各研究生姓名“导师导师”表是表是“系系”表与表与“研究生研究生”表之间的联系表之间的联系“桥梁桥梁

7、” 例3-6 例3-11“=”号可省略号可省略 不显示显示 使用参数查询，您可以在每次运行查询时输入不同的条件使用参数查询，您可以在每次运行查询时输入不同的条件值。您可以获得您所需的结果，而不必每次重新创建整个值。您可以获得您所需的结果，而不必每次重新创建整个查询。查询。 如如例例3-6，可通过输入性别，实现查询不同性别的学生。，可通过输入性别，实现查询不同性别的学生。例例 以表以表“研究生研究生”为数据源创建一个参数查询，当运行该查为数据源创建一个参数查询，当运行该查询时，提示框中应显示询时，提示框中应显示“请输入研究方向请输入研究方向”，输入后，在研究，输入后，在研究方向字段中查找具有指定

8、研究方向的学生，显示方向字段中查找具有指定研究方向的学生，显示“学号学号”、“姓名姓名”、“性别性别”、“入学分数入学分数”和和“研究方向研究方向”五个字段五个字段内容，所建查询命名为内容，所建查询命名为“研究方向查询研究方向查询； 例例3-12 3-12 按性别的升序和导师编号的降序显示记录按性别的升序和导师编号的降序显示记录 例例3-13 3-13 按性别的升序和导师编号的降序显示记录，输出按性别的升序和导师编号的降序显示记录，输出字段顺序为：姓名、导师编号、性别和入学分数字段顺序为：姓名、导师编号、性别和入学分数输出字段顺序输出字段顺序排序顺序排序顺序例例3-14 例例3-15 计算字段

9、的值由表达式计算而得，本身不保存在表中计算字段的值由表达式计算而得，本身不保存在表中若表达式中引用的字段或值发生了变化，必须重新执行查询若表达式中引用的字段或值发生了变化，必须重新执行查询 例例3-16 计算字段，计算字段，“出生年月出生年月”为别名为别名 2009- 2009-年龄年龄 改成：改成： Year(NowYear(Now()-()-年龄年龄 1. 用向导生成交叉表用向导生成交叉表 例3-17 3. 引用两个表的字段生成交叉表：交叉表无法同时引用两引用两个表的字段生成交叉表：交叉表无法同时引用两个表的字段。个表的字段。解决方法：先建立一个查询，然后根据查询创建交叉表解决方法：先建立

10、一个查询，然后根据查询创建交叉表 例例3-18 将最后一栏将最后一栏“总计总计 入学分入学分”字段名称更改为字段名称更改为“入学平均分入学平均分” 设定设定“入学分数、入学平均分入学分数、入学平均分”字段属性为小数位字段属性为小数位2 2位位查询的实质是查询的实质是SQL命令命令 SQL结构化查询语言，可用于定义、查询、更新、管理关系型数据库系统。 SQL是一种非过程语言，易学易用，语句由近似自然语言的英语单词组成 例如， Select 学号,姓名,入学分数,研究方向 From 研究生 Where 性别=男 显示显示“研究生研究生”表中所表中所有男生的学号、姓名、有男生的学号、姓名、入学分数和

11、研究方向入学分数和研究方向 SQL的查询语句格式：SELECT 字段列表 INTO 新表 FROM 记录源 WHERE GROUP BY HAVING ORDER BY 字段列表ASC|DESC 中的内容为可选项中的内容为可选项 选择查询对象 选择进入设计视图,不选择表 选择工具栏SQL视图按钮1. 选取记录源的全部或部分字段2. 用distinct 消除重复记录3. 用TOP显示前面若干条记录4. 对记录进行选择5. 用特殊运算符过滤记录6. 用ORDER BY子句将记录排序输出 在查询操作中，经常会同时涉及多个关联表中的字段信息，如查询导师及其所带学生姓名，涉及导师表、研究生表2个表。 1

12、 用Where实现表间关系 例3-32 输出全体导师的姓名及其所带研究生姓名. 常用的合计函数：COUNT（计数）、SUM（求和）、MAX（最大值）、MIN（最小值）、AVG（求平均值） Group by 将字段中取值相同的记录分为一组将字段中取值相同的记录分为一组 例按职称统计教师人数 例344 通过表达式对某一字段信息进行计算。通过表达式对某一字段信息进行计算。例3-46显示所有入学分数高于平均分的研究生数据 错误： 正确：SELECT 姓名姓名,入学分数入学分数 FROM 研究生研究生 WHERE 入学分数入学分数AVG(入学分数入学分数)SELECT 姓名姓名, 入学分数入学分数 FR

13、OM 研究生研究生 WHERE 入学分数入学分数(SELECT AVG(入学分数入学分数) FROM 研究生研究生)内嵌套查询先被执行内嵌套查询先被执行 Create、Alter、DropSELECTInsert 、Update、Delete 这种类型的查询用于创建、删除、更改表结构。这种类型的查询用于创建、删除、更改表结构。SQL语句语句用用 途途CREATE TABLE创建表创建表ALTER TABLE在已有表中添加新字段或约束在已有表中添加新字段或约束DROP TABLE从数据库中删除表、或者从字段组中删从数据库中删除表、或者从字段组中删除索引除索引CREATE INDEX为字段或字段组

14、创建索引为字段或字段组创建索引 进入查询视图，选择菜单进入查询视图，选择菜单“查询查询”“SQL特定查询特定查询”“数据定义数据定义”，进入数据定义查询窗口。，进入数据定义查询窗口。SQL语言基本数据类型语言基本数据类型1.数值型数值型 Integer，Smallint，Float(n)，Real，Double，Numberic(p,q) 2.字符型字符型 Char(n)，Varchar(n)，Text/Memo，Binary(n)，VarBinary (n)3.日期、时间型日期、时间型 Date，Time，DateTime4.逻辑型逻辑型 Boolean5.货币型货币型 SmallMoney

15、，Money6.OLE型型 General 语句格式：语句格式：Create table 表名表名（列名（列名1 数据类型数据类型1 NOT NULL ，列名，列名2 数据类型数据类型2 NOT NULL ） IN 数据库名数据库名PRIMARY KEY 用于设置主键用于设置主键例：创建学生基本情况表。例：创建学生基本情况表。CREATE TABLE 学生情况学生情况 (学号学号 integer primary key, 姓名姓名 text(4), 性别性别 text(1),出生日期出生日期 date, 家庭住址家庭住址 text(20),联系电话联系电话 text(10),备备注注 memo

16、); Alter table add 数据类型数据类型例：例：ALTER TABLE 学生情况学生情况 ADD 邮政编码邮政编码 CHAR(6); Alter table drop 例：例：ALTER TABLE 学生情况学生情况 DROP 邮政编码邮政编码 ; Alter table Alter 数据类型数据类型例：例：ALTER TABLE 学生情况学生情况 Alter 备注备注 Char(10) 语句格式：语句格式： Drop table 语句功能：表名指要删除的表名；一旦删除，在此表上建语句功能：表名指要删除的表名；一旦删除，在此表上建立的索引、视图都自动被删除掉，且无法恢复。立的索引

17、、视图都自动被删除掉，且无法恢复。 例：删除研究管理数据库中的研究生表例：删除研究管理数据库中的研究生表 DROP TABLE 学生情况学生情况操作：操作：1. SQL代码：代码：SELECT * INTO FROM 2. 在查询设计视图中完成：在查询设计视图中完成：打开查询设计视图打开查询设计视图 执行执行“查询查询”/“生成表查询生成表查询”菜单命令菜单命令 例例3-9 P813-9 P81（1 1）添加数据记录）添加数据记录 语句格式：语句格式： INSERT INTO ( ，) Values ( ,) 语句功能：将一个新记录（一行数据）插入指定的表中。语句功能：将一个新记录（一行数据）

18、插入指定的表中。 例：插入一条学生记录例：插入一条学生记录 Insert into 学生情况学生情况(学生学生ID,姓名姓名,性别性别) values(106,张三张三,男男)注意：字段值必须按照顺序写入注意：字段值必须按照顺序写入! 并且所有字段要写全。并且所有字段要写全。操作：打开设计视图， “查询”/“追加查询” 例例3-503-50 只追加男研究生的信息只追加男研究生的信息 但不追加但不追加“性别性别”字段字段 语句格式：语句格式：Update Set = ，= ,Where 语句功能：更新以语句功能：更新以为名的表中数据。为名的表中数据。 例：将学生张三的学号改为例：将学生张三的学号

19、改为90101 Update 学生情况学生情况 Set 学生学生ID=90101 where 姓名姓名=张三张三 作用：作用：根据某种规则批量修改表中的数据根据某种规则批量修改表中的数据 操作：操作：打开设计视图，打开设计视图， “查询查询”/“更新查询更新查询” 例例3-51 将将“导师导师”表每位导师的年龄增加表每位导师的年龄增加1岁岁UPDATE 导师导师 SET 导师导师.年龄年龄 = 年龄年龄+1更新查询对表中记录的修改不得违反所有的数据更新查询对表中记录的修改不得违反所有的数据完整性约束完整性约束 注意：容易写成注意：容易写成 年龄年龄1 造成无法计算的错误。造成无法计算的错误。其对应的其对应的SQL语句为：语句为：UPDATE 导师导师 SET 导师导师.年龄年龄 = 年龄年龄+1;思考：如果写成了思考：如果写成了 年纪年纪 1 1会出现什么情况？会出现什么情况？ 如果用户需要经常运行同一个查询，但查询内容不同，可以利用参数查询解决。 设置方法：在需要查询字段的条件内输入 ，括号内为查询的提示信息。 如仅根据姓