---

DDL,DML,DCL

1. DDL, DCL은 명령어가 하나의 오토커밋 트랜젝션이므로 완료시 되돌릴 수 없다.
2. DML도 오토커밋이 디폴트인경우가 많다(mysql, postgresql)
3. DDL(정의): 데이터베이스, 테이블 등을 정의
4. DML(조작): 데이터를 조작
5. DCL(제어): 권한, 커밋 롤백 등을 제어
6. DB마다 명령어가 미묘하게 다르다(DCL에서 특히 그렇다.)
7. mysql과 maria는 명령어 체계가 거의 같은편(maria가 mysql의 오픈소스 모방같은 것)
8. 쿼리문에는 대소문자가 없다(모두 소문자 취급)

---

DDL(데이터 정의) 명령어

순서

1. 예제
2. 생성
   • DB 생성 및 확인(create database)
   • 테이블 생성 및 확인(create table)
   • 자료형
   • 제약사항
   • 인덱스 생성(create index)
3. 삭제
   • DB 삭제(drop database)
   • 테이블 삭제(drop table
   • 테이블 초기화(truncate table)
   • drop, truncate, delete과 차이
4. 변경
   테이블 변경(alter)

예제

1. 데이터베이스 리스트 확인 & DB생성 & 접속

show databases; #psql에선 \list
create database World;
use world; #psql에서 \c world

2. 테이블 생성 & 테이블 리스트 확인

create table Heaven (
	id int not null auto_increment primary key,
    name varchar(10) not null,
    good bool not null default True,
    key idx_name(name)
);
show tables; #psql에서 \dt

3. 테이블 삭제 & 데이터 베이스 삭제

Drop Table Heaven;
Drop Database World;

생성 명령어

1. DB 생성 및 확인

create database 데이터베이스이름;
show databases; #psql에서 \list

2. 테이블 생성(create table 테이블이름(컬럼 자료형 제약))

create table 테이블이름 (
	컬럼 자료형 제약사항,
    [컬럼 자료형 제약사항, ... ]
);
show tables; #psql에서 \dt

3. 인덱스 생성 제거(create(drop) index 인덱스이름 on 테이블(칼럼))
   create index 인덱스이름 on 테이블(컬럼);
drop index 인덱스이름 on 테이블(컬럼);
4. 주로쓰는 자료형
   -INT: 4바이트 정수(-2147483648 ~ 2144483647)
   -BIGINT: 8바이트 정수(-922337036854775808 ~ 922337036854775807)
   -FLOAT: 4바이트 실수(+_1.175494351E-38 ~ +_3.402823466E_38)
   -DOUBLE: 8바이트 실수(+_1.7976931348623157E-308 ~ +_2.2250738585072014E+308)
   -DECIMAL(M, D): M길의의 전체자릿수의, D길이의 소수자리수의 실수(NUMERIC과 같음)
   -VARCHAR(길이): 가변길이 텍스트
   -TEXT: 제한없는 가변길이 텍스트(보통 최대 2gb)
   -CHAR(길이): 고정길이 텍스트(빈공간 공백패딩)
   -DATETIME: 10010101~99991231까지의 날짜 시간(8byte)
   -TIME: -838:59:59~838:59:59 까지의 시간(3byte)
5. 제약조건
   -NOT NULL: 널금지
   -UNIQUE: 중복금지
   -CHECK(칼럼명 비교연산자): 입력값의 조건 검사
   -PRIMARY KEY: 기본키(널, 중복금지)(보통 자동 인덱스가 된다)
   -FOREIGN KEY: 외례키

삭제 명령어

1. DB 삭제(Drop Database DB이름)
   Drop Database World;
2. 테이블 삭제(Drop Table 테이블이름)
   Drop Table Heaven;
3. 테이블 초기화(TRUNCATE TABLE 테이블이름)
   TRUNCATE TABLE Heaven;
4. drop, truncate, delete과 차이
   drop: 테이블, db 완전삭제
   TRUNCATE: 테이블 데이터만 삭제(스키마는 유지)
   delete: 테이블의 특정 데이터만 삭제(DB와 스키마는 유지)

변경 명령어

1. 테이블 변경
   ALTER TABLE 테이블 권한 COLUMN

타입	값
ADD	ADD COLUMN 컬럼명 자료형 제약사항
Drop	Drop COLUMN 컬럼명
MODIFY	MODIFY COLUMN 컬럼명 자료형 제약사항

2. 예:

ALTER TABLE Heaven ADD COLUMN friend  varchar(40) default '';

---

[DML 명령어]

순서

0. 예시 테이블
1. 삽입
   • 여러개 삽입
   • 하나 삽입
   • 특정컬럼에만 삽입
2. 업데이트
   • 일괄 업데이트
   • 조건부 업데이트
3. 삭제
   • 조건부 삭제
   • 전체 삭제
4. 조회
   • 조건 검색
   • 칼럼별 추출
   • 그룹
   • 정렬
   • 조인
5. 성능측정

예시 테이블

create table Heaven (
	id int not null auto_increment primary key,
    name varchar(10) not null,
    good bool not null default True,
    key idx_name(name)
);

삽입( insert into 테이블 value () )

1. 여러개 삽입:
   insert into heaven values (1001, "강강강", 1), (1002, "강강나", 0), (1003, "강강다", 1);
2. 하나 삽입:
   insert into heaven values (1001, "강강강", 1);
3. 특정 컬럼에만 삽입:
   insert into heaven(name) value ("강감찬");
4. 조회 결과

1001	강강강	1
1002	강강나	0
1003	강강다	1
1004	강감찬	1

5. 참고: 테이블에 중복 금지 제약사항이 없는한 중복된 로우가 들어갈수 있다. 중복 금지 제약사항이 있을 때는 동일한 로우를 삽입 할 수 없다(에러 발생함)

업데이트(update 테이블 set 칼럼=값 [,칼럼=값, ...] where 조건문)

1. 전체 업데이트:
   UPDATE heaven SET name = "동명이인"
2. 조건부 업데이트:
   UPDATE heaven SET name = '' WHERE name = '개명';
3. upsert 방법:
   (mysql)INSERT INTO heaven(id, name) VALUES (1001, '개명') ON DUPLICATE KEY UPDATE name = '개명';
   (postgresql)INSERT INTO heaven (id, name) VALUES (1001, '개명') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

삭제(DELETE FROM 테이블 WHERE 조건문)

1. 조건부 삭제:
   DELETE FROM heaven where id =1001;

2. 전체 삭제:
   TRUNCATE TABLE Heaven;

조회(select 칼럼 from 테이블 WHERE 조건문 )

0. 순서: 조인, 조건, 그룹, 그룹 조건, 정렬 순서
1. 조건 검색(where 절):
   Select * from Heaven where name="강강강";
2. 칼럼별 추출(칼럼)
   Select id from Heaven where name="강감찬";
3. 그룹
   select name,count(name) as count from Heaven group by name having count(name)>3;
4. 정렬(Order by 절, having 절)
   desc(오름차순), asc(내림차순)
   Select * from Heaven order by name DESC;
5. 조인
   SELECT 왼쪽테이블.칼럼, 오른쪽테이블.칼럼, ... FROM 왼쪽테이블 [[left(right|full)] outer join | join |] 오른쪽테이블 ON 왼쪽테이블.칼럼=오른쪽테이블.칼럼

• [left(right|full)] outer join: 하나의 테이블에만 있는것도 출력(left, right의 경우, 하나만 있어도 되는 테이블을 결정)
• inner join: on절에서 기준 삼은 칼럼이 겹치는 것만 출력
• cross join: 두 테이블의 각 행을 각각 조인함(두 행의 개수의 곱만큼 나타남)

성능측정(explain select 칼럼 from 테이블 WHERE 조건문 )

1. 사용법: explain analyze Select * from Heaven where id =1004, explain Select * from Heaven where name ="강강강"
2. 보는법
   -explain: 키에 인덱스가 있는지 확인(null 인 경우 풀스캔일 가능성 농후), ROW값이 좋은게 좋음(순회하는 데이터가 적다는 뜻이니까)
   -explain analyze: 인덱스 룩업이 뜨는게 좋음, cost와 actual time이 적은게 좋음
3. select 구문에서만 사용하는게 장려: 다른 구문에서는 제대로 측정 불가능함(select구문으로 치환해서 측정하는걸 추천)

---

[DCL 명령어]

순서

1. mysql postgresql의 일반적 차이
2. 명령어
   • 유저추가(create)
   • 유저삭제(drop)
   • 유저 비밀번호 변경(alter)
   • 권한 부여(grant)
   • 권한 압수(revoke)
   • 설정사항저장(FLUSH PRIVILEGES)

mysql postgresql의 일반적 차이

차이	mysql	postgresql	비교
비밀번호	IDENTIFIED BY	WITH PASSWORD '비밀번호'
아이디	'아이디'@'접근ip'	'아이디'	ip를 포함유무

• DML 커밋(트랜젝션 결과 고정): COMMIT
• DML 롤백(되돌리기): ROLLBACK

명령어

• 유저 추가(만든다고 기존 데이터베이스에 접근 권한이 생기진 않는다)
  (MYSQL) CREATE USER '아이디'@'접근ip' IDENTIFIED BY '비밀번호';
  (POSTGRESQL)  CREATE USER '아이디' WITH PASSWORD '비밀번호';

• 유저 삭제
  (MYSQL) DROP USER '아이디'@'접근ip'
  (POSTGRESQL) DROP USER username;

• 유저 비밀번호 변경
  (MYSQL) ALTER USER '아이디'@'접근ip' IDENTIFIED BY '비밀번호'; (POSTGRESQL) ALTER USER '아이디' with password '비밀번호';

• 권한 부여
  (MYSQL) GRANT 권한 [,권한, ...] PRIVILEGES ON 데이터베이스명.테이블 TO '아이디'@'localhost' IDENTIFIED BY '비밀번호';
  (POSTGRESQL) GRANT 권한 [,권한, ...] PRIVILEGES ON 데이터베이스명.테이블 TO '아이디';

• 권한 압수
  (MYSQL) REVOKE 권한 [,권한, ...] ON 데이터베이스명.테이블 from '아이디'@'접근ip';
  (POSTGRESQL) REVOKE 권한 [,권한, ...] ON imsi_table FROM '아이디';

• 설정사항 저장
  (MYSQL) FLUSH PRIVILEGES;

---