МИНИСТЕРСТВО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ

Московский государственный институт электроники и математики

(Технический университет)

Кафедра вычислительных систем и сетей

 

ИЗУЧЕНИЕ ОСНОВ ЯЗЫКА SQL

Методические указания к лабораторным работам по курсу "Базы данных"

И.П. Карпова

 

Содержание

ЦЕЛИ И ЗДАЧИ РАБОТ *

1. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ *

1.1. Общие положения *

1.2. Реляционная модель данных *

1.3. Операции реляционной алгебры *

1.3.1 Основные операции реляционной алгебры *

1.3.2. Вспомогательные операции реляционной алгебры *

1.4. Введение в язык SQL *

1.4.1. Создание отношений *

1.4.2. Команды модификации данных *

1.4.3. Извлечение данных из отношений *

1.4.4. Работа с представлениями *

1.4.5. Удаление объектов базы данных *

1.4.6. NULL-значения *

1.4.7. Подзапросы *

1.4.8. Сложные запросы *

2. ВЫПОЛНЕНИЕ ЛАБОРАТОРНЫХ РАБОТ

3. ВАРИАНТЫ ЗАДАНИЙ К ЛАБОРАТОРНЫМ РАБОТАМ

ЦЕЛИ И ЗАДАЧИ РАБОТ

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

1. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

1.1. Общие положения

SQL (Structured Query Language) – это структурированный язык запросов к реляционным базам данных (БД). SQL является декларативным языком, основанным на операциях реляционной алгебры.

Существуют два стандарта SQL, определённые американским национальным институтом стандартов (ANSI): SQL-89 (SQL-1) и SQL-92 (SQL-2). В настоящее время разрабатывается новый стандарт – SQL-3.

Большинство коммерческих систем управления базами данных (СУБД) поддерживают стандарт SQL-92, который принят ISO (International Standards Organization) в качестве международного стандарта. Многие версии имеют свои отличия, которые касаются, в основном, синтаксиса.

1.2. Реляционная модель данных

Базы данных, основанные на реляционной модели данных (РМД), являются в настоящее время наиболее широко распространёнными вследствие своей простоты и универсальности методов обработки данных.

В основе РМД лежит понятие отношения, представляющего собой подмножество декартова произведения доменов. Домен – это некоторое множество значений, которое может принимать элемент (например, множество целых чисел, множество дат и т.п.).

Пусть D1, D2 ,…, Dk – произвольные конечные и не обязательно различные множества (домены). Декартовым произведением этих множеств

D = D1 ґ D2 ґ ...ґ Dk,

называется множество последовательностей вида

{d1, d2,..., dk},

где d1 О D1, d2 О D2, …, dk О Dk. Т.о., декартово произведение позволяет получить все возможные комбинации элементов исходных множеств.

Пример. Декартово произведение для доменов D1 = (1,2), D2 = (A,B,C):

D = {(1,A),(1,B),(1,C),(2,A),(2,B),(2,C)}.

Элементы отношения называют кортежами, элементы кортежа – атрибутами (полями). Длина кортежа (количество атрибутов) определяет арность отношения, количество кортежей – мощность отношения.

Каждое отношение хранит данные об одном типе объекта (сущности) предметной области, причём один кортеж отношения содержит данные об одном экземпляре объекта данного типа.

Отношение обладает двумя важными свойствами:

  1. В отношении не должно быть одинаковых кортежей, т.к. это множество.

  2. Порядок кортежей в отношении несущественен.

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

домен 1 (ключ) . . . . . . . . . . . . .домен 2 . . . . . . . . домен 3 . . . . . . . . домен 4

№ зачётной книжки

ФИО студента

Группа

Дата рождения

С–12201

Белый Сергей Юрьевич

С–12

12.05.1984

С-12202

Юдина Елена Павловна

С–31

22.03.1982

…

С-14401

Федин Юрий Ильич

С–14

06.11.1984

Рис.1. Пример табличной формы представления отношения

Несколько атрибутов отношения могут быть определены на одном и том же домене (например, год рождения и год смерти). Каждый атрибут принадлежит к определённому типу данных и характеризуется размером памяти, выделяемой под его хранение. Описание совокупности атрибутов отношения с их типами и размерами называется схемой отношения.

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

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

Данные в отношениях обрабатываются с помощью операций реляционной алгебры.

1.3. Операции реляционной алгебры

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

1.3.1 Основные операции реляционной алгебры

  1. Проекция (projection).

    2. Это унарная операция (выполняемая над одним отношением), служащая для выбора подмножества атрибутов из отношения R. Она уменьшает арность отношения и может уменьшить мощность отношения за счёт исключения одинаковых кортежей.

    Пример 1. Пусть имеется отношение R(A,B,C) (рис.2,а).

    Тогда проекция p A,C(R) будет такой, как показано на рис.2,б.

    а) Отношение R

    A

    B

    C

    a

    b

    c

    c

    a

    d

    c

    b

    d

    б) Проекция p A,C(R)

    A

    C

    a

    c

    c

    d

    Рис.2. Пример проекции отношения

  2. Селекция (selection).

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

    Пример 2. Для отношения R(A,B,C) (рис. 3,а) селекция s C=d(R) (при условии "значение атрибута C равно d") будет такой (рис. 3,б):

    а) Отношение R

    A

    B

    C

    a

    b

    c

    c

    a

    d

    c

    b

    d

    б) Селекция s C=d(R)

    A

    B

    C

    c

    a

    d

    c

    b

    d

    Рис.3. Пример селекции отношения

  3. Декартово произведение (Cartesian product).

    4. Это бинарная операция над разносхемными отношениями, соответствующая определению декартова произведения для РМД.

    Пример 3. Пусть имеются отношение R(A,B) и отношение S(C,D,E) (рис.4,а). Тогда декартово произведение Rґ S будет таким (рис.4,б):

    а) Исходные отношения

    A

    B

    a

    b

    c

    a

    b

    d

     

    C

    D

    E

    g

    h

    a

    a

    b

    c

    б) Декартово произведение

    A

    B

    C

    D

    E

    a

    b

    g

    h

    a

    a

    b

    a

    b

    c

    c

    a

    g

    h

    a

    c

    a

    a

    b

    c

    b

    d

    g

    h

    a

    b

    d

    a

    b

    c

    Рис.4. Пример декартова произведения отношений

  4. Объединение (union).

    5. Объединением двух односхемных отношений R и S называется отношение T = R U S, которое включает в себя все кортежи обоих отношений без повторов.

  5. Разность (set difference).

    6. Разностью односхемных отношений R и S называется множество кортежей R, не входящих в S.

    Пример 4. Пусть имеются отношение R(A,B,C) и отношение S(A,B,C) (рис.5,а). Тогда разность R–S будет такой (рис.5,б):

    а) Исходные отношения

    A

    B

    C

    a

    b

    c

    c

    a

    d

    c

    h

    c

     

    A

    B

    C

    g

    h

    a

    a

    b

    c

    h

    d

    d

    б)

    A

    B

    C

    c

    a

    d

    c

    h

    c

    Рис.5. Пример разности отношений

    1.3.2. Вспомогательные операции реляционной алгебры

  6. Пересечение (intersection).

    7. Пересечение двух односхемных отношений R и S есть подмножество кортежей, принадлежащих обоим отношениям. Это можно выразить через разность:

    R ∩ S = R – (R – S).

  7. Соединение (join).

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

    Пример 5. Пусть имеются отношения R(A,B,C) и S(A,D,E) (рис.6,а). Тогда естественное соединение
    R> < S будет таким, как показано на рис.6,б.

    а) Исходные отношения

    A

    B

    C

    a

    b

    c

    c

    a

    d

    c

    h

    c

    g

    b

    d

    A

    D

    E

    g

    h

    a

    c

    b

    c

    h

    d

    d

    б) Соединение отношений

    A

    B

    C

    D

    E

    c

    a

    d

    b

    c

    c

    h

    c

    b

    c

    g

    b

    d

    h

    a

    Рис.6. Пример естественного соединения отношений

  8. Деление (division).

Пусть отношение R содержит атрибуты {r1,r2,...,ri,...,rn}, а отношение S – атрибуты {r1,r2,...,ri}. Результирующее отношение содержит атрибуты {ri+1,...,rn}. Кортеж включается в результирующее отношение, если его декартово произведение с отношением S входит в R.

Пример 6. Пусть имеются отношения R(A,B,C) и S(A,B) (рис. 7,а). Тогда частное R/S будет таким как показано на рис. 7,б.

а) Исходные отношения

A

B

C

D

c

b

c

f

b

a

c

f

g

h

a

c

b

a

a

c

c

b

b

c

b

a

b

c

A

B

c

b

b

a

б) Частное

C

D

c

f

b

c

Рис.7. Пример операции деления

 

1.4. Введение в язык SQL

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

Для реализации этих функций SQL включает три группы средств:

По стандарту ANSI DCL является частью DDL.

Синтаксис команд и примеры, рассмотренные в данном пособии, соответствуют синтаксису СУБД Sybase.

В командах SQL не различаются прописные и строчные буквы (за исключением строчных литералов). Каждая команда заканчивается символом ';'. Значения параметров по умолчанию выделено подчеркиванием, например, ALL.

Примем следующие обозначения для описания синтаксиса:

{} – содержимое скобок рассматривается как единое целое для остальных символов;

| – заменяет слово ИЛИ;

[] – содержимое этих скобок является необязательным;

… – всё, что предшествует этим символам, может повторяться произвольное число раз;

.,.. – всё, что предшествует этим символам, может повторяться произвольное число раз, каждое вхождение отделяется запятой.

1.4.1. Создание отношений

Создание нового отношения (таблицы) выполняется с помощью команды DDL CREATE TABLE. Команда CREATE TABLE используется для описания новой таблицы, её атрибутов (полей) и ограничений целостности. Упрощённый синтаксис этой команды:

CREATE TABLE <имя таблицы>
( {<имя поля> <тип данных> [(<размер>)]
[<ограничения целостности поля>…]} .,..
[, <ограничения целостности таблицы>.,..] );

Расшифровка элементов описания приведена в табл. 1.

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

Таблица 1. Описание команды CREATE TABLE

Элемент

Описание

<имя поля>

Имя поля (столбца) таблицы, обычный идентификатор.

<тип данных>

Тип данных поля. Можно использовать одно из значений:

– INTEGER, INT, SMALLINT – целые числа;

– NUMERIC[(длина [, точность])], DECIMAL[(длина [, точность])] – числа с фиксированной запятой;

– FLOAT, REAL, DOUBLE – вещественные числа;

– CHAR[(длина)], VARCHAR(длина) – символьные строки фиксированной и переменной длины;

– DATE – дата; TIME – время.

<размер>

Размер поля в символах (для текста и чисел).

<ограничения целостности>

Можно использовать следующие ограничения:

– PRIMARY KEY – первичный ключ (обязательный и уникальный);

– UNIQUE – уникальное значение поля в пределах столбца таблицы;

– [NOT] NULL – [не] возможность не указывать значение поля;

– CHECK (<условие>) – проверка условия для поля (полей);

– DEFAULT <выражение> – задание значения поля по умолчанию;

– REFERENCES <имя таблицы> [(<имя столбца>)] – внешний ключ.

<ограничения целостности таблицы>

То же, что и для поля. Дополнительно используется:

FOREIGN KEY [(<список полей>.,..)] REFERENCES
<имя таблицы> [(<список полей>)] – внешний ключ.

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

  1. Таблица "Отделы" с полями "Номер отдела" (ПК), "Название отдела":

    2. create table depart
    ( depno numeric(2) primary key,
    name varchar(30) not null);

  2. Таблица "Сотрудники" с полями "Номер отдела" (внешний ключ), "Табельный номер сотрудника" (ПК), "ФИО сотрудника", "Должность", "Оклад", "Дата рождения", "Телефон":

    3. create table emp
    ( depno numeric(2) references depart,
    tabno char(3) primary key,
    name varchar(40) not null,
    post varchar(20) not null,
    salary numeric(7,2) not null,
    born date not null,
    tel char(11));

  3. Таблица "Дети сотрудников" с полями "Табельный номер родителя" (внешний ключ), "Имя ребенка", "Пол", "Дата рождения":

create table children
( tabno char(3) references emp(tabno),
name varchar(20) not null,
gender char(1),
born date,
primary key(tabno, name), /* составной первичный ключ*/
check (gender in (‘м’, ‘ж’)));

Обратите внимание:

create table tab
( id numeric(6) primary key,
class numeric(3),
fdate date,
group char(6),
foreign key (class, fdate) references exam(class, fdate));

1.4.2. Команды модификации данных

К командам модификации данных (DML) относятся добавление, удаление и изменение (обновление) кортежа (записи).

INSERT – добавление записи в таблицу. Синтаксис:

INSERT INTO <имя таблицы> [(<имя поля>.,..)]
VALUES (<список выражений>) | <запрос>;

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

В предложении VALUES указываются выражения, порождающие значения атрибутов новой записи таблицы. Типы значений выражений должны соответствовать типам полей таблицы. Если значения устанавливаются не для всех полей или порядок значений не соответствует тому порядку полей, который был установлен при создании таблицы, то после имени таблицы в скобках приводится список полей в соответствии со списком значений. Если в списке полей не указано обязательное поле таблицы (not null), то ему будет присвоено значение по умолчанию (default), если оно определено в командах CREATE TABLE или ALTER TABLE. (Подробнее о NULL-значениях см. п. 1.4.6).

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

Пример: Добавить в таблицу "Сотрудники" новую запись:

insert into emp

values(3, '112', 'Попов В.Г.', 'экономист', 400*13.5, '1979–12–23', '5–34–11');

UPDATE – обновление данных в таблице. Синтаксис:

UPDATE <имя таблицы>
SET {<имя поля> = <выражение>}.,..
[WHERE <условие>];

Запрос на обновление изменяет в указанной таблице значения указанных полей тех записей, которые удовлетворяют заданному условию отбора (where <условие>). Если условие не указано, обновляются все записи таблицы.

Пример: Изменить должность и зарплату сотрудника Попова В.Г., табельный номер 112:

update emp
set post = 'ст. экономист', salary = salary+1000
where tabno = '112';

DELETE – удаление записей из таблицы. Синтаксис этой команды:

delete from <имя таблицы> [ where <условие> ];

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

Пример: Удалить запись о сотруднике Попове В.Г., табельный номер 112:

delete from emp where tabno = '112';

1.4.3. Извлечение данных из отношений

Извлечение данных из отношений выполняется с помощью команды SELECT (селекция). Эта команда не изменяет данные в БД.

Результатом выполнения команды SELECT является временное отношение, которое помещается в курсор (специальную область памяти СУБД) и обычно сразу выводится на экран. Синтаксис этой команды:

SELECT * | { [ ALL | DISTINCT ] <список выбора>.,..}
FROM {<имя таблицы> [<алиас>] }.,..
[ WHERE <условие>]
[ GROUP BY {<имя поля> | <целое>}.,.. [ HAVING <условие>] ]
[ ORDER BY {<имя поля> | <целое> [ ASC | DESC ] }.,..]
[ UNION [ALL] SELECT …];

Расшифровка элементов описания приведена в табл. 2.

Таблица 2. Элементы команды SELECT

Элемент

Описание

<список выбора>

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

<имя таблицы>

Имя или синоним имени таблицы или представления.

<алиас>

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

<условие>

Условие, которое может быть истинным или ложным для каждого поля или комбинации полей из таблицы (таблиц), определённых предложением FROM.

<имя поля>

Имя поля (столбца) таблицы.

<целое>

Число без десятичной точки. Номер поля в <списке полей>.

DISTINCT – предикат удаления из результирующего отношения повторяющихся кортежей.

ALL – предикат, обратный к DISTINCT (используется по умолчанию).

Рассмотрим основные предложения команды SELECT:

SELECT – после этого ключевого слова указывается список выбора – список выражений, которые будут образовывать результирующее отношение. Выражению можно сопоставить временный синоним (алиас), который будет названием поля результирующего отношения, например:

sal*0.87+bonus as salary

Если надо вывести все поля из тех отношений, к которым обращается данный запрос, можно указать символ * (если в отношениях нет полей с одинаковыми именами). В этом случае сначала будут выведены поля таблицы, стоящей первой в предложении FROM, затем – второй и т.д. Поля, относящиеся к одной таблице, будут выводиться в том порядке, в каком они были записаны при создании таблицы.

FROM – в этом предложении указывается имя таблицы (имена таблиц), в которой будет производиться поиск.

WHERE – содержит условия выбора отдельных записей.

GROUP BY – группирует записи по значению одного или нескольких полей. Каждой группе в результирующем отношении соответствует одна запись.

HAVING – позволяет указать условия выбора для групп записей. Может использоваться только после group by.

ORDER BY – упорядочивает результирующие записи по значению одного или нескольких полей: ASC – по возрастанию, DESC – по убыванию.

Порядок выполнения операции SELECT такой:

  1. Выбор из указанной таблицы тех записей, которые удовлетворяют условию отбора (where).
  2. Группировка полученных записей (group by).
  3. Выбор тех групп, которые удовлетворяют условию отбора (having).
  4. Сортировка записей в указанном порядке (order by).
  5. Извлечение из записей полей, заданных в списке выбора, и формирование результирующего отношения.

Если во фразе FROM указаны две и более таблицы, то эта последовательность действий выполняется для декартова произведения указанных таблиц.

Отношения для примеров приведены в таблицах 3-5.

Таблица 3. Отношение "Сотрудники" (Emp)

TabNo

DepNo

Name

Post

Salary

Born

Tel

988

1

Рюмин В.П.

начальник отдела

4850.0

01.02.60

5-26-12

909

1

Серова Т.В.

вед. программист

4850.0

20.10.71

5-91-19

100

2

Волков Л.Д.

программист

4650.0

16.10.72

null

034

3

Петрова К.В.

секретарь

3200.4

24.04.58

null

110

2

Буров Г.О.

бухгалтер

4588.5

22.05.65

5-46-32

023

2

Малова Л.А.

гл. бухгалтер

4924.0

24.11.54

4-24-55

002

3

Сухов К.А.

начальник отдела

4850.0

18.06.48

5-12-69

Таблица 4. Отношение "Отделы" (Depart)

DepNo

Name

2

Бухгалтерия

3

Отдел кадров

4

Отдел технического контроля

1

Плановый отдел

Таблица 5.Отношение "Дети"(Children)

TabNo

Name

Born

Gender

988

Вадим

03.05.85

м

110

Ольга

18.07.91

ж

023

Илья

19.02.77

м

023

Анна

26.12.79

ж

909

Инна

25.01.99

ж

Примеры:

  1. Выбрать все записи из таблицы "Отделы":

    2. select * from depart;

    DepNo

    Name

    2

    Бухгалтерия

    3

    Отдел кадров

    4

    Отдел технического контроля

    1

    Плановый отдел

  2. Вывести список сотрудников по отделам с указанием должности:

select depno, name, post
from emp
order by depno, name;

DepNo

Name

Post

1

Рюмин В.П.

начальник отдела

1

Серова Т.В.

вед. программист

2

Буров Г.О.

бухгалтер

2

Волков Л.Д.

программист

2

Малова Л.А.

гл. бухгалтер

3

Петрова К.В.

секретарь

3

Сухов К.А.

начальник отдела

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

Пример: Запрос по двум таблицам. Список сотрудников с детьми:

select e.name, c.name child, c.born
from emp e, children c /* e, c – алиасы */
where e.tabno = c.tabno /* условие соединения */
order by e.name, c.born;

Name

Child

Born

Буров Г.О.

Ольга

18.07.91

Малова Л.А.

Илья

19.02.77

Малова Л.А.

Анна

26.12.79

Рюмин В.П.

Вадим

03.05.85

Серова Т.В.

Инна

25.01.99

Расширение возможностей команды SELECT достигается за счёт применения различных операторов, предикатов и функций.

Операторы:

Пример: Составить список сотрудников второго и третьего отдела, имеющих оклады выше 4600 рублей:

select depno, name, salary
from emp
where salary>4600 and (depno=2 or depno=3)
order by name;

DepNo

Name

Salary

2

Волков Л.Д.

4650.0

2

Малова Л.А.

4924.0

3

Сухов К.А.

4850.0

Предикаты, используемые в запросах:

field IN (список значений)

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

field BETWEEN значение1 AND значение2

– определяет, входит ли значение поля field в указанные границы. Если значение поля меньше, чем значение1, или больше, чем значение2, предикат возвращает "ложь".

field LIKE 'образец'

– используется для поиска подстрок, применяется только в полям типа CHAR, VARCHAR. Возможно использование шаблонов: '_' – один любой символ и '%' – произвольное количество символов (в т.ч., ни одного);

field IS [NOT] NULL

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

Примеры:

  1. Список программистов и ведущих программистов:

    2. select depno, name, post from emp
    where post like ('%программист%');

    DepNo

    Name

    Post

    1

    Серова Т.В.

    вед. программист

    2

    Волков Л.Д.

    программист

  2. Список сотрудников старше 40 лет из 1-го и 3-го отделов:

    3. select depno, name from emp
    where depno in (1, 3) and
    year(getdate()) – year(born) > 40;     /*(текущий год) – (год рождения)*/

    DepNo

    Name

    1

    Рюмин В.П.

    3

    Петрова К.В.

    3

    Сухов К.А.

  3. Список сотрудников, не имеющих телефонов:

select tabno, name, post
from emp
where tel is null;

TabNo

Name

Post

100

Волков Л.Д.

программист

034

Петрова К.В.

секретарь

Функции агрегирования:

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

SUM (distinct <поле>) – суммирование различных значений поля;

AVG (distinct <поле>) – среднее арифметическое разных значений поля;

COUNT (distinct <поле>) – подсчёт количества разных значений поля;

COUNT (<поле>) – подсчёт количества ненулевых значений поля;

COUNT (*) – подсчёт количества строк в результате.

Примеры:

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

    2. select depno, count(*), ‘ сотрудник(а)’
    from emp
    group by depno;

    DepNo

    Count(*)

    сотрудник(а)

    1

    2

    сотрудник(а)

    2

    3

    сотрудник(а)

    3

    2

    сотрудник(а)

  2. Сумма зарплаты по отделам:

select depno, sum(salary) as sal
from emp
group by depno;

DepNo

Общая сумма

1

9700.0

2

14162.5

3

8050.4

Предложение UNION позволяет объединять результаты нескольких запросов SELECT для реализации соответствующей операции реляционной алгебры. Результаты этих запросов должны быть построены по одной схеме. Предложение ORDER BY может встречаться в таком запросе один раз – в конце последнего предложения SELECT.

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

select depno, count(name), ‘ сотрудник(а)’
from emp
group by depno
union
select depno, 0, ‘ сотрудников’
from depart
where depno not in (select distinct depno from emp)

order by 1; /* упорядочение по первому столбцу */

DepNo

Count(name)

сотрудник(а)

1

2

сотрудник(а)

2

3

сотрудник(а)

3

2

сотрудник(а)

4

0

сотрудников

1.4.4. Работа с представлениями

Представление (view, обзор) – это хранимый запрос, создаваемый на основе команды SELECT. Представление реально не содержит данных. Запрос, определяющий представление, выполняется тогда, когда к представлению происходит обращение с другим запросом, например, SELECT, UPDATE и т.д.

Создание представления выполняется командой CREATE VIEW:

CREATE VIEW <имя представления> [(<имя столбца>.,..)]
AS <запрос>;

Запрос, на основании которого создаётся представление, называется определяющим запросом, а таблицы, к которым происходит обращение в определяющем запросе – базовыми таблицами. Определяющий запрос не может включать предложение ORDER BY.

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

Пример: Создание представления "Сотрудники с детьми":

create view emp_child(name, child, born)
as select e.name, c.name, c.born
from emp e, children c
where e.tabno = c.tabno;

select * from emp_child order by name, born;

Name

Child

Born

Буров Г.О.

Ольга

18.07.91

Малова Л.А.

Илья

19.02.77

Малова Л.А.

Анна

26.12.79

Рюмин В.П.

Вадим

03.05.85

Серова Т.В.

Инна

25.01.99

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

Пример: Создание представления "Сотрудники 2-го отдела":

create view emp2
as select tabno, name, post, salary, born
from emp where depno = 2;

select * from emp2 order by name;

TabNo

Name

Post

Salary

Born

110

Буров Г.О.

бухгалтер

4588.5

22.05.65

100

Волков Л.Д.

программист

4650.0

16.10.72

023

Малова Л.А.

гл. бухгалтер

4924.0

24.11.54

Обновление базового отношения через представление:

update emp2 set salary = 4800
where tabno = 100;

select * from emp2 order by name;

TabNo

Name

Post

Salary

Born

110

Буров Г.О.

бухгалтер

4588.5

22.05.65

100

Волков Л.Д.

программист

4800.0

16.10.72

023

Малова Л.А.

гл. бухгалтер

4924.0

24.11.54

Изменения будут произведены в базовой таблице и отразятся в представлении. По стандарту SQL-2 представление не является обновляемым, если определяющий запрос:

  1. содержит ключевое слово DISTINCT;
  2. ссылается на другое необновляемое представление;
  3. содержит вычисляемые выражения в списке выбора;
  4. выбирает данные более чем из одной таблицы;
  5. содержит предложение GROUP BY.

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

1.4.5. Удаление объектов базы данных

Удаление объектов БД выполняется с помощью команды DROP.

DROP TABLE <имя таблицы> [RESTRICT | CASCADE];

Таблица будет удалена без дополнительного запроса на подтверждение. При указании CASCADE вместе с таблицей каскадно удаляются все зависящие от неё объекты БД. Если указать RESTRICT, то при наличии зависимых от удаляемой таблицы объектов операция будет отменена.

DROP VIEW <имя представления>;

1.4.6. NULL-значения

Стандарт SQL включает понятие неопределённого значения – NULL-значения. В тех случаях, когда при добавлении записи значение какого-либо поля неизвестно, его можно не устанавливать, пропустив это поле в списке полей или указав для него значение NULL (но только для тех полей, на которые не наложено ограничение целостности NOT NULL).

Значение NULL не сравнимо ни с каким другим значением, даже со значением NULL. Тем не менее, предложение GROUP BY объединяет все NULL-значения в одну группу, DISTINCT оставляет только одно NULL-значение, а функция AVG не учитывает NULL-значения, и сумма значений поля делится на количество ненулевых значений.

1.4.7. Подзапросы

Подзапросы можно разделить на следующие группы в зависимости от возвращаемых результатов:

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

Рассмотрим операторы, которыми модифицируются операторы сравнения:

> ALL (< ALL) – больше (меньше) каждого значения элементов результирующего множества.

= ANY – равно одному из значений элементов результирующего множества (эквивалентно использованию предиката IN).

> ANY (< ANY) – больше (меньше) любого значения элементов результирующего множества.

Если список, модифицированный оператором ALL, содержит NULL-значение, то результирующий запрос будет пуст, т.к. нельзя сравнить никакое значение с NULL-значением.

Выражение <>ANY(…) не эквивалентно NOT IN: оно выполняется всегда, кроме случаев NULL-значений.

Примеры:

  1. Выдать список бездетных сотрудников:

    2. select * from emp e
    where not exists (select * from children c where e.tabno=c.tabno);

    DepNo

    TabNo

    Name

    Post

    Salary

    Born

    2

    100

    Волков Л.Д.

    программист

    4650.0

    16.10.72

    3

    034

    Петрова К.В.

    секретарь

    3200.4

    24.04.58

    3

    002

    Сухов К.А.

    начальник отдела

    4850.0

    18.06.48

  2. Выдать список сотрудников, имеющих детей:

    3. select * from emp
    where tabno in (select distinct tabno from children);

    TabNo

    DepNo

    Name

    Post

    Salary

    Born

    988

    1

    Рюмин В.П.

    начальник отдела

    4850.0

    01.02.60

    909

    1

    Серова Т.В.

    вед. программист

    4850.0

    20.10.71

    110

    2

    Буров Г.О.

    бухгалтер

    4588.5

    22.05.65

    023

    2

    Малова Л.А.

    гл. бухгалтер

    4924.0

    24.11.54
  3. Список сотрудников, оклад которых меньше среднего на предприятии:

select depno, name, salary
from emp
where salary < ANY(select avg(salary) from emp);

DepNo

Name

Post

Salary

3

Петрова К.В.

секретарь

3200.4

1.4.8. Сложные запросы

Существуют варианты запросов, которые в принципе не могут быть реализованы в рамках одного запроса. Например, составление ведомости на получение зарплаты при условии, что подоходным налогом (13%) не облагается один минимальный оклад (300 рублей) на каждого ребенка. Для этого запроса необходима информация из двух отношений ("Сотрудники" и "Дети"), причём для определения количества детей необходимо использовать агрегирующую функцию.

create view nc(tabno, num) as
select tabno, count(tabno)
from children
group by tabno;

select distinct emp.name, salary*0.87+(300*0.13)*num as sal
from emp, nc
where emp.tabno = nc.tabno
union
select name, salary*0.87
from emp
where tabno not in (select distinct tabno from children);

Name

Sal

Буров Г.О.

4030.00

Волков Л.Д.

4176.00

Малова Л.А.

4361.88

Петрова К.В.

2784.35

Рюмин В.П.

4258.50

Серова Т.В.

4258.50

Сухов К.А.

4219.50

В заключение приведём пример ещё одного сложного типа запроса – создание на основании таблиц "Сотрудники" и "Дети" следующего отчёта:

ФИО сотрудника

Количество дочерей

Количество сыновей

…

…

…

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

create view v1 (tabno, fnum, mnum)
as select tabno, count(*), 0
from children
where gender='ж' /*количество дочерей*/
group by tabno;

create view v2 (tabno, fnum, mnum)
as select tabno, 0, count(*)
from children
where gender='м' /*количество сыновей*/
group by tabno;

create view child (name, girls, boys)
as /* сотрудники, имеющие и сыновей, и дочерей */
select name, v1.fnum, v2.mnum
from emp e, v1, v2
where e.tabno=v1.tabno and e.tabno=v2.tabno
union /* сотрудники, имеющие только дочерей */
select name, v1.fnum, 0
from emp e, v1
where e.tabno=v1.tabno and e.tabno not in (select tabno from v2)
union /* сотрудники, имеющие только сыновей */
select name, 0, v2.mnum
from emp e, v2
where e.tabno=v2.tabno and e.tabno not in
(select tabno from v1);

/*выполнение запроса*/

select * from child order by name;

Name

girls

Boys

Буров Г.О.

1

0

Малова Л.А.

1

1

Рюмин В.П.

0

1

Серова Т.В.

1

0

Часть II