Е.А. Бессонов Access Запросы на языке SQL. Методические указания к лабораторной работе для студентов специ альности "Экономика и управление на предприятиях " по курсу "Авто матизация экономических расчетов "
 Скачать 182.35 Kb.
|
|
Министерство образования Российской Федерации Кузбасский государственный технический университет Кафедра вычислительной техники и информационных технологий ACCESS Запросы на языке SQL Методические указания к лабораторной работе для студентов специ- альности “Экономика и управление на предприятиях ” по курсу “Авто- матизация экономических расчетов ” Кемерово 2001 Составитель Е.А. Бессонов Утверждены на заседании кафедры Протокол № 11 от 23.06.2000 Рекомендованы к печати методической комиссией специальности 060800 Протокол № 1 от 3.10.2000 Электронная копия хранится в библиотеке главного корпуса КузГТУ SQL SQL (Structured Query Language – структурированный язык запро- сов) с 1986г. является стандартным языком реляционных баз данных. В частности, он используется в приложениях Access и Excel. Стандарт языка SQL изложен в [1]. Запросы в MS Access сохраняются и реализуются с помощью языка SQL. Хотя большинство запросов можно создать графическими средст- вами (запросы по образцу), однако хранятся они в виде инструкций SQL. В ряде случаев (например в подчиненных запросах) можно ис- пользовать только язык SQL. В MS Access использован и ниже излага- ется диалект этого языка. Многочисленные примеры запросов на языке SQL можно найти в базе данных (БД) Борей (файл I:\Access \Sampapps\Nwind.mdb). SQL заметно отличается от других языков программирования высо- кого уровня. 1. SQL относится к непроцедурным языкам. Он просто декларирует, что нужно сделать, а исполнение возлагается на СУБД (система управ- ления базами данных). 2. В SQL используется трехзначная логика. Наряду с традиционными логическими значениями TRUE и FALSE используется NULL (НЕИЗ- ВЕСТНО или ОТСУТСТВИЕ ДАННЫХ). 3. Операции осуществляются над целыми наборами данных, а не над отдельными элементами, как в других языках программирования. Запрос на языке SQL состоит из инструкций. Каждая инструкция может содержать несколько предложений. FROM Практически ни один запрос не обходится без предложения FROM, описывающего используемые таблицы или запросы, и имеющего син- таксис FROM таблицы [IN внешняя_БД] Таблицы - используемые таблицы/запросы и их взаимосвязи. Если в предложении FROM присутствует конструкция IN(квадратные скобки указывают, что эта часть предложения не является обязатель- ной), то после зарезервированного слова IN должно стоять имя базы данных, в которой находятся таблицы (предполагается, что используют таблицы не из текущей БД). Пример FROM Преподаватели Если запрос строится на двух таблицах, то необходимо указать спо- соб их объединения – один из следующих (предполагается , что чита- тель с ними знаком): , декартово произведение; INNER JOIN внутреннее объединение; LEFT JOIN левое внешнее объединение; RIGHT JOIN правое внешнее объединение. Сразу после способа объединения необходимо поместить фразу ON Таблица1.Ключ = Таблица2.ВнешнийКлюч Ключ - имя ключевого поля со стороны 1. ВнешнийКлюч - имя связующего поля со стороны N. Схема данных На рисунке представлена схема объединения таблиц (схема дан- ных), которая будет использоваться для большинства приводимых ниже примеров. Из рисунка, например, видно, что у таблицы “Экзаменаторы” поле “Предмет” является ключевым (выделено жирным шрифтом), а поле “Преподаватель” – внешним ключом для таблицы “Преподавате- ли”. Объединение между указанными таблицами – внутреннее с обеспе- чением целостности данных. Об этом свидетельствуют знаки 1 и ∞ на концах связующей линии (“Преподаватели” – главная таблица, а “Экза- менаторы” – подчиненная). При внешнем объединении можно увидеть на линии объединения таблиц стрелку, направленную к подчиненной таблице. Это значит, что в главной таблице будут показаны все записи, даже если им нет соответствующих записей в подчиненной. Например, можно получить список преподавателей и названий предметов. Если некоторого преподавателя нет в таблице “Экзаменаторы”, то поле на- звания предмета этого преподавателя будет пусто, если используется объединение LEFT JOIN. Пример FROM Экзаменаторы INNER JOIN Экзамены ON Экзаменаторы.Предмет = Экзамены.Предмет В предложении FROM перед зарезервированными словами INNER JOIN указывается имя таблицы со стороны 1 (в нашем случае таблица “Экзаменаторы”). Если в предложении FROM больше двух таблиц, то объединение двух таблиц можно заключить в круглые скобки и рас- сматривать его как одну таблицу при объединении с другими таблицами или объединениями. Таким способом можно описать объединение лю- бого числа таблиц. Пример FROM Преподаватели INNER JOIN (Экзаменаторы INNER JOIN Экзамены ON Экзаменаторы.Предмет = Экзамены.Предмет) ON Преподаватели.Преподаватель = Экзаменаторы.Преподаватель Описана вся схема данных (см. рисунок). SELECT Инструкция SELECT обеспечивает выборку необходимых полей из таблиц или запросов. Формат минимального варианта: SELECT поля FROM таблицы; Поля - множество выражений и имен полей, разделенных запятыми. Пример SELECT Группа, Студент, Оценка FROM Экзамены; Если имя повторяется в нескольких таблицах, перечисленных в предложении FROM, то перед именем надо поместить имя таблицы и точку, например: [Экзаменаторы].Преподаватель или [Экзамены]. [Предмет] Если имя таблицы или поля содержит пробел или другой спецсим- вол, то это имя нужно заключить в квадратные скобки. В других случа- ях скобки необязательны. В приведенном примере в режиме таблицы запрос выдает таблицу с заголовками столбцов “Группа”, ”Студент”, ”Оценка” (именно в этом порядке). Иногда бывает желательно, чтобы заголовок отличался от имени поля. В этом случае после имени поля следует поместить заре- зервированное слово AS и заголовок (псевдоним), например: SELECT Группа, Студент AS ФИО,Оценка В этом случае вместо заголовка “Студент” появится “ФИО”. Если в заголовке более одного слова, его необходимо заключить в квадрат- ные скобки. Если необходимо выдать все поля таблицы, то аргумент поля сле- дует задать звездочкой или в виде “Таблица.*”. Пример SELECT Преподаватели.* FROM Преподаватели; Выдаются все 5 полей из таблицы “Преподаватели”. Иногда требуется выдать не значение поля, а результат вычисле- ний над значениями полей. В этом случае вместо имени поля надо за- дать выражение. Правила записи выражений не отличаются от правил, применяемых в конструкторе запросов. Пример SELECT Avg([Оценка]) As [Средний балл] FROM Экзамены; Запрос выдает одно число в столбце с заголовком “Средний балл” – среднее арифметическое всех оценок студентов. Пример SELECT Count([Преподаватель]) As [Число преподавателей] FROM Преподаватели; Под заголовком “Число преподавателей” будет помещено число строк таблицы “Преподаватели”, в которых поле “Преподаватель” не пусто (а оно всегда не пусто, так как является ключевым). Это и есть число преподавателей, так как в каждой записи есть номер преподава- теля, отличный от номеров других преподавателей. В инструкции SELECT сразу после слова SELECT может быть за- писан предикат – одно из слов ALL, DISTINCT, DISTINCTROW, TOP N [PERCENT]. Предикат ALL предписывает выдавать все записи, даже если они содержат повторы. DISTINCT запрещает выводить записи, содержащие повторы в отобранных полях. Использование предиката DISTINCT эквивалентно установке значения “Да” свойства “Уникальные значения” в бланке свойств конструктора запросов. DISTINCTROW влияет на результат только в том случае, если в запрос включены не все поля из анализируемых таблиц. Предикат игно- рируется, если запрос содержит только одну таблицу. Предикат DISTINCTROW исключает записи, повторяющиеся полностью. Исполь- зование предиката DISTINCTROW эквивалентно установке значения “Да” свойства “Уникальные записи” в бланке свойств конструктора за- просов. Предикат Top N используется для возврата N записей, находящихся в начале или конце набора, отсортированного по возрастанию или убы- ванию значений этого поля. Сортировка определяется с помощью пред- ложения ORDER BY, размещаемого после предложения FROM инст- рукции SELECT. После слов ORDER BY надо поместить имя поля и слово ASC (по возрастанию, не обязательно) или DESC (по убыванию). Следующая инструкция SQL позволяет получить список из 5 лучших студентов. Пример SELECT TOP 5 Студент, [Средний балл] FROM Успеваемость ORDER BY[Средний балл] DESC; Пример SELECT DISTINCT [Студент] FROM Экзамены ORDER BY [Студент]; Запрос выдает список студентов, отсортированный по возрастанию фамилий. WHERE После предложения FROM инструкции SELECT можно написать Успеваемость Таблица 1 Предмет Группа Студент Средний балл 1 1 2 2 1 3 1 1 2 2 1 1 Волков Медведев Белкин Лисицын Воробьев Кротова 2,4 4,5 5,0 2,1 3,3 4,8 предложение WHERE в форме WHERE условие Условие - логическое выражение, которое вычисляется для каждой за- писи исходной таблицы Е Е с с л л и и у условие истинно, то запись (совокупность полей в списке по- лей предложения SELECT) включается в результирующее множество, если ложно – не включается. Пример SELECT DISTINCT Группа, Студент FROM Экзамены WHERE Оценка = 2; Создается список студентов – двоечников. Пример SELECT [ФИО] FROM Преподаватели WHERE [Должность]=”Доцент” OR [Степень]=”К.т.н.” ORDER BY [ФИО]; Выдается список преподавателей, имеющих ученое звание доцента или ученую степень кандидата технических наук. В этом примере нет необходимости применять предикат, поскольку записи принадлежат разным преподавателям, и повторы невозможны. При отборе строковых выражений можно использовать оператор Выражение Like шаблон, который проверяет соответствие результата вычисления выражения шаблону. В шаблоне могут использоваться не- которые символы, имеющие специальное назначение: * любое количество любых символов; # цифра; ? любой символ. В квадратных скобках можно указать диапазон, в котором нахо- дится или не находится символ. [A-F] символ в диапазоне от A до F включительно. [!A-F] символ не входит в диапазон A-F. Пример SELECT [ФИО] FROM Преподаватели WHERE [ФИО] LIKE “B*” OR [ФИО] LIKE “Щ*”; В результирующее множество войдут фамилии преподавателей, начинающиеся с букв Д или Щ. Пример Х LIKE “P[A-F]###” Написанному условию удовлетворяют строки из 5 символов, начи- нающиеся с буквы Р. За ней должна следовать буква из диапазона A-F. Строку должны завершать 3 цифры. PARAMETERS Если перед каждым выполнением запроса необходимо изменять условия отбора, удобно использовать предложение вида: PARAMETERS тексты; Тексты - список текстов, разделенных запятыми. После каждого текста через пробел указывается тип данных. При выполнении запроса с параметрами не требуется открывать окно конст- руктора запросов и вносить изменения в условия отбора. Вместо этого пользователю предлагается ввести нужное условие во время выполне- ния запроса. Для каждого текста из предложения PARAMETERS на эк- ране появляется диалоговое окно, где каждый текст из описания играет роль подсказки – что именно нужно ввести. В диалоговое окно следует ввести данное указанного типа. Текст, содержащий пробелы и знаки препинания, необходимо за- ключить в квадратные скобки. Тип данных TEXT указывать не обяза- тельно. Если используется предложение PARAMETERS, оно должно на- ходиться перед всеми остальными инструкциями, в том числе и перед инструкцией SELECT, и заканчиваться точкой с запятой. Пример PARAMETERS [Укажите начальную дату] DATETIME, [Укажите конечную дату] DATETIME; В условиях отбора предложений WHERE и HAVING можно ис- пользовать текст без указания типов данных. При выполнении запроса текст заменяется на введенное значение. Пример PARAMETERS [Укажите группу] TEXT; SELECT Студент, Оценка FROM Экзамены WHERE [Группа]=[Укажите группу] And [Предмет]=1; Запрос выдает оценки студентов указанной группы по предмету с кодом 1. GROUP BY Предложение GROUP BY поля объединяет группу записей в ука- занном списке (поля) полей в одну запись. Если инструкция SELECT содержит групповую функцию (например Avg или Sum), то для записи в результирующее множество будет вычислено значение – итог по группе записей. Так, например, если в таблице “Экзамены” сгруппировать за- писи по предмету, то с помощью функции Avg можно получить средний балл по предмету. Группировка по предмету и группе позволит полу- чить средние баллы студенческих групп по указанному предмету. GROUP BY не является обязательным предложением. Если оно присутствует в предложении SELECT, то располагается после предло- жения FROM. При использовании предложения GROUP BY все поля в списке полей предложения SELECT должны быть либо включены в список по- лей предложения GROUP BY, либо использоваться в качестве аргумен- тов групповой функции SQL. Если используются групповые функции, а предложение GROUP BY отсутствует, то роль группы играет вся совокупность исходных за- писей запроса. Пример SELECT Группа, Студент, Avg([Оценка]) AS [Средний балл] FROM Экзамены GROUP BY [Группа],[Студент]; Для каждого студента подсчитывается среднее арифметическое значение его оценок. HAVING Необязательное предложение HAVING условие должно распола- гаться после GROUP BY. Оно определяет, какие из сгруппированных записей войдут в результирующее множество. Условие в HAVING – обычное логическое выражение, как и в WHERE. WHERE и HAVING могут присутствовать в инструкции SELECT и одновременно. В этом случае WHERE отфильтровывает записи до группировки, а HAVING - сгруппированные записи (группы). Пример PARAMETERS [Введите название предмета] TEXT; SELECT Группа, Предмет, Avg([Оценка]) AS [Средний балл] FROM Экзаменаторы INNER JOIN Экзамены ON Экзаменаторы.Предмет=Экзамены.Предмет WHERE [Название предмета]=[Введите название предмета] GROUP BY Группа, Студент HAVING Avg([Оценка])>=4,5 And Min([Оценка])>2; Запрос возвращает список студентов с их средними баллами. В список входят студенты, не имеющие двоек и со средними баллами не ниже 4,5. TRANSFORM Инструкция TRANSFORM используется для создания перекрест- ного запроса. Данные, представленные с помощью перекрестного за- проса, изображаются в более компактном виде, чем с помощью запроса- выборки. Синтаксис: TRANSFORM Функция SELECT …; PIVOT поле; Функция - групповая функция SQL, обрабатывающая данные ячейки таблицы Поле - поле или выражение, значения из которого становятся заголов- ками столбцов. Запрос в режиме таблицы имеет столько столбцов, сколько раз- личных значений принимает поле. Например, если поле выдает названия месяцев, то получится до 12 столбцов, заголовки которых упорядочены по возрастанию (Август, Апрель…Январь). После аргумента поле мож- но поместить предложение IN(список_значений). Фиксированные зна- чения в списке_значений разделяются запятыми. При наличии предло- жения IN каждое значение поля сравнивается со значениями в спи- ске_значений. При совпадении в соответствующем столбце выводится результат вычисления функции. Фиксированные заголовки, которым не соответствуют реальные данные, можно использовать для создания до- полнительных столбцов. Использование предложения PIVOT эквивалентно определению свойства “Заголовки столбцов” в бланке свойств конструктора запросов. Таблица 2 Экзамены Счетчик Предмет Группа Студент Оценка 1 2 3 4 5 6 1 1 2 2 2 3 1 1 2 2 1 1 Волков Медведев Белкин Лисицын Волков Барсуков 2 5 3 4 3 3 Пример TRANSFORM Count([Оценка]) SELECT Группа, Count([Предмет]) AS Всего FROM Экзамены WHERE [Предмет]=1 OR [Предмет]=2 GROUP BY Группа ORDER BY Группа PIVOT Оценка IN(5,4,3,2); Результат выполнения может иметь, например, такой вид: Группа Всего 5 4 3 2 1 2 4 2 1 1 2 1 1 Столбцы “Группа” и “Всего” сформированы инструкцией SELECT, включающей в себя предложения WHERE, FROM, GROUP BY и ORDER BY. Заголовки остальных столбцов определены предло- жением PIVOT, а значения в ячейках этих столбцов формирует функция Count из предложения TRANSFORM. Пример PIVOT “кв.“ & DatePart(“q”,[Дата]) IN(‘кв.1’,’кв.2’,’кв.3’,’кв.4’) В функции DatePart первый аргумент указывает, какую часть нуж- но извлечь из даты, представленной вторым аргументом. Признак “q” предписывает извлечь из даты номер квартала 1,2,3 или 4. Оператор сцепления строк ”&” соединяет текст “кв.“ с номером квартала. Резуль- тат сравнивается с текстами из предложения IN. При успехе сравнения в соответствующий столбец заносится результат вычисления функции из предложения TRANSFORM. UNION Операция UNION позволяет получить запрос-объединение, яв- ляющийся объединением двух или более таблиц или запросов. В про- стейшем случае объединение имеет формат: Запрос1 UNION Запрос2 ЗапросN -имя сохраненного запроса или инструкция SQL. В объедине- нии может участвовать таблица, перед именем которой стоит заре- зервированное слово TABLE. В следующем примере объединяется таблица “Новые преподавате- ли” и результат выполнения инструкции SQL. TABLE [Новые преподаватели] UNION SELECT * FROM Преподаватели; Предполагается, что таблицы “Новые преподаватели” и “Препода- ватели” имеют одинаковое число полей, а инструкция SELECT с помо- щью звездочки отбирает все поля таблицы “Преподаватели”. По умолчанию при использовании операции UNION повторяю- щиеся записи не включаются в результирующее множество. Включение всех записей можно обеспечить, если после слова UNION поместить предикат ALL. Такой запрос выполняется быстрее. Все запросы и таблицы, включенные в операцию UNION должны иметь одинаковое число полей. Имена полей в результирующем наборе берутся из первого аргумента (Запрос1). В конец каждого аргумента ЗапросN можно включить предложе- ние GROUP BY или HAVING, чтобы сгруппировать и отфильтровать возвращаемые записи. В конец последнего аргумента ЗапросN можно включить предложение ORDER BY, чтобы упорядочить возвращенные записи. Пример SELECT Название, Город FROM Поставщики WHERE Страна=”Украина” UNION SELECT Название, Город FROM Клиенты WHERE Страна=”Украина” UNION SELECT Фамилия, Город FROM Сотрудники WHERE Регион=”Европа”; Запрос отбирает названия украинских фирм (клиентов и постав- щиков) и городов, в которых они находятся, а также фамилии и города проживания всех европейских сотрудников. ПОДЧИНЕННЫЕ ЗАПРОСЫ Инструкция SELECT может быть вложена в другую инструкцию (SE- LECT, DELETE или UPDATE). Такой запрос называется подчиненным. Подчиненные запросы можно использовать в режиме SQL окна запроса, в ячейках “Условие отбора” и “Поле” конструктора запросов и в инст- рукциях SQL в программах на языке Access Basic. Ниже рассматривает- ся лишь вариант подчиненного запроса в режиме SQL. В этом случае он представляется в виде инструкции SQL, заключенной в круглые скобки. Если инструкция SQL выдает единственное значение, то ее можно ис- пользовать в условных выражениях предложений WHERE и HAVING. Пример SELECT Группа, Студент FROM Успеваемость WHERE [Средний балл]>(SELECT Avg([Средний балл]) FROM Успеваемость); Групповая функция Avg выдает единственное число – средний балл по всем студентам. Если средний балл студента в главном запросе превосходит это число, то данные о студенте включаются в результи- рующее множество. Пример SELECT У.Группа,У.Студент FROM Успеваемость AS У WHERE Группа=1 And У.[Средний балл]>= ALL (SELECT Avg([Средний балл]) FROM Успеваемость WHERE Группа=2 ) ORDER BY Студент; Подчиненный запрос выдает совокупность средних баллов студен- тов второй группы. Перед подчиненным запросом стоит предикат ALL, который влияет на результат сравнения. В нашем случае выражение, включающее операцию сравнения, имеет вид: [Средний балл]>=ALL(Подчиненный запрос) В общем случае слева от знака сравнения стоит выражение, а знак сравнения может быть любым из списка (=,<>,<,>,<=,>=). Подчиненный запрос выдает множество значений. Значение выражения слева сравни- вается с каждым из значений, возвращаемых подчиненным запросом. Результат сравнения принимает значение TRUE, если все сравнения со значениями подчиненного запроса истинны. В нашем случае в резуль- тирующее множество попадают студенты, средние баллы которых не меньше среднего балла любого студента второй группы. Наряду с ALL находит применение предикат ANY (синоним SOME). В этом случае предикат принимает значение TRUE, когда ре- зультат сравнения хотя бы с одним из возвращаемых подчиненным за- просом значений принимает значение TRUE. Если выражение или некоторое значение из возвращаемых подчи- ненным запросом равно NULL, результат не определен при любом из упомянутых предикатов. Если в результате выполнения подчиненного запроса не возвращается никаких значений, то предикат принимает зна- чение FALSE. В рассматриваемом примере выдается пустая таблица, так как в первой группе (табл. 1) нет студентов, средний балл которых был бы не ниже среднего балла любого из студентов второй группы, например Белкина. Если заменить предикат ALL на ANY (или SOME), то все 4 студента первой группы попадут в результирующую таблицу, так как все они учатся лучше Лисицына. Пример SELECT [Название предмета], Дата FROM Экзаменаторы AS Э WHERE Э.Преподаватель IN (SELECT Преподаватель FROM Преподаватели WHERE Должность=”Профессор”); В этом примере применен предикат IN, сравнивающий выражение Преподаватели Таблица 4 Преподаватель ФИО Должность Степень Звание 1 2 3 Соловьев Щеглов Воробьев Ассистент Профессор Доцент Д.т.н К.т.н Профессор Доцент Экзаменаторы Таблица 3 Предмет Название предмета Преподаватель Дата 1 2 3 Информатика Математика Кибернетика 1 2 3 5.01.99 8.01.99 12.01.99 слева от него (поле “Преподаватель” из таблицы “Экзаменаторы”) со списком значений, выдаваемых подчиненным запросом. В результате главный запрос выдает название предмета и дату экзаменов, которые проводят профессора ( математика, 8.01.99 ). В главном запросе введен псевдоним Э для таблицы “Экзаменато- ры”. Для каждой таблицы и каждого запроса можно при желании опре- делить альтернативное имя. Это имя можно использовать как псевдо- ним вместо полного имени таблицы при задании имен столбцов в спи- ске выбора, в предложении WHERE или в подчиненных предложениях. Если имя таблицы или имя запроса совпадает с зарезервированным сло- вом языка SQL (например ORDER), такое имя нужно заключить в квад- ратные скобки. Пример SELECT [Название предмета], Дата FROM Экзаменаторы AS Э WHERE NOT EXISTS (SELECT * FROM Экзаменаторы INNER JOIN Экзамены ON Экзаменаторы.Предмет=Экзамены.Предмет WHERE Оценка=2 AND Экзамены.Предмет=Э.Предмет); Предикат EXISTS проверяет не значения отдельных полей, а нали- чие или отсутствие в подчиненном запросе записей, удовлетворяющих условиям отбора в предложениях WHERE и HAVING. Поскольку от- дельные поля не возвращаются, в подчиненном запросе список полей представлен звездочкой. В данном примере запрос выдает список пред- метов, по которым нет двоек, и даты экзаменов (математика 8.01.99, ки- бернетика 12.01.99). Внутренний запрос содержит ссылку на поле внешней таблицы (псевдоним Э). В таких случаях подчиненный запрос выполняется для каждой записи внешнего запроса. UPDATE Инструкция UPDATE позволяет производить изменения сразу во всех полях таблицы. Синтаксис: UPDATE таблицы SET присваивания WHERE условие Таблицы - отдельная таблица или объединение таблиц, как в предложе- нии FROM. Присваивания - последовательность присваиваний, разделенных запя- тыми. Каждое присваивание имеет вид “поле=выражение”. В указанной таблице для всех записей, удовлетворяющих условию, производятся присваивания. Пример UPDATE Экзаменаторы SET [Дата]=[Дата]+1 WHERE [Название предмета]=”Информатика” Даты всех экзаменов по информатике сдвигаются на один день вперед. Особенно удобно использовать инструкцию UPDATE, если требу- ется изменить сразу много записей или записи, подлежащие изменению, находятся в разных таблицах. INSERT Инструкция INSERT вставляет одну или несколько новых строк в таблицу или запрос. Вариант INSERT INTO таблица SELECT …; вставляет в таблицу строки, отобранные инструкцией SELECT. После имени таблицы можно указать в круглых скобках список столбцов, в которые помещаются новые значения. Запрос INSERT выполняется только в том случае, когда вставляемые данные удовлетворяют уста- новленным ограничениям на значения столбцов, условию на значения для таблиц и на целостность данных. В следующем примере используется таблица “Новые преподавате- ли”, в которой содержатся сведения о преподавателях, проходящих ме- сячный испытательный срок. INSERT INTO Преподаватели SELECT Преподаватель, ФИО, Должность, Степень, Звание FROM [Новые преподаватели] WHERE Дата INSERT INTO таблица VALUES(список_значений) или INSERT INTO таблица (список_полей) VALUES (спи- сок_значений) Если список полей опущен, предложение VALUES должно содер- жать значения для каждого поля таблицы. Пример INSERT INTO Преподаватели (Преподаватель, ФИО,Должность) VALUES (7, ”Журавлев”, ”Ассистент”); DELETE DELETE таблица.* FROM таблицы WHERE условие; Инструкция DELETE позволяет удалять записи из таблиц, пере- численных в предложении FROM. Удаляются записи, удовлетворяющие условию WHERE. Если в предложении FROM указана одна таблица, то ее можно обозначить звездочкой. Таблицу можно удалить с помощью инструкции DROP таблица; Удаление данных из отдельных полей можно осуществить с по- мощью инструкции UPDATE, в которой следует указать SET поле=Null. Пример DELETE * FROM Преподаватели WHERE Преподаватель=3; Отменяются все экзамены преподавателя с шифром 3. CREATE TABLE CREATE TABLE таблица (поля, индексы) Поля - список полей с характеристиками. Индексы - необязательный список составных индексов. Инструкция CREATE TABLE создает таблицу. Каждое поле в спи- ске поля может иметь до трех элементов, разделенных пробелами: “По- ле тип индекс”. Поле - имя поля. Тип - тип поля. Если поле текстовое, то в круглых скобках указывается размер в байтах. Индекс - предложение CONSTRAINT для создания простого индекса. Указывается только в случае, если по данному полю нужно создать ин- декс. Формат: CONSTRAINT имя вариант Имя - имя индекса. Вариант - необязательный параметр. Может принимать одно из сле- дующих значений: PRIMARY KEY - поле ключевое; UNIQUE - уникальный индекс ( в этом поле не допускаются повто- рения); REFERENCES - внешняя таблица (внешнее поле) – поле во внешней таблице. CONSTRAINT для создания составного индекса имеет аналогичный формат, но вместо поля используется список полей. Если в таблице оп- ределен составной уникальный индекс, то комбинация значений вклю- ченных в него записей должна быть уникальной для каждой записи таб- лицы, хотя отдельные поля могут иметь совпадающие значения. Нельзя использовать зарезервированные слова PRIMARY KEY , если в таблице уже определен ключ. Пример CREATE TABLE [Расписание экзаменов]( [Предмет] INTEGER CONSTRAINT КлПоле PRIMARY KEY, [Название предмета] TEXT, [Преподаватель] INTEGER, Группа TEXT(10), Дата DATATIME); CREATE INDEX Инструкция CREATE INDEX создает новый индекс для сущест- вующей таблицы. CREATE [UNIQUE] индекс ON таблица (список_полей) [WITH вариант] В квадратных скобках указаны необязательные элементы синтакси- са. Список_полей - список полей, составляющих индекс. Индекс - имя создаваемого индекса. Вариант - дополнительная характеристика индекса – один из вариан- тов: PRIMARY[(индексированные поля)],DISALLOWNULL, IGNORENULL. Последние два варианта запрещают использование пус- тых значений в индексированных полях. Инструкция DROP INDEX индекс ON таблица; удаляет индекс из таблицы. Пример CREATE INDEX Предметы ON [Расписание экзаменов]([Название предмета],[Группа]); Пример DROP INDEX Предметы; ALTER TABLE Инструкция ALTER TABLE позволяет добавлять и удалять поля и индексы таблицы в соответствии с форматом ALTER TABLE таблица вариант; Вариант принимает в каждом случае одно из значений: ADD COLUMN поле тип [CONSTRAINT индекс]; ADD CONSTRAINT составной_индекс; DROP COLUMN поле; DROP CONSTRAINT индекс. ADD COLUMN добавляет в таблицу новый столбец, который ста- новится последним столбцом таблицы. Необязательное слово COLUMN является поясняющим. Оно не связано с какими-либо операциями и может быть опущено. Если поле текстовое, после слова TEXT можно указать в круглых скобках длину поля в символах. Пример ALTER TABLE Преподаватели ADD COLUMN Комментарий TEXT(50); Вариант ADD CONSTRAINT позволяет создать в таблице состав- ной индекс. Варианты DROP удаляют из таблиц поля или индексы. ЗАДАНИЕ Если запросы разрабатываются в процессе создания базы данных, то в отчете запросы должны быть представлены на языке SQL. В случае выполнения самостоятельного задания по запросам на языке SQL необходимо создать 3 запроса: многотабличный запрос- выборку; запрос с группировкой данных и перекрестный запрос. Список рекомендуемой литературы 1. Мартин Грабер. Справочное руководство по SQL. M.:Изд-во “Лори”, 1997. 2. Вейскас Д. Эффективная работа с Microsoft Access 2: Пер. с англ. - СПб.: Питер,1996.-864 c.:ил. Составитель Евгений Александрович Бессонов ACCESS Запросы на языке SQL Методические указания к лабораторной работе для студентов специальности “Экономика и управление на предприятиях ” по курсу “Автоматизация экономи- ческих расчетов” Редактор Е.Л.Наркевич ЛР № 020313 от 23.12.96. Подписано в печать 23.11.2000. Формат 60х84/16. Бумага офсетная. Отпечатано на ризографе. Уч.-изд. л. 1,00. Тираж 100 экз. Заказ Кузбасский государственный технический университет. 650026, Кемерово, ул. Весенняя, 28. Типография Кузбасского государственного технического университета. 650099, Кемерово, ул. Д. Бедного, 4а. |