MY_STUDY

DDL( Data Definition Language ) - 데이터 정의어

컴퓨터 사용자 또는 응용 프로그램 소프트웨어가 컴퓨터의 데이터를 정의하는 컴퓨터 언어 또는 컴퓨터 언어 요소이다.
DDL에서는 이렇듯 데이터 정의를 하게 되는데 여기서 쓰는 데이터 정의는 데이터베이스 객체들을 정의 하는 것이다. 데이터베이스 객체는 테이블, 뷰, 인덱스, 시퀀스, 파티션 테이블 등등이 있다.

종류

CREATE - 새로운 데이터베이스 관계 (테이블), VIEW, 인덱스, 저장 프로시저 만들기
DROP - 이미 존재하는 데이터베이스 관계(테이블), 뷰, 인덱스, 저장 프로시저를 제거한다.
ALTER - 이미 존재하는 데이터베이스 개체에 대한 변경, RENAME의 역할을 한다.
TRUNCATE - 관계 (테이블)에서 데이터를 돌이킬 수 없는 제거

DML (Data Manipulation Lanaguage ) - 데이터 조작어

데이터베이스 사용자 또는 응용 프로그램 소프트웨어가 컴퓨터 데이터베이스에 대해 데이터 검색, 등록, 삭제, 갱신을 위한, 데이터베이스 언어 또는 데이터베이스 언어 요소이다. 간단하게 데이터베이스 테이블에 들어가는 데이터에 대해 여러가지 조작을 하는 것이다.

종류 

SELECT : 데이터를 추출(조회)
INSERT : 데이터를 생성
UPDATE : 데이터를 수정
DELETE : 데이터를 삭제 (테이블 내의 테이터 삭제, 공간 유지) 

DCL (Data Control Lanaguage ) - 데이터 제어어

데이터의 보안, 무결성, 데이터 회복, 병행 수행 제어 등을 정의하는 데 사용하는 언어, 데이터베이스 관리자가 데이터 관리를 목적으로 사용함

종류

GRANT : 사용자를 생성하고 , 사용자에 대한 권한 부여한다.
REVOKE : 사용자를 삭제하고, 사용자에 대한 권한을 회수한다.

CASE 문

모든 명령어에는 조건문이 있다. SQL에서는 IF, CASE문이 그 예인데, 가장 사용을 많이하는 조건문은 CASE문이다.

사용 구문

CASE 
    WHEN [조건절] THEN [TRUE일때값]
    ELSE [FALSE일때값] 
END

사용 예시

STUDENT 테이블에 담긴 데이터이다.

SELECT ID, NAME, SCORE,
    CASE 
        WHEN SCORE >= 90 THEN 'A'
        WHEN SCORE BETWEEN 80 AND 89 THEN 'B'
        WHEN SCORE BETWEEN 70 AND 79 THEN 'C' 
        WHEN SCORE BETWEEN 60 AND 69 THEN 'D'
        ELSE 'F'
    END AS GRADE
FROM STUDENT

GRADE 라는 등급을 나타내기 위해, SCORE 컬럼을 이용한다. WHEN 문에 조건절을 적고, THEN으로 해당 조건이 맞을 경우 반환하는 반환값을 작성한다. ELSE는 위에서 적은 모든 조건문에 해당하는게 없을 경우 반환할 값을 적는다. END로 CASE문의 끝이란 걸 알린다. 그리고 AS로 쓰고 싶은 해당 컬럼의 이름을 작성하면 된다. (GRADE)

쿼리 실행결과, CASE 조건문으로 설정한 대로 반환된 GRADE 컬럼을 볼 수 있다.

JOIN

두개 이상의 테이블들을 연결 또는 결합해서 데이터를 출력

종류별 예시

student 테이블과 patient 테이블이 있다.

LEFT OUTER JOIN

좌측 테이블 데이터에 추가로 우측 정보를 조인하는 문법.

조인 가능한 것은 붙이고, 불가능한 것은 NULL

좌측 테이블인 student 테이블에 추가로 우측 테이블 patient 정보를 조인했다. 좌측 테이블 name의 '정채연' 데이터가

우측 데이터에는 없기 때문에, 우측 테이블에서 불러온 address 컬럼의 정채연 부분에는 NULL

RIGHT OUTER JOIN

단순히 LEFT 조인을 뒤집은 개념이다. 좌측 테이블을 붙이되 조건을 만족하는 데이터만 붙힌다.

우측 테이블 patient 데이터에 좌측 테이블 student를 붙혔는데, 우측 테이블에 없는 좌측 테이블의 조건들은 NULL로 출력

INNER JOIN

내부 조인이라고 하며, 조인 조건에서 동일한 값이 있는 행만 반환한다.

on 조건절을 활용하면 컬럼병이 다르더라도 조인 조건 사용 가능.

말 그대로 동일한 값이 있는 것만 반환된다. INNER JOIN은 생략이 가능하다.

서브쿼리란?

하나의 SQL 문에 포함되어 있는 또 다른 SQL문을 말한다.

특징

1. 괄호로 감싸서 사용

2. 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능

3. 서브쿼리에서는 ORDER BY를 사용하지 못한다.

예시

테이블 하나를 만들고 전체 조회하였다.

서브쿼리를 이용, 송중기와 직업이 같은 데이터들을 조회하였다.

송중기와 나이가 같은 데이터들을 조회하였다.

비교 연산자를 이용해 송중기 보다 나이가 많은 데이터를 조회하였다.

LIMIT

'한계', '한도'라는 뜻으로, select문에서 이 키워드를 사용하면 데이터 조회 시 한계를 지정할 수 있다.

먼저 Product 테이블을 만든 후, insert문으로 아래 데이터들을 삽입

조회결과.

select문으로 전체 조회 쿼리 작성 후, 뒤에 limit 0,1; 쿼리를 추가한다.

0번째 인덱스부터, 1개라는 뜻으로, 0번째 인덱스 하나만 조회된다.

이번엔, '0,1' 이 아닌 '3,3'으로 3번째 인덱스부터 3개라는 뜻

말 그대로 3번째 인덱스인 '웰치스 포도맛' 부터 시작해서 3개를 조회한다.

LIKE

원하는 검색 결과를 얻기 위해서 사용한다.

포도라는 단어를 검색하고 싶다면, select로 전체 조회문을 작성하고 그 뒤에 'where 컬럼명 like %검색어%' 추가

포도를 검색하고 싶다면 '%포도%' 이런식으로 like문을 활용한다. 조회 결과 '포도'가 포함된 데이터들이 조회되었다.

위의 LIMIT와 통합해서 사용할 수 있다. LIKE로 '포도' 검색 후, 그 검색한 결과에서 0번째 인덱스부터 3개를 조회한다.

no라는 정수형 컬럼에 auto_increment 설정후 테이블에 추가하였다. 그리고 no의 내림차순 정렬과 동시에 LIKE와 LIMIT를 활용, 그럼 '포도' 검색 결과 내림차순으로부터 0번째 인덱스에서 3개가 조회된다.

 auto_increment

게시판을 이용할 때, 글을 작성하면, 작성하지도 않았음에도, 글 제목 옆에, 몇번째 글인지 나타나는 경우가 종종 있다. 또한 스키마를 작성하다보면 데이터의 수를 PK로 설정해야 하는 때가 있다. auto_increment문을 활용하여, 정수형 넘버를 자동으로 증가하도록 해보자.

예시

board(게시판) 테이블을 만든다. no라는 int형 필드를 하나 만들고, null 값이 없도록하며, 자동증가 auto_increment와 primary key(기본키)를 설정한다.

컬럼 조회시, 이렇게 나타난다.

insert문으로 데이터를 집어넣는다. 하지만 auto_increment를 설정했던 no 컬럼의 데이터는 따로 작성하지 않는다.

조회 결과... no 컬럼의 데이터는 따로 작성하지 않았음에도, 1,2,3, 이렇게 증가하면서 조회되는걸 확인할 수 있다.

MySQL WorkBench란?

SQL 개발과 관리, 데이터베이스 설계, 생성 그리고 유지를 위한 단일 개발 통합 환경을 제공하는 비주얼 데이터베이스 설계 도구이다.

사용법

워크벤치를 켰을 시, 왼쪽 사이드에 이게 있는데, 맨 밑의 Schemas를 누르면

그동안 만들었던 데이터베이스들이 들어있다.

그 중 practice 데이터베이스를 선택해, univ_student 테이블에 커서를 올려보면, 아이콘들이 뜬다.

맨 오른쪽 아이콘을 누르면, SELECT 쿼리와 함께, 데이터가 조회되는 것을 확인할 수 있다.

가운데 아이콘을 누르면, 컬럼 목록들을 확인할 수 있고, 수정도 가능하다. id 컬럼이 PK로 설정되어 있었다.

쿼리에 insert into로 원하는 값들을 넣을 수 있다. 쿼리문을 작성 후, 번개 모양의 아이콘을 눌러 실행할 수 있는데,

왼쪽의 번개 모양은 전체 쿼리를 실행하는 것이고, 오른쪽 번개 아이콘은 해당 커서부분의 쿼리만 실행하는 것이다.

insert문 실행 후 select로 조회를 하기 위해, 전체 쿼리를 실행하기로 했다.

조회 결과 값이 추가된 걸 확인할 수 있다.

굳이 쿼리문을 작성하지 않고, 조회된 표에서 직접 값을 작성하는 방법도 가능하다.

직접 작성 후, 밑의 apply를 눌러야만 값이 추가된다.

그러나 이 방법은 한 컬럼이 PK로 설정이 되어 있어야지만 가능하다.

apply후 다시 조회를 해보면 작성한 값이 그대로 조회되는 것을 확인 가능하다.

Primary Key(기본키)

후보키 중에서 선정된 키, 중복된 값을 가질 수 없고, NULL 값을 가질 수 없다. 학번같은 중복 데이터가 존재하면 안되는 곳에 주로 사용된다.

Foreign Key(외래키)

두 테이블을 연결하는 키, 다른 테이블의 기본키를 참조하는 키

예시

먼저 univ_student 테이블을 만들고, id(학번)을 기본키로 지정한다.

insert로 값을 입력할 시, 중복된 id를 등록하려고 하면, 오류가 뜬다. 기본키는 중복된 값을 가질 수 없다.

univ_student 테이블 완성

그리고 univ_camp라는 테이블을 만든 후, camp_id를 univ_student 테이블의 기본키인 id의 외래키로 지정

univ_student 테이블의 송중기의 학번을 camp_id의 학번으로 등록시킨다.

부모 테이블인 univ_student의 송중기 학생을 삭제해본다. 그러자 오류가 난다. 부모행을 삭제나 수정할 수 없다.

univ_camp에 송중기 학생의 학번이 등록되었고, 이 외래키 때문에 걸린 제약으로 부모테이블의 송중기 학생을 삭제불가

이게 만약 삭제가 된다면, 자식 테이블인 univ_camp에는 univ_student에 있지도 않은 학생이 캠프에 참가하는게 된다.

그럼 아무 의미없는 데이터가 남게되는 것이다.

add)

기본값이 0인 정수형 english 컬럼 추가

그 결과 english 컬럼이 추가되었다.

값을 집어넣었을 경우

change)

english 컬럼의 이름을 TOEIC으로 변경

컬럼의 이름이 변경된걸 확인할 수 있음

rename)

person1이라는 테이블의 이름을 toeic_student라는 이름으로 변경

테이블 이름이 변경되었다.

CRUD란?

대부분의 컴퓨터 소프트웨어가 가지는 기본적인 데이터 처리 기능

CREATE : 생성 (INSERT INTO ~ )

READ     : 조회 (SELECT ~ )

UPDATE : 수정 (UPDATE ~ )

DELETE : 삭제 (DELETE ~ )

SQL 기본문법

나는 MySQL 8.0 Command Line Client 프로그램을 사용하였다.

우선 show databasese; 명령어로 모든 데이터베이스를 조회하였다.

그 후  use practice;라는 명령어로 practice 데이터베이스를 이용하겠다고 하였고,

show tables;라는 명령어로 practice 데이터베이스 안의 모든 테이블들을 조회했다.

person이라는 테이블이 이미 있는 관계로 person1이라는 새로운 테이블을 만들기로 하였다.

name 컬럼은 10 바이트의 가변 문자열로 설정하였고, age는 정수, height는 실수로 설정

테이블 만들기가 완료되었다면, insert문을 이용하여 데이터를 삽입한다.

person1 테이블의 name, age, height 컬럼에 values 데이터를 추가한다.

CRUD 중의 CREATE에 해당된다.

이제 SELECT문으로 테이블의 데이터들을 조회한다. 모든 데이터를 조회하기 위해서는

select * from 테이블명; 이라는 명령문을 사용한다.

특정 컬럼만을 출력하길 바란다면, select 컬럼명 from 테이블명; 으로 명령문을 적는다.

age가 20인 사람만을 조회하고 싶다면, 뒤에 where문을 이용하여 조회할 수 있다.

CRUD 중 READ에 해당된다.

update문을 이용하여 특정 데이터를 수정할 수 있다. 

테이블 person1에서 name이 홍길동인 데이터의 age를 30으로 수정하였다.

select문을 확인한 결과 age가 20이었던 홍길동의 age가 30으로 수정되어 있는걸 확인할 수 있다.

CRUD 중 UPDATE에 해당된다.

특정 데이터를 삭제하고 싶다? 그럼 delete 문을 이용한다. 

name이 김민수인 테이블 person1의 튜플을 삭제

selete문으로 확인 결과 김민수 데이터가 삭제된 것을 확인할 수 있다.

CRUD 중 DELETE에 해당된다.