데이터베이스 시험

테이블의 구조

■ DDL (Data Defineition Language) : CREATE, ALTER, DROP, RENAME 

 - 데이터베이스의 구조를 정의하는 언어이다

■ DML (Data Manipulation Language) : INSERT, UPDATE, DELETE, SELECT

 - 테이블에서 데이터를 삽입, 수정, 삭제, 조회한다

■ DCL (Data Control Language) : GRANT, REVOKE 

 - 데이터베이스에서 사용자에게 권한을 부여하거나 회수한다

■ TCL (Transaction Control Language) : COMMIT, ROLLBACK

 - 트랜잭션을 제어하는 명령이다

 

DCL ( Data Control Language )

◆ 데이터를 사용하거나 유저를 관리하는 질의문이다

■ 사용자 계정 생성

CREATE USER [유저이름]@[접근 허용할 아이피] IDENTIFIED BY [비밀번호]

- '%' 는 와일드카드로 모든 문자가 들어가도 안들어가도 상관없다는 문자열이다

 

■ 사용자의 권한을 부여 및 회수

GRANT [권한종류] ON [데이터베이스.테이블] TO [유저이름]@[접근 허용할 아이피]

- 권한을 부여하거나 회수하는 절대적인 권한은 GRANT OPTION 이라고 한다

- 나머지 권한(삽입, 수정, 조회, 삭제) 를 포함한 권한은 ALL PRIVILEGES 이다

 

■ 권한 회수 

REVOKE [권한] ON [데이터베이스.테이블] FROM [유저이름]@[접근 허용할 아이피]

 

■ 비밀번호 변경

SET PASSWORD FOR [사용자] = PASSWORD([패스워드]);

- PASSWORD 함수는 암호화하여 저장해준다

 

■ 삭제

DROP USER [유저이름]@[IP]

---

DDL (Data Defineition Language)

◆ 테이블, 데이터베이스를 정의하는 질의문이다

■ 데이터베이스 생성

CREATE DATABASE [데이터베이스의 이름]

 

■ 목록 조회

SHOW DATABASES;

 

 

■ 사용

USE [데이터베이스 이름]

 

■ 테이블 생성

CREATE TABLE [테이블의 이름] (

    [컬럼의 이름] [데이터 타입](크기) [제약조건]

    ,[컬럼의 이름] [데이터 타입](크기) [제약조건]

    ,[컬럼의 이름] [데이터 타입](크기) [제약조건]

);

 

■ 목록 조회

SHOW TABLES;

 

□ 자주 사용하는 데이터 타입

1) 문자

  ◆ Char : 한번 정해진 데이터 크기가 변하지 않는다(2글자로 선언했으나 1글자로 오면 남은 한자리는 공백 처리된다)

  ◆ VarChar : 정해진 크기보다 적게 들어오면, 알아서 줄여준다

 

2) 숫자 

  ◆ Int, Long, Float, Double

 

3) Boolean 

  ◆ True, False

 

4) 날짜 

  ◆ DATE : 0000-00-00

  ◆ DATETIME : 0000-00-00 00:00:00

  ◆ TIMESTAMP : DATATIME 과 같지만 TIMEZONE에 따라 시간이 변경된다. 각 나라의 맞는 시간대로 변경해준다

 

■ 테이블 구조 확인

DESC [테이블의 이름]

 

■ 테이블 수정

1) 테이블의 컬럼의 이름을 변경

ALTER TABLE [테이블의 이름] RENAME COLUMN [변경전 이름] TO [변경후 이름]

- 변경하게 되면 기존에 테이블에 저장된 데이터는 사라지게 되기 때문에 신중하게 바꿔야한다

 

2) 새로운 컬럼을 추가

ALTER TABLE [테이블의 이름] ADD (
    [컬럼의 이름] [데이터 타입](크기)

);

 

3) 컬럼의 데이터 타입을 변경

ALTER TABLE [테이블의 이름] MODIFY COLUMN [컬럼의 이름] [데이터 타입](크기

- 해당 컬럼이 비어있어야지 변경할 수 있다

 

4) 기존 컬럼을 삭제

ALTER TABLE [테이블의 이름] DROP COLUMN [컬럼의 이름]

---

DML

■ INSERT

INSERT INTO [테이블의 이름](컬럼명,....) VALUES (컬럼명에 넣을 값,...);

- 컬럼명과 그 컬럼에 넣을 값의 순서가 동일해야한다 데이터베이스가 자동으로 알맞게 삽입해주진 않는다

 

■ DELETE

DELETE FROM [테이블의 이름] WHERE [조건]

- 테이블에 모든 데이터를 삭제할 수 있는 위험이 있기 때문에 매번 조심해서 실행해야한다

 

■ UPDATE 

UPDATE [테이블의 이름] SET [컬럼] = [값] WHERE [조건]

- 조건이 없다면 모든 행의 해당 컬럼의 값을 전부 변경해버리는 사고가 일어날 수 있어 조심하자

 

---

SELECT

■ 테이블의 전체 데이터를 조회

SELECT [조회할 컬럼] FROM [테이블명]

 

■ 조회된 결과에서 컬럼명을 임시로 변경해서 보여줄 수 있다

SELECT

    [조회할 컬럼] AS [임시 이름]

FROM [테이블명]

 

■ WHERE

◆ 테이블에서 특정 조건에 만족하는 행들만 조회한다

SELECT [조회할 컬럼] FROM [테이블] WHERE [조건]

 

연산자	설명
BETWEEN AND	조건 범위에 맞는 행들을 조회한다
LIKE	대상 문자열에 검색할 문자열이 포함되어있는지 확인한다
IN	대상 컬럼에서 조건에 맞는 행들을 조회한다 (OR과 동일한 결과이지만 속도는 빠르다)
IS NULL	해당 컬럼에서 값이 비어있는 행들을 조회한다

 

■ GROUP BY

SELECT [컬럼1], [컬럼2], ... FROM [테이블이름] GROUP BY [묶어줄 컬럼]

- 특정 컬럼을 기준으로 묶어서 그룹 단위로 가져와 조회한다

- 묶어진 그룹은 각 그룹의 가장 첫값을 보여준다

 

■ HAVING 

SELECT [컬럼1], [컬럼2], ... FROM [테이블이름] GROUP BY [묶어줄 컬럼] HAVING [묶여진 그룹 단위로 적용할 조건]

- 먼저 그룹단위로 조건에 맞는 행들만 다시 모아서 묶어서 보여준다

 

■ ORDER BY

SELECT * FROM [테이블명] ORDER BY [컬럼명] [ASC|DESC]

- 특정 컬럼을 오름차순, 내림차순으로 정렬하여 조회한다

---

트랜젝션

https://gooddw.tistory.com/17

16일차 - 트랜잭션과 관련된 명령문

---

컬럼의 제약조건

https://gooddw.tistory.com/18

17일차 - 컬럼의 제약조건

---

서브쿼리 

◆ 서브쿼리는 쿼리 안의 쿼리라는 뜻이다 사전에 추출된 내용에서 재 검색하거나, 검색된 내용을 가상의 컬럼을 만들어 추가할 수 있다

필요성

■ 추출된 결과에서 재 검색할시 유용하다

■ 검색된 내용을 가상의 컬럼으로 한눈에 알기 쉽게 확인할 수 있다

□ 서브쿼리가 길어지고, 많아질수록 코드가 복잡해져 어떤 결과를 조회하는지 알기 어려워진다

서브쿼리 종류

■ 단일 행 서브쿼리, 결과는 반드시 한행으로만 조회해야한다 안그러면 오류 발생

■ 다중 행 서브쿼리, IN, ALL 등 으로 여러 개의 행을 조건절을 사용할 수 있다

서브쿼리 위치

■ SELECT 문에서 조회되는 컬럼에 위치하면, 새로운 컬럼으로 조회된다

■ 참조하려는 테이블에 위치하면, 별칭을 붙여주면 테이블처럼 조회되어 조회된다

■ 조건절에서는 단행, 다중행으로 나뉘어 적절하게 조건의 피연산자로 사용할 수 있다

---

 조인과 집합 연산

https://gooddw.tistory.com/22

18일차 - 서브쿼리, 조인, 집합

 

---

INDEX,IN,EXISTS,ANY,ALL,VIEW,AUTO_INCREMENT, LIMIT,OFFSET,FUCTION(MAX,MIN,AVG,SUM)

https://gooddw.tistory.com/24

19일차 - INDEX, IN, EXISTS, VIEW

---

정규화

''모든 정규화는 이전 정규화를 만족해야한다''

제 1 정규화 

◆ 한 칸에 하나의 데이터만 차지해야한다

제 2 정규화 

◆ 현재 테이블의 주제와 관련 없는 컬럼을 다른 테이블로 나눈다

■ 현재 테이블의 핵심주제와 상관없는 컬럼들을 모두 제거해라

■ partial dependency 를 제거한 테이블

composite primary key : 후보키 

제 3 정규화 

◆ 고유키 컬럼이 아닌 컬럼에 종속되는 컬럼이 없도록 해당 컬럼을 다른 테이블로 나누어라

''모든 정규화는 이전 정규화를 만족해야한다''

제 1 정규화 

■ 모든 항목에 값이 없어야 하며, 중복열이 없어야한다

■ 없거나 많을 경우 대응되냐

-> 실무에서는 오히려 NULL 이 들어가야할 수도 있다

전화번호 하나만 들어가면 다른 값이 NULL 이고, 하나를 두개를 중복해서 만들어진

여러가지 상황, 하나 들어가는지 아무것도 안들어가지는지

없을수도 딱 하나 있을때 식별

없을수도 여러개 있을 수 도 있는 경우 비식별

논리적 설계

아기가 태어났을때 태명으로 그 사람의 평생의 이름이 되진 않지만, 물리적 설계전까지 쓰인다

제 2 정규화

개체의 속성이 한 식별자에 종속되어야 한다

-> 한 컬럼에 한 기본키를 사용해야한다, 복합키보다 단일키를 사용해야한다 애지간하면 자제해라

-> 설계는 뭐다 끼워맞추지 말고 유연하게 해라

-> 복합키를 써야하는 상황인가?

1) 기본키를 뭐를 잡을까?

2) 이 기본키가 중복이 생기지 않으려면 어떻게 쪼개야할까?

3) 쪼갰을때 문제는?

제 3 정규화

고객 번호를 변경하면 이름이 변경된다

그에 따라 등급도 변경해야한다 

1115번을 바꿨다 이름도 바뀔 수 있다 여기에 따라서 등급도 할인율도 바꾸어야한다

 

어떻게 쪼개줘야 고객번호와 이름 바꾸면 안바꿔도 등급 할인율을 바꿀까?

오직 속도를 위해서 역 정규화를 진행하기도 한다

테이블 쪼개기는 중복이 생길거 같을때

PK 는 하나만 꼭 집어야하는 경우만 해야한다(인덱스 걸린다)

보기는 새로운 테이블을 만들어야한다고 착각하기

개발하는것까지 생각하면서 해야한다

 

우리는 일반회원의 판매자 회원 테이블을 나눌 것인가

카테고리에서 해당 분류의 물품이 바로 나타나야 한다

판매 품