27일차 수업 정리(MySQL)

**데이터 저장

1. 임시 저장 : 프로그램 실행 중에 사용하기 위하여 저장

    => MainMemory(주기억장치 - RAM, Cache memory)에 저장

    => Variable(변수)나 Constant(상수)에 데이터 저장

    => 프로그램을 종료하거나 자신의 영역을 벗어나면 자동으로 소멸

 

2. 반영구적 저장 : 프로그램을 종료하고 다음 실행시에도 사용하기 위해서 저장

    => 컴퓨터에 파일로 저장(보조기억장치 Accessory Memory(디스크등)에 저장)

  1) Local과 Remote 저장

    => Local은 현재 컴퓨터의 디스크에 저장

    => Remote는 다른 컴퓨터의 디스크에 저장

        - 장점 : 여러 컴퓨터에서 동시에 사용 가능, 장애 발생시 복원 가능

        - 단점 : 읽는 속도가 느려짐

  2)Flat File & Database & 분산 파일 시스템

    => Flat File : 일반 파일에 저장

        - 비용은 감소하지만 읽고 쓰기에 불편함

    => Database : Database Management System을 이용

        - 비용은 증가하지만 읽고 쓰기에 편리

    => 분산파일 시스템 : Database의 확장성이나 실행 속도의 문제를 개선한 시스템(Hadoop이 대표적)

  3) Database

    => RDBMS(관계형 데이터베이스)

        - 테이블을 기반으로 한 데이터베이스, 전통적 데이터베이스(SQL 언어 사용)

        - 데이터 베이스 작업에는 SQL을 사용하는데, 데이터베이스 별로 SQL언어가 다름

           (SQL을 이용하지 않고 데이터테이스를 사용하는 Framework - ORM, 대표 ORM이 자바에서는 Hibernate, JPA)

        - 한번 설계되면 수정하기가 쉽지 않음

 

    상용화 : Oracle, IBM의 DB2, SAP의 HANA DB, MS의 SQL Server, Tibero, Access등

    비 상용화 : MySQL(Maria DB), PostgreSQL, SQLite(Local에서만 사용 가능)등

 

    => NoSQL(Not Only SQL)

        - 테이블 구조 대신에 Key-Value(Map)나 Document 기반의 데이터 구조를 이용하는 데이터베이스

        - SQL 대신에 자바스크립트 함수 형태로 데이터베이스 작업을 수행

        - 구조변경에 용이하고, 자바스크립트 문법으로 동작하기 때문에 웹 FrontEnd의 언어인 자바스크립트만 알면됨

        - *Monogo DB, Cassandra, HBase, Firebase의 *Realtime Database(구글 - 모바일에서 많이 사용)등

 

**MySQL 개요

    => SQL에 기반한 관계형 데이터베이스(RDBMS - 테이블 기반)

    => Community 버전(무료)과 Standard, Enterprise, Cluster CGE 버전이 있음

1. 다운로드

    => Oracle 사이트에서 가능

    => https://dev.mysql.com/downloads/mysql

    => 최신버전은 8, 이전버전은 5로 시작

    => 아직 서비스되고 있는 버전은 대부분 5버전

2. 설치

    => Windows에서는 비밀번호만 잘 입력하면 설치가능

        (Windows에서 설치시 닷넷 프레임워크 4.0이나 Visual C++ 2013(14.0)을 설치하라는 메시지가 출력될수 있음)

    => java와 닷넷(MS Windows 프로그래밍)에서는 java나 닷넷 프레임워크 미설치시 프로그램 사용 불가

        - Window에서 C언어로 만들어진 프로그램은 C++ 재배포 패키지가 없으면 설치 불가

    => mac에서는 관리자 비밀번호를 설치할때 스스로 설정하기 때문에 관리자 비밀번호를 메모해두었다가 변경

        - MySQL이 실행된 상태가 아니므로 환경설정에서 서비스를 시작하고 비밀번호를 변경

 

JRE(Hava Runtime Environment)(= .Net Framework) - Java 프로그램을 실행하기 위해

JDK(Java Developer Kit)(= Visual Studio) - 명령어 + JRE

 

3. DBeaver에서 접속

    => MySQL의 관리자 계정은 root

    => HOST : 접속할 서버의 이름

        - 자기 컴퓨터 : localhost 또는 127.0.0.1

        - 선생님 : 192.168.0.200

    => Port : MySQL은 기본적으로 3306

    => Database : mysql

    => username : root

    => password : 900826

 

4. 데이터베이스 작업

    => MySQL은 데이터베이스 단위로 작업

  1) 만들어진 데이터베이스 확인

    => 형식 : show datebases;

    => 처음 만들어진 데이터베이스 중에서 world를 제외하고는 시스템 데이터베이스

    => 시스템 데이터베이스는 조회만 하는 것이지 실제 작업을 하지 않는 것을 권장

  2) 데이터베이스 생성

    => 형식 : create datebase 데이터베이스명;

  3) 데이터베이스 사용

    => 형식 : use 데이터베이스명;

  4) 데이터베이스 삭제

    => drop database 데이터베이스명;

  5) SQL을 사용할 때 첫번째 명령은 대부분 use 데이터베이스명;

 

**테이블 작업

1. 데이터 베이스에 존재하는 모든 테이블을 확인

    => 형식 : show tables; 

2. 테이블 구조 확인

    => 형식 : desc 테이블명;

 

3. DDL(Data Definition Language) : 데이터베이스 개체의 구조에 관련된 명령어

  1) CREATE : 생성

  2) ALTER : 구조 변경

  3) DROP : 삭제

  4) TRUNCATE : 테이블의 데이터를 삭제

  5) RENAME : 테이블의 이름을 변경

    => 데이터베이스 이론에서는 CREATE, ALTER, DROP만 DDL로 취급

 

4. 테이블 생성

    => 형식 : CREATE TABLE 테이블명(

                    컬럼명 자료형 컬럼제약조건,

                    컬럼명 자료형 컬럼제약조건,...

                    테이블 제약 조건)ENGINE = 엔진 종류 AUTO_INCREMENT = 시작값 DEFAULT CHARSET = 인코딩 방식

    => ENGINE 종류에 따라 트랜잭션이 동작하지 않을수 있음

    => CHARSET을 설정하지 않으면 한글 입력이 안됨

        - MYSQL은 기본 인코딩이 ISO-8859-1(서유럽 인코딩 -IOS-LATIN1)

 

5. 자료형

  1) 숫자 : INT(INTEGER), FLOAT, DOUBLE

  2) 문자열 : CHAR, VARCHAR, TEXT

    => CHAR는 크기가 변하지 않는 문자열로 255까지만 설정 가능

    => VARCHAR는 크기가 변하는 문자열로 65535까지 설정 가능

    => TEXT(65535)는 긴 문자열 저장에 사용하는데, LONGTEXT(42억)도 있음

    => 한글 1글자는 3자로 간주

  3) BOOL : TRUE, FALSE 저장

  4) 날짜 : DATE, DATETIME, TIMESTAMP, TIME, YEAR

    => DATE : 날짜

    => DATETIME : 날짜와 시간

    => TIMESTAMP : 날짜와 자세한 시간(날짜와 시간을 같이 저장시 많이 사용)

    => TIME : 시간, YEAR : 년도 (잘 안쓰임)

  5) 파일의 내용 저장 : BLOB(65536바이트 - 64KB), LONGBLOB(42억바이트 - 4G)

 

6. 제약조건(Constraint)

    =>테이블에 잘못된 데이터가 저장되지 않도록 하기위한 유효성 검사 조건

  1) Not null : 필수 입력, 행레벨 제약 조건으로만 설정 가능

  2) Unique : 중복될 수 없음, null은 가능

  3) Primary Key : Not Null이고, Unique

    => 테이블에서 1개만 설정 가능

    => 여러개의 컬럼으로 만드는 것은 가능

  4) Foreign Key : 다른 테이블의 데이터를 참조하기 위해 설정

    => 어떤 테이블의 어떤 컬럼을 연결할 것인지 명시

    => 상대방 테이블에서는 Unique거나 Primary Key여야 함

    => 데이터베이스 이론에서는 반드시 Primary Key여야 함

  5) Check : 값의 범위나 종류를 제한

  6) Defalut : 기본값 설정시 사용

  7) Auto_increment : 자동증가하는 값을 만들 때 사용하고 숫자 자료형에만 사용 가능

                           (Primary Key나 Unique와 함께 사용)

    - 데이터 삽입시 이 컬럼의 값을 입력할 필요가 없음

  8) 설정방법

CREATE TABLE 테이블명(

                     컬럼명 자료형 [CONSTRAINT 제약조건명] 제약조건,

                     .....

                     [CONSTRAINT 제약조건명] 제약조건(컬럼명 나열)

)

    => NOT NULL은 컬럼 제약 조건으로 설정

    => 외래키의 제약조건 명은 foreign key 해야하고, references 상대방 테이블 명(컬럼명) 옵션

    => 외래키 옵션은 on delete cascade, on delete set null

 

7. ENGINE

  1) MyISAM(Indexed Sequential Access Media)

    => 데이터를 빠르게 조회하기 위한 목적으로 사용하는 엔진

    => 트랜잭션 처리가 안됨

  2) InnoDB

    => 트랜잭션 처리를 위한 엔진

    => 삽입 삭제가 빈번히 발생하는 경우에는 InnoDB, 조회가 많은 경우에는 MyISAM을 사용

 

8. AUTO_INCREMENT

    => 일련번호의 개념으로 테이블 제작시 초기값 설정이 가능

    => 초기값을 변경하려면 ALTER TABLE 테이블명 AUTO_INCREMENT = 시작값

 

9. 테이블 생성 실습

 - 테이블 명 : Monster

일련번호 : 정수, 기본키로 설정

이름 : 한글포함 10자이내, 변하지 않는 문자열, 필수 입력

레벨은 : 정수, 필수 입력

데미지 :  정수, 필수입력

크리티컬 확률 : 실수, 필수 입력

경험치 : 정수, 필수입력

출현 필드 : 영문 30자이내, 변하는 문자열

계열 : 영문 1자이내, A,B,C,D중 하나만 가능

 

일련번호는 1부터 시작, 삽입, 삭제는 빈번히 발생

 

10. 기존 테이블에 대한 컬럼 작업

  1) 컬럼 추가

    => 형식 : alter table 테이블명 add 컬럼명 자료형[first 또는 after 다른 컬럼명];

  2) 컬럼 삭제

    => 형식 : alter table 테이블명 drop 컬럼명;

  3) 컬럼 변경

    - 이름과 자료형 변경

    => 형식 : alter table 테이블명 change 이전컬럼명 새로운컬럼명 자료형;

    - 자료형 변경

    => 형식 : alter table 테이블명 modify 컬럼명 자료형

  4) 제약조건 추가

    => 형식 : alter table 테이블명 add [constraint 제약조건명] 제약조건(컬럼 명);

  5) 제약조건 수정

    => 형식 : alter table 테이블명 modify 컬럼 명 자료형 [constraint 제약조건명] 제약조건;

  6) 제약조건 삭제

    => 형식 : alter table 테이블명 drop constraint 제약조건명

 

11. 테이블 삭제

    => 형식 : drop table 테이블 명;

    => 타 테이블에서 이 테이블의 컬럼을 외래키로 참조시 에러가 발생할 수 있음

    => 이 경우에는 자식 테이블을 삭제, 외래키 제약조건 삭제후 수행

 

12. 테이블의 데이터만 삭제

    => 형식 : truncate table 테이블명;

    => delete from 테이블명과 다른 점은 rollback이 안되는 점

 

13. 테이블 이름 변경

    => 형식 : alter table 이전테이블명 rename 새테이블명

 

14. truncate와 rename은 표준 SQL이 아님

    - add나 drop은 거의 모든 데이터베이스가 하는 방법이 유사, modify나 change는 데이터 베이스 종류별로 다름

 

**DML(데이터 조작 언어)

    => DQL(SELECT)과 DML(INSERT, UPDATE, DELETE)로 구분하기도 함

1. 샘플 데이터 생성

    => 다운받은 sample.sql파일의 내용을 수행

2. Select

  1) 테이블의 전체 컬럼을 조회 - select * from 테이블명

    => usertbl 테이블의 모든 데이터 조회

        - select * from usertbl;

    => buytbl 테이블의 모든 데이터 조회

        - select * from buytbl;

  2) 테이블의 일부 컬럼이나 표현식 조회

    => select 컬럼명 이나 표현식 나열 from 테이블명

  3) 컬럼명에 별명 부여

    => select 절에서 컬럼명이나 표현식 뒤에 as 별명

    => as 생략가능

    => 별명에 공백이나 대문자 또는 한글이 포함된 경우 " "로 감싸서 설정

 

    => buytbl 테이블에서 userid와 price*amount 의 값을 조회

        - price*amout는 금액이라는 열 헤더로 조회

        - select userid, price*amount as "금액" from buytbl;

  4) where : 조건절

    => 형식 : select * 또는 조회할 컬럼명 or 표현식 나열 from 테이블 명 where 조건;

    => where는 from 다음에 수행

        - select 절에서 만든 별명은 where절에서 사용 불가

    => =, !=, <, <=, >, >= : 단일행 연산자 - 오른쪽에 하나의 값만 와야 함

    => between A and B : A와 B사이 - B가 A보다 크거나 같아야 함

    => in(값을 나열) : 나열된 값 중에서 일치하는 데이터를 조회

        - 하나의 컬럼에서 데이터를 찾을 경우 in이 or 보다 빠름

    => is null : null 조회

    => and : 2개의 조건을 모두 만족하는 경우

        - 앞의 조건이 false이면 뒤의 조건을 확인하지 않음

    => or : 2개의 조건중 하나라도 만족하는 경우

        - 앞의 조건이 true이면 뒤의 조건을 확인하지 않음

    => or를 만들때는 참일 확률이 높은 조건을 앞에, and를 만들때는 거짓일 확률이 높은 조건을 앞에 작성

    => not between A and B, not in, is not null : 조건을 반대로 적용

    => 실무에서 and나 or를 사용해야 하는 경우가 있으면 각각의 조건을 별도로 실행해보고 결정(앞? or 뒤?)

        - 프로그래밍 언어와 다른점은 쿼리의 속도가 많이 중요함

ex1) usertbl 테이블에서 name이 김태연인 데이터를 조회
select *
from usertbl
where name= "김태연";

ex2) usertbl 테이블에서 birthyear가 1990년 이후이거나 addr이 서울인 데이터를 조회
select *
from usertbl
where birthyear > 1990 or addr = "서울";

ex3) usertbl 테이블에서 birthyear가 1990년 이후이고 addr이 서울인 데이터를 조회
select *
from usertbl
where addr = "서울" and birthyear > 1990;

    => 패턴 일치는 like를 이용

        - % : 글자 수 상관없음

        - _ : 1글자와 매칭

        - id나 pw같은 경우는 완전 일치하는 데이터를 찾지만 대부분의 경우 부분 일치하는 데이터를 찾음

    => not like도 가능

ex4) usertbl 테이블에서 name에 '라'가 포함된 데이터를 조회
select *
from usertbl
where name like "%라%";

ex5) usertbl 테이블에서 name이 배로 시작하는 데이터의 name과 birthyear를 조회
select name, birthyear
from usertbl
where name like "배%"

  5) sub query

    => from 이나 where절에 사용되는 select 구문

    => from 절에 사용되면 inline view라고 하고, where 절에 사용되면 sub query라고 함

    => 서브쿼리의 종류는 단일행 서브쿼리와 다중행 서브쿼리가 있음

    => 단일행 서브쿼리: 결과가 하나의 행, 다중행 서브쿼리: 결과가 여러개로 구성

    => 단일행 서브쿼리는 단일행 연산자만 사용(=, !=, >, <, >=, <=)

    => 다중행 서브쿼리는 단일행 연산자 사용불가(= -> in, != -> not in, 부등호 -> any or all 사용)

    => 서브쿼리는 반드시 ( )안에 작성해야 하고, 메인쿼리가 실행되기 전에 1번만 실행

 

ex1) usertbl 테이블에서 name이 김태연인 데이터보다 birthyear가 더 큰 데이터의 name과 birthyear 조회
(김태연의 birthyear의 갯수는 1개이므로 단일행 서브쿼리)
select name, birthyear
from usertbl
where birthyear > ( select birthyear
                     from usertbl
                     where name = '김태연');

ex2) buytbl에서 userid가 ghr인 데이터가 구매한 productname과 동일한 productname을 구매한 userid와 productname을 조회
select userid, productname
from buytbl
where productname = (select productname
                     from buytbl
                     where userid = "ghr")
and userid != "ghr";

ex3) buytbl에서 userid가 kty인 데이터가 구매한 productname과 동일한 productname을 구매한 userid와 productname을 조회
select userid, productname
from buytbl
where productname in (select productname
                      from buytbl
                      where userid = "kty")
and userid != "kty";

  6) group by

    => 컬럼이나 표현식으로 그룹화 할때 사용하는 절

    => where 다음에 수행

    => 이 절이 종료되면 그룹함수를 사용할 수 있음

    => 그룹함수는 having과 select, order by 절에서 사용 가능

    => 그룹함수 : sum, avg, max, min, stddev, var_samp, count

    => 그룹함수는 null을 제외하고 연산을 수행

    => count함수는 컬럼 이름을 대입하지 않고 보통 *을 이용

  7) having

    => 그룹 함수를 이용한 조건을 만들 때 사용하는 절

    => where절의 조건은 having에 기재하도 되지만 효율이 떨어짐

  8) order by

    => 데이터를 정렬하기 위한 절

    => 컬럼명이나 표현식을 이용해서 데이터를 정렬(asc : 오름차순, desc : 내림차순 정렬)

    => 2개 이상의 컬럼명이나 표현식 사용시 앞의 값과 동일한 경우에 적용

    => 데이터베이스는 데이터를 인덱싱해서 저장하기 때문에 저장한 순서와 조회하는 순서는 다를 수 있음

        - 2개 이상의 데이터를 조회하는 경우 원하는 조건으로 정렬을 하여 조회하는 것을 권장

*  9) select 구문의 실행 순서 (동기식 : 순서대로 실행, 비동기식 구분(Thread) : 순서 없이 실행)

    - 5 - select

    - 1 - from

    - 2 - where

    - 3 - group by

    - 4 - having

    - 6 - order by

    => where의 조건은 having에 기술할 수 있지만 하지 않음

 

  10) select 절에서 컬럼명 앞에 distinct를 기재시 중복이 제거

    => 중복제거는 distinct대신 group by를 사용해도 됨

  11) 데이터 갯수 제한

    => select 구문의 마지막에 limit 데이터갯수나 limit 시작위치, 데이터갯수를 이용해서 필요한 갯수 만큼의 데이터를 가져올 수 있음

 

-- buytbl 테이블에서 userid별 평균 데이터 갯수 조회할 때, userid별 오름차순 정렬

select userid, count(*)

from buytbl

group by userid

order by userid asc;

 

-- buytbl 테이블에서 userid별 데이터 건수가 3개 이상인 데이터의 userid를 조회

select    userid

from     buytbl

group by          userid

having   count(*) >=3;

 

-- usertbl 테이블에서 userid 별로 내림차순 정렬한 후, 처음 3개의 데이터만 조회

select    *

from     usertbl

order by userid desc

limit      3;

 

-- usertbl 테이블에서 userid 별로 내림차순 정렬 한 후, 처음 3개의 데이터를 제외하고 3개의 데이터를 조회

select    *

from     usertbl

order by userid desc

limit      3,3;

 

3. MySQL이 제공해주는 함수

  1) 반올림 : ROUND(데이터, 반올림 할 자리)

  2) 문자열 함수

    - 문자열 결합 : concat(문자열1, 문자열2)

    - 문자열 추출 : substring(문자열데이터, 시작위치, 추출할 갯수)

    - 대소문자 변환 : upper, lower

    - 좌우 공백 제거 : trim

    - 문자 갯수 : char_length

    - 바이트 갯수 : octet_length

 

  3) null 처리 함수

    => IFNULL(데이터, 기본값) : 데이터가 존재하면 데이터, 없으면 기본값을 리턴

        - 프로그래밍 언어의 기본값과 매핑시, 이 함수의 사용을 고려

        - NULL -> int : 예외 발생

  4) 문자열을 가지고 비교를 할 때는 좌우 공백의 문제와 대소문자 구별의 문제를 고려

    - 데이터베이스의 컬럼에 NULL이 저장될 수 있는 경우 프로그래밍 언어와 연동시 주의해야 함

 

4. 날짜 및 시간

  1) 현재 날짜 및 시간

    - current_date() : 현재 날짜

    - current_time() : 현재 시간

    - current_timestamp() : 현재 날짜 및 시간

  2) 특정 날짜 및 시간

    => MySQL은 일반적인 형식의 날짜 및 시간, 문자열은 날짜 및 시간으로 인지

        ex) '2020-05-15 17:57:20' : 2020년 5월 15일 17시 57분 20초로 인식

    => str_to_date(날짜 문자열, 서식 문자열)

        ex) str_to_date('2020-05-15 17:57:20', '%Y-%M-%D %H:%I:%S')

  3) 날짜와 기간형 데이터와의 연산

    - 날짜 + 또는 - INTERVAL 정수 단위(DAY, HOUR, MINUTE, SECOND 등)

  4) 날짜 사이의 뺄셈

    - DATEDIFF(날짜1, 날짜2)

  5) 데이터 삽입

    - insert into 테이블명(컬럼명 나열) values(값을 나열);

    => 컬럼명을 기재하는 경우 순서와 상관없이 데이터를 대입할 수 있음

    => 컬럼명이 생략된 컬럼은 default가 대입, default 비설정시 null삽입

    => 모든 컬럼의 값을 순서대로 전부 대입시 컬럼명 전체를 생략 가능

        - insert into 테이블 명 values(값을 나열);

    => auto_increment가 설정된 컬럼은 생략시 자동으로 1증가하여 대입

 

-- usertbl 테이블에 userid은 kjn, name은 제니, birthyear는 1996, addr은 서울, mobile은 01012334566, mdate는 1996년 1월 16일로 데이터 삽입

(birthyear - 정수, mobile - char, mdate - date)

insert into usertbl(userid, birthyear, addr, mobile, mdate)

values('kjn', "제니", 1996, "서울", '01012341234', '1996-01-16');