SQL
DB 및 테이블 개념
DDL, DML
SELECT절
SELECT컬럼명FROM테이블명
이런 형식으로 사용.
테이블 내에서 데이터를 조회하는 데 사용하는 명령어.
실습
id, pw hr로 작성
기본적으로 생성된 테이블을 다음과 같은 명령어로 조회해보기.
모든 데이터 조회 (SELECT *)
SELECT * FROM employees;출력 :
나머지 데이터는 너무 길어져서 생략..
EMPLOYEE_ID FIRST_NAME LAST_NAME PHONE_NUMBER 100 Steven King SKING 515.123.4567
employees 테이블에서 모든(*) 데이터를 조회(SELECT) 하기
들여쓰기
아래와 같이 여러 줄에 나눠 들여 쓸 수도 있다.
SELECT FIRST_NAME, PHONE_NUMBER FROM employees;출력 :
FIRST_NAME PHONE_NUMBER Steven 515.123.4567 Neena 515.123.4568 Lex 515.123.4569
employees 테이블에서 FIRST_NAME과 PHONE_NUMBER 컬럼을 조회한다.
조회된 컬럼에 사칙연산
SELECT employee_id, first_name, last_name, salary, salary + salary*0.1 FROM employees;출력 :
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY SALARY+SALARY*0.1 100 Steven King 24000 26400
대략적으로 무엇을 조회하는지는 이제 알 것이고, 밑을 보면 컬럼을 조회하는 위치에서 사칙연산 (+, -, *, /) 이 가능하다. (salary + salary * 0.1)
널(NULL) 상태인 컬럼
SELECT department_id, commission_pct FROM employees;출력 :
DEPARTMENT_ID COMMISSION_PCT 90 null 90 null 90 null
기본 생성된 employees 테이블에 저렇게 두 개의 컬럼을 조회하면, commission_pct 컬럼이 NULL인 컬럼들이 있는데, 그 컬럼들의 경우 값이 0인게 아니라 진짜로 그냥 없는 컬럼임을 잊지 말자.
컬럼에 별칭 붙이기 (AS)
컬럼명 as 별명 과 같이 사용하여, 특정 컬럼의 이름을 별명으로 변경하여 출력할 수 있다.
SELECT first_name as 이름, last_name as 성, salary as 급여 FROM employees;출력 :
이런 식으로 컬럼이 조회된다.
컬럼을 연결하여 출력하기 (||)
SELECT first_name || ' ' || last_name || '''s salary is $' || salary as 급여내역 FROM employees;출력 :
중복된 컬럼 제외하고 조회하기 (DISTINCT)
SELECT DISTINCT department_id FROM employees;출력 :
ROWNUM, ROWID
SELECT ROWNUM, ROWID, employee_id FROM employees;출력 :
- ROWNUM : 쿼리에 의해 반환되는 행의 번호 출력
- ROWID : DB에서 행의 주소 반환
WHERE절
실습
JOB_ID 가 IT_PROG인 사람 조회하기
SELECT first_name, last_name, job_id FROM employees WHERE job_id = 'IT_PROG';
JOB_ID가 IT_PROG인 사람만이 조회되었다.
수치를 지정하여 데이터 조회하기
SELECT * FROM employees WHERE salary >= 15000;
급여가 15000 이상인 사람만이 조회되었다.
데이터 행 제한 (BETWEEN, IN, LIKE)
BETWEEN
SELECT * FROM employees WHERE salary BETWEEN 15000 AND 20000;
SELECT * FROM employees WHERE hire_date BETWEEN '03/01/01' AND '03/12/31';
부등호와 AND를 사용하여 >=15000 AND <=20000 이렇게 할 수도 있고, 대량의 데이터를 다룰 땐 부등호와 AND로 묶은 경우가 조금 더 빠르다고 한다.
아래 부분은 고용 날짜가 03년 1월 1일부터 ~ 03년 12월 31일 사이에 고용된 사람들을 조회하고 있다.
IN
SELECT * FROM employees WHERE job_id IN ('IT_PROG', 'AD_VP');
job_id가 IT_PROG 나 AD_VP인 사람을 조회하고 있다.
LIKE
SELECT first_name, hire_date FROM employees WHERE hire_date LIKE '03%';
SELECT first_name, hire_date FROM employees WHERE hire_date LIKE '%15';
SELECT first_name, hire_date FROM employees WHERE hire_date LIKE '%05%';
SELECT first_name, hire_date FROM employees WHERE hire_date LIKE '___05%';
employees 테이블에서 first_name과 hire_date를 가져오되, 고용된 날짜가 03년도인 사람을 조회하기 위해서 03으로 시작하고 뒤가 어떻게 되어있건 상관없는 식의 데이터를 조회하라는 쿼리를 작성했다.
두 번째의 경우 15일에 고용된 사람을 찾기 위해 작성된 쿼리문의 예시이다.
세 번째의 경우 05가 포함된 (연, 월, 일 상관 없이) 부분을 찾기 위한 쿼리문을 작성했다.
네 번째는 5월에 고용된 사람들을 조회하기 위해, 연 월 일 작성 방식 중 앞의 세 자리를 건너뛰고 그 뒤부터 05로 시작하는 부분을 조회하면 '월' 부분에 해당하는 부분을 찾아 일치시킬 수 있어 _ 를 사용하여 작성한 쿼리문이다.
IS NULL, IS NOT NULL
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE commission_pct IS NOT NULL;
manager_id가 NULL인 사람을 조회하는 쿼리문의 예시이다.
아래의 경우 commission_pct가 NULL이 아닌 사람을 조회하기 위해 IS NOT NULL 을 사용했다.
AND, OR
SELECT * FROM employees WHERE job_id = 'IT_PROG' OR job_id = 'FI_MGR' AND salary >= 6000;
SELECT * FROM employees WHERE (job_id = 'IT_PROG' OR job_id = 'FI_MGR') AND salary >= 6000;
첫 번째는, job_id가 IT_PROG 이거나 FI_MGR인 사람을 찾고, salary가 6000 이상인 사람을 조회하려고 했다.
보면 알 수 있지만, SALARY가 6000이상만 조회되지는 않았다. 왜냐하면 OR과 AND를 동시에 사용하는 경우, AND가 우선 연산되어 IT_PROG의 경우 salary가 6000이 넘지 않는 사람도 조회되고 있다.
따라서 두 번째와 같이 조건을 괄호 안에 작성하게 되면, OR 연산의 우선순위를 끌어올릴 수 있고 비로소 원하는 결과를 얻을 수 있다.
데이터의 오름차순(ASC), 내림차순(DESC) 정렬
ASC
SELECT * FROM employees ORDER BY hire_date ASC;
DESC
SELECT * FROM employees ORDER BY hire_date DESC;
ORDER BY 는 데이터를 전부 조회한 다음 정렬을 하기 때문에 맨 끝에 넣도록 하여야 한다.
연습문제
-- 1. 모든 사원의 사원번호, 이름, 입사일, 급여 출력
SELECT employee_id, first_name, hire_date, salary
FROM employees;
-- 2. 모든 사원의 이름과 성을 붙여 출력. 열 별칭은 name으로.
SELECT first_name || ' ' || last_name as "name"
FROM employees;
-- 3. 50번 부서 사원의 모든 정보를 출력
SELECT *
FROM employees
WHERE department_id = '50';
-- 4. 50번 부서 사원의 이름, 부서번호, 직무아이디를 출력하세요
SELECT first_name, department_id, job_id
FROM employees
WHERE department_id = 50;
-- 5. 모든 사원의 이름, 급여 그리고 300달러 인상된 급여를 출력하세요.
SELECT first_name, salary, salary + 300
FROM employees;
-- 6. 급여가 10000보다 큰 사원의 이름과 급여를 출력하세요.
SELECT first_name, salary
FROM employees
WHERE salary > 10000;
-- 7. 보너스를 받는 사원의 이름과 직무, 보너스율을 출력하세요.
SELECT first_name, job_id, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
-- 8. 2003년도 입사한 사원의 이름과 입사일 그리고 급여를 출력하세요 (BETWEEN 연산자 사용)
SELECT first_name, hire_date, salary
FROM employees
WHERE hire_date BETWEEN '03/01/01' AND '03/12/31';
-- 9. 2003년도 입사한 사원의 이름과 입사일 그리고 급여를 출력하세요. (LIKE 연산자 사용)
SELECT first_name, hire_date, salary
FROM employees
WHERE hire_date LIKE '03%';
-- 10. 모든 사원의 이름과 급여를 급여가 많은 사원부터 적은 사원순서로 출력하세요.
SELECT first_name, salary
FROM employees
ORDER BY salary DESC;
-- 11. 위 질의를 60번 부서의 사원에 대해서만 질의하세요. (컬럼: department_id)
SELECT first_name, salary
FROM employees
WHERE department_id = 60
ORDER BY salary DESC;
-- 12. 직무아이디가 IT_PROG 이거나, SA_MAN인 사원의 이름과 직무아이디를 출력하세요.
SELECT first_name, job_id
FROM employees
WHERE job_id IN ('IT_PROG', 'SA_MAN');
-- 13. Steven King 사원의 정보를 "Steven King 사원의 급여는 24000달러입니다" 형식으로 출력하세요.
SELECT first_name || ' ' || last_name || ' 사원의 급여는 ' || salary || '달러입니다'
FROM employees
WHERE first_name = 'Steven' AND last_name = 'King';
-- 14. 매니저(MAN) 직무에 해당하는 사원의 이름과 직무아이디를 출력하세요. (컬럼:job_id)
SELECT first_name, job_id
FROM employees
WHERE job_id LIKE '%MAN';
-- 15. 매니저(MAN) 직무에 해당하는 사원의 이름과 직무아이디를 직무아이디 순서대로 출력하세요.
SELECT first_name, job_id
FROM employees
WHERE job_id LIKE '%MAN'
ORDER BY job_id ASC;문자열 함수
lower, initcap, upper
lower, initcap, upper
-- lower(소문자), initcap(앞글자만 대문자), upper(대문자) /* dual 이라는 테이블은 sys가 소유하는 오라클의 표준 테이블로서, 오직 한 행에 한 컬럼만 담고 있는 dummy 테이블이다. 일시적인 산술 연산이나 날짜 연산 등을 주로 사용한다. 모든 사용자가 접근할 수 있다. */ SELECT 'abcDEF', lower('abcDEF'), upper('abcDEF') FROM dual; SELECT last_name, lower(last_name), initcap(last_name), upper(last_name) FROM employees; /* austin은 Austin으로 저장되어 있어 소문자로만 검색을 하면 나오지 않는다 따라서 lower 함수를 통해 결과를 소문자로 바꾸고 찾으면 검색이 된다. */ SELECT last_name FROM employees WHERE lower(last_name) = 'austin';
length, instr
length, instr
-- length(길이), instr(문자 찾기. 없을 경우 0값이 나오고 있는 경우 인덱스값이 나옴) SELECT 'abcdef' AS ex, LENGTH('abcdef'), INSTR('abcdef', 'a') FROM dual;
substr, concat
substr, concat
-- substr(문자열 자르기), concat(문자 연결) 1부터 시작 SELECT 'abcdef' AS ex, SUBSTR('abcdef', 1, 4), CONCAT('abc', 'def') FROM dual; SELECT CONCAT(first_name, last_name) AS fullname FROM employees;
LPAD, RPAD
LPAD, RPAD
-- LPAD, RPAD (좌, 우측 지정 문자열로 채우기) SELECT LPAD('abc', 10, '*') FROM dual; SELECT RPAD('abc', 10, '*') FROM dual;
