数据库学习Day7

表与表之间的关系

多表查询

1. 合并结果集

#去重
SELECT * FROM employee_china UNION SELECT * FROM employee_usa;

#不去重
SELECT * FROM employee_china UNION ALL SELECT * FROM employee_usa;

2. 连接查询

#笛卡尔积
SELECT * FROM employee_china,employee_usa;
#笛卡尔积过滤
CREATE TABLE department(
    id INT,
    dname VARCHAR(50)
);

INSERT INTO department VALUE(10001,'销售部');
INSERT INTO department VALUE(10002,'咨询部');
INSERT INTO department VALUE(10003,'人事部');
INSERT INTO department VALUE(10004,'技术部');

CREATE TABLE employee(
   id INT,
   ename VARCHAR(50),
   depno INT
);

INSERT INTO employee VALUES(1,'tony',10001);
INSERT INTO employee VALUES(2,'lucy',10001);
INSERT INTO employee VALUES(3,'mia',10001);
INSERT INTO employee VALUES(4,'amy',10002);
INSERT INTO employee VALUES(5,'jerry',10002);
INSERT INTO employee VALUES(6,'micheal',10003);
INSERT INTO employee VALUES(7,'lily',10003);
INSERT INTO employee VALUES(8,'elain',10004);
INSERT INTO employee VALUES(9,'ruly',10004);
INSERT INTO employee VALUES(10,'kk',10004);
INSERT INTO employee VALUES(11,'cici',10004);

SELECT e.id '员工编号',e.ename '员工姓名',d.dname '部门名称',d.id '部门编号' 
FROM department d,employee e 
WHERE e.depno = d.id;

标准内连接

SELECT e.id '员工编号',e.ename '员工姓名',d.dname '部门名称',d.id '部门编号' 
FROM department d INNER JOIN employee e ON e.depno = d.id;

外连接

INSERT INTO department VALUE(10005,'保洁部');
INSERT INTO employee (id,ename) VALUES(12,'ela');

左(外)连接 先查询左表(employee),右表(departmen)满足条件的显示数据不满足的显示NULL
SELECT e.id '员工编号',e.ename '员工姓名',d.dname '部门名称',d.id '部门编号' 
FROM employee e LEFT JOIN department d ON e.depno = d.id;
#等价写法
SELECT e.id '员工编号',e.ename '员工姓名',d.dname '部门名称',d.id '部门编号' 
FROM employee e LEFT OUTER JOIN department d ON e.depno = d.id;


右(外)连接 先查询右表(departmen),左表(employee)满足条件的显示数据不满足的显示NULL
SELECT e.id '员工编号',e.ename '员工姓名',d.dname '部门名称',d.id '部门编号' 
FROM employee e RIGHT JOIN department d ON e.depno = d.id;
#等价写法
SELECT e.id '员工编号',e.ename '员工姓名',d.dname '部门名称',d.id '部门编号' 
FROM employee e RIGHT OUTER JOIN department d ON e.depno = d.id;

自连接

#需求：求7369员工编号、姓名、经理编号、经理姓名
SELECT e1.empno '员工编号',e1.ename '员工姓名',e2.mgr '经理编号',e2.ename '经理姓名' 
FROM emp e1,emp e2
WHERE e1.empno=7369 AND e1.mgr = e2.empno;

自然连接

#需要两张连接的表中名称和类型完全一致的列作为条件
#写法一:
SELECT * FROM t_department NATURAL JOIN t_employee;
#写法二：
SELECT * FROM t_department INNER JOIN t_employee USING(depno);
#自然左连接
SELECT * FROM t_department NATURAL LEFT JOIN t_employee;
#自然右连接
SELECT * FROM t_department NATURAL RIGHT JOIN t_employee;