When we want to retrieve information from a database, we query the structure with language appropriate to the database.
Remember right at the start of this series we saw that a database could be a phone book or a recipe book. So how do we find the phone number of Randolph West? By looking up the surnames starting with W, and going through all the Wests in alphabetical order until we get to that entry.
The same goes for finding the recipe for lemon meringue in a recipe book. Start at the index at the back, look through the desserts, and then search for meringue.
In a RDBMS (relational database management system), the language for querying data is called Structured Query Language, or SQL. We can pronounce it like “sequel”, or sound out each letter.
SQL Server is commonly pronounced “Sequel Server”. MySQL is pronounced “My-S-Q-L”, sounding out the letters (some people pronounce it “My-Sequel”). It all depends on who’s saying it. PostgreSQL is just pronounced “Postgres”, because seriously.
These, along with Oracle, are the major players in the RDBMS industry.
ANSI SQL
When it comes to putting information into a database server, and getting information out, we can write queries that look very similar across platforms. This is thanks to a standards body called ANSI (American National Standards Institute), which (with proposals from each vendor) has suggested ANSI SQL syntax that all vendors should use.
For the sake of this series, we will assume that the vendors do follow the standard exactly, but in reality it isn’t that simple.
Putting data in: INSERT
Adding new data to a database is performed using an INSERT operation.
Changing data: UPDATE
Modifying existing data is done with an UPDATE operation.
Getting rid of data: DELETE
Removing rows from a table is performed with a DELETE operation.
Getting data out: SELECT
The vast majority of operations in a database has to do with data retrieval. To get data out, we SELECT it.
CRUD
In technical circles, between software developers and database developers, we might refer to these four operations using the mnemonic CRUD, which stands for Create, Read, Update, Delete.
When referring to a specific database or application, it could mean that the database is just being used as a data store (a virtual box of random stuff) and may not have proper relationships between the tables, nor be normalized.
There’s nothing necessarily evil about denormalized data, because the application code may handle that intelligence. Be wary though. Using an RDBMS to store non-relational data might cause headaches.
Next time we will write our first SELECT statement to query a database. Stay tuned!
Look me up on Twitter if you want to ask any questions about this series, on @bornsql.