初始化
MYSQL,MSSQLServer:
CREATE TABLE T_Person(FId VARCHAR(20) NOT NULL,FNumber VARCHAR(20),FName VARCHAR(20),FManagerId VARCHAR(20),PRIMARY KEY (FId),FOREIGN KEY (FManagerId) REFERENCES T_Person(FId));
CREATE TABLE T_Merchandise(FId VARCHAR(20) NOT NULL ,FNumber VARCHAR(20),FName VARCHAR(20),FPrice INT,PRIMARY KEY (FId)); CREATE TABLE T_SaleBill(FId VARCHAR(20) NOT NULL ,FNumber VARCHAR(20),FBillMakerId VARCHAR(20),FMakeDate DATETIME,FConfirmDate DATETIME,PRIMARY KEY (FId),FOREIGN KEY (FBillMakerId) REFERENCES T_Person(FId)); CREATE TABLE T_SaleBillDetail(FId VARCHAR(20) NOT NULL ,FBillId VARCHAR(20),FMerchandiseId VARCHAR(20),FCount INT,PRIMARY KEY (FId),FOREIGN KEY (FBillId) REFERENCES T_SaleBill(FId),FOREIGN KEY (FMerchandiseId) REFERENCES T_Merchandise(FId));
CREATE TABLE T_PurchaseBill(FId VARCHAR(20) NOT NULL ,FNumber VARCHAR(20),FBillMakerId VARCHAR(20),FMakeDate DATETIME,FConfirmDate DATETIME,PRIMARY KEY (FId),FOREIGN KEY (FBillMakerId) REFERENCES T_Person(FId)); CREATE TABLE T_PurchaseBillDetail(FId VARCHAR(20) NOT NULL ,FBillId VARCHAR(20),FMerchandiseId VARCHAR(20),FCount INT,PRIMARY KEY (FId),FOREIGN KEY (FBillId) REFERENCES T_PurchaseBill(FId),FOREIGN KEY (FMerchandiseId) REFERENCES T_Merchandise(FId));
Oracle:
CREATE TABLE T_Person(FId VARCHAR2(20) NOT NULL ,FNumber VARCHAR2(20),FName VARCHAR2(20),FManagerId VARCHAR2(20),PRIMARY KEY (FId),FOREIGN KEY (FManagerId) REFERENCES T_Person(FId));
CREATE TABLE T_Merchandise (FId VARCHAR2(20) NOT NULL ,FNumber VARCHAR2(20),FName VARCHAR2(20),FPrice INT,PRIMARY KEY (FId));
CREATE TABLE T_SaleBill (FId VARCHAR2(20) NOT NULL ,FNumber VARCHAR2(20),FBillMakerId VARCHAR2(20),FMakeDate DATE,FConfirmDate DATE,PRIMARY KEY (FId),FOREIGN KEY (FBillMakerId) REFERENCES T_Person(FId)); CREATE TABLE T_SaleBillDetail(FId VARCHAR2(20) NOT NULL ,FBillId VARCHAR2(20),FMerchandiseId VARCHAR2(20),FCount INT,PRIMARY KEY (FId),FOREIGN KEY (FBillId) REFERENCES T_SaleBill(FId),FOREIGN KEY (FMerchandiseId) REFERENCES T_Merchandise(FId));
CREATE TABLE T_PurchaseBill (FId VARCHAR2(20) NOT NULL ,FNumber VARCHAR2(20),FBillMakerId VARCHAR2(20),FMakeDate DATE,FConfirmDate DATE,PRIMARY KEY (FId),FOREIGN KEY (FBillMakerId) REFERENCES T_Person(FId)); CREATE TABLE T_PurchaseBillDetail(FId VARCHAR2(20) NOT NULL ,FBillId VARCHAR2(20),FMerchandiseId VARCHAR2(20),FCount INT,PRIMARY KEY (FId),FOREIGN KEY (FBillId) REFERENCES T_PurchaseBill(FId),FOREIGN KEY (FMerchandiseId) REFERENCES T_Merchandise(FId));
DB2:
CREATE TABLE T_Person (FId VARCHAR(20) NOT NULL ,FNumber VARCHAR(20),FName VARCHAR(20),FManagerId VARCHAR(20),PRIMARY KEY (FId),FOREIGN KEY (FManagerId) REFERENCES T_Person(FId));
CREATE TABLE T_Merchandise (FId VARCHAR(20) NOT NULL ,FNumber VARCHAR(20),FName VARCHAR(20),FPrice INT,PRIMARY KEY (FId)); CREATE TABLE T_SaleBill (FId VARCHAR(20) NOT NULL ,FNumber VARCHAR(20),FBillMakerId VARCHAR(20),FMakeDate DATE,FConfirmDate DATE,PRIMARY KEY (FId),FOREIGN KEY (FBillMakerId) REFERENCES T_Person(FId)); CREATE TABLE T_SaleBillDetail (FId VARCHAR(20) NOT NULL ,FBillId VARCHAR(20),FMerchandiseId VARCHAR(20),FCount INT,PRIMARY KEY (FId),FOREIGN KEY (FBillId) REFERENCES T_SaleBill(FId),FOREIGN KEY (FMerchandiseId) REFERENCES T_Merchandise(FId)); CREATE TABLE T_PurchaseBill (FId VARCHAR(20) NOT NULL ,FNumber VARCHAR(20),FBillMakerId VARCHAR(20),FMakeDate DATE,FConfirmDate DATE,PRIMARY KEY (FId),FOREIGN KEY (FBillMakerId) REFERENCES T_Person(FId)); CREATE TABLE T_PurchaseBillDetail (FId VARCHAR(20) NOT NULL ,FBillId VARCHAR(20),FMerchandiseId VARCHAR(20),FCount INT,PRIMARY KEY (FId),FOREIGN KEY (FBillId) REFERENCES T_PurchaseBill(FId),FOREIGN KEY (FMerchandiseId) REFERENCES T_Merchandise(FId));
insert into T_Person(FId,FNumber,FName,FManagerId)
values('00001','1','Robert',NULL);
insert into T_Person(FId,FNumber,FName,FManagerId)
values('00002','2','John','00001');
insert into T_Person(FId,FNumber,FName,FManagerId)
values('00003','3','Tom','00001');
insert into T_Person(FId,FNumber,FName,FManagerId)
values('00004','4','Jim','00003');
insert into T_Person(FId,FNumber,FName,FManagerId)
values('00005','5','Lily','00002');
insert into T_Person(FId,FNumber,FName,FManagerId)
values('00006','6','Merry','00003');
insert into T_Merchandise(FId,FNumber,FName,FPrice)
values('00001','1','Bacon',30);
insert into T_Merchandise(FId,FNumber,FName,FPrice)
values('00002','2','Cake',2);
insert into T_Merchandise(FId,FNumber,FName,FPrice)
values('00003','3','Apple',6);
insert into T_SaleBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00001','1','00006','2007-03-15','2007-05-15');
insert into T_SaleBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00002','2',null,'2006-01-25','2006-02-03');
insert into T_SaleBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00003','3','00001','2006-02-12','2007-01-11');
insert into T_SaleBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00004','4','00003','2008-05-25','2008-06-15');
insert into T_SaleBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00005','5','00005','2008-03-17','2007-04-15');
insert into T_SaleBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00006','6','00002','2002-02-03','2007-11-11');
insert into T_PurchaseBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00001','1','00006','2007-02-15','2007-02-15');
insert into T_PurchaseBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00002','2','00004','2003-02-25','2006-03-03');
insert into T_PurchaseBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00003','3','00001','2007-02-12','2007-07-12');
insert into T_PurchaseBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00004','4','00002','2007-05-25','2007-06-15');
insert into T_PurchaseBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00005','5','00002','2007-03-17','2007-04-15');
insert into T_PurchaseBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00006','6',null,'2006-02-03','2006-11-20');
insert into T_SaleBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00001','1','00006',TO_DATE('2007-03-15', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2007-05-15', 'YYYY-MM-DD HH24:MI:SS'));
insert into T_SaleBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00002','2',null,TO_DATE('2006-01-25', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2006-02-03', 'YYYY-MM-DD HH24:MI:SS'));
insert into T_SaleBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00003','3','00001',TO_DATE('2006-02-12', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2007-01-11', 'YYYY-MM-DD HH24:MI:SS'));
insert into T_SaleBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00004','4','00003',TO_DATE('2008-05-25', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2008-06-15', 'YYYY-MM-DD HH24:MI:SS'));
insert into T_SaleBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00005','5','00005',TO_DATE('2008-03-17', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2007-04-15', 'YYYY-MM-DD HH24:MI:SS'));
insert into T_SaleBill(FId,FNumber,FBillMakerId,FMakeDate,FConfirmDate)
values('00006','6','00002',TO_DATE('2002-02-03', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2007-11-11', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO T_PurchaseBill (FId, FNumber, FBillMakerId, FMakeDate, FConfirmDate) VALUES ('00001', '1', '00006', TO_DATE('2007-02-15', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2007-02-15', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO T_PurchaseBill (FId, FNumber, FBillMakerId, FMakeDate, FConfirmDate) VALUES ('00002', '2', '00004', TO_DATE('2003-02-25', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2006-03-03', 'YYYY-MM-DD HH24:MI:SS')) ;
INSERT INTO T_PurchaseBill (FId, FNumber, FBillMakerId, FMakeDate, FConfirmDate) VALUES ('00003', '3', '00001', TO_DATE('2007-02-12', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2007-07-12', 'YYYY-MM-DD HH24:MI:SS')) ;
INSERT INTO T_PurchaseBill (FId, FNumber, FBillMakerId, FMakeDate, FConfirmDate) VALUES ('00004', '4', '00002', TO_DATE('2007-05-25', 'YY
程序员的SQL金典8-8
需积分: 9 172 浏览量
2011-05-20
18:30:31
上传
评论
收藏 19KB RAR 举报
SQL.rar （10个子文件） 
书中用到的SQL 
ch02.txt 3KB ch07.txt 6KB ch06.txt 13KB ch10.txt 10KB ch08.txt 10KB ch09.txt 2KB ch11.txt 29KB ch03.txt 3KB ch05.txt 20KB ch04.txt 15KB资源评论
