【MySQL】复合查询

于 2023-08-16 15:15:53 发布
阅读量571 收藏 4
点赞数 4
分类专栏： MySQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_66314292/article/details/132168745
版权

文章目录

一、基本查询

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)

1.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 * from emp where (sal > 500 or job='MANAGER') and substring(ename, 1, 1)='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)

1.2 按照部门号升序且员工工资降序进行排序

mysql> select * from emp order by empno ASC, sal DESC;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 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 ename, empno, sal from emp order by empno, sal DESC;
+--------+--------+---------+
| ename  | empno  | sal     |
+--------+--------+---------+
| SMITH  | 007369 |  800.00 |
| ALLEN  | 007499 | 1600.00 |
| WARD   | 007521 | 1250.00 |
| JONES  | 007566 | 2975.00 |
| MARTIN | 007654 | 1250.00 |
| BLAKE  | 007698 | 2850.00 |
| CLARK  | 007782 | 2450.00 |
| SCOTT  | 007788 | 3000.00 |
| KING   | 007839 | 5000.00 |
| TURNER | 007844 | 1500.00 |
| ADAMS  | 007876 | 1100.00 |
| JAMES  | 007900 |  950.00 |
| FORD   | 007902 | 3000.00 |
| MILLER | 007934 | 1300.00 |
+--------+--------+---------+
14 rows in set (0.00 sec)

1.3 使用年薪（月薪*12+奖金）进行降序排序

mysql> select ename, sal, comm  from emp order by (sal*12 + ifnull(comm, 0)) DESC;
+--------+---------+---------+
| ename  | sal     | comm    |
+--------+---------+---------+
| KING   | 5000.00 |    NULL |
| SCOTT  | 3000.00 |    NULL |
| FORD   | 3000.00 |    NULL |
| JONES  | 2975.00 |    NULL |
| BLAKE  | 2850.00 |    NULL |
| CLARK  | 2450.00 |    NULL |
| ALLEN  | 1600.00 |  300.00 |
| TURNER | 1500.00 |    0.00 |
| MARTIN | 1250.00 | 1400.00 |
| MILLER | 1300.00 |    NULL |
| WARD   | 1250.00 |  500.00 |
| ADAMS  | 1100.00 |    NULL |
| JAMES  |  950.00 |    NULL |
| SMITH  |  800.00 |    NULL |
+--------+---------+---------+
14 rows in set (0.00 sec)


# 把年薪显示出来
mysql> select ename, sal, comm, (sal * 12 + ifnull(comm, 0)) as 年薪 from emp order by 年薪
DESC;
+--------+---------+---------+----------+
| ename  | sal     | comm    | 年薪     |
+--------+---------+---------+----------+
| KING   | 5000.00 |    NULL | 60000.00 |
| SCOTT  | 3000.00 |    NULL | 36000.00 |
| FORD   | 3000.00 |    NULL | 36000.00 |
| JONES  | 2975.00 |    NULL | 35700.00 |
| BLAKE  | 2850.00 |    NULL | 34200.00 |
| CLARK  | 2450.00 |    NULL | 29400.00 |
| ALLEN  | 1600.00 |  300.00 | 19500.00 |
| TURNER | 1500.00 |    0.00 | 18000.00 |
| MARTIN | 1250.00 | 1400.00 | 16400.00 |
| MILLER | 1300.00 |    NULL | 15600.00 |
| WARD   | 1250.00 |  500.00 | 15500.00 |
| ADAMS  | 1100.00 |    NULL | 13200.00 |
| JAMES  |  950.00 |    NULL | 11400.00 |
| SMITH  |  800.00 |    NULL |  9600.00 |
+--------+---------+---------+----------+
14 rows in set (0.00 sec)

二、简单子查询

2.1 查找最高工资员工及其所在的部门

可以在SQL语句内部进行子查询，先执行内部SQL，再执行外部SQL。

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)

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

mysql> select * from emp where sal>(select avg(sal) from emp);
+--------+-------+-----------+------+---------------------+---------+------+--------+
| empno  | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+-----------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 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 |
| 007902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+-----------+------+---------------------+---------+------+--------+
6 rows in set (0.00 sec)

三、聚合查询

3.1 显示每个部门的平均工资和最高工资

mysql> select deptno, avg(sal) 平均, max(sal) 最高 from emp group by deptno;
+--------+-------------+---------+
| deptno | 平均        | 最高    |
+--------+-------------+---------+
|     10 | 2916.666667 | 5000.00 |
|     20 | 2175.000000 | 3000.00 |
|     30 | 1566.666667 | 2850.00 |
+--------+-------------+---------+
3 rows in set (0.00 sec)

# 为了美观可以保留精度
mysql> select deptno, format(avg(sal),2) 平均, format(max(sal), 2) 最高 from emp group by dep
ptno;
+--------+----------+----------+
| deptno | 平均     | 最高     |
+--------+----------+----------+
|     10 | 2,916.67 | 5,000.00 |
|     20 | 2,175.00 | 3,000.00 |
|     30 | 1,566.67 | 2,850.00 |
+--------+----------+----------+
3 rows in set (0.00 sec)

3.2 显示平均工资低于2000的部门号和它的平均工资

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

3.3 显示每种岗位的雇员总数及其平均工资

mysql> select job, count(ename) 雇员总数, avg(sal) 平均工资 from emp group by job;
+-----------+--------------+--------------+
| job       | 雇员总数     | 平均工资     |
+-----------+--------------+--------------+
| ANALYST   |            2 |  3000.000000 |
| CLERK     |            4 |  1037.500000 |
| MANAGER   |            3 |  2758.333333 |
| PRESIDENT |            1 |  5000.000000 |
| SALESMAN  |            4 |  1400.000000 |
+-----------+--------------+--------------+
5 rows in set (0.00 sec)

四、多表查询

实际上很多时候我们要查的数据可能不在同一张表，所以需要多表查询。
比方说一张表里面有2行信息，另一个表里面有3行信息，如果直接select * form 表1,表2那么就会在表中进行穷举组合（得到的结果称为笛卡尔积），生成6行信息。

在这里插入图片描述
可以看到SMITH这个人明明是20号部门的，但是也还是会拼接上其他部门的无效信息。

使用笛卡尔积后，后面必定紧跟一个where子句来确定多张表共同的字段，筛掉多张表中排列组合出来的无效数据，仅留下有效数据供程序员排查。
这样，多表查询问题就转化成了单表查询。

mysql> select * from emp as e, dept as d where e.deptno=d.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)

4.1 显示员工名、员工工资及其所在的部门名字

mysql> select ename,sal,dname from emp as e, dept as d where e.deptno=d.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)

4.2 显示部门号为10的部门名，员工名和工资

mysql> select e.deptno,dname,ename,sal from emp as e,dept as d where e.deptno=d.deptno and e.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)

4.3 显示各个员工的姓名，工资，及工资级别

mysql> select ename,sal,grade from emp,salgrade where sal>losal 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 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
12 rows in set (0.00 sec)

五、自连接

刚刚我们多表连接的时候使用两张表做笛卡尔积，那如果想对同一张表做笛卡尔积呢？

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

自连接是指在同一张表连接查询。
对同一张表做重命名，在同一张表连接查询

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

首先找到FORD领导的编号，然后再利用编号自查寻领导的姓名

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

# 自连接
mysql> select e2.ename from emp as e1, emp as e2 where e1.ename='FORD' and e1.mgr=e2.empno;
+-------+
| ename |
+-------+
| JONES |
+-------+
1 row in set (0.00 sec)

六、复杂自查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。
子查询在where子句中使用，充当判断条件，在from子句中出现，充当笛卡尔积。

6.1 在where子句子查询（条件判断）

6.1.1 单行子查询

显示SMITH同一部门的员工

select * from emp where deptno=(select deptno from emp where ename='SMITH');
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007369 | SMITH | CLERK   | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
| 007876 | ADAMS | CLERK   | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL |     20 |
| 007902 | FORD  | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+---------+------+---------------------+---------+------+--------+
5 rows in set (0.00 sec)

6.1.2多行子查询

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

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

mysql> select distinct ename,job,sal,deptno from emp where job in (select distinct 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)
  • 显示工资比部门30的所有员工的工资高的员工信息

all： >all()就表示比括号里面的所有数据都大

思路是先找到部门30的最高工资，再查找比他高的。

mysql> select * from emp where sal > (select max(sal) from emp where deptno=30);
+--------+-------+-----------+------+---------------------+---------+------+--------+
| empno  | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+-----------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 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 |
| 007902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+-----------+------+---------------------+---------+------+--------+
4 rows in set (0.00 sec)

mysql> select * from emp where sal > all(select sal from emp where deptno=30);
+--------+-------+-----------+------+---------------------+---------+------+--------+
| empno  | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+-----------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 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 |
| 007902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+-----------+------+---------------------+---------+------+--------+
4 rows in set (0.00 sec)
  • 显示工资比部门30的任意（最低）员工的工资高的员工信息

any： 表示任意

mysql> select * from emp where sal > any(select distinct sal from emp where deptno=30);
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 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 |
| 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 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
12 rows in set (0.00 sec)

6.1.3 多列子查询

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

  • 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
mysql> select ename from emp where (deptno, job)=(select deptno, job from emp where ename='SMITHH') and ename!= 'SMITH';
+-------+
| ename |
+-------+
| ADAMS |
+-------+
1 row in set (0.00 sec)

6.2 在from子句中使用子查询

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

# 先筛选出每个部门的平均工资
mysql> select deptno, avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

# 多表查询
mysql> select * from emp, (select deptno, avg(sal) from emp group by deptno) tmp;
………………

# 转变成单表信息
mysql> select * from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) 临时表 wheere 

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

# 单表查询
mysql> select ename, emp.deptno from emp, (select deptno, avg(sal) 平均工资 from emp group by deeptno) 临时表 where emp.deptno=临时表.deptno and emp.sal > 平均工资;
+-------+--------+
| ename | deptno |
+-------+--------+
| ALLEN |     30 |
| JONES |     20 |
| BLAKE |     30 |
| SCOTT |     20 |
| KING  |     10 |
| FORD  |     20 |
+-------+--------+
6 rows in set (0.00 sec)

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

这一问比上一问多了一个部门地点，涉及到多表查询。

做笛卡尔积然后筛选有效信息

# 做笛卡尔积
mysql> select * from dept, (select ename, emp.deptno from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) 临时表 where emp.deptno=临时表.deptno and emp.sal> 平均工资) t1;
+--------+------------+----------+-------+--------+
| deptno | dname      | loc      | ename | deptno |
+--------+------------+----------+-------+--------+
|     10 | ACCOUNTING | NEW YORK | ALLEN |     30 |
|     20 | RESEARCH   | DALLAS   | ALLEN |     30 |
|     30 | SALES      | CHICAGO  | ALLEN |     30 |
|     40 | OPERATIONS | BOSTON   | ALLEN |     30 |
|     10 | ACCOUNTING | NEW YORK | JONES |     20 |
|     20 | RESEARCH   | DALLAS   | JONES |     20 |
|     30 | SALES      | CHICAGO  | JONES |     20 |
|     40 | OPERATIONS | BOSTON   | JONES |     20 |
|     10 | ACCOUNTING | NEW YORK | BLAKE |     30 |
|     20 | RESEARCH   | DALLAS   | BLAKE |     30 |
|     30 | SALES      | CHICAGO  | BLAKE |     30 |
|     40 | OPERATIONS | BOSTON   | BLAKE |     30 |
|     10 | ACCOUNTING | NEW YORK | SCOTT |     20 |
|     20 | RESEARCH   | DALLAS   | SCOTT |     20 |
|     30 | SALES      | CHICAGO  | SCOTT |     20 |
|     40 | OPERATIONS | BOSTON   | SCOTT |     20 |
|     10 | ACCOUNTING | NEW YORK | KING  |     10 |
|     20 | RESEARCH   | DALLAS   | KING  |     10 |
|     30 | SALES      | CHICAGO  | KING  |     10 |
|     40 | OPERATIONS | BOSTON   | KING  |     10 |
|     10 | ACCOUNTING | NEW YORK | FORD  |     20 |
|     20 | RESEARCH   | DALLAS   | FORD  |     20 |
|     30 | SALES      | CHICAGO  | FORD  |     20 |
|     40 | OPERATIONS | BOSTON   | FORD  |     20 |
+--------+------------+----------+-------+--------+
24 rows in set (0.00 sec)

# 筛选有效信息
mysql> select t1.ename, dept.loc, t1.deptno from dept, (select ename, emp.deptno from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) 临时表 where emp.deptnoo=临时表.deptno and emp.sal > 平均工资) t1 where dept.deptno=t1.deptno;
+-------+----------+--------+
| ename | loc      | deptno |
+-------+----------+--------+
| ALLEN | CHICAGO  |     30 |
| JONES | DALLAS   |     20 |
| BLAKE | CHICAGO  |     30 |
| SCOTT | DALLAS   |     20 |
| KING  | NEW YORK |     10 |
| FORD  | DALLAS   |     20 |
+-------+----------+--------+
6 rows in set (0.01 sec)

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

# 通过group by聚合找出各部门的最高工资
mysql> select deptno,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) from emp group by deptno) 部门最高工资 where emp.deptno=部门最高工资.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 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+----------+
14 rows in set (0.00 sec)

# 在添加工资等于最高工资的条件
mysql> select ename, sal, emp.deptno, maxsal from emp, (select deptno,max(sal) maxsal from emp group by deptno) 部门最高工资 where emp.deptno=部门最高工资.deptno and 部门.高工资.maxsal=emp.sal;
+-------+---------+--------+---------+
| ename | sal     | deptno | maxsal  |
+-------+---------+--------+---------+
| BLAKE | 2850.00 |     30 | 2850.00 |
| SCOTT | 3000.00 |     20 | 3000.00 |
| KING  | 5000.00 |     10 | 5000.00 |
| FORD  | 3000.00 |     20 | 3000.00 |
+-------+---------+--------+---------+
4 rows in set (0.00 sec)

3.2.4 显示每个部门的信息（部门名，编号，地址）和人员数量

  • 多表方法

1️⃣ 先统计每个部门有多少人

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

2️⃣ 对上面的临时表进行笛卡尔积并过滤无效信息

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

3️⃣ 提取想要打印的信息

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

七、合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。注意左右两边的select所查询的字段要相同。

7.1 union（并集+去重）

在这里插入图片描述
合并成个整体并且对蓝色部分去重。

  • 找出工资大于2500或职位是MANAGER的人
mysql> select * from emp where sal > 2500 union select * from emp where job='MANAGER';
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
6 rows in set (0.00 sec)

7.2 union all（并集+不去重）

  • 将工资大于25000或职位是MANAGER的人找出来（取并集）
mysql> select * from emp where sal > 2500 union all select * from emp where job='MANAGER';
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
8 rows in set (0.01 sec)
命由己造～
关注
  • 4
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
数据库之复合查询
你又来看我了，一起学习吧
07-27 1250
文章目录1. 基本查询回顾2. 多表查询3. 自连接4. 子查询4.1 单行子查询4.2 多行子查询4.3 多列子查询4.4 在from子句中使用子查询4.5 合并查询4.5.1 union4.5.3 union all 1. 基本查询回顾 显示工资最高的员工姓名和其工作岗位 select ename,job from EMP where sal=(select max(sal) from EM...
MySQL复合查询（灰常重要o）
01-19
1 基本查询回顾 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J select * from EMP where (sal>500 or job='MANAGER') and ename like 'J%'; 按照部门号升序但雇员的工资降序排序 ...
MySQL复合查询
冰果滴的博客
07-25 933
经过前面的学习，MySQL的基本操作都已经掌握了。之前介绍的是对于一张表进行查询，但是仅仅查询一张表是不够的，所以从这一篇开始要介绍多张表时如何查询。
mysql 复合查询
TH_NUM的博客
03-09 3093
MySQL 的复合查询或者嵌套查询，有表两张，要以 clrTheme 表两张为表列，将 clrColor 横向列出，故选择嵌套查询。 MySQL 复合嵌套查询命令如下 select * from clrTheme as t1, ( (select * from clrColor where `sort` = 0) as c1, (select * from clrColor...
MySQL连接查询实例详解
09-09
在本篇文章中，我们将通过实例详细讲解MySQL中的连接查询，包括表的创建、内连接、左连接、右连接以及复合条件连接查询。 首先，创建表是进行连接查询的前提。例如，我们创建了名为`suppliers`的供应商表，用于存储...
Mysql查询优化详解（含示例）
最新发布
05-06
### MySQL查询优化详解 #### 一、使用EXPLAIN分析查询 **EXPLAIN** 是MySQL中一个非常有用的工具，用于分析SQL语句的执行计划。它能够揭示MySQL如何处理查询，帮助我们理解查询的效率和潜在的性能瓶颈。下面是一些...
MySQL千万级大数据SQL查询优化知识点总结
09-08
MySQL千万级大数据SQL查询优化是数据库管理中的关键环节，尤其对于处理海量数据的应用来说，高效的查询性能至关重要。以下是一些核心的优化知识点： 1. **建立索引**：索引可以显著提高查询速度，特别是在`WHERE`和...
MySQL 创建主键,外键和复合主键的语句
09-11
本文将详细介绍如何在MySQL中创建主键、外键以及复合主键。 1. **创建主键** 主键是数据库表中一个或一组字段，其值在全表中具有唯一性，并且不能为空。它用于唯一标识表中的每一行记录。在MySQL中，创建主键的...
MySql复合查询
weixin_53946312的博客
04-27 226
复合查询基本查询多表查询自连接子查询单行子查询多行子查询in关键字all关键字any关键字多列子查询在from子句中使用子查询合并查询unionunion all 基本查询 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J select * from EMP where (sal>500 or job='MANAGER') and ename like 'J%'; 按照部门号升序而雇员的工资降序排序 select * from EMP order by dept
MySQL的复合查询
qq_45967533的博客
08-13 926
包含多表查询，子查询，合并查询，内外连接的查询方法
mysql复合条件查询
08-19
在MySQL中，你可以使用逻辑运算符和比较运算符来进行复合条件查询。 下面是一个示例，展示了如何在MySQL中进行复合条件查询： ```sql SELECT * FROM table_name WHERE condition1 AND condition2; ``` 在上面的示例中，`table_name` 是你要查询的表名，`condition1` 和 `condition2` 是你的复合条件。 你可以使用逻辑运算符 `AND` 来连接多个条件，要求同时满足这些条件。你还可以使用其他逻辑运算符，如 `OR` 或 `NOT`，根据你的需求。 以下是一个具体的示例： ```sql SELECT * FROM customers WHERE age > 25 AND city = 'New York'; ``` 上述示例将从名为 `customers` 的表中选择满足条件的记录：年龄大于25岁且所在城市为纽约的客户信息。 请根据你的具体需求，将 `table_name` 替换为你要查询的表名，将 `condition1`、`condition2` 等替换为你的实际条件。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

命由己造～

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值