MySQL5.30
2022/5/31 2:20:02
本文主要是介绍MySQL5.30,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
add:
指的是最小2个以及2个以上的条件才满足要求
select * from employees where first_name="Parto"and gender="M" limit 5;
or:
2个或者2个以上满足其中一个条件即可
select * from employees where first_name="Chirstian"or last_name="Speek" limit 5;
大于
select * from salaries where salary>60117 limit 5;
大于等于
select * from salaries where salary>=60117 limit 5;
小于
select * from salaries where salary<60117 limit 5;
小于等于
select * from salaries where salary<=60117 limit 5;
不等于
select * from salaries where salary!=60117 limit 5;
等于
select * from salaries where salary=60117 limit 5;
in:包含
任意一种即可
select * from employees where first_name in ("Georgi","Parto") limit 5;
between...and
查询范围,包含开始也包含结尾
select * from salaries where salary between 60117 and 60118 limit 5;
not
not in
select * from salaries where salary not in (60117,60118) limit 5;
not between...and
select * from salaries where salary not between 60117 and 60118 limit 5;
正则
%:匹配任意字符
可以放在任意位置
select * from employees where first_name like "%gi"limit 5;
select * from employees where first_name like "Ch%an" limit 5;
select * from employees where first_name like "Ch%" limit 5;
^:以......为开头
rlike:正则模式
select * from employees where first_name rlike "^Ch" limit 5;
$:以......为结尾
select * from employees where first_name rlike "an$" limit 5;
as:别名
select first_name as 首名 from employees limit 5;
函数:实现某一个特定的功能
max():最大
select max(score) as 最高分 from student;
min():最小
select min(score)as 最低分 from student;
avg():平均分
select avg(score)as 平均分 from student;
sum():总数
select sum(score)as 总分 from student;
order by:排序
默认排序:由小到大
select * from student order by score;
asc:顺序排序
由小到大
select * from student order by score asc;
desc:倒序排序
由大到小
distinct:去重
select distinct name from student;
select distinct * from student;
聚合函数
group by
例题1:对employees表中的gender进行分组,然后对每组进行统计
select gender,count(gender)from employees group by gender limit 10;
例题2:查询出以年龄取出平均分 过滤出平均分大于50 并且,以平均分进行倒序排序
select age,avg(score) as 平均分 from student group by age having 平均分>50 order by 平均分 desc;
理解
1、取出不同年龄段的平均分
2、在这个不同年龄段平均分的基础上过滤出>50的
3、再对这些大于50的平均分做一个倒序排序
内连接:
inner join
查询2个或者2个以上 查询他们交集(共同拥有)的数据
例题1:
查询出店铺名称 商品名称 商品类型 商品价格
select shop_name,good_name,good_type,price from shop inner join goods on shop.id=goods.shop_id;
例题2:查询出店铺名称,商品名称,商品类型,价格,还有快递名称,电话
select shop_name,good_name,good_type,price,name,phone from shop inner join goods on shop.id=goods.shop_id inner join logistic on goods.id=logistic.good_id;
例题3:查询商品目前有多少个
例题4:
1、查询shop,goods表的所有字段
2、以商品的价格作为倒序排序
select * from shop inner join goods on shop.id=goods.shop_id order by goods.price desc;
having与where的区别:
1. having只能⽤于group by(分组统计语句中)
2. where 是⽤于在初始表中筛选查询,having⽤于在where和group by 结果分组中查询
3. having ⼦句中的每⼀个元素也必须出现在select列表中
4. having语句可以使⽤聚合函数,⽽where不使⽤。
这篇关于MySQL5.30的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-10-23BinLog入门:新手必读的MySQL二进制日志指南
- 2024-10-23Binlog入门:MySQL数据库的日志管理指南
- 2024-10-22MySQL数据库入门教程:从安装到基本操作
- 2024-10-22MySQL读写分离入门教程:轻松实现数据库性能提升
- 2024-10-22MySQL分库分表入门教程
- 2024-10-22MySQL慢查询的诊断与优化指南
- 2024-10-22MySQL索引入门教程:快速理解与应用指南
- 2024-10-22MySQL基础入门教程:从安装到基本操作
- 2024-10-22MySQL数据库中的Binlog详解与操作教程
- 2024-10-12部署MySQL集群项目实战:新手入门教程