آیا میدانید اس کیو الی !؟!؟

[hamed_Arfaee]

سلام

بعضی (توابع،دستورات،روال ها،و قابلیتها) یی در اس کیو ال سرور وجوددارد که دونستنشون میتونه مفید باشه .
تصمیم گرفتم بعضیاشونو اینجا معرفی کنم تا دوستان هم در جریانشون باشن .

اولین مورد :
تولید شماره ردیف ردیفی و گروهی .

کد:

ROW_NUMBER()

که ساختارش به این صورت هست :

کد:

ROW_NUMBER ( )
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

کاربرد : برای گذاشتن شماره ردیف برای انتخاب هامون استفاده میشه .
مثال :

کد:

select ROW_NUMBER ( ) OVER (ORDER BY SalesYTD DESC) as Row ,* from tbl1

کد:

Row FirstName    LastName               SalesYTD
--- -----------  ---------------------- -----------------
1   Linda        Mitchell               4251368.54
2   Jae          Pak                    4116871.22
3   Michael      Blythe                 3763178.17
4   Jillian      Carson                 3189418.36
5   Ranjit       Varkey Chudukatil      3121616.32
6   José         Saraiva                2604540.71
7   Shu          Ito                    2458535.61
8   Tsvi         Reiter                 2315185.61
9   Rachel       Valdez                 1827066.71
10  Tete         Mensa-Annan            1576562.19
11  David        Campbell               1573012.93
12  Garrett      Vargas                 1453719.46
13  Lynn         Tsoflias               1421810.92
14  Pamela       Ansman-Wolfe           1352577.13

مثال 2

کد:

select ROW_NUMBER ( ) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) as Row ,* from tbl1

کد:

FirstName  LastName             TerritoryName        SalesYTD      Row
---------  -------------------- ------------------   ------------  ---
Lynn       Tsoflias             Australia            1421810.92    1
José       Saraiva              Canada               2604540.71    1
Garrett    Vargas               Canada               1453719.46    2
Jillian    Carson               Central              3189418.36    1
Ranjit     Varkey Chudukatil    France               3121616.32    1
Rachel     Valdez               Germany              1827066.71    1
Michael    Blythe               Northeast            3763178.17    1
Tete       Mensa-Annan          Northwest            1576562.19    1
David      Campbell             Northwest            1573012.93    2
Pamela     Ansman-Wolfe         Northwest            1352577.13    3
Tsvi       Reiter               Southeast            2315185.61    1
Linda      Mitchell             Southwest            4251368.54    1
Shu        Ito                  Southwest            2458535.61    2
Jae        Pak                  United Kingdom       4116871.22    1

و یا میتونیم انتخاب صفحه ای انجام بدیم :

کد:

GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber
    FROM Sales.SalesOrderHeader
)
SELECT SalesOrderID, OrderDate, RowNumber  
FROM OrderedOrders
WHERE RowNumber BETWEEN 3 AND 8;

کد:

3   Michael      Blythe                 3763178.17
4   Jillian      Carson                 3189418.36
5   Ranjit       Varkey Chudukatil      3121616.32
6   José         Saraiva                2604540.71
7   Shu          Ito                    2458535.61
8   Tsvi         Reiter                 2315185.61

[babyy]

اون مثال دو رو بیشتر باز میکنی آقا حامد جان؟

[hamed_Arfaee]

اون مثال دو رو بیشتر باز میکنی آقا حامد جان؟

دقیقا شبیه اینه که برای Group By شماره ردیف میزنی :

اول بر اساس ستون SalesYTD مرتب کرده بعد از اون بر اساس ستون TerritoryName شماره ردیف زده .یعنی از هر کدوم چند تا داریم .

[hamed_Arfaee]

مرتب سازی شرطی

فرض کنیم میخواهیم لیست کارمندان شرکت رو بصورت مرتب شده نشون بدیم ولی اسم مدیر اول لیست باشه :

کد:

SELECT lastName FROM tblperson
ORDER BY
CASE  WHEN lastName=N'Arfaee' THEN  NULL
else
    lastName
END

[hamed_Arfaee]

دریافت خروجی کامل بجای پیام

وقتی کوئری زیر رو اجرا کنیم :

کد:

Delete from table1 where id>10

خروجی یه چیزی شبیه این خواهد بود :
X row(s) affected
ولی می خواهیم بدونیم اون ایکس رکوردی که پاک شدن کدوم ها بودن ؟
یه راهش اینه که تریگری بنویسیم و در جدول موقتی ثبت کنیم که چیا پاک شدن .
ولی اصل راهش اینه :

کد:

DELETE  FROM test output deleted.*  WHERE id>0
UPDATE test SET msg='1' output deleted.* WHERE id>0
INSERT into test output inserted.*  VALUES ('salam')

نکته : وقتی جدولی آپدیت میشه اطلاعات قبل از آپدیت در جدول Deleted ثبت میشه و اطلاعات جدید در جدول Inserted

[hamed_Arfaee]

تجمیع سطر ها در یک و یا چند سطر

می خواهیم خروجی Group by رو کاربردی تر کنیم ولی از Group by استفاده نکنیم .

مثال :
جدول زیر رو فرض کنید :

کد:

id     name
--     -------
1     Ali
2     naser
3     hamed
4     zahra
5     hanie
6     farzin
7     sara
8     mehdi
9     hadi
10    esmat
که حال این کوئری هست :
Select * from names

حالا میخواهیم اینا رو 5 تا 5 تا جدا کنیم و خروجیمون تو 2 ردیف باشه:

کد:

DECLARE @NameList1 nvarchar(Max)
DECLARE @NameList2 nvarchar(Max)

SELECT @NameList1 = COALESCE(@NameList1 + ', ', '') +
    names
FROM TableName
WHERE ID > 0 AND ID < 6

SELECT @NameList2 = COALESCE(@NameList2 + ', ', '') +
    names
FROM TableName
WHERE ID > 5 AND ID < 11

SELECT @NameList1 Combined
UNION ALL
SELECT @NameList2

که خروجیش میشه :

کد:

Combined
------------------------------------
Ali,naser,hamed,zahra,hanie
farzin,sara,mehdi,hadi,esmat

[hamed_Arfaee]

جستجوی یک الگو داخل یک عبارت
Patindex
تقریبا میشه گفت معادل دستور instr وی بی هست

ساختار تابع به این صورت هست :

کد:

PATINDEX ( '%pattern%' , expression )

چند تا مثال :

کد:

SELECT PATINDEX('%en_ure%', 'please ensure the door is locked');

خروجی : 8

کد:

SELECT PATINDEX('%[0-9]%', 'please ensure the door 5 is locked');

خروجی : 24
جایگاه اولین کاراکتر عددی

کد:

DECLARE @MyValue varchar(10)
SET @MyValue = 'the';
SELECT PATINDEX('%' + @MyValue + '%', 'please ensure the door is locked')

خروجی: 15

کد:

SELECT PATINDEX('%[-,*,+,/]%', '4*6');

خروجی : 2

جایگاه اولین علامت از مجموعه علایم ریاضی

----

قراره این تاپیک فقط یه نویسنده داشته باشه ؟
دوستان قصد ندارن تجربه هاشونو به اشتراک بذارن ؟

[hamed_Arfaee]

چند تابع کاربردی

کلید کنترلی

کد:

CHECKSUM

برای تولید کلید کنترلی از داده استفاده میشه .
فرض کنید جدولی داریم که اعتبار داده هاش به ورود اولیس و نباید بروز رسانی بشه

نکته : میشه کلید کنترل رو برای یک و یا چند و یا همه ستون ها استفاده کرد

کد:

CHECKSUM ( * | expression [ ,...n ] )

---

کلید کنترلی2

کد:

BINARY_CHECKSUM

برای تولید کلید کنترلی از داده استفاده میشه .
تقریبا شبیه به چک سام معمولی هست با این تفاوت که این تابع دید کاراکتری نداره و باینری عمل میکنه یعنی خروجی چک سام معمولی برای حرف A و a برابر هست ولی باینری چک سام متفاوت .

نکته : میشه کلید کنترل رو برای یک و یا چند و یا همه ستون ها استفاده کرد

کد:

BINARY_CHECKSUM ( * | expression [ ,...n ] )

---

کلید کنترلی ستونی

کد:

CHECKSUM_AGG

برای تولید کلید کنترلی از داده های عددی یک ستون استفاده میشه .

نکته : میشه کلید کنترل رو برای اعداد غیرتکراری و یا همه اعداد تولید کرد
تذکر : نوع داده ها حتما باید عددی باشند

کد:

CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )

---

کلید از کل جدول
با ترکیب توابع بالا میتونیم یه کلید از کل اطلاعات جدول بدست بیاریم :

کد:

SELECT CHECKSUM_AGG(CHECKSUM(*)) FROM MyTable

---

کلید حفاظتی

کد:

HASHBYTES

تولید هش کد برای تائید صحت اطلاعات
بر اساس نوع الگوریتم ورودی رو کد میکنه

نکته : فقط برای یک ستون و یا یک متغییر قابل استفادس نه چند ستون .

کد:

HASHBYTES ( '<algorithm>', { @input | 'input' } )

<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

مثال :

کد:

DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),'IranLED');
SELECT HASHBYTES('MD2', @HashThis);
SELECT HASHBYTES('MD5', @HashThis);
SELECT HASHBYTES('SH1', @HashThis);

[hamed_Arfaee]

کنترل اطلاعات

میخواهیم محدودیت ها و کنترل هایی داخل دیتابیس داشته باشیم برای یکپارچگی و اجتماع پذیری

مثلا
تاریخ تولد کارمند نباید کوچکتر از1310/01/01 و بزرگتر از 1390/01/01 باشد
حداکثر 10 رکورد در جدول ثبت شود
اسم میوه حتما باید با حرف صدا دار شروع شود

برای اعمال این کنترل ها میتوان هنگام ورود داده توسط کاربر آنها را کنترل کرد ولی حالتی را فرض می کنیم که ورودی از چندین برنامه مجزا انجام می گیرد و نمی خواهییم تنوع داده معتبر داشته باشیم و تا حد ممکن قابلیت اجتماع داده را تضمین کنیم .

کد:

Constraints

در کل 5 نوع محدودیت داریم

کد:

Primary Key Constraint = محدودیت کلید اصلی

Foreign Key Constraint = محدودیت کلید خارجی

Check Constraint = محدودیت کنترلی

Unique Constraint = محدودیت یکتایی

Default Constraint = محدودیت مقدار پیش فرض

محدودیت مورد نظر ما "محدودیت کنترلی" می باشد
   
   

همونطوری که در عکسهای بالا مشخصه محدودیت رو ایجاد میکنیم

کنترل تاریخ تولد :

کد:

(CDate >'1320/01/01' AND CDate <'1390/01/01')

اسم مورد نظر با حرف صدا دار شروع شود

کد:

LEFT(CName,1)in ('a','e','o','u','i')

حداکثر 10 رکورد

اول یک تابع ایجاد میکنیم که تعداد رکورد ها رو بهمون بده

کد:

CREATE FUNCTION [dbo].[GetCount]()
RETURNS int
AS
BEGIN

RETURN (select count(*) from tbl)
END

و محدودیت رو اینطوری میگیم :

کد:

dbo.GetCount() < 11

و کلی شرط و شروط دیگه

[Di Di]

خيلي ممنون حامد جان

يه سوال : اگر من اين كار رو انجام دادم و كاربر از داخل برنامه اين شرايط رو رعايت نكرد چه اتفاقي مي افته؟؟

يعني به جز اينكه مانع ثبت اطلاعات مي شه ،‌چطور پيغام خطا رو به كاربر نشون مي ده و چه پيغامي رو؟

ممنون

[hamed_Arfaee]

خيلي ممنون حامد جان

يه سوال : اگر من اين كار رو انجام دادم و كاربر از داخل برنامه اين شرايط رو رعايت نكرد چه اتفاقي مي افته؟؟

يعني به جز اينكه مانع ثبت اطلاعات مي شه ،‌چطور پيغام خطا رو به كاربر نشون مي ده و چه پيغامي رو؟

ممنون

سلام آقا مهدی گل
این محدودیت موجب تولید اکسپشن میشه

یعنی وقتی اینجوری اجراش کنیم :

کد:

Dim db As New Mydb(myConnectionString, True)
                Try
            db.RunSQL("INSERT INTO CIO ([CDate],[CTime],[CEmployee],[CNom],[CType])VALUES('1391/01/01','1258','12554',58,5)")
        Catch ex As SqlException
            MsgBox(ex.Message)
        End Try

که شماره و متنش این هست :

کد:

547    
    
The %ls statement conflicted with the %ls constraint "%.*ls". The conflict occurred in database "%.*ls", table "%.*ls"%ls%.*ls%ls.

که از متن خطامیشه فهمید کدوم محدودیت روی کدوم ستون باعث بوده .