На самом деле мы сегодня будем рассматривать язык манипулирования данными. Как мы говорили на прошлом уроке: одно из основных функций любой СУБД является поддержка языков баз данных, это язык определения данных и язык определения данных, с помощью языка определения данных можно описать структуру данных а с помощью языка манипулирования данными можно производить различные операции над данными, такие как вставка данных, удаление данных, изменение и так далее. И на текущий момент самым популярным языком описания и определения данных в реляционных базах данных является язык SQL. Но на самом деле мы работаем с My SQL 2012, потому мы будем работать с процедурным расширением стандарта SQL, который называется T-SQL. Давайте рассмотрим основные операции, которые можно производить с помощью языка манипулирования данными. Это такие операции как INSERT, SELECT, UPDATE, DELETE. INSERT позволяет вставлять данные в таблицу, SELECT позволяет выводить данные из таблицы, UPDATE – менять данные и DELETE – удалять данные из таблицы. На прошлом уроке мы создали табличку Shopdb и попытались её заполнить с помощью визуальных средств Management Studio. Сегодня на первом примере мы попробуем сделать тоже самое только используя INSERT языка DML и давайте это сделаем. Давайте откроем Samples и откроем файл Insert.sql. Перед тем как вставлять данные в таблицу MyFriends, которую мы создали на прошлом уроке давайте подключимся к БД Shopdb. На 7-й строке у нас идет инструкция CREATE TABLES , которая позволяет создать табличку. Но эта табличка у нас уже должна быть. Если я зайду в Object Explorer в каталог Shopdb/ Tables и увижу что такая табличка есть, поэтому еще раз заново я её создавать не буду. Давайте выполним 20-ю строку и убедимся что действительно это табличка есть. Это делаем с помощью процедуры sp_helps, которая позволяет получить сведенья о любой объекте в БД. Мы получаем сведенья о таблице MyFriends. Такая табличка у нас есть. Теперь давайте перейдем непосредственно к оператору вставки INSERT с помощью которого мы можем вставлять данные в таблицу. Синтаксис такого оператора достаточно прост. Нам нужно указать ключевое слово INSERT и имя таблицы в которую мы хотим вставить, дальше можно указывать порядок записи данных и все что осталось сделать дальше это после ключевого слова VALUES указать данные строки, которую мы вставляем в таблицу. Давайте разберем это построчно. На 28-й строке мы указываем что вставляем данные в таблицу MyFriends, на 29-й строке мы указываем порядок записи данных, то есть это порядок заполнения полей строк нашей таблицы. Я указываю что у меня сперва будет заполняться поле FirstName, далее LastName, далее BirthDay и поле NumberTelephone. После ключевого слова VALUES я указываю определение этой строки, то есть строки, которую я вставляю в таблицу. В первом поле будет значение Max, во втором-Petrov, в третьем 02/03/1988 и в четвертом 093******* (номер телефона). Давайте выполним с 28-й по 32-ю строки. М-да, все таки придется нам удалить эту табличу, давайте пропишем удаление: DROP TABLE MyFriends , создадим её заново и перейдем обратно к нашим примерам. Давайте еще раз выполнимся, с 30 по 34-ю строки и мы видим что у нас была обработана строка, то есть одна строка была добавлена в табличку. Давайте попробем вставить еще одну строку в табличку MyFriends, но только теперь поменять порядок записи данных. Ну практически тот же пример. Только сначала мы будем заполнять поле LastName и потом FirstName. И мы вставляем данные: Martinov, Kostya, дату рождения и номер телефона. Выполняем с 38-й по 42-ю строки. И мы вставили еще одну строку в табличку MyFriends. К тому же при использовании оператора Insert при вставке можно не указывать порядок записи данных. Тогда порядок будет использоваться по умолчанию, то есть тот порядок в котором поля располагаются в таблице. Поле FriendsId мы заполнять не будем, оно у нас ключевое и обозначено словом IDENTITY а это значит что сервер сам будет заполнять это поле. Мы не указываем порядок данных, потому записываем в поле FirstName (оно у нас первое) значение Vitya, в поле в поле LastName - Sokolov в следующее - 001/03/1986 и в последнее поле номер телефона - 095********.

Давайте выполнимся и убедимся что действительно строка у нас вставлена, и действительно, мы это видим. На 52-й строке мы еще раз будем вставлять еще одну строку, при этом мы не будем указывать порядок записи данных и в этой строке у нас будет ошибка очередности записи. Потому что мы указываем сначала фамилию нашего сотрудника или же нашего друга, а потом указываем имя. Но если мы посмотрим порядок следования полей в таблице, то мы увидим что сначала идет имя а потом фамилия. Потому будьте аккуратны при заполнении таблицы без указания последовательности и помните что за чем у вас идет в таблице. Ну понятное дело ошибки никакой не будет потому что сервер не может определить где мы записываем имя а где фамилию, ему важно что мы просто вставили строку. Теперь мы вставили несколько строк в таблицу и давайте выберем данные из таблицы и убедимся что данные строки были вставлены.Для этого нужно воспользоваться оператором SELECT, правда в следующем примере мы посмотрим как ним расширено пользоваться но пока что мы просто выберем все данные из таблицы. Пользоваться этим оператором очень легко. После ключевого слова SELECT мы хотим посмотреть какие данные есть в таблице. Мы хотим видеть данные из всех полей. Для этого используем символ «*», SELECT ALL, это значит выбрать все из всех полей. Давайте выполним 58-ю строку и мы видим все данные, которые есть в табличке MyFriends. Во всех полях есть все данные которые мы указывали. Последняя вставка имела ошибку ввода, мы это видим. Теперь давайте перейдем к следующему примеру на 61-й строке и попробуем вставить нового друга, который не имеет имени. Я указываю оператор INSERT, указываю VALUE и вставляю следующую строку : (NULL, Imilianov, 01/01/1986, 095*******) Выполняем с 61-й по 65-ю строку. И у нас выбивает ошибку, то что поле FirstName таблицы MyFriends не может иметь значение NULL. Когда мы создаем нашу таблицу мы должны указывать ряд основных параметром, это тип данных, имя поля и допустимость пустого значения. Когда мы создавали поле FirstName мы указали что это поле не может иметь пустые значения поэтому если мы захотим вставить такую пустую строку в данное поле то у нас ничего не получиться. Если мы будем вставлять друга без фамилии то у нас получиться потому что при создании таблицы мы указали что поле LastName может принимать пустое значения. Выполняем с 68-й по 72-ю строки и видим что еще одна строка была добавлена в таблицу MyFriends. Так же с помощью этого оператора мы можем вставлять сразу несколько строк в нашу таблицу MyFriends, указываем порядок заполнения данными и дальше после ключевого слова слов VALUES в круглих скобках через запятую мы определяем записи, которые должны будут быть вставлены в нашу таблицу, это 4 строки, это друзья с именами: Max Petrov, Alex vare, Sergei Smolnikov и Petr Vitiushkin. Давайте выполним с 75-й по 82-ю строки. Теперь у нас еще 4 строки добавлены в строку MyFriends. И теперь мы умеем с помощью оператора INSERT вставлять несколько операторов в таблицу. Мы так же можем не указывать порядок записи данных, мы можем пропустить строку кода на 86-й строке. Что мы и сделали. Выбираем 85-й по 92-ю строку. Можем еще раз выбрать данные и увидеть что все у нас было вставлено.

Переходим к следующему примеру Select.sql. Сейчас мы попробуем разобрать с вами оператор Select, который помогает выбирать и запрашивать данные из таблиц, которые находятся у вас в БД. Сперва давайте подключимся к БД Shopdb. Мы подключились, как вы видите. И дальше давайте пользоваться оператором SELECT. Сперва давайте произведем выборку всех данных из таблицы MyFriends. На восьмой строке мы указываем оператор SELECT, ставим «*», что говорит о том что выбираются все файлы и после ключового слова мы указываем имя таблицы MyFriend. Выполняем 8-ю строку, получаем все данные, которые есть в таблице MyFriend. На 11-й строке производим выборку данных из столбца FirstName из таблицы MyFriend. Теперь у нас в окошке вывода будет всего одно поле, которое будет заполнено какими то данными. То есть из таблицы мы хотим показать только одно поле, остальные нам не важны. Просто хотим посмотреть друзья с какими именами есть у нас в таблице. На 14-й строке мы будем выбирать данные только из столбца LastName таблицы MyFriend. Запустимся и видим только фамилии наших друзей. На 16-й строке хотим посмотреть только даты рождения наших друзей. На 19-й строке хотим посмотреть имена и фамилии наших друзей, то есть указываем что хотим выбрать данные из полей : FirstName и LastName таблицы MyFriend. Давайте выполнимся и в окошке вывода получим два поля: FirstName и LastName. На 22-й строке мы хотим выбрать данные из всех столбцов, кроме столбца NumberTelephone. То есть нас интересует имена, фамилии и даты рождения наших друзей. Выполняем 22-ю строку и видим что у нас действительно есть то что хотели вывести. Теперь давайте перейдем к следующему примеру. И в следующих примерах мы будем использовать учебную БД AdvebtureWork2012. Эту БД вы можете бесплатно скачать в интернете или же взять с папки с вашими примерами. У меня уже подключена БД AdvebtureWork2012 и я ею могу пользоваться. Каким образом можно подключить БД к серверу. Во первых нужно подключить студию с правами администратора. После этого вы можете нажать на каталоги вашего сервера и нажать на меню Attach, то есть присоединить к серверу БД. У вас появляется окошко, в котором есть кнопка Add , то есть добавить в БД новый файл, нажимаем Add. И помним что БД состоит минимум из двух файлов. Первый файл основной, то есть тот, в котором хранятся таблички, это файл с расширением *.mdf этот файл содержит журнал транзакций. Что бы присоединить БД вам нужно найти файлик с расширением *.mdf У нас этот файл называется AdventureWorks2012_Data.mdf Вы находите ваш файлик, выбираете его и нажимаете «ОК» и в окне Databases to attach у вас появляется ваша БД и в окне Details появляються дополнительные данные о БД. Вижу что действительно есть файлик с расширением mdf и еще один с расширением .ldf. Все что мне осталось сделать это нажать «Ок».

У нас появилась ошибка потому что во первых такая БД уже есть а во вторых Managemetn Studio не имеет дополнительных привилегий. Ну я думаю вы сами сможете это сделать. Давайте подключимся к БД AdventureWorks2012 и попробуем выбирать данные из таблиц данной БД. На 37-й строке нашего кода мы будем выбирать данные из полей CardType, CardNumber, ExpMonth, ExpYear из таблицы CreditCard. Эта таблица находится по схеме Sales. Вот тут мы видим новое для нас определение. Что же такое схема базы данных. Вот эти схемы это каталог пространства имен, которые мы можем видеть в C#. Эти схемы используются для упрощения отношения между пользователями нашего сервера и объектами, которые находятся в БД нашего сервера. Вот допустим если мы посмотрим в БД AdventureWorks2012 и мы видим что в этой БД довольно таки много таблиц и некоторые из этих таблиц касаются продаж, продукции, клиетов сотрудников и так далее. И если бы у нас не было схем то разобраться в этих всех данных было бы очень сложно. Вот так мы создали логические контейнеры, в которые поместили данные. Вот например схема Human Resources, в этой папке будут храниться все данные которые касаются наших сотрудников. Мы видим что здесь есть такие таблицы как Departament, PayHistory и JobCabdidate. Что касается наших клиентов это другие таблицы в другой схеме, продукция это тоже другая схема ну и так далее. К тому же когда у нас есть схема мы можем разделить все данные нашей БД на некие логические части и каждому нашему сотруднику давать доступ только к одной из этих частей. Ну вот допустим менеджер по персоналу. Конечно же он не должен видеть данные, которые касаются продукции, продаж или же там клиентов. То есть он должен видеть только таблицы, которые касаются непосредственно HumanResources наших трудовых ресурсов и по этому мы представляем доступ нашему менеджеру по персоналу только к объектам, которые находятся по схеме HumanResources . Ну я думаю вы поняли для чего нужны эти схемы. Мы будем получать доступ к таблице CreditCard, которая находится по схеме Sales и будем получать данные из полей этой таблицы: Cardtype, CardNumber, ExpMonth и ExpYear. Давай выполним 37-ю строку и увидим те данные, которые есть у нас в таблице. Данных довольно таки много. Хорошо, простейшие запросы мы уже можем себе создавать но неплохо было бы иметь какой то фильтр, инструмент который бы мог фильтровать наши данные, то есть те которые мы хотим увидеть. Ну допустим нам потребовалось бы вывести только те карточки, тип которых Vista. Вот для этого можно использовать фильтр.

Фильтр создается с помощью конструкции WHERE нашого запроса. Давайте перейдем к следующему примеру и выберем данные из таблицы CreditCard у которых ID=10. То есть мы хотим посмотреть карточку с ID=10. На 44-й строке мы создадим запрос, который будет выбирать данные из всех полей строк таблицы CreditCard к тому же мы указываем фильтр, то есть мы хотим посмотреть только те строки, точнее ту строку у которой ID=10. Давайте выполним 44-ю и 45-ю строку и вот мы видим, что у нас есть действительно одна кредитная карточка с ID=10, тип карты SuperiorCard. Так же вот когда мы создаем наши фильтры, вообще в принципе фильтром может быть любое выражение, результат которого может иметь только два состояния правда или же ложь, то есть True или False. На 48-й строке мы создали еще один запрос с фильтром который теперь будет выбирать данные из таблицы CreditCard, то есть те кредитные карточки у которых айди меньше десяти. Так же выражение, которое может вернуть два значения True или False. Выполняем 48-ю и 49-ю строки и мы видим что у нас выводятся только те кредитные карты, у которых айди меньше десяти. 53-я строка – мы создаем запрос, который в своем фильтре будет использовать оператор BETWEEN, этот оператор возвращает истину если операнд находится внутри диапазона. В данном примере мы будем выбирать все данные из таблицы CreditCard. Мы будем выбирать кредитные карточки у которых Id будет находиться в диапазоне между значениями 1 и 3. Давайте выполним 53-ю и 54-ю строку и мы получим что у нас есть три такие карточки. Так же мы можем пользоваться логическим оператором «ИЛИ/OR» или же лог. Оператором «И/AND». Логический оператор ИЛИ будет возвращать TRUE если хотя бы один из операндов, который так же является одним из выражений возвращающий значение TRUE или FALSE. Если хотя бы один из операторов значения TRUE значит и весь оператор вернем значение TRUE. 58-я строка – мы указываем что мы хотим хотим увидеть только значение полей CardType и ExpYear таблицы CreditCard. На 59-й строке мы указываем фильтр, где ExpYear = 2005 ИЛИ ExpYear = 2006. Выполняем 58-ю и 59-ю строки и получаем результат. То есть теперь мы видим только те карточки у которых ExpYear 2005 или 2006, других вариантов у нас здесь нет. На 63-й строке мы еще раз используем операцию проверки диапазона для таблицы CreditCard и поля ExpYear, только теперь мы указываем между 2005 и 2007. Давайте выполнил 63-ю и 64-ю строку и теперь мы видим все карточки у которых ExpYear = 2005,2006 или 2007. Следующее что мы сделаем – мы попробуем в фильтре использовать логическую оперцию «И». Эта операция будет возвращать TRUE только в том случае если оба операнда будут иметь значения TRUE. 68-я строка мы выбираем данные из таблицы CreditCard и указываем фильтры где ExpYear = 2005 и CardType=Vista. Давайте выполним 68-ю и 69-ю строки и мы видим только те карточки у которых тип Vista и ExpYear=2005, давайте к стати уюерем их тут что бы они не сливались, что бы данные были не одинаковы. Выполняем 68-ю и 69-ю строки и получаем различные кредитные карточки у которых различные Id но у всех них тип Vista и ExpYear=2005. Дальше мы рассмотрим логическую операцию «НЕ». Этот оператор позволяет изменить значение других булевых операторов на противоположные. Тут мы используем операцию сравнение на равенство, в фильтре мы указываем ExpYear=2006, оператор NOT в результат работы этого логического оператора, сравнение на равенство, то есть на самом деле мы получим все кредитные карточки у которых ExpYear НЕ 2006. Давайте выполним 73-ю и 74-ю строки и вот мы получим набор таких карточек, среди которых не будут встречаться такие карточки у которых ExpYear=2006. Теперь давайте рассмотрим оператор LIKE.

Оператор LIKE позволяет нам проверить значение строкового поля на соответствие с неким шаблоном. На 78-й строке мы создали запрос, который будет выбирать данные из поля CardType и ExpYear таблицы CreditCard. На 79-й строке мы указали фильтр, то есть нам нужно показать только те карточки у которых поле CardType соответствует шаблону ColonialVoice. Давайте выполним 78-ю и 79-ю строку и мы видим только те кредитные карточки у которых CardType ColonialVoice. Пока что мы не видим разницу работы этого оператора с оператором проверки на равенство. 81-я строка, создаем в принципе такой же запрос, который вместо LIKE использует оператор равенства «=» и получаем абсолютно те же результаты. Но оператор LIKE нам полезен тогда когда мы с ним используем символы макроподстановки. Символы макроподстановки это специальные символы, которые заменяют собой отдельные комбинации символов в исходной символьной строке. Существует примерное 6 символов такой макроподстановки. Мы будем разбирать два основных из них. Давайте рассмотрим. На 88-й строке мы можем увидеть первый символ макроподстановки – это символ «%». Этот символ макроподстановки означает произвольное количество символов, которые можно подставить вместо вот этого символа в исходной строке. Давайте разберем пример и нам тут же все станет понятно. На 87-й строке мы создаем запрос, который будет выбирать данные из таблицы CreditCard а именно из полей CardType и ExpYear. На 87-й стоке в фильтре мы будем использовать оператор соответствия шаблонов, то есть оператор LIKE, при этом в шаблоне мы будем использовать символ макроподстановки %. Это значит что из всех кредитных карточек, которые есть в CardType мы будем выбирать те, у которых CardType будет соответствовать шаблону, то есть CardType будет Dis.. и дальше какое то любое количество других символов. Давайте выполним 87-ю и 88-ю троку. ТО есть только один тип кредитных карточек, который у нас есть в таблице CreditCard соответствует нашему шаблону. Это тип кредитных карточек Distinhuish. На 91-й строке мы хотим построить тот же запрос, только теперь использовать символ макроподстановки % с оператором проверки на равенство. Когда мы выполним 91-ю и 92-ю строку то мы ничего не получим, потому что оператор проверки на равенство не знает никаких символов проверки на равенство и он будет искать в таблице CreditCard у которой будет тип Dis% . Такого типа кредитных карточек в таблице просто напросто нет. Так же существует еще один символ макроподстановки, который можно использовать с оператором LIKE.

Это символ нижнего подчеркивания. Этот символ определяет любой один символ. На 96-й строке мы строим запрос, который будет выбирать данные. И указываем фильтр, то есть нужно выбрать только те карточки, у которых тип, который соответствует шаблону Vis__ и дальше два любых символа. Давайте выполним 96-ю и 97-ю строки и видим что у нас есть кредитная карточка, верней тип кредитной карточки, который соответствует шаблону, это тип Vista. Видим Vis__ и ta это два произвольных символа, которые мы указали с помощью символов макроподстановки нижнего подчеркивания. На 101-й строке мы используем еще один запрос, в принципе он такой же как и предыдущий, только теперь мы ищем карточки у которых тип соответствует шаблону Vis_ (Vis и один символ подчеркивания). Таких карточек мы не обнаружим, таких карточек просто нет. Если мы символ нижнего подчеркивания поменяем на символ %, то мы имеет карточку, которая имеет тип Vista, потому что символ макроподстановки % заменяет собой любое количество произвольных символов. 106-я строка, построили еще один запрос. Теперь мы будем использовать следующий запрос Vis_a. У нас есть такой тип кредитных карточек – это тип Vista. 111-я строка мы создаем еще один запрос, который будет использовать оператор LIKE и символ макроподстановки %, то есть мы будем искать карточки у которых тип соответствует шаблону «произвольное количество любих символов/ist/ произвольное количество любих символов». Выполняемся и видим что у нас есть два типа таких карточек, которые соответствуют шаблону, это Vista и Distinguish. И у того и у другого типа кредитных карточек встречается строка «ist» которую мы и искали. Дальше мы рассмотрим оператор IN , который можно использовать в запросах. Этот оператор будет возвращать истину если операнд находится в списке. Список значений мы указываем в круглых скобках после оператора IN. Давайте рассмотрим пример и нам тут же все станет ясно. На 116-й строке мы создаем запрос, который будет выбирать данные из всех полей таблицы CreditCard. Мы указываем фильтр. На самом деле мы хотим выбрать не все строки а только те у которых ExpMonth входит в список, то есть у которых ExpMonth=1;5;11. Выполняем 116-ю строку и получаем результат. То есть теперь мы видим те строки в которых ExpMonth либо 11, либо 1, либо 5, других значений тут нет. На 118-й строке мы строим практически тот же результат, тот же запрос функционально но для этого используем другой оператор в фильтре.

Мы буде использовать логический оператор «ИЛИ/OR». Он будет работать так же как оператор Int в предыдущем запросе. К тому же мы можем увидеть с предыдущего примера можем увидеть что язык T-SQL не чувствителен к регистру. То есь мы можем писать имена запросов с маленькой, с большой буквы или даже чередовать. Регистр не важен. Переходим к третьему примеру. В третьем примере 003_ORDER_BY.sql мы с вами научимся пользоваться инструкцией ORDER BY, которая позволяет отсортировать результирующий набор данных по одному или нескольким полям. Сперва давайте подключимся к БД AdventureWorks2012 и давайте посмотрим на запрос в 12-й строке. Мы выбираем данные из полей : BusinessEntityID, FirstName, LastName, MiddleName, ModifiedDate из таблицы Person по схеме Person и так же мы будем упорядочивать результирующий набор данных по полю FirstName. Давайте выполнимся и мы увидим, что наш набор данных был отсортирован именно по имени. В поле FirstName значения начинаются с первой буквы латинского алфавита и последние строки заканчиваются значениями которые начинаются с последней буквы латинского алфавита. И если мы выполним тот же запрос без сортировки, то результат будет на лицо. Так же можно отсортировать не только по имени, не только по строковым значениям а так же можно отсортировать по полям которые представляют значения даты и времени. Давайте выполним запрос с 18-й по 20-ю строку и теперь в ORDER BY используется ModiiedDate, которая представляет дату и время, но вернее только дату. И когда мы выполним этот запрос мы увидим что первая строка имеет 2000-й год а последние строки имеют 2009-й год. Это говорит о том что данные были отсортированы по дате. Так же можно сортировать по числам. По целым или вещественным. Так же есть возможность сортировать по нескольким полям. На 24-й строке мы создали наш запрос, такой же запрос на 26-й строке. Мы используем сортировку. Сначала по полю FirstName, потом по полю LastName. Давайте выполним запрос с 24-й по 26-ю строку и вот что мы увидим. У нас сначала действительно было отсортировано по имени а потом мы отсортировали еще раз по фамилии. Что это значит? Это значит что мы определили группы элементов, которые имеют одинаковые значения в после FirstName и в этих группах мы отсортировали еще и по LastName. Вот у нас есть несколько наших клиентов. Например это клиент с именем Aaron. И первый Aaron , которого мы видим начинается с первой буквы латинского алфавита. Если мы посмотрим на последнего нашего клиента с именем Aaron то увидим что его фамилия начинается с последней буквы латинского алфавита. Так же есть возможность сортировать по возрастанию или же убыванию.

В следующем примере мы будем выбирать данные из полей BusinessEntityID, FirstName, LastName, MiddleName, ModifiedDate. При этом мы будем сортировать результирующий набор данных по полю FirstName, ASC – значит по возрастанию. Когда мы выполняемся то в принципе увидим то же что видели в предыдущих примерах. На самом деле ASC можно не использовать так как если мы используем сортировка то по умолчанию сортировка будет по возрастанию. Так же можно сортировать по убыванию. Для этого нужно указать ключевое слово DESC. Давайте выполним следующий запрос. Мы видим что результирующий набор данных должен быть отсортирован по FirstName и при этом по убыванию. По этому в окошке вывода мы видим табличку в которой у первой строки в поле FirstName имеется строковое значение, которое начинается с последней буквы латинского алфавита. Последняя строка, которая есть в нашей табличке в окошке вывода – это строка имеет в поле FirstName имеет строковое значение, которое начинается с первой буквы латинского алфавита. Довольно часто требуется организовать отчеты в алфавитном или числовом порядке но и видеть какую то итоговую информацию. Итоговая информация может иметь какие то средние значения для набора данных или же сума всех значений в столбце. Или же это может быть количество объектов определенного типа ну и так далее и так далее. И для того что б строить такие запросы и видеть итоговую информацию мы будем использовать предложение GROUP BY. Это предложение позволяет сгруппировать данные, которые находятся в таблице по какому-то критерию. Если это предложение используется в связке с функцией агрегирования то можно выводить итоги в конце отчета. Давайте теперь разберемся что такое функция агрегирования. Функции агрегирование позволяют вычислять сводные значения такие как среднее значение или сумма всех значений столбца. Чтобы лучше понять принципы работы этого предложения давайте рассмотрим пример. Переходим к файлу GROUP BY, на десятой строке мы создаем запрос, который будет выбирать данные из полей SalesOrderID,OrderQty таблицы SalesOrderDetails. При этом мы указываем фильтр, нужно выбрать те строки у которых в SalesOrderID содержится значение или 43666, или 43660 или 43664. Давайте выполним этот запрос и посмотрим что у нас выводится. Ну, видимо у нас выводится ID продажи и выводится поле OrderQty (количество единиц товара, которые были проданы). И вот в этом примере нам понадобится вывести какую то итоговую информацию, то есть сколько всего было куплено единиц продукции например в продаже 43660. И как мы говорили для этого им потребуется выражение GROUP BY, которое позволяет группировать данные результирующего набора. Значит, и в следующем примере, который находиться на 21-й строке мы попробуем посчитать сумму единиц товара в каждой продаже, которая у нас есть, в продаже 43666, 43660 и в продаже 43664. И как мы говорили, для того чтобы получить допустим сумму всех сгруппированных данных нам потребуется пользоваться агрегированными функциями. Одна из этих агрегированных функций это SUM, она позволяет считать сумму значений которые находятся в группе. То есть сперва нам придется сгруппировать результат нашей выборки. Мы создаем выборку SELECT, которая будет выбирать данные из столбца SalesOrderID, а так же будет считать сумму по полю OrderQty каждой из групп. В каждой из групп у нас будут хранится элементы которые имеют одинаковые значения в поле SalesOrderID. То есть у нас всего есть три группы потому что мы будем фильтровать нашу выборку по SalesOrderID у которых только 43666,43660,43664.

Если бы мы не группировали то увидели бы результат который уже был у нас при выполнении запроса на 10-й строке. Но мы будем группировать наши данные по значению SalesOrderID, а OrderQty мы будем суммировать. Давайте выполним с 21-й по 24-ю строку и мы увидим что все данные были сгруппированы на 3 группы 43666,43660,43664. Второе поле это у нас сумма по OrderQty каждой группы. Видим что сумма по OrderQty для продажи 43660 это 2, для 43664- это 14 а для 43666- это 7. Но мы видим, что результат работы агрегированной функции SUM, которую видно у нас в окошке вывода, вот это поле у нас не имеет какого то вывода. При работе агрегированных функций таких как SUM поле, которое получается в результате работы этой функции не имеет имени. Давайте перейдем к следующему примеру и попробуем задать этому полю какое то имя. Для этого нам придется пользоваться AS-ами. Существует возможность задавать таблицам или столбцам таблицы другие имена используя для этого псевдоним. AS как раз является псевдонимом. Это может быть полезным если у нас очень сложные или длинные имена таблиц или столбцов. Псевдоним может быть каким угодно, но обычно это короткие имена. Псевдоним задается после ключевого слова AS, которое может быть после (ну в данном случае AS ставится для полей и таблиц). То есть мы выбираем данные из таблицы SalesOrderDetail, указываем фильтр, группируем и для столбцов в результирующем наборе данных мы указываем AS. То есть у нас первое поле будет называться не SalesOpderID а просто ID. Второе поле, которое бы у нас называлось no column name у нас оно будет называться Total. Выполняемся и видим что первое поле у нас с именем ID, а второе с именем Total в результирующем наборе данных. Так же мы рассмотрим и агрегированную функцию Count, которая позволяет выводить количество строк в таблице. Вот к примеру, давайте определим сколько сотрудников у нас работает у нас в компании. У нас есть таблица Employee в которой находятся все данные о сотрудниках. Что бы посчитать число сотрудников, которые у нас работают нам нужно просто создать запрос, который посчитает количество строк в таблице. Вот как раз здесь мы будем пользоваться агрегированной функцией COUNT. Выполняемся, у нас идет подсчет количества строк и в итоге мы увидим что у нас есть 290 сотрудников, которые работают у нас в компании. Так же потому как функция COUNT является агрегированной функцией то её можно использовать с выражением GROUP BY, то есть вместе с выражением группировки. Допустим, функцию COUNT можно использовать с GROUP BY для того что бы посчитать количество элементов в группах. Вот в следующих примерах мы выбираем данные из таблицы Sales.Order.Detail, выбираем значение ProductID и второе поле это будет результат работы с функцией COUNT для всех групп, которые у нас будут сформированы. И группировать мы будем именно по ProductID. Если мы выполним наш запрос то увидим сколько раз встречается у нас допустим значение 707 для ProductID встречается 3083 раза. Для 708 встречается 3007 раз. Вот так вот можем посмотреть. Возможно нам так же потребуется создать фильтр для тех значений которые мы получаем от функции COUNT, это те значения, которые у нас есь во втором столбце. Использовать для этого выражение WHERE не получится потому мы не работаем с реальными столбцами таблиц, а работаем с данными, которые у нас были рассчитаны с помощью той или иной функции, и вот для таких данных, агрегированных данных, которые у нас есть в результирующем наборе мы должны использовать конструкцию HAVING. Вот давайте посмотрим конструкцию в примере.

В следующем примере мы хотим взять ту же выборку, которая у нас есть но отфильтровать те строки, которые есть в этой выборке по значению поля COUNT. Для этого мы использовали следующее предложение: HAVING COUNT > 3300. То есть из всех строк, которые мы сей час видим в нашем окошке вывода мы увидим только те, у которых COUNT имеет значение больше 3300. Вот к примеру 711 мы убедимся, 712, давайте проверим какие, вот у нас был 712, так же есть 870 и 873. Давайте перейдем дальше. HAVING у нас должен использоваться только с GROUP BY. То есть если мы хотим фильтровать по значению полей, то используем WHERE, если мы хотим фильтровать по агрегированным данным, то тогда нужно использовать HAVING. Попытка выполнения с 54-й по 57-ю строку выбьет ошибку: неправильное использование конструкции HAVING. В запросе на 60-й строке мы выберем данные из таблицы SalesOrderID. Мы выбрали только те строки, которые в поле SalesOrderID имеют значение 43666, 43660, 43664. Мы их сгруппируем и найдем какое количество раз встречаются строки с тем или инным значением в поле SalesOrderID. При этом мы будем указывать фильтр, который у нас отфильтрует группы, которые в сумме имеют значения меньше 5-ти. Давайте выполним наш запрос и увидим только два значения 8 и 6, давайте так же тут добавим и SalesOrderID. И теперь мы можем видеться что в нашей таблице SalesOrderDetail встречается 8 строк у которых в поле salesid имеются значения 43664 и 43666 и меньше 5 которые имеют значение в этом поле 43660. Если мы будем выполнятся без инструкции HAVING то мы увидим три значения, если будем использовать с HAVING, то первая строка, которая сейчас у нас есть в результирующем наборе – она будет отфильтрована. Мы уже рассмотрели операторы DML, которые позволяют рассмотреть данные в таблице и выбирать данные из таблиц. Ну мы еще не познакомились с операторами, которые позволяют удалять данные или заменять их. Давайте посмотрим наш последний пример в данном уроке под названием 005_UPDATE_DELETE.sql. И посмотрим на эти операторы. Сперва мы посмотрим на оператор UPDATE, который позволяет изменять данные в таблице. Мы подключимся к базе данных Shopdb и вставим одну строку таблицы MyFriends. Эта строка у нас имеет в поле FirstName значение Max, в поле NumberTelephone значение 050********. Мы не указали значения для полей LastName B-DAY по этому они будут пустыми, точнее заполнены маркерами пустых значений по имени NULL.

На 15-й строке мы используем оператор для того чтобы изменить данные в таблице MyFriends. Мы хотим изменить номер телефона на 098******* у друга по имени Max. Давайте выполним с 15-й по 18-ю строку. Значит у нашего Макса был номер телефона – 050******* а теперь мы изменили на 098*******. Давайте это проверим, выполняем строку, ищем имя Мах и видим что у всех наших Максов номера поменялись на 098********. То есть оператор может найти сразу же все строки которые удовлетворяют запрос и сразу поменять во всех строках поле. На 24-й строке мы еще раз хотим поменять значение в таблице MyFriends, поменять имя на TestName и номер телефона на 000******** у тех друзей, у которых имя Мах. Давайте выполним с 24- по 27-ю строку и у нас было обработано 4 поля, 4 строки так как в нашей таблице есть четыре строки, в которых в поле FirstName есть значение Мах. У нас есть 4 друга с именем Мах. Выполняем 29-ю строку, ищем все данные из нашей таблицы и получаем что у всех друзей имена с Мах сменились на TestName. И у всех них номер телефона 000*******. Если мы не будем указывать конструкцию WHERE UPDATE то тога у всех строк нашей таблице будет меняться значение в заданном поле. На 32й строке меняем таблицу MyFriends и устанавливаем у всех строк номер телефона, который начинается с 060. Выполняемся с 32-й по 34-ю строку и у нас было обработано всего 14 строк. Выполняем 36-ю строку и видим что у всех строк нашей таблице есть теперь поле номер телефона со значением 060*******. И так же нам осталось рассмотреть оператор DELETE, который позволяет удалять данные из таблицы.

Этот оператор очень похож на оператор UPDATE и так же в нем можно использовать тот же фильтр для того что бы указать какие строки нужно удалить. Давайте удалим всех наших друзей, у которых имя TestName. На 44-й строке мы будем удалять данные из таблицы MyFriends. На 45-й строке мы указываем фильтр. Нам нужно удалить только тех другей, у которых FirstName имеет значение TestName. Давайте выполним 44-ю и 45-ю строку. У нас было 4 друга с таким именем и теперь они удалены. Теперь выполняем 46-ю строку и у нас теперь 10 строк, потому что 4 мы удалили. ТО есть теперь у нас нет друзей с именем TestName. Так же можно удалить все данные из некой таблицы. Для этого не нужно использовать какой либо фильтр. 49-я строка – удаляем все данные из таблицы MyFriends.

Выполняем 49-ю строку и у нас должно быть обработано 10 строк. Вот что мы и видим. Выбираем все данные из таблицы MyFriends, но в ней уже нет данных потому что мы их всех удалили. Так же для удаления всех данных из таблицы можно использовать операцию TRUNCATE. TRUNCATE сразу же удаляет все данные из таблицы. Конечно есть большая разница между использованием TRUNCATE и DELETE. TRUNCATE у нас отрабатывает быстрее, чем стандартный DELETE. Но, чтобы полностью разобраться в чем разница вам нужно обратится к документации TRUNCATE и почитать. К тому же эта операция работает с различными системами управления базами данных (СУБД). Я думаю это не будет проблемой – зайти и почитать. Спасибо, на сегодня наш урок закончен.