MySQL数据库的条件查询（2）

1. 连接查询

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

语法

select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

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

内连接查询： 查询的结果为两个表匹配到的数据	左连接查询： 查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充	右连接查询： 查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充
例1：使用内连接查询班级表与学生表 select * from students inner join classes on students.cls_id = classes.id;	例2：使用左连接查询班级表与学生表 此处使用了as为表起别名，目的是编写简单 select * from students as s left join classes as c on s.cls_id = c.id;	例3：使用右连接查询班级表与学生表 select * from students as s right join classes as c on s.cls_id = c.id; 例4：查询学生姓名及班级名称 select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;

2. 连接查询-自关联

• 自连接查询就是把一张表模拟成左右两张表，然后进行连表查询。
• 自连接就是一种特殊的连接方式，连接的表还是本身这张表

示例：

• 设计省信息的表结构provinces
  • id
  • ptitle

• 设计市信息的表结构citys

  • id
  • ctitle
  • proid

• citys表的proid表示城市所属的省，对应着provinces表的id值

观察两张表发现，citys表比provinces表多一个列proid，其它列的类型都是一样的，存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大

定义表areas，结构如下：

• id
• atitle
• pid

说明:

• 因为省没有所属的省份，所以可以填写为null
• 城市所属的省份pid，填写省所对应的编号id
• 这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
• 在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息

创建areas表的语句如下：

create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
);

从sql文件中导入数据：

source areas.sql;

查询一共有多少个省：

select count(*) from areas where pid is null;

例1：查询省的名称为“山西省”的所有城市	例2：查询市的名称为“广州市”的所有区县
select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省';	select dis.* from areas as dis inner join areas as city on city.aid=dis.pid where city.atitle='广州市';

3. 子查询

3.1. 子查询的介绍

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询.

主查询和子查询的关系:

1. 子查询是嵌入到主查询中
2. 子查询是辅助主查询的,要么充当条件,要么充当数据源
3. 子查询是可以独立存在的语句,是一条完整的 select 语句

3.2 子查询分类

• 标量子查询: 子查询返回的结果是一个数据(一行一列)
• 列子查询: 返回的结果是一列(一列多行)
• 行子查询: 返回的结果是一行(一行多列)

标量子查询	列级子查询	行级子查询
查询班级学生平均年龄 查询大于平均年龄的学生 查询班级学生的平均身高 select * from students where age > (select avg(age) from students);	查询还有学生在班的所有班级名字 找出学生表中所有的班级 id 找出班级表中对应的名字 select name from classes where id in (select cls_id from students);	需求: 查找班级年龄最大,身高最高的学生 行元素: 将多个字段合成一个行元素,在行级子查询中会使用到行元素 select * from students where (height,age) = (select max(height),max(age) from students);

3.3 子查询中特定关键字使用

• in 范围
  • 格式: 主查询 where 条件 in (列子查询)

总结

查询的完整格式

SELECT select_expr [,select_expr,...] [      
      FROM tb_name
      [WHERE 条件判断]
      [GROUP BY {col_name | postion} [ASC | DESC], ...] 
      [HAVING WHERE 条件判断]
      [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
      [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

完整的select语句

select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count

• 执行顺序为：
  • from 表名
  • where ....
  • group by ...
  • select distinct *
  • having ...
  • order by ...
  • limit start,count
• 实际使用中，只是语句中某些部分的组合，而不是全部