MySql查询二

聚合函数

• count(*)表示计算总行数，括号中写星（全查）或列名
• max(列)表示求此列的最大值
• min(列)表示求此列的最小值
• sum(列)表示求此列的和
• avg(列)表示求此列的平均值

表结构

id	name	age	high	gender	cls	birth	is_delete
1	iron man	28	179.99	男	1	2020-01-01	1
3	shell	20	212.22	男	1	NULL	0
4	joy	20	160.00	女	1	1996-01-01	0
5	jujuboy	18	141.00	女	1	2019-11-29	0
6	fafa	18	179.99	女	2	2019-11-29	0
7	yao	40	212.00	男	2	2019-11-29	0
8	jr	25	202.00	男	2	2019-11-29	0
9	shell	20	186.00	保密	3	2019-11-29	0
10	jr	25	182.00	保密	3	2019-11-29	0
11	iron man	28	199.00	男	3	2019-11-29	0
12	lingo	25	173.00	女	4	2019-11-29	0

--count(*)
--查询表的行数
select count(*) from students;
select count(*) from students where gender=1;
--max(*)
select max(age) from students;
--min(*)
select min(age) from students;
--sum
select sum(age) from students where gender=2;
--avg 默认保留4位小数
select avg(age) from students where gender=2 and is_delete = 0;
--round(123.22,1)四舍五入，1表示保留1位小数
select round(avg(age),1) from students where gender=2 and is_delete=0;

如果你也好奇sum(name)会有什么效果，可以在cmd里面试一下，然后再把几个name改成int试试。

分组

group by

1. group by：将查询结果按照1个或多个字段进行分组，字段值相同的为1组
2. group by 可用于单个字段分组，也可以用于多个字段分组

group by + group_concat()

1. group_concat(‘字段名’)可以作为一个输出字段来使用
2. 表示分组之后根据分组结果，使用group_concat()来放置每一组的某字段的值得集合

** group by + having**

1. having条件表达式,用来分组查询后制定一些条件来输出查询结果
2. having作用和where一样，但having只能用于group by

--按照性别分组，注意查询字段和分组字段应该一致
select gender from students group by gender;

--按照性别和生日分组
select gender,birth from students group by gender,birth;

--计算男生和女生中的人数
select gender as '性别',count(*) as '人数' from students group by gender;

--男女同学最大年龄
select gender,max(age) from students group by gender;

--查询同性别中的姓名
select gender,group_concat(name) from students group by gender;
select gender,group_concat(name,age) from students group by gender;
select gender,group_concat(name,'-',age) from students group by gender;

--查询男女生总数大于2的
select gender,count(*) as '总数' from students group by gender having count(*)>2;

--查询男女生总数大于2的姓名
select gender,count(*),group_concat(name) from students group by gender;

--查询平均年龄超过18岁的性别及姓名
select gender,group_concat(name),avg(age) from students group by gender having avg(age)>22;

排序

order by

select * from 表名 order by 列1 asc(desc),列2 asc(desc);

• 将行数据按照列1进行排序，如果列1的值相同时，则按照列2排序，以此类推
• 默认按照列值从大到小排列(asc)
• asc从小到大排序，即升序
• desc从大到小排序，即降序

--查询年龄在18到26之间的男同学，按照年龄从小到大排序(asc可以不写)
select * from students where (age between 18 and 26) and gender=1 order by asc;

--查询年龄在18到26之间的女同学，身高从高到矮排序
select * from students where age between 18 and 26 order by high desc;

--查询年龄在18到28岁之间的女性，身高从高到排序，如果身高相同的情况下按照年龄从小到大排序
mysql> insert into students values(0,'dream',25,179.99,2,3,default,0);

select * from students where (age between 18 and 28) and gender=2 order by high desc,age;

--按照年龄从小到大，身高从高到矮排序
select * from students order by age,high desc;

分页

当数据量过大时，在一页中查看数据是一件非常痛苦的事儿。

从start开始，获取count行数据

select * from 表名 limit start count;

--limit start count 其实位置从0开始
--查询前5个数据
select * from students limit 5;

--查询id为6-10(包含)的数据(注意我的表没有id为2的)
select * from students limit 5,5;

分页功能可以用于网页的分页，但是用于小体量

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

--第二页
select * from students limit 2,2;

--第三页
select * from students limit 4,2;

--第四页
select * from students limit 6,2;

--查询年龄最小的女同学
select gender,min(age) from students group by gender having gender=2; 

链接查询

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

• 内连接查询：查询的结果为两个表匹配到的数据

• 左连接查询：查询的结果为两个表匹配到的数据，坐标特有的数据，对于右表中不存在的数据使用null填充

• 右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充

select * from 表1 inner/left/right jion 表2 on 表1.列=表2.列

--select ... from 表A inner join 表B,这种查询方式的结果是一种笛卡尔积，用人话来说返回的结果表A*表B，表不大，可以试试。
select * from students inner jion classes;

--查询对应班级的学生及班级名
select * from students as s inner join classes as c on s.cls=c.id;

--基于上述要求的同时，显示students表的全部和classes表的班级名
select s.*,c.name from students as s inner join classes as c on s.cls=c.id;

--以上查询将classes中的姓名放第一列(换一下需求字段顺序即可)
select c.name,s.* from students as s inner join classes as c on s.cls=c.id;

--基于上述条件，要求年龄按照升序排列
select c.name,s.* from students as s join classes as c on s.cls=c.id order by age;

left join

-- 查询每位学生对应的班级信息
select * from students left join classes on students.cls=classes.id;

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

--右连接很少用，因为...在左连接中把两个表的顺序换一下，就是等同于右连接了
select * from classes as c left join students as s on c.id=s.cls;

子查询

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

--查询最高的男生信息
    --最高的男生
    select max(high) from students where gender=1;
    --子查询
    select * from students where high=(select max(high) from students where gender=1) and gender=1;

--查询出高于平均身高的信息
select * from students where high>(select avg(high) from students);

--查询班级号能够对应到的学生信息
    --因为子查询返回结果多于一个值，要用in
select * from students where cls in (select id from students);

子关联

在你填写收获信息的时候，会提示你选择省-市-区，当你选则江西省的后，市选项里不会出现广州市，只会显示江西省下的市，这样一个省级联动如何实现？

• 设计省信息表(province)

  • id
  • provinceid
  • province

• 设计市信息表(citys)

  • id
  • cityid
  • ctitle
  • provinceid

pid 是省标中的id，如广州市的pid应该是广东省的id

• 设计区信息表(areas)
  • id
  • atitle
  • cid

cid 是市表中的id，如海珠区的cid应该是广州市的id

--查询广东省有哪些市
    --1.在province中查询广东省的provinceid
    select provinceid from provinces where province='广东省';
    --2.拿到provinceid后,在根据该id去找在cities表中的市
    select * from cities where provinceid='440000';
    --或是使用子查询语句
    select * from cities where provinceid=(select provinceid from provinces where province='广东省');
    --亦或是内连接
    select * from provinces as p inner join cities as c on p.provinceid=c.provinceid having p.province='广东省';

查找广州市下的区也是同样的原理，但是如果我想要查找县，乡，甚至是具体到某一个街道，那就需要更多的表，这个查询就变得很长，我们能不能把上面3个表融合为一个表？

• 表结构
  • id 中国(1)开始
  • pid 中国(0)/省(1)/市(省id)/区(市id)
  • name 省/市/区的名字

即，省的pid=国的id，市的pid=省的id，区的pid=市的id，区的id自增

--查询'黑龙江'
select * from areas where name='黑龙江';

--查询黑龙江省下的市
select a.name,b.name from areas as a inner join areas as b on a.id=b.pid having name='黑龙江';

--查询哈尔滨市下的区
select * from areas as a inner join areas as b on a.id=b.pid having a.name='哈尔滨';

练习

-- id       新闻ID
-- titile   新闻标题
-- content  新闻内容
-- types    新闻的类型
-- created_at   新闻添加的时间
-- image    新闻的缩略图
-- author   作者
-- is_valid 是否删除

--创建表'news'
create table news(
    id int not null auto_increment,
    title varchar(200) not null,
    content varchar(2000) not null,
    types varchar(10),
    image varchar(300),
    author varchar(20),
    created_at datetime,
    is_valid int default 1,
    primary key(id)
)default charset='utf8';

数据插一半心态崩了，改一下表结构，有兴趣的小伙伴自己慢慢弄吧

alter table news modify content varchar(2000) default '省略2000字';
alter table news modify image varchar(300) default'20191208A046PI00.html';
mysql> alter table news modify author varchar(20) default'tx新闻';
--使用sql语句向数据表写入15条不同数据
insert into news(title,types,created_at) values
('澳门回归20周年','要闻','2019-12-01'),
('四中全会','要闻','2019-12-02'),
('来自一线的稳外资观察','要闻','2019-12-03'),
('新华社评论员','要闻','2019-12-04'),
('绘就美丽中国','要闻','2019-12-05'),
('曼城1-2曼联','体育','2019-12-06'),
('西蒙斯命中职业生涯第二记3分球','体育','2019-12-07'),
('丁俊晖6-2力克颜丙涛','体育','2019-12-08'),
('韦世豪准备留样？','体育','2019-12-09'),
('灰熊vs爵士','体育','2019-12-10'),
('郑爽的鼻子赢了','娱乐','2019-12-11'),
('baby走红毯都抠图','娱乐','2019-12-12'),
('气到动手？','娱乐','2019-12-13'),
('陈小春演唱会','娱乐','2019-12-14'),
('1米85李现','娱乐','2019-12-15')；

--使用sql语句查询类别为体育的新闻数据
select * from news where types='体育';

--使用sql语句删除一条新闻数据
 update news set is_valid=0 where id=12;

--使用sql语句查询所有新闻，以添加实际的倒叙进行排序
select * from news order by created_at desc;

--使用sql语句查询第二页数据（每页5条）
select * from news limit 5,5;