[혼공학습단 12기] 혼자 공부하는 SQL - 4주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 1주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 2주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 3주차
- 테이블
테이블: 표 형태로 구성된 2차원 구조로, 행과 열로 구성
행: 로우, 레코드
열: 컬럼, 필드
- GUI 환경에서 테이블 만들기
CREATE DATABASE naver_db;
다음과 같이 데이터를 입력한다.
naver_db의 기본 키 - 외래 키 관계를 선택할 수 없으므로 코드를 수정해야 하는데,
원래는 다음과 같이 창이 넘어갈 때 입력해야 한다.
* auto_increment로 지정한 열은 primary key나 unique로 꼭 지정해야 한다.
create table `naver_db`.`buy` (
데이터 입력(GUI에서 이미입력하였으므로 생략)
foreign key(mem_id) references member(mem_id));* 외래 키 이름을 지정하려면 다음과 같이 입력 (명시적으로 이름을 지정하려면 CONSTRAINT 키워드를 사용)
alter table `naver_db`.`buy`
add constraint `buy_ibfk_1` foreign key (mem_id) references member(mem_id);
* 외래 키 이름을 지정하지 않고, 자동으로 받으려면 다음과 같이 입력
alter table `naver_db`.`buy`
add foreign key (mem_id) references member(mem_id);
* 백틱을 사용하는 이유: 예약어와 충돌 방지, 대소문자 구분, 공백이나 특수문자 사용
* 만약 foreign key가 자꾸 생긴다면 다음과 같이 기존의 키들을 삭제한다.
<-- ALTER TABLE 테이블이름 DROP FOREIGN KEY 기존키 이름; -->
ALTER TABLE `naver_db`.`buy` DROP FOREIGN KEY `기존키 이름`;
ALTER TABLE `naver_db`.`buy` DROP FOREIGN KEY `기존키 이름`;
* 오류가 발생하는데, 회원 테이블과 구매 테이블은 기본 키-외래 키로 연결되어 있다. 구매 테이블의 mem_id 값은 반드시 회원 테이블의 mem_id로 존재해야 한다. 회원테이블에 APN 회원이 없기 때문에 발생하는 오류이다. (회원으로 가입하기 전에 물건을 구매하려고 해서 발생하는 오류)
이를 해결하기 위해, APN 회원을 제외하고 테이블에 추가한다.
- p. 226의 market_db의 회원 테이블(member) 생성
drop database if exists naver_db;
create database naver_db;
use naver_db;
drop table if exists member;
create table member
( mem_id char(8) not null primary key,
mem_name varchar(10) not null,
mem_number tinyint not null,
addr char(2) not null,
phone1 char(3) null,
phone2 char(8) null,
height tinyint unsigned null,
debut_date date null
);
drop table if exists buy;
create table buy
( num int auto_increment not null primary key,
mem_id char(8) not null,
prod_name char(6) not null,
group_name char(4) null,
price int unsigned not null,
amount smallint unsigned not null,
foreign key(mem_id) references member(mem_id)
);
- p. 229 데이터 입력한 후 인증
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015-10-19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016-8-8');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015-1-15');
INSERT INTO buy VALUES( NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES( NULL, 'BLK', '맥북프로', '디지털', 1000, 1);
INSERT INTO buy VALUES( NULL, 'APN', '아이폰', '디지털', 200, 1); ----- apn이 member 테이블에 없어서 발생하는 오류
- 확인문제
1. 문자를 저장할 때 사용하는 것 2개: CHAR, VARCHAR
2. 정수형 데이터를 0부터 입력되도록 설정: UNSIGNED
-128~+127까지 값이 저장: TINYINT
'2022-11-12'와 같은 데이터가 저장: DATE
가변형 문자형으로 짧거나 긴 문자가 뒤죽박죽 입력될 때 사용: VARCHAR
3. GUI에서 테이블을 생성하는 방식의 설명 중 거리가 먼 것:
3) UNSIGNED는 UQ부분을 체크한다.(원래 UN 부분을 체크한다.)
4. 두 테이블을 기본 키-외래 키로 설정하는 것에 대한 설명으로 거리가 먼 것:
3) 기본키와 외래키는 일반적으로 한 테이블에 모두 설정 (원래 다른 테이블에 설정한다.)
5. 데이터 형식 또는 제약 조건이 적절하지 않은 행 번호 2개
create table member
( mem_id char(8) not null foreign key,-----(1) primary key로 설정
mem_name varchar(10) not null,
mem_number tinyint not null,
addr char(2) not null,
phone1 char(3) null,
phone2 char(8) null,
height tinyint null,-----(2) tinyint unsigned 또는 smallint, int, bigint로 설정
debut_date date null
);- 제약 조건
기본키, 외래키
이메일과 휴대폰처럼 중복되지 않는 열: 고유키 지정
제약 조건(예: 실수로 200 입력 방지): 체크(check)
매번 입력이 귀찮다면 제약 조건으로 기본값 설정
값 입력 필요 시 not null 제약조건 존재
제약조건(constraint) : 데이터의 무결성을 지키기 위해 제한 하는 조건
데이터의 무결성: '데이터에 결함이 없음'
데이터의 결함이 없는 것: 데이터의 무결성
결함 방지를 위해 회원 테이블의 아이디를 기본키로 지정
기본키의 조건: '중복되지 않고, 비어있지도 않음'
primary key 제약조건
foreign key 제약 조건
unique 제약 조건
check 제약조건
default 정의
null 값 허용
*테이블을 삭제 하는 순서: 회원 테이블과 구매 테이블은 기본키-외래키로 연결되어 있는데, 회원 테이블의 회원만 구매 테이블에 입력될 수 있다. 구매 테이블이 있는데 회원 테이블을 삭제하면, 구매 테이블의 이름과 연락처를 알고 싶어도 조회가 불가능하므로, 외래키가 설정된 테이블을 먼저 삭제해야 한다.
- alter table에서 기본키 제약조건과 외래키 제약 조건
drop table if exists member;
create table member
(mem id char(8) not null,
mem name varchar(10) nut null,
height tinyint unsigned null
);
alter table member
add constraint
primary key(mem_id);----기본키 제약조건
create table buy
(num int auto_incrememt not null primary key,
mem_id char(8) not null,
prod_name char(6) not null
);
alter table buy
add constraint
foreign key(mem_id)
references member(mem_id);----외래키 제약조건- 외래키 제약조건
두 테이블 사이의 관계를 연결해주고, 테이블의 무결성을 보장해주는 역할로,
외래키가 설정된 열은 다른 테이블의 기본키와 연결된다.
기본키가 있는 회원 테이블을 기준 테이블이라고 부르고, 외래키가 있는 구매 테이블을 참조 테이블이라고 부른다.
*기본키-외래키 관계를 맺으면 오류가 없는 데이터가 된다.
참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본키나 고유키로 설정되어 있어야 한다.
foreign key(열_이름) references 기준_테이블(열_이름)외래키의 형식은 위와 같다.
*기준 테이블의 열이름과 참조 테이블의 열이름이 반드시 같아야 하는 것은 아니다.
create table member
(mem_id char(8) not null primary key,
...
);
create table buy
(...
user_id char(8) not null,
...
foreign key(user_id) references member(mem_id)
);* ON UPDATE CASCADE
기준 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제되는 기능
drop table if exists buy;
create table buy
(num int auto_increment not null primary key,
mem_id char(8) not null,
prod_name char(6) not null
);
alter table buy
add constraint
foreign key(mem_id) references member(mem_id)
on update cascade
on delete cascade;insert into buy values(null, 'BLK', '지갑');
insert into buy values(null, 'BLK', '맥북');
update member set mem_id = 'pink' where mem_id = 'BLK';
delete from member where mem_id='pink';기준 테이블(회원 테이블)에서 삭제하면, 참조 테이블(구매 테이블)의 데이터도 삭제 된다.
- 기타 제약조건
- 고유키 제약조건: 중복되지 않는 유일한 값을 입력해야 하는 조건
* 고유키 제약 조건은 null 값을 허용한다. null 값은 여러개가 입력되어도 상관없다. 기본키는 테이블에 1개만 설정해야 하지만, 고유키는 여러개를 설정해도 된다.
create table member
( ...
email char(30) null unique);*중복은 비허용, null 값은 허용
- 체크 제약 조건: 입력되는 데이터를 점검하는 조건
* 예) 평균 키(height)에 마이너스 값이 입력되지 않도록 하는 것
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
);데이터를 입력할 떄 체크 제약 조건에 위배되는 행은 오류가 발생한다.
insert into member values('blk', '블랙핑크', 163, null);
insert into member values('twc', '트와이스', 99, null);
| 0 | 19 | 16:53:41 | insert into member values('twc', '트와이스', 99, null) | Error Code: 3819. Check constraint 'member_chk_1' is violated. | 0.000 sec |
테이블을 만든 후에 alter table로 제약조건을 추가하는 것도 가능하다.
- alter 문으로 check 제약조건, 기본값 정의 사용하기
alter table member
add constraint
check (phone1 in ('02','031','032','054'));* in 은 괄호 안에 있는 값 중에서 하나와 같아야 참이 된다.
alter table member
alter column phone1 set default '02';기본값이 설정된 열에 기본값을 입력하려면 default라고 써주고 원하는 값을 입력하려면 해당 값을 써주면 된다.
- 확인문제
1.기본키 제약조건에 대한 설명으로 거리가 먼것:
3) 기본키로 설정된 열은 null값이 입력된다.
(예약어는 primary key를 사용한다, 기본키로 설정하면 클러스터형 인덱스가 생성된다.는 기본키 제약조건에 해당)
2. 기본키를 설정하는 문법에 대한 설명으로 거리가 먼것:
4) 기본키로 설정한 열은 not null을 생략할 수 없으므로 반드시 써줘야 한다.
3. sql에서 두 테이블을 연결하는 외래 키를 설정하는 빈칸에 들어갈 내용을 고르기
create table member
( mid char(8) not null primary key,
mem_name var char(10) not null,
...
);
create table buy
(num int auto_incrememt not null primary key,
mem_id char(8) not null,
prod_name char(6) not null,
foreign key(mem_id) references member(mid)(4)
4. 각 문항이 설명하는 것으로 옳은 것은:
1) 기준 테이블의 기본키를 삭제하면 참조 테이블의 외래키도 삭제 시키는 구문: on delete cascade
2) 기준 테이블의 기본키를 변경하면 참조 테이블의 외래키도 변경 시키는 구문: on update cascade
5. 각 문항이 설명하는 것:
1) 입력되는 데이터가 조건에 맞는지 검사하는 기능: check
2) 값을 입력하지 않으면 자동으로 들어갈 값: default
3) 빈 값을 입력하는 것을 허용하지 않음: not null
- 뷰
데이터 베이스 개체 중의 하나로, 단순 뷰와 복합 뷰로 나윈다.
테이블 처럼 데이터를 가지고 있지는 않지만, 출력된 결과를 보면 결국 테이블의 모양을 지닌다.
select 문으로 실행해서 나온 결과를 테이블로 보아도 된다.
즉, 뷰의 실체가 select 문이 된다.
뷰를 만드는 형식은 상당히 단순하다.
create view 뷰_이름
as
select 문;
뷰를 만든 후, 뷰에 접근 하는 방식은 일반 테이블과 동일하다.
use market_db;
ccreate view v_member
as
select mem_id, mem_name, addr from member;필요한 열만 보거나 조건식을 넣을 수도 있다.
select mem_name, addr from v_member
where addr in ('서울', '경기);사용자가 뷰에 접근하는 방식은 그림과 같이, 뷰를 테이블이라고 생각하고 접근하고, mysql이 뷰안에 있는 select를 실행해서 그 결과를 사용자에게 보내준다. 사용자 입장에는 뷰에서 모두 처리된 것으로 이해하게 된다. 뷰는 기본적으로 '읽기 전용'이지만 뷰를 통해서 원본 테이블의 데이터를 수정할 수도 있다.
뷰는 보안에 도움이 되는데, 원래 테이블의 중요한 개인정보가 뷰에는 들어있지 않기 때문이다.
또한, 복잡한 sql을 단순하게 만들 수 있다.
select b.mem_id, m.mem_name, b.prod_name, m.addr,
concat(m.phone1, m.phone2) '연락처'
from buy b
inner koin member m
on b.mem_id = m.mem_id;
위의 쿼리를 뷰를 사용하면 단순하게 이용할 수 있다.
create view v_memberbuy
as
select b.mem_id, m.mem_name, b.prod_name, m.addr,
concat(m.phone1, m.phone2) '연락처'
from buy b
inner join member m
on b.mem_id = m.mem_id;
select * from v_memberbuy where mem_name = '블랙핑크';
뷰의 실제 생성, 수정, 삭제 부분은 생략한다.
- 확인문제 (p. 271 확인 문제 4번 풀고 인증)
1. 뷰에 대한 설명으로 거리가 먼것:
2) 뷰의 실체는 create table 문입니다. (원래는, 뷰의 실체가 select 문이 된다.)
2. 뷰의 특징으로 거리가 먼 것:
1) 뷰에는 테이블의 모든 열을 포함시켜야 한다. (원래는, 뷰는 테이블에서 필요한 열만 골라서 포함시킬 수 있다.)
3. 뷰의 별칭과 관련된 것으로 가장 거리가 먼 것:
4)select에서는 별칭에 공백이 없어도 백틱으로 묶어줘야 한다. (원래는, 별칭에 공백이 없으면 백틱으로 묶지 않아도 된다. 또한, 별칭은 뷰의 열 이름으로 사용할 수 있고, 한글로 사용이 가능하다.)
4. 기존에 뷰가 있으면 덮어쓰고, 없으면 새로 생성하는 sql문에서 빈칸에 들어갈 내용은:
create or replace view 뷰_이름
as
select 문;2)
5.
1) 뷰의 소스코드를 확인한다: show create view
2) 뷰에 설정된 값의 범위만 입력: with check option
3) 뷰의 상태를 확인: check table