Структура языка SQL Кафедра прикладной информатики в экономике

Структура языка SQL Кафедра прикладной информатики в экономике и управлении Разработчик: Панова Н. Ф.

Структура языка SQL 1 Операторы языка: 1. 1 Операторы языка определения данных 1. 2 Операторы языка манипулирования данными 2 Оператор Select 2. 1 Оператор Select общий вид 2. 2 Выбор строк 2. 3 Сортировка строк 2. 4 Использование обобщенных функций 2. 5 Группировка 2. 6 Подзапросы 2. 7 Использование ключевых слов (some) и all 2. 8 Выполнение соединений 2. 9 Комбинирование результирующих таблиц

Операторы языка: Операторы языка делятся на 2 группы: - операторы языка определения данных (DDL); - операторы языка манипулирования данными (DML). Операторы определения данных: - Create table – создать таблицу; - Drop table – удалить таблицу; - Alter table - изменить структуру таблицы; - Create view – создать представление; - Drop view – удалить представление (виртуальная таблица, соответствующая результату выполнения запроса); - Create index – добавить индекс; - Drop index – удалить индекс. Операторы манипулирование данными: - Insert – вставить строку в таблицу; - Delete – удалить строку из таблицы; - Update - изменить содержимое таблицы. - Select – выборка данных из одной или нескольких таблиц

Средства администрирования базы данных: - Create database – создать базу данных; - Drop database – удалить базу данных; - Grant – предоставить право доступа к данным; - Revoke – лишить права доступа к данным; Средства поддержки механизма транзакций: - Begin transaction - начать транзакцию; - Commit – завершить транзакцию; - Rollback – откат транзакции; - Save point – сохранить контрольную точку.

Таблица Rashod Название поля содержание Тип N_nakl Номер накладной Числовой Dat Дата отпуска товара Тип даты Kolvo Количество товара Числовой Tovar Наименование товара Символьный Pokup Наименование покупателя Символьный Таблица Rashod содержит следующую информацию: N_nakl Dat Kolvo Tovar pokup 1 01. 03. 2004 90 Сахар Иванов 2 01. 03. 2004 50 Соль Петров 3 11. 04. 2004 20 Печенье Иванов 4 25. 04. 2003 180 Cахар Сидоров 5 7. 06. 2004 45 Масло подсолнечное Антонов 6 7. 06. 2004 60 Мука Борисов 7 7. 06. 2004 75 Сахар Иванов 8 21. 09. 2004 120 Печенье Борисов 9 19. 10. 2004 80 Масло подсолнечное Сидоров 10 19. 10. 2004 160 Мука Петров

Таблица Tovar Название поля содержание Тип Tovar Наименование товара символьный Cena Цена за единицу числовой Ed Единица измерения символьный Таблица Tovar содержит следующую информацию: Tovar cena Ed Сахар 18 Кг Соль 3 Кг Печенье 35 Кг Масло подсолнечное 34 бутылка Мука 25 Кг

Таблица Pokup Название поля содержание Тип Pokup Наименование покупателя символьный Adres Адрес Символьный Таблица Pokup следующую информацию: Pokup Adres Иванов г. Оренбург, ул. Чкалова 32, кв. 8 Петров г. Орск, пр. Победы 21, кв. 7 Pokup Adres Сидоров г. Гай, ул. Ленина, 95 Антонов г. Орск, ул. Советская 64, кв. 12 Борисов г. Оренбург, ул. Попова 7 Ежов Г. Медногорск, ул. Постникова 11, кв. 90

Связи между таблицами представлены ниже: TOVAR RASHO D N_nakl Tovar cena Dat Kolvo Tovar Pokup POKUP Pokup Adres

Оператор Select Общий вид оператора: Select [all | distinct]* | <список столбцов> ► From <список таблиц> ► Where <условие выборки строк или соединения таблиц> ► Group by <список группируемых столбцов> ► Having <условие отбора групп> ► Order by <список столбцов сортировки> ►

Выбор строк Во фразе Where можно задавать следующие условия поиска: - сравнение (< , >= , <= , <>); - диапазон значений; а) Between A and B; б) Not between A and B; - проверка принадлежности значения атрибута некоторому множеству; а) In (<множество>); б) Not in (<множество>); - проверка соответствия или несоответствия шаблону; а) Like <шаблон>; б) Not like <шаблон>; - проверка на неопределенное значение; а) Not null; б) Is null. В качестве символов шаблона можно использовать подчёркивание “_”, что означает одиночный произвольный символ или “%” - любое количество произвольных символов.

Примеры: 1. Создать отчёт по товарам, включая в него товары дороже 30 руб. за единицу. Select * From tovar Where cena > 30 В результате получится таблица следующего вида: Tovar cena ed Печенье 35 кг Масло подсолнечное 34 бутылка

2. Создать отчёт по товарам, включая в него сведенья только о сахаре, соли, муке. Select * From Tovar Where tovar = ’Сахар’ or tovar = ’Соль’or tovar = ’Мука’. Результат запроса приведен ниже. Tovar Cena ed Сахар 18 кг Соль 3 кг Мука 25 кг Можно воспользоваться другим способом: Select * From Tovar Where tovar in ( ‘Сахар’ , ’Соль’ , ’Мука’ )

3. Создать отчет по продажам за 2004 год. Select * From Rashod Where dat between ‘ 1. 01. 2004’ and ’ 31. 12. 2004’ Результирующая таблица будет содержать следующие строки: N_nakl Dat Kolvo Tovar pokup 1 01. 03. 2004 90 Сахар Иванов 2 01. 03. 2004 50 Соль Петров 3 11. 04. 2004 20 Печенье Иванов 5 7. 06. 2004 45 Масло подсолнечное Антонов 6 7. 06. 2004 60 Мука Борисов 7 7. 06. 2004 75 Сахар Иванов 8 21. 09. 2004 120 Печенье Борисов 9 19. 10. 2004 80 Масло подсолнечное Сидоров 10 19. 10. 2004 160 Мука Петров

4. Создать отчет по продажам, включив в него только тех покупате фамилии которых начинаются на букву ‘И’. Select * From Rashod Where pokup like ‘И%’ Результирующая таблица будет содержать следующие строки: N_nakl Dat Kolvo Tovar pokup 1 01. 03. 2004 90 Сахар Иванов 3 11. 04. 2004 20 Печенье Иванов 7 7. 06. 2004 75 Сахар Иванов

Сортировка строк результирующей таблицы Примеры: 1. Создать отчёт по продажам, упорядочив его по полю “покупатель”. Select * From Rashod Order by pokup Результирующая таблица будет включать все строки исходной таблицы в котором покупатели будут расположены в алфавитном порядке. N_nakl Dat Kolvo Tovar pokup 5 7. 06. 2004 45 Масло подсолнечное Антонов 6 7. 06. 2004 60 Мука Борисов 8 21. 09. 2004 120 Печенье Борисов 1 01. 03. 2004 90 Сахар Иванов 3 11. 04. 2004 20 Печенье Иванов 7 7. 06. 2004 75 Сахар Иванов 2 01. 03. 2004 50 Соль Петров 10 19. 10. 2004 160 Мука Петров 4 25. 04. 2005 180 Cахар Сидоров 9 19. 10. 2004 80 Масло подсолнечное Сидоров

2. Результирующую таблицу можно отсортировать по значениям нескол столбцов. Создать отчёт по продажам, упорядочив его по столбцу pokup, а при одинаковых значениях в столбце pokup упорядочить строки по количест купленного им товара. Select * From Rashod Order by pokup, kolvo Результат выполнения запроса представлен ниже. N_nakl Dat Kolvo Tovar pokup 5 7. 06. 2004 45 Масло подсолнечное Антонов 6 7. 06. 2004 60 Мука Борисов 8 21. 09. 2004 120 Печенье Борисов 3 11. 04. 2004 20 Печенье Иванов 7 7. 06. 2004 75 Сахар Иванов 1 01. 03. 2004 90 Сахар Иванов 2 01. 03. 2004 50 Соль Петров 10 19. 10. 2004 160 Мука Петров 9 19. 10. 2004 80 Масло подсолнечное Сидоров 4 25. 04. 2005 180 Cахар Сидоров

Использование обобщающих функций SQL В языке реализованы следующие обобщающие функции: - Min – возвращает минимальное значение в указанном столбце; - Max - возвращает максимальное значение в указанном столбце; - Count – подсчитывает количество значений в указанном столбце; - Sum возвращает сумму значений в указанном столбце; - Avg – возвращает усредненное значение в указанном столбце. Функции Min, Max, Count применимы к столбцам любого типа Функции Sum и Avg могут использоваться только для числовых столбцов. Все функции при вычислении результирующих значений вначале исключают все пустые значения. Функция Count(*) подсчитывает все строки результирующей таблицы, включая дублирующие строки и строки, содержащие пустые значения. Следует помнить, что обобщающие функции можно использовать только во фразе having или сразу после select в списке столбцов. При этом столбец указывается в качестве аргумента функции.

Примеры: 1. Подсчитать сколько наименований товаров покупал Иванов. Select count (distinct tovar) as chislo From Rashod Where pokup = ‘ Иванов’ Результат выполнения запроса содержит одну строку и один столб chislo 2 Ключевое слово ‘distinct’ здесь необходимо для удаления строк-дубликатов перед подсчетом количества наименований товара. 2. Определить количество проданного сахара и общее число покупател Select sum (kolvo) as sum Count (distinct pokup) as chislo From Rrashod Where tovar = ’Сахар’ Результат выполнения запроса: Sum chislo 345 2

3. Определить минимальную, максимальную и среднюю цену товара. Select max (cena) as max_ cena, min (cena) as min_ cena, avg (cena) as cena From Tovar Результат представлен ниже: max_ cena min_ cena 35 3 15

Группировка результатов Пример: Определить количество покупателей для каждого вида товара и сред количество купленного ими товара для получения отчёта необходимо сгруппировать данные по столбцу tovar. Select tovar, count (distinct pokup) as pok, Avg (kolvo) as kol from Rashod Group by tovar В результате получим следующую таблицу: Tovar pok kol Сахар 2 172. 5 Соль 1 50 Печенье 2 70 Масло подсолнечное 2 62. 5 Мука 2 110 Замечание: стандарт языка требует, чтобы все столбцы, перечисленные списке select, присутствовали и во фразе group by, за исключением тех столбцов, которые используются в обобщающих функциях.

Ограничения на выполнение группирования (having) Фраза having предназначена для фильтрации групп, помещаемых в результирующую таблицу. Стандарт требует, чтобы имена столбцов, которые использовались во фразе having, были указаны и в списке фразы group by или применялись в обобщающих функциях. Пример - Выдать даты отпуска товаров, в которых количество единиц отпускаемых товаров >=50, в результирующую таблицу включить те группы, по которым число отпусков >1. Select dat, count (*) from Rashod as kol Where kolvo >=50 Group by dat Having count (*)>1 Выполняется запрос следующим образом: - из таблицы Rashod выбираются те строки, в которых количество отпущенного товара >=50. N_nakl Dat Kolvo Tovar pokup 1 01. 03. 2004 90 Сахар Иванов 2 01. 03. 2004 50 Соль Петров 4 25. 04. 2003 180 Cахар Сидоров 6 7. 06. 2004 60 Мука Борисов 7 7. 06. 2004 75 Сахар Иванов 8 21. 09. 2004 120 Печенье Борисов 9 19. 10. 2004 80 Масло подсолнечное Сидоров 10 19. 10. 2004 160 Мука Петров

- эти строки группируются по датам и для каждой группы подсчитывается количество строк, т. е. отпусков товара, в которых количество единиц отпущенного товара превышает 50. N_nakl Dat Kolvo Tovar pokup 1 01. 03. 2004 90 Сахар Иванов 2 01. 03. 2004 50 Соль Петров N_nakl Dat Kolvo Tovar 180 Cахар pokup 2 отпуска 4 25. 04. 2003 Сидоро в 1 отпуск 6 7. 06. 2004 60 Мука Борисов 7 7. 06. 2004 75 Сахар Иванов 8 21. 09. 2004 120 Печенье Борисов 1 отпуск 9 19. 10. 2004 80 Масло подсолнечное Сидоров 10 19. 10. 2004 160 Мука Петров

- затем в результирующую таблицу включаются только те группы, в которых число отпусков товара более чем 50 единиц превышает 1. Dat Kolvo Tovar Pokup 1 01. 03. 2004 90 Сахар Иванов 2 01. 03. 2004 50 Соль Петров N_nakl 2 отпуска 6 7. 06. 2004 60 Мука Борисов 7 7. 06. 2004 75 Сахар Иванов 2 отпуска 9 19. 10. 2004 80 Масло подсолнечное Сидоров 10 19. 10. 2004 160 Мука Петров 2 отпуска Окончательный результирующий набор имеет вид: Dat Kol 01. 03. 2004 2 7. 06. 2004 2 19. 10. 2004 2

Подзапросы В тело оператора select может быть внедрён другой оператор select. Этот внутренний запрос называется вложенным запросом или подзапросом. Он может быть помещён в предложение where или having. Существует следующие виды подзапросов: - скалярный подзапрос. Такой подзапрос возвращает единственное значение. - строковый подзапрос. Возвращает значение одного или нескольких столбцов, но в виде единственной строки. - табличный подзапрос. Возвращает значения одного или нескольких столбцов, но в виде нескольких строк. Пример скалярного подзапроса - выдать все даты, на которые приходятся максимальные отпуска товаров. Select kolvo, dat from Rashod Where kolvo = ( select max (kolvo) from rashod )

Примером строкового подзапроса может служить следующий запрос. Выбрать из таблицы Rashod все факты отпуска товара покупателям, проживающим в г. Орске. Select * from Rashod where pokup in ( select pokup from Pokup where adres like ‘ % Орск‘ ) Поскольку покупателей, проживающих в г. Орске, может быть несколько, подзапрос возвращает несколько значений. Pokup Петров Антонов Adres г. Орск, пр. Победы 21, кв. 7 г. Орск, ул. Советская 64, кв. 12

Пример: Использование ключевых слов any (some) и all Перечислить все факты отгрузки товаров, в которых количество отпускаемых това превышает среднее значение. Select * from Rashod where kolvo > all (select pokup, avg (kolvo) from Rashod group by pokup) Запрос выполняется следующим образом: - в таблице Rashod строки группируются по значению столбца pokup; Kolvo pokup 45 Антонов 60 Борисов 120 Борисов 90 Иванов 20 Иванов 75 Иванов 50 Петров 160 Петров 180 Сидоров

- для каждого покупателя вычисляется среднее значение отпущенного ему товара (в единицах); Kolvo pokup 45 Антонов 90 Борисов 90 Иванов 47. 5 Иванов 105 Петров 130 Сидоров - из таблицы Rashod выбираются те строки, в которых значение в столбце kolvo превышает все найденные средние значения. Бывают случаи, когда в условии поиска нужно указать, что из таблицы необходимо отобрать только те записи, для которых подзапрос возвращает одно или более значений. В таких запросах используется предикат Exists, который принимает значение истина , когда подзапрос не пуст, т. е. содержит хотя бы одну строку.

имеры: Пр Выдать список всех покупателей, которые покупали товар. Select p. pokup From Pokup p Where exists ( select r. pokup from Rashod r where r. pokup = p. pokup ) Список покупателей, не приобретавших товар, можно получить, используя предикат Not Exists: Select p. pokup From Pokup p Where not exists ( select r. pokup from Rashod r where r. pokup = p. pokup )

В запросе указаны псевдонимы для таблиц r и p. Запрос выполняется следующим образом: - из перечисленных столбцов r. *, p. adres составляется декартово произведение таблиц Rashod и Pokup. - из декартового произведения выбрасываются записи, которые не удовлетворяют условию, заданному в предложении where. Например - выдать все записи таблицы Rashod. Для каждого отпуска рассчитать общую стоимость товара. Чтобы вычислить стоимость отпуска, количество отпущенного товара умножается на цену. Select r. *, t. cena, r. kolvo * t. cena as stoim From Rashod r, Tovar t Where r. tovar = t. tovar Пример - вычислить общую стоимость отпущенных товаров за 19. 10. 2004 г. Select sum (r. kolvo * t. cena) as stoim From Rashod r, Tovar t Where r. tovar = t. tovar and r. dat = ‘ 19. 04. 2004’ Результат представлен ниже. stoim 6720

Многотабличные запросы Пример - выдать все записи таблицы Rashod. Для каждого отпуска рассчитать общую стоимость товара. Чтобы вычислить стоимость отпуска, количество отпущенного товара умножается на цену. Select r. *, t. cena, r. kolvo * t. cena as stoim From Rashod r, Tovar t Where r. tovar = t. tovar В запросе указаны псевдонимы для таблиц r и t. Запрос выполняется следующим образом: - из перечисленных столбцов составляется декартово произведение таблиц Rashod и Tovar. - из декартового произведения выбрасываются записи, которые не удовлетворяют условию, заданному в предложении where.