[Oracle] 여러번 봐야할 SQL문 총정리

[Oracle] 예시로 배우는 오라클 SQL문 총정리

- 안녕하세요 자주 사용하고 꼭 알아야 할 SQL문을 모두 정리하는 페이지입니다.  즐겨찾기 해두셨다가 출퇴근이나,  자투리 시간에 보며 본인이 아는 sql 지식을 확인하고 주석을 참조하면 좋은 공부가 될 것이고, 외워버리면 더욱 금상 첨 화인 녀석들을 모아서 정리했습니다. 기초적인 DML, DCL, DDL.. 의 SQL문을 공부하고 쭉 살펴보면서 내가 알고 있는 내용을 점검하고 해석해보며 공부해봅시다.

 

• select * from student;  // student 테이블의 모든 칼럼을 조회합니다.
• select distinct num from student; //distint는 열에서  num 뿌리는데 중복되는 값을 제외한 다른 값만 뿌려줍니다.
• select count(distinct num) from student; //num뿌리는데 중복 제외한 열만 개수를 세서 뿌려줍니다.
• where 절은 select, update, delete 문에 사용된다.
• select * from student where num=1;  //num이1 인조 건을 만족하는 열을 모두 선택해 뿌려줍니다.
• select * from student where num between 1 and 3;  //num이 1 이상 3 이하인 모든 열을 뿌려줍니다.
• select * from student where city like '%s';  //s로 시작하는 단어를 포함한 city 행의 열을 모두 뿌려줍니다.
• select * from customer where score in(90,100);  //score열에 90과 100을 포함한 녀석은 모두 뿌려줍니다.
• select * from customer where city='a' or id='b';  //city=a이거나 아이디가=b인 조건을 만족하는 녀석을 모두 뿌림.
• select * from customer where city='a' and id='b'; // city=a이고 id=b인 두 조건을 만족하는 녀석을 모두 뿌림.
• select * from customer where nation='korea' and (city='b' or city='c');                                                     //nation='korea'이고 city='b'이거나 city='c'인 두 조건을 만족하는 녀석을 모두 뿌려줍니다.
• select * from customer where not country='j' and not country='s';                                                          //country='j'가 아니고 country='s'가 아닌 두 녀석을 뺀 나머지를 뿌려준다.
• select * from customer where name='kim' order by country;                                                                    //order by는 정렬입니다. 여기선 country를 asc 즉 사전 순서로 기준해 정렬해 뿌려줍니다.
• order by 칼럼명 → asc 문자(a->z순서) , 숫자(오름차순)
• order by 칼럼명 →desc 문자(z->a순서), 숫자(내림차순)
• select * from customer order by country, name;                                                                                   // 먼저 country로 정렬하고 만약! 그중 중복되어 정렬이 어려운 경우 name을 기준으로 또 정렬해줘라
• order by는 asc가 디폴트 값이고 생략하면 오름차순으로 적용됩니다.
• select * from customer order by country asc, name desc;                                                                       //고객 테이블을 국가를 기준으로 오름차순으로 정렬 후 만약! 중복 값이 있다면 name을 기준으로 내림차순으로 정렬해주세요.
• 삽입(insert) 방법은 2개 있습니다.
• insert into student(id, name, score) vlaues(a, 'nick',30); //삽입방법 1 칼럼명 적고  순서대로 넣기
• insert into student values(a, 'nick',30); // 삽입방법 2 순서대로 값 만적 돼 순서대로 넣기
• select * from student where address is not null; //주소에 널값이 없는 값만 출력
• select * from student where address is null; // 주소가 널인 녀석만 출력
• update student set id='b', name='jack', score=100 where id='a';                                                               // 수정할 때 주의할 점은 where절이 없다면 모든 레코드가 저렇게 바뀌니 주의하자.
• update customer set name='peter' where country='mexico'; //특정 열을 만족하는 name을 모두 peter로 바꾼다.
• delete from customer where name='jack'; // 특정 조건을 만족하는 녀석의 열을 삭제
• delete from customer ;  //테이블 이름이 customer인 녀석을 모두 테이블 삭제
• select * from customers where rownum <= 3; // rownum은 행에서 위 3줄만 가져와 보여줍니다
• select * from customers where country='germany' and rownum <=3;                                        //country=germany 인 녀석들을 위에서 3개만 뿌려줍니다.
• select min(num) as smallNum from student;                                                                                        //학생 테이블에서 num값이 가장 작은 값 하나를 찾습니다 이때 그 행의 이름을 별칭을 주어 smallNum으로 바꿔 보여줍니다.
• select max(num) as bigNum from student;    //반대로 최댓값 하나를 찾는 건 max(칼럼명)입니다.
• select count(id) from student;  // count(칼럼명) 해당 칼럼의 개수를 반환합니다.
• selelct avg(score) from student; //avg(칼럼명)은 평균을 반환합니다.
• select sum(score) from student; //sum(칼럼명)은 합을 반환합니다.
• LIKE 연산자는 WHERE절에서 열에 지정된 패턴을 검색하는 데 사용되고 이때 와일드카드를 주로 활용합니다.
• 와일드카드는 %, _ 두 가지가 있습니다.
• select * from student WHERE Name LIKE 'a%';   //a로 시작하는 이름을 가진 학생을 모두 뿌립니다.
• select * from student WHERE Name LIKE '% a';   //a로 끝나는 이름을 가진 학생을 모두 뿌립니다.
• select * from student WHERE Name LIKE '%a';   //a라는 알파벳이 들어간 모든 이름을 뿌립니다. ex) aim, sae, aak..
• select * from student WHERE Name LIKE '_k';  //두 번째 위치에 k 타들어간 이름을 모두 뿌립니다.
• selelct * form student WHERE Name LIKE 'A__'; //A로 시작, 3자 이상인 이름을 뿌립니다 _(언더바) 2개 총 3 단어
• select * from  student WHERE Name LIKE 'a% o' // a로 시작하고 o로 끝나는 이름 뿌림.
• select * from student where name like '_ack';                                                                                      //_는 임의의 문자를 의미하는 와일드카드 기능도 합니다. 즉 임의의 문자로 시작해서 뒤가 ack인 이름을 뿌려줍니다.
• select * from customer where city like '[bsp]%';                                                                            //[charlist] 와일드카드는 sql문은 도시가 b나 s나 p로 시작하는 모든 고객을 선택합니다.
• select * from customer where city like '[a-c]%';                                                                                  //[a-c] 즉 캐릭터 리스트에서 a에서 c 즉 도시가 a or b or c로 시작하는 모든  고객을 선택합니다.
• select * from customer where city like '[! bsp]%';                                                                                     //! 는 부정으로 도시가 b나 s나 p로 시작하지 않는 모든 고객을 선택합니다. ==... not like [bsp]도 가능
• IN연산자를 사용하면 WHERE절에서 여러 값을 지정할 수 있습니다. == OR연산
• BETWEEN A AND B연산자는 숫자, 텍스트, 날짜를 주어진 범위 내에서 모두 선택합니다.
• AS 별칭은 테이블의 열에 임시로 이름을 제공하고 그 쿼리 기간 동안만 존재하게 사용합니다.
• 별칭 이름에 공백이 필요하면 큰따옴표 또는 대괄호가 필요합니다.
• select name as n, id as [student id] from student; // 별칭에 공백 줄 때 예시
• JOIN 절은 둘 이상의 테이블 사이의 관련 열을 기반으로 행을 결합하는 데 사용됩니다.
• INNER JOIN 은 두 테이블에서 일치하는 값이 있는 레코드를 선택합니다. 교집합인 값이 있다면 두 테이블의 내용을 보는 방법입니다.
• select c.score, k.num from TESTC c, testk k where c.pw = k.pw;                                                                // 이너 조인은 두 테이블을 조인할 때 두 테이블의 값이 같은 즉 교집합인 값이 있으면 두 테이블의 값들을 조회할 수 있는 것이다. 여기선 각각 textk, textc테이블이 있는데 이들에서 각각의 num과 score을 보는 테이블을 만들고 싶은데 각각에 c, k로 별칭을 주고 where절에 값이 같은 pw가 1인 녀석과 다른 테이블의 pw가 1인 녀석이 있음으로 조건을 만족한다.  여기서 주의할 점은 꼭 같은 칼럼이 아니라도 값만 같다면 where 조건으로 = 을 통해서 두 테이블의 교집합 관계를 만족하여 이너 조인으로 두 테이블에서 원하는 값을 얻을 수 있는 것이다!

<testk테이블>

 

id	pw	score
a	1	100
b	2	20

<testc테이블>

log	pw	num
c	1	12
d	2	23

• RIGHT JOIN 키워드는 오른쪽 테이블 (table 2)의 모든 레코드와 왼쪽 테이블 (table1)에서 일치하는 레코드를 반환합니다. 결과는 일치하지 않는 경우 왼쪽에서 NULL입니다.
• LEFT JOIN 키워드는 왼쪽 테이블 (table1)의 모든 레코드와 오른쪽 테이블 (table 2)에서 일치하는 레코드를 반환합니다. 일치하는 항목이 없으면 결과는 오른쪽에서 NULL입니다.
• OUTTER JOIN은 테이블 레코드에 일치하는 항목이 있는 경우 모든 레코드를 반환합니다. 즉 두 테이블 사이에 같은 값이 없어도 조인하는 방식이다. 이때 값이 없는 테이블에(+)를 주어 값이 없더라도 행이 출력되게 하는 것이다. 이때 행은 나오지만 값이 없다면  값들은 NULL로 나온다
• select c.num, k, id from testk c, member k where c.id=k.id(+); //아웃터 조인 예시
• Group by문 은 "각 반의 여자 수 찾기" 와같이 동일한 값을 가진 행을 요약행으로 그룹화합니다.
• 이러한 GROUP BY는 종종 집계 함수인 COUNT, MAX, MIN, SUM, AVG와 함께 사용하여 결과 집합을 하나 이상의 열로 그룹화합니다.
• select count(cutomerId), country from customer group by country order by count(customerId) desc;                //각 국가의 고객 수를 높은 순으로 정렬하여 숫자, 나라로 그룹화하여 출력합니다.
• Having 절은 where 키워드를 집계 함수와 함께 사용할 수 없기 때문에 이용합니다.
• select count(CustomerId), country from customers group by country having count(CustomerId) > 5;                //각 국가의 고객 수를 높은 순으로 정렬하여 나열합니다 (고객이 5 명 이상인 국가만 포함).
• any와 all 연산자는  where 또는 having절과 함께 사용됩니다.
• ANY연산자는 하위 쿼리 값이 조건을 충족하는 경우 TRUE를 반환합니다.
• ALL연산자는 모든 하위 쿼리 값이 조건을 충족하는 경우 TRUE를 반환합니다.
• SELECT name from product where productId = ANY(SELECT productId from orderDetail where quantity=10);   //OrderDetails 테이블에서 수량이 10 인 레코드를 찾으면 제품 이름을 나열합니다.
• SELECT name from product where productId = ALL(SELECT productId from orderDetail where quantity=10);       //OrderDetails 테이블의 모든 레코드에 수량 = 10 인 경우 제품 이름을 나열합니다 (따라서 OrderDetails 테이블의 모든 레코드가 수량 = 10이 아니므로이 예에서는 FALSE를 반환합니다).
• alter table student add email varchar2(50); // alter table 테이블명 add 칼럼명 타입;으로 기존 테이블에 열을 추가할 수 있다.
• alter table student drop column email; // alter table 테이블명 drop column 칼럼명;으로 기존의 테이블에서 특정 열을 삭제할 수 있다.
• alter table person modify num varchar2(3); // person 테이블의 , num 열의 데이터 타입을 변경하는 방법입니다. varchar2(3)으로 변경하는데 이때 중요한 사실은 안에 데이터 가 있으면 변경이 안된다는 사실입니다.
• alter table t1 rename column num to pw; // 이번에는 t1 테이블의 num 열을 pw로 변경합니다. rename column __ to __으로 해줍니다.
• 칼럼의 데이터 타입을 변경하기 위해서는 해당 컬럼의 값을 모두 지워야 변경이 가능하다.
• constraint는 테이블을 만들 때 테이블의 데이터에 대한 규칙을 지정하는 데 사용됩니다.
• create table로 테이블을 만들 때, 만든 후 alter table로 만든후 제약조건을 지정할 수 있습니다.
• contraint 제약조건은 테이블에 들어갈 수 있는 데이터 유형을 제한하는 데 사용됩니다. 이렇게 하면 테이블에 있는 데이터의 정확성과 신뢰성이 보장됩니다 그도 그럴 것이 제약조건을 위배하는 작업을 할 때 작업을 중단하여 에러를 보여주기 때문입니다.
• NOT NULL 제약조건이 붙으면 데이터 삽입시 NULL값을 넣을 수 없어 반드시 유효한 값이 들어가게 강제합니다. 원래는 NULL의 입력을 허용하지만 NOT NULL의 제약 조건에 따라 널값이 들어갈 위험을 막습니다.
• ALTER TABLE person modify age number(2) not null; // 이미 만든 테이블도 제약 조건을 추가하여 나중에 테이블을 변경할 수 있습니다. modify 칼럼명 칼럼의 데이터 타입을 명시 + not null을 입력해줍니다.
• UNIQUE 제약 조건은 열의 모든 값이 서로 다른지 확인 즉 중복을 배제합니다.
• UNIQUE, PRIMARY KEY 제약 조건은 모두 열의 고유성을 보장합니다.
• ALTER TABLE T1 ADD UNIQUE(PW); //유니크 제약 조건을 이미 생성한 테이블의 PW 열에  추가합니다.
• ALTER TABLE person add constraint UNIQUE_C UNIQUE(id, pw)// 이렇게 제약조건에 이름을 주어 id , pw에 설정합니다.
• ALTER TABLE person DROP CONSTRAINT unique_c ; // 특정 별칭을 준 제약조건을 삭제합니다.
• PRIMARY KEY 제약: DMS 테이블의 각 레코드를 고유하게 식별하기 위해 사용합니다. 이 기본키는 UNIQUE 값을 포함해야 하고 NOT NULL입니다.  기본키 = UNIQUE + NOT NULL
• ALTER TABLE Persons DROP CONSTRAINT PK_Person;  // 프라이머리 제약 조건 제거
• FOREIGN KEY는 두 테이블을 함께 연결하여 사용하기 위한 키입니다. 이 녀석을 다른 테이블의 PRIMARY KEY를 참조하는 한 테이블의 필드입니다.
• 외래 키가 포함된 테이블은 하위 테이블이라 하고 후보 키가 포함된 테이블을 참조 또는 상위 테이블이라고 합니다.
• ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID); //Orders"테이블이 이미 생성된 경우 "PersonID"열에 FOREIGN KEY 제약 조건을 생성
• ALTER TABLE Orders drop constraint KEY fk_orderskey; //  참조키의 이름으로( fk_orderskey ) 제약조건을 날릴 수 있다.
• check 제약 조건은 열에 배치할 수 있는 값의 범위를 아이에 테이블 생성 시 제한합니다. 단일 열에 check 제약 조건을 정의하여 특정 열에 대한 조건을 만족해야 값을 넣을 수 있게 만들 수 있다

create table person(
id int nou null,
pw number(10),
age int CHECK (AGE>=18)
);

 

• 위처럼 생성한 PERSON 테이블은 AGEDP 18 이상의 값만 넣을 수 있게 되는 것입니다.
• ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes'); // 이렇게 이미 생성된 테이블에 제약 조건을 추가할 수도 있고 이때 제약 조건에 이름을 주면 나중에 관리에 용의 합니다.
• ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge; //이렇게 제약 조건의 이름을 드롭하여 제약 조건을 제거할 수 있습니다.
• DEFAULT 제약 조건은 열에 대한 기본값을 지정하는 용도로 사용됩니다.
• 즉 다른 값을 지정하지 않으면 모든 새 래코드에 해당 기본값이 들어갑니다.

CREATE TABLE Ord (
    ID varchar2(3) NOT NULL,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT sysdate
);

insert into ord(id, orderNumber) values('a',3);

select * from ord;

•  위 예시를 보면 ord 테이블에 default 값이 sysdate로 지금 시간이 들어가는데 insert문에서 orderdate를 안넣주니 null 이아니라 현재 시간이 들어갑니다.
• ALTER TABLE ord MODIFY ID DEFAULT 'EX';  //이후에 테이블에 디폴트 제약조건을 넣어 주는 방법입니다.
• SEQUENCE를 통해 자동 증가 필드를 사용할 수 있다. 아래 코드 예시를 살펴봅니다.

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

INSERT INTO Persons (Personid,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen');

• 위 코드는 시퀀스를 만들고 삽입한 예시입니다. SEQ_PERSON 이란 시퀀스를 생성하되 1로 시작하여 1씩 증가하고 최대 10개의 값을 캐시 하는 시퀀스를 만듭니다 참고로 캐시 옵션은 더 빠른 액세스를 위해 메모리에 저장될 시퀀스 값 수를 지정합니다. 그 후 시퀀스 이름으로 삽입해주는데 주로 기본키로 활용하고 삽입시 반드시 시퀀스명. nextVal로 넣어야 합니다.
• sql 날짜 에대하여 날짜로 값을 작업할 때 어려운 부분은 삽입하려는 날짜의 형식이 데이터 베이스의 날짜 열 형식과 일치하는지 확인하는 것인데 이때 시간까지 넣으려 하면 복잡해지니 지양합니다.
• 뷰 만들기: create or replace view result_inner_join as select  a.empno, a.ename, a.job, a.hiredate, a.deptno, b.dname, b.loc from emp a, dept b where a.deptno = b.deptno;
• 오라클 - 뷰(View)
  -- 물리적인 테이블에 근거한 논리적인 가상 테이블.
  -- 가상이란 단어는 실질적으로 데이터를 저장하고 있지 않기 때문에 붙인 것이고,
  -- 테이블이란 단어는 실질적으로 데이터를 저장하고 있지 않더라도 사용 계정자는 마치
  -- 테이블을 사용하는 것과 동일하게 뷰를 사용할 수 있기 때문에 붙인 것.
  -- 뷰는 기본 테이블에서 파생된 객체로서 기본테이블에 대한 하나의 쿼리문임.
  -- 실제 테이블에 저장된 데이터를 뷰를 통해서 볼 수 있도록 함.
  -- 사용자에게 주어진 뷰를 통해서 기본 테이블을 제한적으로 사용하게 됨.
  -- 뷰는 이미 존재하고 있는 테이블에 제한적으로 접근하도록 한다.
  -- 뷰를 생성하기 위해서는 실질적으로 데이터를 저장하고 있는 물리적인 테이블이 존재해야 되는데 이 테이블은 기본 테이블이라고 한다.

- ps. 이후 심화 편에서 다시한번 까다로운 sql문들을 공부 하겠습니다 수고하셨습니다. ^^