MySQL CMA Zertifikat: Kapitel 20.3

20.3 Using SHOW and DESCRIBE to Obtain Metadata

Datenbanken anzeigen: 

mysql> SHOW DATABASES;
+---------------------------+
| Database                  |
+---------------------------+
| information_schema        | 
| lokal                     | 
| lokal2                    | 
| mysql                     | 
| programm                  | 
| t3_gus                    | 
| t3_redaktion              | 
+---------------------------+

Tabellen einer Datenbank anzeigen lassen (wenn man mit USE eine Verbindung zu einer bestimmten Datenbank hat, so reicht ein einfache SHOW TABLES aus): 

mysql> SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              | 
| db                        | 
| func                      | 
| help_category             | 
| help_keyword              | 
| help_relation             | 
| help_topic                | 
| host                      | 
| tables_priv               | 
| time_zone                 | 
| time_zone_leap_second     | 
+---------------------------+

Spalten einer Tabelle anzeigen lassen: 

mysql> SHOW COLUMNS FROM host;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       | 
| Db                    | char(64)      | 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       |       | 
+-----------------------+---------------+------+-----+---------+-------+

Genauere Spalteninformationen: 

mysql> SHOW FULL COLUMNS FROM host\G;
*************************** 1. row ***************************
     Field: Host
      Type: char(60)
 Collation: utf8_bin
      Null: NO
       Key: PRI
   Default: 
     Extra: 
Privileges: select,insert,update,references
   Comment: 
*************************** 2. row ***************************
     Field: Db
      Type: char(64)
 Collation: utf8_bin
      Null: NO
       Key: PRI
   Default: 
     Extra: 
Privileges: select,insert,update,references
   Comment:

Keys einer Tabelle anzeigen: 

mysql> SHOW KEYS FROM host\G;
*************************** 1. row ***************************
       Table: host
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: Host
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: host
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 2
 Column_name: Db
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
2 rows in set (0.00 sec)

"Suchfunktion" mit SHOW DATABASES LIKE 'xxx%'. Prozent ist Platzhalter: 

mysql> SHOW DATABASES LIKE '%en%';
+-------------------+
| Database (%en%)   |
+-------------------+
| web_dosenkoeche_1 | 
| web_zuhoeren_1    | 
| web_zuhoeren_2    | 
+-------------------+

Verfügbare Zeichensätze anzeigen: 

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 | 
....
....

Verfügbare Sortierreihenfolgen anzeigen lassen: 

mysql> SHOW COLLATION;
+----------------------+----------+-----+---------+----------+---------+
| Collation            | Charset  | Id  | Default | Compiled | Sortlen |
+----------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci      | big5     |   1 | Yes     | Yes      |       1 | 
| big5_bin             | big5     |  84 |         | Yes      |       1 | 
| dec8_swedish_ci      | dec8     |   3 | Yes     | Yes      |       1 | 
| dec8_bin             | dec8     |  69 |         | Yes      |       1 | 
| cp850_general_ci     | cp850    |   4 | Yes     | Yes      |       1 |