We\'ve detected that you\'re using AdBlock Plus or some other adblocking software. Please be aware that this is only contributing to the demise of the site. We need money to operate the site, and almost all of that comes from our online advertising.

Linux tips and tricks for all users.

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.

Post a Comment

Your email is kept private. 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 *