Microsoft SQL Server Zelfstudie
Aan de slag met Microsoft SQL Server

Opmerkingen

versies

INSERT / SELECT / UPDATE / DELETE: de basis van Data Manipulation Language

D ata M anipulation L aal (DML kort) omvat handelingen zoals INSERT , UPDATE en 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 dit script maken we een tabel om enkele basisquery's te demonstreren.

De volgende voorbeelden tonen hoe query's op tabellen kunnen worden uitgevoerd:

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

zal de eerste 10 verslagen van het selecteren Customer tafel, in opdracht van de kolom CompanyName uit de database Northwind (dat is een van sample databases van Microsoft, kan worden gedownload vanaf hier ):

Merk op dat Use Northwind; wijzigt de standaarddatabase voor alle volgende query's. U kunt nog steeds naar de database verwijzen met behulp van de volledig gekwalificeerde syntaxis in de vorm van [Database]. [Schema]. [Tabel]:

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

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

Dit is handig als u gegevens uit verschillende databases opvraagt. Merk op dat dbo , opgegeven "tussendoor" een schema wordt genoemd en moet worden opgegeven tijdens het gebruik van de volledig gekwalificeerde syntaxis. Je kunt het zien als een map in je database. dbo is het standaardschema. Het standaardschema kan worden weggelaten. Alle andere door de gebruiker gedefinieerde schema's moeten worden gespecificeerd.

Als de databasetabel kolommen bevat die worden genoemd als gereserveerde woorden, bijvoorbeeld Date , moet u de kolomnaam als volgt tussen haakjes plaatsen:

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

Hetzelfde geldt als de kolomnaam spaties in de naam bevat (wat niet wordt aanbevolen). Een alternatieve syntaxis is om dubbele aanhalingstekens te gebruiken in plaats van vierkante haken, bijvoorbeeld:

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

is equivalent maar niet zo vaak gebruikt. Let op het verschil tussen dubbele aanhalingstekens en enkele aanhalingstekens: enkele aanhalingstekens worden gebruikt voor tekenreeksen, dat wil zeggen

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

is een geldige syntaxis. Merk op dat T-SQL een N voorvoegsel heeft voor NChar- en NVarchar-gegevenstypen, bijv

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

geeft alle bedrijven terug met een bedrijfsnaam die begint met AL ( % is een jokerteken, gebruik het zoals u de asterisk in een DOS-opdrachtregel zou gebruiken, bijvoorbeeld DIR AL* ). Voor LIKE zijn er een aantal wildcards beschikbaar, kijk hier voor meer informatie.

Doet mee

Joins zijn handig als u velden wilt doorzoeken die niet in één enkele tabel bestaan, maar in meerdere tabellen. Bijvoorbeeld: U wilt alle kolommen uit de Region in de Northwind database opvragen. Maar je merkt dat je ook de RegionDescription , die is opgeslagen in een andere tabel, Region . Er is echter een gemeenschappelijke sleutel, RgionID die u kunt gebruiken om deze informatie als volgt in een enkele query te combineren ( Top 5 retourneert alleen de eerste 5 rijen, laat het weg om alle rijen te krijgen):

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

toont alle kolommen uit Territories plus de RegionDescription kolom uit Region . Het resultaat is geordend door TerritoryDescription .

Tabel Aliassen

Wanneer uw zoekopdracht een verwijzing naar twee of meer tabellen vereist, kan het handig zijn om een tabelalias te gebruiken. Tabelaliassen zijn steno-verwijzingen naar tabellen die kunnen worden gebruikt in plaats van een volledige tabelnaam en die het typen en bewerken kunnen verminderen. De syntaxis voor het gebruik van een alias is:

<TableName> [as] <alias>

Waar as is een optionele trefwoord. De vorige zoekopdracht kan bijvoorbeeld worden herschreven als:

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

Aliassen moeten uniek zijn voor alle tabellen in een query, zelfs als u dezelfde tabel twee keer gebruikt. Als uw tabel Werknemer bijvoorbeeld een veld SupervisorId bevat, kunt u deze query gebruiken om een werknemer en de naam van zijn supervisor te retourneren:

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

vakbonden

Zoals we eerder hebben gezien, voegt een Join kolommen uit verschillende tabelbronnen toe. Maar wat als u rijen uit verschillende bronnen wilt combineren? In dit geval kunt u een UNION gebruiken. Stel dat u een feest plant en niet alleen werknemers, maar ook de klanten wilt uitnodigen. Vervolgens kunt u deze query uitvoeren om het te doen:

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

Het retourneert namen, adressen en steden van de werknemers en klanten in één enkele tabel. Merk op dat dubbele rijen (indien aanwezig) automatisch worden geëlimineerd (als u dit niet wilt, gebruikt u in plaats daarvan een UNION ALL ). Het kolomnummer, de kolomnamen, de order en het gegevenstype moeten overeenkomen met alle select-statements die deel uitmaken van de unie - dit is de reden waarom de eerste SELECT FirstName en LastName van Employee combineert in ContactName .

Tabelvariabelen

Het kan handig zijn als u tijdelijke gegevens moet verwerken (vooral in een opgeslagen procedure) om tabelvariabelen te gebruiken: Het verschil tussen een "echte" tabel en een tabelvariabele is dat deze gewoon in het geheugen bestaat voor tijdelijke verwerking.

Voorbeeld:

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

maakt een tabel in het geheugen. In dit geval is het voorvoegsel @ verplicht omdat het een variabele is. U kunt alle bovengenoemde DML-bewerkingen uitvoeren om rijen in te voegen, te verwijderen en te selecteren, bijv

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

Maar normaal zou je het invullen op basis van een echte tabel zoals

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

die de gefilterde waarden uit de echte tabel dbo.Region zou lezen en in de geheugentabel @Region - waar het kan worden gebruikt voor verdere verwerking. Je zou het bijvoorbeeld kunnen gebruiken in een join zoals

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

die in dit geval alle Northern en Southern gebieden zou teruggeven. Meer gedetailleerde informatie vindt u hier . Tijdelijke tabellen worden hier besproken, als u geïnteresseerd bent om meer over dat onderwerp te lezen.

OPMERKING: Microsoft beveelt alleen het gebruik van tabelvariabelen aan als het aantal rijen met gegevens in de tabelvariabele kleiner is dan 100. Als u met grotere hoeveelheden gegevens werkt, gebruikt u in plaats daarvan een tijdelijke tabel of tijdelijke tabel.

AFDRUKKEN

Geef een bericht weer aan de uitvoerconsole. Met SQL Server Management Studio wordt dit weergegeven op het tabblad berichten in plaats van op het tabblad resultaten:

PRINT 'Hello World!';

SELECTEER alle rijen en kolommen uit een tabel

Syntaxis:

SELECT *
FROM table_name

Het gebruik van de asterisk-operator * dient als een snelkoppeling voor het selecteren van alle kolommen in de tabel. Alle rijen worden ook geselecteerd omdat deze SELECT instructie geen WHERE clausule heeft om filtercriteria op te geven.

Dit zou ook op dezelfde manier werken als u een alias aan de tabel toevoegt, bijvoorbeeld e in dit geval:

SELECT *
FROM Employees AS e

Of als u alles uit een specifieke tabel wilt selecteren, kunt u de alias + ". *" Gebruiken:

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

Database-objecten kunnen ook worden geopend met behulp van volledig gekwalificeerde namen:

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

Dit wordt niet noodzakelijkerwijs aanbevolen, omdat het wijzigen van de server- en / of databasenamen ertoe zou leiden dat de query's met volledig gekwalificeerde namen niet langer worden uitgevoerd vanwege ongeldige objectnamen.

Merk op dat de velden voor table_name in veel gevallen kunnen worden weggelaten als de query's worden uitgevoerd op respectievelijk een enkele server, database en schema. Het is echter gebruikelijk dat een database meerdere schema's heeft en in deze gevallen moet de schemanaam niet worden weggelaten wanneer mogelijk.

Waarschuwing: het gebruik van SELECT * in productiecode of opgeslagen procedures kan later tot problemen leiden (omdat nieuwe kolommen aan de tabel worden toegevoegd of als kolommen in de tabel worden herschikt), vooral als uw code eenvoudige veronderstellingen maakt over de volgorde van kolommen, of aantal geretourneerde kolommen. Het is dus veiliger om altijd expliciet kolomnamen op te geven in SELECT-instructies voor productiecode.

SELECT col1, col2, col3
FROM table_name

Selecteer rijen die overeenkomen met een voorwaarde

Over het algemeen is de syntaxis:

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

Bijvoorbeeld:

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

Voorwaarden kunnen complex zijn:

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

UPDATE specifieke rij

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

De bovenstaande code werkt de waarde van het veld "HelloWorld" bij met "HELLO WORLD !!!" voor het record waar "Id = 5" in de HelloWorlds-tabel.

Opmerking: In een update-instructie wordt geadviseerd om een "where" -clausule te gebruiken om te voorkomen dat de hele tabel wordt bijgewerkt, tenzij en totdat uw vereiste anders is.

UPDATE alle rijen

Een eenvoudige vorm van bijwerken is het verhogen van alle waarden in een bepaald veld van de tabel. Om dit te doen, moeten we het veld en de incrementwaarde definiëren

Het volgende is een voorbeeld dat het Score met 1 verhoogt (in alle rijen):

UPDATE Scores
SET score = score + 1  

Dit kan gevaarlijk zijn, omdat u uw gegevens kunt beschadigen als u per ongeluk een UPDATE maakt voor een specifieke rij met een UPDATE voor alle rijen in de tabel.

Opmerkingen in code

Transact-SQL ondersteunt twee vormen van het schrijven van opmerkingen. Opmerkingen worden genegeerd door de database-engine en zijn bedoeld voor mensen om te lezen.

Opmerkingen worden voorafgegaan door -- en worden genegeerd totdat een nieuwe regel wordt aangetroffen:

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

Slash star-opmerkingen beginnen met /* en eindigen met */ . Alle tekst tussen die scheidingstekens wordt beschouwd als een commentaarblok.

/* 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 star-opmerkingen hebben het voordeel dat de opmerking bruikbaar blijft als de SQL-instructie nieuwe lijntekens verliest. Dit kan gebeuren wanneer SQL wordt vastgelegd tijdens het oplossen van problemen.

Slash star-opmerkingen kunnen worden genest en een beginnende /* in een slash star-opmerking moet worden afgesloten met een */ om geldig te zijn. De volgende code resulteert in een fout

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

De schuine ster, hoewel in het citaat wordt beschouwd als het begin van een opmerking. Daarom moet het worden afgesloten met nog een laatste sterretje. De juiste manier zou zijn

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

Basic Server-informatie ophalen

SELECT @@VERSION

Retourneert de versie van MS SQL Server die op het exemplaar wordt uitgevoerd.

SELECT @@SERVERNAME

Retourneert de naam van de MS SQL Server-instantie.

SELECT @@SERVICENAME

Retourneert de naam van de Windows-service MS SQL Server wordt uitgevoerd als.

SELECT serverproperty('ComputerNamePhysicalNetBIOS');

Retourneert de fysieke naam van de machine waarop SQL Server wordt uitgevoerd. Handig om het knooppunt in een failover-cluster te identificeren.

SELECT * FROM fn_virtualservernodes();

In een failover-cluster retourneert elk knooppunt waarop SQL Server kan worden uitgevoerd. Het retourneert niets, zo niet een cluster.

Transacties gebruiken om gegevens veilig te wijzigen

Telkens wanneer u gegevens wijzigt, kunt u met een opdracht DIM-gegevens (Data Manipulation Language) uw wijzigingen in een transactie verwerken. DML omvat UPDATE , TRUNCATE , INSERT en DELETE . Een van de manieren waarop u ervoor kunt zorgen dat u de juiste gegevens wijzigt, is het gebruik van een transactie.

DML-wijzigingen zorgen ervoor dat de betreffende rijen worden vergrendeld. Wanneer u een transactie start, moet u de transactie beëindigen, anders blijven alle objecten die in de DML worden gewijzigd vergrendeld door degene die de transactie is gestart. U kunt uw transactie beëindigen met ROLLBACK of COMMIT . ROLLBACK brengt alles binnen de transactie terug naar de oorspronkelijke staat. COMMIT plaatst de gegevens in een definitieve status waar u uw wijzigingen niet ongedaan kunt maken zonder een andere DML-instructie.

Voorbeeld:

--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

Opmerkingen:

• Dit is een vereenvoudigd voorbeeld dat geen foutafhandeling omvat. Maar elke databasebewerking kan mislukken en dus een uitzondering veroorzaken. Hier is een voorbeeld van hoe een dergelijke vereiste foutafhandeling eruit zou kunnen zien. Gebruik nooit transacties zonder een foutafhandelaar , anders kunt u de transactie in een onbekende staat achterlaten.
• Afhankelijk van het isolatieniveau zorgen transacties ervoor dat de gegevens worden opgevraagd of gewijzigd. U moet ervoor zorgen dat transacties lange tijd niet worden uitgevoerd, omdat ze records in een database vergrendelen en kunnen leiden tot deadlocks met andere parallel lopende transacties. Houd de bewerkingen ingekapseld in transacties zo kort mogelijk en minimaliseer de impact met de hoeveelheid gegevens die u vergrendelt.

VERWIJDER Alle rijen

DELETE
FROM Helloworlds

Hiermee worden alle gegevens uit de tabel verwijderd. De tabel bevat geen rijen nadat u deze code hebt uitgevoerd. In tegenstelling tot DROP TABLE , behoudt dit de tabel zelf en zijn structuur en kunt u doorgaan met het invoegen van nieuwe rijen in die tabel.

Een andere manier om alle rijen in de tabel te verwijderen, is deze in te korten, als volgt:

TRUNCATE TABLE HelloWords

Verschil met DELETE-bewerking zijn verschillende:

1. De bewerking Truncate wordt niet opgeslagen in het transactielogbestand
2. Als het veld IDENTITY bestaat, wordt dit gereset
3. TRUNCATE kan op de hele tafel worden toegepast en niet op een deel ervan (in plaats daarvan kunt u met de DELETE opdracht een WHERE clausule koppelen)

Beperkingen van TRUNCATE

1. Kan een tabel niet TRUNCEREN als er een FOREIGN KEY referentie is
2. Als de tafel deelneemt aan een INDEXED VIEW
3. Als de tabel wordt gepubliceerd met behulp van TRANSACTIONAL REPLICATION of MERGE REPLICATION
4. Er wordt geen TRIGGER geactiveerd die in de tabel is gedefinieerd

[sic]

TRUNCATE LIJST

TRUNCATE TABLE Helloworlds 

Met deze code worden alle gegevens uit de tabel Helloworlds verwijderd. Beknotte tabel lijkt bijna op Delete from Table . Het verschil is dat u niet kunt gebruiken waar clausules met Truncate. Afkaptabel wordt als beter beschouwd dan verwijderen omdat deze minder transactielogboekruimten gebruikt.

Merk op dat als er een identiteitskolom bestaat, deze wordt gereset naar de initiële seed-waarde (bijvoorbeeld, de automatisch opgehoogde ID wordt opnieuw gestart vanaf 1). Dit kan tot inconsistentie leiden als de identiteitskolommen als een externe sleutel in een andere tabel worden gebruikt.

Maak een nieuwe tabel en voeg records van oude tabel in

SELECT * INTO NewTable FROM OldTable

Maakt een nieuwe tabel met de structuur van de oude tabel en voegt alle rijen in de nieuwe tabel in.

Enkele beperkingen

1. U kunt geen tabelvariabele of tabelwaardeparameter als de nieuwe tabel opgeven.
2. U kunt SELECT ... INTO niet gebruiken om een gepartitioneerde tabel te maken, zelfs niet als de brontabel gepartitioneerd is. SELECT ... INTO maakt geen gebruik van het partitieschema van de brontabel; in plaats daarvan wordt de nieuwe tabel gemaakt in de standaard bestandsgroep. Als u rijen in een gepartitioneerde tabel wilt invoegen, moet u eerst de gepartitioneerde tabel maken en vervolgens de instructie INSERT INTO ... SELECT FROM gebruiken.
3. Indexen, beperkingen en triggers die in de brontabel zijn gedefinieerd, worden niet overgedragen naar de nieuwe tabel en kunnen ook niet worden opgegeven in de instructie SELECT ... INTO. Als deze objecten vereist zijn, kunt u ze maken nadat u de instructie SELECT ... INTO hebt uitgevoerd.
4. Als u een ORDER BY-component opgeeft, kan niet worden gegarandeerd dat de rijen in de opgegeven volgorde worden ingevoegd. Wanneer een schaarse kolom is opgenomen in de selectielijst, wordt de eigenschap schaarse kolom niet overgedragen naar de kolom in de nieuwe tabel. Als deze eigenschap vereist is in de nieuwe tabel, wijzigt u de kolomdefinitie na het uitvoeren van de instructie SELECT ... INTO om deze eigenschap op te nemen.
5. Wanneer een berekende kolom is opgenomen in de selectielijst, is de overeenkomstige kolom in de nieuwe tabel geen berekende kolom. De waarden in de nieuwe kolom zijn de waarden die werden berekend op het moment dat SELECT ... INTO werd uitgevoerd.

[ sic ]

Krijgen van tabelrijtelling

Het volgende voorbeeld kan worden gebruikt om het totale aantal rijen voor een specifieke tabel in een database te vinden als table_name wordt vervangen door de tabel table_name u wilt zoeken:

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

Het is ook mogelijk om het aantal rijen voor alle tabellen te krijgen door terug te keren naar de partitie van de tabel op basis van de tabellen 'HEAP (index_id = 0) of cluster geclusterde index (index_id = 1) met behulp van het volgende script:

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;

Dit is mogelijk omdat elke tabel in wezen een enkele partitietabel is, tenzij er extra partities aan worden toegevoegd. Dit script heeft ook het voordeel dat het de lees- / schrijfbewerkingen naar de tabellenrijen niet verstoort '.