【Oracle】色々なSQL文まとめ

SQL文の使い方を忘れないように解説とともに残しておきます。

テーブルの作成

テーブルを作成する時は「create文」を使います。

書き方は以下のようになります。

create table テーブル名
(
入れたい値 それの型(最大文字数) ※制約
);

だいたいこんな感じで作成できます。

以下は実際作成する時の例になります。

create table student
(
student_id number(5) not null primary key, 
first_name varchar2(10),
last_name vacher2(10),
birthday date,
gender char(1)
age number(3) default ‘0’ not null
);

それではこれの解説をしていきます。

作成したテーブル名は、「student」です。

このテーブルに、「student_id」と「first_name」と「last_name」と「birthday」「gender」と「age」という値を設定します。次にそれに対しての「型」を設定します。

型の種類によって入られる物が異なります。
number  →  数字
varchar2 → 文字列
char → 1文字
date → 日付

そして、student_id には制約で「not null」と「 primary key」を設定しています。

primary keyとは、主キーの事でこれを元に他の値が関連付けられます。そしてnull で値を入れる事は出来ません。次に、「age」の制約の default ‘0’ ですが、これは、登録時に何も設定されなかった場合には0になります。そしてこれもnullは設定できません。

primary keyを２つ設定したい時もあると思います。そんな時は以下のように記述します。

/*
create table student
(
student_id number(5) not null, 
first_name varchar2(10),
last_name vacher2(10),
birthday date,
gender char(1)
age number(3) default ‘0’ not null
*/
primary key(student_id,birthday)
);

コメントアウトした所はさっきとほぼ同じです。そして最後に「primary key(student_id,birthday)」を追加しました。これで主キーを2つ設定する事ができます。カッコの中のに主キーにしたい物を記述しています。

テーブルに値を入れる

テーブルに値をいれる時は「insert文」を使います。

コードは以下のようになります。

insert into テーブル名　（列名）valuse(入れたい値)；

上のテーブルに値を入れてみましょう。

insert into student (

//列名
student_id,first_name,last_name,birthday,gender,age)

//入れたい値
values(
6,'次郎','山田',to_date('1982-10-10 00:00:00','yyyy/mm/dd hh24:mi:ss'),'M',15
);

date型は少し特殊です。
to_dateという関数のような物を使って第1引数に入れたい値、第2引数にそれのフォーマットを記述します。

to_date('1982-10-10 00:00:00','yyyy/mm/dd hh24:mi:ss')
これは「1982-10-10 00:00:00」という値を入れたいので「yyyy/mm/dd hh24:mi:ss」というフォーマットで設定しています。

データベースの結合

データベースを結合する内部結合と外部結合について解説します。
テーブルは上で作った「student」と新しく「test」テーブルを作ったとします。共通する列(値)は「student_id」とします。

内部結合
それぞれのテーブルの指定した列の値が一致するものだけを結合します。内部結合の場合、ベースとなるテーブルから、条件にマッチするレコードがないものは削除されます。コードは以下のようになります。

SELECT 列名 FROM テーブル名① 仮名、 テーブル② 仮名 
WHERE 条件;

例:

SELECT st.first_name,st.last_name 
FROM student st, test te
WHERE st.student_id = te.test_id;

このコードを解説します。
表示するのは「student」テーブルの「first_name列」と「last_name列」です。そして、where句で「student」テーブルの「student_id」と「test」テーブルの「student_id」が一致するものを表示する。としています。

外部結合
内部結合のようにそれぞれのテーブルの指定したカラムの値が一致するものを結合するのに加え、どちらかのテーブルにしか存在しないものに関しても取得します。存在しないものは null になります。
コードは以下のようになります。

SELECT カラム名 FROM テーブル①
LEFT(RIGHT) OUTER JOIN テーブル名② ON 結合の条件;

例:

SELECT first_name,last_name 
FROM student st
LEFT(RIGHT) OUTER JOIN test te 
ON st.student_id = te.student_id;

LEFT か RIGHTのどちらかを記述します。
・LEFT OUTER JOIN: 左側のテーブルを基準にして外部結合を行う方法
・RIGHT OUTER JOIN:右側のテーブルを基準にして外部結合を行う方法

どっちのテーブルを基準にするかを決めています。この場合だと「student」が基準になります。

サブクエリの使い方

is not null と not in

not in : サブクエリで取り出せてないものをnot inで取り出して表示

is not null : null以外のものが取り出せる

SELECT * FROM student　　　　　　　　　　
WHERE student_id not in
(SELECT student_id FROM test     
WHERE student_id is not null );　　　　　　

２つ目のSELECT文がサブクエリになります。

次は「exists句」を使います。
not existsでサブクエリで取り出せなかったものを表示します。

select * from STUDENT A　　　　　　　　　
where not exists
(select 'X' from TEST TE　
where A.STUDENT_ID=TE.STUDENT_ID);

サブクエリの’X’はexists句を使うときに使うものです。

GROUP BY句

「GROUP BY句」は指定した列に同じ値の物があったら１つにまとめてくれます。

この「GROUP BY句」と相性がいいのが「SUM()関数」や「AVG ()関数」などといったものです。
SUM()関数は、数字などの合計を出します。
AVG ()関数は、数字などの合計の平均を出します。

SELECT　gender,AVG(age)
FROM student
GROUP BY last_name,gender;　　　　　　　　

このようにすると「gender」の同じ値の物がまとめられて、「age」は「gender」でまとめられた物の平均が表示されます。また、このままだと表示した時に列名が「AVG(age)」と表示されてしまうのでこれを「age_average」として表示したい場合は以下のようになります。

SELECT　gender,AVG(age) as age_average
FROM student
GROUP BY gender;　　　

名前を変えたい列名の後ろに「as 変えたい列名」とすると変えることができます。

「GROUP BY 句」を使う時は、「SELECT」の後ろに記述した列名を「GROUP BY 」の後ろでも記述しないといけません。(AVG()関数などを使った場合は記述しなくても大丈夫)

更に、「WHERE句」と一緒に使う場合は「GROUP BY」の前に記述します。

SELECT　gender,AVG(age) as age_average
FROM student
WHERE gender = ‘M’
GROUP BY gender;　

値によって表示を変える方法

プログラミング言語の「if文」のように条件によって表示を変えたりしたい時があると思います。そんな時は以下のようになります。

SELECT 列名,
CASE
条件
END 新しい列のタイトル,
列名
FROM テーブル名

「CASE」と「END」までが１つの例となります。この場合は3つの列が表示されます。

例:「STUDENT_ID」が奇数か偶然で分けて、奇数なら「奇数」と表示「偶然」なら偶然と表示させます。
そしてこの列のタイトルを「checked_result」とします。

select STUDENT_ID, 
case　　　　　　　　　　　　　　　　　　　　
when mod(STUDENT_ID, 2)=1 then '奇数'　　
when mod(STUDENT_ID, 2)=0 then '偶数'　　　　
else 'なし' 
end checked_result 　　　　　　　　　　　
from student;

　「STUDENT_ID」を分ける時は「mod関数」を使って振り分けます。これはカッコの中で STUDENT_IDをを2で割ってその余りが　1 か 0 で分けています。

UNION句とUNION ALL句

UNION句は複数のSELECT結果を統合し、重複レコードは1行にまとめられます。

例 : 別のテーブルの「FIRST_NAME」と「LAST_NAME」を統合させて表示さたい。更に「LAST_NAME」でソートしたい時は以下のように記述します。

select FIRST_NAME, LAST_NAME         
from student      
union        
select FIRST_NAME, LAST_NAME         
from test       
order by LAST_NAME desc   

この２つのテーブルで同じ列の値が被ってたらそれは削除されて表示されます。

UNION ALL句 は複数のSELECT結果を統合し、重複レコードはそのまま表示されます。

例 : 上と同じ

select FIRST_NAME, LAST_NAME         
from student      
union all 
select FIRST_NAME, LAST_NAME         
from test       
order by LAST_NAME desc   

違いは「union」の後ろに「all」が加わっただけです。
これで、この２つのテーブルで同じ列の値が被っていてもそのまま表示されます。

また、「union句」と「union all句」にはルールがあり表示さる列は２つの「select文」で同じにしないといけません。
「where句」を使う場合はそれぞれの「select文」で記述します。

その他

小数点を切り捨てる関数

TRUNC(12345.678)

これは「12345」と表示されます。

Like句(パーセント「%」)

パーセントを使って検索する事ができます。

Select FIRST_NAME
form STUDENT 
where FIRST_NAME like ‘山%’;

これで「FIRST_NAME列」の文字列の最初が「山」から始まる値が表示されます。

Select FIRST_NAME
form STUDENT 
where FIRST_NAME like ‘%山’;

パーセントを検索したい文字列の前にする事で最後の文字が「山」で終わる値を検索します。

Select FIRST_NAME
form STUDENT 
where FIRST_NAME like ‘%山%’;

パーセントで検索したい文字列を挟む事で文字列に「山」を含む値が表示されます。

Where句の使い方

where句で「or」と「and」を使う時は、「or」の条件をカッコでくくります。そうすると「or」の条件が優先されます。

select ST.FIRST_NAME, ST.BIRTHDAY, TE.RESULT
from STUDENT ST, TEST TE
where (ST.STUDENT_ID=1 or ST.STUDENT_ID=6 ) 　
and ST.FIRST_NAME = TE.FIRST_NAME;

これで「STUDENT_ID」が 1 か 2 で且つ「FIRST_NAME」の値が同じ物が表示されます。

-¥-¥-¥-¥-¥-¥-¥-¥-¥-¥-¥-¥-¥-¥-¥-¥-¥-¥-¥-¥-¥

最後まで読んで頂きありがとうございました。

少しでも勉強になった、役に立ったと思ったら「 サポート」 で貴方のお気持ちを頂けると嬉しいです。
今後の励みにもなります。是非よろしくお願いします。