˙Ⱉ˙学习笔记——mysql查询(2)

发布于 2021-03-31 05:24

--聚合函数

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

--最大值
--max
--查询最大的年龄
select max(age) from students;

--查询女性的最高 身高
select max(height) from students where gender=2;

--最小值
--min

--求和
--sum
--计算所有人的年龄综合
select sum(age) from students;

--平均值
--avg
--计算平均年龄
select avg(age) from students;

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

--四舍五入 round(123.23,1) 保留1位小数
--计算所有人的平均年龄，保留2位小数
select round(sum(age)/count(*),2) from students;

--计算男性的平均身高 保留2位小数
select round(sum(age)/count(*),2) from students where gender=1;
--失败select round(sum(age)/count(*),2) from students where gender=1;

--分组

--group by
--按照性别分组，查询所有性别
--失败select name from students group by gender;
--失败select * from students group by gender;
select gender from students group by gender;

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

--计算男性的人数
select gender,count(*) from students where gender =1 group by gender;

--group_concat(…) (链接在一起显示)
--查询同种性别的姓名
select gender, group_concat(name) from students group by gender;
>>>彭于晏，刘德华
select gender, group_concat(name, age, id) from students group by gender;
>>> 彭于晏293，刘德华596…
select gender, group_concat(name, “_”, age, “ ”, id) from students group by gender;
>>> 彭于晏_29 3，刘德华_59

--having
--查询平均年龄超过30岁的性别，以及姓名 having avg(age)>30
select gender , group_concat(name), avg(name) from students group by gender having avg(name)>3;
where对原始表判断，having对结果进行判断

--查询每种性别中的人数多于2个的信息
--select gender, group_concat(name) from students group by gender having count(*)>2;

--分页

--limit start, count

--限制查询出来的数量
select * from students where gender=1 limit2;

--查询前5个数据
select *from students limit 5;
select *from students limit 0,5; （从0开始5个）

--查询id6-10（包括）的书序
select *from students limit 5,5;

--每页显示2个，第1个页面
select * from students limit 0,2;

--每页显示2个，第2个页面
select * from students limit 2,2;

--每页显示2个，第3个页面
select * from students limit 4,2;

--每页显示2个，第4个页面
select * from students limit 6,2; -----> limit(第N页-1)*每个的个数，每页的个数

--每页显示2个，显示第6页的信息，按照年龄从小到大排序
--失败select *from students limit 2*(6-1), 2
--失败select *from students limit 10, 2 order by age asc；（limit在最后）
select *from students order by age asc limit 10, 2；

where->orderby->limit

--连接查询

--inner join …on

--select… from 表a inner join 表b；
select * from students inner join classes;

--查询 有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;

--按照要求显示姓名，班级
select students.name , classes.name from students inner join classes on students.cls_id=classes.id;

--给数据表起名字
select s.name , c.name from students as s inner join classes as c on s.cls_id=c.id;

--查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
select s.* , c.name from students as s inner join classes as c on students.cls_id=classes.id;

--在以上的查询中，将班级姓名显示在第1列
select c.name, s.* from students as s inner join classes as c on students.cls_id=classes.id;

--查询 有能够对应班级的学生以及班级信息，按照班级进行排序
--select c.xxx s.xxx from student as s inner join classes as c on …order by…;
select c.name, s.* from students as s inner join classes as c on students.cls_id=classes.id order by c.name;

--当时同一个班级的时候，按照学生的id进行从小到大排序
select c.name, s.* from students as s inner join classes as c on students.cls_id=classes.id order by c.name, s.id;

--left join
--查询每位学生对应的班级信息
select * from students as s left join classes as c on s.cls_id=c.id;
students表在左边，以students表所有的记录为基准，没有对应显示null

--查询没有对应班级信息的学生
--select … from xxx as s left join xxx as c on …where …
--select … from xxx as s left join xxx as c on …having…
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;
select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;

--right join  on
--将数据表名字互换位置，用left join完成

--自关联

--省级联动 url:http://demo.lanrenzhijia.com/2014/city0605/

--创建areas表 （用一张表完成）
Create table areas(
      aid int primary key,
      atitle varchar(20),
      pid int
  );

--查询所有省份
select * from areas where pid is null;

--查询出山东省有哪些城市
select aid from areas where atitle= “山东省”;
select * from areas where pid=370000;
select * from areas as province inner join areas as city on city.pid=provice.aid having province.artitle= “山东省”;
select province.atitle, city.atitle from areas as province inner join areas as city on city.pid=provice.aid having province.artitle= “山东省”;
（一张表当多个表对待）
select * from areas where pid=(select aid from areas where atitle= “山东省”)；

--子查询

--标量子查询

--查询出高于平均身高的信息

--查询最高的男生信息

select * from students where height(select max(height) from students);

--列级子查询

--查询学生的班级号能够对应的学生信息

select * from students where cls_id in (select id from classes);

本文来自网络或网友投稿，如有侵犯您的权益，请发邮件至：aisoutu@outlook.com 我们将第一时间删除。

相关素材

放在床上的笔记本图片
放在床上的笔记本图片
放在桌子上的笔记本图片
放在桌子上的笔记本图片
读书笔记草地夏日阅读记录阅读学习插画
读书笔记草地夏日阅读记录阅读学习插画