Элементарные основы языка SQL

Запрос на выборку данных из базы данных

SELECT - используется, чтобы извлечь записи из БД как некоторый набор и сохранить их в новом объекте типа Recordset. Операторы SELECT не изменяют данные, а только извлекают их из базы данных.

Формат запроса:

SELECT список_полей

FROM список_таблиц IN внешняя_база_данных

WHERE условие_отбора

GROUP_BY список_полей

HAVING условие_группировки

ORDER BY список_полей

SELECT * FROM stud - запрос на выбор всех данных из всех полей из таблицы stud

SELECT fio_stud FROM stud - запрос на выбор всех данных из поля fio_stud из таблицы stud

SELECT birthday_stud as рождение FROM stud - создание псевдонимов столбцов

Если имя поля повторяется более чем в одной таблице предложения FROM, то при обращении к полю ставится имя таблицы, точка, имя поля:

SELECT stud.name, stud.surname, par.name, par.surname FROM stud, par

можно использовать алиасы (ссылки) для обращения к таблицам в громоздких конструкциях

SELECT s.name, s.surname, p.name, p.surname FROM stud as s, par as p

также допускается другая запись после from: FROM stud s, par p

Использование переменных VBA при формировании запроса

StrSQL = "SELECT name, birthday FROM stud WHERE name = '" & name.text & "'"

Фильтрация и сортировка результатов запроса.

Предикат DISTINCT позволяет пропускать записи, которые содержат повторяющиеся данные в выбранных столбцах

Если мы хотим увидеть все разные имена, которые имеют студенты группы 325, то можем выполнить следующий запрос

SELECT DISTINCT name FROM stud WHERE num_group=325

Предикат TOP N возвращает первые N записей (от начала набора записей) в порядке, определенном оператором ORDER BY

SELECT TOP 10 [name], [surname] FROM stud WHERE year_out = 2002 ORDER BY [average_mark] DESC - сортировать по уменьшению значения

Если не использовать ORDER BY, то будут возвращены 10 произвольных значений

Для того, чтобы возвратить процентное количество можно использовать предикат PERCENT

SELECT TOP 20 PERCENT [name], [surname]

FROM stud

WHERE year_out = 2002

ORDER BY [average_mark] DESC

Предложение WHERE указывает какие записи из таблиц, указанных в предложении FROM, будут включены в результат оператора SELEC

Если предложение WHERE не указоно, то возвращаются все записи таблицы

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

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

WHERE используется после FROM

Примеры:

WHERE cpec_group = 'физика'

WHERE stud_age BETWEEN 18 AND 25

Предложение WHERE может содержать 40 выражений, связанных логическими операторами AND и OR

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

SELECT [ID товара], [имеется на складе]

FROM Товары

WHERE [Имеется на складе] <= [Допустимый остаток]

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

MM/DD/YY и заключена в литералы даты (#)

Например, если вас интересуют товары с датой поставки 13 марта 2012 года, то запрос будет выглядеть так:

SELECT * FROM Заказы

WHERE [дата поставки] = #03/13/12#

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

Пример для программы в России

SELECT * FROM Заказы

WHERE [дата поставки] = DateValue('13/3/12')

Пример для программы в США

SELECT * FROM Заказы

WHERE [дата поставки] = DateValue('3/13/12')

GROUP BY объединяет записи с идентичными величинами в указанном поле в одну запись, если в оператор SELEC включена статистическая функция SUM или COUNT, то для каждой записи создается итоговое значение.

Если стат. функции нет, то итоговые значения не создаются. Величины Null группируются и не опускаются.

Предложение WHERE используется для исключения строк, которые не нужно группировать, а предложение HAVING для фильтрации записей после того как они сгруппированы

Поле в списке предложения GROUP BY может ссылаться на любое поле в предложении FROM, если оно не содержит данных типа MEMO, даже если поле не включено в оператор SELECT, при условии, что оператор SELECT содержит статистическую SQL функцию

SELECT [Название товара], SUM([Имеется на складе])

FROM Товары

GROUP BY [Название товара]

Предложение HAVING определяет какие записи показываются в операторе SELECT с предложение GROUP BY (подобно оператору WHERE)

Предложение ORDER BY определяет порядок сортировки записей, полученных в ходе выполнения запроса

SELECT * FROM stud

ORDER BY fio_stud ASC; - сортировка по возрастанию

SELECT [Фамилия], [Имя] FROM stud

ORDER BY [Фамилия] по умолчанию по возрастанию

Можно сортировать в порядке убывания по номеру столбца

SELECT [Фамилия], [Имя] FROM stud

ORDER BY 2 DESC

SELECT [Фамилия], Зарплата FROM Служащие

ORDER BY Зарплата DESC, Фамилия

Выбирается оклад жалования и сортируется в нисходящем порядке, служащие имеющие одинаковый оклад сортируются по возрастанию

SELECT COUNT (fio_stud) FROM stud WHERE num_group_stud =122 -запрос на подсчет количества студентов, числящихся в группе 122

SELECT COUNT DISTINCT (name_stud) FROM stud - запрос на подсчет количества различных имен в таблице

Запрос на создание таблицы

SELECT * INTO [Новые служащие] FROM Служащие

В данном случае создается новая таблица, а не набор записей

Запрос на удаление данных

DELETE - удаляет из одной и более таблиц записи, удовлетворяющие условию

WHERE

DELETE FROM stud WHERE key_stud = 22 - удаляет из таблицы stud запись, которой

соответствует значение поля key_stud, равное 22

Запрос на добавление данных

Оператор INSERT INTO используется, чтобы добавить записи к таблице или создать запрос на добавление.

INSERT INTO приемник [IN внешняя база данных]

SELECT [источник. ] поле1[, поле2]

FROM выражение

Если надо добавить одну запись, то

INSERT INTO приемник [(Поле1[, Поле2])]

VALUES (Значение1[, Значение2])

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

Пример.

Добавление информации в таблицу town

INSERT INTO town (name_town, region_town) VALUES ('Томск','Россия')

Более сложный вариант

INSERT INTO stud (fio_stud, birthday_stud, key_group, key_town, num_zachet_stud, data_in_stud, sex_stud)

Запрос на обновление данных

UPDATE таблица

SET новое значение

WHERE критерий

Пример:

UPDATE stud SET fio_stud='Петров В.В.' WHERE fio_stud like 'Петров В.А.'

Данный запрос позволяет изменить у всех экземпляров сущности Студент, хранящихся в таблице stud, имеющих значение поля fio_stud, похожее на "Петров В.А." на значение "Петров В.В."

Запрос используется в методе Execute

Обновление нескольких записей

UPDATE Заказы

SET [Сумма заказа]=[Сумма заказа]*1.1,

Фрахт = Фрахт * 1.03

WHERE [Страна поставки]='UK'

Составные запросы

Подзапросы

Для составления подзапроса используются следующие синтаксические формы

сравнение [ANY | ALL | SOME] (SQL -оператор)

выражение [NOT] IN (SQL-оператор)

[NOT] EXISTS (SQL-оператор)

Подзапрос может быть вместо выражения в списке полей оператора SELECT или в предложения WHERE и HAVING

ANY, SOME выбираются записи, которые удовлетворяют сравнению с какой-либо записью

ALL удовлетворяют сравнению со всеми записями

SELECT * FROM Товары

WHERE [Цена] > ANY

(SELECT [Цена] FROM [Подробности заказа]

WHERE [Скидка]>=25)

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

Если надо извлечь записи совпадающие по величине, то

SELECT * FROM Товары

WHERE [Product ID] IN

(SELECT [Product ID] FROM [Подробности заказа]

WHERE [Скидка]>= 25)

Чтобы связать таблицу в основном запросе с таблицей в подзапросе можно воспользоваться алиасом

SELECT [Фамилия], [Имя], Должность, Зарплата

FROM Служащие AS T1

WHERE Зарплата >=

(SELECT AVG(Зарплата)

FROM Служащие

WHERE T1.Должность=Служащие.Должность) ORDER BY должность

СОЕДИНЕНИЯ

INNER JOIN (внутреннее) - записи из обеих таблиц включаются в соединение только в случае совпадения заданного поля первой таблицы с заданным полем второй таблицы

LEFT OUTER JOIN - включаются все записи первой таблицы и те записи второй таблицы, где заданные поля совпадают

RIGHT OUTER JOIN - включаются все записи второй таблицы и те записи первой таблицы, где заданные поля совпадают

FROM table1 INNER JOIN table2 ON table1.field1=table2.field2

FROM table1 [LEFT | RIGHT] JOIN ON table1.field1=table2.field2

Сложный запрос

SELECT поля

FROM таблица1 INNER JOIN

(таблица2 INNER JOIN [(] таблица 3

[INNER JOIN [(] таблицаX [INNER JOIN...)]

ON таблица3.поле3=таблицаХ.полеХ])

ON таблица2.поле2=таблица3.поле3)

ON таблица1.поле1=таблица2.поле2

SELECT DISTINCTROW sum([Цена]) * [Количество] AS [Объем сбыта], [Имя] & " " &

[Фамилия] AS Имя FROM Служащие

INNER JOIN (Заказы INNER JOIN [Подробности заказа]

ON Заказы.[ИД Заказа]=[Подробности заказа].[ИД Заказа])

ON Служащие.[ИД Служащего]=Orders.[ИД Служащего]

GROUP BY [Имя] & " " [Фамилия]

При работе с языком SQL. Для того, чтобы выполнить любой запрос, который не возвращает записи, необходимо заключить оператор в двойные кавычки и использовать его как аргумент метода Execute объектов Database или QueryDef

MyDB.Execute "CREATE TABLE stud ([fio_stud] TEXT, [sex_stud] bool)"

Для того, чтобы использовать любую из команд, которые возвращают строки (SELECT), запрос можно использовать как источник метода OpenRecordset

MyDB.OpenRecordset("SELECT * from stud",dbOpenDynaset)

Создание базы данных

1. Открыть Microsoft Access. Создать новую базу данных (Файл/Создать/База данных). В диалоговом окне указать путь сохранения диск D:\Личный_Каталог\ Базу данных назвать Uch_Proc. Расширение файла будет зависеть от версии СУБД ACCESS (может быть mdb, может быть accdb.

2. В открывшемся окне созданной базы данных выбрать объект Таблицы. Два раза щелкнуть по пункту меню «Создание таблицы в режиме конструктора». Заполнить появившуюся таблицу так, как это указано в табличной схеме. При сохранении макета, на вопрос как назвать Таблицу, задать имя сущности или отношения. В некоторых версиях MS Access имя таблицы запрашивается сразу при переходе в режим конструктора.

3. Для Access 2007 - Создание\Таблица\Режим конструктора\

4. Таким же образом в режиме конструктора создать все остальные таблицы сущностей (отношений)

5. Создание связей по внешним ключам можно осуществить с использованием мастера подстановок. Для выполнения данной процедуры необходимо выбрать искомую таблицу; перейти в режим конструктора; для поля являющегося внешним ключом выбрать тип данных - Мастер подстановок.