MySQLの基本的な概念

エンティティ

実際の物体または対象。これらは静的な特徴を持つ。

データ

計算機で使用される、対象を記述するためのシンボル。これらの特徴が組み合わさってレコードを形成する。

データベース

データを一定の構造に組織化し、保存および管理するプログラム。関係型データベースと非関係型データベースがある。

データベースシステム (DBS)

データの形式定義、各種操作、およびデータベースへのアクセスを含む。

データベース管理システム (DBMS)

データ操作に特化し、通常は視覚的なインターフェースを提供する。

一般的なデータベースシステム

関係型データベース: MySQL, Oracle, SQL Server
非関係型データベース: Redis, MongoDB, HBase

データベースの接続

DOS 界面 (Windows): mysql -h IPアドレス -P ポート番号 -u アカウント名 -p

すべてのデータベースの表示

show databases;

指定のデータベースを選択

use データベース名;

データベース内のテーブルを表示

show tables;

特定のテーブルを使用

use テーブル名;

データベースの作成と削除

create database if not exists データベース名;
drop database if exists データベース名;

SQL文

1. 概念

構造化クエリ言語。データベース操作に使用される。

2. 構成要素
1. データベース定義言語 (DDL)：データオブジェクトの定義と管理を行う。

create, drop, alter

2. データ操作言語 (DML)：データベースオブジェクト内のデータを操作する。

insert, update, delete

3. データクエリ言語 (DQL)：データベースを照会する。

select

4. データ制御言語 (DCL)：データベースの管理、権限管理、データ変更を行う。

grant, commit, rollback

MySQLでよく使用されるデータ型

数値型 (int, double)、文字列 (char, varchar)、日付と時間 (date, datetime)、空値 (null)

テーブルの作成と削除

-- テーブルの作成
create table テーブル名 (
    列名 列データ型 制約(必須、主キー) コメント
) engine = ストレージエンジン charset = 文字セット comment = 'コメント';

-- テーブルの削除
drop table if exists テーブル名;

-- 新しいデータベースの作成
create database if not exists `company`;
-- データベースの選択
use `company`;
-- テーブル `employee` の作成
create table if not exists `employee` (
    id varchar(10) primary key not null comment '社員ID, 主キー',
    name varchar(20) not null comment '社員名',
    sex varchar(1) not null comment '性別',
    phone varchar(11) not null comment '電話番号',
    department varchar(20) not null comment '部門名',
    salary double not null comment '給与',
    join_date date not null comment '入社日'
) charset = utf8 comment '社員表';

-- 部門テーブルの作成
create table department (
   department_id int not null auto_increment comment '部門ID',
   department_name varchar(20) not null comment '部門名',
   department_description varchar(50) not null comment '部門概要',
   department_responsibility varchar(100) not null comment '部門責任',
   primary key (department_id)
) charset = utf8;

alter table department comment '部門情報表';

-- 部門の追加
insert into department (department_name, department_description, department_responsibility)
values ('ソフトウェア開発部', 'ソフトウェア開発の部門', '技術動向の調査と内部技術交流'),
       ('ソフトウェア販売部', 'ソフトウェア販売の部門', '商談、見積もり、プロジェクト損益の計算'),
       ('人事部', '人事管理の部門', '人事制度の策定と管理'),
       ('顧客サービス部', '顧客サービスの部門', '全面的なアフターサービスの提供');

-- ポストテーブルの作成
create table post (
   post_id int not null auto_increment comment 'ポストID',
   post_name varchar(20) not null,
   post_department_id int not null comment '所属部門ID',
   post_description varchar(100) not null comment 'ポスト内容',
   post_responsibility varchar(200) not null comment 'ポスト責任',
   post_salary double not null comment 'ポスト給与',
   primary key (post_id)
);

alter table post comment 'ポスト情報表';

alter table post add constraint fk_post_department_id foreign key (post_department_id)
references department (department_id) on delete restrict on update restrict;

-- ポストの追加
insert into post (post_name, post_department_id, post_description, post_responsibility, post_salary)
values ('プロジェクトマネージャ', 1, '進捗と品質管理', '厳格な管理', 30000),
       ('アーキテクト', 1, 'アーキテクチャ設計', '厳格な管理', 20000),
       ('プログラマー', 1, 'コード実装', '期限内に完了', 10000),
       ('営業担当', 2, '営業活動', '期限内に完了', 10000);

MySQLのデフォルトのテーブルエンジン

InnoDB: トランザクションサポート（データ操作のコミット時にロールバック可能）

MySQL演算子

1. 算術演算子: + - * / (div) %
2. 比較演算子: > < >= <= != <> <=>
3. 論理演算子: && || !(and or not xor)
4. その他の演算子: is null, is not null

MySQLの曖昧検索

1. like を使用して文字列を検索
2. 包含: %内容%
3. 開始: 内容%
4. 終了: %内容
5. 単一文字の一致: _

MySQLの制約

1. 主キー制約: primary key
2. 非空制約: not null
3. 外部キー制約: foreign key (alter table add constraint 外部キー制約名 foreign key 列名 reference 参照テーブル名(参照列名))

MySQLの基本的なCRUD操作

1. insert into `テーブル名` (列名, 複数の列名) values (列に対応する値)
2. delete from `テーブル名` where 条件
3. update `テーブル名` set 列名 = 値, 複数の値はカンマ区切り
4. select * from `テーブル名` where 条件
5. 結果のソート: order by 列名 [asc 昇順; desc 降順]
6. 集計関数: sum, avg, max, min

自定義接続ツールクラスを使用した問題

自定義接続ツールクラスを使用すると、文字列連結によるSQLインジェクションのリスクがあります。直接テキストボックスから条件を入力することで、データベース全体のデータが漏洩または改ざんされる可能性があるため、文字列連結を避けるべきです。

/**
 * クエリの実行
 * SpringのMySQL接続ツールクラスを使用する場合、明示的に変換コード (RowMapper) を実装する必要があり、ラムダ式では直接使用できません。
 *
 * @param sql SQL文
 * @param mapper ResultSetからTに変換するマッパー
 * @param params パラメータリスト
 * @return List<T>
 * @throws SQLException SQL例外
 */
public <T> List<T> executeQuery(String sql, MyFunction<ResultSet, T> mapper, Object[] params) throws SQLException {
    List<T> tList = new ArrayList<>();
    // 1. 接続オブジェクトの作成: dataSourceを使用
    MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setURL(url);
    dataSource.setUser(user);
    dataSource.setPassword(password);
    Connection connection = dataSource.getConnection();
    // 2. ステートメントオブジェクトの作成
    PreparedStatement statement = connection.prepareStatement(sql);
    // 2.1 パラメータの設定
    if (params != null && params.length != 0) {
        for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
        }
    }
    // 3. クエリの実行と結果の取得
    ResultSet resultSet = statement.executeQuery();
    while (resultSet.next()) {
        tList.add(mapper.apply(resultSet));
    }
    // 4. 接続のクローズ
    resultSet.close();
    statement.close();
    connection.close();
    return tList;
}

テストメソッドのコード

@Test
public void executeQuery() throws SQLException {
    // IDと名前でポスト情報を検索
    String sql = "select * from post where post_id = ? and post_name = ?";
    Object[] objects = {1, "プロジェクトマネージャ"};
    // ツールクラスのメソッドを使用して検索
    MysqlUtil.getInstance().executeQuery(sql, this::toPostVo, objects).forEach(System.out::println);
}

// ResultSetからVOに変換するメソッド
public PostVO toPostVo(ResultSet resultSet) throws SQLException {
    return new PostVO(
            resultSet.getInt("post_id"),
            resultSet.getString("post_name"),
            resultSet.getInt("post_department_id"),
            resultSet.getString("post_description"),
            resultSet.getString("post_responsibility"),
            resultSet.getDouble("post_salary")
    );
}