DDL
1. 자료형
- 수치데이터
- 문자데이터
CHAR(N - 255) - 불변, VARCHAR(N - 65535) - 가변,
*수정이 자주발생하면 VARCHAR가 더 안좋다. CHAR는 공간을 확보하면 공간의 크기가 변경되지 않는다. VARCHAR는 공간을 확보하고 데이터를 삽입하면 데이터의 크기로 변경이 된다
-
날짜 데이터
-
BOOL : true 또는 false
-
JSON : 5.7.8 버전 이후에서 제공
-
GEOMETRY : 공간 데이터 형식
2. 테이블 생성
구문
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 테이블이름(
컬럼이름 자료형 [제약조건],
...
[테이블 제약 조건]
)테이블 생성 시 추가하는 옵션
-
ENGINE
- MyISAM : 조회에 유리하도록 만들 때 사용 (조회에 유리)
- InnoDB : 트랜잭션 처리에 유리하도록만들 때 사용 (처리에 유리)
-
auto_increment=초기값
시퀀스(일련번호)의 초기 값을 설정할 수 있음
ALTER TABLE [테이블명] auto_increment=[시작하려는 값]을 이용해서 초기값을 재설정 할 수 있다 -
DEFAULT CHARSET 인코딩방식 : 인코딩 방식 설정 – UTF8로 설정하지 않으면 한글 사용을 못함
-- 인코딩 방식을 적지 않은 경우 기본적으로 데이터베이스를 설치할 때의 인코딩 방식을 따르게 된다
-- 이 경우는 데이터베이스 설치시 utf8로 설정이 돼서 인코딩 방식을 설정하지 않아도 utf8로 설정이 된다.
-- 일반적인 문자의 경우는 utf8로 입력이 가능한데 이모티콘을 사용하는 경우는 utf8mb4로 설정해야 한다.
-- 이렇게 설정되면 varchar의 경우 20바이트가 아니라 utf8 20자가 들어간다
-- 실제로 60바이트가 할당된다
-- 디폴트 인코딩 여부에 상관없이 utf8로 설정하고자 하는 경우에는 varchar 대신 nvarchar를 사용하면 된다
CREATE TABLE SAMPLE1(
name varchar(20)
) ENGINE=MyISAM CHARSET=utf8;
insert into sample1 values('abc');
insert into sample1 values('안녕하세요');
-- utf8로 설정하지 않아도 한글이 잘 입력됨
CREATE TABLE SAMPLE2(
name varchar(20)
) ENGINE=MyISAM;
insert into sample2 values('안녕하세요');
select *
from sample1;
select *
from sample2;인코딩방식 확인방법 :
show variables like 'c%';
->utf8mb4
테이블 생성
contact 테이블
num : 정수이고 일련번호형식, 기본키
name : 영문 20자, 자주 변경되지 않음
address : 영문 100자, 자주 변경됨
tel : 문자 20자
email : 영문 100자, 자주 변경됨
birthday 날짜
조회를 자주 할 것 같고 일련번호의 첫번째 값은 1이고 한글을 사용
-- contact 테이블 생성
CREATE TABLE contact(
num integer auto_increment primary key,
name varchar(20),
address char(100),
tel varchar(20),
email char(100),
birthday date) ENGINE = MyISAM auto_increment = 1 CHARSET = utf8;3. 테이블 수정
컬럼 추가
ALTER TABLE 테이블이름 ADD 컬럼이름 자료형 [FIRST 또는 AFTER 컬럼이름];컬럼 추가
ALTER TABLE contact ADD AGE INTEGER;테이블 구조 확인
-- 테이블 구조 확인
desc 테이블이름;컬럼 삭제
ALTER TABLE 테이블이름 DROP 컬럼이름;컬럼 수정
-- 컬럼 이름이나 자료형 변경
ALTER TABLE 테이블이름 change 이전컬럼이름 새로운 컬럼이름 자료형;
ALTER TABLE 테이블이름 modify 컬럼이름 자료형;
-- NOT NULL 을 수정할 때도 자료형 변경 명령으로 수행한다
-- 제약조건 수정이 아니다NAME CHAR(10) NOT NULL : 10칸
NAME CHAR(10) : 11칸이 되어서 1 칸이 비어있으면 NOT NULL, 1칸이 채워져있으면 NULL
데이터베이스에서 NOT NUL이나 NULL은 크기가 바뀜 따라서 제약조건이지만 자료형의 변경으로 확인하는 것이다.
-- 컬럼 순서 조정
-- 첫번째 위치로
ALTER TABLE 테이블이름 modify column 컬럼이름 자료형 FIRST;
-- 특정 컬럼 뒤로 이동
ALTER TABLE 테이블이름 modify column 컬럼이름 자료형 AFTER 다른 컬럼;예시) tell의 자료형을 integer로 수정
ALTER TABLE contact modify tel integer;테이블 수정
-- 테이블 이름 수정
ALTER TABLE 이전테이블이름 RENAME 새로운테이블명;테이블 삭제
-- 테이블 삭제
DROP TABLE 테이블명;
-- 테이블의 모든 데이터 삭제
TRUNCATE TABLE 테이블명;4. 제약조건
- 테이블의 무결성 유지를 위해서 지켜야 하는 조건
1) NOT NULL
- 필수입력
- 이 설정은 컬럼의 자료형에 영향을 미치므로 컬럼 제약 조건으로 설정 가능하다
2) CHECK 제약 조건
- 컬럼에 대입되는 값을 점검하기 위한 제약 조건
- 컬럼 제약 조건 그리고 테이블 제약 조건으로도 추가가 가능
CHECK(컬럼이름 제약조건)예시) gender는 남또는 여 라는 값만 갖도록하고 age는 1부터 120까지의 값을 갖도록 하는 테이블을 생성
create table sample3(
gender varchar(1) CHECK(gender = '남' or gender = '여'),
age integer CHECK(age >= 1 and age <= 120)
);
insert into sample3 values('남', 70); -- 성공
insert into sample3 values('ㅋ', 70); -- 에러(남 or 여)
insert into sample3 values('남', 150); -- 에러(나이 제한)
unique
- 데이터의 값이 1개만 존재하도록해주는 제약조건
- 이 제약조건이 설정된 곳에는 NULL이 대입이 된다.
- 컬럼 제약조건으로 설정할 수 있고 테이블 제약조건으로 설정할 수 있다.
예제 테이블 제약조건으로 설정학;
create table sample4(
area int,
popu int,
unique(area)
);
insert into sample4 values(1, 100);
insert into sample4 values(1, 100); -- 동일한 area를 ㅎ사용하므로 에러
insert into sample4 values(NULL, 100); -- NULL값은 얼마든지 넣어도 에러가 나지 않음
UNIQUE( , ) 컬럼 2개를 합쳤을 때 유일해야 하므로 1,100 -> 1, 1000이면 에러가 나지않음
create table sample4(
area int,
popu int,
unique(area, POPU)
);
insert into sample4 values(1, 100);
insert into sample4 values(1, 1000); -- 컬럼 2개를 합쳤을 때 유일해야 하므로 1,100 -> 1, 1000이면 에러가 나지않음
insert into sample4 values(NULL, 100); -- NULL값은 얼마든지 넣어도 에러가 나지 않음4 PRIMARY KEY
- 기본키
- NOT NULL이고 UNIQUE
- 테이블의 1개만 지정가능
- 여러개
## 기본키를 2개로 설정하고 싶을 대
create table sample4(
area int,
popu int,
primary key(area, POPU)
);5. primary key와 uniqe
기본킨와 유니크로 설정된 컬럼은 인덱스가 자동으로 생성돼서 조회할 떼ㅐ 빠르다
6. 외래키
외래키 또는 참조키라고도 함
다른 테이블의 데이터를 참조하기 위해서 설정하는 키
현재 테이블에서는 어떤 컬럼이든 외래키가 될 수 있지만 이 속성은 다른테이블에서는 기본키나 유니크여야 한다.
외래키 설정방법
두 개 테이블 사이의 관계를 확인
1 : 1 관계
양쪽 테이블의 기본키를 상대방 테이블의 외래키로 추가
1 : n
1 쪽 테이블의 기본킬를 nWhr 테입르의 외래키로 추가
n : m
양쪽 테이블의 기본키를 가지고 새로 테이블을 만들어서 그 테이블에서 외래키를 설정
외래키를 설정하지 않은 경우
-- 직원
CREATE TABLE tEmployee(
name char(10) primary key,
salary int not null,
addr varchar(30) not null
);
insert into tEmployee values('군계',650,'제주도');
insert into tEmployee values('쥬니',480,'수원');
insert into tEmployee values('헨리',500,'서울');
-- 프로젝트
CREATE TABLE tProject(
projectid int primary key,
employee char(10) not null,
project varchar(30) not null,
cost int
);
# 없는 이름도 삽입 가능
insert into tProject values(1,'아이린','수출',80);
insert into tProject values(2,'쥬니','요가',300);
insert into tProject values(3,'리리','요가',30);
insert into tProject values(4,'군계','사이클',900);
# 헨리는 삭제되어 있는데 프로젝트 테이블에는 남아있음
delete from tEmployee WHERE NAME = '헨리'외래키를 설정하지 않으면 직원 이름이 없어도 추가가 되고 직원이 그만둬도 프로젝트에 이름이 삭제 되지 않는다.
외래키를 설정하면 문제가 사라진다.
-- 직원
CREATE TABLE tEmployee(
name char(10) primary key,
salary int not null,
addr varchar(30) not null
);
insert into tEmployee values('군계',650,'제주도');
insert into tEmployee values('쥬니',480,'수원');
insert into tEmployee values('헨리',500,'서울');
-- 프로젝트
# 현재 테이블의 employee 컬럼은 tEmployee 테이블의 name 을 참조
CREATE TABLE tProject(
projectid int primary key,
employee char(10) not null,
project varchar(30) not null,
cost int,
foreign key(employee) references tEmployee(name);
);
insert into tProject values(1,'쥬니','요가',300);
insert into tProject values(2,'헨리','요가',30);
insert into tProject values(3,'초콜릿','사이클',900); # 참조 테이블에 없는 이름은 추가할 수 없음
delete from tEmployee WHERE NAME = '헨리'
# 삭제 못함 - 데이터가 참조 테이블에서 사용되고 있기 때문에
# 참조하는 테이블에 존재하는 데이터는 삭제할 수 없음
# 참조 당하는 테이블 제거 불가
drop table tEmployee;
없는 이름은 삽입 시 에러가 난다
외래키를 설정하면 제약조건들이 생김
참조 무결성 제약 조건
외래키로 설정된 데이터는 참조할 수 있는 값을 가져야 한다.
null을 가질 수는 있지만 참조할 수 없는 값을 가질 수는 없다.
외래키 옵션
ON DELETE {NO ACTION | CASCADE | SET NULL}
참조당하는데이터가 삭제될 때 동작하는 옵션으로 NO ACTION은 아무일도 하지 않는 것이고,
CASCADE를 설정하면 참조하는 테이블의 데이터도 같이 삭제되고,
SET NULL을 설정하면 참조하는 데이터가 NULL로 변경된다
ON UPDATE 는 수정될 때의 동작이다.
제약조건 수정
1)제약조건 확인
SELECT *
FROM information_schema.table_constraints;2)제약조건 수정
ALTER TABLE 테이블 MODIFY 컬럼이름 자료형 제약조건;3)제약조건 추가
ALTER TABLE 테이블 ADD 제약조건(컬럼이름);4)제약조건 삭제
ALTER TABLE 테이블 DROP CONSTRAINT 제약조건이름름;이경우 제약 조건 이름을 이용해서 삭제를 수행하는 데 제약 조건을 만들 때 이름을 설정하지 않으면 MariaDB가 임의로 제약조건 이름을 설정한다
제약 조건을 만들 때 Constraint 제약조건 이름을 추가해서 만들면 제약 조건 이름을 원하는 대로 만들 수 있다 constraint 제약조건이름
이경우 대부분 기본키는 pk 외래키는 fk 체크제약조건은 ck 등의 약자를 추가하는 경우가 많다.
5) 기본값
- 기본값은 필드값을 지정하지 않았을 때 자동으로 입력되는 값
- Default 라는 키워드와 함께 지정
- 기본값을 입력하고자 할 때는 컬럼을 제외하고 삽입하거나 default로 설정하면 된다
tcityDefault 라는 테이블을 생성
name 인데 고정크기 문자 10개 생성
area 정수, null 허용
popu 정수, null 허용(null)
metro 고정크기 한 글자, 기본 값n, null 허용 x
region 고정크기 문자 6개, null 허용 x
주의점
utf8 : 3byte :
utf8-mb4 : 4byte ㅇ이모티콘 가능
CREATE TABLE tCityDefault(
name char(10),
area int,
popu int null,
metro char(1) default 'n' not null,
region char(6) not null,
constraint pk_tcity primary key(name)); -- 제약조건 설정, not null은 여기서 넣을 수 없음-- 기본값
insert into tCityDefault values('k',1,1,default,'seoul');
-- 기본값 생략하고 삽입시 필드명 작성
insert into tCityDefault (name, area, popu,region) values('l',1,1,'seoul');6) auto_increment
- 일련번호
- 컬럼의 자료형 옆에 기재하면 되는데 기본적으로 자료형은 정수
- 테이블을 만들 때 기본값을 설정할 수 있고 나중에 변경할 수 있다.
- 테이블 당 1개만 생성 가능
- MariaDB에서는 일련번호를 컬럼을 설정해도 직접 값 입력이 가능하다
CREATE TABLE tSale(
saleno int auto_increment,
customer varchar(10)
);
-- 에러 auto_increment 는 키 설정이 꼭 되어야 한다.
-- primary key나 unique 설정해주기
CREATE TABLE tSale(
saleno int auto_increment primary key,
customer varchar(10)
); - auto_increment 컬럼은 기본키나 uniqe 제약조건을 가져야 한다.
insert into tSale(customer) values ('kkim');
insert into tSale(customer) values ('kang');
insert into tSale values (3, 'dfdd'); -- 일련번호에 직접 값을 넣어줘도 동작 o
insert into tSale values (7, 'dfdd');
insert into tSale(customer) values ('gsd'); -- 5,6 사라짐
일련번호에 직접 입력은 가능하지만 일부 번호가 사라질 수 있다.
insert into tSale(customer) values('지원'); -- 일련번호 9
정보를 지웠다가 추가하면 일련번호는 되돌아 가지 않는다
delete from tSale where customer = '지원';
select * from tSale;
insert into tSale(customer) values('지원'); -- 일련번호 10
select * from tSale;일련번호 수정
ALTER TABLE 테이블이름 AUTO_INCREMENT = 시작값;alter table tSale auto_increment = 100;
insert into tSale(customer) values('csafsd'); -- 일련번호 100
DBMS 마다 일련번호 생성 방법이 다르다
- Oracle : Sequence
- MariaDB : Auto_increment
일련번호는 삭제하면 복구되지 않는다.
가장 큰 번호 + 1이렇게 일련번호를 따로 만든다
