Mysql 创建学校管理数据库

-- 创建数据库
create database m_school charset utf8;

-- 设置命令窗口编码格式
set names gbk;
-- -----------------------------------------------------------------------
-- 创建学生表
use m_school;
create table student(
id int unsigned  primary key auto_increment,
number int unsigned unique key not null comment "学号",
name varchar(12) not null comment "姓名",
sex enum("men","women") not null comment "性别",
age int unsigned  default 18 not null comment"年龄",
class varchar(12) comment"班级"
)charset utf8;

desc student;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id     | int(10) unsigned    | NO   | PRI | NULL    |       |
| number | int(10) unsigned    | YES  | UNI | NULL    |       |
| name   | varchar(12)         | NO   |     | NULL    |       |
| sex    | enum('men','women') | NO   |     | NULL    |       |
| age    | int(10) unsigned    | YES  |     | NULL    |       |
| class  | varchar(12)         | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+



-- -----------------------------------------------------------------------
-- 创建学生家庭信息表

create table stu_family(
id int unsigned primary key auto_increment,
father varchar(12)  comment "父亲",
mother varchar(12)  comment "母亲",
s_id int unsigned ,
constraint s_id_key foreign key (s_id) 
references student(id) 
on update cascade on delete cascade
)charset utf8;

desc stu_family;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| father | varchar(12)      | YES  |     | NULL    |                |
| mother | varchar(12)      | YES  |     | NULL    |                |
| s_id   | int(10) unsigned | YES  | MUL | NULL    |                |
+--------+------------------+------+-----+---------+----------------+

-- ---------------------------------------------------------------------
 -- 创建学科表
create table subject(
id tinyint unsigned  primary key auto_increment,
subject varchar(12) unique not null
)charset utf8;

desc subject;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| subject | varchar(12)         | NO   | UNI | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+

-- ---------------------------------------------------------------------------

-- 创建教师表
create table teacher(
id int unsigned primary key auto_increment,
number int unique key not null comment "学号",
name varchar(12) not null comment "姓名",
sex enum("men","women") not null comment "性别",
age int default 18 not null comment"年龄",
subject varchar(12)
)charset utf8;

desc teacher;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| number  | int(11)             | NO   | UNI | NULL    |                |
| name    | varchar(12)         | NO   |     | NULL    |                |
| sex     | enum('men','women') | NO   |     | NULL    |                |
| age     | int(11)             | NO   |     | 18      |                |
| subject | varchar(12)         | YES  |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+

-- ---------------------------------------------------------------------------

-- 创建班级表
create table class(
id smallint unsigned auto_increment,
class varchar(12) not null unique comment "班级",
grade varchar(12) not null comment "年级",
primary key(id)
)charset utf8;

desc class;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| class | varchar(12)          | NO   | UNI | NULL    |                |
| grade | varchar(12)          | NO   |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
-- ---------------------------------------------------------------------------

-- 创建课程表
create table schedule(
id int unsigned auto_increment,
s_day enum("Mon","Teus","Wed","Thur","Fri") not null,
s_order enum("第一节","第二节","第三节","第四节") not null comment "第几节课",
s_time enum("8:00-9:30","10:00-11:30","2:30-4:00","4:30-6:00") not null,
course varchar(12) not null,
teacher_id  int unsigned, 
class_id smallint unsigned,
primary key(id),
constraint teacher_key foreign key (teacher_id ) references teacher(id) on update cascade on delete set null,
constraint class_key foreign key (class_id ) references class(id) on update cascade on delete set null
 ) charset gbk;