IT CookBook, SQL Server로 배우는 데이터베이스 …

1 IT CookBook, SQL Server [ ] ( ) . 136 5 5 ( ) . SQL Server Chapter4. SQL 1. 2. 3. 4.. , , , . , , , . 01. SQL NULL TOP n 01. SQL . SQL DBMS (built-in function) (user-defined funtion).

2 4-1 SQL SQL .. (Configuration Functions) DBMS DATEFIRST, VERSION, SERVERNAME (Conversion Functions) CAST, CONVERT (Cursor Functions) CURSOR_ROWS, CURSOR_STATUS (Date and Time Functions) SYSDATETIME, GETDATE, DAYADD (Mathematical Functions) SIN, COS, ABS, ROUND, CEIL (Metadata Functions) OBJECT_NAME, FILE_ID, SCHEMA_NAME (Security Functions) (role)

3 CURRENT_USER, SUSER_ID, PERMISSIONS (String Functions) CONCAT, SUBSTRING, LEN (System Functions) SQL HOST_NAME, ERROR_NUMBER (System Statistical Functions) CONNECTIONS, CPU_BUSY (Text and Image Functions) TEXTVALIS, TEXTPTR 4-1 SQL Server ABS( ) ABS( )= CEILING( ) CEILING( )=5 FLOOR( ) FLOOR( )=4 ROUND( , m) , m ROUND( , 1)= LOG( ) LOG(10)= POWER( , n) n POWER(2, 3)=8 SQRT( ) ( ) SQRT( )= SIGN( ) -1, 0 0, 1 SIGN( )=1 4-2 ABS : ROUND : 4-1 -78 +78.

4 SELECT ABS(-78), ABS(+78); 4-2 . SELECT ROUND( , 1); 4-3 . SELECT custid " ", ROUND(SUM(saleprice)/COUNT(*), -2) " " FROM Orders GROUP BY custid; CHAR(n) CHAR CHAR(68)=D NCHAR(n) CHAR n NCHAR(68)=D CHARINDEX(str1, str2) INTEGER str2 str1 CHARINDEX( System , Database System )=10 LEFT(str, n) VARCHAR str n LEFT( abcdefg , 2)= ab RIGHT(str, n) VARCHAR str n RIGHT( abcd , 2)= cd LEN(str) INTEGER str LEN( abcdefg )=7 LOWER(str) VARCHAR str LOWER( AbcD )= abcd UPPER(str) VARCHAR str UPPER( AbcD )= ABCD LTRIM(str)

5 VARCHAR str LTRIM( two space here )= two space here RTRIM(str) VARCHAR str RTRIM( two space here )= two space here PATINDEX( %str1% , str2) INTEGER str2 str1 PATINDEX( %en_ure% , I ensure it )=3 REPLACE(str1, str2, str3) VARCHAR str1 str2 str3 REPLACE( abcdefg , cd , dc )= abdcefg REPLICATE(str, n) VARCHAR str n REPLICATE( a , 4)= aaaa REVERSE(str) VARCHAR str REVERSE( abcd )== dcba SPACE(n) VARCHAR n +SPACE(5)+ = SUBSTRING(str, n, m) VARCHAR str n m SUBSTRING( abcdefg , 3, 2)= cd ASCII(str) INTEGER str ASCII( Data )=68 UNICODE(str) INTEGER str UNICODE( Data )=68 4-3 REPLACE : 4-4.

6 SELECT bookid, REPLACE(bookname, ' ', ' ') bookname, publisher, price FROM Book; LEN : ( (byte) ) SUBSTRING : 4-5 . SELECT bookname " ", LEN(bookname) " " FROM Book WHERE publisher=' '; 4-6 ( ) . SELECT SUBSTRING(name, 1, 1) " ", COUNT(*) " " FROM Customer GROUP BY SUBSTRING(name, 1, 1); SYSDATETIME() DATETIME2 SQL Server SYSDATETIME()=2013-03-01 16:39 GETDATE() DATETIME SQL Server GETDATE()=2013-03-01 16.

7 42 DATENAME (datepart, date) VARCHAR date datepart DATENAME(YEAR, 2013-03-01 )= 2013 DATEPART (datepart, date) INTEGER date datepart DATEPART(YEAR, 2013-03-01 )=2013 DAY(date) INTEGER date DAY( 2013-03-01 )=1 MONTH(date) INTEGER date MONTH( 2013-03-01 )=3 YEAR(date) INTEGER date YEAR( 2013-03-01 )=2013 DATEDIFF(datepart, startdate, enddate) INTEGER datepar t startdate enddate DATEDIFF(MONTH, 2013-03-01 , 2013-09-01 )=6 DATEADD(datepart, number, date) DATETIME datepart date number DATEADD(DAY, 5, 2013-03-01 ) = 2013-03-06 00:00 ISDATE(expression) INTEGER 0, 1 ISDATE( 2013-02-30 )=0 4-4 depart datepart year hour hh quarter minute month second dayofyear millisecond ms day microsecond mcs week nanosecond ns weekday dw TZoffset tz 4-5 datepart DATEADD : 4-7 10.

8 SELECT orderid " ", orderdate " ", DATEADD(dd, 10, orderdate) " " FROM Orders; SYSDATETIME : SQL Server 4-8 DBMS . SELECT SYSDATETIME(); /* . */ SELECT DAY(SYSDATETIME()) /* ? */ NULL NULL ? . NULL 0 , ( ), ( ) . NULL . NULL NULL . NULL+ NULL . NULL . SUM, AVG NULL , COUNT 0.

9 NULL NULL Mybook bookid price 1 10000 2 20000 3 NULL SELECT price+100 FROM Mybook WHERE bookid=3; SELECT SUM(price), AVG(price), COUNT(*), COUNT(price) FROM Mybook; SELECT SUM(price), AVG(price), COUNT(*) FROM Mybook WHERE bookid >= 4; NULL NULL IS NULL, IS NOT NULL NULL = IS NULL , NULL IS NOT NULL . Mybook bookid price 1 10000 2 20000 3 NULL SELECT * FROM Mybook WHERE price IS NULL; SELECT * FROM Mybook WHERE price = ''; NULL ISNULL() : NULL Customer 2 NULL.

10 ISNULL( , ) /* NULL ' ' */ UPDATE Customer SET phone=NULL WHERE custid=2; 4-9 , . , . SELECT name " ", ISNULL(phone, ' ') " " FROM Customer; TOP n TOP n . TOP n SQL n .. 4-10 , , . SELECT TOP 2 custid, name, phone FROM Customer ORDER BY name; 02. SELECT FROM WHERE 02 (subquery) ? SQL SQL nested.