반응형
1. 소개
1) 기본적으로 생성되는 데이터베이스(4개외에는 따로 생성한 디비들)
- master : 서버 전체에 대한 오브젝들의 정보를 가지고 있는 디비
- model : 이후 생성될 사용자데이타베이스의 모델이 되는 디비
- sybsystemprocs : 스토어프로시져가 쌓이는 디비
- tempdb : 임시공간으로 활용하는 곳
2) pubs2 : 샘플 데이터베이스를 생성하는 스크립터
- UNIX : $SYBASE_HOME/scripts/installpubs2
- NT : $SYBASE_HOME/scripts/instpbs2.sql
3) T-sql
- isql -U로그인아이디 -P패스워드 -s서버 -i스크립터명 -o결과저장화일명
- 표준 ansi-sql에 위배되는지 검사하기
set fipsflagger on/off (검사하기/안하기)
예) select type, avg(price) from titles => 사이베이스는 그냥 반복해서 평균뿌림.
On하면 에러메세지 나오고 내용출력
- isql안에서 유닉스명령을 수행하려면 : !!
예) 1> !!isql -Usa -P -iaaa.sql : aaa.sql 스크립터문을 수행.
- 디비 이동시 : 꼭 go를 밑에 넣어주어야 함.
> use 디비명
> go
- 이전 입력글을 에디팅하여 다시 수행시 :
> vi
- 주석문
> -- sample test 또는 /* 주석 */
4) SQL Advantage client
- 설정 : dedit를 이용하여 서버이름을 정하고 IP주소와 포트를 지정해줌
- 연결 : 접속시 위에서 설정한 서버이름을 선택하고 아이디와 패스워드입력.
2. 테이블 생성 및 수정, 관리
1) 테이블 생성(DDL)
- 250개의 칼럼 지정 가능
- 정수타입 : tinyint, smallint, int, numeric(p,s), decimal(p,s)
예) 123456.789 : numeric(8,2) => 123456.79,
numeric(10) => 123457, numeric(8,-2) => 123500
numeric(8,3) => 에러, numeric(7,2) => 에러
- 실수타입 : float(p), double precision, real
- money타입 : money, smallmoney
- 날짜타입 : datetime, smalldatetime
- 문자타입 : char(n), nchar(n), varchar(n), nvarchar(n), text
예) nchar는 national character로 언어별 문자수를 크기지정
한글버젼의 경우 char(5)=5bytes, nchar(5)=10bytes
cf) select @@ncharsize : 현재 버전의 문자크기 구하기
cf) char,varchar는 max 255, text는 max 2G
- binary타입 : bit, binary(n), varbinary(n), image(2G까지)
- 새로운 타입 지정 ( sp_addtype / sp_droptype )
예) sp_addtype typ_ch_pub_id, "char(4)", null
""를 넣은 이유는 ()가 있기때문
예) sp_droptype typ_ch_pub_id
예) sp_rename typ_ch_pub_id, new_typ_ch_pub_id
이미 typ_ch_pub_id라는 타입을 쓰고 있어도 변경 가능.
- Identity : 시스템이 unique한 식별자로 자동 증가하는 칼럼의 유형
(1) numeric타입 칼럼만 가능
(2) drop 되어야 다시 시작(delete 소용없음)
(3) 한 테이블에 꼭 하나만들어가고 갱신이 안됨.
(4) 입력시 지정할 수 없으나 필요에 따라 지정가능
예) set identity_insert 테이블명 on/off
- 칼러이름 변경시
예) sp_rename "aaa.a" , aa : 중간에 ""는 사이에 . 이 있기 때문
- default : 기본 값은 지정. Pk는 디폴트 지정 안됨.
예) 수정시 / 새로 지정시
> alter table publishers
> replace city default "Springfield"
- null 지정 : sybase는 기본적으로 not null. 따라서 null은 꼭 기술해주어야 함.
2) 테이블 권한 부여/박탈
- grant [select/insert/update/delete/reference/all] on 테이블명 to 유저
예) grant select on emp to cel, kimds (to public : 누구에게나)
- revoke [select/insert/update/delete/reference/all] on 테이블명 from 유저
예) revoke select on emp from cel, kimds (from public : 누구에게나)
3) 테이블 수정 (칼럼 추가는 되는데 삭제는 안됨)
- 수정
> alter table publishers add address varchar(40) null, country varchar(40) null
- 삭제 : 임시테이블에 넣어놓고 삭제후 다시 입력하기
> select * into ccc_backup from ccc (ccc에 데이터가 없어도 ccc_backup생성됨)
> drop table ccc
> create table ccc ......
> insert ccc select a,b,c from ccc_backup
4) 데이터 수정 : sybase에서 있는 기능만 설명
- 두 테이블을 조인하여 수정 가능. 단, 한 테이블의 내용만 수정가능
> update titles set price=price * $0.90
> from titles t, publishers p
> where t.pub_id=p.pub_id and p_state="CA"
- 오라클의 decode와 유사한 case 구문 : 단 값중에 하나라도 null아닌 값이 있어야함.
case [필드명]
when 조건1 then 값1
when 조건2 then 값2
else 값3
end
예) update titles set price=
case when type="business" then price*$1.3
else price
end
5) 데이터 삭제 : sybase에서 있는 기능만 설명
- 롤백이 가능한 삭제
> delete titles from titles t, publishers p
> where t_pub_id=p.pub_id and p.state="CA"
- 롤백이 안되는 삭제 ( select into도 롤백안됨 )
> truncate table publishers
6) 데이터 조회
- 조회
> select type, avg(price) from titles
> where type not in ("mod_cook","trad_cook")
> group by type
> having avg(price) > $12.00
> order by type
- view 조회 : 수정시 뷰가 항목들이 한 테이블안에 존재하면 수정도 가능
> create view vw_cali_authors
> as
> select au_id, au_lname, au_fname from authors
> where state="CA"
> with check option => state가 꼭 "CA"이어야만 수정/추가됨.
3. 인덱스
1) 인덱스 생성/ 삭제
- create index 인덱스명 on 테이블명(칼럼명1, 칼럼명2)
- drop index 테이블명.인덱스명 ( 꼭 테이블명을 기술해주어야 함 )
2) 인덱스 구분
- clustered index : 인덱스키값을 기준으로 테이블을 저장순서를 정렬하여 기록하는
방식으로 자주 수정이 일어나는 테이블에는 부적절. 기본적으로 한테이블에 하나만 존재하며
PK에 의해 생성되는 인덱스테이블은 기본적으로 해당 방식으로 이루어짐.
- nonclustered index : 실제 데이터 저장순서와는 상관없이 키값과 주소값으로 이루어진
인덱스테이블을 기준으로 b+tree 검색을 하여 데이터 조회. 여러 개 가능.
4. 임시 테이블
1) Shareable Temporary Table : tempdb에 생성하는 테이블. 서버가 reboot하면 사라짐.
- use tempdb하여 create table aaa 하면 됨. 누구나 접근 가능.
2) Session-specific Temporary Table : 해당 session이 살아있는 동안만.
- create table #aaa ( a int null, b char(2) null )
- #뒤에 나오는 테이블명은 13자까지. 만든 유저만 접근 가능.
3) 시스템 테이블들
- select name,type from sysobjects : 만들어진 모든 시스템 오프젝트명과 타입나옴
- 타입 : D(default) R(rule) P(produre) TR(trigger) S (system) U(user table) V(view)
5. Constraint
1) check : 입력값의 조건을 걸어줌 (예: p_id char(4) null check(p_id like "99[0-9][0-9]") )
(주의할 점)
> create table publishers (
> pub_id char(4) not null,
> state char(2) default "OK" null, =>조건 1
> constraint chk_state check ( state in ("CA","OR","WA") ) =>조건 2
> )
> insert into publishers(pub_id) values("9909") => 에러가 남(조건 1,2가 틀림)
2) primary key, unique
3) foreign key : constraint ref_aaa foreign key(칼럼명) references 테이블명(칼럼명)
4) constraint 추가/삭제 : 기존 데이터에 영향을 안미침(단, PK, UNIQUE는 제외-index때문)
> alter table 테이블명 add constraint 조건이름 조건들기술…
> alter table 테이블명 drop constraint 조건이름
5) 사용자 정의 에러메시지
- 만들기 단계 : 메시지 생성-> constraint 걸기
> sp_addmessage 30001(번호), "메시지 내용"
> sp_bindmsg constraint명, 30001(번호)
- 지우기 단계 : 메시지 걸지정지 -> 메시지 지우기
> sp_unbinding constraint명
> sp_dropmessage 30001(번호)
6. Default
1) 생성하여 바인드하기
- create default 디폴트이름 as "값"
> sp_bindefault 디폴트이름, "테이블명.칼럼명"/"사용자정의타입"
: 이미 지정된 필드인 경우 에러남.
2) 언바인드하고 지우기
- sp_unbindefault "테이블명.칼럼명" /"사용자정의타입"
- drop default 디폴트이름
7. rule
1) 생성하여 걸어주기
- create rule 롤명 as 변수(@state) 조건(check, betweenm like….)
- sp_bindrule 롤명, "테이블명.칼럼명" /"사용자정의타입"
2) 풀고 삭제하기
- sp_unbindrule "테이블명.칼럼명" /"사용자정의타입"
- drop rule 롤명
------------------------------------------------------------------------------
Object 생성 삭제 확인 바인트/언바인드
------------------------------------------------------------------------------
사용자정의타입 sp_addtype sp_droptype sp_help /
테이블 create table drop table sp_help /
select into
뷰 create view drop view sp_help /
인덱스 create index drop index sp_help
sp_helpindex
디폴트 create default drop default sp_helptext Sp_bindefault
Sp_unbindefault
룰 create rule drop rule sp_helptext Sp_bindrule
Sp_unbindrule
메시지 sp_addmessage sp_dropmessage Sysusermessage Sp_bindmsg
Sp_unbindmsg
------------------------------------------------------------------------------
8. 함수들
1) 문자열함수
------------------------------------------------------------------------------
함 수 이 름 예 제 설 명
------------------------------------------------------------------------------
upper(칼럼명) Upper("aa'")=AA 대문자로 바꾸기
Lower(칼럼명) Lower("AA")=aa 소문자로 바꾸기
Convert(변경될타입,칼럼명) Convert(char(4),price) 타입을 변경
Substring(문자,시작,길이) Substring("abc",2,1)=b 문자열 잘라내기
Right(문자,길이) Right("abc",2)=ab 왼쪽에서 잘라내기
Charindex(찾는문자,문자) Charindex("b","abc")=2 특정문자위치 찾기
ascii(글자) ascii("T")=84 아스키값구하기
Char(숫자) Char(84)=T 아스키값으로 문자구하기
Ltrim(문자) Ltrim(" abc")=abc 왼쪽의 공백제거하기
Str(숫자,길이,소수점이하자리) Str(45.26, 1)="45.3" 실수를 문자열로변환하기
Soundex(문자) Soundex("green")
------------------------------------------------------------------------------
2) 날짜 함수
--------------------------------------------------------------------------------------
함 수 이 름 예 제 설 명
--------------------------------------------------------------------------------------
Convert(변경될타입,날짜,타입) Convert(char(10),date,111) 날짜를 다른 타입으로 변경
Getdate() 오늘날짜구하기
Datename(날짜타입,날짜) Datename(mm,getdate()) 해당 날짜의 이름값
Datepart(날짜타입,날짜) Datepart(mm,getdate()) 해당 날짜의 숫자값
Dateadd(날짜타입,간격,날짜) Dateadd(dd,3,getdate()) 날짜에 간격만큼 더하기
Datediff(날짜타입,날짜1,날짜2) Datediff(dd,date,getdate()) 날짜1,2의 간격
--------------------------------------------------------------------------------------
Cf) convert의 타입
------------------------------------------------------------------------------
타 입 결 과 타 입 결 과 타 입 결 과
------------------------------------------------------------------------------
1 mm/dd/yy 5 dd-mm-yy 9 mon dd yyyy hh:mm:ss
2 yy.mm.dd 6 dd mon yy 10 mm-dd-yy
3 dd/mm/yy 7 mon dd yy 11 yy/mm/dd
4 dd.mm.yy 8 hh:mm:ss 111 yyyy/mm/dd
------------------------------------------------------------------------------
Cf) 날짜 타입
------------------------------------------------------------------------------
타입 설 명 타 입 설 명 타 입 설 명 타 입 설 명
------------------------------------------------------------------------------
yy 년도 wk 주 dw 년도내주 ss 초
qq 분기 dd 일 hh 시간 ms 1/1000초
mm 달 dy 년도내날짜 mi 분
------------------------------------------------------------------------------
3) 숫자 함수
------------------------------------------------------------------------------
함 수 이 름 예 제 설 명
------------------------------------------------------------------------------
Abs(숫자) Abs(-99)=99 절대값
Ceiling(숫자) Ceiling(123.5)=124 정수로 반올림
floor(숫자) Floor(123.5)=123 정수로 자름
round(숫자,소수점자리수) Round(2.460,2)=2.460 소수점 자리수 표시
exp(실수) Exp(0)=1
rand(정수) Rand(39)=.49234563.. 랜덤함수
log(실수) Log(1)=0 로그
pi() Pi()=3.141592… 파이
Power(숫자,몇승인지) Power(10,3)=1000 제곱승구하기
Sqrt(숫자) Sqrt(100)=10 제곱근구하기
Sin,cos,tan Cos(0)=1 사인,코사인,탄젠트구하기
------------------------------------------------------------------------------
4) 기타 함수
------------------------------------------------------------------------------
함 수 이 름 예 제 설 명
------------------------------------------------------------------------------
Count(칼럼/*) Count(*) 레코드의 개수
Max Max(price) 최대값
Min Min(price) 최소값
Sum Sum(price) 합계
Avg Avg(price) 평균
Isnull(칼럼명,변환할값) Isnull(price,$0.00) 널일경우 값지정하기
Suser_name() 지금 현재 접속한 유저명
------------------------------------------------------------------------------
9. 배치처리
1) 변수선언 : 변수명앞에 @표시
cf) 연산상의 주의점
--------------------------------------------------------------------------------------
Declare @number int, @copy int, @sum int | Declare @number int, @copy int, @sum int
Select @number=10 | Select @number=10, @copy=@number,
Select @copy=@number, | @sum=@number+100
@sum=@number+100 |
| num copy sum
num copy sum | ------- ------ -----------
------- ------ ----------- | 10 10 NULL
10 10 110 |
--------------------------------------------------------------------------------------
2) global 변수
- @@rowcount : 현재 질의의 영향을 받은 레코드 수
- @@error : 가장 최근에 일어난 에러번호
- @@identity : 가장 최근에 일어나 identity의 최대치
- @@version : 현재 DB의 버전
3) 문법
--------------------------------------------------------------------------------------
If... else | If exists(select * from auth where id='kimds')
| Select "데이터가 없어요"
| Else
| begin
| If @@rowcount = 1
| Select "데이터가 하나 있어요"
| Else
| Select "데이터가 여러 개네요.."
| end
--------------------------------------------------------------------------------------
While... | While @price<30
| Begin
| Select @price = @price + $1.00
| If ( select count(*) from titles where price>@price)
| Countinue
| Else
| Break
| end
--------------------------------------------------------------------------------------
Print | Print "table %1 is not owned by user %2",@t_name,@t_user
--------------------------------------------------------------------------------------
raiserror | 변수에 에러지정
| (1) raiserror 70500 "Table %1 not found", @t_name
| (2) sp_addmessage 70500, "Table %1 not found"에러 출력
| (1) raiserror 70500, @t_name
--------------------------------------------------------------------------------------
10. 트랜잭션 처리
1) 기본구성
--------------------------------------------------------------------------------------
bagin tran | 트랜젝션 시작 => 처리1 진행 -> 처리2 진행 =>
처리 1 | 처리3 진행 => 처리3 롤백 => 처리4 진행
save tran 지점1 | (따라서) 처리 1,2,4만 진행되었슴.
처리2 | Cf) select @@trancount
save tran 지점2 | => begin tran이 몇번째 걸려있는지 단계조회
처리3 |
rollback tran 지점2 |
처리4 |
commit tran |
--------------------------------------------------------------------------------------
2) 처리구분
- unchained mode : 사이베이스 기본모드로 하나의 명령이 바로 commit되는 방식
(설정) set chained off : 묶어서 처리하려면 begin tran을 꼭 적어주어야 함.
- chained mode : 여러가지의 처리의 한 단위로 묶어 commit이나 rollback하는 방식
(설정) set chained on : begin tran을 안써도 됨.
11. Locking
1) 구분
- Shared Locks : select하는 동안 걸리는 락으로 읽기만 가능하고 수정은 불가
- Exclusive Locks : 수정하는 동안 걸리는 락으로 읽기/쓰기 다 불가
- Update Locks : 수정을 위해 읽는 동안 걸리는 락으로 읽기만 가능.
단, 수정할 데이터가 없는 경우 바로 락이 풀림.
2) deadlock : 시스템이 그냥 임의적으로 하나의 락을 일방적으로 풀어버림. 따라서
트렌젝션의 손실이 일어날 가능성이 있음
(확인) select @transtate => "3"은 abort가 된 경우, "0"는 진행중인 경우.
3) Lock를 거는 방식
- allpages locking(APL) : 관련 인덱스테이블의 페이지까지 다 lock을 걸기.
해당 방식이 default이므로 안쓰려면 생성시 지정.
Create table (...) lock [allpages/datapages/datarows]
- Datapages locking(DPL) : 인덱스테이블은 안걸고 데이터테이블의 페이지만 걸기.
- Datarows locking(DRL) : 인덱스테이블 안걸고 데이터의 해당 레코드만 락걸기.
- DB의 Default Lock변경 : DBA만 권한 있음.
sp_configure "lock scheme", 0, [allpages/datapages/datarows]
4) Isolation Level : 데이터의 일관성 유지 레벨
- dirty read : 처리1 변경단계의 가짜 데이터를 처리2에서 읽는 경우
- nonrepeatable read : 처리1이 진행중에 처리2가 수행되어 처리1의 값이 중간에 변경
- phanton read : 처리1이 진행중에 처리2에 의해 처리1에서 다른 데이터를 읽는 경우
--------------------------------------------------------------------------------------
dirty read nonrepeatable read phanton read
--------------------------------------------------------------------------------------
Level 0 허용 허용 허용
Level 1 불가 허용 허용
Level 2 불가 불가 허용
Level 3 불가 불가 불가
--------------------------------------------------------------------------------------
- 세팅 : set transaction isolation level [0/1/2/3]
- 확인 : select @@isolation
5) select title from titles holdlock where pub_id="0877" : udpate할때까지 lock걸기.
- 오라클의 for update와 유사
12. Cursors
1) 사용방법
- 선언(declare)
- 열기(open)
- 가져오기(fetch)
- 닫기(close)
- 재선언(deallocate)
2) 예제
--------------------------------------------------------------------------------------
Declare biz_book cursor for select title,title_id from titles where type="business"
For read only
Go (=>꼭 단독배치를)
Declare @title char(80), @title_id char(6)
Open biz_book
Fetch biz_book into @title, @title_id (=> fetch는 항상 forward만 가능)
While @@sqlstatus = 0 (0=>하나성공, 1=>에러, 2=>하나이상남음)
Begin
....
End
Close biz_book
Deallocate cursor biz_book
--------------------------------------------------------------------------------------
3) 하나이상을 fetch해 올 경우 : set cursor rows 레코드갯수 for biz_book
4) set close on endtran on/off : commot/rollback후 커서가 close되면 on, 아니면 off
13. stored procedure
1) 수행단계
- (1) create하면 내용은 syscounts에, query trees는 sysprocedures에 넣어둠
- (2) 수행요청을하면 cach확인후 있으면 해당 query tree대로, 없으면 새로 넣고 처리.
2) 문법 : 프로시져안에서는 뷰,디폴트,룰,다른 프로시져, 트리거 생성 안됨.
create proc 이름 as 내용들 return
--------------------------------------------------------------------------------------
create proc proc_new_price ( @title_id char(6)="000000", @new_price money output )
as
declare @state int
exec @state=proc_datacheck (=> 다른 프로시져 이용하여 결과받기)
select @new_price=price from titles where title_id=@title_id
select @new_price=@new_price*$1.15
return
--------------------------------------------------------------------------------------
3) 에러 : 0=> 성공, 0>error>-99 (시스템에러), 나머지: 사용자정의에러
4) 주의사항 : 프로시져A안에서 다른 프로시져B를 부르는 경우 B안에서 문제가 있어 롤백이 있는 경우
A작업도 같이 롤백됨. 따라서 다른 프로시져부르는 경우 save tran해주기.
5) Select @@nestlevel : 프로시져 몇 단계까지 들어갔는지 표시
6) With recompile : 프로시져는 procedure cach를 사용하기 때문에 관련 테이블이 구조가 변경되면
다시 컴파일을 해주어야 하는데 이것을 해주는 역할
(예)
--------------------------------------------------------------------------------------
생 성 | 수 정
--------------------------------------------------------------------------------------
Create table test ( a int, b int ) | [ 해결방안 ]
create proc pr_test ( @num int ) |
as | (1)생성시 항상 재컴파일하도록
select * from test where a=@num | create proc test(...) with recompile...return
return |
pr_test(1) | (2)수행시 재컴파일하도록(더 낫겟죠)
=>full scan방식으로 조회 | exec pr_test(1) with recompile
========================================== |
Create index test_idx on test(a)Pr_test(1) | (3)해당 테이블을 기준으로 다바꿔
=>index search방식으로 조회가 더 좋은디 | sp_recompile test
--------------------------------------------------------------------------------------
14. Triggers
1) 생성
create trigger 트리거명 on 관련테이블 for 관련DML as 작업내용
(예)
--------------------------------------------------------------------------------------
create trigger trg_I_sales
on salesfor
insert
as
declare @num int
select count(*) from inserted
select @@rowcount=@num
if @num = 0
begin
raiserror 40070, "no data inserted."
Rollback tran
end
--------------------------------------------------------------------------------------
2) 처리방식 : 트리거는 특정 테이블에 insert나 delete나 update가 일어날 때 동시에 처리해주거나
검사해주어야 하는 것을 걸어주는 것으로 처리 적용대상이 되는 레코드들은 inserted,
deleted라는 임시테이블에 저장되어진다.(update는 사실 inserted에도 deleted에도
존재하는 것임) . 트리거는 중간에 문제가 생겨서 이전 프로시져의 트렉젝션까지
롤백하지는 못함.
3) Update 트리거의 경우 if update(테이블PK) 해주어 키가 업데이트가 되었는지 확인가능.
4) Recursive 트리거의 경우 : 무한으로 걸린 경우 16번까지 하다 rollback
15. 시스템 조회명령들
1) sp_help [오브젝트]
- 오브젝트명을 입력하지 않으면 해당 DB의 모든 오브젝트에 대한 정보를,
오브젝트명을 기입한 경우에는 해당 오브젝트의 자세한 정보를 보여줌.
2) sp_syntax 원하는문법
- 해당 문법의 사용 방법을 기술해 줌
3) sp_who [원하는 계정]
- 계정을 안쓰면 전체 계정에 대한 정보를 기술,
계정을 기술하면 해당 계정에 대한 정보를 보여줌.
4) Sp_helpdb [디비명]
- 디비명을 기입한지 않으면 모든 디비정보를, 기입하면 해당 디비의 정보를 보여줌.
5) Sp_rename 오브젝트명, 새로 바꿀 오브젝트명
- 오브젝트명은 변경. 테이블명/칼럼명/타입명/뷰명/인덱스명/constraint
6) sp_helpconstraint 테이블명
- 해당 테이블에 걸려있는 constraint정보를 보여줌
7) sp_depends 테이블명/뷰명/프로시져명
- 테이블명을 적으면 해당 테이블이 reference걸려있는 모든 오브젝트들을,
뷰명 적으면 해당 뷰가 참조하는 테이블명을 보여줌. 프로시져는 관련테이블들 리스트업.
8) sp_helptext 뷰명/디폴트명/롤명/프로시져명
- 작성된 뷰의 질의문을 보여줌
9) sp_helpconstraint 테이블명
- 테이블의 constraint 정보를 보여줌.
10) Sp_lock
- 현재 디비에 걸려있는 락정보를 보여줌
11) sp_cursorinfo 커서명
- 커서에 대한 정보를 보여줌
1) 기본적으로 생성되는 데이터베이스(4개외에는 따로 생성한 디비들)
- master : 서버 전체에 대한 오브젝들의 정보를 가지고 있는 디비
- model : 이후 생성될 사용자데이타베이스의 모델이 되는 디비
- sybsystemprocs : 스토어프로시져가 쌓이는 디비
- tempdb : 임시공간으로 활용하는 곳
2) pubs2 : 샘플 데이터베이스를 생성하는 스크립터
- UNIX : $SYBASE_HOME/scripts/installpubs2
- NT : $SYBASE_HOME/scripts/instpbs2.sql
3) T-sql
- isql -U로그인아이디 -P패스워드 -s서버 -i스크립터명 -o결과저장화일명
- 표준 ansi-sql에 위배되는지 검사하기
set fipsflagger on/off (검사하기/안하기)
예) select type, avg(price) from titles => 사이베이스는 그냥 반복해서 평균뿌림.
On하면 에러메세지 나오고 내용출력
- isql안에서 유닉스명령을 수행하려면 : !!
예) 1> !!isql -Usa -P -iaaa.sql : aaa.sql 스크립터문을 수행.
- 디비 이동시 : 꼭 go를 밑에 넣어주어야 함.
> use 디비명
> go
- 이전 입력글을 에디팅하여 다시 수행시 :
> vi
- 주석문
> -- sample test 또는 /* 주석 */
4) SQL Advantage client
- 설정 : dedit를 이용하여 서버이름을 정하고 IP주소와 포트를 지정해줌
- 연결 : 접속시 위에서 설정한 서버이름을 선택하고 아이디와 패스워드입력.
2. 테이블 생성 및 수정, 관리
1) 테이블 생성(DDL)
- 250개의 칼럼 지정 가능
- 정수타입 : tinyint, smallint, int, numeric(p,s), decimal(p,s)
예) 123456.789 : numeric(8,2) => 123456.79,
numeric(10) => 123457, numeric(8,-2) => 123500
numeric(8,3) => 에러, numeric(7,2) => 에러
- 실수타입 : float(p), double precision, real
- money타입 : money, smallmoney
- 날짜타입 : datetime, smalldatetime
- 문자타입 : char(n), nchar(n), varchar(n), nvarchar(n), text
예) nchar는 national character로 언어별 문자수를 크기지정
한글버젼의 경우 char(5)=5bytes, nchar(5)=10bytes
cf) select @@ncharsize : 현재 버전의 문자크기 구하기
cf) char,varchar는 max 255, text는 max 2G
- binary타입 : bit, binary(n), varbinary(n), image(2G까지)
- 새로운 타입 지정 ( sp_addtype / sp_droptype )
예) sp_addtype typ_ch_pub_id, "char(4)", null
""를 넣은 이유는 ()가 있기때문
예) sp_droptype typ_ch_pub_id
예) sp_rename typ_ch_pub_id, new_typ_ch_pub_id
이미 typ_ch_pub_id라는 타입을 쓰고 있어도 변경 가능.
- Identity : 시스템이 unique한 식별자로 자동 증가하는 칼럼의 유형
(1) numeric타입 칼럼만 가능
(2) drop 되어야 다시 시작(delete 소용없음)
(3) 한 테이블에 꼭 하나만들어가고 갱신이 안됨.
(4) 입력시 지정할 수 없으나 필요에 따라 지정가능
예) set identity_insert 테이블명 on/off
- 칼러이름 변경시
예) sp_rename "aaa.a" , aa : 중간에 ""는 사이에 . 이 있기 때문
- default : 기본 값은 지정. Pk는 디폴트 지정 안됨.
예) 수정시 / 새로 지정시
> alter table publishers
> replace city default "Springfield"
- null 지정 : sybase는 기본적으로 not null. 따라서 null은 꼭 기술해주어야 함.
2) 테이블 권한 부여/박탈
- grant [select/insert/update/delete/reference/all] on 테이블명 to 유저
예) grant select on emp to cel, kimds (to public : 누구에게나)
- revoke [select/insert/update/delete/reference/all] on 테이블명 from 유저
예) revoke select on emp from cel, kimds (from public : 누구에게나)
3) 테이블 수정 (칼럼 추가는 되는데 삭제는 안됨)
- 수정
> alter table publishers add address varchar(40) null, country varchar(40) null
- 삭제 : 임시테이블에 넣어놓고 삭제후 다시 입력하기
> select * into ccc_backup from ccc (ccc에 데이터가 없어도 ccc_backup생성됨)
> drop table ccc
> create table ccc ......
> insert ccc select a,b,c from ccc_backup
4) 데이터 수정 : sybase에서 있는 기능만 설명
- 두 테이블을 조인하여 수정 가능. 단, 한 테이블의 내용만 수정가능
> update titles set price=price * $0.90
> from titles t, publishers p
> where t.pub_id=p.pub_id and p_state="CA"
- 오라클의 decode와 유사한 case 구문 : 단 값중에 하나라도 null아닌 값이 있어야함.
case [필드명]
when 조건1 then 값1
when 조건2 then 값2
else 값3
end
예) update titles set price=
case when type="business" then price*$1.3
else price
end
5) 데이터 삭제 : sybase에서 있는 기능만 설명
- 롤백이 가능한 삭제
> delete titles from titles t, publishers p
> where t_pub_id=p.pub_id and p.state="CA"
- 롤백이 안되는 삭제 ( select into도 롤백안됨 )
> truncate table publishers
6) 데이터 조회
- 조회
> select type, avg(price) from titles
> where type not in ("mod_cook","trad_cook")
> group by type
> having avg(price) > $12.00
> order by type
- view 조회 : 수정시 뷰가 항목들이 한 테이블안에 존재하면 수정도 가능
> create view vw_cali_authors
> as
> select au_id, au_lname, au_fname from authors
> where state="CA"
> with check option => state가 꼭 "CA"이어야만 수정/추가됨.
3. 인덱스
1) 인덱스 생성/ 삭제
- create index 인덱스명 on 테이블명(칼럼명1, 칼럼명2)
- drop index 테이블명.인덱스명 ( 꼭 테이블명을 기술해주어야 함 )
2) 인덱스 구분
- clustered index : 인덱스키값을 기준으로 테이블을 저장순서를 정렬하여 기록하는
방식으로 자주 수정이 일어나는 테이블에는 부적절. 기본적으로 한테이블에 하나만 존재하며
PK에 의해 생성되는 인덱스테이블은 기본적으로 해당 방식으로 이루어짐.
- nonclustered index : 실제 데이터 저장순서와는 상관없이 키값과 주소값으로 이루어진
인덱스테이블을 기준으로 b+tree 검색을 하여 데이터 조회. 여러 개 가능.
4. 임시 테이블
1) Shareable Temporary Table : tempdb에 생성하는 테이블. 서버가 reboot하면 사라짐.
- use tempdb하여 create table aaa 하면 됨. 누구나 접근 가능.
2) Session-specific Temporary Table : 해당 session이 살아있는 동안만.
- create table #aaa ( a int null, b char(2) null )
- #뒤에 나오는 테이블명은 13자까지. 만든 유저만 접근 가능.
3) 시스템 테이블들
- select name,type from sysobjects : 만들어진 모든 시스템 오프젝트명과 타입나옴
- 타입 : D(default) R(rule) P(produre) TR(trigger) S (system) U(user table) V(view)
5. Constraint
1) check : 입력값의 조건을 걸어줌 (예: p_id char(4) null check(p_id like "99[0-9][0-9]") )
(주의할 점)
> create table publishers (
> pub_id char(4) not null,
> state char(2) default "OK" null, =>조건 1
> constraint chk_state check ( state in ("CA","OR","WA") ) =>조건 2
> )
> insert into publishers(pub_id) values("9909") => 에러가 남(조건 1,2가 틀림)
2) primary key, unique
3) foreign key : constraint ref_aaa foreign key(칼럼명) references 테이블명(칼럼명)
4) constraint 추가/삭제 : 기존 데이터에 영향을 안미침(단, PK, UNIQUE는 제외-index때문)
> alter table 테이블명 add constraint 조건이름 조건들기술…
> alter table 테이블명 drop constraint 조건이름
5) 사용자 정의 에러메시지
- 만들기 단계 : 메시지 생성-> constraint 걸기
> sp_addmessage 30001(번호), "메시지 내용"
> sp_bindmsg constraint명, 30001(번호)
- 지우기 단계 : 메시지 걸지정지 -> 메시지 지우기
> sp_unbinding constraint명
> sp_dropmessage 30001(번호)
6. Default
1) 생성하여 바인드하기
- create default 디폴트이름 as "값"
> sp_bindefault 디폴트이름, "테이블명.칼럼명"/"사용자정의타입"
: 이미 지정된 필드인 경우 에러남.
2) 언바인드하고 지우기
- sp_unbindefault "테이블명.칼럼명" /"사용자정의타입"
- drop default 디폴트이름
7. rule
1) 생성하여 걸어주기
- create rule 롤명 as 변수(@state) 조건(check, betweenm like….)
- sp_bindrule 롤명, "테이블명.칼럼명" /"사용자정의타입"
2) 풀고 삭제하기
- sp_unbindrule "테이블명.칼럼명" /"사용자정의타입"
- drop rule 롤명
------------------------------------------------------------------------------
Object 생성 삭제 확인 바인트/언바인드
------------------------------------------------------------------------------
사용자정의타입 sp_addtype sp_droptype sp_help /
테이블 create table drop table sp_help /
select into
뷰 create view drop view sp_help /
인덱스 create index drop index sp_help
sp_helpindex
디폴트 create default drop default sp_helptext Sp_bindefault
Sp_unbindefault
룰 create rule drop rule sp_helptext Sp_bindrule
Sp_unbindrule
메시지 sp_addmessage sp_dropmessage Sysusermessage Sp_bindmsg
Sp_unbindmsg
------------------------------------------------------------------------------
8. 함수들
1) 문자열함수
------------------------------------------------------------------------------
함 수 이 름 예 제 설 명
------------------------------------------------------------------------------
upper(칼럼명) Upper("aa'")=AA 대문자로 바꾸기
Lower(칼럼명) Lower("AA")=aa 소문자로 바꾸기
Convert(변경될타입,칼럼명) Convert(char(4),price) 타입을 변경
Substring(문자,시작,길이) Substring("abc",2,1)=b 문자열 잘라내기
Right(문자,길이) Right("abc",2)=ab 왼쪽에서 잘라내기
Charindex(찾는문자,문자) Charindex("b","abc")=2 특정문자위치 찾기
ascii(글자) ascii("T")=84 아스키값구하기
Char(숫자) Char(84)=T 아스키값으로 문자구하기
Ltrim(문자) Ltrim(" abc")=abc 왼쪽의 공백제거하기
Str(숫자,길이,소수점이하자리) Str(45.26, 1)="45.3" 실수를 문자열로변환하기
Soundex(문자) Soundex("green")
------------------------------------------------------------------------------
2) 날짜 함수
--------------------------------------------------------------------------------------
함 수 이 름 예 제 설 명
--------------------------------------------------------------------------------------
Convert(변경될타입,날짜,타입) Convert(char(10),date,111) 날짜를 다른 타입으로 변경
Getdate() 오늘날짜구하기
Datename(날짜타입,날짜) Datename(mm,getdate()) 해당 날짜의 이름값
Datepart(날짜타입,날짜) Datepart(mm,getdate()) 해당 날짜의 숫자값
Dateadd(날짜타입,간격,날짜) Dateadd(dd,3,getdate()) 날짜에 간격만큼 더하기
Datediff(날짜타입,날짜1,날짜2) Datediff(dd,date,getdate()) 날짜1,2의 간격
--------------------------------------------------------------------------------------
Cf) convert의 타입
------------------------------------------------------------------------------
타 입 결 과 타 입 결 과 타 입 결 과
------------------------------------------------------------------------------
1 mm/dd/yy 5 dd-mm-yy 9 mon dd yyyy hh:mm:ss
2 yy.mm.dd 6 dd mon yy 10 mm-dd-yy
3 dd/mm/yy 7 mon dd yy 11 yy/mm/dd
4 dd.mm.yy 8 hh:mm:ss 111 yyyy/mm/dd
------------------------------------------------------------------------------
Cf) 날짜 타입
------------------------------------------------------------------------------
타입 설 명 타 입 설 명 타 입 설 명 타 입 설 명
------------------------------------------------------------------------------
yy 년도 wk 주 dw 년도내주 ss 초
qq 분기 dd 일 hh 시간 ms 1/1000초
mm 달 dy 년도내날짜 mi 분
------------------------------------------------------------------------------
3) 숫자 함수
------------------------------------------------------------------------------
함 수 이 름 예 제 설 명
------------------------------------------------------------------------------
Abs(숫자) Abs(-99)=99 절대값
Ceiling(숫자) Ceiling(123.5)=124 정수로 반올림
floor(숫자) Floor(123.5)=123 정수로 자름
round(숫자,소수점자리수) Round(2.460,2)=2.460 소수점 자리수 표시
exp(실수) Exp(0)=1
rand(정수) Rand(39)=.49234563.. 랜덤함수
log(실수) Log(1)=0 로그
pi() Pi()=3.141592… 파이
Power(숫자,몇승인지) Power(10,3)=1000 제곱승구하기
Sqrt(숫자) Sqrt(100)=10 제곱근구하기
Sin,cos,tan Cos(0)=1 사인,코사인,탄젠트구하기
------------------------------------------------------------------------------
4) 기타 함수
------------------------------------------------------------------------------
함 수 이 름 예 제 설 명
------------------------------------------------------------------------------
Count(칼럼/*) Count(*) 레코드의 개수
Max Max(price) 최대값
Min Min(price) 최소값
Sum Sum(price) 합계
Avg Avg(price) 평균
Isnull(칼럼명,변환할값) Isnull(price,$0.00) 널일경우 값지정하기
Suser_name() 지금 현재 접속한 유저명
------------------------------------------------------------------------------
9. 배치처리
1) 변수선언 : 변수명앞에 @표시
cf) 연산상의 주의점
--------------------------------------------------------------------------------------
Declare @number int, @copy int, @sum int | Declare @number int, @copy int, @sum int
Select @number=10 | Select @number=10, @copy=@number,
Select @copy=@number, | @sum=@number+100
@sum=@number+100 |
| num copy sum
num copy sum | ------- ------ -----------
------- ------ ----------- | 10 10 NULL
10 10 110 |
--------------------------------------------------------------------------------------
2) global 변수
- @@rowcount : 현재 질의의 영향을 받은 레코드 수
- @@error : 가장 최근에 일어난 에러번호
- @@identity : 가장 최근에 일어나 identity의 최대치
- @@version : 현재 DB의 버전
3) 문법
--------------------------------------------------------------------------------------
If... else | If exists(select * from auth where id='kimds')
| Select "데이터가 없어요"
| Else
| begin
| If @@rowcount = 1
| Select "데이터가 하나 있어요"
| Else
| Select "데이터가 여러 개네요.."
| end
--------------------------------------------------------------------------------------
While... | While @price<30
| Begin
| Select @price = @price + $1.00
| If ( select count(*) from titles where price>@price)
| Countinue
| Else
| Break
| end
--------------------------------------------------------------------------------------
Print | Print "table %1 is not owned by user %2",@t_name,@t_user
--------------------------------------------------------------------------------------
raiserror | 변수에 에러지정
| (1) raiserror 70500 "Table %1 not found", @t_name
| (2) sp_addmessage 70500, "Table %1 not found"에러 출력
| (1) raiserror 70500, @t_name
--------------------------------------------------------------------------------------
10. 트랜잭션 처리
1) 기본구성
--------------------------------------------------------------------------------------
bagin tran | 트랜젝션 시작 => 처리1 진행 -> 처리2 진행 =>
처리 1 | 처리3 진행 => 처리3 롤백 => 처리4 진행
save tran 지점1 | (따라서) 처리 1,2,4만 진행되었슴.
처리2 | Cf) select @@trancount
save tran 지점2 | => begin tran이 몇번째 걸려있는지 단계조회
처리3 |
rollback tran 지점2 |
처리4 |
commit tran |
--------------------------------------------------------------------------------------
2) 처리구분
- unchained mode : 사이베이스 기본모드로 하나의 명령이 바로 commit되는 방식
(설정) set chained off : 묶어서 처리하려면 begin tran을 꼭 적어주어야 함.
- chained mode : 여러가지의 처리의 한 단위로 묶어 commit이나 rollback하는 방식
(설정) set chained on : begin tran을 안써도 됨.
11. Locking
1) 구분
- Shared Locks : select하는 동안 걸리는 락으로 읽기만 가능하고 수정은 불가
- Exclusive Locks : 수정하는 동안 걸리는 락으로 읽기/쓰기 다 불가
- Update Locks : 수정을 위해 읽는 동안 걸리는 락으로 읽기만 가능.
단, 수정할 데이터가 없는 경우 바로 락이 풀림.
2) deadlock : 시스템이 그냥 임의적으로 하나의 락을 일방적으로 풀어버림. 따라서
트렌젝션의 손실이 일어날 가능성이 있음
(확인) select @transtate => "3"은 abort가 된 경우, "0"는 진행중인 경우.
3) Lock를 거는 방식
- allpages locking(APL) : 관련 인덱스테이블의 페이지까지 다 lock을 걸기.
해당 방식이 default이므로 안쓰려면 생성시 지정.
Create table (...) lock [allpages/datapages/datarows]
- Datapages locking(DPL) : 인덱스테이블은 안걸고 데이터테이블의 페이지만 걸기.
- Datarows locking(DRL) : 인덱스테이블 안걸고 데이터의 해당 레코드만 락걸기.
- DB의 Default Lock변경 : DBA만 권한 있음.
sp_configure "lock scheme", 0, [allpages/datapages/datarows]
4) Isolation Level : 데이터의 일관성 유지 레벨
- dirty read : 처리1 변경단계의 가짜 데이터를 처리2에서 읽는 경우
- nonrepeatable read : 처리1이 진행중에 처리2가 수행되어 처리1의 값이 중간에 변경
- phanton read : 처리1이 진행중에 처리2에 의해 처리1에서 다른 데이터를 읽는 경우
--------------------------------------------------------------------------------------
dirty read nonrepeatable read phanton read
--------------------------------------------------------------------------------------
Level 0 허용 허용 허용
Level 1 불가 허용 허용
Level 2 불가 불가 허용
Level 3 불가 불가 불가
--------------------------------------------------------------------------------------
- 세팅 : set transaction isolation level [0/1/2/3]
- 확인 : select @@isolation
5) select title from titles holdlock where pub_id="0877" : udpate할때까지 lock걸기.
- 오라클의 for update와 유사
12. Cursors
1) 사용방법
- 선언(declare)
- 열기(open)
- 가져오기(fetch)
- 닫기(close)
- 재선언(deallocate)
2) 예제
--------------------------------------------------------------------------------------
Declare biz_book cursor for select title,title_id from titles where type="business"
For read only
Go (=>꼭 단독배치를)
Declare @title char(80), @title_id char(6)
Open biz_book
Fetch biz_book into @title, @title_id (=> fetch는 항상 forward만 가능)
While @@sqlstatus = 0 (0=>하나성공, 1=>에러, 2=>하나이상남음)
Begin
....
End
Close biz_book
Deallocate cursor biz_book
--------------------------------------------------------------------------------------
3) 하나이상을 fetch해 올 경우 : set cursor rows 레코드갯수 for biz_book
4) set close on endtran on/off : commot/rollback후 커서가 close되면 on, 아니면 off
13. stored procedure
1) 수행단계
- (1) create하면 내용은 syscounts에, query trees는 sysprocedures에 넣어둠
- (2) 수행요청을하면 cach확인후 있으면 해당 query tree대로, 없으면 새로 넣고 처리.
2) 문법 : 프로시져안에서는 뷰,디폴트,룰,다른 프로시져, 트리거 생성 안됨.
create proc 이름 as 내용들 return
--------------------------------------------------------------------------------------
create proc proc_new_price ( @title_id char(6)="000000", @new_price money output )
as
declare @state int
exec @state=proc_datacheck (=> 다른 프로시져 이용하여 결과받기)
select @new_price=price from titles where title_id=@title_id
select @new_price=@new_price*$1.15
return
--------------------------------------------------------------------------------------
3) 에러 : 0=> 성공, 0>error>-99 (시스템에러), 나머지: 사용자정의에러
4) 주의사항 : 프로시져A안에서 다른 프로시져B를 부르는 경우 B안에서 문제가 있어 롤백이 있는 경우
A작업도 같이 롤백됨. 따라서 다른 프로시져부르는 경우 save tran해주기.
5) Select @@nestlevel : 프로시져 몇 단계까지 들어갔는지 표시
6) With recompile : 프로시져는 procedure cach를 사용하기 때문에 관련 테이블이 구조가 변경되면
다시 컴파일을 해주어야 하는데 이것을 해주는 역할
(예)
--------------------------------------------------------------------------------------
생 성 | 수 정
--------------------------------------------------------------------------------------
Create table test ( a int, b int ) | [ 해결방안 ]
create proc pr_test ( @num int ) |
as | (1)생성시 항상 재컴파일하도록
select * from test where a=@num | create proc test(...) with recompile...return
return |
pr_test(1) | (2)수행시 재컴파일하도록(더 낫겟죠)
=>full scan방식으로 조회 | exec pr_test(1) with recompile
========================================== |
Create index test_idx on test(a)Pr_test(1) | (3)해당 테이블을 기준으로 다바꿔
=>index search방식으로 조회가 더 좋은디 | sp_recompile test
--------------------------------------------------------------------------------------
14. Triggers
1) 생성
create trigger 트리거명 on 관련테이블 for 관련DML as 작업내용
(예)
--------------------------------------------------------------------------------------
create trigger trg_I_sales
on salesfor
insert
as
declare @num int
select count(*) from inserted
select @@rowcount=@num
if @num = 0
begin
raiserror 40070, "no data inserted."
Rollback tran
end
--------------------------------------------------------------------------------------
2) 처리방식 : 트리거는 특정 테이블에 insert나 delete나 update가 일어날 때 동시에 처리해주거나
검사해주어야 하는 것을 걸어주는 것으로 처리 적용대상이 되는 레코드들은 inserted,
deleted라는 임시테이블에 저장되어진다.(update는 사실 inserted에도 deleted에도
존재하는 것임) . 트리거는 중간에 문제가 생겨서 이전 프로시져의 트렉젝션까지
롤백하지는 못함.
3) Update 트리거의 경우 if update(테이블PK) 해주어 키가 업데이트가 되었는지 확인가능.
4) Recursive 트리거의 경우 : 무한으로 걸린 경우 16번까지 하다 rollback
15. 시스템 조회명령들
1) sp_help [오브젝트]
- 오브젝트명을 입력하지 않으면 해당 DB의 모든 오브젝트에 대한 정보를,
오브젝트명을 기입한 경우에는 해당 오브젝트의 자세한 정보를 보여줌.
2) sp_syntax 원하는문법
- 해당 문법의 사용 방법을 기술해 줌
3) sp_who [원하는 계정]
- 계정을 안쓰면 전체 계정에 대한 정보를 기술,
계정을 기술하면 해당 계정에 대한 정보를 보여줌.
4) Sp_helpdb [디비명]
- 디비명을 기입한지 않으면 모든 디비정보를, 기입하면 해당 디비의 정보를 보여줌.
5) Sp_rename 오브젝트명, 새로 바꿀 오브젝트명
- 오브젝트명은 변경. 테이블명/칼럼명/타입명/뷰명/인덱스명/constraint
6) sp_helpconstraint 테이블명
- 해당 테이블에 걸려있는 constraint정보를 보여줌
7) sp_depends 테이블명/뷰명/프로시져명
- 테이블명을 적으면 해당 테이블이 reference걸려있는 모든 오브젝트들을,
뷰명 적으면 해당 뷰가 참조하는 테이블명을 보여줌. 프로시져는 관련테이블들 리스트업.
8) sp_helptext 뷰명/디폴트명/롤명/프로시져명
- 작성된 뷰의 질의문을 보여줌
9) sp_helpconstraint 테이블명
- 테이블의 constraint 정보를 보여줌.
10) Sp_lock
- 현재 디비에 걸려있는 락정보를 보여줌
11) sp_cursorinfo 커서명
- 커서에 대한 정보를 보여줌