Веб-безопасность/Базы данных SQL

Теория

База данных (БД) - представленная в объективной форме совокупность самостоятельных материалов (статей, расчётов, нормативных актов, судебных решений и иных подобных материалов), систематизированных таким образом, чтобы эти материалы могли быть найдены и обработаны с помощью ЭВМ.

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

Функции СУБД

1. управление данными во внешней памяти (на дисках);
2. управление данными в оперативной памяти с использованием дискового кэша;
3. журнализация изменений, резервное копирование и восстановление базы данных после сбоев;
4. поддержка языков БД

Понятия

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

Домен В общем виде домен определяется путем задания некоторого базового типа данных, к которому относятся элементы домена, и произвольного логического выражения, применяемого к элементу этого типа данных (ограничения домена). Элемент данных является элементом домена в том и только в том случае, если вычисление этого логического выражения дает результат истина (для логических значений мы будем попеременно использовать обозначения истина и ложь или true и false). С каждым доменом связывается имя, уникальное среди имен всех доменов соответствующей базы данных. Наиболее правильной интуитивной трактовкой понятия домена является его восприятие как допустимого потенциального, ограниченного подмножества значений данного типа. Данные считаются сравнимыми только в том случае, когда они относятся к одному домену.

Итак, заголовком (или схемой) отношения r (Hr) называется конечное множество упорядоченных пар вида <A, T>, где A называется именем атрибута, а T обозначает имя некоторого базового типа или ранее определенного домена. По определению требуется, чтобы все имена атрибутов в заголовке отношения были различны.

Кортежем tr, соответствующим заголовку Hr, называется множество упорядоченных триплетов вида <A, T, v>, по одному такому триплету для каждого атрибута в Hr. Третий элемент – v – триплета <A, T, v> должен являться допустимым значением типа данных или домена T.

Телом Br отношения r называется произвольное множество кортежей tr.

Значением Vr отношения r называется пара множеств Hr и Br. В изменчивой реляционной базе данных хранятся отношения, значения которых изменяются во времени. Переменной VARr называется именованный контейнер, который может содержать любое допустимое значение Vr. Естественно, что при определении любой VARr требуется указывать соответствующий заголовок отношения Hr.

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

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

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

1. характеризует кортеж
2. уникальный
3. не избыточный

Метаданные

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

Основные операторы SQL

Рассмотрим некоторые операторы SQL и примеры их применения.

Допустим, имеются три таблицы: characters с полями (id, name, race, level, class), classes с полями (class_type, strength, dexterity, constitution, intelligence, wisdom, charisma) и monsters с полями (name, level), хранящиеся в базе данных heroes.

База данных

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

 show databases();

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

 use heroes;

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

 show tables;

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

 select database();
 select user();

Для создания БД и таблиц в ней используются файлы инициализации (init-db.sql), написанные на языке sql запросов.

 source init-db.sql;

Аналогичный эффект будет получен, если напрямую вводить команды из файла в терминал.
Note: если init-db.sql находится не в текущей папке, то следует прописать к нему путь.

Для получения описания таблицы, следует перейти в соответствующую БД и вызвать

 describe classes;

Таблицы

Теперь обратим внимание на таблицы.

Сначала посмотрим все записи в таблице characters.

 select * from characters;

Если же требуется вывести только определенные колонки (например name и race), то запрос будет таким.

 select name, race from characters;

Для вывода классов с харизмой выше 7 следует сформировать следующий запрос.

 select * from classes where wisdom > 15;

А для вывода все персонажей класса Bard понадобится такой запрос:

 select * from characters where class='Bard';

Далее выведем число персонажей каждого класса.

 select class, count(class) as num_of_class from characters group by class;

Если же нужно вывести персонажей с уникальным классом, то к предыдущему запросу следует добавить условие с помощью having.

 select class, count(class) as num_of_class from characters group by class having num_of_class = 1;

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

 select * from classes order by  level [asc|desc];

Для вывода первых нескольких(5) записей таблицы используется запрос

 select * from classes limit 5;

Если хотим вывести список персонажей со всеми их характеристиками, то сформируем следующий запрос. В нем таблицы characters и classes объединяются по колонкам class и class_type соответственно.

 select * from characters 
 inner join classes 
 on characters.class = classes.class_type;

Если хотим вывести все имена и уровни персонажей и монстров, сформируем следующий запрос.

 select name, level from characters 
 union 
 select name, level from monsters;

Первая строчка выберет все имена и уровни из таблицы characters, третья выберет все имена и уровни из таблицы monsters, а вторая объединит записи. Важно учитывать, общее число в обеих таблицах должно совпадать, а так же соответствующие друг другу столбцы разных таблиц должны быть одного типа.

Также можно формировать более сложные запросы. Например запрос

 select name, class from characters 
 where class in
 (select class_type from classes where wisdom > 11)

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

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

Ссылки

• видео "Базы данных. SQL"
• Презентация
• БД, с которой работали на семинаре