数据库sql

由于将服务器上的表迁移至本机，视图会不能运行，

所以需要将数据库授予权限，授予权限

grant all privileges on     *.* to     ''@"%" identified by '';

更新权限列表

flush privileges;

添加课程：

insert into course select left(md5("山东大学-XXX-中心校区 明德楼，B座5楼505-20180505190000-20180505210000-数据结构"),10),
"数据结构",teacher_id,classroom_id,"20180505190000","20180505210000" from teacher,classroom 
where teacher.school_id="山东大学" and teacher_name="XXX" 
and classroom.school_id="山东大学" and classroom_name="明德楼，B座5楼505";

创建emotion_avg_all as视图

create view emotion_avg_all as(
select avg(happiness) happiness_avg,avg(fear) fear_avg,avg(surprise) surprise_avg,avg(anger) anger_avg,
avg(disgust) disgust_avg,avg(neutral) neutral_avg,avg(sadness) sadness_avg  from emotion
);

查看emotion_avg_all视图

select * from emotion_avg_all as;

创建emotion_avg_teacherid视图

create view emotion_avg_teacherid as(  
select teacher_id,teacher_name,avg(happiness) happiness_avg,avg(fear) fear_avg,avg(surprise) surprise_avg,
avg(anger) anger_avg,avg(disgust) disgust_avg,avg(neutral) neutral_avg,avg(sadness) sadness_avg 
from emotion2 group by teacher_id);

查看emotion_avg_teacherid视图

select * from emotion_avg_teacherid;

创建emotion_avg_teachername视图

create view emotion_avg_teachername as(  
select teacher_id,teacher_name,avg(happiness) happiness_avg,avg(fear) fear_avg,avg(surprise) surprise_avg,
avg(anger) anger_avg,avg(disgust) disgust_avg,avg(neutral) neutral_avg,avg(sadness) sadness_avg 
from emotion2 group by teacher_name);

查看emotion_avg_teachername视图

select * from emotion_avg_teachername;

创建emotion_avg_coursename视图

create view emotion_avg_coursename as(  
select course_name,teacher_name,avg(happiness) happiness_avg,avg(fear) fear_avg,avg(surprise) surprise_avg,
avg(anger) anger_avg,avg(disgust) disgust_avg,avg(neutral) neutral_avg,avg(sadness) sadness_avg 
from emotion2 group by course_name);

查看emotion_avg_coursename视图

select * from emotion_avg_coursename;

创建emotion_avg_courseid视图

create view emotion_avg_courseid as(  
select course_id,course_name,teacher_name,avg(happiness) happiness_avg,avg(fear) 
fear_avg,avg(surprise) surprise_avg,avg(anger) anger_avg,avg(disgust) disgust_avg,avg(neutral) neutral_avg,avg(sadness) sadness_avg 
from emotion2 group by course_id);

创建con_num_avg视图

create view con_num_avg as (
 select avg(con_num) con_num_avg from con2 
);

创建con_avg_teacherid视图

create view con_avg_teacherid as ( 
select teacher_id,teacher_name,avg(con_num) con_num_avg from con2 group by  teacher_id
 );

创建con_avg_courseid视图

create view con_avg_courseid as ( 
select teacher_id,teachername,course_id,course_name,avg(con_num) con_num_avg 
from con2 group by  course_id 
);

创建con_avg_coursename视图

create view con_avg_coursename as (
 select teacher_id,teacher_name,course_id,course_name,avg(con_num) con_num_avg 
from con2 group by  course_name 
);

创建word_count_teacherid视图

create view word_count_teacherid as (
 select count(*) from zanghua group by teacher_id 
);

创建word_count_coursename视图

create view word_count_coursename as(
 select count(*) from zanghua group by course_name 
);

创建word_count_courseid视图

create view word_count_courseid as (
 select count(*) from zanghua group by course_id 
);

创建con_avg_minute视图

create view con_avg_minute as (
 SELECT str_to_date(concat(left(date_format(con_time,"%Y-%m-%d %H:%i:"),16),":00"),"%Y-%m-%d %H:%i:%s") AS con_time2,  
avg(con_num) con_num_avg, teacher_id, teacher_name, course_id, course_name from con2 
group by con_time2,teacher_id,course_id
);

create view tmp_emotion_course as (
select emotion_time,emotion.classroom_id,happiness,fear,surprise,anger,disgust,neutral,sadness,
teacher_id,course_name,course_id from emotion ,course where emotion_time between start_time and end_time and emotion.classroom_id=emotion.classroom_id
);

create view emotion2 as (
select teacher_id , teacher_name,course_name ,course_id,emotion_time ,
 classroom_id , happiness , fear , surprise , anger , disgust , neutral , sadness  
from tmp_emotion_course natural join teacher
);

create view tmp_concentrate_course as (
select con_time,con.classroom_id,con_num,teacher_id,course_name,course_id 
from con ,course 
where con_time between start_time and end_time and con.classroom_id=con.classroom_id
);

create view con2 as (
select teacher_id , teacher_name,course_name ,course_id,con_time , classroom_id , 
con_num  from tmp_concentrate_course natural join teacher
);

insert into word values(
md5("a588f531f3a37921d72323a58483d96b-20170512120000-你奶奶的"),
"a588f531f3a37921d72323a58483d96b","20170512120000","你奶奶的"
);

insert into course values(md5("山东大学-XXX老师-软件园5105-2018-03-07-16:00-18:00-2"),
"数学建模",NULL,NULL,"20170307160000","20170307180000");

insert into teacher values(md5("清华大学-XXX"),"XXX",md5("清华大学"),"软件学院");

insert into course
select md5("山东大学-XXX-软件园-食堂-2018-05-12-16:00-18:00-2"),"线性代数",
teacher_id,classroom_id,"20180505160000","20180505180000" 
from (select *  from school where school_name="山东大学") as s natural join 
(select *  from  classroom where classroom_name="软件园-食堂") as c natural join 
(select * from teacher where teacher_name="XXX") as t;

insert into con values(
md5("20180505160100-55.5-a588f531f3"),"20180505160100","a588f531f3",55.5
);