管理信息系统数据库查询.docx
《管理信息系统数据库查询.docx》由会员分享,可在线阅读,更多相关《管理信息系统数据库查询.docx(25页珍藏版)》请在冰豆网上搜索。
管理信息系统数据库查询
数据查询
在关系数据库中查询数据时,只需执行一个SQL查询语句,通过各种查询条件,就可以得到一个从数据库中返回的查询结果,这个查询结果的内容来自该数据库中的一张或几张表。
数据查询是SQL语言的主要功能之一。
基本查询语句
完整的SQL查询语句为:
SELECT[ALL|DISTINCT]<选择字段表>[,<选择字段表>]……
FROM<表名或视图名>[,<表名或视图名>]……
[WHERE<查找条件表达式>]
[GROUPBY<字段名>[HAVING<查找条件表达式>]]
[ORDERBY<字段名>[AORDERS|DEORDERS]]
SELECT语句根据WHERE子句的查找条件表达式,通过匹配从FROM子句指定的基本表或视图中找出满足条件的记录,再按照选择字段表,返回相应的字段值形成结果集。
结果集中的一条条记录有时也被形象地称为“行”。
GROUPBY子句将结果按BY后的<字段名>进行分组。
如果HAVING子句<查询目标表达式>中包含聚合函数,则计算每组的汇总值。
如果GROUP子句带HAVING短语,则只有满足指定条件的组才会输出。
ORDERBY子句将结果集中的数据按升序或降序排列。
该查询为无条件查询,查询语句中只使用SELECT、FROM子句,查询结果是CUSTOMERS表中指定字段表的所有值。
NULL
例1的查询结果
如果<选择字段表>写为*,则会返回表中所有的字段。
【例2】查询全体客户的详细订单表。
SELECT*FROMORDERS
2
例2的查询结果
【例3】更改商品表PRODUCTS商品编号PDESC为“描述”。
SELECTPNO,PNAME,PCNO,PRICE,‘描述‘=PDESC
FROMPRODUCTS
如果指定的字段名不是标准的标识符格式时,应使用引号定界符。
另外,选择字段表的表达式还可以是一个计算表达式,通过计算得到用户所需要的数据。
100%进口原料
例3的查询结果
【例4】查询客户的出生年份。
SELECTCNAME,CSEX,‘出生年份’=2006-CAGE
FROMCUSTOMERS
WHERECSEX=’女'
其中:
'出生年份'不是原表的字段名,需加上引号,用来指明其字段名称。
而"2006-CAGE"则为计算表达式,用来计算客户的出生年份。
1981
例4的查询结果
SELECT子句中有两个可选参数ALL和DISTINCT。
DISTINCT返回的查询结果会删除其中的重复项,而使用ALL不会删除重复项,仅仅将重复项分开显示。
ALL是默认的参数选项。
1003
带DISTINCT属性的查询结果
1001
带ALL属性的查询结果
【例5】执行语句
SELECTPCNO
FROMPRODUCTS
WHEREPNO<200005
默认为ALL,查询结果见表。
如果想删除返回表中的重复项,则需将执行语句改为
SELECTDISTINCTPCNO
FROMPRODUCTS
WHEREPNO<200005
加了“DISTINCT”关键字的查询结果见表。
【例6】在客户情况表CUSTOMERS和订单表ORDERS中都存在客户编号CNO字段,在查询两个表中的CNO时应使用下面语句格式加以限定:
SELECTCNAME,CUSTOMERS.CNO,PNO
FROMCUSTOMERS,ORDERS
WHERECUSTOMERS.CNO=ORDERS.CNO
用<表名>.<字段名>这种格式可以区分不同表中的同名字段。
CUSTOMERS.CNO表示CUSTOMERS表中的CNO字段,ORDERS.CNO则表示ORDERS表中的CNO字段,通过这种办法就可以将两个属于不同表的相同字段分开。
FROM子句还可以为基本表或视图指定别名。
用法为:
FROM<表名或视图名>[as],<别名>
【例7】将例6中的语句用表的别名格式表示。
SELECTCNAME,First.CNO
FROMCUSTOMERSasFirst,ORDERSasSecond
WHEREFirst.CNO=Second.CNO
200002
50007
例6的查询结果
例7的查询结果
WHERE子句可包括各种条件运算符。
NOT、AND、OR
WHERE子句中的条件运算符
【例8】查询所有28岁的客户姓名。
SELECTCNAME
FROMCUSTOMERS
WHERECAGE=28
查询结果仅包含一条记录:
“吴丹”。
注意:
WHERE子句的查询条件里的不等于符号为"<>",而不是"!
="。
【例9】查询所有不是“VIP会员”的客户信息。
SELECT*
FROMCUSTOMERS
WHERECRANK<>'VIP会员'
NULL
例9的查询结果
【例10】查询所有价格低于30元的商品的编号、名称和价格。
SELECTPNO,PNAME,PRICE
FROMPRODUCTS
WHEREPRICE<30
【例11】查询所有年龄小于27岁的客户编号、姓名和年龄。
SELECTCNO,CNAME,CAGE
FROMCUSTOMERS
WHERECAGE<27
25
20.50
例11的查询结果
例10的查询结果
【例12】查询所有年龄在26岁与31岁(包括26岁和31岁)之间的客户的姓名、年龄、性别
SELECTCNAME,CAGE,CSEX
FROMCUSTOMERS
WHERECAGEBETWEEN26AND31
VIP会员
女
例13的查询结果
例12的查询结果
【例13】查询“VIP会员”、“普通会员”的客户编号、姓名和会员类别。
SELECTCNO,CNAME,CRANK
FROMCUSTOMERS
WHERECRANKIN('VIP会员','普通会员')
【例14】查询所有商品分类编号为“1001”、“1002”的商品的编号、名称和分类号。
SELECTPNO,PNAME,PCNO
FROMPRODUCTS
WHEREPCNOIN('1001','1002')
NOTIN的功能与IN相反,用于用户查找不属于指定字段表的内容。
IN后面跟的是一个字段表,用户只需定义字段表的内容就可以得到自己所需的查找内容。
例15的查询结果
例14的查询结果
模式运算符判断字段值是否与指定的字符串格式相匹配,可用于CHAR、VARCHAR、TEXT、NTEXT、DATETIME和SMALLDATETIME等类型查询。
金牌会员
1002
【例15】查询所有非“VIP会员”、“普通会员”的客户编号、姓名和会员类别。
SELECTCNO,CNAME,CRANK
FROMCUSTOMERS
WHERECRANKNOTIN('VIP会员','普通会员')
模式运算符判断字段值是否与指定的字符串格式相匹配,可用于CHAR、VARCHAR、TEXT、NTEXT、DATETIME和SMALLDATETIME等类型查询。
正则表达式:
用于匹配的匹配串可以是一个含有合法符号的字符串如:
“abcd”,也可以是含有通配符的字符串。
百分号“%”可匹配任意类型和长度的字符,其长度也可以为0。
如:
c%e表示以c开头,以e结尾的任意长度的字符串,“come”,“cope”,“combe”等都满足该匹配。
下划线“_”匹配单个任意字符,常用来限制表达式的字符长度。
如:
1_2表示以1开头,以2结尾的长度为3的任意字符串。
如“1a2”,“132”等。
方括号“[]”要求匹配的对象为方括号内中的字符或字符串。
如:
[a-f]或集合[abcdef])都可以表示从a到f之间的任何一个字符。
“[^]”的使用与“[]”相同,不过它要求所匹配对象为指定字符以外的任一个字符。
如“[^A]%”表示除“A”以外的字符
【例16】查询客户编号为“50001”的客户的详细情况。
SELECT*
FROMCUSTOMERS
WHERECNOLIKE'50001'
例16的查询结果
会员NULL
【例17】查询所有编号以“500”开头
的客户的姓名和编号。
SELECTCNO,CNAME
FROMCUSTOMERS
WHERECNOLIKE'500%'
50007
例17的
查询结果
【例18】查询所有姓“王”的客户且全名为两个字符的客户姓名。
SELECTCNO,CNAME
FROMCUSTOMERS
WHERECNAMELIKE'王_'
王伟
例18的查询结果
手机充值卡
【例19】查询商品名称含有“机”的商品名称。
SELECTPNO,PNAME
FROMPRODUCTS
WHEREPNAMELIKE'%机%'
例19的查询结果
【例20】查询所有姓“梁张刘”的客户的姓名和编号。
SELECTCNO,CNAME
FROMCUSTOMERS
WHERECNAMELIKE'[梁张刘]%'
张涛
【例21】查询所有不姓王的客户的姓名和编号。
SELECTCNO,CNAME
FROMCUSTOMERS
WHERECNAMELIKE'[^王]%'
该查询还可以写为:
SELECTCNO,CNAME
FROMCUSTOMERS
WHERECNAMENOTLIKE‘[王]%'
例20的查询结果
张涛
例21的
查询结果
如果用户要查询的数据项中本身就含有“%”,由于“%”是通配符,所以需要使用ECSAPE短语。
【例22】查询商品描述中有“%”及“出版社”的商品名称和商品描述。
SELECTPNAME,PDESC
FROMPRODUCTS
WHEREPDESCLIKE'%[!
%出版社]%'ESCAPE'!
'
表达式中的'!
'为换码字符,其作用是使’!
’后面的字符’%’不再具有通配符的含义,而是转义为普通字符。
100%进口原料
例22的查询结果
空值判断符用来判断表达式是否为空,在数据库中如果某一数据项下没有输入数据,那么它的值将记为空(ISNULL)。
【例23】查询所有登记了联系电话的客户的编号、姓名和电话。
SELECTCNO,CNAME,CTEL
FROMCUSTOMERS
WHERECTELISNOTNULL
8008801092
例23的查询结果
逻辑运算符用于多条件的逻辑连接。
其优先级由高到低为:
NOT,AND,OR,但用户可以使用括号来改变优先级。
25
【例24】查询年龄在30岁以下的女性客户姓名、性别和年龄。
SELECTCNAME,CSEX,CAGE
FROMCUSTOMERS
WHERECSEX='女'ANDCAGE<30
例24的查询结果
【例25】查询“普通会员”或“注册会员”的客户姓名和客户编号。
SELECTCNO,CNAME,CRANK
FROMCUSTOMERS
WHERECRANK='普通会员'ORCRANK='VIP会员'
普通会员
例25的查询结果
ORDERBY子句对查询返回的结果按某字段或多个字段排序。
参数ASC表示升序,为默认值,参数DESC为降序。
※注意:
ORDERBY不能对NTEXT,TEXT和IMAGE数据类型进行排序。
【例26】查询“非注册会员”的客户姓名和年龄,查询结果按年龄降序排列。
SELECTCNAME,CAGE,CRANK
FROMCUSTOMERS
WHERECRANK<>'注册会员'
ORDERBYCAGEDESC
如果要进行多个字段排序,那么要在字段与字段之间加上逗号。
普通会员
例26的查询结果
【例27】查询所有的客户信息,查询结果按不同的会员级别排列,同类会员按年龄降序排列。
SELECTCNAME,CAGE,CRANK
FROMCUSTOMERS
WHERECRANK<>'注册会员'
ORDERBYCRANK,CAGEDESC
普通会员
如果用户想要对一个表中的记录进行数据统计,需要用到集合函数。
例如,用户想知道有多少客户是普通会员,或者普通会员的平均年龄是多少等等。
MicrosoftSQL支持五种类型的集合函数。
用户可以通过这些复合函数统计数量(COUNT),计算平均值(AVG)、最小值(MIN)、最大值(MAX)以及求和(SUM)。
COUNT()函数用来统计一个表中有多少条记录。
(2)使用集合函数的查询
例27的查询结果
【例28】查询普通会员的总人数。
SELECTCOUNT(*)
FROMCUSTOMERS
WHERECRANK='注册会员'
查询结果是:
3.表明共有3个注册会员。
在使用COUNT()时,结果中的空值将被忽略,即空值不会被进行统计。
但如果使用COUNT(*),那么它会统计所有的记录,不管它包含空值与否。
【例29】查询所有客户一共定购了几种商品。
SELECTCOUNT(DISTINCTPNO)
FROMORDERS
查询结果是:
5.
当用户使用一个集合函数时,它只返回一个数,该数值代表这几个统计值之一。
在统计数据时可能会遇到重复的数据项,如指定DISTINCT短语,则在统计数据时会自动取消指定字段中的重复值。
如指定ALL短语,则表示不取消重复值统计。
AVG()函数用来返回一个字段中所有值的平均值。
【例30】计算所有“普通会员”的平均年龄。
SELECTAVG(CAGE)
FROMCUSTOMERS
WHERECRANK='普通会员'
查询结果是:
27,这是因为两位普通会员的年龄分别是33和21,平均年龄是27.
MIN()函数用来返回某个字段的最小值;MAX()函数则用来返回某个字段的最大值。
【例31】查询“普通会员”中年龄最小的客户的年龄。
SELECTMIN(CAGE)
FROMCUSTOMERS
WHERECRANK='普通会员'
查询结果是:
21.
如果要查询“普通会员”中年龄最大
SELECTMAX(CAGE)
FROMCUSTOMERS
WHERECRANK='普通会员'
查询结果是:
33.
SUM()函数用来统计某个字段值的和。
【例32】计算客户编号为“50001”的客户的所有定购商品的总数量。
SELECTSUM(OQUANT)
FROMORDERS
WHERECNO='50001'
查询结果是:
3,说明客户编号为“50001”的客户定购了3件商品。
如果在GROUPBY子句中指定的字段为空值,则会返回一行数据项为空的查询结果。
使用GROUPBY子句时,也可以对多个字段进行分组。
例33的查询结果
例34的查询结果
1
3
【例34】统计每个学院的男生与女生的人数。
SELECTCRANK,CSEX,COUNT(CNO)
FROMCUSTOMERS
GROUPBYCRANK,CSEX
当SQL处理该查询语句时,首先按CSEX对记录进行分组,然后在每个CSEX分组中再按会员类别进行分组,最后计算每个分组中的人数。
如果需要对分组后的结果进行筛选,则可以使用HAVING短语。
需要注意的是,HAVING短语的作用对象是分组,而不是基本表。
【例35】查询至少被两个及以上客户定购过的商品编号。
SELECTPNO
FROMORDERS
GROUPBYPNO
HAVINGCOUNT(*)>1
使用了HAVING短语后,该查询只会将满足条件的分组筛选出来。
查询结果是:
“200002”和“200005”。
如果使用参数ALL则会将所有的记录并入一个结果集合显示。
若不使用ALL,那么合并后的结果集中重复的记录只保留一项。
金牌会员
【例36】查询“VIP会员”及所有姓王的客户的信息。
SELECTCNO,CNAME,CRANK
FROMCUSTOMERS
WHERECRANK='VIP会员'
UNION
SELECTCNO,CNAME,CRANK
FROMCUSTOMERS
WHERECNAMELIKE'王%'
例36的查询结果
该查询实际就是将所有“VIP会员”的信息与所有姓王的客户的信息合并起来显示。
联合查询时,查询结果的字段名为第一个查询语句的字段名。
因此,要定义字段名必须在第一个查询语句中定义。
要对联合查询结果排序时,也必须使用第一查询语句中的字段名。
在使用UNION运算符时,应保证每个联合查询语句的选择字段表中有相同数量的表达式,并且每个查询选择表达式应具有相同的数据类型,或是可以自动将它们转换为相同的数据类型。
具体的SQL语句实现如下:
SELECTCUSTOMERS.*,ORDERS.*
FROMCUSTOMERS,ORDERS
WHERECUSTOMERS.CNO=ORDERS.CNO
【例37】查询每个客户及其定购商品的信息。
要查询每个客户及其定购商品的情况,需要涉及到两个基本表分别是:
客户情况表CUSTOMERS和客户订单表ORDERS.客户编号CNO是两个表的公共字段,所以可以用CNO将两张表连接起来。
查询结果如表所示。
例37的查询结果
2
如果将例37的查询语句改为:
SELECTCUSTOMERS.CNO,CUSTOMERS.CNAME,PNO,OADD
FROMCUSTOMERS,ORDERS
WHERECUSTOMERS.CNO=ORDERS.CNO
则其执行结果中将只含有一个CNO字段。
去掉重复字段的查询结果
北京市朝阳区八里庄北里986号
在内连接查询中,只有满足连接条件的记录才会作为结果输出。
如果该行不满足连接条件,则不会被显示到结果集合中。
如果用户希望不满足连接条件的记录也显示到结果集合中,这时应该使用外连接查询。
②外连接查询
【例38】在例37中若采用左外连接,则SQL语句为:
SELECTCUSTOMERS.CNO,CUSTOMERS.CNAME,PNO,OADD
FROMCUSTOMERSLEFTOUTERJOINORDERS
ONCUSTOMERS.CNO=ORDERS.CNO
执行结果如表所示。
多了2条记录,表明“梁亮”和“张艳”没有定购任何商品。
左外连接查询结果
北京市朝阳区八里庄北里986号
同样,在例37中也可采用右外连接,相应的SQL语句如下:
SELECTCNO,ORDERS.PNO,PRODUCTS.PNAME,OADD
FROMORDERSRIGHTOUTERJOINPRODUCTS
ONORDERS.PNO=PRODUCTS.PNO
右外连接查询结果
NULL
注意:
使用外连接时,不满足连接条件的记录将字段用空值来填充。
③自身连接查询
在连接查询中存在一种特殊的情况,即用于连接查询的两张表是同一张表,这种连接查询称为自身连接查询。
【例39】在客户情况表中,查询比其他客户大2岁的客户编号、姓名和年龄。
相应的SQL查询语句如下:
SELECTfirst.CNO,first.CNAME,first.CAGE,
second.CNO,second.CNAME,second.CAGE
FROMCUSTOMERSfirstINNERJOINCUSTOMERSsecond
ONfirst.CAGE=second.CAGE+2
查询结果如表所示。
33
自身连接查询结果
交叉连接查询结果
【例40】对CUSTOMERS和PRODUCTS表做交叉连接查询。
相应的查询SQL语句如下:
SELECTCNO,CNAME,PNO,PNAME
FROMCUSTOMERSCROSSJOINPRODUCTS
WHERECNO<50003ANDPNO<200003
计算机安全学
如果不加WHERE子句,那么查询结果会有8×8=64条记录。
加上WHERE子句后,查询结果则只有3×2=6条记录。
嵌套查询
在SQL查询中,如果在一个SELECT语句的WHERE子句中嵌入了另一个SELECT语句,那么称这种查询为嵌套查询。
WHERE子句中的SELECT语句称为子查询。
子查询也称为内部查询,而包含子查询的语句也称为外部查询。
子查询的语句通常采用以下格式:
(1)WHEREexpression[NOT]IN(subquery)
(2)WHEREexpressioncomparison_operator[ANY|ALL](subquery)
(3)WHERE[NOT]EXISTS(subquery)
【例41】结合例38的查询结果,查询没有被客户定购的商品编号、名称、类别号和价格。
相应的SQL查询语句如下:
SELECTPNO,PNAME,PCNO,PRICE
FROMPRODUCTS
WHEREPNOIN
(SELECTPRODUCTS.PNO
FROMORDERSRIGHTOUTERJOINPRODUCTS
ONORDERS.PNO=PRODUCTS.PNO
WHERECNOISNULL)
例41的查询结果
32
许多包含子查询的嵌套SQL语句都可以改为用连接查询表示。
为得到上面的结果,可以使用下面的连接查询语句:
SELECTPRODUCTS.PNO,PRODUCTS.PNAME,
PRODUCTS.PCNO,PRODUCTS.PRICE
FROMORDERSRIGHTOUTERJOINPRODUCTS
ONORDERS.PNO=PRODUCTS.PNO
WHERECNOISNULL
在SELECT子查询中,可以得到没有被定购的商品编号。
然后,通过IN子句,即可得到所需的查询结果。
【例42】查询年龄大于平均值的客户信息。
可以采用带比较运算符的SQL嵌套查询语句:
SELECT*
FROMCUSTOMERS
WHERECAGE>
(SELECTAVG(CAGE)
FROMCUSTOMERS)
查询结果如表所示。
先通过子查询获得平均年龄,然后通过嵌套查询得到大于平均年龄的客户信息。
NULL
例4