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

2024-05-31 22:36
文章标签 mysql 接口 函数 多表 查询 操作 复合 集合 database 连外

本文主要是介绍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。

右外连接 

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

结语

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

这篇关于MySQL复合查询操作【 函数接口集合 | 多表查询 | 子查询 | 表的内连外连】的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1019229。

相关文章

SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
hdu1171（母函数或多重背包）

hdu1171（母函数或多重背包）

题意：把物品分成两份，使得价值最接近 可以用背包，或者是母函数来解，母函数（1 + x^v+x^2v+.....+x^num*v）（1 + x^v+x^2v+.....+x^num*v）（1 + x^v+x^2v+.....+x^num*v） 其中指数为价值，每一项的数目为（该物品数+1）个 代码如下： #include<iostream>#include<algorithm>
阅读更多...
活用c4d官方开发文档查询代码

活用c4d官方开发文档查询代码

当你问AI助手比如豆包，如何用python禁止掉xpresso标签时候，它会提示到 这时候要用到两个东西。https://developers.maxon.net/论坛搜索和开发文档 比如这里我就在官方找到正确的id描述 然后我就把参数标签换过来
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
uva 11178 计算集合模板题

uva 11178 计算集合模板题

题意： 求三角形行三个角三等分点射线交出的内三角形坐标。 代码： #include <iostream>#include <cstdio>#include <cstdlib>#include <algorithm>#include <cstring>#include <cmath>#include <stack>#include <vector>#include <
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2