データベース,PostgreSQL,MySQL,DB,SQL,例文 - サンプル。
■基本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
・検索スピードを向上させる目的として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';
・参考
http://www.postgresql.jp/document/pg800doc/html/functions-datetime.html
■その他
・メモ
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 Linux版 ====================
■データベース / DB
・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
・シーケンス削除
drop sequence シーケンス名;
・シーケンスの設定
primary keyを設定したテーブルに\copyでデータをテーブルにコピーしても
シーケンス値は反映されない。
その場合は手動で最大値を設定する。
一致しない場合は、後々データをinsertできなくなる場合がある。
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版設定
・ダウンロード
http://www.mysql.com/
・パスを通す
MySQLをインストールするとmysqlのフォルダができるので、
その下のbinフォルダにパスを通す。
・起動
mysql\bin\winmysqladmin.exeを起動
その後、DOSプロンプトで
mysql
でMySQLが操作できます。
基本的にDOSプロンプトの画面で操作します。
・終了
\q
・ヘルプ
\h
■データベース / DB
・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;