mysql数据库：多表查询

mysql数据库：多表查询

• 准备sql

  -- 创建部门表
  CREATE TABLE dept(
     id INT PRIMARY KEY auto_increment,
     NAME VARCHAR(20)
  );
  -- 插入数据
  INSERT INTO dept(NAME) VALUES ('开发部'),('市场部'),('财务部'); 
  
  
  -- 创建员工表
  CREATE TABLE emp(
     id INT PRIMARY KEY auto_increment,
     NAME VARCHAR(10),
     gender CHAR(1),  -- 性别
     salary DOUBLE,  -- 工资
     join_date DATE, -- 入职日期
     dept_id INT,
     FOREIGN KEY(dept_id) REFERENCES dept(id)  -- 外键，关联部门表的主键
  );
  -- 插入数据
  INSERT INTO emp(NAME,gender,salary,join_date,dept_id) 
  VALUES 	('孙悟空','男',7200,'2013-02-24',1),
   		('猪八戒','男',3600,'2010-12-02',2),
   		('唐僧','男',9000,'2008-08-08',2),
   		('白骨精','女',5000,'2015-10-07',3),
   		('蜘蛛精','女',4500,'2011-03-14',1);
  

• 笛卡尔积

  有两个集合A,B 取这两个集合的所有组成情况。
  
  例如：
  A：(a,b,c)
  B：(1,2,3)
  A与B作笛卡尔积---> a,1 a,2 a,3 b,1 b,2 b,3 c,1 c,2 c,3
  

• 多表查询，消除无用的数据

  • 内连接查询

    • 隐式内连接：使用where条件消除无用数据

      --  查询所有员工信息和对应的部门信息
      SELECT * FROM emp,dept WHERE emp.dept_id=dept.id;
      
      -- 查询员工表的名称，性别。部门表的名称
      SELECT 	emp.`NAME`
      		,emp.gender
      		,dept.`NAME` 
      FROM emp,dept WHERE emp.dept_id=dept.id;
      
      -- 或者
      SELECT	t1.`NAME`
      		,t1.gender
      		,t2.`NAME`
      FROM emp t1,dept t2
      WHERE t1.dept_id=t2.id;
      

    • 显式内连接

      --语法：select 字段列表 from 表名1 [inner] join 表名2 on 条件
      
      SELECT * FROM emp INNER JOIN dept ON emp.dept_id=dept.id;
      

  • 外连接查询

    • 左外连接

      --语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
      
      --查询的是左表所有数据以及其交集部分。 
      

    • 右外连接

      --语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
      
      --查询的是右表所有数据以及其交集部分。
      

  • 子查询

    • 查询中嵌套查询，称嵌套查询为子查询

      -- 查询工资最高的员工信息
      
      -- 1.查询最高的工资是多少 9000
      SELECT MAX(salary) FROM emp;
       
      -- 2.查询员工信息，并且工资等于9000的
      SELECT * FROM emp WHERE emp.salary=9000;
      
      -- 一条sql就完成这个操作
      SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);
      

    • 子查询的结果是单行单列的

      --子查询可以作为条件，使用运算符去判断。  运算符：> >= < <= =
      
      --查询员工工资小于平均工资的人
      SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
      

    • 子查询的结果是多行单列的

      --子查询可以作为集合，使用in、not int
      
      --查询财务部和市场部所有员工信息
      SELECT id FROM dept WHERE `NAME`='财务部' OR `NAME`='市场部';
      SELECT * FROM emp WHERE dept_id=3 OR dept_id=2;
      
      --使用子查询
      SELECT * FROM emp WHERE dept_id in (SELECT id FROM dept WHERE `NAME`='财务部' OR `NAME`='市场部');
      

    • 子查询的结果是多行多列的

      --子查询可以作为一张虚拟表参与查询
      --查询员工入职日期是2011-11-11日之后的员工信息和部门信息
      
      -- 子查询
      select * from dept t1 (select * from emp where emp.join_date > '2011-11-11') t2 where t1.id = t2.dept_id;
      
      --普通内连接查询
      select * from emp t1,dept t2 where t1.dept_id = t2.id and t1.join_date > '2011-11-11'  
      

  • 表的拼接

    拼接时两张表的结构必须完全一致
    
    * union 对数据进行去重
    * union all