Проектирование баз данных

КАЗАНСКИЙ (ПРИВОЛЖСКИЙ) ФЕДЕРАЛЬНЫЙ УНИВЕРСИТЕТ

 

Пинягина О.В.

Основы работы

С базами данных -

СУБД mySQL

 

Казань – 2015

УДК 004.6

ББК 32.973.26 – 018.2

Печатается по решению Редакционно-издательского совета

ФГАОУВПО «Казанский (Приволжский) федеральный университет»,

Редакционно-издательского совета Института вычислительной

математики и информационных технологий

Протокол № от,

заседания кафедры анализа данных и исследования операций

Протокол № от.

 

Рецензенты:

…

 

Пинягина О.В.

Основы работы с базами данных - СУБД mySQL: Учебное пособие / О.В. Пинягина – Казань: Казанский университет, 2015. – 90 с.

 

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

В пособии рассматриваются основы проектирования и построения баз данных: ER-модель и реляционная модель, а также основы языка SQL. Каждая тема содержит задания для индивидуальной работы.

В качестве среды программирования используется my SQL Workbench.

Электронный ресурс по данному курсу располагается на сайте кафедры анализа данных и исследования операций КФУ по адресу: http: //kek.ksu.ru/EOS/mysql/index.html.

ã Казанский университет, 2015

ã Пинягина О.В., 2015

Оглавление

 

Проектирование баз данных. 4

ER-модель (entity-relationship model) 4

Пример ER-модели: Контора «Рога и копыта». 10

Пример ER-модели: «Музыканты». 11

Установка MySQL. 13

Работа в mySQL Workbench - Создание EER-диаграммы.. 16

Работа в mySQL Workbench - Создание базы данных из EER-диаграммы.. 23

Работа в mySQL Workbench - Заполнение базы данных, модификация данных. 28

Работа в mySQL Workbench - Запросы к базе данных. 41

Выборка из одной таблицы.. 42

Использование условий отбора. 44

Использование агрегирующих функций. 46

Сортировка. 47

Подзапросы.. 48

Группировка. 50

Выборка из нескольких таблиц. 51

Объединение запросов. 53

И еще несколько примеров. 54

Работа в mySQL Workbench - Представления. 57

Работа в mySQL Workbench - Хранимые процедуры.. 61

Работа в mySQL Workbench - Триггеры.. 67

Приложение 1. Сценарий создания базы данных. 71

Приложение 2. Dbforge Studio от Devart 75

Приложение 3. Реляционная алгебра и SQL. 85

Литература. 94

Дистрибутивы.. 94

 

 

Проектирование баз данных

Пример ER-модели: Контора «Рога и копыта»

Описание задачи

Контора «Рога и копыта» занимается коммерческой деятельностью по реализации продукции, произведенной из рогов и копыт, и предоставлению магических услуг.

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

Организация работает с предприятиями-клиентами. Каждое предприятие имеет название и адрес. С предприятием может быть заключено несколько договоров.

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

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

 

Пример ER-модели: «Музыканты»

 

 

Описание задачи

 

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

Музыкант характеризуется именем, датой рождения и страной рождения.

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

Музыкант может играть на разных инструментах с разной степенью квалификации.  

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

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

Задание. Выберите предметную область (3-4 сущности и связи между ними). В произвольной форме опишите её. Нарисуйте ER- диаграмму базы данных.

 Дополнительная информация. Подробнее о проектировании базы данных и построении ER-модели можно почитать в [ 1, Глава 1, параграфы 1.2, 1.3], [ 4, Глава 3].

Установка MySQL

Рассмотрим установку пакета программ для Windows, содержащего MySQL server, среду для разработки и администрирования MySQL Workbench и много других полезных компонентов. (Источник - http: //www.mysql.com/downloads/installer/ )

1. Для начала нужно установить Microsoft.NET 4.0 Framework, если его нет.

2. Установить Visual C++ Redistributable Packages for Visual Studio 2013.

 

Теперь можно устанавливать MySQL Workbench - mysql-installer-community-5.5.28.3.msi или более новую версию:

 

На 2 странице принимаем лицензию. На 3 странице не проверяем обновления (skip the check for updates).

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

 

Устанавливаем следующие продукты: (первый пункт – это сам сервер, второй пункт – среда разработчика)

Конфигурирование сервера:

Для администратора по имени root зададим пароль (запомните его! ) А также создадим пользователя по имени user с паролем (тоже запомните его! ) (можно сделать пароли, совпадающие с именем пользователя: root и user, хотя в смысле безопасности это плохой вариант, но для учебных целей годится).

На следующих страницах ничего не изменяем. Имя сервера по умолчанию MySQL55. Установка успешно завершена.

Выборка из одной таблицы

 

Обязательные ключевые слова команды – SELECT и FROM.

Тривиальная выборка всех полей и всех строк одной таблицы.

Получить полную информацию обо всех предприятиях:

SELECT * FROM k_firm

 

Выбор отдельных полей таблицы.

Получить названия и адреса всех предприятий:

SELECT firm_name, firm_addr FROM k_firm

 

Поля выборки можно переименовывать. Если новое название состоит из нескольких слов, помещайте его в кавычки.

 

 SELECT firm_name

AS " Название предприятия",

firm_addr 

AS " Адрес предприятия" FROM k_firm

 

В списке полей выборки можно использовать выражения. В этом случае часто приходится преобразовывать данные из одного типа в другой с помощью функции CONVERT. Строковые константы следует помещать в одинарные кавычки. Функция CONCAT служит для сцепления строк.

Распечатать информацию о счетах:

SELECT CONCAT(" Счет № ", CONVERT(bill_num, CHAR),

" от ", CONVERT(bill_date, CHAR),

" на сумму ", CONVERT(bill_sum, CHAR)) AS Счета

FROM k_bill

 

 

Для того чтобы исключить дубликаты строк, нужно использовать ключевое слово DISTINCT.

Напечатать список городов, в которых находятся предприятия-клиенты:

SELECT DISTINCT firm_addr FROM k_firm

 

 

Сортировка

 

Для сортировки используется ключевое слово ORDER BY и имя поля или его номер в списке полей выборки.

Напечатать список сотрудников, отсортированный по алфавиту:

SELECT staff_name FROM k_staff ORDER BY 1

 

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

Напечатать список сотрудников, отсортированный по дате поступления на работу:

SELECT staff_name FROM k_staff ORDER BY staff_hiredate

 

 

Сортировать данные можно и по убыванию. Кроме того, можно ограничить количество строк в результате.

Напечатать информацию о 5 последних выписанных счетах в порядке убывания даты счета:

SELECT bill_num, bill_date

FROM k_bill ORDER BY bill_date DESC LIMIT 5

 

Подзапросы

 

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

Подзапрос всегда указывается в скобках.

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

Получить список договоров, по которым в декабре 2011 года выписаны счета:

SELECT contract_num, contract_date FROM k_contract

WHERE contract_num IN

    (SELECT k_contract_contract_num FROM k_bill

    WHERE bill_date

BETWEEN '2011-12-01' AND '2011-12-31')

 

 

Тот же самый запрос с использованием ключевого слова ANY:

SELECT contract_num, contract_date FROM k_contract

WHERE contract_num =ANY

(SELECT k_contract_contract_num FROM k_bill

WHERE bill_date

BETWEEN '2011-12-01' AND '2011-12-31')

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

Кроме того, в данном примере иллюстрируется использование ключевого слова EXISTS:

 

SELECT contract_num, contract_date FROM k_contract c

WHERE EXISTS

(SELECT * FROM k_bill b

WHERE bill_date   

BETWEEN '2011-12-01' AND '2011-12-31'

AND c.contract_num=b.k_contract_contract_num)

 

Пример использования ключевого слова ALL.

Напечатать информацию о товаре (товарах) с наименьшей ценой.

SELECT price_name, price_sum FROM k_price

WHERE price_sum < = ALL

    (SELECT price_sum FROM k_price)

Этот запрос можно сформулировать и по-другому. В этом примере мы можем использовать операцию сравнения =, т.к. подзапрос возвращает ровно одну строку и один столбец.

SELECT price_name, price_sum FROM k_price

WHERE price_sum = 

    (SELECT MIN(price_sum) FROM k_price)

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

SELECT price_name, MIN(price_sum) FROM k_price

 

Как видите, значение столбца price_name просто было взято из первой строки таблицы.

 

Группировка

 

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

Получить список договоров и общую сумму счетов по каждому договору:

SELECT contract_num, SUM(bill_sum) AS contract_sum

FROM k_bill

GROUP BY contract_num

 

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

Получить список договоров, имеющих 2 или более счетов, и общую сумму счетов по каждому договору:

SELECT k_contract_contract_num, SUM(bill_sum) AS contract_sum

FROM k_bill

GROUP BY k_contract_contract_num

HAVING COUNT(bill_num)> =2;

 

Объединение запросов

 

Для объединения результатов двух и более запросов нужно использовать ключевое слово UNION. Объединяемые запросы должны иметь одинаковое количество и тип полей. Параметр ORDER BY, если он нужен, следует указывать только в последнем запросе.

 

Получить список договоров и общую сумму счетов по каждому договору, а также строку с итоговой суммой:

 

SELECT CONCAT('Договор № ',

          CONVERT(k_contract_contract_num, CHAR),

          ' на сумму ') AS " Номер",

  SUM(bill_sum) AS " Сумма" FROM k_bill

GROUP BY k_contract_contract_num

UNION

SELECT 'ИТОГО: ', SUM(bill_sum) FROM k_bill ORDER BY 1

 

И еще несколько примеров

 

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

 

SELECT pr.price_name, pr.price_sum,   

  SUM(prot.kolvo*prot.price_sum)

FROM k_price pr, k_protokol prot

WHERE pr.price_num=prot.k_price_price_num

GROUP BY pr.price_num ORDER BY 1

 

 

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

SELECT b.bill_num AS " Номер счета",

b.bill_date AS " Дата счета",

b.bill_sum AS " Сумма счета",

SUM(p.payment_sum) AS " Сумма оплаты"

FROM k_bill b, k_payment p

WHERE b.bill_num=p.k_bill_bill_num AND

b.bill_sum< =

(SELECT SUM(payment_sum) FROM k_payment p2

WHERE b.bill_num=p2.k_bill_bill_num)

GROUP BY b.bill_num

 

Полностью неоплаченные счета, по которым вообще нет платежей.

SELECT b.bill_num AS " Номер счета",

b.bill_date AS " Дата счета",

b.bill_sum AS " Сумма счета",

0 AS " Сумма оплаты"

FROM k_bill b

WHERE b.bill_num NOT IN (SELECT k_bill_bill_num FROM k_payment)

Частично оплаченные счета. Обратите внимание, что в этом примере в параметре FROM вместо второй таблицы используется вложенный SELECT

SELECT b.bill_num AS " Номер счета",

b.bill_date AS " Дата счета",

b.bill_sum AS " Сумма счета",

p.pay_sum AS " Сумма оплаты"

FROM k_bill b,

(SELECT k_bill_bill_num, SUM(payment_sum) as pay_sum 

FROM k_payment

GROUP BY k_bill_bill_num) p

WHERE b.bill_sum > p.pay_sum AND b.bill_num=p.k_bill_bill_num

 

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

Дополнительная информация. Подробнее о команде SELECT, используемых в ней операциях и функциях  можно прочитать в [1, Глава 2, параграф 2.5, Глава 3 ], [3, Главы 5, 6, 7 ].

 

 

 

Объявление курсора:

DECLARE имя_курсора CURSOR FOR SELECT текст_запроса;

Таким образом, любой курсор создается на основе некоторого оператора SELECT.

Открытие курсора:

OPEN имя_курсора;

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

Чтение значений из текущей строки курсора в набор переменных и перемещение указателя на следующую строку:

FETCH имя_курсора INTO список_переменных;

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

Закрытие курсора:

CLOSE имя_курсора;

При переборе строк курсора возникает необходимость проверки, добрались ли мы до конца курсора или еще нет. В разных СУБД для этого могут быть предусмотрены разные средства. В СУБД MySQL назначается обработчик состояния “NOT FOUND”. Определять его нужно сразу же после описания структуры курсора:

DECLARE CONTINUE HANDLER FOR NOT FOUND оператор;

Например, этот обработчик может выглядеть так:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

Теперь попробуем модифицировать процедуру «Распродажа» с учетом того, что у нас может быть несколько товаров с минимальным количеством продаж.

 

DELIMITER //

 

CREATE PROCEDURE clearance2 (percent INT)

BEGIN

DECLARE p INT;

DECLARE finished NUMERIC(1);

–- объявляем курсор на основе некоторого оператора SELECT

DECLARE my_cursor CURSOR

FOR SELECT k_price_price_num FROM k_protokol

GROUP BY k_price_price_num

    HAVING SUM(kolvo)< =ALL

    (SELECT SUM(kolvo) FROM k_protokol

         GROUP BY k_price_price_num);       

-- объявляем обработчик состояния NOT FOUND

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

 

IF percent > 0 AND percent < 100 THEN

SET finished = 0;

OPEN my_cursor;          -- открываем курсор

FETCH my_cursor INTO p; -- читаем первую строку 

WHILE( finished! = 1) DO

   UPDATE k_price

      SET price_sum=price_sum*(100-percent)/100

      WHERE price_num=p;

   FETCH my_cursor INTO p; -- читаем очередную строку

END WHILE;

CLOSE my_cursor; -- закрываем курсор

END IF;

END//

Задание. Создайте несколько (не менее 2) хранимых процедур для вашей базы данных. Желательно использовать параметры. Запустите процедуры на выполнение. Более подробно о хранимых процедурах в MySQL можно прочитать в [2, Глава 33].

 

Приложение 1. Сценарий создания базы данных

В данном приложении приведен сценарий, автоматически сформированный при создании базы данных из EER-модели.

 

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

 

CREATE SCHEMA IF NOT EXISTS `kontora` DEFAULT CHARACTER SET cp1251 COLLATE cp1251_general_ci;

 

-- -----------------------------------------------------

-- Table `kontora`.`k_staff`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kontora`.`k_staff` (

`staff_num` INT NULL AUTO_INCREMENT,

`staff_name` VARCHAR(45) NOT NULL,

`staff_post` VARCHAR(45) NULL,

`staff_hiredate` DATE NULL,

`staff_termdate` DATE NULL,

`K_dept_dept_num` INT NOT NULL,

PRIMARY KEY (`staff_num`),

INDEX `fk_k_staff_K_dept1` (`K_dept_dept_num` ASC),

CONSTRAINT `fk_k_staff_K_dept1`

FOREIGN KEY (`K_dept_dept_num` )

REFERENCES `kontora`.`K_dept` (`dept_num` )

 

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

 

-- -----------------------------------------------------

-- Table `kontora`.`K_dept`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kontora`.`K_dept` (

`dept_num` INT NULL AUTO_INCREMENT,

`dept_full_name` VARCHAR(45) NULL,

`dept_short_name` VARCHAR(10) NOT NULL,

`k_staff_staff_num` INT NOT NULL,

PRIMARY KEY (`dept_num`),

INDEX `fk_K_dept_k_staff1` (`k_staff_staff_num` ASC),

CONSTRAINT `fk_K_dept_k_staff1`

FOREIGN KEY (`k_staff_staff_num` )

REFERENCES `kontora`.`k_staff` (`staff_num` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

 

-- -----------------------------------------------------

-- Table `kontora`.`k_firm`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kontora`.`k_firm` (

`firm_num` INT NULL AUTO_INCREMENT,

`firm_name` VARCHAR(45) NOT NULL,

`firm_addr` VARCHAR(45) NULL,

`firm_phone` VARCHAR(20) NULL,

PRIMARY KEY (`firm_num`) )

ENGINE = InnoDB;

 

-- -----------------------------------------------------

-- Table `kontora`.`k_contract`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kontora`.`k_contract` (

`contract_num` INT NULL AUTO_INCREMENT,

`contract_date` DATE NOT NULL,

`contract_type` ENUM('A', 'B', 'C') NOT NULL,

`k_firm_firm_num` INT NOT NULL,

`k_staff_staff_num` INT NOT NULL,

PRIMARY KEY (`contract_num`),

INDEX `fk_k_contract_k_firm1` (`k_firm_firm_num` ASC),

INDEX `fk_k_contract_k_staff1` (`k_staff_staff_num` ASC),

CONSTRAINT `fk_k_contract_k_firm1`

FOREIGN KEY (`k_firm_firm_num` )

REFERENCES `kontora`.`k_firm` (`firm_num` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_k_contract_k_staff1`

FOREIGN KEY (`k_staff_staff_num` )

REFERENCES `kontora`.`k_staff` (`staff_num` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

 

-- -----------------------------------------------------

-- Table `kontora`.`k_bill`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kontora`.`k_bill` (

`bill_num` INT NULL AUTO_INCREMENT,

`bill_date` DATE NULL,

`bill_sum` DECIMAL(9, 2) NULL,

`bill_term` DATE NULL,

`bill_peni` DECIMAL(6, 2) NULL,

`k_contract_contract_num` INT NOT NULL,

PRIMARY KEY (`bill_num`),

INDEX `fk_k_bill_k_contract1` (`k_contract_contract_num` ASC),

CONSTRAINT `fk_k_bill_k_contract1`

FOREIGN KEY (`k_contract_contract_num` )

REFERENCES `kontora`.`k_contract` (`contract_num` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB

AUTO_INCREMENT = 1;

 

-- -----------------------------------------------------

-- Table `kontora`.`k_payment`

-- -----------------------------------------------------

 

CREATE TABLE IF NOT EXISTS `kontora`.`k_payment` (

`payment_num` INT NULL,

`payment_date` DATE NULL,

`payment_sum` DECIMAL(9, 2) NULL,

`k_bill_bill_num` INT NOT NULL,

PRIMARY KEY (`payment_num`),

INDEX `fk_k_payment_k_bill1` (`k_bill_bill_num` ASC),

CONSTRAINT `fk_k_payment_k_bill1`

FOREIGN KEY (`k_bill_bill_num` )

REFERENCES `kontora`.`k_bill` (`bill_num` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

 

-- -----------------------------------------------------

-- Table `kontora`.`k_price`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kontora`.`k_price` (

`price_num` INT NULL AUTO_INCREMENT,

`price_name` VARCHAR(45) NOT NULL,

`price_sum` DECIMAL(9, 2) NULL,

`price_type` VARCHAR(1) NULL,

PRIMARY KEY (`price_num`) )

ENGINE = InnoDB;

 

-- -----------------------------------------------------

-- Table `kontora`.`k_protokol`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kontora`.`k_protokol` (

`k_bill_bill_num` INT NOT NULL,

`k_price_price_num` INT NOT NULL,

`kolvo` INT NOT NULL,

`price_sum` DECIMAL(9, 2) NOT NULL,

PRIMARY KEY (`k_bill_bill_num`, `k_price_price_num`),

INDEX `fk_k_bill_has_k_price_k_bill1` (`k_bill_bill_num` ASC),

INDEX `fk_k_bill_has_k_price_k_price1` (`k_price_price_num` ASC),

CONSTRAINT `fk_k_bill_has_k_price_k_bill1`

FOREIGN KEY (`k_bill_bill_num` )

REFERENCES `kontora`.`k_bill` (`bill_num` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_k_bill_has_k_price_k_price1`

FOREIGN KEY (`k_price_price_num` )

REFERENCES `kontora`.`k_price` (`price_num` )

ON DELETE NO ACTION

 

ON UPDATE NO ACTION)

ENGINE = InnoDB;

 

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

 

Приложение 2. Dbforge Studio от Devart

Для работы с сервером MySQL существуют альтернативные интерфейсы, позволяющие осуществлять разработку баз данных и администрирование сервера. Один из наиболее интересных вариантов – dbForge Studio от компании Devart. Приведем несколько иллюстраций для наиболее типичных административных функций.

Приложение 3. Реляционная алгебра и SQL

 

Рассмотрим, как связаны операции реляционной алгебры и язык SQL, т.е. приведем примеры запросов SQL, аналогичных операциям реляционной алгебры. В качестве примера базы данных будем использовать «Музыкантов».

 

Операция проекции proj выражается через SELECT с ключевым словом DISTINCT.

Получить все названия ансамблей:

proj НазАнс (Ансамбли)

 

SELECT DISTINCT НазАнс FROM Ансамбли

    Операция выбора sel выражается через SELECT с ключевым словом WHERE.

Получить данные об ансамблях из России:

sel СтрАнс='Россия' (Ансамбли)

 

SELECT * FROM Ансамбли WHERE СтрАнс='Россия'

 

Условия также могут быть и сложными.

 

Получить имена музыкантов, родившихся в 20-м веке

 

SELECT ИмяМуз FROM Музыканты WHERE ДатаРожд> '1900-12-31' AND ДатаРожд< '2001-01-01'

 

Операция соединения таблиц   join  может быть выражена несколькими способами.

Получить имена композиторов:

proj ИмяМуз (Музыканты join Сочинения)

Можно использовать связь таблиц через условие WHERE:

 

SELECT DISTINCT ИмяМуз FROM Музыканты М, Сочинения С WHERE С.НомМуз=М.НомМуз

 

Можно использовать более современный синтаксис JOIN... ON

 

SELECT DISTINCT ИмяМуз FROM Музыканты М JOIN Сочинения С ON С.НомМуз=М.НомМуз

 

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

 

SELECT DISTINCT ИмяМуз FROM Музыканты WHERE НомМуз IN

(SELECT НомМуз FROM Сочинения)

 

или

 

SELECT DISTINCT ИмяМуз FROM Музыканты WHERE НомМуз = Any

(SELECT НомМуз FROM Сочинения)

 

или

 

SELECT DISTINCT ИмяМуз FROM Музыканты М WHERE EXISTS

(SELECT * FROM Сочинения С WHERE

       С.НомМуз=М.НомМуз)

 

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

Получить названия ансамблей, которые играли Моцарта на саксофоне:

Proj НазАнс

(proj НомСоч (sel ИмяМуз='Моцарт' (Музыканты) join

     Сочинения)

join

proj НомСоч, НомАнс

(proj НомИсп

(sel Инструмент ='Саксофон' (Исполнители))

    join УчАнс join Исполнения)

join Ансамбли )

 

SELECT НазАнс FROM Ансамбли WHERE НомАнс IN

(

SELECT И1.НомАнс

FROM Исполнения И1, Исполнители И2, Музыканты М,

Сочинения С, УчАнс У

WHERE И1.НомСоч=С.НомСоч AND С.НомМуз=М.НомМуз AND

И1.НомАнс=У.НомАнс AND И2.НомИсп=У.НомИсп AND

М.ИмяМуз='Моцарт' AND

И2.Инструмент='Саксофон'

)

 

Операция объединения   union  соответствует нескольким командам SELECT, связанным ключевым словом UNION.

Получить общий список фамилий композиторов и дирижеров:

Union

Intersection

Diffrence

Proj НазАнс

(proj НомАнс, НомСоч (Исполнения)

division

proj НомСоч (sel ИмяМуз='Моцарт' (Музыканты)

    join Сочинения)

join Ансамбли)

 

SELECT НазАнс FROM Ансамбли А WHERE NOT EXISTS

(

SELECT * FROM Сочинения С, Музыканты М

WHERE С.НомМуз=М.НомМуз AND ИмяМуз='Моцарт'

AND NOT EXISTS

(

       SELECT * FROM Исполнения И

       WHERE И.НомСоч=С.НомСоч AND

       И.НомАнс=А.НомАнс

)

)

Литература

1. Гольцман B. MySQL 5.0. Серия: Библиотека программиста. - СПб.: Питер, 2009. - 256 с.

2. Кузнецов М., Симдянов И. MySQL 5. - СПб: БХВ-Петербург, 2010. 1024 с.

3. Веллинг Л., Томсон Л. MySQL. Учебное пособие. - М.: Вильямс, 2005. - 304 с.

4. Крёнке Д. Теория и практика построения баз данных. 8-е изд. - СПб.: Питер, 2003. - 800 с.

 

Дистрибутивы

http: //www.mysql.com/downloads/installer/
Пакет программ для Windows, содержащий MySQL server, среду для разработки и администрирования MySQL Workbench и много других полезных компонентов.

http: //www.devart.com/ru/dbforge/mysql/studio/download.html
dbForge Studio for MySQL 6.0. Содержит набор инструментов для профессиональной разработки и управления MySQL-базы данных.

Для заметок

Для заметок

КАЗАНСКИЙ (ПРИВОЛЖСКИЙ) ФЕДЕРАЛЬНЫЙ УНИВЕРСИТЕТ

 

Пинягина О.В.

Основы работы

С базами данных -

СУБД mySQL

 

Казань – 2015

УДК 004.6

ББК 32.973.26 – 018.2

Печатается по решению Редакционно-издательского совета

ФГАОУВПО «Казанский (Приволжский) федеральный университет»,

Редакционно-издательского совета Института вычислительной

математики и информационных технологий

Протокол № от,

заседания кафедры анализа данных и исследования операций

Протокол № от.

 

Рецензенты:

…

 

Пинягина О.В.

Основы работы с базами данных - СУБД mySQL: Учебное пособие / О.В. Пинягина – Казань: Казанский университет, 2015. – 90 с.

 

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

В пособии рассматриваются основы проектирования и построения баз данных: ER-модель и реляционная модель, а также основы языка SQL. Каждая тема содержит задания для индивидуальной работы.

В качестве среды программирования используется my SQL Workbench.

Электронный ресурс по данному курсу располагается на сайте кафедры анализа данных и исследования операций КФУ по адресу: http: //kek.ksu.ru/EOS/mysql/index.html.

ã Казанский университет, 2015

ã Пинягина О.В., 2015

Оглавление

 

Проектирование баз данных. 4

ER-модель (entity-relationship model) 4

Пример ER-модели: Контора «Рога и копыта». 10

Пример ER-модели: «Музыканты». 11

Установка MySQL. 13

Работа в mySQL Workbench - Создание EER-диаграммы.. 16

Работа в mySQL Workbench - Создание базы данных из EER-диаграммы.. 23

Работа в mySQL Workbench - Заполнение базы данных, модификация данных. 28

Работа в mySQL Workbench - Запросы к базе данных. 41

Выборка из одной таблицы.. 42

Использование условий отбора. 44

Использование агрегирующих функций. 46

Сортировка. 47

Подзапросы.. 48

Группировка. 50

Выборка из нескольких таблиц. 51

Объединение запросов. 53

И еще несколько примеров. 54

Работа в mySQL Workbench - Представления. 57

Работа в mySQL Workbench - Хранимые процедуры.. 61

Работа в mySQL Workbench - Триггеры.. 67

Приложение 1. Сценарий создания базы данных. 71

Приложение 2. Dbforge Studio от Devart 75

Приложение 3. Реляционная алгебра и SQL. 85

Литература. 94

Дистрибутивы.. 94

 

 

Проектирование баз данных

12345678910Следующая ⇒

Поделиться: