MySQL复合查询

最新推荐文章于 2024-05-16 11:09:42 发布
最新推荐文章于 2024-05-16 11:09:42 发布
阅读量1.2k 收藏 31
点赞数 22
分类专栏： 数据库 文章标签： mysql 数据库 前端
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_74310945/article/details/137020100
版权

先看一下几张表结构，接下来的内容都是围绕这几张表来说的。

mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

1.基本查询

  • 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J
mysql> select * from emp where (sal > 500 or job = 'manager') and ename like 'J%';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007900 | JAMES | CLERK   | 7698 | 1981-12-03 00:00:00 |  950.00 | NULL |     30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)
  • 按照部门号升序而雇员的工资降序排序
mysql> select ename, sal, deptno from emp order by deptno asc, sal desc;
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| KING   | 5000.00 |     10 |
| CLARK  | 2450.00 |     10 |
| MILLER | 1300.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| FORD   | 3000.00 |     20 |
| JONES  | 2975.00 |     20 |
| ADAMS  | 1100.00 |     20 |
| SMITH  |  800.00 |     20 |
| BLAKE  | 2850.00 |     30 |
| ALLEN  | 1600.00 |     30 |
| TURNER | 1500.00 |     30 |
| WARD   | 1250.00 |     30 |
| MARTIN | 1250.00 |     30 |
| JAMES  |  950.00 |     30 |
+--------+---------+--------+
14 rows in set (0.00 sec)
  • 使用年薪进行降序排序

年薪 = 工资*12 + 奖金

mysql> select ename, sal*12+comm 年薪 from emp;
+--------+----------+
| ename  | 年薪     |
+--------+----------+
| SMITH  |     NULL |
| ALLEN  | 19500.00 |
| WARD   | 15500.00 |
| JONES  |     NULL |
| MARTIN | 16400.00 |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| TURNER | 18000.00 |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+
14 rows in set (0.00 sec)

如果使用这种方法的话，我们发现很多值都为null，因为有些人的奖金为null，而null是不参与运算的（null和任何数计算结果都为null），导致最后的结果是null。所以我们可以使用ifnull函数判断，如果结果为null，就不要加上奖金了

mysql> select ename, sal * 12 + ifnull(comm,0) 年薪 from emp;
+--------+----------+
| ename  | 年薪     |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19500.00 |
| WARD   | 15500.00 |
| JONES  | 35700.00 |
| MARTIN | 16400.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

得到结果之后再进行降序排序即可。

mysql> select ename, sal * 12 + ifnull(comm,0) 年薪 from emp order by 年薪 desc;
+--------+----------+
| ename  | 年薪     |
+--------+----------+
| KING   | 60000.00 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| JONES  | 35700.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| ALLEN  | 19500.00 |
| TURNER | 18000.00 |
| MARTIN | 16400.00 |
| MILLER | 15600.00 |
| WARD   | 15500.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| SMITH  |  9600.00 |
+--------+----------+
14 rows in set (0.00 sec)
  • 显示每个部门的平均工资和最高工资
mysql> select deptno, max(sal) 最高, avg(sal) 平均 from emp group by deptno;
+--------+---------+-------------+
| deptno | 最高    | 平均        |
+--------+---------+-------------+
|     10 | 5000.00 | 2916.666667 |
|     20 | 3000.00 | 2175.000000 |
|     30 | 2850.00 | 1566.666667 |
+--------+---------+-------------+
3 rows in set (0.00 sec)
  • 显示平均工资低于2000的部门号和它的平均工资
mysql> select deptno, avg(sal) 平均 from emp group by deptno;
+--------+-------------+
| deptno | 平均        |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

mysql> select deptno, avg(sal) 平均 from emp group by deptno having 平均 < 2000;
+--------+-------------+
| deptno | 平均        |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)

上面都是对前面内容的回顾，大家可以试一下，看看自己能不能写出来。

2.多表查询

实际开中往往数据来自不同的表，所以需要多表查询。

  • 案例：显示雇员名、雇员工资以及所在部门的名字

分析：雇员名，雇员工资在一张表中，而部门名在另一张表中，所以需要用到多表查询。

mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)


mysql> select * from emp, dept;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     10 | ACCOUNTING | NEW YORK |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     20 | RESEARCH   | DALLAS   |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     40 | OPERATIONS | BOSTON   |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     10 | ACCOUNTING | NEW YORK |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     20 | RESEARCH   | DALLAS   |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     40 | OPERATIONS | BOSTON   |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     10 | ACCOUNTING | NEW YORK |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     20 | RESEARCH   | DALLAS   |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     40 | OPERATIONS | BOSTON   |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     10 | ACCOUNTING | NEW YORK |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     20 | RESEARCH   | DALLAS   |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     40 | OPERATIONS | BOSTON   |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     20 | RESEARCH   | DALLAS   |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     30 | SALES      | CHICAGO  |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     40 | OPERATIONS | BOSTON   |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     20 | RESEARCH   | DALLAS   |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     30 | SALES      | CHICAGO  |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     40 | OPERATIONS | BOSTON   |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     10 | ACCOUNTING | NEW YORK |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     20 | RESEARCH   | DALLAS   |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     40 | OPERATIONS | BOSTON   |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     10 | ACCOUNTING | NEW YORK |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     20 | RESEARCH   | DALLAS   |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     40 | OPERATIONS | BOSTON   |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     20 | RESEARCH   | DALLAS   |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     30 | SALES      | CHICAGO  |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     40 | OPERATIONS | BOSTON   |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
56 rows in set (0.00 sec)

可以看到这就是两个表做完笛卡尔积之后的结果。实际就是将两个表合并成了一个表，但是为啥有这么多项呢。

两个表中每一项都和对面的每一项都要结合一次，所以新形成的表比原来的两个表更大。

但是仔细观察会发现表中很多数据都是没有用的。只有两个deptno相等的才是有效数据，所以我们需要进行筛选。

mysql> select * from emp, dept where emp.deptno = dept.deptno;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
14 rows in set (0.00 sec)

最终我们得到了这样一个表。

我们再来完成题目的要求

mysql> select ename, sal, dname from emp, dept where emp.deptno = dept.deptno;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| SMITH  |  800.00 | RESEARCH   |
| ALLEN  | 1600.00 | SALES      |
| WARD   | 1250.00 | SALES      |
| JONES  | 2975.00 | RESEARCH   |
| MARTIN | 1250.00 | SALES      |
| BLAKE  | 2850.00 | SALES      |
| CLARK  | 2450.00 | ACCOUNTING |
| SCOTT  | 3000.00 | RESEARCH   |
| KING   | 5000.00 | ACCOUNTING |
| TURNER | 1500.00 | SALES      |
| ADAMS  | 1100.00 | RESEARCH   |
| JAMES  |  950.00 | SALES      |
| FORD   | 3000.00 | RESEARCH   |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
14 rows in set (0.00 sec)
  • 显示部门号为10的部门名，员工名和工资

和上面的类似。

mysql> select emp.deptno, dname, ename, sal from emp, dept where emp.deptno = dept.deptno and emp.deptno = 10;
+--------+------------+--------+---------+
| deptno | dname      | ename  | sal     |
+--------+------------+--------+---------+
|     10 | ACCOUNTING | CLARK  | 2450.00 |
|     10 | ACCOUNTING | KING   | 5000.00 |
|     10 | ACCOUNTING | MILLER | 1300.00 |
+--------+------------+--------+---------+
3 rows in set (0.00 sec)
  • 显示各个员工的姓名，工资，及工资级别
mysql> select ename, sal, grade from emp, salgrade where losal <= sal and sal <= 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 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.00 sec)

3.子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

限制性内部的SQL，再执行外部的。

3.1单行子查询

  • 显示SMITH同一部门的员工

这个案例如果使用之前的查询方法不太好解决，所以我们可以试一下使用子查询的方法，将这个案例分为两步。

1.先找到smith属于哪一个部门

2.显示所有这个部门的成员。

mysql> select deptno from emp where ename = 'smith';
+--------+
| deptno |
+--------+
|     20 |
+--------+
1 row in set (0.00 sec)

找到了部门号为20之后，我们可以把这一长串的内容当作第二步的条件。

mysql> select ename, deptno from emp where deptno = (select deptno from emp where ename = 'smith');
+-------+--------+
| ename | deptno |
+-------+--------+
| SMITH |     20 |
| JONES |     20 |
| SCOTT |     20 |
| ADAMS |     20 |
| FORD  |     20 |
+-------+--------+
5 rows in set (0.00 sec)


mysql> select ename, deptno from emp where deptno = 20;
+-------+--------+
| ename | deptno |
+-------+--------+
| SMITH |     20 |
| JONES |     20 |
| SCOTT |     20 |
| ADAMS |     20 |
| FORD  |     20 |
+-------+--------+
5 rows in set (0.00 sec)

上面两种方法其实是一样的。最终都能完成题目的要求，但是使用子查询的方式就可以将两个句子合成一个句子。

  • 显示工资最高的员工的名字和工作岗位

1.先找工资最高的员工

2.显示他的名字和工作岗位。

mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.00 sec)
mysql> select ename, sal, job from emp where sal = (select max(sal) from emp);
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| KING  | 5000.00 | PRESIDENT |
+-------+---------+-----------+
1 row in set (0.00 sec)

  • 显示工资高于平均工资的员工信息

也是一样的步骤。

1.找到平均工资是多少。

2.显示高于平均工资的员工信息。

mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)

mysql> select ename, sal from emp where sal > (select avg(sal) from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)

3.2多行子查询

返回多行记录的子查询

3.2.1 in关键字

in关键字：判断一个列值是否在集合中。

  • 查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

意思就是在其他部门中找工作岗位与10号部门中相同（只要有一个相同即可）雇员信息。

在单行子查询中，内部的select语句最终结果都只有一行，所以可以使用 = ，而在多行子查询中，内部select语句的最终结果是有多行的，我们需要使用 in。

mysql> select job from emp where deptno = 10;
+-----------+
| job       |
+-----------+
| MANAGER   |
| PRESIDENT |
| CLERK     |
+-----------+
3 rows in set (0.00 sec)

结果多行，使用in

mysql> select ename, job, sal, deptno from emp where job = (select job from emp where deptno = 10) and deptno != 10;
ERROR 1242 (21000): Subquery returns more than 1 row

mysql> select ename, job, sal, deptno from emp where job in (select job from emp where deptno = 10) and deptno != 10;
+-------+---------+---------+--------+
| ename | job     | sal     | deptno |
+-------+---------+---------+--------+
| JONES | MANAGER | 2975.00 |     20 |
| BLAKE | MANAGER | 2850.00 |     30 |
| SMITH | CLERK   |  800.00 |     20 |
| ADAMS | CLERK   | 1100.00 |     20 |
| JAMES | CLERK   |  950.00 |     30 |
+-------+---------+---------+--------+
5 rows in set (0.00 sec)

3.2.2 all关键字

all关键字：判断与集合中所有值的关系

  • 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
mysql> select ename, sal, deptno from emp where sal > (select max(sal) from emp where deptno = 30);
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| JONES | 2975.00 |     20 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+
4 rows in set (0.00 sec)

mysql> select ename, sal, deptno from emp where sal > all(select sal from emp where deptno = 30);
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| JONES | 2975.00 |     20 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+
4 rows in set (0.00 sec)

第一种的思路是，先找到30号部门中工资最高的人，然后只要比工资最高的人还高，那就是比整个部门都高。

第二种思路就是运用all，比较集合中所有元素的值，如果都大，就是满足条件的。

3.2.3 any关键字

any关键字：与集合中任意一个元素之间的关系

  • 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）
mysql> select ename, sal, deptno from emp where sal > (select min(sal) from emp where deptno = 30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| CLARK  | 2450.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| KING   | 5000.00 |     10 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
| MILLER | 1300.00 |     10 |
+--------+---------+--------+
12 rows in set (0.00 sec)

mysql> select ename, sal, deptno from emp where sal > any(select min(sal) from emp where deptno = 30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| CLARK  | 2450.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| KING   | 5000.00 |     10 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
| MILLER | 1300.00 |     10 |
+--------+---------+--------+
12 rows in set (0.00 sec)

和上面那道题一样，方法一找到的是三十号部门中最小值，方法二是在表中找到任意一个满足条件的即可。

3.3多列子查询

单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句

  • 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

先找到smith所在的部门和岗位。

mysql> select job, deptno from emp where ename = 'smith';
+-------+--------+
| job   | deptno |
+-------+--------+
| CLERK |     20 |
+-------+--------+
1 row in set (0.00 sec)

由于返回的表有多列，所以需要用到多列子查询。

mysql> select ename, job, deptno from emp where (job, deptno) = (select job, deptno from emp where ename = 'smith') and ename != 'smith';
+-------+-------+--------+
| ename | job   | deptno |
+-------+-------+--------+
| ADAMS | CLERK |     20 |
+-------+-------+--------+
1 row in set (0.00 sec)

3.4在from子句中使用子查询

子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。

  • 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

先找到每一组的平均工资。

mysql> select avg(sal), deptno from emp group by deptno;
+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 2916.666667 |     10 |
| 2175.000000 |     20 |
| 1566.666667 |     30 |
+-------------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp, (select avg(sal) 平均, deptno from emp group by deptno) temp where emp.deptno = temp.deptno and emp.sal > temp.平均;
+--------+-------+-----------+------+---------------------+---------+--------+--------+-------------+--------+
| empno  | ename | job       | mgr  | hiredate            | sal     | comm   | deptno | 平均        | deptno |
+--------+-------+-----------+------+---------------------+---------+--------+--------+-------------+--------+
| 007839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |   NULL |     10 | 2916.666667 |     10 |
| 007566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |   NULL |     20 | 2175.000000 |     20 |
| 007788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |   NULL |     20 | 2175.000000 |     20 |
| 007902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |   NULL |     20 | 2175.000000 |     20 |
| 007499 | ALLEN | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 | 1566.666667 |     30 |
| 007698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |   NULL |     30 | 1566.666667 |     30 |
+--------+-------+-----------+------+---------------------+---------+--------+--------+-------------+--------+
6 rows in set (0.00 sec)

因为平均工资表和员工信息表在两张表中，我们可以使用多表查询，让两个表做笛卡尔积，形成一张表。再进行判断即可。

  • 查找每个部门工资最高的人的姓名、工资、部门、最高工资

先找到每个部门工资最高多少。

mysql> select deptno, max(sal) max_sal from emp group by deptno;
+--------+---------+
| deptno | max_sal |
+--------+---------+
|     10 | 5000.00 |
|     20 | 3000.00 |
|     30 | 2850.00 |
+--------+---------+
3 rows in set (0.00 sec)
mysql> select * from emp, (select deptno, max(sal) max_sal from emp group by deptno) temp where emp.deptno = temp.deptno;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+---------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | max_sal |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+---------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | 3000.00 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | 2850.00 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | 2850.00 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | 3000.00 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | 2850.00 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | 2850.00 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | 5000.00 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | 3000.00 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | 5000.00 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | 2850.00 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | 3000.00 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | 2850.00 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | 3000.00 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | 5000.00 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+---------+

将上面那张表和员工表做笛卡尔积之后，去掉没用的数据得到的结果。这下就变简单了，只要筛选条件即可。

mysql> select ename, sal, max_sal from emp, (select deptno, max(sal) max_sal from emp group by deptno) temp where emp.deptno = temp.deptno and sal = max_sal;
+-------+---------+---------+
| ename | sal     | max_sal |
+-------+---------+---------+
| BLAKE | 2850.00 | 2850.00 |
| SCOTT | 3000.00 | 3000.00 |
| KING  | 5000.00 | 5000.00 |
| FORD  | 3000.00 | 3000.00 |
+-------+---------+---------+
4 rows in set (0.00 sec)
  • 显示每个部门的信息（部门名，编号，地址）和人员数量

因为所需要的信息在两张表中，所以需要做笛卡尔积。员工表中找出人员数量，部门表中找出部门名，编号，地址。

我们先找出人员数量。

mysql> select deptno, count(*) dept_num from emp group by deptno;
+--------+----------+
| deptno | dept_num |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+
3 rows in set (0.00 sec)

再将两张表做笛卡尔积。

mysql> select dname, dept.deptno, loc, dept_num from dept, (select deptno, count(*) dept_num from emp group by deptno) temp where dept.deptno = teemp.deptno;
+------------+--------+----------+----------+
| dname      | deptno | loc      | dept_num |
+------------+--------+----------+----------+
| ACCOUNTING |     10 | NEW YORK |        3 |
| RESEARCH   |     20 | DALLAS   |        5 |
| SALES      |     30 | CHICAGO  |        6 |
+------------+--------+----------+----------+
3 rows in set (0.00 sec)

4.自链接

自连接是指在同一张表连接查询

  • 显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）

因为FORD的上级领导也在员工表内。所以需要用到自链接。

使用的子查询：

mysql> select empno, ename from emp where empno = (select mgr from emp where ename='FORD');
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec)

使用多表查询（自查询）：

mysql> select * from emp e1, emp e2 where e1.empno = e2.mgr and e2.ename = 'ford';
+--------+-------+---------+------+---------------------+---------+------+--------+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno | empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 | 007902 | FORD  | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+---------+------+---------------------+---------+------+--------+--------+-------+---------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)

其实和多表查询类似，只是做笛卡尔积的是同一张表。

5.合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all

5.1 union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。注意：select左右两边需要查询的字段必须是相同的。

  • 将工资大于2500或职位是MANAGER的人找出来
mysql> select ename, job, sal from emp where job = 'manager' or sal > 2500;
+-------+-----------+---------+
| ename | job       | sal     |
+-------+-----------+---------+
| JONES | MANAGER   | 2975.00 |
| BLAKE | MANAGER   | 2850.00 |
| CLARK | MANAGER   | 2450.00 |
| SCOTT | ANALYST   | 3000.00 |
| KING  | PRESIDENT | 5000.00 |
| FORD  | ANALYST   | 3000.00 |
+-------+-----------+---------+
6 rows in set (0.00 sec)

mysql> select ename, job, sal from emp where job = 'manager' union select ename, job, sal from emp where sal > 2500;
+-------+-----------+---------+
| ename | job       | sal     |
+-------+-----------+---------+
| JONES | MANAGER   | 2975.00 |
| BLAKE | MANAGER   | 2850.00 |
| CLARK | MANAGER   | 2450.00 |
| SCOTT | ANALYST   | 3000.00 |
| KING  | PRESIDENT | 5000.00 |
| FORD  | ANALYST   | 3000.00 |
+-------+-----------+---------+
6 rows in set (0.00 sec)

两种方法都可。

5.2 union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

  • 将工资大于2500或职位是MANAGER的人找出来
mysql> select ename, job, sal from emp where job = 'manager' union all select ename, job, sal from emp where sal > 2500;
+-------+-----------+---------+
| ename | job       | sal     |
+-------+-----------+---------+
| JONES | MANAGER   | 2975.00 |
| BLAKE | MANAGER   | 2850.00 |
| CLARK | MANAGER   | 2450.00 |
| JONES | MANAGER   | 2975.00 |
| BLAKE | MANAGER   | 2850.00 |
| SCOTT | ANALYST   | 3000.00 |
| KING  | PRESIDENT | 5000.00 |
| FORD  | ANALYST   | 3000.00 |
+-------+-----------+---------+
8 rows in set (0.00 sec)

后续的诗
关注
  • 22
    点赞
  • 踩
  • 31
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
MySQL复合查询（灰常重要o）
01-19
1 基本查询回顾 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J select * from EMP where (sal>500 or job='MANAGER') and ename like 'J%'; 按照部门号升序但雇员的工资降序排序 ...
MySQL连接查询实例详解
09-09
主要介绍了MySQL连接查询,结合实例形式总结分析了mysql表的创建、内连接、左连接、右连接、复合条件连接查询等相关操作技巧,需要的朋友可以参考下
MySQL复合查询(多表查询)
分享学习的知识，记录我的技术成长道路
10-17 1106
通常当进行自查询的时候，为了避免冲突，通常需要为表取别名，由于from是在select之前就执行了的，因此select中可以使用from中起的别名。多列子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句。书写复杂的查询过程，一定要先明确执行顺序，子查询是第一个执行的，from语句是第二个执行的，where是第三个执行的，select是最后执行的。在了解复合查询之前，我们需要先复习一下在单表中的查询：这些查询都是在oracle标准表中进行的。使用关键字进行比较。
mysql 复合查询
TH_NUM的博客
03-09 3073
MySQL 的复合查询或者嵌套查询，有表两张，要以 clrTheme 表两张为表列，将 clrColor 横向列出，故选择嵌套查询。 MySQL 复合嵌套查询命令如下 select * from clrTheme as t1, ( (select * from clrColor where `sort` = 0) as c1, (select * from clrColor...
【MySQL】复合查询
chuxinchangcun的博客
11-30 1128
然后再过滤出非法的数据, 必须保证:员工的部门编号=平均工资表的部门编号才有意义, 子查询是先被执行的,先有的avg_tb表,然后才进行非法数据过滤,所以可以用别名。先确定要的数据在哪些表,然后把这些表进行笛卡尔积,整合在一起, 多表就变成了一张表, 然后再根据连接条件对数据做清洗,过滤掉非法的数据, 然后再按条件进行筛选。做法:把两个表进行笛卡尔积,把数据穷举到一起, 然后根据连接条件:员工表的部门编号=部门表的编号, 把合法数据筛选出来, 然后根据条件筛选数据。
mysql 复合查询语句
weixin_30332705的博客
08-16 431
INSERT INTO runwa(rshottime,rmoney,renamecount) VALUES (CURDATE(),(select SUM(MONEY) from income where CREATESHOTTIME = CURDATE()),(select count(distinct hostname) from income where CREATESHOTTIM...
[MySQL] MySQL复合查询（多表查询、子查询）
weixin_67596609的博客
12-08 3172
前面我们学习了MySQL简单的单表查询。但是我们发现，在很多情况下单表查询并不能很好的满足我们的查询需求。本篇文章会重点讲解MySQL中的多表查询和一些复杂查询。希望本篇文章会对你有所帮助。
MySql常见复合查询(重点)
wtl666_6的博客
11-17 6582
MySql常见复合查询(重点)
MySQL —— 复合查询
M的博客
02-20 1080
MySQL —— 复合查询
MySQL——复合查询
qq_63943454的博客
12-23 2784
MySQL —— 复合查询，重点在于多表查询和子查询。
MySQL复合索引示例
天地孤影任我行的博客
07-17 646
虽然不符合最左匹配原则，但是发现还是使用了索引，这是因为建表语句一共四个字段，出了主键其他的字段组成联合索引，就意味着表里面的所有数据都能在联合索引里面找到，如果增加一个字段重新执行就会发现全表扫描了。ref只会读取匹配的行，当查询的列是一个索引的最左前缀或者是一个普通索引（不是主键索引或唯一索引）会使用。符合最左匹配原则，通过explain执行结果可以看到type=ref，key使用了联合索引。可以看到跟第一个执行结果一致，这是因为SQL语句经过优化器优化之后，依然符合最左匹配原则。.........
Mysql查询优化详解（含示例）
05-06
本文深入探讨了MySQL查询优化的多种策略和技巧，旨在帮助数据库管理员和开发人员提升MySQL数据库的性能。首先，介绍了使用EXPLAIN命令分析查询执行计划的重要性，以便识别潜在的性能瓶颈。接着，详细阐述了索引优化...
MySQL 创建主键,外键和复合主键的语句
09-11
MySQL 创建主键，外键和复合主键的方法，需要的朋友可以参考下。
python学习-使用pandas库分析excel表，并导出所需的表
最新发布
SixSix的自留地
05-16 97
使用pandas库分析excel表中多个子表的数据
mybatis 跨库查询 mysql
zzhongcy的专栏
05-11 400
跨库，表关联的查询，实现起来很简单：selecta.uidfrom只要在表的前边加上库名即可。
MySql软件安装
m0_62731842的博客
05-12 400
1.打开mysql官网网址 MySQL :: Download MySQL Community Server 2.本次针对版本8的图形化界面安装，下载成功后接下来对MySQL进行安装 3.图形化下载后有一个MSI文件 4.我们安装典型即可，选择第一个 5.选择数据库信息存放的路径，我默认放在C盘不做改变 6.MySQL平时作为一些应用的数据提供者，选择服务类型即可 7.端口号（暴露的端口号便于我们连接数据库，进行相关数据操作）可以修改，但我们平常使用的端口号是33
js遇到需要正则匹配来修改img标签+清除行内样式
阿乐的博客
05-14 239
遇到特殊的情况就是自带样式，那么方法一就不能用了，因为只会算是从原有字符串上面多加字符串。这个正则表达式就是匹配所有的。//踩坑完毕，可以直接使用。第二行代码按自己需要改改。
Docker 安装 MySQL（Mac电脑M芯片）
wenqi1992的博客
05-13 186
Docker 安装 MySQL，并用 DBeaver 工具连接MySQL
mysql复合条件查询
08-19
在MySQL中，你可以使用逻辑运算符和比较运算符来进行复合条件查询。 下面是一个示例，展示了如何在MySQL中进行复合条件查询： ```sql SELECT * FROM table_name WHERE condition1 AND condition2; ``` 在上面的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值