쿼리를 짜다보면 여러 행을

하나의 행으로 묶어서 보여줘야 할 때가 있다. 

여러 행을 하나로 합치는 건 기본적으로 집계이다.

그렇기 때문에 GROUP BY를 사용해 기준을 주어서 묶고 

함수를 사용해서 결과를 만들어야 한다.

이때, 여러 행을 ', '로 구분해서 합쳐주는 함수가

WM_CONCAT 이다. 

예시

다음의 예시를 통해 사용방법을

쉽게 익힐 수 있을 것이다.

개인별로 지원해본 회사명을 

입력한 테이블이 있다고 가정할 때,

이 테이블의 내용을 개인당 한 행으로

합쳐서 보이도록 하는 예시이다.

SELECT 이름,WM_CONCAT(회사명) AS "회사명"
FROM (
    SELECT 이름,회사명
    FROM TB_COM 
    )
GROUP BY 이름

도움이 되었다면

로그인이 필요없는 공감 버튼 꾹 눌러주세요! 

WITH 문

WITH [테이블명] AS (
    SELECT ... 
    FROM ...
    -- [임시테이블 SQL]
)
SELECT ..
FROM ... 
-- [사용 테이블]

쿼리를 만들다 보면 하나의 SQL문에서

가공한 테이블을 계속해서 사용해야 할 때가 있다.

가공한 테이블이 쿼리 안에서 계속 쓰다 보면 

가독성이 떨어져 혼란스럽고 개발하기 복잡해지는 경우가 종종 있다.

예시 

다음의 쿼리는 별로 복잡하지 않지만
엄청 복잡한 쿼리라고 가정해보자

-- 자주 조회할 테이블

SELECT T1.키,T1.컬럼1, T2.컬럼2
FROM T1 
    LEFT OUTER JOIN T2 
    ON T1.키 = T2.키

이제 이 쿼리는 복잡한 쿼리를 가져와서 

조건, 조인 등으로 사용하면서 더더욱 복잡해질 것이다.

-- 복잡해진 쿼리

SELECT A.키, TEST1.컬럼2-A, TEST2.컬럼2-B
FROM A
    LEFT OUTER JOIN 
    (
    SELECT T1.키,T1.컬럼1, T2.컬럼2
    FROM T1 
        LEFT OUTER JOIN T2 
        ON T1.키 = T2.키
    ) TEST1
    ON A.키 = TEST1.키
    AND TEST1.구분코드 = 'A'
    
    LEFT OUTER JOIN 
    (
    SELECT T2.키,T1.컬럼1, T2.컬럼2
    FROM T1 
        LEFT OUTER JOIN T2 
        ON T1.키 = T2.키
    ) TEST2
    ON A.키 = TEST2.키
    AND TEST2.구분코드 = 'B'

복잡해서 잘 안읽히는 코드는 유지보수에도 어려움이 있기 때문에 
최대한 보기 좋게 짜는 것도 요령인듯하다
이런 점에서 WITH 문을 활용할 수 있다

-- 간결해진 쿼리

-- 자주 조회할 테이블
WITH TEMP AS(
    SELECT T1.키,T1.컬럼1, T2.컬럼2
    FROM T1 
        LEFT OUTER JOIN T2 
        ON T1.키 = T2.키
)
-- 메인 쿼리
SELECT A.키, TEST1.컬럼2-A, TEST2.컬럼2-B
FROM A

    LEFT OUTER JOIN TEMP TEST1
    ON A.키 = TEST1.키
    AND TEST1.구분코드 = 'A'
    
    LEFT OUTER JOIN TEMP TEST2
    ON A.키 = TEST2.키
    AND TEST2.구분코드 = 'B'

도움이 된 정보였다면

로그인이 필요 없는 공감 버튼 꾹 눌러주세요! 

MariaDB(MySQL)를 새로 재설치할 때마다 

초기 설정은 기억 저 너머로 ...

또 잊을까봐 기록해본다.

한글 설정하기

데이터베이스 설정 시

꼭 빼놓지 말아야할 것이 바로 

한글 설정이다.

1. 현재 설정 확인

MariaDB에 접속해서 

다음과 같은 명령어를 입력하면

인코딩을 한번에 확인할 수 있다.

-- 인코딩 설정 확인
show variables like 'c%';

한글은 EUC-KR이나

UTF-8 인코딩을 많이 쓰는데 

특히, 리눅스 환경에서

MariaDB(MySQL)를 설치하면 

서버의 character_set이 엉뚱한 인코딩으로

설정되어있기 때문에

한글데이터가 "?" 로 깨져서 나온다.

2. 설정 방법

설정방법은 MariaDB안에서 해주지않는다.

MariaDB 설치시에 생성되는

my.cnf라는 설정파일에서 들어가서 

아래의 항목들을 추가해주면 된다.

//설정파일의 폴더로 이동 

cd /etc/mysql
sudo vi my.cnf 

아래의 내용을 설정에 추가해주면 된다.

//[client] 하위에 아래의 내용 추가

default-character-set = utf8


//[mysqld] 하위에 아래의 내용 추가

init_connect = "SET collation_connection = utf8_general_ci"
init_connect = "SET NAMES utf8"
character-set-server = utf8
collation-server = utf8_general_ci


//[mysql] 하위에 아래의 내용 추가

default-character-set = utf8

설정 후 MariaDB 재실행

sudo service mariadb restart

// mysql의 경우

sudo service mysql restart

모든 설정이 완료된 후 

DB에 접속해서 설정값을 조회해보면

위와 같이 utf-8들로 설정 된 것을 확인할 수 있다.

이렇게 설정이 되면

insert시 한글을 넣더라도 깨지않는다.

도움이 되는 포스팅이었다면

로그인이 필요없는 공감 버튼 꾹 눌러주세요! 

DML은 자주 쓰다보니 잊어버리지 않는데 

아무래도 DDL은 빈도가 낮다보니 자주 잊어버린다 😫

"컬럼 변경하기"

아래 예시 코드는

WORSHIP 테이블의 version 컬럼을 변경하는 쿼리이다.

컬럼명을 order_version 으로 변경하고

데이터 타입은 INT 로,

NOT NULL 제약을 적용하고 

기본값을 0으로 지정하는 쿼리이다. 

ALTER TABLE `WORSHIP` 
CHANGE COLUMN `version` `order_version` INT 
NOT NULL 
DEFAULT 0 
COMMENT 'version' ;

마지막에 COMMENT는

컬럼에 대한 코멘트라고 이해하면 되는데

ERD 자동화 툴을 사용하면

컬럼의 논리명이 COMMENT에 들어간다.

테이블에 컬럼 추가하는건 가끔하는데

매번 까먹어서 찾는다 ㅠㅠ

나름 DB 공부를 열심히 한다고 했고

DB 관련 자격증도 몇개 취득했음에도...

자주 사용하지 않으면 까먹는 건 어쩔 수 없나보다..!

화면에서 게시글이 보여질지 여부를 판단하기 위해서

컬럼을 하나 추가하는 작업이 었다.

오라클에 사용했고

DEFAULT로 'true'가 들어가게 해놓았다.

-- ALTER TABLE 테이블명 ADD (컬럼명 자료형 DEFAULT '값');
ALTER TABLE BOARD_CONTENT ADD (VIEW_YN varchar (5) DEFAULT 'true');

Python으로 DB에 insert 문을 execute 했는데 다음과 같은 에러 로그가 뜬다...

# 에러 로그
pymysql.err.DataError: (1406, "Data too long for column 'book' at row 1")

DB 스카마가 잘못된 줄 알고 Column 사이즈도 늘려봤지만 여전히 같은 문구의 에러를 출력한다.

-- Strict mode 확인하기 

-- MySQL이나 MariaDB에서 데이터 insert 할 때 문제가 되는 부분을 점검하는 걸로 보인다.
-- 아래와 같은 구문을 통해서 현재의 모드를 확인할 수 있다.

select @@global.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                         |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+

자세히 읽어보면 테이블로 데이터 전송을 엄격하게하고, 영으로 나누지 못하게하고.. 여러가지 방법으로 보호하나보다. 찾아보니 Data too long~ 에러를 일으키는 원인은 STRICT_TRANS_TABLES 이었다. 과감하게 이 부분을 지운다.

 set @@global.sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

이제 insert가 정상적으로 된다!

리눅스 환경이라면 /etc/my.cnf 부분에 접속해서 기본 설정을 직접 바꿔주고 설정을 적용하여 실행하게 하는 것도 방법이다.

import pymysql.cursors

conn = pymysql.connect(
        host='aaaicu.synology.me', # MariaDB 서버
        user='dev_kyome',  # MariaDB 아이디
        password='password', # MariaDB 비밀번호
        db = 'database_name', # MariaDB 데이터베이스명
        charset='utf8' )
        
        

Python으로 MariaDB에 연결하려하니 콘솔에 아래와 같은 에러로그를 출력한다.

#에러 로그
pymysql.err.OperationalError: (1044, "Access denied for user 'dev_kyome'@'%' to database 'database_name'")

dev_kyome라는 계정이 데이터베이스에 접근할 권한이 없기 때문이다.

-- dev_kyome 사용자에게 권한을 부여한다.
-- GRANT ALL PRIVILEGES ON ~~로 해서 전체 권한을 줄 수도 있지만,
-- 개발용 계정이 너무 많은 권한을 갖고 있을 필요가 없기 떄문에 DML만 주기로 했다.

GRANT DELETE, INSERT, SELECT, UPDATE ON database_name.* TO `dev_kyome`@`%` IDENTIFIED BY 'password';

-- `dev_kyome`@`%`  에서 @ 뒤는 접속하는 호스트를 말하는데, 
-- 외부에서 접속해서 조작할 수 있는 권한을 주기 위해서 
-- %로 외부에서 접속할 수 있게 설정해주었다.

-- 권한 확인하기 
SHOW GRANTS FOR 'dev_kyome'@'%';

-- 'dev_kyome'@'%' 설정했던 호스트를 입력해야지만 확인 가능하다.

+-------------------------------------------------------------------------------------------------------------+
| Grants for 'dev_kyome'@'%';                                                                                 |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dev_kyome'@'%' IDENTIFIED BY PASSWORD 'password'                                     |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `database_name`.* TO 'dev_kyome'@'%'                                |
+-------------------------------------------------------------------------------------------------------------+

데이터를 한번에 insert 하기위해 Python으로 MariaDB의 root 계정에 연결을 시도했다.

import pymysql.cursors

conn = pymysql.connect(
        host='aaaicu.synology.me', # MariaDB 서버
        user='root',  # MariaDB 아이디
        password='password', # MariaDB 비밀번호
        db = 'database_name', # MariaDB 데이터베이스명
        charset='utf8' )

그런데, 다음과 같은 에러 로그가 출력되었다.

pymysql.err.OperationalError: (1045, "Access denied for user 'root'@'000.000.000.000' (using password: YES)")

root 계정접속이 외부호스트에서 막혀있기 때문이다.

액세스할 수 있도록 호스트를 설정해주지 않으면 localhost만 허용되기 때문에 외부에서 접속가능한 계정을 만들어 주거나 root의 설정을 수정을 해주어야한다

계정 생성하기

나는 root의 계정을 열어주기보단 외부에서 접속할 수 있는 계정을 열어 줄 생각이다.

create user `dev_kyome`@`%` identified by 'password';

‘생성할 계정명’@‘접속 호스트’ 로 생성 하면된다.

접속 호스트는 특정 IP를 입력해도 되고 도메인명을 입력해도 된다.

외부 어디서든 접속 가능하도록 만드려면 %를 입력하면 된다.

1. ORA-28000 에러

SCOTT 계정으로 접속해서 EMP테이블로 실습을 하려고하는데

다음과 같은 오라클 에러가 나왔어요... ㅠ 

계정이 잠겨있다는 말인데 계정의 잠김 여부는 어디서 확인할 수 있고 어떻게 풀 수 있는지 확인해보겠습니다!

2. 해결

USER의 잠김 상태확인

SYS계정으로 접속해서 DBA권한으로 유저들을 확인해보면 잠겨있는지 여부를 확인할 수 있어요.

수많은 USER들이 나오기 때문에 조건문에 SCOTT을 추가하여 검색해보겠습니다. 

SCOTT의 ACCOUNT_STATUS를 보니 패스워드 기한만료와 계정 잠금상태였네요.

여기서 잠깐 ACCOUNT_STATUS 상태를 정리하자면 아래와 같아요.

USER의 잠금해제

계정의 잠그거나 해제하는 방법은 아래와 같습니다.

위와 같은 방법으로 SCOTT 계정의 잠금 상태를 해제해보겠습니다.

USER의 패스워드 재생성

잠금은 풀렸으나 아직 패스워드가 파기된 상태입니다.

파기된 패스워드는 다시 재생성 해주어야 합니다.

패스워드 재생성은 이전에 올린 게시글 (클릭)을 참조하시면 될 것같아요

패스워드를 재생성 해주니 드디어 계정이 'OPEN' 상태가 되었네요.

OPEN된 계정으로 접속한 결과입니다. 

예상대로 잘 접속됩니다! :-) 

패스워드를 생성하는 것과 반대로 패스워드를 파기할 수도 있습니다.

구문은 아래와 같습니다.

1.USER 패스워드 분실  

오라클 사용자 비밀번호를 잊어버려서 접속이 안 되는 경우 어떻게 해야 할까요? 

(windows10 환경)

2.USER 패스워드 변경

이런 경우 SYS 계정에 접속해서 USER의 패스워드를 변경해주시면 됩니다.

사용자 패스워드 변경방법은 아래와 같아요!

SYS 계정의 패스워드도 잊어버렸다면 어떡할까?

명령 프롬프트에서 패스워드 입력 없이 접속이 가능합니다. (실습환경에서)

첫번째는 sqlplus 접속과 동시에 sys계정으로 들어가는 방법입니다. 

두번째 방법으로는, SQL Plus에 로그인 없이 접속하여 SYS로 연결하는 방법도 있습니다.

접속했다면 아래와 같이 SYS계정의 패스워드도 변경하시고 

원래 접속하려고 했던 USER의 패스워드까지 변경해주신다면 끝!

*참고로 데이터베이스에 USER의 목록을 확인하고 싶다면

위의 SELECT 문으로 확인하실 수 있습니다.