多表关联查询基础题

一、创建表的素材

create table dept (
?? ??? ?id int primary key auto_increment,
?? ??? ?name varchar(50) unique not null,
?? ??? ?intro text
);

insert into dept(name,intro) values("教学部","教授知识的部门");
insert into dept(name,intro) values("教研部","研究知识的部门");
insert into dept(name,intro) values("财务部","管钱的部门");
insert into dept(name,intro) values("校园部","管理学校的部门");
insert into dept(name,intro) values("市场部","推广部门");

create table emp (
?? ??? ?id int primary key auto_increment comment "用户编号",
?? ??? ?name varchar(50) not null comment "用户名称",
?? ??? ?age int default 18 comment "用户年龄",
?? ??? ?tel char(11) unique comment "用户电话",
?? ??? ?email varchar(255) unique comment "用户邮箱",
?? ??? ?join_time datetime comment "用户加入时间",
?? ??? ?salary float comment "用户工资",
?? ??? ?dept_id int comment "用户部门"
);

insert into emp(name,tel,email,join_time,salary,dept_id) values("zhangsan","15516708880","15516708880@qq.com",'2020-3-4 8:20:35',5000,1);
insert into emp(name,tel,email,join_time,salary,dept_id) values("lisi","15288764455","15288764455@qq.com",'2020-12-4 12:12:12',10000,2);
insert into emp(name,tel,email,join_time,salary,dept_id) values("wangwu","17101235678","17101235678@qq.com",'2021-10-14 18:12:15',12000,4);
insert into emp(name,tel,email,join_time,salary,dept_id) values("xiaohong","13249874755","13249874755@qq.com",'2023-5-4 8:20:35',15000,5);
insert into emp(name,tel,email,join_time,salary,dept_id) values("xiaoming","15019874455","15019874455@qq.com",'2019-2-14 10:20:45',8000,3);
insert into emp(name,tel,email,join_time,salary,dept_id) values("xiaobai","17133997896","17133997896@qq.com",'2018-1-4 12:20:35',7000,5);
insert into emp(name,tel,email,join_time,salary,dept_id) values("xiaoma","17719873455","17719873455@qq.com",'2024-1-4 15:20:35',6000,5);
insert into emp(name,tel,email,join_time,salary,dept_id) values("xiaoliu","13100998888","13100998888@qq.com",'2024-1-13 10:30:55',10000,null);

二、实验步骤

2.1 创建表并插入数据

2.2 交叉连接（不建议使用）

①语法

交叉连接（cross join）：
?? ??? ?select *
?? ??? ?from emp,dept;

SQL99标准：
?? ?select *
?? ?from emp cross join dept;

②结果

2.3?内连接

①语法

内连接（inner join）：
?? ?select *?
?? ?from emp,dept where emp.dept_id=dept.id;

SQL99标准：
?? ?select *?
?? ?from emp inner join dept on (emp.dept_id=dept.id);

?②结果

注意：由于内连接要求匹配的行必须都存在，因此当出现NULL外键值时，它不会将该行与另一张表进行连接。

2.4 外连接?

①语法

SQL99标准：
外连接（outer join）：
?? ?左外连接（left join）：将left join左侧的表中所有数据展示
?? ??? ??? ?select e.*,d.name from emp e left join dept d on (e.dept_id=d.id);
?? ?右外连接（right join）：将right join右侧的表中所有数据展示
?? ??? ??? ?select e.*,d.name from emp e right join dept d on (e.dept_id=d.id);
?? ?全外连接（full join）：MySQL目前不支持

②结果?

2.5 自连接

自连接（self join）：外键指向自身。

2.6 自然连接

自然连接（nature join）：通过表的字段，自动关联外键，不建议使用！！！它会默认将两张表中相同的字段认为就是外键，对于表的设计难度太大。