美文网首页数据库知识点程序员
SQL语句剖析——这句SQL到底在查什么?

SQL语句剖析——这句SQL到底在查什么?

作者: 浪尖的游鱼 | 来源:发表于2019-01-13 13:24 被阅读4次

    事先声明:此文章适合已经有基本的增删改查概念的用户。其语法基于Oracle(其实数据库在SQL语法方面都一样),如果对一些关键词有所不理解,比如WHERE、SELECT等等,请自行谷歌。谢谢。

    哇,吓一大跳!

    select major_name,studentCount,count(b.student_id) as jiuyrenshu ,count(b.student_id)/studentCount
    from

    (select m.major_name, count(s.student_id) as studentCount from student s,class_ c_,major m
    where s.class_id in c_.class_id and c_.grade_id = '001' and c_.major_id = '001' and m.major_id=c_.major_id
    group by m.major_name) a,
    (select distinct student_id from employment_register_society where verify_state=1
    union
    select distinct student_id from employment_register_school where verify_state=1) b

    group by major_name,studentCount;

    若能够一眼看出这句SQL的意思,这篇文章对于各位大佬只有巩固的作用了

    SELECT语句的执行规范

    DB会自觉的对提交的SQL语句进行语法和语义分析,并且优化SQL的执行效率。如果不是DBA的话,在书写SQL时只要考虑会不会写和有没有较优秀的执行效率就好。所以从开发者的视角出发,优先从SELECT开始,剖析下“开发者视角下的SQL执行规范”。

    无论如何复杂的SQL语句,每一步都可以看做:(备注:行筛选指的是对SQL查询结果进行每一条数据的筛选。列筛选是对SQL查询结果进行每一个字段的筛选)
    1.对结果集(虚拟表)进行筛选

    #WHERE
    SELECT * FROM [TABLE_A] WHERE [TABLE_A].[COLUMN_A] = "X";
    #这句SQL描述了从表TABLE_A中以[TABLE_A].[COLUMN_A] = "X"为条件,筛选出*(所有的)内容。这是两步:
    #第一步,WHERE条件对表TABLE_A进行行筛选;
    #第二步,SELECT条件对第一步产生的结果集进行列筛选。
    #ON
    SELECT [TABLE_A].[COLUMN_A],[TABLE_B].[COLUMN_B] FROM [TABLE_A] LEFT JOIN [TABLE_B] ON [TABLE_A].[COLUMN_A] = [TABLE_B].[COLUMN_B];
    #这句SQL描述了:
    #第一步:从表[TABLE_A]中关联上表[TABLE_B],两表以ON的内容为条件关联
    #第二步:以第一步的结果集(虚拟表)用SELECT进行列筛选
    #HAVING
    SELECT [TABLE_A].[COLUMN_A] FROM [TABLE_A] GROUP BY [TABLE_A].[COLUMN_A] HAVING MAX([TABLE_A].[COLUMN_B]) > 0;
    #这句SQL描述了:
    #第一步:GROUP BY对表TABLE_A这个结果集进行分组
    #第二步:HAVING对第一步分组完的结果集进行行筛选
    #第三步:SELECT对第二步筛选完的结果集进行列筛选
    

    2.对结果集(虚拟表)进行分组

    #GROUP BY
    SELECT MAX([TABLE_A].[COLUMN_B]) FROM [TABLE_A] GROUP BY [TABLE_A].[COLUMN_A]
    #这句SQL描述了:
    #1.第一步:对TABLE_A,以COLUMN_A字段进行分组
    #2.第二步:对于第一步的分组条件,筛选出每组最大的COLUMN_B
    

    3.对结果集(虚拟表)进行排序

    #ORDER BY
    SELECT [TABLE_A].[COLUMN_A] FROM [TABLE_A] ORDER BY [TABLE_A].[COLUMN_A]
    #这句SQL描述了:
    #第一步:从TABLE_A以SELECT的条件进行列筛选
    #第二步:以第一步的结果,根据COLUMN_A的内容进行排序
    

    这三步我们看完后,继续重新回到文章开头的那句SQL,他是怎么跑通的呢?

    1.student s,class_ c_,major m三张表,以WHERE为条件,进行列筛选
    2.GROUP BY以m.major_name为条件对第1步进行分组
    3.SELECT以count(s.student_id)为条件对第2步进行列筛选
    4.将第3步的结果集取别名 a
    5.表employment_register_society,以WHERE为条件,进行列筛选
    6.DISTINCT对第5步进行进行唯一性筛选(俗称去重)
    7.表employment_register_school,以WHERE为条件,进行列筛选
    8.DISTINCT对第7步进行进行唯一性筛选(俗称去重)
    9.用UNION拼凑第6步和第8步的结果集,取别名 b
    10.关联第4步和第9步的结果集
    11.GROUP以major_name,studentCount为条件对第10步结果集进行分组
    12.SELECT对第11步的结果集进行进一步运算和筛选

    复杂吗?其实这一点也不复杂。为什么前面提到大佬一眼就能看出文章开头的那句SQL的意思?其实简单点说:
    第二层引用在什么:一部分需要的数据在a,一部分需要的数据在b
    第一层引用在干嘛:把第一层引用的数据结合起来,再查需要的内容

    SO EASY!


    SELECT语句的执行流程

    上一段已经提到过了,SQL语句中的每一步都可以看做对结果集的一次操作,每一步都在根据1.表(结果集)之间的关系2.判断筛选3.排序展示,形成不同的结果集,然后下一步在上一步的结果集之下,在对结果集进行操作。这些操作有1.表(结果集)之间的关系2.判断筛选3.排序展示。理解了干什么(语义),下面就得知道怎么干(语法)。
    就像所有的程序语言一样,是将等号右边的赋值给等号左边。语法是约定俗称的。SQL语句可以建立在语法的基础上随意发挥。那么基础是?

    WITH {BLOCK}
    (7)SELECT (8) DISTINCT (10) <TOP_COUNT><SELECT_LIST>
    (3) FROM <TABLE(FROM)>
    (1) <JOIN_TYPE> JOIN <TABLE(JOIN)>
    (2) ON <CONDITION(JOIN)>
    (4) WHERE <CONDITION(FROM)>
    (5) GROUP BY <GROUP_BY_LIST>
    (6) HAVING <CONDITION(GROUP BY)>
    (9) ORDER BY <ORDER_BY_LIST>

    这里面WITH比较特殊先避开不谈,循序渐进的来(除特殊粗体字说明,每一步以上一步的结果集为准,进行进一步处理):
    1.2.[LEFT_TABLE] <JOIN_TYPE> JOIN [RIGHT_TABLE] ON <CONDITION(JOIN)>
    以WHERE的一张表为[LEFT_TABLE]以一定规则(左关联、全关联等等)JOIN上一张[RIGHT_TABLE],ON后面是两张表JOIN的条件。
    JOIN理解上难了点,后面会并上第四步一起讲。

    3.FROM [TABLE_A]
    FROM之后的单表形成一个结果集;或者多表强关联形成一个结果集。

    4.WHERE <CONDITION(FROM)>
    WHERE条件对FROM组成了的结果集进行筛选。
    以一个示例规范前四步:

    SELECT * FROM [TABLE_LEFT] LEFT JOIN [TABLE_RIGHT] ON <CONDITION(JOIN)>,[TABLE_A] WHERE [TABLE_A].[COLUMN_A] = [TABLE_RIGHT].[COLUMN_RIGHT]
    

    1.2.JOIN以一定规则(这里是LEFT JOIN),连接[TABLE_LEFT],[TABLE_RIGHT],连接条件是ON <CONDITION(JOIN)>。
    3.4.FROM以第一步的结果集和[TABLE_A]强关联(一般WHERE条件后的表逗号关联都这么说,或者叫笛卡尔积?),条件为WHERE <CONDITION(FROM)>,这里的WHERE可以以FROM形成的结果集内任何一个COLUMN为条件(尤其是不受SELECT影响,毕竟这会SELECT还未对结果集造成影响,所以SELECT对结果集的筛选这里也是没用的。所以别抱怨说为嘛SELECT的COUNT(1)在WHERE中不好下条件之类的)。

    5.6.GROUP BY <GROUP_BY_LIST> HAVING <CONDITION(GROUP BY)>
    对之前的结果集进行分群,以方便下面一些特殊函式的使用(例如COUNT、SUM、MAX都是常见的分群函数),HAVING则是只可以对分群进行条件筛选

    7.SELECT <SELECT_LIST>
    最容易理解的,对结果集进行列筛选。这里面常见的两个注意点就是:
    第一点:SELECT是在分群之后的结果集进行操作的,请注意。
    第二点:在SELECT中进行子查询时,其实就是遍历结果集的行,把每行的值传入从而得到结果。而SELECT只是对结果集进行筛选,返回多结果就会报错。

    8.DISTINCT
    DISTINCT去重。其唯一要关注的是,其是对SELECT完的结果集去重,即不是对DISTINCT后面接的第一个COLUMN去重,也不是对WHERE的结果集去重

    9.ORDER BY <ORDER_BY_LIST>
    ORDER BY排序。排序只可以以SELECT的结果集排序。被SELECT剔除的列不参加排序

    10.<TOP_COUNT>
    TOP在ORACLE是没有的,所以就没得用,但是记住顺序即可。

    11.WITH
    WITH虽说留到最后,其实也不是大头,WITH简单点说就是临时表。在书写WITH时会建立一个临时表为SELECT语句所用。这一点上很像可以直接作为结果集使用的VIEW(用的较少,如果要用WITH一般早建VIEW了)。

    好了,更具上面的顺序来说几件有趣的事

    • 按COLUMN_A排序,取前十条?
      由于ORACLE没有TOP,很多人喜欢用SELECT * FROM [TABLE_A] WHERE ROWNUM <=10 ORDER BY [COLUMN_A]
      这句话错到不能再错,因为WHERE在ORDER BY之前,所以这是先筛前十再排序。
    • 以一条SELECT语句为结果集?
      SELECT语句为什么能复杂到无以复加?正是因为,就上面这样的九步齐全的SELECT语句依然可以作为一个结果集参加另一个SELECT语句。上述凡是关键词包含TABLE的,都是可以被另一个结果集取代的。

    不多说,下面的语句用来复习上述内容,并不讲解。有问题可以留言。

    1.SELECT * FROM [TABLE_A];
    2.SELECT * FROM [TABLE_A] WHERE [TABLE_A].[COLUMN_A] = "X";
    3.SELECT * FROM [TABLE_A] JOIN [TABLE_B] ON [TABLE_A].[COLUMN_A] = [TABLE_B].[COLUMN_B] WHERE [TABLE_A].[COLUMN_A] = "X";
    4.SELECT [TABLE_A].[COLUMN_A],MAX([TABLE_A].[COLUMN_B]) FROM [TABLE_A] JOIN [TABLE_B] ON [TABLE_A].[COLUMN_A] = [TABLE_B].[COLUMN_B] WHERE [TABLE_A].[COLUMN_A] = "X" GROUP BY [TABLE_A].[COLUMN_A];
    5.WITH TABLE_NAME AS (SELECT * FROM [TABLE_C])
    SELECT [TABLE_C].[COLUMN_C],MAX([TABLE_A].[COLUMN_B]) FROM [TABLE_A] JOIN [TABLE_B] ON [TABLE_A].[COLUMN_A] = [TABLE_B].[COLUMN_B] WHERE [TABLE_A].[COLUMN_A] = [TABLE_C].[COLUMN_C] GROUP BY [TABLE_A].[COLUMN_A];
    6.SELECT [TABLE_A].[COLUMN_A],MAX([TABLE_A].[COLUMN_B]) FROM [TABLE_A] JOIN [TABLE_B] ON [TABLE_A].[COLUMN_A] = [TABLE_B].[COLUMN_B] GROUP BY [TABLE_A].[COLUMN_A] HAVING MAX([TABLE_A].[COLUMN_B]) > 0;
    7.SELECT DISTINCT [TABLE_A].[COLUMN_A],MAX([TABLE_A].[COLUMN_B]) FROM [TABLE_A] JOIN [TABLE_B] ON [TABLE_A].[COLUMN_A] = [TABLE_B].[COLUMN_B] WHERE [TABLE_A].[COLUMN_A] = "X" GROUP BY [TABLE_A].[COLUMN_A];
    8.SELECT DISTINCT [TABLE_A].[COLUMN_A],MAX([TABLE_A].[COLUMN_B]) FROM [TABLE_A] JOIN [TABLE_B] ON [TABLE_A].[COLUMN_A] = [TABLE_B].[COLUMN_B] WHERE [TABLE_A].[COLUMN_A] = "X" GROUP BY [TABLE_A].[COLUMN_A] ORDER BY [TABLE_A].[COLUMN_A];
    

    UPDATE?DELETE?INSERT?

    在书写UPDATE、DELETE、INSERT,原则是极简。原因很简单,这三者会动到表内容。
    所以:

    UPDATE

    UPDATE [TABLE] SET [TABLE].[COLUMN_B] = ? WHERE [TABLE].[COLUMN_A] = ?
    

    问号处补充具体的值,或者进一步更方便的写法,用子查询填充:

    UPDATE [TABLE_A] SET [TABLE_A].[COLUMN_B] = (SELECT [TABLE_B].[COLUMN_A] FROM [TABLE_B] WHERE [TABLE_B].[COLUMN_B] = [TABLE_A].[COLUMN_A])
    #当[TABLE_A]中[COLUMN_A]等于[TABLE_B]的[COLUMN_B],则更新[TABLE_A]中[COLUMN_A]为[TABLE_B]中的[COLUMN_A]
    #顶多再多写WHERE条件进行子查询筛选,不推荐多写复杂的
    

    DELETE

    DELETE FROM [TABLE_A] WHERE [TABLE_A].[COLUMN_B] = ?
    #顶多?处子查询,不能再多。
    

    INSERT

    INSERT INTO [TABLE_A](?) VALUES(?)
    INSERT INTO [TABLE_A](?) SELECT ? FROM ?
    #这个额外多说一句,在团体开发中,特别是表结构可能都没稳定的时候,一定要把INSERT的[TABLE_A](?)写具体了。这个问题听话就好,不多说
    

    MARGE?

    MARGE是ORACLE中的得力干将,简单点说,是用来解决下面的情况的:存在则UPDATE,不存在则INSERT。这里有兴趣的就谷歌搜一搜了。实际用处倒也不多。毕竟复杂的情况没法用,简单的情况用不到。

    CONNECT BY?

    这是一个复杂且常用的写法。不知大家对BOM的概念如何。不说这个数据结构树也是一种。再不说这个,在非关系数据库中,尤其是搜索引擎,特别喜欢层次数据结构,都与这个关键词脱不了干系。
    简单点说CONNECT BY是用来父子关系存在一个表中的理清树这种结构关系的,这种关系在产品结构、爬虫抓取的网页资料等十分常见。因为这边介绍基础,所以有兴趣的自己查吧。

    编程语言?

    CASE THEN WHEN END?
    SUBSTR?
    等等等等,SQL语句中还有很多操作,但这些操作就是很简单的了。基本明确一个概念,他们就是你给他传参,他给你回传值就好。其实就是简单的逻辑语句和函数应用而已。

    相关文章

      网友评论

        本文标题:SQL语句剖析——这句SQL到底在查什么?

        本文链接:https://www.haomeiwen.com/subject/ogomrqtx.html