阿昆同学的Java学习日记Day3

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);