能運行在多個系統上的代碼具有可移植性，在數據庫軟件中，多數sql語句時可移植的，可移植性比較強；而函數的移植性不強，主要是由于各種數據庫軟件都支持自己所獨有的函數。因此許多sql用戶不認同使用數據庫軟件所獨有的的函數
字符串函數
    concat(str1,...,strn)   : 連接n個字符串為1個完全的字符串
    insert(str,x,y,instr)   : 將字符串str從第x位置開始，y個字符長的子串替換為instr
    lower(str)      : 所有字符變成小寫
    upper(str)      ：所有字符變成大寫
    left(str)       : 返回字符串最左側的x個字符
    right(str)      : 返回字符串str最右側的x個字符
    lpad(str,n,pad)     : 使用pad字符串對字符串最左側進行填充，直到長度為n個字符
    rpad(str,n,pad)     : 使用pad字符串對字符串最右側進行填充，直到長度為n個字符
    ltrim(str)      : 去掉字符串str最左側的空格
    rtrim(str)      : 去掉字符串str最右側的空格
    repeat(str,x)       : 返回字符串str重復x次的結果
    replace(str,a,b)    : 使用字符串b替換字符串str中所有出現的字符串
    strcmp(str1,str2)   : 比較字符串str1和str2
    trim(str)       : 去掉字符串str行頭和行尾的空格
    substring(str,x,y)  : 返回字符串str中從x位置起y個字符串長度的字符串

    1、concat(str1,...,strn)
        mysql> select concat('M','y','sql');
        +-----------------------+
        | concat('M','y','sql') |
        +-----------------------+
        | Mysql                 |
        +-----------------------+
        mysql> select concat('M','y','sql',null);
        +----------------------------+
        | concat('M','y','sql',null) |
        +----------------------------+
        | NULL                       |
        +----------------------------+
        注意：如果傳入的參數有null，則返回的結果將是null
    2、insert(str,x,y,instr)
        mysql> select insert('mysql learning',3,5,'miss you');
        +-----------------------------------------+
        | insert('mysql learning',3,5,'miss you') |
        +-----------------------------------------+
        | mymiss youearning                       |
        +-----------------------------------------+ 
    3、strcmp(str1,str2)
        mysql> select strcmp('my','sql');
        +--------------------+
        | strcmp('my','sql') |
        +--------------------+
        |                 ⑴ |
        +--------------------+
        1 row in set (0.01 sec)

        mysql> select strcmp('mysd','mysql');
        +------------------------+
        | strcmp('mysd','mysql') |
        +------------------------+
        |                     ⑴ |
        +------------------------+
        1 row in set (0.00 sec)

        mysql> select strcmp('mysd','mysal');
        +------------------------+
        | strcmp('mysd','mysal') |
        +------------------------+
        |                      1 |
        +------------------------+
        1 row in set (0.00 sec)

        mysql> select strcmp('mysd','mysd');
        +-----------------------+
        | strcmp('mysd','mysd') |
        +-----------------------+
        |                     0 |
        +-----------------------+
    4、獲得字符串長度length()和字符數函數char_length()
        mysql> select 'mysql' as '英文字符串字節長度',
            -> length('mysql') as '字符串長度',
            -> '常建功' as '中文字符串',
            -> length('常建功') as '字符串字節長度';
    +--------------------+------------+------------+----------------+
    | 英文字符串字節長度 | 字符串長度 | 中文字符串 | 字符串字節長度 |
    +--------------------+------------+------------+----------------+
    | mysql              |          5 | 常建功     |              6 |
    +--------------------+------------+------------+----------------+
        select 'mysql' as '英文字符串',char_length('mysql') as '字符串字符數', '常建功' as '中文字符串',char_length('常建功') as '字符串字符數';
    +------------+--------------+------------+--------------+
    | 英文字符串 | 字符串字符數 | 中文字符串 | 字符串字符數 |
    +------------+--------------+------------+--------------+
    | mysql      |            5 | 常建功     |            6 |
    +------------+--------------+------------+--------------+
    理論上是3，但是實際上我的顯示時6.
    5、大小字母轉換upper()和lower()
        select upper('aBcD') as  'aBcD',lower('HJjdIUE') as 'HJjdIUE';
        +------+---------+
        | aBcD | HJjdIUE |
        +------+---------+
        | ABCD | hjjdiue |
        +------+---------+
    6、查找字符串
        find_in_set(),field(),locate(),position(),instr(),ELT()

        find_in_set(str1，str2)獲得相匹配字符串的位置，參數str2中將包括若干個用逗號隔開的字符串    
            mysql> select find_in_set('mysql','I,love,mysql,and,you?') as '位置';
            +------+
            | 位置 |
            +------+
            |    3 |
            +------+

        filed(str,str1,str2,...):返回第1個與字符串str相匹配的字符串的位置
            mysql> select field('mysql','oracle','sql server','mysql') as '位置';
            +------+
            | 位置 |
            +------+
            |    3 |
            +------+

        locate(str1,str):返回參數str中字符串str1的開始位置
        position(str1 in str)
        instr(str,str1)
            mysql> select locate('sql','mysql') as'locate',position('sql' in 'mysql') as 'position',instr('mysql','sql') as 'instr';
            +--------+----------+-------+
            | locate | position | instr |
            +--------+----------+-------+
            |      3 |        3 |     3 |
            +--------+----------+-------+
        elt(n,str1,str2,...)
            返回第n個字符串
            mysql> select elt(1,'mysql','oracle','sql server') as ELT;
            +-------+
            | ELT   |
            +-------+
            | mysql |
            +-------+
        make_set(num,str1,str2,...,strn)
            首先將數值num轉換成2進制數，然后依照2進制從參數str1，str2，...，strn當選取相應的字符串。在通過2進制來選擇字符串時，會從右到左讀取該值，如果值為1選擇該字符串，否則將不選擇字符串。
            mysql> select bin(13) as BIN, make_set(13,'a','b','c','d','e','f','g') as make_set;
            +------+----------+
            | BIN  | make_set |
            +------+----------+
            | 1101 | a,c,d    |
            +------+----------+
            1 row in set (0.00 sec)

            mysql> select bin(23) as BIN, make_set(23,'a','b','c','d','e','f','g') as make_set;
            +-------+----------+
            | BIN   | make_set |
            +-------+----------+
            | 10111 | a,b,c,e  |
            +-------+----------+
            1 row in set (0.00 sec)

            mysql> select bin(23) as BIN, make_set(23,'a','b') as make_set;
            +-------+----------+
            | BIN   | make_set |
            +-------+----------+
            | 10111 | a,b      |
            +-------+----------+
    7、從現有字符串中截取子字符串
        left(str,num)
            mysql> select left('algfdg',3) as 'left';
            +------+
            | left |
            +------+
            | alg  |
            +------+
        right(str,num)      
            mysql> select right('algfdg',3) as 'right';
            +-------+
            | right |
            +-------+
            | fdg   |
            +-------+
        substring(str,num,len)和mid(str,num,len)：截取指定位置和長度的子字符串
            mysql> select substring('algfdg',3,4) as 'substring',mid('algfdg',3,4) as 'mid';
            +-----------+------+
            | substring | mid  |
            +-----------+------+
            | gfdg      | gfdg |
            +-----------+------+
    8、去除字符串的首尾空格
        ltrim(str)
            mysql> select ltrim('   mysql') as '   mysql';
            +-------+
            | mysql |
            +-------+
            | mysql |
            +-------+
            1 row in set, 1 warning (0.00 sec)

            mysql> select '   mysql' as '   mysql';
            +----------+
            | mysql    |
            +----------+
            |    mysql |
            +----------+
            1 row in set, 1 warning (0.00 sec)
        right(str)
            mysql> select rtrim('mysql      ') as 'mysql';
            +-------+
            | mysql |
            +-------+
            | mysql |
            +-------+
            1 row in set (0.00 sec)

            mysql> select 'mysql      ' as 'mysql';
            +-------------+
            | mysql       |
            +-------------+
            | mysql       |
            +-------------+     
        trim(str)
            mysql> select trim('  mysql  ') as '  mysql  ';
            +---------+
            | mysql   |
            +---------+
            | mysql   |
            +---------+
    9、替換字符串
        insert(str,pos,len,newstr):
            mysql> select '這是mysql數據庫' as 'oldstring',insert('這是mysql數據庫',3,5,'oracle') as 'newstring';
            +-----------------+------------------+
            | oldstring       | newstring        |
            +-----------------+------------------+
            | 這是mysql數據庫 | 這oracleql數據庫 |
            +-----------------+------------------+
        replace(str,substr,newstr)
            mysql> select '這是mysql數據庫' as 'oldstring',replace('這是mysql數據庫','mysql','oracle') as newstring;
            +-----------------+------------------+
            | oldstring       | newstring        |
            +-----------------+------------------+
            | 這是mysql數據庫 | 這是oracle數據庫 |
            +-----------------+------------------+
數值函數
    abs(x)      返回數值x的絕對值
    ceil(x)     向上取整
    float(x)    向下取整
    mod(x,y)    返回x模y的值
    rand()      返回0~1內的隨機數
    rand(n)     指定種子
    round(x,y)  返回x的4舍5入后有y位小數的數值
    truncate(x,y)   返回數值x截斷y位小數的數值

    mysql> select truncate(903.2432,2),truncate(902.324534,⑵);
    +----------------------+-------------------------+
    | truncate(903.2432,2) | truncate(902.324534,⑵) |
    +----------------------+-------------------------+
    |               903.24 |                     900 |
    +----------------------+-------------------------+      
日期和時間函數
    curdate()
    curtime()
    now()
    unix_timestamp(date)
    from_uinxtime()
    week(date)
    year(date)
    hour(time)
    minute(time)
    monthname(date)

    1、獲得當前日期和時間

        now(),current_timestamp(),localtime(),sysdate();
        推薦使用now()

            mysql> select now() as 'now',current_timestamp() as 'current_timestamp',localtime() as 'localtime',sysdate() as 'sysdate';
            +---------------------+---------------------+---------------------+---------------------+
            | now                 | current_timestamp   | localtime           | sysdate             |
            +---------------------+---------------------+---------------------+---------------------+
            | 2015-04⑵9 21:28:38 | 2015-04⑵9 21:28:38 | 2015-04⑵9 21:28:38 | 2015-04⑵9 21:28:38 |
            +---------------------+---------------------+---------------------+---------------------+   
    2、獲得當前日期

        curdate(),current_date()
        推薦使用curdate()


        mysql> select current_date() as 'current_date',curdate() as 'curdate';
        +--------------+------------+
        | current_date | curdate    |
        +--------------+------------+
        | 2015-04⑵9   | 2015-04⑵9 |
        +--------------+------------+

    3、獲得當前時間

        curtime()和current_time()
        推薦使用curtime()

        mysql> select current_time() as 'current_time',curtime() as 'curtime';
        +--------------+----------+
        | current_time | curtime  |
        +--------------+----------+
        | 07:00:27     | 07:00:27 |
        +--------------+----------+

    4、通過各種方式顯示日期和時間

        (1)unix方式顯示

            unix_timestamp():返回時間戳格式的時間
            from_unixtime():將時間戳格式時間轉換成普通格式的時間

            mysql> select  now() as '當前時間',unix_timestamp(now()) 'unix格式',from_unixtime(unix_timestamp(now())) as '普通格式';

            +---------------------+------------+---------------------+
            | 當前時間            | unix格式   | 普通格式            |
            +---------------------+------------+---------------------+
            | 2015-04⑶0 07:04:21 | 1430348661 | 2015-04⑶0 07:04:21 |
            +---------------------+------------+---------------------+

            注意：unix_timestamp()函數沒有參數傳入，則會顯示出當前時間和日期的時間戳情勢，如果傳入了某個時間參數，則會顯示所傳入時間的時間戳。

        (2)通過UTC方式顯示日期和時間

            UTC，即國際調和時間
            utc_date()：實現日期
            utc_time()：實現時間

            mysql> select now() as 'now',utc_date() as 'utc date',utc_time() as 'utc time';
            +---------------------+------------+----------+
            | now                 | utc date   | utc time |
            +---------------------+------------+----------+
            | 2015-04⑶0 07:14:49 | 2015-04⑵9 | 23:14:49 |
            +---------------------+------------+----------+

            注意：返回的時間與現在的時間有8小時之差
    5、獲得日期和時間各部份值
        year()      :日期的年份
        quarter()   :日期所屬第幾個季度
        month()     :月
        week()      :日期屬于第幾個星期
        dayofmonth()    :屬于當前月的第幾天
        hour()      :時間的小時
        minute()    :分鐘
        second()    :秒

        mysql> select
            -> now() as 'now',
            -> quarter(now()) as 'quarter',
            -> month(now()) as 'month',
            -> week(now()) as 'week',
            -> dayofmonth(now()) as 'day',
            -> hour(now()) as 'hour',
            -> minute(now()) as 'minute',
            -> second(now()) as 'second';
        +---------------------+---------+-------+------+------+------+--------+--------+
        | now                 | quarter | month | week | day  | hour | minute | second |
        +---------------------+---------+-------+------+------+------+--------+--------+
        | 2015-04⑶0 07:29:19 |       2 |     4 |   17 |   30 |    7 |     29 |     19 |
        +---------------------+---------+-------+------+------+------+--------+--------+

        1、月
            month()     :1~12
            monthname() :月份的英文名字

            mysql> select now() as 'now',
                -> month(now()) as 'month',
                -> monthname(now()) as 'month';
            +---------------------+-------+-------+
            | now                 | month | month |
            +---------------------+-------+-------+
            | 2015-04⑶0 07:33:08 |     4 | April |
            +---------------------+-------+-------+ 

        2、星期
            week()和weekofyear()     :返回日期和時間中星期是當前年的第幾個星期，范圍為1~53
                實際利用中，常常不需要實現上述功能

                mysql> select now() as 'now',week(now()) as 'week',weekofyear(now()) as 'weekofyear';
                +---------------------+------+------------+
                | now                 | week | weekofyear |
                +---------------------+------+------------+
                | 2015-04⑶0 07:41:59 |   17 |         18 |
                +---------------------+------+------------+

            dayname()   :星期的英文名
            dayofweek() :星期幾，1~7，星期日為1
            weekday()   :星期幾，0~6,0表示星期1，順次類推

                mysql> select now() as 'now',dayname(now()) as 'dayname',dayofweek(now()) as 'dayofweek',weekday(now()) as 'weekday';
                +---------------------+----------+-----------+---------+
                | now                 | dayname  | dayofweek | weekday |
                +---------------------+----------+-----------+---------+
                | 2015-04⑶0 07:45:08 | Thursday |         5 |       3 |
                +---------------------+----------+-----------+---------+            

                mysql> select now() as 'now',dayname(now()) as 'dayname',dayofweek(now())⑴ as 'dayofweek',weekday(now())+1 as 'weekday';
                +---------------------+----------+-----------+---------+
                | now                 | dayname  | dayofweek | weekday |
                +---------------------+----------+-----------+---------+
                | 2015-04⑶0 08:07:24 | Thursday |         4 |       4 |
                +---------------------+----------+-----------+---------+    

        3、天
            dayofmonth()    :月中的第幾天
            dayofyear() :年中的第幾天

                mysql> select now() as 'now',dayofmonth(now()) as 'dayofmonth',dayofyear(now()) as 'dayofyear';
                +---------------------+------------+-----------+
                | now                 | dayofmonth | dayofyear |
                +---------------------+------------+-----------+
                | 2015-04⑶0 07:52:37 |         30 |       120 |
                +---------------------+------------+-----------+
                1 row in set (0.00 sec)

        4、獲得指定時間域的值
            extract()   :extract,提取，獲得
            extract(type from date)

            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> select now() as 'now',extract(year from now()) as 'year',extract(month from now()) as 'month',extract(day from now()) as 'day',extract(hour from now()) as 'hour',extract(minute from now()) as 'minute',extract(second from now()) as 'second',extract(week from now()) as 'week',extract(hour_minute from now()) as 'hour_minute',extract(quarter from now()) as 'quarter',extract(day_second from now()) as 'day_second' ;
            +---------------------+------+-------+------+------+--------+--------+------+-------------+---------+------------+
            | now                 | year | month | day  | hour | minute | second | week | hour_minute | quarter | day_second |
            +---------------------+------+-------+------+------+--------+--------+------+-------------+---------+------------+
            | 2015-04⑶0 08:14:22 | 2015 |     4 |   30 |    8 |     14 |     22 |   17 |         814 |       2 |   30081422 |
            +---------------------+------+-------+------+------+--------+--------+------+-------------+---------+------------+


    6、計算日期和時間的函數
        1、計算與默許日期和時間（0000年1月1日）相互操作的函數，
            to_days(date)
                    計算日期參數date與默許日期和時間(0000年1月1日)之間相隔天數
            from_days(number)
                    該函數計算從默許日期和時間(0000年1月1日)開始經歷number天后的日期和時間
            datediff(date1,date2)       
                    計算date1和date2之間相隔天數

            mysql> select now() as 'now',to_days(now()) as 'toDays',from_days(to_days(now())+7) as 'from_days',datediff(now(),'2000⑴2-01') 'datediff';
            +---------------------+--------+------------+----------+
            | now                 | toDays | from_days  | datediff |
            +---------------------+--------+------------+----------+
            | 2015-04⑶0 08:27:11 | 736083 | 2015-05-07 |     5263 |
            +---------------------+--------+------------+----------+

        2、計算與指定日期和時間相互操作的函數 
            adddate(date,n) 
            subdate(date,n) 

                mysql> select curdate() as 'curdate',adddate(curdate(),5) as 'five day after',subdate(curdate(),5) as 'five day before';
                +------------+----------------+-----------------+
                | curdate    | five day after | five day before |
                +------------+----------------+-----------------+
                | 2015-04⑶0 | 2015-05-05     | 2015-04⑵5      |
                +------------+----------------+-----------------+       

            adddate(d,interval expr type)
            subdate(d,interval expr type)
                interval:間隔，區間
                type的值
                    SECOND      秒       ss  
                    MINUTE      分鐘      mm
                    HOUR        小時      hh
                    DAY     日       DD
                    MONTH       月       MM
                    YEAR        年       YY
                    MINUTE_SECOND   分鐘和秒    mm和ss之間用任意符號隔開
                    HOUR_SECOND 小時和秒    hh和ss之間用任意符號隔開
                    HOUR_MINUTE
                    DAY_SECOND      
                    DAY_MINUTE      
                    DAY_HOUR        
                    YEAR_MONTH      
                mysql> select curdate() as 'curdate',adddate(curdate(),interval '2,3' year_month) as 'two year three month after';
                +------------+----------------------------+
                | curdate    | two year three month after |
                +------------+----------------------------+
                | 2015-04⑶0 | 2017-07⑶0                 |
                +------------+----------------------------+
                1 row in set (0.00 sec)

                mysql> select curdate() as 'curdate',adddate(curdate(),interval '2,3' day_minute) as 'two year three month after';
                +------------+----------------------------+
                | curdate    | two year three month after |
                +------------+----------------------------+
                | 2015-04⑶0 | 2015-04⑶0 02:03:00        |
                +------------+----------------------------+
                注意：第2個查詢語句沒有出現想要的結果

            addtime()
            subtime()
                mysql> select curtime() as 'curtime',addtime(curtime(),5) as 'five second after',subtime(curtime(),5) as 'five second before';
                +----------+-------------------+--------------------+
                | curtime  | five second after | five second before |
                +----------+-------------------+--------------------+
                | 08:42:08 | 08:42:13          | 08:42:03           |
                +----------+-------------------+--------------------+
                1 row in set (0.01 sec)

                mysql> select curtime() as 'curtime',addtime(curtime(),5*60) as 'five minute after',subtime(curtime(),5*60) as 'five minute before';
                +----------+-------------------+--------------------+
                | curtime  | five minute after | five minute before |
                +----------+-------------------+--------------------+
                | 08:42:54 | 08:45:54          | 08:39:54           |
                +----------+-------------------+--------------------+

系統信息函數

    version()
    database()
    user()
    last_insert_id()


        mysql> select version() as 'version',database() as 'database',user() as 'user',last_insert_id() as 'auto_increment_id'
        +--------------------------+----------+----------------+-------------------+
        | version                  | database | user           | auto_increment_id |
        +--------------------------+----------+----------------+-------------------+
        | 5.0.51b-community-nt-log | test3    | root@localhost |                 0 |
        +--------------------------+----------+----------------+-------------------+