管理信息系统数据库查询.docx

1、管理信息系统数据库查询数据查询 在关系数据库中查询数据时，只需执行一个SQL 查询语句，通过各种查询条件，就可以得到一个从数据库中返回的查询结果，这个查询结果的内容来自该数据库中的一张或几张表。 数据查询是SQL 语言的主要功能之一。基本查询语句完整的SQL 查询语句为： SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY AORDERS | DEORDERS SELECT 语句根据WHERE 子句的查找条件表达式，通过匹配从FROM 子句指定的基本表或视图中找出满足条件的记录，再按照选择字段表，返回相应的字段值形成结果集。结

2、果集中的一条条记录有时也被形象地称为“行”。 GROUP BY 子句将结果按BY 后的进行分组。 如果HAVING 子句中包含聚合函数，则计算每组的汇总值。 如果GROUP 子句带HAVING 短语，则只有满足指定条件的组才会输出。 ORDER BY 子句将结果集中的数据按升序或降序排列。 该查询为无条件查询，查询语句中只使用SELECT、FROM子句，查询结果是CUSTOMERS表中指定字段表的所有值。NULL例1的查询结果如果写为*，则会返回表中所有的字段。【例2】查询全体客户的详细订单表。 SELECT * FROM ORDERS2例2的查询结果【例3】更改商品表PRODUCTS商品编号

3、PDESC为“描述”。 SELECT PNO, PNAME, PCNO, PRICE, 描述 =PDESC FROM PRODUCTS如果指定的字段名不是标准的标识符格式时，应使用引号定界符。另外，选择字段表的表达式还可以是一个计算表达式，通过计算得到用户所需要的数据。100%进口原料例3的查询结果【例4】查询客户的出生年份。 SELECT CNAME, CSEX, 出生年份= 2006-CAGE FROM CUSTOMERS WHERE CSEX=女其中：出生年份不是原表的字段名，需加上引号，用来指明其字段名称。而2006-CAGE则为计算表达式，用来计算客户的出生年份。1981例4的查询结

4、果SELECT 子句中有两个可选参数ALL 和DISTINCT。DISTINCT 返回的查询结果会删除其中的重复项，而使用 ALL 不会删除重复项，仅仅将重复项分开显示。ALL 是默认的参数选项。1003带DISTINCT 属性的查询结果1001带ALL 属性的查询结果【例5】执行语句 SELECT PCNO FROM PRODUCTS WHERE PNO200005默认为 ALL，查询结果见表。如果想删除返回表中的重复项，则需将执行语句改为 SELECT DISTINCT PCNO FROM PRODUCTS WHERE PNO200005加了“DISTINCT”关键字的查询结果见表。【例6

5、】在客户情况表CUSTOMERS 和订单表ORDERS 中都存在客户编号CNO 字段，在查询两个表中的 CNO 时应使用下面语句格式加以限定：SELECT CNAME, CUSTOMERS.CNO, PNOFROM CUSTOMERS, ORDERSWHERE CUSTOMERS.CNO=ORDERS.CNO用.这种格式可以区分不同表中的同名字段。CUSTOMERS.CNO 表示CUSTOMERS 表中的CNO 字段，ORDERS.CNO 则表示ORDERS 表中的CNO 字段，通过这种办法就可以将两个属于不同表的相同字段分开。FROM 子句还可以为基本表或视图指定别名。用法为： FROM a

6、s，【例7】将例6中的语句用表的别名格式表示。 SELECT CNAME, First.CNO FROM CUSTOMERS as First, ORDERS as Second WHERE First.CNO=Second.CNO20000250007例6的查询结果例7的查询结果WHERE子句可包括各种条件运算符。NOT、AND、ORWHERE 子句中的条件运算符【例8】查询所有28 岁的客户姓名。 SELECT CNAME FROM CUSTOMERS WHERE CAGE=28查询结果仅包含一条记录：“吴丹”。注意：WHERE 子句的查询条件里的不等于符号为，而不是!=。【例9】查询所有

7、不是“VIP 会员”的客户信息。 SELECT * FROM CUSTOMERS WHERE CRANKVIP 会员NULL例9的查询结果【例10】查询所有价格低于30 元的商品的编号、名称和价格。 SELECT PNO, PNAME, PRICE FROM PRODUCTS WHERE PRICE30【例11】查询所有年龄小于27 岁的客户编号、姓名和年龄。 SELECT CNO, CNAME, CAGE FROM CUSTOMERS WHERE CAGE272520.50例11的查询结果例10的查询结果【例12】查询所有年龄在26 岁与31 岁(包括26 岁和31 岁)之间的客户的姓名、年

8、龄、性别SELECT CNAME, CAGE, CSEXFROM CUSTOMERSWHERE CAGE BETWEEN 26 AND 31VIP会员女例13的查询结果例12的查询结果【例13】查询“VIP 会员”、“普通会员”的客户编号、姓名和会员类别。 SELECT CNO, CNAME, CRANK FROM CUSTOMERS WHERE CRANK IN (VIP 会员，普通会员) 【例14】查询所有商品分类编号为“1001”、“1002”的商品的编号、名称和分类号。SELECT PNO, PNAME, PCNOFROM PRODUCTSWHERE PCNO IN(1001, 100

9、2)NOT IN 的功能与IN 相反，用于用户查找不属于指定字段表的内容。IN 后面跟的是一个字段表，用户只需定义字段表的内容就可以得到自己所需的查找内容。例15的查询结果例14的查询结果 模式运算符判断字段值是否与指定的字符串格式相匹配，可用于CHAR、VARCHAR、TEXT、NTEXT、DATETIME 和 SMALLDATETIME 等类型查询。金牌会员1002【例15】查询所有非“VIP 会员”、“普通会员”的客户编号、姓名和会员类别。SELECT CNO, CNAME, CRANKFROM CUSTOMERSWHERE CRANK NOT IN (VIP 会员, 普通会员) 模式运

10、算符判断字段值是否与指定的字符串格式相匹配，可用于CHAR、VARCHAR、TEXT、NTEXT、DATETIME 和 SMALLDATETIME 等类型查询。正则表达式： 用于匹配的匹配串可以是一个含有合法符号的字符串如：“abcd”，也可以是含有通配符的字符串。百分号“%”可匹配任意类型和长度的字符，其长度也可以为0。如：c%e 表示以c 开头，以e 结尾的任意长度的字符串，“come”，“cope”，“combe”等都满足该匹配。 下划线“_”匹配单个任意字符，常用来限制表达式的字符长度。如：1_2 表示以1 开头，以2 结尾的长度为3 的任意字符串。如“1a2”，“132”等。 方括号

11、“”要求匹配的对象为方括号内中的字符或字符串。如：a-f或集合abcdef)都可以表示从a 到f 之间的任何一个字符。 “”的使用与“”相同，不过它要求所匹配对象为指定字符以外的任一个字符。如“A%”表示除“A”以外的字符【例16】查询客户编号为“50001”的客户的详细情况。 SELECT * FROM CUSTOMERS WHERE CNO LIKE 50001例16的查询结果会员NULL【例17】查询所有编号以“500”开头 的客户的姓名和编号。 SELECT CNO, CNAME FROM CUSTOMERS WHERE CNO LIKE 500%50007例17的查询结果【例18】查

12、询所有姓“王”的客户且全名为两个字符的客户姓名。SELECT CNO, CNAMEFROM CUSTOMERSWHERE CNAME LIKE 王_王伟例18的查询结果手机充值卡【例19】查询商品名称含有“机”的商品名称。 SELECT PNO, PNAME FROM PRODUCTS WHERE PNAME LIKE %机%例19的查询结果【例20】查询所有姓“梁张刘”的客户的姓名和编号。 SELECT CNO, CNAME FROM CUSTOMERS WHERE CNAME LIKE 梁张刘%张涛【例21】查询所有不姓王的客户的姓名和编号。 SELECT CNO, CNAME FROM

13、CUSTOMERS WHERE CNAME LIKE 王%该查询还可以写为： SELECT CNO, CNAME FROM CUSTOMERS WHERE CNAME NOT LIKE 王%例20的查询结果张涛例21的查询结果如果用户要查询的数据项中本身就含有“%”，由于“%”是通配符，所以需要使用ECSAPE短语。【例22】查询商品描述中有“%”及“出版社”的商品名称和商品描述。 SELECT PNAME，PDESC FROM PRODUCTS WHERE PDESC LIKE %!%出版社% ESCAPE !表达式中的!为换码字符，其作用是使!后面的字符%不再具有通配符的含义，而是转义为普

14、通字符。100%进口原料例22的查询结果空值判断符用来判断表达式是否为空，在数据库中如果某一数据项下没有输入数据，那么它的值将记为空(IS NULL)。【例23】查询所有登记了联系电话的客户的编号、姓名和电话。 SELECT CNO, CNAME, CTEL FROM CUSTOMERS WHERE CTEL IS NOT NULL8008801092例23的查询结果逻辑运算符用于多条件的逻辑连接。其优先级由高到低为：NOT，AND，OR，但用户可以使用括号来改变优先级。25【例24】查询年龄在30 岁以下的女性客户姓名、性别和年龄。 SELECT CNAME, CSEX, CAGE FROM

15、 CUSTOMERS WHERE CSEX=女 AND CAGE30例24的查询结果【例25】查询“普通会员”或“注册会员”的客户姓名和客户编号。 SELECT CNO, CNAME, CRANK FROM CUSTOMERS WHERE CRANK=普通会员 OR CRANK =VIP 会员普通会员例25的查询结果ORDER BY 子句对查询返回的结果按某字段或多个字段排序。参数ASC 表示升序，为默认值，参数DESC 为降序。 注意：ORDER BY 不能对NTEXT，TEXT 和IMAGE 数据类型进行排序。【例26】查询“非注册会员”的客户姓名和年龄，查询结果按年龄降序排列。 SELE

16、CT CNAME, CAGE, CRANK FROM CUSTOMERS WHERE CRANK 注册会员 ORDER BY CAGE DESC如果要进行多个字段排序，那么要在字段与字段之间加上逗号。普通会员例26的查询结果【例27】查询所有的客户信息，查询结果按不同的会员级别排列，同类会员按年龄降序排列。 SELECT CNAME, CAGE, CRANK FROM CUSTOMERS WHERE CRANK 注册会员 ORDER BY CRANK, CAGE DESC普通会员 如果用户想要对一个表中的记录进行数据统计，需要用到集合函数。例如，用户想知道有多少客户是普通会员，或者普通会员的平

17、均年龄是多少等等。Microsoft SQL 支持五种类型的集合函数。用户可以通过这些复合函数统计数量(COUNT)，计算平均值(AVG)、最小值(MIN)、最大值(MAX)以及求和(SUM)。COUNT()函数用来统计一个表中有多少条记录。(2) 使用集合函数的查询例27的查询结果【例28】查询普通会员的总人数。 SELECT COUNT(*) FROM CUSTOMERS WHERE CRANK=注册会员查询结果是：3. 表明共有3 个注册会员。在使用COUNT()时，结果中的空值将被忽略，即空值不会被进行统计。但如果使用COUNT(*) ，那么它会统计所有的记录，不管它包含空值与否。【例

18、29】查询所有客户一共定购了几种商品。 SELECT COUNT(DISTINCT PNO) FROM ORDERS 查询结果是：5. 当用户使用一个集合函数时，它只返回一个数，该数值代表这几个统计值之一。在统计数据时可能会遇到重复的数据项，如指定DISTINCT 短语，则在统计数据时会自动取消指定字段中的重复值。如指定ALL 短语，则表示不取消重复值统计。 AVG()函数用来返回一个字段中所有值的平均值。【例30】计算所有“普通会员”的平均年龄。 SELECT AVG(CAGE) FROM CUSTOMERS WHERE CRANK=普通会员 查询结果是：27，这是因为两位普通会员的年龄分别

19、是33 和21，平均年龄是27. MIN() 函数用来返回某个字段的最小值；MAX()函数则用来返回某个字段的最大值。 【例31】查询“普通会员”中年龄最小的客户的年龄。 SELECT MIN(CAGE) FROM CUSTOMERS WHERE CRANK=普通会员 查询结果是：21.如果要查询“普通会员”中年龄最大 SELECT MAX(CAGE) FROM CUSTOMERS WHERE CRANK=普通会员 查询结果是：33.SUM()函数用来统计某个字段值的和。【例32】计算客户编号为“50001”的客户的所有定购商品的总数量。 SELECT SUM(OQUANT) FROM ORD

20、ERS WHERE CNO=50001查询结果是：3，说明客户编号为“50001”的客户定购了3 件商品。如果在GROUP BY 子句中指定的字段为空值，则会返回一行数据项为空的查询结果。使用 GROUP BY 子句时，也可以对多个字段进行分组。例33的查询结果例34的查询结果13【例34】统计每个学院的男生与女生的人数。 SELECT CRANK，CSEX，COUNT(CNO) FROM CUSTOMERS GROUP BY CRANK，CSEX 当SQL 处理该查询语句时，首先按CSEX 对记录进行分组，然后在每个CSEX 分组中再按会员类别进行分组，最后计算每个分组中的人数。 如果需要对

21、分组后的结果进行筛选，则可以使用HAVING 短语。需要注意的是，HAVING短语的作用对象是分组，而不是基本表。【例35】查询至少被两个及以上客户定购过的商品编号。 SELECT PNO FROM ORDERS GROUP BY PNO HAVING COUNT(*)1使用了 HAVING 短语后，该查询只会将满足条件的分组筛选出来。查询结果是：“200002”和“200005”。如果使用参数ALL 则会将所有的记录并入一个结果集合显示。若不使用 ALL ，那么合并后的结果集中重复的记录只保留一项。金牌会员【例36】查询“VIP 会员”及所有姓王的客户的信息。 SELECT CNO, CNA

22、ME, CRANK FROM CUSTOMERS WHERE CRANK=VIP 会员 UNION SELECT CNO, CNAME, CRANK FROM CUSTOMERS WHERE CNAME LIKE 王%例36的查询结果 该查询实际就是将所有“VIP 会员”的信息与所有姓王的客户的信息合并起来显示。 联合查 询时，查询结果的字段名为第一个查询语句的字段名。因此，要定义字段名必须在第一个查询语句中定义。要对联合查询结果排序时，也必须使用第一查询语句中的字段名。 在使用 UNION 运算符时，应保证每个联合查询语句的选择字段表中有相同数量的表达式，并且每个查询选择表达式应具有相同的数

23、据类型，或是可以自动将它们转换为相同的数据类型。具体的SQL 语句实现如下： SELECT CUSTOMERS.*，ORDERS.* FROM CUSTOMERS，ORDERS WHERE CUSTOMERS.CNO=ORDERS.CNO【例37】查询每个客户及其定购商品的信息。要查询每个客户及其定购商品的情况，需要涉及到两个基本表分别是：客户情况表CUSTOMERS 和客户订单表ORDERS. 客户编号 CNO 是两个表的公共字段，所以可以用CNO 将两张表连接起来。查询结果如表所示。例37的查询结果2如果将例37的查询语句改为： SELECT CUSTOMERS.CNO, CUSTOMER

24、S.CNAME, PNO, OADD FROM CUSTOMERS, ORDERS WHERE CUSTOMERS.CNO=ORDERS.CNO则其执行结果中将只含有一个CNO 字段。去掉重复字段的查询结果北京市朝阳区八里庄北里986号 在内连接查询中，只有满足连接条件的记录才会作为结果输出。如果该行不满足连接条件，则不会被显示到结果集合中。如果用户希望不满足连接条件的记录也显示到结果集合中，这时应该使用外连接查询。 外连接查询【例38】 在例37中若采用左外连接，则SQL语句为： SELECT CUSTOMERS.CNO, CUSTOMERS.CNAME, PNO, OADD FROM CU

25、STOMERS LEFT OUTER JOIN ORDERS ON CUSTOMERS.CNO = ORDERS.CNO执行结果如表所示。多了2 条记录，表明“梁亮”和“张艳”没有定购任何商品。左外连接查询结果北京市朝阳区八里庄北里986号同样，在例37中也可采用右外连接，相应的SQL语句如下： SELECT CNO, ORDERS.PNO, PRODUCTS.PNAME, OADD FROM ORDERS RIGHT OUTER JOIN PRODUCTS ON ORDERS.PNO = PRODUCTS.PNO右外连接查询结果NULL注意：使用外连接时，不满足连接条件的记录将字段用空值来填

26、充。 自身连接查询在连接查询中存在一种特殊的情况，即用于连接查询的两张表是同一张表，这种连接查询称为自身连接查询。【例39】在客户情况表中，查询比其他客户大2 岁的客户编号、姓名和年龄。相应的SQL 查询语句如下： SELECT first.CNO, first.CNAME, first.CAGE, second.CNO, second.CNAME, second.CAGE FROM CUSTOMERS first INNER JOIN CUSTOMERS second ON first.CAGE = second.CAGE+2查询结果如表所示。33自身连接查询结果交叉连接查询结果【例40】对CUSTOMERS 和PRODUCTS 表做交叉连接查询。相应的查询SQL 语句如下： SELECT CNO,CNAME,PNO,PNAME FROM CUSTOMERS CROSS JOIN PRODUCTS WHERE CNO50003 AND PNO (SELECT AVG(CAGE) FROM CUSTOMERS)查询结果如表所示。先通过子查询获得平均年龄，然后通过嵌套查询得到大于平均年龄的客户信息。NULL例4