SQL 문법 MariaDB (체크, 식별자, 기본키 설정, 복합키, 유니크, 일련번호 필드, AUTO_INCREMENT, 조인(JOIN), 단순조인)

1. 연습문제 (기본값 적용) 

# 직원 테이블에 각 필드에 기본값을 적용하여 tStaffDefault 테이블을 생성하라
# 부서는 영업부, 직급은 수습, 초봉은 280, 성취도는 1.0의 기본값을 적용하라
# 기본값을 지정해도, NULL허용이라면 NULL 기입 가능 
CREATE TABLE tStaffDefault (
	NAME CHAR(10) PRIMARY KEY,
	depart CHAR(10) DEFAULT '영업부' NOT NULL,
	gender CHAR(5) NOT NULL,
	joindate DATE NOT NULL,
	grade CHAR(10) DEFAULT '수습' NOT NULL,
	salary INT DEFAULT 280 NOT NULL,
	score DECIMAL(5,2) DEFAULT 1.0);

 

2. 체크 

 - 체크 제약은 필드의 값 종류를 제한 

 - 추가할 값의 범위 제한 및 허용 값 지정

 - 일정한 범위 일때는 최소값, 최대값 지정 대신 BETWEEN AND 조건문 편리

 - 삽입할 때 뿐만 아니라 UPDATE 할 때도 체크 제약 조건을 점검

CREATE TABLE tCheckTest (
	gender CHAR(3) NULL CHECK(gender = '남' OR gender = '여'),
	grade INT NULL CHECK (grade >= 1 AND grade <= 3),
	origin CHAR(3) NULL CHECK (origin IN ('동','서','남','북')),
	NAME CHAR(10) NULL CHECK (NAME LIKE '김%')
	);
    
# 정상 실행
INSERT INTO tcheckTest (gender) VALUES ('여');
INSERT INTO tcheckTest (grade) VALUES (1);
INSERT INTO tcheckTest (origin) VALUES ('동');
INSERT INTO tchecktest (NAME) VALUES ('김좌진');

# 오류 발생
INSERT INTO tchecktest (gender) VALUES ('노'); # 남 또는 여 입력 가능
INSERT INTO tchecktest (grade) VALUES (0); # 1~3 정수만 입력 가능
INSERT INTO tchecktest (origin) VALUES ('중'); # [동 서 남 북]만 입력가능 
INSERT INTO tchecktest (NAME) VALUES ('청산리'); # '김'으로만 시작하는 문자만 입력가능

 

3. 연습문제 2

# 직원 테이블의 각 필드에 제약 조건을 설정하여 부서는 영업부, 총무부, 인사과 중 하나만
# 성별은 남 아니면 여로 제한하고, 월급은 0보다 크다는 조건만 설정하라

CREATE TABLE tStaffTest (
	NAME CHAR(15) NOT NULL,
	depart CHAR(10) NOT NULL CHECK (depart IN ('영업부', '총무부', '인사과')),
	gender CHAR(3) NOT NULL CHECK(gender IN ('남', '여')),
	joindate DATE NOT NULL,
	grade CHAR(10) NOT NULL,
	salary INT(11) NOT NULL CHECK(salary > 0),
	score DECIMAL(5,2) NULL
	);

 

4. 식별자

 - 테이블의 특정 레코드를 읽거나 변경하려면 레코드끼리 구분할 수 있는 고유의 키가 필요

 - 키는 1. 값이 꼭 있어야 하며, 2. 구분을 위해 고유값을 가져야 함

 - 레코드를 가장 잘 대표하는 키 하나를 골라 기본키 Primary Key 로 선정. 줄여서 PK.

 - 대표성, 자주 참조하는 속성, 가급적 짧은 속성

 - 회원 목록 테이블이라면 회원 ID가 가장 적합한 기본키 (회원은 반드시 ID가 있어야 하고 같은 ID 회원은 둘 이상 X)

 

5. 기본키 설정

 - 선언 위치에 따라 컴럼 제약과 테이블 제약이 있음

 - PRIMARY KEY 제약은 NOT NULL 속성을 포함함.

 - 지정할 때, 컬럼 제약이 간단하지만 여러 필드나 복합키, 외래키 지정할 때는 테이블 제약 사용

CREATE TABLE 테이블
(
	필드 선언,
    	필드 선언, <- 이 위치에 오면 컬럼 제약
    	필드 선언,
          	<- 이 위치에 오면 테이블 제약
);

CREATE TABLE tcity2( 
	NAME CHAR(10),
	AREA INT NULL,
	popu INT NULL,
	metro CHAR(1) NOT NULL,
	region CHAR(6) NOT NULL,
	CONSTRAINT pk_tcity_name PRIMARY KEY(NAME)
	);

 

6. 복합키

 - 두개 이상의 필드를 묶어 기본키로 지정하는 것 [복합키 Composite Key]

# 복합키 이렇게 코드 작성하면 오류
CREATE TABLE tcity3( 
	NAME CHAR(10) PRIMARY key,
	AREA INT NULL ,
	popu INT NULL,
	metro CHAR(1) NOT NULL,
	region CHAR(6) PRIMARY key
	);
    
# 복합키 지정 시 아래처럼 작성
CREATE TABLE tcity3( 
	NAME CHAR(10) NOT null,
	AREA INT NULL ,
	popu INT NULL,
	metro CHAR(1) NOT NULL,
	region CHAR(6) NOT NULL,
	CONSTRAINT PK_tcity_name_region PRIMARY KEY (NAME, region)
	);
    
# 이렇게 하면 이름이 같은 두 개의 도시를 저장할 수 있음
INSERT INTO tCity3 VALUES ('광주', '전라', 123, 456, 'y');
INSERT INTO tCity3 VALUES ('광주', '경기', 123, 456, 'n');

 

7. 유니크

 - 필드의 중복값을 방지하여 모든 필드가 고유한 값을 가지도록 강제

 - 기본키는 NULL을 허용하지 않지만 '유니크는 NULL을 허용', 단 NULL도 딱 하나만 존재 가능

 - 유니크는 테이블 당 개수에 상관없이 지정 가능

 - 인덱스를 생성하더라도 기본키의 인덱스와는 종류와 효울이 다름

 - 유니크는 기본키를 보조하는 중복 방지 제약

 - 두 개 이상의 키를 묶어 복합 UNIQUE 제약을 걸 수도 있음

CREATE TABLE tCityUnique (
	NAME CHAR(10) PRIMARY KEY,
	AREA INT NULL,
	popu INT UNIQUE NULL,
	metro CHAR(1) NOT NULL,
	region CHAR(6) NOT null
	);

 

8. 일련번호 필드

 - 기본키는 레코드의 유일성을 보장할 뿐만 아니라 관계를 형성하는 중요한 역할.

 - 하지만 기본키로 쓸만한 마땅한 필드가 없는 경우도 있음

 - 계속 증가하는 일련번호를 붙이면 NULL도 아니고 고유성도 부여할 수 있음

 

9. AUTO_INCREMENT

 - MariaDB는 시퀀스를 지원하지 않음

 - 대신 IDENTITY와 거의 유사한 AUTO_INCREMENT 구문을 지원

 - 필드 선언문에 AUTO_INCREMENT라고 선언하면 자동 증가하는 일련번호가 매겨짐

CREATE TABLE tSale
( 
	saleno INT AUTO_INCREMENT PRIMARY KEY,
	customer NCHAR(10),
	product NCHAR(30)
	);
    
# saleno는 1부터 1씩 증가하는 일련번호이며 초기값, 증가값은 지정할 수 없음
# 삽입할 때 saleno는 빼고 나머지 필드의 값만 지정
INSERT INTO tsale (customer, product) VALUES ('단군', '지팡이');
INSERT INTO tsale (customer, product) VALUES ('고주몽', '고등어');

# 2번을 지운 후 다시 삽입하면 3번으로 지정됨
DELETE FROM tsale WHERE saleno = 2;
INSERT INTO tsale (customer, product) VALUES ('박혁거세', '계란');

# 일련번호를 특정값으로 지정하는 것은 항상 가능. 다만, 중복되지는 말아야 함
INSERT INTO tsale (saleno, customer, product) VALUES (2, '고주몽', '고등어');

 

테이블 > 옵션 > 자동증가 : 창에 다음 지정될 INDEX 값 확인 가능

 

 - 일련번호를 특정 값으로 리셋할 때는 ALTER 명령을 사용

 - 일련번호를 리셋하면 이후 삽입하는 레코드는 리셋한 번호부터 순서대로 증가하는 값을 받음

 - 최후값은 알아낼 때는 LAST_INSERT_ID() 함수를 사용

ALTER TABLE tsale AUTO_INCREMENT = 100;
INSERT INTO tsale (customer, product) VALUES ('왕건', '너구리');
UPDATE tsale SET product = '짜파게티' WHERE saleno = LAST_INSERT_ID();

 

10. 조인(Join)의 정의

 - 여러 개의 테이블을 조건에 맞게 조회하는 것

# 테이블 생성
CREATE TABLE tcar
(
car VARCHAR(30) NOT NULL,
capacity INT NOT NULL,
price INT NOT NULL,
maker VARCHAR(30) NOT NULL
);

INSERT INTO tCar (car, capacity, price, maker) VALUES ('소나타', 2000, 2500, '현대');
INSERT INTO tCar (car, capacity, price, maker) VALUES ('티볼리', 1600, 2300, '쌍용');
INSERT INTO tCar (car, capacity, price, maker) VALUES ('A8', 3000, 4800, 'Audi');
INSERT INTO tCar (car, capacity, price, maker) VALUES ('SM5', 2000, 2600, '삼성');

CREATE TABLE tMaker
(
maker VARCHAR(30) NOT NULL, -- 회사
factory CHAR(10) NOT NULL, -- 공장
domestic CHAR(1) NOT NULL  -- 국산 여부, Y/N
);

INSERT INTO tmaker (maker, factory, domestic) VALUES ('현대', '부산', 'y');
INSERT INTO tmaker (maker, factory, domestic) VALUES ('쌍용', '청주', 'y');
INSERT INTO tmaker (maker, factory, domestic) VALUES ('Audi', '독일', 'n');
INSERT INTO tmaker (maker, factory, domestic) VALUES ('기아', '서울', 'y');

 

11. 단순 조인

 - 두 개 이상의 테이블을 특별한 조건없이 논리곱(AND)으로 조합

# FROM 절에 출력 대상 테이블의 목록을 콤마로 구분하여 나열
SELECT * FROM tcar, tmaker;

# ANSI 표준은 단순 조인을 크로스 조인으로 정의하여 공식화해 두었는데 이름만 다를 뿐 같은 조인
# 콤마 대신 CROSS JOIN이라고 쓰면 된다.
SELECT * FROM tCar CROSS JOIN tMaker;

# 논리곱에 조건을 지정하면 이 중 원하는 조합만 표시.
# WHERE 조건절에 tCar의 회사명과 tMaker의 회사명이 일치하는 것만 표시하도록 지정
SELECT * FROM tCar, tMaker WHERE tCar.maker = tMaker.maker;