신대방 삼거리인데, 신림사거리에 내려서 지각해버림..또! 10분 일찍 도착하려고 했는데. 10분 지각이라니ㅠ 따흐으윽ㅠㅜㅠ

자 이제, 수업을 시작해야죠? 오늘 수업은 테이블 수정 실습이네여

오늘도 파이팅~^^

테이블 수정

: ALTER TABLE 구문을 사용한다.
테이블에서 정의된 열과 제약 조건의 추가, 수정, 삭제, 이름 변경 등을 할 수 있다.

1. 열의 추가 / 수정 / 삭제

1) 열 추가: ALTER와 ADD 구문을 이용.

cf) 열을 추가하면 기본적으로 가장 뒤에 추가가 된다. FIRST 또는 AFTER 를 사용하면, 열의 순서를 정할 수 있다.

• usertbl 테이블에 age 열을 추가. 단, 기본값은 0으로 지정하고 birthYear 뒤에 생성

ALTER TABLE usertbl ADD age TINYINT DEFAULT 0 FIRST;
ALTER TABLE usertbl ADD age TINYINT DEFAULT 0 AFTER;

2) 열의 수정

• ALTER와 MODIFY 구문을 이용해서, 열의 데이터 타입과 DEFAULT 값을 변경할 수 있다.

-- usertbl 테이블에서 name 열의 데이터 유형을 CHAR(15)로 변경
ALTER TABLE usertbl MODIFY `name` CHAR(15) NULL;

• 에러가 발생할 때 (1)

-- usertbl 테이블에서 name 열의 데이터 유형을 CHAR(1)로 변경
-- 변경하려는 데이터 유형의 크기보다 이미 큰 값이 존재하기 때문에, 에러가 발생함.
ALTER TABLE usertbl MODIFY `name` CHAR(1) NULL;

• 에러가 발생할 때 (2)

-- usetbl 테이블에서 name 열의 데이터 유형을 INT로 변경
-- 이미 name 열에 문자열이 저장되어 있기 때문에, 에러가 발생.
ALTER TABLE usertbl MODIFY `name` INT(1) NOT NULL;

• 가능한 경우

-- usertbl 테이블에서 homepage 열에 데이터 유형을 INT로 변경
-- 값이 없으면 문자 타입을 정수 타입으로 변경이 가능하다.
ALTER TABLE usertbl MODIFY `homepage` INT(1);

• ALTER와 RENAME COLUMN 구문을 이용해서, 열의 이름을 변경할 수 있다.

-- usertbl 테이블에서  name 열의 이름을 uname으로 변경하기.
ALTER TABLE usertbl RENAME COLUMN `name` TO `uname`;

-- 위 내용을 한 번에 변경
ALTER TABLE usertbl 
CHANGE COLUMN `uname` `name` VARCHAR(20) NOT NULL;

• ALTER와 DROP COLUMN 구문을 이용해서 열을 삭제할 수 있다.

-- usertbl 테이블에서 age, homepage, gender 열을 삭제
ALTER TABLE usertbl DROP COLUMN age;
ALTER TABLE usertbl DROP COLUMN homepage;
ALTER TABLE usertbl DROP COLUMN gender;

-- usertbl 테이블에서 userid 열을 삭제
-- 참조되고 있는 열이 있다면, 삭제가 불가능하다.
-- 제약 조건을 삭제하거나 참조하는 열이 없도록 한 후에 삭제해야한다.
ALTER TABLE usertbl DROP COLUMN userid;
CREATE TABLE dept_copy (
	SELECT *
	FROM department
);

ALTER TABLE dept_copy DROP COLUMN dept_id;
ALTER TABLE dept_copy DROP COLUMN dept_title;

주의) 테이블에는 최소 한 개의 열은 존재해야 한다.

ALTER TABLE dept_copy DROP COLUMN location_id;

2.열의 제약 조건 추가 / 삭제

1) 열의 제약 조건 추가

-- tb_member_grade 테이블에 PRIMARY KEY 제약 조건 추가
ALTER TABLE tb_member_grade ADD CONSTRAINT PRIMARY KEY(grade_code);

-- tb_member 테이블에 PRIMARY KEY 제약 조건 추가
ALTER TABLE tb_member ADD CONSTRAINT PRIMARY KEY(mem_no);
ALTER TABLE tb_member MODIFY mem_no INT AUTO_INCREMENT;
SELECT * FROM usertbl;

-- tb_member 테이블에 UNIQUE 제약 조건 추가
ALTERT TABLE tb_member ADD CONSTRAINT UNIQUE(mem_id);

-- tb_member 테이블에 grade_code 열을 생성 후, FOREIGN KEY 제약조건을 추가
ALTER TABLE tb_member ADD grade_code VARCHAR(10) AFTER mem_name;
ALTER TABLE tb_member ADD CONSTRAINT fk_tb_member_grade_code
FOREIGN KEY(grade_code) REFERENCES tb_member_grade(grade_code);

-- tb_member 테이블에 gender 열 생성 후 CHECK 제약 조건을 추가
ALTER TABLE tb_member ADD gender CHAR(2) AFTER mem_name;
ALTER TABLE tb_member ADD CONSTRAINT CHECK(gender = '남자' OR gender = '여자');

-- tb_member 테이블에 age 열 생성 후 CHECK 제약 조건을 추가
ALTER TABLE tb_member ADD age TINYINT(2) AFTER gender;
ALTER TABLE tb_member ADD CONSTRAINT ck_tb_member_age
CHECK(age BETWEEN 0 AND 120);

• 실습

-- employee 테이블에서 emp_id 열에 PK 제약 조건 추가
ALTER TABLE employee ADD CONSTRAINT PRIMARY KEY (emp_id);

-- employee 테이블에서 emp_no 열에 UNIQUE 제약 조건 추가
ALTER TABLE employee ADD CONSTRAINT uq_employee_emp_no UNIQUE (emp_no);

-- tb_member 테이블에서 dept_code 열에 FK 제약 조건 추가
ALTER TABLE tb_member
ADD CONSTRAINT fk_tb_member_dept_code
FOREIGN KEY (dept_code) REFERENCES department(dept_id);

-- tb_member 테이블에서 job_code 열에 FK 제약 조건 추가
ALTER TABLE tb_member
ADD CONSTRAINT fk_tb_member_job_code
FOREIGN KEY (job_code) REFERENCES job(job_code);

2) 열의 제약 조건 삭제

-- tb_member 테이블에서 PRIMARY KEY 제약 조건 삭제
ALTER TABLE tb_member MODIFY mem_nno INT;
ALTER TABLE tb_member DROP CONSTTRAINT PRIMARY KEY;

-- tb_member 테이블에서 UNIQUE 제약 조건 삭제
ALTER TABLE tb_member DROP CONSTRAINT uq_tb_member_mem_id;

-- tb_member 테이블에서 FOREIGN KEY 제약 조건 삭제
ALTER TABLE tb_member DROP FOREIGN KEY fq_tb_member_grade_code;

-- tb_member 테이블에서 CHECK 제약 조건 삭제
ALTER TABLE tb_member DROP CONSTRAINT CONSTRAINT_1;
ALTER TABLE tb_member DROP CONSTRAINT ck_tb_member_age;

3. 테이블 삭제

-- 참조되고 있는 테이블은 삭제가 되지 않는다. foregin key 
DROP TABLE tb_member_grade;

-- 자식 테이블을 먼저 삭제한 후, 부모 테이블을 삭제한다.
DROP TABLE tb_member, tb_member_grade;

뷰

1) 뷰 생성

• 사원들의 사번, 직원명, 부서명, 직급명, 입사일을 조회하는 뷰 생성

CREATE VIEW v_employee
AS SELECT e.emp_id,
		e.emp_name,
		d.dept_title,
		j.job_name,
		e.hire_date
FROM employee e
LEFT OUTER JOIN department d ON e,dept_code = d.dept_id
LEFT OUTER JOIN job j ON e.job_code = j.job_code;

-- 가상의 테이블로 실제 데이터가 담겨있는 것은 아니다.
SELECT *
FROM v_employee;

2) 뷰 수정

• 회원의 아이디, 이름, 구매 제품, 주소, 연락처를 조회하는 뷰를 생성

CREATE VIEW v_userbuytbl
AS SELECT u.userid,
		u.name,
		b.prodName,
		u.addr,
		CONCAT(u.mobile1, u.mobile2) AS 'mobile'
FROM usertbl u
INNER JOIN buytbl b ON u.userid = b.usrid;

- 위에서 생성한 뷰의 수정

SELECT *
FROM v_userbuytbl;

3) 뷰를 이용해서 DML(INSERT, UPDATE, DELETE)사용.

CREATE VIEW v_job
AS SELECT *
FROM job;

SELECT *
FROM v_job;

-- SELECT 
SELECT job_code, job_name FROM v_job;

-- INSERT
--INSERT INTO v_job VALUES('J8');

-- UPDTE
UPDATE v_job
SET job_name = '인턴'
WHERE job_code = 'J8';

4) DML 조작이 불가능한 경우

-- 1) 뷰의 정의에 포함되지 않은 열을 조작하는 경우
CREATE OR REPLACE VIEW v_job
AS SELECT job_code
	FROM job;

SELECT *FROM v_job;	

-- INSERT
INSERT INTO v_job VALUES('J8', '알바'); -- 에러 발생

-- UPDATE
UPDATE v_job
SET job_name = '인턴'
WHERE job_code = 'J7'

-- DELETE
DELETE
FROM v_job
WHERE job_name = '사원';

-- 2) 산술 연산으로 정의된 열을 조작하는 경우
-- 직원들의 사번, 이름, 주민번호, 연봉을 조회하는 뷰를 생성
SELECT emp_id,
		emp_name,
		emp_no,
		salary * 12 AS 'salary'
	FROM employee;
	
SELECT * FROM v_emp_salary;

-- INSERT
INSERT INTO v_emp_salary
VALUES('300', '홍길동', '20250701-3123456', 30000000);

-- UPDATE
UPDATE v_emp_salary
SET salary = 30000000
WHERE emp_id='200';
AS SELECT  * 
FROM y;

5) WITH.CHECK OPTION

-- 서버 쿼리에 기술된 조건에 부합하지 않는 값으로 
-- 수정하는 경우 오류를 발생시킨다.
CREATE OR REPLACE VIEW v_emplo
AS SELECT *
	FROM employee
	WHRE salary >= 30000000
	WITH CHECK OPTION;
	
-- 서브 쿼리의 조건

6) 뷰 삭제

DROP VIEW v_userrbuytbl;
DROP VIEW v_employee, v_emp,salary, _job;

쿨쿨쿨 졸려서, 중간에 놓쳐버렸당... 미래의 내가 해결하길.. 해결하겠지?

인덱스