mysql 关联查询技巧

2022-11-25 10:10:47

废话不多说，直接进入正题

#数据准备

班级表class:

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

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

相关文章