MySQL第三讲

最新推荐文章于 2023-04-20 10:29:45 发布

奕霖、Martin

最新推荐文章于 2023-04-20 10:29:45 发布

阅读量378

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/m0_56528635/article/details/126807281

版权

三、模糊查询与分组查询

1、模糊检索

1.1 like

模糊查询的结果不是一定的，只是大概相似的结果，对应的，用户输入的查询条件也可以是模糊的、大概的、不明确的。

运算符	说明
%	代表任意长度的字符串
_	代表一个字符
escape	规定转义字符
\	转义字符

符号解释案例：

-- 查询user表中名字带有“_”字符的用户
-- 这两种写法效果是一样的
select * from user where name like concat("%","\_","%");
select * from user where name like concat("%","$_","%") escape "$";
-- 因为_是特殊字符，所以查找时，应该将_看做是普通字符，需要用转义字符来标识；
-- escape "$" 的意思就是，把$看做是转义字符，$后面的字符就是普通字符，没有特殊含义

create table t_class(
id int primary key auto_increment,
cname varchar(100)
);

-- 创建学生表 
create table tab_student(
id int primary key auto_increment,
stuname varchar(100),
sex varchar(2),
begintime date,
gradutetime date,
idcard varchar(18),
age int,
address varchar(200),
cid int,
CONSTRAINT fk_classid FOREIGN key(cid)
references t_class(id)
);
insert into tab_student(id,stuname,sex,begintime,gradutetime,idcard,age,address) values('2','雷wl',"男",'2022-1-1','2022-12-30','412723xxxxxxxx0813','35','周口市');
insert into tab_student(id,stuname,sex,begintime,gradutetime,idcard,age,address) values(null,'雷wl1',"女",'2022-1-1','2022-12-30','412723xxxxxxxx0813','15','郑州市');
insert into tab_student(id,stuname,sex,begintime,gradutetime,idcard,age,address) values(null,'雷wl22',"女",'2022-1-1','2022-12-30','412723xxxxxxxx0813','30','上海市');
insert into tab_student(id,stuname,sex,begintime,gradutetime,idcard,age,address) values(null,'a雷wl5',"男",'2022-1-1','2022-12-30','412723xxxxxxxx0813','60','北京市');

select * from tab_student;
-- 模糊查询 关键字：like / between and / in  
-- like ：模糊查询提取的数据不一定是确切的
-- —代表1个字符 两个_就代表有两个字符
select * from tab_student where stuname like '雷__';-- 查询所有的雷xx
select * from tab_student where stuname like '雷___';-- 查询所有的雷xxx
select * from tab_student where stuname like concat('雷','__');-- 查询所有的雷xxx
-- like %代表任意个字符
select * from tab_student where stuname like '雷%';
select * from tab_student where stuname like concat('雷','%');
-- 不论'雷'在字符串的哪个位置都能查询出来
select * from tab_student where stuname like '%雷%';
select * from tab_student where stuname like concat('%','雷','%');

1.2 正则

MySQL 中的正则表达式与 Java 语言、PHP 语言等编程语言中的正则表达式基本一致。

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	‘^b’ 匹配以字母 b 开头的字符串	book、big、banana、bike
$	匹配文本的结束字符	‘st$’ 匹配以 st 结尾的字符串	test、resist、persist
.	匹配任何单个字符	‘b.t’ 匹配任何 b 和 t 之间有一个字符	bit、bat、but、bite
*	匹配零个或多个在它前面的字符	‘f*n’ 匹配字符 n 前面有任意个字符 f	fn、fan、faan、abcn
+	匹配前面的字符 1 次或多次	‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a	ba、bay、bare、battle
<字符串>	匹配包含指定字符的文本	‘fa’ 匹配包含‘fa’的文本	fan、afa、faad
[字符集合]	匹配字符集合中的任何一个字符	‘[xz]‘ 匹配 x 或者 z	dizzy、zebra、x-ray、extra
[^]	匹配不在括号中的任何字符	[^abc] 匹配任何不包含 a、b 或 c 的字符串	desk、fox、f8ke
字符串{n,}	匹配前面的字符串至少 n 次	‘b{2}’ 匹配 2 个或更多的 b	bbb、bbbb、bbbbbbb
字符串 {n,m}	匹配前面的字符串至少 n 次，至多 m 次	‘b{2,4}’ 匹配最少 2 个，最多 4 个 b	bbb、bbbb

-- 正则：一套复杂的规则：410或者412打头的身份证
select * from studentinfo where studentno like '410%' or studentno like '412%' 
select * from studentinfo where studentno REGEXP '^41[0|2]'

1.3 between and

-- between and ：根据某个字段值的区间范围来查询符合条件的记录，这个范围内有多少数据并不明确
-- between and 的执行效率较高，相对于比较表达式效率高
select * from tab_student where id>3 && id<6;  -- 结果是 id= 4/5
select * from tab_student where id between 4 and 5;-- 结果是 id= 4/5

1.4 in

-- in ：用来匹配某个字段的值是几个可数的选项值之一 
-- in 的执行效率比or高
-- 查询地址为 周口市 上海市 北京市的学生
select * from tab_student where address='周口市' or address='上海市' or address='北京市';
select * from tab_student where address in('周口市','上海市','北京市');
-- 查询地址不为 周口市 上海市 北京市的学生
select * from tab_student where address!='周口市' and address!='上海市' and address!='北京市';
select * from tab_student where address not in('周口市','上海市','北京市');

2、分组查询

分组查询显示的结果是根据分组查询的结果，分组之后的表与原始表格的列名就不一致了，所以最后不会显示原本表格中的信息

2.1聚合函数

获取某些列的最大值、最小值、平均值等统计分析信息，有时候还需要计算出究竟查询到多少行数据项。分别有：sum、max、min、avg、count（注意非空值）——计数，结果集有几行

-- 聚合函数 count / sum / avg / max / min /
-- 如果没有group by 所有数据算是一组进行聚合
-- count 关键字:count计数：求所有非空(空指的是count中书写的列的内容全部为空)条数的
-- 语法：select count(列名) from 表名
select * from tab_student;-- 这里使用* 效率会降低
select count(*) from tab_student; -- 这里用*，效率会变高
-- 查询学生表中所有名字为空的学生
select count(*) from tab_student where stuname is null;
-- 查询学生表中所有名字不为空的学生
select count(*) from tab_student where stuname is not null;
-- sum 求总数
select sum(age) from tab_student;
-- max 求最大值
select max(age) from tab_student;
-- min 求最小值
select min(age) from tab_student;
-- avg 求平均值
select avg(age) from tab_student;

2.2 分组函数

-- 分组函数
-- 分组函数一般与聚合函数配合使用
-- 分组后在聚合是每一组的聚合:查询结果处展示的分组列和聚合列，分组列整体不重复
-- 如果查询位置出现普通列：常规会报错，mysql会默认显示第一行
-- group by：：一组中只有一条数据来标识每个性别里面有多少学生
select sex,count(*) from tab_student group by sex;
-- 每个班级里面有多少个学生
select count(*) from tab_student group by cid;
-- 每个班级里面每种性别有多少个学生
select cid,sex,count(*) from tab_student group by cid,sex;
-- 每个城市有多少个学生
select address,count(*) from tab_student group by address;

2.3 having函数

-- having：对分组后的数据做过滤
-- 查找班级人数大于2的班级
-- 思路1：
-- 1.先对每个班级的人数分组
-- 2.使用having关键字过滤 人数>2 
select cid,count(*) from tab_student group by cid having count(*)>2;
-- 思路2：
-- 虚表==虚拟表 == 不存在的表
select * from(
select cid,count(*) as a from tab_student group by cid) vir where a>2;

3、关联查询

3.1 关联查询的作用

假如所有数据都存在一张表中，有以下缺点：

1）冗余存储，有些字段只有很少的对象会有，但是必须要设立该字段

2）删除、修改操作风险大

3）过长的数据看起来麻烦，而且占内存比较大

所以存储信息的时候会分在多个表中，这就解决了以上麻烦，但是分表的话，查询起来就比较费时费力，解决这个麻烦就需要用关联查询。

3.2 关联查询的分类

关联查询：就是根据一定的条件把多张表数据组合在一起，得到类似于一张表的效果

3.2.1内连接

内连接：也叫等值连接，内连接和外连接一样都是最常用的连接查询，它根据表中共同的的列进行匹配，特别是两个表存在主外键关系时，通常会使用到内连接查询

返回的结果集是两个表中所有相匹配的数据，舍弃不匹配的数据

两张表都符合关联条件—根据关联条件查询，左右表都有该数据那么该数据显示，如果一张表有一张表没有该数据就不显示。

例如：班级和学生关联查询——班级id=班级id

学生中的班级id在班级中找不到——这个学生不显示

学生班级id直接null——肯定找不到数据所以不显示。

-- 内连接：也叫等值连接
-- 第一种写法格式
-- select 列名from 表1inner join 表2 on 表1和表2 的关联条件inner join 表3 on 表1和表3的 表2 和表3条件--关联条件
-- 第二种写法格式
-- select 列名from  表1  , 表2 , 表3.....where 关联条件和过滤条件
--一般情况下手：关联条件在前 过滤条件在后
-- 过滤条件一般是右往左，右边的过滤先执行。

-- 获取所有的学生的信息，并且获取对应的班级id
-- 第一种写法 inner join ：inner可以省略不写
select * from tab_student stu inner join t_class cls on stu.cid=cls.id;-- inner 可以省略
select stu.*,cls.* from tab_student stu inner join t_class cls on stu.cid=cls.id;
-- 第二种写法：where的写法
select stu.*,cls.* from tab_student stu,t_class cls where stu.cid=cls.id;

3.2.2 外连接

外连接是至少返回一个表中的所有记录，根据匹配条件有选择性地返回另一张表的记录，如果匹配就显示两表如果不匹配就显示为null

外连接就是在满足表连接关系的情况下不但可以查找出匹配的数据，而且还可以包含左表，右表或是两表中的所有的数据行

-- outer可以省略不写
左外：保证左表数据完整
select 列名
from 左表
left 【outer】 join 右表 on 条件
......

右外：保证右表数据完整
select 列名
from 左表
right 【outer】 join 右表 on 条件
......

全外连接：保证两表数据完整——Mysql不支持
select 列名
from 左表
full【outer】 join 右表 on 条件
......
-- 外连接：左外连接/右外连接
-- 所有班级里的所有学生
-- 左连接：left outer join 
-- 特点：左表（主表）中的数据全部显示，如果右表中没有数据用null来填充
select * from t_class cls left join tab_student stu on cls.id=stu.cid;
select * from tab_student stu left join t_class cls on cls.id=stu.cid;

-- 右连接：right outer join 
select * from tab_student stu right join t_class cls on cls.id=stu.cid;

-- 全连接 ： union/union all
-- 全连接就是连接左右连接，全连接时，要保持列名一致
-- union ：自动去除重复的数据
select cls.*,stu.* from tab_student stu left join t_class cls on cls.id=stu.cid
union
select cls.*,stu.* from tab_student stu right join t_class cls on cls.id=stu.cid;
-- union all ：不能去除重复的数据
select cls.*,stu.* from tab_student stu left join t_class cls on cls.id=stu.cid
union all
select cls.*,stu.* from tab_student stu right join t_class cls on cls.id=stu.cid;

3.2.3 交叉连接（笛卡尔乘积）

交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行再一一组合，相当于两个表“相乘”

create table emp(   
empid int primary key auto_increment,   
empname varchar(200));

create table dbdate(    
dateid int primary key auto_increment,  
dateinfo date);

create table ribao( 
ribaoid int primary key auto_increment, 
empid int , 
dateid int);

insert into emp values(null,'zs'),(null,'ls'),(null,'ww')
insert into dbdate values(null,'2021-6-1'),(null,'2021-6-2'),(null,'2021-6-3')
insert into ribao values(null,1,1),(null,2,2),(null,3,3)
-- 交叉连接：笛卡尔乘积
select * from emp a join dbdate b on 1=1

select a.empname,b.dateinfo,c.* from emp a cross join 
dbdate b left join ribao c on a.empid=c.empid and b.dateid=c.dateid

-- 运行结果
zs  2021-06-01  1   1   1
ls  2021-06-02  2   2   2
ww  2021-06-03  3   3   3
ls  2021-06-01          
ww  2021-06-01          
zs  2021-06-02          
ww  2021-06-02          
zs  2021-06-03          
ls  2021-06-03

4、自连接

create table tab_stu(
    stuid int not null auto_increment primary key ,
    stuname varchar(255) default null,
    zuzhangid int  
);
INSERT INTO `tab_stu` VALUES (1, '于昊辰', NULL);
INSERT INTO `tab_stu` VALUES (2, '郝金林', 1);
INSERT INTO `tab_stu` VALUES (3, '叶帅', NULL);
INSERT INTO `tab_stu` VALUES (4, '张统', 3);

select * from tab_stu;
-- 自连接
select * from tab_stu as zuzhang 
join tab_stu as zuyuan 
on zuyuan.zuzhangid=zuzhang.stuid