Section 03. SQL 시작하기

01. SELECT 문으로 데이터 검색하기

자주 사용하는 형태의 SELECT 문

SELECT 열

FROM 테이블

WHERE 조건

ORDER BY 열

 

한줄주석

-- 주석입력

 

여려줄 주석처리

/*

내용

*/

 

SELECT 문으로 특정 열 검색하기

전체 열 검색

SELECT * FROM PROFESSOR;

실무에서는 네트워크를 통해 수십개의 프로그램이 데이터베이스에 접속해 여러 쿼리를 호출하므로 

전체검색은 가급적 사용을 하지말자.

최소한의 검색을 하는 습관 중요!

 

여러 개의 열 검색

SELECT ID FROM PROFESSOR;

 

여러 개의 열 검색

SELECT ID, NAME FROM PROFESSOR;

 

열 정보 조회

SELECT * FROM COLS WHERE TABLE_NAME = 'PROFESSOR';

해당 테이블이 가지고있는 COLUMN의 다양한 정보를 알수있다.

02. WHERE 문으로 조건에 맞는 데이터 검색하기

WHERE 문으로 특정 값 검색하기

SELECT * FROM PROFESSOR WHERE POSITION = '정교수';

POSITION컬럼의 값이 정교수 인것만 가져오며, 다양한 연산자 사용이 가능하다.

!<  => 필터링조건보다 작지 않은값

정렬이 중요한데이터는 WHERE보단 ORDER BY를 쓰자.

WHERE 문으로 논리 연산자 사용하기

연산자	설명
ALL	모든 비교 집합이 TRUE이면 TRUE
AND	두 표현식이 TRUE이면 TRUE
ANY	비교 집합 중 하나라도 TRUE이면 TRUE
BETWEEN	피연산자가 범위 내에 있으면 TRUE
EXISTS	하위 쿼리에 행이 포함되면 TRUE
IN	피연산자가 리스트 중 하나라도 포함되면 TRUE
LIKE	피연산자가 패턴과 일치하면 TRUE
NOT	bool 연산자를 반대로 실행
OR	하나의 bool식이 TRUE이면 TRUE
SOME	비교 집합 중 일부가 TRUE 이면 TRUE

 

SELECT * FROM PROFESSOR WHERE PROFNO > 3002 AND PROFNO < 4005;

 

SELECT * FROM PROFESSOR WHERE PROFNO BETWEEN 3002 AND 4005;

BETWEEN은 조건값을 포함해서 탐색한다.

 

OR을 반복해서 쓰는것보단 IN을 사용하자. IN은 OR과 같은 역할이지만 쉼표를 사용하는 점만 다르다.

SELECT * FROM PROFESSOR WHERE PROFNO IN(3002, 4001, 4004);

 

논리연산자에는 우선순위가 있으니 필요한 연산이 먼저될 수 있도록 ( ) 를 자주 사용하자.

 

NULL 검색

SELECT * FROM PROFESSOR WHERE BONUS IS NULL;

 

NULL이 아닌 데이터 검색은 IS NOT NULL 사용 

03. ORDER BY 문으로 데이터 정렬하기

SELECT * FROM PROFESSOR WHERE BONUS IS NOT NULL ORDER BY POSITION ASC, HIREDATE DESC;

열을 2개 이상 기준으로 정렬하면, 처음 입력된 열 POSITION 에 같은 값이 있을경우 HIREDATE열의 값으로 정렬한다.

SELECT * FROM PROFESSOR WHERE ROWNUM <= 10;

상위 10개 데이터 검색.

SELECT * FROM PROFESSOR OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

10개 데이터를 건너뛰고 11번째 데이터부터 10개를 검색.

와일드카드로 문자열 검색하기

%사용법

• A% : A로 시작하는 모든 문자열.
• %A : A로 끝나는 모든 문자열.
• %A% : A를 포함하는 모든 문자열.

SELECT * FROM PROFESSOR WHERE POSITION NOT LIKE '%수';

 

'%'가 포함된 데이터 검색

SELECT * FROM PROFESSOR WHERE POSITION LIKE '%#%%' ESCAPE '#';

#대신 다른특수 문자를 써도 된다.

 

_사용법

• A_ : A로 시작하는 글자수 2개 문자열.
• _A : A로 끝나는 글자수 2개 문자열.
• _A_ : A를 포함하는 글자수 3개 문자열.

SELECT * FROM PROFESSOR WHERE ID LIKE 'c%i_';

C로시작해서 i로 끝나고 뒤에 한글자가 붙는 문자열.

[ ]사용법

• [A, B, C]% or [A-C]% : 첫 문자가 A,B,C중 하나인 모든 문자열.
• [A, B, C]% or [A-C]% : 마지막 문자가 A,B,C중 하나인 모든 문자열.

'A[A, B, C]' : 첫 문자 A 2번째 문자 A,B,C중 하나.

'A[A-C, G, M-R]' 첫 문자 A 2번째 문자 A-C, G, M-R] 중 하나.

 

문자나 문자 범위를 제외한 문자열 검색

'A[^A, ^B, ^C]' : 첫 문자 A 2번째 문자가 A,B,C가 아닌 문자열.

05. 데이터 그룹화 다루기

GROUP BY 문으로 데이터 그룹화 하기

한개의 열을 그룹화 하는건 중복제거가 되어 선택되는 의미가 있지만

두가지 열을 그룹화만 하는건 큰 의미는 없어보인다. 집게함수와 같이써야 효과를 보는 듯 하다.

라고 생각했지만, 나의 DB에 같은포지션이면서 같은 부서번호를 가진 데이터가 없어서 전체데이터 개수만큼 출려된거였다...

 

SELECT POSITION FROM PROFESSOR GROUP BY POSITION;

 

SELECT POSITION, DEPTNO FROM PROFESSOR GROUP BY POSITION, DEPTNO;

SELECT DEPTNO, POSITION FROM PROFESSOR GROUP BY DEPTNO, POSITION;

 

집계 함수 COUNT로 그룹화한 열의 데이터 개수 확인하기

각 그룹에 몇 개의 행이 있는지 검색하는 COUNT 함수 사용.

SELECT POSITION, COUNT(*) AS CNT FROM PROFESSOR GROUP BY POSITION;

각 포지션이 가지고있는 행 개수 검색.

 

데이터를 살짝 바꿔서 두가지 열을 그룹화하고 카운트를 해보았다!

SELECT POSITION, DEPTNO, COUNT(*) AS CNT FROM PROFESSOR GROUP BY POSITION, DEPTNO ORDER BY POSITION, DEPTNO;

 

짜잔~ DB에 정교수이면서 101부서번호를가진 데이터가 2개! 

 

*주의사항

그룹화 할때는 그들 기준이 되는 열이 꼭 필요하므로 GROUP BY 절에 사용하는 열은 반드시 SELECT 문에서도 사용해야한다.

 

HAVING 문으로 그룹화한 데이터 필터링하기

WHERE 문은 테이블에 있는 열에 적용가능.

HAVING 문은 SELECT문이나 GROUP BY문에 사용한 열에만 적용가능.

GROUP화에 사용하지 않은 열을 HAVING문에 사용하면 오류발생.

 

SELECT POSITION, DEPTNO, COUNT(*) AS CNT FROM PROFESSOR GROUP BY POSITION, DEPTNO HAVING POSITION = '정교수';

DISTNCT  문으로 중복 데이터 제거하기

SELECT DISTINCT POSITION FROM PROFESSOR;

DISTINCT문은 단순히 중복제거다 집계하거나 계산할 수 없다.

GROUP BY문과 같다고 해서 대신쓰면 안된다.

06. 테이블 생성하고 데이터 조작하기

데이터베이스 생성하고 삭제하기

CREATE DATABASE [DB명];

DROP DATABASE [DB명];

 

오라클은 mysql과 달리 데이터 테이블을 관리할 수 있는 데이터 베이스를 만들기가 더 까다롭다.

우선 사용자라는 것을 만들고 권한을 부여해야만 하는데

그리고 나서 데이터베이스를 따로 만드는 것이 아니라 사용자를 중심으로 테이블을 관리한다.

즉 데이터베이스를 만드는 것처럼 사용자를 만들고 사용자에 맞게 테이블을 관리하면 된다.

테이블 생성하고 삭제하기

CREATE TABLE 테이블명 (

열1 자료형,

열2 자료형,

...

)

 

DROP TABLE 테이블명;

가끔 테이블이 삭제되지 않는 경우가 있는데, 현재 삭제하려는 테이블이 다른 테이블과 종속관계면서 부모 테이블인 경우 삭제에 실패한다. => 종속 관계를 제거해야한다.

테이블에 데이터 삽입, 수정, 삭제하기

INSERT 문으로 데이터 삽입하기

INSERT INTO 테이블 [열1, 열2, ...] VALUES [값1, 값2, ...]

개념스키마에 정의된 VALUE를 전부 삽입할땐, 열 이름을 생략해도 된다.

하지만 일부 열에 값을 넣지 않고 싶을땐 VALUE를 삽입할 열 명을 꼭 명시해줘야한다.

 

소괄호를 사용해 한번에 여러개의 데이터를 삽입할 수 있다.

SQL서버에선 쉽표로 구분해서 삽입이 되는데

오라클은...다음과 같이 작성해야한다.

INSERT ALL

INTO PROFESSOR (PROFNO,NAME,ID,POSITION,PAY,HIREDATE)

VALUES(4008,'장나라','captain','정교수',550,to_date('1980-06-23','YYYY-MM-DD'))

INTO PROFESSOR (PROFNO,NAME,ID,POSITION,PAY,HIREDATE)

VALUES(4009,'정동철','captain','정교수',550,to_date('1980-06-23','YYYY-MM-DD'))

SELECT * FROM DUAL;

 

SELECT문 은 왜쓸까?

DUAL 테이블 이란

Dual Table 이란 꼭두각시(dummy) 테이블이다. 틀은 있지만 안에 내용물은 없는 느낌!

Dual Table은 다음과 같은 특징을 갖는다.

• 오라클 자체에서 제공하는 테이블
• 간단하게 함수를 이용해서 계산 결과 값을 확인 할 때 사용
• 시스템 사용자(sys)가 소유하는 오라클의 표준 테이블
• 시스템 사용자(sys)가 소유하지만 어느 사용자에서 접근 가능함.
• 카디널리티(컬럼 수)와 차수()가 모두 1인 dummy 테이블

INSERT ALL 설명문에 따르면 서브쿼리는 필수라고한다.

INSERT 절은 서브 쿼리에 의해 반환되는 각 행(즉, SELECT 문)에 대해 실행된다. SELECT * FROM DUAL은 단일 행을 반환하므로 INSERT 절은 한 번 실행된다......

서브쿼리 dual에 의해 반횐되는 1개의 dummy 행을 데이터가 하나 삽입될 떄 마다 return 해서 반복적으로 삽입을 가능하게 하는 것 같다. 어렵다 -_-

 

UPDATE 문으로 데이터 수정하기

UPDATE PROFESSOR SET PROFNO = 4020 WHERE PROFNO = 4008;

PROFNO 가 4008인 데이터를 PROFNO = 4020으로 업데이트.

UPDATE PROFESSOR SET PROFNO = PROFNO +1;

모든 PROFNO 1더하기.

 

DELETE 문으로 데이터 삭제하기

DELETE 테이블 이름 WHERE [열] = 조건;

외래키로 연결된 데이터  입력, 삭제하기

관계형 데이터베이스는 데이터의 무결성을 유지해야 하므로 부모 테이블에 없는 데이터를 자식 테이블이 가지면 안 되는 것이 원칙

 

CREATE TABLE STUDENT (

profno number(4));

ALTER TABLE STUDENT

ADD CONSTRAINT FK_RPOFNO FOREIGN KEY(PROFNO) REFERENCES PROFESSOR (PROFNO);

INSERT INTO STUDENT VALUES (5000);

자식테이블에 부모테이블에 없는 PROFNO값을 넣으려고 하니 제약조건 에러가 발생했다.

INSERT INTO STUDENT VALUES (4008);

반면에 부모가 가지고있는 값은 삽입성공!

 

데이터 삭제또한 외래키 제약 조건을 잘 생각해야한다.

제약 조건이 걸려있는 상태로 부모데이터를 먼저 삭제하려고하면 에러가 발생한다.

자식의 테이블의 데이터를 먼저 삭제하고 부모 테이블의 데이터를 삭제해야한다.

 

테이블 삭제도 마찬가지로 제약조건이 걸려있는 상태에서는 자식 테이블이 삭제되어야 부모테이블 삭제가 가능하다.

 

만약 자식 테이블의 데이터는 유지하면서 부모 테이블을 삭제하고 싶다면, 제약조건제거를 먼저 하는 방법이 있다.

ALTER TABLE

DROP CONSTRAINTS;

다른 테이블에 검색 결과 입력하기

INSERT [입력할테이블명]

SELECT * FROM [검색테이블명]

WHERE 조건;

 

검색한 결과를 바로 원하는테이블에 INSERT 가능하다.

 

새 테이블 생성하며 검색 결과 입력하기

SELECT * INTO [새로운테이블명]  FROM [검색테이블];

07. SQL Server에서 다루는 자료형 정리하기

다음은 오라클 자료형이다!

 

 

*참조

https://coding-factory.tistory.com/416