2. Transact-SQL Работа с данными

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

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

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

На данный момент мы уже создали базу данных, но для тестирования примеров необходимо наполнить ее данными. Добавление данных в таблицы мы будем рассматривать чуть позже, поэтому пока что я рекомендую выполнить запрос из файла Chapter2/fill_data.sql.

Но прежде чем мы начнем изучение операторов SQL для работы с данными, я хочу вам показать два оператора USE и GO. Они поддерживаются не во всех программах и только с MS SQL Server (с Access работать не будут). Эти операторы точно работают в Query Analizer, который я рекомендую использовать для тестирования запросов к серверу MS SQL Server.

Оператор USE позволяет выбрать нужную базу данных. Например, вы хотите выполнить запрос, который будет работать с объектами базы данных master. Чтобы выбрать в качестве текущей базы master нужно выполнить:

USE master

С помощью GO мы говорим, что необходимо начать выполнение запроса выше этого оператора до начала перехода к следующим операторам. Например:

CREATE DATABASE dbNew
GO
CREATE DATABASE new

Если бы не было GO, то программа клиент отправила бы оба оператора создания базы данных одним блоком. Но у нас есть GO, поэтому будет отправлено серверу два блока с запросом – текст до оператора и после. Вы можете разбивать файл сценария на любое количество блоков с помощью GO.

2.1. Выборка данных

Оператор выборки достаточно большой и сложный. На его рассмотрение у нас уйдет очень много времени, потому что именно это является основой базы данных. Я специально сегодня посчитал SQL запросы в одном из своих проектов, и количество запросов на получение данных превысили все остальные в 5 раз. И это притом, что я стараюсь писать достаточно универсальные программы, и запросы решают несколько задач одновременно.

Итак, для получения данных от сервера используется оператор SELECT. В самом общем и упрощенном виде он показан в листинге 2.1.

Листинг 2.1. Сокращенный формат оператора SELECT

SELECT select_list 
[ INTO new_table ] 
FROM table_source 
[ WHERE search_condition ] 
[ GROUP BY group_by_expression ] 
[ HAVING search_condition ] 
[ ORDER BY order_expression [ ASC | DESC ] ] 

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

• SELECT – основной и обязательный оператор, после которого идет перечисление полей, необходимых для получения от сервера;
• INTO – необязательная секция, которая описывает таблицу, в которую необходимо записать результат выполнения. Чаще всего результат передается в памяти, но может быть сохранен для хранения в новой таблице;
• FROM – в этой секции идет описание таблиц, из которых необходимо прочитать данные;
• WHERE – параметры поиска, ограничения выводимых данных;
• GROUP BY – здесь можно описать список полей, по которым необходимо сгруппировать данные;
• HAVING – в этой секции можно указать дополнительные ограничения поиска, чаще всего, они необходимы для ограничения параметров группировки;
• ORDER BY – параметры сортировки, а точнее поля, по которым необходимо отсортировать результат.

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

Листинг 2.2. Полный вариант оператора SELECT

SELECT statement ::= 
    < query_expression > 
    [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] } 
        [ ,...n ]    ] 
    [ COMPUTE 
        { { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ ,...n ] 
        [ BY expression [ ,...n ] ] 
    ] 
    [ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT } 
            [ , XMLDATA ] 
            [ , ELEMENTS ]
            [ , BINARY base64 ]
        } 
] 
    [ OPTION ( < query_hint > [ ,...n ]) ] 

< query expression > ::= 
    { < query specification > | ( < query expression > ) } 
    [ UNION [ ALL ] < query specification | ( < query expression > ) [...n ] ] 

< query specification > ::= 
    SELECT [ ALL | DISTINCT ] 
        [ { TOP integer | TOP integer PERCENT } [ WITH TIES ] ] 
        < select_list > 
    [ INTO new_table ] 
    [ FROM { < table_source > } [ ,...n ] ] 
    [ WHERE < search_condition > ] 
    [ GROUP BY [ ALL ] group_by_expression [ ,...n ] 
        [ WITH { CUBE | ROLLUP } ]
    ]
    [ HAVING < search_condition > ] 

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

2.2. Выборка данных

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

SELECT vcFamil, vcName, vcSurName
FROM tbPeoples

Все запросы начинаются с этой оператора SELECT, что означает - "выбрать". После команды перечисляются имена полей, которые необходимо выбрать из базы данных. Оператор FROM указывает на таблицу (или несколько таблиц) из которых нужно получить указанные поля.

Теперь посмотрим, что и откуда выбирает вышеприведенный запрос. Выборка происходит из таблицы tbPeoples, выбираются три поля: "vcFamil", "vcName", "vcSurName". Результат выполнения этой команды в Query Analyzer можно увидеть на рисунке 2.1.

Рис. 2.1. Результат выборки полей фамилии, имени и отчества

Ничего сложного тут нет. Такой маленький запрос возвращает нам поля с фамилией, именем и отчеством.

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

SELECT 1

В результате мы увидим на экране таблицу из одной строки и одной колонки, где будет находиться только число 1.

Если нужно вывести все поля, то можно перечислить все их имена или воспользоваться значком "*", что соответствует всем полям таблиц, перечисленных в секции FROM. Следующий запрос выведет все поля из таблицы tbPeoples:

SELECT * 
FROM tbPeoples

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

2.2.1. Полный путь

Полный формат обращения к таблице выглядит следующим образом:

База_Данных.Владелец.Таблица

Но если вы подключены к нужной базе данных, то можно сразу же указывать имя таблицы. Попробуйте подключиться к базе данных Master и выполнить этот же запрос. Результатом будет ошибка, потому что таблица tbPeoples в базе данных Master будет не найдена. Чтобы находясь в одной таблице, обратиться к другой, необходимо написать ее полный формат:

SELECT * 
FROM FlenovSQLBook.dbo.tbPeoples

Зедесь:

• FlenovSQLBook – имя базы данных;
• dbo – владелец базы данных;
• tbPeoples – имя таблицы.

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

SELECT * 
FROM FlenovSQLBook..tbPeoples

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

Чтобы точно быть уверенным, что объект создан от имени dbo, а не от конкретного пользователя базы данных, можно указывать полное имя и при создании объекта. Например, следующий пример создает таблицу Peoples, явно указывая владельца:

CREATE TABLE dbo.Peoples
(
 idPeoples int
)

Если вы находитесь в базе данных Master, а таблицу нужно создать в базе данных TestDB, то можно указать полный путь, включая базу данных:

CREATE TABLE TestDB.dbo.Peoples
(
 idPeoples int
)

Если вы находитесь в той же базе данных, что и объект, но объект принадлежит не dbo, то базу данных указывать не надо, а вот владельца указать необходимо:

SELECT * 
FROM Flenov.tbPeoples

В данном случае Flenov – это имя владельца.

Если вы находитесь в той же базе данных, что и объект, и при этом объект принадлежит dbo, то сразу указываем имя таблицы:

SELECT * 
FROM tbPeoples

2.2.2. Ограничение вывода строк

В SQL существует два ключевых слова, которые характеризуют параметры вывода информации: ALL и DISTINCT. Параметр ALL означает, что выводить необходимо все строки, а DISTINCT означает, что ненужно выводить повторяющиеся строки. Следующий запрос выведет все возможные имена людей из таблицы:

SELECT vcName
FROM tbPeoples

Но у нас в таблицы очень много записей, у которых в поле "vcName" находиться одно и то же имя. Например, если вы для заполнения базы данных данными использовали запрос с компакт диска, то можете увидеть, что имя Андрей повторяется пять раз. Зачем нам это в результате? Чтобы отбросить лишнее, указываем после оператора SELECT оператор DISTINCT:

SELECT DISTINCT vcName
FROM tbPeoples

Если в таблице встретится две строки с одинаковым именем, то запрос выведет это имя только один раз. Если не задать параметр DISTINCT, то считается, что надо использовать параметр ALL.

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

SELECT DISTINCT vcFamil, vcName
FROM tbPeoples

Здесь мы просим вывести два поля "vcFamil" и "vcName" и при этом, результат не должен содержать одинаковых строк, то есть две строки не должны иметь одновременно одинаковое имя и фамилию. В моей таблице совпадений по двум полям нет ни в одной строке, поэтому в результат попали все 19 строк таблицы.

Мне не раз приходилось работать с таблицами, которые содержали до миллиона строк. Давайте немного посчитаем. Если одна строка будет занимать 100 байт, то общий размер таблицы будет равен размер строки умноженное на количество записей. Результат будет около 100Мб. Если для такой таблицы выполнить запрос SELECT *, то серверу придется по сети передать достаточно большой объем данных, а на клиенте понадобиться около 100Мб памяти для хранения результата запроса. Это достаточно много и ни каждый клиентский компьютер согласиться принять такое количество данных. Если на компьютере не хватит оперативной памяти, то он начнет тормозить.

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

Ограничение количества выводимых данных происходит с помощью оператора TOP, который ставиться сразу после SELECT, например, в следующем запросе мы запрашиваем только 10 первых строк результата:

SELECT TOP 10 *
FROM tbPeoples

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

А что если нам нужно получить определенный процент от результата? Например, нужно увидеть 50% всех строк таблицы? В этом случае, укажите число в процентах, а после числа необходимо написать оператор PERCENT:

SELECT TOP 50 PERCENT *
FROM tbPeoples

2.2.3. Псевдонимы полей

В нашей базе данных используются имена полей на английском языке. Дело в том, что далеко не все базы данных позволяют создавать поля на русском языке. Хотя Microsoft заботиться о нас, и позволяет создавать имена полей на родном языке, я привык давать имена, используя только латинские буквы, и вам советую то же самое. Почему? Проблемы кодировки решены еще не полностью и может возникнуть ситуация, когда вы встретитесь с ними. Особенно, если будете экспортировать данные в другие таблицы, такие как DBF.

А вот во время вывода данных с помощью запросов, можно давать вполне понятные имена. Это делается, с помощью ключевого слова AS. После имени поля ставиться это ключевое слово, за которым идет текст в одинарных кавычках, которых как раз и будет выводиться в заголовке поля:

SELECT vcFamil AS 'Фамилия', vcName AS 'Имя', 
   vcSurName AS 'Отчество', dDateBirthDay AS 'Дата рождения'
FROM tbPeoples

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

SELECT 1

В этом случае, результатом будет просто число единица, которое не является полем и не может иметь заголовка. В некоторых программах в качестве заголовка для такого поля будет просто пустое поле, а в SQL Query Analyzer в заголовке будет (No column name). Таким образом, с помощью псевдонима мы можем дать имя полю, у которого до этого вообще не было имени. Например:

SELECT 1 AS Num

Теперь в качестве результата будет поле с именем Num, а в единственной строке в этом поле можно будет увидеть число 1.