CREATE TABLE dept(
id INT PRIMARY KEY auto_increment,
dep_name VARCHAR(20),
addr VARCHAR(20)
);
CREATE TABLE emp(
id int PRIMARY KEY auto_increment,
name VARCHAR(20),
age INT,
dep_id INT,
-- 添加外键dep_id,关联dept表的id主键
CONSTRAINT FK_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp(NAME,age,dep_id) VALUES
('张三',20,1),
('李四',20,1),
('王五',20,1),
('赵六',20,1),
('孙七',22,1),
('周八',18,1);
INSERT INTO dept(dep_name,addr) VALUES
('研发部','广州'),
('销售部','深圳');
SELECT * FROM emp;
ALTER TABLE dept CHANGE dep_name dep_name VARCHAR(20) CHARACTER SET utf8;
ALTER TABLE dept CHANGE addr addr VARCHAR(20) CHARACTER SET utf8;
ALTER TABLE emp CHANGE name name VARCHAR(20) CHARACTER SET utf8;
-- 删除外键
ALTER TABLE emp DROP FOREIGN KEY FK_emp_dept;
-- 建完表后添加外键
ALTER TABLE emp ADD CONSTRAINT FK_emp_dept FOREIGN key(dep_id) REFERENCES key dept(id);
阿昆同学的Java学习日记Day3
最新推荐文章于 2024-05-09 22:32:27 发布