변수 종류
Local variables
batch(scope)에 종속
DECLARE @MyVariable int;
SET @MyVariable = 1;
-- Terminate the batch by using the GO keyword.
GO
SET @MyVariable = 2; --X
-- @MyVariable has gone out of scope and no longer exists.
-- This SELECT statement generates a syntax error because it is
-- no longer legal to reference @MyVariable.
batch란 클라이언트 애플리케이션이 단일 단위로 실행하기 위해 SQL Server에 전송한 하나 이상의 T-SQL 문이다.
batch에는 파싱(syntax checking), 분해능(참조된 객체와 열의 존재 여부 확인), 권한 확인, 단위로서의 최적화가 이루어진다.
Transactions과 batches를 혼동하지 마십시오. Transaction는 업무의 원자 단위다. 한 batch는 여러 개의 Transaction을 가질 수 있으며, 한 개의 Transaction을 여러 개의 batch로 부분적으로 제출할 수 있다.
A batch is one or more T-SQL statements sent by a client application to SQL Server for execution as a single unit.
The batch undergoes parsing (syntax checking), resolution (checking the existence of referenced objects and columns), permissions checking, and optimization as a unit.
Dont confuse transactions and batches. A transaction is an atomic unit of work. A batch can have multiple transactions, and a transaction can be submitted in parts as multiple batches.
Global Variables
Table Variables
DECLARE
DECLARE @PrintMessage nvarchar(50);
set @PrintMessage= N'This message was printed on ' + RTRIM(CAST(GETDATE() AS nvarchar(30))) + N'.';
print @PrintMessage;
GO
-- variables
DECLARE @MyVariable int;
SET @MyVariable = 1;
select @MyVariable*10;
go
declare @BirthDate smalldatetime;
DECLARE @LastName varchar(30), @FirstName varchar(20), @StateProvince char(2);
DECLARE @AvgWeight decimal(8,2), @BikeCount int
DECLARE @Iteration Integer = 1
go
SQL Server provides commands that are not Transact-SQL statements, but are recognized by
the sqlcmd and osql utilities and SQL Server Management Studio Code Editor. These commands
can be used to facilitate the readability and execution of batches and scripts.
BEGIN...END
기존 언어에서 괄호 역할은 한다고 생각하면 됨
Transact-SQL 문 그룹을 실행할 수 있도록 일련의 Transact-SQL 문을 묶습니다.
BEGIN 및 END는 흐름 제어 언어 키워드입니다.BEGIN { sql_statement | statement_block } END
IF...ELSE
IF Boolean_expression { sql_statement | statement_block } [ ELSE { sql_statement | statement_block } ]
USE AdventureWorks2019;
GO
IF
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
PRINT 'There are more than 5 Touring-3000 bicycles.'
ELSE PRINT 'There are 5 or less Touring-3000 bicycles.' ;
GO
USE AdventureWorks2019;
GO
DECLARE @AvgWeight decimal(8,2), @BikeCount int
IF
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
BEGIN
SET @BikeCount =
(SELECT COUNT(*)
FROM Production.Product
WHERE Name LIKE 'Touring-3000%');
SET @AvgWeight =
(SELECT AVG(Weight)
FROM Production.Product
WHERE Name LIKE 'Touring-3000%');
PRINT 'There are ' + CAST(@BikeCount AS varchar(3)) + ' Touring-3000 bikes.'
PRINT 'The average weight of Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.';
END
ELSE
BEGIN
SET @AvgWeight =
(SELECT AVG(Weight)
FROM Production.Product
WHERE Name LIKE 'Touring-3000%' );
PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.' ;
END ;
GOwhile
--while loop
DECLARE @Iteration Integer = 1
WHILE @Iteration <11
BEGIN
print cast(3 as varchar(1)) + N' X ' + cast(@Iteration as varchar(1)) + ' = ' + cast(@Iteration * 3 as varchar(2))
SET @Iteration += 1
ENDCase
ProductNumber 컬럼과 Category란 이름의 컬럼에 ProductLine 에 따라 그 값을 정해준 것 그리고 Name을 출력해준다. (+ProductNumber의 순서에 따라)
USE AdventureWorks2019;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO
SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
GO SalariedFlag가 1인건 ID를 내림차순으로 정렬, 0이면 오름차순으로 정렬함.
Built-in Functions
- deterministic: Functions are deterministic when they always return the same result any time they are called by using a specific set of input values.
결정론적: 함수는 특정 입력 값 집합을 사용하여 호출될 때마다 항상 동일한 결과를 반환할 때 결정론적이다. - nondeterministic: Functions are nondeterministic when they could return different results every time they are called, even with the same specific set of input values.
비결정론적: 함수는 동일한 특정 입력 값 집합에서도 호출될 때마다 다른 결과를 반환할 수 있을 때 비결정론적이다. - Aggregate functions : Aggregate functions perform a calculation on a set of values and return a single value.
집계함수 : 집계함수는 값 집합에 대한 계산을 수행하고 단일 값을 반환한다. - Analytic functions : Analytic functions compute an aggregate value based on a group of rows. However, unlike aggregate functions, analytic functions can return multiple rows for each group.
분석함수 : 분석함수는 행의 그룹을 기초로 집계값을 계산한다. 그러나 집계함수와 달리 분석함수는 각 그룹에 대해 여러 행을 반환할 수 있다. - Ranking functions : Ranking functions return a ranking value for each row in a partition.
순위 함수 : 순위 함수는 파티션의 각 행에 대한 순위 값을 반환한다. - Rowset functions : Rowset functions Return an object that can be used like table references in an SQL statement.
Rowset 함수 : Rowset 함수 SQL 문의 테이블 참조처럼 사용할 수 있는 객체를 반환한다. - Scalar functions : return a single value. Categories of scalar functions are shown in the following Table.
스칼라 함수 : 단일 값을 반환한다. 스칼라 함수의 범주는 다음 표와 같다.
Configuration Functions
- 비결정적
- 다음 스칼라 함수는 현재 구성 옵션 설정에 대한 정보를 반환합니다.
- @@ 이거 2개 붙으면 시스템 함수같이 중요한걸 뜻하는 것?
EX) @@LANGID, @@LANGUAGE
SELECT @@LANGID AS 'Language ID', @@LANGUAGE AS 'Language Name';
SET LANGUAGE 'Italian'
SELECT @@LANGID AS 'Language ID', @@LANGUAGE AS 'Language Name';
이런식으로 구성 옵션을 바꾸거나 볼 수 있다.
Date and Time Functions
ex) DATEDIFF (year대신 YY이렇게 써도된다)
SELECT DATEDIFF(year, ’2005-12-31 23:59:59.9999999’, ’2006-01-01
00:00:00.0000000’);
SELECT DATEDIFF(quarter, ’2005-12-31 23:59:59.9999999’, ’2006-01-01
00:00:00.0000000’);
SELECT DATEDIFF(month, ’2005-12-31 23:59:59.9999999’, ’2006-01-01
00:00:00.0000000’);
SELECT DATEDIFF(day, ’2005-12-31 23:59:59.9999999’, ’2006-01-01
00:00:00.0000000’);
SELECT DATEDIFF(week, ’2005-12-31 23:59:59.9999999’, ’2006-01-01
00:00:00.0000000’);
SELECT DATEDIFF(hour, ’2005-12-31 23:59:59.9999999’, ’2006-01-01
00:00:00.0000000’);
SELECT DATEDIFF(minute, ’2005-12-31 23:59:59.9999999’, ’2006-01-01
00:00:00.0000000’);
SELECT DATEDIFF(second, ’2005-12-31 23:59:59.9999999’, ’2006-01-01
00:00:00.0000000’);
SELECT DATEDIFF(millisecond, ’2005-12-31 23:59:59.9999999’, ’2006-01-01
00:00:00.0000000’);MathFunctions.sql
--ABS
DECLARE @i int;
SET @i = -21474836;
SELECT ABS(@i);
GO
--degrees
SELECT 'The number of degrees in PI/2 radians is: ' +
CONVERT(varchar, DEGREES((PI()/2)));
GO
-- rand
DECLARE @counter smallint;
SET @counter = 1;
WHILE @counter < 15
BEGIN
print round(RAND()*1000, 0)
SET @counter = @counter + 1
END;
GO
--
SET NOCOUNT OFF;
DECLARE @cos float;
SET @cos = -1.0;
SELECT 'The ACOS of the number is: ' + CONVERT(varchar, ACOS(@cos));
--
/* The first value will be -1.01. This fails because the value is
outside the range.*/
DECLARE @angle float
SET @angle = -1.0
SELECT 'The ASIN of the angle is: ' + CONVERT(varchar, ASIN(@angle))
GO
-- The next value is -1.00.
DECLARE @angle float
SET @angle = -1.00
SELECT 'The ASIN of the angle is: ' + CONVERT(varchar, ASIN(@angle))
GO
-- The next value is 0.1472738.
DECLARE @angle float
SET @angle = 0.1472738
SELECT 'The ASIN of the angle is: ' + CONVERT(varchar, ASIN(@angle))
GO
--
SELECT 'The ATAN of -45.01 is: ' + CONVERT(varchar, ATAN(-45.01))
SELECT 'The ATAN of -181.01 is: ' + CONVERT(varchar, ATAN(-181.01))
SELECT 'The ATAN of 0 is: ' + CONVERT(varchar, ATAN(0))
SELECT 'The ATAN of 0.1472738 is: ' + CONVERT(varchar, ATAN(0.1472738))
SELECT 'The ATAN of 197.1099392 is: ' + CONVERT(varchar, ATAN(197.1099392))
GO
--
DECLARE @x float = 35.175643, @y float = 129.44;
SELECT 'The ATN2 of the angle is: ' + CONVERT(varchar, ATN2(@y, @x));
GO
--
SELECT CEILING($123.75), CEILING($-123.45), CEILING($0.0);
GO
--
SELECT TAN(PI()/2)
--
DECLARE @angle float;
SET @angle = 14.78;
SELECT 'The COS of the angle is: ' + CONVERT(varchar,COS(@angle));
GO
--
DECLARE @angle float;
SET @angle = 45.175643;
SELECT 'The SIN of the angle is: ' + CONVERT(varchar,SIN(@angle));
GO
--
DECLARE @angle float;
SET @angle = 124.1332;
SELECT 'The COT of the angle is: ' + CONVERT(varchar,COT(@angle));
GO
--
DECLARE @var float
SET @var = 10
SELECT 'The EXP of the variable is: ' + CONVERT(varchar,EXP(@var))
GO
--
SELECT FLOOR(123.76), FLOOR(-123.45), FLOOR($123.45);
go
--
DECLARE @var float;
SET @var = 145.175643;
SELECT 'The LOG10 of the variable is: ' + CONVERT(varchar,LOG10(@var));
GO
--
DECLARE @var float = 10;
SELECT 'The LOG of the variable is: ' + CONVERT(varchar, LOG(@var));
GO
--
SELECT PI();
GO
--
DECLARE @input1 float;
DECLARE @input2 float;
SET @input1= 2;
SET @input2 = 2.5;
SELECT POWER(@input1, 3) AS Result1, POWER(@input2, 3) AS Result2;
--
-- First value is -45.01.
DECLARE @angle float
SET @angle = -45.01
SELECT 'The RADIANS of the angle is: ' +
CONVERT(varchar, RADIANS(@angle))
GO
-- Next value is -181.01.
DECLARE @angle float
SET @angle = -181.01
SELECT 'The RADIANS of the angle is: ' +
CONVERT(varchar, RADIANS(@angle))
GO
-- Next value is 0.00.
DECLARE @angle float
SET @angle = 0.00
SELECT 'The RADIANS of the angle is: ' +
CONVERT(varchar, RADIANS(@angle))
GO
-- Next value is 0.1472738.
DECLARE @angle float
SET @angle = 0.1472738
SELECT 'The RADIANS of the angle is: ' +
CONVERT(varchar, RADIANS(@angle))
GO
-- Last value is 197.1099392.
DECLARE @angle float
SET @angle = 197.1099392
SELECT 'The RADIANS of the angle is: ' +
CONVERT(varchar, RADIANS(@angle))
GO
-- ROUND => 3이면 소수점 3자리까지, -1이면 일의자리에서 반올림한다.
SELECT ROUND(123.9994, 3), ROUND(123.9995, -1);
GO
-- SIGN함수는 계산한 값의 결과가 0 / 또는 양수 음수냐에 따라서 0 / 1 / -1을 반환 해주는 함수이다.
DECLARE @value real
SET @value = -1
WHILE @value < 2
BEGIN
SELECT SIGN(@value)
SET NOCOUNT ON
SELECT @value = @value + 1
SET NOCOUNT OFF
END
SET NOCOUNT OFF
GO
--
DECLARE @myvalue float;
SET @myvalue = 1.00;
WHILE @myvalue < 10.00
BEGIN
print SQRT(@myvalue);
SET @myvalue = @myvalue + 1
END;
GO
--
DECLARE @h float, @r float;
SET @h = 5;
SET @r = 1;
SELECT PI()* SQUARE(@r)* @h AS 'Cyl Vol'
--
- SET NOCOUNT ON
영향 받은 행 수를 나타내는 메시지가 반환되지 않는다.- SET NOCOUNT OFF
SELECT, INSERT, UPDATE, DELETE 의 명령을 실행하면 영향 받은 행 수를 나타낸다.
출처 - https://lovedb.tistory.com/221
String Functions
SELECT ASCII('A') AS A, ASCII('B') AS B,
ASCII('a') AS a, ASCII('b') AS b,
ASCII(1) AS [1], ASCII(2) AS [2];
--
SET TEXTSIZE 0;
-- Create variables for the character string and for the current
-- position in the string.
DECLARE @position int, @string char(8);
-- Initialize the current position and the string variables.
SET @position = 1;
SET @string = 'New Moon';
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)), CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END;
GO
--
DECLARE @document varchar(64);
SELECT @document = 'Reflectors are vital safety' +
' components of your bicycle.';
SELECT CHARINDEX('bicycle', @document);
GO
--
CREATE TABLE #temp (
emp_name nvarchar(200) NOT NULL,
emp_middlename nvarchar(200) NULL,
emp_lastname nvarchar(200) NOT NULL
);
INSERT INTO #temp VALUES( 'Name', NULL, 'Lastname' );
SELECT CONCAT( emp_name, emp_middlename, emp_lastname ) AS Result
FROM #temp;
--
SELECT CONCAT_WS( ' - ', database_id, recovery_model_desc, containment_desc) AS DatabaseInfo
FROM sys.databases;
SELECT CONCAT_WS(',','1 Microsoft Way', NULL, NULL, 'Redmond', 'WA', 98052) AS Address;
--
-- Returns a DIFFERENCE value of 4, the least possible difference.
SELECT SOUNDEX('Green'), SOUNDEX('Greene'), DIFFERENCE('Green','Greene');
GO
-- Returns a DIFFERENCE value of 0, the highest possible difference.
SELECT SOUNDEX('Blotchet-Halls'), SOUNDEX('Greene'), DIFFERENCE('Blotchet-Halls', 'Greene');
GO
--
DECLARE @d DATETIME = '10/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'
,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result'
,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'
,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result';
SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result'
,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result'
,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result'
,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result';
---
SELECT LEFT('abcdefg',2);
GO
--
DECLARE @v1 varchar(40),
@v2 nvarchar(40);
SELECT
@v1 = 'Test of 22 characters ',
@v2 = 'Test of 22 characters ';
SELECT LEN(@v1) AS [varchar LEN] , DATALENGTH(@v1) AS [varchar DATALENGTH];
SELECT LEN(@v2) AS [nvarchar LEN], DATALENGTH(@v2) AS [nvarchar DATALENGTH];
--
-- Uses AdventureWorks
use DreamHome
SELECT LOWER(SUBSTRING(fName, 1, 20)) AS Lower,
UPPER(SUBSTRING(gName, 1, 20)) AS Upper
FROM Client
--
DECLARE @string_to_trim varchar(60);
SET @string_to_trim = ' 5 spaces are at the beginning of this string.';
SELECT
@string_to_trim AS 'Original string',
LTRIM(@string_to_trim) AS 'Without spaces';
GO
-- 위에 문자의 유니코드 값 숫자로 나타낸거, 아래 문자로 나타낸거
DECLARE @nstring nchar(8);
SET @nstring = 'Kdbenhavn';
SELECT UNICODE(SUBSTRING(@nstring, 2, 1)),
NCHAR(UNICODE(SUBSTRING(@nstring, 2, 1)));
GO
--
SELECT PATINDEX('%ter%', 'interesting data');
--
SELECT QUOTENAME('abc[]def');
--
SELECT REPLACE('abcdefghicde','cde','fff');
GO
--
IF EXISTS(SELECT name FROM sys.tables
WHERE name = 't1')
DROP TABLE t1;
GO
CREATE TABLE t1
(
c1 varchar(3),
c2 char(3)
);
GO
INSERT INTO t1 VALUES ('2', '2'), ('37', '37'),('597', '597');
GO
SELECT REPLICATE('0', 3 - DATALENGTH(c1)) + c1 AS 'Varchar Column',
REPLICATE('0', 3 - DATALENGTH(c2)) + c2 AS 'Char Column'
FROM t1;
GO
--
DECLARE @myvar varchar(10);
SET @myvar = 'sdrawkcaB';
SELECT REVERSE(@myvar) AS Reversed ;
GO
SELECT STR(123.45, 6, 1);
GO
--
SELECT STRING_AGG (gName, CHAR(13)) AS csv
FROM Client;
--
DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'
SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';
--
SELECT STUFF('abcdef', 2, 3, 'ijklmn');
GO
--
SELECT name, SUBSTRING(name, 1, 1) AS Initial ,
SUBSTRING(name, 3, 2) AS ThirdAndFourthCharacters
FROM sys.databases
WHERE database_id < 5;
--
DECLARE @nstring nchar(12);
SET @nstring ='akergatan 24';
SELECT UNICODE(@nstring), NCHAR(UNICODE(@nstring)); CHARINDEX 중요 => 해당하는 단어가 나타나는 처음 나타나는 인덱스를 반환한다. 단어만 앞에 붙어있다면 뒤에 다른 글자가 붙어도 상관없음
테이블을 만들때 앞에 #을 붙이면 임시테이블을 만들 수있다.
CREATE TABLE #temp ( emp_name nvarchar(200) NOT NULL, emp_middlename nvarchar(200) NULL, emp_lastname nvarchar(200) NOT NULL ); INSERT INTO #temp VALUES( 'Name', NULL, 'Lastname' ); SELECT CONCAT( emp_name, emp_middlename, emp_lastname ) AS Result FROM #temp;
CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
argument들 사이를 separator를 붙여서 연결해준다.
SOUNDEX ( character_expression )
두 문자열의 유사성을 평가하기 위한 4자의 SOUNDEX 코드를 반환합니다.
DIFFERENCE ( character_expression , character_expression ) 이 함수는 두 문자 식의 SOUNDEX() 값의 차이를 측정하는 정수 값을 반환합니다.
FORMAT
DECLARE @d DATETIME = '10/01/2011'; SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result' ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result' ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result' ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result'; SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result' ,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result' ,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result' ,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result'; ---
- PATINDEX ( '%pattern%' , expression )
유효한 모든 텍스트 및 문자 데이터 형식으로 지정한 식에서 패턴이 처음 나타나는 시작 위치를 반환하거나 패턴을 찾지 못하면 0을 반환합니다.
- QUOTENAME ( 'character_string' [ , 'quote_character' ] )
입력 문자열이 유효한 SQL Server 구분 식별자가 되도록 구분 기호가 추가된 유니코드 문자열을 반환합니다.
SELECT QUOTENAME('abc[]def');
결과[abc[]]def] (1 row(s) affected)abc[]def 문자열에서 오른쪽 대괄호는 이중으로 사용되었는데, 이것은 이스케이프 문자를 나타내기 위한 것입니다.
- REPLACE ( string_expression , string_pattern , string_replacement )
ex)SELECT REPLACE('abcdefghicde','cde','xxx'); GO결과
abxxxfghixxx (1 row(s) affected)
- REPLICATE ( string_expression , integer_expression )
지정한 횟수만큼 문자열 값을 반복합니다.
- STRING_AGG ( expression, separator ) [ <order_clause> ]
문자열 식의 값을 연결하고 그 사이에 구분 기호 값을 추가합니다. 구분 기호는 문자열 끝에 추가되지 않습니다.
ex)SELECT STRING_AGG (fName, CHAR(126)) AS csv FROM Client;
여기서 126은 유니코드의 문자를 뜻한다. 126을 문자로 하면 '~'이다.
- STUFF ( character_expression , start , length , replaceWith_expression )
STUFF 함수는 다른 문자열에 문자열을 삽입합니다. 이 함수는 지정된 시작 위치와 문자 수에 따라 첫 번째 문자열의 문자를 삭제하고 두 번째 문자열을 시작 위치에 삽입합니다.SELECT STUFF('abcdef', 2, 3, 'ijklmn'); GO다음 예에서는 첫 문자열 abcdef에서 2 위치의 b부터 세 문자를 삭제하고 삭제 지점에 두 번째 문자열을 삽입하여 만든 문자열을 반환합니다.
aijklmnef (1 row(s) affected)
sys.objects, sys.parameters
select * from sys.objects고유하게 컴파일된 스칼라 사용자 정의 함수를 포함하여 데이터베이스 내에서 생성되는 각 사용자 정의 스키마 범위 개체에 대한행을 포함합니다.
select * from sys.all_columns사용자 정의 개체 및 시스템 개체에 속하는 모든 열을 보여 줍니다.
마이크로소프트 데이터베이스 예제 페이지
https://github.com/Microsoft/sql-server-samples/releases
마이크로소프트 데이터베이스 예제 파일
https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2019.bak
