More MySQL tips for viewing data in a database and listing the columns in a table.

In this example; we are using the old and crusty Northwind database in the latest MySQL database engine.

Here I am showing the tables in the database.

mysql> show tables in nw_old;
+------------------+
| Tables_in_nw_old |
+------------------+
| categories       |
| customers        |
| employees        |
| order_details    |
| orders           |
| products         |
| shippers         |
| suppliers        |
+------------------+
8 rows in set (0.00 sec)

But if I want to view the columns in a specific table; then I need this SQL query.

mysql> show columns in orders;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| OrderID        | int(11)     | NO   | PRI | NULL    | auto_increment |
| CustomerID     | varchar(5)  | YES  |     | NULL    |                |
| EmployeeID     | int(11)     | YES  |     | NULL    |                |
| OrderDate      | date        | YES  |     | NULL    |                |
| RequiredDate   | date        | YES  |     | NULL    |                |
| ShippedDate    | date        | YES  |     | NULL    |                |
| ShipVia        | int(11)     | YES  |     | NULL    |                |
| Freight        | float(1,0)  | YES  |     | 0       |                |
| ShipName       | varchar(40) | YES  |     | NULL    |                |
| ShipAddress    | varchar(60) | YES  |     | NULL    |                |
| ShipCity       | varchar(15) | YES  |     | NULL    |                |
| ShipRegion     | varchar(15) | YES  |     | NULL    |                |
| ShipPostalCode | varchar(10) | YES  |     | NULL    |                |
| ShipCountry    | varchar(15) | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
14 rows in set (0.00 sec)

This example shows us using AND in a query to combine two queries into one.

mysql> select * from customers where city = "Warszawa" and contactTitle = "Owner";
+------------+----------------+-------------------------+--------------+-----------------+----------+--------+------------+---------+---------------+---------------+
| CustomerID | CompanyName    | ContactName             | ContactTitle | Address         | City     | Region | PostalCode | Country | Phone         | Fax           |
+------------+----------------+-------------------------+--------------+-----------------+----------+--------+------------+---------+---------------+---------------+
| WOLZA      | Wolski  Zajazd | Zbyszek Piestrzeniewicz | Owner        | ul. Filtrowa 68 | Warszawa |        | 01-012     | Poland  | (26) 642-7012 | (26) 642-7012 |
+------------+----------------+-------------------------+--------------+-----------------+----------+--------+------------+---------+---------------+---------------+
1 row in set (0.00 sec)

And this query is specifying a certain date range to return information.

mysql> select * from orders where OrderDate < "1996-07-11";
+---------+------------+------------+------------+--------------+-------------+---------+---------+---------------------------+----------------------+----------------+------------+----------------+-------------+
| OrderID | CustomerID | EmployeeID | OrderDate  | RequiredDate | ShippedDate | ShipVia | Freight | ShipName                  | ShipAddress          | ShipCity       | ShipRegion | ShipPostalCode | ShipCountry |
+---------+------------+------------+------------+--------------+-------------+---------+---------+---------------------------+----------------------+----------------+------------+----------------+-------------+
|   10248 | VINET      |          5 | 1996-07-04 | 1996-08-01   | 1996-07-16  |       3 |       0 | Vins et alcools Chevalier | 59 rue de l'Abbaye   | Reims          |            | 51100          | France      |
|   10249 | TOMSP      |          6 | 1996-07-05 | 1996-08-16   | 1996-07-10  |       1 |       0 | Toms Spezialit?ten        | Luisenstr. 48        | M?nster        |            | 44087          | Germany     |
|   10250 | HANAR      |          4 | 1996-07-08 | 1996-08-05   | 1996-07-12  |       2 |       0 | Hanari Carnes             | Rua do Pa?o, 67      | Rio de Janeiro | RJ         | 05454-876      | Brazil      |
|   10251 | VICTE      |          3 | 1996-07-08 | 1996-08-05   | 1996-07-15  |       1 |       0 | Victuailles en stock      | 2, rue du Commerce   | Lyon           |            | 69004          | France      |
|   10252 | SUPRD      |          4 | 1996-07-09 | 1996-08-06   | 1996-07-11  |       2 |       0 | Supr?mes d?lices          | Boulevard Tirou, 255 | Charleroi      |            | B-6000         | Belgium     |
|   10253 | HANAR      |          3 | 1996-07-10 | 1996-07-24   | 1996-07-16  |       2 |       0 | Hanari Carnes             | Rua do Pa?o, 67      | Rio de Janeiro | RJ         | 05454-876      | Brazil      |
+---------+------------+------------+------------+--------------+-------------+---------+---------+---------------------------+----------------------+----------------+------------+----------------+-------------+
6 rows in set (0.00 sec)

And returning a specific customer ID.

mysql> select * from orders where CustomerID like "RATTC";
+---------+------------+------------+------------+--------------+-------------+---------+---------+----------------------------+-----------------+-------------+------------+----------------+---------------+
| OrderID | CustomerID | EmployeeID | OrderDate  | RequiredDate | ShippedDate | ShipVia | Freight | ShipName                   | ShipAddress     | ShipCity    | ShipRegion | ShipPostalCode | ShipCountry   |
+---------+------------+------------+------------+--------------+-------------+---------+---------+----------------------------+-----------------+-------------+------------+----------------+---------------+
|   10262 | RATTC      |          8 | 1996-07-22 | 1996-08-19   | 1996-07-25  |       3 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10272 | RATTC      |          6 | 1996-08-02 | 1996-08-30   | 1996-08-06  |       2 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10294 | RATTC      |          4 | 1996-08-30 | 1996-09-27   | 1996-09-05  |       2 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10314 | RATTC      |          1 | 1996-09-25 | 1996-10-23   | 1996-10-04  |       2 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10316 | RATTC      |          1 | 1996-09-27 | 1996-10-25   | 1996-10-08  |       3 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10346 | RATTC      |          3 | 1996-11-05 | 1996-12-17   | 1996-11-08  |       3 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10401 | RATTC      |          1 | 1997-01-01 | 1997-01-29   | 1997-01-10  |       1 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10479 | RATTC      |          3 | 1997-03-19 | 1997-04-16   | 1997-03-21  |       3 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10564 | RATTC      |          4 | 1997-06-10 | 1997-07-08   | 1997-06-16  |       3 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10569 | RATTC      |          5 | 1997-06-16 | 1997-07-14   | 1997-07-11  |       1 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10598 | RATTC      |          1 | 1997-07-14 | 1997-08-11   | 1997-07-18  |       3 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10761 | RATTC      |          5 | 1997-12-02 | 1997-12-30   | 1997-12-08  |       2 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10820 | RATTC      |          3 | 1998-01-07 | 1998-02-04   | 1998-01-13  |       2 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10852 | RATTC      |          8 | 1998-01-26 | 1998-02-09   | 1998-01-30  |       1 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10889 | RATTC      |          9 | 1998-02-16 | 1998-03-16   | 1998-02-23  |       3 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   10988 | RATTC      |          3 | 1998-03-31 | 1998-04-28   | 1998-04-10  |       2 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   11000 | RATTC      |          2 | 1998-04-06 | 1998-05-04   | 1998-04-14  |       3 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
|   11077 | RATTC      |          1 | 1998-05-06 | 1998-06-03   | 0000-00-00  |       2 |       0 | Rattlesnake Canyon Grocery | 2817 Milton Dr. | Albuquerque | NM         | 87110          | United States |
+---------+------------+------------+------------+--------------+-------------+---------+---------+----------------------------+-----------------+-------------+------------+----------------+---------------+
18 rows in set (0.00 sec)

This query will list all of the primary keys in the database.

mysql> select * from information_schema.table_constraints where constraint_name = "PRIMARY";
+--------------------+-------------------+-----------------+--------------+---------------------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME                | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+---------------------------+-----------------+
| def                | ace_haul          | PRIMARY         | ace_haul     | stations                  | PRIMARY KEY     |
| def                | ace_haul          | PRIMARY         | ace_haul     | temperatures              | PRIMARY KEY     |
| def                | acme              | PRIMARY         | acme         | ADDRESS                   | PRIMARY KEY     |
| def                | acme              | PRIMARY         | acme         | CUSTOMER                  | PRIMARY KEY     |
| def                | acme              | PRIMARY         | acme         | DISCOUNT                  | PRIMARY KEY     |
| def                | acme              | PRIMARY         | acme         | ORDER_HEADER              | PRIMARY KEY     |
| def                | acme              | PRIMARY         | acme         | ORDER_LINE                | PRIMARY KEY     |
| def                | acme              | PRIMARY         | acme         | ORDER_SHIPMENT            | PRIMARY KEY     |
| def                | acme              | PRIMARY         | acme         | PAYMENT_TERMS             | PRIMARY KEY     |
| def                | acme              | PRIMARY         | acme         | PHONE                     | PRIMARY KEY     |
| def                | acme              | PRIMARY         | acme         | PRODUCT                   | PRIMARY KEY     |
| def                | acme              | PRIMARY         | acme         | RESELLER                  | PRIMARY KEY     |
| def                | acme              | PRIMARY         | acme         | SALESMAN                  | PRIMARY KEY     |
| def                | acme              | PRIMARY         | acme         | SHIPMENT                  | PRIMARY KEY     |
| def                | acme              | PRIMARY         | acme         | STATUS                    | PRIMARY KEY     |
| def                | fun               | PRIMARY         | fun          | operatingsystems          | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | columns_priv              | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | db                        | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | event                     | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | func                      | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | help_category             | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | help_keyword              | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | help_relation             | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | help_topic                | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | host                      | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | ndb_binlog_index          | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | plugin                    | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | proc                      | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | procs_priv                | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | proxies_priv              | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | servers                   | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | tables_priv               | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | time_zone                 | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | time_zone_leap_second     | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | time_zone_name            | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | time_zone_transition      | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | time_zone_transition_type | PRIMARY KEY     |
| def                | mysql             | PRIMARY         | mysql        | user                      | PRIMARY KEY     |
| def                | nw_old            | PRIMARY         | nw_old       | categories                | PRIMARY KEY     |
| def                | nw_old            | PRIMARY         | nw_old       | customers                 | PRIMARY KEY     |
| def                | nw_old            | PRIMARY         | nw_old       | employees                 | PRIMARY KEY     |
| def                | nw_old            | PRIMARY         | nw_old       | order_details             | PRIMARY KEY     |
| def                | nw_old            | PRIMARY         | nw_old       | orders                    | PRIMARY KEY     |
| def                | nw_old            | PRIMARY         | nw_old       | products                  | PRIMARY KEY     |
| def                | nw_old            | PRIMARY         | nw_old       | shippers                  | PRIMARY KEY     |
| def                | nw_old            | PRIMARY         | nw_old       | suppliers                 | PRIMARY KEY     |
+--------------------+-------------------+-----------------+--------------+---------------------------+-----------------+
46 rows in set (0.01 sec)

As you can see there are a lot of them in a large database like Northwind. If you want to download the Northwind database yourself and play around with it; there is a copy here for the MySQL database management system.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current day month ye@r *