SQL入门教程（五）：数据库高级查询

1.排序

--order by 字段
--asc 从小到大排列
--desc 从大到小排列

--查询18到34岁之间的男性，按照年龄从小到大排列
select * from students where (age between 18 and 34) and gender='男' order by age;

--从大到小排列
select * from students where (age between 18 and 34) and gender='男' order by age desc;

--order by 多个字段排序，年龄从小到大，若年龄相同,身高从高到低
select * from students order by age asc, height desc;

2.聚合函数

--查询有多少男性，多少女性
select count(*) as 男性总数 from students where gender='男';

--查询最大年龄
select max(*) as 最大年龄 from students;

--计算所有人年龄总和
select sum(age) from students;

--计算平均年龄
select sum(age)/count(*) from students;
select avg(age) from students;

--四舍五入，保留两位小数
select round(avg(height), 2) from students;

3.分组函数

--group by 按...分组
select gender from students group by gender;

--计算每组中的人数
select gender, count(*) from students group by gender;

--显示每组的人名
select gender, group_concat(name, 'ID', id, '年龄', age) from students group by gender;

--对分组人群进行筛选 having
select gender, froup_concat(name) avg(age) from students group by gender having avg(age)>30;

3.分页

--限制查询个数
select * from students where gender=1 limit 2;

--limit 开始个数，数量
--查询第N页公式：limit (第N-1页)*每页的个数，每页的数量
--查询第26个页面，每页显示7个值
select * from studemts where gender=1 limit 25,7;

--注：limit放在各种条件最后进行限制 

4.多个数据库连接查询

4.1内连接

--将两个数据库连接在一起
--第一个数据库的每行对应第二个数据库的每项
select * from students inner join classes;

--能有效对应学术班级信息
--on代表条件
select * from students inner join classes on students.cls_id=classes.id;

--按要求显示相要的信息
select students.*, classes.name from students inner join classes on students.cls_id=classes.id; 

--把想要的内容排序
select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name s.id;

4.2 左连接：以左侧的数据库为基准， 匹配右侧的内容，未匹配的显示NULL

--左连接 left join
--查询每位学生对应的班级信息
select * from students as s left join classes as c on s.cls_id=c.id;

--查询没有对应学生的信息
--where 作为原始表的数据筛选，having作为查询出新表的数据筛选
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;

4.3 子查询

--查询最高男生的信息
select * from students where height=(select max(heught) from students);