多表查询

image.png
image.png

  1. #显示雇员名，雇员工资及所在部门的名字
  2. #雇员名，雇员工资来自 emp表
  3. #部门的名字来自 dept 表
  4. #当我们需要指定某个表的列：表.列名
  5. SELECT ename,sal,dname
  6.     FROM emp,dept
  7.     WHERE emp.deptno = dept.deptno
  1. #显示员工姓名，工资，工资级别
  2. #姓名，工资来自emp 工资级别来自salgrade
  4. SELECT ename,sal,grade
  5.     FROM emp,salgrade
  6.     WHERE sal BETWEEN losal AND hisal;

自连接

自连接是指在同一张表的连接查询

  1. #显示公司员工名字和他上级的名字
  2. #员工名字在emp表中，上级的名字也在emp中，两者通过emp中的mgr列关联
  4. SELECT worker.ename AS '职员名', boss.ename AS '上级名'
  5.     FROM emp worker, emp boss
  6.     WHERE worker.mgr = boss.empno   #职员上司的编号
  8. #自连接的特点：
  9. #        1. 把同一张表当作两张表使用
  10. #        2. 需要给表取别名(一个worker表，一个boss表)

子查询

子查询是指嵌入在其它sql语句中的select语句，也叫嵌套查询

单行子查询

指只返回一行数据的子查询语句 使用 = 接收返回值

  1. #如何显示与smith在同一部门的所有员工
  3. #1. 先查询到smith的部门号
  4. #2. 把上面的select语句当作一个子查询来使用
  6. SELECT deptno
  7.     FROM emp
  8.     WHERE ename = 'smith';
  10. SELECT * 
  11.     FROM emp
  12.     WHERE depto = (
  13.         SELECT deptno
  14.         FROM emp
  15.         WHERE ename = 'smith'
  16.     )

多行子查询

返回多行数据的子查询，使用关键字 in 接收返回值

  1. #查询和部门10工作内容相同的员工的 名字、岗位、工资、部门号
  2. #但是不包括部门10的员工
  4. # 1. 查询到10号部门有哪些工作 （不能重复，因此用distinct）
  5. # 2. 把上面查询的结果当作子查询使用
  7. SELECT DISTINCT job
  8.     FROM emp
  9.     WHERE deptno = 10;
  11. SELECT ename,job,sal,deptno
  12.     FROM emp
  13.     WHERE job IN (
  14.         SELECT DISTINCT job
  15.         FROM emp
  16.         WHERE deptno = 10
  17.     ) AND deptno != 10  #部门不为10

子查询临时表

可以将子查询当作一张临时表使用

  1. #查询ecshop中各个类别，价格最高的商品
  3. #先得到各个类别中，价格最高的商品 (max, group by cat_id) 当作临时表
  5. SELECT cat_id,MAX(shop_price)
  6.     FROM ecshop
  7.     GROUP BY cat_id
  8. #获得编号+对应的最高价格    
  10. SELECT goods_id,ecshop.cat_id,goods_name,shop_price #注意要声明哪个表中的cat_id
  11.     FROM (
  12.         SELECT cat_id,MAX(shop_price)
  13.         FROM ecshop
  14.         GROUP BY cat_id
  15.     ) temp , ecshop   #temp是临时表的名字，从临时表和总表中查找
  16.     WHERE temp.cat_id = ecshop.cat_id
  17.     AND temp.max_pricr = ecshop.shop_price
  18. #满足编号相同，价格是最大的，输出信息

all 和 any

  1. #显示工资比部门30 的所有员工的工资高 的员工的姓名、工资和部门号
  3. SELECT ename,sal,deptno
  4.     FROM emp
  5.     WHERE sal > ALL(
  6.         SELECT sal
  7.         FROM EMP
  8.         WHERE deptno = 30
  9.     ) #嵌套查询 ，查出所有部门30员工的工资
  11. #注：也可以把ALL 改成 MAX
  1. #显示工资比部门30 的其中一个工资高 的员工的姓名、工资和部门号
  3. SELECT ename,sal,deptno
  4.     FROM emp
  5.     WHERE sal > ANY(
  6.         SELECT sal
  7.         FROM EMP
  8.         WHERE deptno = 30
  9.     ) #嵌套查询 ，查出所有部门30员工的工资
  11. #注：也可以把ANY 改成 MIN

基本上ALL等价于MAX，ANY等价于MIN。

多列子查询

WHERE (字段1,字段2…) = (子查询语句)

  1. #多列子查询返回多个列数据
  3. #查询与smith部门和岗位完全相同的所有雇员（不包括smith本人）
  4. SELECT deptno , job
  5.     FROM emp
  6.     WHERE ename = 'smith'  #得到smith的岗位和工作
  8. #把上面的查询当作子查询来使用，并且使用多列子查询的语法来匹配
  9. SELECT * 
  10.     FROM emp
  11.     WHERE (deptno,job) = (
  12.         SELECT deptno , job
  13.         FROM emp
  14.         WHERE ename = 'smith' 
  15.     ) AND ename != 'smith'

一些练习

  1. #查找每个部门工资高于本部门平均工资的人的资料
  3. #1.先得到每个部门的部门号和对应的平均工资
  4. SELECT deptno,AVG(sal) AS avg_sal
  5.     FROM emp
  6.     GROUP BY deptno  #用部门号进行分组
  8. #2. 把上面的结果当作子查询，和emp进行多表查询
  10. SELECT ename,sal,temp.avg_sal,emp.deptno
  11.     FROM emp,(
  12.         SELECT deptno,AVG(sal) AS avg_sal
  13.         FROM emp
  14.         GROUP BY deptno
  15.     )temp
  16.     WHERE emp.deptno = temp.deptno AND emp.sal > temp.avg_sal
  1. #查询每个部门的信息和人员数量
  3. #部门信息在dept表（部门名，编号，地址）
  4. #各个部门的人员数量 -> 构建一个临时表
  6. SELECT COUNT(*),deptno
  7.     FROM emp
  8.     GROUP BY deptno
  10. SELECT dname,dept.deptno,loc,tmp.per_num AS '人数' 
  11.     #也可以把 deptno和per_num 合并写成 tmp.*
  12.     FROM dept, (
  13.         SELECT COUNT(*),deptno
  14.         FROM emp
  15.         GROUP BY deptno
  16.     ) tmp
  17.     WHERE dept.deptno = tmp.deptno

注：在多表查询中，当多个表的列名不重复时，才可以直接写列名（不然会引起误解）。

合并查询

  1. SELECT expression1, expression2, ... expression_n FROM tables
  2. [WHERE conditions]
  3. UNION [ALL | DISTINCT]
  4. SELECT expression1, expression2, ... expression_n FROM tables
  5. [WHERE conditions];

image.png

  1. SELECT ename,sal,job FROM emp WHERE sal >2500
  2. UNION ALL  #查询结果合并，不会去重   DISTINCT会去重
  3. SELECT ename,sal,job FROM emp WHERE job = 'MANAGER'
  5. #合并两条 SELECT语句的结果

外连接

列出部门名称和这些部门的员工名称和工作，同时要求 显示出那些没有员工的部门，这个无法用多表查询得到（因为无法关联起来）
左外连接 left join 左侧的表完全显示。 右外连接 right join 右侧的表完全显示。

  1. #使用左外连接实现
  2. SELECT dname,ename,job
  3.     FROM dept LEFT JOIN emp
  4.     ON dept.deptno = emp.deptno
  6. #使用右外连接实现
  7. SELECT dname,ename,job
  8.     FROM emp RIGHT JOIN dept
  9.     ON dept.deptno = emp.deptno

自增长

添加后，该字段从1开始自动增长。
image.png

  1. CREATE TABLE t24(
  2.     id INT PRIMARY KEY AUTO_INCREMENT);
  1. #修改默认的自增长开始值
  2. ALTER TABLE t24 AUTO_INCREMENT = 100

image.png
比如加入了500这个数值，那么下一次就从501开始。