sqldeveloper-12

오라클 데이터베이스 sql developer

SELECT ALL department_id

FROM employees

ORDER BY 1 ASC;

 

ALL : default value

 

SELECT DISTINCT department_id

FROM employees

ORDER BY 1 DESC;

 

**DISTINCT

 

의사컬럼(Pseudo-Column) : 실제로 존재하지 않는 컬럼, 오라클엔진 제공

ROWID : 결과행이 저장된 메모리 주소

 

CONCAT (str1, str2) : 두 문자열을 연결한 결과를 반환

-- || : 연결 연산자 (간단)

 

바인드 변수 (:=) : 쿼리 실행할 때 임의의 값을 대입해서 처리

--PL/SQL 대입연산자(:=) - 변수나 상수에 값을 대입하는 연산자

 

CREATE TABLE emp_test AS

SELECT employee_id AS emp_id,

                last_name AS l_name

FROM hr.employees

WHERE employee_id BETWEEN 100 AND 150;

 

테이블 삭제

DROP TABLE emp_test;

 

-- 연속적인 일련번호 생성을 해주는 '시퀀스(Sequence)'라는 오라클 데이터베이스 객체에 대해서

 

-- 1) 그래픽 모드로 생성

-- 접속 > 계정(=스키마) > HANUL > 시퀀스 > 마우스 우클릭

-- 2) SQL 모드로 생성

-- CREATE SEQUENCE 시퀀스명

INCREMENT BY 1

START WITH 시작하는 숫자

MAXVALUE  끝나는 숫자

MINVAULE 최소값

NOCACHE;

 

INSERT INTO emp_test 

VALUES (emp_id_sequence.NEXTVAL, 'Seon', 'IT_TUT', 80);

 

 

-- 

 

--CTAS : 씨타스, 1) 테이블 구조 2)테이블 구조 + 데이터를 복사하듯 생성하는 DDL

헤이디 sql

SELECT memid, memname FROM members
ORDER BY memname LIMIT 3 OFFSET 1;

 

AUTO_INCREMENT

값이 자동으로 증가하도록 정의할 수 있다.

반드시 INT 타입이어야 한다

 

UPDATE order_h

SET cancelyn = Y, cancledtm = CURRENT_TIMESTAMP

WHERE orderno = 202201005;

 

 

FROM order_h, members;

-- p.236 내부조인(INNER JOIN)
-- ※ 오라클은 EQUI JOIN(동등 조인, 동등연산자 = 를 사용한 조인)
-- 두 테이블의 공통 컬럼을 기준으로, 조인 연산한 결과를 반환하는 형식
-- 1) MariaDB JOIN
SELECT ROWNUM() AS 'NO',
order_h.orderno, order_h.orddt, order_h.memid,order_h.ordamt,
members.memid, members.memname, members.addr
FROM order_h, members
WHERE order_h.memid = members.memid;


-- 2) ANSI JOIN
-- ON절 <===> WHERE 조건절
SELECT ROWNUM() AS 'NO',
order_h.orderno, order_h.orddt, order_h.memid,order_h.ordamt,
members.memid, members.memname, members.addr
FROM order_h INNER JOIN members
ON order_h.memid = members.memid;

-- 3) ANSI JOIN 
-- USING 절 ==> 공통 컬럼은 참조하는 테이블의 별칭(Alias)를 생략
SELECT ROWNUM() AS 'NO',
order_h.orderno, order_h.orddt, memid,order_h.ordamt,
memid, members.memname, members.addr
FROM order_h INNER JOIN members
USING (memid);

-- ※p.237 INNER 조인을 사용하면, 조건을 만족하지 않은 데이터는 누락될수 있어서
-- 반드시 결과에 대한 검증이 필요함
-- order_h의 주문자(memid)중 abcd 라는 member가 없어서, 9개 주문기록중 8건이 조회됨


-- p.240 외부조인(OUTER JOIN) : [LEFT | RIGHT]
-- order_h : 9rows   ,   members : 6rows
-- abcd 라는 memid의 주문
SELECT a.orderno, a.memid,
b.memname
FROM order_h a LEFT OUTER JOIN members b
ON a.memid = b.memid
ORDER BY a.orderno;

-- OUTER JOIN 결과 컬럼의 값이 NULL이라면, 실제 NULL인지 또는 조인 조건이
-- 잘못되어 NULL인지 분명하게 판단해야 한다.

-- INNER JOIN vs OUTER JOIN 을 언제 사용할까?
-- 개체 관계도(ERD)에서 개체간의 관계가 1개 또는 여러개라면, 두 조인의 결과는 같다.
-- 하지만, 0개 또는 1개 또는 여러개의 관계에서는 데이터에 따라서 그 결과가 다를수 있다.
-- ========================================================================================
-- ※데이터베이스 관련 개발자는 항상 ERD를 참조하여 올바른 결과를 만들어 낼 수 있어야 한다.
-- ========================================================================================


-- p.243 다음과 같은 상황의 데이터를 검색해보자
-- 주문금액이 3000보다 크고, 주문자의 직업코드가 1또는 1인 회원이 주문한 주문내용으로
-- 주문번호, 주문자ID, 주문자이름, 주문총액, 직업코드를 조회한다.
-- 결과 데이터는 주문번호의 오름차순으로 정렬해서 표시한다.
SELECT a.orderno AS 주문번호, a.memid AS 주문자ID, b.memname AS 주문자이름,
a.ordamt AS 주문총액, b.jobcd AS 직업코드
FROM order_h a LEFT OUTER JOIN members b
ON a.memid = b.memid
AND b.jobcd IN ('1', '2')
ORDER BY a.orderno;


SELECT a.orderno AS 주문번호, a.memid AS 주문자ID, b.memname AS 주문자이름,
a.ordamt AS 주문총액, b.jobcd AS 직업코드
FROM order_h a INNER JOIN members b
ON a.memid = b.memid
AND b.jobcd IN ('1', '2')
ORDER BY a.orderno;

-- p.233

-- 1) 상호조인 (CROSS JOIN)
-- 조인하는 테이블의 Row 수 만큼 곱한 결과를 반환
-- members: 6 rows    x    9 rows : order_h   ==> 54 rows
-- ※CROSS JOIN은 일반적으로 사용하지 않는다 ==> 조인 조건을 생략하지 않도록 주의!
SELECT ROWNUM() AS 'NO',
order_h.orderno, order_h.orddt, order_h.memid,order_h.ordamt,
members.memid, members.memname, members.addr
FROM order_h, members;

-- p.236 내부조인(INNER JOIN)
-- ※ 오라클은 EQUI JOIN(동등 조인, 동등연산자 = 를 사용한 조인)
-- 두 테이블의 공통 컬럼을 기준으로, 조인 연산한 결과를 반환하는 형식
-- 1) MariaDB JOIN
SELECT ROWNUM() AS 'NO',
order_h.orderno, order_h.orddt, order_h.memid,order_h.ordamt,
members.memid, members.memname, members.addr
FROM order_h, members
WHERE order_h.memid = members.memid;


-- 2) ANSI JOIN
-- ON절 <===> WHERE 조건절
SELECT ROWNUM() AS 'NO',
order_h.orderno, order_h.orddt, order_h.memid,order_h.ordamt,
members.memid, members.memname, members.addr
FROM order_h INNER JOIN members
ON order_h.memid = members.memid;

-- 3) ANSI JOIN 
-- USING 절 ==> 공통 컬럼은 참조하는 테이블의 별칭(Alias)를 생략
SELECT ROWNUM() AS 'NO',
order_h.orderno, order_h.orddt, memid,order_h.ordamt,
memid, members.memname, members.addr
FROM order_h INNER JOIN members
USING (memid);

-- ※p.237 INNER 조인을 사용하면, 조건을 만족하지 않은 데이터는 누락될수 있어서
-- 반드시 결과에 대한 검증이 필요함
-- order_h의 주문자(memid)중 abcd 라는 member가 없어서, 9개 주문기록중 8건이 조회됨


-- p.240 외부조인(OUTER JOIN) : [LEFT | RIGHT]
-- order_h : 9rows   ,   members : 6rows
-- abcd 라는 memid의 주문
SELECT a.orderno, a.memid,
b.memname
FROM order_h a LEFT OUTER JOIN members b
ON a.memid = b.memid
ORDER BY a.orderno;

-- OUTER JOIN 결과 컬럼의 값이 NULL이라면, 실제 NULL인지 또는 조인 조건이
-- 잘못되어 NULL인지 분명하게 판단해야 한다.

-- INNER JOIN vs OUTER JOIN 을 언제 사용할까?
-- 개체 관계도(ERD)에서 개체간의 관계가 1개 또는 여러개라면, 두 조인의 결과는 같다.
-- 하지만, 0개 또는 1개 또는 여러개의 관계에서는 데이터에 따라서 그 결과가 다를수 있다.
-- ========================================================================================
-- ※데이터베이스 관련 개발자는 항상 ERD를 참조하여 올바른 결과를 만들어 낼 수 있어야 한다.
-- ========================================================================================


-- p.243 다음과 같은 상황의 데이터를 검색해보자
-- 주문금액이 3000보다 크고, 주문자의 직업코드가 1또는 1인 회원이 주문한 주문내용으로
-- 주문번호, 주문자ID, 주문자이름, 주문총액, 직업코드를 조회한다.
-- 결과 데이터는 주문번호의 오름차순으로 정렬해서 표시한다.
SELECT a.orderno AS 주문번호, a.memid AS 주문자ID, b.memname AS 주문자이름,
a.ordamt AS 주문총액, b.jobcd AS 직업코드
FROM order_h a LEFT OUTER JOIN members b
ON a.memid = b.memid
AND b.jobcd IN ('1', '2')
ORDER BY a.orderno;


SELECT a.orderno AS 주문번호, a.memid AS 주문자ID, b.memname AS 주문자이름,
a.ordamt AS 주문총액, b.jobcd AS 직업코드
FROM order_h a INNER JOIN members b
ON a.memid = b.memid
AND b.jobcd IN ('1', '2')
ORDER BY a.orderno;


-- p.249 셀프조인(SELF JOIN)
-- 하나의 테이블을 서로 다른 테이블인것처럼 조인연산 하는 형식
-- ※ INNER JOIN 또는 OUTER JOIN 절의 좌우에 같은 테이블 이름을 기술하여 자기 자신을
-- JOIN 하는 것

-- 1) members 테이블에 recommender 컬럼, 가변문자 10바이트 추가
ALTER TABLE members
ADD COLUMN recommender VARCHAR(10) AFTER stat;
/* 영향 받은 행: 0  찾은 행: 0  경고: 0  지속 시간 1 쿼리: 0.734 초 */

-- 2) members 테이블의 데이터중 memid가 'seo'인 행에서, recommender를 'kim1'으로 수정
-- ※UPDATE, DELETE 구문은 데이터를 영구적으로 변경 ==> 복구할 수 없다 (미리 백업하는걸 추천..)
UPDATE members
SET recommender = 'kim1'
WHERE memid = 'seo';
/* 영향 받은 행: 1  찾은 행: 0  경고: 0  지속 시간 1 쿼리: 0.469 초 */

-- 3) SELF JOIN으로, members 테이블에서 recommend 컬럼의 값을 이용해 같은 테이블인
-- members의 추천인 이름을 조회하시오

SELECT a.memid AS 추천인ID, a.memname AS 추천인,
b.memname AS 추천자
FROM members a INNER JOIN members b
ON a.recommender = b.memid
-- USING (공통컬럼) : 테이블 별칭(Alias)를 생략
ORDER BY 1;

SELECT a.memid AS 추천인ID, a.memname AS 추천인,
b.memname AS 추천자
FROM members a LEFT OUTER JOIN members b
ON a.recommender = b.memid
-- USING (공통컬럼) : 테이블 별칭(Alias)를 생략
ORDER BY 1;

 

 

-- p.233

-- 1) 상호조인 (CROSS JOIN)
-- 조인하는 테이블의 Row 수 만큼 곱한 결과를 반환
-- members: 6 rows    x    9 rows : order_h   ==> 54 rows
-- ※CROSS JOIN은 일반적으로 사용하지 않는다 ==> 조인 조건을 생략하지 않도록 주의!
SELECT ROWNUM() AS 'NO',
order_h.orderno, order_h.orddt, order_h.memid,order_h.ordamt,
members.memid, members.memname, members.addr
FROM order_h, members;

-- p.236 내부조인(INNER JOIN)
-- ※ 오라클은 EQUI JOIN(동등 조인, 동등연산자 = 를 사용한 조인)
-- 두 테이블의 공통 컬럼을 기준으로, 조인 연산한 결과를 반환하는 형식
-- 1) MariaDB JOIN
SELECT ROWNUM() AS 'NO',
order_h.orderno, order_h.orddt, order_h.memid,order_h.ordamt,
members.memid, members.memname, members.addr
FROM order_h, members
WHERE order_h.memid = members.memid;


-- 2) ANSI JOIN
-- ON절 <===> WHERE 조건절
SELECT ROWNUM() AS 'NO',
order_h.orderno, order_h.orddt, order_h.memid,order_h.ordamt,
members.memid, members.memname, members.addr
FROM order_h INNER JOIN members
ON order_h.memid = members.memid;

-- 3) ANSI JOIN 
-- USING 절 ==> 공통 컬럼은 참조하는 테이블의 별칭(Alias)를 생략
SELECT ROWNUM() AS 'NO',
order_h.orderno, order_h.orddt, memid,order_h.ordamt,
memid, members.memname, members.addr
FROM order_h INNER JOIN members
USING (memid);

-- ※p.237 INNER 조인을 사용하면, 조건을 만족하지 않은 데이터는 누락될수 있어서
-- 반드시 결과에 대한 검증이 필요함
-- order_h의 주문자(memid)중 abcd 라는 member가 없어서, 9개 주문기록중 8건이 조회됨


-- p.240 외부조인(OUTER JOIN) : [LEFT | RIGHT]
-- order_h : 9rows   ,   members : 6rows
-- abcd 라는 memid의 주문
SELECT a.orderno, a.memid,
b.memname
FROM order_h a LEFT OUTER JOIN members b
ON a.memid = b.memid
ORDER BY a.orderno;

-- OUTER JOIN 결과 컬럼의 값이 NULL이라면, 실제 NULL인지 또는 조인 조건이
-- 잘못되어 NULL인지 분명하게 판단해야 한다.

-- INNER JOIN vs OUTER JOIN 을 언제 사용할까?
-- 개체 관계도(ERD)에서 개체간의 관계가 1개 또는 여러개라면, 두 조인의 결과는 같다.
-- 하지만, 0개 또는 1개 또는 여러개의 관계에서는 데이터에 따라서 그 결과가 다를수 있다.
-- ========================================================================================
-- ※데이터베이스 관련 개발자는 항상 ERD를 참조하여 올바른 결과를 만들어 낼 수 있어야 한다.
-- ========================================================================================


-- p.243 다음과 같은 상황의 데이터를 검색해보자
-- 주문금액이 3000보다 크고, 주문자의 직업코드가 1또는 1인 회원이 주문한 주문내용으로
-- 주문번호, 주문자ID, 주문자이름, 주문총액, 직업코드를 조회한다.
-- 결과 데이터는 주문번호의 오름차순으로 정렬해서 표시한다.
SELECT a.orderno AS 주문번호, a.memid AS 주문자ID, b.memname AS 주문자이름,
a.ordamt AS 주문총액, b.jobcd AS 직업코드
FROM order_h a LEFT OUTER JOIN members b
ON a.memid = b.memid
AND b.jobcd IN ('1', '2')
ORDER BY a.orderno;


SELECT a.orderno AS 주문번호, a.memid AS 주문자ID, b.memname AS 주문자이름,
a.ordamt AS 주문총액, b.jobcd AS 직업코드
FROM order_h a INNER JOIN members b
ON a.memid = b.memid
AND b.jobcd IN ('1', '2')
ORDER BY a.orderno;


-- p.249 셀프조인(SELF JOIN)
-- 하나의 테이블을 서로 다른 테이블인것처럼 조인연산 하는 형식
-- ※ INNER JOIN 또는 OUTER JOIN 절의 좌우에 같은 테이블 이름을 기술하여 자기 자신을
-- JOIN 하는 것

-- 1) members 테이블에 recommender 컬럼, 가변문자 10바이트 추가
ALTER TABLE members
ADD COLUMN recommender VARCHAR(10) AFTER stat;
/* 영향 받은 행: 0  찾은 행: 0  경고: 0  지속 시간 1 쿼리: 0.734 초 */

-- 2) members 테이블의 데이터중 memid가 'seo'인 행에서, recommender를 'kim1'으로 수정
-- ※UPDATE, DELETE 구문은 데이터를 영구적으로 변경 ==> 복구할 수 없다 (미리 백업하는걸 추천..)
UPDATE members
SET recommender = 'kim1'
WHERE memid = 'seo';
/* 영향 받은 행: 1  찾은 행: 0  경고: 0  지속 시간 1 쿼리: 0.469 초 */

-- 3) SELF JOIN으로, members 테이블에서 recommend 컬럼의 값을 이용해 같은 테이블인
-- members의 추천인 이름을 조회하시오

SELECT a.memid AS 추천인ID, a.memname AS 추천인,
b.memname AS 추천자
FROM members a INNER JOIN members b
ON a.recommender = b.memid
-- USING (공통컬럼) : 테이블 별칭(Alias)를 생략
ORDER BY 1;

SELECT a.memid AS 추천인ID, a.memname AS 추천인,
b.memname AS 추천자
FROM members a LEFT OUTER JOIN members b
ON a.recommender = b.memid
-- USING (공통컬럼) : 테이블 별칭(Alias)를 생략
ORDER BY 1;