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

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

データベースの作成

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

Smalltalk

あのAlan Kayが作った歴史ある言語です。Pharoの公式サイトより開発環境がダウンロードできます。

Pharo Download

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

作ったコンテンツはimageとして保存することが可能です。

入力はplayground、表示はtranscriptで表示します。smalltalkはオブジェクトに対して、メッセージを送信するものになっています。
%e7%84%a1%e9%a1%8c

Transcript show: 'hello'

ctl + ‘d’で実行のショートカットになっています。

改行

Transcript show: 'hello'.
Transcript cr.
Transcript show: 'world'

カスケード

Transcript show: 'hello';
cr;
show: 'world'

inspector it 感動の領域に入ってきました。
%e7%84%a1%e9%a1%8c

変数

message := 'hello'.
Transcript show:message.

二項メッセージ

4 + 3 factorial gcd: 5

クラスとインスタンス

t := Date today.
t addDays:14.

システムブラウザ

Color browse.
color :=Color random.
color.

2 sqrt
(5/2)asFloat
5 // 2
5 \\ 2
5 + 3 * 2

文字列、配列

$a charCode
#(1 2 3)

配列

#(1 2 3) size.
#(1 2 3) reverse.
#(1 2 #(5, 2)).
#(1 $a 'hello').
#(1 2 3),#(10, 12, 15).
x:= #(2 5 8 10)
x at:2. 
x at: 2 put: 20

ブロック

[5 + 4] value.
[:x |x+2]value:2.
[:x :y | x* y ]value:10 value:20.

f :=[:x:y|x + y + 2].
f value:10 value 20.

条件分岐

score :80.
(score > 60)
 ifTrue:['great!'] 
 ifFalse:['so so...']

ループ処理

i:=1.
10 timesRepeat:
[ Transcript show:i; cr.i:=i + 1].

配列の処理

#(1 3 5)do: 
	[:each| Transcript show:each; cr ].

R

> v <- c(1, 3, 5)
> v
[1] 1 3 5
> v[2]
[1] 3
> v[2] <- 10
> v
[1]  1 10  5
> length(v)
[1] 2
1] 2
> v <- seq(1, 10)
> v
 [1]  1  2  3  4  5  6  7  8  9 10
> v <- rep(1:5, times=3)
> v
 [1] 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5
> x <- c(1, 3, 5)
> y <- c(2, 4, 6)
> x*2
[1]  2  6 10
> x + y
[1]  3  7 11
> x > y
[1] FALSE FALSE FALSE
> x %in% y
[1] FALSE FALSE FALSE
> union(x, y)
[1] 1 3 5 2 4 6
> intersect(x, y)
numeric(0)
> setdiff(x, y)
[1] 1 3 5
> setequal(x, y)
[1] FALSE
> x <- c("S", "M", "L", "M", "L")
> x
[1] "S" "M" "L" "M" "L"
> x.fc <- factor(x)
> x.fc
[1] S M L M L
Levels: L M S
> levels(x.fc)
[1] "L" "M" "S"
> x.or <- ordered(x, levels=c("S","M","L"))
> x.or
[1] S M L M L
Levels: S < M < L
> x <- matrix(1:6, nrow=3, ncol=2)
> x
     [,1] [,2]
[1,]    1    4
[2,]    2    5
[3,]    3    6
> 
> x <- matrix(1:6, nrow=3, ncol=2, byrow=TRUE)
> x
     [,1] [,2]
[1,]    1    2
[2,]    3    4
[3,]    5    6
> x <- rbind(c(1, 2), 3:4, 5:6)
> x
     [,1] [,2]
[1,]    1    2
[2,]    3    4
[3,]    5    6
> x <- cbind(c(1,2),3:4,5:6)
> x
     [,1] [,2] [,3]
[1,]    1    3    5
[2,]    2    4    6
> x + 1
     [,1] [,2] [,3]
[1,]    2    4    6
[2,]    3    5    7
> 1/ x
     [,1]      [,2]      [,3]
[1,]  1.0 0.3333333 0.2000000
[2,]  0.5 0.2500000 0.1666667
> dim(x)
[1] 2 3
> nrow(x)
[1] 2
> ncol(x)
[1] 3
> x
     [,1] [,2] [,3]
[1,]    1    3    5
[2,]    2    4    6
> x[, 1]
[1] 1 2
> x[2,]
[1] 2 4 6
> x[1, 2]
[1] 3
> x[1, 1:2]
[1] 1 3
> x[1, c(1,2)]
[1] 1 3
> x[1, 2] <- 10
> x
     [,1] [,2] [,3]
[1,]    1   10    5
[2,]    2    4    6
> edit(x)
     col1 col2 col3
[1,]    1   10    5
[2,]    2    4    6
> x2 <- edit(x)
> x
     [,1] [,2] [,3]
[1,]    1   10    5
[2,]    2    4    6
> x <- list(5:10, "abc", matrix(1:6, nrow=2, ncol=3))
> x
[[1]]
[1]  5  6  7  8  9 10

[[2]]
[1] "abc"

[[3]]
     [,1] [,2] [,3]
[1,]    1    3    5
[2,]    2    4    6

> x[1]
[[1]]
[1]  5  6  7  8  9 10

> x[[1]]
[1]  5  6  7  8  9 10
> x[[3]][1, 2]
[1] 3
x <- read.table("sales.txt", header=TRUE, sep=",", na.strings="*")
sum(x$sales), max(x$sales), mean(x$sales),median(x$sales),sd($sales)
mean(x$DISTANCE, na.rm=TRUE), summary(x$sales), summary(x), str(x)

p5.js random()

ランダム関数で座標軸や円の半径を決めていきます。。

/*
*/

var x, y, r;

function setup(){
    createCanvas(480, 240);
    background(255, 0, 0, 10);
    noStroke();
    background('skyblue')

}

function draw(){
  x = random(width);
  y = random(height);
  if (random()> 0.9) {
    r = random (50, 60);
  } else {
    r = random(10, 30);
  }
  fill(255, 255, 255, random(30, 250));
  ellipse(x, y, r, r);
}

sin(),cos()

var angle = 0;
var r = 50;

function setup(){
    createCanvas(480, 240);
    noStroke();

      background('skyblue')

    slider = createSlider(0, 100, 30)
    slider.position(10, 20);

    button = createButton("clear!");
    button.position(10, 40);
    button.mousePressed(function(){
      background('skyblue');
    });
}

function draw(){
  r = slider.value();
  push();
  translate(width/2, height/2);
  x = sin(radians(angle)) * r;
  y = cos(radians(angle)) * r;
  ellipse(x, y, 10, 10);
  pop();
  angle += 2;
  r += 0.1;
}

p5.js

p5.jsはprocessingのJavaScriptです。CSSの表記をそのまま使用することもできます。

rectMode(coner),rectMode(coners),rectMode(center)などあります。

function setup(){
  createCanvas(480, 240);
    background(255, 0, 0, 10);

    rect(50, 50, 150, 100)
}

function draw(){

}

楕円 ellipse, point, arc, line

    var c = color('pink')
    fill(c);
    stroke(c);
    ellipse(width/2, height/2, 100, 100)
    text("hello world", 100, 100);

座標軸による操作

function setup(){
    createCanvas(480, 240);
    background(255, 0, 0, 10);
    noStroke();

    fill(255, 0, 0, 127);
    rect(0, 0, 100, 100);

    r = 30;
    push();
    translate(10, 10);
    rotate(radians(r));
    scale(1.2, 1.2)
    fill(0, 0, 255, 127);
    rect(0, 0, 100, 100);
    pop();
}

マウスの操作

function draw(){
   noStroke();
   background('skyblue');
   ellipse(mouseX, mouseY, 34, 34);
}

入力による動作

r = 50;

function setup(){
    createCanvas(480, 240);
    background(255, 0, 0, 10);
   

}

function draw(){
   noStroke();
   background('skyblue');

   if (keyIsPressed === true){
    fill('pink');
   } else {
    fill('white');
   }
   ellipse(mouseX,mouseY, r, r)

}

function keyTyped(){
  if (key === 'u'){
    r += 10;
  }
}

Processing

Processing is a flexible software sketchbook and a language for learning how to code within the context of the visual arts.

プロセッシングはjavaで、主にアーティスト・デザイナー向けです。
processing

size(200, 200);
smooth();
background(255);
rect(50, 50, 80, 30);

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

角丸

rect(50, 50, 80, 30, 10);

rectMode()

rectMode(CORNER);
rect(50, 50, 30, 30);
rectMode(RADIUS);
rect(50, 50, 30, 40);

fill(255, 0, 0, 127);
rect(50, 50, 30, 40);

枠線

stroke(#ff0000);
strokeWeight(3);
fill(127);
rect(50, 50, 100, 100);

円、線

stroke(#ff0000);
fill(#999999);

line(50, 50, 100, 100);
ellipse(50, 50, 80, 80);
arc(50, 50, 80, 80, 0, radians(180));

矢印

stroke(#ff0000);
fill(#999999);

beginShape();
vertex(100, 20);
vertex(120, 100);
vertex(100, 80);
vertex(80, 100);
endShape(CLOSE);

画像の表示

PImage img;
img = loadImage("a.jpg");
image(img,10, 10);

テキスト描画

PFont f;
f = createFont("Helvetica", 16, true);

textFont(f, 12);
fill(#ff0000);

text("hello world", 100, 100);

座標空間の変更

fill(#ff0000, 127);
noStroke();
rect(10, 10, 50, 50);

fill(#0000ff, 127);
noStroke();
pushMatrix();
translate(10, 10);
rect(10, 10, 50, 50);
popMatrix();

立体

size(200, 200, P3D);
smooth();
background(255);
lights();

pushMatrix();
translate(50, 50, 0);
rotateX(radians(30));
rotateY(radians(40));
rotateZ(radians(10));
box(40);
popMatrix();

setup()

void setup(){
  size(200, 200, P3D);
smooth();
background(255);

 noStroke();
 fill(#ff0000);
 
 frameRate(30);
}

int x = 0;
int y = 0;
void draw(){
  rect(x, y, 100, 100);
  x++;
  y++;
  println(frameCount);
}

マウス情報

int r = 100;
void draw(){
  ellipse(mouseX, mouseY, r, r);
}

ライブラリ
https://processing.org/reference/