Понятие данных, принципы построения информационных систем

Таблица 2.7. Сокращения названий месяцев

Название месяца	Используемое  обозначение
Январь	JAN
Февраль	FEB
Март	MAR
Апрель	APR
Май	MAY
Июнь	JUN
Июль	JUL
Август	AUG
Сентябрь	SEP
Октябрь	OCT
Ноябрь	NOV
Декабрь	DEC

 

 
Основные операторы языка SQL. 
основные операторы SQL

CREATE DATABASE

Создает новую базу данных:

CREATE TABLE

Создает новую таблицу в выбранной  базе данных.

DESCRIBE

Показывает структуру созданных  таблиц:

ALTER TABLE

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

DROP TABLE

Удаляет одну или несколько таблиц:

DROP DATABASE

Удаляет базу данных со всеми таблицами, входящими в ее состав:

INSERT INTO... VALUES

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

DELETE

Удаляет из таблицы table_name записи, удовлетворяющие  заданным в definition условиям, и возвращает число удаленных записей.

SELECT

Извлекает данные из одной или нескольких таблиц и имеет синтаксис:

UPDATE

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

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

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

SHOW

Оператор SHOW может принимать множество  форм и предназначен для мониторинга  таблиц, баз данных и сервера MySQL.

При помощи оператора SHOW CHARACTER SET можно  выяснить кодировки, поддерживаемые сервером MySQL.

Оператор SHOW COLUMN TYPES позволяет вывести  перечень информации о типах столбцов, которые использовались при создании таблиц MySQL.

При помощи оператора SHOW FULL COLUMNS FROM authors можно получить расширенное описание структуры таблицы.

Оператор SHOW PROCESSLIST позволяет получить информацию о потоках, выполняющихся  на сервере.

Оператор SHOW STATUS позволяет получить значения переменных состояния сервера.

 
Определение столбца, его свойства. 
Столбцы таблицы называются полями. Поле БД — это столбец таблицы, содержащий значения определенного свойства. Значения полей в одной строчке относятся к одному объекту. Разные поля отличаются именами.

В реляционной БД используются четыре основных типа полей:

• Числовой (в качестве значений полей используются только цифры);
• Символьный (слова, тексты, коды);
• Дата (календарные даты в форме «день/месяц/год»);
• Логический (принимает два значения: «истина» или «ложь»).

 
Создание базы данных, ограничение  целостности БД. Индексы. 
 
Структура запросов. 
 
Основные операции над таблицами. 
 
Команды манипулирования данными: INSERT, DELET, UPDATE.

 Команда INSERT

Вариант 1 - вставка  одной строки

Команда имеет вид: INSERT INTO имятаблицы [(списокименстолбцов)] VALUES (список значений)

Вариант 2 - вставка  множества строк на основе запроса  по другим таблицам

Команда имет вид: INSERT INTO имя таблицы [(список столбцов)] SELECT ... (запрос на выборку из других таблиц)

. Команда DELETE

Общий вид  команды: DELETE FROM имя таблицы [WHERE условиеотборастрокдляудаления]

Например:

Удалить всех студентов с фамилией Иванов DELETE FROM students WHERE name_st='Иванов'

Полностью очистить таблицу students

DELETE FROM students

Нельзя удалить строку родительской таблицы, если есть связанные строки в подчиненной таблице и действует  правило обеспечения ссылочной целостности ON DELETE RESTRICT.

Команда UPDATE

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

Общий вид  команды: UPDATE имятаблицы SET столбец1=значение [, столбец2=значение

[WHERE условиеотборастрок]

Например;

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

 
Команда выборки данных SELECT, сортировка данных.

 Команда выборки данных (SELECT)

Команда выборки SELECT

SELECT [DISTINCT] список_выражений(* - все столбцы таблицы) FROM список_таблиц, представлений, запросов (возможно, с операцией соединения JOIN) [WHERE условияотборастрок] [GROUP BY список_выражений_для_группировки] [HAVING условия_отбора_групп_с_агрегатными_функциями] [ORDER BY списоквыраженийдлясортировки]

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

Запросы на выборку по одной таблице

Выборка всех данных таблицы

SELECT * FROM имятаблицы (представления)

Например: выбрать все данные из таблицы students SELECT * FROM students

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

Например: выбрать столбцы с фамилией и датой рождения из таблицы студентов SELECT namest, born FROM students выбрать только столбец с фамилией из таблицы студентов SELECT namest FROM students

Результаты выборки  по этим запросам могут не удовлетворить  пользователей по двум причинам.

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

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

Отбор строк (операция выборки)

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

Условие отбора строк может включать следующие  операции:

• операции сравнения <,>,<=,>=, !=(<>), =
• операция проверки на отсутствие данных IS NULL

или их наличие IS NOT NULL

• логические операции: AND, OR, NOT
• операция вхождения значения в заданный диапазон значений

BETWEEN начальное значение AND конечное значение

• операция принадлежности значения заданному множеству

IN (множество)

• операция соответствия заданному шаблону (для текстовых столбцов)

LIKE 'шаблон'

В шаблоне для операции LIKE разрешено использовать два символа-заменителя:

• % заменяет последовательность из любого количества любых символов (в том числе и пустая последовательность)
• _ заменяет один любой символ в заданной позиции.

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

В стандарте определено 5 агрегатных функций:

SUM(имя_столбца) - сумма значений заданного столбца,

AVG(имястолбца) - среднее значение

MIN(имя_столбца), МАХ(имястолбца)- минимальное и максимальное значение

COUNT([DISTINCT] * или имястолбца) - подсчет количества строк.

 
Вложенные и комбинированные запросы. 
Вложенные запросы

Это запросы, которые содержатся в теле другого запроса. Они могут использоваться во фразах FROM, WHERE и HAVING, а также в списке после слова SELECT, создавая, таким образом, вычисляемый столбец.

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

• тело вложенного запроса всегда заключается в скобки
• вложенные запросы могут содержать другие вложенные запросы, при этом выполнение запроса всегда начинается с самого «глубокого» вложенного запроса и заканчивается внешним запросом
• во вложенном запросе не следует использовать фразу ORDER BY, поскольку сортировка результатов должна быть выполнена один раз после выполнения всего запроса целиком

Вложенные запросы во фразе FROM

конструкция SELECT .... FROM (SELECT ... ) полностью равнозначно SELECT .... FROM имятаблицы

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

SELECT MIN (avgmark) minavg mark, MAX(avgmark) maxavgmark FROM (SELECT AVG (mark) avg_mark FROM marks GROUP BY cod_st)

Вложенные запросы как альтернатива соединению таблиц в запросах

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

Например, пусть требуется выяснить оценку студента Иванова по математике (если таких студентов несколько - все оценки). Можно легко решить эту задачу, соединив таблицы students, subjects и marks и выполнив выборку из полученной в результате соединения таблицы. Однако, логичным представляется и такой вариант: найти коды всех Ивановых из таблицы students, найти код математики из таблицы subjects, на последнем этапе найти оценку (или несколько оценок) в таблице marks. Получаем такой текст запроса с двумя вложенными запросами: SELECT mark FROM marks WHERE cod_st IN

(SELECT codst FROM students WHERE name_st='HBaHOB') AND codsub IN (SELECT codsub FROM subjects WHERE name_sub='Математика')

Вложенные запросы в условиях отбора (WHERE и HAVING)

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

Пусть требуется вывести фамилии  самых молодых студентов (т.е. тех, у которых самая поздняя дата рождения). Этот запрос состоит из двух этапов: сначала нужно найти самую  позднюю дату рождения (эту задачу решит вложенный запрос), а затем всех студентов, которые имеют найденную дату рождения. SELECT namest FROM students WHERE born= (SELECT MAX(born) FROM students)

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

запрос  также корректно работает, возвращая  сообщение «строки не выбраны»).

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

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

В результате получили «многоэтажный», но довольно простой по логике, запрос:

SELECT namest FROM students WHERE codst IN (SELECT cod_st FROM marks GROUP BY cod_st HAVING AVG(mark)= (SELECT MAX(avgmark) maxavgmark FROM

(SELECT AVG (mark) avg_mark FROM marks GROUP BY cod_st

) ) )

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

Такое использование  вложенных запросов приводит к очень  интересной конструкции SELECT ... (SELECT ...) FROM ... Она поддерживается большинством СУБД.

В качестве примера приведем задачу вычисления средних баллов всех студентов, для  которой уже было представлено несколько  вариантов запросов. Приведем еще один, не самый эффективный, но правильный. SELECT codst, namest,

(SELECT AVG(mark) FROM marks WHERE cod_st=students.cod_st) avgmark FROM students

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

В последнем приведенном примере  можно обратить внимание на одно важное обстоятельство. Вложенный запрос, использующий только одну таблицу marks, тем не менее, содержит во фразе WHERE столбец из другой таблицы students.mark.

Комбинированные запросы

Над результатами запросов можно выполнять обычные операции над множествами.

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

Стандартом установлены следующие  обозначения для операций: UNION [ALL]-объединение запросов INTERSECT [ALL] -пересечение запросов ЕСХЕРТ (MTNUS в Oracle) [ALL] - разность запросов

Ключевое  слово ALL в данном контексте запрещает удаление дубликатов из результатов операций (тем самым повышается эффективность, поскольку удаление дубликатов - достаточно медленная операция). SELECT namest FROM students 1