Category: sqlite / MySql / psql

MYSAMとは、ストレートエンジンのこと。データの保存処理を行っており、MySQLでは「InnoDB」と「MyISAM」が有名。
MySQL5.5以上は、InnoDBがデフォルト。

違いは、InnoDBは対象のレコードに対してロックを行い、MyISAMは対象のテーブルに対してロックを行う。

まず、バージョン確認。

[vagrant@localhost map]$ mysql -v
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
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.

Reading history-file /home/vagrant/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6.34です。

続いて、databaseを作ります。

create database google;
use google;

ここで、テーブルを作る際に、MYISAMを指定します。

CREATE TABLE google.makers (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR (60) NOT NULL
) ENGINE = MYISAM;

以下のように、updateする内容を配列でもって、それをexecuteすれば、そのまま反映されます。

<?php

$dsn = "mysql:dbname=stock;host=localhost";
$user = "root";
$password = "";
try {
	$dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e){
	print('connection failed:'.$e->getMessage());
}

$change = array(420, 21, 2929);
$sql = "UPDATE stoph SET price = ?, ratio = ? WHERE code = ?";
$stmt = $dbh->prepare($sql);

$stmt->execute($change);
// $sql = "UPDATE stoph SET price = :price WHERE code = :code";
// $stmt->execute(array(':price'=>425, ':code'=>2929));
?>

PHP Data Objectの略で、php5.1.0以降のデータベース接続クラスの事。GDO(golf digest online)ではありません。

では、まずmysqlにログイン(mysql -u root -p)して、データベースをつくりましょう。サンプルで、ストップ高のテーブルをつくります。

create database stock;

次に、ストップ高のテーブルをつくります。

create table stock.stoph(
	code int,
	name varchar(255),
	price int,
        ratio int
);

そして、テーブルにストップ高の銘柄を入れていきます。

insert into stock.stoph values
(1743, 'コーアツ工業', 3175, 18.82),
(2929, 'ファーマフーズ', 440, 22.22),
(3469, 'デュアルタップ', 1144, 15.09),
(3825, 'リミックスポイント', 791, 14.47);

では、コーディングしていきます。

<?php

$dsn = "mysql:dbname=stock;host=localhost";
$user = "root";
$password = "";

try {
	$dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e){
	print('connection failed:'.$e->getMessage());
}

$sql = "select * from stoph";
$stmt = $dbh->query($sql);

while($result = $stmt->fetch(PDO::FETCH_ASSOC)){
	print($result["code"]);
	print($result["name"]);
	print($result["price"]);
	print($result["rate"]."<br>");
}


?>

new PDO とは、PDOのクラスをつくっています。
$dbh->query($sql)　は query(※実行)のメソッドに$sqlを代入しています。
$stmt->fetch(PDO::FETCH_ASSOC)は、厳密には、$dbh->query->fetch(PDO::FETCH_ASSOC)ですね。

もっとスマートな書き方があると思いますが。。。

<?php

$link = mysql_connect('localhost', 'root', '***');
if(!link){
	die('接続失敗です。'.mysql_error());
}
$selected = mysql_select_db('sample', $link);
if ($db_selected){
	die('データベース選択失敗です。'.mysql_error());
}

echo "接続に成功しました。<br>";

$data = array('4','word31','word32','word33','word34','word35','word36','word37','word38','word39','word40');
var_dump($data);

$sql = "INSERT INTO words(id, word1, word2, word3, word4, word5, word6, word7, word8, word9, word10) VALUES ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]')";
$result_flag = mysql_query($sql);

$result = mysql_query('SELECT * FROM words');
if (!$result){
	die('クエリーが失敗しました。'.mysql_error());
}

while ($row = mysql_fetch_assoc($result)) {
    print('<p>');
    print('id='.$row['id']);
    print(',word1='.$row['word1']);
    print('</p>');
}

echo "<br>";
$close_flag = mysql_close($link);

if($close_flag){
	echo "切断に成功しました。";
}

接続に成功しました。
array(11) { [0]=> string(1) “4” [1]=> string(6) “word31” [2]=> string(6) “word32” [3]=> string(6) “word33” [4]=> string(6) “word34” [5]=> string(6) “word35” [6]=> string(6) “word36” [7]=> string(6) “word37” [8]=> string(6) “word38” [9]=> string(6) “word39” [10]=> string(6) “word40” }
id=1,word1=ワード01

id=2,word1=ワード11

id=3,word1=ワード21

id=4,word1=word31

切断に成功しました。

update

$sql = sprintf("UPDATE words SET word1=%s, word2=%s, word3=%s, word4=%s, word5=%s, word6=%s, word7=%s, word8=%s, word9=%s, word10=%s WHERE id=2", quote_smart($data[1]), quote_smart($data[2]), quote_smart($data[3]), quote_smart($data[4]), quote_smart($data[5]), quote_smart($data[6]), quote_smart($data[7]), quote_smart($data[8]), quote_smart($data[9]), quote_smart($data[10]));

まず接続

<?php

$link = mysql_connect('localhost', 'root', '***');
if(!link){
	die('接続失敗です。'.mysql_error());
}

echo "接続に成功しました。<br>";

$close_flag = mysql_close($link);

if($close_flag){
	echo "切断に成功しました。";
}

接続に成功しました。
切断に成功しました。

データベースの選択

$selected = mysql_select_db('sample', $link);
if ($db_selected){
	die('データベース選択失敗です。'.mysql_error());
}

mysql_query　とmysql_fetch_assocで、テーブルからデータを取得。

<?php

$link = mysql_connect('localhost', 'root', '');
if(!link){
	die('接続失敗です。'.mysql_error());
}
$selected = mysql_select_db('sample', $link);
if ($db_selected){
	die('データベース選択失敗です。'.mysql_error());
}

echo "接続に成功しました。<br>";
$result = mysql_query('SELECT * FROM words');
if (!$result){
	die('クエリーが失敗しました。'.mysql_error());
}
$row = mysql_fetch_assoc($result);

$result = array();
foreach($row as $key => $value){
	$result[] = $value;
}
var_dump($result);

echo "<br>";
$close_flag = mysql_close($link);

if($close_flag){
	echo "切断に成功しました。";
}

接続に成功しました。
array(11) { [0]=> string(1) “1” [1]=> string(11) “ワード01” [2]=> string(11) “ワード02” [3]=> string(11) “ワード03” [4]=> string(11) “ワード04” [5]=> string(11) “ワード05” [6]=> string(11) “ワード06” [7]=> string(11) “ワード07” [8]=> string(11) “ワード08” [9]=> string(11) “ワード09” [10]=> string(11) “ワード10” }
切断に成功しました。　

mysql -u root -p で接続します。

[vagrant@localhost api]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
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.

次に、データベースをつくります。今回は、”sample”。

mysql> create database sample;
Query OK, 1 row affected (0.11 sec)

テーブルを作成する。

mysql> use  sample;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table sample.words(
    ->  id int,
    ->  word1 varchar(255),
    ->  word2 varchar(255),
    ->  word3 varchar(255),
    ->  word4 varchar(255),
    ->  word5 varchar(255),
    ->  word6 varchar(255),
    ->  word7 varchar(255),
    ->  word8 varchar(255),
    ->  word9 varchar(255),
    ->  word10 varchar(255)
    -> );
Query OK, 0 rows affected (0.27 sec)

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

データを挿入します。

mysql> insert into sample.words values
    -> (1, 'ワード01','ワード02','ワード03','ワード04','ワード05','ワード06','ワード07','ワード08','ワード09','ワード10'),
    -> (2, 'ワード11','ワード12','ワード13','ワード14','ワード15','ワード16','ワード17','ワード18','ワード19','ワード20'),
    -> (3, 'ワード21','ワード22','ワード23','ワード24','ワード25','ワード26','ワード27','ワード28','ワード29','ワード30');
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

テーブルのカラムを表示
desc words;
テーブルの中身を表示
select * from words;

Overview of Methodology: Data First

Information flow diagram – tasks, ER Diagram – Abstract code/SQL, Relational schema – PHP code, MySQL relational platform

Property values are
– lexical, visible, audible
– they are things that name other things

Relational Model
– Data Structures
– Constraints
– Operations
algebra
calculus

db. = sqlite3.connect(':memory:')
db.execute('create table links ' +
            '(id integer, submitter_id integer, submitted_time integer, ' +
            'votes integer, title text, url text)')
for l in links:
    db.execute('insert into links values (?, ?, ?, ?, ?, ?)', l)

def query():
    c = db.execute("select * from links")

def query():
    cursor = db.execute("select * from links")
    for link_tuple in cursor:
        link = Link(*link_tuple)

print query()

def query():
    cursor = db.execute("select * from links where submitter_id = 62443 and votes > 1000")
    link = Link(*c.fetchone())
    return link.id

order by

def query():
    cursor = db.execute("select * from links where submitter_id = 62443 order by submitter_time asc")
    for link_tuple in c:
        link = Link(*link_tuple)
        results.append(link.id)
    return results

join

def link_by_id(link_id):
    for l in links:
        if l.id == link_id:
            return l

scaling database is to 1.replicate, 2.shared.

<!doctype html>
<html>
<head>
  <title>検索画面</title>
  <meta charset="utf-8">
</head>
<body>
  <h1>検索画面</h1>

<form action="pdo_search.php" method="post">
  検索用語を入力:<input type="text" name="yourname">
<input type="submit" value="検索する">
</form>

</body>
</html>

mysqlのテーブルからfetch

<?php
header("Content-type: text/html; charset=utf-8");

if(empty($_POST)){
  header("Location: pdo_search_form.html");
  exit();
} else {
  //名前入力判定
  if(!isset($_POST&#91;'yourname'&#93;) || $_POST&#91;'yourname'&#93; === ""){
    $errors&#91;'name'&#93; = "名前が入力されていません。";
  }
}

if(count($errors) === 0){
  $dsn = 'mysql:host=localhost;dbname=test;charset=utf8';
  $user = 'dbuser';
  $password = 'xxxx';

  try {
    $dbh = new PDO($dsn, $user, $password);
    $statement = $dbh->prepare("select * from rss where title LIKE (:title)");

    if($statement){
      $yourname = $_POST['yourname'];
      $like_yourname = "%".$yourname."%";
      //プレースホルダへ実際の値を設定
      $statement->bindValue(':title',$like_yourname, PDO::PARAM_STR);

      if($statement->execute()){
        //レコード件数取得
        $row_count = $statement->rowCount();

        while($row = $statement->fetch()){
          $rows[] = $row;
        }
    } else {
      $errors['error'] = "検索失敗しました。";
    }
    $dbh = null;
  }
}catch (PDOException $e){
  print('Error:'.$e->getMessage());
  $errors['error'] = "データベース接続失敗しました。";
}

}
?>

<!doctype html>
<html>
<head>
  <title>検索結果</title>
  <meta charset="utf-8">
</head>
<body>

<?php if (count($errors) === 0): ?>

<p><?=htmlspecialchars($yourname, ENT_QUOTES, 'utf-8')."さんで検索しました。"?></p>
<p><?=$row_count?>件です。</p>

<table border='1'>
  <tr><td>id</td><td>title</td></tr>

<?php
foreach($rows as $row){
?>
<tr>
  <td><?=$row&#91;'id'&#93;?></td>
  <td><?=htmlspecialchars($row&#91;'title'&#93;,ENT_QUOTES,'utf-8')?></td>
</tr>
<?php
}
?>

<?php elseif(count($errors) > 0): ?>
<?php
  foreach($errors as $value){
    echo "<p>".$value."</p>";
  }
?>
<?php endif; ?>

</body>
</html>

mysql重複カラムを除外

SELECT ALL col_name, … FROM tbl_name;

example:
mysql> select distinct address from personal;

mysqlにデータを挿入する場合は、uniqueキーを設定して、重複しないようにする。