数据库习题答案

1、17实验4 数据库查询与视图实 验实验1 SQL Server 2008应用演示实验目的了解SQL Server 2008和客户端开发工具开发数据库信息管理系统的大致过程。实验准备（1）安装SQL Server 2008简体中文版。（2）安装Visual Basic 6.0中文版。（3）到本书前言中指定的网站上，下载本书配套的产品销售信息文件到演示机器的硬盘上。实验步骤（1）打开SQL Server 2008中文版，将产品销售信息管理系统的产品销售数据库CPXS恢复到SQL Server 2008中。（2）打开SQL Server Management Studio，选择产品销售数据库CPXS

2、，打开销售商（XSS）表，显示记录，临时输入一些记录。（3）打开SQL Server 2008查询分析器，查询（2）中临时输入的全部或部分记录。（4）打开Visual Basic 6.0中文版，打开产品销售信息工程。（5）大致对工程中的文件进行说明。（6）运行产品销售信息管理系统。实验2 SQL Server管理工具的使用实验目的（1）掌握SQL Server Management Studio“对象资源管理器”的使用方法。（2）掌握SQL Server Management Studio“查询分析器”的使用方法。（3）了解数据库及其对象。实验准备（1）了解SQL Server 2008支持的

3、身份验证模式。（2）了解SQL Server Management Studio的启动。（3）了解“对象资源管理器”中目录树的结构。（4）了解在查询分析器中执行SQL语句的方法。（5）基本了解数据库、表及其他数据库对象。实验步骤1对象资源管理器的使用（1）进入SQL Server Management Studio。单击“开始”选择“程序”选择Microsoft SQL Server 2008单击SQL Server Management Studio，打开“连接到服务器”窗口，如图2.1所示。图2.1 连接到服务器在打开的“连接到服务器”窗口中使用系统默认设置连接服务器，单击“连接”按钮，系

4、统显示SQL Server Management Studio窗口。在SQL Server Management Studio窗口中，左边是对象资源管理器，它以目录树的形式组织对象。右边是操作界面，如“查询分析器”窗口、“表设计器”窗口等都在此显示。（2）了解系统数据库和数据库的对象。在SQL Server 2008安装后，系统生成了4个数据库：master、model、msdb和tempdb。在“对象资源管理器”中单击“系统数据库”，下边显示4个系统数据库，如图2.2所示。选择系统数据库master，观察SQL Server 2008对象资源管理器中数据库对象的组织方式。其中，表、视图在“数

5、据库”结点下，存储过程、触发器、函数、类型、默认值、规则等在“可编程性”中，用户、角色、架构等在“安全性”中。（3）试试不同数据库对象的操作方法。展开系统数据库master展开“表”“系统表”选择dbo.spt_values，单击鼠标右键，系统显示对象操作快捷菜单，如图2.3所示。 图2.2 SQL Server Management Studio 图2.3 对象操作快捷菜单（4）认识表的结构。展开图2.3中的dbo.spt_values表，查看该表有哪些列。2查询分析器的使用在SQL Server Management Studio窗口中单击“新建查询”按钮（单击菜单栏中的“视图”菜单选择“

6、工具栏”中的“标准”菜单项，就可以打开该工具）。在“对象资源管理器”的右边就会出现“查询分析器”窗口，如图2.4所示，在该窗口中输入下列命令：USE masterSELECT * FROM dbo.spt_values GO单击“！执行”按钮（单击菜单栏中的“视图”菜单选择“工具栏”中的“SQL 编辑器”菜单项，可以打开该工具），命令执行结果如图2.4所示。图2.4 查询命令和执行结果如果在SQL Server Management Studio面板上的可用数据库下拉列表框中选择当前数据库为master，则USE master命令可以省略。使用USE命令选择当前数据库为model：USE mo

7、del【练习】通过企业管理器和查询分析器查询msdb数据库中sysalerts表的所有记录，然后比较操作方法。实验3 数据库和表创建实验3.1 创建数据库和表实验目的（1）了解SQL Server数据库的逻辑结构和物理结构。（2）了解表的结构特点。（3）了解SQL Server的基本数据类型。（4）了解空值概念。（5）学会在对象资源管理器中创建数据库和表。（6）学会使用T-SQL语句创建数据库和表。实验准备首先要明确，能够创建数据库的用户必须是系统管理员，或者是被授权使用CREATE DATABASE语句的用户。其次，创建数据库必须要确定数据库名、所有者（即创建数据库的用户）、数据库大小（最初

8、的大小、最大的大小、是否允许增长及增长方式）和存储数据库的文件。然后，确定数据库包含哪些表，以及所包含的各表的结构，还要了解SQL Server的常用数据类型，以创建数据库的表。此外，还要了解两种常用的创建数据库、表的方法，即在对象资源管理器中使用 T-SQL的CREATE DATABASE语句来创建。实验步骤创建用的产品销售数据库，数据库名为CPXS。数据库CPXS包含下列3个表。· 产品表：表名为CP，描述产品信息。· 销售商表：表名为XSS，描述销售商信息。· 产品销售表：表名为XSCP，描述产品的销售信息。各表的结构分别如表3.1表3.3所示。表3.1 产

9、品表（CP）表的结构列 名数 据 类 型 长 度是否允许为空值说 明CPBH字符型(char)6×产品编号，主键CPMC字符型(char)30×产品名称JG浮点型(float)系统默认价格KCL整型(int)系统默认库存量表3.2 销售商（XSS）表的结构列 名数 据 类 型 长 度是否允许为空值说 明XSBH字符型(char)6×销售商编号，主键XSMC字符型(char)30×销售商名称DQ字符型(char)10地区FZR字符型(char)8负责人DH字符型(char)12电话BZ变长字符型(varchar)200备注表3.3 产品销售（XSCP）表的

10、结构列 名数 据 类 型长 度是否允许为空值说 明CPBH字符型(char)6×产品编号，主键XSBH字符型(char)6×销售商编号，主键XSSJdate系统默认×销售时间SL整型(int)系统默认×数量JE浮点型(float)系统默认×金额（1）在对象资源管理器中创建数据库CPXS。要求：数据库CPXS初始大小为10MB，最大为50MB，数据库自动增长，增长方式是按10%比例增长；日志文件初始为2MB，最大可增长到5MB（默认为不限制），按1MB增长（默认是按5%比例增长）。数据库的逻辑文件名和物理文件名均采用默认值，分别为CPXS和C:P

11、rogram FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA CPXS.mdf；事务日志的逻辑文件名和物理文件名也均采用默认值，分别为CPXS_log和C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATACPXS.ldf。使用系统管理员用户以Windows身份验证方式登录SQL Server服务器，在“对象资源管理器”窗口中选择其中的“数据库”结点，右击鼠标，在弹出的快捷菜单中选择“新建数据库（N）”菜单项，打开“新建数据库”窗口。在“新建数据库”窗口的“常规”

12、选项卡中输入数据库名CPXS，所有者为默认值。在“数据库文件”下方的列表栏中，分别设置“数据文件”和“日志文件”的增长方式和增长比例。设置完成后单击“确定”按钮完成数据库的创建。（2）在对象资源管理器中删除CPXS数据库。在“对象资源管理器”中选择数据库CPXS，右击鼠标，在弹出的快捷菜单中选择“删除”菜单项。在打开的“删除对象”窗口中单击“确定”按钮，执行删除操作。（3）使用T-SQL语句创建数据库CPXS。按照步骤（1）的要求创建数据库CPXS。在“查询分析器”窗口中输入如下语句：CREATE DATABASE CPXSON(NAME='CPXS', FILENAME=&#

13、39;C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA CPXS.mdf', SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=10%)LOG ON( NAME='CPXS_Log',FILENAME='C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATACPXS.ldf', SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=1MB)GO单击快捷工具栏的“！执行”按

14、钮，执行上述语句，并在“对象资源管理器”窗口中查看执行结果。如果“数据库”列表中未列出CPXS数据库，则右击“数据库”，选择“刷新”选项。（4）使用对象资源管理器创建表CP、XSS和XSCP。【练习】以创建CP表为例，在“对象资源管理器”中展开数据库CPXS选择“表”，右击鼠标，在弹出的快捷菜单中选择“新建表”菜单项在“表设计”窗口中输入CP表的各字段信息单击工具栏中的“保存”按钮在弹出的“保存”对话框中输入表名CP，单击“确定”按钮即创建了表CP。按同样的操作过程创建表XSS和XSCP。（5）使用对象资源管理器删除表CP、XSS和XSCP。在对象资源管理器中展开“数据库”目录。选择CPXS中

15、的“表”结点，右击其中的dbo.CP表，在弹出的快捷菜单中选择“删除”菜单项，打开“删除对象”窗口。在“删除对象”窗口中单击“显示依赖关系”按钮，打开“CP依赖关系”窗口。在该窗口中确认表CP确实可以删除之后，单击“确定”按钮，返回“删除对象”窗口。在“删除对象”窗口，单击“确定”按钮，完成表CP的删除。按同样的操作过程删除表XSS和XSCP。（6）使用T-SQL语句创建表CP、XSS和XSCP。在“查询分析器”窗口中输入以下T-SQL语句：USE CPXSCREATE TABLE CP( CPBH char(6) NOT NULL PRIMARY KEY, CPMC char(30) NOT

16、 NULL,JG float NULL,KCL int NULL)GO单击快捷工具栏的“！执行”图标，执行上述语句，即可创建表CP。【练习】按同样的操作过程创建表XSS和XSCP，请读者自己写出相应的SQL语句，并在“对象资源管理器”中查看结果。实验3.2 表数据插入、修改和删除实验目的（1）学会使用界面方式对数据库表进行插入、修改和删除数据操作。（2）学会使用T-SQL语句对数据库表进行插入、修改和删除数据操作。（3）了解数据更新操作时要注意的数据完整性。（4）了解T-SQL语句对表数据操作的灵活控制功能。实验准备首先，了解对表数据的插入、删除、修改都属于表数据的更新操作。对表数据的操作可以

17、通过界面方式进行，也可以由T-SQL语句实现。其次，掌握T-SQL中用于对表数据进行插入、修改和删除的命令分别是INSERT、UPDATE和DELETE（或TRANCATE TABLE）。要特别注意在执行插入、删除、修改等数据更新操作时，必须保证数据完整性。此外，还要了解使用T-SQL语句在对表数据进行插入、修改及删除时，比在对象资源管理器中操作表数据更为灵活，功能更强大。实验步骤分别使用界面方式和命令方式，在数据库CPXS的3个表CP、XSS和XSCP中插入多行数据记录，然后修改和删除一些记录。使用T-SQL进行有限制的修改和删除。1使用界面方式向数据库CPXS表加入数据在“对象资源管理器”

18、中展开“数据库CPXS”结点，选择要进行操作的表CP，右击鼠标，在弹出的快捷菜单上选择“编辑前200行”菜单项，进入“表数据窗口”。在此窗口中，表中的记录按行显示，每个记录占一行。用户可通过“表数据窗口”向表中加入表3.4中的记录，输完一行记录后将光标移到下一行即保存了上一行记录。注意：· 插入的数据要符合列的类型。试着在tinyint型的列中插入字符型数据（如字母），查看发生的情况。· bit类型的列在用界面方式插入数据时只能插入True或False。True表示1，False表示0。· 不能插入两行有相同主键的数据。例如，如果编号000001的产品信息已经在C

19、P中存在，则不能向CP表再插入编号为000001的数据行。表3.4 CP表数据CPBHCPMCJGKCL100001彩色电视机3000.010100002洗衣机1200.020100003冰箱1800.012100004电热水器2000.030100005太阳能热水器2200.081000061匹空调1800.051000071.5匹空调2400.0201000082匹空调3800.06100009音响3500.03100010台式计算机6000.05100011MP3300.010100012复读机200.020【练习】使用对象资源管理器向表XSS中插入表3.5记录。表3.5 XSS表数据X

20、SBHXSMCDQFZRDHBZ000001广电公司南京张建02511111111NULL000002家电市场无锡李思成05102222222NULL000003电器商场上海王京平02133333333NULL000004小家电商场南京赵雅如02566666666NULL【练习】使用对象资源管理器向表XSCP中插入表3.6记录。表3.6 XSCP表数据CPBHXSBHXSSJSLJE1000010000012009-03-1013000.01000010000032009-05-2026000.01000020000012009-03-1211200.01000020000022009-02-

21、2222400.01000020000032009-05-2933600.01000110000042009-06-0151000.02使用界面方式修改数据库CPXS表数据（1）在对象资源管理器中删除CP表的第10行。在“对象资源管理器”中选择表CP，右击鼠标，在弹出的快捷菜单中选择“编辑前200行”菜单项，在打开的“表数据”窗口中选中要删除的行，右击鼠标，在弹出的快捷菜单中选择“删除”菜单项。（2）在对象资源管理器中将CP表中CPBH为100005的价格改为2000。在“对象资源管理器”中右击表CP，选择“编辑前200行”菜单项，在“表数据”窗口中将光标定位至CPBH为100005的记录的J

22、G字段，将值2200改为2000。将光标移出本行即保存了修改。3使用T-SQL命令修改数据库CPXS表数据（1）使用T-SQL命令分别向CPXS数据库表CP、XSS和XSCP中插入一行记录。在“查询”窗口中输入以下T-SQL语句：USE CPXSINSERT INTO cp VALUES('100013','燃气热水器',1500,10)GOINSERT INTO xss VALUES('000005','JL电器销售部','苏州','朱平平','051288888888',NULL

23、)GOINSERT INTO xscp VALUES('100013','000005','2009-7-20',2,3000)GO单击快捷工具栏的执行图标，执行上述语句。【练习】在对象资源管理器中分别打开CPXS数据库的表CP、XSS和XSCP，观察数据变化。（2）使用T-SQL命令修改表CP中的某个记录的字段值。在“查询”窗口中输入以下T-SQL语句：USE CPXSUPDATE cp SET JG = 1800 WHERE CPBH = '100013'GO单击快捷工具栏中的执行图标，执行上述语句。【练习】· 在对

24、象资源管理器中打开CPXS数据库的CP表，观察数据变化。· 在“查询分析器”中输入T-SQL语句，将编号为100005的价格改为2200。（3）使用T-SQL命令修改表CP中的所有记录的字段值：将所有商品价格增加100。在“查询”窗口中输入并执行以下T-SQL语句：UPDATE cp SET JG= JG +100【练习】输入以下T-SQL语句，观察数据变化：SELECT * FROM cp（4）使用TRANCATE TABLE语句删除表中所有行。在“查询”窗口中输入并执行以下T-SQL语句：TRANCATE TABLE cpGO执行上述语句，将删除CP表中的所有行。注意：实验时一般

25、不要轻易做这个操作，因为后面实验还要用到这些数据。如果要试验该命令的效果，可创建一个临时表，输入少量数据后进行。【练习】自定义数据库结构，创建数据库TEST，包含数据表t1和t2（字段自定义），然后向t1和t2表中输入若干行数据，最后使用TRANCATE TABLE语句删除表中所有行。实验4 数据库查询与视图实验4.1 数据库查询实验目的（1）掌握SELECT语句的基本语法和查询条件表示方法。（2）掌握连接查询的表示。（3）掌握嵌套查询的表示。（4）掌握数据汇总的方法。（5）掌握GROUP BY子句的作用和使用方法。（6）掌握ORDER BY子句的作用和使用方法。实验准备（1）了解SELECT

26、语句的基本语法格式。（2）了解SELECT语句的执行方法。（3）了解查询条件种类和表示方法。（4）了解连接查询的表示。（5）了解嵌套查询的表示方法。（6）了解数据汇总的方法。（7）了解SELECT语句的GROUP BY子句的作用和使用方法。（8）了解SELECT语句的ORDER BY子句的作用。实验步骤使用T-SQL的SELECT查询语句，在数据库CPXS的表CP、XSS和XSCP中进行各种查询，包括单表查询、连接查询、嵌套查询，并进行数据汇总以及使用GROUP BY子句、ORDER BY子句对查询结果进行分组和排序处理。1SELECT语句的基本使用以下的所有查询都在查询分析器中执行，在查询分

27、析器中将当前数据库设为CPXS。（1）查询每种产品的所有数据。在查询分析器的输入窗口输入如下语句并执行：SELECT * FROM cp【练习】用SELECT语句查询XSS和XSCP表的所有记录。（2）查询每种产品的价格和库存量。SELECT JG, KCL FROM cp【练习】用SELECT语句查询表XSS和XSCP的一列或若干列。（3）查询XSBH为000001的销售商的地区和电话。SELECT DQ, DH FROM xss WHERE XSBH='000001'【练习】用SELECT语句查询表CP和XSCP中满足指定条件的一列或若干列。（4）查询XSS表中的XSBH为

28、“000001”的销售商的地区和电话，使用AS子句将结果中各列的标题分别指定为地区、电话。SELECT DQ AS 地区, DH AS 电话 FROM xss WHERE XSBH='000001'【练习】查询CP表中的CPBH为100006的产品的价格和库存量，使用AS子句将结果中各列的标题分别指定为价格、库存量。（5）计算所有产品的总价值。SELECT CPBH , 总价值 = JG*KCL FROM cp【练习】计算所有产品的总库存量。（6）找出所有名称中含“电”的产品的编号和价格。SELECT CPBH, JG FROM cp WHERE CPMC LIKE '

29、%电%'【练习】找出所有名称中含“家电”的销售商的编号和地区。（7）找出所有价格在15002800之间的产品编号和产品名称。SELECT CPBH, CPMC FROM cp WHERE JG BETWEEN 1500 AND 2800【练习】找出所有库存量在10（含10）以上的产品编号和产品名称。2连接查询（1）查询每种产品的情况及其销售的情况。SELECT cp.* , xscp.* FROM cp , xscp WHERE cp.CPBH =xscp.CPBH 【练习】查询每个销售商的情况及其销售产品的情况。（2）查找价格在2000及以上的产品名称及其销售情况。SELECT CP

30、MC,XSMC,XSSJ,SL,JE FROM cp, xss , xscp WHERE cp.CPBH = xscp.CPBH AND xss.XSBH = xscp.XSBH AND JG>=2000【练习】查找库存量在10及以上的产品名称及其销售情况。（3）查询在2009年7月1日以前销售的产品名称及其销售情况。SELECT CPMC,XSMC,XSSJ,SL,JE FROM cp, xss , xscp WHERE cp.CPBH = xscp.CPBH AND xss.XSBH = xscp.XSBH AND XSSJ<='20090701'3嵌套查询（1

31、）查找与“广电公司”在同一地区的销售商的情况。SELECT * FROM xssWHERE DQ = ( SELECT DQ FROM xss WHERE XSMC = '广电公司') （2）查找销售了“彩色电视机”产品的销售商名称。SELECT XSMC FROM xss WHERE EXISTS ( SELECT * FROM xscp,cpWHERE XSBH = xss.XSBH AND xscp.CPBH = cp.CPBH AND CPMC='彩色电视机')（3）查找出售了所有000001销售商所出售的全部产品的销售商名称。SELECT XSMC F

32、ROM xss WHERE XSBH IN ( SELECT XSBH FROM xscp xscp1 WHERE NOT EXISTS ( SELECT * FROM xscp AS xscp2 WHERE xscp2.XSBH = '000001' AND NOT EXISTS ( SELECT * FROM xscp xscp3 WHERE xscp3.XSBH= xscp1.XSBH AND xscp3.CPBH=xscp2.CPBH)（4）查找未销售“台式计算机”产品的销售商情况。SELECT * FROM xss WHERE XSBH NOT IN ( SELECT

33、 XSBH FROM xscp WHERE CPBH = ( SELECT CPBH FROM cp WHERE CPMC = '台式电脑' )【练习】查询未销售任何产品的销售商名称。4数据汇总（1）求所有产品的平均价格。SELECT AVG(JG) AS '产品平均价格' FROM cp （2）查询产品的最高和最低价格。SELECT MAX(JG) AS '产品最高价格', MIN(JG) AS '产品最低价格' FROM cp（3）求地区为南京的总销售商数。SELECT COUNT( XSBH ) FROM xss WHERE

34、 DQ = '南京'【练习】统计销售商总数。（4）统计销售总值。SELECT SUM(JE) AS '销售总值' FROM xscp【练习】统计2004年1月1日以来的销售总值。5GROUP BY、ORDER BY子句的使用（1）查询各地区的销售商数。SELECT DQ,COUNT( XSBH ) FROM xss GROUP BY DQ（2）求被销售产品的名称和销售该种产品的销售商数。SELECT CPMC AS '产品名称', COUNT(XSBH) AS '销售商数' FROM xscp,cp WHERE xscp.CPBH

35、 = cp.CPBH GROUP BY CPMC（3）将各产品的情况按价格由低到高排列。SELECT * FROM cp ORDER BY JG（4）将各产品的情况按总值由低到高排列。SELECT * FROM cp ORDER BY JG*KCL（5）将各销售商按总销售额值由低到高排序。SELECT XSBH,SUM(JE) FROM xscp GROUP BY XSBH ORDER BY SUM(JE)实验4.2 视 图实验目的（1）熟悉视图的概念和作用。（2）掌握视图的创建方法。（3）掌握如何查询和修改视图。实验准备（1）了解视图的概念。（2）了解创建视图的方法。（3）了解并掌握对视图的操作。实验步骤1创建视图（1）创建CPXS数据库上的视图DS_VIEW，视图包含CP表的全部列。CREATE VIEW DS