データベースの基本概念
データベース（DB）とは、構造化された形式で情報を保存するためのシステムです。データは特定の規則に従って整理・保管され、効率的なアクセスが可能になります。
データベース管理システム（DBMS）は、これらのデータを管理するソフトウェアであり、代表的なものには MySQL や Oracle などがあります。
SQL（Structured Query Language）は、リレーショナルデータベースを操作するための標準的な言語です。
リレーショナルモデルの特徴
RDBMS（リレーショナルデータベース管理システム）では、情報は複数のテーブルによって表現されます。各テーブルは行と列から構成されており、関連するデータ同士がリンクされています。
- 一貫したテーブル形式により、保守性が向上します。
- SQLを使用することで、統一された方法でデータ操作が可能です。
SQL文の基本構文と種別
SQLステートメントは以下のように記述できます:
- 1行または複数行で記述でき、セミコロンで終了します。
- インデントや空白は任意ですが、可読性を高めるために推奨されます。
- MySQLでは大文字小文字を区別しませんが、キーワードは大文字で書くことが一般的です。
コメントの書き方:
- 単一行コメント:
-- コメント内容または# コメント内容（MySQL限定） - 複数行コメント:
/* コメント内容 */
SQLは以下の4つのカテゴリに分類されます:
- DDL（Data Definition Language）: データベースやテーブルなどのオブジェクトを作成・変更・削除します。
- DML（Data Manipulation Language）: データの挿入・更新・削除を行います。
- DQL（Data Query Language）: データの検索を行います。
- DCL（Data Control Language）: アクセス権限やユーザー管理に関係します。
DDLによるスキーマ定義
データベースの確認と選択:
SHOW DATABASES;
SELECT DATABASE();
USE database_name;データベースの作成と削除:
CREATE DATABASE IF NOT EXISTS db_name
[DEFAULT CHARACTER SET charset_name]
[COLLATE collation_name];
DROP DATABASE IF EXISTS db_name;テーブルの確認と詳細表示:
SHOW TABLES;
DESCRIBE table_name;
SHOW CREATE TABLE table_name;テーブルの作成例:
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '識別子',
name VARCHAR(50) NOT NULL COMMENT '氏名',
email VARCHAR(100) UNIQUE COMMENT 'メールアドレス'
) COMMENT='ユーザー情報';主なデータ型:
- 整数型: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
- 浮動小数点型: FLOAT, DOUBLE, DECIMAL(M,D)
- 文字列型: CHAR(n), VARCHAR(n), TEXT
- 日付時間型: DATE, TIME, DATETIME, TIMESTAMP, YEAR
テーブル構造の変更:
-- 新しいカラム追加
ALTER TABLE table_name ADD COLUMN new_col VARCHAR(255);
-- カラムの型変更
ALTER TABLE table_name MODIFY old_col TEXT;
-- カラム名の変更
ALTER TABLE table_name CHANGE old_name new_name VARCHAR(100);
-- カラムの削除
ALTER TABLE table_name DROP COLUMN col_name;
-- テーブル名の変更
ALTER TABLE old_table_name RENAME TO new_table_name;
-- テーブルの削除
DROP TABLE IF EXISTS table_name;
TRUNCATE TABLE table_name;DMLによるデータ操作
レコードの挿入:
INSERT INTO table_name (col1, col2) VALUES ('val1', 'val2');
INSERT INTO table_name VALUES ('val1', 'val2');レコードの更新:
UPDATE table_name SET col1 = 'new_val' WHERE condition;レコードの削除:
DELETE FROM table_name WHERE condition;DQLによるデータ取得
基本的なSELECT文:
SELECT col1, col2 FROM table_name;
SELECT * FROM table_name;
-- 別名設定
SELECT col1 AS alias1, col2 AS alias2 FROM table_name;
-- 重複除去
SELECT DISTINCT col_list FROM table_name;条件付き検索:
SELECT col_list FROM table_name WHERE conditions;集計関数の利用:
SELECT COUNT(*) FROM table_name;
SELECT MAX(col) FROM table_name;
SELECT MIN(col) FROM table_name;
SELECT AVG(col) FROM table_name;
SELECT SUM(col) FROM table_name;※NULL値は集計対象外となります。
グループ化とフィルタリング:
SELECT col_list FROM table_name
WHERE condition
GROUP BY group_column
HAVING having_condition;- WHERE句はグループ化前にフィルタリングし、HAVING句はグループ化後に適用されます。
- HAVING句では集計関数を使用できますが、WHERE句ではできません。
並び替え:
SELECT col_list FROM table_name
ORDER BY col1 ASC, col2 DESC;ページング処理:
SELECT col_list FROM table_name LIMIT offset, count;
-- 例: 2ページ目（10件ずつ）
SELECT * FROM table_name LIMIT 10, 10;※MySQLではLIMIT句がページングに使用されます。
クエリ実行順序:
- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
DCLによるアクセス制御
ユーザーの一覧と管理:
USE mysql;
SELECT User, Host FROM user;
-- ユーザー作成
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
-- パスワード変更
ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'new_password';
-- ユーザー削除
DROP USER 'username'@'host';権限の付与と剥奪:
SHOW GRANTS FOR 'username'@'host';
-- 権限付与
GRANT privileges ON database.table TO 'username'@'host';
-- 権限剥奪
REVOKE privileges ON database.table FROM 'username'@'host';主要な権限:
- ALL: 全ての権限
- SELECT: 読み取り
- INSERT: 追加
- UPDATE: 更新
- DELETE: 削除
- ALTER: スキーマ変更
- DROP: テーブルやデータベースの削除
- CREATE: 新規作成