SQL Cook: DBeaver & DB 연동

Chappie: 코딩 좋아하는 사람·2022년 7월 2일
dbeaver mariadbeaver 연동sql cookbook 데이터베이스sql cookbook 예제 데이터베이스
0

SQL_COOK

목록 보기
2/35
post-thumbnail

DBeaver

window 환경의 HeidiSQL에 대응되는 DB 관리 툴을 찾다가 발견했다.

설치

  1. 공식 홈페이지에서 커뮤니티 버전을 다운 받거나 
% brew install --cask dbeaver-community
  1. 위와 같이 brew를 통해 설치할 수 있다.

실행


사진의 아이콘을 클릭하여 DB 서버에 접근할 수 있다.

사용 중인 DB 서버를 선택한다.

  • Database:
    생성해둔 Database 이름을 적어준다
    터미널 환경에서 use 데이터베이스 이름 입력과 같은 원리다

    • 당연하게도 터미널 환경에서 DB 서버에 접속하여 Database를 만들어둔 상태여야 한다
    • 아래의 사용자 이름 및 비밀번호도 마찬가지

  • Username / Password:
    해당 Database에 접근 권한이 있는 계정명과 비밀번호를 입력해준다

  • Server Time Zone:
    DB를 다루면서 기록되는 시간의 기준점을 설정한다
    Asia/Seoul 이 있으니 찾아서 설정한다

  • Test Connection:
    말 그대로 연결 테스트 버튼이다
    최초 테스트 커넥션 시에 오류가 나올 수 있다
    화면에 나올 드라이버 설치를 해주면 즉시 해결된다

SQL Cookbook 샘플 Data 입력

책의 초장에 '사용 테이블'이 명시되어 있다.

  • 'emp'와 'dept'라는 두 개의 테이블을 사용
    • emp: 숫자 / 문자열 / 날짜 필드로 이루어진 14행 테이블
    • dept: 숫자 / 문자열 필드로 이루어진 4행 테이블

정말 간단한 구조지만 나는 이마저도 귀찮았으며, 이를 위한 쿼리를 검색하는 일도 귀찮았다.

한빛미디어 홈페이지에서 쿼리를 찾았다

/* Statement 1. 
EMP 테이블의 상위 테이블이 될 DEPT 테이블 생성*/
create table dept(  
  deptno     number(2,0),  
  dname      varchar2(14),  
  loc        varchar2(13),  
  constraint pk_dept primary key (deptno)  
);


/* Statement 2. 
DEPT 테이블에 대한 외래 키 참조가 있는 EMP 테이블 생성. 
외래 키를 사용하려면 EMP 테이블의 DEPTNO가 DEPT 테이블의 DEPTNO 열에 있어야 합니다.*/

create table emp(  
  empno    number(4,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0),  
  constraint pk_emp primary key (empno),  
  constraint fk_deptno foreign key (deptno) references dept (deptno)  
);


/* Statement 3. DEPT 테이블에 행을 삽입 */
insert into DEPT (DEPTNO, DNAME, LOC)
values(10, 'ACCOUNTING', 'NEW YORK');

insert into DEPT 
values(20, 'RESEARCH', 'DALLAS');

insert into DEPT 
values(30, 'SALES', 'CHICAGO'); 

insert into DEPT 
values(40, 'OPERATIONS', 'BOSTON');

/* Statement 4. 
TO_DATE 함수를 사용하여 EMP 행 삽입 */

insert into emp  
values(  
 7839, 'KING', 'PRESIDENT', null,  
 to_date('17-11-1981','dd-mm-yyyy'),  
 5000, null, 10  
);

insert into emp  
values(  
 7698, 'BLAKE', 'MANAGER', 7839,  
 to_date('1-5-1981','dd-mm-yyyy'),  
 2850, null, 30  
);


insert into emp  
values(  
 7782, 'CLARK', 'MANAGER', 7839,  
 to_date('9-6-1981','dd-mm-yyyy'),  
 2450, null, 10  
);

insert into emp  
values(  
 7566, 'JONES', 'MANAGER', 7839,  
 to_date('2-4-1981','dd-mm-yyyy'),  
 2975, null, 20  
);


insert into emp  
values(  
 7788, 'SCOTT', 'ANALYST', 7566,  
 to_date('13-JUL-87','dd-mm-rr') - 85,  
 3000, null, 20  
);

insert into emp  
values(  
 7902, 'FORD', 'ANALYST', 7566,  
 to_date('3-12-1981','dd-mm-yyyy'),  
 3000, null, 20  
);


insert into emp  
values(  
 7369, 'SMITH', 'CLERK', 7902,  
 to_date('17-12-1980','dd-mm-yyyy'),  
 800, null, 20  
);

insert into emp  
values(  
 7499, 'ALLEN', 'SALESMAN', 7698,  
 to_date('20-2-1981','dd-mm-yyyy'),  
 1600, 300, 30  
);

insert into emp  
values(  
 7521, 'WARD', 'SALESMAN', 7698,  
 to_date('22-2-1981','dd-mm-yyyy'),  
 1250, 500, 30  
);

insert into emp  
values(  
 7654, 'MARTIN', 'SALESMAN', 7698,  
 to_date('28-9-1981','dd-mm-yyyy'),  
 1250, 1400, 30  
);

insert into emp  
values(  
 7844, 'TURNER', 'SALESMAN', 7698,  
 to_date('8-9-1981','dd-mm-yyyy'),  
 1500, 0, 30  
);

insert into emp  
values(  
 7876, 'ADAMS', 'CLERK', 7788,  
 to_date('13-JUL-87', 'dd-mm-rr') - 51,  
 1100, null, 20  
);

insert into emp  
values(  
 7900, 'JAMES', 'CLERK', 7698,  
 to_date('3-12-1981','dd-mm-yyyy'),  
 950, null, 30  
);

insert into emp  
values(  
 7934, 'MILLER', 'CLERK', 7782,  
 to_date('23-1-1982','dd-mm-yyyy'),  
 1300, null, 10  
);

/* Statement 5. 
DEPT 테이블 DEPTNO의 기본 키와 EMP 테이블의 DEPTNO 외래 키를 기반으로 하는 DEPT와 EMP 테이블 간의 단순 조인 구문으로 입력된 값 확인 */

select ename, dname, job, empno, hiredate, loc  
from emp, dept  
where emp.deptno = dept.deptno  
order by ename; 

/* 아래와 같은 값이 조회됨  
ENAME	DNAME	JOB	EMPNO	HIREDATE	LOC
ADAMS	RESEARCH	CLERK	7876	23-MAY-87	DALLAS
ALLEN	SALES	SALESMAN	7499	20-FEB-81	CHICAGO
BLAKE	SALES	MANAGER	7698	01-MAY-81	CHICAGO
CLARK	ACCOUNTING	MANAGER	7782	09-JUN-81	NEW YORK
FORD	RESEARCH	ANALYST	7902	03-DEC-81	DALLAS
JAMES	SALES	CLERK	7900	03-DEC-81	CHICAGO
JONES	RESEARCH	MANAGER	7566	02-APR-81	DALLAS
KING	ACCOUNTING	PRESIDENT	7839	17-NOV-81	NEW YORK
MARTIN	SALES	SALESMAN	7654	28-SEP-81	CHICAGO
MILLER	ACCOUNTING	CLERK	7934	23-JAN-82	NEW YORK
SCOTT	RESEARCH	ANALYST	7788	19-APR-87	DALLAS
SMITH	RESEARCH	CLERK	7369	17-DEC-80	DALLAS
TURNER	SALES	SALESMAN	7844	08-SEP-81	CHICAGO
WARD	SALES	SALESMAN	7521	22-FEB-81	CHICAGO
*/

/* Statement 6. 
SQL 문의 GROUP BY 절은 그룹화되지 않은 열의 집계 함수를 허용합니다. 
조인은 내부 조인이므로 직원이 없는 부서는 표시되지 않습니다. */

select dname, count(*) count_of_employees
from dept, emp
where dept.deptno = emp.deptno
group by DNAME
order by 2 desc;

/* 그 외 테이블 생성 */

CREATE TABLE T1 (ID INTEGER);

INSERT INTO T1 VALUES (1);

CREATE TABLE T10 (ID INTEGER);

INSERT INTO T10 VALUES (1);
INSERT INTO T10 VALUES (2);
INSERT INTO T10 VALUES (3);
INSERT INTO T10 VALUES (4);
INSERT INTO T10 VALUES (5);
INSERT INTO T10 VALUES (6);
INSERT INTO T10 VALUES (7);
INSERT INTO T10 VALUES (8);
INSERT INTO T10 VALUES (9);
INSERT INTO T10 VALUES (10);


CREATE TABLE T100 (ID INTEGER);

insert into t100 values (1);
insert into t100 values (2);
insert into t100 values (3);
insert into t100 values (4);
insert into t100 values (5);
insert into t100 values (6);
insert into t100 values (7);
insert into t100 values (8);
insert into t100 values (9);
insert into t100 values (10);
insert into t100 values (11);
insert into t100 values (12);
insert into t100 values (13);
insert into t100 values (14);
insert into t100 values (15);
insert into t100 values (16);
insert into t100 values (17);
insert into t100 values (18);
insert into t100 values (19);
insert into t100 values (20);
insert into t100 values (21);
insert into t100 values (22);
insert into t100 values (23);
insert into t100 values (24);
insert into t100 values (25);
insert into t100 values (26);
insert into t100 values (27);
insert into t100 values (28);
insert into t100 values (29);
insert into t100 values (30);
insert into t100 values (31);
insert into t100 values (32);
insert into t100 values (33);
insert into t100 values (34);
insert into t100 values (35);
insert into t100 values (36);
insert into t100 values (37);
insert into t100 values (38);
insert into t100 values (39);
insert into t100 values (40);
insert into t100 values (41);
insert into t100 values (42);
insert into t100 values (43);
insert into t100 values (44);
insert into t100 values (45);
insert into t100 values (46);
insert into t100 values (47);
insert into t100 values (48);
insert into t100 values (49);
insert into t100 values (50);
insert into t100 values (51);
insert into t100 values (52);
insert into t100 values (53);
insert into t100 values (54);
insert into t100 values (55);
insert into t100 values (56);
insert into t100 values (57);
insert into t100 values (58);
insert into t100 values (59);
insert into t100 values (60);
insert into t100 values (61);
insert into t100 values (62);
insert into t100 values (63);
insert into t100 values (64);
insert into t100 values (65);
insert into t100 values (66);
insert into t100 values (67);
insert into t100 values (68);
insert into t100 values (69);
insert into t100 values (70);
insert into t100 values (71);
insert into t100 values (72);
insert into t100 values (73);
insert into t100 values (74);
insert into t100 values (75);
insert into t100 values (76);
insert into t100 values (77);
insert into t100 values (78);
insert into t100 values (79);
insert into t100 values (80);
insert into t100 values (81);
insert into t100 values (82);
insert into t100 values (83);
insert into t100 values (84);
insert into t100 values (85);
insert into t100 values (86);
insert into t100 values (87);
insert into t100 values (88);
insert into t100 values (89);
insert into t100 values (90);
insert into t100 values (91);
insert into t100 values (92);
insert into t100 values (93);
insert into t100 values (94);
insert into t100 values (95);
insert into t100 values (96);
insert into t100 values (97);
insert into t100 values (98);
insert into t100 values (99);
insert into t100 values (100);

근데 문제는 이걸 넣어도 오류가 난다.
keyword들이 사라졌거나 변경된 모양이다.

그래서 그냥 쿼리를 직접 찾아서 입력해볼 것이다.

Query로 하자

DBeaver는 GUI를 가지고 있다.
하지만 쿼리를 공부하는 이상 기본적인 것부터 손에 익히는 게 좋을 것 같다.
쿼리를 써서 data를 입력할 것이다.

테이블 생성

데이터베이스는 생성되어 있는 상태이므로 이 다음은 테이블을 생성하면 된다.
테이블을 생성하면서 칼럼(열)도 함께 생성할 것이다

  • dept 테이블 및 칼럼 생성
# dept라는 이름의 테이블을 생성한다
CREATE TABLE dept(
	# 테이블 내에 칼럼 생성
    # 칼럼명 자료형(크기)
	deptno int, 
	dname varchar(100),
	loc varchar(100),
    # pk 설정은
    # 제약조건키워드 제약조건이름 PRIMARY KEY (지정할 칼럼명)
	CONSTRAINT pk_dept PRIMARY KEY (deptno)
);

이 내용을 DBeaver의 스크립트 편집기에서 실행하면 테이블과 칼럼이 생성된다.
스크립트 편집기는 아래의 이미지 파일 참조하여 열기

아래의 화면처럼 쿼리를 입력하고 스크립트 실행버튼을 눌러 준다.

쿼리 스크립트가 실행되었음을 알려주는 상태창을 볼 수 있다.
물론 이 작업을 CLI에서 실행해도 문제 없다.

Database Navigator 탭에서 방금 생성했던 테이블의 정보가 나오지 않는다면 우클릭 -> 새로고침을 해주면 볼 수 있다.

이 방법을 계속 반복하여 예제 Database를 만들 것이다.

int()나 varchar() 내부의 괄호는 크기를 적는 것으로 알고 있는데, 책을 보여 실습하는 데에는 그냥 숫자를 넉넉하게 잡아 넣어줘도 문제가 없을 것이라 생각한다.

계속해본다.


  • emp 테이블 및 칼럼 생성
CREATE TABLE emp(
	empno int,
	ename varchar(100),
	job varchar(100),
	mgr int,
	hiredate date,
	sal int,
	comm int,
	deptno int,
	CONSTRAINT pk_emp PRIMARY KEY (empno),
	CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno)
);

잘 들어가는 것을 볼 수 있다.

이번에는 foreign key 즉 외부키를 하나 넣었다.

  • 'fk_deptno'라는 이름의 제약 조건을 만든다
  • 제약 조건 내용은 외부키를 생성하는 것이며 대상은 deptno칼럼이다
  • deptno칼럼은 dept테이블의 deptno를 참조한다

이렇게 하면 테이블 생성은 끝났다.
이제 실질적인 데이터(row)를 넣어줄 차례다.

data 생성

가장 위의 샘플 쿼리에서 'dept' 테이블 데이터의 생성과 관련된 insert문들은 잘 돌아간다.
따라서 그대로 복사하여 붙여넣어도 된다.

/* Statement 3. DEPT 테이블에 행을 삽입 */
/* 이 부분은 멀쩡하게 잘 돌아갑니다 */
insert into DEPT (DEPTNO, DNAME, LOC)
values(10, 'ACCOUNTING', 'NEW YORK');

insert into DEPT 
values(20, 'RESEARCH', 'DALLAS');

insert into DEPT 
values(30, 'SALES', 'CHICAGO'); 

insert into DEPT 
values(40, 'OPERATIONS', 'BOSTON');

하지만 'emp' 테이블의 데이터는 직접 입력해야할 것 같다.

단 14행이니 참아야 한다.

일단 기본 문법 확인하기.

# insert into 테이블명(칼럼1, 칼럼2, ...)
insert into dept(deptno, dname, loc)
# values(칼럼1에 넣을 값, 칼럼2에 넣을 값, ...)
values (10, 'ACCOUNTING', 'NEW YORK');
  • 데이터의 생성 또는 삽입에는 insert into를 사용한다
  • 칼럼명과 그에 넣을 데이터의 위치는 함수에서 일반 파라미터 위치를 맞추듯 정확하게 넣어준다

'emp' 테이블의 첫 줄만 보면...

empnoenamejobmgrhiredatesalcommdeptno
7369SMITHCLERK790217-DEC-200580020

이 한 줄만 쿼리로 입력해보겠다.

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(7369, 'SMITH', 'CLERK', 7902, 17-DEC-2005, 800, null, 20)

날짜 포맷이 문제가 되는 것 같다.
여러 방법을 찾아보고 쿼리를 넣어봤지만 오류에 막혔다.

그래서 'emp' 테이블은 DBeaver로 직접 넣기로 했다.
😩😖😣😥😭😰🥶


그림에 나온것처럼 순서대로 눌러주면 data를 입력할 수 있다.
하나씩 입력해주고 하단의 Script 버튼을 누르면 쿼리를 볼 수 있다.

내 쿼리의 문제점은 null 이었다.
공란으로 비워두기 위해서는 해당 칼럼을 제외한 나머지 칼럼에만 값을 주면 되는 것이었다.

# 내가 입력한 테이블 및 칼럼 조건
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(7369, 'SMITH', 'CLERK', 7902, '2005-12-17', 800, null, 20)

#정답
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7369, 'SMITH', 'CLERK', 7902, '2005-12-17', 800, 20)
  • 칼럼 comm의 값을 주지 않으려고 비워둔 부분에서 Error 발생
    + 칼럼에 빈 값을 주고 싶다면 대문자로 NULL 입력하면 들어간다!
  • 날짜의 경우 입력은 YYYY-MM-DD 형태로 insert
    이후 select 시 포맷팅을 통해 DD-MON-YYYY 형태로 출력할 수 있다

피벗 테이블이 남았는데 이건 아직 뭔지도 모르고 책을 읽다가 필요한 순간에 생성하고 정리하려 한다.

이로써 기본적인 실습 준비가 끝났다.
너무 오래 걸렸다.

+ EMP 테이블 데이터를 넣는 쿼리

  • DBeaver 쿼리 스크립트 입력에서 따온 것입니다
  • 직접 작성했으므로 오타가 있을 수 있습니다
    제보 바랍니다
INSERT INTO sqlcook.emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7369,'SMITH','CLERK',7902,'2005-12-17',800,NULL,20);


INSERT INTO sqlcook.emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7499,'ALLEN','SALESMAN',7698,'2006-02-20',1600,300,30);


INSERT INTO sqlcook.emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7844,'WARD','SALESMAN',7698,'2006-02-22',1250,500,30);


INSERT INTO sqlcook.emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7844,'JONES','MANAGER',7839,'2006-04-02',2875,NULL,30);


INSERT INTO sqlcook.emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7654,'MARTIN','SALESMAN',7698,'2006-09-28',1250,1400,30);


INSERT INTO sqlcook.emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7698,'BLAKE','MANAGER',7839,'2006-05-01',2850,NULL,30);


INSERT INTO sqlcook.emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7782,'CLARK','MANAGER',7698,'2006-06-09',2450,NULL,10);


INSERT INTO sqlcook.emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7788,'SCOTT','ANALYST',7566,'2007-12-09',3000,NULL,20);


INSERT INTO sqlcook.emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7839,'KING','PRESIDENT',NULL,'2006-11-17',5000,NULL,10);


INSERT INTO sqlcook.emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7844,'TURNER','SALESMAN',7698,'2006-09-08',1500,0,30);


INSERT INTO sqlcook.emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7876,'ADAMS','CLERK',7788,'2008-01-12',1100,NULL,20);


INSERT INTO sqlcook.emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7900,'JAMES','CLERK',7698,'2006-12-03',950,0,30);


INSERT INTO sqlcook.emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7902,'FORD','ANALYST',7566,'2006-12-03',3000,NULL,20);


INSERT INTO sqlcook.emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7934,'MILLER','CLERK',7782,'2007-01-23',1300,NULL,10);
profile
Chappie: 코딩 좋아하는 사람
Can do this all day
이전 포스트

SQL Cook

다음 포스트

SQL Cook: 레코드 검색

0개의 댓글