11、多表查询

1、数据

-- 创建部门表(id,dep_name,dep_location)
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 添加2个部门 
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
-- 创建员工表(id,name,age,dep_id) 
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT
);
-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('vue', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('react', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('angular', 18, 2);
-- 添加外键
-- 格式  alter table 表名 add constraint 外键名 foreign key(本表外键列名) references主表名(主键列名)
alter table employee add constraint fk_01 foreign key(dep_id) references department(id);

通过外键关联上面的两种表。

navicat里面查询结构

2、navicat设置主键

3、多表查询

SELECT * FROM employee e,department d WHERE e.dep_id = d.id;