MySQL Tutorial/MySQL Utilities/SHOW
Содержание
- 1 Display all table names
- 2 Show a detailed listing of the columns of your database.
- 3 SHOW COLUMNS and DESCRIBE can display information about the columns in individual INFORMATION_SCHEMA tables.
- 4 SHOW CREATE TABLE statement.
- 5 SHOW INDEX FROM tbl_name
- 6 To see the structure or schema of a database
- 7 Use the SHOW statement to find out what databases currently exist on the server:
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:
- SHOW CHARACTER SET
- SHOW COLLATION
- SHOW COLUMNS
- SHOW DATABASES
- SHOW FUNCTION STATUS
- SHOW KEYS
- SHOW OPEN TABLES
- SHOW PROCEDURE STATUS
- SHOW STATUS
- SHOW TABLE STATUS
- SHOW TABLES
- 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.