eadgnus

인덱스(index)란?

    - 테이블의 동작속도(조회)를 높여주는 자료구조
    - 데이터의 위치를 빠르게 찾아주는 역할
    - MYI(MySQL Index)파일에 저장
    - 인덱스를 설정하지 않으면 Table Full Scan이 일어나 성능이 저하되거나 장애가 발생할 수 있음
    - 조회속도는 발라지지만 update, insert, delete의 속도는 저하될 수 있음
    - MySQL에서는 primary key, unique 제약조건을 사용하면 해당 컬럼에 index가 적용됌
    - 인덱스는 하나 또는 여러개의 컬럼에 설정할 수 있음. 
    - where절을 사용하지 않고 인덱스가 걸린 컬럼을 조회하면 성능에 아무런 효과가 없음 ★★★★★
    - 가급적 업데이트가 안되는 값을 설정하는것이 좋음 

order by, group by와 index

    - order by 인덱스 컬럼, 일반컬럼: 복수의 키에 대해 order by를 사용한 경우 효과x
    - where 일반컬럼1='값' order by 인덱스컬럼: 연속하지 않은 컬럼에 대해 order by를 실행한 경우  효과x
    - order by 인덱스컬럼1 desc, 인덱스컬럼2 asc: desc와 asc를 혼합해서 사용한 경우 효과x
    - group by 일번컬럼1 order by 인덱스컬럼: group by와 order by의 컬럼이 다른경우 효과x
    - order by 함수(인덱스컬럼): order by 절에 컬럼이 아닌 다른표현을 사용한 경우 효과x
    

인덱스 문법

create index 인덱스명 on 테이블이름(필드이름)
create index 인덱스명 on 테이블명(필드명1, 필드명2, ...)
    
    
create index ix_hp on member(hp); # 인덱스 걸기
show index from member;

인덱스 조회하기 

show index from 테이블명; 

    
인덱스 삭제하기    

alter table 테이블명 drop index 인덱스명;

예시)

select * from member;
show index from member;
create index ix_hp on member(hp); # 인덱스 걸기
show index from member;

alter table member drop index ix_hp; # 인덱스 삭제하기

목차:

1. 트랜젝션이란?
2. 특징
3. 자동커밋
4. 예외
5. 삭제 

1. 트랜젝션(Transaction)

    - 분할이 불가능한 업무처리의 단위 (ex.은행에서 송금&입금되는과정)
    - 한꺼번에 수행되어야 할 연산의 모음
    
    commit: 모든 작업들을 정상 처리하겠다고 확정하는 명령어로써, 해당 처리 과정을 DB에 영구적으로 저장
    rollback: 작업 중 문제가 발생되어 트랜젝션의 처리 과정에서 발생한 변경상항을 모두 취소하는 명령어
    
    start transaction;
        블록안 명령어들은 하나의 명령어처럼 처리됌
        ...
        성공하던지, 실패하던지 둘 중 하나의 결과가 됌
        문제가 발생하면 rollback;
        정상적인 처리가 완료되면 commit;

2. 트랜젝션의 특징

    - 원자성: 트랜젝션이 데이터베이스에 모두 반영 되던가, 아니면 전혀 반영되지 않아야 함
    - 일관성: 트랜젝션의 작업 처리 결과가 항상 일관성이 있어야 함
    - 독립성: 어떤 하나의 트렌젝션이라도, 다른 트렌젝션의 연산에 끼어들수 없다 (ex.영화관 매표할때 자리 지정)
    - 영구성: 결과는 영구적으로 반영되어야 함

3. 자동 커밋 

# 자동커밋 확인
show variables like '%commit%';

# autocommit: on -> 자동으로 commit 해줌
# set autocommit=0 (off), set autocommit=1 (on)
set autocommit=1; # 1은 켜기 0은 끄기

start transaction;               # 트랜젝션의 시작. commit or rollback으로 끝내야 함 
insert into product values ('100005', '고철', '팔아요', 100, now());
rollback;
select * from product;

4. 트랜젝션의 예외

DDL문(create, drop, alter, rename, truncate)에 대해 예외를 적용 -> rollback 대상이 아님

5. 삭제 (truncate)

# 개별적으로 행을 삭제할 수 없으며, 테이블 내부의 모든 행(데이터)를 삭제
# rollback이 불가능
# 트랜젝션 로그에 한 번만 기록하므로 delete 구문보다 성능면에서 빠름
# truncate table 테이블명 = delete from 테이블명

transaction 예시:

select * from product_new;
start transaction;
delete from product_new;
select * from product_new;
rollback;

start transaction;
truncate table product_new;
select * from product_new;
rollback;

select * from product_new;

뷰(View)

    - 가상의 테이블을 생성
    - 실제 테이블처럼 행과 열을 가지고 있지만 데이터를 직접 저장하고 있지는 않음   

뷰를 만드는 이유

    - sql 코드를 간결하게 만들기 위함 
    - 삽입, 삭제, 수정 작업에 제한 사항을 가짐
    - 내부 데이터를 전체 공개하고 싶지 않을 때

목차:

1. 만들기
2. 수정하기
3. 삭제하기
4. 확인

1. 뷰 만들기

기본 구조:
                create view 뷰이름 as 쿼리 ...;
예시:
        select userid, username, hp, gender from member;                                              # 원래 하던 방법
        create view vw_member as select userid, username, hp, gender from member; # 뷰를 이용하는 방법
        select * from vw_member;                                                                                     # 확인

# 문제

# member의 userid, username, hp와 profile의 mbti를 출력하는 뷰를만들고 select 할 수 있는 melon 계정을 생성

답안:
select m.userid, m.username, m.hp, p.mbti from member as m left join profile as p on m.userid = p.userid; 
# 만들거 미리 확인하기

create view vw_memberprofile as select m.userid, m.username, m.hp, p.mbti from member as m left join profile as p on m.userid = p.userid; 
# 뷰 만들기

create user 'melon'@'localhost' identified by '3333'; # 멜로 계정만들고 비번은 3333
grant select on kdt.vw_memberprofile to 'melon'@'localhost'; # 권한주기
flush privileges; # 바로 적용하게 하기

2. 뷰 수정하기

alter view 뷰이름 as 쿼리 ... 

# 뷰 대체
기본 구조: 
    create or replace view 뷰이름 as 쿼리 ... 
                            # 먼저 뷰를 만들때 이걸로 만들면 처음엔 뷰 생성 두번째 실행부터는 뷰 수정(리플레이스)가 된다.

예시:
create or replace view vw_memberprofile as select m.userid, m.username, m.hp, p.mbti from member as m left join profile as p on m.userid = p.userid; 
                           # 뷰 수정

3. 뷰 삭제하기

# drop view 뷰이름;
drop view vw_memberprofile;

4. 뷰에서 데이터 변경가능여부 확인해보기

create view vw_memberprofile as select m.userid, m.username, m.hp, p.mbti from member as m left join profile as p on m.userid = p.userid;
select * from vw_member;
update vw_member set hp='010-9999-9999' where userid='berry'; 
# 베리 번호 수정하기 # 뷰에서 변경하게 되어도 실제 데이터가 변경됌


insert into vw_member values ('avocado', '안가도', '010-8888-8888', '남자'); 
# Error Code: 1423. Field of view 'kdt.vw_member' underlying table doesn't have a default value
# 원본테이블에 not null 되어있는게 있어서 insert로 데이터가 들어가지 않는다.
# 해결하고 싶다면 원본에 null 값을 허용을 해주어야함

MySQL 사용자

목차:

1. 사용자 추가하기
2. DB 권한 부여하기 
   1. 할당 권한 상세 옵션
   2. IP권한 상세 옵션 
3. 예제
4. 사용자 제거
5. 사용자 권한 조회하기
6. 사용자 권한 제거하기

1. 사용자 추가하기

- mysql 8.0 command line client root 계정으로 로그인
- 로컬에서 접속 가능한 사용자 추가하기

기본 구조:
                create user '사용자명' @ 'localhost' identified by '사용자 비밀번호';
예시:
                create user 'apple'@'localhost' identified by '1111';

2. DB 권한 부여하기 

grant all privileges on *.* to '사용자' @ 'localhost'; #모든 DB에 접근가능 
grant all privileges on 데이터베이스명.to '사용자'@'localhost';
flush privileges; # 새로운 세팅을 적용함

            
✔ 2-1 할당 권한 상세 옵션

create, drop, alter:                  테이블에 대한 생성, 삭제, 변경 권한
select, insert, update, delete: 테이블의 데이터를 조회, 삽입, 변경, 삭제에 대한 권한
all:                                          모든 권한
usage:                                    권한을 부여하지 않고 계정만 생성

ex) grant select(,,,하면서 더 추가 가능) on (데이터베이스명.테이블명 to '사용자'@'localhost');
                                               # 괄호안에있는 것들은 생략가능 

✔ 2-2 IP권한 상세 옵션 

%: 모든 아이피에서 접근 가능
127.0.0.1: localhost에서 접근 가능
ex) grant select(,,,하면서 더 추가 가능) on 데이터베이스명.테이블명 to '사용자'@'%';
ex) grant select(,,,하면서 더 추가 가능) on 데이터베이스명.테이블명 to '사용자'@'특정아이피 주소';      

3. 예제

# apple 데이터베이스에 kdt.member 테이블을 복사하고 해당 테이블의 select 권한만 가능한 orange 계정을 만들어보자.
use kdt;
create table members(select * from member); 
create user 'orange'@'localhost' identified by '1111';
grant select on kdt.members to 'orange'@'localhost';

# 선생님 답안(터미널 사용)
use apple
create table member(select * from member); 
grant select on apple.members to 'orange'@'localhost';
flush privileges;

# 오렌지 계정 테스트
use apple;
select * from member;
update member set point = 500 where userid='berry';  # Error Code: 1142. UPDATE command denied to user 'orange'@'localhost' for table 'member'

# 사용자 목록 조회
use mysql;
select user, host from user;

4. 사용자 제거

# drop user 계정명; # 추천!
# delete from user where user=계정명;

5. 사용자 권한 조회하기

show grants for 계정명@'localhost';
show grants for 'apple'@'localhost';
show grants for 'orange'@'localhost';

6.사용자 권한 제거하기

revoke 권한명 privileges on 데이터베이스명.테이블명 from '계정명'@'localhost';
revoke all privileges on apple.* from 'apple'@'localhost';

목차:

1. 데이터 검색
2. 별명
3. 모든 컬럼 가져오기
4. null과 ' '
5. 조건절
   1. 조건절 예시(로그인 쿼리)
6. is
7. like 연산자
8. 정렬
   1. 조건절 + 정렬
9. limit
   1. 정렬 + limit
10. 집계함수
    1. count
    2. sum
    3. avg
    4. min
    5. max
11. 그룹
    1. 그룹 + 그룹조건
    2. 조건절 + 그룹 + 그룹조건 + 정렬

# 1. 데이터 검색

기본구조:
                select 필드명1, 필드명2...  from 테이블명;

예시:
         select 100;
         select 100+50;

# 2. 별명

기본 구조:
                select 필드명 as 별명 from 테이블명;

예시:
                select 100 +50 as '덧셈';            #  -- ''를 사용하는 이유는 띄어쓰기가 있을 수 있어서 사용
                select 100 + 50 as '덧셈 연산'; 
                select 100 + 50 as 덧셈;            #  ''없어도 가능은 하다
                select 100 + 50 덧셈;                 #  as도 없이 사용가능은 함

일부 항목만 가져오기:
                                   use kdt;
                                   select userid, username, gender from member; # 필드나 로우는 불러오는순으로 보여줌
                                   select userid, gender, username from member; # 순서가 정해져있는게 x

응용:
         select userid as 아이디, username as 이름, hp as 휴대폰번호 from member;

# 3. 모든 컬럼 가져오기(사용 권장x)

# 개발할때는 연산량이 많아져서 느려짐
# 실제 개발시, 필요한 컬럼만 가져와서 사용함
# 실서버에서 사용시 모든연산이 이것만 처리해서 유저들화면에는 흰로딩창만 뜨게됌

select * from member;

# 4. null과 ' '

select null;            # 데이터가 없음, insert가 되지 않은것
select '';                # DB에서는 ''라는 데이터가 있는것

select 100+null;    # 결과: null, 연산할수 없음
select 100+'';        # 결과: 100, 연산가능

# 5. 조건절

기본 구조:
                select 필드명1, 필드명2 ... from 테이블명 where 조건절;
                조건에 맞는걸 먼저 필터링후 셀렉함

예시:
        select userid, username, hp, email from member where userid='apple';
        select userid, username from member where gender='남';
        select userid, username, point from member where point >= 300;

# 5 - 1 (로그인 쿼리)

ex)   id = apple      pw=1234
select userid, username, hp, email from member where userid = 'apple' and userpw = '1234';
# 맞을시 데이터가 반환되면 로그인 시켜주고

select userid, username, hp, email from member where userid = 'apple' and userpw = '1000'; 
# 반환된 데이터가 없으면 틀렸다고 출력하면 됌

# 6 is

사용예시:
              select userid, username, hp from member where address1 = 'null'; # 틀린 방법
              select userid, username, hp from member where address1 = null; # 틀린 방법2
              select userid, username, hp from member where address1 is null; # 정답
              select userid, username, hp from member where address1 is not null; # 정답2

응용:
        select userid, username, point from member where point between 300 and 600; # 이 두개가 같은 말이다
        select userid, username, point from member where point >= 300 and point <= 600; # 300이상 600이하

# 7 like 연산자

# 게시판에서 제목, 글내용 검색할때 자주 사용됌

select userid, username from member where userid like 'a%';           -- a로 시작하는 문자열을 찾아달라

select userid, username from member where userid like '%a';           -- a로 끝나는 문자열

select userid, username from member where userid like '%a%';        -- a가 들어가는 문자열

select userid, username from member where userid like '%app%';    -- app이 들어가는 문자열

select userid, username from member where userid like 'app__';      -- app으로 시작하는 총5글자인 문자열

# 8.정렬

기본 구조:
                # select 필드명1, 필드명2 ... from 테이블명 order by 정렬할필드명 [asc, desc];
                # 써도되고 안써도됌 [asc, desc]

예시:
        select userid, username, point from member order by userid asc; # 아이디로 오름차순
        select userid, username, point from member order by userid desc; # 아이디로 내림차순alter
        select userid, username, point from member order by userid; # 생략할시 오름차순

응용:
        #포인트를 기준으로 내림차순 하고 같은 점수인경우 아이디로 내림차순
        select userid, username, point from member order by point desc, userid desc;
        # 이미 포인트가 기준이었기 때문에 , 뒤에 정렬할것만 붙여주기하면 됌

# 8 - 1. 조건절 + 정렬

기본 구조:
                # select 필드명1, 필드명2... from 테이블명 where 조건절 order by 정렬할필드명 [asc, desc];

응용:
        # 성별이 여성인 회원을 point가 많은순으로 정렬(단, 포인트가 같을경우 먼저 가입한 순으로 정렬)
        select userid, username, gender, regdate, point from member where gender='여' order by point desc, regdate asc;

#  9. limit

기본 구조:
              # select 필드명1, 필드명2 from 테이블명 limit 가져올 행의 갯수
              # select 필드명1, 필드명2 from 테이블명 limit 시작행, 가져올 행의 갯수

예시:
        select userid, username, gender from member limit 3;        # 처음부터 3개의 행을 가져옴
        select userid, username, gender from member limit 3, 2;    # index 3행부터 2개의 행을 가져온다는뜻

# 9-1 정렬 + limit

기본 구조:
             # select 필드명1, 필드명2 from 테이블명 order by 정렬할필드명 [asc, desc] limit 가져올 행의 갯수;

예시:
        select userid, username, gender, point from member order by point desc limit 3;

# 10. 집계함수(행을 통틀어서 계산해주는함수)

# 10 - 1. count: 행의 갯수를 세줌

# 전체인원을 알고싶다!:                  primary key가 적용되어 null이 포함될수 없음
                                                        select count(userid) as 전체인원 from member;

# 주소를 입력한 인원을 알고싶다!: null이 있으면 주소를 입력하지 않았음
                                                        select count(zipcode) as 우편번호 from member; # null을 제외하고 갯수를 셈

# 10 - 2. sum: 행의 값을 더함

기본 구조:
                select sum(point) as 포인트총합 from member;

오류:
                select userid, sum(point) as 포인트합 from member;
                # Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT
                   list contains nonaggregated column 'kdt.member.userid';
                   this is incompatible with sql_mode=only_full_group_by
                # 앞에 무언갈 적어주고싶으면 그룹을 적어야함

# 10 - 3. avg: 행 값의 평균을 구함

기본 구조:
                select avg(point) as 평균 from member;

  
# 10 - 4. min: 행의 최솟값을 구함

기본 구조:
                select min(point) as 최솟값 from member;

# 10 - 5. max: 행의 최댓값을 구함

기본 구조:
                select max(point) as 최댓값 from member;

# 11. 그룹

기본 구조:
             # select 그룹을 맺은 컬럼 또는 집계함수 from 테이블명 group by 그룹을맺을필드명;

예시:
       select gender from member group by gender; 
       select gender, sum(point) from member group by gender;
       select gender, avg(point) from member group by gender;
       select gender, count(userid) as 총원 from member group by gender; # 이런식으로 활용가능

# 11 - 1. 그룹 + 그룹조건

기본 구조:
             # select 그룹을맺은 컬럼 또는 집계함수 from 테이블명 group by 그룹을 맺을 필드명 having 조건절;

예시:
         select gender from member group by gender having gender = '여';

# 11 - 2. 조건절 + 그룹 + 그룹조건 + 정렬

기본 구조:
             # select 그룹을맺은 컬럼 또는 집계함수 from 테이블명 where 조건절 group by 그룹을 맺을 필드명 having 조건절 order by 정렬할필드명 [asc,desc];

응용:
        # 포인트가 0이 아닌 회원들중에 남, 여로 그룹을 나눠 포인트의 평균을 구하고 포인트가 100 이상인 성별을 검색하여 내림차순으로 정렬
         select gender, avg(point) from member where point > 0 group by gender having avg(point) >= 100 order by gender desc; # 별명 x
         select gender, avg(point) as avg from member where point > 0 group by gender having avg >= 100 order by gender desc; # 별명 o

SQL 연산자

1. 산술 연산자

+, -, *, /, mod(나머지 연산), div(몫연산)
    

2. 비교 연산자

=(같다), <, >, >=, <=, <>(다르다)
    

3. 대입 연산자

=

4. 논리 연산자

and, or, not, xor
    

5. 기타 연산자

    1. is: 양쪽이 모두 같으면 True 아니면 False
    2. between A and B: 값이 A보다는 크거나 같고 B보다는 작거나 같으면 True 아니면 False
    3. in: 매개변수로 전달된 리스트에 값이 존재하면 True 아니면 False
    4. like: 패턴으로 문자열을 검색하여 값이 존재하면 True 아니면 False