MySQL 教程 - 08 - 运算符和函数

字符函数

(1) concat( ‘xxx’ , ‘xxx’) 可以用来连接字符串.

注意: 大部分的 数据库使用 ‘+’ 或者 ‘||’ 来连接字符串, 但是 MySql 使用 Concat() 函数.

    SELECT CONCAT('imooc','MySQL');

    CONCAT('imooc','MYSQL')        -- 列名, 其他的例子中我就不再写出来了.
    imoocMySQL                     -- 结果

    SELECT CONCAT('imooc','-','MySQL');
    imooc-MySQL

    SELECT concat( username , ' 是 ' , address , '人!' ) FROM student WHERE id=2;

    李四 是 北京人!

(2) concat_ws ( 连接符, 字符1, 字符2, 字符3, …… ) 连接多个字符

SELECT CONCAT_WS(':','A','B','C');    A:B:C

(3) format( 数字 , 小数点位数 ) 数字格式化

    SELECT FORMAT(12560.75.2);    12,560.75
    SELECT FORMAT(12560.75,1);    12,560.8
    SELECT FORMAT(12560.75,0);    12,561

(4) lower() / upper() 大小写转化, 全部转化

    SELECT LOWER('MySq1');    mysq1
    SELECT UPPER('MySq1');    MYSQL

(5) left() / right ( 要截取的字符串 , 要截取的字符个数 ) 截取字符串.

    SELECT LEFT('MySql'.2);     My
    SELECT RIGHT('MySq1',2);    ql

(6) length() 获取字符串长度, 包含空格.

    SELECT LENGTH('MYSQL');      5
    SELECT LENGTH('MY S QL');    7

(7) ltrim() 删除第一个字符前的空格.
rtrim() 删除最后一个字符后的空格.

    SELECT LTRIM(' MY S QL');    MYS QL

trim()     还可以删除某些特定的字符串.
删除前导 ?  使用 LEADING
删除后导 ?  使用 TRAILING
同时删除前导和后导 ? 使用 BOTH

    SELECT TRIM(LEADING '?' FROM '??My?SQL?');    My?SQL?
    SELECT TRIM(TRAILING '?' FROM '??My?SQL?');    ??My?SQL
    SELECT TRIM(BOTH '?' FROM '??My?SQL?')；         My?SQL

(8) replace( 要替换的字符串 , 被替换的字符 / 旧字符 , 替换的字符 / 新字符 ) 字符串替换.

    SELECT REPLACE('??MY?SQL?','?','');    MYSQL
    SELECT REPLACE('??My?SQL?','?','/*');  /*/*My/*SQL/*
    SELECT REPLACE('/*/*/*MYS*/QL/*'，'/*','');    MYS*/QL

(9) substring( 要截取的字符串 , 向右截取的起始位数 , 截取的个数 ) 截取字符串

    -- MYSQL 中字符串的下标从 1 开始, 不同于其他编程语言中的从 0 开始.
    SELECT SUBSTRING('MYSQL',1,2);    MY

    -- 截取的起始位数为负. -3表示从右向左数3位, 2 表示向后转截取2位.
    SELECT SUBSTRING('MYSQL',-3,2);    SQ

    -- 缺少要截取的个数.默认截取到结尾.  注意: 截取的个数必须>=0.
    SELECT SUBSTRING('MYSQL',-3);    SQL

    -- 截取的起始位数大于字符串长度.则截取到空字符.
    SELECT SUBSTRING('MYSQL',10,2);    ''

数值运算符和函数

    -- CEIL 向上取整.
    SELECT ceil(3.01);    4
    --  FLOOR 向下取整.
    SELECT floor(3.99);    3
    -- ROUND(数, 保留几位小数) 四舍五入
    SELECT round(3.141592,2);    3.14
    SELECT round(3.141592,0);    3
    -- TRUNCATE()  将数置为0
    SELECT TRUNCATE(125.89,1);    125.8
    SELECT TRUNCATE(125.89,0);    125
    SELECT TRUNCATE(125.89.-1);    120
    SELECT TRUNCATE(125.89.-2);    100

    -- 普通除法
    SELECT 3/4;    0.7500
    -- DIV  整数除法
    SELECT 3 div 4;    0

    -- MOD 求余
    SELECT 3 mod 4;    3
    SELECT 5.3 mod 4;    1.3

    -- POWER(数, 次方)    求幂
    SELECT power(5,2);    25

比较运算符和函数

    -- BETWEEN .. AND..     闭合区间
    SELECT 2 BETWEEN 1 AND 2;    1

    -- IN()  点
    SELECT 10 IN(5,10,15);    1
    SELECT 13 IN(5,10,15);    0

    -- IS NULL
    SELECT AMLLL IS NMULL;    1
    SELECT 0 1S NULL;    0
    SELECT '' IS NULL;    0
    -- 查找电话为空的用户
    SELECT  *  FROM users WHERE  tel  IS NULL;

日期时间函数

    -- NOW()    查询当前的日期和时间
    SELECT now();    2017-01-18 21:19:33

    -- CURDATE()    只查询当前的日期
    SELECT curdate();    2017-01-18

    -- CURTIME()    只查询当前时间
    SELECT curtime();    21:19:33

    -- DATE_ADD(时间, 要增加或者减少的日期或时间)    日期时间的调整
    -- 明年的这时候
    SELECT date_add(NOW(),INTERUAL 365 DAY);    2018-01-18 21:32:11
    -- 去年的这时候
    SELECT date_add(NOW(),INTERUAL -365 DAY);    2016-01-18 21:32:11
    -- 时间加 1 年
    SELECT date_add(NOW(),INTERUAL 1 YEAR);
    -- 时间加 3 周
    SELECT date_add(NOW(),INTERUAL 3 WEEK);

    -- DATEDIFF(时间A,时间B)    查询两个时间的差值
    SELECT datediff('2013-03-12',NOW());    -1408

    -- DATE_FORMAT(时间, 格式)    格式化时间
    SELECT DATE_FORMAT<NOW(),'xm/xd/XY”);    01/18/2017

    -- %Y 代表年 , %m 代表月 带有前导0 , %d 代表日 带有前导0 , %H 代表时 带有前导0 , %i 代表分 带有前导0 , %s 代表秒 带有前导0.

    -- YEAR()    返回年份    MONTH()    返回月份    DAY()    返回天数
    -- 查询 2017年 5 月的所有订单
    SELECT * FROM t_orders WHERE year(order_date) = 2017 AND month(order_date) = 5;

    -- 格式化当前时间
    SELECT DATE_FORMAT(NOW(), '%Y年-%m月-%d日 %H时:%i分:%s秒')

信息函数

    -- LAST_INSERT_ID()  返回最后插入记录的 id
    -- 若最后插入记录时,使用一个INSERT插入了多条记录, 则返回本次插入中,插入的第一条记录的id,而不是最后一条的id.

    -- CONNECTION_ID()  查看你连接MySQL时的连接id
    SELECT connection_id();    2

    -- USER()    查询当前用户
    SELECT USER();    root@localhost

聚合函数

特点：只有一个返回值.

    -- avg()    求该列记录的平均值. 忽略值为 null 的行.
    SELECT ROUND(AVG(goods_price),2) As aug price FROM t_goods;    5852.27

    -- count()    计数. 如果指定了列, 则忽略该列记录为null的行.
    SELECT count(*) AS good_count FROM t_goods;    22
    SELECT count(good_id) AS good_count FROM t_goods;    22

    -- max()    求该列记录的最大值. 忽略值为 null 的行.
    -- min()    求该列记录的最小值. 忽略值为 null 的行.
    -- sum()    求该列记录的和. 忽略值为 null 的行.

加密函数

    -- 如果密码是为了WEB页面做准备，那么采用MD5()函数
    SELECT MD5('admin');    21232f297a57a5a743894a0e4a801fc31
    SELECT PASSWORD('admin');    *4ACFE3202A5FF5CF467898FC58AAB1D6150294411

    -- 如果是为了修改数据库的密码，用PASSWORD()函数
    -- 修改当前用户的密码
    SET PASSWORD = PASSWORD('123456');

随机函数

在 mysql 中，rand() 函数用于产生0（包含）到1（不包含）的随机数，

    -- 获取随机数, 处于 0-1 范围
    SELECT RAND();
    -- 向上取整
    SELECT CEIL(RAND());
    -- 向下取整
    SELECT FLOOR(RAND());
    -- 获取 1 - N 之间的随机数
    select CEIL(RAND()*N)
    -- 取 x - y 之间的随机数.
    SELECT (CEIL(RAND() * (y-x+1)) + (x-1));
    -- 随机取整数3或4
    SELECT (CEIL(RAND() * 2) + 2);
    -- 随机取整数100-150
    SELECT (CEIL(RAND() * 51) + 99);