mysql数据库的条件查询（二）

1. 排序

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,…]
语法说明:
先按照列1进行排序，如果列1的值相同时，则按照 列2 排序，以此类推
asc从小到大排列，即升序
desc从大到小排序，即降序
默认按照列值从小到大排序（即asc关键字）

例1：查询未删除男生信息，按学号降序:
select * from students where gender=1 and is_delete=0 order by id desc;

例2：显示所有的学生信息，先按照年龄从大–>小排序，当年龄相同时 按照身高从高–>矮排序:
select * from students order by age desc,height desc;

2. 分页查询
分页查询的语法
select * from 表名 limit start,count
说明:
limit是分页查询关键字
start表示开始行索引，默认是0
count表示查询条数

例1：查询前3行男生信息:
select * from students where gender=1 limit 0,3;
简写
select * from students where gender=1 limit 3;

分页查询案例
已知每页显示m条数据，求第n页显示的数据
提示: 关键是求每页的开始行索引
查询学生表，获取第n页数据的SQL语句:
select * from students limit (n-1)*m,m

使用 limit 关键字可以限制数据显示数量，通过 limit 关键可以完成分页查询
limit 关键字后面的第一个参数是开始行索引(默认是0，不写就是0)，第二个参数是查询条数

3.聚合函数

聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组（group by）来使用，
用于统计和计算分组数据
常用的聚合函数:
1. count(col): 表示求指定列的总行数
2. max(col): 表示求指定列的最大值
3. min(col): 表示求指定列的最小值
4. sum(col): 表示求指定列的和
5. avg(col): 表示求指定列的平均值

求总行数
---- 返回非NULL数据的总行数
select count(height) from students;
---- 返回总行数，包含null值记录
select count(*) from students;

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

求最小值
---- 查询未删除的学生最小编号
select min(id) from students where is_delete = 0;

求和
– 查询男生的总身高
select sum(height) from students where gender = 1;
– 平均身高
select sum(height) / count(*) from students where gender = 1

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

– 求男生的平均身高, 包含身高是null的
select avg(ifnull(height,0)) from students where gender = 1;

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

聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算，必须使用ifnull函数对null值做替换。

4. 分组查询

分组查询基本的语法格式：
group by 列名 [having 条件表达式] [with rollup]
说明:
列名: 是指按照指定字段的值进行分组。
having条件表达式: 用来过滤分组后的数据。
with rollup：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

group by的使用
group by可用于单个字段分组，也可用于多个字段分组
– 根据gender字段来分组
select gender from students group by gender;
– 根据name和gender字段进行分组
select name, gender from students group by name, gender;

group by + group_concat()的使用
group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割
– 根据gender字段进行分组， 查询gender字段和分组的name字段信息
select gender,group_concat(name) from students group by gender;

group by + 聚合函数的使用
– 统计不同性别的人的平均年龄
select gender,avg(age) from students group by gender;
– 统计不同性别的人的个数
select gender,count(*) from students group by gender;

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

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

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

-- 根据gender字段进行分组，汇总总人数
select gender,count(*) from students group by gender with rollup;
-- 根据gender字段进行分组，汇总所有人的年龄
select gender,group_concat(age) from students group by gender with rollup;

1. group by 根据指定的一个或者多个字段对数据进行分组
2. group_concat(字段名)函数是统计每个分组指定字段的信息集合
3. 聚合函数在和 group by 结合使用时, 聚合函数统计和计算的是每个分组的数据
4. having 是对分组数据进行条件过滤
5. with rollup在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果

5. 连接查询-内连接
连接查询的介绍
a. 内链接查询
b. 左连接查询
c. 右连接查询
d. 自连接查询

内连接查询语法格式:
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2
说明:
inner join 就是内连接查询关键字
on 就是连接查询条件

例1：使用内连接查询学生表与班级表:
select * from students as s inner join classes as c on s.cls_id = c.id;

6. 连接查询-左连接

左连接查询语法格式:
select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2
说明:
left join 就是左连接查询关键字
on 就是连接查询条件
表1 是左表
表2 是右表

例1：使用左连接查询学生表与班级表:
select * from students as s left join classes as c on s.cls_id = c.id;

7. 连接查询-右连接

右连接查询语法格式:
select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2
说明:
right join 就是右连接查询关键字
on 就是连接查询条件
表1 是左表
表2 是右表

例1：使用右连接查询学生表与班级表:
select * from students as s right join classes as c on s.cls_id = c.id;

连接查询-自连接

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

创建areas表:
create table areas(
id varchar(30) not null primary key,
title varchar(30),
pid varchar(30)
);

执行sql文件给areas表导入数据:
source areas.sql;
说明:
source 表示执行的sql文件

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

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

子查询的使用
例1. 查询大于平均年龄的学生:
select * from students where age > (select avg(age) from students);

例2. 查询学生在班的所有班级名字:
select name from classes where id in (select cls_id from students where cls_id is not null);

例3. 查找年龄最大,身高最高的学生:
select * from students where (age, height) = (select max(age), max(height) from students);

9. 数据库设计之三范式
范式: 对设计数据库提出的一些规范，目前有迹可寻的共有8种范式，一般遵守3范式即可。

第一范式（1NF）: 强调的是列的原子性，即列不能够再分成其他几列。
第二范式（2NF）: 满足 1NF，另外包含两部分内容，一是表必须有一个主键；二是非主键字段 必须完全依赖于主键，而不能只依赖于主键的一部分。
第三范式（3NF）: 满足 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

1. 范式就是设计数据库的一些通用规范。
   a. 1NF强调字段是最小单元，不可再分
   b. 2NF强调在1NF基础上必须要有主键和非主键字段必须完全依赖于主键，也就是说 不能部分依赖
   c. 3MF强调在2NF基础上 非主键字段必须直接依赖于主键，也就是说不能传递依赖(间接依赖)。
2. E-R模型由 实体、属性、实体之间的关系构成，主要用来描述数据库中表结构。
3. 开发流程是先画出E-R模型，然后根据三范式设计数据库中的表结构

10. 外键SQL语句的编写
外键约束作用:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，
数据如果不合法则更新和插入会失败，保证数据的有效性

对于已经存在的字段添加外键约束
– 为cls_id字段添加外键约束
alter table students add foreign key(cls_id) references classes(id);

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

– 创建学校表
create table school(
id int not null primary key auto_increment,
name varchar(10)
);

– 创建老师表
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 外键名;