8870188 发表于 2018-10-9 07:05:44

补12.关于mysql的多表查询

  首先先准备两张表。
  首先是员工信息表,表名为employee。
  create table employee( emp_id int primary key auto_increment not null, emp_name varchar(50), age int, dept_id int );
  然后在员工信息表中插入6条记录:
  insert into employee(emp_name,age,dept_id)values ('A',19,200), ('B',26,201), ('C',30,201), ('D',24,202), ('E',20,200), ('F',38,204);
  #创建了6个员工。
  然后是部门信息表。
  create table department( dept_id int, dept_name varchar(100) );
  #部门信息表中包含了部门ID以及部门名称。
  insert into department values(200,'人事部'),(201,'技术部'),(202,'销售部'),(203,'财政部');
  #在部门表中添加了4个部门。
  employee表:
  +--------+----------+------+---------+
  | emp_id | emp_name | age| dept_id |
  +--------+----------+------+---------+
  |      1 | A      |   19 |   200 |
  |      2 | B      |   26 |   201 |
  |      3 | C      |   30 |   201 |
  |      4 | D      |   24 |   202 |
  |      5 | E      |   20 |   200 |
  |      6 | F      |   38 |   204 |
  +--------+----------+------+---------+
  department表:
  +---------+-----------+
  | dept_id | dept_name |
  +---------+-----------+
  |   200 | 人事部    |
  |   201 | 技术部    |
  |   202 | 销售部    |
  |   203 | 财政部    |
  +---------+-----------+
  一、笛卡尔积查询:
  select * from employee,department;
  结果:
  +--------+----------+------+---------+---------+-----------+
  | emp_id | emp_name | age| dept_id | dept_id | dept_name |
  +--------+----------+------+---------+---------+-----------+
  |      1 | A      |   19 |   200 |   200 | 人事部    |
  |      1 | A      |   19 |   200 |   201 | 技术部    |
  |      1 | A      |   19 |   200 |   202 | 销售部    |
  |      1 | A      |   19 |   200 |   203 | 财政部    |
  |      2 | B      |   26 |   201 |   200 | 人事部    |
  |      2 | B      |   26 |   201 |   201 | 技术部    |
  |      2 | B      |   26 |   201 |   202 | 销售部    |
  |      2 | B      |   26 |   201 |   203 | 财政部    |
  |      3 | C      |   30 |   201 |   200 | 人事部    |
  |      3 | C      |   30 |   201 |   201 | 技术部    |
  |      3 | C      |   30 |   201 |   202 | 销售部    |
  |      3 | C      |   30 |   201 |   203 | 财政部    |
  |      4 | D      |   24 |   202 |   200 | 人事部    |
  |      4 | D      |   24 |   202 |   201 | 技术部    |
  |      4 | D      |   24 |   202 |   202 | 销售部    |
  |      4 | D      |   24 |   202 |   203 | 财政部    |
  |      5 | E      |   20 |   200 |   200 | 人事部    |
  |      5 | E      |   20 |   200 |   201 | 技术部    |
  |      5 | E      |   20 |   200 |   202 | 销售部    |
  |      5 | E      |   20 |   200 |   203 | 财政部    |
  |      6 | F      |   38 |   204 |   200 | 人事部    |
  |      6 | F      |   38 |   204 |   201 | 技术部    |
  |      6 | F      |   38 |   204 |   202 | 销售部    |
  |      6 | F      |   38 |   204 |   203 | 财政部    |
  +--------+----------+------+---------+---------+-----------+
  显然这种结果没有任何的意义。
  二、内连接查询。
  查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
  下面是一个内连接查询的例子:
  还拿前面的两张表举例,现在需要列出所有员工的姓名,以及职位名称。
  select emp_name,dept_name from employee,department where employee.dept_id = department.dept_id;
  +----------+-----------+
  | emp_name | dept_name |
  +----------+-----------+
  | A      | 人事部    |
  | B      | 技术部    |
  | C      | 技术部    |
  | D      | 销售部    |
  | E      | 人事部    |
  +----------+-----------+
  三、外连接查询。

[*]  左外链接:在内连接的基础上增加左边有右边没有的结果。
  select * from employee left join department on employee.dept_id = department.dept_id;
  #就是以左边的表为基准
  +--------+----------+------+---------+---------+-----------+
  | emp_id | emp_name | age| dept_id | dept_id | dept_name |
  +--------+----------+------+---------+---------+-----------+
  |      1 | A      |   19 |   200 |   200 | 人事部    |
  |      5 | E      |   20 |   200 |   200 | 人事部    |
  |      2 | B      |   26 |   201 |   201 | 技术部    |
  |      3 | C      |   30 |   201 |   201 | 技术部    |
  |      4 | D      |   24 |   202 |   202 | 销售部    |
  |      6 | F      |   38 |   204 |    NULL | NULL      |
  +--------+----------+------+---------+---------+-----------+
  2.右外链接:在内连接的基础上增加右边有左边没有的结果。
  select * from employee right join department on employee.dept_id = department.dept_id;
  +--------+----------+------+---------+---------+-----------+
  | emp_id | emp_name | age| dept_id | dept_id | dept_name |
  +--------+----------+------+---------+---------+-----------+
  |      1 | A      |   19 |   200 |   200 | 人事部    |
  |      2 | B      |   26 |   201 |   201 | 技术部    |
  |      3 | C      |   30 |   201 |   201 | 技术部    |
  |      4 | D      |   24 |   202 |   202 | 销售部    |
  |      5 | E      |   20 |   200 |   200 | 人事部    |
  |   NULL | NULL   | NULL |    NULL |   203 | 财政部    |
  +--------+----------+------+---------+---------+-----------+
  3.全外链接:
  全外连接是将左外链接和右外链接做一个结合。
  select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id UNION select * from employee LEFT JOIN department on employee.dept_id = department.dept_id;
  使用union关键字将左链接和有链接的结果做个一个拼接。
  union :去除所有重复的结果。
  union all : 没有去重功能。
  4.多表复合条件查询。
  比如说现在想要查询,公司年龄大于25岁的员工都在哪个部门中。
  select distinct department.dept_name from employee,department where employee.dept_id = department.dept_id and age > 25;
  +-----------+
  | dept_name |
  +-----------+
  | 技术部    |
  +-----------+
  以公司所有员工的年龄以从小到大的方式排序。
  select employee.emp_id,employee.emp_name,employee.age,department.dept_name from employee,department where employee.dept_id = department.dept_id order by age asc;
  5.多表子查询。
  子查询,就是将一个查询的sql语句,嵌套在另一个查询的sql语句中。
  内层查询语句的查询结果,可以为外层查询语句提供查询条件。
  子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字。
  IN关键字:这个关键字大概是包含的意思。
  比如说,查询employee表,但dept_id必须在department表中出现过。
  包含在department表中dept_id字段中,才会显示。
  select * from employee where dept_id in (select dept_id from department);
  +--------+----------+------+---------+
  | emp_id | emp_name | age| dept_id |
  +--------+----------+------+---------+
  |      1 | A      |   19 |   200 |
  |      2 | B      |   26 |   201 |
  |      3 | C      |   30 |   201 |
  |      4 | D      |   24 |   202 |
  |      5 | E      |   20 |   200 |
  +--------+----------+------+---------+
  使用比较运算符:
  =、!=、>、>=、
页: [1]
查看完整版本: 补12.关于mysql的多表查询