안녕하세요. SQLER의 코난 김대우입니다.
이번 강좌에서는, 2-3. SELECT - 컬럼선택을 진행 하겠습니다.
SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
시작이 반이라는 말처럼 개발에는 개발환경 구축이 반이라는 말이 있지요. 첫 SQL 쿼리 수행을 다시 한번 축하합니다. 이번 강좌에서는 SELECT 컬럼 선택을 진행하겠습니다.
TL;DR
SELECT 구문에서 컬럼을 선택하는 방법과, 테이블 목록, 컬럼 목록을 확인하는 여러 방법을 살펴봅니다. SQL Server에서 스키마의 정의와 사용 방법도 함께 알아봅니다.
SELECT 컬럼 선택
이전 강좌에서 SELECT * FROM Production.Product 질의를 실행했습니다. 여기서 *는 모든 컬럼을 가져온다는 의미입니다.
이러한 식으로 결과가 보일 겁니다. 아울러 컬럼의 이름은 ProductID, Name, ProductNumber 등 컬럼 이름이 보이실 겁니다. 이 강좌의 목표는 *로 모든 컬럼을 보는 게 아니라 원하는 컬럼만 선택해 보는 것입니다.
ProductID, Name, ProductNumber 컬럼만 선택하고 쿼리 합니다.
SELECT ProductID, Name, ProductNumber FROM Production.Product;
다음은 출력 결과에서 컬럼 이름을 변경하는 샘플입니다.
SELECT ProductID as N'제품ID', Name as N'제품명', ProductNumber as N'제품번호' FROM Production.Product;
출력 결과의 컬럼 이름을 확인해 보세요. 위의 AS는 ANSI 표준입니다.
SELECT N'제품ID'=ProductID, N'제품명'=Name, N'제품번호'=ProductNumber FROM Production.Product;
이런 식도 가능하나 ANSI 표준인 위의 as를 이용하는 방법을 추천해 드립니다.
다음은 결과셋에 문자열 추가입니다.
SELECT N'제품명 : ', Name FROM Production.Product;
실행하면 결과셋에 '제품명' 문자열이 추가됩니다. 결과 부분이 표 형태로 선이 그려져 나오지요? 설정을 조절해 텍스트로 출력도 가능합니다.
쿼리 메뉴의 결과 처리 방법 - 텍스트로 결과표시를 하면? 결과를 텍스트로 볼 수 있습니다. 단축키로 컨트롤 + T 키를 눌러도 텍스트 결과 표시가 되며. 다시 표형식(그리드)으로 보려면 컨트롤 + D를 누르면 됩니다.
SELECT 절의 컬럼 선택
SELECT 키워드 - 다음 select_list에 컬럼들을 열거합니다.
SELECT column_name[, column_name…] |
컬럼 순서 재배열
SELECT 키워드 다음 열거하는 순서에 따라 결과로 나타나는 컬럼들의 순서가 결정됩니다.
Literal 사용
SELECT문의 결과에 문자열을 추가하여 읽기 쉽게 표시합니다.
SELECT column_name | ‘string literal’[, column_name | ‘string literal’…] |
컬럼 이름 변경
컬럼의 제목을 컬럼 이름이 아닌 다른 이름으로 변경할 수 있습니다. 여기서 AS를 사용할 수 있습니다.
SELECT column_heading = column_name[, column_name…] 또는 SELECT column_name column_heading[. Column_name…] |
데이터베이스의 테이블 목록 및 컬럼 목록 확인 방법
AdventureWorks 예제 데이터베이스에는 어떤 테이블이 있고
테이블에 어떤 컬럼이 존재하나요?
AdventureWorks라는 데이터베이스 이야기입니다. 지금까지 SQL 쿼리 구문을 이용해 테이블에서 정보를 조회했습니다. 그렇다면, 데이터베이스에 어떤 테이블들이 존재할까요?
SSMS에서 확인하는 방법은 SQL Server를 선택하고 데이터베이스를 선택 후, 테이블을 클릭하면 테이블 리스트를 볼 수 있습니다.
물론 SQL 쿼리로도 가능합니다. 테이블 리스트를 보는 방법은 세 가지 방법이 있습니다.
테이블 목록 확인 방법 1 - 인포메이션 스키마 뷰
먼저 첫 번째 방법으로 “인포메이션 스키마 뷰(Information Schema View)” 이용한 방법입니다. SQL 뷰(VIEW)는 일정의 거울이라고 생각하세요. 테이블을 비추는 일종의 거울을 만들고, 사용자는 이 거울을 통해서 조합된 결과셋을 보는 기능입니다. 이 뷰 역시 SQLER 강좌로 진행됩니다.
-- 전체 정보 select * from INFORMATION_SCHEMA.TABLES; --테이블 이름만 확인 select table_name from INFORMATION_SCHEMA.TABLES;
위의 쿼리를 수행해 데이터베이스에 존재하는 테이블을 확인할 수 있습니다. 뷰, 스키마, 새로운 키워드가 계속 나옵니다. 어렵더라도 지금은 이런 게 있구나 정도만 기억하고 강좌를 계속 진행하세요.
☑️ 챗GPT 활용: 인포메이션 스키마 뷰에 대해서 설명해 줘
테이블 목록 확인 방법 2 - 시스템 저장 프로시저
두 번째 방법은 시스템 저장 프로시저를 이용하는 방법입니다.
sp_tables;
이렇게 시스템 저장 프로시저를 이용해 테이블 리스트를 확인 가능합니다. 저장 프로시저는 이후 강좌에서 상세히 기술합니다.
테이블 목록 확인 방법 3 - 시스템 카탈로그(System Catalog)
마지막 세 번째 방법은 데이터베이스의 시스템 카탈로그 테이블을 조회하는 방식입니다. 하나의 데이터베이스의 개체들(테이블 같은 것)들은 모두 “sys.”으로 시작하는 시스템 카탈로그에 저장됩니다. 데이터베이스마다 존재하는 sys.tables라는 SQL서버가 제어하는 시스템 카탈로그 테이블에 저장됩니다.
SELECT * FROM sys.tables;
이렇게 테이블 정보를 시스템 카탈로그로 확인 가능합니다.
이 방법들 외에 sysobjects 테이블을 보는 방법도 있지만, 권장하지 않는 방법이라 진행하지 않습니다.
테이블 컬럼 리스트 조회 방법 1 - 인포메이션 스키마 뷰
다음은 한 테이블의 컬럼 리스트를 조회할 경우입니다.
첫 번째로 인포메이션 스키마 뷰 - INFORMATION_SCHEMA.COLUMNS를 이용하는 방법입니다.
-- Product 테이블의 컬럼 리스트 SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Product'; -- Product 테이블의 컬럼 이름만 리스트 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Product';
WHERE 조건문도 보이는데요, 바로 이어지는 강좌에서 진행하니 지금은 이렇게 필터링을 하는구나 정도만 보시면 됩니다.
테이블 컬럼 리스트 조회 방법 2 - 시스템 저장 프로시저
두 번째 방법은 시스템 저장 프로시저를 이용한 방식입니다. 시스템 저장프로시저로 Product 테이블의 컬럼 리스트를 출력하려면
sp_columns 'Product';
이렇게 수행하면 됩니다.
테이블 컬럼 리스트 조회 방법 3 - 시스템 카탈로그
마지막 세 번째로 sys.columns 시스템 카탈로그 테이블에서 컬럼리스트를 출력합니다.
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Production.Product');
이렇게 테이블의 컬럼을 보는 세 가지 방법을 소개해 드립니다.
데이터베이스의 테이블의 리스트가 궁금하거나 테이블 컬럼 리스트가 궁금하면 위의 방법 중에서 골라 사용하시면 됩니다. 가능한 인포메이션 스키마 뷰 방식을 사용하세요.
데이터베이스 스키마(Schema)
테이블은 알겠는데 계속 스키마에 대한 이야기가 나옵니다. 맨 처음 테이블을 조회할 때도 SELECT * FROM Production.Product 이렇게 테이블 앞에 “Production’이라는 스키마를 이용했습니다. SQL Server의 스키마란 무엇이고 왜 사용하는 것일까요?
스키마란 무엇인가?
스키마는 개체를 별도의 네임스페이스로 그룹화할 수 있는 데이터베이스 개체의 명명된 컨테이너입니다.
프로그래밍 경험이 있다면 네임스페이스에 익숙하실 겁니다. 스키마는 데이터베이스 안에서 테이블과 같은 개체가 고유한 이름을 가지게 하는 식별자입니다.
☑️ 챗GPT 활용: 데이터베이스 스키마(Schema)에 대해서 설명해 줘
데이터베이스 개체 참조(4 Part object reference)
처음 배운 테이블 쿼리는 이런 형식입니다.
SELECT * FROM Production.Product;
엄밀한 의미에서 SQL Server는 4 부분으로 구성된 개체 참조 방식을 사용합니다.
Server.Database.DatabaseSchema.DatabaseObjec |
위의 쿼리를 예로 든다면
SELECT * FROM Server.Database.Production.Product; |
이런 식으로 쿼리를 작성해야 하지만, 서버명과 데이터베이스명은 이미 설정되기 때문에 생략합니다.
그렇다면 스키마를 왜 사용하나요?
1. 스키마를 사용하면 데이터베이스 개체를 그룹화할 수 있어 관리하기 편합니다.
2. (이후에 강좌로 진행될) 데이터베이스 개체별로 접근 권한을 부여하지 않고 스키마 단위로 권한을 부여하면 스키마에 포함된 개체에 대해 권한을 상속받습니다.
3. 이전 SQL 버전에서는 데이터베이스 개체 소유자를 삭제할 경우 개체의 소유자를 다른 소유자로 변경하거나 개체를 삭제해야만 했으나 스키마를 사용하면서 이 과정이 필요 없습니다.
스키마가 있는 데이터베이스 테이블인데 저는 스키마 없이 테이블을 사용해도 잘 되던데요?
SELECT * FROM Production.Product; --이런 스키마.테이블명 참조를 SELECT * FROM Product; -- (오류) 기본스키마면 생략할 수 있습니다.
생략 가능한 경우는, 현재 로그인한 계정의 기본스키마가 Production으로 설정되었을 가능성이 높습니다. 사용자의 기본 스키마를 지정 가능할 경우 생략할 수 있습니다. (또는 dbo 스키마일 경우에도 생략 가능)
기본적으로 데이터베이스에는 dbo와 몇몇 시스템이 관리하는 스키마가 있고, AdventureWorks 데이터베이스도 Production, Sales 및 HumanResources 스키마가 있습니다.
스키마를 사용하는 최선의 방법은 무엇인가요?
데이터베이스 개체를 참조할 경우 항상 스키마를 참조하세요.
SELECT * FROM Production.Product; --항상 스키마를 참조
쿼리 오류를 줄일 수 있으며 다른 데이터베이스 사용자가 같은 쿼리를 이용해도 오류 없이 수행됩니다. 코드가 길어지는 약간의 불편함도 있으나 장점이 더 많으니 항상 스키마를 참조해 쿼리를 작성하실 것을 권장해 드립니다.
참고링크
SQL Server에서 소유권 및 사용자와 스키마 분리
SQL 강좌 책 구매
강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다.