MySQL

MySQLとPHPによるWebサーバー
サーバー
【Webサーバソフト】
HTMLファイル
HTML,PHPファイルの要求【80,443】
クライアント
http://〜/〜.html
http://〜/〜.php
HTMLファイルの送信
PHPファイル
Apache
IIS PHPファイル
HTML
PHP
DB (MySQL)
SQL Query
Record
要求されたPHPファイルからHTMLへの
動的変換
【RDBMS】
【サーバーサイドスクリプト言語】
1
RDBMS
リレーショナルデータベースマネジメントシステム (relational database management system,
RDBMS) とは、エドガー・F・コッドが提唱した関係モデル (リレーショナルモデル) に基づいた、コ
ンピュータのデータベースマネジメントシステム (DBMS) である。 RDBMS によって構築するデータ
ベースを、リレーショナルデータベースという。関係モデルにおける「関係」(リレーション) は、
一般には「表」(テーブル) と呼ばれることが多い。 2007年現在、RDBMS は最も一般的に使われてい
るデータベースマネジメントシステムである。
いくつかの RDBMS では、オブジェクト指向の機能拡張を行っている。このような RDBMS は、
ORDBMS と呼ばれる (オブジェクト関係データベース) 。
商用の RDBMS としては Oracle Database や IBM DB2 などが、オープンソースの RDBMS としては
MySQL や PostgreSQL などが有名である。ただし、これらの DBMS を真のRDBMS と呼んで良いのか
どうかについては、後述のとおり、議論の対象となっている。
RDBMS とされるシステムの多くは、データベース言語として SQL を採用している。
By Wikipedia
2
エドガー・フランク・コ
ッド
エドガー・フランク・コッド（Edgar Frank "Ted" Codd, 1923年8月23日 - 2003年4月18日）は、イギリス生まれの情報工学者。リレーショナルデータベースの理
論に多大な貢献をした。IBMに勤務しながら、データベースの関係モデルを創造した。他にも情報工学に数々の貢献をしているが、関係モデルはデータ管理
の一般理論として大きな影響を与え、彼の最大の業績と言われている。
[編集] 生涯
コッドはイギリスのドーセット州ポートランドで生まれた。オックスフォード大学エクセター・カレッジで数学と化学を専攻する。第二次世界大戦ではイギ
リス空軍のパイロットとして参戦した。1948年、アメリカ合衆国ニューヨーク州に移住し、IBMでプログラマとして就職した。1953年、上院議員ジョセフ・
マッカーシーの許可を得てカナダのオタワに移住。10年後アメリカ合衆国に戻り、アナーバー (ミシガン州)のミシガン大学で情報工学の博士号を取得。2年後、
サンノゼ (カリフォルニア州)に移り、IBMのアルマーデン研究所に勤務するようになった。
1960年代から1970年代、コッドはデータ配置に関する理論を構築し、1970年 "A Relational Model of Data for Large Shared Data Banks" （大規模共有データバンク
のデータ関係モデル）という論文を発表した（IBM内ではその1年前に公表している）。しかし、IBMはライバルがそれを実装し始めるまで彼の提案を実行に
移そうとせず、コッドは失望した。
当初、IBMはIMS/DBの収益を守るため、関係モデルを実装することを拒んだ。コッドはIBMの顧客に自身のモデルを実装した場合の可能性を提示し、顧客か
らIBMに圧力をかけさせた。そこでIBMは System Rプロジェクトを立ち上げたが、その開発チームとコッドは分離され、しかもコッドの理論に精通した者は
チーム内にいなかった。結果として彼らはコッドの Alpha 言語を使わずにリレーショナルでないSEQUEL言語を開発した。とはいえ SEQUEL は以前のシステ
ムより優れていた。ラリー・エリソンは SEQUEL 完成前に発表された論文に基づいて Oracle を完成させ先に発売している。IBMは、SQL/DS を発売した。な
お、SEQUEL という名前は既に他で使われていたので、SQL に改称した。
コッドはクリス・デイトと共同で関係モデルの拡張と開発を続けた。関係の正規化 (正規形) の一種であるボイス・コッド正規形には彼の名が刻まれている。
関係モデルは1980年代に隆盛期を迎え、コッドはしばしば古いデータベースに関係モデルをちょっとだけ導入したような悪質なベンダーと戦うキャンペーン
を展開しなければならなくなった。そのキャンペーンの一環でコッドの12の規則を公表し、リレーショナルデータベースを定義した。彼のキャンペーンは
SQL言語にもおよび、コッドはこれを彼の理論の間違った実装であるとした。このためコッドのIBM内の立場は難しいものとなり、コッドはクリス・デイト
らと新たなコンサルティング会社を設立した。
1981年、コッドはチューリング賞を受賞した。
2003年4月18日、エドガー・F・コッドはフロリダ州ウィリアムズ・アイランドの自宅で心不全で亡くなった。享年79。
By Wikipedia
3
MySQLのインストール
【手順1】mysql-serverのインストール（スーパーユーザー）
[root@fedora ~]# yum -y install mysql-server
4
MySQL設定ファイルの編集
【手順 2】MySQL設定ファイル(my.cnf)編集
viまたはテキストエディタ(gedit)で編集。geditを使う場合はファイルのアクセス権限を変更する。
[root@fedora ~]# vi /etc/my.cnf ← MySQL設定ファイル編集（赤字部分を追加する）
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
default-character-set = utf8 ← 追加(MySQLサーバーの文字コードmyをUTF-8にする)
以下を追加(MySQLクライアントの文字コードをUTF-8にする)
[mysql]
default-character-set = utf8
5
MySQL起動
【手順 3】MySQL起動
[root@fedora ~]# /etc/rc.d/init.d/mysqld start ← MySQL起動
MySQL データベースを初期化中: Installing MySQL system tables...
OK
Filling help tables...
.
.
MySQL を起動中:
[ OK ]
【手順 4】MySQL自動起動設定
[root@fedora ~]# /sbin/chkconfig mysqld on
←
MySQL自動起動設定
[root@fedora ~]# /sbin/chkconfig --list mysqld ← MySQL自動起動設定確認
mysqld
0:off 1:off 2:on
3:on
4:on
5:on
6:off ←
ランレベル2〜5のonを確認
6
MySQLセキュア設定 I
【手順 5】MySQLセキュア設定
[root@fedora ~]# mysql_secure_installation
←
MySQL初期設定
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
←
空ENTER
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n]
← 空ENTER(rootパスワード設定)
New password:
← rootパスワード応答
Re-enter new password:
← rootパスワード応答(確認)
Password updated successfully!
Reloading privilege tables..
... Success!
＜続く＞
7
MySQLセキュア設定 II
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n]
... Success!
←
空ENTER(匿名ユーザー削除)
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n]
... Success!
←
空ENTER(リモートからのrootログイン禁止)
＜続く＞
8
MySQLセキュア設定 III
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n]
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
←
空ENTER(testデータベース削除)
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n]
... Success!
←
空ENTER
Cleaning up…
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
＜以上＞
9
WebMySQL 3.32Jのインストール
■ WebMySQL（MySQL用ユーティリティ）3.32Jのインストー
ル
1.配布したwebmslを/var/www/html/にコピー
2.http://localhost/webmsl/start.phpにアクセス
3.MebMySQLでDB「mysql」、テーブル「user」内に
Host：localhost、User：comm_access、 password：kagoshima
のレコードを作成
10
実験
■ WebMySQL2.1を使用したDBの作成
1.データベース「Student」を作成
2.データベース「Student」内にテーブル「S_Base」を作成
3.テーブル「S_Base」内にフィールド「Std_id(Null:No,PrimaryKey,char20)」
「S_name(char40) 」「E_mail(char200) 」を作成
4.テーブル「S_Base」内にとりあえずダミーの１レコードを作成する
11
実験
■ エディタ（mi）を使用したPHPファイルの作成
1./Library/Webserver/Documents/内にフォルダ「student」を作成
2.USBメモリ内の「dbaccess.php」を/Library/Webserver/Documents/student内にコピー
3.エディタmiにて「 dbaccess.php」の中身を確認
4.http://localhost/student/dbaccess.php をアクセスしてみる。
課題：新規レコードの追加、各レコードの削除ができるようにPHPファイル
「 dbaccess.php」を変更せよ
※学籍番号「1111111111」氏名「山田太郎」E_mail「[email protected]」のレコードを追加するには
insert into S_base (Std_id, S_name, E_mail) values (‘1111111111’, ‘山田太郎’, ‘[email protected]’)
というクエリー文を発効すれば良い。
※学籍番号「1111111111」のレコードを削除するには
delete from S_base where Std_id=‘1111111111’
というクエリー文を発効すれば良い。
12
RDBMSの構成
ファイルシステム
DataBase
・・・・
Table
13
Table (Field & Record)
フィールド名：name
テーブル名：gakusei
レコード（行）
・
・
・
・
ID
name
Email
1234567890
鹿児島太郎
[email protected]
1234567891
鹿児島二郎
[email protected]
1234567892
薩摩花子
[email protected]
・
・
・
・
・
・
・
・
・
・
・
・
・
・
・
フィールド（列）
14
RDBMSの設計
1. データベース名の決定
2. テーブル構成の設計
3. 各テーブル内フィールド（列）の決定
例）
学生の基本情報（学籍番号、氏名、電子メール）
<Std_id, S_name, E_mail>
15
学生DB
1. データベース名：Student
2. テーブルの決定：S_base
3.フィールド（列）の決定：
S_base
→
Std_id(char20)
S_name (char40)
E_mail (char200)
16
フィールドの型(Field Type)
CHAR(バイト数)
VARCHAR(バイト数)
INT
FLOAT
DOUBLE
DATE
TIME
DATETIME
TEXT
MEDIUMTEXT
LONGTEXT
固定長文字列
可変長文字列
整数
浮動小数点数
倍精度浮動小数点数
日付
時間
日付時間
可変長文字列
可変長文字列
可変長文字列
など
17
学生DB（テーブル）
Table: S_base
Std_id
S_name
E_mail
1234567890
鹿児島太郎
[email protected]
1234567891
鹿児島二郎
[email protected]
1234567892
薩摩花子
[email protected]
・
・
・
・
・
・
・
・
・
・
18
SQL基礎
■データ操作命令（レコードの読み出し、生成、変更、削除）
SELECT, INSERT, UPDATE, DELETE
■トランザクション（排他制御）
■データ定義命令（DB, Table, Fieldの生成、削除、属性変更）
CREATE, DROP, ALTER, etc.
19
データ操作命令:SELECT
■テーブルからレコード（行）を得る。
SELECT [ALL | DISTINCT] expression [, expression….. ]
FROM table_name [, table_name ….. ]
[WHERE where_expression ]
[GROUP BY expression [, expression….. ] ]
[HAVING where_expression ]
[ORDER BY expression [, expression….. ] ]
Expression：任意の式
table_name ：レコード（行）を選択するテーブル名
where_expression：レコード（行）を絞り込む条件式
20
SELECT
■テーブルS_baseからレコード（行）を得る。
SELECT Std_id, S_name FROM S_base
SELECT * FROM S_base
SELECT Std_id FROM S_base
mysql> SELECT Std_id, S_name FROM S_base;
+------------+--------------+
| Std_id
| S_name
|
+------------+--------------+
| 1234567890 | 鹿児島太郎 |
| 1234567891 | 鹿児島二郎 |
| 1234567892 | 薩摩花子
|
+------------+--------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM S_base;
+------------+--------------+
| Std_id
| S_name
|
+------------+--------------+
| 1234567890 | 鹿児島太郎 |
| 1234567891 | 鹿児島二郎 |
| 1234567892 | 薩摩花子
|
+------------+--------------+
3 rows in set (0.00 sec)
mysql> SELECT Std_id from S_base;
+------------+
| Std_id
|
+------------+
| 1234567890 |
| 1234567891 |
| 1234567892 |
+------------+
3 rows in set (0.00 sec)
21
SELECT（WHERE句）
WHERE boolean_expression
■テーブルLectureからレコード（行）を得る。WHERE句
SELECT * FROM Lecture
SELECT * FROM Lecture WHERE Std_id=‘1234567890’
SELECT * FROM Lecture
WHERE Std_id=‘1234567890’ and L_name=‘数学%’
mysql> SELECT * FROM Lecture;
+------------+----------------+
| Std_id
| L_name
|
+------------+----------------+
| 1234567890 | 工学概論
|
| 1234567890 | 数学II
|
| 1234567892 | プログラミング |
| 1234567890 | プログラミング |
| 1234567892 | アルゴリズム
|
+------------+----------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM Lecture
WHERE Std_id='1234567890';
+------------+----------------+
| Std_id
| L_name
|
+------------+----------------+
| 1234567890 | 工学概論
|
| 1234567890 | 数学II
|
| 1234567890 | プログラミング |
+------------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM Lecture
WHERE Std_id=‘1234567890’
and L_name=‘数学%’;
+------------+----------------+
| Std_id
| L_name
|
+------------+----------------+
| 1234567890 | 数学II
|
+------------+----------------+
1 rows in set (0.00 sec)
22
SELECT （GROUP BY句）
GROUP BY expression [, expression]
■テーブルLectureからレコード（行）を得る。
SELECT Std_id FROM Lecture GROUP BY Std_id
SELECT Std_id, count(*) FROM Lecture GROUP BY Std_id
SELECT L_name, count(*) FROM Lecture GROUP BY L_name
mysql> SELECT Std_id FROM Lecture GROUP BY
Std_id;
+------------+
| Std_id
|
+------------+
| 1234567890 |
| 1234567892 |
+------------+
2 rows in set (0.00 sec)
mysql> SELECT Std_id, count(*) FROM Lecture
GROUP BY Std_id;
+------------+----------+
| Std_id
| count(*) |
+------------+----------+
| 1234567890 |
3 |
| 1234567892 |
2 |
+------------+----------+
2 rows in set (0.00 sec)
mysql> SELECT L_name, count(*) FROM
Lecture GROUP BY L_name;
+----------------+----------+
| L_name
| count(*) |
+----------------+----------+
| アルゴリズム
|
1 |
| プログラミング |
2 |
| 工学概論
|
1 |
| 数学II
|
1 |
+----------------+----------+
4 rows in set (0.00 sec)
23
SELECT （ORDER BY句）
ORDER BY expression [ASC | DESC] [, expression [ASC | DESC]……]
■テーブルLectureからレコード（行）を得る。
SELECT * FROM Lecture ORDER BY Std_id ASC
SELECT * FROM Lecture ORDER BY Std_id DESC
mysql> SELECT * FROM Lecture ORDER BY
Std_id ASC;
+------------+----------------+
| Std_id
| L_name
|
+------------+----------------+
| 1234567890 | 工学概論
|
| 1234567890 | 数学II
|
| 1234567890 | プログラミング |
| 1234567892 | プログラミング |
| 1234567892 | アルゴリズム
|
+------------+----------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM Lecture ORDER BY
Std_id DESC;
+------------+----------------+
| Std_id
| L_name
|
+------------+----------------+
| 1234567892 | プログラミング |
| 1234567892 | アルゴリズム
|
| 1234567890 | 工学概論
|
| 1234567890 | 数学II
|
| 1234567890 | プログラミング |
+------------+----------------+
5 rows in set (0.00 sec)
24
データ操作命令:INSERT
■テーブルにレコード（行）を追加する。
INSERT [INTO] table_name [( field_name [, field_name …..] )]
[VALUES (value [, value …..] )]
[select_statement]
table_name ：レコード（行）を追加するテーブル名
Field_name：フィールド（列）名
value：挿入するフィールド（列）の値
Select_statement：SELECT命令
25
INSERT
■テーブルS_baseにレコード（行）を追加する。
INSERT INTO S_base (Std_id, S_name)
VALUES (‘1234567893’, ‘西郷隆盛’)
INSERT INTO S_base (Std_id, S_name)
SELECT Std_id, L_name FROM Lecture WHERE Std_id=‘1234567892’
mysql> INSERT INTO S_base (Std_id, S_name)
VALUES('1234567893', '西郷隆盛’);
Query OK, 1 row affected (0.00 sec)
mysql> select * from S_base;
+------------+--------------+
| Std_id
| S_name
|
+------------+--------------+
| 1234567890 | 鹿児島太郎 |
| 1234567891 | 鹿児島二郎 |
| 1234567892 | 薩摩花子
|
| 1234567893 | 西郷隆盛
|
+------------+--------------+
4 rows in set (0.00 sec)
mysql> INSERT INTO S_base
-> FROM Lecture WHERE
Query OK, 2 rows affected
レコード数: 2 重複数: 0
(Std_id, S_name) SELECT Std_id, L_name
Std_id='1234567892';
(0.01 sec)
Warnings: 0
mysql> select * from S_base;
+------------+----------------+
| Std_id
| S_name
|
+------------+----------------+
| 1234567890 | 鹿児島太郎
|
| 1234567891 | 鹿児島二郎
|
| 1234567892 | 薩摩花子
|
| 1234567893 | 西郷隆盛
|
| 1234567892 | アルゴリズム
|
| 1234567892 | プログラミング |
+------------+----------------+
26
データ操作命令:UPDATE
■テーブルの既存レコード（行）を更新する。
UPDATE table_name
SET field_name= expression [, field_name= expression.. ] )]
[WHERE where_expression ]
注）WHERE句を指定しなければ対象テーブルの全レコードが更新される。
table_name ：更新するテーブル名
Field_name：フィールド（列）名
expression：任意の値、もしくは式
where_expression：レコード（行）を絞り込む条件式
27
UPDATE
■テーブルS_baseのレコード（行）の値を更新する。
UPDATE S_base SET Std_id=‘1234567894’, S_name=‘大久保利道’
WHERE S_name =‘アルゴリズム’
UPDATE S_base SET Std_id=‘1234567894’, S_name=‘大久
保利道’ where S_name=‘アルゴリズム’;
Query OK, 1 row affected (0.00 sec)
一致数(Rows matched): 1 変更: 1 Warnings: 0
mysql> select * from S_base;
+------------+----------------+
| Std_id
| S_name
|
+------------+----------------+
| 1234567890 | 鹿児島太郎
|
| 1234567891 | 鹿児島二郎
|
| 1234567892 | 薩摩花子
|
| 1234567893 | 西郷隆盛
|
| 1234567894 | 大久保利道
|
| 1234567892 | プログラミング |
+------------+----------------+
6 rows in set (0.00 sec)
28
データ操作命令:DELETE
■テーブルの既存レコード（行）を削除する。
DELETE FROM table_name
[WHERE where_expression ]
注）WHERE句を指定しなければ対象テーブルの全レコードが削除される。
table_name ：更新するテーブル名
where_expression：レコード（行）を絞り込む条件式
29
DELETE
■テーブルS_baseのレコード（行）を削除する。
DELETE FROM S_base WHERE S_name =‘プログラミング’
mysql> DELETE FROM S_base WHERE S_name=’プログラミング’;
Query OK, 1 row affected (0.00 sec)
mysql> select * from S_base;
+------------+--------------+
| Std_id
| S_name
|
+------------+--------------+
| 1234567890 | 鹿児島太郎 |
| 1234567891 | 鹿児島二郎 |
| 1234567892 | 薩摩花子
|
| 1234567893 | 西郷隆盛
|
| 1234567894 | 大久保利道 |
+------------+--------------+
5 rows in set (0.00 sec)
30
データ操作命令のまとめ
■SELECT
レコードの選択（読み出し）
select
records
■INSERT
レコードの挿入（生成）
insert
■UPDATE
レコードの更新
update
■DELETE
New Record
Change Record
レコードの削除
delete
Delete Record
31
PHPによるMySQL操作
■MySQLデータベースのOpenとClose
<?PHP
$G_connect=mysql_connect('host_name', 'user_name', 'passwoed');
mysql_select_db('Risyu_test', $G_connect);
printf(“<HTML>¥n”);
・
・
printf(“</HTML>¥n”);
mysql_close($G_connect);
?>
’host_name’はサーバー上でMySQLが稼働していれば localhost
32
PHPによるMySQL操作
■Queryの発行とレコード情報の取得
$txt_query=“select * from S_base”;
$result=mysql_query($txt_query,$G_connect);//Queryを発行、結果をバッファ$resultへ
$num_rows = mysql_num_rows($result); //対象レコード数
printf(“<TABLE>¥n”);
printf(“<TR> ¥n”);
printf(“<TD>学籍番号 <TD> <TD>氏名<TD> ¥n”);
printf(“</TR> ¥n”);
for($i=0;$i < $num_rows ; $i++){
$row=mysql_fetch_array($result); //バッファ$resultから配列$row[]への変換
printf(“<TR> ¥n”);
printf(“<TD>%s</TD> ¥n”, $row[‘Std_id’] );
printf(“<TD>%s</TD> ¥n”, $row[‘S_name’] );
printf(“</TR> ¥n”);
}
printf(“</TABLE>¥n”);
mysql_free_result($result); //バッファ$resultの解放
33
PHPによるMySQL操作
■クライアントへ渡されるHTML
<HTML>
<TABLE>
<TR><TD>学籍番号 <TD> <TD>氏名<TD> </TR>
<TR> <TD>1234567890</TD> <TD>鹿児島太郎</TD></TR>
<TR> <TD>1234567891</TD> <TD>鹿児島二郎</TD></TR>
<TR> <TD>1234567892</TD> <TD>薩摩花子</TD></TR>
・
・
<TABLE>
</HTML>
34
まとめ
■MySQL
Linux, Microsoft Windows, FreeBSD, Sun Solaris, IBM’s AIX, Mac OS X, HP-UX, AIX, QNX, Novell
NetWare, SCO OpenUnix, SGI Irix, and Dec OSF といった様々なプラットホームに対応している。
オープンソース
日本MySQLユーザー会
http://www.mysql.gr.jp/
■PHP
PostgreSQL,MySQL,Oracle,Sybaseといった各種データベースとの連携に優れている。
オープンソース
日本PHPユーザー会
http://www.php.gr.jp/
35