Профессор А. К. Иванов-Шиц Базы данных Профессор Алексей Кириллович Иванов-Шиц МГИМО Кафедра математических методов и информационных технологий ММИТ
Профессор А. К. Иванов-Шиц My. SQL ММИТ После входа в систему можно узнать список команд h Выйти из программы монитора q. Ключевые слова SQL не зависят от регистра (!!!) И… чтобы не было путаницы, будем считать, что идентификаторы (имена БД, таблиц, столбцов, индексов) зависят от регистра После входа в систему можно узнать наличие баз данных: SHOW DATABASES; (; - обязательно, попробовать без; ) Затем выбираем БД, с которой будем работать: USE “Имя БД”; Затем можем убрать БД, Затем смотрим таблицы в БД: SHOW TABLES; Затем выбираем таблицу, с которой будем работать: Describe “Имя таблицы» ; -------------------------------------- Базы данных My. SQL Слайд 2
Профессор А. К. Иванов-Шиц My. SQL ММИТ Приступим к созданию БД EMPLOYEE Employee(employee_ID, name, job, department_ID) Department(department_ID, name) Employee. Skills(employee_ID, skill) Client(client_ID, name, address, contact. Person, contact. Number) Assignment(client_ID, employee_ID, workdate, hours) CREATE DATABASE EMPLOYEE; Затем проверяем: SHOW DATABASES; и выбираем БД: USE EMPLOYEE; (Для сведения: DROP DATABASE EMPLOYEE; DROP DATABASE IF EXISTS EMPLOYEE; CREATE DATABASE EMPLOYEE; ) Базы данных My. SQL Слайд 3
Профессор А. К. Иванов-Шиц My. SQL ММИТ Если хотим давать названия с разрывом или на русском языке, то названия заключить в одиночные кавычки (где буква Ё) Базы данных My. SQL Слайд 4
Профессор А. К. Иванов-Шиц My. SQL ММИТ Создаем таблицы: CREATE TABLE DEPARTMENT (DEPARTMENT_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(20)) TYPE=INNODB; (в таблице 2 столбца, type = Inno. DB указывает на механизм хранения. My. SQL по умолчанию использует My. ISAM. Inno. DB поддерживает внешние ключи и транзакции, а My. ISAM - нет, но таблицы My. ISAM работают быстрее. Подробнее смотри в файле Движки My. SQL. docx). ). CREATE TABLE EMPLOYEE (EMPLOYEE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(80), JOB VARCHAR(15), DEPARTMENT_ID INT NOT NULL REFERENCES DEPARTMENT(DEPARTMENT_ID) ) TYPE=INNODB; Здесь выражение REFERENCES DEPARTMENT(DEPARTMENT_ID) является внешним ключом, т. е. DEPARTMENT_ID ссылается на столбец DEPARTMENT_ID в таблице DEPARTMENT. My. SQL Слайд 5 Базы данных
Профессор А. К. Иванов-Шиц My. SQL ММИТ Создаем еще одну таблицу: CREATE TABLE EMPLOYEE_SKILLS (EMPLOYEE_ID INT NOT NULL REFERENCES EMPLOYEE(EMPLOYEE_ID), SKILL VARCHAR(15) NOT NULL, PRIMARY KEY (EMPLOYEE_ID, SKILL)) TYPE=INNODB; В этой таблице есть внешний ключ, а также первичный сложный ключ из двух столбцов EMPLOYEE_ID, SKILL, которые мы сначала объявили. Создаем еще одну таблицу: CREATE TABLE CLIENT (CLIENT_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(40), ADDRESS VARCHAR(100), CONTACT_PERSON VARCHAR(80), CONTACT_NUMBER CHAR(12)) TYPE=INNODB; Базы данных My. SQL Слайд 6
Профессор А. К. Иванов-Шиц My. SQL ММИТ Создаем еще одну таблицу: CREATE TABLE ASSIGNMENT ( CLIENT_ID INT NOT NULL REFERENCES CLIENT(CLIENT_ID), EMPLOYEE_ID INT NOT NULL REFERENCES EMPLOYEE(EMPLOYEE_ID), WORKDATE NOT NULL, HOURS FLOAT, PRIMARY KEY (CLIENT_ID, EMPLOYEE_ID, WORKDATE) ) TYPE=INNODB; После этого проверяем, какие таблицы созданы: SHOW TABLES; И узнаем о каждой таблице, например: DESCRIBE Department; Изменение структуры таблиц: ALTER TABLE Employee ADD INDEX NAME (name); Базы данных My. SQL Слайд 7
Профессор А. К. Иванов-Шиц My. SQL ММИТ Домашняя работа: Создать БД: Customer(customer_ID, Customer. Name, customer. Address) Order(order_ID, order date, customer_ID) Order. Item(order_ID, item. Quantity) Item(item_ID, item. Name) Измените таблицу Order: добавьте поле notes, которое имеет тип TEXT. Базы данных My. SQL Слайд 8
Профессор А. К. Иванов-Шиц My. SQL ММИТ Вставка, удаление и обновление данных use employee; (на всякий случай удаляем все текстовые данные) delete from department; insert into department values (42, 'Finance'), (128, 'Research and Development'), (NULL, 'Human Resources'), (NULL, 'Marketing'); (Если обнаружена ошибка, то можно ALTER TABLE DEPARTMENT CHANGE NAME VARCHAR(30); Имя столбца повторяется два раза, оно «как бы» переименовывается) delete from employee; insert into employee values (7513, 'Nora Edwards', 'Programmer', 128), (9842, 'Ben Smith', 'DBA', 42), (6651, 'Ajay Patel', 'Programmer', 128), (9006, 'Candy Burnett', 'Systems Administrator', 128); Базы данных My. SQL Слайд 9
Профессор А. К. Иванов-Шиц My. SQL ММИТ delete from employee_skills; insert into employee_skills values (7513, 'C'), (7513, 'Perl'), (7513, 'Java'), (9842, 'DB 2'), (6651, 'VB'), (6651, 'Java'), (9006, 'NT'), (9006, 'Linux'); delete from client; insert into client values (NULL, 'Telco Inc', '1 Collins St Melbourne', 'Fred Smith', '95551234'), (NULL, 'The Bank', '100 Bourke St Melbourne', 'Jan Tristan', '95559876'); delete from assignment; insert into assignment values (1, 7513, '2003 -01 -20', 8. 5); Базы данных My. SQL Слайд 10
Профессор А. К. Иванов-Шиц My. SQL ММИТ Извлечение данных Как получить все данные из таблицы? С помощью запроса: Базы данных My. SQL Слайд 11
Профессор А. К. Иванов-Шиц My. SQL ММИТ Запросы SQL Напомним SELECT столбцы FROM таблицы [WHERE условия [GROWP BY группа [HAVING BY групповые условия]] [ORDER BY сортировка-столбцов] [LIMIT пределы] Базы данных My. SQL Слайд 12
Профессор А. К. Иванов-Шиц Псевдонимы: My. SQL ММИТ Запросы SQL В данном случае мы назначили столбцу NAME новое имя EMPLOYEEName только для этого запроса. Поменяем последовательность выведения столбцов Базы данных My. SQL Слайд 13
Профессор А. К. Иванов-Шиц Выборка с условием Базы данных My. SQL ММИТ Запросы SQL My. SQL Слайд 14
Профессор My. SQL А. К. Иванов-Шиц ММИТ Запросы SQL SELECT JOB FROM EMPLOYEE ; Базы данных My. SQL Слайд 15
Профессор А. К. Иванов-Шиц My. SQL ММИТ Запросы SQL SELECT DISTINCT JOB FROM EMPLOYEE ; Сортировать порядок групп (в убывающем порядке) Базы данных My. SQL Слайд 16
Профессор А. К. Иванов-Шиц СЛОЖНЫЕ ЗАПРОСЫ Объединение двух таблиц My. SQL ММИТ Запросы SQL Что здесь нехорошо? Базы данных My. SQL Слайд 17
Профессор А. К. Иванов-Шиц My. SQL ММИТ Запросы SQL В названиях таблицы заголовки с одним именем NAME. Воспользуемся псевдонимами Базы данных My. SQL Слайд 18
Профессор А. К. Иванов-Шиц My. SQL ММИТ Запросы SQL Если из запроса убрать where, т. е. выполнить запрос SELECT EMPLOYEE. Name, DEPARTMENT. NAME FROM EMPLOYEE, DEPARTMENT; Получим Получили декартово произведение (все возможные комбинации из двух таблиц) Мы объединяли таблицы по внешним ключам!!! Базы данных My. SQL Слайд 19
Профессор А. К. Иванов-Шиц My. SQL ММИТ Запросы SQL Узнаем, служащим какого отдела было поручено работать с клиентом фирмы Telco inc. Вспомним нашу БД EMPLOYEE Employee(employee_ID, name, job, department_ID) Department(department_ID, name) Employee. Skills(employee_ID, skill) Client(client_ID, name, address, contact. Person, contact. Number) Assignment(client_ID, employee_ID, workdate, hours) Зная имя клиента, находим его идентификационный номер (client_ID), затем идем в таблицу заданий и находим номера служащих (employee_ID), которые работали с клиентом, а затем из таблицы служащих находим номера отделов, где работают служащие: Базы данных My. SQL Слайд 20
Профессор А. К. Иванов-Шиц My. SQL ММИТ Запросы SQL Для связывания 4 таблиц использовали три условия объединения. Базы данных My. SQL Слайд 21
Профессор А. К. Иванов-Шиц My. SQL ММИТ Запросы SQL Самообъединение таблиц – если интересуют связи между строками одной таблицы. Мы хотим выяснить имена служащих отдела, в котором работает Nora Edwards. Для этого надо найти по имени Nora отдел, в котором она работает, и вывести все имена (Напомним, что для таблиц можно задавать псевдонимы по разному: SELECT E. NAME FROM EMPLOYEE AS E; Можно обойтись без ключевого слова AS SELECT E. NAME FROM EMPLOYEE E; ) Базы данных My. SQL Слайд 22
Профессор А. К. Иванов-Шиц My. SQL ММИТ Запросы SQL Для таблицы EMPLOYEE определили два псевдонима (две таблицы Е 1 и Е 2), а затем их объединяем. Чуть улучшим запрос Базы данных My. SQL Слайд 23
Профессор А. К. Иванов-Шиц My. SQL ММИТ Домашнее задание 1. Создайте запрос, возвращающий имя служащего и всю информацию о его квалификации (SKILL). Базы данных My. SQL Слайд 24
Профессор А. К. Иванов-Шиц My. SQL ММИТ Домашнее задание 2. Создайте запрос, использующий LEFT JOIN и возвращающий список клиентов, для которых не было выделено служащих, работающих с ними. Базы данных My. SQL Слайд 25
Профессор А. К. Иванов-Шиц My. SQL ММИТ Домашнее задание 3. Перепишите свой запрос № 2, чтобы в нем использовалось бы ключевое слово EXISTS. Базы данных My. SQL Слайд 26
Профессор ММИТ А. К. Иванов-Шиц Спасибо. Занятие окончено. My. SQL Базы данных Слайд 27
Скачать презентацию Профессор А К Иванов-Шиц Базы данных Профессор Алексей
БД_Лекция_10(MySQL)_студ.ppt