ソフトウェアエンジニアの技術ブログ：Software engineer tech blog – Page 681 – 随机应变 ABCD: Always Be Coding and … : хороший

PDOで条件付き検索

<?php

define('DB_DATABASE', 'name_db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'xxxx');
define('PDO_DSN', 'mysql:dbhost=localhost;dbname=' . DB_DATABASE);

try {
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // $stmt = $db->prepare("select score from users where score > ?");
  // $stmt->execute([60]);

  //$stmt = $db->prepare("select name from users where name like ?");
  //$stmt->execute(['%t%']);

  $stmt = $db->prepare("select score from users order by score desc limit ?");
  $stmt->bindValue(1, 1, PDO::PARAM_INT);
  $stmt->execute();

  $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
  foreach($users as $user){
    var_dump($user);
  }
  echo $stmt->rowCount() . "records found.";

  /*
  (1) exec(): 結果を返さない、安全なsql
  (2) query(): 結果を返す、安全、何回も実行されない
  (3) prepare(): 結果を返す、安全対策が必要、複数回実行
  */

  // $stmt = $db->prepare("insert into users(name, score) values(?, ?)");
  // $stmt->execute(['tagu', 44]);
  // $stmt = $db->prepare("insert into users(name, score) values(:name, :score)");
  // $stmt->execute(['name'=>'nishikawa', ':score'=>76]);
  // echo "inserted: " . $db->lastInsertId();

  // bindValue 値をbind
  // bindParam 変数への参照をbindValue

  // $stmt = $db->prepare("insert into users(name, score) values(?, ?)");

  // $name = 'tagu';
  // $stmt->bindValue(1, $name, PDO::PARAM_STR);
  // $score= 23;
  // $stmt->bindValue(2, $score, PDO::PARAM_INT);
  // $stmt->execute();
  // $score= 44;
  // $stmt->bindValue(2, $score, PDO::PARAM_INT);
  // $stmt->execute();
  //$stmt->bindParam(2, $score, PDO::PARAM_INT);
  // $score = 52;
  // $stmt->execute();
  // $score = 44;
  // $stmt->execute();
  // $score = 2;
  // $stmt->execute();

} catch (PDOException $e){
  echo $e->getMessage();
  exit;
}

PODとはphp data obujectの略で、データベース接続クラスのことです。 PHPは標準でMySQLやPostgreSQLやSQLiteなど、色々なデータベースに接続するための命令が用意されています。PDOを使うことで、どのデータベースを利用する場合でも同じ関数を使うことができますので、もし将来データベースを変更することがあっても、PDOオブジェクトを作成する時に指定したパラメータだけを変更するばいいことになります。

index.php

<?php

define('DB_DATABASE', 'name_db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'xxxx');
define('PDO_DSN', 'mysql:dbhost=localhost;dbname=' . DB_DATABASE);

try {
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // insert
  $db->exec("insert into users (name, score) values('yamada', 55)");
  echo "user added!";

  // disconnect
  $db = null;

} catch (PDOException $e){
  echo $e->getMessage();
  exit;
}

%e7%84%a1%e9%a1%8c

prepared

<?php

define('DB_DATABASE', 'name_db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'xxxx');
define('PDO_DSN', 'mysql:dbhost=localhost;dbname=' . DB_DATABASE);

try {
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  /*
  (1) exec(): 結果を返さない、安全なsql
  (2) query(): 結果を返す、安全、何回も実行されない
  (3) prepare(): 結果を返す、安全対策が必要、複数回実行
  */

  $stmt = $db->prepare("insert into users(name, score) values(?, ?)");
  $stmt->execute(['name', 44]);
  echo "inserted: " . $db->lastInsertId();

} catch (PDOException $e){
  echo $e->getMessage();
  exit;
}

mysqlのバックアップ・復元

バックアップの取り方

[vagrant@localhost mysql]$ mysqldump -u dbuser -p blog_app > blog_app.dump.sql
Enter password:
[vagrant@localhost mysql]$ ls
blog_app.dump.sql  commands.sql

復元方法
不等号記号がバックアップの逆になります。
mysql -u dbuser -p blog_app < blog_app.dump.sql

mysqlで遊ぼう3

レコードの件数表示
select count(*) from users;
レコードのフィールドの値
select distinct team from users;
最大値の抽出
select max(score) from users;
select avg(score) from users;
select sum(score) from users;
チームごとの平均値表示
select avg(score) from users group by team;
ランダム関数も使えます。
select rand();
select * from users order by rand() limit 1;

mysql> select * from users order by rand() limit 1;
+----+--------+------------------+----------+------+-------+---------------------+
| id | name   | email            | password | team | score | created             |
+----+--------+------------------+----------+------+-------+---------------------+
|  3 | kimura | kimura@gmail.com | NULL     | red  |   2.3 | 2016-11-01 09:00:00 |
+----+--------+------------------+----------+------+-------+---------------------+
1 row in set (0.00 sec)

文字列、日付関数
select email, length(email) from users;
連結関数
select concat(name,'(‘,team,’)’) from users;
select concat(name,'(‘,team,’)’) as label from users;
select name, substring(team,1,1) from users;
select now();
select name,month(created) from users;
select name, datediff(now(), created) from users;

レコードの更新は通常updateを使います。

mysql> update users set team = 'red' where id = 5;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+----+----------+--------------------+----------+--------+-------+---------------------+
| id | name     | email              | password | team   | score | created             |
+----+----------+--------------------+----------+--------+-------+---------------------+
|  1 | yamada   | yamada@gmail.com   | NULL     | blue   |   5.5 | 2016-11-03 11:00:00 |
|  2 | sasaki   | sasaki@gmail.com   | NULL     | yellow |   8.2 | 2016-11-02 10:00:00 |
|  3 | kimura   | kimura@gmail.com   | NULL     | red    |   2.3 | 2016-11-01 09:00:00 |
|  4 | tanaka   | tanaka@gmail.com   | NULL     | blue   |   4.5 | 2016-10-31 08:00:00 |
|  5 | yamamoto | yamamoto@gmail.com | NULL     | red    |   7.4 | 2016-10-30 07:00:00 |
|  6 | yoshida  | yoshida@gmail.com  | NULL     | yellow |   4.2 | 2016-10-29 06:00:00 |
+----+----------+--------------------+----------+--------+-------+---------------------+
6 rows in set (0.00 sec)

削除にはdeleteを使用します。
delete from users where score <= 3.0; データ構造の変更
フィールドの追加
alter table users add full_name varchar(255) after name;
フィールドの変更
alter table users change full_name full_name varchar(100);
フィールドの削除
alter table users drop full_name;
テーブル名変更
alter table users rename blog_users;

テーブルの連結
テーブルを追加して、連結します。

create table posts (
 id int not null auto_increment primary key,
 user_id int not null,
 title varchar(255),
 body text,
 created datetime
);

insert into posts (user_id,title,body,created) values
(1, 'title-1 by yamada','body-1','2016-11-01 14:00:00'),
(1, 'title-2 by yamada','body-2','2016-10-31 13:00:00'),
(2, 'title-3 by sasaki','body-3','2016-10-30 12:00:00'),
(2, 'title-4 by sasaki','body-3','2016-10-29 11:00:00'),
(3, 'title-5 by kimura','body-4','2016-10-28 10:00:00'),
(3, 'title-6 by kimura','body-4','2016-10-27 09:00:00');

mysql> select * from posts;
+----+---------+-------------------+--------+---------------------+
| id | user_id | title             | body   | created             |
+----+---------+-------------------+--------+---------------------+
|  1 |       1 | title-1 by yamada | body-1 | 2016-11-01 14:00:00 |
|  2 |       1 | title-2 by yamada | body-2 | 2016-10-31 13:00:00 |
|  3 |       2 | title-3 by sasaki | body-3 | 2016-10-30 12:00:00 |
|  4 |       2 | title-4 by sasaki | body-3 | 2016-10-29 11:00:00 |
|  5 |       3 | title-5 by kimura | body-4 | 2016-10-28 10:00:00 |
|  6 |       3 | title-6 by kimura | body-4 | 2016-10-27 09:00:00 |
+----+---------+-------------------+--------+---------------------+
6 rows in set (0.00 sec)

mysql> select users.name,posts.title from users,posts where users.id=posts.user_id;
+--------+-------------------+
| name   | title             |
+--------+-------------------+
| yamada | title-1 by yamada |
| yamada | title-2 by yamada |
| sasaki | title-3 by sasaki |
| sasaki | title-4 by sasaki |
+--------+-------------------+
4 rows in set (0.00 sec)

条件指定することも可能です。
select users.name,posts.title,posts.created from users,posts where users.id=posts.user_id order by posts.created desc;

外部のsqlファイルからコマンドを打つことも可能です。

drop table if exists users;
create table users (
 name varchar(255),
 email varchar(255)
);
insert into users (name,email) values ('name','name@gmail.com');

外部コマンドの入力方法は以下の通りです。
[vagrant@localhost mysql]$ mysql -u dbuser -p blog_app < commands.sql

mysqlで遊ぼう2

テーブルの作り方

mysql> create table users (
    ->  id int,
    ->  name varchar(255),
    ->  email varchar(255),
    ->  password char(32)
    -> );
Query OK, 0 rows affected (0.33 sec)

テーブルの確認

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

削除する際は、drop table users とします。
mysqlでは扱えるデータ型は複数あります。
-数値
int
double
-文字列
char(固定長)
varchar(可変長)
text
-日付
date
datetime
それ以外
enum

また、フィールドにオプションをつけることが可能です。
入力必須:not null
デフォルト値: default ”
自動連番　auto_increment
索引(インデックス)　主キー(primary key)、キー(key)、ユニークキー(unique)

では、createして、descでテーブルの構造を見てみましょう。

mysql> create table users (
    ->  id int not null auto_increment primary key,
    ->  name varchar(255),
    ->  email varchar(255) unique,
    ->  password char(32),
    ->  score double,
    ->  sex enum('male', 'female') default 'male',
    ->  memo text,
    ->  created datetime,
    ->  key score(score)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc users;
+----------+-----------------------+------+-----+---------+----------------+
| Field    | Type                  | Null | Key | Default | Extra          |
+----------+-----------------------+------+-----+---------+----------------+
| id       | int(11)               | NO   | PRI | NULL    | auto_increment |
| name     | varchar(255)          | YES  |     | NULL    |                |
| email    | varchar(255)          | YES  | UNI | NULL    |                |
| password | char(32)              | YES  |     | NULL    |                |
| score    | double                | YES  | MUL | NULL    |                |
| sex      | enum('male','female') | YES  |     | male    |                |
| memo     | text                  | YES  |     | NULL    |                |
| created  | datetime              | YES  |     | NULL    |                |
+----------+-----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

挿入はinsert int tablename() values();、入力後は、select * from users;で確認できます。

mysql> insert into users (name,email,password,score,memo,created) values('name','test@gmail.com','xxx','5.5','memomemomemo','2016-11-04 12:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+------+----------------+----------+-------+------+--------------+---------------------+
| id | name | email          | password | score | sex  | memo         | created             |
+----+------+----------------+----------+-------+------+--------------+---------------------+
|  1 | name | test@gmail.com | xxx      |   5.5 | male | memomemomemo | 2016-11-04 12:00:00 |
+----+------+----------------+----------+-------+------+--------------+---------------------+
1 row in set (0.00 sec)

select * from users \G で縦に表示することも可能です。
select * from users where score >= 5.0; など条件付きの検索はwhereを使います。

mysql> select * from users where score >= 5.0;
+----+----------+--------------------+----------+--------+-------+---------------------+
| id | name     | email              | password | team   | score | created             |
+----+----------+--------------------+----------+--------+-------+---------------------+
|  1 | yamada   | yamada@gmail.com   | NULL     | blue   |   5.5 | 2016-11-03 11:00:00 |
|  2 | sasaki   | sasaki@gmail.com   | NULL     | yellow |   8.2 | 2016-11-02 10:00:00 |
|  5 | yamamoto | yamamoto@gmail.com | NULL     |        |   7.4 | 2016-10-30 07:00:00 |
+----+----------+--------------------+----------+--------+-------+---------------------+
3 rows in set (0.00 sec)

一部一致の検索は、likeを使います。

mysql> select * from users where email like '%@gmail.com';
+----+----------+--------------------+----------+--------+-------+---------------------+
| id | name     | email              | password | team   | score | created             |
+----+----------+--------------------+----------+--------+-------+---------------------+
|  1 | yamada   | yamada@gmail.com   | NULL     | blue   |   5.5 | 2016-11-03 11:00:00 |
|  2 | sasaki   | sasaki@gmail.com   | NULL     | yellow |   8.2 | 2016-11-02 10:00:00 |
|  3 | kimura   | kimura@gmail.com   | NULL     | red    |   2.3 | 2016-11-01 09:00:00 |
|  4 | tanaka   | tanaka@gmail.com   | NULL     | blue   |   4.5 | 2016-10-31 08:00:00 |
|  5 | yamamoto | yamamoto@gmail.com | NULL     |        |   7.4 | 2016-10-30 07:00:00 |
|  6 | yoshida  | yoshida@gmail.com  | NULL     | yellow |   4.2 | 2016-10-29 06:00:00 |
+----+----------+--------------------+----------+--------+-------+---------------------+
6 rows in set (0.00 sec)

その他の以下のようにコマンドです。
mysql> select * from users where score between 5.0 and 8.0;
mysql> select * from users where team in (‘red’, ‘yellow’);
mysql> select * from users where score <= 4.5 and team 'blue'; 並び替えはorder byを指定します。descは並び順が逆になります。 select * from users order by score; select * from users order by score desc; 制限を加えることもできます。 select * from users limit 3; select * from users limit 2, 2; select * from users order by score limit 3;

MySQLで遊ぼう

mysqlの開発者用サイトです。
MySQL
データベースには、テーブル、フィールド(列)、レコード(行)の概念があります。Excelのスプレッドシートのように考えると分かり易いです。

mysqlの起動では、ユーザー名を入れて起動します。

[vagrant@localhost ~]$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

パスワードの設定は以下の通りです。

set password for root@localhost=password(”)

データベースの作り方は、create databaseで作れます。

mysql> create database blog_app;
Query OK, 1 row affected (0.12 sec)

一覧表示

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| blog_app           |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.14 sec)

データベースの削除

mysql> drop database blog_app;
Query OK, 0 rows affected (0.21 sec)

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

データベースの利用

mysql> use test;
Database changed

作業ユーザーの設定方法

mysql> create database blog_app;
Query OK, 1 row affected (0.06 sec)

mysql> grant all on blog_app.* to dbuser@localhost identified by 'xxxx';
Query OK, 0 rows affected (0.16 sec)

作業ユーザーでのログイン

[vagrant@localhost ~]$ mysql -u dbuser -p blog_app
Enter password:

AWS サーバーの増設

サーバーのスペックだけでなく、ロードバランサを使って振り分けたいときなどは、イメージから、サーバーを作成します。
%e7%84%a1%e9%a1%8c

それから、ロードバランサーを作成していきます。
loadblancer

AWS　DBの起動

AWSのDBはRDSにありますので、そこからインスタンスを起動します。
%e7%84%a1%e9%a1%8c

Multi-AZ Deploymentの設定は通常「はい」にして、セキュリティを高めます。

DBインスタンスを作成したら、エンドポイントを確認します。
%e7%84%a1%e9%a1%8c

そして、mysqlに接続します。

[ec2-user@ip-172-31-21-202 ~]$ mysql -h mydbinstance.c7fqvxerxxxx.ap-northeast-1.rds.amazonaws.com -u                 name -p

AWS IMAGESの作成

IMAGESはインスタンスのテンプレートのようなものです。
%e7%84%a1%e9%a1%8c

イメージは、サーバーがシャットダウンされて、snapshots、AMIsが作られます。
インスタンスがシャットダウンされると、パブリックDNSが使用できなくなるので、Elastic IPsでIPアドレスを作成します。

そして、作成済みのインスタンスと関連付けを行います。
%e7%84%a1%e9%a1%8c

すると、インスタンスに割り当てられていることがわかります。
%e7%84%a1%e9%a1%8c

バックアップはElastic block storeのvolumesでクリップスナップショットをつくれば、その時点のバックアップをとります。
%e7%84%a1%e9%a1%8c

インスタンスのスペック変更は、一旦インスタンスを停止し、インスタンスタイプの変更で簡単に変更することができます。
%e7%84%a1%e9%a1%8c

t2.nanoでウェブサーバーを立ち上げよう

AWSに接続したら、サーバーにhttpd、php、mysqlをインストールします。

[ec2-user@ip-172-31-21-202 ~]$ sudo yum -y install httpd php mysql

インストールが完了したら、httpdの立ち上げを行います。

[ec2-user@ip-172-31-21-202 ~]$ sudo service httpd start
Starting httpd:                                            [  OK  ]
[ec2-user@ip-172-31-21-202 ~]$ sudo chkconfig httpd on

そうしたら、index.htmlファイルを作成し、ウェブサーバーが稼働しているか確認してみます。

[ec2-user@ip-172-31-21-202 ~]$ sudo vim /var/www/html/index.html

すると、反映されていることが確認できます。
%e7%84%a1%e9%a1%8c