mysql的join查询分类

1.语句分类

2.join语句分类

      join的查询速度快于子查询，主要是因为子查询过程中需要创建中间表  

     内连接：inner join on = join on

     左外连接：left join on = LEFT OUTER JOIN on

     右外连接：right join on = right OUTER JOIN on

     全外连接： full join on 但mysql不支持，可使用union 连接左外连接和右外连接数据

     交叉连接：CROSS JOIN 相当于a*b表的结果，没有on从句

 

3.sql实例

a.建表

/* 老师表   */
CREATE TABLE teachers (
  id int(4) not null auto_increment,
  name varchar(20) not null default '' comment '老师名称',
  subject varchar(20) not null default '' comment '学科',
    primary key(id)
)engine=INNODB auto_increment=1 DEFAULT charset=utf8 comment '老师表';

insert into teachers (name,subject) values ('张三','数学'),('李四','语文'),('王五','英语'),('张柳','历史'),('三七','地理');

/* 父亲表   */
DROP table father;
create table father(
  id int(4) not null auto_increment,
  name varchar(20) not null default '' comment '名称',
  children varchar(20) not null default '' comment '孩子的名称',
  primary key (id)
)engine=innodb auto_increment=1 default charset=utf8 comment '父亲表';

insert into father(name,children) values ('张三','小孩1'),('李四','小孩2'),('王五','小孩3'),('大大','小孩4'),('小杰','小孩5');

create table teacherDate(
 id int(4) not null auto_increment,
 name varchar(20) not null default '' comment '老师名称',
 date date not null comment '上课日期',
 hour int(4) not null default 0 comment '当天的上课时间',
 primary key(id)
)engine=innodb auto_increment=1 default charset=utf8 comment '老师的上课时间表';

insert into teacherDate (name,date,hour) values ('张三','2019-06-11',3),('张三','2019-06-10',4),('张三','2019-06-09',5),('张三','2019-06-08',6)
,('李四','2019-06-11',3),('李四','2019-06-10',4),('李四','2019-06-09',5),('李四','2019-06-08',6)
,('王五','2019-06-11',3),('王五','2019-06-10',4),('王五','2019-06-09',5),('张柳','2019-06-08',6)
,('三七','2019-06-10',4),('三七','2019-06-09',5),('张柳','2019-06-11',6);

b.内连接

----属于老师且是父亲的信息 ：两表求并集  inner join = join----
SELECT a.name,a.subject,b.children from teachers a JOIN father b on a.name=b.name;
SELECT a.name,a.subject,b.children from teachers a INNER JOIN father b on a.name=b.name;

c.左连接

----是老师但不是父亲的 left join = LEFT OUTER JOIN ----
SELECT a.name,a.subject,b.children from teachers a left join father b on a.name=b.name where b.name is null ;
SELECT a.name,a.subject,b.children from teachers a left outer join father b on a.name=b.name where b.name is null ;

d.右连接

----是父亲但不是老师 right join = right OUTER JOIN  ----
select b.name,a.subject,b.children from teachers a right join father b on a.name=b.name where a.name is null;

e.全外连接

----选出老师以及父亲的全部信息 ----
SELECT a.name ,a.subject,b.children from teachers a FULL JOIN father b on a.name=b.name ;    /* mysql不支持full join on */

/* 将左关联和右关联查询结果使用union合并起来 */
SELECT a.name,a.subject,b.children from teachers a left join father b on a.name=b.name
   union
   select b.name,a.subject,b.children from teachers a right join father b on a.name=b.name;

f.交叉连接

---- 交叉连接：相当于A表*B表  没有on从句 ----
SELECT a.name,a.subject,b.children from teachers a CROSS JOIN father b ;

 

4.join查询的优化场景

a.优化子查询

子查询： SELECT COUNT(a.id) from shops a where a.sid in (SELECT b.sid from consignee b where b.static=1);

 join优化：SELECT COUNT(a.id) from shops a LEFT JOIN consignee b ON a.sid=b.sid where b.static=1;

join优化后的查询相当于子查询时间的一半

b.update不能操作from中的表

需求：更新 同时是老师和父亲的人员的学科信息为 家庭学

update teachers set subject='家庭学' where name in(/* 不能更新from从句中的表 */
  SELECT a.name from teachers a inner join father b on a.name=b.name
);    //[Err] 1093 - You can't specify target table 'teachers' for update in FROM clause

可使用如下语句:

UPDATE teachers d join (SELECT a.name from teachers a inner join father b on a.name=b.name) c on d.name=c.name set d.subject='家庭学';

update teachers a inner join father b on a.name=b.name set a.subject='家庭学3';

UPDATE teachers a , father b set a.subject='家庭学2' where a.name=b.name ;

c.join优化聚合子查询

查询出各个老师上课最多的日期

--查询特定老师上课最多的日期--

select c.id,c.name,max(c.hour) from teacherDate c where c.name='三七' ;

--查询老师的上课信息--
SELECT a.name,a.`subject`,b.date,b.hour from teachers a join teacherDate b on a.name=b.name;

通过将1的查询结果作为2的子查询的方式进行处理
SELECT a.name,a.`subject`,b.date,b.hour from teachers a join teacherDate b on a.name=b.name
  where b.hour in (select max(c.hour) from teacherDate c where c.name=b.name );

d.选出每个人打怪做多的两天的记录