项目四-公司管理数据库系统的数据查询.ppt课件.ppt

1、项目四 公司管理数据库系统的数据查询 终极目标：能根据需要灵活、快速地查询公司管理系统数据库的数据 一、教学目标： 1、会在一个数据表中实现数据的简单查询 2、会同时在多个数据表中实现数据的复合查询 3、会实现分组查询 4、会对查询结果集排序 5、会利用库函数进行数据统计二、工作任务 根据需要灵活快速地查询公司管理数据库系统的数据2模块1 公司管理数据库系统的简单查询一、教学目标： 1、 准确理解TRANSACT-SQL查询语句的基本结构 2、 能根据需要灵活使用查询语句的各子句二、工作任务 公司对所有招收的雇员的材料都存储在公司管理数据库系统COMPANYINFO的employee表中，该表

2、中包含了雇员的姓名、性别、出生年月、雇佣日期、特长和薪水等信息。现编写查询语句，按要求得到相应的结果。3SELECT语句 使用数据库和数据表的主要目的是存储数据，以便在需要时进行检索、统计或组织输出，通过T-SQL的查询可以从表或视图中迅速、方便地检索数据。在众多的T-SQL语句中，SELECT语句是使用频率最高的一个。 查询的最基本方式是使用SELECT语句，按照用户给定的条件从SQL SERVER数据库中取出数据，并将数据通过一个或多个结果集返回给用户。 4一、SELECT格式 从数据库中检索行，并允许从一个或多个表中选择一个或多个行或列。SELECT ALL| DISTINCT , IN

3、TO FROM , WHERE GROUP BY HAVING ORDER BY ASC | DESC 5vSelect子句：指定由查询返回的列 。vInto子句：将检索结果存储到新表或视图中。vFrom子句：用于指定引用的列所在的表和视图 。vWhere子句：指定用于限制返回的行的搜索条件。vGroup by子句：指定用来放置输出行的组，并且如果 SELECT 子句 中包含聚合函数，则计算每组的汇总值。 vhaving子句：指定组或聚合的搜索条件。 HAVING 通常与 GROUP BY 子句一起使用。如果不使用 GROUP BY 子句，HAVING 的行为与 WHERE 子句一样。vOrd

4、er by子句：指定结果集的排序。 1、子句的功能62、SELECT语句的执行方式1）通过查询设计器辅助生成和执行select语句2）查询分析器执行select语句7二、SELECT 子句功能功能: : 指定由查询返回的列。指定由查询返回的列。格式：格式： SELECT ALL|DISTINCTTOP N PERCENT SELECT ALL|DISTINCTTOP N PERCENT 列名列名1,1,列名列名2,2,列名列名NN FROM FROM 表名或视图名表名或视图名 参数的含义：参数的含义：vALLALL：指定在结果集中可以显示重复行。：指定在结果集中可以显示重复行。ALL ALL

5、是默认设置。是默认设置。vDISTINCTDISTINCT：指定在结果集中只能显示唯一行。即表示输出：指定在结果集中只能显示唯一行。即表示输出无重复的所有记录。无重复的所有记录。vTOP NTOP N PERCENTPERCENT：指定只从查询结果集中输出前：指定只从查询结果集中输出前 N N 行。行。如果还指定了如果还指定了 PERCENTPERCENT，则只从结果集中输出前百分之，则只从结果集中输出前百分之 N N 行。行。81、查询所有的列 SELECT 子句中，在选择列表处使用通配符“*”,表示选择指定的表或视图中所有的列。服务器会按用户创建表格时声明列的顺序来显示所有的列.语法 ：

6、SELECT * FROM 表名 9【例【例1 1】从公司管理数据库】从公司管理数据库COMPANYINFOCOMPANYINFO中的客户表中的客户表(customer)(customer)中检索所有的客户的公司名称、联系中检索所有的客户的公司名称、联系人姓名、联系方式、地址和邮编。人姓名、联系方式、地址和邮编。USE COMPANYINFOUSE COMPANYINFOGOGOSELECT SELECT * *FROM customerFROM customerGO GO 102、查询特定的列 【例【例2 2】 从公司管理数据库从公司管理数据库(COMPANYINFO)(COMPANYINF

7、O)中的中的客户表客户表(customer)(customer)中检索所有的客户的公司名中检索所有的客户的公司名称、联系人姓名、地址。称、联系人姓名、地址。USE COMPANYINFOUSE COMPANYINFOGOGOSELECT SELECT 公司名称，联系人姓名，地址公司名称，联系人姓名，地址FROM customerFROM customerGO GO 11【例3】 从公司管理数据库(COMPANYINFO)中的雇员表（employee）中检索所有的雇员的姓名和特长。USE COMPANYINFOGOSELECT 姓名，特长FROM employeeGO 123 3、指定特定列的列

8、名、指定特定列的列名（1）采用符合ANSI规则的标准方法，在列表达式后面给出列名。【例4】查询每个人的薪水降低30%信息。 USE COMPANYINFO GO SELECT 姓名, 薪水 原薪水 , 薪水-薪水*0.3 现薪水 FROM employee GO13（2）用“=”来连接列表达式【例5】查询每个人的薪水降低30%信息。USE COMPANYINFOGOSELECT 姓名, 原薪水=薪水, 现薪水=薪水-薪水*0.3FROM employeeGO14（3 3）用）用ASAS关键字来连接列表达式关键字来连接列表达式和指定的列名和指定的列名【例【例6 6】查询每个人的薪水降低】查询每个

9、人的薪水降低30%30%信息。信息。USE COMPANYINFOUSE COMPANYINFOGOGOSELECT SELECT 姓名姓名, , 薪水薪水 as as 原薪水原薪水 , , 薪水薪水- -薪水薪水* *0.3 as 0.3 as 现薪水现薪水 FROM employeeFROM employeeGOGO154使用TOP关键字 SQL Server 2000提供了TOP关键字，让用户指定返回前面一定数量的数据。 语法格式： SELECT TOP n | TOP n PERCENT 列名1,列名2,列名n FROM 表名其中:vTOP n:表示返回最前面的n行，n表示返回的行数。

10、vTOP n PERCENT:表示返回的前面的n%行。16【例7】从northwind数据库的customers表中返回前面10行数据。use northwindgoselect top 10 *from customersgo17【例8】从northwind数据库中的customers表中返回前10%的数据。use northwindgoselect top 10 percent *from customersgo18三、 WHERE 字句 使用WHERE子句的目的是为了从表格的数据集中过滤出符合条件的行。 语法格式： SELECT 列名1 ,列名2, 列名n FROM 表名 WHERE 条

11、件19常用查询条件常用查询条件 查询条件运算符号比较=、=、!=、!、!=3000=3000GOGO232使用逻辑表达式 在T-SQL里的逻辑表达式共有3个。分别是：1) NOT：非，对表达式的否定。2) AND：与，连接多个条件，所有的条件都成立时为真。3) OR：或，连接多个条件，只要有一个条件成立就为真。 24【例11】查询所有在美国加利福尼亚州的出版社。use pubsgo select pub_id as 出版社代号, pub_name as 出版社名称,city as 城市, state as 州, country as 国家from publisherswhere country

12、=usa and state =cago25查询结果如下：出版社代号 出版社名称 城市 州 国家 - - - - - 1389 Algodata Infosystems Berkeley CA USA （所影响的行数为 1 行）263使用BETWEEN关键字 使用BETWEEN关键字可以更方便地限制查询数据的范围。语法格式为： 表达式 NOT BETWEEN 表达式1 AND 表达式227【例12】查询COMPANYINFO数据库的雇员表（employee）中，薪水在3000元至4000元的雇员的姓名和薪水。SELECT 姓名，薪水FROM employee WHERE 薪水 between

13、3000 and 400028【例13】查询库存量大于200和库存量小于100的产品名、库存量和单价。SELECT 产品名 ，库存量 ，单价 FROM productWHERE 库存量 not between 200 and 10029【例 14】 查询价格在15和20美元之间的书的书号、种类和价格。use pubsgo select title_id as 书号,type as 种类 ,price as 原价from titleswhere price between $15 and $20go3031【例15】查询书价大于20和书价小于15的书的代号、种类和价格。use pubsgo se

14、lect title_id as 书号,type as 种类, price as 原价 from titleswhere price $20go32查询结果如下：书号种类原价- - - BU1111business11.9500BU2075business2.9900MC3021mod_cook2.9900PC1035popular_comp22.9500（所影响的行数为 11 行） 334使用IN关键字 同BETWEEN关键字一样，IN的引入也是为了更方便地限制检索数据的范围，灵活使用IN关键字，可以用简洁的语句实现结构复杂的查询。 语法格式为： 表达式 NOT IN (表达式1 , 表达式

15、2 ,表达式n)34【例16】查询所有居住在KS、CA、MI或IN州的作家。use pubsgo select au_id,au_lname,au_fnamefrom authorswhere state IN (CA,KS,MI,IN)go 如果不使用IN关键字，这些语句可以使用下面的语句代替：use pubsgo select au_id,au_lname,au_fnamefrom authorswhere state=CA or state=KS or state=MI or state=INgo35【例17】查询所有不在上述4个州居住的作家。use pubsgo select au_i

16、d,au_lname,au_fnamefrom authorswhere state not in (CA, KS, MI, IN) go 与下面的语句等价：use pubsgo select au_id,au_lname,au_fnamefrom authorswhere stateCA and stateKS and stateMIand stateINgo 365、通配符的使用 注意：所有通配符都只有在LIKE子句中才有意义，否则通配符会被当作普通字符处理。符号含义%（百分号）表示从0N个任意字符_（下划线）表示单个的任意字符 （封闭方括号） 表示方括号里列出的任意一个字符任意一个没有在

17、方括号里列出的字符37【例18】列出所有姓“章”的雇员的信息。 查询语句如下： SELECT *FROM employeeWHERE 姓名 LIKE 章%38【例19】查询雇员表（employee）中所有姓名中含有“利”字的雇员。SELECT 雇员ID，姓名FROM employeeWHERE 姓名 like %利%GO39【例20】查询雇员表（employee）中所有雇员ID满足前2个字符为“01”，第4个字符为“-”的雇员的姓名和出生年月。SELECT 雇员ID，姓名，出生年月FROM employeeWHERE 雇员id like 01_-%GO40【例21】查询所有au_id满足前2个

18、字符为“72”，第4个字符为“-”的作家的姓名和电话号码。use pubsgo select au_lname, au_fname, phone, au_id from authorswhere au_id like 72_-%go41查询结果如下：au_lnameau_fnamephone au_id- - - - DeFranceMichel 219 547-9982722-51-5454StringerDirk 415 843-2991724-08-9931MacFeather Stearns 415 354-7128724-80-9391 （所影响的行数为 3 行）42【例22】查询所

19、有au_id的第一个字符为5-9、第二个字符为1-4的作家的姓名和电话号码。use pubs go select au_lname,au_fname, phone, au_idfrom authorswhere au_id like 5-91-4%go43五、 from 子句 在每一条要从表或视图中检索数据的 SELCET 语句中，都需要使用 FROM 子句。用 FROM 子句可以： 列出选择列表和 WHERE 子句中所引用的列所在的表和视图。可用 AS 子句为表和视图的名称指定别名。44FROM 子句可以指定：一个或多个表或视图。 例： SELECT * FROM Shippers 两个表或

20、视图之间的联接： SELECT Cst.CustomerID, Cst.CompanyName, Cst.ContactName, Ord.ShippedDate, Ord.Freight FROM Northwind.dbo.Orders AS Ord JOIN Northwind.dbo.Customers AS Cst ON (Cst.CustomerID = Ord.CustomerID) 45六、 ORDER BY子句 SELECT语句获得的数据一般是没有排序的。为了方便阅读和使用，最好对查询的结果进行一次排序。SQL语言中，用于排序的是ORDER BY子句。 语法格式为： ORDE

21、R BY 表达式1 ASC | DESC ,表达式2 ASC | DESC,n 46【例24】在公司管理数据库（COMPANYINFO）中的订单表(p_order)中，查询产品ID，数量和订货日期，并按的订货日期降序给产品排序。USE COMPANYINFOGO SELECT 产品ID，数量，订货日期FROM p_orderORDER BY 订货日期 descGO47【例25】查询pubs数据库中的表stores中商店的名字，并按商店名的降序给商店排序。use pubsgo select stor_namefrom storesorder by stor_name descgo48【例26】查

22、询titles中各类书的销售利润和书号，并按照各种书的销售利润降序排列。use pubsgo select title_id,profit=price*ytd_salesfrom titlesorder by profit descgo49查询结果如下：title_idprofit - - PC1035201501.0000TC4203180397.2000PC888881900.0000BU103281859.0500BU783281859.0500PS333381399.2800（所影响的行数为 18 行） 50【例27】查询titles中各类书的书号、价格、年销售量和销售利润，并用年销售

23、量和书价进行升序排列。use pubsgo select title_id,price,ytd_sales,profit=price*ytd_sales from titlesorder by ytd_sales,price go5152七 GROUP BY子句 在大多数情况下，使用统计函数返回的是所有行数据的统计结果。如果需要按某一列数据的值进行分类，在分类的基础上再进行查询，就要使用GROUP BY子句了。 53常用统计函数 为了有效的决心数据集分类汇总、求平均等统计，SQL Server2000提供了一系列统计函数。54【例 28】计算订单表(p_order)中所有产品的已订购总额：US

24、E COMPANYINFOGOSELECT sum(数量) FROM p_order55【例 29】 对订单表(p_order)中按产品ID ，查询每件产品的订购总和USE COMPAMYINFOSELECT 产品ID , sum(数量) as 总数量FROM p_order GROUP BY 产品ID 561简单分组【例 30】订单表(p_order) 按产品ID的种类分类，求出各种类型产品的价格总和、平均价格以及各类产品的数量。USE COMPAMYINFO GO SELECT 产品ID, sum(单价) 价格总和, avg(单价) 平均单价, count(*)FROM p_orderGR

25、OUP BY 产品ID GO572、使用 GROUP BY 分组多行 GROUP BY 子句用来为结果集中的每一行产生聚合值。如果聚合函数没有使用 GROUP BY 子句，则只为 SELECT 语句报告一个聚合值。 当 SELECT 语句中包含 GROUP BY 关键字时，对可以在选择列表中指定的项有一些限制。在该选择列表中所允许的项目是：q 分组列。q 为分组列中的每个值只返回一个值的表达式，例如将列名作为其中一个参数的聚合函数。这些函数称为矢量聚合。58八、使用HAVING筛选结果 指定组或聚合的搜索条件。HAVING 通常与 GROUP BY 子句一起使用。如果不使用 GROUP BY

26、子句，HAVING 的行为与 WHERE 子句一样。 语法： HAVING 参数 ： 指定组或聚合应满足的搜索条件。当 HAVING 与 GROUP BY ALL 一起使用时，HAVING 子句替代 ALL。 在 HAVING 子句中不能使用 text、image 和 ntext 数据类型。59【例31】查询所有价格超过10元的产品的种类和平均价格。 USE COMPANYINFO GO SELECT 类别ID , avg(单价) 平均价格 FROM Product WHERE 单价10 GROUP By 类别ID GO60【例32】在所有价格超过10美元的书中，查询所有平均价格超过18美元的

27、书的种类和平均价格。use pubsgo select type , avg(price) avg_pricefrom titleswhere price10group by typehaving avg(price)$18go61查询的结果是：Type avg_price- - mod_cook 19.9900popular_comp 21.4750 （所影响的行数为 2 行） WHERE子句在求平均值之前从表中选择所需要的行，HAVING子句在进行统计计算后产生的结果中选择所需要的行。 62九、 COMPUTE子句 COMPUTE 子句可以用于SELECT 语句既查看明细行，又查看汇总行。

28、可以计算分组的汇总值，也可以计算整个结果集的汇总值。 语法格式： COMPUTE 聚合函数名 (列名) , N BY expression , N 63【例 33】语句使用简单 COMPUTE 子句生成 titles 表中 price 及 advance 的求和总计：USE pubsGo SELECT type, price, advance FROM titles ORDER BY type COMPUTE SUM(price), SUM(advance) by type64 【例 34】查找雇员表（empoyee）中各雇员的姓名、出生年月和特长，并产生一个雇员总人数行。USE COMPAN

29、YINFOGOSELECT 姓名，出生年月，特长FROM employeeOrder by 特长COMPUTE count(雇员) by 特长65比较比较 COMPUTE 和和 GROUP BYqGROUP BY 生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。选择列表只能包含分组依据列和聚合函数。qCOMPUTE 生成多个结果集。一类结果集包含每个组的明细行，其中包含选择列表中的表达式。另一类结果集包含组的子聚合，或 SELECT 语句的总聚合。选择列表可包含除分组依据列或聚合函数之外的其它表达式。聚合函数在 COMPUTE 子句中指定，而不是在选择列表中。 6

30、6模块2 公司管理数据库系统的多表查询一、教学目标： 终极目标：使用联接查询和嵌套查询的方法实现公司管理数据库系统的多表查询67二、工作任务 公司对于产品订单有关的数据保存在订单表P-ORDER和，此表包含了客户订购产品有关的订单ID、产品ID、数量、雇员ID、客户ID和订货日期。在产品表PRODUCT中包含了产品ID、产品名、类别ID、单价、库存量。在顾客表CUSTOMER中包含了客户ID、公司名称、联系人姓名、联系方式和地址和邮编。现编写查询语句，按下列要求查询得到相应的结果： 1、查询订货公司的名称，联系人姓名、订购产品名称和数量 2、查询订购“苹果汁”的顾客的公司名称和联系方法。 3、

31、查询客户“通恒机械”下的订单订购的产品名和数量。 4、查询产品“苹果汁”所属的类别和说明。 68一、联接（ join） 通过联接，可以根据各个表之间的逻辑关系从两个或多个表中检索数据。联接表示应如何使用一个表中的数据来选择另一个表中的行。 联接条件通过以下方法定义两个表在查询中的关联方式： v 指定每个表中要用于联接的列。典型的联接条件在一个表中指定外键，在另一个表中指定与其关联的键。v指定比较各列的值时要使用的逻辑运算符（=、 等）。69联接类型 当联接表时，创建的联接类型影响出现在结果集内的行。 联接类型： 内联接 外联接 左向外联接 右向外联接 完整外部联接 交叉联接 70联接语法格式：

32、FROM JOIN ON 参数： := INNER | LEFT | RIGHT | FULL OUTER 711、内连接 内连接也叫自然连接，它是组合两个表的常用方法。自然连接将两个表中的列进行比较，返回每对匹配的行。废弃两个表中不匹配的行。如果未指定联接类型，则这是默认设置。语法一： SELECT 列 FROM 表1 inner JION 表2 ON 表1.列=表2.列语法二： SELECT 列 FROM 表1,表2 WHERE 表1.列=表2.列72【例34】从titles和titleauthor表中查询书的书号、书名、作者号和价格。usepubsgo select titles.tit

33、le_id as 书号, title as 书名,au_id as 作者号, price as 价格from titles inner join titleauthoron titles.title_id=titleauthor.title_id7374【例35】从titles、authors和titleauthor表中查询书的书号、书名、作者号和作者名。usepubsgo select titles.title_id, title, authors.au_id, au_lnamefrom titles join titleauthoron titles.title_id=titleautho

34、r.title_idjoin authorson authors.au_id=titleauthor.au_id7576任务任务1 1：查询已订购了产品的公司的公司名称，联系：查询已订购了产品的公司的公司名称，联系人姓名和所订产品的产品名和数量。人姓名和所订产品的产品名和数量。 SELECT cu.公司名称,联系人姓名,pr.产品名, pd.数量FROM customer as cuINNER JOIN p_order as pd ON cu.客户ID=pd.客户IDINNER JOIN product as pron pr.产品ID=Pd.产品ID77任务3：查询客户名为通恒机械的公司所订购

35、产品的产品ID和数量。SELECT 公司名称,产品ID,数量FROM customer as A INNER JOIN p_order as BON A.客户ID=B.客户IDWHERE A.公司名称=通恒机械78【例36】从学生信息表、教学成绩表和课程信息表中查询学生的学号、姓名、课程名称和分数use 教学成绩管理数据库goselect cj.学号,姓名,kc.名称,分数from 教学成绩表 as cj inner join 学生信息表 as xs on cj.学号 = xs.学号 inner join 课程信息表 as kc on cj.课程编号 = kc.编号79【例 37】查询2003

36、01班均分高于80分的学生的班级、学号、姓名、均分，结果按均分降序排列.use 教学成绩管理数据库select 班级编号, a.学号, b.姓名, avg(分数) 均分from 教学成绩表 as a join 学生信息表 as b on a.学号 = b.学号 join 课程信息表 as c on a.课程编号 = c.编号group by 班级编号, a.学号, b.姓名having avg(分数)80 and 班级编号=200301 order by avg(分数) desc802外连接（Outer join） 在自然连接中，只有在两个表中匹配的行才能在结果集中出现。而在外连接中可以只限制

37、一个表，而对另外一个表不加限制（即所有的行都出现在结果集中）。 外连接分为左外连接、右外连接和全外连接。左外连接是对连接条件中左边的表不加限制；右外连接是对右边的表不加限制；全外连接对两个表都不加限制，所有两个表中的行都会包括在结果集中。81左外连接的语法为： SELECT 列 FROM 表1 LEFT OUTERJOIN 表2 ON 表1.列1=表2.列2右外连接的语法为： SELECT select_list FROM 表1 RIGHTOUTERJOIN 表2 ON 表1.列1=表2.列2全外连接的语法为： SELECT select_list FROM 表1 FULLOUTER JOIN

38、 表2 ON 表1.列1=表2.列2821）左向外联接 联接两个表，并保留左表中没有匹配项的行。不包括右表中的不匹配行。【例38】authors 表与 titleauthor 表在各表的 au_id 列上相匹配。无论作品出版或未出版，所有作者均出现在结果集中。Use pubs go SELECT authors.au_lname AS Last, titleauthor.title_id FROM authors LEFT OUTER JOIN titleauthor ON authors.au_id = titleauthor.au_id 832）右向外联接 包括第二个命名表（“右”表，出现

39、在 JOIN 子句的最右边）中的所有行。不包括左表中的不匹配行。【例39】在 titles 和 publishers 表之间的右向外联接将包括所有的出版商，甚至包括那些在 titles 表中没有书名的出版商。 SELECT titles.title_id, titles.title, publishers.pub_name FROM titles RIGHT OUTER JOIN publishers ON titles.pub_id = publishers.pub_id843）完整外部联接 包括所有联接表中的所有行，不论它们是否匹配。【例40】查询书的书号、书名 和作者的作者号、作者名。u

40、sepubsgoselect title_id, title, au_id, au_lnamefrom titles full join authorson titles.title_id=authors.au_id85【例41】返回 titles 表中的书籍标题及对应的出版商。还返回未出版列在 titles 表中的书籍的出版商，以及不是由 publishers 表中所列的出版商出版的所有书籍标题。 SELECT titles.title , publishers.pub_name FROM publishers FULL OUTER JOIN titles ON titles.pub_id

41、= publishers.pub_id ORDER BY publishers.pub_name864）交叉联接 没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。 87二、用嵌套进行多表查询 在实际应用中，经常要用到多层查询。在SQL中，将一条SELECT语句作为另一条SELECT语句的一部分称为嵌套查询。外层的SELECT语句被称为外部查询或父查询，内层的SELECT语句成为内部查询或子查询。 881、 单值单值嵌套 在该方式下，通过子查询返回一个单一的数据，该数据可以参加相关表达式的运算。当子查询返回的是单值，可以使

42、用，=，=，!=或等比较运算符。89【例42】查询“南天有限公司”的所有订购的订单号和产品。第一步：确定“南天有限公司”的客户IDSELECT 客户IDFROM COSTOMERWHERE 公司名称=南天有限公司执行结果：客户ID3 第二步：查询客户ID为“3”订购的订单号和产品idSELECT 订单ID，产品IDFROM P_ORDERWHERE 客户ID=390SELECT SELECT 订单订单ID,ID,产品产品IDID FROM P_ORDER FROM P_ORDER WHERE WHERE 客户客户ID=ID=（SELECT SELECT 客户客户IDID FROM COSTUM

43、ER FROM COSTUMER WHERE WHERE 公司名称公司名称=南天有限公南天有限公司司） 说明：在这种方法下通过子查询取得的数据必须是惟一说明：在这种方法下通过子查询取得的数据必须是惟一的，不能返回多值，否则运行将出现错误。的，不能返回多值，否则运行将出现错误。91 多值嵌套查询 子查询的返回结果是一列值的嵌套查询称为子查询的返回结果是一列值的嵌套查询称为多值嵌套查询。多值嵌套查询。q使用使用NOTINNOTIN操作符操作符 ININ运算符可以测试表达式的值是否与子查询运算符可以测试表达式的值是否与子查询返回集中的某一个相等，返回集中的某一个相等，NOT INNOT IN恰好与其

44、相反。恰好与其相反。q使用使用NOTEXISTSNOTEXISTS操作符操作符 EXISTSEXISTS表示一个子查询至少返回一行时产生表示一个子查询至少返回一行时产生逻辑真值逻辑真值“true”true”，不返回任何行时产生逻辑假，不返回任何行时产生逻辑假值值“false”false”。与。与EXISTSEXISTS相对应的是相对应的是NOT EXISTSNOT EXISTS。 92【例 43】查询类别ID为“1”的所有订购的订单号。SELECT 订单IDFROM P_ORDERWHERE 产品ID IN (SELECT 产品ID FROM PRODUCT WHERE 类别ID=1 ) 93

45、任务2:查询所有订购了“鼠标”产品的公司的公司名称和联系方式。SELECT 公司名称,联系方式FROM customerWHERE 客户ID in (SELECT 客户ID FROM p_order WHERE 产品ID=(SELECT 产品ID FROM product WHERE 产品名=鼠标 ) )94任务4：查询“鼠标”所属的类别名和相应的说明。 SELECT 类别名，说明FROM categoryWHERE 类别ID=(SELECT 类别ID FROM product WHERE 产品名=鼠标)95【例 45】查询学习杜老师所授课程的学生的学号、姓名、该课程名称及分数。use 教学成

46、绩管理数据库select 学生信息表.学号,姓名,名称,分数from 学生信息表,教学成绩表,课程信息表where 学生信息表.学号 = 教学成绩表.学号 and 课程信息表.课程编号 = 教学成绩表.课程编号 and 教师编号 = (select教师编号 from 教师信息表 where 姓名=杜老师)96【例 46】查询200701班没有学习900011课程的学生的学号、姓名。use 教学成绩管理数据库select 学号,姓名from 学生信息表where 班级编号=200701 and学号 not in (select 学号 from 教学成绩表 where 课程编号=900011)97

47、【例 47】查询没有学习任何课程的学生的学号、姓名。use 教学成绩管理数据库select 学号,姓名from 学生信息表Where 学号 not in (select 学号 from 教学成绩表)98习题一、写出实现下列操作的Transact-SQL语句1、从pubs数据库的authors表中返回所有的数据。2、查询pubs数据库的authors表中的作者的姓（au_lname）、名（au_fname）和电话号码（phone）。3、从Northwind 数据库的Products表中检索出所有单位价格（UnitPrice）超过50美元的货物名称（Product Name ）、货物代号（Prod

48、uctID）以及每单位重量（QuantityPerUnit）。4、查询在Northwind数据库的Employees表中以字母A作Firstname第一个字母的雇员的Firstname和Lastname。99二、写出实现下列操作的Transact-SQL语句 假定当前打开的数据库为学生管理数据库，该库中共包含有三个数据表:：xscj，xskc，xsqk 。 数据表结构如下： Xscj(学生成绩)表 （学号char(6)，课程号char(3)，成绩int，学分int）xskc(学生课程)表（课程号char(3)，课程名char(16)，开课学期tinyint，学时int，学分int）xsqk(学生情况)表（系别char(10)，班级char(12)，学号varchar(30)，姓名char(12)，性别char(2)，出生年月datatime，总学分int）1、按学号的顺序显示“信息系”学生的情况记录2、查询所有课程的种类3、查询“李梅”的“计算机基础”的成绩4、查询所有学生“计算机基础”的成绩5、每门课程的平均成绩6、显示平均成绩大于60的学生学号和平均成绩100