Текст книги "Oracle SQL. 100 шагов от новичка до профессионала. 20 дней новых знаний и практики"

Автор книги: Максим Чалышев

Важные замечания

Несколько условий можно объединять скобками, например из таблицы MAN нам необходимо вывести те строки, где людям 25 или 28 лет с именем Иван. Это можно сделать с помощью следующего запроса:

Очень важно понимать отличие AND от OR, например выведите авто с марками LADA и BMW – в этом запросе необходимо использовать инструкцию OR и ни в коем случае не AND.

Вопросы учеников

Можно ли неравенство заменить на инструкцию NOT?

 
Да, в большинстве запросов можно так сделать.
Например, запросы
 

 
идентичны.
 

Как поменять несколько условий, перечисленных после WHERE в SQL-запросе, на обратные?

Вопрос не очень понятен, но предположим, у нас есть запрос, который возвращает все строки из таблицы AUTO с марками BMW и LADA.

Если необходимо посмотреть авто не BMW и LADA, то запрос обретет следующий вид:

Контрольные вопросы и задания для самостоятельного выполнения

1. Сколько строк вернет запрос?

2. Выберите из таблицы CITY (*) город, где 200 тысяч жителей (PEOPLES); город с наименованием Москва (CITYNAME).

3. Выберите из таблицы CITY города с населением (PEOPLES) не 500 тысяч жителей.

4. Выберите из таблицы AUTO (*) все синие AUDI (COLOR, MARK).

5. Выберите из таблицы AUTO номера (regnum) и марки (MARK) всех VOLVO и BMW.

6. Выберите из таблицы MAN имена, фамилии (FIRSTNAME, LASTNAME) и возраст людей (YEAROLD), которым больше 29 и меньше 35 лет.

Шаг 13. Сортировка результатов запросов

Введение

Данные, выводимые с помощью запроса SELECT, часто необходимо сортировать по одному или нескольким выводимым колонкам.

Для этого в языке SQL есть специальный оператор ORDER BY, который используется в команде SELECT и пишется в конце запроса. Для того чтобы изменить порядок сортировки на обратный, используется ключевое слово DESC.

Теория и практика

ORDER BY используется для сортировки выводимых данных по заданным колонкам: от большего к меньшему, и наоборот.

Текстовые данные будут отсортированы от А до Я или же от Я до А – при использовании ключевого операнда DESC.

Числовые данные сортируются от меньшего числа к большему числу и от большего числа к меньшему при использовании ключевого операнда DESC.

Данные DATE также: от меньшей даты к большей, и наоборот.

Синтаксис

Где таблица – наименование таблицы, условия выборки которой могут быть объединены логическими операндами.

Примеры запросов

Выведите все записи из таблицы AUTO, отсортируйте автомобили по марке (MARK).

Рисунок 25. Выбрать из таблицы AUTO, отсортировать по марке

Выведите все записи из таблицы AUTO, отсортируйте автомобили по дате создания и цвету в обратном порядке (RELEASED, MARK).

Рисунок 26. Запрос к таблице AUTO: сортировка по дате и цвету авто

Сортировка по двум колонкам RELEASED от меньшей даты к большей, MARK от A—Z.

Выберите только те записи из таблицы MAN, где возраст (YEAROLD) человека больше 30 лет; отсортируйте выбранные записи по возрасту, сортировку произведите в обратном порядке.

Рисунок 27. Выбор из таблицы MAN, где возраст больше 30 лет; сортировка по году выпуска

Пример использования ORDER BY и WHERE

Выберите наименования городов (CITYNAME) и население (PEOPLES) из таблицы CITY, отсортируйте выбранные данные в обратном порядке по наименованию города и количеству населения (PEOPLES).

Рисунок 28. Запрос к CITY: сортировка по названию и населению в обратном порядке

Важные замечания

Следует пояснить, как работает сортировка по нескольким колонкам.

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

Команда сортировки ORDER BY выполняется в запросе последней, сортируется итоговое выражение запроса.

Это важная информация, и она пригодится нам в дальнейшем, при создании сложных SQL-запросов.

Вопросы учеников

Как сделать, если я хочу одну колонку отсортировать по возрастанию, а две другие по убыванию?

Давайте рассмотрим пример.

Выберите наименования городов (CITYNAME) и население (PEOPLES) из таблицы CITY, отсортируйте выбранные данные по коду города (CITYCODE), в обратном порядке по наименованию города и количеству населения.

Я знаю, что для инструкции ORDER BY существует альтернативный синтаксис. Расскажите о нем.

Перепишем этот запрос с использованием альтернативного синтаксиса:

То есть вместо названий колонок таблиц в инструкции ORDER BY мы используем порядковый номер колонки в нашем запросе – у CITYCODE он равен 1, у CITYNAME 2, у PEOPLES 3.

Можно ли отсортировать запрос по полю, которого нет в запросе, но которое есть в таблице после инструкции FROM?

Да, синтаксис SQL это допускает, и вот пример:

Контрольные вопросы и задания для самостоятельного выполнения

1. Выберите записи из таблицы CITY, где в названии города (CITYNAME) есть слог «ем», отсортируйте запрос по названию города (CITYNAME) и по популяции (PEOPLES) в обратном порядке.

2. Выберите все записи из таблицы AUTO, отсортируйте записи по цвету (COLOR) и по марке (MARK) автомобиля в обратном порядке.

Шаг 14. Ограничение на количество выбранных строк ROWNUM, TOP (n)

Введение

Иногда запросы строятся таким образом, что на экран сразу выводится множество строк.

А что если нам необходимо ограничить количество строк выводимой информации, то есть из десятков тысяч строк нам достаточно нескольких строк для анализа информации?

Теория и практика

Для решения этой задачи в разных диалектах языка SQL используются разные синтаксические конструкции: в MS SQL это конструкция TOP, в ORACLE есть специальный предикат ROWNUM, в PostgreSql, MYSQL для этого существует конструкция LIMIT.

Разберем диалект SQL ORACLE.

Конструкция ROWNUM позволит ограничить количество выводимых строк на заданную величину.

Синтаксис

Примеры

Вывести первые 5 строк из таблицы AUTO.

Рисунок 29. Запрос с ограничением строк (первые 5)

Вывести первые 5 строк из таблицы CITY, где население (PEOPLES) городов больше 300 000.

Рисунок 30. Выбрать первые 5 строк, запрос города: население больше 300 000

Важные замечания

Если использовать ROWNUM совместно с сортировкой, то необходимо прибегнуть к специальному приему, иначе ROWNUM не будет работать как нужно и запрос вернет неверные данные.

Правильно следует написать так:

Вывести первые 5 автомобилей, отсортированных по дате производства (RELEASEDT).

Ошибочный запрос:

Правильный запрос:

Вопросы учеников

Покажите, как ограничивать вывод строк в MYSQL, MS SQL и PostgreSQL.

Разберемся на примере.

Вывести первые 5 строк из таблицы CITY, где население (PEOPLES) городов больше 3000.

ORACLE SQL

MY SQL

MS SQL

PostgreSQL

Контрольные вопросы и задания для самостоятельного выполнения

1. Поясните, как использовать ROWNUM с сортировкой в SQL-запросе.

2. Выведите на экран первые 3 строчки из таблицы CITY.

3. Выведите на экран первые 4 строчки из таблицы CITY, отсортированные по количеству населения (PEOPLES).

4. Выведите на экран данные из таблицы MAN.

Шаг 15. Вставка данных в таблицу – INSERT

Введение

Ранее мы с вами изучили, как создавать таблицы и изменять структуру таблиц, а также как извлекать данные из таблиц на экран с помощью команды SELECT. Мы выбирали данные, которые уже были в таблицах. Настало время узнать о том, как добавить необходимые данные в таблицу.

Теория и практика

Для добавления данных в SQL используется команда INSERT.

Команда INSERT существует в SQL в двух вариантах.

Во-первых, для добавления заданных значений – VALUES.

Синтаксис

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

Также очень важно, чтобы колонки и значения соответствовали по типу данных.

Примеры

Добавим новые сведения о человеке в таблицу MAN:

В таблицу MAN добавлена строка о человеке с номером телефона «120120120», именем «Максим», фамилией «Леонидов», кодом города 2, ему 25 лет (PHONENUM, FIRSTNAME, LASTNAME, CITYCODE, YEAROLD).

Добавим сведения о новой машине в таблицу AUTO:

В таблицу AUTO добавлена строка о авто c номером «128877655» марки «LADA», цвет «КРАСНЫЙ», дата выпуска «2001-01-01», «123114444».

Второй вариант, для добавления из запроса SELECT

Здесь источником данных являются не одиночные значения, а настоящий запрос SELECT.

Синтаксис:

Примеры

В таблицу CITY1 добавить все строки из CITY, где население больше миллиона человек.

В таблицу MAN1 добавить все строки из MAN.

Важные замечания

Команда INSERT является командой модификации данных, поэтому ее выполнение необходимо завершить одной из следующих команд:

COMMIT – фиксация изменений;

ROLLBACK – откат изменений.

Только после выполнения фиксации изменений данные появятся в базе.

Обратите внимание: при добавлении данных типа дата (DATE) мы использовали следующую конструкцию: DATE’YYYY-MM-DD», YYYY – текущий год, ММ – месяц, DD – день.

При использовании INSERT с запросом SELECT также необходимо, чтобы последовательность и типы колонок, перечисленных после команды INSERT, совпадали с последовательностью и типами колонок в запросе SELECT.

Вопросы учеников

Все же зачем применять COMMIT или ROLLBACK и что будет, если эти команды не выполнять?

Во многих СУБД применяется транзакционная модель, что это такое – узнаем чуть позже, но сейчас необходимо понимать, что при запуске операций изменения, вставки, удаления данных эти изменения появятся в базе только после выполнения команды COMMIT.

Я использовал только команду INSERT и не применял ни COMMIT, ни ROLLBACK, но данные все равно появились в базе. Почему так вышло?

Некоторые редакторы поддерживают режим автофиксации изменений, то есть выполняют команду COMMIT за вас.

Если я перечислю не все колонки, какие есть в таблице, куда мы добавляем данные, будет ошибка?

Необязательно. В колонки, которые вы не перечислили, будет добавлено пустое (NULL) значение, ошибка возникнет только если на колонках этой таблицы есть ограничение NOT NULL или первичный ключ.

Контрольные вопросы и задания для самостоятельного выполнения

 
1. Повторите материалы записей из данного шага.
2. Добавьте в таблицу городов новый город; код города и название придумайте сами.
3. В таблицу CITY1 добавьте все строки из CITY, где популяция меньше полумиллиона человек.
4. В таблицу AUTO добавьте новую машину 999999 ЛАДА, цвет зеленый, 1999 года выпуска.
 

День четвертый

Шаг 16. Обновление данных – UPDATE

Введение

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

Теория и практика

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

Синтаксис команды

Здесь TABLE_NAME – имя таблицы, где обновляются данные.

Здесь через запятую перечислены имена колонок = значения, а после WHERE описываются условия для отбора обновляемых строк.

Примеры

Добавить к наименованию (CITYNAME) города CITY с кодом (CITYCODE) меньше 2 символ #.

К населению города с населением (PEOPLES) больше 1 000 000 добавить 10.

У всех у людей MAN с именем (FIRSTNAME) Алексей поменять имя на Максим.

У всех у людей с телефоном (PHONENUM), заканчивающихся на 3, поменять имя (FIRSTNAME) на Александр.

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

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

Обнулить колонку PEOPLES в таблице CITY.

Во всей таблице значение колонки PEOPLES будет равно 0.

Важные замечания

Команда обновления данных UPDATE тоже должна завершаться выполнением COMMIT – фиксацией изменений, либо ROLLBACK – откатом изменений.

При выполнении обновления данных следует быть предельно аккуратным и внимательным: последующее восстановление измененных данных, возврат таблицы к состоянию до выполнения UPDATE очень часто бывает затруднителен.

Команда UPDATE TABLE set column1 = val1, columnn = valn без инструкции WHERE обновит значения во всех строчках таблицы TABLE колонок column1, column, будьте особо осторожны при выполнении таких команд.

Вопросы учеников

Если мы обновляем текстовое поле, необходимо ли значение писать в одинарных кавычках?

Да, при обновлении текстовых данных необходимо заключать значения в одинарные кавычки.

Приведите пример обновления данных типа DATE.

Пример: обновите даты выпуска автомобилей LADA на 01.01.2010.

Я выполнил команду UPDATE, попытался выполнить ее повторно из другого окна, но ничего не получилось и программа зависла, в чем причина?

Причина в том, что вы забыли зафиксировать изменения с помощью команды COMMIT. При выполнении первого UPDATE на строки были установлены блокировки, повторное обновление не выполнилось, так как изменения не были вами зафиксированы. Об этом будет более подробная информация в книге в дальнейшем.

Контрольные вопросы и задания для самостоятельного выполнения

 
1. Сколько строк обновит команда UPDATE AUTO SET MARK = «BMW» WHERE 1=0?
2. Добавить 1 к возрасту людей (MAN) c именем Андрей.
3. Добавить 2 к возрасту людей (MAN), чей возраст больше 19 лет.
4. К имени людей (MAN), чей телефон содержит 915, добавить префикс 915.
5. У всех у людей (MAN) с телефоном, чей возраст больше 22 лет, поменять имя на Роман.
 

Шаг 17. Удаление данных – DELETE

Введение

Иногда некоторые данные нам необходимо удалять из таблиц. Для этого в языке SQL есть специальная команда – DELETE.

Теория и практика

Команда DELETE.

Синтаксис

Здесь Имя таблицы – имя таблицы, из которой мы удаляем строки, WHERE – условие для отбора строк, которые мы удаляем.

Примеры

Удалить записи из таблицы CITY1 с кодами городов CITYCODE 7, 9.

Удалить из таблицы AUTO1 все автомобили BMW (MARK).

Удалить из таблицы AUTO1 все автомобили с датой выпуска больше 01.01.2017.

Завершите выполнение командой COMMIT для фиксации изменений.

Важные замечания

Данная команда может удалить записи только в одной заданной таблице.

Команда DELETE также должна завершаться инструкцией COMMIT для фиксации изменений в базе.

Следует помнить, что язык SQL в ORACLE поддерживает механизм транзакций, поэтому модификации данных, в том числе и операции удаления, чаще всего необходимо завершать командой СOMMIT или ROLLBACK.

COMMIT – для фиксации изменений;

ROLLBACK – для отката изменений.

Команда DELETE без инструкции WHERE очищает все строки в таблице, поэтому при использовании команды DELETE надо соблюдать предельную осторожность.

Вопросы учеников

Чем отличается команда DELETE от команды DROP?

Это совершенно разные команды. Я предлагаю повторить материал на эту тему и ответить на данный вопрос самостоятельно.

Как еще можно очистить таблицу?

Для этого есть специальный оператор TRUNCATE TABLE TABLENAME. При его выполнении происходит быстрая очистка всех записей таблицы. Мы еще будем изучать эту тему в следующих шагах.

Я пытаюсь удалить записи из некоторой таблицы, но возникает ошибка.

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

Контрольные вопросы и задания для самостоятельного выполнения

 
1. Чем команда DELETE TABLE_NAME отличается от команды DROP TABLE TABLE_NAME?
2. Напишите команду для удаления из таблицы AUTO таких записей, где дата выпуска авто больше 2018 года.
3. Повторите материалы данного шага.
 

Шаг 18. Псевдонимы

Введение

В языке SQL есть такая синтаксическая конструкция, как псевдонимы.

С помощью псевдонимов мы можем большим или сложным наименованиям таблиц или колонок таблиц в запросе SQL присвоить более короткие, удобные и понятные нам псевдонимы (ALIAS).

Теория и практика

Псевдонимы для колонок, выводимых в запросе, задаются с помощью инструкции AS, псевдонимы же для таблиц указываются сразу же после имени таблицы.

Примеры

Вывести из таблицы MAN колонки «имя», «фамилия» и «возраст».

Для таблицы задать псевдоним m для колонки имени (FIRSTNAME), для колонки фамилии (LASTNAME) – Fml.

Рисунок 31. Демонстрация работы псевдонимов: запрос

Вывести из таблицы AUTO марку и цвет автомобиля.

 
• Для таблицы AUTO задать псевдоним AV;
• для колонки «марка» задать псевдоним Mr;
• для колонки «цвет» задать псевдоним CV.
 

Вывести только автомобили с годом выпуска больше 01.01.2001.

Рисунок 32. Запрос к AUTO: псевдонимы

Обратите внимание, как формируется условие для при обращении к колонке типа DATE.

Важные замечания

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

Если мы задали псевдоним для таблицы, из которой SQL-запрос выбирает данные, то и в условии WHERE мы также должны использовать заданный псевдоним.

Вопросы учеников

Если мы используем псевдоним для таблицы и псевдонимы для колонок, должны ли мы обращаться к колонкам в инструкции WHERE по псевдонимам колонок?

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

 
Пример
 

 
Запись вида
 

 
будет неверной.
 

Можно ли нескольким выводимым колонкам запроса SQL задать псевдоним, а другим не задавать?

Конечно; вот пример такого запроса:

Контрольные вопросы и задания для самостоятельного выполнения

1. Найдите ошибку в следующем запросе:

2. Найдите ошибку в еще одном запросе:

3. Выведите с помощью запроса SQL наименование города из таблицы CITY. Для таблицы задайте псевдоним GR, для колонки «наименования» задайте псевдоним NM.

4. Выведите количество жителей в городе Москва, для колонки PEOPLES задайте псевдоним CLZ, для таблицы задать псевдоним MS.

Шаг 19. BETWEEN

Введение

В языке SQL есть специальная конструкция, которая позволяет работать с интервалами – своего рода фильтр, позволяющий выбирать данные, соответствующие заданному интервалу значений. Этот оператор называется BETWEEN и может использоваться как в выборке SELECT, так и в операциях модификации и удаления данных (UPDATE, DELETE).

Теория и практика

Рассмотрим применение BETWEEN в команде SELECT.

Синтаксис:

Примеры

Выбираем из таблицы MAN (*) всех людей, чей возраст (YEAROLD) от 25 до 32 лет.

Рисунок 33. Запрос к MAN: возраст от 25 до 32

Выбираем города CITY * с населением (PEOPLES) от 300 000 до 1 000 000.

Рисунок 34. Запрос к таблице CITY: население от 300 000 до 1 000 000

Выбираем людей * с именем Андрей (FIRSTNAME) и возрастом (YEAROLD) от 25 до 28 лет.

Рисунок 35. Запрос к таблице MAN: население от 25 до 28 лет, имя Андрей

Выбираем людей * с возрастом не в интервале от 25 до 28 лет.

Выбрать машины * с годом выпуска (RELEASEDT) от 2001 до 2014.

Рисунок 36. Запрос всех записей из AUTO

Важные замечания

Следует отметить, что использование BETWEEN разумно только для данных числовых типов и для данных типа DATE, что естественно, так как мы можем найти числа в заданном интервале и даты в заданном интервале, а вот со строкам и текстом это сделать затруднительно.

Также важное замечание: обратите внимание, что BETWEEN позволяет выбрать данные, принадлежащие и равные нижней и верхней границам заданного интервала.

Вопросы учеников

Оператор BETWEEN на что-то похож, можно ли обойтись без него?

Оператор BETWEEN создан для удобства, и, разумеется, его можно заменить несколькими логическими выражениям, и вот пример.

Пример:

Выбираем города CITY * с населением (PEOPLES) от 300 000 до 1 000 000.

 
Не будем использовать BEETWEEN.
 

Можно ли в запросе использовать несколько BETWEEN, объединенных логическими операндами?

Да, разумеется, такой запрос можно написать. Выберем из таблицы MAN людей с возрастом в интервалах от 20 до 30 и от 35 до 39 лет.

Вы говорили, что BETWEEN также можно использовать и в операторах модификации данных?

Да, как и все другие операторы, используемые для выбора строк в WHERE.

Примеры

Обновим дату создания авто на 01.01.2014 для автомобилей с датой создания в интервале от 01.01.2011 до 01.01.2014.

DELETE MAN WHERE YEAROLD BETWEEN 7 AND 16

Удаление всей информации о людях, которым от 7 до 16 лет. Обратите внимание: возраст 7 и 16 входит в заданный интервал и эта информация также будет удалена.

Контрольные вопросы и задания для самостоятельного выполнения

1. Как можно заменить оператор BETWEEN и обойтись без него, чтобы запрос возвращал правильные данные?

2. Выберите города с населением от 40 000 до 2 000 000 человек, напишите SQL-запрос.

3. Выберите города с населением НЕ в интервале 40 000 до 2 000 000 человек, напишите SQL-запрос.

4. Выберите людей * с возрастом НЕ в интервале 25 до 28 лет, используйте NOT, напишите SQL-запрос.

5. Выберите машины с годом выпуска от 2007 до 2011, напишите SQL-запрос.