SQL数据库的查询.docx
《SQL数据库的查询.docx》由会员分享,可在线阅读,更多相关《SQL数据库的查询.docx(28页珍藏版)》请在冰点文库上搜索。
SQL数据库的查询
数据库的查询
数据库检索速度的提高是数据库技术发展的重要标志之一。
在数据库的发展过程中,数据检索曾经是一个非常困难的事情,直到使用了SQL之后,数据库的检索才变得相对简单。
对于使用SQL的数据库,检索数据都要使用SELECT语句。
使用SELECT语句,既可以完成简单的单表查询、联合查询,也可以完成复杂的联接查询、嵌套查询。
4.1SELECT语句结构
SELECT语句能够从数据库中检索出符合用户需求的数据,并将结果以表格的形式返回,是SQLServer中使用昀频繁的语句之一。
它功能强大,所以也有较多的子句,包含主要子句的基本语法格式如下:
SELECT列名1[,列名2]...[INTO新表名]FROM表名1[,表名2]...[WHERE条件][GROUPBY列名列表][HAVING条件][ORDERBY列名列表[ASC|DESC]]
其中,用[]表示可选项。
SELECT语句是比较复杂的语句,上述结构还不能完全说明其用法,下面将把它拆分为若干部分详细讲述。
SELECT语句至少包含两个子句:
SELECT和FROM,SELECT子句指定要查询的特定表中的列,FROM子句指定查询的表。
WHERE子句指定查询的条件,GROUPBY子句用于对查询结果进行分组,HAVING子句指定分组的条件,ORDERBY子句用于对查询结果进行排序。
【例4.1】查询员工表中所有员工的姓名和联系电话,可以写为:
SELECT姓名,电话FROMemployees程序执行结果如下:
姓名
电话
李建国
01032147588
李圆圆
01032358697
刘金武
01032298726
万兴国
01032658325
孟全
01058546230
黎美丽
01058964357
冯晓丹
01036571568
王峰
01032987564
陈吉轩
01058796545
(所影响的行数为11行)
4.2基本子句查询
4.2.1SELECT子句
SELECT子句用于指定要返回的列,其完整的语法如下:
SELECT[ALL│DISTINCT][TOPn[PERCENT][WITHTIES]]
列名<列名>:
:
=
{*│{表名│视图名│表的别名}.*│{列名│表达式│IDENTITYCOL│ROWGUIDCOL}[[AS]别名]│别名=表达式
}[,...n]其中,用<>表示在实际编写语句时可以用相应的内容代替,用[,…n]表示重复前面的内容,用{}表示是必选的,用A│B表示A和B只能选择一个。
各参数说明如表4.1所示。
表4.1SELECT子句参数
参数
功能
ALL
显示所有记录,包括重复行,ALL是系统默认的
DISTINCT
如果有相同的列值,只显示其中一个。
此时,空值被认为相等
TOPn[PERCENT]
指明返回查询结果的前n行,如果后面紧跟PERCENT,则返回查询结果的前n%行,若n%为小数则取整
WITHTIES
除返回TOPn[PERCENT]指定的行外,还返回与TOPn[PERCENT],返回的昀后一行记录中由ORDERBY子句指定列值相同的数据行
列名
指明返回结果中的列,如果是多列,用逗号隔开
*
通配符,返回所有列值
续表
参数
功能
{表名│视图名│表别名}.*
限定通配符“*”返回的作用范围
表达式
表达式,可以为列名、常量、函数或它们的组合
IDENTITYCOL
返回标识列
ROWGUIDCOL
返回行全局惟一标识列
列别名
在返回的查询结果中,用此别名替代列的原名。
使用列别名有三种定义方法,分别为:
列名AS列别名;列名列别名;列别名=列名
1.使用通配符“*”,返回所有列值【例4.2】查询员工表中的所有记录,程序为:
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号
1301
冯晓丹
0
销售部
01036571568
北京市育才路78号
1302
王峰
1
销售部
01032987564
北京市沿江路123号
1303
陈吉轩
1
销售部
01058796545
北京市德外大街19号
(所影响的行数为11行)
2.使用DISRINCT关键字消除重复记录【例4.3】查询进货表中所有的生产厂商,去掉重复值,程序为:
SELECTDISTINCT生产厂商FROMgoods程序执行结果如下:
生产厂商
dell公司
TCL公司
惠普公司
佳能公司
联想公司
三星公司
索尼公司
(所影响的行数为7行)
3.使用TOPn指定返回查询结果的前n行记录【例4.4】查询进货表中商品名称、单价和数量的前4条记录,程序为:
SELECTTOP4商品名称,进货价,数量FROMgoods程序执行结果如下:
商品名称进货价数量
打印机
1205.0000
10
液晶显示器
2210.0000
12
数码相机
2380.0000
8
扫描仪
998.0000
16
(所影响的行数为4行)
4.使用列别名改变查询结果中的列名【例4.5】使用列的别名,查询员工表中所有记录的员工编号(别名为number),姓名(别名为name)和电话(别名为telephone),程序为:
SELECT编号ASnumber,name=姓名,电话telephoneFROMemployees程序执行结果如下:
numbernametelephone1001赵飞燕010321984541002刘德发010322987261003李建国010321475881101李圆圆010323586971102刘金武010322987261103万兴国010326583251201孟全010585462301202黎美丽010589643571301冯晓丹010365715681302王峰010329875641303陈吉轩01058796545(所影响的行数为11行)
5.使用列表达式在SELECT子句中可以使用算术运算符对数字型数据列进行加(+)、减(-)、乘(*)、除(/)和取模(%)运算,构造列表达式,获取经过计算的查询结果。
【例4.6】查询各件商品的进货总金额,可以写为:
SELECT商品名称,进货价*数量AS总金额FROMgoods
程序执行结果如下:
商品名称总金额打印机12050.0000液晶显示器26520.0000数码相机19040.0000扫描仪15968.0000笔记本电脑156000.0000MP3播放器8244.0000摄像机35100.0000台式电脑68500.0000CRT显示器4740.0000
(所影响的行数为9行)
4.2.2FROM子句
只要SELECT子句有要查询的列,就必须使用FROM子句指定进行查询的单个或者多个表。
此外,SELECT语句要查询的数据源除了表以外还可以是视图,视图相当于一个临时表,其语法格式如下:
FROM{表名|视图名}[,...n]
当有多个数据源时,可以使用逗号“,”分隔,但是昀多只能有16个数据源。
数据源也可以像列一样指定别名,该别名只在当前的SELECT语句中起作用。
方法为:
数据源名AS别名,或者数据源名别名。
指定别名的好处在于以较短的名字代替原本见名知意的长名。
【例4.7】在Employees表中查询姓名为王峰的员工的联系电话,程序为:
SELECT姓名,电话FROMemployeesAScWHEREc.姓名='王峰'
程序执行结果如下:
姓名电话王峰01032987564
(所影响的行数为1行)
4.2.3WHERE子句
WHERE子句指定查询的条件,限制返回的数据行。
其语法格式如下:
WHERE指定条件
WHERE子句用于指定搜索条件,过滤不符合查询条件的数据记录,使用比较灵活且复杂。
可以使用的条件包括比较运算、逻辑运算、范围、模糊匹配以及未知值等。
表4.2中列出了过滤的类型和用于过滤数据的相应搜索条件。
表4.2过滤的类型与相应搜索条件
过滤类型
搜索条件
比较运算符
=、>、<、>=、<=、<>、!
>、!
<、!
=
逻辑运算符
NOT、AND、OR
字符串比较
LIKE、NOTLIKE
值的范围
BETWEEN、NOTBETWEEN
列的范围
IN、NOTIN
未知值
ISNULL、ISNOTNULL
1.算术表达式使用比较运算符=(等于)、>(大于)、<(小于)、>=(大于等于)、<=(小于等于)、<>(不等于)、!
=(不等于)、!
<(不小于)、!
>(不大于)可以让表中的值与指定值或表达式作比较。
【例4.8】查询笔记本电脑的进货信息,程序为:
SELECT*FROMgoodsWHERE商品名称='笔记本电脑'
【注意】数据类型为char、nchar、varchar、nvarchar、text、datetime和smalldatetime的数据,引用时要用单引号括起来。
【例4.9】查询在2005年1月1日以前销售的商品信息,可以写为:
SELECT商品编号,数量,售出时间FROMsellWHERE售出时间<'2005-1-1'
第4章
数据库的查询
程序执行结果如下:
商品编号
数量
售出时间
2
1
2004-10-1500:
00:
00.000
2
1
2004-10-1600:
00:
00.000
5
2
2004-10-2600:
00:
00.000
(所影响的行数为3行)【例4.10】查询进货总金额小于10000元的商品名称,可以写为:
SELECT商品名称FROMgoodsWHERE进货价*数量<10000
程序执行结果如下:
商品名称MP3播放器CRT显示器
(所影响的行数为2行)
2.逻辑表达式【例4.11】查询2005年1月1日以前进货且进货价大于1000元的商品,可以写为:
SELECT商品名称FROMgoodsWHERE进货时间<'2005-1-1'AND进货价>1000
程序执行结果如下:
商品名称打印机
(所影响的行数为1行)
3.LIKE关键字【例4.12】查询“李”姓员工的基本信息,可以写为:
SELECT*FROMemployeesWHERE姓名LIKE'李%'
程序执行结果如下:
编号姓名性别部门电话地址
1003
李建国
1
采购部
01032147588
北京市民主路6号
1101
李圆圆
0
财务部
01032358697
北京市仁爱路一巷41号
(所影响的行数为2行)
【注意】LIKE只适用数据类型为char、nchar、varchar、nvarchar、datetime、smalldatetime、binary和varbinary的数据,以及特定情况下数据类型为text、ntext和image数据。
匹配符的含义:
%代表任意长度的字符串;_代表任意一个字符;[]指定某个字符的取值范围;[^]指定某个字符要排除的取值范围。
4.BETWEEN关键字【例4.13】查询零售价格为2000元~3000元的所有商品,可以写为:
SELECT商品名称,零售价FROMgoodsWHERE零售价BETWEEN2000AND3000程序执行结果如下:
商品名称零售价液晶显示器2980.0000数码相机3000.0000CRT显示器2200.0000
(所影响的行数为3行)
5.IN关键字
【例4.14】查询打印机、摄像机的进货价格,程序为:
SELECT商品名称,进货价FROMgoodsWHERE商品名称IN('打印机','摄像机')程序执行结果如下:
商品名称进货价打印机1205.0000摄像机5850.0000
(所影响的行数为2行)
6.NULL关键字
【例4.15】查询电话不为空的员工信息,可以写为:
SELECT*FROMemployeesWHERE电话ISNOTNULL
4.2.4ORDERBY子句
ORDERBY子句用于按查询结果中的一列或多列对查询结果进行排序。
其语法格式如下:
ORDERBY列名列表[ASC|DESC]
各参数说明如表4.3所示。
表4.3ORDERBY子句参数
参数
功能
列名列表
指定要排序的列,可以是多列。
如果是多列,系统先按照第一列的顺序排列,当该列出现重复值时,再按照第二列的顺序排列,依此类推。
列的数据类型不能为ntext、text、image型
ASC
指明查询结果按升序排列,是系统默认值
DESC
指明查询结果按降序排列,NULL被认为是昀小的值
【例4.16】查询商品的进货价格并按从大到小排序,程序为:
SELECT商品名称,进货价FROMgoodsORDERBY进货价DESC程序执行结果如下:
商品名称进货价笔记本电脑7800.0000台式电脑6850.0000摄像机5850.0000数码相机2380.0000液晶显示器2210.0000CRT显示器1580.0000打印机1205.0000扫描仪998.0000
MP3播放器458.0000(所影响的行数为9行)【例4.17】按照商品进货数量的升序排序,在同一数量内,将按照进货价的降序排列,程序为:
SELECT商品名称,进货价,数量FROMgoodsORDERBY3,2DESC程序执行结果如下:
商品名称进货价数量
CRT显示器
1580.0000
3
摄像机
5850.0000
6
数码相机
2380.0000
8
台式电脑
6850.0000
10
打印机
1205.0000
10
液晶显示器
2210.0000
12
扫描仪
998.0000
16
MP3播放器
458.0000
18
笔记本电脑
7800.0000
20
(所影响的行数为9行)【注意】ORDERBY子句后面的数字表示排序列在选择列表中的位置。
用于排序的列不一定要出现在选择列表中。
4.2.5INTO子句
INTO子句用于把查询结果存放到一个新建立的表中,新表的列由SELECT子句中指定的列构成,其语法格式如下:
INTO新表名
【例4.18】使用INTO子句创建一个新表,可以写为:
SELECTTOP15PERCENT商品名称,进货价*数量AS总金额INTO金额表FROMgoods
执行以上程序,即可在sales数据库中创建一个新表,表名为金额表。
查询金额表的数据,可以执行程序SELECT*FROM金额表,结果如下:
商品名称总金额
打印机12050.0000液晶显示器26520.0000(所影响的行数为2行)
4.3数据汇总
4.3.1使用聚合函数
聚合函数的功能是对整个表或表中的列组进行汇总、计算,求平均值或总和,常见的聚
合函数及其功能如表4.4所示。
表4.4聚合函数
函数格式
功能
COUNT([DISTINCT|ALL]*)
计算记录个数
COUNT([DISTINCT|ALL]<列名>)
计算某列值个数
AVG([DISTINCT|ALL]*)
计算某列值的平均值
MAX([DISTINCT|ALL]<列名>)
计算某列值的昀大值
MIN([DISTINCT|ALL]<列名>)
计算某列值的昀小值
SUM([DISTINCT|ALL]<列名>)
计算某列值的和
其中,DISTINCT表示在计算时去掉列中的重复值,如果不指定DISTINCT或指定ALL(默认),则计算所有指定值。
COUNT(*)函数计算所有的行的数量,即使每行都包含空值,而COUNT(<列名>)将忽略该列中的空值。
同样,AVG、MAX、MIN和SUM等函数也只对非空值有效。
【例4.19】查询财务部的员工人数,可以写为:
SELECTCOUNT(*)AS人数FROMemployeesWHERE部门='财务部'程序执行结果如下:
人数3(所影响的行数为1行)
【例4.20】查询商品编号为2的商品的销售数量,可以写为:
SELECTSUM(数量)as销售数量FROMsellWHERE商品编号='2'程序执行结果如下:
销售数量
2(所影响的行数为1行)
4.3.2使用GROUPBY子句
GROUPBY子句用来对查询结果进行分组,其语法格式如下:
GROUPBY[ALL]列名列表[WITH{CUBE|ROLLUP}]各参数说明如表4.5所示。
表4.5GROUPBY子句参数
参数
功能
ALL
返回所有的组和结果,甚至是不满足WHERE子句搜索条件的组和结果。
不能和CUBE或ROLLUP同时使用
列名列表
执行分组的列或表达式
WITHCUBE
返回由GROUPBY分组的行外,还包含汇总行。
在查询结果内返回每个可能的组和子组组合的GROUPBY汇总行
WITHROLLUP
返回由GROUPBY分组的行外,还包含汇总行
【例4.21】统计各部门的人数,可以写为:
SELECT部门,COUNT(*)AS人数FROMEmployeesGROUPBY部门程序执行结果如下:
部门
人数
财务部
3
采购部
3
库存部
2
销售部
3
(所影响的行数为4行)【例4.22】对员工表按性别统计各部门人数,可以写为:
SELECT性别,部门,COUNT(部门)FROMEmployeesGROUPBY性别,部门程序执行结果如下:
性别部门人数
0
财务部
1
1
财务部
2
0
采购部
1
1
采购部
2
0
库存部
1
1
库存部
1
0
销售部
1
1
销售部
2
(所影响的行数为8行)
【例4.23】使用WITHCUBE,可以写为:
SELECT性别,部门,COUNT(部门)AS人数FROMEmployeesGROUPBY性别,部门WITHCUBE
程序执行结果如下:
性别
部门
人数
0
财务部
1
0
采购部
1
0
库存部
1
0
销售部
1
0
NULL
4
1
财务部
2
1
采购部
2
1
库存部
1
1
销售部
2
1
NULL
7
NULL
NULL
11
NULL
财务部
3
NULL
采购部
3
NULL库存部2NULL销售部3(所影响的行数为15行)
【例4.24】使用WITHROLLUP,可以写为:
SELECT性别,部门,COUNT(部门)AS人数FROMEmployeesGROUPBY性别,部门WITHROLLUP
程序执行结果如下:
性别
部门
人数
0
财务部
1
0
采购部
1
0
库存部
1
0
销售部
1
0
NULL
4
1
财务部
2
1
采购部
2
1
库存部
1
1
销售部
2
1
NULL
7
NULL
NULL
11
(所影响的行数为11行)
【注意】SELECT后面的每一列数据除了出现在聚合函数中的以外,都必须在GROUPBY子句中应用。
使用WITHCUBE和WITHROLLUP选项都会对分组结果进行汇总,与CUBE不同的是ROLLUP只返回第一个分组列的汇总数据,且更改分组列的顺序会影响结果集中生成的行数。
4.3.3使用HAVING子句
HAVING子句用来指定分组或集合的搜索条件,通常和GROUPBY子句一起使用,其行为与WHERE子句相似,只是WHERE子句作用于表和视图,HAVING子句作用于组。
其语法格式如下:
HAVING指定条件当HAVING与GROUPBYALL一起使用时,HAVING子句替代ALL。
在HAVING子句中不能使用text、image、ntext数据类型。
【例4.25】统计各部门的男性人数,可以写为:
SELECT性别,部门,COUNT(部门)AS人数
FROMEmployeesGROUPBY性别,部门HAVING性别='1'
程序执行结果如下:
性别部门人数
1财务部2
1采购部2
1
库存部
1
1
销售部
2
(所影响的行数为4行)
4.3.4使用COMPUTE和COMPUTEBY子句
有时我们不仅需要知道数据的汇总情况,可能还需要知道详细的数据记录,此时可以使用COMPUTE或COMPUTEBY子句生成明细汇总结果。
COMPUTE子句用于对列进行聚合函数计算并生成汇总值,汇总的结果以附加行的形式出现,其语法格式如下:
COMPUTE{{AVG|COUNT|MAX|MIN|STDEV|STDEVP|VAR|VARP|SUM