第4章 数据库的查询

第4章数据库的查询学习目标理解查询的机制使用SELECT语句进行条件查询在查询中返回限制行、进行查询排序掌握多表连接查询掌握在查询中使用数据汇总函数什么是查询？客户程序查询请求查询结果集SQLSERVERABCDEFG

select*fromemployees查询产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正存储，每次执行查询只是现从数据表中提取数据，并按照表的形式显示出来。编号姓名性别部门电话地址1001赵飞燕0采购部01032198454北京市南京东路55号1002刘德发1采购部01032298726北京市建国路101号1003李建国1采购部01032147588北京市民主路6号1101李圆圆0财务部01032358697北京市仁爱路一巷41号1102刘金武1财务部01032298726北京市建国路101号1103万兴国1财务部01032658325北京市南大街南巷250号1201孟全1库存部01058546230北京市南大街南巷115号1202黎美丽0库存部01058964357北京市教育路32号select*fromemployees

where性别=01001赵飞燕0采购部01032198454北京市南京东路55号1101李圆圆0财务部01032358697北京市仁爱路一巷41号1202黎美丽0库存部01058964357北京市教育路32号思考题既然查询得到的“结果集”的结构类似于一张表，那么可以在“结果集”上继续进行查询吗？如在执行语句select*fromemployees

where性别=0后，能否进一步在结果集中查询库存部的雇员？select*from(select*fromemployeeswhere

性别=0)astable1where

部门='库存部'select列名1[,列名2]...[into新表名]from表名1[,表名2]...[where条件][groupby列名列表][having条件][orderby列名列表[asc|desc]]列名select编号，姓名，性别，部门select

编号，姓名，性别，部门fromemployees

where性别=0

orderby

姓名表fromemployees过滤条件where

性别=0排序条件orderby

姓名select子句使用通配符“*”，返回所有列值使用DISTINCT关键字消除重复记录使用TOPn指定返回查询结果的前n行记录使用列别名改变查询结果中的列名select*fromemployeesselectdistinct生产厂商fromgoodsselecttop4商品名称,进货价,数量fromgoodsselecttop20percent商品名称,进货价,数量fromgoodsselect编号asnumber,name=姓名,电话telephonefromemployees使用列表达式select

商品名称,进货价*数量

as

总金额fromgoodsfrom子句使用FROM子句指定进行查询的单个或者多个表。在Employees表中查询姓名为王峰的员工的联系电话，程序为：select

姓名,电话fromemployeesas

c

where

c.姓名='王峰'where子句where子句用于指定搜索条件，过滤不符合查询条件的数据记录。过滤类型搜索条件比较运算符=、>、<、>=、<=、<>、!>、!<、!=逻辑运算符not、and、or字符串比较like、notlike值的范围between、notbetween列的范围in、notin未知值isnull、isnotnull1．算术表达式2．逻辑表达式select*fromgoodswhere

商品名称='笔记本电脑'select商品编号,数量,售出时间fromsellwhere

售出时间<'2005-1-1'select商品名称fromgoodswhere

进货价*数量<10000select商品名称fromgoods

where

进货时间<'2005-1-1'and

进货价>10003．like关键字可以与like相匹配的符号的含义如下%代表任意长度的字符串；_代表任意一个字符；[]指定某个字符的取值范围，例如[a-e]指集合[abcde]内的任何单个字符；[^]指定某个字符要排除的取值范围，例如[^a-e]指不在集合[abcde]内的任何单个字符。select*fromemployeeswhere

姓名like'李%'4．between关键字5．in关键字6．null关键字select商品名称,零售价fromgoodswhere

零售价between2000and3000select商品名称,进货价fromgoodswhere

商品名称in('打印机','摄像机')select*fromemployeeswhere电话isnotnull对数据进行排序：orderbyorderby用于按查询结果中的一列或多列对查询结果进行排序。单列排序多列排序select商品名称,进货价

fromgoodsorderby

进货价desc

select商品名称,进货价,数量fromgoodsorderby3

,

2desc

数据汇总使用聚合函数使用groupby子句使用having子句使用compute和computeby子句使用聚合函数聚合函数的功能是对整个表或表中的列组进行汇总、计算，求平均值或总和。函数格式搜索条件count([distinct|all]*)计算记录个数count([distinct|all]]<列名>)计算某列值个数avg([distinct|all]<列名>)计算某列值的平均值max([distinct|all]<列名>)计算某列值的最大值min([distinct|all]<列名>)计算某列值的最小值sum([distinct|all]<列名>)计算某列值的和示例查询财务部的员工人数selectcount(*)as人数fromemployeeswhere部门='财务部'查询编号为1301的员工销售的商品总数量和最大一次销售数量select'1301'as

员工编号,sum(数量)

as

销售总数量,max(数量)

as

最大一次销售量fromsellwhere

售货员工编号='1301'使用groupby子句groupby子句用来对查询结果进行分组，其语法格式如下：groupby[all]列名列表[with{cube|rollup}]参数搜索条件all返回所有的组和结果，甚至是不满足where子句搜索条件的组和结果。不能和cube或rollup同时使用列名列表执行分组的列或表达式withcube返回由groupby分组的行外，还包含汇总行。在查询结果内返回每个可能的组和子组组合的groupby汇总行withrollup返回由groupby分组的行外，还包含汇总行统计各个部门的人数select部门,count(*)as人数fromemployeesgroupby部门分别统计各个部门男性女性的人数select性别,部门,count(部门)fromemployeesgroupby性别,部门使用withcube汇总使用withrollup汇总select性别,部门,count(部门)as人数fromemployees

groupby

性别,部门withcubeselect性别,部门,count(部门)as人数fromemployeesgroupby

性别,部门withrollupwithcubewithrollup注意select后面的每一列数据除了出现在聚合函数中的以外，都必须在groupby子句中应用。使用withcube和withrollup选项都会对分组结果进行汇总，与cube不同的是rollup只返回第一个分组列的汇总数据，且更改分组列的顺序会影响结果集中生成的行数。使用having子句having子句用来指定分组或集合的搜索条件，通常和groupby子句一起使用，其行为与where子句相似，只是where子句作用于表和视图，having子句作用于分组。其语法格式如下：当having与groupbyall一起使用时，having子句替代all。在having子句中不能使用text、image、ntext数据类型having指定条件统计各部门的男性人数select性别,部门,count(部门)as人数

fromemployeesgroupby性别,部门

having

性别='1'select性别,部门,count(部门)as人数

fromemployeeswhere

性别='1'groupby性别,部门

统计男员工人数少于两人的部门及人数select性别,部门,count(部门)as人数

fromemployeesgroupby性别,部门

having

性别='1’and

count(部门)<2select性别,部门,count(部门)as人数

fromemployeesWhere

性别='1’and

count(部门)<2groupby性别,部门

where子句从数据源中去掉不符合其搜索条件的数据groupby子句搜集数据行到各个组中，统计函数为各个组计算统计值having子句去掉不符合其组搜索条件的各组数据行使用compute和computeby子句有时我们不仅需要知道数据的汇总情况，可能还需要知道详细的数据记录，此时可以使用compute或computeby子句生成明细汇总结果。compute子句用于对列进行聚合函数计算并生成汇总值，汇总的结果以附加行的形式出现，其语法格式如下：compute{{avg|count|max|min|stdev|stdevp|var|varp|sum}(列名1)}[,...n][by列名1[,...n]]统计销售总数量，同时显示详细的数据记录分别统计各个员工的销售总数量，同时显示详细的数据记录select售货员工编号,商品编号,数量fromsellcompute

sum(数量)select售货员工编号,商品编号,数量fromsellorderby

售货员工编号

compute

sum(数量)by

售货员工编号多表联接查询联接查询分为五种类型：内联接、左外联接、右外联接、完全外部联接和交叉联接。数据库的设计原则是精简，通常是各个表中存放不同的数据，最大限度的减少数据库冗余数据。而实际工作中，往往需要从多个表中查询出用户需要的数据并生成单个的结果集，这就是联接查询。联接查询的语法格式ANSI联接语法格式如下SQLServer联接语法格式如下：select表名.列名1[,...n]from{表名1[联接类型]join表名2on

联接条件}[,...n]where

查询条件select表名.列名1[,...n]from表名1[,...n]where{查询条件

and|or

联接条件}[,...n]多表联接查询注意事项多表联接查询实际上是通过各个表之间共同列的关联性来查询数据，使用时应该注意以下几点。尽可能以表的主键和外键来指定联接条件，如果以各表共同拥有的列来指定联接，则这些列的数据类型必须相同或相兼容。如果所要联接的表具有相同的列名，则在引用这些列的时候，必须指定其表名，格式为：表名.列名参与联接查询的表的个数越多，SQLServer处理这个查询所花费的时间就越长，所以应该尽量限制表的个数。1．内联接所谓内联接指的是返回参与联接查询的表中所有匹配的行，在ANSI联接形式中使用关键字INNERJOIN表示。例查询销售商品的编号、名称和销售数量，使用ANSI联接形式（INNERJOIN）完成。select

销售编号,商品名称,sell.数量as销售数量

fromgoodsinnerjoinsellon

goods.商品编号=sell.商品编号2．左外联接所谓左外联接指的是返回参与联接查询的表中所有匹配的行和所有来自左表的不符合指定条件的行。在右表中对应于左表无记录的部分用null值表示。在ANSI联接形式中使用关键字left[outer]join表示，而在SQLServer联接形式中使用运算符“*=”表示。查询销售商品的编号、名称和销售数量，使用左外联接（left[outer]join）完成。select

销售编号,商品名称,sell.数量

as

销售数量

fromgoodsleftjoinsellongoods

.

商品编号=

sell

.

商品编号SQLServer联接形式3．右外联接所谓右外联接指的是返回参与联接查询的表中所有匹配的行和所有来自右表的不符合指定条件的行。在左表中对应于右表无记录的部分用NULL值表示。在ANSI联接形式中使用关键字right[outer]join表示，而在SQLServer联接形式中使用运算符“=*”表示。select

销售编号,商品名称,sell.数量

as

销售数量

fromgoodsright

joinsellongoods

.

商品编号=

sell

.

商品编号SQLServer联接形式4．完全外部联接所谓完全外部联接指的是返回联接的两个表中的所有相应记录，无对应记录的部分用NULL值表示。完全外部联接的语法只有ANSI语法格式一种，使用关键字full[outer]join表示。select

销售编号,商品名称,sell.数量

as

销售数量

fromgoodsfulljoinsellongoods

.

商品编号=

sell

.

商品编号5．交叉联接所谓交叉联接指的是返回两个表交叉查询的结果。它返回两个表联接后的所有行（即返回两个表的笛卡尔积），不需要用ON子句来指定两个表之间任何连接的列。select

销售编号,商品名称,sell.数量

as

销售数量

fromgoodscrossjoinsell4.5联合查询联合查询是指将多个SELECT语句返回的结果通过UNION操作符组合到一个结果集中。参与查询的SELECT语句中的列数和列的顺序必须相同，数据类型也必须兼容。其语法格式如下：all是指查询结果包括所有的行，如果不使用all，则系统自动删除重复行。联合查询中，查询结果的列标题是第一个查询语句中的列标题。如果希望结果集中的行按照一定的顺序排列，则必须在最后一个有union操作符的语句中使用orderby子句指定排列方式，且使用第一个查询语句中的列名、列标题或列序号。select语句1union[all]select语句2例联合查询进货员工和销售员工的编号select

售货员工编号

as

业务员

fromsellunion

select

进货员工编号

fromgoods4.6嵌套查询嵌套查询是指在一个select语句的where子句或having子句中，又嵌套有另外一个select语句的查询。嵌套查询中上层的select语句块称为父查询或外层查询，下层的select语句块称为子查询或内层查询。在