|
|
想找个范例的数据库原来真不是简单。呵呵
原书本上的数据库只是个标准，不是实现的版本。
在网上艰辛地找到了这个表格之后与发现自已用的Mysql字段的类型不符。
于是做了改动。下面的Sql文件可以直接导入Mysql了。
有了这个东东。可以节省很多输入的时间。学习更方便了。
--study.sql文件如下：
create datebase study;
use study
--表employee_tbl结构
create table employee_tbl
(emp_id varchar(9) not null,
last_name varchar(15) not null,
first_name varchar(15) not null,
middle_name varchar(15),
address varchar(30) not null,
city varchar(15) not null,
state char(2) not null,
zip numeric(5) not null,
phone char(10),
pager char(10),
constraint emp_pk primary key(emp_id) );
--表employee_pay_tbl结构
create table employee_pay_tbl
(emp_id varchar(9) not null,
position varchar(15) not null,
date_hire date,
pay_rate numeric(4,2),
date_last_raise date,
salary numeric(8,2),
bonus numeric(6,2),
constraint emp_fk foreign key(emp_id) references employee_tbl(emp_id) );
--表customer_tbl结构
create table customer_tbl
(cust_id varchar(10) not null primary key,
cust_name varchar(30) not null,
cust_address varchar(20) not null,
cust_city varchar(15) not null,
cust_state char(2) not null,
cust_zip numeric(5) not null,
cust_phone numeric(10),
cust_fax numeric(10) );
--表orders_tbl结构
create table orders_tbl
(ord_num varchar(10) not null primary key,
cust_id varchar(10) not null,
prod_id varchar(10) not null,
qty numeric(6) not null,
ord_date date );
--表products_tbl结构
create table products_tbl
(prod_id varchar(10) not null primary key,
prod_desc varchar(40) not null,
cost numeric(6,2) not null );
以下是各表的插入记录。
insert into employee_tbl values
('311549902','STEPHENS','TINA','DAWN','RR 3 BOX 17A','GREEN WOOD',
'IN','47890','3178784465',NULL) ;
insert into employee_tbl values
('442346889','PLEW','LINDA','CAROL','3301 REACON','INDIANAPO LIS',
'IN','46224','3172978990',NULL) ;
insert into employee_tbl values
('213764555','GLASS','BRANDON','SCOTT','1710 MAIN ST','WHITELAND',
'IN','47885','3178984321','3175709980') ;
insert into employee_tbl values
('313782439','GLASS','JACOB',NULL,'3789 WHITE RIVER BLVD',
'INDIANAPOLIS','IN','45734','3175457676','8887345678') ;
insert into employee_tbl values
('220984332','WALLACE','MARIAH',NULL,'7889 KEYSTONE AVE',
'INDIANAPOLIS','IN','48741','3173325986',NULL) ;
insert into employee_tbl values
('443679012','SPURGEON','TIFFANY',NULL,'5 GEORGE COURT',
'INDIANAPOLIS','IN','46234','317569007',NULL) ;
insert into employee_pay_tbl values
('311549902','MARKETING','1989-05-23',NULL,'1997-05-01','40000',NULL) ;
insert into employee_pay_tbl values
('442346889','TEAM LEADER','1990-06-17','14.75','1997-06-01',NULL,NULL) ;
insert into employee_pay_tbl values
('213764555','SALES MANAGER','1994-08-14',NULL,'1997-08-01','30000','2000') ;
insert into employee_pay_tbl values
('313782439','SALESMAN','1997-06-28',NULL,NULL,'20000','1000');
insert into employee_pay_tbl values
('220984332','SHIPPER','1996-07-22','11.00','1997-07-01',NULL,NULL) ;
insert into employee_pay_tbl values
('443679012','SHIPPER','1991-01-14','15.00','1997-01-01',NULL,NULL) ;
insert into customer_tbl values
('232','LESLTE GLEASON','798 HARDAWAY DR','INDIANAPOLIS',
'IN','47856','3175457690',NULL) ;
insert into customer_tbl values
('109','NANCY BUNKER','APT A 4556 WATERWAY','BROAD RIPPLE',
'IN','47950','3174262323',NULL) ;
insert into customer_tbl values
('345','ANGELA DOBKO','RR3 BOX 76','LEBANON','IN','49967',
'7858970090',NULL) ;
insert into customer_tbl values
('090','WENDY WOLF','3345 GATEWAY DR','INDIANAPOLIS','IN',
'46224','3172913421',NULL) ;
insert into customer_tbl values
('12','MARYS GIFT SHOP','435 MAIN ST','DANVILLE','IL','47978',
'3178567221','3178523434') ;
insert into customer_tbl values
('432','SCOTT MARKET','RR2 BDX 173','BROWNSBURG','IN',
'45687','3178529835','3178529836') ;
insert into customer_tbl values
('333','JASDNS AND DALLAS GOODIES','LAFAYETTE SQ MALL',
'INDIANAPOLIS','IN','46222','3172978886','3172978887') ;
insert into customer_tbl values
('21','MORGANS CANDIES AND TREATS','5657 W TENTH ST',
'INDIANAPOLIS','IN','46234','3172714398',NULL) ;
insert into customer_tbl values
('43','SCHYLERS NOVELTIES','17 MAPLE ST','LEBANON','IN',
'48990','3174346758',NULL) ;
insert into customer_tbl values
('288','HOLLYS GAMEARAMA','567 US 31 SOUTH','WHITELAND',
'IN','49980','3178879023',NULL) ;
insert into customer_tbl values
('287','GAVINS PLACE','9880ROCKVILLE RD','INDIANAPOLIS',
'IN','46244','3172719991','3172719992') ;
insert into customer_tbl values
('590','HEATHERS FEATHERS AND THINGS','4090 N SHADELAND AVE',
'INDIANAPOLIS','IN','43278','3175456768',NULL) ;
insert into customer_tbl values
('610','RAGANS HOBBIES INC','451 GREEN ST','PLAINFIELD','IN',
'46818','3178393441','3178399090') ;
insert into customer_tbl values
('560','ANDYS CANDIES','RR 1 BOX 34','NASHVILLE','IN',
'48756','8123239871',NULL) ;
insert into customer_tbl values
('221','RYANS STUFF','2337 S SHELBY ST','INDIANAPOLIS','IN',
'47834','3175634402',NULL) ;
insert into orders_tbl values
('56A901','232','11235','1','1997-10-22') ;
insert into orders_tbl values
('56A917','12','907','100','1997-09-30') ;
insert into orders_tbl values
('32A132','43','222','25','1997-10-10') ;
insert into orders_tbl values
('16C17','090','222','2','1997-10-17') ;
insert into orders_tbl values
('18D778','287','90','10','1997-10-17') ;
insert into orders_tbl values
('23E934','432','13','20','1997-10-15') ;
insert into products_tbl values
('11235','WITCHES COSTUME','29.99') ;
insert into products_tbl values
('222','PLASTIC PUMPKIN 18 INCH','7.75') ;
insert into products_tbl values
('13','FALSE PARAFFIN TEETH','1.10') ;
insert into products_tbl values
('90','LIGHTED LANTERNS','14.50') ;
insert into products_tbl values
('15','ASSORTED COSTUMES','10.00') ;
insert into products_tbl values
('9','CANDY CORN','1.35') ;
insert into products_tbl values
('6','PUMPKIN CANDY','1.45') ;
insert into products_tbl values
('87','PLASTIC SPIDERS','1.05') ;
insert into products_tbl values
('119','ASSORTED MASKS','4.95') ;
|
|
