mysql常用函数与视图索引全面梳理

Phoebe ·

更新时间:2024-11-13

· 1351 次阅读

常用函数

几种函数类型

1、日期函数

2、字符串函数

3、数字函数

4、聚合函数

合并

视图索引

视图

概念及特点

视图作用

基本语法

索引

概念

优缺点

分类

索引维护

常见的错误代码

常用函数

函数的概念及特点：

与java，js中的函数概念一致

特点：数据库函数必须要有返回值（一行一列）

几种函数类型 1、日期函数

now() 获取当前日期；

示例：selectnow();

day() 获取指定日期的日部分；

示例：select day(now());

month() 获取指定日期的月部分；

示例：select month(now());

year()获取指定日期的月部分；

示例：selectyear(now());

date_format() 将日期按指定的格式转换为字符串;

示例：selectdate_format(now(), '%Y-%m-%d %H:%i:%s');

str_to_date()：将特定格式的日期转换成日期；

示例：select str_to_date('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s');

综合示例：

1）直接按年份计算学生年龄
    SELECT t.sname, YEAR(NOW()) - YEAR(t.birthday) FROM t_student t
2）按照出生日期来算，当前月日 < 出生年月的月日则（说明月份还没到），年龄减一
SELECT sid,sname,birthday,YEAR(NOW())-YEAR(birthday) '年龄',
       YEAR(NOW())-YEAR(birthday) + IF(CONVERT(DATE_FORMAT(NOW(),'%m%d'),SIGNED)-CONVERT(DATE_FORMAT(birthday,'%m%d'),SIGNED)<0,-1,0) '真实年龄'
FROM t_student;
3）查询本月过生日的学生信息
SELECT * FROM t_student t WHERE MONTH(NOW()) - MONTH(t.birthday) = 0;

2)示例运行结果如下：

2、字符串函数

upper() 转换成大写字符

示例：select upper('faafafa')

lower() 转换成小写字符

示例：Sselect lower('FEFEFF')

replace() 搜索并替换字符串中的子字符串

示例：select replace('www.google.net','w','n')

substring() 从具有特定长度的位置开始的最一个子字符串

示例：

select substring('abcdefghijk', 1, 3)

select substring(('abcdefghijk', 4);

select substring(('abcdefghijk', -3);

trim() 去除首尾空格

示例：select trim(' fdfdfdfd ');

length() 获取字符串长度

示例：select length('abcdef');

3、数字函数

floor()向下取整

示例：select floor(123.8934);

ceil() 向上取整

示例：select ceil(123.8934)；

round() 四舍五入

示例：select round(123.8934, 2);

4、聚合函数

特点：常用group by一同使用，也可单独使用，如需求过滤，可以使用having子句

SUM 求和

COUNT 统计记录数

AVG 求平均值

MAX 求最大值

MIN 求最小值

合并

关键字：

union 将所有的查询结果放在一起，并去掉相同的记录

union all将所有的查询结果合并在一起，不去掉相同的记录

合并的前提：结果集列数个数相同

使用场景：在项目统计报表模块，用来合并数据

select 'abc', 123 
union
selet 'def',456
select 'abc', 123
union 
select 'abc', 123
select sid fromt_score where cid = 1
union
select sid from t_score where cid = 2
# 注意此处去掉了重复的值，可以与下面的语句执行结果比较
select sid fromt_score where cid = 1
union all
select sid from t_score where cid = 2
select 'abc', 123
union all
select 'def', 456
select 'abc', 123
union all
select 'abc', 123

视图索引 视图 概念及特点

视图是一种虚拟表，是从数据库中的一个或多个表中导出的表

数据库中存放视图的定义，而不会存放视图的数据，数据依然存放在原来的表中。使用视图查询数据时，数据库会从原来的表中获取数据（注意：此处并没有包括物化视图，目前mysql默认不支持物化视图）

视图作用

1）简化操作

2）增加数据的安全性

3）提高表的逻辑独立性

基本语法

createview 视图名 as select 语句

示例：

create view stu_score_statistics as
select t1.sid, t1.sname, t1.ssex, t2.courses, t3.total total_score from t_student t1 
left join (select sid, count(*) courses from t_score group by sid) t2 on t1.sid=t2.sid
left join (select sid, sum(score) total from t_score group by sid) t3 on t1.sid=t3.sid

索引概念

索引是由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度

可以理解为书本的目录的作用

优缺点

优点：对于中型或大型表，恰当的使用索引可以显著提高查询性能

缺点：增加了索引维护的工作，使的插入，修改，删除操作变慢

分类

普通索引（基本索引，目的就是提高查询性能）

唯一索引（除提高查询性能外，还可起到避免列值出现重复）

主键索引（特殊的唯一索引，一个表只能有一个主键，不允许有空值）

组合索引（多列组合生成的索引，使用是需要注意索引顺序）

全文索引（用于支持全文搜索（FULLTEXT））