221226. DB 3. DML, 제약조건, ALTER TABLE

하루입니다.

 

---

 

DML

• insert, update, delete
• 마리아DB는 스키마와 데이터베이스가 동의어이다. 
• 오라클은 테이블이 모여 스키마, 스키마가 모여 데이터베이스.

 

 

sql으로 사용하는 것에 익숙해지자!

 

 

 

---

 

실습 준비

1. employees 테이블 복사하기 : CREATE TABLE 사용

 

 

(+) 테이블 복사 안 하고 값만 복사하기 : INSERT INTO 사용

 

 

---

 

 

6장. 데이터관리 DML

1. insert 

• insert into 테이블명 (컬럼, 컬럼 ... ) values (값리스트);

 

• 만약 intp emp_copy 뒤를 생략한다면 모든 컬럼에 값을 넣는다는 것이다. 

 

• 여러개 insert 하는 법
• insert into 테이블명 (컬럼, 컬럼 ... ) values (값리스트), (값리스트), (값리스트), ... ;

 

 

---

 

 

IGNORE

• 아래 오류 이유 : date type value에 올바른 값이 들어가지 않았다.
• '2022-02-22' 형태여야 하는데, '2022'만 들어갔다.
• 오류를 무시하고 실행해라! 나중에 내가 하겠다! 라는 뜻으로 ignore를 사용한다.

 

 

 

• INSERT ignore INTO emp_copy VALUES (8, '길동', '정', 6000, '2022', NULL);
• ignore을 사용했다.
• 에러는 발생하지 않지만 경고가 발생한다. 

 

 

---

 

 

autocommit

• mariaDB는 autocommit 한다.
• 만약 커밋 상태를 변경하고 싶다면 SET autocommit = FALSE; 를 사용하자.

 

 

---

 

 

delete, truncate, drop

delete	truncate
DELETE from 테이블명 WHERE 조건;	TRUNCATE TABLE employees;
레코드(가로)를 삭제한다.	테이블의 모든 레코드를 삭제한다.
특정 조건에 맞는 레코드를 삭제하는 것이 대부분이라, where를 많이 사용한다.	물리적인 기억 장소도 지우고,  임시영역에 로그도 남기지 않는다.
autocommit 상태를 false로 한다면 복구 기회가 있다.	복구 기회 없다.
where절 사용 가능하다.	where절 사용 불가하다.
테이블까지 삭제하기 위해서는 DROP을 사용하자.

 

 

 

삭제 안전 장치

• 만약 내가 DELETE FROM employees WHERE salary <= 5000; 를 하고 싶다면, 그 전에 select where 절을 사용해 내가 삭제할 데이터를 미리 조회하자. 변경할 데이터가 맞다면 delete/update를 사용하자.

 

 

---

 

 

UPDATE

• UPDATE 테이블명 SET 변경컬럼명 = 변경값;

 

 

정리

SELECT 컬럼명 FROM 테이블 WHERE 조건 (ORDER BY 정렬기준)

INSERT INTO 테이블 (컬럼명) VALUES (값목록)

DELETE FROM 테이블 WHERE 조건

UPDATE 테이블 SET 컬럼명 = 값, 컬럼명 = 값 ... WHERE 조건

 

 

 

---

 

 

7장. 제약조건

데이터가 결함없이 완벽한 상태를 무결성이라고 한다.

• 클라이언트의 무결성 : 입력할 정보에 꼭 맞는 컨트롤러 오입력을 방지한다.
• 서버의 무결성 : DBMS가 규칙을 기억하고 있다가비정상 데이터의 입력을 거부하는 식이다.
• 컬럼 무결성 : 도메인 무결성. 컬럼 하나에 저장되는 원자적인 값을 점검한다. 타입 지정, null 허용 여부, 체크, 기본값 등
• 엔티티 무결성 : 레코드끼리 중복값을 가지지 않게 해 유일한 식별자를 관리. PK, UNIQUE
• 참조 무결성 : 테이블간의 관계가 항상 유효하도록 관리한다. 외래키 제약을 관리한다. 이 테이블이 참조하는 정보는 저 테이블에 반드시 있어야 한다.
• 서버에서 무결성을 유지하는 가장 기본적인 수단은 타입이다. 데이터의 물리적 포맷을 강제한다.

 

• constraint
• 현실세계를 모델링해서 product 테이블을 만든다.
• CODE는 NULL이 아니며 중복 허용도 안 할 것이다.
• NAME은 NULL이 아니다.
• PRICE는 조건 없다.
• BALANCE는 0 이상이어야 한다.

 

 

---

 

 

NULL

• NULL은 아무것도 입력되어 있지 않은, 알 수 없거나 결정되지 않은 특수한 상태이다.
• 데이터베이스의 성능을 저해한다. 
• 항상 NULL 상태를 감안해서 필드값이 존재하는지 점검해야 하고, 보통의 값과는 다루는 방식이 다르기 때문이다.

 

 

기본값 (DEFAULT)

• NULL보다 성능상 유리하다.
• 필드값이 입력되지 않았을 때 자동으로 입력할 값이다.
• 보통 수치는 0, 문자는 빈칸 혹은 'N/A'를 많이 사용한다.
• !! 기본값의 유무와 NULL은 완전히 별개이다 !!

 

 

 

CHECK

• 논리적인 값의 형식을 점검한다.
• 무의미한 값을 걸러낸다.
• 예를 들어, 월급이 음수인 경우

 

 

 

PRIMARY KEY

• 레코드끼리 구분하는 고유한 키(= 식별자)이다.
• 고유하고 항상 값이 있는 값들을 후보키로 놓고, 그 중 레코드를 가장 잘 나타내는 키를 기본키로 선정한다.
• 레코드를 상징해야 하고, 자주 참조하는 속성이어야 하고(자동 인덱스 생성), 짧은 속성이어야 한다(비교속도 위해).
• 중복 금지, NULL 금지, 인덱스 생성
• 제약의 이름을 생략하면 서버가 자동으로 이름 붙임. 그래서 PK_CITY_NAME 이런 값 붙이는 거임

 

 

출처 : 김상형의 SQL 정복

 

 

 

복합키

• 경기도 광주와 전라도 광주를 구분하려면 어떻게 해야 할까?
• CITY와 REGION을 묶어서 복합키로 만든다. 
• 전라도 광주 / 경기도 광주 / 경기도 수원 등 복합키를 구성하는 개별키는 중복되어도 상관 없다.

 

 

 

UNIQUE

• 필드의 중복값을 방지하여 모든 필드가 고유한 값을 가지도록 한다.
• NULL을 허용한다. 하나의 NULL만 허용한다.
• 많이 지정 가능하다. 
• 인덱스 자동 생성되지 않는다.
• 예를 들어, 이메일은 입력하지 않아도 되지만 입력된다면 중복되어서는 안 된다.

 

 

 

 

100은 기본키라 중복값이 불가해!

 

 

 

P_NAME은 NULL값이 불가능해!

 

 

P_BALANCE의 CHECK 제약조건은 0 이상이라서 -10이 들어갈 수 없어!

 

 

---

 

 

제약조건을 사용하여 테이블 생성하고 제약조건 조회하기

• 제약조건의 효력은 DML이 실행될 때 발생한다.
• user_id는 문자(10)고, null이 아니고, 유일한 값이다.
• user_pw는 문자고(5), null이 아니다.
• user_name은 문자(30)다.
• user_email은 문자(30)고, 유일한 값이다.
• user_phone은 문자(12)고, 010-으로 시작한다.
• address는 문자(100)이다.

 

위의 설명을 코드로 만들었다.

 

 

위의 코드로 만든 테이블을 조회했다.

 

 

제약조건을 조회한다.

 

 

CHECK 제약조건을 조회한다.

 

 

 

---

 

 

 

정수, 숫자 자동 증가 : auto increment

• 오라클은 sequence
• sqlserver는 identity
• maria db는 auto_increment

 

 

---

 

 

외래키 (FOREIGN KEY)

• 컬럼레벨로만 가능 : not null
• 테이블레벨로만 가능 : foreign key
• 컬럼레벨 + 테이블레벨 가능 : primary key, unique, check
• CONSTRAINT PK_BOARD_SEQ PRIMARY KEY(SEQ),
• CONSTRAINT FK_BOARD_WRITER FOREIGN KEY(WRITER) REFERENCES users(user_id)

 

 

 

 

제약조건 포함 생성 방법 1 : 컬럼 레벨에서 정의

 

 

제약조건 포함 생성 방법 2 : 테이블 레벨에서 정의

 

 

결과

SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME='board';

 

 

---

 

 

자식이 있는 부모키를 삭제할 때

• 현재 users의 user_id 에는 maria, maria2가 있다.
• boards의 writer는 users의 user_id를 외래키로 한다. 
• users는 boards의 부모이다.

 

• 아래의 두 코드를 실행한다.
• 만약 maria3을 입력할 경우, user_Id에 없기에 오류가 발생한다.

INSERT INTO BOARD(TITLE, CONTENT, WRITER) VALUES ('제목1', '내용1', 'maria');

INSERT INTO BOARD(TITLE, CONTENT, WRITER) VALUES ('제목2', '내용2', 'maria2');

 

• DELETE FROM users WHERE user_id = 'maria';
• 위의 코드를 실행하려 하면 오류가 발생한다.
• boards에 maria를 부모로 하는 자식값이 있기 때문이다. 
• 이를 해결하기 위해서는 maria를 삭제할 때 1. writer를 null로 변경하거나 2. maria를 외래키로 하는 값을 삭제하는 것이다. 
• board table을 alter한다.
• 먼저 외래키 제약조건을 삭제 후, 다시 제약조건을 걸면서 on delete cascade 혹은 on delete set null을 설정한다.

 

ALTER TABLE board ADD CONSTRAINT FK_BOARD_WRITER 
FOREIGN KEY(writer) REFERENCES users(user_id) ON DELETE CASCADE;

 

• 이제 maria를 삭제하면 maria user_id를 외래키로 참조하는 값들이 삭제된다.

 

---

 

 

ALTER TABLE

-- CREATE TABLE : 없는 테이블을 정의한다.
-- ALTER TABLE : 있는 테이블에 컬럼을 추가 / 삭제/ 변경 / 제약조건을 수정한다.
ALTER TABLE 테이블명 ADD A INT;			-- 없던 컬럼 추가
ALTER TABLE 테이블명 MODIFY A CHAR(10);	 	-- 있는 컬럼 타입이나 길이 수정
ALTER TABLE 테이블명 DROP XXXX;			-- 있던 컬럼 삭제
ALTER TABLE 테이블명 ADDCONSTRAINT ... ;	-- 있는 컬럼의 없던 제약조건 추가
ALTER TABLE 테이블명 MODIFY CONSTRAINT ...;	-- 있는 컬럼의 제약조건 수정
ALTER TABLE 테이블명 DROP CONSTRAINT;		-- 제약조건 삭제

 

 

1. null 삽입이 가능한 컬럼을 not null로 변경하고 싶어요!

• add not null이 아니라 modify not null을 사용해야 한다.
• not null이 없다면 자동으로 null이 삽입될 수 있다고 한다. 그래서 add가 아니라 modify이다.

 

 

 

2. 작성 시간 컬럼을 추가하고 싶어요!

 

 

 

3. 작성시간 컬럼을 삭제하고 싶어요!

 

 

 

 

 

---