SQL 테이블, 무결성 제약, case

TABLE
- 테이블 목록 확인 : use 명령을 사용하여 database를 먼저 선택한 후 SHOW TABLES로 확인
- 테이블 구조 확인 : DESC테이블명
- 테이블 변경 : RENAME TABLE 원본 테이블명 TO 변경 테이블명


데이터 유형
1. 숫자형
2. 문자형
   - TEXT열에는 INDEX를 사용할 수 없다
   - TEXT열에는 DEFAULT속성을 사용할 수 없다
3. 날짜형
   - DATETIME : 5BYTE
   - TIMESTEP : 4BYTE


테이블 생성
- CREATE TABLE 테이블명(컬럼명1 타입(크기)제약조건, ...)

ex)학번(mid), 성명(irum), 연락처(phone)을 갖는 student테이블 생성
create table student(
   mid varchar(10),
   irum varchar(50),
   phone varchar(20)
);

테이블 제거
- DROP TABLE 테이블명;

테이블 복사
- CREATE TABLE 테이블명 AS SELECT ...;
- SELECT절에 의해 선택된 컬럼이나 데이터를 사용하여 새로운 테이블을 생성할 수 있다

테이블명 변경
- RENAME TABLE 원본 TO 변경





테이블 구조 변경

[컬럼추가]
- ALTER TABLE 테이블명 ADD [COLUMN] 컬럼명 컬럼유형[제약조건];

[컬럼 삭제]
- ALTER TABLE 테이블명 DROP [COLUMN] 필드명;

[컬럼 유형 변경]
- ALTER TABLE 테이블명 MODIFY[COLUMN] 필드명;

[필드명 수정]
- ALTER TABLE 테이블명 CHANGE[COLUMN] 이전 필드명 수정필드명 데이터 유형[제약조건];

무결성 제약조건의 종류
제약 조건명
1. PRIMARY KEY(PK)
- NOT NULL, UNIQUE의 특성을 갖고 있고 테이블당 하나만 존재
- 둘 이상의 컬럼을 묶어 하나의 PK를 선언할 수 있다
- 자동 INDEX가 만들어진다
 
2. FOREIGN KEY(FK)
- 왜래키
- 자식테이블의 데이터가 존재했을 때 부모 테이블의 데이터가 삭제되거나
  수정되는 것을 예방한다
- ON CASCASE UPDATE나 ON CASCASE DELETE 옵션을 사용하여 부모 테이블의 데이터가 삭제되거나
  수정되었을 때 자식 테이블의 데이터를 삭제하거나 수정할 수 있게 한다
- 부모 테이블의 컬럼은 PK나 UNIQUE제약 조건이 설정되어 있어야한다

3. NOT NULL
- NULL값을 허용하지 않는다
- 컬럼 생성 시 기본이 NULL이기 때문에 컬럼에 제약 조건을 수정할 때
  ADD를 사용하지 않고 MODIFY를 사용한다

4. UNIQUE
- 하나의 NULL은 허용하고, 중복되지 않는 데이터를 유지하기 위해 사용된다
- 자동으로 INDEX가 만들어진다

5. CHECK
- 허용되는 값을 일정하게 지정하거나 범위를 줄 수 있다
- MariaDB 10, Mysql8 버전 이상부터 지원

6. DEFAULT
- 입력되는 값이 NULL값일 때 특정값이 입력값이 되도록 설정할 수 있다
- 제약조건 수정할 때 ADD를 사용하지 않고 MODIFY를 사용한다


제약조건 확인
- 데이터 베이스명을 information_schema로 변경한 뒤 작업한다
-> USE information_schema
    show tables
    SELECT *FROM table_constraints

with rollup : group by된 그룹별 소계를 다시 계산해준다
ex)oderNumber별 합계와 전체 합계
select orderNumber, sum(priceEach) from orderdetails group by orderNumber with rollup;
- 전체를 기준으로 합계를 계산해준다

ex)
select ordernumber, orderLineNumber, sum(priceEach)
from orderdetails
group by orderNumber(1차 그룹 중), orderLineNmuber(2차 그룹)
with rollup;
<ordernumber> <orderLineNumber> <sum(priceEach)>
100                  1                           
100                  2                          
100                  3  
200                  2
200                  3
300                  1                 


CASE문
- SELECT절에서 CASE문을 사용할 수 있는데, 
  이는 다른 언어에서 SWITCH문과 매우 유사하게 사용할 수 있는 제어문

<CASE문 [기본구조] type1>
CASE 
   WHEN 조건1 THEN 처리1
   WHEN 조건2 THEN 처리2
   ...
[ELSE 기타 처리]
END AS 닉네임
*case문에 컬럼명이 없으면 when절의 조건1, 조건2에서 범위를 줄 수 있다


<CASE문 [기본구조]type2>
CASE 컬럼명
   WHEN 상수1 THEN 처리1
   WHEN 상수2 THEN 처리2
   ...
[ELSE 기타 처리]
END AS 닉네임
*case문에 컬럼명이 있으면 when절에서는 상수만 사용할 수 있다


조건절에서의 like연산자
- where name like '_' => name이 한 글자인 데이터
   - '_(언더바 한 개)' : 한 자리 데이터

- where name like '__' => name이 두 글자인 데이터
   - '__(언더바 두 개)' : 두 자리 데이터

- where phone like '0_0' => phone 010(O), 070(O), 011(X)
 
- where name like '김%' => name이 '김'으로 시작하는(%는 '없다'도 포함)

- where name like '%광%' => name에 앞뒤중간순서 상관없이 문자 '광'을 포함