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
mysql> use test
Database changed
mysql>
mysql> SHOW TABLES;
Empty set (0.02 sec)
Show a detailed listing of the columns of your database.
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
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.
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)
SHOW INDEX FROM tbl_name
CREATE INDEX indexname ON tablename(columnnamelist);
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>
To see the structure or schema of a database
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>
Use the SHOW statement to find out what databases currently exist on the server:
mysql>
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql>
mysql>
The mysql database describes user access privileges.
The test database is for users to try things out.
