### Лабораторная работа 1. Язык MySQL

1 Теоретический материал
1.0. Структура учебной базы данных
1.1. Запрос данных
1.2. Использование реляционных, булевых и специальных операторов
1.3. Агрегатные функции
1.4. Вывод данных из нескольких таблиц. Объединение таблиц с собой
1.5. Вложенные запросы
1.6. Создание, изменение и удаление таблиц
1.7. Изменение данных в таблицах
2 Конторольные вопросы
3 Задание

Структурированный язык запросов (Structured Query Language) SQL - это инструмент, предназначенный для обработки и чтения данных, содержащихся в компьютерных базах данных. Одна из функций SQL - организация связи через локальную сеть с сервером базы данных, в в которой хранятся совместно используемые данные. Операторы SQL напоминают английские предложения, что значительно облегчает их понимание и изучение.

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

### 1 Теоретический материал

### 1.0. Структура учебной базы данных

1. После устанвки пакета Denwer создаем приведенную ниже базу данных - непосредственно с помощью phpMyAdmin или используя файл ins.php, который расположен в директории work полученного Вами задания.

Таблица sal - продавцы
snum - номер продавца
sname - имя продавца
city - город, где находится продавец
comm - комиссионные продавца

> snum | sname | city | comm
> --- | --- | --- | ---
> 1001 | Peel | London | 0.12
> 1002 | Serres | San Jose | 0.13
> 1004 | Motica | London | 0.11
> 1007 | Rifkin | Barcelona | 0.15
> 1003 | Axelrod | New York | 0.10

Таблица cust - покупатели
cnum - номер покупателя
cname - имя покупателя
city - город, где находится покупатель
rating - рейтинг покупателя
snum - номер продавца, у которого покупатель сделал покупку

> cnum | cname | city | rating | snum
> --- | --- | --- | --- | ---
> 2001 | Hoffman | London | 100 | 1001
> 2002 | Giovanni | Rome | 200 | 1003
> 2003 | Liu | San Jose | 200 | 1002
> 2004 | Grass | Berlin | 300 | 1002
> 2006 | Clemens | London | 100 | 1001
> 2008 | Cisneros | San Jose | 300 | 1007
> 2007 | Pereira | Rome | 100 | 1004

Таблица ord - заказы
onum - номер заказа
amt - сумма заказа
odate - дата заказа
cnum - номер покупателя, который сделал заказ
snum - номер продавца, которому сделан заказ

> onum | amt | odate | cnum | snum
> --- | --- | --- | --- | ---
> 3001 | 18.69 | 03-OCT-90 | 2008 | 1007
> 3003 | 767.19 | 03-OCT-90 | 2001 | 1001
> 3002 | 1900.10 | 03-OCT-90 | 2007 | 1004
> 3005 | 5160.45 | 03-OCT-90 | 2003 | 1002
> 3006 | 1098.16 | 03-OCT-90 | 2008 | 1007
> 3009 | 1713.23 | 04-OCT-90 | 2002 | 1003
> 3007 | 75.75 | 04-OCT-90 | 2004 | 1002
> 3008 | 4723.00 | 05-OCT-90 | 2006 | 1001
> 3010 | 1309.95 | 06-OCT-90 | 2004 | 1002
> 3011 | 9891.88 | 06-OCT-90 | 2006 | 1001

### 1.1. Запрос данных

Запрос данных в языке SQL осуществляется командой SELECT. Для выбора всей информации из таблицы используется наиболее простая форма команды:

SELECT * FROM <имя_объекта>;

где <имя_объекта> вообще говоря, имеет вид <имя_базы_данных.имя_таблицы>

Например:

SELECT * FROM sample.sal;

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

Например:

SELECT * FROM sal;

УПРАЖНЕНИЕ 1.1.

Просмотрите содержимое таблиц учебной базы данных (SAL,

CUST, ORD).

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

SELECT * FROM cust;

SELECT city, cname FROM cust;

Для того, чтобы вывести данные из столбца БЕЗ повторяющихся значений, используется параметр DISTINCT. Сравните результаты запросов:

SELECT city FROM sal;

SELECT DISTINCT city FROM sal;

Выбор требуемых строк осуществляется применением фразы WHERE <условие> в команде SELECT. Следующая команда выводит записи только о тех покупателях, которые размещаются в городе San Jose (Сан Хосе):

SELECT * FROM cust

WHERE city = 'San Jose';

Для указания конкретных столбцов в команде SELECT необходимо знать их точное название. Команда DESCRIBE (или сокращенно DESC) позволяет получить имена столбцов и другую информацию о структуре таблиц. Например, следующая команда выводит структуру таблицы Заказов:

DESC ord;

УПРАЖНЕНИЕ 1.2.

Запросите информацию о структурах всех известных вам

объектов базы SAMPLE.

### 1.2. Использование реляционных, булевых и специальных операторов

Реляционные операторы, которыми располагает SQL:

= - равно

> - больше, чем

< - меньше, чем

>= - больше или равно

<= - меньше или равно

<>, != - не равно

Cледующая команда выводит сведения о покупателях, чей рейтинг отличен от 200:

SELECT * FROM cust WHERE rating <> 200;

Cтандартными булевыми операторами, распознаваемыми SQL, являются:

AND - логическое И

OR - логическое ИЛИ

NOT - логическое ОТРИЦАНИЕ

В следующем примере показано использование реляционных и булевых операторов при указании условия выборки данных. Команда SELECT выбирает всех заказчиков, находящихся в San Jose и имеющих рейтинг выше 200:

SELECT * FROM cust

WHERE city = 'San Jose' AND rating > 200;

УПРАЖНЕНИЕ 2.1.

Выполните два следующих запроса и объясните результаты:

SELECT * FROM cust

WHERE NOT city = 'San Jose' OR rating > 200;

SELECT * FROM cust

WHERE NOT (city = 'San Jose' OR rating > 200);

Оператор IN определяет набор значений, в который данное значение может быть включено. В следующем примере запрашиваются все продавцы, размещенные в городах Barcelona или London:

SELECT * FROM sal

WHERE city IN ('Barcelona', 'London');

УПРАЖНЕНИЕ 2.2.

Как еще можно выполнить тот же запрос?

Оператор BETWEEN похож на оператор IN, но BETWEEN задает диапазон значений. После ключевого слова BETWEEN вводится начальное значение диапазона, затем ключевое слово AND и конечное значение. Следующая команда будет извлекать всех продавцов с комиссионными от 0.10 до 0.12:

SELECT * FROM sal WHERE comm BETWEEN .10 AND .12;

УПРАЖНЕНИЕ 2.3.

Повторите последний запрос, но поменяйте местами границы

диапазона.

Оператор LIKE используется, чтобы находить подстроки в указанном поле таблицы. Этот оператор применим только к полям типа CHAR или VARCHAR. В искомой строке могут использоваться групповые символы (wildkards):

- символ подчеркивания (_) замещает любой одиночный символ,

- знак процента (%) группу из любого числа символов.

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

SELECT * FROM cust WHERE cname LIKE '_i%';

УПРАЖНЕНИЕ 2.4.

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

буквой s.

### 1.3. Агрегатные функции

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

SUM - арифметическая сумма всех значений столбца

AVG - среднее значение всех значений столбца

MAX - наибольшее значение из всех значений столбца

MIN - наименьшее значение из всех значений столбца

Для групповых функций применяется следующий общий синтаксис:

<функция> ([ DISTINCT | ALL ] <выражение>)

Агрегатные функции используются подобно именам полей в команде SELECT, при этом они используют имена полей (столбцов) как аргументы. Функции SUM и AVG могут применяться только к числовым полям. С MAX и MIN могут использоваться как числовые, так и символьные поля.

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

SELECT SUM (amt) FROM ord;

УПРАЖНЕНИЕ 3.1.

Сравните и объясните результаты двух следующих запросов:

SELECT MAX(city) FROM sal;

SELECT MIN(city) FROM sal;

Агрегатные функции могут применяться к группам строк при использовании опции GROUP BY. При этом автоматически выполняется разбивка на группы значений в тех столбцах, которые указаны как аргументы в опции GROUP BY. Например, требуется найти наиболее крупный заказ для каждого продавца. Можно сделать раздельный запрос для каждого из них, выбрав MAX(amt) из таблицы Заказов для каждого значения поля snum. Однако GROUP BY позволяет это сделать одной командой:

SELECT snum, MAX(amt) FROM ord

GROUP BY snum;

УПРАЖНЕНИЕ 3.2.

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

разницу:

SELECT snum, odate, MAX(amt) FROM ord

GROUP BY snum, odate;

Таблицы - это неупорядоченные наборы строк. Для того чтобы выводить данные в желаемой последовательности, SQL использует фразу ORDER BY с опциями ASC и DESC. ASC определяет возрастающий порядок значений поля, а DESC - убывающий. Поля во фразе ORDER BY могут быть указаны по именам или по номерам. Номер указывает позицию поля в команде SELECT, а не в таблице. Упорядочивать можно не только поля, но и группы, определенные фразой GROUP BY. Следующая команда выводит данные о заказах, причем номера покупателей указаны в убывающем порядке:

SELECT amt, odate, cnum FROM ord

ORDER BY cnum DESC;

УПРАЖНЕНИЕ 3.3.

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

и команды, указанной ниже:

SELECT amt, odate, cnum FROM ord

ORDER BY cnum DESC, 1 DESC;

### 1.4. Вывод данных из нескольких таблиц. Объединение таблиц с собой

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

SELECT cname, sname, sal.city FROM sal, cust

WHERE sal.city = cust.city;

УПРАЖНЕНИЕ 4.1.

Измените предыдущую команду так, чтобы из перечня городов

был исключен Лондон, и сравните результаты запросов.

Объединение таблиц с собой может быть представлено как объединение двух копий одной и той же таблицы. Таблицы на самом деле не копируются, но SQL выполняет команды так, как если бы это было сделано.

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

В следующем примере выбираются все пары покупателей, имеющих один и тот же рейтинг, причем SQL ведет себя так, как если бы он соединял две таблицы - first и second:

SELECT first.cname, second.cname, first.rating

FROM cust first, cust second

WHERE first.rating = second.rating;

УПРАЖНЕНИЕ 4.2.

Сравните и объясните результаты выполнения предыдущей

команды и команды, приведенной ниже:

SELECT first.cname, second.cname, first.rating

FROM cust first, cust second

WHERE first.rating = second.rating

AND first.cname < second.cname;

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

Например, следующий запрос находит всех покупателей, живущих в городах, в которых продавец Motica (с номером 1004) имеет своих покупателей:

SELECT b.cnum, b.cname

FROM cust a, cust b

WHERE a.snum = 1004

AND b.city = a.city;

УПРАЖНЕНИЕ 4.3.

Выполните такой же запрос для продавца Serres и поясните

результат.

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

SELECT sname, sal.snum, a.cname, b.cname

FROM cust a, cust b, sal

WHERE a.snum = b.snum

AND sal.snum = a.snum;

Если же в предыдущем запросе добавить

AND a.cname <> b.cname;

то можно исключить избыточные строки (т.е. пары покупателей сами с собой).

### 1.5. Вложенные запросы

Язык SQL позволяет вкладывать один запрос внутрь другого. Чтобы выполнить внешний (основной) запрос, SQL сначала должен выполнить внутренний запрос (подзапрос). Предположим, что требуется извлечь все заказы из таблицы Заказов для продавца Motica. Если мы знаем его номер (1004), то это можно сделать с помощью следующего запроса:

SELECT * FROM ord WHERE snum = 1004;

Допустим, что номер продавца Motica нам неизвестен, тогда можно

использовать подзапрос для решения той же задачи:

SELECT * FROM ord WHERE snum =

(SELECT snum FROM sal WHERE sname = 'Motica');

УПРАЖНЕНИЕ 5.1.

Получите с помощью подзапроса все заказы покупателя Grass.

В предыдущем примере использован подзапрос, который возвращает единственное значение (одну строку). Для того, чтобы воспользоваться подзапросом, возвращающим больше одного значения, применяют оператор IN. Следующая команда выбирает все заказы для продавцов из Лондона.

SELECT * FROM ord WHERE snum IN

(SELECT snum FROM sal WHERE city = 'London');

УПРАЖНЕНИЕ 5.2.

Выполните упражнение 5.1 с использованием оператора IN.

### 1.6. Создание, изменение и удаление таблиц

Команды создания, изменения и удаления таблиц относятся к группе команд SQL, называемой языком определения данных - DDL (Data Definition Language). Таблицы создаются командой CREATE TABLE. В простейшем случае команда определяет имя таблицы, имена столбцов, а также типы и размеры данных для столбцов. В примере показано создание таблицы MYTAB, включающей три столбца:

CREATE TABLE mytab

(col1 INT(3) NOT NULL,

col2 VARCHAR (10) NOT NULL,

col3 DATE);

Обратите внимание, что команды создания, изменения и удаления таблиц (равно как и команды изменений данных в таблицах) не приводят к выводу данных на экран.

Для удаления таблицы из базы данных можно использовать команду DROP TABLE.

УПРАЖНЕНИЕ 6.1.

Удалите таблицу MYTAB и создайте другую, с именем TAB1 и

с той же структурой. Убедитесь, что в словаре базы данных

указана новая таблица. Выведите ее структуру.

Команда CREATE TABLE создает пустую таблицу. Для наполнения таблицы данными их можно загружать в таблицу при ее создании. Для этого в команде CREATE TABLE используют фразу AS SELECT..., которая позволяет копировать данные из другой, уже существующей таблицы. В этом случае структура создаваемой таблицы будет идентична (полностью или частично) структуре этой другой таблицы.

В следующем примере показано, как при создании таблицы TAB2 в нее загружаются все данные из таблицы SАL:

CREATE TABLE tab2

AS SELECT * FROM sal;

УПРАЖНЕНИЕ 6.2.

Создайте новую таблицу и скопируйте в нее первые два столбца

из таблицы SAL. Выведите структуру и содержимое вновь

созданной таблицы.

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

таблицы. В первом примере добавляется столбец к таблице ТАВ1, во втором - увеличивается размер столбца CITY таблицы ТАВ2:

ALTER TABLE tab1 ADD colnew CHAR(10);

ALTER TABLE tab2 MODIFY city VARCHAR(20);

УПРАЖНЕНИЕ 6.3.

Модифицируйте таблицу, которую вы создали в упражнении 6.2

следующим образом: увеличьте размер второго столбца, добавьте

два новых столбца к таблице. Выведите структуру таблицы

после модификации.

### 1.7. Изменение данных в таблицах

Для вставки в таблицу базы данных новых строк можно использовать команду INSERT. Команда INSERT применяется в двух вариантах в зависимости от того, нужно ли вставить в таблицу одну строку или сразу несколько строк. Например, приведенная команда добавит строку в таблицу TB1 с пустыми значениями для третьего и четвертого столбцов:

INSERT INTO tb1

VALUES (100, 1012, NULL, NULL);

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

INSERT INTO tab2

SELECT * FROM sal;

УПРАЖНЕНИЕ 7.1.

Создайте две таблицы с одинаковой структурой. Вставьте

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

команды INSERT. Скопируйте строки из первой таблицы во

вторую, применив второй вариант команды.

Для удаления строк из таблиц можно использовать команды DELETE и TRUNCATE. При помощи команды DELETE можно удалить все строки из таблицы (если не использовать фразу WHERE) или только строки, удовлетворяющие условию во фразе WHERE. Команда TRUNCATE всегда удаляет все строки из таблицы.

Примеры:

DELETE FROM tab1;

DELETE FROM tab2 WHERE snum=1003;

TRUNCATE TABLE tab2;

УПРАЖНЕНИЕ 7.2.

Удалите все строки из первой таблицы, созданной

в упражнении 7.1. В вашей копии таблицы Продавцов (TAB2)

удалите только одну строку, в которой город - San Jose.

Для изменения полей существующих в таблице строк используют команду UPDATE. Эта команда обновляет поля для всех строк, удовлетворяющих условию во фразе WHERE. В следующем примере команда изменяет название города:

UPDATE tab2

SET city = 'Paris'

WHERE snum = '1002';

УПРАЖНЕНИЕ 7.3.

Скопируйте таблицу SAL под именем MYSAL и увеличьте

в своей копии комиссионные на 0.01 у продавцов, работающих

в городе Лондон.

### 2 Конторольные вопросы

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

2.Перечислите известные вам реляционные, булевы и специальные операторы языка MySQL.

3.Каким образом агрегатные функции применяются к группам строк?

4.Для чего служит механизм определения псевдонимов таблиц?

5.Назовите команды создания, изменения и удаления таблиц.

6.Перечислите команды изменения данных в таблицах.

### 3 Задание

В файл z1-1.txt занесите ответы на следующие вопросы:

1. Напишите запрос, который выводит все строки из таблицы Покупателей, для которых номер продавца равен 1001.

2. Напишите запрос, который выводит таблицу Продавцов со столбцами в следующем порядке: city, sname, snum, comm.

3. Напишите запрос, который выводит оценку (rating), сопровождаемую именем каждого покупателя в городе San Jose.

4. Напишите запрос, который выводит значение номера продавца всех продавцов из таблицы Заказов без каких бы то ни было повторений.

5. Напишите запрос, который может выдать вам поля sname и city для всех продавцов в Лондоне с комиссионными строго больше 0.11

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

7. Запросите двумя способами все заказы на 3 и 5 октября 1990 г.

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

9. Напишите запрос, который выберет всех продавцов, имена которых содержат букву e.

10. Напишите запрос, который сосчитал бы сумму всех заказов на 3 октября 1990 г.

11. Напишите запрос, который сосчитал бы сумму всех заказов для продавца с номером 1001

12. Напишите запрос, который выбрал бы наибольший заказ для каждого продавца.

13. Напишите запрос, который выбрал бы покупателя, чье имя является первым в алфавитном порядке среди имен, заканчивающихся на букву s.

14. Напишите запрос, который выбрал бы средние комиссионные в каждом городе.

15. Напишите запрос, который вывел бы для каждого заказа на 3 октября его номер, стоимость заказа в евро (1$=0.8 евро), имя продавца и размер комиссионных, полученных продавцом за этот заказ.

16. Напишите запрос, который выводит номера заказов в возрастающем порядке, а также имена продавцов и покупателей заказов, продавец которых находится в Лондоне или Риме.

17. Запросите имена продавцов в алфавитном порядке, суммарные значения их заказов, совершенных до 5 октября, и полученные комиссионные.

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

19. Запросите все пары покупателей, обслуживаемые одним и тем же продавцом. Исключите комбинации покупателей с самими собой, а также пары в обратном порядке.

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

21. Напишите команду, создающую копию таблицы Продавцов с одновременным копированием данных из SAMPLE.SAL. Убедитесь в сходности структур таблиц при помощи команды DESC и идентичности данных в таблице-оригинале и таблице-копии.

22. Напишите последовательность команд, которая вставляет две новые записи в вашу таблицу Продавцов, выводит таблицу после вставки, удаляет одну запись о новом продавце и вновь выводит таблицу.

23. Напишите последовательность команд, которая вставляет две строки в вашу таблицу Продавцов, увеличивает в 2 раза комиссионные у всех продавцов и выводит содержимое таблицы после каждого изменения.