Приветствую всех, сегодня мы рассмотрим t-sql запросы.
SQL ( Structured Query Language ) — язык структурированных запросов, применяемый для создания, модификации и управления данными в реляционных базах данных.
T-SQL — это собственный диалект языка структурированных запрос (SQL), применяемый в СУБД SQL Server.
SQL был расширен такими дополнительными возможностями:
- Управляющие операторы.
- Локальные и глобальные переменные.
- Различные дополнительные функции.
- Поддержка аутентификации Microsoft Windows.
Директивы сценария — это специфические команды, которые используются только в MS SQL. Эти команды помогают серверу определять правила работы со скриптом и транзакциями. Типичные представители: GO — информирует программы SQL Server об окончании пакета инструкций Transact-SQL, EXEC (или EXECUTE ) — выполняет процедуру
или скалярную функцию.
Выборка данных – это совокупность, выделенная из общего числа данных, обладающая объявленными свойствами.
Data Manipulation Language (DML)— это семейство компьютерных языков, используемых пользователями баз данных для получения, вставки, удаления или изменения данных в базах данных.
Функции DML :
INSERT – вставка данных
SELECT – выборка данных
UPDATE – изменение данных
DELETE – удаление данных
Для подробного рассмотрение этих функций, будем экспериментировать над нашей таблицей которую мы создали в прошлом уроке
Как вы помните в прошлом уроке мы создали бд и таблицу, в которую благодаря конструктору внесли данные. Сейчас мы расширим нашу таблицу и сделаем это программно.
Для этого используем оператор вставки INSERT
|
1 2 3 4 5 6 |
-- Можно явно изменить порядок записываемых даных. INSERT INTO MyFriends (LastName, FirstName, BirthDate, PhoneNumber) -- Указание порядка записи данных. VALUES ('Martinov', 'Kostya', '01/03/1986', '097*******'); -- Вводимые данные. GO |
|
1 2 3 4 5 |
INSERT INTO MyFriends -- Указание порядка записи данных отсутствует, значит используем порядок по умолчанию. VALUES ('Vitia', 'Sokolov', '01/03/1986', '095*******'); -- Вводимые данные. GO |
|
1 2 3 4 5 6 |
-- Записываем строку в таблицу MyFreand INSERT INTO MyFriends -- Ключевое слово INTO можно не использовать. (FirstName, BirthDate, PhoneNumber) -- Указание порядка записи данных. VALUES ('Max', '02/03/1988', '093*******'); -- Вводимые данные. GO |
После того как мы выполнили все 3 блока кода, мы рассмотрим пример как можно выбраться все данные из таблицы, сделать это можно с помощью запроса:
|
1 |
SELECT * FROM MyFriends |
SELECT-Выбрать
*-все
From-из
MyFriend-имя таблицы.
Выполнив его у нас должно получиться как на скриншоте ниже:
Однако стоит помнить что у нас не все поля могут быть NULL и при отсутствии данных во время записи, может возникнуть ошибка.
Следующий пример покажет как можно одновременно добавить в нашу таблицу несколько данных людей:
|
1 2 3 4 5 6 7 8 |
INSERT INTO MyFriends (FirstName,LastName,BirthDate,PhoneNumber) VALUES -- Начиная с 2008 SQL, допускается вводить несколько строк одновременно ('Andrey', 'Petrov', '02/07/1982', '234*******'), ('Anna', 'Klochkova', '03/10/1992', '144*******'), ('Sergei', 'Ivanov', '05/11/1995', '223*******'), ('Sofiy', 'Badeevna', '13/09/1983', '493*******') GO |
Теперь рассмотрим варианты запросов отвечающих за выборку данных SELECT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-------------------------------------------------------------------------- -- Выборка данных. Запрос SELECT -------------------------------------------------------------------------- -- Производим выборку всех данных из таблицы MyFriends. SELECT * FROM MyFriends; -- Производим выборку данных из столбца FirstName, таблицы MyFriends SELECT FirstName FROM MyFriends; -- Производим выборку данных из столбца LastName, таблицы MyFriends SELECT LastName FROM MyFriends; SELECT BirthDate FROM MyFriends; -- Производим выборку данных из столбцов FirstName и LastName, таблицы MyFriends SELECT FirstName, LastName FROM MyFriends; -- Производим выборку данных из столбцов FirstName, LastName и BirthDate, таблицы MyFriends SELECT FirstName,LastName,BirthDate FROM MyFriends; -------------------------------------------------------------------------- |
Далее для работы нам потребуется скачать AdventureWorks2012_Data.mdf из интернета. Так как в базе содержится много таблиц которые мы будем рассматривать. После того как скачали, в свойствах файла уберите флажок только для чтения, и не забудьте там же нажать кнопку разблокировать. Иначе будет ошибка добавления базы данных. Так же вам потребуется запустить Microsoft SQL Server Management Studio 17 от имени администратора, иначе у вас будут вылетать ошибки, которые не позволят добавить вам эту базу данных.
После добавления базы не забудьте выполнить следующую команду:
|
1 2 |
USE AdventureWorks2012; GO |
которая позволит вам переключиться на работу к базе данных AdventureWorks2012_Data.mdf
Попробуем выбрать из таблицы CreditCard всех: типов кредитных карт, номеров карт, месяца до которого действительна карта, и года до которого действительна карта
|
1 |
SELECT CardType, CardNumber, ExpMonth, ExpYear FROM Sales.CreditCard; -- Sales - схема |
Производим выборку всех данных из таблицы CreditCard, где значения ячеек столбца CreditCardID равны 10
|
1 2 |
SELECT * FROM Sales.CreditCard WHERE CreditCardID = 10; -- В конструкции WHERE применена операция сравнения (=) |
Производим выборку всех данных из таблицы CreditCard, где значения ячеек столбца CreditCardID меньше 10.
|
1 2 |
SELECT * FROM Sales.CreditCard WHERE CreditCardID < 10; -- В конструкции WHERE применена операция сравнения (<) |
Производим выборку всех данных из таблицы CreditCard, где значения ячеек столбца CreditCardID лежат в диапазоне от 1 до 3 (включительно).
|
1 2 |
SELECT * FROM Sales.CreditCard WHERE CreditCardID BETWEEN 1 AND 3 -- В конструкции WHERE применена операция проверки диапазона. |
Производим выборку данных из столбцов CardType и ExpYear, таблицы CreditCard, где значения ячеек столбца ExpYear равно 2005 или 2006.
|
1 2 |
SELECT * FROM Sales.CreditCard WHERE ExpYear = 2005 OR ExpYear = 2007 -- Логическая операция "ИЛИ" |
Производим выборку всех данных из таблицы CreditCard, где значения ячеек столбца ExpYear лежат в диапазоне от 2005 до 2006 (включительно).
|
1 2 |
SELECT * FROM Sales.CreditCard WHERE ExpYear BETWEEN 2005 AND 2007 -- В конструкции WHERE применена операция проверки диапазона. |
Производим выборку данных из столбцов CardType и ExpYear, таблицы CreditCard, где значения ячеек столбца ExpYear равно 2005 и значения ячеек столбца CardType равно ‘Vista’.
|
1 2 |
SELECT CardType, ExpYear FROM Sales.CreditCard WHERE ExpYear = 2005 AND CardType = 'Vista' -- Логическая операция "И" |
Если у вас ячейки имеют тип Data и вы хотите выбрать только с определенной даты, до определенной даты, то этом можно сделать по аналогии:
|
1 2 |
SELECT * FROM Product WHERE SellStartDate BETWEEN '20/08/2011' AND '10/08/2018'; |
Производим выборку данных из столбцов CardType и ExpYear, таблицы CreditCard, где значения ячеек столбца ExpYear не равно 2006
|
1 2 |
SELECT CardType, ExpYear FROM Sales.CreditCard WHERE NOT ExpYear = 2006 -- Логическая операция "НЕ" |
Производим выборку данных из столбцов CardType и ExpYear, таблицы CreditCard, где значения ячеек столбца CardType соответствуют шаблону ‘ColonialVoice’
|
1 2 |
SELECT CardType, ExpYear FROM Sales.CreditCard WHERE CardType LIKE 'ColonialVoice' -- LIKE - операция проверки соответствия заданному шаблону - 'ColonialVoice' |
|
1 2 |
SELECT CardType, ExpYear FROM Sales.CreditCard WHERE CardType = 'ColonialVoice' |
Производим выборку данных из столбцов CardType и ExpYear, таблицы CreditCard, где значения ячеек столбца CardType соответствуют шаблону ‘Dis%’
|
1 2 3 |
SELECT CardType, ExpYear FROM Sales.CreditCard WHERE CardType LIKE 'Dis%' -- Вывод всех карт, имя типа которых начинается на 'Dis', -- знак % обозначет произвольное количество символов после Dis. |
|
1 2 |
SELECT CardType, ExpYear FROM Sales.CreditCard WHERE CardType = 'Dis%' -- Выборка пустая потому что такого значения как 'Dis%' нет в таблице |
Производим выборку данных из столбцов CardType и ExpYear, таблицы CreditCard, где значения ячеек столбца CardType соответствуют шаблону ‘Vis__’
|
1 2 |
SELECT CardType, ExpYear FROM Sales.CreditCard WHERE CardType LIKE 'Vis__' --_ - нижнее подчеркивание определяет любой один символ после Vis |
Производим выборку данных из столбцов CardType и ExpYear, таблицы CreditCard, где значения ячеек столбца CardType соответствуют шаблону ‘Vis_’
|
1 2 |
SELECT CardType, ExpYear FROM Sales.CreditCard WHERE CardType LIKE 'Vis_' -- почему выборка пустая? |
Производим выборку данных из столбцов CardType и ExpYear, таблицы CreditCard, где значения ячеек столбца CardType соответствуют шаблону ‘Vis_а’
|
1 2 |
SELECT CardType, ExpYear FROM Sales.CreditCard WHERE CardType LIKE 'Vis_a' |
Производим выборку данных из столбцов CardType и ExpYear, таблицы CreditCard, где значения ячеек столбца CardType соответствуют шаблону ‘%ist%’
|
1 2 |
SELECT CardType, ExpYear FROM Sales.CreditCard WHERE CardType LIKE '%ist%' |
Производим выборку всех данных из таблицы CreditCard, где значения ячеек столбца ExpMonth равняются одному из значений ряда (1, 5, 11)
|
1 |
SELECT * FROM Sales.CreditCard WHERE ExpMonth IN (1, 5, 11); -- IN (1, 5, 11) определяет значения ExpMonth равные 1 или 5 или 11 |
|
1 |
SELECT * FROM Sales.CreditCard WHERE ExpMonth = 1 or ExpMonth = 5 or ExpMonth = 11; |
Конструкция ORDER BY — УПОРЯДОЧИТЬ ПО…
Производим выборку данных из столбцов BusinessEntityID, FirstName, LastName, MiddleName и ModifiedDate, где данные упорядочены по столбцу FirstName, таблицы Person.Person
|
1 2 3 |
SELECT BusinessEntityID, FirstName, LastName, MiddleName, ModifiedDate FROM Person.Person ORDER BY FirstName; -- Упорядочить по имени. |
Производим выборку данных из столбцов BusinessEntityID, FirstName, LastName, MiddleName и ModifiedDate, где данные упорядочены по столбцу ModifiedDate, таблицы Person.Person
|
1 2 3 |
SELECT BusinessEntityID, FirstName, LastName, MiddleName, ModifiedDate FROM Person.Person ORDER BY ModifiedDate; -- Сортировать по ModifiedDate |
Производим выборку данных из столбцов BusinessEntityID, FirstName, LastName, MiddleName и ModifiedDate, где данные упорядочены по столбцам FirstName и LastName, таблицы Person.Person
|
1 2 3 |
SELECT BusinessEntityID, FirstName, LastName, MiddleName, ModifiedDate FROM Person.Person ORDER BY FirstName, LastName; -- Cортировать по Имени и Фамилии |
Производим выборку данных из столбцов BusinessEntityID, FirstName, LastName, MiddleName и ModifiedDate, где данные упорядочены по столбцу FirstName, таблицы Person.Person
|
1 2 3 |
SELECT BusinessEntityID, FirstName, LastName, MiddleName, ModifiedDate FROM Person.Person ORDER BY FirstName ASC; -- Сортировка по возрастанию. ASC - по умолчанию. |
Производим выборку данных из столбцов BusinessEntityID, FirstName, LastName, MiddleName и EmailAddress, где данные упорядочены в порядке убывания, по столбцу FirstName, таблицы Person.Contact
|
1 2 3 |
SELECT BusinessEntityID, FirstName, LastName, MiddleName, ModifiedDate FROM Person.Person ORDER BY FirstName DESC; -- Сортировка по убыванию. |
Агрегирование данных. Конструкция GROUP BY
Производим выборку данных из столбцов SalesOrderID и OrderQty, таблицы Sales.SalesOrderDetail, где значения ячеек столбца SalesOrderID равняются одному из значений ряда (43666, 43660, 43664)
|
1 2 3 |
SELECT SalesOrderID, OrderQty FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43666, 43660, 43664); |
Производим выборку данных из столбца SalesOrderID и возвращаемого значения агрегированной функции SUM по столбцу OrderQty, таблицы Sales.SalesOrderDetail, где значения ячеек столбца SalesOrderID равняется одному из значений ряда (43666, 43660, 43664), при этом производится группировка по значениям столбца SalesOrderID. Агрегирование-процесс объединения элементов.
|
1 2 3 4 |
SELECT SalesOrderID, SUM(OrderQty) -- Агрегированная функция SUM() FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43666, 43660, 43664) GROUP BY SalesOrderID; |
|
1 2 3 4 |
SELECT SalesOrderID AS ID, SUM(OrderQty) AS Total -- После ключевого слова AS задаем псевдоним(alias) для результата функции SUM(). FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43666, 43660, 43664) GROUP BY SalesOrderID; -- Агрегирование данных. |
Производим выборку возвращаемого значения агрегированной функции COUNT таблицы HumanResources.Employee.
|
1 2 |
SELECT COUNT(*) AS Emp FROM HumanResources.Employee; -- Агрегированная функция COUNT() выводит количество строк в таблице |
Производим выборку данных из столбца ProductID и возвращаемого значения агрегированной функции COUNT, таблицы Sales.SalesOrderDetail, при этом производится группировка по значениям столбца ProductID.
|
1 2 3 |
SELECT ProductID AS Product, COUNT(*) AS [Count] FROM Sales.SalesOrderDetail GROUP BY ProductID; |
Производим выборку данных из столбца ProductID и возвращаемого значения агрегированной функции COUNT, таблицы Sales.SalesOrderDetail, при этом производится группировка по значениям столбца ProductID, где возвращаемое значение агрегированной функции COUNT больше 3300-ти.
|
1 2 3 4 5 |
SELECT ProductID AS Product, COUNT(*) AS [Count] FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING COUNT(*) > 3300; -- HAVING - должен использоваться совместно с GROUP BY (HAVING аналогичен WHERE). -- HAVING - условие применяемое только к группам. |
Важно! HAVING — должен использоваться совместно с GROUP BY
WHERE работает до группировки, а HAVING работает вместе с группировкой.
|
1 2 3 4 5 |
SELECT SalesOrderID, COUNT(*) AS TOTAL FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43666, 43660, 43664) GROUP BY SalesOrderID HAVING COUNT(*) > 5; |
Оператор UPDATE (изменение(обновление) данных в таблице)
|
1 2 3 |
UPDATE MyFriends SET PhoneNumber = '098*******' -- изменить номер телефона того, WHERE FirstName = 'Andrey' -- чье имя 'Andrey' |
Изменяем значение сразу в нескольких столбцах
|
1 2 3 4 |
UPDATE MyFriends SET FirstName = 'TestName', PhoneNumber = '000*******' WHERE FirstName = 'Andrey' |
|
1 2 |
UPDATE MyFriends SET PhoneNumber = '060*******' -- Если не задана консрукция WHERE, то изменяется весь столбец на указанное значение |
Оператор DELETE (удаление данных из таблицы)
Удалить всех пользователей имя которых TestName
|
1 2 3 |
DELETE MyFriends WHERE FirstName = 'TestName'; SELECT * FROM MyFriends; |
Удаление всех данных из таблицы с помощью DELETE
|
1 |
DELETE MyFriends; |
Для удаления всех данных из таблицы лучше использовать — TRUNCATE TABLE, так как TRUNCATE удаляет информацию из базы быстрее чем стандартный DELETE.
|
1 |
TRUNCATE TABLE MyFriends; |
В случаи если в ваше таблице при создании ее, была допущена ошибка, в имени или в типе значений, и при изменений выскакивает ошибка сохранение изменений запрещено
То можно выполнить запрос alter, в котором мы берем таблицу Product в которой изменяем столбец Cost вводим новый тип значения и последним параметром не обязательное заполнение.
|
1 |
alter table Product alter column Cost char(10) NOT NULL |