-- 部门表
CREATE TABLE dept(
-- 外部键 自 增长
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(10),
mar VARCHAR(10)
)
-- 员工表
CREATE TABLE asaff(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(10),
jod VARCHAR(10),
did INT, -- 外键
CONSTRAINT fk_stff_dept FOREIGN KEY(did) REFERENCES dept(did) -- 外键约束
)
-- 先增数(主表)
INSERT INTO dept (dname,mar) VALUES('产品部','tom');
INSERT INTO dept (dname,mar) VALUES('开发部','jerry');
INSERT INTO dept (dname,mar) VALUES('测试部','tony');
-- （副表）
INSERT INTO asaff(sname,jod,did) VALUES('amy','销售',4);
INSERT INTO asaff(sname,jod,did) VALUES('sam','售后',4);
INSERT INTO asaff(sname,jod,did) VALUES('toy','开发',5);
INSERT INTO asaff(sname,jod,did) VALUES('sun','测试',6);
-- 修改(先修改副表)
UPDATE asaff SET did = 3 WHERE did=2
UPDATE dept SET did = 4 WHERE did= 2
-- 删除数据（先删副表）
DELETE FROM asaff WHERE did=3
DELETE FROM dept WHERE did=3
SELECT * FROM dept
SELECT * FROM asaff
-- 交叉来接 对象 先写 对象 链接 一样的id
SELECT * FROM asaff JOIN dept ON asaff.did=dept.did
