Mysql之连接查询

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

新老客户大回馈,云服务器低至5折

#数据准备
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;

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:翻译:MariaDB wait/nowait

下一篇:详解MySQL基准测试和sysbench工具