Скачать презентацию СПб ГУТ им проф М А Бонч-Бруевича Технологии
01__ТОИ_ВВЕДЕНИЕ__.ppt
- Количество слайдов: 88
Скачать презентацию СПб ГУТ им проф М А Бонч-Бруевича Технологии
Скачать презентацию СПб ГУТ им проф М А Бонч-Бруевича Технологии
СПб. ГУТ им. проф. М. А. Бонч-Бруевича Технологии обработки информации 230400 Информационные системы и технологии ЛЕКЦИИ, ЛАБОРАТОРНЫЕ РАБОТЫ, ПРАКТИЧЕСКИЕ ЗАНЯТИЯ, ЭКЗАМЕН К. т. н. , доцент кафедры ИУС Феликс Васильевич Филиппов 9000096@mail. ru
Темы лекций Тема 1 Информация и технологии Тема 2 Передача и кодирование информации Тема 3 Базы данных и язык SQL Тема 4 XML - cтруктурирование данных Тема 5 Java. Script Тема 6 Структурирование знаний Тема 7 Семантический веб Тема 8 XML Schema Тема 9 RDF и RDF Schema Тема 10 Онтологии и OWL
Тема 11 SPARQL и поиск информации Тема 12 Язык R и RStudio Тема 13 Большие даные (Big Data) Тема 14 Технология Hadoop Тема 15 Data Mining
Литература
Тема 1 Информация и технология
Понятие «информация» (первая концепция) Информация – средство, снимающее неопределенность (энтропию) события энтропию или объекта познания. К. Шеннон – конец 40 -х годов XX века. Эта концепция не учитывает смысловую сторону информации, она оказалась весьма полезной в технике связи и вычислительной технике, послужила основой для измерения информации и оптимального кодирования сообщений.
Понятие «информация» (вторая концепция) Информация – свойство (атрибут) материи. атрибут Это понятие связано с развитием кибернетики - Н. Винер 1948 год. Академик В. М. Глушков: "информацию несут Глушков не только испещренные буквами листы книги или человеческая речь, но и солнечный свет, складки горного хребта, шум водопада, шелест травы".
Понятие «информация» (третья концепция) Информация – это действующая, полезная, "работающая" часть знаний. Концепция основана на логико-семантическом подходе. Социальная информация представляет собой знания, сообщения, сведения о социальной форме движения материи и о всех других формах в той мере, в какой она используется обществом.
Свойства информации • • • Содержательность Достаточность (полнота) Доступность Актуальность (своевременность) Точность (достоверность) Относительность
Данные, информация и знания Ф. Филиппов, доцент СПб. ГУТ им. проф. М. А. Бонч-Бруевича
Место информации Ф. Филиппов, доцент СПб. ГУТ им. проф. М. А. Бонч-Бруевича
Количество информации 1928 год Формула Хартли H = log 2 n H – количество информации n - число возможных равновероятных событий
Количество информации 1948 год Формула Шеннона H – количество информации n - число возможных событий Pi – вероятность i-го события
Домашнее задание № 1 1. На железнодорожном вокзале 8 путей отправления поездов. Вам сообщили, что ваш поезд прибывает на четвертый путь. Сколько информации вы получили? 2. В коробке лежат 16 кубиков. Все кубики разного цвета. Сколько информации несет сообщение о том, что из коробки достали красный кубик? 3. Была получена телеграмма: «Встречайте, вагон 7» . Известно, что в составе поезда 16 вагонов. Какое количество информации было получено? 4. В мешке находятся 20 шаров. Из них 15 белых и 5 красных. Какое количество информации несет сообщение о том, что достали: а) белый шар; б) красный шар. 5. В коробке лежат кубики: 10 красных, 8 зеленых, 5 желтых, 12 синих. Вычислите вероятность доставания кубика каждого цвета и количество информации, которое при этом будет получено.
Технологии • Технология — в широком смысле — объём знаний, которые можно в широком смысле использовать для производства продукта. • Технология — в узком смысле — способ преобразования в узком смысле вещества, энергии, информации в процессе производства информации продукта. • Современные технологии основаны на достижениях научнотехнического прогресса и ориентированы на производство продукта: материальная технология создаёт материальный материальная продукт, информационная технология (ИТ) — информационный информационная продукт.
Обработка информации – составная часть ИТ Информационные технологии (ИТ, от англ. information technology, IT) — IT широкий класс дисциплин и областей деятельности, относящихся к технологиям управления и обработки данных, а также создания данных, в том числе, с применением вычислительной техники. Технологии обработки информации: • представление информации; • поиск исчерпывающей информации; • передача в реальном времени. передача
Тема 2 Передача и кодирование информации
Общая схема передачи информации Источник сообщений Получатель сообщений Кодирующее устройство Декодирующее устройство Канал связи
Эталонные модели (стеки протоколов) OS I 7 Прикладной 6 Представлен ия 5 4 Сеансовый 3 Транспортны й 2 Сетевой 1 Канальный Физический TCP / IP Прикладной Транспортны й Межсетевой От хоста к сети
Протокол TCP/IP TCP / IP (Transmission Control Protocol) / (Internet Protocol) Прикладной Транспортны TCP – разбивает, нумерует Транспортны TCP – собирает фа й й Межсетевой IP – адресует От хоста к сети Канал связи Межсетевой От хоста к сети IP – убирает адрес
Протокол Bit. Torrent Пир – сид или лич Сид – пир имеющий все сегменты Лич – скачивающий пир Рой – совокупность всех пиров Официальный сайт: http: //bitconjurer. org/Bit. Torrent Трекер
Bit. Torrent Overall Architecture Tracker Web Server . torre 1 nt Web page with link to. torrent C A Peer [Leech] Downloader ЗАГРУЗЧИК “US” B Peer [Leech] [Seed]
Bit. Torrent Overall Architecture Tracker Web Server Web page with link to. torrent 2 ce n -an t Ge n ou C A Peer [Leech] Downloader ЗАГРУЗЧИК “US” B Peer [Leech] [Seed]
Bit. Torrent Overall Architecture Tracker Web Server Web page with link to. torrent 3 t s r li e e -p se on p es R C A Peer [Leech] Downloader ЗАГРУЗЧИК “US” B Peer [Leech] [Seed]
Bit. Torrent Overall Architecture Tracker Web Server Web page with link to. torrent Shake-hand 4 A Peer Sh [Leech] Downloader ЗАГРУЗЧИК “US” ak e-h an d C Peer B Peer [Leech] [Seed]
Bit. Torrent Overall Architecture Tracker Web Server Web page with link to. torrent pieces A Peer [Leech] Downloader ЗАГРУЗЧИК “US” pie ce ce s 5 s C Peer B Peer [Leech] [Seed]
Bit. Torrent Overall Architecture Tracker Web Server Web page with link to. torrent ce n ou n st -an t r li e Ge -pe e ns po es pieces R 6 A Peer [Leech] Downloader ЗАГРУЗЧИК “US” pie ce ce s s C Peer B Peer [Leech] [Seed]
Платформа Ace Stream Основные направления специализации платформы: • • AVo. D (Аудио и Видео по Запросу); Live Stream ( «живое» /потоковое вещание); децентрализованные социальные сети; IOTT (Interactive-Over-The-Top) - интерактивные сервисы поверх трансляций. Платформа Ace Stream обеспечивает непревзойдённое аудиовизуальное качество онлайн трансляций/показов в сети Интернет и самую эффективную систему хранения и доставки видео и аудио контента.
Кодирование информации Выражение данных одного типа через данные другого типа. В общем случае с помощью m битов можно закодировать N = 2 m понятий. Байт – восемь двоичных разрядов, служит основой адресации памяти. N = 28 = 256 кодов используется для кодирования текстовой информации.
Кодирование текстовой информации АSCII - код 1 сентября 2014 года 3120 E 1 A 5 ADE 2 EFA 1 E 1 EF 203230313420 A 3 AEA 4 A 0
Кодирование чисел и позиционные системы счисления X(s) = an-1 an-2…a 0, a-1 a-2…a-m = = an-1 sn-1+an-2 sn-2 +…+ a 0 s 0+a-1 s-1 +…+ a-ms-m s=2 ai = {0, 1} s=8 ai = {0, 1, 2, 3, 4, 5, 6, 7} s = 10 ai = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9} s = 16 ai = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, a, b, c, d, e, f} s – основание системы счисления ai – цифры в системе счисления n – количество целых разрядов числа Xs m – количество дробных разрядов числа Xs
Непозиционные системы счисления Римские цифры I X C M - 1 10 1000 V L D - 5 50 500 Примеры: XII 12 MM 2000 Mileniu. M ? MMMCMXCIX 3999 Max ?
Представление чисел в научном (экспоненциальном) формате Десятичное число 169, 3 -27, 8 0, 0017 -0, 0008 Нормальная форма 0, 1693× 103 -0, 278× 102 0, 17× 10 -2 -0, 8× 10 -3 Научный формат 0, 1693 Е 03 - 0, 278 Е 02 0, 17 Е-02 - 0, 8 Е-03
Домашнее задание № 2 1. Перевести год своего рождения из 10 -й в 2 -ю и 16 -ю систему счисления. 2. Перевести следующие числа из 2 -ой в 10 -ю систему счисления: 10101011 11011, 011 11, 11 (отрицательное число) 3. Перевести следующие числа из 10 -ой системы счисления в 2 -ю: 12345 -709, 625 1024, 14
Тема 3 Базы данных и SQL язык
Реляционные базы данных РБД – это связанная информация в виде двумерных таблиц Синонимы: строка = запись столбец = поле таблица = ( файл, отношение)
Особенности таблиц РБД w Порядок строк (записей) - произволен w Все столбцы (поля) – поименованы и пронумерованы w Каждая строка, каждой таблицы – уникальна w Столбец, который однозначно идентифицирует каждую строку называется – первичный ключ (primary key). w Primary key – обеспечивает целостность данных.
Характеристики и требования к СУБД 1. Производительность - время выполнения запросов - время выполнения операций импортирования БД из других форматов 2. Обеспечение целостности данных на уровне БД - встроенные средства для назначения первичного ключа - средства поддержания ссылочной целостности 3. Обеспечение безопасности - шифрование данных и программ - защита паролем - ограничение уровня доступа
Характеристики и требования к СУБД 4. Работа в многопользовательских средах - блокировка БД, файлов, записей, полей 5. Импорт - экспорт - возможность обработки СУБД информации, подготовленной другими программами и наоборот 6. Инструментальные средства разработки прикладного ПО - язык программирования - средства реализации меню, экранных форм, ввода-вывода и генерации отчетов 7. Доступ к данным с помощью SQL – Structured Query Language (Структурированный язык запросов). Используется при работе с реляционными БД во всех современных СУБД: Fox. Pro, Paradox, My. SQL, MS Access, IBM DB 2 SQL – стандарт ANSI, ISO архитектуры Файл-Сервер и Клиент-Сервер.
Централизованные базы данных Удаленный (сетевой) доступ – 2 классические архитектуры Файл - сервер Файл-сервер Передача файлов БД Рабочие станции Хранение файлов Клиент - сервер Обработка файлов Сервер СУБД Передача данных извлеченных из БД Рабочие станции СУБД – программный комплекс, предназначенный для реализации всей совокупности функций, связанных с созданием и эксплуатацией БД. БД
SQL запросы Запрос наиболее часто используемый аспект SQL. Есть USERs, которые только запросы (из всего языка SQL) и используют. Алгоритм выборки 1. 2. 3. 4. 5. 6. Рассмотрим строку таблицы. Проверим - является ли эта строка одной из строк которая нам нужна. Если да, сохраним ее где-нибудь пока вся таблица не будет проверена. Проверим имеются ли другие строки в таблице. Если имеются, возвратимся к шагу 1. Если строк больше нет, выведем все значения сохраненные на шаге 3.
Пример базы данных Таблица 1. Salespeople (Продавцы) snum sname city comm 1001 Peel London 0. 12 1002 Serres San Jose 0. 13 1004 Motika London 0. 11 1007 Rifkin Barcelona 0. 15 1003 Axelrod New York 0. 10 Таблица 3. Orders (Заказы) onum odate cnum snum 3001 18. 69 10/03/2008 1007 3003 767. 19 10/03/2008 2001 1001 3002 1900. 10 10/03/2008 2007 1004 3005 Таблица 2. Customers (Покупатели) amt 5160. 45 10/03/2008 2003 1002 3006 1098. 16 10/03/2008 1007 cnum cname city rating snum 3009 1713. 23 10/04/2008 2002 1003 2001 Hoffman London 1001 3007 75. 75 10/04/2008 2004 1002 2002 Giovanni Rome 200 1003 3008 4723. 00 10/05/2008 2006 1001 2003 Liu San Jose 200 1002 3010 1309. 95 10/06/2008 2004 1002 2004 Grass Berlin 300 1002 3011 9891. 88 10/06/2008 2006 1001 2006 Clemens London 1001 2008 Cisneros San Jose 300 1007 2007 Pireira Rome 1004
SQL запросы Команда SELECT наиболее часто используемый аспект SQL SELECT snum, sname, city, comm FROM Salespeople; snum sname city comm 1001 Peel London 0. 12 1002 Serres San Jose 0. 13 1004 Motika London 0. 11 1007 Rifkin Barcelona 0. 15 1003 Axelrod New York 0. 10 ==== SQL Execution Log ===== | SELECT snum, sname, sity, comm | | FROM Salespeople; | | ================= ==| | snum sname city comm | | ----------------| | 1001 Peel London 0. 12 | | 1002 Serres San Jose 0. 13 | | 1004 Motika London 0. 11 | | 1007 Rifkin Barcelona 0. 15 | | 1003 Axelrod New York 0. 10 | ================= SELECT * FROM Salespeople;
SQL запросы Выбор определенных столбцов snum sname city comm 1001 Peel London 0. 12 1002 Serres San Jose 1004 Motika London 1007 Rifkin Barcelona 1003 Axelrod New York = SQL Execution Log = | SELECT snum, comm | 0. 11 | FROM Salespeople; | 0. 15 | ========= = SQL Execution Log == | snum comm | 0. 10 | SELECT sname, сomm | | ------- | | FROM Salespeople; | | 1001 0. 12 | | =========| | 1002 0. 13 | | sname comm | 1004 0. 11 | | | ------- | | 1007 0. 15 | | Peel 0. 12 | | 1003 0. 10 | | Serres 0. 13 ========= | | Motika 0. 11 | | Rifkin 0. 15 | | Axelrod 0. 10 | ========== 0. 13
SQL запросы Перестановка столбцов snum sname city comm 1001 Peel London 0. 12 1002 Serres San Jose 0. 13 1004 Motika London 0. 11 1007 Rifkin Barcelona 0. 15 1003 Axelrod New York 0. 10 ===== SQL Execution Log ==== | SELECT odate, snum, onum, amt | | FROM Orders; | | ------------------------------| | odate snum onum amt | | ---------------- | | 10/03/2008 1007 3001 18. 69 | | 10/03/2008 1001 3003 767. 19 | | 10/03/2008 1004 3002 1900. 10 | | 10/03/2008 1002 3005 5160. 45 | | 10/03/2008 1007 3006 1098. 16 | | 10/04/2008 1003 3009 1713. 23 | | 10/04/2008 1002 3007 75. 75 | | 10/05/2008 1001 3008 4723. 00 | | 10/06/2008 1002 3010 1309. 95 | | 10/06/2008 1001 3011 9891. 88 | =================
SQL запросы Устранение избыточных строк = SQL Execution Log | SELECT snum | | FROM Orders; | | ----------------| | snum | | ------| | 1007 | | 1001 | | 1004 | | 1002 | | 1007 | | 1003 | | 1002 | | 1001 | ========= Ключевое слово DISTINCT ==== SQL Execution Log ==== | SELECT DISTINCT snum | | FROM Orders; | | ----------------------| | snum | | ------| | 1007 | | 1001 | | 1004 | | 1002 | | 1003 | =============
Домашнее задание № 3 1. Напишите команду SELECT, которая бы вывела номер заказа, сумму, и дату для всех строк из таблицы Orders. 2. Напишите запрос, который вывел бы все строки из таблицы Customers, с номером продавца = 1001. 3. Напишите запрос, который вывел бы таблицу со столбцами в следующем порядке: city, sname, snum, comm. 4. Напишите команду SELECT, которая вывела бы рейтинг (rating), сопровождаемый именем каждого покупателя в San Jose. 5. Напишите запрос, который вывел бы значения snum всех продавцов в текущем порядке из таблицы Orders без каких бы то ни было повторений.
SQL запросы Квалифицированный выбор строк Ключевое слово WHERE city = ‘London’ snum sname city comm 1001 Peel London 0. 12 1002 Serres San Jose 0. 13 1004 Motika London 0. 11 1007 Rifkin Barcelona 0. 15 1003 Axelrod New York 0. 10 Условие (предикат) ======= SQL Execution Log ======= | SELECT sname, city | | FROM Salespeople | | WHERE city = 'London' | | =============== | | sname city | | --------| | Peel London | | Motika London | ================
SQL запросы Квалифицированный выбор строк snum sname city comm 1001 Peel London 0. 12 1002 Serres San Jose 0. 13 1004 Motika London 0. 11 1007 Rifkin Barcelona 0. 15 1003 Axelrod New York 0. 10 Ключевое слово WHERE rating = 100 Условие ======== SQL Execution Log ===== | SELECT * | | FROM Customers | | WHERE rating = 100; | | ==================== | | сnum cname city rating snum | | -------------| | 2001 Hoffman London 1001 | | 2006 Clemens London 1001 | | 2007 Pereira Rome 1001 | =====================
SQL запросы Операторы отношения и логические операторы Операторы отношения (Реляционные операторы) Логические операторы (Булевы операторы) = - равно AND - логическое И > - больше OR - логическое ИЛИ < - меньше NOT - логическое НЕ >= - больше или равно <= - меньше или равно <> - не равно
SQL запросы Составное условие cnum cname city rating snum 2001 Hoffman London 1001 2002 Giovanni Rome 200 1003 2003 Liu San Jose 2004 Grass Berlin 300 1002 2006 Clemens London 1001 2008 Cisneros San Jose 300 1007 2007 Pireira Rome 1004 200 WHERE 1002 city = ‘San Jose’ AND rating > 200 Составное условие ====== SQL Execution Log ====== | SELECT * | | FROM Customers | | WHERE city = 'San Jose' | | AND rating > 200; | ==================== | сnum cname city rating snum | | -------------- | | 2008 Cirneros San Jose 300 1007 | ====================
SQL запросы Составное условие cnum cname city rating snum 2001 Hoffman London 1001 2002 Giovanni Rome 200 1003 2003 Liu San Jose 200 1002 2004 Grass Berlin 300 1002 2006 Clemens London 1001 2008 Cisneros San Jose 300 2007 Pireira Rome 100 city = ‘San Jose’ OR rating > 200 Составное условие ====== SQL Execution Log ====== 1007 | SELECT * | 1004 | FROM Customers | | WHERE city = 'San Jose' | | OR rating > 200; | | =================== | | сnum cname city rating snum | | -------------- | | 2003 Liu San Jose 200 1002 | | 2004 Grass Berlin 300 1002 | | 2008 Cirneros San Jose 300 1007 | ====================
SQL запросы Составное условие cnum cname city rating snum 2001 Hoffman London 1001 2002 Giovanni Rome 200 1003 2003 Liu San Jose 200 1002 2004 Grass Berlin 300 2006 Clemens London 100 2008 Cisneros San Jose 300 2007 Pireira Rome 1002 city = NOT ( ‘San Jose’ OR rating > 200 ) ====== SQL Execution Log ====== | 1001 SELECT * | | 1007 FROM Customers | | 1004 WHERE NOT ( city = 'San Jose' | | OR rating > 200); | | =================== | | сnum cname city rating snum | | -------------- | | 2001 Hoffman London 1001 | | 2002 Giovanni Rome 200 1003 | | 2006 Clemens London 1001 | | 2007 Pereira Rome 1004 | ====================
Домашнее задание № 4 6. Напишите запрос, который выведет все заказы со значениями суммы выше чем 1000. 7. Напишите запрос, который выведет поля sname и city для всех продавцов в Лондоне с комиссионными выше 0. 10. 8. Напишите запрос к таблице Покупатели, который определит всех покупателей с рейтингом =< 100, если они не в Риме. 9. Что может быть выведено в результате следующего запроса ? SELECT * FROM Orders WHERE NOT ((odate = 10/03/2008 OR snum > 1006) AND amt > = 1500 ); 10. Как можно проще переписать такой запрос ? SELECT snum, sname, city, comm FROM Salespeople WHERE ( comm > + 0. 12 OR comm < 0. 14 );
SQL запросы Специальные операторы для использования в условиях IN - в списке (перечне) BETWEEN - между LIKE - подобно (похоже) IS NULL - пусто (не определено)
SQL запросы Специальный оператор IN SELECT * FROM Salespeople WHERE city = 'Barcelona' OR city = 'London'; Проще SELECT * FROM Salespeople WHERE city IN ( 'Barcelona', 'London' );
SQL запросы Специальный оператор IN cnum cname city rating snum 2001 Hoffman London 1001 2002 Giovanni Rome 200 1003 2003 Liu San Jose 200 1002 2004 Grass Berlin 300 1002 2006 Clemens London 1001 2008 Cisneros San Jose 300 1007 2007 Pireira Rome 1004 WHERE snum IN ( 1001, 1007, 1004); ====== SQL Execution Log ====== | SELECT * | | FROM Customers | | WHERE snum IN ( 1001, 1007, 1004 ); | | =================== | | snum cname city rating snum | | ------------- | | 2001 Hoffman London 1001 | | 2006 Clemens London 1001 | | 2008 Cisneros San Jose 300 1007 | | 2007 Pereira Rome 1004 | ====================
SQL запросы Специальный оператор BETWEEN WHERE comm BETWEEN 0. 10 AND 0. 12; 0 snum sname city comm 1001 Peel London 0. 12 1002 Serres San Jose 0. 13 1004 Motika London 0. 11 1007 Rifkin Barcelona 0. 15 1003 Axelrod New York 0. 10 ==== SQL Execution Log ====== | SELECT * | | FROM Salespeople | | WHERE comm BETWEEN 0. 10 AND 0. 12; | | ==================| | snum sname city comm | | ---------------- | | 1001 Peel London 0. 12 | | 1004 Motika London 0. 11 | | 1003 Axelrod New York 0. 10 | ==================
SQL запросы Специальный оператор BETWEEN WHERE cname BETWEEN ‘A’ AND ‘G’; cnum cname city rating snum 2001 Hoffman London 1001 2002 Giovanni Rome 200 1003 2003 Liu San Jose 200 1002 2004 Grass Berlin 300 2006 Clemens London 100 2008 Cisneros San Jose 300 2007 Pireira Rome 1002 ====== SQL Execution Log ====== | SELECT * | 1001 | FROM Customers | 1007 | WHERE cname BETWEEN 'A' AND 'G'; | 1004 | =================== | | cnum cname city rating snum | | ------------- | | 2006 Clemens London 1001 | | 2008 Cisneros San Jose 300 1007 | ====================
SQL запросы Специальный оператор LIKE Имеются два типа групповых символов используемых с LIKE: символ подчеркивания ( _ ) замещает любой одиночный символ. • Например, 'b_t' будет соответствовать словам 'bat' или 'bit', но не будет соответствовать 'brat'. знак процента (%) замещает последовательность любого числа символов. • Например '%p%t' будет соответствовать словам 'put', 'posit', или 'opt', но не 'spite'.
SQL запросы Специальный оператор LIKE (примеры) WHERE cname LIKE ‘P__L%'; WHERE cname LIKE 'G%'; snum сname city comm cnum cname city rating snum 1001 Peel London 0. 12 2001 Hoffman London 1001 1002 Serres San Jose 0. 13 2002 Giovanni Rome 200 1003 1004 Motika London 0. 11 2003 Liu San Jose 200 1002 1007 Rifkin Barcelona 0. 15 2004 Grass Berlin 300 1002 1003 Axelrod New York 0. 10 2006 Clemens London 1001 = SQL Execution Log = | SELECT cname | | FROM Salespeople | | WHERE cname | | LIKE ‘P__L%'; | | ========== | cname | | -----| | Peel | ========== SQL Execution Log ====== 2008 300 1007 | SELECTCisneros San Jose * | 2007 Pireira Rome 1004 | FROM Customers | | WHERE cname LIKE 'G%'; | | =================== | | cnum cname city rating snum | | ------------- | | 2002 Giovanni Roma 200 1003 | | 2004 Grass Berlin 300 1002 | ====================
SQL запросы Специальный оператор IS NULL (примеры) SELECT * FROM Customers WHERE city IS NULL; ====== SQL Execution Log ====== | SELECT * | | FROM Customers | | WHERE city NOT NULL; | | =================== | cnum snum 2001 1001 2002 2003 1003 2004 1002 2006 1002 2008 1001 2007 cnum cname city rating 2001 Hoffman London 100 2002 Giovanni Rome 2003 Liu San Jose 2004 Grass Berlin 300 2006 Clemens London 100 2008 Cisneros San Jose 300 1007 2007 Pireira Rome 1004 cname Hoffman Giovanni Liu Grass Clemens Cisneros Pireira city London Rome San Jose Berlin London San Jose Rome rating 100 200 300 100 snum 1001 1003 1002 1001 1007 1004
Домашнее задание № 5 11. Напишите два запроса, которые могли бы вывести все заказы на 3 и 4 Октября 2008. 12. Напишите запрос, который выберет всех заказчиков, обслуживаемых продавцами Peel или Motika. ( Подсказка: поле snum связывает вторую таблицу с первой ). 13. Напишите запрос, который может вывести всех заказчиков, чьи имена начинаются с буквы попадающей в диапазон от A до G. 14. Напишите запрос, который выберет всех покупателей чьи имена начинаются с буквы C. 15. Напишите запрос который выберет все заказы, имеющие нулевые значения или NULL в поле amt (сумма).
SQL запросы Агрегатные функции COUNT - подсчитывает число не нулевых значений поля выбранных в запросе строк SUM - вычисляет арифметическую сумму всех выбранных значений данного (числового) поля AVG - вычисляет среднее значение всех выбранных значений данного (числового) поля MAX - находит максимальное из всех выбранных значений данного поля MIN - находит минимальное из всех выбранных значений данного поля
SQL запросы Агрегатные функции SUM и AVG (примеры) onum amt odate cnum snum 3001 18. 69 10/03/2008 1007 3003 767. 19 10/03/2008 2001 1001 3002 1900. 10 10/03/2008 2007 1004 3005 5160. 45 10/03/2008 2003 1002 3006 1098. 16 10/03/2008 1007 3009 1713. 23 10/04/2008 2002 1003 3007 75. 75 10/04/2008 2004 1002 3008 4723. 00 10/05/2008 2006 1001 3010 1309. 95 10/06/2008 2004 1002 3011 9891. 88 10/06/2008 2006 1001 = SQL Execution Log = | SELECT SUM (amt) | | FROM Orders | | ========== | -----| | 26658. 4 | ========== = SQL Execution Log = | SELECT AVG (amt) | | FROM Orders | | ========== | ----| | 2665. 84 | ==========
SQL запросы Агрегатная функция COUNT (примеры) cnum cname city rating snum onum amt odate cnum snum 2001 Hoffman London 1001 3001 18. 69 10/03/2008 1007 2002 Giovanni Rome 200 1003 3003 767. 19 10/03/2008 2001 1001 2003 Liu San Jose 200 1002 3002 1900. 10 10/03/2008 2007 1004 2004 Grass Berlin 300 1002 3005 5160. 45 10/03/2008 2003 1002 2006 Clemens London 1001 3006 1098. 16 10/03/2008 1007 2008 Cisneros San Jose 300 1007 3009 1713. 23 10/04/2008 2002 1003 2007 Pireira Rome 1004 3007 75. 75 10/04/2008 2004 1002 3008 4723. 00 10/05/2008 2006 1001 = SQL Execution Log = | SELECT COUNT (*) | | FROM Customers | | ========== | -----| | 7 | ========== SQL Execution 10/06/2008 2004 Log ====== 3010 1309. 95 | SELECT COUNT 9891. 88 10/06/2008 2006| (DISTINCT snum) 3011 | FROM Orders | | =============== | | -----| | 5 | ================ 1002 1001
SQL запросы Вспомогательные операторы GROUP BY - позволяет определять подмножества значений отдельного поля и применять функции агрегирования к полученному подмножеству HAVING - определяет критерий, по которому определенные группы строк исключаются из числа выходных данных
SQL запросы Вспомогательный оператор GROUP BY (пример) onum amt odate cnum snum 3001 18. 69 10/03/2008 1007 3003 767. 19 10/03/2008 2001 1001 3002 1900. 10 10/03/2008 2007 1004 3005 5160. 45 10/03/2008 2003 1002 3006 1098. 16 10/03/2008 1007 3009 1713. 23 10/04/2008 2002 1003 3007 75. 75 10/04/2008 2004 1002 3008 4723. 00 10/05/2008 2006 1001 3010 1309. 95 10/06/2008 2004 1002 3011 9891. 88 10/06/2008 2006 1001 ==== SQL Execution Log ===== | SELECT snum, MAX (amt) | | FROM Orders | | GROUP BY snum; | | ============= | | snum | | --------| | 1001 9891. 88 | | 1002 5160. 45 | | 1003 1713. 23 | | 1004 1900. 10 | | 1007 1098. 16 | ============== Максимальная сумма продажи у каждого продавца
SQL запросы Вспомогательный оператор HAVING (пример) ===== SQL Execution Log ======= |SELECT snum, odate, MAX (amt) | | FROM Orders | | GROUP BY snum, odate; | |============== | | snum odate | | -----------| | 1001 10/03/2008 767. 19 | | 1001 10/05/2008 4723. 00 | | 1001 10/06/2008 9891. 88 | | 1002 10/03/2008 5160. 45 | | 1002 10/04/2008 75. 75 | | 1002 10/06/2008 1309. 95 | | 1003 10/04/2008 1713. 23 | | 1004 10/03/2008 1900. 10 | | 1007 10/03/2008 1098. 16 | =============== SQL Execution Log ======= |SELECT snum, odate, MAX (amt) | | FROM Orders | | GROUP BY snum, odate | | HAVING MAX(amt) > 3000. 00; | |============== | | snum odate | | -----------| | 1001 10/05/2008 4723. 00 | | 1001 10/06/2008 9891. 88 | | 1002 10/03/2008 5160. 45 | ===============
Домашнее задание № 6 16. Напишите запрос который сосчитает число всех заказов, сделанных 3 октября. 17. Напишите запрос который сосчитал бы число различных не-NULL значений поля city в таблице Покупателей. 18. Напишите запрос который выбрал бы наименьшую сумму для каждого покупателя. 19. Напишите запрос который бы выбирал покупателей в алфавитном порядке, чьи имена начинаются с буквы G. 20. Напишите запрос который выбрал бы высший рейтинг в каждом городе.
SQL запросы Форматирование результатов (отчеты) Использование выражений (*, /, +, -) Вставка комментариев (‘…’) Упорядочивание строк (ORDER BY, ASC, DESC, по номеру столбца)
SQL запросы Форматирование результатов (примеры) snum city comm 1001 Peel London 0. 12 1002 Serres San Jose 0. 13 1004 Motika London 0. 11 1007 ===== SQL Execution Log ======== | SELECT snum, sname, city, comm * 100, ‘%’ | | FROM Salespeople; | |===================| | snum sname city | | --------------- ---| | 1001 Peel London 12. 00 % | | 1002 Serres San Jose 13. 00 % | | 1004 Motika London 11. 00 % | | 1007 Rifkin Barcelona 15. 00 % | | 1003 Axelrod New York 10. 00 % | ==================== сname Rifkin Barcelona 0. 15 1003 Axelrod New York 0. 10
SQL запросы Форматирование результатов (примеры) onum amt odate cnum snum 3001 18. 69 10/03/2008 1007 3003 767. 19 10/03/2008 2001 1001 3002 1900. 10 10/03/2008 2007 1004 3005 5160. 45 10/03/2008 2003 1002 3006 1098. 16 10/03/2008 3009 1713. 23 10/04/2008 2002 3007 75. 75 10/04/2008 2004 3008 4723. 00 10/05/2008 2006 3010 1309. 95 10/06/2008 2004 3011 9891. 88 10/06/2008 2006 ====== SQL Execution Log ===== 1007 | SELECT ‘На', odate, ', ' сделано заказов ' , | | 1003 | COUNT (DISTINCT onum), ' шт ' | 1002 | FROM Orders | GROUP BY odate; | 1001 |=================== | 1002 | odate | 1001 | ------------ ------ | | На 10/03/2008 , сделано заказов 5 шт. | | На 10/04/2008 , сделано заказов 2 шт. | | На 10/05/2008 , сделано заказов 1 шт. | | На 10/06/2008 , сделано заказов 2 шт. | ====================
SQL запросы Форматирование результатов (примеры) onum amt odate cnum snum 3001 18. 69 10/03/2008 1007 3003 767. 19 10/03/2008 2001 3002 1900. 10 10/03/2008 2007 3005 5160. 45 10/03/2008 2003 3006 1098. 16 10/03/2008 3009 1713. 23 10/04/2008 2002 3007 75. 75 10/04/2008 2004 3008 4723. 00 10/05/2008 2006 3010 1309. 95 10/06/2008 2004 3011 9891. 88 10/06/2008 2006 ===== SQL Execution Log ====== | SELECT * FROM Orders | 1001 | ORDER BY cnum DESC, amt DESC; | 1004 |=================== 1002 | onum amt odate cnum snum | 1007| ------------------ | 1098. 16 10/03/2008 1007 | 1003| 3006 18. 69 10/03/2008 1007 | 1002| 3001 | 3002 1900. 10 10/03/2008 2007 1004 | 1001 | 3011 9891. 88 10/06/2008 2006 1001 | 1002 | 3008 4723. 00 10/05/2008 2006 1001 | 1001| 3010 1309. 95 10/06/2008 2004 1002 | | 3007 75. 75 10/04/2008 2004 1002 | | 3005 5160. 45 10/03/2008 2003 1002 | | 3009 1713. 23 10/04/2008 2002 1003 | | 3003 767. 19 10/03/2008 2001 1001 | ====================
SQL запросы Форматирование результатов (примеры) snum sname city comm 1001 Peel London 0. 12 1002 Serres San Jose 0. 13 1004 Motika London 0. 11 1007 Rifkin Barcelona 0. 15 1003 Axelrod New York 0. 10 ==== SQL Execution Log ====| | SELECT sname, comm | | FROM Salespeople | | ORDER BY 2 DESC; | | ============| | sname comm | | -------| | Rifkin 0. 15 | | Serres 0. 13 | | Peel 0. 12 | | Motika 0. 11 | | Axelrod 0. 10 | =============
Домашнее задание № 7 21. Предположим что каждый продавец имеет 12% комиссионных. Напишите запрос к таблице Заказов, который мог бы вывести номер заказа, номер продавца, и сумму комиссионных продавца для этого заказа. 22. Напишите запрос к таблице Покупателей, который мог бы найти высший рейтинг в каждом городе. Вывод должен быть в такой форме: Для города (city), высший рейтинг: (rating). 23. Напишите запрос, который выводил бы список покупателей в нисходящем порядке. Вывод поля рейтинга ( rating ) должен сопровождаться именем заказчика и его номером. 24. Напишите запрос который бы выводил общие рейтинги на каждый день и помещал результаты в нисходящем порядке.
SQL запросы Объединение таблиц cnum cname city rating snum 2001 Hoffman London 1001 2002 Giovanni Rome 200 1003 2003 Liu San Jose 200 1002 2004 Grass Berlin 300 1002 2006 Clemens London 1001 2008 Cisneros San Jose 300 1007 2007 Pireira Rome 1004 snum sname city comm 1001 Peel London 0. 12 1002 Serres San Jose 0. 13 1004 Motika London 0. 11 1007 Rifkin Barcelona 0. 15 1003 Axelrod New York 0. 10 Задача. Поставить в соответствие каждому продавцу его покупателей в городе, в котором они живут. ==== SQL Execution Log ===== | SELECT Customers. cname, | | Salespeople. sname, Salespeople. city | | FROM Salespeople, Customers | | WHERE Salespeople. city = Customers. city | | ================== | | cname sname city | | -----------------| | Hoffman Peel London | | Liu Serres San Jose | | Cisneros Serres San Jose | | Hoffman Motika London | | Clemens Motika London | ==================
SQL запросы Объединение более двух таблиц cnum cname city rating snum 2001 Hoffman London 1001 2002 Giovanni 2003 Liu 2004 Grass 2006 Clemens 2008 Cisneros 2007 Pireira snum sname 1001 Peel 1002 Serres 1004 Motika 1007 Rifkin 1003 Axelrod Найти все заказы покупателей не находящихся в тех городах, где находятся их продавцы. ======== SQL Execution Log ====== === Rome 200 | SELECT 1003 , cname, Orders. cnum, Orders. snum onum | San Jose | FROM Salespeople, Customers, Orders 200 1002 | | Berlin | WHERE Customers. city < > Salespeople. city 300 1002 | London | AND Orders. cnum = Customers. cnum 1001 | AND Orders. snum = Salespeople. snum; | San Jose 300 1007 | ======================= | Rome 100 | onum 1004 cname cnum snum | | ----------| city comm | 3001 Cisneros 2008 1007 | London 0. 12 | 3002 Pereira 2007 1004 | San Jose 0. 13 | 3006 Cisneros 2008 1007 | London | 3009 0. 11 Giovanni 2002 1003 | Grass 2004 1002 | Barcelona | 3007 0. 15 Grass 2004 1002 | New York | 3010 0. 10 ========================
SQL запросы Подзапросы onum amt odate cnum snum 3001 18. 69 10/03/2008 1007 3003 767. 19 10/03/2008 2001 1001 3002 1900. 10 10/03/2008 2007 1004 3005 5160. 45 10/03/2008 2003 1002 3006 1098. 16 10/03/2008 1007 3009 1713. 23 10/04/2008 2002 1003 3007 75. 75 10/04/2008 2004 1002 3008 4723. 00 10/05/2008 2006 1001 3010 1309. 95 10/06/2008 2004 1002 snum 3011 sname 9891. 88 city 10/06/2008 comm 1001 2006 1001 Peel London 0. 12 1002 Serres San Jose 0. 13 1004 Motika London 0. 11 1007 Rifkin Barcelona 0. 15 1003 Axelrod New York 0. 10 Задача. Известно только имя продавца Motika. Надо узнать все выполненные им заказы из таблицы Orders ====== SQL Execution Log ==== | SELECT * | | FROM Orders | | WHERE snum = | | (SELECT snum | | FROM Salespeople | | WHERE sname = 'Motika'); | |===================| | onum amt odate cnum snum | | -------------- | | 3002 1900. 10 10/03/2008 2007 1004 | ===================
SQL запросы Объединение запросов Используется специальный оператор UNOIN snum sname city comm 1001 Peel London 0. 12 1002 Serres San Jose 0. 13 1004 Motika London 0. 11 1007 Rifkin Barcelona 0. 15 1003 Axelrod New York 0. 10 cnum cname city rating snum 2001 Hoffman London 1001 2002 Giovanni Rome 200 1003 2003 Liu San Jose 200 1002 2004 Grass Berlin 300 1002 2006 Clemens London 1001 2008 Cisneros San Jose 300 1007 2007 Pireira Rome 1004 === SQL Execution Log === | SELECT snum, sname | | FROM Salespeople | | WHERE city = 'London' | | UNION | | SELECT cnum, cname | | FROM Customers | | WHERE city = 'London'; | ============ | -------| | 1001 Peel | | 1004 Motika | | 2001 Hoffman | | 2006 Climens | =============
Домашнее задание № 8 25. Напишите запрос формирующий список номеров заказов сопровождающихся именем покупателя, который делал эти заказы. 26. Напишите запрос, который бы выдавал имена продавца и покупателя для каждого заказа после номера заказа. 27. Напишите запрос, который бы выводил всех покупателей обслуживаемых продавцом с комиссионными выше 12%. Выведите имя покупателя, имя продавца, и ставку комиссионных продавца. 28. Напишите запрос который вычислил бы сумму комиссионных продавца для каждого заказа покупателя с рейтингом выше 100.
SQL команды модификации DML Data Manipulate Language INSERT ВСТАВИТЬ UPDATE МОДИФИЦИРОВАТЬ DELETE УДАЛИТЬ
SQL команды модификации (примеры) Примеры вставки строк INSERT INTO Salespeople VALUES (1001, 'Peel', 'London', 0. 12); INSERT INTO Salespeople VALUES (1005, ‘Bogner', ‘Adelaida', 0. 11); INSERT INTO Customers (city, cnamе, cnum) VALUES ('London', 'Honman', 2001); Примеры удаления строк DELETE FROM Salespeople WHERE snum = 1003; DELETE FROM Salespeople; DROP TABLE; Примеры модификации строк UPDATE Customers SET rating = 200 WHERE snum = 1001; UPDATE Customers SET sum = 1004 WHERE snum = 1002;
SQL команды создания и удаления объектов БД DDL Data Definision Language CREATE TABLE СОЗДАТЬ ТАБЛИЦУ DROP TABLE УДАЛИТЬ ТАБЛИЦУ CREATE INDEX СОЗДАТЬ ИДЕКС DROP INDEX УДАЛИТЬ ИНДЕКС CREATE VIEW СОЗДАТЬ ПРЕДСТАВЛЕНИЕ
SQL команды создания и удаления объектов (примеры) Добавочные ограничения CREATE TABLE Salespeople ( snum integer, NOT NULL PRIMARY KEY sname char (10), NOT NULL UNIQUE city char (10), comm declmal ); CREATE UNIQUE INDEX Custid ON Customers (cnum); DROP INDEX имя индекса; DROP TABLE имя таблицы;
SQL команды создания объектов (примеры) CREATE VIEW Londonstaff AS SELECT * FROM Salespeople WHERE City = ‘London’ Представление Londonstaff snum sname city comm 1001 Peel London 0. 12 1004 Motika London 0. 11
SQL команды предоставления стандартных привилегий GRANT ПРЕДОСТАВИТЬ REVOKE ЛИШИТЬ Что? Чего? SELECT, UPDATE, INSERT, DELETE, ALL
SQL команды предоставления стандартных привилегий (примеры) Предоставление кому-то чего-то: GRANT SELECT ON Customers TO Marie; GRANT UPDATE (city, comm) ON Salespeople TO Dima; GRANT ALL PRIVILEGES ON Customers TO George; Предоставление всем чего-то: GRANT SELECT ON Orders TO PUBLIC; Лишение кого-то чего-то: REVOKE INSERT, DELETE ON Customers FROM George;
Скачать презентацию СПб ГУТ им проф М А Бонч-Бруевича Технологии
01__ТОИ_ВВЕДЕНИЕ__.ppt