KOSTA 교육 22일차(HAVING, 함수, DDL, DML, 제약조건)

18.11.27(화)

 

배운 내용

 

1. HAVING절
2. 함수
3. DDL
4. DML
5. 제약조건

---

1. HAVING절

HAVING은?
전체 그룹에서 일부의 그룹만 추출하고 싶을 때 사용한다.

• GROUP BY와 함께 사용되고 일반적으로 뒤에 사용된다.
• WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다.

 

• department_id라는 그룹들을 HAVING절로 함수에 대한 조건을 제시한다.
• 월급의 평균이 5000 이하일 경우를 출력하게 된다.
• 평균을 2번째 ROUND함수를 이용해 소숫점에서 반올림을 하여 출력하려 한다.
• 여기서 화학과를 제외하기 위하여 HAVING에 !를 선언하여 출력한다.

2. 함수

함수는?
데이터를 처리하기 위해 사용하고 특정 연산을 처리해주는 것을 말한다.
종류별, 용도별로 구분하여 사용되고 자주사용되는 함수에 대해서 알아보자.

 

문자함수

• LOWER은 소문자로 변환해 준다 DataBase를 database로 변환시킨다.
• UPPER은 이와 반대로 DATABASE로 변환시킨다.
• SUBSTR은 자바에서의 substring과 같은 역할을 한다. abcdef의 인덱스 2부터 시작하여 4까지를 추출한다.
  여기서 주의할 점은 sql에서의 인덱스 값은 0이 아닌 1부터 시작이다.
• LENGTH는 문자열의 길이를 나타낸다.
  퀴즈를 이용하여 풀어보면 과목명의 첫 번째부터 LENGTH)(cname) — 1을 해주면 맨 뒤의 글자에서 하나를 제외하고 출력된다.

 

숫자 함수

• MOD는 나머지값을 return해준다. 10%3은 1이 출력된다.
• ROUND는 반올림값을 return해준다. 432432.99의 10의 자리를 반올림하여 432400이 출력된다
• CEIL, FLOOR는 소수점을 무조건 반올림, 반내림 해준다. CEIL(10.5)는 11 FLOOR(10.5)는 10으로 출력된다.

 

날짜 함수

• SYSDATE는 오늘을 나타내고 오늘에서 입사년도를 빼고 365를 나누어주면 년차가 나오게 된다. 여기서 소수점 첫자리 까지 나타내준다.

 

변환 함수

• TO_CHAR()은 숫자, 날짜를 문자열로 변환한다.
  오늘을 YYYY-MM-DD로 변환하고, 50000000을 %999,999,999로 변환한다.
• 입사날을 YYYY로 변환하는데 2007년인 경우를 출력하도록 한다.

 

• NVL()은 NULL값을 0 또는 다른 값으로 변환시킨다.
  NVL(commission_pct, 0)은 commission_pct에 있는 NULL값들을 0으로 변환시키는 역할을 한다.
• DECODE()는 해당 칼럼에 해당된다면 원하는 칼럼으로 변환시킨다.
  SELECT절에 선언하며 ()안에(칼럼, IF1, THEN1, IF2, THEN2…) 을 선언한다. 여기서 IF와 THEN이 한 짝이 되서 IF면 THEN으로 변환시킨다.
• CASE WHEN은 DECODE와 역할과 유사하다.
  특이점은 CASE에 칼럼을 선언하고 WHEN과 THEN이 짝을 이루어 변환하고 ELSE를 선언하여 나머지를 변환한다.
  다음 CASE를 END 선언한다.

3. DDL

DDL은?
데이터 정의어를 말하며 데이터의 구조를 정의하기 위한 테이블이다.

데이터형의 명령어들

• 문자형 : CHAR(size) = 불변형, VARCHAR2(size) = 가변형
• 숫자형 : NUMBER(size)
• 날짜형 : DATE, TIMESTAMP = 조금 더 상세한 날짜
• 대용량(이미지, 파일) : LOB, BLOB

 

• 테이블 복사는 CREATE절을 사용한다.
• 구조를 복사하는 방법은 WHERE 1 = 0;을 공식화 하여 사용하니 기억하면 좋다.
• 컬럼 추가는 ALTER 명령어와 ADD(컬럼명 데이터형);을 선언한다.
• 컬럼 수정는 ALTER 명령어와 MODIFY(컬럼명 데이터형);을 선언한다.
• 컬럼 삭제는 ALTER 명령어와 DROP COLUMN 컬럼명;을 선언한다.
• 테이블 삭제는 DROP TABLE 테이블명 PURGE;을 선언한다.
• 테이블 이름 변경은 RENAME 구테이블명 TO 신테이블명;을 선언한다.
• 테이블 데이터 삭제는 TRUNCATE TABLE 테이블명;
  DELETE FROM 테이블명;을 선언한다.
  TRUNCATE는 DCL(트랜잭션 적용 불가)은 복구 불가하지만 DELETE는 DML(트랜잭션 적용 가능)이 복구 가능하다.

4. DML

DML은?
데이터 조작어로서 데이터 조회 및 변형을 위한 명령어이다.

DML의 종류

• SELECT : 데이터 조회
• INSERT : 데이터 입력
• UPDATE : 데이터 수정
• DELETE : 데이터 삭제

 

• INSERT문은 INSERT INTO 테이블명( 컬럼1, 컬럼2, 컬럼3… ) VALUES ( ‘값1’, ‘값2’, ‘값3’…. );로 선언한다.
• 여기서 컬럼에 대한 VALUES값을 데이터에 삽입한다.
• UPDATE문은 UPDATE 테이블명 SET 컬럼명 = ‘수정할 값1’ WHERE 컬럼1 = ‘값1’로 선언한다.
• 퀴즈를 예로 들면 SET에 salary라는 컬럼명이 들어가고 수정할 값에는 10% 인상값, WHERE절에는 salary의 SET에 대한 수정될 조건을 작성한다.
• DELETE문은 DELETE 테이블명 WHERE 컬럼1 = ‘값1’으로 선언한다.
• 퀴즈를 예로 들면 dept01 테이블의 department_name의 IT Service 값을 DELETE 한다.

5. 제약조건

제약조건은?
데이터를 추가, 삭제, 수정하는 가운데 DB의 무결성을 유지하기 위해 사용한다.

제약조건의 특징

• 테이블을 생성할 때 지정이 가능하며 생성 후 ALTER를 통해 추가가 가능하다.
• CONSTRAINT를 통해 쉽게 참조가 가능하다.
• NOT NULL제약조건은 반드시 컬럼 레벨에서 가능하다.

 

• CREATE TABLE 테이블명 으로 테이블을 생성하고 각 컬럼에 따른 데이터형을 선언한다.
• 여기서 INSERT INTO로 VALUES를 NULL값을 포함해서 넣어도 오류가 나지 않는다. 이유는 NOT NULL 선언을 해주지 않았기 때문에 NULL값도 오류가 나지 않는 것이다.
• 다음 테이블에서 NOT NULL값을 선언해준다면 NULL값을 삽입하면 오류가 나게 된다.
  하지만 여기서 같은 NUMBER값을 삽입하면 같은 NUMBER값이 2개가 되기 때문에 옳지 않다.
• 여기서 필요한 조건이 UNIQUE 조건으로 중복을 방지해주는 역할을 한다.
• 칼럼명 뒤에 UNIQUE를 제한하여 VALUES값이 동일할 때 오류가 나게끔 유도한다.
• PRIMARY KEY조건은 NOT NULL과 UNIQUE를 합친 역할을 한다.
  하지만 하나의 테이블에 하나만 사용이 가능하다는 것을 명심!
• 칼럼명 뒤에 PRIMARY KEY를 제한하여 중복과 NULL값이 오류날 수 있도록 유도한다.
• FOREIGN KEY는 외래키라는 의미고 기본키를 참조하는 컬럼을 말한다. 이 외래키가 참조하는 기본키의 컬럼과 데이터형이 일치해야하고 참조되는 기본키는 삭제가 불가능하다.
• REFERENCES 테이블(칼럼)을 선언하여준다.

 

• 테이블 레벨 방식으로서 CONSTRAINT 를 통해 생성할 수 있다.
• PRIMARY KEY 제약조건 방법 
  CONSTRAINT 테이블명_칼럼명_이름 PRIMARY KEY(칼럼명)
• FOREIGN KEY 제약조건 방법
  CONSTRAINT 테이블명_칼럼명_이름 FOREIGN KEY(칼럼명)
  REFERENCE 테이블(칼럼)
• 테이블 수정 방식은 가장 많이 사용되고 있는 방식이다.
• 생성된 테이블에 대해 ALTER TABLE 명령어로 추가를 하는 방법으로PRIMARY KEY를 추가하려면 ‘ALTER TABLE 테이블명’ 을 추가한
  ALTER TABLE 테이블명 
  ADD CONSTRAINT 테이블명_칼럼명_이름 PRIMARY KEY(칼럼명)
  위처럼 나타낸다.
• FOREIGN KEY를 추가하려면 ‘ALTER TABLE 테이블명’ 을 추가한
  ALTER TABLE 테이블명 
  ADD CONSTRAINT 테이블명_칼럼명_이름 PRIMARY KEY(칼럼명)
  REFERENCE 테이블(칼럼)
  위처럼 나타낸다.
• CHECK 제약조건은 의도된 데이터만 입력 받기 위하여 사용한다.
• 기존 테이블에 gender조건을 입력하여 원하는 데이터를 입력한다.
• DEFAULT 제약조건은 기존 테이블에 DEFAULT를 선언하여 조건에 맞지 않는 값들에 대한 값을 선언해준다.
• 여기서 제약조건을 선언할 때 칼럼을 2개 이상 선언할 수 있다는 것도 알아두자.
• 제약조건 삭제는
  ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명; 으로 삭제한다.

오늘 교육 간 느낀 점

오늘은 어제보다 훨씬 많은 양의 내용을 배웠다. 확실히 난이도가 많이 올라갔고 너무 많은 양이 한 번에 들어오다 보니까 서로의 개념이 섞여서 헷갈리기도 하다…

제약조건, DML 등 중요한 내용을 많이 배워서 배운 것을 정리하고 블로그에 투고하는 것도 오래걸리고 헷갈리는 부분은 다시 찾아보고 개념을 정리하였다.

 

SQL을 실습하면서 느끼는 것이 조금이라도 실수를 하면 에러를 찾기가 힘들어 정신 제대로 차리고 코딩을 해야하고 또 개인적으로 문법이 완전히 다른게 아니라 조금씩 비슷해서 오히려 헷갈린다고 느낀다.

 

오늘 join을 조금 배웠는데 내일 한 번에 정리해서 공부하려고 한다.
오늘은 DML과 제약조건에 집중해서 학습하려 한다.
오늘도 고생했고 화이팅이닷!