SQL basics learned using SQLite

SQL is a query language for inserting and retrieving data into a database. Since the basic writing method of SQL is standardized, you can issue a query to the database with the same description in SQLite, MySQL, Oracle, or PostgreSQL.

Preparation for executing SQL

The following explains how to build an environment for executing SQLite on Windows.

Install SQLite on Windows

When you finish this work

sqlite3 test.db

Let's start sqlite3 by specifying the database name. If the database does not exist, it will be created automatically.

Syntax other than select

The difficult part in SQL is select statement. Other syntax is relatively simple. In preparation for executing select statement, I will first explain the SQL that is frequently used other than select statement.

create table - create table

Use the "create table" statement to create a table. Here, I will explain only the format of the simplest "create table" statement. (For "create table", see "Defining a table with create table (unpublished)".)

create table table name (
  Field name 1,
  Field name 2,
  ...
);

SQL that creates a table named books with two fields, title and author.

create table books (
  title,
  author, author,
  price
);

Please try it. The last semicolon in SQL is required. It's easy to forget, so don't forget it.

SQLite provides a command to display the created table list. Run the following command and check that you see a table called "books". (Note that there is a dot (.) At the beginning.)

.table

drop table - drop the table

Use "drop table" to drop a table.

drop table table name;

Now let's delete the table we created earlier called "books".

drop table books;

Type ".table" to make sure the table has been deleted. After this, the "books" table will be used, so create it again using "create table".

insert - add record

Use insert statement to insert a record into the table.

insert into table name (field name 1, field name 2, ..) values (value 1, value 2, value 3);

To add a record with "title" of "Good life", "author" of "Saki" and "price" of "1900" to the table, execute the following SQL. Note that if the data is a string, enclose it in single quotes.

insert into books (title, author, price) values ('Good life', 'Saki', 1900);

This will add a record like the one below to the table.

+ - -- -- -- --- + - -- -- -- - + - -- --- +
# | title | author | price | This is the field name
+============= +========== +======== +
| Good life | Saju | 1900 |
+ - -- -- -- --- + - -- -- -- - + - -- --- +

I will explain select statement in detail later, but let's execute select statement to confirm that one line has been inserted.

select * from books;

If the above content is displayed, insert is successful. (The output format is slightly different. I will explain how to display it in an easy-to-read format in select statement)

Let's insert a few more records for the rest of this.

insert into books (title, author, price) values ('Simple is good', 'Mai', 2000);
insert into books (title, author, price) values ('Good morning', 'Kenta', 3000);

Record update update

Use update statement to update the data in the table. Generally, you can narrow down the update target by specifying the search conditions in the where section. As explained in select statement, multiple conditions can be specified in the where section.

update table name set field name 1 = value 1, feel name 2 = value 2, .. where to search field name = value

The SQL to change the title value of a record whose "author" is "taro" to Ruby is as follows.

update books set title = 'Ruby' where author = 'taro'

Let's execute update statement using do.

my $update = "update books set title = 'Ruby' where author = 'taro'";
$dbh->do($update);

The previous record is updated and looks like this:

+ - -- --- + - -- -- -- +
# | title | author | header (this can be done when you create the table)
+ - -- --- + - -- -- -- +
# | Ruby | taro | record(record updated by update statement)
+ - -- --- + - -- -- -- +

Delete record delete

Use delete statement to delete a record. Generally, the search condition is specified in the where section to narrow down the deletion target.

delete from table name where field name to search = value

The SQL to delete a record whose "author" is "taro" looks like this:

delete from books where author = 'taro';

Let's execute delete statement using do.

my $delete = "delete from books where author = 'taro';";
$dbh->do($delete);

The record will be deleted.

To the basics of databases learned with SQLite

Perl ABC