Mysql常用内置函数,复合查询及内外连接
一.常用内置函数
1.1日期函数
current_date(),current_time(),current_timestamp()演示:
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2025-07-08 |
+----------------+
1 row in set (0.00 sec)mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 19:47:37 |
+----------------+
1 row in set (0.00 sec)mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2025-07-08 19:47:43 |
+---------------------+
1 row in set (0.00 sec)
date()函数:
mysql> select date(current_timestamp());
+---------------------------+
| date(current_timestamp()) |
+---------------------------+
| 2025-07-08 |
+---------------------------+
1 row in set (0.00 sec)mysql> select date(current_time());
+----------------------+
| date(current_time()) |
+----------------------+
| 2025-07-08 |
+----------------------+
1 row in set (0.00 sec)mysql> select date(current_date());
+----------------------+
| date(current_date()) |
+----------------------+
| 2025-07-08 |
+----------------------+
1 row in set (0.00 sec)其实通过这三个例子我们可以发现,上面的三个函数其实都是current_timestamp(),其余两个只是对该函数的结果进行了裁剪.
date_add(),date_sub()与datediff()函数:
mysql> select date_sub(date('2025-7-8'),interval 3 day);
+-------------------------------------------+
| date_sub(date('2025-7-8'),interval 3 day) |
+-------------------------------------------+
| 2025-07-05 |
+-------------------------------------------+
1 row in set (0.00 sec)mysql> select date_sub('2025-7-8',interval 3 day);
+-------------------------------------+
| date_sub('2025-7-8',interval 3 day) |
+-------------------------------------+
| 2025-07-05 |
+-------------------------------------+
1 row in set (0.00 sec)#mysql也会自动转换mysql> select date_sub(now(),interval 3 day);
+--------------------------------+
| date_sub(now(),interval 3 day) |
+--------------------------------+
| 2025-07-05 19:53:20 |
+--------------------------------+
1 row in set (0.00 sec)mysql> select date_sub(current_date(),interval 3 day);
+-----------------------------------------+
| date_sub(current_date(),interval 3 day) |
+-----------------------------------------+
| 2025-07-05 |
+-----------------------------------------+
1 row in set (0.00 sec)select datediff(current_date(),date_sub(current_date(),interval 72 hour));
+--------------------------------------------------------------------+
| datediff(current_date(),date_sub(current_date(),interval 72 hour)) |
+--------------------------------------------------------------------+
| 3 |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)而对于now()函数,与time_currentstamp()函数基本类似,但是他们也有着细微的区别:
这里我们不再展开细讲,有兴趣的读者可以搜索相关资料进行了解.
1.2字符串函数
Mysql中的字符串函数大多与我们c/c++中的字符串函数功能类似,我们只介绍部分函数(其他的函数基本一看就明白)
我们重点介绍下replace函数与trim系列函数:
1.对于replace函数,我们可以通过一道题目来解释他:
查找字符串中逗号出现的次数_牛客题霸_牛客网
如何求得每个字符串中的逗号的数量呢,很简单:
select id,length(string) - length(replace(string,',','')) cnt from strings; 将所有逗号的部分直接替换为空即可.
2.trim系列函数
ltrim与rtrim其实没有什么好说的,看下面的例子就可以明白:
mysql> select ltrim(' hello world ');
+------------------------------------------+
| ltrim(' hello world ') |
+------------------------------------------+
| hello world |
+------------------------------------------+
1 row in set (0.00 sec)mysql> select rtrim(' hello world ');
+------------------------------------------+
| rtrim(' hello world ') |
+------------------------------------------+
| hello world |
+------------------------------------------+
1 row in set (0.00 sec)
上面两个函数只会去除左侧or右侧的空格,不会去除字符串中间的空格.
而trim函数不仅仅可以同时去除两侧的空格,还可以指定两侧需要去除的字符:
mysql> select trim(' hello world ') t1;
+-------------+
| t1 |
+-------------+
| hello world |
+-------------+
1 row in set (0.00 sec)mysql> select rtrim(' hello world ') t1;
+------------------------+
| t1 |
+------------------------+
| hello world |
+------------------------+
1 row in set (0.00 sec)
#去除左右两侧空格mysql> select trim(both 'x' from 'xxxxhello worldxxx') result;
+-------------+
| result |
+-------------+
| hello world |
+-------------+
1 row in set (0.00 sec)mysql> select trim(leading 'x' from 'xxxxhello worldxxx') result;
+----------------+
| result |
+----------------+
| hello worldxxx |
+----------------+
1 row in set (0.00 sec)mysql> select trim(trailing 'x' from 'xxxxhello worldxxx') result;
+-----------------+
| result |
+-----------------+
| xxxxhello world |
+-----------------+
1 row in set (0.00 sec)
#同时去除两侧特定字符或去除指定侧的指定字符
1.3数学函数
我们这里简单介绍下这部分的函数,下面讲复合查询的时候我们会把上面的函数穿起来讲:
mysql> select abs(-100.2);#绝对值
+-------------+
| abs(-100.2) |
+-------------+
| 100.2 |
+-------------+
1 row in set (0.00 sec)mysql> select ceiling(23.04);#向上取整
+----------------+
| ceiling(23.04) |
+----------------+
| 24 |
+----------------+
1 row in set (0.00 sec)mysql> select floor(23.7);#向下取整
+-------------+
| floor(23.7) |
+-------------+
| 23 |
+-------------+
1 row in set (0.00 sec)mysql> select format(12.3456, 2);#保留2位小数位数（小数四舍五入)
+--------------------+
| format(12.3456, 2) |
+--------------------+
| 12.35 |
+--------------------+
1 row in set (0.00 sec)
1.4其他函数
mysql> select user();#user() 查询当前用户
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)mysql> select md5('admin');#md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串
+----------------------------------+
| md5('admin') |
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+
1 row in set (0.00 sec)mysql> select database();#database()显示当前正在使用的数据库
+------------+
| database() |
+------------+
| d2 |
+------------+
1 row in set (0.00 sec)mysql> select password('root');#password()函数，MySQL数据库使用该函数对用户加密
+-------------------------------------------+
| password('root') |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)mysql> select ifnull('abc', '123');#ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值
+----------------------+
| ifnull('abc', '123') |
+----------------------+
| abc |
+----------------------+
1 row in set (0.00 sec)
二.复合查询
2.1复盘基本查询
我们借助前文提到的scott表同时结合下面几个例子来回顾前文介绍的基本查询与熟悉本文前半部分的内置函数:
scott.sql · ly/Project-Code - Gitee.com
实例:
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)
2.按照部门号升序而雇员的工资降序排序
mysql> select * from emp order by deptno asc,sal desc;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
3.使用年薪进行降序排序
mysql> select ename,sal * 12 + ifnull(comm,0) ssal from emp order by ssal desc;
+--------+----------+
| ename | ssal |
+--------+----------+
| 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)
4.显示工资最高的员工的名字和工作岗位
mysql> select ename,job from emp where sal = (select max(sal) from emp);
+-------+-----------+
| ename | job |
+-------+-----------+
| KING | PRESIDENT |
+-------+-----------+
1 row in set (0.00 sec)
#这里用到了子查询的知识,我们下面会介绍5.显示工资高于平均工资的员工信息
#这里用到了子查询的知识,我们下面会介绍
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.01 sec)
6.显示每个部门的平均工资和最高工资
mysql> select deptno,format(avg(sal),2) 平均工资,max(sal) 最高工资 from emp group by deptno;
+--------+--------------+--------------+
| deptno | 平均工资 | 最高工资 |
+--------+--------------+--------------+
| 10 | 2,916.67 | 5000.00 |
| 20 | 2,175.00 | 3000.00 |
| 30 | 1,566.67 | 2850.00 |
+--------+--------------+--------------+
3 rows in set (0.00 sec)
7.显示平均工资低于2000的部门号和它的平均工资
mysql> select deptno,format(avg(sal),2) 平均工资 from emp group by deptno having 平均工资 <= format(2000,2);
+--------+--------------+
| deptno | 平均工资 |
+--------+--------------+
| 30 | 1,566.67 |
+--------+--------------+
1 row in set (0.00 sec)
8.显示每种岗位的雇员总数，平均工资
mysql> select job 岗位类别,count(*) 雇员总数,format(avg(sal),2) 平均工资 from emp group by job;
+--------------+--------------+--------------+
| 岗位类别 | 雇员总数 | 平均工资 |
+--------------+--------------+--------------+
| ANALYST | 2 | 3,000.00 |
| CLERK | 4 | 1,037.50 |
| MANAGER | 3 | 2,758.33 |
| PRESIDENT | 1 | 5,000.00 |
| SALESMAN | 4 | 1,400.00 |
+--------------+--------------+--------------+
5 rows in set (0.00 sec)
2.2多表查询
笛卡尔积
试想一下,如果这样子写下面的sql语句:
select * from dept,salgrade;我们会得到如下的结果:
+--------+------------+----------+-------+-------+-------+
| deptno | dname | loc | grade | losal | hisal |
+--------+------------+----------+-------+-------+-------+
| 10 | ACCOUNTING | NEW YORK | 1 | 700 | 1200 |
| 20 | RESEARCH | DALLAS | 1 | 700 | 1200 |
| 30 | SALES | CHICAGO | 1 | 700 | 1200 |
| 40 | OPERATIONS | BOSTON | 1 | 700 | 1200 |
| 10 | ACCOUNTING | NEW YORK | 2 | 1201 | 1400 |
| 20 | RESEARCH | DALLAS | 2 | 1201 | 1400 |
| 30 | SALES | CHICAGO | 2 | 1201 | 1400 |
| 40 | OPERATIONS | BOSTON | 2 | 1201 | 1400 |
| 10 | ACCOUNTING | NEW YORK | 3 | 1401 | 2000 |
| 20 | RESEARCH | DALLAS | 3 | 1401 | 2000 |
| 30 | SALES | CHICAGO | 3 | 1401 | 2000 |
| 40 | OPERATIONS | BOSTON | 3 | 1401 | 2000 |
| 10 | ACCOUNTING | NEW YORK | 4 | 2001 | 3000 |
| 20 | RESEARCH | DALLAS | 4 | 2001 | 3000 |
| 30 | SALES | CHICAGO | 4 | 2001 | 3000 |
| 40 | OPERATIONS | BOSTON | 4 | 2001 | 3000 |
| 10 | ACCOUNTING | NEW YORK | 5 | 3001 | 9999 |
| 20 | RESEARCH | DALLAS | 5 | 3001 | 9999 |
| 30 | SALES | CHICAGO | 5 | 3001 | 9999 |
| 40 | OPERATIONS | BOSTON | 5 | 3001 | 9999 |
+--------+------------+----------+-------+-------+-------+
20 rows in set (0.00 sec)
像这样从第一张表中选出第一条记录，和第二个表的所有记录进行组合,然后从第一张表中取第二条记录，和第二张表中的所有记录组合不加过滤条件，得到的结果称为笛卡儿积。
所以如果我们想要查询对应员工的工作地点的话,可以这样写sql语句(借助笛卡尔积) :
mysql> select emp.*,dept.dname,dept.loc from emp,dept where emp.deptno = dept.deptno;
+--------+--------+-----------+------+---------------------+---------+---------+--------+------------+----------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | dname | loc |
+--------+--------+-----------+------+---------------------+---------+---------+--------+------------+----------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | RESEARCH | DALLAS |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | SALES | CHICAGO |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | SALES | CHICAGO |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | RESEARCH | DALLAS |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | SALES | CHICAGO |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | SALES | CHICAGO |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | ACCOUNTING | NEW YORK |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | RESEARCH | DALLAS |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | ACCOUNTING | NEW YORK |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | SALES | CHICAGO |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | RESEARCH | DALLAS |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | SALES | CHICAGO |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | RESEARCH | DALLAS |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | ACCOUNTING | NEW YORK |
+--------+--------+-----------+------+---------------------+---------+---------+--------+------------+----------+
14 rows in set (0.00 sec)
我们以两个例子来熟悉笛卡尔积:
1.显示部门号为10的部门名，员工名和工资
mysql> select emp.ename,emp.sal,dept.deptno,dept.dname from emp,dept where emp.deptno = dept.deptno order by dept.deptno;
+--------+---------+--------+------------+
| ename | sal | deptno | dname |
+--------+---------+--------+------------+
| MILLER | 1300.00 | 10 | ACCOUNTING |
| KING | 5000.00 | 10 | ACCOUNTING |
| CLARK | 2450.00 | 10 | ACCOUNTING |
| SCOTT | 3000.00 | 20 | RESEARCH |
| ADAMS | 1100.00 | 20 | RESEARCH |
| SMITH | 800.00 | 20 | RESEARCH |
| JONES | 2975.00 | 20 | RESEARCH |
| FORD | 3000.00 | 20 | RESEARCH |
| ALLEN | 1600.00 | 30 | SALES |
| MARTIN | 1250.00 | 30 | SALES |
| WARD | 1250.00 | 30 | SALES |
| BLAKE | 2850.00 | 30 | SALES |
| JAMES | 950.00 | 30 | SALES |
| TURNER | 1500.00 | 30 | SALES |
+--------+---------+--------+------------+
14 rows in set (0.00 sec)
2.显示各个员工的姓名，工资，及工资级别
mysql> select emp.ename,emp.sal,salgrade.grade from emp,salgrade where sal between losal and hisal order by grade;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| ADAMS | 1100.00 | 1 |
| SMITH | 800.00 | 1 |
| JAMES | 950.00 | 1 |
| WARD | 1250.00 | 2 |
| MILLER | 1300.00 | 2 |
| MARTIN | 1250.00 | 2 |
| TURNER | 1500.00 | 3 |
| ALLEN | 1600.00 | 3 |
| FORD | 3000.00 | 4 |
| SCOTT | 3000.00 | 4 |
| JONES | 2975.00 | 4 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| KING | 5000.00 | 5 |
+--------+---------+-------+
14 rows in set (0.00 sec)
2.3自连接
自连接顾名思义就是同一张表自己跟自己连接,我们看一个例子就明白了:
显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）
mysql> select e2.ename manger,e2.empno manger_empno from emp e1,emp e2 where e1.ename = 'FORD' and e1.mgr = e2.empno;
+--------+--------------+
| manger | manger_empno |
+--------+--------------+
| JONES | 007566 |
+--------+--------------+
1 row in set (0.00 sec)
2.4子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
2.4.1单行子查询
顾名思义就是该子查询仅返回只有一条记录的临时表,我们看下面这个例子:
显示SMITH同一部门的员工(不包含他自己本人):
mysql> select * from emp where deptno = (select deptno from emp where ename = 'SMITH') and ename <> 'SMITH';
+--------+-------+---------+------+---------------------+---------+------+--------+
| 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 |
| 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 |
+--------+-------+---------+------+---------------------+---------+------+--------+
4 rows in set (0.00 sec)
2.4.2多行子查询
该子查询返回的是多条记录的临时表,我们需要借助in,all,any关键字来使用,下面我们通过三个例子来介绍下这三个关键字:
1.in:查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己
mysql> select 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)
2.all:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号(使用all不可以使用max)
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)
3.any: 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）
mysql> select ename,sal,deptno from emp where sal > any(select 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)
2.4.3多列子查询
单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句
举个例子就明白了:查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人:
mysql> select ename from emp where (deptno, job)=(select deptno, job from emp where ename='SMITH') and ename <> 'SMITH';
+-------+
| ename |
+-------+
| ADAMS |
+-------+
1 row in set (0.00 sec)
2.4.4在from子句中使用子查询
子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。
这里也能很好的体现我们前面所说的mysql中一切皆表的概念,无论这个表是物理上存在的还是临时的.我们以几个例子来介绍:
1.显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
mysql> select ename, deptno, sal, format(asal,2) from emp, (select avg(sal) asal, deptno dt from emp group by deptno) tmp where emp.sal > tmp.asal and emp.deptno=tmp.dt;
+-------+--------+---------+----------------+
| ename | deptno | sal | format(asal,2) |
+-------+--------+---------+----------------+
| KING | 10 | 5000.00 | 2,916.67 |
| JONES | 20 | 2975.00 | 2,175.00 |
| SCOTT | 20 | 3000.00 | 2,175.00 |
| FORD | 20 | 3000.00 | 2,175.00 |
| ALLEN | 30 | 1600.00 | 1,566.67 |
| BLAKE | 30 | 2850.00 | 1,566.67 |
+-------+--------+---------+----------------+
6 rows in set (0.00 sec)
2.查找每个部门工资最高的人的姓名、工资、部门、最高工资
mysql> select emp.ename, emp.sal, emp.deptno, ms from emp, (select max(sal) ms, deptno from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal=tmp.ms;
+-------+---------+--------+---------+
| ename | sal | deptno | ms |
+-------+---------+--------+---------+
| 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.显示每个部门的信息（部门名，编号，地址）和人员数量
#1. 对EMP表进行人员统计
select count(*), deptno from emp group by deptno;
+----------+--------+
| count(*) | deptno |
+----------+--------+
| 3 | 10 |
| 5 | 20 |
| 6 | 30 |
+----------+--------+
3 rows in set (0.00 sec)#2. 将上面的表看作临时表
select dept.deptno, dname, mycnt, loc from dept,
(select count(*) mycnt, deptno from emp group by deptno) tmp
where dept.deptno=tmp.deptno;
+--------+------------+-------+----------+
| deptno | dname | mycnt | loc |
+--------+------------+-------+----------+
| 10 | ACCOUNTING | 3 | NEW YORK |
| 20 | RESEARCH | 5 | DALLAS |
| 30 | SALES | 6 | CHICAGO |
+--------+------------+-------+----------+
3 rows in set (0.00 sec)
2.4.5合并查询
合并查询的关键字是union与union all,就是取两张临时表的并集,需要保证两张临时表的列数相同,我们以下面的一个案例来理解这两个家伙:
将工资大于2500或职位是MANAGER的人找出来:
mysql> select ename, sal, job from emp where sal>2500 union select ename, sal, job from emp where job='MANAGER';
+-------+---------+-----------+
| ename | sal | job |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| CLARK | 2450.00 | MANAGER |
+-------+---------+-----------+
6 rows in set (0.00 sec)mysql> select ename, sal, job from emp where sal>2500 union all select ename, sal, job from emp where job='MANAGER';
+-------+---------+-----------+
| ename | sal | job |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| CLARK | 2450.00 | MANAGER |
+-------+---------+-----------+
8 rows in set (0.01 sec)
所以说union与union all唯一的区别就是前者会进行去重而后者不会.
三.表的内连与外连
表的连接包含内连接和外连接两种类型。
3.1表的内连
内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询,它的语法如下:
select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；
#前文提到的连接其实都是内连接比如我们要显示SMITH的名字和部门名称,下面两种写法的结果是一样的:
-- 用前面的写法
select ename, dname from EMP, DEPT where EMP.deptno=DEPT.deptno and
ename='SMITH';
-- 用标准的内连接写法
select ename, dname from EMP inner join DEPT on EMP.deptno=DEPT.deptno and
ename='SMITH';3.2表的外连
外连接分为两种,一种是左外连接,另一种是右外连接,通俗点来讲就是前者写在左侧的表必须完全显示,不管右侧是否有对应值,后者则刚好反过来,我们以下面的表来介绍这两种外连接:
create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);3.2.1表的左外连接
语法:
select 字段名 from 表名1 left join 表名2 on 连接条件案例:
查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来:
mysql> select name,grade from stu left join exam on stu.id = exam.id;
+------+-------+
| name | grade |
+------+-------+
| jack | 56 |
| tom | 76 |
| kity | NULL |
| nono | NULL |
+------+-------+
4 rows in set (0.00 sec)
3.2.2表的右外连接
语法:
select 字段名 from 表名1 right join 表名2 on 连接条件案例:
对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来:
mysql> select * from stu right join exam on stu.id=exam.id;
+------+------+------+-------+
| id | name | id | grade |
+------+------+------+-------+
| 1 | jack | 1 | 56 |
| 2 | tom | 2 | 76 |
| NULL | NULL | 11 | 8 |
+------+------+------+-------+
3 rows in set (0.00 sec)
综合案例:
我们来看一个综合案例:
626. 换座位 - 力扣（LeetCode）
这道题的一种思路如下,因为是奇数位同学与偶数位同学进行交换,所以我们需要添加如下限制条件:
((s1.id % 2 = 1) and (s1.id = (s2.id - 1))) or ((s1.id % 2 = 0) and (s1.id = (s2.id + 1)))那么如果总数为奇数就会导致最后一名同学不显示,此时我们就可以使用左外连接让其强制显示,所以最终的答案sql语句如下:
select s1.id,ifnull(s2.student,s1.student) 'student' from Seat s1 left join Seat S2 on ((s1.id % 2 = 1) and (s1.id = (s2.id - 1))) or ((s1.id % 2 = 0) and (s1.id = (s2.id + 1))) order by s1.id;