MySql嵌套查询+关联查询+多表查询+对应案例+mybatis动态sql 超详细

81 阅读 0 评论 54 点赞

我是靠谱客的博主花痴口红，最近开发中收集的这篇文章主要介绍MySql嵌套查询+关联查询+多表查询+对应案例+mybatis动态sql 超详细，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

最近学习MyBatis框架用到多表查询比较多，以前学的不是很好，今特意回来补上。呜呜呜。

有对MySql数据库的初步使用不是很了解的朋友们，可以切换到这里噢~~
https://blog.csdn.net/haobo__/article/details/110356744

文章目录

- - 先看我的数据库表（直接看目录找需要的）
  - 1. INNER JOIN 内连接
  - 2 .LEFT JOIN 左查询
  - 3. RIGHT JOIN 右查询
  - 4. UNION 全外连接
  - 5. LIMIT 分页查询
  - 6. ORDER BY 排序查询
  - 7. 聚合函数
  - 8.分组查询
  - 9.嵌套查询
  - - 1. IN 表示值是否存在子查询结果集中
    - 2. EXISTS 是表示子查询是否返回结果，而不管返回的具体内容。
    - 3.ALL表示子查询结果中的所有。
    - 4.ANY是表示子查询结果中任意一个
  - 10 LIKE模糊查询 LOCATE（）模糊搜索
  - 11.mybatis的动态查询
  - - 1.autoMapping和autoMappingBehavior的区别
    - 2. resultMap概述
    - 3. resultType元素
    - 4.动态sql
  - 12、mysql的常用语法
  - - 插入一条数据
    - 插入多条数据
    - 插入查询结果)（把查询的结果插入到对应的表中）
    - 去重查询
    - 查询的时候直接使用四则运算
    - 查询的时候重命名 (as 可以省略不写)
    - 使用concat连接字符串得到自定义格式的查询结果
    - 使用order by对查询的结果进行排序，asc升序，desc降序
    - 使用group by对数据进行分组
    - 组合使用 count 是统计查询出的条数
    - 使用group_concat（）实现显示没个分组中的字段
    - 如果要在分组查询中加入条件，则必须使用having而不是where
    - 如果使用条件分组的同时还要排序，则order by必须位于having后边
    - concat连接字符串
    - concat_ws使用分隔符连接字符串
    - length和char_length来获取字符串的长度
  - 11.时间与时间戳的转换
  - - 获取当前的时间/时间戳
    - 10位时间戳/时间格式转换
    - 13位时间戳/时间格式转换
  - 14 树状结构一次查询（不推荐使用）
  - FIND_IN_SET和Match+Against区别和使用
  - 获取随机数

先看我的数据库表（直接看目录找需要的）

总共
4张表
在这里插入图片描述

老师职位表 tb_position
学生表(为了好辨认效果) tb_student
老师表（id 班级老师名字职位对应表） tb_teacher
老师与学生的关系对应表，毕竟有多种关系多对多 tb_stu_teach

1. INNER JOIN 内连接

-- 把两张表中 某列 相同值的给查询出来
select stu.t_stu_name,tea.t_no,tea.t_name
from  tb_teacher tea 
INNER JOIN tb_student stu 
on stu.id = tea.id;

在这里插入图片描述

2 .LEFT JOIN 左查询

-- 左查询  
-- 以左边的表的数据为基准， 去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为null；
SELECT STU.T_STU_NAME,TEA.T_NAME
FROM tb_student STU
LEFT JOIN tb_teacher TEA
ON STU.id = TEA.id;

在这里插入图片描述

3. RIGHT JOIN 右查询

-- 右查询  
-- 以右边的表的数据为基准，去匹配左边的表的数据，如果匹配到就显示，匹配不到就显示为null；
SELECT STU.T_STU_NAME,TEA.T_NAME
FROM tb_teacher TEA   --所谓的左边
RIGHT JOIN  tb_student STU  --所谓的右边
ON STU.id = TEA.id;

在这里插入图片描述

4. UNION 全外连接

-- 把两张表的字段都查出来，没有对应的值就显示null，
-- 注意：mysql是没有全外连接的(mysql中没有full outer join关键字)，想要达到全外连接的效果，可以使用union关键字连接左外连接和右外连接； 
(两个select 除了关键字不一样  其他都得一样，可以试试把查出来的值换一个 试试 哈哈哈)
SELECT STU.T_STU_NAME,TEA.T_NAME
FROM tb_student STU
LEFT JOIN tb_teacher TEA
ON STU.id = TEA.id
UNION
SELECT STU.T_STU_NAME,TEA.T_NAME
FROM tb_student STU
RIGHT JOIN  tb_teacher TEA
ON STU.id = TEA.id;

为了看出不同在教师表中添加了一行数据
在这里插入图片描述

5. LIMIT 分页查询

-- INDEX =(当前页码-1)*个数
 -- 下标从 INDEX 开始  查询  X 条  我这里  index是0，x是3 
SELECT * 
FROM tb_student
LIMIT 0,3;

在这里插入图片描述

6. ORDER BY 排序查询

默认为 升序 可以用关键字 DESC(降序) ASC(升序)

SELECT * 
FROM tb_teacher
ORDER BY position_id 
DESC;  -- 降序

在这里插入图片描述

SELECT * 
FROM tb_teacher
ORDER BY position_id 
ASC;  -- 升序

在这里插入图片描述

7. 聚合函数

在查询数据时可以将一列数据进行纵向的计算
在这里插入图片描述
用法 : 聚合函数查询的语法 SELECT 聚合函数(列名) FROM 表名

-- 计算教师id平局值
SELECT AVG(ID) 
FROM tb_teacher;

在这里插入图片描述

8.分组查询

按照特定条件把数据进行分组，把每一组当做一个整体，分别对某一组数据进行计算。
分组查询语法，字段列表只能是分组列、或者聚合函数

标准语句 SELECT 字段列表 FROM 表名 where 分组前条件 GROUP BY 分组列名 HAVING 分组后条件

对所有数据分组查询

-- 按教师的职位分类 查询 当前职业教师的个数
SELECT COUNT(*),
FROM tb_teacher 
GROUP BY position_id;

在这里插入图片描述
2. 分组前筛选


-- 分组前筛选 将id大于2的按教师的职位分类 查询 当前职业教师的个数
SELECT COUNT(*)
FROM tb_teacher
WHERE  id>2
GROUP BY position_id;

在这里插入图片描述

3.分组后筛选

因为数据表建不适合演示这个所以把用法贴在这

 SELECT 你要显示的一些数据，比如平均数（COUNT(列名)，列名
 FROM 表名 
 WHERE  分组条件  比如 分数大于60 。。等等等 
 GROUP BY   第二个分组条件  ， 当然先执行上面那个  
 HAVING 通过查找出来的数据  通过一些条件在进行筛选  ;   z

【表达的有点绕】

9.嵌套查询

子查询：一般在子查询中，程序先运行在嵌套在最内层的语句，再运行外层。因此在写子查询语句时，可以先测试下内层的子查询语句是否输出了想要的内容，再一层层往外测试，增加子查询正确率。否则多层的嵌套使语句可读性很低。
子查询一般出现在FROM和WHERE子句中。
子查询在主查询前执行一次
主查询使用子查询的结果

1. IN 表示值是否存在子查询结果集中

--  1. 先查询出 我的教师表中的 id
--  2. 再根据子查询的id ，寻找我学生表中对应的id
SELECT * 
FROM tb_student 
WHERE id 
IN (SELECT id FROM tb_teacher)

在这里插入图片描述

2. EXISTS 是表示子查询是否返回结果，而不管返回的具体内容。

SELECT * 
FROM tb_student 
WHERE
EXISTS (SELECT * FROM tb_teacher WHERE id=2)
-- 我这里的子查询为能查到结果  所以返回值如下
-- 要是我将子查询条件设置为  WHERE id=100  因为我教师表中，没有id为100的，所以总的查询结果为null.

在这里插入图片描述

3.ALL表示子查询结果中的所有。

all表示要大于子查询结果中的所有，才会返回true, not in 相当于“<>all”.

-- 1. 先查出教师表中  id<=2的  
-- 2.在查出学生表中的id > 教师表查出结果的数值 （每个值都会比较）
SELECT * 
FROM tb_student 
WHERE id>
All (SELECT id FROM tb_teacher WHERE id <=2)

在这里插入图片描述

4.ANY是表示子查询结果中任意一个

any表示只要大于子查询结果中的任一个，表达式就成立，=any表示等于子查询中的任一个,相当于in.

SELECT * 
FROM tb_student 
WHERE id>
ANY (SELECT id FROM tb_teacher WHERE id <=2)

在这里插入图片描述

in在子查询不返回数据的时候,为false,子查询结果中有null的时候，null不会用于比较。
any 同样在子查询不返回数据的时候,为false,子查询结果中有null的时候，null不会用于比较。
all在子查询不返回数据的时候,为true,子查询结果中有null的时候，不会返回数据。
not in 或not exists来代替.
not in 不等于<> any,相当于<>all,
<>any是只要不等于其中的任意一个，就成立

关于 IN,NOT IN,ANY和ALL使用时的陷进可以看一下这篇帖子
https://blog.csdn.net/kkdelta/article/details/7468850

10 LIKE模糊查询 LOCATE（）模糊搜索

-- 模糊查询   这里的 LIKE 后面的 %  需要注意
SELECT * FROM tb_student WHERE t_stu_name LIKE '%张%'

LOCATE（）

-- 模糊搜索
SELECT * FROM tb_student WHERE `LOCATE`('张',t_stu_name)

两张表的结果一样
在这里插入图片描述
使用LIKE时间稍慢

11.mybatis的动态查询

1.autoMapping和autoMappingBehavior的区别

autoMappingBehavior
mybatis核心配置文件中settings中配置,指定 MyBatis 应如何自动映射列到字段或属性。 NONE 表示取消自动映射；PARTIAL 只会自动映射没有定义嵌套结果集映射的结果集。 FULL 会自动映射任意复杂的结果集（无论是否嵌套）。默认是partial，这是一种全局设置
autoMapping
在resultMap或者association,collections中使用，是一个局部开关，开启后会自动设置嵌套查询中的属性，局部开关优先级大于全部开关，当全部开关开启FULL映射时，局部开关关闭，这时候仍然不会进行映射。

autoMappingBehavior是里面的，是全局总开关。autoMapping是里面的，是局部select语句映射开关。局部开关优先级大于全局开关。

2. resultMap概述

resultMap是Mybatis映射文件中最重要最强大的元素。它描述如何从结果集中加载对象，主要作用是定义映射规则、级联的更新、定制类型转化器。它可以让你从 90% 的 JDBC ResultSets 数据提取代码中解放出来, 并在一些情形下允许你做一些 JDBC 不支持的事情。实际上，在对复杂语句进行联合映射的时候，它很可能可以代替数千行的同等功能的代码。 resultMap的设计思想是，简单的语句不需要明确的结果映射，而复杂一点的语句只需要描述它们的关系就行了。

【association】

    A对象里面有B对象属性   一对一   内连接查询  inner join  *** on  ***

            当查询的sql语句中有别的类的对象的时候  返回值用resultMap 然后用<resultMap  type="主要的返回类型"  id="就是resultMap的值[对应关系]">
                【主键字段】  <id column="第一个参数" property="第一个参数">
                   <result column="返回的第二个参数“ property="第二个参数别名”>
            <!-- 一对一关系 -->
            <association  property="需要获得的别的类对象" javaType="别名">
                【主键字段】 <id column="第一个参数" property="第一个参数">
                    <result column="返回的第二个参数“ property="第二个参数别名”>
            </association >
            </resultMap>
            这个里面的select 属性  可以引入外部的 select查询语句【查询嵌套】

【collection】

  注：当两个表里面有一样名字的列名时  一定要在sql里写别名  as
    A对象里面有List<B>属性时  一对多   左外连接   left  jo
    当查询的sql语句中有别的类的对象的集合的时候  返回值用resultMap 然后用<resultMap  type="主要的返回类型"  id="就是resultMap的值[对应关系]">
    【主键字段】  <id column="第一个参数" property="第一个参数">
       <result column="返回的第二个参数“ property="第二个参数别名”>
            <!-- 一对一关系 -->
            <collection  property="需要获得的别的类对象" ofType="别名">

3. resultType元素

使用resultType进行输出映射，只有查询出来的列名和pojo（实体bean）中的属性名一致，该列才可以映射成功。简单来说也就是你的数据库字段和JavaBean里的字段名称必须一致才能映射成功。

4.动态sql

1.基于OGNL表达式
2.完成多条件查询等逻辑实现
3.用于实现动态SQL的元素主要有 if  trim   where   set  choose(when、otherwise)  foreach

if

 where <if test="name!=null" >
                and name=#{name}
            </if>
        当有可能一个参数都不传得情况下 上面写法会报错
                <where>  //会自动剔除第一个if前面的and
                <if>
                 </if>
                 </where>
进行更新等sql操作时可以用
            <set><if></if></set>
            【注】: 1。这条跟新语句至少要成功一个条件
                    2. 每个sql后注意,隔开

trim

	prefix:前缀  prefixOverrides=""前缀怎么处理
   	suffix:后缀  suffixOverrides=“后缀怎么处理”
   	灵活的去除多余关键字。  代替where和 set

foreach

条数据的操作：比如查询条件是一个集合,通常用于in条件
属性
item、index、collection:必须指定{list、array、map-key}、open、separator、close

在这里插入图片描述

12、mysql的常用语法

我的表

一些常用的数据操作

插入一条数据

INSERT into shop_user(uid,PASSWORD,telephone,username) VALUES (4,‘aaa’,‘123’,‘bvc’);

插入多条数据

INSERT into shop_user(uid,PASSWORD,telephone,username) VALUES (1,‘dsa’,‘123’,‘dadqa’),(2,‘dasd’,‘2133’,‘dsadsa’),(3,‘dad’,‘123’,‘jiawwwwdei’)

插入查询结果)（把查询的结果插入到对应的表中）

INSERT into shop_user(uid,PASSWORD,telephone,username) select t.uid,t.PASSWORD,t.telephone,t.username from shop_user t;

去重查询

select distinct uid,PASSWORD,telephone,username from shop_user

查询的时候直接使用四则运算

select distinct uid*4,PASSWORD,telephone,username from shop_user

查询的时候重命名 (as 可以省略不写)

select distinct uid as ‘Id’,PASSWORD as ‘密码’,telephone as ‘电话’,username as ‘姓名’ from shop_user;

使用concat连接字符串得到自定义格式的查询结果

select concat (username,‘的密码是：’,password)result from shop_user

使用order by对查询的结果进行排序，asc升序，desc降序

select * from shop_user ORDER BY uid asc ,PASSWORD desc;

使用group by对数据进行分组

select * from shop_user GROUP BY username

组合使用 count 是统计查询出的条数

select count(*) from shop_user GROUP BY username ORDER BY username desc;

使用group_concat（）实现显示没个分组中的字段

select group_concat(uid)uid,group_concat(PASSWORD)PASSWORD,group_concat(telephone)telephone,username from shop_user GROUP BY username

如果要在分组查询中加入条件，则必须使用having而不是where

select * from shop_user GROUP BY uid HAVING uid<4

如果使用条件分组的同时还要排序，则order by必须位于having后边

select * from shop_user GROUP BY uid HAVING uid<4 ORDER BY uid desc

concat连接字符串

select concat (‘ID:’,uid,‘密码：’,PASSWORD,telephone,username) from shop_user

concat_ws使用分隔符连接字符串

select concat_ws(‘&&&’,uid,PASSWORD,telephone,username) from shop_user

length和char_length来获取字符串的长度

select LENGTH(“sssssssss”)

11.时间与时间戳的转换

获取当前的时间/时间戳

10位时间戳
select unix_timestamp(now());
13位时间戳
SELECT REPLACE(unix_timestamp(current_timestamp(3)),‘.’,‘’);
时间格式
select date_format(NOW(), ‘%Y-%m-%d %H:%i:%s’);

10位时间戳/时间格式转换

获取10位时间戳
select unix_timestamp(‘2016-01-02 00:00:00’);
获取时间
Select FROM_UNIXTIME(1451664000 )

13位时间戳/时间格式转换

获取13位时间戳(时间后面加.000)
SELECT REPLACE(unix_timestamp(‘2016-01-02 00:00:00.000’),‘.’,‘’);
获取时间
select FROM_UNIXTIME(round(1451664000000/1000,0))

14 树状结构一次查询（不推荐使用）

一次查询所有子类

SELECT
	DATA .id
FROM
	(
		SELECT
			@ids AS _ids,
			(
				SELECT
					@ids := GROUP_CONCAT(id)
				FROM
					表名
				WHERE
					FIND_IN_SET(parent_id, @ids)
			) AS cids,
			@l := @l + 1 AS LEVEL
		FROM
			表名,
			(SELECT @ids :=(1), @l := 0) b
		WHERE
			@ids IS NOT NULL
	) ID,表名 DATA
WHERE FIND_IN_SET(DATA.id, ID._ids)
ORDER BY	LEVEL,	id

一次查询所有父类（并且使用横杠拼接）


EXPLAIN SELECT
	id,
GROUP_CONCAT(DATA.NAME separator '-') AS name
FROM
	(
		SELECT
			@id AS _id,
			(
				SELECT
					@id := parent_id
				FROM
					表名
				WHERE
					id = @id
			) AS _pid,
			@l := @l + 1 AS LEVEL
		FROM
			表名,
			(SELECT @id :=(155), @l := 0) b
		WHERE
			@id > 0
	) ID,
	表名 DATA
WHERE
	ID._id = DATA .id
ORDER BY
	LEVEL DESC

FIND_IN_SET和Match+Against区别和使用

使用场景：某一列参数以”,”分隔如 (1,2,6,8) 查询时

EXPLAIN SELECT * FROM  表名  WHERE MATCH (字段 ) AGAINST ('值')

EXPLAIN SELECT * FROM 表名 WHERE FIND_IN_SET('值', 字段)

结论使用FIND_IN_SET不会走索引，大数据量情况下会影响效率
如果使用 Match+Against会走全文索引，前提是该列有全文索引

创建全文索引方式： 
ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(字段);

图片： 1: Match+Against 2：FIND_IN_SET
在这里插入图片描述

获取随机数

select RAND()；随机获取0-1的小数
小数范围取值

-- startNum 起始(最小)
-- endNum 目标（最大）
-- number 小数点后面位数
ROUND(startNum + RAND() * (endNum - startNum), number);
-- 生成 [0.1, 0.3]
select ROUND(0.1 + RAND() * (0.3 - 0.1), 2);

整数范围取值

y是最大范围，x是最小范围；
SELECT FLOOR(x + rand()*(y-x));
例如：生成100-1000的随机整数：
SELECT  FLOOR( 100 + RAND() * (1000 - 100)) ;

继续更新~~~

最后

以上就是花痴口红为你收集整理的MySql嵌套查询+关联查询+多表查询+对应案例+mybatis动态sql 超详细的全部内容，希望文章能够帮你解决MySql嵌套查询+关联查询+多表查询+对应案例+mybatis动态sql 超详细所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：随手记录的小知识点
浏览次数：81 次浏览
发布日期：2023-05-16 16:46:01
本文链接：https://www.kaopuke.com/article/k-p-k_14_ujo_2_f3_12__23_k2.html

MySql嵌套查询+关联查询+多表查询+对应案例+mybatis动态sql 超详细

概述

文章目录

先看我的数据库表 （直接看目录找需要的）

1. INNER JOIN 内连接

2 .LEFT JOIN 左查询

3. RIGHT JOIN 右查询

4. UNION 全外连接

5. LIMIT 分页查询

6. ORDER BY 排序查询

7. 聚合函数

8.分组查询

9.嵌套查询

1. IN 表示值是否存在子查询结果集中

2. EXISTS 是表示子查询是否返回结果，而不管返回的具体内容。

3.ALL表示子查询结果中的所有。

4.ANY是表示子查询结果中任意一个

10 LIKE模糊查询 LOCATE（）模糊搜索

11.mybatis的动态查询

1.autoMapping和autoMappingBehavior的区别

2. resultMap概述

3. resultType元素

4.动态sql

12、mysql的常用语法

插入一条数据

插入多条数据

插入查询结果)（把查询的结果插入到对应的表中）

去重查询

查询的时候 直接使用四则运算

查询的时候 重命名 (as 可以省略不写)

使用concat连接字符串得到自定义格式的查询结果

使用order by对查询的结果进行排序，asc升序，desc降序

使用group by对数据进行分组

组合使用 count 是统计查询出的条数

使用group_concat（）实现显示没个分组中的字段

如果要在分组查询中加入条件，则必须使用having而不是where

如果使用条件分组的同时还要排序，则order by必须位于having后边

concat连接字符串

concat_ws使用分隔符连接字符串

length和char_length来获取字符串的长度

11.时间与时间戳的转换

获取当前的时间/时间戳

10位时间戳/时间格式转换

13位时间戳/时间格式转换

14 树状结构一次查询（不推荐使用）

FIND_IN_SET和Match+Against区别和使用

获取随机数

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

先看我的数据库表（直接看目录找需要的）

查询的时候直接使用四则运算

查询的时候重命名 (as 可以省略不写)

发表评论取消回复