| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 8 | 9 | 10 | 11 | 12 | 13 | 14 |
| 15 | 16 | 17 | 18 | 19 | 20 | 21 |
| 22 | 23 | 24 | 25 | 26 | 27 | 28 |
| 29 | 30 | 31 |
- MSSQL
- 즐겨찾기 추가
- 블로그 방문자 늘리기
- Jrun
- .NET
- Vista
- CSS
- CVS
- 블로그 조회수
- JSP
- 블로그 수익화
- Eclipse
- MySQL
- oralce
- Administrator
- Adobe pdf reader
- 갑근세
- samba
- Jexcel
- 블루수국그림
- HTML
- 블로그 조회수 늘리기
- JavaScript
- IIS
- 에덴미술
- 소득세
- 국민연금
- PHP
- flash
- 인테리어그림
- Today
- Total
I LOVE EJ
MS-SQL 종합 본문
from http://asp.tipi.co.kr/mssql.htm
M S - S Q L http://my.dreamwiz.com/hideny/study/sql/sql.html
--------------------------------------------------------------------------------
도움말은 F1 이나 SHIFT + F1
☞ SHIFT + F1 은 보고자 하는 명령어를 블록을 잡아 실행시키면 된다.
● 데이터베이스 생성
(1)Create Database mydb ON
(Name ='mydb.dat' , FileName = 'D:\MSSQL7\Data\mydb.mdf', size=10)
Log on (Name = 'mydb.log' , FileName = 'D:\MSSQL7\Data\mydb.ldf', size=3)
drop database mydb
☞ D 드라이브 상에서 자신의 DB를 생성하기
☞ drop database mydb 는 자신의 DB인 mydb를 삭제하는 것이다.
(2) Create Database exdb ON (Name ='ex.dat' , FileName = 'D:\MSSQL7\Data\ex.mdf',
Size = 10, Maxsize = 50, Filegrowth = 1)
Log on (Name = 'ex.log' , FileName = 'D:\MSSQL7\Data\ex.ldf', Size = 3mb, Maxsize = 25mb, Filegrowth = 1mb)
drop database exdb
● SQL 명령어
1) DCL명령어(Data Control Language) : 사용자 관리 명령
Grant, Rovoke, Deny 등
2) DDL 명령어)Data DEfinetion Language) : DB, Table 에 관한 구조 정의
Create, Alter,Drop 등
☞ Create 생성, Alter 추가, Drop 삭제
3)DML 명령 (data Manepution Language) :Col 과 Row 작업 (레코드에 관한 작업)
Select, Insert, Delete,Updata
★ 자료형에 대하여.....
select * from systypes
select name, prec, scale from systypes
☞ select (필드명) from (테이플 명)
● Table의 제한
데이타 베이스당 20억개의 테이블을 갖을 수 있다.
최대 1024개의 칼럼을 가질수 있다.
행당 8096바이트를 가질수 있다.(단 이미지와 TEXT는 제외)
● 테이블의 제약조건
Not NullNull값을 허용하지 않는다
Unique중복을 허용하지 않는다
Default기본값 설정
CheckValidation Rule을 설정
Identity 칼럼의 값을 자동으로 증가,감소할 수 있도록 설정.
테이블당 1개만 허용한다. 갱신될 수 없다.
Null을 허용하지 않는다.
Integer자료형(Int, Smallint, Tinyint)이나 Numeric, Decimal사용(scale은 0이어야 한다).
형식 IDENTITY(초기값,증가감소)
IDENT_INCR('테이블명') - 증가값을 보여준다
IDENT_SEED('테이블명') - 시작값을 보여준다
사용자에 의해서 자료 삽입이 가능하게 하려면
SET IDENTITY_INSERT db명..테이블명 {ON|OFF}
Identity는 Unique 속성은 갖지 않는다.
=======================================================================================================
☞ 지금 부터는 Table을 생성
Buseo table(primary-부서 코드)
부서 코드
부서명
부서위치
전화
Buseono
Buseoname
BuseoLoc
Buseotel
Int
Varchar(10)
Varchar(10)
Varchar(15)
Sawon table(primary-사번)
Buseo 의 부서코드 (Buseono)종속키(상대방은 반드시 기본키)
사번
이름
부서 코드
직책
급여
입사일
Sabun
Saname
Buseono
sajob
sapay
sahire
Int
Varchar(10)
Int
Varchar(10)
Numeric(10,0)
Datetime
Gogek table(primary-고객 번호
sawon 의 사번(sabun) 종속키
고객 번호
이름
전화
성별
담당자
Gobun
Goname
Gotel
Gosex
godam
Int
Varchar(10)
Varchar(20)
Varchar(2)
Int
☞부서 코드( Buseo table )와 부서 코드(Sawon table )그리고 사번( Sawon table )과 담당자( Gogek table )가 서로 연결
☞ /* ~~~~~~ */ 은 주석
(1) 테이블 작성 buseo
● 생성하자 (10개의 데이터)
create table buseo(buseono int identity(10,10), /* 부서코드, Not Null이 생략 */
buseoname varchar(10), /* 부서명 */
buseoloc varchar(10), /* 부서위치 */
buseotel varchar(15),/* 전화 */
constraint buseo_buseono_pk primary key(buseono))
insert into buseo values('총무부', '부산', '123-1234') /* 부서명, 부서위치, 전화 */
insert into buseo values('영업부', '광주', '234-2234')
insert into buseo values('전산부', '서울', '133-4444')
insert into buseo values('총무부', '서울', '456-5678')
insert into buseo values('영업부', '대구', '678-1256')
insert into buseo values('전산부', '부산', '784-3756')
insert into buseo values('총무부', '대전', '376-8367')
insert into buseo values('영업부', '부산', '397-3089')
insert into buseo values('전산부', '안양', '265-6836')
insert into buseo values('관리부', '인천', '609-2834')
☞ constraint 테이블명_필드명_pk(성격) primary key(buseono))
● 간단한 연습
1) 결과출력
select * from buseo
2) 중복연습 (Unique의 연습)
alter table buseo add constraint buseo_buseoname_uq unique(buseoname) --안된다(총무부 ...)
alter table buseo add constraint buseo_buseotel_uq unique(buseotel)
insert into buseo values('생산부', '대구', '111-1111') --2번을 insert하면 안된다
alter table buseo drop constraint buseo_buseotel_uq
3) 필드추가
: 단 필드는 6.5는 삭제할 수 없지만 7.0은 가능하다
: Oracle 8.0과의 형식이 다르다
alter table buseo add address varchar(15) /*7.0식 */
alter table buseo add address varchar(15) null /*6.5식 */
select * from buseo
alter table buseo drop column address
alter table buseo drop column buseotel/* Oracle8.0은 안된다 (☞ 레코드가 있는 것은 지울 수 가 없다.
단, 레코드가 없는 것만 지운다.)
4) Update의 연습
㉠ update buseo set address='' where buseono=10
☞ ''는 큰 따움표가 아니라 싱글 마크 ' 를 두 번 쓴다.
㉡ update buseo set address='' where buseono >= 20 and buseono <= 40
☞ 20~40 까지 null 을 없앴다.
㉢ update buseo set address='' where buseono between 50 and 70
㉣ update buseo set address='' where address = null --안된다(마치 되는것처럼...)
㉤ update buseo set address='' where address is null
㉥ update buseo set address='' /*가능 하다.*/
㉦update buseo set address='gg' where buseono address=10
☞ buseono 10 값에 address 값에 gg 가 추가 된다.
5) 삭제 (Delete는 DML명령, Drop은 DDL명령)
delete from buseo where buseoname='총무부'
☞ 레코드 단위
truncate table buseo
☞ 페이지 단위
truncate table buseo where buseoname='총무부' --안된다
☞ 조건부 형식은 되지 않는다.
drop table buseo
☞ 테이블 단위
=======================================================================================
(2) 테이블 작성 Sawon (27개의 데이터)
create table sawon(sabun int identity(1,1),/* 사번 */
saname varchar(10),/* 이름 */
buseono int,/* 부서코드 */ ☞buseono int 에서 int 인 이유는 Primary가 int 이므로 sajob varchar(4) default '사원',/* 직책 */
sapay numeric(10,0),/* 급여 */
sahire datetime,/* 입사일 */
constraint sawon_sabun_pk primary key(sabun),
constraint sawon_buseono_fk foreign key(buseono) references buseo(buseono))
☞ 사원과 부서가 연결될 경우: 사원은 지워야 부서가 지워짐
☞ references buseo(buseono)) 참조하는 primary 표시
insert into sawon values('엄정화', 10, '사원', 10000, '2-12-1999') /* 이름 이후 입력 */
insert into sawon values('룰라', 20, '대리', 10000, '2-12-1999')
insert into sawon values('강수지', 30, '부장', 20000, '2-13-1999')
insert into sawon values('설운도', 40, '차장', 30000, '2-22-1999')
insert into sawon values('서태지', 50, '대리', 40000, '2-26-1999')
insert into sawon values('송대관', 60, '사장', 50000, '2-28-1999')
insert into sawon values('남진', 70, default, 60000, '3-1-1999')
insert into sawon values('나훈아', 80, default, 70000, '3-3-1999')
insert into sawon values('영턱스', 90, '회장', 150000, '3-7-1999')
insert into sawon values('신성우', 10, '주임', 90000, '4-1-1999')
insert into sawon values('박진영', 20, '주임', 180000, '5-1-1999')
insert into sawon values('송창식', 40, '부장', 110000, '6-1-1999')
insert into sawon values('혜은이', 30, '주임', 110000, '7-1-1999')
insert into sawon values('이은하', 20, '대리', 130000, '8-1-1999')
insert into sawon values('양파', 60, '이사', 140000, '9-1-1999')
insert into sawon values('이선희', 10, '담당', 70000, '3-1-1999')
insert into sawon values('신성훈', 10, '대리', 80000, '4-1-1999')
insert into sawon values('김건모', 10, '과장', 90000, '5-1-1999')
insert into sawon values('이미자', 10, '주임', 180000, '6-1-1999')
insert into sawon values('현철', 50, default, 90000, '9-22-1999')
insert into sawon values('김추자', 30, '과장', 190000, '5-3-1999')
insert into sawon values('소찬희', 40, '주임', 280000, '6-5-1999')
insert into sawon values('안재욱', 20, '과장', 60000, '5-2-1999')
insert into sawon values('주영훈', 20, '주임', 280000, '6-21-1999')
insert into sawon values('이문세', 30, '과장', 190000, '5-11-1999')
insert into sawon values('이기찬', 40, '주임', 120000, '6-17-1999')
insert into sawon values('윤태운', 20, default, 120000, '6-17-1999')
insert into sawon values('윤희경', 20, 120000, '6-17-1999')
--디폴트 생략시 Error
insert into sawon values('윤희경', 20, null, 120000, '6-17-1999')
insert into sawon values('윤희경', 20, default, 120000, '6-17-1999')
select * from sawon
drop table sawon
(3) 테이블 작성 Gogek
Create Table table (col명 type [제약규칙], col명 type [제약규칙],...)
생성하자
create table gogek(gobun int identity(1,1),/* 고객번호 */
goname varchar(10),/* 이름 */
gotel varchar(20),/* 전화 */
gosex varchar(2),/* 성별 */
godam int,/* 담당자 */
☞ 사본이 primary int 로 되어 있기 때문에 godam 도 역시 int(일련 번호는 int로 잡혔있다.)
constraint gogek_gobun_pk primary key(gobun),
constraint gogek_gosex_ck check(gosex in('남','여')),
☞ gosex in('남','여')에서 in 은 꼭 써주어야 한다.
constraint gogek_godam_fk foreign key(godam) references sawon(sabun))
insert into gogek values('이주일', '123-1234', '남', 1) /* 이름 이후 입력 */
insert into gogek values('김미화', '223-1234', '여', 2)
insert into gogek values('이용식', '323-1234', '남', 3)
insert into gogek values('배연정', '423-1234', '여', 4)
insert into gogek values('김국진', '583-1234', '남', 5)
insert into gogek values('이경규', '873-1234', '남', 1)
insert into gogek values('김옥주', '863-1234', '여', 2)
insert into gogek values('이기동', '853-1234', '남', 3)
insert into gogek values('이영자', '843-1234', '여', 4)
insert into gogek values('남성남', '533-1234', '남', 5)
insert into gogek values('심형래', '133-1234', '남', 1)
insert into gogek values('홍진경', '223-1234', '여', 2)
insert into gogek values('김형곤', '373-1234', '남', 3)
insert into gogek values('조혜련', '483-1234', '여', 4)
insert into gogek values('최양락','123-1234','중',5)
/* Error의 이유는? */
☞체크 속성이 되어 있지 않기 때문이다.
insert into gogek values('최양락', '123-1234', '남', 5)
select * from gogek
예) 제약조건 설정과 해제 (Oracle과 형식이 다르다)
Alter table gogek NOCHECK Constraint gogek_gosex_ck
Alter table gogek CHECK Constraint gogek_gosex_ck
Alter table gogek NOCHECK Constraint ALL
Alter table gogek CHECK Constraint ALL
예) 제약조건 삭제
Alter table gogek DROP Constraint gogek_gosex_ck
drop table gogek
★ DTS(Data Transformation Services) : Excel,Access,Oracle 등으로 Export, Import
★ DB(Oracle,SyBase, Paradox)와 App(VB,PB,Delphi) 등과 MS-SQL의 연결
★ 단 Oracle은 조금은 난해한 연결을 하여야 한다
Oracle Net8 Easy Config를 이용.
CR과 연결시 Connect에 odbc;dsn=aaa;uid=scott;pwd=tiger을 명기.
Oracle시 Select * from “emp” ;
==================================================================================================
● 테이블 이름, 구조 보기 (DB선택하고, EM에서도 실습)
(1) Master DB 또는 mydb 등이 가지고 있는 서버의 정보보기
예) Use master
예) Use mydb
☞ 서버의 정보 보기
예) Select * from sysobjects order by name
☞ sysobjects(시스템에 관련)order by name(이름 별로)
예) Select name,id,uid,type, userstat from sysobjects order by name
(2) DB에 있는 테이블 정보보기 (SP_를 Help로 보는 시간을 가진다)
형식)SP_TABLES 테이블명,소유자명,DB명
예) SP_TABLES
예) SP_TABLES gogek, dbo, mydb
예) SP_TABLES null, dbo
Oracle의 select * from tab;
(3) Table의 칼럼정보 보기
형식)SP_COLUMNS 테이블명
예) SP_COLUMNS gogek
(4) 현재 선택된 DB에 접속하여 사용할 수 있는 모든유저명 보기
예) select * from sysusers
(5) 오브젝트이름이나 칼럼명 바꾸기(일반유저가 사용가능)
형식1)sp_rename old테이블명, new테이블명
예) sp_rename gogek, go
sp_rename go, gogek
☞ gogek을 go로 바꾸기
형식2)sp_rename '테이블명.old칼럼명', new칼럼명
예) sp_rename 'gogek.gobun', gobun1
select * from gogek
sp_rename 'gogek.gobun1', gobun
(6) 데이타베이스명 바꾸기
형식)sp_renamedb olddb명, newdb명
예) sp_renamedb mydb, youdb
sp_renamedb youdb, mydb
☞ 기본 기법이 멀티 유저(user) 이기에 단독 user 로 바꿔져야 한다.
단 Server의 mydb \ Properties \ Option에서 Single_User로 선택되어야 한다
또한 mydb가 사용하지 않아야 한다 (mydb가 Query에서 선택되어 있지 않아야 한다)
(7) sp_명령으로 Login의 만들기 (User와 혼동해서는 안된다)
☞ 간단히 말해 동호회에서 사용자명을 생각하면된다.
로그인 해서 동호회 가입시 다른 사용자명을 사용할수 있다는 걸..
간단히 permissions의 개념도 한다
☞ 오른쪽 마우스를 통해서 propertion 에서 권한을 줄 수도 있다.
(8) 패스워드 바꾸기 (매우 중요.....)
형식)sp_password old, new [, login_id]
예) SA가 다른사람의 패스워드를 바꿀때는 login id를 사용한다.
현재 접속되어 있는 사람이 패스워드를 바꿀때는 login id는 생략가능하다.
sp_password null, '123', SA
sp_password '123', null, SA
SA에 대한 소유권을 다른 유저에게 이전할 수 있다(단 Master DB는 이전할 수 없다)
DB를 만들수 있는 사람은 SA만 가능하므로 모든 DB의 소유권은 SA가 갖는다.
그러나 SA가 바쁜일로 특정 DB를 관리할 수 없는 경우 해당 DB에 대한 소유권을
다른 유저에게 이전 할 수 있다.
소유권을 이전받은 유저는 해당 DB에 대해서만 모든 관리 권한을 갖는다.
물론 소유권을 이전한 SA도 양도한 DB를 관리할 수 있다.
주)SA는 모든일을 할 수 있다.
(9) 소유권이전(SA로 접속),
형식> sp_changedbowner login_id [, true]
예) sp_changedbowner ywh , true
(10) 소유권 가져오기(SA로 접속)-SA가 다시 SA로 주면 된다.
예) sp_changedbowner sa , true
================================================================
● 아래는 적용범위에 대하여 이해를 하여야 한다
(1) 쿼리분석기에서...
1) 사용자 정의 유형 (User Defined Data Types)
생성> sp_addtype 새로운이름의 type명, 내부 datatype명 [null에 대한 설정]
예1> sp_addtype irum, 'varchar(10)'
☞ varchar(10)인 이유는 saname irum 값을 표현하기 위해서
sp_addtype pay, 'money'
예2> create table ex1(saname irum, sapay pay)
select * from ex1
삭제> sp_droptype 새로운이름의 type명
sp_droptype pay
바인딩이 되어 있으므로 안된다(ex1 Table). 그러므로 Table을 삭제하면 된다
2) default 정의 하기
형식> create default default_name as constraint_expression
예> create default namedef as '영구'
☞ 만들기만 하고 연결이 되어 있지 않기 때문에 3) 을 먼저 실행해야 한다.
insert into ex1 values(default, 10000)
select * from ex1 --Null이 된다, 바인딩이 안되었으므로...
3) default 를 table의 컬럼에 바인드 시키기
형식> sp_bindefault defaultname, 'table명.field명'
예> sp_bindefault namedef, 'ex1.saname'
insert into ex1 values(default, 10000)
select * from ex1
4) default 를 table의 컬럼에 바인드 해제 시키기
형식> sp_unbindefault 'table_name.field명'
예> sp_unbindefault 'ex1.saname'
다시> insert into ex1 values(default,20)
select * from ex1
5) default 삭제
형식> drop default default_name, default_name, default_name,.....
예> drop default namedef
6) 데이타 type에 바인드하기 (3번과 형식의 차이가 있다)
: 내장된 데이타 타입에는 바인드 할수 없고 사용자 정의 타입에만 정의가 가능
형식> sp_bindefault namedef, '사용자정의자료형'
먼저> create default namedef as '영구'
예> sp_bindefault namedef, 'irum'
insert into ex1 values(default,20)
select * from ex1
7) Rule 만들기
형식> create rule rulename as @condition_expression
예> create rule payrule as @sapay between 10 and 100
create rule namerule as @saname in('영구','kim','park','han')
Delete from ex1
insert into ex1 values('han', 150)--적용되지 않는다
insert into ex1 values('kkk', 50)--적용되지 않는다
select * from ex1
8) Rule을 table의 컬럼에 바인드 시키기
형식> sp_bindrule rule_name, 'table.col_name'
예) sp_bindrule payrule, 'ex1.sapay'
Delete from ex1
insert into ex1 values('han', 150)
☞실행되지 않는다.
insert into ex1 values('kkk', 50)--적용되지 않는다
☞ 실행은 된다.
select * from ex1
9) Rule의 bind 풀기
형식> sp_unbindrule 'table.col명'
예) sp_unbindrule 'ex1.sapay'
Delete from ex1
insert into ex1 values('han', 150)--적용되지 않는다
☞ 실행은 된다.
insert into ex1 values('kkk', 50)--적용되지 않는다
☞ 실행은 된다.
select * from ex1
10) 삭제
형식> drop rule rule_name,rule_name,rule_name,....
예) drop rule payrule
==========================================================================
여태 까지는 DDL명령, 지금부터는 DML명령이다
===========================================================================
즉 select, insert, delete, update
형식>selectcollist, collist, collist,.....
from table_name, table_name, table_name,......
where 조건식, 조건식,..... /* Where가 생략되면 모든 레코드 */
group by 1차그룹, 2차그룹.....
having 그룹에 대한 조건, 그룹에 대한 조건, .....
order by collist [asc|desc], collist [asc|desc]......
compute 계산식, 계산식,...... /* Oracle은 Break On */
/* where절과 having절이 같이 쓰이면 having절이 우선 */
예1) 모든것을 보자
select * from sawon
예2) 원하는 필드만 보자
select saname, buseono, sapay from sawon
예3) 표제를 변경하자
select saname as '사원이름', sapay as '급여' from sawon --Oracle 및 MS-SQL 기법
select saname as 사원이름, sapay as 급여 from sawon --문자는 ' ' 이다
select saname as 사원 이름, sapay as 급여 from sawon --안된다
select saname as '사원 이름', sapay as '급여' from sawon
select '사원이름'=saname, '급여'=sapay from sawon ☞ SQL에서만 된다.
예4) 사원이름과 급여로 표제를 바꾸고 sapay의 내림차순으로 출력
select saname as '사원이름', sapay as '급여' from sawon
order by sapay desc ☞ asc 는 내림차순
예5) 사원Table에서 과장과 주임만 sapay를 내림차순으로 보자
☞ 반드시 필드명이 있어야 한다.
select * from sawon where sajob='과장' or sajob='주임'
order by sapay desc
select * from sawon where sajob='과장', '주임' --안된다
order by sapay desc
select * from sawon where sajob in('과장','주임')
order by sapay desc
☞ in 비교 연산자
예6) 원하는 자료를 하나의 그룹으로만 출력
select sajob from sawon
select distinct sajob from sawon
☞ distinct 중복되는 것은 제외
select distinct gosex from gogek
select distinct godam from gogek
select distinct gosex, godam from gogek
===============================================================================
● 연산자 공부하자
1. 산술연산자+(덧셈),-(뺄셈),*(곱셈),/(나눗셈), %(나머지)
2. 관계연산자=,>,<,>=,<=,<>, !=같지않다, !<크지않다, !>작지않다
3. 논리연산자NOT,AND,OR
4. 비교연산자
1) between 10 and 100, 또는 not between 10 and 100
예> sawon의 buseono가 10부터 30까지
select * from sawon where buseono between 10 and 30
select * from sawon where buseono between 30 and 10 --안된다
2) in(값1, 값2,....) 또는 not in
예> sawon의 sajob이 '대리' or '과장'
select * from sawon where sajob in('대리','과장')
3) is null, is not null
4) like, not like
%:모든 문자(Like와....)
_(UnderBar):1문자 해당
[ ]:대괄호안에 있는 문자중에 1개
[^]:대괄호안에 없는 문자
예>
saname like '김%':김씨만 출력하라 ☞ "김*"은 Access 기법이다
sabun like '[0-9]':0부터 9까지
saname like 'a[^b]%':첫글자가 a로 시작하고 두번째 문자 b가 아닌것
1) sawon 테이블에서 김씨가 아닌 사람을 출력
select * from sawon where saname not like '김%'
select * from sawon where saname like '김%' -- 안된다
select * from sawon where saname = '김%' -- 안된다
select * from sawon where saname not like "김*"-- 안된다
2) sawon 테이블에서 김씨, 이씨, 신씨가 아닌 사람
select * from sawon where saname not like '[김, 이, 신]%'
select * from sawon where saname not in ('[김%, 이%, 신%]') -- 안된다.....
-- 즉 saname='김%' or saname='이%' or saname='신%' 이다
select * from sawon where saname not in ('김%', '이%', '신%') -- 안된다.....
-- 즉 Like와 %는 실과 바늘...
3) gogek 테이블에서 gotel이 1번부터 3번으로 시작하는 것을 출력
select * from gogek where gotel like '[1-3]%'
select * from gogek where goname like '[박-이]%'
4) sawon 테이블에서 직책별로 인원수와 급여합을 구하라
select sajob as '직책', count(*) as '인원수', sum(sapay) as '급여합' from sawon
group by sajob
select sajob as '직책', count(*) as '인원수', sum(sapay) as '급여합' from sawon
-- 안된다 일반필드는 그룹필드와 같이 쓸 수 없다
5) sawon 테이블에서 직책별로 인원수와 급여합을 구하는데 직책별이 2이상 내림차순..
select sajob as '직책', count(*) as '인원수', sum(sapay) as '급여합'
from sawon
group by sajob
having count(*)>=2
order by count(*) desc, sum(sapay) -- 2차 Sort Key(sum(sapay))의 기본값은 ASC
6) sawon 테이블에서 사원과 대리를 제외한 table (18)
select *
from sawon
where sajob not in('사원','대리')
select *
from sawon
where sajob not like('[사원,대리]%') --안된다(사장이 없으므로...),
-- [ ]는 대괄호안에 있는 문자중에 1개
7) sawon 테이블에서 부서번호는 10,20이고 직책은 부장,과장인 사람찾기 (2)
select *
from sawon
where buseono in(10,20)
and
sajob in('부장','과장')
8) sawon 테이블에서 부서번호는 10,20이고 급여가 100000~200000이 아닌 사람찾기 (8)
select *
from sawon
where buseono in (10,20)
and
sapay not between 100000 and 200000
9) 날짜가 99년 2월을 출력하라
select * from sawon where sahire between '2-1-99' and '2-28-99' --날짜형식이므로...
select * from sawon where sahire between 99-2-1 and 99-2-28
select * from sawon where sahire between '99-2-1' and '99-2-28'
select * from sawon where sahire like '2-%-99' --문자형식이므로 안된다...
select * from sawon where sahire between #99-2-1# and #99-2-28# --Access와 VB이다
10) gogek 테이블에서 전화번호가 없는 사람찾기 (0)
select * from gogek where gotel is null --안된다
select * from gogek where gotel = null --안된다
select * from gogek where gotel = ''
11) sawon테이블에서 이름의 두번째 글자가 '은'인 사람찾기 (2)
select * from sawon where saname like'_은%'
select * from sawon where saname like'_은_' -- 안된다
select * from sawon where saname like'%은%' -- 안된다
12) sawon테이블에서 이름이 두글자로 되어 있으며 두번째 글자가 ' 현'인 사람찾기 (0)
select saname from sawon where saname like '_현'
13) sawon 테이블에서 이름이 두글자로 되어 있는 사람찾기 (4)
select saname from sawon where saname like '__'
====================================================================================
● 간단한 JOIN
INNER JOIN두 테이블의 일치하는 자료만 출력
LEFT OUTER JOIN왼쪽 테이블의 자료는 모두출력하고 오른쪽 것은 일치하는 것만
RIGHT OUTER JOIN오른쪽 테이블의 자료는 모두출력하고 왼쪽것은 일치하는 것만
SELF JOIN자신의 테이블에 결합
CROSS JOIN
--두 table간의 곱(Cartesian product)이 나온다. 잘 사용 안함(학술적 의미)
--where절이 없다.
--양쪽 table의 모든 행에 대해 서로 연결
select * from sawon cross join buseo
select * from sawon cross join gogek
select * from gogek cross join buseo
문제1> buseo 테이블과 sawon 테이블이 일치(inner join이다)
select *
from buseo, sawon
where buseo.buseono = sawon.buseono
☞ sawon.buseono 은 테이플명. 필드명
문제2> buseo테이블과 sawon 테이블이 일치에서 일부분만 출력(inner join이다)
select buseo.buseono, buseo.buseoname, sawon.saname, sawon.sajob
from buseo, sawon
where buseo.buseono = sawon.buseono
select buseo.buseono, buseoname, saname, sajob
from buseo, sawon
where buseo.buseono = sawon.buseono
★★★ ANSI-SQL과 T-SQL의 의미★★★
-- 아래는 ANSI-SQL Join 기법이다
select buseo.buseono, buseoname, saname, sajob
from buseo inner join sawon
on buseo.buseono = sawon.buseono
문제3> buseo테이블과 sawon 테이블에서 buseo를 전부 출력(outer join이다)
select buseo.buseono, buseo.buseoname, sawon.saname, sawon.sajob
from buseo, sawon
where buseo.buseono *=sawon.buseono
Oracle은?
☞ left outer join
select buseo.buseono, buseo.buseoname, sawon.saname, sawon.sajob
from buseo, sawon
where buseo.buseono=sawon.buseono(+)
☞ buseo.buseono(+)=sawon.buseono 은 right outer join
======================================================================================
★ Self Join에 대해서
drop table self
create TABLE self(sabun int,
sanamevarchar(10) not null,
sajob varchar(10),
sapay numeric(10,0),
sahire datetime,
sasex varchar(4),
saMgr int,
constraint self_sabun_PK primary key(sabun),
constraint self_samgr_FK foreign key(samgr) references self(sabun))
select * from self
Insert Into self Values(1,'윤위현','회장',5000,'2-12-1990','남자',null)
Insert Into self Values(2,'한국남','부장',3000,'2-12-1992','남자',1)
Insert Into self Values(3,'이순신','과장',3500,'3-13-1993','남자', 2)
Alter table self NOCHECK Constraint self_samgr_fk
☞ 위 문장을 실행하지 않고 바로 실행 할 경우 에러 발생
이유는 다음 17번에서 연결되어 있는 문서가 없기 때문이다.
Insert Into self Values(4,'이미라','대리',2503,'3-16-1996','여자', 17)
Insert Into self Values(5,'윤희경','사원',1200,'3-19-1999','여자', 3)
Insert Into self Values(6,'공부만','과장',4003,'4-15-1993','남자', 17)
Insert Into self Values(7,'놀기만','과장',2300,'4-20-1993','여자', 2)
Insert Into self Values(8,'채송화','대리',1703,'4-30-1996','여자', 17)
Insert Into self Values(9,'무궁화','사원',1100,'5-1-1999','여자', 12)
Insert Into self Values(10,'공부해','사원',1303,'5-3-1998','남자', 4)
Insert Into self Values(11,'배은주','과장',1600,'5-8-1994','여자', 2)
Insert Into self Values(12,'류명한','대리',1800,'5-20-1997','남자', 20)
Insert Into self Values(13,'무궁화','부장',3000,'6-1-1992','여자', 1)
Insert Into self Values(14,'채시라','사원',3400,'3-20-1999','여자', 3)
Insert Into self Values(15,'최진실','사원',2000,'2-28-1999','여자', 12)
Insert Into self Values(16,'김유신','사원',400,'4-1-1998','남자', 4)
Insert Into self Values(17,'이성계','부장',2803,'11-9-1993','남자', 1)
Insert Into self Values(18,'강감찬','사원',1003,'2-9-1998','남자', 4)
Insert Into self Values(19,'임꺽정','사원',2200,'5-3-1999','남자', 7)
Insert Into self Values(20,'윤태운','과장',4500,'7-8-1994','남자', 13)
Alter table self CHECKConstraint self_samgr_fk
☞ 체크 표시를 다시 하여 문서 간에 연결을 다시 함
예1> 어느 사원의 관리자는 누구인가?
SANAME SAMGR SANAME
---------- --------- ---------- ------------------
한국남 1 윤위현
이순신 2 한국남
이미라 17 이성계
select saname, samgr, saname from self --안된다
Select A.Saname, A.Samgr, B.Saname
From self A, self B
Where A.Samgr=B.Sabun
☞ Select A.Saname,B.Saname 이란 가상의 테이블을 만드는 것이다.
예2) 회장이 관리하는 사람을 출력
SANAME SAJOB SANAME
---------------- --------- ---------- -----------
한국남 부장 윤위현
무궁화 부장 윤위현
이성계 부장 윤위현
SELECT A.SANAME, A.SAJOB, B.SANAME
FROM self A, self B
WHERE A.SAMGR = B.SABUN AND B.SABUN=1
☞ 회장이 1이으모 (고유성 때문)
예3) 사원의 목록을 전부 출력하고, 그 사원의 관리자를 SELF JOIN하여 출력하여라.
SANAME SAJOB SANAME SAJOB
---------- ---------- ---------- -------------------------------
윤위현 회장
한국남 부장 윤위현 회장
이순신 과장 한국남 부장
이미라 대리 이성계 부장
윤희경 사원 이순신 과장
공부만 과장 이성계 부장
놀기만 과장 한국남 부장
SELECT A.SANAME, A.SAJOB, B.SANAME, B.SAJOB
FROM self A , self B
WHERE A.SAMGR*=B.SABUN
예4) 6번 공부만 보다 급여를 많이 받는 사람의 목록을 SELF JOIN하여 출력하여라.
SANAME SAPAY SANAME SAPAY
---------- --------- ---------- ---------------- -------------
공부만 4003 윤위현 5000
공부만 4003 윤태운 4500
SELECT A.SANAME, A.SAPAY, B.SANAME, B.SAPAY
FROM self A, self B
WHERE A.SANAME='공부만' AND A.SAPAY<B.SAPAY
================================================================================
● 산술함수
abs(n), acos, asin, cos, sin, tan, exp()지수값을 구하는 정수 , log(), pi()
ceiling(n):n보다 큰 정수값,
ceiling(10.3)=11, ceiling(-10.3)=-10, 즉 오른쪽값
floor:n보다 작은 정수값
floor(10.3)=10, floor(-10.3)=-11, 즉 왼쪽값
degrees(n):라디안 값(호도)을 각도로 계산
log10():상용 로그 값
power(n,m):n의 m승
radians():호도를 라디안으로
sign():양수 음수 0을 표시
sqrt():루트
round(n,p):round(10.7568,2)는 10.76
☞ round(n,p) :p 값이 양수 이면 소수점 오른쪽 p째 자리 까지
음수 이면 수수점 왼쪽으로 p 째 자리 까지 계산
실습>
select ceiling(10.5)/* 11 */
select floor(-10.5)/* -11*/
select degrees(pi()/6)/* 29.999999*/
select log10(100)/* 2.0*/
select power(2,5)/* 32*/
select sqrt(2)/* 1.4142135623731*/
select ceiling(10.5), floor(-10.5), degrees(pi()/6), log10(100), power(2,5), sqrt(2)
Oracle은
select saname, sabun, power(sabun,3), sapay, round(sapay,-3), trunc(sapay,-3) from sawon;
select distinct abs(-5), sin(30*3.141592/180), sqrt(2), log(10,1000) from sawon;
1) select round(10.7856,2)/* 10.7900*/
2) select round(67584.234,-2)/* 67600.000*/
3) select round(67584.234,0)/* 67584.000*/
4) select round(49.123,2)/* 49.120*/
5) select round(49.123,-2)/* 0.000*/
6) select round(49,-2)/* 0*/
7) select round(49,-1)/* 50*/
8) select round(49.0,-1)/* 50.0*/
9) select round(49.0,-2)/* 0.0*/
10) select round(50,-2)/* 100*/
11) select round(50.0,-2)/* 100.0*/
12) select round(50,-1)/* 50*/
13) select round(50.0,-1)/* 50.0*/
14) select round(50.,-2)/* 100*/
15) select round(5.,-1)/* 10*/
실습> sawon 테이블의 이름, 급여, 보너스(70%까지로 100원까지), 연봉을 구하라
/* 정수*실수=실수 */
1) select saname as '이름', sapay as '급여',
sapay*0.7 as '보너스',
sapay*0.7*6+sapay*12 as '연봉' from sawon
2) select saname as '이름', sapay as '급여',
round(sapay*0.7,-2) as '보너스',
round(sapay*0.7,-2)*6+sapay*12 as '연봉' from sawon
이름 급여 보너스 연봉
---------- -------------- ---------------- -------------------
엄정화 10000 7000.0 162000.0
룰라 10000 7000.0 162000.0
강수지 20000 14000.0 324000.0
설운도 30000 21000.0 486000.0
서태지 40000 28000.0 648000.0
송대관 50000 35000.0 810000.0
남진 60000 42000.0 972000.0
3) select saname as '이름', sapay as '급여',
int(round(sapay*0.7,-2)) as '보너스',
int(round(sapay*0.7,-2)*6+sapay*12) as '연봉' from sawon
--안된다 'int' is not a recognized function name.
문법 : CONVERT (datatype[(length)], expression [, style])
4) select saname as '이름', sapay as '급여',
convert(int,round(sapay*0.7,-2)) as '보너스',
convert(int,round(sapay*0.7,-2)*6+sapay*12) as '연봉' from sawon
이름 급여 보너스 연봉
---------- ------------ ----------- ------------- -------
엄정화 10000 7000 162000
룰라 10000 7000 162000
강수지 20000 14000 324000
설운도 30000 21000 486000
서태지 40000 28000 648000
송대관 50000 35000 810000
남진 60000 42000 972000
==============================================================================
● 문자함수
+:문자열을 결합
ascii('문자'):문자에 대한 ascii값
char(숫자):숫자에 대한 ascii문자
charindex('문자','문자열'):charindex('r','korea')은 3, VB의 instr를 연상
lower():소문자, VB의 Lcase를 연상
upper():대문자, VB의 Ucase를 연상
reverse('kor'):rok
ltrim, rtrim:공백제거
substring :VB의 Mid연상
space(n):n개의 공백을 채운다
str(숫자)
stuff('문자열1', 위치, 갯수, '문자열2')
:문자열1의 위치에서 갯수만큼을 지우고 문자열2로 치환
stuff('korea', 3, 2, 'yoon') 즉 koyoona
replicate('문자열', 반복횟수)
간단한 문제>
select ascii('a'), char(65), charindex('r','korea'), lower('KOREA'),
upper('korea'), reverse('korea'), ltrim(' korea'),
str(123), stuff('korea', 3, 2, 'yoon')
결과) 97 A 3 korea
KOREA aerok korea
123 koyoona
문제1> +에 대하여 (Sawon 테이블에서 씨, 번, 님)
select saname+'씨' as '이름', buseono +'번' as '부서', sajob+'님' as '직책' from sawon --안된다
select saname+'씨' as '이름', str(buseono)+'번' as '부서', sajob+'님' as '직책' from sawon
☞ str 은 문자로 변환
select '이름'=saname+'씨', '부서'=str(buseono)+'번' , '직책'=sajob+'님' from sawon
이름 부서 직책
------------------------ ------------ ------
엄정화씨 10번 사원님
룰라씨 20번 대리님
강수지씨 30번 부장님
문제2> replicate에 대하여(Sawon 테이블에서 급여를 1만원권으로 바꾸었을 때 *로 표기)
select saname as '사원이름', sapay as '급여', replicate('*', sapay/10000) as '별표' from sawon
사원이름 급여 별표
-------------- -------------- -------------------
엄정화 10000 *
룰라 10000 *
강수지 20000 **
설운도 30000 ***
서태지 40000 ****
송대관 50000 *****
남진 60000 ******
나훈아 70000 *******
문제3> charindex, Substring, Null에 대하여...
sp_helpsort
sawon 테이블에서 이름이 '이'만 추출 (설치시 Korea Type이므로 1이다, 즉 문자단위이다)
select saname from sawon where saname like '%이%'
select * from sawon
select saname, charindex('이', saname) from sawon
☞ 이가 몇 번째 았느냐?
select saname, substring(saname, charindex('이', saname), 1) as '선택' from sawon
☞substring 은 순서와 상관없이 찾겠다.
select saname, isnull(substring(saname, charindex('이', saname), 1),'') as '선택' from sawon
☞ null 값은 푠현하지 않음
select saname, isnull(substring(saname, charindex('이', saname), 1),'') as '선택' from sawon where saname like '%이%'
★ ISNULL 에서...
형식> ISNULL(check_expression, replacement_value)
Null값과 의 연산은 Null이다
use pubs
select * from discounts
discounttype stor_id lowqty highqty discount
---------------------------------------- ------- ------ ------------------------------------------
Initial Customer NULL NULL NULL 10.50
Volume Discount NULL 100 1000 6.70
Customer Discount 8042 NULL NULL 5.00
select stor_id, lowqty, lowqty+100 from discounts
☞ lowgty 은 최저값 표시
select stor_id, lowqty, isnull(lowqty,0)+100 from discounts
☞ null 값을 0 으로 대치
-- Oracle은 형식은 같고, 다만 isnull이 아니고 nvl이다
use mydb
===================================================================================================
문제> 월급을 계산(그룹별)하여 보자 (Compute로 EXCEL의 부분합을 연상하라)
1) 일반적인 월급계산
select saname, sajob, sapay from sawon
select sum(sapay) as '월급합계' from sawon
월급합계
----- --------
2950000
2) sawon테이블에서 saname으로 sort하고 마지막에 월급의 합계 출력(compute)
select saname, sajob, sapay from sawon
order by saname --생략가능
compute sum(sapay)
saname sajob sapay
--------------- --- ----- ---------
강수지 부장 20000
김건모 과장 90000
김추자 과장 190000
나훈아 사원 70000
sum
--------
2950000
3) sawon테이블에서 직급(sajob)별로 합계 출력
이때 반드시 order by가 선언되어야 한다 (부분합이므로)
COMPUTE 절은 전체에 대한 계산을 한다.
COMPUTE BY 절은 그룹에 대한 계산을 한다.
그러므로 반드시 ORDER BY를 사용해야 한다.
select saname, sajob, sapay from sawon
order by sajob --생략불가능
compute sum(sapay) by sajob
saname sajob sapay
---------- ----- --------------
김건모 과장 90000
김추자 과장 190000
안재욱 과장 60000
이문세 과장 190000
sum
==============
530000
saname sajob sapay
---------- ----- --------------
이선희 담당 70000
sum
==============
70000
saname sajob sapay
---------- ----- --------------
룰라 대리 10000
서태지 대리 40000
이은하 대리 130000
신성훈 대리 80000
sum
==============
260000
select saname, sajob, sapay from sawon
compute sum(sapay) by sajob --안된다
select saname, sajob, sapay from sawon
order by sajob
compute sum(sapay) --안된다
-- 부서별로 출력
4) sawon테이블에서 직급(sajob)별로 합계출력, 마지막에 총합계출력
select saname, sajob, sapay from sawon
order by sajob
compute sum(sapay) by sajob
compute sum(sapay)
5) sawon테이블에서 직급(sajob)별로 합계출력 평균출력,
마지막에 총합계출력 총평균 출력
select saname, sajob, sapay from sawon
order by sajob
compute sum(sapay) by sajob
compute avg(sapay) by sajob
compute sum(sapay)
compute avg(sapay)
6) sawon테이블에서 buseono은 1차그룹, 직급(sajob)별로 2차 그룹을 선언하고,
sapay로 Sort하고, 합계를 출력, 마지막에 총합계출력
select buseono, sajob, saname, sapay from sawon
order by buseono, sajob, sapay
compute sum(sapay) by buseono, sajob
compute sum(sapay)
● 전체를......
select saname, sajob, sapay from sawon
select sum(sapay) as '월급합계' from sawon
select saname, sajob, sapay from sawon order by saname compute sum(sapay)
select saname, sajob, sapay from sawon order by sajob compute sum(sapay) by sajob
select saname, sajob, sapay from sawon order by sajob compute sum(sapay) by sajob compute sum(sapay)
select buseono, sajob, saname, sapay from sawon order by buseono, sajob, sapay compute sum(sapay) by buseono, sajob compute sum(sapay)
7) Compute를 GROUP BY, ROLLUP, CUBE 로....
예) group by만 이용한 경우
select buseono, sajob, avg(sapay), sum(sapay), count(sabun) from sawon
group by buseono, sajob
예) rollup를 이용한 경우
select buseono, sajob, avg(sapay), sum(sapay), count(sabun) from sawon
group by buseono, sajob with rollup
예) cube를 이용한 경우
select buseono, sajob, avg(sapay), sum(sapay), count(sabun) from sawon
group by buseono, sajob with cube
8) 각 부서별 월급합계 및 월급평균을 구하라
예) compute 를 이용한 경우
예) group by 를 이용한 경우
예) rollup 를 이용한 경우
예) cube 를 이용한 경우
buseono 월급합계 월급평균
------------------------- -------------------------------
10 520000 86666.666666
20 780000 130000.000000
30 510000 127500.000000
40 540000 135000.000000
select buseono, saname, sajob, sapay from sawon order by buseono
compute sum(sapay) by buseono
compute avg(sapay) by buseono
compute sum(sapay)
compute avg(sapay)
select buseono, sum(sapay) as '월급합계', avg(sapay) as '월급평균' from sawon
group by buseono
select buseono, sum(sapay) as '월급합계', avg(sapay) as '월급평균' from sawon
group by buseono with rollup
select buseono, sum(sapay) as '월급합계', avg(sapay) as '월급평균' from sawon
group by buseono with cube
★ Excel로 Export (엑셀에서 직접 Setting)
데이터\데이터 가져오기\새질의 작성
직급별 인원수를 그래프로 출력
데이터\피벗테이블 보고서\외부데이터
★ CR 로 작성 (#3 \Common\Tools\Vb\Crysrept
☞영문
#3 \Common\Tools\Vb\Korean\Crysrept )
☞한글
● Sub Query 조건비교의 결과에 따라 연산,
비슷한 개념으로 1*2-(3/4), ( )를 먼저 생각
서브쿼리는 10번부서라는 개념처럼, 정해진 명령이고,
상관쿼리는 각 부서란 명령이 있다
문제1> sawon 테이블에서 일반적인 급여평균
select avg(sapay) as 급여평균 from sawon
select saname, sajob, sapay from sawon compute avg(sapay)
문제2> sawon 테이블에서 급여가 평균보다 큰사람
saname sajob sapay
---------- ----- --------------
영턱스 회장 150000
박진영 주임 180000
송창식 부장 110000
혜은이 주임 110000
select saname, sajob, sapay from sawon
where sapay>( select avg(sapay) from sawon )
문제3) sawon테이블에서 엄정화와 같은 부서(buseono)에 근무하는 사람찾기
saname buseono sajob
---------- ----------- ----- ----------
엄정화 10 사원
신성우 10 주임
이선희 10 담당
신성훈 10 대리
select saname, buseono, sajob from sawon
where buseono = (select buseono from sawon where saname='엄정화')
문제4) sawon테이블에서 급여가 가장 많은 사람 찾기
saname sapay sajob
------------------- -------------- -----
소찬희 280000 주임
주영훈 280000 주임
select saname, sapay, sajob from sawon
where sapay = (select max(sapay) from sawon)
문제5) sawon테이블에서 사원들 중 급여가 가장 많은 사람은?
1)select saname, sapay, sajob from sawon
where sajob='사원' and sapay= (select max(sapay) from sawon where sajob='사원')
saname sapay sajob
------------------- -------------- -----
윤태운 120000 사원
2)select saname, sapay, sajob from sawon
where sapay= (select max(sapay) from sawon where sajob='사원') -- 안된다
saname sapay sajob
------------------ -------------- -----
이기찬 120000 주임
윤태운 120000 사원
3)select saname, sapay, sajob from sawon
where sajob='사원' and sapay= (select max(sapay) from sawon) --안된다
saname sapay sajob
-------------------- ------------ -----
(0 row(s) affected)
예6) 10번 부서에서 전체 부서의 평균급여보다 급여가 적은 사람 찾기?
select * from sawon
where buseono=10 and sapay<(select avg(sapay) from sawon)
-- 전체 부서에서 10번 부서의 평균급여보다 급여가 적은 사람 찾기?
select * from sawon
where sapay<(select avg(sapay) from sawon where buseono=10)
-- 전체 부서에서 전체 부서의 평균급여보다 급여가 적은 사람 찾기?
select * from sawon
where sapay<(select avg(sapay) from sawon)
예7) 20번 부서에서 10번부서의 최소급여보다 급여가 많은 사람 찾기?
select saname, sajob, sapay from sawon
where buseono=20 and sapay>(select min(sapay) from sawon where buseono=10)
예8) 10번부서에서 최대급여, 최소급여를 받는 사람찾기?
select saname, sajob, sapay from sawon
where buseono=10 and (sapay=(select min(sapay) from sawon where buseono=10)
or sapay=(select max(sapay)from sawon where buseono=10))
select saname, sajob, sapay from sawon
where buseono=10 and sapay in (select Max(sapay) from sawon where buseono=10
Union select Min(sapay) from sawon where buseono=10)
-- 이런 방법은 안된다(in을 쓸수 없다, 값의 수가 너무 많습니다 즉 수식필드는 하나만 가능)
select saname, sajob, sapay from sawon
where buseono=10 and sapay
in(select min(sapay), max(sapay) from sawon where buseono=10)
예9) 10번부서의 최대, 최소 급여를 Union(합집합)하여라
select saname, sajob, sapay from sawon
where buseono=10 and sapay in
(select min(sapay) from sawon where buseono=10
union
select max(sapay) from sawon where buseono=10)
예10) "룰라"와 부서가 같고 직책이 같은 사람찾기?
select saname, sajob from sawon
where buseono=(select buseono from sawon where saname='룰라')
and sajob=(select sajob from sawon where saname='룰라')
예11) 전체부서에서 20번부서의 최소급여보다 많이 받는 사람찾기?
select saname, sajob, sapay from sawon
where sapay>(select min(sapay) from sawon where buseono=20)
예12) 10번부서의 직책이 사원인 사람 중에서 사원의 평균급여보다 급여가 적은 사람은?
select saname, sajob, sapay, buseono from sawon
where buseono=10 and sajob='사원' and
sapay < (select avg(sapay) from sawon where sajob='사원')
● ALL : 모든(전체) 자료보다.....,
ANY : 어떤(일부분의) 자료보다......
예1) 전체 sawon 테이블에서 총무부의 최대급여보다 큰사람
(참고로 총무부를 buseono=10으로 보았을 때)
해결1> select saname, sapay, sajob from sawon
where sapay> (select max(sapay) from sawon where buseono=10)
saname sapay sajob
------------------- -------------- -----
김추자 190000 과장
소찬희 280000 주임
주영훈 280000 주임
이문세 190000 과장
해결2> select saname, sapay, sajob from sawon
where sapay > all(select sapay from sawon where buseono=10)
예2) 전체 sawon 테이블에서 총무부의 최소급여보다 큰 사람
(참고로 총무부를 buseono=10으로 보았을 때)
해결1> select saname, sapay, sajob from sawon
where sapay>(select min(sapay) from sawon where buseono=10)
saname sapay sajob
---------- ----------------------- -----
강수지 20000 부장
설운도 30000 차장
서태지 40000 대리
송대관 50000 사장
남진 60000 사원
나훈아 70000 사원
해결2> select saname, sapay, sajob from sawon
where sapay>any (select sapay from sawon where buseono=10)
● 상관 Query
(1) EXCEL의 Rank를 참조
(2) 연산자 조심
(3) Sub에서는 Main을 참조하고 Main 에서는 Sub를 참조하는 Query
(4) 자기자신과 비교하므로 별명이 필요(즉 복제) ▶ Self Join의 연상
(5) 문제에 "각"이란 단어가 있다(?)
(6) 별명인 "a"를 생략해 보자
문제1) sawon 테이블에서 각 부서의 평균급여보다 급여를 많이 받는 사람을 출력하고 번호로 Sort
saname buseono sajob sapay
------------------ ----------- ----- --------------
신성우 10 주임 90000
김건모 10 과장 90000
이미자 10 주임 180000
박진영 20 주임 180000
select saname, buseono, sajob, sapay from sawon a
where sapay > (select avg(sapay) from sawon where buseono=a.buseono)
order by buseono-- 생략해 본다
문제2) sawon 테이블에서 각 부서에서 급여를 가장 많이 받는 사람을 출력하고, 번호로 Sort.
saname buseono sajob sapay
---------- ----------- ----- ---------------------
이미자 10 주임 180000
주영훈 20 주임 280000
김추자 30 과장 190000
이문세 30 과장 190000
select saname, buseono, sajob, sapay from sawon a
where sapay = (select max(sapay) from sawon where buseono=a.buseono)
order by buseono -- 생략해 본다
문제3) sawon 테이블에서 급여순위가 5위 이내의 사람을 내림차순 출력하라.(Rank 함수)
saname sapay sahire
---------- ------------ ---------------------------
주영훈 280000 1999-06-21 00:00:00.000
소찬희 280000 1999-06-05 00:00:00.000
이문세 190000 1999-05-11 00:00:00.000
김추자 190000 1999-05-03 00:00:00.000
select saname, sapay, sahire from sawon a
where 5 > (select count(*) from sawon where sapay > a.sapay)
order by sapay desc, sahire desc -- 생략해 본다
문제4) sawon 테이블의 각 직책에서 급여를 가장 많이 받는 사람과 가장적게 받는 사람을 구하시오.
saname sapay sajob
---------- ------------ ------- --------
김추자 190000 과장
이문세 190000 과장
안재욱 60000 과장
이선희 70000 담당
select saname, sapay, sajob from sawon a
where sapay=(select max(sapay) from sawon where sajob=a.sajob) or
sapay=(select min(sapay) from sawon where sajob=a.sajob)
order by sajob, sapay desc-- 생략해 본다
문제5) sawon 테이블에서 각 부서별로 급여 서열 2위까지 출력하고 Sort.
saname buseono sajob sapay
---------- ----------- ----- ------------------
이미자 10 주임 180000
신성우 10 주임 90000
김건모 10 과장 90000
주영훈 20 주임 280000
박진영 20 주임 180000
Select saname, buseono, sajob, sapay from sawon a
where 2 > (select Count(*) from sawon where sapay > a.sapay and buseono=a.buseono)
order by buseono, sapay desc-- 생략해 본다
서브쿼리와의 차이점은 서브쿼리는 질의만 수행해도 수행되는 반면,
상관쿼리는 안쪽쿼리만 수행하면 Error발생.
서브쿼리는 안쪽쿼리가 바깥쿼리에 돌려지는 반면,
상관쿼리는 바깥쿼리에서 선택된 행이 안쪽쿼리의 Where절에서 다시 참조된다.
상관쿼리는 서브쿼리보다 훨씬 느리다.(될수있으면 피해라)
● 지금부터는 Access의 Action Query를 연상하자
Make Table을 연상
형식>
select 필드리스트 into 새로운 테이블 이름 from 기존 테이블 이름
이때 서버에서 mydb\Properties\Options에서 허가..(6.5에서, 7.0은 기본값)
문제1> sawon 테이블 전체를 sam1로 Make Table 하시오(일종의 Copy)
select * into sam1 from sawon
select * from sam1
문제2> buseono=10인 가운데, 일부분만 sam1 테이블에서 sam2로 Make Table 하시오
select saname, buseono, sajob, sapay into sam2 from sam1 where buseono=10
select * from sam2
문제3> sajob='사원'인 가운데, 일부분만 sam2 테이블에서 sam3로 Make Table 하시오
select saname, buseono, sajob, sapay into sam3 from sam2 where sajob='사원'
select * from sam3
● UNION두 테이블간의 무중복합 (단 첫번째 필드가 자동정렬)
UNION ALL 두 테이블간의 중복합 (차례로 Append)
합집합을 구한다(중복도 가능 단 필드는 일치))
select * from sam2
saname buseono sajob sapay
---------- ----------- ----- -------------------
엄정화 10 사원 10000
신성우 10 주임 90000
이선희 10 담당 70000
신성훈 10 대리 80000
김건모 10 과장 90000
이미자 10 주임 180000
select * from sam3
saname buseono sajob sapay
---------- ----------- --------- --------------
엄정화 10 사원 10000
select * from sam2 union select * from sam3
select * from sam2 union all select * from sam3
select * from sam2 union all select * from sawon --안된다
select * from sam2 union all select saname, buseono, sajob, sapay from sawon --된다
select saname, buseono from sam2 union all select saname, buseono from sawon --된다
-- 오라클의 Intersect(두 Query의 교집합), Minus(두 Query의 차집합)의 기능은 없다
문제> Sawon의 직급에서, 사원 중 최대급여 최소급여를 받는사람은 누구인가?
(단 서브쿼리가 아닌 Union으로 할것)
-- 서버의 교통량을 생각하면 서브, 상관쿼리는 권장사항이 아니다
sabun saname buseono sajob sapay sahire
--------- ----- ------------ -------------------------------------------------------
1 엄정화 10 사원 10000 1999-02-12 00:00:00.000
27 윤태운 20 사원 120000 1999-06-17 00:00:00.000
Select * from sawon
where sajob='사원' and sapay in (select Max(sapay) from sawon where sajob='사원'
Union select Min(sapay) from sawon where sajob='사원')
Select * from sawon
where sajob='사원' and (sapay=(select min(sapay) from sawon where sajob='사원')
or sapay=(select max(sapay)from sawon where sajob='사원'))
-- 이런 방법은 안된다(in을 쓸수 없다, 값의 수가 너무 많습니다 즉 수식필드는 하나만 가능)
Select * from sawon
where sajob='사원' and sapay
in(select min(sapay), max(sapay) from sawon where sajob='사원')
● Transaction /* Auto Commit = False 이다 */
1) begin transaction
: 이 명령이 수행된 이후의 모든 작업은 수행을 취소할 수 있다.
2) rollback transaction
: 이 명령으로 이전으로 복구
3) commit transaction
: commit 으로 수행한 작업을 확정 (단 그 이전 작업을 취소할 수 없음)
예1)begin transaction
select * from gogek
delete from gogek where godam=1
select * from gogek
rollback transaction--Delete의 Rollback
select * from gogek
예2)insert into gogek values('윤희경','111-1111','여',2)
select * from gogek
rollback transaction--안된다, begin transaction이 없으니까!!!!
예3)begin transaction
insert into gogek values('윤태운','111-1111','남',2)
select * from gogek
rollback transaction--Insert의 Rollback
select * from gogek
예4)begin transaction
select * into sam4 from sawon
select * from sam4
rollback transaction--MakeTable의 Rollback
select * from sam4--Invalid object name 'sam4'.
예5)insert into gogek values('aaa','111-1111','남',2)
insert into gogek values('bbb','111-1111','남',2)
begin transaction--AutoCommit = Fasle에 의해서...
insert into gogek values('ccc','111-1111','남',2)
insert into gogek values('ddd','111-1111','남',2)
select * from gogek
예1) BEGIN TRANSACTION
:
ROLLBACK TRANSACTION (commit transaction)
예2) BEGIN TRANSACTION
SAVE TRANSACTION AAA
:
SAVE TRANSACTION BBB
:
ROLLBACK TRANSACTION BBB
:
ROLLBACK TRANSACTION AAA
ROLLBACK TRANSACTION
rollback transaction 시 Alias를 붙일경우 save transaction으로 하여야한다.
예3>select * from gogek
BEGIN TRANSACTION
delete from gogek where gobun=1
select * from gogek--1 삭제
SAVE TRANSACTION AAA
delete from gogek where gobun=2
delete from gogek where gobun=3
delete from gogek where gobun=4
select * from gogek--2 3 4 삭제
SAVE TRANSACTION BBB
delete from gogek where gobun=5
delete from gogek where gobun=6
delete from gogek where gobun=7
select * from gogek--5 6 7 삭제
-- ROLLBACK TRANSACTION --모두 복구
-- select * from gogek
ROLLBACK TRANSACTION BBB-- 만약 생략하고, aaa하면...
select * from gogek-- 5 6 7 복구
delete from gogek where gobun=8
delete from gogek where gobun=9
delete from gogek where gobun=10
select * from gogek--8 9 10 삭제
ROLLBACK TRANSACTION AAA --1 을 제외한 나머지의 복구
select * from gogek
ROLLBACK TRANSACTION--1복구
select * from gogek
오라클은 Savepoint name, Rollback To name
● UNION두 테이블간의 무중복합 (단 첫번째 필드가 자동정렬)
UNION ALL 두 테이블간의 중복합 (차례로 Append)
합집합을 구한다(중복도 가능 단 필드는 일치))
select * from sam2
saname buseono sajob sapay
---------- ----------- ----- -------------------
엄정화 10 사원 10000
신성우 10 주임 90000
이선희 10 담당 70000
신성훈 10 대리 80000
김건모 10 과장 90000
이미자 10 주임 180000
select * from sam3
saname buseono sajob sapay
---------- ----------- --------- --------------
엄정화 10 사원 10000
select * from sam2 union select * from sam3
select * from sam2 union all select * from sam3
select * from sam2 union all select * from sawon --안된다
select * from sam2 union all select saname, buseono, sajob, sapay from sawon --된다
select saname, buseono from sam2 union all select saname, buseono from sawon --된다
-- 오라클의 Intersect(두 Query의 교집합), Minus(두 Query의 차집합)의 기능은 없다
문제> Sawon의 직급에서, 사원 중 최대급여 최소급여를 받는사람은 누구인가?
(단 서브쿼리가 아닌 Union으로 할것)
-- 서버의 교통량을 생각하면 서브, 상관쿼리는 권장사항이 아니다
sabun saname buseono sajob sapay sahire
----------- ---------- ----------- ----- ------------ -------------------------------------------------------
1 엄정화 10 사원 10000 1999-02-12 00:00:00.000
27 윤태운 20 사원 120000 1999-06-17 00:00:00.000
Select * from sawon
where sajob='사원' and sapay in (select Max(sapay) from sawon where sajob='사원'
Union select Min(sapay) from sawon where sajob='사원')
Select * from sawon
where sajob='사원' and (sapay=(select min(sapay) from sawon where sajob='사원')
or sapay=(select max(sapay)from sawon where sajob='사원'))
-- 이런 방법은 안된다(in을 쓸수 없다, 값의 수가 너무 많습니다 즉 수식필드는 하나만 가능)
Select * from sawon
where sajob='사원' and sapay
in(select min(sapay), max(sapay) from sawon where sajob='사원')
● Append 명령 (필드명은 반드시 일치[Not Null일 경우])
insert into 테이블명 [(삽입필드 리스트,...... )] /* 생략시는 모든 필드 */
문1) sam2 밑에 sawon 테이블의 번호가 30번을 추가
select * from sam2
select * from sawon
1) insert into sam2
select saname, buseono, sajob, sapay from sawon where buseono=30
2) insert into sam2
select * from sawon where buseono=30 -- 필드의 수가 너무 많습니다.
3) insert into sam2
select saname, buseono from sawon where buseono=30 -- 필드의 수가 충분하지 않습니다
4) insert into sam2(saname, buseono)
select saname, buseono from sawon where buseono=30
문2> 원하는 필드(데이터)만 Insert할 경우
select * from gogek
Insert Into gogek(goname) Values ('봉숭화')
● Update 명령
update 테이블명 set 필드=값,필드=값 .... where 조건(조건이 없으면 모든 컬럼)
문1> sam1에서 월급을 30% 인상하라
update sam1 set sapay=sapay*1.3
select * from sam1
문2> sam1에서 대리를 과장으로 승진 시키고, 월급은 10% 인상하라.
update sam1 set sajob='과장', sapay=sapay*1.1 where sajob='대리'
문3> sam1에서 입사일을 1년을 더하라
update sam1 set sahire=dateadd(yy,1,sahire)
--오라클은 update sam1 set sahire=add_months(sahire,12)
★ 권한의 부여 및 삭제하기(DCL명령) : NT의 도메인사용자관리자를 연상한다
먼저 Login Id를 생성한다 EM에서 sa1, sa2 --Default를 설정하지 않으면 Master로 설정
ISQL에서 sa3, sa4
형식> sp_addlogin login_id [, password [, defdb [, deflanguage [, login_suid]]]]
예> sp_addlogin sa3 "sa3"
sp_addlogin sa4, "sa4", master
★ 권한부여하기
grant select(insert, delete, update)on 테이블명 to 사용자명, 사용자명(or public) [with grant option]
/* public 은 모든사용자 */
/* with grant option 은 권한을 받은 사람이 다른 제3의 사용자에게 권한을 부여할수 있는 명령 */
☞ 앞에 먼저 나온 sa1.혹은 sa, sa2 등은 그곳에서 할 경우를 말한다. 곧 사용자라고 생각하면 된다.
예1> sa1에 sawon 테이블의 선택, 삽입을 할수 있도록 권한을 준다
/* 먼저 sa1에서 sawon 테이블을 select 하고자 sa1로 Logon한다 */
sa1Use mydb/* EM에서 허락(mydb의 Logon에 permissions) */
sa1select * from sawon/* 권한이 없다 */
sagrant select, insert on sawon to sa1
sa1select * from dbo.sawon /* 원래는 소유자인 dbo를 쓴다 */
insert into sawon values('윤태운',10,'대리',10000,'4-3-99')
select * from dbo.sawon
delete from sawon/* 안된다, 그래서 EM에서 허락 */
delete from sawon/* 된다, 단 외래키가 있으므로...*/
create table cc(sabun int) /* 안된다 그래서..... EM에서 허락 */
예2> sa1에 sa2에게 권한을 부여할수 있는 능력을 부여하라
sa1grant select, insert on dbo.sawon to sa2
--DB의 접근권한이 없다,
--권한부여의 능력이 없으므로,,,,,, 그래서 sa에서 부여한다
sa grant select, insert on dbo.sawon to sa1 with grant option
sa1 grant select, insert on dbo.sawon to sa2 --된다
sa2 select * from sawon --된다
★ 권한삭제하기
형식)
revoke [grant option for] select(insert,delete) on table명 from 사용자(or public) [cascade]
-- grant option for란 양도할 수 있는 권한 제거
-- cascade 란제1,제2의 권한부여자들로부터 권한을 가져오는 명령
권한 제거>
sa1grant select, insert on dbo.sawon to sa3 --된다(먼저 EM에서 허락(logon의 permissions))
sa3select * from sawon --된다
sarevoke grant option for select,insert on sawon from sa1 cascade
sa2select * from sawon --cascade에 의하여 안된다
sa3select * from sawon --cascade에 의하여 안된다
sa3grant select, insert on dbo.sawon to sa4 --원래 안된다
sa1select * from sawon --된다(권한 부여 능력만 제거 되었다)
sa1grant select, insert on dbo.sawon to sa4
--안된다 제거 되었기 때문에....
--그러나 Select와 Insert는 여전히 가능하다
--그래서 완전히 없애자
sarevoke select, insert on sawon from sa1
sa1select * from dbo.sawon
--안된다 제거 되었기 때문에....
● VIEW 만들기
테이블에 대한 가상의 테이블(Access의 Query를 연상하면 된다)
복잡한 Query를 간단하게 표현(매우 긴 문장???)
일반유저가 중요한 데이타를 보아서는 안된다 (보안등급을 연상하자)
-- 총무부가 다른 부서의 내용을 보아서는 안된다 (Select, Delete, Update 등)
-- Table에 직접 권한을 주지 않는다 (손상을 주어서는 안된다)
형식) create view view_name as select명령 ~
drop view view_name : create는 무조건 drop이다
예1> sawon 테이블에서 buseono=10인 사람만 보자
select * from sawon
create view v_buseono as select * from sawon where buseono=10
select * from v_buseono
-- sa1에서 select * from v_buseono --안된다 EM에서 권한부여
예2>v_buseono에서 데이터를 추가할 수 있다
insert into v_buseono values('중앙',20,'부장',4500000,'6-26-1999')
select * from sawon
select * from v_buseono
--원본 사원테이블에는 데이터가 추가되었지만 v_buseono에서는 볼수 없다.
--왜 buseono가 10 이기 때문에
--물론 sa1로도 연습을 한다
insert into v_buseono values('이대',10,'과장',4000000,'6-30-1999')
select * from v_buseono -- v_buseono에서 볼수 있다
--물론 sa1로도 연습을 한다
예3>sawon 테이블에서 보너스의 30% 비율을 v_bouns로 출력하라(EM에서 실습)
create view v_bouns
as select saname, sajob, sapay, sapay*0.3 from sawon
--안된다, Could not perform CREATE VIEW because no column name was specified for column 4.
create view v_bouns(이름, 직급, 월급, 보너스)
as select saname, sajob, sapay, sapay*0.3 from sawon
select * from v_bouns
예4>두개의 테이블(buseo, sawon)에서 buseono가 10번인 필드를 선택해서 .v_join을 만든다 (EM에서 실습)
saname sajob buseoname buseoloc
---------- ----- ------------------ ---------------------
엄정화 사원 총무부 부산
신성우 주임 총무부 부산
create view v_join
as select saname, sajob, buseoname,buseoloc from sawon, buseo
where sawon.buseono=buseo.buseono and sawon.buseono=10
select * from v_join
예5)sawon에서 부서별로, 부서별 총원을, 월급의 합계를 출력하라(v_buseo)(EM에서 실습)
create view v_buseo(부서별, 인원수, 월급합계)
as select buseono, count(*), sum(sapay) from sawon group by buseono
-- 반드시 별명을 만들어야 한다
-- 일반필드는 그룹함수와 같이 쓸수 없다 (group by buseono)
select * from v_buseo
레코드의 삭제가 가능하려면.....
1) 하나의 테이블에 의해 생성되어야 한다
2) Group by 에 의해서 만들어지지 않아야 한다.
3) 종속키도 안된다
예6>v_join에서 엄정화를 삭제
delete from v_join where saname='엄정화' --두개의 테이블
-- View 'v_join' is not updatable because the FROM clause names multiple tables.
select * from v_join
v_buseo에서 부서별이 10을 삭제 --그룹
delete from v_buseo where 부서별=10
--View 'v_buseo' is not updatable because it contains aggregates.
sawon에서 엄정화를 삭제
delete from sawon where saname='엄정화' --종속키
-- DELETE statement conflicted with COLUMN REFERENCE constraint 'gogek_godam_fk'.
-- The conflict occurred in database 'mydb', table 'gogek', column 'godam'.
-- The statement has been terminated.
-- 그러나 삭제할수가 있다
sp_helpdb mydb-- Database 전체에 대한 정보
sp_helpfile mydb-- Database 파일에 대한 정보
sp_help sawon-- sawon table에 대한 정보
sp_helpconstraint sawon
select * from sawon
delete from sawon where saname='엄정화' --안된다
sp_help sawon
Alter table gogek NOCHECK Constraint gogek_godam_fk
delete from sawon where saname='엄정화' --된다
select * from sawon
Alter table gogek CHECK Constraint gogek_godam_fk
select * from sawon
delete from sawon where saname='강수지' --지워 진다(매우중요)
select * from sawon
Alter table gogek NOCHECK Constraint gogek_godam_fk
delete from sawon where saname='엄정화'
Alter table sawon CHECK Constraint sawon_buseono_fk
Alter table gogek CHECK Constraint gogek_godam_fk
delete from sawon where saname='강수지'
select * from gogek 시 Cascade 로 삭제(???)
--Count 함수의 보충 설명
select count(gosex) from gogek --null 값은 포함 안한다
select count(distinct gosex)from gogek
select count(*)from gogek --null값을 포함
select gosex from gogek
select distinctgosex from gogek
-- 처리속도의 문제
select * from sawon
select top 3 * from sawon
--sapay의 순위가 하위 3위까지인 사람들의 출력
select * from sawon order by sapay
select top 3 * from sawon order by sapay
--같은 등위인 사람이 여러명 있을 경우
select top 5 * from sawon order by sapay desc
select top 5 with ties * from sawon order by sapay desc
--상위 50%까지인 사람 여러명 있을 경우
select top 50 PERCENT * from sawon order by sapay desc
select top 50 PERCENT with ties * from sawon order by sapay desc
● 사용자 정의 프로시져 만들기 (Stored Procedures)
(1) 현재 사용중인 데이타베이스에 내장 프로시져를 만든다.
sp_를 연상, 즉 함수
(2) SQL문이 미리 COMPILE된 것(반복되는 문장을 수행 시 사용).
SQL문장을 자주 사용한다면 stored procedure를 만들어두는 것이 좋다.
(3) 처음 Compile될 때는 SQL문과 같으나 한번 Compile되면
Cache에 저장되므로 반복적 수행 시 훨씬 빠르다.
(4) Network에서 오고 가는 긴 SQL문의 Network Traffic을 줄일 수 있다.
(5) 보안상의 관리에 편리 (EM에서 Permissions)
Table에 대한 직접적인 권한 대신 Stored Procedure의 실행 권한만 준다
-- Parsing(구문분석)문법을 분석하는 단계
-- Compile실제 binary 형태로 생성된 실행 module.
생성 형식) CREATE PROC 프로시져명 [매개변수] [,매개변수].... AS Sql문장
예1)전체 sawon 테이블을 구하시오
create proc sawon_list_pro as select * from sawon
실행 sawon_list_pro
삭제형식) : DROP PROC 프로시져명
-- Data를 Procedure로 만들어 보자
예2)sawon의 인원수를 구하시오
create proc sawon_count_pro as select count(*) from sawon
실행 sawon_count_pro
삽입 insert into sawon values('윤희경', 10, '부장', 100000, '2-12-1999')
실행 sawon_count_pro
예3)sajob이 과장을 구하라 --EM에서 작성
create proc 과장 as select * from sawon where sajob= '과장'
create proc 부장 as select * from sawon where sajob= '부장'
create proc 대리 as select * from sawon where sajob= '대리'
실행 과장
실행 부장
실행 대리
예4)직급별로 정렬하라.
create proc sajob_sort_pro
as select * from sawon order by case sajob
when '회장' then 1
when '사장' then 2
when '이사' then 3
when '부장' then 4
when '차장' then 5
when '과장' then 6
when '대리' then 7
when '주임' then 8
when '사원' then 9
when '담당' then 10
end
, sapay desc
실행 sajob_sort_pro
sp_helptext sajob_sort_pro
-- Prints the text of a rule, a default, or an unencrypted stored procedure,
-- trigger, or view.
sp_depends sajob_sort_pro
예5) 이름을 갖고 해당이름을 갖는 사람의 정보를 찾는 Stored procedure
create proc saname_pro
@a varchar(10)
☞ @은 변수 선언
as select * from sawon where saname=@a
select * from sawon
실행 saname_pro -- 안된다(@a 때문에...)
saname_pro '이선희'
saname_pro '신성우'
--'이'씨로 시작하는 이름은.....
drop proc saname_pro2
create proc saname_pro2
@a varchar(10),
@b varchar(10)='%'
as select * from sawon where saname like @a+@b order by saname
saname_pro2 '이'
create proc saname_pro3
@a varchar(10)
as select * from sawon where saname like @a+'%' order by saname
saname_pro3 '이'
--부서번호가 10부터 20까지.....
drop proc buseono_pro
create proc buseono_pro
@a int, @b int
as select * from sawon where buseono between @a and @b order by buseono
buseono_pro @a=10, @b=20
예6) 직책을 갖고 해당직책을 갖는 사람의 정보를 찾는 Stored procedure(예3의 Update)
create proc sajob_pro2
@a varchar(10)
as select * from sawon where sajob=@a
실행 sajob_pro2 '과장'
실행 sajob_pro2 '부장'
예7) 부서와 직책을 갖고 사람의 정보를 찾는 stored procedure
create proc buseonosajob_pro
@a1 int=null,
@a2 varchar(10)=null
as
if @a1 is null
begin
print 'Please Enter buseono'
return
end
select *from sawon where buseono=@a1 and sajob=@a2
실행 exec buseonosajob_pro
exec buseonosajob_pro @a1=10, @a2='주임'
exec buseonosajob_pro @a1=30, @a2='과장'
drop proc buseonosajob_pro2
create proc buseonosajob_pro2
@a1 int=null,
@a2 varchar(10)=null
as
if @a1 <> isnumeric(@a1)
begin
print 'Please Enter buseono'
return
end
select *from sawon where buseono=@a1 and sajob=@a2
실행 exec buseonosajob_pro2
exec buseonosajob_pro2 @a1='aa', @a2='주임'
exec buseonosajob_pro2 @a1=30, @a2='과장'
예8) 부서이름과 직책을 갖고 사람의 정보를 찾는 stored procedure
buseoname buseotel saname sajob sapay
---------- --------------- ----------------- ----- ----------------------
총무부 123-1234 신성우 주임 90000
총무부 123-1234 이미자 주임 180000
create proc buseonamesajob_pro
@a1 varchar(10)=null,
@a2 varchar(10)=null
as
if @a1 is null
begin
print 'Please Enter buseoname'
return
end
select buseoname, buseotel, saname, sajob, sapay from buseo, sawon
where buseo.buseono=sawon.buseono and buseoname=@a1 and sajob=@a2
실행 exec buseonamesajob_pro --안된다
exec buseonamesajob_pro @a1='총무부', @a2='주임'
exec buseonamesajob_pro '총무부', '주임'
exec buseonamesajob_pro '전산부', '과장'
create proc buseonamesajob_pro2 --결과가 다르므로 주의 한다
@a1 int,
@a2 varchar(10)
as
select buseo.buseono, buseotel, saname, sajob, sapay from buseo, sawon
where buseo.buseono=sawon.buseono and buseo.buseono=@a1 and sajob=@a2
실행 exec buseonamesajob_pro2 10, '주임'
예9) Procedure 수정 (5번 saname_pro의 수정) -- EM에서 수정
alter proc saname_pro
@a varchar(10)
as select sabun, saname, sapay from sawon where saname=@a
sp_helptext saname_pro
실행 exec saname_pro '이선희'
예10) Parameter를 이용한 Insert Stored Procedure 만들기
☞ 오라클에서 많이 사용
create proc sawon_insert
@irumvarchar(10),
@noint,
@jobvarchar(4),
@paynumeric(10,0),
@inpsadatetime
as insert into sawon values (@irum, @no, @job, @pay, @inpsa)
실행exec sawon_insert '김소연', 10, '주임', 10000, '11-18-1999'
exec sawon_insert '한고은', 20, '과장', 20000, '12-25-1999'
select * from sawon
★ 오라클과 같이 Insert,Update, Delete Procedure, "연봉 계산"과 같은 PL_SQL 을 작성하여 본다
예11) 각종 Sp_ 의 명령은 Master에서 볼수 있다
예12) 자동실행 stored procedure (일종의 자동배치파일, 제어판\서비스)
1) 어떤것들이 자동으로 실행중인지 확인할 때 (단 Master만)
use master
sp_helpstartup
sp_helpstartup sp_addlogin
sp_helptext sp_addlogin
use mydb
sp_helpstartup
sp_helpstartupdt_adduserobject
sp_helptext dt_adduserobject
2) SQL Server가 실행될때 자동으로 실행되는 stored procedure (단 Master만)
sp_makestartup procedure_name
3) 해제시
sp_unmakestartup procedure_name
-- SQL Server의 시작시간을 보는 SP를 만들어 보자...master에서 실행하여야 한다
USE master
GO
CREATE TABLE dbo.ServiceStart(starttime DATETIME DEFAULT GETDATE())
GO
CREATE PROC dbo.ServiceStart_pro AS INSERT ServiceStart DEFAULT VALUES
GO
--자동 실행 스토어드 프로시저 등록하기
EXEC sp_procoption Servicestart_pro, 'startup', 'true'
--이 외 각종 정보는 profiler를 이용하는 것이 좋다
--mydb의 데이터를 깨끗이...master에서 실행하여야 한다
CREATE PROC dbo.sqldata_pro as
go
use mydb
go
sqldata
go
use master
go
--자동 실행 스토어드 프로시저 등록하기
EXEC sp_procoption sqldata_pro, 'startup', 'true'
★ 자동실행 procedure에 문제가 있을때
EM의 configure SQL server에서 ->paremeter -> -T4022입력
command prompt에서 c:\mssql\binn\sqlserver.exe\t4022
★ 색인(Index) : 전화번호부를 연상
검색키, primary key 는 자동색인키를 갖는다.
clustered 된 인덱스, asc만 허용 (디스크도구\디스크 조각모음)
Index는 Table의 값을 빠르게 Access하도록 하는 DataBase 객체이다.
Server는 Index를 자동으로 사용하고 유지보수 한다.
(1) Index 를 어떻게 만드는가?
1) Unique Index
Server는 Primary Key나 Unique제약 조건을 갖는 Column을 정의할 때
Unique Index를 자동으로 생성한다.
2) Non_Unique Index
(2) Index 의 유형
1) Unique IndexColumn이 고유한 값을 갖도록 함(Primary의 의미)
2) Non_Unique IndexQuery의 속도 증가
(3) 형식)
create [unique][clustered|nonclustered] index 인덱스명 on table명(필드,- - )
clustered 는 1개의 테이블에 1개만 작성할 수 있다 (번호별로, 부서별로)
clusetered index 를 먼저 만든 후에 nonclusetered index를 만들어야 한다.
(4) 모든 COLUMN에 INDEX를 만들지 않는 이유
1)색인을 만드는데 시간이 오래 걸린다.
2)TABLE공간외에 디스크 공간이 필요
3)DATA의 UPDATE, DELETE, INSERT 시 변화를 반영하기 위해서 INDEX는 동적으로 관리 된다.
(5) Index 생성시 주의 사항
1) Index 를 만들때
Where절이나 조인 조건에서 Column을 자주 이용할때
Column이 넓은 범위의 값을 가질때
많은 Null값을 갖는 Column일 때
Table이 크고 대부분의 Query가 행의 10~15% 이하를 검색한다고 예상될 때
2) Index 를 만들지 않아야 할 때
Table이 작을때
Column이 Query의 조건으로 사용되는 경우가 별로 없을때
Table이 자주 변경될때
(6) Index 삭제
Index의 소유자이거나 Drop Index권한을 가지고 있어야 한다
drop index 인덱스이름
(7) 유의할 점(알아둘 점)
1) Data는 8KB의 Data Pages로 저장된다.
2) Index는 항상 Asc정렬만 있다(Desc가 없다)
3) Data에 접근 방법
where조건 등이 걸리면 물리적인 Table이 아닌 Index (Key)에서 찾는다.
4) View에는 Index 생성 안된다 (가상테이블이므로...)
5) Table Owner 만 만들수 있다.
예1> 월급으로 인덱스 하시오(sapay_idx)
primary key 는 있으므로 nonclustered로 작성한다
create clustered index sapay_idx on sawon(sapay) --안된다
-- Cannot create more than one clustered index on table 'sawon'. Drop the existing clustered index 'sawon_sabun_pk' before creating another.
create nonclustered index sapay_idx on sawon(sapay)
select * from sawon order by sapay-- 정렬의 의미(출력결과는 동일)
select * from sawon (index=sapay_idx)-- 검색속도의 의미(출력결과는 동일)
select * from sawon where sajob='과장' order by sapay-- 정렬의 의미(출력결과는 동일)
select * from sawon (index=sapay_idx)where sajob='과장'-- 검색속도의 의미(출력결과는 동일)
drop index sawon.sapay_idx
예2> 입사일로 인덱스 하시오(sahire_idx) -- EM에서도 작성하여 본다
create nonclustered index sahire_idx on sawon(sahire)
select * from sawon order by sahire
select * from sawon(index=sahire_idx)
select * from sawon where sajob='과장' order by sahire
select * from sawon(index=sahire_idx) where sajob='과장'
drop index sawon.sahire_idx
★ TRIGGER : 연쇄반응으로써, 무결성의 원칙을 유지하는데 목적.
형식1)create trigger 트리거이름 on table명 for { insert, delete, update} as 트리거의 수행내용(sql문장)
-- insert, delete, update 에 대한 트리거만 생성할 수 있다.
형식2)if update문과 같이 사용
create trigger 트리거이름 on table명 for { insert, delete, update} as
if update (column_name) [{and | or} update (column_name)...] 트리거의 수행내용(sql문장)
예1) buseo테이블에서 자료를 삽입하면, buseo의 모든 내용을 출력하라
create trigger buseo_insert_trg on buseo for insert as select * from buseo
insert into buseo values('총무부','인천','123-1234')
sp_helptext buseo_insert_trg
sp_depends buseo_insert_trg
예2) sawon테이블에서 자료를 삽입하면, sawon의 모든 내용을 출력하라
create trigger sawon_insert_trg on sawon for insert as select * from sawon
insert into sawon values('배은주', 20, '대리', 240000, '6-17-1999')
-- EM에서도 작성하여 본다
예3) sawon테이블에서 자료를 갱신하면, gogek의 모든 내용을 출력하라
create trigger sawon_update_trg on sawon for update as select * from gogek
update sawon set sapay=sapay*1.3
-- sawon 및 buseo 의 출력, 두가지를 모두 수행한다 (단 트리거의 이름은 달라야 한다)
예4) sawon테이블에서 자료를 삽입하면, buseo의 모든 내용을 출력하라
create trigger sawon_insert_trg2 on sawon for insert as select * from buseo
insert into sawon values('배은영', 30, '대리', 240000, '8-21-1999')
-- 트리거를 맺은 상태에서는 무결성을 먼저 검사하고서 트리거를 실행한다..
-- 무결성에 위배가 되면 trigger는 작동하지 않는다.
예5) sawon테이블에서 자료를 지우면, sawon의 모든 내용을 출력하라
create trigger sawon_delete_trg on sawon for delete as select * from sawon
delete from sawon where sajob='과장' --Trigger가 수행 된다
delete from sawon where sajob='부장' --Trigger가 수행 안된다
예6) 재고관리 Trigger 프로그램
drop table chulgo-- Table을 삭제하면 트리거와 인덱스는 제거된다
drop table ipgo
drop table jaego
create table jaego (id int, sangpung varchar(10), su int, constraint jaego_id_pk primary key(id))
create table ipgo (id int, su int, constraint ipgo_id_fkforeign key(id) references jaego(id))
create table chulgo (id int, su int, constraint chulgo_id_fkforeign key(id) references jaego(id))
insert into jaego values(1001, 'TV', 10)
insert into jaego values(1002, 'VTR', 5)
insert into jaego values(1003, 'RADIO', 3)
select * from jaego
select * from ipgo
select * from chulgo
1) jaego table의 id가 update되지 않게 한다.
--Update가 되서는 안되는 컬럼이다 종속키로 연결된 컬럼이므로...
update jaego set id=1111 where id=1001 --Update가 된다
update jaego set id=1001 where id=1111 --Update가 된다
select * from jaego
create trigger jaego_update on jaego for update --재고에 update가 이루어지면...
as if update (id)
begin
print "갱신이 되서는 안되는 컬럼입니다"
rollback transaction
end
select * from jaego
update jaego set id=1111 where id=1001 --Update가 안된다
select * from jaego
-- Insert Trigger --
2) ipgo table에 insert시 jaego table에도 그 수량을 더해준다. -- (jaego=ipgo+jaego)
create trigger ipgo_insert on ipgo for insert --입고에 insert가 이루어지면…
as update jaego set jaego.su=jaego.su+inserted.su --Insert시 무결성을 검사하기 위한 가상테이블
from jaego, inserted
where jaego.id=inserted.id
insert into ipgo values(1002,30)
select * from ipgo-- 30
select * from jaego -- 35 (ipgo+jaego)
3) chulgo table에 insert시 jaego table에서 그 수량을 빼준다.
create trigger chulgo_insert on chulgo for insert-- 출고에 insert가 이루어지면...
as update jaego set jaego.su=jaego.su-inserted.su --Insert시 무결성을 검사하기 위한 가상테이블
from jaego, inserted
where jaego.id=inserted.id
insert into chulgo values(1002,30)select * from chulgo-- 30
select * from jaego-- 5
-- Delete Trigger --
4) ipgo table에 delete시 jaego table에도 그 수량을 빼준다. --원래는 에러
create trigger ipgo_delete on ipgo for delete -- 입고에 delete가 이루어지면…
as update jaego set jaego.su=jaego.su-deleted.su --Delete시 무결성을 검사하기 위한 가상테이블
from jaego, deleted
where jaego.id=deleted.id
delete from ipgo where id=1002
select * from ipgo-- Null
select * from jaego -- (-25)
5) chulgo table에 delete시 jaego table에도 그 수량을 더해준다.
create trigger chulgo_delete on chulgo for delete -- 출고에 delete가 이루어지면…
as update jaego set jaego.su=jaego.su+deleted.su --Delete시 무결성을 검사하기 위한 가상테이블
from jaego, deleted
where jaego.id=deleted.id
delete from chulgo where id=1002
select * from chulgo-- Null
select * from jaego -- 5
-- Update Trigger --
6) ipgo table의 su에 update가 발생하면 jaego table의 su도 변경
create trigger ipgo_update on ipgo for update
as if update(su)
begin
update jaego set jaego.su=jaego.su-deleted.su
from jaego, deleted
where jaego.id=deleted.id
update jaego set jaego.su=jaego.su+inserted.su
from jaego, inserted
where jaego.id=inserted.id
end
insert into ipgo values(1002,10)
select * from ipgo --10
select * from jaego--15
update ipgo set su=40 where id=1002-- 30의 차이
select * from ipgo -- 40
select * from jaego -- 45
7) chulgo table의 su에 update가 발생하면 jaego table의 su도 변경
create trigger chulgo_updated on chulgo for update
as if update(su)
begin
update jaego set jaego.su=jaego.su+deleted.su
from jaego, deleted
where jaego.id=deleted.id
update jaego set jaego.su=jaego.su-inserted.su
from jaego, inserted
where jaego.id=inserted.id
end
insert into chulgo values(1002,5)
select * from chulgo -- 5
select * from jaego -- 40
update chulgo set su=40 where id=1002-- 30의 차이
select * from chulgo -- 40
select * from jaego -- 5
--한table에서 어떤 값이 입력되었을 때 자동적으로
-- 다른 table에 data를 변경해야 할 때 사용 (데이터 무결성의 원칙)
-- 도서 대출에 대한 ........
IF BOOK_LOAN(책 대출 TABLE)에 DATA INSERT시 같은 책의 ID를 가진 책을
BOOK_LOAN TABLE(책정보)에서 LOAN COLUMN을 'Y'(대출중)로 표시하라.
1)Table 만들기
DROP TABLE BOOK_LOAN
DROP TABLE BOOK_INFO
DROP TABLE CUSTOMNAME
CREATE TABLE BOOK_INFO
(ID INT CONSTRAINT BOOK_INFO_ID_PK PRIMARY KEY
,BOOKNAME VARCHAR(20)
,AUTHORS VARCHAR(20)
,LOAN CHAR(8))
GO
CREATE TABLE CUSTOMNAME
(ID INT CONSTRAINT CUSTOMER_ID_PK PRIMARY KEY
,NAME VARCHAR(15)
,LOANBOOK INT)
GO
CREATE TABLE BOOK_LOAN
(ID INT
,CUSTOMERNAME INT
,ENDDAY DATETIME
,CONSTRAINT BOOK_LOAN_ID_FK FOREIGN KEY(ID) REFERENCES BOOK_INFO(ID)
,CONSTRAINT BOOK_LOAN_CUSTOMERNAME_FK FOREIGN KEY(CUSTOMERNAME)
REFERENCES CUSTOMNAME(ID))
GO
INSERT INTO BOOK_INFO VALUES(1, 'MS-SQL','윤태운','대출가능')
INSERT INTO BOOK_INFO VALUES(2, 'ORACLE','윤희경','대출가능')
INSERT INTO BOOK_INFO VALUES(3, 'VB','배은주','대출가능')
INSERT INTO BOOK_INFO VALUES(4, 'PB', '박태수','대출가능')
INSERT INTO BOOK_INFO VALUES(5, 'Delphi','최대치','대출가능')
GO
INSERT INTO CUSTOMNAME VALUES(1,'홍길동',0)
INSERT INTO CUSTOMNAME VALUES(2,'임꺽정',0)
INSERT INTO CUSTOMNAME VALUES(3,'전우치',0)
INSERT INTO CUSTOMNAME VALUES(4,'꼼뿌타',0)
SELECT *FROM BOOK_INFO
SELECT *FROM CUSTOMNAME
SELECT * FROM BOOK_LOAN
GO
-- INSERT TRIGGER ▶ Inserted table 이용
-- BOOK_LOAN에 data가 insert되면 BOOK_INFO의 LOAN column을 'Y'로 updata해라
sp_addmessage 50001,10,'***** 지금 신청하신 책은 대출중입니다 *****'
sp_addmessage 50002,10,'***** 3권이상의 책을 대출할 수 없습니다 *****'
sp_dropmessage 50001
create trigger LOAN on BOOK_LOAN for insert as
IF EXISTS(SELECT * FROM BOOK_INFO,INSERTED
WHERE INSERTED.ID=BOOK_INFO.ID AND LOAN='대여중')
BEGIN--신청한 책이 대출중이면...
RAISERROR (50001,10,1)
ROLLBACK TRAN
RETURN
END
IF (SELECT SUM(LOANBOOK) FROM CUSTOMNAME,INSERTED
WHERE CUSTOMNAME.ID=INSERTED.CUSTOMERNAME) >2
BEGIN--이미 3권의 책을 대출 했으면...
RAISERROR (50002,10,1)
ROLLBACK TRAN
RETURN
END
update CUSTOMNAME
set LOANBOOK=(SELECT SUM(LOANBOOK)+1 FROM CUSTOMNAME,INSERTED
WHERE CUSTOMNAME.ID=INSERTED.CUSTOMERNAME)
from CUSTOMNAME,INSERTED
where CUSTOMNAME.id=inserted.CUSTOMERNAME
update BOOK_INFO
set LOAN='대여중'
from BOOK_INFO, inserted
where BOOK_INFO.id=inserted.id
GO
INSERT INTO BOOK_LOAN VALUES(2,1,'12/03/99')
select * from BOOK_LOAN
select * from BOOK_INFO
SELECT * FROM CUSTOMNAME
INSERT INTO BOOK_LOAN VALUES(2,3,'12/03/99') -- ERROR(대여중)
INSERT INTO BOOK_LOAN VALUES(4,1,'12/03/99')
INSERT INTO BOOK_LOAN VALUES(5,1,'12/03/99')
INSERT INTO BOOK_LOAN VALUES(3,1,'12/03/99') -- ERROR(3권이상 대여)
select * from BOOK_LOAN
select * from BOOK_INFO
SELECT * FROM CUSTOMNAME
--트리거를 맺은 상태에서는 무결성을 먼저 검사하고서 트리거를 실행한다..
--무결성에 위배가되면 trigger는 작동하지 않는다.
INSERT INTO BOOK_LOAN VALUES(6,2,'12/03/99')
-- Delete trigger
-- BOOK_LOAN에 data가 delete되면 BOOK_INFO의 LOAN column을 '대출가능'로 updata해라
-- DELETED TRIGGER작성 --> deleted table 이용
sp_addmessage 50003,10,'***** 책의 대여 기간이 지났습니다. 기간을 확인 하시고 벌금을 받으세요 *****'
create trigger RE_BOOK on BOOK_LOAN for delete as
IF EXISTS(SELECT * FROM BOOK_INFO,DELETED
WHERE DELETED.ID=BOOK_INFO.ID AND DELETED.ENDDAY<GETDATE())
BEGIN
RAISERROR (50003,10,1)
END
update BOOK_INFO
set LOAN='대출가능'
from BOOK_INFO, deleted
where BOOK_INFO.id=deleted.id
-->CUSTOMNAME TABLE에서 대출 갯수를 하나 줄인다.
update CUSTOMNAME
set LOANBOOK=(SELECT SUM(BOOK_LOAN.CUSTOMERNAME)FROM BOOK_LOAN,DELETED
WHERE BOOK_LOAN.CUSTOMERNAME=DELETED.CUSTOMERNAME)
from CUSTOMNAME,DELETED
where CUSTOMNAME.id=DELETED.CUSTOMERNAME
GO
DELETE FROM BOOK_LOAN WHERE ID=2
-->delete trigger 작동
select * from BOOK_LOAN
select * from BOOK_INFO
select * from CUSTOMNAME
DELETE FROM BOOK_LOAN WHERE ID=5
select * from BOOK_LOAN
select * from BOOK_INFO
select * from CUSTOMNAME
GO
INSERT INTO BOOK_LOAN VALUES(2,1,'11/03/99')
GO
-->insert trigger 작동
DELETE FROM BOOK_LOAN WHERE ID=2
*** delete trigger2
-- 책을 대여중인 CUSTOMER의 DATA를 COSTOMNAME에서 삭제못하게끔 한다.(대출과 회원 정보)
-- 트리거를 맺은 상태에서는 무결성을 먼저 검사하고서 트리거를 실행한다..
-- 무결성에 위배가되면 trigger는 작동하지 않는다.
sp_addmessage 50004,10,
'***** 고객이 지금 하나 이상의 책을 대여중 입니다. 고객 정보를 삭제 할 수 없습니다 *****'
-->제약조건을 해제 후
ALTER TABLE BOOK_LOAN NOCHECK CONSTRAINT BOOK_LOAN_CUSTOMERNAME_FK
GO
create trigger CUSTOMDELETE on CUSTOMNAME for delete as
if(select count(*) from BOOK_LOAN,DELETED where BOOK_LOAN.CUSTOMERNAME=DELETED.ID)> 0
BEGIN
RAISERROR (50004,10,1)
rollback tran
RETURN
end
ELSE
BEGIN
PRINT '***** 고객의 정보를 삭제하였습니다 *****'
COMMIT
END
GO
select * from CUSTOMNAME
select * from BOOK_LOAN
SELECT * FROM BOOK_INFO
GO
delete from CUSTOMNAME where id=1 --ERROR
GO
delete from CUSTOMNAME where id=4
select * from CUSTOMNAME
select * from BOOK_LOAN
GO
ALTER TABLE BOOK_LOAN CHECK CONSTRAINT BOOK_LOAN_CUSTOMERNAME_FK
-- update trigger(if update문과 함께 사용)
-- BOOK_INFO의 id column이 update가 되는 것을 방지
select * from BOOK_INFO
select * from BOOK_LOAN
create trigger UPDATETRI on BOOK_INFO for update as
if update (id)
begin
print 'no update data column'
rollback transaction
end
GO
select * from BOOK_INFO
GO
update BOOK_INFO set id=7 where id=3
GO
★ Cursor
-- DB는 한번에 한 행씩 처리
-- TRIGGER나 STORED PROCEDURE에서 사용가능
-- 변수는 하나의 Record만 저장되는 반면에 Cursor는 모든 Record가 저장 가능
-- 나중에 Access하기 위한 특정문자를 수용한 Memory영역을 사용자가 명명하는것
-- 커서의 처리
커서의 처리 방법에 따라 어떤 DATA가 TEMPDB에 저장되는지가 결정된다.
ORDER BY, GROUP BY, UNION, DISTINCT,
HAVING이 있거나 INSENSITIVE가 지정되었으면 커서는 읽기 전용이다.
-- 커서의 반영(커서 OPEN후, 네트워크에서 데이터가 수정되었을 때 어떻게 하는가?)
1) DYNAMIC CURSOR
현재의 키 값만 TEMPDB에 저장하고, 다른 행으로 옮겨가면 TEMPDB는 기반 TABLE로부터 갱신된다.
2) KEYSET CURSOR
커서 결과 SET로부터 모든 키 값을 TEMPDB에 저장하고, 한 행이 FETCH되면 그 행 전체를 가져온다.
기반 TABLE의 모든 변경을 반영한다.
3) INSENSITIVE OR STATIC CURSOR (기본값)
전체 CURSOR 결과 SET를 TEMPDB에 저장한다.
INSENSITIVE CURSOR는 한번만 DATA를 가져오므로
기반 TABLE의 변경을 반영할 수 없다.
-- Cursor 작업 순서
1. 커서를 만들고 정의하기 위한 Declare-- Declare
2. 선언된 커서를 Open-- Open
3. 선언된 정보를 가져오고(Fetch) 그것으로 작업을 수행-- Fetch
4. 커서를 Close-- Close
5. 커서 정의 제거-- Deallocate
1. DECLARE문
-- 커서의 속성을 생성하고 정의한다.(INSENSITIVE와 SCROLL 두 종류의 커서)
DECLARE CURSOR_NAME [INSENSITIVE][SCROLL] CURSOR
FOR SELECT문
[FOR {READ ONLY | UPDATE [OF COLUMN_LIST]}]
1) INSENSITIVE (기본값)
다음행 작업만 할 수 있고, 커서에서 정의된 DATA들을 TEMPDB로 복사되고
원본이 바뀌어도 바뀐 내용은 반영되지 않는다.
2) SCROLL
행을 앞뒤로 마음대로 이동 할수는 있지만 지정 하지 않으면 다음 행만 가져온다.
2. OPEN문
-- 커서를 열고, 필요시 임시 TABLE을 만든다.
-- 구문은 OPEN CURSOR_NAME
3. FETCH문
-- FETCH문은 커서로부터 행을 가져오거나 검색한다.
-- 커서 선언시 SCROLL OPTION이 지정되지 않았다면 NEXT ROW만 가져온다.
-- 구문은 FETCH [[NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n] FROM] CURSOR_NAME
--[INTO @VARIABLE_NAME1,@VARIABLE_NAME2,...]
-- 전역변수 @@FETCH_STATUS는 FETCH 할 때마다 갱신된다.
@@FETCH_STATUS
0 : 성공적으로 가져왔다.
-1 : 가져오는데 실패
-2 : 그 행이 전에는 있었지만 더 이상 커서 DATA SET에 존재 않함
4. CLOSE문
-- 커서를 닫고 버퍼용으로 사용중이던 MEMORY를 풀어준다.
-- 커서가 닫힌 후에는, FETCH되거나 갱신되거나 삭제될 수 없다.
-- 구문은 CLOSE CURSOR_NAME
5. DEALLOCATE문
-- 커서의 정의를 제거
-- 커서를 다시 열려면 새로운 DECLARE문이 필요
-- 구문은 DEALLOCATE CURSOR_NAME
예1) sawon table로 부터 각 사원의 이름과 급여를 구하라.
-- create proc saname_sapay_cur as
DECLARE @A VARCHAR(10), @B MONEY
DECLARE saname_sapay_cur CURSOR --읽기전용이고, 다음행을 가져온다
FOR select saname, sapay from sawon
OPEN saname_sapay_cur
FETCH NEXT FROM saname_sapay_cur INTO @A, @B
WHILE (@@FETCH_STATUS<>-1)-- DATA를 가져오는데 실패하지 않으면...
BEGIN --조건문의 블록화
SELECT '당신의 이름은:', @A, '급여는:', @B
FETCH NEXT FROM saname_sapay_cur INTO @A, @B --만약 생략을 하면...
END
CLOSE saname_sapay_cur
DEALLOCATE saname_sapay_cur
-------------- ---------- ------- ------------------------------------
당신의 이름은 : 엄정화 급여는 :10000.0000
--Record의 형식은...
SELECT '당신의 이름은 : '+ saname, '급여는 : ' + str(sapay) from sawon
예2) sawon table로 부터 각 사원의 이름만을 구하라. -- A는 saname, B는 정화
-- create proc saname_cur_pro as
DECLARE @A VARCHAR(10), @B VARCHAR(10)
DECLARE saname_cur CURSOR
FOR select saname from sawon
OPEN saname_cur
FETCH NEXT FROM saname_cur INTO @A
WHILE (@@FETCH_STATUS<>-1)
BEGIN
SELECT @B=SUBSTRING(@A, 2, 2) --문자타입이므로 2, 2 이다
SELECT '현재의 이름은:', @B
FETCH NEXT FROM saname_cur INTO @A
END
CLOSE saname_cur
DEALLOCATE saname_cur
-------------- ------------------
현재의 이름은: 정화
--Record의 형식은...
SELECT '현재의 이름은 : ' +SUBSTRING(saname, 2, 2) from sawon
예3) sawon table로 부터 100,000원보다 봉급을 많이 받는
사원의 이름, 직책, 부서, 급여, 상여금( * 0.7)를 구하시오.
select * from buseo
select * from sawon
-- create proc many_cur_pro as
DECLARE @A VARCHAR(10), @B VARCHAR(4), @C VARCHAR(10), @D NUMERIC, @E NUMERIC
DECLARE many_cur CURSOR
FOR SELECT saname, sajob, buseoname, sapay from buseo, sawon
WHERE buseo.buseono=sawon.buseono AND sapay > 100000
OPEN many_cur
FETCH NEXT FROM many_cur INTO @A, @B, @C, @D --상여금은 계산을 하여야 한다
WHILE (@@FETCH_STATUS<>-1)
BEGIN
SELECT @E=@D*0.7
SELECT '사원의 이름은:',@A, '직책은:',@B, '부서는:',@C, '급여는:',@D, '상여금은:',@E
FETCH NEXT FROM many_cur INTO @A, @B, @C, @D
END
CLOSE many_cur
DEALLOCATE many_cur
------- -------------------- --------- -------------------- -------------------------------
사원의 이름은 : 영턱스 직책은 : 회장 부서는 : 전산부 급여는 :150000 상여금은 : 105000
--Record의 형식은...
SELECT '사원의 이름은 : ' + saname,
'직책은 : ' + sajob,
'부서는 : ' + buseoname,
'급여는 : ' + str(sapay)
FROM buseo, sawon
WHERE buseo.buseono=sawon.buseono AND sapay > 100000
/*
Access에서 MS-SQL과 ORACLE의 가져오기 및 테이블연결
가져오기, 테이블연결의 차이
MS-SQL은 Client Network Utility
ORACLE은 Oracle Net8 Easy Config
제어판의 ODBC의 설정
질의에서 각종 Select 및 ActionQuery의 작성
★ Backup & Restore
(1) Backup Database 장치 만들기
-- 먼저 6.5의 개념인 Device를 만든다, 탐색기에서 생성되지 않는다
형식> sp_addumpdevice 'device type', 'logical name', 'physical name'
예1>sp_addumpdevice 'Tape', 'backup_ex', 'd:\work\backup_ex.bak'
예2>sp_addumpdevice 'disk', 'backup_ex', 'd:\work\backup_ex.bak'
(2) Device 정보 확인시
형식> sp_helpdevice 'logical name'
예>sp_helpdevice 'backup_ex'
(3) Database 무결성 검사
예>dbcc checkdb ('exdb') with no_infomsgs
(4) Backup 하기 --탐색기에서 생성 된다
예1>backup database exdb to disk='d:\work\backup_ex.bak' with init
예2) backup database exdb to disk='d:\work\backup_ex.bak' with noinit --생략 가능
예3>backup database exdb to backup_ex with init -->BackUp장치 초기화 (Format의 개념)
예4>backup database exdb to backup_ex with noinit
(5) Backup, Restore 에 대한 정보를 볼때
예1>restore headeronly from backup_ex--백업장치의 모든 백업에 관한 정보를 확인
예2>restore filelistonly from backup_ex--특정 백업장치에 백업되어있는 로그와 데이타베이스의 목록 확인
예3>restore labelonly from backup_ex --백업장치의 미디어가 요약
예4>restore verifyonly from backup_ex --테이프와 디스크 모두가 복구에 사용가능한지 확인
(6) Restore 하기
예>restore database exdb from backup_ex
(7) Backup database 장치 삭제
형식>sp_dropdevice 'logical name', 'delfile'
예>sp_dropdevice 'backup_ex', 'delfile'
★ data backup시 master, msdb, user-database는 반드시 backup 해야 함
(binn\rebuildm.exe).
★ 시스템DB를 숨길수도 있다
★ 복제 서비스 마법사 (해당DB\ Tools\ Wizards)
★ SQL Performance Monitor
① Buffer Cache Hit Ratio
디스크로 부터 읽는 대신 데이터 캐시에서 요구가 발견되는 시간의 비율,
즉 특정한 요구에 응답하기 위해 SQL Server가 물리적 디스크로 부터 읽는
빈도를 결정하는 데 쓰인다. 캐시 히트 비율이 높으면 물리적 드라이브에
액세스 하지 않고 대부분의 요구가 캐시로 부터 응답이 된다
이값은 항상 높게 유지되어야 한다 이값이 떨어지면 메모리를 늘여야 한다
② User Connections
사용자 연결의 수
③ TotaL Server Memory
Total amount of dynamic memory the server is currently consuming
④ SQL Compilation/sec
Number of SQL compilations.
⑤ Page Read/sec
초당 읽는 물리적 페이지 수
⑥ Page Write/sec
로그와 캐시 분출에 의해 초당 수행되는 페이지 기록의 수
★ 전체 시스템 정보 (편집\차트에 추가)
① 시스템에 얼마나 많은 인터럽트가 발생하는지 체크 (⑤번참조)
Processor(개체) \ Interrupt/Sec(카운터)
② 서버가 얼마나 바쁜지
Server(개체) \ Bytes.Total/sec(카운터)
③ 서버가 메모리에서 디스크의 정보를 스왑하는 빈도수
Memory(개체) \ Pages/Sec(카운터)
5이하여야 하며, 초과를 하면 메모리를 추가하여야 한다
④ 빈공간이 적으면 LAN에 문제가 있다
LogicalDisk(개체) \ Free Megabytes(카운터)
⑤ 90, 4가 넘으면 CPU능력을 향상하여야 한다(교체) (①번참조)
Processor(개체) \ %Processor time(카운터)
Processor(개체) \ Interrupt sec(카운터)
★ Profiler (일종의 NT에서 관리도구\이벤트 표시기)
★ 간단한 한글 MS-DOS(명령 프롬프트)
(1) Alt + Enter
(2) chcp 437 chcp 949, hcode/e hcode/k
(3) 영어, 한글, 한문
(4) 복사 기법
(5) change directory (cd)
(6) Doskey 내장
● BCP(Bulk Copy Program) Dos 상에서 실행한다.
전제조건으로 DB의 Properties의 Option에서(6,5만)......
DTS는 적은 양의 데이터 (???)
Oracle의 SQL * Loader와 같다
(1) 데이타베이스의 자료를 Text파일로 Export하거나,
Text파일의 자료를 데이타베이스에 Import하기 위한 유틸리티이다.
(2) SQL 서버에서 다른 SQL서버로 많은 양의 데이타 복사
(3) 사용자가 정의하는 파일 형식의 자료를 복사
(4) Local 컴퓨터의 자료를 Local DB에 복사할 수 있고,
Local 컴퓨터의 자료를 Remote DB에 복사할 수도 있다.
(5) 테이블에 복사되는 내용은 현재의 테이블 내용에 Append 된다.
(6) TABLE이 이미 만들어져 있어야 한다.
(7) 파일에 복사되는 내용은 현재의 파일에 OverWrite 된다.
형식)
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors][-f formatfile][-e errfile]
[-F firstrow][-L lastrow][-b batchsize]
[-n native type][-c character type][-w wide character type]
[-N keep non-text native][-6 6x file format]
[-q quoted identifier][-C code page specifier]
[-t field terminator] [-r row terminator][-i inputfile]
[-o outfile] [-a packetsize][-S server name]
[-U username] [-P password][-T trusted connection]
[-v version] [-R regional enable][-k keep null values]
[-E keep identity values][-h "load hints"]
/U : 대문자 사용으로 login_id, /P : PASSWORD , /S : SERVER NAME,
/c : Ascii Code로 저장(문자열로 저장) /t",":필드를 ,로 구분을 하겠다.
예1) TXT로 내보내자 (Export)
bcp mydb..sawon out d:\work\sawon.txt /c /t"," /r\n /e d:\work\sawon.err /m100 /Usa /P /Sywh
예2) Import 하자
sawon.txt를 mydb의 sawon_bcp라는 Table로 보낸다(전제조건으로 Table의 구축)
select * into sawon_bcp from sawon --일종의 Copy
delete from sawon_bcp
select * from sawon_bcp
bcp mydb..sawon_bcp in d:\work\sawon.txt /c /t"," /r\n /e d:\work\sawon.err /m100 /Usa /P /Sywh
select * from sawon_bcp
예3) 예1,2를 DTS로 연습
★ VB에서 그래픽의 처리
drop table picvb
CREATE TABLE picvb (bunho int IDENTITY (1, 1),
irum char(6),
sajin image,
constraint picvb_bunho_pk primary key(bunho))
★ SQL에서 그래픽의 처리
drop table picsql
CREATE TABLE picsql (bunho int IDENTITY (1, 1),
irum char(6),
sajin image,
constraint picsql_bunho_pk primary key(bunho))
insert into picsql values('최진실') --Error
insert into picsql values('최진실', null)
insert into picsql values('강석우', null)
insert into picsql values('박상원', null)
select * from picsql
Mssql7\Binn> textcopy /?
TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
[/D [database]] [/T table] [/C column] [/W"where clause"]
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]
/S sqlserverThe SQL Server to connect to. If 'sqlserver' is not
specified, the local SQL Server is used.
/U loginThe login to connect with. If 'login' is not specified,
a trusted connection will be used.
/P passwordThe password for 'login'. If 'password' is not
specified, a NULL password will be used.
/D databaseThe database that contains the table with the text or
image data. If 'database' is not specified, the default
database of 'login' is used.
/T tableThe table that contains the text or image value.
/C column The text or image column of 'table'.
/W"where clause" A complete where clause (including the WHERE keyword)
that specifies a single row of 'table'.
/F fileThe file name.
/ICopy text or image value into SQL Server from 'file'.
/OCopy text or image value out of SQL Server into 'file'.
/K chunksizeSize of the data transfer buffer in bytes. Minimum
value is 1024 bytes, default value is 4096 bytes.
/ZDisplay debug information while running.
/?Display this usage information and exit.
textcopy /I /Usa /P /Sywhn /Dmydb /Tpicsql /Csajin /W"where='최진실'" /F최진실.bmp
★ Role
1) 의미
여러 사용자에게 여러 권한을 줄 경우
Role을 이용하지 않으면 일일히 다 주어야 하지만
이용하면 여러 권한을 Rolename에 준후, Rolename을 사용자에게 주면 된다.
Role은 사용자에게 허가할 수 있는 관련된 권한들의 Group이다.
이러한 Role을 이용하면 권한 부여와 회수를 쉽게 할 수 있다.
한 사용자가 여러 Role을 Access할 수 있고 다른 여러 사용자에게 같은 Role을
지정할 수 있다.
2) Role의 작성과 지정
DBA가 Role을 생성 해야 하고, 다음에 권한을 Role에 지정하고, 사용자에게 Role을 부여한다.
3) 형식) CREATE ROLE ROLENAME;
Role이 생성되면 DBA는 Role에 권한을 지정한 것과 마찬가지로 사용자에게
Role을 지정하기 위해 Grant 명령을 사용할 수 있다.
★ SQL SERVER Security
(1) 서버롤 설정하기 (Server Roles)
1) SysadminSQL서버의 모든 관리를 수행한다.
2) Securityadmin서버 로그인 계정을 관리한다.
3) Serveradmin서버 전반적인 세팅을 설정한다.
4) SetupadminLinked Server를 추가/삭제하고 복제작업을 설정한다.
5) ProcessadminSQL서버 프로세스를 관리한다.
6) Diskadmin디스크 파일을 관리한다.
7) Dbcreator데이터베이스를 만들고 변경한다.
(2) 고정된 데이터베이스 롤 (Database Access)
1) Db_Owner데이터베이스에서의 모든 권한을 가진다.
2) Db_Accessadmin사용자 계정을 추가, 삭제 등을 관리한다.
3) Db_Securityadmin롤을 관리하고 Statement권한, 오브젝트 권한을 관리한다.
4) Db_Ddladmin테이블, 인덱스 등 데이터베이스 오브젝트를 관리한다.
5) Db_Backupoperator데이터 베이스를 백업, 저장한다.
6) Db_Datareader모든 사용자 테이블의 데이터를 읽는다.
7) Db_Datawriter모든 사용자 테이블의 데이터를 수정, 변경, 삭제한다.
8) Db_Denydatareader모든 테이블의 데이터를 읽지 못한다.
9) Db_Denydatawriter모든 테이블의 데이터를 변경하지 못한다.
★ 혼합모드, 윈도우 NT모드
★ 분산쿼리(Distributed Query)
1. 개념
(1) 두대의 SQL 서버(급여서버와 회계서버)
(2) 두대의 서버가 SQL서버와 ORACLE서버(은행의 자동이체)
2. 분류
(1) OpenRowSet 함수를 이용하는 경우
쿼리에서 억세스하려고 하는 리모트 데이터소스의 연결방식을
그때그때 지정하여 사용할 수 있게 해 준다
즉 자주 이용(연결)하지 않을때, 또는 테스트용으로 사용
(2) Linked Server를 이용하는 경우
쿼리에서 자주 사용하는 데이터소스에 대하서 어떤 설정으로 어떻게
연결해서 사용할 것이라는 것을 미리 로컬 SQL서버에 등록해 놓은 것이다
즉 고정적으로 연결시 사용
3, 분산 쿼리 사용하기
(1) OpenRowSet 함수를 이용하는 경우
1)구문
OPENROWSET('provider_name'
{'datasource'; 'user_id'; 'password', 'provider_string' },
{[catalog.] [schema.] object 'query' })
'provider_name'provider의 고유 식별자이다
SQL은 SQLOLEDB
ORACLE은 MSDAORA
ODBC는 MSDASQL 이다
'datasource'SQL은 NetBios Name (즉 Client Network Utility)
ORACLE은 Oracle Net8 Easy Config
ODBC는 System DSN
'provider_string'SQL은 'driver={sql server}; server=sqlser; uid=sa; pwd=;',
ORACLE은 아래에서 설명
catalogcatalog, 즉 데이터베이스
schemaschema, 즉 소유주(Object Owner)
object테이블이름
'query'실행된 쿼리
2) 리모트 데이터 소스로 SQL 서버를 이용하는 경우
즉 로컬서버를 통해서 sqlser라는 서버컴퓨터에 설치된 SQL서버의
mydb의 dbo.sawon 을 데이터를 억세스 하는 경우이다
-- OLE DB 설정 사용하기
-- Client Network Utility를 이용하여 sqlser의 설정
select *
from openrowset('sqloledb',
'sqlser'; 'sa'; '',
'select * from mydb.dbo.sawon')
-- ODBC Provider 문자열 사용하기
select *
from openrowset('msdasql',
'driver={sql server}; server=sqlser; uid=sa; pwd=;',
'select * from mydb.dbo.sawon')
3) 리모트 데이터 소스로 ORACLE 서버를 이용하는 경우
-- Oracle에서@oracledata, select * from buseo;
ⓐ Oracle Net8 Easy Config를 이용하여 oraser라는 Alias의 설정
ⓑ System DSN의 Microsoft ODBC For Oracle를 sqlora라는 이름으로 설정
ⓒ ODBC를 이용
select * from openrowset(
'msdasql', 'sqlora'; 'scott'; 'tiger', 'select * from scott.buseo')
select * from openrowset(
'msdasql', 'DSN=sqlora; UID=scott; PWD=tiger', 'select * from scott.emp')
ⓓ OLE DB 설정 사용하기
select * from openrowset(
'msdaora', 'oraser'; 'scott'; 'tiger', 'select * from scott.buseo')
ⓔ ODBC Provider 문자열 사용하기 --error(?)
select * from openrowset(
'msdasql', 'driver={Microsoft ODBC For Oracle};
server=oraser; uid=scott; pwd=tiger;', 'select * from scott.buseo')
ⓕ 로컬 SQL 서버와, 리모트 오라클 DB 함께 사용하기 --error(?)
select * from mydb.dbo.sawon join openrowset('msdasql', 'sqlora';
'scott'; 'tiger', 'select * from scott.buseo') on sawon.buseono=buseo.buseono
(2) Linked Server를 이용하는 경우
1) 미리 정의 하는 기법이므로....
OLE DB 데이터 소스를 Linked Server로 등록
sp_addlinkedserver [@server =] 'server' [, [@srvproduct =] 'product_name']
[, [@provider =] 'provider_name'] [, [@datasrc =] 'data_source']
[, [@location =] 'location'] [, [@provstr =] 'provider_string']
[, [@catalog =] 'catalog']
@server로컬 SQL 서버에 등록될 Linked Server 이름
@srvproductOLE DB 데이터 소스의 제품이름
@providerprovider의 고유 식별자이다
SQL은 SQLOLEDB
ORACLE은 MSDAORA
ODBC는 MSDASQL 이다
@datasrcSQL은 NetBios Name
ORACLE은 Oracle Net8 Easy Config
ODBC는 System DSN
@locationOLE DB Provider에 의해 해석되어지는 데이터소스의 위치
@provstrSQL은 'driver={sql server}; server=sqlser; uid=sa; pwd=;',
@catalogcatalog, 즉 데이터베이스
-- 조금씩 기법이 다르므로 SP_addlinkedserver 를 이용한다
2) 리모트 SQL 서버 등록하기
exec sp_addlinkedserver 'sqlser', 'sql server'
3) 리모트 오라클 서버 등록하기
-- OLE DB 설정 사용하기
exec sp_addlinkedserver
@server='oraser',@srvproduct='oracle',
@provider='msdaora',@datasrc='oraser'
-- ODBC 설정 사용하기
exec sp_addlinkedserver
@server='oraser',@srvproduct='oracle',
@provider='msdasql',
@datasrc='sqlora'-- ODBC System DSN의 이름
4) Linked Server 보안 설정하기
①select * from openquery(oraser, 'select * from scott.buseo')
-- Linked Server를 등록하였다해서 로그온은 안된다(보안)
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'msdaora'.
[OLE/DB provider returned message: ORA-01017: invalid username/password; logon denied]
-- 그래서 보안 설정은 2가지 방법이 있다
ⓐ Impersonation
기본값으로 두 서버의 보안설정이 동일한 경우이다
ⓑ 로그인 계정 Mapping
sp_addlinkedsrvlogin [@rmtsrvname =] 'rmtsrvname'
[,[@useself =] 'useself']
[,[@locallogin =] 'locallogin']
[,[@rmtuser =] 'rmtuser']
[,[@rmtpassword =] 'rmtpassword']
@rmtsrvname보안설정을 할 Linked Server이름
@useselfImpersonation(True), 또는 Mapping의 결정
@locallogin로컬 SQL 서버에서 보안설정이 적용될 로그인 계정
@rmtuser와 @rmtpassword
②Linked Server 보안 설정하기
-- sa는 scott/tiger로 Mapping
exec sp_addlinkedsrvlogin 'oraser', 'false', sa, 'scott', 'tiger'
-- 그 외 모든 계정은 scott/tiger로 매핑
exec sp_addlinkedsrvlogin 'oraser', 'false', null, 'scott', 'tiger'
③Linked Server 정보보기
sp_linkedservers
sp_catalogssqlser
sp_catalogsoraser--Error(???)
sp_indexessqlser
sp_indexessqlser, 'buseo'
sp_indexesoraser
sp_indexesoraser, 'buseo'
sp_primarykeyssqlser
sp_primarykeyssqlser, 'buseo'
sp_primarykeysoraser
sp_primarykeysoraser, 'buseo'
sp_foreignkeyssqlser
sp_foreignkeyssqlser, 'buseo'
sp_foreignkeysoraser
sp_foreignkeysoraser, 'buseo'
sp_columnssqlser
sp_columnssqlser, 'buseo'
sp_columnsoraser
sp_columnsoraser, 'buseo'
sp_tables_ex sqlser, 'buseo'
sp_tables_ex oraser, 'buseo'
(3) 실질적인 Linked Server를 사용한 분산쿼리
-- Linked Server 등록하기
exec sp_addlinkedserver 'sqlser', 'sql server'
-- Linked Server 보안 설정
exec sp_addlinkedsrvlogin 'sqlser', 'true', sa /*전에 False로 설정 */
-- Linked Server 에서 테이블 읽어오기
select * from sqlser.mydb.dbo.sawon
-- Linked Server 데이터 수정하기(권한 부여)-- Error
update oraser..scott.buseo
set buseoname='총무부2' where buseoname='총무부'
-- OpenQuery함수를 이용
select * from openquery(sqlser, 'select * from mydb.dbo.sawon')
select * from openquery(oraser, 'select * from scott.buseo')
-- OpenQuery함수를 이용하여 Linked Server 데이터 수정하기(권한 부여)
update openquery (sqlser, 'select * from mydb.dbo.sawon')
set saname='정화' where saname='엄정화'
select * from sawon
sqldata
(4) 분산 트랜잭션(Distributed Transaction)
--분산 트랜잭션 처리하기--Error
set xact_abort on/*분산 트랜잭션 간의 데이터를 수정시 사용 */
begin distributed tran
update sqlser.mydb.dbo.sawon
set saname='엄정화' where saname='정화'
commit tran
--분산 트랜잭션에서 은행간 계좌이체 처리하기
set xact_abort on
begin distributed tran
update seoulbank.accountdb.dbo.account set ....... /* 출금처리 */
update chejubank.accountdb.dbo.account set ....... /* 입금처리 */
commit tran
(5) 분산 트랜잭션과 데이터 리플리케이션
분산 트랜잭션과 데이터 리플리케이션은 둘 다 데이터를 여러 서버에
중복해서 저장하는데 사용할 수 있는 방법이다.
여기서는 이런경우에 두 방법을 사용할 때 각각의 장단점에 대해서 알아보자.
예를 들어 제조업체의 업무를 분석해 보면...
기본적으로 각 공장에서는 제품별 생산량과 재고를 관리해야 하며,
물건을 파는 매장에서도 판매량과 재고를 관리해야 한다.
그리고 본사에서는 전체 공장과 매장의 데이터를 모두 종합적으로
관리할 수 있어야만 제품의 생산 및 유통과 관련된 의사 결정을 할 수 있다.
이 때 예전에는 주로 데이터 복제, 즉 리플리케이션의 개념을 많이 사용해 왔다.
데이터 리플리케이션은 일정 시간 간격으로 테이블에서 그 동안 변한 데이터를
한꺼번에 다른 서버나 테이블에 복사하여 적용하는 개념이다.
이것은 위의 예에서 적용하자면 매일 업무가 끝난 후 한가한 시간,
즉 저녁이나 새벽 시간에 각 공장이나 매장에서 그날의 생산이나
판매 데이터를 본사 서버에 복제하는, 즉 마감처리를 하게 된다.
이것은 본사 입장에서 봤을 때는 공장이나 매장에서 제품이
생산, 판매되는 시점의 데이터를 곧바로 알 수 없어 불편하지만
로컬 서버 입장에서는 매일 한참 처리해야할 데이터가 많은 작업 시간에
본사 서버에 대한 고려를 할 필요가 없으므로 OLTP처리가 빠르고 간편하다.
이에 비해 지금까지 설명한 분산 트랜잭션을 사용하는 방법은 실시간으로
즉 공장에서 제품이 생산되거나 매장에서 물건이 팔려 자체 로컬 서버에
등록될 때 동시에 본사 서버에도 데이터가 전송되어 처리되도록
하나의 트랜잭션으로 처리되도록 해주는 개념이다.
이것은 로컬 서버 입장에서 봤을 때는 OLTP작업에서 각각의 트랜잭션이
처리될 때마다 로컬 서버와 본사 서버의 데이터가 동시에 처리되어야 하므로
분산 트랜잭션 처리와 네트워크 트래픽 처리에 그만큼 부담이 가지만
본사 입장에서 봤을 때는 실시간으로 전체 제품의 생산, 판매 현황을 파악할 수
있으므로 경영자가 아주 신속하게 의사 결정을 내릴 수 있는 장점이 있다
참고로 분산 트랜잭션은 반드시 실시간으로 데이터가 처리되어야 하는
은행 업무 같은 곳에서 많이 사용하며
데이터 리플리케이션은 상대적으로 서버간에 시간차가 나도 괜찮은
데이터 웨어하우스 구축과 같은 OLAP관련 업무에서 많이 사용한다.
OLAP은행에서 최근 일년간 예금액의 증가치와, 은행금리의 관계를 비교분석
OLTP은행의 입출금처리
데이터 웨어하우스
DB의 설계, 구축, 관리
★ 웹서버의 구축
★ IIS의 설치 후, 점검
★ HTML, Scripts, IDC, ASP의 차이
★ VBScript
★ MS-SQL과 IDC를 이용한 Home Page 작성법 (1/2)
(1) MS-SQL Server에서 Table의 구축 (bangt)
bun(Primary key와 identity의 설정), irum, email, age, tel, memo
create table bangt(bun int identity(1,1),
irum varchar(10),
email varchar(20),
age int,
tel varchar(20),
memo varchar(255),
constraint bangt_bun_pk primary key(bun))
(2) 제어판의 ODBC를 이용한 시스템 DSN(bangdsn)의 설정
(3) Index.htm 을 작성한 후 WWW의 홈에 저장한다 (HTML, SCRIPT, IDC, ASP)
<html> <head> <title> 여기는 중앙 윤위현의 집입니다. </title></head>
<body>
안녕하세요<br>
현이의 집에 오신걸 환영합니다. <br>
<a href="/scripts/bangout.idc"> 방명록보기 </a><br> -- Alias명/bangout.idc
<a href="banginp.htm"> 방명록등록 </a>
</body> </html>
(4) banginp.htm를 작성 -- wwwroot, 방명록의 등록을 클릭하면...
<html> <head><title>방명록입력</title></head>
<body>
-- Method는 입력받은 정보를 전송하는 방식,
-- Action은 입력받은 정보를 어디로 전송할것인가를 기입
-- idc(Internet Database Connector)
<form method="POST" action="/scripts/banginp.idc">
이름 <input type="text" name="ir" size="10" maxlength=4> <p>
메일 <input type="text" name="em" size="20" maxlength=20> <p>
나이 <input type="text" name="ag" size="5" maxlength=2> <p>
전화 <input type="text" name="te" size="15" maxlength=15> <p>
내용 <textarea rows=7 cols=48 name="me" wrap=physical></textarea><p>
<input type="submit" value="등록">
<input type="reset" value="입력다시">
</form> </body> </html>
(5) banginp.idc의 작성(Internet Database Connector)
datasource: bangdsn
username: sa--password를 쓸 경우도...
template: banginp.htx --출력할 파일은...
SQLStatement:
+ Insert Into bangt
+ values('%ir%', '%em%', %ag%, '%te%', '%mo%') -- %는 idc에서 변수를 받아 들일 때 쓰는 형식(1/2)
(6) banginp.htx 작성
<html> <body> <%idc.ir%>님이 등록되었습니다...... </body> </html>
(7) ISQL에서 Select로 확인
(8) bangout.idc 작성 -- "방명록 보기"를 클릭한 경우
datasource: bangdsn
username: sa
template: bangout.htx
SQLstatement:
+ select *
+ from bangt
(9) bangout.htx
<html> <head><title> 방명록 목록 </title></head>
<body>
<%if irum EQ ""%> -- <% .......... %>는 서버에서 쓰는 Tag (2/2)
방명록이 비어 있습니다. <br>
<%else%>
<%begindetail%>
<%if currentrecord EQ 0%>
<pre>번호 이름 메일주소 나이 전화 메모</pre> <br>
<hr>
<%endif%>
<%bun%> <%irum%> <%email%> <%age%> <%tel%> <%memo%><p>
<%enddetail%>
<%endif%>
</body> </html>
★ MS-SQL과 Visual Basic(ASP)를 이용한 Home Page 작성법 (2/2)
ASP란?
1) 스크립트가 안되는 브라우저가 있다
2) 스크립트가 공개가 된다. 이런 등의 이유로 보완된 개념이 ASP이다
3) Scripts 폴더에 저장한다
(1) 7의 배수
(2)오늘 날짜의 출력
(3) 방명록테이블이름은 bbs (Scripts에 저장)
(4) 제어판에서 Setting(시스템DSN에서 mybbs)
(5) 데이터 목록을 보여주는 bbs.asp (Scripts에 저장)
(6) 방명록 쓰기인 write.asp (Scripts에 저장)
(7) 방명록 보기인 view.asp (Scripts에 저장)
★ Bbs.asp
★ Write.asp
★ View.asp
★ 인원수 만들기 (기존 CGI는 Refresh때도 올라간다???)
1. 방문자 총 수를 저장할 파일을 우선 만든다.
가) 메모장 등에서 숫자를 입력하고 visited.txt라고 저장한다(0을 기입).
나) wwwroot 밑에 \count라는 디렉토리를 만들고 여기에 저장한다.
2. 카운터를 표시하려면…
필요한 곳에 <%= Application("visited") %>라고 쓰기만 하면 된다 (단 asp로 저장).
(단 Text로, 이미지는 다음 페이지를 참조한다)
3. global.asa파일에 아래의 코드를 삽입한다 (wwwroot에 저장, Rebooting).
<Script language=VBscript runat=server>
Sub Application_OnStart
strFname=Server.MapPath("count")+"\visited.txt"‘//visited.txt가 있는 곳의 경로지정
‘// 절대경로를 의미한다
‘//strFname=”c:\Inetpub\wwwroot\count\visited.txt”
Set objFile=Server.CreateObject("Scripting.FileSystemObject")
Set FileOut=objFile.OpenTextFile(strFname,1,false,false)‘//읽기 전용
Application("visited")=FileOut.ReadLine‘//visited.txt파일에서 숫자를 읽어온다
FileOut.Close
Application("Fname")=strFname‘//경로를 APP변수에 저장한다.
end sub
4. 카운터 만들기 (이미지 카운터)
1) 0에서 9까지의 숫자에 해당하는 그림 파일을 0.gif, 1.gif …, 9.gif와 같이
만들어 count 디렉토리 밑에 저장한다.
2) 카운터를 표시할 때 텍스트 카운터의 경우는 <%=Application(“visited”)%>,
이미지 카운터의 경우는 아래의 코드를 삽입하게 된다.
<center>당신은 ???? / ?? / ?? 이후
<% for i=1 to Len(Application("visited"))
num=Mid(Application("visited"), i, 1)
Response.Write "<img src=\count\"&num&".gif border=0 valign=top>"
next %> 번째 방문객입니다.</center>
5. 참고로 현재 접속 건수는?
Index.asp에는 <%= Application ("now_visited") %>
'Data Base > MsSql 2000' 카테고리의 다른 글
| [MSSQL] 날짜 형식 변환 (0) | 2008.07.24 |
|---|---|
| ms sql db복구하기 (0) | 2008.06.12 |
| mdf 와 ldf 디비 복원 (0) | 2008.06.12 |
| MS-SQL 단축키 (0) | 2008.06.11 |
| MS-SQL 쿼리문 정리 (0) | 2007.10.15 |