mysql 关联查询技巧

TABLE `class` ( `class_no` int(2) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT ‘班级编号‘, `class_name` varchar(30) CHARACTER SET utf8 NOT NULL COMMENT ‘班级名称‘, PRIMARY KEY (`class_no`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
insert into class values(1, ‘培优班‘); insert into class values(2, ‘普通班‘); insert into class values(3, ‘提升班‘);

学生表student:

CREATE TABLE `student` (
  `stu_no` int(2) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT ‘学员编号‘,
  `stu_name` varchar(30) CHARACTER SET utf8 NOT NULL COMMENT ‘学员姓名‘,
  `stu_sex` varchar(3) CHARACTER SET utf8 NOT NULL COMMENT ‘学员性别‘,
  `stu_age` tinyint(2) unsigned zerofill DEFAULT NULL COMMENT ‘学员年代‘,
  `grade` double(5,2) unsigned zerofill DEFAULT NULL COMMENT ‘成绩‘,
  `class_no` int(2) unsigned zerofill DEFAULT NULL COMMENT ‘所在班级编号‘,
  PRIMARY KEY (`stu_no`),
  KEY `class_no` (`class_no`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

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);

1: exists子查询

如果子查询有返回结果则为true,如果没有返回值则为false

select * from student where exists(select * from student where grade = 80)

比如not exists：

select * from student where not exists(select * from student where grade = 80);

以上结果返回空,因为 not exists 返回了 false

select * from student where exists (select * from class where class.class_no = student.class_no);

上面的查询可以看到，我们少了一条数据，第十条的clas_no 是null。。。所以这条数据是flase....而

class.class_no = student.class_no 为true的，就全部返回了

 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);

这里再说下union 和union all的区别：

union:

(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);

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);

通过以上两个查询，我们可以看到：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  join class where `student`.class_no = `class`.class_no;

以上sql等同于：

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;

内连接的inner字符可以不用写

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定义成交叉连接而已

就写到这把。。其他的连接方式也简单，资料也很多啦。。。

mysql 关联查询技巧

标签：返回   rom   unsigned   concat   排序   ges   max   进入   mysq