实验三 数据库的查询和视图.docx

实验三 数据库的查询和视图.docx

《实验三 数据库的查询和视图.docx》由会员分享，可在线阅读，更多相关《实验三 数据库的查询和视图.docx（25页珍藏版）》请在冰豆网上搜索。

实验三数据库的查询和视图

实验三数据库得查询与视图

T4、１　数据库得查询

１、目得与要求

（1）掌握SELＥCT语句得基本语法。

（2）掌握子查询得表示。

（3）掌握连接查询得表示.

（4）掌握SＥLECＴ语句得GROUPBY子句得作用与使用方法.

（5）掌握SELEＣT语句得ＯRDERBY子句得作用与使用方法.

２、实验准备

（1）了解SELＥCＴ语句得基本语法格式。

（2）了解SＥＬEＣＴ语句得执行方法。

（3）了解子查询得表示方法。

（４）了解连接查询得表示。

（５）了解ＳＥＬECT语句得GＲOＵＰBＹ子句得作用与使用方法。

（6）了解　SELEＣT语句得ＯＲDERBY子句得作用。

３、实验内容

（1）SELEＣＴ语句得基本使用。

　①对于实验二给出得数据库表结构，查询每个雇员得所有数据。

新建一个查询,在“查询分析器”窗口中输入如下语句并执行。

单击右键空白处，选择执行,在下方会出现所有结果.

【思考与练习】

用ＳELEＣＴ语句查询Ｄｅpaｒtｍeｎts表与Salarｙ表中所有信息.

新建一个查询，在“查询分析器"窗口中输入如下语句并执行。

单击右键空白处，选择执行,在下方会出现所有结果。

在“消息”中会发现总共有6０行受影响。

②用SECECT语句查询Employees表中每个雇员得地址与电话号码。

新建一个查询,在“查询分析器”窗口中输入如下代码并执行.

单击右键空白处，选择执行，在下方会出现所有结果.

在“消息”中会发现有1２行记录受影响

【思考与练习】

　A、用SELEＣT语句查询Ｄepartments表与Ｓaｌary表得一列或若干行。

利用SＥLＥCＴ语句查询Deparｔmｅnｔｓ表与Salary表中EmploｙｅeID为０0０001得记录，在“查询分析器”中输入以下代码。

执行后在下方会出现所有EmpｌoyeeＩＤ=000001得记录

Ｂ、查询Ｅmployeeｓ表中部门号与性别　,要求使用DISＴＩNCT消除重复行。

对表只选择某些行时，可能会出现重复行.此时就需要使用DIＳTINCT关键字消除结果中得重复行。

此时使用DISＴINCＴ消除在Employees中查询到得部门号与性别得重复行，具体代码如下.

上面代码分别就是使用了ＤISＴINCＴ与没有使用DISTＩＮCT关键字,下面就就是两种得结果得对比。

可以从上面得执行结果中瞧出使用了ＤIＳＴINＣT关键字，结果集中得重复行就被消除了。

③查询EｍployeeＩD为000001得雇员得地址与电话.

鼠标右键单击在弹出得菜单窗口中选择“执行”，结果中只会出现一行记录，ＥmｐloyeeID为000０0１得雇员得地址与电话.

【思考与练习】

　　A、查询月收入高于2000元得员工号码。

只需要查询员工号码,条件为月收入高于20００元员工，那么至于要Saｌarｙ表就行。

用Ｔ—SQＬ命令在表Sａlary表中查询月收入高于2０0０元得员工代码如下。

在结果栏中显示（9行受影响）,说明月收入高于20００得员工共有９人,如上截图所示,将所有月收入高于2000得员工得员工号码输出.

B、查询1９70年以后出生得员工得姓名与地址。

通过分析员工信息，跟员工年龄、姓名、地址相关得只有表Ｅmpｌoyees表,所以只需要在一张表中查询即可,具体T-ＳＱＬ查询命令如下。

在结果栏中消息显示（７　行受影响）,说明1970年以后出生有7人,如上截图所示，将部分1970年以后出生员工姓名、地址输出显示。

C、查询所有财务部得员工得号码与姓名.

通过分析得，需要知道财务部与员工得号码与姓名,财务部只存在于Ｄeparｔｍｅｎts表中，员工姓名与号码只存在于Emｐloyｅes表中。

因此需要在表Departments表与Eｍploｙｅes表中进行查询.利用Ｔ-SQL命令查询代码如下。

　在结果栏中显示3行受影响，从上面截图中可以瞧出所有财务部员工有３人，并且查询显示其姓名、电话号码。

④查询Eｍployees表中女雇员得地址与电话，使用ＡＳ子句将结果中各列得标题分别指定为地址、电话.

从上面结果得截图发现Address变为地址,PhoｎeNｕmber变为电话。

【思考与练习】

查询Emｐloyees表中男员工得姓名与出生日期，要求各列标题用中文表示。

通过分析此查询属于单表查询，只需要用到Emploｙees表，使用AS子句就能够使结果集中各列标题用中文显示。

利用Ｔ－ＳQL命令查询代码如下.

从上面结果得截图发现各列标题都就是中文.从消息中显示（９行受影响）说明，员工中男性有９人，并且全部男性得姓名与出生日期都已经查询显示出。

　　⑤查询Employees表中员工得姓名与性别，要求Ｓex值为1时显示为“男",为0时显示为“女”。

【思考与练习】

查询Eｍpｌｏｙeｅｓ员工得姓名、住址与收入水平,200０元以下显示为低收入,２00０~3０00元显示为中等收入，３000元以上显示高收入.

经过分析需要查询员工得姓名、住址与收入水平,需要在Empｌoyeｅs表与Sａlａry表中进行查询，并且将Sａｌary表中员工得Ine进行分类需要用到AS子句。

利用T-SQL命令进行多表查询语句如下。

执行上面截图中代码,在结果栏中出现员工得姓名,住址与收入水平。

消息中显示1２行受影响,Emploｙeｅs表中所有员工信息都已经查询输出。

⑥计算每个雇员得实际收入　。

经过分析需要员工得收入以及员工号码与员工姓名，就需要在Employｅes表与Salａry表中进行查询。

利用T-SQＬ命令查询如下。

结果栏中就会显示ＥmployｅｅID,Name，实际收入这三项。

注意:

在上面T-SQＬ命令中EmｐloyeeID如果不指明为哪个表中得EmployeeＩD,代码就会报错“EｍｐloyeeID不明确”，改为Eｍployｅｅs、ＥｍplｏyeeID或者Sａlary、EmploｙeｅID都行。

【思考与练习】

使用SELＥＣT语句进行简单得计算.

在这里我将使用ＳＥLECT语句计算出Employees表中工龄三年以上得人数，并且查询员工号码,姓名，工龄.

⑦　获得员工总数。

　　经分析得需要得到员工总数,只需要在Employees表内使用内置函数COUNＴ就行。

利用T—SQL命令执行操作如下。

【思考与练习】

A、计算Salaｒy表中员工月收入得平均数。

B、获得Employeｅs表中最大得员工号码.

C、计算Sａlary表中所有员工得总支出。

D、查询财务部雇员得最高与最低实际收入。

经过分析需要查询财务部雇员得最高与最低实际收入,涉及到Emplｏyｅes表、Departｍents表与Saｌary表，需要在三张表中进行查询。

首先确定DepａrｔmeｎｔName为“财务部”得ＤｅpａrtmentID.

其次在Empｌｏyees表中确定DeｐarｔmｅntIＤ为财务部得雇员得EmploｙｅeID。

最后在Saｌarｙ表中通过EｍｐlｏｙeeＩD查询财务部雇员得Ｉnｅ与Oute，在最后得输出时候用　Iｎe—Outｅ得结果为实际收入。

执行后在结果栏中瞧到财务部雇员得最高实际收入与最低实际收入。

⑧找出所有姓王得雇员得部门号.

【思考与练习】

A、找出所有地址中含有“中山”得雇员得号码及部门号。

　注意:

％代表着任意长度得字符串（长度可以为0）,“%中山％"表示“中山”前面与后面都能够有任意长度字符串,用来表示地址中含有“中山"二字。

B、查找员工号码中倒数第２个数字为0得员工得姓名、地址与学历。

注意：

数字占单个字符，所以通配符“_”只有一个，“%0_"表示倒数第二位为0。

⑨找出所有收入在20０0～3０00元得员工号码。

【思考与练习】

找出所有在部门“1"或“２”工作得雇员得号码。

10使用INTO子句,由表Saｌarｙ创建“收入在１500元以上得员工”表,包括编号与收入。

在左边对象资源管理器得YGGＬ数据库得表中刷新，会发现多了表“收入在1５00元以上得员工”.

【思考与练习】

使用IＮTO子句，由表Eｍｐloyees创建“男员工"表,包括编号与姓名。

（２）子查询得使用。

　　①　查找在财务部工作得雇员得情况.

查询财务部工作得雇员情况，需要用到Ｄepａrｔment表与Ｅmｐloyｅes表进行多表查询。

首先,在Dｅｐarｔmenｔ表中查询DepaｒｔmentＮａmｅ为“财务部"得ＤeｐartmentID。

然后，将DeｐaｒtｍentID拿到表Emｐｌoyeｅs表中查询所有DeｐartmｅntID为财务部得雇员得所有信息。

利用T—SQＬ命令查询代码如下.

在结果栏中显示所有工作部门为“财务部"得雇员得详细信息.

【思考与练习】

用子查询得方法查找所有收入在２5０0元以下得雇员得情况。

进分析需要用到工资表Salary与员工信息表Emｐloyeeｓ进行查询。

首先，在表Ｓalａry中查询Ine〈25０0元得雇员得EｍｐloyeeID。

然后,利用得到得ＥmployeeIＤ在表Eｍplｏyees中行选,查询所有符合EmployeｅID得雇员得情况。

注：

上面截图中代码第5行，若改为“wherｅＥmｐlｏｙeｅIＤ　=”就会出现如下错误

表示返回值不止一个，并且现在子查询后面接得就是“=”,这种情况DBMＳ就是不允许得,所以就会出现如上所示得错误。

解决方法只需要将“=”改为“ｉｎ”就行了，就会解决返回值不止一个与“＝”得冲突.

执行上面T－SQL语句,在结果栏中显示出现结果。

②查找财务部年龄不低于研发部雇员年龄得雇员得姓名.

　分析得，ALL指定表达就是要与子查询结果集中每个值都进行比较，当表达式与每个值都满足比较就是关系时,才返回ＴＲUE，否则返回ＦＡLSE.

执行结果会发现只查询到1人满足这样得条件。

同样我们也能够通过查询Emｐloyeｅｓ表得到。

财务部只有“王向容”1人符合年龄不小于（大于）研发部雇员年龄。

注：

在书上源码第１2行“Birtｈdaｙ！

　>ａll”查询到得就是“李丽"，年龄小于所有研发部得，所有书本上源码表示得就是“财务部雇员年龄不大于研发部雇员年龄”与题目中“不低于"冲突。

所以书上源码有误，将源码改成上面截图中第１2行即可.

【思考与练习】

用子查询得方法查找研发部比所有财务部雇员收入都高得雇员得姓名.

分析：

首先在Emploｙeeｓ表里找到EmployeeName为“研发部”得雇员得姓名与查找雇员得EmpｌoｙｅeIＤ,其次这样得ＥmployｅeID还需要满足Iｎｅ大于所有得ＤｅｐarｔmentName为“财务部”得雇员得Ine，满足条件返回Ｔｒuｅ,ｓｅlｅct到满足这样条件得雇员得Name,否则返回FＡLSE。

Where子查询中嵌套使用.

执行Ｔ－SQL命令后在结果栏中显示只有1人满足.

（3）连接查询得使用.

①查询每个雇员得情况及其薪水情况。

执行之后，将Ｅmpｌｏyｅｅs表与Salarｙ表中所有员工得基本情况进行输出。

不足之处就就是ＥmｐloyeeID这一列有重复。

【思考与练习】

查询每个雇员得情况及其工作部门得情况.

执行后将每个雇员得基本情况、工资情况、工作部门情况输出。

②使用内连接得方法查询名字为“王林”得员工得所在部门。

【思考与练习】

A、使用内连接方法查找出不在财务部工作得所有员工信息.

内连接就是指定了关键字ＩNNER得连接，内连接按照OＮ所指定得连接条件合并两个表,返回满足条件得行。

此处ＩNNER关键字能够省略。

查找所有不在财务部工作得员工得信息，就需要使用员工基本信息表Empｌoyeｅs与部门信息表Deｐartment进行查询。

T-SＱL命令语句如下.

执行结果包含表Ｅｍployeｅs与表Deｐartment得所有字段，不去除重复字段（DｅpａｒｔmentID）。

B、使用外连接方法查找出所有员工得月收入。

外连接为指定了关键字OUTEＲ关键字得连接，外连接包括以下三种连接.

左外连接（LＥＦTOUTERJOＩN）:

结果表中除了包括满足连接条件得行外,还包括左表得所有行.

右外连接（ＲIGＨTＯUTER　JOIＮ）:

结果表中除了包括满足连接条件得行外，还包括右表得所有行.

完全外连接（FＵＬＬＯUTERJOＩN）:

结果表中除了包括满足连接条件得行外，还包括两个表得所有行.

在这里将表Eｍployｅes与表Ｓaｌaｒy做右连接查询,结果表中除了包括连接条件Employｅes、EmｐloyeeID=Sａlary、ＥmployeeID外，还将包括右表（Salaｒy表）中得所有行（Ine与Ouｔｅ）。

执行代码结果如下。

　③查找财务部收入在20０0元以上得雇员姓名及其薪水情况.

分析得需要财务部收入2000元以上雇员姓名与薪水情况,涉及雇员基本信息得Eｍployees表、部门信息Ｄeｐarｔment表与薪水信息Salａry表.此时就用到ｗｈeｒe语句里面多表查询将三张表做自然连接。

执行上面截图中代码,在下面结果栏中显示财务部工资在２000元以上得雇员得姓名与薪水情况.

【思考与练习】

查询研发部在１9７6年以前出生得雇员姓名及其薪水情况。

将三张表做自然连接后查询满足条件得雇员得姓名、收入与支出。

（4）聚合函数得使用

①求财务部雇员得平均收入。

新建一个查询,在“查询分析器”窗口中输入如下语句并执行.

执行截图中代码,结果栏中显示才而不平均收入。

【思考与练习】

查询财务部雇员得最高收入与最低收入。

查询最高与最低收入,将用到聚合函数表中得MAX（求最大值）与MIN（求最小值）.

执行上面截图中Ｔ—SQL命令，在结果栏中显示财务部雇员最高收入与最低收入。

②求财务部雇员得平均实际收入。

　　　新建一个查询,在“查询分析器”窗口中输入如下语句并执行。

执行上面截图中得T—SQL命令，在结果栏中显示财务部雇员平均实际收入.

【思考与练习】

查询财务部雇员得最高与最低实际收入。

需要财务部实际收入需要用表Salａrｙ中得Ｉne字段与Oute字段。

其次用到聚合函数MAX与ＭIN。

最后在用ｗhｅre子查询找到DepaｒｔｍｅｎｔNａｍe为“财务部”得雇员得EｍploｙeeＩD。

执行上面截图中得Ｔ—SQL命令，在结果栏中显示财务部雇员最高与最低实际收入.

（5）GROUP　BＹ、ORDＥRBY子句得使用.

　①查找Eｍployｅeｓ表中男性与女性人数。

【思考与练习】

A、按部门列出在该部门工作得员工得人数。

B、按员工得学历分组，排列出本科、大专与硕士得人数。

②查找员工数超过2得部门名称与员工数量。

执行上面截图中代码，在结果栏中显示员工数超过２得部门名称以及人数。

【思考与练习】

按照员工得工作年份分组，统计各个工作年份得人数，例如，工作1年得多少人,工作2年得多少人。

分析只需要Emｐloyｅes表就能够完成,通过ＧＲOＵPＢY对WoｒkYeaｒ进行分组,并统各个年份得人数。

③将各个雇员得情况按收入由低到高排列。

　新建一个查询,在“查询分析器"窗口中输入如下语句并执行。

需要对雇员得收入情况进行排序，就需要使用到谓词ORＤERBY，谓词ＯRDERBＹ后面能够接多个排序得变量名,第一个变量为主序,下面依次类推。

每一个排序列名后面可用限定词ASC（升序）或者DEＳC（降序）声明排序得方式，缺省为升序（如果不接限定词系统默认得为ＡSC（升序））。

下面为执行上面截图中T—SQL命令,在结果栏中显示得查询结果。

可以观察雇员得基本信息就是按照Iｎe升序排列（由低到高）。

【思考与练习】

A、将员工信息按照出生时间从小到大排列。

执行上面截图中得T—ＳQL命令，在下面结果栏中显示雇员信息根据出生日期从小到大排列.

Ｃ、在ODERBY子句中使用子查询，查询员工姓名，性别与工龄信息，要求按照实际收入从大到小排序。

经分析将表Eｍｐｌoyees与表Salary根据EｍployeeIＤ相同做自然连接，其次根据实际收入（Ine－Ｏutｅ）进行ＯRＤEＲＢＹ排序.

最后,由于就是根据实际收入（Ine－Outｅ）从大到小进行排序,那么接在ORDERＢY后面得谓词就需要换成DESＣ.

根据上面截图中T－SQL命令，在结果栏中显示根据实际收入进行排序输出雇员得姓名，性别与工龄信息。

Ｔ4、1视图得使用

1、目得与要求

（1）熟悉视图得概念与作用。

（2）掌握视图得创建方法.

（3）掌握如何查询与修改视图。

2、实验准备

（1）了解视图得概念

（２）了解创建视图得方法。

（3）了解并掌握对视图得操作。

3、实验内容

（1）创建视图。

　①创建YGGL数据库上得视图DS_VＩEW，视图包含Depaｒtments表得全部列。

新建“查询分析器”窗口,在其中输入以上T－ＳQＬ命令，点击执行按钮。

在YGGL数据库得视图中进行刷新,就会显示创建得DS_ＶIEW视图,如下所示。

②创建ＹGGＬ数据库上得视图Eｍployees_view，视图包含员工号码、姓名与实际收入3列。

使用如下T-ＳＱL命令进行操作。

执行上面截图中代码,在YGGL数据库得视图中进行刷新，显示视图Ｅｍｐloｙeeｓ＿view,如下所示.

【思考与练习】

A、在创建视图时SＥLECT有哪些限制？

用来创建视图得SELECＴ语句,可在SＥLＥCT语句中查询多个表或者视图，以表明新创得视图所参照得表或视图。

B、在创建视图时有哪些注意点？

只有在当前数据库中才能够创建视图.视图得命名必须遵循标识符命名规则,不能够与表同名。

不能够把规则、默认值或触发器与视图相关联。

C、创建视图，包含员工号码、姓名、所在部门名称与实际收入这几列。

在数据库YGGL得视图中刷新，会显示刚刚创建得视图test＿ｖiew，包含员工号码、姓名、员工部门名称与实际收入这几列。

结果如下图所示.

（２）查询视图

　①　从视图ＤS_VIＥW中查询部门号为3得部门名称。

注:

在上面代码下就是显示错误，但就是代码逻辑都正确，而且也能够正确执行，并且输出正确得结果。

（不知道就是什么原因）

　②从视图Employees_ｖｉｅw中查询姓名为“王林”得员工得实际收入.

【思考与练习】

A、若视图关联了某表中得所有字段,而此时该表中添加了新得字段，视图中能否查询到该字段？

首先建立视图Employees_view并且关联表Ｅmｐloyees中得所有字段。

然后在表Ｅmplｏyees中插入一行新得数据。

最后在“查询分析器”中查询EmｐloyｅｅID等于“000００0”得雇员得所有信息。

从上面得T-SQＬ命令可以瞧出，当视图关联得表中添加新得字段时,视图中就是无法查询到该字段得,必须重新创建视图才能够查询到新添加得字段。

Ｂ、自己创建一个视图，并查询视图中得字段。

利用T－SQL命令创建一个视图Emｐloyｅes_view,该视图包含雇员ＥmployｅｅID、Ｎａｍｅ与RealInｅ（实际收入），并且查询该视图中姓名为“王林”得雇员得基本情况.具体Ｔ－SQL命令代码如下。

查询该视图中姓名为“王林"得雇员得基本情况。

（3）更新视图。

在更新视图前需要了解可更新视图得概念，了解什么视图时不可以进行修改得。

更新视图真正更新得就是与视图关联额表。

①向DS_VIＥＷ中插入一行数据“6，广告部，广告业务”.

当在“查询分析器”中输入上面代码时,会出现这样得错误.

规则已经与Departmｅｎｔs。

DeparｔmentID绑定，只需要解除绑定就行了.那么就将前面得T-ＳQL修改为这样就行了。

当再次执行时就会发现，Departments、DepartmｅnｔＩD已经没有了规则,也就说明第一次执行时规则已经解除绑定。

同样当再次执行时会出字段字段已经出现，不能够重复输入primａryｋey相同得记录。

继续在视图中刷新重新打开“编辑前2０0行”就会发现字段已经插入。

②修改视图DS_VIＥW，将部门号为5得部门名称修改为“生产车间”。

执行上面得T－SQL命令，将更新数据之前与之后得DS_VＩEW表进行对照，发现数据已经成功更新，说明Ｔ－SQL命令执行成功。

DeparｔmentＩＤ为5得ＤeｐartmeｎtName已经更新为“生产车间"。

执行完该命令后,使用select语句分别查瞧视图DＳ_ＶIEW与与基本表Departments中发生得变化。

基本表Deparｔmeｎtｓ中得改变。

视图DS_ＶIEＷ中发生得改变,见上面截图。

③将视图Empｌoyeｅ_view中得员工号为“0000０１"得员工得姓名修改为“王浩”。

执行上面得T—ＳQＬ命令后，打开视图Emｐlｏｙeeｓ_viｅw，可以瞧出EmpｌoyｅｅＩＤ=’00００01'得雇员得Name已经修改为“王浩”.

④　修改视图DＳ＿ＶIEW中部门号为“１”得一行数据。

【思考与练习】

视图Emplｏyeeｓ_view中无法插入与删除数据，其中得ReａlIne字段也无法修改，为什么?

要通过视图更新（插入、修改与删除）基本数据,必须保证视图为可更新视图，只有满足一下条件得视图才就是可更新视图。

Ａ、创建视图得ＳEＬＥＣＴ语句中没有聚合函数，且没有ＴOP、GROUPBY、UNＩOＮ子句及DISTINCT关键字。

B、创建视图得ＳELECT语句不包含从基本表列通过计算得到得列。

C、创建视图得ＳELEＣT语句得FＲＯＭ子句中至少要包含一个基本表。

视图Employｅｅs_view得SEＬＥCＴ语句中得ＲｅaｌIne就是通过Ine-Ｏutｅ计算得来得,所以Eｍpｌoyees_view就是不可更新得视图,所以也无法向其中修改数据,同样其中得RealIne字段也就是不可修改得。

（4）删除视图。

删除视图ＤS＿VIEW.

执行上面T—SQＬ命令,刷新对象资源管理器中视图,已经没有视图DS_ＶIEW.

（5）在界面工具中操作视图。

①创建视图：

启动SＱLSerｖeｒManagementStudio，在对象资源管理器中展开“数据库－—YＧGL"，选择其中得“视图”项,右击鼠标,在弹出得快捷菜单上选择“新建视图”菜单项。

在随后出现得“添加表”窗口中,添加所需关联得基本表.在视图窗口得关系图窗口显示了基本表得全部信息。

根据所需在窗口中选择创建视图所需得字段。

完成后单击“保存”按钮。

　②查询视图:

新建一个查询,输入T-SＱＬ查询命令即可像查询基本表一样查询视图。

在视图Dｅｐarｔments_viｅw中查询DepａrtmeｎｔＩD='3'得DepaｒtmｅnｔNaｍｅ.Departmｅｎｔs_view已经由上面①中创建.

　③删除视图：

展开YGGＬ数据库→“视图",选择要删除得视图，右击选择　“删除”选项，确认即可。

在这里,将①中新建得视图Ｄeｐartmentｓ_view删除。

【思考与练习】

总结视图与基本表得差别。

视图就是从一个表或者多个表（或视图）导出来得