MySQL Tutorial/MySQL Utilities/SHOW

Материал из SQL эксперт
Перейти к: навигация, поиск

Display all table names

   <source lang="sql">

mysql> use test Database changed mysql> mysql> SHOW TABLES; Empty set (0.02 sec)</source>


Show a detailed listing of the columns of your database.

   <source lang="sql">

mysql> mysql> mysql> use mysql; Database changed mysql> SHOW COLUMNS FROM user; +-----------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | 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 | | | Reload_priv | enum("N","Y") | NO | | N | | | Shutdown_priv | enum("N","Y") | NO | | N | | | Process_priv | enum("N","Y") | NO | | N | | | File_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 | | | Show_db_priv | enum("N","Y") | NO | | N | | | Super_priv | enum("N","Y") | NO | | N | | | Create_tmp_table_priv | enum("N","Y") | NO | | N | | | Lock_tables_priv | enum("N","Y") | NO | | N | | | Execute_priv | enum("N","Y") | NO | | N | | | Repl_slave_priv | enum("N","Y") | NO | | N | | | Repl_client_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 | | | Create_user_priv | enum("N","Y") | NO | | N | | | ssl_type | enum("","ANY","X509","SPECIFIED") | NO | | | | | ssl_cipher | blob | NO | | | | | x509_issuer | blob | NO | | | | | x509_subject | blob | NO | | | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | +-----------------------+-----------------------------------+------+-----+---------+-------+ 37 rows in set (0.02 sec) mysql> use test; Database changed</source>


SHOW COLUMNS and DESCRIBE can display information about the columns in individual INFORMATION_SCHEMA tables.

Several SHOW statements have been extended to allow a WHERE clause:

  1. SHOW CHARACTER SET
  2. SHOW COLLATION
  3. SHOW COLUMNS
  4. SHOW DATABASES
  5. SHOW FUNCTION STATUS
  6. SHOW KEYS
  7. SHOW OPEN TABLES
  8. SHOW PROCEDURE STATUS
  9. SHOW STATUS
  10. SHOW TABLE STATUS
  11. SHOW TABLES
  12. SHOW VARIABLES

The WHERE clause is evaluated against the column names displayed by the SHOW statement.

SHOW CHARACTER SET;

SHOW CHARACTER SET WHERE `Default collation` LIKE "%japanese%";

This statement displays the multi-byte character sets:

SHOW CHARACTER SET WHERE Maxlen > 1;

26. 10. SHOW 26. 10. 1. SHOW COLUMNS and DESCRIBE can display information about the columns in individual INFORMATION_SCHEMA tables. 26. 10. 2. <A href="/Tutorial/MySQL/0520__MySQL-Utilities/UsetheSHOWstatementtofindoutwhatdatabasescurrentlyexistontheserver.htm">Use the SHOW statement to find out what databases currently exist on the server:</a> 26. 10. 3. <A href="/Tutorial/MySQL/0520__MySQL-Utilities/Displayalltablenames.htm">Display all table names</a> 26. 10. 4. <A href="/Tutorial/MySQL/0520__MySQL-Utilities/SHOWCREATETABLEstatement.htm">SHOW CREATE TABLE statement.</a> 26. 10. 5. <A href="/Tutorial/MySQL/0520__MySQL-Utilities/SHOWINDEXFROMtblname.htm">SHOW INDEX FROM tbl_name</a> 26. 10. 6. <A href="/Tutorial/MySQL/0520__MySQL-Utilities/Toseethestructureorschemaofadatabase.htm">To see the structure or schema of a database</a> 26. 10. 7. <A href="/Tutorial/MySQL/0520__MySQL-Utilities/Showadetailedlistingofthecolumnsofyourdatabase.htm">Show a detailed listing of the columns of your database.</a>

SHOW CREATE TABLE statement.

   <source lang="sql">

mysql> mysql> mysql> CREATE TABLE Employee( 

   ->     id            int,
   ->     first_name    VARCHAR(15),
   ->     last_name     VARCHAR(15),
   ->     start_date    DATE,
   ->     end_date      DATE,
   ->     salary        FLOAT(8,2),
   ->     city          VARCHAR(10),
   ->     description   VARCHAR(15)
   -> );

Query OK, 0 rows affected (0.02 sec) mysql> mysql> mysql> show create table Employee; +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Employee | CREATE TABLE `employee` ( 

 `id` int(11) default NULL,
 `first_name` varchar(15) default NULL,
 `last_name` varchar(15) default NULL,
 `start_date` date default NULL,
 `end_date` date default NULL,
 `salary` float(8,2) default NULL,
 `city` varchar(10) default NULL,
 `description` varchar(15) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec)</source>


SHOW INDEX FROM tbl_name

CREATE INDEX indexname ON tablename(columnnamelist);



   <source lang="sql">

mysql> mysql> mysql> mysql> mysql> mysql> mysql> CREATE TABLE Employee( 

   ->     id            int,
   ->     first_name    VARCHAR(15),
   ->     last_name     VARCHAR(15),
   ->     start_date    DATE,
   ->     end_date      DATE,
   ->     salary        FLOAT(8,2),
   ->     city          VARCHAR(10),
   ->     description   VARCHAR(15)
   -> );

Query OK, 0 rows affected (0.01 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");

Query OK, 1 row affected (0.02 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> mysql> CREATE INDEX id_index ON employee(id); Query OK, 8 rows affected (0.02 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> mysql> show index from employee; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | employee | 1 | id_index | 1 | id | A | NULL | NULL | NULL | YES | BTREE | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec) mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.01 sec) mysql> mysql></source>


To see the structure or schema of a database

   <source lang="sql">

mysql> 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> mysql> use test; Database changed mysql> mysql></source>


Use the SHOW statement to find out what databases currently exist on the server:

   <source lang="sql">

mysql> mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.00 sec) mysql> mysql></source>


The mysql database describes user access privileges.

The test database is for users to try things out.

Источник — «http://sqle.ru/index.php?title=MySQL_Tutorial/MySQL_Utilities/SHOW&oldid=20»