数据库-复杂查询与视图使用

任务描述

本关任务：查询

编程要求

我们已经为你建好了数据库与数据表，并添加了相应的数据内容。你只需根据右侧提示，完成以下任务：

1．查询

（1）查询仓库编号为1002的产品总库存量，显示仓库编号，总库存量；

（2）查询提成金额最高的代理商，显示代理商编号、姓名、地址、提成金额；

（3）查询编号为300的客户通过的代理商的姓名和地址。

（4）查询产品编号为0033、订购数量大于100的客户编号及其姓名。

注：这里所说的订购数量是统计顾客总共订购编号为0033产品的数量。因为，可能同一个顾客是在多个不同订单中订购了0033产品。

（5）查询每个客户的订单，显示客户编号、客户名、订单编号及订货日期。

（6）查询所有2000年以后（不包括2000年）的订货项目，显示且仅显示订单编号，产品编号，订购数量，订购单价。

（7）查询提成金额为100000以上（含100000）的代理商所对应的客户姓名。

2．视图

（1）建立含有订单编号及该订单的客户编号和代理商编号三个字段的视图,名为订单信息视图。

（2）从订单信息视图中，查询订单编号为111的产品信息（包括订单编号、客户编号、代理商编号）

（3）建立一个含有产品编号、产品库存量及产品在订量三个字段的视图，名为产品信息视图。

注意：

① 产品在订量指产品已经累计被订购的数量

② group by 子句可以将查询结果按某一列或多列的值进行分组，但出现在select后面的字段要么是聚合函数中的,要么是在group by 中的。

（4）从产品信息视图中，查询产品在订量大于6100的产品，显示产品编号及产品在定量。

（5）建立一个含有客户编号、客户所订的产品编号、该产品的订购单价及该产品的描述四个字段的视图，名为客户订购产品视图。

（6）从客户订购产品视图中，查询所订购的产品的订购单价<=1000的客户编号。注：不要显示重复数据。

订单数据库

订单数据库中的关系模型

订单数据库里面有5张数据表，关系模型如下：

1、代理商（代理商编号、姓名、地址、邮政编码、提成金额、提成比例）其中代理商编号为primary key（主码）

字段名	数据类型	备注
代理商编号	char(4)	primary key
姓名	nvarchar(10)
地址	nvarchar(20)
邮政编码	char(6)
提成金额	smallmoney
提成比例	tinyint

2、客户（客户编号、姓名、地址、邮政编码、收支差额、贷款限额、代理商编号）其中客户编号为primary key（主码）

字段名	数据类型	备注
客户编号	char(4)	primary key
姓名	nvarchar(10)
地址	nvarchar(20)
邮政编码	char(6)
收支差额	smallmoney
贷款限额	smallmoney
代理商编号	char(4)	foreign key

3．产品（产品编号、描述信息、库存量、类别、仓库的编号、价格）其中产品的编号为primary key（主码）

字段名	数据类型	备注
产品编号	char(4)	primary key
描述信息	nvarchar(20)
库存量	int
类别	tinyint
仓库编号	char(4)
价格	smallmoney

4．订单（订单编号、订货日期、客户编号）

字段名	数据类型	备注
订单编号	char(4)	primary key
订货日期	Smalldatetime
客户编号	char(4)	foreign key

5．订货项目（订单编号、产品编号、订购数量、订购单价）

字段名	数据类型	备注
订单编号	char(4)	primary key ， foreign key
产品编号	char(4)	primary key， foreign key
订购数量	smallint
订购单价	smallmoney

注意：这里不是有两个主键，没有数据表会有两个主键。
这里是联合主键，也就是说通过字段订单编号和产品编号来确认一条记录的唯一性。
primary key(订单编号,产品编号)

订单数据库中原始数据

1．对于代理商（代理商编号、姓名、地址、邮政编码、提成金额、提成比例）

01、联邦、东环路1号、541001、30000.00、40

02、惠普、东环路2号、541002、4000.00、10

03、三洋、东环路3号、541003、10000.00、30

04、联想、东环路4号、541004、100000.00、60

2．客户（客户编号、姓名、地址、邮政编码、收支差额、贷款限额、代理商编号）

100、张三、西环路1号、100001、10.00、100.00、02

200、李四、西环路2号、100001、-10.00、10.00、04

300、王五、西环路3号、100001、100.00、1000.00、02

400、赵六、西环路4号、100001、600.00、2000.00、01

600、李明、西环路6号、100001、20.00、300.00、03

700、张进、西环路7号、100001、400.00、1000.00、03

3．产品（产品的编号、描述信息、库存量、类别、仓库的编号、价格）

0011、药物,单位(瓶)、1000、12、1001、40.00

0022、机器,单位(件)、300、3、1002、50000.00

0033、中药,单位(包)、800、12、1001、300.00

0044、软件,单位(套)、1500、10、1003、2000.00

0055、家具,单位(件)、6000、3、1002、1000.00

0066、小型机,单位(台)、10000、3、1002、200000.00

4．订单（订单编号、订货日期、客户编号）

111、2000-10-01、200

222、2000-09-01、200

444、2002-02-02、300

555、2003-03-03、100

666、2005-05-06、100

5．订货项目（订单编号、产品编号、订购数量、订购单价）

111、0033、100、280.00

222、0066、6000、150000.00

444、0011、300、39.00

555、0055、5500、950.00

444、0055、1000、1000.00

555、0033、100、280.00

666、0033、50、270.00

创建视图

例：建立电商系学生的视图

create view ec_student as select sno, sname from student where dept=’ec’

删除视图

DROP VIEW <视图名>

查询

查询包括：单表查询、连接查询、带有exists的相关子查询、集合操作四中。

select…from   常用语句执行过程 
select…     ⑤    投影 
from…    	①    table→内存               
where…		②    选取元组             
group…   	③    分组         
having…  	④    选择分组          
[{union|…}  ⑥    查徇结果的集合运算
select…  ]	①～⑤ order by…          
order by…	⑦    排序输出

1、单表查询

group by ：group by 子句可以将查询结果按某一列或多列的值进行分组，但出现在select后面的字段要么是聚合函数中的,要么是在group by 中的。

order by：用order by子句对查询结果按照一个或多个列的值进行升/降排列输出,升序为ASC；降序为desc,空值将作为最大值排序

having 与 where的区别 :

where 决定哪些元组被选择参加运算，作用于关系中的元组.
having 决定哪些分组符合要求，作用于分组

2、连接查询

连接查询包括：多表连接查询、单表连接查询(自连接)、外连接查询、嵌套查询4种

连接条件一

[表名1.] 列名1 比较运算符 [表名2.]列名2

连接条件二

[表名1.]列名1 between [表名2.]列名2 and [表名2.]列名3 连接条件中的列名称为连接字段，对应的连接字段应是可比的。

执行过程：采用表扫描的方法，在表1中找到第一个元组，然后从头开始扫描表2，查找到满足条件的元组即进行串接并存入结果表中；再继续扫描表2，依次类推，直到表2末尾。再从表1中取第二个元组，重复上述的操作，直到表1中的元组全部处理完毕。

2.1 单表连接（自连接）

用表别名把一个表定义为两个不同的表进行连接。例：查找至少选修了2号和4号课程的学生的学号

select FIRST.snofrom SC as FIRST, SC as SECOND where FIRST.Sno=SECOND.Sno and FIRST.cno='s2' and SECOND.cno='4'

2.2 外连接查询

外连接查询包括：Left join、right join、full join

2.3 嵌套查询

• 在select … from … where语句结构的where子句中可嵌入一个select语句块

• 其上层查询称为外层查询或父查询，其下层查询称为内层查询或子查询

• SQL语言允许使用多重嵌套查询 • 在子查询中不允许使用order by子句

• 嵌套查询的实现一般是从里到外，即先进行子查询，再把其结果用于父查询作为条件

（1）返回单个值的子查询

例：求与“刘力”同一个系的学生名，年龄方法一：

select Sname, Sage from student where Sdept = (select sdept from student where  Sname = '刘力');

方法二：

select FIRST.Sname, FIRST.Sage from Student FIRST, Student SECONDwhere FIRST.Sdept = SECOND.Sdept AND SECOND.Sname = '刘力';

（2）返回一组值的子查询

例：求选修“C6”课程且成绩超过90分的学生方法一：

select * from student where sno IN (select sno from SC where Cno='C6' AND Grade>90);

方法二（连接查询）：

select student.*from student，SCwhere Student.Sno=SC.Sno AND Cno='C6' AND Grade>90;

例：求比计算机系中所有学生年龄小的其他系的学生方法一：

select * from student where sdept!='CS' AND sage < ANY (select Sage from Student where Sdept='CS');

方法二：

select  *from Studentwhere Sdept!='CS' AND Sage < (select MAX(Sage) from Student where Sdept='CS');

（3）多重子查询

表结构：

Teacher(tno, tname, salary, dno)

Department(dno, dname)

例：求工资介于“张三”与“里司”两个之间的职工

select * from teacherwhere    
Salary >= (select MIN(Salary) from teacher where Tname IN ('张三', '里司')) AND      Salary <= (select MAX(Salary) from teacher where Tname IN ('张三', '里司');

（4）在from语句中使用子查询，对查询结果定义表名及列名

例：求平均成绩超过80分的学号及平均成绩

select Sno, avg_G from 
(select Sno, avg(Grade) from SC group by Sno) AS RA(Sno, avg_G)where avg_G > 80;

AS RA(Sno, avg_G)为查询作为定义表名（RA）和列名（Sno, avg_G）

3、带有exists的相关子查询

• 不相关子查询：子查询的查询条件不依赖于父查询的称为不相关子查询。

• 相关子查询：子查询的查询条件依赖于外层父查询的某个属性值的称为相关子查询，带exists 的子查询就是相关子查询

• exists表示存在量词，带有exists的子查询不返回任何记录的数据，只返回逻辑值“True” 或“False”

例：求所有选修了“C1”课程的学生名。

不相关子查询

select Sname from student where sno IN ( select sno from SC where Cno = 'C1' );

相关子查询

select Sname from student where exists (select * from SC where student.sno=SC.sno AND Cno = 'C1' );

相关子查询执行过程：先在外层查询中取student表的第一个元组（记录），用该记录的相关的属性值（在内层where子句中给定的）处理内层查询，若外层的where子句返回‘TRUE’值，则此元组送入结果的表中。然后再取下一个元组；重复上述过程直到外层表的记录全部遍历一次为止。

• 不关心子查询的具体内容，因此用 select * exists + 子查询用来判断该子查询是否返回元组

• 当子查询的结果集非空时，exists 为“True”；当子查询的结果集为空时，exists为“False”。

• not exists ：若子查询结果为空，返回“TRUE”值，否则返回“FALSE”

4、SQL的集合操作

• 属性个数必须一致、对应的类型必须一致

• 属性名可以不一致，最终结果集采用第一个结果的属性名

• 缺省为自动去除重复元组，除非显式说明ALL • order by放在整个语句的最后

4.1 “并”操作，例：查询计算机系的学生或者年龄不大于19岁的学生，并按年龄倒排序。

select * from student where Sdept='CS'UNION 
select * from student where AGE <= 19order by AGE desc

4.2 “交”操作，例：查询计算机系的学生并且年龄不大于19岁的学生，并按年龄倒排序。

(select * from student where Sdept = 'CS')INTERSECT
(select * from student where AGE <= 19)
order by AGE desc

4.3 “差”操作，例：查询选修课程1但没有选修课程2的学生。

select Sname, Sdeptfrom studentwhere sno IN 
((select sno from SC where Cno='1')EXCEPT    
(select sno from SC where Cno='2'))

对订单数据库进行查询等操作

USE 订单数据库
GO
SET NOCOUNT ON

/*1．查询*/
/*
（1）查询仓库编号为1002的产品总库存量，显示仓库编号，总库存量；
*/
--   第一题SQL语句开始   --
SELECT 仓库编号, sum(库存量) 总库存量 FROM 产品 WHERE 仓库编号='1002' GROUP BY 仓库编号;



--   第一题结束  --
GO


/*
（2）查询提成金额最高的代理商，显示代理商编号、姓名、地址、提成金额；
*/
--  第二题SQL语句开始  --

SELECT TOP 1 代理商编号, 姓名, 地址, 提成金额 FROM 代理商 ORDER BY 提成金额  DESC;

--  第二题结束  --
GO


/*
（3）查询编号为300的客户通过的代理商的姓名和地址。
 */
--  第三题SQL语句开始 --
 
 SELECT 姓名, 地址
 FROM 代理商 WHERE 代理商编号 =
 (
	 SELECT 代理商编号
	 FROM 客户 WHERE 客户编号='300'
 );
 
 
--  第三题结束   --
GO

/*
（4）查询产品编号为0033、订购数量大于100的客户编号及其姓名。
 */
--  第四题SQL语句开始 --
 
 SELECT top 1 [客户编号], [姓名] FROM [客户]
 WHERE [客户编号] IN
 (
	 SELECT DISTINCT [客户编号] FROM [订单]
	 WHERE [订单编号] IN
	 (
		 SELECT 订单编号 FROM 订货项目 WHERE 产品编号='0033' AND [订购数量]>=100
	 )
 )
--  第四题结束   --
GO

/*
（5）查询每个客户的订单，显示客户编号、客户名、订单编号及订货日期。
 */
--  第五题SQL语句开始 --
 
 SELECT [客户].[客户编号], [姓名], [订单编号], [订货日期] FROM [客户], [订单]
 WHERE [客户].[客户编号]=[订单].[客户编号];
 
--  第五题结束   --
GO

/*
（6）查询所有2000年以后（不包括2000年）的订货项目，显示且仅显示订单编号，产品编号，订购数量，订购单价。
 */
--  第六题SQL语句开始 --
 SELECT [订货项目].[订单编号], [产品编号], [订购数量], [订购单价] FROM [订货项目], [订单]
 WHERE [订货项目].[订单编号]=[订单].[订单编号] AND [订单].[订货日期] > '2000/12/31';
 
 
--  第六题结束   --
GO

/*
（7）查询提成金额为100000以上（含100000）的代理商所对应的客户姓名。
 */
--  第七题SQL语句开始 --
 SELECT [客户].[姓名] FROM [代理商], [客户]
 WHERE [代理商].[代理商编号]=[客户].[代理商编号] AND [代理商].[提成金额]>=100000;
 
 
--  第七题结束   --
GO


/*1．视图*/
/*
（1）建立含有订单编号及该订单的客户编号和代理商编号三个字段的视图,名为订单信息视图。
*/
--   第一题SQL语句开始   --
CREATE VIEW 订单信息视图
AS
SELECT [订单编号], [客户].[客户编号], [代理商编号] 
FROM [客户], [订单] WHERE [客户].[客户编号]=[订单].[客户编号];

--   第一题结束  --
GO


/*
（2）从订单信息视图中，查询订单编号为111的产品信息（包括订单编号、客户编号、代理商编号）
*/
--  第二题SQL语句开始  --

SELECT [订单编号], [客户编号], [代理商编号]
FROM 订单信息视图 WHERE [订单编号]='111';

--  第二题结束  --
GO


/*
（3）建立一个含有产品编号、产品库存量及产品在订量三个字段的视图，
名为产品信息视图。
注：① 产品在定量指产品已经累计被订购的数量
   ② group by 子句可以将查询结果按某一列或多列的值进行分组，但出现在select后面的字段 要么是聚合函数中的,要么是在group by 中的。
 */
--  第三题SQL语句开始 --
 
 CREATE VIEW 产品信息视图
 AS
 SELECT [产品].[产品编号], [产品].[库存量], SUM([订购数量]) 产品在定量
 FROM [产品], [订货项目] WHERE [产品].[产品编号]=[订货项目].[产品编号]
 GROUP BY [产品].[产品编号], [产品].[库存量];
 
--  第三题结束   --
GO

/*
（4）从产品信息视图中，查询产品在定量大于6100的产品，显示产品编号及产品在定量。
 */
--  第四题SQL语句开始 --
 
 SELECT [产品编号], [产品在定量]
 FROM 产品信息视图 
 WHERE [产品在定量]>6100;
 
--  第四题结束   --
GO

/*
（5）建立一个含有客户编号、客户所订的产品编号、该产品的订购单价及该产品的描述四个字段的视图，
名为客户订购产品视图。
 */
--  第五题SQL语句开始 --
 CREATE VIEW 客户订购产品视图
 AS
 SELECT [订单].[客户编号], [产品].[产品编号], [订货项目].[订购单价], [产品].[描述信息]
 FROM [订单], [订货项目], [产品]
 WHERE [订单].[订单编号]=[订货项目].[订单编号] AND [订货项目].[产品编号]=[产品].[产品编号]
 
 
--  第五题结束   --
GO

/*
（6）从客户订购产品视图中，查询所订购的产品的订购单价<=1000的客户编号。
注：不要显示重复数据。
 */
--  第六题SQL语句开始 --
 SELECT DISTINCT[客户编号] FROM 客户订购产品视图
 WHERE [订购单价]<=1000;
 
 
--  第六题结束   --
GO

posted @ 2021-01-15 16:42 漫漫长夜何时休阅读(498) 评论(0) 编辑收藏举报

刷新页面返回顶部

阁下