10.27 总结

10.27 总结

1.简单查询

select *|字段  from 表名;

-- select  查询
-- * 表示  表中的所有字段   查询效率极低  工作中请你不要使用 *
-- from 从哪张表
-- 表名
-- 执行顺序  先 from  筛选
select * from tb_student;

# 用字段名表示想要哪些列
select stu_no,stu_name from tb_student;

2.筛选查询

1. 结构

书写顺序
	select *|字段 from 表名  where 筛选条件；
执行顺序
	from  --- where  -- select  

2. 例子：

-- 筛选条件
-- 书写顺序 select 字段名 from 表名 where 条件；
-- 执行顺序  from  -- where -- select

-- 查询所有1班的学生信息
select * from tb_student where class_no = 1;
-- 查询所有1班的学生姓名
select stu_name from tb_student where class_no=1;

3.分组查询

每一个班级中的学生人数 
数据展示 
	班级  人数
	1	   3
	2      5
	3      7
select 班级，人数  from 表名 
	
书写顺序
	*    统计null在内
	字段名 null不统计在内 
	select 字段,统计函数(*|字段名) from 表名  group by 分组的字段（根据xxx进行分组）;

分组的目的 获取的内容  统计的结果
聚合函数 
	count() -- 计数 
	max() -- 最大值
	min() -- 最小值
	sum() -- 求和 
	avg() -- 求平均 
	
	使用逗号分隔多个值，将多个数据组合 


书写 
	select 字段，聚合操作
		from  表名
		where 分组之前的筛选
		group by 分组字段（不一定唯一）可以多个字段分
        having 分组之后的筛选
        
执行顺序  
	from ---  where ---- group by  -- having ---  select 

1. 例子详解

-- 获取每个班级的学生人数
-- 分组 的字段 是 班级

select class_no,count(stu_date)
    #   想要班级   统计学生人数    
   from tb_student   # 从tb_student这张表中获取
   group by class_no;   # 根据班级来分组
   
-- 如果觉得系统自带的名字不好，可以用as起别名  as 可以省略
select class_no as '班级',count(stu_date) as '学生人数'
   from tb_student
   group by class_no;
  
# as省略后：
select class_no '班级',count(stu_name)  '学生人数'
    from tb_student
    group by class_no;
   
   
-- * 统计的是所有的总行数  包含null  如果有null值存在 统计
-- 字段名 如果在该字段中有null值  不会统计在内
# 在一行中有一个null,这一行就不会被 * 统计

select class_no '班级',count(*)  '学生人数'
    from tb_student
    group by class_no;
 
# 将 tb_student 中 stu_date 修改为空  只改stu_no=1的
# 若之前设置非空 可能该不了
update tb_student set stu_date = null where stu_no=1;

# 查询 tb_student 的所有信息
select * from tb_student;

-- 统计 男女生个数
-- 性别  人数
-- 男    2
-- 女    5
select gender,count(stu_no)   # 建议用stu_no，是因为这是学生唯一标识
    from tb_student
    group by gender;
    
-- 统计每个班级 男女生的个数
/*
    班级  性别  人数
    1     男
    1     女
    2     男
    2     女
    根据两个字段进行分组
*/
select class_no,gender,count(stu_no)
    from tb_student    # 求什么，就从什么那里找，求人数，找学生表
    group by class_no,gender;   

2. 例题：有第二种写法的

1.-- 统计 每个班的 男生人数
-- 1. 把所有男生找好
select * from tb_student where gender='男';
-- 2. 分组统计
/*
    班级  人数
    1     2
    2    2
    3    2
*/
select class_no,count(stu_no)
    from tb_student
    where gender='男'   # 把女生 剔除出去
    group by class_no;  # 分组
    
# 另外一种写法：
select class_no,count(gender)
    from tb_student
    where gender ='男'
    group by class_no;
    
    

2.-- 统计 每个班的 男生人数
-- 1. 把所有男生找好
select * from tb_student where gender='男';
-- 2. 分组统计
/*
    班级  人数
    1     2
    2    2
    3    2
*/
select class_no,count(stu_no)
    from tb_student
    where gender='男'   # 把女生 剔除出去
    group by class_no;  # 分组
    
# 另外一种写法：
select class_no,count(gender)
    from tb_student
    where gender ='男'
    group by class_no;
    
3.-- 获取1号学生的平均分
select avg(score)
    from tb_score
    where stu_no=1;
# 该题只有一个学生，分不分组都行
select stu_no,avg(score)
    from tb_score
    where stu_no = 1 

2. 错误集锦：

-- 统计 1班和2班的 学生人数
-- 1. 所有的学生中找出1班和2班的学生
select * from tb_student where class_no <=2;
-- 2 分组 计数
select class_no,count(stu_no)
    from tb_student
    where class_no<=2
    group by class_no;
    
# 错误示范：
-- 统计 1班和2班的 学生人数
select class_no,count(stu_no)
    from tb_student
    where class_no =1 and class_no=2
    group by class_no;
    

select * from tb_score;   # 查看成绩表所有信息


3.-- 查询每个学生的平均成绩
/*
    学生  平均分
    1      80
    2      70
    3      50

*/
select stu_no,avg(score)
    from tb_score
    group by stu_no;
    
# 错误注意 ；
select stu_no,avg(score)
    from tb_score;  # group by 报红，可能是上面
    group by stu_no;    
    
-- 查询 出生在 2月的学生
select *
    from tb_student
    where stu_date like '____-02%';   # 注意这是单引号，不能是双引号

3. 普通例题：

1.-- 查询每个课程的平均分
/*
    课程编号  平均分
    1
    2
    3
*/
select course_no,avg(score)
    from tb_score
    group by course_no;

2.-- 获取每个课程的最高分
/*
    课程 最高分
    1
    2
    3
*/
select course_no,max(score)
    from tb_score
    group by course_no;

2.-- 获取 1-5号学生的 每个学生的平均分
/*
    学生 平均分
    1
    2
    3
    4
    5
*/
-- 1. 1-5号学生选出来
select * from tb_score where stu_no<=5;
-- 2 分组统计  平均分
select stu_no,avg(score)
    from tb_score
    where stu_no<=5
    group by stu_no;   # 根据学生来分组

3-1 having分组查询:

聚合之前的筛选用 where

聚合之后的筛选用 having

1. having结构和流程

/*
1. 获取数据结果的形式
2. 判断是不是先进行筛选 聚合之前的筛选 where
3. 确定分组字段
4. 确定统计的结果 个数 最大 最小 求和
5. 聚合之后 筛选    having

*/
-- 成绩表
select * from tb_score;
-- 找 1-5号学生 平均成绩  >70 的
-- 1. 把1-5号学生数据
select * from tb_score where  stu_no<=5;

-- 2. 对每个学生求平均成绩  分组聚合
/*
 学生  平均成绩
 1
*/
select stu_no,avg(score)
    from tb_score
    where stu_no<=5
    group by stu_no;
-- 3. 筛选 平均成绩大于 70
select stu_no,avg(score)
    from tb_score
    where stu_no<=5
    group by stu_no
    having avg(score)>=70;
-- where 用户分组之前的筛选  后面不能跟聚合函数
-- having 用于分组之后的筛选 后面可以跟聚合函数

2. 例子：

-- 获取每个学生课程的最低分大于等于60的
-- 学生所选科目都及格 -- 最低分如果大于等于60
-- 1.每个学生的最低成绩  学生  最低分
select stu_no,min(score)
    from tb_score
    group by stu_no;
-- 2. 获取最低分大于等于60
select stu_no,min(score)
    from tb_score
    group by stu_no
    having min(score)>=60;
-- 获取学生的所选科目都不及格的学生
-- 学生的最高成绩 <60
select stu_no,max(score)
    from tb_score
    group by stu_no
    having max(score)<60;

-- 获取选课学生在三人以上的课程编号和学生数量
/*
    课程编号  选课人数
    1        5
    2        4
    3        7
*/
-- 1. 每个课程的选课人数
select course_no,count(stu_no)
    from tb_score
    group by course_no;

-- 2. 选课人数>=3
select course_no,count(stu_no)
    from tb_score
    group by course_no
    having count(stu_no)>=3;

3-2 group_concat:

可以对数据进行组合

默认使用逗号分隔 separator 指定其他分隔符号

排序的操作 order by 字段 默认是升序 asc 还是降序 desc

-- 获取每个课程的选课学生的编号
/*
    课程编号   学生编号
    1           1，2，3，4，5
    2           2，3，4，5，6
    3           7，8，9
分组 字段 课程编号
    聚合
默认使用逗号分隔  separator 指定其他分隔符号
*/
-- 学生编号 降序排序  排序的操作  order by 字段  默认是升序 asc  还是降序 desc
select course_no,group_concat(stu_no order by stu_no desc separator ',' )
    from tb_score
    group by course_no;
    
    
--统计每个学生的选课科目
/*
    学生 学科科目们
    1      1，2
    2       2，3
*/
select stu_no,group_concat(course_no) as '选课科目'
    from tb_score
    group by stu_no;

-- 查询每个班级的学生姓名
/*
    班级   学生们
    1     小红，小兰，乐乐
    2
    3
*/
select class_no,group_concat(stu_name) '学生们'
    from tb_student     # 有学生姓名的是tb_student这张表
    group by class_no;

4.子查询

把一个查询结果应用在另一个sql语句中，称为子查询

应用场景：判断条件不是一个确定的值，而是通过查询得到的结果

例子：

select * from tb_student;

-- 找 和 张小倩 同年出生的人
-- 1. 获取张小倩的出生年份
select left(stu_date,4) from tb_student where stu_name='张小倩';
-- 2. 和张小倩同年出生的人
select *
    from tb_student
    where left(stu_date,4) = (select left(stu_date,4) from tb_student where stu_name='张小倩')
    and stu_name <> '张小倩';

-- 和 小明同班级的学生姓名
-- 1. 小明的班级
select class_no
    from tb_student
    where stu_name='小明';
-- 2. 和小明同班级
select stu_name
    from tb_student
    where class_no = (select class_no
                      from tb_student
                      where stu_name='小明')
    and stu_name <> '小明';

-- 和 小强同性别的学生姓名
-- 1. 小强的性别
select gender
    from tb_student
    where stu_name='小强';
-- 2. 和小强同性别
select stu_name
    from tb_student
    where gender = (select gender
                        from tb_student
                        where stu_name='小强')
    and stu_name<>'小强';
    
-- 和 2号学生 姓氏相同的学生信息
-- 1. 获取 2号学生的姓氏
select left(stu_name,1)
    from tb_student
    where stu_no=2;
-- 2. 和2号学生 姓氏相同的学生信息
select *
    from tb_student
    where left(stu_name,1) = (select left(stu_name,1)
                                from tb_student
                                where stu_no=2)
    and stu_no<>2;

select * from tb_class;
-- 在python班级的学生信息
-- 学生信息   tb_student
select *
from tb_student;
-- 班级名称  tb_class
select * from tb_class;

-- python班级的班级编号
select class_no
    from tb_class
    where class_name = 'python';
-- 学生表中 找班级编号 = 结果
select *
    from tb_student
    where class_no = (select class_no
                        from tb_class
                        where class_name = 'python');

# 四张表数据查询：
select * from tb_student;
select * from tb_class;
select * from tb_course;
select * from tb_score;

-- 选了 围棋的学生信息
-- 1. 根据围棋 查询 课程的编号
select course_no
    from tb_course
    where course_name='围棋';
-- 2. 根据课程编号 查询 学生编号
select stu_no
    from tb_score
    where course_no = (select course_no
                       from tb_course
                       where course_name='围棋');
-- 3. 根据学生编号 查询学生信息  学生姓名 所在班级
select stu_name,class_no
    from tb_student
    where stu_no in (select stu_no
                     from tb_score
                     where course_no = (select course_no
                                        from tb_course
                                        where course_name='围棋'));
--  更新数据
select * from tb_student;
-- 把马小兰的班级编号修改成和小强一样
-- 1. 查询小强的班级编号
select class_no
    from tb_student
    where stu_name='小强';
-- 2. 修改数据
-- [HY000][1093] You can't specify target table 'tb_student' for update in FROM clause
-- 报错的原因
-- 列表  易错点 遍历的同时对数据进行修改  漏掉元素的检验  处理方案 复制一份
-- 遍历表的同时 对表中数据进行修改 报错  处理方案  复制一份
/*
update tb_student
    set class_no = (select class_no
                    from tb_student
                    where stu_name='小强')
    where stu_name = '马小兰';
*/
-- 复制一张表  把查询结果作为一张表  如果把一个查询结果作为一张表  这张表必须起别名
select * from tb_student;
update tb_student
    set class_no =  (select t.class_no
                        from (select * from tb_student) as t
                        where t.stu_name ='小强'
                     )
    where stu_name='马小兰';

-- with语句  根据查询结果 定义一张临时表  查询结束 这张表就没有了
-- 马小兰的班级修改成和小红一样
with t as (select * from tb_student)  # 把这张表查出来，并设置了一张临时表t
update tb_student
    set class_no = (select class_no
                    from t
                    where t.stu_name = '小红'
                     )
    where stu_name='马小兰';

5.筛选条件运算符

1.比较运算符

字段名 > 值  筛选的是字段中的数据大于某个值的 

>
>=
<
<=
=
<>  /  !=

例子：

-- 查询所有的男生
select * from tb_student where gender='男';
-- 查询不是1班的所有的学生  <>   !=
select * from tb_student where class_no <> 1;


-- 查询所有的出生日期在 1995-1-1 到2000-12-31 学生
-- 创建表  stu_date  -- date  赋值的时候  文本字符串
-- 按照字符串比较大小

# 不能连续比较，要and连接
# 错误示范：
select * from tb_student where '1995-1-1'<=stu_date<='2000-12-31';

select * from tb_student where '1995-1-1'<=stu_date and stu_date<='2000-12-31';

-- between and：
select * from tb_student where stu_date between '1995-1-1' and '2000-12-31';

# 在1995-1-1年以前，以及2000-12-31之后的：
select * from tb_student where stu_date not between '1995-1-1' and '2000-12-31';

2.逻辑运算符

and  逻辑与

or   逻辑或

!    逻辑非 

例子：

-- 查询1班的男生  班级1  性别为男  and
select * from tb_student where class_no=1 and gender='男';

-- 查询 班级为 2或者 性别为女的学生姓名
select stu_name from tb_student where class_no=2 or gender='女';

# 查询该表所有信息
select * from tb_student;
#  * 代表取所有信息 加字段则是有取舍

-- 获取 出生在1995年的 女生
select * from tb_student where gender='女' and stu_date between '1995-01-01' and '1995-12-31';
select * from tb_student where gender='女' and stu_date >= '1995-01-01' and  stu_date <='1995-12-31';
select * from tb_student where gender='女' and left(stu_date,4)='1995';

3. 成员运算符

in  

	字段 in (包含多个数据)

not in  

	字段 not in  (包含多个数据)

4.区间运算

between A and B
字段  between A and B   包含 B
# 字段在A和B之间

not between A and B
字段 not between A and B   
# 字段没有在A和B之间

5.非空判断

is null 
	字段 is null  

is not null 
	字段 is not null   # 字段不为空的记录

6.模糊判断

like   # 字段对应的值像...一样
字段 like '张_'  # 匹配张开头两个字的名字

	通配符
		_    -- 匹配任意一个符号
		%    -- 匹配任意多个符号

例子：

• length 和 char_length 一个字符，三个字节

-- 查询名字 叫张xxx的
select * from tb_student where stu_name like '张%';

-- 查询名字是两个字的
select * from tb_student where stu_name like '__';

-- 内置函数  测量长度  char_length 测量字符的个数
select * from tb_student where char_length(stu_name)=2;

-- length 测量字节个数  utf-8  一个中文 有3个字节
select * from tb_student where length(stu_name)=6;

-- 获取 性别为女 的张姓同学
select * from tb_student where gender = '女' and stu_name like '张%';
-- 名字的第一个字是张  left函数  左边开始的前几位
select * from tb_student where gender='女' and left(stu_name,1) = '张';

-- 获取 一班 的 名字是3个字的 同学
select * from tb_student where class_no=1 and stu_name like '___';

-- 获取 出生在1995年的 女生
select * from tb_student where gender='女' and stu_date like '1995%';

7.正则筛选

如果正则表达式没有限定开头的结尾，找的是包含对应内容的

regexp

字段  regexp  '正则表达式'

字段  regexp '熊.'   # 没有限定开头和结尾，找包含内容的 
例如：大熊瞎子  里面有 熊瞎  可视为熊. 只要有就算

字段  regexp '^熊.$' # 限定了开头结尾，那就必须是这两个字

例子：

-- 查询名字 叫张xxx的
# '^张  以张开头
select * from tb_student where stu_name regexp '^张';

-- 查询名字是两个字的        ^限定开头，$限定结尾，不限定表示包含
select * from tb_student where stu_name regexp '^.{2}$';

-- 获取 出生在1995年的 女生
select * from tb_student where gender='女' and stu_date regexp '^1995';

6. 筛选、分组练习：

-- 查询学生名字 1班中的女生 或者2班中的男生
select stu_name
    from tb_student
    where (class_no=1 and gender='女') or (class_no=2 and gender='男');
    
    
-- 查找名字以张或者刘开头的学生信息
select *
    from tb_student
    where stu_name like '张%' or stu_name like '刘%';

select *
    from tb_student
    where left(stu_name,1) in ('张','刘');

-- 查询名字中有 小的男生
select *
    from tb_student
    where gender='男' and stu_name like '%小%';

select *
    from tb_student
    where gender='男' and stu_name regexp '小';

-- 查询 出生在 2月的学生
select *
    from tb_student
    where stu_date like '____-02%';   # 注意这是单引号

select *
    from tb_student
    where month(stu_date) = 2;

-- 获取年  year()   month()   day()
select day(stu_date)
    from tb_student;
    

-- 查询男 女生 的名字   男生 小明，小强，刘乐乐  女生 小花，小月，
select gender,group_concat(stu_name) as '学生们'
    from tb_student
    group by gender;

-- 查询 每个课程的最高分，最低分，平均分  分组
select course_no,max(score),min(score),avg(score)
    from tb_score
    group by course_no;

-- 查询 每个学生的最高分，最低分，平均分  分组字段 学生编号
select stu_no,max(score),min(score),avg(score)
    from tb_score
    group by stu_no;

-- 查询所有学生所有课程的平均分  --
select avg(score) from tb_score;

-- 查询 班级人数 >4 的班级
-- 每个班级的人数  分组  班级
select class_no,count(stu_no)
    from tb_student
    group by class_no
    having count(stu_no)>4;

-- 获取 有两个女生的班级
-- 所有的女生
select class_no,count(stu_no)
    from tb_student
    where gender='女'
    group by class_no
    having count(stu_no)=2 ;

-- 获取平均分在70以上的课程编号
-- 每个课程的平均分 分组
select course_no
    from tb_score
    group by course_no
    having avg(score)>70;

分组问题： 不是每个都需要分组

# -- 查询每个班级的学生姓名
select class_no,group_concat(stu_name)
    from tb_student
    group by class_no;   # 要不要分组，看有没有需求，比如每个，这就需要
select * from tb_student;

7.错误注意：

# 1. 获取每个学生课程的最低分大于等于60的
select stu_no,course_no,min(score)
from tb_score
group by course_no, stu_no
having min(score)>=60;
# 这样写是错的，没说每个课程。

select stu_no,min(score)
from tb_score
group by stu_no
having min(score)>=60;    # 这是对的

# 2.-- 查询名字中有 小的男生
select *
    from tb_student
    where gender='男' and stu_name like '%小%';

select *
    from tb_student
    where gender='男' and stu_name regexp '小';
    
# 3.-- 查询男 女生 的名字   男生 小明，小强，刘乐乐  女生 小花，小月，
select gender,group_concat(stu_name) as '学生们'
    from tb_student
    group by gender;