MySQL建表语句

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