素材库网站,建设网站需要多少时间,网站页面划分,泰安网站建设哪里找文章目录 一、MySQL复合查询基本查询回顾多表查询自连接子查询单行子查询多行子查询多列子查询在from子句中使用子查询合并查询 二、MySQL内外连接内连接外连接左外连接右外连接 一、MySQL复合查询
基本查询回顾 准备测试表 下面给出三张表#xff0c;员工表#xff08;emp员工表emp、部门表dept和工资等级表salgrade。
员工表(emp) 包含如下字段
雇员编号empno雇员姓名ename雇员职位job雇员领导编号mgr雇佣时间hiredate工资月薪sal奖金comm部门编号deptno 部门表(dept) 中包含如下字段
部门编号deptno部门名称dname部门所在地点loc 工资等级表(salgrade) 中包含如下字段
等级grade此等级最低工资losal此等级最高工资hisal 查询工资高于500或岗位为MANAGER的员工同时要求员工姓名的首字母为大写的J select ename,sal,job from emp where (sal500 or enameMANAGER) and ename like J%;查询员工信息按照部门号升序而雇员的工资降序排序 select ename,deptno,sal from emp order by deptno asc,sal desc;查询员工信息按年薪降序显示 select ename,sal*12ifnull(comm,0) 年薪 from emp order by 年薪 desc;注意
由于NULL与任何值做计算得到的结果都是NULL因此在计算年薪时不能直接用月薪的12倍加上每个员工的奖金这样可能导致得到的年薪为NULL值。在计算每个员工的年薪时应该通过ifnull函数判断员工的奖金是否为NULL如果不为NULL则ifnull函数返回员工的奖金如果为NULL则ifnull函数返回0避免让NULL值参与计算。 查询工资最高的员工的姓名和岗位 select ename,job from emp where sal(select max(sal) from emp);查询工资高于平均工资的员工信息 select ename,sal from emp where sal (select avg(sal) from emp);查询每个部门的平均工资和最高工资 select deptno,avg(sal) 平均工资,max(sal) 最高工资 from emp group by deptno;查询平均工资低于2000的部门和它的平均工资 select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资2000;查询每种岗位的雇员总数和平均工资 select job,count(*) 雇员总数,avg(sal) 平均工资 from emp group by job;多表查询
上面的基础查询都是在一张表的基础上进行的查询而实际开发中往往需要将多张表关联起来进行查询这就叫做 多表查询。
在进行多表查询时只需要将多张表的表名依次放到from子句之后用逗号隔开即可这时MySQL将会对给定的这多张表取笛卡尔积作为多表查询的初始数据源。多表查询的本质就是对给定的多张表取笛卡尔积然后在笛卡尔积中进行查询。
所谓的对多张表取笛卡尔积就是得到这多张表的记录的所有可能有序对组成的集合比如下面对员工表和部门表进行多表查询由于查询语句中没有指明筛选条件因此最终得到的结果便是员工表和部门表的笛卡尔积。 对部门表和工资等级表取笛卡尔积时会先从部门表中选出一条记录与工资等级表中的所有记录进行组合然后再从部门表中选出一条记录与工资等级表中的所有记录进行组合以此类推最终得到的就是这两张表的笛卡尔积。
需要注意的是对多张表取笛卡尔积后得到的数据并不都是有意义的比如对员工表和部门表取笛卡尔积时员工表中的每一个员工信息都会和部门表中的每一个部门信息进行组合而实际一个员工只有和自己所在的部门信息进行组合才是有意义的因此需要从笛卡尔积中筛选出员工的部门号和部门的编号相等记录。
这里说明一下当进行笛卡尔积的多张表中可能会存在相同的列名这时在选中列名时需要通过 表明.列名 的方式进行指明。 显示部门号为10的部门名、员工名和员工工资 select dname,ename,sal from emp,dept where emp.deptnodept.deptno and emp.deptno10;这里我们需要注意的是第一个筛选条件已经筛选出员工的部门号和部门编号相同的的记录因此在筛选部门号等于10的部门时可以使用员工表中的部门号也可以使用部门表中的部门编号。
select ename,sal,grade from emp,salgrade where sal between losal and hisal;说明一下员工表和工资等级表的笛卡尔积中将每一个员工的信息和每一个工资等级的信息都进行了组合而实际一个员工只有和自己的工资对应的工资等级信息进行组合才是有意义的。因此需要根据各个工资等级的最低工资和最高工资判断一个员工是否属于该工资等级进而筛选出有意义的记录。 自连接
自连接 是指在同一张表进行连接查询也就是说我们不仅可以取不同表的笛卡尔积也可以对同一张表取笛卡尔积。
如果一张表中的某个字段能够将表中的多条记录关联起来那么就可以通过自连接将表中通过该字段关联的记录组合起来。 显示员工FORD的上级领导的编号和姓名 我们可以使用子查询先对员工表进行查询得到FORD的领导的编号然后再根据领导的编号进对员工表进行查询得到FORD领导的姓名。
select empno,ename from emp where empno(select mgr from emp where enameFORD);当然解决该问题也可以使用自连接因为员工表中的mgr字段可以将表中员工的信息和员工领导的信息关联起来。
对员工表进行自连接后在where子句中指明筛选条件为员工的领导编号等于领导的编号这时就能筛选出每个员工信息与其领导信息组合形成的记录进一步指明筛选条件为员工的姓名为FORD这时便能筛选出员工FORD的信息和他的领导的信息组成的记录。
mysql select leader.empno,leader.ename from emp worker,emp leader - where worker.enameFORD and worker.mgrleader.empno;这里说明一下由于自连接是对同一张表取笛卡尔积因此在自连接时至少需要给一张表取别名否则无法区分这两张表中的列。 子查询
子查询 是指嵌入在其他SQL语句中的查询语句也叫嵌套查询。它可以分为单行子查询、多行子查询、多列子查询以及在 from子句 中使用的子查询。
单行子查询
单行子查询是指返回单行单列数据的子查询。 显示SMITH同一部门的员工 在子查询中查询SMITH所在的部门号在where子句中指明筛选条件为员工部门号等于子查询返回的部门号并且员工的姓名不为SMITH。
select * from emp where deptno(select deptno from emp where enameSMITH) and enameSMITH;此问题当然也可以使用自连接来解决如下
select t2.* from emp t1,emp t2 where t1.deptnot2.deptno and t1.enameSMITH and t2.enameSMITH;多行子查询
多行子查询 是指返回多行单列数据的子查询。 显示和10号部门的工作岗位相同的员工的名字、岗位、工资和部门号但是不包含10号部门的员工。这里主要用到了 IN 关键字 先查询10号部门有哪些工作岗位然后将上述查询作为子查询在查询员工表时在where子句中使用in关键字判断员工的工作岗位是子查询得到的若干岗位中的一个如果是则符合筛选条件由于要求筛选出来的员工不包含10号部门的因此还需要在where子句中指明筛选条件为部门号不等于10。
mysql select ename,job,sal,deptno from emp - where job in (select distinct job from emp where deptno10) and deptno10;显示工资比30号部门的所有员工的工资高的员工的姓名、工资和部门号这里主要用到了all关键字 先查询30号部门员工的工资这里最好对结果进行去重然后将上述查询作为子查询在查询员工表时在where子句中使用all关键字判断员工的工资是否高于子查询得到的所有工资如果是则符合筛选条件。
mysql select ename,sal,deptno from emp - where sal all(select distinct sal from emp where deptno30);当然这道题也可以使用单行子查询得到30号部门的最高工资然后判断员工工资是否高于子查询得到的最高工资即可。 显示工资比30号部门的任意员工的工资高的员工的姓名、工资和部门号包含30号部门的员工。这里主要用到了 ANY 关键字 mysql select ename,sal,deptno from emp- where sal any (select distinct sal from emp where deptno30);多列子查询
多列子查询是指返回多列数据的子查询。 显示和SMITH的部门和岗位完全相同的员工不包含SMITH本人 先查询SMITH所在的部门号和他的岗位然后将上述查询作为子查询在查询员工表时在where子句中指明筛选条件为部门号和岗位等于子查询得到的部门号和岗位并且员工的姓名不为SMITH即可。
mysql select * from emp - where (deptno,job)(select deptno,job from emp where enameSMITH) and enameSMITH;说明一下
多列子查询得到的结果是多列数据在比较多列数据时需要将待比较的多个列用圆括号括起来。多列子查询返回的如果是多行数据在筛选数据时也可以使用in、all和any关键字。 在from子句中使用子查询
子查询不仅可以出现在 where 子句中也可以出现在 from 子句中。子查询语句出现from子句中其查询结果将会被当作一个临时表使用。 显示每个高于自己部门平均工资的员工的姓名、部门、工资和部门的平均工资 先查询每个部门的平均工资由于显示信息中包含部门的平均工资需要同时使用员工表和上述的查询结果进行多表查询这时可以将上述查询作为子查询放在from子句中然后对员工表和临时表取笛卡尔积在where子句中指明筛选条件为员工的部门号等于临时表中的部门号并且员工的工资大于临时表中的平均工资。
mysql select ename,emp.deptno,sal,平均工资- from emp,(select deptno,avg(sal) 平均工资 from emp group by deptno) tmp- where emp.deptnotmp.deptno and sal 平均工资;这里我们需要注意的是在from子句中使用子查询时必须给子查询得到的临时表取一个别名否则将会出错。 显示每个部门工资最高的员工的姓名、工资、部门和部门的最高工资 mysql select ename,sal,emp.deptno,最高工资- from emp,(select deptno,max(sal) 最高工资 from emp group by deptno) tmp- where emp.deptnotmp.deptno and sal最高工资;显示每个部门的部门名、部门编号、所在地址和人员数量 在group by子句中指明按照部门号进行分组分别查询每个部门的人员数量。
mysql select dname,dept.deptno,loc,人员数量- from dept,(select deptno,count(*) 人员数量 from emp group by deptno) tmp- where dept.deptnotmp.deptno;合并查询
合并查询 是指多个查询结果进行合并可使用的操作符有 union 和 union all。
union 用于取得两个查询结果的并集union会自动去掉结果集中的重复行union all也用于取得两个查询结果的并集但union all 不会去掉结果集中的重复行 显示工资大于2500或职位是MANAGER的员工 在合并查询这里可以使用union操作符将上述的两条查询SQL连接起来这时将会得到两次查询结果的并集并且会对合并后的结果进行去重。
mysql select ename,sal,job from emp where sal2500- union- select ename,sal,job from emp where jobMANAGER;此外也可以使用 union all 操作符将上述的两条查询SQL连接起来这是也会得到两次查询结果的并集但不会对合并后的结果进行去重。如下 说明一下
待合并的两个查询结果的列的数量必须一致否则无法合并。待合并的两个查询结果对应的列属性可以不一样但不建议这样做。 二、MySQL内外连接
在 MySQL 中表的连接Table Join 是指通过共享一个或多个共同的列将两个或多个表中的数据联合起来的操作。这样可以根据关联列的值将数据进行关联以便在查询结果中获得更全面的信息。表的连接是 SQL 查询中的重要操作之一它允许我们从多个表中检索相关的数据。表的连接分为内连接和外连接。
内连接
内连接实际上就是利用 where 子句对两种表形成的笛卡儿积进行筛选我们前面学习的查询都是内连接也是在开发过程中使用的最多的连接查询。
内连接的SQL如下
select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;
select 字段 from 表1 inner join 表2 on 连接条件 where 其他条件;显示SMITH的名字和部门名称 给出一张员工表和一张部门表员工表中的ename表示员工的姓名deptno代表的是员工所在部门的部门号。如下 部门表中的dname代表的是部门名depno代表的是部门的部门号。如下 这里按照复合查询的做法就是取员工表和部门表的笛卡尔积在where子句中指明筛选条件为员工的部门号等于部门的部门号筛选出每个员工匹配的的部门信息并指明员工姓名为SMITH筛选出SMITH的信息和其所在的部门的信息。
select ename,dname from emp,dept where emp.deptnodept.deptno and enameSMITH;实际上述多表查询的方式本质就是内连接用标准的内连接SQL编写
将员工表和部门号放在from子句中并通过inner join关键字隔开。在on子句后指明内连接的条件为员工的部门号等于部门的部门号保证筛选出来的数据是有意义的。在and之后指明筛选条件为员工的姓名为SMITH。
这样筛选出来的结果和多表查询筛选出来的结果是一样的只不过是写法有一些不同而已。 外连接
左外连接
左外连接 返回左表中的所有行以及右表中与左表匹配的行。如果没有匹配的行右表的列将显示为 NULL。
select 字段名 from 表名1 left join 表名2 on 连接条件;先创建两张表学生表和成绩表。学生表中的name代表的是学生的姓名id代表的是学生的学号。
-- 建两张表
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);mysql select * from stu;
------------
| id | name |
------------
| 1 | jack |
| 2 | tom |
| 3 | kity |
| 4 | nono |
------------
4 rows in set (0.00 sec)mysql select * from exam;
-------------
| id | grade |
-------------
| 1 | 56 |
| 2 | 76 |
| 11 | 8 |
-------------
3 rows in set (0.00 sec)查询所有学生的成绩就算这个学生没有成绩也要将学生的个人信息显示出来 如果直接使用内连接将学生表和成绩表连接起来然后筛选出学生学号等于考试学生学号的记录那么只能筛选出来有考试成绩的学生信息。 这时我们可以在连接学生表和成绩表时将学生表放在左侧那么就可以使用左外连接这时如果左侧表中的某条记录根据连接条件没有找到匹配的右侧表中的记录就会直接显示左侧表中的记录信息而其对应的右侧表中的列信息将会用NULL值进行填充。如下
select * from stu left join exam on stu.idexam.id;右外连接
右外连接 与左外连接相反返回右表中的所有行以及左表中与右表匹配的行。如果没有匹配的行左表的列将显示为 NULL。
select 字段 from 表名1 right join 表名2 on 连接条件;查询所有的成绩就算这个成绩没有学生与它对应也要将学生成绩信息显示出来 select * from stu right join exam on stu.idexam.id;简单案例 列出部门名称和这些部门的员工信息同时列出没有员工的部门 题目要求同时列出没有员工的部门也就是部门表当中的内容需要完全被显示出来如果在连接部门表和员工表时将部门表放在左侧那么就可以使用左外连接。如下
select dname,emp.* from dept left join emp on dept.deptnoemp.deptno;当然如果要使用右外连接那么可以在连接部门表和员工表时将部门表放在右侧。如下
select dname,emp.* from emp right join dept on dept.deptnoemp.deptno;
