MYSQL 教程: 高级查询

查找出员工及其部门名

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';

§7.2 理解不同的连接类型

＊基本连接类型

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

如下查询:

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值，所以不会为空。


§7.3 书写子查询

子查询又称为嵌套查询,是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 的语法请参考教材。