like的使用
#like的使用语法,where后先写查询列再写like,最后写匹配字符串
SELECT * FROM student where userName like '杨_';
SELECT * FROM student where userName like '杨%';
SELECT * FROM student where introduce like '%大%';
#模糊查询基本上所有后台管理系统都会有此功能
select * from student where pwd is not null;
#范围查询,between and 是包含==的,既可以查询数值范围,也可以查询时间范围,用途广泛。
select * from student where age between 22 and 30;
select * from student where createDate BETWEEN'2024-02-21 00:00:00' and '202-02-25 00:00:00';
#in 关键词的使用,不建议使用这个关键字,因为它不一定走索引,在百万表的数据中会很卡
select * from student where userName in ('赵灵儿','杨过','龙姑娘');
聚合函数的应用(MySQL)
#聚合函数5个,数量count(x),最大值max(x),最小值min(X),平均数avg(x),求和sum(x)
select *count(*)'所有成年人数量' from student where age>=18;
select max(age) '最大年龄',min(age) '最小年龄' from student;
select avg(age)'平均年龄'from student;
select sum(age)'总年龄' from student;
select avg(age)'avg出的总年龄',sum(age)/(select count(*) from student)'计算出的' from student;