创建一个school数据库作为测试库
创建一个school数据库作为测试数据库
代码如下:需要更多数据自行添加
CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别,0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址,允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard`(`identitycard`),
KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
-- data for the table `grade`
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`classhour` INT(4) DEFAULT NULL COMMENT '学时',
`gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`subjectno` INT(4) NOT NULL COMMENT '课程编号',
`examdate` DATETIME NOT NULL COMMENT '考试日期',
`studentresult` INT (4) NOT NULL COMMENT '考试成绩',
KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),`subject`
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
-- 插入学生数据
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`,`Email`,
`IdentityCard`) VALUES
(1000, '123', '张三', 1, 1, '123456789', '西安', '2013-11-11 16:00:00', '123@qq.com','12345113674489#'),
(1001, '123', '李四', 1, 2, '123456789', '北京', '2013-11-11 16:00:00', '123@qq.com', '123453674489#'),
(1002, '123', '王五', 0, 3, '123456789', '西安', '2013-11-11 16:00:00', '123@qq.com', '12341256789#'),
(1003, '123', '张六', 0, 4, '123456789', '西安', '2013-11-11 16:00:00', '123@qq.com', '1234560041789#'),
(1004, '123', '李七', 1, 5, '123456789', '重庆', '2013-11-11 16:00:00', '123@qq.com', '123124125655789#'),
(1005, '123', '王八', 0, 1, '123456789', '西安', '2013-11-11 16:00:00', '123@qq.com', '123451164789#'),
(1006, '123', '张久', 0, 2, '123456789', '西安', '2013-11-11 16:00:00', '123@qq.com', '12345678029#'),
(1007, '123', '李式', 1, 3, '123456789', '西安', NULL, '123@qq.com', '1234524672289#'),
(1008, '123', '赵四', 1, 4, '123456789', '西安', '2013-11-11 16:00:00', '123@qq.com', '12345106789#'),
(1009, '123', '狂神', 1, 5, '123456789', '深证', NULL, '123@qq.com', '789456123');
INSERT INTO `result`(`StudentNo`, `SubjectNo`, `ExamDate`, `StudentResult`) VALUES (1000, 1, '2013-11-11 16:00:00', 80),
(1001, 1, '2013-11-11 16:00:00', 81), (1002, 1, '2013-11-11 16:00:00', 82), (1003, 1, '2013-11-11 16:00:00', 83), (1004, 1, '2013-11-11 16:00:00', 84),(1005, 1, '2013-11-11 16:00:00', 85), (1006, 1, '2013-11-11 16:00:00', 86), (1007, 1, '2013-11-11 16:00:00', 87), (1008, 1, '2013-11-11 16:00:00', 88);