[SQL Server] Chapter 03. T-SQL기초(1)

 *이 포스팅 내용은 개인 학습 내용을 정리한 것입니다.

 

* Transact-SQL (T-SQL)  : 현재 SQL서버에서 사용중인 SQL문

 

1. SELECT문

 SELECT문의 형식 ( []문 안의 내용은 옵션)

SELECT [DISTINCT] [TOP] <컬럼별칭과 반환될 컬럼, 표현식, 상수값들>  [INTO] <새로운 테이블명>  FROM <기초 테이블명과 별칭들>  [WHERE] <검색 조건들>  [GROUP BY] <집계 기준 컬럼들>  [HAVING] <집계함수결과에 대한 조건들>  [ORDER BY] <정렬될 기준 컬럼들>

 

 SELECT문의 기본 구조는 다음과 같다. 

SELECT * FROM (MyDB.dbo.)MyTable

 *은 모든 컬럼을 가져오라는 와일드카드. MyTable은 내가 조회하여 올 컬럼이다. 만약 MyDB를 현재 사용중이라면 'MyDB.dbo.'이 부분은 생략해도 된다.

 FROM절에는 SQL2000에서 지원하는 사용자 정의함수를 사용할 수 있다. 즉, 다음과 같은 구문도 OK 

SELECT * FROM ::fn_helpcollaptions();

 사용자 정의 함수에 대해서는 추후에 올릴 예정

 

2. 식별자

 - DB의 개체 이름은 식별자이다. 일반식별자의 규칙을 따르지 않는 경우, '"'(큰 따옴표)나, '[]'(대괄호)로 구분해서 구분식별자로 사용하여야 한다.

 - 위의 두 경우 모두, 식별자 이름 길이는 최대 128자. (임시테이블의 경우 116자)

 - 일반식별자의 규칙 

1) 첫 문자는 다음중 하나여야 한다.     i) Unicode Standard3.2에서 정의한 문자. a-z, A-Z으로 시작하는 문자와 다른 언어의 문자가 포함됨     ii) '_'(밑줄), '@'기호, '#'기호 로 시작하는 경우.  2) 후속 문자는 다음을 포함할 수 있다.     i) Unicode Standard 3.2에서 정의한 문자.     ii) 숫자     iii) '@'기호, '$'기호, '#'기호  3) T-SQL예약어는 식별자로 사용할 수 없다.  4) 공백이나 특수문자는 사용할 수 없다.  5) 보충문자도 사용할 수 없다. (보충문자 : 표준 문자 집합에 포함되지 않은 문자)   -출처 : MSDN(http://msdn.microsoft.com/ko-kr/library/ms175874.aspx)

 SQL에서 @로 시작하는 식별자는 지역변수 or 매개변수를 의미한다. #으로 시작하는 식별자는 임시테이블 또는 프로시저. ##로 시작하는 식별자는 전역 임시테이블을 나타낸다. T-SQL의 어떤 함수들은 @@로 시작하므로, 이 형태도 사용을 자제하는 것이 좋다.

 - 구분식별자에는 일반식별자에서 포함되는 내용 외에 다음 문자들을 추가로 사용할 수 있다. 

물결표(~), 하이픈(-), 느낌표(!), 좌측중괄호({),우측중괄호(}), 아포스트로피('). 백분율(%), 캐럿(^)  앰퍼샌드(&), 마침표(.), 좌측괄호((), 우측괄호()), 백슬래시(\), 악센트기호(`)  -출처 : MS TechNet( http://technet.microsoft.com/ko-kr/library/ms176027(v=sql.105).aspx)

 

 - 큰따옴표(")를 구분자로 사용하려면, SET QUOTED_IDENTIFIER ON으로 세션 옵션이 설정되어야 한다.

 

3. 문자열과 작은따옴표 표현

 - 문자열 표현을 위해 작은따옴표(')를 사용한다. 문자열에 작은따옴표를 포함하고 싶다면, '' (두번표시)를 하여 처리하면 된다.

 - SET QUOTED_IDENTIFIER OFF로 세션옵션이 설정되면, 큰따옴표 (")를 문자열 표현에 사용할 수 있다. 이런경우에 작은따옴표는 '로 표시가 가능하다. 

SELECT 'hello''s'는  SET QUOTED_IDENTIFIER OFF  SELECT "Hello's"와 같다.

  QUOTED_IDENTIFIER 가 OFF일 경우, 큰따옴표는 ""로 표시하면 된다.

 

4. SELECT, FROM, WHERE

 예를들어 다음과 같은 테이블이 있다고 가정하자. 

stor_id	title_id	qty	price
P01	테스트1	30	500
S01	테스트2	45	NULL
P02	테스트3	20	10
D01	테스트4	35	5000
P03	테스트5	40	NULL
S02	테스트6	60	300

                                  [테이블 : 상점정보. 이름은 'Sales']

 (1) Sales에서 모든 데이터를 불러오고 싶다면 다음과 같은 쿼리를 수행한다. 

SELECT * FROM Sales

 

 (2) 이 테이블에서, title_id가 '테스트1'인 상점을 찾고 싶다면 다음과 같이 쿼리문을 작성할 수 있다.

SELECT * FROM Sales WHERE title_id='테스트1'

  - 이 구문은 문자열이 정확하게 일치하여야 한다.

 

 (3) 특정 패턴을 가지는 문자열을 찾고 싶다면 (예를들어stor_id가 P로 시작하는 경우)

SELECT * FROM Sales WHERE stor_id LIKE 'P%'

   - LIKE문을 사용하고, 여기에 추가할 수 있는 패턴 문자열은 아래와 같다. 

패턴	설명	예	예제 설명
%	어떤 경우라도	LIKE 'Co%'  LIKE '%co%'	Co로 시작하는 문자열  co가 포함된 문자열
_	어떤 하나의 문자	LIKE '_ean'	ean으로 끝나는 문자열. '_'수만큼 길이가 일치해야 한다.
[]	[]안의 각각의 문자	LIKE '[CK]on%'  LINE '[C-K]on'	C또는 K로 시작하고, 다음이 co인 문자열  C에서 K사이 문자와 on으로 끝나는 문자열
[^]	[^]안의 문자를 제외	LIKE 'de[^l]%'  LIKE '[A^l%'	de로 시작하며, 다음이 l이 아닌 모든 문자열  A로 시작하고, 다음이 I가 아닌 모든 문자열

  * _의 경우 '_'의 개수가 자리만큼이다. 만약 값 중에, sa, sala,라는 데이터가 있는데, '_a'일 경우 sa만 반환되고, '___a'일 경우 sala가 반환된다. 만약, 자릿수가 정확하지 않으면 '%a'를 사용하라

 

 (4) 범위 검색 : BETWEEN을 사용한다. (예 판매수량이 30개이상, 40개 이하인 경우)

SELECT * FROM Sales WHERE qty BETWEEN 30 AND 40

    이 구문은 다음 구문과 같다

    > SELECT * FROM Sales WHERE qty >= 30 AND qty <= 40

 

 (5) 특정 값 안에 있는지 여부 : IN (예를들어 S01, P03을 찾으려고 할 경우)

SELECT * FROM Sales WHERE stor_id IN ('P03','S01')    위 구문은 아래와 같다   SELECT * FROM Sales WHERE stor_id = 'P03' OR stor_id='S01'

 

 (6) IN으로 비교할 비교 값에는 하위 쿼리를 사용할 수 있다. 즉, 다음과 같은 쿼리가 가능하다

SELECT * FROM Sales WHERE stor_id IN (SELECT stor_id FROM Stores WHERE name='test')

 

 (7) NULL값 비교

   - NULL값과의 비교를 위해서는 IS NULL 구문을 사용하여야 한다. 반대로, != NULL을 비교하기 위해서는 IS NOT NULL을 사용하여야 한다.    

SELECT stor_id, price FROM Sales WHERE price IS NULL   결과>    S01 NULL    P03 NULL

   4,5에서처럼 아래 구문도 동일한 동작을 할까? 

SELECT stor_id, price FROM Sales WHERE price = NULL

   위 구문은 정상적으로 동작하지 않는다. 위 구문의 동작을 위해서는 다음 옵션을 설정하여 주어야 한다. 

SET ANSI_NULLS OFF

  ANSI_NULLS 옵션을 OFF로 해주어야 한다. ANSI표준은 IS NULL, IS NOT NULL을 사용하는 것이다.

 

 (8) WHERE절에서는 NOT, AND, OR 논리연산자를 사용하여 조건을 중첩할 수 있다. 우선순위는 NOT, AND, OR순이다. 하지만 괄호를 사용하여 명시적으로 우선순위를 두는 것이 좋다.

 

5. ORDER BY

 - SELECT문으로 조회한 결과를 조건에 의해 정렬할 때 사용하는 구문, ntext, text, mage데이터 형식에는 해당 조건을 줄 수 없다.

 - 오름차순의 경우 ASC키워드를, 내림차순의 경우 DESC키워드를 사용한다. 기본은 오름차순이다.

 

6. TOP n

 - 조회된 결과의 상위 n개의 자료만 조회할 때 사용함.

  예제 구문은 다음과 같다. (4에서의 테이블을 사용한다고 가정)

SELECT TOP 3 stor_id, qty FROM Sales ORDER BY qty DESC  결과>   S02 60   S01 45

 

7. DISTINCT

 - 중복된 값을 배제하고 고유한 값을 가져오고 싶을 때 사용한다. 이와 반대로 ALL을 사용하여 모든 데이터를 가져올 수 있다. 자세한 내용은 차후에 다시 설명됨

 

8. GROUP BY, HAVING

 - 요약 정보를 위해 GROUP BY절과 집계함수를 사용할 수 있다. 집계함수는 NULL값을 제외하고 처리한다. (COUNT는제외). GROUP BY에서 주의할 점은 SELECT에서 사용될 컬럼은, GROUP BY에 언급되거나, 집계함수에 의해 만들어진 함수여야 한다. 

>집계함수 상세설명

*집계함수  - 값 집합에 대한 계산을 수행하고, 단일 값을 반환. SELECT문의 GROUP BY절과 함께 사용되는경우가 많음  SELECT목록 또는 HAVING절에서만 사용가능하다.  - T-SQL에서 제공하는 집계함수  COUNT를 제외한 모든 함수는 NULL은 무시한다.  함수명  설명   AVG  그룹에 속한 값의 평균을 반환.  MIN  식의 최소값 반환  CHECKSUM_AVG  그룹값의 체크섬이 반환됨. 뒤에 OVER절이 붙을 수 있다.  SUM  식의 모든 값 또는 DISTINCT값의 합을 반환. 숫자열에만 사용가능  COUNT  그룹의 항목수를 반환, 반환형은 int  COUNT_BIG  COUNT와 비슷하지만 반환형이 bigint를 반환  GROUPING  GROUP BY목록에 지정된 열 식이 집계되었는지 여부 반환  GROUPING_ID  그룹 수준을 계산함. SELECT 목록, HAVING, ORDER BY절에만 사용가능  MAX  식의 최대값 반환  STDEV  표준편차를 구함  STDEVP  표준모편차를 구함. 모집단에 대한 표준편차를 구하는  것이 다름  VAR  분산을 구한다.  VARP  분산 중 모집단에 대한 분산(모분산)을 구한다

 

9. SELECT ~ INTO

 이 구문을 사용하면 select된 결과를 새로운 테이블을 생성하여 데이터를 삽입할 수 있다. 예제 구문은 아래와 같다. 

SELECT *  INTO SalesTest3 FROM (SELECT stor_id, qty FROM Sales) AS temp

 

10. JOIN

 - JOIN : 여러 테이블을 쿼리하여 하나의 결과물을 만들음

 - JOIN은 아래와 같이 세가지 유형이 있음

  (1) INNER JOIN : 연결조건을 모두 만족하는 행을 조회함 가장 일반적인 조인 

SELECT <getCols> FROM <tableA> INNER JOIN <tableB> ON <condition>

   위와 같이 사용한다.

   - 아래 쿼리의 예제는 supplierID가 같은 행을 조회하는 쿼리

SELECT productID, productname, suppliers.supplierID, companyname  FROM suppliers INNER JOIN products     ON ( suppliers.supplierID = products.supplierID)  아래는 별칭을 사용한 경우  SELECT p.productID, p.productname, s.supplierID, companyname  FROM suppliers s INNER JOIN products p     ON ( s.supplierID = p.supplierID)

 

위 쿼리에서, 두 테이블 중에 유일한 컬럼의 경우 명시적인 표시를 안해도 되지만, 같은 이름의 컬럼인 경우 명시적으로 어느 테이블에 속한 컬럼인지 명시해 줄 것

     - 여러 테이블에 대한 조인을 반복하기 위해서는 JOIN~ON~을 계속 붙이면 된다.

  

SELECT p.productID, p.productname, s.supplierID, companyname, pc.price  FROM suppliers s INNER JOIN products p  ON ( s.supplierID = p.supplierID)  INNER JOIN priceinfo pc ON (p.productID = pc.productID)

 

   (2) CROSS JOIN : 상호조인. 조인조건이 없음, 왼쪽 테이블의 모든 행을 오른쪽 테이블의 모든 행과 조인한다.

SELECT <getCols> FROM <tableLeft> CROSS JOIN <tableRight>

    - 모든 행이 조회된다. 만약 각 테이블이 5행, 6행일 경우 결과는 30행이 나온다.

SELECT price, productname, p.productID FROM products p CROSS JOIN priceinfo

 

    -  조인은 같은 테이블끼리의 조인이 가능하므로, CROSS JOIN의 경우 각 행의 모든 경우의 수를 만드는데는 쓸 수 있을 것 같다.

 

   (3) OUTER JOIN : 조건을 만족하는 행과 만족하지 못하는 행을 모두 확인하고 싶을 때 사용.

    - 조인의 종류는  LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN. T-SQL에서는 *=, =*(각각 좌,우 외부조인임)를 사용할 수 있다. FULL OUTER JOIN은 ANSI표준을 사용하여야 한다

테이블 생성 쿼리 및 삽입 예  >생성  CREATE TABLE Menus( Menu_id int, Menu_name varchar(20) NOT NULL, CONSTRAINT PK_Menu_id PRIMARY KEY (menu_id) ) CREATE TABLE Meals( Menu_id int NOT NULL, Date smalldatetime, Amount int NOT NULL)  >삽입 insert into menus values(1, '백반') insert into menus values(2, '순두부') insert into menus values(3, '김치찌개') insert into menus values(4, '짬뽕') insert into meals values(1,'20141201', 4000) insert into meals values(4,'20141201', 3800) insert into meals values(2,'20141202', 4500) insert into meals values(1,'20141202', 4000) insert into meals values(9,'20141203', 2500) insert into meals values(4,'20141203', 3800)

 

    a) LEFT OUTER JOIN 

ANSI표준>  SELECT <getCols> FROM <tableA> LEFT OUTER JOIN <tableB> ON <Condition> T-SQL>  SELECT <getCols> FROM <tableA>, <tableB> WHERE <tableA.col> *= <tableB.col>

      - 좌측 테이블에 있는 데이터를 기준으로 조회하여 올 수 있다. (좌측테이블에 없으면 조회되지 않음)

쿼리>  SELECT Meals.Menu_id, Menu_name, Date, Amount FROM Menus LEFT OUTER JOIN Meals ON Menus.Menu_id = Meals.Menu_id 결과>  Menu_id     Menu_name              date                 Amount -----------------------------------------------------------      1           백반               2014-12-01 00:00:00       4000      1           백반               2014-12-02 00:00:00       4000      2           순두부            2014-12-02 00:00:00       4500    NULL       김치찌개                 NULL                  NULL      4           짬뽕               2014-12-01 00:00:00       3800      4           짬뽕               2014-12-03 00:00:00       3800

 

  이 예제에서, 우측테이블에 자료가 없는 '김치찌개' 항목까지 같이 조회가 되었다. 값이 없으므로 NULL

 

    b) RIGHT OUTER JOIN

ANSI표준>   SELECT <getCols> FROM <tableA> RIGHT OUTER JOIN <tableB> ON <Condition>  T-SQL>   SELECT <getCols> FROM <tableA>, <tableB> WHERE <tableA.col> =* <tableB.col>

     - 우측 테이블(TableB)의 항목을 기준으로 조회

 

쿼리>  SELECT Meals.Menu_id, Menu_name, Date, Amount FROM Menus RIGHT OUTER JOIN Meals ON Menus.Menu_id = Meals.Menu_id 결과> Menu_id     Menu_name              date                 Amount -----------------------------------------------------------      1           백반               2014-12-01 00:00:00       4000      4           백반               2014-12-01 00:00:00       3800      2           순두부            2014-12-02 00:00:00       4500      1           백반               2014-12-02 00:00:00       4000      9           NULL              2014-12-03 00:00:00       2500      4           짬뽕               2014-12-03 00:00:00       3800

 

    c) FULL OUTER JOIN 

SELECT <getCols> FROM <TableA> FULL OUTER JOIN <TableB> ON <Condition>

      - 좌,우 테이블 모든 항목을 가지고 조회한다. 

쿼리>  SELECT Meals.Menu_id, Menu_name, Date, Amount FROM Menus FULL OUTER JOIN Meals ON Menus.Menu_id = Meals.Menu_id  결과> Menu_id     Menu_name              date                 Amount -----------------------------------------------------------      1           백반               2014-12-01 00:00:00       4000      1           백반               2014-12-02 00:00:00       4000      2           순두부            2014-12-02 00:00:00       4500    NULL       김치찌개                 NULL                  NULL      4           짬뽕               2014-12-01 00:00:00       3800      4           짬뽕               2014-12-03 00:00:00       3800      9           NULL              2014-12-03 00:00:00       2500

 

 - T-SQL로 외부조인 사용시 검색조건에 NULL을 비교하는 경우에는 조인조건인지 검색조건인지 명확하지 않아 잘못된 결과를 얻을 수 있다. 그래서 NULL검색조건에서는 ANSI표준을 사용하는 것을 권장한다. 

쿼리>  SELECT Meals.Menu_id, Menu_name, Date, Amount FROM Menus LEFT OUTER JOIN Meals ON Menus.Menu_id = Meals.Menu_id WHERE Date IS NULL 결과> Menu_id     Menu_name              date                 Amount -----------------------------------------------------------    NULL       김치찌개                 NULL                  NULL   쿼리> SELECT Meals.Menu_id, Menu_name, Date, Amount FROM Menus , Meals WHERE Menus.Menu_id *= Meals.Menu_id AND Date IS NULL Menu_id     Menu_name              date                 Amount -----------------------------------------------------------    NULL       백반                       NULL                  NULL    NULL       순두부                    NULL                  NULL    NULL       김치찌개                 NULL                  NULL    4             짬뽕                       NULL                  3800

 

이후는 다음 포스팅에

 

 - 출처 : SQL Server Advanced programming