知的システム構築工房 　　Linux設定セミナーシリーズ 第6回　MySQL設定 2011年3月17日（木） 株式会社 アセンディア 1

目的 当セミナーを通して、 Linuxにデータベース環境を構築し、データベース と連携したWebアプリ開発を体得する

目標 当セミナーを受講することにより １．データベースについて説明できるようになること ２．データベースをインストールできるようになること 　１．データベースについて説明できるようになること 　２．データベースをインストールできるようになること 　３．データベースを管理できるようになること 　４．データベースと連携したWebアプリを開発できるよう 　　　になること

セミナー内容 1．データベースについて 2．MySQLインストール ３．SQLについて 4．MySQLの管理 　　DBMS 　　DBの比較 　　MySQLについて 2．MySQLインストール 　　インストール 　　MySQL基本コマンド ３．SQLについて 　　SELECT 文,CREATE 文,INSERT 文,UPDATE 文,DELETE 文 4．MySQLの管理 　　GUIツールを用いたDB管理 5．MySQLと連携したWebアプリケーション 　　JDBCドライバーについて 　　Webアプリケーション開発

1．データベースについて

ＤＢＭＳ DBMS (Database Management System) ・データベースを管理し、データに対するアクセス要求に応えるソフトウェア 　(例)　Oracle Database 　　　　 IBM DB2 　　　　 Microsoft SQL Server PostgreSQL(無償) MySQL

DBの比較 ※１MySQL 運用によって無償ライセンス版もあります 　　　Oracle データベースサイズ、CPU数に制限がある無償ライセンス版もあります。 ※2 Oracle 10g R2のみMacOS版があります。

MySQLについて MySQLとは、入手も利用も無料のオープンソースのRDBMS（リレーショナルデータベース管理システム）のことです。 特徴 ・ LinuxなどのUNIX 系システムだけでなく、 Windows 、Mac OSなど多彩なプラットフォームでの動作が確認されています。 ・ 日本語に正式対応しており、大量のデータを高速に検索することができる。 ・MySQLは、頻繁にアップデートが行われています。

2．MySQLインストール

MySQLインストール インストールの流れ １．MySQLインストール ２．MySQL設定 ３．MySQL起動 ４．MySQL初期設定 グループを作成します # groupadd mysql # useradd -g mysql -d /home/mysql mysql # passwd mysql

１． MySQLインストール rootユーザで実行します [root@fedora ~]# yum -y install mysql-server　←　mysql-serverインストール

２． MySQL設定 [root@fedora ~]# vi /etc/my.cnf ← viコマンドで MySQL設定ファイル編集 [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 default-character-set = utf8　←　追加(MySQLサーバーの文字コードをUTF-8にする) 以下を追加(MySQLクライアントの文字コードをUTF-8にする) [mysql] default-character-set = utf8

３． MySQL起動 [root@fedora ~]# /etc/rc.d/init.d/mysqld start ← MySQL起動 ：： [root@fedora ~]# chkconfig mysqld on　←　MySQL自動起動設定

４． MySQL初期設定 [root@fedora ~]# mysql_secure_installation ← MySQL初期設定 ← 空ENTER ← 空ENTER (rootパスワード設定) ← rootパスワード応答 ← rootパスワード応答(確認)

← 空ENTER (匿名ユーザー削除) ← 空ENTER ← 空ENTER(リモートからのrootログイン禁止) ←　空ENTER(testデータベース削除) ←　空ENTER

基本コマンド データベースログイン 既存のデータベースを見る [sugiura@user01 ~]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.1.55 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 既存のデータベースを見る mysql> show databases; +--------------------+ | Database | | information_schema | | mysql | 2 rows in set (0.00 sec)

MySQLデータベース構成確認 statusコマンド mysql> status -------------- mysql Ver 14.14 Distrib 5.1.55, for redhat-linux-gnu (i386) using readline 5.1 Connection id: 13 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.55 Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 3 min 48 sec Threads: 1 Questions: 28 Slow queries: 0 Opens: 16 Flush tables: 1 Open tables: 9 Queries per second avg: 0.122

Query OK, 1 row affected (0.00 sec) mysql> mysql> use lessondb; データベースを作成~ユーザ作成 mysql> create database lessondb; Query OK, 1 row affected (0.00 sec) mysql> mysql> use lessondb; Database changed mysql> grant all on lessonDB.* to lessonuser@localhost identified by 'lesson'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for lessonuser@localhost; +-------------------------------------------------------------------------------------------------------------------+ | Grants for lessonuser@localhost | | GRANT USAGE ON *.* TO 'lessonuser'@'localhost' IDENTIFIED BY PASSWORD '*EB5A41D4AC471BAA097E14C6' | | GRANT ALL PRIVILEGES ON `lessonDB`.* TO 'lessonuser'@'localhost' | 2 rows in set (0.00 sec) mysql> exit Bye 接続確認 mysql -u lessonuser -p ログインできることを確認 ←データベース作成 ←データベース切り替え ↓ユーザ作成 ←ユーザ作成後、確認

←作成したユーザ(lessonuser)でログイン [root@user01 src]# mysql -u lessonuser -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.1.55 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | | information_schema | | lessondb | 2 rows in set (0.00 sec) mysql> use lessondb Database changed mysql> show tables; Empty set (0.00 sec) ←作成したユーザ(lessonuser)でログイン ←作成したユーザ(lessonuser)で利用できるDatabaseの確認 ←lessondbを選択 ←作成しているテーブルを表示

テーブルの構造を見る mysql> show fields from user; +-----------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | (中略) |max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | 39 rows in set (0.00 sec) mysql>

バックアップ データの写しを取って保存すること。コンピュータに保存されたデータやプログラムを、破損やコンピュータウイルス感染などの事態に備え、別の記憶媒体に保存すること。 全てバックアップ mysqldump -u user -x --all-databases > /tmp/mysql.dump -p 特定のデータベースのみのバックアップの場合は mysqldump -u root データベース名 > dump.sql -p リストア 「修復」「復元」という意味の英単語で、破損したシステムやディスク、　データベースなどを復旧することを指す。 「バックアップ」の対義語として用いられることが多い。 全てリストア mysql -u root -p < dump.sql 特定データベースのみの復元は mysql -u lessonuser -p lessonDB < dump.sql

3．SQLについて

SQL ・リレーショナルデータベースマネージメントシステム (RDBMS) において データの操作や定義を行うためのデータベース言語（問い合わせ言語） 種別 コマンド 内容 (データ定義言語 ) DBオブジェクトの定義 DBオブジェクトの削除 DBオブジェクトの定義変更 (データ操作言語) データの挿入 データの更新 データの削除 データの参照、検索 DDL CREATE DROP ALTER DML INSERT UPDATE DELETE SELECT

SELECT 文 1.全列のデータの参照 SELECT * FROM テーブル名; (例) SELECT * FROM 学生表; 2.特定列のデータの参照 SELECT 列名, 列名, ・・・ FROM テーブル名; (例)　SELECT 学籍番号, 氏名, 出身地 FROM 学生表;

SELECT 文(条件指定) 条件指定のデータ参照 SELECT 列名 FROM テーブル名 WHERE 条件; 3.比較演算子 =　　等しい <>　等しくない >　より大きい <　より小さい >=　以上 <=　以下 4.BETWEEN演算子(数値の範囲指定) (例)　SELECT * FROM 学生表 WHERE 年齢 BETWEEN 19 AND 21;

5.IN演算子(複数条件指定) (例)　SELECT * FROM 学生表 WHERE 学部 IN (‘工学部’, ‘文学部’); 6.論理演算子 (例)　SELECT * FROM 学生表 WHERE 性別 = ‘男’ AND 学部 = ‘経済学部’; AND：両方の条件を満たしている(かつ) OR　：いずれかの条件を満たしている(もしくは) 7.LIKE演算子(部分一致指定) (例)　SELECT * FROM 学生表 WHERE 氏名 LIKE ‘%田%’;

ORDER BY 句(並び替え) DISTINCT 句(重複行の削除) 8.並び順の指定 ORDER BY 列名 ASC(or DESC) (例)　SELECT * FROM 学生表 ORDER BY 年齢 DESC; DISTINCT 句(重複行の削除) 9.重複行を表示しない指定 SELECT DISTINCT 列名 FROM テーブル名; (例)　SELECT DISTINCT 学部 FROM 学生表;

集合関数 10.集合関数の利用 SELECT 集合関数(列名) FROM テーブル名; (例)　SELECT SUM(単価) FROM 商品表; 集合関数 用途 SUM() 指定された列の合計を求める AVG() 指定された列の平均を求める MAX() 指定された列の最大値を求める MIN() 指定された列の最小値を求める COUNT() 指定された列の行数を求める

GROUP BY 句(グループ化) HAVING 句(条件指定のグループ化) 11.グループ化の利用 GROUP BY 列名 (例)　SELECT 売上No, SUM(数量) FROM 売上明細表 GROUP BY 売上No; HAVING 句(条件指定のグループ化) 12.条件指定のグループ化の利用 GROUP BY 列名 HAVING 条件; (例)　SELECT 売上No, SUM(数量) FROM 売上明細表 GROUP BY 売上No HAVING SUM(数量) > 40;

テーブルの結合 計算式 13.基本的な結合 SELECT 列名 FROM テーブル名, テーブル名 WHERE 結合条件; 14.計算式の利用 SELECT 列名 演算子 列名 FROM テーブル名; (例)　SELECT 商品表.商品コード, 商品名, 単価, 数量, 単価*数量 FROM 商品表, 売上明細表 WHERE 商品表.商品コード = 売上明細表.商品コード;

名前付け 15.表、列に別名を付ける SELECT 列名 AS 別名 FROM テーブル名 AS 別名; (例)　SELECT S.商品コード, 商品名, 単価, 数量, 単価*数量 AS 売上金額 FROM 商品表 AS S, 売上明細表 AS U WHERE S.商品コード = U.商品コード;

副問合せ(サブクエリー) 16.基本的な副問合せ SELECT 列名 FROM テーブル名 WHERE 列名 = (SELECT 顧客コード FROM 売上表 WHERE 売上No = 1); 17.結合したテーブルでの副問合せ (例)　SELECT 売上No, 単価, 数量, 単価*数量 AS 売上金額 FROM 商品表 AS S, 売上明細表 AS U WHERE S.商品コード = U.商品コード AND 単価 >= (SELECT AVG(単価) FROM 商品表);

NULL値 18.NULL値のデータ参照 SELECT 列名 FROM テーブル名 WHERE 列名 IS NULL;

CREATE 文 19.テーブルの作成 CREATE TABLE テーブル名( 列名 データ型 制約, 列名 データ型 制約) 型 意味 INT 符号付整数 TINYINT 符号なし整数 DOUBLE 浮動小数点数 VARCHAR 文字列 DATE 日付 制約 意味 PRIMARY KEY 主キーの設定 FOREIGN KEY 外部キーの設定 NOT NULL NULL不許可 DEFAULT 初期値設定 TINYINT -128から127 (符号無しの場合0から255) SMALLINT -32768から32767 (符号無しの場合0から65535) MEDIUMINT -8388608から8388607 (符号無しの場合0から16777215) INT -2147483648から2147483647 (符号無しの場合0から4294967295) 別名：INTEGER BIGINT -9223372036854775808から9223372036854775807 (符号無しの場合0から18446744073709551615)

(例)　CREATE TABLE ORGANIZATION( 　　　　 ORGANIZATION_ID VARCHAR(2) PRIMARY KEY, 　　　　 ORGANIZATION_NAME VARCHAR(100) NOT NULL); CREATE TABLE EMPLOYEE( EMPLOYEE_ID INTEGER PRIMARY KEY, EMPLOYEE_NAME VARCHAR(100) NOT NULL, BIRTH_DATE DATE, HOMETOWN VARCHAR(100) DEFAULT '不明', ORGANIZATION_ID VARCHAR(2));

INSERT 文 UPDATE 文 20.データの挿入 INSERT INTO テーブル名(列名, 列名) VALUES (値, 値); (例)　INSERT INTO ORGANIZATION (ORGANIZATION_ID, ORGANIZATION_NAME) VALUES ('01', '総務部'); UPDATE 文 21.データの更新 UPDATE テーブル名 SET 列名 = 値; (例)　UPDATE EMPLOYEE SET HOMETOWN = '佐賀県';

DELETE 文 DROP 文 22.データの削除 DELETE FROM テーブル名 (例)　DELETE FROM EMPLOYEE WHERE EMPLOYEE_ID = 1; (例)　DELETE FROM EMPLOYEE; DROP 文 23.テーブルの削除 DROP TABLE テーブル名; (例)　DROP TABLE EMPLOYEE;

演習 次のテーブルを作成してください。 テーブル名：STUDENT 次のデータを登録してください。 CREATE TABLE STUDENT ( studentId VARCHAR(10), password VARCHAR(10), name VARCHAR(50), tel VARCHAR(15) ); studentIdにインデックスを作成します（インデックス名:idx_studentId) CREATE INDEX idx_studentId ON STUDENT (studentId); テーブルを作成し、データを登録した後、バックアップおよびリストアを行ってください。

insert into STUDENT (studentId,password,name,tel ) values( 'A1001','1001P','大分 太郎','097-111-2222' ); 'A1002','1002P','別府 花子','0975-33-4444' 'A1003','1003P','佐伯 タツオ','0972-55-6666'

4．MySQLの管理

GUIツールを用いたDB管理 Webmin インストールの流れ １．ソフトウェアの取得 ２．rpmコマンドでインストール ３．初期設定 　　１．ソフトウェアの取得 　　２．rpmコマンドでインストール 　　３．初期設定 　　４．GUIツールを用いたMySQL動作確認

１．ソフトウェアの取得 Webサイトより取得します。 http://www.webmin.com/index.html

個人フォルダ/ダウンロードフォルダ配下に コピーされます

２．rpmコマンドでインストール rootユーザに変わります su - [rootユーザのパスワード] rpmコマンドでインストールします 　　rpm – ihv webmin-1.530-1.noarch.rpm

３．初期設定 ブラウザで操作していきます http://localhost:10000にアクセスします Rootユーザへ変更するときと同じで　usernameにroot　passwordにrootのパスワードを入力します。　その後Loginを押します

Webmin Configurationをクリックします

Languageをクリックします

Japanese(JA_JP.UTF-8)を選択しChange Languageをクリックします

４． GUIツールを用いたMySQL動作確認 MySQL Database Serverをクリックします(ブラウザを更新すると日本語になります)

MySQL サーバを起動をクリックします。 この後、管理ログイン画面がある場合があります。 ある場合はログインroot　パスワードは設定したパスワードを入力してください

データベースとテーブルを作成します

データベース名 test テーブル名 Student カラム 主キー Student_No 数(８) Name 文字(40) 以下の設定で作成します データベース名　test テーブル名　Student　 カラム　　主キー　　　Student_No 　　　数(８) 　　　　　　　　　　　　　 Name　　　　　　　　文字(40)

このように作成されます

5．MySQLと連携したWebアプリケーション

JDBCドライバについて Javaとデータベースを接続させるAPI Linux（OS) Apache HTTP Server Tomcat Java VM JDBCドライバ MySQL STUDENT テーブル ※JDBCドライバにはType1～Type4 まで４種類カテゴライズされています。

構築するWebアプリケーションの概要