ايران ويج

صفحه‌ها: 1 2 3

سلام

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

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

کد: 
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 نوشته است: [ -> ]اون مثال دو رو بیشتر باز میکنی آقا حامد جان؟

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

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

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

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

کد: 
SELECT lastName FROM tblperson

ORDER BY 

CASE  WHEN lastName=N'Arfaee' THEN  NULL 

else 

    lastName 

END

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

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

کد: 
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

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

می خواهیم خروجی 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

جستجوی یک الگو داخل یک عبارت
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

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

----

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

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

کلید کنترلی

کد: 
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);

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

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

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

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

کد: 
Constraints

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

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

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

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

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

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

محدودیت مورد نظر ما "محدودیت کنترلی" می باشد
[attachment=8360]
[attachment=8361]

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

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

کد: 
(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 نوشته است: [ -> ]خيلي ممنون حامد جان

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

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

ممنون

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

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

کد: 
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.

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

صفحه‌ها: 1 2 3

hamed_Arfaee

babyy

hamed_Arfaee

hamed_Arfaee

hamed_Arfaee

hamed_Arfaee

hamed_Arfaee

hamed_Arfaee

hamed_Arfaee

Di Di

hamed_Arfaee