数据库技术及应用实验sql.docx

数据库技术及应用实验sql.docx

《数据库技术及应用实验sql.docx》由会员分享，可在线阅读，更多相关《数据库技术及应用实验sql.docx（34页珍藏版）》请在冰豆网上搜索。

数据库技术及应用实验sql

实验步骤

利用企业管理器访问系统自带的数据库Northwind。

（1）启动SQLServer服务管理器。

（2）以系统管理员身份登录到企业管理器并访问Northwind数据库。

（3）在企业管理器的目录树中展开文件夹→展开Northwind数据库图标，则将列出该数据库的所有对象，如：

表、视图、存储过程、默认、规则等。

（4）选中Northwind下的“表”图标，将列出Northwind数据库所有的表（系统表和用户表），在此以用户Employees表为例，打开该表，查看其内容，得到的界面如图1所示。

图1.1通过企业管理器查看Northwind数据库的Employees表的内容

（5）在表的尾部试着插入一条记录，会发生什么情况？

（6）删除步骤（5）插入的记录。

1、利用查询分析器访问系统自带的数据库Northwind。

（1）启动SQLServer服务管理器；

（2）运行查询分析器；

（3）展开查询分析器对象浏览器目录树中的Northwind数据库图标，则将列出该数据库的所有对象，如：

表、视图、存储过程等，如图2所示。

图1-2查询分析器目录树

（4）以Northwind数据库的用户表Customers为例，选中该表的图标右击，弹出快捷菜单，执行“打开”菜单项，打开该表，查看其内容。

（5）在表的尾部插入一记录。

（6）在查询分析器的代码输入窗口，输入如下T-SQL语句：

USENorthwind

SELECT*

FROMCustomers

GO

看看执行结果是什么？

2、通过企业管理器和查询分析器查询pubs数据库中publishers、authors表的所有记录，并进行记录的插入和删除操作，然后比较一下操作方法。

实验2创建数据库和表

实验步骤

1、创建的产品销售数据库，数据库名为CPXS。

数据库CPXS包含下列3个表：

●产品表：

表名为CP，描述产品信息；

●销售商表：

表名为XSS，描述销售商信息；

●产品销售表：

表名为XSCP，描述产品的销售信息。

各表的结构分别如表2-1～表2-3所示。

表2-1产品表（CP）表结构

列名

数据类型

长度

是否允许为空值

说明

CPBH

字符型（char）

6

×

产品编号，主键

CPMC

字符型（char）

30

×

产品名称

JG

浮点型（float）

8

√

价格

KCL

整型（int）

4

√

库存量

表2-2销售商（XSS）表结构

列名

数据类型

长度

是否允许为空值

说明

XSBH

字符型（char）

6

×

销售商编号，主键

XSMC

字符型（char）

30

×

销售商名称

DQ

字符型（char）

10

√

地区

FZR

字符型（char）

8

√

负责人

DH

字符型（char）

12

√

电话

BZ

文本（text）

16

√

备注

表2-3产品销售（XSCP）表结构

列名

数据类型

长度

是否允许为空值

说明

CPBH

字符型（char）

6

×

产品编号，主键

XSBH

字符型（char）

6

×

销售商编号，主键

XSSJ

Datetime

8

×

销售时间

SL

整型（int）

4

×

数量

JE

浮点型（float）

8

×

金额

（1）在企业管理器中创建数据库CPXS

要求：

数据库CPXS初始大小为10MB，最大为50MB，数据库自动增长，增长方式是按10%比例增长；日志文件初始为2MB，最大可增长到5MB（默认为不限制），按1MB增长（默认是按5%比例增长）。

数据库的逻辑文件名和物理文件名均采用默认值，分别为CPXS_data和e:

\sql\data\MSSQL\Data\CPXS.mdf，其中e:

\sql\data\MSSQL为SQLServer的系统安装目录；事务日志的逻辑文件名和物理文件名也均采用默认值，分别为CPXS_LOG和e:

\sql\data\MSSQL\Data\CPXS_Log.ldf。

以系统管理员或被授权使用CREATEDATABASE语句的用户登录SQLServer服务器，启动企业管理器→在服务器上单击鼠标右键→新建数据库→输入数据库名CPXS→选择“数据文件”选项卡→设置增长方式和增长比例→选择“事务日志”选项卡→设置增长方式和增长比例。

（2）在企业管理器中删除CPXS数据库

在企业管理器中选择数据库CPXS→在CPXS上单击鼠标右键→删除。

（3）使用T-SQL语句创建数据库CPXS

按照步骤

（1）的要求创建数据库CPXS。

启动查询分析器→在“查询”窗口中输入以下T-SQL语句：

CREATEDATABASEcpxs

ON

（NAME='CPXS_Data',

FILENAME='e:

\sql\data\MSSQL\Data\CPXS.mdf',

SIZE=10MB,

MAXSIZE=50MB,

FILEGROWTH=10%

）

LOGON

（NAME='CPXS_Log',

FILENAME='e:

\sql\data\MSSQL\Data\CPXS_Log.ldf',

SIZE=2MB,

MAXSIZE=5MB,

FILEGROWTH=1MB

）

GO

单击快捷工具栏的执行图标，执行上述语句，并在企业管理器中查看执行结果。

（4）在企业管理器中分别创建表CP、表XSS和表XSCP

在企业管理器中选择数据库CPXS→在CPXS上单击鼠标右键→新建→表→输入CP表各字段信息→将CPBH设为主键→单击保存图标→输入表名CP，即创建了表CP。

按同样的操作过程创建表XSS和表XSCP。

（5）企业管理器中删除表CP、表XSS和表XSCP

在企业管理器中选择数据库CPXS的表CP→在CP上单击鼠标右键→删除，即删除了表CP。

按同样的操作过程删除表XSS和表XSCP。

（6）使用T-SQL语句创建表CP、表XSS和表XSCP

启动查询分析器→在“查询”窗口中输入以下T-SQL语句：

USEcpxs

CREATETABLECP

（CPBHchar（6）NOTNULLPRIMARYKEY,

CPMCchar（30）NOTNULL,

JGfloatNULL,

KCLintNULL

）

GO

单击快捷工具栏的执行图标，执行上述语句，即可创建表CP。

2、按同样的操作过程创建表XSS和表XSCP，请读者自己写出相应的SQL语句，并在企业管理器中查看结果。

实验3数据插入、修改和删除

。

实验步骤

分别使用企业管理器和T-SQL语句，在数据库CPXS的3个表CP、表XSS和表XSCP中插入多行数据记录，然后修改和删除一些记录。

使用T-SQL进行有限制的修改和删除。

1．在企业管理器中插入数据库CPXS表数据

（1）在企业管理器中向表CP中加入如表3-1所示的记录。

在企业管理器中选择表CP→在其上单击鼠标右键→选择“返回所有行”→逐字段输入各记录值，输入完后，关闭表窗口。

表3-1CP表数据

CPBH

CPMC

JG

KCL

100001

彩色电视机

3000.0

10

100002

洗衣机

1200.0

20

100003

冰箱

1800.0

12

100004

电热水器

2000.0

30

100005

太阳能热水器

2200.0

8

100006

1匹空调

1800.0

5

100007

1.5匹空调

2400.0

20

100008

2匹空调

3800.0

6

100009

音响

3500.0

3

100010

台式电脑

6000.0

5

100011

MP3

900.0

10

100012

复读机

200.0

20

（2）在企业管理器向表XSS中插入如表3-2所示的记录：

表3-2XSS表数据

XSBH

XSMC

DQ

FZR

DH

BZ

000001

广电公司

南京

张三

111111111

NULL

000002

家电市场

无锡

李四

222222222

NULL

000003

电器商场

上海

王五

333333333

NULL

000004

小家电商场

南京

赵六

666666666

NULL

（3）在企业管理器中向表XSCP中插入如表3-3所示的记录：

表3-3XSCP表数据

CPBH

XSBH

XSSJ

SL

JE

100001

000001

2004-03-10

1

3000.0

100001

000003

2004-05-20

2

6000.0

100002

000001

2004-03-12

1

1200.0

100002

000002

2004-02-22

2

2400.0

100002

000003

2004-05-29

3

3600.0

100010

000004

2004-06-01

5

4500.0

2．在企业管理器中修改数据库CPXS表数据

（1）在企业管理器中删除CP表的第10行和表XSS的第2行。

在企业管理器中选择表CP→在其上单击鼠标右键→选择“返回所有行”→选择要删除的行→单击鼠标右键→删除→关闭表窗口。

（2）在企业管理器中删除XSCP表的第6行，同时也要删除XSCP表的第4行，以保持数据完整性。

操作方法同

（1）。

（3）在企业管理器中将CP表中CPBH为100005的价格改为2000。

在企业管理器中选择表CP→在其上单击鼠标右键→选择“返回所有行”→将光标定位至CPBH为100005的记录的JG字段，将值2200改为2000。

3．使用T-SQL命令修改数据库CPXS表数据

（1）使用T-SQL命令分别向CPXS数据库的CP表、XSS表和XSCP表中插入一行记录。

启动查询分析器→在“查询”窗口中输入以下T-SQL语句：

USEcpxs

INSERTINTOcp

VALUES（'100013','燃气热水器',1500,10）

GO

INSERTINTOxss

VALUES（'000005','JL电器销售部','苏州','朱平平','88888888',NULL）

GO

INSERTINTOxscp

VALUES（'100013','000005','2004-7-20',2,3000）

GO

单击快捷工具栏的执行图标，执行上述语句。

在企业管理器中分别打开CPXS数据库的CP表、XSS表和XSCP表，观察数据变化。

（2）使用T-SQL命令修改表CP中的某个记录的字段值。

启动查询分析器→在“查询”窗口中输入以下T-SQL语句：

USEcpxs

UPDATEcp

SETjg=1800

WHEREcpbh='100013'

GO

单击快捷工具栏的执行图标，执行上述语句。

（3）在企业管理器中分别打开CPXS数据库的CP表，观察数据变化。

（4）在查询分析器中输入T-SQL语句，将编号为011112的职工收入改为2890。

（5）修改表XSS和表XSCP的记录值，要注意数据的完整性。

操作过程同

（2）。

（6）使用T-SQL命令修改表CP中的所有记录的字段值：

将所有商品价格增加100。

启动查询分析器→在“查询”窗口中输入以下T-SQL语句：

USEcpxs

UPDATEcp

SETjg=jg+100

GO

单击快捷工具栏的执行图标，执行上述语句。

（7）输入以下T-SQL语句，观察数据变化：

SELECT*FROMcp

（8）使用TRANCATETABLE语句删除表中所有行。

启动查询分析器→在“查询”窗口中输入以下T-SQL语句：

USEcpxs

TRANCATETABLEcp

GO

单击快捷工具栏的执行图标，执行上述语句，将删除CP表中的所有行。

注意：

实验时一般不要轻易做这个操作，因为后面实验还要用到这些数据。

如要试验该命令的效果，可建一个临时表，输入少量数据后进行。

3、自定义数据库结构，创建数据库TEST，包含数据表t1和t2（字段自定义），然后向t1表和t2表中输入若干行数据，最后使用TRANCATETABLE语句删除表中所有行。

实验4数据库查询

实验步骤

使用T-SQL的SELECT查询语句，在数据库CPXS的CP表、XSS表和XSCP表进行各种查询，包括单表查询、连接查询、嵌套查询，并进行数据汇总以及使用GROUPBY子句、ORDERBY子句对查询结果进行分组和排序处理。

1．SELECT语句的基本使用

以下的所有查询都在查询分析器中执行，在查询分析器中将当前数据库设为CPXS。

（1）查询每种产品的所有数据。

•在查询分析器的输入窗口输入如下的语句并执行：

SELECT*

FROMcp

•用SELECT语句查询XSS表和XSCP表的所有记录。

（2）查询每种产品的价格和库存量。

•在查询分析器的输入窗口输入如下的语句并执行：

SELECTJG,KCL

FROMcp

•用SELECT语句查询XSS表和XSCP表的一列或若干列。

（3）查询XSBH为000001的销售商的地区和电话。

•在查询分析器的输入窗口输入如下的语句并执行：

SELECTDQ,DH

FROMxss

WHEREXSBH=’000001’

•用SELECT语句查询CP表和XSCP表中满足指定条件的一列或若干列。

（4）查询XSS表中的XSBH为000001的销售商的地区和电话，使用AS子句将结果中各列的标题分别指定为地区、电话。

•在查询分析器的输入窗口输入如下的语句并执行：

SELECTDQAS地区,DHAS电话

FROMxss

WHEREXSBH=’000001’

•查询CP表中的CPBH为100006的产品的价格和库存量，使用AS子旬将结果中各列的标题分别指定为价格、库存量。

（5）计算所有产品的总价值。

•在查询分析器的输入窗口输入如下的语句并执行：

SELECTCPBH,总价值=JG*KCL

FROMcp

•计算所有产品的总库存量。

（6）找出所有名称中含“电”的产品的编号和价格。

•在查询分析器的输入窗口输入如下的语句并执行：

SELECTCPBH,JG

FROMcp

WHERECPMCLIKE’%电%’

•找出所有名称中含“家电”的销售商的编号和地区。

（7）找出所有价格在1500～2800之间的产品编号和产品名称。

•在查询分析器的输入窗口输入如下的语句并执行：

SELECTCPBH,CPMC

FROMcp

WHEREJGBETWEEN1500AND2800

•找出所有库存量在10（含10）以上的产品编号和产品名称。

2．连接查询

（1）查询每种产品的情况以及其销售的情况。

•在查询分析器的输入窗口输入如下的语句并执行：

SELECTCP.*,XSCP.*

FROMcp,xscp

WHEREcp.CPBH=xscp.CPBH

•查询每个销售商的情况以及其销售产品的情况。

（2）查找价格在2000及以上的产品名称及其销售情况。

•在查询分析器的输入窗口输入如下的语句并执行：

SELECTCPMC,XSMC,XSSJ,SL,JE

FROMcp,xscp,xss

WHEREcp.CPBH=xscp.CPBHANDxss.XSBH=xscp.XSBHANDJG>=2000

•查找库存量在10及以上的产品名称及其销售情况。

（3）查询在2004年7月1日以前销售的产品名称及其销售情况。

在查询分析器的输入窗口输入如下的语句并执行：

SELECTCPMC,XSMC,XSSJ,SL,JE

FROMcp,xscp,xss

WHEREcp.CPBH=xscp.CPBHANDxss.XSBH=xscp.XSBHANDXSSJ<=’20040701’

3．嵌套查询

（1）查找与“广电公司”在同一地区的销售商的情况。

在查询分析器的输入窗口输入如下的语句并执行：

SELECT*

FROMxss

WHEREDQ=

（SELECTDQ

FROMxss

WHEREXSMC='广电公司'）

（2）查找销售了“彩色电视机”产品的销售商名称。

在查询分析器的输入窗口输入如下的语句并执行：

SELECTXSMC

FROMxss

WHEREEXISTS

（SELECT*

FROMxscp,cp

WHEREXSBH=xss.XSBHANDxscp.CPBH=cp.CPBH

ANDCPMC='彩色电视机'）

（3）查找出售了所有000001销售商所出售的全部产品的销售商名称。

在查询分析器的输入窗口输入如下的语句并执行：

SELECTXSMC

FROMxss

WHEREXSBHIN

（SELECTXSBH

FROMxscpxscp1

WHERENOTEXISTS

（SELECT*

FROMcscpASxscp2

WHERExscp2.XSBH=’000001’ANDNOTEXISTS

（SELECT*

FROMxscpxscp3

WHERExscp2.XSBH=xscp1.XSBH

ANDxscp3.CPBH=xscp2.CPBH）））

（4）查找未销售“台式电脑”产品的销售商情况。

•在查询分析器的输入窗口输入如下的语句并执行：

SELECT*

FROMxss

WHEREXSBHNOTIN

（SELECTXSBH

FROMxscp

WHERECPBH=

（SELECTCPBH

FROMcp

WHERECPMC=’台式电脑’））

•查询未销售任何产品的销售商名称。

4．数据汇总

（1）求所有产品的平均价格。

在查询分析器的输入窗口输入如下的语句并执行：

SELECTAVG（JG）AS‘产品平均价格’

FROMcp

（2）查询产品的最高价格和最低价格。

在查询分析器的输入窗口输入如下的语句并执行：

SELECTMAX（JG）AS‘产品最高价格’,MIN（JG）AS‘产品最低价格’

FROMcp

（3）求地区为南京的总销售商数。

•在查询分析器的输入窗IZl输入如下的语句并执行：

SELECTCOUNT（XSBH）

FROMxss

WHEREDQ=’南京’

•统计销售商总数。

（4）统计销售总值。

•在查询分析器的输入窗口输入如下的语句并执行：

SELECTAVG（JE）AS‘销售总值’

FROMxscp

•统计2004年1月1日以来的销售总值。

5．GROUPBY、ORDERBY子句的使用

（1）查询各地区的销售商数。

在查询分析器的输入窗口输入如下的语句并执行：

SELECTDQ,COUNT（XSBH）

FROMxss

GROUPBYDQ

（2）求被销售产品的名称和销售该种产品的销售商数。

在查询分析器的输入窗口输入如下的语句并执行：

SELECTCPMCAS‘产品名称，COUNT（XSBH）AS‘销售商数’

FROMxscp,cp

GROUPBYCMPC

（3）将各产品的情况按价格由低到高排列。

在查询分析器的输入窗口输入如下的语句并执行：

SELECT*

FROMcp

ORDERBYJG

（4）将各产品的情况按总值由低到高排列。

在查询分析器的输入窗口输入如下的语句并执行：

SELECT*

FROMcp

ORDERBYJG*KCL

（5）将各销售商按总销售额值由低到高排序。

在查询分析器的输入窗口输入如下的语句并执行：

SELECTXSBH,SUM（JE）

FROMxscp

GROUPBYXSBH

ORDERBYSUM（JE）

实验5T-SQL编程

实验步骤

（1）对于产品销售数据库CPXS，首先自定义一数据类型，用于描述产品编号和销售商编号。

在查询分析器编辑窗口输入如下程序并执行：

USEcpxs

EXECsp_addtype‘id_type’,’int’,’notnull’

G0

注意：

不能漏掉单引号。

（2）通过SQL语句新定义3个表：

产品表：

表名为CPl，描述产品信息；

销售商表：

表名为XSSl，描述销售商信息；

产品销售表：

表名为XSCPl，描述产品的销售信息。

各表结构如表5—1～表5—3所示。

表5-1CPl表结构

列名

数据类型

长度

是否为空

说明

CPBH

CPMC

JG

KCL

自定义类型（id-typel

字符型（char）

浮点型（float）

整型（int）

同int

30

8

4

×

×

√

√

产品编号，系统自动编码，主键

产品名称

价格

库存量

表5-2XSSl表结构

列名

数据类型

长度

是否为空

说明

XSBH

XSMC

DQ

FZR

DH

BZ

自定义类型（id-type）

字符型（char）

字符型（char）

字符型（char）

字符型（char）

文本（text）

同int

30

10

8

12

16

×

×

√

√

√

√

销售商编号，系统自动编码，主键

销售商名称

地区

负责人

电话

备注

表5-3XSCPl表结构

列名

数据类型

长度

是否为空

说明

CPBH

XSBH

XSSJ

SL

JE

自定义类型（id-typel

自定义类型（id-typel

Datetime

整型（int）

浮点型（float）

同int

同int

8

4

8

×

×

×

×

×

产品编号，与CPl表中CPBH字段对应，

销售商编号，与XSSl表中XSBH字段对应，

CPBH与XSBH组合构成主键

销售时间

数量

金额

在查询分析器编辑窗口输入如下程序并执行：

USEcpxs

IFEXISTS（SELECTnameFROMsysobjects

WHEREtype=‘U’ANDname=’cpl’）

DROPTABLEcp1

/*首先在系统表中查看是否有cp1表存在，若存在，删除该表*