MySQL复合查询操作【 函数接口集合 | 多表查询 | 子查询 | 表的内连外连】

  博客主页：花果山~程序猿-CSDN博客

文章分栏：MySQL之旅_花果山~程序猿的博客-CSDN博客

关注我一起学习，一起进步，一起探索编程的无限可能吧！让我们一起努力，一起成长！

目录

一，mysql函数集合

日期函数 

字符串函数

数学函数

其他函数

user()

md5(str)

database() 

password(str)

ifnull（val1， val2）

二，复合查询

1.回顾查询案例 

2.多表查询

3，自连接

4，子查询

单行子查询 

多行子查询

多列子查询

进阶，在from中使用子查询

三，表的内连与外连（重要）

1.内连接

2. 外连接

左外连接 

右外连接 

结语

嗨！收到一张超美的图，愿你每天都能顺心！

一，mysql函数集合

日期函数 

字符串函数

数学函数

 函数使用示例：

其他函数

user()

查询当前用户

select user();

md5(str)

对一个字符串进行md5摘要，摘要后得到一个32位字符串

database() 

显示当前正在使用的数据库

select database();

password(str)

函数，MySQL数据库使用该函数对用户结果数据加密

ifnull（val1， val2）

如果val1为null，返回val2，否则返回val1的值。类似于三元表达式。

二，复合查询

本篇文章采用的 oracle 9i 的经典测试表，测试表创建如下：

• EMP员工表
• DEPT部门表
• SALGRADE工资等级表

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;USE `scott`;DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (`deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',`dname` varchar(14) DEFAULT NULL COMMENT '部门名称',`loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (`empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',`ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',`job` varchar(9) DEFAULT NULL COMMENT '雇员职位',`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',`hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',`sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',`comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (`grade` int(11) DEFAULT NULL COMMENT '等级',`losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',`hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

1.回顾查询案例 

查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J 

select empno, ename from emp where (sal > 500 or job= 'MANAGER') and ename like 'J%';

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

select ename, sal from EMP where sal>(select avg(sal) from EMP);

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

select deptno, avg(sal) as avg_sal from EMP group by deptno having avg_sal<2000;

2.多表查询

select * from EMP, DEPT; 会有什么样的结果？ 

结果是EMP与DEPT进行穷举组合。

用法展示：

显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表（看用法类似于外键）

select ename, sal, job from emp, dept where emp.deptno=dept.deptno;

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

select ename, sal, dname from emp, dept where emp.deptno =dept.deptno and dept.deptno=10;

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

select ename, sal , grade from emp, salgrade where sal between losal and hisal;

3，自连接

多表查询，是两个不同的表，自连接就是利用相同的表。

请看下面案例：

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

子查询

select empno, ename from emp where empno=(select mgr from emp where ename='FORD');

多表查询（自连接）

 select e2.empno,e2.ename from emp as e1, emp as e2 where e1.ename='FORD' and e1.mgr=e2.empno;

4，子查询

单行子查询 

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。前面我们使用的基本上是单行子查询，就是只显示一行结果的，如下：

• 显示SMITH同一部门的员工

select * from EMP WHERE deptno = (select deptno from EMP where ename='smith');

多行子查询

案例：

in关键字；查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的（我认为其他方法不好解决此类，判断模糊的情况）

select ename, job, sal , deptno from emp where job in (select job from emp where deptno=10 group by job) and deptno!=10;

all关键字；显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号(可替代)

利用统计函数 + 单行查询：

select ename, sal, deptno from emp where sal > (select max(sal) from emp where deptno =30);

多行查询：

select ename, sal, deptno from emp where sal > all (select sal  from emp where deptno =30 group by sal);

any关键字；显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）（可替代）

利用统计函数 + 单行查询：

select ename, sal, deptno from emp where sal > (select min(sal) from emp where deptno =30);

多行查询：

select ename, sal, deptno from emp where sal > any (select sal  from emp where deptno =30 group by sal);

多列子查询

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

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

单行子查询法：

select ename from emp where job=(select job from emp where ename = 'SMITH') 
and deptno=(select deptno from emp where ename ='SMITH') 
and ename <> 'SMITH';

多列子查询：

select ename from emp where (job, deptno)=(select job, deptno from emp where ename = 'SMITH')MITH') and ename <> 'SMITH';

功能：相比于单行子查询，可以减少大部分的重复语句，同时，也可搭配 ' in '使用。

进阶，在from中使用子查询

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

案例：

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

select ename, emp.deptno, sal, _dep.avg_dep 
from emp, (select emp.deptno, avg(sal)as avg_dep from emp group by emp.deptno) as _dep 
where emp.deptno=_dep.deptno and emp.sal > _dep.avg_dep;
// 首先我们需要区分出那些是需要展示的统计数据，然后通过from
// 将不同表进行整合在一张表中

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

select ename, sal, emp.deptno, maxsal 
from emp ,(select emp.deptno, max(sal) maxsal from emp group by deptno) as mt 
where emp.deptno = mt.deptno and sal = maxsal;

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

多表查询:

select dept.dname, dept.deptno, dept.loc, count(*) 
from emp, dept 
where emp.deptno =dept.deptno 
group by dept.deptno ,dept.loc ,dept.dname;

from子查询： 

select *
from dept, (select emp.deptno, count(empno) sum from emp group by emp.deptno) as s_t 
where dept.deptno=s_t.deptno;

三，表的内连与外连（重要）

1.内连接

本质上等价于笛卡尔积，笛卡尔积是内连接的一种。

// 上面学习过的笛卡尔积
select ... from table1,talbe2  where table1.字段 = talbe2.字段;

通过 where 对不合理的搭配进行筛选，而内连接正统语法：

select ... from table1 inner join talbe2 on table1.字段= table2.字段 and 其他条件

从效果来看两种写法相同，但从逻辑简易来看，后者语法可以将表内连接条件更加紧凑，前者使用外部条件判断，逻辑较分散。

2. 外连接

左外连接 

语法：

select ... from talbe1 left join talbe2 on 外连接条件 and 其他条件

内连接条件需要两表同时满足，才可保留；而外连接就是保留一侧数据，没有匹配，则右侧插入的表字符全部设置为null。这里以左外连接进行举例：

-- 实验案例，建两张表
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 not null, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);

• 查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来

select * from stu left join exam on stu.id=exam.id;

从实验结果来看，左外连接的特点是，左侧表即使没有与右侧表匹配成功，也会被保留，右侧表的字段将全部设置null，对！即使右侧表字段属性是 not null。

右外连接 

右外连接原理一模一样，不如直接把表对换一下，我们一般可以直接用左外连接一个就行。

结语

   本小节就到这里了，感谢小伙伴的浏览，如果有什么建议，欢迎在评论区评论，如果给小伙伴带来一些收获，请动动你发财的小手点个免费的赞，你的点赞和关注永远是博主创作的动力源泉。