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) |
Using a MySQL query to return 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.