1第第4章章 SQL语言语言l4.1 SQL语言概述语言概述l4.2 数据定义功能与约束数据定义功能与约束l4.3 数据操纵功能数据操纵功能l4.4 数据查询功能与视图数据查询功能与视图2查询语句的基本结构查询语句的基本结构lSELECT FROM WHERE GROUP BY HAVING ORDER BY 34.4 数据查询功能与视图数据查询功能与视图l5.4.1 单表查询单表查询l5.4.2 查询条件的设置查询条件的设置l5.4.3 聚合函数聚合函数l5.4.4 分组分组l5.4.5 查询结果的排序查询结果的排序l5.4.6 多表连接查询多表连接查询l5.4.7 子查询子查询l5.4.8 视图视图4单表查询单表查询l数据源只涉及一张表的查询数据源只涉及一张表的查询l选择列选择列5查询语句的结构查询语句的结构lSELECT FROM 6CREATE TABLE 演员演员( 姓名姓名 char ( 20 ), 工号工号 char ( 10 ), 性别性别 char (2), PRIMARY KEY (工号工号 ) )CREATE TABLE 电影电影( 片名片名 char ( 60 ), 导演导演 char ( 20 ), 年代年代 datetime, PRIMARY KEY (片名，年代片名，年代 ) )CREATE TABLE 出演出演( 工号工号 char ( 10 ), 片名片名 char ( 60 ), 年代年代 datetime, 片酬片酬 money, PRIMARY KEY (工号工号,片名片名,年代年代 ) )7姓名姓名工号工号性别性别王菲王菲A010女女宁静宁静A120女女梁朝伟梁朝伟A231男男葛优葛优A751男男片名片名导演导演年代年代重庆森林重庆森林王家卫王家卫1994红河谷红河谷冯小宁冯小宁1998不见不散不见不散冯小刚冯小刚1998花样年华花样年华王家卫王家卫2000工号工号片名片名年代年代片酬片酬A120红河谷红河谷199850kA231花样年华花样年华2000100kA751不见不散不见不散1998120k演员演员电影电影出演出演8查询指定的列查询指定的列l查询演员的姓名与性别查询演员的姓名与性别lSELECT 姓名，性别姓名，性别 FROM 演员演员l查询结果查询结果姓名姓名性别性别王菲王菲女女宁静宁静女女梁朝伟梁朝伟男男葛优葛优男男9查询全部列查询全部列l查询全体演员的记录查询全体演员的记录lSELECT * FROM 演员演员l查询结果查询结果姓名姓名工号工号性别性别王菲王菲A010女女宁静宁静A120女女梁朝伟梁朝伟A231男男葛优葛优A751男男10数据库管理系统中的函数数据库管理系统中的函数lgetdate()取当前的系统日期取当前的系统日期lyear(date)取日期时间型参数取日期时间型参数date的年份的年份lmonth(date)lday(date)l11使用常量列和计算列使用常量列和计算列l查询电影的片名以及拍摄距今时间查询电影的片名以及拍摄距今时间lSELECT 片名片名, 拍摄距今拍摄距今, l year(getdate()-year(年代年代), 年年 FROM 电影电影l查询结果查询结果片名片名重庆森林重庆森林拍摄距今拍摄距今11年年红河谷红河谷拍摄距今拍摄距今7年年不见不散不见不散拍摄距今拍摄距今7年年花样年华花样年华拍摄距今拍摄距今5年年12改变列标题改变列标题l计算列、函数列和常量列的显示结果都计算列、函数列和常量列的显示结果都没有列标题，通过指定列的别名可以改没有列标题，通过指定列的别名可以改变查询结果的列标题变查询结果的列标题l改变列标题的语法：改变列标题的语法： 列名列名 | 表达式表达式 AS 列标题列标题 或：列标题或：列标题 列名列名 | 表达式表达式13改变列标题的示例改变列标题的示例l查询电影的片名以及拍摄距今时间查询电影的片名以及拍摄距今时间lSELECT 片名片名, year(getdate()-year(年代年代)l AS 拍摄距今年限拍摄距今年限 FROM 电影电影l查询结果查询结果片名片名拍摄距今年限拍摄距今年限重庆森林重庆森林11红河谷红河谷7不见不散不见不散7花样年华花样年华514姓名姓名工号工号性别性别王菲王菲A010女女宁静宁静A120女女梁朝伟梁朝伟A231男男葛优葛优A751男男片名片名导演导演年代年代重庆森林重庆森林王家卫王家卫1994红河谷红河谷冯小宁冯小宁1998不见不散不见不散冯小刚冯小刚1998花样年华花样年华王家卫王家卫2000工号工号片名片名年代年代片酬片酬A120红河谷红河谷199850kA231花样年华花样年华2000100kA751不见不散不见不散1998120k演员演员电影电影出演出演15查询结果中相同行被自动消除查询结果中相同行被自动消除l查询数据库中的电影拍摄年代查询数据库中的电影拍摄年代lSELECT year(年代年代) AS 拍摄年代拍摄年代 FROM 电影电影l查询结果查询结果拍摄年代拍摄年代199419982000165.4 数据查询功能与视图数据查询功能与视图l5.4.1 单表查询单表查询l5.4.2 查询条件的设置查询条件的设置l5.4.3 聚合函数聚合函数l5.4.4 分组分组l5.4.5 查询结果的排序查询结果的排序l5.4.6 多表连接查询多表连接查询l5.4.7 子查询子查询l5.4.8 视图视图17查询语句的结构查询语句的结构lSELECT FROM WHERE 18常用查询条件常用查询条件 WHERE子句常用查询条件子句常用查询条件 谓谓 词词 比较（比较运算符）比较（比较运算符） =, , =, , =, =, , =, , =, （或（或!=!=），），NOT+NOT+上述比较运算符上述比较运算符 确定范围确定范围 BETWEEN AND, NOT BETWEEN AND BETWEEN AND, NOT BETWEEN AND 确定集合确定集合 IN, NOT IN IN, NOT IN 字符匹配字符匹配 LIKE, NOT LIKE LIKE, NOT LIKE 空值空值 IS NULL, IS NOT NULL IS NULL, IS NOT NULL 多重条件（逻辑谓词）多重条件（逻辑谓词） AND, OR AND, OR 19查询条件：比较运算查询条件：比较运算1l查询女演员的姓名查询女演员的姓名lSELECT 姓名姓名 FROM 演员演员 WHERE 性别性别女女姓名姓名王菲王菲宁静宁静20查询条件：比较运算查询条件：比较运算2l查询拍摄距今超过查询拍摄距今超过10年电影的片名年电影的片名lSELECT 片名片名 FROM 电影电影 WHERE year(getdate()-year(年代年代) 10l查询结果查询结果片名片名重庆森林重庆森林21字符匹配的条件运算符字符匹配的条件运算符l用于查找指定列中符合匹配模式的元组用于查找指定列中符合匹配模式的元组l列名列名 NOT LIKE l匹配串中可包含如下四种匹配串中可包含如下四种通配符通配符 _ 匹配任意一个字符；匹配任意一个字符； % 匹配匹配0个或多个字符；个或多个字符； 匹配匹配 中的任意一个字符；中的任意一个字符； 不匹配不匹配 中的任意一个字符中的任意一个字符22查询条件的设置查询条件的设置:字符匹配字符匹配 1l查询查询 “张张”“王王”“李李”姓演员的情况姓演员的情况lSELECT 姓名姓名 FROM 演员演员 WHERE 姓名姓名 LIKE 张王李张王李%l查询结果查询结果姓名姓名王菲王菲23查询条件的设置查询条件的设置:字符匹配字符匹配 2l查询姓名第查询姓名第2个字不是个字不是“菲菲”或者或者“优优” 的的演员的情况演员的情况lSELECT 姓名姓名 FROM 演员演员 WHERE 姓名姓名 LIKE _菲优菲优%l查询结果查询结果姓名姓名宁静宁静梁朝伟梁朝伟24查询条件的设置查询条件的设置:字符匹配字符匹配 3l查询姓名由查询姓名由2个字组成的演员的情况个字组成的演员的情况lSELECT 姓名姓名 FROM 演员演员 WHERE 姓名姓名 LIKE _ _l查询结果查询结果姓名姓名王菲王菲宁静宁静葛优葛优25判断空值的条件运算符判断空值的条件运算符l空值（空值（NULL）表示）表示不确定的值不确定的值l判断取值为空的语句格式：判断取值为空的语句格式： 列名列名 IS NULLl判断取值不为空的语句格式：判断取值不为空的语句格式： 列名列名 IS NOT NULL26涉及空值的查询涉及空值的查询l查询有片酬记录的出演情况查询有片酬记录的出演情况lSELECT * FROM 出演出演 WHERE 片酬片酬 IS NOT NULLl查询结果查询结果27多重条件的组织多重条件的组织l在在WHERE子句中可以使用逻辑运算符子句中可以使用逻辑运算符AND和和OR来组成多重条件查询来组成多重条件查询l用用AND连接的条件表示必须全部满足所连接的条件表示必须全部满足所有的条件的元组才被选中有的条件的元组才被选中l用用OR连接的条件表示只要满足其中一个连接的条件表示只要满足其中一个条件的元组即被选中条件的元组即被选中28多重条件查询多重条件查询 1l查询王姓女演员的情况查询王姓女演员的情况lSELECT * FROM 演员演员 WHERE 姓名姓名 LIKE 王王% AND 性别性别女女 l查询结果查询结果29多重条件查询多重条件查询 2l查询王姓演员和全体男演员的情况查询王姓演员和全体男演员的情况lSELECT * FROM 演员演员 WHERE 姓名姓名 LIKE 王王% OR 性别性别男男 l查询结果查询结果30确定范围的条件运算符确定范围的条件运算符l BETWEENAND和和 NOT BETWEENANDl格式格式 列名列名 | 表达式表达式 NOT BETWEEN 下限值下限值 AND 上限值上限值31查询条件的设置查询条件的设置:确定范围确定范围 1l查询拍摄距今查询拍摄距今5至至10年的电影年的电影lSELECT 片名片名 FROM 电影电影 WHERE year(getdate()year( 年代年代) BETWEEN 5 AND 10不见不散不见不散红河谷红河谷花样年华花样年华片名片名l查询结果查询结果32查询条件的设置查询条件的设置:确定范围确定范围 2l查询拍摄距今查询拍摄距今5至至10年的电影年的电影lSELECT 片名片名 FROM 电影电影 WHERE year(getdate()year( 年代年代) = 5 AND year(getdate()year( 年代年代) = 10 33查询条件的设置查询条件的设置:确定范围确定范围 3l查询拍摄距今不到查询拍摄距今不到5年或者年或者10年以上的电影年以上的电影lSELECT 片名片名 FROM 电影电影 WHERE year(getdate()year( 年代年代) NOT BETWEEN 5 AND 10l查询结果查询结果片名片名重庆森林重庆森林34查询条件的设置查询条件的设置:确定范围确定范围 4l查询拍摄距今不到查询拍摄距今不到5年或者年或者10年以上的电影年以上的电影lSELECT 片名片名 FROM 电影电影 WHERE year(getdate()year( 年代年代) 10 35集合的条件运算符集合的条件运算符l IN和和NOT INl格式格式 列名列名 NOT IN （集合）（集合）36查询条件的设置查询条件的设置:确定集合确定集合 1l查询拍摄距今为查询拍摄距今为5，10或者或者15年的电影年的电影lSELECT 片名片名 FROM 电影电影 WHERE year(getdate() - year( 年代年代) IN (5, 10, 15)l查询结果查询结果片名片名花样年华花样年华37查询条件的设置查询条件的设置:确定集合确定集合 2l查询拍摄距今为查询拍摄距今为5，10或者或者15年的电影年的电影lSELECT 片名片名 FROM 电影电影 WHERE year(getdate()year( 年代年代) = 5 OR year(getdate()year( 年代年代) = 10 OR year(getdate()year( 年代年代) = 15 38查询条件的设置查询条件的设置:确定集合确定集合 3l查询拍摄距今年限不是查询拍摄距今年限不是5，10或者或者15的电影的电影lSELECT 片名片名 FROM 电影电影 WHERE year(getdate() - year( 年代年代) NOT IN (5, 10, 15)l查询结果查询结果片名片名重庆森林重庆森林红河谷红河谷不见不散不见不散39查询条件的设置查询条件的设置:确定集合确定集合 4l查询拍摄距今年限不是查询拍摄距今年限不是5，10或者或者15的电影的电影lSELECT 片名片名 FROM 电影电影 WHERE year(getdate()year( 年代年代) != 5 AND year(getdate()year( 年代年代) != 10 AND year(getdate()year( 年代年代) != 15 405.4 数据查询功能与视图数据查询功能与视图l5.4.1 单表查询单表查询l5.4.2 查询条件的设置查询条件的设置l5.4.3 聚合函数聚合函数l5.4.4 分组分组l5.4.5 查询结果的排序查询结果的排序l5.4.6 多表连接查询多表连接查询l5.4.7 子查询子查询l5.4.8 视图视图41使用计算函数汇总数据使用计算函数汇总数据l计算函数计算函数=l集合函数集合函数=l聚合函数聚合函数=l聚集函数聚集函数l对一组值进行计算并返回一个单值对一组值进行计算并返回一个单值42SQL提供的计算函数提供的计算函数 lCOUNT（ * ）: 统计表中元组个数统计表中元组个数lCOUNT（ ）: 统计本列列值个数统计本列列值个数lMAX（ ）：求列值最大值）：求列值最大值lMIN（ ）：求列值最小值）：求列值最小值lSUM（ ）: 计算列值总和计算列值总和lAVG（ ）: 计算列值平均值计算列值平均值43使用计算函数的注意事项使用计算函数的注意事项l在在SELECT，HAVING子句中使用子句中使用l计算函数不能直接出现在计算函数不能直接出现在WHERE子句中子句中l如果使用了计算函数或者分组，那么如果使用了计算函数或者分组，那么 SELECT子子句中的列必须是计算函数或者是分组依据列句中的列必须是计算函数或者是分组依据列lSUM 与与AVG只能用于数值类型的列，参数可以只能用于数值类型的列，参数可以是计算列是计算列l要求只有一个参数要求只有一个参数l除除COUNT（*）外，其他函数在计算过程中均忽）外，其他函数在计算过程中均忽略略NULL值值44出演表例子出演表例子 45COUNT函数的使用函数的使用 1l查询演员出演的总人次数查询演员出演的总人次数l查询演员出演记录的总条数查询演员出演记录的总条数lSELECT COUNT(*) FROM 出演出演lSELECT COUNT(演员姓名演员姓名) FROM 出演出演lSELECT COUNT(片名片名) FROM 出演出演lSELECT COUNT(年代年代) FROM 出演出演 lSELECT COUNT(片酬片酬) FROM 出演出演l查询结果查询结果46COUNT函数的使用函数的使用 2l查询有片酬记录的总条数查询有片酬记录的总条数lSELECT COUNT(片酬片酬) AS 片酬记录条数片酬记录条数lFROM 出演出演l查询结果查询结果47COUNT函数的使用函数的使用 3l查询出演表中不同演员的人数查询出演表中不同演员的人数lSELECT COUNT(DISTINCT 演员姓名演员姓名) AS 演员总数演员总数lFROM 出演出演l查询结果查询结果48SUM函数的使用函数的使用 1l查询出演表中支付的片酬总数查询出演表中支付的片酬总数lSELECT SUM(片酬片酬) AS 总片酬总片酬lFROM 出演出演l查询结果查询结果49SUM函数的使用函数的使用 2l查询梁朝伟的片酬总数查询梁朝伟的片酬总数lSELECT 演员姓名，演员姓名，SUM(片酬片酬) AS 总片酬总片酬lFROM 出演出演lWHERE 演员姓名演员姓名 梁朝伟梁朝伟l查询结果查询结果 列列 出演出演.演员姓名演员姓名 在选择列表中无效，因为在选择列表中无效，因为该列未包含在聚合函数中，并且没有该列未包含在聚合函数中，并且没有 GROUP BY 子句。子句。50SUM函数的使用函数的使用 3l查询梁朝伟的片酬总数查询梁朝伟的片酬总数lSELECT SUM(片酬片酬) AS 梁朝伟的总片酬梁朝伟的总片酬lFROM 出演出演lWHERE 演员姓名演员姓名 梁朝伟梁朝伟l查询结果查询结果51AVG函数的使用函数的使用 1l查询每人次出演支付的片酬平均数查询每人次出演支付的片酬平均数lSELECT AVG(片酬片酬) AS 平均片酬平均片酬lFROM 出演出演l查询结果查询结果52NULL对计算函数的影响对计算函数的影响lSELECT AVG(片酬片酬),l SUM(片酬片酬)/COUNT(片酬片酬),l SUM(片酬片酬)/COUNT(*)lFROM 出演出演l查询结果查询结果53AVG函数的使用函数的使用 2l查询梁朝伟的平均片酬查询梁朝伟的平均片酬lSELECT 演员姓名，演员姓名，AVG(片酬片酬) AS 平均片酬平均片酬lFROM 出演出演lWHERE 演员姓名演员姓名 梁朝伟梁朝伟l查询结果查询结果 列列 出演出演.演员姓名演员姓名 在选择列表中无效，因为该在选择列表中无效，因为该列未包含在聚合函数中，并且没有列未包含在聚合函数中，并且没有 GROUP BY 子句。子句。54AVG函数的使用函数的使用 3l查询梁朝伟的平均片酬查询梁朝伟的平均片酬lSELECT AVG(片酬片酬) AS 梁朝伟的平均片酬梁朝伟的平均片酬lFROM 出演出演lWHERE 演员姓名演员姓名 梁朝伟梁朝伟l查询结果查询结果55MIN与与MAX函数的使用函数的使用 1l查询出演表中支付的最高片酬查询出演表中支付的最高片酬lSELECT MAX(片酬片酬) AS 最高片酬最高片酬lFROM 出演出演l查询结果查询结果56MIN与与MAX函数的使用函数的使用 2l查询最低片酬以及获得最低片酬的演员姓名查询最低片酬以及获得最低片酬的演员姓名lSELECT 演员姓名，演员姓名，MIN(片酬片酬) AS 最低片酬最低片酬lFROM 出演出演l查询结果查询结果 列列 出演出演.演员姓名演员姓名 在选择列表中无效，因为该在选择列表中无效，因为该列未包含在聚合函数中，并且没有列未包含在聚合函数中，并且没有 GROUP BY 子句。子句。57MIN与与MAX函数的使用函数的使用 3l查询最低片酬以及有关演员的姓名查询最低片酬以及有关演员的姓名lSELECT 演员姓名演员姓名lFROM 出演出演lWHERE 片酬片酬 MIN(片酬片酬) l查询结果查询结果 聚合不应出现在聚合不应出现在 WHERE 子句中，除非该聚合子句中，除非该聚合位于位于 HAVING 子句或选择列表所包含的子查子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。询中，并且要对其进行聚合的列是外部引用。585.4 数据查询功能与视图数据查询功能与视图l5.4.1 单表查询单表查询l5.4.2 查询条件的设置查询条件的设置l5.4.3 聚合函数聚合函数l5.4.4 分组分组l5.4.5 查询结果的排序查询结果的排序l5.4.6 多表连接查询多表连接查询l5.4.7 子查询子查询l5.4.8 视图视图59查询语句的结构查询语句的结构lSELECT FROM WHERE GROUP BY HAVING 60对查询结果进行分组计算对查询结果进行分组计算l分组把一个表划分为子集，可以细化计分组把一个表划分为子集，可以细化计算函数的作用对象算函数的作用对象lHAVING子句指定满足条件的组子句指定满足条件的组l在一个查询语句中，可以使用任意多个在一个查询语句中，可以使用任意多个列进行分组列进行分组l分组依据列不能是分组依据列不能是text，ntext，image和和bit类型类型61GROUP BY子句的使用子句的使用l查询每位演员拍片数目以及个人平均片酬，查询每位演员拍片数目以及个人平均片酬，个人最低个人最低/最高片酬最高片酬lSELECT 演员姓名演员姓名，COUNT(片名片名) 拍片数拍片数目，目，AVG(片酬片酬) 个人平均片酬，个人平均片酬， MIN(片酬片酬) 个人最低片酬，个人最低片酬， MAX(片酬片酬) 个人最高片酬个人最高片酬lFROM 出演出演lGROUP BY 演员姓名演员姓名626364上述查询的结果上述查询的结果 65HAVING子句的功能子句的功能lHAVING子句用于对分组后的结果再进子句用于对分组后的结果再进行过滤行过滤lHAVING子句的作用对象是组而不是行子句的作用对象是组而不是行l在在HAVING子句中可以使用计算函数子句中可以使用计算函数lHAVING与与GROUP BY子句一起使用子句一起使用66HAVING子句的使用子句的使用l查询出演不少于查询出演不少于2部电影的演员姓名部电影的演员姓名676869使用使用HAVING子句的查询方案子句的查询方案lSELECT 演员姓名，演员姓名，COUNT(片名片名) 拍片数目拍片数目lFROM 出演出演lGROUP BY 演员姓名演员姓名lHAVING COUNT(*)=270上述查询的执行步骤与结果上述查询的执行步骤与结果l先用先用GROUP BY按按演员姓名演员姓名分组分组l再用再用COUNT(*)统计每组包含的记录条数统计每组包含的记录条数l最后挑选记录条数最后挑选记录条数=2的组，返回对应的的组，返回对应的演员姓名演员姓名和该组记录的条数和该组记录的条数l查询结果查询结果71计算函数与分组的联合使用计算函数与分组的联合使用 1l查询梁朝伟的个人片酬总数与个人平均片酬查询梁朝伟的个人片酬总数与个人平均片酬lSELECT 演员姓名，演员姓名，SUM(片酬片酬) 个人总片酬，个人总片酬， AVG(片酬片酬) 个人平均片酬个人平均片酬lFROM 出演出演lGROUP BY 演员姓名演员姓名lHAVING 演员姓名演员姓名 梁朝伟梁朝伟72查询结果查询结果73计算函数与分组的联合使用计算函数与分组的联合使用 2lSELECT 演员姓名演员姓名, AVG(片酬片酬) 个人平均片酬个人平均片酬lFROM 出演出演lWHERE year(年代年代)100000l查询查询2000年以前个人最低片酬大于年以前个人最低片酬大于100k的演员的的演员的个人平均片酬个人平均片酬7475上述查询的结果上述查询的结果 76计算函数与分组的联合使用计算函数与分组的联合使用 3lSELECT 演员姓名演员姓名, AVG(片酬片酬) 个人平均片酬个人平均片酬lFROM 出演出演lGROUP BY 演员姓名演员姓名lHAVING MIN(片酬片酬)100000 and year(年代年代)2000l查询结果查询结果列列 出演出演.年代年代 在在 HAVING 子句中无效，因为该列既子句中无效，因为该列既不包含在聚合函数中，也不包含在不包含在聚合函数中，也不包含在 GROUP BY 子句子句中。中。l查询查询2000年以前个人最低片酬大于年以前个人最低片酬大于100k的演员的个人的演员的个人平均片酬平均片酬77计算函数与分组的联合使用计算函数与分组的联合使用 4l查询查询2000年以前演员的个人平均片酬年以前演员的个人平均片酬lSELECT 演员姓名演员姓名, AVG(片酬片酬) 个人平均片酬个人平均片酬lFROM 出演出演lGROUP BY 演员姓名演员姓名lWHERE year(年代年代)100000 and year(年代年代)2000lGROUP BY 演员姓名演员姓名l查询结果查询结果聚合不应出现在聚合不应出现在 WHERE 子句中，除非该聚合位于子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。要对其进行聚合的列是外部引用。l查询查询2000年以前个人最低片酬大于年以前个人最低片酬大于100k的演员的个人的演员的个人平均片酬平均片酬795.4 数据查询功能与视图数据查询功能与视图l5.4.1 单表查询单表查询l5.4.2 查询条件的设置查询条件的设置l5.4.3 聚合函数聚合函数l5.4.4 分组分组l5.4.5 查询结果的排序查询结果的排序l5.4.6 多表连接查询多表连接查询l5.4.7 子查询子查询l5.4.8 视图视图80查询语句的结构查询语句的结构lSELECT FROM WHERE GROUP BY HAVING ORDER BY 81对查询结果进行排序对查询结果进行排序 1l排序子句的格式为：排序子句的格式为： ORDER BY ASC | DESC , n l为排序的依据列，可以是列名或列为排序的依据列，可以是列名或列的别名的别名lASC表示对列进行升序排序，表示对列进行升序排序，DESC表示对表示对列进行降序排序列进行降序排序l默认的排序方式为升序排序默认的排序方式为升序排序82对查询结果进行排序对查询结果进行排序 2l指定多个排序列时，首先按最前面的列进指定多个排序列时，首先按最前面的列进行排序行排序l如果排序后存在多个列值相同的记录，则如果排序后存在多个列值相同的记录，则对这些记录依据后续排序列进行排序对这些记录依据后续排序列进行排序83对查询结果进行排序示例对查询结果进行排序示例 1l查询全体演员的情况，按姓名排序查询全体演员的情况，按姓名排序lSELECT * l FROM 演员演员 ORDER BY 姓名姓名l查询结果查询结果84对查询结果进行排序示例对查询结果进行排序示例 2l查询全体演员的情况，先女后男再按姓名排序查询全体演员的情况，先女后男再按姓名排序lSELECT * l FROM 演员演员 ORDER BY 性别性别 DESC, 姓名姓名l查询结果查询结果85分组与排序的联合使用分组与排序的联合使用 1lSELECT 演员姓名演员姓名, AVG(片酬片酬) 个人平均片酬个人平均片酬lFROM 出演出演lWHERE year(年代年代)100000 lORDER BY 演员姓名演员姓名 DESCl查询结果查询结果l查询查询2000年以前个人最低片酬大于年以前个人最低片酬大于100k的演员的个人的演员的个人平均片酬平均片酬86分组与排序的联合使用分组与排序的联合使用 2lSELECT 演员姓名演员姓名, AVG(片酬片酬) 个人平均片酬个人平均片酬lFROM 出演出演lWHERE year(年代年代)100000l查询结果查询结果在关键字在关键字 GROUP 附近有语法错误。附近有语法错误。l查询查询2000年以前个人最低片酬大于年以前个人最低片酬大于100k的演员的个人的演员的个人平均片酬平均片酬875.4 数据查询功能与视图数据查询功能与视图l5.4.1 单表查询单表查询l5.4.2 查询条件的设置查询条件的设置l5.4.3 聚合函数聚合函数l5.4.4 分组分组l5.4.5 查询结果的排序查询结果的排序l5.4.6 多表连接查询多表连接查询l5.4.7 子查询子查询l5.4.8 视图视图88多表连接查询多表连接查询l查询结果来源于多张表查询结果来源于多张表l匹配不同表的公共列，实现多表连接匹配不同表的公共列，实现多表连接l连接的类型连接的类型内连接内连接外连接外连接89连接的格式连接的格式l. .l. BETWEEN . AND .l连接字段：连接谓词中的列名称连接字段：连接谓词中的列名称l连接条件中的各连接字段类型必须是可比的，但连接条件中的各连接字段类型必须是可比的，但名字不必是相同的名字不必是相同的90内连接内连接l最常用的比较运算符是最常用的比较运算符是lFROM 表表1，表，表2 WHERE . . l连接列必须是可比较的连接列必须是可比较的语义相同语义相同91姓名姓名工号工号性别性别王菲王菲A010女女宁静宁静A120女女梁朝伟梁朝伟A231男男葛优葛优A751男男片名片名导演导演年代年代重庆森林重庆森林王家卫王家卫1994红河谷红河谷冯小宁冯小宁1998不见不散不见不散冯小刚冯小刚1998花样年华花样年华王家卫王家卫2000黑骏马黑骏马管虎管虎2000工号工号片名片名年代年代片酬片酬A120红河谷红河谷1998NULLA231花样年华花样年华2000100kA751不见不散不见不散1998120k演员演员电影电影出演出演92内连接的使用内连接的使用 1l查询演员出演电影的情况查询演员出演电影的情况lSELECT 演员演员.姓名，出演姓名，出演.片名，出演片名，出演.年代年代lFROM 演员，出演演员，出演lWHERE 演员演员.工号工号 出演出演.工号工号93姓名姓名性别性别工号工号王菲王菲女女A010宁静宁静女女A120梁朝伟梁朝伟男男A231葛优葛优男男A751工号工号片名片名年代年代片酬片酬A120红河谷红河谷1998NULLA231花样年华花样年华2000100kA751不见不散不见不散1998120k演员演员出演出演查询结果查询结果94内连接的使用内连接的使用 2l查询演员与导演的合作情况查询演员与导演的合作情况lSELECT 姓名姓名,导演导演, 电影电影.片名片名,l year(电影电影.年代年代) 年代年代 lFROM 演员，电影，出演演员，电影，出演lWHERE 演员演员.工号工号=出演出演.工号工号 AND l 出演出演.片名片名=电影电影.片名片名 AND l 出演出演.年代年代=电影电影.年代年代 95片名片名年代年代导演导演重庆森林重庆森林1994王家卫王家卫红河谷红河谷1998冯小宁冯小宁不见不散不见不散1998冯小刚冯小刚花样年华花样年华2000王家卫王家卫黑骏马黑骏马2000管虎管虎工号工号片名片名年代年代片酬片酬A120红河谷红河谷1998NULLA231花样年华花样年华2000100kA751不见不散不见不散1998120k演员演员电影电影出演出演姓名姓名性别性别工号工号王菲王菲女女A010宁静宁静女女A120梁朝伟梁朝伟男男A231葛优葛优男男A75196上述查询的结果上述查询的结果 97内连接的使用内连接的使用3 自连接自连接l查询与王家卫影片同年拍摄的其他导演的影片查询与王家卫影片同年拍摄的其他导演的影片lSELECT 电影电影1.片名，片名， 电影电影1.导演，导演， year（电影（电影1.年代）年代） 年代年代lFROM 电影电影 电影电影1，电影，电影 电影电影2lWHERE 电影电影2.导演导演 = 王家卫王家卫 ANDl 电影电影2.年代年代 = 电影电影1.年代年代 ANDl 电影电影1.导演导演 != 王家卫王家卫98片名片名导演导演年代年代重庆森林重庆森林王家卫王家卫1994红河谷红河谷冯小宁冯小宁1998不见不散不见不散冯小刚冯小刚1998花样年华花样年华王家卫王家卫2000黑骏马黑骏马管虎管虎2000电影电影 1年代年代导演导演片名片名1994王家卫王家卫重庆森林重庆森林1998冯小宁冯小宁红河谷红河谷1998冯小刚冯小刚不见不散不见不散2000王家卫王家卫花样年华花样年华2000管虎管虎黑骏马黑骏马电影电影 2查询结果查询结果99外连接外连接l连接时，输出一方的全部元组和对方的匹配元组连接时，输出一方的全部元组和对方的匹配元组lFROM l 表表1 LEFT | RIGHT OUTER JOIN 表表2 l ON 100左外连接左外连接lFROM l 表表1 LEFT OUTER JOIN 表表2 l ON l输出：表输出：表1全部，表全部，表2满足连接条件元组满足连接条件元组101右外连接右外连接lFROM l 表表1 RIGHT OUTER JOIN 表表2 l ON l输出输出:表表1满足连接条件元组，表满足连接条件元组，表2全部全部102左外连接的使用左外连接的使用 l查询所有演员出演影片的情况，包括没有出演查询所有演员出演影片的情况，包括没有出演记录的演员记录的演员lSELECT 姓名姓名, 片名片名lFROM 演员演员 LEFT OUTER JOIN 出演出演 l ON 演员演员.工号工号=出演出演.工号工号103姓名姓名性别性别工号工号王菲王菲女女A010宁静宁静女女A120梁朝伟梁朝伟男男A231葛优葛优男男A751工号工号片名片名年代年代片酬片酬A120红河谷红河谷1998NULLA231花样年华花样年华2000100kA751不见不散不见不散1998120k演员演员出演出演查询结果查询结果104右外连接的使用右外连接的使用 l查询所有演员出演影片的情况，包括没有出演查询所有演员出演影片的情况，包括没有出演记录的演员记录的演员lSELECT 姓名姓名, 片名片名lFROM 出演出演 RIGHT OUTER JOIN 演员演员 l ON 演员演员.工号工号=出演出演.工号工号105工号工号姓名姓名性别性别A010王菲王菲女女A120宁静宁静女女A231梁朝伟梁朝伟男男A751葛优葛优男男片名片名年代年代片酬片酬工号工号红河谷红河谷1998NULLA120花样年华花样年华2000100kA231不见不散不见不散1998120kA751演员演员出演出演查询结果查询结果1065.4 数据查询功能与视图数据查询功能与视图l5.4.1 单表查询单表查询l5.4.2 查询条件的设置查询条件的设置l5.4.3 聚合函数聚合函数l5.4.4 分组分组l5.4.5 查询结果的排序查询结果的排序l5.4.6 多表连接查询多表连接查询l5.4.7 子查询子查询l5.4.8 视图视图107子查询子查询lSQL的的查询块查询块：一个：一个SELECTFROMWHERE的组合体的组合体l子查询子查询内层查询内层查询：嵌套在：嵌套在SELECT、UPDATE或或DELETE等语句中的查询块等语句中的查询块l主查询主查询外层查询外层查询：包含子查询的语句：包含子查询的语句108子查询的用法子查询的用法l子查询要写在圆括号中子查询要写在圆括号中l查询块中不能使用查询块中不能使用ORDER BY子句子句l引入子查询的保留字为引入子查询的保留字为INl子查询通常用在外层查询的子查询通常用在外层查询的WHERE子句子句或或HAVING子句中子句中109子查询的用法子查询的用法l带有带有IN谓词的子查询谓词的子查询 l带有比较运算符的子查询带有比较运算符的子查询l带有带有ANY（SOME）或）或ALL谓词的子查询谓词的子查询l带有带有EXISTS谓词的子查询谓词的子查询110姓名姓名工号工号性别性别王菲王菲A010女女宁静宁静A120女女梁朝伟梁朝伟A231男男葛优葛优A751男男片名片名导演导演年代年代重庆森林重庆森林王家卫王家卫1994红河谷红河谷冯小宁冯小宁1998不见不散不见不散冯小刚冯小刚1998花样年华花样年华王家卫王家卫2000黑骏马黑骏马管虎管虎2000工号工号片名片名年代年代片酬片酬A120红河谷红河谷1998NULLA231花样年华花样年华2000100kA751不见不散不见不散1998120k演员演员电影电影出演出演111子查询的使用子查询的使用 l查询片酬最低的演员姓名查询片酬最低的演员姓名lSELECT 演员演员.姓名姓名lFROM 演员演员, 出演出演lWHERE 演员演员.工号工号=出演出演.工号工号 ANDl 片酬片酬 IN ( SELECT MIN (片酬片酬) l FROM 出演出演 )112姓名姓名工号工号性别性别王菲王菲A010女女宁静宁静A120女女梁朝伟梁朝伟A231男男葛优葛优A751男男片名片名导演导演年代年代重庆森林重庆森林王家卫王家卫1994红河谷红河谷冯小宁冯小宁1998不见不散不见不散冯小刚冯小刚1998花样年华花样年华王家卫王家卫2000黑骏马黑骏马管虎管虎2000工号工号片名片名年代年代片酬片酬A120红河谷红河谷1998NULLA231花样年华花样年华2000100kA751不见不散不见不散1998120k演员演员电影电影出演出演113查询结果查询结果114 WHERE Sage ANY（ ） AND Sdept IS ORDER BY Sage DESC 例（例（带带ANY或或ALL谓词的子查询）找出比谓词的子查询）找出比IS系至系至少一名学生年龄小的其他系学生名单。少一名学生年龄小的其他系学生名单。 SELECT Sname，Sage，Sdept FROM StudentSELECT distinct Sage FROM Student WHERE Sdept=IS115WHERE （ Sage ALL（ ） ） AND （ Sdept IS ）ORDER BY Sage DESC 例例 查询比查询比IS系系所有所有学生年龄学生年龄都小都小的其他各系学生的其他各系学生姓名姓名, 年龄，系名。年龄，系名。SELECT Sage FROM Student WHERE Sdept=IS SELECT Sname，Sage，Sdept FROM Student116EXISTS的使用的使用 l由由EXISTS引出的子查询，其目标列表达式通常引出的子查询，其目标列表达式通常都用都用* ，因为带，因为带EXISTS的子查询只返回真值或的子查询只返回真值或假值，给出列名无实际意义假值，给出列名无实际意义lEXISTSl若内层查询结果非空，则外层的若内层查询结果非空，则外层的WHERE子句子句返回真值返回真值l若内层查询结果为空，则外层的若内层查询结果为空，则外层的WHERE子句子句返回假值返回假值117l查询所有选修了查询所有选修了1号课程的学生姓名号课程的学生姓名l用嵌套查询用嵌套查询l SELECT Snamel FROM Studentl WHERE EXISTSl (SELECT *l FROM SCl WHERE Sno=Student.Sno AND Cno= 1 )118l查询所有选修了查询所有选修了1号课程的学生姓名号课程的学生姓名l用连接查询用连接查询l SELECT SnamelFROM Student, SClWHERE Student.Sno=SC.Sno AND l SC.Cno= 1119l查询选修了全部课程的学生姓名查询选修了全部课程的学生姓名lSELECT Snamel FROM Studentl WHERE NOT EXISTSl （SELECT * FROM Coursel WHERE NOT EXISTSl (SELECT * FROM SCl ） ）集合查询集合查询120121 例例1 查找选修了课程查找选修了课程1或课程或课程2的学生学号和姓名。的学生学号和姓名。 SELECT SC.Sno, Sname FROM SC, Student WHERE (Cno=2) AND (SC.Sno =Student.Sno) SELECT SC.Sno, Sname FROM SC, Student WHERE (Cno=1) AND (SC.Sno =Student.Sno) UNION集合查询就是对集合查询就是对几个查询块的结果集几个查询块的结果集进行并、进行并、交、差运算。交、差运算。122 例例1 查找选修了课程查找选修了课程1或课程或课程2的学生学号和姓名。的学生学号和姓名。 SELECT SC.Sno, Sname FROM SC, Student WHERE ( (Cno=1) OR (Cno=2) AND (SC.Sno =Student.Sno)集合查询就是对集合查询就是对几个查询块的结果集几个查询块的结果集进行并、进行并、交、差运算。交、差运算。123 AND Sno IN （SELECT Sno FROM SC WHERE Cno =2） 例例2 查找既选修课程查找既选修课程1，又选修课程，又选修课程2的学生集合的学生集合 这实际上是查找选修了课程这实际上是查找选修了课程1学生，与选修了学生，与选修了课程课程2 的学生的的学生的交集交集。SELECT SnoFROM SCWHERE Cno =1124 INTERSECT ( SELECT Sno FROM SC WHERE Cno =2) 例例2 查找既选修课程查找既选修课程1，又选修课程，又选修课程2的学生集合的学生集合 这实际上是查找选修了课程这实际上是查找选修了课程1学生，与选修了学生，与选修了课程课程2 的学生的的学生的交集交集。(SELECT SnoFROM SCWHERE Cno =1)125 AND Sno NOT IN（ ） 例例3 查找选修了课程查找选修了课程1，但不选修课程，但不选修课程2的学生。的学生。 这实际上是查找选修课程这实际上是查找选修课程1的学生，与选修课的学生，与选修课程程2的学生的的学生的差集差集： SELECT Sno FROM SC WHERE Cno=1SELECT SnoFROM SC WHERE Cno =2126 EXCEPT (SELECT Sno FROM SC WHERE Cno=2) 例例3 查找选修了课程查找选修了课程1，但不选修课程，但不选修课程2的学生。的学生。 这实际上是查找选修课程这实际上是查找选修课程1的学生，与选修课的学生，与选修课程程2的学生的的学生的差集差集： (SELECT Sno FROM SC WHERE Cno=1)1275.4 数据查询功能与视图数据查询功能与视图l5.4.1 单表查询单表查询l5.4.2 查询条件的设置查询条件的设置l5.4.3 聚合函数聚合函数l5.4.4 分组分组l5.4.5 查询结果的排序查询结果的排序l5.4.6 多表连接查询多表连接查询l5.4.7 子查询子查询l5.4.8 视图视图128视图概念的示意图视图概念的示意图 基本表基本表1基本表基本表2视图视图129视图视图l基本表：基本表：CREAT TABLE语句创建的表语句创建的表l基本表中的数据存储在磁盘上基本表中的数据存储在磁盘上l视图视图：从基本表中选取出来的数据组成：从基本表中选取出来的数据组成的的逻辑窗口逻辑窗口l视图包含的数据存放在基本表中，数据视图包含的数据存放在基本表中，数据库中只存放视图的定义库中只存放视图的定义l视图可以建立在基本表上，也可以建立视图可以建立在基本表上，也可以建立在其他的视图上在其他的视图上130定义视图的语法定义视图的语法lCREATE VIEW (视图列名表视图列名表)lAS 子查询语句子查询语句l子查询通常不包含子查询通常不包含ORDER BY和和DISTINCT子句子句l视图列名或者全部指定，或者全部省略。省略时，视图列名或者全部指定，或者全部省略。省略时，视图的列名与子查询列名相同。视图的列名与子查询列名相同。l以下三种情况必须明确指定组成视图的所有列名：以下三种情况必须明确指定组成视图的所有列名：某个目标列是计算函数或列表达式；某个目标列是计算函数或列表达式；多表连接时选出了几个同名列作为视图的字段；多表连接时选出了几个同名列作为视图的字段；需要在视图中为某个列选用新的更合适的列名需要在视图中为某个列选用新的更合适的列名131视图的定义示例视图的定义示例 l把在把在1998年拍摄过电影的演员情况定义为视图年拍摄过电影的演员情况定义为视图lCREATE VIEW 出演出演1998 ASl SELECT 姓名姓名, 性别性别, 片名片名l FROM 演员演员, 出演出演l WHERE 演员演员.工号工号=出演出演.工号工号 ANDl 出演出演.工号工号 IN ( SELECT 工号工号 l FROM 出演出演 l WHERE year(年代年代)=1998 )132视图出演视图出演1998的显示数据的显示数据 133使用视图的查询使用视图的查询l查询在查询在1998年拍摄过电影的女演员的情年拍摄过电影的女演员的情况况lSELECT *lFROM 出演出演1998lWHERE 性别性别=女女l查询结果查询结果134删除视图的语法删除视图的语法lDROP VIEW l例如，删除视图例如，删除视图出演出演1998lDROP VIEW 出演出演1998135视图的作用视图的作用l简化数据查询语句简化数据查询语句l使用户能从多角度看到同一数据使用户能从多角度看到同一数据l提高了数据的安全性提高了数据的安全性l提供了一定程度的逻辑独立性提供了一定程度的逻辑独立性