기본 문법 정리
# sql burffer? 명령어 ed
전체 순서
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
<현재 날짜>
select sysdate
2 from dual;
<conn ID/PW>
DB에 접속/.
conn hr 계정 /비밀번호
<ed_명령어>
sql buffer -> 명령 실행 목록 ( ; -> / 변경)
<desc_명령어>
테이블에 구조를 출력
<column department_name format a17>
coumn -> 출려되는 컬럼에 크기를 조절
format -> 이후 형식 (a17 -> 문자,날자 17개)
-> 숫자는 '9'로 사용되며 개당 갯수
format 자리에 clear 입력시 초기화
<select>
selection -> 모든 항목 출력 <'*'>
projection -> 일부 항목만 검색<column_name, column_name>
join ->
# select 산술 연산 가능 단 숫자, 날짜 만 가능
-> NULL 산술 연산시 출력 NULL로 변경
<nvl(column, x)>
NULL이 아닌 값들은 x를 column 대신 연산에 사용
(alias) <as name>, <공백 name(단일문장)>, <"복수문자 및 원본포멧 출력">
as 뒤에 이름으로 컬럼 머리글 출력 (as 생략 가능)
as 생략시 성능 저하, 항상 대문자로 출력됨
"name" 큰따음표로 묶을시 문자열( 소문자) 그대로출력
<name || number>
연결 연산자 -> nmae + number 되어 출력
<name || 'is name'> #(작은 따음표)
리터럴 문자 -> name column에 있는 값 뒤에 is name이 붙어서 출력됨
select문에 기본적인 명령어를 제외한 모든 문자들
그대로 출력되는 특징이 있음
<distinct column>
중복 컬럼 제거
#distinct column, column > 그룹화 되어서 멀티플 중복 제거
처음 커럼 기준 그룹화, 두번째 컬럼 기준 중복 제거
<where column = x(조건)>
조건절 or 행을 제한하는 절, 컬럼값이 동일한 값들만 출력
# from절 이후 where절 실행후 select절이 실행되므로 alias명 사용 불가
# where department_id = 90 여기서 90이 리터럴 문자
-> 값이 문자, 날짜인 경우 반드시 'val' 작은 따음표 사용 단 숫자는 사용하지 않음
#연산기호는 다른 기호와 같음
1. '<>' 와 '!=' 는 같지만 '<>'보다는 '!=' 가 성능상 좋다
2. BETWEEN A AND B : A와 B 사이 (문자, 날짜 작은 따음표, 숫자는 필요없음)
3. IN(set) : 값 몰록 주으이 값과 일치(복수 연산자) -> ex[department_id in (20, 50)]
4. LIKE : 문자에 같은 문자가 포함되어 있는지 검사 ('%'문장 전체, '_' 문자 하나, '#'숫자 하나)
-> '='에서는 사용 불가능 꼭 LIKE에서만 사용가능 format 주의 ex) where column like 'a_%'
5. IN NULL : 널 값 검사 <---? is not null
6. AND, OR, NOT사용 가능 단 IS NOT NULL, 크기비교 NOT사용 불가
-> 범위 지정 필요할시 BETWEEN 사용
<ORDER BY>
컬럼 정렬 # ASC, DESC
ASC 오림차순, 기본설정으로 생략 가능
DESC 내림차순
컬럼 순서대로 숫자로도 사용 가능
컬럼에 as 별명 이 아니라 "별명" 이라는 alias를 사용했다면 order by 에서도 "별명" 사용해야함
멀티플 정렬 : 그룹화 하여 정렬, SELECT절에 선언된 순서대로 정렬됨
ex)
SELECT department_id, sum(salary)
FROM employees
GROUP by department_id
ORDER BY department_id ASC
# (join)조인
조건 : 조인을 하기 위해 테이블에 데이터를 서로 맞추는 조건
-> 조건이 없을시 가능한 모든 조합을 만들어버림 (카티션 프로덕트)
-> n개 테이블 결합시 반드시 n-1개 이상에 조인 조건이 명시되어 있어야함
# 오라클 전용(이거 사용) <---->SQL:1999
등가 조인 : 같은 데이터를 조건으로 조인 (기본키, 외래키로 연결되어 있는 테이블들에 사용 )
-> 컬럼명이 동일할때 어떤 테이블 소속인지 명시해야함 ex(employees.department_id)
-> 성능상 어떤 테이블 소속인지 명시하는게 좋음
-> 테이블명 arias 사용 가능 > 컬럼에 소속 테이블명을 대신해서 사용 가능,
ex : select e.last_name, d.department_name, e.department_id
from employees e, departments d
-> 조건을 추가할때 AND로만 묶어야함 <--> OR 사용시 조인 조건이 일반 조건이 되어버림
OUTER연산자 : <(+)>
->OUTER조인 : null값이 존재하면 조인할 테이블에 null행을 생성하고 조인함
ex : WHERE e.department_id(+)=d.department_id
비등가 조인 : 데이터가 같지 않는 조건으로 조인
포괄 조인 :
자체조인 : 1개 테이블을 이용해 조인
ex : SELECT e.employee_id, e.last_name, e.manager_id, m.last_name
FROM employees e, employees m
WHERE e.manager_id = m.employee_id
# 테이블명 arias 사용 가능 > 컬럼에 소속 테이블명을 대신해서 사용 가능
ex : select e.last_name, d.department_name, e.department_id
from employees e, departments d
# 그룹함수<GROUP BY column>
-> 데이터 타입 주의,[ ALL(생략), DISTINCT 괄호 안에 사용 가능]
-> NULL행 무시>NVL함수 사용 # count(*)는 모든 행을 따지므로 null 인식
ex) SELECT AVG(NVL(commission_pct,0))
-> GROUP BY절 사용시 그룹함수 WHERE절 사용불가 > WHERE 대신 HAVING 사용
AVG : 평균 , 숫자데이터 타입
COUNT
MAX : 최대 , 모든 데이터 타입(아스키코드 기준)
MIN : 최소 , 모든 데이터 타입(아스키코드 기준)
SUM : 합, 숫자 데이터 타입
STDDEV : 표준편차
VARIANCE : 배열
# 서브 쿼리
-> select 내부에 새로운 select 선언 > 중복해서 서브 쿼리 선언 가능\
-> FROM, WHERE, HAVING 절에서 사용 가능
-> ORDER BY절은 Top-N을 수행하지 않는 이상 사용을 궈장하지 않음
-> # 단일 행 서브 쿼리에는 단일 행 연산자를 사용 하고 다중 행 서브 쿼리에는 다중 행 연산자로 사용합니다. > 단일 : 값 1개 반환, 다중 : 값 n개 반환//
-> 다중 행 서브 쿼리 : 연산자 정리>>>>>>>
ANY (X, Y, **) : 복수 입력 대이터중 가장 최소값보다 큰값을 찾음
ALL (X, Y, **) : 복수 입력 대이터중 가장 최대값보다 작은값을 찾음
IN (X, Y, **) : 입력 대이터와 동일한 값을 찾음
EX) (10, 20, 30, 40, 50, 60)들중
ANY(10,30,40) > 10보다 큰값 출력
ALL(10,30,40) > 40보다 작은값 출력
IN(20,30) > 20, 30값과 동일한 값 출력
<<< DML>>>
INSERT INTO 컬럼 VALUES(값) : 행단위
DELETE : 행단위
UPDATE (컬럼) SET(값) : 컬럼 단뒤
트랜잭션은 논리 작업 단위를 형성하는 DML 문의 모음으로 구성
-> INSERT, DDL, DCL > AUTO COMMIT
-> DML은 문장 전체가 트랜잭션 > 진행중인 COMMITㄷ
<INSERT INTO TABLE_NAME(COLUMN) VALUES(데이터) >
EX) INSERT INTO
VALUES(데이터)
* 데이터 추가시 맨 마지막 행에 추가
* 선언한 컬럼 순서에 맞춰서 데이터 순서도 동일해야함
-> 테이블에 입력된 컬럼에 순서와는 무관
* 선언되지 않은 컬럼에 행은 NULL, VALUES에 NULL 산입
*** 무결성 검사 1.column무결성, 2.제약조건 무결성, 참조 무결성
---> 키본키, 외래키 변경시 삭제 오류
select sysdate < 현재 날짜>
from dual;
insert시 sysdate를 values에 입력
<치환변수>
values (%출력이름)
{읽기 일관성}
트랜잭션이 commit되지 않았다면 트랜잭에서 수행된 연산을
볼수 없게함
{ 병행제어} -> 동시성
-> 트랜잭션이 실행되면 접근하는 행을 LOCK
-> 다른 트랜잭션 진행중이라면 대기상태가됨
-> 트랜잭션 크기는 큰거 < 작은거 성능 좋음
>>>>>>>>>>>>COMMIT 명령어 필수
<<<데이터베이스 객체>>>
테이블
뷰
시퀀스
인덴스
<<<DDL>>> 구조
-> 트랜잭션 수행시 테이블 전체 LOCK
<CREATE table table_name
(컬럼이름 데이터형(길이), 컬럼이름 데이터형(길이), )>
테이블 생성
<데이터 유형>
VARCHAR2(size) : 가변길이 문자, 저장공간 최적
CHAR(size) : 고정길이 문자, 처리 성능 최적
NUMBER(p, s) : 가변 숫자
->NUMBER[256자리], NUMBER(10)[10자리], NUMBER(a, b)[a와 소수점b]
DATE : 날짜 및 시간 값 > 운영체제에 설치된 언어에 따라서 달라짐
-> TIMESTAMP
ROWID : 인덱스
<테이블에 컬럼 추가>
ALTER TABE 테이블 _이름
ADD (컬럼명 데이터형(크기))
<테이블에 컬럼 데이터형 변경>
ALTER TABE 테이블 _이름
MODIFY (컬럼명 데이터형(크기)) > 기존에 있는 데이터 길이, 형태에 주의
<테이블에 >
ALTER TABE
테이블 _이름
DROP column 컬럼명
**** DROP 명령어 (DATE, CLOUMN, 저장공간) 같이 삭제
----> alter table dept
set unused (loc);
활성화된 column을 비활성화로 설정
alter table dept
drop unused columns;
<delete>
행단위 삭제
<drop>
구조, 데이터 모두 삭제
<trumcate>
테이블에 모든 데이터를 지우는 명령어
공간 회수
DEL
UPDATE
(기본키, 외래키)
constraint
check
not null
c
(명령어 모음)
<save 파일이름.sql>
<@ 파일 이름.sql>
파일 경로 : C:\oraclexe\app\oracle\product\11.2.0\server\bin
<<<view>>>
create view 뷰 이름
as select ~ 서브쿼리
-> 뷰는 물리적으로 저장되지 않고 텍스트로 저장되며 select시
참조하는 table select를 실행해서 정보를 가져옴
제거 : drop view 뷰이름
<<인라인뷰>>
select 내부 from절 내부에 서브쿼리 사용
<< Ton-N분석 >>
ROWNUM 와 인라인뷰를 같이 사용하는것
ROWNUM : 출력된 행에 순서를 출력 # 단 행이 출력이 되어야 값을 검사할수 있음
ex)
1 select rownum, employee_id, salary
2 from employees
3 where rownum > 4
이라면 결과값이 없음
예제 : 부서별 평균 연봉 최상위 3개 출력
select rownum, department_id, d_salary
from (select department_id, avg(salary) d_salary
from employees
group by department_id
order by d_salary desc)
where rownum < 4
/
<<<시퀀스 >>>
create sequence 이름
increment by 10
start with 120
maxxvalue 9999
nocahe
nocycle;;
(cycle) 이거 사용하면 숫자가 다시 돌아서 사용 안함
<생성 예제>
create sequence dept_deptid_seq
increment by 10
start with 300
maxvalue 9999;
<사용 예제>
insert into departments
values ( dept_deptid_seq.nextval, 'IT', 100, 1700);
시퀀스는 공유 가능한 객체 사용중 다른 사용자가 있다면 공백생김 또는 Rollback
<수정 예제>
alter sequence dept__deptid__sed
increment by20
~
~
<삭제 예제>
drop sequence 이름
<< 초기 hr 계정 권한 변경 및 사용 방법 >>
alter user hr
identified by 비번;
alter user hr
account nulock;
<<< 유저 관리 >>>>
<유저 생성>
create user 이름
identified by 비번;
~~ 자원 관리 옵션
<<유저 권한>>
< grant 허용 옵션 to 사용자 이름>
ex) grant create session to test > 접속 권한
grant create table to test > 테이블 생성 권한
grant connect, resource to test > 저장 권한
<revoke 권한 옵션 from 사용자 이름;>
revoke connect, resource from test; > 저장권한 취소
<권한 관리>
create role manager > 그룹 생성
grant a,b to manager > 그룹 권한 지정
grant manager to test, test1 > 그룹 권한을 사용자 권한으로
>>>>>>>>이후 자동으로 그룹에 변경되는 권한을 사용자에게 반영함
grant c,d to manager
revoke d from maner
#롤 권한
# 객체 권한
grant (select ~~~) on c, d to manager > 메니저에게
#권한 전도? 전달?
with grant 0ption 을 사용하면 가능하다
(시스템 권한 이동) : with admin option 사용XX 하면 안됨
