230405 서른 세 번째 수업_SQL

Oracle

종류 모두 다 관계형 데이터 베이스이다.

아직까지는 객체-관계형보다 관계형 데이터 베이스가 주를 이루고 있음
-레코드: 한 행을 의미
-컬럼: 한 열을 의미

속성,애트리뷰트: 고객아이디, 고객이름, 나이, apple, gold와 같은 값들
CHAR(20): 캐릭터 자료형으로 20바이트 차지

릴레이션: 쉽게 말해 테이블이라고 생각하면 됨

도메인: char -> 문자열에 맞는 속성 값만 들어왔는지 판별, 쉽게 말해 자료형이라고 생각

SQL 사용자명(관리자명):

• sys: Super User, 상위
• system: 다른 기능 똑같이 해내지만 데이터베이스를 생성할 권한만 없음

sys로 접속하려면 'sys as sysdba'라고 써야함

system으로 접속하려면 방법 2개

1. conn/system/1234
   1234가 비밀번호라 비밀번호가 보인다는 단점이 있음.

2. conn 입력 -> system 입력 -> 비번: 1234

• 나오려면 exit;

cmd로 DB 접속하는 방법:

create user 사용자명 test identified by 비밀번호;

11버전 이상부터 사용자명에 c##을 붙여야함
create user c##사용자명 test identified by 비밀번호;

권한부여를 반드시 해야 접속 가능
grant connect, resource to c##test;

한줄로 쓰는 방법을 쓰면 비밀번호가 보임!

developer로 하는 방법:

아이콘을 누르거나 도구 - SQL워크시트

도구-환경설정-도구 편집기->
-글꼴 설정 가능
-환경-인코딩 UTF-8 설정 가능
-행 여백- 행 번호 표시 설정 가능

실행 단축키

developerd의 테이블 생성 및 사용:

(')홑따옴표: 문자열을 감싸주는 기호
(")쌍따옴표: 컬럼명 등을 감싸주는 기호

1. 사용자 생성

create user c##사용자명 identified by 비밀번호 ;

• 유저 이름 회피하는 방법(c##안 붙여도 되는 설정)

alter session set "_oracle_script" = true;
create user 사용자이름 identified by 비밀번호;

2. 사용자에게 권한 부여

grant connect, resource to 사용자명;

3. 테이블 스페이스 할당(저장공간 할당)

alter user 사용자명 quota 30M(30MB만 쓸 수 있음) on users; -- 저장용량 제한
alter user KH default tablespace users quota unlimited on users; --저장 공간 제한을 두지 않고

• 사용자 삭제 방법

  drop user c##사용자명;

• 테이블이 존재할 경우 cascade 옵션 넣고 사용자 삭제

  drop user c##사용자명 cascade;

SELECT FROM:

데이터 조회할 때 사용하는 구문
-result set: select문을 통해 조회된 결과물(즉, 조회된 행(튜플)들의 집합을 의미)

[표현법]
SELECT 조회하고자하는 컬럼명, 컬럼명,...
FROM 테이블명;

EMPLOYEE 테이블의 모든 컬럼 조회

SELECT *
FROM EMPLOYEE;

EMPLOYEE 테이블의 사번, 이름, 급여만 조회

SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE;

컬럼값을 통한 산술연산:

SELECT절 컬럼명 작성부분에 산술연산 기술 가능(산술연산된 결과 조회)

EMPLOYEE 테이블에서 사원명, 급여, 보너스, 연봉, 보너스포함연봉 조회

SELECT EMP_NAME, SALARY, BONUS, (SALARY+ BONUS*SALARY)*12
FROM employee;

산술 과정 중 NULL값이 존재할 경우 결과값도 NULL

오늘날짜: SYSDATE

-DATE형식끼리도 연산 가능: 결과값은 일 단위
-EMPLOYEE 테이블에서 사원명, 입사일, 근무일수(오늘날짜-입사일)

SELECT EMP_NAME, HIRE_DATE, SYSDATE-HIRE_DATE
FROM EMPLOYEE;

값이 소수점으로 나오는 이유: 시분초 단위 시간정보까지 관리해서

컬럼명에 별칭 지정하기:

산술연산시 컬럼명이 산술에 들어간 수식 그대로 됨. 별칭부여시 정리 가능

[표현법]
컬럼명 별칭 | 컬럼명 AS 별칭 | 컬럼명 "별칭" | 컬럼명 AS "별칭"

별칭에 띄어쓰기, 특수문자가 포함되면 반드시 ""쌍따옴표로 기술

SELECT EMP_NAME 사원명, SALARY AS 급여, BONUS, SALARY*12 "연봉(원)", (SALARY+ BONUS*SALARY)*12 AS "총 소득"
FROM employee;

리터럴:

임의로 지정한 문자열(' ')

SELECT절에 리터럴을 제시하면 마치 테이블상에 존재하는 데이터처럼 조회 가능
조회된 RESULT SET의 모든 행에 반복적으로 같이 출력

EMPLOYEE테이블에서 사번, 사원명, 급여 조회(급여에 원을 붙여 출력)

SELECT EMP_ID, EMP_NAME,'귀하' AS 존칭, SALARY, '원' AS 단위
FROM EMPLOYEE;

연결 연산자 || :

여러 컬럼값들을 마치 하나의 컬럼인 것처럼 연결하거나, 컬럼값과 리터럴을 연결할 수 있음

*연습문제*
1. EMPLOYEE 테이블 이름, 연봉, 총 수령액(보너스 포함), 실수령액(총수령액-(연봉*세금3%)) 조회
단, 산술연산은 별칭부여

SELECT EMP_NAME, SALARY*12 || '원' 단위 , (salary+ salary*bonus)*12 || '원' 단위 , (salary+salary*bonus)*12-salary*0.3 || '원' 단위
FROM EMPLOYEE;

2. LOCATION 테이블에서 NATIONAL_CODE 옆에 국가 컬럼 추가

   SELECT NATIONAL_CODE, '국가' 나라
   FROM LOCATION;

3. DEPARTMENT테이블에서 1컬럼에 '인사관리부의 위치는 L1 입니다' 출력

   SELECT DEPT_TITLE || '의 위치는 ' || location_id || '입니다'
   FROM DEPARTMENT;

DISTINCT:

컬럼에 중복된 값을 한번씩만 표기할 때
유의사항 : DISTINCT는 SELECT절에 딱 한 번만 기술 가능

SELECT DISTINCT DEPT_CODE, DISTINCT JOB_CODE 
FROM EMPLOYEE;

SELECT DISTINCT JOB_CODE, DEPT_CODE
FROM EMPLOYEE;

WHERE절, 비교연산자:

조회하고자 하는 테이블로부터 특정 조건에 만족하는 데이터만 조회할 때
이때 WHRER절에 조건식을 제시함
조건식에는 다양한 연산자들 사용

[표현법]
SELECT 컬럼, 컬럼, ...
FROM 테이블명
WHERE 조건식;

비교연산자

<, >, >=, <= :대소 비교
= : 대등 비교
!=, ^=, <> : 같지 않은지 비교

EMPLOYEE 테이블에 부서코드가 'D1'이 아닌 사원들의 사번, 사원명, 부서코드 조회

SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
--★WHERE DEPT_CODE != 'D1';
--WHERE DEPT_CODE ^= 'D1'; 잘 사용ㄴㄴ
WHERE DEPT_CODE <> 'D1';

*연습문제*

1. 급여가 300이상인 사원의 사원명, 급여, 입사일, 연봉 조회

SELECT EMP_NAME, SALARY, HIRE_DATE, SALARY*12
FROM EMPLOYEE
WHERE SALARY >= 3000000;

2. 연봉이 5000이상인 사원들의 사원명, 급여, 연봉, 부서코드 조회

SELECT EMP_NAME, SALARY, SALARY12, DEPT_CODE
FROM EMPLOYEE
WHERE SALARY12 >= 50000000;

3. 직급코드가 'J3'이 아닌 사원들의 사번, 사원명, 직급코드, 퇴사여부 조회

SELECT EMP_ID, EMP_NAME, JOB_CODE, ENT_YN
FROM EMPLOYEE
WHERE JOB_CODE != 'J3';

논리 연산자:

여러개의 조건을 제시하고자 할 때 사용
AND(~이면서, 그리고)
OR(~이거나, 또는)

1. 부서코드가 'D6'이거나 급여가 300이상인 사원들의 사원명, 부서코드, 급여 조회
   SELECT EMP_NAME, DEPT_CODE, SALARY
   FROM EMPLOYEE
   WHERE DEPT_CODE = 'D6' OR SALARY >= 3000000;

2. 급여가 350만원 이상 600만원 이하인 사원들의 사번, 사원명, 급여 조회
   SELECT EMP_ID, EMP_NAME, SALARY
   FROM EMPLOYEE
   WHERE SALARY >= 3500000 AND SALARY <= 6000000;

BETWEEN AND:

조건식에서 사용되는 구문
~이상 ~이하 같은 범위에 대한 조건을 제시할 때 사용

[표현법]
비교대상컬럼 BETWEEN 하한값 AND 상한값
=>해당 컬럼값이 하한값 이상이고 상한값 이하인 경우

1. 급여가 350만원 이상 600만원 이하인 사원들의 사번, 사원명, 급여 조회
   SELECT EMP_ID, EMP_NAME, SALARY
   FROM EMPLOYEE
   WHERE SALARY BETWEEN 3500000 AND 6000000;

2. 급여가 350만원 미만 600만원 초과인 사원들의 사번, 사원명, 급여 조회
   SELECT EMP_ID, EMP_NAME, SALARY
   FROM EMPLOYEE
   --WHERE SALARY < 3500000 OR SALARY > 6000000;
   WHERE NOT SALARY BETWEEN 3500000 AND 6000000;
   --NOT: 논리부정연산자