# MySQL Day04

MySQL Day04

# 重点

1.什么是子查询

• 子查询是指插入在其他SQL语句中的SELECT语句，也成为嵌套查询。使用子查询主要是将结果作为外部主查询的查询条件来使用的查询

2.什么时候使用子查询?

1. 当要显示的数据在表里出现并不存在，但可以通过对以由数据的加工获得，可通过子查询实现
2. 子查询可以出现SELECT、WHERE子句、FROM子句、DML子句、DDL子句。
3. 在SELECT、INSERT、UPDATE或DELETE命令中允许是一个表达式的地方都可以包含子查询，子查询甚至可以包含在另外一个子查询中

3.子查询的编写思路

• 子查询的编写思路：
  1. 仔细分析题目，确定要查询的表及字段
  2. 分析要查询的字段那些在标离直接存在，那些不存在
  3. 考虑如何把要显示的数据造出来（通过查询语句获得）
  4. 考虑子查询与表的连接点是什么（通常是主外键、共有字段）
  5. 考虑子查询放在什么位置
  6. 组合成完整的SQL语句

4.使用子查询的注意事项

1. 要将子查询放如圆括号中
2. 子查询出现在WHERE子句、FROM子句、SELECT列表和HAVING子句 DDL DML中
3. 子查询不能出现在主查询的 GROUP BY语句中
4. 子查询和主查询可以使用不同表，只要子查询返回的结果能够被主查询使用即可
5. 单行子查询只能使用单行操作符，多行自嘲寻只能使用多行操作符
6. 在多行子查询中，ALL和ANY操作符不能单独使用，而只能与单行比较（=、<、>、<>、<=、>=）
7. 要注意子查询中的空值问题，如果子查询返回了一个空值，则主要查询将不会查询任何结果
8. 在WHERE子句中进行子查询的时候，不能带有GROUP BY子句
9. 子查询允许嵌套许多层，不能超过255层

5.常规SQL语句的优化

1. 建议不用“”来代替所有列名
2. 用TRUNCAT 代替DELECT 删除数据表的所有数据时
3. 在确定完整性的情况下多用commit （事务提交）
4. 尽量减少表的查询次数
5. 用[NOT] EXISTS 代替[NOT] IN

6.什么是索引？

• 索引：目录 给一张表添加一个目录
• 索引 是一个单独的、物理的数据结构，是某个表中一列或若干列值得集合和相应得指向物理标识这些值得数据页得逻辑指定清单。索引依赖于表建立得，提供了编排表中数据得内部方法。
• 目的为了数据得查询效率

7.索引的作用有那些？

• 索引得作用：
  1. 为了提高效率
  2. 通过快速定位数据得方法，减少磁盘I/O操作

8.索引得分类有哪些？

1. 普通索引 不许要添加任何条件，可以创建在任何数据类型中，由字段本身得完整性约束决定
2. 唯一索引 使用UNIQUE 参数进行设置， 该值必须是唯一的 主键是一种特殊得唯一索引
3. 全文索引 使用FULLTEXT参数进行设置， 只能创建在char，varchar或者text类型得字段上（适用于查询数据较大得字符串类型得字符）
4. 单列索引 在表中单个字段上创建，只能根据该字段进行索引查询，只要保证该索引只对应一个字段即可。多列索引在表中多个字段上创建，可根据多个字段进行索引查询（注意 只有查询条件中使用了这些字段中得第一个字段时，索引才会被使用）例如 id，name， age询条件中使用了 id 字段时该索引才会被使用。
   5.空间索引（用的比较少）使用spatial参数进行设置，只能建立在空间数据类型上（geomertry、point、linerstering、polygon）

9.创建索引得原则由哪些？

1.选择唯一索引 因为唯一索引得值是唯一得，可快速通过该索引来确定某条记录 例如 人–>身份证号 学生–>学号
2. 为经常需要排序、分组和联合操作得字段建立索引 频繁使用 order by、group by、distinct和union等来操作字段
3. 经常作为查询条件得字段建立索引
WHERE 条件 经常使用得字段可以创建索引， 可以给外键创建索引
4. 限制索引得数目：索引得数目并不是越多越好，每个索引都要占磁盘空间，修改表时，对索引得重构和更新比较麻烦
5. 尽量使用数据量少得索引
6. 尽量使用前缀来索引 检索值很长时，比如 text】blog 之间所前面得若干字符
7. 不使用或使用频率低得，应尽快删除 不适合建索引得情况 1.表很小 2. 字段不经常出现在WHERE子句中 3. 每次访问得数据量 大于记录总数得2%-4% 4.字段经常更新

实际应用

（一）子查询

1. 单行子查询
   示例1
   在WHERE子句中得单行子查询

   -- 1. 查询部门名称为"RESEARCH"的员工信息(显示员工号，姓名，职位)
   SELECT empno，ename，job FROM emp WHERE deptno=（SELECT deptno FROM dept WHERE dname='RESEARCH');
   

   这个要求可以看成两个部分
   1. 先找到部门为 RESEARCH
   2. 在部门中查询员工信息
      注意： 子查询 一般使用 分步法 及先写出缩小的范围 再在缩小的范围中查找
      示例2
      在HAVING子句中得单行子查询

   --2.查询出各部门员工的平均工资低于各部门高平均工资的部门号和部门的平均工资
   SELECT deptno,AVG(sal) avgsal
   FROM emp
   Group BY deptno
   HAVING avg(sal)<(select MAX(sal) as avgasl from emp group by deptno) e);
   

   注意 单行子查询
   • WHERE条件限定不规范二返回多行 就会出现单行子查询返回多行得错误
   • 子查询中不能包含ORDER BY 子句 相反 任何排序都必须在外部查询中完成
   • MySQL中不允许聚合函数直接嵌套 MAX(AVG(sal)) 。在Oracle中可以

---

2. 多行子查询
   多行子查询中得操作符使用
   IN操作符
   示例1

-- 标准嵌套子查询多层嵌套
--  列出薪水与销售部门在同部门的员工薪水相同的所有员工的姓名和薪金。 
    SELECT ename ,sal
    FROM emp
    WHERE sal in(
        select sal
        from emp
        where deptno=(
            select deptno
            from dept
            where dname="SALES"
        )
    )
 -- 至少有4个员工得所有部门信息
     SELECT * from dept
     where deptno IN(select deptno
                      from dept
                      GROUP BY deptno
                      HAVING count(*)>=4)

ANY操作符

-- 在emp表中，查询工资大于部门编号为10的任意一个员工工资的其他部门的员工信息。          SELECT * 
   FROM emp 
   WHERE sal>ANY(
   SELECT sal FROM emp WHERE deptno=10) 
   AND deptno<>10)

ALL操作符

-- 显示工资大于所有部门平均工资的雇员姓名,工资。 
SELECT ename , sal
from emp
where sal>all(
select avg(sal)
from emp
GROUP BY deptno
HAVING avg(sal))

重点：EXISTS 操作符

--  查询在“SALES"销售部门的所有员工信息。
SELECT  * 
FROM emp e 
WHERE  EXISTS(SELECT deptno 
               FROM dept  d              
               WHERE e.deptno = d.deptno 
               AND d.dname='SALES')
-- 查询在NEW YORK工作的所有雇员的名字、职位、薪水、所在部门
select ename,sal ,job
from emp e
where exist(
           select deptno
           from dept d
           where e.loc="NEW YORK" AND e.deptno=d.deptno)

3. 多列表查询

-- 查询显示和ALLEN同部门同职位的员工姓名、职位、部门编号
select ename ，job，deptno
from emp e
where （daptno，job）=（select deptno，job from emp where job=“ALLEN”）；

-- 方法二
SELECT ename,job,deptno
FROM emp
WHERE deptno=(SELECT deptno FROM emp WHERE ename="ALLEN")
and job=(SELECT job FROM emp WHERE ename="ALLEN");

4. 关联子查询
   注意： 在单行子查询和多行子查询中，内查询与外查询时分开执行得，也就是说内查询得执行与外查询得执行没有关系，外查询仅仅是使用内查询得最终结果
   但是关联子查询中得内查询与外查询是相互关联的
   内查询得执行需要借助外查询，二外查询得执行又离不开内查询得执行

• 在where 中使用子查询

--  在emp表中，使用“关联子查询”检索工资大于同职位的平均工资的员工信息(显示字段：员工编号，姓名，工 资）
    select empno,ename,sal
    from emp e
    where sal>(select avg(sal) from emp m where m.job=e.job)
--  查询所有大于本部门平均工资的员工信息 
    select *
    from emp e
   where sal>(select avg(sal) from emp m where m.deptno= e.deptno)

• 在select中使用子查询

-- 任务 统计出有奖金和没有奖金的人数 
SELECT (SELECT COUNT(comm) FROM emp WHERE comm!=0) hascomm,
(SELECT COUNT(*) FROM emp WHERE comm is null) nocomm
from  DUAL;
*

• 插入语句中使用子查询

--  将20号部门的员工信息插入新的员工表emp1中 （emp1与emp结构一样，没有数据) 
INSERT INTO emp1 SELECT * FROM emp WHERE deptno=20; 
COMMIT;  -- 若开启事务，手动提交 

• FROM 子句中使用子查询

-- 检查部门编号，部门名称，部门所在地及其每个部门得员工人数
select d.deptno,d.dname,d.loc,e.count
from dept d,(select deptno, count(*) count from emp GROUP BY deptno )e
where d.deptno = e.deptno; *

– 合并查询结果

-- 查询工资大于2500或者职位为经理的员工的姓名，工资，职位
SELECT ename,sal,job 
FROM emp 
WHERE sal>2500
UNION ALL 
SELECT ename,sal,job 
FROM emp 
WHERE job='MANAGER';