frontpage做网站,太原做网站直通车,工厂网站建设,电子商务网站建设与管理基础#x1f308;欢迎来到Python专栏 #x1f64b;#x1f3fe;♀️作者介绍#xff1a;前PLA队员 目前是一名普通本科大三的软件工程专业学生 #x1f30f;IP坐标#xff1a;湖北武汉 #x1f349; 目前技术栈#xff1a;C/C、Linux系统编程、计算机网络、数据结构、Mys… 欢迎来到Python专栏 ♀️作者介绍前PLA队员 目前是一名普通本科大三的软件工程专业学生 IP坐标湖北武汉 目前技术栈C/C、Linux系统编程、计算机网络、数据结构、Mysql、Python目前在学 博客介绍通过分享学习过程加深知识点的掌握也希望通过平台能认识更多同僚如果觉得文章有帮助请您动动发财手点点赞本人水平有限有不足之处欢迎大家扶正~ 最后送大家一句话共勉知不足而奋进望远山而前行。愿大家都能早日进大厂实现财富自由~ ———————————————— 复合查询详解实战操作 1.资料准备2.多表查询3.自连接4. 子查询单行子查询多行子查询多列子查询 5.在from子句中使用子查询合并查询 1.资料准备
用到个scott.sql内容作为演示学习的案例 代码如下
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);
该数据库中包含了三张表 emp员工信息表salgrader工资等级表deptno部门信息表
2.多表查询
实际开发中往往数据来自不同的表所以需要多表查询。本节我们用一个简单的公司管理系统有三张表EMP,DEPT,SALGRADE来演示如何进行多表查询。 案例 显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表因此要联合查询 其实我们只要emp表中的deptno dept表中的deptno字段的记录
select EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno DEPT.deptno;显示部门号为10的部门名员工名和工资
select ename, sal,dname from EMP, DEPT where EMP.deptnoDEPT.deptno and
DEPT.deptno 10;显示各个员工的姓名工资及工资级别
select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and
hisal;3.自连接
自连接是指在同一张表连接查询 案例
显示员工FORD的上级领导的编号和姓名mgr是员工领导的编号–empno
使用的子查询
select empno,ename from emp where emp.empno(select mgr from emp where
enameFORD);使用多表查询自查询
-- 使用到表的别名
--from emp leader, emp worker给自己的表起别名因为要先做笛卡尔积所以别名可以先识
别
select leader.empno,leader.ename from emp leader, emp worker where
leader.empno worker.mgr and worker.enameFORD;4. 子查询
子查询是指嵌入在其他sql语句中的select语句也叫嵌套查询
单行子查询
返回一行记录的子查询
显示SMITH同一部门的员工
select * from EMP WHERE deptno (select deptno from EMP where
enamesmith);多行子查询
返回多行记录的子查询
in关键字查询和10号部门的工作岗位相同的雇员的名字岗位工资部门号但是不包含10自己的
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno10) and deptno10;all关键字显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename, sal, deptno from emp where sal all(select sal from emp where deptno30);any关键字显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号包含自己部门的员工 select ename, sal, deptno from emp where sal any(select sal from emp where deptno30);多列子查询
单行子查询是指子查询只返回单列单行数据多行子查询是指返回单列多行数据都是针对单列而言的而多列子查询则是指查询返回多个列数据的子查询语句
案例查询和SMITH的部门和岗位完全相同的所有雇员不含SMITH本人
select ename from emp where (deptno, job)(select deptno, job from emp where enameSMITH) and ename SMITH; 5.在from子句中使用子查询
子查询语句出现在from子句中。这里要用到数据查询的技巧把一个子查询当做一个临时表使用。
案例 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
//获取各个部门的平均工资将其看作临时表
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.deptnotmp.dt查找每个部门工资最高的人的姓名、工资、部门、最高工资
select EMP.ename, EMP.sal, EMP.deptno, ms from EMP,
(select max(sal) ms, deptno from EMP group by deptno) tmp
where EMP.deptnotmp.deptno and EMP.saltmp.ms;显示每个部门的信息部门名编号地址和人员数量
使用多表
select DEPT.dname, DEPT.deptno, DEPT.loc,count(*) 部门人数 from EMP,
DEPT
where EMP.deptnoDEPT.deptno
group by DEPT.deptno,DEPT.dname,DEPT.loc;使用子查询
-- 1. 对EMP表进行人员统计
select count(*), deptno from EMP group by deptno;
-- 2. 将上面的表看作临时表
select DEPT.deptno, dname, mycnt, loc from DEPT,
(select count(*) mycnt, deptno from EMP group by deptno) tmp
where DEPT.deptnotmp.deptno;总结就是 把问题分解 单个查询做成一个临时表 然后再做笛卡尔积 最后筛选出合法数据
合并查询
在实际应用中为了合并多个select的执行结果可以使用集合操作符 unionunion all
union 该操作符用于取得两个结果集的并集。当使用该操作符时会自动去掉结果集中的重复行。 案例将工资大于2500或职位是MANAGER的人找出来
mysql select ename, sal, job from EMP where sal2500 union
- select ename, sal, job from EMP where jobMANAGER;--去掉了重复记录
---------------------------
| 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 |
---------------------------union all 该操作符用于取得两个结果集的并集。当使用该操作符时不会去掉结果集中的重复行。 案例将工资大于25000或职位是MANAGER的人找出来
mysql select ename, sal, job from EMP where sal2500 union all
- select ename, sal, job from EMP where jobMANAGER;
---------------------------
| 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 |
---------------------------
