[문과 코린이의 IT 기록장] MSSQL - 테이블/열 생성 및 삭제 (SQL 데이터베이스 언어, CREATE TABLE 쿼리, ALTER TABLE, 글꼴 변경, 동시에 여러개 열 추가 및 삭제 방법, RENAME COLUMN, 종속성 보기, Inner Join)

벼리네 2022. 1. 20. 14:37

< 참고 강의 영상 >

MSSQL Server 2016 기반의 데이터베이스 입문에서 활용까지 Part.2 대시보드 - 인프런 | 강의 (inflearn.com)

MSSQL Server 2016 기반의 데이터베이스 입문에서 활용까지 Part.2 - 인프런 | 강의

데이터베이스 구축에서부터 테이블 생성, 각종 SQL 쿼리문을 사용해서 데이터베이스 내 데이터를 조작, 변경할 수 있습니다., MSSQL로 시작하는 데이터베이스 학습 -데이터를 다양하게 조작, 변경,

www.inflearn.com

2022.01.19 - [문과 코린이의, [SQL] 기록] - [문과 코린이의 IT 기록장] MSSQL - 집합 연산자 (관계형 데이터베이스(DBMS) vs 집합, 합집합, 차집합, 교집합, 곱집합, 프로젝션, 셀렉션, 조인/디비전)

[문과 코린이의 IT 기록장] MSSQL - 집합 연산자 (관계형 데이터베이스(DBMS) vs 집합, 합집합, 차집합,

[문과 코린이의 IT 기록장] MSSQL - 집합 연산자 (관계형 데이터베이스(DBMS) vs 집합, 합집합, 차집합, 교집합, 곱집합, 프로젝션, 셀렉션, 조인/디비전) < 참고 영상 강의 > MSSQL Server 20..

vansoft1215.tistory.com

2022.01.19 - [문과 코린이의, [SQL] 기록] - [문과 코린이의 IT 기록장] MSSQL - DB & Table 생성 (데이터베이스 만들기, 테이블 만들기)

[문과 코린이의 IT 기록장] MSSQL - DB & Table 생성 (데이터베이스 만들기, 테이블 만들기)

[문과 코린이의 IT 기록장] MSSQL - DB & Table 생성 (데이터베이스 만들기, 테이블 만들기) < 참고 영상 강의 > MSSQL Server 2016 기반의 데이터베이스 입문에서 활용까지 Part.1 - 인프런 | 강의..

vansoft1215.tistory.com

2022.01.19 - [문과 코린이의, [SQL] 기록] - [문과 코린이의 IT 기록장] MSSQL - SQL Server 데이터 형식 (SQL Server 데이터 형식, char vs varchar, nchar vs nvarchar, 정수 데이터 크기와 범위, Boolean, 날짜, 시간 데이터 형식)

[문과 코린이의 IT 기록장] MSSQL - SQL Server 데이터 형식 (SQL Server 데이터 형식, char vs varchar, nchar vs n

[문과 코린이의 IT 기록장] MSSQL - SQL Server 데이터 형식 (SQL Server 데이터 형식, char vs varchar, nchar vs nvarchar, 정수 데이터 크기와 범위, Boolean, 날짜, 시간 데이터..

vansoft1215.tistory.com

1. SQL 데이터베이스 언어

- SQL : Structured Query Language

- 관계형 데이터베이스(RDBMS)를 조작하기 위한 데이터베이스 언어.

- 즉, SQL은 DBMS로 소통하는 것임.

- SQL은 언어인만큼 프로그래밍 언어 요소와 개념이 학습시 필요하다.

(초보자 - 기본 쿼리문 위주 숙달, 고급 쿼리 - 이해 목표)

2. CREATE TABLE 쿼리

- 데이터베이스 내 테이블을 만들 때, CREATE문을 사용한다.

- 개체탐색기를 사용해서도 만들 수 있지만, 보통 초보자에서 넘어서게 되면 쿼리창에서 직접 쿼리문으로 생성한다.

1) 테이블명

- 의미를 알아볼 수 있도록, 영문으로 간결하게 마든다.

- 영문 사용시 복수형보다는, 단수형을 권장한다.

- 다른 테이블 이름과 중복되지 않도록 한다.

2) Column명

- 한 테이블 내에서는 컬럼(Column)명이 중복되지 않도록 한다.

- 컬럼명 작성은 일관성있고 규칙성있게 만드는 것이 좋다. ex. mem_id라면, 이후에도 mem_name 이런 식으로

- 컬럼명 길이에 대한 제한이 DBMS마다 있다.

3) 주의사항

- CREATE문 내에서 ( ) 이 괄호를 사용한다.

* { }사용 X.

- 각 컬럼들은 매 줄마다 콤마(,)로 구분하고 마지막은 안한다.

- CREATE문 마지막은 세미콜론(;)으로 닫는다.

- 테이블명과 컬럼명은 문자로 시작할 것을 권장한다. (대/소문자 구분 X)

- DBMS 예약 키워드(ex. CREATE)는 불허한다. 또한 특수문자(_,$,#)사용 또한 불허한다.

* #와 같은 경우는 식별자를 자동으로 삽입 X. 나머지는 자동으로 삽입해줌

예약 키워드를 사용해야 할 경우에는 이렇게 식별자([ ])를 자동으로 생성해줌 (버전에 따라 다를 수 있음)

* 식별자 ' ', " ", [ ],` ` 를 직접 입력해서 사용해도 됨.

4) 형식

CREATE TABLE dbo.membeTbl( -- dbo는 테이블 이름
    컬럼명1	데이터형식	제약조건(NOT NULL), -- 한 줄 끝날 때 , 사용
    컬럼명2	데이터형식	제약조건,
    컬럼명3	데이터형식	제약조건 -- 마침표 X
); -- {}아님. ()사용.

PRIMARY KEY(기본키)
1. 레코드의 대표값으로 레코드를 구별하는 유일값이 저장되는 컬럼.
2. 기본키 제약조건이 서정된 컬럼은, 자동적으로 NULL을 허용하지 않게 되고, UNIQUE제약 조건이 역시 자동으로 설정된다.

DBO.
- 테이블 생성시 이름 앞에 dbo.기본 스키마를 붙일 수 있다.
- 스키마 없이 생성하면, dbo. 기본 스키마가 붙으면서 테이블 생성. (모든 개체가 기본적으로 dbo.스키마에 포함)

dbo : 기본 스키마 / HumanResources : 이 또한 스키마

5) 저장방법

3. ALTER TABLE

- 기존 테이블에, 새로운 열을 추가하거나 삭제할 때 사용하는 쿼리문.

* Tip : 처음부터 DB 업무파악을 잘하여 변경할 일이 없도록 하면 좋다.

1) ADD

- 새로운 열을 추가하는 것

ALTER TABLE dbo.memberTble
	ADD 	  mem_point 	int 	NULL
    -- mem_point : 추가할 컬럼명
    -- int : 추가할 컬럼의 데이터 타입

- 새롭게 추가된 컬럼은 자동적으로 테이블의 맨 마지막 컬럼이 된다.

- 추가시 내가 임의적으로 컬럼의 위치를 지정할 수는 없다.

* 애플리케이션이나 쿼리 수준을 변경하는 식으로(DB자체를 변경하지 않도록) 지정하는 것이 좋다.

* 즉 쿼리로 반환된 열에서 순서를 지정해주는 방법이 더 좋다.

a. 기존 테이블에 열을 추가할 경우

< 기존 테이블에 열을 추가할 경우 주의사항 >
a. 기존 테이블에 데이터가 있다면 고려사항이 많아진다.
b. NOT NULL 속성을 추가할 수 없다.
cf. NOT NULL - 데이터가 미리 존재해야 한다. (열 자체가 없다가 생긴것이기 때문에 데이터가 X. 따라서 NULL로만 채울 수 있음)
c. NULL 값으로 채울 수 없는 경우도 존재한다.

=> 이 까다로운 상황을 해결하기 위한 방법 존재

# 기존 테이블에 데이터가 있는 경우 : DEFAULT제약과 IDENTITY속성을 사용하여 추가한다.
a. DEFAULT는 열에 기본 값을 저장해주므로, DEFAULT('') NOT NULL 이처럼 사용한다.
* ''(값이 없는 값)을 넣어놓는 방법.
b. IDENTITY 속성은 자동 증가 값을 저장해주므로, IDENTITY(1,1) NOT NULL 이처럼 사용한다.
ex. 새로운 열의 레코드에 자동 증가값 저장 : (1,1) -> 1,2,3,4... / (1,2) -> 1,3,5,7...

b. 열 추가시 default 지정 방법

ALTER TABLE dbo.memberTbl
	Add		mem_point	int		default('0')

2) DROP COLUMN

- 열을 삭제하는 것

ALTER TABLE dbo.memberTbl
	DROP COLUMN mem_point

< 데이터 존재 시 열 삭제가 가능한가? >
- 해당 컬럼에 데이터가 존재할 때, 삭제가 가능하다.
- 삭제된 컬럼은 이후에 복구가 안되며 모두 삭제되므로, 삭제할 때 매우 주의하며 사용되어야 한다.

4. 글꼴 변경

- 도구(Tool) - 옵션(Options) - 환경(Environment) - 글꼴 및 색(Fonts and Colors)

5. 동시에 여러개 열 추가 및 삭제 방법은?

1) 동시에 열 추가

ALTER TABLE dbo.memberTbl -- 괄호 쓰지 않기.
    ADD        -- ADD는 한번만
    	mem_point	int		null,
        mem_point2	int		null,
        mem_nickname	varchar(20)		null

2) 동시에 열 삭제

ALTER TABLE dbo.memberTbl
	DROP COLUMN	
		mem_point1,
		mem_point2,
		mem_nickname

6. RENAME COLUMN

- 컬럼명을 수정할 수 있다. 그렇지만 일반적으로는 안하는 것이 좋다.

- 사용법

EXEC sp_rename 'dbo.memberTbl.mem_point1', 'mem_point12';
-- EXEC sp_rename '테이블 이름.기존 컬럼명', '새 컬럼명';

* EXEC는 저장 프로시저를 호출할 때 사용 (테이블, 인덱스,열, 별칭, 데이터형식 등 사용자가 만든 개체의 이름 변경)

< 열 변경시 또는 테이블 이름 변경시 마지막 옵션은 생략이 가능하다. >
a. 열 이름 변경시
EXEC sp_rename 'dbo.memberTbl', 'memberTbl', 'mbmberTbl3', 'COLUMN';
b. 테이블 이름 변경시
EXEC sp_rename 'dbo.memberTbl', 'memberTbl3', 'OBJECT'

7. 종속성 보기

- 테이블 삭제 및 변경시에는, 이 개체가 다른 테이블 등에 종속되어 의존하고 있는지 여부를 체크하는 것이 좋다.

- 개체 탐색기 사용

: 해당 테이블 마우스 우측버튼 클릭 후, [종속성 보기(View Dependencies)]를 선택

이 테이블이 종속된 개체 (이 테이블이 하위) * titleauthor는 titles의 tid와 연결되어있는 종속관계를 가지고 있음

- 개체 종속성 식별 (쿼리)

a. SELECT * FROM sys.dm_sql_referencing_entities('스키마.개체이름','OBJECT');

b. sys.dm_sql_referenced_entities

* 종속 개체(OBJECT) 확인을 위한 DMF(Dynamic Management Functions) 지원

** 데이터베이스 개체 : 테이블, 저장 프로시서, 뷰, ...

* Databases - System Databases - master - Synonyms - Programmability - Functions - System Functions - Table-valued Functions

< 쿼리(SQL)를 사용하는 이유가 무엇인가? >
- 모든 경우에 SMSS를 사용할 수 있는 것은 아니다.
- 즉, 직접적으로 DB에 접속하여 데이터를 조작, 변경할 수 있는게 아니기 때문에, 외부에서 데이터를 전달하여 DB를 조작, 변경하도록 하는 것이다.

8. Inner Join

1. 
SELECT referencing_schema_name, referencing_entity_name, referencing_id
FROM sys.dm_sql_referencing_entities('dbo.titleauthor','OBJECT');
-- titleauthor에 종속된 개체가 무엇인지를 알 수 있음.
-- 그러나 구체적인 type에 대해서는 알 수 없음.

2.
SELECT name, type, type_desc, object_id 
FROM sys.objects;
--SELECT * FROM sys.objects; -- 해당 데이터베이스의 개체들을 모두 불러온다
--해당 개체의 type에 대한 정보를 얻어낼 수 있음. (그러나 종속된 개체가 무엇인지 알 수 X)

3.
--Inner Join 사용 O. (위의 두개를 섞으면 원하는 정보를 적절히 얻어낼 수 있음)
SELECT referencing_schema_name, referencing_entity_name, name, type, type_desc
FROM sys.dm_sql_referencing_entities('dbo.titleauthor','OBJECT') AS a INNER JOIN sys.objects AS b
  -- a로서 sys.dm_sql_referencing entities('dbo.titleauthor','OBJECT')를 부르자
  -- b로서 sys.objects를 부르자
	ON referencing_id = object_id;	-- ON(~조건에서)
	-- ON a.referencing_id = b.object_id;

* 유의사항
- 아직 공부하고 있는 문과생 코린이가, 정리해서 남겨놓은 정리 및 필기노트입니다.
- 정확하지 않거나, 틀린 점이 있을 수 있으니, 유의해서 봐주시면 감사하겠습니다.
- 혹시 잘못된 점을 발견하셨다면, 댓글로 친절하게 남겨주시면 감사하겠습니다 :)