## User 생성
CREATE USER '[UserID]'@'[Host]' IDENTIFIED BY '[Password]';
ex )
CREATE USER 'kopo'@'%' IDENTIFIED BY 'kopo'; ## '%'는 모든 IP 허용
CREATE USER 'kopo2'@'localhost' IDENTIFIED BY '1234';
## 권한 부여
GRANT [type_of_permission] ON [database_name.table_name] TO '[username]'@'[host]';
ex )
GRANT all privileges on *.* TO 'kopo'@'%';
## 권한 뺐기
REVOKE [type_of_permission] ON [database_name.table_name] FROM '[username]'@'[host]';
## User 삭제
DROP USER '[username]'@'[localhost]';
## User 조회
SELECT USER FROM mysql.user;
## User, Host 조회
SELECT USER,HOST FROM mysql.user;
## 권한 설정 DB 서버 재시작 없이 적용 (우분투에서 source /etc/profile 하던 것과 같은거다)
FLUSH PRIVILEGES;
## 자바 설치
sudo apt install openjdk-11-jre-headless
## 자바 설치 경로 확인
whereis java
java: /usr/bin/java /usr/share/java /usr/share/man/man1/java.1.gz
## javac 확인
whereis javac
## java만 설치되고 javac가 없어서 다시 설치
sudo apt install openjdk-11-jre-headless
## 자바, javac 설치경로 확인
whereis javac
javac: /usr/bin/javac /usr/share/man/man1/javac.1.gz
## 경로로 이동
cd /usr/bin
## j로 시작하는 모든 것을 자세히 보기
ll j*
링크된 것으로 실제 경로는 /etc/alternatives/java에 있는 것을 확인할 수 있다.
또 링크가 되어있다.. /etc/alternatives에서 링크된 원본 따라가보자.
## 자바 설치 경로로 이동
cd /usr/lib/jvm/java-11-openjdk-amd64/
## bin 디렉토리 내 java, javac 확인
cd bin
ll
## 모든 사용자에게 적용되도록 시스템 환경변수 설정
vi /etc/profile
(링크된 경로 기준)
export JAVA_HOME=/usr/bin/java
export PATH=$PATH:$JAVA_HOME/bin/
export CALSS_PATH=$CLASS_PATH:$JAVA_HOME/lib
(실제 경로 기준)
export JAVA_HOME=/usr/lib/jvm/java-11-openjdk-amd64
export PATH=$PATH:$JAVA_HOME/bin/
export CALSS_PATH=$CLASS_PATH:$JAVA_HOME/lib
둘 중 하나 추가하기
## 설정 적용하기
source /etc/profile
또는
sudo su - $USER
3. 개발환경 : Docker Engine 19.03.12, build 48a66213fe Docker-compose 1.26.0, built d4451659 (Host OS : Ubuntu 18.04.3 LTS)
Dokcer image : Ubuntu:20.04 (오리지널 우분투에서 18.04 다음 LTS 버전이 20.04라서)
## curl 설치
sudo apt update
sudo apt install curl
## dockder engine 설치
curl -s https://get.docker.com | sudo sh
docker -v
## docker-compose 설치
sudo curl -L "https://github.com/docker/compose/releases/download/1.26.0/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose
docker-compose -v
## 일반 사용자 도커 그룹에 넣기
sudo usermod -aG docker $USER ## 현재 접속 중인 사용자를 도커 그룹에 넣기
sudo su - $USER ## 적용을 위해 현재 사용자 재로그인 하기
## 우분투 이미지 가져오기
docker pull ubuntu:20.04
## 우분투 컨테이너 생성
docker run -d -it --name ub ubuntu:20.04 /bin/bash
주의!!! 여기서부터는 우분투 내에서 하는게 아니고 도커 내 우분투 컨테이너에서 해야한다!!!
Java 설치
## curl 설치
apt update
apt install curl
## 자바 다운로드 (JDK 11에는 더이상 JRE가 포함되지 않는다. 유료화... JDK 8을 받는다.)
cd ~
curl -OL "https://download.oracle.com/otn/java/jdk/8u251-b08/3d5a2bb8f8d4428bbe94aed7ec7ae784/jdk-8u251-linux-x64.tar.gz?AuthParam=1593508166_a0ec4e4730475bcc7e56dcfe303cb284"
## 자바 압축 해제 및 이동
tar zxf jdk-8u251-linux-x64.tar.gz\?AuthParam\=1593508166_a0ec4e4730475bcc7e56dcfe303cb284
mv jdk1.8.0_251 /usr/local
cd /usr/local
## 자바 링크 생성
ln -s ./jdk1.8.0_251 java
## 자바 위치 확인
whereis java
java: /usr/local/java
## 환경변수 설정
cd /root
find .bash*
.bash_history
.bashrc
## vi 에디터로 편집
apt install vim
vi profile
## 환경변수 설정
vi /root/.bashrc
(링크된 경로 기준)
export JAVA_HOME=/usr/local/java
export PATH=$PATH:$JAVA_HOME/bin/ ## 처음 추가할 때 $PATH 이 기본 경로를 반드시 넣어줘야한다. CLASS_PATH도 마찬가지...
export CALSS_PATH=$CLASS_PATH:$JAVA_HOME/lib
(실제 경로 기준)
export JAVA_HOME=/usr/local/jdk1.8.0_251
export PATH=$PATH:$JAVA_HOME/bin/
export CALSS_PATH=$CLASS_PATH:$JAVA_HOME/lib
둘 중 하나 추가하기
도커 리눅스는 도커가 올라가고 내려갈 때 부팅이 되는게 아니고 중단 되었다 재개되는거라 /etc/profile에 넣으면 환경변수 적용이 안 된다. 따라서 계정이 로그인 될 때 불러올 수 있도록 /root/.bashrc에 환경변수를 설정한다.
또는 Dockerfile을 만들어서 도커 start시 환경변수를 자동 적용되도록 만든다.
## 설정 적용하기
source /root/.bashrc
또는
sudo su - $USER
## 확인하기
javac -version
## MariaDB 시작
service mysql start
mysql
MariaDB [(none)]>
## User 생성
CREATE USER 'kopo'@'%' IDENTIFIED BY '1234';
## 권한 부여
GRANT all privileges ON *.* TO 'kopo'@'%';
## USER 조회
SELECT USER,HOST FROM mysql.user;
## 권한 설정 바로 적용
FLUSH PRIVILEGES;
## 나가기
exit
## MariaDB 종료
service mysql stop
Tomcat 설치 (jdk 설치 이후에 진행)
## Tomcat 설치
cd ~
curl -OL "http://apache.tt.co.kr/tomcat/tomcat-9/v9.0.38/bin/apache-tomcat-9.0.38.tar.gz"
또는
curl -OL "http://mirror.navercorp.com/apache/tomcat/tomcat-9/v9.0.36/bin/apache-tomcat-9.0.36.tar.gz"
## 압축 풀기
tar zxf apache-tomcat-9.0.36.tar.gz
## 톰캣 이동
mv apache-tomcat-9.0.36 /usr/local/
## 톰캣 링크 설정
ln -s ./apache-tomcat-9.0.36 tomcat
만약, tar가 아닌 zip으로 받았을 경우... (왠만하면 tar로 받자...)
## unzip 설치 (왠만하면 zip말고 tar로 받자... tar zxf 는 따로 설치 안 하고 풀린다.)
sudo apt install unzip
## zip 풀기
unzip apache-tomcat-9.0.36.zip
tar zxf apache-tomcat-9.0.36.tar.gz
만약, 인터넷이 되지 않는 환경이라 우분투에서 도커 내 우분투로 파일을 복사해야 하는 경우 docekr cp 명령어를 사용한다. (단, 이렇게 하기보다 컨테이너를 외부 인터넷이 되는 환경에서 만든 다음 이미지로 만들어 그 이미지를 통째로 가져오는게 더 낫다. 어차피 외부에서 만들어 온 이미지나 여기서 힘들게 만든 이미지나 같으니까...)
## 도커 컨테이너 리눅스 내부로 옮기기(docker cp [파일명] [컨테이너이름][:내부경로]
docker cp apache-tomcat-9.0.36.zip ub:/usr/local
## 도커 리눅스 컨테이너 내부로 접속
docker attach ub
## 환경변수 vi 에디터로 편집
vi /root/.bashrc
export CATALINA_HOME=/usr/local/tomcat
export PATH=$PATH:$CATALINA_HOME/bin
export CALSS_PATH=$CLASS_PATH:$JAVA_HOME/lib/tools.jar:$JAVA_HOME/jre/lib/ext:$CATALINA_HOME/lib/jsp-api.jar:$CATALINA_HOME/lib/servlet-api.jar
export LC_ALL=C.UTF-8
을 추가한다. PATH랑 CLASS_PATH는 뒤에 : 으로 추가하면 되니까 이렇게 하면 된다.
export JAVA_HOME=/usr/local/java
export CATALINA_HOME=/usr/local/tomcat
export PATH=$PATH:$JAVA_HOME/bin/:$CATALINA_HOME/bin
export CALSS_PATH=$CLASS_PATH:$JAVA_HOME/lib/tools.jar:$JAVA_HOME/jre/lib/ext:$CATALINA_HOME/lib/jsp-api.jar:$CATALINA_HOME/lib/servlet-api.jar
export LC_ALL=C.UTF-8
## 설정 적용하기
source /root/.bashrc
또는
sudo su - $USER
## 톰캣 실행 (기본 포트 8080)
$CATALINA_HOME/bin/startup.sh
## 톰캣 정지
$CATALINA_HOME/bin/shutdown.sh
컨테이너 -> 이미지 (컨테이너가 실행중이라면 정지시키고 진행한다.)
## 도커허브 계정으로 로그인
docker login
Login with your Docker ID to push and pull images from Docker Hub. If you don't have a Docker ID, head over to https://hub.docker.com to create one.
Username:
Password:
WARNING! Your password will be stored unencrypted in /root/.docker/config.json.
Configure a credential helper to remove this warning. See
https://docs.docker.com/engine/reference/commandline/login/#credentials-store
Login Succeeded
## 컨테이너 커밋
docker commit [컨테이너이름] [이미지이름][:버전명]
docker commit ub myubuntu:1.0.0
## 컨테이너 태그 (:[버전명] -> 도커에서 이것을 'TAG'라 한다. 하나의 IMAGE ID에 여러개의 태그를 달 수 있다.)
## Usage: docker tag SOURCE_IMAGE[:TAG] TARGET_IMAGE[:TAG] ## TARGET_IMAGE 이름과 [:TAG] 버전명은 원본과 아무런 연관이 필요 없다.
## Create a tag TARGET_IMAGE that refers to SOURCE_IMAGE
docker tag myubuntu:1.0.0 test:2.0.0
docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
myubuntu 1.0.0 64a7aa9f814b About an hour ago 1.13GB
test 2.0.0 64a7aa9f814b About an hour ago 1.13GB
ubuntu 20.04 74435f89ab78 2 weeks ago 73.9MB
## ubuntu:20.04는 도커 허브에서 pull 해서 받은 최초의 이미지다. (docker pull ubuntu:20.04)
## ubuntu:20.04를 ub라는 컨테이너로 만들어 여러 앱을 설치했고, (docker run -d -it --name ub ubuntu:20.04 /bin/bash)
## ub라는 컨테이너를 myubuntu:1.0.0 이라는 이미지로 commit을 해서 생성했다. (docker commit ub myubuntu:1.0.0)
## myubuntu:1.0.0 이라는 이미지를 test:2.0.0 이라는 이미지로 태그를 달아줬다. (docker tag myubuntu:1.0.0 test:2.0.0)
## IMAGE ID를 보면 myubuntu:1.0.0은 이미지를 새로 생성해서 IMAGE ID가 부여되었다는 것을 알 수 있다.
## test:2.0.0은 태그를 달아준 것이기 때문에 myubuntu:1.0.0과 IMAGE ID가 같다는 것을 알 수 있다.
## 이런식으로 태그를 여러개 따면 이미지를 여러개 만들지 않고도 브랜치 따듯 관리를 할 수 있다.
## 도커 허브에 올릴 이미지 생성
## 도커 허브에 올릴 이미지는 규칙이 있다.
## [도커허브ID]/[이미지이름][:TAG] 형식으로 이미지를 만들어야한다.
## ([이미지이름][:TAG] 으로 만들고 push할 때 앞에 '[도커허브ID]/'를 붙이면 '[도커허브ID]/[이미지이름][:TAG]' 전체를 하나의 문자열로 인식해 해당하는 이미지가 없다고 에러가 발생한다.)
docker tag myubuntu:1.0.0 [도커허브ID]/hubtest:2.0.1
docker push [도커허브ID]/hubtest:2.0.1
docker tag myubuntu:1.0.0 apbifnopo/hubtest:2.0.1
docker push apbifnopo/hubtest:2.0.1
--###################################
--### 1. PK, FK, 데이터 삽입 및 조회 ###
--###################################
-- 1. Domain Constraint, 도메인 무결성 제약
CREATE TABLE KOPO_PRODUCT_COLUMN_TEST
(
CHARCOL VARCHAR2 (100),
NUMCOL NUMBER NOT NULL
);
-- 자료형에 위배되는 데이터는 데이터는 들어갈 수 없다.
INSERT INTO KOPO_PRODUCT_COLUMN_TEST
VALUES('TEST1','TEST2')
-- NULL을 허용하지 않는다 하여 NULL이 들어갈 수 없다.
INSERT INTO KOPO_PRODUCT_COLUMN_TEST
VALUES('TEST1',NULL)
-- 도메인 무결성 제약을 위배하지 않는 데이터는 정상적으로 들어간다.
INSERT INTO KOPO_PRODUCT_COLUMN_TEST
VALUES('TEST1',10)
SELECT * FROM KOPO_PRODUCT_COLUMN_TEST
-- 추가로 넣어도 또 들어간다.
INSERT INTO KOPO_PRODUCT_COLUMN_TEST
VALUES('TEST1',10)
DROP TABLE KOPO_PRODUCT_COLUMN_TEST
-- 2. PK(Primary Key Constraint, 개체 무결성 제약), FK(Foreign Key Constrain, 참조 무결성 제약)
-- 모든 테이블 조회
SELECT * FROM TABS
-- 부모 테이블 생성 및 조회 (행사 정보 테이블)
CREATE TABLE KOPO_EVENT_INFO_FOREIGN
(
EVENTID VARCHAR2(20),
EVENTPERIOD VARCHAR2(20),
PROMOTION_RATIO NUMBER,
CONSTRAINT PK_KOPO_EVENT_INFO_FOREIGN PRIMARY KEY (EVENTID) -- 제약조건 설정 / 형식상 써주는거 / PK (지정할 컬럼)
);
SELECT * FROM KOPO_EVENT_INFO_FOREIGN
DESC KOPO_EVENT_INFO_FOREIGN
-- 자식 테이블 생성 및 조회 (실적 정보 테이블)
CREATE TABLE KOPO_PRODUCT_VOLUME_FOREIGN
(
REGIONID VARCHAR2(20),
PRODUCTGROUP VARCHAR2(20),
YEARWEEK VARCHAR2(8),
VOLUME NUMBER NOT NULL,
EVENTID VARCHAR2(20),
CONSTRAINT PK_KOPO_PRODUCT_VOLUME_FOREIGN PRIMARY KEY (REGIONID, PRODUCTGROUP, YEARWEEK),
CONSTRAINT FK_KOPO_PRODUCT_VOLUME_FOREIGN FOREIGN KEY (EVENTID) REFERENCES KOPO_EVENT_INFO_FOREIGN (EVENTID) -- 제약조건 설정 / 형식상 써주는거 / FK (지정할 컬럼) ref '부모테이블'(의 컬럼명)
);
SELECT * FROM KOPO_PRODUCT_VOLUME_FOREIGN
DESC KOPO_PRODUCT_VOLUME_FOREIGN
-- 자식 테이블에서 참조키 설정하기 (테이블 생성 후 FK 설정하기)(부모 테이블을 강제 삭제하기 위해 CASCADE 옵션을 주면 자식 테이블의 FK가 삭제된다.)
ALTER TABLE KOPO_PRODUCT_VOLUME_FOREIGN
ADD CONSTRAINTS FK_KOPO_PRODUCT_VOLUME_FOREIGN FOREIGN KEY (EVENTID)
REFERENCES KOPO_EVENT_INFO_FOREIGN (EVENTID)
--ON DELETE CASCADE -- 부모 테이블에서 에러 없이 삭제가 가능해진다. (자식 테이블의 해당 row를 전부 삭제)
--ON DELETE SET NULL -- 부모 테이블에서 에러 없이 삭제가 가능해진다. (자식 테이블의 해당 FK를 NULL처리하고 row 자체를 삭제하진 않는다.)
-- 자식 테이블에서 참조키 드랍하기
ALTER TABLE KOPO_PRODUCT_VOLUME_FOREIGN
DROP CONSTRAINT FK_KOPO_PRODUCT_VOLUME_FOREIGN;
-- 부모 테이블에 데이터 입력
INSERT INTO KOPO_EVENT_INFO_FOREIGN
VALUES ('A01','20',0.1)
-- 자식 테이블에 데이터 입력 (부모 테이블에 'A01'이 이미 만들어져 있음)
INSERT INTO KOPO_PRODUCT_VOLUME_FOREIGN
VALUES ('SEOUL','REF','202010',20,'A01')
-- 자식 테이블에 데이터 입력 (부모 테이블에 'A02'가 아직 없음)
INSERT INTO KOPO_PRODUCT_VOLUME_FOREIGN
VALUES ('SEOUL','REF','202010',20,'A02')
-- 부모 테이블에서 데이터 삭제 (자식 테이블이 부모 테이블의 'A01'을 FK로 참조중)
DELETE FROM KOPO_EVENT_INFO_FOREIGN
WHERE EVENTID = 'A01'
-- 부모 테이블을 삭제 (자식 테이블이 부모 테이블의 'A01'을 FK로 참조중)
DROP TABLE KOPO_EVENT_INFO_FOREIGN
-- 부모 테이블을 강제 삭제 (자식 테이블이 부모 테이블의 'A01'을 FK로 참조중)
DROP TABLE KOPO_EVENT_INFO_FOREIGN CASCADE CONSTRAINT -- 테이블 삭제를 위해 CONSTRAINT 제약 조건(PK, FK)을 삭제시키는 옵션을 준다.
DROP TABLE KOPO_EVENT_INFO_FOREIGN DELETE SET NULL CONSTRAINT
-- ON DELETE SET NULL 실습 후 해당 row 데이터 삭제
DELETE FROM KOPO_PRODUCT_VOLUME_FOREIGN
WHERE REGIONID = 'SEOUL'
--#####################################################
--### 2. 특정 컬럼 및 특정 조건 데이터 조회, 테이블 UNION ###
--#####################################################
-- 1. Relation Algebra(관계 대수)
-- 모든 테이블 조회하기
SELECT * FROM TABS
-- 해당 테이블데이터의 모든 데이터 조회하기
SELECT * FROM KOPO_PRODUCT_VOLUME
SELECT
REGIONID, -- 지역정보
PRODUCTGROUP, -- 상품정보
YEARWEEK, -- 주차정보
VOLUME -- 판매량
FROM KOPO_PRODUCT_VOLUME
-- 특정 컬럼만 조회하기.
SELECT
PRODUCTGROUP, -- 상품정보
YEARWEEK, -- 주차정보
VOLUME -- 판매량
FROM KOPO_PRODUCT_VOLUME
SELECT YEARWEEK
FROM KOPO_PRODUCT_VOLUME
-- 컬럼명 변경하기 (VOLUME -> VOLUME2)
ALTER TABLE KOPO_PRODUCT_VOLUME
RENAME COLUMN VOLUME TO VOLUME2
-- 컬럼명 다시 되돌리기 (VOLUME2 -> VOLUME)
ALTER TABLE KOPO_PRODUCT_VOLUME
RENAME COLUMN VOLUME2 TO VOLUME
-- 1.1 데이터 조회 (Selection) : 특정 row 조회하기
-- KOPO_PRODUCT_VOLUME 테이블에서 물량이 800,000 이상인 데이터만 조회하세요.
SELECT
REGIONID, -- 지역정보
PRODUCTGROUP, -- 상품정보
YEARWEEK, -- 주차정보
VOLUME -- 판매량
FROM KOPO_PRODUCT_VOLUME
WHERE VOLUME >= 800000
-- KOPO_PRODUCT_VOLUME 테이블에서 201601주차 이상이면서, ST0001인 상품 데이터를 조회하세요.
SELECT
REGIONID, -- 지역정보
PRODUCTGROUP, -- 상품정보
YEARWEEK, -- 주차정보
VOLUME -- 판매량
FROM KOPO_PRODUCT_VOLUME
WHERE YEARWEEK >= '201601' AND PRODUCTGROUP = 'ST0001'
-- 1.2 데이터 조회 (Projection) : 특정 column 조회하기
-- KOPO_PRODUCT_VOLUME 테이블에서 모든 상품을 조회하세요.
SELECT
PRODUCTGROUP -- 상품정보
FROM KOPO_PRODUCT_VOLUME
-- 1.3 집합 연산자 (Union) : 2개의 릴레이션을 합치기
-- A02 테이블을 하나 생성하고 데이터를 불러온다.
CREATE TABLE KOPO_PRODUCT_VOLUME_A02
(
REGIONID VARCHAR2(20), -- 지역정보
PRODUCTGROUP VARCHAR2(20), -- 상품정보
YEARWEEK VARCHAR2(6), -- 주차정보
VOLUME NUMBER, -- 판매량
CONSTRAINT PK_KOPO_PRODUCT_VOLUME_A02 PRIMARY KEY(REGIONID, PRODUCTGROUP, YEARWEEK)
);
SELECT * FROM KOPO_PRODUCT_VOLUME_A02
DESC KOPO_PRODUCT_VOLUME_A02
-- A01 지점과 A02 지점의 실적을 합치기. (데이터 Union 하기) (속성 수와 컬럼 정보가 같아야한다.)
SELECT
REGIONID, -- 지역정보
PRODUCTGROUP, -- 상품정보
YEARWEEK, -- 주차정보
VOLUME -- 판매량
FROM KOPO_PRODUCT_VOLUME
UNION ALL -- 중복 제거 안 함
--UNION -- 중복 제거 및 정렬
SELECT
REGIONID, -- 지역정보
PRODUCTGROUP, -- 상품정보
YEARWEEK, -- 주차정보
VOLUME -- 판매량
FROM KOPO_PRODUCT_VOLUME_A02
--######################################################
--### 3. Expression 수식 사용하기, DISTINCT(중복값 제거) ###
--######################################################
-- 1. Expression 수식 사용하기 (수식 AS 컬럼명)(별칭 달기)
-- '실적정보'라는 문자열 수식을 'MEASURE_NAME'이라는 컬럼에 담는다.
SELECT '실적정보' AS MEASURE_NAME, A.*
FROM KOPO_PRODUCT_VOLUME A
-- 위와 동일.
SELECT
'실적정보' AS MEASUER_NAME,
A.REGIONID, -- 지역정보
A.PRODUCTGROUP, -- 상품정보
A.YEARWEEK, -- 주차정보
A.VOLUME -- 판매량
FROM KOPO_PRODUCT_VOLUME A
-- '상수 문자열'이 아닌 컬럼 정보를 이용할 수도 있다.
SELECT
A.REGIONID || '_' || A.PRODUCTGROUP AS PLANID, -- REGIONID + '_' + PRODUCTGROUP 을 'PLANID'라는 컬럼에 담는다.
A.REGIONID -- 지역코드
FROM KOPO_PRODUCT_VOLUME A
-- 2. Expression 수식 사용하기 (수식을 통해 계산)(계산)
SELECT
A.YEARWEEK + A.VOLUME AS TEST, -- (YEARWEEK + VOLUME) 을 'TEST라는 컬럼에 담는다다.
A.*
FROM KOPO_PRODUCT_VOLUME A
-- Expression 을 사용해 조회한 데이터를 저장까지 하고 싶은 경우
CREATE TABLE STABLE AS
SELECT
YEARWEEK+VOLUME AS TEST,
A.*
FROM KOPO_PRODUCT_VOLUME A
-- 위에서 생성한 STABLE 조회
SELECT * FROM STABLE
-- 원본 테이블의 무결성 제약 조건을 살펴본다.(PK)
DESC KOPO_PRODUCT_VOLUME
-- 이렇게 저장한 데이터는 값은 저장되지만 '권한' 설정까지 복제하지는 못 한다.
DESC STABLE
-- 위에서 생성한 STABLE 삭제
DROP TABLE STABLE
SELECT * FROM TABS
-- 2. DISTINCT 중복값 제거
SELECT * FROM KOPO_CHANNEL_RESULT
-- ACCOUNTNAME에 어떤 것들이 있나 확인할 수 있다.
SELECT
DISTINCT ACCOUNTNAME
FROM KOPO_CHANNEL_RESULT
-- 상품군(PRODUCTGROUP)에 대한 DISTINCT 구현 후 컬럼 이름을 PRODUCT로 변경하세요.
SELECT
DISTINCT PRODUCTGROUP AS PRODUCT
FROM KOPO_CHANNEL_RESULT
--#######################################################################
--### 4. DB 링크 사용하기, IN, NOT IN, BETWEEN, 메타 문자 % 활용해 조회하기 ###
--#######################################################################
-- 1.1 DB 링크 생성 (접속 엔진)
CREATE DATABASE LINK GRAM
CONNECT TO kopo -- ID
IDENTIFIED BY kopo -- PW
USING '
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.219.127)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)'
CREATE DATABASE LINK GRAM
CONNECT TO kopo -- ID
IDENTIFIED BY kopo -- PW
USING 'MYGRAM'
-- 1.2 DB 링크 전체 조회 (엔진 생성이 잘 되었나 확인)
SELECT * FROM ALL_DB_LINKS
-- 1.3 DB 링크 삭제하기. (실습 후 1.2로 삭제된 것을 확인하고 위 1.1로 가서 다시 생성한다.)
DROP DATABASE LINK GRAM
-- 2.1 내 XE에서 'KOPO_CHANNEL_RESULT_NEW'라는 테이블을 조회하라 -> 없어서 안 나옴.
SELECT * FROM KOPO_CHANNEL_RESULT_NEW
-- 2.2 엔진을 이용해 'KOPO_CHANNEL_RESULT_NEW'라는 테이블을 조회하라 -> 엔진을 이용해 학교 서버에 있는 DB를 조회.
SELECT * FROM KOPO_CHANNEL_RESULT_NEW@GRAM
-- 3.1 엔진을 이용해 내 XE에 데이터베이스를 복사해오자.
-- 내 XE에 'KOPO_CHANNEL_RESULT_NEW'라는 테이블을 만들어라. -> AS는 엑셀의 수식 기능 = 이랑 같다고 보면 된다.
CREATE TABLE KOPO_CHANNEL_RESULT_NEW AS
SELECT * FROM KOPO_CHANNEL_RESULT_NEW@GRAM
-- 3.2 이제 내 XE에서 'KOPO_CHANNEL_RESULT_NEW' 테이블을 조회할 수 있다.
SELECT * FROM KOPO_CHANNEL_RESULT_NEW
-- 3.3 생성된 테이블 목록도 확인해보자.
SELECT * FROM TABS
-- 실습 1) GRAM 엔진을 이용해 학교 서버에서 'KOPO_REGION_MST' 테이블을 복사해온 다음 조회하세요.
CREATE TABLE KOPO_REGION_MST AS
SELECT * FROM KOPO_REGION_MST@GRAM
SELECT * FROM KOPO_REGION_MST
-- 실습 2) MYGRAM 서버에서 'KOPO_PRODUCT_VOLUME' 테이블을 복사해오고, ST0002 상품의 연주차가 201514, 201516 인 제품만 조회하세요.
-- 1. 조회하기
SELECT * FROM KOPO_PRODUCT_VOLUME@GRAM
-- 2. DB 복사해오기
CREATE TABLE KOPO_PRODUCT_VOLUME AS
SELECT * FROM KOPO_PRODUCT_VOLUME@GRAM
-- 3.1 자료형 확인하기
DESC KOPO_PRODUCT_VOLUME
-- 3.2 데이터 조회하기 (AND OR 이용)
SELECT * FROM KOPO_PRODUCT_VOLUME
WHERE 1=1
AND PRODUCTGROUP = 'ST0002'
AND (YEARWEEK = '201514' OR YEARWEEK = '201516')
-- 3.2 데이터 조회하기 (IN 이용) YEARWEEK가 '201514','201516'인 것만 조회하기
SELECT * FROM KOPO_PRODUCT_VOLUME
WHERE 1=1
AND PRODUCTGROUP = 'ST0002'
AND YEARWEEK IN ('201514','201516')
-- 3.3 YEARWEEK가 '201514','201516'이 아닌 것만 조회하기
SELECT * FROM KOPO_PRODUCT_VOLUME
WHERE 1=1
AND PRODUCTGROUP = 'ST0002'
AND YEARWEEK NOT IN ('201514','201516')
-- 3.4 YEARWEEK가 '201514 ~ 201516'인 것을 조회하기 / 201514, 201515, 201516이 조회된다.
SELECT * FROM KOPO_PRODUCT_VOLUME
WHERE 1=1
AND PRODUCTGROUP = 'ST0002'
AND YEARWEEK BETWEEN '201514' AND'201516'
-- 실습 3) MYGRAM 서버에서 'KOPO_CHANNEL_RESULT'테이블을 복사해오고, 상품명이 'UN55~~'로 시작하는 제품만 조회하세요.
-- 1. DB 복사해오기
CREATE TABLE KOPO_CHANNEL_RESULT AS
SELECT * FROM KOPO_CHANNEL_RESULT@GRAM
-- 2. 자료형 확인하기
DESC KOPO_CHANNEL_RESULT
-- 3. ITEM이 'UN55~~'로 시작하는 상품 조회하기
SELECT * FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND ITEM LIKE 'UN55%'
SELECT * FROM TABS
WHERE 1=1
AND TABLE_NAME LIKE '%KOPO%'
SELECT * FROM TABS
WHERE 1=1
AND TABLE_NAME LIKE 'KOPO%'
SELECT * FROM TABS
WHERE 1=1
AND TABLE_NAME LIKE '%KOPO'
SELECT * FROM TABS
WHERE 1=1
AND TABLE_NAME LIKE 'KOPO'
--########################################
--### 5. 산술연산자, 결측값, 변수 입력 받기 ###
--########################################
SELECT *
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
SELECT DISTINCT SALESNAME
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND SALESNAME LIKE '%CO'
SELECT DISTINCT AP1ID
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND AP1ID IN ('401737','407373','402383')
CREATE TABLE AP1IDLIST
(
AP1ID VARCHAR2(100),
AP2NAME VARCHAR2(100)
)
INSERT INTO AP1IDLIST VALUES ('401737','폴리텍강서')
INSERT INTO AP1IDLIST VALUES ('401373','폴리텍정수')
INSERT INTO AP1IDLIST VALUES ('402383','폴리텍융기원')
SELECT * FROM AP1IDLIST
SELECT *
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND YEARWEEK BETWEEN 201728 AND 201732
SELECT *
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND YEARWEEK IN (201728, 201732)
-- 산술연산자
SELECT QTY * 1.5 AS QTY2, QTY
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND YEARWEEK BETWEEN 201728 AND 201732
-- 반올림
SELECT ROUND(QTY * 1.23, 2) AS QTY2, QTY
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND YEARWEEK BETWEEN 201728 AND 201732
-- 실습 1) MYGRAM 서버의 KOPO_PROMOTION 테이블에서 조회를 통해 PMAP에 10% 할인된 가격을 ROUND 처리하여 'PMAP10'이라는 컬럼을 생성하세요.
-- 1. MYGRAM에서 DB 불러와 저장하기
CREATE TABLE KOPO_PROMOTION AS
SELECT * FROM KOPO_PROMOTION@GRAM
-- 2. 읽어온 DB 조회하기
SELECT *
FROM KOPO_PROMOTION
-- 3. EXPRESSION 수식 사용해서 조회하기
SELECT
A.REGIONID,
A.ITEM,
A.TARGETWEEK,
A.PLANWEEK,
A.MAP_PRICE,
A.IR,
A.PMAP,
A.PRODUCT,
ROUND(A.PMAP * 0.9, 0) AS PMAP10
-- ROUND(A.PMAP - (A.PMAP * 0.1), 0) AS PMAP10
FROM KOPO_PROMOTION A
WHERE 1=1
-- 실습 2) KOPO_PROMOTION 테이블에서 PMAP/MAP_PRICE 비율을 구해서 PERCENT 컬럼을 생성하세요.
SELECT
A.REGIONID,
A.ITEM,
A.TARGETWEEK,
A.PLANWEEK,
A.MAP_PRICE,
A.IR,
A.PMAP,
A.PRODUCT,
ROUND(A.PMAP / A.MAP_PRICE, 3) AS PERCENT
FROM KOPO_PROMOTION A
WHERE 1=1
-- 실습 3) KOPO_PRODUCT_VOLUME 테이블에서 연산자를 활용하여 'ST0001'의 201544 ~ 201548 주차 실적만 조회하세요.
SELECT * FROM KOPO_PRODUCT_VOLUME
SELECT
A.REGIONID,
A.PRODUCTGROUP,
A.YEARWEEK,
A.VOLUME
FROM KOPO_PRODUCT_VOLUME A
WHERE 1=1
AND PRODUCTGROUP = 'ST0001'
AND YEARWEEK BETWEEN '201544' AND '201548'
-- 결측값 조회하기
CREATE TABLE KOPO_PROMOTION_HK AS
SELECT * FROM KOPO_PROMOTION_HK@GRAM
SELECT *
FROM KOPO_PROMOTION_HK
WHERE 1=1
AND MAP_PRICE IS NULL
--AND MAP_PRICE IS NOT NULL -- 비어있지 않은 값을 조회
-- 변수를 입력 받아 조회하기 (입력 받을 변수의 값이 문자면 아래와 마찬가지로 ' ' 로 입력해준다.)
SELECT *
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND PRODUCTGROUP = &PROD
SELECT *
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
AND PRODUCTGROUP = 'MOBILE'
--##############################################################
--### 6. 함수편! 정렬, UNION, CONCAT, FORMATTING(포맷팅) 등... ###
--##############################################################
-- 정렬하기 (정렬은 PK로 정렬하는게 좋다. 중복이 되지 않으니까)
SELECT *
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
ORDER BY SALESID, SALESNAME, PRODUCTGROUP
-- 1. 정렬하기. (ASC : 오름차순(기본값), DESC : 내림차순)
SELECT *
FROM KOPO_PRODUCT_VOLUME
WHERE 1=1
ORDER BY REGIONID, PRODUCTGROUP, YEARWEEK ASC;
--2. 내림차순 정렬하기
SELECT *
FROM KOPO_PRODUCT_VOLUME
WHERE 1=1
ORDER BY REGIONID, PRODUCTGROUP, YEARWEEK DESC;
--3. 테이블 합치기.
SELECT * FROM KOPO_PRODUCT_VOLUME
SELECT *
FROM KOPO_PRODUCT_VOLUME
WHERE 1=1
AND PRODUCTGROUP = 'ST0001'
UNION
SELECT *
FROM KOPO_PRODUCT_VOLUME
WHERE 1=1
AND PRODUCTGROUP = 'ST0002';
--Ctrl + E 누르면 소모된 비용(컴퓨터 자원 소모)를 보여준다.
SELECT *
FROM KOPO_PRODUCT_VOLUME
WHERE 1=1
AND PRODUCTGROUP = 'ST0001'
UNION ALL
SELECT *
FROM KOPO_PRODUCT_VOLUME
WHERE 1=1
AND PRODUCTGROUP = 'ST0002';
--UNION : PLAN(Cost) 78,265 3초 소요 Cardinality 697,996
--UNION ALL : PLAN(Cost) 7,065 즉시 실행 Cardinality 1,243,778
-- KOPO_CHANNEL_RESULT의 RECORD COUNT 결과가 697,996이다.
-- UNION ALL은 중복 제거를 하지 않으니 빠르고 그대로 자기 자신을 붙여 Cardinality가 2배가 되었고,
-- UNION은 중복 제거를 하여 자원 소모 비용이 높고 시간이 걸린다. 이 경우 Cardinality는 당연히 자기 자신 1배수 그대로 나왔다.
SELECT *
FROM KOPO_CHANNEL_RESULT
UNION
SELECT *
FROM KOPO_CHANNEL_RESULT;
--4. 대소문자 처리
SELECT A.*, A.LOWER(REGIONID) AS REGIONID_P
FROM KOPO_CHANNEL_SEASONALITY_NEW.A;
--5. 합치기(CONCAT, ||)
--CONCAT은 한 번에 2개만 된다.
SELECT CONCAT(REGIONID, PRODUCT) AS KEY_IDX
FROM KOPO_CHANNEL_SEASONALITY_NEW;
--3개 이상 CONCAT하기.
SELECT CONCAT(CONCAT(REGIONID, PRODUCT),YEARWEEK) AS KEY_IDX
FROM KOPO_CHANNEL_SEASONALITY_NEW;
--OR 조건으로도 동일하게 만들 수 있다.
SELECT REGIONID||PRODUCT||YEARWEEK AS KEY_IDX
FROM KOPO_CHANNEL_SEASONALITY_NEW;
--OR 조건을 사용하면 구분도 가능하다.
SELECT REGIONID||'_'||PRODUCT||'_'||YEARWEEK AS KEY_IDX
FROM KOPO_CHANNEL_SEASONALITY_NEW;
--6. 인덱스로 문자열 추출하기
SELECT PRODUCT,
SUBSTR(PRODUCT,1,3) AS PRODUCT
FROM KOPO_CHANNEL_SEASONALITY_NEW;
SELECT PRODUCT,
SUBSTR(PRODUCT,-3,2) AS PRODUCT
FROM KOPO_CHANNEL_SEASONALITY_NEW;
SELECT PRODUCT,
LPAD(2,2,'0') AS PRODUCT
FROM KOPO_CHANNEL_SEASONALITY_NEW;
SELECT *
FROM DUAL; -- 오라클에만 있는 더미 테이블
-- 자릿수 맞추기 (FORMATTING 생각하면 된다. 기존 문자열보다 큰 값을 넣고 빈 자리를 채워넣는 함수)
SELECT LPAD(2,2,'0')
FROM DUAL;
SELECT PRODUCT,
LPAD(PRODUCT, 10,'*')
FROM KOPO_CHANNEL_SEASONALITY_NEW;
SELECT PRODUCT,
RPAD(PRODUCT,12,'QT')
FROM KOPO_CHANNEL_SEASONALITY_NEW;
--컬럼 값 제거하기
SELECT PRODUCT,
LTRIM(PRODUCT, 'PRO'),
RTRIM(PRODUCT, '56')
FROM KOPO_CHANNEL_SEASONALITY_NEW;
-- 공백 제거 (TRIM)
SELECT ' ABC ' AS TEST FROM DUAL;
SELECT TRIM(' ABC ') AS TEST FROM DUAL;
-- LENGTH
SELECT LENGTH(' ABC ') AS TEST FROM DUAL;
SELECT LENGTH(TRIM(' ABC ')) AS TEST FROM DUAL;
--컬럼 값 변경하기
SELECT
REGIONID,
REPLACE(REGIONID,'A','REGION_')
FROM KOPO_CHANNEL_SEASONALITY_NEW;
--실습 1. KOPO_CUSTOMERDATA의 고객 코드는 10자리이다.
--만약 10자리가 아니면 왼쪽에 0으로 채운다.
--이후 고객코드의 뒷 4자리는 암호화를 위해 * 처리를 해야한다.
SELECT * FROM KOPO_CUSTOMERDATA;
-- 앞에 0으로 10자릿수 맞추기.
SELECT
LPAD(A.CUSTOMERCODE, 10, '0') AS NEW_CUSTOEMRCODE,
A.*
FROM KOPO_CUSTOMERDATA A;
--고객코드의 뒷 4자리는 암호화를 위해 * 처리 하기.
SELECT
REPLACE(LPAD(A.CUSTOMERCODE, 10, '0'), SUBSTR(A.CUSTOMERCODE, -4, 4), '****') AS NEW_CUSTOEMRCODE,
A.*
FROM KOPO_CUSTOMERDATA A;
--실습 2. NUMBER_EXARMPLE 테이블에서 FIRST/SECOND NUMBER를 활용하여 아래와 같은 결과를 출력하세요.(4.SQL실습 1 P.24)
SELECT * FROM NUMBER_EXAMPLE;
SELECT
FIRST_NUMBER,
SECOND_NUMBER,
(FIRST_NUMBER / SECOND_NUMBER) AS AVG,
ROUND(FIRST_NUMBER / SECOND_NUMBER) AS ROUND_EX,
CEIL(FIRST_NUMBER / SECOND_NUMBER) AS CEIL_EX,
FLOOR(FIRST_NUMBER / SECOND_NUMBER) AS FLOOR_EX,
MOD(FIRST_NUMBER, SECOND_NUMBER) AS MOD_EX,
POWER(FIRST_NUMBER, SECOND_NUMBER) AS POW_EX
FROM NUMBER_EXAMPLE;
