-- 如果存在名為school的數(shù)據(jù)庫就刪除它
drop database if exists school;

-- 創(chuàng)建名為school的數(shù)據(jù)庫并指定默認的字符集為utf-8
create database school default charset utf8;

-- 切換到school數(shù)據(jù)庫上下文環(huán)境
use school;

-- 創(chuàng)建學生表
create table tb_student
(
stuid int not null,
stuname varchar(20) not null,
stusex bit default 1,
stubirth date,
primary key (stuid)
);



-- 修改學生表
alter table tb_student add column stuaddr varchar(255);
alter table tb_student change column stuaddr stuaddr varchar(511);
alter table tb_student drop column stuaddr;



-- 修改學生表添加學院編號(colid)列
alter table tb_student add column colid int;

-- 修改學生表添加外鍵約束(參照完整性)
alter table tb_student add constraint fk_student_colid foreign key (colid) references tb_college (colid);

-- 更新學生表為學生指定所屬學院
update tb_student set colid=1 where stuid between 1001 and 1006;
update tb_student set colid=2 where stuid in (1007, 1008);
update tb_student set colid=3 where stuid=1009;



-- 創(chuàng)建老師表
create table tb_teacher
(
teaid int not null comment '工號',
teaname varchar(20) not null comment '姓名',
teasex bit default 1 comment '性別',
teabirth date comment '生日',
teatitle varchar(10) default '助教' comment '職稱',
colid int not null comment '所在學院'
-- primary key (teaid),
-- foreign key (colid) references tb_college (colid)
);

alter table tb_teacher add constraint pk_teacher_teaid primary key (teaid);

alter table tb_teacher add constraint fk_teacher_colid foreign key (colid) references tb_college (colid);