sql лекц 43 стр 1. Подготовка к изучению. Программы для работы

• Название: Подготовка к изучению. Программы для работы
• Дата: 13.05.2022
• Размер: 0.66 Mb.
• Имя файла: sql лекц 43 стр 1 .docx
• Тип: Документы; #527003
• страница: 9 из 9

Отбор по спискам значений

В условии отбора можно указать список возможных значений поля. Для этого исполь- зуется ключевое слово IN, после которого в скобках перечисляются значения. Значе- ния разделяются запятыми.

Следующий запрос выберет из таблицы Tasks записи задач, которые относятся к спискам №№ 2, 3 и 4.

select * from Tasks where Tasklist_ID in (2,3,4)


Выражение IN с перечислением можно использовать не только с числами, а с любы- ми типами значений. Например, со строками:

select * from Categories where Category in ('Семья', 'Дом')

Отбор по диапазонам значений

В SQL также есть способ указать диапазон значений в условии отбора. Для этого ис- пользуется ключевое слово BETWEEN, после которого указываются минимальное и максимальное значения, разделённые словом AND. Следующий запрос тоже от- берёт из таблицы Tasks записи задач из списков №№2, 3 и 4, но вместо перечисле- ния мы укажем в нём диапазон значений:

select * from Tasks where Tasklist_ID between 2 and 4

Комбинирование условий сравнения

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

select Tasklist, Task, Description from Tasks, Tasklists where Tasks.Tasklist_ID=Tasklists.Tasklist_ID

and Tasks.Tasklist_ID=1
Ключевое слово AND (и) в данном случае означает, что должны выполняться оба условия, которые оно объединяет.

SQL поддерживает также ключевое слово OR (или). Оно означает, что должно выпол- няться хотя бы одно из указанных условий. Например, следующий запрос выберет информацию о задачах, входящих в список задач номер 3 или номер 4.

select * from Tasks

where Tasklist_ID=3 or Tasklist_ID=4

В условиях отбора можно комбинировать сколько угодно выражений сравнения, объединяемых ключевыми словами AND и OR. Но при этом нужно очень хорошо представлять, в каком порядке будут выполняться эти сравнения. Проблема в том, что при сравнении ключевые слова AND и OR имеют разный приоритет: сначала об- рабатываются все условия, связанные словом AND, и только после этого — комбина- ции условий с помощью OR.

Например, если мы хотим отобрать записи по всем выполненным задачам (Done = 1), входящим в списки №3 и №4, то следующий запрос вернёт неправильный результат:

select * from Tasks where Tasklist_ID=3 or Tasklist_ID=4 and Done=1

Почему так получилось? Потому что при разборе запроса движок сначала объединил условия, связанные словом AND:

Tasklist_ID=4 and Done=1

А потом добавил условие, стоящее перед OR:

Tasklist_ID=3
Полученную комбинацию условий можно словами описать так: «задача входит в спи- сок №4 и выполнена, либо задача входит в список №3 (и при этом неважно, выпол- нена или нет)».

Чтобы избежать подобной путаницы, можно использовать скобки. Комбинации усло- вий в скобках обрабатываются с наивысшим приоритетом:

select * from Tasks where (Tasklist_ID=3 or Tasklist_ID=4) and Done=1




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

select * from Tasks where Tasklist_ID in (3,4) and Done=1

Это хорошая практика составления удобочитаемых запросов: использовать условия с ключевыми словами IN (задавая перечень значений) или BETWEEN (указывая диапазон значений) вместо комбинирования условий с помощью OR. Кроме того, в большинстве случаев такие запросы ещё и быстрее выполняются.

Практика

Для выполнения задания скачайте учебную базу данных «Учёт расходов», запустите программу SQLite Database Browser и откройте скачанную базу данных.

Ссылка на тест этого задания в курсе «SQL для непрограммистов»: Условия отбора — тест

1. 
   Составьте запрос, возвращающий значения всех полей таблицы Spendings для покупок на сумму более 1000руб.
   
2. 
   Составьте запрос к таблице Spendings, возвращающий название магазина и по- траченную сумму для покупок, совершённых не позже 3 июня 2015года.
   

Подсказка. Эту дату в запросе нужно указать в виде такой текстовой строки: '2015-06-03'

3. 
   Составьте запрос, возвращающий названия категорий с номерами 1, 3 и4 (с использованием ключевого словаIN)
   
4. 
   Составьте запрос к таблице Spendings, возвращающий название магазина, дату покупки и сумму для покупок, сумма которых находится в диапазоне от 1000до 2000 руб (с использованием ключевого словаbetween).
   
5. 
   Составьте запрос, обращающийся к таблицам Goods и Goods_Spendings и воз- вращающий названия отслеживаемых товаров и потраченные на них суммы, превышающие 500 руб.
   

Запрос с сортировкой

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




Но результат запроса выглядит не очень красиво, и его неудобно читать. Категории и списки «размазаны» по строкам, и хочется их сгруппировать, чтобы работать со списком было удобнее.

SQL предоставляет такую возможность с помощью ещё одного ключевого слова: ORDER BY (сортировать по). Следующий запрос отсортирует результаты по названию списка задач.

select Category, Tasklist, Task, Description from Categories, Tasks, Tasklists

where Tasks.Tasklist_ID=Tasklists.Tasklist_ID and Tasklists.Category_ID=Categories.Category_ID order by Tasklist


В обобщённом виде запрос теперь выглядит так:

SELECT {список полей} FROM {список таблиц} WHERE {условие отбора} ORDER BY

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

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

select Category, Tasklist, Task, Description from Categories, Tasks, Tasklists

where Tasks.Tasklist_ID=Tasklists.Tasklist_ID and Tasklists.Category_ID=Categories.Category_ID order by Category, Tasklist

Сортировка по номерам полей вместо имён

В списке полей вместо названий полей можно использовать их порядковые номера в результатах запроса. В предыдущем примере вместо

order by Category, Tasklist

можно написать:

order by 1, 2
Весь запрос тогда будет выглядеть таким образом:

select Category, Tasklist, Task, Description from Categories, Tasks, Tasklists

where Tasks.Tasklist_ID=Tasklists.Tasklist_ID and Tasklists.Category_ID=Categories.Category_ID order by 1, 2
Результат его работы не изменится.

Сортировка в обратном порядке

По умолчанию ORDER BY сортирует числовые значения по возрастанию, а тексто- вые поля по алфавиту. Но этот порядок можно изменить на обратный с помощью ключевого слова DESC, которое добавляется после имени (или номера) поля, по ко- торому выполняется сортировка.

Сравните результат выполнения следующих двух запросов. Первый запрос выбирает из таблицы Tasks все краткие описания задач и сортирует их по алфавиту в порядке возрастания.

select Task from Tasks order by Task

Второй запрос делает то же самое (только вместо имени поля указан его номер), но в выражение ORDER BY добавлено слово DESC.

select Task from Tasks order by 1 desc


Противоположностью ключевого слова DESC является ключевое слово ASC. Эти слова являются сокращениями от английских слов ascending (восходящий) и de- scending (нисходящий).

Слово ASC обычно не указывают, потому что порядок сортировки по возрастанию ис- пользуется по умолчанию.

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

Оба запроса выбирают из таблиц Tasklists и Tasks название списка задач (поле Tasklist) и краткое описание задачи (поле Task). В обоих запросах для сортировки используются последовательно поля Tasklist и Task. Но в первом запросе слово DESC стоит после Tasklist, а во втором — после Task.

select Tasklist, Task from Tasklists, Tasks

where Tasklists.Tasklist_ID=Tasks.TaskList_ID order by Tasklist desc, Task

select Tasklist, Task from Tasklists, Tasks

where Tasklists.Tasklist_ID=Tasks.TaskList_ID order by Tasklist, Task desc

Практика

Для выполнения задания скачайте учебную базу данных «Учёт расходов», запустите программу SQLite Database Browser и откройте скачанную базу данных.

Ссылка на тест этого и следующего задания в курсе «SQL для непрограммистов»: Сортировка и удаление дубликатов — тест

1. 
   Составьте запрос, возвращающий названия всех категорий покупок, отсортиро- ванных поалфавиту.
   
2. 
   Составьте запрос к таблице Spendings, возвращающий названия магазинов, даты совершенных покупок и суммы покупок, отсортированные в порядке убы- ваниясумм.
   
3. 
   Составьте запрос, возвращающий названия отслеживаемых товаров и все по- траченные на них суммы в следующем порядке: названия отсортированы по алфавиту, а суммы по каждому товару отсортированы в порядкеуменьшения.
   
4. 
   Составьте запрос, возвращающий название отслеживаемого товара, название магазина, в котором он был куплен и потраченную на этот товар сумму для тех покупок, в которых эта сумма превышает 200 руб, отсортированные в следую- щем порядке: название товара по алфавиту, название магазина по алфавиту, потраченная сумма в порядкеуменьшения.
   

Это демонстрационный фрагмент книги «SQL для непрограммистов». Полная версия книги доступна на этой странице:

https://www.webursitet.ru/product/sql-dlya-neprogrammistov-kniga.html

Введите здесь