sqlserver创建表.doc

/*
测试数据库--MSSQL版*/createdatabaseBeta_DBgouseBeta_DBgocreatetableemployee(emp_nochar(5)primarykey,--员工编号emp_namechar(10)notnull,--员工姓名sexchar(1)notnull,--性别deptchar(4)notnull,--部门titlechar(6)notnull,--职称date_hireddatetimenotnull,--上任日期birthdaydatetimenull,--生日salaryintnotnull,--工资addrchar(50)null,--住址modifierchar(5)default(user_name(null)),--操作人mod_datedatetimedefault(getdate())--操作日期)gocreatetablecustomer(cus_idchar(5)primarykey,--客户编号cus_namechar(20)notnull,--客户姓名addrchar(40)notnull,--客户住址tel_nochar(10)notnull,--客户电话zipchar(6)null--邮递区号)gocreatetablesales(order_nointidentity(1000,1)notnull,--定单编号cus_idchar(5)notnull,--客户编号sale_idchar(5)notnull,--业务员编号toa_amtnumeric(9,2)notnull,--定单金额order_datedatetimedefault(getdate()),--定货日期0ship_datedatetimenotnull,--出货日期invoice_nochar(10)notnull,--发票号码o_staticchar(2)notnull,--定单处理状况constraintUQ_invoiceuniquenonclustered(invoice_no))gocreatetablesale_item(order_nointnotnull,--定单编号prod_idchar(5)notnull,--产品编号sup_idchar(5)notnull,--供应商编号qtyintnotnull,--销售数量unit_pricenumeric(7,2)notnull,--单价order_datedatetimenull,--定单日期constraintpk_sale_itemprimarykeyclustered(order_no,prod_id,sup_id))gocreatetablestock(prod_idchar(5)notnull,--产品编号sup_idchar(5)notnull,--供应商编号stk_qtyintnotnull,--库存数量constraintpk_stockprimarykeyclustered(prod_id,sup_id))gocreatetablepur_item(pur_nointnotnull,--采购定单prod_idchar(5)notnull,--产品编号sup_idchar(5)notnull,--供应商编号qtyintnotnull,--采购数量unit_pricenumeric(7,2)notnull,--单价pur_datedatetimenotnull,--采购日期constraintpk_pur_itemprimarykeyclustered(pur_no,prod_id,sup_id))gocreatetableproduct(prod_idchar(5)primarykey,--产品编号prod_namechar(20)notnull--产品名称)gocreatetablesupply(sup_idchar(5)notnull,--供应商编号sup_namechar(20)null--供应商名称)goinsertintoemployeevalues('E0001','王大华','M','业务','经理','1976/10/13','1951/08/01',80000,'上海市',default,default)insertintoemployeevalues('E0003','陈自强','M','会计','科长','1986/09/15','1963/06/09',48000,'南京市',default,default)insertintoemployeevalues('E0014','周小梅','F','业务','职员','1996/03/01','1970/03/28',32000,'上