SQL basics with SQLite
Posted on April 11, 2019 at 19:00 PM
In this tutorial we'll be going through the basic CRUD (create, read, update and delete) functions to a database.
- Installing SQLite browser
- Creating a database and inserting data
- Selecting data from a database
- Deleting data from a database
- Summary
Installing SQLite browser
For this tutorial we will be using SQLite - a light weight, easy to use/install database which will give us everything required to go through the basics for starting out with databases. It's worth pointing out there are many other types of databases, including MySQL, PostgreSQL, Oracle... the list goes on, although for this tutorial we'll look specifically at SQLite. Before we get started you'll want to download DB viewer for SQLite - this will provide us with a nice interface to play around with. Simply download from the link above, and the once downloaded open 'DB Browser (SQL)' from the start menu and you should be greeted with a screen as depicted below.
Creating a database and inserting data
To create your first database, click on the 'new database' button - this will prompt you to save a new database which you should do. It will then prompt you to create your first table - cancel out of this as we want to write SQL statements directly. Head to the 'execute SQL' tab. We'll start by creating a table named 'Persons' which will have personal information about users.
CREATE TABLE Persons (
PersonID INTEGER PRIMARY KEY AUTOINCREMENT,
FirstName varchar(255),
LastName varchar(255)
);
Enter the SQL above into the 'execute SQL' and press the 'play' button and all things being well you'll have created a table. SQL has been specifically designed so its wonderfully easy to understand just from reading it - i.e. in the SQL above, we have created a new table with the name 'Persons', and given it 3 fields - each field accepts a data type (in our example either an integer or varchar with a length which means a string up to a certain number of characters).
We can know add some users to our 'Persons' table - the syntax being INSERT INTO [table name] ([table columns]) VALUES ([values]);.
INSERT INTO Persons (LastName, FirstName) VALUES ('Charlie','Jackson');
INSERT INTO Persons (LastName, FirstName) VALUES ('Scott','Jackson');
INSERT INTO Persons (LastName, FirstName) VALUES ('Dave','Jackson');
INSERT INTO Persons (LastName, FirstName) VALUES ('Audrey','Plastiras');
INSERT INTO Persons (LastName, FirstName) VALUES ('Nikki','Plastiras');
INSERT INTO Persons (LastName, FirstName) VALUES ('Dave','Plastiras');
INSERT INTO Persons (LastName, FirstName) VALUES ('Grace','Plastiras');
Selecting data from the database
Now we have a database and a table with data in it, we can pull the data out using a 'select' SQL statement - the syntax is as follows: SELECT * FROM [table name];. We can go one stage further using the 'where' conditional statement. For example, we can retrieve all the users with the surname 'Jackson' with the by executing the following SQL statement.
SELECT * FROM Persons WHERE LastName = 'Jackson'
# returns: Charlie, Dave and Scott
Note, in addition to the 'where' clause we can also use 'and' and 'or' operators to select more data.
SELECT * FROM Persons WHERE FirstName = 'Charlie' or FirstName = 'Scott'
# returns: Charlie and Scott
Updating data in a database
It's common to need to update data in a database - for example take user passwords or address which are stored in databases - users often forgot their password and subsequently need to reset them. This is a typical example where we want to keep the existing data but update the password. Using our example, we'll say someone changes their name. The syntax is: UPDATE [table name] SET [table column] = 'some value' WHERE [table column] = 'some value';.
UPDATE Persons SET LastName = 'Jackson' WHERE FirstName = 'Audrey';
SELECT * FROM Persons WHERE LastName = 'Jackson'
# returns: Charlie, Dave, Scott and Audrey
Deleting data from a database
The final part of the CRUD functionality is to delete some data from a database. Again, this follows the simple syntax : DELETE FROM [table name] WHERE [table column] = 'some value';.
DELETE FROM Persons WHERE FirstName = 'Charlie';
SELECT * FROM Persons;
# returns: Dave J, Scott, Audrey, Nikki, Grace, Dave P
Summary
Lastly, it's worth pointing out that CRUD applications have a backend language such as PHP or Python which can take user inputs from a form and parse into an SQL statement. There are many things on a day to day basis which require databases but to give you a few examples:
- Signing up to mailing list - 'insert' statement
- unsubscribing from a mailing list - 'delete' statement (supposedly)
- Logging into a site - 'select' statement
- Forgotten password - 'update' statement
This is just a few simple examples to give a flavour of the simple 'CRUD' applications but nearly all large sites online will have a database behind them. Many CMS's like WordPress store all web content in databases and return the content to a page when a URL is selected.