《数据库应用项目实训》指导书

数据库应用项目实训指导书第1页数据库应用项目实训指导书姓名：班级：数据库应用项目实训指导书第2页课题一、数据库操作练习1.1创建数据库请企业管理器创建销售数据库（SaleDB），要求如下：参数参数值数据库名称SaleDB数据库逻辑文件名Sale_dat操作系统数据文件名d:\Sales\Sale_dat.mdf数据文件的初始大小5MB数据文件最大大小50MB数据文件增长量原来的20%日志逻辑文件名Sale_log操作系统日志文件名d:\Sales\Salelog.ldf日志文件初始大小5MB日志文件的最大大小25MB日志文件增长增量1MB操作提示：打开创建数据库对话框后，设定如下参数：练习1.2：请试验用SQL创建数据库SaleDB1参数参数值数据库名称SaleDB1数据库应用项目实训指导书第3页数据库逻辑文件名Sale1_dat操作系统数据文件名d:\Sales\Sale1_dat.mdf数据文件的初始大小5MB日志逻辑文件名Sale1_log操作系统日志文件名d:\Sales\Sale1log.ldf日志文件初始大小5MB日志文件的最大大小25MB--创建SaleDB1数据库的SQL脚本CREATEDATABASE[SaleDB1]ONPRIMARY(NAME=N'SaleDB1_Data',FILENAME=N'd:\Sales\Sale1_dat.mdf',SIZE=5MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)LOGON(NAME=N'SaleDB_Log',FILENAME=N'd:\Sales\Sale1_Log.LDF',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=10%)数据库应用项目实训指导书第4页课题二、数据库表和数据完整性销售管理数据的关系图如下：练习2.1用企业管理器中创建[用户]表要求：l注意设置[用户ID]字段为主键，并设置为标识（自动产生）。l为[用户编号]字段，建立唯一约束l为[用户名称]字段，建立唯一约束操作提示：1、打开新建表界面数据库应用项目实训指导书第5页2、设计表的字段3、设计[用户编号]字段的唯一约束数据库应用项目实训指导书第6页4、设计[用户编号]字段的唯一约束数据库应用项目实训指导书第7页完成建表后，生成[用户]表的创建SQL语句，将SQL语句写在下面空白处。生成建表SQL的操作提示：[用户]表的建表SQL：数据库应用项目实训指导书第8页练习2.2用企业管理器中创建[部门]表要求：l注意设置[部门ID]字段为主键，并设置为标识（自动产生）。l为[部门编号]字段，建立唯一约束l为[部门名称]字段，建立唯一约束完成建表后，生成[部门]表的创建SQL语句，将SQL语句写在下面空白处。[部门]表的建表SQL：练习2.3在查询窗口用SQL语句创建[产品分类]表创建的SQL语句如下：CREATETABLE[产品分类]([类别ID][int]IDENTITY(1,1)NOTNULLprimarykey,[类别名称][varchar](50)NOTNULLUNIQUE,[上级分类ID][int]NULL,[层次][int]NULL)练习2.4在查询窗口用SQL语句创建[产品]表数据库应用项目实训指导书第9页表的约束有：l产品ID为关键字，并设置为标识l产品库存量必须=0，l产品价格必须=0l外键约束约束：产品分类ID参展[分类]表的分类ID字段创建[产品表]的SQL语句如下：CREATETABLE[产品]([产品ID][int]IDENTITY(1,1)NOTNULL,[产品分类ID][int]NOTNULL,[产品名称][varchar](50)NOTNULL,[产品条型码][char](48)NULL,[产品描述][text]NULL,[照片][image]NULL,[产品库存量][numeric](18,0)NULL,[有效标志][char](2)NULLCONSTRAINT[DF_产品_有效标志]DEFAULT('是'),[产品价格][money]NULL,CONSTRAINT[PK_产品]PRIMARYKEYCLUSTERED)GO修改[产品]表，增加约束：产品价格必须=0ALTERTABLE[dbo].[产品]WITHCHECKADDCONSTRAINT[CK_产品_价格]CHECK(([产品价格]=0))GOALTERTABLE[dbo].[产品]CHECKCONSTRAINT[CK_产品_价格]修改[产品]表，增加约束：产品库存量必须=0ALTERTABLE[dbo].[产品]WITHCHECKADDCONSTRAINT[CK_产品]CHECK(([产品库存量]=0))GOALTERTABLE[dbo].[产品]CHECKCONSTRAINT[CK_产品]GO数据库应用项目实训指导书第10页修改[产品]表，增加外键约束约束：产品分类ID参展[分类]表的分类ID字段ALTERTABLE[dbo].[产品]WITHNOCHECKADDCONSTRAINT[FK_产品_产品分类]FOREIGNKEY([产品分类ID])REFERENCES[dbo].[产品分类]([类别ID])GOALTERTABLE[dbo].[产品]CHECKCONSTRAINT[FK_产品_产品分类]GO练习2.5在查询窗口用SQL语句创建[客户]表表的约束有：l客户ID为关键字，并设置为标识l客户名称必须唯一l性别缺省为“男”创建[产品表]的SQL语句如下：CREATETABLE[客户]([客户ID][int]IDENTITY(1,1)NOTNULL,[客户名称][varchar](50)NULL,[联系电话][varchar](50)NULL,[邮件][varchar](50)NULL,[地址][varchar](200)NULL,[性别][char](2)NULLCONSTRAINT[DF_客户_性别]DEFAULT('男'),[年龄][int]NULL,CONSTRAINT[PK_客户]PRIMARYKEYCLUSTEREDCONSTRAINT[IX_客户名称]UNIQUENONCLUSTERED)GO练习2.6在查询窗口用SQL语句创建[订单主表]数据库应用项目实训指导书第11页表的约束有：l订单ID为关键字，并设置为标识l下单时间缺省为当时的机器时间，通过getdate()函数取得l订单状态有：下单、送货、完成。缺省值为“下单”l客户ID需要建立外键，参考[客户]表的客户ID字段创建[订单主表]的SQL语句如下：CREATETABLE[订单主表]([订单ID][int]IDENTITY(1,1)NOTNULL,[客户ID][int]NOTNULL,[下单时间][datetime]NULLCONSTRAINT[DF_订单主表_下单时间]DEFAULT(getdate()),[备注][text]NULL,[送货物地址][varchar](200)NULL,[状态][char](4)NULLCONSTRAINT[DF_订单主表_状态]DEFAULT('下单'),[订单完成时间][datetime]NULL,CONSTRAINT[PK_订单主表]PRIMARYKEYCLUSTERED)ALTERTABLE[dbo].[订单主表]WITHNOCHECKADDCONSTRAINT[FK_订单主表_客户]FOREIGNKEY([客户ID])REFERENCES[dbo].[客户]([客户ID])GOALTERTABLE[dbo].[订单主表]CHECKCONSTRAINT[FK_订单主表_客户]练习2.7在查询窗口用SQL语句创建[订单明细表]表的约束有：数据库应用项目实训指导书第12页l订单ID和产品ID为组合关键字l产品ID需要建立外键，参考[产品]表的产品ID字段创建[订单明细]的SQL语句如下：CREATETABLE[订单明细]([订单ID][int]NOTNULL,[产品ID][int]NOTNULL,[数量][numeric](18,0)NULL,[成交价格][money]NULL,CONSTRAINT[PK_订单明细]PRIMARYKEYCLUSTERED)GOALTERTABLE[dbo].[订单明细]WITHNOCHECKADDCONSTRAINT[FK_订单明细_产品]FOREIGNKEY([产品ID])REFERENCES[dbo].[产品]([产品ID])GOALTERTABLE[dbo].[订单明细]CHECKCONSTRAINT[FK_订单明细_产品]GOALTERTABLE[dbo].[订单明细]WITHCHECKADDCONSTRAINT[FK_订单明细_订单主表]FOREIGNKEY([订单ID])REFERENCES[dbo].[订单主表]([订单ID])GOALTERTABLE[dbo].[订单明细]CHECKCONSTRAINT[FK_订单明细_订单主表]数据库应用项目实训指导书第13页课题三、数据操作练习3.1在企业管理器中，打开[部门]表插入如下数据练习3.2在查询窗口用SQL语句在[用户]表中插入如下数据插入数据的SQL语句如下：INSERTINTO[用户]([用户编号],[用户名称],[部门ID],[密码],[邮件地址],[电话])VALUES('01001’,‘李明’,1,‘1234’,‘[e-mail]’‘9792223’)练习3.2在查询窗口用SQL语句在[产品分类]表中插入如下数据数据库应用项目实训指导书第14页练习3.3在查询窗口用SQL语句在[产品]表中插入如下数据产品名称为：联想旭日150产品分类为：笔记本产品描述为：处理器IntelCeleronMprocessor1.40GHz硬盘30G光驱8XDVD显示屏15.1TFT,1024×768,32位真彩色网卡10/100以太网卡产品库存量：150产品价格：5990练习3.4在查询窗口用SQL将“联想旭日150”的产品价格下调10%。UPDATE[产品]SET[产品价格]=[产品价格]*0.9WHERE[产品名称]=‘联想旭日150’练习3.5在查询窗口用SQL将用户“李明”的所在部门调为[软件维护组]。SQL语句应为：练习3.6在查询窗口用SQL将插入以下要求的数据，将SQL写在下面空白处。1、插入以你名字的客户到[客户]表2、以你作为客户，创建订单购买1台“联想旭日150”，价格为5000元SQL语句应为：练习3.7删除数据，删除[产品分类]表中的“AMDCPU”记录DELETEFROM[产品分类]WHERE[类别名称]=‘AMDCPU’练习3.8输入其它测试数据数据库应用项目实训指导书第15页课题四、数据查询操作练习4.1查询产品价格大于5000的产品记录练习4.2查询产品信息，要求列出产品信息和产品分类名称练习4.3查询最近一个月的订单练习4.4查询销售量最大的前10个产品练习4.5查询分类为笔记本的所有产品练习4.6查询用户“李明”所在的部门名称数据库应用项目实训指导书第16页练习4.7查询统计“笔记本”类产品的个数练习4.8查询统计“笔记本”类产品的库存金额练习4.9查询统计每一类产品的库存数量练习4.10查询统计每个产品的销售量练习4.11查询统计每类产品的销售量数据库应用项目实训指导书第17页课题五、数据视图练习5.1程序中经常需要查询产品信息，而产品信息中没有包含产品分类名称，请创建一个视图包括产品信息和产品分类名称createviewview_产品信息asselect[产品ID],[产品分类ID],[产品名称],[产品条型码],[产品描述],[产品库存量],[有效标志],[产品价格],[产品分类].[类别名称]FROM[SaleDB].[dbo].[产品]INNERJOIN[产品分类]ON[产品].[产品分类ID]=[产品分类].[类别ID]练习5.2程序中经常需要查询定单、订单明细、订单对应