Использование операций SOME, ANY и ALL
Все эти ключевые слова используются с подзапросами, которые возвращает один столбец. Операторы SOME и ANY являются синонимами. Если записи подзапроса предшествует ключевое слово ANY или SOME, то условие считается истинным, если оно выполняется, хотя бы для одного значения из списка значений. В случае ALL условие считается истинным, если оно выполняется для всех записей подзапроса.
Например, выбрать всех клиентов, которые хотя бы раз покупали товар:
SELECT Клиенты.ФИО FROM Клиенты
WHERE Клиенты.НомерКлиента SOME
(SELECT НомерКлиента FROM Сделки);
Следующий пример, выбрать даты сделок с самым большим количеством товара:
SELECT ДатаСделки FROM Сделки WHERE Количество >=ALL
(SELECT Количество FROM Сделки);
Использование EXISTS и NOT EXISTS
Операции EXISTS и NOT EXISTS используются, если подзапрос возвращает несколько строк, т.е. целую таблицу. Для ключевого слова EXISTS условие в WHERE будет истинным, если в возвращаемой подзапросом результирующей таблице присутствует, хотя бы одна строка. По ключевым словам EXISTS и NOT EXISTS проверяется только наличие или отсутствие строк в подзапросе.
Например, выбрать список товаров, которые ни разу не покупались:
SELECT Товары.Название
FROM Товары
WHERE NOT EXISTS
(SELECT * FROM Сделки
WHERE Сделки.НомерТовара= Товары. НомерТовара);
Задание
Пусть даны следующие таблицы:
1. Товары(*НомерТовара, Название, Цена)
2. Клиенты(*НомерКлиента, ФИО, Адрес)
3. Сделки(*Номер Сделки, Номер Клиента, Номер Товара, Количество, Дата Сделки)
Назначения полей очевидны. В таблице «Товары» содержится описание вообще всех товаров. В таблице «Клиенты» содержится информация вообще обо всех клиентах. А в таблице «Сделки» содержится информация только о тех товарах, которые покупались клиентами и только о тех клиентах, которые покупали товар.
Требуется написать SQL-операторы для выполнения следующих запросов:
1. Посчитать общее количество сделок.
2. Посчитать по каждому клиенту количество его сделок и выдать результат в виде:
Номер Клиента, ФИО, Количество сделок.
3. Посчитать по каждому клиенту количество его сделок, на какую сумму и выдать результат в виде: Номер Клиента ФИО Количество сделок На общую Сумму.
4. Посчитать количество сделок и общую сумму сделок по месяцам года. Выдать результат в виде: Номер месяца, Количество сделок, На общую Сумму.
5. Выбрать Фамилия только тех клиентов, которые совершили сделок на сумму больше 5000 рублей.
Контрольные вопросы
1. В каких случаях следует использовать операторы IN или NOT IN в подзапросах? Приведите примеры.
2. В каких случаях следует использовать операторы EXISTS или NOT EXISTS в подзапросах? Приведите примеры.
3. Чем SOME и ANY отличается от ALL при использовании во вложенных запросах? Приведите примеры.
Лабораторная работа № 14
Тема: «Построение запросов на изменение данных».
Цель работы: Научиться создавать запросы на изменение данных.
Время выполнения: подготовка: 5 мин; выполнение: 75 мин; проверка: 10 мин; всего: 90 мин.
Указания к выполнению лабораторной работы
Команда INSERT
Команда INSERT относится к командам манипулирования данными и позволяет добавлять одну или несколько строк к таблице. Команда имеет два варианта синтаксиса. Оба варианта приведены ниже:
INSERT INTO ИмяТаблицы [(ИмяАтрибута, ...)] VALUES (Выражение,...)
или
INSERT INTO ИмяТаблицы [(ИмяАтрибута, ...)] SELECT ...
В случае 1-го варианта синтаксиса команда добавляет одну строку к таблице и заполняет ее значениями, указанными в списке VALUES. При этом список столбцов [(ИмяАтрибута, ...)] должен точно соответствовать списку значений, указанному в круглых скобках после VALUES. В выражении, следующем после VALUES можно использовать любое поле из списка атрибутов (или таблицу, если список имен столбцов не задан).
В случае 2-го варианта синтаксиса команда добавляет к таблице подмножество строк из другой таблицы, определяемое оператором SELECT. Оператор SELECT должен использоваться без ORDER BY.
Список столбцов [( ИмяАтрибута, ...)] должен точно соответствовать списку значений, указанному в VALUES.
В Выражении можно использовать любое поле из списка имен атрибутов (или таблицу, если список имен столбцов не задан).
В случае второго варианта синтаксиса SELECT должен использоваться без ORDER BY.
Задание 1
1. Создать новую запись в таблице Sotr и заполнить поля FIO, N_otd, zarpl значениями
INSERT INTO Sotr (FIO, N_otd, zarpl) VALUES (“Иванов И И”, 1, 15000)
2. Скопирует все данные из таблицы
Otdel2 в таблицу Otdel1.
INSERT INTO Otdel1 SELECT * FROM Otdel2; - при
3. С помощью одной команды добавить в таблицу несколько строк
INSERT INSERT INTO Otdel VALUES (1,”Отдел кадров”), (2,”Плановый отдел”), (3,”Бухгалтерия”);
Команда UPDATE
Команда UPDATE относится к командам манипулирования данными и позволяет обновлять одну или несколько записей в таблице. Команда также имеет многотабличный синтаксис, позволяющий обновлять строки нескольких таблиц.
Синтаксис команды приведен ниже:
UPDATE ИмяТаблицы1 [ ,ИмяТаблицы2 ]
SET ИмяСтолбца1 = { Выражение1|DEFAULT }
[ ,ИмяСтолбца2={ Выражение1|DEFAULT } ] ...
[WHERE Условие]
[ORDER BY ...]
[LIMIT КолвоСтрок];
После оператора UPDATE указывается список таблиц, столбцы которых будут обновлены. Ключевое слово SET задает список столбцов с указанием значений, которые должны быть им назначены. Одной командой можно обновить несколько столбцов.
Условие WHERE задает подмножество строк, которое будет обновлено.
В случае многотабличного синтаксиса, необходимо после оператора UPDATE указать список таблиц, столбцы которых будут обновлены, а в опции WHERE указать условие, которое задает подмножество строк, которые будут обновлены. Все обновления выполняются слева направо.
Задание 2
1. Увеличить зарплату сотрудников 3 отдела в 1,2.
UPDATE Sotr SET Zarpl=Zarpl*1,2 WHERE N_OTD=3;
2. Отдел с номером 3 переименовать в отдел с номером 33.
UPDATE Otdel, Sotr SET Otdel.N_otd=33, Sotr.N_Otd=33
WHERE Otdel. N_otd=3 and Sotr.N_Otd=3;
Команда DELETE
Команда DELETE относится к командам манипулирования данными и позволяет удалять одну или несколько записей как в одной таблице, так и в нескольких. Команда также имеет многотабличный синтаксис.
Синтаксис удаления записей в одной таблице:
DELETE FROM ИмяТаблицы
[WHERE условие]
[ORDER BY ...]
[LIMIT КолСтрок];
Синтаксис удаления записей в нескольких таблицах:
DELETE ИмяТаблицы1[.*] [, ИмяТаблицы2[.*]] ...
FROM СписокТаблиц
[WHERE условие];
Команда удаляет записи, удовлетворяющие условию, заложенному в WHERE. Команда возвращает количество обработанных записей. Если команда DELETE используется без WHERE, то все записи в таблице будут удалены. В этом случае DELETE вернет 0 для числа обработанных записей.
В случае многотабличного синтаксиса после DELETE указывается список таблиц, данные которых должны быть удалены. После FROM указывается список таблиц, которые используются для удаления. В конструкции WHERE необходимо указывать условие соединения таблиц и условие отбора тех записей, которые подлежат удалению.
Задание 3
1. Удалить все строки из таблицы Otdel
DELETE FROM Otdel;
2. Удалить одну строку в таблице Otdel с номером отдела 55 и все строки из таблицы Sotr, которые на нее ссылаются.
DELETE otdel, sotr FROM otdel1, sotr1
WHERE otdel.nomer =sotr.nom AND sotr.nom =55;
Контрольные вопросы
1. Можно ли одним оператором INSERT к одной таблице добавить
2. все строки из другой таблицы? Существуют ли при этом ограничения на структуру таблиц, т.е. таблицы могут иметь произвольную структуру или обязаны иметь структуру?
3. Можно ли при добавлении новой записи в таблицу с помощью оператора INSERT не заполнять новым значением поле AUTO_INCREMENT идентичную. Если да, то какое значение получит это поле?
4. Можно ли при добавлении новой записи в таблицу с помощью
5. оператора INSERT не указывать список имен полей, в которые заносятся значения перед VALUES?
6. В каких случаях требуется явное указание списка полей перед
7. VALUES при добавлении новой записи в таблицу с помощью оператора INSERT?
8. Можно при обновлении записей командой UPDATE обновить
9. сразу несколько полей таблицы?
10. Можно ли с помощью оператора UPDATE обновить записи нескольких
11. таблиц? Приведите пример.
12. Можно ли при удалении записей оператором DELETE удалить записи нескольких таблиц? Приведите пример.
Лабораторная работа № 15
Тема: «Применение запросов в клиентской части приложения».
Цель работы: Научиться создавать запросы в клиентской части приложения.
Время выполнения: подготовка: 5 мин; выполнение: 165 мин; оформление и сдача: 10 мин; всего: 90 мин.
Указания к выполнению лабораторной работы
Задание
В С++ Builder создайте приложение с представленным ниже интерфейсом.
На каждое из событий введите код.
Unit1.h
private: // User declarations
public: TDate Bday; // User declarations
__fastcall TForm1(TComponent* Owner);
Unit1.cpp
void __fastcall TForm1::Button1Click(TObject *Sender)
{ // поиск студента по фамилии
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("SELECT ФИО, Дата_р FROM Студенты WHERE фио= '"+Edit1 -> Text+"';");
ADOQuery1 -> Open();
Label10 -> Caption = "Найденный студент";
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button2Click(TObject *Sender)
{ // Поиск студента по специальности
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("SELECT Студенты.ФИО, Студенты.Дата_Р");
ADOQuery1 -> SQL -> Add ("FROM Специальность INNER JOIN Студенты ON Специальность.код_спец = Студенты.код_спец");
ADOQuery1 -> SQL -> Add ("WHERE Специальность.код_спец= "+Edit2-> Text+"; ");
ADOQuery1 -> Open();
AnsiString S= "Студенты Специальности " +Edit2 -> Text;
Label10 -> Caption = S;
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button3Click(TObject *Sender)
{ // Удалить студента по ФИО
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("DELETE FROM Студенты WHERE ФИО = '"+Edit3 -> Text+ "';");
ADOQuery1 -> ExecSQL();
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("SELECT * FROM Студенты;");
ADOQuery1 -> Open();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button4Click(TObject *Sender)
{ //Вставить нового сутдента
Bday=StrToDate(Edit6 -> Text);
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("INSERT INTO Студенты([№ группы], ФИО, Дата_Р, код_спец)");
ADOQuery1 -> SQL -> Add ("Values('"+Edit4 -> Text+"', '"+Edit5 -> Text+"', DateValue('"+FormatDateTime("dd/mm/yyyy",Bday)+"'),"+Edit7 -> Text+");");
ADOQuery1 -> ExecSQL();
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("SELECT * FROM Студенты");
ADOQuery1 -> Open();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button5Click(TObject *Sender)
{ // Изменить данные специальности студента
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("UPDATE Студенты SET код_спец= '"+Edit9 -> Text+"' WHERE ФИО= '"+Edit8 -> Text+"';");
ADOQuery1 -> ExecSQL();
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("SELECT * FROM Студенты WHERE ФИО= '"+Edit8 -> Text+"';");
ADOQuery1 -> Open();
Label10 -> Caption = "Переведенный студент";
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button6Click(TObject *Sender)
{ // Вставка специальности с параметром
ADOQuery1 -> Close();
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("INSERT INTO Специальность (код_спец, название) Values (:К_с,:Naz);");
ADOQuery1 -> Parameters -> ParamByName("К_с") -> Value = Edit10 -> Text;
ADOQuery1 -> Parameters -> ParamByName ("Naz") -> Value = Edit11 -> Text;
ADOQuery1 -> ExecSQL();
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("SELECT * FROM Специальность;");
ADOQuery1 -> Open();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button7Click(TObject *Sender)
{ // поиск студента по ФИО с параметром
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("SELECT ФИО FROM Студенты WHERE ФИО = :fio");
ADOQuery1 -> Parameters -> ParamByName ("fio") -> Value = Edit12 -> Text;
ADOQuery1 -> Open();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button8Click(TObject *Sender)
{ // Поиск студента по специальности с параметром
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("SELECT Студенты.ФИО, Студенты.Дата_Р");
ADOQuery1 -> SQL -> Add ("FROM Специальность INNER JOIN Студенты ON Специальность.код_спец = Студенты.код_спец");
ADOQuery1 -> SQL -> Add ("WHERE Специальность.код_спец = :c_spec");
ADOQuery1 -> Parameters -> ParamByName ("c_spec") -> Value = Edit13 -> Text;
ADOQuery1 -> Open();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button9Click(TObject *Sender)
{ // Удалить студента по ФИО с параметром
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("DELETE FROM Студенты WHERE ФИО = :FIO");
ADOQuery1 -> Parameters -> ParamByName ("FIO") -> Value = Edit14 -> Text;
ADOQuery1 -> ExecSQL();
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("SELECT * FROM Студенты;");
ADOQuery1 -> Open();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button10Click(TObject *Sender)
{ // Изменить данные специальности студента с параметром
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("UPDATE Студенты SET код_спец= :cod_s WHERE ФИО= :fIo;");
ADOQuery1 -> Parameters -> ParamByName ("cod_s") -> Value = Edit16 -> Text;
ADOQuery1 -> Parameters -> ParamByName ("fIo") -> Value = Edit15 -> Text;
ADOQuery1 -> ExecSQL();
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("SELECT * FROM Студенты WHERE ФИО= :fIo;");
ADOQuery1 -> Parameters -> ParamByName ("fIo") -> Value = Edit15 -> Text;
ADOQuery1 -> Open();
Label10 -> Caption = "Переведенный студент";
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button12Click(TObject *Sender)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button11Click(TObject *Sender)
{ //Вставить нового сутдента с параметрами
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("INSERT INTO Студенты([№ группы], ФИО, Дата_Р, код_спец)");
ADOQuery1 -> SQL -> Add ("Values(:gr, :Fam, :data,:cod_specal)");
ADOQuery1 -> Parameters -> ParamByName ("gr") -> Value = Edit17 -> Text;
ADOQuery1 -> Parameters -> ParamByName ("Fam") -> Value = Edit18 -> Text;
ADOQuery1 -> Parameters -> ParamByName ("data") -> Value = StrToDate (Edit19 -> Text);
ADOQuery1 -> Parameters -> ParamByName ("cod_specal") -> Value = Edit20 -> Text;
ADOQuery1 -> ExecSQL();
ADOQuery1 -> Active = false;
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("SELECT * FROM Студенты");
ADOQuery1 -> Open();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::ComboBox1Change(TObject *Sender)
// формирование ComboBox Change
{
ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("SELECT * FROM Студенты WHERE [№ группы] = :P_g");
ADOQuery1-> Parameters-> ParamByName("P_g") -> Value = ComboBox1 -> Items -> Strings[ComboBox1 -> ItemIndex];
ADOQuery1 -> Open();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::FormCreate(TObject *Sender)
// формирование ComboBox
{ ADOQuery1 -> SQL -> Clear();
ADOQuery1 -> SQL -> Add ("SELECT * FROM Студенты");
ADOQuery1 -> Open();
while (!ADOQuery1->Eof)
{
ComboBox1 -> Items ->Add (ADOQuery1-> FieldByName("№ группы") -> AsString);
ADOQuery1 -> Next();
}
}
//---------------------------------------------------------------------------
void __fastcall TForm1::DBGrid1DrawColumnCell(TObject *Sender, const TRect &Rect,
int DataCol, TColumn *Column, TGridDrawState State)
//полоска выделения строки таблицы красным цветом
{
// в св-ве DBGrid Установить dbRowSelected = true (Properties -> options -> dbRowSelected = true)
if (State.Contains(Grids::gdSelected))
{
DBGrid1->Canvas->Brush->Color=clRed;
DBGrid1->DefaultDrawColumnCell (Rect, DataCol, Column, State);
}
}
Задание 2
1. Создайте клиентское приложение для своего варианта базы данных.
2. Создайте необходимые запросы к базе данных.
Контрольные вопросы
1. Какой метод компонента ADOQuery запускает на выполнение оператор SELECT?
2. Какой метод компонента ADOQuery запускает на выполнение операторы манипулирования данными?
3. Для чего необходимо очистить свойство SQL компонента ADOQuery?
4. Как создать запрос с параметром?
5. Как заполнить компонент ComboBox данными из таблицы?
Лабораторная работа № 16
Тема: «Создание представлений»
Цель работы: Научиться создавать запросы в клиентском приложении.
Время выполнения: подготовка: 3 мин; выполнение: 67 мин; оформление и сдача: 10 мин; всего: 90 мин.
Указания к выполнению лабораторной работы
Представление (просмотр) это объект базы данных, SELECT - запрос, который сохраняется в базе данных. Представление позволяет ограничить доступ пользователя к подмножеству строк и столбцов одной или нескольких таблиц. С представлениями
работают 3 команды SQL:
CREATE VIEW – создать представление.
ALTER VIEW - изменить представление.
DROP VIEW - удалить представление.
CREATE [OR REPLACE] VIEW ИмяПредставления [(СписокИменСтолбцов)]
AS select-предложение [WITH CHECK OPTION]
Команда CREATE VIEW создает новое представление или замещает существующее,
если используется OR REPLACE. Если представление уже существует, то команда CREATE OR REPLACE VIEW это то же самое, что и ALTER VIEW.
СписокИменСтолбцов – это имена столбцов в представлении.
select-предложение – задает оператор SELECT, который определяет тело представления. Выборка может выполняться как из таблиц, так и из других представлений.
Ключевые слова WITH CHECK OPTION не позволяют вставлять или изменять через представление строки базовой таблицы, которые не удовлетворяют уточнителю WHERE в select-предложении.
Для создания представления необходимо иметь привилегию CREATE VIEW.
Например, создадим представление VSotr3 для таблицы Sotr(Tab_nom, Fio, Nom_Otd), которое позволяет видеть только сотрудников 3 отдела:
mysql> CREATE VIEW VSotr3 AS SELECT * FROM Sotr
WHERE Nom_Otd=3;
С помощью команды SHOW TABLES; - убедимся с том, что представление создано.
С помощью команды SELECT * FROM VSotr3; убедимся в том, представление позволяет видеть только сотрудников 3 отдела.
Создадим еще одно представление для этой же таблицы, но с опцией WITH CHECK OPTION.
mysql> CREATE VIEW VSotr3Ch AS SELECT * FROM Sotr
WHERE Nom_otd=3 WITH CHECK OPTION;
Некоторые представления являются модифицирующими (типа updatable), т.е. позволяют с помощью операторов UPDATE, DELETE, или INSERT изменять данные базовой таблицы. Для того, чтобы представление было представлением типа updatable необходимо, чтобы существовали связи один-к-одному между строками представления и базовой таблицы. Существуют также и другие ограничения.
Представление является представлением типа updatable, т.е. позволяющим изменять строки таблицы, на которой оно основано, если оно удовлетворяет следующим условиям:
• Является подмножеством единственной таблицы или другого представления типа updatable
• Оператор select, формирующий тело представления не содержит агрегатные функции (SUM(), MIN(), MAX(), COUNT() и т.д.), DISTINCT, GROUP BY, HAVING, UNION или UNION ALL, подзапросы, не updatable-представления.
• Столбцы, не входящие с представление должны допускать ввод NULL значений.
• В противном случае представление является представлением типа read only, т.е. не позволяющим изменять строки базовой таблицы.
Оба представления VSotr3Ch и VSotr3, созданные нами, являются updatable-представлениями. Единственная разница между ними заключается в том, что представление VSotr3 позволяет вводить строки, не удовлетворяющие опции WHERE
оператора SELECT, а представление VSotr3Ch не позволяет этого делать. Проверим это. Исходные данные таблицы Sotr приведены в таблице:
| T_nom | fio | nom_otd |
| Sid | ||
| Tom |
С помощью команды INSERT добавим новую строку в таблицу, используя представление VSotr3:
mysql> INSERT INTO VSotr3 VALUES (105,”Min”,5);
С помощью команды SELECT убедимся в том, что строка действительно добавлена в таблицу.
mysql> SELECT * FROM Sotr;
Результат работы оператора SELECT * FROM Sort приведен в таблице:
| T_nom | fio | nom_otd |
| Sid | ||
| Tom | ||
| Mih |
Как видим, строка в базовую таблицу добавлена через представление VSotr3.
При добавлении строки, не удовлетворяющей опции WHERE через представление VSotr3Ch, будет выдано сообщение об ошибке.
mysql> insert into VSotr3Ch values (106, «Dan», 2);
ERROR 1369 (HY000): CHECK OPTION failed 'proba.vsotr3ch'
Команда ALTER VIEW синтаксически подобна команде CREATE VIEW и работает также.
Для удаления представлений используется команда DROP VIEW, в которой задается имя представления. Например:
DROP VIEW VSotr3Ch;
Структуру конкретного представления можно просмотреть с помощью команды:
SHOW CREATE VIEW <ИмяПредставления>\G;
Задание 1. Создать представление VSotr3 для таблицы Sotr(Tab_nom, Fio, Nom_Otd), которое позволяет видеть только сотрудников 3 отдела:
CREATE VIEW VSotr3 AS SELECT * FROM Sotr WHERE Nom_Otd=3;
Задание 2. С помощью команды SHOW TABLES; - убедитесь в том, что представление создано.
Задание 3. Создайте представление для этой же таблицы, но с опцией WITH CHECK OPTION.
CREATE VIEW VSotr3Ch AS SELECT * FROM Sotr
WHERE Nom_otd=3 WITH CHECK OPTION;
Задание 4.Создать представления для своего варианта базы данных.
Контрольные вопросы
1. Для чего нужны представления?
2. Как создать представление? Пример.
3. Какие команды языка SQL работают с представлениями
4. Можно ли используя представление изменять строки базовой таблицы, т.е. таблицы, на основе которой создано представление?
5. Какие команды позволяют просмотреть созданные в базе данных представления?
Практическая работа № 17
Тема: «Создание хранимых процедур и функций»
Цель работы: Научиться создавать запросы в клиентском приложении.
Время выполнения: подготовка: 5 мин; выполнение: 120 мин; проверка: 10 мин; всего: 135 мин.
Указания к выполнению лабораторной работы
Хранимые подпрограммы это программный код, который хранится в базе данных вместе с данными. Хранимые подпрограммы могут быть либо процедурами, либо функциями.
Преимущества использования хранимых подпрограмм:
Производительность.Хранимые подпрограммы хранятся в откомпилированном,
т.е. готовом к выполнению виде, поэтому выполняются быстрее по сравнению с посылкой отдельных SQL-операторов на сервер.
Разгрузка сетипо сравнению с посылкой отдельных SQL-операторов на сервер, т.к. для вызова хранимой подпрограммы по сети отправляется только оператор вызова возможно с
параметрами, а выполнение всех операторов, входящих в подпрограмму выполняется на сервере.
Централизованное администрирование. Хранимые подпрограммы могут разделяться несколькими приложениями, они хранятся на сервере и поэтому изменение кода хранимой
подпрограммы отразится сразу на всех приложениях, которые ее используют.
Хранимые процедуры создаются операторами CREATE PROCEDUREи CREATE FUNCTION.Процедура вызывается оператором CALLи может возвращать несколько значений, используя выходные переменные. Функция может участвовать в выражениях и возвращает скалярное значение. Хранимые подпрограммы могут вызывать другие хранимые подпрограммы.
С помощью оператора CREATE PROCEDURE создается процедура, с помощью оператора CREATE FUNCTION создается функция. Синтаксис хранимых подпрограмм приведен ниже:
CREATE
PROCEDURE Имя ([ПараметрПр[,...]])
ТелоПР
CREATE FUNCTION Имя ([ПараметрФ[,...]])
RETURNS тип
ТелоФ
ПараметрПР:
[ IN | OUT | INOUT ] Имя Тип
ПараметрФ
Имя Тип
Тип – любой правильный тип MySQL.
ТелоФ: ТелоПр:
Begin Begin
… …
End; End;
С помощью операторов CREATE PROCEDURE и CREATE FUNCTION выполняется синтаксический контроль, и определение подпрограммы записывается в базу данных.
Для создания хранимых подпрограмм необходимо наличие привилегий CREATE ROUTINE. По умолчанию MySQL автоматически предоставляет привилегии ALTER ROUTINE и EXECUTE для создателя хранимых подпрограмм.
Параметры подпрограмм заключаются в круглые скобки. Если параметров нет, то круглые скобки не требуются. Каждый параметр описывается со своим типом. По умолчанию каждый параметр есть IN параметр. Для описания других типов параметров нужно
явно указать их вид: OUT или INOUT перед именем параметра.
Виды параметров OUT, INOUT используются только для процедур.
Функции используют только параметры IN.
IN-параметр передает значение в процедуру(функцию). В теле подпрограммы он может модифицироваться, но извне эти изменения не видны.
OUT-параметр – значение этого параметра возвращается процедурой. Его начальное значение равно NULL.
INOUT-параметр - имеет входное значение, может модифицироваться процедурой, возвращается процедурой по ее окончании.
Параметры OUT и INOUT возвращаются процедурой при ее завершении.
Оператор RETURNS определен только для функций.
Тело подпрограмм должно содержать правильные SQL –операторы. Это могут быть простые операторы, такие как SELECTили INSERT,или составной оператор в операторных скобках BEGIN и END.Составные операторы могут включать описания, циклы и другие управляющие конструкции. Некоторые операторы не разрешены в хранимых подпрограммах.
Запись подпрограммы в базу данных выполняется с помощью консольного приложения mysql.exe. Для примера, запишем простую процедуру simpleproc, использующую выходной параметр param1 в базу данных. Для замены разделителя операторов используется оператор DELIMITER, который изменяет разделитель точку с запятой на символы - //.
Оператор DELIMITER // говорит о том, что признаком завершения оператора является не
точка с запятой(;), а символы //. Поэтому код описания процедуры завершается символами //.
Для восстановления символа окончания операторов используется оператор:
DELIMITER ;
Код процедуры, так как он записывается в базу данных, приведен ниже:
mysql> DELIMITER //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
Выполним вызов процедуры с помощью оператора CALL:
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
С помощью оператора SELECT убеждаемся в том, что выходной параметр param1 был изменен в теле процедуры:
mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
Пример использования функции. Создадим функцию hello, которой в качестве параметра передается имя s. Функция возвращает строку:
mysql> DELIMITER //
mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50)
-> RETURN CONCAT(‘Hello, ‘,s,’!’);
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
Вызов функции выполняется с помощью оператора SELECT:
mysql> SELECT hello(‘world’);
+----------------+
| hello(‘world’) |
+----------------+
| Hello, world! |
+----------------+
Вызов процедуры выполняется с помощью оператора CALL:
CALL ИмяПроцедуры([Параметр[,...]])
CALL ИмяПроцедуры[()]
Оператор CALL возвращает параметры, переданные как OUTили INOUT.Если процедура не имеет параметров, она может вызываться так:
CALL p() или CALL p
Например, следующая процедура имеет OUTпараметр, который возвращает текущую версию сервера и INOUTпараметр, который увеличивается на единицу в теле процедуры:
CREATE PROCEDURE p (OUT ver_param VARCHAR(25),
INOUT incr_param INT)
BEGIN
# Установить значение OUT параметра
SELECT VERSION() INTO ver_param;
# Увеличить значение INOUT параметра
SET incr_param = incr_param + 1;
END;
Все, что располагается правее символа #, рассматривается как комментарий.
Перед вызовом процедуры нужно инициализировать INOUTпараметр. После вызова процедуры значения обоих параметров будут изменены.
mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);
mysql> SELECT @version, @increment;
+------------+---------------+
| @version | @increment |
+------------+---------------+
| 5.1 | 11 |
+-----------+---------------+
Вызов функции
Так как функция возвращает значение, ее можно использовать в выражениях. Например, создадим функцию с именем my_version, которая возвращает символьную строку – текущую версию сервера MySQL. Текст функции приведен ниже:
delimiter //
CREATE FUNCTION my_version()
RETURNS CHAR(20)
RETURN Version();
//
delimiter ;
Вызов функции выполняется двумя способами:
1) SELECT my_version();
2) mysql> SET @r=my_version();
mysql> SELECT @r;
Язык хранимых процедур смотрите в лекционных материалах.
Задание 1
Валюта покупается «Обменным пунктом валюты» в обмен на рубли. Будем считать, что код рубля в таблице «Валюта» равен 1.
Даны следующие таблицы:
1. Таблица Валюта(Valuta), которая хранит информацию о валютных остатках в обменном пункте, и имеет следующую структуру:
Nval - Номер валюты, первичный ключ,
Nazv - Название валюты,
Ost - текущий остаток валюты в обменном пункте.
2. Таблица Покупка(Pokupka) хранит информацию о покупке валюты обменным пунктом и имеет следующую структуру:
N_pok - Номер покупки, первичный ключ,
Data_pok - Дата покупки,
N_val - Номер валюты, внешний ключ, ссылающийся
на первичный ключ таблицы Валюта.
Kurs - Курс,
Kol – Количество купленной валюты.
Требуется разработать процедуру «Покупка валюты» обменным пунктом. Входные параметры: Номер валюты, дата покупки, курс покупки, количество покупаемой валюты. Выходной параметр: результат выполнения процедуры, т.е. выполнена или не выполнена процедура.
В теле процедуры выполняются следующие действия:
1. если остаток 1. в рублях недостаточен для выполнения процедуры, то выход из процедуры, при этом выходной параметр сигнализирует о том, что процедура не выполнена;
2. изменяются остатки: остаток в рублях уменьшается, остаток покупаемой валюты увеличивается;
3. операция записывается в таблицу «Покупка валюты».
4. Выходной параметр сигнализирует о том, что процедура успешно выполнена.
Задание 2
Даны таблицы
1. Абоненты (*номер телефона, ФИО, Адрес) - хранит всю информацию об абонентах телефонной сети. Поле «Номер телефона» – первичный ключ.
2. Тариф (код города, Название города, цена за минуту) – хранит информацию о тарифах, где поле «код города» – первичный ключ.
3. Разговоры(*номер разговора, номер телефона, дата, код города, время) – хранит информацию о разговорах абонентов, где поле «номер разговора» - первичный ключ, а поле «номер телефона» - внешний ключ, который ссылается на первичный ключ таблицы «Абоненты», поле «код города» - внешний ключ, который ссылается на первичный ключ таблицы Тариф.
Разработать процедуру или функцию, которая по каждому абоненту (номеру телефона) выдает информацию о разговорах этого абонента за период времени с даты 1 по дату 2, например, в следующем виде: Код города, Название города, Дата, Время, Цена за мин, Сумма.
Контрольные вопросы
1. В чем преимущество использования хранимых подпрограмм?
2. Чем хранимая процедура отличается от хранимой функции?
3. В чем отличие IN, OUT и INOUT параметров подпрограмм?
4. Какие параметры IN, OUT или INOUT можно использовать в функциях?
5. Какие операторы языка SQL можно использовать в теле подпрограмм?
6. Можно ли в теле подпрограмм использовать ветвления, циклические конструкции?
7. Как подпрограмму записать в базу данных? Для чего используется оператор DELIMITER?
8. Как вызывается процедура? Как вызывается функция?
9. Можно ли в теле подпрограмм использовать переменные?
10. Требуют ли они предварительного описания? Если да, то как это сделать?
11. Как переменной назначить значение?
12. Для чего используется оператор CASE в теле подпрограммы?
13. Для чего используются операторы LEAVE и ITERATE в теле подпрограммы?
14. Как просмотреть созданные процедуры и функции?
15. Что такое курсор, для чего он используется? Порядок работы с курсором.
16. Приведите пример хранимой процедуры, функции.
Практическая работа № 18
Тема: «Создание триггеров»
Цель работы: Научиться создавать триггеры.
Время выполнения: подготовка: 5 мин; выполнение: 120 мин; проверка: 10 мин; всего: 135 мин.
Указания к выполнению лабораторной работы
Триггер — это хранимая процедура, которая не вызывается непосредственно, а исполняется при наступлении определенного события ( вставка, удаление, обновление строки ).
Триггеры это особые процедуры которые срабатывают при изменении данных в таблицы операторами INSERT, UPDATE и DELETE.
Триггеры могут срабатывать как до BEFORE так и после AFTER изменения таблицы. Получается, что триггер может быть в 6 состояниях.
1 INSERT (BEFORE | AFTER)
2 UPDATE (BEFORE | AFTER)
3 DELETE (BEFORE | AFTER)
Добавление триггера
Задание. Необходимо при добавлении записи в табл user, пароль преобразовывать в хеш md5(), также имя и отчество преобразовывать в инициалы.
mysql> DELIMITER //
mysql> CREATE TRIGGER `test_user_pass` BEFORE INSERT ON `test`.`user`
-> FOR EACH ROW
-> BEGIN
-> SET NEW.name = LEFT(NEW.name,1);
-> SET NEW.otch = LEFT(NEW.otch,1);
-> SET NEW.pass = md5(NEW.pass);
-> END//
mysql> DELIMITER;
Теперь вставляем туда запись
mysql> INSERT INTO `user` SET `fam`='Нагайченко', `name`='Максим', `otch` = 'Валерьевич', `pass` = 'password', `login` = 'maxnag';
mysql> SELECT * FROM `user`;
| id | fam | name | otch | pass | login |
| 1 | Нагайченко | M | В | 5f4dcc3b5aa765d61d8327deb882cf99 | maxnag |
Создать триггер на UPDATE таблицы, с таким же телом, чтобы пользователь не смог записать полное имя, отчество и пароль не в МД5();