SHOW DATABASES
Dupa crearea unei baze de date, poate fi verificata existenta ei pe server prin intermediul instructiunii SHOW DATABASES; aceasta instructiune listeaza toate bazele de date prezente pe server. Sunt afisate bazele de date care corespund nivelului de acces al utilizatorului:
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | lefti_stud | | sakila | | starwars | | universitate | +--------------------+ 5 rows in set
Instructiunile de tip SHOW sunt disponibile pentru sistemele de baze de date de tip MySQL. Instructiunea SHOW DATABASES prezinta urmatoarea sintaxa:
SHOW {DATABASES | SCHEMAS} [LIKE model]
SHOW SCHEMAS este un sinonim pentru instructiunea SHOW DATABASES.
Daca este prezenta clauza LIKE, aceasta indica un model pentru numele bazelor de date care urmeaza a fi listate. De exemplu, listarea numelor de baze de date care incep cu litera s.
mysql> SHOW DATABASES LIKE 's%'; +---------------+ | Database (s%) | +---------------+ | sakila | | starwars | +---------------+ 2 rows in set
SHOW TABLES
Instructiunea SHOW TABLES afiseaza tabelele netemporare dintr-o baza de date. Instructiunea listeaza si vizualizarile dintr-o baza de date.
SHOW [FULL] TABLES [{FROM | IN} nume_baza_date] [LIKE model | WHERE expresie]
Daca nu este precizata nici o baza de date in instructiunea SHOW TABLES, atunci sunt listate tabelele din baza de date activa.
mysql> USE sakila Database changed mysql> SELECT database(); +------------+ | database() | +------------+ | sakila | +------------+ 1 row in set
Rezultatele furnizate prin intermediul instructiunii SHOW TABLES sunt identice cu cele obtinute prin apelarea utilitarului mysqlshow, daca acesta are ca si parametru numele unei baze de date de pe server.
mysql> SHOW TABLES; +----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | | view_email_staff | | view_films_catagory | +----------------------------+ 25 rows in set
mysqlshow -h data.uncoded.ro -u airman -p sakila Enter password: *********** Database: sakila +----------------------------+ | Tables | +----------------------------+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | | view_email_staff | | view_films_catagory | +----------------------------+
Clauza FULL introduce o noua coloana la afisare, in care pot fi specificate urmatoarele valori: BASE TABLE pentru tabele, respectiv VIEW pentru vizualizari.
mysql> SHOW FULL TABLES -> FROM sakila; +----------------------------+------------+ | Tables_in_sakila | Table_type | +----------------------------+------------+ | actor | BASE TABLE | | actor_info | VIEW | | address | BASE TABLE | | category | BASE TABLE | | city | BASE TABLE | | country | BASE TABLE | | customer | BASE TABLE | | customer_list | VIEW | | film | BASE TABLE | | film_actor | BASE TABLE | | film_category | BASE TABLE | | film_list | VIEW | | film_text | BASE TABLE | | inventory | BASE TABLE | | language | BASE TABLE | | nicer_but_slower_film_list | VIEW | | payment | BASE TABLE | | rental | BASE TABLE | | sales_by_film_category | VIEW | | sales_by_store | VIEW | | staff | BASE TABLE | | staff_list | VIEW | | store | BASE TABLE | | view_email_staff | VIEW | | view_films_catagory | VIEW | +----------------------------+------------+ 25 rows in set
Daca este prezenta, clauza LIKE introduce un model pentru numele tabelelor, astfel incat vor fi listate doar numele tabelelor care corespund modelului.
mysql> SHOW TABLES -> FROM sakila -> LIKE 'actor%'; +---------------------------+ | Tables_in_sakila (actor%) | +---------------------------+ | actor | | actor_info | +---------------------------+ 2 rows in set
In plus, clauza WHERE poate fi utilizata in instructiuni SHOW TABLES pentru a specifica conditii mult mai generale de selectare a inregistrarilor care urmeaza a fi afisate.
mysql> SHOW TABLES -> FROM sakila -> WHERE tables_in_sakila REGEXP 'actor|film'; +----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | film | | film_actor | | film_category | | film_list | | film_text | | nicer_but_slower_film_list | | sales_by_film_category | | view_films_catagory | +----------------------------+ 10 rows in set
mysql> SHOW TABLES -> FROM sakila -> WHERE tables_in_sakila REGEXP '^(actor)|(actor)$'; +------------------+ | Tables_in_sakila | +------------------+ | actor | | actor_info | | film_actor | +------------------+ 3 rows in set
SHOW COLUMNS
Instructiunea SHOW COLUMNS afiseaza informatii despre coloanele dintr-un tabel. Acesta instructiune poate fi utilizata si in cazul vizualizarilor.
SHOW [FULL] COLUMNS {FROM | IN} nume_tabel [{FROM | IN} nume_baza_date] [LIKE model | WHERE expresie]
mysql> USE sakila Database changed mysql> SHOW COLUMNS -> FROM actor; +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ | actor_id | smallint unsigned | NO | PRI | NULL | auto_increment | | first_name | varchar(45) | NO | | NULL | | | last_name | varchar(45) | NO | MUL | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ 4 rows in set
Daca a fost stabilita o baza de date activa, nu este necesara precizarea numelui bazei de date, intr-o instructiune SHOW COLUMNS.
mysql> SHOW COLUMNS -> FROM sakila.city; +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ | city_id | smallint unsigned | NO | PRI | NULL | auto_increment | | city | varchar(50) | NO | | NULL | | | country_id | smallint unsigned | NO | MUL | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ 4 rows in set
Instructiunea de mai sus poate fi rescrisa in mai multe moduri. Se poate observa ca nume_baza_date.nume_tabel reprezinta o alternativa pentru nume_tabel {FROM | IN} nume_baza_date.
SHOW COLUMNS FROM city FROM sakila; SHOW COLUMNS FROM city IN sakila; SHOW COLUMNS IN city IN sakila; SHOW COLUMNS IN city FROM sakila; SHOW COLUMNS IN sakila.city;
Daca este utilizata, clauza FULL afiseza informatii suplimentare, precum: setul de caractere si comentariile utilizate pentru fiecare coloana sau privilegiile corespunzatoare unui utilizator, pentru fiecare coloana.
mysql> SHOW FULL COLUMNS -> FROM sakila.city; +-------------+-------------------+--------------------+------+-----+-------------------+-----------------------------------------------+------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------------+-------------------+--------------------+------+-----+-------------------+-----------------------------------------------+------------+---------+ | city_id | smallint unsigned | NULL | NO | PRI | NULL | auto_increment | select | | | city | varchar(50) | utf8mb4_0900_ai_ci | NO | | NULL | | select | | | country_id | smallint unsigned | NULL | NO | MUL | NULL | | select | | | last_update | timestamp | NULL | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | select | | +-------------+-------------------+--------------------+------+-----+-------------------+-----------------------------------------------+------------+---------+ 4 rows in set
Coloanele unei tabele mai pot fi listate folosind comanda corespunzatoare utilitarului mysqlshow, care primeste un argument de tipul nume_baza_date nume_tabel.
mysqlshow -h data.uncoded.ro -u airman -p sakila city Enter password: *********** Database: sakila Table: city +-------------+-------------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------------+-------------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+---------+ | city_id | smallint unsigned | | NO | PRI | | auto_increment | select,insert,update,references | | | city | varchar(50) | utf8mb4_0900_ai_ci | NO | | | | select,insert,update,references | | | country_id | smallint unsigned | | NO | MUL | | | select,insert,update,references | | | last_update | timestamp | | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | select,insert,update,references | | +-------------+-------------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+---------+
SHOW FIELDS este un sinonim pentru instructiunea SHOW COLUMNS.
Instructiunea SHOW COLUMNS afiseaza urmatoarele valori pentru fiecare coloana a unui tabel:
• Field: indica numele coloanei;
• Type: specifica tipul de date pentru fiecare coloana;
• Null: poate contine doua valori: YES, daca pot fi stocate valori de tip NULL in coloana, si NO, in caz contrar;
• Key: precizeaza daca o coloana este indexata;
• Default: indica valoarea implicita pentru fiecare coloana;
• Extra: contine informatii suplimentare cu privire la o coloana, precum AUTO_INCREMENT si ON UPDATE CURRENT_TIMESTAMP.
Clauza LIKE introduce un model pentru numele coloanelor, iar clauza WHERE ofera posibilitatea utilizarii unor conditii mult mai generale de selectare a inregistrarilor care urmeaza a fi afisate.
mysql> SHOW COLUMNS -> IN sakila.actor -> LIKE '%name' ; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | first_name | varchar(45) | NO | | NULL | | | last_name | varchar(45) | NO | MUL | NULL | | +------------+-------------+------+-----+---------+-------+ 2 rows in set
DESCRIBE
Pentru a obtine informatii cu privire la coloanele dintru-un tabel, poate fi utilizata instructiunea DESCRIBE, care prezinta urmatoarea sintaxa:
{DESCRIBE | DESC} nume_tabel [nume_coloana | wild-card]
DESCRIBE reprezinta o alta forma a instructiunii SHOW COLUMNS si este furnizata pentru a asigura compatibilitatea cu sistemul de gestiune a bazelor de date ORACLE. Instructiunea DESCRIBE permite si afisarea informatiilor corespunzatoare vizualizarilor.
mysql> DESCRIBE sakila.actor; +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ | actor_id | smallint unsigned | NO | PRI | NULL | auto_increment | | first_name | varchar(45) | NO | | NULL | | | last_name | varchar(45) | NO | MUL | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ 4 rows in set
In instructiunea DESCRIBE poate fi precizat numele unei coloane, sau un sir, care contine caractere speciale de tip wild-card (“%”, “_”) pentru a afisa doar anumite coloane.
mysql> DESCRIBE sakila.actor first_name; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | first_name | varchar(45) | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 1 row in set
mysql> DESCRIBE sakila.actor '%name'; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | first_name | varchar(45) | NO | | NULL | | | last_name | varchar(45) | NO | MUL | NULL | | +------------+-------------+------+-----+---------+-------+ 2 rows in set