MySQL开发技巧(一)

MySQL开发技巧(一)

常用的SQL语句类型:

  • DDL 数据定义语言
  • TPL 事务处理语言
  • DCL 数据控制语言
  • DML 数据操作语言

正确使用SQL的重要性:

  • 增加数据库处理效率,减少应用相应时间
  • 减少数据库服务器负载,增加服务器稳定性
  • 减少服务器间通讯的网络流量

join类型

  • 内连接(INNER)
  • 全外连接(FULL OUTER)
  • 左外连接(LEFT OUTER)
  • 右外连接(RIGHT OUTER)
  • 交叉连接(CROSS)

join从句 – 内连接 (INNER)

西天取经四人组

iduser_nameover
1唐僧旃檀功德佛
2孙悟空斗战神佛
3猪八戒净坛使者
4沙僧金身罗汉

悟空的朋友们

iduser_nameover
1孙悟空成佛
2牛魔王被降服
3蛟魔王被降服
4鹏魔王被降服
5狮驼王被降服

内连接
内连接Inner join基于连接谓词将两张表(如A和B)的列组合在一起,产生新的结果表。可以用来交集。
在这里插入图片描述

SELECT a.user_name,a.over,b.over FROM user1 a INNER JOIN user2 b ON a.username=b.user_name;

   
   
  • 1

结果:
在这里插入图片描述

左外连接(LEFT OUTER)

左外连接有两种形式
一种是包含所有A表中的数据
在这里插入图片描述

select a.id,a.user_name, a.over, b.id, b.user_name, b.over 
from user1 a
left join user2 b
on a.user_name = b.user_name

   
   
  • 1
  • 2
  • 3
  • 4

结果:
在这里插入图片描述

一种是通过左外连接查询出只存在于A表中,不存在与B表中的数据
在这里插入图片描述

select a.id,a.user_name, a.over, b.id, b.user_name, b.over 
from user1 a
left join user2 b
on a.user_name = b.user_name
where b.user_name is null;

   
   
  • 1
  • 2
  • 3
  • 4
  • 5

结果:
在这里插入图片描述

右外连接(RIGHT OUTER)

以右表为基础,查询结果会包好右表中所有的记录。

在这里插入图片描述

在悟空朋友中有谁参与的取经:

select b.user_name,b.over,a.over
from user1 a
Right JOIN user2 b
on a.user_name = b.user_name
where a.user_name is not null;

   
   
  • 1
  • 2
  • 3
  • 4
  • 5

在这里插入图片描述

join从句—全连接(Full-Join)

全连接可以查询所有存在于A表和B表中的数据
或者查询只存在A表或B表中的数据

在这里插入图片描述

MySQL不支持全连接,可以使用union all来实现

select a.id,a.user_name, a.over, b.id, b.user_name, b.over 
from user1 a
left join user2 b
on a.user_name = b.user_name
union all
select a.id,a.user_name, a.over, b.id, b.user_name, b.over 
from user1 a
right join user2 b
on a.user_name = b.user_name;

   
   
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

结果:
在这里插入图片描述

join从句—交叉连接(cross join)

交叉连接(cross join),又称笛卡尔连接(cartesian join)或叉乘,如果A和B是连个集合,他们的交叉连接即为AXB
不需要提供连接关键词从句

select * from user1 a cross join user2 b;

   
   
  • 1

在这里插入图片描述

使用join更新表

如何更新包含在from从句中的表?

如下,把user1中的over字段改为’齐天大圣’,过滤条件使用了user1

UPDATE user1 
SET 
    over = '齐天大圣'
WHERE
    user1.user_name IN (SELECT 
            b.user_name
        FROM
            user1 a
                INNER JOIN
            user2 b ON a.user_name = b.user_name);

   
   
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

在MySQL中执行会出错:

Error Code: 1093. Table 'user1' is specified twice, both as a target for 'UPDATE' and as a separate source for data

   
   
  • 1

原因是:

if you're doing an UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query (you can however reference a field from that outer table...)

   
   
  • 1

所以正确的做法可以是:

update user1 a join (
    select b.user_name 
    from user1 a inner join user2 b on
    a.user_name = b.user_name
) b on a.user_name = b.user_name
set a.over = '齐天大圣';

   
   
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

或者

update user1 a inner join user2 b on a.user_name = b.user_name set a.over='齐天大圣';

   
   
  • 1

在这里插入图片描述

使用join优化子查询

如下使用了子查询语句,子查询比较耗时,可以使用join来优化子查询

select a.user_name, a.over, (
select over from user2 b where a.user_name = b.user_name
) as over2
from user1 a;

   
   
  • 1
  • 2
  • 3
  • 4

a表中的每一条记录都要进行子查询,非常低效

如果使用join连接,查询的结果一样,但是所用的时间更少

select a.user_name, a.over, b.over as over2 
from user1 a
left join user2 b on
a.user_name = b.user_name;

   
   
  • 1
  • 2
  • 3
  • 4

结果:
在这里插入图片描述

使用join优化聚合子查询

如何查询出四人组中打怪最多的日期?

引入一张新表,按日期记录四人组中每个人打怪的数量
表3 user_kills

iduser_idTimestrkills
132013-01-10 00:00:0010
232013-02-01 00:00:002
332013-02-05 00:00:0012
442013-01-10 00:00:003
532013-02-11 00:00:005
632013-02-06 00:00:001
722013-01-11 00:00:0020
832013-02-12 00:00:0010
932013-02-07 00:00:0017

原始方法如下:

select a.user_name, b.timestr, b.kills
from user1 a join user_kills b
on a.id = b.user_id
where b.kills=(select max(c.kills) from user_kills c where c.user_id=b.user_id);

   
   
  • 1
  • 2
  • 3
  • 4

在这里插入图片描述

验证不使用子查询只使用MAX()不正确

SELECT a.user_name,b.timestr,max(b.kills) from user1 a join user_kills b on a.id = b.user_id group by a.user_name,b.timestr;

   
   
  • 1

在这里插入图片描述
如何查询出四人组中打怪最多的日期?(避免子查询)

select a.user_name, b.timestr, b.kills
from user1 a
join user_kills b on a.id = b.user_id
join user_kills c on c.user_id = b.user_id
group by a.user_name, b.timestr, b.kills
having b.kills = max(c.kills);

   
   
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

使用了两次join,一次为了查询出时间和数量,另一次为了在having从句中使用来得出最大的数量是多少
在这里插入图片描述

如何实现分组选择数据

一些记录可以分成多个组,在每个组中选取一定数量的数据,就叫做分组选择

现在2张表
user1表中的数据如下:
在这里插入图片描述
user_kills表中的数据如下:
在这里插入图片描述
需求是:选择多条记录,比如阅读量最多的前几条
本例是每个人杀怪最多的头两天
对"孙悟空"而言查询语句如下:

select a.id, a.user_name, b.timestr, b.kills 
from user1 a join user_kills b
on a.id = b.user_id
where user_name = '孙悟空'
order by b.kills desc
limit 2;

   
   
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

查询结果为:
在这里插入图片描述
查询"猪八戒"时,把user_name换成"猪八戒"即可。

但这样做存在着一些问题:

  1. 如果分类或是用户很多的情况下则需要多次执行同一查询
  2. 增加应用程序同数据库的交互次数
  3. 增加了数据库执行查询的次数,不符合批处理的原则
  4. 增加了网络流量

优化方式一:
在这里插入图片描述

MySQL优化:分类聚合问题,一次性查询出所有结果:

select d.user_name, c.timestr, kills from
(
    select user_id, timestr, kills, (select count(*) from user_kills b 
    where b.user_id=a.user_id and a.kills<=b.kills) as cnt
    from user_kills a
    group by user_id, timestr, kills
) c join user1 d on c.user_id=d.id
where cnt<=2;

   
   
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值