/*
여러줄 주석
*/
-- 한줄 주석
-- 현재 사용자
SHOW USER;
-- 현재 사용자의 모든 테이블
SELECT * FROM TAB;
--SQL 구조
-- 동사 목적어 테이블
select * from employees; -- * : 와일드 카드 (전부 다)
-- 일반적으로 아래처럼 쓰는 것을 권장한다. (디버깅 때문에 찾기 쉽게)
select *
from employees;
-- 특정 열만 조회
select employee_id, first_name, last_name -- 2번
from employees; -- 1번
-- 특정 열을 중심으로 정렬
select employee_id, first_name, last_name -- 2번
from employees -- 1번 -- 세미클론 지우기 안지우면 여기서 끝.
order by employee_id DESC; -- DESC(EDNDENT) 내림차순
select employee_id, first_name, last_name -- 2번
from employees -- 1번 -- 세미클론 지우기 안지우면 여기서 끝.
order by employee_id ASC; -- ASC(EDNDENT) 내림차순(기본)
-- 중복값 제거
select DISTINCT job_id
from employees;
--AS(Alias)
select employee_id AS 사원번호, first_name AS 이름, last_name AS 성
from employees;
select employee_id AS 사원번호, first_name AS 이름, last_name AS 성, email AS 이메일, phone_number AS 핸드폰번호, HIRE_DATE AS 날짜,
job_id AS 직업아이디, salary AS 봉급
from employees;
-- AS 생략가능
select employee_id 사원번호, first_name 이름, last_name 성, email 이메일, phone_number 핸드폰번호, HIRE_DATE 날짜,
job_id 직업아이디, salary 봉급
from employees;
-- 데이터 값 연결
select employee_id 직원아이디, first_name || last_name 사원명
from employees;
-- 산술 처리하기
select employee_id, salary
from employees;
select employee_id, salary, salary+500, salary+100, (salary*1.1)/2
from employees;
select employee_id 사원번호,
salary 급여,
salary+500 추가급여,
salary-100 인하급여,
(salary*1.1)/2 조정급여
from employees;
-- where 조건절
select * -- 3번
from employees -- 1번
where employee_id = 100; --2번
-- 문제 salary가 3000이상인 사람 출력하기
select * -- 3번
from employees -- 1번
where salary >= 3000; --2번
-- 문제 위의 결과를 salary기준으로 오름차순 정리하시오.
select *
from employees
where salary >= 3000
order by salary ASC;
-- 비교
-- 문자열 비교
select *
from employees
where first_name = 'David' ; -- 문자열 비교시 싱글쿼테이션(' ')을 써야 한다. 대소문자 구분해야 함.(david 안됨.)
-- id가 100인 직원
select *
from employees
where employee_id = 100;
-- Between 연산자(~사이)
select * -- 3번
from employees -- 1번
where salary BETWEEN 10000 AND 20000; -- 10000만 이상에서 20000이하
-- where salary BETWEEN 20000 AND 10000; 큰수가 뒤에 와야 한다.
-- >=, <=
select * -- 3번
from employees -- 1번
where salary >= 10000 AND salary <= 20000; -- 10000만 이상에서 20000이하
-- IN 연산자 (~중에)
select *
from employees
where salary IN(10000, 17000, 24000);
-- Like(~처럼, ~같이)
select *
from employees
where job_id LIKE 'AD___'; -- _ : 와일드카드, 한글자, 3글자!
select *
from employees
where job_id LIKE 'AD%'; -- % : 이하 모든 문자
-- IS NULL(~이다)
select *
from employees
where manager_id IS NULL;
-- 논리 연결(연봉이 4천 초과이고 잡아이디가 아래와 같은 것 출력)
select *
from employees
where salary > 4000
and job_id ='IT_PROG';
-- OR(또는 , 앞 조건 또는 뒤 조건 만족 하는 것 출력)
select *
from employees
where salary>4000
and job_id = 'IT_PROG'
or job_id = 'FI_ACCOUNT';
-- 부정 연산자(같지 않다.)<>(표준), 오라클!=, ^=
select *
from employees
where employee_id <> 105;
select *
from employees
where manager_id IS NOT NULL;
-------------------------------------------------------------------------
select *
from employees;
select employee_id 직원아이디, LAST_NAME || first_name 이름
from employees;
SELECT
*
FROM employees
WHERE salary >= 10000 AND salary <= 20000;
SELECT
*
FROM employees
where salary>15000
AND job_id Like 'SA%'
or first_name LIKE 'A%';
-------------------------------[18일]------------------------------------
select employee_id 사원번호,
first_name || ' ' || last_name 사원명,
email || '@' || 'company.com ' AS 이메일
from employees;
-- [4장] --
-- 4 . 2 . 1
-- [예제 4-1] --
SELECT
last_name,
LOWER(LAST_name), -- 소문자로 변환
UPPER(LAST_name), -- 대문자로 변환
email,
INITCAP(email) -- INITCAP 적용(첫번째 글자만 대문자로 변환)
FROM employees;
-- [예제 4-2] --
select job_id, SUBSTR(job_id, 1, 2) -- SUBSTR(열(컬럼), 시작위치, 길이), 문자열 중 일부분 선택
FROM employees;
-- [예제 4-3] --
select job_id, REPLACE(job_id, 'ACCOUNT', 'ACCNT') -- 적용결과 특정 문자열을 찾아 바꾼다. 예를 들어 A를 찾아 E로 바꾼다.
FROM employees;
-- [예제 4-4] --
select first_name, LPAD(first_name, 12, '*') -- LPAD 적용결과, 왼쪽부터 특정 문자로 자리를 채운다. -- 12개를 기준으로 부족한 문자는 *(에스터리스크)로 채운다.
from employees;
select first_name, RPAD(first_name, 12, '*') -- RPAD 적용결과, 오른쪽부터 특정 문자로 자리를 채운다. -- 12개를 기준으로 부족한 문자는 *(에스터리스크)로 채운다.
from employees;
-- [예제 4-5] --
SELECT job_id,
LTRIM(job_id, 'F') LTRIM 적용결과, -- 왼쪽에서 F를 찾아 삭제한다.
RTRIM(job_id, 'T') RTRIM 적용결과 -- 오른쪽에서 T 찾아 삭제한다.
FROM employees;
-- 공백제거
select 'start' || TRIM(' - space - ') || 'end' -- 문자의 앞뒤 공백을 제거한다.(중간 공백은 제거하지 않는다.)
from dual; -- dual은 더미 테이블이다(그냥 비어있는 테이블이라고 생각하면 됨.)
-- [예제 4-6] --
-- ROUND : 숫자를 반올림 한다. 0번째가 .0자리 > 1번째가 .00자리 순으로 ~, -1은 0. 자리 순으로 ~
SELECT salary,
salary/30 일급,
ROUND(salary/30, 0), --적용결과 0
ROUND(salary/30, 1), --적용결과 1
ROUND(salary/30, -1) --적용결과 MINUSI
FROM employees;
-- [예제 4-7] --
-- TRUNC : 절삭, 숫자를 절삭한다. 0이 소수점 첫째 자리.
SELECT salary,
salary/30 일급,
TRUNC(salary/30,0), -- 적용결과 0
TRUNC(salary/30,1), -- 적용결과 1
TRUNC(salary/30,-1) -- 적용결과 MINUSI
FROM employees;
4. 2. 3
-- 날짜
-- Date + Number -> Date
-- Date - Number -> Date
-- Date - Date > 일수
-- Date + Date/24 -> Date
select sysdate, TO_CHAR(sysdate, 'YY/MM/DD/HH24:MI'), -- 날짜 -> 문자열
SYSDATE +1,
SYSDATE -1,
TO_DATE('20171202') - TO_DATE('20171201') -- 문자열 -> 날짜
from dual;
-- [예제 4-8] --
-- MONTHS_BETWEEN : 두 날짜 사이의 개월 수 계산
SELECT SYSDATE, hire_date, MONTHS_BETWEEN(SYSDATE, hire_date) 적용결과
FROM employees
WHERE department_id = 100;
-- [예제 4-9] --
SELECT hire_date,
ADD_MONTHS(hire_date, 3),
ADD_MONTHS(hire_date, -3)
FROM employees
WHERE employee_id BETWEEN 100 AND 106;
-- NEXT_DAY : 돌아오는 요일의 날짜
SELECT hire_date,
NEXT_DAY(hire_date, '금요일') 적용결과_문자지정,
NEXT_DAY(hire_date, 6) 적용결과_숫자지정
FROM employees
WHERE employee_id BETWEEN 100 AND 106;
-- LAST_DAY : 돌아오는 월의 마지막 날짜
SELECT hire_date,
LAST_DAY (hire_date) 적용결과_문자지정
FROM employees
WHERE employee_id BETWEEN 100 AND 106;
-- ROUND, TRUNC
SELECT hire_date
from employees
where employee_id = 100;
-- 오라클의 날짜 포맷 확인 명령어
select VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT';
SELECT hire_date,
ROUND(hire_date, 'MONTH' ),
ROUND(hire_date, 'YEAR' )
from employees
where employee_id = 100;
SELECT 1+'2' -- 자동 형변환
FROM dual;
select TO_CHAR(SYSDATE, 'HH:MI:SS PM'), -- 날짜 -> 문자
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS PM') 날짜와시간조합
FROM dual;
select TO_CHAR(SYSDATE, 'HH-MI--SS PM') 시간형식,
TO_CHAR(SYSDATE, ' "날짜 :" YYYY/MM/DD "시간 :" HH:MI:SS PM') 날짜와시각표현
FROM dual;
-- TO_NUMBER : 숫자로
select TO_NUMBER('123')
FROM dual;
-- TD_DATE : 날짜로
select TO_DATE('20271007', 'YYMMDD')
FROM dual;
-- 일반 함수(중요)
SELECT *
FROM employees
ORDER BY commission_pct;
SELECT salary * commission_pct
FROM employees
ORDER BY commission_pct;
SELECT salary * NVL (commission_pct, 1) -- commission_pct -> NULL 값만 1로 변경
FROM employees
ORDER BY commission_pct;
-- DECODE : 조건 논리 처리하기(많이 쓰임)
SELECT first_name,
last_name,
department_id,
salary 원래급여,
-- if (department_id==60) salary*1.1
-- else salary
DECODE(department_id, 60, salary*1.1, salary) 조정급여,
DECODE(department_id, 60, '10%인상', '미인상') 인상여부
from EMPLOYEES;
-- CASE 표현식 : 복작한 논리 조건 처리하기
SELECT employee_id, first_name, last_name, salary,
CASE
WHEN salary >= 9000 THEN '상위급여'
WHEN salary BETWEEN 6000 AND 8999 THEN '중위급여'
ELSE '하위급여'
END AS 급여등급
FROM employees
WHEN job_id = 'IT_PROG';
-- [예제 4-16] --
select employee_id,
salary,
RANK() OVER(ORDER BY salary DESC),
DENSE_RANK() OVER(ORDER BY salary DESC),
ROW_NUMBER() OVER(ORDER BY salary DESC)
FROM employees;
-- 그룹함수 : 여러행에 대해 함수가 적용되어 하나의 결과를 나타내는 함수
select * from employees;
-- 여기서 중요한 것은 전체 행을 계산하여 하나의 결과값을 만든다. (중요!!)
select count(salary)
from employees;
select sum(salary)
from employees;
select AVG(salary)
from employees;
SELECT MAX(salary) 최대값, MIN(salary) 최소값, MAX(first_name) 최대문자값, MIN(first_name) 최소문자값
FROM employees;
4. 3. 2
-- [예제 4-21] --
-- GROUP BY : 열을 지정하여 그룹화하는 명령어, 그룹으로 묶기
-- 다중행 != 한개의 결과행 출력
SELECT job_id, sum(salary) 직무별_총급여, AVG(salary) --3
FROM employees --1
GROUP BY job_id --2
order by 직무별_총급여 desc; --4
SELECT job_id, sum(salary) 직무별_총급여, AVG(salary) --4
FROM employees --1
WHERE employee_id >=10 --2
GROUP BY job_id --3
order by 직무별_총급여 desc; --5
SELECT job_id, sum(salary) 직무별_총급여, AVG(salary) 직무별_평균급여 --4
FROM employees --1
WHERE employee_id >=10 --2
GROUP BY job_id --3
order by 직무별_총급여 desc, 직무별_평균급여 desc; --5, 1순위 : 직무별_총급여, 2순위 : 직무별_평균급여 1순위 같은 값 나오면 2순위로 매김.
SELECT job_id job_id_대그룹,
manager_id manager_id_중그룹,
SUM(salary) 그룹핑_총급여,
AVG(salary) 그룹핑_평균급여
FROM employees
WHERE employee_id >=10
GROUP BY job_id, manager_id
ORDER BY 그룹핑_총급여 DESC, 그룹핑_평균급여;
-- GROUP BY ~HAVING(조건)
SELECT job_id 직무, SUM(salary) 직무별_총급여, AVG(salary) 직무별_평균급여
FROM employees
WHERE employee_id >=10
GROUP BY job_id
-- GROUP BY 조건 추가
HAVING SUM(salary) > 30000
ORDER BY 직무별_총급여 DESC, 직무별_평균급여;
-------------------------------[19일]------------------------------------
--JOIN(조인) : 현업에서 많이 쓰임.
-- 1. 동등조인(EQUI JOIN, INNER JOIN) == 내부조인
-- 중복된 내용을 출력한다, 동일한 내용만 출력
SELECT * FROM employees;
select * from tab;
SELECT *
FROM employees;
select *
from departments;
select *
from employees, departments
WHERE employees.department_id = departments.department_id
AND employees.manager_id = departments.manager_id;
select *
from employees A, departments B
WHERE A.department_id = B.department_id
AND A.manager_id = B.manager_id;
select A.employee_id, A.department_id, B.department_name, C.location_id, C.city
from employees A, departments B, locations C
WHERE A.department_id = B.department_id
AND B.location_id = C.location_id;
-- 2. 외부 조인(Outter Join)
-- 중복되는 내용 뿐만 아니라 중복되지 않은 내용도 포함.
select count (*)
from employees; -- 107
select count(*)
from departments; -- 27
select count(*) -- 조인된 건수
from employees A, departments B
WHERE A.department_id = B.department_id; -- 106, 모든 데이터 중에 한개가 누락
select *
from employees A, departments B
WHERE A.department_id = B.department_id(+);
select a.employee_id, a.first_name, a.last_name, b.department_id, b.department_name
from employees A, departments B
WHERE A.department_id = B.department_id(+) -- 부쪽한 쪽에 +를 넣어줘야함. 많은 쪽에 넣으면 쓸데 없는 데이터가 많이 나옴.
ORDER BY employee_id; -- 107, 포함되지 않았던 내용도 포함되어서 나옴.
-- 3. SELF JOIN(자체 조인)
-- 자신의 테이블(1개)에서 조인 실행
SELECT *
FROM employees A, employees B;
SELECT a.employee_id, a.first_name, a.last_name, a.manager_id, b.first_name| | ' ' | | b.last_name 매니저_이름
FROM employees A, employees B
WHERE A.manager_id = B.employee_id
ORDER BY A.employee_id;
-- 집합연산자
-- 1) UNION : 합 집합
SELECT department_id
FROM employees
UNION
SELECT department_id
FROM departments;
-- 오라클의 외부 조인에서 left side와 right side 조인을 동시에 허용하는 Full outer Join이 없다.
-- 이런한 문제를 해결하는 방안으로 UNION이 사용된다.
-- MYSQL은 Full outer Join이 가능하다.
-- WHERE A.department_id(+) = B.department_id(+) -- 허용 안됨.
-- 2) UNION ALL : 합 집합 + 중복 내용 모두 출력
SELECT department_id
FROM employees
UNION ALL
SELECT department_id
FROM departments
ORDER BY department_id;
-- 3) INTERSECTOR : 중복내용
SELECT department_id
FROM employees
INTERSECT
SELECT department_id
FROM departments
ORDER BY department_id;
-- 4) MINUS : 첫번째에서 두번째 뺀 내용
SELECT department_id
FROM departments
MINUS
SELECT department_id
FROM employees;
-- 서브 쿼리(Sub Query) : 메인 쿼리 + 서브 쿼리 조합
-- employees 테이블의 last_name이 'De Haan'인 직원과 Salary가 동일한 직원은 누가 있는지?
-- 1) employees 테이블의 last_name이 'De Haan'인 직원의 Salary 60
-- 2) Salary가 동일한 직원 60인 직원을 찾는다.
-- 메인 커리
select *
from employees A
WHERE A.salary = 17000;
-- 서브 쿼리
select salary
from employees
where last_name = 'De Haan'; -- 문자열 ' ', 대소문자 엄격 구분
-- 단일 행 서브 쿼리
select *
from employees A
WHERE A.salary = (select salary
from employees
where last_name = 'De Haan');
-- 다중 행 서브 쿼리
-- 1) 각 부서별 salary가 낮은 값
-- 12개, 다중행
SELECT MIN(salary) 최저급여
FROM employees
GROUP BY department_id;
-- 2) 해당 직원
-- IN : 다중행 비교 연산자
select *
from employees
where employees.salary IN ( SELECT MIN(salary) 최저급여
FROM employees
GROUP BY department_id)
ORDER BY salary DESC;
select *
from employees
where employees.salary IN ( SELECT MIN(salary) 최저급여
FROM employees
GROUP BY department_id)
ORDER BY department_id, salary DESC;
select *
from employees
where (department_id, salary) in(
select department_id , MIN(salary)
from employees
group by department_id
)
order by salary desc;
-- 다중 열 연산자
SELECT employee_id, first_name, last_name, job_id, salary
FROM employees a
WHERE(a.job_id, a.salary) IN(
SELECT job_id, MIN(salary)
from employees
GROUP BY job_id)
ORDER BY a.salary DESC;
-- FROM 절 서브 쿼리 : 인라인뷰
SELECT *
FROM employees A,departments B
WHERE a.department_id = b.department_id
and B.department_name = 'IT';
select *
from employees a, (SELECT * FROM departments WHERE department_name = 'IT') b
where a.department_id = b.department_id;
-- DML : 데이터를 삽입/ 갱신/ 삭제(사실 select도 사실 DML이다.)와 같은 조작 명령어를 말한다.
select * from departments;
-- INSERT 구문(삽입)
-- INSERT INTO 테이블 (컬럼명) (값)
INSERT INTO departments(department_ID, department_NAME, manager_id, location_id)
values( 271, 'Sample_Dept', 200, 1700);
INSERT INTO departments(department_id, department_name, manager_id, location_id)
values( 15, 'Sample_Song', 100, 1800);
commit;
-- DML은 대부분 commit을 해주여야 영구반영된다.
-- UPDATE : 수정
UPDATE departments
set manager_id = 201, location_id = 1800
where department_name = 'Sample_Dept';
UPDATE departments
set manager_id = 101, location_id = 1700
where department_name = 'Sample_Song';
UPDATE departments
SET department_name = 'Sample_Lim'
WHERE manager_id = 101;
-- DELETE : 삭제
DELETE from departments
where department_name = 'Sample_Dept';
DELETE from departments -- 이거만 입력하면 전체 테이블 삭제
where department_name = 'Sample_Lim';
commit;
-- DDL (데이터 정의) 테이블 정의 -> 테이블 생성
CREATE TABLE sample_product
(
product_id number,
product_name varchar2(30),
menu_date date
);
select * from tab; -- 테이블 전체
desc sample_product; -- 구조
SELECT * FROM sample_product;
INSERT into sample_product(product_ID, product_NAME, MENU_DATE)
values (100, '붕어빵', '20241119');
UPDATE sample_product
SET product_id = 200, product_NAME = '커피머신'
WHERE MENU_DATE = '20241119';
DELETE FROM sample_product
WHERE product_id = 200;
commit;
drop table sample_product;
select * from tab;
-------------------------------[20일]------------------------------------
-- 19일에 다중 행 출력 잘 못되었던 것 부서별 최소급여 출력 재풀이
select first_name || last_name , department_id, salary
from employees
where (department_id, salary) in(
select department_id , MIN(salary)
from employees
group by department_id
)
order by salary asc;
-- 조인으로 출력
select *
from employees a
where a.salary in (select MIN(salary)
from employees b
where a.department_id = b.department_id
group by department_id)
order by salary desc;
-- 쌤이 푼거
select
e. first_name AS employee_name,
(
select d.department_name
from departments d
where d.department_id = e.department_id) as department_name,
e.salary as minimum_salary
from employees e
where e.salary =
(
select MIN(b.salary)
from employees b
where b.department_id = e.department_id
)
order by minimum_salary;
drop table sample_product; -- 많들어 놓은 테이블 삭제
select * from tab; -- 테이블 전체 확인.
--테이블 생성
CREATE TABLE sample_product
(
product_id number,
product_name varchar2(30),
menu_date date
);
INSERT INTO sample_product VALUES(1, 'television', to_date('140101' , 'YYMMDD'));
INSERT INTO sample_product VALUES(2, 'washer', to_date('150101' , 'YYMMDD'));
INSERT INTO sample_product VALUES(3, 'cleaner', to_date('160101' , 'YYMMDD'));
commit;
SELECT * FROM sample_product;
-- ALTER(수정)
alter table sample_product ADD (factory varchar(10)); -- 팩토리 바차(10)형 추가
-- NULL 초기화 문제
-- 1) NULL 허용 : NULL로 초기화 -> 기본값을 원하면 DEFAULT 기본값 (0)
-- 2) NULL 허용 X(NOT NULL) -> DEFAULT 기본값 (0)
-- 열삭제 가능 (ALTER TABLE table_name DROP COLUMN column_name;)
ALTER TABLE sample_product DROP COLUMN factory;
desc sample_product;
-- 열 수정
alter table sample_product MODIFY (factory char(10));
-- PRODUCT_NAME VARCHAR2(30) -> varchar(5)
-- ORA-01441: cannot decrease column length because some value is too big
-- 01441. 00000 - "cannot decrease column length because some value is too big"
-- 기존 저장 공간보다 작게 변경할 수 없다. 기존 데이터에 손실이 발생할 수 있기 때문이다.
alter table sample_product MODIFY (product_name varchar(5));
-- 이름 변경
alter table sample_product rename column factory to factory_name;
-- TRUNCATE(삭제 : 데이터 삭제 + 저장 공간 삭제 + 인덱스 삭제)
TRUNCATE TABLE sample_product;
-- 구조는 남아있다.
desc sample_product;
-- DROP은 전부 다 지워 버림.
DROP TABLE sample_product ;
-- 완전히 삭제 됨.
desc sample_product;
SQLDEVELOPER 단축키