Введение в Transact-SQL и SQL Query Аnalyzer

Оператор INSERT

Оператор INSERT применяется для вставки строки данных в таблицу или в представление. Например, если вы хотите добавить нового заказчика (customer) в нашу таблицу-пример Customer_Data, то оператор INSERT может выглядеть так:

INSERT INTO Customer_Data 
(customer_id, first_name, last_name, phone) 
VALUES (777, 'Frankie', 'Stein', '4895873900')

Обратите внимание, что во второй строке первого оператора SQL помещен список имен колонок. Этот список определяет, в какие именно колонки будут помещены данные (данные вводятся в той последовательности, как они указаны). Так, первое значение данных будет помещено в первую колонку из списка, в customer_id; второе значение будет помещено во вторую колонку из списка, и т.д. Мы перечислили значения данных в том же порядке, в каком были заданы колонки при создании таблицы, поэтому можно вообще не указывать имена колонок и применить вот такой оператор INSERT:

INSERT INTO Customer_Data 
VALUES (777, 'Frankie', 'Stein', '4895873900')

Оператор SELECT

Оператор SELECT применяется для извлечения данных из таблицы или из таблиц. Извлекаемые данные определяются перечисленными колонками и предложением WHERE. Предположим, например, что нам надо извлечь значения из колонок customer_id и first_name нашей таблицы Customer_Data, и эти данные нужны лишь для строк со значением Frankie в поле first_name. Тогда нужно применить такой оператор SELECT:

SELECT customer_id, first_name FROM Customer_Data 
WHERE first_name = 'Frankie' 
Если критерию из оператора SELECT будет соответствовать лишь одна строка, 
то результат может быть таким:
customer_id      		first_name 
--------------		-------------
777                    				Frankie

Оператор UPDATE

Оператор UPDATE применяется для обновления (т.е. изменения) значений в одной или нескольких строках таблицы. Например, допустим, заказчик Frankie Stein позвонил и попросил изменить его имя в записях базы данных на Franklin. Эту задачу выполнит такой оператор UPDATE:

UPDATE Customer_Data 
SET first_name = 'Franklin'
WHERE last_name = 'Stein' and customer_id=777

В предложении WHERE указан идентификационный номер заказчика ( customer_id ), чтобы не допустить изменения имен других заказчиков с такой же фамилией Stein (если такие заказчики имеются), имена будут изменены лишь у заказчика с идентификационным номером 777.

Оператор DELETE

Оператор DELETE применяется для удаления из таблицы одной или нескольких строк с данными. Можно удалить даже все строки таблицы. Чтобы удалить все строки из нашей таблицы-примера Customer_Data, можно воспользоваться каким-либо одним из двух показанных ниже операторов DELETE:

DELETE FROM Customer_Data

или

DELETE Customer_Data

Ключевое слово FROM перед именем таблицы в операторе DELETE не обязательно, эти два оператора ничем не отличаются. Для удаления из таблицы Customer_Data строк, у которых значение колонки customer_id меньше, чем 100, применяется такой оператор:

DELETE FROM Customer_Data 
WHERE customer_id < 100

Теперь, когда вы вкратце познакомились с операторами языков DDL и DML, входящих в состав SQL, давайте рассмотрим язык T-SQL.

Что такое T-SQL

T-SQL – это усовершенствование стандартного языка программирования SQL. Первоначальный, основной SQL применяется для взаимодействия между приложениями и SQL Server. В T-SQL имеются все возможности языков DDL и DML стандартного SQL, а кроме этого имеются также расширенные функции, системные хранимые процедуры и конструкции для программирования (такие, как IF and WHILE ), обеспечивающие гораздо большую гибкость программирования. По мере выхода новых версий SQL Server возможности T-SQL растут.

Обзор новых возможностей T-SQL

В T-SQL для Microsoft SQL Server 7 появились многочисленные добавления и улучшения, в том числе новые хранимые процедуры, системные таблицы, функции, типы данных, операторы и новые возможности для старых операторов. Все было сохранено и в SQL Server 2000, и мы сейчас дадим краткий обзор этих новых возможностей (если вы не знаете о них по опыту своей работы с SQL Server 7). Однако их слишком много, чтобы мы смогли дать подробные описания, поэтому мы рассмотрим лишь несколько примеров для каждой их разновидности.

Системные хранимые процедуры

В SQL Server имеются системные хранимые процедуры, с помощью которых можно выполнять администрирование и другие задачи, требующие обновления системных таблиц и извлечения информации из системных таблиц. Системные хранимые процедуры инсталлируются вместе с SQL Server, их имена начинаются с sp_ или с xp_. Эти процедуры хранятся в базе данных master (главной системной базе данных) (а также базе msdb. – Прим. ред.), их владелец – системный администратор, но многие из них могут запускаться из любой пользовательской базы данных, извлекая при этом информацию из системных таблиц этой базы данных. Когда вы исполняете системную хранимую процедуру, она работает с системными таблицами текущей базы данных.

Многие системные хранимые процедуры появились в SQL Server 7, сейчас они по-прежнему доступны и в SQL Server 2000. В табл. 13.1 перечислены некоторые из этих системных процедур, они могут вам пригодиться.

Некоторые из этих хранимых процедур не только немедленно выдают информацию, но могут и сохранять для дальнейшего применения важную информацию о пользовательских базах данных. Например, процедуры, возвращающие информацию о базе данных пользователя, могут оказаться полезными при исполнении в сценариях T-SQL, когда выдаваемая ими информация будет сохраняться в файле. Вы можете сохранять информацию, выдаваемую после запуска хранимых процедур sp_helpfile, sp_helpfilegroup и sp_helpdb, примененных к нужной базе данных (процедура sp_helpdb, хоть и старая, но по-прежнему пользуется популярностью). Это пригодится в случае, если придется воссоздавать базу данных и понадобится информация о первоначально созданных и сконфигурированных файлах, группах файлов и опциях базы данных. Список остальных новых системных процедур, появившихся в SQL Server 7, вы найдете в Books Online, в теме "New Features in Transact-SQL" (Новые возможности в Transact-SQL).

Системные таблицы

Системные таблицы служат для хранения информации о конфигурации SQL Server и хранения заданных для баз данных определений объектов, пользователей и полномочий доступа. Каждая пользовательская база данных имеет свои собственные системные таблицы, в которых хранится информация для этой базы данных. Системные таблицы, хранящие информацию о конфигурации на уровне сервера, хранятся только в базе данных master (главной системной базе данных). Для доступа к системным таблицам нужно применять системные хранимые процедуры, а не осуществлять доступ непосредственно. Список новых системных таблиц, появившихся первоначально в SQL Server 7, вы найдете в Books Online, в теме "New Features in Transact-SQL". Ниже перечисленные некоторые интересные системные таблицы.

Функции

При помощи встроенных функций SQL Server возможно быстрое и удобное решение некоторых задач. Некоторые новые функции появились в SQL Server 7, и они остались доступными и в SQL Server 2000. Зная об имеющихся функциях, вам будет проще программировать приложения SQL Server. Полный список новых функций вы найдете в Books Online, в теме "New Transact-SQL Functions" (Новые функции Transact-SQL). Ниже приведены описания лишь нескольких из новых функций, которые могут вам пригодиться:

Типы данных

В SQL Server 7 появилось несколько новых типов данных, а некоторые из имеющихся типов данных получили новые размеры. В SQL Server 2000 помимо этих новшеств появилось еще три новых типа данных. Про большинство типов данных было рассказано в лекции 10. Ниже дан список изменений, относящихся к типам данных, появившихся в SQL Server 7 и оставшихся и в SQL Server 2000:

А ниже перечислены три новых типа данных, появившихся в SQL Server 2000:

Операторы

В SQL Server 7 появилось много новых операторов T-SQL, а имеющиеся операторы приобрели новые возможности, и все это вошло и в SQL Server 2000. Эти операторы соответствуют некоторым из новых возможностей, появившихся в SQL Server 7. Например, оператор ALTER DATABASE получил следующие новые опции для работы с файлами и группами файлов: MODIFY FILE, ADD FILEGROUP, MODIFY FILEGROUP, REMOVE FILE и REMOVE FILEGROUP. А для групп файлов новый оператор DBCC CHECKFILEGROUP проверяет размещение и структурную целостность всех таблиц в заданной группе файлов.

В SQL Server 7 и SQL Server 2000 появилось еще два оператора DBCC – DBCC SHRINKFILE и DBCC SHRINKDATABASE. Первый из них сжимает заданный файл данных, уменьшая его размер, а второй оператор сжимает все файлы в заданной базе данных, освобождая неиспользуемое место на диске.

У SQL Server 7 и SQL Server 2000 была усовершенствована архитектура для резервного копирования и восстановления. Новый оператор BACKUP позволяет выполнять полные и частичные резервные копирования базы данных и журнала, а новый оператор RESTORE позволяет восстанавливать базы данных и журналы после этого резервного копирования. Эти операторы применяются вместо операторов DUMP и LOAD из старых версий SQL Server. Полный список новых операторов и опций, появившихся в SQL Server 7 и SQL Server 2000, вы найдете в Books Online, в теме "New Features in Transact-SQL". Более подробно о резервном копировании и о восстановлении после резервного копирования будет рассказано в лекциях 32 и 33.

Как применять T-SQL

T-SQL можно применять не только в программах ваших приложений (эта тема выходит за рамки нашей книги), вы можете также исполнять интерактивные операторы T-SQL, пользуясь какой-нибудь из трех утилит – ISQL, OSQL или Query Analyzer; кроме того, вы можете создавать и исполнять сценарии SQL.

Утилита ISQL

Утилита ISQL взаимодействует с SQL Server через API-интерфейс DB-LIB; с ее помощью можно исполнять интерактивные операторы T-SQL, хранимые процедуры и файлы сценариев. API-интерфейс DB-LIB остался на уровне функциональности SQL Server 6.5, поэтому приложение ISQL не поддерживает некоторые возможности SQL Server 2000. Например, ISQL не может извлекать данные из типа данных ntext (в кодировке Unicode).

Утилита OSQL

Утилита OSQL появилась в SQL Server 7 и осталась в SQL Server 2000 в качестве замены для ISQL. Утилиты ISQL и OSQL очень во многом сходны, но OSQL для коммуникации с SQL Server применяет API-интерфейс ODBC (Open Database Connectivity), а не DB-LIB, и OSQL поддерживает все возможности SQL Server 2000. Во всем остальном OSQL и ISQL функционируют одинаково. SQL Server 2000 поддерживает обе этих утилиты, однако, чтобы не столкнуться с проблемами, о которых мы упомянули, следует пользоваться OSQL, а не ISQL.Чтобы исполнять OSQL из окна с приглашением командной строки (из окна MS-DOS), нужно просто запустить программу OSQL.exe с соответствующими параметрами, например, так:

osql –U имя_пользователя –P пароль –S имя_сервера

Когда OSQL установит соединение с SQL Server, появится такое приглашение с номером:

1>

После этого приглашения можно вводить операторы T-SQL, например, такой оператор:

1>    		sp_helpdb master 
2>    		go

В результате его выполнения будет выдана информация о базе данных master. Ключевое слово go не является оператором T-SQL, это – команда, распознаваемая ISQL, OSQL и Query Analyzer и обозначающая завершение пакета операторов T-SQL. Вывод от исполнения таких интерактивных запросов будет выдаваться в окне с приглашением командной строки.

Если при наборе командных строк OSQL вы допустили опечатку, то, воспользовавшись командой RESET, можно снова вернуться к приглашению первой строки:

1>    		sp_helpbd 
2>    		reset 
1>    		sp_helpdb  
2>    		go

Для завершения работы с утилитой OSQL введите команду QUIT или EXIT. Команду или запрос, исполняющуюся в текущий момент времени, можно завершить, нажав Ctrl+C (выход из утилиты OSQL при этом не произойдет).

Утилита OSQL имеет и другие параметры кроме -U, -P и -S. Полное описание всех параметров и дополнительную информацию об утилите OSQL вы найдете в Books Online, в теме "osql Utility".

Query Analyzer

Query Analyzer применяется для исполнения операторов или сценариев T-SQL из графического пользовательского интерфейса и для получения результатов в форматированном виде. Query Analyzer обладает также некоторыми средствами для анализа индексов запросов. Некоторые люди предпочитают применять Query Analyzer, а не запускать операторы в окне "приглашения MS-DOS". Для работы с Query Analyzer выполните следующие действия:

Сценарии T-SQL

Создав свои собственные сценарии, вы получите замечательную возможность для многократного запуска операторов и хранимых процедур T-SQL. Сценарий (script) – это просто файл, в котором содержатся операторы T-SQL, которые нужно запускать. В скрипте может быть как один оператор, так и последовательность операторов. Мы сразу дадим одну рекомендацию: создавая сценарии T-SQL, давайте именам их файлов расширение .sql, тогда вам будет легче находить эти файлы.

Ниже дан пример кода простого сценария:

use MyDB 
go 
sp_helpdb MyDB 
go 
sp_helpfilegroup 
go 
sp_spaceused Customer_Data 
go 
sp_spaceused Product_Info 
go

Этот сценарий вызывает несколько системных хранимых процедур, собирающих разнообразную информацию о базе данных MyDB и ее файлах, группах файлов и таблицах (Customer_Data и Product_Info).

Предположим, что этот сценарий был сохранен в файле MyDB_info.sql. Тогда, чтобы запустить его из командной строки, вы можете воспользоваться опциями  -i и -o утилиты OSQL. Опция  -i вызывает запуск сценария, находящегося во входном файле ( input ), имя которого указано после опции. Опция  -o задает выходной файл ( output ), имя которого указано после опции, в него будут помещены результаты. Пусть выходной файл у нас будет иметь имя MyDB_info.out. Давайте также применим опцию -e, тогда операторы T-SQL из входного файла будут отображаться (echo) также и в выходном файле, что облегчит понимание выходного файла. Например, чтобы исполнить наш сценарий, будучи системным администратором, надо дать следующую команду в командной строке:

osql –U sa  –P  –i MyDB_info.sql  –o MyDB_info.out  –e

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

Есть и другой способ запуска сценариев, при котором вам не понадобится каждый раз набирать с клавиатуры команду osql -u ....Надо создать .cmd-файл и поместить в него эту команду. Так, можно назвать этот файл MyDB_info.cmd и поместить в него вышеприведенную команду osql -u .... Теперь вы можете не только запустить команду MYDB_INFO из командной строки, но и дважды щелкнуть мышью на имени файла MyDB_info.cmd в Проводнике Microsoft Windows.

Сценарии можно запускать и в Query Analyzer. Чтобы запустить наш сценарий MyDB_info.sql, откройте этот файл, выбрав в Query Analyzer команду Open в меню File, после этого в верхней панели появится код сценария. Нажмите на кнопку Execute Query или нажмите клавиши Ctrl+E, и операторы сценария исполнятся. Вывод от операторов сценария будет выдаваться в соответствии с порядком исполнения операторов (рис. 13.5). Обратите внимание, что на рис. 13.5 две верхние таблицы результатов выданы хранимой процедурой sp_helpdb.

Заключение

В этой лекции вы познакомились с основами языков SQL и T-SQL, увидели примеры простых операторов DDL и DML. Теперь вы умеете запускать операторы T-SQL, причем разными способами – при помощи ISQL, OSQL, Query Analyzer и из сценариев T-SQL. (Об использовании T-SQL см. в лекциях 14, 15 и 25.)