Скачать презентацию ОСНОВЫ ЯЗЫКА SQL SQL Structured Скачать презентацию ОСНОВЫ ЯЗЫКА SQL SQL Structured

ОСНОВЫ ЯЗЫКА SQL_12.ppt

  • Количество слайдов: 75

ОСНОВЫ ЯЗЫКА SQL ОСНОВЫ ЯЗЫКА SQL

• SQL (Structured Query Language) – Структурированный Язык Запросов – стандартный язык запросов • SQL (Structured Query Language) – Структурированный Язык Запросов – стандартный язык запросов по работе с реляционными БД. Язык SQL появился после реляционной алгебры, и его прототип был разработан в конце 70 -х годов в компании IBM Research. Он был реализован в первом прототипе реляционной СУБД фирмы IBM System R. В дальнейшем этот язык применялся во многих коммерческих СУБД и в силу своего широкого распространения постепенно стал стандартом «дефакто» для языков манипулирования данными в реляционных СУБД.

• Первый международный стандарт языка SQL был принят в 1989 г. (его принято • Первый международный стандарт языка SQL был принят в 1989 г. (его принято называть SQL/89 или SQL 1). Иногда стандарт SQL 1 также называют стандартом ANSI/ISO, и подавляющее большинство доступных на рынке СУБД поддерживают этот стандарт почти полностью. Однако развитие информационных технологий, связанных с базами данных, и необходимость реализации переносимых приложений потребовали в скором времени доработки расширения первого стандарта SQL. • В конце 1992 г. был принят новый международный стандарт языка SQL, который принято называть SQL/92 или SQL 2. И он не лишен недостатков, но в то же время является существенно более точным и полным, чем SQL/89. В настоящий момент большинство производителей СУБД внесли изменения в свои продукты так, чтобы они в большей степени удовлетворяли стандарту SQL 2. • В 1999 году появился новый стандарт, названный SQL 3. Если отличия между стандартами SQL 1 -и SQL 2 во многом были количественными, то стандарт SQL 3 претерпел серьезные качественные изменения.

• SQL нельзя в полной мере отнести к традиционным языкам программирования, он не • SQL нельзя в полной мере отнести к традиционным языкам программирования, он не содержит традиционные операторы, управляющие ходом выполнения программы, операторы описания типов и многое другое, он содержит только набор стандартных операторов доступа к данным, хранящимся в базе данных. Операторы SQL встраиваются в базовый язык программирования, которым может быть любой стандартный язык типа C++, Object Pascal, COBOL и т. д. Кроме того, операторы SQL могут выполняться непосредственно в интерактивном режиме.

Структура SQL • В отличие от реляционной алгебры, где были представлены только операции запросов Структура SQL • В отличие от реляционной алгебры, где были представлены только операции запросов к БД, SQL является полным языком, в нем присутствуют не только операции запросов, но и операторы, соответствующие DDL - Data Definition Language - языку описания данных. Кроме того, язык содержит операторы, предназначенные для управления (администрирования) БД.

Язык описания данных • Представляет собой одну из самых главных частей языка SQL. Основная Язык описания данных • Представляет собой одну из самых главных частей языка SQL. Основная задача языка DDL – единое описание данных и их структур вне зависимости от выбранной системы управления базами данных. Язык DDL вводит единые типы данных и единые языковые конструкции (т. е. операторы языка) для создания любых структур данных.

Типы данных • Тип данных – это способ кодирования информации в двоичную форму и Типы данных • Тип данных – это способ кодирования информации в двоичную форму и способ декодирования этой информации в форму, понятную человеку. Тип данных – не только форма информации, но множество операций по работе над этой информацией. Так если тип данных – число, то базовыми операциями над этой информацией будут операции сложения, вычитания, умножения и деления, если тип данных – строка текста, то очевидно это будут операции соединения и разъединения строк, поиска текста в строке и т. п. Можно выделить три основные группы типов данных: - строковые (текстовые); - числовые; - дата/время. • Рассмотрим основные типы данных, используемые в языке DDL и определенные в стандарте SQL 2. (табл. )

Основные операторы DDL • Основные операторы языка DDL представлены в следующей таблице. Основные операторы DDL • Основные операторы языка DDL представлены в следующей таблице.

Создание таблицы базы данных • Как известно, таблица базы данных представляет собой основную структуру Создание таблицы базы данных • Как известно, таблица базы данных представляет собой основную структуру данных, используемую в реляционных базах данных. Для создания таблицы базы данных в любой СУБД необходимо отправить в СУБД специальную команду на языке SQL. Для создания таблиц используется оператор CREATE TABLE. • Синтаксис (т. е. форма оператора) выглядит следующим образом: CREATE TABLE <имя таблицы> (<список полей>); где <имя таблицы> – последовательность букв и цифр латинского алфавита или русского алфавита 2, обозначающее будущую таблицу; <список полей> определим далее

• Более детальное представление синтаксиса CREATE TABLE выглядит следующим образом: • CREATE TABLE • Более детальное представление синтаксиса CREATE TABLE выглядит следующим образом: • CREATE TABLE <имя таблицы> (<Имя поля 1> <Тип данных1> <Ключевые слова -признаки 1>, <Имя поля 2> <Тип данных2> <Ключевые словапризнаки 2>, … );

• Пример: • CREATE TABLE Продажа (Дата. Операции DATETIME NOT NULL, Номер. Сделки • Пример: • CREATE TABLE Продажа (Дата. Операции DATETIME NOT NULL, Номер. Сделки INTEGER NOT NULL, Наименование. Клиента VARCHAR(50), Наименование. Товара VARCHAR(30), Количество NUMERIC(6, 2), Цена NUMERIC(6, 2), Сумма NUMERIC(10, 2) ); В результате выполнения оператора система управления базами данных создает таблицу базы данных с именем Продажа и указанными в скобках полями. Имена таблиц и полей всегда пишутся без пробелов. В случае необходимости смыслового разделения по словам в наименовании таблицы или поля необходимо вместо пробела использовать символ подчеркивания.

Определение первичного и внешнего ключей • Кроме определения структуры таблицы, при создании таблицы необходимо Определение первичного и внешнего ключей • Кроме определения структуры таблицы, при создании таблицы необходимо указать первичный ключ, а также связи таблицы с другими таблицами БД. Первичный ключ определяется с помощью инструкции PRIMARY KEY(<имена полей, входящих в первичный ключ>). Внешний ключ определяется с помощью инструкции FOREIGN KEY (<имя поля таблицы, участвующее в связи>).

Предположим необходимо создать три таблицы: • Продажа (Номер. Продажи, Дата. Операции, Код. Клиента, Код. Предположим необходимо создать три таблицы: • Продажа (Номер. Продажи, Дата. Операции, Код. Клиента, Код. Товара, Количество, Цена, Сумма) • Товар (Код. Товара, Наименование), • Клиент (Код. Клиента, Наименование, Адрес).

• Создание таблиц необходимо начинать с создания независимых (справочных) таблиц. Создадим независимую таблицу • Создание таблиц необходимо начинать с создания независимых (справочных) таблиц. Создадим независимую таблицу Товар, в качестве первичного ключа которой определим поле Код. Товара: CREATE TABLE Товар ( Код. Товара INTEGER NOT NULL, Наименование VARCHAR (50) NOT NULL, PRIMARY KEY (Код. Товара) ) • Таким же образом создадим таблицу Клиент, в качестве первичного ключа определим поле Код. Клиента: CREATE TABLE Клиент ( Код. Клиента INTEGER NOT NULL, Наименование VARCHAR (50) NOT NULL, Адрес VARCHAR (100), PRIMARY KEY (Код. Клиента) )

• Теперь определим таблицу Продажа. В качестве первичного ключа определим поле Номер. Продажи. • Теперь определим таблицу Продажа. В качестве первичного ключа определим поле Номер. Продажи. Кроме того, таблица Продажа имеет два внешних ключа: Код. Клиента и Код. Товара, посредством которых осуществляется ее связь с соответствующими таблицами БД. CREATE TABLE Продажа ( Номер. Продажи INTEGER NOT NULL, Дата. Операции DATETIME NOT NULL, Код. Клиента INTEGER NOT NULL, Код. Товара INTEGER NOT NULL, Количество NUMERIC(6, 2), Цена NUMERIC(6, 2), Сумма NUMERIC(10, 2), PRIMARY KEY (Номер. Продажи), CONSTRAINT Состоит_из FOREIGN KEY (Код. Товара) REFERENCES Товар ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT Оформляется FOREIGN KEY (Код. Клиента) REFERENCES Клиент );

• Здесь строка CONSTRAINT определяет наименование для связи между таблицами и несет чисто • Здесь строка CONSTRAINT определяет наименование для связи между таблицами и несет чисто информативный характер. Так для внешнего ключа Код. Товара определено описание Состоит_из. Команда REFERENCES указывает, с какой таблицей должна быть установлена связь. Далее следуют необязательные правила удаления и обновления значений для данного отношения, так команда ON DELETE CASCADE устанавливает режим каскадного удаления связанных записей для отношений Продажа-Товар.

• В таблице, создаваемой первой в базе данных невозможно определить внешние ключи, т. • В таблице, создаваемой первой в базе данных невозможно определить внешние ключи, т. к. при попытке их определения СУБД сравнивает определение каждого внешнего ключа с определениями связанных таблиц. СУБД проверяет, соответствуют ли другу внешний ключ и первичный ключ в связанных таблицах, как по числу столбцов, так и по типу данных. Если две или более таблиц образуют ссылочный цикл, необходимо создать таблицу без определения внешнего ключа, а затем (когда будет определена вторая таблица) добавить его определение с помощью инструкции ALTER TABLE.

Изменение определения таблицы ALTER TABLE • Чаще всего инструкция ALTER TABLE применяется для добавления Изменение определения таблицы ALTER TABLE • Чаще всего инструкция ALTER TABLE применяется для добавления столбцов в существующую таблицу. Для добавления полей в таблицу используется команда ADD. Добавим в таблицу Клиент поле Телефон: ALTER TABLE Клиент ADD Телефон VARCHAR (10) • Удаление полей из таблицы производится с помощью команды DROP. Удалим из таблицы Клиент поле Телефон: ALTER TABLE Клиент DROP Телефон

• В команде ALTER TABLE существует возможность изменения первичных и внешних ключей таблицы. • В команде ALTER TABLE существует возможность изменения первичных и внешних ключей таблицы. • В качестве примера создадим новую таблицу Группа. Товара (Код. Группы, Наименование), а также создадим внешний ключ и соответствующую связь таблицы Группа. Товара с таблицей Товар. CREATE TABLE Группа. Товара ( Код. Группы INTEGER NOT NULL, Наименование VARCHAR (30), PRIMARY KEY (Код. Группы) ) • Добавим в таблицу Товар поле Группа. Товара: ALTER TABLE Товар ADD Группа. Товара INTEGER • Сделаем поле Группа. Товара внешним ключом и установим связь с таблицей Группа. Товара: ALTER TABLE Товар ADD CONSTRAINT Относится_к FOREIGN KEY (Группа. Товара) REFERENCES Группа. Товара

• Точно таким же образом можно удалить связь между таблицами. Для этого необходимо • Точно таким же образом можно удалить связь между таблицами. Для этого необходимо использовать команду DROP CONSTRAINT Имя. Связи. • Имя связи используется тоже, что и при создании внешнего ключа в строке CONSTRAINT. Если имя связи присвоено не было, то для изменения внешнего ключа придется удалить таблицу и воссоздать ее в новом формате. Например, для удаления связи Относится_к между таблицами Группа-Товара и Товар, необходимо выполнить следующий запрос: • ALTER TABLE Товар • DROP CONSTRAINT Относится_к

Создание индексов • Оператор CREATE INDEX используется для создания индекса – специальной структуры данных Создание индексов • Оператор CREATE INDEX используется для создания индекса – специальной структуры данных увеличивающую скорость поиска информации по заранее определенным полям. • CREATE INDEX <Наименование индекса> ON <Наименование таблицы> (<список полей для индексирования>);

• Пример: CREATE INDEX Индекс. Дата. Операции ON Продажа (Дата. Операции); • В • Пример: CREATE INDEX Индекс. Дата. Операции ON Продажа (Дата. Операции); • В результате выполнения этого оператора СУБД создаст специальную структуру данных для ускорения процесса поиска данных по выбранному полю (полям). Подробнее об индексах, ограничениях целостности будет рассказано в отдельном занятии. • Удаление таблиц • Для удаления таблицы используется оператор DROP TABLE с указанием имени таблицы. • Пример: DROP TABLE Продажа;

Язык запросов (Data Query Language) в SQL состоит из единственного оператора SELECT. Этот единственный Язык запросов (Data Query Language) в SQL состоит из единственного оператора SELECT. Этот единственный оператор поиска реализует все операции реляционной алгебры. Один и тот же запрос может быть реализован несколькими способами, и, будучи все правильными, они, тем не менее, могут существенно отличаться по времени исполнения, и это особенно важно для больших баз данных.

• Синтаксис оператора SELECT имеет следующий вид: SELECT [ALL | DISTINCT ] <список • Синтаксис оператора SELECT имеет следующий вид: SELECT [ALL | DISTINCT ] <список полей> | *) FROM <Список таблиц> [WHERE <условие выборки или соединения>] [GROUP BY <список полей группировки>] [HAVING <условие для группы>] [ORDER BY <список полей сортировки > [ASC] | [DESC] ] • Здесь ключевое слово ALL означает, что в результирующий набор строк включаются все строки, удовлетворяющие условиям запроса. Значит, в результирующий набор могут попасть одинаковые строки. И это нарушение принципов теории отношений (в отличие от реляционной алгебры, где по умолчанию предполагается отсутствие дубликатов в каждом результирующем отношении). Ключевое слово DISTINCT означает, что в результирующий набор включаются только различные строки, то есть дубликаты строк результата не включаются в набор. Символ *. (звездочка) означает, что в результирующий набор включаются все столбцы из исходных таблиц запроса. • – список полей через запятую. Формат записи: <имя таблицы1>. <имя поля 1> as <название 1>, … <имя таблицы. N>. <имя поля N> as <название. N>

• Здесь <имя таблицы> - имя или псевдоним одной из таблиц, указанных в • Здесь <имя таблицы> - имя или псевдоним одной из таблиц, указанных в разделе FROM (см. ниже); <имя поля> - имя поля этой таблицы; <название> наименование поля в результирующей таблице. • В списке полей также допускаются арифметические выражения из полей таблиц, а также функции агрегирования (такие как SUM, MIN, MAX, AVG). • В разделе FROM задается перечень исходных отношений (таблиц) запроса через запятую и процедура их условного соединения (если это условное соединение имеет место в данном запросе). • В разделе WHERE задаются условия отбора строк полученной таблицы или условия соединения кортежей исходных таблиц, подобно операции условного соединения в реляционной алгебре. .

• В разделе GROUP BY задается список полей группировки. В разделе HAVING задаются • В разделе GROUP BY задается список полей группировки. В разделе HAVING задаются условия, накладываемые на каждую группу, попадающую в таблицу результатов выполнения запроса. • В части ORDER BY задается список полей сортировки. Записи результирующей таблицы упорядочиваются по возрастанию сначала по первому полю в списке, затем по второму и т. д. • Если необходимо произвести сортировку по убыванию по какому-либо полю, то необходимо после имени поля указать ключевое слово DESC.

• В выражении условий раздела WHERE могут быть использованы следующие предикаты: • Предикаты • В выражении условий раздела WHERE могут быть использованы следующие предикаты: • Предикаты сравнения { =, <>, >, <, >=, <= }, которые имеют традиционный смысл. • Предикат Between A and В - принимает значения между А и В. Предикат истинен, когда сравниваемое значение попадает в заданный диапазон, включая границы диапазона. Одновременно в стандарте задан и противоположный предикат Not Between A and В, который истинен тогда, когда сравниваемое значение не попадает в заданный интервал, включая его границы. • Предикат вхождения в множество IN (множество) истинен тогда, когда сравниваемое значение входит в множество заданных значений. При этом множество значений может быть задано простым перечислением или встроенным подзапросом. Одновременно существует противоположный предикат NOT IN (множество), который истинен тогда, когда сравниваемое значение не входит в заданное множество.

• Предикаты сравнения с образцом LIKE и NOT LIKE. Предикат LIKE требует задания • Предикаты сравнения с образцом LIKE и NOT LIKE. Предикат LIKE требует задания шаблона, с которым сравнивается заданное значение, предикат истинен, если сравниваемое значение соответствует шаблону, и ложен в противном случае. Предикат NOT LIKE имеет противоположный смысл. По стандарту в шаблон могут быть включены специальные символы: • символ подчеркивания «_» - для обозначения любого одиночного символа; • символ процента (%) - для обозначения любой произвольной последовательности символов; остальные символы, заданные в шаблоне, обозначают самих себя.

• Предикат сравнения с неопределенным значением IS NULL. Понятие неопределенного значения было внесено • Предикат сравнения с неопределенным значением IS NULL. Понятие неопределенного значения было внесено в концепции баз данных позднее. Неопределенное значение интерпретируется в реляционной модели- как значение, неизвестное на данный момент времени. Это значение при появлении дополнительной информации в любой момент времени может быть заменено на некоторое конкретное значение. При сравнении неопределенных значений не действуют стандартные правила сравнения: одно неопределенное значение никогда не считается равным другому неопределенному значению. Для выявления равенства значения некоторого атрибута неопределенному применяют специальные стандартные предикаты: • <имя атрибута> IS NULL и <имя атрибута> IS NOT NULL,

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

• SELECT - ключевое слово, которое сообщает СУБД, что эта команда является запросом. • SELECT - ключевое слово, которое сообщает СУБД, что эта команда является запросом. Все запросы начинаются этим словом с последующим пробелом. За ним может следовать способ выборки - с удалением дубликатов (DISTINCT) или без удаления (ALL, подразумевается по умолчанию). Затем следует список перечисленных через запятую столбцов, которые выбираются запросом из таблиц, или символ '*' (звездочка) для выбора всей строки. Любые столбцы, не перечисленные здесь, не будут включены в результирующее отношение, соответствующее выполнению команды. Это, конечно, не значит, что они будут удалены или их информация будет стерта из таблиц, потому что запрос не воздействует на информацию в таблицах — он только показывает данные.

• FROM – ключевое слово, подобно SELECT, которое должно быть представлено в каждом • FROM – ключевое слово, подобно SELECT, которое должно быть представлено в каждом запросе. Оно сопровождается пробелом и затем именами таблиц, используемых в качестве источника информации. В случае если указано более одного имени таблицы, неявно подразумевается, что над перечисленными таблицами осуществляется операция декартова произведения. Таблицам можно присвоить именапсевдонимы, что бывает полезно для осуществления операции соединения таблицы с самой собою или для доступа из вложенного подзапроса к текущей записи внешнего запроса (вложенные подзапросы здесь не рассматриваются). • Все последующие разделы оператора SELECT являются необязательными.

• Самый простой запрос SELECT без необязательных частей соответствует просто декартову произведению наборов • Самый простой запрос SELECT без необязательных частей соответствует просто декартову произведению наборов данных, где каждая запись (строка) первой таблицы соединяется со всеми записями второй. Например, выражение SELECT * FROM Rl, R 2 соответствует декартову произведению таблиц R 1 и R 2. Выражение SELECT R 1. A, R 2. B FROM R 1, R 2 соответствует проекции декартова произведения двух таблиц на два столбца А из таблицы Rl и В из таблицы R 2, при этом дубликаты всех строк сохранены, в отличие от операции проекции в реляционной алгебре, где при проекции по умолчанию все дубликаты кортежей уничтожаются. • WHERE – ключевое слово, за которым следует предикат – условие, налагаемое на запись в таблице, которому она должна удовлетворять, чтобы попасть в выборку, аналогично операции селекции в реляционной алгебре.

Реализация операций реляционной алгебры средствами оператора SELECT Горизонтальный выбор (фильтрация) • Для выбора кортежей Реализация операций реляционной алгебры средствами оператора SELECT Горизонтальный выбор (фильтрация) • Для выбора кортежей (записей набора данных), отвечающих некоторому условию необходимо выполнить запрос вида: SELECT [ALL | DISTINCT ] <список полей> | *) FROM <Список таблиц> [WHERE <условие выборки или соединения>]

Все последующие примеры будут рассматриваться на примере следующих таблиц. Все последующие примеры будут рассматриваться на примере следующих таблиц.

• Пример № 1. Выбрать из таблицы «Закупки» все операции по товару «Товар1» • Пример № 1. Выбрать из таблицы «Закупки» все операции по товару «Товар1» (Код. Товара=1). SELECT * FROM Закупки WHERE Код. Товара = 1 где * - означает, что необходимо выбрать все поля таблицы; Закупки – имя таблицы, из которой производится выборка; [Код. Товара] – имя поля таблицы Закупки (квадратные скобки используются в том случае, если в составе имени поля или таблицы используется символ пробела). В результате будут выбраны все записи, для которых выполняется условие [Код. Товара] = 1 свидетельствующее о том, что операция проводится по товару «Товар 1» . Результат операции представлен в следующей таблице.

• Пример № 2. • Выбрать из таблицы «Закупки» все операции за период • Пример № 2. • Выбрать из таблицы «Закупки» все операции за период с 01. 05 по 31. 03. 05. SELECT * FROM Закупки WHERE Дата. Операции >= #01/01/2005# AND Дата. Операции <= #15/01/2005# • где # – символ, использующийся для обрамления значений типа дата в запросах; AND – операция логического «И» . Результат операции приведен в следующей таблице.

• Следует обратить внимание на то, что при работе с одной таблицей в • Следует обратить внимание на то, что при работе с одной таблицей в запросе имя таблицы в выражениях, состоящих из имен полей, констант и арифметико-логических операций, можно опускать. Если же в запросе используется более одной таблицы, необходимо при указании поля таблицы обязательно указывать также имя таблицы, т. е. показанный выше запрос должен выглядеть следующим образом: SELECT Закупки. * FROM Закупки WHERE Закупки. Дата. Операции >= #01/01/2005# AND Закупки. Дата. Операции <= #15/01/2005#

• Пример № 3. • Выбрать из таблицы «Закупки» все операции за период • Пример № 3. • Выбрать из таблицы «Закупки» все операции за период с 01. 05 по 31. 03. 05 по товарам «Товар1» (Код. Товара=1) и «Товар 2» (Код. Товара=2). SELECT Z. * FROM Закупки Z WHERE (Z. Дата. Операции >= #01/01/2005# AND Z. Дата. Операции <= #15/01/2005#) AND (Z. Код. Товара = 1 OR Код. Товара = 2) • где Закупки – имя таблицы, из которой производится выборка; [Дата. Операции], [Код. Товара] – имена полей этой таблицы на которые накладываются определенные условия; AND – операция логического "И"; OR – операция логического "ИЛИ". Результат операции приведен в следующей таблице.

Вертикальный выбор (проекция) • Вертикальный выбор (проекция) представляет собой операцию выбора определенных столбцов из Вертикальный выбор (проекция) • Вертикальный выбор (проекция) представляет собой операцию выбора определенных столбцов из множества столбцов таблицы. Если какойлибо столбец выбран, то можно говорить, что по нему построена проекция. • Пример № 1. • Выбрать данные о дате, товаре, количестве, цене производимых операций закупки. SELECT [Дата. Операции], [Код. Товара], [Количество], [Цена] FROM Закупки • В результате получим таблицу, содержащую все записи таблицы Закупки и перечисленные в списке поля. Результат операции представлен в следующей таблице.

• Сочетание операций горизонтального и вертикального выбора можно проиллюстрировать следующим примером. • Пример • Сочетание операций горизонтального и вертикального выбора можно проиллюстрировать следующим примером. • Пример № 2. • Выбрать данные о дате, товаре, количестве, цене производимых операций закупки за период с 01. 2005 по 15. 01. 2005 г. SELECT Дата. Операции, Код. Товара, Количество, Цена FROM Закупки WHERE Закупки. Дата. Операции >= #01/01/2005# AND Закупки. Дата. Операции <= #15/01/2005# • Результат операции представлен в следующей таблице.

Условное соединение • Условное соединение позволяет соединить кортежи наборов данных, для которых выполняется некоторое Условное соединение • Условное соединение позволяет соединить кортежи наборов данных, для которых выполняется некоторое условие. Соединение может быть внутренним (естественным) и внешним. • Внутреннее соединение может быть реализовано либо за счет помещения условия соединения в секции WHERE оператора SELECT, либо с использованием оператора INNER JOIN в секции FROM. Рассмотрим примеры.

• • Пример № 1. В рассмотренных выше примерах для таблицы Закупки для • • Пример № 1. В рассмотренных выше примерах для таблицы Закупки для идентификации товара использовалось поле Код. Товара, представляющее собой ссылку на запись таблицы Товары. Однако, реальная таблица с информацией по закупкам товаров представляемая пользователю должна содержать не коды товаров, а их наименования. Следовательно, требуется соединить набор данных • Закупки с набором данных Товары по условию Закупки. Код. Товара = Товары. Код. Товара. SELECT Закупки. Дата. Операции, Товары. Наименование. Товара, Закупки. Количество, Закупки. Цена FROM Закупки, Товары WHERE Закупки. Код. Товара = Товары. Код. Товара • Результат этого запроса приведен в следующей таблице.

• При выполнении этой операции СУБД последовательно формирует строки декартова произведения таблиц, перечисленных • При выполнении этой операции СУБД последовательно формирует строки декартова произведения таблиц, перечисленных во фразе FROM, проверяет, удовлетворяют ли данные сформированной строки условиям фразы WHERE, и если удовлетворяют, то включает в ответ на запрос те ее поля, которые перечислены во фразе SELECT. • В таблице Закупки также имеется поле Код. Контрагента (т. е. код поставщика), являющееся ссылкой (внешним ключом) на запись таблицы Контрагенты. В реальной ситуации пользователю после выполнения запроса нужны не коды записей, а наименования контрагентов в строках таблицы. Для этого снова необходимо использовать внутреннее соединение.

• • Пример № 2. На основе ранее составленного запроса составим новый запрос, • • Пример № 2. На основе ранее составленного запроса составим новый запрос, который, помимо внутреннего соединения таблиц Закупки и Товары, будет также содержать соединение таблицы Закупки с таблицей Контрагенты. SELECT Закупки. Дата. Операции, Товары. Наименование. Товара, Контрагенты. Наименование. Контрагента, Закупки. Количество, Закупки. Цена FROM Закупки, Товары, Контрагенты WHERE Закупки. Код. Товара=Товары. Код. Товара AND Закупки. Код. Контрагента= Контрагенты. Код. Контрагента; • При выполнении этого запроса СУБД сначала составляет строки декартова произведения таблиц Закупки, Товары, Контрагенты, после чего происходит фильтрация результирующего отношения по условию, указанному во фразе WHERE. • Результат выполнения этой операции приведен в следующей таблице.

• Как уже отмечалось, внутреннее соединение может также создаваться через секцию FROM. Так • Как уже отмечалось, внутреннее соединение может также создаваться через секцию FROM. Так аналогичный предыдущему запрос может выглядеть следующим образом. SELECT Z. Дата. Операции, T. Наименование. Товара, K. Наименование. Контрагента, Z. Количество, Z. Цена FROM (Закупки AS Z INNER JOIN Товары AS T ON Z. Код. Товара=T. Код. Товара) INNER JOIN Контрагенты K ON Z. Код. Контрагента=K. Код. Контрагента • где INNER JOIN - обозначение внутреннего соединения, ON – условие соединения.

• Внешнее соединение реализуется за счет помещения условия соединения в секции FROM оператора • Внешнее соединение реализуется за счет помещения условия соединения в секции FROM оператора SELECT. Внешнее соединение получается сцеплением записей первой таблицы с записями второй таблицы при соблюдении условия соединения. Естественно предположить, что при соединении таблиц будут иметь место ситуации, когда какой-либо строке первой таблицы не будет соответствовать ни одна строка второй таблицы (например, какой-либо товар не поступал в отчетном периоде). В этом случае в соответствующих столбцах результирующей таблицы будут неопределенные значения (значения типа NULL). Неопределенные значения в таблицах и запросах означают отсутствие данных. В зависимости от взаимного положения таблиц при соединении различают левое и правое внешнее соединения.

• Пример № 3. • Предположим, необходимо определить список товаров, по которым закупок • Пример № 3. • Предположим, необходимо определить список товаров, по которым закупок не производилось. Для этого общий список товаров (таблицу Товары) необходимо соединить с таблицей Закупки внешним левым соединением. SELECT Товары. [Наименование. Товара], Закупки. Количество FROM Товары LEFT JOIN Закупки ON Товары. [Код. Товара] = Закупки. [Код. Товара] WHERE Закупки. Количество IS NULL • где LEFT JOIN – обозначение левого внешнего соединения; IS NULL – проверка поля на неопределенное значение (т. е. на отсутствие значения).

• • • Левое соединение в данном случае означает, что записи таблицы Товары, • • • Левое соединение в данном случае означает, что записи таблицы Товары, соединяются слева с записями таблицы закупки (необходимо образно представить, что таблица Товары располагается в левой части, а таблица Закупки в правой. При соединении сначала берется запись левой таблицы (Товары) и соединяется поочередно с записями правой таблицы (Закупки) при выполнении условия соединения. Результат представляет собой таблицу, полученную от выполнения данной операции над всеми записями левой таблицы (таблицы Товары). Результат выполнения запроса без строки WHERE Закупки. Количество IS NULL

• Из результата соединения отбираются записи, отвечающие условию Закупки. Количество IS NULL • • Из результата соединения отбираются записи, отвечающие условию Закупки. Количество IS NULL • Т. е. необходимо отобрать только те записи, которые говорят об отсутствии информации (имеют неопределенное значение в поле количество). Следовательно, по данным товарам операций не производилось. Результат операции представлен в следующей таблице.

Операции группировки и агрегирования • • • В SQL добавлены дополнительные функции, которые позволяют Операции группировки и агрегирования • • • В SQL добавлены дополнительные функции, которые позволяют вычислять обобщенные групповые значения. Это так называемые агрегатные функции. Эти функции используются очень часть для получения итоговых (расчетных) значений на основе данных одного или нескольких полей таблицы. Для применения агрегатных функций предполагается предварительная операция группировки. При группировке все множество кортежей отношения разбивается на группы, в которых собираются кортежи, имеющие одинаковые значения атрибутов, которые заданы в списке группировки. Список группировки – это список полей таблицы, указанных в строке GROUP BY. В языке SQL определены следующие наиболее часто применяемые агрегатные функции (см. табл. ) Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент. С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями МАХ и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке

• • • Функция COUNT () Пример № 1. В качестве примера определим • • • Функция COUNT () Пример № 1. В качестве примера определим сколько товаров перечислено в списке товаров. SELECT Count(*) AS Количество. Товаров FROM Товары; • Результат операции представлен в следующей таблице.

• Пример № 2. • Определить сколько операций закупки было произведено. SELECT COUNT(Количество) • Пример № 2. • Определить сколько операций закупки было произведено. SELECT COUNT(Количество) AS Количество. Операций. Покупки FROM ( SELECT Товары. Наименование. Товара, Закупки. Количество FROM Товары LEFT JOIN Закупки ON Товары. Код. Товара = Закупки. Код. Товара ) • Результат операции представлен в следующей таблице. Несмотря на то, что результатом вложенного запроса является 6 строк (см. предыдущие примеры), результатом данного запроса является число 4. Это происходит потому, что в аргументе функции COUNT указано поле Количество и подсчет происходит только строк результирующего запроса, в которых значение данного поля не пусто. В других агрегирующих функциях поля со значение NULL игнорируются.

• Однако, чаще всего операции агрегирования применяются совместно с операциями группировки. • Пример • Однако, чаще всего операции агрегирования применяются совместно с операциями группировки. • Пример № 3. • Посчитать количество операций покупки каждого товара. SELECT Товары. Наименование. Товара, COUNT (Количество) AS Количество. Операций. Покупки FROM ( SELECT Товары. Наименование. Товара, Закупки. Количество FROM Товары LEFT JOIN Закупки ON Товары. Код. Товара = Закупки. Код. Товара ) GROUP BY Товары. Наименование. Товара • Результат выполнения запроса представлен в следующей таблице.

• Функция SUM () • Пример № 4. • Предположим, необходимо выяснить сколько • Функция SUM () • Пример № 4. • Предположим, необходимо выяснить сколько было куплено каждого наименования товара за период с 01. 2005 по 15. 01. 2005. В этом случае запрос будет выглядеть следующим образом. SELECT Код. Товара, SUM(Количество) AS Количество. Товара FROM Закупки WHERE Дата. Операции BETWEEN #01/01/2005# AND #15/01/2005# GROUP BY Код. Товара • Результат запроса приведен в следующей таблице.

• В случае, если в условии запроса используется поле, не указанное в строке • В случае, если в условии запроса используется поле, не указанное в строке SELECT, необходимо использование строки WHERE. Если же необходимо указать условие для группы, желательно использование строки HAVING, т. к. в этом случае запрос выполняется быстрее. Так, если необходимо определить товары с объемами закупок свыше 50, запрос будет выглядеть следующим образом. SELECT Код. Товара, SUM (Количество) AS Количество. Товара FROM Закупки WHERE Дата. Операции BETWEEN #01/01/2005# AND #20/01/2005# GROUP BY Код. Товара HAVING SUM (Количество)>50 • Результат запроса приведен в следующей таблице.

Теоретико-множественные операции Операция объединения • Объединение двух таблиц содержит те строки, которые есть либо Теоретико-множественные операции Операция объединения • Объединение двух таблиц содержит те строки, которые есть либо в первой, либо во второй, либо в обеих таблицах. Для рассмотрения примеров добавим к существующей модели таблицу Продажи. Структура таблицы и ее содержимое приведено ниже.

• • Пример № 1. Предположим необходимо получить список товаров, по которым в • • Пример № 1. Предположим необходимо получить список товаров, по которым в некотором периоде (например, с 01. 2005 по 31. 01. 2005) было движение (производились операции закупки либо продажи). • Для решения задачи необходимо выбрать все записи из таблицы Закупки за определенный период по полю [Код. Товара]. Аналогичную операцию необходимо выполнить для таблицы Продажи. Далее необходимо соединить запросы посредством оператора UNION. SELECT Код. Товара FROM Закупки WHERE (Дата. Операции>=#01/01/2005#) AND (Дата. Операции<=#31/01/2005#) UNION SELECT Код. Товара FROM Продажи WHERE (Дата. Операции>=#01/01/2005#) AND (Дата. Операции<=#31/01/2005#) • Результат запроса приведен в следующей таблице. Примечание: Здесь и далее предполагается, что получение списка кодов товаров тождественно получению списка наименований товаров, т. к. список наименований товаров получается простым соединением с данными таблицы Товары.

Операция пересечения • Пересечение двух таблиц содержит только те строки, которые есть и в Операция пересечения • Пересечение двух таблиц содержит только те строки, которые есть и в первой, и во второй. В общем случае операцию пересечения можно представить в виде следующего шаблона: SELECT <Имя поля> FROM A WHERE <Имя поля> IN (SELECT <Имя поля> FROM B) • где А и B – имена таблиц или запросов, результат пересечения которых необходимо получить.

• Пример № 2. • Определить список товаров, по которым были закупки и • Пример № 2. • Определить список товаров, по которым были закупки и продажи. SELECT Код. Товара FROM Закупки WHERE Код. Товара IN (SELECT Код. Товара FROM Продажи) • где IN – оператор вхождения в другой набор данных; выражение SELECT Код. Товара FROM Продажи – представляет собой подзапрос. Использование подзапросов будет подробно рассмотрено в дальнейшем. В настоящий момент достаточно знать, что для решения различных вычислительных задач в операторе SELECT широко используются подзапросы. Подзапрос также представляет собой SQLоператор, начинающийся со слова SELECT. • Представленный выше запрос читается следующим образом: выбрать коды товаров из таблицы Закупки, для которых код товара также входит в таблицу, формируемую путем выбора кодов товаров из таблицы Продажи. • Результат запроса приведен в следующей таблице.

Операция разности Разность двух таблиц содержит только те строки, которые есть в первой, но Операция разности Разность двух таблиц содержит только те строки, которые есть в первой, но отсутствуют во второй. Определение данной операции на языке SQL практически полностью совпадает с определением операции пересечения. Отличием является наличие слова NOT перед словом IN. • Пример № 3. • Определить список товаров, которые закупались, но не продавались SELECT Код. Товара FROM Закупки WHERE Код. Товара NOT IN (SELECT Код. Товара FROM Продажи) • Результат запроса приведен в следующей таблице. Таким образом, не существует товаров, которые закупались, но при этом не продавались.

Расширенное декартово произведение Реализация операции расширенного декартового произведения, как уже упоминалось производится с помощью Расширенное декартово произведение Реализация операции расширенного декартового произведения, как уже упоминалось производится с помощью самого простого запроса SQL. • Например, при использовании следующего запроса SELECT Товары. Наименование. Товара, Контрагенты. Наименование. Конт рагента FROM Товары, Контрагенты будет получен следующий результат.

Использование подзапросов • Для решения задач, требующих сложной обработки данных, необходимо использовать такое средство Использование подзапросов • Для решения задач, требующих сложной обработки данных, необходимо использовать такое средство как вложенные запросы, называемые также подзапросами. • Подзапрос представляет собой такой же SQL-запрос, начинающийся со слова SELECT, который выполняет расчет какого-либо значения, отбор каких-либо значений из других таблиц. Подзапрос всегда заключается в круглые скобки. • Подзапросы необходимо использовать в следующих случаях: 1. Когда необходимо провести отбор (фильтрацию) одной таблицы на основе рассчитанного или выбранного значения из другой таблицы. 2. Когда запрос формирует таблицу на основе других запросов.

Пример № 1. • Найти товар, объем продаж которого за январь 2005 года был Пример № 1. • Найти товар, объем продаж которого за январь 2005 года был максимальным. • Для решения задачи необходимо: • 1. рассчитать объемы продаж по товарам за январь 2005 года. Эта задача реализуется следующим SQL запросом. SELECT Код. Товара, SUM (Количество) AS Объем. Продаж FROM Продажи WHERE (Дата. Операции >= #01/01/2005#) AND (Дата. Операции <= #31/01/2005#) GROUP BY Код. Товара • Присвоим этому запросу имя Объемы. Продаж. • Результат выполнения запроса приведен в таблице.

• 2. рассчитать максимальный объем продаж из полученного в запросе Объемы. Продаж набора • 2. рассчитать максимальный объем продаж из полученного в запросе Объемы. Продаж набора данных. SELECT MAX(Объем. Продаж) AS Макс. Объем. Продаж FROM (…) • Далее вместо многоточия подставим созданный ранее запрос. Получим SELECT MAX(Объем. Продаж) AS Макс. Объем. Продаж FROM (SELECT Код. Товара, SUM (Количество) AS Объем. Продаж FROM Продажи WHERE (Дата. Операции >= #01/01/2005#) AND (Дата. Операции <= #31/01/2005#) GROUP BY Код. Товара) • Результат выполнения данного запроса приведен в следующей таблице.

• Вместо полного текста запроса можно просто подставить его имя. Тогда получим следующий • Вместо полного текста запроса можно просто подставить его имя. Тогда получим следующий запрос SELECT MAX (Объем. Продаж) AS Макс. Объем. Продаж FROM Объемы. Продаж • Назовем этот запрос Объем. Продаж. Mакс. • 3. отобрать записи из запроса Объемы. Продаж для получения списка • товаров с максимальным объемом продаж за январь 2005 года. Переформулируем эту задачу в терминах, близких к SQL: выделить (отобрать) все записи из запроса Объемы. Продаж, для которых выполняется условие Объем. Продаж = Макс. Объем. Продаж. SELECT O. Код. Товара FROM Объемы. Продаж AS O, Объем. Продаж. Макс AS M WHERE O. Объем. Продаж=M. Макс. Объем. Продаж • Результат запроса приведен в следующей таблице.

Язык манипулирования данными • Язык манипулирования данными DML (Data Manipulating Language) необходим в случаях, Язык манипулирования данными • Язык манипулирования данными DML (Data Manipulating Language) необходим в случаях, когда необходимо внести изменения в таблицу или несколько таблиц БД. Эти изменения касаются не структурных изменений, а изменение содержимого таблицы. Язык DML состоит из трех команд: INSERT – добавление строк в таблицу DELETE – удаление строк из таблицы UPDATE – обновление данных в таблице • Добавление данных • В реляционных СУБД существует три основных способа добавления новых строк в БД: 1. однострочная инструкция INSERT – позволяет добавлять в таблицу одну строку; 2. многострочная инструкция INSERT – обеспечивает извлечение строк из одной части БД и добавление их в другую таблицу; 3. SELECT INTO – создание новой таблицы на основе результатов выборки запроса. • Синтаксис команды INSERT в общем виде выглядит так: INSERT INTO <Имя. Таблицы> [<Список. Полей>] VALUES (<Список. Значений>) <Имя. Таблицы> – Имя таблицы, в которую будет производится вставка значений, перечисленных в <Список. Значений>. <Список. Полей> – список полей, в которые будет произведена вставка значений. Он может отсутствовать, в этом случае в списке значений необходимо последовательно перечислить значения для каждого поля таблицы в том порядке, в котором они расположены в таблице.

• • • Пример № 1. Создать запрос для добавления в таблицу Контрагенты • • • Пример № 1. Создать запрос для добавления в таблицу Контрагенты нового контрагента. INSERT INTO Контрагенты VALUES (5, “Контрагент5”) В результате запроса в таблицу Контрагенты будет добавлена запись. Результат выполнения запроса приведен в следующей таблице. В данном случае строка INSERT INTO не содержит списка полей, в которые будет происходить добавление значений. Это означает, что строка VALUES должна содержать значения для каждого поля таблицы, в противном случае запрос не будет исполнен. Таким образом, при необходимости добавления целой строчки в таблицу (значений для каждого ее поля) список полей в запросе INSERT можно опускать. Запрос INSERT не должен нарушать правил целостности информации в СУБД, в противном случае он не будет исполнен.

Пример № 2. • Добавить в таблицу Закупки новую операцию закупки. INSERT INTO Закупки Пример № 2. • Добавить в таблицу Закупки новую операцию закупки. INSERT INTO Закупки (Код. Операции, Дата. Операции, Код. Товара, Количество) VALUES (20, #30/01/2005#, 3, 100) • В результате выполнения запроса, в таблицу Закупки будет добавлена новая строка. Однако, т. к. не все поля таблицы перечислены в запросе, добавление произойдет только в те из них, которые перчислены в стоке INSERT INTO, следовательно поля Код. Контрагента и Цена останутся пустыми. Точнее в эти поля попадут значения, установленные по умолчанию в свойствах поля данной таблицы. Если значения по умолчанию установлены не были, данные поля примут значения NULL. • Результат выполнения запроса представлен в следующей таблице.

• Пример № 3. • В качестве примера групповых операций создадим запрос, в • Пример № 3. • В качестве примера групповых операций создадим запрос, в результате которого будет создана новая таблица Новые. Закупки (Дата, Контрагент, Товар), в которую будет помещена информация, полученная из таблицы Закупки. SELECT Z. Дата. Операции AS Дата, K. Наименование. Контрагента AS Контрагент, T. Наименование. Товара AS Товар INTO Новые. Закупки FROM Закупки Z, Контрагенты K, Товары T WHERE Z. Код. Контрагента=K. Код. Контрагента AND Z. Код. Товара=T. Код. Товара • В результате выполнения запроса будет создана таблица, структура и содержимое которой изображены ниже.

Изменение данных • Чаще всего изменение данных производится с помощью команды UPDATE, позволяющей выполнять Изменение данных • Чаще всего изменение данных производится с помощью команды UPDATE, позволяющей выполнять как простое обновление данных в столбце, так и сложные операции модификации данных во множестве строк таблицы. • Рассмотрим синтаксис команды UPDATE <Имя. Таблицы> SET <Имя. Столбца 1>=<выражение 1>, <Имя. Столбца 2>=<выражение 2> WHERE <условие> • Пример. • Увеличить закупочную цену всех товаров в 2 раза. UPDATE Закупки SET Цена = Цена*2;

Удаление данных • Удаление данных из таблицы осуществляется с помощью команды DELETE. При этом Удаление данных • Удаление данных из таблицы осуществляется с помощью команды DELETE. При этом за одну операцию можно удалить от одной до нескольких тысяч строк. Синтаксис команды выглядит следующим образом: DELETE FROM <Имя. Таблицы> WHERE <условие выборки> • Пример. • Удалить все операции продажи, совершенные между 01. 2005 и 15. 01. 2005 включительно. • DELETE FROM Продажи • WHERE Дата. Операции BETWEEN #01/01/2005# AND #15/01/2005#