上QQ阅读APP看本书，新人免费读10天
设备和账号都新为新人
2.7 创建数据表并生成样本数据
设计好对象后，就可以开始创建数据表了，创建数据表的SQL语句介绍如下。
（1）本地数据库Derby数据表的代码如下。
# 创建用户users数据表
CREATE TABLE USERS (
ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
USERNAME VARCHAR(8) NOT NULL,
PASSWORD VARCHAR(8) NOT NULL,
USERTAG CHAR(1) WITH DEFAULT '0',
PRIMARY KEY(ID) );
# 创建教学单位名称teachunit数据表
CREATE TABLE teachunit (
id int generated always as identity(start with 1,increment by 1),
unitid char(8) not null,
unitname varchar(8) not null,
primary key(id));
# 创建年度教学单位yearteachunit数据表
CREATE TABLE yearteachunit (
id int generated always as identity(start with 1,increment by 1),
nian int not null,
unitid char(2) not null,
haszyk char(1) not null with default '1',
hasggk char(1) not null with default '0',
ssb int not null with default 0,
jfys decimal(10,2) not null with default 0,
jfss decimal(10,2) not null with default 0,
jxywper decimal(6,2) not null with default 0,
jxglper decimal(6,2) not null with default 0,
jxyjper decimal(6,2) not null with default 0,
szpyper decimal(6,2) not null with default 0,
ta1 int not null with default 0,
ta2 int not null with default 0,
ta3 int not null with default 0,
ta4 int not null with default 0,
ta5 int not null with default 0,
ta6 int not null with default 0,
ta7 int not null with default 0,
ta8 int not null with default 0,
primary key(id));
# 创建年度测算结果calcresult数据表
CREATE TABLE calcresult (
id int generated always as identity(start with 1,increment by 1),
nian int not null,
unitid char(2) not null,
ui decimal(10,2) not null with default 0,
pi decimal(10,2) not null with default 0,
ci decimal(10,2) not null with default 0,
ryjf decimal(10,2) not null with default 0,
zhywf decimal(10,2) not null with default 0,
primary key(id));
# 创建教学单位课程明细course数据表
CREATE TABLE course (
id int generated always as identity(start with 1,increment by 1),
nian int not null,
term char(1) not null,
unitid char(2) not null,
courseid char(8) not null,
coursename char(40) not null,
coursetype char(1) not null,
classname char(40) not null,
nj int not null with default 0,
n2j int not null with default 0,
r1j decimal(3,1) not null with default 1.0,
r2j decimal(3,1) not null with default 1.0,
r3j decimal(3,1) not null with default 1.0,
primary key(id));
# 创建公共参数pubdata数据表
CREATE TABLE pubdata (
id int generated always as identity(start with 1,increment by 1),
nian int not null,
mt decimal(8,2) not null with default 0,
rte decimal(10,5) not null with default 0,
mte decimal(10,5) not null with default 0,
sjf decimal(6,2) not null with default 0,
uper decimal(5,2) not null with default 0,
pper decimal(5,2) not null with default 0,
cper decimal(5,2) not null with default 0,
jcjper decimal(5,2) not null with default 0,
xsknbzper decimal(5,2) not null with default 0,
xshdjfper decimal(5,2) not null with default 0,
xsjxjper decimal(5,2) not null with default 0,
tb1 decimal(4,2) not null with default 1.0,
tb2 decimal(4,2) not null with default 1.0,
tb3 decimal(4,2) not null with default 1.0,
tb4 decimal(4,2) not null with default 1.0,
tb5 decimal(4,2) not null with default 1.0,
tb6 decimal(4,2) not null with default 1.0,
tb7 decimal(4,2) not null with default 1.0,
tb8 decimal(4,2) not null with default 1.0,
primary key(id));
（2）远程教学单位数据库服务器数据表的代码如下所示。
# 创建MySQL数据库课程信息courseinfo数据表
DROP DATABASE IF EXISTS `jwgl`;
CREATE DATABASE `jwgl`;
USE `jwgl`;
CREATE TABLE `courseinfo` (
`id` int(11) NOT NULL auto_increment,
`nian` int(4) NOT NULL,
`term` char(1) NOT NULL,
`unitid` char(2) NOT NULL,
`courseid` char(8) NOT NULL,
`coursename` char(40) NOT NULL,
`coursetype` char(1) NOT NULL,
`classname` char(40) NOT NULL,
`nj` int(6) NOT NULL default '0',
`n2j` int(4) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED COMMENT='课程明细表';
（3）生成样本数据
下面是生成样本数据的一些示例SQL语句，随书所附光盘中有更详细的样本SQL语句。
# 生成Derby的users表数据
insert into USERS(USERNAME,PASSWORD,USERTAG) values('admin','1010','2');
insert into USERS(USERNAME,PASSWORD,USERTAG) values('jwc','jwc','1');
# 生成Derby的teachunit表数据
insert into teachunit(unitid,unitname) values('01','英语');
insert into teachunit(unitid,unitname) values('02','机电学院');
# 生成Derby的yearteachunit表数据
insert into yearteachunit(nian,unitid,hasggk,ssb,jfys,jfss,ta1,ta3,ta6)
values(2006,'01','1',16,535500.0,535500.0,5,8,15);
insert into yearteachunit(nian,unitid,hasggk,ssb,jfys,jfss,ta1,ta3,ta6)
values(2006,'02','0',17,635500.0,535500.0,6,12,10);
# 生成Derby的calcresult表数据
insert into calcresult(nian,unitid,ui,pi,ci,ryjf,zhywf)
values(2006,'01',56,66,43,50,95);
insert into calcresult(nian,unitid,ui,pi,ci,ryjf,zhywf)
values(2006,'02',46,66,63,55,78);
# 生成Derby的course表数据
insert into course(nian,term,unitid,courseid,coursename,coursetype,classname,
nj,n2j) values(2006,'1','01','01100010','大学英语','1','科技英语041-2',78,60);
insert into course(nian,term,unitid,courseid,coursename,coursetype,classname,
nj,n2j) values(2006,'1','01','01100120','高级英语','1','英语031-2',65,60);
# 生成Derby的pubdata表数据
insert into pubdata(nian,tb1,tb2,tb3,tb4,tb5,tb6,tb7,tb8)
values(2006,1.3,1.25,1.2,1.15,1.1,1.05,1.0,0.9);
insert into pubdata(nian,tb1,tb2,tb3,tb4,tb5,tb6,tb7,tb8)
values(2007,1.3,1.25,1.2,1.15,1.1,1.05,1.0,0.9);
# 生成MySQL的courseinfo表数据
INSERT INTO `courseinfo` VALUES (1,2007,'1','01','01100010','大学英语','1',
'科技英语051-2',70,60);
INSERT INTO `courseinfo` VALUES (2,2007,'1','01','01100120','高级英语','1',
'英语041-2',67,60);