SQL Server 2005

1
SQL Server 2005

• ??? ??? ?
• Transact-SQL ?

2
?? T-SQL ?? ???? SELECTFROM (1)

• SELECT?? ?? ??
• WITH ltcommon_table_expressiongt
• SELECT select_list INTO new_table
• FROM table_source
• WHERE search_condition
• GROUP BY group_by_expression
• HAVING search_condition
• ORDER BY order_expression ASC DESC
• ?? ??? SELECT ??
• SELECT ???
• FROM ???
• WHERE ??

3
?? T-SQL ?? ???? SELECTFROM (2)

• USE ??
• USE ??????_??
• SELECT? FROM
• ?? ?)
• USE Adventureworks
• SELECT FROM HumanResources.Employee
• SELECT FROM BRAIN.AdventureWorks.HumanResources.
  Employee
• SELECT Name, GroupName FROM HumanResources.Depart
  ment
• ??
• -- ???? ???? ???

4
lt??1gt DB??, ????? ?? ??

• ?? ??
• DB??, ??? ??, ??? ?? ?? ??? ???? ?? ? ???? ???
  ???.
• ?? ??
• DB ??
• EXEC sp_helpdb
• ??? ?? ??
• EXEC sp_tables _at_table_type "'TABLE'"
• ??? ??
• EXEC sp_columns _at_table_name ?????',
  _at_table_owner ?????

5
lt??2gt ?? ??????? ??

• ?? ??
• ??? ???? ?? ??? ??????? ???? ????.
• ?? ?????? ??

6
?? T-SQL ?? SELECTFROM... WHERE (1)

• ???? WHERE ?
• SELECT ???? FROM ????? WHERE ???
• ?????? ??
• SELECT userID, Name FROM userTbl WHERE birthYear
  gt 1981 AND height gt 180
• BETWEEN AND? IN()
• SELECT Name, height FROM userTbl WHERE height
  BETWEEN 180 AND 183
• SELECT Name, height FROM userTbl WHERE height IN
  (178, 180, 182)
• ANY/ALL/SOME ??? ????(SubQuery, ????)
• SELECT Name, height FROM userTbl WHERE height gt
  ANY (SELECT height FROM userTbl WHERE mobile1
  '019')
• SELECT Name, height FROM userTbl WHERE height
  ANY (SELECT height FROM userTbl WHERE mobile1
  '019)

7
?? T-SQL ?? SELECTFROM... WHERE (2)

• ORDER BY ? ???? ??
• SELECT Name, height FROM userTbl ORDER BY height
• DISTINCT ? ????
• SELECT DISTINCT addr FROM userTbl
• TOP(N) ? ?? N?? ??
• SELECT TOP(10) CreditCardID FROM Sales.CreditCard
• SELECT TOP(0.1) PERCENT CreditCardID FROM
  Sales.CreditCard
• TABLESAMPLE ? ?? ?? ??
• SELECT FROM Sales.SalesOrderDetail
  TABLESAMPLE(5 PERCENT)
• SELECT INTO ? ??? ??? ?? ??
• SELECT ???? INTO ?????? FROM ?????
• SELECT INTO buyTbl2 FROM buyTbl

8
?? T-SQL ?? GROUP BY

• GROUP BY ? ??? ???? ???
• ??
• WITH ltcommon_table_expressiongt
• SELECT select_list INTO new_table
• FROM table_source
• WHERE search_condition
• GROUP BY group_by_expression
• HAVING search_condition
• ORDER BY order_expression ASC DESC
• ?? ?
• SELECT userid AS ??????, SUM(amount) AS
• ????? FROM buyTbl GROUP BY userid

9
?? T-SQL ?? ?? ??

• SUM() ?? ?? ???? ????
• ?? ?
• SELECT AVG(amount) AS ?????? FROM buyTbl
• SELECT userid, AVG(amount1.0) AS ?????? FROM
  buyTbl GROUP BY userid
• SELECT Name, height
• FROM userTbl
• WHERE height (SELECT MAX(height)FROM
  userTbl)
• OR height (SELECT MIN(height)FROM
  userTbl)

??? ??
AVG() ??? ???
MIN() ???? ???
MAX() ???? ???
COUNT() ?? ??? ??
COUN_BIG() ??? ??. ? ???? bigint ???.
STDEV() ????? ???
10
lt??3gt ????? ?? SQL?? ?? ??

• ?? ??
• ????? ? ???? ?? SQL??? ?? ??? ?? ? ??? ?? ????.
• ?? ??

11
?? T-SQL ?? HAVING ?

• ????? ??? ?? ???? ??
• SELECT userid AS ???, SUM(priceamount)
• FROM buyTbl
• WHERE SUM(priceamount) gt 1000
• GROUP BY userid
• HAVING ?? ??
• SELECT userid AS ???, SUM(priceamount)
• FROM buyTbl
• GROUP BY userid
• HAVING SUM(priceamount) gt 1000

12
?? T-SQL ?? ??? ??

• COMPUTE ? ??? ??? ??, ? ????? ??
• SELECT , priceamount AS ?? FROM buyTbl
• COMPUTE SUM(price amount)
• COMPUTE AVG(price amount)
• COMPUTE BY? ? ??? ??? ????? ??
• SELECT , price amount FROM buyTbl
• ORDER BY groupName
• COMPUTE SUM(price amount) BY groupName
• COMPUTE AVG(price amount) BY groupName
• ROLLUP / GROUPING() / CUBE ? ?? ??
  ????? ????
• SELECT num, groupName, SUM(price amount) AS
  ??
• FROM buyTbl GROUP BY groupName, num
• WITH ROLLUP

13
?? T-SQL ?? WITH?? CTE (1)

• ??
• WITH ?? CTE(Common Table Expression)? ???? ?? ???
• CTE? ??? ?, ?????, ?????? ??? ? ??? ? ???? ???
• ANSI-SQL99 ???? ???
• ??? CTE? ? ??? CTE? ???
• ? ??? CTE
• ??? ??? ??? ???? ??? ???? ??
• ?? ??
• WITH CTE_?????(???)
• AS
• ( lt???gt )
• SELECT ??? FROM CTE_?????

14
?? T-SQL ?? WITH?? CTE (2)

• ? ??? CTE
• CTE? ?
• WITH abc(userid, total)
• AS
• (SELECT userid, SUM(priceamount)
• FROM buyTbl GROUP BY userid )
• SELECT FROM abc ORDER BY total DESC
• CET? ??

15
?? T-SQL ?? WITH?? CTE (3)

• ??? CTE
• ?? ??? ????? ???? ??
• ??? ??? ?? ???? ?
• ?? ??
• WITH CTE_?????(???)
• AS
• (
• lt???1 SELECT FROM ???A gt
• UNION ALL
• lt???2 SELECT FROM ???A JOIN CTE_?????gt
• )
• SELECT FROM CTE_?????

16
lt??4gt ?? ??? ??? ?? ??? CTE

• ?? ??
• ??? CTE? ???? ??? ???? ???? ??? ???.
• ?? ??

17
?? T-SQL ?? T-SQL? ??

• DML(Data Manipulation Language ??? ?? ??)
• ???? ??, ??, ??, ??? ???
• ??? ???? ?
• SELECT, INSERT, UPDATE, DELETE ? ???
• ????(Transaction)? ???? (select ??)
• DDL(Data Definition Language ??? ?? ??)
• ?????? ??? ??/??/????? ???
• CREATE, DROP, ALTER ?? ???
• ????? ????? ??
• DCL(Data Control Language ??? ?? ??)
• ????? ?????? ?? ???
• GRANT, REVOKE, DENY ?? ???

18
?? T-SQL ?? ??? ??(Insert,Update,Delete)(1)

• ???? ?? (Insert)
• ?? ??
• INSERT INTO lt???gt (?1, ?2, ) VALUES (?1, ?2
  )
• ?? ?
• INSERT INTO testTbl1 VALUES (1, '???', 25)
• INSERT INTO testTbl1(id, userName) VALUES (2,
  '???)
• IDENTITY ????? ???? ?? / DEFAULT ?
• CREATE TABLE testTbl2
• (id int IDENTITY, userName nchar(3),
• age int, nation nchar(4) DEFAULT '????')
• GO
• INSERT INTO testTbl2 VALUES ('???', 30, DEFAULT)
• ??? ???? ?? INSERT INTO SELECT
• INSERT INTO ????? (???1, ???2, )
• SELECT ?

19
?? T-SQL ?? ??? ??(Insert,Update,Delete)(2)

• ???? ?? (Update)
• ?? ??
• UPDATE ?????
• SET ?1?1, ?2?2
• WHERE ??
• ?? ?
• UPDATE testTbl3 SET tel '??' WHERE Fname
  'Kim'
• ???? ?? (Delete)
• ?? ??
• DELETE ????? WHRER ??
• ?? ?
• DELETE testTbl3 WHERE Fname 'Kim'

20
lt??5gt ???? ??? ??

• ?? ??
• ???? ???? ?? ??(Delete, Drop, Truncate)?? ?? ??,
  ??? ???? ?? ??? ??.
• ?? ??

21
SQL Server? ??? ?? ??? ??? ?? (1)

• ??? ??? ??

??? ?? ??? ?? ?? ??
?BIT 1 0 ?? 1 Boolean?? ?(True), ??(False)? ??
?INT 4 ? -21? 21? ??
?SMALLINT 2 -32,76832,767 ??
?TINYINT 1 0 255 ?? ??
?BIGINT 8 -263 263-1 ??
?DECIMAL(p,s) 517 N/A ?? ???(p)? ??(s)? ?? ???
NEMERIC 517 N/A DECIMAL? ??? ??? ??
REAL 4 FLOAT(24)? ??
FLOAT(p) 48 P? 25???? 4???, 25???? 8???? ??? ??
MONEY 8 -263 263-1 ?? ??? ??
SMALLMONEY 4 ? -21? 21? ?? ??? ??
22
SQL Server? ??? ?? ??? ??? ?? (2)

• ??? ??? ??

??? ?? ??? ? ??
?CHAR(n) 08000 ????
?NCHAR(n) 08000 ???? 0 4000?. ???? ????
?VARCHAR(n) 0231-1 (2GB) ???? . SQL Server 2005 ?? ??? 08000
?NVARCHAR(n) 0231-1 ???? 0230-1. ???? ????
TEXT(n) 0231-1 (2GB) SQL Server 2005 ?? ????? 8000?? ?? ??? ??? ??????, SQL Server 2005??? VARCHAR(MAX)? ?? ??? ?? ??
NTEXT(n) TEXT? ???? ?. ????? SQL Server 2005??? NVARCHAR(MAX)? ??? ?? ??.
BINARY(n) 08000 ????? ?? ??? ?
VARBINARY(n) 0231-1 ????? ?? ??? ?. SQL Server 2005 ?? ??? 08000
IMAGE(n) 0231-1 SQL Server 2005 ?? ????? 8000?? ?? ?? ???? ??? ??????, SQL Server 2005??? VARBINARY(MAX)? ?? ??? ?? ??
23
SQL Server? ??? ?? ??? ??? ?? (3)

• ??? ??? ??
• ?? ??? ??

??? ?? ??? ? ??
?DATETIME 8 9999/12/31?? ??. ???? 1/1000? ????
SMALLDATETIME 4 2079/1/6?? ??. ???? ? ????
??? ?? ??? ? ??
TIMESTAMP 8 VARBINARY(8)? ????, ?????? ??? ???? ??? ?? ?? ??? ????. SQL Server ????? ???? ???? ??? ? ??? ??.
ROWVERSION 8 TIMESTAMP? ??
SYSNAME 128 NVARCHAR(128)? ????, ?????? ??? ??? ????. ? ?? SQL Server ????? ????.
?CURSOR 1 T-SQL ??? ??? ???.
?TABLE ??? ??? ??. ?? ???? ??? ??.
UNIQUEIDENTIFIER 16 ??(Replication)?? ???? ?????, ???? ???? ?? GUID ?? ???.
SQL_VARIANT ?? ??? ??? ??? ??? ????(??? ??).
?XML XML ???? ???? ?? ??. SQL Server 2005?? ?? ??.
24
SQL Server? ??? ?? ??? ??? ?? (4)

• ??? ?? ??? ??
• ?? ??? ??? ??? ??? ??? ???, ??? ???? ?? ??
• ?? ??
• sp_addtype ????? ????? ??, ??? ??? ??,NULL
  ??,???
• ?? ?
• EXEC sp_addtype 'typeName', 'nchar(10)', 'NULL',
  'dbo
• VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)
• SQL Server 2005?? LOB(Large Object ??? ???)?
  ???? ??? ???? ??
• ???? ???
• ?? ???? ???? ??? ??, ? ??? ?? ???? ??? ?? ???? ??
  ???? ??
• ?????? nchar, nvarchar, ntext ? ??
• ????? N??? ???? ???? ?? ???

25
SQL Server? ??? ?? ??? ??

• ????? ????, ????? ?? ???
• ?? ??
• ??? ?? DECLARE _at_???? ?????
• ??? ? ?? SET _at_???? ????
• ??? ? ?? SELECT _at_????
• ?? ??
• ??? ???? ???.

lt??6gt ??? ??
26
SQL Server? ??? ?? ????? ?? ??? ?? (1)

• CAST() , CONVERT() ???? ?? ??
• ??
• ???? ??? ??? ?? ??
• ?? ??
• CAST ( expression AS ????? (?? ) )
• CONVERT ( ?????(??) , expression , ??? )
• ?? ? ? ??? ??? ??
• SELECT AVG( CAST(amount AS FLOAT) ) AS ??????
  FROM buyTbl
• SELECT AVG( CONVERT(FLOAT, amount) ) AS ??????
  FROM buyTbl
• ??? ??? / ??? ???
• ??? ???
• CAST() ?? CONVERT()? ???? ?? ???? ?
• ??? ???
• CAST() ?? CONVERT()? ???? ??, ????? ???? ?? ????
  ?
• ?? ?? ?? ?? , ?? ?? ?? ?? ?? ?.
• ???? ??? ??? ??? ??? ?? ? ???? ??

27
SQL Server? ??? ?? ????? ?? ??? ?? (2)

• ??? ??
• ??
• ?? ?? ???? ?? ?? ?? ??
• ???? ????, ?? ??, ??/????, ????, ??? ?? ?.
• ??? ??? ?? ?

??? ??
_at__at_VERSION ?? ??? SQL Server ? ?? ??? ????.
GETDATE ??? ??? ??? ????. lt?gt SELECT GETDATE()
ABS ??? ???? ????. lt?gt SELECT ABS(-100)
DB_ID DB_NAME DB? ID ?? DB? ??? ????. lt?gt SELECT DB_ID(N'AdventureWorks') SELECT DB_NAME(6) DB_ID()? AdventureWorks DB? id ??? 6? ????, DB_NAME()? 6? DB? ??? AdventureWorks? ????.
LEFT RIGHT ??/???/?? ???? ??? ???? ????. lt?gt SELECT LEFT('SQL Server 2005', 3), RIGHT('SQL Server 2005', 4) SQL Server 2005?? ??? ???SQL? ??? ???2005? ????.
REPLICATE ???? ??? ??? ????. lt?gt SELECT REPLICATE ('SQL', 5) SQL? 5? ???? ????.
28
lt??7gt MAX??? ??? ??

• ?? ??
• SQL Server 2005? ??? ??? VARCHAR(MAX) ?
  NVARCHAR(MAX)??? ??? ????, ?? ??? ???? ???.
• ?? ??
• MAX ? ???? ??
• ? ??? (1??? ??) ?? ? ?? ?? ??
• CAST(), CONVERT() ??? ??? ???
• ??? ??? ??? ????? ????

29
SQL Server? ??? ?? ????? ?? ??? ?? (3)

• ?? ??
• ??
• ??(?? ??)? ???? ?? ??
• SQL Server 2005?? ??? ??
• RANK(), NTILE(), DENSE_RANK(), ROW_NUMBER() ? ??
• ?? ??
• lt??????gt( ) OVER(
• PARTITION BY ltpartition_by_listgt
• ORDER BY ltorder_by_listgt)
• ?? ??
• ????? ???? ???.
• ?? ??
• ? ??? ??? ?? ROW_NUBER()
• ???? ? ??? ?? ?? PARTITION BY ? ??
• ??? ??? ?? ??? ?? DENSE_RANK()
• ?? ???? ??? ?? ? RANK()

lt??8gt ?? ??
30
?? (Join) INNER JOIN (????) (1)

• ??? ??
• ?? ??? ???? ?? ??? ??? ?????? ??? ?? ?
• INNER JOIN ??
• ?? ? ?? ?? ???.
• ???? ??? ? INNER JOIN? ???? ??.
• ?? ??
• SELECT lt? ??gt
• FROM lt? ?? ???gt
• INNER JOIN lt? ?? ???gt
• ON lt??? ??gt
• WHERE ????
• ?? ? ?? ????? ???LCS?? ???? ?? ??? ??? ???
  ???? ???, ??/??/??? ?? ??
• USE sqlDB
• SELECT
• FROM buyTbl
• INNER JOIN userTbl
• ON buyTbl.userid userTbl.userid
• WHERE buyTbl.userid 'LCS'

31
?? (Join) INNER JOIN (????) (2)

• INNER JOIN ? ??? ? ????

32
?? (Join) INNER JOIN (????) (3)

• ??(Alias)? ??
• ???? ??? ?????? ??? ??? ? ? ??.
• ??? ???? ?
• SELECT buyTbl.userid, userTbl.name,
  buyTbl.prodName, userTbl.addr, userTbl.mobile1
  userTbl.mobile2 AS ???
• FROM buyTbl
• INNER JOIN userTbl
• ON buyTbl.userid userTbl.userid
• ??? ??? ?
• SELECT B.userid, U.name, B.prodName, U.addr,
  U.mobile1 U.mobile2 AS ???
• FROM buyTbl B
• INNER JOIN userTbl U
• ON B.userid U.userid

33
lt??9gt 3? ???? ?? ??

• ?? ??
• ???? ?? ? ?????? ????.
• 3? ???? ?? ??? ???.
• ?? ??? ??

34
?? (Join) OUTER JOIN (?? ??) (1)

• ??
• ????? ??? ??? ???? ?? ?? ???? ???, ????? ??? ????
  ?? ??? ???.
• ?? ??
• SELECT lt? ??gt
• FROM lt? ?? ???(LEFT ???)gt
• ltLEFT RIGHT FULLgt OUTER JOIN lt? ?? ???(RIGHT
  ???)gt
• ON lt??? ??gt
• WHERE ????

35
?? (Join) OUTER JOIN (?? ??) (2)

• ????? ????? ??
• ?? ??? ??
• SELECT U.userid, U.name, B.prodName,
• U.addr, U.mobile1 U.mobile2
• AS ???
• FROM userTbl U
• INNER JOIN buyTbl B
• ON U.userid B.userid
• ORDER BY U.userid
• ????? ??
• SELECT U.userid, U.name, B.prodName,
• U.addr, U.mobile1 U.mobile2
• AS ???
• FROM userTbl U
• LEFT OUTER JOIN buyTbl B
• ON U.userid B.userid
• ORDER BY U.userid

36
lt??10gt 3? ???? ?? ??

• ?? ??
• 3? ???? ?? ?? ??? ???.
• LEFT OUTER JOIN? RIGHT OUTER JOIN ? ??? FULL
  OUTER JOIN ? ??? ???.
• ?? ??
• lt??9gt? ???? ????? ????? ???? ??
• ???? ???? ?? ??? ?? ? LEFT JOIN
• ????? ??? ?? ???? ?? ? OUTER JOIN
• ???? ???? ?? ?? ? ??? ??? ?? ???? ?? ?? ? FULL
  JOIN

37
?? (Join) CROSS JOIN (????)

• ??
• ?? ???? ?? ??? ??? ???? ?? ?? ????? ??.
• ?? ??? ??? ???? ???? ???? ??
• ?? ??? ??
• SELECT FROM buyTbl CROSS JOIN userTbl

38
?? (Join) SELF JOIN (????)

• ??
• ????? ????? ????? ??.
• ?? ??? ??? ?? ???
• ?? ??? ?
• SELECT A.emp AS ???? , B.emp AS ????,
  B.department AS ??????
• FROM empTbl A
• INNER JOIN empTbl B
• ON A.manager B.emp
• WHERE A.emp ???
• ?? ??
• ???? ???? ??????
• -----------------------------
• ??? ??? ???
• (1? ? ???)

?? ?? (EMP) ?? ?? (MANAGER) ?? (DEPARTMENT)
??? ?? (NULL) ?? (NULL)
??? ??? ???
??? ??? ???
??? ??? ???
??? ??? ???
??? ??? ???
??? ??? ???
??? ??? ???
??? ??? ???
??? ??? ???
??? ??? ???
39
?? (Join) UNION, UNION ALL

• ??
• UNION? ? ??? ??? ??? ??? ?.(?, ?? ??)
• UNION ALL ? ???? ???
• UNION? ?? ??

40
SQL ????? IFELSE (??? ?? ??)

• ??
• ?? ??? ??? ???? ? ??? ?? ? ??
• ?? ??
• IF lt?? ???gt
• BEGIN
• SQL???1..
• END
• ELSE
• BEGIN
• SQL???2..
• END

• ?? ?
• DECLARE _at_var1 INT
• -- _at_var1 ?? ??
• SET _at_var1100
• -- ??? ? ??
• IF _at_var1 100
• -- ?? _at_var1 ? 100???,
• BEGIN
• PRINT '_at_var1 ?100 ??'
• END
• ELSE
• BEGIN
• PRINT '_at_var1 ?100????.'
• END

41
SQL ????? CASE (?? ??)

• ??
• ?? ?? ??? ??? ?? ?? ??? ??? ??? IF??? ???? ??? ?
  ??.
• ?? ?
• DECLARE _at_point INT, _at_credit NCHAR(1)
• SET _at_point 100
•  
• SET _at_credit
• CASE
• WHEN (_at_point gt 90) THEN 'A'
• WHEN (_at_point gt 80) THEN 'B'
• WHEN (_at_point gt 70) THEN 'C'
• WHEN (_at_point gt 60) THEN 'D'
• ELSE 'F'
• END
•  
• PRINT '????gt ' CAST(_at_point AS NCHAR(3))
• PRINT '??gt ' _at_credit

42
lt??11gt CASE ? ??

• ?? ??
• ??? ??? ??? ?????/????/???? ??? ???? ??? CASE??
  ??? ??.
• ?? SQL ??
• SELECT U.userid, U.name, sum(priceamount) AS
  ????,
• CASE
• WHEN (sum(priceamount) gt 2000) THEN
  N'?????'
• WHEN (sum(priceamount) gt 1000) THEN
  N'????'
• WHEN (sum(priceamount) gt 1 ) THEN
  N'????'
• ELSE N'????'
• END AS ????
• FROM buyTbl B
• RIGHT OUTER JOIN userTbl U
• ON B.userid U.userid
• GROUP BY U.userid, U.name
• ORDER BY sum(priceamount) DESC

43
SQL ????? WHILE,BREAK,CONTINUE,RETURN

• WHILE
• ?? ?? ??? ?? ???? ???
• CONTINUE? BREAK
• ?? CONTINUE? WHILE?? ????? ????, BREAK? WHILE??
  ????
• ?? ?
• DECLARE _at_i INT
• -- 1?? 100?? ??? ??
• DECLARE _at_hap BIGINT
• -- ?? ?? ??? ??
• SET _at_i 1
• SET _at_hap 0

WHILE (_at_i lt 100) BEGIN IF (_at_i 7 0)
BEGIN PRINT '7??? ' CAST (_at_i AS NCHAR(3))
SET _at_i _at_i 1 CONTINUE
END SET _at_hap _at_hap _at_i IF (_at_hap gt 1000)
BREAK SET _at_i _at_i 1 END PRINT '??'
CAST(_at_hap AS NCHAR(10))
44
SQL ????? GOTO, TRYCATCH

• GOTO
• ??
• ??? ??? ??? ???.
• ??? ???? ?? ?? ??.
• TRY CATCY
• ??
• SQL Server 2005?? ??? ???.
• ??? ???? ? ???? ???
• ?? ??
• BEGIN TRY
• ?? ???? SQL ???
• END TRY
• BEGIN CATCH
• ?? BEGIN TRY?? ??? ???? ??? ??
• END CATCH

• ?? ?
• USE sqlDB
• BEGIN TRY
• INSERT INTO userTbl VALUES('AJH', '???', 1988,
  '??', NULL, NULL, 170)
• PRINT N'??????????.'
• END TRY
• BEGIN CATCH
• PRINT N'???????.'
• END CATCH