mySQL基本函数

目录

一、函数概念及特点

二、日期函数

三、字符串函数

四、数字函数

五、聚合函数

 六、合并

七、视图与索引

1、视图

2、索引

八、常见错误代码

---

一、函数概念及特点

概念：与java、js中的函数概念一致

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

二、日期函数

函数名	作用	示例
NOW()	获取当前日期	SELECT NOW();
DAY()	获取指定日期的日部分	SELECT DAY(NOW());
MONTH()	获取指定日期的月部分	SELECT MONTH(NOW());
YEAR()	获取指定日期的年部分	SELECT YEAR(NOW());
DATE_FORMAT()	将日期按指定的格式转换为字符串	SELECT DATE_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;

说明：

示例2）用到的convert(expr,转换类型)为类型转换函数， 第一个参数expr为需要转换的内容，第二个参数为转换为什么类型。convert示例如下：

SELECT CONVERT('0983', SIGNED);

转换类型有：

• SIGNED 整数
• UNSIGNED 无符号整数
• DECIMAL  浮点数
• DATETIME 日期时间
• TIME  时间
• DATE  日期

3）查询本月过生日的学生信息

SELECT * FROM t_student t WHERE MONTH(NOW()) - MONTH(t.birthday) = 0;

三、字符串函数

函数名	作用	示例
UPPER()	转换成大写字符	SELECT UPPER('faafafa')//FAAFAFA
LOWER()	转换成小写字符	SELECT LOWER('FEFEFF')//fefeff
REPLACE()	搜索并替换字符串中的子字符串	SELECT REPLACE('www.google.net','w','n') //nnn.google.net
SUBSTRING()	从具有特定长度的位置开始的最一个子字符串	SELECT SUBSTRING('abcdefghijk', 1, 3)//abc SELECT SUBSTRING('abcdefghijk', 4);//defghijk SELECT SUBSTRING('abcdefghijk', -3);//ijk
TRIM()	去除首尾空格	SELECT TRIM('    fdfdfdfd    ');//fdfdfdfd
LENGTH()	获取字符串长度	SELECT LENGTH('abcdef');//6

四、数字函数

函数名	作用	示例
FLOOR()	向下取整	SELECT FLOOR(123.8934);//123
CEIL()	向上取整	SELECT CEIL(123.8934)//124
ROUND()	四舍五入	SELECT ROUND(123.8934, 2);//123.89

五、聚合函数

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

常见函数：

sum：求和

count：统计记录数

avg：求平均数

max：求最大值

min：求最小值

 六、合并

• 关键字：

关键字	作用
UNION	将所有的查询结果放在一起，并去掉相同的记录
UNION ALL	将所有的查询结果合并在一起，不去掉相同的记录

• 合并的前提：

结果集列数个数相同，使用场景在项目统计报表模块，用来合并数据。

UNION示例：

第一种情况：没有重复值

SELECT 'abc', 123
UNION 
SELECT 'def',456

运行结果如下： 

  

第二种情况：有重复值

SELECT 'abc', 123
UNION 
SELECT 'abc', 123

运行结果如下：  

 

 通过对比可以看出将所有的查询结果放在一起，会去掉相同的记录

UNION ALL示例：

第一种情况：没有重复值

SELECT 'abc', 123
UNION ALL
SELECT 'def', 456

运行结果如下：   

第二种情况：有重复值 

SELECT 'abc', 123
UNION ALL
SELECT 'abc', 123

运行结果如下：

    

 通过对比可以看出将所有的查询结果放在一起，不会去掉相同的记录 

七、视图与索引

1、视图

• 概念及特点

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

•  视图的作用

1. 简化操作
2. 增加数据的安全性
3. 提高表的逻辑独立性

• 基本语法

CREATE VIEW 视图名 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
  
  SELECT*FROM stu_score_statistics

2、索引

• 概念

索引是由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度（可以理解为书本的目录的作用）

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

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

• 分类

名称	描述
普通索引	基本索引，目的就是提高查询性能
唯一索引	除提高查询性能外，还可起到避免列值出现重复
主键索引	特殊的唯一索引，一个表只能有一个主键，不允许有空值
组合索引	由多列组合生成的索引，使用是需要注意索引顺序
全文索引	用于支持全文搜索（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	违反外键约束