数据库——连接查询

创建副表
CREATE TABLE emp01(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(10),
esex CHAR(5),
did INT,
CONSTRAINT fk_emp01_dept01 FOREIGN KEY(did) REFERENCES dept01(did) -- 外键约束
)
新增数据
INSERT INTO dept01(dname,mgr) VALUES('开发部','tom');
INSERT INTO dept01(dname,mgr) VALUES('测试部','jerry');

INSERT INTO emp01(ename,esex,did) VALUES('小红帽','女',3);
INSERT INTO emp01(ename,esex,did) VALUES('大灰狼','男',3);
INSERT INTO emp01(ename,esex,did) VALUES('海绵宝宝','男',4);
SELECT * FROM dept01
SELECT * FROM emp01
修改数据(先改副表，再改主表)
UPDATE emp01 SET did=2 WHERE did=1
UPDATE dept01 SET did=3 WHERE did=1
删除数据(先删副表，再删主表)
DELETE FROM emp01 WHERE did=2
DELETE FROM dept01 WHERE did=2
DELETE FROM dept01
DELETE FROM emp01

交叉连接
SELECT * FROM emp01 JOIN dept01
内连接
SELECT * FROM emp01 JOIN dept01 ON emp01.did=dept01.did
查询大灰狼的部门经理
SELECT mgr FROM emp01 JOIN dept01 ON emp01.did=dept01.did
WHERE ename='大灰狼'
查询开发部所有员工的信息
SELECT * FROM emp01 JOIN dept01 ON emp01.did=dept01.did WHERE dname='开发部'
查询每个部门的人数
SELECT dname,COUNT(*) FROM emp01 JOIN dept01 ON emp01.did=dept01.did GROUP BY dname
多表
FROM 表1 JOIN 表2 ON 表1.字段=表2.字段 JOIN 表3 ON 表2.字段=表3.字段
左外连接
INSERT INTO dept01(dname,mgr) VALUES('产品部','tony')
查询所有用户信息及其订单信息
SELECT * FROM dept01 LEFT JOIN emp01 ON dept01.did=emp01.did
右外连接
SELECT * FROM emp01 RIGHT JOIN dept01 ON dept01.did=emp01.did
子查询 把查询的结果当作另一个的查询条件
查询开发部所有员工信息
SELECT * FROM emp01 WHERE did=(SELECT did FROM dept01 WHERE dname='开发部')
查询所有女员工所属部门名称
SELECT dname FROM dept01 WHERE did=(SELECT did FROM emp01 WHERE esex='女')
查询男员工所属部门经理
SELECT mgr FROM dept01 WHERE did IN(SELECT did FROM emp01 WHERE esex='男')
SELECT did,dname FROM dept01 UNION SELECT eid,ename FROM emp01