SQL データベースプログラミング - note4recurrent @ ウィキ

2022/06/24 田渕先生

• webプログラミング
  • フロントサイド：web プログラミング
    • html,css,javascript,(PHP) ...
    • アプリ：swift,Python,C++,VBA.net,...
  • サーバーサイド：
    • JAVA,PHP,Python,Ruby,NodeJS（Javascript),C++... <SQL> データベース（MySQL,PostgreSQL...）
• 組み込み(FA設備、IoTデバイス、自販機、自動車制御、テレビ、エアコン...)
  • C,Python...
• PCやスマホアプリ（ローカルで動くもの）
  • C++,Swift,VBA

MySQLをインストール

• server only
• pwは指定のものを使う

SQL基本４命令

• select

  select * form 家計簿
  where 日付 = '2022/1/1';
  

• 新規レコード作成

  insert into 家計簿 (日付,メモ,出金額,入金額)
  values ('2022/1/1','ほげほげ',0,5500);
  -更新
  update 家計簿
  set 出金額 = 30000
  where 日付 ='2022/2/1';
  -削除
  delete from 家計簿
  where 日付 = '2022/2/1';
  

• コメント

  -- 行末まで
  /*   */ 複数行
  例：
  /*入出金表示用SQL バージョン0.1
  　　  作成者：朝香あゆみ　作成日：2022-02-01*/
  　select 入金額,出金額 -- 金額関連の列のみ表示
  　from 家計簿
  

2022/7/1

• テーブル設定
  • 論理名：日本語
  • 物理名：英語とすることが多い（SQLではこの名前でアクセスする）教科書では日本語にしているが普通はローマ字

• 固定長と可変長 P52
  • SQLの文字数は文字数。バイト数ではない。
  • 半角も全角も10文字入る。
  • 固定長CHARCHARには後ろに空白が入る。
  • 可変長VARCHAR

• ４大命令:確実にできるように覚えておくこと
  • select 列名 from テーブル where
  • update テーブル set 列名１ = '値' , 列名２ = 333 where 条件
  • delete from テーブル名 where 条件
  • insert into テーブル名 （列名1,列名2,列名3）value = ('文字列',555,'文字列２')

• as 複数のテーブルに対してアクセスる場合に列名を特定する時に使う
  • select kake.日付 arc.日付 from 家計簿 as kake, 家計簿アーカイブ as arc

• 検索と更新
  • 検索：select
  • 更新：update , delete , insert

• 既存と新規：既存のデータに対して操作を行うとき。対象となるレコードを特定するために where 句を使う
  • 既存：select , update , delete: (where)
  • 新規：insert

• is null is not null　：P８５
• like : 文字列に使用する　P87
• between 100 and 5000 : 左が小さい数になるようにしないといけない P89
• in (ddd,ggg,aaa):どれかに一致する P90
• not in (a,b,c) :どれにも一致しない
• any/all はあまり使わない P90
  • any:
  • all:

• escape P88
  • like '%100$%' escape '$'
  • like '%100-%' escape '-' : escapeで指定する文字に制限はない他の言語では\(またはバックスラッシュ)が決まっている。

• 以下の三つは同じ結果 JAVAやJavascriptで使う　！＝　は使えないので <> を使う（VBAもこの形）

  select * from 湊くんの買い物リスト where not 販売店 = 'B'
  select * from 湊くんの買い物リスト where  販売店 <> 'B'
  select * from 湊くんの買い物リスト where 販売店 not in ('B')
  

• 論理演算子　and or :JAVAやjavascript でやったif 文で使う条件とほぼ同じP95 　販売店= 'A' or 販売店　= 'B' and カテゴリ ='ゲーム' or カテゴリ = 'DVD' 　だと、販売店Aの全部と販売店Bのゲームとすべての店舗のDVDとがヒットする。 　 　(販売店= 'A' or 販売店　= 'B' ) and (カテゴリ ='ゲーム' or カテゴリ = 'DVD')

  販売店AかBでカテゴリがゲームかDVDのものに絞られる
  

• 主キー（プライマリーキー）の必要性
  • レコードを特定できるようにするための重複しない列
  • 通常、DBを設計する際には主キーを設定する。例：マイナンバー、レシートにある番号
  • 複数のキーを組み合わせて一意のデータにできる場合もある：複合主キー

    店舗番号と売上連番を組み合わせて売上データのキーにするようなケースがある。
    この場合、各店舗で１からスタートして管理することができ、管理が簡単。
    本社で集計する場合は店舗番号と連番を組み合わせれば一意に指定することが可能。
    

• 3章の練習問題までやっておくとよい。次回は4章。
  • 3章の練習問題用のデータは下のページに貼ってあります。多分大丈夫やと思います。 https://w.atwiki.jp/note4recurrent/pages/24.html

2022/07/08

第４章補足

• P121 OFFSET -FETCH　は　MySQLでサポートされないので、授業では使わない。
  代わりに　LIMIT- OFFSET を使う。

  書式　　　LIMIT　抜き出す行数 OFFSET　抜き出す際の一番最初のレコード順 
  例　　　　　　select * from tableA where date > '2022-01-01' order by date limit 20 offset 40;
           tableA という表から2022年1月1日以降のデータを抽出し、日付順に並べ、最初から40行目から20レコードを表示する。
           検索結果を表示するようなウェブページで２ページ目に表示するデータを選ぶようなときに使う。
  

• P129 のEXCEPTとP131 のINSTINCTはMySQLでサポートされないので、授業では使わない。以下もMySQLでは実行できません。（dokoQLはできます）
  • P136 の差集合と積集合の問題
  • P474 の 30,31
  • P480 の 31,32

2022/07/15

式・関数・case when then

• MySQL 注意事項
  • 日付の計算は使えない
  • 文字列の結合はconcat関数で行う（VBAのconcatinateと一緒か）
  • select 出金額,truncate(出金額,-2) as 百円単位の出金額 from 家計簿;
  • MySQLはバイトで数えるので全角文字はchar_lengthを使う
  • 半角はok

    select メモ, char_length(メモ) as メモの長さ from 家計簿;
    

• 半角しか使えない

  select メモ , trim(メモ) as 空白を除去したメモ from 家計簿;
  

• 1 select 商品コード,商品名,単価,商品区分,関連商品コード from 商品

• 2 select 商品名 from 商品

• 3 select * from 注文
• 4 select 注文番号,注文枝番,商品コード from 注文

• 5 insert into 商品 values ('W0461','冬のあったかコート',12800,'1',null); insert into 商品 values ('S0331','春のさわやかコート',6800,'1',null); insert into 商品 values ('A0582','秋のシックなコート',9800,'1',null);

• 6 select * from 商品 where 商品コード = 'W1252';
• 7 update 商品 set 単価 = 500 where 商品コード = 'S0023';
• 8 select * from 商品 where 単価 <= 1000;
• 9 select * from 商品 where 単価 > 50000;
• 10 select * from 注文 where 注文日 > '2022-01-01';
• 11 select * from 注文 where 注文日 < '2021-11-30';
• 12 select * from 商品 where 商品区分 <> '1';
• 13 select * from 注文 where クーポン割引料 is null;
• 14 delete from 商品 where 商品コード like 'N%';
• 15 select 商品コード,商品名,単価 from 商品 where 商品名 like '%コート%';
• 16 select 商品コード,商品区分 from 商品 where 商品区分 in ('2','3','9');
• 17 select * from 商品 where 商品コード between 'A0100' and 'A0500';
• 18 select * from 注文 where 商品コード in ('N0501','N1021','N0223');
• 19 select * from 商品 where 商品区分 = '3' and 商品名 like '%水玉%'
• 20 select * from 商品 where 商品名 like '%軽い%' or 商品名 like '%ゆるふわ%'
• 21 select * from 商品 where (商品区分 = '1' and 単価 <= 3000) or(商品区分 = '3' and 単価 >= 10000 );
• 22 select * from 注文 where 注文日 between '2022-03-01' and '2022-03-31' and 数量 >= 3;
• 23 select * from 注文 where 数量 >= 10 or クーポン割引料 is not null;
• 24
• 商品：商品コード、注文：注文番号・注文枝番

• 25 select 商品コード,商品名 from 商品

     where 商品区分 = '1' order by 商品コード desc
  

• 26 select 注文日,注文番号,注文枝番,商品コード,数量 from 注文 where 注文日 >= '2022-03-01'order by 1,2,3;
• 27 select distinct 商品コード from 注文 order by 1;
• 28 select distinct 注文日 from 注文 order by 1 limit 10;
• 29 select * from 商品 order by 単価,商品区分,商品コード limit 15 offset 5;
• 30 select * from 廃番商品 where 廃番日 between '2020-12-01' and '2020-12-31' union select * from 廃番商品 where 売上個数 >100 order by 6 desc
• 33 select distinct 商品コード,商品名,単価 from 商品 where 商品区分 ='9' and (単価 <= 1000 or 単価 >10000) order by 単価,商品コード

• P473
• 1 select 口座番号,名義,種別,残高,更新日 from 口座
• 2 select 口座番号 from 口座
• 3 select 口座番号,残高 from 口座
• 4 select * from 口座
• 5 update 口座 set 名義 ='XXXXX'
• 6 update 口座 set 残高 = 99999999 更新日 = '2022-03-01'
• 7 insert into 口座 values ('0642191','アオキ　ハルカ','1',3640551,'2022-03-13') insert into 口座 values ('1039410','キノシタ　リュウジ','1',259017,'2021-11-30') insert into 口座 values ('1239855','タカシナ　ミツル','2',6509773,null)
• 8 delete from 口座
• 9 select * from 口座 where 口座番号 = '37651'
• 10 select * from 口座 where 残高 > 0
• 11 select * from 口座 where 口座番号 < '1000000';
• 12 select * from 口座 where 更新日 < '2021-12-01';
• 13 select * from 口座 where 残高 >= 1000000;
• 14 select * from 口座 where 種別 <> '1';
• 15 select * from 口座 where 更新日 is null;
• 16 select * from 口座 where 名義 like '%ハシ%';
• 17 select * from 口座 where 更新日 between'2022-01-01' and '2022-01-31';
• 18 select * from 口座 where 種別 = '2' or 種別 = '3';
• 19 select * from 口座 where 名義 in ('サカタ　リョウヘイ','マツモト　ミワコ','ハマダ　サトシ');
• 20 select * from 口座 where 更新日 between '2021-12-30' and '2022-01-04';

• lesson1 にカレントスキーマを変更

select 出金額 ,出金額 + 100 as 百円増しの出金額 , 'SQL' from 家計簿;

insert into 家計簿 (出金額) values(1000+105); delete from 家計簿 where 日付 is null;

update 家計簿 set 出金額 = 出金額 + 100;

select 費目,出金額, case 費目 when '居住費' then '固定費' when '水道光熱費' then '固定費' else '変動費' end as 出費の分類 from 家計簿 where 出金額 > 0;

select 費目,入金額, case when 入金額 < 5000 then 'お小遣い' when 入金額 < 100000 then '一時収入' when 入金額 < 300000 then '給料出たー！' else '想定外の収入です！' end as 収入の分類 from 家計簿 where 入金額 > 0;

select メモ, char_length(メモ) as メモの長さ from 家計簿;

• MySQLはバイトで数えるので全角文字はchar_lengthを使う
• 半角はok

select メモ, char_length(メモ) as メモの長さ from 家計簿 where char_length(メモ) <= 10;

• 半角しか使えない select メモ , trim(メモ) as 空白を除去したメモ from 家計簿;

update 家計簿 set メモ = replace(メモ,'購入','買った');

select * from 家計簿 where substring(費目,1,3) like '%費%';

select 費目,substring(費目,1,3) as substring後 from 家計簿;

select concat(費目,'：',メモ) from 家計簿;

select 出金額,round(出金額,-2) as 百円単位の出金額 from 家計簿;

select 出金額,truncate(出金額,-2) as 百円単位の出金額 from 家計簿;

insert into 家計簿 values(current_date,'食費','ドーナツを買った',0,260);

select coalesce('A','B','C'); select coalesce(NULL,'B','C'); select coalesce(NULL,'B',NULL); select coalesce(NULL,NULL,'C'); select coalesce(出金額,0) from 家計簿;

update 家計簿 set メモ = Null where 日付 ='2022-07-15';

select 日付, 費目, coalesce(メモ,'（メモはNULLです）') as メモ,入金額,出金額 from 家計簿;

select 日付,費目,メモ,入金額,出金額,入金額-出金額 as 入出金差額 from 家計簿;

select 日付,費目, case when char_length(メモ) >= 8 then concat(substring(メモ,1,8),'...') else メモ end as メモ,入金額,出金額 from 家計簿;

select 日付, truncate(入金額/110.0 , 0) as 入金ドル, truncate(出金額/110.0,0) as 出金ドル from 家計簿;

select * from 家計簿 where 日付 = current_date;

select 日付, coalesce(メモ,費目,'不明') as 備考 from 家計簿;

select sum(出金額) as 出金額の合計 from 家計簿;

select sum(出金額) as 合計出金額, avg(出金額) as 平均出金額, max(出金額) as 最大出金額, min(出金額) as 最小出金額 from 家計簿;

select count(*) as 食費の行数 from 家計簿 where 費目='食費';

select count(出金額) as 食費の行数 from 家計簿;

select count(*) as 食費の行数 from 家計簿;

select count(distinct(費目)) from 家計簿;

select 日付 , sum(出金額) as 出金額計 from 家計簿;

select sum(出金額) from 家計簿; update 家計簿 set 出金額 = null;

select count(出金額) from 家計簿;

update 家計簿 set 出金額 = 7560 where 費目 = '水道光熱費';

select max(出金額) from 家計簿; select avg(出金額) from 家計簿; select avg(coalesce(出金額,0)) from 家計簿;

2022/07/22

• メモを保存するのを忘れてたorz...

2022/08/26

JAVA とDBMSをつなぐのがJDBCドライバ DBMSの種類とバージョンを合わせる必要がある