Today
- SQL 프로그래밍
-- 2024-02-20
-- HAVING절 : WHERE절에서 집계함수를 사용하고 싶지만, 문법적으로 WHERE절에서 사용할 수 없는 경우 사용
USE employees;
# HAVING을 사용해야하는 경우 예시
SELECT emp_no, AVG(salary)
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-08-31'
WHERE AVG(salary) > 80000 # 그룹핑 하기전에 조건 체크하는 부분이라 오류발생+되더라도 총평균으로 계산해버림
GROUP BY emp_no
;
# 위 쿼리를 HAVING으로 작성
SELECT emp_no, AVG(salary)
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-08-31'
GROUP BY emp_no
HAVING AVG(salary) > 80000
;
-- 실습1 : AVG(salary)가 100,000 보다 큰 사람이 있는 부서와 부서코드, salary의 평균을 구하는 쿼리를 작성하시오.
SELECT * FROM salaries LIMIT 100;
SELECT * FROM dept_emp LIMIT 100;
SELECT * FROM departments LIMIT 100;
SELECT
A.dept_no, A.dept_name, AVG(A.AVG_SAL)
FROM salaries = SA
INNER JOIN (
SELECT
SA.emp_no, DP.dept_no, DP.dept_name, AVG(SA.salary) AS AVG_SAL
FROM salaries AS SA
INNER JOIN dept_emp AS DE ON DE.emp_no = SA.emp_no
INNER JOIN departments AS DP ON DP.dept_no = DE.dept_no
GROUP BY SA.emp_no
HAVING AVG_SAL > 100000
) AS A
ON A.emp_no = SA.emp_no
GROUP BY dept_no
;
-- 실습2 : 2002년 월별 매출 데이터 평균을 WITH ROLLUP 을 이용하여 구하는 쿼리를 작성하시오.
SELECT * FROM salaries LIMIT 100;
SELECT
to_char(from_date, 'YYYY-MM'), AVG(salary)
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31'
GROUP BY to_char(from_date, 'YYYY-MM') WITH ROLLUP
;
-- 순위함수 : 데이터의 순위를 만들어주는 함수
# 순위함수, 랭크함수, 윈도우 함수라고도 함.
/*
[사용법]
- ROW_NUMBER()
ROW_NUMBER() OVER(PARTITION BY 그룹기준열 ORDER BY 순위기준열 {ASC/DESC})
◆ PARTITION BY기준으로 그룹으로 분할하고 그 안에서 ORDER BY 순위기준열 기준으로 랭킹 결정
◆ ROW_NUMBER()의 경우 랭킹 매길 때 같은 값이 있어도 그냥 1,2,3순으로 쫙 매겨버림
1위 : 10 점
2위 : 8점
3위 : 8점
4위 : 8점
5위 : 5점
- RANK()
RANK() OVER(PARTITION BY 그룹기준열 ORDER BY 순위기준열 {ASC/DESC})
◆ 사용법은 동일
◆ RANK()의 경우 랭킹 매길 때 같은 값이 있으면 같은 순위로 매기고 그 동률의 개수만큼 순위를 빼고 다음 순위 매김
1위 : 10 점
2위 : 8점
2위 : 8점
2위 : 8점
5위 : 5점
- DENSE_RANK()
DENSE_RANK() OVER(PARTITION BY 그룹기준열 ORDER BY 순위기준열 {ASC/DESC})
◆ 사용법은 동일
◆ DENSE_RANK()의 경우 랭킹 매길 때 같은 값이 있으면 같은 순위로 매기고 해당 순위 이어서 다음 순위 매김
1위 : 10 점
2위 : 8점
2위 : 8점
2위 : 8점
3위 : 5점
*/
# ROW_NUMBER() 써보기
SELECT
CONCAT(MONTH(from_date),'월') AS mon,
emp_no, salary,
ROW_NUMBER() OVER(PARTITION BY MONTH(from_date) ORDER BY salary desc) AS rank
FROM salaries
WHERE from_date >= '2002-01-01'
ORDER BY MONTH(from_date), rank
;
-- 실습1
/*
ROW_NUMBER()로 MONTH(from_date) 이용하여 월별 매출정보를 부서별로 출력하세요.
기간 2001년
*/
SELECT * FROM salaries LIMIT 100;
SELECT * FROM dept_emp LIMIT 100;
SELECT * FROM departments LIMIT 100;
SELECT
CONCAT(MONTH(SA.from_date),'월') AS MON
, ROW_NUMBER()
OVER(PARTITION BY MONTH(SA.from_date) ORDER BY SUM(SA.salary) DESC) AS rank
, DP.dept_no, DP.dept_name
, SUM(SA.salary) AS total
FROM salaries AS SA
INNER JOIN dept_emp AS DE ON SA.emp_no = DE.emp_no
INNER JOIN departments AS DP ON DP.dept_no = DE.dept_no
WHERE SA.from_date BETWEEN '2002-01-01' AND '2002-12-31'
GROUP BY DP.dept_no, MONTH(SA.from_date)
ORDER BY MON, rank
;
# 검증하기 : d005 부서의 2002년 월별 매출 합계
SELECT DP.dept_no, MONTH(SA.from_date), SUM(SA.salary)
FROM salaries AS SA
INNER JOIN dept_emp AS DE ON SA.emp_no = DE.emp_no
INNER JOIN departments AS DP ON DP.dept_no = DE.dept_no
WHERE (SA.from_date BETWEEN '2002-01-01' AND '2002-12-31') && (DP.dept_no = 'd005')
GROUP BY DP.dept_no, MONTH(SA.from_date)
;
# ROW_NUMBER() vs RANK() vs DENSE_RANK() 순위 매겨지는 차이!
# 95번행을 보시오!
# ROW_NUMBER()
SELECT
emp_no
, CONCAT(MONTH(from_date), '월') AS mon
, AVG(salary) AS avg_sal
, ROW_NUMBER()
OVER(PARTITION BY MONTH(from_date) ORDER BY AVG(salary) DESC) AS rnk
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31'
GROUP BY MONTH(from_date), emp_no
ORDER BY MONTH(from_date), rnk asc
;
# RANK()
SELECT
emp_no
, CONCAT(MONTH(from_date), '월') AS mon
, AVG(salary) AS avg_sal
, RANK()
OVER(PARTITION BY MONTH(from_date) ORDER BY AVG(salary) DESC) AS rnk
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31'
GROUP BY MONTH(from_date), emp_no
ORDER BY MONTH(from_date), rnk asc
;
# DENSE_RANK()
SELECT
emp_no
, CONCAT(MONTH(from_date), '월') AS mon
, AVG(salary) AS avg_sal
, DENSE_RANK()
OVER(PARTITION BY MONTH(from_date) ORDER BY AVG(salary) DESC) AS rnk
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31'
GROUP BY MONTH(from_date), emp_no
ORDER BY MONTH(from_date), rnk asc
;
-- NTILE() : 그룹화하여 순위매기는 함수
/*
[사용법]
NTILE(나눌 그룹 수) OVER(PARTITION BY 그룹기준열 ORDER BY 순위기준열 {ASC/DESC})
◆ 지정한 그룹 개수로 나눠서 값들을 각 그룹에대해 순위를 나누는 함수
◆ 3그룹으로 나눈다 하면 = > 1위 그룹, 2위 그룹, 3위 그룹으로 나눠서 기준 값으로 3그룹으로 분류하는 방식
*/
SELECT
emp_no
, CONCAT(MONTH(from_date), '월') AS mon
, AVG(salary) AS avg_sal
, NTILE(10)
OVER(PARTITION BY MONTH(from_date) ORDER BY AVG(salary) DESC) AS rnk
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31'
GROUP BY MONTH(from_date), emp_no
ORDER BY MONTH(from_date), rnk asc
;
-- PERCENT_RANK()/CUME_DIST()
/*
PERCENT_RANK() : 입력한 기준의 순위에서 해당 값이 위치하는 상대 순위 백분율을 출력 함.
CUME_DIST() : 입력한 기준의 순위에서 해당 값이 위치하는 누적 분포 백분율을 출력 함.
[사용법]
PERCENT_RANK() OVER(PARTITION BY 그룹기준열 ORDER BY 순위기준열 {ASC/DESC})
◆ 지정한 기준의 순위에서 해당 값이 위치하는 백분율을 구합니다.
◆ (현재순위 - 1) / (전체행수 -1)
CUME_DIST() OVER(PARTITION BY 그룹기준열 ORDER BY 순위기준열 {ASC/DESC})
◆ 지정한 기준의 순위에서 해당 값이 위치하는 누적백분율을 구합니다.
◆ (현재순위) / (전체행수)
*/
# PERCENT_RANK() 사용해보기!
SELECT
emp_no
, CONCAT(MONTH(from_date), '월') AS mon
, AVG(salary) AS avg_sal
, PERCENT_RANK()
OVER(PARTITION BY MONTH(from_date) ORDER BY AVG(salary) DESC) AS rnk
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31'
GROUP BY MONTH(from_date), emp_no
ORDER BY MONTH(from_date), rnk asc
;
# CUME_DIST() 사용해보기!
SELECT
emp_no
, CONCAT(MONTH(from_date), '월') AS mon
, AVG(salary) AS avg_sal
, CUME_DIST()
OVER(PARTITION BY MONTH(from_date) ORDER BY AVG(salary) DESC) AS rnk
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31'
GROUP BY MONTH(from_date), emp_no
ORDER BY MONTH(from_date), rnk asc
;
-- VIEW!!!!
/*
[사용법]
- 뷰 테이블 생성
CREATE VIEW 뷰이름 AS
생성쿼리(SELECT ~~~)
◆ 생성쿼리, SELECT문으로 가져온 결과를 해당 뷰 테이블로 저장해놓는 방식 입니다.
◆ 특정 데이터를 계속 활용해야할 때 이런식으로 뷰 테이블로 만들어 놓으면 다른데서 활용할 수 있습니다.
◆ 뷰이름은 'VIEW_대문자영문명' 이런식으로 작성 함 => 해당 테이블이 뷰인지, 진짜 테이블인지 구분용
천재교육의 경우 'VW_대문자영문명' 이런식으로 주로 사용함.
◆ 뷰 생성할 때
1) SELECT문 안에서 AS로 alias 로 컬럼명을 바꿔서 VIEW로 저장할 수 있음 (=> 원본테이블의 컬럼명을 숨길 수 있음)
2) SELECT 생성쿼리 내 ORDER BY 정렬순서를 줘서 VIEW로 저장할 수 있음
3) @변수명 을 이용할 수 없습니다.
- 뷰 삭제
DROP VIEW 뷰이름
- 뷰 덮어쓰기
REPLACE VIEW 뷰이름 AS
생성쿼리
◆ 사용법은 생성할 때와 동일
◆ REPLACE로도 바로 생성할 수 있지만, 해당 키워드로 생성할 경우 해당 뷰이름이 존재할 경우에도 냅다 덮어씌우기 떄문에
의도한 경우가 아니라면 가능한 생성만을 목적으로 할 경우 CREATE로 사용하는게 좋습니다.(CREATE의 경우는 해당 뷰이름이 있는 경우 에러뜸)
[뷰의 장/단점]
- 장점
엄청나게 긴 셀렉트 조회 구문을 저장해서 쓰기 떄문에 간편함
뷰로 저장하게 될 시 해당 뷰가 캐시에 올라가서 그냥 SELECT를 조회하는 것보다 속도가 빠름
뷰 테이블도 INDEX를 일부 타기 때문에 속도 면에서 강점이 있음
SELECT/DELETE/UPDATE/INSERT 전부 사용가능
- 단점
해당 뷰테이블의 기준테이블 자체가 변경되면 에러가 날 수 있음
*/
USE maria;
SELECT * FROM tbl_orderinfo;
SELECT * FROM tbl_orderdetail;
# 뷰생성해보기
CREATE VIEW VW_ORDER_INFO_SELECT
AS
SELECT
MB.memberId, MB.name
, OI.orderNo, OI.orderDate
, OD.orderedCnt, OD.amount
, GI.goodsCode, GI.goodsName, GI.state
FROM tbl_orderinfo AS OI
INNER JOIN tbl_orderdetail AS OD ON OD.orderNo = OI.orderNo
INNER JOIN tbl_goodsinfo AS GI ON GI.goodsCode = OD.goodsCode
INNER JOIN tbl_member AS MB ON MB.memberId = OI.memberId
;
# 만든 뷰 조회해보기
SELECT * FROM vw_order_info_select;
-- 실습 : 회원테이블의 정보를 가져오는 뷰를 작성하되 컬럼명을 다르게 하여 가져오는 뷰를 작성하시오
SELECT * FROM tbl_member;
CREATE OR REPLACE VIEW VW_MEMBER
AS
SELECT
memberId AS ID
, NAME AS NM
, pwd AS PW
, jumin AS JM
, CONCAT(addr1, ' ',addr2) AS ADDR
, birthday AS BD
, jobCode AS JC
, mileage AS ML
, memberState AS MS
, regDate AS RD
, leaveDate AS LD
FROM tbl_member;
SELECT * FROM VW_MEMBER;
-- VIEW에 INSERT/UPDATE/DELETE하기!
/*
VIEW에 INSERT/UPDATE/DELETE 경우는 해당 뷰를 만들 때 작성한 기본 테이블에 추가/수정/삭제하는 것이기 때문에
1. 기본 테이블에서 NULL허용 불가인데 해당 컬럼을 뷰테이블에는 안갖고온 경우
2. 뷰 생성시 작성한 SELECT문에서 JOIN문을 사용한 경우 (아마 UNION도?)
3. infomation_schema.VIEWS 에서 해당 뷰의 IS_UPDATABLE 컬럼이 NO인 경우
<조회 쿼리 >
SELECT TABLE_NAME, IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = '데이터베이스명'
;
위 3가지 경우에는 INSERT/UPDATE/DELETE가 불가합니다.
선생님 TIP : 뷰에서는 가능한 INSERT/UPDATE/DELETE 하지말자, 해야한다면 원래 테이블 가서 항목에 맞춰 작업하자!
*/
# INSERT/UPDATE/DELETE할 뷰 만들기
CREATE VIEW VW_tbl_member
AS
SELECT memberId, NAME, pwd, jumin, addr1, addr2, birthday
, jobCode, mileage, memberState, regDate, leaveDate
FROM tbl_member
;
SELECT * FROM VW_tbl_member;
# INSERT 하기
INSERT INTO VW_tbl_member(memberId, NAME, pwd, regDate)
VALUES('test2', '테스트회원2', '1234', NOW());
# UPDATE 하기
UPDATE VW_tbl_member
SET jumin = '345678-1234567', addr1 = '서울 금천구 독산1동'
, birthday = '2000-12-25', jobCode = '06', mileage = 3000
, memberState = 'Y', regDate = NOW()
WHERE memberId = 'memberId';
# DELETE 하기
SET AUTOCOMMIT = 0;
START TRANSACTION;
SELECT * FROM VW_tbl_member;
# DELETE문 사용
DELETE FROM VW_tbl_member
WHERE memberId = 'test2';
COMMIT;
ROLLBACK;
# JOIN을 사용한 VIEW에 작업해보기
DESC vw_order_info_select;
SET AUTOCOMMIT = 0;
START TRANSACTION;
INSERT INTO vw_order_info_select(memberId, NAME, orderNo, orderDate, orderedCnt, amount, goodsCode, goodsName, state)
VALUES ('test3', '테스트회원3','202402001', NOW(), 5, 5000, 'DGS001', '노트', 'Y');
# can not modify more than one base table through a join view 'maria.vw_order_info_select 오류 발생!!!
# 하나 이상의 JOIN이 이루어진 테이블에서는 수정할 수 없다!!!!!!
COMMIT;
ROLLBACK;
# IS_UPDATEABLE 조회해보기
SELECT TABLE_NAME, IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'maria'
;
-- ALTER : 테이블 구조/속성 변경 합니다.
/*
[기본구문]
- 테이블
ALTER TABLE 테이블명(or 뷰명)
{ADD | DROP | MODIFY | CHANGE | RENAME} {COLUMN | INDEX KEY | CONSTRAINT | FOREIGN KEY} 내용내요~
◆ 자세한 사용법은 아래에서!!!!!
- 데이터베이스명
ALTER [IF EXISTS] DATABASE 데이터베이스명
으로 사용하는 듯함 자세히 보지는 않음
◆ 선생님 TIP : DDL 에서는 CREATE TABLE ~ / ALTER TABLE ~ / DROP TABLE ~ 이런 식으로 명시를 해줌.
*/
CREATE TABLE if NOT EXISTS `tbl_member2`
LIKE `tbl_member`; # tbl_member와 형식이 같은 table 생성
DESC tbl_member_test;
-- 테이블명 수정하기
ALTER TABLE tbl_member2 RENAME tbl_member_test;
-- 컬럼 수정
ALTER TABLE tbl_member_test
CHANGE COLUMN jumin jumin_no VARCHAR(200)
;
-- 속성값을 추가/수정할 때
ALTER TABLE tbl_member_test
MODIFY COLUMN jumin_no VARCHAR(300) DEFAULT NULL COMMENT '주민등록번호';
-- 컬럼 추가
INSERT INTO tbl_member_test
SELECT * FROM tbl_member;
SELECT * FROM tbl_member_test;
ALTER TABLE tbl_member_test
ADD COLUMN if NOT EXISTS ssn1
CHAR(6) NULL COMMENT '주민번호 앞 6자리'
first;
# 이미 데이터가 있는 테이블인데 NOT NULL 속성을 주면 기존 데이터에서 해당 컬럼 데이터가 다 없어서 안됨.(에러발생) => 데이터 있는 테이블에서 NOT NULL컬럼 추가 불가
# 선생님 TIP : 시스템 상에서 NULL = ''
-- 컬럼 삭제
DESC tbl_member_test;
ALTER TABLE tbl_member_test
DROP COLUMN if EXISTS ssn1;
-- 프라이머리 키 삭제
ALTER TABLE tbl_member_test
DROP PRIMARY KEY;
-- AUTO_INCREMENT 속성의 컬럼 추가하기
/*
◆ AUTO_INCREMENT 속성의 경우 PRIMARY KEY 또는 INDEX로 지정되어있어야함. + 기존 값은 해당 값이 없으므로 채워주기도 해야함.
1. 컬럼 먼저 추가
2. 기존데이터의 데이터 넣어주기
3. 프라이머리 키 또는 인덱스로 지정
4. AUTO_INCREMENT 속성 추가
*/
ALTER TABLE tbl_member_test
ADD COLUMN idx INT COMMENT '인덱스'
FIRST;
# 뭔가 하려다 실패한 흔적
BEGIN
DECLARE i INT DEFAULT 1;
while (i<8) DO
UPDATE tbl_member_test SET idx = i
WHERE rownum() = i;
SET i = i + 1;
END while;
END
# 추가한 idx 컬럼 기존데이터에 값 주기(노가다)
SELECT * FROM tbl_member_test;
UPDATE tbl_member_test SET idx = 1 WHERE memberId = 'gee1';
UPDATE tbl_member_test SET idx = 2 WHERE memberId = 'member1';
UPDATE tbl_member_test SET idx = 3 WHERE memberId = 'member2';
UPDATE tbl_member_test SET idx = 4 WHERE memberId = 'member3';
UPDATE tbl_member_test SET idx = 5 WHERE memberId = 'member4';
UPDATE tbl_member_test SET idx = 6 WHERE memberId = 'memberId';
UPDATE tbl_member_test SET idx = 7 WHERE memberId = 'test';
# 추가한 컬럼에 프라이머리키 적용
# 방법1
ALTER TABLE tbl_member_test
ADD PRIMARY KEY (`idx`);
# 방법2
ALTER TABLE tbl_member_test
ADD CONSTRAINT PK_tbl_member_test_idx PRIMARY KEY (`idx`);
# AUTO_INCREMENT() 속성 추가
ALTER TABLE tbl_member_test
MODIFY COLUMN idx
int NOT NULL AUTO_INCREMENT COMMENT '인덱스';
-- 제약사항 추가하기
ALTER TABLE tbl_member_test
ADD CONSTRAINT ck_jumin CHECK(CHAR_LENGTH(jumin_no) >= 13); # jumin 컬럼에 제약조건으로 글자수가 13보다 크거나 같아야한다라는 제약조건을 검
ALTER TABLE tbl_member_test
ADD CONSTRAINT ck_pwd CHECK(CHAR_LENGTH(pwd) >= 4); # pwd 컬럼에 제약조건으로 글자수가 4보다 크거나 같아야한다라는 제약조건을 검
# 위 제약조건 잘 걸렸는지 확인용 INSERT
INSERT INTO tbl_member_test (memberId, NAME, pwd)
VALUES ('test3', '테스트3', '123'); # ERROR : CONSTRINT `ck_pwd` failed for `maria`.`tbl_member_test` 에러가 뜸! 제약조건을 만족못해서!
-- 실습1 : 현재 추가된 ck_jumin, ck_pwd 제약을 삭제하는 쿼리를 작성하세요.
ALTER TABLE tbl_member_test
DROP CONSTRAINT ck_pwd;
ALTER TABLE tbl_member_test
DROP CONSTRAINT ck_jumin;
-- 실습2
/*
ssn1 char(6) 컬럼 추가하고, 제약추가 : 6자리만 입력
ssn2 char(7) 컬럼 추가하고, 제약추가 : 7자리만 입력
각각의 컬럼 추가 후
jumin_no 의 값 앞 6자리 --> ssn1
jumin_no 의 값 앞 7자리 --> ssn2
로 업데이트 하는 쿼리를 작성하시오.
*/
# 컬럼추가
ALTER TABLE tbl_member_test
ADD COLUMN If NOT EXISTS `ssn1`
CHAR(6) COMMENT '주민번호 앞 6자리'
FIRST;
ALTER TABLE tbl_member_test
ADD COLUMN If NOT EXISTS `ssn2`
CHAR(7) COMMENT '주민번호 뒤 7자리'
FIRST;
# 제약 추가
ALTER TABLE tbl_member_test
ADD CONSTRAINT ck_ssn1 CHECK(CHAR_LENGTH(ssn1) = 6);
ALTER TABLE tbl_member_test
ADD CONSTRAINT ck_ssn2 CHECK(CHAR_LENGTH(ssn2) = 7);
# 값 추가해보기
SELECT * FROM tbl_member_test;
INSERT INTO tbl_member_test (memberId, NAME, pwd, ssn1, ssn2)
VALUES ('test1', '테스트1', '1234', '222222','3333333');
INSERT INTO tbl_member_test (memberId, NAME, pwd, ssn1, ssn2)
VALUES ('test2', '테스트2', '1234', '555555','6666666');
# UPDATE문 작성
SET AUTOCOMMIT = 0;
START TRANSACTION;
# 지현 답1 (반대로함!ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ)
UPDATE tbl_member_test AS A INNER JOIN tbl_member_test AS B ON A.memberId = B.memberId
SET A.jumin_no = CONCAT(B.ssn1, '-', B.ssn2)
WHERE A.jumin_no IS NULL;
# 지현 답2
UPDATE tbl_member_test AS A INNER JOIN tbl_member_test AS B ON A.memberId = B.memberId
SET A.ssn1 = LEFT(B.jumin_no,6), A.ssn2 = RIGHT(B.jumin_no,7)
WHERE A.ssn1 IS NULL && A.ssn2 IS NULL;
ROLLBACK;
COMMIT;
-- 외래키 설정
/*
ALTER TABLE 테이블명
ADD CONSTRAINT 키이름
FOREIGN KEY (컬럼명) REFERENCES 부모테이블명(컬럼명)
ON UPDATE [RESTRICT | CASCADE | SET NULL]
ON DELETE [RESTRICT | CASCADE | SET NULL]
◆ 외래키의 키이름은 맘대로 만들어도 되지만 대부분 FK_부모테이블_컬럼명 으로 작성합니다.
◆ ON UPDATE/DELETE 의 경우 부모테이블에서 원조 데이터가 UPDATE/DELETE 될 때 자식 테이블에서 행할 옵션입니다.
RESTRICT : 외래키로 다른 테이블에서 쓰고 있는 컬럼 UPDATE/DELETE 불가하게 처리
CASCADE : 외래키로 다른 테이블에서 쓰고 있는 컬럼 UPDATE/DELETE 시 사용중인 자식 테이블에서 해당 값 전부 다 같이 UPDATE/DELETE
SET NULL : 외래키로 다른 테이블에서 쓰고 있는 컬럼 UPDATE/DELETE 시 사용중인 자식 테이블에서 해당 값 전부 NULL 처리 됨
NO ACTION : 부모 테이블에서 해당 값이 UPDATE/DELETE 되더라도 자식 테이블에 있는거 그대로 유지
◆ ON UPDATE/DELETE SET NULL의 경우 해당 컬럼이 애초에 NULL허용으로 설정되어있지 않으면 적용 불가!
*/
ALTER TABLE tbl_orderdetail
ADD CONSTRAINT FK_tbl_orderDetail_goodsCode
FOREIGN KEY (goodsCode) REFERENCES tbl_goodsinfo(goodsCode)
ON UPDATE CASCADE ON DELETE CASCADE;
-- 실습 : cascade 삭제하고, set null로 변경
# 외래키는 외래키로써 제약조건에서도 삭제해야하고, 인덱스에서도 삭제해줘야함.
ALTER TABLE tbl_orderdetail
DROP CONSTRAINT FK_tbl_orderDetail_goodsCode;
ALTER TABLE tbl_orderdetail
DROP INDEX FK_tbl_orderDetail_goodsCode;
ALTER TABLE tbl_orderdetail
ADD CONSTRAINT FK_tbl_orderDetail_goodsCode
FOREIGN KEY (goodsCode) REFERENCES tbl_goodsinfo(goodsCode)
ON UPDATE SET NULL ON DELETE SET NULL; # goodsCode는 null허용이 안되어있어서 냅다 오류나옴Review
- SQL...배우면 배울수록 응용하는게 어렵다...
TO DO
- Java 최종 정리(~2/25)
- Maria DB 정리 수업진도 따라잡기
블로그 이전 했습니다. 아래 블로그 아이콘(🏠) 눌러서 놀러오세요