Introduction to SQL
In this lesson, we'll quickly introduce SQL.
We'll cover the following
SQL, which stands for Structured Query Language, is widely used in web applications to store and retrieve data from databases. SQL is a subtle and complex topic, so, for now, we’ll cover just enough to understand one of the most common database attacks, the SQL injection.
The examples in this chapter are written to work on MySQL, a widely used open-source database.
How SQL works
The first step in using SQL is to establish a connection to the database that people can connect to directly using a SQL client. Usually, people only use a direct connection to do maintenance work like upgrades and to troubleshoot performance issues or bugs. Most connections, however, are performed by other software—for example, a typical web application with the proper credentials. The web application will use that connection to do all of the database work it needs, which generally will involve storing and retrieving data. Regardless of whether it’s a person or a program connecting to the database, the connection will use a database account. Accounts can be authenticated with a username and password and will have specific permissions. A database might give full permissions to an administrative user, for example, but give only limited permissions to another user.
Example
In the SQL model, data is stored in tables. You can think of a table as a grid of data. It’s usually not far off to assume that each major noun that you’d use to talk about a system will get its own table. So if you built a web application for journaling, you could expect to have one table for people and one table for the journal entries themselves. The journal entry table would have one row for each journal entry. The person table would have one row for each person in the system. Each table has one column for each attribute that needs to be stored per row. So we could visualize our schema like this:
| JournalEntryId | PersonId | CreatedTimestamp | Body |
|---|---|---|---|
| 1 | 1 | 2018-01-01 03:00:00 | Everybody shim sham! |
| 2 | 3 | 2018-01-07 12:34:56 | Time for klava. |
| 3 | 2 | 2018-01-08 22:14:28 | Make no little plans. |
| 4 | 1 | 2018-01-08 22:14:37 | Time for lindy hop. |
| PersonId | FirstName | LastName |
|---|---|---|
| 1 | Frankie | Manning |
| 2 | Daniel | Burnham |
| 3 | Vlad | Taltos |
Searching for a specific person’s entries
Now that we have all this wonderful data in tables, what can we do with it? Well, one thing we can do is search it. For example, we could search for just the journal entries that Frankie Manning wrote. To do that, we’d write the following SQL:
Get hands-on with 1400+ tech skills courses.