SQLの基本の使い方
Postgresで練習
テーブル作成
create table Jyushoroku
(toroku_bango int not null,
namae varchar(128) not null,
jyusho varchar(256) not null,
tel_no char(10),
mail_address char(20),
primary key(toroku_bango));
列追加
alter table Jyushoroku add column yubin_bango char(8) not null;
名前変更
テーブル
alter table Jushoroku rename to Jyushoroku;
カラム
alter table Jushoroku rename jusho to jyusho;
テーブル削除
drop table Jyushoroku;
SELECT
WHEREの後にGroup by
例
select shohin_bunrui,sum(hanbai_tanka),sum(shiire_tanka)
from shohin
group by shohin_bunrui
having sum(shiire_tanka)*1.5<sum(hanbai_tanka);
集約関数を使うとSELECT句に書ける要素が限定される
COUNTなどの集約関数を使うと以下しかSELECT句に書けない
- 定数
- 集約関数
- Group by句で指定した列名(集約キー)
DELETE
行毎で削除
delete from shohin;
INSERT
全列挿入
insert into shohin values(a,b,c);
一部挿入
insert into shohin(col1,col2,col3) values(a,b,c);
UPDATE
update shohin set col1=a where ~~;
VIEW
create view v1 as select a from b where c=d;
相関サブクエリ
サブクエリ内に外側のクエリとの結合条件を記述することで、テーブルをその結合キーでカットした部分集合に対して操作を行うことを可能にする技術
select
(select avg(a)
from table as t2
where t1.b=t2.b --外側のクエリとの結合条件
group by t2.b) --それぞれのグループごとの平均を出す
from table as t1;
NOT INの罠
not inにnullが含まれると空行をかえす(何も返らない)
select * from table where not in (1,2,null);
CASE
case式の便利な使い方として、SELECT文の結果を行列変換する方法が知られている。
分類ごとの合計を列ごとに表示したいなら以下。
select sum(case when shohin_bunrui = '衣服'
then hanbai_tanka else 0 end) as sum_tanak_ihuku,
sum(case when shohin_bunrui = 'キッチン用品'
then hanbai_tanka else 0 end) as sum_tanak_kitchen,
sum(case when shohin_bunrui='事務用品'
then hanbai_tanka else 0 end) as sum_tanka_jimu
from Shohin;
数を表示したいなら、then 1 else 0のSUMをする。
ウィンドウ関数
集約関数とRANKなどのウィンドウ専用関数を使える。
SELECT句でしか使えない。
select shohin_mei, shohin_bunrui, hanbai_tanka,
rank () over (partition by shohin_bunrui
order by hanbai_tanka) as ranking
from Shohin;
※PARTITION BYは指定しなくてよい。あくまで、テーブルを複数の部分に小分けしたい場合に使う。
※RANKなどのウィンドウ専用関数はrank ()のように引数を空白で使用する。
※OVER句内のORDER BYはウィンドウ関数がどういう順序で計算するかを決めるだけなので、結果の並び順には影響しない。（並び変えてから、処理をする）
つまり、ウィンドウ関数の実行計画にはソートが発生する。
⇒結果を並べ替えるには、SELECTの最後でORDER BYする。
集約関数の場合、各行までの累積の結果がでる。
例えばSUMなら累計、AVGなら累計平均。
フレーム
ウィンドウ内でフレームという集計範囲を決めれる。
-
ROWS n PRECEDINGカレントコード含めたn行前のレコードを含む -
ROWS BETWEEN n PRECEDING AND m FOLLOWINGn行前からm行後のレコードを含む
テーブル情報取得
テーブル定義情報
desc tablename
テーブル件数
SELECT
TABLE_NAME,
TO_NUMBER(
EXTRACTVALUE(
XMLTYPE(
DBMS_XMLGEN.GETXML('SELECT COUNT(*) C FROM '||TABLE_NAME))
,'/ROWSET/ROW/C')) COUNT
FROM USER_TABLES
WHERE TABLE_NAME NOT LIKE 'BIN$%'
AND (IOT_TYPE != 'IOT_OVERFLOW' OR IOT_TYPE IS NULL)
ORDER BY TABLE_NAME
Postgresql
テーブル定義情報
$ \d tablename
テーブル件数
select relname, n_live_tup from pg_stat_user_tables where schemaname='public';
SQL Server
テーブル一覧
select * from sys.objects;
テーブル件数
SELECT A.name AS TableName, B.rows
FROM sys.objects AS A
JOIN sys.sysindexes AS B
ON A.object_id = B.id AND B.indid < 2
WHERE A.type = 'U'
ORDER BY A.name