日付カウントダウン

setTimeoutは、一定時間後に特定の処理をおこなう(繰り返さずに一度だけ)処理です。setTimeout(function(){
getResult();},200);で、0.2秒ごとに処理を繰り返します。new Date().getTime();から、Date.now()を引いた秒数で表示しています。

<!DOCTYPE html>
<html lang="ja">
  <head>
    <meta charset="utf-8">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link href="https://fonts.googleapis.com/css?family=Bilbo" rel="stylesheet">
    <title>Web font</title>
    <style>
    body {
      font-family: 'Bilbo', cursive;
      background: #000;
      text-align: center;
      font-size:36px;
      color:red;
    }
    h1 {
      color: #fff;
      font-size: 128px;
      margin:0;
    }
    #remainingDays, #remainingTime {
      font-size: 64px;
    }
    </style>
  </head>
  <body>
    <h1>Q</h1>
    <p>
      <span id="remainingDays"></span>days <span id="remainingTime"></span> to go
    </p>
  <script>
  function getResult(){
    var target = new Date(2020, 04, 01, 10, 0, 0).getTime();
    var now = Date.now();
    var diff = (target - now) /1000;
    var d = Math.floor(diff / 60 / 60/ 24);
    diff = diff - 60 * 60 * 24 * d;
    var h = Math.floor(diff / 60 / 60);
    diff = diff - 60 * 60 * h;
    var m = Math.floor(diff / 60);
    diff = diff - 60 * m;
    var s = Math.floor(diff);

    document.getElementById('remainingDays').innerHTML = d;
    document.getElementById('remainingTime').innerHTML = padZero(h)  + ':' + padZero(m) + ':' + padZero(s);
    setTimeout(function(){
    getResult();
  },200);
  }
  getResult();

  function padZero(n){
    return (n < 10) ? '0'+n : n;
  }

  </script>
  </body>
</html>

二次元バーコード生成

jQuery doc
.attr()
Get the value of an attribute for the first element in the set of matched elements or set one or more attributes for every matched element.

<!DOCTYPE html>
<html lang="ja">
  <head>
    <meta charset="utf-8">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>二次元バーコード</title>
    <style>
    body {

    }
    </style>
  </head>
  <body>
    <h1>二次元バーコード</h1>
    <p>
      <input type="text" id="msg">
      <input type="button" value="変換" id="button">
    </p>
    <div id="barCode"></div>
  <script>
    $(function(){
      $('#button').click(function(){
          var msg = encodeURIComponent($('#msg').val());
          var img = $('<img>').attr('src', 'http://chart.apis.google.com/chart?cht=qr&chs=150x150&choe=Shift_JIS&chl='+msg);
          $('#barCode').html(img);
      });
    });
  </script>
  </body>
</html>

Redisの操作


127.0.0.1:6379> rpush mycolor pink
(integer) 1
127.0.0.1:6379> rpush mycolor green
(integer) 2
127.0.0.1:6379> rpush mycolor red
(integer) 3
127.0.0.1:6379> rpush mycolor yellow
(integer) 4
127.0.0.1:6379> rpush mycolor blue
(integer) 5
127.0.0.1:6379> rpush myclolor purple
(integer) 1
127.0.0.1:6379> lrange mycolor 0 5
1) "pink"
2) "green"
3) "red"
4) "yellow"
5) "blue"
127.0.0.1:6379> lrange mycolor 0 -1
1) "pink"
2) "green"
3) "red"
4) "yellow"
5) "blue"

リスト操作

127.0.0.1:6379> rpop mycolor
"blue"
127.0.0.1:6379> lrange mycolor 0 -1
1) "pink"
2) "green"
3) "red"
4) "yellow"
127.0.0.1:6379> lpop mycolor
"pink"
127.0.0.1:6379> lrange mycolor 0 -1
1) "green"
2) "red"
3) "yellow"
127.0.0.1:6379> lindex mycolor 2
"yellow"
127.0.0.1:6379> llen mycolor
(integer) 3
127.0.0.1:6379> ltrim mycolor 0 2
OK
127.0.0.1:6379> lrange mycolor 0 -1
1) "green"
2) "red"
3) "yellow"

セット型の計算

127.0.0.1:6379> sadd myset1 a
(integer) 1
127.0.0.1:6379> sadd myset1 b
(integer) 1
127.0.0.1:6379> sadd myset1 c
(integer) 1
127.0.0.1:6379> smembers myset1
1) "c"
2) "b"
3) "a"
127.0.0.1:6379> sadd myset1 d
(integer) 1
127.0.0.1:6379> smembers myset1
1) "d"
2) "c"
3) "b"
4) "a"
127.0.0.1:6379> srem myset1 d
(integer) 1
127.0.0.1:6379> smembers myset1
1) "c"
2) "b"
3) "a"
127.0.0.1:6379> sadd myset2 b
(integer) 1
127.0.0.1:6379> sadd myset2 c
(integer) 1
127.0.0.1:6379> sadd myset2 e
(integer) 1
127.0.0.1:6379> smembers myset1
1) "c"
2) "b"
3) "a"
127.0.0.1:6379> smembers myset2
1) "c"
2) "b"
3) "e"
127.0.0.1:6379> sunion myset1 myset2
1) "b"
2) "c"
3) "e"
4) "a"
127.0.0.1:6379> sinter mysete1 myset2
(empty list or set)
127.0.0.1:6379> sdiff myset1 myset2
1) "a"

ソート済みの集合

127.0.0.1:6379> zadd hs 22 yamada
(integer) 1
127.0.0.1:6379> zadd hs 50 tanaka
(integer) 1
127.0.0.1:6379> zadd hs 80 yasuda
(integer) 1
127.0.0.1:6379> zadd hs 21 okamoto
(integer) 1
127.0.0.1:6379> zrange hs 0 -1
1) "okamoto"
2) "yamada"
3) "tanaka"
4) "yasuda"
127.0.0.1:6379> zrevrange hs 0 -1
1) "yasuda"
2) "tanaka"
3) "yamada"
4) "okamoto"
127.0.0.1:6379> zrank hs yamada
(integer) 1
127.0.0.1:6379> zrevrank hs yamada
(integer) 2

Redis

永続化の機能があるインメモリデータベースです。高速処理用に部分的に使われるなどのケースが多いようです。

http://redis.io/commands

インストールして、サーバーを立ち上げます。

[vagrant@localhost redis]$ redis-server
[14193] 12 Nov 04:58:10.521 # Warning: no config file specified, using the default config. In order to specify a config file use redis-server /path/to/redis.conf
[14193] 12 Nov 04:58:10.522 # You requested maxclients of 10000 requiring at least 10032 max file descriptors.
[14193] 12 Nov 04:58:10.522 # Redis can't set maximum open files to 10032 because of OS error: Operation not permitted.
[14193] 12 Nov 04:58:10.522 # Current maximum open files is 1024. maxclients has been reduced to 4064 to compensate for low ulimit. If you need higher maxclients increase 'ulimit -n'.
                _._
           _.-``__ ''-._
      _.-``    `.  `_.  ''-._           Redis 2.8.12 (00000000/0) 64 bit
  .-`` .-```.  ```\/    _.,_ ''-._
 (    '      ,       .-`  | `,    )     Running in stand alone mode
 |`-._`-...-` __...-.``-._|'` _.-'|     Port: 6379
 |    `-._   `._    /     _.-'    |     PID: 14193
  `-._    `-._  `-./  _.-'    _.-'
 |`-._`-._    `-.__.-'    _.-'_.-'|
 |    `-._`-._        _.-'_.-'    |           http://redis.io
  `-._    `-._`-.__.-'_.-'    _.-'
 |`-._`-._    `-.__.-'    _.-'_.-'|
 |    `-._`-._        _.-'_.-'    |
  `-._    `-._`-.__.-'_.-'    _.-'
      `-._    `-.__.-'    _.-'
          `-._        _.-'
              `-.__.-'

[14193] 12 Nov 04:58:10.524 # Server started, Redis version 2.8.12
[14193] 12 Nov 04:58:10.525 # WARNING overcommit_memory is set to 0! Background save may fail under low memory condition. To fix this issue add 'vm.overcommit_memory = 1' to /etc/sysctl.conf and then reboot or run the command 'sysctl vm.overcommit_memory=1' for this to take effect.
[14193] 12 Nov 04:58:10.526 * The server is now ready to accept connections on port 6379

cliからのアクセス, exit, shutdouwn

[vagrant@localhost redis]$ redis-cli

データベースの選択
> select

データの保存
> bgsave
bgsaveでデータベースを保存すると、redis-serverを立ち上げたディレクトリに、dump.rdbが作成されます。

データの設定

127.0.0.1:6379> set name yamada
OK
127.0.0.1:6379> get name
"yamada"
127.0.0.1:6379> mset email yamada@gmail.com score 120
OK
127.0.0.1:6379> get name email score
(error) ERR wrong number of arguments for 'get' command
127.0.0.1:6379> mget name email score
1) "yamada"
2) "yamada@gmail.com"
3) "120"

incr score

127.0.0.1:6379> incr score
(integer) 121
127.0.0.1:6379> get score
"121"
127.0.0.1:6379> decrby score 10
(integer) 111
127.0.0.1:6379> get score
"111"

keyの操作

127.0.0.1:6379> keys *
1) "name"
2) "score"
3) "email"
127.0.0.1:6379> keys *m*
1) "name"
2) "email"
127.0.0.1:6379> exists score
(integer) 1
127.0.0.1:6379> exists age
(integer) 0
127.0.0.1:6379> set age 14
OK
127.0.0.1:6379> rename age nenrei
OK
127.0.0.1:6379> keys *
1) "nenrei"
2) "email"
3) "score"
4) "name"
127.0.0.1:6379> del nenrei
(integer) 1
127.0.0.1:6379> keys *
1) "email"
2) "score"
3) "name"
127.0.0.1:6379> set age 14
OK
127.0.0.1:6379> expire age 5
(integer) 1
127.0.0.1:6379> get age
(nil)

psql document

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です。

psql 

条件検索

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 行)

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