Mysql高级查询

连接查询操作

• 当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回。

mysql支持的三种类型的连接查询，分别是：

• 内连接查询：
  • 查询结果为两个表匹配到的数据
• 右连接查询：
  • 查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充
• 左连接查询：
  • 查询的结果为两个表匹配到的数据，左表特有的数据，队友右表中不存在的数据使用null填充

实例：

• 下面创建一个班级表，一个学生表：

create table classes(
id int unsigned primary key auto_increment not null, 
name varchar(20) default '',
isdelete bit default 0
);

create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) default '',
age tinyint unsigned default 0,
height decimal(5,2),   -- 最大5位数字，其中两位小数
gender enum('男','女','人妖','保密'),
cls_id int unsigned default 0,   
isdelete bit default 0
);

• 为表插入数据

insert into classes values 
(1,"python1", 0),
(2,"python2",0),
(3,"python3",0),
(4,"python4",0),
(6,"python5", 1);

insert into students values
(0,'韦少',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'芙蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'周杰',34,176.00,2,5,0);

连接查询

内连接查询：inner join … on …

• on 后面跟着连接查询的条件。
• 使用内连接查询班级表与学生表
  • 连接查询
  • 查询班级学生的名字和学生所在班级名字
  • 学生名字：在students表
  • 班级名字：在classes表

select students.name,classes.name from students inner join classes on students.cls_id = classes.id;

左连接查询：left join on

• 左连接查询有主表和从表
• left前面的为主表，left后面的为从表
• 主表中的数据会全部显示（不管满不满足连接条件都会显示）

select s.name,c.name from students as s left join classes as c on s.cls_id = c.id;

右链接查询：right join on

• 右连接查询：right前面的为从表，right后面的为主表

select s.name,c.name from students as s right join classes as c on s.cls_id = c.id;

扩展学习：展示出班级中所有的学生

• 内连接的做法的第一种：

select c.name,group_concat(s.name) from students as s,classes as c where s.cls_id = c.id group by c.name;

• 内连接的做法的第二种：

select c.name,group_concat(s.name) from students as s inner join classes as c on s.cls_id = c.id group by c.name;

• 左连接的做法：

select c.name,group_concat(s.name) from students as s left join classes as c on s.cls_id = c.id group by c.name;

• 右连接的做法：

select c.name,group_concat(s.name) from students as s right join classes as c on s.cls_id = c.id group by c.name;

内连接的两种简写模式

• join on

select s.name, c.name from students as s join classes as c on  s.cls_id = c.id;

• 第二种简写：cross join on

select s.name, c.name from students as s cross join classes as c on  s.cls_id = c.id;