3. Keywords, Functions ...
4. Case Insensitivity
5. Dynamic Queries
Like every computer language, SQL has a basic syntax that it uses for everything. Because SQL is so specific to databases, however, this syntax is very simple and you can actually only do one thing: write queries.
A query is nothing more than a single request from the database. As we saw in last chapter, this request can be any of the four CRUD operations. For example, you can request the five newest entries in a table, which means you query the table for the five newest entries. So, every time you do something with your database, you’re providing a list of queries that should be executed one after the other. Just as with other programming languages, one query is not executed until the previous one finishes.
All we need now, is to have a way to tell the database when one query ends and another begins. For this, we use the semicolon (
-- Both of these queries do exactly the same INSERT INTO users (username, bio, gender) VALUES ("Gandalf", "Use the force, Harry!", "male"); SELECT (username, signup_date, bio) FROM users WHERE gender="female"; INSERT INTO users (username, bio, gender) VALUES ("Gandalf", "Use the force, Harry!", "male"); SELECT (username, signup_date, bio) FROM users WHERE gender="female";
It’s important to note that SQL disregards extra white space and newlines. This means that you can display the query in a way that you can easily understand it, without breaking something. It also means that if you forget a semicolon, errors will pop up all over the place.
When developing anything, it’s often useful to include comments telling you what exactly a certain query is trying to do. These comments are removed when the query is sent to the database and actually executed. Don’t worry though, they do not make a query slower.
To create a single-line comment, use
-- Some nice comment here
To create a multi-line comment, if your RDBMS supports it, use
/* A MULTILINE COMMENT */
Keywords, Functions & Names
Every query is built out of three components: keywords, functions and names.
Keywords are words provided by SQL that, well, do something if you include them in the query. For example, the
SELECT keyword starts a query that reads things from a database.
Functions transform the data of a query. For example, the
AVG(column) function takes the average of all values of the specified column.
Names are the names of your columns, tables, databases – anything that you’ve given a name.
-- SELECT, FROM and WHERE are keywords -- COUNT(*) is a function that counts the amount of entries -- users is a table name we decided ourselves SELECT COUNT(*) FROM users
Everything in SQL is case insensitive. This means that you can write
Select, or whatever spelling you want. Writing the words differently every time, however, is not a good idea.
It’s best practice to write your keywords and functions in all uppercase, and to choose your column and table names all lowercase. If needed, separate words are connected with an underscore (
-- All queries do exactly the same, but I think you can see which one reads better; SELECT COUNT(*) FROM users; select Count(*) from USERS; Select count(*) From users;
The names you decide yourself are not case insensitive. For example, you could have a column named
user_id, and one named
USER_ID, and they would be completely different.
Most RDBMS have an interface that allows you directly put in queries. While this can be very useful to do some database maintenance, such as remove some garbage or replace things that need to be updated, it’s not that useful to most applications. Usually, you want to execute an SQL query when users load a page, and you want that query to change depending on certain variables.
For example, most blogs use only a single file for displaying all posts. When a user visits a URL, there is usually some information about the post in the URL (such as
/News/05-06-2016/hello-world). You must use a programming language (not SQL) to get these variables, and put them into a query that selects the right blog post. That programming language then sends the query to the database, retrieves the results, and displays them in a sensible manner.
Therefore, if you haven’t done so already, it’s wise to also learn a server-side language (such as PHP or ASP.NET) that can interact with SQL and databases. Remember, SQL is for databases, not for layout or websites.