What's New Qiita

20190305のSQLに関する記事は6件です。

  • 投稿日：2019-03-05T23:46:32+09:00

TOEIC285学生のためのSQL

SQLとは

私は恥ずかしながら25歳になるまでSQLという言葉の存在すら知らなかった。

SQLは関係データベース管理システム (RDBMS) で、データの操作や定義を行うためのデータベース言語である

RDBMS....RedDragonBattleModeSystem（レッドドラゴンバトルモードシステム）....いや違う

Relational DataBase Management System（リレーショナルデータベースマネジメントシステム）

【Relational】リレーショナル・・・関係のある
【DataBase】データベース・・・記録の集合体
【Management】マネジメント・・・制御、管理
【System】システム・・・組織、機関

つまり、SQLは
データベースに対して「消えろ！」と言ったらデータが消えて
データベースに対して「増えろ！」と言ったらデータがコピーされる命令が出来る、らしい。

ところで　RDBMS　は「Relational DataBase Management System」
なら　SQL　は？　「SQLは略語じゃないそうです」

RDBMSのあるところにSQLあり、データを取り扱うにはSQLをマスターしたほうが良さそうです。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-03-05T21:22:53+09:00

バッチでpostgreDBからデータ抽出を行う

バッチでpostgresDBに接続してデータ抽出を行う。
実行ファイルはedb-psql.exeを用いる。

実行環境

windows server 2008 R2 Standard
psqledit_4.0.4.1

バッチ構成

D：
 |--¥#Batch開発
       |
       |--bat
       |   |--テストpos.bat
       | 
       |--sql
           |--テストpos.sql

テストpos.bat

テストpos.bat
@echo off
SET PGPASSWORD=testpass
SET PGUSER=usr_test
SET PGPORT=5432
SET PGHOST=10.xx.xx.xx
SET PGDATABASE=db_tsna

clip < ¥encoding SJIS

echo #############################
echo ## postgresに接続
echo #############################

D:¥db¥PostgreDB¥migrationstudio¥edb-psql.exe -f "D¥#Batch開発¥sql¥テストpos.sql"

使用するのはpsql.exeではなくedb-psql.exeです。

テストpos.sql

テストpos.sql
¥o PAR_DATA.txt

select 一連番号,更新年月日,sum(金額)
from table_TEST
where 更新年月日 < '20190301' and 更新年月日 > '20190131'
group by 一連番号,更新年月日
order by sum(金額) desc;

¥q

補足説明

先頭に¥o [ファイル名].[拡張子]を記述すると、postgreでファイル出力ができます。
セットとして最後尾に¥qを書いて、勝手に終了するようにします。
sqlスクリプトの保存は文字コードがUTF8、改行コードはLFにしないとうまくSQL文を読み取ってもらえません。
この時、出力ファイル名が日本語の場合は文字化けしてしまうので、今回は英語にしています。
出力ファイル名を変更したい場合は、テストpos.batの中にリネーム(ren)コマンドを付け加えることで対応できます。（コードは模索中です）

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-03-05T10:09:28+09:00

CloudFront から S3 へ出力されたログを、Athena で解析してみる

０．はじめに

先日、こちらの記事の対応を行ったんですが、

S3 へ出力されたログを解析するのにいい方法が無いかと調べたところ、Athena で解析するのがいいみたいなので試してみました。

基本的には、以下のページの手順に従って進めました。

他にこちらの記事なども参考にさせて頂きました。

こういった方々がノウハウをシェアして頂けることは、本当にありがたいですね♪

感謝!! ?‍♂️

１．S3 のログファイルのデータを Athena でテーブルを作成して入れ込む

  1. Athena のコンソールを開きます。
  2. 以下のクエリを実行して、テーブルを作成し、S3 のログファイルのデータを入れ込みます。
    • ※参考にしたサイトに記載されているクエリそのままです。S3 バケットを変えただけ。
    • FireShot Capture 004 - Athena - ap-northeast-1.console.aws.amazon.com.png
CREATE EXTERNAL TABLE IF NOT EXISTS default.cloudfront_logs (
         `date` DATE,
         time STRING,
         location STRING,
         bytes BIGINT,
         requestip STRING,
         method STRING,
         host STRING,
         uri STRING,
         status INT,
         referrer STRING,
         useragent STRING,
         querystring STRING,
         cookie STRING,
         resulttype STRING,
         requestid STRING,
         hostheader STRING,
         requestprotocol STRING,
         requestbytes BIGINT,
         timetaken FLOAT,
         xforwardedfor STRING,
         sslprotocol STRING,
         sslcipher STRING,
         responseresulttype STRING,
         httpversion STRING,
         filestatus STRING,
         encryptedfields INT 
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION 's3://[S3 のバケット名]/[CloudFront の Log Prefix]/' TBLPROPERTIES ( 'skip.header.line.count'='2' )

２．作成した Athena のテーブルへ、クエリを実行して必要なデータを抽出する

  1. 以下の様なクエリを実行して、テーブルから必要なデータを抽出します。
    • 以下のクエリは、
      • [日付(YYYY-MM-DD)] より後の、　※画像では、2019-03-03
      • [URL パス] へのアクセスで、　※画像では、/portal/index.html
      • [IP アドレス] からのアクセス
    • となっています。
    • FireShot Capture 005 - Athena - ap-northeast-1.console.aws.amazon.com.png
SELECT * FROM default.cloudfront_logs
WHERE date > date '[日付(YYYY-MM-DD)]'
AND uri = '[URL パス]'
AND requestip = '[IP アドレス]'

９９．ハマりポイント

  • そこまでハマりはしませんでいたが…、

  • クエリの WHERE 句の、DATE 条件がわからなくてちょっと困りました…。

ＸＸ．まとめ

CloudFront の導入で、運用や管理のやり方も変わってくるので、正直不安もありますが、こういったサービスがしっかりと用意されているのが、AWS の強みかと思います。

本当は、CloudWatch Logs に出力されると嬉しい気もしますが…、データ量が多すぎるとコスト量むし、このやり方がいい落としどころなのかもしれないですね。

何かご参考になれば嬉しいです!!

では♪

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-03-05T00:42:28+09:00

PostgreSQLの基本的なCRUD操作

この記事について

1年目の新人が備忘録＆アウトプット用として作成したので、至らないところはあるかもしれませんが、ご了承ください。:sweat_smile:

今回はPostgreSQLの基本的なCRUD操作について書いていきたいと思います。

PostgreSQLとは

PostgreSQL を一言で言えば
フリーなオープンソースのリレーショナルデータベース管理システム

他サイトからの引用ですが、簡単にいうとこれに尽きるかと思います。
じゃあ、他のRDBとの違いは何？と思ってらっしゃる方も多いのでしょうが、またその記事は後日書き記したいと思います。

ひとまず今回は、
「リレーショナルデータベースのことかー」
と思ってくださればOKです。:ok_hand:

CRUDとは

CRUD = Create（生成）、Read（読み取り）、Update（更新）、Delete（削除）
の操作をまとめてCRUDと呼んでいます。

webアプリケーションでいうと
Create（登録機能）、Read（参照機能）、Update（変更機能）、Delete（削除機能）
となります。

PostgreSQLの基本的なCRUDとは

これで、タイトルの「PostgreSQLの基本的なCRUD操作」の意味がわかったのではないでしょうか。

「データーベースに対する、基本的な登録、参照、変更、削除　の操作」
です。

基本的なCRUD操作

それでは、基本的なCRUD操作についてご紹介していきたいと思います。
CRUD操作に使用するテーブルはこちらです。
テーブル名はstudentsでid, name, age の列名があります。

id name age

これはCREATE TABLEをして作成しました。今回のCRUD操作とは別ですが、一応記しておきます。

基本構文はこちら:point_down:
CREATE TABLE テーブル名 (列名 データ型 制約 [, 列名 データ型 制約 …] );

例はこちら:point_down:
CREATE TABLE students(id CHAR(4) NOT NULL, name TEXT NOT NULL, age INTEGER, PRIMARY KEY (id));

まず、CREATE TABLEでテーブル名 = students を指定します。その後、列名 = idと、そのデータ型 = CHAR(4) と制約 = NOT NULLを指定しています。最後に主キーの指定をしています。

Create（生成）

まずは、INSERTです。こちらではテーブルに対して、値の追加をすることができます。

基本構文はこちら:point_down:
INSERT INTO テーブル名 (列名,列名...) VALUES (値,値...)

例はこちら:point_down:
INSERT INTO students (id, name, age) VALUES ('0001', '川端奈緒', 11);

まず、INSERT INTO でテーブル名 = students を指定します。その後、　列名 = id, name, ageを指定しています。 VALUESでは列に入れる値を指定しています。今回の場合は id ='0001', name = '川端奈緒', age = 11 という値を入れています。

この結果

id name age

上記のテーブルが、下記のようになります。

id name age
0001 川端奈緒 11

Read（読み取り）

続いてSELECTです。SELECTは指定した値など様々なデータベースの情報を取得することができます。

基本構文はこちら:point_down:
SELECT 列名 FROM テーブル名

例はこちら:point_down:
SELECT * FROM students

まず、取得したい列名に*を入れているので、全ての列を取得します。どこから列名を取得するのかというと、FROM の後に指定した studentsテーブルからです。

この結果はこちら。

id name age
0001 川端奈緒 11

全て取得しているので全て表示されます。

では、こちらはどうでしょう。
SELECT id FROM students
取得する列名のみidに変更しました。

その結果はこちら

id
0001

idを指定したので、idのみを取得することができました。

Update（更新）

次はUPDATEです。UPADTEは、既存の列に入っている値を更新することができます。

基本構文はこちら:point_down:
UPDATE テーブル名 SET 列名 = 値

例はこちら:point_down:
UPDATE students SET name = '多田典子'

UPDATEの後に更新対象のテーブル名 = students を指定します。その後のSETで　更新する列名 = nameと更新する値 = '多田典子'を入れます。

この結果はこちら。

id name age
0001 多田典子 11

nameの列が多田典子に更新されています。
ただし、この場合はnameの列が全て多田典子になってしまうので、１つの行を特定して更新したい場合は

UPDATE テーブル名 SET 列名 = 値 WHERE 削除する行を特定する条件

という風にWHEREを追加します。

例はこちら:point_down:
UPDATE students SET name = '多田典子' WHERE id = '0001'

とすれば、idが0001の行のnameが多田典子になります。

例えば、このようなテーブルがあったとします。

id name age
0001 川端奈緒 11
0002 山田智 12

これに対して

UPDATE students SET name = '多田典子' WHERE id = '0001'

こちらのSQLを流せば、

id name age
0001 多田典子 11
0002 山田智 12

という風にWHERE以降で指定した条件の id = '0001'に合致する行のみが更新対象となっています。

Delete（削除）

最後はDELETEです。DELETEは値の削除をすることができます。

基本構文はこちら:point_down:
DELETE FROM テーブル名 WHERE 削除する行を特定する条件

例はこちら:point_down:
DELETE FROM students WHERE id = '0001'

DELETE FROMの後にテーブル名 = studentsを指定します。その後、WHEREで 削除したい行を特定するために id = '0001'を指定しました。

id name age

指定したidの列が削除されています。WHEREを使用しなかった場合はテーブルすべての行が削除されてしまうので注意してください。

まとめ

今回は、CRUD操作の基礎を書きました。
INSERT ：　INSERT INTO テーブル名 (列名,列名...) VALUES (値,値...)
SELECT ：　SELECT 列名 FROM テーブル名
UPDATE ：　UPDATE テーブル名 SET 列名 = 値
DELETE ：　DELETE FROM テーブル名 WHERE 削除する行を特定する条件
です。

これはPostgreSQLの基礎中の基礎なので、今後はさらに詳しい記事を作成していきたいと思います。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-03-05T00:42:28+09:00

PostgreSQL 基本的なCRUD操作

この記事について

1年目の新人が備忘録＆アウトプット用として作成したので、至らないところはあるかもしれませんが、ご了承ください。:sweat_smile:

今回はPostgreSQLの基本的なCRUD操作について書いていきたいと思います。

PostgreSQLとは

フリーなオープンソースのリレーショナルデータベース管理システム

簡単にいうとこれに尽きるかと思います。
じゃあ、他のRDBMS（Relational DataBase Management System ）との違いは何？と思ってらっしゃる方も多いのでしょうが、またその記事は後日書き記したいと思います。

ひとまず今回は、
「データベースを管理するシステムのことかー」
と思ってくださればOKです。:ok_hand:

CRUDとは

CRUD = Create（生成）、Read（読み取り）、Update（更新）、Delete（削除）
の操作をまとめてCRUDと呼んでいます。

webアプリケーションでいうと
Create（登録機能）、Read（参照機能）、Update（変更機能）、Delete（削除機能）
となります。

PostgreSQLの基本的なCRUDとは

これで、タイトルの「PostgreSQLの基本的なCRUD操作」の意味がわかったのではないでしょうか。

「データーベースに対する、基本的な登録、参照、変更、削除　の操作」
です。

基本的なCRUD操作

それでは、基本的なCRUD操作についてご紹介していきたいと思います。
CRUD操作に使用するテーブルはこちらです。
テーブル名はstudentsでid, name, age の列名があります。

id name age

これはCREATE TABLEをして作成しました。今回のCRUD操作とは別ですが、一応記しておきます。

基本構文はこちら:point_down:
CREATE TABLE テーブル名 (列名 データ型 制約 [, 列名 データ型 制約 …] );

例はこちら:point_down:

CREATE TABLE students(id CHAR(4) NOT NULL, name TEXT NOT NULL, age INTEGER, PRIMARY KEY (id));

まず、CREATE TABLEでテーブル名 = students を指定します。その後、列名 = idと、そのデータ型 = CHAR(4) と制約 = NOT NULLを指定しています。最後に主キーの指定をしています。

Create（生成）

まずは、INSERTです。テーブルに対して、値の追加をすることができます。

基本構文はこちら:point_down:
INSERT INTO テーブル名 (列名,列名...) VALUES (値,値...)

例はこちら:point_down:

INSERT INTO students (id, name, age) VALUES ('0001', '川端奈緒', 11);

まず、INSERT INTO でテーブル名 = students を指定します。その後、　列名 = id, name, ageを指定しています。 VALUESでは列に入れる値を指定しています。今回の場合は id ='0001', name = '川端奈緒', age = 11 という値を入れています。

この結果

id name age

上記のテーブルが、下記のようになります。

id name age
0001 川端奈緒 11

Read（読み取り）

続いてSELECTです。指定した値など様々なデータベースの情報を取得することができます。

基本構文はこちら:point_down:
SELECT 列名 FROM テーブル名

例はこちら:point_down:

SELECT * FROM students

まず、取得したい列名に*を入れているので、全ての列を取得します。どこから列名を取得するのかというと、FROM の後に指定した studentsテーブルからです。

この結果はこちら。

id name age
0001 川端奈緒 11

全て取得しているので全て表示されます。

では、こちらはどうでしょう。

SELECT id FROM students

取得する列名のみidに変更しました。

その結果はこちら

id
0001

idを指定したので、idのみを取得することができました。

Update（更新）

次はUPDATEです。既存の列に入っている値を更新することができます。

基本構文はこちら:point_down:
UPDATE テーブル名 SET 列名 = 値

例はこちら:point_down:

UPDATE students SET name = '多田典子'

UPDATEの後に更新対象のテーブル名 = students を指定します。その後のSETで　更新する列名 = nameと更新する値 = '多田典子'を入れます。

この結果はこちら。

id name age
0001 多田典子 11

nameの列が多田典子に更新されています。
ただし、この場合はnameの列が全て多田典子になってしまうので、１つの行を特定して更新したい場合は

UPDATE テーブル名 SET 列名 = 値 WHERE 削除する行を特定する条件

という風にWHEREを追加します。

例はこちら:point_down:

UPDATE students SET name = '多田典子' WHERE id = '0001'

例えばこのようなテーブルがあったとします。

id name age
0001 川端奈緒 11
0002 山田智 12

これに対して先ほどののSQLを流せば、

id name age
0001 多田典子 11
0002 山田智 12

という風にWHERE以降で指定した条件の id = '0001'に合致する行のみが更新対象となっています。

Delete（削除）

最後はDELETEです。値の削除をすることができます。

基本構文はこちら:point_down:
DELETE FROM テーブル名 WHERE 削除する行を特定する条件

例はこちら:point_down:

DELETE FROM students WHERE id = '0001'

DELETE FROMの後にテーブル名 = studentsを指定します。その後、WHEREで 削除したい行を特定するために id = '0001'を指定しました。

id name age

指定したidの列が削除されています。WHEREを使用しなかった場合はテーブルすべての行が削除されてしまうので注意してください。

まとめ

今回は、CRUD操作の基礎を書きました。

CRUD 基本構文
INSERT INSERT INTO テーブル名 (列名,列名...) VALUES (値,値...)
SELECT 　SELECT 列名 FROM テーブル名
UPDATE テーブル名 SET 列名 = 値
DELETE DELETE FROM テーブル名 WHERE 削除する行を特定する条件

これはPostgreSQLの基礎中の基礎なので、今後はさらに詳しい記事を作成していきたいと思います。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-03-05T00:42:28+09:00

【PostgreSQL】 基本的なCRUD操作

PostgreSQLとは

フリーなオープンソースのリレーショナルデータベース管理システム

簡単にいうとこれに尽きるかと思います。
じゃあ、他のRDBMS（Relational DataBase Management System ）との違いは何？と思ってらっしゃる方も多いのでしょうが、またその記事は後日書き記したいと思います。

ひとまず今回は、
「データベースを管理するシステムのことかー」
と思ってくださればOKです。:ok_hand:

CRUDとは

CRUD = Create（生成）、Read（読み取り）、Update（更新）、Delete（削除）
の操作をまとめてCRUDと呼んでいます。

webアプリケーションでいうと
Create（登録機能）、Read（参照機能）、Update（変更機能）、Delete（削除機能）
となります。

【PostgreSQL】基本的なCRUDとは

これで、タイトルの「【PostgreSQL】基本的なCRUD操作」の意味がわかったのではないでしょうか。

「データーベースに対する、基本的な登録、参照、変更、削除　の操作」
です。

基本的なCRUD操作

それでは、基本的なCRUD操作についてご紹介していきたいと思います。
CRUD操作に使用するテーブルはこちらです。
テーブル名はstudentsでid, name, age の列名があります。

id name age

これはCREATE TABLEをして作成しました。今回のCRUD操作とは別ですが、一応記しておきます。

基本構文はこちら:point_down:
CREATE TABLE テーブル名 (列名 データ型 制約 [, 列名 データ型 制約 …] );

例はこちら:point_down:

テーブルの作成
CREATE TABLE students(id CHAR(4) NOT NULL, name TEXT NOT NULL, age INTEGER, PRIMARY KEY (id));

まず、CREATE TABLEでテーブル名 = students を指定します。その後、列名 = idと、そのデータ型 = CHAR(4) と制約 = NOT NULLを指定しています。最後に主キーの指定をしています。

Create（生成）

まずは、INSERTです。テーブルに対して、値の追加をすることができます。

基本構文はこちら:point_down:
INSERT INTO テーブル名 (列名,列名...) VALUES (値,値...)

例はこちら:point_down:

値の追加
INSERT INTO students (id, name, age) VALUES ('0001', '川端奈緒', 11);

まず、INSERT INTO でテーブル名 = students を指定します。その後、　列名 = id, name, ageを指定しています。 VALUESでは列に入れる値を指定しています。今回の場合は id ='0001', name = '川端奈緒', age = 11 という値を入れています。

この結果

id name age

上記のテーブルが、下記のようになります。

id name age
0001 川端奈緒 11

でも、毎回これをするのはめんどくさいですよね。

値の追加
INSERT INTO students (id, name, age) VALUES ('0001', '川端奈緒', 11);

そういう時は

値の追加
INSERT INTO students (id, name, age) VALUES ('0001', '川端奈緒', 11), ('0002', '安田梨沙', 21);

みたいな感じでVALUESを,で繋げると、連続で値を入れてくれます。

Read（読み取り）

続いてSELECTです。指定した値など様々なデータベースの情報を取得することができます。

基本構文はこちら:point_down:
SELECT 列名 FROM テーブル名

例はこちら:point_down:

データの取得
SELECT * FROM students

まず、取得したい列名に*を入れているので、全ての列を取得します。どこから列名を取得するのかというと、FROM の後に指定した studentsテーブルからです。

この結果はこちら。

id name age
0001 川端奈緒 11

全て取得しているので全て表示されます。

では、こちらはどうでしょう。

指定したデータの取得
SELECT id FROM students

取得する列名のみidに変更しました。

その結果はこちら

id
0001

idを指定したので、idのみを取得することができました。

Update（更新）

次はUPDATEです。既存の列に入っている値を更新することができます。

基本構文はこちら:point_down:
UPDATE テーブル名 SET 列名 = 値

例はこちら:point_down:

更新
UPDATE students SET name = '多田典子'

UPDATEの後に更新対象のテーブル名 = students を指定します。その後のSETで　更新する列名 = nameと更新する値 = '多田典子'を入れます。

この結果はこちら。

id name age
0001 多田典子 11

nameの列が多田典子に更新されています。
ただし、この場合はnameの列が全て多田典子になってしまうので、１つの行を特定して更新したい場合は

UPDATE テーブル名 SET 列名 = 値 WHERE 削除する行を特定する条件

という風にWHEREを追加します。

例はこちら:point_down:

指定したデータの更新
UPDATE students SET name = '多田典子' WHERE id = '0001'

例えばこのようなテーブルがあったとします。

id name age
0001 川端奈緒 11
0002 山田智 12

これに対して先ほどののSQLを流せば、

id name age
0001 多田典子 11
0002 山田智 12

という風にWHERE以降で指定した条件の id = '0001'に合致する行のみが更新対象となっています。

Delete（削除）

最後はDELETEです。値の削除をすることができます。

基本構文はこちら:point_down:
DELETE FROM テーブル名 WHERE 削除する行を特定する条件

例はこちら:point_down:

指定したデータの削除
DELETE FROM students WHERE id = '0001'

DELETE FROMの後にテーブル名 = studentsを指定します。その後、WHEREで 削除したい行を特定するために id = '0001'を指定しました。

id name age

指定したidの列が削除されています。WHEREを使用しなかった場合はテーブルすべての行が削除されてしまうので注意してください。

まとめ

今回は、CRUD操作の基礎を書きました。

CRUD 基本構文
INSERT INSERT INTO テーブル名 (列名,列名...) VALUES (値,値...)
SELECT 　SELECT 列名 FROM テーブル名
UPDATE テーブル名 SET 列名 = 値
DELETE DELETE FROM テーブル名 WHERE 削除する行を特定する条件

これはPostgreSQLの基礎中の基礎なので、今後はさらに詳しい記事を作成していきたいと思います。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

日付別

タグ一覧