Лекция 14: Извлечение данных при помощи Transact-SQL

ESCAPE. При помощи ключевого слова ESCAPE можно задать сопоставление шаблону, содержащему сами знаки, служащие для обозначения метасимволов (т.е., сами символы ^, %, [, ] и _). Для этого вы должны задать после ключевого слова ESCAPE так называемый escape-символ, обозначающий, что символ, следующий за ним, следует понимать "как есть". Например, чтобы найти все строки из таблицы, имеющие символ подчеркивания в колонке title, можно применить такой запрос:

SELECT   		title 
FROM     		titles 
WHERE    		title LIKE "%e_%" ESCAPE "e"
GO

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

BETWEEN. Ключевое слово BETWEEN используется всегда в сочетании с ключевым словом AND и задает диапазон вхождения, применяемый как условие поиска. Ниже показан его синтаксис:

<проверяемое_выражение> BETWEEN <начальное_выражение> AND <конечное_выражение>

Если проверяемое_выражение больше или равно, чем начальное_выражение, и в то же время меньше или равно, чем конечное_выражение, то результатом условия поиска является булево значение TRUE, а в противном случае результатом условия поиска является FALSE.

Ниже приведен пример запроса, в котором BETWEEN применяется для поиска названий книг с ценой от 5 до 25 долларов:

SELECT   		price, title 
FROM     		titles 
WHERE    		price BETWEEN 5.00 AND 25.00
GO

Этот запрос вернет 14 строк.

Вместе с BETWEEN тоже можно применять NOT, тогда будут искаться строки, не входящие в заданный диапазон. Например, чтобы найти названия книг с ценами вне диапазона от 20 до 30 долларов (а именно, книг дешевле 20 и дороже 30 долларов), можно было бы применить такой запрос:

SELECT   		price, title 
FROM     		titles 
WHERE    		price NOT BETWEEN 20.00 AND 30.00
GO

В конструкции с ключевым словом BETWEEN проверяемое_выражение должно иметь такой же тип данных, как и начальное_выражение и конечное_выражение.

В последнем примере колонка price (цена) имеет тип данных money, поэтому и начальное_выражение, и конечное_выражение должны быть числами, сравнимыми с типом данных money, или для них должно быть возможным неявное преобразование в этот тип данных. Вы не можете применять в качестве проверяемого выражения значение из колонки price, а для начального и конечных выражений – символьные строки (т.к. они имеют тип данных char). Если вы все-таки сделаете это, то SQL Server выдаст сообщение об ошибке.

Примечание. SQL Server при необходимости будет автоматически преобразовывать типы данных, если только возможно неявное преобразование типов данных (implicit conversion). Неявное преобразование типов данных - это автоматическое преобразование одного типа данных в другой совместимый тип данных. После такого преобразования становится возможным сравнение. Например, если колонка, имеющая тип данных smallint, сравнивается колонкой, имеющей тип данных int, то перед выполнением сравнения SQL Server неявно преобразует тип данных первой колонки к типу данных int. Если неявное преобразование типов данных не поддерживается, то вы можете применять функцию CAST или CONVERT, чтобы выполнить явное преобразование колонки. Чтобы получить схему, показывающую, какие типы данных SQL Server будет преобразовывать неявно, а для каких необходимо явное преобразование, обратитесь к предметному указателю SQL Server Books Online, для темы CAST, а затем в диалоговом окне Topics Found (Найденные темы) выберите CAST and CONVERT (T-SQL).

Мы приведем еще один пример использования ключевого слова BETWEEN, которое на этот раз применяет в качестве условия поиска текстовые строки:

SELECT   		au_lname 
FROM     		authors 
WHERE    		au_lname BETWEEN "Bennet" AND "McBadden" 
GO

Этот запрос позволяет найти фамилии авторов, которые при сортировке в алфавитном порядке попали бы между фамилиями "Bennet" и "McBadden". Поскольку границы диапазона BETWEEN считаются входящими в проверяемый диапазон, то фамилии "Bennet" и "McBadden" тоже войдут в набор результатов запроса (эти фамилии имеются в таблице).

IS NULL. Ключевое слово IS NULL используется в условиях поиска, ищущих строки, содержащие null -значение в заданной колонке. Например, чтобы найти в таблице titles названия книг, у которых нет данных в колонке notes (примечания), т.е. когда значением колонки notes является NULL, можно воспользоваться таким запросом:

SELECT   		title, notes 
FROM     		titles 
WHERE    		notes IS NULL
GO
Этот запрос выдаст такой набор результатов:
title                                  	notes 
----------------------------------------	------
The Psychology of Computer Cooking     	NULL

Как видите, null -значения в колонке notes в наборе результатов отображаются словом NULL. Это слово не является содержимым колонки, оно служит просто обозначением того, что в данной колонке находится null-значение. (Помните, в "Создание таблиц баз данных" объяснялось, что null -значения – это неизвестные значения.)

Чтобы найти названия книг, у которых имеются данные о колонке notes (т.е., у которых значение колонки notes не является null -значением), применяйте конструкцию IS NOT NULL, вот так:

SELECT   		title, notes 
FROM     		titles 
WHERE    		notes IS NOT NULL
GO

Набор результатов будет содержать 17 строк, в каждой из которых в колонке notes будут иметься символы (один или несколько), т.е. не null -значения.

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

<проверяемое_выражение> IN (<подзапрос>)

или

<проверяемое_выражение> IN (<список_значений>)

Подзапрос – это оператор SELECT, который возвращает в наборе результатов только одну колонку. Подзапрос должен быть заключен в скобки. Список значений – это просто список из нескольких значений, разделенных запятыми и заключенный в скобки. Колонка, получающаяся как набор результатов подзапроса или список значений, должна иметь такой же тип данных, как и проверяемое выражение. Если необходимо, SQL Server выполнит неявное преобразование типов данных.

В приведенном ниже примере запроса ключевое слово IN применяется в сочетании со списком значений для поиска идентификаторов должностей для трех описаний должностных обязанностей:

SELECT   		job_id 
FROM     		jobs 
WHERE    		job_desc IN ("Operations Manager",  
                      			      "Marketing Manager", 
                      			      "Designer")
GO

В этом запросе применен такой список значений: (Operations Manager, Marketing Manager, Designer). Запрос будет возвращать идентификаторы должностей из строк, содержащих в колонке job_desc какое-либо значение из списка. Благодаря ключевому слову IN ваш запрос проще и понятней для восприятия, чем запрос, который получился, если бы вы применили две операции OR, вот такой:

SELECT   		job_id 
FROM     		jobs 
WHERE    		job_desc = "Operations Manager" OR  
        	job_desc = "Marketing Manager"  OR   
         	job_desc = "Designer"
GO

В следующем запросе ключевое слово IN используется в одном операторе дважды – первый раз с подзапросом, а второй раз – со списком значений внутри подзапроса:

SELECT   		fname, lname    	-- Внешний запрос 
FROM     		employee 
WHERE    		job_id IN (	SELECT 	job_id   -- Внутренний запрос, подзапрос 
                     				FROM   	jobs 
                   					WHERE  	job_desc IN ("Operations Manager", 
                     					"Marketing Manager", 
                     					"Designer"))
GO

Сначала будет искаться подзапрос (в нашем примере – набор значений job_id ). Значения job_id получаются как результаты подзапроса и не выводятся на экран, они используются внешним запросом как выражения для его собственного условия поиска IN. Окончательный набор результатов будет содержать имена и фамилии всех сотрудников, должности которых называются Operations Manager, Marketing Manager или Designer.

Набор результатов будет таким (запрос вернет 11 строк):

fname                		lname 
------------------------
Pedro                		Afonso 
Lesley               		Brown 
Palle                		Ibsen 
Karin                		Josephs 
Maria                		Larsson 
Elizabeth     	Lincoln 
Patricia             		McKenna 
Roland               		Mendel 
Helvetius            	Nagy 
Miguel               		Paolino 
Daniel               		Tonini

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

SELECT   		pub_name 
FROM     		publishers 
WHERE    		state NOT IN (	"CA", 
                       			"TX", 
                       			"IL")
GO

Этот запрос вернет пять строк, у которых значение в колонке state (штат) не является обозначением ни одного из трех штатов, указанных в списке значений. Если вы установили настройку ANSI nulls вашей базы данных на ON, то набор результатов будет содержать только три строки. Такое малое количество выданных строк произошло потому, что две из пяти строк первоначального набора результатов в качестве значения для state будут иметь NULL, а значения NULL не извлекаются при настройке ANSI nulls заданной как ON.

Чтобы узнать, какова настройка ANSI nulls для базы данных pubs, запустите следующую системную хранимую процедуру:

sp_dboption "pubs", "ANSI nulls" 
GO

Если настройка ANSI nulls задана как OFF, то измените ее на ON при помощи следующего оператора:

sp_dboption "pubs", "ANSI nulls", TRUE 
GO

А чтобы изменить настройку с ON на OFF, примените такой же оператор, но с FALSE вместо TRUE.

Дополнительная информация. Чтобы получить более подробную информацию о действии настройки ANSI nulls, найдите в предметном указателе (индексе) SQL Server Books Online "sp_dpoption", а затем в диалоговом окне найденных тем выберите sp_db_option (T-SQL). Кроме того, найдите в предметном указателе SQL Server Books Online "ANSI nulls" и нажмите в нижней части страницы на ссылку SET ANSI_NULLS, в результате чего на экране появится тема "SET ANSI_NULLS (T-SQL)".

EXISTS. Ключевое слово EXISTS используется для проверки существования строк в выводе подзапроса, указанного после него. Используется такой синтаксис:

EXISTS (<подзапрос>)

Если имеется хоть одна строка, удовлетворяющая подзапросу, то возвращается значение TRUE.

Чтобы найти имена авторов, у которых имеются опубликованные книги, можно применить такой запрос:

SELECT   		au_fname, au_lname 
FROM      		authors  
WHERE     		EXISTS (	SELECT au_id 
                  			FROM   titleauthor 
                  			WHERE  titleauthor.au_id = authors.au_id)
GO

Авторы, имена которых имеются в таблице authors, но не имеющие опубликованных книг в таблице titleauthor, выбраны не будут. Если в подзапросе не будет найдено ни одной строки, то набор результатов внешнего запроса будет пустым (будет найдено ноль строк).

CONTAINS и FREETEXT. Ключевые слова CONTAINS и FREETEXT используются для полнотекстного поиска в колонках, имеющих символьные типы данных. Это обеспечивает большую гибкость, по сравнению с возможностями ключевого слова LIKE. Например, благодаря ключевому слову CONTAINS, можно искать слова, обладающие не точным соответствием, а некоторой похожестью на заданное слово или фразу (такое сопоставление называется "нечетким"). При помощи FREETEXT можно искать слова, соответствующие или нечетко соответствующие заданной строке поиска или ее части. Найденные слова не обязаны ни полностью совпадать со строкой поиска, ни следовать в порядке расположения слов в строке поиска. Эти два ключевых слова могут применяться различными способами, они обеспечивают разнообразные возможности полнотекстного поиска, рассмотрение которых выходит за рамки нашей книги.

Дополнительная информация. Для дополнительной информации о ключевых словах CONTAINS и FREETEXT, обратитесь к SQL Server Books Online, к темам "CONTAINS" и "FREETEXT" в предметном указателе.