脱离企业战略 IT曲线救国也能成功-信息化专区

脱离企业战略 IT曲线救国也能成功

作者：IT168 周卫丰编辑：赵建凯 2007-06-29 12:28

2 使用数据库

mysql> USE INFORMATION_SCHEMA;
Database changed

mysql> SELECT DATABASE();
+--------------------+
| database()         |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT VERSION(), CURRENT_DATE, CURDATE(), NOW(), USER();
+---------------------+--------------+------------+---------------------+-------
---------+
| VERSION()           | CURRENT_DATE | CURDATE()  | NOW()               | USER()
         |
+---------------------+--------------+------------+---------------------+-------
---------+
| 5.0.27-community-nt | 2007-05-24   | 2007-05-24 | 2007-05-24 17:01:16 | root@l
ocalhost |
+---------------------+--------------+------------+---------------------+-------
---------+
1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'version';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| version       | 5.0.27-community-nt |
+---------------+---------------------+
1 row in set (0.13 sec)

3 MYSQL的SHOW命令

前边的例子中我们已经用过了MYSQL的SHOW命令:

mysql> SHOW DATABASES;

SHOW命令可用于获取关于INFORMATION_SCHEMA本身结构的信息。

一些SHOW语句允许使用FROM、WHERE子句，这样，在指定需要显示的行时，可更为灵活。下边给出部分例子：

mysql> SHOW TABLES FROM MYSQL;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)

mysql> SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
16 rows in set (0.00 sec)

SHOW TABLES命令显示了当前用数据库中的数据库对象列表，而从TABLES视图的查询我们将得到所有数据库下的对象列表。这个例子就是给出了一个查询MYSQL的表相关的系统视图，类似ORACLE中的(DBA_TABLES、USER_TABLES)和SYBASE中的SYSOBJECTS。

mysql> SELECT TABLE_NAME, TABLE_TYPE, ENGINE FROM TABLES;
+---------------------------------------+-------------+--------+
| table_name                            | table_type  | engine |
+---------------------------------------+-------------+--------+
| CHARACTER_SETS                        | SYSTEM VIEW | MEMORY |
| COLLATIONS                            | SYSTEM VIEW | MEMORY |
| COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | MEMORY |
| COLUMNS                               | SYSTEM VIEW | MyISAM |
| COLUMN_PRIVILEGES                     | SYSTEM VIEW | MEMORY |
| KEY_COLUMN_USAGE                      | SYSTEM VIEW | MEMORY |
| ROUTINES                              | SYSTEM VIEW | MyISAM |
| SCHEMATA                              | SYSTEM VIEW | MEMORY |
| SCHEMA_PRIVILEGES                     | SYSTEM VIEW | MEMORY |
| STATISTICS                            | SYSTEM VIEW | MEMORY |
| TABLES                                | SYSTEM VIEW | MEMORY |
| TABLE_CONSTRAINTS                     | SYSTEM VIEW | MEMORY |
| TABLE_PRIVILEGES                      | SYSTEM VIEW | MEMORY |
| TRIGGERS                              | SYSTEM VIEW | MyISAM |
| USER_PRIVILEGES                       | SYSTEM VIEW | MEMORY |
| VIEWS                                 | SYSTEM VIEW | MyISAM |
| columns_priv                          | BASE TABLE  | MyISAM |
| db                                    | BASE TABLE  | MyISAM |
| func                                  | BASE TABLE  | MyISAM |
| help_category                         | BASE TABLE  | MyISAM |
| help_keyword                          | BASE TABLE  | MyISAM |
| help_relation                         | BASE TABLE  | MyISAM |
| help_topic                            | BASE TABLE  | MyISAM |
| host                                  | BASE TABLE  | MyISAM |
| proc                                  | BASE TABLE  | MyISAM |
| procs_priv                            | BASE TABLE  | MyISAM |
| tables_priv                           | BASE TABLE  | MyISAM |
| time_zone                             | BASE TABLE  | MyISAM |
| time_zone_leap_second                 | BASE TABLE  | MyISAM |
| time_zone_name                        | BASE TABLE  | MyISAM |
| time_zone_transition                  | BASE TABLE  | MyISAM |
| time_zone_transition_type             | BASE TABLE  | MyISAM |
| user                                  | BASE TABLE  | MyISAM |
+---------------------------------------+-------------+--------+
33 rows in set (0.03 sec)

mysql> SHOW COLUMNS FROM TABLES;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512) | YES  |     | NULL    |       |
| TABLE_SCHEMA    | varchar(64)  | NO   |     |         |       |
| TABLE_NAME      | varchar(64)  | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)  | NO   |     |         |       |
| ENGINE          | varchar(64)  | YES  |     | NULL    |       |
| VERSION         | bigint(21)   | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)  | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21)   | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21)   | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21)   | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21)   | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21)   | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21)   | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21)   | YES  |     | NULL    |       |
| CREATE_TIME     | datetime     | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime     | YES  |     | NULL    |       |
| CHECK_TIME      | datetime     | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(64)  | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21)   | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255) | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(80)  | NO   |     |         |       |
+-----------------+--------------+------+-----+---------+-------+
21 rows in set (0.06 sec)

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
36 rows in set (0.00 sec)

mysql> SHOW CHARACTER SET like 'big5';
+---------+--------------------------+-------------------+--------+
| Charset | Description              | Default collation | Maxlen |
+---------+--------------------------+-------------------+--------+
| big5    | Big5 Traditional Chinese | big5_chinese_ci   |      2 |
+---------+--------------------------+-------------------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM COLLATIONS WHERE COLLATION_NAME LIKE '%big5%';
+-----------------+--------------------+----+------------+-------------+--------
-+
| COLLATION_NAME  | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN
 |
+-----------------+--------------------+----+------------+-------------+--------
-+
| big5_chinese_ci | big5               |  1 | Yes        | Yes         |       1
 |
| big5_bin        | big5               | 84 |            | Yes         |       1
 |
+-----------------+--------------------+----+------------+-------------+--------
-+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS;
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| Grants for root@localhost
                                                         |
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*DA2
8842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB' WITH GRANT OPTION |
+-------------------------------------------------------------------------------
---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR ROOT;
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
| Grants for root@%

       |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,
 FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,
 LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'%
' IDENTIFIED BY PASSWORD '*DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB' WITH GRANT
OPTION |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
1 row in set (0.00 sec)

我们也可以通过查询系统表来获得用户的权限：

mysql> SELECT * FROM USER_PRIVILEGES;

第1页：MySQL安装第2页：登录及一些基本操作第3页：使用数据库第4页：MYSQL的权限第5页：创建用户数据库第6页：创建数据库对象第7页：索引第8页：创建存储过程第9页：创建函数第10页：创建视图和触发器

关注我们