MySQL Tools Comparison.

  • Увеличить размер шрифта
  • Размер шрифта по умолчанию
  • Уменьшить размер шрифта
Home SHOW DATABASE информация SHOW and DESCRIBE Statements

SHOW and DESCRIBE Statements

Печать PDF
Рейтинг пользователей: / 0
ХудшийЛучший 

Using Table-Related SHOW Statements

We can use the SHOW COLUMNS statement to lists the columns in a table, along with information about the columns.

Syntax
SHOW [FULL] COLUMNS FROM <table name> [FROM <database name>] [LIKE '<value>']

FULL keyword shows more complete information about each column, and LIKE can be used to limit the values returned.

Code Sample:

USE sakilakubili;
SHOW COLUMNS FROM film_review;
SHOW COLUMNS FROM user FROM mysql LIKE 'max%';

You can also retrieve construction information of a table by using a SHOW CREATE TABLE statement, which shows the actual table definition.

Code Sample:

USE sakilakubili;
SHOW CREATE TABLE film_review;

Note::The entire results may not fit on the screen and what you see depends on your system.

The next statement displays a list of indexes in a table. The output SHOW INDEX statement is shown below.

Code Sample:

USE sakila;
SHOW INDEX FROM film_review;
SHOW INDEX FROM user FROM mysql;
+-------------+------------+---------+-------------+-----------
| Table | Non_unique | Key_name| Seq_in_index| Column_name
Collation | Cardinality | ... | Null | Index_type | Comment |
+-------------+------------+---------+-------------+-----------
| film_review | 0 | PRIMARY | 1| review_id
A | 0 | ... | | BTREE | |
+-------------+------------+---------+-------------+-----------
1 row in set (0.00 sec)

The next statement is the SHOW TABLES statement, which displays a list of tables in the current database or a specified database.

Code Sample:

USE sakila;
SHOW TABLES;
SHOW TABLES FROM mysql LIKE 'time%';
+------------------------+
| Tables_in_sakilakubili |
+------------------------+
| film_review |
+------------------------+
1 row in set (0.00 sec)

Using DESCRIBE Statement

DESCRIBE is another statement useful for viewing table information. The following example shows a DESCRIBE statement that returns information about all columns in the user table that end with "priv":

DESCRIBE user '%priv';

Code Sample:

SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sakila |
| test |
+--------------------+

4 rows in set (0.11 sec)

Code Sample:

SHOW CREATE DATABASE sakilakubili;

As we work through this courseware, these statements will be used repeatedly to make our lives easier.

INFORMATION_SCHEMA Tables

INFORMATION_SCHEMA is the information database in MySQL server that provides access to database metadata, or data about the data, such as a table name, the constraints on a column, or access privileges. The same informationis also referred to as data dictionary and system catalog.

Here is an example to list all the tables in sakila database, in reverse alphabetical order:

Code Sample:

SELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = 'sakila'
ORDER BY table_name DESC;
+----------------------------+------------+--------+
| table_name | table_type | engine |
+----------------------------+------------+--------+
| actor | BASE TABLE | InnoDB |
| actor_info | VIEW | NULL |
| ... | | ... |
| store | BASE TABLE | InnoDB |
| studio | BASE TABLE | InnoDB |
+----------------------------+------------+--------+
26 rows in set (0.20 sec)

Note:Users prefer to use both SHOW and INFORMATION_SCHEMA

The following example lists the various tables found in INFORMATION_SCHEMA.

Code Sample:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'INFORMATION_SCHEMA'
AND table_name LIKE '%';

Showing Databases

Here is a read of another table which shows that the following statements are equivalent:

SELECT SCHEMA_NAME AS `Database`
FROM INFORMATION_SCHEMA.SCHEMATA
[WHERE SCHEMA_NAME LIKE '
wild
']

SHOW DATABASES [LIKE '
wild
']

Code Sample:

SELECT SCHEMA_NAME AS `Database`
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE 'sakila%';

SHOW DATABASES LIKE 'sakila%';

Show Database Information Conclusion

This lesson covered the mechanisms to create a database and some components.

To continue to learn MySQL go to the top of this page and click on the next lesson in this MySQL Tutorial's Table of Contents.
 

Tunnel-heidisql

-

 

 

I used HTTPtunnel GNU package for the organization HTTP tunnel with HeidiSQL. Read more...

'mysql.proc' doesn't exist fixed problem

_

Table 'mysql.proc' doesn't exist. Fix error 1146 Table doesn't exist here...