자료형

Oracle

1. DQL

1-1.

select * from session_privs;
select * from user_sys_privs;
select * from user_tab_privs;
select * from user_users;
select * from user_ts_quotas;
select * from user_tables;

1-2.

select * from employees where employee_id=100;

 

2. DML(더 있긴하지만, 이정도만)

2-1.

insert into emp(employee_id, last_name, salary)
values(100,'원대훈',1000000);
2-2. 열을 바꿈
update emp
set last_name ='바보'
where employee_id=100;
2-3. 행을 삭제함
delete from emp
where employee_id=101;

2-4.

merge into dw d

using oltp o

on (d.employee_id = o.employee_id)

when matched then

  update set 

  --d.employee_id = o.employee_id, 이건 들어가면 오류남

  d.last_name = o.last_name,

  d.salary = o.salary*12

  delete where o.flag='x'

when not matched then

  insert (d.employee_id, d.last_name, d.salary)

  values (o.employee_id, o.last_name, o.salary);

 

3. TCL

3-1.

commit;

rollback;

savepoint a;

rollback to a;

3-2.

DML실수 후 commit 시 
select * from emp
as of timestamp to_timestamp('2019-03-28 13:35:00','yyyy-mm-dd hh24-mi-ss.sssss');--최대15분★

 

4. DDL

4-1.create
4-1-1.ctas
create table emp
as select * from hr.employees; 

create table emp
as select * from hr.employees where 0=1;
4-1-2.

create table emp(id number(8,3), name varchar2(30), day date default sysdate); 

4-2.alter
4-2-1.컬럼
alter table emp add flag number(10);
alter table emp drop column flag;
4-2-2.unused ★
alter table emp set unused(department_id);
alter table emp drop unused column;

4-3.rename
rename emp to emp_new;
alter table emp_new rename column employee_id to emp_id;
alter table emp_new rename constraint emp_empid_pk to empid_pk;
alter index emp_empid_idx rename to empid_idx; ★

4-4.drop
4-4-1.
drop table emp;
drop table emp purge;
drop table emp cascade constraints purge;
4-4-2. 복구/영구삭제
show recyclebin;
select * from recyclebin;
select * from "BIN$mJH1pueaT1WQDNiUupYfrg==$0";
flashback table emp to before drop;
flashback table emp to befor drop rename to emp1;
flashback table "BIN$mJH1pueaT1WQDNiUupYfrg==$0" to before drop;
purge recyclebin;
purge table emp;
purge table "BIN$mJH1pueaT1WQDNiUupYfrg==$0";

4-5. truncate
truncate table emp;

 

4-6. 제약조건
4-6-1.
select * from user_constraints where table_name='EMP';
select * from user_cons_columns where table_name='EMP';
4-6-2.테이블
create table dept(
deptid number(10) constraint dept_deptid_pk primary key,
dname varchar2(30) constraint dept_dname_nn not null,
mgrid number(10) constraint dept_mgrid_fk references emp(employee_id),
locid number(10) ,
constraint dept_mgrid_ck check(mgrid > 0));
drop table dept purge;
4-6-3.열
alter table emp add constraint emp_empid_pk primary key(employee_id);
alter table emp drop constraint emp_empid_pk;
alter table emp add constraint emp_deptid_fk foreign key(department_id) references dept(department_id);
alter table emp add constraint emp_sal_ck check(salary>0);
alter table emp add constraint emp_email_uk unique(email);
alter table emp modify last_name constraint emp_lastname_nn not null;
alter table emp modify last_name constraint emp_lastname_nn null; 

4-7. 인덱스
4-7-1.
select * from user_indexes where table_name='EMP';
select * from user_ind_columns where table_name='EMP';
4-7-2.
create unique index emp_empid_idx on wondae.emp(email); --unuque 인덱스 ★
drop index emp_empid_idx;
create index emp_empid_idx on wondae.emp(email); --non-unuque 인덱스
drop index emp_empid_idx;
create unique index emp_empid_idx on wondae.emp(manager_id, flag); --조합인덱스
drop index emp_empid_idx;

 

5. DCL

dba 권한
grant create session to wondae;
revoke create session from wondae;
grant create table to wondae;
revoke create table from wondae;
grant create sequence to wondae;
revoke create sequence from wondae;
grant create view to wondae;
revoke create view from wondae;
테이블 소유자 권한
grant select on hr.employees to wondae; ★
revoke select on hr.employees from wondae;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

R 자료형
1. vector
1-1.생성
x<-c(10,20,c=30,d=40,e=50,60)
y<-c(100,200,300,400)
1-2.검색
class(x)
str(x)
x[,]
x[1];x[2] #1개추출
x[c(2,3)] #n개이상추출 
x[length(x)] #맨뒤1개추출  
x[length(x)-1:0] #맨뒤2개추출

x<-c(10,20,30,40,50) #★맨뒤2개추출
x[-c(1:(length(x)-2))]

x[length(x)-1:1] #맨뒤한칸왼쪽거 1개추출   
x[3:6]
x[6:3] #순서 거꾸로
x[-4:-2] #인덱스 2~4를 뺀 나머지
x[-2:-4]
1-3.추가
x[5]<-50
x<-append(x,45,after=4)
x[length(x)+1]<-70
x<-append(x,100,after=length(x))
append(x,y)
1-4.수정
x[2:3]<-NA
names(x) <-c('a','b','c','b')
names(x)[5]<-'e'
1-5.삭제
x<-x[-3]
x<-x[1:length(x)-1]
x<-NULL

2. list
2-1.생성
x<-list(name='홍길동',addr='서울시',전화번호='010-0000-0000')
2-2.검색
class(x)
str(x)
x$name;x$addr;x$전화번호
names(x)
x[1]
x[[1]]
x[1:2]
2-3.추가
x$sal<-10000 #키추가
2-4.수정
names(x)[1]<-'주소'
x[2]<-NA
x$name<-'윤건' ; x$addr<-'부산시' ; x$전화번호<-''
2-5.삭제
x$name<-NULL ; x$addr<-NULL ; x$전화번호<-NULL

3. matrix
3-1.생성
x<-matrix(c(1:9),nrow=3,dimnames=list(c('r1','r2','r3'),c('c1','c2','c3')), byrow=FALSE)
3-2.검색
x[,1,drop=FALSE]
x[c(3,7)]
x[,2]
x[2,c(1:3)]
dimnames(x)
rownames(x)
colnames(x)
3-3.추가
3-4.수정
x[c(3,6)] <- c(30,60)
x[,1] <- c(100,200,300)
dimnames(x) <- list(c('r1','r2','r3'),c('c1','c2','c3'))
rownames(x) <- c('r1','r2','r3')
colnames(x) <- c('c1','c2','c3')
3-5.삭제
x<-x[,-1]
x<-NULL
3-6.연산
x%*%x
t(x)
solve(x)

4.array
4-1.생성
x<-array(c(1:12),dim=c(2,2,3),dimnames=list(c('r1','r2'),c('c1','c2'),c('p1','p2','p3')))
4-2.검색
str(x)
x[,,1]
x[2,,1]
x[,1,,drop=FALSE]
4-3.추가
이상함...
4-4.수정
x[2,,2]<-c(100,200)
4-5.삭제
x<-x[,1,,drop=FALSE]

5.factor
5-1.생성
x<-factor(c('m','f','f','m'),levels=c('f','m'))
x<-factor(c('good','bad','soso','good'),levels=c('bad','soso','good'),ordered=TRUE)
5-2.검색
str(x)
levels(x)
levels(x)[1]
nlevels(x)
x[c(1,2)]
5-3.추가
x<-as.vector(x) ★ x를 vector로 풀어줘야함
x<-append(x,c('good','bad'))
x<-factor(x,levels=c('bad','soso','good'),ordered=TRUE)
5-4.수정
x[1] <- 'bad'
x[1] <- 'hi' #★ 없는 값은 넣을 수 없다
5-5.삭제
x<-NULL
x<-x[-c(1:2)]

6. data.frame
6-1.생성
x<-data.frame(name=c('kim','hong','won'),sql=c(90,100,80),r=c(100,90,90),python=c(70,80,100))
x<-data.frame(name=c('kim','hong','won'), sql=c(90,100,80), r=c(100,90,90), python=c(70,80,100), stringsAsFactors = FALSE)★
6-2.검색
x[1,]
x[,2,drop=FALSE]
x[,c('sql','python'),drop=FALSE]
x[-1,-2]
x$name ; x$sql ; x$r ; x$python
NROW(x)
length(x)
6-3.추가
x<-cbind(x,excel=c(60,60,70))
x<-rbind(x,data.frame(name='hwang',sql=100,r=100,python=100,excel=100))★
6-4.수정
x[1,c(1,2)]<-c('sangkyun',100)
6-5.삭제
x$excel<-NULL
x<-x[,-3]
x<-NULL

 

 

 

python 데이터형

1.리스트

1-1. 생성

x=[]

x=[10,]

x=[10,20,30]

x=[10,20,[30,40,50],60,70]

1-2. 검색

x[0]

x[-1]

x[:]

x[0:3]

x[-2:]

x[2][1:2]

x[::1]

x[::2]

x[::-1]

1-3. 추가

x=[200,300,400]

y=[60,70,80]

1-3-1 list + list

x*2 #x가 2개 출력 ★

x+y

x.extend(y)

x=[x.append(i) for i in y]

1-3-2 list + 문자열

x.append(1000)

1-3-3 list + 문자열 (위치지정)

x.insert(5,10000)

x

1-4. 수정

x[0]=999

x[1:3]=[111,222,333]

 

1-5. 삭제

x.remove('콜라') #1개씩 지움

del x[0]

del x

x.clear()

x.pop()

 

 

2.tuple

2-1.생성

x=()

x=(10,)

x=(10,20,40,80,160)

x=(10,20,(30,40,50),100)

2-2.검색

리스트형과 같다

2-3.추가/수정/삭제

제한되지만 다음과 같이 다시 변수에 넣는 방법으로 하면 어느정도는 된다

x=x+(10,20)#추가

x=x[0:2]+(1000,)+x[3:] #수정 : 삭제 후 추가★

x=x[1:len(x)-1]#삭제

 

 

3. dictionary

빈도수 체크에 좋음

3-1. 생성

x={}

x={'사과':5,'포도':6,'바나나':10}

x={'사과':[5,10,100],'포도':6,'바나나':(10,20,30)}

3-2. 검색

x['사과']

x.get('문도') #없으면 에러가 아닌 None 리턴함 ★

x.keys()

x.values()

x.items()

for k,v in x.items():

print(k,v)

3-3. 추가

x['망고']='맛있다'

3-4. 수정

x['사과'][1]=999

x['바나나']=(100,200,300,400,500)

3-5. 삭제

del x['사과'][1]

x['사과']=[]

x['사과'].clear()

del x['망고']

x.clear()

del x

3-6. 리스트형으로 변형

list(x)

list(x.keys())

list(x.values())

list(x.items()) #리스트 안에 튜플로 됨

 

 

4.set

4-1.생성

x=set()

x={10,1,2,20,60,8000,1,'a','p'}

x=set(['a',10,10,10,20,30,40,10,20,30,60,90,100,'a','b','c','a'])

y=set(['b','c',10,50,70,100])

4-2.검색

x

4-3.추가

x.add('hi') ★

4-4.수정

#삭제 후 추가해야함

4-5.삭제

x.remove('a') ★

4-6.집합연산 ★

x.intersection(y)#교

x&y

x.difference(y)#차

x-y

x.union(y)#합

x|y

x.symmetric_difference(y)#순수 x와 y에만 있는 값

(x-y)|(y-x)

 

 

 

 

5. Series (R의 vector와 비슷)
import pandas as pd
1.생성
pd.Series([10,20,'가','나'])
pd.Series([10,20,['가','나','다']])
pd.Series([10,20,30,40],index=['a','b','c','d'])
pd.Series((10,20,30,40))
dic=pd.Series({'a':10,'b':20,'c':30,'d':40,'e':50})
x=pd.Series(dic,index=['a','b','c']) #부분만뽑기★
x.name='갯수'
x.index.name='알파벳'
2.검색
x.dtype
x.values
x.index
x.values[-1]
x.index[0:2]
x[x.values==10]
x[x.index=='c']
x[0]
x[[0,2]] #r처럼 되네
x[x>15]
'c' in x
3.추가
x['g']='hihi'
x['z']=[1,2,3,1,2,3,8]
4.수정
x=x.astype('int')
x=x.astype('float')
x=x.astype('object')
x['a']=100
5.삭제
del x['z']
del x
6.연산
x=pd.Series({'a':10,'b':20,'c':30})
y=pd.Series({'a':10,'c':20,'f':30})
x+10 #x-10
x*2 #x/2
x//3
x%3
z=x+y #결과 : 20,nan,50,nan ★
    #같은 인덱스끼리 더함
7.np.nan
pd.isnull(z['b']) #True
pd.notnull(z['b']) #False


6. DataFrame (R의 data.frame과 비슷) 
import pandas as pd
import numpy as np
1.생성
df = pd.DataFrame([[1,2,3],[10,20,30],[100,200,300]],
                  columns=['c1','c2','c3'],
                  index=['r1','r2','r3'])
df = pd.DataFrame({'c1':[1,2,3],'c2':[10,20,30],'c3':[100,200,300]},
                   index=['r1','r2','r3'])
df = pd.DataFrame(np.arange(15).reshape(5,3),
                  index=[str(i) for i in range(2012,2017)],
                  columns=['a','b','c'])
df.name='제목'
df.index.name='인덱스이름'
2.검색
df.dtypes
df.loc['r1'].dtypes
df.info()
df.index
df.columns
df.values
df['c1']
df[['c3','c2']]
df.loc['r1']
df.loc['r1':'r3',]
df.loc[:,'c2':'r3']
df.iloc[:,0]
df.iloc[1,:]
df.iloc[1,1]
df.iat[1,2]
df.at['r1':,'c1']
df.loc[df['c2']<15,['c1','c2']] #인덱싱의 핵심 ★
df[['c1','c2']][df['c2']<15]    #인덱싱의 핵심 ★
3.추가
df['c4']=[1000,2000,3000]
x=pd.Series([4,40,400],index=['c2','c1','c3']) #series로 추가★
df.loc['r4']=x #인덱스에 맞게 추가됨 ★
df2=df.append(df) #열방향으로 append하게 추가됨 ★
pd.concat([df,df],axis=0) #행기준 열방향 추가 ★
pd.concat([df,df],axis=1) #열기준 행방향 추가 ★
4.수정
df.iloc[0:3,1] = [11,22,33]
df.iat[1,2]=20000
df.at['r1':,'c1']
df.index=['r10','r20','r30']
df=df.rename(index={'r1':'r10'}) #df.columns[0]='c10' 안됨 ★
df=df.rename(columns={'c1':'c10'})
5.삭제
df1 = df.iloc[0:3,0:3]
del df['c3']
del df
df=df.drop(['r4','r2'],axis=0)#행삭제★
df=df.drop(['c1','c3'],axis=1)#열삭제★
6.기타
df.T#전치행렬

 

 

 

7. arange

 

 

import numpy as np
1.생성
1)
np.arange(6)
np.arange(5,9)
np.array([0,1,2,3,4,5])
np.array((0,1,2,3,4,5))
np.array([[0,1,2],[3,4,5]])
np.arange(6).reshape([2,3])
np.arange(6).reshape([2,3], order='C')
np.arange(6).reshape([2,3], order='F')
2)
np.zeros((3,3))
np.ones((3,3))
np.full((3,3),2)
np.eye(3)

2.조회
1)
x.dtype
x.shape
2)인덱싱,슬라이싱
x[:,1]
x[0,]
x[:,-3]

3.수정

4.삭제

5.연산
x=np.array([1,2,3])
y=np.arange(9).reshape(3,3)
#broadcasting
x+y#np.add(x,y)
x-y#np.subtract(x,y)
x*y#np.multiply(x,y)
x/y#np.divide(x,y)
np.dot(x,y) #행렬의곱
y.T #전치행렬, pandas.DataFrame도 됨

np.sum(y)
np.sum(y,axis=1)
np.mean(y,axis=0)
np.var(y,axis=0)
np.std(y,axis=1)
np.min(y,axis=1)
np.max(y,axis=0)
np.argmin(y)
np.argmax(y)
np.cumsum(y)
np.cumsum(y,axis=1)
np.cumprod(y,axis=0)

6. 기타
np.random.rand(10)
np.random.rand(3,5)
np.random.randn(5)
np.random.randn(3,5)
np.random.randint(10,size=10)
np.repeat(3)
x.repeat([2,3,4])
x.repeat(2,axis=0)
np.tile(x,2)

np.unique(x)
data1 = np.arange(0,20,2)
data2 = np.arange(0,30,3)
np.maximum(data1,data2)
data3 = [1,2,3]
data4 = [1,2,3]
np.setdiff1d(data3,data4)
np.setdiff1d(data1,data2)