mysql相除_mysql 常用函数

流程控制函数

CASE 函数

[写法-]

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

当value等于compare_value时，则返回result，否则返回else里的result，如果没有else子句则返回null

[写法2]

CASE WHEN [condition] THEN result [WHEN [condition] THEN

result ...] [ELSE result] END

• 当第一个condition满足时，则返回result，否则返回else里的result，如果

没有else子句时则返回null

例

写法1：条件等于的时候 把被对比的值写在case value when之间

select case 1 when 2 then 'aa' when 1 then 'bb' else 'cc' end;

>bb #输出结果

当1 等于 2 的时候 则输出 aa 结束， 显然1 不等于 2 ，则用 1 跟第二个when 的值 比较 显然1等于1 输出 bb 语句结束,否则的话输出cc

select dept_id,case dept_id when 1 then 'english' when 2 then 'accounting' else 'unknown' end from students;

查询dept_id，等于1的时候输出 english，等于2的时候输出accounting ,未知的的话输出unknown

#写法2 当条件是大于 ，等于， 大于等于， 被对比的条件 写在case when 后面 case when 条件

select sname,case when dept_id>=1 and dept_id <=2 then 'english' when dept_id>=2 then 'accounting' else 'unknown' end from students;

当条件1 dept_id>=1 and dept_id <=2

条件2 dept_id>=2

条件1 和 条件2 都满足时，则先输出条件1 的满足的内容

IF 函数

[用法]

IF (expr1,expr2,expr3)

只有3个三个参数

expr表示 表达式

当expr1为1/true时，则返回expr2，否则返回expr3

[例]

mysql> select sname,if(dept_id>=2,'english','accounting') ,dept_id from students;

+----------+---------------------------------------+---------+

| sname | if(dept_id>=2,'english','accounting') | dept_id |

+----------+---------------------------------------+---------+

| zhangsan | accounting | 1 |

| lisi | english | 2 |

| wangwu | english | 5 |

| xiaowang | english | 3 |

| xiaowang | english | 2 |

| xiaoli | english | 2 |

| xiaojie | english | 5 |

+----------+---------------------------------------+---------+

7 rows in set (0.00 sec)

IFNULL函数

IFNULL(expr1,expr2)

当expr1为非null时，则返回expr1，否则返回expr2

[例]

mysql> SELECT IFNULL(1,0);

+-------------+

| IFNULL(1,0) |

+-------------+

| 1 |

+-------------+

1 row in set (0.00 sec)

NULLIF函数

NULLIF(expr1,expr2)

当expr1等于expr2时，则返回null，否则返回expr1

[例]

mysql> SELECT NULLIF(1,1);

+-------------+

| NULLIF(1,1) |

+-------------+

| NULL |

+-------------+

1 row in set (0.00 sec)

字符串函数

ASCII(str)返回str字符串中最左边字符的ascii码值，如果是空串则返回0，如果str是null则返回null

• mysql> SELECT ASCII('a');

• -> 97

• mysql> SELECT ASCII('b');

• -> 98

• mysql> SELECT ASCII('ab');

• -> 97

只返回第一个字符的ASCII码

字符串比较大小的时候就是根据ASCII 比较大小

CHAR(N,… [USING charset_name])将括号中的N转化成ascii码对应的字符，返回这些字符组成的字符串，其中的null会被忽略

• mysql> SELECT CHAR(77,121,83,81,'76');

• -> 'MySQL'

• mysql> SELECT CHAR(77,77.3,'77.3');

• -> 'MMM‘

• mysql> SELECT CHARSET(CHAR(X'65')), CHARSET(CHAR(X'65' USING utf8));

• +----------------------+---------------------------------+

• | CHARSET(CHAR(X'65')) | CHARSET(CHAR(X'65' USING utf8)) |

• +----------------------+---------------------------------+

• | binary | utf8 |

• +----------------------+---------------------------------+

CHAR_LENGTH(str)返回字符串的字符长度

mysql> select char_length('asdf');

>4

mysql>

mysql> select char_length(4567);

>4

CONCAT(str1,str2,…)• 返回括号里所有参数字符串连接在一起，当其中有参数为NULL时则返回NULL

• mysql> SELECT CONCAT('My', 'S', 'QL');

• -> 'MySQL'

• mysql> SELECT CONCAT('My', NULL, 'QL');

• -> NULL

• mysql> SELECT CONCAT(14.3);

• -> '14.3'

INSERT(str,pos,len,newstr)• 将str中从pos位置开始后的len个字符替换成newstr字符串(从哪个位置开始，替换多少个字符)

• mysql> SELECT INSERT('Quadratic', 3, 4, 'What'); # 从第三个位置开始替换4个字符

• -> 'QuWhattic'

• mysql> SELECT INSERT('Quadratic', -1, 4, 'What'); #如果有负数则不替换

• -> 'Quadratic'

• mysql> SELECT INSERT('Quadratic', 3, 100, 'What');

• -> 'QuWhat'

INSTR(str,substr)• 返回str字符串中第一个出现substr字符串的位置，如果没出现则返回为0

• mysql> SELECT INSTR('foobarbar', 'bar');

• -> 4

• mysql> SELECT INSTR('xbar', 'foobar');

• -> 0

LEFT(str,len)• 返回str字符串中从左边开始的len个长度的字符

• mysql> SELECT LEFT('foobarbar', 5);

• -> 'fooba‘

LOAD_FILE(file_name)• 读取文件且返回文件内容为字符串

LOCATE(substr,str)• 返回str字符串中第一次出现substr字符串的位置，如果没有则返回null

• mysql> SELECT LOCATE('bar', 'foobarbar');

• -> 4

• mysql> SELECT LOCATE('xbar', 'foobar');

• -> 0

LOWER(str)• 返回将str字符串中所有字符变换成小写后的字符串，但对二进制文本无效

• mysql> SELECT LOWER('QUADRATICALLY');

• -> 'quadratically'

• mysql> SET @str = BINARY 'New York';

• mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));

• +-------------+-----------------------------------+

• | LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |

• +-------------+-----------------------------------+

• | New York | new york |

+-------------+-----------------------------------+

LPAD(str,len,padstr)• 将str的左边补充为padstr，直到补充成len长度的字符串并返回；如果str的长度比len长，则返回str中最左边开始的len长度的字符

• mysql> SELECT LPAD('hi',4,'??');

• -> '??hi'

• mysql> SELECT LPAD('hi',1,'??');

• -> 'h‘

LTRIM(str)• 将str最左边的空格去掉并返回

• mysql> SELECT LTRIM(' barbar');

• -> 'barbar‘

RTRIM(str)• 将str最右边的空格去掉并返回

REPEAT(str,count)• 将str重复count并组合成字符串返回，如果count<1，则返回空串

• mysql> SELECT REPEAT('MySQL', 3);

• -> 'MySQLMySQLMySQL'

REPLACE(str,from_str,to_str)• 将所有str字符串中匹配from_str子串的地方都替换成to_str子字符串

• mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');

• -> 'WwWwWw.mysql.com‘

REVERSE(str)• 将str字符串中的字符按照倒序组合并返回

• mysql> SELECT REVERSE('abc');

• -> 'cba‘

RIGHT(str,len)• 将str字符串中从右边开始的len个字符返回

• mysql> SELECT RIGHT('foobarbar', 4);

• -> 'rbar'

RPAD(str,len,padstr)• 将字符串str从右边开始补充为padstr直到整体长度为len，如果str的长度本身大于len，则返回str中len长度的字符串

• mysql> SELECT RPAD('hi',5,'?');

• -> 'hi???'

• mysql> SELECT RPAD('hi',1,'?');

• -> 'h‘

RTRIM(str)• 将字符串str右边的空格去掉并返回

• mysql> SELECT RTRIM('barbar ');

• -> 'barbar‘

SPACE(N)• 返回N个长度的空格组成的空字符串

• mysql> SELECT SPACE(6);

• -> ' '

SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROMpos FOR len)SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len),SUBSTRING(str FROM pos FOR len)• 如果没有len参数，则返回从pos位置开始的str中的子字符串；如果有len参数，则从pos位置开始返回str中长度为len的子字符串；如果pos为负值，则代表pos从右边开始数from 和 逗号 一样的效果

SUBSTRING_INDEX(str,delim,count)delimit 定界当count为正数，则返回delim出现在str字符串中第count次之前的子字符串，如果是负数，则从右边开始计算

• mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);

• -> 'www.mysql'

• mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);

• -> 'mysql.com‘

UPPER(str)• 返回将str字符串中所有字符转换成大写的字符串

• mysql> SELECT UPPER('Hej');

• -> 'HEJ‘

字符串对比函数

主要有LIKE , NOT LIKE ,STRCMP

LIKEexpr LIKE pat [ESCAPE ‘escape_char’]• 通配符%表示匹配0个或多个字符• 通配符_表示匹配1个字符

• mysql> SELECT 'David!' LIKE 'David_';

• -> 1 #返回的结果是1 或则 0 也就是true或则false

• mysql> SELECT 'David!' LIKE '%D%v%';

• -> 1

• 当匹配字符中有特殊字符时，可以用 \或者escape来指定特殊字符为字符

• mysql> SELECT 'David!' LIKE 'David\_';

• -> 0

• mysql> SELECT 'David_' LIKE 'David\_';

• -> 1

• mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';

• -> 1

NOT LIKEexpr NOT LIKE pat [ESCAPE ‘escape_char’]• 是上一个字符串对比函数的反义

STRCMPSTRCMP(expr1,expr2)• 当expr1等于expr2时等于0，当expr1小于expr2时为-1，反之为1

• mysql> SELECT STRCMP('text', 'text2');

• -> -1

• mysql> SELECT STRCMP('text2', 'text');

• -> 1

• mysql> SELECT STRCMP('text', 'text');

• -> 0

数字函数之算数操作符

算数操作符

+加法 - 把运算符两边的值相加-减法 - 左操作数减去右操作数*乘法 - 把运算符两边的值相乘/和div/除法 - 左操作数除以右操作数DIV整除%和mod取模 - 左操作数除以右操作数后得到的余数

• mysql> SELECT 3/5;

• -> 0.60

• mysql> SELECT 102/(1-1);

• -> NULL

• mysql> SELECT 5 DIV 2, -5 DIV 2, 5 DIV -2, -5 DIV -2;

• -> 2, -2, -2, 2

数字函数

ABS(X)• 绝对值函数

• mysql> SELECT ABS(2);

• -> 2

• mysql> SELECT ABS(-32);

• -> 32

CEILING(X)/CEIL(X)• 返回>=X值的最小整数

• mysql> SELECT CEILING(1.23);

• -> 2

• mysql> SELECT CEILING(-1.23);

• -> -1

FLOOR(X)• 返回<=X值的最大整数

• mysql> SELECT FLOOR(1.23), FLOOR(-1.23);

• -> 1, -2

MOD(N,M), N % M, N MOD M• 取余数操作，返回两者相除之后剩下的余数

• mysql> SELECT MOD(234, 10);

• -> 4

• mysql> SELECT 253 % 7;

• -> 1

• mysql> SELECT MOD(29,9);

• -> 2

• mysql> SELECT 29 MOD 9;

• -> 2

• mysql> SELECT MOD(34.5,3);

• -> 1.5

RAND([N])• 获取0到1之间的随机小数，比如当想获取7~12之间的随机整数是可以使用

• SELECT FLOOR(7 + (RAND() * 5));

• Select * from students order by rand() limit 1;

ROUND(X), ROUND(X,D)• 四舍五入为D位个小数，当D参数不存在是，则默认为0

• mysql> SELECT ROUND(-1.23);

• -> -1

• mysql> SELECT ROUND(-1.58);

• -> -2

• mysql> SELECT ROUND(1.58);

• -> 2

• mysql> SELECT ROUND(1.298, 1);

• -> 1.3

• mysql> SELECT ROUND(1.298, 0);

• -> 1

• mysql> SELECT ROUND(23.298, -1);

• -> 20

TRUNCATE(X,D)• 数字X只保留D位的小数，其余均舍弃,保留几位小数

• mysql> SELECT TRUNCATE(1.223,1);

• -> 1.2

• mysql> SELECT TRUNCATE(1.999,1);

• -> 1.9

• mysql> SELECT TRUNCATE(1.999,0);

• -> 1

• mysql> SELECT TRUNCATE(-1.999,1);

• -> -1.9

• mysql> SELECT TRUNCATE(122,-2);

• -> 100

• mysql> SELECT TRUNCATE(10.28*100,0);

• -> 1028

日期和时间函数

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)• 当expr为正数时则为增加时间，为负数时则为减少时间• Unit参数可以是任意时间单位

• mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);

• -> '2008-02-02‘

• mysql> SELECT ADDDATE('2008-01-02', 31);

• -> '2008-02-02‘

ADDTIME(expr1,expr2)• 将expr2的时间增加到expr1上

• mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');

• -> '2008-01-02 01:01:01.000001'

• mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');

• -> '03:00:01.999997'

CONVERT_TZ(dt,from_tz,to_tz)• 将时间dt从from_tz这个时区转换成to_tz这个时区并返回

• mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

• -> '2004-01-01 13:00:00'

• mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');

• -> '2004-01-01 22:00:00‘

CURDATE()， CURRENT_DATE, CURRENT_DATE()• 返回以yyyy-mm-dd或者yyyymmdd格式的当前时间

• mysql> SELECT CURDATE();

• -> '2018-07-20'

• mysql> SELECT CURDATE() + 0;

• -> 2018-07-20

CURTIME(),CURRENT_TIME,CURRENT_TIME()• 按照hh:mm:ss或者hhmmss格式返回当前时间

• mysql> SELECT CURTIME();

• -> '23:50:26'

• mysql> SELECT CURTIME() + 0;

• -> 235026.000000

Now(),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP()• 返回当前的日期和时间，格式为yyyy-mm-dd hh:mi:ss或者yyyymmddhhmiss

• mysql> SELECT NOW();

• -> '2007-12-15 23:50:26'

• mysql> SELECT NOW() + 0;

• -> 20071215235026.000000

DATE(expr)• 获取expr中的日期

• mysql> SELECT DATE('2003-12-31 01:02:03');

• -> '2003-12-31‘

DATEDIFF(expr1,expr2)• 返回expr1和expr2之间的天数差异，忽略时分秒

• mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');

• -> 1

• mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');

• -> -31

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)date_add增加时间date_sub减少时间

• mysql> SELECT DATE_ADD('2000-12-31 23:59:59',

• -> INTERVAL 1 SECOND);

• -> '2001-01-01 00:00:00'

• mysql> SELECT DATE_ADD('2010-12-31 23:59:59',

• -> INTERVAL 1 DAY);

• -> '2011-01-01 23:59:59'

• mysql> SELECT DATE_ADD('2100-12-31 23:59:59',

• -> INTERVAL '1:1' MINUTE_SECOND);

• -> '2101-01-01 00:01:00'

• mysql> SELECT DATE_SUB('2005-01-01 00:00:00',

• -> INTERVAL '1 1:1:1' DAY_SECOND);

• -> '2004-12-30 22:58:59'

• mysql> SELECT DATE_ADD('1900-01-01 00:00:00',

• -> INTERVAL '-1 10' DAY_HOUR);

• -> '1899-12-30 14:00:00'

• mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);

• -> '1997-12-02'

• mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',

• -> INTERVAL '1.999999' SECOND_MICROSECOND);

• -> '1993-01-01 00:00:01.000001'

• mysql> SELECT DATE_ADD('2009-01-30', INTERVAL 1 MONTH);

• -> '2009-02-28'

DATE_FORMAT(date,format)• 将date日期时间转换成format格式

• mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');

• -> 'Sunday October 2009'

• mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');

• -> '22:23:00'

• mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',

• -> '%D %y %a %d %m %b %j');

• -> '4th 00 Thu 04 10 Oct 277'

• mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',

• -> '%H %k %I %r %T %S %w');

• -> '22 22 10 10:23:00 PM 22:23:00 00 6'

• mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');

• -> '1998 52'

• mysql> SELECT DATE_FORMAT('2006-06-00', '%d');

• -> '00'

DAY(date), DAYOFMONTH(date)• 返回date中日期在当前月份中是第几天

• mysql> SELECT DAYOFMONTH('2007-02-03');

• -> 3

EXTRACT(unit FROM date)Unit单元和date_add/date_sub函数中的一样，是获取date日期的unit部分

• mysql> SELECT EXTRACT(YEAR FROM '2009-07-02');

• -> 2009

• mysql> SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');

• -> 200907

• mysql> SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');

• -> 20102

• mysql> SELECT EXTRACT(MICROSECOND

• -> FROM '2003-01-02 10:30:00.000123');

• -> 123

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)• 根据给定的unixtime，返回yyyy-mm-dd hh:mi:ss或者yyyymmddhhmiss格式的具体时间，或者按照format返回时间

• mysql> SELECT FROM_UNIXTIME(1447430881);

• -> '2015-11-13 10:08:01'

• mysql> SELECT FROM_UNIXTIME(1447430881) + 0;

• -> 20151113100801

• mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),

• -> '%Y %D %M %h:%i:%s %x');

• -> '2015 13th November 10:08:01 2015‘

LAST_DAY(date)• 返回date日期所在月份的最后一天日期

• mysql> SELECT LAST_DAY('2004-02-05');

• -> '2004-02-29'

• mysql> SELECT LAST_DAY('2004-01-01 01:01:01');

• -> '2004-01-31'

• mysql> SELECT LAST_DAY('2003-03-32');

• -> NULL

SYSDATE()• 返回当前日期和时间，格式为yyyy-mm-dd hh:mi:ss或者yyyymmddhhmiss• 和now()函数的区别在于now()返回的时间是语句执行的时间，而sysdate()返回的时间是该函数执行的时间

mysql> select now(),sleep(2),now();

+---------------------+----------+---------------------+

| now() | sleep(2) | now() |

+---------------------+----------+---------------------+

| 2018-07-20 20:57:42 | 0 | 2018-07-20 20:57:42 |

+---------------------+----------+---------------------+

mysql> select sysdate(),sleep(2),sysdate();

+---------------------+----------+---------------------+

| sysdate() | sleep(2) | sysdate() |

+---------------------+----------+---------------------+

| 2018-07-20 20:58:12 | 0 | 2018-07-20 20:58:14 |

+---------------------+----------+---------------------+

1 row in set (2.02 sec)

TIME(expr)• 返回expr日期时间中的时间部分

• mysql> SELECT TIME('2003-12-31 01:02:03');

• -> '01:02:03'

• mysql> SELECT TIME('2003-12-31 01:02:03.000123');

• -> '01:02:03.000123‘

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)• 如果没有date参数，则返回当前时间到1970-01-01 00:00:00之间的秒数，如果有date参数，则表示date到1970-01-01 00:00:00之间的秒数

• mysql> SELECT UNIX_TIMESTAMP();

• -> 1447431666

• mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');

• -> 1447431619

格式转换函数

Cast()和convert()两个函数都可以用来转换数据类型或者转换字符集允许转换的数据类型包括：• Binary[N]• char[N]• Date• Datetime• decimal[M,[D]]• Time• Signed [integer]• Unsigned [integer]

• SELECT CONVERT(_latin1'Müller' USING utf8);

• SELECT CONVERT('test', CHAR CHARACTER SET utf8);

• SELECT CAST('test' AS CHAR CHARACTER SET utf8);

• select CAST('2000-01-01' AS DATE);

• select CONVERT('2000-01-01', DATE);

聚合函数

• 用在存在group by子句的语句中

AVG([DISTINCT] expr)• 计算expr的平均值，distinct关键词表示是否排除重复值

#求出每个学生的平均成绩

• mysql> SELECT student_name, AVG(test_score)

• -> FROM student

• -> GROUP BY student_name;

COUNT(expr)• 计算expr中的个数，如果没有匹配则返回0，注意NULL的区别

求出每个学生都都有哪些课程

• mysql> SELECT student.student_name,COUNT(*)

• -> FROM student,course

• -> WHERE student.student_id=course.student_id

• -> GROUP BY student_name;

COUNT(DISTINCT expr,[expr…])

• 计算有多少个不重复的expr值，注意是计算非NULL的个数

在sutdent表统计resutls字段 ，并且除去重复的值

• mysql> SELECT COUNT(DISTINCT results) FROM student;

MAX([DISTINCT] expr),MIN([DISTINCT] expr)• 返回expr中最大或者最小的值

• mysql> SELECT student_name, MIN(test_score), MAX(test_score)

• -> FROM student

• -> GROUP BY student_name;

SUM([DISTINCT] expr)• 返回expr的求和值