时间加减函数

Mysql时间加减函数为 date_add() 、 date_sub()

定义和用法

DATE_ADD() 函数向日期添加指定的时间间隔。
DATE_SUB() 函数向日期减少指定的时间间隔。

语法

DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)

  • date 参数是合法的日期表达式。
  • expr 参数是您希望添加的时间间隔。

  • type 参数可以是下列值：

    Type 值

  • MICROSECOND 毫秒

  • SECOND 秒
  • MINUTE 分
  • HOUR 时
  • DAY 天
  • WEEK 星期
  • MONTH 月
  • QUARTER 季度
  • YEAR 年
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

例子

MySQL 为日期增加一个时间间隔：date_add()

  1. set @dt = now();
  2. select date_add(@dt, interval 1 day); - 加1天
  3. select date_add(@dt, interval 1 hour); - 加1小时
  4. select date_add(@dt, interval 1 minute); - 加1分钟
  5. select date_add(@dt, interval 1 second); - 加1秒
  6. select date_add(@dt, interval 1 microsecond); - 加1毫秒
  7. select date_add(@dt, interval 1 week); - 加1周
  8. select date_add(@dt, interval 1 month); - 加1月
  9. select date_add(@dt, interval 1 quarter); - 加1季
  10. select date_add(@dt, interval 1 year); - 加1年

例子：更新某个时间，每个时间加上一个星期

  1. UPDATE comment c set c.time = DATE_ADD(c.time, INTERVAL 7 DAY);

MySQL 为日期减去一个时间间隔：date_sub()，格式同date_add（）类似

例子：更新某个时间，使每个时间减少一个月

  1. UPDATE comment c set c.time = DATE_SUB(c.time, INTERVAL 1 MONTH);

字符函数

CONCAT()：字符连接

  1. SELECT CONCAT('IMOOC','-','MySQL');//IMOOC-MySQL
  2. SELECT CONCAT (first_name,last_name) AS fullname FROM test;

CONCAT_WS()：使用指定的分隔符进行字符连接

第一个位置指定分隔符，后面的为分割的内容

  1. SELECT CONCAT_WS('%','abc','def'); //第一个是指定的分隔符；//abc%def

FORMAT()：数字格式化

  1. SELECT FORMAT(123560.75,2); //123,560.75;第二个参数若为0（四舍五入），显示整数

LOWER()：转化小写

UPPER()：转换大写

  1. SELECT UPPER('mysql');//MYSQL

LEFT()：获取左侧字符

RIGHT()：获取右侧字符

  1. SELECT LEFT ('MYSQL',2); //MY
  2. SELECT LOWER(LEFT('mYsql',2)); //函数嵌套；//my

LENGTH()：取得字符串长度

  1. LENGTH('MYSQL ');//6；最后加了个空格

LTRIM()：清空字符串前面（左端）空格

RTRIM()：清空字符串末尾（右端）空格

TRIM()：删除前导、后续空格或者指定字符

  1. SELECT TRIM(' MySQL ');
  2. SELECT TRIM(LEADING '?' FROM '??MYSQL???'); //删除前导？
  3. SELECT TRIM(TRAILING '?' FROM '??MYSQL???'); //删除后续？
  4. SELECT TRIM(BOTH '?' FROM '??MYSQL???'); //前后均删除，但不能删除中间的?

REPLACE()：替换

  1. SELECT REPLACE('MY???SQL','?',''); //将问号换成空白，即去掉？
  2. SELECT REPLACE('MY???SQL','?','~~'); //可以将n个？换成m个组合符号

SUBSTRING()：字符串截取（从1开始，编程里可能从0开始）

  1. SELECT SUBSTRING('mYSQL',1,2); //从字符串中的第一个位置开始截取2个字符mY
  2. SELECT SUBSTRING('mYSQL',3); //SQL 从第3的位置开始截取到结束
  3. SELECT SUBSTRING('mYSQL',-2); //QL 从倒数第2位开始截取到结束
  4. SELECT SUBSTRING('mYSQL',-3,-1); //F，长度不能是负的，但是有些编程语言支持，而mysql不支持

[NOT] LIKE：模式匹配

百分号%代表任意个字符； 下划线_代表任意一个字符

例子：”tom%”

  1. SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1'; // 去除’1’后面的编译，查找包含’%’符号的信息 ‘_’任意一个字符

数值运算符函数

  1. SELECT CEIL(3.01) --==>4; -- 有n.xx 都是n+1 进一取整 向上取整
  2. SELECT FLOOR(3.99) --==>3; -- 取n.xx 都是n 舍一取整 向下取整
  3. SELECT 3 DIV 4 --==> 0 -- 整数除法，取商
  4. SELECT 3/4 --==>0.75 -- 除法
  5. SELECT 2.1 MOD 2 --==>0.1 -- 取余数(取模) 被除数是整数,小数都可以
  6. SELECT 21 % 2 --==>1 -- 取余数 mod=%
  7. SELECT POWER(3,4) --==>81 -- 3的4次方 幂运算
  8. SELECT ROUND(3.1415926,4) --==>3.1416 -- 四舍五入，保留4位小数
  9. SELECT TRUNCATE(123.89,1) --==>123.8 -- 截取小数点后的位数
  10. SELECT TRUNCATE(123.89,0) --==>123 -- 截取小数点后的位置,0位为整数部分
  11. SELECT TRUNCATE(123.89,-1) --==>120 -- 截取-1,从个位起去掉后面的数值替换为0

比较运算符和函数

BETWEEN…AND…

[NOT]BETWEEN…AND…[不]在范围之内，结果返回1或0。

  1. 15 BETWEEN 10 AND 16; -- 1

IN

[NOT]IN()列出值选项。

  1. SELECT 10 IN(5,10,25); -- 1

IS NULL

IS [NOT] NULL：只有NULL IS [NOT] NULL返回1，其他情况（包括”,0）都返回0。

日期时间函数

  1. SELECT NOW(); -- 当前日期时间。
  2. SELECT CURDATE(); -- 当前日期。
  3. SELECT CURTIME(); -- 当前时间。
  4. SELECT DATE_ADD('2014-3-12',INTERVAL [-]365 DAY); -- 2015-03-12，DAY还可以替换为WEEK，MONTH，YEAR等。DATE_ADD不是增加的意思，是变化，既可以增加，也可以减去。
  5. DATEDIFF(); -- 日期的差值。
  6. DATEDIFF('2013-3-12','2014-3-12'); -- -365 日期差值。
  7. DATE_FORMAT(); -- 日期格式化。
  8. SELECT DATE_FORMAT('2014-3-2','%m%d%Y'); -- 03/02/2014（’%’带有前导0作用）。

信息函数

  1. SELECT CONNECTION_ID(); -- ==>2 连接ID
  2. SELECT DATABASE(); -- 当前打开的数据库
  3. SELECT LAST_INSERT_ID(); -- 最后插入的记录 同时写入多条记录是只返回写入的第一条的id。而且表中必须要有自动编号的字段。
  4. DESC tab_name; -- 查看数据库字段及属性，查看表结构
  5. SELECT USER(); -- 打印当前用户信息
  6. SELECT VERSION(); -- 打印当前数据库信息

聚合函数

只有一个返回值

AVG() - 平均值

  1. SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;

COUNT() - 计数

  1. SELECT COUNT(goods_id) as counts FROM tdb_goods;

MAX() - 最大值

  1. SELECT MAX(goods_price) as counts FROM tdb_goods;

MIN() - 最小值
SUM() - 求和

  1. SELECT SUM(goods_price) as counts FROM tdb_goods;

加密函数

MD5()：信息摘要算法

  1. select md5（’admin’）; -- 给admin加密。如果mysql中的信息是为了web开发做准备，那么尽量使用md5

password（）：密码算法

  1. set password=password（'1236'）-- 修改了当前登录密码为1236