Ideiglenes táblák: memóriában tárolódik, speciális szegmens tárolja
- tranzakció szintű
- session szintű
- lokális
- globális: ##nev
(A tábla neve előtt # szerepel).
Fizikai tábla: fizikai, permanens tábla:
- heap: a rekordok folytonos egységben tárolódnak
- klaszter: egységekre bontott a tábla, van egy hasító függvény, amely a kulcsa alapján
megadja a tároló csoport helyét. Következmény: az azonos kulcsúak egy helyre kerülnek.
A klaszter csoportok több különböző tábla rekordjait is tudja tárolni.
- klaszter: egységekre bontott a tábla, van egy hasító függvény, amely a kulcsa alapján
megadja a tároló csoport helyét. Következmény: az azonos kulcsúak egy helyre kerülnek.
Táblák létrehozása:
- permanens tábla: CREATE TABLE nev (…) ON filegroup
- lokális ideiglenes tábla: CREATE TABLE #tablanev (…)
- globális ideiglenes tábla: CREATE TABLE ##tablanev(…)
- memóriabeli tábla (táblaváltozó): DECLARE @tablavaltozo TABLE(…)
- permanens tábla: CREATE TABLE nev (…) ON filegroup
- lokális ideiglenes tábla: CREATE TABLE #tablanev (…)
- globális ideiglenes tábla: CREATE TABLE ##tablanev(…)
- memóriabeli tábla (táblaváltozó): DECLARE @tablavaltozo TABLE(…)
MERGE Céltábla frissítése (beszúrás, módosítás, törlés) egy forrás tábla alapján.
MERGE <target> USING
(SELECT <expression> FROM <source>) AS <alias> ON <intersection>
WHEN MATCHED THEN <UPDATE | DELETE >
WHEN TARGET NOT MATCHED [AND <conditions>] THEN <INSERT>
WHEN SOURCE NOT MATCHEND [AND <conditions>] THEN <UPDATE | DELETE>
MERGE <target> USING
(SELECT <expression> FROM <source>) AS <alias> ON <intersection>
WHEN MATCHED THEN <UPDATE | DELETE >
WHEN TARGET NOT MATCHED [AND <conditions>] THEN <INSERT>
WHEN SOURCE NOT MATCHEND [AND <conditions>] THEN <UPDATE | DELETE>
AL-SELECT használata
Korrelálatlan:
Korrelált:
A SELECT parancs speciális elemei: CASE, WITH, IDENTITY
CASE
With-es SQL szerkezet alkalmazása(Hierarchikus kapcsolat lekérdezés ):
----definició
WITH dinamikus_tabla AS
(
SELECT CAST('2011-01-01' AS DATETIME) DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 <= '2030-12-31'
)
----- használat
SELECT YEAR(datevalue) ev,
MONTH(datevalue) honap,
--DAY(datevalue) nap,
CONVERT(date, min(datevalue)) honap_eleje,
CONVERT(date, max(datevalue)) honap_vege,
CONVERT(numeric(9,0), COUNT(datevalue)) honap_napok_db
--INTO #YearMonthTempTable
FROM dinamikus_tabla
group by YEAR(datevalue), MONTH(datevalue) --,DAY(datevalue)
order by YEAR(datevalue), MONTH(datevalue) --,DAY(datevalue)
OPTION (MAXRECURSION 0)
;
Az --horgony tagnál adjuk meg a rekurzió kezdeti pontját meghatározó
rekordot. A --rek_tag definiálja a rekurzív algoritmust. Az INNER JOIN
segítségével a WITH által definiált kifejezés ismételten meghívódik.
WITH TempOrg([ID], [Name], ParentID) AS
(
--horgony
SELECT O.ID, O.Name, O.ParentID
FROM Organization O
WHERE ParentID IS NULL
---------
UNION ALL
----------
--rek_tag
SELECT O.ID, O.Name, O.ParentID
FROM Organization O
INNER JOIN TempOrg TMP ON
TMP.ID = O.ParentID
)
SELECT * FROM TempOrg
Oracle LISTAGG függvény ms-sql megvalósítása:
SELECT FieldA , STUFF(( SELECT ','+ FieldB FROM TableName a
WHERE b.FieldA = a.FieldA FOR XML PATH('')),1 ,1, '') Members
FROM TableName b
GROUP BY FieldA;
SQL DATEADD – SQL DATEDIFF funkciók használata
- SELECT CURRENT_TIMESTAMP — 2012-01-05 07:02:10.577
- SELECT DATEADD(month,2,‘2012-12-09’) — 2013-02-09 00:00:00.000
- SELECT DATEDIFF(day,‘2012-12-09’,‘2013-02-09’) — 62
- SELECT DATENAME(month, ‘2012-12-09’) — December
- SELECT DATENAME(weekday, ‘2012-12-09’) — Sunday
- SELECT DATEPART(month, ‘2012-12-09’) — 12
- SELECT DAY(‘2012-12-09’) — 9
- SELECT GETDATE() — 2012-01-05 07:02:10.577
- SELECT GETUTCDATE() — 2012-01-05 12:02:10.577
- SELECT MONTH(‘2012-12-09’) — 12
- SELECT YEAR(‘2012-12-09’) — 2012
Egyedi dátum formátum összerakása (YYYY_MM_DD)
select CurrentDate=rtrim(year(getdate())) + ‘_’ +
right(‘0’ + rtrim(month(getdate())),2) + ‘_’ +
right(‘0’ + rtrim(day(getdate())),2)
Gyűjtő más néven aggregáló függvények:
- AVG: a sorok egy kijelölt csoportjának átlagát számolja ki.
- COUNT: megszámlálja azokat a sorokat, amelyek adatokat tartalmaznak (NOT NULL)
- MIN: a sorok egy csoportjában lévő legkisebb értéket adja meg.
- MAX: a sorok egy csoportjában lévő legnagyobb értéket adja meg.
- SUM: a sorok egy csoportjában lévő adatok összegét adja vissza.
Például:
SELECT nev1, COUNT(1) as DB
FROM tablam
GROUP BY nev1
HAVING COUNT(1) > 1
ORDER BY nev1
Tárolt eljárás, függvény előnyei:
- kisebb hálózati forgalom
- gyorsabb SQL végrehajtás
- centralizált kezelés
- hatékonyabb karbantartás
- védhető adatbázis objektum
- klienst tehermentesíti
- egymást hívhatják
- ütemezhetőek
- lehet benne függvény is
- gyorsabb SQL végrehajtás
- centralizált kezelés
- hatékonyabb karbantartás
- védhető adatbázis objektum
- klienst tehermentesíti
- egymást hívhatják
- ütemezhetőek
- lehet benne függvény is
Eljárás :
Nincs visszatérési értéke, de lehet kimeneti paramétere
CREATE PROCEDURE nev(@e1 [=ert1] tip1 …)
WITH ENCRYPTION | EXECUTE AS user
AS
BEGIN TRY
…
END;
Futtatása: EXEC nev p1, p2;
CREATE PROCEDURE nev(@e1 [=ert1] tip1 …)
WITH ENCRYPTION | EXECUTE AS user
AS
BEGIN TRY
…
END;
Futtatása: EXEC nev p1, p2;
Függvény :
A visszatérési értéke lehet érték és táblázat is
CREATE FUNCTION fnev(@p1 tip1…) RETURNS tipus
WITH ENCRYPTION | EXECUTE AS user
AS BEGIN
…
RETURN ertek;
END;
Futtatása: SELECT * FROM nev(p1, p2)
CREATE FUNCTION (param) RETURNS @ret TABLE(m1 tip1, …) AS
BEGIN
…
INSERT INTO @ret VALUES(…);
RETURN;
END;
A visszatérési értéke lehet érték és táblázat is
CREATE FUNCTION fnev(@p1 tip1…) RETURNS tipus
WITH ENCRYPTION | EXECUTE AS user
AS BEGIN
…
RETURN ertek;
END;
Futtatása: SELECT * FROM nev(p1, p2)
CREATE FUNCTION fnev(@p1 tip1…) RETURNS tipus
WITH ENCRYPTION | EXECUTE AS user
AS BEGIN
…
RETURN ertek;
END;
Futtatása: SELECT * FROM nev(p1, p2)
CREATE FUNCTION (param) RETURNS @ret TABLE(m1 tip1, …) AS
BEGIN
…
INSERT INTO @ret VALUES(…);
RETURN;
END;
Kurzor kezelés
Deklaráció: DECLARE knev CURSOR FOR SELECT…
Nyitás: OPEn knev
Lekérdezés: FETCH poz knev INTO @v1…
Ciklus: WHILE @@FETXH_STATUS = 0
Lezárás: CLOSE knev
Felszabadítás: DEALLOCATE knev
Információ lekérdezés: @@CURSOR_ROWS
BEGIN
DECLARE @v char(29)
DECLARE c1 CURSOR FOR SELECT nev FROM tablam;
OPEN c1;
FETCH NEXT FROM c1 INTO @v;
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @v;
FETCH NEXT FROM c1 INTO @v;
END;
DEALLOCATE c1;
END;
Triggerek
- Automatikusan meghívódik egy tábla vagy adatbázis objektum eseményre.
- A végrehajtás sebessége gyorsabb, előre le van fordítva.
- Garantálja a kapcsolódó tevékenységet, nagyobb biztonság, megbízhatóság.
- Figyelni kell a triggerek közötti függőségre, melyik trigger mit hív.
- Tábla insertnél meglehet hívni saját magát a triggert.
CREATE TRIGGER triggernev ON tablam
[WITH ENCRYPTION | EXECUTE AS user]
FOR UPDATE | INSTEAD OF | INSERT | UPDATE | DELETE
AS
BEGIN
…
END;
XML fájl előállítása aadatbázis adatokból:
- SELECT … FROM … FOR XML RAW (’tagnev’): nincs gyökér, egyszintű, minden
rekord egy tag; minden mezo egy attribútum
- SELECT … FROM … FOR XML RAW, ELEMENTS: minden mező egy gyerekelem
- SELECT … FROM … FOR XML AUTO: minden forrástáblához külön gyerekelem;
minden mező attribútum
- SELECT … FROM … FOR XML AUTO, ELEMENTS: minden mező gyerekelem
- SELECT … FOR XML PATH(’tagnev’), ROOT (’nev’): részletes kontroll, egyedi ki-
alakítás; minden mezőre külön XPath beállítás; gyökérelem is kijelölhető
- SELECT … FOR XML EXPLICIT(’tagnev’), ROOT(’nev’): legnagyobb kontroll,
egyedi kialakítás; mezőkre külön kiterjesztett XPath beállítás
Kivétel, hibakezelés
BEGIN TRY
…
END TRY
BEGIN CATCH
ERROR_NUMBER()
ERROR_MESSAGE()
ERROR_LINE()
ERROR_SEVERITY()
END CATCH;
RAISEERROR(szoveg, kod1, kod2)
Adatbázis-szintű objektumkatalógus (object catalog):
- sys.check_constraints: az adatbázisban érvényes érték-jellegű kényszerek.
- sys.columns: azadatbázisban található oszlopok (a rendszertáblák oszlopai nincsenek listázva).
- sys.events: azon események, amelyek értesítést vagy triggert indítanak el.
- sys.foreign_keys: az adatbázisban levő oszlopok, amelyeken külső kulcs kényszer van definiálva.
- sys.foreign_key_columns: az adatbázisban levő azon oszlopok, amelyek külső kulcsok.
- sys.identity_columns: az adatbázisban lévő egyediség kényszerrel ellátott oszlopok.
- sys.indexes: az adatbázisban levő indexstruktúrák.
- sys.index_columns: az adatbázisban levő indexelt oszlopok listája.
- sys.key_constraints: az adatbázisban érvényes elsődleges-kulcs kényszerek.
- sys.objects: az adatbázisban levő összes objektum (táblák, indexek, kényszerek stb.)
- sys.partitions: az adatbázishoz tartozó tábla- illetve indexpartíciók.
- sys.procedures: az adatbázishoz tartozó tárolt eljárások.
- sys.schemas: az adatbázisban létrehozott sémák.
- sys.synonyms: az adatbázisban tárolt szinonimák.
- sys.tables: az adatbázisban tárolt táblák (a rendszertáblák nincsenek listázva)
- sys.triggers: a tábla- illetve adatbázis-szintű triggerek.
- sys.types: a szerveren érvényes típusok
- sys.views: az adatbázisban tárolt nézetek
Adatbázisok és adatfájlok katalógus (database and files catalog):
- sys.databases: a szerveren található adatbázisok.
- sys.database_files: az adatbázishoz tartozó fájlok
- sys.filegroups: az adatbázishoz tartozó fájlcsoportok
- sys.master_files: a szerveren található összes adatbázishoz tartozó fájlok
Biztonság és engedélyezés:
- sys.database_permissions: az összes adatbázis-szintű engedély.
- sys.database_principals: az adatbázis-szintű hozzáférők (user, role).
- sys.server_permissions: az összes szerver-szintű engedély.
- sys.server_principals: a szerver-szintű hozzáférők (login, role).
- sys.sql_logins: a szerveren érvényes loginok
Dinamikus menedzsment
- sys.dm_db_index_usage_stats: szerver-szintű indexhasználat.
- sys.dm_exec_cached_plans: a szerver által cache-elt végrehajtási tervek.
- sys.dm_exec_query_stats: a cache-elt végrehajtási tervek teljesítmény-statisztikái.
- sys.dm_exec_connections: a szerver által létesített kapcsolatok.
- sys.dm_exec_requests: a szerveren végrehajtás alatt levő kérések.
- sys.dm_exec_sessions: a szerveren futó sessionök.
- sys.dm_tran_active_transactions: a szerveren futó tranzakciók.
- sys.dm_tran_current_transaction: információk a nézetet lekérdező tranzakcióról (1 soros).
- sys.dm_tran_database_transactions: az aktuális adatbázisban futó tranzakciók.
- sys.dm_tran_locks: a szerver zárkezelőjéhez beérkezett zárkérések és a már lefoglalt zárak
- sys.messages: a szerver lehetséges hibaüzenetei.
- sys.server_events: azon szerver-szintű események, amelyek értesítést vagy triggert aktiválnak.
- sys.server_event_sessions: a külön sessionben futó eseménykezelő eljárások.
- sys.servers: a helyi szerver illetve azon távoli szerverek tulajdonságai, amivel a helyi szerver összeköttetésben van
Alapfogalmak
MySql dátumsor szekvenciába rendezés:
Egy esemény rögzítő tábla (egy mezőben az esemény bekövetkezésének dátuma) lekérdezése dátum_tól-ig szerkezet szerint.
Számoló mező létrehozása, eltolással adat kapcsolás a közös select részben és kész.
Vannak bizonyos adatbázis típusoknál előkészített pszeodu oszlop nevek és függvények is a gyorsabb megoldásért sqlserver:ROWNUM, oracle:row_number()
pl.
1.
ROW_NUMBER() OVER (ORDER BY OrderingColumn) AS RowNum
2.
SELECT a.*
from (select b.*,
rownum as b_rownum
FROM (SELECT c.*
from u_v_e c
ORDER BY v_neve) b
where rownum <= 5) a —- ig
WHERE b_rownum >= 3 —- tól
from (select b.*,
rownum as b_rownum
FROM (SELECT c.*
from u_v_e c
ORDER BY v_neve) b
where rownum <= 5) a —- ig
WHERE b_rownum >= 3 —- tól
1. Megoldás verzió:
select 0 into @rank;
select a1.bug_id , a1.field_name, a2.date_modified as kezdet_dat, a1.date_modified as veg_datum, a2.rank as rank2, a1.rank as rank1
from
(
select
@rank:=@rank+1 as rank,
aa.*
from mantis_bug_history_table aa
where bug_id =3
and field_name =’status’
order by bug_id , date_modified
) a1 left join
(
select
@rank:=@rank+1 as rank,
aa.*
from mantis_bug_history_table aa
where bug_id =3
and field_name =’status’
order by bug_id , date_modified
) as a2
on
a1.bug_id = a2.bug_id
and a1.field_name = a2.field_name
and a1.rank = (a2.rank – 3)
;
from
(
select
@rank:=@rank+1 as rank,
aa.*
from mantis_bug_history_table aa
where bug_id =3
and field_name =’status’
order by bug_id , date_modified
) a1 left join
(
select
@rank:=@rank+1 as rank,
aa.*
from mantis_bug_history_table aa
where bug_id =3
and field_name =’status’
order by bug_id , date_modified
) as a2
on
a1.bug_id = a2.bug_id
and a1.field_name = a2.field_name
and a1.rank = (a2.rank – 3)
;
Eredmény2:
bug_id field_name kezdet_dat veg_datum rank2 rank1
3 status 2008-07-28 17:41:50.0 NULL 1
3 status 2008-07-28 17:41:50.0 2008-07-28 17:43:02.0 5 2
3 status 2008-07-28 17:43:02.0 2008-07-31 09:01:24.0 6 3
3 status 2008-07-31 09:01:24.0 2008-09-01 13:43:58.0 7 4
3 status 2008-07-28 17:41:50.0 2008-07-28 17:43:02.0 5 2
3 status 2008-07-28 17:43:02.0 2008-07-31 09:01:24.0 6 3
3 status 2008-07-31 09:01:24.0 2008-09-01 13:43:58.0 7 4
2. Megoldás verzió:
select 0 into @rank;
select a1.bug_id , a1.field_name, a1.date_modified as kezdet_dat, a2.date_modified as veg_datum, a2.rank as rank2, a1.rank as rank1
from
(
select
@rank:=@rank+1 as rank,
aa.*
from mantis_bug_history_table aa
where bug_id =3
and field_name =’status’
order by bug_id , date_modified
) a1 left join
(
select
@rank:=@rank+1 as rank,
aa.*
from mantis_bug_history_table aa
where bug_id =3
and field_name =’status’
order by bug_id , date_modified
) as a2
on
a1.bug_id = a2.bug_id
and a1.field_name = a2.field_name
and a1.rank+5 = a2.rank
–and a2.rank is null
;
select a1.bug_id , a1.field_name, a1.date_modified as kezdet_dat, a2.date_modified as veg_datum, a2.rank as rank2, a1.rank as rank1
from
(
select
@rank:=@rank+1 as rank,
aa.*
from mantis_bug_history_table aa
where bug_id =3
and field_name =’status’
order by bug_id , date_modified
) a1 left join
(
select
@rank:=@rank+1 as rank,
aa.*
from mantis_bug_history_table aa
where bug_id =3
and field_name =’status’
order by bug_id , date_modified
) as a2
on
a1.bug_id = a2.bug_id
and a1.field_name = a2.field_name
and a1.rank+5 = a2.rank
–and a2.rank is null
;
Eredmény2:
bug_id field_name kezdet_dat veg_datum rank2 rank1
3 status 2008-07-28 17:41:50.0 2008-07-28 17:43:02.0 46 41
3 status 2008-07-28 17:43:02.0 2008-07-31 09:01:24.0 47 42
3 status 2008-07-31 09:01:24.0 2008-09-01 13:43:58.0 48 43
3 status 2008-09-01 13:43:58.0 NULL 44
3 status 2008-07-28 17:41:50.0 2008-07-28 17:43:02.0 46 41
3 status 2008-07-28 17:43:02.0 2008-07-31 09:01:24.0 47 42
3 status 2008-07-31 09:01:24.0 2008-09-01 13:43:58.0 48 43
3 status 2008-09-01 13:43:58.0 NULL 44
Adat feltötés az adatbázis táblába:
BCP paranccsal
bcp kl_db.dbo.tablam in C:\tmp\forras.csv -c -T -t;
bcp kl_db.dbo.tablam in C:\tmp\forras.csv -c -t, -S localhost\SqlSrv2008 -T
bcp kl_db.dbo.tablam in C:\tmp\forras.txt -c -t , -r \n -T
-c Adatmezők karaktereskénti betöltés.
-t , Mező határóló megadása pl.: (,)
-r \n Sor határoló megadása.
-T Biztonságos kapcsolat WINDOWS autentikáció használatával (belépés/futtatás)
BULK INSERT paranccsal
USE kl_db;
GO
BULK INSERT dbo.tablam FROM 'C:\tmp\forras.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);
GO
Megjegyzések
Megjegyzés küldése