-- 查询所有学生信息
select * from tb_student;
-- 查询所有老师信息
select * from tb_teacher;
-- 查询所有课程信息
select * from tb_course;
-- 查询所有课程名称及学分(投影和别名)
select couname, credit from tb_course;
-- 查询所有学生的姓名和性别(投影和别名)
select stuname as 姓名, sex as 性别 from tb_student;
select stuname as 姓名, case sex when 1 then '男' else '女' end as 性别 from tb_student;
select stuname as 姓名, if(sex, '男', '女') as 性别 from tb_student;
-- 查询老师的姓名和职称 concat() 将字符串连接起来
select concat(teaname, title) as 全称 from tb_teacher;
-- 查询所有女学生的姓名和出生日期(筛选)
select stuname, birth from tb_student where sex=0;
-- 查询女学生的姓名和年龄 floor()向下取整 / ceil()向上取整 /datediff()计算时间函数
select stuname as 姓名, floor(datediff(now(), birth)/365) as 年龄 from tb_student where sex=0;
-- 查询所有80后学生的姓名、性别和出生日期(筛选)
select stuname, sex, birth from tb_student where birth>='1980-1-1' and birth<='1989-12-31';
select stuname, sex, birth from tb_student where birth between '1980-1-1' and '1989-12-31';
-- 查询姓”杨“的学生姓名和性别(模糊)
select stuname, sex from tb_student where stuname like '杨%';
-- 查询姓”杨“名字两个字的学生姓名和性别(模糊)
select stuname, sex from tb_student where stuname like '杨_';
-- 查询姓”杨“名字三个字的学生姓名和性别(模糊)
select stuname, sex from tb_student where stuname like '杨__';
-- 查询名字中有”不“字或“嫣”字的学生的姓名(模糊)
select stuname from tb_student where stuname like '%不%' or stuname like '%嫣%';
-- 查询没有录入家庭住址的学生姓名(空值)
select stuname from tb_student where addr is null;
-- 查询录入了家庭住址的学生姓名(空值)
select stuname from tb_student where addr is not null;
-- 查询学生选课的所有日期(去重)
select distinct seldate from tb_record;
-- 查询学生的家庭住址(去重)
select distinct addr from tb_student where addr is not null;
-- 查询男学生的姓名和生日按年龄从大到小排列(排序)
select stuname, birth, floor(datediff(now(), birth)/365) as age from tb_student where sex=1 order by age desc;
select stuname, birth from tb_student order by sex asc, birth desc;
-- max / min / sum / avg / count
-- 查询年龄最大的学生的出生日期(聚合函数)
select min(birth) from tb_student;
-- 查询年龄最小的学生的出生日期(聚合函数)
select max(birth) from tb_student;
-- 查询男女学生的人数(分组和聚合函数)
select if(sex, '男', '女') as 性别, count(stuid) as 人数 from tb_student group by sex;
select '男' as 性别, count(stuid) as 人数 from tb_student where sex=1
union
select '女' as 性别, count(stuid) as 人数 from tb_student where sex=0;
-- 统计学生的籍贯和人数
select addr, count(stuid) as total from tb_student where addr is not null group by addr order by total desc;
-- 查询课程编号为1111的课程的平均成绩(筛选和聚合函数)
select count(cid) from tb_record where cid=1111;
select count(score) from tb_record where cid=1111;
select avg(score) from tb_record where cid=1111;
-- 查询学号为1001的学生所有课程的平均分(筛选和聚合函数)
select avg(score) from tb_record where sid=1001;
-- 查询每个学生的学号和平均成绩(分组和聚合函数)
select sid, avg(score) from tb_record group by sid;
-- 查询平均成绩大于等于90分的学生的学号和平均成绩
-- where子句代表分组以前的筛选,所以写在group by子句的前面
-- having子句代表分组以后的筛选,所以写在group by子句的后面
select sid, avg(score) as avgscore from tb_record group by sid having avgscore>=90 order by avgscore desc;
-- 查询年龄最大的学生的姓名(子查询/嵌套查询)
-- 把一个查询的结果作为另外一个查询的一部分来使用
select stuname from tb_student where birth=(
select min(birth) from tb_student);
-- 查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)
select stuname from tb_student where stuid in (select sid from tb_record group by sid having count(sid)>2);
-- 查询学生姓名、课程名称以及成绩(连接查询)
-- 如果没有连接条件会形成笛卡尔积
select stuname, couname, score from tb_student, tb_course, tb_record where stuid=sid and couid=cid;
select stuname, couname, score from tb_student inner join tb_record on stuid=sid inner join tb_course on couid=cid;
-- 查询选课学生的姓名和平均成绩(子查询和连接查询)
select stuname, avgscore from tb_student t1 inner join
(select sid, avg(score) as avgscore from tb_record group by sid) t2 on stuid=sid;
-- 查询每个学生的姓名和选课数量(左外连接和子查询)
-- left outer join 左表(写在前面的表)不满足连表条件的列用null进行填充 / right outer join 右表(写在后面的表)不满足连表条件的列用null填充 / full outer join 全外连接(MySQL不支持)
select stuname, ifnull(total, 0) as total from tb_student t1 left join (select sid, count(sid) as total from tb_record group by sid) t2 on stuid=sid order by total desc limit 5 offset 3;
-- limit 3, 5;