php员工管理系统中数据库连表查询的实现

一、案例描述

1、 考核知识点

SQL的连表查询

2、 练习目标

Ø 能够熟练使用连表查询从多个表中获取组合数据

3、 需求分析

在实际项目中，员工的详细数据保存在员工表中，部门的详细数据保存在部门表中。如果需要在 查询员工信息时，一并显示其所属的部门信息，就需要同时获取员工表和部门表两张表的数据，这样 的查询在MySQL中就被称为连表查询。下面在教材【案例15】的基础上，演示连表查询的实现。

4、 设计思路

1） 创建部门信息表，该表用来保存部门的相关信息。

2） 修改用户表，删除部门名称字段，添加部门ID字段。

3） 修改showList.php文件，重新组合SQL语句。

4） 修改list_html.php文件，主要修改表格中输出的字段信息，以符合查询到的数据。

二、案例实现

1、创建部门信息表，SQL语句如下：

create table `emp_dept` (

  `d_id` int unsigned primary key auto_increment,

  `d_name` varchar(20) not null

)charset=utf8;

在上述SQL语句中，d_id表示部门编号，将其声明为无符号的int类型，并作为该表的主键且为自增形式。d_name表示部门名称，将其声明为varchar类型。

接下来向该表中插入数据，用来做查询测试，插入的SQL语句如下：

INSERT INTO `emp_dept` VALUES

 (1, '开发部'), (2, '媒体部'), (3, '人事部'),(4, '后勤部'),

 (5, '市场部'),  (6, '运维部'), (7, '销售部');

2、修改用户表

删除用户表中的部门名称字段，再添加部门ID字段，SQL语句如下：

drop table if exists `emp_info`;

create table `emp_info` (

  `e_id` int unsigned primary key auto_increment,

  `e_name` varchar(20) not null,

  `d_id` int unsigned not null,

  `date_of_birth` timestamp not null,

  `date_of_entry` timestamp not null

)charset=utf8;

完成用户表修改后，向其中添加用户数据，此时部门名称字段被部门ID字段代替，因此员工添加的SQL语句如下：

insert into `emp_info` values

 (1, '小红', 1, '2015-4-9 17:51:00', '2015-4-9 17:52:00'),

 (2, '李四', 5, '2008-4-3 13:33:00', '2013-10-24 17:53:00'),

 (3, '王五', 4, '2008-4-3 13:33:00', '2015-4-21 13:33:00'),

 (4, '赵六', 4, '2008-4-3 13:33:00', '2015-3-20 17:54:00'),

 (5, '小兰', 2, '1989-5-4 17:33:00', '2012-6-18 17:54:00'),

 (6, '小新', 5, '1993-9-18 17:36:00', '2015-2-28 17:36:00'),

 (7, '小白', 2, '1991-10-17 17:37:00', '2014-8-16 17:37:00'),

 (8, '小智', 7, '1987-6-20 17:37:00', '2015-1-10 17:38:00'),

 (9, '大头', 6, '1991-2-14 08:49:00', '2014-7-12 08:49:00'),

 (10, '小明', 3, '1991-2-14 08:49:00', '2015-3-4 09:10:00'),

 (11, '小刘', 1, '1992-3-18 14:52:00', '2014-7-21 09:00:00');

3、重新组合SQL语句

由于部门信息从员工表中抽取了出来，存放到了部门表中。因此想要获取员工数据的同时获取部门名称，就需要使用连接查询。下面修改showList.php中的SQL语句，具体代码如下：

……

//准备SQL语句

$sql = 'select emp.e_id,emp.e_name,emp.date_of_birth,emp.date_of_entry,dept.d_name

from emp_info as emp

left join emp_dept as dept

on emp.d_id = dept.d_id';

……

在该SQL语句中，为emp_info表定义别名emp，为emp_dept表定义别名dept，通过别名来标识要获取的字段。由于获取的是员工信息以及员工所属的部门名称，因此该查询以emp_info表为主，而emp_info表又在emp_dept之前，因此使用left join进行左连接查询。最后需要使用on关键字标识出两个表的关联条件。

为了让大家直观的感受到连表查询，下面将该SQL语句查询获取到的数据进行打印，通过观察获取的数据来体会连表查询的作用，查询结果下图所示。

从上图显示的两组数据可以看出，本不属于用户表数据的d_name（部门名称）也被获取到了，这就是连表查询下的结果。

4、修改视图文件

由于表结构的变化，获取到的数据字段名也随之发生了变化。因此需要修改视图文件，按照查询到的字段来显示表单信息，将原本的$row['e_dept']修改为$row['d_name']即可。

三、案例总结

1、最好为参与连表查询的各表设置不同的别名，以便区分各表字段。

2、连表操作需要明确以那张表为主，以便选择left join或right join，这关系到最终获取的数据形式。

3、连表操作需要明确两张表有关联的字段。