WebプログラミングⅡ　PHP

2012/5/15 久米

2.2 SQL文の基礎（１）

SQLとは？

データベースに問い合わせる言語
問合せはたった数種類

問合せ種類	説明	よく使う
CREATE	DBやテーブル（表）を作成する
INSERT	テーブル（表）にデータ（行）を挿入する	○
SELECT	データ（行）を取り出す	◎
UPDATE	データ（行）を更新する	○
DELETE	データ（行）を削除する
ALTER	テーブル（表）を変更する
DROP	DBやテーブル（表）を削除する

では、コマンドラインツールを使って試してみましょう。

~~注意）MySQLの文字コードをutf-8にしている場合、コマンドプロンプトでは日本語は文字化けします。~~
~~コマンドプロンプトの文字コードがShif_Jis(cp932)であるためです。~~
~~これを回避する方法はあるようですが難しいです。~~
~~よって授業ではコマンドラインからは英数文字だけ使用します。~~

MySQLコマンド起動

コマンドプロンプトを立ち上げ、下記のように打ち込む（太字部分）

C:\Documents and Settings\kume>cd C:\xampp\mysql\bin

C:\xampp\mysql\bin>mysql -u root -p
Enter password: （なし）
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

下記のように青文字のところを打ち込んでみる。
現在のデータベースをリストアップするshow databases;を実行。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql>

事前準備

初回のみ

自分用のデータベース作成　 create database データベース名 collate utf8_general_ci;
自分用のデータベースを使用することを宣言　use データベース名;
Windowsのコマンドプロンプトの文字コードはsjisなので、sjisで送るよと、MySQLに教えておく set names sjis;
　注）set names sjis;はコマンドプロンプトのみで使用。スクリプトで使うと脆弱性がある。

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 60 to server version: 4.0.17-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database web2 collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)

mysql> use web2;
Database changed

mysql> set names sjis;

mysql>

2回目以降

（以後mysqlを動かすごとに、use データベース名;　してからSQL文を発行する。）

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 60 to server version: 4.0.17-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use web2;
Database changed

mysql> set names sjis;

mysql>

2.2.1 CREATE TABLE 文

データを保存するためには、まずテーブルを作ります。
テーブルを作るとは、テーブル名と、列を定義することです。
列のデータ型は、前節参照

	文法	例
テーブル作成	CREATE TABLE テーブル名( 列名データ型 [, ・・・]);	CREATE TABLE monster( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32), level INT, ability VARCHAR(20), birthday DATE);

テーブルの確認方法

mysql> describe テーブル名;
あるいは
mysql> desc テーブル名;

自動連番キーidの付け方

列idを、整数型で主キーにして、かつ自動増加を設定する。
検索や更新するときなどに、必ず役に立ちます。

CREATE TABLE テーブル名(
id INT PRIMARY KEY AUTO_INCREMENT,
・・・
・・・
);

デフォルトの指定方法

CREATE TABLE テーブル名(
・・・
level INT DEFAULT 0,
ability VARCHAR(20) DEFAULT 'パンチ',
・・・
・・・
);

コラム　mysqlコマンドラインツールの使い方メモ　

機能	操作
途中で入力止めたい時	\c　ENTERキー
ヘルプを表示	?　ENTERキー
仮名漢字入力切替	Altキー＋半角/全角漢字キー
前に入力した文再入力	↑
次に入力した文再入力	↓
コピー	ウィンドウ上部を右クリックしてッメニュー編集から
ペースト	ウィンドウ上部を右クリックしてッメニュー編集から
簡単ペースト	ウィンドウ内をマウス右クリック

2.2.2 INSERT INTO 文

CREATE文でテーブルの列を作りました。
次はそこに行を入れていきます。
１行を１レコードといいます。
データは１行単位で挿入します。
挿入にはINSERT INTO文を使用します。

	文法	例
データ挿入	INSERT INTO テーブル名 (列名 [,・・・]) VALUES (値 [, ・・・]);	INSERT INTO monster (name, level, birthday) VALUES ('スライム', 3, '2004/5/10');
データ挿入（CREATEした順番で全列挿入なら列名記入省略可）	INSERT INTO テーブル名 VALUES (値 [, ・・・]);	INSERT INTO monster VALUES (100, 'チョコボ', 2, 'キック', '2004/5/10');

データ型ごとの値の記述方法

データ型	記述方法	例
数値型	そのまま数字	120
文字列型	'　'で囲む	'富士太郎'
日時型	'YYYY/MM/DD HH:MM:SS' 'YY/MM/DD HH:MM:SS' 'YYYY/MM/DD' 'YY/MM/DD' など	'2004/05/05 15:30:00' '04/05/10 09:00:00' '2004/10/10' '04/12/31'

NULL

値がない時は、どのデータ型でも、NULL　というデータを挿入します。

INSERT INTO monster
VALUES
(200, 'キングスライム', NULL, NULL, NULL);

2.2.3 SELECT 文

INSERT文で行にデータが入りました。
いろんな問い合わせでそれらのデータを読むことができます。
問い合わせにはSELECT文を使います。

問い合わせ種類	文法	例
すべての行	SELECT * FROM テーブル名;	SELECT * FROM monster;
列を指定	SELECT 列名 [, ...] FROM テーブル名;	SELECT name, ability FROM monster;
条件にあったものだけ	SELECT * あるいは列名 [, ...] FROM テーブル名 WHERE 検索条件;	SELECT * FROM monster WHERE level>3;
並び替えて結果取得	SELECT * あるいは列名 [, ...] FROM テーブル名 WHERE 検索条件 ORDER BY 列名 [DESC];	SELECT * FROM monster WHERE level>3 ORDER BY birthday;

検索条件WHEREの記述方法

データ型	条件	例
文字列	一致	WHERE name='Suraimu'
	不一致	WHERE name<>'Suraimu'
	部分一致	WHERE name LIKE '%ライ%'
数値	一致	WHERE level=3
	不一致	WHERE level<>3
	以上	WHERE level>=3
	未満	WHERE level<3
日付	以前	WHERE birthday<='2004/5/30'

複数条件

	形式	例
および	WHERE 条件１ AND 条件２	WHERE name LIKE 'スラ%' AND level>=3
あるいは	WHERE 条件１ OR 条件２	WHERE name LIKE 'スラ%' OR level>=3

便利な機能

	形式	意味
計算式の読み出し	SELECT (a+b+c) FROM honyarara_table;	列aと列bと列cの値の合計を表示
長い列名の置き換え	SELECT (a+b+c) t FROM honyarara_table; SELECT (a+b+c) as t FROM honyarara_table;	a+b+cという列名は長いのでtに置き換えて表示
合計順	SELECT *, (a+b+c) as t FROM honyarara_table ORDER BY (a+b+c) DESC;	a+b+cの合計の大きい順に表示

演習1　

下記表をCreateし、データを挿入し、のち問いに答えよ。

id	name	sugaku	kokugo	eigo
1	大島	55	65	75
2	前田	50	80	55
3	篠田	70	55	45
4	板野	55	40	100
5	渡辺	45	55	65
6	高橋	40	45	35
7	小嶋	60	50	70
8	柏木	75	40	55
9	宮澤	30	70	40
10	松井	45	45	80

問１　合計点の多い順に結果を表示せよ。同点であれば英語の高い方を上にせよ。

問２　数学、国語、英語の合計点が１７０以上の人を検索せよ。

問３　すべての科目が５０以上という万能タイプを検索せよ。

問４　ひとつの科目だけ５０以上それ以外は５０未満という、スペシャリストタイプを検索せよ。（難問）

問５　英語が他の科目よりも得意であり、かつ万能タイプを検索せよ。（難問）

演習2　自分の興味あるもののテーブルを作ってみよう

ただし、VARCHARとINTとDATE型の列をそれぞれ１つ以上定義してください。
また、自動連番idの列をいれておいてください。
また、5行以上の行を挿入してください。
　
WHEREで検索条件を適当に指定してSELECT文を発行してみてください。

id	VARCHARの列	INTの列	DATEの列	・・・	・・・

例

ブランド品ショッピングサイト用テーブル
モンスター図鑑
PHP関数リスト
求人票検索システム
電子掲示板用テーブル
などなど

コラム

SQLの予約語

あらかじめ予約された単語を、列名あるいはテーブル名に使うとエラーになります。
一般的な単語は避けるか、
どうしても使いたい一般単語があれば上記リンクで調べましょう。

複数テーブル

WebプログラミングⅡ PHP

2.2 SQL文の基礎（１）

SQLとは？

MySQLコマンド起動

事前準備

初回のみ

2回目以降

2.2.1 CREATE TABLE 文

テーブルの確認方法

自動連番キーidの付け方

デフォルトの指定方法

コラム mysqlコマンドラインツールの使い方メモ

2.2.2 INSERT INTO 文

データ型ごとの値の記述方法

NULL

2.2.3 SELECT 文

検索条件WHEREの記述方法

複数条件

便利な機能

演習1

演習2 自分の興味あるもののテーブルを作ってみよう

コラム

WebプログラミングⅡ　PHP

コラム　mysqlコマンドラインツールの使い方メモ　

演習1　

演習2　自分の興味あるもののテーブルを作ってみよう