MySQL系列三：函数&索引&视图&错误代码编号含义

Hi ~o(*￣▽￣*)ブ 我是【小阿飞_】😜

本期为大家带来mySQL系列文章之三：MySQL常用函数&索引&视图

本期精彩：先讲概念再讲例题，带大家认识了解并且使用MySQL中的常用函数、索引、视图以及常见的报错提示

常用函数

日期函数

字符串函数

数字函数

聚合函数

合并

视图与索引

索引

常见错误代码编号的意思

---

常用函数

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

日期函数

mysql中常见的日期函数（含义+使用示例）

1、NOW()

• 获取当前日期
• SELECT NOW();

2、DAY()

• 获取指定日期的日部分
• SELECT DAY(NOW());

3、MONTH()

• 获取指定日期的月部分
• SELECT MONTH(NOW());

4、YEAR()

• 获取当前年份
• SELECT YEAR(NOW());

5、DATE_FORMAT()

• 将日期按指定的格式转换为字符串
• SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

6、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;

 注：convert(expr,转换类型)👇

• 作用：类型转换函数， 第一个参数expr为需要转换的内容，第二个参数为转换为什么类型
• 示例：SELECT CONVERT('0983', SIGNED);
• 转换类型：SIGNED 整数、UNSIGNED 无符号整数、DECIMAL  浮点数、DATETIME 日期时间、TIME  时间、DATE  日期
• if(condition,满足条件的结果,不满足条件的结果)

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

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

字符串函数

mysql中常见的字符串函数（含义+使用示例）

1、UPPER()

• 转换成大写字符
• SELECT UPPER('faafafa')

2、LOWER()

• 转换成小写字符
• SELECT LOWER('FEFEFF')

3、REPLACE()

• 搜索并替换字符串中的子字符串
• SELECT REPLACE('www.google.net','w','n')SUBSTRING()
• 从具有特定长度的位置开始的最一个子字符串
• SELECT SUBSTRING('abcdefghijk', 1, 3);
• SELECT SUBSTRING('abcdefghijk', 4);
• SELECT SUBSTRING('abcdefghijk', -3);

4、TRIM()

• 去除首尾空格
• SELECT TRIM('    fdfdfdfd    ');

5、LENGTH()

• 获取字符串长度
• SELECT LENGTH('abcdef');

数字函数

mysql中常见的数字函数（含义+使用示例）

1、FLOOR()

• 向下取整
• SELECT FLOOR(123.8934);

2、CEIL()

• 向上取整
• SELECT CEIL(123.8934)

3、ROUND()

• 四舍五入
• SELECT ROUND(123.8934, 2);

聚合函数

mysql中常见的聚合函数（含义+使用示例）

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

常用👇

• SUM：求和
• COUNT：统计记录数
• AVG：求平均值
• MAX：求最大值
• MIN：求最小值

合并

关键字👇

• UNION将所有的查询结果放在一起，并去掉相同的记录
• UNION ALL将所有的查询结果合并在一起，不去掉相同的记录(用的多)

合并的前提

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

SELECT sid FROM t_score WHERE cid = 1
UNION
SELECT sid FROM t_score WHERE cid = 2
-- 注意此处去掉了重复的值，可以与下面的语句执行结果比较
SELECT sid FROM t_score WHERE cid = 1
UNION ALL
SELECT sid FROM t_score WHERE cid = 2
​

视图与索引

概念及特点

• 视图是一种虚拟表，是从数据库中的一个或多个表中导出的表
• 数据库中存放视图的定义，而不会存放视图的数据，数据依然存放在原来的表中
• 使用视图查询数据时，数据库会从原来的表中获取数据

（注意：此处并没有包括物化视图，目前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

索引

概念

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

优缺点

• 对于中型或大型表，恰当的使用索引可以显著提高查询性能
• 增加了索引维护的工作，使的插入，修改，删除操作变慢

分类

1. 普通索引：基本索引，目的就是提高查询性能
2. 唯一索引：除提高查询性能外，还可起到避免列值出现重复
3. 主键索引：特殊的唯一索引，一个表只能有一个主键，不允许有空值
4. 组合索引：由多列组合生成的索引，使用是需要注意索引顺序
5. 全文索引：用于支持全文搜索（FULLTEXT）

索引维护

  1、创建

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

  2、修改

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

  3、删除

DROP INDEX 索引名 ON 表名
DROP INDEX birthday_inx ON t_student;

常见错误代码编号的意思

1. 1075有自增键，但不是未将其设置为主键
2. 1142：操作被拒绝，一般是没有权限
3. 1064：一般是存在语法错误，如关键字错误，缺少空格，sql脚本中sql语句后缺少;等原因
4. 1048：列不能为空
5. 1055：不在GROUP BY中
6. 1265：保存数据的格式与定义是不同
7. 1366：数据编码
8. 1451：违反外键约束

感谢阅读：本期关于mySQL相关常识知识的分享到这里就结束啦，关注[小阿飞_]，下期带你更加深入了解mySQL(●'◡'●)