ITスクール DAY29 Oracle Sequence/SELECT/INSERT/関数
初めに
先週は、DDLを中心に勉強をしましたが、今日は個別個別のエンティティを扱うDMLとOracle内の関数（build-in function）について勉強しました。
Sequence
factory patternといいまして、自動化のために使うやつです。
１億行目のデーターに一一数字を挿入するのは、相当なテーマがかかりますので、その場合はSequneceを活用した方が効率が高いです。
CREATE SEQUENCE sequencename;
CREATE SEQUENCE table_name_sequence
慣習的に、適用するテーブル名_sequenceに名づけます。
CREATE SEQUENCE table_name_sequence;
INCREMENT by 1 -- 1ずつ上がります
START WITH 1 -- 初期化する値
MINVALUE
MAXVALUE
CYCLE/NOCYCLE -- 最大値になった場合、最低値に戻るかないか
CACHE/NOCACHE -- メモリ上にsequenceを作っといて持ってくるかないか
DROP SEQUENCE sequencename; -- sequence削除
table_name_sequence.nextval; --Autoincrementによる値増加(++)
Insert(INSERT INTO)
CRUDのCに該当します。
テーブル（カラム）にデーター（値）を入れるDMLです。
INSERT INTO table_name (col1,col2,...) VALUES(v1,v2...);
INSERT INTO table_name VALUES(v1,v2,...);
属性を指定して入れる方法とターブルに一基に入れる方法があります。
テーブルに一基に入れる場合は、属性の順序に沿って値を入れることが必修になります。
インスタンス化する際にパラメーターを入れることと同じだと考えば、わかりやすいです。
自習
create table snack(
s_no number(4) primary key,
s_name varchar2 (10 char) not null,
s_maker varchar2 (10 char) not null,
s_price number(4) not null,
s_exp date not null
);
CREATE SEQUENCE snack_seq;
INSERT INTO snack VALUES
(snack_seq.NEXTVAL,'새콤달콤','롯데',500,TO_DATE('2023-08-22 12','YYYY-MM-DD HH24'));
INSERT INTO snack VALUES
(snack_seq.NEXTVAL,'오예스','해태',3000,TO_DATE('2023-08-25 15','YYYY-MM-DD HH24'));
INSERT INTO snack VALUES
(snack_seq.NEXTVAL,'허니버터칩','해태',3000,TO_DATE('2023-08-20 07','YYYY-MM-DD HH24'));
.
.
.
INSERT INTO snack VALUES
(snack_seq.NEXTVAL,'초코파이','오리온',3000,TO_DATE('2023-08-22 10','YYYY-MM-DD HH24'));
INSERT INTO snack VALUES
(snack_seq.NEXTVAL,'포카칩 오리지날','오리온',1200,TO_DATE('2023-08-24 16','YYYY-MM-DD HH24'));
INSERT INTO snack VALUES
(snack_seq.NEXTVAL,'포카칩 어니언맛','오리온',1200,TO_DATE('2023-08-23 08','YYYY-MM-DD HH24'));
s_no s_name s_maker ....
1 새콤달콤 롯데 .....
2 오예스 해태 .....
3 허니버터칩 해태 .....
.
.
17 초코파이 오리온 ....
18 포카칩 오리지날 오리온 ....
19 포카칩 어니언맛 오리온 ....
snack_seq.NEXTVALというSequenceで、s_noが自動的に増加したことが分かります。
Read(SELECT)
SELECT [distinct] [c_name],[c_name as othername], [Operator],[aggregate function(集計関数],
from [table_name]
where [conditional statement]
group by [grouping target]
having [function-inclusive condition(関数包含条件)]
order by [ASC/DESC (昇順 / 降順)] --defaultはASC(Ascend)
*/
今日は、distinct, as, 演算子(Opeator),集計関数(aggregate function) を勉強しました! 今後、Group by, having, order byも載せます。
* : テーブル内の全てのカラムの値＝すべてのエンティティを読みます。
SELECT * FROM snack;
distinct : 属性(カラム)の値の中で重腹は除去して読みます。
SELECT DISTINCT s_maker FROM snack;
s_maker
-------
롯데
오리온
해태
snack tableの中で全てのお菓子会社が分かりたい。ただし、重腹は除去する
とと
as ：属性の名前に別の名前を付けることができます。必ずasが必修ではありません。ただし、空白文字（space）と記号などが入る場合は、別称に" "を付けることをオススメします。
SELECT s_price/100 as s_price, s_price / 100 s_price2 from snack;
SELECT s_price/100 as "s_price/100" , s_price/100 "s_price2/100"
s_price/100 は s_priceに、s_price/100はs_price2に
|| : 文字を結合する際に使う演算子です。javaと異なり、Oracleは1+'3'が4になる整数演算が行われます。1||3に演算すれば、Javaのように、13が出ます。
関数(Build-in-function)
数字・数学
ABS(数字) : Math.abs(数字)、絶対値計算
ROUND(数字,M) : 四捨五入
POWER(数字,N) : 数字のN条 (Math.pow(数字、N))
TRUNC(数字,M) : 数字をM桁まで残して値を捨てます。
MOD(M,N) : M%N、MをNに分けた後のあまり
SQRT(数字) : 数字の√を計算(陽数のみ)
SELECT ABS(-4.5) FROM DUAL; --4.5
SELECT ROUND(5.36,1) FROM DUAL; --5.4
SELECT POWER(2,3) FROM DUAL; --8
SELECT TRUNC(123.456,1) FROM DUAL; --123.4
SELECT TRUNC(123.456,-1) FROM DUAL; --120
SELECT MOD(12,10) FROM DUAL; --2
SELECT SQRT(9.0) FROM DUAL; --3
文字
CONCAT(S1,S2) : 二つの文字列を繋ぐ
SUBSTR(S1,N,K) : S1のN桁の文字からKの長さほど切り取ります。
INSTR(S1,S2,N,K) : S1のN番目から始まり、探したい文字列S2がK番目に出現する文字列の位置を返します。
LPAD(S,N,C) : 文字列Sを左から指定した桁数Nまで指定した文字Cで埋めます。
RPAD(S,N,C) : 文字列Sを右から指定した桁数Nまで指定した文字Cで埋めます。
LTRIM(S1,S2) : 文字列S1を基準にして左側にある指定した文字S2を削除します。
RTRIM(S1,S2) : 文字列S1を基準にして右側にある指定した文字S2を削除します。
REPLACE(S1,S2,S3) : S1の指定した文字S2を希望する文字S3に置き換えます。
LENGTH : 文字数を数えてくれる関数
LENGTHB : 文字のバイト数を数えてくれる関数
SELECT CONCAT('A','B') FROM DUAL -- 'AB'
SELECT SUBSTR('ABCDEFG',3,4) FROM DUAL -- 'CDEF'
SELECT INSTR('HELLO','L',1,2) FROM DUAL -- 4
SELECT LPAD('ABC',5,'*') FROM DUAL -- '**ABC'
SELECT RPAD('ABC',5,'*') FROM DUAL -- 'ABC**'
SELECT LTRIM('**ABC','*') FROM DUAL -- 'ABC'
SELECT RTRIM('ABC**','*') FROM DUAL -- 'ABC'
SELECT REPLACE('JACK and JUE','J', 'BL') FROM DUAL -- 'BLACK and BLUE'
SELECT LENGTH('ㅋㅋㅋ') 글자수, LENGTHB('ㅋㅋㅋ') 바이트수 FROM DUAL -- 글자수: 3, 바이트수: 9
日付、時間
YYYY: 4桁の年 / YYY, YY, Y: それぞれ4桁の年の末尾3、2、1桁
MM: 月 / DD: 1ヶ月の中の日付 / DAY: 曜日（月曜日〜日曜日）/ DY: 曜日（月〜日）
HH, HH12: 12時間単位 / HH24: 24時間単位 / MI: 分 / SS: 秒 / AM/PM: 午前/午後
SYSDATE: 現在の日付/時刻を返します
TO_DATE(S1, DATETIME形式): 文字列データを日付形式に変換します。
SELECT TO_DATE ('2023-08-21 오후 03:10','YYYY-MM-DD AM HH:MI') FROM DUAL;
-TO_CHAR(DATE,DATETIME형식): 日付形式を文字列デーに変換します。
SELECT TO_CHAR (SYSDATE,'YYYY-MM-DD') FROM DUAL;
SELECT TO_CHAR (TO_DATE ('2023-08-21 오후 03:10','YYYY-MM-DD AM HH:MI'),'YYYY-MM-DD AM HH:MI') FROM DUAL;
特定の日付値を取得したい場合は、SYSDATEの代わりにTO_DATE関数を使用して指定できます！
ADD_MONTHS(日付, 数値) : 日付から指定した月数を加算します（1: 次の月、-1: 前の月）
SELECT ADD_MONTHS(SYSDATE,-3) FROM DUAL;
LAST_DAY(DATE) : 該当月の最後の日をリターンします。
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY')||'년' 연도 FROM DUAL;
SELECT TO_CHAR(SYSDATE,'MM')||'월' 월 FROM DUAL;
SELECT TO_CHAR(SYSDATE,'DD')||'일' 일 FROM DUAL;
SELECT TO_CHAR(SYSDATE,'HH')||'시' 시, TO_CHAR(SYSDATE,'mm') ||'분' 분 FROM DUAL
SELECT TO_CHAR(SYSDATE,'ss')||'초' 초 FROM DUAL;
SELECT TO_CHAR(SYSDATE,'DAY')||'요일' 요일 FROM DUAL;
SELECT TO_CHAR(SYSDATE,'AM') "오전/오후" FROM DUAL;
文字列 '2023-08-23 午前 06:00' ➡ 文字列 2023.08.23
SELECT TO_CHAR
(TO_DATE ('2023-08-23 오전 06:00','YYYY-MM-DD AM HH:MI'),'YYYY-MM-DD') FROM DUAL;
3か月前の月と最後の日をリターン ➡ 05-31
SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-3)),'MM-DD') FROM DUAL;
集計/分析関数
AVG(フィールド名) : 平均
COUNT(フィールド名) : 検索されたデータ数
MAX(フィールド名) : 最大値
MIN(フィールド名) : 最小値
SUM(フィールド名) : 合計
RANK: 重複する順位だけ次の順位値を増加させる（1,2,2,4）
DENSE_RANK: 重複する順位が存在しても、連続的に次の順位値として表示する（1,2,2,3）
NVL
NVL関数: NULLの場合に指定した値で置換する関数
NVL(値, NULLのときの代替値)
SELECT NVL(NULL,'B'), NVL('C','B') FROM DUAL;
NVL2関数: NULLの有無に応じて指定した値で置換する関数
NVL2(値, 値があるときの代替値, NULLのときの代替値)
SELECT NVL2(NULL,'A','B'), NVL2('C','A','B') FROM DUAL;
WHERE句(条件式)
全データのうち、指定した条件に一致するデータを検索する文
条件式で使用される演算子 => 比較演算子
--全てのお菓子の名前、価格を取得
SELECT s_name 名前 FROM snack;
SELECT s_price || '円' 価格 FROM snack;
--全てのお菓子の名前(価格)を取得
SELECT s_name||'(' || s_price|| '円)' "名前(価格)" FROM snack;
--2000円以下のお菓子の中で最低価格
SELECT MIN(s_price) FROM snack WHERE s_price <= 2000;
--全てのお菓子の平均価格
SELECT ROUND(AVG(s_price),1) || '円' 平均価格 FROM snack;
--30%割引されたお菓子の名前と価格
SELECT s_name "お菓子名", ROUND(s_price*0.7,1) || '円' "30%割引額" FROM Snack;
--3500円以下のお菓子の数
SELECT COUNT(*) FROM snack WHERE s_price <= 3500;
--4000円以下のお菓子の名前、会社名
SELECT s_name "お菓子名" , s_maker "会社名" FROM Snack WHERE s_price <= 4000;
Discussion