Day 16 (SQL)

집합 연산자는 집합의 역할을 하는 연산자이다. 합집합, 교집합, 차집합역할을 하는 연산자들이다. 합집합을 나타낼때는 UNION과 UNION ALL을 사용한다. 둘다 데이터를 모두 출력하는데 UNION은 중복값을 제외하고, UNION ALL은 중복값도 중복으로 출력한다. 교집합의 역할은 intersect 연산자가 하게 되는데 아래 예제에서 보듯이 AND를 사용해서 표기해도 같은 값이 나오고 성능에도 큰 차이가 없어 intersect연산자를 많이 사용하지 않는다. minus는 차집합을 나타내는 연산자인데 이것도 AND연산자로 동일하게 표현이 가능하기 때문에 굳이 사용하지 않는다. 아래 예제는 minus와 intersect를 사용한 것과 AND연산자를 사용했을때 출력값들이다.

minus대신 AND를 사용해도 무방하다

intersect대신 AND를 사용해도 무방하다

 

실습 12

 

1. 화학과 학생과 교수를 검색한다. (집합 연산자를 이용한다.)

 

2. 정교수의 명단과 모델링이 업무인 직원의 이름, 입사일(부임일)을 검색한다.

 

일단 내용을 보기 전에 알아두어야 할것은 함수는 여기에 적힌것이 전부는 아니다. 극히 일부일 뿐이며 사용빈도가 높은 함수의 내용을 적은 것이다. 단일 행 함수에는 문자함수, 숫자함수, 날짜함수, 변환함수, 일반함수가 있는데 굳이 사용하지 않아도 괜찮지만 사용하게 되면 프로그래밍이나 튜닝이 좀더 수월해진다. 먼저 오늘은 문자함수, 숫자함수, 날짜함수까지만 볼것이다.

문자 함수는 말그대로 문자를 변환해주고 수정해주는 등의 역할을 하는 함수이다. 대소문자 변환 함수는 영어에서만 사용 가능한 함수이다. 데이터 안의 값을 사용할때 대소문자 구분이 정확하게 되어있는지 확실하지 않은 경우 하나로 통일 시켜주기 위해 사용한다. 따라서 LOWER(문자열)을 사용하여 문자열을 전부 소문자로 변환하는 것과 UPPER(문자열)을 사용하여 문자열을 전부 대문자로 변환하는것,  INITCAP(문자열)을 사용하여 첫 글자만 대문자로 변환하고 나머지는 소문자로 변환하는 함수가 있다.

문자를 연산하는 함수도 있다.

두개의 문자열을 연결해주는 CONCAT(문자열1, 문자열2)함수,

문자열 내에 지정된 위치의 문자열을 반환하는 SUBSTR(문자열, 시작점, 시작점부터 몇글자까지 출력할것인지)함수,

문자열을 길이로 반환하는 LENGTH(문자열)함수, 문자열을 Byte수로 반환하는 LENGTHB(문자열)함수. 다만 LENGTHB에서 한글도 사용 가능하긴 한데 사용하는 DB설정에 따라서 한글자당 2Byte일수도 3Byte일수도 있다.

지정된 문자의 위치를 반환하는 INSTR(문자열, 검색문자, 시작위치, 횟수)함수. INSTR함수에서 시작위치와 횟수옵션은 생략 가능하다.

접두어나 접미어를 잘라내는 TRIM([leading | trailing | both] 제외문자 FROM 문자열)함수. TRIM함수에서 [leading | trailing | both]부분은 접두어를 잘라낼것인지, 접미어를 잘라낼것인지, 아니면 둘다 잘라낼것인지를 선택하는데 default값은 both이다. 또한 제외문자를 설정하지 않는다면 공백문자를 잘라낸다.

마지막으로 지정된 문자열의 길이만큼 빈 부분에 문자를 채우는 LPAD(문자열, 출력폭, 채움문자), RPAD(문자열, 출력폭, 채움문자)함수가 있다. LPAD는 출력폭 만큼 화면에 결과를 출력하는데 문자열을 적고 모자란 부분에 왼쪽부터 채움문자를 넣어 출력한다. 반면 RPAD는 출력폭 만큼 화면에 결과를 출력하는데 문자열을 적고 모자란 부분에 오른쪽부터 채움문자를 넣어 출력한다. 이때 아래 결과에서 보는것처럼 출력폭이 문자열 보다 작게되면 글자가 짤린다.

입력한 문자열이 출력폭보다 큰 경우

 

또한 문자를 다른문자로 치환 해주는 문자치환함수도 존재한다. TRANSLATE(문자열, 검색문자, 치환문자)는 문자열안에서 검색문자와 일치하는 글자를 찾아 한글자씩 치환할 문자로 변환하는 함수이다. REPLACE(문자열, 검색문자열, 치환문자열)은 문자열 단위로 치환된 값을 반환하는 함수이다.

TRANSLATE함수와 REPLACE함수의 차이점

 

 

실습 13

1. 이름이 두 글자인 학생의 이름을 검색한다.

문자열 안에 컬럼명을 적어야지 '컬럼명'을 적으면 안된다

문자열 란에 '별명'을 적으면 안된다.

 

2. '강'씨 성을 가진 학생의 이름을 검색한다.

 

3. 교수의 지위를 한글자로 검색한다. (ex. 조교수 -> 조)

 

4. 일반 과목을 기초 과목으로 변경해서 모든 과목을 검색한다. (ex. 일반화학 -> 기초화학)

 

5. 만일 입력 실수로 student 테이블의 sname 컬럼에 데이터가 입력될 때 문자열 마지막에 공백이 추가되었다면 검색할 때 이를 제외하고 검색하는 SELECT문을 작성한다.

 

6. 직원의 연봉을 10자리로 검색한다. 단 공백은 임의의 채움 문자로 채워 넣는다.

 

7. 학생의 이름을 검색한다. 단 성이 '심'인 학생은 성을 '사마'로 바꾸어 검색한다.

---

 

단일 행 함수중에 숫자와 날짜 함수가 있는데 이 둘은 상당히 비슷하다. 아래 표로 내용을 살펴보자.

숫자함수		날짜함수
함수명	기능	함수명	기능
ROUND(m,n)	n자리 까지 출력(뒤는 반올림)	ROUND(날짜,'형식')	형식에 맞추어 반올림한 날짜를 출력	YYYY : 연도 MM : 월   DD : 날짜   HH : 시   MI : 분   SS : 초
TRUNC(m,n)	n자리 까지 출력(뒤는 버림)	TRUNC(날짜,'형식')	형식에 맞추어 버림한 날짜를 출력
MOD(m,n)	m을 n으로 나눈 나머지 출력	MONTHS_BETWEEN (날짜1,날짜2)	두 날짜 사이의 기간을 개월수로 출력
POWER(m,n)	m의 n승을 계산	ADD_MONTHS(날짜,n)	날짜에 n개월 후의 날짜를 출력
CEIL(m)	m보다 큰 정수출력(소수올림)	NEXT_DAY(날짜,'요일')	날짜 이후 지정된 요일에 해당하는  날짜를 계산
FLOOR(m)	m보다 작은 가장 큰수 출력(소수버림)	LAST_DAY(날짜)	날짜를 포함한 달의 마지막 날짜를  출력
ABS(m)	m의 절대값 출력
SQRT(m)	m의 제곱근을 출력
SIGN(m)	m이 음수일때 -1, 양수일때 1, 0일때 0 출력

티스토리는 진짜 표그리기 뭣같은홈페이지다

숫자 TRUNC에서 -2를 입력하면 정수단위에서 2째자리까지 표현하고 뒤에는 00으로 한다는 의미이다. 예를들어 TRUNC(1234,-2)하면 1200이 출력된다. 또한 날짜나 숫자끼리의 연산이 가능하다.

날짜 + 숫자	날짜	일수 이후 날짜
날짜 - 숫자	날짜	일수 이전 날짜
날짜 + 숫자 /24	날짜	시간을 더한 날짜
날짜 - 날짜	숫자	두 날짜간의 차(일수)

 

 

1. 교수들이 부임한 달에 근무한 일수는 몇 일인지 검색한다.

 

2. 교수들의 오늘까지 근무한 주가 몇 주인지 검색한다.

 

3. 1991년에서 1995년 사이에 부임한 교수를 검색한다.

 

4. 학생들의 4.5 환산 평점을 검색한다. (단 소수 이하 둘째 자리까지)

 

 

5. 사원들이 일한 날짜에 대해서만 급여를 받는다면 급여가 현재와 동일하다는 조건에서 입사한 달에 급여는 얼마나 지급되었을지 검색한다.

1일부터 일한 사원의 급여가 맞지 않는다

말일에 일한 사원의 급여가 출력되지 않는다

결과출력

 

6. 사원들의 오늘까지 근무 기간이 몇 년 몇 개월 몇일인지 검색한다.

출력 코드

출력 결과