Mysql基礎教程 mysql進階

Mysql其他常用函數



加密函數


名稱

描述

MD5(STR)

信息摘要算法

PASSWORD(STR)

密碼算法

ENCODE(STR,PED_STR)

加密結果是一二進制數,必須使用BLOB類型字段保存

DECODE(CRYPT_STR,PWD_STR)

對通過ENCODE加密之后的內容解密


使用MD5返回一個32位字符串


mysql> SELECT MD5('ADMIN');
+----------------------------------+
| MD5('ADMIN')                     |
+----------------------------------+
| 73acd9a5972130b75066c82595a1fae3 |
+----------------------------------+
1 row in set (0.07 sec)


查看它的字符長度


mysql> SELECT LENGTH(MD5('ADMIN'));
+----------------------+
| LENGTH(MD5('ADMIN')) |
+----------------------+
|                   32 |
+----------------------+
1 row in set (0.00 sec)


打開MySQL數據庫


mysql> USE mysql;
Database changed


查看表


mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.03 sec)


查看用戶詳細信息


mysql> SELECT * FROM user \G;
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: 
 authentication_string: 
      password_expired: N
*************************** 2. row ***************************
                  Host: 127.0.0.1
                  User: root
              Password: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: 
 authentication_string: 
      password_expired: N
*************************** 3. row ***************************
                  Host: ::1
                  User: root
              Password: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: 
 authentication_string: 
      password_expired: N
*************************** 4. row ***************************
                  Host: %
                  User: king
              Password: *0C6F8A2CE8ABFD18609CCE4CDFAB3C15DAD20718
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: 
      password_expired: N
4 rows in set (0.14 sec)
ERROR: 
No query specified


查看加密的結果


mysql> SELECT PASSWORD('root'),PASSWORD('king');
+-------------------------------------------+-------------------------------------------+
| PASSWORD('root')                          | PASSWORD('king')                          |
+-------------------------------------------+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | *0C6F8A2CE8ABFD18609CCE4CDFAB3C15DAD20718 |
+-------------------------------------------+-------------------------------------------+
1 row in set (0.02 sec)


(注意:PASSWORD(STR)不能對系統用戶和添加用戶的密碼加密。)



其它常用函數


名稱

描述

FORMAT(x,n)

將數字x進行格式化,將x保留到小數點后n位

ASCLL(s)

返回字符串s的第一個字符的ASCLL碼

BIN(x)

返回x的二進制編碼

HEX(x)

返回x的十六進制編碼

OCT(x)

返回x的八進制編碼

CONV(x,f1,f2)

將x從f1進制數變成f2進制數

INET_ATOU(IP)

將IP地址轉換為數字

INET_NToA(n)

將數字轉換成ip地址

GET_LOCT(name,time)

定義鎖

RELEASE_LOCX(name)

解鎖


將數字進行格式化,將數字保留到小數點后幾位


mysql> SELECT FORMAT(3.14567,2);
+-------------------+
| FORMAT(3.14567,2) |
+-------------------+
| 3.15              |
+-------------------+
1 row in set (0.00 sec)


查看ascll碼的英文對應值


mysql> SELECT ASCII('abc');
+--------------+
| ASCII('abc') |
+--------------+
|           97 |
+--------------+
1 row in set (0.02 sec)


返回二進制的編碼


mysql> SELECT BIN(5),HEX(5),OCT(5);
+--------+--------+--------+
| BIN(5) | HEX(5) | OCT(5) |
+--------+--------+--------+
| 101    | 5      | 5      |
+--------+--------+--------+
1 row in set (0.02 sec)


CONV一個進制轉化成另一個進制


mysql> SELECT CONV(5,10,2);
+--------------+
| CONV(5,10,2) |
+--------------+
| 101          |
+--------------+
1 row in set (0.00 sec)


CONV一個進制轉化成另一個進制


mysql> SELECT CONV(35,10,2);
+---------------+
| CONV(35,10,2) |
+---------------+
| 100011        |
+---------------+
1 row in set (0.00 sec)


CONV一個進制轉化成另一個進制


mysql> SELECT CONV(35,10,8);
+---------------+
| CONV(35,10,8) |
+---------------+
| 43            |
+---------------+
1 row in set (0.00 sec)


CONV一個進制轉化成另一個進制


mysql> SELECT CONV(35,10,16);
+----------------+
| CONV(35,10,16) |
+----------------+
| 23             |
+----------------+
1 row in set (0.00 sec)


INET_ATON把IP轉換成二進制


mysql> SELECT INET_ATON('127.0.0.1');
+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+
1 row in set (0.06 sec)


INET_ATON把二進制轉換成Ip


mysql> SELECT INET_NOTA(2130706433);
ERROR 1305 (42000): FUNCTION mysql.INET_NOTA does not exist


INET_ATON把二進制轉換成Ip


mysql> SELECT INET_NTOA(2130706433);
+-----------------------+
| INET_NTOA(2130706433) |
+-----------------------+
| 127.0.0.1             |
+-----------------------+
1 row in set (0.02 sec)


GET_LOCK定義king為10返回值為1


mysql> SELECT GET_LOCK('KING',10);
+---------------------+
| GET_LOCK('KING',10) |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.05 sec)


按斷這個鎖是否被使用


mysql> SELECT IS_FREE)LOCK('KING');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')LOCK('KING')' at line 1


使用IS_FREE_LOCK按斷這個鎖是否被使用


mysql> SELECT IS_FREE_LOCK('KING');
+----------------------+
| IS_FREE_LOCK('KING') |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.02 sec)


使用IS_FREE_LOCK按斷這個鎖是否被使用


mysql> SELECT IS_FREE_LOCK('KING');
+----------------------+
| IS_FREE_LOCK('KING') |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)


使用IS_FREE_LOCK按斷這個鎖是否被使用


mysql> SELECT IS_FREE_LOCK('KING');
+----------------------+
| IS_FREE_LOCK('KING') |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)


使用IS_FREE_LOCK按斷這個鎖是否被使用


mysql> SELECT IS_FREE_LOCK('KING');
+----------------------+
| IS_FREE_LOCK('KING') |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)


使用RELEASE_LOCK解鎖的同時也創建新的鎖


mysql> SELECT RELEASE_LOCK('KING');
+----------------------+
| RELEASE_LOCK('KING') |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)


使用IS_FREE_LOCK這個被解鎖king不存在了


mysql> SELECT IS_FREE_LOCK('KING');
+----------------------+
| IS_FREE_LOCK('KING') |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)


使用GET_LOCK創建一個maizi鎖


mysql> SELECT GET_LOCK('MAIZI',10);
+----------------------+
| GET_LOCK('MAIZI',10) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)


使用IS_FREE_LOCK判斷是否存在這個鎖(0代表存在)


mysql> SELECT IS_FREE_LOCK('MAIZI');
+-----------------------+
| IS_FREE_LOCK('MAIZI') |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)


使用GET_LOCK創建鎖市刪除上一個鎖


mysql> SELECT GET_LOCK('AB',5);
+------------------+
| GET_LOCK('AB',5) |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)


查看maizi是否存在(1代表不存在)


mysql> SELECT IS_FREE_LOCK('MAIZI');
+-----------------------+
| IS_FREE_LOCK('MAIZI') |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)



【本文由麥子學院獨家原創,轉載請注明出處并保留原文鏈接】

logo
? 2012-2016 www.jvbprd.live
蜀ICP備13014270號-4 Version 5.0.0 release20160127

免費領取價值1888元求職寶典!

客服熱線 400-862-8862

回到頂部

彩票安徽25选5