리눅스 강의 13일차_2025_02_17

지난 시간에는 MariaDB의 DDL, DML, DCL의 개념을 알고 어떤 명령어들이 있는지 확인했었다.

 

이번 시간에는 DCL의 명령을 실행시켜 보자.

 

user 1,2,3을 생성하고

 

user1에는 어떠한 권한도 주지 않고, user2, user3에 권한을 각각

2에는 shopping_db의 고객 테이블만 조회할 수 있는 권한을, 

3에는 shopping_db에 전부를 조회할 수 있는 권한을 제공한다.

 

사용자의 권한 상태를 보고 싶을 때는 show grants for '유저명'@'유저IP'를 적어주면 된다.

 

user1은 권한이 없어 db로 이동도 하지 못하고, show databases;를 사용 시 db가 2개밖에 보이지 않는다.

 

이제 user2로 로그인해 보자.

 

user2는 customer 테이블은 조회할 수 있으나 purchase는 조회하지 못한다.

조회할 수 있는 DB가 하나 늘고 고객 테이블을 확인할 수 있다.

 

고객 테이블은 조회가 가능하나, purchase 테이블은 조회하지 못하는 것을 볼 수 있다.

 

user3은 shopping_db의 모든 테이블을 조회할 수 있는 권한을 받았지만, 데이터 삽입 권한은 없다.

실패하는 것을 볼 수 있다.

 

insert 권한 추가

문수철이 잘 들어간 것을 볼 수 있다.

 

 

revoke 명령으로 권한을 제거할 수 있다.

---

트랜잭션 실습 내용

purchase의 구매 내역이 제거된다는 시나리오

1. 구매 내역에서 제거 전 구매 취소 테이블에 옮긴다.
2. 구매 내역에서 취소한다.

이 두 개의 작업은 하나만 진행돼도 안되고 반드시 두 개 다 실행돼야 한다.
(하나의 논리 작업으로 묶어야 함)

진행 중인 과정을 하나의 논리 과정으로 묶었으므로 트랜잭션 시작 시 다른 계정에서는
진행 상황을 볼 수 없고, 작업을 완료하고 commit을 치지 않으면 그 진행 상황은 
적용되지 않는다.

원하는 단계까지 갔을 때 savepoint를 설치하고 
실수나 오류 발생 시 rollback to [savapoint명]을 
설정해서 해당 지점까지 돌아갈 수 있다.

그냥 rollback만 치면 트랜잭션은 종료되고, 트랜잭션 전의 사항으로 돌아간다.

 

자 구매 취소 테이블을 만들어보자.

기존 purchase db에서 취소 일자를 추가한 cancel 테이블이다.

 

이제 트랜잭션을 켜보자

 

 

작업을 하나 할 때마다 save point를 생성해 주면, 언제든지 돌아갈 수 있고 최악의 경우 트랜잭션 걸기 직전 상황까지 돌아갈 수 있다.

 

choi가 산 물건들을 전부 환불하고 계정을 삭제했다고 생각해 보자.

 

하지만 삭제 전에 반드시 삭제 테이블로 옮기고 삭제를 해야 할 것이다.

 

실수로 cancel 테이블에 옮기지 않고 삭제했지만 괜찮다.

 

rollback 명령을 하면 트랜잭션을 걸기 전으로 돌아간다.

 

멀쩡한 모습을 볼 수 있다.

 

만약 진행 상황을 저장하며 진행하고 싶다면, savepoint 명령으로 지정한 진행 상황으로 이동할 수 있다.

 

 

첫 번째 리눅스에서 트랜잭션이 켜진 상태라면 무슨 짓을 하든 트랜잭션을 끝내기 전까지는 두 번째 리눅스에 영향을 주지 않는다.

---

시나리오2
1. 구매 내역에서 제거 전 구매 취소 테이블에 옮긴다.
2. 구매 내역에서 취소한다.
3. 계정 제거(customer에서 삭제)

최시원이 탈퇴하려고 한다.

 

트랜잭션을 킨 상태에서 최시원을 구매 취소 테이블에 옮기고 

customer 테이블에서 choi를 제거한다면 두 번째 리눅스에서는 어떻게 될까?

 

 

choi 레이블을 cancel로 옮기고 purcase에서 삭제한 뒤

 

 

이렇게 고객 테이블에서 삭제시키고 두 번째 리눅스에서 choi의 데이터를 update 하려고 한다면?

 

트랜잭션 특징상 끝나기 전까지 행을 lock 할 수밖에 없기 때문에 두 번째 리눅스는 그 행의 lock이 풀리기 전까지 대기해야 할 것이다.

 

이런 경우를 교착상태 혹은 데드락 상태라고 한다.

 

 

이 상황에서 첫 번째 리눅스에 commit을 한다면 없는 데이터이므로 아무 일도 일어나지 않으며,

rollback을 한다면 choi가 30세로 바뀐 채 지워지지 않은 상태일 것이다.

 

---

 

 

grant로 권한을 줄 때 update 권한에서 인스턴스를 업데이트하는 권한을 특정 필드에만 줄 수 있다.

 

 

insert로 인스턴스 삽입은 앞에 쿼리문장을 한 번만 써도 인스턴스 값을 반복해서 삽입할 수 있다.

 

 

value 전에 속성을 정해놨어도 해당 사항은 같다.

 

join은 교집합 결과로 데이터를 결합하는 방법을 의미한다.

 

보통 pk끼리 결합해서 조인을 하고, 거기에 select 문으로 원하는 필드만 볼 수 있다.

 

 

평균의 값을 넣고, A반만 출력해 보자

 

 

A반 학생의 각각의 평균과 학생들의 과목별 평균까지 출력해 보자

 

a반, b반 학생들의 평균과 과목 평균을 포함해서 출력해 보자

 

 

결과

 

 

inner 조인은 두 테이블에 모두 정보가 있을 때만 출력된다.

 

한 테이블에 값이 없어도 그 값을 비우고 출력하고 싶다면 left outer join을 사용해 보자

---

join은 종류가 많다.

 

일단 조인 연산이란 공통 속성을 중심으로 두 개의 릴레이션을 하나로 합쳐서 새로운 릴레이션을 출력하는 것이다.

 

1. inner 조인

조인 연산은 나온 출력을 그대로 쓸 수도 있지만 보통 select로 원하는 속성을 골라서 원하는 모습으로 출력하는 경우가 많다.

 

조합한 id와 cust_id 전부 있는 것을 보니 조인될 때 중복된 속성을 하나 가지게 된 것을 알 수 있다.

 

여기서 자연 조인(natural join)이 나온다.

 

2. natural join

자연조인을 하기 위해서는 조인될 두 테이블에서 조인에 사용될 필드명이 같아야 한다.

 

 

자연 조인은 속성의 값이 같으므로, 어떤 필드명이 어떤 테이블의 것이고 그 둘은 같은 값이다 라는 정보를 제공할 필요가 없다.

 

그리고 출력에 중복되는 필름이 나오지 않는다.

 

inner 조인은 두 테이블에 모두 있는 pk(조인에 사용된 값) 값만 출력된다.

 

초록이와 퍼렁이가 추가됬지만 score 테이블에는 없기 때문에 출력되지 못한다.

 

하지만 outer left join을 사용 시 왼쪽으로 설정한 학생 테이블에 있는 pk 튜플을 모두 넣고 score를 채우며, 값이 없는 경우는 null로 채우는 형식으로 진행한다.

---

group by
같은 내용끼리 묶어서 연산

 

join에 사용된 키로, 오름차순으로 정렬된다.
정렬의 기준을 바꾸려면
order by no asc/desc

 

\

---

서브쿼리

부속 질의라고도 하며, select 절과 from 절이 반드시 포함되어야 한다.

 

연산자	설명
IN	목록중에 있는가
ANY, SOME	값을 서브쿼리에 리턴된 각각의 값과 비교한다. (하나라도 만족하면 OK)
ALL	리턴된 모든 값을 만족해야 한다.
EXISTS	만족하는 값이 존재하는지의 여부 확인

 

이런 식으로 질의안에 질의를 한 번 더 하는 형식이다.

---

가장 많은 판매액을 보인 물건을 매출 정보만 출력

select * from purchase
where product = (서브 쿼리)

만약 2개가 같은 값으로 나온다면?

select product, sum(count)
    -> from purchase
    -> group by product;


select product from purchase
group by product
having sum(count) = 10;


select product from purchase
    -> group by product
    -> having sum(count) = (
select sum(count) from purchase
    -> group by product
    -> order by desc
    -> limit 1
);

where product in ("게임기, R

=은 여러 개의 값을 대변할 수 없지만
in은 ~중 하나의 기능을 가질 수 있다.

---

제품 이름이 a혹은 b인 매출 정보만 출력

where product = 'a' or product = 'b'
where product in ('a', 'b')


제품 이름이 tv, 세탁기, 냉장고인 매출 정보
select * from purchase 
where product in ("TV", "세탁기", "냉장고");


 tv, 세탁기, 냉장고를 제외한 매출 정보
select * from purchase 
where product not in ("TV", "세탁기", "냉장고");

information_schema에 다른 db의 메타데이터들이 들어가 있음
데이터딕셔너리

explain schemata
데이터베이스에 대한 자세한 설명

tables

select table_schema, talbe_name, table_rows, table_collation, table_comment
from tables;

table_schema, table_name

shopping_db customer
purchase


select table_schema, table_name, column_name, data_type, CHARACTER_MAXIMUM_LENGTH, CHARACTER_SET_NAME
from columns
where table_schema = "school";




모든 권한
ALL PRIVILEGES => user_privileges
모든 권한을 받은 유저

shopping_db.* => schma_privileges
db권한

shopping_db.customer => table_privileges
테이블 권한

shopping_db.customer(id, name, age) => colum_privileges
컬럼 권한

---

계정 정보 수정 시 

use mysql

user라는 테이블에서 관리한다.

'admin'@'localhost'  password
host user  hash_value

select * from global_priv

계정 비밀번호 변경
alter user 'admin1'@'192.168.1.200' identified by 'p@ssw0rd';

 

 

인덱스와 뷰

인덱스(색인)

 

pk, fk는 자동으로 색인작업이 이뤄진다

 

인덱스 생성


인덱스 조회 방법

mysql 가면 word라는 db파일을 제공함