MySql嵌套查询+关联查询+多表查询+对应案例+mybatis动态sql 超详细,一看就会!!!

MySql 同时被 3 个专栏收录
2 篇文章 0 订阅
2 篇文章 0 订阅

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

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

文章目录

先看我的数据库表 (直接看目录找需要的)

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

  1. 老师职位表 tb_position
    在这里插入图片描述
  2. 学生表(为了好辨认效果) tb_student
    在这里插入图片描述
  3. 老师表 (id 班级 老师名字 职位对应表) tb_teacher
    在这里插入图片描述
  4. 老师与学生的关系对应表,毕竟有多种关系 多对多 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 分组后条件

  1. 对所有数据分组查询
-- 按教师的职位分类 查询 当前职业教师的个数
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.嵌套查询

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

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)

在这里插入图片描述

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

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

10 LIKE模糊查询 LOCATE()模糊搜索

  1. LIKE
-- 模糊查询   这里的 LIKE 后面的 %  需要注意
SELECT * FROM tb_student WHERE t_stu_name LIKE '%张%'
  1. 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概述

resultMapMybatis映射文件中最重要最强大的元素。它描述如何从结果集中加载对象,主要作用是定义映射规则、级联的更新、定制类型转化器。它可以让你从 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.时间与时间戳的转换

获取当前的时间/时间戳

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

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

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

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

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

会继续更新~~~

  • 3
    点赞
  • 4
    评论
  • 23
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 技术工厂 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值