SQL / SELECT 기본 문법

edu day 2

DISTINCT

• 선택한 컬럼에 출력되는 중복되는 값을 제거하여 한번만 출력한다.

SELECT DISTINCT JOB FROM EMP;

WHERE

전 글에서 배웠던 WHERE의 추가적인 부분이다.

SELECT ENAME 이름, DEPTNO 부서번호 FROM EMP WHERE DEPTNO = 20;
SELECT ENAME 이름, DEPTNO 부서번호 FROM EMP WHERE DEPTNO = '20'; -- 문자 20을 숫자 20으로 자동 형변환 후 출력
SELECT ENAME 이름, hiredate 입사일 FROM EMP WHERE hiredate = '81/11/17'; -- 날짜는 특수문자 /가 있기 때문에 ''로 묶어준다.

비교연산자

• '=' 같다
• '>' 보다 크다
• '>=' 보다 크거나 같다
• '<' 보다 작다
• '<=' 보다 작거나 같다
• '<>', '!=', '^='다르다

SELECT ENAME 이름, SAL 월급 FROM EMP WHERE SAL <= 1250;
SELECT ENAME 이름, HIREDATE 입사날짜 FROM EMP WHERE HIREDATE < '81/05/01';
SELECT ENAME 이름, JOB 직업 FROM EMP WHERE JOB != 'SALESMAN';

• BETWEEN ... AND ... : 두 값의 범위에 포함되는

SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL BETWEEN 1250 AND 1600;
SELECT ENAME 이름, HIREDATE 입사날짜 FROM EMP WHERE HIREDATE BETWEEN '81/01/01' AND '81/05/31';
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL BETWEEN 1600 AND 1250; -- X 앞의 값이 뒤의 갚보다 무조건 작아야 된다.

• IN(set) : 괄호 안의 값과 일치하는

SELECT EMPNO, ENAME, JOB FROM EMP WHERE EMPNO IN (7839, 7844, 7876);
SELECT EMPNO 사원번호, ENAME 이름 FROM EMP WHERE ENAME IN ('SMITH', 'ALLEN', 'WARD');

LIKE : 문자의 조합이 같은

- 와일드 카드 문자

• % : %위치에 0개이상의 문자(열)과 대체
  ABCDEFG : 'A%' '%G' '%DE%'

SELECT EMPNO 사원번호, ENAME 이름, SAL 월급 FROM EMP WHERE ENAME LIKE 'J%'; -- J로 시작하는 모든 사원 SELECT
SELECT EMPNO 사원번호, ENAME 이름 FROM EMP WHERE ENAME LIKE '%LA%';
SELECT EMPNO 사원번호, ENAME 이름 FROM EMP WHERE ENAME LIKE '%ES';

• _ : 반드시 하나의 문자만 대체한다.

SELECT EMPNO 사원번호, ENAME 이름 FROM EMP WHERE ENAME LIKE '_L%';	--두번째 문자가 'L'인 모든 사원 SELECT
SELECT EMPNO 사원번호, ENAME 이름 FROM EMP WHERE ENAME LIKE '_____N%';	--여섯번째 문자가 'N'인 모든 사원 SELECT

• 이름에 _가 들어가있다면 어떻게 찾아아할까?

SELECT EMPNO 사원번호, ENAME 이름 FROM EMP WHERE ENAME LIKE '%_%';	--'_'가 들어간 이름을 찾고싶은데 모든 이름이 출력된다.
SELECT EMPNO 사원번호, ENAME 이름 FROM EMP WHERE ENAME LIKE '%$_%' ESCAPE '$';	--ESCAPE를 써서 사용자가 임의의 문자로 지정할 수 있게 하여 원하는 문자로 대체한다.

논리 연산자

AND / OR

SELECT ENAME, JOB, SAL, COMM  
FROM EMP
WHERE JOB = 'CLERK' OR JOB = 'ANALYST' AND COMM IS NULL AND SAL >= 1000 AND SAL <= 3000;
-- AND 연산자가 OR보다 더 빨리 계산되기 때문에 결과 이 제대로 출력되지 않는다.

SELECT ENAME, JOB, SAL, COMM  
FROM EMP
WHERE (JOB = 'CLERK' OR JOB = 'ANALYST') AND COMM IS NULL AND SAL >= 1000 AND SAL <= 3000;
-- OR 계산 부분을 괄호로 묶어줘서 정상적인 출력이 가능하게 함

연산자 우선순위

1. 괄호()
2. NOT 연산자(!=)
3. 비교 연산자(=, <, >)
4. AND
5. OR
   
   

정렬

• 오름차순: 증가, asc
• 내림차순: 감소, desc

- ORDER BY를 사용한다.

• 표준 사용법

SELECT [DISTINCT] { *, column [alias],… }
FROM table
[WHERE 조건식]
[ORDER BY {column , exp } [ASC|DESC] ];

• 예제

SELECT EMPNO 사원번호, ENAME 이름 , SAL 월급 FROM EMP ORDER BY SAL;	-- ORDER BY column만 적으면 오름차순으로 정렬한다. ASC는 생략 가능

SELECT EMPNO 사원번호, ENAME 이름 , SAL 월급 FROM EMP ORDER BY SAL DESC;	-- column뒤에 DESC를 붙이면 내림차순으로 정렬

SELECT empno 사번, ename 이름, sal 월급
FROM emp ORDER BY 월급 desc;	-- alias를 통해서도 order by 사용 가능

SELECT empno 사번, ename 이름, sal 월급
FROM emp ORDER BY 3 desc;	-- select한 column이 앞자리부터 1 2 3... 순서로 되어 이 또한 사용 가능

SELECT empno 사번, ename 이름, sal 월급
FROM emp ORDER BY 1;	-- 사번을 기준으로 오름차순 정렬.

심화과정

SELECT DEPARTMENT_NAME "학과 명", CATEGORY 계열 FROM TB_DEPARTMENT;

SELECT DEPARTMENT_NAME || '의 정원은'|| capacity ||'입니다.' "학과별 정원" FROM tb_department;

-- 국어국문학과에 있는 휴학중인 학생의 이름을 출력.

SELECT DEPARTMENT_NO FROM tb_department WHERE DEPARTMENT_name = '국어국문학과'; -- 국어국문학과의 department_no를 먼저 구한다.
SELECT student_name FROM TB_STUDENT WHERE department_no = '001' AND absence_yn = 'Y';

-- 도서관 장기 연체자를 찾아 이름을 게시함. 대상자 학번은 다음과 같다.(tb_student)

SELECT STUDENT_NAME FROM TB_STUDENT WHERE student_no IN ('A513079','A513091','A513110');

SELECT DEPARTMENT_NAME, CATEGORY FROM TB_DEPARTMENT 
WHERE CAPACITY BETWEEN 20 AND 30;
--WHERE CAPACITY >= 20 AND CAPACITY <= 30;  -- 같은 SELECT와 FROM을 갖고 있다면 주석처리로 한번에 묶어줄 수 있다.

-- 전산상 오류로인해 학과가 없는 사람 출력

SELECT student_name FROM TB_STUDENT WHERE department_no IS NULL;

-- 중복 없이 카테고리 출력

SELECT DISTINCT CATEGORY FROM TB_DEPARTMENT;

-- 02학번 전주 거주자들의 모임. 재학중인 학생들의 학번, 이름, 주민번호 출력.

SELECT STUDENT_NO, student_name, student_ssn FROM tb_student WHERE entrance_date LIKE '02%' AND student_address LIKE '전주%' AND absence_yn = 'N';

-- 계열이 '공학'인 학과 중 입학정원이 20이상, 30이하인 학과의 계열, 학과 이름, 정원 조회. 단 학과이름을 기준으로 오름차순 정렬

SELECT CATEGORY 계열, department_name 학과이름, CAPACITY 정원 FROM tb_department WHERE CATEGORY = '공학' AND CAPACITY BETWEEN 20 AND 30 ORDER BY 2;

---

SQL 함수

단일행 함수

• 모든 행에 대해서 각각 적용되어 행의 개수와 동일한 개수의 결과를 리턴.
• SELECT, WHERE, ORDER BY절에 사용 가능하다.

문자 관련 함수(중요!!)

UPPER : 모든 글자를 대문자로 변환하여 반환한다.
LENGTH : 문자열의 길이를 반환한다.
SUBSTR : 문자열의 일부분을 반환한다.
LPAD, RPAD : 오른쪽/왼쪽 정렬 후 빈 공백에 특정 문제를 태운다.
TRIM : 왼쪽, 오른쪽, 양쪽에 있는 특정문자를 삭제한다.(오라클뿐만 아니라 자바에서도 사용)

INITCAP: 첫 문자만 대문자로 변경한다. 나머지는 소문자로 변경

SELECT INITCAP('ORACLE SQL') FROM DUAL;
==> Oracle Sql

.

UPPER: 모든 대소문자를 대문자로 변경한다.

UPPER('str')

SELECT UPPER('Oracle Sql') from dual;
==> ORACLE SQL
SELECT UPPER(ename) from emp;	--EMP 테이블의 ENAME column이 대문자로 출력된다.

!!중요!!

SELECT * FROM EMP WHERE UPPER(ENAME) = 'ABC'; --ENAME를 대문자로 바꿔서 대소문자 상관 없이 출력할 수 있게한다.

LOWER: 모든 대소문자를 소문자로 변경한다.

LOWER('str')

• 이하동문

SELECT LOWER('Oracle Sql') FROM DUAL;
SELECT LOWER(ENAME) FROM EMP;
SELECT * FROM EMP WHERE LOWER(ENAME) = 'abc'; --소문자로 바꾸기 때문에 소문자로 찾는다.

CONCAT: 두 개의 문자열을 합성한다. 두 개의 매개변수만 사용 가능

SELECT CONCAT('Oracle', 'Sql') from dual;
SELECT 'Oracle' || 'Sql' from dual;
-- 둘 다 결과 값은 'OracleSql'

LENGTH : 문자열의 길이를 숫자값으로 RETURN 한다. 공백도 글자 개수로 인식한다.

SELECT LENGTH('Oracle') FROM DUAL;	-- 값은 6

• 이름의 길이가 5 이상인 이름 출력

SELECT EMPNO 사번, ENAME 이름 FROM EMP WHERE LENGTH(ENAME) >= 5;

• EMP 테이블에서 20번 부서 중 이름의 길이 및 급여의 자릿수를 사원번호, 이름, 이름의 자릿수, 급여, 급여의 자릿수 출력

SELECT EMPNO 사번, ENAME 이름, LENGTH(ENAME) "이름의 자릿수", SAL 급여, LENGTH(SAL)"급여의 자릿수" FROM EMP WHERE deptno = 20;

INSTR :

INSTR( 컬럼명 | 표현식, 검색값, [m,n] )

m은 문자를 검색하기 위한 시작 위치값을 의미한다. n값은 몇 번째로 나오는 문자를 검색하지를 결정한다.

SELECT INSTR('MILLER', 'L', 1, 2), INSTR('MILLER', 'X', 1, 2) FROM DUAL;
--> 시작부터 세어 두번 째 나오는 L값의 위치는 4
	시작부터 세어 두번 째 나오는 X값은 없기 때문에 0 

SUBSTR : 문자열에서 일부분의 문자열을 추출할 때 쓰는 함수. m번째 자리부터 길이가 n개인 문자열을 반환한다.
자바에서도 많이 사용하는 함수이다. 중요!!

SUBSTR(colum | 표현식, m, [n])

SELECT SUBSTR('900303-1234567', 8, 1) FROM DUAL; -> 1
SELECT SUBSTR('900303-1234567', 1, 8) FROM DUAL; -> 900303-1
SELECT SUBSTR('900303-1234567', 8 ) FROM DUAL;	-- n의 값을 빼면 m번째 자리부터 끝까지 출력한다. -> 1234567
SELECT SUBSTR('900303-1234567', -8 ) FROM DUAL;	-- m의 값에 -가 붙으면 뒤에서 부터 8번째까지 센다. 그러나 출력은 앞에부터 출력-> -1234567
SELECT SUBSTR('900303-1234567', 1, 8) || '******' FROM DUAL;	--주민번호 처리 -> 900303-1******

• 입사년도만 나오게 출력

SELECT ENAME 사원명, HIREDATE 입사일, SUBSTR(HIREDATE, 1, 2) 입사년도 FROM EMP;

• 81년도에 입사한 사람들만 출력

SELECT ENAME 사원명, HIREDATE 입사일 FROM EMP WHERE SUBSTR(HIREDATE, 1, 2) = '81';

REPLACE : 특정 문자열을 치환하는 함수

syntax : REPLACE( col | expression, 'str1', 'str2')

SELECT REPLACE('JACK AND JUE', 'J', 'BL') FROM DUAL;
SELECT REPLACE(ENAME, 'J', 'BL') FROM EMP WHERE ENAME LIKE 'J%';

응용

SELECT REPLACE( '900303-1234567', SUBSTR('900303-1234567', 9), '******') FROM DUAL;
->900303-1******

--이는 결국 STUDENT TABLE의 모든 주민번호를 처리하여 출력할 수 있다. 
SELECT REPLACE(STUDENT_SSN, SUBSTR(STUDENT_SSN, 9), '******') FROM TB_STUDENT;