当前位置 : 主页 > 数据库 > mysql >

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

来源:互联网 收集:自由互联 发布时间:2022-12-24
目录 常用函数 几种函数类型 1、日期函数 2、字符串函数 3、数字函数 4、聚合函数 合并 视图索引 视图 概念及特点 视图作用 基本语法 索引 概念 优缺点 分类 索引维护 常见的错误代码
目录
  • 常用函数
    • 几种函数类型
      • 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))

      索引维护

      创建

      语法

      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

      违反外键约束

      到此这篇关于mysql常用函数与视图索引全面梳理的文章就介绍到这了,更多相关mysql常用函数内容请搜索自由互联以前的文章或继续浏览下面的相关文章希望大家以后多多支持自由互联!

      上一篇:JDK1.7下测试ConnectorJ连接MySQL8.0的方法
      下一篇:没有了
      网友评论