Mojolicious Tutorial

Database basics

Next, let's learn how to use the database to store the data. For web applications, try to store your data in a database. In the past, it was often saved in a file, but considering the file locking mechanism and the amount of access, it is best to use a database.

Databases include SQLite, MySQL, PostgreSQL, etc., but this time we will use the simplest SQLite. SQLite is easy, but it has limited functionality, so MySQL or PostgreSQL is the best choice when actually creating a website. I would like to deal with MySQL in the advanced version.

Rather than explain the database in detail, this time we will focus on how to use it. First, let's learn the basics of database operations using sqlite3, an application that utilizes SQLite databases.

Install sqlite3

First, install sqlite3, which is a tool for operating SQLite.

Installing sqlite3 on Windows

If you are using Windows, follow the steps below to download sqlite3. Please download sqlite3 from the following site.

SQLite.org Download → sqlite-shell-win32-x86-3080403.zip

The zip file will be downloaded, so unzip it. Make sure you have a file called "sqlite3.exe". This is an application for working with SQLite databases. You need to place it somewhere in your application's path.

This time, place it in the same directory where you installed Perl. Copy and place "sqlite3.exe" in the directory "C: \ Perl \ site \ bin".

Then launch a command prompt and execute the following command.

sqlite3 test.db

Installation is complete when sqlite3 starts as shown below.

SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.
sqlite>

Type .q to exit and press Enter.

.q

Installing sqlite3 on Mac OS X

Mac OS X comes with sqlite3 installed from the beginning, so no special work is required. Let's start sqlite3 from the terminal.

sqlite3 test.db

Let's check if sqlite3 starts as follows.

SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.
sqlite>

Type .q to exit and press Enter.

.q

Installing sqlite3 on Linux

For CentOS, Redhat Linux, Fedora, install sqlite3 with the yum command.

yum -y install sqlite

For Ubuntu, use the apt-get command to install sqlite3 and sqlite3.

sudo apt-get install -y sqlite3

Then run the following command from the shell:

sqlite3 test.db

Installation is complete when sqlite3 starts as shown below.

SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.
sqlite>

Type .q to exit and press Enter.

.q

Create table

SQLite saves the data in a file. Let's create a database with a file named test.db.

sqlite3 test.db

Create the table first when sqlite3 starts. Use the "create table" syntax to create a table. The language that gives instructions to the database is called SQL. "Create table" is also one of SQL.

Let's run "create table" with the table name book and create a table with columns id, title and author.

create table book (
  id primary key,
  title not null default'',
  author not null default''''
);

The column definition is in the form "column name (type) (optional)".

Column name (type) (optional)

Column names are required. SQLite does not require you to specify a data type. If you do not specify the data type, it will be treated as a character string.

Let's specify the id that represents the primary key for each table. This is to uniquely identify the row. Put a primary key constraint on the primary key. The primary key constraint is to ensure that each row is unique and to prevent duplicate primary keys from being registered.

Make sure to put not null constraints on the other columns. A not null constraint is a constraint that prevents a row from being set to the value null. When not null constraint is performed, it is necessary to set default , so set it as well.

It's a good practice to avoid using nulls as much as possible, so put a not null constraint on all columns. It's a good idea to design your database on the assumption that it is not null.

Display table list

After creating the table, let's see what kind of table exists. Use the .tables command to display the table list.

.tables

A list of tables is displayed.

book

Check the table definition

Let's check the definition of the table created by the create table statement. Use the " .schema " command.

.schema book

The table definition is displayed.

CREATE TABLE book (CREATE TABLE book
  id primary key,
  title not null default'',
  author not null default''''
);

Insert line --insert statement

Now let's insert a line. Use the insert statement to insert a row into the database.

# how to use
insert into table name (column name 1, column name 2, ...) values ​​(value 1, value 2, value 3);

Let's actually insert the data. Insert the data that "id" is "00000001", "title" is "Perl", and "author" is "kimoto" into the database. The string must be enclosed in single quotes.

insert into book (id, title, author) values ​​('00000001','Perl','kimoto');

Let's use the select statement to see what kind of row was inserted. The select statement will be explained in detail later.

select * from book;

It will be displayed in the following manner.

00000001 | Perl | kimoto

It's a little hard to see because the column names aren't displayed. You can display the column names by executing the .header command.

.header on

Try executing the select statement again. The rows are displayed, including the column names, as shown below.

id | title | author
00000001 | Perl | kimoto

Line update --update statement

Now let's update the line. Use the update statement to update the row.

update table name set column name 1 = value 1, column name 2 = value 2, ... where condition

In the update statement, the condition is specified using the where clause, and the row that matches the condition is updated. Note that if you do not specify the where clause, all rows will be updated.

Now let's use the update statement to change the string "Perl" in the "title" column to "Ruby". Specify the value of "id" in the condition and update.

update book set title ='Ruby' where id = '00000001';

Let's confirm that the value has been changed in the select statement.

select * from book;

It will be displayed in the following manner.

00000001 | Ruby | kimoto

Delete line --delete statement

Now let's delete the line. Use the delete statement to delete a row.

delete from table name where condition

In the delete statement, the condition is specified using the where clause, and the row that matches the condition is deleted. Note that if you do not specify a where clause, all lines will be deleted.

Now let's delete the line using the delete statement. Specify the value of "id" in the condition and delete it.

delete from book where id = '00000001';

Let's confirm that the value has been changed in the select statement.

select * from book;

Because the line has been deleted, Nothing is displayed.

Select row --select statement

I've written a select statement to get a row, but I'll explain it in a little more detail. First, in order to try the select statement, insert a row with the following SQL.

insert into book (id, title, author) values ​​('00000001','Perl Tutorial','kimoto');
insert into book (id, title, author) values ​​('00000002','Perl advantage','ken');
insert into book (id, title, author) values ​​('00000003','Ruby Tutorial','yosuke');

Column selection

If you specify "*" in the select statement, you can get all columns. You can also specify the column name to get only the target column.

# All columns
select * from book;

#id
select id from book;

#id and title
#select id, title book;

Condition-where clause

The select statement is often used by specifying the condition in the where clause.

select column name from table name where condition

The conditional operators are:

operator	meaning
= =	equal
& lt; & gt;	Not equal
Greater than	& gt
Greater than or equal to	& gt =
Less than	& lt
Less than or equal to	& lt =
Including	like
null	is null
Not null	is not null

The operators can be used as follows:

# "Id" is equal to "00000002"
select * from book where id = '00000002';

The usage of the like operator is a little different, so I will explain it. If you use the like operator, you can use "%" as a wildcard in the string. You can get only the lines that contain "Perl" in "title" by writing as follows.

# Includes "Perl" in "title"
select * from book where title like'%Perl%';

You have now mastered the minimum knowledge you need to store and retrieve data in the database.