含义: 又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
比如下面两张表:
查询每个每个类型下的社团信息,这里面的数据来源于两个表,就用到了多表查询。
语法:
-- 查询社团名称和社团分类名称
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;
连接查询分类:
按功能分类:
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接
- 右外连接
- 全外连接(MySQL不支持)
- 交叉连接
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;
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
语句: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
语句: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
语句: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后面:
特点:
- 子查询放在小括号内
- 子查询一般放在条件的右侧
- 标量子查询,一般搭配着单行操作符使用(> < >= <= = !=)
- 列子查询,一般搭配着多行操作符使用(in、any/some、all)
- 子查询的执行优先于主查询
标量子查询:
例如:哪个社团的收费比’大学生画坊的收费高’。
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 '%会%';