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

Текст добавлен: 9 октября 2019, 22:30

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

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

Жанр:

Прочая компьютерная литература

сообщить о нарушении

Текущая страница: 3 (всего у книги 5 страниц)

Назад к карточке книги

Шаг 8. Первичные ключи. Вторичные ключи

Введение

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

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

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

Вторичный ключ также называют внешним ключом таблицы.

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

В нашем примере есть две таблицы (таблица CITY, MAN по колонке CITYCODE), в таблице CITY CITYCODE является первичным ключом.

В таблице MAN CITYCODE будет вторичным ключом.

Синтаксис создания первичного ключа:

CREATE TABLE TABLE_NAME

(

column1 DATAtype NULL/NOT NULL,

column2 DATAtype NULL/NOT NULL,

…

CONSTRAINT constraINt_NAME PRIMARY KEY (column1, column2, … column_n)

);

Также возможно создание первичного ключа с помощью конструкции ALTER TABLE:

ALTER TABLE TABLE_NAME ADD CONSTRAINT constraINt_NAME PRIMARY KEY (column1, column2, … column_n);

Синтаксис создания вторичного ключа:

CREATE TABLE TABLE_NAME (

column1 DATAtype NULL/NOT NULL,

…

CONSTRAINT fk_column

FOREIGN KEY (column1, column2, … column_n)

REFERENCES parent_TABLE (column1, column2, … column_n));

Добавление вторичного ключа с помощью конструкции ALTER TABLE:

ALTER TABLE TABLE_NAME

ADD CONSTRAINT constraINt_NAME

FOREIGN KEY (column1, column2, … column_n)

REFERENCES parent_TABLE (column1, column2, … column_n);

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

Первичный ключ может состоять из одной или нескольких колонок.

Пример:

ALTER TABLE TABLE_NAME ADD CONSTRAINT constraINt_NAME PRIMARY KEY (column1);

или же

ALTER TABLE TABLE_NAME ADD CONSTRAINT constraINt_NAME PRIMARY KEY (column1, columnN);

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

Обязательно ли обозначать внешний ключ? Почему это не будет работать просто так?

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

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

1. Повторите материалы данного шага.
2. Поясните, в чем отличие первичного и вторичного ключей.

Шаг 9. Ограничения

Введение

Для колонок таблицы в базе данных можно создавать ограничения.

Ограничения – это специальные синтаксические конструкции уровня колонок таблицы, которые предназначены для поддержания ссылочной целостности данных или для вставки правильных данных согласно бизнес-логике приложения.

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

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

Все ограничения, которые используются в ORACLE SQL, можно разделить на следующие группы:

1. Ограничения на вставку пустых значений NOT NULL

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

Снять данное ограничение можно с помощью команды изменения поля таблицы MODIFY.

Синтаксис

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

CREATE TABLE TABLENAME (

Column1 NOT NULL, ColumnN NOT NULL

);

Это стандартный синтаксис создания таблицы, к имени колонки добавляется синтаксис ограничения NOT NULL.

Или изменять значения для уже готовой таблицы с помощью команды ALTER TABLE.

ALTER TABLE TABLENAME MODIFY ColumnName NOT NULL

Примеры

Создание таблицы «Корзина» с ограничением на вставку пустых значений в колонки itemNAME, itemCOUNT.

CREATE TABLE shopINgcart (

article VARCHAR2 (50) PRIMARY KEY

,itemNAME VARCHAR2 (50) NOT NULL

,itemCOUNT NUMBER NOT NULL

);

Запрет добавления пустого значения в FirstName в таблицу MAN.

ALTER TABLE MAN MODIFY FirstName NOT NULL

Запрет добавления пустого значения в LAStName.

ALTER TABLE MAN MODIFY LAStName NOT NULL

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

Снимаем ограничение на вставку пустых значений:

ALTER TABLE MAN MODIFY LAStName NULL

После выполнения команды значение в колонке LAStName MAN обязательно должно быть заполнено.

Ограничения на уникальность

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

Синтаксис

ALTTER TABLE TABLENAME ADD CONSTRAINT cINs_NAME

UNIQUE (columnNAME);

Пример:

ALTTER TABLE CITY ADD CONSTRAINT CITY_uniq

UNIQUE (CITYNAME);

Названия городов, только уникальные значения.

Ограничения на вторичный ключ

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

Синтаксис

ALTER TABLE for_TABLE

ADD CONSTRAINT fk_const_NAME

FOREIGN KEY (fk_column)

REFERENCES primary_talbe (pk_column);

Здесь for_TABLE, fk_column – таблица, колонка, куда устанавливается ограничение. Проверка значений происходит в таблице primary_talbe и колонке pk_column.

Пример

Здесь для таблицы MAN колонки CITYCODE устанавливается ссылочное ограничение по колонке CITYCODE с таблицей CITY, где CITY является главной таблицей.

ALTER TABLE MAN

ADD CONSTRAINT fk_MAN_CITY_CODE

FOREIGN KEY (CITYCODE)

REFERENCES CITY (CITYCODE);

Ограничение CHECK на вставку и изменение данных

– вычитание;

Синтаксис

ALTER TABLE TABLENAME ADD CONSTRAINT CHECK_NAME

CHECK (condition);

Здесь condition – условие, CHECK_NAME – наименование ограничения, TABLENAME – имя таблицы.

Пример

Ограничение в таблице MAN на возраст (YEAROLD) больше 16 лет:

ALTER TABLE MAN ADD CONSTRAINT CHECK_YEAROLD_MAN

CHECK (YEAROLD> 16);

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

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

ALTTER TABLE CITY ADD CONSTRAINT CITY_uniq

UNIQUE (CITYNAME, CITYCODE);

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

Существуют дополнительные опции для создания ограничений ссылочной целостности:

• On delete cAScade – автоматическое удаление связанных строк по внешнему ключу;

• On delete NULL – значение внешнего ключа устанавливается в NULL.

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

Условие в ограничении CHECK может ссылаться на любой столбец таблицы, но не может ссылаться на столбцы другой таблицы.

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

Чем ограничение на уникальность UNIQUE отличается от первичного ключа (Primary KEY)?

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

Если колонка в таблице содержит пустые значения, а мы добавляем к этой колонке ограничение NOT NULL, что произойдет?

Вы получите сообщение об ошибке, и ограничение не будет добавлено.

Можно ли создавать ограничения для колонок BLOB, CLOB?

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

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

1. Добавьте ограничение для уникальных значений на колонки PHONENUM таблицы AUTO.

2. Добавьте ограничение на вставку пустых значений для колонки YEAROLD в таблице MAN.

3. Добавьте ограничение CHECK для колонки YEAROLD в таблице MAN, чтобы YEAROLD было меньше 100.

4. Добавьте ограничение ссылочной целостности для двух таблиц – AUTO и MAN.

Шаг 10. Индексы

Введение

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

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

Индексы работают по принципу b-tree, то есть сбалансированной древовидной структуры.

Для примера: у нас в одной из колонок таблицы есть уникальные значения от 1 до 500 000.

Нам необходимо найти значение 255 000. По ссылочному индексу мы определяем, больше или меньше искомое значение 250 000, то есть половины всех значений, далее больше или меньше искомое значение 260 000, далее мы переходим к нашему значению 255 000.

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

Индексы создаются для определенной колонки (колонок) таблицы.

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

Синтаксис

CREATE INDEX IDx_NAME ON TABLE_NAME (column_NAME);

Idx_NAME – наименование индекса;

TABLE_NAME – наименование таблицы, где создается индекс;

column_NAME – наименование колонки, для которой создается индекс.

Пример создания индекса для колонки MARK таблицы AUTO:

CREATE INDEX IDx_AUTO_MARK ON AUTO (MARK);

Реверсивный индекс

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

Синтаксис

CREATE INDEX IDx_NAME ON TABLE_NAME (column_NAME) REVERSE;

Пример: создание реверсивного индекса для колонки MARK таблицы AUTO.

CREATE INDEX reg_DATE ON AUTO (reg_num) REVERSE;

Удаление индекса

Для удаления индекса используется команда

DROP INDEX IDx_NAME;

Индексы создаются для определенной колонки таблицы.

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

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

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

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

При наименовании индексов следует придерживаться следующего правила: IDx_имя таблицы_имена_колонок.

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

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

Нет, индексы для такой таблицы, скорее всего, не понадобятся.

Какие типы индексов существуют в различных СУБД?

Существует множество разных типов индексов, но более подробно мы разберем эту тему в следующих шагах.

В моей базе данных фильтр (WHERE) данных, поиск данных всегда осуществляется одновременно по определенному набору колонок. Какие индексы следует использовать в этом случае?

В этом случае создается композитный индекс.

Синтаксис:

CREATE INDEX IDx_NAME ON TABLE_NAME (column_NAME1, column_NAMEn) REVERSE;

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

1. В таблице есть ограничение уникальности, имеет ли смыcл создавать на этой колонке индекс?
2. В некоторой таблице постоянно обновляются записи, следует ли использовать индексы в этой таблице?
3. Создайте индекс на колонку COLOR в таблице AUTO.
4. Создайте реверсивный индекс для колонки YEAROLD в таблице MAN.

День третий

Шаг 11. Простые запросы

Введение

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

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

В нашей рабочей схеме уже есть три таблицы с данными – это таблицы AUTO, CITY, MAN.

Напомню, что в таблице MAN хранятся сведения о покупателях: их имена, их возраст, CITY – это данные о городах, а таблица AUTO содержит сведения об автомобилях некоторого автосалона.

Если человек приобретает автомобиль, то в таблице AUTO в колонке PHONEnum выставляется номер телефона человека, который приобрел машину.

Для извлечения данных из базы и вывода этих данных на экран используются команды, называемые запросами к базе данных, специальная команда SQL – SELECT. Эта команда является наиболее часто используемой командой в языке SQL и постоянно применяется на практике.

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

Начнем с самого легкого запроса, рассмотрим синтаксис самого простого оператора SELECT:

SELECT * FROM TABLE_NAME

Здесь TABLE_NAME – имя таблицы, из которой мы запрашиваем данные.

Символ * означает, что мы выводим на экран данные из всех колонок.

Откройте тестовую среду и выполните запрос

SELECT * FROM CITY;

Рисунок 7. Запрос из таблицы CITY

На экран выведены названия колонок в первой строке, а также данные в каждой колонке из таблицы CITY.

А теперь другой вариант синтаксиса такого же простого запроса SQL:

SELECT column_NAME1, column_NAME1, column_NAMEn FROM TABLE_NAME

В этом варианте вместо звездочки используются наименования колонок и на экран будут выведены только перечисленные колонки из заданной в поле FROM таблицы.

Пример (выполните в нашей тестовой среде):

SELECT CITYCODE, CITYNAME FROM CITY;

Рисунок 8. Запрос к таблице CITY по колонкам CITYCODE, CITYNAME

Результат запроса – на экран выведены только те две колонки, которые мы указали после оператора SELECT.

Существует и другой вариант синтаксиса для SQL-запросов:

SELECT TABLE_NAME.* FROM TABLE_NAME

или

SELECT TABLE_NAME. column_NAME1, TABLE_NAME. column_NAME1, TABLE_NAME. column_NAMEn FROM TABLE_NAME

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

SELECT CITY.* FROM CITY

При выполнении этого запроса SELECT получается результат, совершенно аналогичный тому, что и в примерах выше.

Небольшой лайфхак.

Как я составляю запросы? Сначала пишу SELECT *, затем FROM, имя таблицы, выполняю запрос, а уже после перечисляю колонки, которые необходимо вывести на экран, и далее выполняю запрос повторно.

Фильтр строк WHERE в запросе SELECT

Итак, мы научились выводить на экран все данные из заданной таблицы, но как же поступить, если нам необходимо вывести на экран только избранные строки? Допустим, что в заданной таблице миллион строк, а нам необходимо посмотреть из них лишь 10.

К счастью, язык SQL позволяет это сделать. Для этого в языке SQL и в частности в команде SELECT предусмотрен специальный оператор – WHERE.

Рассмотрим синтаксис команды SELECT с оператором WHERE:

SELECT * или перечень колонок FROM TABLE_NAME WHERE условие отбора строк

Примеры:

Выберем названия городов, где население 300 000 человек.

SELECT * FROM CITY WHERE PEOPLES = 300000

Альтернативная форма записи:

SELECT CITY.* FROM CITY WHERE CITY.PEOPLES = 300000

Выражение в WHERE формируется с помощью математических операндов сравнения, рассмотрим этот момент подробнее.

Операнды сравнения

> больше
<меньше
= строгое равенство
или неравенство! =

Примеры

Выберем все колонки (*) из таблицы городов, где население больше 300 000 человек.

SELECT * FROM CITY WHERE PEOPLES> 300000

Рисунок 9. Запрос таблице CITY с условием

Выберем название города с кодом города, равным 2, из CIty

Альтернативная форма записи:

Рисунок 10. Запрос к таблице CITY по заданному CITYCODE

Выберем все имена и фамилии из таблицы MAN:

Рисунок 11. Запрос двух колонок к таблице MAN

Все колонки (*) возраст больше 27 лет из таблицы MAN:

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

Из таблицы AUTO выберем номера автомобилей, выпущенных после 1 февраля 2005 года.

Рисунок 13. Запрос к таблице AUTO с ограничением по дате

Из таблицы AUTO выберем только зеленые автомобили.

Рисунок 14. Запрос к таблице AUTO, где цвет авто зеленый

Из таблицы MAN выберем только людей с именем Миша.

Рисунок 15. Запрос к таблице MAN: выбираем людей с именем Миша

Если осуществляется сравнение строковых данных, то есть тип данных в колонке сравнения VARCHAR, VARCHAR2, то строка сравнения заключается в одинарные кавычки.

Примеры

Выбрать из таблицы MAN все колонки (*), где имя Миша (равно Миша).

Рисунок 16. Запрос к MAN, где имя равно Миша

Выбрать из таблицы MAN все колонки (*), где имя не Олег (не равно Олег).

Рисунок 17. Запрос к MAN, где имя не равно Олег

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

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

Выражение DATE’YYYY-MM-DD» работает только в СУБД ORACLE, в MS SQL SERVER и POSTGREESQL работа с данными типа «дата» осуществляется по-другому (смотрите подробности документации к этим СУБД).

Следует учитывать, что в некоторых типах баз данных для неравенства можно использовать <> или знак!=, подобную информацию необходимо уточнять в документации к СУБД.

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

Какой способ написания команды SELECT наиболее часто используется?

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

Так все-таки какой смысл в этой звездочке вместо перечисления колонок?

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

Мы можем использовать форму записи с именем таблицы в фильтре WHERE?

Да, и вот пример. SELECT * FROM MAN WHERE MAN.FIRSTNAME= «Олег».

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

1. Чем отличаются разные формы записи SQL-запроса SELECT?
2. Выбрать из таблицы MAN (*) людей, где возраст (YEAROLD) человека больше 30 лет.
3. Выбрать из таблицы городов все колонки (*), где город (CITYNAME) называется Москва.
4. Выбрать названия (CITYNAME) городов CITY с населением (PEOPLES) больше миллиона человек.
5. Выбрать телефоны людей из MAN, чья фамилия (LASTNAME) не Денисов.
6. Выбрать информацию о машинах car (*) синего цвета (COLOR).

Шаг 12. Более сложные условия. Знакомимся с логикой выбора строк

Введение

Язык SQL позволяет задавать и более сложные фильтры отбора строк с помощью оператора WHERE. Для этого в языке SQL применяются логические операнды, позволяющие комбинировать несколько условий, создавать тем самым сложные логические выражения.

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

Итак, логические операнды позволяют объединять несколько условий, чтобы создать более сложные критерии выбора строк в операторе WHERE. Разберемся поподробнее, как это работает.

усл1 AND усл2 – логическое И, позволяет объединить несколько условных выражений, так что запрос вернет строку таблицы, если каждое из условий будет верным.

усл1 OR усл2 – логическое Или, позволяет выбрать строки, если одно из заданных условий верно.

NOT усл – логическое отрицание, выбирает строки, если выражение полностью неверно.

AND OR и NOT – как указано выше, можно гармонично сочетать в запросе.

Синтаксис