DML은 데이터 조작 언어로 데이터를 검색, 등록 삭제, 갱신을 위한 언어이다.
단순하게 만든 테이블과 컬럼안의 데이터들을 다루는 언어이다.
컬럼을 만들었으므로 삽입부터 쭉 다룬다.
이 중 Merge는 조건이 맞을경우, 틀릴경우 작동하는데
jsp같은데서 사용할 때 조건문을 사용하지 db에서는 잘 쓰지 않는다.
이런게 있구나 하고 넘어가면 된다.
Insert, Update, Delete, Merge
-- DML은 테이블의 데이터를 입력/수정/삭제/읽기를 다룬다.
--
-- 1. 데이터 추가
-- INSERT INTO [테이블 명] ([컬럼 명], ...) VALUES ([넣을 값], ...);
DESC employees;
INSERT INTO employees (emp_no, first_name, family_name, email,
mobile, salary, commision)
VALUES (111, '둘리', '고', 'asd@acomp.com', '01234567890', 10000000, 90);
INSERT INTO employees (emp_no, first_name, family_name, email,
mobile, salary, commision)
VALUES (112, '또치', '고', 'asd@acomp.com', '01234567890', 10000000, 90);
INSERT INTO employees (emp_no, first_name, family_name, email,
mobile, salary, depart_no, commision)
VALUES (113, '도넛', '고', 'asd@acomp.com', '01234567890', 10000000, 3, 90);
SELECT * FROM employees;
-- INSET INTO employees VALUES ( 모든 데이터 값 );와 같이 모든 값이 들어 갈 경우 컬럼명을 생략해도 된다.
-- 하지만 다른 사람이 읽을 때는 컬럼명을 생략했을때 알아보기 힘들어지므로 다 적는것을 추천
-- 현재 예제는 REG_DATE가 중간에 있어서 생략 할 시 오류가 발생, 마지막에 있다면 정상 작동 할 것.
-- 2. 데이터 수정
-- UPDATE [테이블] SET [컬럼] = [변경할 값] WHERE [조건]; 조건을 안붙이면 전부 수정해버리므로 주의!
UPDATE employees SET depart_no = 'DEV_002' WHERE depart_no IS NULL;
SELECT * FROM employees;
-- 커미션이 90인 사람을 10으로
UPDATE employees SET commision = 10 WHERE commision = 90;
-- 이름이 '김지훈'인 사람의 comission을 90으로
UPDATE employees SET commision = 90 WHERE first_name = '또치';
SELECT * FROM employees;
-- 3. 삭제
-- DELETE FROM [테이블] WHERE [조건]; 조건을 안붙이면 전부 지워버리므로 주의!
-- emp_no = 113인 사람 지우기
DELETE FROM employees WHERE emp_no = 113;
SELECT * FROM employees;
-- 삽입
-- emp_no = 113 ~ 117
-- dev_001 1명, 나머지 dev_002
-- commision은 null
INSERT INTO employees (emp_no, first_name, family_name, email,
mobile, salary, depart_no)
VALUES (113, '도넛', '고', 'asd@acomp.com', '01234567890', 10000000, 'DEV_002');
INSERT INTO employees (emp_no, first_name, family_name, email,
mobile, salary, depart_no)
VALUES (114, '길동', '고', 'asd@acomp.com', '01234567890', 10000000, 'DEV_001');
INSERT INTO employees (emp_no, first_name, family_name, email,
mobile, salary, depart_no)
VALUES (115, '희동', '박', 'asd@acomp.com', '01234567890', 10000000, 'DEV_002');
INSERT INTO employees (emp_no, first_name, family_name, email,
mobile, salary, depart_no)
VALUES (116, '이콜', '마', 'asd@acomp.com', '01234567890', 10000000, 'DEV_002');
INSERT INTO employees (emp_no, first_name, family_name, email,
mobile, salary, depart_no)
VALUES (117, '실이', '공', 'asd@acomp.com', '01234567890', 10000000, 'DEV_002');
SELECT * FROM employees;
-- 4. MERGE
-- 특정 조건에 부합하면 수정 또는 삭제 그렇지 않으면 삽입하는 기능
-- MERGE INTO [테이블] USING (서브쿼리) ON (조건);
-- WHEN MATCHED THEN
-- [DELETE] 또는 [UPDATE]
-- WHEN NOT MATCHED THEN
-- [INSERT]
-- 서브 쿼리는 뒤에서 설명하지만 쿼리문으로 뽑아낸 특정 데이터를 쿼리문에서 사용하는 것.
-- 쿼리 안의 쿼리
-- 테이블 하나 사용 시
-- 1) depart_no가 dev_001인 데이터가 있으면 commision을 10으로 변경
-- UPDATE employees SET commision = 10 WHERE depart_no = 'dev_001';
-- DUAL은 가상 테이블로 지금은 텅 비어있음
MERGE INTO employees USING DUAL ON (depart_no = 'DEV_001')
WHEN MATCHED THEN
UPDATE SET commision = 10;
-- 여기까지, MATCHED만 사용 할 경우 UPDATE문이나 DELETE문과 다를게 없다.
SELECT * FROM employees;
-- 2) depart_no가 dev_003인 데이터가 있으면 commision을 10으로 변경하고 없으면 추가 한다.
MERGE INTO employees USING DUAL ON (depart_no = 'DEV_003')
WHEN MATCHED THEN
UPDATE SET commision = 10
WHEN NOT MATCHED THEN
INSERT (emp_no, first_name, family_name, email, mobile, salary, depart_no)
VALUES (118, '계인', '외', 'E@email.net', '01012345678', 4000000, 'DEV_003');
SELECT * FROM employees;
-- 3) depart_no가 dev_002일 때 commision을 10으로 변경
-- 그리고 EMP_NO = 117인 데이터 삭제
MERGE INTO employees USING DUAL ON (depart_no = 'DEV_002')
WHEN MATCHED THEN
UPDATE SET commision = 10
DELETE WHERE emp_no = 117;
SELECT * FROM employees;
-- 테이블 2개 사용 시(스스로)
Select
Select는 데이터 베이스의 항목들을 조회, 읽어오는 것
가장 많이 쓰이고 데이터가 많아질 수록 복잡해진다.
그리고 보통 모든 항목을 표시하는 *을 자주 사용하는데
이는 다른 사람이 봤을때 따로 찾아봐야 하는 일이 생기므로 사용하는걸 권장 x
1. 조회
2. 특정 조건을 만족하는 데이터(WHERE)
3. AND 조건 (A&&B)
4. OR 조건(A||B)
5. BETWEEN AND
6. 중복 제거(DISTINCT)
7. IN
8. IS NULL / IS NOT NULL
9. LIKE
10. 정렬 (ORDER BY)
11. GROUP BY
12. HAVING
-- 1. 데이터 조회
-- 1) 특정 컬럼 조회
-- SELECT [column] FROM [table]
SELECT * FROM employees;
SELECT first_name, family_name, salary FROM employees;
-- 산술 표현(실제 데이터에 적용되지 않음)
SELECT first_name, family_name, salary/10000 FROM employees;
-- 연봉 알아보기(AS를 통해 항목 이름을 임시로 바꿔서 표현)
-- AS는 특정 컬럼에 별칭을 주는 것.
SELECT first_name, family_name, salary / 10000, ((salary/10000) * 12) AS 연봉 FROM employees;
-- 문자열 합치기 (||, 버티컬 바)
SELECT family_name||' ' ||first_name AS 이름, salary / 10000 AS 봉급, ((salary/10000) * 12) AS 연봉 FROM employees;
SELECT family_name||' ' ||first_name AS 이름, salary / 10000 || '만원' AS 봉급, ((salary/10000) * 12) || '만원' AS 연봉 FROM employees;
-- 2)특정 조건을 만족하는 데이터(WHERE)
-- SELECT [column] FROM [table] WHERE [condition];
-- family_name = '고'인 사람을 찾아보자
SELECT family_name||' ' ||first_name AS 이름, salary / 10000 || '만원' AS 봉급, ((salary/10000) * 12) || '만원' AS 연봉 FROM employees WHERE family_name = '고';
-- salary > 5000000
SELECT family_name||' ' ||first_name AS 이름, salary / 10000 || '만원' AS 봉급, ((salary/10000) * 12) || '만원' AS 연봉 FROM employees WHERE salary > 5000000;
-- 3) AND 조건 (A&&B)
-- salary > 3000000, salary <= 7000000
SELECT family_name||' ' ||first_name AS 이름, salary / 10000 || '만원' AS 봉급, ((salary/10000) * 12) || '만원' AS 연봉 FROM employees WHERE salary > 3000000 AND salary < 7000000;
-- 4) OR 조건(A||B)
-- family_name = '고' OR salary > 7000000
SELECT family_name||' ' ||first_name AS 이름, salary / 10000 || '만원' AS 봉급, ((salary/10000) * 12) || '만원' AS 연봉 FROM employees WHERE family_name = '고' OR salary > 7000000;
-- 5) BETWEEN AND
-- WHERE salary >= 3000000 AND salary <= 7000000
-- = WHERE salary BETWEEN 3000000 AND 7000000
-- BETWEEN을 사용하는 이유는 프로그램마다 읽는 방식이 다르기 때문에
-- 예를들어 html에선 <> 비교를 <P> 와 같이 사용하기 때문이다.
SELECT family_name||' ' ||first_name AS 이름, salary / 10000 || '만원' AS 봉급, ((salary/10000) * 12) || '만원' AS 연봉 FROM employees WHERE salary BETWEEN 3000000 AND 7000000;
-- 6) 중복 제거(DISTINCT)
-- SELECT DISTINCT [column] FROM [table];
SELECT DISTINCT salary FROM employees;
-- 7) IN
-- 여러개의 OR 조건을 표현 할 때(속도면에서 우월)
-- 문장이 짧아져 보기쉽고 프로그램 상 속도도 OR보다 빠르다.
SELECT * FROM employees WHERE
family_name = '고' OR family_name = '박' OR family_name = '마';
SELECT * FROM employees WHERE
family_name IN ('고', '박', '마');
-- 8) IS NULL / IS NOT NULL
-- DB에 NULL이 있는건 좋지 않기 때문에 쓸 일이 별로 없을것임.
SELECT * FROM employees WHERE commision IS NULL;
SELECT * FROM employees WHERE commision IS NOT NULL;
-- Like 실험을 위해 수정
UPDATE employees SET email = 'bsd@gmail.com' WHERE first_name = '도넛';
UPDATE employees SET email = 'bsd@gmail.com' WHERE first_name = '길동';
UPDATE employees SET email = 'csd@acomp.com' WHERE first_name = '희동';
UPDATE employees SET email = 'csd@acomp.com' WHERE first_name = '이콜';
-- 9) LIKE
-- 일부 비슷한 내용을 검색
-- WHERE [column] LIKE '[%를 포함한 문자열]'
-- %는 와일드 카드라고 함
-- 앞 자리가 as로 시작하는 ... => as%
-- 뒷 자리가 com으로 끝나는 ... => %com
-- b와 c를 포함하는 ... => %b%c%
SELECT * FROM employees WHERE email LIKE 'as%';
SELECT * FROM employees WHERE email LIKE '%com';
SELECT * FROM employees WHERE email LIKE '%b%c%';
-- gmail 이 포함된 내용을 찾아라
SELECT * FROM employees WHERE email LIKE '%gmail%';
-- 정렬 실험을 위한 UPDATE
UPDATE employees SET salary = 12000000 WHERE first_name = '또치';
UPDATE employees SET salary = 8000000 WHERE first_name = '길동';
UPDATE employees SET salary = 6000000 WHERE first_name = '이콜';
-- 10) 정렬 (ORDER BY)
-- 특정 컬럼을 기준으로 오름차순(ASC) 또는 내림차순 (DESC)
-- SELECT [column] FROM [table] ORDER BY [column] [ASC | DESC]
-- SELECT [column] FROM [table] WHERE [조건] ORDER BY [column] [ASC | DESC]
-- 급여가 높은 사람 순으로 정렬
SELECT * FROM employees ORDER BY salary DESC;
-- 성을 가나다 순으로 정렬
SELECT * FROM employees ORDER BY family_name ASC;
-- ASC, 오름차순은 생략 될 수 있다.
SELECT * FROM employees ORDER BY family_name;
-- 연봉이 높은 사람 기준으로 정렬
-- 별칭을 지정하였을 경우 별칭만 입력해도 됨
SELECT family_name||' ' ||first_name AS 이름, salary / 10000 || '만원' AS 봉급, ((salary/10000) * 12) || '만원' AS 연봉 FROM employees ORDER BY ((salary/10000) * 12) || '만원' ASC;
-- 11) GROUP BY
-- 데이터를 그루핑 하여 가져오는 경우 사용
-- Group by에 사용 할 컬럼은 Select 절의 컬럼에 반드시 있어야 함
-- SUM, AVG와 같은 계산 묶음을 사용하지 않으면 오류 발생, 그룹으로 묶으면 개별 정보를 표현할 수 없기 때문
-- 부서별 급여 합산, 평균
SELECT depart_no, SUM(salary) FROM employees GROUP BY depart_no;
SELECT depart_no, AVG(salary) FROM employees GROUP BY depart_no;
-- 부서별 급여 합과 평균 커미션
SELECT depart_no, SUM(salary), AVG(commision) FROM employees GROUP BY depart_no;
-- 12) HAVING
-- GROUP BY 결과로 부터 특정 조건의 값만 추출
-- HAVING에는 WHERE과 다르게 별칭을 사용할 수 없음.
-- 부서별 급여 합계와 성과급 평균 => 합계가 3000 미만인 그룹만 추출
SELECT depart_no, SUM(salary) as 합, AVG(commision) FROM employees GROUP BY depart_no
HAVING SUM(salary) < 30000000;
-- 부서별 합계가 3000만원 이하인 부서를 금액이 높은 순으로 정렬
SELECT depart_no, SUM(salary) as 합 FROM employees GROUP BY depart_no
HAVING SUM(salary) < 30000000 ORDER BY SUM(salary) DESC;
