Mysql入门查询语句之内连接、外连接、子查询【Mysql数据库基础】

最新推荐文章于 2023-05-07 09:53:44 发布

liudachu

最新推荐文章于 2023-05-07 09:53:44 发布

阅读量355

点赞数

分类专栏： Mysql数据库文章标签：数据库 sql mysql sqlserver sqlite

本文链接：https://blog.csdn.net/liudachu/article/details/105939701

版权

Mysql数据库专栏收录该内容

14 篇文章 0 订阅

订阅专栏

1.查询结果集去重：distinct关键字

select distinct job from emp;
注意：distinct只能出现在所有字段的最前面。
select distinct deptno,job from emp;
案例：统计岗位的数量
select count(distinct job) from emp;

mysql> select count(distinct job) as '岗位数量' from emp;
+----------+
| 岗位数量 |
+----------+
|        5 |
+----------+

2.连接查询

2.1实际开发中，一个业务对应多张表，从多张表中联合查询取出结果。

  stuno    stuname    classno    classname
  --------------------------------------------------------
  1        张三         1       北京大学计算机专业1班
  2        李四         1       北京大学计算机专业1班

*学生和班级信息存储到一张表中，结果如上，数据存在大量的重复，导致数据的冗余。

2.2连接查询的分类

   根据表的连接方式来划分：
         内连接
	       等值连接
	       非等值连接
	       自连接
         外连接
	       左外连接(左连接)
	       右外连接(右连接)
	  全连接(很少用)

2.3笛卡尔乘积现象

当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。
案例：找出每一个员工的部门名称，要求显示员工名和部门名。
select e.ename,d.dname from emp e,dept d;
表别名的好处：1.执行效率好 2.可读性好
加条件进行过滤：（匹配次数没有发生改变,过滤了无效的记录）
select
e.name,d.dname;
from
emp e,dept d;
where
e.deptno=d.deptno;//SQL92,老语法

2.4 内连接之等值连接

最大特点是：条件是等量关系
案例：查询每个员工的部门名称，要求显示员工名和部门名。

    select 
	     e.name,d.dname
	 from
	     emp e
	 join    
	     dept d
	 on
	     e.deptno=d.deptno;//SQL99,新语法
+--------+------------+
| ename  | dname      |
+--------+------------+
| smith  | research   |
| allen  | sales      |
| ward   | sales      |
| jones  | research   |
| martin | sales      |
| blake  | sales      |
| clark  | accounting |
| scott  | research   |
| king   | accounting |
| turner | sales      |
| adams  | research   |
| james  | sales      |
| frod   | research   |
| miller | accounting |
+--------+------------+
14 rows in set (0.00 sec)

     
  SQL99语法优点：语法结构更清晰，表连接的条件和where条件分离了。

	    ...
	       A
(inner) join           //inner jion内连接，innner可以省略
	       B
	    on
	       连接条件
        where
	       ...

2.5 内连接之非等值连接

特点：连接条件中的关系是非等量关系
案例：找出每个员工的工资等级，要求显示员工名、工资。

mysql> select e.ename,e.sal,s.grade
    -> from
    -> emp e
    -> join
    -> salgrade s
    -> on
    -> e.sal between s.losal and s.hisal;//在最高薪资和最低薪资之间的。
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| smith  |  800.00 |     1 |
| allen  | 1600.00 |     3 |
| ward   | 1250.00 |     2 |
| jones  | 2975.00 |     4 |
| martin | 1250.00 |     2 |
| blake  | 2850.00 |     4 |
| clark  | 2450.00 |     4 |
| scott  | 3000.00 |     4 |
| king   | 5000.00 |     5 |
| turner | 1500.00 |     3 |
| adams  | 1100.00 |     1 |
| james  |  950.00 |     1 |
| frod   | 3000.00 |     4 |
| miller | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.00 sec)

2.7 自连接

特点：一张表看作两张表，自己连接自己
案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。
思路：员工的领导编号=领导的员工编号

   mysql> select a.ename as '员工名',b.ename as '领导名'
    -> from
    -> emp a
    -> join
    -> emp b
    -> on
    -> a.mgr=b.empno;
+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| scott  | jones  |
| frod   | jones  |
| ward   | blake  |
| martin | blake  |
| turner | blake  |
| james  | blake  |
| miller | clark  |
| adams  | scott  |
| jones  | king   |
| blake  | king   |
| clark  | king   |
| smith  | frod   |
| allen  | frod   |
+--------+--------+
13 rows in set (0.00 sec)

2.8 外连接

内连接和外连接的区别：

内连接：inner join  A 和 B 获得的是A和B的交集(intersect),即韦恩图(venn diagram) 相交的部分.

      假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接 
      AB两张表没有主副之分，两张表是平等的
      
外连接：outer join A和B获得的是A和B的并集(union), 即韦恩图(venn diagram)的所有部分.

       假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询
       主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配，副表自动模拟出NULL与之匹配。

外连接的分类：
左外连接(左连接)：表示左边的这张表是主表。
右外连接(右连接)：表示右边的这张表是主表。
左连接有右连接的写法，右连接也会有对应的左连接的写法。

案例：找出每个员工的上级领导，要求显示员工名和对应的领导名，没有上级领导也显示出来。

（左连接）                                    （右连接）          
     select                                     select 
        a.ename '员工',e.ename'领导'                a.ename''员工,e.ename'领导'
     from                                        from
        emp a                                       emp b
     left join                                   right join
        emp b                                       emp a
     on                                          on
        a.mgr=b.empno;                             a.mgr=b.empno;
     //(outer)可以省略
  
 结果相同： 
+--------+-------+
| 员工   | 领导  |
+--------+-------+
| scott  | jones |
| frod   | jones |
| ward   | blake |
| martin | blake |
| turner | blake |
| james  | blake |
| miller | clark |
| adams  | scott |
| jones  | king  |
| blake  | king  |
| clark  | king  |
| smith  | frod  |
| allen  | frod  |
| king   | NULL  |
+--------+-------+
14 rows in set (0.00 sec)

案例：找出那个部门没有员工

  select
	   d.*
	from
	  emp e
	join
	  dept d
	on
	  e.deptno=d.deptno
	where
	  e.empno is null;

2.9 三张表的连接查询

案例：找出每个员工的部门名称以及工资等级

mysql> select
    -> e.ename,d.dname,s.grade
    -> from emp e
    -> join dept d
    -> on e.deptno=d.deptno
    -> join salgrade s
    -> on e.sal between s.losal and s.hisal;
+--------+------------+-------+
| ename  | dname      | grade |
+--------+------------+-------+
| smith  | research   |     1 |
| adams  | research   |     1 |
| james  | sales      |     1 |
| ward   | sales      |     2 |
| martin | sales      |     2 |
| miller | accounting |     2 |
| allen  | sales      |     3 |
| turner | sales      |     3 |
| jones  | research   |     4 |
| blake  | sales      |     4 |
| clark  | accounting |     4 |
| scott  | research   |     4 |
| frod   | research   |     4 |
| king   | accounting |     5 |
+--------+------------+-------+

案例：找出每个员工的部门名称以及工资等级、以及上级领导,使用外连接。

mysql> select e.ename,d.dname,s.grade,e1.ename
    -> from emp e
    -> join dept d
    -> on e.deptno=d.deptno
    -> join salgrade s
    -> on e.sal between s.losal and s.hisal
    -> left join
    -> emp e1
    -> on e.mgr=e1.empno;
+--------+------------+-------+-------+
| ename  | dname      | grade | ename |
+--------+------------+-------+-------+
| scott  | research   |     4 | jones |
| frod   | research   |     4 | jones |
| james  | sales      |     1 | blake |
| ward   | sales      |     2 | blake |
| martin | sales      |     2 | blake |
| turner | sales      |     3 | blake |
| miller | accounting |     2 | clark |
| adams  | research   |     1 | scott |
| jones  | research   |     4 | king  |
| blake  | sales      |     4 | king  |
| clark  | accounting |     4 | king  |
| smith  | research   |     1 | frod  |
| allen  | sales      |     3 | frod  |
| king   | accounting |     5 | NULL  |
+--------+------------+-------+-------+
14 rows in set (0.00 sec)

3.子查询

3.1 select语句中嵌套select语句，被嵌套的select语句是子查询。

select
    ..(select)
from
    ..(select)
where
    ..(select)

3.2 where子句中使用子查询

案例：找出高出平均薪资的员工薪资
第一步：找出平均薪资
第二部：where过滤
select * from emp where sal>(select avg(sal) from emp);

mysql> select * from emp where sal>(select avg(sal) from emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | jones | manager   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | blake | manager   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | clark | manager   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | scott | analyst   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | king  | persident | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | frod  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
6 rows in set (0.00 sec)

3.3 from后面嵌套子查询

案例：找出每个部门平均薪资的薪资等级。
1.找出每个部门平均薪资(按照部门编号分组)
select deptno,avg(sal) from emp group by deptno;
2.以上查询结果当作临时表t,与等级表连接，条件：t.avgsal between s.losal and s.hisal.

mysql>  select t.*,s.grade
    -> from (select deptno,avg(sal)as avgsal from emp group by deptno)t
    -> join salgrade s
    -> on t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)

案例：找出每个部门薪资等级的平均值。

     1。找出每个员工的薪水等级
    select 
      e.ename,e.sal,e.deptho,s.grade
     from
     emp e
     join
     salgrade s
      on
     e.sal between s.losal and s.hisal
2.按照deptno分组，求grade平均值     
 mysql> select e.deptno,avg(s.grade)
   -> from emp e
   -> join
   -> salgrade s
   -> on
   -> e.sal between s.losal and s.hisal
   -> group by e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+
3 rows in set (0.00 sec)

4.union(可以将查询结果集相加)

案例：找出工作岗位是SALESMAN和MANAGER的员工
第一种：select ename.job from emp where job =‘MANAGER’ or ‘SALESMAN’;
第二种：select ename.job from emp where job in(MANAGER’ or ‘SALESMAN’);
第三种：union
select ename,job from emp where job=‘MANAGER’
union
select ename,job from emp where job=‘SALESMAN’;
注意：union两张不相干的表中的数据拼接在一起显示(数据列数必须相同)；

mysql> select ename,job from emp where job='manager'
    -> union
    -> select ename,job from emp where job='salesman';
+--------+----------+
| ename  | job      |
+--------+----------+
| jones  | manager  |
| blake  | manager  |
| clark  | manager  |
| allen  | salesman |
| ward   | salesman |
| martin | salesman |
| turner | salesman |
+--------+----------+
7 rows in set (0.00 sec)

5. limit (分页)

5.1 limit是myssql特有的，不通用
5.2 limit作用：取结果集中的部分数据
5.3 语法 limit startIndex,length
startIndex 表示起始位置
length 表示取几个
案例：取出工资前5名的员工(思路：降序取前5个)
select ename,sal from emp order by sal desc limit 0,5 //= limit 5

mysql> select ename,sal from emp order by sal desc limit 0,5;
+-------+---------+
| ename | sal     |
+-------+---------+
| king  | 5000.00 |
| frod  | 3000.00 |
| scott | 3000.00 |
| jones | 2975.00 |
| blake | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

5.4 limit是sql语句最后执行的一个环节

select 5
from 1
where 2
group by 3
having 4
order by 6
limit 7
5.5 案例：找出工资排名在第四到第九名的员工.
select ename,sal from emp order by sal limit 3,6

mysql> select ename,sal from emp order by sal limit 3,6;
+--------+---------+
| ename  | sal     |
+--------+---------+
| ward   | 1250.00 |
| martin | 1250.00 |
| miller | 1300.00 |
| turner | 1500.00 |
| allen  | 1600.00 |
| clark  | 2450.00 |
+--------+---------+
6 rows in set (0.00 sec)

5.6 通用的标准分页sql

*每页显示pageSize条记录：（pageSize：每页显示多少条记录）
第pageNo页：(pageNo-1)pageSize,pageSize

学习使用的案例表

emp员工表

emp

dept部门表

dept

salgrade工资等级表

liudachu

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Mysql入门查询语句之内连接、外连接、子查询【Mysql数据库基础】

MySQL（day02）1.查询结果集去重：distinct关键字2.连接查询2.2连接查询的分类2.3笛卡尔乘积现象2.4 内连接之等值连接2.5 内连接之非等值连接2.7 自连接2.8 外连接内连接和外连接的区别：案例：找出那个部门没有员工2.9 三张表的连接查询3.子查询4.union(可以将查询结果集相加)5. limit (分页)5.4 limit是sql语句最后执行的一个环节5.6 通...
复制链接

扫一扫