Join Sheeri Cabral for an in-depth discussion in this video Understanding declarative language, part of Up and Running with MySQL Development.
Before we dive into using SQL, let's talk a little bit about the structure of SQL. You may be familiar with programming languages, and that some programming languages are procedural. SQL is not procedural. It is what we call a declarative language. What this means is that we declare what we want to get, not how we want to get it. How it's done is the procedure. The basic SQL command to get information follows a Select, From, Where format.
You select what you want, where you want it from, and any filters you want to match. For example, we can write a query that says SELECT table_name FROM information_schema.tables WHERE table_schema='mysql';. SELECT table_name is saying, show me all the table names. It is what we should get. FROM information_schema.tables Is saying look in this table. Where table schema equals MySQL is saying that we should only return records that are in the MySQL database.
Notice that the query does not say get all the table names, loop through each name, and see if it matches MySQL or not, and return the ones that do. The advantage of a declarative language is that the database figures out the procedure. This work is done by a part of the database called the optimizer whose job it is to find the best algorithm for what you want to find. The disadvantage is that this is a much different way of thinking. Programming is all about choosing and writing out algorithms, so if you're used to that, using a declarative language is difficult.
It is hard to give up control of what you want the database to be doing, and you have to think in different ways. You have to think of what you want the result to be, not how to get it. If you want to find the mobile numbers of people in your address book that have Cabral as their last name, it is a simple matter of saying, SELECT mobile FROM address_book WHERE last_name equals Cabral. You do not have to say how it gets the name. Just the what you want is the mobile numbers.
This also gets difficult when a query is taking a long time. If a procedure is taking too long, you can think of ways to change the procedure to take less time. Maybe there are steps you can eliminate or do a different way. With the declarative language, you might have to change the schema to make things go faster. Because you do not always have control over how the query will be answered. Using a declarative language, can be a difficult paradigm shift for programmers who are used to procedural languages. However, declarative languages like SQL can be extremely efficient, because algorithms are chosen by the database optimizer, not a human being.
Because you don't see all the pieces, it may be harder to debug when a database is slow, versus when a piece of code is slow.
- Exploring MySQL data types
- Creating a database
- Creating tables
- Reading and adding data
- Using date and number functions
- Sorting results
- Inserting and replacing records
- Joining tables