* 데이터 수정 : UPDATE ~ SET
* 형식 : UPDATE [테이블명] SET [컬럼명=새로운 데이터],[컬럼명=새로운 데이터]...
* WHERE [UPDATE 조건절]
* -- WHERE 절이 생략되면 전체 테이블에 업데이트 적용
* */
-- 홍길동의 연봉을 8700으로 인상
UPDATE employee_copy SET salary=8700 WHERE emp_name = '홍길동';
-- employee_copy 테이블의 전체사원들의 연봉을 10% 인상
SELECT * FROM employee_copy ec
UPDATE employee_copy SET salary = salary*1.1;
-- 김삼순 사원의 사원명을 '김희진'으로 수정
SELECT * FROM employee_copy ec WHERE emp_name = '김삼순';
UPDATE employee_copy SET emp_name = '김희진' WHERE emp_name = '김삼순';
-- 서브쿼리를 이용한 데이터 수정
-- 형식 : update 테이블명 set 컬럼명 = 사부커리 where 업데이트 조건절
-- 김희진 사원의 부서를 영업부에서 정보시스템 부서로 수정
SELECT * FROM employee_copy ec
UPDATE employee_copy
SET dept_id = (SELECT dept_id FROM department WHERE dept_name = '정보시스템')
WHERE employee_copy.emp_name = '김희진';
-- 박여사 사원과 같은 부서의 직원들 연봉을 10% 인상
SELECT * FROM employee_copy ec;
DESC employee_copy
UPDATE employee_copy SET salary = salary * 1.1
WHERE dept_id in (SELECT dept_id FROM employee_copy WHERE emp_name = '박여사') AS b );
UPDATE employee_copy
SET salary = salary * 1.1
WHERE dept_id = (SELECT sub.dept_id FROM (SELECT dept_id FROM employee_copy WHERE emp_name = '박여사') AS sub);
-- update delete insert 시 자기자신을 subquery로 사용하는 것은 막아놨음.
-- 오라클에서는 사용이가능함
-- 영업부 소속의 사원들 연봉을 10% 삭감
SELECT * FROM employee_copy ec
UPDATE employee_copy
SET salary = salary * 0.9
WHERE dept_id = (SELECT dept_id FROM department WHERE dept_name ='영업' )
SELECT * FROM employee_copy ec
SELECT ec.dept_id ,ec.emp_name ,e.salary ,ec.salary FROM employee_copy ec INNER JOIN employee e ON ec.emp_id = e.emp_id
WHERE ec.dept_id = (SELECT dept_id FROM department WHERE dept_name ='영업')
-- 안경태 사원과 같은 부서의 사원들의 입사일을 현재날짜로 수정하라
SELECT * FROM employee_copy ec;
UPDATE employee_copy set hire_date = curdate()
WHERE dept_id = (SELECT dept_id FROM (SELECT dept_id FROM employee_copy WHERE emp_name ='안경테' ) AS sub);
SELECT * FROM employee_copy ec WHERE dept_id = 'acc';
-- 참조관계에서의 데이터 수정
SELECT * FROM student s;
SELECT * FROM subject s;
SELECT * FROM professor p;
/*
-- html 과목의 sub_id 값을 s009로 수정
-- 업데이트 안됨 : sub_id 는 다른 테이블(student,professor)에서 참조하고 있어서 수정이 불가능하다
-- 솔루션 :student,professor 테이블 생성시 참조관계 설정시 subject가 수정,삭제 시
-- 참조하는 student,professor 테이블도 수정, 삭제가 가능하도록 설정
*/
UPDATE subject SET sub_id = 's009'
WHERE sub_name = 'html';
SELECT * FROM student s;
ST01 홍길동 컴퓨터공학과 S002 hong@naver.com
ST02 테스트 경영학과 S004 hong@naver.com
ST03 홍길동 컴퓨터공학과 S001 hong@naver.com
ST04 테스트 경영학과 S003 hong@naver.com
ST05 청길동 철학과 S003 blue@naver.com
SELECT * FROM subject s;
SELECT * FROM professor p;
P001 공유 010-1234-4587 S001
P002 리사 010-1234-4587 S002
P003 제니 010-1234-4587 S003
P004 김아무개 010-1234-4487 S004
P005 박누락씨 010-1234-4487 S005
DROP TABLE student
DROP TABLE professor
CREATE TABLE student(
sid char(4),
sname varchar(20) NOT NULL,
dept varchar(20),
sub_id char(4),
email varchar(20),
CONSTRAINT student_sid_pk PRIMARY key(sid),
CONSTRAINT student_sub_id_fk FOREIGN key(sub_id) REFERENCES subject(sub_id) ON UPDATE cascade
);
INSERT INTO student values('ST01','홍길동','컴퓨터공학과','S002','hong@naver.com');
INSERT INTO student values('ST04','테스트','경영학과','S003','hong@naver.com');
INSERT INTO student values('ST05','청길동','철학과','S003','blue@naver.com');
UPDATE subject SET sub_id = 's009' WHERE sub_name = 'html'
SELECT * FROM subject s
DESC student
-- 조인으로 subject,student profesoor sub_id를 조회
-- 테이블 변경 : ALTER TABLE
-- 형식 : alter table 테이블명
-- 컬럼추가 : ADD COLUMN(CONSTRAINT) [컬럼명 데이터타입(크기)]
-- 컬럼변경 :modify column [변경하는 컬럼크기 ]
-- 컬럼삭제 : drop column [삭제할 컬럼명]
-- 컬럼 변경과 삭제는 데이터가 존재한다면 .. 반드시 데이터 유실부분을 체크하도록 한다!!
SELECT * FROM emp e
-- emp 테이블에 부서 아이디(dept_id,문자,가변형10) 컬럼을 추가해주세욥
ALTER table emp ADD column dept_id varchar(10);
-- 추가하는 테이블에 기존 데이터가 존재 하는 경우에는 추가 컬럼의 제약사항을 null 허용으로 설정해야함
DESC emp
-- emp 테이블의 dept_id 컬럼의 크기를 문자형,5 변경
ALTER TABLE emp MODIFY COLUMN dept_id varchar(5);
SELECT * FROM emp e;
-- emp 테이블의 emp_id의 크기를 char,4
ALTER TABLE emp MODIFY COLUMN emp_id char(4); -- 에러 발생 이유 정리
-- emp 테이블의 emp_id의 크기를 char,12
ALTER TABLE emp MODIFY COLUMN emp_id char(12); -- 에러 발생 이유 정리
-- emp 테이블에 phone 컬럼 추가, 크기(10),null은 허용하지 않음
-- 원래 다른 툴에서는 컬럼추가는 not null 로 주면 안됨, not null로 새로만든 컬럼은
ALTER TABLE emp ADD COLUMN phone varchar(10) NOT NULL;
SELECT * FROM emp e
DESC emp;
-- emp 테이블에 bonus 컬럼 추가 int ㅜㅕㅣㅣ dms gjdydgkwl dksgdma
ALTER TABLE emp ADD COLUMN bonus int NOT null
-- emp 테이블의 dept_id 컬럼 삭제
ALTER TABLE emp DROP COLUMN dept_id;
-- 전체테이블목록조회
SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'hrdb2019'
-- 테이블명 변경 rename
-- 테이블의 이름을 emp_emp 변경
ALTER TABLE emp RENAME emp_emp;
-- 전체 제약사항 목록 조회
SELECT * FROM information_schema.table_constraints WHERE TABLE_SCHEMA = 'hrdb2019';
DESC employee_copy
-- employee_copy 테이블을 삭제해주세요
DROP TABLE employee_copy;
-- employee 테이블을 복제하여 employee_copy 테이블 생성
-- 단, 2017,2018년도 입사자만
CREATE TABLE employee_copy
as
SELECT * FROM employee e WHERE LEFT (hire_date,4) BETWEEN 2017 AND 2018;
SELECT * FROM employee_copy ec
desc
DROP TABLE employee_copy
-- department 테이블을 복자형 dept_copy 테이블 생성
CREATE TABLE dept_copy
AS
SELECT * FROM department d;
SELECT * FROM dept_copy;
-- employee_copy ,dept_copy 테이블에 제약사항 추가
-- 제약사항 추가 : alter table 테이블명 add constraint [제약사항이름](컬럼)
DESC employee_copy;
SELECT * FROM employee_copy;
-- employee_copy 테이블의 emp_id 컬럼에 기본키제약을 추가하라
ALTER table employee_copy ADD CONSTRAINT PRIMARY KEY(emp_id)
ALTER table employee_copy ADD CONSTRAINT PRIMARY KEY(gender)
-- 에러이유 : 중복된 데이터를 가진 gender 컬럼은 기본키로 부적합함
-- dept_copy 테이블의 dept_id 칼럼에 기본키 제약 추가
SELECT * FROM dept_copy
ALTER TABLE dept_copy ADD CONSTRAINT PRIMARY key(dept_id)
DESC dept_copy;
-- employee_copy 테이블의 dept_id 컬럼에 dept_copy 테이블의 dept_id 컬럼을
-- 참조하는 참조키 제약을 추가
SELECT * FROM employee_copy ec ;
ALTER TABLE employee_copy ADD CONSTRAINT FOREIGN key(dept_id) REFERENCES dept_copy(dept_id)
SELECT * FROM dept_copy;
-- dept_copy 테이블의 회계부서 아이디를 'ACD' 로 수정
UPDATE dept_copy SET dept_id = 'ACD' WHERE dept_id = 'ACC'; -- 에러발생
-- 이유 : 참조키이기 때문에 단독적으로 변경할 수 없음
-- employee_copy 테이블에 추가된 참조키 제약을 삭제하고 on update cascade 옵션을 추가하여 다시 실행
-- 제약 사항 삭제 : alter table [테이블명] drop constraint [제약명]
-- 또는 alter table [테이블명] drop foreign key [제약명]
SELECT * FROM information_schema.table_constraints WHERE TABLE_NAME = 'employee_copy'
ALTER TABLE employee_copy DROP FOREIGN KEY employee_copy_ibfk_3;
-- on update cascade 옵션 추가하여 참조키 설정
ALTER TABLE employee_copy ADD FOREIGN KEY (dept_id) REFERENCES dept_copy(dept_id) ON UPDATE CASCADE;
UPDATE dept_copy SET dept_id = 'ACD' WHERE dept_name = '회계'
SELECT * FROM dept_copy