오라클 DB 기본 SQL문

수업시간에 배운내용 정리한것

 

==========================

SQL>select*from tab;
>> tab에 있는거 전부 보여줘라

SQL>set sqlprompt_user
>> 'user이름>' 으로 뜨게 만들기

 

SQL>alter user @@ account unlock;
>> @@계정 잠금 풀어줘라

SQL>alter user @@ identified by 1234;
>> @@계정 비번 1234로 해줘라

DESC @@
>> @@정보를 알려줘라

 

======================================
#NULL
오라클에서 null은 0도 아니고 빈공간도 아니다.
#NULL을 값으로 하는걸 찾으려면... 
'=' 쓸 수 없음 => IS 사용 

NVL(E1,E2)
: E1값이 NULL이면 E2값을 반환하고 아니면 E1값을 반환하는 함수.

 
#별칭 주기
바로뒤에 AS 붙이거나 AS생략하고 별칭 바로 이어쓰면  그걸로 별칭 지정 가능
별칭 지정할때 대소문자를 구별하거나 특수문자를 쓰고 싶으면 " " 를 사용하면 된다.(한글도됨)
조인문을 쓰게되면 자주 쓰게될것...
 
======================================

문장처럼 보이도록 특정 값 사이에 공백없이 연결하고 싶으면 "||" 사용

SELECT ENAME || 'IS A'|| JOB FROM EMP;

라고 하면(별칭 제외한 문자열은 반드시 싱글코트!)

ENAME IS A JOB  

이런식으로 나옴.

======================================

#DISTINCT

중복 제외 한번씩만 나옴(칼럼 앞에 쓴다)

======================================

#(날짜)이전 : <= 을 사용

 

#F로 시작하는 사람 찾기
WHERE ENAME LIKE 'F%';

cf) % : 문자가 없어도 되고 몇개가 오든 상관 없다
     _ : 문자가 하나만 온다.

#오름차순 ASC 내림차순 DESC
단, 아무것도 쓰지 않으면 오름차순이 디폴트.

 

#월급 오름차순인데 값이 같으면 이름순으로 하려면

ORDER BY SAL, ENAME 

>> ( , ) 넣어주면됨
ENAME뒤에 DESC넣으면 이름 내림차순.

#단일행 함수: 모든 행에 적용되는 함수

#ABS() : 절대값

#FLOOR(): 괄호안의 수보다 작은 수중 가장 큰 정수

CF)FLOOR(-5.6)=-6

#ROUND : 소수 첫째자리에서 반올림

#TRUNC :지정한 자리수에서 버림

#MOD(A,B) : A를 B로 나눈 나머지

#SUBSTR(~,A,B) :(앞에서부터)A자리부터 B개 >문자 반환

 CF)SUBSTR(~,-1,1) : 맨 뒤에서부터 1개

#INSTR(~,'A',I,J) : ~에서 I자리부터 J번째로 들어가는 A의 위치 >위치의 숫자 반환

======================================

#SELECT SYSDATE 오늘의 날짜 보여줘

#근무일수 구하기

-SELECT FLOOR(SYSDATE-HIREDATE)||'일' AS "근무일수"

-SELECT CONCAT(TRUNC(SYSDATE-HIREDATE),'일') 근무일수
>> CONCAT 문자의 값을 연결한다. 

#날짜
SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYY"년 " MM"월 " DD"일 " DAY')

#TO_CHAR 숫자->문자형

#TO_DATE 문자->숫자 날짜

=================================

#CREATE TABLE TABLE_NAME ;

>> TABLE_NAME 인 테이블 만들어줘

#ALTER TABLE TABLE_NAME
-ADD CALUMN 컬럼 추가
-MODIFY CALUMN 컬럼 수정(데이터 타입이나 크기, 기본값들을 변경한다는 의미)
-DROP CALUMN 컬럼 삭제
-RENAME COLUMN 구이름 TO 신이름; 컬럼이름 변경
#TRUNCATE: 테이블에 들어있는 모든 로우 삭제
#RENAME 구이름 TO 신이름; 테이블이름 변경

#INSERT INTO 테이블이름
(칼럼이름들)
VALUES(데이터들)

#UPDATE 테이블이름
SET 컬럼1=밸류1,....
WHERE 조건(WHERE절이 없으면 모든행에 적용)

#ROLLBACK(이전단계로 되돌리기) 은 DML에서만 가능.
======================================
#서브쿼리 
IN: OR 대신 쓸 수 있음 
>ALL~ : 하면 MAX보다 큰것 구하기 가능 
EXISTS :조건 값이 있으면 참, 없으면 거짓으로 인식 

 

#스칼라 서브쿼리: SELECT절에 서브쿼리가 오는 경우 
#UNION(합집합): 두 테이블의 결합, 두 테이블의 중복 데이터 제거 
SELECT문자의 열의 개수가 반드시 같야야, 결합시 반드시 같은 자료형이라야함. 
*중복된 데이터까지 나오게 하려면: UNION ALL 사용 
#INTERSECT(교집합) 
#MINUS(차집합)  

#다중열서브쿼리

SQL>SELECT ENAME, DEPTNO, SAL
  2  FROM EMP
  3  WHERE(DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL) FROM EMP
  4  GROUP BY DEPTNO);
>> 부서별 최대 급여 받는 사람

SQL>SELECT ENAME, JOB, SAL
  2  FROM EMP
  3  WHERE(JOB, SAL) IN(SELECT JOB, MAX(SAL) FROM EMP
  4  GROUP BY JOB);
>>직업별 최대급여 받는 사람


스칼라 서브쿼리(SELECT절에 서브쿼리가 오는 경우)
Q. 직업이 ANALYST인 사람의 ENAME, JOB, DNAME
SQL>SELECT ENAME,
  2  (SELECT DNAME
  3     FROM DEPT D
  4     WHERE D.DEPTNO=E.DEPTNO)DNAME, JOB
  5  FROM EMP E
  6  WHERE JOB='ANALYST';


테이블 생성
SQL>CREATE TABLE EMP01(     //EMP01이라는테이블을 만들어요
  2  EMPNO NUMBER(4),         //NUMBER 사이즈 4
  3  ENAME VARCHAR2(20),
  4  SAL NUMBER(7,2));

두개의 서브쿼리 연결
SQL>CREATE TABLE EMP01
  2  AS SELECT * FROM EMP;

SQL>UPDATE DEPT01
  2  SET(DNAME, LOC)=(SELECT DNAME, LOC
  3                             FROM DEPT
  4                             WHERE DEPTNO=40)
  5  WHERE DEPTNO=20;

MERGE
SQL>CREATE TABLE EMP02
  2  AS
  3  SELECT * FROM EMP
  4  WHERE JOB='MANAGER';

SQL>INSERT INTO EMP02
  2  VALUES(8000,'SYJ','TOP',7566,'2019/01/12',1200,10,20);

SQL>MERGE INTO EMP01
  2  USING EMP02
  3  ON(EMP01.EMPNO=EMP02.EMPNO)
  4  WHEN MATCHED THEN
  5  UPDATE SET
  6     EMP01.ENAME=EMP02.ENAME,
  7     EMP01.JOB=EMP02.JOB,
  8     EMP01.MGR=EMP02.MGR,
  9     EMP01.HIREDATE=EMP02.HIREDATE,
 10     EMP01.SAL=EMP02.SAL,
 11     EMP01.COMM=EMP02.COMM,
 12     EMP01.DEPTNO=EMP02.DEPTNO
 13  WHEN NOT MATCHED THEN
 14  INSERT VALUES(
 15     EMP02.EMPNO, EMP02.ENAME, EMP02.JOB, EMP02.MGR, EMP02.HIREDATE,
 16     EMP02.SAL, EMP02.COMM, EMP02.DEPTNO);

 

========================================

#데이터 무결성 제약조건: 테이블에 부적절한 자료가 입력되는 것을 방지하기위해 테이블을 생성할 때 각 칼럼에 대해 정의하는 여러 규칙
도메인=칼럼
개체=테이블
NOT NULL :  NULL 안됨
UNIQUE: 데이터 중복 안됨(NULL은 괜찮)
PRIMARY KEY: 데이터 중복 안됨&NULL 안됨
FOREIGN KEY: 부모키가 되기 위한 칼럼은 반드시 부모테이블의 기본키(PRIMARY KEY) 거나 유일키(UNIQUE KEY)로 설정되어 있어야 한다. =>칼럼이름 데이터타입 CONSTRAINT 제약조건명시(이름) REFERENCES 부모

*CONSTRAINT를 사용하면 더 쉽게 삭제할 수 있도록 제약조건의 이름을 지정할 수 있음.
CHECK
DEFAULT: 데이터 입력이 없을경우 디폴트값으로 정해놓은 값이 들어감.

#DESC USER_CONSTRAINTS 데이터딕셔너리뷰로 제약조건 볼 수 있음

#CREATE TABLE 테이블이름(
컬럼이름1 데이터타입(크기)  NOT NULL ,..., );

#제약조건 명시하기
SCOTT>CREATE TABLE EMP04(
  2  EMPNO NUMBER(4) CONSTRAINTㅇ개ㅔ 
  3  EMP04_EMPNO_UK UNIQUE,
  4  ENAME VARCHAR2(10)
  5  CONSTRAINT EMP04_ENAME_NN NOT NULL,
  6  JOB VARCHAR2(9),
  7  DEPTNO NUMBER(2) );

=>확인1(제약조건 타입이 나옴)
SCOTT>SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
  2  FROM USER_CONSTRAINTS
  3  WHERE TABLE_NAME IN('EMP04');

=>확인2(컬럼정보가 나옴)
SCOTT>SELECT * FROM USER_CONS_COLUMNS
  2  WHERE TABLE_NAME IN ('테이블이름') ;

#테이블 레벨에서 제약조건 설정
>> 필요이유: 한 테이블에 PRIMARY KEY는 한개만 올 수 있는데 두개의 칼럼에 PK를 걸고싶을때.
SCOTT>CREATE TABLE MEMBER01(
  2  NAME VARCHAR2(10),
  3  ADDRESS VARCHAR2(30),
  4  HPHONE VARCHAR2(16),
  5  CONSTRAINT MEMBER01_COMBO_PK PRIMARY KEY(NAME, HPHONE));
=>NAME, HPHONE 둘 다 제약조건에 걸릴경우 에러
 
##DELETE CASCADE
SCOTT>CREATE TABLE CHILD2(
  2  ID NUMBER(2),
  3  HOBBY VARCHAR2(6),
  4  CONSTRAINT CHILD2_ID_FK FOREIGN KEY(ID) REFERENCES PARENT2(ID) ON DELETE CASCADE);
=> 부모테이블의 행이 삭제되면 자식 테이블의 행도 삭제된다.

##ON DELETE SET NULL
SCOTT>CREATE TABLE CHILD3(
  2  ID NUMBER(2),
  3  HOBBY VARCHAR2(6),
  4  CONSTRAINT CHILD3_ID_FK FOREIGN KEY(ID)
  5  REFERENCES PARENT3(ID) ON DELETE SET NULL);
=>부모테이블의 행이 삭제되면 자식테이블의 삭제된 부모키의 값이 NULL이 됩니다.

#제약조건도 ALTER TABLE 이용해서 수정가능
SCOTT>ALTER TABLE EMP01
  2  ADD CONSTRAINT EMP01_EMPNO_PK PRIMARY KEY(EMPNO);
*NOT NULL의 추가는 MODIFTY 이용
#스키마: 객체의 소유자

#제약조건 비활성화
>ALTER TABLE EMP01
DISABLE CONSTRAINT EMP01_DEPTNO_FK;
#활성화(단 활성화 할때는 자식 테이블과 부모테이블간 데이터 무결성이 위배되지 않아야한다.)
>ALTER TABLE EMP01
ENABLE CONSTRAINT EMP01_DEPTNO_FK;

#CASCADE옵션
: 부모 테이블과 자식 테이블간의 참조 설정이 되어있을 때 부모 테이블의 제약조건을 비활성화 하면 이를 참조하고 있는 자식 테이블의 제약조건까지 같이 비활성화 시켜주는 옵션.
ALTER TABLE 테이블이름
DISABLE 참조키 CASCADE

#새로운 사용자 만들기
SYSTEM>CREATE USER USER02 IDENTIFIED BY TIGER;
기본적으로 부여해줘야할 권리: CREATE SESSION, CREATE TABLE, ALTER~QUOTA 2M

#WITH ADMIN OPTION
SYSTEM>GRANT CREATE SESSION TO USER02 WITH ADMIN OPTION; 
=>부여받은 권한을 다른 사용자에게 부여할 수 있다.

#객체에 권한부여
#USER_TAB_PRIVS_MADE 내가 준 권한
#USER_TAB_PRIVS_RECD 내가 받은 권한
#롤: 여러 사용자에게 권한을 일일히 부여하려면 번거롭기 때문에 롤을 만들어 작업을 간소화할 수 있다.
SYSTEM>CREATE ROLE MROLE;
SYSTEM>GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO MROLE; 
 =>부여하고 싶은 권한들을 나열한뒤 TO 롤이름 이라고 쓰면됨.
*롤을 만드는건 무조건 시스템에서 그러나 권한 부여는 다른 계정에서도 할 수 있다.
SYSTEM>CREATE ROLE MROLE02;
SCOTT>GRANT SELECT ON EMP TO MROLE02;

 

=========================================

#같은 자료 두개 JOIN 하려면=>별칭 주기.

#OUTER JOIN: 조건에 맞지 않아도 모든 데이터가 나오게

SCOTT>SELECT E.ENAME, D.DEPTNO, D.DNAME

  2  FROM EMP E, DEPT D

  3  WHERE E.DEPTNO(+)=D.DEPTNO;

결과에 빈값이 생기는쪽에 (+) 붙여주기

#ANSI(미국 표준)

, =>CROSS JOIN

,~WHERE=>INNER JOIN~ON

조건을 더 붙일때 AND=>WHERE

SCOTT>SELECT ENAME, DNAME

  2  FROM EMP INNER JOIN DEPT

  3  ON EMP.DEPTNO=DEPT.DEPTNO

  4  WHERE ENAME='SCOTT';

#ANSI_USING

조인하는 두 테이블에 공통의 컬럼이 있을때

USING을 사용하여 간단히 표현할 수 있다.

SCOTT>SELECT EMP.ENAME, DEPT.DNAME

  2  FROM EMP INNER JOIN DEPT

  3  USING(DEPTNO);

*USING을 사용하면 전체(*) 를 SELECT할 수 있음

마찬가지로 뒤에 WHERE을 붙여 조건을 더할수있다.

#NATURAL JOIN

두 테이블의 각각 조인을 정의한 컬럼의 이름이 동일할때 더 간단히 표현 가능.

SCOTT>SELECT EMP.ENAME, DEPT.DNAME

  2  FROM EMP NATURAL JOIN DEPT;

#LEFT OUTERJOIN : 왼쪽에 있는 테이블에서 조건에 맞지 않는 데이터도 나오게

RIGHT OUTERJOIN: 오른쪽에 있는 테이블에서 조건에 맞지 않는 데이터도 나오게

FULL OUTERJOIN: 양쪽 다 데이터 나오게

=========================================================