어느덧! 벌써 4주 차라니요. . . T-T
시간이 굉장히~ 아주~ 빠르게 느껴집니닷 ㅎ0ㅎ
이번 주는 우수혼공족! 해 볼게유 그나저나 우수혼공족이 되려면 어떻게 해야 하는 거죠?
일단 전 저대로 열심히 달립니다 ~ （￣︶￣）
테이블 만들기
테이블은 표 형태로 구성된 2차원 구조로, 행과 열로 구성된다.
행은 로우나 레코드라고 부르며, 열은 컬럼 또는 필드라고 부른다.
데이터베이스와 테이블 설계하기
데이터 형식을 활용해서 각 열에 가장 적합한 데이터 형식을 지정한다.
GUI 환경에서 테이블 만들기
'네이버 쇼핑 DB 구성도'의 회원 테이블과 구매 테이블은 기본 키 - 외래 키로 연결되어 있다.
이는 구매 테이블의 mem_id 값은 반드시 회원 테이블의 mem_id로 존재해야 한다는 의미이다.
SQL로 테이블 만들기
NULL과 NOT NULL을 지정해서 테이블을 생성하고, 기본 키로 설정하기 위해서는 지정한 열 뒤에
PRIMARY KEY문을 붙여주면 된다.
순번 옆에 AUTO_INCREMENT를 설정하였다.
AUTO_INCREMENT로 지정한 열은 PRIMARY KEY나 UNIQUE로 꼭 지정해야 한다.
25번째 줄의 코드는 외래 키로 설정하는 단계이며,
이 테이블의 mem_id 열을 member 테이블의 mem_id 열과 외래 키 관계로 연결해라 라는 의미이다.
제약조건으로 테이블을 견고하게
테이블을 만들 때는 테이블의 구조에 필요한 제약조건을 설정해 줘야 한다.
앞에서 확인한 기본 키와 외래 키가 대표적인 제약조건이다.
기본 키는 고유한 번호를 의미하는 일에, 외래 키는 기본 키와 연결되는 열에 지정한다.
회원의 평균 키는 절대 200cm를 넘지 않는다.
이때 실수로 200을 입력하는 것을 방지하는 제약조건이 체크이다.
또한 회원 테이블에 국적을 매번 입력하는 게 귀찮다면 제약 조건으로 기본값을 설정할 수도 있다.
값을 꼭 입력해야 하는 NOT NULL이라는 제약 조건도 있다.
제약 조건의 기본 개념과 종류
제약 조건은 데이터의 무결성을 지키기 위해 제한하는 조건이다.
일단 데이터의 무결성이란 '데이터에 결함이 없음'을 의미한다.
결함을 미리 방지하기 위해서 회원 테이블의 아이디를 기본 키로 지정할 수 있다.
기본 키의 조건은 중복되지 않고, 비어 있지 않음이다!
기본 키 외에 MySQL에서 제공하는 대표적인 제약 조건은 다음과 같다. (‾◡◝)
- PRIMARY KEY 제약조건
- FOREIGN KEY 제약조건
- UNIQUE 제약조건
- CHECK 제약조건
- DEFAULT 정의
- NULL 값 허용
기본 키 제약 조건
데이터를 구분할 수 있는 식별자 : 기본키
기본 키에 입력되는 값은 중복될 수 없으며, NULL 값이 입력될 수 없다.
또한 테이블은 기본 키를 1개만 가질 수 있다.
기본 키 - 외래 키 관계로 연결된 테이블은 외래 키가 설정된 테이블을 먼저 삭제해야 한다.
CREATE TABLE에서 기본 키를 지정하는 다른 방법은
제일 마지막 행에 PRIMARY KEY(mem_id)를 추가하는 것이다.
제약 조건을 설정하는 또 다른 방법은 이미 만들어진 테이블을 수정하는 ALTER TABLE 구문을 사용하는 것이다.
ALTER TABLE member -- 멤버 변경
ADD CONSTRAINT -- 제약 조건 추가
PRIMARY KEY (mem_id); -- mem_id 열에 기본 키 제약조건 설정
이렇게 사용할 수 있다 (*^_^*)
외래 키 제약 조건
외래 키 제약 조건은 두 테이블 사이의 관계를 연결해 주고, 그 결과 데이터의 무결성을 보장해 주는 역할을 한다.
외래 키가 설정된 열은 꼭 다른 테이블의 기본 키와 연결된다.
기본 키가 있는 회원 테이블을 기준 테이블이라고 부르며, 외래 키가 있는 구매 테이블을 참조 테이블이라 부른다.
참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본 키나 고유 키로 설정되어 있어야 한다.
외래 키의 형식은 FOREIGN KEY (열_이름) REFERENCES 기준_테이블(열_이름)이다.
ALTER TABLE buy -- buy 수정
ADD CONSTRAINT -- 제약 조건 추가
FOREIGN KEY(mem_id) -- 외래 키 제약조건을 buy 테이블의 mem_id레 설정
REFERENCES member(mem_id); -- 참조할 기준 테이블은 member 테이블의 mem_id 열임
ALTER TABLE 구문을 사용한 예이다. ( •̀ ω •́ )✧
만약 기준 테이블의 열이 변경될 경우에는 어떻게 해야 할까?!
기본 키 - 외래 키로 맺어진 후에는 기준 테이블의 열 이름이 변경되지 않는다.
열 이름이 변경되면 참조 테이블의 데이터에 문제가 발생하기 때문이다~
삭제도 같은 오류로 진행되지 않는다.
기준 테이블의 열 이름이 변경될 때 참조 테이블의 열 이름이 자동으로 변경되면 더 효율적일 것이다!
이런 기능을 지원하는 것이 ON UPDATE CASCADE 문이다.
ON UPDATE CASCADE 문은 기준 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제되는 기능이다.
ALTER TABLE buy -- buy 수정
ADD CONSTRAINT -- 제약 조건 추가
FOREIGN KEY(mem_id) -- 외래 키 제약조건을 buy 테이블의 mem_id레 설정
REFERENCES member(mem_id); -- 참조할 기준 테이블은 member 테이블의 mem_id 열임
ON UPDATE CASCADE
ON DELETE CASCADE;기타 제약 조건
고유 키 제약조건은 '중복되지 않는 유일한 값'을 입력해야 하는 조건이다.
기본 키 제약조건과 거의 비슷하지만, 차이점은 고유 키 제약 조건은 NULL 값을 허용한다는 점이다.
NULL 값은 여러 개가 입력되어도 상관없다.
또한 기본 키는 테이블에 1개만 설정해야 하지만, 고유 키는 여러 개를 설정해도 된다.
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
( mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL,
email CHAR(30) NULL UNIQUE
);
체크 제약조건은 입력되는 데이터를 점검하는 기능을 한다.
열의 정의 뒤에 CHECK(조건)을 추가해 주면 된다. (*^_^*)
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
( mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL CHECK (height > = 100),
phone1 CHAR(3) NULL
);
필요하다면 ALTER TABLE 문으로 제약 조건을 추가해도 된다.
ALTER TABLE member
ADD CONSTRAINT
CHECK (phone1 IN ('02', '031', '032', '054', '055', '061' ));
기본값 정의는 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법이다.
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
( mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL DEFAULT 160,
phone1 CHAR(3) NULL
);
ALTER TABLE 사용 시 열에 DEFAULT를 지정하기 위해서는 ALTER COLUMN 문을 사용한다.
ALTER TABLE member
ALTER TABLE phone1 SET DEFAULT '02';
기본값이 설정된 열에 기본값을 입력하려면 default라고 써주면 된다.
널 값을 허용하려면 생략하거나 NULL을 사용하고 허용하지 않으려면 NOT NULL을 사용한다.
다만 PRIMARY KEY가 설정된 열에는 NULL 값이 있을 수 없으므로 생략하면 자동으로 NOT NULL로 인식된다.
NULL 값은 '아무것도 없다'라는 의미로, 공백이나 0과 다르다!
가상의 테이블: 뷰
뷰는 데이터베이스 개체 중에 하나이다.
뷰는 한 번 생성해 놓으면 테이블이라고 생각하고 사용해도 될 정도로 거의 동일한 개체로 취급한다.
뷰는 테이블처럼 데이터를 가지고 있지는 않다.
뷰는 단순 뷰와 복합 뷰로 나뉘는데 단순 뷰는 하나의 테이블과 연관된 뷰,
복합 뷰는 2개 이상의 테이블과 연결된 뷰를 말한다.
뷰의 개념
뷰의 실체가 SELECT 문이 된다.
CREATE VIEW 뷰_이름
AS
SELECT 문;
이것이 뷰를 만드는 형식이다. o(*^＠^*)o
뷰를 만든 후에 뷰에 접근하는 방식은 테이블과 동일하게 SELECT 문을 사용한다.
SELECT 열_이름 FROM 뷰_이름
[WHERE 조건];
뷰의 작동
뷰는 기본적으로 '읽기 전용'으로 사용되지만, 뷰를 통해서 원본 테이블의 데이터를 수정할 수도 있다.
뷰를 사용하는 이유
1. 보안에 도움이 된다.
- 데이터 베이스도 사용자마다 테이블에 접근하는 권한에 차별을 둬서 처리하고 있으며,
사용자별 권한이 데이터베이스 보안의 중요한 주제 중 하나이다.
1. 복잡한 SQL을 단순하게 만들 수 있다.
- 복잡한 SQL 코드를 뷰로 생성해 놓고 사용자들은 해당 뷰에만 접근하도록 하면 된다.
뷰의 실제 작동
기본적인 뷰를 생성할 때는 별칭을 사용하는데 중간에 띄어쓰기가 가능하다.
별칭은 열 이름 뒤에 작은따옴표 또는 큰따옴표로 묶어주고 형식상 AS를 붙여준다.
단, 뷰를 조회할 때는 열 이름에 공백이 있으면 백틱으로 묶어줘야 한다.
CREATE VIEW v_viewtest1
AS
SELECT B.mem_id 'Member ID', M.mem_name AS 'Member Name',
B.prod_name "Product Name",
CONCAT(M.phone1, M.phone2) AS "Office Phone"
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;
SELECT DISTINCT `Member ID`, `Member Name` FROM v_viewtest1;
뷰의 수정은 ALTER VIEW 구문을 사용하며, 열 이름에 한글을 사용해도 된다. \^o^/
ALTER VIEW v_viewtest1
AS
SELECT B.mem_id '회원 아이디', M.mem_name AS '회원 이름',
B.prod_name "제품 이름",
CONCAT(M.phone1, M.phone2) AS "연락처"
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;
SELECT DISTINCT `회원 아이디`, `회원 이름` FROM v_viewtest1;
뷰의 삭제는 DROP VIEW를 사용한다.
DROP VIEW v_viewtest1;
데이터베이스를 생성할 때는 CREATE 개체_종류를 사용한다.
예로 뷰를 생성할 때는 CREATE VIEW를 사용한다.
데이터베이스를 수정할 때는 ALTER 개체_종류를 사용한다.
예로 테이블을 수정할 때는 ALTER TABLE을 사용한다.
데이터베이스의 개체를 삭제할 때는 DROP 개체_종류를 사용한다.
예로 스토어드 프로시저를 삭제할 때는 DROP PROCEDURE을 사용한다.
DESCRIBE v_viewtest2;
뷰도 테이블과 동일하게 정보를 보여준다.
주의할 점은 PRIMARY KEY 등의 정보는 확인되지 않는다.
SHOW CREATE VIEW 문으로 뷰의 소스 코드도 확인할 수 있다.
SHOW CREATE VIEW v_viewtest2;
평균 키가 167 이상인 뷰를 생성했다. o(*￣▽￣*)ブ
CREATE VIEW v_height167
AS
SELECT * FROM member WHERE height >= 167;
SELECT * FROM v_height167;DELETE FROM v_height167 WHERE height < 167;
이 코드를 실행하면 167 미만인 데이터가 없기 때문에 삭제될 데이터가 없다.
INSERT INTO v_height167 VALUES('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01');
SELECT * FROM v_height167;
위 코드에서는 입력은 되지만 167 이상만 조회가 되므로 입력한 티아라는 보이지 않는다.
이때 처음부터 167 이상의 데이터만 입력되도록 하는 것이 논리적으로 바람직함으로 이럴 때 사용하는 예약어가
WITH CHECK OPTION이다. 이를 통해 뷰에 설정된 값의 범위가 벗어나는 값은 입력되지 않도록 할 수 있다.
ALTER VIEW v_height167
AS
SELECT * FROM member WHERE height >= 167
WITH CHECK OPTION;
INSERT INTO v_height167 VALUES('T0B', '텔레토비', 4, '영국', NULL, NULL, 140, '1995-01-01');
SELECT * FROM v_height167;
이때 140을 입력해서 에러가 발생한다.
다음은 테이블의 삭제이다. (●ˇ∀ˇ●)
현재 여러 개의 뷰가 두 테이블과 관련이 있는데도 테이블이 삭제되었다.
이때 다시 뷰를 조회하면 에러가 뜬다.
DROP TABLE IF EXISTS buy, member;
SELECT * FROM v_height167;
뷰가 조회되지 않으면 CHECK TABLE 문으로 뷰의 상태를 확인해 볼 수 있다.
가면 갈수록 내용은 어려워집니닷............
제가 부족하다고 생각하는 부분을 강의를 보며 다시 한번 공부하는 게 최선의 방법인 것 같아유.
모듀모듀 파이팅이에용~!
'레쭈고 혼공스' 카테고리의 다른 글
| 혼공스 6주 차 (˘･_･˘) (0) | 2024.08.17 |
|---|---|
| 혼공스 5주 차 (oﾟvﾟ)ノ (7) | 2024.08.03 |
| 혼공스 3주 차 (*￣3￣)╭ (0) | 2024.07.19 |
| 혼공스 2주 차 (*￣3￣)╭ (0) | 2024.07.14 |
| 혼공스 1주 차 ^^ (0) | 2024.07.06 |