MySQL学习笔记-2

连接查询

主要分为内连接(等值连接，非等值连接，自连接)，外连接（左外连接，右外连接，全外连接），交叉连接三大类。

sql92标准连接形式: from 表1，表2 ，表3 [where 条件1 and 条件2 and 题目条件] #笛卡尔连接
sql99标准连接形式： from 表1 join 表2 on 条件1 [ join 表3 on 条件2 where 题目条件]

• 内连接：表之间通过某种关系建立连接来进行关联查询。其中自连接是针对自身的两次表的查询，必须指定别名来区别查询时使用哪一次的表

• 外连接

  • 外连接主要用于添加 where语句后筛选：(主表中有但是副表中没有)'s 主表中的对象。不添加where 语句则筛选出：以主表为主的主副连接表

  • select 字段 
    from table_m 
    left [outer] join table_vice1 on 表间关联条件1 
    [left join table_vice2 on 关联条件2 ...] 
    where condition_clause;#条件语句一般为 ... is [not] null .
    

  • 与查询有关的关键字段属于哪个表，哪个表就是主表。

  • 例如：查询没有奖金的员工姓名，此时员工表e就为主表，奖金表s为副表。
    SELECT e.e_name FROM e LEFT JOIN s ON e.e_id=s.e_id WHERE s.e_id IS NULL;

  • right [outer] join 类似同理(主副表位置调换)

  • full [outer] join 全连接 ：两表交集(通过内连接可实现)+左外连接(A主B副)+左外连接(B主A副)

• 交叉连接：交叉连接是笛卡尔积在sql中的实现。 关键字：cross join

子查询(内查询)

• 定义：出现在其他语句中的select查询语句。
• 按照子查询出现的位置可以主要分为四类：
  • select语句中：仅仅支持标量子查询(查询结果为一行一列/单个字段)。
  • from语句中：支持表子查询，即将查询的结果集充当一张'新表'(必须起别名以供调用)。
  • where/having 条件语句：支持标量子查询(>.<,=,<>)；列子查询(单列多行：判断大小时与in语句，any/some语句或all语句嵌套使用。in可以改写为=any,not in 可以改写为<>all)；行子查询(较少应用)。
  • exists语句中(相关子查询)：表子查询。
• 子查询语句应包含在"()"内，且结束用 右括号"）"标识即可，不应再加分号表示子查询语句的结束，分号可能会被误认为是用来标识主查询语句的结束，会报错。

联合查询

• 关键字：Union 将多条查询语句的结果合并成一个结果。
• 应用场景; 要查询的结果来自多个表，且多个表之间没有直接的连接关系，但查询的信息一致时。
• 特点： 查询的字段数需相等；各字段类型与顺序最好保持一致；union 默认去重，若需包含重复项，可使用union all .

一些思考点

• (1) . group by语句的使用场景。
  多表连接查询时，若主查询的select字段中含有 count, sum, average等聚合函数，以 "查询每个部门的员工个数" 为例：

  • 若count字段作为外查询字段而直接显式存在，需要使用group by 语句呈现分类效果

    select d.* ,count(e.department_id) as 员工个数 
    from departments d 
    left join employees e on e.department_id =d.department_id  
    group by d.department_id;
    /* count(e.department_id) 不应书写为count(d.department_id),也不应书写为count(*),两者都会导致本没有员工的部门员工个数计为1。应充分理解外连接查询结果的含义，从而理解外查询count(para)字段中para的不同所对应的含义，当para为“*”时，是计算列数，空值也会计算在内并计为1，故当要求“没有员工的部门的员工个数既要正确书写为0，又不能省略员工个数为0的部门(简单的在e表中按照d_id分类count查询）”时正确操作应为：d表为主 e表为副 group by(d.department_id) 查询 count(e.department_id)  */
    

    假如无group by 子句，则查询结果将始终为单行，不管与count(*)同时查询的还有什么字段。如：
    
    或是(不合题，仅作示例)：
    

  • 若count字段作为子查询(内查询)隐式存在

    select d.* ,(select count(*) from employees e where e.department_id = d.department_id ) 员工个数 
    from departments d ;
    

    此时为内外嵌套，外在表现为单表查询。

    • 外为主，即d表为主，首先保证了部门的完整呈现，即不会像 ①e表为主，d表为副连接查询时count(*) ; ②内连接时count(*) 因为某些部门无员工而缺省该行
    • 外为主，即d表为主且 count查询字段隐式存在， 又保证了查询结果直接按照d表desc呈现，无需group by子句再进行分类。
    • 实际上，内在的来讲，该操作还是属于两表连接查询 (内连接)，只不过是没有向92的','或者99的'join'之类的关键字眼。(理解语句之间的执行顺序)

• (2). Exist 子查询的使用。(未充分理解)
  总结详细，可供参考 关键字: 执行顺序&规则，boolean，应用场景，效率。

• (3). 如题：查询各部门工资比所在部门平均工资高的员工的员工号，姓名，薪资

  • ① 第一想法: 内连接+where clause 子查询 关联三表 ，结果正确，表面也易理解。

    select d.department_id , d.department_name ,e.employee_id , e.last_name , e.salary 
    from employees e 
    join departments d on e.department_id = d.department_id  
    where salary > (select avg(salary) from employees em where em.department_id = e.department_id);
    

  • ② 将各部门的平均工资通过 select 子查询 命名'建表' ——davg。employees,departments,davg 三表左外连接 。

    • code如下:

    select d.department_id , d.department_name ,e.employee_id , e.last_name , e.salary , davg.a  as 该部平均工资
    from departments as d 
    left join employees as e on e.department_id = d.department_id 
    left join (select department_id , avg(salary) a from employees group by department_id ) as davg on davg.department_id = d.department_id where e.salary > davg.a 
    order by davg.a,e.salary;
    

    • 当然因为where语句的限制，某些没有员工的部门没有呈现出来。(当然题目也没有这种要求，但是理论上d为主表进行 left join 查询时所有的部门都应该呈现，即使对应的其余表为皆为null值）。通过删除where 限制，并按department_id DESC排列可以看到实际上三表连接后是存在这样的数据的。

      • code:

      ...
      on davg.department_id = d.department_id 
          order by d.department_id DESC 
          limit 0,50; 
      

      • 关于上述代码中的 Limit clause ，详见手册13.2.10 Select Statement.
        ① 基本语法：Limit [ offset ，] row_count ;
        ② 参数释义：The first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1)
        ③ 两参数一般皆为非负整数常量(在预处理语句以及其他语句中可能为其他值）；
        select 结果为 第offset+1行到第offset+row_cout行的数据；
        当offset为0时，该参数可省略。
        ④ MySQL 同样支持PostgreSQL中的分页语法：
        Limit row_count OFFSET offset
        ⑤ 关于 Limit 查询的优化细节。需要时可参见：
        手册8.2.1.19 LIMIT Query Optimization 一节

  • ③ 当然也可以省略select 字段中的department_name 字段，从而免去departments表与另外两表的连接。只将e表与davg表(通过select 子查询命名的各部门平均薪资表)内连接或者外连接。代码略。

• (4) 如题：查询平均工资最低的部门信息。

  • 错解1： 子查询所构建的表不识别，失效

    SELECT	d.*, davg.a 
    FROM
    	departments AS d
    	RIGHT JOIN ( SELECT department_id, avg( salary ) a FROM employees WHERE department_id IS NOT NULL GROUP BY department_id ) AS davg ON davg.department_id = d.department_id 
    WHERE
    	davg.a = ( SELECT MIN( davg.a ) FROM davg );
    

    • 现象：在上述写法中，除去where 语句，运行正常。添加后显示：
      Table 'my_employees.davg' doesn't exist。
    • ① 通过将where 子查询语句直接更改为‘TRUE’，发现虽然结果为空，但并没有报错。说明错误之处一定是where子查询语句，即where子查询中的davg不能识别
      ② 猜想：子查询中所调用的只能是当前系统已显式create并存储的当前数据库中的"全局表"，各个子查询之间是独立的，所以在right join 语句中所建的表davg不能在where 子查询语句中被识别。

  • 正解2: 可以使用 order by + limit 的结合语句来实现查询效果

    SELECT d.*, davg.a 
    FROM
    	departments AS d
    	RIGHT JOIN ( SELECT department_id, avg( salary ) a FROM employees WHERE department_id IS NOT NULL GROUP BY department_id ) AS davg ON davg.department_id = d.department_id #内连接更简？
    ORDER BY
    	davg.a ASC 
    	LIMIT 1;# 可修改行数确定是否最低值只有一个。
    

    该策略同样适用于: 其他有关 '最低，最高 '类字眼的查询 (在此不考虑效率问题)

  • 正解3：复杂嵌套1

    SELECT 	d.* 
    FROM  departments d 
    WHERE 	department_id = (    #此处 '=' 换用 'in' 更好
    	SELECT davg.department_id 
    	FROM  ( SELECT department_id, avg(salary) a 
                FROM employees 
                GROUP BY department_id
               ) AS davg 
    	WHERE a = ( SELECT MIN(该部平均工资)
                    FROM (SELECT avg(salary) AS 该部平均工资 
                          FROM employees 
                          GROUP BY department_id 
                          ) AS davg  
                    # 'AS davg' 必须有，语法要求。尽管前面select min(arg)不加davg也OK
                  )
    )
    # 两个子查询字段中的别名‘davg’互不影响，当然若写成不一样更易理解。
    

    此类嵌套查询解题思路：
    ①明确各个层次，清晰定义。② 从最内层，最小解题单元开始，逐步完善。③ 耐心，耐心。
    疑问：对于mysql中重复出现的语句有没有类似命名定义方法。

  • 正解4：复杂嵌套2

    SELECT 	d.* 
    FROM  	departments d 
    WHERE  	department_id = (   #此处 '=' 换用 'in' 更好
                SELECT department_id 
                FROM employees e 
                GROUP BY department_id  
                HAVING  AVG(salary) = ( 
                            SELECT MIN( a ) 
                            FROM ( SELECT AVG(salary) a 
                                  FROM employees 
                                  GROUP BY department_id
                                 ) AS davg 
                                       )
    						 )
    

    该解不同与正解3的地方在于Having 子句的使用替代了where子查询嵌套，需要理解的是：
    

    要真正理解group by 子句的所带来的效果/意义，以及各子句之间的执行顺序：
    ① group by 子句的使用必然与' 需使用聚合类函数的需求'有关(想不出没有使用聚合类函数的需求仍然使用group by 子句的)，但: 并不意味着聚合类函数一定[显式]出现在select 语句的字段中，它也可以出现在 having ,where 等子句中(执行顺序在它之后的就OK)
    ② 执行顺序：from 子句，join ta_name on cond类 子句 , group by 子句 , having 子句 , where 子句，select 子句 ，order by 子句，limit 子句。

  • 解5 存储过程？视图？