# 统计所有年龄为19岁的学生数量
select count(*) from t_student_info where age = 19;
# 统计学校男女老师的数量
select count(gender), gender from t_teacher group by gender;
联表查询:
# 查询分数表中所有年龄大于等于18岁的学生的详细(姓名,年龄,性别)信息和分数以及对应的课程
select stu.sname, stu.age, stu.sex, score.score, cou.cname, cou.course_no
from t_student_info stu join t_score score on stu.stuno = score.stuno
join t_course cou on score.course_no = cou.course_no
where stu.age >= 18;
# 查询所有年龄大于20岁的 理工农学院的老师信息
alter table t_teacher add column age int(3) not null comment '年龄';
select * from t_teacher join t_department on t_teacher.department = t_department.dept_no where age > 20;
汇聚查询:
# 汇聚查询
# 将老师和学生的信息进行聚合显示
select tname, age, gender from t_teacher union select sname, age, sex from t_student_info limit 100;