Month: November 2016

Postgresには独自の命令文が多数用意されています。

PostgreSQL document

blogapp=# select name, length(name) from users;
  name  | length
--------+--------
 yamada |      6
 satou  |      5
 sasaki |      6
 yamada |      6
 satou  |      5
 sasaki |      6
 yamada |      6
(7 行)

抽選システム

blogapp=# select * from users order by random() limit 1;
 id |  name  | score | team
----+--------+-------+-------
  6 | sasaki |   7.6 | green
(1 行)

更新

blogapp=# update users set score = 5.8 where name = 'yamada';
UPDATE 3

削除

blogapp=# delete from users where id = 7;
DELETE 1

テーブルの変更

blogapp=# alter table users add fullname varchar(255);
ALTER TABLE
blogapp=# select * from users;
 id |  name  | score | team  | fullname
----+--------+-------+-------+----------
  2 | satou  |   4.3 | green |
  3 | sasaki |   8.2 | green |
  5 | satou  |   4.2 | blue  |
  6 | sasaki |   7.6 | green |
  1 | yamada |   5.8 | red   |
  4 | yamada |   5.8 | red   |
(6 行)

blogapp=# alter table users alter name type varchar(32);
ALTER TABLE

複数のユーザを突き合わせ

select users.name, post.title from users, posts wher users.id = posts.id;

複雑なselect文はviewを作成

blogapp=# create view yamada_posts as
blogapp-# select users.name, post.title from users, posts wher users.id = posts.id;

beginとcommitで囲むと、トランザクションという処理ができるようになります。変更の取り消しはrollbackです。

条件検索

blogapp=# select * from users where score > 4.0;
 id |  name  | score |  team
----+--------+-------+--------
  1 | yamada |   6.5 | red
  2 | satou  |   4.3 | green
  3 | sasaki |   8.2 | green
  5 | satou  |   4.2 | blue
  6 | sasaki |   7.6 | green
  7 | yamada |   5.7 | yellow
(6 行)

blogapp=# select * from users where name = 'yamada';
 id |  name  | score |  team
----+--------+-------+--------
  1 | yamada |   6.5 | red
  4 | yamada |   3.6 | red
  7 | yamada |   5.7 | yellow
(3 行)

並び順

blogapp=# select * from users order by score;
 id |  name  | score |  team
----+--------+-------+--------
  4 | yamada |   3.6 | red
  5 | satou  |   4.2 | blue
  2 | satou  |   4.3 | green
  7 | yamada |   5.7 | yellow
  1 | yamada |   6.5 | red
  6 | sasaki |   7.6 | green
  3 | sasaki |   8.2 | green
(7 行)

blogapp=# select * from users order by score desc;
 id |  name  | score |  team
----+--------+-------+--------
  3 | sasaki |   8.2 | green
  6 | sasaki |   7.6 | green
  1 | yamada |   6.5 | red
  7 | yamada |   5.7 | yellow
  2 | satou  |   4.3 | green
  5 | satou  |   4.2 | blue
  4 | yamada |   3.6 | red

offset

blogapp=# select * from users limit3 offset 3;
 id |  name  | score |  team
----+--------+-------+--------
  4 | yamada |   3.6 | red
  5 | satou  |   4.2 | blue
  6 | sasaki |   7.6 | green
  7 | yamada |   5.7 | yellow
(4 行)

レコード集計

blogapp=# select count(*) from users;
 count
-------
     7
(1 行)

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へのログイン

[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

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は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