MySQL DQL分组查询、条件查询、子查询

1.分组函数

1.分组函数又称统计函数、组函数、聚合函数。
2.功能：用作统计使用
3.分类：sum求和、avg平均值、max最大值。min最小值、count计数（非空）。

/*
分组函数：统计函数、组函数、聚合函数
sum 求和、avg 平均值、max 最大值、min 最小值、count 计数（非空）
列的值如果是null不会被统计，一般使用主键或者*，*所有列，遇到一个为空的列，重新找不为空的列进行统计
*/

-- sum求和
SELECT SUM(stu_grade) FROM t_student
-- avg平均值
SELECT AVG(stu_grade) FROM t_student
-- max最大值
SELECT MAX(stu_grade) FROM t_student
-- min最小值
SELECT MIN(stu_grade) FROM t_student
-- count计数 
SELECT COUNT(stu_sex) FROM t_student

2.分组查询

1.分组查询：会将相同内容分到同一组。
2.group by：根据某一列数据进行分组。
3.where后是在分组前对数据进行筛选过滤。
4.having后是对分组完成后的结果进行条件过滤。

-- 分组查询 分组 会将相同内容分到同一组      例如使用性别分组
-- select 结果 from 表 group by 分组列
SELECT * FROM t_student GROUP BY stu_sex    -- 会报错

-- 统计男生、女生各有多少人
SELECT stu_sex,COUNT(*) FROM t_student GROUP BY stu_sex

-- 添加查询操作
SELECT stu_sex,COUNT(*) c
FROM t_student
WHERE stu_grade>0  -- 在分组前对数据进行筛选过滤
GROUP BY stu_sex
ORDER BY c DESC  -- 对分组后的结果进行排序

--  查询性别人数大于2的是哪个性别    对分组后的结果进行条件筛选
SELECT stu_sex,COUNT(*) c
FROM t_student
GROUP BY stu_sex
HAVING c>2   -- 对分组后的结果进行条件过滤

3.条件查询

1.使用where子句，将不满足条件的行过滤掉，where子句紧随from子句。
语法：select <结果> from <表名> where <条件>
比较：=、!=或<>、<、>、<=、>=
逻辑运算：and 和、or 或、not 非
2.模糊查询
like：是否匹配于一个模式，一般和通配符搭配使用，可以判断字符型数值或数值型。
通配符：%表示若干个字符，包含0个字符；_表示任意单个字符。
3.between and：两者之间，包含临界值。
4.in：判断某字段的值是否属于in列表中的某一项。
5.is null（为空的）或is not null（不为空的）。
6.union和union all
一般union用的比union all多，union all是直接连接的，取得所有的值，记录可能有重复，而union是取唯一值，记录没有重复。
效率：
UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。
（1）、对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。
（2）、对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。
从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。
7.排序 order by
查询结果排序，使用order by子句排序：order 排序列 asc/desc，asc表示的是升序，desc表示的是降序，不写默认为升序。
order by子句中可以支持单个字段、多个字段、表达式、函数、别名
8.数量限制 limit
limit子句：对查询结果限制数目，添加在sql语句末端
有三种方式：
（1）select 查询结果 from table limit 5;
限制只显示5行
（2）select 查询结果 from table limit 0,3;
0表示起始位置，3表示显示行数
（3）select 查询结果 from table limit 3 offset 0;
0表示起始位置，3表示显示行数。

-- select 结果 from 表名 where 数据条件过滤 group by    having    order by     limit

-- =在where子句后面表示  等于
SELECT * FROM t_student
SELECT * FROM t_student WHERE stu_sex='男'

-- 不等于 != 或者 <>
SELECT * FROM t_student WHERE stu_sex!='男'
SELECT * FROM t_student WHERE stu_sex<>'男'


-- and 多个条件并列，必须全部成立
SELECT * FROM t_student WHERE stu_grade<=100 AND stu_grade>=60

-- or 多个条件只要满足一个即可
SELECT * FROM t_student WHERE stu_grade>90 OR stu_sex='男'

-- 列名 between 区间开始 and 区间结束    包含60和100
SELECT * FROM t_student WHERE stu_grade BETWEEN 60 AND 100

-- 模糊查询 like
SELECT * FROM t_student WHERE stu_name='Jim'

-- 查询姓李得人 like '关键字%_'  %匹配若干个（包括0个）字符，_匹配单个字符
SELECT * FROM t_student WHERE stu_name LIKE '李_'
SELECT * FROM t_student WHERE stu_name LIKE '李%'

-- in   查询成绩为99和89的学生
SELECT * FROM t_student WHERE stu_grade=99 OR stu_grade=89
-- in(值1,值2,.....)
SELECT * FROM t_student WHERE stu_grade IN(99,89) 

-- not 不在，不是   查询成绩不为99，89的
SELECT * FROM t_student WHERE stu_grade NOT IN(99,89) 

-- is null为空   is not null 不为空
SELECT * FROM t_student WHERE stu_birthday IS NULL
SELECT * FROM t_student WHERE stu_address IS NOT NULL

/*
union 将多个查询结果合并，要求查询出来的列数形同
sql1
union
sql2

union 去除两个查询结果中重复数据，排序
union all 只是单一合并，不排序
*/
SELECT stu_num,stu_name,stu_grade FROM t_student WHERE stu_sex='男'
UNION
SELECT stu_num,stu_name,stu_grade FROM t_student WHERE stu_sex='女'

SELECT stu_num,stu_name,stu_grade FROM t_student WHERE stu_sex='男'
UNION
SELECT stu_num,stu_name,stu_sex FROM t_student WHERE stu_sex='女'

SELECT stu_num,stu_name,stu_grade FROM t_student WHERE stu_sex='男'
UNION
SELECT stu_num,stu_name,stu_grade FROM t_student WHERE stu_sex='男'

-- order by  排序  默认使用升序排序  ASC 升序   DESC降序
-- 默认使用主键升序排序
SELECT * FROM t_student
SELECT * FROM t_student ORDER BY stu_grade DESC

--   order by 指定排序列 排序规则
-- 在条件后面添加排序


-- 行数限制  limit
--  SELECT * FROM table  LIMIT [offset,] rows | rows OFFSET offset;
--                       提取前三行数据
SELECT * FROM t_student LIMIT 3
-- 查询成绩前两名同学
SELECT * FROM t_student ORDER BY stu_grade DESC LIMIT 2

--  limit  开始位置（从0开始）,数量
SELECT * FROM t_student LIMIT 0,2

--  limit 数量 offset 开始位置
SELECT * FROM t_student LIMIT 3 OFFSET 0

4.子查询

1.含义：出现在其他语句中的select语句
2.其他语句：insert、update、delete、select
3.在select语句中只有子查询
（1）按照子查询结果分为：
标量子查询（一行一列）；
列子查询（一列多行）；
行子查询（一行多列）；
表子查询（多行多列）。
（2）按子查询出现的位置：
select后面：仅仅支持标量子查询；
from后面：支持表子查询；
where或者having后面：支持表子查询、列子查询、行子查询；
exists后面：支持表子查询。

-- 子查询：出现在其他语句中的select语句
-- 其他语句：insert、update、delete、select

ALTER TABLE t_student MODIFY stu_num INT AUTO_INCREMENT
-- 在insert语句中使用子查询
INSERT INTO t_student(stu_name,stu_sex) SELECT stu_name,stu_sex FROM t_student WHERE stu_num=104

-- 在update语句中使用子查询，注意查询结果不能在这张表中
UPDATE t_student SET stu_name='李五' WHERE stu_num=(SELECT stu_num FROM student WHERE stu_name='李四') 

-- 在delete语句中使用子查询,注意查询结果不能在这张表中
DELETE FROM t_student WHERE stu_num=(SELECT stu_num FROM student WHERE stu_name='李四')

/*
在select语句中使用子查询
按功能、结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
*/

-- 在select语句后加子查询只能是标量子查询
SELECT (SELECT NOW()) FROM t_student

-- 在from后面，支持表子查询  每一个派生表必须有自己的别名
-- 性别人数大于2的性别
SELECT stu_sex FROM (SELECT stu_sex,COUNT(*) c FROM t_student GROUP BY stu_sex) tabless WHERE c>2

-- 列子查询（一列多行）
SELECT stu_name FROM t_student WHERE stu_grade IN(SELECT stu_grade FROM t_student WHERE stu_grade>80)

-- 行子查询（一行多列）  查询学号最小成绩最高的学生       返回的是一个一行多列
SELECT * FROM t_student WHERE (stu_num,stu_grade)=(SELECT MIN(stu_num),MAX(stu_grade) FROM t_student)

-- 查询成绩最高的学生信息
SELECT * FROM t_student WHERE stu_grade=(SELECT MAX(stu_grade) FROM t_student)