access课件第5章SQL.ppt

数据库原理及应用 Access2003 简阳市三星中学 主讲 吴老师 第5章SQL语言 简介SQL是结构化查询语言StructuredQueryLanguage的缩写 SQL包括数据定义 数据查询 数据操纵 和数据控制4个部分 是一种功能齐全的数据库语言 SQL已经成为关系数据库领域中的一个主流语言 单纯的SQL语言所包含的语句并不多 但在使用的过程中需要大量输入各种表 查询和字段的名字 这样当你建立一个涉及大量字段的查询时 就需要输入大量文字 与用查询设计视图建立查询相比 就麻烦多了 所以在建立查询的时候一般都是先在查询设计视图将基本的查询功能都实现了 最后再切换到SQL视图通过编写SQL语句完成一些特殊的查询 下面我们就介绍一些在ACCESS使用中常常会用到的一些SQL语言 SQL查询 建数据库的目的是为了查询数据 SQL查询是使用SQL语句创建的查询 可以用结构化查询语言 SQL 来查询 更新和管理数据库 在查询 设计 视图中创建查询时 Access将在后台构造等效的SQL语句 大多数的查询属性在 SQL 视图中都有可用的等效子句和选项 用设计视图和向导就可以建立很多有用的查询 而且它的功能已经基本上能满足我们的需要 而SQL语言作为一种通用的数据库操作语言 并不是ACCESS用户必须要掌握的 但在实际的工作中有时必须用到这种语言才能完成一些特殊的工作 SQL特定查询 某些SQL查询 称为SQL特定查询 不能在设计网格中创建 对于传递查询 数据定义查询和联合查询 必须直接在 SQL 视图中创建SQL语句 联合查询UNION 联合查询将两个或更多个表或查询中的字段合并到查询结果的一个字段中 使用联合查询可以合并两个表中的数据 注 所有查询中的列数和列的顺序必须相同 例 可以合并 供应商 表和 客户 表中列出的所有公司名称和城市数据 然后可以根据联合查询创建生成表查询以生成一个新表 SELECT公司名称 城市FROM供应商UNIONSELECT公司名称 城市FROM客户 数据定义查询 数据定义查询可用来创建或更改数据库中的对象 可以创建 删除或改变表 也可以在数据库表中创建索引 使用数据定义查询来修改数据库对象可能会有风险 因为完成操作时并不出现确认对话框 如果操作出错 可能造成数据丢失或意外更改表的设计 使用数据定义查询来修改数据库中的对象时要格外小心 如果你所使用的数据库不是由你来负责维护的 应当在运行数据定义查询之前咨询数据库的管理员 此外 最好对要运行此类查询的表制作备份 创建数据定义查询的方法 1 在 数据库 窗口中 单击 对象 下的 查询 然后单击 数据库 窗口工具栏上的 新建 2 在 新建查询 对话框中 单击 设计视图 然后单击 确定 3 无需添加任何表或查询 在 显示表 对话框中单击 关闭 4 在 查询 菜单上指向 SQL特定查询 然后单击 数据定义 5 为数据定义查询输入SQL语句 每个数据定义查询只能包含一条数据定义语句 支持的数据定义语句 数据类型 TEXT 创建表 例 创建一个 朋友 表 由 朋友ID 姓名 出生年月 电话 CREATETABLE朋友 朋友IDinteger 姓氏char 4 名字char 5 出生日期date 电话text 备注memo 修改表 删除表 例 向 朋友 表中增加 住址 列 数据类型为文本型 ALTERTABLE朋友ADD住址TEXT 例 删除 朋友 表 DROPTABLE朋友 插入记录 Insertinto表名 字段1 字段2 字段3 values 常量1 常量2 常量3 功能 为指定表指定字段插入记录例如 P106两例思考 1 OLE对象型 备注型字段的值可以用命令来插入吗 2 为学生表插入姓名 张三 年龄20的令该如何写 数据更新 基本格式 UPDATE表名set字段1 表达式1 字段2 表达式2where条件功能 对指定表中指定字段的值进行更改 如省略WHERE子句则对表中所有记录的相应字段值进行更改 例如 P107思考 1 如学生表中有年龄字段但未录入值 对年龄大于18岁的学生进行加1操作 2 把成绩表所有同学成绩加20分 删除记录 基本格式 deletefrom表名where条件功能 删除表中符合条件的记录 例 P107思考 1 如省略where则删除表中所有记录 2 删除姓名是张三的记录 基本的SQL语句 查询 SELECT语句构成了SQL数据库语言的核心 它的语法包括5个主要子句 分别是 FROM WHERE GROUPBY HAVING ORDERBY子句 SELECT语句的结构 SELECT 字段列表 FROM 表列表 WHERE 选择条件 GROUPBY 分组依据 HAVING 组选择条件 ORDERBY 排序依据 例 建立一个查询 查询 资产信息 表中的 资产类型 和 购买日期 两个字段信息 SELECT资产类型 购买日期FROM资产信息 练习 查看所有借出信息的全部情况 代表所有属性 SELECT FROM借出信息 括起来的部分称之为SELECT语句的子句 子句在SELECT语句中都是可选项目 WHERE子句 是一个行选择说明子句 用这个语句可以对表中的记录进行限制 当WHERE后面的行选择说明为真的时候才将这些行作为查询的行 而且在WHERE中还可以有多种约束条件 这些条件可以通过 AND 这样的逻辑运算符联接起来 WHERE子句的条件表达式可以使用下列运算符 算术比较运算符 BETWEEN逻辑运算符AND OR NOT集合元素包含运算符IN NOTIN通配符LIKE LIKE 例 在 资产信息 表中 找出 资产编号 为 TH0003 的资产名称及购买价格 SELECT资产名称 购买价格FROM资产信息WHERE资产编号 TH0003 练习 资产信息 表中 找出 使用期限 大于2年的 资产名称 购买价格 使用期限 SELECT资产名称 购买价格 使用期限FROM资产信息WHERE使用期限 2 将视图切换到设计视图 例 查找 资产状态 为 在库中 和 出借中 的所有 资产名称 及 资产编号 SELECT资产编号 资产名称 资产状态FROM资产信息WHERE资产状态IN 在库中 借出中 或SELECT资产编号 资产名称 资产状态FROM资产信息WHERE资产状态 在库中 OR 借出中 例查找 资产编号 以 TH 打头的所有 资产名称 及 存放位置 SELECT资产编号 资产名称 存放位置FROM资产信息WHERE资产编号LIKE TH 注 Access里谓词LIKE的通配符用法 表示任何单一字符 表示零个或多个字符 表示任何一个数字 GROUPBY子句 分组说明 例查找不同资产类型的价格总计 SELECT资产类型 SUM 购买价格 AS价格总计FROM资产信息GROUPBY资产类型 注释 SUM 对某一列的值求和 属性必须是数值类型 AS 定义新字段名例 SELECT字段1AS新字段1FROM表1 SQL的库函数 COUNT 计算元组的个数 COUNT 对列的值计算个数求和函数SUM 对某一列的值求和 属性必须是数值类型 求平均值AVG 对某一列的值求平均值 属性必须是数值类型 求最大值MAX 找出一列值中的最大值 求最小值MIN 找出一列值中的最小值 练习 求出各个 资产类型 的最高价格 最低价格 平均价格 SELECT资产类型 MAX 购买价格 MIN 购买价格 AVG 购买价格 FROM资产信息GROUPBY资产类型 练习 求各资产类型的资产个数 SELECT资产类型 COUNT 资产类型 AS个数FROM资产信息GROUPBY资产类型 HAVING子句 组选择说明 HAVING子句 条件限制语句 和GROUPBY共同使用 例 在 资产信息 表中 求 电脑网络 的 购买价格 总和 SELECT资产类型 Sum 购买价格 AS价格总计FROM资产信息GROUPBY资产类型HAVING资产类型 电脑网络 查找134班学生人数查找12班男生人数select班级 count as人数fromxswhere性别 男 groupby班级having班级in 1 2 练习 在 资产信息 表中 求除 电脑网络 外的其他资产类别的 购买价格 总和 SELECT资产类型 Sum 购买价格 AS价格总计FROM资产信息GROUPBY资产类型HAVING资产类型 电脑网络 HAVING资产类型NOTIN 电脑网络 HAVINGNOT资产类型 电脑网络 ORDERBY语句 排序 例 查找 购买价格 在1000元和5000元之间的 资产名称 及 资产编号 结果按照 购买价格 降序和 资产编号 的升序排列 SELECT资产编号 资产名称 购买价格FROM资产信息WHERE购买价格BETWEEN1000AND5000ORDERBY购买价格DESC 资产编号ASC 注释 BETWEEN AND在 和 之间DESC降序ASC升序 WHERE购买价格 1000AND购买价格 5000 练习 查找所有报废产品的 资产名称 及 购买价格 结果按 购买价格 降序排列 SELECT资产名称 购买价格FROM资产信息WHERE资产状态 报废 ORDERBY购买价格DESC 字符串常量 SELECT子句中允许有字符串常量 用引号 引起来 字符串常量用于提醒用户 使结果易于阅读 例 求资产的总数 SELECT 资产总数 COUNT FROM资产信息 查询结果为 资产总数 12 多表联接查询 以上简单查询只涉及到一个关系 如果查询目标涉及到两个或几个关系 往往要进行联接运算 由于SQL是高度非过程化的 用户只要在FROM子句中指出关系名称 在WHERE子句中写明联接条件即可 联接运算由系统完成并实现优化 INNERJOIN内联接 内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行 例如 检索students和courses表中学生标识号相同的所有行 LEFTJOIN左联接的结果集包括LEFT子句中指定的左表的所有行 而不仅仅是联接列所匹配的行 如果左表的某行在右表中没有匹配行 则在相关联的结果集行中右表的所有选择列表列均为空值 RIGHTJOIN右联接是左向外联接的反向联接 将返回右表的所有行 如果右表的某行在左表中没有匹配行 则将为左表返回空值 FULLJOIN完全联接返回左表和右表中的所有行 当某行在另一个表中没有匹配行时 则另一个表的选择列表列包含空值 如果表之间有匹配行 则整个结果集行包含基表的数据值 联接查询 例 查找 李红 所借 资产名称 型号 配置 SELECT资产名称 配置 型号FROM借出信息 资产信息WHERE借出信息 资产编号 资产信息 资产编号and借用人 李红 SELECT资产名称 配置 型号FROM借出信息innerjoin资产信息on借出信息 资产编号 资产信息 资产编号WHERE借用人 李红 练习 查找 sys 所批复设备的 资产名称 型号 配置 批复人 SELECT资产名称 配置 型号 批复人FROM资产信息 借出信息WHERE资产信息 资产编号 借出信息 资产编号and批复人 sys SELECT资产名称 配置 型号 批复人FROM资产信息innerjoin借出信息on资产信息 资产编号 借出信息 资产编号WHERE批复人 sys 在XS和CJ按总分降序查找出相关信息selectxs 姓名 xs 性别 xs