MySQL 查询操作

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;