• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / Listing tables and their structure with the MySQL Command Line Client

Listing tables and their structure with the MySQL Command Line Client

The MySQL Command Line client allows you to run sql queries from the a command line interface. This post looks at how to show the tables in a particular database and describe their structure. This is the continuation of a series about the MySQL Command Line client. Previous posts include Using the MySQL command line tool and Running queries from the MySQL Command Line.

After logging into the MySQL command line client and selecting a database, you can list all the tables in the selected database with the following command:

mysql> show tables;

(mysql> is the command prompt, and "show tables;" is the actual query in the above example).

In a test database I have set up, this returns the following:

+----------------+
| Tables_in_test |
+----------------+
| something      |
| something_else |
+----------------+
2 rows in set (0.00 sec)

This shows us there are two tables in the database called "something" and "something_else". We can show the structure of the table using the "desc" command like so for the "something" table:

mysql> desc something;

My test database table returns a result like so, showing there are 4 columns and what types etc they are:

+--------------+------------------+------+-----+-------------------+----------------+
| Field        | Type             | Null | Key | Default           | Extra          |
+--------------+------------------+------+-----+-------------------+----------------+
| something_id | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| name         | varchar(50)      | NO   |     | NULL              |                |
| value        | varchar(50)      | NO   |     | NULL              |                |
| ts_updated   | timestamp        | YES  | MUL | CURRENT_TIMESTAMP |                |
+--------------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

Finally, you can show the indexes from a particular table like so:

mysql> show keys from something;

My test database has two indexes (these are labelled in the "key" column from the "desc something" output above as PRI and MUL). The output from the above command looks like this:

+-----------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name   | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| something |          0 | PRIMARY    |            1 | something_id | A         |           2 |     NULL | NULL   |      | BTREE      | NULL    |
| something |          1 | ts_updated |            1 | ts_updated   | A         |        NULL |     NULL | NULL   |      | BTREE      | NULL    |
+-----------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

Summary

The MySQL Command Line client is useful for running queries as well as displaying what tables are in a MySQL database, the structure of those tables and the indexes in those tables as covered in this post.

Check Out These Related posts:

  1. MySQL utility commands
  2. Show indexes for a table with MySQL
  3. Backing up MySQL with mysqldump
  4. Listing MySQL tables with PHP

Filed Under: MySql

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap