mySQL(DQL多表连接查询)

目录

一. 笛卡尔集&内连接

 1.1 笛卡尔集

 1.2 内连接

二. 外连接

2.1 左外连接

2.2 右外连接

三. 子查询

四. 分页查询

五.  联合查询（union ）

---

一. 笛卡尔集&内连接

 1.1 笛卡尔集

产生条件：

1. 省略连接条件
2. 连接条件无效
3. 所有表中所有行互相连接

解决方案：

 添加有效筛选条件

 1.2 内连接

 SQL92语法：

        SELECT 查询列表
        FROM 表名1 别名1 ,表名2 别名2 
        WHERE 连接条件                 
        AND 筛选条件                
        GROUP BY 分组列表            
        HAVING 分组后筛选条件          
        ORDER BY 排序列表    

 SQL99语法：

        select 字段列表 
        from 表名1 
        [inner] join 表名2 on 条件
        where 筛选条件
        group by 分组条件
        having 分组后的筛选条件
        order by 排序字段

 注意：

    1. 使用表名前缀在多个表中区分相同的列
    2. 在不同表中具有相同列名的列可以用表的别名加以区分
    3. 如果使用了表别名，则在select语句中需要使用表别名代替表名
    4. 表别名最多支持32个字符长度，但建议越少越好

 例题：查询每个工种、每个部门的部门名、工种名和最低工资

SELECT `department_name`,`job_title`,MIN(salary)
FROM `departments` d
INNER JOIN `employees` e
ON e.`department_id`=d.`department_id`
INNER JOIN `jobs` j
ON e.`job_id`=j.`job_id`
GROUP BY j.`job_id`,d.`department_id`;

 

二. 外连接

2.1 左外连接

语法：

        select 字段列表 
        from 表1 
        left [outer] join 表2 on 条件
        ...

注意：       

右外连接查询的是右表所有数据以及其交集部分 

外连接一般用于除了交集部分剩余的不匹配的行

可能出现null值的表是次表

例题：查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充

SELECT bo.*,b.`id`,b.`name`
FROM `beauty` b
LEFT OUTER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.`id`>3;

2.2 右外连接

 语法：

       select 字段列表 
        from 表1 
        right [outer] join 表2 on 条件
        ...

 注意：

右外连接查询的是右表所有数据以及其交集部分    

 

三. 子查询

 概念

 查询中嵌套查询，称嵌套查询为子查询

 特点：

    1、子查询都放在小括号内
    2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的            右侧
    3、子查询优先于主查询执行，主查询使用了子查询的执行结果
    4、子查询根据查询结果的行数不同分为以下两类：
        ① 单行子查询
            结果集只有一行
            一般搭配单行操作符使用：> < = <> >= <= 
            非法使用子查询的情况：
                   a、子查询的结果为一组值
                b、子查询的结果为空
           ② 多行子查询
            结果集有多行
            一般搭配多行操作符使用：any、all、in、not in
            in： 属于子查询结果中的任意一个就行
            any和all往往可以用其他查询代替

 例题：查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

SELECT `employee_id`,`last_name`
FROM employees
WHERE `department_id`IN (
	SELECT DISTINCT `department_id`
	FROM employees
	WHERE `last_name` LIKE '%u%'
);

四. 分页查询

语法：

    select 字段|表达式,...
    from 表
    where 条件
    group by 分组字段
    having 条件
    order by 排序的字段
    limit 起始的条目索引，条目数;

 示例：每页显示3条记录

           SELECT * FROM student LIMIT 0,3; -- 第1页
        
          SELECT * FROM student LIMIT 3,3; -- 第2页
        
          SELECT * FROM student LIMIT 6,3; -- 第3页

特点：

    1.起始条目索引从0开始

    2.limit子句放在查询语句的最后

    3.公式：select * from  表 limit （page-1）*sizePerPage,sizePerPage
    假如:
        每页显示条目数sizePerPage
        要显示的页数 page

 

五.  联合查询（union ）

语法：

    select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
    select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
    select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
    .....
    select 字段|常量|表达式|函数 from 表 where 条件

特点： 

    1、多条查询语句的查询的列数必须是一致的
    2、多条查询语句的查询的列的类型几乎相同（最好相同）
    3、union代表去重，union all代表不去重

应用场景： 

要查询的结果来自于多个表，且多个表没有直接的链接关系，但查询的信息一致

 总结：

多表连接，主要找到表与表之间的链接关系