Querying the MySQL database - MySQL

As you likely already know, interaction with an SQL database takes place through queries. A query is nothing more than a request for information from a database, this request being a phrase constructed from various keywords and table / column references. In this section, I’ll demonstrate using queries how database normalization makes an administrator’s life considerably easier. To begin, assume that the news table has been filled with this information.

Using this information, assume I execute the following query:

---

mysql>SELECT n.title, a.name FROM news n, administrators a WHERE
->n.admin_id = a.admin_id AND a.admin_id = 2;

---

Resulting in:

title	name
Nuevo Sitio: www.ziobudda.net	Michel

Knowing that the admin_id ‘2’ maps to ‘Michel’, we can build our query without worrying that Michel will be misspelled. Furthermore, if the administrator later decides to changes his name to just the initials ‘M.M.’, the query will not need to be changed because the criteria is based upon an admin_id and not a name. Additionally, this name change will not require the consumption of potentially magnanimous amounts of resources, as would be the case if the name were included along with each row of the news table.

Considering another example, consider the query:

---

mysql>SELECT n.title FROM news n, categories c WHERE 
->n.category_id = c.category_id AND c.category_id = 2;

---

This yields:

title	name
Nuovo Sito: www.phpitalia.com	New Sites
Nuevo Sitio: www.ziobudda.net	New Sites

Now suppose that I want to update one of the category names found in the categories table, in particular I want to change the name ‘New Sites’ to ‘Great Sites’. All that I need to do is update one row found in the categories table:

---

mysql>update categories set name = ‘Great Sites’ 
->where category_id = ‘2’;

---

Again, executing the previous SELECT query would result in:

title	name
Nuovo Sito: www.phpitalia.com	Great Sites
Nuevo Sitio: www.ziobudda.net	Great Sites

Of course, your Web application is not likely to repeat the category name, but this will certainly make a difference when using dynamically-named table headers.

>>> More MySQL Articles          >>> More By W.J. Gilmore

blog comments powered by Disqus