Первая страница
Наша команда
Контакты
О нас

    Головна сторінка



1. Основи моделювання інформаційних систем Загальне поняття системи. Еволюційні системи

Скачати 472.5 Kb.

1. Основи моделювання інформаційних систем Загальне поняття системи. Еволюційні системи




Скачати 472.5 Kb.
Сторінка3/4
Дата конвертації10.03.2017
Розмір472.5 Kb.
1   2   3   4

Загальна структура оператора вибірки:
SELECT [ ALL | DISTINCT ] select_item_commalist

FROM table_reference_commalist

[ WHERE conditional_expression ]

[ GROUP BY column_name_commalist ]

[ HAVING conditional_expression ]

[ ORDER BY order_item_commalist ]

Выполнение запроса состоит из нескольких шагов, соответствующих разделам оператора выборки. На первом шаге выполняется раздел FROM. Если список ссылок на таблицы (table_reference_commalist) этого раздела соответствует таблицам A, B, … C120), то в результате выполнения раздела FROM образуется таблица (назовем ее T), являющаяся расширенным декартовым произведением таблиц A, B, …, C. Если в разделе FROM указана только одна таблица, то она же и является результатом выполнения этого раздела. Как говорилось в лекции 4, в реляционной алгебре для корректного выполнения операции взятия расширенного декартова произведения отношений в общем случае требуется применение операции переименования атрибутов. Соответствующие возможности переименования столбцов таблиц, указанных в списке раздела FROM, поддерживаются и в SQL. Альтернативный способ именования столбцов результирующей таблицы T основывается на использовании квалифицированных имен столбцов. Идея этого подхода (более раннего в истории SQL) заключается в том, что с любой таблицей, ссылка на которую содержится в списке раздела FROM, можно связать некоторое имя-псевдоним (в стандарте оно называется correlation name121)). Тогда если с такой таблицей A связан псевдоним Z, то в пределах оператора выборки можно ссылаться на любой столбец a таблицы A по квалифицированному имени Z.a. Мы обсудим это подробнее в следующем подразделе. Пока же будем считать, что имена всех столбцов таблицы T определены и различны.

На втором шаге выполняется раздел WHERE. Условное выражение (conditional_expression) этого раздела применяется к каждой строке таблицы T, и результатом является таблица T1, содержащая те и только те строки таблицы T, для которых результатом вычисления условного выражения является true. (Заголовки таблиц T и T1 совпадают.) Если раздел WHERE в операторе выборки отсутствует, то это трактуется как наличие раздела WHERE true,122) т. е. T1 содержит те и только те строки, которые содержатся в таблице T. Обратите внимание на разницу в трактовке логических выражений в операторах выборки и в табличных ограничениях целостности. Логическое выражение раздела WHERE (и раздела HAVING) оператора выборки разрешает выборку строки в том и только в том случае, когда результатом вычисления логического выражения на данной строке является true (значения false и uknown не являются разрешающими). Логическое выражение табличного ограничения целостности запрещает наличие строки в таблице в том и только в том случае, когда результатом вычисления логического выражения на данной строке является false (значения true и uknown не являются запрещающими).

Если в операторе выборки присутствует раздел GROUP BY, то он выполняется на третьем шаге. Каждый элемент списка имен столбцов (column_name_commalist), указываемого в этом разделе, должен быть одним из имен столбцов таблицы T1. В результате выполнения раздела GROUP BY образуется сгруппированная таблица T2, в которой строки таблицы T1 расставлены в минимальное число групп, таких, что во всех строках одной группы значения столбцов, указанных в списке имен столбцов раздела GROUP BY (столбцов группировки), одинаковы.123) Заметим, что сгруппированные таблицы не могут являться окончательным результатом оператора выборки. Они существуют только на концептуальном уровне на стадии выполнения запроса, содержащего раздел GROUP BY.

Если в операторе выборки присутствует раздел HAVING, то он выполняется на следующем шаге. Условное выражение этого раздела применяется к каждой группе строк таблицы T2, и результатом является сгруппированная таблица T3, содержащая те и только те группы строк таблицы T2, для которых результатом вычисления условного выражения является true. Условное выражение раздела HAVING строится по синтаксическим правилам, общим для всех условных выражений, но обладает той спецификой, что применяется к группам строк, а не к отдельным строкам. Поэтому предикаты, из которых строится это условное выражение, должны быть предикатами на группу в целом. В них могут использоваться имена столбцов группировки (инварианты группы) и так называемые агрегатные функции (COUNT, SUM, MIN, MAX, AVG) от других столбцов. Мы обсудим агрегатные функции более подробно в лекции 19.

При наличии в запросе раздела HAVING, которому не предшествует раздел GROUP BY, таблица T1 рассматривается как сгруппированная таблица, состоящая из одной группы строк, без столбцов группирования. В этом случае логическое выражение раздела HAVING может состоять только из предикатов с агрегатными функциями, а результат вычисления этого раздела T3 либо совпадает с таблицей T1, либо является пустым.

Если в операторе выборки присутствует раздел GROUP BY, но отсутствует раздел HAVING, то это трактуется как наличие раздела HAVING true,124) т. е. T3 содержит те и только те группы строк, которые содержатся в таблице T2.

После выполнения раздела WHERE (если в запросе отсутствуют разделы GROUP BY и HAVING, случай (a)) или явно или неявно заданного раздела HAVING (случай (b)) выполняется раздел SELECT. При выполнении этого раздела на основе таблицы T1 в случае (a) или на основе сгруппированной таблицы T3 в случае (b) строится таблица T4, содержащая столько строк, сколько строк или групп строк содержится в таблицах T1 или T3 соответственно. Число столбцов в таблице T4 зависит от числа элементов в списке элементов выборки (select_item_commalist) и от вида элементов.
4. Робота з даними тимчасових таблиць баз даних

1. При создании таблицы, в скобках, через запятую, перечисляются атрибуты таблицы и их описания (типы данных, значения по умолчанию, ограничения на применяемые значения, и другие дополнительные характеристики). После последнего атрибута запятая не требуется:


CREATE TABLE `table_name` (

`Field_1` variable_type,

..,

`Field_N` variable_type );


2. Удаление таблицы:

DROP TABLE `table_name`;

3. Для изменения таблиц используется операнд ALTER TABLE, вид производимого изменения определятся последующими дополнительными командами:

3.1. Переименовать таблицу:

ALTER TABLE `table_name` RENAME TO `new_table_name`;

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

ALTER TABLE `table_name` ADD COLUMN `new_column` variable_type

[ FIRST|AFTER `exist_column` ];

3.3. Изменить тип атрибута таблицы. Выбирается таблица, изменяемый атрибут, и заново перечисляются все требуемые для него параметры:

ALTER TABLE `table_name` MODIFY COLUMN `bad_column` new_parameters;

3.4. Удалить атрибут из таблицы:

ALTER TABLE `table_name` DROP COLUMN `bad_column`;


1. Додавання рядків в таблицю. Можна або по всіх полях таблиці:

INSERT INTO `table_name` VALUES (value_1 .., value_n);

Або строго по заданих:

INSERT INTO `table_name` (`field_1` .. `field_n`)

VALUES (value_1 .., value_n);

Незаповненим полям привласнять значення NULL — відмітка про відсутність значення, не плутайте з нульовим значенням.

2. Зміна внесених даних. Для цього вимагається перерахувати змінні поля і вказати на яке значення замінити вміст вибраних атрибутів:

UPDATE `table_name`

SET `field1`='value1' `field2`='value2' ... `fieldN`='valueN';

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

UPDATE `table_name`

SET `field1`='value1' `field2`='value2' ... `fieldN`='valueN'

WHERE умова  ;
3. Для видалення з таблиці, потрібно вказати назву таблиці і накласти умову відбору, по якому будуть знайдені рядки для видалення:
DELETE FROM `table_name`

WHERE умова  ;


Ключи

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

Пример создания:

CREATE TABLE `t1` (

`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

...


) TYPE=InnoDB;

Внешний ключ

Пример создания:

CREATE TABLE `laptops` (

`id` SERIAL PRIMARY KEY,

`manufacturer_id` BIGINT UNSIGNED NOT NULL,

CONSTRAINT `made_by` FOREIGN KEY(`manufacturer_id`) REFERENCES `manufacturers`(`id`) ON DELETE CASCADE

) TYPE=InnoDB;

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

Способы образования новых таблиц из исходных:



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

  2. Ограничение — построение новой таблицы из исходной, путем включения в нее тех строк исходной таблицы, которые отвечают некоторому критерию в виде логического условия (ограничения).

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

  4. Объединение — построение новой таблицы из двух или более исходных, путем включения в нее всех строк исходных таблиц (при условии, что они подобны).

В чистом виде встречается только декартово произведение (операнд CROSS JOIN). Остальные операнды соединения таблиц происходят в несколько этапов, используя разные вышеперечисленные операции над таблицами.

CROSS JOIN и INNER JOIN

В СУБД MySQL так реализуют операцию декартова произведения таблиц:

SELECT * FROM `join1` CROSS JOIN `join2`;

SELECT * FROM `join2` INNER JOIN `join1`;

Введя дополнительные команды, можно:



  • добавить условие соединения по полям таблиц:

SELECT * FROM `join1` INNER JOIN `join2` ON `join1`.`surname` = `join2`.`text`;

  • произвести декартово произведение по полям таблиц с одинаковыми названиями:

SELECT * FROM `join1` INNER JOIN `join2` USING(`id_join2`);

SELECT * FROM `join1` INNER JOIN `join2` USING(`name`, `id_join2`);

FULL (OUTER) JOIN

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

SELECT * FROM `join1` FULL JOIN `join2`;

LEFT (OUTER) JOIN и RIGHT (OUTER) JOIN

При использовании синтаксиса OUTER JOIN вы должны использовать ключевое слово RIGHT или LEFT, чтобы указать таблицу, все строки которой будут включены в результат.

Си́нтаксис (дав.-гр. σύνταξις - "побудова, порядок, складання", від σύν - "з, разом" і ταξις - "впорядкування") - розділ граматики, що вивчає граматичну будову словосполучень та речень у мові.

Следующие записи эквивалентны:

SELECT * FROM `join1` LEFT JOIN `join2` ON join1.id_join2 = join2.id_join2;

SELECT * FROM `join2` RIGHT JOIN `join1` ON join1.id_join2 = join2.id_join2;

В обоих случаях будут показаны все строки связей с `join1`, но в отличие от FULL (OUTER) JOIN не будут выведены несвязные строки таблицы `join2`.

NATURAL JOIN

Операция естественного соединения выражается через операции переименования, соединения общего вида и проекции. Для упрощения записи используется операнд NATURAL JOIN:

SELECT * FROM `join1` NATURAL JOIN `join2`;

Объединения запросов

UNION


UNION используется для объединения результатов работы нескольких команд SELECT в один набор результатов:

(SELECT join1.name, join1.surname AS text FROM join1) UNION (SELECT join2.name, join2.text FROM join2);

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

Конструкция "join1.surname AS text" означает, что для атрибута "join1.surname" присвоен псевдоним "text" с целью исключения совпадения имен в результате запроса и для упрощения работы в СУБД.

UNION ALL и UNION DISTINCT

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

(SELECT `join1`.`name`, `join1`.`surname` AS `text` FROM `join1`) UNION ALL (SELECT `join2`.`name`, `join2`.`text` FROM `join2`);

Сравните результат этого запроса с предыдущим.

Подзапросы

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

SELECT * FROM `join1` WHERE `join1`.`id_join2` =

(SELECT `join2`.`id_join2` FROM `join2` WHERE `join2`.`id_join2` > 1);

На подзапросы можно налагать дополнительные ограничения, такие как:

1) Оператор EXISTS. Используется, чтобы указать предикату, производит ли подзапрос вывод.

Предика́т (від лат. praedicare - проголошувати, заявляти, присуджувати) у сучасній логіці зазвичай означає булевозначну функцію P: X→ , яка називається предикатом на X. Однак, предикати мають багато різних інтерпретацій та способів використання у математиці та логіці, і їх точне визначення різниться від теорії до теорії.

Возвращает булево значение:

SELECT * FROM `join1`

WHERE EXISTS `join1`.`id_join2` = (SELECT `join2`.`id_join2` FROM `join2` WHERE `join2`.`id_join2` > 1);

2) Операторы SOME/ANY (взаимозаменяемые — различие в терминологии состоит в том, чтобы позволить людям использовать тот термин, который наиболее однозначен). Оператор ANY берет все значения выведенные подзапросом, причем такого же типа, как и те, которые сравниваются в основном предикате. В этом его отличие от EXISTS, который просто определяет, производит ли подзапрос результаты, и фактически не использует эти результаты.

SELECT * FROM `join1`

WHERE `join1`.`id_join2` = ANY (SELECT `join2`.`id_join2` FROM `join2`);

3) Оператор ALL. Предикат является верным, если каждое значение выбранное подзапросом удовлетворяет условию в предикате внешнего запроса:

SELECT * FROM `join1`

WHERE `join1`.`id_join2` = ALL (SELECT `join2`.`id_join2` FROM `join2`);



5. Аналіз складних запитів

Представления

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

Плюсы использования виртуальных таблиц:



  1. Повышение скорости работы. Когда прикладной программе требуется таблица с определённым набором данных, она делает простейший запрос из подготовленного представления. Поскольку SQL-запрос, выбирающий данные представления, зафиксирован на момент его создания, СУБД получает возможность применить к этому запросу оптимизацию или предварительную компиляцию, что положительно сказывается на скорости обращения к представлению по сравнению с прямым выполнением того же запроса из прикладной программы.

  2. Независимая модификация прикладной программы и схемы хранения данных.

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

Может возникнуть такая ситуация, что в исходных таблицах есть обязательные и не пустые (NOT NULL) поля, а в представлении их нет. Тогда операция добавления строки данных не может быть выполнена за одно действие. Во избежание потерь данных подобных действий следует избегать.

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



  1. Создание:

CREATE VIEW `name` [ FIELDS ] AS { запрос };

  1. Удаление:

DROP VIEW `name`;

  1. Изменение:

ALTER VIEW `name` [ FIELDS ] AS { новый запрос }

Индексы


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

Что следует учитывать при создании индексов



  1. Если использование индекса требует от MySQL прохода более чем по 30% строк в данной таблице, в таком случае просмотр всей таблицы, по всей видимости, окажется намного быстрее, так как потребуется выполнить меньше операций поиска. Поэтому индексы не используют для малых таблиц. Индексы дают прирост производительности при большом разбросе значений индексированных данных.

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

  3. Особенность строения индекса как бинарного дерева приводит к тому, что индекс с известным началом ("index%") работает много лучше чем, если известна его середина ("%index" или "%index%").

  4. При создании многостолбцовых индексов следует учитывать порядок создания индексов. Например, создан индекс по столбцам index1-index2-index3 (обратите внимание на их порядок). Из-за структуры индекса в виде B-дерева (B-tree) он будет эффективен при запросах, в которых участвуют следующие индексы (именно в таком порядке): index1-index2-index3, index1-index2, index1. В остальных случаях использования проиндексированных столбцов ("index1", "index2", "index3") индекс не повлияет на производительность.

Операнды работы с индексами

  1. Создание индекса:

CREATE INDEX `indexName` ON `table1` (`fieldA`, `fieldB`, ...);

  1. Удаление:

DROP INDEX `name` ON `table1`;

  1. Использование индекса. Эффективность использования индекса зависит от задачи:



    • эффективен при соединении таблиц, т.к. в проиндексированных полях упрощается поиск данных для сопоставления, а значит — соединение работает в целом быстрее;

    • эффективен при поиске по значению индекса;

    • не годится для выборки всей таблицы.

SELECT

`index_a` /* :-( */

FROM

`table1` LEFT JOIN `table2`



ON `table1`.`index_b` = `table2`.`index_c` /* :-)) */

WHERE


`index_d` = expr; /* :-) */

Полнотекстовый поиск

Полнотекстовый поиск — один из вариантов использования индексов, поддерживаемый только в движке MyISAM для типов данных CHAR, VARCHAR, TEXT. Используется для поиска подстрок в строках таблиц:

CREATE TABLE `articles` (

`id` INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

`title` VARCHAR(200),

`body` TEXT,

FULLTEXT (`title`, `body`)

);

 

INSERT INTO `articles` (`title`, `body`) VALUES



('MySQL Tutorial','DBMS stands for DataBase ...'),

('How To Use MySQL Well','After you went through a ...'),

('Optimizing MySQL','In this tutorial we will show ...'),

('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),

('MySQL vs. YourSQL','In the following database comparison ...'),

('MySQL Security','When configured properly, MySQL ...');

 

SELECT * FROM `articles` WHERE MATCH (`title`, `body`) AGAINST ('database');



---- ------------------- ------------------------------------------

| id | title | body |

---- ------------------- ------------------------------------------

| 5 | MySQL vs. YourSQL | IN the following DATABASE comparison ... |

| 1 | MySQL Tutorial | DBMS stands FOR DATABASE ... |

---- ------------------- ------------------------------------------

 

SELECT `id`, `body`, MATCH (`title`, `body`) AGAINST



('Security implications of running MySQL as root') AS `score`

FROM `articles` WHERE MATCH (`title`, `body`) AGAINST

('Security implications of running MySQL as root');

---- ------------------------------------- -----------------

| id | body | score |

---- ------------------------------------- -----------------

| 4 | 1. Never run mysqld AS root. 2. ... | 1.5219271183014 |

| 6 | When configured properly, MySQL ... | 1.3114095926285 |

---- ------------------------------------- -----------------

Долгие запросы и их анализ

Довольно часто возникают проблемы с выполнением тех или иных сложных запросов, выливающиеся в возрастание их времени выполнения, при этом не всегда понятно на каком именно запросе возникает возрастание времени выполнения. Для обнаружения подобных проблем можно использовать штатное средство MySQL -- журналы долгих операций. В конфигурационный файл my.cnf следует внести следующие коррективы:

log-slow-queries=/path/TO/slow_queries.log

long_query_time=10

//где long_query_time -- количество секунд выполнения запроса, после которых он попадет в журнал медленных запросов

Либо запустить сервер MySQL c параметром:

–log-slow-queries[=/tmp/slow_queries.log]

Подобным образом за счет параметра

–log-queries-not-using-indexes

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

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

/usr/sbin/mysqld, Version: 5.0.84-log (Gentoo Linux mysql-5.0.84-r1). started WITH:

Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock

Time Id Command Argument

# Time: 091104 15:39:23

# User@Host: andrey[andrey] @ localhost []

# Thread_id: 2 Schema: laba_index

# Query_time: 82.416012 Lock_time: 0.342861 Rows_sent: 0 Rows_examined: 0 Rows_affected: 1050024 Rows_read: 1050025

USE `laba_index`;

UPDATE `example1` SET `Delta`= (`Tx`-`Rx`)*10*rand();

Оператор EXPLAIN

EXPLAIN `tbl_name` -- эквивалент для DESCRIBE `tbl_name`.

 

EXPLAIN SELECT select_options -- анализ оптимизатором MySQL выполнения запроса.



 

EXPLAIN SELECT * FROM `example1` IGNORE INDEX (`comboTR`,`comboRT`,`comboRTD`,`Txindex`) WHERE `Rx`<500 AND `Tx`<500;

---- ------------- ---------- ------- --------------- --------- --------- ------ ------ -------------

| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |

---- ------------- ---------- ------- --------------- --------- --------- ------ ------ -------------

| 1 | SIMPLE | example1 | range | RXindex | RXindex | 5 | NULL | 4502 | USING WHERE |

---- ------------- ---------- ------- --------------- --------- --------- ------ ------ -------------

 

 



EXPLAIN SELECT * FROM `example1` USE INDEX (`comboTR`,`comboRT`,`comboRTD`,`Txindex`) WHERE `Rx`<500 AND `Tx`<500;

-- ----------- -------- ----- -------------------------------- -------- ------- ---- ---- --------------------------

|id|select_type|TABLE |type |possible_keys |KEY |key_len|ref |rows| Extra |

-- ----------- -------- ----- -------------------------------- -------- ------- ---- ---- --------------------------

| 1| SIMPLE |example1|range|TXindex,comboTR,comboRT,comboRTD|comboRTD|5 |NULL|4689| USING WHERE; USING INDEX |

-- ----------- -------- ----- -------------------------------- -------- ------- ---- ---- --------------------------


1   2   3   4


Скачати 472.5 Kb.

  • 4. Робота з даними тимчасових таблиць баз даних
  • 5. Аналіз складних запитів