[DB] 1. 더미데이터 세팅

Oracle 더미데이터를 MySQL 문법으로 바꾸는 방법

1. 해당 파일 더블 클릭 후 메모장으로 열기

2. 전체 복사 후 Workbench에 붙여 넣기

❗

• GRANT 아래 부터 복사

• CONNECT SCOTT/TIGER 뒤에 ; 붙이기

3. Oracle 문법이기 때문에 MySQL 문법으로 변경

4. 완성된 MySQL 더미데이터 (scott)

create database scott;
use scott;

-- 사용자 생성 및 권한 부여 (MySQL에서는 CREATE USER와 GRANT 사용)
CREATE USER 'scott'@'%' IDENTIFIED BY 'tiger';
GRANT ALL PRIVILEGES ON *.* TO 'scott'@'%' WITH GRANT OPTION;

-- 테이블 삭제 (존재할 경우만 삭제)
DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS BONUS;
DROP TABLE IF EXISTS SALGRADE;

-- DEPT 테이블 생성
CREATE TABLE DEPT (
    DEPTNO INT(2) PRIMARY KEY,
    DNAME VARCHAR(14),
    LOC VARCHAR(13)
);

-- EMP 테이블 생성
CREATE TABLE EMP (
    EMPNO INT(4) PRIMARY KEY,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INT(4),
    HIREDATE DATE,
    SAL DECIMAL(7,2),
    COMM DECIMAL(7,2),
    DEPTNO INT(2),
    FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);

-- 데이터 삽입
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');

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, STR_TO_DATE('17-12-1980', '%d-%m-%Y'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, STR_TO_DATE('20-02-1981', '%d-%m-%Y'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, STR_TO_DATE('22-02-1981', '%d-%m-%Y'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, STR_TO_DATE('02-04-1981', '%d-%m-%Y'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, STR_TO_DATE('28-09-1981', '%d-%m-%Y'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, STR_TO_DATE('01-05-1981', '%d-%m-%Y'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, STR_TO_DATE('09-06-1981', '%d-%m-%Y'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, DATE_SUB(STR_TO_DATE('13-07-1987', '%d-%m-%Y'), INTERVAL 85 DAY), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, STR_TO_DATE('17-11-1981', '%d-%m-%Y'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, STR_TO_DATE('08-09-1981', '%d-%m-%Y'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, DATE_SUB(STR_TO_DATE('13-07-1987', '%d-%m-%Y'), INTERVAL 51 DAY), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, STR_TO_DATE('23-01-1982', '%d-%m-%Y'), 1300, NULL, 10);

-- BONUS 테이블 생성
CREATE TABLE BONUS (
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    SAL DECIMAL(7,2),
    COMM DECIMAL(7,2)
);

-- SALGRADE 테이블 생성
CREATE TABLE SALGRADE (
    GRADE INT,
    LOSAL INT,
    HISAL INT
);

-- SALGRADE 데이터 삽입
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);

COMMIT;

5. MySQL test_data 더미테이터

SET SESSION sql_mode='STRICT_TRANS_TABLES';

DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
  empno     INT PRIMARY KEY,
  ename     VARCHAR(20),
  job       VARCHAR(9),
  mgr       INT,
  hiredate  DATE,
  sal       DECIMAL(7,2),
  comm      DECIMAL(7,2),
  deptno    INT
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1982-02-22',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'1987-04-17',3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

CREATE INDEX idx_emp_deptno ON emp (deptno);

DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
  deptno   INT PRIMARY KEY, 
  dname    VARCHAR(14), 
  loc      VARCHAR(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');

DROP TABLE IF EXISTS professor;
CREATE TABLE professor (
  profno  INT PRIMARY KEY,
  name    VARCHAR(30) NOT NULL, 
  id      VARCHAR(15) NOT NULL,
  position VARCHAR(30) NOT NULL,
  pay     INT NOT NULL,
  hiredate  DATE NOT NULL,
  bonus   INT,
  deptno  INT,
  email   VARCHAR(50),
  hpage   VARCHAR(50)
);

INSERT INTO professor VALUES (1001,'조인형','captain','정교수',550,'1980-06-23',100,101,'captain@abc.net','http://www.abc.net');
INSERT INTO professor VALUES (1002,'박승곤','sweety','조교수',380,'1987-01-30',60,101,'sweety@abc.net','http://www.abc.net');
INSERT INTO professor VALUES (1003,'송도권','powerman','전임강사',270,'1998-03-22',NULL,101,'pman@power.com','http://www.power.com');

DROP TABLE IF EXISTS department;
CREATE TABLE department (
  deptno  INT PRIMARY KEY,
  dname   VARCHAR(30) NOT NULL,
  part    INT,
  build   VARCHAR(30)
);

INSERT INTO department VALUES (101,'컴퓨터공학과',100,'정보관');
INSERT INTO department VALUES (102,'멀티미디어공학과',100,'멀티미디어관');
INSERT INTO department VALUES (103,'소프트웨어공학과',100,'소프트웨어관');

DROP TABLE IF EXISTS student;
CREATE TABLE student (
  studno   INT PRIMARY KEY,
  name     VARCHAR(30) NOT NULL,
  id       VARCHAR(20) NOT NULL UNIQUE,
  grade    INT CHECK(grade BETWEEN 1 AND 6),
  jumin    CHAR(13) NOT NULL,
  birthday DATE,
  tel      VARCHAR(15),
  height   INT,
  weight   INT,
  deptno1  INT,
  deptno2  INT,
  profno   INT
);

INSERT INTO student VALUES (9411,'서진수','75true',4,'7510231901813','1975-10-23','0553812158',180,72,101,201,1001);
INSERT INTO student VALUES (9412,'서재수','pooh94',4,'7502241128467','1975-02-24','0514261700',172,64,102,NULL,2001);
INSERT INTO student VALUES (9413,'이미경','angel000',4,'7506152123648','1975-06-15','0532668947',168,52,103,203,3002);


DROP TABLE IF EXISTS emp2;
CREATE TABLE emp2 (
  empno     INT PRIMARY KEY,
  name      VARCHAR(30) NOT NULL,
  birthday  DATE,
  deptno    VARCHAR(6) NOT NULL,
  emp_type  VARCHAR(30),
  tel       VARCHAR(15),
  hobby     VARCHAR(30),
  pay       INT,
  position  VARCHAR(12),
  pempno    INT
);

INSERT INTO emp2 VALUES (19900101,'나사장','1964-01-25','0001','정규직','0542230001','음악감상',100000000,'대표이사',NULL);
INSERT INTO emp2 VALUES (19960101,'전부장','1973-03-22','1000','정규직','0262558000','독서',72000000,'부장',19900101);

COMMIT;