--1.创建准备数据库
--drop database if exists edw;
create database if not exists edw;
alter database edw default character SET utf8 COLLATE utf8_general_ci;

use edw;

--创建学生表
drop table if exists ods_stu_entity;
create table if not exists ods_stu_entity(
id int primary key auto_increment,
stu_id varchar(64) comment ‘学号‘  unique,
name varchar(64) comment ‘姓名‘,
sex varchar(16) comment ‘性别‘ default ‘男‘,
age int  comment ‘年龄‘ check(id>0 and id<150),
grade varchar(16) comment ‘年级‘ ,
address varchar(64) comment ‘家庭地址‘
)ENGINE=InnoDB default CHARSET=utf8;


--创建学生成绩表
drop table if exists ods_stu_score;
create table if not exists ods_stu_score(
id int primary key auto_increment,
stu_id varchar(64) comment ‘学号‘  unique,
name varchar(64) comment ‘姓名‘,
grade varchar(16) comment ‘年级‘ ,
chinese float comment ‘语文成绩‘ default 0,
math    float comment ‘数学成绩‘ default 0,
english float comment ‘英语成绩‘ default 0
)ENGINE=InnoDB default CHARSET=utf8;

--触发器
--当插入学生信息之后,触发器往成绩表中插入数据
drop trigger if exists trigger_ods_stu_score;
create trigger trigger_stu_score 
after insert on ods_stu_entity for each row
begin
    insert into ods_stu_score (stu_id,name,grade) 
    values (new.stu_id,new.name,new.grade);
end;

--测试插入一条学生数据
insert into ods_stu_entity(stu_id,name,sex,age,grade,address) values(‘120510016‘,‘Jim‘,‘男‘,‘27‘,‘大一‘,‘江西省上饶市婺源县‘);
insert into ods_stu_entity(stu_id,name,sex,age,grade,address) values(‘120510017‘,‘Jack‘,‘男‘,‘26‘,‘大一‘,‘浙江省杭州市西湖区‘);
insert into ods_stu_entity(stu_id,name,sex,age,grade,address) values(‘120510018‘,‘Lucy‘,‘女‘,‘25‘,‘大一‘,‘辽宁省大连市沙河口区‘);


--查看学生表
select * from ods_stu_entity;

--查看成绩表中是否有数据
select * from ods_stu_score;