Книга: VBA для чайников

Настройка набора записей: задаем критерии

Настройка набора записей: задаем критерии

Для ограничения набора записей только теми записями, которые удовлетворяют определенным критериям, добавьте к инструкции SELECT ключевое слово WHERE, как показано в приведенных ниже примерах:

SELECT * FROM Toys WHERE Price <= 20

SELECT Customer, Date FROM Sales WHERE Date = #10/24/2000#

SELECT Name, Rank, CerealNumber FROM Kids WHERE Rank = 'Queen'

SELECT Name, Age, [Shoe Size] FROM Kids WHERE Age Between 3 And 6

Как легко видеть, ключевое слово WHERE указывается после ключевого слова FROM и содержит выражение, определяющее критерий, которому должны соответствовать записи, чтобы попасть в набор. Кроме того, эти выражения не похожи на обычные выражения VBA. Во-первых, строковые значения заключаются в одинарные, а не двойные кавычки. Во-вторых, вы можете определять диапазоны с помощью конструкции Between ... And, которая в VBA отсутствует. И в SQL оператор Like функционирует совсем не так, как в VBA.

Вы можете объединить несколько выражений, используя логические операторы (And, Or и т.д.), как показано ниже;

SELECT * FROM Toys WHERE Price > 20 And Category = 'Action Figures'

В коде VBA принято использовать одинарные кавычки для определения строки в инструкции SQL, которая целиком является строкой с точки зрения VBA, a значит, заключается в двойные кавычки. Например, вы можете настроить объект Command следующим образом:

strSQL = "SELECT Name FROM Kids WHERE Hates =

'Brocolli'" cmdEr.CommandText = strSQL

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

strSQL = "SELECT Name FROM Kids WHERE Hates = ' " _

& frmInputForm.Text Box l & "'"

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

Ключевое слов GROUP BY позволяет вам объединять записи, содержащие одинаковые значения в указанных полях, преобразуя их в одну запись в полученном наборе записей. Обычно это ключевое слово используется в том случае, если вам необходим набор записей, содержащий общие сведения о данных. Например, вам понадобилось узнать, сколько записей содержится в базе данных для каждого значения указанного поля. Соответствующий пример приведен ниже:

SELECT Category, Countf[Category]) AS [Number of Items] FROM Toys

GROUP BY Category;

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

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

Ключевое слово HAVING следует после ключевого слова GROUP BY и позволяет определить критерии для сгруппированных записей. Оно работает практически так же, как и ключевое слово WHERE; вы можете использовать его отдельно или в комбинации со словом WHERE для того, чтобы наложить на полученные записи дополнительные ограничения. В этом примере ключевое слово HAVING включает только те категории, которые содержат как минимум пять записей, удовлетворяющих критериям, определенным с помощью ключевого слова WHERE:

SELECT Category, Count(Category) As [Number cf Items] FROM Toys

WHERE Price > 100 GROUP BY Category HAVING Count(Category) > 4

Используйте оператор ORDER BY для сортировки записей, полученных с помощью инструкции SELECT, в соответствии со значениями одного или нескольких полей. Оператор ORDER BY указывается в конце инструкции, как показано ниже:

SELECT Toy, Price, InStock FROM ToyInventory ORDER BY Toy

В полученном наборе записей список игрушек будет упорядочен по именам.

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

SELECT Toy, Price FROM ToyInventory ORDER BY Price DESC, Toy

По умолчанию сортировка всегда проводится по возрастанию. Для явного указания порядка сортировки используйте ключевое слово DESC (descending- убывание) или ASC (ascending- возрастание), после которого необходимо указать имя соответствующего поля.

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

Инструкции UPDATE и DELETE позволяют вам изменять или удалять группу записей в источнике данных с помощью всего одной команды. Эти инструкции работают непосредственно с исходной базой данных; вам не нужно предварительно получать набор записей, изменять записи, а затем переносить в базу данных внесенные изменения. Изучите приведенный ниже пример, иллюстрирующий повышение цены на 10% для товаров определенной категории:

UPDATE Toys SET Price = Price * 1.1 WHERE Category = 'Trains'

Имя таблицы, с которой вы работаете, указывается сразу после слова UPDATE. После этого указывается оператор SET, с помощью которого вы определяете значение одного или нескольких полей в таблице. И наконец, необязательный оператор WHERE позволяет вам задать критерии, ограничивающие записи, к которым будут применяться изменения. Оператор WHERE работает точно так же, как и в инструкциях SELECT.

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

DELETE FROM Toys WHERE InStock = 0 And OnOrder = 0

Для удаления значений отдельных полей, а не целых записей, используйте инструкцию UPDATE вместе с оператором SET, определяющим значение поля равным Null.

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

Оглавление книги