Кренделева Н.Ю. Конспект лекций по SQL92 с примерами

Подождите немного. Документ загружается.

Оглавление

Режим SQL...............................................................................................................3

Создание таблиц БД...............................................................................................3

Ограничения данных.............................................................................................4

Пример создания взаимосвязанных таблиц.......................................................5

Выборка данных. Запрос на выборку................................................................6

Запрос на выборку с условием поиска данных................................................6

Агрегатные функции в запросах.........................................................................8

Параметрические запросы..................................................................................9

Использование вычисляемых полей в запросе..................................................10

Группировка полей в запросах..........................................................................10

Использование текстовых сообщений в запросах с группировкой................11

Сортировка данных в запросах.........................................................................11

Многотабличные запросы..................................................................................12

Вложенные запросы.............................................................................................13

Базовая конструкция организации вложенного запроса:...............................13

Двухуровневый подзапрос...................................................................................13

Объединение таблиц............................................................................................15

Переименование полей в запросе на объединение............................................15

Возвращение повторяющихся записей..............................................................15

в запросе на объединение...................................................................................15

Внесение изменений в БД...................................................................................16

INSERT..................................................................................................................16

DELETE................................................................................................................17

UPDATE...............................................................................................................17

Введение в SQL

SQL (Structured Query Language) — это сокращенное название структурированного языка

запросов. Он представляет собой средство создания и обработки данных в реляционных БД.

Независимость от специфики компьютерных технологий, а также поддержка SQL лидерами

промышленности в области технологии реляционных баз данных сделали его основным

стандартным языком БД.

В 1987 году SQL стал стандартом языков для профессиональных реляционных СУБД и

начал внедряться во все распространенные системы. Это связано с рядом следующих

моментов.

1. Постоянный рост быстродействия, а также снижение энергопотребления, размеров и

стоимости компьютеров привели к резкому расширению возможных рынков их сбыта,

крута пользователей, разнообразия типов и цен.

2. Как следствие расширился спрос на разнообразное программное обеспечение.

3. В борьбе за покупателя фирмы, производящие ПО, стали выпускать на рынок все более

интеллектуальные, а значит, объемные программные комплексы.

4. Приобретая их. многие организации и отдельные пользователи часто не могли

разместить их на собственных ЭВМ.

5. Для обмена информацией и ее распространения были созданы сети ЭВМ, где обобщаю-

щие программы и данные стали размещать на специальных файловых серверах.

6. СУБД, работающие с файловыми серверами, позволяют множеству пользователей

разных ЭВМ, расположенных достаточно далеко друг от друга, получать доступ к одним

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

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

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

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

Но! При таком подходе вся обработка запросов из программ или с терминалов

пользовательских ЭВМ на них и выполняется, поэтому для реализации даже простого

запроса необходимо считывать из файлового сервера или записывать на сервер целые

файлы, а это ведет к конфликтным ситуациям и перегрузке сети.

Для исключения указанных недостатков была предложена технология клиент/сервер,

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

Реализация в SQL концепции операций, ориентированных на табличное представление

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

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

В нем существуют:

■ предложения определения данных — определение БД, а также определение и уничтожение

таблиц и индексов;

■ запросы на выбор данных - предложение SELECT;

■ предложения модификации данных - добавление, удаление и изменение данных;

■ предложения управления данными - предоставление и отмена привилегий на доступ к

данным, управление транзакциями и другие.

Кроме того, SQL предоставляет возможность выполнять в этих предложениях:

■ арифметические вычисления, включая разнообразные функциональные преобразования,

обработку текстовых строк и выполнение операций сравнения значений арифметических выражений и

текстов;

■ упорядочение строк или столбцов при выводе содержимого таблиц на печать или экран

дисплея;

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

объема в БД;

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

представления в другой таблице;

■ группирование данных и применение к этим группам таких операций, как среднее, сумма,

максимум, минимум, число элементов и т.п.

Стандарт SQL определяется ANSI (американским национальным институтом стандартов)

и в данное время также принимается ISO (международной организацией по стандартизации).

Язык SQL является основой многих СУБД, т.к. он позволяет принимать SQL-запросы от

других компонентов СУБД и пользовательских приложений.

Режим SQL

Чтобы перейти в режим SQL, нужно открыть любую базу данных или при

создании новой базы данных перейти на закладку Запросы и выполнить команду

СоздатьЗапрос с помощью Конструктора. После этого перейти в режим SQL с

помощью команд главного меню Вид, ЯзыкSQL. Открывается окно для

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

Предложение может начинаться со слов:

CREATE, SELECT, INSERT, UPDATE, DELETE, ALTER, DROP и т.п.

Полное предложение называют конструкцией SQL (или запросом SQL). Оно

состоит из одной или нескольких команд и заканчивается символом «;».

Выполняют конструкцию командой «Выполнить» или нажимают на кнопку

запуска запроса (!) на панели инструментов.

Создание таблиц БД

Для создания новой таблицы в БД следует ввести команду:

CREATE TABLE имя_таблицы (поле1 тип1, поле2 тип2,...);

Имена таблице и полям дает пользователь, В стандарте SQL 92 оно не

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

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

пользователь), необходимо указывать имя владельца. Например, Andrew.sport.

это полное имя таблицы sport, владельцем которой является пользователь

Andrew. Полное имя конкретного поля таблицы включает имя самой таблицы,

например, sport.Fname — поле Fname из таблицы sport.

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

integer целые числовые данные;

char или text(n) текстовые данные, где n длина данного;

real короткие действительные числовые данные;

float длинные действительные числовые данные;

binary(n) логические данные со значениями true, false с указанием длины

данного;

bit логические данные со значениями 1, 0;

counter счетчик;

datetime дата и время;

money денежные данные;

image картинка.

Если имя таблицы или поля содержит пробел или другой разделительный

символ, то его записывают в квадратных скобках, например,

CREATE TABLE Склад1 ( [Номер товара] integer,

[Название товара] text(30),

Цена real,

Количество integer,

Импорт bit,

Картинка image);

Одно из полей можно сделать ключевым, дописав после указания его типа

служебные слова PRIMARY KEY.

Над полем определены такие действия:

ADD—добавить,

DROP—удалить,

ALTER—поменять тип поля.

Чтобы удалить всю таблицу из БД, выполняют команду:

DROP TABLE имя_таблицы;

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

INSERT INTO таблица(поле1, поле2,...)

VALUES (значение1, значение2,...);

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

закладку Таблицы и открыв нужную таблицу для ввода данных.

Модифицировать структуру таблицы можно командой:

ALTER TABLE имя_таблицы действие COLOMN поле тип;

Ограничения данных

Задаваемые ограничения для данных в таблицах БД:

Ограничение Описание

PRIMARY KEY Первичный ключ

NULL /NOT NULL Необязательное Поле / Обязательное

UNIQUE Уникальное поле

FOREING KEY Внешний ключ

Пример создания взаимосвязанных таблиц

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

(создание связей).

CREATE TABLE ПРОЕКТ

(НОМЕРПРОЕКТА INTEGER NOT NULL,

НАЗВАНИЕ CHAR(20) NOT NULL,

ОТДЕЛ VARCHAR(50) NULL,

МАКСТРУДОЗАТРАТЫ NUMERIC,

CONSTRAINT ПроектПК PRIMARY KEY (НОМЕРПРОЕКТА));

(CONSTRAINT- ограничение. Каждому ограничению даётся конкретное собственное имя)

CREATE TABLE СОТРУДНИК

(НОМЕРСОТРУДНИКА INT NOT NULL,

ИМЯ CHAR(20) NOT NULL,

ТЕЛЕФОН CHAR(11),

ОТДЕЛ VARCHAR(50),

CONSTRAINT СотрудникПК PRIMARY KEY (НОМЕРСОТРУДНИКА));

CREATE TABLE НАЗНАЧЕНИЕ

(НОМЕРПРОЕКТА INTEGER NOT NULL,

НОМЕРСОТРУДНИКА INTEGER NOT NULL,

ФАКТЗАТРАТЫ NUMERIC,

CONSTRAINT НазначениеПК PRIMARY KEY (НОМЕРПРОЕКТА, НОМЕРСОТРУДНИКА),

CONSTRAINT СотрудникВК FOREIGN KEY (НОМЕРСОТРУДНИКА)}REFERENCES

СОТРУДНИК (НОМЕРСОТРУДНИКА),

CONSTRAINT ПроектВК FOREIGN KEY (НОМЕРПРОЕКТА) REFERENCES ПРОЕКТ

(НОМЕРПРОЕКТА) );

(REFERENCES — ссылается на.., имеет отношение к…)

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

Выборка данных. Запрос на выборку.

Выборка — это динамический набор данных из базы данных, отображающийся на экране

компьютера.

Выборку записей создают при помощи конструкции языка SQL — SELECT, имеющей

общий вид:

SELECT предикат список полей

FROM таблица {IN адрес внешней БД на диске}

{WHERE условие поиска}

{GROUP BY поле для группировки записей}

{ORDER BY список полей для сортировки);

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

Предикатом может быть:

- служебное слово ALL (показать все найденные записи),

- TOP n — показать первые n записей,

- TOP n PERCENT — показать первые n% записей,

- DISTINCT — показать только различные записи.

Список полей содержит имена полей, отображаемых в выборке.

Элементы списков отделяются запятыми.

Все поля таблицы обозначаются так: таблица.* или * , если рассматривается одна таблица.

Источником данных может быть одна или несколько таблиц текущей или внешней базы

данных.

Имена полей из разных таблиц должны быть дополнены именами таблиц. Между двумя

именами записывают точку, например, Склад1.Цена, Товары.Цена.

Если таблиц несколько, то их объявляют списком в команде FROM.

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

[Номер товара], то команду FROM надо писать так:

FROM таблица1 INNER JOIN таблица2

ON таблица1.[Номер товара]=таблица2.[Номер товара].

Запрос на выборку с условием поиска данных

Команда WHERE является важнейшей в конструкции SELECT. Она служит для отбора

нужныx записей согласно некоторому критерию (условию поиска), условие поиска — это

простое или составное логическое выражение. Простое логическое выражение — это два

выражения, единенные операцией отношения: =, <, >, <=, >=. Отношение не равно»

обозначается о или !=.

Выражения могут содержать имена полей и постоянные личины, соединенные допустимыми

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

логических операций NOT, AND, OR, XOR и других так, как это принято в языках

программирования Visual Basic или Qbasic. Посмотрим примеры условий поиска:

1) [Название товара]="телефон";

2) [Дата изготовления]=#5/21/05#;

3) NOT Продавец="Афоня" или Продавец != "Афоня";

4) Мат > 3 AND Физ > 3 AND Инф > 3;

5) [Цена товара] > 20.5 AND [Цена товара] < 85.

Для последнего примера определена более удобная и более компактная конструкция, если

имя поля длинное: [Цена товара] BETWEEN 20.5 AND 85. Все даты за последние 30 дней: Дата

BETWEEN day() - 30 AND day().

Даты следует записывать в американском формате месяц/ день/год.

Для поиска данных в текстовых полях также полезна операция сравнения Like.

Примеры:

SELECT *

From студенты

where стипендия =100 and имя Like '*я' ;

Функція Like — сравнение текстовых данных.

SELECT *

from студенты

where стипендия =100 and имя Like '*я' or отчество Like 'Васильевич';

Запрос на поиск по конкретной дате.

Показаны:

1. запрос,

2. исходная таблица Успеваемость,

3. результат выполнения запроса.

Результат запроса

Агрегатные функции в запросах

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

помощью агрегатных функций.

Для функций имена полей являются аргументами, поэтому они берутся в скобки.

Например:

SELECT SUM (ОсновнаяЗарплата)

FROM Зарплата;

Пример подсчёта всех строк в таблице:

SELECT COUNT (*)

FROM Зарплата;

В данном запросе будут подсчитаны все строки с нулевыми и дублирующимися записями.

Функция Назначение

Примечание

COUNT

Производит подсчёт кол-ва строк или непустых

значений полей

Числовые или

символьные поля

SUM

Рассчитывает арифметическую сумму выбранных

значений данного поля

Только числовые поля

AVG

Производит подсчёт среднего значения из всех

выбранных значений

Только числовые поля

MAX

Возвращает максимальное значение из всех

значений

Числовые или

символьные поля

MIN

Возвращает минимальное значение из всех

значений

Числовые или

символьные поля

Примеры использования агрегатных функций:

SELECT sum(стипендия)

from студенты;

SELECT avg(стипендия)

from студенты;

SELECT count (стипендия) ( в конкретном поле таблицы)

from студенты;

(считает только непустые поля)

SELECT count (*)

from студенты; ( в таблице вообще)

SELECT max (стипендия-100)

From студенты;

SELECT max (стипендия*2)

from студенты;

SELECT стбил, min (Оценка)

from успеваемость

group by стбил;

выборка минимальных оценок для каждого из студентов (студБилета) из таблицы

УСПЕВАЕМОСТЬ

Параметрические запросы

Параметрические запросы — это такие запросы, которые позволяют указывать

конкретное значение поля, по которому производится поиск, в момент запуска запроса на

исполнение.

При записи такого запроса используются квадратные скобки. В одной конструкции SQL

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

SELECT *

from студенты

where фамилия=[введите фамилию студента];

При запуске такого запроса на исполнение на экране появляется диалоговое окно с

пустой строкой для ввода конкретной фамилии студента.

SELECT *

from студенты

where фамилия=[введите фамилию] and имя= [введите имя];

Использование вычисляемых полей в запросе

SQL позволяет добавить вычисляемые поля непосредственно в запрос. При этом в

таблицах они не отображаются.

SELECT фамилия, имя, отчество,

стипендия*0.2 AS профСбор, стипендия - стипендия*0.2 AS К_выдаче

FROM студенты;

Создаются два новых вычисляемых поля: профСбор и К_выдаче

Группировка полей в запросах

Команда GROUP BY позволяет определить подмножество значений в поле в терминах

другого поля, и применить агрегатную функцию к такому подмножеству. Она может

применяться с агрегатными функциями.

SELECT СтБил, min(оценка)

FROM успеваемость

GROUP BY СтБил;

(Определение наименьшей оценки, полученной каждым студентом)

Обычно команда применятся к одному полю в запросе, хотя допускается её

использование с несколькими полями.

SELECT СтБил, дата, min(оценка)

FROM успеваемость

GROUP BY СтБил, дата;

(группировка сразу по двум полям: сначала сгруппированы записи по номеру студ. билета,

и для каждого студента дополнительно определена минимальная оценка в конкретный

день (дату) ).

Т.е. если студент сдавал несколько зачётов в один день – запрос выведет только дату с

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

При добавлении

вычисляемого поля, ему

можно задать имя. Для

этого используется

служебное слово AS.