首页 > > 网络编程 > Mysql >

Mysql之连接查询

2018-06-18 00:19:04来源：未知阅读 ()

#数据准备
drop table if exists class;
create table class(
   class_no int(2) unsigned zerofill primary key auto_increment comment '班级编号',
   class_name varchar(30) not null comment '班级名称'
);
insert into class values(1, '培优班');
insert into class values(2, '普通班');
insert into class values(3, '提升班');

drop table if exists student;
create table student(
   stu_no int(2) unsigned zerofill primary key auto_increment comment '学员编号',
   stu_name varchar(30) not null comment '学员姓名',
   stu_sex varchar(3) not null comment '学员性别',
   stu_age tinyint(2) unsigned zerofill comment '学员年代',
   grade double(5,2) zerofill comment '成绩',
   class_no int(2) unsigned zerofill comment '所在班级编号',
   foreign key(class_no) references class(class_no)
);
insert into student values(01, '李白', '男', 18, 60, 01);
insert into student values(02, '杜甫', '男', 20, 76, 01);
insert into student values(03, '张飞', '男', 32, 80, 02);
insert into student values(04, '韩信', '男', 26, 98, 02);
insert into student values(05, '了龙', '男', 27, 56, 02);
insert into student values(06, '大乔', '女', 17, 88, 01);
insert into student values(07, '小乔', '女', 16, 96, 01);
insert into student values(08, '小乔', '女', 16, 90, 01);
insert into student values(09, '关哥', '男', 32, 80, 02);
insert into student values(10, '刘备', '男', 36, 98, null);
alter table student drop foreign key `student_ibfk_1`;
***********************************************************************************************************************************

1: exists子查询
   如果子查询有返回结果则为true,如果没有返回值则为false
   例: select * from student where exists(select * from student where grade = 80)
   例: select * from student where not exists(select * from student where grade = 80);
        select * from student where exists (select * from class where class.class_no = student.class_no);
***********************************************************************************************************************************

2: [union] 并合查询
   需求: 拿到01班级的最高成绩和 02班级的最低成绩
   select max(grade) from student where class_no = 01;
   select min(grade) from student where class_no = 02;
   例:   (select concat('1号班级最高成绩:', max(grade)) '成绩' from student where class_no = 01)
       union
       (select concat('2号班级最低成绩:', min(grade)) '成绩' from student where class_no = 02);

   例: (select class_no, stu_name, stu_age from student where class_no = 1)
       union
       (select class_no, stu_name, stu_age from student where class_no = 2);

   例: (select class_no, stu_name, stu_age from student where class_no = 1)
       union all
       (select class_no, stu_name, stu_age from student where class_no = 2);
   ps: union并合查询它会自动的去重复的记录, 如果不想要去掉重复的记录则可以使用 union all;

   例: (select class_no, stu_name, stu_age from student where class_no = 1)
       union all
       (select class_no, stu_name, stu_age from student where class_no = 2) order by stu_age desc;
***********************************************************************************************************************************

连接查询的分类
   1: 内连接
   2: 外连接
   3: 自然连接

1: inner join(内连接)
   需求: 查询出学员的学号, 姓名, 所在的班级名称
   例: select stu_no, stu_name, class_name from student inner join class where `student`.class_no = `class`.class_no;
   例: select stu_no, stu_name, class_name from student join class where `student`.class_no = `class`.class_no;
        select stu_no,stu_name,class_name from student,class where student.class_no = class.class_no;
   ps: 内连接的inner字符可以不用写

   例: select stu_no, stu_name, class_name from student inner join class where class_no = class_no;   --报错
   例: select class_no, stu_no, stu_name, class_name from student inner join class where `student`.class_no = `class`.class_no; --报错
   ps: 如果说连接字段或者要查询的字段存在冲突时,这个时候应该要给这些字段加上表名,用来区分这个字段到时底是哪个表的,也可以给表起一个别名,用表的别名来区分

2: cross join(交叉连接,迪卡尔集) 没有条件的内连接
   例: select * from student cross join class;
   例: select * from student inner join class;
   例: select * from student cross join class where `student`.class_no = `class`.class_no;
   ps: cross join 与 inner join 在使用上没有区分，只是在mysql中把cross join定义成交叉连接而已

3: 连接条件
   where( 在做完成交叉连接后然后再对数据进行过滤 )
       例: select stu_no, stu_name, class_name from student inner join class where `student`.class_no = `class`.class_no;
   on( 在连接的时候就开始判断过滤 )
       例: select stu_no, stu_name, class_name from student inner join class on `student`.class_no = `class`.class_no;
   using( 要求连接的两个表的这个连接字段名称是要一样的 )
       例: select stu_no, stu_name, class_name from student inner join class using(class_no);
   ps: 如果连接的字段名称同名的则选择使用using, 在通用的情况则使用on

4: 外连接
   left outer join(左外连接)
       在连接的时候, 如果出现左边表的数据连接不到右边表的数据时。则左边表的数据在最终结果里保存,而右边表的数据不会保存
       例: select stu_no, stu_name, class_name from student left outer join class on `student`.class_no = `class`.class_no;
       例: select stu_no, stu_name, class_name from class left outer join student on `student`.class_no = `class`.class_no;

   right outer join(右外连接)
       在连接的时候, 如果出现右边表的数据连接不到左边表的数据时。则右边表的数据在最终结果里保存,而左边表的数据不会保存
       例: select stu_no, stu_name, class_name from student right outer join class on `student`.class_no = `class`.class_no;

   全外连接(现不支持)
       在mysql里边还没有全外连接, 但是我们可以通过union all来进行模拟
   (select stu_no, stu_name, class_name from student left outer join class on `student`.class_no = `class`.class_no)
   union
   (select stu_no, stu_name, class_name from student right outer join class on `student`.class_no = `class`.class_no)

   例: select stu_no, stu_name, class_name from student right outer join class;   --报错
   ps: 外连接不能没有条件
   例: select stu_no, stu_name, class_name from student right outer join class where `student`.class_no = `class`.class_no; --报错
   ps: 外连接不能使用where

5: 自然连接(不能使用on)
   自然连接就是不需要连接条件的,mysql它会自动使用表内的相同的字段作为连接条件
   自然内连接(natural join)
       例: select * from student natural join class;
       例: select * from student inner join class where `student`.class_no = `class`.class_no;

   自然左外连接(natural left join)
       例: select * from student natural left join class;

   自然右外连接(natural right join)
       例: select * from student natural right join class;