创建部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
)

insert into dept (name) values (‘开发部’),(‘市场部’),(‘财务部’); # 创建员工表

create table emp (
id int primary key auto_increment,
name varchar(10),
gender char(1), — 性别
salary double, — 工资
join_date date, — 入职日期
dept_id int,
foreign key (dept_id) references dept(id) — 外键，关联部门表(部门表的主键)
)

insert into emp(name,gender,salary,join_date,dept_id) values(‘孙悟空’,’男’,7200,’2013-02-24’,1);
insert into emp(name,gender,salary,join_date,dept_id) values(‘猪八戒’,’男’,3600,’2010-12-02’,2);
insert into emp(name,gender,salary,join_date,dept_id) values(‘唐僧’,’男’,9000,’2008-08-08’,2);
insert into emp(name,gender,salary,join_date,dept_id) values(‘白骨精’,’女’,5000,’2015-10-07’,3);
insert into emp(name,gender,salary,join_date,dept_id) values(‘蜘蛛精’,’女’,4500,’2011-03-14’,1);

什么是笛卡尔积：
如下图所示,不加条件的多表查询会产生直积现象,查询结果会有3*5=15条数据.

— 需求：查询所有的员工和所有的部门
select * from emp,dept; # 这样查询是错误的

— 设置过滤条件 Column ‘id’ in where clause is ambiguous
select * from emp,dept where id=5;

— 查询员工和部门的名字
select emp.name, dept.name from emp,dept where emp.dept_id = dept.id;

— 隐式内连接查询
select * from emp,dept where emp.dept_id = dept.id;

— 显示内连接查询 , 也是会产生笛卡尔积现象 ,所以也要找他们他们的等式关系
select * from emp inner join dept;

—————————————————————————————————————-
select from emp inner join dept on emp.dept_id = dept.id where emp.name = ‘孙悟空’;
# 上下两句代码形式上有些区别,但是在结果上没有区别
select from emp,dept where emp.dept_id = dept.id and emp.name= ‘孙悟空’;