20230314 1일차
Oracle SQL Developer
■ SQL(Structured Query Language)
데이터베이스에 있는 테이블의 데이터를 액세스 할 수 있는 언어
◆ DQL(Data Query Language)
◆ DML(Data Manipulation Language)
◆ TCL(Transaction Control Language)
◆ DDL(Data Definition Language)
◆ DCL(Data Control Language)
■ TABLE
SELECT * FROM dba_users;
alter user hr
identified by hr
account unlock;
SELECT * FROM hr.employees;
SELECT문
테이블에서 정보를 검색하는 문
SELECT 컬럼이름, 컬럼이름 - 절
FROM 테이블명 - 절
SELECT문 기능
1. PROJECTION : 열 추출
SELECT employees id
FROM employees
2. SELECTION : 행 추출
3. JOIN : 서로 다른 테이블에 있는 데이터를 추출
[WHERE문 예시]
SELECT * FROM employees
WHERE employees_id = 100;
■ SQL문 작성시 주의점
1. SQL문은 대소문자를 구분하지 않음
SELECT FROM employees;
select from employees;
공백문자는 구분됨
SELECT employee_id, last_name FROM employees;
SELECT employee_id, last_name FROM employees;
SELECT employee_id, last_name
FROM employees;
hint(힌트, 실행계획을 제어)
F10 실행계획 확인
주석
SELECT /*+ full(e) */*
FROM employees e
WHERE employees_id = 100;
SELECT
employee_id, — 사번
last_name —성
FROM employees;
■ 산술연산자
*,/,+,-
NUMBER(숫자열)
VARCHAR2, CHAR(문자열)
DATE(날짜열)
desc employees describe, 테이블 컬럼 정보 확인
SELECT employee_id, salary, salary12, hire_date, hire_date100
FROM employees;
◆ 사칙연산 우선순위
1. *,/
2. +,-
SELECT x
FROM dual; dummy table(가상 테이블)
SELECT 1 * 2 / 3 + 4 – 3
FROM dual;
SELECT ((((1 * 2) / 3) + 4) – 3)
FROM dual;
◆ 사칙연산의 우선순위를 제어하는 방법(괄호로 표현)
SELECT (1 * (2 / (3 + (4 – 3))))
FROM dual;
■ NULL
SELECT employee_id, salary, commission_pct, ((salary*12) + (salary*12*commission_pct))
FROM employees;
SELECT employee_id, salary, nvl(commission_pct,0), ((salary*12) + (salary*12*nvl(commission_pct,0))) annual_salary
FROM employees;
■ 조건제어문(PL/SQL)
IF THEN
ELSE
■ 열 별칭 변환(as는 선택사항)
SELECT employee_id, salary, commission_pct, ((salary*12) + (salary*12*commission_pct)) annual_salary
FROM employees;
SELECT
employee_id as 사번,
salary as 급여,
commission_pct as comm,
((salary*12) + (salary*12*commission_pct)) as annual_salary
FROM employees;
SELECT “오늘 하루도 행복하세요..!” 하루
FROM
■ 연결 연산문
SELECT last_name ||’ ‘|| first_name
FROM employees;
SELECT ‘My name is’ || last_name ||’ ‘|| first_name
FROM employees;
SELECT ‘My name‘s’ || last_name ||’ ‘|| first_name
FROM employees; 오류 발생
SELECT ‘My name’‘s’ || last_name ||’ ‘|| first_name
FROM employees;