管理信息系统数据库查询.docx
《管理信息系统数据库查询.docx》由会员分享，可在线阅读，更多相关《管理信息系统数据库查询.docx（20页珍藏版）》请在冰豆网上搜索。
管理信息系统数据库查询
数据查询
在关系数据库中查询数据时，只需执行一个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
升级会员 