SQL分组、排序及多表连接语句.doc

实验五SQL分组、排序及多表连接语句一、 实验目的1. 熟练掌握SQL分组语句；2. 熟练掌握SQL排序语句；3. 熟练掌握多表连接语句；二、实验内容给定一个练习数据库和相应的练习题，要求上机完成，并验证结果实验基础知识提要从数据库中检索行，并允许从一个或多个表中选择一个或多个行或列。虽然 SELECT 语句的完整语法较复杂，但是其主要的子句可归纳如下：SELECT select_listFROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC From子句中的连接类型指定从其中检索行的表，当存在多个表时用“,”分隔。表之间可以使用连接，连接的类型如下：INNER 指定返回所有相匹配的行对。废弃两个表中不匹配的行。如果未指定联接类型，则这是默认设置。LEFT OUTER 指定除所有由内联接返回的行外，所有来自左表的不符合指定条件的行也包含在结果集内。来自左表的输出列设置为 NULL。RIGHT OUTER 指定除所有由内联接返回的行外，所有来自右表的不符合指定条件的行也包含在结果集内。来自右表的输出列设置为 NULL。FULL OUTER 如果来自左表或右表的某行与选择准则不匹配，则指定在结果集内包含该行，并且将与另一个表对应的输出列设置为 NULL。除此之外，结果集中还包含通常由内联接返回的所有行。Group By子句指定用来放置输出行的组，并且如果 SELECT 子句 中包含聚合函数，则计算每组的汇总值。指定 GROUP BY 时，选择列表中任一非聚合表达式内的所有列都应包含在 GROUP BY 列表中，或者 GROUP BY 表达式必须与选择列表表达式完全匹配。Having子句指定组或聚合的搜索条件。HAVING 通常与 GROUP BY 子句一起使用。如果不使用 GROUP BY 子句，HAVING 的行为与 WHERE 子句一样。Order By子句指定结果集的排序。除非同时指定了 TOP，否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。ASC指定按递增顺序，从最低值到最高值对指定列中的值进行排序。DESC指定按递减顺序，从最高值到最低值对指定列中的值进行排序。空值被视为最低的可能值。三、实验步骤构建以下的数据表作为实验数据内容3.1 Suppliers(供货厂商)代码描述数据类型长度约束条件SupplierID供货厂商编号INT4主码CompanyName厂名VARCHAR40ContactName联系人名VARCHAR30ContactTitle联系人职位VARCHAR30Address地址VARCHAR60City城市名VARCHAR15Region地区VARCHAR15PostalCode邮政编码VARCHAR10Country国家VARCHAR15Phone电话VARCHAR24Fax传真VARCHAR24HomePage主页VARCHAR163.2 Region（地区）代码描述数据类型长度约束条件RegionID地区编号INT4主码RegionDescription地区描述VARCHAR503.3 Products（产品）代码描述数据类型长度约束条件ProductID产品编号INT4主码ProductName品名VARCHAR40SupplierID供货厂商编号INT4CategoryID所属种类号INT4QuantityPerUnit单位数量VARCHAR20UnitPrice单价FLOAT8UnitsInStock库存INT2UnitsOnOrder定货数INT2ReorderLevel修订量INT2Discontinued是否进行BIT13.4 Orders（定单）代码描述数据类型长度约束条件OrderID定单编号INT4主码CustomerID顾客编号VARCHAR5EmployeeID职员编号INT4OrderDate定货日期DATETIME8RequiredDate交货日期DATETIME8ShippedDate载运日期DATETIME8ShipVia经由数INT4Freight运费FLOAT8ShipName船名VARCHAR40ShipAddress地址VARCHAR60ShipCity城市VARCHAR15ShipRegion地区VARCHAR15PostalCode邮政编码VARCHAR10ShipCountry国籍VARCHAR153.5 OrderDetails（定单详细信息）代码描述数据类型长度约束条件OrderID定单编号INT4主码ProductID产品编号INT4主码UnitPrice单价FLOAT8Quantity数量INT2Discount折扣FLOAT43.6 Employees（职工）代码描述数据类型长度约束条件EmployeeID职工编号INT4主码LastName姓VARCHAR20FirstName名VARCHAR10Title头衔VARCHAR30TitleOfCourtesy性别VARCHAR25BirthDate生日DATETIME8HireDate受聘日期DATETIME8Address地址VARCHAR60City城市VARCHAR15Region地区VARCHAR15PostalCode邮政编码VARCHAR10Country国籍VARCHAR15HomePhone住宅电话VARCHAR24Extension分机号VARCHAR4Photo照片IMAGE16Notes备注VARCHAR16ReportsTo直接上级号INT4Photopath职工照片路径VARCHAR2553.7 Customers（顾客）代码描述数据类型长度约束条件CustomerID顾客编号VARCHAR5主码CompanyName公司名VARCHAR40ContactName联系人名VARCHAR30ContactTitle联系人头衔VARCHAR30Address地址VARCHAR60City城市VARCHAR15Region地区VARCHAR15PostalCode邮政编码VARCHAR10Country国籍VARCHAR15Phone电话VARCHAR24Fax传真VARCHAR243.8 OldSuppliers（供应厂商备份表）结构与Suppliers表相同运行SQL SERVER服务管理器，确认数据库服务器开始运行。运行企业管理器，以图示方式点击“附加数据库”，恢复db目录下的数据库文件打开查询分析器，选择刚才恢复的数据库exampleDB，输入SQL指令，获得运行结果。任务：完成以下SQL查询语句n 查询顾客表(Customer)中没有设定区域的顾客编号和公司名select CustomerID,CompanyName from customers where Region is NULLn 统计职工表(Employees)中头衔的数量select distinct count(Title) from Employeesn 查找订单表(Orders)中顾客编号为VICTE和WELLI的的订单号和运费，并按照运费的降序排列select OrderID,Freight from orders where CustomerID in (VICTE,WELLI) order by Freight DESC 查找产品表(Products)中的平均库存总价(库存总价=单价库存数)select avg(UnitPrice*UnitsInStock) from productsn 查找提供产品的各个供应商编号及其供应的产品数量select SupplierID,UnitsOnOrder from productsn 查找提供产品的各个供应商名称及其供应的产品数量select CompanyName,UnitsOnOrder from products,suppliers where products.SupplierID=suppliers.SupplierIDn 在订单详细信息表(OrderDetails)中查找每个订单号对应的产品种类超过4种的订单号和产品种类数，并按照产品种类数升序排列select OrderID,count(ProductID) from OrderDetails group by OrderID having count(ProductID)=4 order by count(ProductID)n 查询客户的公司名和它所下订单的订单编号select CompanyName,OrderID from customers,orders where customers.CustomerID=orders.CustomerIDn 找出所有的职员姓和名以及他的直接上级的姓和名select f1.LastName CLastName,f1.FirstName CFirstName,f2.LastName RLastName,f2.FirstName RFirstNamefrom Employees f1 LEFT JOIN Employees f2 ON (f1.ReportsTo=f2.EmployeeID)n 使用外部连接，查找所有的职员的基本信息以及其直接上级的姓、名select f1.*,f2.LastName RLastName,f2.FirstName RFirstNamefrom Employees f1 LEFT JOIN Employees f2 ON (f1.ReportsTo=f2.EmployeeID)四. 实验结果与分析（上交实验报告）分析思考问题：1. 分析Where筛选和Having筛选的区别；having 和where 都是用来筛选用的 having 是筛选组 而where是筛选记录他们有各自的区别1当分组筛选的时候 用having2其它情况用where用having就一定要和group by连用，用group by不一有having （它只是一个筛选条件用的）where作用于表而Having作用于组。2. 给出（可写遇到的问题及解决方法，或者对技术的理解等）多表查询中的左外连接，右外连接和全连接示例SQL语句，并分析其使用场景； a表 id name b表 id job parent_id 1 张3 1 23 1 2 李四 2 34 2 3 王武 3 34 4 a.id同parent_id 存在关系 左连接 select a.*,b.* from a left join b on a.id=b.parent_id结果是 1 张3 1 23 1 2 李四 2 34 2 3 王武 null 右连接 select a.*,b.* from a right join b on a.id=b.parent_id 结果是 1 张3 1 23 1 2 李四 2 34 2 null 3 34 4 完全连接 select a.*,b.* from a full join b on a.id=b.parent_id 结果是 1 张3 1 23 1 2 李四 2 34 2 null 3 34 4 3 王武 null用法：外联接。外联接可以是左向外联接、右向外联接或完整外部联接。 在 FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定： LEFT JOIN 或 LEFT OUTER JOIN。 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。 RIGHT JOIN 或 RIGHT OUTER JOIN。 右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。3. 给出不少于5个聚合函数，并写出示例SQL语句，说明含义。其中至少有两个SQL语句要包含Having子句Avg 函数：select sno