实验一 建立数据库和基本表.doc

实验一 建立数据库和基本表实验目的1、理解数据库模式的概念，通过使用SQL SERVER企业管理器建立数据库和基本表。模式为人事表、客户表、销售表、销售明细表、产品表。熟悉SQL SERVER企业管理器的使用，并将得到的表生成脚本，然后保存。帮助学生理解上述基本表之间的关系，从而正确、合理地建立表结构，并输入相应记录信息。2、掌握视图的定义与工作原理实验内容与步骤实验内容与步骤实验内容与步骤（一）建立数据库studentdb：create database studentdbon( name=studentdb,filename=d:mwystudentdb.mdf,size=3,maxsize=10,filegrowth= 1 )log on( name=studentlog,filename=d:mwystudentlog.ldf,size=3,maxsize=3,filegrowth=1)go 表一/*员工人事表employee */：use studentdb1gocreate table employee(emp_nochar(5)Not null,emp_namechar(10)Not null,sexchar(1)Not null,deptvarchar(20)Not null,titlechar(6)Not null,date_hireddatetimeNot null,birthdaydatetimeNull,salaryintNot null,addrvarchar(50)null,Mod_datedatetimeDefault(getdate()表二/*客户表customer */：use studentdb1gocreate table customer(cust_idchar(5)Not null,cust_namevarchar(20)Not null,addrvarchar(40)Not null,tel_novarchar(15)Not null,zipchar(6)Null,)表三/*销售主表sales */：use studentdb1gocreate table sales(order_nointNot null,cust_idchar(5)Not null,sale_idchar(5)Not null,tot_amtnumeric(9,2)Not null,order_datedatetimeNot null,ship_datedatetimeNot null,invoice_nochar(10)Not null,)表四/*销货明细表sale_item */：use studentdb1gocreate table sale_item(order_nointNot null ,prod_idchar(5)Not null,qtyintNot null,unit_pricenumeric(7,2)Not null,order_datedatetimenull,)表五/*产品名称表product */：use studentdb1gocreate table product(prod_idchar(5)Not null,prod_namevarchar(30)Not null,)2、通过T-SQL语句修改表约束。分别为每张表建立主键约束。Employee:alter table employee add constraint employee_emp_no primary key(emp_no);Customer:alter table customer add constraint customer_cust_id primary key(cust_id);Sales:alter table sales add constraint sales_order_no primary key(order_no);Sale_item:alter table sale_item add constraint sale_item_order_no_prod_id primary key(order_no,prod_id);Product;alter table product add constraint product_prod_id primary key(prod_id);为相关表建立外键约束。create table ordersorder_id char(8),order_no int,prod_id char(5) ,constraint pk_order_id primary key (order_id) ,foreign key (prod_id) References product(prod_id ),foreign key (order_no ) References sales(order_no )在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。alter table employeeadd constrait emp_no check (emp_no=E_);alter table employeeadd constrait pk_sex check (sex in (M,F);alter table sales为销售主表sales中的发票编号字段建立UNIQUE约束。add constrait fk_sales UNIQUE(invoice_no);3、使用命令给上述基本表添加记录。（1）insert employee values(E0001,喻自强,M,会计,科长, 19870415,19610206,48000,南京市青海路,)insert employee values(E0002,张小梅,F,业务,职员, 19961101,19710328,32000,上海市北京路,)insert employee values(E0003,张小娟,F,业务,职员, 19960306,19720328,32000,上海市南京路,)insert employee values(E0004,张露, F,业务,部长, 19960105,19700328,52000,南昌市八一大道,)insert employee values(E0005,张小东,M,业务,职员, 19960903,19720328,32000,南昌市阳明路,)insert employee values(E0006,陈辉, M,业务,职员, 19961101,19700328,32000,南昌市青山路,)insert employee values(E0007,韩梅, F,业务,职员, 19961211,19701128,32000,上海市浦东,)insert employee values(E0008,刘风, F,业务,职员, 19960521,19710228,32000,南昌市财大,)insert employee values(E0009,吴浮萍,M,业务,经理, 19960912,19700628,52000,南昌市高新技术开发区,)insert employee values(E0010,高代鹏,M,业务,主管, 19960102,19711128,52000,南昌市高新技术开发区,)insert employee values(E0011,陈诗杰,M,会计,科长, 19980106,19601206,48000,南昌市财大,)insert employee values(E0012,张良, M,业务,职员, 19960216,19720228,32000,上海市,)insert employee values(E0013,张梅, F,业务,职员, 19960515,19710228,32000,上海市,)insert employee values(E0014,张小, F,业务,职员, 19961013,19700528,32000,南昌市,)insert employee values(E0015,张小梅,F,业务,职员, 19961106,19701118,32000,深圳市,)insert employee values(E0016,黄小梅,F,业务,职员, 19961007,19701209,32000,株洲市,)insert employee values(E0017,李小梅,F,业务,职员, 19960815,19710804,32000,香港,)insert employee values(E0018,张小花,F,业务,职员, 19960613,19700623,32000,南昌市孺子路,)insert employee values(E0019,张飞, M,业务,职员, 19960305,19710916,32000,北京市,)insert employee values(E0020,毛小鹏,M,业务,董事, 19961007,19710218,82000,南昌市高新技术开发区,)go （2）insert dbo.customer values(C0001,客户甲, 天津市,400)insert dbo.customer values(C0002,客户乙, 天津市,400)insert dbo.customer values(C0003,客户丙, 北京市,800)insert dbo.customer values(C0004,客户丁, 上海市,100)insert dbo.customer values(C0005,客户戊, 北京市,803)insert dbo.customer values(C0006,客户己, 上海市,100)insert dbo.customer values(C0007,客户庚, 上海市,102)insert dbo.customer values(C0008,世界技术开发公司, 上海市,100)insert dbo.customer values(C0009,万事达股份有限公司,天津市,400)insert dbo.customer values(C0010,红度股份有限公司, 北京市,800)insert dbo.customer values(C0011,兴隆股份有限公司, 上海市,104)insert dbo.customer values(C0012,联合股份有限公司, 北京市,803)insert dbo.customer values(C0013,上海生物研究室, 上海市,103)insert dbo.customer values(C0014,南昌市电脑研制中心,上海市,102)insert dbo.customer values(C0015,统一股份有限公司, 上海市,100)insert dbo.customer values(C0016,大地商城, 天津市,401)insert dbo.customer values(C0017,五一商厦, 北京市,800)insert dbo.customer values(C0018,南昌第一会计事务所, 上海市,104)insert dbo.customer values(C0019,公交公司, 南昌市,803)insert dbo.customer values(C0020,南昌大学, 南昌市,103)insert dbo.customer values(C0021,华东交大, 南昌市,102)go（3）insert sales values(10001,C0001,E0006,700.00,19970510,19971117,I000000001) insert sales values(10002,C0002,E0012,13960.00,19970615,19971017,I000000002)insert sales values(10003,C0003,E0014,33000.00,19971210,19971217,I000000003)insert sales values(10004,C0004,E0005,60000.00,19971220,19971224,I000000004)insert sales values(10005,C0004,E0008,20000.00,19970215,19970517,I000000005)insert sales values(10006,C0001,E0013,22700.00,19961015,19971117,I000000006) insert sales values(10007,C0007,E0010,13960.00,19970422,19971017,I000000007)insert sales values(10008,C0008,E0002,33000.00,19970120,19971217,I000000008)insert sales values(10009,C0002,E0006,100.00,19961210,19971224,I000000009)insert sales values(10010,C0010,E0013,20000.00,19961015,19971017,I000000010)insert sales values(10011,C0011,E0017,22700.00,19970510,19971117,I000000011) insert sales values(10012,C0011,E0005,13960.00,19971015,19971017,I000000012)insert sales values(10013,C0013,E0014,33000.00,19970310,19971217,I000000013)insert sales values(10014,C0011,E0020,60000.00,19971110,19971224,I000000014)insert sales values(10015,C0015,E0008,20000.00,19971110,19971117,I000000015)insert sales values(10016,C0018,E0015,22700.00,19970210,19971117,I000000016) insert sales values(10017,C0020,E0019,130.00,19970625,19971017,I000000017)insert sales values(10018,C0018,E0019,300.00,19970516,19971217,I000000018)insert sales values(10019,C0020,E0008,300.00,19961113,19971024,I000000019)insert sales values(10020,C0020,E0008,20000.00,19961124,19971017,I000000020)insert sales values(10021,C0008,E0002,35000.00,19990120,19991217,I000000021)go（4）insert sale_item values(10001,P0001,5 , 2500.00,19970510)insert sale_item values(10001,P0002,3 , 6500.00,19970615)insert sale_item values(10003,P0003,2 , 5300.00,19971210)insert sale_item values(10004,P0004,2 , 1600.00,19971220)insert sale_item values(10005,P0005,3 , 2600.00,19970215)insert sale_item values(10005,P0006,1 , 5300.00,19970619)insert sale_item values(10005,P0007,2 , 4800.00,19970422)insert sale_item values(10008,P0008,4 , 2700.00,19970120)insert sale_item values(10009,P0009,2 , 1580.00,19961210)insert sale_item values(10010,P0010,5 , 2500.00,19961220)insert sale_item values(10011,P0001,3 , 6500.00,19970510)insert sale_item values(10011,P0003,3 , 6500.00,19970510)insert sale_item values(10011,P0005,3 , 6500.00,19970510)insert sale_item values(10012,P0012,2 , 5300.00,19971015)insert sale_item values(10012,P0008,2 , 1600.00,19970310)insert sale_item values(10012,P0014,3 , 2600.00,19971110)insert sale_item values(10012,P0015,1 , 5300.00,19971110)insert sale_item values(10012,P0009,5 , 4800.00,19970210)insert sale_item values(10017,P0017,4 , 2700.00,19970625)insert sale_item values(10018,P0018,2 , 1580.00,19970516)insert sale_item values(10019,P0019,5 , 2500.00,19961113)insert sale_item values(10020,P0020,3 , 6500.00,19961124)insert sale_item values(10002,P0021,5 , 5300.00,19970615)insert sale_item values(10001,P0022,2 , 1600.00,19970510)insert sale_item values(10006,P0001,3 , 2500.00,19970619)insert sale_item values(10002,P0001,4 , 2500.00,null)insert sale_item values(10010,P0001,8 , 2500.00,null)insert sale_item values(10021,P0001,8 , 2500.00,null)insert sale_item values(10021,P0003,8 , 2500.00,null)insert sale_item values(10021,P0005,8 , 2500.00,null)insert sale_item values(10021,P0009,8 , 2500.00,null)go（5）insert duct values(P0001,16M DRAM)insert duct values(P0002,14寸显示器)insert duct values(P0003,1.2GB硬盘)insert duct values(P0004,3.5寸软驱)insert duct values(P0005,键盘)insert duct values(P0006,VGA显示卡)insert duct values(P0007,网卡)insert duct values(P0008,Pentium100CPU)insert duct values(P0009,激光打印机)insert duct values(P0010,8倍速光驱)insert duct values(P0011,计算机字典)insert duct values(P0012,9600bits/s调制解调)insert duct values(P0013,Pentium主板)insert duct values(P0014,硕泰克SLK8AN-RL主板)insert duct values(P0015,龙基777FT纯平显示器)insert duct values(P0016,升级KV8MAX3主板)insert duct values(P0017,酷龙-F71散热器)insert dbo.pr