5.1 表结构:
mysql> desc employee;
+--------------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+----------------------+------+-----+---------+----------------+
| emp_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| fname | varchar(20) | NO | | NULL | |
| lname | varchar(20) | NO | | NULL | |
| start_date | date | NO | | NULL | |
| end_date | date | YES | | NULL | |
| superior_emp_id | smallint(5) unsigned | YES | MUL | NULL | |
| dept_id | smallint(5) unsigned | YES | MUL | NULL | |
| title | varchar(20) | YES | | NULL | |
| assigned_branch_id | smallint(5) unsigned | YES | MUL | NULL | |
+--------------------+----------------------+------+-----+---------+----------------+
9 rows in set
mysql> desc department;
+---------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+----------------+
| dept_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
+---------+----------------------+------+-----+---------+----------------+
2 rows in set
5.2 查找所有主管,位于另一个部门的雇员,需要获取的ID,姓氏,和名字。
我犯了一个错误,对主管的理解不清楚,
第2点:没有非等值连接的想法
本质上就是上述的2个的组合。
select id,name
from emp join dept
using( dept_id)
where d.name in (
select name
from emp,emp
)
正确的答案为:(不等值连接和自连接)
mysql> select e.emp_id,e.fname,e.lname
-> FROM employee e inner join employee mgr
-> on e.superior_emp_id =mgr.emp_id
-> WHERE e.dept_id !=mgr.dept_id
-> ;
+--------+-------+-----------+
| emp_id | fname | lname |
+--------+-------+-----------+
| 4 | Susan | Hawthorne |
| 5 | John | Gooding |
+--------+-------+-----------+
2 rows in set
5.4 不等值连接: