FOREIGN KEY / 삭제 옵션
-- FOREIGN KEY
-- 외래키 , 외부키 , 참조키
-- 다른 테이블의 컬럼값을 참조(REFERENCE)하여 참조하는 테이블의 값만 허용한다.
-- FOREIGN KEY 제약조건을 통해서 다른 테이블과의 관계(RELATIONSHIP)가 형성된다.
CREATE TABLE USER_GRADE(
GRADE_CODE INT PRIMARY KEY,
GRADE_NAME VARCHAR(100) NOT NULL
);
INSERT INTO USER_GRADE VALUES(1,'일반 회원');
INSERT INTO USER_GRADE VALUES(2,'VIP');
INSERT INTO USER_GRADE VALUES(3,'VVIP');
INSERT INTO USER_GRADE VALUES(4,'VVVIP');
SELECT * FROM USER_GRADE;
CREATE TABLE USER_FOREIGN_KEY(
USER_NO INT PRIMARY KEY,
USER_ID VARCHAR(20),
USER_PW VARCHAR(20),
USER_NAME VARCHAR(15),
GENDER VARCHAR(1) CHECK(GENDER IN ('M','F')),
GRADE_CODE INT,
CONSTRAINT FK_GRADE_CODE FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRA이창DE(GRADE_CODE)
);
INSERT INTO USER_FOREIGN_KEY VALUES(1,'123','321','이길동','F',2);
INSERT INTO USER_FOREIGN_KEY VALUES(2,'1223','3321','이길동','M',4);
INSERT INTO USER_FOREIGN_KEY VALUES(3,'1233','3121','이길동','M',1);
INSERT INTO USER_FOREIGN_KEY VALUES(4,'122133','32321','이길동','F',3);
INSERT INTO USER_FOREIGN_KEY VALUES(5,'12233','32321','이길동','F',1);
-- SQL Error [1452] [23000]: Cannot add or update a child row: a foreign key constraint fails (`multi`.`USER_FOREIGN_KEY`,
-- 외래키로 지정된 GRADE_CODE는 참조하는 USER_GRADE테이블의 GRADE_CODE에 있는 데이터만 INSERT 가능하다 .
INSERT INTO USER_FOREIGN_KEY VALUES(6,'12D3','3F21','박길동','M',10);
SELECT * FROM USER_FOREIGN_KEY
JOIN USER_GRADE USING(GRADE_CODE);
->
GRADE_CODE|USER_NO|USER_ID|USER_PW|USER_NAME|GENDER|GRADE_NAME|
----------+-------+-------+-------+---------+------+----------+
1| 3|1233 |3121 |이길동 |M |일반 회원
1| 5|12233 |32321 |이길동 |F |일반 회원
2| 1|123 |321 |이길동 |F |VIP |
3| 4|122133 |32321 |이길동 |F |VVIP |
4| 2|1223 |3321 |이길동 |M |VVVIP |
-- USER_GRADE 테이블에 저장된데이터들중에 GRADE_CODE가 4인 데이터 삭제
-- dml :delete
-- SQL Error [1451] [23000]: Cannot delete or update a parent row: a foreign key constraint fails (`multi`.`USE
DELETE FROM USER_GRADE
WHERE GRADE_CODE = 4;
-- 삭제 옵션
-- 일반적으로 참도뇌는 원본 컬럼(user_grade.grade_code)내용을 삭제하려고 할때
-- 외래키로 사용 중인 자식 컬럼(user_foreign_key.grade_code)이 있다면 함부로 삭제 할 수 없다 .
-- 참조되고있는 원본 테이블의 컬럼값이 삭제 될때
-- 참조하는 값을 어떻게 처리할것인지 설정하는 옵션
-- ON DELETE CASCADE : 부모 컬럼 삭제할때 자식도 함께 삭제
-- ON UPDATE CASCADE : 부모 컬럼 업데이트할때 함께 업데이트
drop table USER_GRADE;
drop table USER_FOREIGN_KEY;
CREATE TABLE USER_FOREIGN_KEY(
USER_NO INT PRIMARY KEY,
USER_ID VARCHAR(20),
USER_PW VARCHAR(20),
USER_NAME VARCHAR(15),
GENDER VARCHAR(1) CHECK(GENDER IN ('M','F')),
GRADE_CODE INT,
CONSTRAINT FK_GRADE_CODE FOREIGN KEY(GRADE_CODE)
REFERENCES USER_GRADE(GRADE_CODE) ON DELETE CASCADE
);
INSERT INTO USER_FOREIGN_KEY VALUES(1,'123','321','이길동','F',2);
INSERT INTO USER_FOREIGN_KEY VALUES(2,'1223','3321','이길동','M',4);
INSERT INTO USER_FOREIGN_KEY VALUES(3,'1233','3121','이길동','M',1);
INSERT INTO USER_FOREIGN_KEY VALUES(4,'122133','32321','이길동','F',3);
INSERT INTO USER_FOREIGN_KEY VALUES(5,'12233','32321','이길동','F',1);
SELECT * FROM USER_GRADE;
SELECT * FROM USER_FOREIGN_KEY;
DELETE FROM USER_GRADE
WHERE GRADE_CODE = 2;
USER_NO|USER_ID|USER_PW|USER_NAME|GENDER|GRADE_CODE|
-------+-------+-------+---------+------+----------+
2|1223 |3321 |이길동 |M | 4|
3|1233 |3121 |이길동 |M | 1|
4|122133 |32321 |이길동 |F | 3|
5|12233 |32321 |이길동 |F | 1|
-> 2인데이터가 사라짐 ( 원본에서도 grade 2는 같이 사라졌음)
INSERT / UPDATE / DELETE
-- DML(데이터 조작 언어) --
-- INSERT , UPDATE ,DELETE , SELECT(DQL)
-- [CRUD]
-- C(CREATE) : INSERT / 데이터 추가
-- R(READ) : SELECT / 데이터 조회
-- U(UPDATE) : UPDATE / 데이터 수정
-- D(DELETE) : DELETE / 데이터 삭제
-- INSERT : 새로운 행을 특정 테이블에 추가하는 명령어 ,
-- 해당 명령어 실행 후에는 테이블의 행의 개수가 증가.
-- [사용 형식]
-- 1. 특정 컬럼에 값을 추가
-- INSERT INTO 테이블명(컬럼 , 컬럼 , ...) VALUES(값 , 값 , ...);
-- 2. 모든 컬럼에 값을 추가
-- INSERT INTO 테이블명 VALUES(값 , 값 , ...);
desc EMPLOYEE ;
-- 컬럼 명시
INSERT INTO EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,
JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN)
VALUES(500,'박길동','990307-1213213','dudtjr7137@naver.com','01033351234','D1','J7','S4',3100000,0.1,'200',NOW() ,null , default);
SELECT * from EMPLOYEE where emp_id = 500;
-- 컬럼 생략
INSERT INTO EMPLOYEE
VALUES(600 , '박잉석' , '990990-1231233','duddjd@naer.com' , '01033351234','D1','J7','S3' , 4000000,0.2 ,'200',now(), null ,default);
SELECT * from EMPLOYEE where EMP_ID in(500,600);
->
EMP_ID|EMP_NAME|EMP_NO |EMAIL |PHONE |DEPT_CODE|JOB_CODE|SAL_LEVEL|SALARY |BONUS|MANAGER_ID|HIRE_DATE |ENT_DATE|ENT_YN|
------+--------+--------------+--------------------+-----------+---------+--------+---------+-------+-----+----------+----------+--------+------+
500 |박길동 |990307-1213213|dudtjr7137@naver.com|01033351234|D1 |J7 |S4 |3100000| 0.1|200 |2023-03-14| |N |
600 |박잉석 |990990-1231233|duddjd@naer.com |01033351234|D1 |J7 |S3 |4000000| 0.2|200 |2023-03-14| |N |
-- insert + subquery
-- INSERT 문에 서브쿼리를 사용하여 values에 작성하던 값 대신 저장하여 추가 가능하다.
create table emp_01(
emp_id int,
emp_name varchar(20),
eopt_title varchar(40)
);
insert into emp_01(
select emp_id , emp_name , dept_title
from EMPLOYEE e
left join DEPARTMENT on(DEPT_CODE =DEPT_ID)
);
select * from emp_01;
->
emp_id|emp_name|eopt_title|
------+--------+----------+
200|선동일 |총무부 |
201|송종기 |총무부 |
202|노옹철 |총무부 |
203|송은희 |해외영업2부 |
204|유재식 |해외영업2부 |
205|정중하 |해외영업2부 |
206|박나라 |해외영업1부 |
207|하이유 |해외영업1부 |
208|김해술 |해외영업1부 |
209|심봉선 |해외영업1부 |
210|윤은해 |해외영업1부 |
211|전형돈 |기술지원부 |
212|장쯔위 |기술지원부 |
213|하동운 | |
214|방명수 |인사관리부 |
215|대북혼 |해외영업1부 |
216|차태연 |인사관리부 |
217|전지연 |인사관리부 |
218|이오리 | |
219|임시환 |회계관리부 |
220|이중석 |회계관리부 |
221|유하진 |회계관리부 |
222|이태림 |기술지원부 |
500|박길동 |인사관리부 |
600|박잉석 |인사관리부 |
200|선동일 |총무부 |
201|송종기 |총무부 |
202|노옹철 |총무부 |
203|송은희 |해외영업2부 |
204|유재식 |해외영업2부 |
205|정중앙 |해외영업2부 |
206|박나라 |해외영업1부 |
207|하이유 |해외영업1부 |
208|김해술 |해외영업1부 |
209|심봉선 |해외영업1부 |
210|윤은해 |해외영업1부 |
211|전형돈 |기술지원부 |
212|장쯔위 |기술지원부 |
213|하동운 | |
214|방명수 |인사관리부 |
215|대북혼 |해외영업1부 |
216|차태연 |인사관리부 |
217|전지연 |인사관리부 |
218|이오리 | |
219|임시환 |회계관리부 |
220|이중석 |회계관리부 |
221|유하진 |회계관리부 |
222|이태림 |기술지원부 |
500|박길동 |인사관리부 |
600|박잉석 |인사관리부 |
create table test0101
as select emp_id ,emp_name,dept_code,hire_date
from EMPLOYEE ;
SELECT * from test0101;
-- UPDATE : 해당 테이블의 데이터를 수정하는 명령어
-- update 테이블명 set 컬럼명 = 바꿀값(where 조건식 )
-- UPDATE 실행 후 데이터의 개수는 변하지 않는다.
create table DEPT_COPY
AS SELECT * FROM DEPARTMENT ;
SELECT * FROM DEPT_COPY;
->
DEPT_ID|DEPT_TITLE|LOCATION_ID|
-------+----------+-----------+
D1 |인사관리부 |L1 |
D2 |회계관리부 |L1 |
D3 |마케팅부 |L1 |
D4 |국내영업부 |L1 |
D5 |해외영업1부 |L2 |
D6 |해외영업2부 |L3 |
D7 |해외영업3부 |L4 |
D8 |기술지원부 |L5 |
D9 |총무부 |L1 |
-- D9부서를 총모부 -> 전략기획
UPDATE dept_copy set dept_title = '전략기획부'
where dept_id - 'd9'
->
DEPT_ID|DEPT_TITLE|LOCATION_ID|
-------+----------+-----------+
D1 |인사관리부 |L1 |
D2 |회계관리부 |L1 |
D3 |마케팅부 |L1 |
D4 |국내영업부 |L1 |
D5 |해외영업1부 |L2 |
D6 |해외영업2부 |L3 |
D7 |해외영업3부 |L4 |
D8 |기술지원부 |L5 |
D9 |전략기획부 |L1 |
-- 실습 1
-- 임플로이 테이블에서 주민번호가 말못 표기되어있는 사원들이 있다 .
-- 해당 사원을 찾아 사번 순으로 각 주민번호 앞자리를
-- '621230 ' '631126' ' 850705' 로 변경하는 업데이트
SELECT EMP_ID,EMP_NO FROM EMPLOYEE e
-- 200 201 214
update EMPLOYEE set emp_no = concat('621230',substr(emp_no,7))
where emp_id = 200;
update EMPLOYEE set emp_no = concat ('631126',substr(emp_no,7))
where emp_id = 201;
update EMPLOYEE set emp_no = concat ('860705',substr(emp_no,7))
where emp_id = 214;
SELECT * from EMPLOYEE e ;
-- update
-- 여러 행을 변경 여러컬럼의 값을 변경하고자할때 서브커리를 사용한 업데이트문
-- 셋컬럼 서브쿼리
create table emp_salary
as
select emp_id , emp_name , dept_code, salary , bonus
from EMPLOYEE ;
SELECT * from emp_salary where emp_name in('유재식',' 방명수');
update emp_salary
set SALARY = (select SALARY from EMPLOYEE where emp_name = '유재식'),
BONUS = (select BONUS from EMPLOYEE where emp_name = '유재식')
where emp_name = '방명수';
UPDATE EMP_SALARY
SET (SALARY , BONUS) = (SELECT SALARY,BONUS FROM emp_salary WHERE EMP_NAME = '유재식')
where emp_name in ('노옹철', '전형돈','정중하','하동운');
-- UPDATE 시에도 변경할 값이 해당 컬럼의 제약조건을 위배하지 않아야 한다 .
DESC USER_FOREIGN_KEY ;
SELECT * FROM USER_GRADE;
-- 에러
UPDATE USER_FOREIGN_KEY
SET GRADE_CODE = 10
WHERE GRADE_CODE = -1 ;
-- DELETE
-- 테이블의 행을 삭제하는 명어
-- 실행 후에는 테이블의 행의 개수가 줄어든다 .
create table test_delete
as select * from EMPLOYEE ;
SELECT * from test_delete;
commit;
-- 데이터 전체 삭제
DELETE from test_delete;
rollback;TRANSACTION / DROP / VIEW / AUTO INCREAMENT
-- 트랜잭션 --
-- 정의 : 데이터를 처리하는 작업을 잘게 나눈 데이터처리의 최소 작업 단위
-- 하나의 트랜잭션으로 이루어진 작업 내용은 전체성공(저장) or 하나라도 실패하면
-- 전체 실패 (복구) , 각각의 작업마다 저장시점을 구분해서 commit(작업내역 저장/반영) / rollback (작업 내역 취소)
commit;
create table USER_TB(
NO INT UNIQUE,
ID VARCHAR(20) NOT NULL UNIQUE,
PW VARCHAR(20) NOT NULL
);
INSERT INTO USER_TB VALUES(1,'TEST01','PASS01');
INSERT INTO USER_TB VALUES(2,'TEST02','PASS02');
SELECT * FROM USER_TB;
->
NO|ID |PW |
--+------+------+
1|TEST01|PASS01|
2|TEST02|PASS02|
3|TEST03|PASS03|
4|TEST04|PASS04|
commit; -- 현재까지 작업한 DML(INSERT 두번) 내용을 DB에 반영(저장)
INSERT INTO USER_TB VALUES(3,'TEST03','PASS03');
SELECT * FROM USER_TB;
ROLLBACK;
SELECT * FROM USER_TB;
INSERT INTO USER_TB VALUES(3,'TEST03','PASS03');
-- 세이브 포인트 지정 --
SAVEPOINT SP1;
INSERT INTO USER_TB VALUES(4,'TEST04','PASS04');
SELECT * FROM USER_TB;
-- DELETE FROM USER_TB WHERE NO = 4;
ROLLBACK TO SP1; -- 해당 세이브포인트로 되돌아가겠다.
-- ALTER : 생성한 객체(TABLE , VIEW ...)를 수정
DROP TABLE DEPT_COPY;
CREATE TABLE DEPT_COPY
AS SELECT * FROM DEPARTMENT ;
SELECT * FROM DEPT_COPY;
->
DEPT_ID|DEPT_TITLE|LOCATION_ID|
-------+----------+-----------+
D1 |인사관리부 |L1 |
D2 |회계관리부 |L1 |
D3 |마케팅부 |L1 |
D4 |국내영업부 |L1 |
D5 |해외영업1부 |L2 |
D6 |해외영업2부 |L3 |
D7 |해외영업3부 |L4 |
D8 |기술지원부 |L5 |
D9 |전략기획부 |L1 |
-- 컬럼을 추가하기
alter table DEPT_COPY ADD LNAME VARCHAR(20);
SELECT * FROM DEPT_COPY;
->
DEPT_ID|DEPT_TITLE|LOCATION_ID|LNAME|
-------+----------+-----------+-----+
D1 |인사관리부 |L1 | |
D2 |회계관리부 |L1 | |
D3 |마케팅부 |L1 | |
D4 |국내영업부 |L1 | |
D5 |해외영업1부 |L2 | |
D6 |해외영업2부 |L3 | |
D7 |해외영업3부 |L4 | |
D8 |기술지원부 |L5 | |
D9 |전략기획부 |L1 | |
-- 컬럼을 삭제하기
ALTER TABLE DEPT_COPY DROP COLUMN LNAME;
DESC DEPT_COPY ;
Field |Type |Null|Key|Default|Extra|
-----------+-----------+----+---+-------+-----+
DEPT_ID |varchar(10)|NO | | | |
DEPT_TITLE |varchar(35)|NO | | | |
LOCATION_ID|varchar(2) |NO | | | |
-- 컬럼 추가 ( 기본값 적용 )
ALTER TABLE DEPT_COPY ADD LNAME VARCHAR(20) DEFAULT '한국' ;
SELECT * from DEPT_COPY;
-- 제약조건 추가
ALTER TABLE DEPT_COPY ADD CONSTRAINT PK_DEPT_CP PRIMARY KEY(DEPT_ID);
-- 타입의 크기 등 수정
ALTER TABLE DEPT_COPY MODIFY DEPT_ID VARCHAR(30);
DESC DEPT_COPY;
-- 테이블 이름 변경
ALTER TABLE DEPT_COPY
RENAME TO DEPTCOPY;
SELECT * FROM DEPT_COPY;
SELECT * FROM DEPTCOPY;
-- DROP --
-- 객체의 특정 요소를 제거하거나 객체 자체를 제거할때 사용하는 명령어 .
-- 1. 객체의 특정요소를 제거
-- alter 객체 객체명
-- drop column 컬럼명;
-- 2. 객체 자체를 제거
-- drop 객체 객체명;
DROP TABLE DEPTCOPY;
-- VIEW(뷰) --
-- SELECT를 실행한 결과화면을 담는 객체
-- SELECT 문장 자체를 저장.
-- 호출할떄마다 저장된 쿼리를 실행하여 결과를 보여주는 객체다 .
-- 실질적으로 데이터를 담고 있지 않다.
-- 보통 노출하고 싶지 않은 정보다 , 업무에 불피룡한 정보를제외한
-- 필요 정보들만 조회하고자 할 때 사용한다.
-- CREATE [OR REPLACE] VIEW 뷰 이름
-- AS 서브쿼리 (뷰에 저장하여 확인할 select 쿼리)
create or REPLACE VIEW V_EMP
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE FROM EMPLOYEE ;
SELECT * FROM V_EMP;
CREATE OR REPLACE VIEW V_EMP(사번 , 사원명 , 부서 , 직급)
AS SELECT EMP_ID , EMP_NAME , DEPT_CODE, JOB_CODE FROM EMPLOYEE;
SELECT * FROM V_EMP;
drop view V_EMP;
--
CREATE OR REPLACE VIEW V_RESULT_EMP(사번,이름명,직급명,부서명,근무지)
as
select emp_id , emp_name, job_name , dept_title,local_name
from EMPLOYEE
left join JOB using(job_code)
left join DEPARTMENT on(dept_code=dept_id)
left join LOCATION on(location_id=local_code);
select * from V_RESULT_EMP;
-- VIEW는 SELECT 쿼리를 저장하고 있기에
-- 원본이 변경되었을때 뷰도 함께 변경해서 조회 가능하다.
UPDATE EMPLOYEE
SET EMP_NAME='정중앙'
WHERE EMP_ID='205';
SELECT * FROM EMPLOYEE;
COMMIT;
SELECT * FROM V_RESULT_EMP;
-- 뷰에는 연산결과를 포함한 SELECT문 저장 가능하다.
SELECT EMP_ID,EMP_NAME,
IF(SUBSTR(EMP_NO, 8 , 1 )=1, '남성','여성')
from EMPLOYEE ;
create or replace view v_emp(사번,사원,성별)
as
select emp_id ,emp_name,
if(substr(emp_no,8,1)=1 ,'남','여')
from EMPLOYEE ;
select * from v_emp;
-- 뷰에 데이터 삽입 , 수정 , 삭제하기
create or replace view V_JOB
as
select * from JOB ;
SELECT * from V_JOB;
-- 뷰를 통해 데이터 추가
INSERT into V_JOB
VALUES('J8','인턴');
select * from V_JOB;
select * from JOB;
-- 수정
UPDATE V_JOB
SET JOB_NAME = '알바'
WHERE JOB_CODE = 'J8';
SELECT * FROM V_JOB;
SELECT * FROM JOB;
-- 삭제
DELETE FROM V_JOB
WHERE JOB_CODE = 'J8';
SELECT * FROM V_JOB;
SELECT * FROM JOB ;
-- auto_increament
-- INSERT 할 때 자동으로 키를 1씩 증가 .
CREATE TABLE AT_TEST(
ID INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(30)
);
select last_insert_id();TRANSACTION / DROP / VIEW / AUTO INCREAMENT
'mySQL' 카테고리의 다른 글
| [mySQL]그룹에 관련된 명령어 (0) | 2024.04.01 |
|---|---|
| [mySQL]연산자 , 함수 (0) | 2024.03.29 |
| [mySQL] 데이터베이스 (0) | 2024.03.24 |