MySQL入門(Linux版)メモ


Apr2014 coskx

１．はじめに

この文書は，データベース利用環境の１つであるLinux版MySQL()学習の最初のチュートリアルである。
MySQLではすべての権限をを持つユーザ「root」があり，インストール時に「root」に対するパスワードが設定されている。
通常，「root」はMySQLの設定を行い，個々のデータベースでの作業は通常のユーザが行っている。
 そのため，このチュートリアルではrootがテストユーザを設定し，テストユーザとしてデータベースの作業することを想定している。。

ここではMySQLがインストールされた直後を想定し，
管理者ID        ： root
管理者パスワード： rootpwd　　（学習環境ではパスワードは設定されていないことも有る）
 が設定されているものとしてチュートリアルを開始することとする。

また「sudo service mysql start」で，MySQLは稼働しているものとする。
（このコマンド投入時にもパスワードを求められるが，求められるパスワードはLinuxユーザのパスワードである。）
(sudo service mysql stopでMySQL動作終了)
xxxxx@yyyyyy:~$ sudo service mysql start


２．管理者による最初のログイン

２．１　起動
コマンドプロンプトを立ち上げ，その中で「sudo mysql -u root -p」を記述して管理者としてログインする。
（学習環境でrootのパスワードが設定されていない場合は「sudo mysql -u root」）
xxxxx@yyyyyy:~$ sudo mysql -u root -p
Enter password: ********　←ここにパスワードrootpwdを記述
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>


Welcome to the MySQL monitor.　・・・
が表示されたらログイン成功でプロンプトが「mysql>」になる。

２．２　すぐに終了
終了するときは

quit;
または
exit;

mysql>quit;　←コマンドの最後は必ず「;」をつける
bye

コマンド行末の「;」を忘れた場合には「->」が表示されるので，「;」を入力する。

３．テスト環境の構築

まず最初に管理者としてログインし，管理者として作業する。
テストユーザを登録して，テストユーザが作業できるようにする。
xxxxx@yyyyyy:~$ sudo mysql -u root -p
Enter password: ********

MySQLでは，複数のデータベースを作ることができ，１つのデータベース中に複数のテーブルをもつことができる。
テーブルとはExcelの１つのシートのイメージでデータを保存する単位である。

MySQL
  |--データベース１
  |        |--テーブル１
  |        |--テーブル２
  |        |      ：
  |        |--テーブルN
  |
  |--データベース２
  |        |--テーブル１
  |        |--テーブル２
  |        |      ：
  |        |--テーブルN
  |
  |--データベース３
　：


３．１　データベース一覧

どのようなデータベースを持っているのか表示してみる。
（root用のデータベースを表示する）

データベース一覧表示コマンド　show databases;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
6 rows in set (0.01 sec)

すでにいくつかのデータベースが作られていることがわかる。
データベースに関する作業はこれ以上行わない。
３．２　テストユーザ登録

実際のデータベースに関する作業をrootが行うことはない。rootの仕事はMySQLの管理である。
そこでテストユーザを設定し，この後はテストユーザが操作できるようにする。

テストユーザなどのユーザに関しては，rootが
　「ユーザID」
　「ユーザのパスワード」
　「ユーザ使用予定のデータベース名」
を登録する。

 登録されたユーザは，rootが許したデータベース名のみ使用できる。
設定するテストユーザ
ユーザID　　　　　　　　　　　　　　　testuser
パスワード　　　　　　　　　　　　　　testuserpwd
テストユーザが使用するデータベース名　testuserdb

rootがログインしている状態で以下の作業を行う。
mysql> create user 'testuser'@'localhost' identified by 'testuser';
Query OK, 0 rows affected (0.14 sec)

mysql> grant all on testuserdb.* to 'testuser'@'localhost';
Query OK, 0 rows affected (0.06 sec)

mysql> set password for 'testuser'@'localhost' = password('testuserpwd');
Query OK, 0 rows affected (0.00 sec)

quit;でrootは作業を終了する。
testuserが登録されたので，この後はtestuserとしてログインしてから作業する。

４．テストユーザでログインして操作


４．１　テストユーザでログイン

コマンドプロンプトを立ち上げ，その中で「mysql -u testuser -p」を記述してテストユーザとしてログインする。

xxxxx@yyyyyy:~$ mysql -u testuser -p
Enter password: ********　←ここにパスワードtestuserpwdを記述
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>


４．２　データベースの作成と削除

テスト用データベースtestuserdbを作成し，できていることを確認した後，すぐに削除する。

データベース一覧表示コマンド　show databases;
 データベース作成コマンド　    create database データベース名;
データベース削除コマンド　    drop database データベース名;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

mysql> create database testuserdb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| testuserdb         |
+--------------------+
2 rows in set (0.01 sec)

mysql> drop database testuserdb;
Query OK, 0 rows affected (0.22 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

データベース一覧表示コマンドを実行すると，最初から1つだけデータベースができており，
そこに新しいデータベースtestuserdbを加えていることがわかる。
testuserdbの名前は3.2でrootが使用を許可したデータベース名である。

データベースは自由に作成できるのではなく，予めrootが登録した名前のデータベースのみ作成できる。

この作業ではつぎのようなデータベースを作ったことになる。
MySQL
  |--testuserdb
  |



４．３　データベース中でのテーブルの作成と削除
もう一度データベースtestuserdbを作り，その中にテーブルmygoodlistを作る。
何もせず，テーブルmygoodlistを削除し，データベースtestuserdbを削除する。

データベースの作業対象宣言コマンド　use データベース名;
テーブル作成コマンド　　　　　　　　create table テーブル名(列名 型, 列名 型, 列名 型, ・・・);
テーブル削除コマンド　　　　　　　　drop table テーブル名;
mysql> create database testuserdb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| testuserdb         |
+--------------------+
2 rows in set (0.01 sec)

mysql> use testuserdb;
Database changed

mysql> create table mygoodlist(ID int, name varchar(20), price int);
Query OK, 0 rows affected (0.27 sec)

mysql> show tables;
+----------------------+
| Tables_in_testuserdb |
+----------------------+
| mygoodlist           |
+----------------------+
1 row in set (0.00 sec)

mysql> drop table mygoodlist;
Query OK, 0 rows affected (0.12 sec)

mysql> drop database testuserdb;
Query OK, 0 rows affected (0.22 sec)

この作業ではつぎのようなデータベースとテーブルを作ったことになる。

テーブル名は，rootが予め登録する必要が無いので，ユーザが自由に作成できる。
MySQL
  |--testuserdb
  |        |--mygoodlist
  |

このテーブルmygoodlistにはまだデータは入っていないが次のような表となっている。
ID （整数の値）
 name (文字列)
 price (整数の値)








４．４　テーブルデータの作成

ここまでの作業で，データベースとその中のテーブルの作り方，削除の仕方がわかった。

もう一度最初から，データベースとテーブルを作成してから，テーブル内にデータを入力する。

データ挿入コマンド　insert into テーブル名 values (列１のデータ, 列2のデータ, 列3のデータ, ・・・);
（insertコマンドで日本語文字がうまく入力できずにエラーになってしまう場合はとりあえずローマ字で）

データベース抽出表示コマンド select
select * from mygoodlist　で「*」はすべての意味で，mygoodlistからすべてを選択するという意味である。
mysql> create database testuserdb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| testuserdb         |
+--------------------+
2 rows in set (0.01 sec)

mysql> use testuserdb;
Database changed

mysql> create table mygoodlist(ID int, name varchar(20), price int);
Query OK, 0 rows affected (0.27 sec)

mysql> show tables;
+----------------------+
| Tables_in_testuserdb |
+----------------------+
| mygoodlist           |
+----------------------+
1 row in set (0.00 sec)

mysql> insert into mygoodlist values (1, "みかん", 100);
Query OK, 1 row affected (0.06 sec)

mysql> insert into mygoodlist values (2, "りんご", 150);
Query OK, 1 row affected (0.06 sec)

mysql> insert into mygoodlist values (3, "りんご青森", 200);
Query OK, 1 row affected (0.06 sec)

mysql> select * from mygoodlist;
+------+------------+-------+
| ID   | name       | price |
+------+------------+-------+
|    1 | みかん     |   100 |
|    2 | りんご     |   150 |
|    3 | りんご青森 |   200 |
+------+------------+-------+
3 rows in set (0.05 sec)

このテーブルmygoodlistにはつぎのようにデータが入っている。
ID （整数の値）
 name (文字列)
 price (整数の値)
1
 みかん
 100
2
 りんご
 150
3
 りんご青森
 200

ここで一旦ログアウトする。
mysql> quit;
Bye

データベース，テーブル，データは残ったままになる。


５．テストユーザのデータベースの操作

もう一度testuserでログインしてデータベースを操作してみる。
抽出表示selectコマンドにおいて「*」はすべてを意味し，「%」は任意の文字列を表わす。
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| testuserdb         |
+--------------------+
2 rows in set (0.01 sec)

mysql> use testuserdb
Database changed

mysql> show tables;
+----------------------+
| Tables_in_testuserdb |
+----------------------+
| mygoodlist           |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from mygoodlist;
+------+------------+-------+
| ID   | name       | price |
+------+------------+-------+
|    1 | みかん     |   100 |
|    2 | りんご     |   150 |
|    3 | りんご青森 |   200 |
+------+------------+-------+
3 rows in set (0.05 sec)

mysql> select name from mygoodlist;
+------------+
| name       |
+------------+
| みかん     |
| りんご     |
| りんご青森 |
+------------+

mysql> select name, price from mygoodlist;
+------------+-------+
| name       | price |
+------------+-------+
| みかん     |   100 |
| りんご     |   150 |
| りんご青森 |   200 |
+------------+-------+

mysql> select name, price from mygoodlist where name="みかん";
+--------+-------+
| name   | price |
+--------+-------+
| みかん |   100 |
+--------+-------+

mysql> select name, price from mygoodlist where price>110;
+------------+-------+
| name       | price |
+------------+-------+
| りんご     |   150 |
| りんご青森 |   200 |
+------------+-------+

mysql> select name, price from mygoodlist where name like "りんご%";
+------------+-------+
| name       | price |
+------------+-------+
| りんご     |   150 |
| りんご青森 |   200 |
+------------+-------+
2 rows in set (0.04 sec)

データ表示補足
ここまでは，useコマンドで使用するデータベースを指定して，その中のテーブルを次のように表示していた。
> select * from mygoodlist;
useコマンドが実行されていない状態では
> select * from testuserdb.mygoodlist;
のようにデータベース名を含めてコマンドを与える。

４．３～４．４と同じ方法で新しいテーブルをtestuerdb中に作ることができる。

データの更新は次のように行なうことができる。
> update mygoodlist set price=170 where name="りんご";

データの削除は次のように行なうことができる。
> delete from mygoodlist where name='りんご';
データ削除（先頭から２件）
> delete from mygoodlist limit 2 ;
テーブルの全データ削除
> delete from mygoodlist;

コマンドを羅列したファイルをカレントフォルダに作成しておくと，
> source ファイル名
で一連のコマンドを実行できる。

６．PHPからのデータベースアクセス
6.1 準備
php.iniを変更
次の２行をさがして有効にする。（mysqliに関しては当面使わない）
extension=php_mysql.dll
extension=php_mysqli.dll

次の４行を探して，右辺をセットする
mysql.default_port =3306
mysql.default_host =localhost         ←これはなくて良い(PHPプログラム中で与えるので)
mysql.default_user =testuser          ←これはなくて良い(PHPプログラム中で与えるので)
mysql.default_password =testuserpwd   ←これはなくて良い(PHPプログラム中で与えるので)

Apacheを再起動する


6.2 アクセス

データベースへのSQLコマンドは，直接MySQLを操作するのと同じである。
MySQLは標準で文字コードがUTF-8なので，PHPもUTF-8で作成する。
PHPの記述の中で関数mysql_query()で送っているのがSQLコマンド（クエリ）である。

前提
ユーザID　testuser
パスワード testuserpwd
使用データベース　testuserdb
使用テーブル　mygoodlist
使用テーブルのカラム　ID, name, price　（ID，品物名，価格）

下記phpプログラム内で
$link = mysqli_connect('localhost', 'testuser', 'testuserpwd');
で，サーバlocalhostにユーザtestuserがパスワードtestuserpwdで接続しています。

次の例は，とにかくテーブルmygoodlistにアクセスして，すべてを表示する例である。
MySQLテーブルmygoodlist内容一覧表示　mysqltest.php
<?php

//****** 関数領域 ここから *****
function quote_smart($value)
{
    global $link;
    // 数値以外をクオートする
    if (!is_numeric($value)) {
        $value = "'" . mysqli_real_escape_string($link, $value) . "'";
    }
    return $value;
}

function getWholeTable()
{
    global $link;
    $resulttxt="";
    $result = mysqli_query($link, 'select * from mygoodlist');
    if (!$result) {
        die('クエリーが失敗しました。'.mysqli_error($link));
    }
    $resulttxt.='<p>';
    while ($row = mysqli_fetch_assoc($result)) {
        $resulttxt.='id='.$row['ID'].", ";
        $resulttxt.='name='.quote_smart($row['name']).", ";
        $resulttxt.='price='.$row['price']."<br>";
    }
    $resulttxt.='</p>';
    return $resulttxt;
}
//****** 関数領域 ここまで *****

//****** 制御領域 ここから *****
$message="";
$resulttxt="";

$link = mysqli_connect('localhost', 'testuser', 'testuserpwd');
if (!$link) {
    die('MySQL への接続を失敗しました。'.mysqli_error($link));
}
$message.="MySQL への接続に成功しました。<br>";

$db_selected = mysqli_select_db($link, 'testuserdb');
if (!$db_selected){
    die('データベース testuserdb でコマンド select が実行できませんでした。' . mysqli_error($link));
}
$message.="データベース testuserdb でコマンド select が実行できました。<br>";

$resulttxt=getWholeTable();

$close_flag=mysqli_close($link);
if (!$close_flag){
    die('MySQL のクローズに失敗しました。' . mysqli_error($link));
}
$message.="MySQL のクローズにに成功しました。<br><br>";

//****** 制御領域 ここまで *****
?>

<?php
//****** 表示領域 ここから *****
?>

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><META http-equiv="Content-Type" content="text/html;charset=UTF-8">
<title>PHP MYSQL TEST</title>
</HEAD>
<BODY>
<H2>PHP MYSQLI TEST</H2>
<?php print $message; ?>
<?php print $resulttxt; ?>
</BODY>
</html>

<?php
//****** 表示領域 ここまで *****
?>
実行時の画面
PHP MYSQL TESTPHP MYSQL TEST

PHP MYSQL TEST

MySQL への接続に成功しました。
データベース testuserdb でコマンド select が実行できました。
MySQL のクローズにに成功しました。

id=1, name='みかん', price=100
id=2, name='りんご', price=150
id=3, name='りんご青森', price=200


次の例は，対話型でテーブルmygoodlistにアクセスする例である。

MySQL操作　mysqltest2.php
<?php

//****** 関数領域 ここから *****
function quote_smart($value)
{    global $link;
   // 数値以外をクオートする
    if (!is_numeric($value)) {
        $value = "'" . mysqli_real_escape_string($link, $value) . "'";
    }
    return $value;
}

function getWholeTable()
{
    global $link;
    $resulttxt="";
    $result = mysqli_query($link, 'select * from mygoodlist');
    if (!$result) {
        die('クエリーが失敗しました。'.mysqli_error($link));
    }
    $resulttxt.='<p>';
    while ($row = mysqli_fetch_assoc($result)) {
        $resulttxt.='id='.$row['ID'].", ";
        $resulttxt.='name='.quote_smart($row['name']).", ";
        $resulttxt.='price='.$row['price']."<br>";
    }
    $resulttxt.='</p>';
    return $resulttxt;
}
//****** 関数領域 ここまで *****

//****** 制御領域 ここから *****
$message="";
$resulttxt="";

$link = mysqli_connect('localhost', 'testuser', 'testuserpwd');
if (!$link) {
    die('MySQL への接続を失敗しました。'.mysqli_error($link));
}
$message.="MySQL への接続に成功しました。<br>";

$db_selected = mysqli_select_db($link, 'testuserdb');
if (!$db_selected){
    die('データベース testuserdb でコマンド select が実行できませんでした。' . mysqli_error($link));
}
$message.="データベース testuserdb でコマンド select が実行できました。<br>";

if (isset($_POST['selectall'])) { //全表示
    $resulttxt=getWholeTable();
} else if (isset($_POST['insert'])) { //データ挿入
    $sql = "insert into mygoodlist values (".$_POST['id'].",\"".$_POST['name']."\",".$_POST['price'].")";
    $result_flag = mysqli_query($link, $sql);
    if (!$result_flag) {
        die('クエリー insert が失敗しました。'.mysqli_error($link));
    }
    $resulttxt=getWholeTable();
} else if (isset($_POST['delete'])) { //データ削除
    $sql = "delete from mygoodlist where ID=".$_POST['id'];
    $result_flag = mysqli_query($link,$sql);
    $result = mysqli_query($link,'SELECT * FROM mygoodlist');
    if (!$result) {
        die('クエリー delete が失敗しました。'.mysqli_error($link));
    }
    $resulttxt=getWholeTable();
}

$close_flag=mysqli_close($link);
if (!$close_flag){
    die('MySQL のクローズに失敗しました。' . mysqli_error($link));
}
$message.="MySQL のクローズにに成功しました。<br><br>";

//****** 制御領域 ここまで *****
?>

<?php
//****** 表示領域 ここから *****
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><META http-equiv="Content-Type" content="text/html;charset=UTF-8">
<title>PHP MYSQL TEST</title>
</HEAD>
<BODY>
<H2>PHP MYSQLI TEST</H2>
<?php print $message; ?>
<?php print $resulttxt; ?>

<form method="post">
テーブルの内容を全表示します。<br>
<input type="submit" name="selectall" value="全表示"><br><br>
</form>
<form method="post">
テーブルに新規データを追加します。<br>
ID <input type="text" name="id">(半角数字のみ)<br>
name <input type="text" name="name"><br>
price <input type="text" name="price">(半角数字のみ)<br>
<input type="submit" name="insert" value="新規データ挿入"><br><br>
</form>
<form method="post">
テーブルからIDを指定してデータを削除します。<br>
ID <input type="text" name="id">(半角数字のみ)<br>
<input type="submit" name="delete" value="データ削除"><br><br>
</form>

</BODY>
</html>

<?php
//****** 表示領域 ここまで *****
?>
実行時の画面
PHP MYSQL TEST

PHP MYSQL TEST

MySQL への接続に成功しました。
データベース testuserdb でコマンド select が実行できました。
MySQL のクローズにに成功しました。

id=1, name='みかん', price=100
id=2, name='りんご', price=150
id=3, name='りんご青森', price=200
id=4, name='愛媛みかん', price=80
id=5, name='バナナ', price=160

テーブルの内容を全表示します。


 テーブルに新規データを追加します。
ID (半角数字のみ)
name
price (半角数字のみ)


 テーブルからIDを指定してデータを削除します。
ID (半角数字のみ)