《SQLServer数据库应用与维护》-实验4-SQL对象管理 - 答案

温州职业技术学院实验报告实验课程SQLServer数据库应用与维护实验项目实验四：SQLServer对象管理实验地点明德楼指导教师翁正秋班级学生姓名学号教师评分日期

实验四SQL对象管理一、实验目的1、 掌握用SQL对数据库对象的管理；2、 进一步了解SQL数据库的创建，数据表的创建，视图、索引以及约束的管理；二、实验内容第一题【创建和修改数据库】使用SQL语句创建产品销售数据库company，数据库中包含一个数据文件，逻辑文件名为company_data，磁盘文件名为D：\company_data.mdf，文件初始容量为5MB，最大容量为80MB，文件容量递增值为1MB；事务日志文件的逻辑文件名为company_log，磁盘文件名为D：\company_log.ldf，文件初始容量为1MB，最大容量为5MB，文件容量递增值为10%。CreatedatabasecompanyOnprimary(name=company_data,filename='D:\company_data.mdf',size=5,maxsize=80,filegrowth=1)Logon(name=company_log,filename='D:\company_log.ldf',size=1,maxsize=5,filegrowth=10%)2、对company数据库进行修改：添加两个数据文件，其中一个逻辑文件名为company_data1，磁盘文件名为company_data1.ndf，文件初始容量为 ，最大容量为50MB，文件容量递增值为1MB，另一个逻辑文件名为company_data2，磁盘文件名为company_data2.ndf，文件初始容量为5MB，最大容量为无限，文件容量递增值为1MB。USEmastergoALTERDATABASEcompanyADDFILE( name=company_data1, filename='D:\company_data1.ndf', size=5, maxsize=50, filegrowth=1),( name=company_data2, filename='D:\company_data2.ndf', size=5, maxsize=unlimited, filegrowth=1)将数据库company数据主文件company_data容量扩充到200MBUSEMASTERGOALTERDATABASEcompanyMODIFYFILE( name='company_data', SIZE=200)4、将次数据文件company_data2删除，从而缩小数据库容量。USEmasterALTERDATABASEcompanyREMOVEFILEcompany_data2go第二题【创建和修改数据表】某公司的产品销售数据库中存在员工人事表、客户表、销售表、销售明细表、产品表。各表的结构如下：员工表字段名字段类型是否为空是否主键员工编号char(5)非空主键员工姓名varchar(10)非空性别char(2)非空所属部门varchar(10)非空职称varchar(10)非空雇佣日datetime非空生日datetime允许为空薪水int非空电话varchar(20)允许为空住址varchar(50)允许为空客户表字段名字段类型是否为空是否主键客户号char(5)非空主键客户名称varchar(20)非空客户住址varchar(40)非空客户电话varchar(20)非空邮政编码char(6)允许为空销售主表字段名字段类型是否为空是否主键订单编号int非空主键客户号char(5)非空业务员编号char(5)非空订单金额numeric(9,2)非空订货日期datetime非空销货明细表字段名字段类型是否为空是否主键订单编号int非空产品编号char(5)非空销售数量int非空单价numeric(7,2)非空订单日期datetime允许为空产品名称表字段名字段类型是否为空是否主键产品编号char(5)非空主键产品名称varchar(20)非空成本int非空T-SQL语句在数据库company中创建以上五张表。第一张表：usecompanygocreatetable员工( 员工编号char(5)NOTNULLprimarykey, 员工姓名varchar(10)NOTNULL, 性别char(2)NOTNULL, 所属部门varchar(10)NOTNULL, 职称varchar(10)NOTNULL, 雇佣日datetimeNOTNULL, 生日datetimeNULL, 薪水intNOTNULL, 电话varchar(20)NULL, 住址varchar(50)NULL)第二张表：usecompanygocreatetable客户( 客户char(5)NOTNULLPRIMARYKEY, 客户名称varchar(20)NOTNULL, 客户住址varchar(40)NOTNULL, 客户电话varchar(20)NOTNULL, 邮政编码char(6)NOTNULL,)第三张：usecompanygocreatetable销售主表( 订单编号intNOTNULLprimarykey, 客户号char(5)NOTNULL, 业务员编号char(5)NOTNULL, 订单金额numeric(9,2)NOTNULL, 订货日期datetimeNOTNULL)第四张表usecompanygocreatetable销售明细表( 订单编号intNOTNULL, 产品编号char(5)NOTNULL, 销售数量intNOTNULL, 单价numeric(7,2)NOTNULL, 订单日期datetimeNULL)第五张表usecompanygocreatetable产品名称表( 产品编号char(5)NOTNULLprimarykey, 产品名称varchar(20)NOTNULL, 成本intNOTNULL)2、售主表中添加字段“发票号码”，char(10)。usecompanygoaltertable销售主表 add发票号码char(10)工表中删除字段“职称”usecompanygoaltertable员工 DROPCOLUMN职称第三题【定义约束】1、在销售主表中设置“业务员编号”字段为外键，参照字段为员工表中的字段“员工编号”，外建约束名为FK_sale_id，“客户号”字段为外键，参照字段为客户表中的字段“客户号”，约束名为FK_cust_id。usecompanygoaltertable销售主表 addconstraintFK_sale_idforeignkey(业务员编号) references员工(员工编号);usecompanygoaltertable销售主表 addconstraintFK_cust_idforeignkey(客户号) references客户(客户);货明细表中设置“订单编号”字段为外键，参照字段为销售主表中的字段“订单编号”，外键约束名为FK_order_no，“产品编号”字段为外键，参照字段为产品名称表中的“产品编号”字段，外键约束名为FK_prod_id。usecompanygoaltertable销售明细表 addconstraintFK_order_idforeignkey(订单编号) references销售主表(订单编号);usecompanygoaltertable销售明细表 addconstraintFK_prod_idforeignkey(产品编号) references产品名称表(产品编号);添加检查约束：，将员工表中的“薪水”字段的值限定在1000至10000间，约束名为CK_salary，将员工人事表中的“员工编号”字段设定为以“E”字母开头,后面跟4位数的编号，约束名为CK_emp_no，将员工表中的“性别”字段设定为取值只能是“男”和“女”。约束名为CK_sex。ALTERTABLE员工addconstraintck_salarycheck(薪水between1000and10000)ALTERTABLE员工addconstraintck_employeeIDcheck( len(员工编号)=5and left(员工编号,1)='E'andsubstring(员工编号,2,1)like'[0-9]'and substring(员工编号,3,1)like'[0-9]'and substring(员工编号,4,1)like'[0-9]'and substring(员工编号,5,1)like'[0-9]')ALTERTABLE员工addconstraintck_employeeSEXcheck( 性别in('男','女')--性别='男'or性别='女')为销售主表中的字段“发票号码”设置为唯一约束，约束名为UN_inno。ALTERTABLE销售主表ADDCONSTRAINTuc_saleIDUNIQUE(订单编号)添加默认约束：员工表的“住址”默认为：地址不详ALTERTABLE员工addconstraintAddress_defaultDEFAULT'地址不详'for住址第三题【视图管理】1、创建视图（1）启动SSMS，在产品销售数据库中创建成本小于2000的产品视图VIEW_CP_PRICE2000，要求加密并保证对该视图的更新都要符合成本小于2000这个条件，写出T-SQL语句：CREATEVIEWVIEW_CP_PRICE2000WITHENCRYPTIONASSELECT*FROM产品名称表WHERE成本<2000（2）用T-SQL语句创建各客户购买产品的情况VIEW_GMQK视图，包括客户编号、客户名称、产品编号、产品名称、价格，购买日期、购买数量。CREATEVIEWVIEW_GMQKASSelect客户,客户名称,销售明细表.产品编号,产品名称,成本,订单日期,销售数量from客户,产品名称表,销售明细表where销售明细表.产品编号=产品名称表.产品编号2、查询视图（1）基于VIEW_CP_PRICE2000视图，查询价格在2000以下产品的产品编号、名称和价格。写出T-SQL语句：Select*fromVIEW_CP_PRICE2000（2）基于VIEW_GMQK视图，查询各客户在2017年3月18日购买产品的情况。写出T-SQL语句：Select*fromVIEW_GMQKwhere订单日期='2017-3-18'3、更新视图利用T-SQL语句对于视图VIEW_CP_PRICE2000进行以下数据更新。（1）插入一条产品记录（'100042','数码相机',1500）。（2）将产品编号为’100042’的成本改为1800。（3）删除产品编号为’100042’的产品。INSERTINTOVIEW_CP_PRICE2000values('10042','数码相机',1500)UPDATEVIEW_CP_PRICE2000SET产品编号='1800'where产品编号='10042'DELETEFROMVIEW_CP_PRICE2000WHE