tb_college
tb_course
tb_score
tb_student
tb_teacher
查询所有学生信息
select * from tb_student
查询所有课程名称及学分(投影和别名)
select cname as 课程名称，credit as 学分 from tb_course;
查询所有女学生的姓名和出生日期(筛选)
select sname,birth from tb_student where gender=0;
查询所有80后学生的姓名、性别、和出生日期(筛选)
select sname 姓名,if(gender,'男','女') as 性别,birth 生日
from tb_student
where birth between '1980-1-1' and '1989-12-31';
select sname 姓名,case gender when 1 then '男' else '女' end as 性别,birth 生日
from tb_student
where birth between '1980-1-1'and '1989-12-31';
查询姓杨的学生的名字和性别(模糊)
select sname,gender from tb_student where sname like '杨%'
查询姓”杨“名字两个字的学生姓名和性别(模糊)
select sname,gender from tb_student where sname like '杨_';
查询姓”杨“名字三个字的学生姓名和性别(模糊)
select sname,gender from tb_student where sname like '杨__';
查询名字中有”不“字或“嫣”字的学生的姓名(模糊)
select sname,gender from tb_student where sname like'%不%' or
sname like '%嫣%'
查询没有录入家庭住址的学生姓名(空值)
select sname from tb_student where addr is null;
查询录入了家庭住址的学生的姓名(空值)
select sname from tb_student where addr is not null;
查询学生选课的所有日期(去重)
select distinct seldate from tb_score;
查询学生家庭住址(去重)
select distinct addr from tb_student where addr is not null;
查询男学生的姓名和年龄按年龄从大到小排序(排序)
select sname as 姓名，year(now())-year(birth) as 年龄
from tb_student where gender=1 order by birth;
max() / min() / sum() / avg() / count()
查询年龄最大的学生的出生日期(聚合函数)
select min(birth) from tb_student;
查询年龄最小的学生的出生日期(聚合函数)
select max(birth) from tb_student;
查询男女学生的人数(分组和聚合函数)
select if(gender,'男','女'),count(stuid) from tb_student group by gender;
查询课程编号为1111的课程的平均成绩(筛选和聚合函数)
select cid,avg(mark) from tb_score where cid=1111;
查询学号为1001的学生的所有课程的平均分
select sid,avg(mark) from tb_score where sid=1001;
查询每个学生的学号和平均成绩
select sid,avg(mark) from tb_score group by sid;
查询平均成绩大于等于90分的学生的学号和平均成绩
select sid,avg(mark) from tb_score group by sid having 平均分>90;
子查询 - 在一个查询中又使用到了另外一个查询的结果
查询年龄最大的学生和姓名(子查询)
select sname from tb_student
where birth=(select min(birth) from tb_student);
查询年龄最大的学生的姓名和年龄
select sname,year(now())-year(birth) from tb_student
where birth=(select min(birth) from tb_student);
查询选了两门以上课程的学生姓名
select sname from tb_student where stuid in
(
select sid from tb_score group by sid having count(sid)>2
);
连接查询（联结查询/联接查询)
查询学生的姓名和所在学院的名称
select sname,collname
from tb_student t1,tb_collname t2
where t1.collid=t2.collid;
select sname,collname from tb_student t1
inner join tb_college t2
on t1.collid=t2.collid;
如果查询多个表没有任何限制条件那么就会产生笛卡尔集。
select sname, collname from tb_student,tb_college;
查询学生姓名、课程名称以及成绩
select sname,cname,mark
from tb_student,tb_course,tb_score
where sid=stuid and cid=couid;
-------用一张中间表来连接
select sname,cname,mark from tb_student
inner join tb_score on sid=stuid
inner join tb_course on cid=couid;
查询选课学生的姓名和平均成绩
select sname,avgMark from tb_student t1
inner join(select sid,avg(mark) as avgMark from tb_score group by sid) t2
on stuid=sid;
select sname,avgMark from tb_student t1 left join (select sid,avg(mark) as avgMark from tb_score group by sid) t2
on stuid=sid;
查询每个学生的姓名和选课数量(左外连接和子查询)
select sname,ccid from tb_student t1 left join
(select count(cid) as ccid,sid from tb_score group by sid) t2
on stuid=sid;
