RDB

PreviousPython NextRedmine

Last updated 4 years ago

RDB

用語

DDL：Data Definition Language データ定義言語。データを格納するための構造を定義する。

DML：Data Manipulation Language データ操作言語。定義されたデータ構造中の個々のデータを操作する。

DCL：Data Control Language データ制御言語。データへのアクセス権限などを制御する。

命名規則

命名規則はスネークケース(小文字で単語を_区切りにしたもの)を採用しているパターンが多い

カラム名もスネークケースを採用したほうが命名規則が統一されて美しい

テーブル編
複数形

tlm_values

基本的にはRailsの命名規則に従うのがよいだろう

SQLite

使い方

よく使うコマンド

.databases

.table

.dump ?TABLE?

.help

.exit

利用可能な型

PostgreSQL

よく使うSQL

CSVにする

COPY (select * from foo) TO '/tmp/aaa.csv' (FORMAT csv);

バイナリをlike検索

select * from binary where data::varchar like '%010203%';

Byteaを挿入

普通にinsertすればよい

INSERT INTO table (data) VALUES (
\x13070809'
);

Select結果をinsert

INSERT INTO table(A, B, C, time)
SELECT
0,
'aaa',
COL_C,
lag(time) OVER (ORDER BY time asc),
FROM
table2

ページャーを変更

シェルで

export PAGER=less

ページャーを使わない

\pset pager off

キャラクタ型を整数に変換する方法

ALTER TABLE cps_def ALTER COLUMN no_imaging_flag TYPE integer USING (no_imaging_flag::integer);

DB内全消去

drop schema public cascade; create schema public;

Tips

カラム内のJson配列の結果の最大値を取得する

select max(cast(j.jsonb_array_elements_text as integer)) from (select jsonb_array_elements_text(t.time_range) from (select (select jsonb_agg(t -> 'time') as time_range from jsonb_array_elements(param_array) as x(t)) from data where type = 'D') t) j;

型を調べる

pg_typeof

カラムのjsonのデータにアクセスする

select (select jsonb_agg(t -> 'time') from jsonb_array_elements(parameters) as x(t)) from table where type = 'A';

integerからdayに変換する

かければいい

2 * interval '1 day'

intervalから日数だけintegerで抜き出す

extract(day from '2019-07-22 10:00:00' - time)

intervalから日数だけ抜き出す

date_trunc('days', '2019-07-22 10:00:00' - time)

datetimeをtimeにキャストする方法

cast("start_time" as time)

特定のカラムを対象として重複行を削除したい

enumにコメントをつけたい

各項目にはつけられないみたいなのでenum自体に列挙する CREATE TYPE my_type_description AS ENUM('foo desc', 'bar desc');

ひとつ前のレコードの前回値を取得する

ORDER BYでnull値を除外する

WHERE target_column IS NOT NULL ORDER BY target_column

target_columnに別名をつけているの場合はWHEREできないので

ORDER BY target_column desc NULLS LAST

MySQL

コマンド

mysql -uroot -p'password' -D database_name

Tips

バイナリを曖昧検索する

SELECT * FROM table WHERE data like BINARY CONCAT('%', 0x6671, '%')

TroubleShooting

phpmyadminでblobのバイナリフィールドを表示する方法

ActiveRecord::StatementInvalid (Mysql2::Error: Incorrect string value:というエラーが出る

文字コードが原因。

show table status;

をして、

latin1_swedish_ci とかを探す。

あったら、

ALTER TABLE my_table CONVERT TO CHARACTER SET utf8;

をしてUTF-8にする。

PreviousPython NextRedmine

Last updated 4 years ago

用語

DDL：Data Definition Language データ定義言語。データを格納するための構造を定義する。

DML：Data Manipulation Language データ操作言語。定義されたデータ構造中の個々のデータを操作する。

DCL：Data Control Language データ制御言語。データへのアクセス権限などを制御する。

命名規則

命名規則はスネークケース(小文字で単語を_区切りにしたもの)を採用しているパターンが多い

カラム名もスネークケースを採用したほうが命名規則が統一されて美しい

テーブル編
複数形

tlm_values

基本的にはRailsの命名規則に従うのがよいだろう

SQLite

使い方

よく使うコマンド

.databases

.table

.dump ?TABLE?

.help

.exit

利用可能な型

PostgreSQL

よく使うSQL

CSVにする

COPY (select * from foo) TO '/tmp/aaa.csv' (FORMAT csv);

バイナリをlike検索

select * from binary where data::varchar like '%010203%';

Byteaを挿入

普通にinsertすればよい

INSERT INTO table (data) VALUES (
\x13070809'
);

Select結果をinsert

INSERT INTO table(A, B, C, time)
SELECT
0,
'aaa',
COL_C,
lag(time) OVER (ORDER BY time asc),
FROM
table2

ページャーを変更

シェルで

export PAGER=less

ページャーを使わない

\pset pager off

キャラクタ型を整数に変換する方法

ALTER TABLE cps_def ALTER COLUMN no_imaging_flag TYPE integer USING (no_imaging_flag::integer);

DB内全消去

drop schema public cascade; create schema public;

Tips

カラム内のJson配列の結果の最大値を取得する

型を調べる

pg_typeof

カラムのjsonのデータにアクセスする

select (select jsonb_agg(t -> 'time') from jsonb_array_elements(parameters) as x(t)) from table where type = 'A';

integerからdayに変換する

かければいい

2 * interval '1 day'

intervalから日数だけintegerで抜き出す

extract(day from '2019-07-22 10:00:00' - time)

intervalから日数だけ抜き出す

date_trunc('days', '2019-07-22 10:00:00' - time)

datetimeをtimeにキャストする方法

cast("start_time" as time)

特定のカラムを対象として重複行を削除したい

enumにコメントをつけたい

各項目にはつけられないみたいなのでenum自体に列挙する CREATE TYPE my_type_description AS ENUM('foo desc', 'bar desc');

ひとつ前のレコードの前回値を取得する

ORDER BYでnull値を除外する

WHERE target_column IS NOT NULL ORDER BY target_column

target_columnに別名をつけているの場合はWHEREできないので

ORDER BY target_column desc NULLS LAST

MySQL

コマンド

mysql -uroot -p'password' -D database_name

Tips

バイナリを曖昧検索する

SELECT * FROM table WHERE data like BINARY CONCAT('%', 0x6671, '%')

TroubleShooting

phpmyadminでblobのバイナリフィールドを表示する方法

ActiveRecord::StatementInvalid (Mysql2::Error: Incorrect string value:というエラーが出る

文字コードが原因。

show table status;

をして、

latin1_swedish_ci とかを探す。

あったら、

ALTER TABLE my_table CONVERT TO CHARACTER SET utf8;

をしてUTF-8にする。