mysql常用查询|IT运维中文博客

mysql常用查询

时间:2013-10-04 03:10:02 作者:beebol 标签: 分类: Mysql

SELECT VERSION()	显示版本号
SELECT CURRENT_DATE	显示当前日期
SELECT NOW()	显示当前时间
SELECT USER()	显示当前用户
SHOW DATABASES	显示所有数据库
SHOW TABLES IN DATABASE	显示某个数据库中的表
USE DATABASE	使用某个数据库
DESCRIBE TABLE_NAME	显示表的详细信息

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.15-log |
+------------+1 row in set (0.00 sec)
1 row in set (0.00 sec)

显示当前时间，如下

mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2013-10-04     |
+----------------+1 row in set (0.00 sec)
1 row in set (0.00 sec)

CURRENT_DATE()和NOW()的区别前者只显示年月日，没有显示时间。后者显示了时间

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2013-10-04 15:19:02 |
+---------------------+1 row in set (0.00 sec)
1 row in set (0.00 sec)

显示当前登录的用户，如下表示我用root从本地登录。

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

显示一个数据库的所有表

mysql> SHOW TABLES IN mysql;     
+---------------------------+
| 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.10 sec)
24 rows in set (0.10 sec)

DESCRIBE tablename显示表详细信息，可以简写为DESC TABLE_NAME

mysql> use mysql           
Database changed
mysql> DESCRIBE db;  
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+22 rows in set (0.00 sec)
22 rows in set (0.00 sec)

时间:2013-10-04 03:10:02 作者:beebol 标签: 分类: Mysql