План-конспект открытого урока по дисциплине "Базы данных" на тему "Запросы к базе данных"

МИНИСТЕРСТВО  ПРОСВЕЩЕНИЯ ПМР

ГОУ «ТИРАСПОЛЬСКИЙ ТЕХНИКУМ ИНФОРМАТИКИ И ПРАВА»

УТВЕРЖДАЮ

Зам. директора по УР

________________ Н. В. Сыли

«_____» _______ 2012 г.

РАССМОТРЕНО И ОДОБРЕНО

на заседании  ЦК информационных и

экономических дисциплин

протокол № ______

от «_____» ________ 2012 г.

Председатель ЦК

________ Л. И. Гончарук

 

СОГЛАСОВАНО

Зав. метод. отделом

_________ Г. М. Пименовой 

«_____» _______ 2012 г.

ПЛАН

 

ОТКРЫТОГО  ЗАНЯТИЯ

по теме: «Запросы к базе данных»

 

Дисциплина: «Базы данных»

Специальность:  АСОИ и У в промышленности

Преподаватель:  Гончарук Л. И.

                                                                                                                                             

Дата проведения:  «23» 11. 2012 г.

Время проведения: 10⁰⁰

Место проведения:  ауд. № 36

Группа:   № 312

 

 

 

 

Тирасполь, 2012 г.

Дисциплина: «Базы данных»

Тема урока: «Запросы к базе данных»

Методическая цель занятия: развития творческих способностей учащихся при изучении дисциплины «Базы данных»

 

Цели занятия:

 

Образовательные:

 

ü Усвоение умений самостоятельно в комплексе применять знания, умения, навыки, осуществлять их перенос в новые условия;

ü Формирование умений создавать таблицу, добавлять запись в таблицу, изменять запись, удалять ненужные записи из таблицы, выполнять выборки из таблиц с использованием языка SQL, конструктора запросов в Visual FoxPro

 

Развивающие:

 

ü формирование грамотной речи, сознательного восприятия учебного материала;

ü развитие мышления, памяти, внимания;

ü развитие у студентов логического мышления;

формирование навыков самостоятельной работы студентов.

 

 Воспитательные:

 

ü развитие познавательного интереса учащихся, основ коммуникационного общения, уверенности в собственных силах;

ü воспитание творческого подхода к работе, желания экспериментировать;

ü способствовать формированию профессиональной культуры будущего специалиста

 

Тип урока:  урок формирования и закрепления умений и навыков

Вид урока:  практическое занятие

 

Методы обучения:

ü Самостоятельная работа;

ü Работа на компьютере;

ü Беседа (словесный);

ü Инструктаж;

ü Демонстрация (наглядный).

Методы контроля ЗУН:

ü Самоконтроль;

ü Фронтальный опрос

Комплексно-методическое обеспечение занятия

Материально-техническое оснащение: компьютеры, мультимедийный проектор

Программное обеспечение урока: программа MS Visual FoxPro 9.0

Дидактическое обеспечение урока: методические рекомендации к  практическим занятиям, карточки-заданий, плакат Базы данных

Внутрипредметные связи: Реляционная модель данных, реляционные операции

Межпредметные связи: Информатика, Основы алгоритмизации и программирования, Информационные технологии, Операционные системы и среды

СТРУКТУРА УРОКА

I.      Организационный этап (2 мин)

ü Приветствие;

ü Проверка отсутствующих на занятии;

ü Проверка готовности к занятию;

II.   Мотивация учебной деятельности (2 мин);

III.             Актуализация опорных знаний  и умений  (15 мин)

IV.            Применение знаний (30 мин):

a)     упражнения по образцу и в сходных условиях с целью выработки умений безошибочного применения знаний;

V.        Валеологическая пауза (5 мин)

b)    упражнение с переносом знаний в новые условия (15 мин)

VI.            Подведение итогов (3 мин)

VII.         Информация о домашнем задании (3 мин)

ОСНОВНАЯ ЛИТЕРАТУРА

1.     Андон Ф., Резниченко В. Язык запросов SQL. Учебный курс. — СПб.: Питер; Киев: Издательская группа BHV, 2006. — 416 с: ил.

2.     Каратыгин С.А., Тихонов А.Ф., Тихонова Л.Н. Работа в Visual FoxPro на примерах. М.:

3.     Лебедев А. Н. Visual FoxPro 9.0 – М.: НТ Пресс, 2005. – 328 с.

4.     М.Грабер.  Введение  в SQL. /пер.  с  англ. - М.:  Изд. «ЛОРИ», 1996.

5.     Омельченко Л. Н. Самоучитель Visual FoxPro 8. – Спб.: БХВ-Петербург, 2003.- 688 с.

ДОПОЛНИТЕЛЬНАЯ ЛИТЕРАТУРА

6.     Бейли Л. Изучаем SQL. — СПб.: Питер, 2012. — 592 с.: ил.

7.     Джеймс Р. Грофт, Пол Н. Вайнберг. SQL: полное руководство /пер. с англ. - Киев.: Изд. «BHV», 2005

8.     Форта Бен. Освой самостоятельно SQL. 10 минут на урок. 3-е издание Пер. с англ. – М.: Издательский дом «Вильямс», 2005, 288 с.

ХОД УРОКА

 

I.                 Организационный этап (2 мин)

ü Приветствие;

ü Проверка отсутствующих на занятии;

ü Проверка готовности к занятию.

 

II.              Мотивация учебной деятельности (2 мин);

Объявление темы занятия, его целей.

Хотите вы этого или нет, но вы постоянно пользуетесь базами данных. Каждый раз, когда вы выбираете имя в адресной книге электронной почты, вы используете базу данных. Если вы что-то ищете при помощи поискового сервера в Internet, вы используете базу данных. Когда вы регистрируетесь в локальной сети на работе, вы вводите свое имя и пароль, которые затем сравниваются со значениями, хранящимися в базе данных. И даже когда вы используете свою пластиковую карту в банкомате, вы используете базу данных при проверке PIN-кода и остатка на счету.

Сегодня вы увидите, как работать с БД с помощью команд и конструктора запросов, убедитесь в эффективности этих команд.

Не важно, кем вы будете — разработчиком приложений, администратором баз данных, Web-дизайнером или пользователем пакета Microsoft Office, — хорошее практическое знание SQL поможет вам взаимодействовать с базами данных.

 

III.          Актуализация опорных знаний  и умений  (15 мин)

Фронтальный опрос учащихся с использованием презентации

1.                Что такое БД? Из чего состоит БД?

База данных – это один или несколько файлов, содержащие связанную (по смыслу) между собой информацию. Более формальное определение базы данных (БД) – поименованная, целостная, единая система данных, организованная по определённым правила, которые предусматривают общие принципы описания, хранения  и обработки данных.

БД по английски DATEBASE

Структура базы данных следующая:

Реляционная база данных состоит из одной или нескольких таблиц.

Каждая таблица имеет одно или несколько полей.

В каждой таблице имеется одна или несколько записей.

Пример таблицы

 

 

 

 

 

 

 

 

 Таблица 1.1. Табличная база данных

№ п/п	Название	Тип процессора	Оперативная память (Мбайт)
1.	Compaq	Celeron	64
2.	Dell	Pentium III	128
3.	IBM	Pentium 4	256

 

2.                Что такое запрос? Какие бывают запросы?

Запрос – это спецификация (предписание)  на специальном языке базы данных для обработки данных. В реляционных СУБД запросы к базе данных выражаются на языке SQL.

Классификация запросов в реляционных СУБД

 

 

 

 

3.                Что такое SQL?

Это стандартизированный язык структурированных запросов в реляционных СУБД. SQL (Structured Query Language)

Язык SQL относится к декларативным (непроцедурным) языкам программирования. В отличие от процедурных языков (С, Паскаль, Фортран, Кобол, Бейсик) на нем формулируются предложения (инструкции) о том, «что сделать», но не «как сделать».

Команды  SQL бывают следующих видов:

1.     Язык описания данных DDL (Data Definition Language) это подмножество SQL, используемое для определения и модификации различных структур данных. К данной группе относятся команды, предназначенные для создания, изменения и удаления различных объектов базы данных. Команды CREATE TABLE создать таблицу, ALTER TABLE изменить структуру таблицы, DROP TABLE удалить таблицу

2.     Язык манипулирования данными DML (Data Manipulation Language) команды позволяют пользователю перемещать данные в базу данных и из нее: INSERT — осуществляет вставку строк в таблицу. DELETE — осуществляет удаление строк из таблицы. UPDATE — осуществляет модификацию данных в таблице.

3.     Язык запросов DQL (Data Query Language ) состоит из единственной команды SELECT выбрать записи из таблицы

 

4.                Какие типы данных используются для полей таблицы в VFP?

Типы данных используются при создании таблиц, для определения типа значений каждого поля таблицы

 

5.                Синтаксис команды CREATE DATABASE, CREATE TABLE

 

Чтобы создать БД введите в командном окне Command (Команда) команду CREATE DATABASE, имеющую следующий синтаксис:

CREATE DATABASE [имяБазыДанных | ?]

В том случае, если вы не укажете имя создаваемой базы данных имяБазыДанных, на экране откроется диалоговое окно Create (Создать), позволяющее его ввести.

 

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

1.     Как будет называться таблица?

2.     Как будут называться столбцы (поля) таблицы?

3.     Какие типы данных будут закреплены за каждым столбцом?

4.     Какой размер памяти должен быть выделен для хранения каждого столбца?

 

Синтаксис команды CREATE TABLE создает таблицу, имеющую указанные поля.

CREATE TABLE TableName1 (<описание полей>)

         Описание поля это комбинация имени поля, его типа, через пробел. После указания типа поля в скобках указывается размер поля. Если полей несколько, то их описания перечисляются через запятую.

 

Пример . Создать таблицу для хранения данных о товарах, поступающих в продажу в некоторой торговой фирме. Необходимо учесть такие сведения, как название и тип товара, его цена, сорт и город, где товар производится.

CREATE TABLE Товар

(Name C(50),price Y,type C(50), sort C(50),city C(50))

 

6.                Синтаксис команды INSERT

* В следующем примере открывается таблица customer в базе данных

* testdata. Содержимое текущей записи распределяется по переменным

* памяти, а структура таблицы копируется в новую таблицу cust2. Команда

* INSERT - SQL используется для вставки новой записи в таблицу cust2, а

* команда BROWSE выводит новую запись на экран.

 

CLOSE DATABASES

OPEN DATABASE (HOME( ) + 'samples\data\testdata')

USE Customer               && Open customer table

SCATTER MEMVAR

 

COPY STRUCTURE TO cust2

INSERT INTO cust2 FROM MEMVAR

SELECT CUST2

BROWSE

7.                Синтаксис команды UPDATE

8.                Синтаксис  команды DELETE

9.                Синтаксис команды SELECT

 

 

Internet Movie Database (IMDb, в переводе на рус. База данных фильмов в Интернете) — крупнейшая в мире база данных и веб-сайт о кинематографе. В базе собрана информация (по состоянию на конец сентября 2010 года) о более чем 2 млн.138 тыс. кинофильмов, телесериалов и отдельных их серий, а также о 4,5 млн персоналий, связанных с кино, — актёров, режиссёров, сценаристов и др.[1]

 

C 1998 года базой данных IMDb владеет компания «Amazon.com». Почти вся информация IMDb находится в свободном доступе, сайт IMDb.com функционирует на основе свободного программного обеспечения (Apache, Perl и т. д.).

1.     В самом общем смысле база данных – это один или несколько файлов, содержащие связанную (по смыслу) между собой информацию. Более формальное определение базы данных (БД) – поименованная, целостная, единая система данных, организованная по определённым правила, которые предусматривают общие принципы описания, хранения  и обработки данных.

 

Тип данных

Тип разрешенных для хранения данных. Каждому столбцу базы данных присваивается тип данных, который запрещает (или разрешает) хранить в нем определенную информацию.

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

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

Совместимость типов данных Типы данных и их названия являются одним из основных источников несовместимости в SQL. Основные типы данных обычно поддерживаются всеми СУБД, в отличие от некоторых расширенных типов. Более того, иногда вы будете сталкиваться с тем фактом, что один и тот же тип данных в разных СУБД называется по-разному. К сожалению, с этим ничего нельзя поделать, но помнить об этом при создании структуры таблиц необходимо.

Данные в таблице хранятся в строках; каждая запись хранится в своей строке. Возвращаясь к сравнению с сеткой, можно сказать, что ее вертикальные столбцы являются столбцами таблицы, а горизонтальные строки — строками таблицы.

Например, в таблице клиентов информация о каждом клиенте хранится в отдельной строке. Число строк в таблице равно числу записей о клиентах.

 

 

 

 

VIII.      Актуализация опорных знаний  и умений  (мин)

Презентация Команды SQL

 

Алгоритм работы

 

2. сортировка выбранных данных при помощи предложения ORDER BY оператора SELECT. Это предложение, которое должно быть последним в операторе SELECT, можно использовать для сортировки данных по одному или нескольким столбцам.

 На этом уроке вы узнаете, как использовать предложение

WHERE оператора SELECT для указания предложений поиска.

 В таблицах баз данных обычно содержится очень много

информации и довольно редко возникает необходимость

выбирать все строки таблицы. Гораздо чаще бывает нужно

извлечь какую-то часть данных таблицы для каких-либо

действий или отчетов. Выборка только необходимых дан-

ных включает в себя критерий поиска, также известный

под названием предложение фильтрации.

В операторе SELECT данные фильтруются путем указа-

ния критерия поиска в предложении WHERE. Предложение

WHERE указывается сразу после названия таблицы (предложения FROM) следующим образом:

 

SELECT prod_name, prod_price;

FROM Products;

WHERE prod_price = 3.49

Операции в предложении в Where

 

SELECT prod_name, prod_price

FROM Products

WHERE prod_price BETWEEN 5 AND 10;

 SELECT prod_name

FROM Products

WHERE prod_jprice IS NULL;

В этом уроке рассказывалось о том, как отфильтровы-

вать возвращаемые данные при помощи предложения

WHERE оператора SELECT. Теперь вы знаете, как можно про-

верить данные на равенство, неравенство, наличие значений

больше чем и меньше чем, диапазон значений, а также на

значение NULL.

 Использование ключевого слова AND

Чтобы отфильтровать данные по более чем одному

столбцу, необходимо воспользоваться ключевым словом AND

для добавления предложений в предложение WHERE. Вот

как это делается:

ВВОД

SELECT prod_id, prod_price, prod_name

FROM Products

WHERE vend_id = 'DLL01' AND prod_price <=4;

 Использование ключевого слова OR

Действие ключевого слова OR противоположно действию

ключевого слова AND. Ключевое слово OR указывает системе

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

рые удовлетворяют хотя бы одному предложению. На са-

мом деле в большинстве лучших СУБД второе предложение

даже не рассматривается в предложении OR WHERE, если

удовлетворено первое предложение. (Если первое предло-

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

второго предложения.)

Взгляните на следующий оператор SELECT:

ВВОД

SELECT prod_name, prod_price

FROM Products

WHERE vend id = 'DLL011 OR vend id = 'BRS01’

OR

Ключевое слово, применяемое в предложении WHERE

для того, чтобы возвращались все строки, удовлетво-

ряющие любому из указанных предложений.

 В следующей инструкции SELECT используется комбинация

ключевых слов AND и OR для формулирования предложе-

ния WHERE:

ВВОД

SELECT prod_name, prod_j?rice

FROM Products

WHERE vend_id = 'DLL01' OR vend_id =

AND prod_price >= 10;

'BRS01

Использование ключевого

слова IN

Ключевое слово IN используется для указания диапазона

условий, любое из которых может быть выполнено. При

этом значения, заключенные в скобки, перечисляются через

запятую. Рассмотрим следующий пример:

ВВОД

SELECT prod_name, prodjprice

FROM Products

WHERE vend_id IN ('DLL011, 'BRS01!

ORDER BY prod_name

Использование ключевого

слова NOT

Логический оператор NOT предложения WHERE служит

для выполнения только одной функции — отрицать все

 предложения, следующие за ним. Поскольку NOT никогда

не используется сам по себе (а только вместе с другими ло-

гическими операторами), его синтаксис немного отличается

от синтаксиса остальных операторов. В отличие от них, NOT

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

нужно отфильтровать, а не после.

NOT

Ключевое слово, применяемое в предложении WHERE для отрицания какого-то условия.

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

логического оператора NOT. Чтобы извлечь список продук-

тов, изготовленных всеми производителями, кроме DLL01,

можно потребовать выполнить следующее:

 SELECT prod_name

FROM Products

WHERE NOT vend_id =

ORDER BY prod_name;

' Использование

метасимволов для

фильтрации

В этом уроке вы узнаете, что такое метасимволы, как их

использовать и как выполнять поиск с применением мета-

символов и логического оператора LIKE для фильтрации

выводимых данных.

Использование логического

оператора LIKE

Все предыдущие операторы, которые мы рассмотрели,

производили фильтрацию по известным значениям. Они

искали совпадения по одному или нескольким значениям,

более чем и менее чем известное значение или диапазон

значений. При этом везде искалось известное значение. Од-

нако фильтрация данных таким способом не всегда работа-

ет. Например, как бы вы искали продукты, в названии ко-

торых содержатся слова bean bag? Этого нельзя сделать при

помощи простых операций сравнения, здесь на помощь

приходит поиск с использованием метасимволов. При по-

мощи метасимволов можно создавать условия поиска дан-

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

названии которых содержатся слова bean bag, необходимо

составить шаблон поиска, позволяющий найти текст bean

bag в любом месте названия продукта.

Метасимволы  Специальные символы, применяемые для поиска части значения.

 Шаблон поиска Условие поиска, состоящее из текста, метасимволов и любой их комбинаций.

Метасимвол "знак процента" (%)

Наиболее часто используемый метасимвол — знак про-

цента (%). В строке поиска % означает найти все вхождения

любого символа. Например, чтобы найти все продукты, на-

звания которых начинаются со слова Fish, можно выпол-

нить следующий запрос:

 SELECT prod_id, prod_name

FROM Products

WHERE prod_name LIKE 'Fish%';

 Метасимвол "квадратные скобки" ([ ]) используется для

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

совпадать со значением, причем точно в указанном месте

(в местоположении метасимвола).

SELECT prod_id, prod_name

FROM Customers

WHERE cust_contact LIKE •’[JM]%’

ORDER BY cust contact

 Можно использовать метасимвол, выполняющий проти-

воположное действие, добавив перед ним символ Л. Напри-

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

не начинаются с буквы J или М (в отличие от предыдущего

примера):

 SELECT prod_id, prod_name

FROM Customers

WHERE cust_contact LIKE ' [AJM] %

ORDER BY cust contact

В этом уроке вы узнаете, что такое вычисляемые поля, как

их создавать и как использовать псевдонимы для ссылки на

такие поля из вашего приложения.

Что такое вычисляемые поля

Данные, хранимые в таблицах базы данных, обычно бы-

вают представлены не в таком виде, который необходим

для ваших приложений. Вот несколько примеров.

• Вам необходимо отобразить поле, содержащее имя

компании с ее адресом, но эта информация располо-

жена в разных столбцах таблицы.

 Конкатенация

Комбинирование значений (путем присоединения их

друг к другу) для получения одного "длинного" значения.

 Для этого необходимо соединить два значения. В SQL-выражении SELECT можно выполнить конкатенацию двух столбцов при помощи специального оператора. В зависимости от СУБД это может быть знак "плюс" (+) или две вертикальные черточки (||).

 Псевдоним — это альтернативное имя

для поля или значения. Псевдонимы присваиваются при

помощи ключевого слова AS. Взгляните на следующий опе-

ратор SELECT:

  что такое вычисляемые поля и

как их можно создавать. Были рассмотрены примеры ис-

пользования вычисляемых полей для конкатенации строк и

выполнения математических операций. Кроме того, вы уз-

нали, как следует создавать и использовать псевдонимы

так, чтобы ваше приложение могло обращаться к вычис-

ляемым полям.

 

Использование статистических

функций

Часто бывает необходимо просуммировать данные без их

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

функции. SQL-запросы с этими функциями часто исполь-

зуются с целью выборки данных для анализа и создания

отчетов. Примерами таких выборок могут послужить:

• определение числа строк в таблице (либо числа строк,

которые удовлетворяют какому-то условию или со-

держат определенное значение);

• получение суммы по набору строк в таблице;

• поиск наибольшего, наименьшего и среднего значе-

ний из столбца таблицы (из всех или из каких-то

конкретных строк).

В каждом из этих примеров необходимы какие-то итого-

вые данные по таблице, а не сами данные. Поэтому воз-

вращение реальных данных таблицы было бы пустой тра-

той времени и ресурсов (не говоря о пропускной способно-

сти сети). Итак, все, что вам нужно, — это только итоговая

информация.

Чтобы облегчить такой способ извлечения информации,

в SQL предусмотрен набор из пяти статистических функ-

ций, которые перечислены в табл. 9.1. Эти функции позво-

ляют выполнять все варианты выборки, которые были пе-

речислены выше. В отличие от функций манипулирования

данными из предыдущего урока, статистические SQL-

 Использование статистических

функций

Часто бывает необходимо просуммировать данные без их

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

функции. SQL-запросы с этими функциями часто исполь-

зуются с целью выборки данных для анализа и создания

отчетов. Примерами таких выборок могут послужить:

• определение числа строк в таблице (либо числа строк,

которые удовлетворяют какому-то условию или со-

держат определенное значение);

• получение суммы по набору строк в таблице;

• поиск наибольшего, наименьшего и среднего значе-

ний из столбца таблицы (из всех или из каких-то

конкретных строк).

В каждом из этих примеров необходимы какие-то итого-

вые данные по таблице, а не сами данные. Поэтому воз-

вращение реальных данных таблицы было бы пустой тра-

той времени и ресурсов (не говоря о пропускной способно-

сти сети). Итак, все, что вам нужно, — это только итоговая

информация.

Чтобы облегчить такой способ извлечения информации,

в SQL предусмотрен набор из пяти статистических функ-

ций, которые перечислены в табл. 9.1. Эти функции позво-

ляют выполнять все варианты выборки, которые были пе-

речислены выше. В отличие от функций манипулирования

данными из предыдущего урока, статистические SQL-

 Статистические SQL-функции

Функция Описание

AVG () Возвращает среднее значение столбца

COUNT () Возвращает число строк в столбце

МАХ () Возвращает самое большое значение в столбце

MIN () Возвращает самое маленькое значение в столбце

SUM () Возвращает сумму значений столбца

 

 SELECT AVG(prod_price) AS avg_jprice

FROM Products;

ВЫВОД

avg_price

6.823333

 Функция COUNT () подсчитывает число строк. При по-

мощи функции COUNT () можно узнать общее число строк в

таблице или число строк, удовлетворяющих определенному

критерию.

Эту функцию можно использовать двумя способами:

• В виде COUNT {*) для подсчета числа строк в таблице

независимо от того, содержат столбцы значения NULL

или нет.

• В виде COUNT (column) для подсчета числа строк, ко-

торые имеют значения в указанных столбцах, причем

значения NULL игнорируются.

В первом примере возвращается общее число имен кли-

ентов, содержащихся в таблице Customers:

 SELECT COUNT(*) AS num_cust

FROM Cu s t ome r s;

 SELECT COUNT(cust_email) AS num_cust

FROM Customers;

 Создание групп

Группы создаются с помощью предложения GROUP BY

оператора SELECT.

Лучше всего это можно объяснить на примере:

ВВОД

SELECT vend_id, COUNT(*) AS numjprods

FROM Products

GROUP BY vend id;

 Вышеприведенный оператор SELECT предписывает вы-

вести два столбца —vend_id, содержащий идентификатор

поставщика продукта, и num_prods, содержащий вычис-

ляемые поля (он создается с помощью функции COUNT (*)).

Предложение GROUP BY указывает СУБД сортировать дан-

ные и группировать их по столбцу vend_id. В результате

значение num_prods будет вычисляться по одному разу для

каждой группы записей vend_id, а не один раз для всей

таблицы products. Как видите, в результатах указывается,

что поставщик BRS01 предлагает три продукта, поставщик

DLL01 — четыре продукта, а поставщик FNG01 — 2 продукта.

Поскольку было использовано предложение GROUP BY,

не потребовалось указывать каждую группу, для которой

должны быть произведены вычисления. Это было сделано

автоматически. Предложение GROUP BY указывает СУБД

группировать данные и затем выполнять вычисление по

каждой группе, а не по всему набору результатов.

 Прежде чем использовать предложение GROUP BY, озна-

комьтесь с важными правилами, которыми необходимо ру-

ководствоваться .

• В предложениях GROUP BY можно указывать столько

столбцов, сколько вам необходимо. Это позволяет

вкладывать группы одна в другую, благодаря чему

обеспечивается тщательный контроль за тем, какие

данные подлежат группированию.

• Если вы используете вложенные группы в предложе-

нии GROUP BY, данные суммируются для последней

указанной вами группы. Другими словами, если вве-

дено группирование, вычисления осуществляются для

всех указанных столбцов (вы не сможете вернуть

данные для каждого отдельного столбца).

• Каждый столбец, указанный в предложении

GROUP BY, должен быть столбцом выборки или вы-

ражением (но не функцией группирования). Если в

операторе SELECT используется какое-то выражение,

то же самое выражение должно быть указано в пред-

ложении GROUP BY. Псевдонимы применять нельзя.

• В большинстве реализаций SQL нельзя указывать в

предложении GROUP BY столбцы, в которых содер-

жатся данные переменной длины (т.е. столбцы, со-

держащие текстовые поля или поля комментариев).

• За исключением операторов статистических вычисле-

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

SELECT, должен быть представлен в предложении

GROUP BY.

• Если столбец, подлежащий группированию, содержит

строку со значением NULL, оно будет возвращено в

качестве группы. Если имеется несколько строк со

значениями NULL, они будут сгруппированы вместе.

• Предложение GROUP BY должно следовать после пред-

ложения WHERE и до какого-либо предложения

ORDER BY.

 SELECT vend_id, COUNT(*) AS num_prods

FROM Products

WHERE prod_price >= 4

GROUP BY vend_id

HAVING COUNT(*) >= 2;

Первая строка

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

щий статистическую функцию, — точно так же, как в пре-

дыдущих примерах. Предложение WHERE фильтрует все

строки со значениями в столбце prod_price не менее 4.

Затем данные группируются по столбцу vend_id, а потом

предложение HAVING фильтрует только группы, содержа-

щие не менее двух членов. При отсутствии предложения

WHERE была бы получена лишняя строка (поставщик, пред-

лагающий 4 продукта, каждый из которых дешевле 4), как

показано ниже.

 

Подзапросы Подзапросы всегда обрабатываются, начиная с самого

внутреннего оператора SELECT в направлении "изнутри на-

ружу".

Подзапросы как вычисляемые поля

SELECT cust_name,

cust state,

 (SELECT COUNT(*)

FROM Orders

WHERE Orders.cust_id = Customers.cust_id) AS orders

FROM Customers

ORDER BY cust name;

 В этом уроке вы узнали, что такое подзапросы и как и?

можно использовать. Чаще всего подзапросы используют i

операторах IN предложения WHERE и для заполнения вы

числяемых столбцов. Были представлены примеры опера

ций обоих названных типов.

Вариант

 

Таблица

Создание таблицы

 

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

 

Таблица – основной объект для хранения информации в реляционной базе данных. Она состоит из содержащих данные строк и столбцов, занимает в базе данных физическое пространство и может быть постоянной или временной.

 

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

 

Пример 3.2. Создание таблицы для хранения данных о товарах, поступающих в продажу в некоторой торговой фирме. (html, txt)

 

Пример 3.3.Создать таблицу для сохранения сведений о постоянных клиентах с указанием названий города и фирмы, фамилии, имени и отчества клиента, номера его телефона.

CREATE TABLE  Клиент

    (Фирма       CHARACTER(50) NOT NULL,

    Фамилия      CHARACTER(50) NOT NULL,

    Имя          CHARACTER(50) NOT NULL,

    Отчество     CHARACTER(50),

    ГородКлиента CHARACTER(50),

    Телефон      CHAR(10) NOT NULL)

 Пример 3.3. Создание таблицы для сохранения сведений о постоянных клиентах. (html, txt)

Изменение таблицы

 

Структура существующей таблицы может быть модифицирована с помощью команды ALTER TABLE, упрощенный синтаксис которой представлен ниже:

ALTER TABLE имя_таблицы

  {[ADD [COLUMN] имя_столбца тип_данных [

    NULL | NOT NULL ]]

  | [DROP [COLUMN] имя_столбца]}

 

В среде MS SQL Server упрощенный синтаксис команды модификации таблицы имеет вид:

ALTER TABLE имя_таблицы

  {[ALTER COLUMN имя_столбца

  {новый_тип_данных [(точность[,масштаб])]

    [ NULL | NOT NULL ]}]

  | ADD { [имя_столбца тип_данных]

  | имя_столбца AS выражение } [,...n]

  | DROP {COLUMN имя_столбца}[,...n]

  }

 

Команда позволяет добавлять и удалять столбцы, изменять их определения.

 

Одно из основных правил при добавлении столбцов в существующую таблицу гласит: когда в таблице уже содержатся данные, добавляемый столбец не может быть определен с атрибутом NOT NULL. Этот атрибут означает, что для каждой строки данных соответствующий столбец должен содержать некоторое значение, поэтому добавление столбца с атрибутом NOT NULL приводит к появлению противоречия – уже существующие строки данных таблицы не будут иметь в новом столбце ненулевых значений.

 

Тем не менее существует способ добавления обязательных полей в существующую таблицу. Для этого необходимо:

добавить в таблицу новый столбец, определив его с атрибутом NULL (т.е. столбец не обязан содержать каких-либо значений);

ввести в новый столбец какие-либо значения для каждой строки данных таблицы ;

убедившись, что новый столбец содержит ненулевые значения для каждой строки данных, изменить структуру таблицы, заменив атрибут этого столбца на NOT NULL.

 

При изменении определений столбцов следует принимать во внимание некоторые общепринятые правила:

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

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

количество разрядов числового типа данных всегда может быть увеличено;

количество разрядов числового типа данных может быть уменьшено только в том случае, если количество разрядов наибольшего значения в соответствующем столбце не будет превосходить нового числа разрядов, определенного для этого столбца ;

количество десятичных знаков числового типа данных может быть уменьшено или увеличено;

тип данных столбца, как правило, может быть изменен.

 

Некоторые реализации фактически могут ограничить разработчика в использовании некоторых опций команды ALTER TABLE. Например, может оказаться недопустимым удаление столбцов из существующей таблицы. Чтобы добиться этого, сначала потребуется удалить саму таблицу и только потом заново ее построить с нужными столбцами. Причем уже внесенные в таблицу данные будут потеряны.

 

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

 

Пример 3.4. Добавить в таблицу Клиент поле для номера расчетного счета.

ALTER TABLE Клиент ADD Рас_счет CHAR(20)

 Пример 3.4. Добавление в таблицу Клиент поля для номера расчетного счета. (html, txt)

Удаление таблицы

 

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

DROP TABLE имя_таблицы [RESTRICT | CASCADE]

 

Следует отметить, что эта команда удалит не только указанную таблицу, но и все входящие в нее строки данных. Если требуется удалить из таблицы лишь данные, сохранив структуру таблицы, следует воспользоваться командой DELETE.

 

Оператор DROP TABLE дополнительно позволяет указывать, следует ли операцию удаления выполнять каскадно. Если в операторе указано ключевое слово RESTRICT, то при наличии в базе данных хотя бы одного объекта, существование которого зависит от удаляемой таблицы, выполнение оператора DROP TABLE будет отменено. Если указано ключевое слово CASCADE, автоматически удаляются и все прочие объекты базы данных, чье существование зависит от удаляемой таблицы, а также другие объекты, зависящие от удаляемых объектов. Общий эффект от выполнения оператора DROP TABLE с ключевым словом CASCADE может оказаться весьма ощутимым, поэтому подобные операторы следует использовать с максимальной осторожностью.

 

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

В качестве основы при изложении языка был выбран стандарт ISO-ANSI 2003 г.

Ход урока

 

 

 

 

Практические задания по языку SQL

 

Вариант № 1

 

С помощью команд языка SQL выполнить следующие действия с базой данных «Гостиница»:

1. Создать таблицы:

- Корпус (наименование, количество этажей, количество номеров, категория);

- Номер (номер, количество мест, количество комнат, стоимость, телефон);

- Клиент (ФИО, пол, паспортные данные);

- Проживание (дата заезда, дата выезда, способ оплаты).

2. Выполнить следующие запросы:

- выдать информацию о всех корпусах у которых категория «Люкс»;

- выдать информацию о комнатах, в которых количество комнат больше 2;

- выдать информацию о номерах, которые находятся в одноэтажных корпусах и стоимость проживания в них не больше 1000 рублей;

- выдать информацию о свободных номерах  на 25 декабря 2009 года;

- выдать фамилии всех клиентов, когда- либо проживавших в номере «100», в алфавитном порядке. 

3. Добавить в таблицу «Проживание» информацию о том, что клиент «Иванов» въехал в номер «55» 20 декабря 2009 года, дата выезда 5 января 2010 года и способ оплаты – наличные.

4. Повысить стоимость проживания на 200 рублей в номерах корпусов, в которых количество номеров меньше 50.

5. Удалить все записи из таблицы «Проживание» в которых дата выезда меньше 31 декабря 2000 года.

 

 

Вариант № 2

 

С помощью команд языка SQL выполнить следующие действия с базой данных «Магазин»:

1. Создать таблицы:

- Продавец (ФИО, адрес прописки, стаж работы, телефон);

- Товар (наименование, цена, количество);

- Покупатель(ФИО, адрес, место работы, телефон);

- Покупка(дата покупки, способ доставки, способ оплаты).

2. Выполнить следующие запросы:

- выдать информацию о всех продавцах, у которых стаж работы больше 10 лет;

- выдать информацию о товаре, стоимость которого больше 500 рублей и количество которого находится в диапазоне от 10 до 50 штук;

- выдать информацию о покупателя, совершивших покупку 31 декабря 2009 года;

- выдать ФИО продавцов в алфавитном порядке, совершивших продажу товара «Телевизор» в кредит;

- выдать название товара, который был продан продавцом «Ивановым» покупателю «Петров» 20 декабря 2009 года.

3. Добавить в таблицу «Покупка» информацию о том, что 25 декабря 2009 года покупатель «Вьюгина» купила товар «Самовар» у продавца «Карпова», способ доставки – самовывоз, оплата – наличными.

4. Уменьшить стоимость товара на 10% количество которого меньше 50 штук.

5. Удалить все таблицы.

 

 

Вариант № 3

 

С помощью команд языка SQL выполнить следующие действия с базой данных «ГИБДД»:

1. Создать таблицы:

- Инспектор(ФИО, звание, стаж работы);

- Автовладелец(ФИО, адрес, стаж вождения);

- Машина (госномер, владелец, марка, дата выпуска);

- ДТП (дата и время происшествия, вид происшествия, место происшествия).

2. Выполнить следующие запросы:

- выдать информацию о всех инспекторах, у которых стаж работы меньше 1 года;

- выдать информацию о машинах и их владельцах дата выпуска которых меньше 1980 года;

- выдать ФИО инспекторов, оформлявших ДТП 20 и 25 декабря 2009 года;

- выдать ФИО автовладельцев и госномера их машин, побывавших в ДТП в 2009 году;

- выдать госномера автомашин попавших в аварию на перекрестке «ул. Хакурате – ул. Жуковского» и ФИО инспекторов, оформлявших ДТП.

3. Добавить в таблицу «ДТП» информацию о том, что машина «Волга» госномер «У 101 АА» попала в аварию 1 января 2010 года и оформлял ДТП инспектор «Сидоров».

4. Изменить в таблице «Инспектор» звание с «Младший лейтенант» на «Лейтенант» если его стаж работы больше 5 лет.

5. Удалить из таблицы «ДТП» все аварии, которые были совершены до 1990 года.

 

 

Вариант № 4

 

С помощью команд языка SQL выполнить следующие действия с базой данных «Больница»:

1. Создать таблицы:

- Врач(ФИО, должность, стаж работы);

- Пациент(ФИО, пол, дата рождения);

- Лекарство(наименование, стоимость, назначение);

- Лечение(начало лечения, завершение лечения, результат).

2. Выполнить следующие запросы:

- выдать информацию в алфавитном порядке о лекарствах стоимостью больше 1000 рублей;

- выдать ФИО пациентов закончивших лечение 1 декабря 2009 года с результатом лечения «Полностью здоров»;

- выдать ФИО врачей когда-либо выписывавших лекарство «Новокаин»;

- выдать ФИО всех женщин, начавших лечение в декабре 2009 года;

- выдать ФИО врача, лечившего пациента «Соколов» в период с 1 по 21 ноября 2009 года.

3. Добавить в таблицу «Лечение» информацию о том, что пациент «Соколов» в период с 1 по 21 ноября 2009 года лечился у врача «Орлова» с применением лекарства «Анальгин».

4. Увеличить стоимость лекарства на 20% если его назначение «Против гриппа».

5. Удалить таблицы «Лечение» и «Врач».

 

Преподаватель                              ___________________  Гончарук Л. И.

 Коптева Л.Г., Смирнов С.Н. Системы управления базами данных:   классы, модели БД, проектирование, SQL, хранилища данных .  – М.: РОАТ, 2009. – 91 с.

3.  Карпова Т.С. Базы данных: модели, разработка, реализация –СПб.: Питер, 2001.

4.Омельченко Л.М.  Самоучитель Visual FoxPro 6.0, СПб., 1999.

5. Картыгин С.А., Тихонов А.Ф., Тихонова Л.Н. Visual FoxPro 7, М.: «Бином», 2002.

 

Дополнительная литература

 

 6.  Р. Ахаян, А. Горев, С. Макашарипов. Эффективная работа с СУБД. - С.-Петербург, 2001. - 700 с

7. Мартин Грабер. Введение в SQL.- М.: ЛОРИ, 2001. - 643 с 

Команда описания таблицы в VFP

CLOSE DATABASES

OPEN DATABASE (HOME( ) + 'samples\data\testdata')

USE orders

CLEAR

DISPLAY STRUCTURE

 

В MySQL

DESC table1

 

 

 

 

 

 

 

 

 

 

 

Структурированному языку запросов — SQL, который является стандартным языком реляционных систем управления базами данных (СУБД). Международная организация стандартизации (ISO) и Американский национальный институт стандартов (ANSI) уже более двадцати лет ведут работы по поддержанию стандарта этого языка, и практически все современные  реляционные СУБД используют его в качестве языка интерфейса пользователей с  базами данных.

Таким образом, знание SQL открывает путь к успешному освоению и 

использованию практически любой современной реляционной СУБД.

Домашнее задание