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

目录

常用函数

函数的概念及特点:

与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))

索引维护

创建

语法

CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名[(长度)][ASC|DESC])

示例

CREATE INDEX sname_inx ON t_student(sname);

修改

语法

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT] INDEX 索引名(字段名[(长度)][ASC|DESC])

示例

ALTER TABLE t_student ADD INDEX birthday_inx(birthday);

删除

语法

DROP INDEX 索引名 ON 表名

示例

DROP INDEX birthday_inx ON t_student;

常见的错误代码

1075

有自增键,但不是未将其设置为主键

1142

操作被拒绝,一般是没有权限

1064

一般是存在语法错误,如关键字错误,缺少空格,sql脚本中sql语句后缺少;等原因

1048

列不能为空

1055

不在GROUP BY中

1265

保存数据的格式与定义是不同

1366

数据编码

1451

违反外键约束

原文地址:https://blog.csdn.net/qq_64001795/article/details/126233472
19人参与, 0条评论 登录后显示评论回复

你需要登录后才能评论 登录/ 注册