第二周总结

数据库别名使用

• 使用语法:列的别名在列名后，中间不加或者加一个AS关键字
• 作用：减少可读性差，增加可读性
• 注意：
  - 别名不允许时SQL关键字
  - 别名本身不区分大小写，且不能含有空格
  - 加上双引号区分大小写，且可以含有空格

SELECT ename name,sal salary,deptno
FROM emp
WHERE deptno =10

  所有数据库通用的不等于号 <>

常用的一些关键字

• AND OR关键字 （与，或条件）

  • AND 代表条件与
  • OR 代表条件或
  • 注意：
    1. AND 和OR 可以多个连接
    2. AND的优先级大于OR
    3. 当多个OR与AND相连时可以通过括号提高OR的优先级

   SELECT * FROM emp WHERE sal>1000 AND job='CLERK'
  
  SELECT * FROM emp WHERE sal>1000 OR job ='CLERK'
  
  SELECT ename,sal,job FROM emp WHERE sal>1000 AND  (job = 'SALESMAN'OR job = 'CLERK' )
  

• LIKE 关键字（模糊查询）

  • 注意:
    - LIKE通常要借助两个通配符
    - % ：表示0到多个字符
    - _:表示单个字符

  SELECT  ename,sal
  FROM emp
  WHERE ename LIKE '_A%'
  

• IN 和NOT IN关键字（判断在列表中和不在列表中）

  • IN 和NOT IN常用来判断子查询的结果

  SELECT * FROM emp WHERE job IN ('SALESMAN','CLERK')
  
  SELECT * FROM emp WHERE deptno NOT IN (10,20)
  

• BETWEEN…AND…关键字

  • 查询某个值域范围条件的数据
  • 最常用的是适用于数字类型的数据范围，但对字符类型和日期类型的数据同样有效

  SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000
  

• ANY 和ALL条件

  • ANY和ALL不能单独使用，需要配合单行比较操作符>,<,>=,<=一起使用
  • 1.>ANY(list) 大于列表中最小的即可
    2.>ALL(list) 大于列表中最大的 。。。。。
  • ANY和ALL常用于子查询

  SELECT empno,ename FROM emp WHERE sal < ANY (SELECT sal FROM emp WHERE deptno = 10);
  

   可以使用函数或表达式的结果作为过滤条件
  

  • 例如

  SELECT * FROM emp WHERE sal * 12 >30000
  SELECT ename,sal FROM emp WHERE ename = UPPER('king')
  

• DISTINCT过滤重复

  • 写在SELECT之后，对指定字段的重复记录进行去重
  • 注意：不可再DISTINCT前增加字段
  • 对多字段去重，时多字段的组合没有重复

  SELECT DISTINCT job 
  FROM emp
  SELECT DISTINCT job,deptno 
  FROM emp
  

• ORDER BY 排序

  • 对数据按照一定规则进行排序操作，必须出现在SELECT中的最后一个字句
  • ASC:升序，不写默认值就是升序
  • DESC:降序
  • 注：多字段排序时，先按照第一个字段排序，如果第一个字段有重复，排序按照第二个字段排序。以此类推。
  • 如果排序的字段中由NULL值，则NULL被认为最小值

  SELECT ename,sal FROM emp ORDER BY sal
  
  SELECT ename,sal FROM emp ORDER BY  sal DESC 
  
  SELECT ename, sal,deptno FROM emp ORDER BY deptno DESC,sal DESC
  
  SELECT ename, sal,deptno,comm FROM emp ORDER BY comm DESC
  

• 聚合函数（统计），或称为多行函数，分组函数

                聚合函数是对结果集某些字段进行统计的，所有聚合函数都忽略对NULL值统计
  

  • MAX MIN 求给定字段的最大值最小值（对值统计）

     SELECT MAX(sal),MIN(sal) FROM emp 
    

  • AVG SUM 求平均值，总和(对值统计)

    SELECT AVG(sal),SUM(sal) FROM emp
    SELECT AVG(IFNULL(comm,0)) FROM emp
    

  • COUNT 不是对值统计，而是对给定字段不为NULL的记录数进行统计的

    SELECT COUNT(ename) FROM emp
    SELECT COUNT(comm) FROM emp
    SELECT COUNT(IFNULL(comm,0)) FROM emp
    

• GROUP BY （分组）

  • GROUP BY 可以将结果集按照其后指定字段值相同的看作一组，然后配合聚合函数进行更细分的统计工作，
  • GROUP BY也可以根据多个字段进行分组，分组原则为这几个字段值都相同的看作一组

  SELECT ROUND(AVG(sal)),deptno 
  FROM emp GROUP BY deptno
  
  SELECT MAX(sal) FROM emp
  GROUP BY job
  
  SELECT AVG(sal),job,deptno
  FROM emp
  GROUP BY job,deptno
  

  • 当SELECT字句中含有聚合函数时，那么凡不在聚合函数中的其他单独字段都必须出现在GROUP BY 中，反过来则不是必须的
  • WHERE不能使用聚合函数作为过滤，原因是过滤的时机不对.WHERE是在数据库检查表中数据时对数据逐条过滤，以决定是否查询出该条数据时使用的，所以WHERE用来确定结果集数据，不适用于聚合函数。
  • 使用聚合函数作为过滤条件，那么一定是从表中查数据完毕（WHERE在查询过程中发挥作用）得到结果集，并且分组完毕进行函数统计结果，得到后才能对分组进行过滤，由此可见这个过滤时机是在WHERE之后进行的。
  • 聚合函数的过滤条件要在HAVING子句中使用，HAVING必须跟在GROUP BY子句之后，HAVING是用来过滤分组的。

      SELECT deptno,AVG(sal)
      FROM emp 
     GROUP BY deptno
     HAVING AVG(sal) >2000
    
     SELECT deptno,MAX(sal),MIN(sal)
     FROM emp
     GROUP BY deptno 
     HAVING AVG(sal)>2000
    

查询语句的执行顺序

• 1 . FROM子句 执行顺序为从后往前，从右到左

  • 数据量较少的表尽量放后面

• 2 .WHERE子句：执行顺序为自下而上，从右到左

  • 将能过滤掉的做大数量的条件放在WHERE子句的最前面

• 3 .GROUP BY ：执行顺序为从左往右分组

  • 最好在GROUP BY之前使用WHERE 将不需要的记录在GROUP BY之前过滤掉

• 4 . HAVING 子句：消耗资源

  • 尽量避免使用，HAVING会在检索出所有记录之后才会对结果集进行过滤，需要排序等操作

• 5 . SELECT子句 ：少用*号，尽量取字段名称

• 6 . ORDER BY子句：执行顺序为从左到右排序，消耗资源

关联查询

• 从多张表中查询数据
• 关联查询的重点在于这些表中有对应关系，这个关系也称为过滤条件
• 关联查询的表名也可以取别名，可以简化SELECT语句的复杂度
• 当关联查询的表具有相同字段时，必须明确指定该字段来自哪张表。
• 关联查询需要添加连接条件，否则会产生笛卡尔积，笛卡尔积是一个无意义的结果集，他的记录是所有参加查询的表的记录数。尽量要避免出现笛卡尔积，数据量大时，极易出现内存溢出的现象。
• N张表连接查询时就要有N-1个连接条件（至少有N-1个）

SELECT e.ename,d.dname,e.deptno
FROM emp e,dept d
WHERE e.deptno=d.deptno

SELECT emp.ename,dept.loc,emp.deptno
FROM emp,dept
WHERE emp.deptno = dept.deptno
AND dept.loc = 'NEW YORK'

SELECT emp.ename,emp.sal,dept.dname,dept.loc
FROM emp,dept
WHERE emp.deptno = dept.deptno
AND emp.sal>3000

-- 三张表 
SELECT e.ename,m.ename,m.deptno,p.loc
FROM emp e,emp m,dept p
WHERE e.mgr = m.empno
AND m.deptno = p.deptno
AND e.ename = 'SMITH'

• 内连接
  • 语法 表1 JOIN 表2 ON 关联条件
  • 内连接也是用来完成关联查询的
  • 不满足连接条件的记录是不会在关联查询中被查询出来的

  SELECT emp.ename,emp.deptno,dept.loc
  FROM emp JOIN dept 
  ON emp.deptno = dept.deptno()
  WHERE emp.sal>3000
  
  --三张表的内连接
  SELECT e.ename,m.ename,m.deptno,p.loc
  FROM emp e JOIN emp m 
  ON e.mgr = m.empno 
  JOIN dept p
  ON m.deptno =p.deptno
  WHERE e.ename = 'SMITH'
  

• 外链接

  • 除了会将满足条件的记录查询出来，同时也还会将不满足条件的记录查询出来

  • 左外链接：以JOIN左侧的表作为驱动器表（所有数据会被查询出来），当该表中的某条记录不满足链接条件时，来自右侧的表的全部字段填NULL

    • 语法LEFT OUT JOIN 表1 ON 表2

        SELECT emp.ename,dept.dname
        FROM emp LEFT OUTER JOIN dept
        ON emp.deptno = dept.deptno
    

  • 右外链接:同左外链接定义

    • 语法RIGHT OUT JOIN 表1 ON 表2

     SELECT emp.ename,dept.dname
     FROM emp RIGHT OUTER JOIN dept
     ON emp.deptno = dept.deptno
    

  • 全外链接:同左外链接

    • MYSQL 暂不支持全外链接
• 自链接
  • 自链接即当前表的一条记录可以对应当前表的多条记录
  • 自链接是为了解决同类型数据，但是又存在上下级关系的树状结构数据时使用

  SELECT e.ename,m.ename
  FROM emp e,emp m
  WHERE e.mgr = m.empno 
  

子查询

• 子查询是一条SELECT语句，但是它嵌套在其他的SQL语句中的，为的是给该SQL提供数据，以支持其操作。

• 在DDL中使用子查询

  • 可以根据子查询的结果快速的创建一张表
  • 创建表时若子查询中的字段有别名，则该表对应的字段就使用该别名作为其字段名
  • 当子查询一个字段有函数或者表达式，那么该字段必须给别名

  CREATE TABLE employee
  AS
  SELECT e.empno,e.ename,e.job,e.sal,
   		 e.deptno,d.dname,d.loc
  FROM emp e LEFT OUTER JOIN  dept d
  ON e.deptno=d.deptno
  

• 在DML中使用子查询

  • MYSQL 不支持子查询删除
  • 删除子查询的思路：1.把子查询作为临时表存储.2把这个临时表当做原表删除条件。3.删除临时表
  • 子查询常用于SELECT语句中
    • 单行单列子查询：常用于过滤条件，可以配合逻辑运算符使用

    SELECT deptno,ename,sal 
    FROM emp 
    WHERE sal>(SELECT AVG(sal) FROM emp)
    

    • 多行单列子查询：常用于过滤条件，由于查询出多个值，在判断等于时要使用IN，判断大于小于时，使用ALL，ANY。

    SELECT ename,job,deptno
    FROM emp
    WHERE deptno 
    IN (SELECT deptno
    	FROM emp
    	WHERE job = 'SALESMAN'
    	)
    AND job <> 'SALESMAN'
    
    
    SELECT ename,job,deptno
    FROM emp
    WHERE sal >ALL(
    SELECT sal 
    FROM emp
    WHERE job ='CLERK' 
    AND job = 'SALESMAN'
    )
    

    • 多行多列子查询：常用作一张表看待

  SELECT e.ename,e.sal,e.deptno
  FROM emp e,(
  SELECT AVG(sal) avg_sal,deptno
  FROM emp
  GROUP BY deptno
  ) t
  WHERE e.deptno = t.deptno
  AND e.sal>avg_sal
  

• EXISTS关键字

  • EXISTS后面跟着一个子查询，当该子查询可以查询出至少一条记录时，则EXISTS表达式成立，返回TRUE

  SELECT deptno,dname 
  FROM dept d
  WHERE EXISTS (
  SELECT *
  FROM emp e
  WHERE d.deptno=e.deptno)
  

• 子查询在FROM子句中的使用

  • 当一个子查询是多列子查询，通常将该子查询的结果看作一张表，并基于它进行二次查询

    SELECT e.ename,e.sal,e.deptno
    FROM emp e,(
    SELECT AVG(sal) avg_sal,deptno
   FROM emp
   GROUP BY deptno
   ) t
  WHERE e.deptno = t.deptno
  AND e.sal>avg_sal
  

• 子查询在SELECT中使用

  • 可以将子查询的结果当作外层，查询记录中的一个字段显示

  SELECT e.ename,e.sal,
  (SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) dept
  FROM emp e