データベースSQL（PostgreSQL、MySQL）例文・サンプル

データベース（PostgreSQL、MySQLなど）に関する昔のメモ。 古いので注意。

SQL（基本）

よく使うコマンド

SELECT（検索）

DBから項目検索
select * from member;
select log from member where id = '00YoKjf945kUKik';
select log, id from member;
select a.id, b.log from member a, comment b where a.id = b.id;

INSERT（挿入）

DBに項目追加
insert into member values('00krogr063Nfkme','データ挿入');
insert into member(id, log) values('00krogr063Nfkme','データ挿入');

UPDATE（更新）

DBの項目更新
update member set log = 'データ更新。' where id = '00krogr063Nfkme';
update member set log = 'データ更新。', sample = 'ABC' where id = '00krogr063Nfkme';

DELETE（削除）

DBから項目削除
delete from member where id = '00krogr063Nfkme';

WHERE（条件）

処理する条件を指定
select * from member where id = '00YoKjf945kUKik' and log = 'コメント';
select * from member where id = '00YoKjf945kUKik' or id = '00h4kgg945grKgr';
select * from member where id = '00YoKjf945kUKik' and (log = 'コメント' or log = 'テキスト');

ORDER BY（ソート）

特定の項目に関して並び順を指定
select * from member order by id;
昇順
select * from member order by id ASC;
降順
select * from member order by id DESC;

GROUP BY

同じ値のものを1つにまとめて処理
byの後、1つに集約したいカラム名を書く。
カンマで区切って複数並べることもできる。
select id, count(id) from member group by id;
select id, address, count(id) from member group by id, address;

カラム名の代わりに数字も使える。
検索結果のカラムの先頭から順に1、2、3、・・・。
select referer,count(referer)
from member
group by 1
order by 2 DESC;

select agent,count(distinct uid)
from member
where date >= '2005-01-01'
group by 1;

count

検索数・行数をカウント
select count(*) from member;
重複を除いてカウントする場合は、distinctを使う。
select count(distinct id) from member;

sum（合計）

各フィールドの合計値を計算します。
select sum(score) from member;
重複を除いてカウントする場合は、distinctを使う。
select count(distinct score) from member;
count()は単にレコード数のカウントで、
sum()は各レコードのフィールドに含まれる数値を合計してくれます。

limit（検索表示制限）

表示する行数を制限
select * from member limit 10;

offset（オフセット）

検索を開始する位置を指定
select * from member limit 10 offset 10;
上記の場合、検索結果の11rows目から10件を表示する。
MySQLの場合は、
select * from member limit 0, 10;
で、limit 10 offset 0 と同じ。
PostgreSQLとの互換性を考慮しoffsetも使える。

like（文字列一致）

対象文字列を含むかどうか検索
%はワイルドカード
select * from member where agent like '%bot%';
select * from member where agent like 'DoCoMo%';

NOT !=（否定）

select *
from member
where not referer like '%ABC%'
and referer!='';

join（テーブル結合、連結）

select
	a.id as a_id, a.name as a_name,
	b.id as b_id, b.name as b_name,
	c.id as c_id, c.name as c_name,
	d.id as d_id, d.name as d_name,
	e.id as e_id, e.name as e_name
from
	category a
	right join pref b on a.id=b.id
	left join capital c on c.id=b.id
	left join employee d on d.id=b.id
	left join stock e on e.id=b.id;

select
	member.uid, sample.uid, sample.stat
from sample
	left join member on
	((sample.r_date<'2002-12-01' and sample.stat=1)
	or (sample.r_date<'2002-12-01' and sample.c_date>='2002-12-01' and sample.stat=0))
	and (sample.uid=member.uid);

index

SQLによる検索スピードを向上させる目的としてindexを作成。 検索条件（where）として頻繁に使用されるフィールドに対してindexを作成。

通常。

create index インデックス名 on テーブル名(フィールド名);
create index member_id_index on member(id);

対象フィールドの各値がユニークの場合に可能。

create unique index インデックス名 on テーブル名(フィールド名);
create unique index member_id_index on member(id);

アクセスメソッド。

create index インデックス名 on テーブル名 using アクセスメソッド名(フィールド名);
create index member_id_index on member using btree(id);
	btree：Lehman-Yao による高同時性 btree の実装。（デフォルトは btree）
	rtree：Guttman による二次分割アルゴリズムを使用した標準の rtree の実装。
	hash：Litwin による線形ハッシュの実装。

インデックスを削除。 MySQLは on テーブル名を省略できない。

drop index インデックス名 on テーブル名;

テーブル定義変更、カラム追加

カラム追加。

alter table テーブル名 add カラム名 属性;
alter table memberid add agent text;

カラム削除。

alter table テーブル名 drop カラム名;
alter table memberid drop agent;

カラム名変更。

alter table テーブル名 rename 旧カラム名 to 新カラム名;
alter table memberid rename agent to useragent;

日付・時間の範囲、加算、減算

現在の日時。

CURRENT_TIMESTAMP(0)
現在の日付と時間
select * from sample_log where accessdate <= CURRENT_TIMESTAMP(0);

日付の加算、減算。 自動的に日付を計算してくれる。

30日前
select * from sample_log where accessdate >= date '2005-07-09' - integer '30';

7日後
date '2005-07-09' + integer '7'（指定日から7日後）
date 'today' - integer '90'（今日から90日前（3ヶ月前））

時間指定
date '2005-07-01' - interval '1 hour'
date '2005-07-01' - interval '4 hours'（時間が1以上は複数形）
date '2005-07-01' + time '16:30'
now() - interval '30 minutes'（現在時刻から30分前）
now() - interval '0.5 hours'（現在時刻から30分前）

今日、前後の日付。

now(現在の時間)、today(今日)、yesterday(昨日)、tomorrow(明日)
nowは、今日の日付の現在の時刻。それ以外は、0時0分の日付。
insert文などでも使える。
	select * from sample_log where accessdate >= 'yesterday';

参考： 日付/時刻関数と演算子（PostgreSQL）

その他

select a.count_total,b.count_today,c.count_yesterday
from
(select count(*) as count_total
from test_log
where id = '123'
and accessdate > date 'today' - integer '90')
as a,
(select count(*) as count_today
from test_log
where id = '123'
and accessdate >= 'today')
as b,
(select count(*) as count_yesterday
from test_log
where id = '123'
and (accessdate >= 'yesterday' and accessdate < 'today'))
as c;

PostgreSQL

データベース。

・DBを作成
create database DB名
または
createdb DB名

・文字コードを指定
createdb --encoding=文字コード名 DB名
文字コード名は、EUC_JP、UTF-8、SQL_ASCII など

・DBにアクセス
psql DB名 -U ユーザ名
psql -h DBサーバ名 -nd DB名 -U ユーザ名

・DBを削除
psqlでdropするDB以外にログイン後、drop database DB名
または
psqlでログインせず、dropdb DB名 -U ユーザ名

table（テーブル）。

・テーブルを作成
create table member(
id char(15),
log text
);

create table member(
id char(10) primary key,
flg int2,
count int2 default 0,
accessdate timestamp,
updatedate timestamp default current_timestamp not null,
uagent text
);

・テーブルを削除
drop table member;

SEQUENCE（シーケンス）の設定。 primary keyを設定したテーブルに\copyでデータをテーブルにコピーしてもシーケンス値は反映されない。 その場合は手動で最大値を設定する。 一致しない場合は、後々データをinsertできなくなる場合がある。

・シーケンス削除
drop sequence シーケンス名;

primary keyの値を参照
select sample_id from member order by sample_id desc limit 3;

シーケンス値の参照
select last_value from member_sample_id_seq;

シーケンス値に新たな値を設定
select setval('member_sample_id_seq',1357);

コマンド。

・テーブル一覧を参照
テーブル、シーケンス表示
\d;

テーブルのみ表示
\dt;

シーケンスのみ表示
\ds;

作成済みのデータベースのリストを表示
\l

テーブルのフィールド・設定・構造を参照
\d テーブル名;

トランザクション

PostgreSQLは、初期状態ではSQLを実行すれば即コミットされる。 もしSQL発行前の状態に戻したい場合があればトランザクションを利用する。 SQLの前後でbegin、commit、rollbackを実行する。

トランザクションの開始（SQLを発行する前に行う）
begin;

コミット（SQLに問題がなければコミットする）
commit;

巻き戻し（SQLに問題があり、beginの前までDBの状態を戻す）
rollback;

例
begin;
update member set log = '1' where id = '00krogr063Nfkme';
rollback;

begin;
update member set log = '10' where id = '00krogr063Nfkme';
commit;

ファイル・バックアップなど

ファイルのデータをテーブルにコピー。

\copy テーブル名 from  member.txt

テーブルのデータをファイルに出力。

\copy テーブル名 to member20050101.txt

タブ区切りでSQLの結果をファイルに出力。 カンマ区切りなら'\t'を','にする。

\o member.txt
\a
\pset fieldsep '\t'
select * from member where date>='2005-01-01' and date<'2005-02-01';

カンマ区切り(CSV)でSQLの結果をファイルに出力。 タブ区切りなら","を"\t"にする。

psql DB名 -U ユーザ名 --pset format=unaligned --field-separator="," -c "select * from member;" > test.log
psql DB名 -U ユーザ名 --pset format=unaligned --field-separator="," < ./test.sql > test.log

ダンプ

DBの構造やデータ全てをバックアップ。 psqlでログインせずに pg_dump、pg_dumpall コマンドを行う。

●通常のダンプ
・ファイルにダンプ
	pg_dump DB名 > ファイル名
	ユーザ名が必要な場合は、オプションで指定（他のコマンドも同様）
	pg_dump DB名 -U ユーザ名 -h ホスト名 -p ポート名 > ファイル名
・元に戻す場合（リストア）
	psql -d DB名 -f バックアップファイル名
	または
	psql DB名 < バックアップファイル名

・ラージオブジェクトを含むDBのダンプ
	・データをtar形式でダンプ
		pg_dump -Ft -b DB名 > ファイル名.tar
	・元に戻す場合（リストア）
		pg_restore -d DB名 ファイル名.tar

・全てのDBをダンプ
	・ファイルにダンプ
		pg_dumpall > ファイル名
	・元に戻す場合（リストア）
		psql -f バックアップファイル名 template1
		または
		psql template1 < バックアップファイル名

vacuum

PostgreSQLは追記型DBのため、delete、updateされたレコードは見えない状態でDBに残る。 それを除去する。

psqlでログイン後
vacuum;

処理情報を出力したい場合
vacuum verbose;

psqlでログインせず、vacuumdbコマンドでも可能。
vacuumdb -d DB名 -U ユーザ名
vacuumdb -d DB名 -v -U ユーザ名
-vは--verboseでもOK

MySQL

MySQL Windows版設定。

パスを通す

MySQLをインストールするとmysqlのフォルダができるので、その下のbinフォルダにパスを通す。

起動

mysql\bin\winmysqladmin.exeを起動。 その後、DOSプロンプトで「mysql」でMySQLが操作できる。 基本的にDOSプロンプトの画面で操作。

終了

\q

ヘルプ

\h

データベース

・DBを作成
	DOSプロンプトでMySQLを起動した後、
		create database DB名
	でDBを新規作成できます。
	作成したDBは、mysql\dataの下にあります。
	
・文字コード
	create database DB名 character set 文字コード名
	文字コード名は、utf8、ujis（eucのこと）、sjisなど

・DBにアクセス
	DOSプロンプトでMySQLを起動した後、
		connect DB名
	または、DOSプロンプトで
		mysql DB名
	で作成済みのDBにアクセスできます。
	DBが別のサーバの場合はホスト名を指定
		mysql -u ユーザ名 -p データベース名 -h ホスト名
		
・DBを削除
	DOSプロンプトでMySQLを起動した後、
		drop database DB名
	でDBを削除できます。

TABLE

・テーブルを作成
	create table member(
	id char(15),
	log text
	);

・テーブルを削除
	drop table テーブル名;

・テーブル一覧を参照
	show tables;

・作成済みのデータベースのリストを表示
	show databases;

・DBの構造を見る
	desc テーブル名;
	または
	show fields from テーブル名;

serial

PostgreSQLの場合のserial
	create table member(
	id serial primary key,
	log text
	);
は、auto_incrementを使う。
型を指定しないとエラーになる場合がある。
create table member(
id int auto_increment primary key,
log text
);

unique

unique インデックス名 (フィールド名1,フィールド名2,・・・)
インデックス名は、省略可能。
uniqueにするフィールドは、型によってはエラーになる。
	create table memberid(
	id int auto_increment primary key,
	userid char(16),
	date1 timestamp default 'current_timestamp',
	unique (userid)
	);

timestamp

PostgreSQLのcurrent_timestampを指定するには、シングルクオートで囲まないとエラーになる。 current_timestampにしなくてもcurrent_timestampになる？ 1つのテーブルにtimestampのフィールドが複数ある場合は、最初のフィールドしかcurrent_timestampにならない。

insert into memberid(userid,date1,date2) values('sample',NOW(),NOW());
などで現在の日時を挿入する。

create table memberid(
id int auto_increment primary key,
userid char(16),
date1 timestamp default 'current_timestamp',
date2 timestamp default 'current_timestamp',
unique (userid)
);

コマンド

ファイルのデータをテーブルにインポート。

load data infile 'C:/www/sample.txt' into table member
fields terminated by '\t' optionally enclosed by ''
lines terminated by '\r\n';

テーブルのデータをファイルにエクスポート。

select * from member into outfile 'C:/www/sample.txt';

SQLを書いたテキストファイルでSQLを実行。

例えば、command.sqlというテキストファイルに
	select * from member;
というのを書いて、
	source C:/www/command.sql
とすると、command.sqlに書いたSQL文を実行できる。

ダンプ。 DBの情報をファイルとしてバックアップする。 SQL形式で出力される。

mysqldump -u ユーザ名 -p DB名 > 出力ファイル名

また、文字コードの指定やDBサーバが別のホストの場合には以下のように行う。

mysqldump -u ユーザ名 -p DB名 --default-character-set=文字コード名 --host=ホスト名 > 出力ファイル名

-u はユーザの指定で --user=ユーザ名 と同様。 -p はパスワードで --password と同様。 パスワードを書かずコマンドを実行するとパスワードの入力を要求されるので、そこで入力。

リストア。 ダンプしたものをDBに戻す。

mysql -u ユーザ名 -p DB名 < 出力ファイル名

トランザクション

MySQLは、初期状態ではSQLを実行すれば即コミットされる。 もしSQL発行前の状態に戻したい場合があればトランザクションを利用する。

SQLの前後でbegin、commit、rollbackを実行する。

トランザクションの開始（SQLを発行する前に行う）。 以下3種類どれでも可。

begin; （MySQL 3.23.17以降）
begin work; （MySQL 3.23.19以降）
start transaction; （MySQL 4.0.11以降）

コミット（SQLに問題がなければコミットする）。

commit;

ロールバック・巻き戻し（SQLに問題があり、beginの前までDBの状態を戻す）。

rollback;

begin;
update member set log = '1' where id = '00krogr063Nfkme';
rollback;

begin;
update member set log = '10' where id = '00krogr063Nfkme';
commit;