2024.02.22 - [데이터베이스/기타] - Mac에서 Oracle(XE) + Mysql + DBeaver 설치

Scott 사용하기

관리자 권한으로 sqlplus 접속

docker exec -it oracle sqlplus

SCOTT 계정 생성 및 권한 부여

CREATE USER scott identified by tiger;
-- 한줄씩 입력해주세요.
GRANT CONNECT, resource, dba to scott;

• 계정이름 : scott
• 비밀번호 : tiger(사용하고픈 비밀번호로 변경하세요)

테이블 생성과 데이터 입력 쿼리

scott 계정으로 다시 접속

아래의 쿼리를 실행한다.

https://github.com/mv/mvdba/blob/master/demo/demobld.sql

--
-- Copyright (c) Oracle Corporation 1988, 2000.  All Rights Reserved.
--
-- NAME
--   demobld.sql
--
-- DESCRIPTION
--   This script creates the SQL*Plus demonstration tables in the
--   current schema.  It should be STARTed by each user wishing to
--   access the tables.  To remove the tables use the demodrop.sql
--   script.
--
--  USAGE
--    From within SQL*Plus, enter:
--        START demobld.sql

SET TERMOUT ON
PROMPT Building demonstration tables.  Please wait.
SET TERMOUT OFF

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT 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');

CREATE TABLE BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM  NUMBER);

CREATE TABLE SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY
        (DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.

EXIT

참고로 DBeaver 에선 쿼리를 드래그하고, SQL 스크립트 실행 버튼을 누르면 여러 쿼리를 순차적으로 한번에 실행할 수 있다.

쿼리를 실행한 후 데이터가 잘 입력되었는지 확인해보자

select * from emp;

참고 : https://shanepark.tistory.com/400

HR 사용하기

관리자 권한으로 sqlplus 접속

계정 생성 및 권한 부여

CREATE USER HR IDENTIFIED BY tiger
GRANT RESOURCE, CONNECT TO HR;
grant create session, create table, create procedure to HR;
ALTER USER HR quota unlimited on USERS;

• 계정명 : HR
• 비밀번호 : tiger(변경할 분은 변경)

테이블 생성  및 데이터 입력 쿼리

HR 계정으로 접속

테이블 생성 쿼리

https://www.sqltutorial.org/wp-content/uploads/2020/04/oracle.txt

CREATE TABLE regions (
	region_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	region_name VARCHAR2 (25) DEFAULT NULL
);

CREATE TABLE countries (
	country_id CHAR (2) PRIMARY KEY,
	country_name VARCHAR2 (40) DEFAULT NULL,
	region_id NUMBER NOT NULL,
	FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE
);

CREATE TABLE locations (
	location_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	street_address VARCHAR2 (40) DEFAULT NULL,
	postal_code VARCHAR2 (12) DEFAULT NULL,
	city VARCHAR2 (30) NOT NULL,
	state_province VARCHAR2 (25) DEFAULT NULL,
	country_id CHAR (2) NOT NULL,
	FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE
);

CREATE TABLE jobs (
	job_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	job_title VARCHAR2 (35) NOT NULL,
	min_salary NUMBER (8, 2) DEFAULT NULL,
	max_salary NUMBER (8, 2) DEFAULT NULL
);

CREATE TABLE departments (
	department_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	department_name VARCHAR2 (30) NOT NULL,
	location_id NUMBER DEFAULT NULL,
	FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE
);

CREATE TABLE employees (
	employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	first_name VARCHAR2 (20) DEFAULT NULL,
	last_name VARCHAR2 (25) NOT NULL,
	email VARCHAR2 (100) NOT NULL,
	phone_number VARCHAR2 (20) DEFAULT NULL,
	hire_date DATE NOT NULL,
	job_id NUMBER NOT NULL,
	salary NUMBER (8, 2) NOT NULL,
	manager_id NUMBER DEFAULT NULL,
	department_id NUMBER DEFAULT NULL,
	FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE,
	FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE,
	FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);

CREATE TABLE dependents (
	dependent_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	first_name VARCHAR2 (50) NOT NULL,
	last_name VARCHAR2 (50) NOT NULL,
	relationship VARCHAR2 (25) NOT NULL,
	employee_id NUMBER NOT NULL,
	FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE
);

데이터 입력 쿼리

https://www.sqltutorial.org/wp-content/uploads/2020/04/oracle-data.txt

/*Data for the table regions*/
INSERT INTO regions(region_id,region_name) VALUES (1,'Europe');
INSERT INTO regions(region_id,region_name) VALUES (2,'Americas');
INSERT INTO regions(region_id,region_name) VALUES (3,'Asia');
INSERT INTO regions(region_id,region_name) VALUES (4,'Middle East and Africa');
 
 
/*Data for the table countries */
INSERT INTO countries(country_id,country_name,region_id) VALUES ('AR','Argentina',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('AU','Australia',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('BE','Belgium',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('BR','Brazil',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('CA','Canada',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('CH','Switzerland',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('CN','China',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('DE','Germany',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('DK','Denmark',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('EG','Egypt',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('FR','France',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('HK','HongKong',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('IL','Israel',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('IN','India',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('IT','Italy',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('JP','Japan',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('KW','Kuwait',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('MX','Mexico',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('NG','Nigeria',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('NL','Netherlands',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('SG','Singapore',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('UK','United Kingdom',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('US','United States of America',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('ZM','Zambia',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('ZW','Zimbabwe',4);
 
/*Data for the table locations */
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1500,'2011 Interiors Blvd','99236','South San Francisco','California','US');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1700,'2004 Charade Rd','98199','Seattle','Washington','US');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2400,'8204 Arthur St',NULL,'London',NULL,'UK');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE');
 
 
/*Data for the table jobs */
 
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (1,'Public Accountant',4200.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (2,'Accounting Manager',8200.00,16000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (3,'Administration Assistant',3000.00,6000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (4,'President',20000.00,40000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (5,'Administration Vice President',15000.00,30000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (6,'Accountant',4200.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (7,'Finance Manager',8200.00,16000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (8,'Human Resources Representative',4000.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (9,'Programmer',4000.00,10000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (10,'Marketing Manager',9000.00,15000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (11,'Marketing Representative',4000.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (12,'Public Relations Representative',4500.00,10500.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (13,'Purchasing Clerk',2500.00,5500.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (14,'Purchasing Manager',8000.00,15000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (15,'Sales Manager',10000.00,20000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (16,'Sales Representative',6000.00,12000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (17,'Shipping Clerk',2500.00,5500.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (18,'Stock Clerk',2000.00,5000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (19,'Stock Manager',5500.00,8500.00);
 
 
/*Data for the table departments */
 
INSERT INTO departments(department_id,department_name,location_id) VALUES (1,'Administration',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (2,'Marketing',1800);
INSERT INTO departments(department_id,department_name,location_id) VALUES (3,'Purchasing',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (4,'Human Resources',2400);
INSERT INTO departments(department_id,department_name,location_id) VALUES (5,'Shipping',1500);
INSERT INTO departments(department_id,department_name,location_id) VALUES (6,'IT',1400);
INSERT INTO departments(department_id,department_name,location_id) VALUES (7,'Public Relations',2700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (8,'Sales',2500);
INSERT INTO departments(department_id,department_name,location_id) VALUES (9,'Executive',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (10,'Finance',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (11,'Accounting',1700);
 
 
 
/*Data for the table employees */
 
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (100,'Steven','King','steven.king@sqltutorial.org','515.123.4567',DATE '1987-06-17',4,24000.00,NULL,9);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (101,'Neena','Kochhar','neena.kochhar@sqltutorial.org','515.123.4568',DATE '1989-09-21',5,17000.00,100,9);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (102,'Lex','De Haan','lex.de haan@sqltutorial.org','515.123.4569',DATE '1993-01-13',5,17000.00,100,9);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (103,'Alexander','Hunold','alexander.hunold@sqltutorial.org','590.423.4567',DATE '1990-01-03',9,9000.00,102,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (104,'Bruce','Ernst','bruce.ernst@sqltutorial.org','590.423.4568',DATE '1991-05-21',9,6000.00,103,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (105,'David','Austin','david.austin@sqltutorial.org','590.423.4569',DATE '1997-06-25',9,4800.00,103,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (106,'Valli','Pataballa','valli.pataballa@sqltutorial.org','590.423.4560',DATE '1998-02-05',9,4800.00,103,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (107,'Diana','Lorentz','diana.lorentz@sqltutorial.org','590.423.5567',DATE '1999-02-07',9,4200.00,103,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (108,'Nancy','Greenberg','nancy.greenberg@sqltutorial.org','515.124.4569',DATE '1994-08-17',7,12000.00,101,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (109,'Daniel','Faviet','daniel.faviet@sqltutorial.org','515.124.4169',DATE '1994-08-16',6,9000.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (110,'John','Chen','john.chen@sqltutorial.org','515.124.4269',DATE '1997-09-28',6,8200.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (111,'Ismael','Sciarra','ismael.sciarra@sqltutorial.org','515.124.4369',DATE '1997-09-30',6,7700.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (112,'Jose Manuel','Urman','jose manuel.urman@sqltutorial.org','515.124.4469',DATE '1998-03-07',6,7800.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (113,'Luis','Popp','luis.popp@sqltutorial.org','515.124.4567',DATE '1999-12-07',6,6900.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (114,'Den','Raphaely','den.raphaely@sqltutorial.org','515.127.4561',DATE '1994-12-07',14,11000.00,100,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (115,'Alexander','Khoo','alexander.khoo@sqltutorial.org','515.127.4562',DATE '1995-05-18',13,3100.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (116,'Shelli','Baida','shelli.baida@sqltutorial.org','515.127.4563',DATE '1997-12-24',13,2900.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (117,'Sigal','Tobias','sigal.tobias@sqltutorial.org','515.127.4564',DATE '1997-07-24',13,2800.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (118,'Guy','Himuro','guy.himuro@sqltutorial.org','515.127.4565',DATE '1998-11-15',13,2600.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (119,'Karen','Colmenares','karen.colmenares@sqltutorial.org','515.127.4566',DATE '1999-08-10',13,2500.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (120,'Matthew','Weiss','matthew.weiss@sqltutorial.org','650.123.1234',DATE '1996-07-18',19,8000.00,100,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (121,'Adam','Fripp','adam.fripp@sqltutorial.org','650.123.2234',DATE '1997-04-10',19,8200.00,100,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (122,'Payam','Kaufling','payam.kaufling@sqltutorial.org','650.123.3234',DATE '1995-05-01',19,7900.00,100,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (123,'Shanta','Vollman','shanta.vollman@sqltutorial.org','650.123.4234',DATE '1997-10-10',19,6500.00,100,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (126,'Irene','Mikkilineni','irene.mikkilineni@sqltutorial.org','650.124.1224',DATE '1998-09-28',18,2700.00,120,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (145,'John','Russell','john.russell@sqltutorial.org',NULL,DATE '1996-10-01',15,14000.00,100,8);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (146,'Karen','Partners','karen.partners@sqltutorial.org',NULL,DATE '1997-01-05',15,13500.00,100,8);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (176,'Jonathon','Taylor','jonathon.taylor@sqltutorial.org',NULL,DATE '1998-03-24',16,8600.00,100,8);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (177,'Jack','Livingston','jack.livingston@sqltutorial.org',NULL,DATE '1998-04-23',16,8400.00,100,8);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (178,'Kimberely','Grant','kimberely.grant@sqltutorial.org',NULL,DATE '1999-05-24',16,7000.00,100,8);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (179,'Charles','Johnson','charles.johnson@sqltutorial.org',NULL,DATE '2000-01-04',16,6200.00,100,8);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (192,'Sarah','Bell','sarah.bell@sqltutorial.org','650.501.1876',DATE '1996-02-04',17,4000.00,123,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (193,'Britney','Everett','britney.everett@sqltutorial.org','650.501.2876',DATE '1997-03-03',17,3900.00,123,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (200,'Jennifer','Whalen','jennifer.whalen@sqltutorial.org','515.123.4444',DATE '1987-09-17',3,4400.00,101,1);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (201,'Michael','Hartstein','michael.hartstein@sqltutorial.org','515.123.5555',DATE '1996-02-17',10,13000.00,100,2);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (202,'Pat','Fay','pat.fay@sqltutorial.org','603.123.6666',DATE '1997-08-17',11,6000.00,201,2);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (203,'Susan','Mavris','susan.mavris@sqltutorial.org','515.123.7777',DATE '1994-06-07',8,6500.00,101,4);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (204,'Hermann','Baer','hermann.baer@sqltutorial.org','515.123.8888',DATE '1994-06-07',12,10000.00,101,7);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (205,'Shelley','Higgins','shelley.higgins@sqltutorial.org','515.123.8080',DATE '1994-06-07',2,12000.00,101,11);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (206,'William','Gietz','william.gietz@sqltutorial.org','515.123.8181',DATE '1994-06-07',1,8300.00,205,11);
 
 
/*Data for the table dependents */
 
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (1,'Penelope','Gietz','Child',206);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (2,'Nick','Higgins','Child',205);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (3,'Ed','Whalen','Child',200);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (4,'Jennifer','King','Child',100);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (5,'Johnny','Kochhar','Child',101);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (6,'Bette','De Haan','Child',102);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (7,'Grace','Faviet','Child',109);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (8,'Matthew','Chen','Child',110);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (9,'Joe','Sciarra','Child',111);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (10,'Christian','Urman','Child',112);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (11,'Zero','Popp','Child',113);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (12,'Karl','Greenberg','Child',108);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (13,'Uma','Mavris','Child',203);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (14,'Vivien','Hunold','Child',103);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (15,'Cuba','Ernst','Child',104);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (16,'Fred','Austin','Child',105);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (17,'Helen','Pataballa','Child',106);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (18,'Dan','Lorentz','Child',107);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (19,'Bob','Hartstein','Child',201);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (20,'Lucille','Fay','Child',202);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (21,'Kirsten','Baer','Child',204);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (22,'Elvis','Khoo','Child',115);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (23,'Sandra','Baida','Child',116);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (24,'Cameron','Tobias','Child',117);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (25,'Kevin','Himuro','Child',118);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (26,'Rip','Colmenares','Child',119);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (27,'Julia','Raphaely','Child',114);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (28,'Woody','Russell','Child',145);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (29,'Alec','Partners','Child',146);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (30,'Sandra','Taylor','Child',176);

쿼리를 정상적으로 모두 입력했다면 아래의 명령어로 제대로 테이블이 생성되고 입력되었는지 테스트 해볼 수 있다.

SELECT e.FIRST_NAME , e.LAST_NAME, d.DEPARTMENT_NAME, m.LAST_NAME 
FROM EMPLOYEES e 
	JOIN DEPARTMENTS d ON e.DEPARTMENT_ID =d.DEPARTMENT_ID 
	LEFT OUTER JOIN EMPLOYEES m ON e.MANAGER_ID = m.EMPLOYEE_ID ;

참고 : https://velog.io/@serringg/m1-oracle-%EC%97%B0%EA%B2%B0%ED%95%98%EA%B8%B0

DBeaver에서 여러 쿼리 한번에 실행하기

참고로 DBeaver 에선 쿼리를 드래그하고, SQL 스크립트 실행 버튼을 누르면 여러 쿼리를 순차적으로 한번에 실행할 수 있다.

참고 : https://wakestand.tistory.com/1048

문제상황

맥OS에서 Oracle 데이터베이스를 사용하려는 과정에서 아래의 명령어를 입력하였다.

colima start --arch x86_64 --cpu 4 --memory 8

위 명령어를 실행하면 FATA[0022] error starting vm: error at 'starting': exit status 1 오류가 발생하였다.

해결

폭풍 구글링 결과 아래의 링크에서 해답을 찾을 수 있었다.

https://stackoverflow.com/questions/77195672/keep-getting-error-runing-colima-fata0001-error-starting-vm-error-at-startin

colima를 삭제하고 다시 설치했더니 정상적으로 되었다.

colima delete
colima start --arch x86_64 --cpu 4 --memory 8

아래의 모든 명령어은 Homebrew가 설치되어있다고 가정한다.

DBeaver 설치

터미널에서 아래의 명령어를 입력하면 특별한 오류가 나지 않는이상 설치는 완료된다.

brew install --cask dbeaver-community

이후 앱을 실행하면 된다.

Oracle(XE)

Oracle XE

Oracle Database Express Edition (Oracle XE)은 Oracle Corporation에서 제공하는 무료, 경량 버전의 Oracle 데이터베이스이다. 이 버전은 개발자, 교육용으로, 그리고 소규모 배포를 위해 설계되었다.
Oracle XE는 기능적으로는 전체 Oracle 데이터베이스 시스템의 서브셋을 제공하지만, 많은 기본적인 기능과 툴을 포함하고 있어 데이터베이스 학습이나 개발, 테스트 환경 구축에 이상적이다.

초기 세팅

https://www.youtube.com/watch?v=yCQD_rZEzZs&ab_channel=%EB%A6%AC%EC%96%BC%EB%8D%B0%EB%B8%8C%E2%B8%B0%EB%9F%AC%EB%8B%9D

위 영상을 참고하였음

터미널에서 아래의 명령어를 입력한다.

먼저 오라클은 맥 os를 지원하지 않기 때문에 도커를 통해서 실행할 수 있기 때문에 도커를 설치한다.

brew install docker

Colima은 MacOS 사용자를 위한 Docker와 Kubernetes 환경을 간편하게 설정하고 관리할 수 있는 오픈 소스 도구이다.

도커를 원활히 실행시킬 수 있도록 콜리마 또한 설치한다.

brew install colima

colima start --arch x86_64 --cpu 4 --memory 8 명령어는 Colima를 사용하여 x86_64 아키텍처를 지원하는 컨테이너 환경을 시작하는 데 사용된다.

여기서 --cpu 4는 컨테이너에 4개의 CPU 코어를 할당하고, --memory 8은 8GB의 메모리를 할당하라는 것을 의미한다.

colima start --arch x86_64 --cpu 4 --memory 8

아래의 명령어에서 가장 중요한 부분은 -e ORACLE_PASSWORD=비밀번호 이다.

데이터베이스의 SYS, SYSTEM, PDBADMIN 계정에 대한 비밀번호를 "비밀번호"로 설정한다.

당연히 이 부분은 사용자가 자신이 원하는 패스워드로 변경해서 명령어를 실행해야 한다.

docker run -d --name oracle-xe -p 8080:8080 -p 1521:1521 -e ORACLE_PASSWORD=비밀번호 -v ~/data/oracle:/opt/oracle/oradata gvenzl/oracle-xe

이후 sysdba 권한으로 접속한다.

sqlplus 명령어를 실행할 수 없다면
docker exec -it oracle-xe sqlplus 명령어를 사용하면 된다.

sqlplus / as sysdba

orcl이라는 사용자를 생성하고 비밀번호를 java로 설정한다.(원하는 아이디와 비밀번호로 바꿔도된다.)

CREATE USER orcl IDENTIFIED BY java;

권한 부여

GRANT CONNECT, RESOURCE TO orcl;

사용 방법

실행

콜리마를 실행한다.

이미 이 명령어를 실행했다면 이 부분은 생략해도 된다.

colima start --arch x86_64 --cpu 4 --memory 8

도커가 oracle-xe 컨테이너를 실행하는 명령어를 입력한다.

docker start oracle-xe

docker ps 명령어를 이용해 oracle-xe 컨테이너가 있는지 확인할 수 있다.

작업

이제 실행은 되고 있는 상태이다.

따라서 실행 - 작업 - 종료 순서에서 이 부분인 작업 부분은 사용자에 따라 다르기 때문에 무시해도 된다.

DBeaver에서 위에서 설정한 정보를 바탕으로 접속하려면 아래와 같다.

Database : XE

Username : orcl

Password : java

sqlplus 명령어를 사용하려면 아래의 명령어를 입력하면 된다.

docker exec -it oracle-xe sqlplus

sqlplus 명령어를 모두 끝냈다면 exit를 입력해서 벗어나면 된다.

exit

종료

실행 하는 순서를 거꾸로하면 종료하는 순서가 된다.

docker stop oracle-xe

colima stop

MySQL(8.X)

초기 설정

mysql 설치

brew install mysql

mysql 서버 시작

mysql.server start

보안 설정 스크립트

mysql_secure_installation

보안 설정 스크립트를 실행하면 아래와 같이 어떻게 보안을 설정할지 물어본다.

이 부분은 각자 환경에 맞게 설정하면 된다.

Would you like to setup VALIDATE PASSWORD plugin?
설치 과정에서 설정하지 않았다면, MySQL 루트 계정의 비밀번호를 설정하라는 요청을 받게 된다. 이미 설정된 비밀번호가 있다면, 더 강력한 비밀번호로 변경할 것인지 묻는다.

Remove anonymous users?
익명 사용자 제거: MySQL은 기본적으로 '익명 사용자'를 허용하는데, 이는 누구나 사용자 이름 없이 MySQL 서버에 접근할 수 있음을 의미한다. mysql_secure_installation 스크립트는 이러한 익명 사용자를 제거하도록 권장한다.

Disallow root login remotely?
루트 사용자 원격 접속 제한: 기본적으로, 루트 사용자는 어느 네트워크에서나 MySQL 서버에 접근할 수 있다. 보안을 위해, 이 스크립트는 루트 사용자의 원격 접속을 금지하고 로컬에서만 접근할 수 있도록 설정을 변경할 것을 권장한다.
->localhost 외에 다른 아이피로는 접속을 하지 못하게 하는 설정

Remove test database and access to it?
test 데이터베이스 삭제 여부: 보통 y를 선택

Reload privilege tables now?
권한 테이블 즉시 적용: 변경사항을 적용하고 권한 테이블을 새로고침하여, 모든 보안 변경사항이 바로 적용되도록 한다. 변경 사항을 즉시 적용하기 위해 보통 Y를 선택

사용 방법

MySQL 서버 시작 명령(이미 시작되어있다면 생략)

mysql.server start

루트 계정으로 로그인 명령

mysql -uroot -p

설정한 비밀번호를 입력해야 접속 가능

MySQL 서버 종료 명령

mysql.server stop

DBeaver 접속 설정

mysql_secure_installation에서 설정한 루트 비밀번호를 입력하여 접속

DBeaver 에서 MySQL을 연결하려고 하였으나 Public key retrieval is not allowed 오류가 발생하였다.

이 오류는 데이터베이스 서버로부터 공개 키를 안전하게 검색하는 것이 기본적으로 허용되지 않을 때 발생하는데, 주로 MySQL 8.0 이상 버전에서 새로운 인증 방식을 사용할 때 나타난다고 한다.

해결 방법은 간단했다.

• 이미 설정된 연결 목록에서 해당 연결을 우클릭하고 "Edit Connection"을 선택

1. "Connection Settings" 창에서 "Driver Properties" 탭으로 이동
2. 여기서 allowPublicKeyRetrieval 속성을 찾아 값을 true로 설정
3. 모든 변경 사항을 적용한 후, "OK" 또는 "Apply" 버튼을 클릭하여 연결 설정을 저장

이제 DBeaver에서 해당 데이터베이스 연결을 다시 시도하면, "Public key retrieval is not allowed" 오류 없이 연결이 성공적으로 이루어져야 한다.

H2 데이터베이스

H2 데이터베이스는 자바 기반의 경량화된 관계형 데이터베이스 관리 시스템(RDBMS)이다.

메모리 내에서 실행될 수 있어서 개발이나 테스트 용도로 많이 사용되곤 한다.

그래서 애플리케이션 개발 시 데이터베이스 서버를 별도로 구성하지 않고도 손쉽게 데이터베이스 환경을 구축할 수 있어서 매우 편리하다.

H2 데이터베이스의 주요 특징은 아래와 같다.

• H2는 자바 애플리케이션에 내장될 수 있어서 복잡한 설치 과정 없이 데이터베이스를 바로 사용할 수 있다.
• 메모리에서 직접 실행되므로 빠른 데이터 액세스와 테스트가 가능하다. 개발 중에는 메모리 데이터베이스를 사용하고, 실제 운영 환경에서는 디스크 기반 저장소를 사용하는 식으로 활용할 수 있다.
• H2는 표준 SQL을 많이 지원한다. 복잡한 쿼리나 함수, 트리거 같은 고급 데이터베이스 기능도 사용할 수 있다.
• H2 데이터베이스는 웹 기반의 콘솔을 제공해서, 웹 브라우저를 통해 데이터베이스를 쉽게 관리하고 SQL 쿼리를 실행할 수 있다.
• 오픈 소스이기 때문에 무료로 사용할 수 있고, 필요에 따라 코드를 수정하거나 확장하는 것도 가능하다.

이러한 특성 때문에, H2는 개발 초기 단계나 단위 테스트를 위한 데이터베이스로 아주 적합하다.

그러나 메모리 데이터베이스의 특성상 데이터가 영구적이지 않기 때문에, 중요한 데이터를 관리할 때는 디스크 기반의 저장 방식을 사용하는 것이 좋다.

H2 데이터베이스 설치

h2 데이터베이스는 아래의 링크에서 다운로드 받을 수 있다.

https://www.h2database.com/html/download.html

맥은 Platform-Independent Zip를 다운로드 하고 아무곳이나 zip을 풀어준다.

버전에 맞게 설치하면 되고, 나는 스프링 부트에서 제공되는 버전이 2.2.224라서 이와 맞는 버전을 다운로드 하였다.

다운로드 받은 폴더의 구조는 아래의 그림과 같다.

이후 터미널을 열고, 다운로드 받은 폴더로 이동한다.

cd 다운로드 받은 폴더 경로

이동했다면 아래의 명령어로 권한을 허락해준다.

chmod 755 ./bin/h2.sh

아래의 명령어로 h2.sh를 실행한다.

./bin/h2.sh

명령어를 실행시키면 웹 페이지가 하나 뜰텐데, 주소창에서 아이피:8082~~가 아니라

localhost:8082~~가 되도록 수정한다.

localhost 외에 다른 것을 수정하면 권한 문제가 생길 수도 있다.

이런 창에서 아래와 같이 수정한다.