SQL分组排序及多表连接语句.docx
- 文档编号:18576620
- 上传时间:2023-08-19
- 格式:DOCX
- 页数:18
- 大小:195.89KB
SQL分组排序及多表连接语句.docx
《SQL分组排序及多表连接语句.docx》由会员分享,可在线阅读,更多相关《SQL分组排序及多表连接语句.docx(18页珍藏版)》请在冰点文库上搜索。
SQL分组排序及多表连接语句
实验五SQL分组、排序及多表连接语句
一、实验目的
1.熟练掌握SQL分组语句;
2.熟练掌握SQL排序语句;
3.熟练掌握多表连接语句;
二、实验内容
给定一个练习数据库和相应的练习题,要求上机完成,并验证结果
实验基础知识提要
从数据库中检索行,并允许从一个或多个表中选择一个或多个行或列。
虽然SELECT语句的完整语法较复杂,但是其主要的子句可归纳如下:
SELECTselect_list
FROMtable_source
[WHEREsearch_condition]
[GROUPBYgroup_by_expression]
[HAVINGsearch_condition]
[ORDERBYorder_expression[ASC|DESC]]
From子句中的连接类型
指定从其中检索行的表,当存在多个表时用“,”分隔。
表之间可以使用连接,连接的类型如下:
INNER
指定返回所有相匹配的行对。
废弃两个表中不匹配的行。
如果未指定联接类型,则这是默认设置。
LEFT[OUTER]
指定除所有由内联接返回的行外,所有来自左表的不符合指定条件的行也包含在结果集内。
来自左表的输出列设置为NULL。
RIGHT[OUTER]
指定除所有由内联接返回的行外,所有来自右表的不符合指定条件的行也包含在结果集内。
来自右表的输出列设置为NULL。
FULL[OUTER]
如果来自左表或右表的某行与选择准则不匹配,则指定在结果集内包含该行,并且将与另一个表对应的输出列设置为NULL。
除此之外,结果集中还包含通常由内联接返回的所有行。
GroupBy子句
指定用来放置输出行的组,并且如果SELECT子句
指定GROUPBY时,选择列表中任一非聚合表达式内的所有列都应包含在GROUPBY列表中,或者GROUPBY表达式必须与选择列表表达式完全匹配。
Having子句
指定组或聚合的搜索条件。
HAVING通常与GROUPBY子句一起使用。
如果不使用GROUPBY子句,HAVING的行为与WHERE子句一样。
OrderBy子句
指定结果集的排序。
除非同时指定了TOP,否则ORDERBY子句在视图、内嵌函数、派生表和子查询中无效。
ASC
指定按递增顺序,从最低值到最高值对指定列中的值进行排序。
DESC
指定按递减顺序,从最高值到最低值对指定列中的值进行排序。
空值被视为最低的可能值。
三、实验步骤
构建以下的数据表作为实验数据内容
3.1Suppliers(供货厂商)
代码
描述
数据类型
长度
约束条件
SupplierID
供货厂商编号
INT
4
主码
CompanyName
厂名
VARCHAR
40
ContactName
联系人名
VARCHAR
30
ContactTitle
联系人职位
VARCHAR
30
Address
地址
VARCHAR
60
City
城市名
VARCHAR
15
Region
地区
VARCHAR
15
PostalCode
邮政编码
VARCHAR
10
Country
国家
VARCHAR
15
Phone
电话
VARCHAR
24
Fax
传真
VARCHAR
24
HomePage
主页
VARCHAR
16
3.2Region(地区)
代码
描述
数据类型
长度
约束条件
RegionID
地区编号
INT
4
主码
RegionDescription
地区描述
VARCHAR
50
3.3Products(产品)
代码
描述
数据类型
长度
约束条件
ProductID
产品编号
INT
4
主码
ProductName
品名
VARCHAR
40
SupplierID
供货厂商编号
INT
4
CategoryID
所属种类号
INT
4
QuantityPerUnit
单位数量
VARCHAR
20
UnitPrice
单价
FLOAT
8
UnitsInStock
库存
INT
2
UnitsOnOrder
定货数
INT
2
ReorderLevel
修订量
INT
2
Discontinued
是否进行
BIT
1
3.4Orders(定单)
代码
描述
数据类型
长度
约束条件
OrderID
定单编号
INT
4
主码
CustomerID
顾客编号
VARCHAR
5
EmployeeID
职员编号
INT
4
OrderDate
定货日期
DATETIME
8
RequiredDate
交货日期
DATETIME
8
ShippedDate
载运日期
DATETIME
8
ShipVia
经由数
INT
4
Freight
运费
FLOAT
8
ShipName
船名
VARCHAR
40
ShipAddress
地址
VARCHAR
60
ShipCity
城市
VARCHAR
15
ShipRegion
地区
VARCHAR
15
PostalCode
邮政编码
VARCHAR
10
ShipCountry
国籍
VARCHAR
15
3.5OrderDetails(定单详细信息)
代码
描述
数据类型
长度
约束条件
OrderID
定单编号
INT
4
主码
ProductID
产品编号
INT
4
主码
UnitPrice
单价
FLOAT
8
Quantity
数量
INT
2
Discount
折扣
FLOAT
4
3.6Employees(职工)
代码
描述
数据类型
长度
约束条件
EmployeeID
职工编号
INT
4
主码
LastName
姓
VARCHAR
20
FirstName
名
VARCHAR
10
Title
头衔
VARCHAR
30
TitleOfCourtesy
性别
VARCHAR
25
BirthDate
生日
DATETIME
8
HireDate
受聘日期
DATETIME
8
Address
地址
VARCHAR
60
City
城市
VARCHAR
15
Region
地区
VARCHAR
15
PostalCode
邮政编码
VARCHAR
10
Country
国籍
VARCHAR
15
HomePhone
住宅电话
VARCHAR
24
Extension
分机号
VARCHAR
4
Photo
照片
IMAGE
16
Notes
备注
VARCHAR
16
ReportsTo
直接上级号
INT
4
Photopath
职工照片路径
VARCHAR
255
3.7Customers(顾客)
代码
描述
数据类型
长度
约束条件
CustomerID
顾客编号
VARCHAR
5
主码
CompanyName
公司名
VARCHAR
40
ContactName
联系人名
VARCHAR
30
ContactTitle
联系人头衔
VARCHAR
30
Address
地址
VARCHAR
60
City
城市
VARCHAR
15
Region
地区
VARCHAR
15
PostalCode
邮政编码
VARCHAR
10
Country
国籍
VARCHAR
15
Phone
电话
VARCHAR
24
Fax
传真
VARCHAR
24
3.8OldSuppliers(供应厂商备份表)
结构与Suppliers表相同
运行SQLSERVER服务管理器,确认数据库服务器开始运行。
运行企业管理器,以图示方式点击“附加数据库”,恢复db目录下的数据库文件
打开查询分析器,选择刚才恢复的数据库exampleDB,输入SQL指令,获得运行结果。
任务:
完成以下SQL查询语句
⏹查询顾客表(Customer)中没有设定区域的顾客编号和公司名
selectCustomerID,CompanyName
fromcustomers
whereRegionisNULL
⏹统计职工表(Employees)中头衔的数量
selectdistinctcount(Title)
fromEmployees
⏹查找订单表(Orders)中顾客编号为’VICTE’和’WELLI’的的订单号和运费,并按照运费的降序排列
selectOrderID,Freight
fromorders
whereCustomerIDin('VICTE','WELLI')orderbyFreightDESC
查找产品表(Products)中的平均库存总价(库存总价=单价×库存数)
selectavg(UnitPrice*UnitsInStock)
fromproducts
⏹查找提供产品的各个供应商编号及其供应的产品数量
selectSupplierID,UnitsOnOrder
fromproducts
⏹查找提供产品的各个供应商名称及其供应的产品数量
selectCompanyName,UnitsOnOrder
fromproducts,supplierswhereproducts.SupplierID=suppliers.SupplierID
⏹在订单详细信息表(OrderDetails)中查找每个订单号对应的产品种类超过4种的订单号和产品种类数,并按照产品种类数升序排列
selectOrderID,count(ProductID)
fromOrderDetailsgroupbyOrderID
havingcount(ProductID)>=4orderbycount(ProductID)
⏹查询客户的公司名和它所下订单的订单编号
selectCompanyName,OrderID
fromcustomers,orders
wherecustomers.CustomerID=orders.CustomerID
⏹找出所有的职员姓和名以及他的直接上级的姓和名
selectf1.LastNameCLastName,f1.FirstNameCFirstName,f2.LastNameRLastName,f2.FirstNameRFirstName
fromEmployeesf1LEFTJOINEmployeesf2ON(f1.ReportsTo=f2.EmployeeID)
⏹使用外部连接,查找所有的职员的基本信息以及其直接上级的姓、名
selectf1.*,f2.LastNameRLastName,f2.FirstNameRFirstName
fromEmployeesf1LEFTJOINEmployeesf2ON(f1.ReportsTo=f2.EmployeeID)
四.实验结果与分析(上交实验报告)
分析思考问题:
1.分析Where筛选和Having筛选的区别;
having和where都是用来筛选用的having是筛选组而where是筛选记录他们有各自的区别1》当分组筛选的时候用having2》其它情况用where用having就一定要和groupby连用,用groupby不一有having(它只是一个筛选条件用的)where作用于表而Having作用于组。
2.给出(可写遇到的问题及解决方法,或者对技术的理解等)多表查询中的左外连接,右外连接和全连接示例SQL语句,并分析其使用场景;
a表idnameb表idjobparent_id
1张31231
2李四2342
3王武3344
a.id同parent_id存在关系
左连接
selecta.*,b.*fromaleftjoinbona.id=b.parent_id
结果是
1张31231
2李四2342
3王武null
右连接
selecta.*,b.*fromarightjoinbona.id=b.parent_id
结果是
1张31231
2李四2342
null3344
完全连接
selecta.*,b.*fromafulljoinbona.id=b.parent_id
结果是
1张31231
2李四2342
null3344
3王武null
用法:
外联接。
外联接可以是左向外联接、右向外联接或完整外部联接。
在FROM子句中指定外联接时,可以由下列几组关键字中的一组指定:
LEFTJOIN或LEFTOUTERJOIN。
左向外联接的结果集包括LEFTOUTER子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。
如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。
RIGHTJOIN或RIGHTOUTERJOIN。
右向外联接是左向外联接的反向联接。
将返回右表的所有行。
如果右表的某行在左表中没有匹配行,则将为左表返回空值。
3.给出不少于5个聚合函数,并写出示例SQL语句,说明含义。
其中至少有两个SQL语句要包含Having子句
Avg函数:
selectsno,cno
FromSCx
WhereGrade>=(selectAVG(Grade)
FromSCy
Wherey.sno=x.sno);
Count函数:
SELECTtype
FROMtitles
GROUPBYtype
HAVINGCOUNT(type)>1
Min、Max函数:
selectmax(sal),job
fromempgroupbyjob
Sum函数:
selecta,sum(a)
fromt1,tagroupbya
havingsum(a)>100
五.讨论、心得
基本上没有问题。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 分组 排序 连接 语句
![提示](https://static.bingdoc.com/images/bang_tan.gif)