MySQL——连接查询

含义: 又称多表查询,当查询的字段来自于多个表时,就会用到连接查询

比如下面两张表:

查询每个每个类型下的社团信息,这里面的数据来源于两个表,就用到了多表查询。

语法:

-- 查询社团名称和社团分类名称
select klasifiko_name,name from club,club_klasifiko;

查询出的数据只是club与club_klasifiko进行无条件匹配。

笛卡尔积现象:表1有m行,表2有n行,结果=m*n行

发生原因:没有有效的连接条件

如何避免:添加有效的连接条件

select klasifiko_name,name from club_klasifiko,club where club_klasifiko.id=club.klasifiko_id;
连接查询分类:

按功能分类:

  1. 内连接
    1. 等值连接
    2. 非等值连接
    3. 自连接
  2. 外连接
    1. 左外连接
    2. 右外连接
    3. 全外连接(MySQL不支持)
  3. 交叉连接
1. 等值连接:

在连接条件中使用等于号(=)运算符比较被连接列的列值

例如:查询出每个社团类型下对应的社团名称

select klasifiko_name,name from club_klasifiko,club where club_klasifiko.id = club.klasifiko_id;
  • 为表起别名:

    • 优点:提高语句的简洁度,区分多个重名的字段
    • 注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定
  • 当查询语句中设计多张表或者表名比较复杂时,我们可以为其起别名

select c1.klasifiko_name,c2.name from club_klasifiko as c1,club as c2 where c1.id=c2.klasifiko_id;
  • 当从多张表查询数据时,表的顺序可以任意调换
select c1.klasifiko_name,c2.name from club as c2,club_klasifiko as c1 where c1.id=c2.klasifiko_id;
  • 加筛选条件
-- 查询出每个文化体育类型下对应的社团名称
select c1.klasifiko_name,c2.name from club_klasifiko c1,club c2 where c1.id=c2.klasifiko_id and c1.klasifiko_name='文化体育';
2. 非等值连接:

连接条件不为’=’

例如:查询员工的工资和工资级别,如下图所示:

select salary,grade_level from job_grades,employees where salary between lowest_sal and highest_sal;

MySQL——连接查询

3. 自连接:

顾名思义:自己连接自己

自连接是连接的一种用法,但并不是连接的一种类型,因为他的本质是把一张表当成两张表来使用。mysql有时在信息查询时需要进行对自身连接(自连接),所以我们需要为表定义别名。

例如:查询出比大学生画坊入会费还高的社团的信息

select c2.* from club c1,club c2 where c1.name = '大学生花坊' and c2.cost > c1.cost;
4. 外连接:
4.1 左外连接:left join

关键字: left join on 或 left outer join on

MySQL——连接查询
语句:select * from table1 t1 left join table2 t2 on t1.t_id=t2.t_id;

说明:left join 是left outer join的简写,它的全称是左外连接,是外连接的一种。左外连接,左表(table1)的记录将会全部表示出来,而右表(table2)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

比如:查询出不同类型下的所有社团信息

select * from club_klasifiko c1 left join club1 c2 on c1.id=c2.klasifiko_id;
4.2 右外连接:rigth join

关键字:rigth join on 或 right outer join on
MySQL——连接查询
语句:select * from table1 t1 right join table2 t2 on t1.t_id=t2.t_id;

右连接与左连接的效果恰恰相反。右表的记录会全部显示出来,左表只会显示符合搜索条件的记录。

比如:查询出不同社团对应的社团类型

 select * from club_klasifiko c1 right join club1 c2 on c1.id=c2.klasifiko_id;
4.3 交叉连接:cross join

交叉连接的结果就如同笛卡尔积的结果一样

例如:

select * from club_klasifiko cross join club1;
4.4 内连接:inner join

关键字:inner join on
MySQL——连接查询
语句:select * from table1 t1 inner join table2 t2 on t1.t_id=t2.t_id;

说明:组合两个表中的记录,返回关联字段相符的记录,也就是两个表的交集部分。

例如:查询出社团类型表和社团信息表的交集

select * from club_klasifiko c1 inner join club1 c2 on c1.id=c2.klasifiko_id;
5. 子查询:

含义:出现在其他语句中的select语句,成为子查询或者内查询

内部嵌套其他查询语句的查询,成为主查询或外查询

例如:

select c1.klasifiko_name from club_klasifiko c1 where c1.id in (select c2.klasifiko_id from club c2 where c2.cost=10);
分类:

按子查询出现的位置:

  • select后面:
    • 仅仅支持标量子查询
  • from后面
    • 支持表子查询
  • where或having后面(重点)
    • 标量子查询
    • 列子查询
    • 行子查询
  • exists后面(相关子查询)
    • 表子查询

按结果集的行列数不同:

  • 标量子查询(结果集只有一行一列)
  • 列子查询(结果集只有一列多行)
  • 行子查询(结果集可以有一行多列)
  • 表子查询(结果集一般为多行多列)
where或having后面:

特点:

  1. 子查询放在小括号内
  2. 子查询一般放在条件的右侧
  3. 标量子查询,一般搭配着单行操作符使用(> < >= <= = !=)
  4. 列子查询,一般搭配着多行操作符使用(in、any/some、all)
  5. 子查询的执行优先于主查询

标量子查询:

例如:哪个社团的收费比’大学生画坊的收费高’。

1.查询出大学生画坊社团的收费

select cost from club where name='大学生画坊';

2.查询社团的信息,满足cost>上一步的结果

select c.name from club c where c.cost > (select cost from club where name='大学生画坊');

例如:查询收费与大学生画坊相同的社团的信息

1.查询出大学生画坊社团的收费

select cost from club where name='大学生画坊';

2.查询社团的信息,满足cost>上一步的结果

select c.name,c.cost from club c having c.cost=(select cost from club where name='大学生画坊') and c.name != '大学生画坊';

列子查询:

例如:查询出文化体育或创新创业类型下的所有社团信息

1.查询出文化体育和创新创业的id

select id from club_klasifiko where klasifiko_name='文化体育' or klasifiko_name='创业创新';

2.查询社团信息,满足klasifiko_id in上一步的结果

select name from club where klasifiko_id in(select id from club_klasifiko where klasifiko_name='文化体育' or klasifiko_name='创业创新');

行子查询

例如:查询出社团编号最小且收费最高的社团信息(可能没有数据)

select * from club where (id,cost)=(select min(id),max(cost) from club);
select后面:

例如:查询出每个类型下对应的社团个数

select *,(select count(*) from club c2 where c2.klasifiko_id= c1.id) as 个数 from club_klasifiko c1;
from后面:

将子查询的结果重当一张表,要求必须起别名

例如:查询不同类型下社团的平均收费

select c1.klasifiko_name,c2.* from (select avg(cost),klasifiko_id from club group by klasifiko_id) c2 inner join club_klasifiko c1 on c1.id=c2.klasifiko_id;
exists后面(相关子查询):

EXISTS用于检查子查询是否至少返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False

语法:exists(完整的查询语句)

例如:

select exists(select icon from club_klasifiko);
+---------------------------------+
| exists(select icon from club_klasifiko) |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.05 sec)
select exists(select * from club_klasifiko where is_delete=1);
+---------------------------------------------+
| exists(select * from club_klasifiko where is_delete=1) |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
1 row in set (0.02 sec)

例如:查询有社团的类型名称

select c1.klasifiko_name from club_klasifiko c1 where exists(select * from club c2 where c1.id=c2.klasifiko_id);
分页查询:

应用场景:当要显示的数据,一页显示不全,需要分页一脚sql请求

语法:select 查询列表 from 表 【条件】limit offset,size;

offset 要显示条目的其实索引(起始索引从0开始)

size 要显示的条目个数

例如:查询前五条学生信息

select * from std_message limit 0,5;

例如:查询第11条到第25条学生信息

 select * from std_message limit 10,15;
联合查询:union

union:联合 合并:将多条查询语句的结果合并成一个结果

语法:查询语句1 union 查询语句2 union …

例如:查询出收费大于10或社团名称中包含’会’的社团信息

select name,cost from club where cost > 10 union select name,cost from club where name like '%会%';
上一篇:python数据分析 Lending Club贷款数据


下一篇:基于java拼图游戏