SQLの基本の使い方

# SQLの基本の使い方
Postgresで練習

## テーブル作成
```sql
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));
```
### 列追加
```sql
alter table Jyushoroku add column yubin_bango char(8) not null;
```
### 名前変更
#### テーブル
```sql
alter table Jushoroku rename to Jyushoroku;
```
#### カラム
```sql
alter table Jushoroku rename jusho to jyusho;
```
### テーブル削除
```sql
drop table Jyushoroku;
```
## SELECT
WHEREの後にGroup by
例

```sql
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
行毎で削除

```sql
delete from shohin;
```

## INSERT
全列挿入

```sql
insert into shohin values(a,b,c);
```

一部挿入

```sql
insert into shohin(col1,col2,col3) values(a,b,c);
```

## UPDATE

```sql
update shohin set col1=a where ~~;
```

## VIEW
```sql
create view v1 as select a from b where c=d;
```

## 相関サブクエリ
サブクエリ内に外側のクエリとの結合条件を記述することで、テーブルをその結合キーでカットした部分集合に対して操作を行うことを可能にする技術

```sql
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が含まれると空行をかえす(何も返らない)

```sql
select * from table where not in (1,2,null);
```

## CASE
case式の便利な使い方として、SELECT文の結果を行列変換する方法が知られている。
分類ごとの合計を列ごとに表示したいなら以下。

```sql
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句でしか使えない。

```sql
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 FOLLOWING`
n行前からm行後のレコードを含む

# テーブル情報取得
### テーブル定義情報
```
desc tablename
```
### テーブル件数
http://tunes.sakura.ne.jp/tipi/?Oracle%2F%A5%C6%A1%BC%A5%D6%A5%EB%B0%EC%CD%F7%A4%F2%B7%EF%BF%F4%A4%C8%C6%B1%BB%FE%A4%CB%C9%BD%BC%A8%A4%B9%A4%EBSQL

```sql
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
```
### テーブル件数
https://qiita.com/shouta-dev/items/3f9d3bf284d59874f78a

```sql
select relname, n_live_tup from pg_stat_user_tables where schemaname='public';
```
## SQL Server
https://qiita.com/qsuke92/items/51a85a58ac91782ee528
### テーブル一覧
```sql
select * from sys.objects;
```

### テーブル件数
```sql
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
```