25.10.23 11일차 [ SQL 기초 개념 강의_{DDL(CREATE, ALTER, INDEX,VIEW) DML(INSERT,UPDATE,DELETE,TRUNCATE,DROP)DCL

오늘은 DDL, DML, DCL에 대한 수업을 진행하였다.

• DDL
  • create
  • alter
  • index
  • view
• DML
  • insert
  • update
  • delete
  • truncate
  • drop
• DCL

---

DDL

• Data Definition Language
• 데이터 정의어
  • SQL
    • create
    • alter
    • index
    • drop
    • view
• 특징
  • 트렌젝션 발생 x
  • 명령어가 즉시 수행된다.

• create
  • create database
    • 새로운 데이터베이스 생성
    • create database 데이터베이스명 [인코딩, ... : 최초 mysql 설치 시 기본 설정한 값을 따옴]

데이터베이스 란에 데이터베이스 A가 추가된 것을 볼 수 있다.

 

• create table as select
  • 특정 테이블을 조회하여, 그것과 동일한 테이블 (테이블 속성, 구조 카피)를 생성한다.
  • 데이터도 카피한다,

create table as select 문법을 이용하여 t1 데이터베이스에 있는 테이블 city를 a 데이터베이스에 city_copy라는 이름의 테이블로 복사해서 가져오는 쿼리이다.

 

SELECT 결과셋에서 국가코드, 이름, 인구수만 골라서 복사해 오는 쿼리이다. 위의 쿼리와 비교해보길

 

 

• CREATE TABLE
  • 테이블 생성
  • 특정 주제, 구조를 가진 데이터를 하나의 단위로 관리하기 위한 스키마( 혹은 구조 ) , 단위
  • 정형데이터로 구성 -> 사람 손을 탔기 때문에 날것이 아니다.

-- 회원테이블
create table users (  -- 컬럼명 | 타입 | null 가능 불가능 여부 | 인덱스 |  
id int not null, 
uid varchar(32) not null,   <- 유저의 아이디, 문자열, 32자릿수 제한 
upw varchar(128) not null,  <- 유저의 비밀번호, 문자열, 32자릿수 제한 
email varchar(64) null, 
regdate timestamp not null <- 탈퇴날짜
);

위의 텍스트 쿼리

USERS 테이블을 생성하고 그 안에 테이터를 기입한 내용이다, GUI기반 프로그램에서는 클릭으로도 쉽게 구현 가능

 

테이블 상단 탭의 CREATE코드는 이렇게 나온다.

 

• ALTER TABLE
  • 테이블 수정
  • 컬럼 추가 | 수정 | 삭제
  • 기본형 중심 체크

기존 USERS 테이블의 데이터, 61번 쿼리를 실행한 결과다.

ALTER를 이용해서 컬럼 COL이 추가된 것을 볼 수 있다.

수정할 부분만 기입하면 이렇게 수정할 수 있다, 기존 수치형 데이터에서 문자열로 바뀐 모습

 

컬럼 삭제를 이용해서 6번 COL 컬럼을 삭제한 모습이다.

 

• INDEX
  • 목적 
    • 빠른 검색을 통해 데이터를 찾기 위함
  • 특징 
    • 사용한다면
      • 장점
        • 검색 성능 향상
          • 테이블 전체를 읽지 않아도 된다. (구현하기 나름이다)
          • 인덱스가 없다면 데이터가 많을 시 탐색 시간도 늘어난다,
        • 정렬, 그룹화 시간 향상
        • ...
      • 단점
        • 저장공간 사용 ( 소모 )
          • 점점 커짐
        • 데이터가 갱신되면 성능 저하
          • 데이터 업데이트 , 수정, 삭제, 추가,...자꾸 발생하면
            • 업데이트가 빈번하게 발생
          • 관리 복잡 (선택적)
    • 종류
      • B-Tree 인덱스 ( 가장 많이 사용함 )
        • ex : 주문 날짜, 사용자 아이디 + 비번 , 전화번호 검색 등등
        • 범위 쿼리, 정렬된 데이터에 효과적 반응
        • 데이터에 따라서 성능 저하가 올 수 있음
      • Hash 인덱스
        • 정확하게 일치하면 검색이 필요할 때
        • 사용자 아이디, 전화번호 검색
        • 범위 쿼리, 정렬이 필요한 경우에는 적합하지 않다.
      • Full Text 인덱스
        • 텍스트 검색 효과적, 일부 제품에서만 지원
        • 문서 내 키워드 단어 등등 검색 시 사용
      • R-Tree 인덱스
        • 지리정보시스템 ( GIS ) 에서 공간 데이터 검색 시 효과적
        • 일반 데이터에서는 부적

• create index

A 데이터베이스에 있는 모든 인덱스 보여줘라!

인덱스 uid_idx를 추가하여 추가된 모습, 이미 실습때 해서 지난 사진들에도 출력이 되긴 한다.기본값은 BTREE 형식

 

• create unique index
  • 중복값 허용 x 인덱스

unique index가 추가된 모습이다.

일반 인덱스와 유니크 index의 차이는 테이블탭의 인덱스 칸을 보면 키의 색으로 알 수 있다. 유니크 인덱스는 한개의 값만 인정하고 같은 값이 들어오면 오류가 난다

고유한 값을 가진 여러개의 컬럼을 가진 인덱스는 ( ) 를 이용해서 만들 수 있다, 예시는 아이디와 비밀번호가 결합된 인덱스

.

uid와 upw 둘 모두 일치해야 검색이 완료된다. 1개라도 틀리면 안된다

alter 명령을 이용해서 인덱스를 삭제할 수 있다. 삭제된 모습

텍스트 내 빠른 결과를 추출하는 fulltext 인덱스를 만드는 쿼리이다, 회원가입 시 아이디 중복검사 기능 등에 사용한다,

 

 

• view
  • 특징
    • 데이터베이스에 존재하는 가상테이블
    • 실제 테이블처럼 행과 열을 가지고 있다.
    • 실제로 데이터를 저장 X 
    • 뷰어 역할을 담당한다. (조회용)
      • 생성된 이후로는 수정 불가
      • 조인등 복잡하게 구성하여 만든다면 -> 쿼리문이 단순해지는 효과를 볼 수 있다.
      • 인덱스 x
      • 데이터마트 등 특수 목적을 위해서 임시로 구성하여 사용 가능
• create view
  • 뷰 생성

1. create view를 사용해서 city_view를 새로 만들었다. 2. 이후 city_view를 출력한 결과값

데이터베이스 탭에 city_view가 추가된것을 볼 수 있다,

 

전체 탭으로 본다면 이런 모습, 이름과 인구수만 담은 가상 테이블이 만들어진다.

 

실습 문제

1. city, country, countrylanguage 세 테이블을 조인한다.
2. where문으로 한국 코드, 한국 언어 사용하는 나라만 출력한다
3. 결과셋이 도시명, 면적, 인구수, 랭귀지로 나오게 세팅한다
4. 모든 결과를 담은 가상테이블을 만든다.

내 답

좌측 탭에 total_kor_language view가 추가되고 원하는 출력값이 나오는것을 볼 수 있다. 이때 국가면적은 다 같으니 필요 없다는걸 판단하는것도 중요하다

강사님 답

과정을 바꿔서 더 연산이 빠르게 수정하셨다.
1. from 에서 한국만 추출하고 a로 별칭을 부여한다
2. city 테이블을 b로 별칭 부여해 a = b, country 테이블을 c 로 별칭 부여해 a = c 조인
3. 나머지는 같다, 이때 연산이 더 빠른 이유는 애초에 조인하기 전부터 한국만 추출하여 적은 양의 데이터를 분석해서이다.

view를 이용하는 경우는 필요한 데이터만 뽑아서 한번에 분석이나 전처리등에 용이해서이다.

 

alter view

기존 city_view 데이터

여기서 alter를 이용해서 name을 code로 바꿔준다면?

1번 컬럼의 name 칸이 code로 변하는 것을 볼 수 있다.

 

drop view

drop view 이후 city_view가 사라진 모습

 

DML

 

• Data Manipulation Language
• 데이터 조작어 -> 이거 할때면 직급 높을거임
• sql
  • insert
    • 데이터 추가
  • update
    • 데이터 수정
  • delete
    • 데이터 삭제
    • 조건을 부여하여 삭제
    • 커밋 전 복구 가능
  • truncate
    • 테이블의 구조는 두고 데이터 삭제
  • drop
    • 테이블 삭제

 

• insert into , insert into ~ select

사전 준비

1. 이것저것 넣은 테이블 users 삭제 후 다시 만들었다, 이때 기존과 달라진 점은
2. primary key = 해당 데이터를 대표하는 고유값, 통상적으로 따로 수치입력하지 않아도 자동으로 증가됨
3. regdate에 기본값으로 현재 시간을 부여함.

둘이 같은 기능이다

먼저 위의 쿼리를 실행하지 않고 users 테이블의 모든 정보를 출력한 결과셋이다. 우리가 만든 테이블대로 컬럼이 잘 들어가있으며 데이터는 하나도 없는것을 확인할 수 있다,
이 때 32~35 쿼리를 실행하면

입력한 데이터가 그대로 반영된것을 확인할 수 있다. 1. id와 6. regdate는 입력하지 않아도 기본값으로 나온것이다.
이 정보는 고유값, 나이, 유저 아이디, 유저 비밀번호, 유저 이메일, 가입시간 이다

그런데 우리가 사이트에 가입할 때에면 아이디, 비밀번호, 이메일 등이 다른사람과 같으면 안된다
그 기능을 추가해야 하는데

이 떄 사용하는것이 unique index이다.

인덱스 값을 수정하지 않으면 같은 아이디 같은 비밀번호도 가입이 된다

테이블 탭에서 하단의 컬럼에 unique로 새 인덱스를 생성해준다

이메일 컬럼이 유니크로 수정된걸 볼 수 있다.

아이디와 비밀번호는 매칭되어 같이 움직여야한다, 그렇다면 shift 또는 ctrl로 같이 잡고 유니크를 부여하면

같이 붙어서 한개의 인덱스로 만들어진걸 확인할 수 있다,

이제 같은 값을 가진 데이터를 추가하려 하면 

Duplicate 표시가 뜨는데 겹친다는 뜻이다

같은 데이터를 걸러주어서 추가되지 않는 모습이다.

그런데 한번 오류가 나고, 아이디와 비밀번호 이메일을 바꿔서 데이터를 추가했는데 id가 2가 아닌 3이다.
그 이유는 id는 오류가 나서 reject되어도 값이 자동부여되어 올라가기 때문이다.

여러개의 데이터를 한번에 삽입하려면 값을 ( ) 로 묶어서 나열하면 된다.

 

• insert into ~ select

특정 테이블에 특정 데이터를 타겟 테이블에 일괄 입력
ex : id가 3 이하인 데이터만 출력

현재 users2 데이터값

이거 꼬였다. 추후복습

• update
  • 기존 데이터 업데이트 ( 수정 )
  • 권장 ( where를 반드시 사용 -> 대상만 반영되게 처리)

쿼리를 실행하기 전의 결과셋이다. 위의 쿼리를 실행한다면

id = 4인 데이터의 나이가 + 3 된 것을 볼 수 있다.

 

• delete
  • 데이터 삭제
  • 원본에 대한 수정이 있어서 조건식 같이 병행하길 권장 (없다면 전체 삭제)
  • 단, 복구 (혹은 백업) 은 가능함

이전 데이터셋에서 나이가 25살 이상인 데이터를 삭제하는 쿼리이다.

delete는 반드시 where과 함께 써야한다

나이가 28살이던 4번행이 삭제되는것을 확인, 28살 삭제라니 너무해 ㅠㅠ

 

 

• truncate
  • 테이블에 데이터를 모두 삭제 (인덱스도 삭제된다고 아는데 삭제가 되지 않아 강사님이 확인 후 공지)
  • 복구 불가 기본 ( 하지만 로그를 통해서 복구는 가능, 근데 쉽지 않음)
  • DB가 점유하고 있는 물리적 공간 복구

truncate 돌리니까 싹 없어졌다,

 

• drop
  • 테이블, 데이터, 공간 등 모두 완전히 삭제
  • 되돌릴 수 없다, 단 (로그를 이용해서 복구는 가능)

1번 쿼리 실행 전 : A 데이터베이스에 users2 테이블이 있다.

 

1번 쿼리 실행 후 : users2 테이블이 없어졌다,

2번 쿼리 실행 후 데이터베이스 A가 없어졌다.

SHOW DATABASES를 해봐도 보이지 않는것을 확인

 

• 제약 조건
  • 기본키, 참조키
  • 참조키를 이용하여 2개의 테이블을 연결할 수 있다.
    • 1 : N ( 일대 다)
      • 1개의 국가 안에 N개의 도시가 존재
      • 1개의 글 안에 N개의 덧글이 존재
      • 1개의 가게에 N개의 리뷰가 존재
  • 참조의 대상이 되는 본 글(본 데이터)가 삭제되면 연결된 모든 데이터도 (리뷰, 댓글, 도시) 같이 삭제됨
  • (설정하기 나름이긴 함)

간단한 도식화

참조 관계 테스트
1. country2 , city2 각각 테이블을 한개씩 만든다.

t1 데이터베이스에 city2, country2 테이블이 추가된 것을 볼 수 있다.

컬럼은 도시 id, 도시명,인구수만 출력하였다.
여기서 밑의 쿼리의 CASCADE가 참조하는 테이블이 죽으면 운명을 함께하겠다는 선언이다.

2. 더미 데이터를 삽입한다.

country2 테이블에 더미 데이터가 들어갔다.

3. city2에 country2의 국가id를 참조하는 데이터를 직접 입력해준다

1. country2.country_id 가 2인 나라는 일본인데 이를 참조해서 도쿄, 오사카 도시데이터에 country_id 2를 부여한 것이다.

4. country2에서 country_id가 2인 데이터를 삭제하였다.
그런데 city2를 출력해보니 city2에서 country_id가 2인 데이터 또한 함께 삭제된것이다, 
이것이 제약조건이다 생각하면 된다.

country_id가 2인 데이터가 삭제되어 추출되는 모습

 

DCL

• Data Control Language
• 데이터 제어/ 접근 제어관리어
• 특정 계정에게 권한 부여, 제거 역할
  • SQL
    • GRANT
    • REVOKE
• 계정 관리
  • 계정 생성, 확인, 삭제

DCL은 금요일에 마무리

---

오늘은 실습이 많다기보단 이것저것 데이터의 구조적으로 조금 더 수업이 나간거 같아서 평소보다는 양이 적다고 느껴졌다, 리뷰가 리뷰시간에 끝난게 얼마나 오랜지 모르겠다

 

근데 리뷰시간에 다 못할거같아서 점심시간에도 하긴 했다 

 

어제는 나름 일찍 잔 편인데도 아침에도 10분정도 늦게 일어나고 도착해서도 너무 졸려서 쉬는시간에 바로 자버렸다.

 

점심시간에 너무 피곤해서 잘까 오전수업 들은 내용 리뷰를 지금 할까 고민하다가 이겨내고 점심시간에 복습을 했다.

 

하고나니까 아주 뿌듯하고 후회되지 않는다, 어차피 잠은 죽어서 자면 되니까

 

근데 너무 졸려서 10분 잤다. 쪽잠이 세상에서 최고인거같다, 10분만 자도 머리가 아주 개운해지니

 

회사 다닐때도 낮잠자고 싶다