Access 数据库 第8课SQL查询

null第8课 SQL查询第8课 SQL查询 SQL概述 SQL数据定义语句 SQL数据查询语句 SQL数据编辑语句8.1 SQL概述 8.1 SQL概述 SQL (Structured Query Language)结构化查询语言 ANSI(美国国家标准协会)规定SQL为关系型数据库管理系统的标准语言。SQL语言的主要功能就是同各种数据库建立联系，进行沟通，以达到操纵数据库数据的目的。 SQL语句可以用来执行各种各样的操作，例如更新数据库中的数据、从数据库中检索数据等。目前，绝大多数流行的关系型数据库管理系统，如Oracle、Sybase、Microsoft SQL Server、Access等，都采用了SQL语言标准。8.2 SQL数据定义功能8.2 SQL数据定义功能建立表： CREATE TABLE 表名 （字段1 数据类型1 [NOT NULL] [,字段2 数据类型2 [NOT NULL]]……） [IN 数据库名] 修改表： ALTER TABLE 表名 ADD 字段名 数据类型 删除表： DROP TABLE 表名8.3 SQL数据查询语句8.3 SQL数据查询语句8.3.1 SQL查询语句 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 8.3.2 单一记录源的查询 8.3.3 多个记录源的查询 8.3.4 合计、汇总与计算 8.3.5 嵌套查询 8.3.1 SQL查询语句格式8.3.1 SQL查询语句格式SQL的查询语句格式： SELECT 字段列表 [INTO 新表] FROM 记录源 [WHERE <条件表达式>] [GROUP BY <分组表达式>] [HAVING <条件表达式>] [ORDER BY 字段列表[ASC|DESC]][ ]中的内容为可选项8.3.2 单一记录源的查询 8.3.2 单一记录源的查询 单一记录源：被查询的对象是一个表或查询 进入SQL视图： 先进入空白查询设计视图(不选择表)8.3.2 基于单一记录源的查询 8.3.2 基于单一记录源的查询 1. 选取记录源的全部字段或部分字段 [例8-1] 输出“订单”表的全部字段 *表示记录源的全部字段 8.3.2 基于单一记录源的查询 8.3.2 基于单一记录源的查询 [例8-2] 以 “98年订单”查询为记录源，显示部分字段 SELECT 订单编号,客户,雇员,发货日期 FROM 98年订单说明： （1）字段名之间的逗号“,”必须是英文字符 （2）如果重命名查询“98年订单”，则本查询中引用的记录源名字将自动更新。 （3）作为记录源的“98年订单”查询不能删除 （4）当真正的记录源“订单”表中的数据更新时，查询的执行结果也自动更新。8.3.2 基于单一记录源的查询 8.3.2 基于单一记录源的查询 2. 用DISTINCT消除重复记录 [例8-3] 若8.3.2 基于单一记录源的查询 8.3.2 基于单一记录源的查询 3. 用TOP显示前面的若干条记录 语法格式：TOP n （n为指定记录数） [例8-4] 显示“订单”表中的前4条记录 SELECT TOP 4 * FROM 订单 8.3.2 基于单一记录源的查询8.3.2 基于单一记录源的查询4. 用AS改变列标题 [例8-5] SELECT 产品ID AS 产品编号, 产品名称, '供应商是' AS 供应商是, 供应商ID AS 供应商编号 FROM 产品;8.3.2 基于单一记录源的查询 8.3.2 基于单一记录源的查询 5. 用WHERE子句对记录进行选择 语法格式： WHERE <表达式> <关系运算符> <表达式> [例8-6] 找单价不低于20元的产品，显示部分字段 SELECT 产品名称,单价 FROM 产品 WHERE 单价>=20 8.3.2 基于单一记录源的查询8.3.2 基于单一记录源的查询WHERE条件表达式内容 算术运算符：+、－、*、/ 逻辑运算符：AND、OR、NOT 比较运算符：=、>=、<=、<> <列名>IS[NOT]NULL：列值是否为空。 <表达式1>[NOT]BETWEEN<表达式2>AND<表达式3>。 <表达式>[NOT]IN(目标列表)：表达式的值是否是目标列表中的一个值。 <列名>[NOT]LIKE<‘字符串’>列值是否包含在‘字符串’中。字符串中可用通配符“?”、和“*”。8.3.2 基于单一记录源的查询 8.3.2 基于单一记录源的查询 可用逻辑运算符形成逻辑表达式。 逻辑运算符的优先级依次为：NOT>AND>OR [例8-7] [例8-8] SELECT 产品名称,单价,类别ID FROM 产品 WHERE 单价>=15 AND 类别ID=3 SELECT 产品名称,单价,类别ID FROM 产品 WHERE 单价>15 AND 类别ID=3 OR 类别ID=2 8.3.2 基于单一记录源的查询 8.3.2 基于单一记录源的查询 6. 用特殊运算符过滤记录 例如，“单价 BETWEEN 10 AND 20”指单价在10~20元之间，包括10元和20元者；而“单价 IN(10,20)”指单价等于10或者20。 8.3.2 基于单一记录源的查询 8.3.2 基于单一记录源的查询 [例8-9] [例8-10] SELECT 产品名称,单价,类别ID,库存量 FROM 产品 WHERE 类别ID IN(1，3) AND 单价 BETWEEN 10 AND 208.3.2 基于单一记录源的查询 8.3.2 基于单一记录源的查询 [例8-11] [例8-12] SELECT * FROM 客户 WHERE 邮政编码 LIKE '*[!0-4]'邮政编码尾数不在0~4 条件也可写成：WHERE 邮政编码 LIKE '*[5-9]'8.3.2 基于单一记录源的查询 8.3.2 基于单一记录源的查询 7. 用ORDER BY子句将记录排序输出 [例8-13] 8.3.3 基于多个记录源的查询 8.3.3 基于多个记录源的查询 1. 用WHERE实现表间关系 [例8-14] 输出产品名称及所属类别名称 SELECT 产品.产品名称,类别.类别名称 FROM 产品,类别 WHERE 产品.类别ID=类别.类别ID查询结果排除： (1)没有产品的类别 (2)无类别ID的产品 说明： (1)不同表的同名字段前要冠以表名 (2)字段名中若有空格，引用时用[ ]括起来 8.3.3 基于多个记录源的查询 8.3.3 基于多个记录源的查询 2. 用内连接INNER JOIN实现表与表的连接 INNER JOIN出现在FROM子句中，格式为： FROM <表1> INNER JOIN <表2> ON <条件表达式> [例8-15]输出产品名称及所属类别名称 SELECT 产品.产品名称,类别.类别名称 FROM 产品 INNER JOIN 类别 ON 产品.类别ID=类别.类别ID8.3.3 基于多个记录源的查询 8.3.3 基于多个记录源的查询 在较长的查询命令中可用给表起别名。 [例8-16] t 作为“产品”表的别名，s 作为“订单明细”表的别名 折扣只有“订单明细”表才有，可以不使用表名 8.3.4 合计、汇总与计算 8.3.4 合计、汇总与计算 1. 合计函数 （1）COUNT函数：统计符合条件的记录 [例8-17] 8.3.4 合计、汇总与计算 8.3.4 合计、汇总与计算 （2）SUM函数：求和。 参与的字段必须为数值类型 [例8-18] 求饮料类产品的总库存量。 SELECT SUM(库存量) AS 饮料库存总计 FROM 产品,类别 WHERE 产品.类别ID=类别.类别ID AND 类别.类别名= '饮料'8.3.4 合计、汇总与计算 8.3.4 合计、汇总与计算 （3）MAX和MIN函数：分别用于在指定的记 录范围内找出具有最大值和最小值的字段。 [例8-19]找出饮料类中的最高价和最低价 SELECT MAX(单价) AS 最高价, MIN(单价) AS 最低价 FROM 产品，类别 WHERE 产品.产品ID=类别.类别ID AND 类别名称= ' 饮料'8.3.4 合计、汇总与计算 8.3.4 合计、汇总与计算 （4）AVG函数：求平均 参与的字段必须为数值类型。 [例8-20]输出百达公司产品被订购的平均价格 SELECT Avg(订单明细.单价) AS 单价之平均值 FROM 供应商 , 产品, 订单明细 WHERE 供应商.供应商ID = 产品.供应商ID AND 产 品.产品ID = 订单明细.产品ID AND 供应商.公 司名称='百达'; “供应商”表与“订单明细”表没有直接联系，必须以“产品”表为桥梁进行组合查询 8.3.4 合计、汇总与计算 8.3.4 合计、汇总与计算 2. 分组查询 将输出记录分成若干组，以字段值相同的记录为一组，配合合计函数进行统计汇总操作。 格式： GROUP BY 分组表达式1[,分组表达式2[,...]] [例8-21] 使用GROUP BY 子句进行分组时，显示的字段只能是参与分组的字段以及带有合计函数计算的字段。8.3.4 合计、汇总与计算 8.3.4 合计、汇总与计算 3. HAVING函数 该函数与GROUP BY子句联合使用，可对分组后要得到的结果进行限制。 [例8-22] 8.3.4 合计、汇总与计算 8.3.4 合计、汇总与计算 4. 计算列 实质是一个表达式，使用方式同字段。 [例8-23]8.3.5 嵌套查询 8.3.5 嵌套查询 嵌套：将第一次查询的结果作为第二次查询的条件 [例8-24]显示所有价格高于平均价格的产品 错误： 正确：SELECT 产品名称,单价 FROM 产品 WHERE 单价>AVG(单价)8.3.5 嵌套查询 8.3.5 嵌套查询 [例8-25] 找出价格最高的产品及其类别名 SELECT 产品名称, 类别.类别名称,单价 FROM 产品,类别 WHERE 产品.类别ID=类别.类别ID AND 单价=(SELECT MAX(单价) FROM 产品)嵌套查询：先找出所有产品中的最高价格 8.4 SQL语言的数据更新功能8.4 SQL语言的数据更新功能1 插入数据 2 修改数据 3 删除数据8.4.1 插入数据8.4.1 插入数据语句格式 INSERT INTO <表名> [(<属性列1>[，<属性列2 >…)] VALUES (<常量1> [，<常量2>] … ) 功能 将新元组插入指定表中。 8.4.1 插入数据8.4.1 插入数据两种插入数据方式 插入单个元组 插入子查询结果 两种插入数据方式 完全添加： INSERT INTO 表名 VALUES (第一个字段值,...,最后一个字段值） 部分添加： INSERT INTO 表名(字段1,…,字段N,…) VALUES (第一个字段值,...,第N个字段值,…)8.4.1 插入数据8.4.1 插入数据 INTO子句 指定要插入数据的表名及属性列 属性列的顺序可与表定义中的顺序不一致 没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致 指定部分属性列：插入的元组在其余属性列上取空值 VALUES子句 提供的值必须与INTO子句匹配 值的个数 值的类型8.4.1 插入数据8.4.1 插入数据[例8-26] 将一个新商品记录 （产品名称:王老吉；供应商ID:1；类别ID:1；单位数量:每箱12罐；单价:36；库存量:50；订购量:30）插入到产品表中。 INSERT INTO 产品 VALUES (‘王老吉’，1，1，‘每箱12罐' ，36 ，50，30)； INSERT INTO 产品（产品名称,供应商,单位数量,单价,库存量,订购量） VALUES (‘王老吉’，1，‘每箱12罐' ，50，30)；8.4.1 插入数据－生成表查询 8.4.1 插入数据－生成表查询 [例8-27]将价格大于50的饮料的全部信息按价格降序生成饮料表。 SQL代码：SELECT 产品.* INTO 饮料 FROM 产品 WHERE 类别ID=1 AND 单价>50 ORDER BY 单价 DESC 8.4.1 插入数据－追加查询 8.4.1 插入数据－追加查询 [例8-28]对应的SQL代码： INSERT INTO 饮料 SELECT 产品.* FROM 产品 WHERE 类别ID=1 AND 单价<=50 ORDER BY 单价 DESC8.4.2 修改数据8.4.2 修改数据语句格式 UPDATE <表名> SET <列名>=<表达式>[，<列名>=<表达式>]… [WHERE <条件>]； 功能 修改指定表中满足WHERE子句条件的元组8.4.2 修改数据8.4.2 修改数据SET子句 指定修改方式 要修改的列 修改后取值 WHERE子句 指定要修改的元组 缺省表示要修改表中的所有元组8.4.2 修改数据8.4.2 修改数据1. 修改某一个元组的值 [例8-29] 将苹果汁的单价改为40元。 UPDATE 产品 SET 单价=40 WHERE 产品名称=‘ 苹果汁 '； 8.4.2 修改数据8.4.2 修改数据2. 修改多个元组的值 [例8-30] 将所有饮料的单价增加1元。 UPDATE 产品 SET 单价= 单价+1 WHERE 类别ID=1； 8.4.3 删除数据8.4.3 删除数据 DELETE FROM <表名> [WHERE <条件>]； 功能 删除指定表中满足WHERE子句条件的元组 WHERE子句 指定要删除的元组 缺省表示要修改表中的所有元组8.4.3 删除数据8.4.3 删除数据[例8-31] 删除产品名称为苹果汁的记录。 DELETE FROM 产品 WHERE 产品名称=‘苹果汁'；8.4.3 删除数据8.4.3 删除数据[例8-32] 删除张颖负责的订单对应的明细信息 DELETE FROM 订单明细 WHERE 订单明细.订单ID IN (SELECT 订单.订单ID FROM 雇员,订单 WHERE 雇员.雇员ID = 订单.雇员ID AND([雇员]![姓氏] & [雇员]![名字])="张颖")