MySQL--查询和常用函数(知识点)

1.查询

1.1 查询语法:

select 显示的字段列表 from 表名 where 条件 GROUP BY 分组 having 条件 limit 开始记录，条数 order by 排序字段 desc降序|asc升序

10.1.1 全查询

语法: select 显示的字段列表 from 表名 全查询

10.1.2 条件查询(查询部分行)

语法: select 显示的字段列表 from 表名 where 条件

###查询2005-2010 年之间的女生信息
select * from student where sex='女' and bormDate >= '2005-01-01 00:00:00' and bormDate <= '2010-12-31 23:59:59';

10.1.3 查询部分列

语法: select 列1，列2，...，列n from 表名

select studentName,phone,address from student

select studentName,sex,phone from student where sex='女' and bormDate >= '2005-01-01 00:00:00' and bormDate <= '2010-12-31 23:59:59';

where后面的条件控制的是显示多少行，select后面的列名列表控制的是要显示多少列，所有列用* 代替。

10.1.4 查询部分列和行

语法: select 列1，列2,...，列n from 表名 where 条件

select studentName,phone,address from student where studentNo<30000

10.1.5 对列取别名

语法: select 列1 as 别名1，列2 as 别名2，...，列n as 别名n from 表名

select studentName as 姓名,sex as 性别,phone as 电话 from student where sex='女' and bormDate >= '2005-01-01 00:00:00' and bormDate <= '2010-12-31 23:59:59';

10.1.6 查询空值数据.

select * from student where identityCard  is null;  #查询空值的数据
select * from student where identityCard  is not null;  #查询非空值的数据

10.1.7 常量列

select '常量值' as 列名 from 表名;

select * ,'重庆市渝北区人和' as 详细地址   from student

1.2 查询中的各种子句

1) 排序

语法: select * from 表名 order by 排序字段 asc|desc

案例： select * from student order by bormDate desc

2) 分页

语法: select * from 表名 limit 起始记录位置，数量

案例: select * from student limit 0,3

pageindex是当前的页码

pagecount是每页显示多少条记录

分页公式: select * from student limit (pageindex-1)*pagecount , pagecount

3) in和not in

问题: 查询出21,24,27,30的学生信息

笨办法: select * from student where studentNo=21 or studentNo=24 or studentNo=27 or studentNo=30

使用in: select * from student where studentNo in(21,24,27,30);

in的作用就是当前的值在某一个集合中存在就为真

not in 的作就是当in为真的时候进行非运算

问题：查询不是21,24,27,30的学生信息

案例： select * from student where studentNo not in(21,24,27,30);

4) between 值1 and 值2

是一种范围判断

##问题: 查询出25到30的学生信息

select * from student where studentNo between 25 and 30

##问题，查询出2000年到2010年之间出生的学生信息

select * from student where bormDate between '2000-01-01' and '2010-12-31'

5) like 模糊查询

语法 : select * from 表名 where 模糊查询的字段名 like '查询规则';

通配符:

%代表匹配0个或多个字符

_代表匹配一个字符

#问题: 查询出所有姓张的同学

案例: select * from student where studentName like '张_%'

案例: select * from student where studentName like '%老%' 6) case...when...then...end

语法:

case

when 条件1 then 值1

when 条件2 then 值2

...

end

问题，将成绩表中的成绩换成等级显示出来

案例:

select *,case
when studentResult between 90 and 100 then 'A'
when studentResult between 80 and 89 then 'B'
when studentResult between 70 and 79 then 'C'
when studentResult between 60 and 69 then 'D'
when studentResult between 0 and 59 then 'E'
end as '等级'
from result

常用函数

1.1 聚合函数

统计:

统计数量: count(字段名)

select count(*) from student; #效率最慢 这是对所有的字段进行统计得出来的结果 select count(studentNo) from student; # 其次 这是对一个字段进行统计得出来的结果 select count(1) from student; # 效率最高 这是靠自身迭代的次数进行的统计

最大值: max(字段名)

select max(studentNo) from student;

最小值: min(字段名)

select min(studentNo) from student;

总数: sum(字段名)

select sum(studentResult) from result;

平均数: avg(字段名)

select avg(studentResult) from result;

可以使用条件来进行精细化的统计:

#求21这名同学的总分

select sum(studentResult) from result where studentNo=21;

#求课程为1的最高分

select max(studentResult) from result where subjectNo=1;

#统计各科目的总分

分组 group by, 如果要和其他字段一起显示需要进行分组，表示对不同的数据进行分组后的一个统计操作

按科目进行分组统计

select subjectNo,sum(studentResult) from result group by subjectNo;

#统计各科目不同考试场次的总分，这里就要按两个字段进行分组

select subjectNo,examDate,sum(studentResult) from result group by subjectNo,examDate;

#查询科目编号为9并且不同的场次考试总分小于500分的记录

条件 having:

为什么最后的总分小于500分的记录条件不能写在where中.

where 只能对没有分组的数据进行筛选(原数据)，having能对分组后的数据进行筛选

select examDate,sum(studentResult) as sumres from result where subjectNo=9 group by examDate having sumres<500;

当where和having同时在一条sql语句中存在的时候

执行顺序是where->group by ->having

1.2 地理位置函数

lng经度

lat 纬度

point(lng,lat)--> 描述成为一个点

st_distance--> 对两个点进行计算

st_distance(point(log,lat),point(106.485737,29.531308))这个结果要乘以111195 得到的数据就是两点之间的直接距离，单位是米

所以可以用它来进行数据定位的查询

select *,(st_distance(point(log,lat),point(106.485737,29.531308))*111195) distance from business having distance<=3000;

注意这个距离是计算出来的一个值，所以数据条件要用having来筛选。

1.3 字符串函数

substring(字符串，开始位置，长度)

select substring("hello重庆world",6,2);

concat(字符串1，字符串2，...字符串n)

select concat("hello","-","w","orld");

案例:

select studentNo,studentName,sex, concat(substring(phone,1,3), '',substring(phone,8,4)) as phone , address from student;

lower(字符串) 大写转小写

select lower('HELLOWORLD')

upper(字符串)小写转大写

select upper('helloworld')

length(字符串)获取长度

select length('helloworld')

trim(字符串)删除前后空格

select length(trim(' helloworld '))

locate(要查找的字符串,原字符串)查找字符串所在位置

select locate('w','helloworld') 返回结果为6，数据库中下标从1开始,找不到返回0

1.4 日期函数

curdate() 返回当前日期

curtime() 返回当前时间

now() 返回当前日期时间

案例:

select curdate() select curtime() select now()

week(日期) 返回指定日期是一年中的第几周

year(日期) 返回指定日期中的年份

MONTH(日期) 返回指定日期中的月份

day(日期) 返回指定日期中的号数

HOUR(日期) 返回指定日期中的小时数

MINUTE(日期) 返回指定日期中的分钟

second(日期时间) 返回指定日期时间中的秒数

案例:

select week(now()) select year(now()) select month(now()) select day(now()) select hour(now()) select minute(now()) select second(now())

datediff(日期1，日期2) 计算两个日期之间的天数

adddate(日期，天数) 为指定的日期添加天数

select datediff(now(),'1989-09-07') select adddate(now(),10)

DAYOFWEEK(日期) 返回指定日期是星期几 1代表星期天 ---7表示星期六

select dayofweek('2022-09-11')

1.5 数学函数

rand() 随机产生0-1之间的小数

select rand()

ceil(数字) 向上取整

select ceil(3.14)-->4

floor(数字) 向下取整

select floor(3.14)--->3

生成一个10-100之间的随机数，包含10和100

select floor(rand()*91+10)

round() 四舍五入

select round(3.6);