4-laboratoriya ishi. Har bir talaba o’z mavzusidan kelib chiqib quyida keltirilgan vazifani o’zini ishlayotgan bazasi bo’yicha yoritish kerak

EXCEPT

EXCEPT operatori ikkita namuna o'rtasidagi farqni, ya'ni birinchi namunadagi, lekin ikkinchisida bo'lmagan qatorlarni topish imkonini beradi. Uni ishlatish uchun quyidagi rasmiy sintaksis qo'llaniladi:

Masalan, oldingi mavzudagi jadvallarni olaylik:


Xodimlar jadvali barcha bank xodimlari to'g'risidagi ma'lumotlarni o'z ichiga oladi va mijozlar jadvali barcha mijozlar to'g'risidagi ma'lumotlarni o'z ichiga oladi. Ammo bank xodimlari ham uning mijozi bo'lishi mumkin. Aytaylik, bankning xodimlari bo'lmagan barcha mijozlarni topishimiz kerak:


Xuddi shunday, siz uning mijozlari bo'lmagan barcha bank xodimlarini olishingiz mumkin:

INTERSECT

INTERSECT operatori ikkita namuna uchun umumiy qatorlarni topish imkonini beradi, ya'ni bu operator o'rnatilgan kesishish amalini bajaradi. Uni ishlatish uchun quyidagi rasmiy sintaksis qo'llaniladi:

Masalan, oldingi mavzudagi jadvallarni olaylik:


Mijozlar jadvalida bankning barcha mijozlari, Xodimlar jadvalida esa uning barcha xodimlari saqlanadi. Ammo xodimlar bir vaqtning o'zida bank mijozlari bo'lishlari mumkin, shuning uchun ularning ma'lumotlari bir vaqtning o'zida ikkita jadvalda saqlanishi mumkin. Keling, bankning mijozlari bo'lgan barcha xodimlarini topaylik. Ya'ni, biz ikkita namunaning umumiy elementlarini topishimiz kerak:


O'rnatilgan funktsiyalar
String funktsiyalari
T-SQL da satrlar bilan ishlash uchun quyidagi funksiyalardan foydalanishingiz mumkin:

LEN: satrdagi belgilar sonini qaytaradi. Parametr sifatida funktsiyaga satr uzatiladi, buning uchun biz uzunlikni topishimiz kerak:

LTRIM: satrdan oldingi bo'shliqlarni olib tashlaydi. Parametr sifatida qatorni oladi:

RTRIM: satrdan keyingi bo'shliqlarni olib tashlaydi. Parametr sifatida qatorni oladi:

CHARINDEX: satrdagi pastki qatorning birinchi paydo bo'lishi joylashgan indeksni qaytaradi. Birinchi parametr pastki qator, ikkinchi parametr esa qidirish uchun satr:

PATINDEX: satrda ma'lum bir naqshning birinchi paydo bo'lishi indeksini qaytaradi:

LEFT: satr boshidan ma'lum miqdordagi belgilarni kesib tashlaydi. Funktsiyaning birinchi parametri satr, ikkinchisi esa satrdan birinchi bo'lib kesiladigan belgilar soni:

RIGHT: Satr oxiridan belgilangan belgilar sonini kesadi. Funktsiyaning birinchi parametri satr, ikkinchisi esa satrdan birinchi bo'lib kesiladigan belgilar soni:

SUBSTRING: belgilangan indeksdan boshlab satrdan belgilangan uzunlikdagi pastki qatorni kesadi. Funktsiyaning birinchi parametri satr, ikkinchisi - kesish uchun boshlang'ich indeks va uchinchi parametr - kesish kerak bo'lgan belgilar soni:

REPLACE: satr ichida bir pastki qatorni boshqasiga almashtiradi. Funktsiyaning birinchi parametri satr, ikkinchisi almashtiriladigan pastki qator va uchinchisi bilan almashtirilishi kerak bo'lgan pastki qator:

REVERSE: satrni teskari aylantiradi:

CONCAT: ikkita satrni bittaga birlashtiradi. Parametr sifatida u 2 yoki undan ortiq qatordan ulanishni oladi:

LOWER: satrni kichik harfga aylantiradi:

UPPER: satrni bosh harfga aylantiradi

SPACE: ma'lum miqdordagi bo'sh joyni o'z ichiga olgan qatorni qaytaradi

Masalan, jadvalni olaylik:

Va ma'lumotlarni olishda biz string funktsiyalaridan foydalanamiz:

Raqamlar bilan ishlash funksiyalari

ROUND: Raqamni yaxlitlaydi. Birinchi parametr sifatida raqam uzatiladi. Ikkinchi parametr uzunlikni belgilaydi. Agar uzunlik ijobiy son bo'lsa, u qaysi kasrga yaxlitlash kerakligini ko'rsatadi. Agar uzunlik manfiy sonni bildirsa, u sonning oxiridan o'nli kasrgacha bo'lgan qaysi raqamga yaxlitlash kerakligini ko'rsatadi.

ISNUMERIC: Qiymat raqam ekanligini aniqlaydi. Funktsiya parametr sifatida ifodani oladi. Agar ifoda son bo'lsa, u holda funktsiya 1 ni qaytaradi. Agar bo'lmasa, 0 qaytariladi.

ABS: Raqamning mutlaq qiymatini qaytaradi.

CEILING: joriy qiymatdan katta yoki unga teng eng kichik butun sonni qaytaradi.

FLOOR: Joriy qiymatdan kichik yoki unga teng eng katta butun sonni qaytaradi.

Kvadrat: raqamni kvadratga aylantiradi.

SQRT: Raqamning kvadrat ildizini oladi.

RAND: 0 dan 1 gacha bo'lgan tasodifiy suzuvchi nuqta sonini yaratadi.

COS: radyanlarda ifodalangan burchakning kosinusini qaytaradi

SIN: radianlarda ifodalangan burchak sinusini qaytaradi

TAN: radianlarda ifodalangan burchak tangensini qaytaradi

Masalan, jadvalni olaylik:

Tovar narxining mahsulotini shu tovar miqdoriga yaxlitlang:

Sana va vaqt funksiyalari

T-SQL sana va vaqt bilan ishlash uchun bir qator funksiyalarni taqdim etadi:

GETDATE: sana va vaqt obyekti sifatida tizim soatiga asoslangan joriy mahalliy sana va vaqtni qaytaradi

GETUTCDATE: joriy mahalliy GMT (UTC/GMT) sana va vaqtni sana va vaqt obyekti sifatida qaytaradi

SYSDATETIME: tizim soatiga asoslangan joriy mahalliy sana va vaqtni qaytaradi, lekin GETDATE dan farqi shundaki, sana va vaqt datetime2 obyekti sifatida qaytariladi.

SYSUTCDATETIME: joriy mahalliy GMT (UTC/GMT) sana va vaqtni datetime2 obyekti sifatida qaytaradi

SYSDATETIMEOFFSET: GMT ga nisbatan sana va vaqtni o'z ichiga olgan datetimeoffset(7) obyektini qaytaradi

DAY: parametr sifatida qabul qilingan sananing kunini qaytaradi

MONTH: sananing oyini qaytaradi

YEAR: sanadan yilni qaytaradi

DATENAME: Sana qismini qator sifatida qaytaradi. Sana qismini tanlash opsiyasi birinchi parametr sifatida, sananing o'zi esa ikkinchi parametr sifatida o'tkaziladi:

Quyidagi parametrlar sananing bir qismini aniqlash uchun ishlatilishi mumkin (ularning qisqartirilgan versiyalari qavs ichida ko'rsatilgan):

• 
  year (yy, yyyy): yil
  

 quarter (qq, q): квартал
 month (mm, m):oy

• 
  dayofyear (dy, y): yil kuni
  
• 
  day (dd, d): oy kuni
  
• 
  week (wk, ww): hafta
  
• 
  weekday (dw): hafta kuni
  
• 
  hour (hh):soat
  
• 
   minute (mi, n): минута
  
• 
   second (ss, s): секунда
  
• 
   millisecond (ms): миллисекунда
  
• 
   microsecond (mcs): микросекунда
  
• 
   nanosecond (ns): наносекунда
  

tzoffset (tz): GMT ga nisbatan daqiqalarda ofset (datatimeoffset obyekti uchun)
DATEPART: sananing bir qismini raqam sifatida qaytaradi. Sana qismini tanlash parametri birinchi parametr sifatida (DATENAME uchun bir xil parametrlardan foydalangan holda) va sananing o'zi ikkinchi parametr sifatida uzatiladi:

• 
  DATEADD: Belgilangan sana komponentiga raqam qo‘shish natijasi bo‘lgan sanani qaytaradi. Birinchi parametr DATENAME funksiyasi uchun yuqorida tavsiflangan sana komponentini ifodalaydi. Ikkinchi parametr - qo'shiladigan miqdor. Uchinchi parametr - bu siz qo'shmoqchi bo'lgan sananing o'zi:
  

Agar qo'shilgan miqdor salbiy raqam bo'lsa, unda sana aslida qisqartiriladi.

DATEDIFF: Ikki sana orasidagi farqni qaytaradi. Birinchi parametr sana komponenti bo'lib, u farqni o'lchash kerak bo'lgan birliklarni belgilaydi. Ikkinchi va uchinchi parametrlar taqqoslanadigan sanalardir:

TODATETIMEOFFSET: boshqa datetimeoffset ob'ektiga vaqt ofsetini qo'shish natijasi bo'lgan datetimeoffset qiymatini qaytaradi

SWITCHOFFSET: datetime2 ob'ektiga vaqt ofsetini qo'shish natijasi bo'lgan datetimeoffset qiymatini qaytaradi

EOMONTH: Parametr sifatida qabul qilingan sanada ishlatiladigan oyning oxirgi kuni sanasini qaytaradi.

Ixtiyoriy ikkinchi parametr sifatida sanaga qo'shish uchun oylar sonini o'tkazishingiz mumkin. Keyin oyning oxirgi kuni yangi sana uchun hisoblanadi.

DATEFROMPARTS: yil, oy va kun bo'yicha sanani yaratadi

ISDATE: Ifoda sana ekanligini tekshiradi. Agar shunday bo'lsa, u 1 ni qaytaradi, aks holda 0 ni qaytaradi.

Funktsiyalardan foydalanishga misol sifatida buyurtma sanasini o'z ichiga olgan buyurtma jadvalini yaratish mumkin:

DEFAULT GETDATE() iborasi maʼlumotlarni qoʻshishda sana oʻtmagan boʻlsa, u GETDATE() funksiyasi yordamida avtomatik ravishda hisoblanishini bildiradi.

Yana bir misol, 16 kun oldin qilingan buyurtmalarni topish:

Ma'lumotlarni konvertatsiya qilish

Biz bir turdagi qiymatni boshqa turdagi ma'lumotlarni saqlaydigan ustunga belgilaganimizda yoki har xil turdagi ma'lumotlarni o'z ichiga olgan operatsiyalarni bajarganimizda, SQL Server konversiyani amalga oshirishga harakat qiladi va ishlatilgan qiymatni kerakli turga o'tkazadi. Lekin barcha transformatsiyalar SQL Server tomonidan avtomatik ravishda amalga oshirilmaydi. SQL Server pastki ustunlik turidan yuqoriroq turga yashirin konversiyalarni amalga oshirishi mumkin. Ustuvorlik jadvali (yuqoriroq, ustuvorlik):

datetime

smalldatetime

float

real

decimal

money

smallmoney

int

smallint

tinyint

bit

nvarchar

nchar

varchar

char

Ya'ni, SQL Server avtomatik ravishda 100.0 (float) raqamini sana va vaqtga (datetime) o'zgartira oladi.

Yuqori ustunlikka ega bo'lgan turlardan pastroq turlarga o'zgartirishni amalga oshirish zarur bo'lgan hollarda, aniq quyma amalga oshirilishi kerak. Buning uchun T-SQL ikkita funktsiyani belgilaydi: CONVERT va CAST.

CAST funktsiyasi ifodani bir turdan ikkinchisiga o'zgartiradi. U quyidagi shaklga ega:

Misol tariqasida quyidagi jadvallarni olaylik:


Masalan, buyurtmalar haqidagi ma'lumotni ko'rsatishda raqamli qiymat va sanani satrga o'zgartiradi:

Konvertatsiya qilish

Transformatsiyalarning aksariyati CAST funktsiyasi bilan qoplangan. Agar sizga qo'shimcha formatlash kerak bo'lsa, CONVERT funksiyasidan foydalanishingiz mumkin. U quyidagi shaklga ega:

Uchinchi ixtiyoriy parametr ma'lumotlarni formatlash uslubini belgilaydi. Ushbu parametr turli xil ma'lumotlar turlari uchun har xil talqinga ega bo'lgan raqamli qiymatni ifodalaydi. Masalan, sana va vaqtni formatlash uchun ba'zi qiymatlar:
0 yoki 100 - sana formati "Ds dd yyyy hh:miAM/PM" (standart qiymat)

1 yoki 101 - sana formati "mm/dd/yyyy"

3 yoki 103 - sana formati "dd/aa/yyyy"

7 yoki 107 - sana formati "Ds dd, yyyy hh:miAM/PM"

8 yoki 108 - sana formati "hh: mi: ss"

10 yoki 110 - sana formati "mm-dd-yyyy"

14 yoki 114 - sana formati "hh:mi:ss:mmmm" (24 soatlik vaqt formati)

Pul ma'lumotlarini satrga formatlash uchun ba'zi qiymatlar:

0 - raqamning kasr qismida faqat ikkita raqam qoladi (standart)

1 - sonning kasr qismida faqat ikkita raqam qoladi va raqamlarni ajratish uchun vergul qo'llaniladi

2 - sonning kasr qismida faqat to'rtta raqam qoladi

Masalan, buyurtmalar sanasi va narxini formatlash bilan ko'rsatamiz:

TRY_AYLANTIRISH

CAST va CONVERT funksiyalaridan foydalanilganda, agar ma'lumotlarni ma'lum bir turga uzatib bo'lmasa, SQL Server istisno qiladi. Misol uchun:

Istisnodan qochish uchun TRY_CONVERT funksiyasidan foydalanishingiz mumkin. Uning ishlatilishi CONVERT funksiyasiga o'xshaydi, faqat ifodani kerakli turga aylantirib bo'lmasa, funktsiya NULLni qaytaradi:

Qo'shimcha funktsiyalar

CAST, CONVERT, TRY_CONVERT-dan tashqari, bir qancha turlarga aylantirish uchun ishlatilishi mumkin bo'lgan bir qator funksiyalar mavjud:

STR(float [, uzunlik [, kasr]]): raqamni satrga aylantiradi. Ikkinchi parametr satrning uzunligini, uchinchisi esa raqamning kasr qismida qancha belgi qoldirish kerakligini ko'rsatadi.

CHAR(int): ASCII raqamli kodini belgiga aylantiradi. Ko'pincha klaviaturadan kiritib bo'lmaydigan belgi kerak bo'lgan holatlar uchun ishlatiladi.

ASCII(char): belgini ASCII raqamli kodga aylantiradi

NCHAR(int): UNICODE raqamli kodini belgiga aylantiradi

UNICODE(char): belgini UNICODE raqamli kodiga aylantiradi

CASE va IIF funksiyalari

CASE
CASE funksiyasi qandaydir ifodaning qiymatini tekshiradi va tekshirish natijasiga qarab u yoki bu natijani qaytarishi mumkin.

CASE quyidagi shaklni oladi:

Misol tariqasida quyidagi mahsulotlar jadvalini olaylik:

Keling, ushbu jadvalni so'raymiz va CASE funktsiyasidan foydalanamiz:

Bu erda ProductCount ustun qiymatlari WHEN iboralaridan keyingi qiymatlar bilan ketma-ket taqqoslanadi. ProductCount ustunining qiymatiga qarab, CASE funksiyasi tegishli THEN bandidan keyin keladigan qatorlardan birini qaytaradi. Qaytarilgan natijada EvaluateCount ustuni aniqlangan:

Bundan tashqari, CASE funktsiyasi yana bir shaklga ega bo'lishi mumkin:

Masalan, Mahsulotlar jadvali uchun:

Aslida, hamma narsa oldingi misoldagi kabi, faqat CASE dan keyin taqqoslangan qiymat ko'rsatilmaydi. Taqqoslash ifodalarining o‘zi esa WHEN operatoridan keyin keladi. Va agar WHEN ifodasidan keyingi ifoda rost bo'lsa, u holda tegishli THEN ifodasidan keyin keladigan qiymat qaytariladi.

IIF
IIF funktsiyasi shartli ifoda natijasiga qarab ikkita qiymatdan birini qaytaradi. Funktsiyaning umumiy shakli quyidagicha ko'rinadi:

Agar IIF funksiyasidagi shart rost bo'lsa, qiymat_1 qaytariladi, agar noto'g'ri bo'lsa, qiymat_2 qaytariladi. Misol uchun:

NEWID, ISNULL va COALESCE funktsiyalari

NEWID
UNIQUEIDENTIFIER ob'ektini, ya'ni qandaydir noyob qiymatni yaratish uchun NEWID() funksiyasidan foydalaniladi. Masalan, biz asosiy kalit ustuni uchun UNIQUEIDENTIFIER turini belgilashimiz va uni NEWID funksiyasi qiymatiga sukut bo'yicha belgilashimiz mumkin:

ISNULL

ISNULL funksiyasi ba'zi ifodaning qiymatini tekshiradi. Agar u NULL bo'lsa, funktsiya ikkinchi parametr sifatida qabul qilingan qiymatni qaytaradi:

Masalan, yuqorida tuzilgan jadvalni olaylik va ma'lumotlarni qabul qilishda ISNULL funksiyasini qo'llaymiz: