Microsoft SQL Server Tutorial
Erste Schritte mit Microsoft SQL Server

Bemerkungen

Versionen

INSERT / SELECT / UPDATE / DELETE: Grundlagen der Datenbearbeitungssprache

D ata M Anipulation L- Sprache (kurz DML) umfasst Operationen wie INSERT , UPDATE und DELETE :

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld

In diesem Skript erstellen wir eine Tabelle , um einige grundlegende Abfragen zu veranschaulichen.

Die folgenden Beispiele zeigen, wie Tabellen abgefragt werden:

USE Northwind;
GO
SELECT TOP 10 * FROM Customers 
ORDER BY CompanyName

wählt die ersten 10 Datensätze der Tabelle Customer , sortiert nach der Spalte CompanyName aus der Datenbank Northwind (eine der Beispieldatenbanken von Microsoft, die hier heruntergeladen werden kann ):

Beachten Sie, dass Use Northwind; ändert die Standarddatenbank für alle nachfolgenden Abfragen. Sie können weiterhin auf die Datenbank verweisen, indem Sie die vollständig qualifizierte Syntax in der Form [Datenbank]. [Schema]. [Tabelle] verwenden.

SELECT TOP 10 * FROM Northwind.dbo.Customers 
ORDER BY CompanyName

SELECT TOP 10 * FROM Pubs.dbo.Authors
ORDER BY City

Dies ist nützlich, wenn Sie Daten aus verschiedenen Datenbanken abfragen. Beachten Sie, dass dbo , angegeben "zwischen", als Schema bezeichnet wird und bei der Verwendung der vollständig qualifizierten Syntax angegeben werden muss. Sie können es sich als einen Ordner in Ihrer Datenbank vorstellen. dbo ist das Standardschema. Das Standardschema kann weggelassen werden. Alle anderen benutzerdefinierten Schemata müssen angegeben werden.

Wenn die Datenbanktabelle Spalten enthält, die wie reservierte Wörter benannt sind, z. B. Date , müssen Sie den Spaltennamen wie folgt in Klammern einschließen:

-- descending order
SELECT TOP 10 [Date] FROM dbo.MyLogTable
ORDER BY [Date] DESC

Dasselbe gilt, wenn der Spaltenname Leerzeichen enthält (was nicht empfohlen wird). Eine alternative Syntax ist die Verwendung von doppelten Anführungszeichen anstelle von eckigen Klammern, zB:

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
order by "Date" desc 

ist gleichwertig, wird aber nicht so häufig verwendet. Beachten Sie den Unterschied zwischen doppelten Anführungszeichen und einfachen Anführungszeichen: Einfache Anführungszeichen werden für Zeichenfolgen verwendet

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
where UserId='johndoe'
order by "Date" desc 

ist eine gültige Syntax. Beachten Sie, dass T-SQL ein N Präfix für NChar- und NVarchar-Datentypen hat, z

SELECT TOP 10 * FROM Northwind.dbo.Customers 
WHERE CompanyName LIKE N'AL%'
ORDER BY CompanyName

Gibt alle Unternehmen zurück, deren Firmenname mit AL beginnt ( % ist ein Platzhalter, verwenden Sie den Stern in einer DOS-Befehlszeile, z. B. DIR AL* ). Für LIKE gibt es ein paar Wildcards zur Verfügung, schauen Sie hier , um weitere Details zu erfahren.

Schließt sich an

Verknüpfungen sind hilfreich, wenn Sie Felder abfragen möchten, die nicht in einer einzigen Tabelle, sondern in mehreren Tabellen vorhanden sind. Beispiel: Sie möchten alle Spalten aus der Region in der Northwind Datenbank abfragen. Sie stellen jedoch fest, dass Sie auch die RegionDescription benötigen, die in einer anderen Tabelle, Region , gespeichert ist. Es gibt jedoch einen gemeinsamen Schlüssel, RgionID dem Sie diese Informationen in einer einzigen Abfrage wie folgt kombinieren können ( Top 5 nur die ersten 5 Zeilen zurück und lässt sie weg, um alle Zeilen abzurufen):

SELECT TOP 5 Territories.*, 
    Regions.RegionDescription 
FROM Territories 
INNER JOIN Region 
    ON Territories.RegionID=Region.RegionID
ORDER BY TerritoryDescription

zeigt alle Spalten von Territories sowie die Spalte RegionDescription von Region . Das Ergebnis wird nach TerritoryDescription sortiert.

Tabellen-Aliase

Wenn für Ihre Abfrage ein Verweis auf zwei oder mehr Tabellen erforderlich ist, kann es hilfreich sein, einen Tabellenalias zu verwenden. Tabellenaliasnamen sind Kurzreferenzen auf Tabellen, die anstelle eines vollständigen Tabellennamens verwendet werden können und die Eingabe und Bearbeitung reduzieren. Die Syntax für die Verwendung eines Alias ​​lautet:

<TableName> [as] <alias>

Wo as ein optionales Schlüsselwort ist. Beispielsweise kann die vorherige Abfrage wie folgt umgeschrieben werden:

SELECT TOP 5 t.*, 
    r.RegionDescription 
FROM Territories t
INNER JOIN Region r 
    ON t.RegionID = r.RegionID
ORDER BY TerritoryDescription

Aliase müssen für alle Tabellen in einer Abfrage eindeutig sein, auch wenn Sie dieselbe Tabelle zweimal verwenden. Wenn Ihre Employee-Tabelle beispielsweise ein SupervisorId-Feld enthält, können Sie diese Abfrage verwenden, um einen Mitarbeiter und den Namen seines Vorgesetzten zurückzugeben:

SELECT e.*, 
    s.Name as SupervisorName -- Rename the field for output
FROM Employee e
INNER JOIN Employee s
    ON e.SupervisorId = s.EmployeeId
WHERE e.EmployeeId = 111

Gewerkschaften

Wie wir bereits gesehen haben, fügt ein Join Spalten aus verschiedenen Tabellenquellen hinzu. Was aber, wenn Sie Zeilen aus verschiedenen Quellen kombinieren möchten? In diesem Fall können Sie eine UNION verwenden. Angenommen, Sie planen eine Party und möchten nicht nur Mitarbeiter, sondern auch die Kunden einladen. Dann könnten Sie diese Abfrage ausführen, um dies zu tun:

SELECT FirstName+' '+LastName as ContactName, Address, City FROM Employees
UNION
SELECT ContactName, Address, City FROM Customers

Es werden Namen, Adressen und Städte der Mitarbeiter und Kunden in einer einzigen Tabelle angezeigt. Beachten Sie, dass doppelte Zeilen (falls vorhanden) automatisch entfernt werden (wenn Sie dies nicht möchten, verwenden Sie stattdessen UNION ALL ). Die Spaltennummer, die Spaltennamen, die Reihenfolge und der Datentyp müssen in allen Select-Anweisungen der Union LastName Aus diesem Grund werden beim ersten SELECT FirstName und LastName aus Employee in ContactName .

Tabellenvariablen

Wenn Sie mit temporären Daten (insbesondere in einer gespeicherten Prozedur) arbeiten müssen, kann es nützlich sein, Tabellenvariablen zu verwenden: Der Unterschied zwischen einer "echten" Tabelle und einer Tabellenvariablen besteht darin, dass sie nur für temporäre Verarbeitung im Speicher vorhanden sind.

Beispiel:

DECLARE @Region TABLE
(
  RegionID int, 
  RegionDescription NChar(50)
)

erstellt eine Tabelle im Speicher. In diesem Fall ist das @ -Präfix obligatorisch, da es sich um eine Variable handelt. Sie können alle oben genannten DML-Operationen ausführen, um Zeilen einzufügen, zu löschen und auszuwählen, z

INSERT INTO @Region values(3,'Northern')
INSERT INTO @Region values(4,'Southern')

Normalerweise füllt man es jedoch anhand einer echten Tabelle aus

INSERT INTO @Region
SELECT * FROM dbo.Region WHERE RegionID>2;

dbo.Region würde die gefilterten Werte aus der realen Tabelle dbo.Region und in die Speichertabelle @Region wo sie zur weiteren Verarbeitung verwendet werden kann. Zum Beispiel könnten Sie es in einem Join wie verwenden

SELECT * FROM Territories t
JOIN @Region r on t.RegionID=r.RegionID

die in diesem Fall alle Northern und Southern Gebiete zurückgeben würde. Weitere Informationen finden Sie hier . Temporäre Tabellen werden hier besprochen, wenn Sie mehr über dieses Thema erfahren möchten.

HINWEIS: Microsoft empfiehlt die Verwendung von Tabellenvariablen nur, wenn die Anzahl der Datenzeilen in der Tabellenvariablen weniger als 100 beträgt. Wenn Sie mit größeren Datenmengen arbeiten, verwenden Sie stattdessen eine temporäre Tabelle oder eine temporäre Tabelle .

DRUCKEN

Zeigt eine Nachricht an die Ausgabekonsole an. In SQL Server Management Studio wird dies auf der Registerkarte Nachrichten und nicht auf der Registerkarte Ergebnisse angezeigt.

PRINT 'Hello World!';

SELECT alle Zeilen und Spalten aus einer Tabelle

Syntax:

SELECT *
FROM table_name

Mit dem Sternchen-Operator * Sie alle Spalten in der Tabelle auswählen. Alle Zeilen werden ebenfalls ausgewählt, da diese SELECT Anweisung keine WHERE Klausel enthält, um Filterkriterien anzugeben.

Dies würde auch auf dieselbe Weise funktionieren, wenn Sie der Tabelle einen Aliasnamen hinzufügen, z. B. e in diesem Fall:

SELECT *
FROM Employees AS e

Wenn Sie alle aus einer bestimmten Tabelle auswählen möchten, können Sie den Alias ​​+ ". *" Verwenden:

SELECT e.*, d.DepartmentName
FROM Employees AS e
    INNER JOIN Department AS d 
        ON e.DepartmentID = d.DepartmentID

Auf Datenbankobjekte kann auch mit vollständig qualifizierten Namen zugegriffen werden:

SELECT * FROM [server_name].[database_name].[schema_name].[table_name]

Dies wird nicht unbedingt empfohlen, da das Ändern der Server- und / oder Datenbanknamen dazu führen würde, dass Abfragen mit vollständig qualifizierten Namen aufgrund ungültiger Objektnamen nicht mehr ausgeführt werden.

Beachten Sie, dass die Felder vor table_name in vielen Fällen weggelassen werden können, wenn die Abfragen auf einem einzelnen Server, einer Datenbank bzw. einem Schema ausgeführt werden. Es ist jedoch üblich, dass eine Datenbank über mehrere Schemas verfügt, und in diesen Fällen sollte der Schemaname nach Möglichkeit nicht ausgelassen werden.

Warnung: Die Verwendung von SELECT * in Produktionscode oder gespeicherten Prozeduren kann später zu Problemen führen (wenn neue Spalten zur Tabelle hinzugefügt werden oder wenn Spalten in der Tabelle neu angeordnet werden), insbesondere wenn Ihr Code einfache Annahmen zur Reihenfolge der Spalten macht oder Anzahl der zurückgegebenen Spalten. Daher ist es sicherer, Spaltennamen in SELECT-Anweisungen für Produktionscode immer explizit anzugeben.

SELECT col1, col2, col3
FROM table_name

Wählen Sie Zeilen aus, die einer Bedingung entsprechen

Im Allgemeinen lautet die Syntax:

SELECT <column names>
FROM <table name>
WHERE <condition>

Zum Beispiel:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith'

Bedingungen können komplex sein:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith' AND (City = 'New York' OR City = 'Los Angeles')

UPDATE-spezifische Zeile

UPDATE HelloWorlds
SET HelloWorld = 'HELLO WORLD!!!'
WHERE Id = 5

Der obige Code aktualisiert den Wert des Felds "HelloWorld" mit "HELLO WORLD !!!" für den Datensatz mit "Id = 5" in der HelloWorlds-Tabelle.

Hinweis: In einer Aktualisierungsanweisung wird die Verwendung einer "Wo" -Klausel empfohlen, um zu vermeiden, dass die gesamte Tabelle aktualisiert wird, sofern nicht Ihre Anforderung anders ist.

Alle Zeilen aktualisieren

Eine einfache Form der Aktualisierung erhöht alle Werte in einem bestimmten Feld der Tabelle. Dazu müssen wir das Feld und den Inkrementwert definieren

Im folgenden Beispiel wird das Score um 1 erhöht (in allen Zeilen):

UPDATE Scores
SET score = score + 1  

Dies kann gefährlich sein, da Sie Ihre Daten beschädigen können, wenn Sie versehentlich ein UPDATE für eine bestimmte Zeile mit UPDATE für alle Zeilen in der Tabelle vornehmen.

Kommentare im Code

Transact-SQL unterstützt zwei Arten des Kommentarschreibens. Kommentare werden von der Datenbank-Engine ignoriert und sind für das Lesen gedacht.

Kommentaren sind vorangestellt -- und werden ignoriert, bis eine neue Zeile gefunden wird:

-- This is a comment
SELECT *
FROM MyTable -- This is another comment
WHERE Id = 1;

Slash-Sternkommentare beginnen mit /* und enden mit */ . Der gesamte Text zwischen diesen Trennzeichen wird als Kommentarblock betrachtet.

/* This is
a multi-line
comment block. */
SELECT Id = 1, [Message] = 'First row'
UNION ALL
SELECT 2, 'Second row'
/* This is a one liner */
SELECT 'More';

Slash-Sternkommentare haben den Vorteil, dass der Kommentar verwendbar bleibt, wenn die SQL-Anweisung neue Zeilenzeichen verliert. Dies kann passieren, wenn SQL während der Problembehandlung erfasst wird.

Schrägstrich-Sternkommentare können verschachtelt sein und ein Start /* innerhalb eines Schrägstrich-Sternkommentars muss mit einem */ , um gültig zu sein. Der folgende Code führt zu einem Fehler

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/

Der Slash-Stern wird zwar als Anfang eines Kommentars betrachtet, obwohl er innerhalb des Zitats steht. Daher muss es mit einem weiteren schließenden Sternschrägstrich beendet werden. Der richtige Weg wäre

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/  */

Rufen Sie grundlegende Serverinformationen ab

SELECT @@VERSION

Gibt die Version von MS SQL Server zurück, die in der Instanz ausgeführt wird.

SELECT @@SERVERNAME

Gibt den Namen der MS SQL Server-Instanz zurück.

SELECT @@SERVICENAME

Gibt den Namen des Windows-Dienstes zurück, unter dem MS SQL Server ausgeführt wird.

SELECT serverproperty('ComputerNamePhysicalNetBIOS');

Gibt den physischen Namen des Computers zurück, auf dem SQL Server ausgeführt wird. Nützlich zum Identifizieren des Knotens in einem Failovercluster.

SELECT * FROM fn_virtualservernodes();

In einem Failovercluster wird jeder Knoten zurückgegeben, auf dem SQL Server ausgeführt werden kann. Es wird nichts zurückgegeben, wenn nicht ein Cluster.

Verwenden von Transaktionen zum sicheren Ändern von Daten

Wenn Sie Daten ändern, können Sie in einem DML-Befehl (Data Manipulation Language) Ihre Änderungen in eine Transaktion einbinden. DML umfasst UPDATE , TRUNCATE , INSERT und DELETE . Sie können beispielsweise sicherstellen, dass Sie die richtigen Daten ändern, indem Sie eine Transaktion verwenden.

DML-Änderungen sperren die betroffenen Zeilen. Wenn Sie eine Transaktion beginnen, müssen Sie die Transaktion beenden, oder alle Objekte, die in der DML geändert werden, bleiben von der Person gesperrt, die mit der Transaktion begonnen hat. Sie können Ihre Transaktion entweder mit ROLLBACK oder COMMIT beenden. ROLLBACK bringt alles in der Transaktion in den ursprünglichen Zustand zurück. COMMIT versetzt die Daten in einen Endzustand, in dem Sie Ihre Änderungen nicht ohne eine andere DML-Anweisung rückgängig machen können.

Beispiel:

--Create a test table

USE [your database]
GO
CREATE TABLE test_transaction (column_1 varchar(10))
GO

INSERT INTO 
 dbo.test_transaction
        ( column_1 )
VALUES
        ( 'a' )

BEGIN TRANSACTION --This is the beginning of your transaction

UPDATE dbo.test_transaction
SET column_1 = 'B'
OUTPUT INSERTED.*
WHERE column_1 = 'A'
  

ROLLBACK TRANSACTION  --Rollback will undo your changes
           --Alternatively, use COMMIT to save your results

SELECT * FROM dbo.test_transaction   --View the table after your changes have been run

DROP TABLE dbo.test_transaction

Anmerkungen:

• Dies ist ein vereinfachtes Beispiel, das keine Fehlerbehandlung enthält. Jede Datenbankoperation kann jedoch fehlschlagen und daher eine Ausnahme auslösen. Hier ein Beispiel, wie eine solche erforderliche Fehlerbehandlung aussehen könnte. Sie sollten niemals Transaktionen ohne Fehlerbehandlungsroutine verwenden . Andernfalls belassen Sie die Transaktion möglicherweise in einem unbekannten Status.
• Abhängig von der Isolationsstufe sperren Transaktionen die abgefragten oder geänderten Daten. Sie müssen sicherstellen, dass Transaktionen lange Zeit nicht ausgeführt werden, da sie Datensätze in einer Datenbank sperren und zu Deadlocks mit anderen parallel laufenden Transaktionen führen können. Halten Sie die in Transaktionen gekapselten Vorgänge so kurz wie möglich und minimieren Sie die Auswirkungen der gesperrten Datenmenge.

LÖSCHEN Alle Zeilen

DELETE
FROM Helloworlds

Dadurch werden alle Daten aus der Tabelle gelöscht. Die Tabelle enthält keine Zeilen, nachdem Sie diesen Code ausgeführt haben. Im Gegensatz zu DROP TABLE bleiben die Tabelle und ihre Struktur erhalten, und Sie können weiterhin neue Zeilen in diese Tabelle einfügen.

Eine andere Methode zum Löschen aller Zeilen in der Tabelle besteht darin, sie wie folgt zu kürzen:

TRUNCATE TABLE HelloWords

Der Unterschied beim DELETE-Betrieb ist mehrere:

1. Der Abschneidevorgang wird nicht in der Transaktionslogdatei gespeichert
2. Wenn das Feld IDENTITY ist, wird dieses zurückgesetzt
3. TRUNCATE kann auf die gesamte Tabelle und nicht auf einen Teil der Tabelle angewendet werden (stattdessen können Sie mit dem Befehl DELETE eine WHERE Klausel verknüpfen.)

Einschränkungen von TRUNCATE

1. TRUNCATE kann nicht ausgeführt werden, wenn ein FOREIGN KEY Verweis vorhanden ist
2. Wenn die Tabelle an einer INDEXED VIEW
3. Wenn die Tabelle mit TRANSACTIONAL REPLICATION oder MERGE REPLICATION
4. Es werden keine in der Tabelle definierten TRIGGER ausgelöst

[sic]

TRUNCATE TABLE

TRUNCATE TABLE Helloworlds 

Dieser Code löscht alle Daten aus der Tabelle Helloworlds. Tabelle abschneiden ist fast ähnlich wie Code Delete from Table löschen. Der Unterschied ist, dass Sie keine where-Klauseln mit Truncate verwenden können. Das Abschneiden von Tabellen gilt als besser als Löschen, da weniger Transaktionsprotokollbereiche verwendet werden.

Wenn eine Identitätsspalte vorhanden ist, wird sie auf den ursprünglichen Startwert zurückgesetzt (z. B. wird die automatisch inkrementierte ID von 1 neu gestartet). Dies kann zu Inkonsistenzen führen, wenn die Identitätsspalten als Fremdschlüssel in einer anderen Tabelle verwendet werden.

Erstellen Sie eine neue Tabelle und fügen Sie Datensätze aus der alten Tabelle ein

SELECT * INTO NewTable FROM OldTable

Erstellt eine neue Tabelle mit der Struktur der alten Tabelle und fügt alle Zeilen in die neue Tabelle ein.

Einige Einschränkungen

1. Sie können keine Tabellenvariable oder einen Tabellenwertparameter als neue Tabelle angeben.
2. Sie können SELECT… INTO nicht verwenden, um eine partitionierte Tabelle zu erstellen, auch wenn die Quellentabelle partitioniert ist. SELECT ... INTO verwendet das Partitionsschema der Quelltabelle nicht. Stattdessen wird die neue Tabelle in der Standarddateigruppe erstellt. Um Zeilen in eine partitionierte Tabelle einzufügen, müssen Sie zuerst die partitionierte Tabelle erstellen und dann die Anweisung INSERT INTO ... SELECT FROM verwenden.
3. In der Quelltabelle definierte Indizes, Integritätsbedingungen und Auslöser werden weder in die neue Tabelle übertragen noch können sie in der SELECT ... INTO-Anweisung angegeben werden. Wenn diese Objekte erforderlich sind, können Sie sie erstellen, nachdem Sie die SELECT ... INTO-Anweisung ausgeführt haben.
4. Durch die Angabe einer ORDER BY-Klausel kann nicht garantiert werden, dass die Zeilen in der angegebenen Reihenfolge eingefügt werden. Wenn eine spärliche Spalte in der Auswahlliste enthalten ist, wird die Eigenschaft der spärlichen Spalte nicht in die Spalte in der neuen Tabelle übertragen. Wenn diese Eigenschaft in der neuen Tabelle erforderlich ist, ändern Sie die Spaltendefinition nach der Ausführung der Anweisung SELECT ... INTO, um diese Eigenschaft einzuschließen.
5. Wenn eine berechnete Spalte in der Auswahlliste enthalten ist, ist die entsprechende Spalte in der neuen Tabelle keine berechnete Spalte. Die Werte in der neuen Spalte sind die Werte, die zum Zeitpunkt der Ausführung von SELECT ... INTO berechnet wurden.

[ sic ]

Tabellenzeilenanzahl abrufen

Mit dem folgenden Beispiel können Sie die Gesamtzahl der Zeilen für eine bestimmte Tabelle in einer Datenbank ermitteln, wenn table_name durch die Tabelle ersetzt wird, die Sie abfragen möchten:

SELECT COUNT(*) AS [TotalRowCount] FROM table_name;

Es ist auch möglich, die Zeilenanzahl für alle Tabellen abzurufen, indem Sie sich mit der Tabellenpartition basierend auf dem HEAP der Tabelle (index_id = 0) oder dem Clustered Clustered-Index (index_id = 1) mit dem folgenden Skript verbinden:

SELECT  [Tables].name                AS [TableName],
        SUM( [Partitions].[rows] )    AS [TotalRowCount]
FROM    sys.tables AS [Tables]
JOIN    sys.partitions AS [Partitions]
    ON  [Tables].[object_id]    =    [Partitions].[object_id]
    AND [Partitions].index_id IN ( 0, 1 )
--WHERE    [Tables].name = N'table name' /* uncomment to look for a specific table */
GROUP BY    [Tables].name;

Dies ist möglich, da jede Tabelle im Wesentlichen eine einzelne Partitionstabelle ist, es sei denn, zusätzliche Partitionen werden hinzugefügt. Dieses Skript hat auch den Vorteil, dass Lese- und Schreibvorgänge in den Tabellenzeilen nicht beeinträchtigt werden.