close

Tambayoyin Tambayoyi don Masu Haɓaka SQL: Tambayoyin SQL gama gari Q&A- Sashe na 4

Bayyana manufar ƙirƙira function kuma procedure a cikin SQL da fa'idodin amfani da su.

Amsa: Function kuma procedure a cikin SQL akwai code blocks da za a iya kira daga wasu tambayoyi ko aikace-aikace.

  • Function: Yana dawo da ƙima kuma galibi ana amfani dashi don ƙididdigewa da dawo da sakamako.
  • Procedure: Ba ya dawo da ƙima kuma ana amfani dashi don aiwatar da sarrafa bayanai ko ayyukan ajiya.

Amfanin amfani da ayyuka da hanyoyin sun haɗa da:

  • Rage kwafin lambar, yana sauƙaƙa don kiyayewa da sarrafa lamba.
  • Ƙara sake amfani da shi, ba da damar sake amfani da lambar a wurare da yawa.
  • Inganta aiki, kamar yadda ayyuka da matakai galibi ana haɗa su sau ɗaya kuma ana sake amfani da su sau da yawa.

 

Yadda ake amfani da RECURSIVE tambayoyin kuma COMMON TABLE EXPRESSION(CTE) a cikin SQL.

Amsa: RECURSIVE Tambayoyi kuma COMMON TABLE EXPRESSION(CTE) ana amfani da su don magance maimaita tambayoyin da sake amfani da wani ɓangaren tambaya a cikin SQL.

  • RECURSIVE: Yana ba da damar yin tambayoyi masu maimaitawa a cikin ma'ajin bayanai.
  • CTE: Yana aiki azaman saitin sakamako na ɗan lokaci, karya tambaya zuwa ƙarami, ƙarin sassa masu iya sarrafawa.
WITH RECURSIVE RecursiveCTE(ID, ParentID, Level) AS( 
  SELECT ID, ParentID, 0 AS Level FROM Categories WHERE ParentID IS NULL  
  UNION ALL  
  SELECT C.ID, C.ParentID, Level + 1 FROM Categories C  
  INNER JOIN RecursiveCTE RC ON C.ParentID = RC.ID  
)  
SELECT * FROM RecursiveCTE;

 

Yadda ake sarrafa kwafin bayanai da lokuta marasa inganci a cikin SQ

Amsa: Don sarrafa kwafi da bayanai marasa inganci a cikin SQL, zamu iya amfani da maganganun SQL kamar DISTINCT, GROUP BY, SAMUN, da ƙuntatawa na musamman don tabbatar da keɓancewar bayanai. Bugu da ƙari, za mu iya amfani UPDATE da ko DELETE bayanai don kawar da kwafi ko bayanan da ba su da inganci.

 

Ga fassarar nau'ikan bayanai na musamman a cikin SQL Server

Yadda za a yi amfani da XML, GEOGRAPHY, da GEOMETRY trong SQL Server. hù da phức tap. Don ƙarin koyo game da abin da ke faruwa:

XML:

  • Nau'in bayanan XML a cikin SQL Server yana ba da damar adanawa da aiki tare da bayanai a cikin Tsarin Harshen Alamar Ƙarfafawa.
  • Bayanan XML na iya ƙunsar abubuwa masu arziƙi, suna ba da damar adana ingantaccen tsari da sassauƙan bayanai.
  • SQL Server yana ba da ayyuka da hanyoyi don sarrafa bayanan XML, bada izinin yin tambaya, ƙirƙira, da canza bayanan XML.

GEOGRAPHY Kuma GEOMETRY:

  • Ana amfani da GEOGRAPHY nau'ikan GEOMETRY bayanai da ke cikin SQL Server don adana bayanan yanki da geometric.
  • GEOGRAPHY ana amfani da shi don wakiltar abubuwa na yanki kamar maki, layi, yankuna, da polygons a saman duniya.
  • GEOMETRY ana amfani da shi don wakiltar abubuwa na geometric kamar maki, layi, yankuna, da polygons a cikin fili mai faɗi.

Duk nau'ikan bayanan biyu suna goyan bayan ayyuka na musamman da ayyuka don yin tambaya da nazarin bayanan ƙasa da geometric.

 

Bayyana ayyuka da ayyuka don sarrafa kwanan wata da bayanan lokaci a cikin SQL

Ana amfani da ayyuka da fasalulluka don sarrafa bayanan kwanan wata da lokaci a cikin SQL don sarrafa da aiwatar da ayyuka masu alaƙa da kwanan wata da lokuta a cikin bayanan. Anan akwai cikakken bayanin wasu ayyuka na gama gari da fasali:

DATEPART() Ana amfani da wannan aikin don fitar da takamaiman sashi(misali, rana, wata, shekara, awa, minti, na biyu) daga ƙimar kwanan wata ko lokaci.

SELECT DATEPART(YEAR, '2023-07-19'); -- Result: 2023

DATEDIFF(): Wannan aikin yana ƙididdige bambancin lokaci tsakanin ƙimar kwanan wata biyu ko lokaci.

SELECT DATEDIFF(DAY, '2023-07-01', '2023-07-19'); -- Result: 18(number of days between two dates)

DATEADD(): Wannan aikin yana ƙara takamaiman adadin kwanaki, watanni, shekaru, ko lokaci zuwa ƙimar kwanan wata ko lokaci.

SELECT DATEADD(DAY, 7, '2023-07-19'); -- Result: '2023-07-26'(adding 7 days)

GETDATE(): Wannan aikin yana mayar da kwanan wata da lokacin tsarin.

SELECT GETDATE(); -- Result: '2023-07-19 12:34:56.789'

CONVERT(): Ana amfani da wannan aikin don canza ƙimar kwanan wata ko lokaci daga wannan tsari zuwa wani.

SELECT CONVERT(VARCHAR, '2023-07-19', 103); -- Result: '19/07/2023'

FORMAT(): Ana amfani da wannan aikin don tsara ƙimar kwanan wata ko lokaci bisa ƙayyadaddun tsari.

SELECT FORMAT('2023-07-19', 'dd/MM/yyyy'); -- Result: '19/07/2023'