본문 바로가기
ICIA 수업일지

2021.07.01 수업일지

by 주성씨 2021. 7. 3.

- SQL(Structured Query Language) 시작하기

우리의 목표는 웹서버 개발자이기 때문에 서버에 저장할 줄 알아야 한다. 이를 위해서 DBMS를 사용해야 한다. 어떤 걸 DBMS라고 할까. 관계형(Relation) DB (정형 데이터 ; 문자, 숫자를 저장한)가 있다. 그 종류에는 오라클, msSQL, mySQL, 마리아 DB 등 이 있다. 이렇게 종류는 많지만 표준 SQL을 공부해보도록 하자.

※ 참고
- Relaton : 데이터들을 표; 테이블 형태로 표현하는것. 릴레이션 스키마(구조 나타냄)와 릴레이션 인스턴스(실제값)로 구성.

- SQL 다운로드 과정

1. 구글 기준으로 오라클 다운로드라고 검색

 

2. 오라클 공식 홈페이지 클릭

 

3. DB란 Database 21c Client 클릭

 

4. 최신 버젼을 받아도 되지만 연습용 버젼을 다운

 

5. 클릭

 

6. 해당 OS에 맞는 버젼 다운로드

 

7. 개정이 있으면 로그인 없으면 계정을 만들어서 다운로드

 

8. 개발자 툴도 같이 받도록 한다. SQL Dev 클릭

 

9. 마찬가지로 운영체제에 맞는 설치버젼을 다운로드하도록 하자 되도록 인클루드 버젼을 다운받자

10. SQL DEV는 압축 풀고 안에 있는 실행파일만 찾아서 바탕화면에 복사하면 된다.

※ 설치는 설정되어 있는 경로에 맞게 하면 되지만 DB 설치시 하는 비밀번호 설정은 최대한 간단하게 하자.

 

- SQL 동작되는지 확인하기

- 시작 메뉴에 오라클 폴더에 들어가면 RUN SQL CL이라고 있다. 위와 같이 하면 된다.

ㄴ CONN ; CONNECT, SYSTEM ; 관리자 계정, 1111 ; 비밀번호

ㄴ DISCONN ; DISCONNECT, CONN / AS SYSDBA ; 1인자 SYS에 접속, SHOW USER ; 사용자 보기

ㄴ 비밀 번호 변경하고 싶을때

 

- SQL 시작하기

ㄴ 처음 접속하면 접속란에 아무것도 없다.

1인자는 SYSDBA

ㄴ 1인자 만든다. 비밀번호는 설치시 설정했던 번호이다. 롤은 SYSDBA로 하자. localhost는 내 컴퓨터 ip이니 냅두면 된다. 포트 번호는 1521 고정이다. 

ㄴ 2인자도 NAME과 사용자 이름만 다르고 똑같이 만들지만 롤을 기본값으로 설정하자.

 

※ TABLESPACE 란-

- 데이터를 조회, 수정, 삭제 작업을 하면 어디가에는 물리적으로 존재해야한다. 오라클에서는 DATA FILE 이라는 물리적 형태로 저장하고 이러한 DATA FILE이 하나 이상 모여서 TABELSPACE(이하 TS)라는 논리적 저장공간을 형성한다.
- TS는 하나의 DATABASE(이하 DB) 안에 가장 큰 논리적 저장 공간으로 업무의 단위나 사용용도에 따라 여러개의 TS로 분리하여 관리되고 Segment(오브젝트)라는 논리적 저장공간의 집합이기도 하다.

1. TS의 종류
TS의 종류는 크게 3가지로 나뉘고 DB 생성 시 꼭 필요한 TS 4개가 있다.

1) PERMANENT TS
 영구 TS는 가장 일반적인 TS로 데이터 축적용도로 사용되는 공간이다. 다른 TS와는 다르게 고의적으로 삭제하지 않는한 영구적으로 보존되는 객체들을 저장하기 위한 용도이다. USERS나 EXAMPLES TS처럼 임의의 이름을 지정하여 원하는 데이터를 저장할 수 있고 DB가 운영되기 위해 꼭 필요한 SYSTEM과 SYSAUX 테이블 스페이스가 있다.

  1_1) SYSTEM(필수 요소)
  DB의 운영에 필요한 기본 정보를 담고 있는 DATA DICTIONARY TABLE이 저장되는 공간으로 DB에서 가장 중요한 TS이다. 중요한 데이터가 담겨져 있는 만큼 문제가 생길 경우 자동으로 DB는 종료될 수 있고 일반 사용자들의 오브젝트들을 저장하지 않는 것을 권장한다. 

  1_2) SYSAUX(필수 요소)
  SYSAUX TS는 SYSTEM TS의 보조로 기존에 SYSTEM TS에 있는 다양한 유틸리티 및 기능을 분리하여 저장한 공간이다. SYSTEM과 마찬가지로 DB 운영에 필수적으로 있어야하는 TS이다.

2) UNDO TS(필수 요소)
 읽기 일관성을 유지하기 위해 사용되는 TS이다. DB 운영 중 많은 사용자들에게서 DML(Data Mainpulation Language) 작업이 이루어진다. 이때 Rollback(되돌리기)하게 되는 경우를 대비하여 DML 작업이 발생했을 때 수정 이전의 값에 대한 정보를 UNDO Segment에 저장한다. 이러한 UNDO Segment에 대한 관리 공간으로 UNDO TS를 사용하고 DB 운영에 있어서 필수적으로 적오도 하나 이상의 TS가 필요하다.

3) Temporary TS(필수 요소)
 Tempoarary TS도 필수적으로 있어야 한다. 사용자 쿼리의 요청으로 정렬하는 작업이 필요한 경우 메모리에 부담을 덜어주기 위해 사용되는 공간이다.

 

- 일반 사용자 계정 생성 및 비밀번호 작성

-- 관리자 계정 접속
-- 일반 사용자 계정 생성
CREATE USER ICIA IDENTIFIED BY 1111;

 

ㄴ 실행하고 싶으면 CTRL + ENTER 또는 F9
ㄴ 되도록 CTRL + ENTER을 사용하도록 하자.

※ CREATE : SCHEMA, DOMAIN, TABLE, VIEW, INDEX 정의

DROP USER ICIA CASCADE;
CREATE USER ICIA IDENTIFIED BY 1111;

ㄴ 기존에 ICIA가 있으면 DROP USER ICIA; 만 써도 된다.
ㄴ 그런데도 안되면 위의 예시처럼 + CASCADE를 이용해서 지운다.

※ DROP : SCHEMA, DOMAIN, TABLE, VIEW, INDEX 삭제

※ CASCADE : 두 테이블을 연결해서 PK(PRIMARY KEY) 를 가지고 있는 쪽의 값을 삭제하면 FK(FOREIGN KEY) 로 연결된 값이 동시에 삭제되게 하는 옵션이다.

 

+ 이제 접속을 만들어야 하는데

ㄴ 권한이 없어서 접속이 안되니 권한을 줘야한다.

 

-- 권한 설정// 접속권한, 테이블 생성 권한 ETC... TO ICIA
-- 이 모든 권한을 한번에 주는 설정은
-- RESOURCE
-- DBA
GRANT CONNECT, RESOURCE, DBA TO ICIA;

ㄴ GRANT : DB 사용자에게 사용권한 부여
ㄴ 위와 같이 CONNECT, RESOURCE, DBA(Database Administrator) 와 같은 중요한 권한은 '관리자'만이 할 수 있다.

생성되었다.

-- ICIA가 사용할 테이블스페이스 수정
ALTER USER ICIA DEFAULT TABLESPACE USERS;
ALTER USER ICIA TEMPORARY TABLESPACE TEMP;

ㄴ 테이블 스페이스를 변경하고 싶을때

※ ALTER : TABLE에 대한 정의변경하는데 사용

 

+ ICIA 열기

-- ICIA 일반 계정
SELECT * FROM DUAL;

ㄴ SELECT : 데이블에서 조건에 맞는 튜플 검색

 

- 테이블에 데이터 저장하기

+ SCOTT.SQL에서 다음과 같이 가져온다.

DROP TABLE DEPT;
CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	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);
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,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
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
	(
	ENAME VARCHAR2(10)	,
	JOB VARCHAR2(9)  ,
	SAL NUMBER,
	COMM NUMBER
	) ;
DROP TABLE SALGRADE;
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;
DROP TABLE DEPT; -- 부서(DEPARTMENT) 테이블 삭제
CREATE TABLE DEPT -- 부서 테이블 생성
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;

ㄴ NUMBER(2) ; 두자리 수까지 넣겠다.

※ CHAR, VARCHAR, VARCHAR2의 차이점

1) CHAR
 CHAR로 데이터 타입을 선언하게 되면 고정된 문자열을 저장하는데 사용한다. 테이블을 만들 때, 무조건 고정된 길이를 지정해줘야 하고 범위는 1 ~ 2000 bytes이다. CHAR를 데이터 타입으로 선언한 변수에 고정된 길이보다 작은 길이의 문자를 넣어도 남은 부분을 공백으로 다 채운다. 만약 더 큰 길이를 입력하게 되면 오라클 DB는 에러를 반환한다.

2) VARCHAR, VARCHAR2
 VARCHAR, VARCHAR2는 가변 길이로 문자열을 저장할 수 있는 데이터 타입입니다. 만약 우리가 VARCHAR2로 변수를 만들면 1 ~ 4000byte까지의 길이를 지정해줄 수 있다.

 CHAR와의 차이점이라면 우리가 선언한 문자열보다 더 짧은 문자열을 선언하게 될 경우 CHAR의 경우 공백으로 남은 부분을 메꾸지만 VARCHAR와 VARCHAR2는 그렇지 않다는 점이다.

 그렇기 떄문에 VARCHAR2를 사용하면 메모리 상의 이점을 얻게 되어 더 많이 사용하게 된다.

3) VARCHAR, VARCHAR2 차이점
 VARCHAR는 MS-SQL에서 사용하는 형식이고 VARCHAR2는 오라클에서 사용하는 형식이다. 문법상으로는 같지만 DBMS에 따라서 다르다.

 

DROP TABLE EMP; -- 사원 테이블 삭제
CREATE TABLE EMP( -- 사원 테이블 생성
    EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	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
);
-- 테이블 검색
SELECT ENAME, SAL FROM EMP;

ㄴ 실행해보자.

아직 데이터가 안들어가 있어서 아무것도 안나온다.

 

-- DEPT 테이블에 데이터 추가
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,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
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
SELECT * FROM EMP;

ㄴ 확인하면

잘 들어가 있다.

 

-- 호봉 테이블 생성
DROP TABLE SALGRADE; -- 테이블 삭제
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);
SELECT * FROM salgrade;

ㄴ 확인해보자

잘 들어가 있다.

 

+ 그렇다면 테이블에 데이터가 잘 들어가 있는지 확인해보자.

잘 들어가 있다.

-- INSERT 데이터를 보조기억장치에 저장
COMMIT;

ㄴ 마무리를 꼭 해야한다.
ㄴ COMMIT : 저장되지 않은 데이터를 모두 저장하고 현재의 TRANSACTION으로 종료한다.

 

- SELECT 검색하기

-- SELECT 검색하기 : SQL 명령어 중 90%이상 사용
-- 현재 계정의 테이블 리스트
SELECT * FROM TAB;

ㄴ TAB : TABLE

-- 테이블 구조보기
DESC DEPT;

ㄴ DESC : descending ; DESCENDING ORDER ; 내림차순을 의미
ㄴ ASC : ascending ; ASCENDING ORDER ; 오름차순을 의미

※ 만약에 테이블 결과를 유지하고 싶다면 핀으로 고정해두자.

 

+ 시분초가 나오게 하고 싶다면? ↓

2021.07.01 - [SQL] - SQL Developer 에서 날짜 형식 시/분/초 까지 나오게 하는 법!!!

 

SQL Developer 에서 날짜 형식 시/분/초 까지 나오게 하는 법!!!

오라클 SQL Developer의 환경설정에서 수정하면 DATE 타입의 컬럼값들을 시/분/초 까지 나오게 변경 할 수 있다. 도구 - 환경설정 - 데이터베이스 - NLS - 날짜 형식 에서 RR/MM/DD HH24:MI:SS 로 변경 해

becomefullstackdev.tistory.com

 

- DEPTNO는 주민번호와 같은거나 그래서 NULL을 허용하지 않는다.

- 이런걸 기본키라고 하며 무조건 입력해줘야 한다.

 

 

- 사원 테이블에서도 마찬가지이다.


- 여기서는 EMPNO가 기본키인 것이다.

 

 

 

 

- SQL의 개념

- 국제 표준 데이터베이스 언어
- 관계형 데이터베이스 지원하는 언어
- 관계대수와 관계해석을 기초로 한 혼합 데이터언어
- 질의어지만, 데이터 구조의 정의, 데이터조작, 데이터 제어기능 갖춤.

-- 셀럭트 보기
-- SQL(구조적 질의어) :
-- 1. DQL : 데이터 질의어
-- 대표적으로 SELECT
-- 2. DML(DATA MAINPULATION LANGUAGE) : 데이터 조작어
-- 대표적으로 INSERT(테이블에 새로운 튜플 삽입)
-- UPDATE; 테이블에서 조건에 맞는 튜플 내용 변경(수정)
-- DELETE; 테이블에서 조건에 맞는 튜플 삭제(삭제)
-- 3. DDL(DATA DEFINE LANGUAGE) : 데이터 정의어
-- CERATE(개체 생성), ALTER(TABLE 정의변경, 수정), DROP(개체 삭제)
-- 4. DCL(DATA CONTROL LANGUAGE) : 데이터 제어어
-- GRANT(권한 부여), REVOKE(권한 취소), COMMIT(완료), ROLLBACK(취소, 복구)
SELECT ENAME, SAL*12 AS 연봉
FROM EMP;

SAL에 12를 곱하고 그 값을 연봉이라고 칭한다면

+ 연봉에 조건을 붙인다면? 3만 이상

SELECT ENAME, SAL*12 AS ANNUAL
FROM EMP
WHERE ANNUAL>30000
;

ㄴ ANNUAL은 가칭이라 출력이 되지 않는다. 일단 어떻게 하는지는 조금 후에 보도록 하자.

결과

SELECT ENAME, SAL*12 ANNUAL
FROM EMP;

ㄴ AS를 생략도 가능하다.

 

SELECT ENAME, SAL*12 "annual"
FROM EMP;

ㄴ ""을 이용해서 문자열로 구분해 소문자 구분이 된다.
ㄴ 그러나 ALIAS에서만 더블쿼터를 사용해보도록 하자. 그거는 자바와의 연동에서 알게 될거다.

※ ALIAS란? ALIAS 는 값에 별칭을 주어 접근을 별칭 형태로 할 수 있도록 하는 역할을합니다.

 

+ 사원이름에 '님'을 붙이고 싶다면?

SELECT ENAME||'님', SAL*12 "annual"
FROM EMP;

ㄴ 싱글쿼터로 문자열을 표현한다.

 

+ 직급이 몇개인지 알고 싶으면?

-- DISTINCT : 중복되는 행 제거
SELECT DISTINCT JOB FROM EMP;

ㄴ DISTINCT를 이용해서 중복되는 행을 제거해서 위는 직급이 총 몇개인지 알 수 있다.

 

- WHRER 조건을 붙여 검색하기

+ EMP 테이블에서 부서 번호가 10번인 사원 검색하기

SELECT * FROM EMP
WHERE DEPTNO = 10;

ㄴ DEPTNO는 다음과 같이 나타낼 수 있다.

SELECT * FROM EMP
WHERE EMP.deptno = 10;

ㄴ 반대로 10번 부서가 아닌 사원 검색하기

SELECT * FROM EMP
WHERE EMP.deptno != 10;

ㄴ !=는 다음과 같이 나타낼 수 있다.

SELECT * FROM EMP
-- WHERE EMP.deptno != 10;
WHERE EMP.deptno <> 10;

ㄴ 또! 다음과 같이도 나타낼 수 있다.

SELECT * FROM EMP
-- WHERE EMP.deptno != 10;
-- WHERE EMP.deptno <> 10;
WHERE NOT EMP.deptno=10;

 

Q1. 급여가 2000이상인 사원 검색

SELECT * FROM EMP
WHERE SAL >=2000;

 

Q2. 이름이 FORD인 사원 검색

SELECT * FROM EMP
WHERE ENAME = 'FORD';

ㄴ 문자열은 대소문자를 가린다. 'ford'라고 치면 검색되지 않는다.

 

Q3. 날짜검색 82년도 이후에 입사한 사람 검색

SELECT * FROM EMP
WHERE HIREDATE >= '1982/01/01' ;

ㄴ 날짜도 문자열 취급해서 싱글쿼터로 하자

 

- LIKE 연산자

-- LIKE 연산자 : 와일드 문자와 같이 사용
-- 1. % : 0 ~ N개 문자와 대응
-- 2. _ : 1개 문자와 대응

Q1. 이름 중에 A가 포함되는 사원을 검색하세요.

SELECT * FROM
WHERE ENAME LIKE '%A%';

ㄴ '%A%' -> A 앞뒤로 몇글자가 있어도 괜찮다.

 

Q2. 이름이 R로 끝나는 사원 검색

SELECT * FROM EMP
WHERE ENAME LIKE '%R';

 

Q3. 이름중에 A가 없는 사원 검색

SELECT * FROM EMP
WHERE ENAME NOT LIKE '%A%';

 

Q4. 이름중에 세번째 글자가 R인 사원 검색

SELECT * FROM EMP
WHERE ENAME LIKE '__R%';

 

Q5. 사원 번호 중에 3이 포함된 사원 검색

SELECT * FROM EMP
WHERE EMPNO LIKE '%3%';

ㄴ 오라클이 형변환을 해줘서 숫자임에도 문자처럼 비교가 가능하다.

 

※ 참고 : 만약에 테이블에 %가 들어간다면?

WHERE ENAME LIKE '%\%%' ESCAPE '\';

 

- 논리연산자

AND(논리곱), OR(논리합), NOT(부정)

Q1. SAL가 2000에서 3000사이인 사원 검색

SELECT * FROM EMP
WHERE SAL <= 3000 AND SAL >= 2000;

ㄴ BETWEEN을 사용해서 바꾼다면

SELECT * FROM EMP
WHERE SAL BETWEEN 2000 AND 3000;

ㄴ 작은 값이 먼저 오도록 한다.
ㄴ 반대로 사이값이 아닌경우는?

SELECT * FROM EMP
WHERE SAL NOT BETWEEN 2000 AND 3000;

ㄴ NOT과 ( )를 사용해서 표현한다면?

SELECT * FROM EMP
WHERE NOT (SAL>=2000 AND SAL<=3000);

ㄴ ( )를 하지 않는다면 NOT이 먼저 수행되어 앞에 SAL>=2000만 된다.

 

Q2. JOB이 CLERK이고, 부서번호가 20인 사원 검색

SELECT * FROM EMP
WHERE JOB = 'CLERK' AND DEPTNO = 20;

 

Q3. COMM을 받는사람

SELECT * FROM EMP
WHERE COMM = 300 OR COMM = 500 OR COMM =1400;

ㄴ 간단하게 하고 싶다면?

SELECT * FROM EMP
WHERE COMM IN(300,500,1400);

ㄴ COMM이 IN안에 값을 포함하면 출력
ㄴ COMM이 없는 사람은?

SELECT * FROM EMP
WHERE COMM NOT IN(300,500,1400);
7844	TURNER	SALESMAN	7698	81/09/08 00:00:00	1500	0	30

ㄴ NULL값이 사람은 안나오게 된다. 그렇다면 어떻게 해야할까?
ㄴ 오라클에서의 NULL은 진짜 아무것도 없는 EMPTY의 상태인 칸도 없는 상태인 것이다.

 

Q4. COMM을 포함한 TOTSAL은?

SELECT ENAME, COMM, SAL*12+COMM ANNUAL FROM EMP;
SMITH		
ALLEN	300	19500
WARD	500	15500
JONES		
MARTIN	1400	16400
BLAKE		
CLARK		
KING		
TURNER	0	18000
JAMES		
FORD		
MILLER

 

ㄴ 연산결과도 NULL, 연산에서 제외되어서 값이 안나오게 된다.

 

Q5. 그렇다면 COMM을 안받거나 NULL인 경우를 출력하고 싶다면?

SELECT * FROM EMP
WHERE COMM NOT IN(300,500,1400) OR COMM IS NULL;
7369	SMITH	CLERK	7902	80/12/17 00:00:00	800		20
7566	JONES	MANAGER	7839	81/04/02 00:00:00	2975		20
7698	BLAKE	MANAGER	7839	81/05/01 00:00:00	2850		30
7782	CLARK	MANAGER	7839	81/06/09 00:00:00	2450		10
7839	KING	PRESIDENT		81/11/17 00:00:00	5000		10
7844	TURNER	SALESMAN	7698	81/09/08 00:00:00	1500	0	30
7900	JAMES	CLERK	7698	81/12/03 00:00:00	950		30
7902	FORD	ANALYST	7566	81/12/03 00:00:00	3000		20
7934	MILLER	CLERK	7782	82/01/23 00:00:00	1300		10

ㄴ 출력된다.
ㄴ NULL값도 제외하고 싶다면?

SELECT * FROM EMP
WHERE COMM NOT IN(300,500,1400) OR COMM IS NOT NULL;
7499	ALLEN	SALESMAN	7698	81/02/20 00:00:00	1600	300	30
7521	WARD	SALESMAN	7698	81/02/22 00:00:00	1250	500	30
7654	MARTIN	SALESMAN	7698	81/09/28 00:00:00	1250	1400	30
7844	TURNER	SALESMAN	7698	81/09/08 00:00:00	1500	0	30

 

Q6. IS NULL, IS NOT NULL을 이용해서 COMM을 안받거나 받는 사원 검색

SELECT * FROM EMP
WHERE COMM IS NULL;
SELECT * FROM EMP
WHERE COMM IS NOT NULL;

 

Q7. 1981년도에 입사한 사원 검색

SELECT * FROM EMP
WHERE HIREDATE LIKE '%81%';

+ 중에 부서번호가 10 또는 20인 사원

SELECT * FROM EMP
WHERE HIREDATE LIKE '81%' AND (DEPTNO = 10 OR DEPTNO = 20);

ㄴ 또는 아래와 같이 가능하다.

SELECT * FROM EMP
WHERE HIREDATE LIKE '81%' AND DEPTNO IN(10,20);

※ 우선순위 NOT > AND > OR

 

- ORDER BY 절 : 정렬

Q1. 급여순으로 출력

SELECT * FROM EMP
ORDER BY SAL;

※ 참고_[ ]에 들어가 있는 것은 옵션이다. 사용 유무를 선택가능하다.

 

Q2. 이름순으로 출력

SELECT * FROM EMP
ORDER BY ENAME;

 

Q3. 날짜순으로 출력

SELECT * FROM EMP
ORDER BY HIREDATE DESC;

 

Q4. 급여가 같았을때 이름은 오름차순으로 정렬

SELECT ENAME, SAL FROM EMP
ORDER BY SAL DESC, ENAME ASC;

ㄴ SAL은 1차 정렬, ENAME 2차 정렬
ㄴ 3차 정렬도 가능하다.

 

- SQL 함수

SELECT 10*20 FROM DUAL;
SELECT * FROM DUAL;
-- 결과를 하나만 보고 싶을 때 DUAL 테이블을 이용하자.

ㄴ DUAL은 SYS소유이다.

 

- ROUND 함수 : 소수점 이하 반올림 생략

SELECT ROUND(45.456)||'일' FROM DUAL;

ㄴ 소수점 첫 째 자리까지

SELECT ROUND(45.456,1)||'일' FROM DUAL;

ㄴ ROUND(실수, '원하는 소수점 자리')

 

- TRUNC 함수 : 소수점 이하를 무조건 생략

SELECT TRUNC(45.456)||'일' FROM DUAL;

 

- MOD 함수 : 나머지를 구하는 함수

SELECT MOD(45456, 100) FROM DUAL;

+ EMP에 있는 SAL을 100으로 나눈다면

SELECT MOD(SAL, 100) FROM EMP;

 

Q. 급여로 100짜리 물건을 구매할 수 있는 개수와 나머지 금액(갯수는 반올림)

SELECT SAL, TRUNC(SAL/100)||개, MOD(SAL,100) FROM EMP;

 

Q. 사원번호가 홀수인 사람만 검색해라.

SELECT * FROM EMP
WHERE MOD(EMPNO,2)=1;

 

Q. 사원번호가 짝수인 사람만 검색해라.

SELECT * FROM EMP
WHERE MOD(EMPNO,2)=0;

 

저작자표시

'ICIA 수업일지' 카테고리의 다른 글

2021.07.05 수업일지  (0) 2021.07.10
2021.07.02 수업일지  (0) 2021.07.03
2021.06.30 수업일지  (0) 2021.07.03
2021.06.29 수업일지  (0) 2021.07.03
2021.06.28 수업일지  (0) 2021.07.03

관련글

티스토리툴바