MySQL分页查询

分页查询

运用

• 使用 limit 关键字可以限制数据显示数量，通过 limit 关键可以完成分页查询

• limit 关键字后面的第一个参数是开始行索引(默认是0，不写就是0)，第二个参数是查询条数

聚合函数

聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结果分组（group by）来使用，用于统计和计算分组数据

常用聚合函数：

1. count:表示求指定列的总行数

2. max：表示求指定列的最大值

3. min:表示求指定列的最小值

4. sum:求和

5. avg：求平均值

求总行数

--返回非NUll数据的总行数
---注意:聚合函数不会对空值进行统计
select count(height) from students;

求最大值

--- 查询女生的编号最大值
select max(id) from students where sex='女';

求和

-- 查询男生的总身高
select sum(height) from students where sex='男';
-- 平均身高
select sum(height)/count(*) from students where sex='男';

求平均值

-- 求男生的平均身高，聚合函数不统计null值，平均身高有误
select avg(height) from students where sex='男';

--求男生的平均身高，包含身高是null的
select avg(ifnull(height,0)) from students where sex='男';

ifnull函数：表示判断指定字段的值是否为null，如果为空使用自己提供的值

分组查询

group by的使用

group by 可用于单个字段分组，也可用于多个字段分组

-- 根据性别字段分组
select sex from students group by sex;

-- 根据name和sex字段进行分组
select name,sex from students group by name,sex;

group by +group_concat()使用

group_concat(字段名)：统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割

select sex,group_concat(name) from students group by sex;

group by +聚合函数使用

-- 统计不同性别的人的平均年龄
select sex,avg(ifnull(age,0)) from students group by sex;
-- 统计不同性别的人的人数
select sex,count(*) from students group by sex;

group by+having的使用

having作用和where类似 都是过滤数据的，但是having是过滤分组数据的，只能用于group by

-- 根据性别字段进行分组，统计分组条数的大于2的
select sex,count(*) from students group by sex having count(*)>2;

group by +with rollup的使用

with rollup的作用是：在最后记录后面新增一行，显示select查询聚合函数的统计和计算结果

-- 根据sex字段进行分组，汇总总人数
select sex,count(*) from students group by sex with rollup;

多表联查

连接查询-内连接

取的是两个表当中的共有记录（交集）

select * from students inner join classes on students.c_id = classes.id;

连接查询-左连接

以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充

 select * from students as s left join classes as c on s.c_id = c.id;

连接查询-右连接

以右表为主根条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充

select * from students as s right join classes as c on s.c_id = c.id;

连接查询-自连接

左表和右表是同一个表，根据连接查询条件查询两个表中的数据

select c.id,c.title,c.pid,p.title from tb_areas as c inner join tb_areas as p on c.pid = p.id where p.title=' 湖南省';

说明：

• 自连接查询必须对表起别名

• 自连接查询就是把一张表模拟成左右两张表，然后进行连表查询。

• 自连接就是一种特殊的连接方式，连接的表还是本身这张表

子查询

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

主查询和子查询的关系：

1. 子查询是嵌入到主查询中

2. 子查询是辅助主查询的，要么充当条件，要么充当数据源

3. 子查询是可以独立存在的语句，是一条完整的select语句

使用

--- 查询大于平均年龄的学生
select * from students where age>(select avg(age) from students);

-- 查询学生在班的所有班级名字
select name from classes where id in (select c_id from students where c_id is not null);

子查询是一个完整的SQL语句。子查询被嵌入到一对小括号里面

外键

外键约束：对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性

对于已经存在的字段添加外键约束

alter table students add foreign key(c_id) references classes(id);

在创建数据表时设置外键约束

create table teacher(id int not null primary key auto_increment,name varchar(10),s_id int not null,foreign key(s_id) references school(id));

删除外键

-- 需要先获取外键约束名称，该名称系统会自动生成，可以通过查看表创建语句来获取名称
show create table teacher;

-- 获取名称之后就可以根据名称来删除外键约束
alter table teacher drop foreign key 外键名;

说明

添加外键约束：alter table 从表 add foreign key(外键字段) references 主表(主键字段）

删除外键约束：alter table表名 drop foreign key 外键名;