MYSQL 教程:§7, 高级查询

高级查询

使用连接查询多个表

 

 

查找出员工及其部门名

mysql> select employee.name as employeeName, department.name as departmentName from employee, department where employee.departmentID = department.departmentID;

+---------------+--------------------------+

| employeeName  | departmentName           |

+---------------+--------------------------+

| Nora Edwards  | Research and Development | 

| Ben Smith     | Finance                  | 

| Ajay Patel    | Research and Development | 

| Candy Burnett | Research and Development | 

+---------------+--------------------------+

4 rows in set (0.02 sec)

实际查询的过程如下:

首先找出所有连接:

+---------------+--------------------------+

| name          | name                     |

+---------------+--------------------------+

| Ajay Patel    | Finance                  |

| Nora Edwards  | Finance                  |

| Candy Burnett | Finance                  |

| Ben Smith     | Finance                  |

| Ajay Patel    | Research and Development |

| Nora Edwards  | Research and Development |

| Candy Burnett | Research and Development |

| Ben Smith     | Research and Development |

| Ajay Patel    | Human Resources          |

| Nora Edwards  | Human Resources          |

| Candy Burnett | Human Resources          |

| Ben Smith     | Human Resources          |

| Ajay Patel    | Marketing                |

| Nora Edwards  | Marketing                |

| Candy Burnett | Marketing                |

| Ben Smith     | Marketing                |

+---------------+--------------------------+

               这个查询叫做Cartesian product,笛卡尔集

        两个列名是一样的,为了改进可读性,可以修改如下:

select employee.name as employeeName, department.name as departmentName

from employee, department

where employee.departmentID = department.departmentID;

多个表之间的查询:n个表需要n-1个表达式.

比如查询哪个部门有和Telco Inc相关的业务.

mysql> select department.name from client, assignment, employee, department where client.name='Telco Inc' and client.clientID = assignment.clientID and assignment.employeeID = employee.employeeID and employee.departmentID = department.departmentID;

+--------------------------+

| name                     |

+--------------------------+

| Research and Development | 

+--------------------------+

1 row in set (0.00 sec)

 

 

自连接

查找和'Nora Edwards'在部门工作的所有员工:

mysql> select e2.name from employee e1, employee e2 where e1.name = 'Nora Edwards' and e1.departmentID = e2.departmentID;

+---------------+

| name          |

+---------------+

| Ajay Patel    |

| Nora Edwards  |

| Candy Burnett |

+---------------+

3 rows in set (0.00 sec)

 

排除掉'Nora Edwards'的查询方法

select e2.name

from employee e1, employee e2

where e1.name = 'Nora Edwards'

and e1.departmentID = e2.departmentID

and e2.name != 'Nora Edwards';

理解不同的连接类型

＊基本连接类型

笛卡尔连接又成为全连接,交叉连接,如果添加了条件则是对等连接.

如下查询:

select employee.name, department.name

from employee, department

where employee.departmentID = department.departmentID;

 

可以用

select employee.name, department.name

from employee join department

where employee.departmentID = department.departmentID;

替代. 也可以用CROSS JOIN or INNER JOIN 代替上面的join

 

＊左连接和右连接

 

左连接,右连接对应于左右边为空的部分,如下查出没有接任务的雇员.

 

mysql> select * from employee;

+------------+---------------+-----------------------+--------------+

| employeeID | name          | job                   | departmentID |

+------------+---------------+-----------------------+--------------+

|       6651 | Ajay Patel    | Programmer            |          128 |

|       7513 | Nora Edwards  | Programmer            |          128 |

|       9006 | Candy Burnett | Systems Administrator |          128 |

|       9842 | Ben Smith     | DBA                   |           42 |

+------------+---------------+-----------------------+--------------+

4 rows in set (0.00 sec)

 

mysql> select * from assignment;

+----------+------------+------------+-------+

| clientID | employeeID | workdate   | hours |

+----------+------------+------------+-------+

|        1 |       7513 | 2003-01-20 |   8.5 |

+----------+------------+------------+-------+

 

mysql> select employee.name from employee left join assignment on employee.employeeID = assignment.employeeID where clientID is null;

+---------------+

| name          |

+---------------+

| Ben Smith     |

| Ajay Patel    |

| Candy Burnett |

+---------------+

3 rows in set (0.00 sec)

 

实际的运行过程是使用右边的来匹配左边的.如果左边和右边的有对应,则为相应的值,否则为null.因为clientID为key值，所以不会为空。

 

书写子查询

子查询又称为嵌套查询,是MySQL 4.1新增的功能, 它并没有增加新功能,但是使用子查询比使用连接更有可读性.多表的删除和更新其实也是一种特殊的子查询.本节主要介绍select语句中的子查询.

MySQL有两种子查询:

导出表子查询和表达子式查询.前者的子查询返回一个表.后者出现于select语句中的where子句,分为两种类型.1,返回单个值或行.2,返回布尔值.

 

＊导出表子查询

导出表子查询允许在from子句中嵌入其他查询.可以快速插入临时表,比如:

 

mysql> select employeeID, name from employee where job='Programmer';

+------------+--------------+

| employeeID | name         |

+------------+--------------+

|       6651 | Ajay Patel   |

|       7513 | Nora Edwards |

+------------+--------------+

 

 

＊     单值子查询

出现在where子句中:

mysql> select e.employeeID, e.name from employee e, assignment a where e.employeeID = a.employeeID and a.hours = (select max(hours) from assignment);

+------------+--------------+

| employeeID | name         |

+------------+--------------+

|       7513 | Nora Edwards |

+------------+--------------+

1 row in set (0.00 sec)

当然也可以返回一行，但是用处不大，不再讲述．

 

＊     布尔子查询

 

布尔子查询一般是针对特殊功能,比如: IN, EXISTS, ALL, ANY, and SOME.

以下查询没有活干的员工.

mysql> select name from employee where employeeID not in       (select employeeID        from assignment);

+---------------+

| name          |

+---------------+

| Ben Smith     |

| Ajay Patel    |

| Candy Burnett |

+---------------+

3 rows in set (0.00 sec)

 

       EXISTS在子查询种使用了主查询的数据，又成为关联查询。如下查询没有任务的员工。

mysql> select e.name, e.employeeID from employee e where not exists           (select *            from assignment            where employeeID = e.employeeID);

+---------------+------------+

| name          | employeeID |

+---------------+------------+

| Ben Smith     |       9842 |

| Ajay Patel    |       6651 |

| Candy Burnett |       9006 |

+---------------+------------+

3 rows in set (0.00 sec)

 

       查询过程： 针对employee中的每一行，在子查询中查看是否都不匹配。

ALL, ANY, and SOME

select e.name

from employee e, assignment a

where e.employeeID = a.employeeID

and a.hours > all

         (select a.hours

         from assignment a, employee e

         where e.employeeID = a.employeeID

         and e.job='Programmer');

这部分还有待理解。

SELECT 的语法请参考教材。