优秀网站设计分析,邢台地区网站建设服务周到,企业展厅设计费用多少,做付费动漫网站Welcome to 9ilks Code World (๑•́ ₃ •̀๑) 个人主页: 9ilk
(๑•́ ₃ •̀๑) 文章专栏#xff1a; MySQL #x1f3e0; 基本查询回顾
假设有以下表结构#xff1a; 查询工资高于500或岗位为MANAGER的雇员#xff0c;同时还要满足他们的姓名首字母为… Welcome to 9ilks Code World (๑•́ ₃ •̀๑) 个人主页: 9ilk
(๑•́ ₃ •̀๑) 文章专栏 MySQL 基本查询回顾
假设有以下表结构 查询工资高于500或岗位为MANAGER的雇员同时还要满足他们的姓名首字母为大写的J 思路 : 用两个条件对员工表进行筛选。条件1工资高于500或岗位为MANAGER。条件2姓名首字母为大写的J。条件1内为或关系条件1和条件2为并联关系。 参考代码
//like
select * from emp where (sal500 or jobMANAGER) and ename like J%;
//子串
select * from emp where (sal500 or jobMANAGER) and substring(ename,1,1)J; //截取子串
测试结果 按照部门号升序而雇员的工资降序排序
参考代码
select * from emp order by deptno asc , sal desc; //asc升序 desc降序
测试结果 使用年薪进行降序排序 员工表中的comm奖金字段可以为空但是MySQL中NULL是不参与运算的,我们可以使用ifnull函数进行处理。 参考代码
//奖金可以为空有的岗位没奖金 所以对这种情况可以使用ifnull 是null就第二个参数
select *,sal*12ifnull(comm,0) 年薪 from emp;
测试结果 显示工资最高的员工的名字和工作岗位 求最高可以使用排序也可以使用聚合函数。 参考代码
select ename,job from emp order by sal desc limit 1;//排序
select ename, job from emp where sal (select max(sal) from emp); //聚合函数子查询
测试结果 注MySQL允许在一条SQL内部再执行select查询称为子查询
显示工资高于平均工资的员工信息 思路我们先需要知道员工表中所有员工的平均信息然后在员工表中根据平均工资筛选员工信息。 参考代码
select * from emp where sal (select AVG(sal) from emp); //先聚合统计平均工资
测试结果 显示每个部门的平均工资和最高工资
参考代码
select deptno,AVG(sal),max(sal) from emp group by deptno;
//先分组 再聚合
测试结果 显示平均工资低于2000的部门号和它的平均工资 思路“我们先根据部门进行分组然后对每个组进行聚合取得平均工资最后对分组之后的结果having进行筛选。 参考代码
select deptno,AVG(sal)平均工资 from emp group by deptno having 平均工资 2000;
测试结果 显示每种岗位的雇员总数平均工资
参考代码
select AVG(sal) 平均工资, count(ename) from emp group by job;
select job,count(*), format(avg(sal),2) from emp group by job;
测试结果 多表查询 实际开发中往往数据来自不同的表所以需要多表查询。本节我们用一个简单的公司管理系统有三张表emp,dept,salgrade来演示如何进行多表查询。 案例
显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表因此要联合查询 分析雇员名和雇员工资信息来自员工表而所在部门名字的信息来自部门表那我们需要两张表的数据进行组合。 多表查询本质将多张表中数据进行穷举组合多张表进行笛卡尔积。此时多张表变为单表多表操作转化为对单表的操作 多表笛卡尔积会有多种组合结果,但有的组合结果是没有意义的,所以只要emp表中的deptno dept表中的deptno字段的记录,其他的都是没意义的。 参考代码
select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno dept.deptno;
//
select ename,sal,dname from emp,dept where emp.deptno dept.deptno;
测试结果 注对于两张表中各自的特有字段在查询时不需要指明所属哪张表如果是共有字段则需要指明是哪一张表的否则会发生冲突。 MySQL中一切皆表组合之后的表也是表结构也可以对该表结构的数据进行整合。 显示部门号为10的部门名员工名和工资
参考代码
select emp.ename,dept.dname,sal from emp,dept where (emp.deptnodept.deptno and emp.deptno10);
测试结果 显示各个员工的姓名工资及工资级别 思路工资级别以及工资信息在工资表里因此我们需要多表查询。同时工资表中有工资等级所属的工资范围我们可以根据范围来判断员工表中员工薪资所属等级。 参考代码
select ename,sal,grade from emp,salgrade where sal between losal and hisal;
测试结果 自连接 自连接是指在同一张表连接查询也就是同一张表进行笛卡尔积。 可行性
select * from salgrade,salgrade;
测试结果 注两张相同的表进行笛卡尔积表名相同会造成冲突我们需要对两张表进行重命名。 案例 显示员工FORD的上级领导的编号和姓名mgr是员工领导的编号--empno
方法1使用子查询 思路先用子查询获取工FORD的上级领导的编号再通过编号筛选出领导的相关信息。 参考代码
select ename,empno from emp where empno(select mgr from emp where enameFORD);
测试结果: 方法2使用多表查询 思路两张相同表进行笛卡尔积假设t1为单纯员工表t2用来作为“查询上级表”则我们可以根据t2的mgr找出t1中是xxx的上级的员工然后再筛选出t2表中名字是FORD最后筛选出t1表中所求上级的编号和姓名。 参考代码 select t1.empno,t1.ename from emp as t1,emp as t2 where t1.empnot2.mgr and t2.enameFORD;
测试结果 注from执顺序先于where因此where可以使用表的重命名 子查询 子查询是指嵌入在其他sql语句中的select语句也叫嵌套查询。 单行子查询 单行子查询返回一行记录的查询。 显示SMITH同一部门的员工
参考代码
select * from emp where deptno(select deptno from emp where enameSMITH);
测试结果 多行子查询 多行子查询返回多行记录的子查询。 in关键字查询和10号部门的工作岗位相同的雇员的名字岗位工资部门号但是不包含10自己的。 思路我们可以根据子查询10号部门的工作岗位然后进一步筛选。 参考代码
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno10) and deptno10;
测试结果 如果还想知道上面条件对应的员工属于部门的名字呢 此时我们可以用上面筛选出来的“表”再和部门表进行笛卡尔积筛选出部门名字
参考代码
select ename,job,sal,dname from (select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno10) and deptno10) as tmp,deptp,dept where tmp.deptnodept.deptno;
测试结果 注一个SQL的查询结果也是一个表结构MySQL一切皆表不是物理上真实存在的表才能做笛卡尔积。同时子查询不仅能出现在where后也能出现在from后
all关键字显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号。 all表示的是查询结果中最大的 测试代码
select ename,sal,deptno from emp where sal all(select sal from emp where deptno30);
//也可以使用聚合函数MAX
select ename,sal,deptno from emp where sal (select MAX(sal) from emp where deptno30);
测试结果 any关键字显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号包含自己部门的员工。 any表示的就是查询结果中的任意一个。 参考代码
select ename,sal,deptno from emp where sal any(select sal from emp where deptno30);
测试代码 多列子查询 单行子查询是指子查询只返回单列单行数据多行子查询是指返回单列多行数据都是针对单列而言的而多列子查询则是指查询返回多个列数据的子查询语句。 案例查询和SMITH的部门和岗位完全相同的所有雇员不含SMITH本人。
思路我们需要根据两个列的字段(部门和岗位)进行筛选然后根据筛选结果筛选出其他雇员的信息。
参考代码
select * from emp where deptno(select deptno from emp where enameSMITH) and job(select job from emp where enameSMITH) and enameSMITH;
//多列子查询
select * from emp where (deptno,job) (select deptno,job from emp where enameSMITH) and enameSMITH;
测试结果 注使用多列子查询时括号内的列顺序和数目要和子查询的列顺序和数目匹配 总结目前全部的子查询都在where子句中充当判断条件但其实任何时刻查询出来的结构本质在逻辑上也是表结构 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.detnnoptnotmp.dt;
//
select emp.ename,emp.deptno,emp.sal,tmp.asal from (select AVG(sal) asal,deptno from emp group by deptno) as tmp,emp where (emp.sal tmp.asal) and emp.depdeptnotmp.deptno;测试结果 查找每个部门工资最高的人的姓名、工资、部门、最高工资 思路先找出每个部门的最高工资分组,再根据这个子表和原表进行笛卡尔积,找出原表中工资等于子表中筛选出的每个部门的最高工资的满足两表部门号相同。 参考代码
select emp.deptno,emp.ename,emp.sal,tmp.msal from (select deptno,MAX(sal) msal from emp group by deptno) tmp,emp where tmp.msal emp.sal and tmp.deptnoemp.deptno;
测试结果 显示每个部门的信息部门名编号地址和人员数量
1方法1使用子查询
参考代码
select dept.dname,dept.loc,dept.deptno,tmp.num from dept,(select deptno,count(*) num from emp group by deptno) tmp where dept.deptno tmp.deptno;
//1.对EMP表进行人员统计
//2.将上面的表看作临时表
测试结果 2使用多表
参考代码
select emp.deptno,count(*),dept.dname,dept.loc from emp,dept where emp.deptnodept.deptno group by emp.deptno,dept.loc,dept.dname;
测试结果 总结解决多表问题的本质:首先是先想办法把多表转化为单表,所以MySQL中所有select的问题全部都可以转化为单表问题! 合并查询 在实际应用中为了合并多个select的执行结果可以使用集合操作符 unionunion all。 1. union 该操作符用于取得两个结果集的并集。当使用该操作符时会自动去掉结果集中的重复行。 案例将工资大于2500或职位是MANAGER的人找出来
参考代码
select ename,sal,job from emp where sal2500 union select ename ,sal,job from emp where jobMANAGER;
测试结果 2. union all 该操作符用于取得两个结果集的并集。当使用该操作符时不会去掉结果集中的重复行。 注使用合并查询时列信息必须一样 表的内连接 内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选我们前面学习的查询都是内连接也是在开发过程中使用的最多的连接查询。 语法
select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件
注前面学习的都是内连接。
案例显示SMITH的名字和部门名称
1. where
-- 用前面的写法
select ename, dname from EMP, DEPT where EMP.deptnoDEPT.deptno and
enameSMITH;
2. 标准内连接
-- 用标准的内连接写法
select ename, dname from EMP inner join DEPT on EMP.deptnoDEPT.deptno and
enameSMITH; 表的外连接 外连接分为左外连接和右外连接。 左外连接 如果联合查询左侧的表完全显示我们就说是左外连接。 案例
-- 建两张表
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);
查询所有学生的成绩如果这个学生没有成绩也要将学生的个人信息显示出来
参考代码
-- 当左边表和右边表没有匹配时也会显示左边表的数据
select * from stu left join exam on stu.idexam.id;
测试结果 此时左表中每一个id都会显示,即使在右表没找到相同的id。 右外连接 如果联合查询右侧的表完全显示我们就说是右外连接。 语法
select 字段 from 表名1 right join 表名2 on 连接条件
案例
对stu表和exam表联合查询把所有的成绩都显示出来即使这个成绩没有学生与它对应也要显示出来。
参考代码
select * from stu right join exam on stu.idexam.id;
测试结果 列出部门名称和这些部门的员工信息同时列出没有员工的部门
1. 方法一左外连接
select d.dname, e.* from dept d left join emp e on d.deptnoe.deptno;
2. 方法二右外连接
select d.dname, e.* from emp e right join dept d on d.deptnoe.deptno;
测试结果 完。
