截图

 

前端传入id    varchar类型的id值，考虑到17级学生学号为11位，后续年级的学生的学号可能会突破int值的限制，所以使用varchar类型

 

 

1、t_certificate 证书表，由id查四六级成绩，查询条件为

（1）成绩>425的四六级证书

  select ce_student_id as 学号,ce_student_name as 学生姓名,

ce_name as 考试科目,ce_score as 考试成绩

from t_certificate

where ce_student_id = "1220106204" and

ce_name like "外语大学英语_级CET_" and ce_score>=425 ;

 

（2）成绩为合格的证书

   select ce_student_id as 学号,ce_student_name as 学生姓名,

ce_name as 考试科目,ce_score as 考试成绩

from t_certificate

where ce_student_id = "13200212108" and

ce_score = "合格" ;

 

（3）成绩为及格的证书

select ce_student_id as 学号,ce_student_name as 学生姓名,

ce_name as 考试科目,ce_score as 考试成绩

from t_certificate

where ce_student_id = "14200119104" and

ce_score = "及格" ;

 

 

2、t_competition  竞赛表

考虑到部分数据没有id,应该先从学生基本表中查名字，再由名字查竞赛名称

 

竞赛名称=co_name+co_level+co_awards  以第一条为例，则是大学生集成电路设计大赛国家级三等奖

select st_name as 学生姓名,

concat_ws('',co_name,co_level,co_awards) as 奖项

from t_competition,t_student

where co_student_name like

 '%'|| (select st_name

from t_student

where st_id = "14200107126")||'%' ;

 

 

3、t_excellent_student 按照id查

 

 

单项名称为ex_year+ex_honor

 

select  ex_student_name as 姓名,

ex_student_id  as 学号,

concat_ws('',ex_year,'年',ex_honor) as 荣誉

from t_excellent_student

where ex_student_id = "14200141111" ;

 

 

4、t_innovation_train 创新训练表，俗称科研立项表

 

 

按照名字来查，使用模糊查询

单项为in_year+in_category+in_name

 

select  t_student.st_name,

concat_ws('',in_year,'年',in_category,':',in_name) as 科研项目

from t_innovation_train,t_student

where in_students like

 '%'|| (select st_name

from t_student

where st_id = "14200135118")||'%' ;

 

 

5、t_party_member 党员表

 

 

按名字来查

单项为pa_state+“党员”

 

select pa_student_name as 学生姓名,

concat_ws('',pa_state,'党员') as 党员状态

from t_party_member

where pa_student_name="杨磊";

 

 

6、t_post_graduate 考研表

 

 

按照名字来查

单项为 po_new_school+“研究生”

 

select po_student_name as 学生姓名,

concat_ws('',po_new_school,'研究生') as 研究生状况

from t_post_graduate

where po_student_name="何勇";

 

 

7、t_push_excellent  推优表

 

 

按照名字来查，若查询到数据

单项为2018.6.14+“已被推优”

 

select pu_student_name as 学生姓名,

concat_ws('',pu_time,'已被推优') as 推优状况

from t_push_excellent

where pu_student_name="王辉";

 

 

8、t_scholarship 奖学金表

 

 

按照学号来查。可能一个同学不止一条数据

单项为sc_year+“年获得”+sc_award

如果sc_award为三个字则sc_award=sc_award+”学金”

否则 sc_award=sc_award

 

 备注:返回字符串类型的数组

 

select sc_student_name as 学生姓名,

concat_ws('',sc_year,'年获得',sc_awards,'学金') as 荣誉

from t_scholarship

where sc_awards like "___"

and sc_student_id = "16200143130"

 

union

 

select sc_student_name as 学生姓名,

concat_ws('',sc_year,'年获得',sc_awards) as 荣誉

from t_scholarship

where  sc_awards like "____%" and

sc_student_id = "16200143130";