📄 0041430.txt
字号:
set nocount on
set dateformat mdy
go
use master
go
if exists (select * from sysdatabases where name='db0041430')
drop database db0041430
go
CREATE database db0041430
ON primary
(NAME = 'db0041430_Data1',
FILENAME = 'd:\SQLDatabase\db0041430_Data1.MDF' ,
SIZE = 1, FILEGROWTH = 10%),
(NAME = 'db0041430_Data2',
FILENAME = 'd:\SQLDatabase\db0041430_Data2.MDF' ,
SIZE = 1, FILEGROWTH = 10%),
(NAME = 'db0041430_Data3',
FILENAME = 'd:\SQLDatabase\db0041430_Data3.MDF' ,
SIZE = 1, FILEGROWTH = 10%),
FILEGROUP myGroup1
(NAME = 'db0041430_group1',
FILENAME = 'd:\SQLDatabase\db0041430_group1.MDF' ,
SIZE = 1, FILEGROWTH = 10%),
(NAME = 'db0041430_group2',
FILENAME = 'd:\SQLDatabase\db0041430_group2.MDF' ,
SIZE = 1, FILEGROWTH = 10%),
FILEGROUP myGroup2
(NAME = 'db0041430_group3',
FILENAME = 'd:\SQLDatabase\db0041430_group3.MDF' ,
SIZE = 1, FILEGROWTH = 10%)
LOG ON
(NAME = 'db0041430_Log1',
FILENAME = 'd:\SQLDatabase\db0041430_Log1.LDF' ,
SIZE = 1, FILEGROWTH = 10%),
(NAME = 'db0041430_Log2',
FILENAME = 'd:\SQLDatabase\db0041430_Log2.LDF' ,
SIZE = 1, FILEGROWTH = 10%)
GO
use db0041430
go
/*员工人事表*/
create table employee(
emp_no char(5) not null, /*员工编号*/
emp_name char(10) not null, /*员工姓名*/
sex char(1) not null, /*员工性别*/
dept char(4) not null, /*所属部门*/
title char(6) not null, /*职称*/
date_hired datetime not null, /*到职日*/
birthday datetime null, /*员工生日*/
salary int not null, /*薪水*/
addr char(50) null, /*员工住址*/
)
on myGroup1
go
/*人事表（employee）数据：*/
insert employee values('A0001','陈小元','M','营销','经理', '19950317','19751026',140000,'上海市南京路')
insert employee values('A0002','张丽萍','F','公关','职员', '19961002','19710328',80000,'上海市南京路')
insert employee values('A0003','张晓珍','F','公关','职员', '19960406','19720328',90000,'上海市南京路')
insert employee values('A0004','孙衣含','F','公关','经理', '19960715','19700328',100000,'上海市南京路')
insert employee values('A0005','陈林','M','营销','职员', '19961103','19720328',12000,'上海市南京路')
insert employee values('A0006','陈胜军','M','营销','职员', '19961103','19700328',10800,'上海市南京路')
insert employee values('A0007','韩一凤','F','公关','职员', '19961211','19701128',98000,'上海市南京路')
insert employee values('A0008','苏智勇','M','公关','职员', '19960521','19710228',90000,'上海市南京路')
insert employee values('A0009','吴圣威','M','公关','职员', '19960912','19700628',90000,'上海市南京路')
insert employee values('A0010','林一铃','F','企划','经理', '19960102','19711128',120000,'上海市南京路')
insert employee values('A0011','申优珍','F','企划','职员', '19981106','19601206',89000,'上海市南京路')
insert employee values('A0012','陈玲', 'F','企划','职员', '19960716','19720228',89000,'上海市南京路')
insert employee values('A0013','李智慧','F','秘书','职员', '19960625','19710228',72000,'上海市南京路')
insert employee values('A0014','陆明君','F','秘书','职员', '19961013','19700528',72000,'上海市南京路')
insert employee values('A0015','宋亚薇','F','秘书','职员', '19961031','19701118',72000,'上海市南京路')
insert employee values('A0016','宋晓珍','F','秘书','经理', '19961125','19701209',98000,'上海市南京路')
insert employee values('A0017','李小珍','F','财务','职员', '19960915','19710804',88000,'上海市南京路')
insert employee values('A0018','谷玲玲','F','财务','职员', '19960629','19700623',82000,'上海市南京路')
insert employee values('A0019','李伟明','M','财务','职员', '19960715','19710916',84000,'上海市南京路')
insert employee values('A0020','裴亚溪','M','财务','总监', '19950526','19710218',102000,'上海市南京路')
go
select * from employee
/*客户表*/
create table customer(
cust_id char(5) not null primary key,/*客户号*/
cust_name char(20) not null, /*客户名称*/
addr char(40) not null, /*客户住址*/
tel_no char(13) not null, /*客户电话*/
zip char(6) null /*邮政编码*/
)
on myGroup1
go
/*客户表（customer）数据：*/
insert customer values('B0001','立信', '武汉市', '027-1234561','15200')
insert customer values('B0002','立信', '武汉市', '027-1234561','15200')
insert customer values('B0003','立信', '武汉市', '027-1234561','15200')
insert customer values('B0004','立信', '武汉市', '027-1234561','15200')
insert customer values('B0005','立信', '武汉市', '027-1234561','15200')
insert customer values('B0006','立信', '武汉市', '027-1234561','15200')
insert customer values('B0007','立信', '武汉市', '027-1234561','15200')
insert customer values('B0008','互联', '北京市', '010-1818191','56230')
insert customer values('B0009','互联', '北京市', '010-1818191','56230')
insert customer values('B0010','互联', '北京市', '010-1818191','56230')
insert customer values('B0011','互联', '北京市', '010-1818191','56230')
insert customer values('B0012','互联', '北京市', '010-1818191','56230')
insert customer values('B0013','互联', '北京市', '010-1818191','56230')
insert customer values('B0014','互联', '上海市', '021-1818191','56230')
insert customer values('B0015','胜利股份有限公司', '江西省', '0791-123456','560')
insert customer values('B0016','胜利股份有限公司', '江西省', '0791-123456','560')
insert customer values('B0017','胜利股份有限公司', '江西省', '0791-123456','560')
insert customer values('B0018','浙江财大', '浙江省', '021-35148455','104')
insert customer values('B0019','公交公司', '浙江省', '0512-3811745','250')
insert customer values('B0020','浙江大学', '浙江省', '0512-3811547','250')
go
select * from customer
/*销售主表*/
create table sales(
order_no int not null primary key,/*定单编号*/
cust_id char(5) not null, /*客户号*/
sale_id char(5) not null, /*业务员编号*/
tot_amt numeric(9,2) not null, /*定单金额*/
order_date datetime not null, /*定货日期*/
ship_date datetime not null, /*出货日期*/
invoice_no char(10) not null, /*发票号码*/
)
on myGroup1
go
/*销售主表（sales）数据：*/
insert sales values('01','C0001','E0006','700.00','19960410','19971017','00001')
insert sales values('02','C0002','E0012','13960.00','19960615','19971017','00002')
insert sales values('03','C0003','E0014','33000.00','19961210','19971217','00003')
insert sales values('04','C0004','E0005','60000.00','19961220','19971224','00004')
insert sales values('05','C0004','E0008','20000.00','19960215','19970517','00005')
insert sales values('06','C0001','E0013','22700.00','19970119','19971117','00006')
insert sales values('07','C0007','E0010','13960.00','19970122','19971017','00007')
insert sales values('08','C0008','E0002','33000.00','19970120','19971217','00008')
insert sales values('09','C0002','E0006','100.00','19971210','19971224','00009')
insert sales values('10','C0010','E0002','20000.00','19971220','19971017','00010')
insert sales values('11','C0011','E0017','22700.00','19970510','19971117','000011')
insert sales values('12','C0011','E0005','13960.00','19971015','19971017','00012')
insert sales values('13','C0013','E0014','33000.00','19970310','19971217','00013')
insert sales values('14','C0011','E0020','60000.00','19971110','19971224','00014')
insert sales values('15','C0015','E0008','20000.00','19971110','19971117','00015')
insert sales values('16','C0018','E0015','22700.00','19970210','19971117','00016')
insert sales values('17','C0020','E0019','130.00','19970625','19971017','00017')
insert sales values('18','C0018','E0019','300.00','19960516','19971217','00018')
insert sales values('19','C0020','E0008','300.00','19960713','19971024','00019')
insert sales values('20','C0020','E0008','20000.00','19960824','19971017','00020')
go
select * from sales
/*销售明细表*/
create table sale_item(
order_no int not null, /*定单编号*/
prod_id char(5) not null, /*产品编号*/
qty int not null, /*销售数量*/
unit_price numeric(9,2) not null, /*单价*/
order_date datetime null, /*定单日期*/
constraint pk_sale_item primary key clustered(order_no,prod_id)
)
on myGroup1
go
/*销售明细表（sale_item）数据：*/
insert sale_item values(01,'P0001',5 , 2500.00,'19970710')
insert sale_item values(01,'P0002',3 , 6500.00,'19980615')
insert sale_item values(03,'P0003',2 , 5300.00,'19981210')
insert sale_item values(04,'P0004',2 , 1600.00,'19970120')
insert sale_item values(05,'P0005',3 , 2600.00,'19971215')
insert sale_item values(05,'P0006',1 , 5300.00,'19980619')
insert sale_item values(05,'P0007',2 , 4800.00,'19980422')
insert sale_item values(08,'P0008',4 , 2700.00,'19990120')
insert sale_item values(09,'P0009',2 , 1580.00,'19991210')
insert sale_item values(10,'P0010',5 , 2500.00,'19960620')
insert sale_item values(11,'P0011',3 , 6500.00,'19970710')
insert sale_item values(12,'P0012',2 , 5300.00,'19971115')
insert sale_item values(12,'P0013',2 , 1600.00,'19980310')
insert sale_item values(12,'P0014',3 , 2600.00,'19981110')
insert sale_item values(12,'P0015',1 , 5300.00,'19991110')
insert sale_item values(12,'P0016',5 , 4800.00,'19970210')
insert sale_item values(17,'P0017',4 , 2700.00,'19980625')
insert sale_item values(18,'P0018',2 , 1580.00,'19970516')
insert sale_item values(19,'P0019',5 , 2500.00,'19961113')
insert sale_item values(20,'P0020',3 , 6500.00,'19981124')
insert sale_item values(02,'P0021',5 , 5300.00,'19970615')
insert sale_item values(01,'P0022',2 , 1600.00,'19970510')
insert sale_item values(06,'P0023',3 , 2500.00,'19980712')
insert sale_item values(02,'P0024',4 , 2500.00,null)
insert sale_item values(100,'P0025',8 , 2500.00,null)
insert sale_item values(103,'P0026',8 , 2500.00,null)
insert sale_item values(101,'P0027',8 , 2500.00,null)
insert sale_item values(110,'P0028',8 , 2500.00,null)
insert sale_item values(210,'P0029',8 , 2500.00,null)
insert sale_item values(120,'P0030',8 , 2500.00,null)
insert sale_item values(120,'P0031',8 , 2500.00,null)
insert sale_item values(110,'P0032',8 , 2500.00,null)
insert sale_item values(120,'P0033',8 , 2500.00,null)
insert sale_item values(130,'P0034',8 , 2500.00,null)
insert sale_item values(109,'P0035',8 , 2500.00,null)
insert sale_item values(108,'P0036',8 , 2500.00,null)
insert sale_item values(107,'P0037',8 , 2500.00,null)
insert sale_item values(107,'P0038',8 , 2500.00,null)
insert sale_item values(170,'P0039',8 , 2500.00,null)
insert sale_item values(109,'P0040',8 , 2500.00,null)
insert sale_item values(108,'P0041',8 , 2500.00,null)
insert sale_item values(107,'P0042',8 , 2500.00,null)
insert sale_item values(107,'P0043',8 , 2500.00,null)
insert sale_item values(108,'P0044',8 , 2500.00,null)
insert sale_item values(106,'P0045',8 , 2500.00,null)
insert sale_item values(107,'P0046',8 , 2500.00,null)
insert sale_item values(105,'P0047',8 , 2500.00,null)
insert sale_item values(105,'P0048',8 , 2500.00,null)
insert sale_item values(104,'P0049',8 , 2500.00,null)
insert sale_item values(11,'P0050',8 , 2500.00,null)
go
select * from sale_item
/*产品名称表*/
create table product(
prod_id char(5) not null primary key,/*产品编号*/
prod_name char(20) not null, /*产品名称*/
)
on myGroup1
go
/*产品名称表（product）数据：*/
insert product values('P0001','手套')
insert product values('P0002','手套')
insert product values('P0003','手套')
insert product values('P0004','鞋子')
insert product values('P0005','鞋子')
insert product values('P0006','鞋子')
insert product values('P0007','帽子')
insert product values('P0008','裤子')
insert product values('P0009','裤子')
insert product values('P0010','裤子')
insert product values('P0011','毛衣')
insert product values('P0012','毛衣')
insert product values('P0013','帽子')
insert product values('P0014','帽子')
insert product values('P0015','袜子')
insert product values('P0016','袜子')
insert product values('P0017','帽子')
insert product values('P0018','帽子')
insert product values('P0019','裙子')
insert product values('P0020','帽子')
go
select * from product
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -