- CREATE TABLE D (
- Yxh char(50) NOT NULL,
- mc char(50) NOT NULL,
- dz char(50) NOT NULL,
- lxdh char(50) NOT NULL,
- PRIMARY KEY (Yxh));
- CREATE TABLE S (
- Xh char(50) NOT NULL,
- xm char(50) NOT NULL,
- xb char(10) NOT NULL,
- csrq char(50) NOT NULL,
- jg char(50) NOT NULL,
- sjhm char(50) NOT NULL,
- yxh char(50) NOT NULL,
- Zt char(50) NULL,
- PRIMARY KEY (Xh),
- Foreign key (yxh) references D(Yxh));
- CREATE TABLE T (
- Gh char(50) NOT NULL,
- xm char(50) NOT NULL,
- xb char(10) NOT NULL,
- csrq char(50) NOT NULL,
- Zc char(50) NOT NULL,
- yxh char(50) NOT NULL,
- PRIMARY KEY (Gh),
- Foreign key (yxh) references D(Yxh));
- CREATE TABLE C (
- kh char(50) NOT NULL,
- km char(50) NOT NULL,
- xf char(10) NOT NULL,
- xs char(10) NOT NULL,
- yxh char(50) NOT NULL,
- PRIMARY KEY (kh),
- Foreign key (yxh) references D(Yxh));
- CREATE TABLE O (
- xq char(30) NOT NULL,
- kh char(50) NOT NULL,
- gh char(50) NOT NULL,
- sksj char(50) NOT NULL,
- PRIMARY KEY (xq,kh,gh),
- Foreign key (kh) references C(kh),
- Foreign key (gh) references T(Gh));
- CREATE TABLE E (
- Xh char(50) NOT NULL,
- xq char(30) NOT NULL,
- kh char(50) NOT NULL,
- gh char(50) NOT NULL,
- cj char(10) NULL,
- PRIMARY KEY (Xh,xq,kh,gh),
- Foreign key (Xh) references S(Xh),
- Foreign key (xq,kh,gh) references O(xq,kh,gh));
- //--- 分割线 ---
- insert into D(Yxh,mc,dz,lxdh)
- values
- ('01','计算机学院','上大东校区三号楼','65347567'),
- ('02','通讯学院','上大东校区二号楼','65341234'),
- ('03','材料学院','上大东校区四号楼','65347890');
- insert into S(Xh,xm,xb,csrq,jg,sjhm,yxh)
- values
- ('1101','李明','男','1993-03-06','上海','13613005486','02'),
- ('1102','刘晓明','男','1992-12-08','安徽','18913457890','01'),
- ('1103','张颖','女','1993-01-05','江苏','18826490423','01'),
- ('1104','刘晶晶','女','1994-11-06','上海','13331934111','01'),
- ('1105','刘成刚','男','1991-06-07','上海','18015872567','01'),
- ('1106','李二丽','女','1993-05-04','江苏','18107620945','01'),
- ('1107','张晓峰','男','1992-08-16','浙江','13912341078','01')
- ;
- insert into T(Gh,xm,xb,csrq,Zc,yxh)
- values
- ('0101','陈迪茂','男','1973-03-06','副教授','01'),
- ('0102','马小红','女','1972-12-08','讲师','01'),
- ('0201','张心颖','女','1960-01-05','教授','02'),
- ('0103','吴宝钢','男','1980-11-06','讲师','01')
- ;
- insert into C(kh,km,xf,xs,yxh)
- values
- ('08305001','离散数学','4','40','01'),
- ('08305002','数据库原理','4','50','01'),
- ('08305003','数据结构','4','50','01'),
- ('08305004','系统结构','6','60','01'),
- ('08301001','分子物理学','4','40','03'),
- ('08302001','通信学','3','30','02')
- ;
- insert into O(xq,kh,gh,sksj)
- values
- ('201201','08305001','0103','星期三 5-8'),
- ('201202','08305002','0101','星期三 1-4'),
- ('201202','08305002','0102','星期三 1-4'),
- ('201202','08305002','0103','星期三 1-4'),
- ('201202','08305003','0102','星期五 5-8'),
- ('201301','08305004','0101','星期二 1-4'),
- ('201301','08305001','0102','星期一 5-8'),
- ('201302','08302001','0201','星期一 5-8')
- ;
- insert into E(Xh,xq,kh,gh,cj)
- values
- ('1101','201201','08305001','0103','60'),
- ('1102','201201','08305001','0103','87'),
- ('1102','201202','08305002','0101','82'),
- ('1102','201301','08305004','0101',null),
- ('1103','201201','08305001','0103','56'),
- ('1103','201202','08305002','0102','75'),
- ('1103','201202','08305003','0102','84'),
- ('1103','201301','08305001','0102',null),
- ('1103','201301','08305004','0101',null),
- ('1104','201201','08305001','0103','74'),
- ('1104','201302','08302001','0201',null),
- ('1106','201201','08305001','0103','85'),
- ('1106','201202','08305002','0103','66'),
- ('1107','201201','08305001','0103','90'),
- ('1107','201202','08305003','0102','79'),
- ('1107','201301','08305004','0101',null)
- ;
来源: http://www.bubuko.com/infodetail-3716487.html