OraCle

OraCle

SQL문의 종류

DDL : create, alter, drop (이전작업내용까지 자동으로 commit되므로 rollback이 안됨)

DML : insert, update, delete

DCL : grant, revoke

TCL : commit, rollback

query : select

select table_name,constraint_name,constraint_type,search_condition from user_constraints where table_name in ('TABLE_NAME','TABLE_NAME');

- Table의 Key를 조회하는 명령

- constraint_type : C - Not Null , p - Primary key , R - Foreign key

privileges(권한)

system privs - create session, create table

object privs - select, insert

SQL> select * from user_sys_privs;

권한 검색 명령

SQL> create user USER_NAME;

user생성

SQL> identified by PASSWORD;

user password 부여

SQL> default tablespace TABLESPACE_NAME;

user별 작업공간

SQL> temporary tablespace TEMP_TABLESPACE_NAME;

sort등과 같은 작업을 위한 공간

SQL> quota SIZE on TABLE_SPACE_NAME;

user작업공간 size : 10m on tablespeace_name

SQL> profile PROFILE_NAME;

user작업 log

SQL> grant create session, create table to USER_NAME;

SQL> grant create session, create table to USER_NAME with grant option;

자신이 받은 권한을 타인에게도 줄수 있슴, object 권한만 가짐.

SQL> grant create session, create table to USER_NAME with admin option;

자신이 받은 권한을 타인에게도 줄수 있슴, system 권한까지 가짐.

ingle row (단일 행 함수)

Date type function

last_day : select last_day(sysdate) from dual;

months_between : select months_between(sysdate,sysdate-60) from dual;

sysdate : select sysdate from dual;

next_day :

- select next_day('19990805',7) review from dual;

- select next_day(sysdate,7) from dual;

// 입력일자이후의 처음에 해당하는 요일을 표시함, 0:일,1:월 ~ 7:토
add_months : select add_months(sysdate,6) from dual; //기준일의 입력된 개월후의 일자 표시

months_between : select months_between('19990801','19990101') from dual; // 일자간의 개월수를 구함.

Number type function
- trunc : select trunc(123.456,2) from dual;

- round : select round(123.456,2) from dual;

- mod : select mod(13,4) from dual;

charater type function

substr : select substr('abcdinstringefg',5,8) from dual; // 5번째 문자부터 8글자를 발췌하라.

upper :

lower :

initcap : select initcap('title') from dual; // 첫문자만 대문자료 표기.

lpad : select lpad('xy',12,'ab') from dual;

// 12자리가 될때까지 'ab'를 왼쪽으로 채움. (결과:abababababxy)

// 'ab'를 생략하면 space를 채움.
rpad : select rpad('xy',12,'ab') from dual;

// 12자리가 될때까지 'ab'를 오른쪽으로 채움. (결과:xyababababab)

// 'ab'를 생략하면 space를 채움.
length : select length('홍길동') from dual;

like : select COLUMN_NAME from TABEL_NAME where upper(COLUMN_NAME) = like '%길%';

type conversion function

to_char :

- select to_char(sysdate,'yyyymmdd') from dual;

- select to_char(sysdate,'yyyy-mm-dd') from dual;

- select to_char(00123,'00999') from dual;

- select to_char(123456,'999,999') from dual;

- select to_char('123456','999,999') from dual;
to_date :

- select to_date('980101','yymmdd') from dual;

- select to_date(980505,'yymmdd') from dual;
to_number : select to_number('1234') from dual;

multiple rows (다중 행 함수 - Group 함수)

sum :

- select sum(salary) from s_emp;

- select sum(COLUMN_NAME) from TABEL_NAME;
count :

- select count(salary) from s_emp;

- select count(COLUMN_NAME) from TABEL_NAME;

- select count(distinct, COLUMN_NAME) from TABEL_NAME;

// 중복된 값은 count하지 않는다.
avg :

- select avg(salary) from s_emp;

- select avg(COLUMN_NAME) from TABEL_NAME;
max :

- select max(salary) from s_emp;

- select max(COLUMN_NAME) from TABEL_NAME;
min :

- select min(salary) from s_emp;

- select min(COLUMN_NAME) from TABEL_NAME;
group by :

- select min(salary) from s_emp group by dept_id;

- select min(COLUMN_NAME) from TABEL_NAME group by COLUMN_NAME;

- select last_name from s_emp where salary = (select max(salary) from s_emp);

- select dept_id, salary, last_name from s_emp where salary = (select max(salary) from s_emp group by dept_id);

- select dept_id, sum(salary), count(salary), trunc(avg(salary),0), max(salary), min(salary)

  from s_emp

  group by dept_id

  having max(salary) > 1000;