mysql 其他函数

user() 可以查询当前用户名

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

md5() 可以对一个字符串进行md5加密，加密后可以得到一个32位字符串

mysql> select md5('123');
+----------------------------------+
| md5('123')                       |
+----------------------------------+
| 202cb962ac59075b964b07152d234b70 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> create table users(id int, name varchar(30), pwd char(32));
Query OK, 0 rows affected (0.75 sec)

mysql> insert into users values(100, 'zs', md5('123'));
Query OK, 1 row affected (0.04 sec)

mysql> select * from users;
+------+------+----------------------------------+
| id   | name | pwd                              |
+------+------+----------------------------------+
|  100 | zs   | 202cb962ac59075b964b07152d234b70 |
+------+------+----------------------------------+
1 row in set (0.00 sec)

database() 函数可以显示当前，你正在使用的数据库

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

password() 函数，可以对我们的mysql用户加密，即这个也是一个加密函数

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backup             |
| mysql              |
| people             |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use mysql;
Database changed

//user 表中记录了mysql的登录用户
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

//password() 对登录用户的密码进行加密
mysql> select password('root');
+-------------------------------------------+
| password('root')                          |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+
1 row in set (0.00 sec)

查询其他数据库中的表

mysql> select * from goods;
ERROR 1146 (42S02): Table 'test.goods' doesn't exist

//当前用的test数据库  test库中没有goods表  people库中有
mysql> select * from people.goods;
+----+------------+-------+------------+
| id | goods_name | price | mfg        |
+----+------------+-------+------------+
|  1 | 雪碧       |   3.5 | 1982-01-01 |
|  2 | 可乐       |   3.5 | 1982-01-01 |
|  3 | 芬达       |     3 | 1982-01-01 |
|  4 | 脉动       |     0 | 1982-01-01 |
|  5 | 奶茶       |     5 | 1982-01-01 |
|  6 | 绿茶       |     3 | 1982-01-01 |
|  7 | 红牛       |     5 | 1982-01-01 |
|  8 | 红牛       |     5 | 1982-01-01 |
+----+------------+-------+------------+
8 rows in set (0.00 sec)

纵向显示用户信息的技巧

mysql> select * from mysql.user \G;
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password:
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
			......