035.3 SQL基礎 | えるすた

035.3 SQL基礎

Web Development Essentials | 2025/01/14

本章では、データを格納するデータベースについてと、SQLと呼ばれるデータを追加・変更・削除する書き方について説明します。前回に引き続き、Node.jsの記述も登場しますが、新たにSQLの書き方を学習していきます。Node.jsの範囲にまだ不安がある方は、Node.jsを振り返り学習してから、本章に取り組みましょう！！

データベースとの連携

まずデータベースとは、データを整理・管理する為の仕組みで、簡単にいえば、データベースは情報を保存しておく為の「倉庫」のようなものです。データベースは独自の関数を作成して、データを記録することもできますが、データベース管理システム(DBMS)を使用することで、開発速度及びデータのセキュリティ面を向上させることが可能です。

データベース管理システム（DBMS: Database Management System）

概要：データベースを効率的かつ安全に管理するためのソフトウェアです。DBMSは、SQLなどのクエリ言語を使用してデータの保存、検索、更新、削除などの操作を簡単に行うことができ、複雑なデータ管理を自動化することができます。DBMSの代表例として、「MySQL」「SQLite」「PostgresSQL」などがありますが、本章ではSQLiteを使って説明をしていきます。

SQLite

概要：軽量でサーバーレスなデータベース管理システムで、データをファイルベースで管理することが出来るため、簡単なプロジェクト開発などに最適なデータベースです。

SQL（Structured Query Language）

概要：SQLは、表形式で格納されたデータベースを操作するための標準的なクエリ言語で、データの検索、挿入、更新、削除といった操作を簡潔に記述することができます。

それでは、実際にSQLiteをインストールしていきましょう。
Node.jsでSQLiteを使用するには、「sqlite3」モジュールをインストールする必要があります。ターミナルで以下のコマンドを実行しましょう。
※sqlite3 とは似て異なる「better-sqlite3」などのSQLite をサポートするモジュールがいくつか存在します。本書では sqlite3 のモジュールを使用する為、他モジュールを選択すると期待通りに動作しない可能性がありますのでご注意ください。

①「node」ディレクトリまで移動

■<Windowsの場合>

C:\Users\ユーザ名> cd $env:USERPROFILE\Desktop\lesson\node

■<Mac OSの場合>

> cd $HOME/Desktop/lesson/node

②「npm install」コマンドを使用して、「sqlite3」モジュールをインストールします。

■ターミナル

> npm  install sqlite3

SQLiteのインストール後、SQLiteの中身を表示する為、SQLite Viewerをインストールしましょう。

VSCodeにSQLite Viewerをインストール

①画面左に並んでいるアイコンからマーク（Extensions）を選択します。

②検索バーで「sqlite」と検索し、「SQLite Viewer」が表示されることを確認します。

➂「SQLite Viewer」の右下にある「Install」をクリックしてインストールします。

Node.jsからデータベースへの接続を確立する

インストールが完了すると、sqlite3 モジュールを組み込んでデータベースとして機能するファイルを作成することができます。前章で作成したサーバを起動するファイルと、データベースを操作するファイルを区別する為、本書では新たに「database.js」ファイルを作成します。
nodeディレクトリの中で「database.js」ファイルを作成して、以下の内容を記述しましょう。

■database.js

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database('sample.sqlite3');

db.run('CREATE TABLE IF NOT EXISTS name_list (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)');

db.close();

説明は一旦省いて、ターミナル上でnodeコマンドを実行します。

■ターミナル

> node database.js

このコマンドを実行すると、database.jsと同階層にsample.sqlite3というデータベースファイルが生成され、その中にname_listというテーブルが作成されます。

テーブル

概要：テーブルは、データベース内でデータを格納する領域で、「レコード」「カラム」「フィールド」から構成され、行と列の表形式でデータを表現します。テーブルはデータの保存、検索、更新、削除などの操作を行うための基本的なデータ構造で、テーブルが作成されるまで、データベースにデータを保存することはできません。

＜テーブルの構成図＞

「レコード」は、テーブル内の各行の1件分のデータにあたります。
「カラム」は、テーブル内の各列の項目にあたります。
「フィールド」は、レコードの中にあるひとつの要素にあたります。

テーブルについての内容は、恐らく試験には含まれない為詳細な説明は割愛しますが、データベースを理解する上では欠かせない内容となります。詳しく説明している記事も多いので、是非ご自身で調べてくださいね！

それでは、database.jsに記述した内容について解説していきます。

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database('sample.sqlite3');

上記記述では、1行目でsqlite3 を読み込み、2行目で「sample.sqlite3」というファイル名のデータベースに接続するための Databaseオブジェクトを作成しています。

db.run('CREATE TABLE IF NOT EXISTS name_list (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)');

次に、こちらのrun() メソッドは、データベースを操作する SQL文を実⾏するために使⽤されます。

CREATE TABLE

概要：テーブルを作成するSQL命令文です。「CREATE TABLE」の後には作成したいテーブル名を指定し、今回はname_listという名前のテーブルを作成しています。
また、今回の例では「CREATE TABLE」の後に「IF NOT EXISTS」を指定しており、テーブル作成時に同名のテーブルがなければ作成、テーブルが既に存在する場合はなにもしない、という処理を行うための条件句を追加しています。
更に、括弧の中には作成したいカラム名を指定しており、今回はid・nameのカラムを作成します。
カラム名「id」では、データ型には整数を表す「INTEGER」を指定し、データには整数が入ることを前提としています。
また、属性としてテーブル内に同じデータを挿入できなくなる「primary key」と、データが格納されるたびに自動で数字の連番が挿入される「autoincrement」を指定しています。
カラム名「name」では、データ型には文字列型を表す「TEXT」を指定し、文字列のデータが入ることを前提としています。
このようにデータ型を設定することで、想定してないデータ型の入力防止や、データの管理を効率的に行う事が可能となります。

close()

概要：開いているデータベースを閉じるメソッドです。SQL文を記述した後には、Databaseオブジェクトを切断(開放)するためにclose()メソッドを実行します。データベースの接続を切断しない場合、処理の遅延やエラーの発生の原因となる為、データベース接続を行った際は、処理の最後にclose()メソッドで必ず切断するように記述します。

基本SQL文

SQLでは「SELECT」「INSERT」「UPDATE」「DELETE」といったの4種類の基本となる命令文があり、ここからは、先程作成したname_listテーブルを使用して、実際にどのように記述するのかを紹介します。

データの挿入（INSERT）

構文：INSERT INTO テーブル名 (カラム名1, カラム名2, …) VALUES (値1, 値2, …);
概要：データを挿入するには「INSERT」文を使用し、「INSERT INTO」の後にはテーブル名を指定します。テーブル名の後ろには、括弧を使用してカラム名を、更にvaluesの後ろにはカラム名の指定順に値を記述します。

それでは以下のようにdatabase.jsに命令文を追加してみましょう。

■database.js

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database('sample.sqlite3');

//以下の文は削除
db.run('CREATE TABLE IF NOT EXISTS name_list (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)');

//この文を追加　今回はnameのみ指定
db.run("INSERT INTO name_list (name) values ( '太郎') ");

db.close();

上記例では、カラム名nameにのみ値を挿入してみます。
では、再度ターミナルで以下のコマンドを実行しましょう。

■ターミナル

$ node database.js

sample.sqlite3を確認して、以下のように表示されていればデータの挿入は成功です。

※2回実行すると、id が重複するのでエラーが返されてしまいます。
※sample.sqlite3を既に開いている場合、一度ファイルを閉じてから再度開いてください。

データの取得（SELECT）

構文：SELECT カラム名 FROM テーブル名 WHERE 条件;
概要：データを検索するには「SELECT」文を使用します。記述方法では、「SELECT」の後に取得したいカラム名を指定し、「FROM」の後には検索したいテーブル名を指定します。
SELECTの後ろにある「＊（アスタリスク）」は「全て」を表しており、今回は全てのカラムからデータを取得しています。※カラム名を指定する場合は、アスタリスクがある場所に記述します。
それでは、database.jsを以下のように変更し、nodeコマンドで実行してみましょう。

■database.js

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database('sample.sqlite3');


//以下の文は削除
db.run("INSERT INTO name_list (name) values ( '太郎') ");

//この文を追加
db.get("SELECT * FROM name_list WHERE id = 1", (err, row) => {
        if (err) {
            console.error(err.message);
        }
        console.log( row.id + ':' + row.name );
    });

db.close();

■ターミナル

$ node database.js

コマンドを実行し、ターミナル上に以下のような表示がでれば成功です。

＜出力結果＞

1 : 太郎

上記記述では、name_listテーブルからWHERE句を使用して特定の行のみ、今回の例ではidが1のデータのみを指定しています。後ほどまとめて紹介する為、この場では説明を割愛しますが、今回のように、1つのデータのみを取得したい場合はget()メソッドを使用します。

WHERE句

概要：データの検索条件を指定する為のSQL構文で、「WHERE」の後に「=」「<>」「! =」「and」「or」などの演算子を使って条件を指定する事が可能です。今回の例では、前述の通り、「id」が「1」と一致するデータを検索しています。

データの更新（UPDATE）

構文：UPDATE テーブル名 SET カラム名 = 新しい値 WHERE 条件;
概要：「UPDATE」文はデータを更新する命令文で、「UPDATE」の後には更新したいデータが含まれるテーブル名を指定します。また、テーブル名の後には「SET」を記述し、更新したいカラム名と値を指定します。
以下のようにdatabase.jsにUPDATE文を追加し、値が正常に更新されているか確認してみましょう。

■database.js

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database('sample.sqlite3');

//このコードを追加
db.run("UPDATE name_list SET name='次郎' WHERE id=1");

db.get("SELECT * FROM name_list WHERE id = 1", (err, row) => {
        if (err) {
            console.error(err.message);
        }
        console.log( row.id + ':' + row.name );
    });

db.close();

■ターミナル

$ node database.js

コマンドを実行し、ターミナル上に以下のように値が更新されていれば成功です。

＜出力結果＞

1 : 次郎

データの削除（DELETE）

構文：DELETE FROM テーブル名 WHERE 条件;
概要：「DELETE」文は、言葉の通りデータを削除する命令文です。DELETE文では「FROM」の後に削除したいデータが含まれるテーブル名を指定します。
SELECT文と同様に、「FROM」の後にテーブル名を指定していますが、DELETE文は構文として、カラムの指定する「＊アスタリスク」などが含まれませんのでご注意ください。

■database.js

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database('sample.sqlite3');

//UPDATE文は削除
db.run("UPDATE name_list SET name='次郎' WHERE id=1");

//このコードを追加
db.run("DELETE FROM name_list WHERE id=1");
db.get("SELECT * FROM name_list WHERE id = 1", (err, row) => {
        if (err) {
            console.error(err.message);
        }
        console.log( row.id + ':' + row.name );
    });

db.close();

■ターミナル

$ node database.js

上記DELETE文では、これまでと同様でWHERE句を使用して、特定のテータのみ削除しようとしていますが、もしテーブルの全データを削除したい場合は、DELETE FROMの後にテーブル名を書くだけで削除することが可能です。

ここまで本記事通りに実践をしている場合、出力結果では何も表示されてなければ成功です。
ここまで紹介した4つの命令文がSQLの基本となります。実際の試験では、WHERE句も使用した命令文の実行結果について問われると思います。

更に、以下は上記4つの命令文に関連して、覚えていた方がよいsqlite3のメソッドとなります。データの挿入や検索などでも使用するメソッドは変わってきますので、記述方法を含め、しっかりと覚えておきましょう。

メソッド	機能	備考
run()	クエリを実行する	INSERT, UPDATE,DELETEなど
close()	データベースを閉じる	全て
all()	全てのデータを取得	SELECT
get()	データを１行のみ取得	SELECT
each()	行毎に繰り返し取得	SELECT

SQLインジェクション

以下の内容は、恐らく試験問題としては出題されないかと思いますが、データベース・SQLを使用する上で把握しておく必要があります。SQLインジェクションとは何か、またどのように発生してしまうのか、一例を紹介します。

SQLインジェクション

概要：SQLインジェクションとは、Webアプリケーションの脆弱性を利用して、データベースに不正なSQL文を実行させる攻撃手法です。例えば、検索ボックスや入力フォームなどに不正な操作を行うSQL文を意図的に「注入（インジェクション）」することにより、データベース内のデータの消去や改ざんといった操作や、データの盗用を図る攻撃などがあります。

実際の例で紹介すると、例えば、検索ボックスでname_listテーブルの中身を検索する機能があったとし、検索ボックスで「太郎」と値を送信すると、実行されるSQLは以下のようになります。

＜実行例＞

SELECT * FROM name_list WHERE name = '太郎'

しかし、もし検索フォームに「’;DELETE FROM name_list」と入力されたらどうでしょう？実行されるSQLは、以下のようになります。

＜実行例＞

SELECT * FROM name_list WHERE name = ' ';DELETE FROM name_list;

上記内容では、冒頭の「;」によって、SELECT文が終了されてしまい、続いて「DELETE FROM name_list」という意図していないSQLが実行されてしまいます。つまり、name_listテーブルの全データを削除する命令が実行できてしまいます。

これを防ぐために、SQL文に直接変数を挿入せず、「プレースホルダ」といわれる可変部分を変数のようにしたSQL文を、予め作成する方法があります。

プレースホルダ

概要：プレースホルダとは、SQL文内の可変にしたい値の場所に「？」などの記号で示しておき、後から記号で示した場所に対して実際の値を機械的な処理で割り当てる手法のことを指します。また、プレースホルダに実際の値を割り当てる処理をバインドと呼びます。
以下は、上記例をプレースホルダを使用した場合の記述例です。

＜実行例＞

SELECT * FROM name_list WHERE name = ? ;

上記のように、プレースホルダを使って指定しておけば、その部分はあくまでも「値」として処理されるので、万が一不正な値が入力されても、SQL命令に関わるような「特殊文字」は無効化(エスケープ処理)されるため、SQL文として実行されることはなくなります。
その為、先ほどと同じように検索ボックス「DELETE FROM name_list」と入力されたとしてもテーブルの全データが取得されることはありません。

前述の通り、SQLインジェクションは恐らく試験としては出題されないかと思いますが、データベースを使用してWeb開発を行う場合、エンジニアとして抑えとかなければいけない知識となります。

また、Web Development Essentialsで紹介・出題される内容は、全てWeb開発を行うを行う上で、覚えておいて損はない内容です。試験対策だけではなく、エンジニアとして活躍できるように、繰り返し学習を行っていきましょう！

以上までが、Web Development Essentialsについての全記事となります。ご拝読いただきありがとうございました！

えるすたでは、受験方法に関する記事と、実際にバウチャーを購入いただける記事もご用意しております。ここまで学習して「よし、受験しよう」と思った方は、是非参照ください！

関連リンク：
・試験をうけるには
・バウチャーを購入する

<<前のページ

目次に戻る