SQLでの間違えやすい箇所
表の結合
自然結合(natural join)
結合に使う列を、表名または別表名で修飾するとエラーになる
--エラーにならない select ename,deptno from emp natural join dept; --エラー select ename,emp.deptno from emp natural join dept;
USING句を使用した結合
結合に使う列を、表名または別表名で修飾するとエラーになる
NATURAL JOINとUSINGは同時に使用出来ない
--エラー select ename,emp.deptno from emp natural join dept using(deptno);
ON句を使用した結合
結合に使う列を、表名または別表名で修飾する必要がある
--エラーにならない select ename,e.deptno from emp e join dept d on e.deptno=d.deptno;
ORACLE独自結合構文による等価結合
結合条件はwhere句に指定する
同じ名前の列がある場合は表名または別表名で修飾する必要がある
制約
NOT NULL制約は列レベルでのみ定義出来る
複合制約は表レベルでのみ定義出来る
外部キー制約の表レベル構文では、FOREIGN KEY キーワードは使わない
CHECK制約の定義には、疑似列や関数の一部、、他の行を参照する問い合わせは定義できない
--列レベル構文(,が付かない) create table emp (deptno number(6) constraint deptno_fk references dept(deptno)); --表レベル構文(,が付く) create table emp (deptno number(6), constraint deptno_fk foreign key(deptno) references dept(deptno));
ビュー
ビューを通じたデータの変更操作の可否
| ビューの定義に含まれている要素 | 行の削除 | 行の更新 | 行の追加 |
|---|---|---|---|
| ビューに含まれていない実表の列にNOT NULL制約が定義されている | ○ | ○ | × |
| 式によって定義された列 | ○ | × | × |
| DISTINCTキーワード | × | × | × |
| ROWNUM疑似列 | × | × | × |
| グループ関数 | × | × | × |
| GROUP BY句 | × | × | × |
SQLの分類とトランザクション
| 分類 | 説明 | コマンド | 自動コミット |
|---|---|---|---|
| DML(Data Manipulation Language) | 表内のデータを直接操作する | SELECT INSERT UPDATE DELETE MERGE |
× |
| DDL(Data Definition Language) | データベースオブジェクトを作成、変更、削除する | CREATE ALTER DROP RENAME TRUNCATE COMMIT |
○ |
| DCL(Data Control Language) | データベースに対する権限を付与、取り消す | GRANT REVOKE |
○ |
| トランザクション制御 | トランザクションの制御 | COMMIT ROLLBACK SAVEPOINT |
- |
SELECT文
SELECTの使い方
SELECT 式のリスト; --条件に合う行を検索する
FROM句で表を指定する
SELECT 列名 FROM 表名;
列に別名を付ける
SELECT 列名 AS 別名 FROM 表名;
WHERE句で検索条件を指定する
SELECT 列名 FROM 表名 WHERE 検索条件;
IN演算子
SELECT 列名 FROM 表名 WHERE 列名 IN(値1,値2,...); --列挙した値のどれかなら真
BETWEEN演算子
SELECT 列名 FROM 表名 WHERE 列名 BETWEEN 値1 AND 値2; --値1〜値2の範囲内なら真
NULLを検索する
SELECT 列名 FROM 表名 WHERE 列名 IS [NOT] NULL;
LIKE演算子
SELECT 列名 FROM 表名 WHERE 列名 LIKE パターン文字列; --パターンマッチングを行なう
パターン文字列で使用出来るワイルドカード
| % | 0文字以上の任意の文字列 |
| _ | 任意の文字列 |
DISTINCTで重複データを除く
SELECT DISTINCT 列名 FROM 表名 ;
ORDER BY句で並び替える
SELECT 列名 FROM 表名 ORDER BY 列名 [ASC]; --昇順に並び替える SELECT 列名 FROM 表名 ORDER BY 列名 DESC; --昇順に並び替える
集計関数を使う
SELECT SUM(列名) FROM 表名; --合計値 SELECT AVG(列名) FROM 表名; --平均値 SELECT MAX(列名) FROM 表名; --最大値 SELECT MIN(列名) FROM 表名; --最小値 SELECT COUNT(列名) FROM 表名;--行数
GROUP BY句でグループにまとめる
SELECT 列名 FROM 表名 GROUP BY グループ化する列名;
HAVING句でグループ化の結果を判定する
SELECT 列名 FROM 表名 GROUP BY グループ化する列名 HAVING 検索条件;
CASE式
if,switchと似た場分け処理
SELECT 列名 CASE WHEN 値式 = 値式1 THEN 戻り値1 WHEN 値式 = 値式2 THEN 戻り値2 ... ELSE 戻り値 END FROM 表名
表の結合
交差結合
SELECT * FROM 表名1 CROSS JOIN 表名2;--二つの表の全ての行の組み合わせを作る
内部結合
SELECT * FROM 表名1 [INNER] JOIN 表名2 ON 結合条件;--指定列を基準にして二つの表に共通にある行を結合する
左外部結合
SELECT * FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 結合条件;--左の表にしかない行+共通にある行を取り出して結合する
右外部結合
SELECT * FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 結合条件;--共通にある行+右の表にしかない行を取り出して結合する
USING句で結合を行なう
上記でつかった ON 検索条件を代替するもの
SELECT * FROM 表名1 [INNER] JOIN 表名2 USING(列名リスト); SELECT * FROM 表名1 LEFT [OUTER] JOIN 表名2 USING(列名リスト); SELECT * FROM 表名1 RIGHT [OUTER] JOIN 表名2 USING(列名リスト); SELECT * FROM 表名1 FULL [OUTER] JOIN 表名2 USING(列名リスト);
自然結合を行なう
USING句を使った結合よりさらに簡潔に結合を行なう
SELECT * FROM 表名1 NATURAL [INNER] JOIN 表名2; SELECT * FROM 表名1 NATURAL LEFT [OUTER] JOIN 表名2; SELECT * FROM 表名1 NATURAL RIGHT [OUTER] JOIN 表名2; SELECT * FROM 表名1 NATURAL FULL [OUTER] JOIN 表名2;
自己結合
SELECT 列名 FROM 表名 別名1, 表名 別名2;--同じ表同士を結合する
問い合わせ結果の集合
UNIONで和集合を得る
SELECT 命令 UNION [ALL] SELECT 命令;--二つの表をまとめる
INTERSECTで共通集合を得る
SELECT 命令 INTERSECT SELECT 命令;--二つの表の共通部分を取得する
EXCEPTで差集合を得る
SELECT 命令 EXCEPT SELECT 命令;--はじめのSELECT結果からふたつめのSELECT結果を引いた残りを抜き出す
副問い合わせ
SELECT文中の値式の部分にSELECT命令を書く事が出来る。より複雑な問い合わせが可能になる
副問い合わせはWHERE句の中に記述される事が多いが、構文としては、SELECT,FROM,WHERE,HAVING の中に記述出来る。
SELECT 列名 FROM 表名 WHERE (SELECT ...);
IN演算子
SELECT 列名 FROM 表名 検索条件 [NOT] IN (SELECT ...);--()の中のどれかなら真
EXISTS演算子
SELECT 列名 FROM 表名 [NOT] EXISTS (SELECT ...);--副問い合わせの結果が1行以上あれば真
ANY演算子
SELECT 列名 FROM 表名 値式 比較演算子 ANY (SELECT ...);--副問い合わせの結果の中のどれかと比較条件が取れればTRUE
ALL演算子
SELECT 列名 FROM 表名 値式 比較演算子 ALL (SELECT ...);--副問い合わせの結果の中の全てと比較条件が取れればTRUE
ビュー
ビューとは問い合わせで得た結果に、識別用の名前(ビュー名)を付け再利用出来るようにした仮想の表。
ビューの定義
CREATE VIEW ビュー名 (列名1,列名2, ...) AS SELECT文; CREATE VIEW ビュー名 AS SELECT文; SELECT * FROM ビュー名;--ビューを利用した文
ビューを使った更新
ビューに対してUPDATE,DELETE,INSERTなどの更新系命令を実行し、そのビューの元になった実表を更新する事が出来る
ビューの削除
DROP VIEW ビュー名;
SQLの基本
基本的な記述ルール
コメント
--行末までコメント /* この範囲がコメント */
命令の終わりにはセミコロン ; を記述する
識別子
先頭は小文字かアンダーバー。２文字目以降は英文字、数字、アンダーバー
大文字と小文字の区別はない
予約語は使えない
文字列定数
(')シングルクォーテーションで囲む
データ型
文字列型
| CHAR[(n)] | 最大n文字の固定長文字列 |
| VARCHAR(n) | 最大長nの可変長文字列 |
真数型
| INTEGER | 符号付き整数 |
| NUMERIC(n,m) | n桁の10進数。小数点以下m桁 |
| DECIMAL(n,m) |
ビット列型
| BIT[(n)] | 最大nの固定長ビット列 |
| BIT VARYING(n) | 最大長nの可変長ビット列 |
日付型
| DATE | 日付 |
| TIME | 時刻 |
| TIMESTAMP[(f)] | 日付と時刻 |
| INTERVAL | 期間 |
演算子
算術演算子
集合演算子
| UNION[ALL] | 和集合 |
| EXCEPT[ALL] | 差集合 |
| INTERSECT[ALL] | 積集合 |
文字列結合演算子
文字列の結合には || を使う
比較演算子
| 小さい | |
| <= | 小さいか等しい |
| > | 大きい |
| >= | 大きいか等しい |
| = | 等しい |
| !=,<> | 等しくない |
その他の演算子
| EXISTS | 存在するかの判定 |
| ANY | どれかという修飾をする |
| SOME | ANYの同義語 |
| ALL | すべてという修飾をする |
| IN | 候補にあるかの判定 |
| BETWEEN | 範囲内であることの判定 |
| LIKE | パターンマッチングを行なう |
データベースの作成
CREATE DATABASE データベース名;
データベースの削除
DROP DATABASE データベース名;
表を作成する
CREATE TABLE 表名 (列名1 データ型,列名2 データ型,...);
表の削除
DROP TABLE 表名;
制約を設定する
CREATE TABLE 表名( 列名 データ型 NOT NULL, -- NOT NULL制約 列名 データ型 UNIQUE, -- UNIQUE制約 列名 データ型 CHECK(チェック式), -- CHECK制約 列名 データ型 PRIMARY KEY(主キー), -- 主キー制約 列名 データ型 REFERENCES 参照先表名(参照先列名) -- 外部キー制約 );
列の追加と削除
ALTER TABLE 表名 ADD [COLUMN] 列名 データ型; --列の追加 ALTER TABLE 表名 DROP [COLUMN] 列名 データ型; --列の削除
制約の追加と削除
ALTER TABLE 表名 ADD 制約名(列名); --制約の追加 ALTER TABLE 表名 DROP CONSTRAINT 制約名; --制約の削除
ユーザーの作成
データベースによりユーザー作成のコマンドは違う
ユーザー権限を付与
GRANT 権限 ON オブジェクト名 TO ユーザー名 [WITH GRNT OPTION];
| 権限の種類 | 説明 |
|---|---|
| SELECT | 表を検索できる |
| INSERT | 表に行を挿入できる |
| DELETE | 表から行を削除できる |
| UPDATE | 表の行を更新できる |
| REFERENCES | 外部キーからの参照を許す |
| ALL PRIVILEGES | 上記の全て |
権限の削除
REVOKE 権限 ON オブジェクト名 FROM ユーザー名;
データの入力
INSERT INTO 表名 (列名1,列名2,...) VALUES(値1,値2,...);
データの更新
UPDATA 表名 SET 列名1=値1, 列名2=値2,... WHERE 検索条件;
データの削除
DELETE FROM 表名 WHERE 検索条件;
セッションを破棄する
<?php # セッションを開始します。 session_start(); # セッション変数を登録します。 $_SESSION['sample'] = 'PHP逆引きレシピ'; # セッション変数を別の変数に保存しておきます。 $oldSession = $_SESSION; # セッション変数をすべて解除します。 $_SESSION = array(); # セッションCookieも削除します。 if (isset($_COOKIE[session_name()])) { setcookie(session_name(), '', time()-42000, '/'); } function h($var) { // HTMLでのエスケープ処理をする関数 if (is_array($var)) { return array_map('h', $var); } else { return htmlspecialchars($var, ENT_QUOTES); } } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <title>セッションを破棄したい</title> </head> <body> <?php echo '<p>破棄前のセッション情報:</p>'; echo '<pre>'; print_r(h($oldSession)); echo '</pre>'; # セッションを破棄します。 session_destroy(); echo '<p>破棄後のセッション情報:</p>'; echo '<pre>'; print_r(h($_SESSION)); echo '</pre>'; ?> </body> </html>
セッションCookieのパラメータを設定したい
<?php # セッションCookieのパラメータを設定します。 session_set_cookie_params(0, '/', 'www.example.jp'); # セッションを開始します。 session_start(); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <title>セッションCookieのパラメータを設定したい</title> </head> <body> <?php echo '<p>現在のセッションCookieの設定内容</p>'; echo '<pre>'; print_r(session_get_cookie_params()); echo '</pre>'; ?> </body> </html>
セッション変数を破棄する
<?php session_start(); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <title>セッション変数を破棄したい</title> </head> <body> <?php $_SESSION['user'] = 'user'; $_SESSION['name'] = 'name'; echo '<table border="1" cellpadding="3">'; echo '<tr><td>元のセッション変数</td><td><pre>'; print_r(h($_SESSION)); echo '</pre></td></tr>'; echo '<tr><td>nameに配列をセット</td><td><pre>'; $_SESSION['name'] = array('PHP逆引きレシピ', 'CodeIgniter徹底入門'); print_r(h($_SESSION)); echo '</pre></td></tr>'; echo "<tr><td>userに「''」空をセット</td><td><pre>"; $_SESSION['user'] = ''; print_r(h($_SESSION)); echo '</pre></td></tr>'; echo '<tr><td>nameを破棄</td><td><pre>'; unset($_SESSION['name']); print_r(h($_SESSION)); echo '</pre></td></tr>'; echo '</table>'; function h($var) { // HTMLでのエスケープ処理をする関数 if (is_array($var)) { return array_map('h', $var); } else { return htmlspecialchars($var, ENT_QUOTES); } } ?> </body> </html>
セッション変数を使う
<?php session_start(); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <title>セッション変数を使いたい</title> </head> <body> <?php if (!isset($_SESSION['visited'])) { echo '初めての訪問です。今からセッションを開始します。'; $_SESSION['visited'] = 1; } else { echo '訪問回数: ' . h(++$_SESSION['visited']) . '<br />'; if (isset($_SESSION['date'])) { echo '前回の訪問日時: ' . h($_SESSION['date']) . '<br />'; } } $_SESSION['date'] = date('Y年m月d日 H時i分s秒'); function h($var) { // HTMLでのエスケープ処理をする関数 return htmlspecialchars($var, ENT_QUOTES); } ?> </body> </html>
