建设商城购物网站,网站正在建设中中文,为什么做的网站在谷歌浏览器打不开,做类似淘宝的网站一、子查询定义 当我们进⾏语句查询的时候#xff0c;总会遇到我们需要的条件需要通过另⼀个查询语句查询出来后才能进⾏#xff0c;就是说A 查询语句需要依赖B 查询语句的查询结果#xff0c;B 查询就是⼦查询#xff0c;A 查询语句就是主查询#xff0c;⼀个SQL语句可以…一、子查询定义 当我们进⾏语句查询的时候总会遇到我们需要的条件需要通过另⼀个查询语句查询出来后才能进⾏就是说A 查询语句需要依赖B 查询语句的查询结果B 查询就是⼦查询A 查询语句就是主查询⼀个SQL语句可以包含多个⼦查询。
二、子查询出现的地方
在where⼦句中: ⼦查询的结果具体可⽤作条件筛选时使⽤的值。在from⼦句中: ⼦查询的结果可充当⼀张表或视图需要使⽤表别名。在having⼦句中: ⼦查询的结果可⽤作分组查询再次条件过滤时使⽤的值在select⼦句中: ⼦查询的结果可充当⼀个字段。仅限⼦查询返回单⾏单列的情况。这种情况下会输出没有结果的话结果会有null的情况
1、子查询出现在where子句中
1、查询⼯资⼤于10号部⻔的平均⼯资的⾮10号部⻔的员⼯信息
分析
110号部⻔的平均⼯资select avg(sal) from emp where deptno 10;
2查询员工信息select * from emp where deptno ! 10 and sal (select avg(sal) from emp where deptno 10);----------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
----------------------------------------------------------------
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
----------------------------------------------------------------2、查询与7369同部⻔的同事信息select deptno from emp where empno 7369;select * from emp where deptno (select deptno from emp where empno 7369)and empno ! 7369;----------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
----------------------------------------------------------------
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
----------------------------------------------------------------2、子查询出现在from子句中
1、查询员⼯的姓名⼯资及其部⻔的平均⼯资select ename,sal from emp ;思考每一个部门的平均工资是多少select deptno,avg(sal) from emp group by deptno;
---------------------
| deptno | avg(sal) |
---------------------
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 100 | NULL |
---------------------
4 rows in set (0.09 sec)
查询员工信息
select * from emp;
mysql select * from emp;
----------------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
----------------------------------------------------------------------
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 8000 | LaoYan | NULL | NULL | NULL | NULL | NULL | 100 |
----------------------------------------------------------------------此时查询出来的平均工资表可以当做一个虚拟的表和emp表关联起来mysql select * from ( select deptno,avg(sal) avgMoney from emp group by deptno ) avgTable;
---------------------
| deptno | avgMoney |
---------------------
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 100 | NULL |
---------------------现在 avgTable 表 和 emp 通过 deptno 相关联
select ename,sal,avgMoney from emp , (select deptno,avg(sal) avgMoney from emp group by deptno) avgTable where emp.deptnoavgTable.deptno;
------------------------------
| ename | sal | avgMoney |
------------------------------
| SMITH | 800.00 | 2175.000000 |
| ALLEN | 1600.00 | 1566.666667 |
| WARD | 1250.00 | 1566.666667 |
| JONES | 2975.00 | 2175.000000 |
| MARTIN | 1250.00 | 1566.666667 |
| BLAKE | 2850.00 | 1566.666667 |
| CLARK | 2450.00 | 2916.666667 |
| SCOTT | 3000.00 | 2175.000000 |
| KING | 5000.00 | 2916.666667 |
| TURNER | 1500.00 | 1566.666667 |
| ADAMS | 1100.00 | 2175.000000 |
| JAMES | 950.00 | 1566.666667 |
| FORD | 3000.00 | 2175.000000 |
| MILLER | 1300.00 | 2916.666667 |
| LaoYan | NULL | NULL |
------------------------------
最终的答案虚拟机的表解释--任何的查询结果都可以当做一个虚拟机的表来使用
mysql select ename,job,sal from emp where job is not null;
----------------------------
| ename | job | sal |
----------------------------
| SMITH | CLERK | 800.00 |
| ALLEN | SALESMAN | 1600.00 |
| WARD | SALESMAN | 1250.00 |
| JONES | MANAGER | 2975.00 |
| MARTIN | SALESMAN | 1250.00 |
| BLAKE | MANAGER | 2850.00 |
| CLARK | MANAGER | 2450.00 |
| SCOTT | ANALYST | 3000.00 |
| KING | PRESIDENT | 5000.00 |
| TURNER | SALESMAN | 1500.00 |
| ADAMS | CLERK | 1100.00 |
| JAMES | CLERK | 950.00 |
| FORD | ANALYST | 3000.00 |
| MILLER | CLERK | 1300.00 |
----------------------------
14 rows in set (0.09 sec)mysql select * from (select ename,job,sal from emp where job is not null) eemp;
----------------------------
| ename | job | sal |
----------------------------
| SMITH | CLERK | 800.00 |
| ALLEN | SALESMAN | 1600.00 |
| WARD | SALESMAN | 1250.00 |
| JONES | MANAGER | 2975.00 |
| MARTIN | SALESMAN | 1250.00 |
| BLAKE | MANAGER | 2850.00 |
| CLARK | MANAGER | 2450.00 |
| SCOTT | ANALYST | 3000.00 |
| KING | PRESIDENT | 5000.00 |
| TURNER | SALESMAN | 1500.00 |
| ADAMS | CLERK | 1100.00 |
| JAMES | CLERK | 950.00 |
| FORD | ANALYST | 3000.00 |
| MILLER | CLERK | 1300.00 |
----------------------------
14 rows in set (0.06 sec)3.子查询在select语句中
2、查询员⼯的姓名⼯资及其部⻔的平均⼯资
说一个情况 emp表和emp表也是可以自己关联自己的。select * from emp e1, emp e2 where e1.empno e2.empno;select ename,sal, (select avg(sal) from emp e2 where e2.deptno e1.deptno ) 部门平均工资 from emp e1;
拆解
select avg(sal) from emp e2 where e2.deptno 10;
select avg(sal) from emp e2 where e2.deptno 20;最终的答案
-------------------------------
| ename | sal | 部门平均工资 |
-------------------------------
| SMITH | 800.00 | 2175.000000 |
| ALLEN | 1600.00 | 1566.666667 |
| WARD | 1250.00 | 1566.666667 |
| JONES | 2975.00 | 2175.000000 |
| MARTIN | 1250.00 | 1566.666667 |
| BLAKE | 2850.00 | 1566.666667 |
| CLARK | 2450.00 | 2916.666667 |
| SCOTT | 3000.00 | 2175.000000 |
| KING | 5000.00 | 2916.666667 |
| TURNER | 1500.00 | 1566.666667 |
| ADAMS | 1100.00 | 2175.000000 |
| JAMES | 950.00 | 1566.666667 |
| FORD | 3000.00 | 2175.000000 |
| MILLER | 1300.00 | 2916.666667 |
| LaoYan | NULL | NULL |
-------------------------------优化一下工资 保留小数点后2位
select ename,sal, round(( select avg(sal) from emp e2 where e2.deptno e1.deptno),2) 部门平均工资 from emp e1;
-------------------------------
| ename | sal | 部门平均工资 |
-------------------------------
| SMITH | 800.00 | 2175.00 |
| ALLEN | 1600.00 | 1566.67 |
| WARD | 1250.00 | 1566.67 |
| JONES | 2975.00 | 2175.00 |
| MARTIN | 1250.00 | 1566.67 |
| BLAKE | 2850.00 | 1566.67 |
| CLARK | 2450.00 | 2916.67 |
| SCOTT | 3000.00 | 2175.00 |
| KING | 5000.00 | 2916.67 |
| TURNER | 1500.00 | 1566.67 |
| ADAMS | 1100.00 | 2175.00 |
| JAMES | 950.00 | 1566.67 |
| FORD | 3000.00 | 2175.00 |
| MILLER | 1300.00 | 2916.67 |
| LaoYan | NULL | NULL |
-------------------------------再来一遍仔细品味
mysql select ename,sal, ( select avg(sal) from emp e2 where e2.deptno 10 ) from emp;
-----------------------------------------------------------------------
| ename | sal | ( select avg(sal) from emp e2 where e2.deptno 10 ) |
-----------------------------------------------------------------------
| SMITH | 800.00 | 2916.666667 |
| ALLEN | 1600.00 | 2916.666667 |
| WARD | 1250.00 | 2916.666667 |
| JONES | 2975.00 | 2916.666667 |
| MARTIN | 1250.00 | 2916.666667 |
| BLAKE | 2850.00 | 2916.666667 |
| CLARK | 2450.00 | 2916.666667 |
| SCOTT | 3000.00 | 2916.666667 |
| KING | 5000.00 | 2916.666667 |
| TURNER | 1500.00 | 2916.666667 |
| ADAMS | 1100.00 | 2916.666667 |
| JAMES | 950.00 | 2916.666667 |
| FORD | 3000.00 | 2916.666667 |
| MILLER | 1300.00 | 2916.666667 |
| LaoYan | NULL | 2916.666667 |
-----------------------------------------------------------------------
15 rows in set (0.10 sec)mysql select ename,sal, ( select avg(sal) from emp e2 where e2.deptno emp.deptno ) from emp;
-------------------------------------------------------------------------------
| ename | sal | ( select avg(sal) from emp e2 where e2.deptno emp.deptno ) |
-------------------------------------------------------------------------------
| SMITH | 800.00 | 2175.000000 |
| ALLEN | 1600.00 | 1566.666667 |
| WARD | 1250.00 | 1566.666667 |
| JONES | 2975.00 | 2175.000000 |
| MARTIN | 1250.00 | 1566.666667 |
| BLAKE | 2850.00 | 1566.666667 |
| CLARK | 2450.00 | 2916.666667 |
| SCOTT | 3000.00 | 2175.000000 |
| KING | 5000.00 | 2916.666667 |
| TURNER | 1500.00 | 1566.666667 |
| ADAMS | 1100.00 | 2175.000000 |
| JAMES | 950.00 | 1566.666667 |
| FORD | 3000.00 | 2175.000000 |
| MILLER | 1300.00 | 2916.666667 |
| LaoYan | NULL | NULL |
-------------------------------------------------------------------------------
15 rows in set (0.11 sec)再来一题
查询每个员⼯的信息及其部⻔的平均⼯资⼯资之和部⻔⼈数
select *,(select avg(sal) from emp e1 where e1.deptno emp.deptno) 平均工资,(select sum(sal) from emp e2 where e2.deptno emp.deptno) 工资之和,(select count(*) from emp e3 where e3.deptno emp.deptno) 部门人数
from emp;尝试-- 这种不行报 Operand should contain 1 column(s)
select *,(select avg(sal) 平均工资,sum(sal) 工资之和,count(*) 部门人数 from emp e1 where e1.deptno emp.deptno) from emp;改变思路在玩一种很新的东西mysql select deptno,avg(sal) 平均工资,sum(sal) 工资之和,count(*) 部门人数 from emp e1group by deptno ;
-----------------------------------------
| deptno | 平均工资 | 工资之和 | 部门人数 |
-----------------------------------------
| 10 | 2916.666667 | 8750.00 | 3 |
| 20 | 2175.000000 | 10875.00 | 5 |
| 30 | 1566.666667 | 9400.00 | 6 |
| 100 | NULL | NULL | 1 |
-----------------------------------------硬套进去失败了原因是这个地方只允许出现一列数据
select * , (select * from (select deptno,avg(sal) 平均工资,sum(sal) 工资之和,count(*) 部门人数 from emp e1group by deptno) pgb where pgb.deptno emp.deptno ) from emp;4.having后有子查询
查询平均⼯资⼤于30号部⻔的平均⼯资的部⻔号和平均⼯资
分析30号部⻔的平均⼯资
select avg(sal) from emp where deptno 30;
查询所有部门的平局工资
select deptno,avg(sal) from emp group by deptno;整合
select deptno,avg(sal) avgMoney from emp group by deptno having avgMoney (select avg(sal) from emp where deptno 30);
---------------------
| deptno | avgMoney |
---------------------
| 10 | 2916.666667 |
| 20 | 2175.000000 |
---------------------三.关键字EXISTSANYALL的子查询 举个例子 1.EXISTS关键字 EXISTS关键字后面的参数可以是任意一个子查询它不产生任何数据只返回TRUE或FALSE。 当返回值为TRUE时外层查询才会执行。 假如王五同学在学生表中则从班级表查询所有班级信息 MySQL命令
select * from class where exists (select * from student where sname王五);2.带ANY关键字的子查询 ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。 查询比任一学生所属班级号还大的班级编号 MySQL命令
select * from class where cid any (select classid from student);3.带ALL关键字的子查询 ALL关键字与ANY有点类似只不过带ALL关键字的子査询返回的结果需同时满足所有内层査询条件。 select * from class where cid all (select classid from student);
