美高梅棋牌官网下载-美高梅棋牌官方版下载

大家可以在美高梅棋牌官网下载当中进行野外生存的游戏、也可以泡泡温泉、吃吃烧烤,美高梅棋牌官方版下载成为卓越不凡的超级娱乐企业巨头,注册可提款,通过该系统的运作,以最为合适的娱乐方式将客户传递到最佳平台。

用户也可以自定义函数

2020-01-21 08:22栏目:美高梅棋牌官网下载
TAG:

本文转自:#function_date-format

格式 說明

MySQL之函数,mysql函数

了解编程的人一般都会知道函数的重要性,丰富的函数有的时候可以给我们带来事半功倍的效果,在MySQL中提供了许多的内置函数,能够帮助开发人员编写简单快捷的SQL语句,除了这些内置的函数之外,用户也可以自定义函数,本次博客整理了一下MySQL中常用的函数,简单的介绍了自定函数,MySQL版本mysql-5.7.19。

 

DATE_FORMAT(date,format)

%a Abbreviated weekday name

常用函数

1.字符串函数

字符串函数是最常用的一种函数。下表列出了常用的字符串函数:

美高梅棋牌官网下载 1

美高梅棋牌官网下载 2

 

2.数值函数

MySQL中另外一类就是数值函数了。这些函数可以处理很多数值方面的运算,常见的数值运算函数如下:

 美高梅棋牌官网下载 3

 

 3.日期和时间函数

 有的时候们需要了解当前的时间,这时候我们就可以调用时间函数了。下面就是一些常用的时间函数:

 美高梅棋牌官网下载 4

下面来说一下DATE_FORMAT(date,format):format可以有以下格式符:

美高梅棋牌官网下载 5Specifier Description %a Abbreviated weekday name (Sun..Sat) %b Abbreviated month name (Jan..Dec) %c Month, numeric (0..12) %D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) %d Day of the month, numeric (00..31) %e Day of the month, numeric (0..31) %f Microseconds (000000..999999) %H Hour (00..23) %h Hour (01..12) %I Hour (01..12) %i Minutes, numeric (00..59) %j Day of year (001..366) %k Hour (0..23) %l Hour (1..12) %M Month name (January..December) %m Month, numeric (00..12) %p AM or PM %r Time, 12-hour (hh:mm:ss followed by AM or PM) %S Seconds (00..59) %s Seconds (00..59) %T Time, 24-hour (hh:mm:ss) %U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0 %u Week (00..53), where Monday is the first day of the week; WEEK() mode 1 %V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X %v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x %W Weekday name (Sunday..Saturday) %w Day of the week (0=Sunday..6=Saturday) %X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V %x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v %Y Year, numeric, four digits %y Year, numeric (two digits) %% A literal % character %x x, for any “x” not listed above 格式符

举个例子吧!

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009                            |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(now(),'%H %k %I %r %T %S %w');
+-------------------------------------------+
| DATE_FORMAT(now(),'%H %k %I %r %T %S %w') |
+-------------------------------------------+
| 23 23 11 11:49:57 PM 23:49:57 57 5        |
+-------------------------------------------+
1 row in set (0.00 sec)

再来看一下DATE_ADD(date,INTERVAL expr unit):其中INTERVAL是关键字,expr是一个表达式,unit为间隔类型,MySQL提供如下的间隔类型:

美高梅棋牌官网下载 6unit Value Expected expr Format MICROSECOND MICROSECONDS SECOND SECONDS MINUTE MINUTES HOUR HOURS DAY DAYS WEEK WEEKS MONTH MONTHS QUARTER QUARTERS YEAR YEARS SECOND_MICROSECOND 'SECONDS.MICROSECONDS' MINUTE_MICROSECOND 'MINUTES:SECONDS.MICROSECONDS' MINUTE_SECOND 'MINUTES:SECONDS' HOUR_MICROSECOND 'HOURS:MINUTES:SECONDS.MICROSECONDS' HOUR_SECOND 'HOURS:MINUTES:SECONDS' HOUR_MINUTE 'HOURS:MINUTES' DAY_MICROSECOND 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS' DAY_MINUTE 'DAYS HOURS:MINUTES' DAY_HOUR 'DAYS HOURS' YEAR_MONTH 'YEARS-MONTHS' 间隔类型

举个例子吧!

mysql> SELECT DATE_ADD('2000-12-31 23:59:59',INTERVAL 1 SECOND);
+---------------------------------------------------+
| DATE_ADD('2000-12-31 23:59:59',INTERVAL 1 SECOND) |
+---------------------------------------------------+
| 2001-01-01 00:00:00                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2100-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND);
+--------------------------------------------------------------+
| DATE_ADD('2100-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND) |
+--------------------------------------------------------------+
| 2101-01-01 00:01:00                                          |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

 

 4.流程函数

 流程函数也是很常用的一类函数,用户可以使用这类函数在一个SQL语句中实现条件选择,这样做能够提高语句的效率。

美高梅棋牌官网下载 7

 举例:

现在有员工工资表如下:

mysql> select * from salary;
+----+---------+
| id | salary  |
+----+---------+
|  1 | 1000.00 |
|  2 | 2000.00 |
|  3 | 3000.00 |
|  4 | 4000.00 |
|  5 | 5000.00 |
|  6 | 6000.00 |
|  7 | 7000.00 |
|  8 |    NULL |
+----+---------+
8 rows in set (0.00 sec)

(1)当工资高于5000就显示高工资,低于5000的就显示低工资:

美高梅棋牌官网下载 8mysql> select id,if(salary>5000,'高工资','低工资') as level from salary; +----+-----------+ | id | level | +----+-----------+ | 1 | 低工资 | | 2 | 低工资 | | 3 | 低工资 | | 4 | 低工资 | | 5 | 低工资 | | 6 | 高工资 | | 7 | 高工资 | | 8 | 低工资 | +----+-----------+ 8 rows in set (0.00 sec) View Code

(2)当没有工资的时候,用0代替:

美高梅棋牌官网下载 9mysql> select ifnull(salary,0) from salary; +------------------+ | ifnull(salary,0) | +------------------+ | 1000.00 | | 2000.00 | | 3000.00 | | 4000.00 | | 5000.00 | | 6000.00 | | 7000.00 | | 0.00 | +------------------+ 8 rows in set (0.00 sec) View Code

(3)用case when来实现例子1:

美高梅棋牌官网下载 10mysql> select id,case when salary>5000 then '高工资' else '低工资' end from salary; +----+-------------------------------------------------------------+ | id | case when salary>5000 then '高工资' else '低工资' end | +----+-------------------------------------------------------------+ | 1 | 低工资 | | 2 | 低工资 | | 3 | 低工资 | | 4 | 低工资 | | 5 | 低工资 | | 6 | 高工资 | | 7 | 高工资 | | 8 | 低工资 | +----+-------------------------------------------------------------+ 8 rows in set (0.00 sec) View Code

(4)当工资是1000的为特低工资,工资为2000的为低工资,其余为高工资:

美高梅棋牌官网下载 11mysql> select id,case salary when 1000 then '特低工资' when 2000 then '低工资' else '高工资' end from salary; +----+-------------------------------------------------------------------------------------------+ | id | case salary when 1000 then '特低工资' when 2000 then '低工资' else '高工资' end | +----+-------------------------------------------------------------------------------------------+ | 1 | 特低工资 | | 2 | 低工资 | | 3 | 高工资 | | 4 | 高工资 | | 5 | 高工资 | | 6 | 高工资 | | 7 | 高工资 | | 8 | 高工资 | +----+-------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec) View Code

 

5.其他函数

美高梅棋牌官网下载 12

 MySQL内置的函数还有很多,这里就不做一一介绍,具体的可以参考官方的文档,里面也有举了很多的栗子:

 

Formats the date value according to the format string.

%A Full weekday name

 自定义函数

 用户可以自定义函数,语法如下:

CREATE FUNCTION func_name([parameter1,parameter2...])
    RETURNS type
    runtime_body

下面就来创建一个函数,比如自定义一个两个整数的加法函数:

DELIMITER $
CREATE FUNCTION mysum(n1 INT,n2 INT) RETURNS INT
BEGIN
 DECLARE num int DEFAULT 0;
 SET num = n1 + n2;
 RETURN(NUM);
END $
DELIMITER ;

执行函数使用SELECT:

mysql> SELECT mysum(1,2);
+------------+
| mysum(1,2) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

删除自定函数使用:

DROP FUNCTION func_name

比如删除上面创建的函数:

mysql> drop function mysum;
Query OK, 0 rows affected (0.00 sec)

自定义函数暂时写到这里,更详细的会在下一篇博客中和存储过程一起介绍。

 

了解编程的人一般都会知道函数的重要性,丰富的函数有的时候可以给我们带来事半功倍的效果,在MySQL中提供了许多...

The following specifiers may be used in the format string. As of MySQL 3.23, the “%” character is required before format specifier characters. In earlier versions of MySQL, “%” was optional.

%b Abbreviated month name

Specifier

%B Full month name

Description

%c Date and time, locale-specific.

%aAbbreviated weekday name (Sun..Sat)%bAbbreviated month name (Jan..Dec)%cMonth, numeric (0..12)%DDay of the month with English suffix (0th, 1st, 2nd, 3rd, …)%d美高梅棋牌官网下载,Day of the month, numeric (00..31)%eDay of the month, numeric (0..31)%fMicroseconds (000000..999999)%HHour (00..23)%hHour (01..12)%IHour (01..12)%iMinutes, numeric (00..59)%jDay of year (001..366)%kHour (0..23)%lHour (1..12)%MMonth name (January..December)%mMonth, numeric (00..12)%p``AM or PM %rTime, 12-hour (hh:mm:ss followed by AM or PM)%SSeconds (00..59)%sSeconds (00..59)%TTime, 24-hour (hh:mm:ss)%UWeek (00..53), where Sunday is the first day of the week%uWeek (00..53), where Monday is the first day of the week%VWeek (01..53), where Sunday is the first day of the week; used with %X %vWeek (01..53), where Monday is the first day of the week; used with %x %WWeekday name (Sunday..Saturday)%wDay of the week (0=Sunday..6=Saturday)%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V %xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v %YYear, numeric, four digits%yYear, numeric (two digits)%%A literal “%” character%xx, for any “x” not listed above

%d Day of the month as decimal number (01-31).

The %v, %V, %x, and %X format specifiers are available as of MySQL 3.23.8. %f is available as of MySQL 4.1.1.

%H Hours as decimal number (00-23).

Ranges for the month and day specifiers begin with zero due to the fact that MySQL permits the storing of incomplete dates such as '2014-00-00' (as of MySQL 3.23).

%I Hours as decimal number (01-12).

As of MySQL 4.1.21, the language used for day and month names and abbreviations is controlled by the value of the lc_time_names system variable (Section9.8, “MySQL Server Locale Support”).

%j Day of year as decimal number (001-366).

As of MySQL 4.1.23, DATE_FORMAT() returns a string with a character set and collation given by character_set_connection and collation_connection so that it can return month and weekday names containing non-ASCII characters. Before 4.1.23, the return value is a binary string.

%m Month as decimal number (01-12).

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'

%M Minute as decimal number (00-59).

%p AM/PM indicator in the locale.

Used in conjuction with ’%I’ and *not* with ’%H’.

%S Second as decimal number (00-61), allowing for up to two leap-seconds

版权声明:本文由美高梅棋牌官网下载发布于美高梅棋牌官网下载,转载请注明出处:用户也可以自定义函数