狂神说-MySQL学习-school.sql代码
CREATE DATABASE IF NOT EXISTS school;
– 创建⼀个school数据库
USE school;
– 创建年级表
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=6DEFAULT CHARSET= utf8;
– 插⼊年级数据
INSERT INTO grade (gradeid,gradename)VALUES(1,'⼤⼀'),(2,'⼤⼆'),(3,'⼤三'),(4,'⼤四'),(5,'预科班');
– 创建成绩表
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 result(studentno,subjectno,examdate,studentresult)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
– 创建学⽣表
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;
– 插⼊学⽣数据其余⾃⾏添加这⾥只添加了2⾏
INSERT INTO student (studentno,loginpwd,studentname,sex,gradeid,phone,address,borndate,email,identitycard) VALUES
(1000,'123456','张伟',0,2,'138********','北京朝阳','1980-1-1','text123@qq','123456************'), (1001,'123456','赵强',1,3,'138********','⼴东深圳','1990-1-1','text111@qq','123456************');
– 创建科⽬表
DROP TABLE IF EXISTS SUBJECT;
CREATE TABLE SUBJECT(
subjectno INT(11)NOT NULL AUTO_INCREMENT COMMENT'课程编号',
subjectname VARCHAR(50)DEFAULT NULL COMMENT'课程名称',
subjectname VARCHAR(50)DEFAULT NULL COMMENT'课程名称', classhour INT(4)DEFAULT NULL COMMENT'学时',
gradeid INT(4)DEFAULT NULL COMMENT'年级编号',
PRIMARY KEY(subjectno)
sql容易学吗)ENGINE=INNODB AUTO_INCREMENT=19DEFAULT 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),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。