mysql基础知识理解和sql题讲解分析面试实战(一)之join
- join 的作用
- left join ,right join ,innerjoin 的区别
实例讲解
有个人员表(tab_person),项目表(tab_item) ,关联表(tab_person_item)
question:查询人员中选了项目表中的项目为oa的人员的id,和姓名
: step 1 : 创建主表和子表
--关联表的学习,创建主表tab_person,再创建项目表tab_item CREATE table tab_person( person_id int(5), person_name varchar(10), person_depart_id int(3), person_del_flag char(1), PRIMARY key(person_id) )
create table tab_item( item_id int(5), item_name varchar(20), item_del_flag char(1), PRIMARY key(item_id) )
--创建子表 -注意关联表的创建
CREATE table tab_person_item( person_id int(5), item_id int(5), FOREIGN key(person_id) REFERENCES tab_person(person_id) on DELETE CASCADE, FOREIGN key(item_id) REFERENCES tab_item(item_id) on DELETE CASCADE )
step 2: 插入数据
--插入主表数据
>insert into tab_person VALUES(1,'zhang3',1,'A'),(2,'zhang4',2,'A'),(3,'zhang5',1,'B'),(4,'zhang6',2,'B');
insert into tab_item values(1,'oa','d'),(2,'dt','e'),(3,'ga','f');
--插入子表数据
>insert INTO tab_person_item VALUES(1,1),(1,2),(2,3),(4,3),(3,2);
#### 查询
--问题 查询人员中选了项目表中的项目为oa的中关村人员的id,和姓名
-- 方法一 分开查询
~~~ sql
select tab_person.person_id,tab_person.person_name from tab_person join (select tab_person_item.person_id from tab_item join tab_person_item on tab_item.item_name='oa'
and tab_item.item_id=tab_person_item.item_id) t on t.person_id = tab_person.person_id ;
-- 方法二 组合查询
select tab_person.person_id,tab_person.person_name from tab_person,tab_item,tab_person_item where tab_item.item_name='oa'
and tab_item.item_id=tab_person_item.item_id and tab_person_item.person_id = tab_person.person_id
-- 结果
join 的用处
将多个表连接在一块进行查询
使用join,不需要加条件
select count(*) from tab_person join tab_person_item
使用right join 和left join 需要加条件也就是on 否则包语法错误 -- 在关联表里删除了一行id=4
select * from tab_person left join tab_person_item ON tab_person_item.person_id = tab_person.person_id
具体区别看图