SQL#

SQL, or Structured Query Language, is the language we use to "speak" to a database. We use SQL to query for data, insert data, delete data, organise and sort data, and more, inside of a relational database.

We're going to learn some basic SQL commands and use them to create a database inside of the SQLite database we created in the last section.

For reference, here's a complete list of the commands we're going to explore:

Creating a database#

In SQLite, you create the database by opening the file: sqlite my_first.db. In MySQL, PSQL, SQL Server, etc., you create the database using the CREATE DATABASE statement.

I cannot go into too much detail here because the syntax for these command changes based on the engine you're using. That, and we don't need to use the statement because SQLite created the database for us.

Here's the syntax of CREATE DATABASE for MySQL, PostgreSQL and SQL Server:

1

create database <name>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
       [ TEMPLATE [=] template ]
       [ ENCODING [=] encoding ]
       [ LOCALE [=] locale ]
       [ LC_COLLATE [=] lc_collate ]
       [ LC_CTYPE [=] lc_ctype ]
       [ TABLESPACE [=] tablespace_name ]
       [ ALLOW_CONNECTIONS [=] allowconn ]
       [ CONNECTION LIMIT [=] connlimit ]
       [ IS_TEMPLATE [=] istemplate ] ]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE DATABASE database_name
[ CONTAINMENT = { NONE | PARTIAL } ]
[ ON
      [ PRIMARY ] <filespec> [ ,...n ]
      [ , <filegroup> [ ,...n ] ]
      [ LOG ON <filespec> [ ,...n ] ]
]
[ COLLATE collation_name ]
[ WITH <option> [,...n ] ]
[;]

As you can see, the syntax can be really simple or more complicated, depending on the engine. You'll have to search for the "create database" syntax for the engine you're working with (in the future.)

Creating a table#

SQLite's CREATE TABLE syntax is as follows:

1
2
3
4
5
6
7

CREATE TABLE database_name.table_name(
   column1 datatype PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype
);

We'll just create one table to work with for now. Copy and paste this SQL into the SQLite prompt you have open for my_first.db:

1
2
3
4
5
6
7

create table people (
  id integer primary key,
  name text not null,
  email text not null unique,
  age integer not null,
  gender text
);

And then use .tables to see what you have:

1
2
3
4
5
6
7
8
9

sqlite> create table people (
   ...>   id integer primary key,
   ...>   name text not null,
   ...>   email text not null unique,
   ...>   age integer not null,
   ...>   gender text
   ...> );
sqlite> .tables
people

Let's break down the command:

1
2
3
4
5
6
7

create table people (
  id integer primary key,
  name text not null,
  email text not null unique,
  age integer not null,
  gender text
);

You'll notice all the documentation, and even as I write this chapter, the SQL is in UPPER CASE? Like CREATE TABLE? Yet my SQL, above, is not? It's actually not that important, but some people prefer to use upper case to keep it consistent with documentation. Either or works.

We called the table people. Simple enough to understand. That's how we'll refer to this particular table (relationship) of data when we use other commands like SELECT.

We created a schema made up of five columns: id, name, email, age, and gender.

For the id column we set the data type to integer and we made this one column the primary key. We could have included other columns in the primary key, but we're just learning here.

The name, email, and gender columns all have a data type of text, meaning they store text data like strings. The gender column is the only column that does not have a not null constraint against it, which means it's optional. The email column has to be unique because emails are, by definition, unique.

We can look at this schema in SQLite: .schema people

1
2
3
4
5
6
7
8

sqlite> .schema people
create table people (
  id integer primary key,
  name text not null,
  email text not null unique,
  age integer not null,
  gender text
);

This allows us to reference the schema later on should we need to.

Adding data#

Now that we have a table, let's insert some people into it!

1
2
3
4
5
6

INSERT INTO people (name, email, age, gender) VALUES(
  "Michael Crilly",
  "mike@upload.academy",
  21, -- LOL!
  "male"
);

This will add a new row to the table people:

1
2

sqlite> select * from people;
1|Michael Crilly|mike@upload.academy|21|male

I've also done something interesting the above SQL. A few things actually.

Firstly, it's all in UPPER CASE to simply prove a point: either or is fine. Pick which you prefer.

And lastly I've included a comment in my SQL in the form of -- LOL!. Any text that being with -- in a SQLite SQL statement is considered a comment and is ignored. I've included this comment because my age being 21 is laughable!

Let's try inserting the exact same information again:

1
2
3
4
5
6
7

sqlite> INSERT INTO people (name, email, age, gender) VALUES(
   ...>   "Michael Crilly",
   ...>   "mike@upload.academy",
   ...>   21, -- LOL!
   ...>   "male"
   ...> );
SQL error: column email is not unique

So our UNIQUE constraint is working as expected on the email column.

You'll notice I also included the gender column when I didn't need to. Let's add a second person:

1
2
3
4
5

INSERT INTO people (name, email, age) VALUES(
  "Crilly Michaels",
  "imposter@upload.academy", -- Hacker!
  20 -- Dammit!
);

This command executes successfully: select * from people;

1
2
3

sqlite> select * from people;
1|Michael Crilly|mike@upload.academy|21|male
2|Crilly Michaels|imposter@upload.academy|20|

No gender column (or value) is present for the second entry, which can be identified by its unique PRIMARY KEY of 2.

Updating records#

Our imposter's age is wrong (I knew it!) so we'll need to update that record:

1

update people set age = 30 where id = 2;

Which gives us the desired results:

1
2
3
4

sqlite> update people set age = 30 where id = 2;
sqlite> select * from people;
1|Michael Crilly|mike@upload.academy|21|male
2|Crilly Michaels|imposter@upload.academy|30|

With an UPDATE, we're defining what we want to update with set column = value and we are using the WHERE "clause" to only update the rows that have an id of 2. There will only be one of them as the id column is unique for every row.

Micro project: use the UPDATE command to change the gender of id = 2 to be fluid. I've already done this myself.

Deleting a record#

We've detected that our "imposter" is in fact, an imposter! We must delete their record from our database otherwise other people might think they're part of the cool team (our team.)

Let's do this using the DELETE command:

1

delete from people where id = 2;

This gets me:

1
2
3

sqlite> delete from people where id = 2;
sqlite> select * from people;
1|Michael Crilly|mike@upload.academy|21|male

Now our imposter has gone! We can also DELETE records based on any column we like:

1
2
3

sqlite> delete from people where email = 'mike@upload.academy';
sqlite> select * from people;
sqlite>

You can even combine them, too. SQL is an extremely powerful language and we're only doing the basics here.

Mass insert#

Before we move onto using SELECT and other commands, let's do a mass insert of records so we've got plenty to work with. Copy and pasting the following:

1
2
3
4
5
6

insert into people (name, email, age, gender) values("Michael Crilly","mike@upload.academy",21,"male");
insert into people (name, email, age, gender) values("John Smith","smithman@john.tld",22,"unknown");
insert into people (name, email, age, gender) values("Michelle Car","mcar@awesome.website.tld",44,"female");
insert into people (name, email, age, gender) values("Michael Carter","carter@mcarter.org.tld",19,"fluid");
insert into people (name, email, age, gender) values("Limmy","limmy@jokesonyou.tld",38,"male");
insert into people (name, email, age, gender) values("Merlo Coffee","merlo@coffee.tld",-1,"none");

This is just a big mass insert of a few records we can use in the next section.

Querying for records#

We have a table we can insert records int, edit them and even delete them. Now we now to be able to retrieve the data from the table using a query.

Let's query for every record: select * from people;

1
2
3
4
5
6
7

sqlite> select * from people;
1|Michael Crilly|mike@upload.academy|21|male
2|John Smith|smithman@john.tld|22|unknown
3|Michelle Car|mcar@awesome.website.tld|44|female
4|Michael Carter|carter@mcarter.org.tld|19|fluid
5|Limmy|limmy@jokesonyou.tld|38|male
6|Merlo Coffee|merlo@coffee.tld|-1|none 

There are the records we added into the table earlier, but the output isn't very readable. Let's change that:

1
2

sqlite> .mode column
sqlite> .headers on

These options make a big difference:

1
2
3
4
5
6
7
8
9

sqlite> select * from people;
id          name            email                age         gender
----------  --------------  -------------------  ----------  ----------
1           Michael Crilly  mike@upload.academy  21          male
2           John Smith      smithman@john.tld    22          unknown
3           Michelle Car    mcar@awesome.websit  44          female
4           Michael Carter  carter@mcarter.org.  19          fluid
5           Limmy           limmy@jokesonyou.tl  38          male
6           Merlo Coffee    merlo@coffee.tld     -1          none

That's all the records and columns. In our SELECT statement we used * inside the column selection part of the query. We can refine the columns we get back from the database by providing column names instead of *: select name, email, age from people;

1
2
3
4
5
6
7
8
9

sqlite> select name, email, age from people;
name            email                age
--------------  -------------------  ----------
Michael Crilly  mike@upload.academy  21
John Smith      smithman@john.tld    22
Michelle Car    mcar@awesome.websit  44
Michael Carter  carter@mcarter.org.  19
Limmy           limmy@jokesonyou.tl  38
Merlo Coffee    merlo@coffee.tld     -1

Now we only get the columns we've asked for.

Limiting the results#

We've got a total of: select count(*) from people; records:

1
2
3
4

sqlite> select count(*) from people;
count(*)
----------
6

What if we only ever want three records returned by our queries? We can use LIMIT to do this: select * from people limit 3;

1
2
3
4
5
6

sqlite> select * from people limit 3;
id          name            email                age         gender
----------  --------------  -------------------  ----------  ----------
1           Michael Crilly  mike@upload.academy  21          male
2           John Smith      smithman@john.tld    22          unknown
3           Michelle Car    mcar@awesome.websit  44          female

That's simple enough to understand.

Refining our queries#

What if we want to restrict the results we get back to only ones that have specific characteristics? For example, what if we want to know who is younger than 30 years old? Let's try a WHERE clause: select * from people where age < 30;

1
2
3
4
5
6
7

sqlite> select * from people where age < 30;
id          name            email                age         gender
----------  --------------  -------------------  ----------  ----------
1           Michael Crilly  mike@upload.academy  21          male
2           John Smith      smithman@john.tld    22          unknown
4           Michael Carter  carter@mcarter.org.  19          fluid
6           Merlo Coffee    merlo@coffee.tld     -1          none

And if we invert that, we can work out who was omitted: select * from people where age > 30;

1
2
3
4
5

sqlite> select * from people where age > 30;
id          name          email                     age         gender
----------  ------------  ------------------------  ----------  ----------
3           Michelle Car  mcar@awesome.website.tld  44          female
5           Limmy         limmy@jokesonyou.tld      38          male

You can also combine WHERE clauses to search inside of a range: select name, email, age from people where age >= 30 and age <= 40;

1
2
3
4

sqlite> select name, email, age from people where age >= 30 and age <= 40;
name        email                 age
----------  --------------------  ----------
Limmy       limmy@jokesonyou.tld  38

We've used AND to create a second "part" to the WHERE clause.

We can do other things with a WHERE clause, too. Here's a table of some common operations:

Using WHERE and LIKE#

With the WHERE clause we can check for specific values or ranges, etc, but what if we want to search for names that are "like" a particular pattern? Let's look at an example.

I want to find people who have a name beginning with m (or M): select * from people where lower(name) like 'm%';

1
2
3
4
5
6
7

sqlite> select * from people where lower(name) like 'm%';
id          name            email                age         gender
----------  --------------  -------------------  ----------  ----------
1           Michael Crilly  mike@upload.academy  21          male
3           Michelle Car    mcar@awesome.websit  44          female
4           Michael Carter  carter@mcarter.org.  19          fluid
6           Merlo Coffee    merlo@coffee.tld     -1          none

I've used three new concepts here: the lower() function to change all upper case characters in the name column to their lower case equivalents; the LIKE operator in the clause; and the m% pattern.

The lower() function has an obvious effect on the value inside of the name column. You should research this function, and other functions, so you're aware of what's available.

We've used LIKE in place of the other operators we can use inside of a WHERE clause, like =, >, or <=, etc. Instead, we're using the LIKE operator to say we want values like the pattern we provided.

And the pattern we provided was m%. The % symbol is a wild card that means all - it's like * in other contexts. There is a whole bunch of things you can do with pattern matching. Here's a few options:

Using WHERE with IN#

Similar to the LIKE operator, the IN operator allows us to search for values in a range or list: select * from people where age in (19, 21, 44);

1
2
3
4
5
6

sqlite> select * from people where age in (19, 21, 44);
id          name            email                age         gender
----------  --------------  -------------------  ----------  ----------
1           Michael Crilly  mike@upload.academy  21          male
3           Michelle Car    mcar@awesome.websit  44          female
4           Michael Carter  carter@mcarter.org.  19          fluid

These are exact matches. You can also replace the list of numbers with a subquery, which is getting a bit advanced, but here's an example: select * from people where age in (select age from people where age <= 25);

1
2
3
4
5
6
7

sqlite> select * from people where age in (select age from people where age <= 25);
id          name            email                age         gender
----------  --------------  -------------------  ----------  ----------
1           Michael Crilly  mike@upload.academy  21          male
2           John Smith      smithman@john.tld    22          unknown
4           Michael Carter  carter@mcarter.org.  19          fluid
6           Merlo Coffee    merlo@coffee.tld     -1          none

I'm sure you can imagine just how powerful a subquery can be.

Ordering our results#

Let's take the above example, repeat it, and order the results by the age column: select * from people where age in (select age from people where age <= 25) order by age;

1
2
3
4
5
6
7

sqlite> select * from people where age in (select age from people where age <= 25) order by age;
id          name          email             age         gender
----------  ------------  ----------------  ----------  ----------
6           Merlo Coffee  merlo@coffee.tld  -1          none
4           Michael Cart  carter@mcarter.o  19          fluid
1           Michael Cril  mike@upload.acad  21          male
2           John Smith    smithman@john.tl  22          unknown

And we can change the order too by changing order by age to order by age desc:

1
2
3
4
5
6
7

sqlite> select * from people where age in (select age from people where age <= 25) order by age desc;
id          name        email              age         gender
----------  ----------  -----------------  ----------  ----------
2           John Smith  smithman@john.tld  22          unknown
1           Michael Cr  mike@upload.acade  21          male
4           Michael Ca  carter@mcarter.or  19          fluid
6           Merlo Coff  merlo@coffee.tld   -1          none

That's reversed the order.

Adding a column#

We need another column: type. I want to add this column because I'm pretty certain "Merlo Coffee" isn't a person, so I want to classify our data based on them being a person or a business.

Let's add the column: alter table people add column type text not null default 'person';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

sqlite> alter table people add column type text not null default "person";
sqlite> select * from people;
id          name            email                age         gender      type
----------  --------------  -------------------  ----------  ----------  ----------
1           Michael Crilly  mike@upload.academy  21          male        person
2           John Smith      smithman@john.tld    22          unknown     person
3           Michelle Car    mcar@awesome.websit  44          female      person
4           Michael Carter  carter@mcarter.org.  19          fluid       person
5           Limmy           limmy@jokesonyou.tl  38          male        person
6           Merlo Coffee    merlo@coffee.tld     -1          none        person

I've used the ALTER TABLE command to literally alter the table and add a column. I used ADD COLUMN to specify that I want to ADD a new COLUMM. You can do other things like deleting a column or changing an existing one. You can also update indexes and other more advanced attributes about columns.

I defined the new column as type TEXT NOT NULL and I set the DEFAULT value to person. Above you can see I've done a SELECT statement and I can now see the new column. Everyone is a person though, so let's update the "Merlo Coffee" column: update people set type = 'business' where id = 6;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

sqlite> update people set type = 'business' where id = 6;
sqlite> select * from people;
id          name            email                age         gender      type
----------  --------------  -------------------  ----------  ----------  ----------
1           Michael Crilly  mike@upload.academy  21          male        person
2           John Smith      smithman@john.tld    22          unknown     person
3           Michelle Car    mcar@awesome.websit  44          female      person
4           Michael Carter  carter@mcarter.org.  19          fluid       person
5           Limmy           limmy@jokesonyou.tl  38          male        person
6           Merlo Coffee    merlo@coffee.tld     -1          none        business

You can do a lot with the ALTER TABLE command, but this is sufficient for now.

Deleting our table#

Let's now wind down and DROP our table, because our people table isn't needed anymore: drop table people;

1
2
3

sqlite> drop table people;
sqlite> select * from people;
Error: no such table: people

Done.

Deleting our database#

With SQLite, deleting the database involves leaving the SQLite tool (Ctrl+D) and deleting the file. In MySQL, PostgreSQL, SQL Server, etc. you'd use the DROP DATABASE command.

Summary#

You're not going to be doing much SQL in your day-to-day life as a systems administrator, but it's useful to know how-to manage a database directly using SQL. This can be useful in multiple situations.

Next#

Now we're going to look at a more complex, server/client model database called MySQL.