Transcription of 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) . 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) CURRENT_USER, SUSER_ID, PERMISSIONS (String Functions) CONCAT, SUBSTRING, LEN (System Functions)
2 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 . SELECT ABS(-78), ABS(+78); 4-2.
3 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) VARCHAR str LTRIM( two space here )= two space here RTRIM(str) VARCHAR str RTRIM( two space here )
4 = 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.
5 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: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.
6 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 .. SELECT orderid " ", orderdate " ", DATEADD(dd, 10, orderdate) " " FROM Orders; SYSDATETIME : SQL Server 4-8 DBMS . SELECT SYSDATETIME(); /* . */ SELECT DAY(SYSDATETIME()) /* ? */ NULL NULL ?
7 NULL 0 , ( ), ( ) . NULL . NULL NULL . NULL+ NULL . NULL . SUM, AVG NULL , COUNT 0 . 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.
8 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 . 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) ?
9 SQL SQL nested .. (main query) (sub query) .. SELECT SUM(saleprice) FROM Orders WHERE custid = (SELECT custid FROM Customer WHERE name = ' ') (main query) (sub query) 4-2 02 SELECT scalar subquery SELECT . FROM inline view, table subquery FROM (view) . WHERE nested subquery, predicate subquery WHERE .. 4-6 SELECT (scalar subquery) ? SELECT , , . , SELECT UPDATE SET.
10 / . 4-3 SELECT custid, ,SUM(saleprice) FROM Orders od GROUP BY custid (SELECT name FROM Customer cs WHERE ) SELECT 4-11 . , . SELECT ( SELECT name FROM Customer cs WHERE ) "name", SUM(saleprice) "total" FROM Orders od GROUP BY ; SELECT SELECT custid, SUM(saleprice) totamt FROM Orders od GROUP BY custid ; SELECT (SELECT name FROM Customer cs WHERE = ) name, SUM(saleprice) totamt FROM Orders od GROUP BY ; SELECT name FROM Customer cs WHERE = SELECT name FROM Customer cs WHERE = Custid 1 ?
