MEMORVA

データベース,PostgreSQL,MySQL,DB,SQL,例文 - サンプル

更新:

データベース,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;