// [2002년 6월 20일]
/*데이타 베이스로의 막연한 여행 (부제 : 무조건 따라가기)*/
sp_databases
/*데이타 베이스 옮기기*/
use 축구
/*테이블 목록 조회*/
select * from sysobjects where type='u'
/*테이블의 컬럼 정보 조회*/
sp_columns 경기별점수
/*테이블 조회*/
select * from 선수
/*Pubs DB의 저자 테이블의 컬럼정의와 내용을 조회해주십시오*/
use pubs
select * from sysobjects where type='u'
sp_columns authors
select * from authors
/*Pubs DB의 구조를 오직 상단의 질의어만으로 이해해보자*/
use pubs
select * from sysobjects where type='u'
/*조회연습 select문*/
/*기본 형식[간단 버젼]*/
/*select [column list] form [table name] where [condition pharse] order by [정렬조건]*/
use northwind
select * from sysobjects where type='u'
/*주문 테이블의 모든 컬럼을 모두 조회하십시오*/
select * from orders
/*특정한 컬럼 조회하기*/
select customerid,employeeid,orderdate from orders
select customerid as '고객번호',employeeid as '담당자번호',orderdate as '주문일자' from orders
/*5번 담당자의 실적을 뽑기*/
select * from orders where employeeid=5
/*6번 담당자의 주문 실적을 최근것이 위로 먼저 나오게 해서 고객 번호와 운송업체 번호를 조회하라*/
select customerid as '고객아이디',shipvia as '운송업체 번호',orderdate as '주문 날짜' from orders where employeeid=6 order by orderdate desc
/*사원 번호가 5번 이상인 사원의 실적을 사원번호 오름차순으로 조회해 주세요*/
select * from orders where employeeid >= 5 order by employeeid asc
/*사원 번호가 5번인 사람을 제외한 사원의 실적을 조회해 주세요*/
select * from orders where employeeid <> 5 order by employeeid asc
/*사원 테이블에서 입사 순으로 조회를 하세요*/
select * from sysobjects where type='u'
select firstname as '성',lastname as '이름',hiredate as '입사날짜' from Employees order by hiredate asc
// [2002년 6월 21일]
use northwind
select * from sysobjects where type='u'
select * from [order details]
/*산술 연산하는것*/
select orderid as '주문번호',productid as '상품번호',
(unitprice*quantity*(1-discount)) as '판매 금액' from [order details]
/*orderid가 같은것끼리 같이 계산해서 출력하시요~~~*/
select orderid as '주문번호',sum(unitprice*quantity*(1-discount))
from [order details] group by orderid
/*사원 테이블에서 같이 붙여서 나오게 하라*/
select * from employees
select ('['+title+']'+titleofcourtesy+lastname+','+firstname) as '사원 명세서' from employees
/*참고*/
select ('['+title+']'+titleofcourtesy+lastname+','+firstname) as '사원 명세서',
datediff(yyyy,birthdate,getdate()),
datediff(yyyy,hiredate,getdate())
from employees
/*논리 연산자*/
select * from customers
select * from customers where country='usa' and contacttitle='owner'
select * from customers where country='usa' or country='uk'
select * from orders where orderid between 10248 and 10250
select * from customers where country in('usa','uk')
select * from customers where not(country ='usa')
select * from customers where not(country !='usa')
select * from customers where country<>'usa'
select * from products where productname='coffee'--이거 커피란 상품이 있는 경우에서이다.
select * from products where productname like '%coffee%'--앞뒤로 coffee가 달어 있는 컬럼 뽑는다.
select * from orders where customerid like '%a____'
select * from customers where customerid like '[a-e]____' order by customerid
select * from orders where customerid like '[^a-e]____' order by customerid
/*주문이 한번이라두 이루어져 있는 상품을 뽑는당*/
select * from [order details]
select distinct orderid from [order details]
/*구성함수*/
select @@datefirst --매주 첫째 요일을 반환한다.
select @@dbts
select @@language
select @@langid
select coalesce(shipregion,shipcity) from orders
select * from orders order by customerid
/*substring 의 사용법*/
select substring(shipname,1,8) from orders
/*replace의 사용법*/
select replace(customerid,'fki','kif') from orders order by customerid
/*rtrim의 사용법*/
select rtrim( '[정옥] ' ), '[정옥] ' from orders
/*substring 사용법 substring(string,startpos,endpos-startpos+1)*/
// [2002년 6월 22일]
use northwind
use 축구
select * from sysobjects where type='u'
select * from 선수
select * from 경기별점수
/*insert 구문 연습하기*/
insert into 선수 (번호,이름,위치) values(4,'안정환','DF')
insert into 경기별점수 (번호,선수번호,대회명,점수) values(1,4,'이태리전',1)
insert 경기별점수 values(3,4,'스페인전',4)
/*bulk copy 테이블을 카피한다*/
select * into 선수다시 from 선수
select * from 선수다시
insert into 선수다시 values(4,'안정환','MF')/*bulk copy는 값과 table의 모양만 copy되고 제약 조건은 제외된다.*/
insert into 선수다시 select * from 선수/*이미 존재하는 테이블로 bulk copy*/
select * into 선수다시 from 선수 /*존재하지 않는 새로운 테이블로 bulk copy*/
/*update문 연습*/
select * from 선수
update 선수 set 이름='차두리'
select * from 선수
update 선수 set 이름='안정환' where 번호=3
select * from 선수
update 선수 set 이름='김정옥',위치='FW' where 번호=4
select * from 선수
insert 선수 values(5,'비에리','MF')
select * from 선수
/*delete from 연습하기*/
delete from 선수
select * from 선수
select * from 경기별점수
// [2002년 6월 24일]
select * from orders where order by customerid
select * from sysobjects where type='u'
select * from customers
select customerid,companyname from customers
select employeeid,reportsto from employees
select * from Employees
/*from 절뒤 inner join [taget table name] on [결합조건]*/
select companyname,orderdate from orders
inner join customers on orders.customerid=customers.customerid
/*위에 것과 같당*/
select companyname,orderdate from orders,customers
where orders.customerid=customers.customerid
/*orders 테이블을 조회하여 customerid를 구매회사이름으로
employeeid를 담당자 이름으로 바꾸고 orderdate와 shippeddate를 표시하고
shippeddate가 비어있으면 '보내지지 않음'으로 표시해 주십시요*/
/*참고 : convert함수와 coalesce함수를 사용*/
select companyname,'['+title+']'+lastname+firstname,
orderdate,coalesce(convert(varchar(20),shippeddate,102),'보내지 않음') from orders
inner join customers on orders.customerid=customers.customerid
inner join employees on orders.employeeid=employees.employeeid
use pubs
select * from sysobjects where type='u'
select * from sales
select * from stores
select * from titles
select sales.stor_id as '출판사 번호',stor_name as '출판사 이름',(zip+' [ '+state+' ] '+city+stor_address) as '출판사 주소',
notes as '책 제목', title as '책 헤드라인',type as '분류',price as '책 가격',royalty as '로얄티',ord_num as '주문 넘버',qty as '주문량',
payterms as '주문 형식',convert(varchar(20),ord_date,102) as '주문 날짜' from sales
inner join stores on sales.stor_id=stores.stor_id
inner join titles on sales.title_id=titles.title_id
select * from sales
inner join titles on titles.title_id=sales.title_id
inner join stores on stores.stor_id=sales.stor_id
/*여기서부터는 outer join 입니다.*/
use northwind
select productid from [order details]
delete [order details] where productid>70
select * from [order details]
right outer join products on [order details].productid=products.productid
order by products.productid
select * from products
select * from [order details]
select [order details].productid,sum([order details].unitprice*quantity*(1-discount)) as '합계' from products
left outer join [order details] on [order details].productid=products.productid
group by [order details].productid order by [order details].productid
// [2002년 6월 25일]
use northwind
select * from orders
select * from customers
select * from employees
select * from shippers
select customers.companyname,lastname,shippers.companyname from orders
inner join customers on orders.customerid=customers.customerid
inner join employees on orders.employeeid=employees.employeeid
inner join shippers on orders.shipvia=shippers.shipperid
/*상품별 총판매금액(단, 팔린 적이 없는 상품은 0으로 표기 하시요)*/
select * from orders
select * from customers
select * from products
select * from sysobjects where type='u'
select * from [order details]
select productname,coalesce(sum([order details].unitprice*quantity*(1-discount)),0) from products
left outer join [order details] on products.productid=[order details].productid
group by products.productname
/*cross join 구문*/
select * from categories
cross join products/*하나의 아이디에 77개의 행이 붙은걸 볼수 있다.*/
/*self join*/
/*피고용자 이름 | 감독관 이름*/
select * from employees
select lastname as '이름',reportsto as '감독관 아이디' from employees as emp1
select employeeid as '감독관 아이디',lastname as '감독관 이름' from employees as emp2
select emp1.lastname as '이름',emp2.lastname as '감독과 이름' from employees as emp1
inner join employees as emp2 on emp1.reportsto=emp2.employeeid
/*union 합집합 옆으로 조인하는게 아니라 위아래로 조인이 된다.*/
use 축구
select * from 경기별점수
select * into 경기별점수_1999 from 경기별점수
select * into 경기별점수_2000 from 경기별점수
select * into 경기별점수_2001 from 경기별점수
select * from 경기별점수
union
select * from 경기별점수_1999
union
select * from 경기별점수_2000
union
select * from 경기별점수_2001
/*합치고자하는 테이블의 데이타 타입이 비슷하거나 같지 않으면 안되고 한 레코드라두 8000바이트가
넘어가면 안되다.*/
select * from 경기별점수
union all
select * from 경기별점수_1999
union all
select * from 경기별점수_2000
union all
select * from 경기별점수_2001
/*sub Query*/
use northwind
select * from [order details] order by productid
select * from products where productid in (select productid from [order details])
select * from products where productid not in (select productid from [order details])
/*Relation Sub Query 상관 서브 쿼리*/
select * from products where exists (select * from [order details] where productid=products.productid)
/*set 문,Declare문*/
/*declare [변수][데이타 형식]*/
declare @i int
/*set[변수]=[값]*/
/*set @i='10'----->이것두 가능하다 숫자로 인식이 가능한 것은 얼마든지 가능 하지만 'A' 이것은 안된다.*/
set @i=10
set @i=@i*100
select @i
print @i
/*if~~~ else~~~*/
/*if[조건문]
begin
................
end
else
begin
..............
end
*/
declare @i int
set @i=10
if @i>10
begin
print '10보다 크다'
end
else
begin
print '10보다 작거나 같다'
end
if (select avg(unitprice) from products)>$30
begin
print'평균적으로 단가가 비쌈'
end
else
begin
print'평균적으로 단가가 싼 편임'
end
/*case 문*/
select productname,
case
when unitprice>30 then '비쌈'
when unitprice<=30 then '싼편'
end as '가격등듭'
from products
/*while[조건문]
begin
break
continue
end*/
/*1~~10까지 찍어보기*/
declare @i int
set @i=1
while @i<=10
begin
print @i
set @i=@i+1
end
/*goto 명령*/
goto test
select * from orders
test:
select * from [order details]
/*set rowcount*/
select * from orders
set rowcount 5
select * from orders
set rowcount 0
select * from orders
select top 10 * from orders
// [2002년 6월 27일]
use master
/*
create database [databasename]
on <-- 데이타 DB에 관한 정의
[
name = [database data file db name],
filename = [database data file path&name],
size = [data file init size],
maxsize = [data file maximum size],
filegrowth = [growth size & size ratio]
]
log on
[
name = [database data file db name],
filename = [database data file path&name],
size = [data file init size],
maxsize = [data file maximum size],
filegrowth = [growth size & size ratio]
]
......
...
.
*/
create database test
drop database test
/*
c:/temp/test_data01.mdf, test_data01, 10M, 100M, 5M
c:/temp/test_log_data01.ldf, test_log01, 5M, 50, 5%
*/
create database test
on
(
name=test_data01,
filename='c:\temp\test_data01.mdf',
size=10MB,
maxsize=100MB,
filegrowth=5MB
),
filegroup test_filegroup01
(
name=test_filegroup01_data01,
filename='c:\temp\test_filegroup01_data01.mdf',
size=5MB,
maxsize=50MB,
filegrowth=5MB
)
log on
(
name=test_log01,
filename='c:\temp\test_log_data01.ldf',
size=5MB,
maxsize=50MB,
filegrowth=5%
)
sp_helpdb test
drop database test
/*testDB 만들기 data file group을 3개로 각 filegroup에 file을 2개씩*/
create database test
on
(
name=test_date01,
filename='c:\temp\test_date01.mdf',
size=5MB,
maxsize=100MB,
filegrowth=5MB
),
filegroup test_group01
(
name=test_group01,
filename='c:\temp\test_group01.mdf',
size=5MB,
maxsize=100MB,
filegrowth=5MB
),
filegroup test_group02
(
name=test_group02,
filename='c:\temp\test_group02.mdf',
size=5MB,
maxsize=100MB,
filegrowth=5MB
)
log on
(
name=test_log01,
filename='c:\temp\test_log01.ldf',
size=5MB,
maxsize=100MB,
filegrowth=5%
)
sp_helpdb test
drop database test
/*
데이타 베이스 변경
alter database [databasename]
add file(filespec)
| add log file(filespec)
| remove file(filespec)
| add filegroup(filespec)
| remove filegroup(filespec)
| modify filegroup(filespec)
| modify file(filespec)
| modify name = new filename
| modify filegroup filegroupname(filespec)
*/
create database test
sp_helpdb test
alter database test modify file (name=test,size=20mb)
sp_helpdb test
alter database test
add file (name=test01
,filename='C:\Program Files\Microsoft SQL Server\MSSQL\data\test01.mdf'
,size=10mb,maxsize=50mb,filegrowth=5mb)
/*그룹 추가*/
alter database test
add filegroup testfilegroup01
alter database test
add file (name=testgroup01file,filename='c:\temp\testgroup01file.ndf'
,size=5mb,maxsize=50mb,filegrowth=5mb) to filegroup testfilegroup01
alter database test
modify filegroup testfilegroup01 default
sp_helpdb test
dbcc shrinkfile(test01,emptyfile)
alter database test
remove file test01
drop database test
// [2002년 7월 2일]
use northwind
create database test
sp_helpdb test
alter database test
add file(name=test01,filename='c:\temp\test_data01.mdf',
size=2mb,maxsize=10mb,filegrowth=5mb)
sp_helpdb test
alter database test
remove file test01
sp_helpdb test
alter database test
modify filegroup [primary] default
--이름 바꾸기~~~~~~
sp_dboption test,'single user',true
sp_renamedb 'test','kjo77zzang'
sp_dboption kjo77zzang,'single user',false
sp_helpdb kjo77zzang
use master
drop database kjo77zzang
/*
저렴하게 H/W(하드웨어) 구성하기
IDE 하드를 여러개 구입한다.
디스크 1 = O/S(+스왑) NTFS
디스크 2 = 스왑 FAT 형식
디스크 3 = Data File
디스크 4 = .
디스크 5 = .
디스크 6 = . 3~~6까지 NTFS 형식으로
디스크 7 = Log File
디스크 8 = .
디스크 9 = . 7~~9까지 FAT 형식으로 설정
*/
/*
Table의 정의 (Table 내의 Field에 관한 정의, 제약조건에 관한 정의)
Create
Alter
Drop 이 세가지고 정의한다.
create table [table name]
(
[field name] [Data type] [NULL , NOT NULL],
)
*/
create database test
use test
create table test --테이블 생성
(
a int not null, --테이블에 컬럼 추가
b varchar(50)
)
sp_columns test
insert into test (a,b) values (1,'test') --컬럼에 값을 넣는다.
insert test (a) values (2) --into는 생략 가능
select * from test
drop table test --테이블을 삭제 한다. drop table [table name]
sp_columns test
create table test --테이블 생성
(
a int,
b char(30),
c varchar(10)
)
--테이블에 값을 입력한다(a가 int형인데 문자열을 넣으려고 하니까 오류가 난다.)
insert into test values('a', 'aaa', 'aaa')
--'1'은 숫자형으로 얼마든지 변환이 가능하게 때문에 insert가 가능하당~~~~~ ^^
insert test values('1', 'aaa', 'aaa')
--정수가 아닌 소수점 이하는 버린다.
insert into test values(1.5, 'aaa', 'aaa')
--select 해 보면 1.5가 1로 들어가 있는걸 볼수 있다.
select * from test
--범위를 초가 해서 insert가 안된다. int 범위 초과
insert into test values(22222222222, 'aaa', 'aaa')
select len('aaa '), len('aaa')
select datalength('aaa '), datalength('aaa')
select datalength(b), datalength(c) from test
drop table test
create table test --이번엔 유니크적인 테이블을 만들어 보자
(
a int,
b uniqueidentifier
)
insert into test(a) values('1') --이렇게 하면 b 라는 컬럼에는 null이 들어가게 된다.
select * from test
insert test (a,b) values(2,newid()) /*uniqueidentifer는 newid()로 하면 된다. 절대 같은수가 안나온다구 는하는데
정말 그런지는 함 확인 해 보기*/
select * from test
// [2002년 7월 3일]
use test
drop table test
create table test
(
a int,
b timestamp /*timestamp 데이터베이스에서 유일하게 사용하는 고유값*/
)
insert into test values(1)/*(x)*/
insert into test(a) values(1)
select * from test
insert into test values(1,default) /*default로 넣으면 알아서 들어간다.*/
select * from test
drop table test
/*money type를 함 써보장*/
create table test
(
a int,
b money
)
insert into test values(1,10000000000)
select * from test
insert into test values(2,$100) /*화페단위를 넣어두 된다.*/
select * from test
insert into test values(3,\10000)
select * from test
drop table test
/*sql_variant를 살펴본다. */
/*특정 데이타 형식이 없는 타입이다. [알수 없는 타입이다.] (x)*/
/*가변적인 데이타 형식이다. 하위 형식이라는 특정한 데이타 형식을 취한다.*/
create table test
(
a int,
b varchar(50),
c datetime,
d sql_variant
)
/*sql_variant 에 관한것*/
insert into test (d) values(100)
insert into test (d) values('100')
insert into test (d) values('a')
insert into test (d) values($100)
select * from test
/*a int에 관한 것*/
insert into test (a) values(100)
insert into test (a) values('100')
insert into test (a) values('a')/*(X)문자는 삽입이 안된다. 단 숫자로 변환이 가능한건 된다. '100' <-- 이것처럼~~*/
insert into test (a) values($100)
select * from test
/*b varchar에 관한것*/
insert into test (b) values(100)
insert into test (b) values('100')
insert into test (b) values('a')
insert into test (b) values($100) /*(X)*/ /*money type은 varchar로 변환할수 없다.*/
insert into test (b) values(getdate())
insert into test (b) values(date()) /*(X)*/ /*date()*/
select * from test
/*datetime에 관한것*/
insert into test (c) values(getdate())
select * from test
insert into test(c) values('2002년 7월 3일')/*(X) 이형식은 안들어 간다.*/
insert into test (c) values('2002.07.03')
select * from test
insert into test (c) values('2002-07-03')
select * from test
insert into test(c) values('2002-07-03 15:21:00')
select * from test
insert into test(c) values('2002-02-30') /*(X) 2월 의 범위를 넘어서 안들어 간다.*/
drop table test
create table test
(
a int,
b varchar(50)
)
insert into test values(1,'aaa')
insert into test values(2,'bbb')
insert into test values(3,'ccc') /*이렇게 만들면 숫자를 다 입력해야 하기때문에 번거롭다.*/
drop table test
/*identity를 사용하여 자동 증감을 해봐자*/
create table test
(
a int identity(1,1),
b varchar(50)
)
insert into test values('aaa') /*a int 형은 identity로 설정하여 자동 증감한다. 그래서 생략 가능*/
insert into test values('bbb')
select * from test
insert into test(b) values('ccc')
select * from test
delete test where a=3
select * from test
delete test where a=17
select * from test
insert into test values('ddd') /*한번 지워진 숫자는 건너 뛰고 다음 숫자로 들어가게 된다.*/
select * from test
/*뛰어 넘은 숫자를 꼭 입력하구 싶다. 그렇다면~~~*/
set identity_insert test on
insert into test (a,b) values(3,'bbcc')
select * from test
set identity_insert test off
select * from test
drop table test
create table test
(
a int identity(30,5),
b varchar(50)
)
insert into test values('aaa')
insert into test values('bbb')
insert into test values('ccc')
select * from test
drop table test
/*사용자 정의 데이타 타입*/
sp_addtype [id],'varchar(50)' /*id는 varchar(50) 이라는 데이타 타입으로 만들게 됨*/
sp_addtype age,tinyint
create table test
(
membername [id],
memberage age
)
sp_columns test
insert into test values('kjo77',19)
select * from test
drop table test
sp_droptype [id] /*type을 drop하기전에 이 type으로 만들어져 있는 테이블을 먼저 drop하구 지워야 한다.*/
sp_droptype age
/*테이블 생성하기~~~~~~ 제대루~~~~*/
/*
create table [table_name]
(
[column spec], ..........,n
or [column name] as computed_column_expression
or [table_constraint]<----제약조건(primany key, null, not null)
)
on [filegroupname or defalut]
textimage_on [filegroupname or default] <-----생략가능 전부 default가 된다.
#[column spec]
[column name] [data type] (not null or null) [constraint] (nonclustered/clustered) with fillfactor=[file factor]
on [filegroup or default]
*/
// [2002년 7월 5일]
create database banchan
sp_helpdb banchan
alter database banchan
add filegroup banchangroup
sp_helpdb banchan
alter database banchan
add file
(
name=banchangroup01,
filename='c:\temp\banchangroup01.ndf',
size=10mb,maxsize=50mb,filegrowth=5mb
) to filegroup banchangroup
sp_helpdb banchan
/*시작 테이블 부터 시작해야 한다.....(여기서는 공장 테이블 부터 시작한다.)*/
use banchan
create table gongjang
(
gongnum varchar(50) not null primary key,
zipcode char(7) not null,
addr2 varchar(300) not null,
phone varchar(13) not null,
fax varchar(13) not null
)
create table member_sangtae
(
sangtae char(1) primary key, /*primary key 설정은 이렇게 한다.*/
content varchar(50) null
)
on banchangroup /*banchangroup에 테이블 지정*/
/*select * from sysobjects where type='u'*/
/*sp_help member_sangtae*/ /*어느 파일 그룹에 위치하나 확인해본다*/
drop table member_sangtae
create table member_sangtae
(
sangtae char(1) not null,
content varchar(50) not null
constraint pk_member_sangtae_sangtae primary key(sangtae)/*primary key설정을 이렇게 할수도 있다.*/
)on banchangroup
/*
foregin key 지정하는 방법
contstraint [constraint] [constraint condition]
#[constraint condition]
foreign key (target columns) reference [reference table]([reference column])
*/
create table daerijum
(
daeri_num varchar(50) not null, /*대리점 번호*/
gong_num varchar(50) not null, /*담당공장번호*/
num varchar(50) not null, /*사업자 등록 번호*/
[name] varchar(50),
constraint pk_daerijum_daeri_num primary key (daeri_num),
constraint fk_daerijum_gong_num_gongjang_gongnum
foreign key (gong_num) references gongjang(gongnum)
)
select * from sysobjects where type='u'
sp_help daerijum
// [2002년 7월 15일]
/*
TABLE 수정하기 (alter table)
alter table 형태~~~~~
alter table [table name]
alter column | constraint [[column spec] | [constraint condition]]
|
add [column spec]
|
[with check | with nocheck] add [constraint condition]
|
drop column | constraint[컬럼명이나 제약 조건]
|
[enable | disable] trigger [all 이나 trigger 이름.......,n]
*/
create database test
use test
create table test
(
[id] varchar(50) not null,
pwd varchar(30) not null,
constraint pk_test_id primary key([id])
)
insert into test values('kjo77','wjddhr')
alter table test
/*add [name] varchar(100) not null 기존에 값이 들어간 table에 컬럼을 추가할경우 not null로 하게 되면 에러가 나난다.*/
/*굳이 넣어야 할경우는~~~~*/
/*defalut를 선언한후 뒤에 들거갈 내용을 적어주면 not null로 alter를 한다.*/
add [name] varchar(100) not null default 'noname' --데이타 베이스를 만든후 컬럼을 추가하는 일은 바람직하지 않다.
select * from test
/*test table에 address컬럼을 추가한다.*/
alter table test
add address varchar(100) null
sp_columns test
/*varchar(100)인 address컬럼을 text형태로 바꾼다.*/
alter table test
alter column address text null
sp_columns test
/*address컬럼을 drop 한다.*/
alter table test
drop column address
sp_columns test
/*test table에서 id컬럼의 primary key를 해제 한다.*/
alter table test
drop constraint pk_test_id
sp_help test
/*test table에서 해제되었던 primary key를 다시 복원시킨다.*/
alter table test
add constraint pk_test_id primary key([id])
sp_help test
drop table member
/*두개의 테이블을 alter table을 이용해서 연결하기~~~*/
create table member --회원 테이블
(
[id] varchar(20) not null,
pwd varchar(20) not null,
sex varchar(2) not null
)
create table rent --대여 테이블
(
rent_idx int not null,
[id] varchar(20) not null,
title varchar(50) not null,
writeday smalldatetime not null
)
--여기서 부터 primary key를 설정합니다.
alter table member
add
constraint pk_member_id primary key (id)
alter table rent
add
constraint pk_rent_rent_idx primary key(rent_idx)
--primary key 설정 끝----
--여기서부터 foreign key를 설정합니다.
alter table rent
add
constraint fk_rent_id_member_id foreign key (id) references member(id)
--foreign key 설정 끝
--sex(성별 구별 컬럼)에 m과 f만 들어가도록 rule을 정한다.
create rule sexgubun as @sexgubun in ('m','f')
sp_bindrule sexgubun, 'member.sex'
--여기까지
--writeday(대여날짜 컬럼)을 현재시간만이 들어가도록 default설정한다.
alter table rent
add
constraint dft_rent_writeday default getdate() for writeday
--요기까지
sp_help rent
/*
Default & Rule
*/
drop table test
create table test (writtendate datetime not null default getdate()) --현재날짜를 default로 설정한다.
sp_help test
insert into test values(default)
select * from test
alter table test
drop constraint DF__test__writtendat__2D27B809 --default설정을 해제 합니다.
sp_help test
alter table test
add constraint dft_test_wirttendate default getdate() for writtendate
sp_help test
insert into test values (default)
select * from test
alter table test
drop constraint dft_test_wirttendate
create default dft_wirttendate as getdate()
sp_bindefault dft_wirttendate, 'test.writtendate'
sp_help test
insert into test values(default)
select * from test
sp_unbindefault 'test.writtendate'
drop default dft_wirttendate
drop table test
/*Rule 특정한 문자만 들어오도록 한다.*/
/*많이 쓰이니 꼭 해보는것두 좋당.~~~~~~~~*/
create table test (state int not null)
create rule videostate as @videostate in (0,1,2) /*0:대기대여, 1:대여중, 2:파손*/
sp_bindrule videostate, 'test.state'
sp_help test
insert into test values(1)
select * from test
insert into test values(3) --rule에 의해 3은 들어갈수가 없다.
update test set state=4 --rule에 의해 4는 update 할수 없다
sp_unbindrule 'test.state'
drop rule videostate
drop table test
use northwind
select * from employees
select * from orders
select *
from orders
inner join employees as emp on orders.employeeid=emp.employeeid
select orderid,productid,unitprice*quantity*(1-discount) as '주문총액'
from [order details]
/*view의 생성*/
/*마치 table인냥 행동하도록 만들어 준다.
짜기 어려운 쿼리를 view로 만들어놓구 필요할때 마다 빼다가 쓰면 되는 것이다.
*/
create view [주문별총액과담당자보기]
as
select
o.orderid as '주문번호',e.lastname as '담당자',
o.shipaddress as '배송지주소',od.total as '주문총액'
from orders as o
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as 'total' from [order details]
group by orderid) as od
on o.orderid = od.orderid
inner join employees as e
on o.employeeid=e.employeeid
/*
요거봐라 ~~ 지가 table 인줄 안당
하지만 난 안다 table이 아닌것을 "넌~~~ view야~~~~~~~~~~"
*/
select * from [주문별총액과담당자보기] --넌 view다~~~
sp_helptext [주문별총액과담당자보기] --view query 보기
sp_depends [주문별총액과담당자보기]
// [2002년 7월 16일]
use northwind
select * from sysobjects where type='v'
drop view 주문별총액과담당자보기
/*주문별 담당자와 총액 을 select 한다.*/
select ord.orderid,emp.lastname,ord.shipaddress,o.total from orders as ord
inner join employees as emp on ord.employeeid=emp.employeeid
inner join (select orders.orderid,sum(unitprice*quantity*(1-discount)) as total from orders
inner join [order details] as od on orders.orderid=od.orderid
group by orders.orderid) as o on ord.orderid=o.orderid
order by ord.orderid
select orders.orderid,sum(unitprice*quantity*(1-discount)) as total from orders
inner join [order details] as od on orders.orderid=od.orderid
group by orders.orderid
/*select 함 부분을 view로 만들어서 쉽게 끌어다 쓴다.*/
create view emptotal
as
select ord.orderid,emp.lastname,ord.shipaddress,o.total
from orders as ord
inner join employees as emp on ord.employeeid=emp.employeeid
inner join (select orders.orderid,sum(unitprice*quantity*(1-discount)) as total from orders
inner join [order details] as od on orders.orderid=od.orderid
group by orders.orderid) as o on ord.orderid=o.orderid
--order by ord.orderid view를 생성에 있어서 order by는 안된다. 왜냐면 생성후 select 할때 하면 되니까~~
select * from emptotal order by orderid
sp_helptext emptotal --view의 쿼리 부분을 출력해 준다.
sp_depends emptotal --view의 참조하고 있는 테이블과 컬럼들을 출력해 준다.
/*view를 아무도 못보게 암호화 시키기~~*/
/*원칙적으론 암호화된것은 되돌릴수는 없다.*/
/*그래서 따로 문서화 시켜서 저장을 한다. 난중에 복구나 관리를 위해서~~~*/
alter view emptotal with encryption
as
select ord.orderid,emp.lastname,ord.shipaddress,o.total
from orders as ord
inner join employees as emp on ord.employeeid=emp.employeeid
inner join (select orders.orderid,sum(unitprice*quantity*(1-discount)) as total from orders
inner join [order details] as od on orders.orderid=od.orderid
group by orders.orderid) as o on ord.orderid=o.orderid
sp_helptext emptotal --암호화 되어 select문장이 보이지 않는다.
sp_depends emptotal --이부분은 숨길수가 없다.
drop view emptotal
/*view를 통한 데이타 수정 , 삽입 */
use test
create table a
(
idx int not null,
title varchar(50) null
)
create table b
(
idx int not null,
content varchar(3000) null
)
create view a1
as
select idx from a
select * from a1
insert into a1 values(1)
select * from a1
select * from a
update a1 set idx=3 where idx=1
select * from a1
/*view에 대한 데이타 수정시 주의사항*/
--1, view에 없는 컬럼의 입력이 있는경우
-- 해당 컬럼이 null허용하거나 default 제약이 있어야 합니다.
create view a2
as
select title from a
insert a2 values('멋쟁이 제목') --물리적인 table의 idx가 not null이기 때문에 인서트가 불가능하당~~~
--2.계산된 컬럼/파생된 컬럼에 대한 수정은 불가능하다.
create table c
(
unitprice int not null,
quantity int not null
)
insert c values('500','100')
insert c values('3000','20')
create view c_total
as
select unitprice*quantity as 'total' from c
select * from c_total
insert c_total values ('200000') --view안에 있는 쿼리 내용이 계산식이기 때문에 insert가 불가능하다.
update c_total set total='300000' where total=5000
--3. 2개 이상의 join 된 view에서의 추가가 안된다.
select * from a
insert into b values(3,'멋쟁이 내용')
create view ab
as
select a.idx,a.title,b.content from a
inner join b on a.idx=b.idx
select * from ab
insert into ab values(4,'쨍이 제목','쨍이 내용') --2개이상의 join된 view에서는 insert가 안된다.
update ab set title='멋쟁이 제목' where idx=3 --update는 된다
/*view를 만들수 없는 질의어*/
--1.select into , select top , order by , compute , compute by
create view ab1
as
select a.idx,a.title,b.content from a
inner join b on a.idx=b.idx
order by a.idx --view 생성에 있어 order by는 사용할수 없다.
--2.임시 table을 이용한 질의는 view 생성이 안된다.
select * into #test from a
create view test1
as
select title from #test
--3.일괄처리 안에서 뷰를 만들고 참조할수 없다.(Go로 분리하면 가능하다.)
create view a3
as
select * from a --일괄처리안에서 뷰를 만들고 그뷰를 select 할수 없다.
go --단 Go로 설정해 주면 된다.
select * from a3
/*스토어 프로시져~~~~ 시작~~~~~~~~*/
sp_help a
sp_databases
use green_video
sp_fkeys title
use test
sp_helptext a1
sp_depends a1
sp_lock
/*begin tran
delete a 시작한다는 명령어는 있지만 끝난다는 명령어는 없으므로 계속 붙잡고 있게 된다.
*/
select [name],[id] from sysobjects where type='u' --잡고있는 테이블의 id를 알수 있다.
1269579561
sp_lock --현잡고있는 번호가 무언지를 알수 있다.
kill 53 --잡고 있는 번호를 kill을 통해 죽여버린다. kill은 곧 죽여버린다는 뜻~~~ ^^
select * from b
/*
드디어 그렇게 배우고 싶었던 스토어 프로시져를 들어가게 된다. 이렇게 기쁠수가~~~~~~~~~~
이게 꿈이야 생시야~~~~~~~~
욜심히 해야징~~~~~~~~
긴장을 늦추지 말구~~~~~~~
혁띠 더욱 쪼이구~~~~~~
그럼 스토어 프로시져 시작~~~
create proc [proc name]
[변수 리스트]
as
[sql 구문]
==변수 리스트
[변수명] [데이타 타입] (OUTPUT),........n
*/
use northwind
create view [7월주문]
as
select * from orders where datediff(mm,orderdate,'1996-07-01')=0
select * from [7월주문]
declare @wannadate datetime
set @wannadate='1996-07-01'
select * from orders where datediff(mm,orderdate,@wannadate)=0
/*proc 를 만든다.*/
create proc procmonthorder
@wannadate datetime
as
select * from orders where datediff(mm,orderdate,@wannadate)=0
/*사용법~~~~~*/
procmonthorder @wannadate='1996-09-01' --실질적으로는 앞에다 변수명을 적어 주어서 실행한다.
use master
xp_cmdshell 'dir d:\' --C 드라이브의 내용을 여기서두 볼수 있다.
use northwind
sp_helptext procmonthorder --procedure에 어떤 쿼리가 있나를 볼수 있다.
// [2002년 7월 18일]
use northwind
select * from orders where datediff(mm,orderdate,'1996-07-01')=0
create view monthlyorder
as
select * from orders where datediff(mm,orderdate,'1996-07-01')=0
select * from monthlyorder
create view nowmonthlyorder
as
select * from orders where datediff(mm,orderdate,getdate())=0
select * from nowmonthlyorder
declare @wannadate smalldatetime
set @wannadate = '1996-07-01'
select * from orders where datediff(mm,orderdate,@wannadate)=0
create proc monthly
@wannadate smalldatetime
as
select * from orders where datediff(mm,orderdate,@wannadate)=0
monthly '1996-06-31'
drop proc monthly
/*
1. 일반 SQL문의 실행과 반복 과정
1) 처음 실행할때
a) 구문분석 : 키워드 분리, 문법검사
b) 표준화 : 각 object의 이름을 확인
c) 보안점검 : 각 object에 관한 적적한 권한이 있는지를 확인.
d) 최적화 : 가장 빠른 성능을 내도록 색인, 조인, 잠금을 결정한다.
e) 컴파일
2) 반복 실행
a) 1)의 a-d과정의 결과인 실행 계획이 캐시에 있으면 그대로 컴파일 실행
b) 없으면 a-e의 과정을 그대로 수행
2. 저장 프로시저의 실행 과정
1) 만들때
a) 구문 분석
b) 표준화
c) 보안점검
d) 결과를 syscomments와 sysobjects에 저장
2) 첫 실행
a) 보안점검
b) 최적화
c) 컴파일하고 실행 계획을 저장
3) 반복 실행
a) 실행 계획이 캐쉬에 있으면 바로 실행
b) 없으면 2)의 과정을 거치게 된다.
이점 :
1) 컴파일 하지 않아 빠르다
2) 네트워크에서 오고가는 긴 SQL문의 트레픽을 줄일수 있다.
3) 보안관리가 용이
4) 매개변수를 사용할수 있다.
5) OUTPUT 매개 변수를 사용할수 있다.
6) RETURN 값을 사용할수 있다.
7)원격 프로시저를 이용하면 원격 서버의 데이타를 처리할수 있다.
*/
/*더하기 하는 프로시져~~~ 전혀 쓸대 없지만 우리는 함 해볼꼬야~~~*/
create proc plus
@a int,
@b int,
@c int
as
select @a+@b+@c as '더하기 결과'
exec plus @a=1,@b=2,@c=3
/*프로시저 수정하기~~~*/
alter proc plus
@a int,
@b int,
@c int
as
select @a as 'a', @b as 'b', @c as 'c', @a+@b+@c as '더하기 결과'
/*각각의 변수에 값이 들어가는 걸 볼수 있다.*/
plus@b=3, @c=1, @a=2
drop proc plus
/*
저장 프로시저 생성시 주의점
1. 지연이름 확인
2. 중첩 프로시저
*/
select * from sysobjects where type='u' and name='test'
create proc proctest
as
select * from test
drop proc proctest
create table test
(
a int not null
)
execute proctest
drop table test
/*중첩 프로시저~~*/
create proc viewlock
as
execute sp_lock
viewlock
-- 단 32단계 까지만 허용 된다.
use master
sp_helptext [sp_databases]
sp_depends [sp_databases]
/*프로시저 암호화 만들기~~~*/
/*뷰와 마찬가지로 암호화할 프로시저를 문서로 가지고 있는것이 좋다.*/
use northwind
alter proc viewlock
--이사이에 매개변수가 들어간다.
with encryption
as
exec sp_lock
/*암호화를 파기할려면 with encryption을 빼고 수정에 들어가면 된다.*/
alter proc viewlock
as
exec sp_lock
sp_helptext viewlock --암호화되어 프로시저 내용이 나오질 않는다.
/*프로시저에서 매개변수를 쓰자 */
/*use pubs
create proc p1
@f int,
@r int output
as
select top 1 * from titles
update titles set price = price * @f
select @r = @@rowcount
select top 1 * from titles */
create proc plus1
@a int,
@b int,
@c int output
as
set @c = @a + @b
declare @result int
execute plus1 1,2,@result output
print @result
/*@c는 프로시저 안에 있는 @c와는 별개의 변수이다.*/
/*javascript에서의 전역변수와 지역변수를 생각하면 쉬울것이다.*/
declare @c int
execute plus1 2,3,@c output
print @c
/*프로시저 변경*/
alter proc plus1
@a int,
@b int
as
return @a+@b
/*
declare @c int
set @c = plus1 (1,2) --set 이건 안된다.
print @c
*/
declare @c int
exec @c = plus1 1,2
print @c
alter proc plus1
@a int = 1, --default값을 넣어준다.
@b int = 2
as
return @a+@b
declare @c int
exec @c = plus1 10,20 --변수에 넣을 값을 정하면 default값이 있더라두 넣은값이 선정되어 변수에 들어간다.
print @c
/*아무것두 지정안하면 위에 있는 디폴트 값이 나온다.*/
declare @c int
exec @c = plus1
print @c
/*많이 쓰는 프로시저 명령어들*/
create table test
(
a int not null,
)
insert into test values(10)
/*update를 천번한다.*/
create proc updatetest
as
declare @cnt int
set @cnt = 0
while @cnt <1000
begin
update test set a = 5
set @cnt = @cnt+1
end
/*update를 천번하면서 실행 결과를 1000번찍는 그럴필요가 없기때문에 alter로 프로시저를 수정한다.*/
updatetest
alter proc updatetest
as
set nocount on --nocount on으로 실행결과를 없앤다.
declare @cnt int
set @cnt = 0
while @cnt <1000
begin
update test set a = 5
set @cnt = @cnt+1
end
set nocount off --nocount off로 다시 원상복귀 시킨다.
updatetest --1000번의 "적용했습니다." 대신 한줄의 "명령이 성공적으로 완료되었습니다." 볼수 있을것이다.
// [2002년 7월 19일]
use northwind
drop proc monthlyorders
create proc monthlyorders
@wannadate smalldatetime,
@cntorders int output
as
set nocount on
select * from orders where datediff(mm,orderdate,@wannadate)=0
set @cntorders=@@rowcount
set nocount off
monthlyorders '1996-08-20'
declare @cnt int
exec monthlyorders '1996-08-20', @cnt output
select @cnt as '해당달의 주문량'
alter proc monthlyorders
@wannadate smalldatetime
as
set nocount on
select * from orders where datediff(mm,orderdate,@wannadate)=0
return @@rowcount
set nocount off
monthlyorders '1996-09-20'
declare @cnt int
--exec set @cnt = monthlyorders '1998-09-20'
exec @cnt = monthlyorders '1996-08-20'
select @cnt as '리턴된 값'
/*인제 문제가 나간다.
아~~~ 진짜 어렵당 스토어 프로시져~~~ 진짜 어렵당~~~
northwind 사원별 실적조회 프로시저 작성
-- 사원의 이름 또는 번호를 통하여 실적을 조회한다.
-- 동명 2인이 존재할 경우 동명 2인이 존재한다는 메세지와 함께 종료
-- 해당 사원이 존재하지 않을 경우 해당사원이 존재하지 않는다는 메세지와 함께 종료
-- 리턴값으로 해당 사원의 총매출액을 반환한다.
-- 결과는 [사원번호] [lastname] [총 주문횟수] [총 주문에 의한 매출액] 과
[주문번호] [고객회사] [주문횟수] 의 목록으로 표현된다.
*/
select * from orders
select * from employees
select * from [order details]
create proc emp_order
@strempid int,
@cnt int output
as
select e.employeeid as '사원번호',e.lastname,ord.total
from orders as o
inner join employees as e on o.employeeid = e.employeeid
inner join (select o.orderid, sum(unitprice*quantity*(1-discount)) as 'total' from orders as o
inner join [order details] as od on o.orderid = od.orderid
group byo.orderid) as ord on o.orderid = ord.orderid
wheree.employeeid=@strempid
order by e.employeeid
set @cnt=@@rowcount
select o.orderid as '주문번호',o.shipname as '고객회사',unitprice*quantity*(1-discount) as 'total' from orders as o
inner join [order details] as od on o.orderid = od.orderid
where o.employeeid = @strempid
order by o.employeeid
--drop proc emp_order
declare @cnt int
exec emp_order 2,@cnt output
select @cnt as '주문 횟수'
select o.orderid, sum(unitprice*quantity*(1-discount)) as 'total' from orders as o
inner join [order details] as od on o.orderid = od.orderid
group by o.orderid
order by o.orderid
select o.orderid as '주문번호',o.shipname as '고객회사',unitprice*quantity*(1-discount) as 'total' from orders as o
inner join [order details] as od on o.orderid = od.orderid
where o.employeeid = 2
order by o.employeeid
// [2002년 7월 22일]
/*프로시저 말들기~~~~~ 다시 시작*/
/*인제 문제가 나간다.
아~~~ 진짜 어렵당 스토어 프로시져~~~ 진짜 어렵당~~~
northwind 사원별 실적조회 프로시저 작성
-- 사원의 이름 또는 번호를 통하여 실적을 조회한다.
-- 동명 2인이 존재할 경우 동명 2인이 존재한다는 메세지와 함께 종료
-- 해당 사원이 존재하지 않을 경우 해당사원이 존재하지 않는다는 메세지와 함께 종료
-- 리턴값으로 해당 사원의 총매출액을 반환한다.
-- 결과는 [사원번호] [lastname] [총 주문횟수] [총 주문에 의한 매출액] 과
[주문번호] [고객회사] [주문횟수] 의 목록으로 표현된다.
*/
use northwind
select * from employees
alter proc orderbyemp
@empid int = 0,
@lastname nvarchar(40) = ''
as
if @empid != 0
begin
if (select count(*) from employees whereemployeeid=@empid)=0
begin
print '존재하지 않는 사원번호 입니다.'
end
else
begin
select orders.orderid as '주문번호',customers.companyname as '고객회사명',x.sumbyorder as '총주문액' from orders
inner join customers on orders.customerid = customers.customerid
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where orders.employeeid = @empid
select employees.employeeid as '사원번호',employees.lastname,count(y.주문총액) as '주문횟수',sum(y.주문총액) as '당담자의 총주문액'
from employees
inner join (select orders.employeeid,x.sumbyorder as '주문총액' from orders
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where orders.employeeid = @empid) as y
on employees.employeeid = y.employeeid
group by employees.employeeid, employees.lastname
end
end
else
begin
if @lastname = ''
begin
print '사원번호 또는 이름을 입력해 주세요'
end
else
begin
if (select count(*) from employees wherelastname=@lastname)=0
begin
print '사원이 존재하지 않습니다.'
end
else if (select count(*) from employees wherelastname=@lastname)>1
begin
print '동명 2인이 존재 합니다.'
end
else
begin
select orders.orderid as '주문번호',customers.companyname as '고객회사명',x.sumbyorder as '총주문액' from orders
inner join customers on orders.customerid = customers.customerid
inner join employees on orders.employeeid = employees.employeeid
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where employees.lastname = @lastname
select employees.employeeid as '사원번호', employees.lastname,
count(y.주문총액) as '주문횟수',sum(y.주문총액) as '당담자의 총주문액'
from employees
inner join (select orders.employeeid,x.sumbyorder as '주문총액' from orders
inner join employees on orders.employeeid = employees.employeeid
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where employees.lastname = @lastname) as y
on employees.employeeid = y.employeeid
group by employees.employeeid, employees.lastname
end
end
end
orderbyemp 90
select orders.orderid,customers.companyname,ord.sum_total
from orders
inner join employees on orders.employeeid = employees.employeeid
inner join customers on orders.customerid = customers.customerid
inner join (select [order details].orderid, sum(unitprice*quantity*(1-discount)) as 'sum_total'
from orders
inner join [order details] on orders.orderid=[order details].orderid
group by [order details].orderid) as ord on orders.orderid = ord.orderid
where employees.lastname='peacock'
select [order details].orderid, sum(unitprice*quantity*(1-discount)) as 'sum_total'
from orders
inner join [order details] on orders.orderid=[order details].orderid
group by [order details].orderid
// [2002년 7월 23일]
use northwind
select * from orders
select orders.orderid as '주문번호',customers.companyname as '고객회사명',x.sumbyorder as '총주문액' from orders
inner join customers on orders.customerid = customers.customerid
inner join employees on orders.employeeid = employees.employeeid
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where employees.lastname = 'leverling'
select employees.employeeid as '사원번호', employees.lastname,
count(y.주문총액) as '주문횟수',sum(y.주문총액) as '당담자의 총주문액'
from employees
inner join (select orders.employeeid,x.sumbyorder as '주문총액' from orders
inner join employees on orders.employeeid = employees.employeeid
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where employees.lastname = 'leverling') as y
on employees.employeeid = y.employeeid
group by employees.employeeid, employees.lastname
/*employeeid로 검색 하기*/
select orders.orderid as '주문번호',customers.companyname as '고객회사명',x.sumbyorder as '총주문액' from orders
inner join customers on orders.customerid = customers.customerid
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where orders.employeeid = 3
select employees.employeeid as '사원번호',employees.lastname,count(y.주문총액) as '주문횟수',sum(y.주문총액) as '당담자의 총주문액'
from employees
inner join (select orders.employeeid,x.sumbyorder as '주문총액' from orders
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where orders.employeeid = 3) as y
on employees.employeeid = y.employeeid
group by employees.employeeid, employees.lastname
select * from [order details]
select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid
/*스토어 프로시저 완성~~~~~ 죽이는구먼~~~~~~ 내가 이런걸 할줄이야~~~*/
create proc orderbyemp
@empid int = 0,
@lastname nvarchar(40) = ''
as
declare @cnt int
if @empid != 0
begin
if (select count(*) from employees whereemployeeid=@empid)=0
begin
print '존재하지 않는 사원번호 입니다.'
end
else
begin
select orders.orderid as '주문번호',customers.companyname as '고객회사명',x.sumbyorder as '총주문액' from orders
inner join customers on orders.customerid = customers.customerid
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where orders.employeeid = @empid
set @cnt = @@rowcount
select employees.employeeid as '사원번호',employees.lastname,count(y.주문총액) as '주문횟수',sum(y.주문총액) as '당담자의 총주문액'
from employees
inner join (select orders.employeeid,x.sumbyorder as '주문총액' from orders
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where orders.employeeid = @empid) as y
on employees.employeeid = y.employeeid
group by employees.employeeid, employees.lastname
return @cnt
end
end
else
begin
if @lastname = ''
begin
print '사원번호 또는 이름을 입력해 주세요'
end
else
begin
if (select count(*) from employees wherelastname=@lastname)=0
begin
print '사원이 존재하지 않습니다.'
end
else if (select count(*) from employees wherelastname=@lastname)>1
begin
print '동명 2인이 존재 합니다.'
end
else
begin
select orders.orderid as '주문번호',customers.companyname as '고객회사명',x.sumbyorder as '총주문액' from orders
inner join customers on orders.customerid = customers.customerid
inner join employees on orders.employeeid = employees.employeeid
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where employees.lastname = @lastname
set @cnt = @@rowcount
select employees.employeeid as '사원번호', employees.lastname,
count(y.주문총액) as '주문횟수',sum(y.주문총액) as '당담자의 총주문액'
from employees
inner join (select orders.employeeid,x.sumbyorder as '주문총액' from orders
inner join employees on orders.employeeid = employees.employeeid
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where employees.lastname = @lastname) as y
on employees.employeeid = y.employeeid
group by employees.employeeid, employees.lastname
return @cnt
end
end
end
orderbyemp 1
orderbyemp 10 --존재하지 않은 사원이라 나온다.
orderbyemp @lastname='king'
orderbyemp @lastname='kjo77' --존재하지 않은 사원이름이라 나온다.
declare @cnt int
set exec @cnt = orderbyemp 4
select @cnt as '반환되는 총 주문 횟수'
/*Cursor 사용하기~~~~~~*/
/*
Cursor 선언하기
declare [cursor_name] (insensitive | scroll) cursor
for [sql statement]
(for read only | update (of [column_name]))
Cursor 열기
open [cursor_name]
Cusor 이동
fetch (next | prior | first | last | absolute[n] | relative[n] from cursor name] inot [variables name][
@@fetch_status : 0=성공,-1=실패,-2=반입된 행 없어
Cursor 닫기
close [cursor_name]
Cursor 파기하기
deallocate [cursor_name]
*/
use pubs
declare cs_sales scroll cursor
for select * from sales
for read only
open cs_sales
fetch next
from cs_sales
select @@fetch_status
fetch prior
from cs_sales
fetch first
from cs_sales
fetch last
from cs_sales
close cs_sales
deallocate cs_sales
declare cs_sales cursor
for select ord_date from sales
open cs_sales
declare @nowdate datetime
fetch
from cs_sales into @nowdate
print datediff(dd,@nowdate,getdate())
close cs_sales
deallocate cs_sales
--
// [2002년 7월 24일]
/*
--커서의 선언 및 생성
declare [cursor name] insensitive | scroll cursor
for [sql statement]
for read only | update (of [cloumn name]..........)
--커서 열기
open [cursor name]
--커서의 이동
fetch (next | prior | first | last | absolute [n] | relative [n])
from [cursor name]
into [variables name].....
@@fetch_status : 0=성공, -1=실패, -2=반입된 행 없어
--커서 닫기
close [cursor name]
--커서 파기
deallocate [cursor name]
*/
use northwind
declare curs_orders scroll cursor --cursor 만들기
for select * from orders
open curs_orders --먼저 cursor를 열어 준다.
fetch next --cursor를 다음 칸으로 이동한다.
from curs_orders
fetch next --cursor를 다음 칸으로 이동한 후에 상태를 표시 한다.
from curs_orders
select @@fetch_status
fetch prior --이전으로 으로 가는것두 가능한다.
from curs_orders
fetch last --cursor를 마지막 레코드로 이동 시킨다.
from curs_orders
fetch first --cursor를 처음 레코드로 이동시킨다.
from curs_orders
close curs_orders --cursor를 닫는다. 닫기만 할뿐 파기는 되지 않는다.
deallocate curs_orders --cursor를 실질적으로 파기한다.
declare cur_order scroll cursor --cur_order라는 cursor를 만든다. 이번엔 orderid를 뽑아낸다
for select orderid from orders
open cur_order --cursor를 open 한다.
declare @i int --변수 1개을 선언해서 절대 적인위치 2로 cursor를 이동시킨다.
fetch absolute 2
from cur_order
into @i
print @i
close cur_order
deallocate cur_order
declare cur_order scroll cursor --다시 cursor를 만든다. 이번엔 orderid와 employeeid를 뽑아 낸다.
for select orderid,employeeid from orders
open cur_order
fetch
from cur_order
declare @i int --변수 두개를 선언해서 orderid와 employeeid를 뽑아낸다.
declare @empid int
fetch relative 2 --이번엔 상대적인 cursor를 이동으로 한다.
from cur_order
into @i,@empid
print @i
print @empid
close cur_order
deallocate cur_order
declare cur_order scroll cursor
for select * from orders order by orderid
open cur_order
fetch next
from cur_order
while @@fetch_status=0
begin
fetch next
from cur_order
end
close cur_order
deallocate cur_order
/*문제~~~~~~
--주문별 총 액을 cursor를 이용하여 구하시요~~~~
*/
declare cur_od cursor
for select orderid,unitprice,quantity,discount from [order details] order by orderid
open cur_od
declare @orderid int
declare @unitprice money
declare @quantity smallint
declare @discount real
declare @sum money
declare @pre_orderid int
set @sum=0
fetch from cur_od into @orderid,@unitprice,@quantity,@discount
set @sum = @sum + (@unitprice*@quantity*(1-@discount))
set @pre_orderid = @orderid
while @@fetch_status=0
begin
fetch next
from cur_od
into @orderid,@unitprice,@quantity,@discount
if @pre_orderid != @orderid
begin
print convert(varchar(20),@pre_orderid)+ ' : ' + convert(varchar(300),@sum)
set @sum = @unitprice*@quantity*(1-@discount)
set @pre_orderid = @orderid
end
else
begin
set @sum = @sum+(@unitprice*@quantity*(1-@discount))
end
end
print convert(varchar(20),@pre_orderid) + ' : ' + convert(varchar(300),@sum)
close cur_od
deallocate cur_od
/*
--트랜잭션의 시작
begin tran
--트랜잭션 취소
rollback tran
--트랜잭션의 적용
commit tran
*/
begin tran
delete from [order details]
select * from [order details]
rollback tran --rollnback tran을 하면 전에 첨으로 돌아간다. 한번 해주면 다시 begin tran을 선언해야 사용할수 있다.
select * from [order details]
begin tran
delete from [order details]
select * from orders
commit tran --트랜젝션의 적용이다. 적용을 시키면 다시 되돌일수가 없다
--모든작업이 완벽하게 끝났을경우에 적용을 시킨다.
/*트랜젝션을 이용하여 계좌 이체를 만들어 본다.*/
drop table guja2
create table guja1
(
idx int not null primary key identity(1,1),
title varchar(500) not null,
inout money not null
)
create table guja2
(
idx int not null primary key identity(1,1),
title varchar(500) not null,
inout money not null
)
create proc inmoney
@much money,
@title varchar(300)
as
insert into guja1 values(@title,@much)
inmoney 20000,'CD 입금'
create proc gujayichae
@much money
as
begin tran
insert into guja1 values('이체출금',-1*@much)
insert into guja2 values('이체입금',@much)
commit tran
gujayichae 100000
select * from guja1
select * from guja2
/*그럼 억지로 오류를 내 보겠당*/
alter proc gujayichae
@much money
as
begin tran
insert into guja1 values('이체출금',-1*@much)
insert into xguja2 values('이체입금',@much)
commit tran
gujayichae 100000
select * from guja1
select * from guja2
// [2002년 7월 25일]
/*
--트랜젝션의 시작
begin tran
--트랜잭션의 취소
rollback tran
--트랜잭션의 반영
commit tran
set xact_about on | off
*/
use tempdb
create table a1
(
i int primary key
)
create table a2
(
i int references a1(i)
)
insert a1 values(1)
insert a1 values(3)
insert a1 values(7)
insert a1 values(5)
--그냥 트랜젝션을 걸어 놓으면 에러가 나두 rollback이 이루어지지 않는다.
begin tran
insert a2 values(1)
insert a2 values(3)
insert a2 values(2) --foregin key error
insert a2 values(7)
commit tran
select * from a2
delete from a2
drop table a2
drop table a1
--set xact_abort on 를 선언한후에 트랜젝션을 넣어야지 에러가 나면 rollback을 한다.
set xact_abort on
begin tran
insert a2 values(1)
insert a2 values(3)
insert a2 values(2) --foregin key error
insert a2 values(7)
commit tran
set xact_abort off --off로 끝내야 실행 된다.
select * from a2
drop table a2
drop table a1
create table guja1 (idx int primary key identity(1,1),title varchar(10) null,much money)
create table guja2 (idx int primary key identity(1,1),title varchar(10) null,much money)
create proc yichae
@much money,
@memo varchar(100)
as
set xact_abort on
begin tran
insert guja1 values('이체출금',-@much)
insert guja2 values(@memo,@much)
commit tran
set xact_abort off
yichae 10000,'쨍이 보냄'
select * from guja1
select * from guja2
yichae 50000,'생일축하합니다. 옥이가보냈습니다.'
/*
#암시적 트랜잭션
set implicit_transation on | off
*/
use northwind
select from sysobjects where type='u'
select * from products
begin tran
delete from [products] where productid=1
rollback tran
--암시적 트랜잭션
set implicit_transactions on --이걸 걸어주면 언제든지 rollback tran이 가능하다.
delete [employeeTerritories]
rollback tran
select * from employeeTerritories
delete from [order details]
select * from [order details]
rollback tran
select * from [order details]
/*
트리거의 사작~~~~~ 죽이는구먼 트리거까지 배우다니~~~~~~ ^^;
create trigger [trigger name]
on [table name | view name]
(with encryption)
for | after | instead of
insert ,| update ,| delete
as
sql statement (include trigger specific statement)
*/
use northwind
drop table a
drop table b
create table a
(
[id] int,
c1 char(10) primary key
)
create table b
(
a varchar(10)
)
--a Table 에 insert가 일어나기 전에 실행되는 동작(트리거) 정의
create trigger tr1 on a for insert
as
insert into b values('b')
go
--a Table 에 insert가 일어나고 나서 실행되는 동작(트리거) 정의
create trigger tr2 on a after insert
as
insert into b values('a')
--위의 2개의 트리거가 아래의 질의어에 의해 insert가 일어나기 전과 후에 실행
insert a values(1,'a')
--그 결과를 아래와 같이 확인
select * from a
select * from b
--a Table의 insert가 일어나는 것을 대신하여 실행되는 동작(트리거) 정의
--고로 a Table에 insert자체가 일어나지 않으므로 상단에 정의된 2개의 트리거는 일어나지 않는다.
create trigger tr3 on a instead of insert
as
insert into b values('c')
insert a values(1,'r')
select * from a
select * from b
drop trigger tr1
drop trigger tr2
drop trigger tr3
create trigger tr1 on a for insert
as
select * from inserted
insert into a values(1,'e')
insert into a values(1,'e')
create table a1
(
[id] int,
c1 char(10)
)
create trigger tr3 on a instead of insert
as
if exists(select * from a inner join inserted on a.c1 = inserted.c1)
begin
--a1으로 데이타 넣기
insert into a1 select * from inserted
end
else
begin
--a에 데이타 넣기
insert into a select * from inserted
end
insert into a values(1,'a')
select * from a
select * from a1
insert into a values(1,'a')
select * from a
select * from a1
/*비디오 대여 에서 포이트 누적이랑 이것저것 하기~~~ ^^*/
create table rent
(
idx int primary key identity(1,1),
[id] varchar(50) not null references member([id]),
point int null
)
create table member
(
[id] varchar(50) primary key,
point money not null
)
insert into member values('kjo77',0)
insert into member values('kjohu',0)
create trigger tr_rent on rent after insert
as
declare @id varchar(20)
declare @point int
select @id=[id],@point=point from inserted
update member setpoint=point+@pointwhereid=@id
select * from rent
insert into rent values('kjo77',100)
select * from rent
select * from member
insert into rent values('kjo77',200)
select * from rent
select * from member
insert into rent values('kjohu',200)
select * from rent
select * from member
insert into rent values('kjohu',-100)
select * from rent
select * from member
drop table a
drop table b
drop table a1
--중첩 트리거 만들기~~~~~~
create table a
(
i int not null
)
create table b
(
i int not null
)
create table c
(
i int not null
)
create trigger tra on a after insert
as
insert into b select * from inserted
create trigger trb on b after insert
as
insert into c select * from inserted
insert into a values(1)
select * from a
select * from b
select * from c
// [2002년 7월 26일]
use tempdb
create table member
(
[id] varchar(50) not null primary key,
point money not null
)
create table rent
(
idx int not null primary key identity(1,1),
[id] varchar(50) not null references member([id]),
title varchar(200) not null,
much money not null,
point int not null
)
insert into member values('kjo77',0)
create trigger tr_point on rent after insert
as
declare @id varchar(50)
declare @point int
select @id=[id],
@point = point from inserted
update member set point =point+@pointwhere [id] = @id
insert into rent values('kjo77','닷넷 따라하기',500,100)
select * from rent
select * from member
insert into rent values('kjo77','여곡성',0,-10)
select * from rent
select * from member
create table a
(
idx int not null primary key,
point int not null
)
create trigger tr_a on member after insert
as
declare @point int
select @point = point from inserted
// 프로시저 연습
/*
northwind 사원별 실적조회 프로시저 작성
-- 사원의 이름 또는 번호를 통하여 실적을 조회한다.
-- 동명 2인이 존재할 경우 동명 2인이 존재한다는 메세지와 함께 종료
-- 해당 사원이 존재하지 않을 경우 해당사원이 존재하지 않는다는 메세지와 함께 종료
-- 리턴값으로 해당 사원의 총매출액을 반환한다.
-- 결과는 [사원번호] [lastname] [총 주문횟수] [총 주문에 의한 매출액] 과
[주문번호] [고객회사] [주문총액] 의 목록으로 표현된다.
*/
---------------------------------------------------------------------------------------------------------------------------------------
/*스토어 프로시저 완성~~~~~ 죽이는구먼~~~~~~ 내가 이런걸 할줄이야~~~*/
create proc orderbyemp
@empid int = 0,
@lastname nvarchar(40) = ''
as
declare @cnt int
if @empid != 0
begin
if (select count(*) from employees whereemployeeid=@empid)=0
begin
print '존재하지 않는 사원번호 입니다.'
end
else
begin
select orders.orderid as '주문번호',customers.companyname as '고객회사명',x.sumbyorder as '총주문액' from orders
inner join customers on orders.customerid = customers.customerid
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where orders.employeeid = @empid
set @cnt = @@rowcount
select employees.employeeid as '사원번호',employees.lastname,count(y.주문총액) as '주문횟수',sum(y.주문총액) as '당담자의 총주문액'
from employees
inner join (select orders.employeeid,x.sumbyorder as '주문총액' from orders
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where orders.employeeid = @empid) as y
on employees.employeeid = y.employeeid
group by employees.employeeid, employees.lastname
return @cnt
end
end
else
begin
if @lastname = ''
begin
print '사원번호 또는 이름을 입력해 주세요'
end
else
begin
if (select count(*) from employees wherelastname=@lastname)=0
begin
print '사원이 존재하지 않습니다.'
end
else if (select count(*) from employees wherelastname=@lastname)>1
begin
print '동명 2인이 존재 합니다.'
end
else
begin
select orders.orderid as '주문번호',customers.companyname as '고객회사명',x.sumbyorder as '총주문액' from orders
inner join customers on orders.customerid = customers.customerid
inner join employees on orders.employeeid = employees.employeeid
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where employees.lastname = @lastname
set @cnt = @@rowcount
select employees.employeeid as '사원번호', employees.lastname,
count(y.주문총액) as '주문횟수',sum(y.주문총액) as '당담자의 총주문액'
from employees
inner join (select orders.employeeid,x.sumbyorder as '주문총액' from orders
inner join employees on orders.employeeid = employees.employeeid
inner join (select orderid,sum(unitprice*quantity*(1-discount)) as sumbyorder
from [order details] group by orderid) as x
on orders.orderid = x.orderid
where employees.lastname = @lastname) as y
on employees.employeeid = y.employeeid
group by employees.employeeid, employees.lastname
return @cnt
end
end
end
---------------------------------------------------------------------------------------------------------------------------------------
orderbyemp 1
orderbyemp 10 --존재하지 않은 사원이라 나온다.
orderbyemp @lastname='king'
orderbyemp @lastname='kjo77' --존재하지 않은 사원이름이라 나온다.
declare @cnt int
set exec @cnt = orderbyemp 4
select @cnt as '반환되는 총 주문 횟수'
//프로시저 연습 2
/*인제 문제가 나간다.
아~~~ 진짜 어렵당 스토어 프로시져~~~ 진짜 어렵당~~~
northwind 사원별 실적조회 프로시저 작성
-- 사원의 이름 또는 번호를 통하여 실적을 조회한다.
-- 동명 2인이 존재할 경우 동명 2인이 존재한다는 메세지와 함께 종료
-- 해당 사원이 존재하지 않을 경우 해당사원이 존재하지 않는다는 메세지와 함께 종료
-- 리턴값으로 해당 사원의 총매출액을 반환한다.
-- 결과는 [사원번호] [lastname] [총 주문횟수] [총 주문에 의한 매출액] 과
[주문번호] [고객회사] [주문총액] 의 목록으로 표현된다.
*/
create proc orderby
@empid int = 0,
@lastname nvarchar(40)=''
as
if @empid != 0
begin
if (select count(*) from employees whereemployeeid=@empid)=0
begin
print '해당 사원번호가 없습니다.'
end
else
begin
print '어라~사원번호가 있넹!!!!'
end
end
else
begin
if @lastname=''
begin
print '사원번호나 이름을 입력하세요'
end
else if (select count(*) from employees wherelastname=@lastname)=0
begin
print '해당 사원이름이 없습니다.'
end
else if (select count(*) from employees wherelastname=@lastname)>1
begin
print '동명 2인이 존재합니다.'
end
else
begin
print 'OK'
end
end
select * from orders
--주문총액 뽑아내기~~~
select orders.orderid,sum(unitprice*quantity*(1-discount)) as '주문총액' from orders
inner join [order details] on orders.orderid = [order details].orderid
group by orders.orderid
--주문회사에 대한 총액 뽑아내기~~~~~~~~~~~`
select orders.orderid as '주문번호',customers.companyname as '주문회사',x.주문총액 as '주문총액'
from orders
inner join employees on employees.employeeid = orders.employeeid
inner join customers on orders.customerid = customers.customerid
inner join (select orders.orderid,sum(unitprice*quantity*(1-discount)) as '주문총액' from orders
inner join [order details] on orders.orderid = [order details].orderid
group by orders.orderid) as x on orders.orderid = x.orderid
where employees.lastname = 'king'
--사원의 대한 매출액 뽑아내기~~~~~
select employees.employeeid as '사원번호', employees.lastname,y.주문총액 as '주문총액'
from employees
inner join orders on employees.employeeid = orders.employeeid
inner join (select orders.orderid,sum(unitprice*quantity*(1-discount)) as '주문총액' from orders
inner join [order details] on orders.orderid = [order details].orderid
group by orders.orderid) as y on employees.employeeid = y.employeeid
where employees.lastname = 'king'
group by employees.employeeid,employees.lastname
orderby @lastname='kjo77'
댓글