SQL - 1

■ SQL 언어(structured query language)

 

sql*plus는 tool이지 언어가 아니다.

 

1) query문 : select(검색) (CRUD의 R), 삽입(insert), 검색(read), 수정(update), 삭제(delete)

2) DML (Data Manipulation language) 데이터 조작(작동) 언어
-- insert, delete, update (crud의 c d u ) 
3) DDL (data definition language)
--  create 등.

 

<코딩규칙>

1) keyword는 대문자

2) 절단위로 줄을 바꿈

3) 문장의 끝에는 세미콜론(;)

 

SELECT *  -- 원래는 이런식으로 작성해야 한다.
FROM locations;

 

■ 실습(CRUD)

 

□ 사원 관리 테이블 만들기

 

CREATE TABLE sawon(
sa_id NUMBER(5),
sa_name CHAR(20),
sa_hiredate DATE,
sa_address VARCHAR2(100),
sa_copct NUMBER(7, 2) -- 정수 5자리, 소수 2자리, 7은 총 자릿수
);

 

1) C(INSERT) : 삽입

 

-- INSERT INTO 테이블명
-- VALUES (컬럼값, 컬럼값, 컬럼값, 컬럼값, 컬럼값);

 

DB SQL에서 문자열은 작은따옴표

 

INSERT INTO sawon
VALUES(1, 'hong', '22/03/10', 'seoul', 0.05);

삽입, 수정, 삭제 DML은 임시방에 들어가기 때문에 DB에 넣기 위해서는 반드시 COMMIT; 을 해줘야 한다.

그래야 임시방 buffer에 있는 자료가 전부 DB로 적용된다.

 

2) R(SELECT) : 검색

 

SELECT *
FROM sawon;

 

3) U(UPDATE) : 수정

 

-- UPDATE 테이블명
-- SET 컬럼명 = 칼럼값
-- WHERE 조건; 

 

-- 사원 id가 1번인 조건에 한해서 커미션을 0.05 -> 0.07로 수정

 

UPDATE sawon
SET sa_copct = 0.07
WHERE sa_id = 1;

COMMIT;

 

4) D(DELETE) : 삭제

 

-- DELETE FROM 테이블명
-- WHERE 조건;

 

-- 이름이 choi인 경우에 한해서 해당 사원을 삭제하시오

 

DELETE FROM sawon
WHERE sa_name = 'choi';

COMMIT;

 

□ casing 기법

 

1) pascal casing : BigPrice
2) camel casing : bigPrice
3) snake casing  : big_price

 

-- 오라클에서 big_price (X)

 

□ JDBC

 

-- pro*C (오라클 + C)
-- EXEC SELECT * ~

-- JDBC = 자바 + 오라클 (드라이버 : ojdbc14.jar, ojdbc6.jar, ojdbc8.jar)
-- (jar 파일 : .class 파일의 집합)

 

---

 

■ 자료형

 

□ 숫자, 문자

 

NUMBER(6) : 000000-999999 정수
NUMBER(8,2) : 000000.00 ~ 정수
VARCHAR2(25) : 25자가 입력되면 25자리를 차지, 5자리가 입력, 5자리 차지
CHAR(25) : 25자, 5자 상관없이 25자리 차지

□ VARCHAR vs CHAR

-- VARCHAR2(5) <=== 'babo'
-- CHAR(5) <=== 'babo'

SELECT 로 출력하면 둘다 babo가 나온다.  그러나 VARCHAR2 == CHAR 냐고 물어보면 다르다고 나온다. 
--      b a b o   b a b o (빈칸 1자리)
--      _ _ _ _   _ _ _ _ _ 

-- 비교시 babo == 빈칸을 자른 babo를 하면 같다고 나온다. 이때 사용하는 문법이 rtrim() : 오른쪽 공백제거 

 

□ 날짜

 

DATE, 크기가 없다.
dd-mon-yy (미국식) ex) 27-MAR-23
yy/mm/dd ==> RR/mm/dd (한국식) 

* 2023년(현재)인데 1923년생이 있다고 치면
(RR >= 50 이면 1900년도로 간주)
(RR ~ 49 2000년대)

 

-- 저장할 때는 
-- spool kalmoori.txt -> 바탕화면에 텍스트 파일 생성
-- 작업 후
-- spool off치면 텍스트 파일에 저장된다.

-- show user : 현재 사용자의 이름을 보여준다.
-- cl scr : 화면지우기

-- set pagesize 25
-- set linesize 100 (기본은 80)

 

■ 쿼리문

 

-- Q) 사원테이블에서 사원아이디, 사원명, 급여를 조회

 

SELECT employee_id, first_name, salary 
FROM employees;

 

-- Q) 사원테이블에서 사원아이디, 사원명, 급여를 조회 출력하시오 (단, 컬럼명을 한글 alias(별명)로 표현하시오)

 

SELECT employee_id AS 사원아이디, first_name AS 사원명, salary AS 급여
FROM employees;

 

---

 

-- Q) 사원테이블에서 사원아이디, 사원명, 연봉을 조회 출력하시오 (단, 컬럼명을 한글 alias(별명)로 표현하시오)

 

SELECT employee_id AS 사원아이디, first_name AS 사원명, salary*12 AS 1년급여
FROM employees;

 

-- 오류가 발생 왜? 숫자 1때문에

 

SELECT employee_id AS 사원아이디, first_name AS 사원명, salary*12 AS 일년 급여
FROM employees;

 

-- 오류 발생 왜? 띄어쓰기 때문에 

 

-- alias 를 만들 때 처음에 숫자가 나오면 안 되고, 빈칸이 있으면 안 된다.

 

SELECT employee_id AS 사원아이디, first_name AS 사원명, salary*12 AS 일년_급여
FROM employees;

 

-- BUT 나는 반드시 빈칸을 나타내고 싶으면
-- 이 경우에는 ""를 사용하면 된다. 이 경우에만 큰따옴표가 사용된다. 

 

SELECT employee_id AS 사원아이디, first_name AS 사원명, salary*12 AS "일 년 급 여"
FROM employees;

 

---

 

-- Q) 사원테이블에서 사원아이디, 사원명, 연봉을 조회 출력하시오 (단, 컬럼명을 한글 alias(별명)로 표현하시오)
-- 또한 작은따옴표와 비교라는 글자를 출력하시오

 

SELECT employee_id AS 사원아이디, first_name AS 사원명, salary*12 AS "일 년 급 여", '작은따옴표와 비교'
FROM employees;
-- ''은 칼럼 역할을 하고 있다. 

-- as 생략
SELECT employee_id 사원아이디, first_name 사원명, salary*12 "일 년 급 여", '작은따옴표와 비교'
FROM employees;

-- (참고) ajax
-- onreadystatechange (casing 기법이 적용되지 않음) 

-- sql*plust => tool(도구)
-- desc employees; (sql tool 명령 o, sql 언어 x)
-- describe

desc employees;

 

-- Q) 사원의 이름과 성명 중 성을 출력하시오
-- 그런 다음 이어서 아래와 같이 출력되도록 작성하시오.

--    <output layer>
--    
--        성명
--    ------------
--     king steven

 

SELECT last_name AS 성명
FROM employees;

 

방법) 성과 이름을 성명으로 결합하면 됨 
SQL 에서는 결합연산자 || ==> 자바의 + 기호와 같다. 
산술연산자 : +,  
관계연산자 : >, < 
논리연산자 : and, or

 

SELECT CONCAT(last_name, CONCAT(' ', first_name)) AS 성명
FROM employees;

SELECT last_name || ' ' || first_name 성명
FROM employees;

 

-- Q) 사원의 급여를 출력하되 salary에 1300이 있을 경우
-- 1300 달러로 출력하는 sql문을 작성하시오.
-- (단, 출력은 이름, 급여)

 

SELECT first_name 이름, salary || ' ' || '달러' 급여
FROM employees;

 

-- Q) 다음과 같이 출력해보자

-- Steven의 봉급은 24000이지말입니다. --> record 출력 내용
-- Lex의 봉급은 17000이지말입니다.
-- Alexander의 봉급은 9000이지말입니다.
-- Bruce의 봉급은 6000이지말입니다.

 

SELECT first_name || '의 봉급은 ' || salary || '이지 말입니다.' "이름과 봉급"
FROM employees;

 

-- Q) 각 부서의 부서아이디와 부서명을 출력하시오(alias 출력)

 

SELECT department_id 부서아이디, department_name 부서명
FROM DEPARTMENTS;

 

-- Q) 사원의 이름과 급여를 출력하시오.
-- (단, 급여가 10000 이상이 경우만)

 

SELECT first_name 이름, salary 급여
FROM employees
WHERE salary >= 10000;
-- 실행순서는 어떻게 될까?
-- employees(테이블) -> where(조건) -> SELECT

 

-- Q) 직원 중에서 연봉이 150000 이상인 직원들의 이름, 연봉을 출력하시오.
-- (단, 보너스는 200%로 한다. 그리고 연봉에 $도 출력한다)
-- 그리고 이름은 결합하여 출력하시오.

 

SELECT first_name || ' ' || last_name 이름, salary * 14 || '$' 연봉
FROM employees
WHERE salary*14 >= 150000;

 

-- Q) 사원아이디가 105번 사원의 이름, 입사일, 부서번호 조회 출력하시오.

 

SELECT first_name || ' ' || last_name 이름, hire_date 입사일, department_id 부서번호
FROM employees
WHERE employee_id = 105;

 

-- Q) 사원아이디가 120 이상이거나 부서번호가 90번인 사람 조회
-- (단, 출력은 사원 id, 이름, 부서번호)

 

-- sql and or ||(결합연산자)

select employee_id 사원id, first_name 이름, department_id 부서번호 -- 3
FROM employees -- 1
WHERE employee_id >= 120 OR department_id = 90 -- 2

 

-- Q) id가 120이상 이거나 부서번호가 90번인 사람 조회
-- (단, 출력은 사원id, 이름, 급여, 부서번호)
-- 그리고 추가로 급여가 11000 이상인 사원 조회

 

SELECT employee_id 사원id, first_name 이름, salary 급여, department_id 부서번호 -- 3
FROM employees -- 1
WHERE (employee_id >= 120 OR department_id = 90) AND salary >= 11000 -- 2

-- hint 추가조건은 무조건 and 추가 

 

-- Q)
-- 1) 급여가 12000 이상인 직원들의 이름, 급여, 부서id를 조회 출력
-- 2) 직원 중에서 연봉이 20000 이상인 직원들의 이름, 월급여, 연봉을 조회 출력

 

SELECT first_name 이름, salary 급여, department_id 부서id
FROM employees
WHERE salary >= 12000

SELECT first_name 이름, salary 월급여, salary*12 연봉
FROM employees
WHERE salary*12 >= 20000;

 

-- Q) 사원테이블에 어떤 부서 아이디가 있는지 출력하시오
-- (나온 결과를 보고자료에 사용할 예정)

 

SELECT department_id 부서아이디
FROM employees;
-- 중복을 피하고 싶다.
-- distinct : 중복배제 하려는 것 바로 앞에 사용.

SELECT DISTINCT department_id 부서아이디
FROM employees;

-- 178 Kimberely는 부서가 없다. 

 

-- Q) 부서번호가 90 or 50인 부서의 사원명, 부서번호, 급여 출력

 

SELECT first_name 이름, department_id 부서id, salary 급여
FROM employees
WHERE department_id = 90 OR department_id = 50;

 

-- Q) 부서id가 90이거나 80이거나 50인 사람 조회
-- (단, 출력은 이름, 부서id, 급여)

 

SELECT first_name 이름, department_id 부서id, salary 급여
FROM employees
WHERE department_id = 90 OR department_id = 50 or department_id = 80;

좀 더 간략하게 바꿔보면

 

SELECT first_name 이름, department_id 부서id, salary 급여
FROM employees
WHERE department_id IN (90, 80, 50);
-- WHERE department_id = (90, 80, 50); (X)
-- ===> sql tunning (어떤 것이 더 빠른가? 우리는 전혀 신경쓰지 않는다 일단은.)

 

-- Q) 급여가 5000부터 10000사이에 있는 직원의 
-- 이름과 급여 출력 

 

SELECT first_name 이름, salary 급여
FROM employees
WHERE salary >= 5000 and salary <= 10000;

-- BETWEEN을 사용한 다른 방법

SELECT first_name 이름, salary 급여
FROM employees
WHERE salary between 5000 and 10000;

 

---

 

□ 서점의 검색?

 

- 로그인이 맞는지 확인하는 방법
c언어 : strcmp()
java : equals()

 

- 서점의 검색은?

c언어 : strstr()
java : startsWith(), endsWith(), indexOf()
윈도우의 경우    
h*.txt : *는 all를 뜻함  
h?.txt : ?는 하나만

 

- 오라클의 경우
%가 all, -이 한글자
그렇다면 aa_price에서 _의 의미는? ==> 그냥 밑줄이다.

 

-- Q) 사원의 이름 중에서 이름이 A로 시작하는 사람 조회

 

SELECT *
FROM employees
WHERE first_name LIKE 'A%';

 

-- Q) 사원의 이름중 n이 들어있는 사람 조회 출력

 

SELECT *
FROM employees
WHERE first_name LIKE '%n%';

 

-- Q) 입사일중 입사날짜가 17일인 직원 이름, 입사일 출력

 

SELECT first_name 이름, hire_date 입사일
FROM employees
WHERE hire_date LIKE '%17';

 

-- Q) 2003년에 입사한 직원을 모두 출력하시오(부등호 사용)

 

SELECT first_name 이름, hire_date 입사일
FROM employees
WHERE hire_date >= '03/01/01' and hire_date <= '03/12/31';

SELECT first_name 이름, hire_date 입사일
FROM employees
WHERE hire_date between '03/01/01' and '03/12/31';

SELECT first_name 이름, hire_date 입사일
FROM employees
WHERE hire_date between '2003/01/01' and '2003/12/31';

 

-- Q) 2003년에 입사한 직원을 모두 출력하시오(% 사용)

 

SELECT first_name 이름, hire_date 입사일
FROM employees
WHERE hire_date LIKE '2003/%'; -- (X) 기본형이 RR로 잡혀있기 때문에 

SELECT first_name 이름, hire_date 입사일
FROM employees
WHERE hire_date LIKE '03/%'; -- (O)

 

□ 년도에 대한 짧은 메모

 

SELECT *
FROM nls_session_parameters;

-- America 19-NOV-20
-- 일 - 월 - 년

-- RR/mm/dd
-- 00 ~ 49 2000년대
-- 50 ~ 99 1900년대

-- 세월이 가서 년도가 많아지면 기준점이 2000년대에서 2100년대로 간다.

-- yy/mm/dd
-- 00~49 2000년대
-- 50~99 2000년대

 

SELECT first_name 이름, hire_date 입사일
FROM employees
WHERE hire_date LIKE '03______';

 

-- Q) 입사일 중 입사월이 8월인 직원 조회 출력 (%)

 

SELECT *
FROM employees
WHERE hire_date LIKE '%/08/%'; -- (O)

SELECT first_name 이름, hire_date 입사일
FROM employees
WHERE hire_date LIKE '___08___'; -- (O)

 

-- Q) job_id에 언더바(_) 있는 id 조회 출력하시오 (%등 사용)

 

SELECT job_id
FROM employees
WHERE job_id LIKE '%_%';

-- 여기서 밑줄은 한글자? 말 그대로 밑줄인가? --> 밑줄이 아니라 한 글자 앞뒤로 전부다라는 뜻이다. 
-- 즉 밑줄이 없어도 나옴

 

-- 그렇다면 밑줄(_) 포함된 사원 다 나와라

-- job_id 에서 _ 들어간 글자 다 나와라

SELECT job_id
FROM employees
WHERE job_id LIKE '%@_%' ESCAPE '@';