DQL[非常重要]
DQL 主要指查询语句,有查询单表数据,也有查多表数据表,
单表
查询
基本查询
条件查询
模糊查询
排序查询
聚合查询
去重查询
分组查询
限制查询
1、 数据准备
将发的stu.sql导入到MySql中
2、 基本查询
select 字段1,字段2,... from 表名;
查询返回的是一张
虚拟表
,查询对原表数据没有任何影响,默认查询的全表数据
-- 基本查询
-- 查询所有列
select sid,sname,age,sex,score,cid groupLeaderId from stu;
-- 查询所有列,在测试,练习时可以使用*代替
select * from stu;
-- 查询指定 列
select sid,sname,sex from stu;
-- 查询的列名可以取别名,使用as,但是一般as不写
select sid as 学号,sname as 姓名,score 成绩 from stu
-- 年龄+1
select age+1 from stu;
算数运算符 | 描述 |
---|---|
+ | 两列做加法运算 |
- | 两列做减法运算 |
* | 两列做乘法运算 |
/ | 两列做除法运算 |
注意:%是占位符,而非模运算符。
3、 条件查询
条件查询就是在基础查询基础上,再给sql设置条件,只查询
部分符合条件
的数据
条件语句 : select 字段1,字段2,... from 表名
where 字段 条件 值
;
条件运算符
= ,在这里是相等,不是赋值
>
<
>=
<=
!=
and 条件并列
or 条件选择
in 范围
not in
between..and 条件范围
-- ============== 条件查询 ==============
-- 查询学号为1001的学生信息
select * from stu where sid = 1001;
-- 查询学生成绩大于60的学生id 姓名,成绩
select sid,sname,score from stu where score > 60;
-- 查询学生性别为女,并且年龄小于50的记录
select * from stu where sex = '女' and age < 50;
-- 查询学生学号为1001,或者姓名为李四的记录
select * from stu where sid = 1001 or sname = '李四';
-- 查询学号为1001,1002,1003的记录
select * from stu where sid = 1001 or sid = 1002 or sid = 1003;
select * from stu where sid in (1001,1002,1003);
select * from stu where sid >= 1001 and sid <= 1003;
-- 查询学号不是1001,1002,1003的记录
select * from stu where sid not in (1001,1002,1003);
select * from stu where sid != 1001 and sid != 1002 and sid != 1003;
-- 查询学生年龄在20到40之间的学生记录
select * from stu where age >= 20 and age <= 40;
select * from stu where age between 20 and 40;
-- 查询性别非男的学生记录
select * from stu where sex != '男';
select * from stu where sex = '女';
-- 查询性别为null的学生记录
update stu set sex = null where sid = 1001.;
-- 查询是不能=null运算
-- select * from stu where sex = null;
select * from stu where sex is null;
-- 查询性别不为null的学生记录
select * from stu where sex is not null;
4、 模糊查询
模糊查询其实也是条件查询
语法: select 字段1,字段2,... from 表名 where
字段 like '%值%'
;
% 匹配任意多个字符
-- ============== 模糊查询 ==============
-- 查询姓名以“张”开头的学生记录
select * from stu where sname like '张%';
-- 查询姓名中包含“三”的学生记录
select * from stu where sname like '%三%';
5、 排序查询
对查询后的数据按照指定字段以及指定规则排序
语法: select 字段1,字段2,... from 表名
order by 字段 [desc|asc]
;
desc 降序
asc 升序,默认是升序
排序查询写在最后
-- ============== 排序查询 ==============
-- 查询所有学生记录,按年龄升序排序
select * from stu order by age asc;
select * from stu order by age;
-- 查询所有学生记录,按年龄降序排序
select * from stu order by age desc;
-- 查询所有学生记录,按年龄升序排序,如果年龄相同时,按编号降序排序
select * from stu order by age asc , sid desc;
-- 查询成绩大于60的学生id,姓名,成绩,并根据成绩降序
select sid,sname,score from stu where score > 60 order by score desc;
6、 聚合函数
将查询的结果,聚合运算得到
一个结果值
,语法特点
聚合运算完,结果只有一行数据
其他字段不能和聚合函数同时查询,除非有分组查询
聚合函数分类
count(expr) 计算指定列的不为null的行数
max(expr) 计算指定列的最大值
min(expr) 计算指定列的最小值
avg(expr) 计算指定列的平均数,除以不为null的条数
sum(expr) 计算指定列的和 ,计算不为null的数据
函数中的expr,可以写列名,也可以写函数表达式
语法: select 聚合函数(字段) from 表名;
-- ================== 聚合函数 ==================
/*
聚合函数: 把多行数据聚合在一起运算得到一个结果
注意: 聚合函数执行完,返回的结果只有一行记录与聚合函数一同出现的列必须出现在group by后
函数:count(字段) : 对该列不为null的行计数sum(字段): 对该列不为null的数据求和avg(字段): 对该列不为null的数据求和再求平均值min(字段): 对该列不为null的数据求最小值max(字段): 对该列不为null的数据求最大值
语法:放在select后from前,对查询的结果列进行运算放在having后使用
*/
-- 查询stu表中记录数
select count(sid) from stu;
select count(sid),sname from stu; -- 报错,语法不对
-- 查询stu表中有成绩的人数
select count(score) from stu;
select count(sex) from stu;
-- 查询stu表中成绩大于60的人数
-- 先执行from获得全部数据,再通过where过滤数据,再计算select后
select count(sid) from stu where score > 60;
-- 查询所有学生成绩和
select sum(score) from stu;
-- 统计所有学生平均成绩(所有有成绩的人的平均分)
select avg(score) from stu;
-- 统计所有学生平均成绩(所有人包括成绩为空的)
select sum(score) / count(sid) from stu;
select sname,age from stu;
-- 扩展: 使用as取别名,as可以省略
select sum(score) / count(sid) as 平均数 from stu;-- 可以
select sum(score) / count(sid) as '平均数' from stu;-- 可以
select sum(score) / count(sid) '平均数' from stu;-- 可以
-- 查询最高成绩和最低成绩
select max(score),min(score) from stu;
select max(score) 'max',min(score) 'min' from stu;
7、 去重函数[了解]
可以将某列数据去重查询,
distinct
,一般不单独使用,配合聚合函数使用
-- ============== 去重查询 ==============
-- 查询年龄不重复的共有多少人
select count(distinct age) from stu;
8、 分组查询
分组查询,就是按照一定条件(列)将查询的数据分为几组.
语法: select 字段1,字段2,... from 表名 [where 字段 条件 值]
group by 字段 having 字段 条件值
;
group by 字段,根据指定字段分组
having 字段 值, 分组后再过滤
有个非常重要特点: SQL只要有分组,分成几组,查询结果就只有几行,所以一般配合聚合函数来使用,是组内数据的聚合
与聚合函数同时出现的列,必须出现在group by语句中
或者说group by后面的字段可以出现在select后
having和where都是过滤
where是分组前过滤,having是分组后过滤
where后不能使用聚合函数,having可以聚合函数
/*
分组查询,通过group by 字段对数据分组,对组内聚合运算
---
分组查询的特点
1) group by语句中,select后的字段必须出现group by中或者说与聚合函数同时出现的列,必须出现在group by中否则会报错
2) 分成几组,最终结果是几行记录
3) 聚合函数是对分组后组内聚合的
-----------------
having和where都是过滤数据,什么区别?
1) where是分组前过滤对每行记录过滤having后分组后 对组过滤
2) having后可以写聚合函数的where不行
*/
-- 查询男生多少人,女生多少人
select sex,count(sex) from stu group by sex;
-- 查询每个班级的班级编号和每个班级的成绩和
select cid,sum(score) from stu group by cid;
-- 查询每个班级的班级编号以及每个班级的人数
select cid,count(*) from stu group by cid;
-- 查询每个班级的班级编号以及每班内男生女生各多少人
select cid,sex,count(*) from stu group by cid,sex
-- 查询成绩总和大于200的班级编号以及成绩和[重点: 分组后过滤用having]
select cid,sum(score)
from stu
group by cid
having sum(score) > 200
-- 查询每个班成绩大于60的人数[会将4班直接过滤掉]
select cid,count(*) from stu where score > 60 group by cid
-- 利用if函数
select cid,count(if(score > 60,score,null)) from stu group by cid;
-- 查询所有人成绩都大于50的班级的班级编号和人数
select cid,count(*) from stu group by cid having min(score) > 50
-- 查询成绩总和大于200的班级编号以及成绩和并根据成绩总和升序
select cid,sum(score)
from stu
group by cid
having sum(score) > 200
order by sum(score) asc
9、 限制查询
就是将查询完的数据,可以限制展现条数
语法: limit n -- 限制输出指定n条,从第一条开始
limit x,y -- 限制输出,从x下标处输出y条,第一条的下标是0
常用于分页操作
-- ================== 分页(limit)查询 ==================
-- 在最后写limit x[,y]
-- limit x,限制输出结果的条数为x
select * from stu limit 5;
select * from stu where score > 40 limit 5;
-- limit x,y 限制从x行处输出y条,x从0开始
select * from stu limit 0,4;
-- 分页中有些已知条件
-- 当前面 pageNo = 1
-- 每页数据量pageSize = 3
-- 数据总量 total = count(sid)
-- 页数 pageCount = total%pageSize==0?total/pageSize:(total/pageSize)+1
-- 第一页
select * from stu limit 0,3
-- 第二页
select * from stu limit 3,3
-- 第三页
select * from stu limit 6,3
-- 第pageNo页
select * from stu limit (pageNo-1)*pageSize,pageSize