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.
I am a computer enthusiast that has always been interested in maintaining and upgrading computer hardware. I was born on a farm and lived there for 13 years before moving into town. I was always interested in technology and my father always gave me good encouragement for developing my knowledge and interest in tinkering with computers. He was using a computer on the farm but that was in the very late 80`s and late 90`s. Seeing what a computer could do always fascinated me and then I knew that is what I wanted to do. The first time I used Linux was Red Hat 6.2 installed off a single CDROM and I was amazed that you did not need to install any more drivers on my old Intel Celeron machine to be able to enjoy a nice 24BPP desktop and use the GIMP to edit photos and enjoy playing the old Linux games like Xbill. Then a while later I tried Mandrake Linux 9.2 and I was hooked. It came with so much software and was very easy to use as well. Then Mandrake Linux 10 and on through FreeBSD, Debian 3.0, Debian 3.1 Suse Linux 9.2, Suse 10 and onto the Ubuntu distributions although I am using Fedora Core 15 and the Gnome 3 desktop at the moment.