use Northwind
/*
T-SQL (Transactional – Structure Query Language) setzt sich aus drei Sprachbereichen zusammen.
1. DDL : Data Definition Language
— CREATE <Objekttyp><Objektname> (Erstellt)
— ALTER <Objekttyp><Objektname> (Ändert)
— DROP <Objekttyp><Objektname> (Löscht)
2. DML : Data Manipulation Language
— INSERT (Fügt einen Datensatz hinzu)
— UPDATE (Aktualisiert einen vorhandenen Datensatz)
— DELETE (Löscht einen vorhandenen Datensatz)
— SELECT (Liefert die Datensätze, die dem angegebenen Kriterium entsprechen.)
*/
— INSERT
— INSERT into <Tabellenname>(<Spaltenname1>,<Spaltenname2>,<SpaltennameN>)
values(<Wert1>,<wert2>,<wertN>)
INSERT into Products (ProductName,UnitPrice,UnitsInStock,Discontinued)
values (‘Getrocknete Bohnen’,5,1000,0)
— UPDATE <tabellenname> SET <Spaltenname1>=<Wert1>,
— <Spaltenname2>=<Wert2>,
— <Spaltenname3>=<Wert3>
–WHERE <Kriterium>
UPDATE Products SET ProductName=’Grüne Bohnen’
WHERE ProductID=78
— DELETE FROM <Tabellenname> WHERE <Kriterium>
DELETE FROM Products WHERE ProductID=78
INSERT into Products (ProductName,UnitPrice,UnitsInStock,Discontinued)
values (‘Getrocknete Bohnen’,5,1000,0)
— SELECT <Spaltenname1>,<Spaltenname2> FROM <Tabellenname> WHERE <ggf. Kriterium>
— SELECT * FROM <Tabellenname> WHERE <ggf. Kriterium> (Liefert alle Spalten..)
SELECT * FROM Employees
SELECT FirstName, LastName FROM Employees
— SELECT <Tabellenname>.<Spaltenname1>, FROM <Tabellenname>.<Spaltenname2> WHERE <ggf. Kriterium>
— Die lange Form..
SELECT Products.ProductName, Products.UnitPrice FROM Products
WHERE UnitPrice>100
— Produkte über 100 $..
— Name, Preis und Bestand der Produkte, deren UnitsInStock-Wert (Bestand) unter 10 liegt
SELECT Products.ProductName, Products.UnitPrice, Products.UnitsInStock FROM Products
WHERE UnitsInStock<10
/*
1. Legen Sie fest, mit welcher Tabelle bzw. welchen Tabellen Sie arbeiten.
2. Legen Sie fest, welche Spalte(n) dieser Tabelle(n) Sie sehen möchten
3. Schreiben Sie gegebenenfalls Ihr Kriterium.
*/
— Firmenname, Ansprechpartner, Adresse, Stadt und Land der Kunden in Brasilien
SELECT CompanyName, ContactName, Address, City, Country FROM Customers
where Country=’Brazil’
— Kunden außerhalb Brasiliens
SELECT CompanyName,ContactName,Address,City,Country FROM Customers
where Country != ‘Brazil’
— Meine Kunden in London oder Paris:
SELECT CustomerID,CompanyName,Address, City FROM Customers
WHERE City= ‘London’ OR City= ‘Paris’
— Kunden, die SOWOHL in Mexico D.F. wohnen ALS AUCH den ContactTitle ‘owner’ haben:
SELECT CustomerID,CompanyName,ContactTitle,City FROM Customers
WHERE City=’México D.F.’ AND ContactTitle=’owner’
— Namen und Preise meiner Produkte, die mit C beginnen
SELECT ProductName,UnitPrice FROM Products
WHERE ProductName LIKE ‘C%’
— Liste der nicht mehr verkauften Produkte
–(Tipp: Discontinued ist 1)
SELECT ProductName,UnitPrice FROM Products
WHERE Discontinued=1
— Wenn wir Vorname, Nachname und Geburtsdatum der Mitarbeiter (Employees) sehen möchten, deren Vorname (FirstName) mit ‘A’ beginnt:
SELECT FirstName,LastName,BirthDate FROM Employees
WHERE FirstName LIKE ‘A%’
— Firmennamen meiner Kunden, deren Name ‘RESTAURANT’ enthält:
SELECT CompanyName FROM Customers
WHERE CompanyName LIKE ‘%RESTAURANT%’
— Namen und Preise aller Produkte zwischen 50 $ und 100 $
SELECT ProductName,UnitPrice FROM Products
WHERE UnitPrice BETWEEN 50 AND 100
— OrderID und OrderDate der Bestellungen (Orders) zwischen dem 1. Juli 1996 und dem 31. Dezember 1996
SELECT OrderID,OrderDate FROM Orders
WHERE OrderDate BETWEEN ’01/07/1996′ AND ’31/12/1996′
— Kunden, deren Land (Country) ENTWEDER Spain, France oder Germany ist
SELECT CompanyName,Country FROM Customers
WHERE Country=’Spain’ OR Country=’France’ OR Country=’Germany’
— Wenn wir OR sehr oft verwendet haben, können wir es auch so schreiben.
SELECT CompanyName,Country FROM Customers
WHERE Country IN (‘Spain’,’France’,’Germany’)
–Die Verneinung (NOT IN)
SELECT CompanyName,Country FROM Customers
WHERE Country NOT IN (‘Spain’,’France’,’Germany’)
–Kunden, deren Faxnummer ich nicht kenne
SELECT CustomerID, CompanyName, Fax FROM Customers
where Fax is null
— ResultSet sortieren:
Ich sortiere meine Kunden nach Land:
SELECT CompanyName,Country FROM Customers
ORDER BY Country ASC
— asc: aufsteigend (vom kleinsten zum größten)
— desc: absteigend (vom größten zum kleinsten)
–Meine Produkte vom teuersten zum günstigsten sortieren; als Ergebnis möchten wir Produktname und Preis:
SELECT ProductName,UnitPrice FROM Products
ORDER BY UnitPrice DESC
–Meine Produkte vom teuersten zum günstigsten sortieren, den Bestand aber aufsteigend anzeigen; als Ergebnis möchten wir Produktname und Preis:
SELECT ProductName,UnitPrice,UnitsInStock FROM Products
–Wir könnten auch WHERE ProductName LIKE ‘C%’ schreiben..
ORDER BY UnitPrice DESC, UnitsInStock ASC
— AGGREGATE FUNCTIONS (Funktionen, die einen Wert zurückgeben, z. B. wie viele Spalten eine Tabelle hat..)
SELECT COUNT(*) FROM Customers
— Ich habe also 91 Kunden..
SELECT CategoryID,CategoryName FROM Categories
SELECT ProductName,CategoryID FROM Products
— Wie viele Produkte gibt es in Kategorie 1..?
SELECT COUNT(*) FROM Products
WHERE CategoryID=1
–COUNT(<Spaltenname>) : Gibt zurück, wie oft die angegebene Spalte im ResultSet vorkommt.
— In wie viele verschiedene Länder exportiere ich..?
SELECT COUNT(DISTINCT Country) FROM Customers
— DISTINCT – Keine Duplikate zurückgeben.. Kommt Deutschland dreimal vor, nur einmal anzeigen..
— Welche Länder sind das..?
SELECT DISTINCT Country FROM Customers
— DISTINCT: Übernimmt von den sich wiederholenden Werten im ResultSet (Hinweis: Das ResultSet liegt im RAM) NUR EINEN und filtert die Daten so gewissermaßen.
–Daten filtern: TOP x
SELECT TOP 3 CompanyName FROM Customers
— Wenn ich die 5 teuersten Produkte möchte
SELECT TOP 5 ProductName, UnitPrice FROM Products
ORDER BY UnitPrice Desc
–Anzahl der Bestellungen meines Kunden mit der CustomerID ALFKI..?
–(Orders)
SELECT COUNT(*) FROM Orders
WHERE CustomerID=’ALFKI’
— Um der Spalte einen Namen zu geben:
SELECT COUNT(*) AS ToplamSiparis FROM Orders
WHERE CustomerID=’ALFKI’
SUM(<Spaltenname>): Gibt die Summe der angegebenen Spalte im ResultSet zurück. Nur für numerische Spalten verwendbar.
–Gesamtkosten meiner Produkte:
SELECT SUM(UnitPrice) ToplamFiyat FROM Products
–Wie viel Umsatz hat mein Unternehmen bisher gemacht..?
–Quelle: Tabelle Order Details
–1-Discount (wir ziehen die Rabatte ab..
SELECT SUM(UnitPrice*Quantity*(1-Discount)) ToplamCiro FROM [Order Details]
–AVG(<Spaltenname>): Bildet den Durchschnitt der angegebenen Spalte im ResultSet:
–Mein durchschnittlicher Produktpreis:
SELECT AVG(UnitPrice) Ortalama FROM Products
SELECT FirstName + ‘ ‘ + LastName as AdSoyad FROM Employees
— MAX und MIN
SELECT MAX(UnitPrice) FROM Products
SELECT MIN(UnitPrice) FROM Products
— Name des teuersten Produkts
SELECT ProductName FROM Products
WHERE UnitPrice= (SELECT MAX(UnitPrice) FROM Products)
–Die Bestellung mit dem geringsten Ertrag (Quelle: Order Details)
SELECT MIN(UnitPrice * Quantity) FROM [Order Details]
Potenzieren:
SELECT POWER (3,2)
SELECT SQRT(81)
SELECT LEN(‘TEST’)
— Der Kunde mit dem längsten Namen unter meinen Kunden (Anzahl der Buchstaben)
SELECT MAX(LEN(CompanyName)) FROM Customers
SELECT GETDATE()
SELECT YEAR (GETDATE())
SELECT DAY (GETDATE())
SELECT MONTH (GETDATE())
–Vorname, Nachname und Alter meiner Mitarbeiter
SELECT FirstName,LastName, YEAR(GETDATE())-YEAR(BirthDate) as Yas FROM Employees
SELECT photo FROM Employees
— GROUP BY (Gruppierung)
— Wie viele Einheiten wurden von jedem Produkt insgesamt bestellt..?
SELECT ProductID, SUM(Quantity ) FROM [Order Details]
GROUP BY ProductID
— Wenn Sie in einer Abfrage eine Aggregatfunktion und eine normale Spalte verwenden, muss die Abfrage ein GROUP BY enthalten. Gruppiert wird nach allen Spalten außerhalb der Aggregatfunktion.
SELECT ProductID, SUM(Quantity ) as ToplamAdet FROM [Order Details]
GROUP BY ProductID
ORDER BY ToplamAdet DESC
— Wie viel habe ich insgesamt an jeder Bestellung verdient..?
— Quelltabelle: Order Details
— Benötigte Spalten: OrderID und Ertrag (Menge*Preis)
— Es gibt ein SUM, also muss ich GROUP BY verwenden..
SELECT OrderID, SUM(Quantity * UnitPrice) FROM [Order Details]
GROUP BY OrderID
— Wie viele Produkte gibt es in jeder Kategorie..?
SELECT CategoryID, COUNT(ProductID) FROM Products
GROUP BY CategoryID
–Da MAX und MIN einen einzelnen Wert zurückgeben, wird kein GROUP BY verwendet..!
SELECT CategoryID, COUNT(ProductID) as Adet FROM Products
GROUP BY CategoryID
ORDER BY Adet
— HAVING:
— Produkte mit mehr als 1000 verkauften Einheiten:
SELECT ProductID,SUM(Quantity) As SatisAdeti FROM [Order Details]
GROUP BY ProductID
HAVING SUM(Quantity)>1000
— Hängt das Kriterium für ein ResultSet von einer Tabellenspalte ab, verwenden Sie WHERE. Wenden Sie ein Kriterium jedoch auf einen im RAM berechneten Wert an, wird HAVING verwendet..
— SUB-QUERY (Unterabfragen, verschachtelte Abfragen)
— Name und Preis meiner Produkte mit einem Preis unter dem Durchschnitt:
— 1. Zuerst eine Abfrage, um den Durchschnitt zu ermitteln:
SELECT AVG(UnitPrice) FROM Products
— 2. Die Abfrage, die dieses Ergebnis als Kriterium verwendet:
SELECT ProductName,UnitPrice FROM Products
WHERE UnitPrice < (SELECT AVG(UnitPrice) FROM Products)
SELECT COUNT(*) FROM Customers
SELECT COUNT(DISTINCT CustomerID) FROM Orders
— Welche meiner Kunden haben noch nie bestellt..? (89 der 91 Kunden hatten bestellt..)
SELECT CompanyName,Address,City,Country FROM Customers
WHERE CustomerID NOT IN (SELECT DISTINCT CustomerID FROM Orders)
— Das folgende Beispiel soll dem Irrtum vorbeugen, eine Sub-Query könne nur nach dem WHERE-Kriterium verwendet werden:
SELECT c.CategoryName + ‘ (‘ + CAST((SELECT COUNT(ProductID) FROM Products
WHERE CategoryID=c.CategoryID) as NVARCHAR(5) ) +’)’ FROM Categories as c
— INNER JOIN
— Der Abfragetyp, der aus verknüpften Tabellen nur aus den Datensätzen mit Produkt (den PASSENDEN, bei denen der PRIMARY KEY ein FOREIGN-KEY-Gegenstück hat) ein ResultSet bildet:
SELECT Products.ProductName, Categories.CategoryName FROM Products
INNER JOIN Categories
ON Products.CategoryID=Categories.CategoryID
— Welches Produkt ist in welcher Kategorie..
INSERT into Categories(CategoryName,Description)
values (‘Desserts’, ‘Milchdesserts’)
SELECT * FROM Categories
— Welcher Lieferant (Suppliers.CompanyName) liefert welches Produkt (Products.ProductName)..?
SELECT sp.CompanyName,p.ProductName FROM Suppliers as sp
INNER JOIN Products as p
ON sp.SupplierID=p.SupplierID
— p.ProductName=Products.ProductName (abgekürzt..)
— Welche Bestellung (Orders.OrderID) wurde mit welchem Versanddienstleister (Shippers.CompanyName) wann (Orders.OrderDate) verschickt..?
SELECT Orders.OrderID,Shippers.CompanyName,Orders.OrderDate FROM Shippers
INNER JOIN Orders
ON Shippers.ShipperID=Orders.ShipVia
— 25.11.2009, Mittwoch
— welcher Kunde hat welche Bestellung aufgegeben..?
SELECT Customers.CompanyName,Orders.OrderID,Orders.OrderDate FROM Customers
INNER JOIN Orders
— PRIMARY KEY — FOREIGN KEY
ON Customers.CustomerID = Orders.CustomerID
— Wie viele Bestellungen hat jeder Mitarbeiter INSGESAMT angenommen..?
— Employees, Orders
SELECT FirstName,LastName, COUNT(OrderID) FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
GROUP BY FirstName,LastName
— Wir haben GROUP BY verwendet, weil wir COUNT verwendet haben..
— Wer hat die meisten Bestellungen angenommen..
SELECT FirstName,LastName, COUNT(OrderID) AS ToplamSiparisSayisi FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
GROUP BY FirstName,LastName
ORDER BY ToplamSiparisSayisi DESC
–BEZIEHUNGEN ÜBER MEHRERE TABELLEN
— Welche Bestellung,
— welcher Mitarbeiter,
— welcher Kunde hat sie aufgegeben..?
SELECT Orders.OrderID,Employees.FirstName,Employees.LastName,Customers.CompanyName FROM Employees
INNER JOIN Orders
ON Employees.EmployeeID=Orders.EmployeeID
INNER JOIN Customers
ON Customers.CustomerID=Orders.CustomerID
— Welches Produkt
— ist in welcher Kategorie..?
— Wer liefert dieses Produkt..?
SELECT Products.ProductName,Categories.CategoryName,Suppliers.CompanyName FROM Products
INNER JOIN Suppliers
ON Products.SupplierID=Suppliers.SupplierID
INNER JOIN Categories
ON Products.CategoryID=Categories.CategoryID
— Welche Bestellung,
— welcher Kunde sie aufgegeben hat,
— welcher Mitarbeiter sie angenommen hat,
— an welchem Datum,
— welcher Versanddienstleister sie verschickt hat
— wie viele Einheiten welches Produkts bestellt wurden,
— zu welchem Preis sie bestellt wurden
— in welcher Kategorie das Produkt ist
— welcher Lieferant dieses Produkt geliefert hat
SELECT
Orders.OrderID,
Customers.companyName,
Employees.FirstName+ ‘ ‘ + Employees.LastName as Calisan,
Orders.OrderDate,
Shippers.CompanyName as Kargo,
Products.ProductName,
[Order Details].Quantity,
[Order Details].UnitPrice * (1-[Order Details].Discount) as indirimliFiyat,
Categories.CategoryName,
Suppliers.CompanyName as Tedarikci
FROM Employees
INNER JOIN Orders
ON Orders.EmployeeID=Employees.EmployeeID
INNER JOIN Customers
ON Customers.CustomerID=Orders.CustomerID
INNER JOIN Shippers
ON Shippers.ShipperID=Orders.ShipVia
INNER JOIN [Order Details]
ON Orders.OrderID=[Order Details].OrderID
INNER JOIN Products
ON Products.ProductID=[Order Details].ProductID
INNER JOIN Categories
ON Categories.CategoryID=Products.CategoryID
INNER JOIN Suppliers
ON Suppliers.SupplierID=Products.SupplierID
— OUTER JOIN
SELECT * FROM Categories
INSERT into Categories(CategoryName,Description) values
(‘Desserts’, ‘Bla Bla’)
— Kategorien ohne Produkte
SELECT Categories.CategoryName,Products.ProductName FROM Products
RIGHT JOIN Categories
ON Products.CategoryID=Categories.CategoryID
WHERE ProductName is null
— Ich habe 91 Kunden. Nur 89 davon haben bestellt. Finden Sie die 2, die nicht bestellt haben:
SELECT CompanyName,Address,OrderID FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
WHERE OrderID is null
— Ob LEFT oder RIGHT, hängt davon ab, auf welcher Seite des Schlüsselworts JOIN die Tabelle steht, die Sie vollständig abrufen möchten.
— Um alle Datensätze der Tabelle links von JOIN abzurufen, verwende ich LEFT,
— für die Tabelle rechts davon RIGHT.
— Vorher hatten wir es so ermittelt..
— Welche meiner Kunden haben noch nie bestellt..? (89 der 91 Kunden hatten bestellt..)
— SELECT CompanyName,Address,City,Country FROM Customers
— WHERE CustomerID NOT IN (SELECT DISTINCT CustomerID FROM Orders)
— WIE VIELE BESTELLUNGEN HAT JEDER MITARBEITER BISHER INSGESAMT ANGENOMMEN..?
— es gibt zwei verschiedene (unabhängige) Abfragen..
SET STATISTICS TIME ON
SELECT EmployeeID,FirstName,LastName,
(SELECT COUNT(orderID) FROM Orders WHERE EmployeeID= x.EmployeeID)
FROM Employees as x
— UNION (vereint 2 verschiedene ResultSets.)
— verbindet zwei oder mehr Tabellen..
— Regel: Beide ResultSets müssen gleich viele Spalten haben, und die Spalten müssen in derselben Reihenfolge denselben Typ haben. (int und string lassen sich nicht untereinander stellen..)
SELECT CompanyName,Address,City,Country, ‘ Kunde’ FROM Customers
UNION
SELECT CompanyName,Address,City,Country, ‘ Lieferant’ FROM Suppliers
ORDER BY Country
— 01.12.2009, DIENSTAG
SET STATISTICS TIME ON
SELECT Products.ProductName, Products.UnitPrice, Categories.CategoryName,Categories.Description FROM Products
CROSS JOIN Categories
— CROSS JOIN zeigt alle verknüpften und nicht verknüpften Tabellen (LEFT JOIN, RIGHT JOIN, INNER JOIN, OUTER JOIN). Er hilft nur dabei zu messen, wie lange eine Abfrage dauert.
CHECK- und DEFAULT-Constraints
use Normalizasyon
ALTER TABLE Urunler
ADD CONSTRAINT DF_AktifMi
DEFAULT 1 for AktifMi
— Für Products wäre es DF_Discontinued – for Discontinued gewesen…
— CHECK
ALTER TABLE Urunler
ADD CONSTRAINT CK_Fiyat
CHECK (Fiyati > -1)
— Jetzt können wir in die Produkttabelle keinen negativen Wert mehr eingeben..
— INDEX —
use Northwind
GO
SELECT ProductName,UnitPrice FROM Products
WHERE ProductID = 1
SELECT * FROM Customers
WHERE Country=’Germany’
— Meine Kunden in Deutschland
— Welche Spalte sollte ich indexieren.. Wenn wir hier Country indexieren, wird “Germany” nur unter den Ländern gesucht..
CREATE Nonclustered INDEX ix_Ulke
ON Customers(Country)
— Abfragen werden durch Lücken langsamer. Bei der Wartung alle 3–6 Monate kann eine Indexbereinigung nötig sein…
— DBCC INDEXDEFRAG
— DROP INDEX ausführen und einen neuen INDEX erstellen
SELECT * FROM Northwind.dbo.Products
— FQN: FULLY QUALIFIED NAME: DER VOLLSTÄNDIGE BEZEICHNER (ein Objektname, der alle Objekte vom Server bis zum Spaltennamen mit Punkten verbindet):
–Beispiel: <Servername>.<Datenbankname>.<Schemaname>.<Tabellenname>.<Spaltenname>
— Mit FQN kann ich von master aus Abfragen an andere Server richten, die mit meinem Server verbunden (oder darauf definiert) sind..
— Statt des Servernamens können wir auch die IP-Adresse angeben…
CREATE VIEW UrunlereGoreTedarikciler
AS
SELECT ProductName,companyName,Address,City,Country FROM Products
INNER JOIN Suppliers
ON
Products.SupplierID=Suppliers.SupplierID
— Prüfen Sie vor dem Erstellen einer View unbedingt, ob die Abfrage funktioniert..
SELECT * FROM UrunlereGoreTedarikciler
— Da es letztlich eine Tabelle ist, können wir auch WHERE-Kriterien verwenden..
WHERE CompanyName LIKE ‘E%’
order by Country
SELECT * FROM tumSiparisDetaylari
WHERE OrderID=10248
ORDER BY ProductName
— Wann kein INSERT in eine View möglich ist:
— 1. Wenn die View einen INNER JOIN enthält (dahinter stehen viele Tabellen und es ist unklar, in welche eingefügt werden soll)
— 2. Wenn die Spalten im ResultSet der View nicht leer bleiben dürfen (NOT NULL)
— 3. Wenn die View mit CHECK OPTION erstellt wurde
CREATE VIEW AlmanMusteriler
AS
SELECT CustomerID,CompanyName,Address,City,Country FROM Customers
WHERE Country=’Germany’
— Sie enthält keinen INNER JOIN und auch die Pflichtfelder (not null), passt also zur View. (Die ersten beiden Bedingungen sind erfüllt)
WITH CHECK OPTION
— Den eingehenden Datensatz beim Insert prüfen und annehmen, wenn er passt..
SELECT * FROM AlmanMusteriler
INSERT into AlmanMusteriler(CustomerID,CompanyName,Address, City,Country)
values
(‘ACTHU’,’ACHTUNG Co.’,’Hede’,’Berlin’,’Germany’)
— Hätten wir hier Türkei geschrieben, wäre das Insert nicht möglich gewesen, weil wir als WHERE-Kriterium Germany festgelegt haben. Ohne WHERE-Kriterium wäre wiederum keine CHECK OPTION nötig gewesen..
— WITH CHECK OPTION: Wenn neue Daten bei einer INSERT- oder UPDATE-Abfrage auf eine View deren Regeln einhalten sollen, können wir CHECK OPTION verwenden.
–Wenn wir sie verschlüsseln möchten, verwenden wir WITH ENCRYPTION.. Danach können nicht einmal wir sie einsehen..
CREATE VIEW UrunBilgisi
with Encryption
AS
SELECT ProductName,UnitPrice FROM Products
— INJECTION
SELECT * FROM Users WHERE KullaniciAdi=” AND Sifre=”
— Normalerweise sieht die Passwortprüfung wie oben aus.. das Folgende hebelt sie also aus..
SELECT * FROM Employees WHERE FirstName=” OR ‘A’=’A’ AND LastName=” OR ‘A’=’A’
‘ or ‘A’=’A
— SCHEMABINDING
— SCRIPT und BATCH
— Variablen
DECLARE @Sayi int
— Wenn ich den Wert der Variablen selbst festlege:
SET @Sayi=5
Print(@Sayi)
DECLARE @CalisaninAdi nvarchar(20)
— Wenn der Wert der Variablen aus einer Abfrage kommt:
SELECT @CalisaninAdi=FirstName FROM Employees
WHERE EmployeeID=1
Print(@CalisaninAdi)
DECLARE @x int
SET @x=8
Print(‘Zahl = ‘+
CAST(@x as nchar(2))
)
— x nehmen und in ein nchar mit 2 Zeichen umwandeln..
DECLARE @x int
SET @x=8
Print(‘Zahl = ‘+
CONVERT(nchar(2),@x)
)
— x nehmen und in ein nchar mit 2 Zeichen umwandeln.. (genau wie oben)
— Wie erhalte ich die zuletzt erzeugte ORDERID, sobald ich eine Bestellung erfasse..?
SELECT TOP 1 OrderID FROM Orders
ORDER BY OrderID DESC
— Wenn ich das in eine Variable schreiben möchte:
DECLARE @ID int
SELECT TOP 1 @ID=OrderID FROM Orders
ORDER BY OrderID DESC
Print(@ID)
INSERT into Orders(CustomerID,EmployeeID,OrderDate,ShipVia)
values (‘ALFKI’,’1′,GETDATE(),1)
SELECT @@IDENTITY
— Globale Variable @@
— Kein Benutzer KANN eine globale SQL-Variable DEFINIEREN
INSERT into Orders(CustomerID,EmployeeID,OrderDate,ShipVia)
values (‘ALFKI’,’1′,GETDATE(),1)
SELECT @@IDENTITY
select SCOPE_IDENTITY()
— WHEN / CASE:
SELECT CompanyName,Region =
CASE
WHEN Region=’BC’ THEN ‘Washington’
WHEN Region=’SP’ THEN ‘Pennsylvania’
WHEN Region=’OR’ THEN ‘Orlando’
END
FROM Customers
WHERE Region is not null
SELECT ProductName,Discontinued=
CASE
WHEN Discontinued=’0′ THEN ‘Verkauf eingestellt’
WHEN Discontinued=’1′ THEN ‘Wird weiter verkauft’
END
FROM Products
— TRY – CATCH —
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
SELECT ERROR_Message()
END CATCH
— STORED PROCEDURE (GESPEICHERTE PROZEDUR) —
CREATE PROC KategoriGetir
AS
SELECT * FROM Categories
KategoriGetir
CREATE PROC IDyeGoreUrun
@id int
AS
SELECT ProductName,UnitPrice,UnitsInStock FROM Products
WHERE ProductID=@id
IDyeGoreUrun 77
— liefert die Daten des Produkts mit der ID 77..
— Normalerweise gehen wir beim Hinzufügen eines Produkts so vor..
INSERT INTO Categories (CategoryName,Description)
values (@KategoriAdi,@Aciklama)
— Machen wir eine Stored Procedure daraus..
CREATE PROC KategoriEkle
@KategoriAdi nvarchar(15),
@Aciklama ntext
AS
INSERT INTO Categories (CategoryName,Description)
values (@KategoriAdi,@Aciklama)
KategoriEkle ‘Backwaren’, ‘Gebäck nach türkischer Art’
— HAUSAUFGABE MIT PREIS: Schokolade für alle, die sie lösen…
— Eine Prozedur, die ermittelt, wie viel (preislich) der Kunde mit der angegebenen ID insgesamt eingekauft hat
SELECT SUM([Order Details].UnitPrice*Quantity*(1-Discount))
FROM [Order Details]
INNER JOIN Orders
ON Orders.OrderID=[Order Details].OrderID
INNER JOIN Customers
ON Customers.CustomerID=Orders.CustomerID
WHERE Customers.CustomerID=’ALFKI’
— Prüfen wir zuerst, ob dieser Teil funktioniert, und
— erstellen wir jetzt die Prozedur
CREATE PROC MusterininAlisVerisi
@ID char(5)
AS
SELECT SUM([Order Details].UnitPrice*Quantity*(1-Discount))
FROM [Order Details]
INNER JOIN Orders
ON Orders.OrderID=[Order Details].OrderID
INNER JOIN Customers
ON Customers.CustomerID=Orders.CustomerID
WHERE Customers.CustomerID=’ALFKI’
MusterininAlisVerisi ‘OCEAN’
SELECT CustomerID from Customers
— Variationen sind möglich…
CREATE PROC MusterininAlisVerisi2
@ID char(5)
AS
SELECT SUM([Order Details].UnitPrice*Quantity*(1-Discount)) AS Toplam
FROM [Order Details]
INNER JOIN Orders
ON Orders.OrderID=[Order Details].OrderID
INNER JOIN Customers
ON Customers.CustomerID=Orders.CustomerID
WHERE Customers.CustomerID=@ID
GROUP BY Customers.CustomerID,CompanyName
ORDER BY Toplam DESC
MusterininAlisVerisi2 ‘QUICK’
SELECT CustomerID from Customers
— Bestellungen zwischen zwei Daten:
SELECT OrderID,OrderDate,ShipAddress FROM Orders
WHERE OrderDate BETWEEN ’31/12/1996′ AND ’15/07/1997′
— Jetzt erstellen wir eine Prozedur..
CREATE PROC TariheGoreSiparis
@Tarih1 smalldatetime,
@Tarih2 smalldatetime
AS
SELECT OrderID,OrderDate,ShipAddress FROM Orders
WHERE OrderDate BETWEEN @Tarih1 AND @Tarih2
TariheGoreSiparis ’01/01/1997′, ’06/06/1997′
— Umsatz nach Jahr
CREATE PROC YilaGoreCiro
@Yil int
AS
SELECT SUM(Quantity * UnitPrice * (1-Discount)) FROM [Order Details]
INNER JOIN Orders
ON Orders.OrderID=[Order Details].OrderID
WHERE YEAR (Orders.OrderDate)=@Yil
YilaGoreCiro 1997
— Welches Produkt hat der Kunde mit der angegebenen ID zuletzt gekauft..?
— An wen hat der Mitarbeiter mit der angegebenen ID am meisten verkauft..?
— Seit wann arbeiten wir mit dem Kunden mit der angegebenen ID zusammen..?
— Die 5 meistverkauften Produkte der Kategorie mit der angegebenen ID..?
— Welche Produkte liefert mir der Lieferant mit der angegebenen ID..?
CREATE PROC HangiUrun
@ID char(5)
AS
SELECT TOP 1 Customers.CompanyName,Products.ProductName,[Order Details].Quantity,Orders.OrderDate FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
INNER JOIN [Order Details]
ON Orders.OrderID=[Order Details].OrderID
JOIN Products
ON [Order Details].ProductID=Products.ProductID
WHERE Customers.CustomerID=@ID
ORDER BY Orders.OrderDate DESC
HangiUrun ‘QUICK’
— Wir haben uns die verknüpften Einträge in der Tabelle angesehen..
— Käufer eines USB-Sticks..
SELECT FROM
— 1. Nur die Bestellungen, die dieses Produkt enthalten:
CREATE PROC BunuAlanBunudaAldi
@productID int
AS
SELECT TOP 5 ProductName, SUM(Quantity) as toplam FROM Products
INNER JOIN [Order Details]
ON Products.ProductID=[Order Details].ProductID
WHERE
[Order Details].OrderID
IN
(
SELECT OrderID FROM [Order Details]
WHERE ProductID=@productID
)
AND [Order Details].ProductID !=@productID
GROUP BY ProductName
ORDER BY toplam DESC
BunuAlanBunudaAldi 9
—– FUNKTIONEN —–
— DIE MWST. ALLER PREISE IN DER PRODUKTTABELLE BERECHNEN
— PROCEDURE
— Heißt es DIE MWST. EINES BELIEBIGEN WERTES BERECHNEN, wird daraus eine FUNKTION.. unabhängig von einer Tabelle..
CREATE FUNCTION KDVHesaplayici
(
@Para money
)
RETURNS money
AS
BEGIN
DECLARE @Deger money
SET @Deger= @Para * 1.18
RETURN @Deger
END
SELECT ProductName,UnitPrice, dbo.KDVHesaplayici(UnitPrice) AS KDVDahil
— Eine Funktion arbeitet immer schemagebunden
FROM Products
— Quadrieren wir eine als Parameter übergebene int-Zahl..
CREATE FUNCTION KaresiniAl
(
@Sayi int
)
RETURNS int
AS
BEGIN
DECLARE @Sonuc int
SET @Sonuc= @Sayi * @Sayi
RETURN @Sonuc
END
SELECT dbo.KaresiniAl(9)
— ODER
CREATE FUNCTION KaresiniAlalim
(
@x int
)
RETURNS int
AS
BEGIN
RETURN @x*@x
END
SELECT dbo.KaresiniAlalim(9)
— A.AKYILDIRIM
SELECT UPPER(‘türkay’)
SELECT LOWER (‘ABDULLAH’)
SELECT LEN(‘Dilara’)
SELECT SUBSTRING(‘BilgeAdam’,1,5)
CREATE FUNCTION ParafOlusturucu
(
@Ad nvarchar(50),
@Soyad nvarchar(50)
)
RETURNS nvarchar(100)
AS
BEGIN
DECLARE @donenDeger nvarchar(100)
SELECT @donenDeger = UPPER(SUBSTRING(@Ad,1,1))+’.’+
UPPER(SUBSTRING(@Soyad,1,1)) +
LOWER(SUBSTRING(@Soyad,2,LEN(@Soyad)))
RETURN @donenDeger
END
SELECT dbo.ParafOlusturucu(‘Ali’,’Akyıldırım’)
SELECT TitleOfCourtesy + ‘ ‘ + dbo.ParafOlusturucu(FirstName,LastName)
FROM Employees
— ES GIBT ZWEI ARTEN VON FUNKTIONEN: IN-LINE-FUNKTIONEN und MULTI-LINE-FUNKTIONEN.
WIE DER NAME SCHON SAGT, ENTHÄLT EINE IN-LINE-FUNKTION NUR EINE EINZIGE ZEILE. IN DIESEM FALL MÜSSEN SIE KEINE BEGIN/END-STRUKTUR VERWENDEN. EINE MULTI-LINE-FUNKTION (MEHRZEILIG) MUSS JEDOCH MIT BEGIN BEGINNEN UND MIT END ENDEN.
CREATE FUNCTION ResultSetOlustur
()
RETURNS TABLE
AS
RETURN SELECT CompanyName, Country FROM Customers
SELECT * FROM dbo.ResultSetOlustur()
— TRANSACTION
BEGIN TRY
END TRY
BEGIN CATCH
END CATCH
— DAS IST DAS MUSTER..
BEGIN TRY
Begin Tran
— Eine Insert-, Update- oder Delete-Abfrage kann innerhalb einer Transaktion verwendet werden
commit tran
END TRY
BEGIN CATCH
END CATCH
— Sehen wir uns Schritt für Schritt an, wie sie geschrieben wird..
BEGIN TRY
Begin Tran T1
— Eine Insert-, Update- oder Delete-Abfrage kann innerhalb einer Transaktion verwendet werden
Begin Tran T2
— Noch eine Abfrage (zuerst läuft T1, dann T2)
commit tran
commit tran
END TRY
BEGIN CATCH
— Tritt ein Fehler auf, die gesamte Transaktion zurücksetzen:
ROLLBACK TRAN T1
END CATCH
BEGIN TRY
BEGIN TRAN KategoriEkle
insert into Categories(CategoryName,Description)
values (‘Schuhe’,’Winter/Sommer Herren/Damen’)
Begin Tran UrunSil
DELETE FROM Products WHERE ProductID=1
commit tran
commit Tran
END TRY
BEGIN CATCH
ROLLBACK TRAN KategoriEkle
END CATCH
— Aus Gründen der Datensicherheit: Wenn die auszuführenden Abfragen aufeinander aufbauen müssen:
— (Beispiel: zuerst die Bestellung anlegen, dann die Menge in den Bestelldetails erfassen, dann den Bestand in der Produkttabelle verringern)
— Verwenden Sie unbedingt einen Transaktionsblock!
–* FUSSNOTE: Stored Procedures eignen sich am besten für Transaktionsblöcke.
— TRIGGER (wird nach einer INSERT-, UPDATE- oder DELETE-Abfrage ausgelöst)—
— SCHREIBEN WIR DEN TRIGGER, DER BEI EINER BESTELLUNG DEN PRODUKTBESTAND VERRINGERT..
CREATE TRIGGER tr_OtomatikStok
ON [Order Details]
for INSERT
— MIT DER INSERT-ABFRAGE LAUFEN, ZUSAMMEN MIT DER INSERT-ABFRAGE AUSLÖSEN
AS
— Ziel: Wird ein Datensatz in die Tabelle Order Details eingetragen, soll der Bestand des betreffenden Produkts in der Tabelle Products um die Menge verringert werden.
DECLARE @UrunID int
— Wir haben eine Variable definiert
DECLARE @Adet int
— Da um die Menge verringert wird, brauchen wir eine Variable namens Adet (Menge)..
— Zu jeder Tabelle gehören zwei temporäre Tabellen. Außerhalb eines Triggers sind sie nicht zugänglich. Sie heißen inserted und deleted.
— INSERTED: Die durch eine INSERT-Abfrage entstehende Zeile wird zunächst in der temporären Tabelle INSERTED gehalten.
— DELETED: Eine zu löschende Zeile wird vor dem Löschen in der temporären Tabelle DELETED gehalten.
SELECT @UrunID=ProductID, @Adet=Quantity
FROM INSERTED
UPDATE Products SET UnitsInStock=UnitsInStock-@Adet
WHERE ProductID=@UrunID
— Nach dem Erstellen des Triggers wird der Bestand bei den folgenden Abfragen automatisch aktualisiert.
INSERT into [Order Details](OrderID, ProductID, Quantity)
values (10323, 1, 2)
SELECT ProductName,UnitsInStock FROM Products
WHERE ProductID=1
— Anforderung: Soll ein Produkt aus der Tabelle Products gelöscht werden, wird seine Spalte Discontinued auf 1 gesetzt (also nicht löschen, sondern aktualisieren):
CREATE TRIGGER tr_SatisiDurdur
ON Products
INSTEAD OF delete
— der Trigger soll laufen, ohne das Delete auszuführen
AS
— Alle INSTEAD-OF-Trigger werden innerhalb von BEGIN – END geschrieben…
— Für das Produkt, das ich löschen möchte, setze ich Discontinued auf 1:
BEGIN
DECLARE @UrunID int
SELECT @UrunID=ProductID FROM deleted
UPDATE Products SET Discontinued=1
WHERE ProductID=@UrunID
END
SELECT * FROM Products WHERE ProductID=1
DELETE FROM Products WHERE ProductID=1
— INSTEAD-OF-TRIGGER laufen ANSTELLE der auslösenden Abfrage. Ihr einziges Unterscheidungsmerkmal ist, dass sie zwischen BEGIN und END geschrieben werden..
— BEISPIELDATENBANK – FÜR TRIGGER —
CREATE TABLE Ogrenciler
(
OgrenciNo int,
Ad nvarchar(50),
Soyad nvarchar(50),
DersNotu tinyint
)
GO
— GO: BEDEUTET ZUM NÄCHSTEN SCHRITT GEHEN
–CREATE TABLE Gecenler
–(
–OgrenciNo int,
–Ad nvarchar(50),
–Soyad nvarchar(50),
–DersNotu tinyint
–)
–GO
–CREATE TABLE Kalanlar
–(
–OgrenciNo int,
–Ad nvarchar(50),
–Soyad nvarchar(50),
–DersNotu tinyint
–)
—- Liegt die Note unter 50, in Kalanlar (nicht bestanden) speichern, liegt sie über 50, in Gecenler (bestanden).. Es soll selbst entscheiden..
–CREATE TRIGGER tr_KararVer
–ON Ogrenciler
–FOR INSERT
–AS
–DECLARE @No int
–DECLARE @Ad nvarchar(50)
–DECLARE @Soyad nvarchar(50)
–DECLARE @DersNotu tinyint
–SELECT @No=OgrenciNo, @Ad=Ad, @Soyad=Soyad, @DersNotu=DersNotu
–FROM INSERTED
—- In der temporären Tabelle INSERTED gibt es nur 1 Zeile..
–IF @DersNotu <50
— INSERT into Kalanlar values (@No,@Ad,@Soyad,@DersNotu)
–ELSE
— INSERT into Gecenler values (@No,@Ad,@Soyad,@DersNotu)
–INSERT into Ogrenciler values (1, ‘Mustafa’, ‘Mutlu’, 80)
–SELECT * FROM Ogrenciler
–SELECT * FROM Gecenler
–INSERT into Ogrenciler values (1, ‘Türkay’, ‘Ürkmez’, 40)
–SELECT * FROM Ogrenciler
–SELECT * FROM Kalanlar
— SICHERHEIT und PERFORMANCE..
— DBCC-BEFEHLE
DBCC ShowContig (‘Customers’)
— Ein praktischer Befehl, der Performance-Details der Tabellen in der Datenbank liefert..
— 08.12.2009, Dienstag —
— Code für die Sicherung
— ALTER DATABASE Northwind
— SET RECOVERY FULL
— Wenn ich meist am selben Ort sichere, kann ich diesen Ort in SQL registrieren. Das ist nicht nötig, nur eine Erleichterung.
— system procedure (sp)
— SQL kann beim Sichern keinen Ordner selbst anlegen. Erstellen Sie daher auf C: einen Ordner namens Yedek (Sicherung) und führen Sie dann die Sicherung aus..
sp_addumpdevice ‘DISK’, MyDevice , ‘C:\Yedek\NWFull.bak’
SELECT * FROM sys.backup_devices
BACKUP DATABASE Northwind TO MyDevice
— Sicherung ohne Device:
BACKUP DATABASE Northwind TO DISK = ‘C:\Yedek\NWManuel.bak’
— Sicherungen müssen regelmäßig erstellt werden. Will ich am Dienstag statt der kompletten Montagssicherung nur die Differenz sichern, brauche ich eine differenzielle Sicherung.
INSERT into Northwind.dbo.Products (ProductName,UnitPrice)
values (‘Speiseeis’,5)
BACKUP DATABASE Northwind TO DISK = ‘C:\Yedek\NWFark.bak’
WITH DIFFERENTIAL
— Warum sollte das Transaktionsprotokoll bereinigt werden..? Wenn INSERT-, UPDATE- und DELETE-Abfragen
ausgeführt werden, wird festgehalten, welche Abfrage wann lief und wie sie
gearbeitet hat – in der Datenbankdatei mit der Endung .ldf. Jedes Mal, wenn wir
diese Abfragen ausführen, werden sie in die Protokolldatei geschrieben. Nach einer Weile
blähen sich die Protokolldateien stark auf, sodass solche Abfragen
langsamer werden. Um sie wieder zu beschleunigen, genügt eine LOG-Sicherung (LOG BACKUP).
— Sichern wir die LOG-Datei..
UPDATE Northwind.dbo.Products SET UnitPrice=10
WHERE ProductID=81
BACKUP LOG Northwind TO DISK = ‘C:\Yedek\LogDosyasi.bak’
— ALLES SCHÖNE HAT EIN ENDE: 😉
DROP DATABASE Northwind
— aber manches lässt sich wiederherstellen:
RESTORE DATABASE Northwind FROM DISK= ‘C:\Yedek\NWFull.bak’ WITH NORECOVERY
RESTORE DATABASE Northwind FROM DISK= ‘C:\Yedek\NWFark.bak’ WITH NORECOVERY
RESTORE LOG Northwind FROM DISK= ‘C:\Yedek\LogDosyasi.bak’ WITH RECOVERY
— Der Unterschied zwischen WITH NORECOVERY und WITH RECOVERY:
— Die Wiederherstellung folgt einer bestimmten Reihenfolge. Wir führen alles auf einmal aus, lassen aber die Voll- und die differenzielle Sicherung warten und führen RECOVERY zuletzt zusammen mit dem LOG aus..


