mysql数据库CRUD-3

本文主要介绍select相关内容

问题：查找年龄最大最小学生，并显示名字

查询语句

查询所有学生信息

select * from tb_student;
select * from tb_course;

笛卡尔积

select * from tb_student, tb_course;

投影和别名:

给字段别名

select sname 姓名, ssex 性别 from tb_student;

#####给查询出来的记录别名

select sname as 姓名, case ssex when 1 then '男' else '女' end as 性别 from tb_student;

​ 这种写法通用型更强，适合多种数据库

select sname as 姓名, if(ssex, '男', '女') as 性别 from tb_student;

​ if是mysql数据库独有的写法

筛选:

= / <> / > / >= / < / <= / is null / is not null

between and / and / or / not

查询所有女学生的姓名和出生日期

1. 查询所有的女学生（筛选条件）

select sname, sbirth from tb_student where ssex=0;

2. 查询学分大于两个学分的课程

select courseid, cname from tb_course where ccredit>2;

范围筛选:

查询所有80后学生的姓名、性别和出生日期

1. and

select sname, ssex, sbirth from tb_student 

where sbirth>='1980-1-1' and sbirth<='1989-12-31';

2. between

select sname, ssex, sbirth from tb_student 

where sbirth between '1980-1-1' and '1989-12-31';

模糊查询:

通配符(wildcard): % / _

• %：代表0个或任意多个字符
  • %x%：代表包含x这个字符的项

• _：精准的匹配一个字符

  查询姓杨的学生姓名和性别
  

回顾：精确查找杨过

select * from tb_student where sname='杨过';

#####模糊查询姓杨的学生

select * from tb_student where sname like '杨%';

模糊查询: 查询姓杨名字总共两个字的学生的姓名

select * from tb_student where sname like '杨_';

#####模糊查询: 查询姓杨名字总共三个字的学生的姓名

select * from tb_student where sname like '杨__';

#####模糊查询: 查询名字中有杨字的学生的姓名(模糊)

select * from tb_student where sname like '%杨%';

#####使用正则表达式

select stuid, sname, ssex from tb_student where sname regexp '^[张李].+';

^：开始匹配位置

.：任意字符

+：一个或多个

多条件和空值处理:

查询没有录入生日或家庭住址的学生姓名
在判断一个列是否为null的时候不能用=或<>而要使用is或者is not

select * from tb_student where sbirth is null or saddr is null;

判断为空值：is null

判断不为空值：is not null

去重查询:

​ 查询学生的籍贯

查询出所有未清洗的学生的籍贯

select saddr from tb_student;

去掉空值

select saddr from tb_student where saddr is not null;

去重（disdinct）

select distinct saddr from tb_student where saddr is not null;

排序

#####asc(升序-从小到大) / desc(降序-从大到小)

第一排序关键字：第一个给出的排序的字段，首要保证的排序依据，如果第一排序关键字排不出来再按照第二排序关键字排序

把女生排前面

select * from tb_student order by ssex;

默认是asc升序排列，因为女生的0比男生的1小，所以order by就是女生排列在前

把男生排前面

select * from tb_student order by ssex desc;

desc： 下降，降序排列

#####多个排序关键字

select * from tb_student order by ssex asc, sbirth desc;

排序+筛选：筛选要写在排序的前面

1. 查询男学生的姓名和生日按年龄从大到小排列

select sname, sbirth from tb_student where ssex=1 order by sbirth;

2. 查询所有录入了家庭住址的男学生的姓名、出生日期和家庭住址按年龄从小到大排列

select sname, sbirth, saddr from tb_student where saddr is not null and ssex=1 order by sbirth desc;

聚合函数

数据库都有的五个聚合函数：max() / min() / sum() / avg() / count()

MySQL特有函数: now() / if()

查询年龄最大的学生的出生日期

select min(sbirth) from tb_student;
select max(sbirth) from tb_student;

查询课程编号为1111的课程的平均成绩

select cid, avg(score) from tb_sc where cid=1111;

查询学号为1001的学生所有课程的平均成绩

select sid, avg(score) from tb_sc 
where sid=1001;

查询每门课程的平均成绩

select cid, avg(score) from tb_sc group by cid;

聚合函数是自动排除空值的

查询平均成绩不及格的课程

where子句构造的筛选是分组以前的筛选
如果希望对分组以后的数据进行筛选那么要写having子句而不是where子句

表明这个分组拥有的进行筛选

select cid, avg(score) as avgScore from tb_sc group by cid having avgScore<80;

分组查询

查询所有学生人数

select count(stuid) from tb_student;

查询男学生人数

select count(stuid) from tb_student where ssex=1;

查询男女学生的人数

select if(ssex, '男', '女')as 性别, count(*)as 人数 from tb_student group by ssex;

经验: 在使用group by分组时如果不希望执行默认的排序操作
可以在分组后使用order by null来避免默认的排序操作提升查询性能

select if(ssex, '男', '女') as 性别, count(*) as 人数 
from tb_student group by ssex order by null;

#####筛选(where) > 分组(group by) > 筛选(having) > 排序(order by)>分页

筛选有家庭住址的男女学生的人数，并按男学生在前女学生在后排序

select if(ssex, '男', '女')as 性别, count(*)as 人数 from tb_student where saddr is not null group by ssex order by ssex desc;

#####分组查询和空值处理: 查询每个学生的学号和平均成绩

select sid, avg(score) from tb_sc 
group by sid having avg(score)<60;

子查询

子查询就是：查询作为另一个查询的一部分使用

#####查询年龄最大的学生的姓名

select sname, sbirth from tb_student
where sbirth=(select min(sbirth) from tb_student);

#####查询选了两门以上的课程的学生姓名

select sname from tb_student 
where stuid in (
select sid from tb_sc
group by sid having count(sid)>2)；

#####查询选了两门以上的课程的男学生姓名

select sname from tb_student 
where stuid in (
select sid from tb_sc
group by sid having count(sid)>2) and ssex=1;

我们建议先写可以去掉部分对象的筛选条件写在右边，因为在sql中写在右边的条件先执行

####连接查询

在使用到多张表时需要使用连接查询，最常见的就是自然连接，即用表之间的外键连接起来

#####查询学生姓名、所选课程名称和成绩

select sname, cname, score 
from tb_sc, tb_student, tb_course
where sid=stuid and cid=courseid;

利用衍生表（临时表）查询

#####子查询：查询选课学生的姓名和平均成绩

最好是从里往外写，先写子查询

两表连查

select sname, avgScore from tb_student,
(select sid, avg(score) as avgScore from tb_sc 
group by sid) t
where stuid=sid;

​ 必须给衍生表别名，给表别名一般不写as，给列别名需要写上

select sna me, avgScore from tb_student t1,
(select sid, avg(score) as avgScore from tb_sc 
group by sid) t2
where stuid=sid;

#####查询每个学生的姓名和选课数量

select sname, sidCount from tb_student,
(select sid, count(sid) as sidCount from tb_sc group by sid) t
where stuid=sid;

​ 在写列名是如果有重名的冲突，可以在前面加上表名

#####内连接:只有满足条件的才查上来

select sname, avgScore from tb_student t1
inner join
(select sid, avg(score) as avgScore from tb_sc 
group by sid) t2
on stuid=sid;

#####左外连接: 左表(写在前面的表)不满足连表条件的记录也要查询出来

select sname, ifnull(total, 0) as total from tb_student t1
left outer join
(select sid, count(sid) as total from tb_sc 
group by sid) t2 
on stuid=sid;

select sname, total from 
(select sid, count(sid) as total from tb_sc
group by sid)t2 left outer join tb_student t1
on stuid=sid;

#####右外连接: 右表(写在后面的表)不满足连表条件的记录也要查询出来

select sname, ifnull(total, 0) as total from
(select sid, count(sid) as total from tb_sc 
group by sid) t2 right outer join tb_student t1
on stuid=sid;

#####全外连接: 左右两张表不满足连表条件都要查询出来

​ MySQL不支持使用全外链接(full outer join)

在表与表之间有逗号，内链接，左右连接不写逗号；连表条件用on表示

分页查询

不同的数据库在这一部分上非常不同，此处只介绍mysql数据库的做法，分页查询可以让效率更高

select sid, sname, cname, score from 
tb_sc inner join tb_student on stuid=sid
inner join tb_course on cid=courseid
limit 5;

limit 5:只显示5条

limit 5 offset 0:偏移量是0，从第一条开始

limit 5 offset 5：从第六条开始，查5条

limit 0, 5:从第一条开始只显示5条