--뷰(View)
--자주쓰는SELECT문을저장객체
--테이블과동일한사용법(단, SELECT 전용)
--1. 테이블에서일부컬럼만을자주볼떄..
--2.테이블에서일부레코드만자주볼때..
--3. 테이블끼리join 결과자주볼때..
--4. 가상컬럼도생성가능
USE pubs;
--도서테이블: 책가격을자주검색..(달러, 원화)
CREATE VIEW vwPrice
AS
SELECT title AS [책제목], price AS [현지가격], price * 1200 AS [판매가격] FROM titles;
--책가격검색: 뷰생성시활당한별칭은뷰의컬럼명이된다.!!
SELECT * FROM vwPrice;
SELECT 책제목, 현지가격, 판매가격 FROM vwPrice;
--프로그래밍
--SQL 언어
-- 1. 데이터질의목적(DDL, DML, DCL...SELECT/INSERT/UPDATE/DELETE)
-- 2. 프로그래밍구문(조건문, 반목문.. 변수생성..)
-- : 뷰나.. 프로시저..트리거를생성함사용
-- 변수생성(자료형변수명)
-- DECLARE @변수명타입:
DECLARE @num int;--0
DECLARE @x int, @y int;--0
DECLARE @x, @y int; --X
DECLARE @name nvarchar(3); --0
--블록을잡은값만생성
--값대입같이블록을잡고그변수를SET을시켜준다.
DECLARE @num int;
SET @num = 20;
--값출력
DECLARE @name varchar(15);
SET @name = '나는짱이다.';
SELECT @name
USE SQLEx
DECLARE @result nvarchar(20);
SELECT @result = name FROM tblGroup WHERE salary = 150 AND workMonth = 3;
SELECT @result + '입니다.';
DECLARE @max int;
SELECT @max = MAX(popu) FROM tblCountry;
SELECT name FROM tblCountry WHERE popu = @max;
-- SELECT 결과가복수일때..
DECLARE @result nvarchar(20);
SELECT name FROM tblGroup; --25명
--SELECT 결과가복수일때..
DECLARE @name varchar(10);
DECLARE @depart varchar(10);
SELECT TOP 1 @name = name, @depart = depart FROM tblGroup;
SELECT @name + '는' + @depart + '직원입니다.';
--조건문(IF)
DECLARE @num int = 10;
SET @num = 20;
IF @num > 0
BEGIN --블럭(영역)
SELECT '양수입니다.';
END
ELSE IF @num < 0
BEGIN
SELECT '음수입니다.';
END
ELSE
BEGIN
SELECT '0입니다.';
END
---방법
declare @popu int;
select @popu = popu from tblCountry where name = '대한민국';
if @popu > 4000
select '인구가많습니다.';
else
select '인구가적습니다.';
--방법
if (select popu from tblCountry where name = '미국') > 4000
select '인구가많습니다.';
else
select '인구가적습니다.';
--방법
declare @name nvarchar(10);
set @name = '미국';
if (select popu from tblCountry where name = @name) > 4000
select '인구가많습니다.';
else
select '인구가적습니다.';
--switch case문
DECLARE @score int;
DECLARE @result char(1);
SET @score = 85;
SET @result =
CASE
WHEN (@score >= 90) THEN 'A'
WHEN (@score >= 80) THEN 'B'
WHEN (@score >= 70) THEN 'C'
WHEN (@score >= 60) THEN 'D'
ELSE 'F'
END
SELECT '점수는' + @result + '입니다'
SELECT name,
CASE
WHEN (cont = 'EU') THEN '유럽'
WHEN (cont = 'AS') THEN '아시아'
WHEN (cont = 'SA') THEN '아메리카'
WHEN (cont = 'AF') THEN '아프리카'
WHEN (cont = 'AU') THEN '오스트레일리아'
ELSE '몰라'
END AS [대륙]
FROM tblCountry;
--반복문(1~100까지의합)
DECLARE @num int, @sum int;
SET @num = 1;
SET @sum = 0;
WHILE @num <= 10
BEGIN
SET @sum = @sum + @num;
SET @num = @num + 1;
END
--출력
SELECT @sum
-- 프로시저(Procedure)
-- exec 문
-- execute
-- 1. exec 프로시저= 실행(F5)과같은행동
-- 2. exec (동적SQL)
-- 문자열로되어있는쿼리를진짜쿼리로실행
DECLARE @sql varchar(100);
SET @sql = 'select * from tblGroup'
exec (@sql);
--테이블의이름을변수에넣어서쿼리를실행
--사용자에게입력받도록처리하여입력받은값에따라쿼리문을날릴수있다.
--DB 오브젝트는변수로대치할수없음!!!
-----------------------------------------------------------------------------
declare @table varchar(100); --테이블명저장용도
set @table = 'tblGroup';
select * from @table;
declare @table varchar(100);
set @table = 'tblCountry';
declare @sql varchar(100);
set @sql = 'select * from ' + @table;
exec (@sql);
-- 네이버까페
-- 신규까페신청
-- -> 그까페명으로전용테이블을생성(누가? 사람?)
DECLARE @sql varchar(1000);
DECLARE @name varchar(100); --coffee
SET @name = 'Coffee';
SET @sql = 'CREATE TABLE tbl'+@name+'
(
[set] int identity(1,1) not null primary key,
title varchar(1000) not null,
content varchar(3000) not null
)';
--EXEC (@sql);
SELECT (@sql);
SELECT * FROM tblcoffee
--아래쿼리문을문자열로만들어사용에게이름을받아서테이블을생성한다.
CREATE TABLE tblBoard
(
[set] int identity(1,1) not null primary key,
title varchar(1000) not null,
content varchar(3000) not null
)
--프로시저
-- : 함수, 메서드
-- : SQL의메서드
-- : 인자값, 반환값
-- : SQL의재사용이목적임******
-- 클래스(데이터+프로시저)
-- SQL서버에서SQL을실행시키는단계
-- (동일한구문을똑같이실행해도매번이단계들은실행됨)
-- 1. 명령어(쿼리) 텍스트(F5) -> 2. 구문분석(Parsing) -> 3. 개체확인-> 4. 권한확인-> 5. 최적화
-- 6. 실행계획(메모리저장) -> 7. 실행
-- 동일한텍스트(SQL)를프로시저로만들어서실행
-- 1. 명령어(프로시저) F5 실행-> 2. 실행계획(메모리저장) -> 3.실행
-- : 텍스트쿼리를실행하는것보다그텍스트쿼리를저장프로시저를만들어서실행하는것이더욱빠르다.!
-- VIEW 는테이블취급
-- PROC는메서드취급
--1. 프로시저선언(생성), tbl, vw, up
CREATE procedure upViewGroup
AS
SELECT * FROM tblGroup;
--2. 프로시저호출
--SELECT * FROM upViewGroup --X
--1)텍스트쿼리를실행해준다.
--2) exec upViewGroup;
execute upViewGroup;
--3. 프로시저삭제
DROP procedure upViewGroup;
--tblCountry의데이터를모두지우고다시초기화
CREATE proc upResettblCountry2
AS
BEGIN
--1. 데이터를모두삭제
DELETE FROM tblCountry;
--2. 다시데이터를삽입
INSERT INTO tblCountry VALUES ('대한민국','서울',4405,'AS',10);
INSERT INTO tblCountry VALUES ('중국','베이징',120660,'AS',959);
INSERT INTO tblCountry VALUES ('일본','도쿄',12461,'AS',37);
INSERT INTO tblCountry VALUES ('미국','워싱턴',24963,'SA',936);
INSERT INTO tblCountry VALUES ('영국','London',5741,'EU',24);
INSERT INTO tblCountry VALUES ('이집트','카이로',5969,'AF',99);
INSERT INTO tblCountry VALUES ('오스트레일리아','Canberra',1787,'AU',768);
INSERT INTO tblCountry VALUES ('칠레','산티아고',1339,'SA',75);
INSERT INTO tblCountry VALUES ('우루과이','몬테비디오',317,'SA',17);
INSERT INTO tblCountry VALUES ('아르헨티나','부에노스아이레스',3388,'SA',278);
INSERT INTO tblCountry VALUES ('인도네시아','자카르타',19134,'AS',191);
INSERT INTO tblCountry VALUES ('네덜란드','암스테르담',1476,'EU',4);
INSERT INTO tblCountry VALUES ('케냐','나이로비',NULL,'AF',58);
INSERT INTO tblCountry VALUES ('벨기에','브뤼셀',1012,'EU',3);
END;
UPDATE tblcountry SET capital = '서울';
SELECT * FROM tblCountry
--초기화
exeCUTE upResettblCountry;
--프로시저의인자
SELECT * FROM tblmember;
--특정회원의예치금이얼마입니까?
SELECT [money] FROM tblMember WHERE pk = 8;
--public void GetMoney(int pk)
CREATE proc upGetMoney
--인자리스트
@pk int --매개변수(가인자)
AS
--@pk 사용가능
SELECT [money] FROM tblMember
WHERE pk = @pk;
EXEC upGetMoney 3;
--특정회원에게예치금보너스를주고싶음+1000원
CREATE PROC upGainMoney
@pk int
AS
UPDATE tblMember SET
[money] = [money] + 1000
WHERE pk = @pk;
EXEC upGainMoney 3;
--특정회원에게예치금보너스를주고싶음+?원
CREATE PROC upGainMoney2
@pk int,
@money int
AS
UPDATE tblMember SET
[money] = [money] + @money
WHERE pk = @pk;
EXEC upGainMoney2 @pk=8, @money=10000;--1번형태
EXEC upGainMoney2 8, 10000;--2번형태
-- 프로시저반환값
-- 1. 출력파라미터: 1개이상의값을반환가능
-- 2. RETURN문: 메서드처럼반환하는값이있어야한다.
-- 비디오를대여하고아직반환을안한사람은몇명? - 프로시저
SELECT COUNT(*)
FROM tblRent
WHERE retDate is null;
CREATE PROC upGetCount
@count int output --반환값전용인자
AS
SELECT @count = COUNT(*) FROM tblRent
WHERE retDate is null;
-- 출력파라미터가있는프로시저를호출하는방법
DECLARE @num int;
EXEC upGetCount @num output;
SELECT @num;
--국가명을알려주면.. 수도와인구수를반환하는프로시져
CREATE PROC upGetInfo
@name nvarchar(20),
@capital nvarchar(20) output,
@popu int output
AS
SELECT @capital = capital, @popu = popu
FROM tblcountry
WHERE name = @name;
--'대한민국'의수도와인구수?
DECLARE @a nvarchar(20);
DECLARE @b int;
EXEC upGetinfo '대한민국', @a output, @b output;
SELECT @a, @b;
--RETURN은인트형만리턴이가능하다.
--국가명-> 수도
--1. tblCountry에서모든국가의인구수가+ 100 씩증가
--SELECT * FROM tblcountry
up1 30
CREATE PROC up1
AS
UPDATE tblcountry SET
[popu] = [popu] + 100
exec up1;
--2. tblCountry에서국가명과변경된수도를건네주면해당국가의수도를변경할수있는프로시저
SELECT * FROM tblcountry
CREATE PROC up2
@city varchar(20),
@input varchar(20)
AS
UPDATE tblcountry
SET capital = @input
WHERE name = @city
exec up2 '대한민국', '부산';
SELECT * FROM tblcountry
UPDATE
프로시져
select *
from tblcountry WHERE name = 인자 받은값
--3. tblCountry에서인구수가건네준인구수보다적으면국가자체를삭제
SELECT * FROM tblCountrY ORDER BY popu
CREATE PROC up3
@input int
AS
DELETE FROM tblcountry WHERE popu < @input
exec up3 8000;
--4. tblBoard1에서글번호를건네주면해당글의모든내용select
SELECT * FROM tblboard1
CREATE PROC up4
@seq int
AS
SELECT * FROM tblboard1 WHERE [seq] = @seq;
exec up4 2;
--5. tblGroup에서실적이가장많은직원은월급을+ 100만원
-- 실적이가장적은직원은월급을- 10만원
--(단, 실적에서null 제외)
CREATE PROC up5
AS
UPDATE tblgroup SET salary = salary + 100 WHERE achieve = (SELECT MAX(achieve) FROM tblgroup);
UPDATE tblgroup SET salary = salary - 10 WHERE achieve = (SELECT MIN(achieve) FROM tblgroup);
EXEC up5;
create table tblBoard
(
seq int identity(1,1) not null primary key,
name nvarchar(15) not null, --글쓴이
title nvarchar(100) not null, --제목
content nvarchar(1000) not null, --내용
readCount int default(0) not null, --조회수
pwd varchar(20) not null, --글비밀번호
regDate datetime default(getdate()) --글쓴시간
)
INSERT INTO tblboard VALUES ('김루피','나는짱','저는스타를제일잘합니다.',45, '12345', default);
INSERT INTO tblboard VALUES ('아이유','나는짱','저는스타를제일잘합니다.',72, '123456', default);
INSERT INTO tblboard VALUES ('홍길동','나는짱','저는스타를제일잘합니다.',45, '123457', default);
INSERT INTO tblboard VALUES ('김조던','나는짱','LOL 캐캐캐캐헤헤헤헤해캐해',45, '123457', default);
INSERT INTO tblboard VALUES ('김소영','나는짱','저는스타를제일잘합니다.',45, '123458', default);
INSERT INTO tblboard VALUES ('박민지','나는짱','저는스타를제일잘합니다.',45, '123459', default);
INSERT INTO tblboard VALUES ('박조로','나는짱','저는스타를제일잘합니다.',45, '1234510', default);
INSERT INTO tblboard VALUES ('이상디','나는짱','저는스타를제일잘합니다.',45, '1234511', default);
INSERT INTO tblboard VALUES ('해적왕','나는짱','저는스타를제일잘합니다.',45, '1234512', default);
SELECT * FROM tblboard
UPDATE tblBoard set content = '지는스타를잘못한대요'
WHERE name = '장예슬'
--6. 모든게시물가져오기
CREATE PROC up6
AS
SELECT *
FROM tblboard
exec up6;
--7. 1개의게시물삭제하기
CREATE PROC up7
@seq int
AS
DELETE FROM tblBoard
WHERE seq = @seq;
exec up7 5;
--8. 새로운글입력하기
CREATE PROC up8
@name varchar(20),
@title varchar(20),
@comment varchar(50),
@pwd varchar(20)
AS
INSERT INTO tblBoard VALUES(@name,@title,@comment,46,@pwd,default);
exec up8 '홍길동', '글제목','글내용', '111';
SELECT * FROM tblBoard
--9. 7번의업그레이드버전- 게시물삭제하기(비밀번호가일치하면삭제, 실패하면통과)
CREATE PROC up9
@pwd varchar(20)
AS
DELETE tblBoard WHERE pwd = @pwd;
exec up9 '1111';
--10번교수님이푼것
create proc upSearch
@column varchar(10),
@search varchar(50)
as
declare @sql varchar(500);
set @sql = 'select * from tblBoard where ' + @column + ' like ''%' + @search + '%''';
exec (@sql);
--11. 시간을입력하면최근해당시간내에등록된게시물가져오기
create proc up11
@time int
as
begin
select * from tblBoard where regDate between dateadd(hh,-@time,getdate()) and getdate();
end
exec up11 5; --5시간이내에등록된모든글select
-- ** 현재시간에서- 1시간
select regDate, dateadd(hh,-1,getdate()) from tblboard
select regDate, datediff(hh, regDate, getdate()) from tblBoard
--11번교수님이푼것
create proc upHourSearch
@hour int
as
select * from tblBoard where datediff(hh, regDate, getdate()) <= @hour;
-- @ 트랜잭션(Transaction)
-- : 논리적인하나의업무를담당하는SQL 작업집합
-- : 주로대상이되는쿼리는insert, update, delete..
--트랜잭션관리
-- : 논리적으로연결이된하나의업무내에서일부작업이실패를하면이전에성공한모든작업까지초기상태로되돌리는작업
-- 1. SQL 에서가능
-- 2. C# 에서도가능
select pk, name, [money] from tblMember where pk in (1, 2);
-- 1. 김유신이강감찬에게1,000원을빌려줌
-- a. 김유신의돈1,000원빼기(-)
-- b. 강감찬의돈1,000원더하기(+)
-- a. 강감찬의돈1,000원더하기(+)
-- b. 김유신의돈1,000원빼기(-)
update tblMember set [money] = [money] - 1000 where pk = 1;
update tblMember set [money] = [money] + 1000 where pk = 2;
-- 이후의모든작업은논리적으로하나의작업으로묶겠습니다.
-- 즉, 이후의모든작업은실제DB에적용이안되며, => 임시저장됨.
begin tran;
update tblMember set [money] = [money] - 1000 where pk = 1;
update tblMember set [money] = [money] + 1000 where pk = 2;
--트랜잭션처리이후에최종승인
-- 여태했던모든작업을승인!!
-- or 여태했던모든작업을취소
-- @ 다음두문장중하나를실행하여승인OR 모두취소
commit tran; --모두승인하겠다~!
rollback tran; --모두취소
-- 한사람이다른사람에게돈을빌려주는작업(1번회원이2번회원에게10,000 을빌려주겠습니다.)
create proc upSendMoney
@send int, --송금하는회원번호
@receieve int, --입금받는회원번호
@money int --금액
as
begin
--begin tran -- 트랜잭션시작
--1. 돈부터송금
update tblMember set [money] = [money] + @money where pk = @receieve;
--2. 보낸사람계좌에서돈을빼기
update tblMember set [money] = [money] - @money where pk = @send;
end
exec upSendMoney 1, 2, 10000
select * from tblMember
-- 보내는사람의돈이음수가되는경우를방지하기위하여조건을추가!!
create proc upSendMoney2
@send int, --송금하는회원번호
@receieve int, --입금받는회원번호
@money int --금액
as
begin
begin tran -- 트랜잭션시작
--1. 돈부터송금
update tblMember set [money] = [money] + @money where pk = @receieve;
--1.5 잔액이충분한지검사
declare @remain int;
select @remain = [money] from tblMember where pk = @send;
if @remain >= @money
begin
--2. 보낸사람계좌에서돈을빼기
update tblMember set [money] = [money] - @money where pk = @send;
--3. 트랜잭션완료(승인)
commit tran;
end
else
begin
--잔액이부족합니다..
--이전에보냈던1번작업까지도취소하여야한다.
rollback tran;
end
end
select * from tblMember
exec upSendMoney2 1, 2, 10000;
--DB모델링
-- : 데이터베이스만드는작업
-- : 테이블(컬럼) -> 관계
--DB모델링
-- : DB 만드는작업중DB 효율적으로구성하기위해서권장되는작업몇가지.. -> 데이터베이스정규화
--데이터베이스정규화
-- 1. 제1 정규화
-- : 필드에저장되는데이터는원자화시켜라!!!(단일화) 중복되는컬럼이없게만들어야한다.
-- 2. 제2 정규화
-- : 기본키를제외한나머지컬럼들의정보는종속적이어여한다. 한마디로이어져야한다.
-- : 기본키에종속적이어야항상그래야조인으로접근이가능하다.
-- 3. 제3 정규화
-- : 기본키가아닌모든컬럼끼리는서로종속적이지않아야함!!!
-- : 자꾸쪼갠다.
-- 4. 역정규화
-- : 다시올라가서쪼개놓은컬럼에오히려마이너스라면다시테이블을합친다.
-- 1. DB 생성-> 2. 제1정규화규칙에.. 맞게조정-> 3. 제2정규화규칙에... 조정-> 4. 제3 정규화규칙.. 조정-> [5. 역정규화] -> 완성
-- 하나의테이블에는하나의엔티티에관련된정보를저장
-- 엔티티: 사람-> 튜플, 레코드, 행
-- 엔티티의관련된정보: 이름, 키, 몸무게, 나이, 주소, 주민번호-> 속성, 컬럼, 필드
-- 테이블생성시식별자(***)
-- : 유일한컬럼
-- - 식별자로가능: 이름
-- - 식별자로불가능: 키, 몸무게, 나이, 주소
-- - 애매한거.. : 이름
-- 식별자(기본키)
-- : primary key
-- : 모든레코드간의유일한값
-- : null 허용이안되는값
-- 식별자(외래키)
-- : 참조키
-- 식별자(복합키)
CREATE TABLE TEST7
(
a int not null,
b int not null,
etc varchar(100) null,
constraint pk_tblTest7 primary key(a,b)--제약을걸겠다.
)
