ТемаVi. Оптимизация запросов и основы sql

1. Оптимизация запросов

Оптимизатор запросов - специализированная программа, предназначенная для выбора наилучшего способа выполнения запроса. Оптимизатор запросов вычисляет стоимость запросов: затраты процессорного времени и количество операций ввода/вывода для каждого возможного способа выполнения за­проса.

Затем выбирается вариант с минимальной стоимостью.

Примеры запросов с вычисляемым аргументом отбора:

1) SELECT*

FROM table1

WHERE col1*10=100

2) SELECT*

FROM table1

WHERE col1=100/10

3) SELECT*

FROM table1

WHERE CONVERT(CHAR(8), col1)=’10’

1) SELECT*

FROM table1

WHERE col1=CONVERT(INT, ’10’)

Функция CONVERT имеет следующую структуру:

CONVERT(тип(длина),выражение[,стиль]) и определяет явное преоб­разование типов данных, транслирует выражение одного типа в выражение другого типа или специальный формат.

2. Основные операторы sql в sql Server 2000

Существует огромное количество операторов SQL в SQL Server 2000 с помощью которых можно выполнить любую опера­цию от простого запроса к таблице до запроса на создание таб­лицы и от вызова простых хранимых процедур до назначения определенных прав доступа пользователю.

Краткое описание наиболее важных операторов SQL:

SELECT выбирает столбцы и строки данных из одной или нескольких таблиц базы данных;

INSERT добавляет строки в таблицу;

UPDATE модифицирует столбцы в существующих стро­ках таблицы;

CREATE TABLE создает новую таблицу на основе за­данной схемы таблицы;

SELECT INTO создает новую таблицу на ос­нове строк и столбцов, отобранных с помощью оператора SELECT.

Оператор SELECT включает следующие компоненты:

SELECT<список полей>	Задает столбцы отбираемых данных
FROM <список источников >	Задает источники для отбора строк
WHERE<условие отбора записей>	Задает критерий отбора записей
GROUP BY <условие группировки>	Для итоговых запросов задает столбец (столбцы) по которому(которым) группируются данные
HAVING <условие отбора групп>	Для итоговых запросов задает критерий, которому должно отвечать итоговое значение
ORDER BY <условие сортировки>	Задает порядок сортировки возвращаемых строк

Рассмотрим примеры операторов SQL для демонстрационной базы данных SQL Server – pubs, которая имеет следующую структуру (рассматривается фрагмент базы данных pubs):

Например, необходимо выбрать авторов не из штата Калифорния, отсортировать авторов по размеру контракта - по убыванию и по фамилии в алфавитном порядке:

SELECT au_id, au_lname, au_ fname, state, zip, contract

FROM authors

WHERE state < >’CA’

ORDER BY contract DESC, au_ lname

В MS SQL Server предусмотрено несколько вариантов сортировки, по умолчанию используется словарный порядок, не учитывающий регистр букв(Dictionary Order, Case-insensitive). Порядок сортировки определяется во время установки SQL Server и позже его переопределить нельзя.

3. Объединение таблиц в операторе select

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

SELECT authors. au_lname ,authors. au_ fname, titles.title

FROM authors, titleauthor, titles

WHERE authors.au_id = titleauthor.au_id AND

title author. title_id = titles. title_id

ORDER BY authors.au_lname, authors. au_fname, titles. title

Этот оператор связывает три таблицы БД, т.к. имена авторов находятся в authors, название книги – в titles, а связи между ними – в titleauthor.

Использование оператора JOIN для объединения таблиц.

Этот метод объединения имеет следующий синтаксис:

SELECT столбец1, столбец2, столбец3, …

FROM таблица1 <опе­ратор_объединения> таблица2

ON <критерий объединения>

Оператор JOIN определяет, каким образом должны быть возвращены строки из объединенных таблиц. Директива ON действует подобно директиве WHERE, указывая, в каких полях объединяемых таблиц должно проверяться равенство значений.

Операторы объединения таблиц:

CROSS JOIN возвращает каждую строку первой таблицы, объединенную с каждой строкой второй таблицы. Количество строк результата равно произведению числа строк каж­дой таблицы.

INNER JOIN возвращает все строки из каждой таблицы, удовлетворяющие критерию отбора, который задан в директиве WHERE, при условии совпадения значений в объединяемых по­лях, указанных в директиве ON.

LEFT[OUTER] JOIN - возвращает все строки таблицы, находящейся слева в объединении, удовлетворяющие критерию отбора директивы WHERE, и только те строки таблицы справа, в которых существует совпадение по объединяемым полям, задан­ным в директиве ON

RIGHT [OUTER] JOIN - возвращает все строки таблицы, находящейся справа в объединении, удовлетворяющие критерию отбора директивы WHERE, и только те строки таблицы слева, в которых существует совпадение по объединяемым полям, задан­ным в директиве ON.

FULL [OUTER] JOIN возвращает все строки из каждой таблицы, удовлетворяющие критерию отбора директивы WHERE, в которых нет совпадения по объединяемым полям, заданным в директиве ON.

Запишем предыдущий пример с помощью объединения:

SELECT a.au_lname, a. au_ fname, b. title

FROM (authors a INNER JOIN titleauthor c ON a.au_id = c.au_id) INNER JOIN titles b ON c.title_id =b.title_id

ORDER BY a.au_lname, a.au_fname, b.title

Внешнее объединение можно было бы использовать, на­пример, для поиска списка всех авторов и названий их книг, т.е. полей title_id- можно получить через внешнее объединение с таблицейtitleauthor.

Итоговые функции в операторах SQL – возвращают итоговые значения для заданных столбцов или выражений в форме итоговых сумм, количества записей, средних значений и т.д. Итоговая функция может возвращать одно значение для всех строк, отобранных запросом. Если же в оператор SQL была до­бавлена директива GROUP BY, то итоговое значение может вычисляться на каждом уровне группировки. В SQL Server нельзя применять функции Var(дисперсия) и StDev(стандартное от­клонение). Итоговые функции SQL Server: AVG, COUNT, MIN, MAX и SUM.

Примеры:

1) SELECT count(title) ’наименований’

FROM titles

В примере count применяется ко всем строкам таблицы

titles без группировки

2) SELECT count(title) AS ’наименований’