MySQL查询的习题及解题思路

MySQL查询的解题思路

练习习题

• 查询如果某个字段的值为null，将该字段的值重新给定值
• 语法：

select  字段名1,..............,if(isnull(为空值的字段名),'重新赋的值',该字段名),字段名n,.......

• 示例1：

select ename,if(isnull(comm),'无奖金',comm) as if_null,deptno from emp where deptno=30;

• 运行结果：

内连接查询

• 示例1： 查询已经分配了部门（部门号不为NULL）的员工的信息
• 思路：首先通过inner join 把员工表和部门表连接起来，如果两个表的员工部门号相等即为不为空，如果部门表中的有一个部门号字段对应值是null，那么员工号对应的部门编号也是null，所以还要有一个条件是限定部门表中和员工表中的部门号都不为null.
• 示例1语句：

select * from emp inner join dept on emp.deptno=dept.deptno where emp.deptno is not null and dept.deptno is not null ;

• 运行结果：

• 示例2：查询员工王五所在部门的所有员工信息
• 思路：首先将员工表和部门表用inner join连接起来，条件是两个表中的对应的部门编号相等，还有一个条件是查找到王五对应的部门编号是多少作为外查询的子条件
• 示例2语句：

select * from emp inner join dept on emp.deptno=dept.deptno where emp.deptno=(select emp.deptno from emp where emp.ename='王五');

• 运行结果：

外连接查询

• 示例1：查询所有部门名称及部门对应员工的姓名
• 思路：查询的两个字段是部门名称和学生姓名，用左外连接查询，用left outer join 连接员工表和部门表，满足条件是员工对应的部门id和部门里部门id相等时返回查询结果。这里如果部门对应的员工为null将有一行部门名称对应没有员工。可以家条件不返回该数据
• 示例1语句：

select emp.ename,dept.dname from dept left outer join emp on dept.deptno=emp.deptno;

• 运行结果：

• 示例2：查询所有员工姓名及对应部门的名称，没有分配部门的员工也需要查询出来
• 思路：根据查询条件我们同样用示例1的方式来书写查询语句，注意把left换为right，满足了右表为员工表emp的所有字段都能查出来，如果对应没有分配的部门则为null,满足题目条件
• 示例2语句：

select emp.ename,dept.dname from dept right outer join emp on dept.deptno=emp.deptno;

运行结果：

复合条件连接查询

• 示例：查询所有员工信息，员工信息包含员工所在部门的名称，并且按员工的工资降序排序
• 思路：通右外连接的方式righ outer join 对部门表和员工表进行连接，on的条件是如果员工表的部门号和部门中的部门表部门号相等，然后后面的条件是对员工信息进行排序，排序规则是根据员工的工资desc降序排序。
• 示例语句：

 select *,dept.dname from dept right outer join emp on dept.deptno=emp.deptno where sal order by sal desc;

• 运行结果：

in关键字结合子查询

• 示例1：查询工资大于2900的员工所属部门
• 思路：查询返回为部门名称，通过题目我们知道，影响查询返回的数据可能是多条的，如果只是用算术运算符，返回结果可能为错误，同时明白子查询的结果是用来给外查询作为查询限制条件的，所以在mysql中子查询总是优先执行的
• 示例1语句：

select dept.dname from dept where dept.deptno in(select emp.deptno from emp where emp.sal > 2900);

• 查询结果：在这里插入图片描述

• 示例2：查询工资小于2900的员工所在的部门信息
• 思路：查询方式如示例所示，该题可理解为最后返回查询的结果为部门表的全部字段信息，示例1为大于2900的条件在示例二中恰好是相反的，那么在示例1的子查询条件外in前面加上not表示相反的条件下查询。
• 示例2语句：

select dept.* from dept where dept.deptno not in(select emp.deptno from emp where  emp.sal > 2900);

• 查询结果：

关键字exists结合的子查询

• 示例：查询工资大于2900的员工所在的部门信息
• 思路：需要返回条件查询的是部门信息，就需要在子查询中能满足条件，否则该条件为false时，exists的外查询是并不被执行的。。
• 示例语句：

select dept.* from dept where exists (select emp.deptno from emp where emp.sal > 2900);

• 查询结果：

any关键字的结合查询

• 示例：
• 查询部门编号为10的员工信息，要求查询到的员工信息中，工资都高于部门编号为20的部门中的最低工资
• 思路：注意关键字any的含义，是只要满足其一的条件都能够被查询返回，所以第一个条件是员工编号是10，其次是工资大于编号为20的员工最低工资，子查询中的条件是编号为20的员工的最低工资
• 示例语句：

select emp.* from emp,dept where dept.deptno=10 and emp.sal > any(select min(emp.sal)  from emp where emp.deptno=20);

• 查询结果：

all关键字的结合子查询

• 示例：
• 查询部门编号为10的员工信息，要求查询到的员工信息中，工资都高于部门编号为20的部门中的最高工资
• 思路：查询思路和any示例查询差不多，但是要注意一点是该查询的外查询条件返回 的结果需要满足子查询的条件的所有记录。该记录中求的是满足部门号为20的最大员工工资。
• 示例语句：

select emp.* from emp,dept where dept.deptno=10 and emp.sal > all(select max(emp.sal) from emp where emp.deptno=20);

• 查询结果：

any的使用特点

• 示例：
• 查询已借阅图书中，价格比任意未借阅的图书价格还低的图书信息，图书信息只需显示图书名称、图书价格和状态
• 思路：看到这样的题我们习惯性的使用where条件和and去处理，但是如果返回超一条记录时不能这样书写，否则就会返回数据错误
• 如下的错误结果：

• 正确示例语句：

select b.name , b.price , b.state from book as b where b.state > 0 and b.price < any(select b.price from book as b where b.state=0);

• 查询结果如下：

)]

• 正确示例语句：

select b.name , b.price , b.state from book as b where b.state > 0 and b.price < any(select b.price from book as b where b.state=0);

• 查询结果如下：