Базы данных и PHP.

ЛЕКЦИЯ № 5 WEB-технологии и компью терное моделирование Краткий учебны й курс 1 Структура учебного курса  Лекция 1. Введение в интернет технологии и компьютерное моделирование.  Лекция 2. Создание WEB страниц с использованием HTML.  Лекция 3. Работа с графикой в Adobe Photoshop и Flash CS.  Лекция 4. Создание динамических WEB страниц с использованием JavaScript и PHP.  Лекция 5. Базы данны х и PHP.  Лекция 6. Пример реализации «Эконометрической модели экономики России» под WEB.  Лекция 7. Основы компьютерного моделирования с использованием Powersim и AnyLogic. 2 Введение в БАЗЫ ДАННЫХ База данны х - это набор информации, организованной тем, или иным способом. Классическая реляционная модель данных требует, чтобы данные хранились в так называемых плоских таблицах. Фактически база данных – это один или несколько файлов, содержащих определенную информацию, представленную в табличном виде. Классический пример – база данных MS Access (файл *.mdb) С Базами данных можно взаимодействовать через различные интерфейсы, в том числе через WEB. Для этого существуют различные средства (например, с помощью PHP, ASP, JSP, С# и др.) и технологии (ADO, JDBC, ODBC и др.). Реляционная модель основана на теории множеств и математической логике. Для доступа к реляционным данным используется язык SQL (Structured Query Language - язы к структурированны х запросов). 3 Введение в БАЗЫ ДАННЫХ Операции над множ ест вами Основны ми операциями над множествами являю тся объединение, пересечение и разность. Определение 1. Объединением двух множеств назы вается новое множество Определение 2. Пересечением двух множеств назы вается новое множество Определение 3. Разностью двух множеств назы вается новое множество Если класс объектов, на которы х определяются различны е множества обозначить  , то дополнением множества A назы вают разность 4 Введение в БАЗЫ ДАННЫХ Таблица «Заказы » (orders) Таблица «Клиенты » (clients) ID2 ID1 Goods ID1 Family Name OLD 1 1 Bred 1 Jonson Jon 18 2 1 cheese 2 Hamilton Bob 30 3 1 biff 4 2 Chicken 5 2 tomato Между таблицами в реляционной СУБД могут бы ть заданы различны е отношения, например «один к одному», «один ко многим», «многие ко многим» и др. 5 Введение в БАЗЫ ДАННЫХ Язы к SQL Основу языка SQL составляют операторы, условно разбитые не несколько групп по выполняемым функциям: Операторы DDL (Data Definition Language) - операторы определения объектов базы данных CREATE SCHEMA - создать схему базы данных DROP SHEMA - удалить схему базы данных CREATE TABLE - создать таблицу ALTER TABLE - изменить таблицу DROP TABLE - удалить таблицу CREATE DOMAIN - создать домен ALTER DOMAIN - изменить домен DROP DOMAIN - удалить домен CREATE COLLATION - создать последовательность DROP COLLATION - удалить последовательность CREATE VIEW - создать представление DROP VIEW - удалить представление 6 Введение в БАЗЫ ДАННЫХ Язы к SQL Операторы DML (Data Manipulation Language) - операторы манипулирования данными SELECT - отобрать строки из таблиц INSERT - добавить строки в таблицу UPDATE - изменить строки в таблице DELETE - удалить строки в таблице COMMIT - зафиксировать внесенные изменения ROLLBACK - откатить внесенные изменения Операторы защиты и управления данны ми CREATE ASSERTION - создать ограничение DROP ASSERTION - удалить ограничение GRANT - предоставить привилегии пользователю или приложению на манипулирование объектами REVOKE - отменить привилегии пользователя или приложения 7 Введение в БАЗЫ ДАННЫХ Язы к SQL Примеры использования операторов манипулирования данны ми (DML) Пример 1. Выбрать все данные из таблицы «Клиенты» SELECT * FROM clients; Пример 2. Выбрать все данные из таблицы «Клиенты», где возраст клиентов > 20 SELECT * FROM clients WHERE clients.old >20 ; Пример 3. Выбрать все данные из таблицы «Заказы» для клиента Jon Jonson. SELECT * FROM orders WHERE orders.ID1 = (SELECT ID1 FROM clients WHERE clients.name = ‘Jon’ AND clients.family=‘Jonson’); (вложение запросов) 8 Введение в БАЗЫ ДАННЫХ Язы к SQL (DML) Пример 4. Вставка нового клиента в таблицу clients INSERT INTO clients (ID1, Name, Family) VALUES (3, “Иван”, "Иванов"); Пример 5. Обновление данных в таблице clients. Изменение имени и фамилии третьего клиента. UPDATE clients SET Name = ‘Петр’, Family = ‘Петров’ WHERE ID1 = 3; Пример 6. Удаление третьего клиента из таблицы clients. DELETE FROM clients WHERE ID1=3 Обратите внимание. Если у третьего клиента уже есть заказы, т.е. записи в таблице orders с ID1=3, то их тоже нужно удалить из таблицы orders (каскадное удаление данны х) 9 Введение в БАЗЫ ДАННЫХ Язы к SQL (DML) Пример 7. Формирование перечня клиентов сделавших заказы. SELECT * FROM clients WHERE clients.ID1 = (SELECT DISTINCT ID1 FROM orders); Обратите внимание. DISTINCT применяется для выборки уникальных (не повторяющихся) значений ID1 из таблицы orders. В противном случае записи по клиентам будут дублироваться Пример 8. Селекция всех клиентов с именем, начинающимся на J SELECT * FROM clients WHERE clients.name LIKE ‘J%’ Пример 9. Селекция всех клиентов с ID1 = 1 и 3. Использование IN для перечисления множества. SELECT * FROM clients WHERE clients.ID1 IN (1, 3); Пример 10. Селекция всех клиентов с ID1 между 3 и 10 SELECT * FROM clients WHERE clients.ID1 BEETWEEN (3, 10); 10 Введение в БАЗЫ ДАННЫХ Язы к SQL (DML) Пример 11. Допустим у Вас есть две таблицы - новые и старые заказы: old_orders new_orders ID2 ID1 Goods ID2 ID1 Goods 1 1 Bred 1 1 coffee 2 1 Cheese 2 2 Chicken 3 2 tomato Тогда, что бы получить список всех заказов (и новы х и стары х) нужно вы полнить запрос SELECT * FROM old_orders UNION Объ единение множества запросов в один SELECT * FROM new_orders; 11 Введение в БАЗЫ ДАННЫХ Язы к SQL (DML) Пример 12. Внутренние соединения. Внутреннее соединение возвращает только те строки, для которых условие соединения принимает значение true. Например, SELECT * FROM old_orders, new_orders WHERE new_orders.ID1 = old_orders.ID1; Результат запроса из двух таблиц old_orders.ID2 old_orders.ID1 old_orders.Goods new_orders.ID2 new_orders.ID1 new_orders.Goods 1 1 Bred 1 1 coffee 2 1 Cheese 1 1 coffee Обратите внимание. В результат попали данные из двух таблиц, для которых ID1=1. Потому что, данных с ID1 = 2 нет в old_orders 12 Введение в БАЗЫ ДАННЫХ Язы к SQL (DML) Пример 13. Внешние соединения. В отличие от внутренних соединений, внешние соединения возвращают все строки из одной таблице и и только те строки из другой таблицы, для которых условие соединения принимает значение true. Строки второй таблицы, не удовлетворяющие условию соединения (т.е. имеющие значение false), получают значение null в результирующем наборе. Существует два вида внешнего соединения: LEFT JOIN и RIGHT JOIN. В левом соединении (LEFT JOIN) запрос возвращает все строки из левой таблицы (т.е. таблицы, стоящей слева от зарезервированного словосочетания “LEFT JOIN”) и только те из правой таблицы, которые удовлетворяют условию соединения. Если же в правой таблице не найдется строк, удовлетворяющих заданному условию, то в результате они замещаются значениями null. 13 Введение в БАЗЫ ДАННЫХ Язы к SQL (DML) Пример 13. Внешние соединения. Пример. SELECT * FROM new_orders LEFT JOIN old_orders ON new_orders.ID1 = old_orders.ID1; Результат new_orders.ID2 new_orders.ID1 new_orders.Goods old_orders.ID2 old_orders.ID1 old_orders.Goods 1 1 coffee 2 1 Cheese 1 1 coffee 1 1 Bred 2 2 Chicken 3 2 tomato Обратите внимание. В результат попали все данные из таблицы new_orders и данные из таблицы old_orders для которых ID1=1, т.е. Cheese и Bred. 14 Введение в БАЗЫ ДАННЫХ Язы к SQL (DML) Пример 14. Сортировка записей в порядке возрастания, например по ID1 SELECT * FROM new_orders ORDER BY ID1 Пример 16. Сортировка записей в порядке убывания, например по ID1 SELECT * FROM new_orders ORDER BY ID1 DESC Пример 17. Группировка записей, например по ID1 с сортировкой SELECT ID1, Goods FROM new_orders GROUP BY ID1, Goods ORDER BY ID1 Пример 18. Подсчет количества записей в таблице new_orders SELECT count(*) FROM new_orders Пример 19. Вычисление максимального значения ID1 в таблице new_orders SELECT MAX(ID1) FROM new_orders 15 Введение в БАЗЫ ДАННЫХ Язы к SQL (DDL) Пример1. Эта команда будет создавать таблицу Клиенты . CREATE TABLE Clients ( ID1 integer, Name varchar(50), Family varchar(50)); Пример2. Эта команда уничтожит таблицу Клиенты . DROP TABLE Clients Пример.3 Создание таблицы Клиенты c первичны м ключом ID1. CREATE TABLE Clients ( ID1 INTEGER NOT NULL PRIMARY KEY, Name varchar(50), Family varchar(50)); 16 Введение в БАЗЫ ДАННЫХ Язы к SQL (DDL для СУБД MySQL) Пример.4 Создание таблицы Заказы c автоматически инкрементируемы м первичны м клю чом ID1 для СУБД MySQL. CREATE TABLE orders ( ID2 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, ID1 INTEGER NOT NULL, Goods varchar(50), FOREIGN KEY (ID1) REFERENCES Clients(ID1)); Обратите внимание. Использование ключевого слова AUTO_INCREMENT PRIMARY KEY, поддерживаемого именно MySQL, позволяет автоматически увеличивать значение ID2 при вставке новой записи. В свою очередь использование FOREIGN KEY позволяет ограничить вставку записей в таблицу orders по клиентам, которых еще нет в таблице Clients Orders Clients ID1 Family Name OLD 1 Jonson Jon 18 2 Hamilton Bob 30 ID2 ID1 Goods 1 1 Bred 2 1 cheese 3 1 biff 4 2 Chicken www.mysql.ru 17 Введение в БАЗЫ ДАННЫХ Язы к SQL (DDL для СУБД MS SQL Server) Пример.4 Создание таблицы Заказы для СУБД MS SQL Server. CREATE TABLE orders ( ID2 INTEGER IDENTITY (1, 1) NOT NULL PRIMARY KEY, ID1 INTEGER NOT NULL FOREIGN KEY (ID1) REFERENCES Clients(ID1), Goods varchar(50)); Обратите внимание на важнейшие отличия с MySQL: 1) Использование IDENTITY (1, 1) вместо AUTO_INCREMENT; Запись FOREIGN KEY сразу вместе с определением атрибута ID1. Orders Clients ID1 Family Name OLD 1 Jonson Jon 18 2 Hamilton Bob 30 ID2 ID1 Goods 1 1 Bred 2 1 cheese 3 1 biff 4 2 Chicken Обращайте внимание на различия синтаксиса DDL для различны х СУБД! 18 Введение в БАЗЫ ДАННЫХ Язы к SQL (DDL для СУБД MS SQL Server) Пример.5 Создание таблицы Заказы для СУБД MS SQL Server. CREATE TABLE orders ( ID2 INTEGER IDENTITY (1, 1) NOT NULL PRIMARY KEY, ID1 INTEGER NOT NULL FOREIGN KEY (ID1) REFERENCES Clients(ID1), Goods varchar(50)); Обратите внимание на важнейшие отличия с MySQL: 1) Использование IDENTITY (1, 1) вместо AUTO_INCREMENT; Запись FOREIGN KEY сразу вместе с определением атрибута ID1. Orders Clients ID1 Family Name OLD 1 Jonson Jon 18 2 Hamilton Bob 30 ID2 ID1 Goods 1 1 Bred 2 1 cheese 3 1 biff 4 2 Chicken Обращайте внимание на различия синтаксиса DDL для различны х СУБД! 19 Введение в БАЗЫ ДАННЫХ Язы к SQL (некот оры е особенност и MS SQL) Пример.5 В MS SQL Server, в частности, возможна массовая вставка файлов из *.txt файлов с помощью специальной команды T-SQL . BULK INSERT Clients В таблицу клиенты FROM ‘clients.txt' вставляем данны е из WITH ( clients.txt CODEPAGE='1251', FIELDTERMINATOR = ';', ROWTERMINATOR = '\n'); Язы к Transact-SQL является клю чом к использованию SQL Server. Все приложения, взаимодействующие с экземпляром SQL Server, независимо от их пользовательского интерфейса отправляют серверу инструкции Transact-SQL. Подробную документацию по MS SQL Server можно посмотреть здесь : http://technet.microsoft.com/ru-ru/library/default.aspx http://msdn.microsoft.com/ru-ru/library/ 20 МНОГОМЕРНЫЕ БАЗЫ ДАННЫХ Схема «звезды », схема звёздного соединения, звездоподобная схема, звёздная схема (от англ. star schema) — специальная организация реляционных таблиц, удобная для хранения многомерных показателей. Лежит в основе реляционного OLAP. Таблицы измерений Таблицы измерений Таблица фактов 21 БАЗЫ ДАННЫХ и PHP Пример 1. Создание соединения и вы бор базы данны х для MySQL 22 БАЗЫ ДАННЫХ и PHP Пример 2. Создание соединения и вы бор базы данны х для MSSQL Обратите внимание на различия MYSQL и MSSQL Server 23 БАЗЫ ДАННЫХ и PHP Обратите внимание. Что бы обеспечить работу на PHP c различны ми СУБД (например, MS SQL Server, Oracle и др.), Вам нужно предварительно сконфигурировать файл C:\Windows\PHP.INI PHP.INI ……………………………………………………………………………… ; Directory in which the loadable extensions (modules) reside. extension_dir = " C:\PHP\ext“ ……………………………………………………………………………. ;extension=php_dbase.dll extension=php_mssql.dll extension=php_mysql.dll ……………………………………………………………………………. Обратите внимание. Cимвол ‘;’ – это комментарий. Убрав его Вы обеспечиваете работу соотв. строки в файле php.ini 24 БАЗЫ ДАННЫХ и PHP Пример 3. Создание новой таблицы в MS SQL с помощью PHP " ); ?> 25 БАЗЫ ДАННЫХ и PHP Пример 4. Заполнение таблицы в MS SQL с помощью PHP данны ми из файла 26 БАЗЫ ДАННЫХ и PHP Фрагмент файла data2.txt 01.07.2001;1;29.33;25.89;175.98;82.124;102.2;2193.884;12.7;1088.143;28.3;926.828; 01.07.2001;2;29.33;25.89;205.31;46.928;102.2;2229.08;12.6;1132.138;26.6;941.493; 01.07.2001;3;29.33;25.89;190.645;64.526;102.2;2193.45;12.6;1097.1;27.5;927.7; 01.07.2001;4;29.33;25.89;284.501;87.99;102.2;2190.951;12.7;1173.2;28.6;941.493; 01.10.2001;1;31;15;305;105;102.83;2543.18;13.45;1378.74;29.44;1091.46; Обратите внимание. Если файл с данны ми большой (например, имеет ты сячи записей и более), то лучше использовать массовую загрузку данны х командой BULK INSERT. Это будет намного бы стрее. 27 БАЗЫ ДАННЫХ и PHP Пример 5. Вы полнение запроса к базе по заданному критерию = ‘2008-01-01’" ; $zapr1=mssql_query($strSQL1); // YYYY-MM-DD $idx=1; while($r1 = mssql_fetch_array($zapr1)) { $E_1[$idx] = $r1[" E" ]; // Считы ваем значения переменны х из Базы в массивы переменны х $O_1[$idx] = $r1[" O" ]; $GT_1[$idx] = $r1[" GT" ]; $TR_1[$idx] = $r1[" TR" ]; $P_1[$idx] = $r1[" P" ]; $Y_1[$idx] = $r1[" Y" ]; $M_1[$idx] = $r1[" M" ]; $N_1[$idx] = $r1[" N" ]; $X_1[$idx] = $r1[" X" ]; $CO_1[$idx] = $r1[" CO" ]; $idx++; } // Осуществляем различны е манипуляции с массивами переменны х $E_1[$idx], $O_1[$idx] и др. ?> 28 БАЗЫ ДАННЫХ и PHP Обратите внимание. Здесь значения переменны х берутся из Базы Данны х 29 БАЗЫ ДАННЫХ и PHP Для представления данны х из Базы на графике можно использовать библиотеку PHP – GD или Flash библиотеку Обратите внимание. Данны е из Базы Данны х можно представить на графике. 30 БАЗЫ ДАННЫХ и PHP Существуют программы - “ билдеры ” для ускорения процесса разработки веб-страниц, взаимодействую щих с базами данны х: 1) PHP Report Maker 2) ASP.NET Maker Любая СУБД 3) Crystal Reports XI - Мощная система отчетов интегрируемы х с WEB 4) Oracle Application Express – вклю чает мощны й построитель WEB страниц для СУБД Oracle (PL/SQL) 31