Microsoft SQL Server Samouczek
Rozpoczęcie pracy z Microsoft SQL Server

Uwagi

Wersje

INSERT / SELECT / UPDATE / DELETE: podstawy języka manipulacji danymi

D ata M anipulation L Anguage (w skrócie DML) obejmuje operacje takie jak INSERT , UPDATE i 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

W tym skrypcie tworzymy tabelę, aby zademonstrować podstawowe zapytania.

Poniższe przykłady pokazują, jak wyszukiwać tabele:

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

wybierze pierwsze 10 rekordów tabeli Customer , uporządkowanych według kolumny CompanyName z bazy danych Northwind (która jest jedną z przykładowych baz danych Microsoft, można ją pobrać stąd ):

Pamiętaj, że Use Northwind; zmienia domyślną bazę danych dla wszystkich kolejnych zapytań. Nadal możesz odwoływać się do bazy danych, używając w pełni kwalifikowanej składni w postaci [Baza danych]. [Schemat]. [Tabela]:

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

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

Jest to przydatne, jeśli przeszukujesz dane z różnych baz danych. Zauważ, że dbo , określone „pomiędzy” nazywa się schematem i musi zostać określone przy użyciu w pełni kwalifikowanej składni. Możesz myśleć o tym jak o folderze w bazie danych. dbo jest domyślnym schematem. Domyślny schemat można pominąć. Wszystkie pozostałe schematy zdefiniowane przez użytkownika muszą zostać określone.

Jeśli tabela bazy danych zawiera kolumny o nazwach podobnych do słów zastrzeżonych, np. Date , należy zawrzeć nazwę kolumny w nawiasach, jak poniżej:

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

To samo dotyczy sytuacji, gdy nazwa kolumny zawiera spacje w nazwie (co nie jest zalecane). Alternatywną składnią jest stosowanie podwójnych cudzysłowów zamiast nawiasów kwadratowych, np .:

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

jest równoważne, ale nie jest tak często używane. Zwróć uwagę na różnicę między podwójnymi cudzysłowami i pojedynczymi cudzysłowami: W przypadku ciągów znaków używane są pojedyncze cudzysłowy

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

jest poprawną składnią. Zauważ, że T-SQL ma prefiks N dla typów danych NChar i NVarchar, np

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

zwraca wszystkie firmy posiadające nazwę firmy zaczynającą się od AL ( % to symbol wieloznaczny, użyj go tak, jakbyś używał gwiazdki w wierszu poleceń DOS, np. DIR AL* ). W przypadku LIKE istnieje kilka symboli wieloznacznych, spójrz tutaj, aby uzyskać więcej informacji.

Łączy się

Połączenia są przydatne, jeśli chcesz wyszukiwać pola, które nie istnieją w jednej tabeli, ale w wielu tabelach. Na przykład: Chcesz wykonać zapytanie do wszystkich kolumn z tabeli Region w bazie danych Northwind . Ale zauważasz, że potrzebujesz również RegionDescription , który jest przechowywany w innej tabeli Region . Jednakże, istnieje wspólny klucz, RgionID których można użyć, aby połączyć te informacje w jednym zapytaniu następująco ( Top 5 prostu zwraca pierwszych 5 wierszy, pomijają to, aby wszystkie wiersze):

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

pokaże wszystkie kolumny z Territories oraz kolumnę RegionDescription z Region . Wynik jest uporządkowany według TerritoryDescription .

Aliasy tabel

Gdy zapytanie wymaga odwołania do dwóch lub więcej tabel, przydatne może być użycie aliasu tabeli. Aliasy tabel to skrócone odniesienia do tabel, które mogą być używane zamiast pełnej nazwy tabeli i mogą ograniczyć pisanie i edycję. Składnia użycia aliasu to:

<TableName> [as] <alias>

Gdzie as jest opcjonalnym słów kluczowych. Na przykład poprzednie zapytanie można przepisać jako:

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

Aliasy muszą być unikalne dla wszystkich tabel w zapytaniu, nawet jeśli użyjesz tej samej tabeli dwa razy. Na przykład, jeśli tabela pracowników zawiera pole SupervisorId, możesz użyć tego zapytania, aby zwrócić pracownika i nazwisko jego przełożonego:

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

Związki

Jak widzieliśmy wcześniej, Join dodaje kolumny z różnych źródeł tabeli. Ale co, jeśli chcesz połączyć wiersze z różnych źródeł? W takim przypadku możesz użyć UNII. Załóżmy, że planujesz przyjęcie i chcesz zaprosić nie tylko pracowników, ale także klientów. Następnie możesz uruchomić to zapytanie, aby to zrobić:

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

Zwróci nazwiska, adresy i miasta od pracowników i klientów w jednym stole. Zauważ, że zduplikowane wiersze (jeśli powinny istnieć) są automatycznie eliminowane (jeśli nie chcesz tego, użyj UNION ALL ). Numer kolumny, nazwy kolumn, kolejność i typ danych muszą być zgodne we wszystkich instrukcjach select, które są częścią unii - dlatego pierwszy SELECT łączy FirstName i LastName od pracownika w ContactName .

Zmienne tabelowe

Przydaje się, gdy trzeba poradzić sobie z danymi tymczasowymi (szczególnie w procedurze przechowywanej), aby użyć zmiennych tabeli: Różnica między tabelą „rzeczywistą” a zmienną tabeli polega na tym, że istnieje ona w pamięci do tymczasowego przetwarzania.

Przykład:

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

tworzy tabelę w pamięci. W takim przypadku przedrostek @ jest obowiązkowy, ponieważ jest zmienną. Możesz wykonać wszystkie wyżej wymienione operacje DML, aby wstawić, usunąć i wybrać wiersze, np

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

Ale zwykle wypełnia się go w oparciu o prawdziwy stół

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

który odczytuje przefiltrowane wartości z prawdziwej tabeli dbo.Region i wstawia ją do tabeli pamięci @Region - gdzie można go wykorzystać do dalszego przetwarzania. Na przykład możesz użyć go do łączenia

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

które w tym przypadku zwrócą wszystkie terytoria Northern i Southern . Bardziej szczegółowe informacje można znaleźć tutaj . Tabele tymczasowe są omawiane tutaj , jeśli chcesz przeczytać więcej na ten temat.

UWAGA: Firma Microsoft zaleca stosowanie tylko zmiennych stołowych jeśli liczba wierszy danych w zmiennej tabeli są mniej niż 100. Jeśli będziesz pracować z większymi ilościami danych, należy użyć tabeli tymczasowej lub temp tabeli, zamiast.

WYDRUKOWAĆ

Wyświetl komunikat w konsoli wyjściowej. Za pomocą SQL Server Management Studio będzie to wyświetlane na karcie wiadomości, a nie na karcie wyników:

PRINT 'Hello World!';

Wybierz wszystkie wiersze i kolumny z tabeli

Składnia:

SELECT *
FROM table_name

Korzystanie z operatora gwiazdki * służy jako skrót do wybierania wszystkich kolumn w tabeli. Wszystkie wiersze zostaną również wybrane, ponieważ ta instrukcja SELECT nie ma klauzuli WHERE celu określenia jakichkolwiek kryteriów filtrowania.

Działałoby to również w ten sam sposób, jeśli dodałeś alias do tabeli, na przykład e w tym przypadku:

SELECT *
FROM Employees AS e

Lub jeśli chcesz wybrać wszystko z określonej tabeli, możesz użyć aliasu + „. *”:

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

Dostęp do obiektów bazy danych można również uzyskać przy użyciu w pełni kwalifikowanych nazw:

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

Niekoniecznie jest to zalecane, ponieważ zmiana nazw serwera i / lub bazy danych spowodowałaby, że zapytania wykorzystujące w pełni kwalifikowane nazwy przestałyby być wykonywane z powodu nieprawidłowych nazw obiektów.

Należy zauważyć, że pola przed table_name można w wielu przypadkach pominąć, jeśli zapytania są wykonywane odpowiednio na jednym serwerze, bazie danych i schemacie. Jednak baza danych ma wiele schematów i w takich przypadkach nazwa schematu nie powinna być pomijana, gdy jest to możliwe.

Ostrzeżenie: użycie SELECT * w kodzie produkcyjnym lub procedurach przechowywanych może później prowadzić do problemów (gdy nowe kolumny są dodawane do tabeli lub jeśli kolumny są przestawiane w tabeli), szczególnie jeśli kod zawiera proste założenia dotyczące kolejności kolumn, lub liczba zwróconych kolumn. Dlatego bezpieczniej jest zawsze jawnie określać nazwy kolumn w instrukcjach SELECT dla kodu produkcyjnego.

SELECT col1, col2, col3
FROM table_name

Wybierz wiersze pasujące do warunku

Ogólnie składnia jest następująca:

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

Na przykład:

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

Warunki mogą być złożone:

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

AKTUALIZACJA Określony wiersz

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

Powyższy kod aktualizuje wartość pola „HelloWorld” o „HELLO WORLD !!!” dla rekordu, w którym „Id = 5” w tabeli HelloWorlds.

Uwaga: W instrukcji aktualizacji zaleca się stosowanie klauzuli „gdzie”, aby uniknąć aktualizacji całej tabeli, chyba że wymagania są inne.

AKTUALIZACJA Wszystkie wiersze

Prostą formą aktualizacji jest zwiększenie wszystkich wartości w danym polu tabeli. W tym celu musimy zdefiniować pole i wartość przyrostu

Poniżej znajduje się przykład, w którym zwiększa Score pola o 1 (we wszystkich rzędach)

UPDATE Scores
SET score = score + 1  

Może to być niebezpieczne, ponieważ możesz uszkodzić swoje dane, jeśli przypadkowo wykonasz UPDATE dla określonego wiersza z UPDATE dla wszystkich wierszy w tabeli.

Komentarze w kodzie

Transact-SQL obsługuje dwie formy pisania komentarzy. Komentarze są ignorowane przez silnik bazy danych i są przeznaczone do czytania.

Komentarze są poprzedzone -- i są ignorowane, dopóki nie zostanie napotkany nowy wiersz:

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

Komentarze do ukośnika zaczynają się od /* a kończą na */ . Cały tekst między tymi ogranicznikami jest traktowany jako blok komentarza.

/* 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';

Komentarze w gwiazdkach ukośnika mają tę zaletę, że umożliwiają użytkowanie komentarza, jeśli instrukcja SQL traci nowe znaki wiersza. Może się to zdarzyć, gdy SQL zostanie przechwycony podczas rozwiązywania problemów.

Komentarze gwiazdy ukośnika mogą być zagnieżdżone, a początkowy /* wewnątrz komentarza ukośnika musi być zakończony znakiem */ aby był prawidłowy. Poniższy kod spowoduje błąd

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

Gwiazda ukośnika, mimo że wewnątrz cytatu, jest uważana za początek komentarza. Dlatego należy go zakończyć kolejną ukośną gwiazdą. Prawidłowy sposób byłby

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

Pobierz podstawowe informacje o serwerze

SELECT @@VERSION

Zwraca wersję MS SQL Server działającą na instancji.

SELECT @@SERVERNAME

Zwraca nazwę wystąpienia MS SQL Server.

SELECT @@SERVICENAME

Zwraca nazwę usługi Windows MS SQL Server działa jako.

SELECT serverproperty('ComputerNamePhysicalNetBIOS');

Zwraca fizyczną nazwę komputera, na którym działa program SQL Server. Przydatne do identyfikacji węzła w klastrze pracy awaryjnej.

SELECT * FROM fn_virtualservernodes();

W klastrze pracy awaryjnej zwraca każdy węzeł, w którym SQL Server może działać. Nic nie zwraca, jeśli nie klaster.

Używanie transakcji do bezpiecznej zmiany danych

Za każdym razem, gdy zmieniasz dane, w poleceniu Data Manipulation Language (DML) możesz zawinąć swoje zmiany w transakcję. DML obejmuje UPDATE , TRUNCATE , INSERT i DELETE . Jednym ze sposobów, aby upewnić się, że zmieniasz właściwe dane, byłoby skorzystanie z transakcji.

Zmiany DML zablokują dotknięte wiersze. Po rozpoczęciu transakcji musisz zakończyć transakcję, w przeciwnym razie wszystkie obiekty zmieniane w DML pozostaną zablokowane przez tego, kto rozpoczął transakcję. Możesz zakończyć transakcję za pomocą funkcji ROLLBACK lub COMMIT . ROLLBACK przywraca wszystko w transakcji do pierwotnego stanu. COMMIT umieszcza dane w końcowym stanie, w którym nie można cofnąć zmian bez kolejnej instrukcji DML.

Przykład:

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

Uwagi:

• To jest uproszczony przykład, który nie obejmuje obsługi błędów. Ale każda operacja na bazie danych może się nie powieść i dlatego zgłasza wyjątek. Oto przykład, jak może wyglądać taka wymagana obsługa błędów. Nigdy nie należy używać transakcji bez procedury obsługi błędów , w przeciwnym razie możesz pozostawić transakcję w nieznanym stanie.
• W zależności od poziomu izolacji transakcje blokują dane, które są wyszukiwane lub zmieniane. Należy upewnić się, że transakcje nie są uruchomione przez długi czas, ponieważ będą blokować rekordy w bazie danych i mogą prowadzić do impasu w przypadku innych równoległych transakcji. Utrzymuj operacje zamknięte w transakcjach tak krótkie, jak to możliwe i minimalizuj wpływ dzięki ilości blokowanych danych.

Usuń wszystkie wiersze

DELETE
FROM Helloworlds

Spowoduje to usunięcie wszystkich danych z tabeli. Tabela nie będzie zawierać wierszy po uruchomieniu tego kodu. W przeciwieństwie do DROP TABLE , zachowuje to samą tabelę i jej strukturę i możesz nadal wstawiać nowe wiersze do tej tabeli.

Innym sposobem usunięcia wszystkich wierszy w tabeli jest obcięcie go w następujący sposób:

TRUNCATE TABLE HelloWords

Różnice między operacją DELETE są następujące:

1. Operacja obcięcia nie zapisuje się w pliku dziennika transakcji
2. Jeśli istnieje pole IDENTITY , zostanie ono zresetowane
3. TRUNCATE można zastosować do całej tabeli, a nie do jej części (zamiast polecenia DELETE można skojarzyć klauzulę WHERE )

Ograniczenia TRUNCATE

1. Nie można TRUNCATE tabeli, jeśli istnieje odwołanie do FOREIGN KEY
2. Jeśli tabela jest INDEXED VIEW
3. Jeśli tabela zostanie opublikowana przy użyciu TRANSACTIONAL REPLICATION lub MERGE REPLICATION
4. Nie uruchomi żadnego wyzwalacza zdefiniowanego w tabeli

[sic]

TABELA ŚCIEŻKI

TRUNCATE TABLE Helloworlds 

Ten kod usunie wszystkie dane z tabeli Helloworlds. Obcięta tabela jest prawie podobna do Delete from Table kodu Delete from Table . Różnica polega na tym, że nie można używać klauzul where w programie Truncate. Tabela obcięcia jest uważana za lepszą niż usuwanie, ponieważ wykorzystuje mniej przestrzeni dzienników transakcji.

Zauważ, że jeśli kolumna tożsamości istnieje, jest ona resetowana do początkowej wartości początkowej (na przykład automatycznie zwiększany identyfikator zostanie zrestartowany od 1). Może to prowadzić do niespójności, jeśli kolumny tożsamości zostaną użyte jako klucz obcy w innej tabeli.

Utwórz nową tabelę i wstaw rekordy ze starej tabeli

SELECT * INTO NewTable FROM OldTable

Tworzy nową tabelę o strukturze starej tabeli i wstawia wszystkie wiersze do nowej tabeli.

Niektóre ograniczenia

1. Nie można określić zmiennej tabeli lub parametru o wartości tabeli jako nowej tabeli.
2. Nie można użyć opcji WYBIERZ… INTO, aby utworzyć partycjonowaną tabelę, nawet jeśli tabela źródłowa jest podzielona na partycje. WYBIERZ ... INTO nie używa schematu partycji tabeli źródłowej; zamiast tego nowa tabela jest tworzona w domyślnej grupie plików. Aby wstawić wiersze do tabeli podzielonej na partycje, należy najpierw utworzyć tabelę podzieloną na partycje, a następnie użyć instrukcji INSERT INTO ... SELECT FROM.
3. Indeksy, ograniczenia i wyzwalacze zdefiniowane w tabeli źródłowej nie są przenoszone do nowej tabeli, ani nie można ich określić w instrukcji SELECT ... INTO. Jeśli te obiekty są wymagane, możesz je utworzyć po wykonaniu instrukcji SELECT ... INTO.
4. Określenie klauzuli ORDER BY nie gwarantuje, że wiersze zostaną wstawione w określonej kolejności. Gdy rzadka kolumna znajduje się na liście wyboru, właściwość rzadkiej kolumny nie jest przenoszona do kolumny w nowej tabeli. Jeśli ta właściwość jest wymagana w nowej tabeli, zmień definicję kolumny po wykonaniu instrukcji SELECT ... INTO, aby uwzględnić tę właściwość.
5. Gdy kolumna obliczeniowa znajduje się na liście wyboru, odpowiednia kolumna w nowej tabeli nie jest kolumną obliczaną. Wartości w nowej kolumnie są wartościami obliczonymi w momencie wykonania instrukcji SELECT ... INTO.

[ sic ]

Uzyskiwanie liczby wierszy tabeli

Poniższy przykład może być użyty do znalezienia całkowitej liczby wierszy dla określonej tabeli w bazie danych, jeśli table_name jest zastąpiona przez tabelę, którą chcesz zapytać:

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

Możliwe jest również uzyskanie liczby wierszy dla wszystkich tabel, łącząc się ponownie z partycją tabeli na podstawie HEAP tabel (id_indeksu = 0) lub indeksu klastrowego klastra (id_indeksu = 1) przy użyciu następującego skryptu:

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;

Jest to możliwe, ponieważ każda tabela jest zasadniczo jedną tabelą partycji, chyba że zostaną do niej dodane dodatkowe partycje. Zaletą tego skryptu jest to, że nie ingeruje w operacje odczytu / zapisu w wierszach tabel.