Справочник функций

Ваш аккаунт

Войти через: 
Забыли пароль?
Регистрация
Информацию о новых материалах можно получать и без регистрации:

Последние темы форума

Показать новые сообщения »

Почтовая рассылка

Подписчиков: 11654
Последний выпуск: 19.06.2015

Тренинг по MS SQL Server

204
23 января 2011 года
Оlga
2.2K / / 04.02.2006
Собственно сейчас начала учить данный язык запросов, с самого простого, и торможу. В Данной теме хотелось бы задавать вопросы, которые возникают по мере изучения данного языка, а не создавать темы пачками. Заранее спасибо ответившим.

Имеется 2 таблицы: tbl1 & tbl2
tbl1 -> _PrimaryKey, brand_name .....
tbl2 -> _ForeignKey, product_name ...

связь один ко многим по ключам. для одного бренда может быть несколько полей с разными _PrimaryKey (как id товара, например)
задача: подсчитать кол-во товара из tbl2 для бренда 'ааа'.

решение:

 
Код:
SELECT count(*)
FROM tbl2
WHERE _ForeignKey IN (
                       SELECT _PrimaryKey FROM tbl1 WHERE brand_name = 'aaa');
на мой взгляд весьма простой запрос, но если немного извратиться ) и написать по другому:
 
Код:
SELECT count(tbl2._ForeignKey)
FROM tbl2 JOIN tbl1 ON _ForeignKey = _PrimaryKey
WHERE  brand_name = 'aaa';
как я понимаю: сначала выполняется команда ФРОМ, т.е. выбираются все поля из тбл2 и тбл1 по заданному условию (совпадение ключей), затем идет отборка по условию WHERE, т.е. остаются поля, где брэнд 'ааа', затем считается кол-во полей - таково мое понимание, но запрос не работает, значит я что то не так мыслю. подскажите пожалуйста, где проблема.
37K
24 января 2011 года
freets
97 / / 15.10.2010
попробуй использовать алиасы таблиц
 
Код:
select count(*)
from tbl2 t2
 join tbl1 t1 on t1._PrimaryKey = t2._ForeignKey
where t1.brand_name = 'aaa'
347
24 января 2011 года
SomewherSomehow
477 / / 25.07.2004
Попробую дать пару советов, если вы только начали разбираться с sql server, надеюсь вам поможет.
Прежде всего, лучше сразу указывать версию сервера, от этого может варьироваться разнообразие решений. Версию можно получить выполнив запрос select @@version.

По поводу того как выполняется запрос. В sql server есть такой модуль как оптимизатор запросов. Он отвечает за то, как ваш логический запрос будет приведен в последовательность физических действий. Т.е. вы пишете select * from mytable, а оптимизатор решает какие именно физические операции нужно сделать чтобы получить результат. То в какой последовательности и как будет выполнять сервер называется планом запроса. Вы можете просматривать план запроса при помощи
1) графической среды выполнения запросов (если 2000, то Query Analyzer(QA), если 2005 и выше Sql Server Management Studio (SSMS).
2) перед запросом перевести контекст выполнения в особый режим одной из следующих инструкций
set showplan_all on или set showplan_text on или set showplan_xml on
выключить режим можно указав вместо on параметр off.

Касательно вашего запроса, в приложенном файле как раз скрин графического плана этого запроса из SSMS.
Теперь как читать план и что означают пиктограммы. План читается справа налево и представляет собой дерево, каждый узел которого это оператор работы с данными, физический или логический. Корневой вершиной собственно является узел на котором был получен конечный результат. Если навести мышкой на узел откроется окошко с подробностями про оператор который представлен в этом узле.
Впринципе тут можно долго распинаться, но по сути это будет только пересказ msdn, так что просто дам ссылки.
Справочник по логическим и физическим операторам
Графическое отображение планов выполнения (SQL Server Management Studio)
Анализ запроса

Касательно привденного плана вышего запроса. Читаем справа налево чтопроисходит.
Сканирование всех строк таблицы @tbl1, причем заметьте в подробностях указано predicate brand_name = 'aaa', это значит он сканирует встроки таблицы и отбирает только те которые удовлетворяют условию в where. Т.е. уже видно что немного не так как вы предполагали, условие отрабатывает до соединения таблиц.
Далее сканируется вторая таблица.
После этого выполняет соединение. слово Hash обозначает алгоритм по которому соединяются строки.
После этого выполняется агррегирвоание строк (функция count)
Потом полученный скаляр выводится в результат.
Результат получился правильный. Отсюда следующий совет, когда говорите что что-то не праильно, приводите либо текст ошибки, либо описание какие нужны результаты, а какие получены.
А чтобы другие могли воспроизвести у себя эти результаты желательно приводить скрипты по созданию и заполнению тестовых табличек и сам запрос. Вот например, я тестировал на этих данных:
Код:
-- объявляем тестовые таблицы, префикс @ означает что это табличная переменная
-- для нас тут важно то, что она уничтожается после выполнения запроса,
-- по этому она хорошо подходит в качестве таблички для проверки запроса
declare @tbl1 table(_PrimaryKey int identity, brand_name varchar(100))
declare @tbl2 table(_ForeignKey int, product_name varchar(100))

-- эта часть заполняет таблицы тестовыми занными
insert into @tbl1 select 'aaa' union all select 'bbb' union all select 'ccc'
insert into @tbl2 select 1, 'a-product1' union all select 1, 'a-product2' union all select 1, 'a-product3'
union all select 2, 'b-product1' union all select 2, 'b-product2' union all select 3, 'c-product1'

-- это собственно ваш запрос
SELECT count(_ForeignKey)
FROM @tbl2 JOIN @tbl1 ON _ForeignKey = _PrimaryKey
WHERE  brand_name = 'aaa';

-- результат его выполнения
-- 3
-- т.е. вроде правильный результат.
план и запрос для версии сервера
 
Код:
select @@version
------------------------------------
Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)
    Dec 10 2010 10:56:29
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Следование этим советам позвоит мне и другим участникам форума более точно отвечать на ваши вопросы. Так что дерзайте и если не плюнете на все это дело, то постараемся разъяснить все моменты =)
204
24 января 2011 года
Оlga
2.2K / / 04.02.2006
freets, спасибо за желание помочь, но при связке двух разных таблиц, наличие алиасов ничего вообще не меняет, не влияет, разве что если имя таблиц длинное и не хочется его каждый раз писать. мое ИМХО )

SomewherSomehow, спасибо за подробный ответ, много интересного ) замечания учту. насчет мне надоест, скорее я еще надоем, и самой себе в том числе ) а учить язык все равно придется, выбора нет.
насчет того что ни привела подробностей, это задача с сайта где запрещено их задачи обсуждать на стороннем форуме, вот я и описала аналог своими словами без подробностей, СУБД прямо у них на сайте интегрирована для решения их задач:
Цитата:
Данный самоучитель выдержан в стиле стандарта SQL-92. Особенности MS SQL Server 2005, который используется для выполнения запросов на сайте....

сайт хороший, но подобные вещи меня ставят в тупик, когда на мой взгляд запрос должен работать, у них выдает ошибку что не правильно... - у меня крышу рвет, начинаю чувствовать, что у меня серьезные проблемы :mad: просто на ощупь сделать запрос, что то убавить, добавить, посмотреть их хелп и т.д. - вполне возможно, но меня такой слепой метод не устраивает, мне всегда важно понимать причину и следствие, иначе это не учеба, а пустая трата времени - дорогое удовольствие. поэтому постараюсь разобраться с этими проблемами, т.к. время они у меня забирают очень много. первое - надо будет на своем компе установить ms sql, а то конечно не очень удобно получается.

моя цель сейчас базис повторить/выучить (около 5 лет назад учила Oracle):
1. запросы разных уровней сложности
2. использование переменных
3. хранимые процедуры
- ..... по пути наверно еще обнаружиться, что мне надо )

мой выбор ms sql - самая популярная, востребованная СУБД у нас на рынке, затем MySql. Oracle - иногда требуется, часто как дополнение к мс скл... в общем живем по потребностям )

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

37K
24 января 2011 года
freets
97 / / 15.10.2010
Цитата: Оlga
freets, спасибо за желание помочь, но при связке двух разных таблиц, наличие алиасов ничего вообще не меняет, не влияет, разве что если имя таблиц длинное и не хочется его каждый раз писать. мое ИМХО )


Ну, во-первых с алиасами действительно удобнее, а во-вторых, когда запрос выглядит вроде как правильно, почему бы не достать бубен?:)

204
24 января 2011 года
Оlga
2.2K / / 04.02.2006
Цитата: freets
... когда запрос выглядит вроде как правильно, почему бы не достать бубен?:)


да потому что я учусь :) а танцы с бубнами этому не помогают )
а так, конечно я могу и беру бубен, но это не решает проблемы, как я писала выше.

347
24 января 2011 года
SomewherSomehow
477 / / 25.07.2004
Насчет подхода к учебе полностью с вами согласен. Чтобы в дальнейшем понимать что и как работает лучше с самого начала потратить время и разобраться, чтобы потом не пользоваться сомнительными способами аля "достать бубен". Как говорилось в старом советском мультике "лучше день потерять, потом за пять минут долететь" =)

Насчет книг, мое имхо, что субд какого-либо производителя лучше изучать по книгам этого производителя. MSDN хорош, но как самоучитель не очень подходит, тут тоже понимаю вас, ибо все-таки это прежде всего документация и стиль изложения соответствующий.
Я вам рекомендую прежде всего книгу самой microsoft называется "Microsoft Corporation Проектирование и реализация баз данных Microsoft SQL Server 2000. Учебный курс". (есть кстати в пдф-ке у меня 46 м.б. могу отправить если влезет в почту, но в инете и так легко гуглится). Поищите может есть такая же и по 2005 в электронном виде, я не искал. Книга построена ввиде самоучителя для подготовки к экзамену, так что если найдете про 2005 можете потом и на сертификатик сдать экзамен (по 2000 экзамены уже отменены). Хотя основы что для 2000, что для 2005 - одинаковы, так можно читать не напрягаясь, практически все написанное там применимо и к более новым версиям.
Есть более современные книги " Разработка приложений на основе Microsoft SQL Server 2005. Авторы Эндрю Дж.Браст, Стивен Форте"., "Microsoft SQL Server 2005 Справочник администратора. Уильям Станек." - это тоже книги редакции MS Press, так что рекомендую.
Еще порекомендую после основ найти и почтить книги Кена Хендерсона. Но там уже рассчитано на имеющих опыт, описываются всякие низкоуровневые вещи, на первых порах точно не надо.

Насчет задачек с примерами, вы похоже уже нашли то что я хотел порекомендовать =) Так что я кажется понимаю о каком сайте вы говорите. =)
Относительно этой задачи, честно говоря фиг знает, что там было в первоначальном условии, но в том условии как вы написали, вариант с join 100% правильный, даже не знаю что бы такое придумать, чтобы его сломать... Может чего не дописали и есть какие-то еще требования или условия? Попробую подумать еще.
Кстати я насколько помню на том сайте ошибка как-то диагностируется, типа вернулось больше рядов или меньше рядов или еще что-то...
204
24 января 2011 года
Оlga
2.2K / / 04.02.2006
Цитата:
Относительно этой задачи, честно говоря фиг знает, что там было в первоначальном условии, но в том условии как вы написали, вариант с join 100% правильный, даже не знаю что бы такое придумать, чтобы его сломать... Может чего не дописали и есть какие-то еще требования или условия? Попробую подумать еще.


думаю тяжело будет думать не видя источника проблемы. лучше я попробую найти страницу, откуда я эту задачу раскопала и кинуть ссылку на их источник, насколько я помню, это из туториала(примеры выборки из нескольких таблиц):
http://www.sql-tutorial.ru/
я вчера у них на 16 задаче поехала ) вот и заехала, уже не помню что откуда и куда. сейчас поискала - не нашла, будет время там позаниматься, думаю дня через 2-3, может опять найду, где я эту задачу нашла.

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

347
24 января 2011 года
SomewherSomehow
477 / / 25.07.2004
Да, сейчас еще раз более точно проверил. Запросы абсолютно одинаковые с точки зрения оптимизатора. Т.к. для их выполнения генерируется одинаовый план.
Код:
declare @tbl1 table(_PrimaryKey int primary key, brand_name varchar(100))
declare @tbl2 table(_ForeignKey int, product_name varchar(100))
-- эта часть заполняет таблицы тестовыми занными
insert into @tbl1 select 1, 'aaa' union all select 2, 'bbb' union all select 3, 'ccc'
insert into @tbl2 select 1, 'a-product1' union all select 1, 'a-product2' union all select 1, 'a-product3'
union all select 2, 'b-product1' union all select 2, 'b-product2' union all select 3, 'c-product1'
-- 1
SELECT count(*)
FROM @tbl2 JOIN @tbl1 ON _ForeignKey = _PrimaryKey
WHERE  brand_name = 'aaa';
-- 2
SELECT count(*)
FROM @tbl2
WHERE _ForeignKey IN (SELECT _PrimaryKey FROM @tbl1 WHERE brand_name = 'aaa');

план в файле, видно что оптимизатор все это приводит к единому набору операторов, и выполняться они будут одинаково и всегда приводить к одним результатм.
но это при условии если в первой таблице поле _PrimaryKey будет действительно первичным ключем, т.е. скрипт создания таблицы такой: declare @tbl1 table(_PrimaryKey int primary key, brand_name varchar(100)). Это заставляет оптимизатор предположить, что повторяющихся значений там быть не может и он приводит второй запрос к такому же плану с Inner join что логично.
Но если таблицу создать без первичного ключа вот так: declare @tbl1 table(_PrimaryKey int, brand_name varchar(100)), планы буду естественно разные. И тогда если в этом поле возможны повторяющиеся значения, то запрос с джойном сломается. Вот например,
Код:
declare @tbl1 table(_PrimaryKey int, brand_name varchar(100))
declare @tbl2 table(_ForeignKey int, product_name varchar(100))

-- эта часть заполняет таблицы тестовыми занными (которые сломают запрос)
insert into @tbl1 select 1, 'aaa' union all select 1, 'aaa' union all select 2, 'bbb' union all select 3, 'ccc'
insert into @tbl2 select 1, 'a-product1' union all select 1, 'a-product2' union all select 1, 'a-product3'
union all select 2, 'b-product1' union all select 2, 'b-product2' union all select 3, 'c-product1'

SELECT count(_ForeignKey)
FROM @tbl2 JOIN @tbl1 ON _ForeignKey = _PrimaryKey
WHERE  brand_name = 'aaa';

SELECT count(*)
FROM @tbl2
WHERE _ForeignKey IN (SELECT _PrimaryKey FROM @tbl1 WHERE brand_name = 'aaa');

разультаты 6 и 3 соответсвенно. В связи с этим вопрос, то что вы назвали в первой таблице полем _PrimaryKey - действительно представяет собой Primary Key или там все же есть повторяющиеся значения? Если есть, то ответ на ваш вопрос найден, если же это рельно первичный ключ, то запросы 100% одинаковые, по крайней мере в тех условиях что вы дали.
И да, поставьте mssql на машину за которой работаете, легче будет изучать и понимать, сразу скопипастил запрос - сам все увидел. Если проблемы с покупкой лицензии, то есть express версия, она бесплатна, а позволяет делать очень многое. Только по-моему к ней нет графических инструментов, но и это решаемо впринцпе =)
347
24 января 2011 года
SomewherSomehow
477 / / 25.07.2004
А, значит мы все-таки про разные сайты, я имел ввиду sql-ex.ru. Там тоже есть упражнения. Ага, посмотрите тогда на задачу повнимательнее если еще раз наткнетесь, а то не знаю как на этих сайтах, а в вопросах от мс иногда встречаются задачи "с подвохом", может и тут что-то подобное.
Насчет почты, можете попробовать отсюда скачать (кстати на сайте еще много интересных книг и по бд и вообще). Если не получится, то кидайте адрес в личку, попробую отправить.
204
24 января 2011 года
Оlga
2.2K / / 04.02.2006
Цитата: SomewherSomehow
А, значит мы все-таки про разные сайты, я имел ввиду www.sql-ex.ru. Там тоже есть упражнения. Ага, посмотрите тогда на задачу повнимательнее если еще раз наткнетесь, а то не знаю как на этих сайтах, а в вопросах от мс иногда встречаются задачи "с подвохом", может и тут что-то подобное.
Насчет почты, можете попробовать отсюда скачать (кстати на сайте еще много интересных книг и по бд и вообще). Если не получится, то кидайте адрес в личку, попробую отправить.


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

вот структура БД:
http://www.sql-ex.ru/help/select13.php
речь о Computer firm

надо было подсчитать кол-во товара в таблице PC для maker 'A'. Один и тот же производитель может иметь продукцию ПК, Лаптопы, Принтеры.

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

п.с. а на "ты" можно? как то в Израиле отвыкла, да и в силу возраста не успела особо привыкнуть :) имею ввиду время проживания вне Израиля, сейчас то уже посолидней =)

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

347
24 января 2011 года
SomewherSomehow
477 / / 25.07.2004
Ок, будем на ты. А расстраиваться не надо, наоборот, если что-то не получилось, но ты потом разобрался и это получилось - это и есть бесценный опыт, на мой взгляд!
Насчет банов - хз, я видел несколько раз обсуждения не рейтинговых задач на других форумах, не знаю, забанили там кого-нить или нет =)
Посмотрел схему, если под tbl1 подразумевалась таблица Продукт, под tbl2 таблица ПК, а под ключевым полем по которому организована связь поле модель, то все правильно, запрос с джойном работает. Единственное нужно указать еще дополнительно в условии соединения что из таблицы продукт требуются записи только с типом ПК, т.к. в этой таблице хранятся сведения моделей и других устройств, лэптопов и принтеров.
В точности такой же задачки не нашел. Но вот например найти среднюю скорость ПК выпущенных фирмой А - получилось именно таким способом.
204
24 января 2011 года
Оlga
2.2K / / 04.02.2006
Цитата: SomewherSomehow
Ок, будем на ты. А расстраиваться не надо, наоборот, если что-то не получилось, но ты потом разобрался и это получилось - это и есть бесценный опыт, на мой взгляд!


правильно, но это если разобралась )

Цитата: SomewherSomehow

Насчет банов - хз, я видел несколько раз обсуждения не рейтинговых задач на других форумах, не знаю, забанили там кого-нить или нет =)


ну, меня предупредили, что могут, а подробностей я не знаю.

Цитата:

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


именно так.

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


да, это было учтено. видно я упростила условие, когда сюда пример делала. написала только самое наболевшее )

Цитата:

В точности такой же задачки не нашел. Но вот например найти среднюю скорость ПК выпущенных фирмой А - получилось именно таким способом.


задача вроде с туториала, я тоже не нашла, пока. если найду, заброшу сюда.

спасибо за помощь)

Знаете кого-то, кто может ответить? Поделитесь с ним ссылкой.

Ваш ответ

Реклама на сайте | Обмен ссылками | Ссылки | Экспорт (RSS) | Контакты
Добавить статью | Добавить исходник | Добавить хостинг-провайдера | Добавить сайт в каталог