By Toptal and Pete Sefton for Udemy
Interested in more than a beginner’s guide? Check out Pete’s full SQL course.
Note: This post is part of our “Getting Started” series of free text tutorials on some of our most popular course topics.
To jump to a specific section, click the table of contents below:
Maybe it is hard to believe, but SQL is used everywhere around us. Every application that is manipulating any kind of data needs to store that data somewhere. Whether it’s Big Data or just a table with few simple rows, a government or a small startups, or a big database that spans over multiple servers or a mobile phone that runs its own small database, SQL is ubiquitous.
But what is a SQL? SQL stands for Structured Query Language, and usually is pronounced as “ess-que-el”. SQL is the language of databases, and is specifically built to communicate with databases. SQL is a simple language and is similar to the English language, as commands are structured almost like English sentences. Those sentences are structured like declared statements, thus SQL is also called a declarative language.
While SQL is the database language, you also need to choose a Database Management System to put everything together. In this article we will be talking about MySQL, its uses and relationship with SQL syntax. MySQL was created in 1995 and has become particularly popular for web applications.
Why learn a whole new language when there are many available tools for writing SQL queries visually? When working with some SQL tools, it is important to know SQL language and to understand what the visual tools are doing, and why. Sometimes there are needs to write few SQL statements manually, not only because it is the fastest way but because it is more powerful and often the only way to achieve targeted goals.
We have mentioned that SQL is the language of databases. What is a database? Databases are a storage mechanism designed to offer access to stored information and their manipulation. Information in the database is stored in objects called tables. Tables are uniquely identified by their names and are comprised of columns and rows. Columns contain the column name, column data type, and any other attributes for the column. Rows contain the records or data for the columns. Many of the tables in a database will have relationships, or links between them, either in a one-to-one or a one-to-many relationship. This is why this kind of databases is called relational model databases.
The easiest way to describe a database structure is by comparing it with an Excel spreadsheet, with which many are familiar. A database is one spreadsheet file. Sheets in the spreadsheet are tables, each one with a given name. Columns and rows are the same in both. SQL language can be used to create new tables, or alter existing ones, and to fetch data, update data, or delete data.
Say we have a big collection of famous movie quotes stored in random separate text files. Even if we are more organized and use Excel spreadsheet, the problem we have is the same. Having quotes stored in that way, we can’t quickly get all quotes from one movie, or get all quotes from one character. If we move our text files or spreadsheet into a database, and create tables with relations between them, all this becomes possible. What does relational really mean? The relational model is a way to describe the data and the relationship between those data entities. In our example, a relation is a connection between every single quote with a table where movie titles are stored, or all characters are stored.
Here is a bit simplified example, with only one sample table called “Movie_quotes”. It has four columns, one for quote text, one for character that said that quote, and one for a movie and a year. We have collected eight movie quotes, and our sample table looks like this:
|I’ll be back||The Terminator||The Terminator||1984|
|I find your lack of faith disturbing.||Darth Vader||Star Wars||1977|
|It’s a trap!||Admiral Ackbar||Star Wars||1983|
|Never tell me the odds.||Han Solo||Star Wars||1980|
|Do. Or do not. There is no try.||Yoda||Star Wars||1980|
|Stupid is as stupid does.||Forrest Gump||Forrest Gump||1994|
|My mama always said: Life was like a box of chocolates.|
You never know what you’re gonna get.
|Forrest Gump||Forrest Gump||1994|
|Run, Forrest! Run!||Jenny Curran||Forrest Gump||1994|
There are many SQL commands, but there are four general SQL operations that can do things to tables and its data:
- Create – Filling data into tables.
- Read – Query data out of a table.
- Update – Change data already in a table.
- Delete – Remove data from the table.
First letters of these basic SQL operations give the acronym “CRUD”, and they are considered as a fundamental set of four basic functions or features that every database must have.
By covering this for basic features, we will cover basic and most important SQL commands: `CREATE`, `INSERT`, `SELECT`, `UPDATE`, `DELETE`, and `DROP`.
First, we need to create our table in the MySQL database. To create a new table, `CREATE TABLE` statement is used. The format of a simple and `CREATE TABLE` statement syntax is as follows:
CREATE TABLE table_name (column_1 data_type, column_2 data_type, column_3 data_type);
First are the keywords `CREATE TABLE` followed by the table name. This is a perfect example of the SQL simplicity and similarity with English language. Keywords are followed by an open parenthesis, where additional parameters are defined, column name and column data type, followed by a closing parenthesis. It is important to note that all SQL statements should end with a `;`.
There are few rules that need to be followed. The table and column names must start with a letter and can be followed by letters, numbers, or underscores. They must not exceed a total of 30 characters in length. SQL reserved keywords as names for tables or column names (such as `select`, `create`, `insert`, etc.) are forbidden.
In our example, the most simple column names would be `TEXT`, `CHARACTER`, `MOVIE`, and `YEAR`. But the problem is that most of those names are reserved words. To avoid any possible conflicts, in our example we will create column names with a prefix `Q_`.
Data types differentiate between different databases, but here are the most common ones for MySQL. They can be grouped into three main categories, Numeric, Date and String.
- Most Common Options
- int or integer
- float (m,d)
- can be used for numeric values that include decimals.
- m is the total number of digits
- d is the number of digits after the decimal
- can be used for numeric values that include decimals.
- A true/fals
- Most Common Options
- Just date information stored in the YYYY-MM-DD format where y = year, m = month, d = day i.e. ‘2015-08-15’
- Date format with time of day included ‘YYYY-MM-DD HH:MM:SS’
- Has the same format as datetime, but converts the current timezone to UTC
- A combination of text characters with the length of m
Data types specify what type of data can be stored in that particular column. If a column called `Q_CHARACTER` is to be used to hold names, then that particular column should have a `varchar` (variable-length character) data type. A column that will hold year of the movie will be type `number`, in our example column `Q_YEAR`.
Final SQL command that will create our table with desired table structure is as follows:
CREATE TABLE Movie_quotes (‘Q_TEXT’ varchar(200), ‘Q_CHARACTER’ varchar(20), ‘Q_MOVIE’ varchar(20), ‘Q_YEAR’ number(4));
Result of this SQL command will create an empty table with columns:
- `Q_TEXT` that can accept a 200 character long string.
- `Q_CHARACTER` that can accept a 20 character long string.
- `Q_MOVIE` that can accept a 20 character long string
- `Q_YEAR`that can accept four numbers for a year.
Next step is filling up the table with our movie quotes data. There are many available tools with graphic interfaces (GUI) for managing tables and data in the database. But often it is quicker to write an SQL script, which is basically a collection of SQL commands that will be executed sequentially. And this is especially handy when you need to fill up your table with lots of data.
SQL command for inserting or adding a row of data into the MySQL database table is `INSERT`. The format is as follows:
INSERT INTO table_name (column_1, column_2, ... column_n) VALUES (value_1, value_2, ... value_n);
To insert a row of data into table, `INSERT` keyword is followed with keyword `INTO` and a name of the table. Following column names in the parenthesis and separated by commas is optional, but is a good practice to define the columns to be sure that the proper data will be inserted into proper columns. The last part is defining what data will be inserted with a keyword `VALUES`, followed by the list of values enclosed in parenthesis. Please note that strings should be enclosed in single quotes, and numbers should not.
Our example SQL script that will fill up our `Movie_quotes` table looks like this:
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES (‘I’ll be back’, ‘The Terminator’, ‘The Terminator’, 1984);
If you would like to add in bulk you can simplify the code to look like this:
INSERT INTO Movie_quotes (Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR) VALUES ('I find your lack of faith disturbing.', 'Darth Vader', 'Star Wars', 1977), ('It’s a trap!', 'Admiral Ackbar', 'Star Wars', 1983), ('Never tell me the odds.', 'Han Solo', 'Star Wars', 1980), ('Do. Or do not. There is no try.', 'Yoda', 'Star Wars', 1980), ('Stupid is as stupid does.', 'Forrest Gump', 'Forrest Gump', 1994), ('My mama always said: Life was like a box of chocolates. You never know what you’re gonna get.', 'Forrest Gump', 'Forrest Gump', 1994), ('Run, Forrest! Run!', 'Jenny Curran', 'Forrest Gump', 1994);
With the data saved in the database, we can now query that data and see what is stored in our table, and we can filter and sort that data in different ways.
The three most important statements when querying your MySQL database are:
The SELECT clause tells the computer what values you would like to have returned to you.
The FROM clause tells you what tables within the database the values can be found.
The WHERE clause lists the conditions that would like like the information to meet before it is chosen. The WHERE clause will not be used until you move past the basics, but then will be heavily involved in your SQL usage.
SELECT column_1, column_1, ... column_n FROM table_name;
Declared column names determine which columns will be returned in the results, and in which order. If we want to select all the columns, or we just don’t know which exactly columns are in the table, we can use a wildcard asterisk character, `*`, that will select everything from the database:
SELECT * FROM table_name;
Query that will show all the data from our example table would look like this:
SELECT * FROM Movie_quotes;
And a query that will show only the list of quotes followed with a year of the movie, would look like this:
SELECT Q_TEXT, Q_YEAR FROM Movie_quotes;
Sometimes we don’t want to get all the data from the table. This may be the case when there is a lot of data in the table, or when we are searching for specific data that match certain criteria. For this we can use `WHERE` SQL clause. The `WHERE` clause will filter records and limit which records are retrieved from the database to those that meet specified defined criteria:
SELECT column_1, column_1, ... column_n FROM table_name WHERE column_name operator value;
As mentioned, the `WHERE` clause is optional, but if we decide to use it, the following operators are available:
- `=` – Equal to.
- `>` – Greater than.
- `<` – Less than.
- `>=` – Greater than or equal to.
- `<=` – Less than or equal to.
- `<>` – Not equal to.
- `BETWEEN` – Between two values.
- `LIKE` – Search for a pattern.
- `IN` – Multiple possible values for a column.
Mathematical operators are self-explanatory. The `BETWEEN` operator looks for values that are between two declared values, including equality on both ends. The `LIKE` pattern matching operator is a very powerful operator that allows us to select only rows that are similar to what we specify. The percent sign `%` can be used as a wildcard to match any possible character that might appear before or after the specified characters.
For example, to get only movie quotes from the movie Star Wars we would write:
SELECT * FROM Movie_quotes WHERE Q_MOVIE = ‘Star Wars’;
Please note that the `WHERE` clause is case sensitive, and that the following SQL command will return no result:
SELECT * FROM Movie_quotes WHERE Q_MOVIE = ‘STAR WARS’;
There can also be more than one `WHERE` clause, combined with logical operators `AND` and `OR`. If we end up using more than one `AND` logical operator on the same column, we should consider using the `IN` clause instead.
Again, to show this with an example, let’s return all the movie quotes from the movies Star Wars and The Terminator:
SELECT * FROM Movie_quotes WHERE Q_MOVIE = ‘Star Wars’ AND Q_MOVIE = ‘The Terminator’;
Better written above example would be by using `IN` clause instead:
SELECT * FROM Movie_quotes WHERE Q_MOVIE IN (‘Star Wars’, ‘The Terminator’);
Until now, we have been discussing only how to filter the data from the database. Returned rows will be sorted by the order in which they were entered (committed) into the database. To control the order in which the rows will appear, we can sort the output data by including the `ORDER BY` clause. The `ORDER BY` clause includes one or more column names that specify the sort order:
SELECT column_1, column_1, ... column_n FROM table_name WHERE column_name operator value ORDER BY column_name;
To expand our previous Star Wars movie quotes example, now sorted by the year, we would write:
SELECT * FROM Movie_quotes WHERE Q_MOVIE = ‘Star Wars’ ORDER BY Q_YEAR;
By default, the sort order for a column is ascending from lowest value to highest value. To change the sort order for a column to be descending, we can include the `DESC` keyword after the column name:
SELECT * FROM Movie_quotes WHERE Q_MOVIE = ‘Star Wars’ ORDER BY Q_YEAR DESC;
The `ORDER BY` clause is not limited to a single column. You can include a comma-delimited list of columns to sort by. Returned rows will be sorted by the first specified column, and then subsequently by the next specified columns. It is also important to note that a sort column does not need to be included in the list of selected columns. We can write the query like this:
SELECT Q_TEXT, Q_CHARACTER, Q_MOVIE FROM Movie_quotes WHERE Q_MOVIE = ‘Star Wars’ ORDER BY Q_YEAR DESC;
After we insert the data initially, we are not restricted to only read that data. We can make changes to any data in any column in any row. The `UPDATE` statement is used to update or change records.
The `UPDATE` statement syntax is as follows:
UPDATE table_name SET column_name = new_value WHERE column_name operator value;
When using the `UPDATE` statement, it is important to carefully construct a `WHERE` clause. The `WHERE` clause specifies which record or records should be updated. If we don’t use a `WHERE` clause when executing the `UPDATE` statement, we will update all the data in the specified column(s).
Let’s look at movie quotes in our `Movie_quotes` table. We have all the quotes ending with punctuation, except the Terminator one. This is a perfect example of how to use `UPDATE` statement:
UPDATE Movie_quotes SET Q_TEXT = ‘I’ll be back!’ WHERE Q_MOVIE = ‘The Terminator’;
Like explained before, if we omit the `WHERE` clause by accident or on purpose we will update all the quotes rows to say “I’ll be back!”. By selecting just the row with the movie “The Terminator”, we are updating only a column `Q_TEXT` in that one specified row.
When databases are used a lot, sooner or later it becomes necessary to remove obsolete data from the database. We can delete just a few rows from tables, or we can delete whole tables altogether.
The `DELETE` statement is used to delete rows in a table. The command’s syntax is as follows:
DELETE FROM table_name WHERE column_name operator value;
Again, like with `UPDATE` statement, the `WHERE` clause specifies which record or records should be deleted. Without a specified `WHERE` clause, all the rows and records will be deleted:
DELETE FROM Movie_quotes;
Let’s assume we don’t like the movie Forrest Gump any more and want to delete quotes from that movie. To remove all quotes from that movie we would write the following SQL command:
DELETE FROM Movie_quotes WHERE Q_MOVIE = ‘Forrest Gump’;
Finally, we had enough of those movies. We are not interested in the movie quotes anymore, and we changed our interest to music. We started collecting song lyrics. With all the existing SQL knowledge we learned so far, changing our database is very simple.
First we need to clean up our database from the data that we are not interested in anymore. To delete a table with all its rows, we can use `DROP TABLE` statement. It is important to note that `DROP TABLE` statement is different from using a `DELETE` statement and deleting all of the records in the table. Deleting all of the records in the table leaves us the table itself and all the defined table structure; including column data type definition and other related database information to that table. Dropping the table removes the table, all the table definition, as well as all of its rows.
The `DROP TABLE` statement syntax is as follows:
DROP TABLE table_name;
To delete our `Movie_quotes` table from the database, we would write:
DROP TABLE Movie_quotes;
Now our database is empty, and ready to accept new data. We can start all over with the CRUD process by creating a new table called `Song_Lyrics` and building a song lyrics database from our new favorite songs.
In this article we have taken a look at the four fundamental database functions covered by CRUD: how to create new data, read that data, update the data we want to change, and finally how to delete unwanted data. This included basic but most important SQL commands like: `CREATE TABLE`, `INSERT INTO`, `SELECT`, `UPDATE`, `DELETE`, and `DROP`.
Those basic SQL commands allow lots of data manipulation, but it is important to know that every covered command has many more options and additional functionality not covered by this article. Altogether, the basics should give a novice SQL developer a head start when beginning work with databases and using a new language, SQL.