create database testonline;
use testonline;
--创建student表
create table student(
id int(10) not null unique auto_increment,
student_id varchar(10) not null unique,
student_name varchar(20) not null ,
password varchar(12) not null,
sex int(1) ,
birth date,
telphone varchar(11),
idcard varchar(18) not null unique,
class_no varchar(8) not null,
login_time varchar(20) ,
status int(1) not null default 0,
primary key (id,student_id)
);
--创建教师表 teacher
create table teacher(
id int(10) not null unique auto_increment,
teacher_id varchar(10) not null unique,
teacher_name varchar(20) not null,
password varchar(12) not null,
sex int(1) ,
birth date,
telephone varchar(11),
idcard varchar(18) not null unique,
class_no varchar(8) not null,
login_time varchar(20),
status int(1) not null default 0,
primary key(id,teacher_id)
);
--创建管理员表administrator
create table administrator(
id int(10) not null unique auto_increment,
name varchar(20) not null,
password varchar(12) not null,
login_time varchar(20) ,
primary key (id)
);
--创建班级表 class
create table class(
id int(10) not null unique auto_increment,
class_no varchar(8) not null unique,
class_name varchar(20) not null unique,
status int(1) not null,
primary key(id,class_no)
);
--创建学科表subject
create table subject(
id int(10) not null unique auto_increment,
subject_no varchar(4) not null unique,
subject_name varchar(30) not null unique,
subject_status int(1) default 0 not null,
primary key(id,subject_no)
);
6.试题表 exam
create table exam(
id int(10) not null unique primary key auto_increment,
topic varchar(100) not null unique,
option_a varchar(50),
option_b varchar(50),
option_c varchar(50),
option_d varchar(50),
option_e varchar(50),
option_f varchar(50),
option_g varchar(50),
option_h varchar(50),
exam_type varchar(2) not null,
rightkey varchar(8) not null,
subject_no varchar(4) not null,
teacher_id varchar(10) not null unique);
7.试卷表 testpaper
create table testpaper(
testpaper_id int(10) not null unique primary key auto_increment,
subject_no varchar(4) not null,
exam_ids text,
class_nos text,
testpaper_status int(1) not null default 1);
8.成绩表 score
create table score(
id int(10) not null unique primary key auto_increment,
student_id varchar(10) not null unique,
score varchar(5) not null,
testpaper_id int(10) not null);
9.考试作答表 exam_answer
create table exam_answer(
id int(10) not null unique primary key auto_increment,
student_id varchar(10) not null,
testpaper_id int(10) not null,
exam_ids text not null,
exam_answers text not null);
10.留言板表 message_board
create table message_board(
id int(10) not null unique primary key auto_increment,
account_id varchar(10) not null,
content text not null,
replay_id int(10) not null);
11.公告表 notices
create table notices(
id int(10) not null unique primary key auto_increment,
title varchar(50) not null,
administrator_id int(10) not null,
date date not null unique,
content text not null,
sfzd char(1));
12.登录日志表 login_log
create table login_log(
id int(10) not null unique primary key auto_increment,
login_account varchar(10) not null unique,
login_ip varchar(15) not null,
login_mac varchar(17) not null,
login_time varchar(20) not null);
--创建外连接
alter table student add foreign key(class_no) references class(class_no);
alter table exam add foreign key(subject_no) references subject(subject_no);
alter table exam add foreign key(teacher_id) references teacher(teacher_id);
alter table testpaper add foreign key(subject_no) references subject(subject_no);
alter table score add foreign key(student_id) references student(student_id);
alter table score add foreign key(testpaper_id) references testpaper(testpaper_id);
alter table exam_answer add foreign key(student_id) references student(student_id);
alter table exam_answer add foreign key(testpaper_id) references testpaper(testpaper_id);
alter table notices add foreign key(administrator_id) references administrator(id);
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
在线购物商城表
create table tb_cart
(cart_id int(11) not null unique AUTO_INCREMENT primary key,
cart_user_id INT(11) not null unique,
cart_standard_id int(11) unique,
cart_count int(6)
);
create table tb_order(
order_id int(11) not null unique AUTO_INCREMENT primary key,
order_address_id int(11) not null unique,
order_standard_id int(11) not null unique,
order_createtime datetime not null,
order_sendtime datetime,
order_paytime datetime,
order_confirmtime datetime,
order_status VARCHAR(1) default 1,
order_user_id INT(11) not null ,
order_seller_id int(11) not null
);
create table standard(
standard_id INT(11) not null unique AUTO_INCREMENT primary key,
standard_price decimal(10,2) not null,
standard_surplus INT(6) not null,
standard_one varchar(5),
standard_two varchar(5),
standard_three varchar(5),
standard_goods_id int (11) not null
);
540955079