实验3.3 连接查询

一、实验目的

掌握使用连接的方法从多个表中查询数据。理解内连接、外连接（包括左外连接、右外连接和全外连接）、自身连接的概念和使用。要求学生熟练掌握在FROM子句和在WHERE子句中指定连接条件的这两种方法。

二、实验原理

在查询语句的FROM子句中用以下形式实现各种连接操作：

l   FROM  表1   [INNER]  JOIN  表2   ON  表1．列名=表2．列名  （实现内连接）

l   FROM  表1  LEFT   [OUTER] JOIN  表2   ON  表1．列名=表2．列名  （实现左外连接）

l   FROM  表1  RIGHT   [OUTER] JOIN  表2   ON  表1．列名=表2．列名  （实现右外连接）

l   FROM  表1 FULL  [OUTER]  JOIN  表2   ON  表1．列名=表2．列名  （实现全外连接）

l   FROM  表1  AS 别名1  JOIN  表1  AS 别名2   ON  别名1．列名=别名2．列名  （实现自身连接）

在查询语句的WHERE子句中用以下形式实现各种连接操作：

l  FROM 表1，表2  WHERE 表1．列名=表2．列名  （实现内连接）

l  FROM 表1，表2  WHERE 表1．列名*=表2．列名  （实现左外连接）

l  FROM 表1，表2  WHERE 表1．列名=*表2．列名  （实现右外连接）

l  FROM 表1  AS 别名1 ，表1  AS 别名2  WHERE 别名1．列名=别名2．列名  （实现自身连接）

三、实验设备

安装有SQL SERVER 2000的计算机。

四、实验示例

1、检索product 表和sale_item表中数量大于2的相同产品的产品编号、产品名称、数量、单价。

select a.prod_id,a.qty,a.unit_price,b.prod_name

from sale_item as a inner join product asb   /*如果改成left join/right join 试分析结果*/

on (a.prod_id=b.pro_id) and a.qty>2

order by a.prod_id

 

2、查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。

select a.emp_name,a.sex,a.title,a.salary,a.addr,b.emp_name,b.sex,b.title,b.salary,b.addr

from employee as a inner join employee asb

on (a.emp_no!=b.emp_no) and(a.emp_name>b.emp_name) and (a.addr=b.addr)

 

3、查找商品名称为14寸显示器商品的销售情况，显示该商品的编号、销售数量、单价和金额

selecta.prod_id,qty,unit_price,unit_price*qty totprice

from sale_item a,product b

where a.prod_id=b.prod_id andprod_name='14寸显示器'

 

五、实验内容

1、查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。

  select distinct a.emp_name,a.sex,a.dept,a.title,a.salary,a.addr  from employeea,employeeb   where a.dept=b.deptand a.sex='女'and a.addr=b.addrand a.emp_no!=b.emp_no

2、检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。

select distinctb.prod_id,a.prod_name,b.qty,b.unit_pricefrom producta,sale_itembwhere a.prod_id=b.prod_id

SELECT     dbo.product.prod_id,dbo.product.prod_name, dbo.sale_item.qty, dbo.sale_item.unit_priceFROM         dbo.product INNER JOIN                      dbo.sale_item ONdbo.product.prod_id = dbo.sale_item.prod_id 

3、检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。

select distinct a.prod_id,a.prod_name,b.qty,b.unit_pricefrom producta,sale_itembwhere a.prod_id=b.prod_idand unit_price>2400 

SELECT    dbo.product.prod_id, dbo.product.prod_name, dbo.sale_item.qty,dbo.sale_item.unit_priceFROM         dbo.productINNER JOIN                     dbo.sale_item ON dbo.product.prod_id = dbo.sale_item.prod_idWHERE    (dbo.sale_item.unit_price > 2400)

4、查询在每张订单中订购金额超过24000元的客户名及其地址。

select a.cust_name,a.addr,b.tot_amtfrom customera,salesbwhere a.cust_id=b.cust_idand b.tot_amt>24000

SELECT    dbo.customer.cust_name, dbo.sales.tot_amtFROM         dbo.customerINNER JOIN                     dbo.sales ON dbo.customer.cust_id = dbo.sales.cust_idWHERE     (dbo.sales.tot_amt> 24000)

5、查找有销售记录的客户编号、名称和订单总额

select a.cust_id,a.cust_name,sum(tot_amt)订单总额from customera,salesbwhere a.cust_id= b.cust_idgroup bya.cust_id,a.cust_name

SELECT    dbo.customer.cust_id, dbo.sales.tot_amt, dbo.customer.cust_nameFROM         dbo.customerINNER JOIN                     dbo.sales ON dbo.customer.cust_id = dbo.sales.cust_id

6、每位客户订购的每种产品的总数量及平均单价，并按客户号，产品号从小到大排列。      

select prod_id,sum(qty)as 总数量,avg(unit_price)as 平均单价from sale_item,saleswhere sale_item.order_no=sales.order_nogroup byprod_idorder by prod_id asc

SELECT     TOP (100) PERCENTdbo.sale_item.qty, dbo.sale_item.prod_id, dbo.sales.cust_id,AVG(dbo.sale_item.unit_price) AS Expr1FROM         dbo.sale_itemINNER JOIN                     dbo.sales ON dbo.sale_item.order_no = dbo.sales.order_noGROUP BY dbo.sale_item.qty, dbo.sale_item.prod_id,dbo.sales.cust_idORDER BY dbo.sales.cust_id, dbo.sale_item.prod_id

7、查找在1997年中有销售记录的客户编号、名称和订单总额

select sales.cust_id,cust_name,tot_amtfrom sales,customerwhere sales.cust_id=customer.cust_idand YEAR(order_date)=1997;

SELECT     dbo.sales.cust_id, dbo.customer.cust_name,dbo.sales.tot_amt, YEAR(dbo.sales.order_date) AS year

FROM         dbo.sales INNER JOIN

                      dbo.customer ONdbo.sales.cust_id = dbo.customer.cust_id

WHERE     (YEAR(dbo.sales.order_date) = 1997)

8、分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。 

左向外连接：

select b.prod_id,a.prod_name,b.qty,b.unit_pricefrom product a left outer join sale_item bon(a.prod_id=b.prod_id)and b.unit_price>2400

右向外连接：

 select a.prod_id,a.prod_name,b.qty,b.unit_pricefrom product a right outer join sale_item bon a.prod_id=b.prod_id and b.unit_price>2400

完整向外连接

select a.prod_id,a.prod_name,b.qty,b.unit_pricefrom product a full join sale_item bon(a.prod_id=b.prod_id)and b.unit_price>2400