[TIL-260303] DB 기초: 제약 조건, SELECT, WHERE 조건

제약 조건

무결성 제약 조건: 데이터베이스에 저장되는 데이터의 정확성과 일관성을 보장하는 테이블 레벨에서 적용되는 규칙.

종류	설명
NOT NULL	지정 열에 NULL을 허용하지 않음
UNIQUE	지정한 열이 유일한 값을 가져야 함(중복 불가). 단 NULL은 중복에서 제외
PRIMARY KEY	지정한 열이 유일한 값이며 NULL을 허용하지 않음. 테이블에 하나만 지정 가능. NOT NULL + UNIQUE(INDEX)
FOREIGN KEY	다른 테이블의 열을 참조하여 존재하는 값만 입력할 수 있음. 부모 테이블의 Primary Key가 자식 테이블의 Primary Key
CHECK	설정한 조건식을 만족하는 데이터만 입력 가능 (거의 사용 x)

 

NOT NULL

CREATE TABLE emp (
    ename VARCHAR(20) NOT NULL
);

📌 제약 조건 확인하기

select * 
from information_schema.table_constraints 
where table_name = 'emp';

UNIQUE

--테이블 생성 시
CREATE TABLE emp (
empno INT UNIQUE,
ename VARCHAR(20)
);

--테이블 생성 후 제약조건 추가
ALTER TABLE emp 
ADD CONSTRAINT emp_uk_deptno UNIQUE ( deptno );

--제약조건 삭제
ALTER TABLE emp 
DROP CONSTRAINT emp_uk_deptno ;

PRIMARY KEY

특정열을 PRIMARY KEY로 지정하면 해당 열에는 자동으로 인덱스가 생성됨.

-- 테이블 생성 시
CREATE TABLE emp (
    empno INT PRIMARY KEY,
    ename VARCHAR(20)
);

-- 또는
CREATE TABLE emp (
    empno INT,
    ename VARCHAR(20)
    CONSTRAINT emp_pk_empno PRIMARY KEY (empno);
);

-- 테이블 생성 후 제약조건 추가
ALTER TABLE emp
ADD CONSTRAINT emp_pk_empno PRIMARY KEY (empno);

FOREIGN KEY

참조하는 테이블이 먼저 생성되어 있어야 함.

부모 테이블의 PRIMARY KEY나 UNIQUE KEY를 참조 - 참조 관계 컬럼은 데이터형이 일치해야 함.

-- 테이블 생성 시
CREATE TABLE emp (
    empno INT PRIMARY KEY,
    deptno INT,
    FOREIGN KEY (deptno) REFERENCES dept(deptno)
);

-- 또는
CREATE TABLE emp (
    empno INT PRIMARY KEY,
    deptno INT,
    CONSTRAINT emp_fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno)
);

-- 테이블 생성 후 제약조건 추가
ALTER TABLE emp
ADD CONSTRAINT emp_fk_deptno 
FOREIGN KEY (deptno) REFERENCES dept(deptno);

• ON DELETE OPTION
  • NO ACTION: 참조하는 테이블이 존재하면 삭제 불가 (기본값)
  • RESTRICT: 참조하는 테이블이 존재하면 삭제 불가 (NO ACTION과 동일)
  • CASCADE: 참조하는 테이블의 내용까지 함께 삭제
  • SET NULL: 참조하는 테이블의 내용을 NULL로 변경
  • SET DEFAULT: 참조하는 테이블의 내용을 미리 지정된 기본값으로 변경
• ON UPDATE OPTION
  • NO ACTION: 변경 불가 (기본값)
  • RESTRICT: 참조하는 테이블이 존재하면 변경 불가 (NO ACTION과 동일)
  • CASCADE:함께 변경
  • SET NULL: NULL로 변경
  • SET DEFAULT: 미리 지정된 기본값으로 변경

CHECK

프론트엔드 단에서 미리 체크하기 때문에 거의 안 씀.

--테이블 생성 시
CREATE TABLE emp (
empno INT PRIMARY KEY,
ename VARCHAR(20),
comm INT CHECK (comm >= 1 AND comm <= 100)
);

--테이블 생성 후 제약조건 추가
ALTER TABLE emp 
ADD CONSTRAINT emp_ck_comm
CHECK (comm >= 1 AND comm <= 100);

--제약조건 삭제
ALTER TABLE emp 
DROP CONSTRAINT emp_ck_comm;

--여러 값 중 하나 선택
ALTER TABLE emp 
ADD CONSTRAINT emp_ck_comm
CHECK (comm IN (10000, 20000, 30000, 40000, 50000));

DEFAULT

데이터를 입력하지 않아도 지정된 값이 기본으로 입력됨.

--테이블 생성 시
CREATE TABLE emp2 (
empno INT PRIMARY KEY,
ename VARCHAR(20),
hiredate DATE DEFAULT CURRENT_DATE() -- 현재 날짜가 기본값
);

--테이블 생성 후 수정
ALTER TABLE emp2 
MODIFY hiredate DATE DEFAULT CURRENT_DATE();

 

---

데이터 조회 (SELECT)

컬럼 이름에 ALIAS 사용

• AS로 컬럼에 별칭 부여
  • SAL*12+COMM AS ANNUAL
• AS없이 컬럼에 별칭 부여
  • SAL*12+COMM ANNUAL
• “”로 별칭 부여
  • SAL*12+COMM "ANNUAL"
  • SAL*12+COMM AS "ANNUAL"

산술 연산

• *와 /연산은 +,- 연산 이전에 실행
• 우선순위가 같은 경우에는 왼쪽에서 오른쪽으로 진행
• 연산자 우선순위를 임의로 바꾸려면 괄호()를 사용
• % (나머지) 연산은 MySql만 가능

NULL

• NULL: 미확정, 알 수 없는 값
• NULL은 연산, 할당, 비교가 불가능
• NULL 처리 함수

-- IFNULL: NULL이면 대체값 반환
select ename, sal, sal*12, sal*12+ifnull(comm, 0) annual
from emp;

-- IF: 조건식이 참이면 첫 번째 값, 거짓이면 두 번째 값 반환
select ename, sal*12+if(comm is null, "수당없음", comm) annual
from emp;

--NULLIF: 두 값이 같으면 NULL, 다르면 첫 번째 값 반환
SELECT NULLIF(10, 10);  --NULL 반환
SELECT NULLIF(10, 20);  --10 반환

IFNULL 을 많이 쓴다(오라클은 NVL).

DISTINCT

동일한 데이터 값들의 중복을 제거함.

SELECT deptno FROM emp; -- 중복 포함
SELECT DISTINCT deptno FROM emp; -- 중복 제거

ORDER BY

결과 데이터를 정렬할 때 사용, SELECT 구문의 마지막에서 사용된다.

• ASC: 오름차순 (기본값)
• DESC: 내림차순

---

WHERE 조건과 연산자

• WHERE 조건: 조건을 만족하는 행만 검색한다.
• 하나 이상의 컬럼, 표현식, 상수 및 비교연산자의 결합
• 문자와 날짜는 작은따옴표 안에 표시해야 한다. (MySql은 큰따옴표 해도 가능. 그래도 그냥 홑따옴표로 하기)

❗❗결과가 잘 나왔는지 꼭 확인하기❗❗

 

비교연산자

프로그래밍 언어와 같이 >, <, >=, <=, =, != 등이 있다.

SELECT * FROM emp WHERE sal > 3000;
SELECT * FROM emp WHERE job = 'MANAGER';
SELECT * FROM emp WHERE hiredate < '1982-01-01';

논리연산자

AND, OR, NOT 등이 있다.

--AND 연산자
SELECT userID , name 
FROM userTbl
WHERE birthyear >= 1970 AND height >= 182;

--OR 연산자
SELECT userID , name 
FROM userTbl
WHERE birthyear >= 1970 OR height >= 182;

-- NOT 연산자
SELECT userID , name 
FROM userTbl
WHERE NOT birthyear >= 1970; -- 이렇게 잘 안씀. 그냥 < 쓰지

NOT과 IN

NOT: 연산자 앞에 위치하여 IN, BETWEEN, LIKE, IS NULL 연산자와 함께 복합적으로 사용하는 경우가 많다.

SELECT userID , name 
FROM userTbl
WHERE addr NOT IN ('경남','전북');

IN: 특정 열의 데이터 값만 조회하고자 하는 경우 사용한다.

SELECT userID , name 
FROM userTbl
WHERE addr IN ('경남','전북');

BETWEEN … AND …

하나의 컬럼 값이 특정 범위에 속하는지 확인한다.

SELECT *
FROM usertbl
-- 180 이상, 183 이하
WHERE height BETWEEN 180 AND 183;
-- WHERE height >= 180 AND height <= 183; 와 동일

LIKE와 와일드 카드

컬럼에 저장된 데이터의 일부가 포함된 데이터를 조회한다.

와일드 카드: 특정 문자 또는 문자열을 대체하거나 문자열 데이터의 패턴을 표기하는 특수 문자

--'윤'으로 시작하는 이름
SELECT * FROM userTbl WHERE name LIKE '윤%';
--두 번째 글자가 '비'인 이름
SELECT * FROM userTbl WHERE name LIKE '_비%';
--'AM'이 포함된 이름
SELECT * FROM employees WHERE name LIKE '%AM%';
--'AM'이 포함되지 않은 이름
SELECT * FROM employees WHERE name NOT LIKE '%AM%'

IS NULL

임의의 컬럼이 NULL인지 아닌지를 구별하기 위해 사용한다.

--아래 쿼리는 결과가 나오지 않음 (잘못된 방법)
SELECT * FROM buytbl WHERE groupname = NULL;
--올바른 NULL 비교 방법
SELECT * FROM buytbl WHERE groupname IS NULL;
--NULL이 아닌 데이터 조회
SELECT * FROM buytbl WHERE groupname IS NOT NULL; -- NOT IS NULL이 아닌 IS NOT NULL

LIMIT

상위 N개만 출력할 수 있게 하여 불필요하게 많은 양의 데이터가 출력 되지 않도록 한다.

--처음부터 5개 출력
SELECT * FROM employees LIMIT 5;
--10번째부터 5개 출력 (11~15번째 행)
SELECT * FROM employees LIMIT 10, 5;
--다른 표현 방식 (MySQL 8.0+)
SELECT * FROM employees LIMIT 5 OFFSET 10;

 

---

❕느낀점

SELECT 구문을 배우면서 이제 SQL의 핵심 부분을 배우기 시작했음을 느꼈다. 구문을 읽고 이해하는 건 어렵지 않지만, 막상 스스로 문제에 맞는 쿼리를 작성하려고 하면 헷갈리고 어렵다. 쿼리 작성하는 연습을 꾸준히 해야겠다. 그리고 자바도 계속해서 복습해야겠다.