Mysql中关于on,in,as,where的区别

目录
  • Mysql on,in,as,where的区别
  • Mysql语句问题解决
    • 1、left join数据筛选问题
    • 2、相同数据重复筛选使用问题
    • 3、根据某个字段排序取每个类别最后三条数据或前三条数据
    • 4、业务逻辑书写位置问题
    • 5、查找另一表内和本表相关字段的数量
    • 6、关于union的使用
    • 7、limit的巧用
    • 8、update ignore和insert ignore的使用
    • 9、mysql存在更新,不存在则插入
  • 总结

Mysql on,in,as,where的区别

答:Where查询条件,on内外连接时候用,as作为别名,in查询某值是否在某条件里

创建2个表:student,score

student:

score:

where

SELECT * FROM student WHERE s_sex='男'

例如:on

SELECT * FROM student LEFT JOIN score on student.s_id=score.s_id;

on和where组合:

SELECT * FROM student LEFT JOIN score on student.s_id=score.s_id WHERE s_name='赵雷' 

例如:in

SELECT * FROM score WHERE s_id in (SELECT s_id FROM student WHERE s_name='赵雷')

as

select * from score as a LEFT JOIN student as b on a.s_id=b.s_id where s_name='赵雷'

Mysql语句问题解决

1、left join数据筛选问题

on后面的条件只能对left join右边的表进行筛选,左表匹配不到右表数据会在原右表位置处显示null,left join左边的表数据不受约束,将on后的条件加到where后会对所有数据进行筛选。

2、相同数据重复筛选使用问题

with <name> as()

mysql内可以使用with as生成临时表,<name>为临时表的名字,使用如下:

with arc as( 
    select id,arc.title,update_time,is_top,cId,pid,name_id from article arc where is_del = 0 
) 
select * from arc

with...as的作用范围只有一次sql执行的时间,执行过后就不再存在,根据例子我们本要处理article表,但表里的数据并非都是我们需要的,所系先筛选建立了一个临时表arc,我们会对arc进行操作。

如果只是上述例子的简单操作是没必要使用with...as的,但是当我们需要将article表与其他表进行联查甚至嵌套时,会出现要多次进行is_del = 0的判断,最终出来的sql语句可能个十分复杂,并且极易出错,但使用arc就不需要在对数据进行重复筛选了。

with...as里的sql可以更复杂些,比如article表里有name_id,但更多时候我们希望使用name,我们可以预先在with...as内查找好,再使用临时表去做其他操作。

3、根据某个字段排序取每个类别最后三条数据或前三条数据

这算是个比较经典的一个问题了,我初学,只会一种解题方法,但会尽力讲的简单通俗点。

示例:

select * from ( 
select cId,title,content( 
    select count(*)+1 from arc a1 where (a1.cId = a2.cId) and a1.updateTime > a2.updateTime 
)updateTimeSort from arc a2 
) a3 
where updateTimeSort <= 3 order by cId,updateTime desc

示例中cId是类别id,updateTime 是更新时间,解决问题是选取arc内每个类别最晚更新的的三条数据,就像新闻的首页需要为每个分类选出最新的三条新闻,按照数据库里的数据我们可以使用排序 order by cId,updateTime desc 对数据按类别和更新时间进行排序,但去取每个类别的特定几条数据,现有数据库是做不到的,因此我们可以添加一个临时字段。

updateTimeSort 它表示的是每个类别中每个子项在这个类别中的排序,在当前问题中这个临时字段应该是和字段 updateTime 相关的,根据更新时间为类别中的每个子项排出顺序。

如示例代码,我们能找到a1和a2这两个表,他们都是arc表的别称,通过子查询的形式结合在一起,以a2为主,去a1表内查找类别和a2当前数据相同的,并且更新时间晚于a2当前数据的数据数量,能看到 count(*)+1 也就是数量加一了,不加一也可以,只是当一条数据在它所处类别更新时间最晚时count(*) 的值是0,若果使用count(*)+1 我们就可以将数据从1开始排序。

最终我们只要选取 updateTimeSort <= 3 的数据即可,如果想要筛选最早发布的新闻也只需要将updateTimeSort 的筛选逻辑变更一下即可,在示例代码中即将

a1.updateTime > a2.updateTime 更改为 a1.updateTime < a2.updateTime

可以看到示例代码中还有一个表a3,它其实时一个临时表,前面我们了解了with..as可以生成临时表,也重这次代码中可以看出,临时表也可以以另一种形式存在,with...as我们只有当sql复杂时才会使用,一般来说现在这种方式能帮我们解决不少问题了,各有优劣,看情况使用。

4、业务逻辑书写位置问题

接触sql多了会发现,sql其实能帮我们解决一定的业务问题,明显的有sql的存储过程和方法,对sql语句的批量处理其实在一定程度上帮我们解决一定的业务问题,但缺点也很明显,当新手接触这个项目时他很难搞清楚某个功能到底是如何实现的,不利于维护。

一般来说我们解决业务是在server层,有时会使用sql解决一些问题,但很少,在sever处理受制于计算机硬件,在数据库处理受制于数据库性能,相比之下,计算机硬件更易于扩展,因此还是不推荐大量使用sql解决问题的。

例如上个问题:根据某个字段排序取每个类别最后三条数据或前三条数据问题,虽然问题基本解决但让存在一些 ‘bug’,例如排序时会产生1、2、3、3、4这种排序,这是因为同个类别内有两条数据更新时间重复了,那我们直观想法(还是要看个人经验值)应该是,既然问题出在数据库,那应该在数据库查询的时候就解决这个问题,但事实上,让数据库去解决并不好解决,数据库的强项在于各种搜索算法,不在于逻辑处理,因此我们就要转移到server层处理,会有不少人陷于这个坑,花费大量时间去找办法让数据库去处理这类问题,但其实就算数据库处理得了,它也不一定有server层处理的效率高,当然如果是为了学习更多东西,这些时间也是值得花的,但是这种解题思路还是要改变下的。将1、2、3、3、4问题交给server处理也就是利用java等高级语言处理这种问题,相信熟用这些语言的开发者解决这些问题都是小case了。

5、查找另一表内和本表相关字段的数量

先复习下知识:用过count函数的人都清楚一旦使用count这类聚合函数,不做其他处理数据就会归为一行数据,但很多时候我们并不期望这样的结果,以此就要想些办法能用聚合函数,也能获取很多数据,我常用的是利用group by分组。

回归问题,现有(现不讨论表是否合理)文章表(id,title,content)有文章id,标题,文章内容三个字段,点赞收藏表(id,arc_id,fav,like)有表id,文章id,收藏字段(0未收藏,1收藏),点赞字段(0未点赞,1点赞),现要查询文章表内每篇文章的点赞收藏数,sql语句:

select art.title,art.content, 
count(case afl.fav when 1 then 1 end) as collectNum, 
count(case afl.like when 1 then 1 end) as likeNum 
from article art 
left join article_favor_like afl on afl.arc_id = art.id 
group by afl.arc_id //这是关键

如果没有group by afl.arc_id 后果就是,查出来一行数据,数据还牛头不对马嘴,但通过对文章收藏表中的文章id进行分组就可以针对每个文章id查询数据,这样left join时右表就有每个文章id对相应的收藏数与点赞数,而不是表内所有点赞数和收藏数,最终数据也是我们所需的。

6、关于union的使用

例子:

select id,title,content,1 isArc from arc 
union 
select id,name,content,0 isArc from news
  • 使用union进行的是上下整合
  • 被联合的数据列数要求一致
  • 列数相同,数据类型不同会自动进行数据类型转换
  • 联合后的列的名字由联合中第一次出现的列名为依据,即使后续被联合数据有自己的列名也不会使用,在例子中最终列名为:id,title,content,name等列名不会使用,因此使用union一般配合别名使用统一结果。
  • 有时候会区分数据是哪个表的,可以通过附加额外的字段来区别,就像例子中的isArc字段,news表中的isArc可以不写,原因也就是第4条,最终列名由第一次出现的列名决定,后续数据列名有没有都可以。

7、limit的巧用

limit一般用于分页,功能是获取指定区间内的数据,因此我们也可以用它来减少数据库的查询,例子:

select * from arc where id = 12 limit 1

数据库查询由索引还好,没有索引是要遍历数据库的,有些数据经由条件筛选在逻辑上应该是唯一的,使用limit 1可以使数据库查询到该数据时不再搜索,减少数据库搜索次数,但这种方法仅是一种技巧,想大幅度优化sql还要另想办法。

8、update ignore和insert ignore的使用

//标题是唯一索引,'新标题'存在则更新操作不执行 
update ignore arc set title = '新标题' 
 
//标题是唯一索引,'标题1号'存在则插入操作不执行 
insert ignore into arc values(null,'标题1号','文章内容')

有这种需求,数据存在时不执行任何操作,不存在则更新或插入,一个办法是使用ingore,它会忽略数据库报错,而数据库执行原子操作时报错是会回滚的,因此只要我们给数据加上主键或唯一索引,当被更新字段或插入字段与原有数据冲突时会报错,但因为ingore会忽视这种报错,后端也就不会报错,sql也未执行,达到了目的,有人会对报错敏感,其实也没什么,报错也是在检查数据是发现不合理之处给的一个提醒或警告,对数据库无害的。

9、mysql存在更新,不存在则插入

区别于上面那个需求,这个是当插入的数据存在时更新数据,不再是不做任何操作,例子:

//本例子中title不是唯一索引,id是主键 
insert into arc values(1,'标题1号','文章内容') 
on duplicate key update title='标题1号'
//若要更新多个字段使用','隔开,例:title='标题1号',content='文章内容'

在例子中,当id为1的数据存在时,更新标题和内容,不存在则插入,如果执行更新操作,未设置新值的字段保持原来的值。

还有一个REPLACE INTO也可以达到这种效果,区别在于,REPLACE INTO更新时是先删除后插入会破坏原有索引,id为3的数据更新时会删除插入id为4的数据,未更新新值的字段设置为默认值或null。

无论是两个中的哪种方式判断数据是否存在的依据都是主键和唯一索引。

总结

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。

(0)

相关推荐

  • sql和MySQL的语句执行顺序分析

    今天遇到一个问题就是mysql中insert into 和update以及delete语句中能使用as别名吗?目前还在查看,但是在查阅资料时发现了一些有益的知识,给大家分享一下,就是关于sql以及MySQL语句执行顺序: sql和mysql执行顺序,发现内部机制是一样的.最大区别是在别名的引用上. 一.sql执行顺序 (1)from (2) on (3) join (4) where (5)group by(开始使用select中的别名,后面的语句中都可以使用) (6) avg,sum....

  • Mysql inner join on的用法实例(必看)

    语法规则 SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name 先创建两个表,1.用户,2.用户类别 用户表 CREATE TABLE `user` ( `id` int(32) NOT NULL AUTO_INCREMENT, `name` varchar(16) NOT NULL, `kindid` int(32) NOT

  • MySQL中三种关联查询方式的简单比较

    看看下面三个关联查询的 SQL 语句有何区别? SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) SELECT * FROM film JOIN film_actor USING (film_id) SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id 最大的不同更多是语法糖,但有一些有意思的东西值得关注. 为了

  • MySQL中create table as 与like的区别分析

    本文分析了MySQL中create table as 与like的区别.分享给大家供大家参考,具体如下: 对于mysql的复制相同表结构方法,有create table as 和create table like 两种,区别是什么呢? 复制代码 代码如下: create table t2 as select * from t1 where 1=2; 或者 复制代码 代码如下: limit 0; as创建出来的t2表(新表)缺少t1表(源表)的索引信息,只有表结构相同,没有索引. 复制代码 代码如

  • MySQL中Decimal类型和Float Double的区别(详解)

    MySQL中存在float,double等非标准数据类型,也有decimal这种标准数据类型. 其区别在于,float,double等非标准类型,在DB中保存的是近似值,而Decimal则以字符串的形式保存数值. float,double类型是可以存浮点数(即小数类型),但是float有个坏处,当你给定的数据是整数的时候,那么它就以整数给你处理.这样我们在存取货币值的时候自然遇到问题,我的default值为:0.00而实际存储是0,同样我存取货币为12.00,实际存储是12. 幸好mysql提供

  • MySQL中索引与视图的用法与区别详解

    前言 本文主要给大家介绍了关于MySQL中索引与视图的使用与区别的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧. 索引 一.概述 所有的Mysql列类型都可以被索引. mysql支持BTREE索引.HASH索引.前缀索引.全文本索引(FULLTEXT)[只有MyISAM引擎支持,且仅限于char,varchar,text列].空间列索引[只有MyISAM引擎支持,且索引的字段必须非空],但不支持函数索引. MyISAM和InnoDB存储引擎的表默认创建BTREE索引,

  • MySQL中in与exists的使用及区别介绍

    先放一段代码 for(int i=0;i<1000;i++){ for(int j=0;j<5;j++){ System.out.println("hello"); } } for(int i=0;i<5;i++){ for(int j=0;j<1000;j++){ System.out.println("hello"); } } 分析以上代码可以看到两行代码除了循环的次序不一致意外,其他并无区别,在实际执行时两者所消耗的时间和空间应该也是一

  • MySQL中int (10) 和 int (11) 的区别

    mysql 中整数数据类型: 不同类型的取值范围: 不同数据类型的默认v显示宽度: 显示的宽度跟负号没有关系,它只在人工设置了 ZEROFILL 属性有效.一旦人工设置了 ZEROFILL 属性,MySQL 会自动设置 UNSIGNED 属性(即 ZEROFILL 不能存储负数). 那取值范围和显示宽度到底有什么关系呢?利用 tinyint 做了个实验, 首先创建一张表如下: mysql> desc test_integer; +-----------+------------+------+-

  • 浅谈mysql中int(1)和int(10)的区别

    目录 困惑 数据说话 零填充 总结 困惑 最近遇到个问题,有个表的要加个user_id字段,user_id字段可能很大,于是我提mysql工单alter table xxx ADD user_id int(1).领导看到我的sql工单,于是说:这int(1)怕是不够用吧,接下来是一通解释. 其实这不是我第一次遇到这样的问题了,其中不乏有工作5年以上的老司机.包括我经常在也看到同事也一直使用int(10),感觉用了int(1),字段的上限就被限制,真实情况肯定不是这样. 数据说话 我们知道在mys

  • mysql中的utf8与utf8mb4存储及区别

    目录 一.如何设置utf8mb4 二.问题 1.为什么存储的时候要区分utf8和utf8mb4 2.为什么读取的时候要区分utf8和utf8mb4 一.如何设置utf8mb4 mysql中针对字符串类型,在设置charset的时候可以精确到字段. 如果只将某个字段设置utf8mb4,那么其他字段不会受影响. 如果针对表来设置,那么已经存在的字段依然是utf8,并且会多出utf8的标记,之后所创建的字段才会是utf8mb4. 如果针对库来设置,那么已经存在的表依然是utf8,之后所创建的表才会是u

  • MySQL中int(10)和int(11)的区别详解

    目录 一.背景 二.MySQL整数类型 总结: 一.背景 在创建数据库表的时候,我们经常会用到int(x)来定义一个字段的类型,一直误以为这里的x表示存储数字的长度. 其实大错特错,这里的 x 指的是 最大显示宽度(最大有效显示宽度是255),且显示宽度与存储大小或类型包含的值的范围无关. 二.MySQL整数类型 类型 字节 取值范围 显示宽度 tinyint 1 -128 ~ 127 4 smallint 2 -32768 ~ 32767 6 mediumint 3 -8388608 ~ 83

  • MySQL中distinct和group by去重效率区别浅析

    目录 一.distinct 二.group by 三.distinct 和 group by 比较 总结 一.distinct distinct的作用 在mysql中,distinct关键字的主要作用就是对数据库表中一个或者多个字段重复的数据进行过滤,只返回其中的一条数据给用户,distinct只可以在select中使用 distinct的原理 distinct进行去重的主要原理是通过先对要进行去重的数据进行分组操作,然后从分组后的每组数据中去一条返回给客户端,在这个分组的过程可能会出现两种不同

  • mySQL中in查询与exists查询的区别小结

    一.关于exists查询 explain select * from vendor where EXISTS(select * from area where area_code = vendor_prov_code ) limit 10 以上是一个典型的exists查询的sql语句. 它的作用方式是这样的:每次从vendor表中查询出一条数据,然后将这条数据中的vendor_prov_code值传递到exists查询中进行执行,也就是进行子查询的执行. 如果子查询查到的数据就返回布尔值true

随机推荐