Transcript mysql

第6回 MySQL設定
知的システム構築工房
Linux設定セミナーシリーズ
第6回 MySQL 設 定
2011年3月17日（木）
株式会社 アセンディア
－1 －
第6回 MySQL設定
目的
当セミナーを通して、
Linuxにデータベース環境を構築し、データベース
と連携したWebアプリ開発を体得する
－2 －
第6回 MySQL設定
目標
当セミナーを受講することにより
１．データベースについて説明できるようになること
２．データベースをインストールできるようになること
３．データベースを管理できるようになること
４．データベースと連携したWebアプリを開発できるよう
になること
－3 －
第6回 MySQL設定
セミナー内容
1．データベースについて
DBMS
DBの比較
MySQLについて
2．MySQLインストール
インストール
MySQL基本コマンド
３．SQLについて
SELECT 文,CREATE 文,INSERT 文,UPDATE 文,DELETE 文
4．MySQLの管理
GUIツールを用いたDB管理
5．MySQLと連携したWebアプリケーション
JDBCドライバーについて
Webアプリケーション開発
－4 －
第6回 MySQL設定
1．データベースについて
－5 －
第6回 MySQL設定
ＤＢＭＳ
DBMS (Database Management System)
・データベースを管理し、データに対するアクセス要求に応えるソフトウェア
(例) Oracle Database
IBM DB2
Microsoft SQL Server
PostgreSQL(無償)
MySQL
－6 －
第6回 MySQL設定
DBの比較
MySQL
Oracle
PostgreSQL
ライセンス
有償(※1)
有償(※1)
無償
Windows,Linux,その他Unix,MacOSで動作(※2)
OS
データベースサイズ ほぼ無制限
無制限
無制限
最大行数
無制限
無制限
無制限
最大カラム数
4096
1000 カラムの型によって 250～1600
トランザクション処理
○
○
○
行ロック
○
○
○
読み取り一貫性
○
○
○
ストアドプロシージャ
△
○
○
パーティショニング
○
○（有償オプション）
○
※１MySQL 運用によって無償ライセンス版もあります
Oracle データベースサイズ、CPU数に制限がある無償ライセンス版もあります。
※2 Oracle 10g R2のみMacOS版があります。
－7 －
第6回 MySQL設定
MySQLについて
MySQLとは、入手も利用も無料のオープンソースのRDBMS（リレーショナルデ
ータベース管理システム）のことです。
特徴
・ LinuxなどのUNIX 系システムだけでなく、 Windows 、Mac OSなど多彩なプ
ラットフォームでの動作が確認されています。
・ 日本語に正式対応しており、大量のデータを高速に検索することができる。
・MySQLは、頻繁にアップデートが行われています。
－8 －
第6回 MySQL設定
2 ．MySQL インス トール
－9 －
第6回 MySQL設定
MySQLインストール
インストールの流れ
１．MySQLインストール
２．MySQL設定
３．MySQL起動
４．MySQL初期設定
グループを作成します
# groupadd mysql
# useradd -g mysql -d /home/mysql mysql
# passwd mysql
－10 －
第6回 MySQL設定
１． MySQLインストール
rootユーザで実行します
[root@fedora ~]# yum -y install mysql-server ← mysql-serverインストール
－11 －
第6回 MySQL設定
２． 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
－12 －
第6回 MySQL設定
３． MySQL起動
[root@fedora ~]# /etc/rc.d/init.d/mysqld start ← MySQL起動
：
：
[root@fedora ~]# chkconfig mysqld on ← MySQL自動起動設定
－13 －
第6回 MySQL設定
４． MySQL初期設定
[root@fedora ~]# mysql_secure_installation ← MySQL初期設定
← 空ENTER
← 空ENTER (rootパスワード設定)
← rootパスワード応答
← rootパスワード応答(確認)
－14 －
第6回 MySQL設定
←
空ENTER (匿名ユーザー削除)
← 空ENTER(リモートからのrootログイン禁止)
← 空ENTER(testデータベース削除)
←
空ENTER
－15 －
第6回 MySQL設定
基本コマンド
データベースログイン
[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)
－16 －
第6回 MySQL設定
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
-------------－17 －
第6回 MySQL設定
データベースを作成~ユーザ作成
mysql> create database lessondb; ←データベース作成
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> use lessondb; ←データベース切り替え
Database changed
↓ユーザ作成
mysql>
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
ログインできることを確認
－18 －
第6回 MySQL設定
[root@user01 src]# mysql -u lessonuser -p←作成したユーザ(lessonuser)でログイン
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)
←作成したユーザ(lessonuser)で利用で
きるDatabaseの確認
mysql> use lessondb←lessondbを選択
Database changed
mysql> show tables; ←作成しているテーブルを表示
Empty set (0.00 sec)
－19 －
第6回 MySQL設定
テーブルの構造を見る
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>
－20 －
第6回 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
－21 －
第6回 MySQL設定
3．SQLについて
－22 －
第6回 MySQL設定
SQL
・リレーショナルデータベースマネージメントシステム (RDBMS) において
データの操作や定義を行うためのデータベース言語（問い合わせ言語）
種別
DDL
(データ定義言語 )
DML
(データ操作言語)
コマンド
内容
CREATE
DBオブジェクトの定義
DROP
DBオブジェクトの削除
ALTER
DBオブジェクトの定義変更
INSERT
データの挿入
UPDATE
データの更新
DELETE
データの削除
SELECT
データの参照、検索
－23 －
第6回 MySQL設定
SELECT 文
1.全列のデータの参照
SELECT * FROM テーブル名;
(例) SELECT * FROM 学生表;
2.特定列のデータの参照
SELECT 列名, 列名, ・・・ FROM テーブル名;
(例) SELECT 学籍番号, 氏名, 出身地 FROM 学生表;
－24 －
第6回 MySQL設定
SELECT 文(条件指定)
条件指定のデータ参照
SELECT 列名 FROM テーブル名 WHERE 条件;
3.比較演算子
(例) SELECT * FROM 学生表 WHERE 出身地 = ‘福岡県’;
等しい
> より大きい
>= 以上
<> 等しくない
< より小さい
<= 以下
=
4.BETWEEN演算子(数値の範囲指定)
(例) SELECT * FROM 学生表
WHERE 年齢 BETWEEN 19 AND 21;
－25 －
第6回 MySQL設定
5.IN演算子(複数条件指定)
(例) SELECT * FROM 学生表
WHERE 学部 IN (‘工学部’, ‘文学部’);
6.論理演算子
(例) SELECT * FROM 学生表
WHERE 性別 = ‘男’ AND 学部 = ‘経済学部’;
AND：両方の条件を満たしている(かつ)
OR ：いずれかの条件を満たしている(もしくは)
7.LIKE演算子(部分一致指定)
(例) SELECT * FROM 学生表 WHERE 氏名 LIKE ‘%田%’;
－26 －
第6回 MySQL設定
ORDER BY 句(並び替え)
8.並び順の指定
ORDER BY 列名 ASC(or DESC)
(例) SELECT * FROM 学生表 ORDER BY 年齢 DESC;
DISTINCT 句(重複行の削除)
9.重複行を表示しない指定
SELECT DISTINCT 列名 FROM テーブル名;
(例) SELECT DISTINCT 学部 FROM 学生表;
－27 －
第6回 MySQL設定
集合関数
10.集合関数の利用
SELECT 集合関数(列名) FROM テーブル名;
(例) SELECT SUM(単価) FROM 商品表;
集合関数
用途
SUM()
指定された列の合計を求める
AVG()
指定された列の平均を求める
MAX()
指定された列の最大値を求める
MIN()
指定された列の最小値を求める
COUNT()
指定された列の行数を求める
－28 －
第6回 MySQL設定
GROUP BY 句(グループ化)
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;
－29 －
第6回 MySQL設定
テーブルの結合
13.基本的な結合
SELECT 列名 FROM テーブル名, テーブル名 WHERE 結合条件;
(例) SELECT * FROM 売上表, 顧客表
WHERE 売上表.顧客コード = 顧客表.顧客コード;
計算式
14.計算式の利用
SELECT 列名 演算子 列名 FROM テーブル名;
(例) SELECT 商品表.商品コード, 商品名, 単価, 数量, 単価*数量
FROM 商品表, 売上明細表
WHERE 商品表.商品コード = 売上明細表.商品コード;
－30 －
第6回 MySQL設定
名前付け
15.表、列に別名を付ける
SELECT 列名 AS 別名 FROM テーブル名 AS 別名;
(例) SELECT S.商品コード, 商品名, 単価, 数量,
単価*数量 AS 売上金額
FROM 商品表 AS S, 売上明細表 AS U
WHERE S.商品コード = U.商品コード;
－31 －
第6回 MySQL設定
副問合せ(サブクエリー)
16.基本的な副問合せ
SELECT 列名 FROM テーブル名 WHERE 列名 =
(SELECT 列名 FROM テーブル名 WHERE 条件);
(例) SELECT * FROM 顧客表 WHERE 顧客コード =
(SELECT 顧客コード FROM 売上表 WHERE 売上No = 1);
17.結合したテーブルでの副問合せ
(例) SELECT 売上No, 単価, 数量, 単価*数量 AS 売上金額
FROM 商品表 AS S, 売上明細表 AS U
WHERE S.商品コード = U.商品コード
AND 単価 >= (SELECT AVG(単価) FROM 商品表);
－32 －
第6回 MySQL設定
NULL値
18.NULL値のデータ参照
SELECT 列名 FROM テーブル名 WHERE 列名 IS NULL;
(例) SELECT * FROM 売上表 WHERE 顧客コード IS NULL;
－33 －
第6回 MySQL設定
CREATE 文
19.テーブルの作成
CREATE TABLE テーブル名(
列名 データ型 制約,
列名 データ型 制約)
意味
型
制約
意味
INT
符号付整数
TINYINT
符号なし整数
PRIMARY KEY 主キーの設定
FOREIGN KEY 外部キーの設定
DOUBLE
浮動小数点数
NOT NULL
VARCHAR
文字列
DEFAULT
DATE
日付
－34 －
NULL不許可
初期値設定
第6回 MySQL設定
(例) 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));
－35 －
第6回 MySQL設定
INSERT 文
20.データの挿入
INSERT INTO テーブル名(列名, 列名) VALUES (値, 値);
(例) INSERT INTO ORGANIZATION
(ORGANIZATION_ID, ORGANIZATION_NAME)
VALUES ('01', '総務部');
UPDATE 文
21.データの更新
UPDATE テーブル名 SET 列名 = 値;
(例) UPDATE EMPLOYEE SET HOMETOWN = '佐賀県';
－36 －
第6回 MySQL設定
DELETE 文
22.データの削除
DELETE FROM テーブル名
(例) DELETE FROM EMPLOYEE WHERE EMPLOYEE_ID = 1;
(例) DELETE FROM EMPLOYEE;
DROP 文
23.テーブルの削除
DROP TABLE テーブル名;
(例) DROP TABLE EMPLOYEE;
－37 －
第6回 MySQL設定
演習
次のテーブルを作成してください。
テーブル名：STUDENT
項目名
属性
studentId
VARCHAR(10)
password
VARCHAR(10)
name
VARCHAR(50)
tel
VARCHAR(15)
次のデータを登録してください。
主キー
○
studentId
A1001
A1002
A1003
password
name
tel
1001P
大分　太郎 097-111-2222
1002P
別府　花子 0975-33-4444
1003P
佐伯　タツオ0972-55-6666
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);
テーブルを作成し、データを登録した後、バックアップおよびリストアを行ってください。
－38 －
第6回 MySQL設定
insert into STUDENT
(studentId,password,name,tel
) values(
'A1001','1001P','大分 太郎','097-111-2222'
);
insert into STUDENT
(studentId,password,name,tel
) values(
'A1002','1002P','別府 花子','0975-33-4444'
);
insert into STUDENT
(studentId,password,name,tel
) values(
'A1003','1003P','佐伯 タツオ','0972-55-6666'
);
－39 －
第6回 MySQL設定
4．MySQLの管理
－40 －
第6回 MySQL設定
GUIツールを用いたDB管理
Webmin
インストールの流れ
１．ソフトウェアの取得
２．rpmコマンドでインストール
３．初期設定
４．GUIツールを用いたMySQL動作確認
－41 －
第6回 MySQL設定
１．ソフトウェアの取得
Webサイトより取得します。 http://www.webmin.com/index.html
－42 －
第6回 MySQL設定
個人フォルダ/ダウンロードフォルダ配下に
コピーされます
－43 －
第6回 MySQL設定
２．rpmコマンドでインストール
rootユーザに変わります
su - [rootユーザのパスワード]
rpmコマンドでインストールします
rpm – ihv webmin-1.530-1.noarch.rpm
－44 －
第6回 MySQL設定
３．初期設定
ブラウザで操作していきます
http://localhost:10000にアクセスします
Rootユーザへ変更するときと同じで usernameにroot passwordにrootのパス
ワードを入力します。 その後Loginを押します
－45 －
第6回 MySQL設定
Webmin Configurationをクリックします
－46 －
第6回 MySQL設定
Languageをクリックします
－47 －
第6回 MySQL設定
Japanese(JA_JP.UTF-8)を選択しChange Languageをクリックします
－48 －
第6回 MySQL設定
４． GUIツールを用いたMySQL動作確認
MySQL Database Serverをクリックします(ブラウザを更新すると日本語になります)
－49 －
第6回 MySQL設定
MySQL サーバを起動をクリックします。
この後、管理ログイン画面がある場合があります。
ある場合はログインroot パスワードは設定したパスワードを入力してください
－50 －
第6回 MySQL設定
データベースとテーブルを作成します
－51 －
第6回 MySQL設定
以下の設定で作成します
データベース名 test
テーブル名 Student
カラム
主キー
Student_No
Name
数(８)
文字(40)
－52 －
第6回 MySQL設定
－53 －
第6回 MySQL設定
このように作成されます
－54 －
第6回 MySQL設定
5．MySQLと連携したWebアプリケーション
－55 －
第6回 MySQL設定
JDBCドライバについて
Javaとデータベースを接続させるAPI
Linux（OS)
Apache HTTP Server
Tomcat
Java VM
JDBCドライバ
MySQL
STUDENT
テーブル
※JDBCドライバにはType1～Type4
まで４種類カテゴライズされています。
－56 －
第6回 MySQL設定
構築するWebアプリケーションの概要
－57 －