Postgresの操作

blogapp=# \dt
           リレーションの一覧
 スキーマ | 名前  |    型    |  所有者
----------+-------+----------+----------
 public   | posts | テーブル | postgres
(1 行)

blogapp=# \d posts
         テーブル "public.posts"
 カラム |           型           | 修飾語
--------+------------------------+--------
 title  | character varying(255) |
 body   | text                   |

外部ファイルからコマンド入力も可能です。

blogapp=# \i commands.sql
CREATE TABLE

テーブルの削除はdrop table hoge;

sqlコマンド

create table posts (
  id serial primary key,
  title varchar(255) not null,
  body text check(length(body)> 5),
  id_draft boolean default TRUE,
  created timestamp default 'now'
 );
blogapp=# insert into posts (title, body) values('title1', 'body1111');
INSERT 0 1
blogapp=# select * from posts;
 id | title  |   body   | id_draft |          created
----+--------+----------+----------+----------------------------
  1 | title1 | body1111 | t        | 2016-11-12 03:44:59.912257
create table users (
	id serial primary key,
	name varchar(255),
	score real,
	team varchar(255)
);
insert into users (name, score, team) values
('yamada', 6.5, 'red'),
('satou', 4.3, 'green'),
('sasaki', 8.2, 'green'),
('yamada', 3.6, 'red'),
('satou', 4.2, 'blue'),
('sasaki', 7.6, 'green'),
('yamada', 5.7, 'yellow');
blogapp=# select * from users;
 id |  name  | score |  team
----+--------+-------+--------
  1 | yamada |   6.5 | red
  2 | satou  |   4.3 | green
  3 | sasaki |   8.2 | green
  4 | yamada |   3.6 | red
  5 | satou  |   4.2 | blue
  6 | sasaki |   7.6 | green
  7 | yamada |   5.7 | yellow
blogapp=# select name, score from users;
  name  | score
--------+-------
 yamada |   6.5
 satou  |   4.3
 sasaki |   8.2
 yamada |   3.6
 satou  |   4.2
 sasaki |   7.6
 yamada |   5.7
(7 行)

PostgreSQL

PostgreSQLへのログイン

[vagrant@localhost ~]$ cd postgre
[vagrant@localhost postgre]$ psql -U postgres
psql (8.4.20)
"help" でヘルプを表示します.

一覧表示

postgres-# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) |
アクセス権
———–+———-+——————+————-+——————-+—-
——————-
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/
postgres
: pos
tgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/
postgres
: pos
\

データベースの作成

postgres=# create database blogapp;
CREATE DATABASE

データベースへの接続

[vagrant@localhost postgre]$ psql -U postgres
psql (8.4.20)
"help" でヘルプを表示します.

postgres=# \connect blogapp
psql (8.4.20)
データベース "blogapp" に接続しました。.
blogapp=# select now();
              now
-------------------------------
 2016-11-12 02:18:41.482582+09
(1 行)

テーブルの作成

blogapp=# create table posts (title varchar(255), body text);
CREATE TABLE

mongo ドキュメント操作

db.hoge.insert();でドキュメントにデータを挿入し、countで数、findで全データを表示します。

> use mydb;
switched to db mydb
> db.users.insert(
... {
... name: "yoshida",
... socore: 30
... }
... );
Cannot use 'commands' readMode, degrading to 'legacy' mode
WriteResult({ "nInserted" : 1 })
> show collections;
system.indexes
users
> db.users.insert({
... name: "yamada",
... score: 50,
... tags: ["web", "mobile"]
... });
WriteResult({ "nInserted" : 1 })
> for (var i = 0; i < 10; i++){
... db.users.insert({
... score: Math.random()
... });
... }
WriteResult({ "nInserted" : 1 })
> dbusers.count();
2016-11-12T01:02:09.395+0900 E QUERY    [thread1] ReferenceError: dbusers is not       defined :
@(shell):1:1

> db.users.count();
12
> db.users.find();
{ "_id" : ObjectId("5825eabd1d7f44161709949b"), "name" : "yoshida", "socore" : 3      0 }
{ "_id" : ObjectId("5825eb3b1d7f44161709949c"), "name" : "yamada", "score" : 50,       "tags" : [ "web", "mobile" ] }
{ "_id" : ObjectId("5825eb711d7f44161709949d"), "score" : 0.46792416776462054 }
{ "_id" : ObjectId("5825eb711d7f44161709949e"), "score" : 0.7100909501228141 }
{ "_id" : ObjectId("5825eb711d7f44161709949f"), "score" : 0.5440256361301811 }
{ "_id" : ObjectId("5825eb711d7f4416170994a0"), "score" : 0.697844529230746 }
{ "_id" : ObjectId("5825eb711d7f4416170994a1"), "score" : 0.16130254718814452 }
{ "_id" : ObjectId("5825eb711d7f4416170994a2"), "score" : 0.6562384177250872 }
{ "_id" : ObjectId("5825eb711d7f4416170994a3"), "score" : 0.4280463098549997 }
{ "_id" : ObjectId("5825eb711d7f4416170994a4"), "score" : 0.39841037701898585 }
{ "_id" : ObjectId("5825eb711d7f4416170994a5"), "score" : 0.5303831592156817 }
{ "_id" : ObjectId("5825eb711d7f4416170994a6"), "score" : 0.7516972732626204 }
> db.users.remove({});
WriteResult({ "nRemoved" : 12 })

ドキュメントの操作
-gte: greater than equeal, ※gt, lte, ltなども
db.user.find(score: {$gte: 50})
$eq: equal, $ne: not equal
name: /t/ , /^t/
db.users.distinct(“team”)

複雑な条件抽出
db.users.find({name:/i/, score:{gte:50}});
db.users.find({age: {$exists: true}})

表示するフィールドを指定
db.users.find({}, {name: true, score:1});

条件
db.user.find({}, {_id:0]}).sort({score: 1});
db.user.find({}, {_id:0]}).sort({score: -1});
db.user.find({}, {_id:0]}).limit(3);
db.user.findOne({}, {_id:0]})
db.user.find({}, {_id:0]}).skip(2);

ドキュメント更新
db.users.update({name: “yamada”}),{$set: {score: 80}});
db.users.update({name: “yamada”}),{name: “yamada”, score: 40});
db.users.update({team:”team-2″}),{$set: {score: 100}},{multi; true});

$inc, $mul, $rename, $unset
db.users.update({name: “okamoto”}, {$inc: {score:5}});
db.users.update({name: “okamoto”}, {$mul: {score:2}});

$upsert, remove()
db.users.update({name: “kato”}, {name: “kato”, score:58}{upsert: true});
db.users.remove({name: “kato”});

索引
db.users.getIndexes();
db.users.createIndex({score: -1});
db.users.createIndex({score: 1});
db.users.dropIndex(“score_-1”);

mongodump -d mydb
[vagrant@localhost mongo]$ mongodump -d mydb
2016-11-12T01:34:45.130+0900 writing mydb.system.indexes to
2016-11-12T01:34:45.147+0900 done dumping mydb.system.indexes (1 document)
2016-11-12T01:34:45.148+0900 writing mydb.users to
2016-11-12T01:34:45.208+0900 done dumping mydb.users (0 documents)

バックアップの復元
mongorestore –drop

MongoDB

MongoDBはNoSQL(スキーマレス)の部類に入るもので、予めデータ構造を決める必要はありません。データベースの中に、コレクション(テーブル)、ドキュメントを作っていきます。データ構造を変更する必要がないので、データを柔軟に管理できます。

RedHat系のmogoDBのインストールはこちら
MogDB Install

> show dbs;
admin  (empty)
local  0.078GB
> use mydb;
switched to db mydb
> show dbs;
admin  (empty)
local  0.078GB
> db.createCollection("users");
{ "ok" : 1 }
> show dbs;
admin  (empty)
local  0.078GB
mydb   0.078GB
> db.stats();
{
        "db" : "mydb",
        "collections" : 3,
        "objects" : 4,
        "avgObjSize" : 64,
        "dataSize" : 256,
        "storageSize" : 24576,
        "numExtents" : 3,
        "indexes" : 1,
        "indexSize" : 8176,
        "fileSize" : 67108864,
        "nsSizeMB" : 16,
        "dataFileVersion" : {
                "major" : 4,
                "minor" : 5
        },
        "extentFreeList" : {
                "num" : 0,
                "totalSize" : 0
        },
        "ok" : 1
}
> db.dropDatabase();
{ "dropped" : "mydb", "ok" : 1 }
> show dbs;
admin  (empty)
local  0.078GB

コレクションの操作

> use mydb;
switched to db mydb
> db.createCollection("users");
{ "ok" : 1 }
> show collections;
Cannot use 'commands' readMode, degrading to 'legacy' mode
system.indexes
users
> db.users.renameCollections("customers");
2016-11-12T00:54:19.076+0900 E QUERY    [thread1] TypeError: db.users.renameCollections is not a function :
@(shell):1:1

> show collections;
system.indexes
users
> db.users.renameCollection("customers");
{ "ok" : 1 }
> show collections;
customers
system.indexes
> db.customers.drop();
true

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:

vagrantにPostgreSQLをインストール

まず、PostgreSQLですが、オープンソースのオブジェクトリレーショナルデータベース管理システム(ORDBMS)です。SQLインタフェースは、ISO/IEC の標準SQLにならった実装になっています。トランザクション、ストアドプロシージャ、トリガ、ビュー、ユーザ定義関数など多彩な機能を備えながらもコンパクトなプログラムで、CPU 数が増えた場合のスケーラビリティが高く、接続数が増えても性能が維持されます。

PostgreSQL

こちらを、vagrant CentOS6に入れたいと思います。

[vagrant@localhost ~]$ sudo yum install postgresql
[vagrant@localhost ~]$ sudo yum install postgresql-server
[vagrant@localhost ~]$ sudo yum install postgresql-contrib
[vagrant@localhost ~]$ service postgresql
Usage: /etc/init.d/postgresql {start|stop|status|restart|condrestart|try-restart|reload|force-reload|initdb}
[vagrant@localhost ~]$ service postgresql status
postmaster は停止しています

続いて初期化します。

[vagrant@localhost ~]$ sudo -u postgres /usr/bin/initdb -D /var/lib/pgsql/data
[vagrant@localhost ~]$ sudo service postgresql start
postgresql サービスを開始中:  

VirtualBox(CentOS)にMongoDBをインストール

VirtualBoxにMongoDBをインストールする手順です。まず、10genリポジトリを追加しましょう。
追加にはvimを使います。vimは高機能エディタで、特にモーション(移動)とオペレータ(操作)が強みであると言われています。

では、vimが入っているか確認してみましょう。

[root@localhost vagrant]# yum list installed | grep vim
vim-minimal.x86_64   2:7.4.629-5.el6    @anaconda-CentOS-201605220104.x86_64/6.8

では、vimを使って、10genリポジトリを追加します。

[root@localhost vagrant]# vi /etc/yum.repos.d/10gen.repo

vimの操作は「i」で挿入、「Esc」でコマンドモード、「:wq」で保存して終了です。

[10gen]
name=10gen Repository
baseurl=http://downloads-distro.mongodb.org/repo/redhat/os/x86_64
gpgcheck=0
enabled=0

追加したら、mogodbをインストールしましょう。

[root@localhost vagrant]# yum install mongo-10gen-server.x86_64 mongo-10gen.x86_64 --enablerepo=10gen

少し時間がかかりますが、インストールしたら、自動起動の設定をして、アクセスしてみましょう。

[root@localhost ~]# sudo /etc/init.d/mongod start
Starting mongod:                                           [  OK  ]
[root@localhost ~]# sudo chkconfig mongod on
[root@localhost ~]# mongo
MongoDB shell version: 2.6.12
connecting to: test
Welcome to the MongoDB shell.
For interactive help, type "help".
For more comprehensive documentation, see
        http://docs.mongodb.org/
Questions? Try the support group
        http://groups.google.com/group/mongodb-user
> exit
bye

こちらで、インストール完了です。