Microsoft SQL Server учебник
Начало работы с Microsoft SQL Server

замечания

Версии

INSERT / SELECT / UPDATE / DELETE: основы языка манипулирования данными

D ata M anipulation L anguage (короткий для DML) включает такие операции, как INSERT , UPDATE и 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

В этом скрипте мы создаем таблицу для демонстрации некоторых основных запросов.

В следующих примерах показано, как запрашивать таблицы:

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

выберет первые 10 записей таблицы Customer , упорядоченные столбцом CompanyName из базы данных Northwind (которая является одной из примерных баз данных Microsoft, ее можно загрузить отсюда ):

Обратите внимание, что Use Northwind; изменяет базу данных по умолчанию для всех последующих запросов. Вы все равно можете ссылаться на базу данных, используя полный синтаксис в форме [Database]. [Схема]. [Таблица]:

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

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

Это полезно, если вы запрашиваете данные из разных баз данных. Обратите внимание, что dbo , заданный «in between», называется схемой и должен быть указан при использовании полного синтаксиса. Вы можете думать о нем как о папке в своей базе данных. dbo - это схема по умолчанию. Схема по умолчанию может быть опущена. Все остальные пользовательские схемы должны быть указаны.

Если таблица базы данных содержит столбцы, имена которых называются зарезервированными словами, например Date , вам нужно заключить имя столбца в скобки, например:

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

То же самое относится, если имя столбца содержит пробелы в имени (что не рекомендуется). Альтернативный синтаксис заключается в использовании двойных кавычек вместо квадратных скобок, например:

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

эквивалентен, но не так часто используется. Обратите внимание на разницу между двойными кавычками и одинарными кавычками: одинарные кавычки используются для строк, т. Е.

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

является допустимым синтаксисом. Обратите внимание, что T-SQL имеет префикс N для типов данных NChar и NVarchar, например

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

возвращает все компании, имеющие название компании, начиная с AL ( % - это дикая карта, используйте ее так же, как вы бы использовали звездочку в командной строке DOS, например DIR AL* ). Для LIKE существует несколько подстановочных знаков, посмотрите здесь, чтобы узнать подробности.

присоединяется

Соединения полезны, если вы хотите запрашивать поля, которые не существуют в одной таблице, но в нескольких таблицах. Например: вы хотите запросить все столбцы из таблицы Region в базе данных Northwind . Но вы заметили, что вам нужен также RegionDescription , который хранится в другой таблице Region . Тем не менее, существует общий ключ RgionID который можно использовать для объединения этой информации в один запрос следующим образом ( Top 5 просто возвращает первые 5 строк, опустите его, чтобы получить все строки):

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

будут показаны все столбцы из Territories и столбец RegionDescription из Region . Результат упорядочен по TerritoryDescription .

Псевдонимы таблиц

Когда ваш запрос требует ссылки на две или более таблицы, вы можете счесть полезным использовать псевдоним таблицы. Табличные псевдонимы - это сокращенные ссылки на таблицы, которые можно использовать вместо полного имени таблицы, и могут уменьшить типизацию и редактирование. Синтаксис для использования псевдонима:

<TableName> [as] <alias>

Где as необязательное ключевое слово. Например, предыдущий запрос можно переписать как:

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

Псевдонимы должны быть уникальными для всех таблиц в запросе, даже если вы используете одну и ту же таблицу дважды. Например, если в таблице Employee указано поле SupervisorId, вы можете использовать этот запрос для возврата сотрудника и его имени руководителя:

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

Союзы

Как мы видели ранее, объединение добавляет столбцы из разных источников таблицы. Но что, если вы хотите объединить строки из разных источников? В этом случае вы можете использовать UNION. Предположим, вы планируете вечеринку и хотите пригласить не только сотрудников, но и клиентов. Затем вы можете запустить этот запрос, чтобы сделать это:

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

Он будет возвращать имена, адреса и города от сотрудников и клиентов в одной таблице. Обратите внимание, что дублирующиеся строки (если они должны быть какие-либо) автоматически удаляются (если вы этого не хотите, используйте вместо этого UNION ALL ). Номер столбца, имена столбцов, порядок и тип данных должны соответствовать всем операторам выбора, которые являются частью объединения, поэтому первый SELECT объединяет FirstName и LastName от Employee в ContactName .

Переменные таблицы

Это может быть полезно, если вам нужно иметь дело с временными данными (особенно в хранимой процедуре), использовать табличные переменные: разница между «реальной» таблицей и табличной переменной заключается в том, что она существует только в памяти для временной обработки.

Пример:

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

создает таблицу в памяти. В этом случае префикс @ является обязательным, поскольку он является переменной. Вы можете выполнить все операции DML, упомянутые выше, для вставки, удаления и выбора строк, например

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

Но, как правило, вы заполняете его на основе реальной таблицы, например

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

который будет считывать отфильтрованные значения из реальной таблицы dbo.Region и вставлять их в таблицу памяти @Region где ее можно использовать для дальнейшей обработки. Например, вы можете использовать его в

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

которая в этом случае вернет все Northern и Southern территории. Более подробную информацию можно найти здесь . Временные таблицы обсуждаются здесь , если вам интересно узнать больше об этой теме.

ПРИМЕЧАНИЕ. Microsoft рекомендует использовать переменные таблицы, если количество строк данных в переменной таблицы меньше 100. Если вы будете работать с большими объемами данных, вместо этого используйте временную таблицу или таблицу temp.

РАСПЕЧАТАТЬ

Отображение сообщения на выходной консоли. Используя SQL Server Management Studio, это будет отображаться на вкладке сообщений, а не на вкладке результатов:

PRINT 'Hello World!';

ВЫБРАТЬ все строки и столбцы из таблицы

Синтаксис:

SELECT *
FROM table_name

Использование оператора asterisk * служит ярлыком для выбора всех столбцов в таблице. Все строки также будут выбраны, потому что этот SELECT не имеет предложения WHERE , чтобы указать любые критерии фильтрации.

Это также будет работать так же, если вы добавили псевдоним в таблицу, например e в этом случае:

SELECT *
FROM Employees AS e

Или, если вы хотите выбрать все из конкретной таблицы, вы можете использовать псевдоним + ". *":

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

К объектам базы данных также можно получить доступ с использованием полных имен:

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

Это не обязательно рекомендуется, так как изменение имени сервера и / или базы данных приведет к тому, что запросы с использованием полностью квалифицированных имен перестанут выполняться из-за неправильных имен объектов.

Обратите внимание, что поля before table_name могут быть опущены во многих случаях, если запросы выполняются на одном сервере, базе данных и схеме соответственно. Однако для базы данных часто существует несколько схем, и в этих случаях имя схемы не должно быть опущено, когда это возможно.

Предупреждение. Использование SELECT * в производственном коде или хранимых процедурах может привести к проблемам позже (при добавлении новых столбцов в таблицу или перестановке столбцов в таблице), особенно если ваш код делает простые предположения о порядке столбцов, или количество возвращенных столбцов. Поэтому безопаснее всегда явно указывать имена столбцов в операторах SELECT для производственного кода.

SELECT col1, col2, col3
FROM table_name

Выберите строки, соответствующие условию

Как правило, синтаксис:

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

Например:

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

Условия могут быть сложными:

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

UPDATE Specific Row

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

Вышеупомянутый код обновляет значение поля «HelloWorld» с «HELLO WORLD !!!» для записи, где «Id = 5» в таблице HelloWorlds.

Примечание. В операторе обновления рекомендуется использовать предложение «где», чтобы избежать обновления всей таблицы, пока и до тех пор, пока ваше требование не будет отличаться.

ОБНОВЛЕНИЕ ВСЕХ строк

Простая форма обновления увеличивает все значения в заданном поле таблицы. Для этого нам нужно определить поле и значение приращения

Ниже приведен пример, который увеличивает поле « Score на 1 (во всех строках):

UPDATE Scores
SET score = score + 1  

Это может быть опасно, так как вы можете повредить свои данные, если вы случайно внесете UPDATE для определенной строки со строками UPDATE для всех в таблице.

Комментарии в коде

Transact-SQL поддерживает две формы написания комментариев. Комментарии игнорируются движком базы данных и предназначены для чтения людьми.

Комментарии предшествуют -- и игнорируются до тех пор, пока не встретится новая строка:

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

Комментарии к слайду начинаются с /* и заканчиваются на */ . Весь текст между этими разделителями рассматривается как блок комментариев.

/* 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 имеют то преимущество, что комментарий может использоваться, если SQL Statement теряет новые строковые символы. Это может произойти при захвате SQL во время устранения неполадок.

Комментарии к Slash star могут быть вложенными, а начало /* внутри комментария слэш-символом должно заканчиваться на */ чтобы быть действительным. Следующий код приведет к ошибке

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

Слэш-звезда, хотя внутри цитаты считается началом комментария. Следовательно, его нужно закончить с помощью другой закрывающей звезды. Правильный путь

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

Получить базовую информацию о сервере

SELECT @@VERSION

Возвращает версию MS SQL Server, запущенную на экземпляре.

SELECT @@SERVERNAME

Возвращает имя экземпляра MS SQL Server.

SELECT @@SERVICENAME

Возвращает имя службы Windows. MS SQL Server работает как.

SELECT serverproperty('ComputerNamePhysicalNetBIOS');

Возвращает физическое имя машины, на которой работает SQL Server. Полезно для идентификации узла в отказоустойчивом кластере.

SELECT * FROM fn_virtualservernodes();

В отказоустойчивом кластере возвращается каждый узел, на котором может работать SQL Server. Он не возвращает ничего, если не кластер.

Использование транзакций для безопасного изменения данных

Всякий раз, когда вы меняете данные, в команде Data Manipulation Language (DML) вы можете обернуть свои изменения в транзакции. DML включает UPDATE , TRUNCATE , INSERT и DELETE . Один из способов, которым вы можете убедиться, что вы меняете правильные данные, - это использовать транзакцию.

Изменения DML будут блокировать затронутые строки. Когда вы начинаете транзакцию, вы должны завершить транзакцию, или все объекты, которые будут изменены в DML, останутся заблокированными тем, кто начал транзакцию. Вы можете завершить транзакцию с помощью ROLLBACK или COMMIT . ROLLBACK возвращает все транзакции в исходное состояние. COMMIT помещает данные в конечное состояние, где вы не можете отменить свои изменения без другого оператора DML.

Пример:

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

Заметки:

• Это упрощенный пример, который не включает обработку ошибок. Но любая операция базы данных может выйти из строя и, следовательно, вызвать исключение. Вот пример того, как может выглядеть такая необходимая обработка ошибок. Вы никогда не должны использовать транзакции без обработчика ошибок , иначе вы можете оставить транзакцию в неизвестном состоянии.
• В зависимости от уровня изоляции транзакции помещают блокировки на запрашиваемые или измененные данные. Вам необходимо убедиться, что транзакции не работают в течение длительного времени, поскольку они будут блокировать записи в базе данных и могут привести к взаимоблокировкам с другими параллельными транзакциями. Держите операции, заключенные в транзакции как можно короче, и минимизируйте влияние количества данных, которые вы блокируете.

УДАЛИТЬ ВСЕ строки

DELETE
FROM Helloworlds

Это приведет к удалению всех данных из таблицы. После запуска этого кода таблица не будет содержать строк. В отличие от DROP TABLE , это сохраняет таблицу и ее структуру, и вы можете продолжать вставлять новые строки в эту таблицу.

Другой способ удалить все строки в таблице - усечь его, как показано ниже:

TRUNCATE TABLE HelloWords

Разница с операцией DELETE несколько:

1. Операция Truncate не сохраняется в файле журнала транзакций
2. Если существует поле IDENTITY , это будет сброшено
3. TRUNCATE может применяться на всей таблице, а не на части (вместо этого с помощью команды DELETE вы можете связать предложение WHERE )

Ограничения TRUNCATE

1. Невозможно TRUNCATE таблицы, если есть ссылка FOREIGN KEY
2. Если в таблице участвуют INDEXED VIEW
3. Если таблица публикуется с использованием TRANSACTIONAL REPLICATION или MERGE REPLICATION
4. Он не будет запускать любой TRIGGER, определенный в таблице

[так в оригинале]

TRUNCATE TABLE

TRUNCATE TABLE Helloworlds 

Этот код удалит все данные из таблицы Helloworlds. Таблица усечений почти аналогична Delete from Table . Разница в том, что вы не можете использовать where clauses с Truncate. Таблица усечения считается лучше, чем удаление, поскольку она использует меньше пространства журналов транзакций.

Обратите внимание, что если столбец идентификатора существует, он сбрасывается до начального начального значения (например, автоматически увеличиваемый ID будет перезапущен с 1). Это может привести к несогласованности, если столбцы идентификации используются в качестве внешнего ключа в другой таблице.

Создать новую таблицу и вставить записи из старой таблицы

SELECT * INTO NewTable FROM OldTable

Создает новую таблицу со структурой старой таблицы и вставляет все строки в новую таблицу.

Некоторые ограничения

1. Вы не можете указать в качестве новой таблицы переменную таблицы или таблицы.
2. Вы не можете использовать SELECT ... INTO для создания секционированной таблицы, даже если исходная таблица разделена. SELECT ... INTO не использует схему раздела исходной таблицы; вместо этого новая таблица создается в файловой группе по умолчанию. Чтобы вставлять строки в секционированную таблицу, вы должны сначала создать секционированную таблицу, а затем использовать инструкцию INSERT INTO ... SELECT FROM.
3. Индексы, ограничения и триггеры, определенные в исходной таблице, не переносятся в новую таблицу и не могут быть указаны в инструкции SELECT ... INTO. Если эти объекты необходимы, их можно создать после выполнения инструкции SELECT ... INTO.
4. Указание предложения ORDER BY не гарантирует, что строки будут вставлены в указанном порядке. Когда разреженный столбец включен в список выбора, свойство разреженного столбца не переносится в столбец в новой таблице. Если это свойство требуется в новой таблице, измените определение столбца после выполнения инструкции SELECT ... INTO, чтобы включить это свойство.
5. Когда вычисленный столбец включен в список выбора, соответствующий столбец в новой таблице не является вычисленным столбцом. Значения в новом столбце - это значения, которые были вычислены в момент выполнения SELECT ... INTO.

[ sic ]

Получение таблицы Row Count

Следующий пример может быть использован , чтобы найти общее количество строк для конкретной таблицы в базе данных , если table_name заменяется таблицами , которую вы хотите запросить:

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

Также можно получить подсчет строк для всех таблиц, присоединившись к разделу таблицы, основываясь на таблицах HEAP (index_id = 0) или кластерном индексе кластера (index_id = 1), используя следующий скрипт:

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;

Это возможно, так как каждая таблица по существу является отдельной таблицей разделов, если к ней не добавляются дополнительные разделы. Этот скрипт также имеет преимущество не вмешиваться в операции чтения / записи в строки таблиц.