精华内容
下载资源
问答
  • mysql 关联查询

    千次阅读 2018-08-29 12:02:50
    数据库在通过连接两张或多张表来返回...所有查询出的结果都是能够在连接的表中有对应记录; t_employee表: t_dept表: select e.empName,d.deptName from t_employee e INNER JOIN t_dept d ON e.dept = ...

    数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。

    1、inner join:

    所有查询出的结果都是能够在连接的表中有对应记录;

    t_employee表:

    t_dept表:

    select 
    e.empName,d.deptName
    from t_employee e
    INNER JOIN t_dept d
    ON e.dept = d.id;
    

    结果

    2、left join:

    以左边的表的数据为基准,去匹配右边的表的数据,如果匹配到就显示,匹配不到就显示为null;

    SELECT e.empName,d.deptName
    from t_employee e
    LEFT OUTER JOIN t_dept d 
    on d.id = e.dept;
    

    3、right join:

    以右边的表的数据为基准,去匹配左边的表的数据,如果匹配到就显示,匹配不到就显示为null;

    SELECT e.empName,d.deptName
    from t_employee e
    RIGHT OUTER JOIN t_dept d 
    on d.id = e.dept;
    

    4、全外连接:

    把两张表的字段都查出来,没有对应的值就显示null,注意:mysql是没有全外连接的(mysql中没有full outer join关键字),想要达到全外连接的效果,可以使用union关键字连接左外连接和右外连接;

    select e.empName,d.deptName
         FROM t_employee e 
         left JOIN t_dept d
         ON e.dept = d.id
    UNION
    select e.empName,d.deptName
         FROM t_employee e 
         RIGHT JOIN t_dept d
         ON e.dept = d.id;
    

    5、自然连接:

    自连接查询就是当前表与自身的连接查询,关键点在于虚拟化出一张表给一个别名;

    SELECT e.empName,b.empName
         from t_employee e
         LEFT JOIN t_employee b
         ON e.bossId = b.id;
    

    在这里,b表是虚拟化出的表,我们可以通过查询了解b表的记录:

    SELECT e.empName,b.empName,b.*
         from t_employee e
         LEFT JOIN t_employee b
         ON e.bossId = b.id;
    

    6、关联查询时,on条件与where条件区别

    在使用left jion时,on和where条件的区别如下:

    1>on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。

    2>where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉

    表1:tab1

    id

    size

    1

    10

    2

    20

    3

    30

    表2:tab2 

    size

    name

     

    10

    AAA

    20

    BBB

    20

    CCC

    两条SQL:

    >select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’;            #1
    >select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’);
    #2

    第一条SQL的过程:

    1>中间表 on条件: tab1.size = tab2.size

    tab1.id tab1.size tab2.size tab2.name

    1

    10

    10

    AAA

    2

    20

    20

    BBB

    2

    20

    20

    CCC

    3

    30

    (null)

    (null)

    2>再对中间表过滤 where 条件: tab2.name=’AAA’

    tab1.id tab1.size tab2.size tab2.name

    1

    10

    10

    AAA

    第二条SQL的过程:

    1>中间表 on条件: tab1.size = tab2.size and tab2.name=’AAA’ (条件不为真也会返回左表中的记录)

    tab1.id tab1.size tab2.size tab2.name

    1

    10

    10

    AAA

    2

    20

    (null)

    (null)

    3

    30

    (null)

    (null)

    其实以上结果的关键原因就是left join,right join,full join的特殊性,不管on上的条件是否为真都会返回left或right表中的记录,full则具有left和right的特性的并集。 而inner jion没这个特殊性,则条件放在on中和where中,返回的结果集是相同的。

    可以这样理解:on是在生成连接表的起作用的,where是生成连接表之后对连接表再进行过滤。

    当使用left join时,无论on的条件是否满足,都会返回左表的所有记录,对于满足的条件的记录,两个表对应的记录会连接起来,对于不满足条件的记录,那右表字段全部是null

    当使用right join时,类似,只不过是全部返回右表的所有记录

    当使用inner join时,功能与where完全相同。

    更加深了对on和where的理解,得出以下结论:

    1>on后的条件如果有过滤主表的条件,则结果对于不符合该条件的主表数据也会原条数保留,只是不匹配右表数据而已。对于on后面对右表的过滤条件,连接时会用该条件直接过滤右表数据后再和左边进行左连接。总之,对于不满足on后面的所有条件的数据,左表会在结果数据中原条数保留数据,只是不匹配右表数据而已。不满足条件的右表数据各字段会直接以NULL连接主表。

    2>ON后对左表的筛选条件对于结果行数会被忽略,但会影响结果中的匹配右表数据,因为只有符合左表条件的数据才会去和符合条件的右表数据进行匹配,不符合条件的左表数据会保留在最后结果中,但匹配的右表数据都是NULL.因此,对于需要过滤左表数据的话,需要把过滤条件放到where后面。

    3>ON后的左表条件(单独对左表进行的筛选条件)对于结果行数无影响,还是会返回所有左表的数据,但和右表匹配数据时,系统只会拿左表符合条件(ON后的对左表过滤条件)的数据去和右表符合条件(ON后的对右表过滤条件)的数据进行匹配抓取数据,而不符合条件的左表数据还是会出现在结果列表中,只是对应的右表数据都是NULL。

    4>ON后的右表条件(单独对右表进行的筛选条件)会先对右表进行数据筛选后再和左表做连接查询,对结果行数有影响(当左表对右表是一对多时),但不会影响左表的显示行数,然后拿符合条件的右表数据去和符合条件的左表数据进行匹配。

    5>Where还是对连接后的数据进行过滤筛选,这个无异议。

    6>匹配数据时无论左右表,都是拿符合ON后的过滤条件去做数据匹配,不符合的会保留左表数据,用NULL填充右表数据。

    综上得出,ON后面对于左表的过滤条件,在最后结果行数中会被忽略,并不会先去过滤左表数据再连接查询,但是ON后的右表条件会先过滤右表数据再连接左表进行查询。

    连接查询时,都是用符合ON后的左右表的过滤条件的数据进行连接查询,只有符合左右表过滤条件的数据才能正确匹配,剩下的左表数据会正常出现在结果集中,但匹配的右表数据是NULL。因此对于左表的过滤条件切记要放到Where后,对于右表的过滤条件要看情况了。如果需要先过滤右表数据就把条件放到ON后面即可。

    7、on、where、having区别

    on、where、having这三个都可以加条件的子句中,on是最先执行,where次之,having最后。有时候如果这先后顺序不影响中间结果的话,那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的。         

    根据上面的分析,可以知道where也应该比having快点的,因为它过滤数据后才进行sum,所以having是最慢的。但也不是说having没用,因为有时在步骤3还没出来都不知道那个记录才符合要求时,就要用having了。         

    在两个表联接时才用on的,所以在一个表的时候,就剩下where跟having比较了。在这单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,只是where可以使用rushmore技术,而having就不能,在速度上后者要慢。

    如果要涉及到计算的字段,就表示在没计算之前,这个字段的值是不确定的,根据上篇写的工作流程,where的作用时间是在计算之前就完成的,而having就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。

    在多表联接查询时,on比where更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由where进行过滤,然后再计算,计算完后再由having进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在那里

     

    展开全文
  • MySQL关联查询

    千次阅读 2016-02-02 17:45:39
    在实际应用中,经常需要在一个查询语句中显示多张表的数据,这种多表数据记录连接查询,简称连接查询。关系数据操作连接操作是关系数据操作中专门用于数据库操作的关系运算。这里将先详细介绍关系数据操作中传统的三...

    在实际应用中,经常需要在一个查询语句中显示多张表的数据,这种多表数据记录关联查询,简称关联查询。

    关系数据操作

    连接操作是关系数据操作中专门用于数据库操作的关系运算。这里将先详细介绍关系数据操作中传统的三种运算:

    • 并(UNION)
    • 笛卡尔积(CARTESIAN PRODUCT)
    • 连接(JOIN)

    其中连接(JOIN)是专门针对数据库操作的运算。

    并(UNION)

    “并”操作就是把具有相同字段数目和字段类型的表合并到一起。比如存在两张用户表,其数据分别如下图所示:
    连接查询    连接查询

    将这两种用户表进行并操作后的数据如下:
    连接查询
    可以发现并操作去掉了重复记录。

    笛卡尔积(CARTESIAN PRODUCT)

    笛卡尔积就是没有连接条件表关系返回的结果。笛卡尔积新关系的字段数为各个表字段数目的和,记录数为各个表记录的积。如下图所示:
    连接查询

    连接(JOIN)

    为了便于用户的操作,专门提供了一种针对数据库操作的运算——连接(JOIN)。所谓连接就是在表关系的笛卡尔积数据记录中,按照相应字段值的比较条件进行选择生成一个新的关系。连接又分为三种:

    • 内连接(INNER JOIN)
    • 外连接(OUTER JOIN)
    • 交叉连接(CROSS JOIN)

    内连接(INNER JOIN)

    就是在表关系的笛卡尔积数据记录中,保留表关系中所有匹配的数据记录,舍弃不匹配的数据记录。按照匹配的条件可以分成三种:

    • 自然连接(NATURAL JOIN)
    • 等值连接
    • 不等连接

    这里将通过表示员工的表(t_employee)和表示部门的表(t_dept)来讲解各种内连接。这两张表的数据记录分别如下图所示:
    连接查询    连接查询

    自然连接操作就是表关系的笛卡尔积中,首先根据表关系中相同名称的字段自动进行记录匹配,然后去掉重复的字段。其笛卡尔积结果如下图:
    连接查询
    根据相同名称字段值进行匹配后得到上图中用红色方框标记出来的数据行。再除去重复字段后得到的结果如下:
    连接查询

    等值连接就是表关系的笛卡尔积中,选择所匹配字段值相等的记录,与自然连接相比保留重复字段,所以上面两个表等值连接后的数据如下图:
    连接查询

    不等连接就是表关系的笛卡尔积中所匹配字段不相等的数据记录,与等值连接的结果集互补,结果如下图:
    连接查询

    外连接(OUTER JOIN)

    外连接就是在表关系的笛卡尔积中,不仅保留表关系中所有匹配的数据记录,而且还会保留部分不匹配的数据记录。按照保留不匹配条件数据记录来源可以分为以下三种:

    • 左外连接(LEFT OUTER JOIN)
    • 右外连接(RIGHT OUTER JOIN)
    • 全外连接(FULL OUTER JOIN)

    这里将通过表示部门的表(t_dept)和表示员工的表(t_employee)来讲解各种外连接。这两张表的数据记录分别如下图所示:
    连接查询    连接查询

    左外连接就是表关系的笛卡尔积中,除了选择相匹配的数据记录,还包含关联左边表中不匹配的数据记录。上面两个表的笛卡尔积的结果如下:
    连接查询
    选择相匹配的数据记录,即上图中红色方框中的数据行。再关联左边表中不匹配的数据记录,即上图中红色细圈中的数据,去掉重复值。得出左外连接的结果如下:
    连接查询

    右外连接就是表关系的笛卡尔积中,除了选择相匹配的数据记录,还包含关联右边表中不匹配的数据记录。用上面的t_employee表右连接t_dept表,结果如下:
    连接查询

    全外连接就是表关系的笛卡尔积中,除了选择相匹配的数据记录,还包含关联左右两边表中不匹配的数据记录。

    内连接查询

    在MySQL中可以通过两种方式来实现连接查询,早期的语法形式如下:

    SELECT [field1, field2, ... ]
    FROM [table1, table2, ...]
    WHERE [condition1 AND|OR condition2 ...]
    

    FIELD表示要查询的字段,在FROM子句中用逗号(,)区分多个表,在WHERE子句中通过逻辑表达式来实现匹配条件。

    另一种是ANSI连接语法形式,其语法形式如下:

    SELECT [field1, field2, ... ]
    FROM table1 INNER JOIN table2 [INNER JOIN table3]
    ON [join condition]
    

    在FROM子句中使用“join … on …”关键字,连接条件写在关键字ON子句中。MySQL推荐使用该语法形式的连接。

    按照匹配情况,内连接查询可以分为 等值连接不等连接 两种,其中等值连接中存在一种特殊的情况叫自连接,这里将讲到这三种连接。

    这一节也将用到表t_dept和表t_employee进行讲解,不过与上面相比对表t_employee增加了表示上级编号的字段superior_no,两个表的数据如下:
    连接查询    连接查询

    自连接

    自连接就是指表与其自身进行连接,下面将通过具体的实例来说明。

    【实例6-1】查询员工的姓名和领导姓名。
    分析可知员工姓名和领导姓名都是表t_employee的emp_name字段,而领导关系表现在表中的emp_no和superior_no字段的对应关系。因为员工姓名和领导姓名都在同一张表中,因此需要将表进行自连接。具体SQL语句如下:

    SELECT e.emp_name employeename ,s.emp_name superiorname 
    FROM t_employee e INNER JOIN t_employee s 
    ON e.superior_no = s.emp_no;
    

    通过“SELECT FROM WHERE”关键字也可以实现,具体SQL语句如下:

    SELECT e.emp_name employeename, s.emp_name superiorname
    FROM t_employee e, t_employee s
    WHERE e.superior_no = s.emp_no;
    

    在MySQL中,提供了一种机制来为表取别名,将别名放在字段名或表名之后即可。其查询结果如下:
    连接查询
    比较t_employee表数据可知上级编号为NULL的数据行并不在结果集中。

    等值连接

    内连接查询中的等值连接,就是在关键字ON后面的匹配条件中通过关系运算符(=)来实现等值条件。

    【实例6-2】查询所有员工的编号、姓名和部门名称。
    分析可知员工编号和姓名在t_employee表中,部门名称在t_dept表中,并通过两个表中的dept_no字段进行关联。具体SQL语句如下:

    SELECT emp_no, emp_name, dept_name
    FROM t_employee e INNER JOIN t_dept d
    ON e.dept_no = d.dept_no;
    

    通过“SELECT FROM WHERE”关键字实现的SQL语句如下:

    SELECT emp_no, emp_name, dept_name 
    FROM t_employee e, t_dept d 
    WHERE e.dept_no = d.dept_no;
    

    上面例子是2个表的连接,下面通过一个实例来说明多表(3张表)等值连接。

    【实例6-3】查询有上级的员工的编号、姓名、部门名称、上级姓名。
    分析可知员工编号和姓名在t_employee表,上级姓名需要自连接t_employee表,部门名称在t_dept表中,涉及到3张表。具体SQL语句如下:

    SELECT e.emp_no, e.emp_name, dept_name, s.emp_name leadername 
    FROM t_employee e INNER JOIN t_dept d ON e.dept_no = d.dept_no 
    INNER JOIN t_employee s ON e.superior_no = s.emp_no;
    

    通过“SELECT FROM WHERE”关键字实现的SQL语句如下:

    SELECT e.emp_no, e.emp_name, dept_name, s.emp_name leadername 
    FROM t_employee e, t_dept d, t_employee s 
    WHERE e.dept_no = d.dept_no AND e.superior_no = s.emp_no;
    

    查询结果如下:
    连接查询

    不等连接

    内连接查询中的不等连接,就是在关键字ON后的匹配条件中除了可以有等于关系运算符来实现的条件外,还包含“>”,“>=”,“<”,“<=”,“!=”等运算符号,语法形式与等值连接相同,只是条件不同而已,这里不再举例说明。

    外连接查询

    在MySQL中,外连接查询会返回操作表中至少一个表的所有数据记录,通过SQL语句“OUTER JOIN … ON … ”来实现。其语法形式如下:

    SELECT [field1, field2, ... ]
    FROM table1 LEFT|RIGHT|FULL [OUTER] JOIN table2
    ON [join condition]
    

    外连接查询分为如下三类:

    • 左外连接
    • 右外连接
    • 全外连接

    左外连接

    上面有说道外连接查询会返回操作表中至少一个表的所有数据记录,左外连接就是指新关系中执行匹配条件时,以关键字LEFT [OUTER] JOIN左边的表为参考表。这里用表t_employee和表t_dept进行说明,表的数据如下:
    连接查询   连接查询

    【实例6-4】查询所有员工的编号、姓名和领导姓名。
    分析可知该查询仍然是t_employee表的自连接,但从数据中可以看出james位于公司最高级别,如果按照【实例6-2-2-1】中的内连接来查询,结果中是没有该数据信息的,所以这里要用到左外连接,其SQL语句如下:

    SELECT e.emp_no, e.emp_name, s.emp_name AS superiorname
    FROM t_employee e LEFT OUTER JOIN t_employee s 
    ON e.superior_no = s.emp_no
    

    其结果如下:
    连接查询
    可见左外连接把JOIN关键字左边的表数据全部显示出来了,没有匹配的数据则用NULL填充。

    右外连接

    右外连接就是指新关系中执行匹配条件时,以关键字RIGHT [OUTER] JOIN关键字右边的表为参考表。

    【实例6-5】查询所有员工的编号、姓名和部门名称。
    分析可知该查询需要关联表t_employee和表t_dept,其SQL语句如下:

    SELECT e.emp_no, e.emp_name, d.dept_name
    FROM t_employee e RIGHT OUTER JOIN t_dept d
    ON e.dept_no = d.dept_no
    

    由于t_dept表中部门编号为50的部门是没有员工的,所以右外连接操作下该部门的记录仍显示,左边数据用NULL填充,其结果如下:
    连接查询

    合并查询数据记录

    MySQL中通过关键字UNION来实现并操作,可将多个SELECT语句的查询结果合并在一起组成新的关系。其语法形式如下:

    SELECT field1, field2, ... fieldn FROM table1
    UNION | UNION ALL
    SELECT field1, field2, ... fieldn FROM table2
    ...
    

    这里通过两张消息表t_msg_2015和t_msg_2016对并操作进行说明,这两张表的数据如下图所示:
    连接查询    连接查询

    这两种表的字段数目和字段类型都相同,对两种表的结果集进行并操作,SQL语句如下:

    SELECT * FROM t_msg_2015 
    UNION 
    SELECT * FROM t_msg_2016;
    

    执行结果如下:
    连接查询

    结果集中把相同的记录给合并了,如果不想合并可用关键字UNION ALL,SQL语句如下:

    SELECT * FROM t_msg_2015 
    UNION ALL
    SELECT * FROM t_msg_2016;
    

    执行结果如下:
    连接查询

    子查询

    在MySQL中虽然可以通过连接查询实现多表数据查询,但是却不建议使用。因为连接查询的性能较差,因此出现了子查询来替代连接查询。

    在“6.1关系数据库操作”章节有讲到,关联查询的SQL语句在执行过程中,首先会对表进行笛卡尔积操作,然后再选取符合匹配条件的数据记录。如果关联查询中的表的数据记录很大,则在笛卡尔积操作时会产生大量的内存消耗,导致性能下降。

    所谓子查询,就是指在一个查询之中嵌套了其它的若干查询,即在一个SELECT查询语句的WHERE或FROM子句中包含一个SELECT查询语句。这些嵌套着的查询语句称之为子查询语句。WHERE子句中的子查询一般返回单行单列、多行单列、单行多列数据记录,而FROM子句中的子查询一般返回多行多列数据记录,可以当做一张临时表。由此可根据子查询的返回结果将其分为以下四种:

    • 单行单列子查询
    • 多行单列子查询
    • 单行多列子查询
    • 多行多列子查询

    单行单列子查询

    这里同样以表t_employee和表t_dept来做说明,其数据如下:
    连接查询  连接查询

    【实例6-6】查询出工资比tom高的员工的编号、姓名和工资。
    分析可知这里要先查出员工tom的工资,然后通过该值来查询工资比tom高的员工的信息,其SQL语句如下:

    SELECT emp_no, emp_name, sal
    FROM t_employee
    WHERE sal > (
        SELECT sal FROM t_employee WHERE emp_name = 'tom'
    );
    

    其查询结果如下:
    连接查询

    单行多列子查询

    WHERE子句中的子查询还可以返回单行多列的数据记录,不过这种子查询很少见。

    【实例6-7】查询出工资、部门编号都与jay相同的员工信息。
    分析可知先要查出tom的工资和部门编号,然后根据这两个值来查询,其SQL语句如下:

    SELECT *
    FROM t_employee
    WHERE (sal, dept_no) = (
        SELECT sal, dept_no FROM t_employee WHERE emp_name = 'tom'
    );
    

    查询结果如下:
    连接查询

    多行单列子查询

    该子查询语句一般会在主查询语句的WHERE子句里,通常会包含IN、ANY、ALL、EXISTS等关键字。

    1.带有关键字IN的子查询

    关键字IN表示查询条件为某个字段在某个结果集内。
    【实例6-8】查询出有员工的部门编号和部门名称。
    分析可知先要从t_employee表中查询出所有出现了的部门编号集合,再从部门表t_dept中查询处于这个集合中的部门信息,其SQL语句如下:

    SELECT dept_no, dept_name 
    FROM t_dept 
    WHERE dept_no IN (
    SELECT DISTINCT dept_no 
    FROM t_employee
    );
    

    关键字DISTINCT表示去掉重复记录,其查询结果如下:
    连接查询

    2.带有关键字ANY的子查询

    关键字ANY用来表示主查询的条件为满足子查询返回查询结果中的任意一条数据记录,该关键字有三种匹配方式,分别如下。

    • =ANY:功能与关键字IN相同;
    • >ANY(>=ANY):比子查询结果中最小的还要大(大于等于)的数据记录;
    • <ANY(<=ANY):比子查询结果中最大的还要小(小于等于)的数据记录;

    【实例6-9】查询员工编号、姓名和工资,这些员工的工资不低于部门编号为20的员工的工资。
    分析可知先要查询出部门编号为20的员工工资集合,然后查出工资大于等于集合中最小值的员工信息,其SQL如下:

    SELECT emp_no, emp_name, sal
    FROM t_employee 
    WHERE sal >= ANY (
        SELECT sal
        FROM t_employee
        WHERE dept_no = 20
    );
    

    查询结果如下:
    连接查询

    3.带有关键字ALL的子查询

    关键字ALL用来表示主查询的条件为满足子查询返回查询结果中所有数据记录,该关键字有一下两种匹配方式:

    • >ALL(>=ALL):比子查询结果中最大的还要大(大于等于)的数据记录;
    • <ALL(<=ALL):比子查询结果中最大的还要小(小于等于)的数据记录;

    【实例6-10】查询员工编号、姓名和工资,这些员工的工资高于部门编号为20的任何一位员工的工资。
    同样,先查询出部门编号为20的员工工资集合,然后查出工资大于集合中最大值的员工信息,其SQL如下:

    SELECT emp_no, emp_name, sal
    FROM t_employee 
    WHERE sal > ALL (
        SELECT sal
        FROM t_employee
        WHERE dept_no = 20
    );

    查询结果如下:
    连接查询

    4.带有关键字EXISTS的子查询

    关键字EXISTS是一个布尔类型,当子查询返回结果集时为TRUE,否则返回FALSE。查询时EXISTS对主查询中的表逐条查询,每次查询都会比较EXISTS的条件语句,当EXISTS条件语句返回TRUE时则返回主查询当前遍历到的记录,反之不返回。

    【实例6-11】查询部门下有员工的部门编号和部门名称。
    SQL语句如下。执行时遍历t_dept表中的每条记录,并判断EXISTS子查询中是否有返回结果。

    SELECT * 
    FROM t_dept d
    WHERE EXISTS (
        SELECT * 
        FROM t_employee e
        WHERE e.dept_no = d.dept_no
    );

    查询结果如下:
    连接查询

    多行多列子查询

    该类子查询一般会在主查询语句的FROM子句里,被当做一张临时表的方式来处理。

    【实例6-12】查询员工表t_employee中各个部门的编号、名称、员工人数和平均工资。
    分析可知先在t_employee表中查询出各个部门的编号、员工人数和平均工资,其SQL语句如下:

    SELECT dept_no, COUNT(emp_no) emp_sum, AVG(sal) average_sal
    FROM t_employee
    GROUP BY dept_no

    结果如下:
    连接查询

    再将该结果集看做一张临时表与部门信息表t_dept进行关联查询,其最终SQL语句如下:

    SELECT d.dept_name, e.dept_no, e.emp_sum, e.average_sal 
    FROM t_dept d INNER JOIN (
        SELECT dept_no, COUNT(emp_no) emp_sum, AVG(sal) average_sal
        FROM t_employee
        GROUP BY dept_no
    ) e
    ON d.dept_no = e.dept_no

    查询结果如下:
    连接查询

    展开全文
  • mysql关联查询查询优化

    千次阅读 2019-07-29 18:55:32
    mysql关联查询 参考:https://blog.csdn.net/lu0422/article/details/78892497 mysql查询慢的原因 记录慢查询日志 分析查询日志,不要直接打开慢查询日志进行分析,这样比较浪费时间和经历,可以使用pt-query-...

    mysql关联查询

     参考:https://blog.csdn.net/lu0422/article/details/78892497

    mysql查询慢的原因

    • 记录慢查询日志
      分析查询日志,不要直接打开慢查询日志进行分析,这样比较浪费时间和经历,可以使用pt-query-digest工具进行分析
    • 使用show profile
      set profileing = 1;开启,服务器上执行所有语句会检测消耗的时间,存到临时表中
      show profiles
      show profile for query Query_ID

     show profiles  查看所有查询消耗 的时间

    show profile for query Query_ID   查看某一条语句查询的具体消耗时间;

    • 使用show status
      show status会返回一些计数器,show global status 查看服务器级别的所有计数
      有时根据这些计数,可以猜测出那些操作代价较高或消耗时间多
    • show processlist
      观察是否有大量线程处于不正常的状态或特征
    • explain
      分析单条的SQL语句

      

    mysql查询优化的方式

    • 访问数据太多会导致查询性能下降(不要用select *)
      • 确定MySQL服务器是否在分析大量不必要的数据行
      • 确定应用程序是否在检索大量超过需要的数据,可能是太多行或列
      • 避免使用如下SQL语句
        • 查询不需要的数据,使用limit解决
        • 所表关联返回全部列,制定A.id,A.name,B.age
        • 总是取出全部列,SELECT * 会让优化器无法完成索引覆盖扫描的优化
        • 重复查询相同的数据,可以缓存数据,下次直接读取缓存
      • 是否在扫描额外的记录
        • 使用explain来进行分析,如果发现查询需要扫描大量的数据但是只返回少数行可以用如下技巧去优化:
          • 使用索引覆盖扫描,把所有用的列都放在索引中,这样存储引擎不需要回表获取对应行就可以返回结果
          • 改变数据库的表的结构,修改数据表范式(如果经常需要关联查询,可将另一张表的字段冗余地存储在要查询的表中,以空间换取时间)
          • 重写SQL语句,让优化器可以以更优的方式执行查询
    • 优化长难的查询语句
      • 切分查询
        将一个大的查询分为多个小的相同的查询
      • 分解关联查询
        可以将一条关联查询分解成多条SQL来执行,可以让缓存的效率更高,执行单个查询可以减少锁的竞争,在应用层做关联可以更容易对数据进行拆分,查询效率会有大幅提升,较少冗余记录的查询
    • 优化特定类型的查询语句
      • 优化count()查询
        • count(*)中的*会忽略所有的列,直接统计所有列数,因此不要使用count(列名)
        • MyISAM中,没有任何WHERE条件的count(*)非常快,当有WHERE条件时,MyISAM的count统计不一定比其他表引擎快
        • 可以使用explain查询近似值,用近似值代替count(*)
        • 增加汇总表
        • 使用缓存
      • 优化关联查询
        • 确定ON 或者 USING子句的列上有索引

          Mysql 中联接SQL语句中,ON子句的语法格式为:table1.column_name = table2.column_name。
          当模式设计对联接表的列采用了相同的命名样式时,就可以使用 USING 语法来简化 ON 语法,格式为:USING(column_name)。

        • 确保GROUP BY 和 ORDER BY中只有一个表中的列,这样MySQL才能使用索引
      • 优化子查询
        • 用关联查询替代
      • 优化GROUP BY 和 DISTINCT
        • 这两种查询均可以使用索引来优化,是最有效的优化方法
        • 关联查询中,使用表示列进行分组的效率会更高(尽量使用主键、自增列等)
        • 如果不需要ORDER BY ,进行GROUP BY 时使用ORDER BY NULL ,mysql不会再进行文件排序
      • 优化LIMIT分页
        • LIMIT偏移量大时,查询效率低
        • 可以记录上次查询的最大ID,下次查询时直接根据该ID来查询
      • 优化UNION查询
        • UNION ALL查询效率高于UNION

    引用的连接:https://www.cnblogs.com/clannadxr/p/7806090.html

    引用的连接:https://blog.csdn.net/qq_35571554/article/details/82800463

     

    展开全文
  • mysql联合查询关联查询

    千次阅读 2017-11-14 23:22:19
    最近面试中,Java的都会考察数据库,写SQL语句,大多数就问关联查询,索引看看下面三个关联查询的 SQL 语句有何区别?SELECT * FROM score, student WHERE score.id = student.id ... SELECT * FROM score JOIN ...

    最近面试中,Java的都会考察数据库,写SQL语句,大多数就问关联查询,索引

    看看下面三个关联查询的 SQL 语句有何区别?
    
    SELECT * FROM score, student WHERE score.id = student.id ...
    SELECT * FROM score JOIN student ON (score.id = student.id) WHERE ...
    SELECT * FROM score JOIN student USING (id) WHERE ...
    
    

    第一种为联合查询或者说并查询,前两种写法,只是写法不同而已,其功能完全相同;

    最后一种,会将同名的列,合并起来。
    第一种,是传统写法,SQL89标准里,就是这种;后面两种,是在SQL92标准才有的!

    
    mysql> select * from score;
    +----+-------+
    | id | score |
    +----+-------+
    |  1 |    60 |
    |  2 |    75 |
    |  4 |    90 |
    +----+-------+
    3 rows in set (0.00 sec)
    
    mysql> select * from student;
    +----+--------+
    | id | name   |
    +----+--------+
    |  1 | 张三   |
    |  2 | 李四   |
    |  3 | 王五   |
    +----+--------+
    3 rows in set (0.00 sec)
    
    mysql> select * from score, student where score.id=student.id;
    +----+-------+----+--------+
    | id | score | id | name   |
    +----+-------+----+--------+
    |  1 |    60 |  1 | 张三   |
    |  2 |    75 |  2 | 李四   |
    +----+-------+----+--------+
    2 rows in set (0.09 sec)
    
    mysql> select * from score join student on score.id=student.id;
    +----+-------+----+--------+
    | id | score | id | name   |
    +----+-------+----+--------+
    |  1 |    60 |  1 | 张三   |
    |  2 |    75 |  2 | 李四   |
    +----+-------+----+--------+
    2 rows in set (0.00 sec)
    
    mysql> select * from score join student using(id);
    +----+-------+--------+
    | id | score | name   |
    +----+-------+--------+
    |  1 |    60 | 张三   |
    |  2 |    75 | 李四   |
    +----+-------+--------+
    2 rows in set (0.00 sec)
    
    

    面试问题:

    mysql 查询一个表中没有存在在另一个表的数据,就是A表中有,B表中没有

    
    
    A表
    id content
    1 sfsdfsg
    2 ddsgffd
    3 gfggg
    4 dggfhfd
    
    B表
    
    id content
    
    2 ddsgffd
    3 gfggg
    

    那么我要查询到A表中 里面在B表中没有的数据(理想结果)就是:

    id content
    1 sfsdfsg
    4 dggfhfd
    
    select * from A where id not in select id from B;
    SELECT * FROM A WHERE NOT  EXISTS  ( SELECT 1 FROM B WHERE B.id = A.id );
    select * from A left join B on A.id = B.id where B.id is null;

    问题2:
    一句SQL查出所有课程成绩最高和最低的学生及其分数
    这里写图片描述
    这里写图片描述

    
    SELECT "最高分",student.*
    FROM student ,(SELECT MAX(score) AS score,`subject` FROM student GROUP BY `subject`)b
    WHERE student.`score` = b.score
    AND student.`subject` = b.subject
    UNION
    SELECT "最低分",student.*
    FROM student ,(SELECT MIN(score) AS score,`subject` FROM student GROUP BY `subject`)b
    WHERE student.`score` = b.score
    AND student.`subject` = b.subject; 

    http://blog.csdn.net/qq_29232943/article/details/78450950
    http://blog.csdn.net/gaoweizang/article/details/52859449
    http://blog.csdn.net/gaoweizang/article/details/52859449

    展开全文
  • mysql联合查询

    千次阅读 2011-12-07 20:03:35
    有一个前提很重要 就是两个表中的对应字段应该是建立联合关系且该键应唯一 在查找该联合建的时候要指明 表.字段 1. select * from 表A,表A子表 where 表A.FileCode = 表A子表.FileCode and 表A.ID in ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 303,358
精华内容 121,343
关键字:

mysql关联查询

mysql 订阅