Учебная страница курса биоинформатики,
год поступления 2010

Семестры Студенты Преподаватели

• 2023
• 2022
• 2021
• 2020
• 2019
• 2018
• 2017
• 2016

• ...

Реляционные базы данных. MySQL.

Реляционные базы данных

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

Пример. Гены и геномы.

В этом примере можно заметить, что некоторые свойства повторяются. Они принадлежат не объекту ген, а объекту геном. Чтобы не тратить места на диске на повторение данных, мы можем завести еще одну таблицу. Она будет описывать свойства геномов, а чтобы связать гены с геномами, будем назначать объектам (т.е. строкам) уникальные номера, идентификаторы (id от identifier), которые будут эти объекты идентифицировать. А чтобы различать таблицы, дадим им имена. . Хорошо бы договориться называть имена таблиц и столбцов (или полей) в одном стиле, например, они будут содержать только латинские буквы или знак подчеркивания.

Таблица genome

Таблица gene

В такой ситуации говорят, что таблица gene ссылается на таблицу genome по полю genome_id. Это поле является первичным ключом для таблицы genome. Т.е. ключом, по значению которого можно найти строку объекта. В таблице gene поле genome_id называют внешним ключом, а первичным ключом тут является поле gene_id.

Язык SQL

Для работы с данными, лежащими в базе данных, разработан язык SQL, Structural Query Language. Он содержит команды для просмотра данных, вставки, корректировки, удаления, создания и удаления таблиц и многого другого. В 1992 году был разработан стандарт этого языка, который в той или иной мере соблюдается различными системами управления базами данных. Для наших задач мы будем использовать СУБД MySQL. Язык для работы с этой СУБД немножко отличается от SQL, но при этом поддерживает все необходимые стандарты.

Простые запросы к базе данных

Команды SQL называют запросами. Запрос выборки имеет вид:

SELECT * FROM gene;
SELECT * FROM gene LIMIT 100;

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

SELECT id, name,stop-start+1 AS len FROM gene;

Слово AS означает, что третья колонка в результирующей выдаче будет называться “len”. Если нам нужны не все строчки (частое желание, если в таблице тысячи записей), то нам нужно использовать фильтр. Он описывается блоком WHERE:

SELECT id, name,stop-start+1 AS len FROM gene
WHERE stop-start+1>=1000;

.. WHERE name = 'thrA'; #(в одинарных кавычках!) 
.. WHERE name LIKE 'thr%';

( _ - один любой символ, % - любое количество любых символов)

В данном примере выдача будет содержать только те строки, для которых верно утверждение в WHERE-блоке, т.е. гены thrA и thrB. Вообще запрос типа SELECT пишется в следующем порядке:

1. определяемся с тем, из какой таблицы выбираем данные (блок FROM);
2. задаем фильтр на строки, т.е. выражение в блоке WHERE;
3. перечисляем поля и производные от них в блоке SELECT.

Строки в таблице располагаются неупорядоченно. Кроме того, запрос выборки позволяет задать способ сортировки возвращаемых записей. За это отвечает блок ORDER BY:

SELECT id,name,stop-start+1 AS len FROM gene
WHERE stop-start+1>=1000 ORDER BY name;

Если мы хотим сортировать в обратном порядке, то нам необходимо добавить после выражения сортировки слово DESC (descending):

SELECT id,name,stop-start+1 AS len FROM gene 
WHERE stop-start+1>=1000 
ORDER BY stop-start+1 DESC;

Помимо арифметических операций можно использовать различные дополнительные функции, предоставляемые СУБД MySQL.

Числовые функции

Строковые функции

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

Группировка данных

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

SELECT genome_id,COUNT(*) AS qnt
FROM gene GROUP BY genome_id;

В этом примере мы группируем строки по одинаковости поля genome_id:

Далее к каждой группе мы применяем функцию COUNT(*), вычисляющую количество записей в группе. В результате для нашего примера, в котором три генома, получим:

Для подсчета минимального, максимального, среднего значения или суммы используются функции MIN, MAX, AVG или SUM соответственно.

Соединение таблиц

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

<таблица1> INNER JOIN <таблица2> ON <условие_соединения>

Рассмотрим простой пример. Нам нужно посчитать количество генов, длина которых не менее 1000 нуклеотидов и в названии соответствующего генома встречается слово ‘coli’:

SELECT count(*) FROM gene INNER JOIN genome ON gene.genome_id=genome.genome_id
WHERE stop-start+1>=1000 AND genome.name LIKE ‘%coli%’;

В этом примере мы несколько раз используем полные квалификаторы полей (но не везде), т.к. в обеих таблицах есть поля genome_id и name. Чтобы сократить запись, можно, как и для полей, делать подмену имени для таблиц:

SELECT count(*)FROM gene gn
INNER JOIN genome gm ON gn.genome_id=gm.genome_id
WHERE stop-start+1>=1000 AND gm.name LIKE '%coli%';

По сути, соединение таблиц – операция обратная той, которую мы делали вначале, когда разделяли данные на несколько таблиц во избежание многократного повторения значений. Мы как бы подстраиваем к записям из таблицы генов записи из таблицы геномов. Вообще запрос, состоящий из блоков SELECT, FROM, INNER JOIN, WHERE и GROUP BY выполняется в таком порядке:

1. таблицы в блоке FROM присоединяются друг к другу в соответствии с условием, описанным в ON
2. из объединенной таблице остаются только те записи, которые удовлетворяют условию в WHERE
3. если есть блок GROUP BY, то записи объединяются в группы, и в результате каждая группа рождает одну запись
4. из всех полей всех таблиц из блока FROM остаются только те (или из них вычисляются выражения), которые перечислены в блоке SELECT.

Подзапросы

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

SELECT count(*)FROM 
(SELECT genome_id,count(*) AS qnt FROM gene GROUP BY genome_id )tbl
WHERE tbl.qnt<=4500;

Как видно, при этом такой виртуальной таблице необходимо задать имя (например, tbl).

Полезные команды диалекта MySQL

Команда

SHOW TABLES;

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

DESC[RIBE] <имя_таблицы>;

перечисляет поля в таблице и их типы. Типы данных, начинающиеся на int, float, double, tinyint являются числовыми. Типы данных со словами char, varchar, text являются текстовыми.

Мы будем работать с базой данных student2_db, которая содержит геномные данные по 10-ти штаммам E.coli. Подключаться к ней нужно через текстовую консоль mysql.exe, вызывая команду:

mysql -h db.bioinf.fbb.msu.ru -u student student2_db

Структура базы данных тут. Клиент MySQL командной строки для Windows тут.