sql编写

sql编写以及sql视图的编写

增加了抬头率等表格

creatw table taitoulv 
(
  taitou_id varchar(10) NOT NULL,
  taitou_time datetime DEFAULT NULL,
  classroom_id varchar(10) DEFAULT NULL,
  taitoulv double DEFAULT NULL,
  PRIMARY KEY (taitou_id)
);

create view emotion_avg_teacherid_rank as 
(
	select emotion_avg_teacherid.teacher_id AS teacher_id,emotion_avg_teacherid.teacher_name AS teacher_name,
	(
		emotion_avg_teacherid.happiness_avg + emotion_avg_teacherid.surprise_avg
	) AS positive from emotion_avg_teacherid 
	order by 
	(
		emotion_avg_teacherid.happiness_avg + emotion_avg_teacherid.surprise_avg
	) 
	desc
);

create view rank_teacherid AS 
(
		select tmp_emotion_join_word.teacher_id AS teacher_id,tmp_emotion_join_word.teacher_name AS 		
		teacher_name,tmp_emotion_join_word.positive AS positive,tmp_emotion_join_word.count AS count,
		con_avg_teacherid.con_num_avg AS con_num_avg from (tmp_emotion_join_word left join con_avg_teacherid on
		(
			tmp_emotion_join_word.teacher_id = con_avg_teacherid.teacher_id
		)
);

create view rank_positive as 
(
	select tmp_emotion_join_word.teacher_id as teacher_id,tmp_emotion_join_word.teacher_name AS teacher_name,
	tmp_emotion_join_word.positive AS positive,tmp_emotion_join_word.count AS count,
	con_avg_teacherid.con_num_avg AS con_num_avg from 
		(
			tmp_emotion_join_word left join con_avg_teacherid on
			(
				tmp_emotion_join_word.teacher_id = con_avg_teacherid.teacher_id)
			)
);

create view tmp_teacherid_rank as 
(
	select tmp_emotion_join_word.teacher_id AS teacher_id,
		tmp_emotion_join_word.teacher_name AS teacher_name,tmp_emotion_join_word.positive AS positive,
		tmp_emotion_join_word.count AS count,con_avg_teacherid.con_num_avg AS con_num_avg 
		from (
			tmp_emotion_join_word left join con_avg_teacherid on
				(
					tmp_emotion_join_word.teacher_id = con_avg_teacherid.teacher_id
				)
		)
);

create view tmp_teacher_join_emotion AS (
	select teacher.teacher_id AS teacher_id,teacher.teacher_name AS teacher_name,
	emotion_avg_teacherid_rank.positive AS positive from 
	(
		teacher left join emotion_avg_teacherid_rank on
			(
				teacher.teacher_id = emotion_avg_teacherid_rank.teacher_id
			)
	)
);

select teacher.teacher_name,teacher.teacher_id,tmp_taitoulv_course.course_id,
	tmp_taitoulv_course.course_name,taitoulv 
	from tmp_taitoulv_course join teacher on 
	tmp_taitoulv_course.teacher_id=teacher.teacher_id;

create view taitoulv_course_teacher as (
	select 	teacher.teacher_name,teacher.teacher_id,tmp_taitoulv_course.course_id,
	tmp_taitoulv_course.course_name,taitoulv,classroom_id 
	from tmp_taitoulv_course join teacher on tmp_taitoulv_course.teacher_id=teacher.teacher_id
);

create view taitoulv_teacherid as (
	select teacher_id,teacher_name,avg(taitoulv) taitoulv_avg 
	from taitoulv_course_teacher group by teacher_id
);

create view taitoulv_coursename as ( 
	select course_name,avg(taitoulv) taitoulv_avg,teacher_id,teacher_name 
	from taitoulv_course_teacher group by course_name
);

create view taitoulv_avg_courseid as ( 
	select course_id,course_name,avg(taitoulv) 
	taitoulv_avg,teacher_name,teacher_id 
	from taitoulv_course_teacher group by course_id
);

create view taitoulv_avg_all as ( 
	select avg(taitoulv) taitoulv_avg 
	from taitoulv_course_teacher
);

create view taitou_minute as ( 
	select str_to_date(concat(left(taitou_time,16),":00"),'%Y-%m-%d %H:%i:%s') taitoulv_time ,
	avg(taitoulv) avg_taitoulv,teacher_id,course_id,course_name,teacher_name 
	from taitoulv_course_teacher group by taitoulv_time ,course_id,teacher_id 
);

create view teacher_id_rank as ( 
	select tmp_teacherid_rank.teacher_id,tmp_teacherid_rank.teacher_name,
	tmp_teacherid_rank.positive,tmp_teacherid_rank.count,tmp_teacherid_rank.con_num_avg,
	taitoulv_avg_teacherid.taitoulv_avg from tmp_teacherid_rank left join taitoulv_avg_teacherid on 		
	tmp_teacherid_rank.teacher_id=taitoulv_avg_teacherid.teacher_id 
);

从数据库中导出

source /home/zibojia/shixun2-9.sql

导入数据库

mysql -uroot -proot
create database shixun2;

use shixun2;

赋予权限

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

更新

flush privileges;

导入

source /home/users/zibojia/shixun2-9.sql