Database:MariaDB show syntax

From Juneday education
Jump to: navigation, search

About

This page shows the syntax in MariaDB for investigating databases and tables. The syntax differs from that of SQLite, so we'll give you a quick reference below.

Show tables

To see what tables exist in the current database in MariaDB, you use the SHOW TABLES syntax.

Show columns

To see the column definitions, you can use the SHOW COLUMNS syntax. A short cut for SHOW COLUMNS is the DESC syntax.

Show databases

To show the databases on the MariaDB server, you use the SHOW DATABASES syntax.

SHOW CREATE TABLE

To see the SQL for the definition of a table (how the table was created), you use the SHOW CREATE TABLE syntax.

Examples

MariaDB [my_reddit]> show tables;
+---------------------+
| Tables_in_my_reddit |
+---------------------+
| links               |
+---------------------+
1 row in set (0.01 sec)

MariaDB [my_reddit]> show tables like 'l%';
+--------------------------+
| Tables_in_my_reddit (l%) |
+--------------------------+
| links                    |
+--------------------------+
1 row in set (0.00 sec)

MariaDB [my_reddit]> show columns from links;
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| id    | int(11)       | NO   | PRI | NULL    | auto_increment |
| url   | varchar(2083) | NO   |     | NULL    |                |
+-------+---------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

MariaDB [my_reddit]> show columns from links where Type LIKE 'int%';
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

MariaDB [my_reddit]> DESC links;
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| id    | int(11)       | NO   | PRI | NULL    | auto_increment |
| url   | varchar(2083) | NO   |     | NULL    |                |
+-------+---------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

MariaDB [my_reddit]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_reddit          |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [my_reddit]> show create table links;
+-------+---------------------------------------------------+
| Table | Create Table                                      |
+-------+---------------------------------------------------+
| links | CREATE TABLE `links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(2083) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4    |
+-------+---------------------------------------------------+
1 row in set (0.00 sec)

Further reading

Here's a good article on Linux Journal on comparing the command line interfaces of various databases.