6월 21일_SQL(기초, 오라클 사용, SELECT 구문)

# 오라클 사용하기

- 오라클 프로그램 화면 기본 구성

- 오라클 SQL Deverloper 프로그램에서는 대소문자 구분하지 않아도 됨

 

 

- 코드 입력 후 출력해보기

drop table members;
drop table buy;



-- 회원 테이블 생성
CREATE TABLE members
( mem_id      VARCHAR2(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  mem_name    VARCHAR2(20) NOT NULL, -- 이름
  mem_number  NUMBER(3) NOT NULL,  -- 인원수
  addr        VARCHAR2(10) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  phone1      CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2      CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height      NUMBER(3),  -- 평균 키
  debut_date  DATE  -- 데뷔 일자
);

-- 구매 테이블 생성
CREATE TABLE buy
( num         NUMBER(8) PRIMARY KEY, -- 순번(PK)
  mem_id      VARCHAR2(8) NOT NULL, -- 아이디(FK)
  prod_name   VARCHAR2(20) NOT NULL, -- 제품이름
  group_name  VARCHAR2(10), -- 분류
  price       NUMBER NOT NULL, -- 가격
  amount      NUMBER(5) NOT NULL, -- 수량
  CONSTRAINT fk_members FOREIGN KEY (mem_id) REFERENCES members(mem_id)
);

-- member 테이블에 데이터 삽입
INSERT INTO members VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, TO_DATE('2015-10-19', 'YYYY-MM-DD'));
INSERT INTO members VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, TO_DATE('2016-08-08', 'YYYY-MM-DD'));
INSERT INTO members VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, TO_DATE('2015-01-15', 'YYYY-MM-DD'));
INSERT INTO members VALUES('OMY', '오마이걸', 7, '서울', NULL, NULL, 160, TO_DATE('2015-04-21', 'YYYY-MM-DD'));
INSERT INTO members VALUES('GRL', '소녀시대', 8, '서울', '02', '44444444', 168, TO_DATE('2007-08-02', 'YYYY-MM-DD'));
INSERT INTO members VALUES('ITZ', '잇지', 5, '경남', NULL, NULL, 167, TO_DATE('2019-02-12', 'YYYY-MM-DD'));
INSERT INTO members VALUES('RED', '레드벨벳', 4, '경북', '054', '55555555', 161, TO_DATE('2014-08-01', 'YYYY-MM-DD'));
INSERT INTO members VALUES('APN', '에이핑크', 6, '경기', '031', '77777777', 164, TO_DATE('2011-02-10', 'YYYY-MM-DD'));
INSERT INTO members VALUES('SPC', '우주소녀', 13, '서울', '02', '88888888', 162, TO_DATE('2016-02-25', 'YYYY-MM-DD'));
INSERT INTO members VALUES('MMU', '마마무', 4, '전남', '061', '99999999', 165, TO_DATE('2014-06-19', 'YYYY-MM-DD'));

-- buy 테이블에 데이터 삽입
INSERT INTO buy VALUES(1, 'BLK', '지갑', NULL, 30000, 2);
INSERT INTO buy VALUES(2, 'BLK', '맥북프로', '디지털', 10000, 1);
INSERT INTO buy VALUES(3, 'APN', '아이폰', '디지털', 200000, 1);
INSERT INTO buy VALUES(4, 'MMU', '아이폰', '디지털', 20000, 5);
INSERT INTO buy VALUES(5, 'BLK', '청바지', '패션', 50000, 3);
INSERT INTO buy VALUES(6, 'MMU', '에어팟', '디지털', 80000, 10);
INSERT INTO buy VALUES(7, 'GRL', '노트북2', '서적', 1500, 5);
INSERT INTO buy VALUES(8, 'APN', 'e-사전', '서적', 2500, 2);
INSERT INTO buy VALUES(9, 'APN', '청바지', '패션', 3800, 1);
INSERT INTO buy VALUES(10, 'MMU', '지갑', NULL, 1500, 1);
INSERT INTO buy VALUES(11, 'APN', '모두의 sql', '서적', 150, 1);
INSERT INTO buy VALUES(12, 'MMU', '지갑', NULL, 300, 4);

-- member 테이블 조회
SELECT * FROM members;

-- buy 테이블 조회
SELECT * FROM buy;

 

-- member 테이블 조회
SELECT * FROM members;

출력 >>>

-- buy 테이블 조회
SELECT * FROM buy;

출력 >>>

---

 

# DB 데이터 베이스

• 논리적으로 연관된 데이터를 모아 일정한 형태로 저장
• 응용 시스템들이 공용으로 사용하기 위해 통합, 저장한 데이터 집합

 

# DBMS 데이터 베이스 메니지먼트 시스템

• 데이터 베이스 관리 프로그램
• DBMS 를 이용하여 데이터 입력, 수정, 삭제 등의 기능 제공

 

# ISOS

• 통합데이터 : 데이터 중복 최소화
• 저장데이터 : 컴퓨터가 접근할 수 있는 매체에 저장된 데이터
• 운영데이터 : 조직의 고유한 업무 수행시 필요한 데이터
• 공용데이터 : 여러 응용 시스템이 공동으로 소유, 유지하는 데이터

 

# 계층형 DB

• 부모와 자식이 트리 관계를 이루는 구조
• 부모는 여러자식 레코드 가짐
• 데이터 중복 발생이 쉬움
• 수정이 어려움 (상하 종속관계라서)
• 현재는 거의 사용 X

 

# 네트워크형 DB

• 데이터를 노드로 표현
• 노드는 네트워크상에 있으며 서로 대등
• 계층형의 단점인 중복 상하종속 관계 해결
• 구조가 복잡하여 변경, 운영이 어렵고 종속성 문제 발생

 

# 키 - 값 DB

• NoSQL의 한 종류, 키 - 값을 일대일 대응한 데이터 저장
• 데이터 중복 발생, 비정형 데이터 저장에 유리

 

# 관계형 DB

• 데이터를 열, 행으로 구성 > 테이블로 정리
• 고유키는 각 행을 식별
• 데이터는 행 단위 저장, 각 항목의 속성은 열이라고 함
• 테이블 간의 관계를 이용해 데이터 정의
• 행 : ROW / 열 : COLUMN

 

# SQL

 

▶ 데이터 분석 과정

문제인식 > 데이터 수집과 가공 > 데이터 분석 > 분석결과 실행

 

▶  정형데이터

• 틀이 잡혀 있는 데이터, 체계화
• 높은 안정성, 유연하지 못한 구조
• 금융, 제조등 업무용 데이터

▶  비정형데이터

• 틀이 없고 사전정의 없음
• 다양하고 방대한 양, 별도의 분석 처리 기술 필요
• 텍스트, 이미지, 음원, 빅데이터 등

▶ SQL(Structured Query Language)

• 관계형 데이터베이스 관리 시스템(RDBMS)에서 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어
• 데이터베이스에 데이터를 저장, 수정, 삭제, 검색하는 등의 작업을 수행

SQL ≠ DATABASE

 

# SQL 명령문의 종류

• DQL : Data Query Language(질의어)
• DML : Data Manipulation Language(데이터 조작어)
• DDL : Data Definition Language(데이터 정의어)
• TCL : Transaction Control Language(트랜젝션 처리어)
• DCL : Data Control Language(데이터 제어어)

# 유형별로 정리한 SQL 명령문

 

DQL - SELECT문

• SQL에서 테이블에 저장된 데이터를 조회하는데 가장 기본적인 문법이다.
• 예) 테이블명이 DEPT인 부서 정보의 데이터를 조회하는 쿼리문SQL> SELECT * FROM DEPT;

-- 현재 내가 작업하고 있는 공간을 의미
show user;

-- select 문 사용하기

select * 
from members;  -- from 이후에 붙는 테이블 명에 해당하는 테이블 값을 불러옴

select * 
from jobs;

select * 
from employees;

select * 
from buy;

select mem_name 
from members;   -- 테이블의 특정 컬럼을 불러올 때

select mem_name, phone1 
from members;   -- 다중 선택한 컬름을 불러올 때

select mem_name, phone1, debut_date 
from members;

-- 다중의 테이블에서 각각 컬럼 가져오기(컬럼이름이 다를때는 가능)
select mem_name, prod_name 
from members, buy;

-- 컬럼의 이름이 같을 때는 불러오려는 테이블을 정확히 명시
select mem_name, prod_name, buy.mem_id 
from members, buy;

-- 컬럼의 혼동을 예방하기 위해서는 테이블을 명확하게 해주고 값을 불러오는 것이 좋음
select members.mem_name, buy.prod_name, buy.mem_id 
from members, buy;

-- 약자를 사용해서도 불러오기 가능
select m.mem_name, b.prod_name, b.mem_id 
from members m, buy b;

---

실습 내용

SHOW USER;

SELECT MEM_NAME,ADDR,DEBUT_DATE 
FROM MEMBERS;

SELECT * 
FROM MEMBERS 
WHERE ADDR = '서울';

SELECT * 
FROM MEMBERS 
WHERE MEM_NAME = '트와이스';

SELECT *
FROM MEMBERS 
WHERE phone1 = '02';

-- 오름차순, 내림차순 정렬 ORDER BY 열이름 ASC OR DESC
SELECT *
FROM MEMBERS
ORDER BY mem_number DESC;

SELECT *
FROM employees;

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM employees;

-- 중복 출력값 제거하기 DISTINCT
SELECT DISTINCT JOB_ID
FROM EMPLOYEES;

SELECT DISTINCT FIRST_name
FROM EMPLOYEES;

-- 열에 별칭을 지정해서 출력하기(AS 이후에 지정한 이름으로 변수명이 출력됨)
SELECT EMPLOYEE_ID AS 사원번호, FIRST_NAME AS 이름, LAST_NAME AS 성
FROM employees;

-- 두개의 열을 붙여서 출력하는 방법
SELECT EMPLOYEE_ID, FIRST_NAME||LAST_NAME
FROM employees;

-- 여러개의 열을 붙여서 출력하는 방법(가운데 공백주기, 문자열 붙여서 출력)
SELECT EMPLOYEE_ID, FIRST_NAME||' '||LAST_NAME,
       email||'@'||'COMPANY.COM'
FROM employees;

-- 변수명 변경까지 적용하기
SELECT EMPLOYEE_ID AS 사원번호, FIRST_NAME||' '||LAST_NAME AS 이름,
       email||'@'||'COMPANY.COM' AS 사원메일
FROM employees;

-- 산술처리: 데이터 값 계산하기 
-- 값만 찍어서 출력해주는 것임, 원본데이터엔 변화없음
SELECT EMPLOYEE_ID, salary, salary+500, salary-100, (SALARY*1.1)/2
FROM employees;

SELECT EMPLOYEE_ID AS 사원번호,
salary AS 연봉,
salary+500 AS 추가급여,
salary-100 AS 인하급여,
(SALARY*1.1)/2 AS 조정급여
FROM employees;

SELECT PROD_NAME AS 제품이름,
PRICE AS 가격,
AMOUNT AS 수량,
PRICE * AMOUNT AS 합계,
(PRICE * AMOUNT)*0.85 AS "할인가격15%"
FROM BUY
ORDER BY AMOUNT ASC, 합계 DESC, "할인가격15%" DESC;

SELECT DISTINCT MEMBERS.ADDR, PHONE1||'-'||PHONE2 AS 전화번호
FROM MEMBERS;

SELECT d.department_id AS 부서ID, d.department_name AS 부서이름, 
j.min_salary AS 최소연봉, j.max_salary AS 최대연봉, 
c.country_name AS 국가이름
FROM DEPARTMENTS D, JOBS J, COUNTRIES C;