最全MySQL8.0实战教程 11 MySQL的多表操作 11.2 多表联合查询【4 内连接查询】

小鱼儿 2024-03-26 12:57 19阅读 0赞

#### 最全MySQL8.0实战教程 ####

#### 文章目录 ####

*   *   *  最全MySQL8.0实战教程
         *  11 MySQL的多表操作
         *   *  11.2 多表联合查询
             *   *  11.2.4 内连接查询

[【黑马程序员MySQL知识精讲+mysql实战案例\_零基础mysql数据库入门到高级全套教程】 ][MySQL_mysql_mysql_]

#### 11 MySQL的多表操作 ####

##### 11.2 多表联合查询 #####

###### 11.2.4 内连接查询 ######

内连接查询求多张表的交集

![在这里插入图片描述][f9ea57801c2b4466b3249de8d3d9cd08.png_pic_center]

【格式】

隐式内连接（SQL92标准）：
    select * from A,B where 条件; 
    显示内连接（SQL99标准）：
    select * from A inner join B on 条件;

【操作】

-- 查询每个部门的所属员工
    select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
    select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;

![在这里插入图片描述][98d016cd5aa24f7995d63bf70c75cb68.png_pic_center]

-- 查询研发部和销售部的所属员工
    select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in('研发部','销售部');
    select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in('研发部','销售部');

![在这里插入图片描述][f0391d12972f4266b1738daa4cfa5aec.png_pic_center]

-- 查询每个部门的员工数,并升序排序
    select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
     
    select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;

![在这里插入图片描述][d0ea770d2c60473c844d11b09055192e.png_pic_center]

-- 查询人数大于等于3的部门，并按照人数降序排序
    select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
     
    select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;

![在这里插入图片描述][c7f5e899ce6c4a529b96b0c5aa96aba5.png_pic_center]

> 【注意】inner 可省略，一个意思
> 
> ![在这里插入图片描述][0fa3444084014ee090c60653df8eb0eb.png_pic_center]

[MySQL_mysql_mysql_]: https://www.bilibili.com/video/BV1iF411z7Pu
[f9ea57801c2b4466b3249de8d3d9cd08.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/d355c8c345ed4bcebc5eff1e8fba074f.png
[98d016cd5aa24f7995d63bf70c75cb68.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/cdd0aa7034534618b1036995f672dfca.png
[f0391d12972f4266b1738daa4cfa5aec.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/270ea2f33bed4d13ab6cb6a06da838f6.png
[d0ea770d2c60473c844d11b09055192e.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/423418b1984840a19ce6363daf5d2a70.png
[c7f5e899ce6c4a529b96b0c5aa96aba5.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/9ae03f411a134195ab5217dc91a29390.png
[0fa3444084014ee090c60653df8eb0eb.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/05624208185a4d60b671a447d051ea6c.png