VB , MS SQL 2005 연동하기

비주얼베이직 6.0 과 MS SQL 2005 연동하기

---

# 기본적으로 SQL SERVER2005에 로그인이 가능한 상태로 만들고,

# 새로운 계정을 하나 만들어 봅니다 (계정 예 ; testdb)

# 다음으로 권한을 설정하고 다시 접속합니다.

★그림 3번 소스 설명

USE testDb

CREATE TABLE userTbl -- 사용자테이블

( userID nchar(8) NOT NULL PRIMARY KEY, -- 사용자아이디

name nvarchar(10) NOT NULL, -- 이름

birthYear int NOT NULL, -- 출생년도

addr nchar(4) NOT NULL, -- 지역(경기,서울,경남,전북식으로글자만입력)

mobile1 nchar(3), -- 휴대폰의국번(011, 016, 017, 018, 019, 010 등)

mobile2 nchar(8), -- 휴대폰의나머지전화번호(하이픈제외)

height smallint -- 신장

)

GO

CREATE TABLE buyTbl -- 사용자구매테이블

( num int IDENTITY NOT NULL PRIMARY KEY, -- 순번(PK)

userid nchar(8) NOT NULL --아이디(FK)

FOREIGN KEY REFERENCES userTbl(userid),

prodName nchar(6) NOT NULL, -- 물품명

groupName nchar(4) , -- 분류

price int NOT NULL, -- 단가

amount smallint NOT NULL -- 수량

)

GO

★그림 4번 소스

INSERT INTO userTbl VALUES(N'PJS', N'박지성', 1983, N'서울', N'011', N'1111111', 181)

INSERT INTO userTbl VALUES(N'PJY', N'박주영', 1986, N'경기', N'011', N'2222222', 178)

INSERT INTO userTbl VALUES(N'JJJ', N'조재진', 1986, N'충북', N'019', N'3333333', 179)

INSERT INTO userTbl VALUES(N'LCS', N'김치우', 1983, N'인천', N'011', N'4444444', 179)

INSERT INTO userTbl VALUES(N'AJH', N'이운재', 1979, N'강원', NULL , NULL , 182)

INSERT INTO userTbl VALUES(N'KNI', N'김남일', 1977, N'경북', N'016', N'6666666', 183)

INSERT INTO userTbl VALUES(N'LYP', N'이영표', 1983, N'전북', NULL , NULL , 178)

INSERT INTO userTbl VALUES(N'SKH', N'설기현', 1978, N'서울', N'011', N'8888888', 182)

INSERT INTO userTbl VALUES(N'SJK', N'송종국', 1979, N'경기', N'018', N'9999999', 178)

INSERT INTO userTbl VALUES(N'CJC', N'김영광', 1975, N'제주', N'019', N'0000000', 185)

GO

INSERT INTO buyTbl VALUES(N'PJY', N'운동화', NULL , 30, 2)

INSERT INTO buyTbl VALUES(N'PJY', N'노트북', N'전자', 1000, 1)

INSERT INTO buyTbl VALUES(N'LCS', N'모니터', N'전자', 200, 1)

INSERT INTO buyTbl VALUES(N'CJC', N'모니터', N'전자', 200, 5)

INSERT INTO buyTbl VALUES(N'PJY', N'청바지', N'의류', 50, 3)

INSERT INTO buyTbl VALUES(N'CJC', N'메모리', N'전자', 80, 10)

INSERT INTO buyTbl VALUES(N'AJH', N'책' , N'서적', 15, 5)

INSERT INTO buyTbl VALUES(N'SKH', N'책' , N'서적', 15, 2)

INSERT INTO buyTbl VALUES(N'SKH', N'청바지', N'의류', 50, 1)

INSERT INTO buyTbl VALUES(N'CJC', N'운동화', NULL , 30, 2)

INSERT INTO buyTbl VALUES(N'SKH', N'책' , N'서적', 15, 1)

INSERT INTO buyTbl VALUES(N'CJC', N'운동화', NULL , 30, 2)

GO

# 위의 입력된 데이터를 조회하는 방법은 많이 존재하고 있는데, 아래의 소스를 보면 다양하게 조건문등을 이용하여, 조회할 수 있음을 알 수 있습니다.

(3)특정한조건의데이터만조회하는select ..from ...where...

SELECT * FROM userTbl

SELECT * FROM userTbl WHERE name = '박지성'

SELECT userID, Name FROM userTbl WHERE birthYear >= 1981 AND height >= 180

SELECT userID, Name FROM userTbl WHERE birthYear >= 1981 OR height >= 180

SELECT Name, height FROM userTbl WHERE height >= 180 AND height <= 183

SELECT Name, height FROM userTbl WHERE height BETWEEN 180 AND 183 // 위구문과동일하다.

SELECT Name, height FROM userTbl WHERE height=178 OR height=180 OR height=182

SELECT Name, height FROM userTbl WHERE height IN (178, 180, 182) // 위구문과동일하다.

SELECT height FROM userTbl WHERE Name = '박지성'

SELECT Name, height FROM userTBL WHERE height > 181

SELECT Name, height FROM userTbl

WHERE height > (SELECT height FROM userTbl WHERE Name = '박지성') //위두구문과동일하다.(하위쿼리)

SELECT Name, height FROM userTbl

WHERE height >= (SELECT height FROM userTbl WHERE mobile1 = '019') // 하위쿼리가두개이상인경우는문제가발생

//이럴때는아래처럼any를써야한다.

SELECT Name, height FROM userTbl

WHERE height >= ANY (SELECT height FROM userTbl WHERE mobile1 = '019') //179이상

SELECT Name, height FROM userTbl

WHERE height = ANY (SELECT height FROM userTbl WHERE mobile1 = '019') //height in (179,82)와동일하다.

SELECT Name, height FROM userTbl

WHERE height IN (SELECT height FROM userTbl WHERE mobile1 = '019') // 위아래구문은동일하다.

//179이상이고182이상이어야한다. 즉ALL은두가지결과값을모두만족해야한다.

SELECT Name, height FROM userTbl

WHERE height >= ALL (SELECT height FROM userTbl WHERE mobile1 = '019') //182이상

//정렬

SELECT Name, height FROM userTbl ORDER BY height // 기본적으로오름차순

SELECT Name, height FROM userTbl ORDER BY height DESC //내림차순

SELECT Name, height FROM userTbl ORDER BY height DESC, name ASC //키가같은경우에는이름은내림차순으로출력

//중복제거

SELECT addr FROM userTbl

SELECT addr FROM userTbl ORDER BY addr

SELECT DISTINCT addr FROM userTbl

//상위N개만출력

USE AdventureWorks

SELECT CreditCardID FROM Sales.CreditCard

WHERE CardType = 'Vista'

ORDER BY ExpYear, ExpMonth

SELECT TOP(10) CreditCardID FROM Sales.CreditCard

WHERE CardType = 'Vista'

ORDER BY ExpYear, ExpMonth

//1%만보기

SELECT TOP(SELECT COUNT(*)/100 FROM Sales.CreditCard ) CreditCardID

FROM Sales.CreditCard

WHERE CardType = 'Vista'

ORDER BY ExpYear, ExpMonth

//0.1%만보기

SELECT TOP(0.1)PERCENT CreditCardID FROM Sales.CreditCard

WHERE CardType = 'Vista'

ORDER BY ExpYear, ExpMonth

//퍼센트계산에의한중복발생시중복허용- 장학생선발시

SELECT TOP(0.1)PERCENT WITH TIES CreditCardID, ExpMonth, ExpYear FROM Sales.CreditCard

WHERE CardType = 'Vista'

ORDER BY ExpYear, ExpMonth

//일정샘플추출- 대용량에서의미가있다.

USE AdventureWorks

SELECT * FROM Sales.SalesOrderDetail TABLESAMPLE(5 PERCENT) //페이지단위의5퍼센트

//조회시수가일정하지않다.

SELECT TOP(5000) * FROM Sales.SalesOrderDetail TABLESAMPLE(5 PERCENT) //일정수만뽑기

//기존의테이블을복사해서새로운테이블생성

USE testDB

SELECT * INTO buyTbl2 FROM buyTbl

SELECT * FROM buyTbl2

SELECT userid, prodName INTO buyTbl3 FROM buyTbl

SELECT * FROM buyTbl3

(4)group by 및having 그리고집계함수

집계함수- 각각인자는없다.

avg(평균), min(최소값) , max(최대값) , count(행의개수),

count_max(int형을초과하는행의개수도셀수있다. 결과값은bigint형도가능하다)

stdev(표준편차)

[with <common_table_expression>]

select select_list [ into new_table ]

[from table_source]

[where search_condition]

[group by group_by_expression]

[having search_expression]

[order by order_expression [asc | desc] ] //세가지의순서가중요하다.

USE testDB

SELECT userid, price, amount FROM buyTbl ORDER BY userid

//그룹으로묶기

SELECT userid, SUM(amount) FROM buyTbl GROUP BY userid //사용자별구매수

SELECT userid AS [사용자아이디], SUM(amount) AS [총구매개수]

FROM buyTbl GROUP BY userid

SELECT userid AS [사용자아이디], SUM(price*amount) AS [총구매액] //사용자별구매액

FROM buyTbl GROUP BY userid

//평균

SELECT AVG(amount) AS [평균구매개수] FROM buyTbl //정수형의평균이므로반올림때문에잘못나올수있다.

SELECT AVG(amount*1.0) AS [평균구매개수] FROM buyTbl // 실수형으로변환해서구해라

-- 또는

SELECT AVG(CAST(amount AS DECIMAL(10,6))) AS [평균구매개수] FROM buyTbl //실수형으로변환

SELECT userid, AVG(amount*1.0) AS [평균구매개수] FROM buyTbl

GROUP BY userid

//최대,최소

SELECT Name, MAX(height), MIN(height) FROM userTbl //문제가있는쿼리- 쿼리가실패한다.

SELECT Name, MAX(height), MIN(height) FROM userTbl GROUP BY Name //문제가있는쿼리

SELECT Name, height

FROM userTbl

WHERE height = (SELECT MAX(height)FROM userTbl)

OR height = (SELECT MIN(height)FROM userTbl)

SELECT COUNT(*) FROM userTbl

SELECT COUNT(mobile1) FROM userTbl //휴대폰이있는사용자의수

//집계함수와일반SQL의성능비교

-프로파일러실행 - 새추적- 실행

(5)having절

SELECT userid AS [사용자], SUM(price*amount) AS [총구매액]

FROM buyTbl

GROUP BY userid

SELECT userid AS [사용자], SUM(price*amount) AS [총구매액] //이구문은실행되지않는다.

FROM buyTbl

WHERE SUM(price*amount) > 1000

GROUP BY userid

SELECT userid AS [사용자], SUM(price*amount) AS [총구매액]

FROM buyTbl

GROUP BY userid

HAVING SUM(price*amount) > 1000 //합계가1000이상인사람별로그룹을묶는다.(순서가중요하다)

//내림차순으로

SELECT userid AS [사용자], SUM(price*amount) AS [총구매액]

FROM buyTbl

GROUP BY userid

HAVING SUM(price*amount) > 1000

ORDER BY SUM(price*amount) ASC

(6)결과의요약

compute - 전체를나열한후에, 그집계결과를출력

compute by - 각소그룹단위로집계함수를사용

rollup/grouping()/cube - 총합또는중간합계가필요시에

단점-체계가흐트러진다. rollup으로보완가능하다.

SELECT *, price*amount AS [가격] FROM buyTbl

COMPUTE SUM(price * amount)

COMPUTE AVG(price * amount)

SELECT *, price * amount FROM buyTbl

ORDER BY groupName

COMPUTE SUM(price * amount) BY groupName

COMPUTE AVG(price * amount) BY groupName

SELECT *, price * amount FROM buyTbl

ORDER BY groupName

COMPUTE SUM(price * amount) BY groupName

COMPUTE AVG(price * amount) BY groupName

COMPUTE SUM(price * amount)

COMPUTE AVG(price * amount)

SELECT num, groupName, SUM(price * amount) AS [비용]

FROM buyTbl

GROUP BY groupName, num

WITH ROLLUP //각각,소합,총합(하나의테이블로나온다)

SELECT groupName, SUM(price * amount) AS [비용]

FROM buyTbl

GROUP BY groupName

WITH ROLLUP

SELECT groupName, SUM(price * amount) AS [비용]

, GROUPING(groupName) AS [추가행여부]

FROM buyTbl

GROUP BY groupName

WITH ROLLUP

//cube는다차원으로조절이가능하다.

# 물론, ORACLE 이나, 여타 다른 데이터베이스 관리 시스템에 대해 공부를 했다면, 이 정도 쯤은 ㅡ,.ㅡ 웃으며 넘어갈 수 있겠지요..........

# 프로시저 만들기

USE testDb

GO

CREATE PROCEDURE pro입고

@년월일nchar(10),

@상품코드int,

@수량int

AS

BEGIN

BEGIN TRAN

INSERT INTO 입고

VALUES (@년월일, @상품코드, @수량);

IF(@@eRROR <> 0) ROLLBACK TRAN ELSE COMMIT TRAN

END

# 프로시저

USE testdb

go

CREATE TRIGGER Tr입고on 입고

FOR INSERT

AS

SET NOCOUNT ON

DECLARE

@년월일 NCHAR(10),

@상품코드 INT,

@수량 INT

SET @년월일= ''

SET @상품코드= 0

SET @수량= 0

SELECT @년월일= 년월일, @상품코드=상품코드, @수량=수량

FROM inserted

INSERT INTO 입출고현황values(@년월일, '입고', @상품코드,@수량)

go