MYSQL 教程:§7, 高级查询
时间：2008-02-19 来源：oychw
§7, 高级查询
§7.1 使用连接查询多个表
查找出员工及其部门名
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 的语法请参考教材。
相关阅读 更多 +
