заметки по sql

Как хранить объекты в таблицах. Для этого есть модель EAV (Entity-Attribute-Value)

(объект-атрибут-занчение) EAV.
create table issues
issue_id int,
status — статус объекта
);
create table issue attrib
issue_id int
attr_name
attr_value
foreign key
);

сложности с отбраковкой недопустивх данных
более сложная выборка данных
более сложная поддержка целостности
можно наложить ограничение внешенего ключа не к конкретному полю объекта а всем полям вида

attr_value
Чтобывывести все атрибуты в строку требуетс язапрос с большим количество внешних

объединений со связкой по имени каждого атрибута. Ясно, что при увеличении числа атрибутов
запросы становятся более сложными и менее производительными.
Более лучшее решение это исопльзовать нереляционные базы данных.
Полиморфные ассоциации сложность задания двух внешних ключей.
Пример
comments, bugs, featureauests
связь bugs — comments, featureauests -comments один ко многим
хотелось бы чтобы одна таблица comments  для двух этих связей была
Решение №1 добавить дополнительный стоблце в таблицу att_type чтобы определить на каую

таблицу ссылается текущая строка на bugs или featureauests
отсутствие принудительной целостности данных так как по внешенму ключу уже не действуют

ограничения.
Решение №2 создать таблицу пересечений BugsComments, FeaturesComments
Решение №3 содать таблице роидительскую для всех.
Хранение многозначных атрибутов
Хранение нескольких занчений атрибута в одном поле через запятую усложняет проверку

достоверности, чтение, изменение занчений
Решение №1 Ввести тэги в таблицу ошибок в каждой из них хранить отдельное значение null

если не исользуется. Тэг, например, классификация ошибок к какому-то определенному типу,

например, производительность, фнукции ввода-вывода и др.
create table tags (
bug_id
tag
primary key (bug_id, tag)
foreign key (bug_id) reference
теперь есть  возможность хранить столько тэгов склько необходимо.
хранить все занчения, обдладающие одним и тем же смыслом в одном столбце
customers
таблицы имеют свойство накапливать данные когда запросы считавшиеся эффективными перестают

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

повседневного использования данных за прошлый период
хотя с точки зрения моделирвоания данных кажется праивльным сохранят все в единой базе

данных, ее разделение заметно упрощает задачи администрирования базы данных после того,

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

прошлый год хранились в своей таблице, данные за дру. гой год  в своей.
вертикальное разделение — это разделение по столбцам это актуально когд аречь идет о blob

полям. Дело в том, что азпрос к таблцам типа * извлекают все строки включая блоб поля а

это медленно если блоб поля большие.

!! Не допускать порождения данными метаданных
numeric и decimal подобно масштабу с фиксированной точкой.
точность — это всего десятичных разрядов, масштаб количество цифр после запятой
Их преимущестов перед float что они могут хранить дробные числа без округления
!! не используйте тип float если без него можно обойтись.
По поводу разновидностей решение №1 по ограничению значенйи в столбце создать таблицу

поиска с одной строкой для каждого занчения, которое допкскается в таблице bug.status

Затем объявить ограничение внешнего ключа по таблице Bug.Status которое ссылается на новую

таблицу.
хранить ссылку на изображение в поле path ссылка на путь в каталоге или зранить само

изображение как blob поле.
недостатки при откате изменений удалении строки соотвествующей файл стандартными

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

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

действия права пользователей.
!! ресурсы, находящиеся вне базы данных, не управляются базой данных.
индексы не надо выбирать по наитию
- отсуствие определни индексов или их недостаточное количество
- определение избыточного количества или бесполезных
- выполнение запросов в которых индексы не помогают
SELECT COUNT(DISTINCT status) /
COUNT(status) AS selectivity FROM Bugs;
Чем меньше коэффициент селективности, тем ниже эффективность индекса.
Если слово ча¬
сто встречается в книге, в индексе может перечисляться много номеров страниц.
Чтобы найти искомую часть книги, следует пройтись по всем страницам в списке.
MENTOR
{Measure, Explain, Nominate, Test, Optimize и Rebuild — измерение, объяснение,
номинирование, тестирование, оптимизация и перекомпоновка).
В Microsoft этот инструмент называют SQL Server Profiler, а в Oracle
этот инструмент называют TKProf.
того как определен запрос, имеющий наибольшую стоимость, на
следующем шаге выясняют, почему он выполняется медленно. В каждой
базе данных применяется оптимизатор с целью выбора индексов для запро¬
са.
Тестирование
Данный шаг важен: после создания индексов повторно выполните профи¬
лирование запросов. Важно убедиться, что внесенное изменение приводит
к другому результату, чтобы быть уверенным в правильности выполненной
работы.

Индексы обеспечивают наибольшую эффективность, когда они сбалансиро¬
ваны. Со временем, по мере обновления и удаления строк, индексы могут
постепенно становиться несбалансированными, подобно тому как со вре¬
менем происходит фрагментация файловых систем.
!!Знайте свои данные, свои запросы и  выполняйте процедуру MENTOR в отношении индексов
NULL это значение ни ложь, ни истина а третье
!! используйте значение null, чтобы обозначить остуствующие данные.
SELECT bpl.product_id, bl.date_reported AS latest, bl.bug_id
FROM Bugs bl JOIN BugsProducts bpl USING (bug_id)
WHERE NOT EXISTS

(SELECT * FROM Bugs Ь2 JOIN BugsProducts bp2 USING (bug_id)
WHERE bpl.product_id = bp2.product_id
AND bl.date_reported < b2.date_reported);
Решение №2
SELECT m.product_id, m.latest, bl.bug_id
FROM Bugs bl JOIN BugsProducts bpl USING (bug_id)
JOIN (SELECT bp2.product_id, MAX(b2.date_reported) AS latest
FROM Bugs b2 JOIN BugsProducts bp2 USING (bug_id)
GROUP BY bp2.product_id) m
ON (bpl.product_id = m.product_id AND bl.date_reported =
m.latest);

!!Следуйте Правилу единственного значения,»чтобы избежать неодйоэначных ре¬
зультатов запроса.
!!Зачастую запрос невозможно оптимизировать используйте другое решение
не имеет смысла вкладывать большое количе¬
ство ресурсов, чтобы оптимизировать их. Содержание индексов, использу¬
емых редко осуществляемыми запросами, может быть столь же дорогостоя¬
щим, как выполнение этих запросов неэффективным способом. Если это
запрос особого характера, то нет никакой гарантии, что соответствующий
ему индекс так или иначе принесет ему пользу.
!!Не нужно использовать-SQL для решения всех проблем.
«Не следует множить сущее без необходимости»),
Время выполнения запроса тоже имеет свою цену. Оптимизация и выпол¬
нение сложного SQL-запроса, использующего много объединений, корре¬
лированных подзапросов и других операций, более трудны для движка SQL,
чем работа с простым запросом. Программисты имеют мнение, что выпол¬
нение меньшего числа запросов лучше сказывается на производительности.
С другой стороны, цена выполнения одного запроса-монстра может расти
в геометрической прогрессии, в отличие от использования нескольких бо¬
лее простых запросов.
Программист, написавший код запроса, должен будет постоянно поддер¬
живать этот код, даже если запросы переводятся в другой проект. Этим
профаммистом могли быть вы, так что не пишите чрезмерно сложный
SQL-код, который никто не сможет поддерживать.
• «Попробуй вставить в запрос еще один оператор distinct».
Компенсируя взрыв строк при декартовом произведении, программисты
уменьшают частоту копирования, используя ключевое слово distinct, как
модификатор запроса и модификатор агрегатной функции. Это скрывает
уродливость запроса, но принуждает РСУБД к дополнительной работе: ге¬
нерировать промежуточный набор результатов, только чтобы отсортировать
и удалить копии.
Другой сигнал, подсказывающий, что запрос, возможно, является запутан¬
ным, — чрезмерно долгое время выполнения. Низкая производительность
также является симптоматикой и других причин, но поскольку вы исследу¬
ете такой вид запросов, вам следует предположить, что вы попытались вы¬
полнить слишком много действий в одном SQL-операторе.

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

Для SQL это значит: когда существует выбор между двумя запросами, про¬
изводящими один и тот же набор результатов, выберите самый простой.
Необходимо помнить об этом при использовании экземпляров данного ан¬
типаттерна.

использовать принцип разделяй и властвуй.
Выполнение большого количества SQL-запросов или операторов, возмож¬
но, не самый эффективный способ решения задачи. Однако необходимо
совмещать стремление к эффективности с целью решения задачи.
0:10 12.05.2015
Срыв рефакторинга

если при insert не указывать столбцы
после добавления нового столбца возникнет ошибка
или если столбцы меняются местами
чтобы уменьшить объем передаваемых данных по сети не используйте select * получайте то, что Вам нужно.
лучше всегда перечислять столбцы в которых нуждаемся.
защита от дурака
!!Берите все, что хотите но съедайте все, что берете..
Вы можете найти различия между понятиями идентификация и аутентифи¬
кация.
Пользователь может идентифицировать себя кем угодно, но аутенти¬
фикация подтвердит, тот ли он, за кого себя выдает. Пароли — самый рас¬
пространенный способ сделать это.

Когда пользователь вводит форму регистрации,
браузер посылает его пароль в открытом текстовом виде на сервер, где вы¬
числяется хещ-код, как описано ранее. Вы можете защититься от подобной
ситуации, переводя пароль в хещ-код еще в браузере пользователя перед
отправлением данных. Но это затруднительно, поскольку вам придется
отыскать соль, связанную с введенным паролем, прежде, чем вы сможете
вычислить корректный хещ-код. Хорощим компромиссом будет являться
использование безопасного НТТР-соединения при отправке пароля от бра¬
узера к приложению.
!!Если вы можете прочитать пароли — сможет и взломщик
PBKDF2 (tools.ietf.org/html/rfc2898) —широко используемый стандарт уси¬
ления защиты ключей.
Bcrypt (bcrypt.sourceforge.net/) реализует адаптивные хеш-функции.

Рубрика: О программировании | Добавить комментарий

Первичный ключ таблицы

Вопрос о том, что нужен ли первичный ключ таблицы иногда задают программисты.

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

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

Если используется внешний ключ по нескольким  полям и уникальность требуется задать по внешнему ключу, то с помощью слова unique  это задается.

Составной первичный ключ — это ключ по нескольким полям и внешний ключ, которые ссылается на составной должен сам быть составным.

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

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

 

Рубрика: О программировании | Добавить комментарий

Ответы к упражнениям по SQL

В наборе записей из таблицы PC, отсортированном по столбцу code (по возрастанию)
найти среднее значение цены для каждой шестерки подряд идущих ПК.
Вывод: значение code, которое является первым в наборе из шести строк, среднее значение цены в наборе.
select code, sum1 from
(
select row_number() over (order by code
)  as rn,
first_value(code) over (order by code
ROWS between  CURRENT ROW AND  5 following ) as code,
avg(price) over (order by code
ROWS between  CURRENT ROW AND  5 following ) as sum1,
(select count(*) from PC) cnt

from PC ) tt
where rn + 6 <= cnt + 1
ORDER BY code

 

http://www.sql-ex.ru/learn_exercises.php#answer_ref

Упражнение  57(3)
Для классов, имеющих потери в виде потопленных кораблей
и не менее 3 кораблей в базе данных, вывести имя класса и число потопленных кораблей.

select s.class, Count(*) —s.class
from Outcomes o
inner join Ships s
on o.ship = s.name

where o.result = ‘sunk’
—and s.class is not null
and s.class in ( select c.class
from Classes c
inner join Ships s
on s.class = c.class
group by c.class
having count(*)>=3)

group by s.class

 

Упражнение  58(3)
Для каждого типа продукции и каждого производителя из таблицы Product c точностью до двух десятичных знаков найти процентное отношение числа моделей данного типа данного производителя к общему числу моделей этого производителя.
Вывод: maker, type, процентное отношение числа моделей данного типа к общему числу моделей производителя

select tt.maker,
t.type,

isnull(ttt.prc,0) as prc
from
(select distinct type from Product) t
cross join
(select distinct maker from Product) tt

left join
(select p.maker, p.type, tt.cntTotal, cast (Count(*) * 100 / cast (tt.cntTotal as float) as decimal(18,2)) as prc
from Product p
inner join
( select maker, Count(type) as cntTotal
from Product
group by maker ) tt
on tt.maker = p.maker
group by p.maker, p.type, tt.cntTotal) ttt
on ttt.maker = tt.maker
and ttt.type = t.type
order by tt.maker

 

Задание: 75 (4)

http://www.sql-ex.ru/learn_exercises.php#answer_ref

Для каждого корабля из таблицы Ships указать название ближайшего по времени сражения из таблицы Battles,
в котором корабль мог бы участвовать после спуска на воду. Если год спуска на воду неизвестен, взять последнее по времени сражение.
Если нет сражения, произошедшего после спуска на воду корабля, вывести NULL вместо названия сражения.
Замечание. Считать, что корабль может участвовать в сражении, которое произошло в год спуска на воду корабля.
Вывод: имя корабля, год спуска на воду, название сражения

 

select tt.name,
tt.launched,
case when launched is null then
(select top 1 name
from Battles
order by date desc)
else tt.battle
end as battle
from
(
select s.name,s.launched,
(select top 1 name
from Battles
where date >= cast (s.launched as varchar(4)) + ’0101′
order by date asc) battle
from Ships s
) tt

 

В наборе записей из таблицы PC, отсортированном по столбцу code (по возрастанию)
найти среднее значение цены для каждой шестерки подряд идущих ПК.
Вывод: значение code, которое является первым в наборе из шести строк, среднее значение цены в наборе.
select code, sum1 from
(
select row_number() over (order by code
)  as rn,
first_value(code) over (order by code
ROWS between  CURRENT ROW AND  5 following ) as code,
avg(price) over (order by code
ROWS between  CURRENT ROW AND  5 following ) as sum1,
(select count(*) from PC) cnt

from PC ) tt
where rn + 6 <= cnt + 1
ORDER BY code

 

 

Рубрика: О программировании | Добавить комментарий

Использование инструкции MERGE

Инструкция MERGE выполняет слияние данных из исходной таблицы и табличного выражения и целевой таблицы с помещением в целевую таблицу.
MERGE INTO <target table>  AS TGT
USING <SOURCE TABLE> AS SRC
ON  <merge predicate>
WHEN MATCHED  — если строке источника соответствует строка в целевой таблице
THEN  — какое действие предпринять
WHEN NOT MATCHED — если строке источника не соответствует строка в целевой —                                             —таблице
THEN INSERT…             — какое действие предпринять
WHEN NOT MATCHED BY SOURCE
THEN

 

Рубрика: Разное | Добавить комментарий

Объект последовательности и IDENTITY

Свойства столбца IDENTITY и объект последовательности служат для генерации последовательность чисел. Рассмотрим их более подробнее.

При определении IDENTITY можно задать начальное значение и приращение, по умолчанию 1,1. Только один столбец может иметь свойство  IDENTITY.
Если при вставке надо указать собственные значение, то установить свойство SET IDENTITY INSERT <table> to ON.
Получить последнее значение можно с помощью:
SCOPE IDENTITY — возвращает последнее значение идентификатора в вашем сеансе в данной области
@@IDENTITY — возвращает последнее значение идентификатора вне зависимости от области
IDENT_CURRENT(‘<table>’) - возвращает последнее значение сгенерированная по таблице
TRANCATE TABLE <table> - удаляет данные из таблицы и сбрасывает значение последовательности.
DBCC CHECKIDENT(‘<table>’,RESEED,4) — устанавливает 4.
IDENTITY не гарантирует уникальность и отсутствие разрывов, не поддерживает цикличность.  Надо добавить UNIQUE или PRIMARY KEY.
Недостатки IDENTITY:
- нельзя обновить значение  IDENTITY
- TRANCATE сбрасывает свойство идентификатора
Объект последовательности CREATE SEQUENCE  создает независимый объект от таблицы с параметрами:
MINVALUE 1. Если включено CYCLE и не указано MINVALUE то после переполнения будет установлено отрицательное значение.
Получить следующее значение SELECT NEXT VALUE FOR <table>
Если установить новое значение, то ALTER SEQUENCE <..> RESTART WITH <…>

 

 

Рубрика: О программировании | Добавить комментарий

Ограничения по умолчанию

Ограничения по умолчанию
CREATE TABLE Production.Products
(
productid    INT          NOT NULL IDENTITY,
productname2  NVARCHAR(50) NOT NULL,
CONSTRAINT DFT_Products_unitprice DEFAULT(0),
discontinued1 BIT          NOT NULL
CONSTRAINT DFT_Products_discontinued DEFAULT(0),

);

Используется в основном когда поле не разрешает значение null, но не хотите, чтобы обязательно указывалось при вставке INSERT.

Ограничение по уникальности:

ALTER TABLE Production.Products

ADD  CONSTRAINT U_Productname UNIQUE (productname);

Рубрика: Разное | Добавить комментарий

Проверочные ограничения

Например:
ALTER TABLE Production.[Products]  WITH CHECK
ADD  CONSTRAINT [CHK_Products_unitprice]
CHECK  (unitprice>=0);

если в столбце есть значения null, то следует иметь в виду, что  null проходит ограничение  unitprice>=0 и unitprice<0.

Рубрика: Разное | Добавить комментарий

Внешний ключ

Внешний ключ — это столбец или комбинация столбцов в одной таблице, которые служат ссылкой для поиска в другой таблице. Во второй таблице столбец должен быть уникальным (первичным ключом или ограничение уникальности). Значение в первой таблице может иметь дубликат. Отношение внешнего ключа даст возможность получить связанные данные во второй таблице.
Например,
ALTER TABLE Production.[Products]  WITH CHECK
ADD  CONSTRAINT [FK_Products_Categories] FOREIGN KEY(categoryid)
REFERENCES Production.Categories (categoryid)

 

Рубрика: Разное | Добавить комментарий

Группирование и оконные функции

Для работы с несколькими наборами группирования есть следующая конструкция:
GROUP BY GROUPING SETS
(
( поле1, поле2 ),
( поле1 ))

Результатом будет запрос возвращающие данные по первому набору группирования (поле1, поля2) конструкция union all запрос возвращающий данные по второму набору (поле1), а в поле 2 будет null.
Пустой набор группирования определяет группу, содержащую все строки для вычисления итоговой функции.

Оператор сведения PIVOT разворачивает возвращающее табличное значение выражение, следующим образом:

WITH PivotData AS
( SELECT <grouping column>
<spreading column>
<aggregate column>
FROM <source table>
)
SELECT <select list>
FROM PivotData
PIVOT (aggregate function(aggr. column) FOR <spreading column> IN (distinct spreading values)) AS P

Т.е. чтобы получить сколько по каждому … значению агрегатной функции.

Статистические оконные функции применяются к окну строк, определенному предложением OVER

Например, SUM (val) OVER (PARTITION BY custid) — итоговая сумма для каждого конкретного клиента.
Оконные функции поддерживаю кадрирование (ROWS и RANGE) и экстент оконного кадра (смещение границ по отношению к текущей строке).
UNBOUNDED PRECEDING или FOLLOWING — начало и конец секции
CURRENT ROW — текущая строку
<n> ROWS  PRECEDING или FOLLOWING означающие n строк перед или после текущей строки соответственно.
Сумму нарастающим итогом в таблице по полю date1 по датам в таблице

select SUM(PersonID)
over (order by PersonID rows between unbounded preceding and current row) as
  total
from zakaz01

Поля таблицы zakaz01 описаны в статье «Подзапросы MSSQL»

Ранжирующие оконные функции:
ROW_NUMBER ()  — вычисляет уникальный последовательный номер
RANK () — возвращает число строк в секции, которые имеют более низкое значение сортируемого столбца,
DENSE_RANK () — количество предшествующих отличающих значений упорядочиваемого столбца
NTILE () — организовать строки внутри секции в группы.

Оконные функции смещения.
LAG — предыдущее значение,
LEAD — следующее значение.

 

 

 

 

 

Рубрика: О программировании | Добавить комментарий

Подзапросы MSSQL

Подзапросы можно условно разделить на независимые и зависимые.

Пусть требуется вывести сумму нарастающим итогом в таблице по полю date1 по датам:

CREATE TABLE zakaz01
(
PersonID int,
date1 datetime
);
Заполним таблицу строками:

insert into zakaz01 (PersonID,date1) values (1,convert(datetime, ’20140101 06:00′, 120));
insert into zakaz01 (PersonID,date1) values (2,convert(datetime, ’20140101 07:00′, 120));
insert into zakaz01 (PersonID,date1) values (3,convert(datetime, ’20140101 08:00′, 120));
insert into zakaz01 (PersonID,date1) values (4,convert(datetime, ’20140101 09:00′, 120));
insert into zakaz01 (PersonID,date1) values (5,convert(datetime, ’20140101 10:00′, 120));
Тогда запрос даст искомый ответ:

select (select sum(PersonID)
from zakaz01
where date1<=z1.date1) as sumF

from zakaz01 z1

Пример №2 Вывести последнюю запись меньшую заданной даты.

select PersonID
from zakaz01
where date1 = ( select max(date1)
from zakaz01
where date1<= convert(datetime, ’20140101 07:30′))

Подзапрос может быть скалярным если он возвращает одно значение или NULL, если множество значений то используется IN или NOT IN

Коррелированные (связанные) подзапросы
В связанных подзапросах можно использовать EXISTS или NOT EXISTS

Есть также обобщенные тадичные выражения (Common Table Expression)

WITH <Cte name>
AS ( <inner_query>)
<outer_query>

WITH C1 AS
(SELECT …
FROM T1
WHERE …),
C2 AS
(SELECT …
FROM C1
WHERE …)
SELECT …
FROM C2
WHERE …

Есть операторы CROSS APPLY, OUTER APPLY которые применяют правое табличное выражение к левому. Если правое будет пусто, то левые строки не возвращаются для первого оператора или возвращаются с NULL для второго. Эти операторы можно использовать вместо курсора.

Рубрика: Разное | Добавить комментарий