- 投稿日：2020-09-11T22:45:37+09:00
【Mac】PostgreSQL と MySQL (基礎)
【インストール -> 接続】
PostgreSQL
インストールから接続まで
インストール
- 何も指定しなければ最新版
$ brew install postgresql実行時下記のエラーが発生した場合
psql -l: error: could not connect to server: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?postgreSQLのアップグレードで解決
brew postgresql-upgrade-database接続
# バージョン確認 $ psql -V # DB一覧 $ psql -l # 接続 $ psql データベース名MySQL
インストールから接続まで
インストール
- 何も指定しなければ最新版
brew install mysql
バージョンを指定する
$ brew search mysql # brew searchでインストールできるバージョンを確認 ==> Formulae automysqlbackup mysql-client@5.7 mysql-search-replace mysql ✔ mysql-connector-c++ mysql@5.6 mysql++ mysql-connector-c++@1.1 mysql@5.7 mysql-client mysql-sandbox mysqltuner ==> Casks mysql-connector-python mysql-utilities navicat-for-mysql mysql-shell mysqlworkbench sqlpro-for-mysql $ brew install mysql @5.6 # 5.6を指定
バージョン確認
brew info mysql実行時下記のエラーが発生した場合
$ mysql.server start ERROR! The server quit without updating PID file (/usr/local/var/mysql/USER-no-MacBook-Air.local.pid).
/usr/local/var/mysqlを削除し再インストールで解決$ sudo rm -rf /usr/local/var/mysql $ brew uninstall mysql $ brew install mysql $ mysql.server start Starting MySQL .. SUCCESS!rootでログイン
$ mysql -u root
【コマンド/クエリ/コメント】
PostgreSQL
「
$」は外部で実行
「#」は内部で実行
動作 PostgreSQL ヘルプ $ psql --help
# \h
# \?バージョン確認 $ psql --version
$ psql -VDB一覧 $ psql --list
$ psql -l
# \lDB削除 $ drop データベース名DB接続 $ psql データベース名DB選択 # \c データベース名選択中のDB # select current_database();切断 # \q
#quit;テーブル一覧 # \dtテーブル詳細 # \d テーブル名テーブル削除 # drop table テーブル名
viewで使用中だと削除できないview一覧 # \dvユーザ一覧 # \duログインユーザー表示 # select session_user;
# select current_user;外部ファイル実行 # \i ファイル名コメント --(行末まで)
/* */(複数行)拡張表示(出力を縦表示) # \x
MySQL
「$」は外部で実行
「>」は内部で実行
動作 MySQL ヘルプ $ mysql --help
> h;バージョン確認 $ mysql --versionDB一覧 > show databases;
> show schemas
> show databases like '検索文字列'DB作成 > create database データベース名;DB削除 > drop database データベース名;DB接続 > mysql -u ユーザ名 -p
※ rootユーザの場合は-p不要DB選択 > use データベース名選択中のDB > select database();切断 > \q
> quit;
> exitテーブル一覧 > show tables;
> show tables like '検索文字列';テーブル詳細 > desc テーブル名;テーブル削除 > drop table テーブル名;view一覧 > show tables;view削除 > drop view名;CREATE VIEW ステートメント表示 > show create view view名;ユーザ一覧 > select Host, User from mysql.user;ログインユーザー表示 > select user();外部ファイル実行 $ mysql -u ユーザ名 < ファイル名(パス)
> source ファイル名(パス)
> \. ファイル名(パス)コメント #(行末まで)
--(行末まで)
/* */(複数行)拡張表示(出力を縦表示) > select * from テーブル名 \G;
※ クエリの末尾に\G
※mysql接続時に-Eオプションを指定DB存在確認 > create database if not exists データベース名;文字コード確認 > show variables like '%char%';警告確認 > show warnings;起動中のスレッド情報 > show processlist;コマンドキャンセル > \cユーザ作成 > create user ユーザ名@接続元 identified by 'パスワード';ユーザ削除 > drop user ユーザ名@接続元;接続 $ mysql -u ユーザ名 -p データベース名;ログインユーザー表示 > select user();権限付与 > grant 権限種類 on データベース名.対象のテーブル to ユーザ名@接続元;
権限レベル
レベル 概要 グローバルレベル(G) 全てのデータベースに適用
mysql.userテーブルに格納されるデータベースレベル(D) 特定のデータベース内の全てのオブジェクトに適用
mysql.dbテーブル内に格納されるテーブルレベル(T) 特定のテーブル内の全てのカラムに適用
mysql.tables_privテーブル内に格納れるカラムレベル(C) 特定テーブル内の単一カラムに適用
mysql.columns_privテーブル内に格納されるルーチンレベル(R) CREATE ROUTINE,ALTER ROUTINE,EXECUTE,GRANT OPTION権限はストアドルーチンに適用
グローバルとデータベースレベルで供与
CREATE ROUTINE以外は、各ルーチンに対してルーチンレベルで適用可能
mysql.procs_privテーブル内に格納されるプロキシユーザー権限(P) あるユーザーが別のユーザーの代理になることができる
mysql.proxies_privテーブル内に格納される
権限一覧
権限 レベル 意味 ALL [PRIVILEGES] GDT GRANT OPTION と PROXY 以外の全ての権限を設定 ALTER GDT テーブルの変更 ALTER ROUTINE GDR ストアドルーチンの変更・削除 CREATE GDT データベース、テーブルの作成 CREATE ROLE G ロールの作成 CREATE ROUTINE GD ストアドルーチンの作成 CREATE TABLESPACE G テーブルスペースとロググループの作成、変更、削除 CREATE TEMPORARY TABLES GD CREATE TEMPORARY TABLE の使用 CREATE USER G CREATE USER 、 DROP USER 、 RENAME USER 、REVOKE ALL PRIVILEGES の使用 CREATE VIEW GDT ビューの作成と変更 DELETE GDT データベース、テーブル、ビューの削除 DROP GDT テーブルの削除 DROP ROLE G ロールの削除 EVENT GD イベントスケジューラがイベントの利用 EXECUTE GDR ストアドルーチンの起動 FILE G SELECT ... INTO OUTFILE と LOAD DATA INFILE の使用 GRANT OPTION GDTRP 他のアカウントへの権限の付与とを削除 INDEX GDT インデックスの作成と削除 INSERT GDTC INSERTの使用 LOCK TABLES GD SELECT 権限を持つテーブル上の LOCK TABLES の使用 PROCESS G SHOW FULL PROCESSLIST の使用 PROXY -- PROXYの使用 REFERENCES GDTC 外部キーの作成 RELOAD G FLUSH の使用 REPLICATION CLIENT G スレーブとマスタの場所を問い合わせ REPLICATION SLAVE G 複製スレーブ (マスタからバイナリログイベントを読み込む為) SELECT GDTC SELECT の使用 SHOW DATABASES G SHOW DATABASES の使用 SHOW VIEW GDT SHOW CREATE VIEWの使用 SHUTDOWN G mysqladmin shutdown の使用 SUPER G CHANGE MASTER TO 、 KILL 、 PURGE BINARY LOGS 、 SET GLOBAL 、 mysqladmin debug command の使用 TRIGGER GDT トリガの作成と削除 UPDATE GDTC UPDATE の使用 USAGE G 「権限が無い」
SELECT権限ではグローバルレベル単位からカラム単位まで任意のレベルで設定可能。
すべてのデータベースに含まれるデータを取得できる権限を設定したり、特定のカラムだけデータを取得できるような権限を与えることができる。
サーバーとクライアントの文字コード
項目名 対象(client/server) 概要 character_set_client client クライアントがサーバにクエリを送信する際に使用 character_set_results server サーバがクライアントにクエリ結果を送る時に使用 character_set_connection client クライアントが送ったクエリをサーバが解析する際に使用 character_set_database server サーバのデフォルトキャラクタセット。LOAD DATA INFILE文はこの指定が使用される。mysql起動時にcharacter_set_serverの値が適用される。 character_set_server server サービス起動時のデフォルトキャラクタセット character_set_system - ファイル名をこのキャラクタセットで扱う character_sets_dir - キャラクタセットを扱う上で必須となるファイルを配置しているディレクトリ 拡張表示について
- 列が多く横長だと見にくい場合に便利
- 横か縦かの違い
拡張表示なしの場合
=# selsec * from users; /* 抽出結果 */ id | name | score | team ----+------------+-------+------- 1 | taguchi | 5.5 | red 2 | fkoji | 8.3 | blue 3 | dotinstall | 2.2 | red 4 | sasaki | 5 | green 5 | sasaki | 4.6 | red 6 | kimura | 4.7 | green (6 rows)
拡張表示ありの場合
=# \x Expanded display is on. =# select * from users; -[ RECORD 1 ]----- id | 1 name | taguchi score | 5.5 team | red -[ RECORD 2 ]----- id | 2 name | fkoji score | 8.3 team | blue -[ RECORD 3 ]----- id | 3 name | dotinstall score | 2.2 team | red . . . =# \x Expanded display is off.
【SQL評価順序】
実行されるクエリの順序
順序 句 動作 先 FROM対象のテーブルを指定 ↓ ONどの列で結合するかを指定 JOIN結合するテーブルを指定 WHERE対象のデータ絞り込み GROUP BY対象データでグループ化 関数集計 HAVINGグループ化した結果から絞り込み SELECT抽出する列を指定 DISTINCT抽出する列を指定 ORDER BY並べ替え 後 LIMIT抽出件数指定
SQL文作成順序
**⑦ SELECT 抽出する列, 関数 ⑧ DISTINCT 抽出する列 ① FROM 対象テーブル指定 ② JOIN 結合するテーブルを指定 ③ ON 結合する列を指定 ④ WHERE 対象データ絞り込み ⑤ GROUP BY グループ化 ⑥ HAVING グループ結果から絞り込み ⑨ ORDER BY 並べ替え ⑩ LIMIT 抽出件数指定;
【注意点】
HAVING句の条件によってSELECT句の指定が変わってくるためSELECT句の指定はHAVING句作成後LIMITが評価されるのは一番最後なので取得件数を少なくしても処理が速くなるわけではない。LIMITで取得件数を絞って速く動作するのは以下の場合に限る
ORDER BYの指定が無いORDER BYで指定されたカラムがインデックスに定義されているWHERE句での絞り込みかインデックスを作成しないと速くならないWHERE句はSELECT句より先に評価されるためSELECT句でASを使ってつけられた別名は使用できない- クエリを速くするには、早い段階で取得するデータ量を少なくする
【テーブル操作】
テーブル作成
PostgreSQL / MySQL
書式create table テーブル名 (カラム名 型（）, カラム名 型());例create table users(name varchar(255), age integer);
テーブル名変更
PostgreSQL
書式alter table 現テーブル名 rename to 新テーブル名;例alter table users rename to employees;
MySQL
書式rename table 現テーブル名 to 新テーブル名;例rename table users rename to employees;
テーブル削除
PostgreSQL / MySQL
書式drop table テーブル名;例drop table users;
データ型
PostgreSQL / MySQL
型 PostgresSQL MySQL 文字(固定長) char(5)char(5)文字(可変長) varchar(255)
textvarchar(255)
text数値(整数) integerまたはintint(正の数と負の数)
int unsigned(正の数のみにしその分扱える範囲を広く)数値(小数点) realfloat
double数値(連番) serial真偽値 boolean(値:true,false,t,f)boolean
別名 ->tinyint(1)
内部的にはtrue->1
false->0となる日付(日付のみ) datedate日付(時間のみ) timetime日付(日付+時間) timestampdatetime
2020-01-01 12:00:00
列挙型
enum
- 複数定義された中から1つ選択
- 内部で割り当てられている数値でinsertやupdateでも有効
MySQL
create table users ( id int unsigned primary key auto_increment, ranks enum('gold', 'silver', 'bronze') -- 列挙型, 内部的には1, 2, 3と連番で数値が割り当てられている ); insert into users (ranks) values ('silver'); insert into users (ranks) values ('gold'); insert into users (ranks) values ('bronze'); insert into users (ranks) values ('red'); -- NULLが入る insert into users (ranks) values (1); insert into users (ranks) values (2); insert into users (ranks) values (3); insert into users (ranks) values (4); -- NULLが入る select * from users where ranks = 'gold'; +----+-------+-------+-------+ | id | name | score | ranks | +----+-------+-------+-------+ | 2 | fkoji | 8.2 | gold | +----+-------+-------+-------+ 1 row in set (0.00 sec) select * from users where ranks = 1; +----+-------+-------+-------+ | id | name | score | ranks | +----+-------+-------+-------+ | 2 | fkoji | 8.2 | gold | +----+-------+-------+-------+ 1 row in set (0.00 sec)
set
- 複数定義された中から1つ選択
- 内部的には1番目の要素には「2の0乗」の数値, 2番目の要素には「2の1乗」の数値, 3番目の要素には「2の2乗」の数値...が割り当てられる
- 1番目と2番目の要素を指定したい場合は「1 + 2」で「３」を、3番目と4番目は「4 + 8」で12を指定する
- 内部で割り当てられている数値でinsertやupdateでも有効
MySQL
create table users ( id int unsigned primary key auto_increment, coins set('gold', 'silver', 'bronze') -- [1番目-> 2の0乗(1), 2番目-> 2の1乗(2), 3番目-> 2の2乗(4)] ); insert into users (coins) values ('gold'); -- (1)でもよい insert into users (coins) values ('silver'); -- (2)でもよい insert into users (coins) values ('bronze'); -- (4)でもよい insert into users (coins) values ('gold,silver'); -- (3)でもよい 1 + 2 insert into users (coins) values ('gold,bronze'); -- (5)でもよい 1 + 4 insert into users (coins) values ('silver,bronze'); -- (6)でもよい 2 + 4 insert into users (coins) values ('silver,bronze,bronze'); -- (7)でもよい 1 + 2 + 4 select * from users where coins = 'gold,silver'; -- gold,silver select * from users where coins like '%gold%'; +----+--------------------+ | id | coins | +----+--------------------+ | 1 | gold | | 4 | gold,silver | | 5 | gold,bronze | | 7 | gold,silver,bronze | +----+--------------------+ 4 rows in set (0.00 sec) select * from users where coins = 1; -- gold select * from users where coins = 2; -- sliver select * from users where coins = 3; -- gold,silver select * from users where coins = 4; -- bronze select * from users where coins = 5; -- gold,bronze select * from users where coins = 6; -- silver,bronze select * from users where coins = 7; -- gold,silver,bronze select * from users where coins = 8; -- Empty```
制約
PostgreSQL / MySQL
制約 PostgresSQL MySQL 入力必須 not nullnot null重複不可 uniqueunique入力チェック check初期値 defalutdefalut主キー primary keyprimary keypostgreSQL
postgresql.sqlcreate table posts ( id serial primary key, title varchar(255) not null, body text check(length(body) > 5), is_draft boolean detaul TRUE, created timestamp default statement_timestamp() ); /* 制約 not null 入力必須 unique 重複不可 check defalut デフォルト値 primary key (not null + unique)テーブルに１つだけ設定可能 */ /* statement_timestamp() -> レコード挿入日時 */MySQL
mysql.sqlcreate table users ( id int unsigned primary key auto_increment, name varchar(20) unique, score float default 0.0 );
抽出結果でテーブル作成
PostgreSQL / MySQL
書式create table 作成するテーブル名 as 問い合わせ;create table users_with_team as select name, score, case when score > 8.0 then 'Team-A' when score > 6.0 then 'Team-B' else 'Team-C' end as Team from users; desc users_with_team; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | score | float | YES | | NULL | | | Team | varchar(6) | NO | | | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
テーブルコピー
PostgreSQL
likeでコピー
- テーブルの構造と制約を引き継ぐ
- レコードは引き継がない
書式create table 作成するテーブル名 (like コピー元テーブル名 including all);例create table users_copy (like users including all); Column | Type | Collation | Nullable | Default --------+------------------------+-----------+----------+----------------------------------- id | integer | | not null | nextval('users_id_seq'::regclass) name | character varying(255) | | not null | score | real | | | 0 team | character varying(255) | | | Indexes: "users_copy_pkey" PRIMARY KEY, btree (id)
抽出結果でコピー
- テーブルの構造とレコードを引き継ぐ
- 制約は引き継がない
書式create table 作成するテーブル名 as 問い合わせ;例create table users_copy as select * from users; Column | Type | Collation | Nullable | Default --------+------------------------+-----------+----------+--------- id | integer | | | name | character varying(255) | | | score | real | | | team | character varying(255) | | |
MySQL
likeでコピー
- テーブルの構造だけコピー(レコードはコピーされない)
likeを使うPrimary keyやインデックスを引き継ぐ書式create table 作成するテーブル名 like コピー元テーブル名;例create table users_empty like users; desc users_empty; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | score | float | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
抽出結果でコピー
- テーブルの構造とレコードをコピー
制約はコピーされない
primary key、インデックスは引き継がれないaute increamentはdetaulf 0に置き換わる書式create table 作成するテーブル名 抽出問い合わせ;例create table users_copy select * from users; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int unsigned | NO | | 0 | | | name | varchar(20) | YES | | NULL | | | score | float | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
【テーブルの構造を変更】
PostgreSQL / MySQL
インデックスについて
postgresql/* フィールド追加 */ alter table テーブル名 add カラム名 型; alter table users add fullname varchar(255); /* フィールド削除 */ alter table テーブル名 drop カラム名; alter table users drop カラム名; /* フィールド名変更 */ alter table テーブル名 rename 元の名前 to 新しい名前; alter table users rename name to myname; /* フィールド型変更 */ alter table テーブル名 alter カラム名 type 型; alter table users alter myname type varchar(32); / * インデックス追加 */ create index インデックス名 on テーブル名(カラム名); create index team_index on users(team); / * インデックス削除 */ drop index インデックス名; drop index team_index;
mysql/*カラム追加*/ alter table テーブル名 add column カラム名 型 after カラム名; alter table users add column email varchar(255) after name; /* カラムnameの後にカラムemailを追加 */ /*カラム削除*/ alter table テーブル名 drop column カラム名; alter table users drop column score; /*カラム変更*/ alter table テーブル名 change 旧カラム名 新カラム名 型 制約; alter table users change name user_name varchar(80) default 'no-name'; /*テーブル名変更*/ alter table 旧テーブル名 rename 新テーブル名; alter table users rename persons; /* インデックス追加 */ alter table テーブル名 add index インデックス名 (インデックスを付与するカラム名); alter table users add index index_socre (score); /* インデックス削除 */ alter table テーブル名 drop index インデックス名; alter table users drop index index_score; /* インデックス確認 */ show index from テーブル名; show index from users;
インデックスについて
primary keyに指定されたカラムは自動的にインデックスされる。- インデックスの追加は、検索のときは早くなるがレコードの挿入、更新時に新しいインデックスを作るので遅くなるためバランスを考える。
列の並び替え
PostgreSQLでは、コマンドレベルでの列の並び替えは不可能。
方法としては以下で対応可能。
- 列を並び替えた状態のテーブルを新しく作成しする
- データを更新し古いテーブルを削除
【INSERT】
PostgreSQL
書式insert into テーブル名(カラム名, カラム名...) values (値, 値...), (値, 値...)...;例insert into users(name, age) values ( ('tanaka', 20), ('suzuki', 30), ('sato', 40) );
MySQL
書式insert into テーブル名(カラム名, カラム名...) values (値, 値...), (値, 値...)...;例insert into users(id, name, score) values (1, 'tanaka', 20), (2, 'suzuki', 30), (3, 'sato', 40);
last_insert_id
MySQL
- 直前に挿入されたレコードの
IDを調べる前提
drop table if exists users; drop table if exists posts; create table users ( id int unsigned primary key auto_increment, name varchar(255) ); create table posts ( id int unsigned primary key auto_increment, user_id int unsigned not null ); alter table posts add constraint fk_posts foreign key (user_id) references users (id); insert into users (name) values ("a"); insert into users (name) values ("b"); insert into users (name) values ("c"); insert into posts (user_id) values (1); insert into posts (user_id) values (1); insert into posts (user_id) values (3); delete from users where id = 2; insert into posts(user_id) values (last_insert_id()); -- user_id = 3のレコードが作成される select * from users; select * from posts; alter table posts drop foreign key fk_posts; +----+------+ | id | name | +----+------+ | 1 | a | | 3 | c | +----+------+ 2 rows in set (0.00 sec) +----+---------+ | id | user_id | +----+---------+ | 1 | 1 | | 2 | 1 | | 3 | 3 | | 4 | 3 | +----+---------+ 4 rows in set (0.01 sec)挿入/更新時刻でレコードを作成
PostgreSQL / MySQL
default current_timestampで現在時刻を挿入default current_time on update current_timestampでレコード更新時、更新時刻で更新するdrop table if exists users; create table users ( id int unsigned primary key auto_increment, name varchar(255), created_at datetime default current_timestamp, updated_at datetime default current_timestamp on update current_timestamp ); insert into users (name) values ('name1'); insert into users (name) values ('name2'); insert into users (name) values ('name3'); select * from users; update users set name = 'update name' where id = 2; do sleep(5); select * from users; +----+-------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+-------+---------------------+---------------------+ | 1 | name1 | 2020-09-17 21:16:12 | 2020-09-17 21:16:12 | | 2 | name2 | 2020-09-17 21:16:12 | 2020-09-17 21:16:12 | | 3 | name3 | 2020-09-17 21:16:12 | 2020-09-17 21:16:12 | +----+-------+---------------------+---------------------+ 3 rows in set (0.00 sec) Query OK, 0 rows affected (5.01 sec) . . . +----+-------------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+-------------+---------------------+---------------------+ | 1 | name1 | 2020-09-17 21:16:12 | 2020-09-17 21:16:12 | | 2 | update name | 2020-09-17 21:16:12 | 2020-09-17 21:16:17 | -- レコード更新時刻で更新される | 3 | name3 | 2020-09-17 21:16:12 | 2020-09-17 21:16:12 | +----+-------------+---------------------+---------------------+ 3 rows in set (0.00 sec)
【SELECT】
PostgreSQL / MySQL
書式select 抽出するカラム from テーブル名;例select * from users; select name, score form users;
抽出時並び替え
PostegreSQL / MySQL
動作 書式 降順 order by カラム名 desc;昇順 order by カラム名;
order by カラム名 asc;抽出するレコード数を操作 limit 抽出数 offset n番目から;/* 昇順 */ select * from users order by score; select * from users order by score asc; /* 降順 */ select * from users order by score desc; /* n番目からnつ */ select * from users limit 3 offset 3; /* scoreトップランキング3 */ select * from users order by score limit 3; /* scoreトップランキング4~6 */ select * from users order by score limit 3 offset 3; /* scoreがnullのレコード */ select * from users where score is null; /* scoreがnullではないレコード */ select * from users where score is not null;
抽出時使用されるインデックスを確認
explain 抽出文;
PostgreSQL
# explain select * from users where id > 1; QUERY PLAN ---------------------------------------------------------- Seq Scan on users (cost=0.00..10.88 rows=23 width=1040) Filter: (id > 1) (2 rows)
MySQL
> explain select * from users where score > 5.0; +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | users | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | | users | 1 | index_score | 1 | score | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 2 rows in set (0.00 sec)
【WHERE】
PostgreSQL / MySQL
書式select カラム名 from テーブル名 where 条件;例select * from users where score > 1; select * from users where score > 2.0 and team = 'red'; select * from users where team = 'red' or team = 'blue';比較演算子
演算子 意味 >~より大きい <~より小さい >=~以上 <=〜以下 =等しい <>等しくない !=等しくない 論理演算子
-- and select * from users where score >= 3.0 and score <= 6.0 ; select * from users where score between 3.0 and 6.0 ; -- or select * from users where name = 'taguchi' or name = 'fkoji'; select * from users where name in ('taguchi', 'fkoji');like検索/ワイルドカード
PostgreSQL / MySQL
- デフォルト
PostgreSQLは大文字小文字を区別するMySQLは大文字小文字を区別しない
ワイルドカード 意味 _任意の1文字 %0文字以上の任意の文字列 a%aで始まる文字列a%aで終わる文字列%a%aを含む文字列PostgreSQL: 大文字小文字を区別しない検索
select * from users where name Ilike 'A%'; -- 大文字のAで始まるレコードのみ抽出MySQL: 大文字小文字を区別した検索
select * from users where name like binary 'A%'; -- 大文字のAで始まるレコードのみ抽出
【レコード集計】
四則演算
PostgreSQL / MySQL
演算子 意味 +加算 -減算 *乗算 /除算 %剰余算 /* idが偶数のレコードを取得 */ select * from users where id % 2 = 0; /* idが偶数のレコードのscoreを1.2倍 */ update users set score = score * 1.2 where id % 2 = 0;
集計関数
PostgreSQL / MySQL
関数 動作 countレコード数 distinctデータの種類 sum合計 avg平均 max最大値 min最小値 select count(score) from users; select count(id) from users; select count(*) from users; select sum(score) from users; select min(score) from users; select max(score) from users; select avg(score) from users; select distinct team from users; select count(distinct team) from users; -- チームの種類数
group by
PostgreSQL / MySQL
select team, sum(score) from users group by team; /* 抽出結果 */ team | sum -------+----------- blue | 8.3 red | 12.299999 green | 9.7
HAVING句
- 集計後のデータに対しての絞り込み
WHERE句とHAVING句の違い
句 概要 WHERE句 SELECT句の結果から絞り込みを行う HAVING句 group byで集計した結果から絞り込みを行う/* チーム毎の合計が10.0以上のレコード */ select team, sum(score) from users group by team having sum(score) > 10.0;/* whereで絞り込みを行った後のレコードに対してgroup byを行う */ select sum(score), team from users_with_team where id > 3 group by team;日時の計算
date_add()date_format()
MySQL
drop table if exists users; create table users ( id int unsigned primary key auto_increment, name varchar(255), created_at datetime default current_timestamp, updated_at datetime default current_timestamp on update current_timestamp ); insert into users (name) values ('name1'); insert into users (name) values ('name2'); insert into users (name) values ('name3'); update users set created_at = '2019-09-17 10:00:00' where id = 2; -- 日付を更新 select * from users; +----+-------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+-------+---------------------+---------------------+ | 1 | name1 | 2020-09-17 21:35:37 | 2020-09-17 21:35:37 | | 2 | name2 | 2019-09-17 10:00:00 | 2020-09-17 21:35:37 | | 3 | name3 | 2020-09-17 21:35:37 | 2020-09-17 21:35:37 | +----+-------+---------------------+---------------------+ 3 rows in set (0.00 sec) select * from users where created_at > '2019-12-31'; -- 条件に日付を指定 +----+-------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+-------+---------------------+---------------------+ | 1 | name1 | 2020-09-17 21:35:37 | 2020-09-17 21:35:37 | | 3 | name3 | 2020-09-17 21:35:37 | 2020-09-17 21:35:37 | +----+-------+---------------------+---------------------+ 2 rows in set (0.00 sec) select created_at, date_add(created_at, interval 21 day) from users; -- 21日後 +---------------------+---------------------------------------+ | created_at | date_add(created_at, interval 21 day) | +---------------------+---------------------------------------+ | 2020-09-17 21:35:37 | 2020-10-08 21:35:37 | | 2019-09-17 10:00:00 | 2019-10-08 10:00:00 | | 2020-09-17 21:35:37 | 2020-10-08 21:35:37 | +---------------------+---------------------------------------+ 3 rows in set (0.00 sec) select created_at, date_add(created_at, interval 3 week) from users; -- 3週間後 +---------------------+---------------------------------------+ | created_at | date_add(created_at, interval 3 week) | +---------------------+---------------------------------------+ | 2020-09-17 21:35:37 | 2020-10-08 21:35:37 | | 2019-09-17 10:00:00 | 2019-10-08 10:00:00 | | 2020-09-17 21:35:37 | 2020-10-08 21:35:37 | +---------------------+---------------------------------------+ 3 rows in set (0.00 sec) select created_at, date_format(created_at, '%W %M %Y') from users; -- 抽出時のフォーマットを指定 +---------------------+-------------------------------------+ | created_at | date_format(created_at, '%W %M %Y') | +---------------------+-------------------------------------+ | 2020-09-17 21:35:37 | Thursday September 2020 | | 2019-09-17 10:00:00 | Tuesday September 2019 | | 2020-09-17 21:35:37 | Thursday September 2020 | +---------------------+-------------------------------------+ 3 rows in set (0.00 sec)
【関数】
PostgreSQL
length(長さ)
select name, length(name) from users; /* 抽出結果 */ name | length ------------+-------- taguchi | 7 fkoji | 5 dotinstall | 10 sasaki | 6 sasaki | 6 kimura | 6 (6 rows)
concat(文字列連結)
select concat(name, '(', team, ')') as from users; /* 結果 */ concat ----------------- taguchi(red) fkoji(blue) dotinstall(red) sasaki(green) sasaki(red) kimura(green) (6 rows)
as(別名)
blogapp=# select concat(name, '(', team, ')') as nameteam from users; nameteam ----------------- taguchi(red) fkoji(blue) dotinstall(red) sasaki(green) sasaki(red) kimura(green) (6 rows)
substring(n文字目からm文字取り出す)
blogapp=# select substring(team, 1, 1) as teaminitiale from users; /* 結果 */ teaminitiale -------------- r b r g r g (6 rows)
random(ランダム抽出)
blogapp=# select * from users order by random() limit 1; /* 結果 */ id | name | score | team ----+-------+-------+------ 2 | fkoji | 8.3 | blue (1 row)
MySQL
round()(小数点切り捨て)
select round(5.355); -- 5 select round(5.355, 1); -- 5.4 第2引数に表示する桁数を指定
floor()(小数点切り捨て)
select floor(5.833); -- 5 select floor(5.238); -- 6
rand()(乱数)
select rand(); -- 乱数 select * from users order by rand() limit 1; -- ランダムで一件取得
length()(長さ)
select length('Hello'); -- 5
substring()(文字列の一部を取得)
select substring('Hello', 2); -- ello select substring('Hello', 2, 3); -- ell
upper()(大文字に変換)
select upper('Hello'); -- HELLO
lower()(小文字に変換)
select lower('Hello'); -- hello
concat()(文字列連結)
select concat('Hello', 'World'); -- HelloWorld
as(別名)
select length(name) as len, name from users order by len; +------+------------+ | len | name | +------+------------+ | 5 | fkoji | | 6 | Tanaka | | 6 | yamada | | 7 | taguchi | | 7 | tashiro | | 10 | dotinstall | +------+------------+ 6 rows in set (0.00 sec)
【条件分岐】
if文
MySQL
mysql.sqldrop table if exists users; -- usersテーブルがあればusersテーブルを削除select name, score, if (score > 5.0, 'OK', 'NG') as result from users; +------------+-------+--------+ | name | score | result | +------------+-------+--------+ | taguchi | 5.8 | OK | | fkoji | 8.2 | OK | | dotinstall | 6.1 | OK | | Tanaka | 4.2 | NG | | yamada | NULL | NG | | tashiro | 7.9 | OK | +------------+-------+--------+ 6 rows in set (0.00 sec)
case文
MySQL
whenに値を指定
select name, score, case floor(score) % 2 when 0 then 'even' when 1 then 'odd' else null end as type from users; +------------+-------+------+ | name | score | type | +------------+-------+------+ | taguchi | 5.8 | odd | | fkoji | 8.2 | even | | dotinstall | 6.1 | even | | Tanaka | 4.2 | even | | yamada | NULL | NULL | | tashiro | 7.9 | odd | +------------+-------+------+ 6 rows in set (0.00 sec)whenに条件thenに返却値を指定
select name, score, case when score > 8.0 then 'Team-A' when score > 6.0 then 'Team-B' else 'Team-C' end as Team from users; +------------+-------+--------+ | name | score | Team | +------------+-------+--------+ | taguchi | 5.8 | Team-C | | fkoji | 8.2 | Team-A | | dotinstall | 6.1 | Team-B | | Tanaka | 4.2 | Team-C | | yamada | NULL | Team-C | | tashiro | 7.9 | Team-B | +------------+-------+--------+ 6 rows in set (0.00 sec)
【UPDATE】
PostgreSQL / MySQL
update users set score = 5.8 where name = 'taguchi'; update users set score = score + 1 where team = 'red'; update users set name = 'sasaki, 'score = score + 1 where name = 'taguchi';
【DELETE】
PostgreSQL / MySQL
/* usersテーブル全件削除 */ delete from users; /* 条件を満たすレコードを削除 */ delete from users where score < 4.0; DELETE 1
【複数のテーブルを扱う】
PostgreSQL / MySQL
書式select テーブルA.カラム名, テーブルB.カラム名 from テーブルA, テーブルB where テーブルサーバー全体のゲーム情報を削除すると退会.カラム名 = テーブルB.カラム名; /* where句では、テーブルAとテーブルBのどのカラムが同一とするかを指定しテーブル同士の結びつきを作成する */例select users.name, posts.title from users, posts where users.id = posts.user_id; /* テーブルusersの「id」とテーブルpostsの「user_id」が同一のものと指定しusersとpostsの結びつけている */例/* from句でテーブルに別名を付け文を短縮する */ /* from句が一番最初に実行されるので別名はfrom句で */ select u.name, p.title from users u, posts p where u.id = p.user_id;/* andを使用し条件を足す */ select u.name, p.title from users u, posts p where u.id = p.user_id and u.id = 1;
【viewを使う】
view作成
PostgreSQL / MySQL
create view view名 as SELECT文;create view top3 as select * from users order by score desc limit 3;
view使用
PostgreSQL / MySQL
select * from view名;
view削除
PostgreSQL / MySQL
drop view view名;drop view taguchi_posts;
【トランザクション】
PostgreSQL
書式begin; -- 開始 /* 処理 */ rollback; -- 取り消し commit; -- 完了
MySQL
書式start transaction; -- 開始 /* 処理 */ rollback; -- 取り消し commit; -- 完了
【サブクエリ】
from句で抽出結果を使用する
PostgreSQL / MySQL
select sum(t.score), t.team from (select id, name, score, -- ここからサブクエリ case when score > 8.0 then 'Team-A' when score > 6.0 then 'Team-B' else 'Team-C' end as Team from users) as t -- ここまで。 group by t.team; +-------------------+--------+ | sum(t.score) | Team | +-------------------+--------+ | 10 | Team-C | | 8.199999809265137 | Team-A | | 14 | Team-B | +-------------------+--------+ 3 rows in set (0.00 sec)【テーブルの結合】
前提
select * from posts; +----+---------+--------+ | id | title | body | +----+---------+--------+ | 1 | title 1 | body 1 | | 2 | title 2 | body 2 | | 3 | title 3 | body 3 | +----+---------+--------+ select * from comments; +----+---------+---------------------------+ | id | post_id | body | +----+---------+---------------------------+ | 1 | 1 | first comment for post 1 | | 2 | 1 | second comment for post 1 | | 3 | 3 | first comment for post 3 | | 4 | 4 | first comment for post 4 | +----+---------+---------------------------+
inner join
- テーブル同士共通のデータだけを取得する
MySQL
書式select 取得するカラム from テーブルA inner join テーブルB on テーブルA.カラム名 = テーブルB.カラム名; select 取得するカラム from テーブルA join テーブルB on テーブルA.カラム名 = テーブルB.カラム名; -- innerは省略可例select * from posts join comments on posts.id = comments.post_id; +----+---------+--------+----+---------+---------------------------+ | id | title | body | id | post_id | body | +----+---------+--------+----+---------+---------------------------+ | 1 | title 1 | body 1 | 1 | 1 | first comment for post 1 | | 1 | title 1 | body 1 | 2 | 1 | second comment for post 1 | | 3 | title 3 | body 3 | 3 | 3 | first comment for post 3 | +----+---------+--------+----+---------+---------------------------+ 3 rows in set (0.00 sec)例-- 取得するカラムを記述する際、from句で指定した軸になるテーブルのテーブル名は省略できる select posts.id, title, body as posts_body, comments.body as commnets_body from posts join comments on posts.id = comments.post_id; +----+---------+------------+---------------------------+ | id | title | posts_body | commnets_body | +----+---------+------------+---------------------------+ | 1 | title 1 | body 1 | first comment for post 1 | | 1 | title 1 | body 1 | second comment for post 1 | | 3 | title 3 | body 3 | first comment for post 3 | +----+---------+------------+---------------------------+```
outer join
- 片方にしかレコードがない場合でも抽出する
MySQL
書式/* 軸になる(左側)テーブルを基準にする */ select 取得するカラム名 from テーブルA left outer join テーブルB on テーブルA.結びつけるカラム名 = テーブルB.結びつけるカラム名; select 取得するカラム名 from テーブルA left join テーブルB on テーブルA.結びつけるカラム名 = テーブルB.結びつけるカラム名; -- outerは省略可 /* 結合する(右側)テーブルを基準にする */ select 取得するカラム名 from テーブルA right outer join テーブルB on テーブルA.結びつけるカラム名 = テーブルB.結びつけるカラム名; select 取得するカラム名 from テーブルA right join テーブルB on テーブルA.結びつけるカラム名 = テーブルB.結びつけるカラム名; -- outerは省略可例/* 左側のpostsを軸に外部結合 */ /* commentsテーブルにはpost_id = 2のデータは存在しないためNULL */ select * from posts left join comments on posts.id = comments.post_id; +----+---------+--------+------+---------+---------------------------+ | id | title | body | id | post_id | body | +----+---------+--------+------+---------+---------------------------+ | 1 | title 1 | body 1 | 1 | 1 | first comment for post 1 | | 1 | title 1 | body 1 | 2 | 1 | second comment for post 1 | | 2 | title 2 | body 2 | NULL | NULL | NULL | | 3 | title 3 | body 3 | 3 | 3 | first comment for post 3 | +----+---------+--------+------+---------+---------------------------+ 4 rows in set (0.01 sec)例/* 左側のcommentsを軸に外部結合 */ /* postsテーブルにはid = 4のデータは存在しないためNULL */ select * from posts right join comments on posts.id = comments.post_id; +------+---------+--------+----+---------+---------------------------+ | id | title | body | id | post_id | body | +------+---------+--------+----+---------+---------------------------+ | 1 | title 1 | body 1 | 1 | 1 | first comment for post 1 | | 1 | title 1 | body 1 | 2 | 1 | second comment for post 1 | | 3 | title 3 | body 3 | 3 | 3 | first comment for post 3 | | NULL | NULL | NULL | 4 | 4 | first comment for post 4 | +------+---------+--------+----+---------+---------------------------+ 4 rows in set (0.00 sec)【外部キー制約】
外部キー制約を追加
- 外部キー制約を付けるカラムは、紐づくカラムと同じ型にする必要がある
- 紐づくレコードが存在しない場合レコードを挿入できない
- カラムに外部キー制約が付いている場合
deleteで削除できなくなる
MySQL
前提create table users ( id int unsigned primary key auto_increment, name varchar(255) ); create table posts ( id int unsigned primary key auto_increment, user_id int unsigned not null );書式/* カラムAはテーブルAに存在するカラムBと関連づけるカラム */ /* テーブルBは関連づけるテーブル名 */ /* カラムBは関連づけるカラム名 */ alter table テーブルA add constraint キー名 foreign key (カラムA) references テーブルB (カラムB);例alter table posts add constraint fk_posts foreign key (user_id) references users (id);
外部キー制約を削除
MySQL
書式alter table テーブル名 drop foreign key キー名;例alter table posts drop foreign key fk_posts;【トリガー】
- トリガー起動時に実行されるSQL文において、
old.カラム名とNEW.カラム名を使って参照可能。- トリガー操作が
INSERTの場合はNEW.カラム名で新しく追加されたデータの値を参照できる。- トリガー操作が
DELETEの場合はOLD.カラム名で削除されたデータの値を参照できる。- トリガー操作操作が
UPDATEの場合はOLD.カラム名とNEW.カラム名の両方が使用でき更新される前の値と更新された後の値を参照できる。トリガー作成
MySQL
書式/* トリガーが起動する操作は{ insert | update | delete }の3種類 */ /* { before | after }でトリガーの発火を操作前か操作後に設定 */ create trigger トリガー名 { before | after } { insert | update | delete } on テーブルA for each row insert into テーブルB (カラム名) values (値);例create trigger posts_insert_trigger after insert on posts for each row insert into logs (msg) values ('post added!');例２drop table if exists users; create table users ( id int unsigned primary key auto_increment, name varchar(255) ); drop table if exists logs; create table logs ( id int unsigned primary key auto_increment, msg text ); drop trigger if exists users_update_logs; delimiter // -- 文の区切り文字を変更 create trigger users_update_logs after update on users for each row begin insert into logs (msg) values ('users updated'); insert into logs (msg) values (concat(old.name, '->', new.name)); -- old, newは関数 end; // -- 文の終端 delimiter ; -- 文の区切り文字を戻す insert into users (name) values ('name1'); insert into users (name) values ('name2'); insert into users (name) values ('name3'); update users set name = 'new' where id = 2; -- トリガー発火 select * from users; select * from logs; +----+-------+ | id | name | +----+-------+ | 1 | name1 | | 2 | new | | 3 | name3 | +----+-------+ 3 rows in set (0.00 sec) +----+---------------+ | id | msg | +----+---------------+ | 1 | users updated | | 2 | name2->new | +----+---------------+ 2 rows in set (0.00 sec)
トリガー一覧
MySQL
/* \Gは拡張表示オプション */ show triggers \G;
トリガー削除
MySQL
書式drop trigger トリガー名;例drop trigger posts_insert_trigger;
【バックアップ / リストア】
MySQL
mysqldump(バックアップ作成)- \. ./バックアップファイル名(リストア)
dump$ mysqldump -u ユーザ名 -p データベース名 > ファイル名リストア$ \. ./バックアップファイル名
- 投稿日：2020-09-11T22:07:34+09:00
Herokuに入れたPostgreSQLの並び順を変更する
背景
Djangoに関して。データベースに入っている投稿を作成順(ID順)に並べる際に、ローカル環境ではデフォルトで入っているMySQLがID順で格納されていくため、特に苦労することはなかった。
しかしHerokuでデプロイした後はMySQLが使えないので、PostgreSQLを使うことになる。PostgreSQLのデータの並び順はデフォルトではID順ではないため(更新順?いまだにどう並んでいるか分かりません)、自分で設定する必要がある。
対応策
データベースの中に入ってそこでORDER_BY句を設定することも考えたが(おそらくこれが王道)、慣れていないためより簡単なDjangoのviews.py上に一つコードを加える対応をとった。
ordering = ['id']を入れることで表示上は問題なくなる。views.pyclass DataList(ListView): template_name = "list.html" model = DataModel ordering = ['-id']orderingについて
なお
''で囲まれた文字列はデータベースのフィールドの名前を書ける。
また降順に並べる場合はオプションで'-'を先頭に付ける。
順番をランダムにするには"?"で対応可能。参考
Djangoのソートがあいうえお順にならない時の対処法
Django v1.0 documentation モデルのMetaオプション
- 投稿日：2020-09-11T14:39:43+09:00
Dockerのコンテナ内のmysqlの日本語設定
はじめに
Rails初学者ですが、docker＋mysqlで開発したい！と思いdockerでrailsのプログラミングをしています。
イロイロ試行錯誤しながら、dockerコンテナを作っては消し、作っては消しを繰り返ししています。
（めちゃくちゃ面倒）
その都度、dockerコンテナの日本語設定をしているので、改めてまとめてみました。＊この設定を行わないと、日本語が正しく表示されず????になります。
＊多分都度都度でやらなくてもイロイロ方法があるんだと思う・・・ご教示いただければ幸いです。＊以下のqiitaを全面的に参考にさせていただいております。ありがとうございます。
https://qiita.com/maejima_f/items/4d5432aa471a9deaea7fDockerコンテナに入る
$ docker exec -it コンテナ名 bashコンテナ内でlocaleの設定を確認する
root@XXXX :/# locale LANG= LANGUAGE= LC_CTYPE="POSIX" LC_NUMERIC="POSIX" LC_TIME="POSIX" LC_COLLATE="POSIX" LC_MONETARY="POSIX" LC_MESSAGES="POSIX" LC_PAPER="POSIX" LC_NAME="POSIX" LC_ADDRESS="POSIX" LC_TELEPHONE="POSIX" LC_MEASUREMENT="POSIX" LC_IDENTIFICATION="POSIX" LC_ALL=このlocaleの情報を日本かつUTF-8に変更するところからスタートです。
その前にコンテナ内をアップデートしたり、vimを入れ準備します。root@XXXX :/# apt-get update root@XXXX :/# apt-get install vim＊どちらも途中でインストールしますか？がでてきますのでyを入力し、コンテニューさせます。
2つを行ったあと、以下の設定を行います。
1.言語設定
＊何度かenterを押しているとLocales to be generated:が出てくるので
日本語の285. ja_JP.UTF-8 UTF-8 を入力しenterを押す（285は変わる可能性があります）root@XXXX :/# dpkg-reconfigure locales ------------------- Locales are a framework to switch between multiple languages and allow users to use their language, country, characters, collation order, etc. Please choose which locales to generate. UTF-8 locales should be chosen by default, particularly for new installations. Other character sets may be useful for backwards compatibility with older systems and software. 1. All locales 125. el_GR ISO-8859-7 249. gl_ES ISO-8859-1 373. pl_PL ISO-8859-2 2. aa_DJ ISO-8859-1 126. el_GR.UTF-8 UTF-8 250. gl_ES.UTF-8 UTF-8 374. pl_PL.UTF-8 UTF-8 3. aa_DJ.UTF-8 UTF-8 127. el_GR@euro ISO-8859-7 251. gl_ES@euro ISO-8859-15 375. ps_AF UTF-8 4. aa_ER UTF-8 128. en_AG UTF-8 252. gu_IN UTF-8 376. pt_BR ISO-8859-1 5. aa_ER@saaho UTF-8 129. en_AU ISO-8859-1 253. gv_GB ISO-8859-1 377. pt_BR.UTF-8 UTF-8 6. aa_ET UTF-8 130. en_AU.UTF-8 UTF-8 254. gv_GB.UTF-8 UTF-8 378. pt_PT ISO-8859-1 7. af_ZA ISO-8859-1 131. en_BW ISO-8859-1 255. ha_NG UTF-8 379. pt_PT.UTF-8 UTF-8 8. af_ZA.UTF-8 UTF-8 132. en_BW.UTF-8 UTF-8 256. hak_TW UTF-8 380. pt_PT@euro ISO-8859-15 9. agr_PE UTF-8 133. en_CA ISO-8859-1 257. he_IL ISO-8859-8 381. quz_PE UTF-8 10. ak_GH UTF-8 134. en_CA.UTF-8 UTF-8 258. he_IL.UTF-8 UTF-8 382. raj_IN UTF-8 11. am_ET UTF-8 135. en_DK ISO-8859-1 259. hi_IN UTF-8 383. ro_RO ISO-8859-2 12. an_ES ISO-8859-15 136. en_DK.ISO-8859-15 ISO-8859-15 260. hif_FJ UTF-8 384. ro_RO.UTF-8 UTF-8 13. an_ES.UTF-8 UTF-8 137. en_DK.UTF-8 UTF-8 261. hne_IN UTF-8 385. ru_RU ISO-8859-5 [More] （略） (Enter the items you want to select, separated by spaces.) Locales to be generated: 285 #これは現在の日本語 UTF-8の設定番号です。2.タイムゾーン設定
＊まずは地域選択で、6のアジアを選択し、
＊タイムゾーン選択で 79のTokyoを選択するroot@XXXX :/# dpkg-reconfigure tzdata ------------------ Please select the geographic area in which you live. Subsequent configuration questions will narrow this down by presenting a list of cities, representing the time zones in which they are located. 1. Africa 3. Antarctica 5. Arctic 7. Atlantic 9. Indian 11. SystemV 13. Etc 2. America 4. Australia 6. Asia 8. Europe 10. Pacific 12. US Geographic area: 6 #アジアを選択 Please select the city or region corresponding to your time zone. 1. Aden 14. Beirut 27. Gaza 40. Karachi 53. Muscat 66. Riyadh 79. Tokyo 2. Almaty 15. Bishkek 28. Harbin 41. Kashgar 54. Nicosia 67. Sakhalin 80. Tomsk 3. Amman 16. Brunei 29. Hebron 42. Kathmandu 55. Novokuznetsk 68. Samarkand 81. Ujung_Pandang 4. Anadyr 17. Chita 30. Ho_Chi_Minh 43. Khandyga 56. Novosibirsk 69. Seoul 82. Ulaanbaatar 5. Aqtau 18. Choibalsan 31. Hong_Kong 44. Kolkata 57. Omsk 70. Shanghai 83. Urumqi 6. Aqtobe 19. Chongqing 32. Hovd 45. Krasnoyarsk 58. Oral 71. Singapore 84. Ust-Nera 7. Ashgabat 20. Colombo 33. Irkutsk 46. Kuala_Lumpur 59. Phnom_Penh 72. Srednekolymsk 85. Vientiane 8. Atyrau 21. Damascus 34. Istanbul 47. Kuching 60. Pontianak 73. Taipei 86. Vladivostok 9. Baghdad 22. Dhaka 35. Jakarta 48. Kuwait 61. Pyongyang 74. Tashkent 87. Yakutsk 10. Bahrain 23. Dili 36. Jayapura 49. Macau 62. Qatar 75. Tbilisi 88. Yangon 11. Baku 24. Dubai 37. Jerusalem 50. Magadan 63. Qostanay 76. Tehran 89. Yekaterinburg 12. Bangkok 25. Dushanbe 38. Kabul 51. Makassar 64. Qyzylorda 77. Tel_Aviv 90. Yerevan 13. Barnaul 26. Famagusta 39. Kamchatka 52. Manila 65. Rangoon 78. Thimphu Time zone: 79 #Tokyoを選択環境変数LANGへ設定をする
先ほどインストールしたvimで、.bashrcを編集し、
export...を追加する。＜vimでの編集方法＞
vim ~/.bashrcで、vimが起動。
十字キーでファイルの一番下に移動して、「i」を押して入力モードへ
export〜を追記し、escを押してコマンドモードに戻る
「:wq」と入力して変更を保存するroot@XXXX :/# export LANG=ja_JP.UTF-8 root@XXXX :/# vim ~/.bashrcもう一度 localeを見て見ると
root@XXXX :/# locale LANG=ja_JP.UTF-8 LANGUAGE= LC_CTYPE="ja_JP.UTF-8" LC_NUMERIC="ja_JP.UTF-8" LC_TIME="ja_JP.UTF-8" LC_COLLATE="ja_JP.UTF-8" LC_MONETARY="ja_JP.UTF-8" LC_MESSAGES="ja_JP.UTF-8" LC_PAPER="ja_JP.UTF-8" LC_NAME="ja_JP.UTF-8" LC_ADDRESS="ja_JP.UTF-8" LC_TELEPHONE="ja_JP.UTF-8" LC_MEASUREMENT="ja_JP.UTF-8" LC_IDENTIFICATION="ja_JP.UTF-8" LC_ALL=変更されてる！
プラスで、.inputrcというファイルを作成し、内容を追加しましょう。
root@XXXX :/# vim ~/.inputrc #以下の3行を.inputrcに追加する set convert-meta off set meta-flag on set output-meta on #適用 root@XXXX :/# source ~/.inputrc以下のようにすると、mysql内の設定も変わっているかと思います。
以上です。
- 投稿日：2020-09-11T08:56:05+09:00
Rails アプリケーションの作成準備
アプリケーションの作成
command% rails _6.0.0_ new application -d mysql・「6.0.0」でバージョンを指定しています。
・「application」は作成したいアプリケーション名を示します。
・「- d mysql」のオプションをつけることで、
データ管理ツールとしてMYSQLを使用します。
データベースの作成
config/database.ymldefault: &default adapter: mysql2 encoding: utf8 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> username: root password: socket: /tmp/mysql.sock「utf8」のようにエンコーディングの設定を行うことができます。
command% rails db:createrailsコマンドでデータベースを作成。
ルーティングの設定
config/route.rbRails.application.routes.draw do root to: "notes#index" endルートパスへのアクセスがあったら、notes_controllerのindexアクションが呼び出されるようになる。
ビューとコントローラーの設定
config/application.rb# 省略 config.load_defaults 6.0 # 中略 config.generators do |g| g.stylesheets false g.javascripts false g.helper false g.test_framework false endrails gコマンドでコントローラーを作成する前に、必要のないファイルを生成しないように設定。
commnad% rails g controller notes indexコントローラー作成時にコントローラ名に続けてアクション名を指定すると、
・notesコントローラーにindexアクションが作られる
・viewsのnotesフォルダにindex.html.erbファイルが作られる
- 投稿日：2020-09-11T08:04:14+09:00
sprintf()でSQL文を生成する際、%は%でエスケープできる
何を言っているのかわからねーと思うが、ありのままを話すぜ2020/09/12 追記
コメントにてSQLインジェクションの原因となる脆弱性をご指摘いただきました。
本記事はsprinf()でSQLクエリを作成する事を推奨するものではないとご理解をお願いします。
@tadsan様 ありがとうございます。はじめに
現在私はPHP+MySQLでフルスクラッチ開発をしている会社で働いているのですが、
まだ、入社して1ヶ月ほどなので既存のコードを読むことがほとんどです。
弊社ではphpファイルの中でSQL文を生成する際に
sprintf()を使用して、フォーマットを整えるようにしています。
（これがスタンダードなのかは不明）その中で、いまいちよくわからない記述を調べていくうちに
興味深いルールに出会いましたので、忘れないようQiita残しておきます。sprintf()でSQLの曖昧検索クエリを作る時
$freeword = 'qiita'; $sql = sprintf('SELECT * FROM title LIKE "%%%s%%"', $freeword);このコードはsprintf()の第二引数 $freeword が、第一引数内の "%%%s%%" の部分に
置き換わってフォーマットされるのですがその際に気をつけたいルールがあります。まず真ん中部分の「%s」は変数の値で今回の例ではqiitaに変換されます。
なので、この時点では「%%qiita%%」になる。この段階で、筆者は
「無駄に%の数多くね？」
と、勘違いしていましたがそもそもsprintf()で%という記号はそのまま文字として認識されないので
エスケープして文字とする必要があります。そこで%をエスケープするための記号が%なのです。
つまりは
残った「%」のうち、後ろの%は、それぞれ手前の 「%」でエスケープされるので、結果としてただの「%」と言う「文字列」になります。
① %→「%」qiita %→「%」
② 結果「%qiita%」が残る
③ これがSQLのLIKE文に残ることで、結果として、
$sql = 'SELECT * FROM title LIKE %qiita%';
と言うSQL文の完成。わかりやすい参考記事
下記ブログ記事の解説が初心者にも大変わかりやすかったので
記事作成の大部分に引用させていただきました。ありがとうございます。