УМКД. БД. и эксперт УМКД+. Учебнометодический комплекс по дисциплине базы данных и экспертные системы
 Скачать 1.37 Mb.
|
|
Тема: Синтаксис предложений языка SQL Синтаксис инструкции SELECT Инструкция SELECT является ядром языка SQL. Она используется для отбора строк и столбцов из таблицы БД и содержит 5 основных предложений. В общем случае синтаксис инструкции представляется в следующем виде: SELECT <список полей> FROM <список таблиц> [WHERE <спецификация отбора строк>] [GROUP BY <спецификатор группировки>] [HAVING <спецификатор отбора групп>] [ORDER BY<спецификатор сортировки>] В MS Access реализованы 4 важных средства расширения языка SQL. Инструкция TRANSFORM позволяет устроить перекрестный запрос. Инструкция IN, с помощью которой создается связь с удаленной БД или указываются имена столбцов перекрестного запроса. П  редикат DISTINCTROW, позволяющий отбирать строки из источников данных, включенных в список таблиц с различными значениями первичных ключей в таблице, из которых поступают столбцы в список полей.Предложение WITH OWNERACCESS OPTION. Запрос, включающий последнее предложение, может выполняться пользователями, которые имеют разрешение на его запуск, но не обладают правом доступа к таблицам, на которые он ссылается в запросе. Имя столбца Синтаксис: [ [  ] { имя таблицы | имя запроса на выборку | псевдоним } [  ] , [ ] имя поля [ ] ]Форма Науса-Бэкунса [ [ { поставщик } ] , [ имя поля ] ] Полный идентификатор поля необходимо указывать только в том случае, если имя поля может быть неоднозначно в контексте запроса. В предложении FROM запроса или подчиненного запроса должно присутствовать имя таблицы, имя запроса на выборку или псевдоним, уточняющий имя поля. Если таблица или запрос имеют псевдоним, то необходимо использовать его, а не реальное имя таблицы или запроса. Имена, содержащие внедренные пробелы должны обязательно заключаться в квадратные скобки. Выражения Выражение определяет некоторое значение в предикате или в списке полей инструкции SELECT или подчиненного запроса. Синтаксис выражения: [ + | – ] { функция | [ ( ] <выражение> [ ) ] | литерал | имя столбца } [ { ( + | - | * | / | ^ | MOD | & } { функция | [ ( ] <выражения> [ ) ] | литерал | имя столбца } ] …любая итоговая функция языка SQL: AVG, COUNT MAX, MIN STDEV, STDEVP SVM, VAR или VARP В выражении может быть задана только одна итоговая функция. Кроме того можно использовать любую функцию, встроенную в Access или определенную на языке Visual Basic. [ ( ] <выражение> [ ) ] Выражение может создаваться как составное выражение, состоящее из нескольких выражений, разделенных операторами. Круглые скобки используются для уточнения порядка вычисления. Литерал – числовая или буквенно-цифровая строковая константа. Буквенно-цифровые константы необходимо заключать в апостроф или двойные кавычки. Литерал дата/время заключается между символами #. Задается в формате mm/dd/yy или mm/dd/yyyy. Имя столбца (таблицы или запроса) в выражении можно включать в выражение, которое задается предложением FROM. В арифметическом выражении должны использоваться только поля, содержащие числовые переменные. Если имя встречается в нескольких таблицах или запросах данной инструкции необходимо использовать полное имя, А НЕ ИМЯ ВЫЧИСЛЯЕМОГО ПОЛЯ. MS Access не поддерживает ссылки на столбцы именованных выражений в предложениях GROUP BY, HAVING, ORDER BY и WHERE. Операции. Арифметические операторы, которые используют при вычислении составного числового выражения требуют, чтобы выражение было только числовым. При этом входящее в него выражение (частное) должно быть числовым или приводиться к определенному типу данных. & - конкатенация. Примеры: Определить среднее значение стоимости -> AVG(стоимость) Премия -> (ПРЕМИЯ *.5) 3 часа дня 8 апреля 2003 года -> #08/04/2003 3:00 РМ# Задание строковой переменной -> “строковая переменная” Предложение FORM задает таблицы или запросы, которые служат источником данных для создаваемого запроса. Лекция №10 Тема: Синтаксис предложений языка SQL (продолжение). Синтаксис предложения FROM FROM{имя_таблицы[[AS]псевдоним]имя_запроса_на_выборку[[AS]псевдоним]|<таблица_объединения>} IN<спецификация_источника> <таблица_объединения>:=({имя_таблицы[[AS]псевдоним]имя_запроса_на_выборку[[AS]псевдоним]|<таблица_объединения>}) {INNER|LEFT|RIGHT|JOIN{имя_таблицы[[AS]псевдоним]имя_запроса_на_выборку[[AS]псевдоним]|<таблица_объединения>}} ON<условие_объединения> Для каждой таблицы и запроса можно выделить отдельное имя, которое будет являться псевдонимом и будет использоваться в качестве полного имени таблицы. При объединении таблицы или запроса с самим собой все указанные копии таблицы или запроса, на которую вы ссылаетесь при задании списка полей, условий объединения или условий отбора необходимо использовать псевдоним. Если имя таблицы или запроса совпадает с зарезервированным словом SQL, его необходимо заключить в квадратные скобки. Если в предложение FROM включено несколько таблиц без задания условий их объединения, а для указания полей таблиц используется предложение WHERE, то MS Access активизирует выполнение запроса, интерпретируя его как объединение. SELECT FROM TableA, TableB WHERE TableA.id=TableB.id Эта конструкция будет выполняться аналогично следующей: SELECT FROM TableA INNER JOIN TableB ON TableA.id=TableB.id При объединении таблиц на основе этих таблиц и предложения WHERE не разрешается обновлять поля базовых таблиц из набора базовых таблиц и формы, построенной на этом запросе, а при использовании оператора JOIN такое обновление разрешается. Если список таблиц содержит несколько таблиц или запросов, но для них не задано условие объединения, то в качестве источника данных используется декартово произведение этих таблиц. Синтаксис предложения GROUP BY Предложение GROUP BY задает столбцы, которые используются для формирования групп из выбранных строк. Строки каждой группы содержат одно и тоже значение заданного столбца или столбцов. В MS Access предложение GROUP BY используют для создания итоговых запросов и оно обязательно включается в перекрестные запросы. GROUP BY <имя_столбца> Имя столбца в предложении GROUP BY может быть именем произвольного столбца из любой таблицы, в упомянутой в предложении FROM даже если этот столбец не содержится в списке инструкции SELECT. Если предложение GROUP BY расположено после предложения WHERE, MS Access создает группы из строк, выбранных после применения предложения WHERE. При включении предложения GROUP BY в инструкцию SELECT, список полей должен состоять из итоговых функций SQL: AVG, COUNT и т.д. или из имен столбцов, указанных в предложении GROUP BY. Синтаксис предложения HAVING Предложение HAVING задает группы строк, которые включаются в логическую таблицу (т.е. набор записей), определяемую инструкцией SELECT. Условия отбора применяются к столбцам, указанным в предложении GROUP BY, к столбцам итоговых функций или к выражениям, содержащим итоговые функции. Если некоторая группа не удовлетворяет условию отбора, она не попадает в набор записей. Синтаксис: HAVING <условие_отбора> Если в инструкции SELECT отсутствует предложение GROUP BY, список полей должен формироваться с использованием одной или нескольких итоговых функций языка SQL. Разница между предложениями WHERE и HAVING заключается в том, что условие отбора, заданное в предложении WHERE применяется к отдельным записям перед их группировкой, а условие отбора в предложении HAVING – к группам строк. Если предложение GROUP BY находится перед предложением HAVING, условия отбора применяются к каждой из групп, сформированных на основе совпадения значений в заданных столбцах. В случае отсутствия предложения GROUP BY, условия отбора применяются ко всей логической таблице, определенной в инструкции SELECT. Синтаксис предложения IN Предложение IN определяет источник данных для базовых таблиц запросов. Источником может быть любая другая БД – Access, FoxPro, Paradox, а так же любая другая БД, для которой установлен драйвер ODBC. Это предложение является расширением Access по сравнению со стандартом SQL. Синтаксис: IN <имя_БД_источника><[строка_подключения_источника_данных]> Если источником БД является MS Access, достаточно знать только имя БД источника. Предложение IN применяется ко всем таблицам, которые указаны в предложении FROM и в подчиненных запросах. В запросе можно ссылаться лишь на одну внешнюю БД. Если необходимо использовать несколько файлов или БД, то их необходимо в Access связать как таблицы и использовать имена связанных таблиц. Синтаксис операции JOIN Операция JOIN используется в предложении FROM для задания типа объединения таблиц в логические наборы записей, из которой будет выбираться необходимая информация. Выделяется внутреннее объединениеinner join и внешнее объединение outer join. Синтаксис: JOIN({имя_таблицы[[AS]псевдоним]|имя_запроса_на_выборку[[AS]псевдоним]} {INNER|LEFT|RIGHT|JOIN{имя_таблицы[[AS]псевдоним]|имя_запроса_на_выборку[[AS]псевдоним]}} ON <условие_объединения> Для каждой таблицы или запроса можно использовать псевдоним. При объединении таблицы или запроса с самим собой для указания копий таблицы или запроса, на который идет ссылка в списках полей в условиях отбора необходимо использовать псевдоним. Операция INNER JOIN используется в том случае, если необходимо получить все строки из двух логических таблиц, удовлетворяющих условию объединения. Операция LEFT JOIN возвращает все строки из первой логической таблицы, объединенной с теми строками из второй таблицы, для которых выполняется условие объединение. А если во второй логической таблице строк нет, то возвращается значение NULL.Аналогичная операция RIGHT JOIN возвращает строки из второй таблицы. Если в условии объединения используется только оператор =, оператор называется объединением по равенству и в бланке запроса можно задавать лишь его.Запрос на основе таблиц по неравенствам (>,>=,<,<=) можно создавать только в режиме SQL. Объединение таблицы самой с собой называется самообъединением. Лекция № 11 Тема: Синтаксис предложения ORDER BY ORDER BY {имя столбца | номер столбца [ASG|DESC]} Предложение ORDER BY задает порядок расположения строк, которые возвращаются инструкцией SELECT или INSERT. Для задания столбца, обозначением которого упорядочиваются строки, возвращаемые функцией SELECT или INSERT можно использовать имя столбца и его порядковый номер в наборе записей запроса. База данных «Пансион». Таблица «Продукты».
Таблица «Наличие». Таблица «Блюда».
Таблица «Рецепт». Таблица «Состав». Таблица «Поставки»
Таблица «Состав» связывает «Блюда» и «Продукты» и оговаривает, какая масса того или иного продукта должна входить в состав порции по весу. Шеф-повар ежедневно получает сведения о количестве в килограммах имеющихся продуктах и их текущей стоимости. Используя эти сведения, он определяет по таблице «Состав» перечень тех блюд, которые можно приготовить из этих продуктов, а также определяет калорийность и стоимость этих блюд. При этом стоимость блюда складывается из стоимости и массы продукта, необходимых для приготовления одной его порции, а также из трудозатрат его приготовления. Калорийность определяется по массе и калорийности каждого из продуктов, входящих в блюдо. Учитывая примерную стоимость и необходимую калорийность дневного рациона составляется меню на следующий день. Таблица «Трапезы». Таблица «Меню».
Таблица «Выбор».
Таблицей «Меню» предлагается несколько альтернативных блюд каждого вида. Для каждой трапезы предлагаются несколько блюд. Отдыхающие вводят в ЭВМ номер закрепленного места СМ в таблице «Выбор» и желаемый набор блюд для каждой из трапез. В таблице «Выбор» объединяются по мере их создания в общую таблицу «Выбрано», в которой определяется сколько порций того или иного блюда надо приготовить для каждой трапезы. Завхоз занимается поставкой продуктов, сведения о которых хранятся в таблице «Поставщики». Эта таблица содержит:
Таблица «Поставки» связывает между собой таблицы «Продукты» и «Поставщики» и оговаривает какое количество продукта и по какой цене поставил тот или иной поставщик. Отсутствие в строке цены и количества говорит о том, что поставщик (ПС) может поставлять продукты (ПР), но в данный момент не осуществил такой поставки. Таблицы в SQL. Понятие «Таблица» в SQL имеет свое представление. SQL использует и создает ряд как будто существующих виртуальных таблиц: представлений, курсоров, неименованных рабочих таблиц, в которых формируются результаты запросов на получение данных из базовых таблиц и, возможно, представлений. Это таблицы, которые не существуют в БД, но как бы существуют с точки зрения пользователя. Б  азовая таблица создается с помощью предложения CREATE TABLE. Рассмотрим пример для создания описания таблицы «Блюда».CREATE TABLE Блюда (БЛ SMALLINT, БЛЮДО CHAR(70), B CHAR (1), ОСНОВА CHAR(10), ВЫХОД FLOAT, ТРУД SMALLINT); Предложение CREATE TABLE специфицирует имя базовой таблицы, которая должна быть создана и типы данных для этих столбцов. CREATE TABLE – выполняемое предложение. Если ввести это предложение с терминала, система построит таблицу блюда, которая сначала будет пустой и будет содержать только строку заголовков (имена столбцов). Значения данных можно ввести с помощью предложения INSERT и создать таблицу в заполненном виде. Если теперь необходимо узнать какие овощные блюда может приготовить повар пансионата, то необходимо набрать запрос следующего вида (на SQL): SELECT БЛ, БЛЮДО FROM БЛЮДА WHERE ОСНОВА=’ОВОЩИ’; Результат: БЛ БЛЮДО 1 Салат Столичный (вообще то я только водку Столичную знаю ) 2 Салат Капуста Для выполнения этого предложения SELECT СУБД должна сначала сформировать пустую рабочую таблицу, состоящую из столбцов «БЛ» и «Блюдо», при этом типы должны соответствовать типам аналогичных столбцов базовой таблицы «БЛЮДА». Затем она должна выбрать из таблицы «Блюда» все строки, у которых в столбце «Основа» имеется слово «Овощи» и выделить из этих строк столбцы «БЛ» и «Блюдо» и загрузить укороченные строки в рабочую таблицу. СУБД должна выполнить процедуру по организации вывода результатов на экран. Лекция №12 |