MySQL学习笔记之基础操作(二) - 海筒研习社.SeaSilo.CN

/ 0评 / 0

1. 表字段的操作

2. 时间类型数据

3. 高级查询语句

4. 聚合操作

聚合操作指的是在数据查找基础上对数据的进一步整理筛选行为,实际上聚合操作也属于数据筛选范围。

5. 索引操作

概念

2. 时间类型数据

3. 高级查询语句

4. 聚合操作

聚合操作指的是在数据查找基础上对数据的进一步整理筛选行为,实际上聚合操作也属于数据筛选范围。

5. 索引操作

概念

​查找练习
1. 查找所有蜀国人信息,按照攻击力排名
select * from sanguo where country="蜀" order by attack desc;
2. 将赵云攻击力设置为360,防御设置为70
update sanguo set defense=70 where name="赵云";
3. 吴国英雄攻击力超过300的改为300,最多改2个
update sanguo set attack=300 where country="吴" and attack>300 limit 2;
4. 查找攻击力超过200的魏国英雄名字和攻击力并显示为姓名, 攻击力
select name as 姓名,attack as 攻击力 from sanguo where attack >200 and country="魏";
5. 所有英雄按照攻击力降序排序,如果相同则按照防御生序排序
select * from sanguo order by attack desc ,defense;
6. 查找名字为3字的
select * from sanguo where name like "___";
7. 查找攻击力比魏国最高攻击力的人还要高的蜀国英雄
select * from sanguo where country="蜀" and attack >(select attack from sanguo where country="魏" order by attack desc limit 1) ;
8. 找到魏国防御力排名2-3名的英雄
select * from sanguo where country="魏" order by defense desc limit 2 offset 1;
9. 查找所有女性角色中攻击力大于180的和男性中攻击力小于250的
select * from sanguo where gender="女" and attack >180 union select * from sanguo where gender="男" and attack <250;

select country as 国家,avg(attack) as 平均攻击力 from sanguo where gender="男" group by country order by avg(attack) desc;
select country,gender,count(*),sum(attack) from sanguo group by country,gender;
having对聚合函数结果进行筛选:
select country,gender,count(*),avg(attack) from sanguo group by country,gender having avg(attack)>=300;

聚合练习
​use books;
1. 统计每位作家出版图书的平均价格
select author,avg(price) from book group by author;
2. 统计每个出版社出版图书数量
select press,count(*) from book group by press;
3. 查看总共有多少个出版社
select count(distinct press) from book;
4. 筛选出那些出版过超过50元图书的出版社,并按照其出版图书的平均价格降序排序
select press as 出版社,count(*) as 数量,avg(price) as 均价 from book
where press in (select press from book where price >50)
group by press order by avg(price) desc;

select press as 出版社,count(*),avg(price) as 均价 from book
group by press
having max(price) >50
order by avg(price) desc;
5. 统计同一时间出版图书的最高价格和最低价格
select press_date,max(price),min(price) from book group by press_date;

发表评论

邮箱地址不会被公开。 必填项已用*标注