SQL*PLUS
C:\Users\USER>sqlplus sys/ss123$ as sysdba //서버에 로그인 하는코딩
SQL> show user //접속한 사용자 계정 보여주는 코딩
SQL> SELECT * //모든 사용자 계정 조회(확인) SQL(쿼리,시퀄)
FROM all_users ;
계정생성
1단계) scott 계정 존재 유무확인
SELECT *
FROM all_users;
2단계) 계정 생성하기
CREATE USER 계정명 IDENTIFIED BY 비밀번호;
여러 기타 옵션....
여러 기타 옵션....
계정수정
계정 비밀번호도 수정 + 잠금 상태 해제
ALTER USER 계정명 IDENTIFIED BY 비밀번호
ACCOUNT UNLOCK;
계정삭제
DROP USER SCOTT CASCADE ;
계정을 삭제할때 CASCADE 옵션을 추가하면 삭제할 계정이 소유하는 모든 객체도 삭제된다.
테이블의 구조
어떤 테이블의 구조 조회(확인)하는 방법
DESC dept;
DESC 테이블명;
이름 널? 유형
------ -------- ------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
계정이 잠겨 있는지 확인하는 방법
SELECT*
FROM dba_users;
(참고) FROM all_users; --계정명, 계정 ID, 생성일 컬럼만 확인 가능하다.
ALTER USER 계정명 ACCOUNT UNLOCK;
--9. 계정 비밀번호도 수정 + 잠금 상태 해제
ALTER USER 계정명 IDENTIFIED BY lion
ACCOUNT UNLOCK;
계정이 잠겨 있다면 비밀번호가 수정이 불가능하다.
--상태: 실패 -테스트 실패: ORA-28000: the account is locked
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
SQL문 예제
DQL SELECT문 사용
SELECT *
FROM all_users; // scott 유저가 있는지 없는지 확인하는 작업
CREATE USER SCOTT IDENTIFIED BY tiger; // 계정 비밀번호 계정생성
GRANT RESOURCE,CONNECT TO SCOTT; // 권한부여
SCOTT 계정으로 오라클 서버에 접속(연결 == CONNECT)
SELECT * //모든 사용자 계정을 조회(검색) 하여
FROM all_users; //HR 계정 있는지 확인
HR 계정으로 오라클 서버에 접속 (연결 == connect) 시도
=>HR 계정의 비밀번호를 모르는 문제 발생
해결방법 : 계정의 비밀번호를 모르니까 그냥 바꾸는 방법을 선택
ALTER USER HR IDENTIFIED BY lion; //HR 계정의 비밀번호를 lion으로 수정하자
--상태: 실패 -테스트 실패: ORA-28000: the account is locked
하지만 이것또한 실패했다.
why? => HR계정이 잠겨 있기 때문이다.
HR 계정이 정말 잠겨있는지 확인하는 방법(확인)
SELECT*
FROM dba_users; //잠금상태 등 all_users보다 더 많은 정보 확인 가능
FROM all_users; //계정명, 계정 ID, 생성일 컬럼만 확인 가능
ALTER USER HR ACCOUNT UNLOCK; //HR 계정의 잠금 해제하는 방법
최종적으로 합치는 방법 계정 비밀번호도 수정 + 잠금 상태 해제
ALTER USER hr IDENTIFIED BY lion
ACCOUNT UNLOCK;
(참고)
SELECT*
FROM dba_users;
계정의 상태를 확인하고
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
계정의 테이블스페이스를 바꿔 줄수도 있다.
REM SCOTT 계정 접속한 스크립트 파일
REM----------------------------------------------------------------------------
--REM은 주석 의미이다.
1.샘플 테이블 생성
CREATE TABLE
ㄱ.SCOTT.sql 스크립트파일 찾아서
2. scott 계정이 소유하고 있는 테이블 조회(확인)
SELECT *
FROM tabs;
--소유하고 있는 테이블은 없다.--
테이블 생성방법
C드라이브 Oracleexe 폴더에서 scott.sql 검색 후 진행
1단계 ) 소유하고 있는 테이블을 확인한다.
SELECT *
FROM tabs;
2단계 ) 테이블명이 겹치지 않게 테이블을 생성한다.
DEPT(부서) 테이블 생성하겠다.
CREATE TABLE DEPT(
DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, --부서번호 컬럼(PRIMARY KEY 고유한 키값이다.)
DNAME VARCHAR2(14) , --부서명 컬럼
LOC VARCHAR2(13) --지역명 컬럼
) ;
오라클 자료형 : NUMBER(2) 2자리 숫자 자료형,
VARCHAR2(14) 14바이트 크기의 문자열 자료형,
SELECT *
FROM dept; --원래는 스키마.dept; 로써야 정석이지만 scott에 접속해서 작성했으므로 dept로만 써도된다.
FROM 스키마.dept;
FROM 테이블명;
FROM 스키마.테이블명;
scott 계정을 생성하면 scott 계정명과 동일한 스키마(SCHEMA)가 생성됨
테이블에 정보 추가하기
dept 테이블 - 부서정보 추가
-- 부서번호 '부서명' '지역명'
-- 오라클에서 '문자열' 또는 '날짜형'
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); --1 행 이(가) 삽입되었습니다.
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
COMMIT;
EMP테이블 예시
SELECT *
FROM dept;
9. EMP(사원) 테이블 생성
CREATE TABLE EMP ( --사원들을 구별할 수 있는 고유한 키로 사원번호 (empno)컬럼을 설정
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, --사원번호 PRIMARY KEY(PK) == 고유키
,ENAME VARCHAR2(10) --사원명
,JOB VARCHAR2(9) --잡
,MGR NUMBER(4) --직속상사의 사원번호
,HIREDATE DATE --입사일자
,SAL NUMBER(7,2) --급여
,COMM NUMBER(7,2) --커미션
,DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT --부서번호
부서테이블(dept)의 부서번호(dept no)참조
);
--Table EMP이(가) 생성되었습니다.
SELECT *
FROM emp;
사원 등록(추가)
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
:
:
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
12명의 사원 등록(추가)된 것을 확인(조회) 가능
SELECT *
FROM emp;
BONUS 테이블
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);
COMMIT; ***DML문은 반드시 COMMIT을 해줘야한다.
scott이 소유한 테이블 정보 조회
SELECT *
FROM tabs;
DQL문
[SELECT문]
--3)데이터를 가져오는데 사용하는 문
--2)대상 : 하나 이상의 테이블(table), 뷰
--1)SELECT, subQuery를 이용해서
소유자 , SELECT 권한을 부여
【형식】
[subquery_factoring_clause] subquery [for_update_clause];
【subquery 형식】
{query_block ?
subquery {UNION [ALL] ? INTERSECT ? MINUS }... ? (subquery)}
[order_by_clause]
【query_block 형식】
SELECT [hint] [DISTINCT ? UNIQUE ? ALL] select_list
FROM {table_reference ? join_clause ? (join_clause)},...
[where_clause]
[hierarchical_query_clause]
[group_by_clause]
[HAVING condition]
[model_clause]
【subquery factoring_clause형식】
WITH {query AS (subquery),...}
SELECT 문의 처리순서
(***매우중요***)
***** SELECT 문의 절(clause) *** 처리 순서 암기 필요
각각의 절마다 줄바꿈 한다.
처리순서
[WITH 절 ----1 ] 생략가능
SELECT 절 ----6
FROM 절 ----2
[WHERE 절 ----3 ]생략가능
[GROUP BY 절 ----4 ]생략가능
[HAVING 절 ----5 ]생략가능
[ORDER BY 절 ----7 ]생략가능
SELECT 절에서 사용할 수 있는 키워드 : DISTINCT, ALL, AS
별칭
AS
sal(급여) + comm == pay
이름 == 별명 == 별칭
[AS 키워드] 는 SELECT절에 컬럼의 별칭을 부여할 때 사용하는 예약어.
SELECT empno AS "사원번호"
, ename AS 사원명 --별칭 줄 때 "" 생략가능
, hiredate 입사일자 --AS도 생략가능
null / NVL2 / NVL
오라클 null 의미 : 미확인된 값, 적용되지 않은 값
예) 이름 / 몸무게
홍길동 / 65.93
김철수 / null 몸무게 측정 x 확인되지 않은 값.
오라클에서 null 처리하는 함수 : nvl2( ), NVL( )
【형식】
NVL2(expr1, expr2, expr3)
expr1 널이면 expr3
expr1 널이아니면 expr2
【형식】
NVL(expr1,expr2)
널(NULL)을 0 또는 다른 값으로 변환하는 함수
중복배제 / 중복 포함
emp 사원테이블에서 job 출력(조회)
조건1) 중복 배제 JOB
SELECT DISTINCT job
FROM emp
ORDER BY job ASC;
--중복 배제 X 모두 출력
SELECT ALL job
FROM emp
ORDER BY job ASC;
WHERE 문 예시
SELECT num,name,ibsadate,city
FROM insa
WHERE city IN('경기','인천','서울')
ORDER BY city;
SELECT num,name,ibsadate,city
FROM insa
where city = '서울' OR --13곳
city = '인천' OR --9곳
city = '경기'
ORDER BY city;
SELECT buseo,name,basicpay
FROM insa
WHERE buseo = '영업부' AND basicpay BETWEEN 1500000 AND 2500000 --이 코딩을 더 많이 씀
ORDER BY basicpay
SELECT buseo,name,basicpay
FROM insa
WHERE 1500000<= basicpay AND basicpay <=2500000 AND buseo = '영업부'
ORDER BY basicpay
SELECT ibsadate, name
FROM insa
WHERE SUBSTR(ibsadate,0,2) = '98'
--WHERE 정규표현식 98 시작 검색해 보자
--WHERE ibsadate BETWEEN '1998-01-01' AND '1998-12-31'
--WHERE ibsadate >= '1998-01-01' AND ibsadate<='1998-12-31'
--WHERE ibsadate >= '1998.1.1' AND ibsadate<='1998.12.31'
--WHERE ibsadate >= '98/01/01' AND ibsadate<='98/12/31'
ORDER BY ibsadate ASC;
Example(예제)
1) 모든 사원 정보를 조회(확인, 검색)하는 SQL(쿼리 = QUERY)를 작성하세요.
SELECT * --모든 칼럼을 조회하겠다.
FROM emp; --테이블명 또는 뷰 대상
2) 모든 사원 정보를 조회(확인, 검색)하는 SQL(쿼리 = QUERY)를 작성하세요.
(조건 : 사원번호, 사원명, 입사일자 만 조회)
SELECT empno, ename, hiredate
FROM emp;
3) 모든 사원 조회(사원번호, 사원명, 입사일자, 급여, 커미션)
조건 1) 급여(sal)많이 받는 사람 순으로 정렬해서 조회,
sal 내림차순(descending) 정렬
SELECT empno, ename, hiredate,sal, comm
FROM emp
ORDER BY sal DESC;
--ORDER BY sal; -- 오름차순(ascending) 정렬, 기본정렬 : ASC 오름차순
4) 모든 사원들을 조회
조건1) 부서번호, 사원번호, 사원명, 입사일자
조건2) 입사일자 순으로 정렬(최근 입사한 사원을 먼저 출력)
SELECT deptno, empno, ename, hiredate
FROM emp
ORDER BY 1 ASC; --부서별로 오름차순 정렬
--ORDER BY deptno ASC, hiredate ASC;
--ORDER BY 1 ASC, 4 DESC; --부서별로 1차 오름차순 정렬을
--부서로 1차 정렬된 후 그리고
--2차 정렬 : 부서 안에서 입사일자를 기준으로 내림차순 정렬하겠다는의미
ORDER BY hiredate DESC;
ORDER BY hiredate ASC;
5) HR 계정으로 접속 Localhost_HR
--오라클에서 문자열 합치는 방법 : 2가지 ---- ||연산자 사용 또는 CONCAT 함수 사용
SELECT ename || 'has $' ||sal
FROM emp;
SELECT CONCAT( CONCAT(ename,'has $'), sal)
FROM emp;
--[SCOTT 접속]
6) 사원번호, 사원명, 입사일자, SAL(기본급),COMM 출력
sal(급여) + comm == pay
이름 == 별명 == 별칭
[AS 키워드] 는 SELECT절에 컬럼의 별칭을 부여할 때 사용하는 예약어.
SELECT empno AS "사원번호"
, ename AS 사원명 --별칭 줄 때 "" 생략가능
, hiredate 입사일자 --AS도 생략가능
, sal, comm
, sal + comm
, NVL2(sal + comm ,sal + comm,sal) AS pay
--, NVL2(comn ,sal + comm,sal) --위랑 같은 코딩
,sal + NVL(comm,0)
FROM emp;
ㄱ. 값 + null ==> null
ㄴ. 오라클 null 의미? 미확인된 값, 적용되지 않은 값
예) 이름 / 몸무게
홍길동 / 65.93
김철수 / null 몸무게 측정 x 확인되지 않은 값.
ㄷ. COMM 컬럼의 값의 null 처리
결정) 0 으로 처리하겠다.
ㄹ. 오라클에서 null 처리하는 함수 : nvl2()
【형식】
NVL2(expr1, expr2, expr3)
expr1 널이면 expr3
expr1 널이아니면 expr2
【형식】
NVL(expr1,expr2)
널(NULL)을 0 또는 다른 값으로 변환하는 함수
SELECT comm
,NVL(comm, 0)
,sal + NVL(comm,0) pay
FROM emp;
7) emp 사원테이블에서 job 출력(조회)
조건1) 중복 배제 JOB
SELECT DISTINCT job
FROM emp
ORDER BY job ASC;
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
--JOB의 종류를 파악하기 위해서 쿼리 작성 -
JOB(일)
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
PRESIDENT
SALESMAN
CLERK
ANALYST
CLERK
8) SCOTT 계정이 소유하는 모든 테이블 조회
user_tables 와 tabs 같은 동의어
오라클 *데이터사전*에 포함된 뷰(View) - user_tables 뷰명
SELECT *
FROM user_tables;
FROM tabs;
FROM 테이블명 또는 뷰명
9)emp 사원 테이블에서 모든 사원 정보를 조회(확인, 검색)
조건1) 10번 부서원만 조회.
조건2) sal+comm 급여를 기준으로 오름차순 정렬
조건3) 부서번호, 사원명, pay 출력
SELECT deptno, ename, sal+NVL(comm,0) pay
FROM emp
--WHERE deptno == 10; 오라클내에서는 틀린 표현이다.
WHERE deptno = 10 --오라클 같다 연산자 =
ORDER BY sal+NVL(comm,0);
ORA-00936: missing expression
00936. 00000 - "missing expression" -> 표현 잘못되었다는 소리이다.
*Cause:
*Action:
391행, 15열에서 오류 발생
7782 CLARK MANAGER 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
7934 MILLER CLERK 7782 82/01/23 1300 10
10)
insa.sql 스크립트 파일
CREATE TABLE insa(
num NUMBER(5) NOT NULL CONSTRAINT insa_pk PRIMARY KEY
,name VARCHAR2(20) NOT NULL
,ssn VARCHAR2(14) NOT NULL
,ibsaDate DATE NOT NULL
,city VARCHAR2(10)
,tel VARCHAR2(15)
,buseo VARCHAR2(15) NOT NULL
,jikwi VARCHAR2(15) NOT NULL
,basicPay NUMBER(10) NOT NULL
,sudang NUMBER(10) NOT NULL
);
INSERT INTO insa (num, name, ssn, ibsaDate, city, tel, buseo, jikwi, basicPay, sudang) VALUES
(1001, '홍길동', '771212-1022432', '1998-10-11', '서울', '011-2356-4528', '기획부',
'부장', 2610000, 200000);
INSERT INTO insa (num, name, ssn, ibsaDate, city, tel, buseo, jikwi, basicPay, sudang) VALUES
(1002, '이순신', '801007-1544236', '2000-11-29', '경기', '010-4758-6532', '총무부',
'사원', 1320000, 200000);
:
:
:
INSERT INTO insa (num, name, ssn, ibsaDate, city, tel, buseo, jikwi, basicPay, sudang) VALUES
(1060, '김신애', '810809-2111111', '2001-10-10', '서울', '011-4151-4444', '개발부',
'사원', 900000 , 102000);
COMMIT;
SELECT *
FROM insa;
DESC insa;
--문제1) 사원의 출신지역이 '서울'인 사원 정보 조회(출력)
-- 조건1) 사원번호, 이름, 입사일자, 출신지역
SELECT num,name,ibsadate,city
FROM insa
WHERE city = '서울'; -- 자바였다면...city.equlas("서울");
--문제2) 사원의 출신지역이 수도권인 사원 정보 조회(출력)
-- 조건1) 사원번호, 이름, 입사일자, 출신지역
SELECT num,name,ibsadate,city
FROM insa
--where city = '서울' OR --13곳
-- city = '인천' OR --9곳
-- city = '경기'
WHERE city IN('경기','인천','서울')
ORDER BY city;
--문제3) insa 테이블에서 사원의 출신지역이 '서울'이 아닌 사원 정보 조회(출력)
-- 조건1) 사원번호, 이름, 입사일자, 출신지역
-- (서울 출신은 제외한 모든 사원 정보 출력)
SELECT num,name,ibsadate,city
FROM insa
WHERE NOT( city ='서울' );
WHERE city <> '서울';
WHERE city ^= '서울';
WHERE city !='서울'; --오라클에서 같지 않다 즉 다르다 연산자 ? != <> ^=
WHERE city NOT IN('서울'); -- NOT IN (list) --()안이 목록이 아니니까 왠만하면 이렇게 쓰지 않는다.
--() 안이 여러개 일때 쓰는걸 추천한다.
--문제4) insa 테이블에서 사원의 출신지역이 수도권이 아닌 사원 정보 조회(출력)
-- 조건1) 사원번호, 이름, 입사일자, 출신지역
SELECT num,name,ibsadate,city
FROM insa
WHERE city != '서울' AND city != '경기' AND city !='인천';
WHERE city NOT IN('서울','인천','경기');
--문제5) insa 테이블에서 기본급이 150만원 이상인 사원 정보 출력
-- 조건1) buseo name basicpay
-- 조건2) 부서별로 1차 정렬, basicpay 오름차순 정렬
SELECT buseo,name, basicpay
FROM insa
WHERE basicpay>=1500000
ORDER BY buseo, basicpay;
--문제6) insa 테이블에서 기본급이 150만원 이상 250만원 이하 받는 사원의 정보 출력
--조건1) 영업부 사원만 조회.
-- 조건2) 기본급 오름차순 정렬
SELECT buseo,name,basicpay
FROM insa
--WHERE 1500000<= basicpay AND basicpay <=2500000 AND buseo = '영업부'
WHERE buseo = '영업부' AND basicpay BETWEEN 1500000 AND 2500000 --이 코딩을 더 많이 씀
ORDER BY basicpay
[실행결과]
영업부 전용재 1950000
영업부 김정훈 1954200
영업부 손인수 2000000
영업부 고순정 2010000
영업부 산마루 2100000
영업부 권영미 2260000
영업부 김인수 2500000
--문제7) insa 테이블에서 기본급이 150만원 미만 250만원 초과 받는 사원의 정보 출력
--조건1) 영업부 사원만 조회.
-- 조건2) 기본급 오름차순 정렬
SELECT buseo, name, basicpay
FROM insa
WHERE buseo = '영업부' AND NOT(basicpay BETWEEN 1500000 AND 2500000) --이건 부정연산자로 not 쓰임
WHERE buseo = '영업부' AND basicpay NOT BETWEEN 1500000 AND 2500000 --여기서는 숙어임 NOT BETWEEN a AND b
ORDER BY basicpay;
--문제8) insa 테이블에서 입사년도가 1998년인 사원의 정보를 출력
-- (name, ibsadate 칼럼 출력)
--'98/10/11' 홍길동
--오라클 날짜, 문자열 ''
--1998 입사년도
--1988.1.1 ~ 1998.12.31
--JAVA 문자열 잘라서 반환하는 메서드 : substring(f,e)
--오라클 날짜 년도 반환 함수? LIKE 연산자, REGEX_LIKE()
SELECT ibsadate
,SUBSTR(ibsadate,1,2)
,SUBSTR(ibsadate,0,2)
FROM insa;
SELECT ibsadate, name
FROM insa
WHERE SUBSTR(ibsadate,0,2) = '98'
--WHERE 정규표현식 98 시작 검색해 보자
--WHERE ibsadate BETWEEN '1998-01-01' AND '1998-12-31'
--WHERE ibsadate >= '1998-01-01' AND ibsadate<='1998-12-31'
--WHERE ibsadate >= '1998.1.1' AND ibsadate<='1998.12.31'
--WHERE ibsadate >= '98/01/01' AND ibsadate<='98/12/31'
ORDER BY ibsadate ASC;
--테이블 구조
IBSADATE DATE 오라클 자료형: 날짜형
--내가 한 코딩
--SELECT name,ibsadate
--FROM insa
--WHERE TO_CHAR(ibsadate,'yy') = TO_CHAR('1998','yy');
--오라클 연산자
--오라클 자료형
--오라클 함수
--문제) emp 테이블에서 comm 이 null 인 모든 사원 정보를 조회(출력)하는
'Oracle' 카테고리의 다른 글
| [Oracle]계정 생성,접속/포워드 엔지니어링__개발공부 80일차 (0) | 2022.06.27 |
|---|---|
| [Oracle] UPDATE/DELETE __개발공부 35일차 (0) | 2022.04.10 |
| [Oracle] WITH/LIKE__개발공부 34일차 (0) | 2022.04.10 |
| [Oracle] 데이터와 데이터베이스 __개발공부 32일차 (0) | 2022.04.05 |
| [Oracle] 설치 및 환경 구축 __개발공부 32일차 (0) | 2022.04.05 |