C# ????????????? ??????????: T-SQL ??????????. ??????? #44
????????? ?????? (Data Types)
???????? ??????
-- ??????????:
DECLARE @????? INT = 25;
DECLARE @???? DECIMAL(10,2) = 99.99;
DECLARE @??????? MONEY = 1500.50;
???????? ??????
-- ??????????:
DECLARE @???? CHAR(5) = '12345'; -- ???????????
DECLARE @?????? VARCHAR(50) = 'John'; -- ????????
DECLARE @????? NVARCHAR(50) = '????????'; -- ???????
??????? ??????
-- ??????????:
DECLARE @?????????_??? DATE = '1995-05-15';
DECLARE @??? TIME = '14:30:00';
DECLARE @???????? DATETIME = '2024-03-15 14:30:00';
???????? (Variables)
????????? ?????? ?? ??????????
-- ??????? ??????
DECLARE @?????????_?????? NVARCHAR(50);
-- ???????????? ????????
SET @?????????_?????? = '??????';
-- ?????? ?? ???????? ??????????
DECLARE @????? INT = 20;
????????? ??????????
-- ????????? ??????? ??????????
DECLARE @????1 INT = 85;
DECLARE @????2 INT = 90;
DECLARE @??????? DECIMAL(4,2);
-- ????????
SET @??????? = (@????1 + @????2) / 2.0;
??????????? (Operators)
???????????? ???????????
DECLARE @? INT = 10;
DECLARE @? INT = 5;
SELECT
@? + @? AS ????, -- ????????
@? - @? AS ???????, -- ?????????
@? * @? AS ????????, -- ??????????
@? / @? AS ????????; -- ??????
????????? ???????????
DECLARE @??????1 INT = 10;
DECLARE @??????2 INT = 20;
IF @??????1 = @??????2 -- ??????
PRINT '?????';
IF @??????1 > @??????2 -- ??????
PRINT '?????';
IF @??????1 < @??????2 -- ?????????
PRINT '????????';
IF @??????1 <> @??????2 -- ?? ?????
PRINT '?? ?????';
???????? ???????????
DECLARE @????? INT = 25;
DECLARE @??????? INT = 2000;
-- AND (??)
IF @????? > 18 AND @??????? > 1000
PRINT '??????????? ????? ????????';
-- OR (??)
IF @????? < 18 OR @??????? < 1000
PRINT '?????????????? ?? ?????? ???????';
-- NOT (???)
IF NOT (@????? < 18)
PRINT '???????????';
??????? ????????????? (Control Flow)
IF...ELSE
DECLARE @???? INT = 85;
IF @???? >= 71
BEGIN
PRINT '???????';
END
ELSE
BEGIN
PRINT '??? ???????';
END
WHILE ?????
DECLARE @??????? INT = 1;
WHILE @??????? <= 5
BEGIN
PRINT '????????: ' + CAST(@??????? AS VARCHAR(2));
SET @??????? = @??????? + 1;
END
CASE ????????????
DECLARE @???????? INT = 85;
SELECT
CASE
WHEN @???????? >= 91 THEN 'A'
WHEN @???????? >= 81 THEN 'B'
WHEN @???????? >= 71 THEN 'C'
ELSE 'F'
END AS ???????_????????;
?????????? ?? ????????????? (Queries and Subqueries)
??????? SELECT ????????
-- ???????? ?????????
SELECT ??????, ?????, ?????
FROM ??????????
WHERE ????? > 20
ORDER BY ?????;
??????????? (Subquery)
-- ???????????? ????????
SELECT ??????, ???????
FROM ?????????????
WHERE ??????? > (
SELECT AVG(???????)
FROM ?????????????
);
?????????? (Joins)
INNER JOIN
SELECT ?.??????, ?.??????????
FROM ?????????? ?
INNER JOIN ??????? ?
ON ?.??????_ID = ?.??????_ID;
LEFT JOIN
SELECT ?.??????, ?.????
FROM ?????????? ?
LEFT JOIN ?????????? ?
ON ?.?????????_ID = ?.?????????_ID;
?????????? ?????????? (Exception Handling)
TRY...CATCH
BEGIN TRY
-- ????, ??????? ???????? ???????? ?????????
DECLARE @?????? INT = 10/0;
END TRY
BEGIN CATCH
-- ???????? ??????????
SELECT
ERROR_NUMBER() AS ????????_??????,
ERROR_MESSAGE() AS ????????_??????;
END CATCH
?? ????????: ??????????? ??????? ???????
-- ????????? ??????
CREATE TABLE ?????????? (
?????????_ID INT PRIMARY KEY,
?????? NVARCHAR(50),
????? NVARCHAR(50),
?????????_?????? DATE
);
CREATE TABLE ?????????? (
?????????_ID INT PRIMARY KEY,
?????????_ID INT,
?????? NVARCHAR(50),
???? INT
);
-- ??????????? ???????? ?? ??????????
BEGIN TRY
-- ????????? ????????
INSERT INTO ?????????? VALUES
(1, '??????', '????????', '2000-05-15');
-- ????????? ????????
INSERT INTO ?????????? VALUES
(1, 1, '??????????', 95);
-- ??????? ????? ????????
SELECT ??.??????,
AVG(???.????) AS ???????_????
FROM ?????????? ??
JOIN ?????????? ???
ON ??.?????????_ID = ???.?????????_ID
GROUP BY ??.??????;
END TRY
BEGIN CATCH
SELECT '????? ???????: ' + ERROR_MESSAGE();
END CATCH;
?? ?????????? ???????: ?????? ???????? ???????
???????? ??????
???????? ?????? ???????? ??????? ???????, ??????? ??????? ?????? ???????????:
????????? ????? ?????????
-- ??????????? ??????
CREATE TABLE ?????????? (
?????????_ID INT PRIMARY KEY,
?????????? NVARCHAR(100),
???? DECIMAL(10,2),
????????? INT,
????????? NVARCHAR(50)
);
-- ?????????????? ??????
CREATE TABLE ????????????? (
????????????_ID INT PRIMARY KEY,
?????? NVARCHAR(50),
????? NVARCHAR(50),
??????? NVARCHAR(100),
????????????_?????? DATE
);
-- ?????????? ??????
CREATE TABLE ????????? (
????????_ID INT PRIMARY KEY,
????????????_ID INT,
?????? DATETIME,
??????? NVARCHAR(20)
);
????????????????? ?????????
??????????? ??????
?????????????? ??????
?????????? ??????????
??????????? ????????
-- ?????????: ????? ???????? ??????????
CREATE PROCEDURE ???????_???????
@????????????_ID INT,
@?????????_ID INT,
@????????? INT
AS
BEGIN
BEGIN TRY
-- ?????????? ??????
DECLARE @????????_????????? INT;
SELECT @????????_????????? = ?????????
FROM ??????????
WHERE ?????????_ID = @?????????_ID;
IF @????????_????????? >= @?????????
BEGIN
-- ????????? ???????
INSERT INTO ????????? (????????????_ID, ??????, ???????)
VALUES (@????????????_ID, GETDATE(), '???????????_????????');
-- ?????????? ??????
UPDATE ??????????
SET ????????? = ????????? - @?????????
WHERE ?????????_ID = @?????????_ID;
PRINT '??????? ?????????? ???????!';
END
ELSE
PRINT '???????????? ??????!';
END TRY
BEGIN CATCH
PRINT '??????? ???????? ???????????: ' + ERROR_MESSAGE();
END CATCH
END
???????????
1. ??????? ????????
- ?????????? ????????????? ?????? ??????? ???????
- ??????? ???????? ?????? ???????
2. ????????????? ???????
- ???????? ????????????
- ?????????? ????????????
- ???????? ??????????????
????????????? ?????????? ??
1. ????????? ??????? ??????????:
- ??????????? ?????????? NVARCHAR ??????? ???????????????
- ????????????? ???????? ?????????? ????? ????
- ????????????? ?????????? DATE ?? DATETIME
2. ????????? ?????????????:
- ???????? ??????? ????????
- ???????? ??????????? ?????? ????
- ?????????? DECLARE ?????? ??????
3. ???????????? ?????????????:
- ????????? ???????? ????????? ??????
- ?????????? ????????? ????? ???????????????????
- ????????? ????? ??????? ???????????