Saturday, February 16, 2008 - 11:42
I love the way you show me secret things. All I do is type: Select * from name_of_a_table And you share everything with me. Without you, my vision is obscured, and all I see is the display on the page. In fact, this was the push that finally made me decide to learn SQL. In our bacterial metagenomics experiment, I realized that my students could use FinchTV to enter their blast results into our iFinch database. That was cool, but with the web interface, we could only view one result at a time. On the other hand, if we use the right SQL query in the iFinch query window, we can see everything! My eyes were opened. What is a relational database? SQL depends on something called a "relational database." In a relational database, data are organized in tables and the tables have some kind of relationship to one another. This whole relationship between different tables is described by something called a "schema." The image below shows a very simple schema with two tables that are related by a common set of identifiers (ID's).
inset class="center"> My web application might show me some of the information in the tables or it might not show me anything. If I can query the database with SQL, I can ask it to show me everything in a table by saying: SELECT * from name_of_table. I don't speak programmer, what does that mean in English? The asterisk (*) is a wildcard. Just like in poker, where a wildcard is a wonderful thing and can stand in for any card, in SQL, the wildcard means "all" or "everything." If I were to say "SELECT *", I would be telling SQL to show me everything. It would probably be bad to end my request there though, since there's lots of stuff in our database. So, I need to tell SQL where to look. I do that by using the word "from" and the name of the table that I want to know about. Thus, in English, the phrase "SELECT * from table1" says "Show me everything in table1" And that hidden information is no longer hidden from me. DISCLAIMER: *** I am not a programmer and I'm trying to describe these mysteries to biology students. Since many of my readers know more about programming than me, I would appreciate it if you point out my mistakes in the comments.****