'프로그래밍/MS - SQL'에 해당되는 글 24건

제목 날짜
  • 윈도우8, 윈도우10 Sql Server 구성 관리자(Sql Server Configuration Manager) 실행방법 2017.02.08
  • Microsoft SQL server 오류 53 해결 방법(2) 2017.01.27
  • 엑셀 가져오기 외부 테이블 형식이 잘못되었습니다. (Microsoft JET Database Engine) 2016.10.06
  • 엑셀 파일 데이타베이스 업로드 내보내기 2016.04.10
  • MS SQL SERVER 2008 데이타베이스 백업 및 복원 하기(2) 2016.03.31
  • 회원 정보를 저장하는 테이블을 만드세요. 2015.12.03
  • 테이블 명으로 SP나 해당테이블 사용 여부 확인 2013.07.25
  • 뷰(View), 프로시져(PROC), 트랜잭션, DB모델링 2012.04.03
  • 릴레이션 외래키(foreign), 조인(Join), 유니온(union), VIEW 2012.04.02
  • MS SQL 쿼리문 모음 2012.04.02

윈도우8, 윈도우10 Sql Server 구성 관리자(Sql Server Configuration Manager) 실행방법

윈도우8, 윈도우10 Sql Server 구성 관리자(Sql Server Configuration Manager) 실행방법

MS SQL SERVER를 사용하면서 SQL Server 구성 관리자(SQL Server Configuration Manager)에 접속할 일이 종종 있습니다. 현재 서버에서 돌아가고 있는 윈도우 서버는 대부분 SQL server 구성 관리자를 쉽게 찾을 수 있습니다. 

하지만 테스트용으로 집에서 자신의 PC에서 돌려보는 경우가 많을 텐데 쉽게 검색만으로는 SQL Server 구성 관리자를 찾을 수가 없었습니다. Sql Server 구성 관리자를 윈도우8 이상 운영체제에서 실행하기 위해서는 다른 방법으로 접근해야 됩니다. 

윈도우8이나 윈도우10에서 MS SQL SERVER를 설치하고 SQL Server 구성 관리자에 접근이 안 되시는 아래 방법으로 접근해 보시기 바랍니다. 아래 이미지에 보시다시피 MSDN 사이트에서 지침을 제공하고 있습니다. (https://msdn.microsoft.com/ko-kr/library/ms174212) 

아래 이미지에 보시다시피 SQL Server 각각의 버전별로 SQL Server 구성 관리자를 실행하는 방법에 대해서 표시되어 있습니다. SQL SERVER 버전 확인만 하면 간단히 SQL Server 구성 관리자를 실행할 수 있을듯합니다. 

아래 표시된 이미지와 같이 경로에 직접 접근하여 실행하셔도 되고 간단히 윈도우 실행 창이나 윈도우 검색을 이용해서 실행 할 수도 있습니다. 
SQL Server 2016 - SQLServerManager13.msc 
SQL Server 2014 - SQLServerManager12.msc 
SQL Server 2012 - SQLServerManager11.msc 
SQL Server 2008 - SQLServerManager10.msc 



먼저 자신의 SQL SERVER 버전을 확인해 보도록 합시다. 사실 접속 시에 버전이 나오는데 연결 이후에도 MS SQL SERVER 버전을 확인할 수 있습니다. 도움말 메뉴를 누르시고 정보 메뉴를 누르시면 됩니다.



제가 사용하고 있는 SQL Server 버전은 2012 버전이네요. 그럼 msdn 홈페이지에서 나온대로 확인해 보면 sqlservermanager11.msc를 실행하면 Sql Server 구성 관리자를 실행할수 있다는 얘기 입니다.



윈도우10 환경에서는 간단히 돋보기 모양의 버튼을 눌러서 검색 부분에 sqlservermanager11.msc를 검색하시면 SQL Server 구성 관리자를 실행할 수 있는 실행 파일이 나옵니다.



다른 방법으로는 윈도우키 + R 버튼을 눌러서 실행 창에서 sqlservermanager11.msc를 넣고 확인을 눌러 주시면 됩니다.



아래 이미지와 같이 Sql Server Configuration Manager에 접근하였습니다. 이제 SQL SERVER와 관련된 서비스 실행 및 관리를 할수 있겠네요.



포트를 변경할 일이 있어서 구성 관리자를 접근 하고 싶었는데 이제서야 접근해서 수정이 가능할 듯 합니다.



윈도우8, 윈도우10에서 MS SQL SERVER를 사용하시면서 Sql Server 구성 관리자에 접근이 안 되시는 분은 이글을 참고하시면 될듯 합니다.


저작자표시비영리변경금지

트랙백

※ 스팸 트랙백 차단중 ...{ ? }

Microsoft SQL server 오류 53 해결 방법

Microsoft SQL server 오류 53 해결 방법 

얼마 전에 개인적으로 즐기는 로또 코드를 손을 보기 위해 취미 삼아 코드를 만지러 MSSQL server에 접속하려 했습니다. 시스템을 변경한것도 없다고 판단 했고 기존에 잘사용 했기 때문에 문제 될게 없다고 생각하고 SQL SERVER에 접속을 시도 했습니다.


그런데 연결 도중 SQL Server에 연결을 설정하는 중에 네트워크 관련 또는 인스턴스 관련 오류가 발생했습니다. 서버를 찾을 수 없거나 접근할 수 없습니다. 인스턴스 이름이 올바르고 SQL Server에 대한 연결을 열 수 없습니다) (Microsoft SQL Server, 오류:53) 네트워크 경로를 찾지 못했습니다.'라고 에러가 나더군요. 


간단히 검색으로 문제점을 간단히 해결할 수 있으리라 판단하고 Microsoft SQL Server, 오류:53이라는 부분으로 검색을 하고 정보를 찾아보는데 어떤 문제 때문인지 정확히 찾아볼 수가 없습니다. 


간단히 해결할 수 있었던 문제였는데 제가 괜히 다른 문제가 있는지 생각이 들어서 조금 헤맸던 것 같습니다. 기본 포트를 변경한 적이 있었나? 아니면 현재 SQL server가 실행 중이지 않는가? 이런 문제점들을 확인 하다 보니 정확히 문제를 해결할 수 없었습니다. 


아래 이미지에 표시된 컴퓨터 이름을 확인해 보시기 바랍니다. 문제 해결에 중요한 요소입니다.



고민을 하던 중에 msdn에 나온 오류 안내 웹페이지를 유심히 확인하였습니다. 컴퓨터는 거짓말을 하지 않고 해당 오류에 대한 문제점에 대해서 친절하게 설명해주고 해결 방법에 대해 제시하고 있습니다. (https://msdn.microsoft.com/ko-kr/library/bb326277(v=sql.120).aspx)


설명을 보니 서버 이름을 확인하라는 문구에 눈이 갔습니다. 글을 보고 곰곰이 생각해 보았습니다.



아래 이미지에 보시다 MSSQL server 설치 시에는 컴퓨터 이름을 변경하지 않고 기존에 사용한 컴퓨터 이름 그대로 설치를 했기 때문에 DESK TOP-OG4OFFO 이런 식으로 서버 이름을 인증 했었습니다.



생각해 보니 몇 달 전에 컴퓨터 이름을 변경한 기억이 났습니다. 제가 겪은 해당 오류의 문제는 이부분이었습니다. 컴퓨터 이름을 바꿨기 때문에 아무리 인증을 해도 서버 이름을 못 찾는다고 에러를 내는 것이었습니다. 


기존에 서버에 컴퓨터 이름을 변경할 일도 없을 테고 서버 컴퓨터 이름 변경하는 작업이 대부분 없기 때문에 이부분을 관과하고 지나칠수 있다는 생각에 다른 분들도 혹시나 해당 오류가 나시면 이부분을 한번 확인해 보시기 바랍니다. 컴퓨터 이름을 윈도우 버전별로 확인하는 방법은 (http://zzarungna.tistory.com/671) 이곳을 참고 하시면 됩니다.



컴퓨터 이름을 확인 했으니 다시 로그인 계정과 암호를 그대로 놓고 현재 컴퓨터 이름인 zzarungna로 서버 이름을 넣고 연결을 눌러 보도록 하겠습니다.



정상적으로 연결이 되네요.



MS SQL SERVER 접속시 오류 53을 만나시면 서버 이름을 한번 꼭 체크해 보시기 바랍니다.

저작자표시비영리변경금지
  • j
    2018.11.04 16:02

    겨우 설치했는데 로그인부터 막혀서 좌절하던 중, 포스팅 해주신 글 덕분에 무사히 성공했습니다ㅜㅜ 정말 감사해요!

    • Favicon of https://zzarungna.com BlogIcon zzarungna
      2018.11.04 18:54 신고

      도움이 되는 정보가 되었다니 다행입니다! :)

트랙백

※ 스팸 트랙백 차단중 ...{ ? }

엑셀 가져오기 외부 테이블 형식이 잘못되었습니다. (Microsoft JET Database Engine)

엑셀 가져오기 외부 테이블 형식이 잘못되었습니다. (Microsoft JET Database Engine)

MS-SQL을 통해 엑셀 데이터를 가져오거나 내보낼수 있습니다. 엑셀 데이터를 가져오는 도중 제목과 같이 엑셀 가져오기 외부 테이블 형식이 잘못되었습니다. (Microsoft JET Database Engine) 라고 에러가 나는 경우가 있습니다.

대부분 이런 오류는 엑셀 데이터를 인터넷에서 받았거나 외부에서 다운로드 받은 엑셀 파일인 경우 대부분 이런 오류가 나옵니다. 



먼저 데이터 원본에서 문제가 되는 엑셀 파일을 열어 보도록 합시다.



아래 이미지와 같이 파일이 손상되었거나 안전하지 않을 수 있다고 나옵니다. 예를 눌러서 엑셀 프로그램에서 문제가 되는 해당 엑셀 파일을 열어 주시면 됩니다.



다른 작업을 하지 않고 엑셀 프로그램에서 파일을 다시 저장하는 것만으로도 오류는 쉽게 해결이 됩니다. 호환이 좋은 Excel 97 - 2003 통합 문서의 형태로 엑셀 파일을 새로 저장하시면 됩니다.



저장이 완료된 엑셀 파일을 다시 MS-SQL 데이타베이스에서 데이터 가져오기를 실행하여 엑셀 파일을 불러 오면 해당 오류는 없어지고 정상적으로 데이터를 가져오게 됩니다.


원인은 정확히 알아 낼수 없지만 역시나 외부 또는 다른곳에서 받은 엑셀 파일일 변환되어 MS-SQL과 호환이 되지 않을떄 위와 같은 현상이 일어나지 않을까 생각이 됩니다. 소스 수정 없이 간단히 엑셀 파일을 다시 다른 이름으로 저장하는 것만으로 오류를 해결할수 있습니다.

저작자표시비영리변경금지

트랙백

※ 스팸 트랙백 차단중 ...{ ? }

엑셀 파일 데이타베이스 업로드 내보내기

엑셀 파일 데이타베이스 업로드 내보내기

엑셀 파일 데이타 베이스 업로드
mssql server 2008을 이용해 엑셀파일을 db에 업로드하고 내보내기 기능.

먼저 TEST용으로 엑셀 파일을 하나 만들어서 데이타베이스에 엑셀파일을 자료를 올려 봅시다.
아래 이미지처럼 엑셀파일을 테스트 용으로 하나 만들었습니다.




그리고 다음처럼 데이타 베이스에 접속해서 test용 데이타베이스를 하나 만들겠습니다.

CREATE DATABASE test;



만든 데이타베이스 오른쪽 버튼을 눌러 테스크 -> 데이터 가져오기를 누릅니다.



그럼 다음과 같이 화면이 나옵니다. 다음버튼을 눌러 진행.




그러면 다음과 같이 가져올 데이터를 선택 하라고 합니다. 엑셀을 선택합니다.



그다음 엑셀파일경로에서 찾아보기 버튼을 눌러 테스트용으로 만든 엑셀 파일을 선택 합시다. 현재 엑셀 버전은 2007까지 지원 하네요.




그다음은 복사할 위치를 지정 하라고 하네요.

로컬에서 작업 할때는 Window인증 으로 사용하고 Sql Server 인증 사용할때는 데이타베이스를 설치할때 넣은 계정과 비번을 넣습니다.

그다음 아까 test용으로 만들었던 데이타베이스 test를 선택 합니다.



그런 다음 대상에 생성된 [dbo].[Sheet1$]를 선택 합니다.

아래의 의미는 엑셀 파일은 기본적으로 파일을 생성하면 Sheet가 3개가 있죠.

아까 테스트용으로 작성할때 엑셀파일의 첫번째 시트에서만 데이타가 있기 때문에 첫번째 sheet에 값만 체크해서 가져오면 됩니다.

만약 sheet2,shee3에도 데이터가 있다면 모두다 체크하고 다음 버튼을 누르시면 됩니다.



제가 설정한 내용들이 나오고 마침을 누르면 엑셀 파일을 db로 가져옵니다.


작업이 진행 되는중입니다.




최종적으로 데이터가 잘들어왔는지 확인 합시다. 
test테이블에 보면 sheet1$이라는 이름으로 테이블이 생성 되었습니다. 

use test;

select * from Sheet1$;

위 쿼리문으로 확인해보니 데이터가 잘들어온것을 확인할수 있습니다.




테이블 이름을 좀바꿔서 마무리를 하도록 하겠습니다.

테이블 이름을 바꾸는 쿼리문은  SP_RENAME '바꾸기전 테이블이름', '적용할 테이블 이름'

SP_RENAME 'Sheet1$', 'buymember'

 

SELECT * FROM buymember;

 





데이타베이스 엑셀파일 내보내기
데이타베이스에서 엑셀파일로 내보내기도 가져오기랑 별차이가 없습니다.

내보넬 테이블이 포함된 데이타베이스를 선택 한뒤 태스크 -> 데이터 내보내기를 누릅니다.




다음 버튼을 누릅니다.



그다음 데이터베이스에서 내보낼 데이타베이스가 선택이 되었는지 확인합니다.



그다음 대상 메뉴를 눌러 Microsoft Excel 메뉴를 고릅니다.

엑셀 파일 저장경로는 찾아보기를 누른뒤에 폴더를 선택 합니다.



다음 처럼 전송 데이터를 쿼리를 작성해도 되지만 첫번째 메뉴를 선택해서 진행 하겠습니다.



그러면 내보낼 테이블을 선택 합니다. 아까 엑셀 가져오기에서 만든 buymember 테이블을 선택하겠습니다.



다음 버튼을 눌러 줍시다.


다음을 누르시고요.



마침을 누릅니다.


완료 버튼을 누르면 됩니다.



그럼 지정한 폴더에 test 엑셀파일이 생성된걸 확인 할수 있습니다.





최종적으로 데이타 베이스에 있는 내용이 엑셀 파일에 옮겨진것을 확인 할수 있습니다.



엑셀 파일을 데이타 베이스에 업로드 하는 방식은 위와 같이 사용 하시면 됩니다.

저작자표시비영리변경금지

트랙백

※ 스팸 트랙백 차단중 ...{ ? }

MS SQL SERVER 2008 데이타베이스 백업 및 복원 하기

MS-SQL SERVER 2008 데이타베이스 백업 하기

데이타베이스에 물리적인 하드가 손상되거나 문제가 있을때를 대비하여 데이타베이스를 백업 합니다.

업무를 할땐 일일 업무에 포함하여 수작업으로 DB를 백업해도 되고 다른 물리적 장치에 스케쥴링을 통해서도 데이타베이스를 백업해 놓습니다.


우선 수작업으로 데이타베이스 백업하는 방법에 대해서 하도록 하겠습니다.


지금은 현재 컴퓨터 하드에서 처리 하지만 실제로는 다른 서버 하드에서 처리 해야 합니다.


우선 원하는 경로 폴더 위치는 어디든 상관 없습니다. 아래처럼 전 C드라이브 밑에 백업 DB를 만들었습니다.




--백업을 위해 테스트용 DB를 하나 만들었습니다.

CREATE DATABASE TEST;

USE TEST;

CREATE TABLE backupTable(    

        number int not null,

        name varchar(50),

        age int not null,

        sex varchar(2),

        email varchar(50)

);

 

--데이터도 한5개만 넣고요

INSERT INTO backupTable values(1, '테스트', 48,'남', 'testbackup@gmail.com')

INSERT INTO backupTable values(2, '이미연', 27,'여', 'aaaaa@gmail.com')

INSERT INTO backupTable values(3, '최소미', 34,'여', 'bbbbb@gmail.com')

INSERT INTO backupTable values(4, '고소미', 22,'여', 'ccccc@gmail.com')

INSERT INTO backupTable values(5, '아이유', 38,'여', 'dddddd@gmail.com')

 

--확인한번 하고

SELECT TOP 10 *

FROM backupTable;




자그럼 백업을 시작 합시다. 아래 화면에 백업할 데이타 베이스를 선택한후 테스크 -> 백업 버튼을 누릅니다.



다음과 같은 창이 열리면 기존 백업할 위치를 제거 하고 아까 만든 백업 DB폴더를 지정합니다. 제거해도 되고 추가 해도 상관 없습니다. 그다음 백업할 위치를 지정하기 위해 추가 버튼을 누릅니다.



추가버튼을 누르면 다음과 같은 화면이 뜹니다 아까 제가 지정해논 C: 백업DB 폴더 생성한곳에 지정하고 파일이름은 날짜와 시간을 기재하여 저장 합니다.(실제 백업시엔 물리적인 디스크 결함이 있을수 있으므로 다른 백업 서버에 저장하는게 보통입니다.)


확인버튼을 누르면


백업이 완료되었다는 문구가 뜹니다.


그러면 다음과 같이 백업 파일이 생성된걸 확인할수 있습니다.

백업 완료!





MS-SQL SERVER 2008 데이타 베이스 복원 하기

이번엔 데이타 베이스를 복원 하도록 하겠습니다.

우선 데이베이스를 복원하기 위해 테이블을 날려보겠습니다. DELETE 문을 날리고 SELECT 해보면 데이터가 날라간것을 확인 할수 있습니다.

--DB 복원을 위해 실수로 테이블을 날렸다고 생각합시다.

DELETE FROM backupTable;

 

SELECT TOP 10 *

FROM backupTable;



그다음 복원을 위해 아래 이미지처럼 메뉴를 선택해 주세요.



그러면 백업할당시에 복원 지점이 있습니다. 여러개를 동시에 백업을 했다면 최신 데이타를 기준으로 체크를 한후 복원을 하시면 됩니다.



복원이 완료되었습니다.




잘복구가 되었는지 SELECT문을 날려 봅시다. 마지막으로 확인해 볼까요.

잘 복구가 되었습니다. 만약 현업에서 이런 일이 일어나서 DB를 날렸다가 복구 했다면 등에 식은땀이 나면서 환호를 지를지도 모르겠습니다.





마지막으로 아래와 같이 에러가 나면서 복구가 안되는 분들은


데이터베이스 을(를) 복원하지 못했습니다.

System.Data.SqlClient.SqlError. 데이타베이스가 사용 중이어서 배타적으로 엑세스할 수없습니다.


쿼리 편집기 창을 종료 하시고 웹페지 또는 프로그램에 계속 쿼리문이 연결이 되있는 상황이라면 DB를 종료 하시고 연결된 쿼리도 접속을 끊으신 뒤에 복구를 진행해 보시기 바랍니다.

저작자표시
  • Favicon of http://blog.naver.com/shegirl1004?41748 BlogIcon 겨울비
    2016.06.11 10:22

    좋은 정보 잘보고 갑니다

    • Favicon of https://zzarungna.com BlogIcon zzarungna
      2016.06.15 15:26 신고

      네~

트랙백

※ 스팸 트랙백 차단중 ...{ ? }

회원 정보를 저장하는 테이블을 만드세요.

회원 정보 테이블 예제

사용환경 : ms sql



번호, 이름, 나이, 성별, 이메일, 핸드폰번호, 등록날짜 넣을수 있는 테이블을 만드세요.

use test;

create table member(

        no int identity(1,1) not null,

        name varchar(20) not null,

        age int not null,

        sex varchar(4) not null,

        email varchar(100) not null,

        handphone varchar(50) not null,

        regdate date default getdate()

);

 

만든테이블을 기준으로 회원 정보를 한개 인서트 하기.

insert into member (name, age,sex, email,handphone) values 

('김일등', '31', '남자', 'kimildung@gmail.com', '010-1234-4567');

 

회원 정보중 나이, 이름, 성별 수정해 보기.

update member set age = '30', name = '김일동', sex = '여자' where no = 1;

 

회원 정보 지워 보기.

delete from member where no = 1;

 

회원 테이블 날려보기

drop table member;

저작자표시

트랙백

※ 스팸 트랙백 차단중 ...{ ? }

테이블 명으로 SP나 해당테이블 사용 여부 확인

테이블 명으로 SP나 해당테이블 사용 여부 확인

--SP중에해당테이블이름으로사용중인것찾을때

SELECT * FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%테이블이름%'

 

--테이블이름으로

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME LIKE '%테이블이름%'

저작자표시

트랙백

※ 스팸 트랙백 차단중 ...{ ? }

뷰(View), 프로시져(PROC), 트랜잭션, DB모델링

--뷰(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)--제약을걸겠다.

)

 

 


저작자표시

트랙백

※ 스팸 트랙백 차단중 ...{ ? }

릴레이션 외래키(foreign), 조인(Join), 유니온(union), VIEW


릴레이션이 이루어지는 컬럼은 형식이 같아야 한다.

테이블 구분은 중복되는 데이터가 있는지 없는지를 보고 테이블이 중복된다면 부 데이터이고 테이블이 중복되지 않는것이 주데이터가 된다.










----------------------------2012. 04. 02-1교시-----------------------------------

 

-- tblStaff, tblProject

-- tblMember, tblGenre, tblVideo, tblRent

 

select * from tblStaff; -- 직원정보

select * from tblProject; -- 프로젝트(담당자- tblStaff의직원)

 

 

use sqlex;

--1. 신입사원입사

insert into tblStaff (name, salary, addr)

       values ('홍길동',200,'서울시')

      

--2. 김기문사원퇴사(문제있음!!)

-- : 담당중이었던tblProject 에서담당자유령~

-- : 사전에프로젝트를위임하거나프로젝트를삭제를한뒤..퇴사

-- 해당스탭테이블에서는명단이삭제되면,

-- 프로젝트테이블에맡고있던테이블이존재해선안된다.

delete from tblStaff where name = '김기문'; --김기문140 서울시

 

--s-1

--s-5

 

--3.김수동사원퇴사(정상적인퇴사)

--3.1 김수동이담당하던프로젝트를아무개에게위임

update tblProject set

       staff = '김기문'

             where staff = '김수동';

            

--3.2 김수동퇴사

delete from tblStaff where name = '김수동';

 

--4. 신규프로젝트발주(정상)

insert into tblProject (prjName, staff)

       values ('고객센터설립','김기문');

      

--5. 신규프로젝트발주(존재하지않는직원이담당자로배임..문제있음)

insert into tblProject (prjName, staff)

       values ('주차장유료화','하하하');

--s-6

      

--문제점: 프로젝트담당자는반드시tblStaff에존재하는인물이어야한다.

 

-- MS-SQL : 관계형데이터베이스(Relation Data Base)

--관계(Relation)

-- : 테이블과테이블간의관계(컬럼과컬럼의관계)

-- : 데이터무결성을유지하기위해..

 

drop table tblStaff;

drop table tblProject;

 

create table tblStaff

(

       name nvarchar(10) not null primary key, --직원명

       salary int not null, --월급

       addr nvarchar(20) not null -- 주소

)

 

create table tblProject

(

       prjID int identity(1,1) not null primary key, -- 프로젝트번호

       prjName nvarchar(30) not null, --프로젝트명

       staff nvarchar(10) not null --담당직원명

             references tblStaff(name) -- 외래키(Foreign Key)가참조하는것은, 반드시PK로지정되어있어야한다.

)

 

drop table tblMember;

drop table tblGenre;

drop table tblVideo;

drop table tblRent;

 

---비디오대여점

---회원테이블

create table tblMember

(

       pk int identity(1,1) not null primary key,-- 회원번호

       name nchar(5) not null, -- 회원명

       grade int not null, --회원등급(1=준, 2=정, 3=우수)

       bYear int not null, --생년(성인)

       tel varchar(15) not null, --연락처

       addr nvarchar(50) null, -- 주소

       [money] int not null -- 예시금

)

--비디오장르테이블(장르별대여가격이다름)

create table tblGenre

(

       name nchar(5) not null primary key, --장르명

       price int not null, --대여가격

       period int not null -- 대영기간

      

)

 

--비디오테이블(상품)

create table tblVideo

(

       pk int identity(1,1) not null primary key, -- 테잎번호

       name nvarchar(30) not null, --제목

       num int not null, -- 보유갯수

       company nvarchar(20) null, --제작사

       director nvarchar(20) null, --감독

       major nvarchar(20) null, -- 주연배우

       genre nchar(5) not null references tblGenre(name) --장르

            

)

 

-- 대여테이블

create table tblRent

(

       pk int identity(1,1) not null primary key,--대여번호

       who int not null references tblMember(pk), -- 대여회원번호

       what int not null references tblVideo(pk), --비디오번호

       rentDate datetime not null default(getdate()),--대여날짜

       retDate datetime null -- 반납날짜

)

 

 

 

--상품테이블

create table tblProduct

(

       product nvarchar(20) not null, -- 상품명

       company nvarchar(20) not null -- 제조사

             references tblCompany(company),

       price int not null -- 가격

)

 

--제조사테이블

create table tblCompany

(

       company nvarchar(20) not null primary key, --제조사명

       addr nvarchar(50) not null, -- 주소

       tel varchar(15) not null -- 연락처

)

 

insert into tblCompany (company, addr, tel)

       values ('농심','서울시','02-123-4567');

insert into tblCompany (company, addr, tel)

       values ('오리온','인천시','032-123-4567');

      

insert into tblProduct (product, company, price)

       values ('신라면','농심',800);

insert into tblProduct (product, company, price)

       values ('초코파이','오리온',500);

insert into tblProduct (product, company, price)

       values ('너구리','농심',700);

      

select * from tblProduct;

select * from tblCompany;

 

 

 

 

 

-----------------------------------2012.04.02 2, 3교시------------------------------------------

--고객테이블+ 판매테이블

--고객명연락처주소판매상품개수판매일

 

create table tblJoinCustomer

(

       name nvarchar(5) not null primary key, --고객명

       tel nchar(15) not null, -- 전화번호

       addr nvarchar(50) not null -- 주소

)

 

create table tblJoinSales

(

       orderNo int identity(1,1) not null primary key,

       customer nvarchar(5) not null

             references tblJoinCustomer(name), -- 구매고객명(***)

       item nchar(20) not null, -- 판매물품

       num int not null, --판매갯수

       [date] datetime not null default(getdate()) -- 판매시간

)

 

-- tblJoinCustomer

 

INSERT INTO tblJoinCustomer VALUES ('송골매', '123-4567', '서울시');

INSERT INTO tblJoinCustomer VALUES ('코요테', '111-1111', '인천시');

INSERT INTO tblJoinCustomer VALUES ('핑클', '222-2222', '대전시');

INSERT INTO tblJoinCustomer VALUES ('핫', '333-3333', '부산시');

INSERT INTO tblJoinCustomer VALUES ('세스', '444-4444', '광주시');

 

 

-- tblJoinSales

 

INSERT INTO tblJoinSales (Customer, Item, Num) VALUES ('송골매', '카세트', 1);

INSERT INTO tblJoinSales (Customer, Item, Num) VALUES ('핑클', '팥빙수', 4);

INSERT INTO tblJoinSales (Customer, Item, Num) VALUES ('핑클', '머리핀', 4);

INSERT INTO tblJoinSales (Customer, Item, Num) VALUES ('코요테', '컴퓨터', 2);

INSERT INTO tblJoinSales (Customer, Item, Num) VALUES ('베이비복스', '애기상자', 5);

INSERT INTO tblJoinSales (Customer, Item, Num) VALUES ('터보', '자외선차단크림', 2);

 

select * from tblJoinSales;

select * from tblJoinCustomer;

 

--테이블들이관계가짐!!

--한쪽(PK) <-> 한쪽(FK)

 

--조인(Join)

--: 최종결과셋이2개이상의테이블들의컬럼으로부터생성

 

--서브쿼리

-- : 최종결과셋이1개의테이블의컬럼으로부터생성

 

--조인의종류

--1. 단순Join(Cross Join) : X

--2. 내부Join(Inner Join) : ***

--3. 외부Join(Outer Join) : ****

--4. 셀프Join(Self Join) : 드물게...사용할수밖에없는상황에서만..

 

 

 

--상품테이블

create table tblProductCompany

(

       product nvarchar(20) not null primary key, -- 상품명

       company nvarchar(20) not null, -- 제조사

       price int not null, -- 가격

       addr nvarchar(50) not null, -- 주소

       tel varchar(15) not null -- 연락처

)

 

insert into tblProductCompany (product, company, price, addr, tel)

       values ('새우깡','농심',700,'서울시','02-585-8585');

insert into tblProductCompany (product, company, price, addr, tel)

       values ('신라면','농심',800,'서울시','02-585-8585');

      

 

select * from tblProductCompany;

select * from tblCompany;

select * from tblProduct;

 

--신라면이얼마입니까?

select price from tblProduct where product = '신라면';

--s-1

select price from tblProductCompany where product = '신라면';

--s-2

 

--농심의연락처?

select tel from tblCompany where company = '농심';

--s-3

select tel from tblProductCompany where company = '농심';

--s-4

 

 

--신라면을판매하는회사의연락처?

select company from tblProduct

       where product = '신라면';

--s-5

select tel from tblCompany

       where company = '농심';

--s-6

select tel from tblCompany

       where company = (select company from tblProduct

                                 where product = '신라면');

--s-6

 

--아래이것은훨씬위에보다간편하지만, DB 특성상겹치는자료들을최대한줄이는것이

--더욱더관리하기효율적이기때문에아래의간편한문구때문에테이블을합친다는것은

--말이안되는행위이다.

select tel from tblProductCompany where product = '신라면';

--s-7

 

--신라면(조건)의가격(출력)과판매회사의지역(출력)?

-- : 800, 서울시

 

select price, tel from tblProductCompany

       where product = '신라면';

 

--inner join

--서로다른테이블의컬럼을하나의결과값으로출력해줄수있게해주는도구.

select * from tblCompany

       inner join tblProduct

             on tblCompany.company = tblProduct.company;

--s-9

 

select * from tblProductCompany;

 

select tblCompany.company, tblProduct.price ,tblCompany.addr from tblCompany

       inner join tblProduct

             on tblCompany.company = tblProduct.company

                    where tblProduct.product = '신라면';

--s-8

 

select * from tblCompany;

--s-11

select * from tblproduct;

--s-12

 

--1. 단순Join

select * from tblCompany, tblProduct;

select * from tblCompany

       cross join  tblProduct;

--s-10

 

--2. 내부Join

-- : 내부Join은어떤테이블이먼저와도상관이없다.

-- : join을맺을때는사전에테이블끼리foreign키로묶여있어야함.

 

-- : inner join은전제조건이단순조인이지만, 조건을걸어서일부레코드만보는것이다.

-- : 그일부조건은, on 이라는명령으로처리할수있다.

-- : 단순Join에서, 의미있는데이터만가져온것이바로inner Join이다.

 

 

select tblProduct.product, tblProduct.price, tblCompany.tel from tblCompany

       inner join tblProduct

             on tblCompany.company = tblProduct.company;

--s-13

 

select * from tblCompany, tblProduct;

--s-14

select p.product, p.price, c.tel from tblCompany as c

       inner join tblProduct as p

             on c.company = p.company;

--s-15

 

--판단기준?

-- : 최종원하는데이터가1개테이블안에..? 2개테이블이상?

 

--춘천시(tblStaff)에사는직원이담당한프로젝트명(tblProject)?

-- : 서브쿼리

select prjName from tblProject

       where staff = (select name from tblstaff

                                 where addr = '춘천시');

--s-17

--춘천시(tblStaff)에사는직원이담당한프로젝트명(tblProject)과그직원월급(tblStaff)?

-- : 조인

select * from tblProject as p

       inner join tblStaff as s

             on s.name = p.staff

--s-16

 

select p.prjname, s.salary from tblProject as p

       inner join tblStaff as s

              on s.name = p.staff

                    where s.addr = '춘천시';

--s-18

 

select * from tblStaff;

 

select * from tblProject;

 

 

-----------------------------------2012.04.02 4교시----------------------------------------

 

-- 대여료(tblGenre)가2000원인비디오의제목(tblVideo)과대여기간(tblGenre)을출력

 

select * from tblVideo as v

       inner join tblGenre as g

             on v.genre = g.name;

            

use pubs;

 

select * from titles;

select * from publishers;

 

--책의제목과출판사명을동시에출력하시오.

select * from titles as t

       inner join publishers as p

             on t.pub_id = p.pub_id;

 

--3개테이블Join

--titles - titleauthor - authors

--책의제목(titles)과저자명(authors)을동시에출력하시오.

select t.title,a.au_fname from titles as t

       inner join titleauthor as ta

             on t.title_id = ta.title_id

                    inner join authors as a

                           on a.au_id = ta.au_id;

 

 

-----------------------------2012. 04. 02-----오후5교시-------------------------------------

                          

use sqlex

 

--땡칠이라는주연이맡은비디오는어떤것? 빌리는데얼마?

select v.name, g.price from tblVideo as v

       inner join tblGenre as g

             on v.genre = g.name

                    where v.major = '땡칠이';

                   

--누가? 무엇을빌려갔는지? : 사람이름, 테잎이름출력

select * from tblrent;

 

select m.name, v.name, r.rentDate from tblMember as m

       inner join tblRent as r

             on m.pk = r.who

                    inner join tblVideo as v

                           on r.what = v.pk

 

 

 

 

--outer join

--외부조인

-- : inner join + a

-- : 2개이상의테이블을inner join 한결과에

-- : 둘중1개의테이블의모든레코드를합한결과를반환

-- : 방향이있음(left, right)

 

insert into tblStaff (name, salary, addr)

       values ('가가가',200,'서울시');

      

insert into tblStaff (name, salary, addr)

       values ('나나나',200,'서울시');

      

select * from tblStaff

select * from tblProject;

 

--1. 단순join

select * from tblstaff, tblProject

 

--2. inner join

-- : 직원들중현재프로젝트를진행하고있는직원과,

-- 그프로젝트정보를가져오시오.

select * from tblstaff as s

       inner join tblProject as p

             on s.name = p.staff

            

--3. outer join

--모든직원의정보와, 그중진행되고있는담당자의프로젝트정보까지가져오시오.

--방향이중요하다!!

--left 사용시outer join 기준으로왼쪽,left가왼쪽테이블을이야기한다.

--right 사용시outer join 기준으로오른쪽,right는오른쪽테이블을이야기한다.

-- : tblStaff과tblProject의inner join 결과+ tblStaff의모든레코드

select * from tblstaff as s

       left outer join tblProject as p

             on s.name = p.staff

            

--가지고있는비디오테잎의제목을출력하고그테잎들이속한장르명을출력하시오.

 

select * from tblVideo as v

       inner join tblGenre g

             on v.genre = g.name

            

select * from tblVideo as v

       right outer join tblGenre g

             on v.genre = g.name

            

--tblGenre의모든행(outer)은출력!! + tblVideo 데이터도같이출력(inner)

 

select * from tblVideo as v

       right outer join tblGenre g

             on v.genre = g.name

--s-20

 

 

--물건을구매한이력이있는회원정보와구매정보를출력하시오. (inner)

--물건을구매한이력이있거나, 없어도회원정보와구매정보를출력하시오. (outer)

 

 

 

 

--셀프join

-- : 자신이자신과Join

create table tblemployee

(

       name nchar(3) not null primary key, -- 직원명

       manager nchar(3) null, -- 자신의담당자

       department nchar(3) not null --소속부서

)

 

insert into tblEmployee values('나사장',null,'사장');

insert into tblEmployee values('김부장','나사장','총무부');

insert into tblEmployee values('김과장','김부장','총무부');

insert into tblEmployee values('김대리','김과장','총무부');

insert into tblEmployee values('이부장','나사장','영업부');

insert into tblEmployee values('이과장','이부장','영업부');

insert into tblEmployee values('이사원','이과장','영업부');

 

select * from tblEmployee

 

select * from tblEmployee as e1

       inner join tblEmployee as e2

             on e1.manager = e2.name;

            

--문제

--1. tblStaff, tblProject에서서울시에사는직원을

-- 제외한나머지직원들의이름, 월급, 담당프로젝트명출력

use sqlex

select s.name, s.salary,p.prjName  from tblStaff as s

       inner join tblProject as p

             on s.name = p.staff

                    where s.addr <> '서울시';

                   

--name       salary      prjName

------------ ----------- ------------------------------

--김상형       150         홍콩수출건

--김상형       150         TV 광고건

--김상형       150         매출분석건

 

--(3개행이영향을받음)

                   

 

--2. tblJoinCustomer, tblJoinSales에서종류에상관없이상품을2개이상구매한회원의

-- 연락처, 이름, 구매상품명, 수량을출력하시오.

select c.tel, c.name, s.item, s.num  from tblJoinCustomer as c

       inner join tbljoinsales as s

             on c.name = s.customer

                    where s.num >= 2

                   

--tel             name  item                 num

----------------- ----- -------------------- -----------

--222-2222        핑클   팥빙수                 4

--222-2222        핑클   머리핀                 4

--111-1111        코요테  컴퓨터                 2

 

--(3개행이영향을받음)

 

 

 

 

--3. 비디오에서모든비디오제목, 수량, 대여가격을출력하시오.

use sqlex

select v.name, v.num, g.price from tblVideo as v

       inner join tblGenre as g

             on v.genre = g.name

            

--name                           num         price

-------------------------------- ----------- -----------

--영구와땡칠이                       5           1000

--어쭈구리                          5           1000

--털미네이터                         3           1500

--육복성                           3           2000

--뽀뽀할까요                         6           2000

--우정과영혼                        2           2000

--주라기유원지                       1           1500

--타이거킹                         4           1000

--텔미에브리딩                      10          2000

--동무                            7           1500

--공동경쟁구역                        2           1500

 

--(11개행이영향을받음)

 

 

 

--4. 비디오에서2007년2월에대여된구매내역을출력하시오. (대여회원명, 비디오명, 대여가격)

select * from tblrent

select * from tblGenre

select * from tblVideo

--m.name, v.name, g.price, r.rentDate

 

select m.name, v.name, g.price, r.rentDate from tblRent as r

       inner join tblMember as m

             on r.who = m.pk

                    inner join tblVideo as v

                           on r.what = v.pk

                                 inner join tblGenre as g

                                        on v.genre = g.name

                                              where r.rentDate >= '2007-02-01' and  r.rentDate < '2007-03-01'

 

--name  name                           money       rentDate

------- ------------------------------ ----------- -----------------------

--강감찬  어쭈구리                          0           2007-02-02 00:00:00.000

--유관순  털미네이터                         20000       2007-02-03 00:00:00.000

--이율곡  털미네이터                         15000       2007-02-04 00:00:00.000

--신숙주  뽀뽀할까요                         0           2007-02-05 00:00:00.000

--김유신  어쭈구리                          10000       2007-02-10 00:00:00.000

 

--(5개행이영향을받음)

 

 

 

--5. 비디오에서현재반납을안한회원과그테잎제목을출력하시오.(retDate컬럼이null)

select m.name, v.name from tblrent as r

       inner join tblmember as m

             on r.who = m.pk

                    inner join tblVideo as v

                           on r.what = v.pk

                                 where retDate is null

                                

--name  name

------- ------------------------------

--김유신  영구와땡칠이

--유관순  털미네이터

--신숙주  뽀뽀할까요

--김유신  어쭈구리

 

--(4개행이영향을받음)

 

                   

 

--6. pubs에서'trad_cook'과'mod_cook'에속한책들의

--title id, title 과저자(lname,fname)과저자의연락처

use pubs;

select * from titles

select * from titleauthor

select * from authors

 

select t.title_id, t.title, a.au_lname, a.au_fname, a.phone from titles as t

       inner join titleauthor as ta

             on t.title_id = ta.title_id

                    inner join authors as a

                           on ta.au_id = a.au_id

                                 where t.[type] in ('trad_cook','mod_cook')

 

--title_id title                                                                            au_lname                                 au_fname             phone

---------- -------------------------------------------------------------------------------- ---------------------------------------- -------------------- ------------

--MC2222   Silicon Valley Gastronomic Treats                                                del Castillo                             Innes                615 996-8275

--MC3021   The Gourmet Microwave                                                            DeFrance                                 Michel               219 547-9982

--MC3021   The Gourmet Microwave                                                            Ringer                                   Anne                 801 826-0752

--TC3218   Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean                  Panteley                                 Sylvia               301 946-8853

--TC4203   Fifty Years in Buckingham Palace Kitchens                                        Blotchet-Halls                           Reginald             503 745-6402

--TC7777   Sushi, Anyone?                                                                   O'Leary                                  Michael              408 286-2428

--TC7777   Sushi, Anyone?                                                                   Gringlesby                               Burt                 707 938-6445

--TC7777   Sushi, Anyone?                                                                   Yokomoto                                 Akiko                415 935-4228

 

--(8개행이영향을받음)

 

                                

 

 

--7. 비디오에서모든테잎의제목과제작사를출력하고,

-- 그중빌려간이력이있는테잎은누가빌려갔었는지같이출력

use sqlex

select * from tblrent

 

select v.name, v.company, m.name from tblvideo as v

       left outer join tblrent as r

             on v.pk = r.what

                    left outer join tblMember as m

                           on r.who = m.pk

                          

--name                           company              name

-------------------------------- -------------------- -----

--영구와땡칠이                       영구필름                김유신

--어쭈구리                          에로프로덕션             강감찬

--어쭈구리                          에로프로덕션             김유신

--털미네이터                         파라마운트               유관순

--털미네이터                         파라마운트               이율곡

--육복성                           대만영화사               NULL

--뽀뽀할까요                         뽀뽀사                 신숙주

--우정과영혼                        파라마운트               NULL

--주라기유원지                       NULL                 NULL

--타이거킹                         Walt                 NULL

--텔미에브리딩                      영구필름                NULL

--동무                            부산필름                NULL

--공동경쟁구역                        뽀뽀사                 NULL

 

--(13개행이영향을받음)

 

 

 

 

-------------------------2012 04 02----------오후6교시----------------------------------

--union

--join : 두테이블이상의특정컬럼들을묶어서하나의결과셋

--union : 두개이상의결과셋을하나로묶어서하나의결과셋

use sqlex

select * from tblVideo where genre = '액션';

--s-22

select * from tblVideo where genre = '코미디';

--s-23

select * from tblVideo where genre in ('액션','코미디');

--s-24

 

 

select * from tblVideo where genre = '액션' union

select * from tblVideo where genre = '코미디';

--s-25

 

--기업, 부서별게시판(영업부게시판, 총무부게시판...)

 

create table tblBoard1 -- 영업부게시판

(

       seq int identity(1,1) not null primary key, -- 글번호

       title nvarchar(50) not null, -- 제목

       name nvarchar(10) not null, -- 글쓴이

       content varchar(1000) not null, -- 내용

       regDate datetime not null default(getdate()) -- 글쓴시간

      

)

create table tblBoard2 --총무부게시판

(

       seq int identity(1,1) not null primary key, -- 글번호

       title nvarchar(50) not null, -- 제목

       name nvarchar(10) not null, -- 글쓴이

       content varchar(1000) not null, -- 내용

       regDate datetime not null default(getdate()) -- 글쓴시간

)

 

insert into tblBoard1 values

       ('영업부게시물입니다~~1','영업부과장','글내용', default),

       ('영업부게시물입니다~~2','영업부과장','글내용', default),

       ('영업부게시물입니다~~3','영업부과장','글내용', default),

       ('영업부게시물입니다~~4','영업부과장','글내용', default),

       (