SQL Query in the SSMS : DB, Table Database Laboratory

차례 DB Table SSMS를 사용하여 생성, 수정 및 삭제 테이블 정의 및 특징과 제약 사항 시스템 테이블 메타데이터 / 시스템 Catalog와 Database Catalog SSMS를 사용하여 테이블 생성, 수정, 삭제 이번 주 강의의 전반적인 내용은 SSMS를 사용하여 DB 생성, 수정 및 삭제를 하는 방법과 Table의 정의 및 간단한 제약사항을 살펴본 후에 시스템 테이블의 특징을 살펴보겠습니다, 또한, 테이블 생성, 수정, 삭제를 하기 위한 질의를 사용하겠으며 이와 같이 테이블을 만들 때 고려해야 할 사항들에는 어떤 것들이 있는지 알아보겠습니다. Database Laboratory

SQL Server Management Studio SQL Server login 저희가 실습시간에 사용할 SQL Server Management Studio입니다. 서버이름을 입력하고 아이디와 암호를 입력하여 서버와 접속하게 됩니다. Database Laboratory

SQL Management Studio 접속 서버 주소 210.115.229.77,2433 아이디 : 학번 Ex) 20121234 비 번 : s학번@hallym Ex) s121234@hallym Database Laboratory

SQL Server Management Studio SSMS (SQL Server Management Studio) Database Laboratory

DB 생성하기 (SSMS) SSMS를 이용한 DB 생성하기 (1/3) SSMS에서 데이터베이스 폴더 아이콘을 오른쪽 마우스로 클릭하면 그림과 같이 메뉴가 생성됩니다. 메뉴 중에서 “새 데이터베이스(N)”를 선택하시기 바랍니다. Database Laboratory

DB 생성하기 (SSMS) SSMS를 이용한 DB 생성하기 (2/3) 새 데이터베이스 이름 입력 새 데이터베이스 속성 창이 열리면 생성할 데이터베이스 명을 입력합니다. 여러분들은 본인의 학번을 입력하여 데이터베이스를 생성하시기 바랍니다. Database Laboratory

DB 생성하기 (SSMS) SSMS를 이용한 DB 생성하기 (3/3) Database Laboratory 여러분은 자기 학번으로 명명된 새로운 데이터베이스가 생성되었음을 확인 하실 수 있을 것입니다. Database Laboratory

DB 생성하기 (QA) SQL Query를 이용한 DB 생성하기 (1/2) /* master DB 사용 */ /* testing DB 생성 */ /* 파일경로 */ /* *.mdf 파일 크기 설정 */ /* 파일경로 */ /* *.ldf 파일 크기 설정 */ 다음은 쿼리 분석기를 사용하여 데이터베이스를 생성하는 것을 보이고 있습니다 . 초기에는 master 데이터베이스를 사용하며 새로 생성한 데이터베이스의 이름은 testing입니다. 데이터베이스를 구성하고 있는 2개의 파일 이름은 *.mdf 와 *.ldf 이며, *.mdf 은 데이터베이스에서 테이블 내에 데이터 값을 입력할 경우 MAXSIZ와 FILEGROWTH를 설정할 수 있습니다. *.ldf 는 데이터베이스에서 어떤 트랜잭션이 발생할 때 트랜잭션 로그정정보가 저장되며 mdf 파일에서 처럼 속성들을 설정할 수 있습니다. 그리고 나서 쿼리 실행을 하면 Test_dat와 Test_log에 속성을 정한 대로 디스크 크기가 할당 된 것을 볼 수 있습니다. Database Laboratory

DB 생성하기 (Query) SQL Query를 이용한 DB 확인하기(2/2) Database Laboratory 쿼리 분석기에서 testing이라는 데이터베이스가 생성된 것을 확인합니다. Database Laboratory

DB 수정하기 초기에 만들어 놓은 데이터베이스가 크기(Sizing) 작 업을 잘 못해서 더 늘려야 하는 경우 데이터베이스에 할당된 데이터의 크기 또는 트랜잭션 로 그 공간을 확장하거나 축소 데이터가 계속 저장, 축적되어 데이터베이스 크기 조정 작업을 해야 하는 경우도 있습니다. 다시 말하면, 데이터베이스의 데이터 크기 또는 트랜잭션 로그 공간을 확장하거나 축소하여 원활한 자원을 할당하는 것입니다. Database Laboratory

DB 수정하기 (파일 크기 확장) 데이터베이스를 만들 때 보았듯이 MS-SQL 서버는 미리 설정해 놓은 증가 값에 따라 데이터베이스의 크 기를 자동으로 확장할 수 있다. 자동으로 크기가 증가하더라도 데이터베이스를 확장할 때 는 그 파일이 증가 할 수 있는 최대크기를 지정하는 것을 권장 최대 파일 크기 지정 SSMS 메뉴에서 파일크기 증가 제한 옵션 사용 Alter Database의 MAXSIZE 매개변수 사용 앞에서 데이터베이스를 만들 때 보았듯이 MS-SQL 서버는 미리 설정해 놓은 증가 값에 따라 데이터베이스의 크기를 자동으로 확장할 수 있습니다. 자동으로 크기가 증가하더라도 데이터베이스를 확장할 때는 그 파일이 증가 할 수 있는 최대크기를 지정하는 것을 권장합니다. 트랜잭션 로그 크기 조정 시 최대 파일 크기를 지정하는 방법은 SSMS 메뉴에서 파일 크기 증가 제한 옵션 사용하거나 ALTER Database의 MAXSIZE 매개변수를 사용하여 값을 할당할 수 있습니다. Database Laboratory

DB 수정하기 (파일 크기 확장) SSMS 메뉴에서 파일크기 증가제한옵션 사용 (데이 터 파일) 오른쪽클릭의 속성탭 데이터베이스에서 파일 크기를 확장하기 위해 SSMS를 사용한 그림 입니다. 확장을 원하는 데이터베이스 testing을 선택한 후 오른쪽 마우스 클릭을 하면 등록 정보 메뉴를 클릭합니다. 그러면 testing 데이터베이스 속성 창이 생성되며 데이터 파일 탭을 선택하게 되면 파일이름, 위치, 할당된 공간 등을 수정할 수 있습니다. 오른쪽클릭의 속성탭 Database Laboratory

DB 수정하기 (파일 크기 확장) SSMS 메뉴에서 파일크기 증가제한옵션 사용 (데이 터 파일) 마찬가지 방법으로 트랜잭션 로그 탭을 선택한 후 속성값 등을 수정 할 수 있습니다. Database Laboratory

DB 수정하기 (파일 크기 확장) 쿼리 분석기 : Alter Database의 MAXSIZE 매개변수 사용 지금까지 SSMS와 쿼리 분석기에서 속성 변경을 통해 데이터베이스를 확장하는 실습을 하였습니다. Database Laboratory

DB 이름 바꾸기 “sp_renamedb” 시스템 stored procedure를 사용 TEST EXAM DB 이름 변경도 수정과 마찬가지로 쉽게 실습을 할 수 있습니다. 그림을 보시면 TEST에서 EXAM으로 데이터베이스 이름이 바뀐 것을 알 수 있습니다. 코드의 내용을 보시면, 스토어드 프로시저에서 데이터베이스의 이름을 변경하는 옵션 sp_remanedb를 사용하여 이름을 변경합니다.sp_dboption 은 다중 사용자를 단일 사용자로 제한하고 이름을 변경한 뒤, 변경할 이름의 사용자를 다중 사용자로 바꾸는 코드 입니다. 또한 , 단지 데이터베이스 이름을 바꾸었다고 해서 데이터 파일이름 또한 바뀌는 것은 아닙니다. 단, 데이터 파일이름은 수정되지 않는다 /* storedprocecure_renamedatabase */ Database Laboratory

DB 삭제 SSMS와 DROP이라는 SQL사용 한번 삭제한 DB는 다시 복구할 방법이 없으므로 반드시 Master DB를 백업한 후 삭제 데이터베이스를 삭제하는 방법 또한 SSMS와 쿼리 분석기에서 수행할 수 있습니다. 한번 삭제한 데이터베이스는 다시 복구할 방법이 없으므로 반드시 Master DB를 백업한 후 삭제하시기 바랍니다. 코드는 DROP DATABASE 데이터베이스 이름 EXAM을 코딩하면 Test_Log.LDF와 TEST_Data.MDF 파일이 모두 삭제 되었다는 메시지가 생성됩니다. 지금까지 SSMS와 쿼리 분석기를 사용하여 데이터베이스의 생성, 수정, 삭제를 실습하였습니다. Database Laboratory

Table 테이블 정의 및 특징과 제약 사항 테이블 생성, 수정, 삭제 Database Laboratory 지금부터는 테이블의 정의 및 특징, 제약사항을 살펴본 후에 시스템 테이블에는 어떤 것들이 있는지? 테이블 생성, 수정, 삭제하는 실습을 하겠습니다. Database Laboratory

Table 만들기 (SSMS) [테이블]-[새 테이블] Database Laboratory 원하는 데이터베이스에 사용자 테이블을 생성하는 과정을 알아보겠습니다. 여러분들의 데이터베이스 폴더를 활성화시키면 테이블 메뉴가 나오는데 오른쪽 마우스 클릭 후 새 테이블 메뉴를 선택합니다. Database Laboratory

Table 만들기 (SSMS) 컬럼 명과 데이터 타입, 데이터의 길이와 널(Null)값 여부를 결정한 후 ‘account’란 이름으로 저장 2. table의 primary key 설정 1. table 속성 설정 테이블 디자인 창이 나타나게 됩니다. 첫 번째로 Table의 속성을 입력합니다. 열 이름에는 테이블을 구성하는 row와 column중 column 이름을 입력하고, 데이터 형식에는 열 이름의 데이터 값의 형식을 입력하는 컬럼으로 정수형일 경우는 int로, 긴 문자열일 경우는 varchar로 입력할 수 있습니다. 길이는 데이터의 최대길이를 뜻하며, Null 허용 컬럼은 값을 입력할 경우 값을 넣지 않아도 된다는 것을 체크할 수 있습니다. 두 번째로는 열 이름을 마우스로 클릭한 뒤 열쇠 모양의 아이콘을 누르면 primary key 로 설정된 것입니다. 테이블에 사용자가 정보 입력을 마치면 저장 아이콘을 누르면 이름 선택이라는 창이 나타납니다. account라는 테이블 명을 입력한 뒤 확인 버튼을 누르면 account 테이블이 생성된 것을 확인 할 수 있습니다. 3. table 저장 Database Laboratory

Table 만들기 (SSMS) 새로 고침 버튼이나 F5 키 누름 Database Laboratory testing 데이터베이스의 테이블을 클릭한 후 새로 고침 버튼이나 F5 키를 누르면 account 테이블이 생성된 것을 볼 수 있습니다. Database Laboratory

Table 만들기 (SSMS) account Table에 data value 입력 Database Laboratory 이렇게 입력한 테이블 정보는 Database Laboratory

Table 만들기 (SSMS) account Table의 값 확인 account 테이블을 클릭 후 오른쪽 마우스 클릭한 다음 모든 행 반환 메뉴를 선택하면 account 테이블에 저장된 데이터들을 확인할 수 있습니다. 여기서 주의 깊게 봐야 할 것은 account_number가 주키로 설정되어 있는데 임의의 컬럼이 주 키로 설정되면 나머지 컬럼의 데이터 값들도 주 키의 데이터 값에 따라 정렬되어 있음을 알 수 있습니다. Primary Key로 설정되어 있어 다른 컬럼의 데이터도 account_number 컬럼에 의존해 정렬되어 있다 Database Laboratory

Table 만들기 (SSMS) account Table 속성 확인/사용 권한 설정 Database Laboratory account 테이블을 double click 한 그림이며 테이블의 속성을 확인할 수 있으며 사용권한 버튼을 누르면 Database Laboratory

Table 만들기 (SSMS) account Table 속성 확인/사용 권한 설정 Database Laboratory 테이블의 사용 권한을 설정하는 창이 생성되는데, account 테이블에 접근할 수 있는 모든 사용자는 SELECT, INSERT, UPDATA만 가능하며 DELETE 권한은 주지 않았습니다. 그리고 적용 버튼을 클릭 후 확인 버튼을 선택합니다. 앞으로 사용할 예제에서도 주로 account 테이블을 사용하게 되는데 자세한 테이블 구조는 강의 사이트에 올려놓은 스키마 구조를 참조하시면 됩니다. 지금까지 EM을 사용하여 테이블을 생성하는 실습을 하였습니다. 다음은 Database Laboratory

Table 만들기(QA) branch Table 생성/확인 Database Laboratory 쿼리 분석기에서 branch 테이블을 생성하는 과정을 실습 하겠습니다. Testing 에서 새 쿼리를 선택한 후 테이블 이름이 branch인 테이블을 코딩합니다. 컬럼의 이름과 이에 대한 데이터 타입, 길이를 코딩한 후에 컬럼의 제약조건인 primary key, foreign key, Null 조건 등을 입력하면 됩니다. 컬럼의 구분은 ,로 한 것을 확인 하세요. branch 테이블은 은행의 지사이름, 지사가 위치한 도시이름과 자산의 규모에 대한 스키마를 정의하고 있습니다. 질의를 실행하면 사용자 테이블에 branch테이블 생성된 것을 확인 하 실수 있습니다. 이와 같이 스키마를 정의한 후에 Database Laboratory

Table 만들기(QA) branch Table 데이터 값 입력 Database Laboratory 그림과 같이 INSERT INTO ~ VALUES 질의어를 사용하여 스키마에 데이터 값을 입력해 줄 수 있습니다. INSERT INTO 테이블 이름 다음에 (컬럼 리스트)가 오며 VALUES 다음에 스트링 형태의 값들은 싱글 쿼테이션 (‘’)으로 표현하며 쉼표로 컬럼을 구분하였습니다. 쿼리 실행을 하면 1개 행 적용됨 메시지가 INSERT INTO 구문 개수만큼 생성됩니다. 쿼리 분석기의 에디터에서 코딩한 결과를 확인하면 Database Laboratory

Table 만들기(QA) branch Table 데이터 값 확인(select 문 이용) Database Laboratory

Table 만들기(QA) branch Table 데이터 값 확인 그림과 같습니다. Database Laboratory

Table 만들기(SSMS) customer Table 생성/데이터 값 입력 Database Laboratory 세 번째 테이블 예제로 은행에서 고객에 관한 스키마 정의, 즉 고객 이름, 고객이 거주하는 거리 명, 그리고 도시와 데이터 값 입력 코드를 보여주고 있으며 질의 실행 결과도 이와 같이 생성됩니다. 지금까지 SSMS에서의 테이블 생성과정을 살펴보았습니다. 이와 같이 여러분들도 테이블을 쉽게 생성 할 수 있을 것입니다. 이제부터는 이렇게 생성된 테이블을 수정하는 Database Laboratory

Table 수정하기 SQL Query (ALTER TABLE 문) SSMS 방법 column 삽입 , 삭제하기 data type 변경하기 SSMS column 삽입, 삭제하기 방법에 대해서 살펴보도록 하겠습니다. 기존 테이블에서 SSMS와 SQL Query를 사용하여 컬럼의 삽입, 삭제 및 데이터 타입을 변경할 수 있습니다. Database Laboratory

Table 수정하기 (ALTER TABLE 문) column 삽입하기 (실패– 오류 메시지) 해결 방법 account 테이블에 internet_banking 컬럼을 삽입하는 예제입니다. alter table 테이블 이름 ADD 컬럼이름 테이터 타입(길이) 제약조건 순으로 코딩을 합니다. 오류 메시지가 발생한 이유는 추가하는 컬럼은 기본적으로 null로 설정해야 하기 때문입니다. 또한 not null에 Default를 사용하여 해결할 수도 있습니다. 첫 번째 방법으로 해결한 결과는 internet_banking column의 제약조건인 Null로 수정(추가 컬럼은 Null을 허용해야 한다) Default 사용 Database Laboratory

Table 수정하기 (ALTER TABLE 문) column 삽입하기(성공 - data value는 아직 입력되지 않음) 이와 같으며 단, internet_banking 컬럼에 데이터 값이 하나도 입력되어 있지 않다는 것을 알 수 있습니다. 그러므로 추가로 데이터 삽입 코딩을 하는 작업이 필요합니다. Database Laboratory

Table 수정하기 (ALTER TABLE 문) column 삽입하기(성공 - DEFAULT value N 입력) NOT NULL에 DEFAULT 값을 사용하여 해결하는 방법은 그림과 같습니다.. 쿼리 실행 후 internet_banking 컬럼에 값 N이 디폴트로 입력되어 있는 것을 볼 수 있습니다. 잘 나타나지 않을 시에는 testing->테이블->dbo.account 내에 있는 열을 새로 고침 해보시기 바랍니다. Database Laboratory

Table 수정하기 (ALTER TABLE 문) 다음은 account 테이블에서 internet_banking 컬럼을 삭제하는 질의 문입니다. alter table 테이블 이름 drop column 삭제할 컬럼 이름 순으로 입력한 후 쿼리 실행을 합니다. 삭제하기 실패 오류 메시지가 나오게 되는데, 오류 메시지를 자세히 보시면 ‘DF__account__interne__31EC6D26’ 이라는 테이블이 internet_column의 정보를 사용하고 있기 때문에 즉 다른 테이블과 정보를 공유하고 있으므로 드롭을 시킬 수 없다는 의미 입니다. 아래 메시지는 더 확실하게 표현하고 있는데 다른 객체들이 이 컬럼에 접근할 수 있기 때문에 드롭 시킬 수 없다는 내용입니다. Database Laboratory

Table 수정하기 (ALTER TABLE 문) Data type 변경 internet_banking의 데이터 타입 변경은 질의 문과 같으며 실행 후의 그림을 보여주고 있습니다. 지금까지 쿼리로 컬럼 추가/삽입/삭제, 데이터 변경 방법에 대해 알아 보았습니다. 다음은 Database Laboratory

Table 수정하기 (SSMS) column 삽입, 삭제/ data type 변경 Database Laboratory SSMS에서 어떻게 컬럼을 삽입, 삭제, 데이터 타입을 변경하는지 살펴 보겠습니다. account 테이블에서 오른쪽 마우스 클릭 후 테이블 디자인 메뉴 클릭을 하면 Database Laboratory

Table 수정하기 (SSMS) column 삽입, 삭제/ data type 변경 Database Laboratory 그림과 같이 account 테이블의 스키마 정보들이 나타납니다. 여기서 오른쪽 마우스 클릭 후 나타나는 열 삽입 메뉴를 선택하면 열에 관한 정보를 입력할 수 있고 열 삭제, 인덱스/ 키 메뉴도 선택할 수 있어 스키마 정보 변경이 가능합니다. Database Laboratory

Table 삭제하기 SQL 문 SSMS DROP TABLE table_name ex) DROP TABLE account 마지막으로 테이블 삭제하는 방법은 질의 문은 drop table 테이블 이름으로 표현하며 SSMS에서는 간단하게 삭제 메뉴를 선택하여 테이블을 제거할 수 있습니다. 지금까지 엔터프라이즈 매니저와 쿼리 분석기를 사용하여 데이터베이스 와 테이블 생성, 수정, 삭제에 대해서 살펴 보았습니다 다음주에는 SQL 질의어를 사용하여 실습을 하겠습니다. 그럼 안녕히 계세요. Database Laboratory

Table 만들기 과제 1 파일형식 : [분반]_이름_학번_과제번호로 제출 : dbing@hallym.ac.kr column : 학번 이름 전화번호 e-mail Value는 3개 이상 넣기 SSMS, SQL 사용 자신의 DB명이 보이도록 캡쳐 파일형식 : [분반]_이름_학번_과제번호로 제출 형식 어길 시 감점처리 제출 : dbing@hallym.ac.kr 제출기간 : 9월16일 월요일 23시59분까지 Database Laboratory