【13】连接查询

1. 笛卡尔积

① 假设 A 表中有3条记录，B表中有 4 条记录
② 那么，语句select * from A,B 会将A，B两张表的数据交叉拼接起来，形成 3 * 4 = 12 条记录的结果集，此结果集叫做笛卡尔积。

2. 使用where实现多表连接

    select count(*) from Emp;  -- 14
    select count(*) from Dept; -- 4
   
    select count(*) from Emp,Dept; -- 56 （56 = 14 * 4）
   
    select * from Emp , Dept
   	   where Emp.DEPTNO = Dept.DEPTNO

	SELECT * FROM Grade,Student
		 WHERE Grade.`Grade_Id` = Student.`Grade_Id`;

• 查询EmpNo，Ename，DeptNo，DNAME

	select empNo,ename,DeptNo,dname from Emp,Dept
		where Emp.DEPTNO = Dept.DEPTNO

		> ORA-00918: 未明确定义列 

【改进办法】明确列的所属

	select empNo,ename,Emp.DeptNo,dname from Emp,Dept
		where Emp.DEPTNO = Dept.DEPTNO

3. 内连接（inner join … on）

• 内连接中，join两边的表没有主次关系，等级是相同的

	select e.empNo,e.ename,e.deptNo,d.dname 
		from Emp e inner join Dept d on e.deptNo = d.deptNo

• 对表设置别名

	SELECT s.`Stu_No`,s.`Stu_Name`,g.`Grade_Name` FROM Student s 
		INNER JOIN Grade g ON s.`Grade_Id` = g.`Grade_Id`
			WHERE s.`Stu_Sex` = '女';
	select e.empNo,e.ename,e.DeptNo,d.dname from Emp e , Dept d
		where e.deptNo = d.deptNo

4. 外连接（left join || right join）

• 外连接中，left join 左边的表是主表，右边的表是子表；主表中的数据完全列出，用子表匹配；如果匹配成功，显示信息；如果匹配不上，显示 null。
• right join 与 left join 正好完全相反。

	select e.empNo,e.ename,e.deptNo,d.dname 
		from Emp e left join Dept d on e.deptNo = d.deptNo
	
	select e.empNo,e.ename,d.deptNo,d.dname 
		from Emp e right join Dept d on e.deptNo = d.deptNo

5. 全连接（CROSS JOIN、FULL JOIN）

    SELECT * FROM Student CROSS JOIN Grade
    SELECT * FROM Student FULL JOIN Grade

6.自连接

6.1 经理、雇员信息类的问题

1. 建表

	create table Employee
	(
	   employee_Id int not null,-- '雇员编号' 
	   employee_Name varchar(30) not null, -- '雇员姓名'
	   manager_Id int, -- '经理编号'
	   primary key(employee_Id)
	);

2. 插入数据

	insert into Employee(employee_Id,employee_Name,manager_Id) values(1,'Obama',null);
	INSERT INTO Employee(employee_Id,employee_Name,manager_Id) VALUES(2,'Lucy',1);
	INSERT INTO Employee(employee_Id,employee_Name,manager_Id) VALUES(3,'Jack',1);
	INSERT INTO Employee(employee_Id,employee_Name,manager_Id) VALUES(4,'James',2);
	INSERT INTO Employee(employee_Id,employee_Name,manager_Id) VALUES(5,'Jerry',2);
	INSERT INTO Employee(employee_Id,employee_Name,manager_Id) VALUES(6,'Andy',3);
	INSERT INTO Employee(employee_Id,employee_Name,manager_Id) VALUES(7,'Blues',4);
	INSERT INTO Employee(employee_Id,employee_Name,manager_Id) VALUES(8,'Aimi',6);
	INSERT INTO Employee(employee_Id,employee_Name,manager_Id) VALUES(9,'Tom',7);

3. 具体实现

• 查询雇员的ID，雇员的Name，经理的ID，经理的Name

    SELECT emp.`employee_Id`,emp.`employee_Name`,emp.`manager_Id`,mgr.`employee_Name`
    	FROM Employee emp LEFT JOIN Employee mgr
    		ON emp.`manager_Id` = mgr.`employee_Id`;

6.2 车票、航班显示、购买类的问题

1. 建表

	CREATE TABLE city
	(
	    cityID INT NOT NULL,
	    cityName VARCHAR(30),
	    PRIMARY KEY(cityID)
	);
	CREATE TABLE TransPort
	(
	    id INT NOT NULL,
	    fromCity INT NOT NULL,
	    toCity INT NOT NULL,
	    PRIMARY KEY(id),
	    FOREIGN KEY(fromCity) REFERENCES city(cityID),
	    FOREIGN KEY(toCity) REFERENCES city(cityID)
	);

2. 插入数据

	INSERT INTO city(cityID,cityName) VALUES (1,'北京');
	INSERT INTO city(cityID,cityName) VALUES (2,'济南');
	INSERT INTO city(cityID,cityName) VALUES (3,'上海');
	INSERT INTO city(cityID,cityName) VALUES (4,'纽约');
	INSERT INTO city(cityID,cityName) VALUES (5,'夏威夷');
		
	INSERT INTO TransPort(id,fromCity,toCity) VALUES (1,1,2);
	INSERT INTO TransPort(id,fromCity,toCity) VALUES (2,2,4);
	INSERT INTO TransPort(id,fromCity,toCity) VALUES (3,4,5);
	INSERT INTO TransPort(id,fromCity,toCity) VALUES (4,3,1);
	INSERT INTO TransPort(id,fromCity,toCity) VALUES (5,1,3);

3. 具体实现

	SELECT t.id,fc.`cityName`,tc.`cityName` FROM TransPort t
	    INNER JOIN city fc ON t.`fromCity` = fc.`cityID`
	    INNER JOIN city tc ON t.`toCity` = tc.`cityID` 
	      WHERE fc.`cityName` LIKE '%济%'