 |
|
 |
 |
 |
 |
|
|
Базы данных MySQL, язык запросов.
База данных – информационно-программный комплекс позволяющий хранить и обрабатывать информацию. База данных MySQL оптимизирована для работы в Интернете и является стандартом де-факто. Принцип работы базы данных следующий: программа (PHP) обращается к базе данных при помощи специального языка SQL, а база возвращает программе результат запроса в виде таблицы (массива). Информация в базе данных хранится виде таблиц.
Таблицы состоят из полей (столбцов) и записей (строк). Поля имеют название (латинские буквы) и вся информация, находящаяся в одном поле одного типа (строка, число). Запись включает в себя информацию из всех полей таблицы (т.е мы всегда знаем, что Иванов – директор, а Сидоров - мастер). Поля таблицы могут быть следующего типа:
Кроме этого в каждой таблице должен присутствовать ключ- специальное поле с уникальными (неповторяющимися) значениями. Это требуется для однозначного определения записи. Например, если у нас в таблице имеются два Егорова и оба слесари, то отличить их программа сможет только по полю id – идентификатору, поскольку цифры в нём не повторяются. Такое поле называется ключевым и обычно в таблице носит название id. Оно обязательно должно иметь атрибут – уникальное. Для того, чтобы не приходилось отслеживать программно уникальность каждой новой вставляемой записи, MySQL позволяет применить атрибут поля – автоинкрементный, Это значит что при вставке новой записи полю id будет присвоено значение на единицу больше чем предыдущее. Вывод таблицы осуществляется следующим образом: Листинг 3: 1 <?php 2 $server="localhost"; //имя сервера 3 $user="root"; //имя пользователя 4 $password=""; //пароль 5 $baza="baza"; //имя базы данных 6 $connect=mysql_pconnect($server,$user,$password); //соединение с сервером 7 mysql_select_db($baza,$connect); //выбор базы данных 8 $query _str="SELECT * FROM table"; //строка запроса 9 $query=mysql_query($query _str,$connect); //выполнение запроса 10 $query_count=mysql_num_rows($query); //вычисление количества строк в запросе 11 ?> 12 <html> 13 <head> 14 <title>Мой документ</title> 15 </head> 16 <body> 17 <?php 18 for ($i=1; $i<=$query_count; $i++) //начало цикла 19 {$query_row=mysql_fetch_assoc($query); //выбор записи из таблицы 20 echo $query_row["id"]." ".$query_row["name"]."<br>"; //вывод поля id и поля name 21 }; //конец цикла 22 ?> 23 </body> 24 </html>
Строки 2-5, задаются переменные с именами сервера, пользователя, пароль, и база данных. Команда mysql_pconnect($server, $user, $password); в строке 6 возвращает идентификатор соединения, по которому сервер баз данных однозначно определяет текущего пользователя (в один момент может соединиться с базой данных произвольное количество пользователей с одинаковыми именами и паролями, поэтому необходимо их различать) мы присваиваем переменной $connect значение этого идентификатора. Строка 7 даём команду серверу mysql_select_db($baza, $connect); что позволяет выбрать определенную базу данных (на сервере баз может быть несколько). Переменной $query_str присвоим строковое значение которое будет являться запросом SQL к базе данных. Выполним запрос командой mysql_query($query _str, $connect); которая возвратит результат запроса (в данном случае - таблицу) в виде ассоциативного массива запишем результат в переменную $query. Вычисляем количество строк в запросе и присваиваем это значение переменной $query_count=mysql_num_rows($query); Строки 18-21 это цикл, повторяющийся $query_count раз. При каждом проходе цикла выполняется команда mysql_fetch_assoc($query); которая возвращает одну запись из запроса в виде массива, где индексами являются имена полей. При первом выполнении этой команды внутренний указатель запроса находится на первой строке, при выполнении команды, указатель переводится на одну строку ниже. Присваиваем результат работы команды переменной $query_row. Выводим значение строки при помощи команды echo обращаясь к полям $query_row[‘имя_поля’]. Подобным образом осуществляется вывод любого запроса.
Запросы Существует четыре типа запросов SELECT, INSERT, UPDATE, DELETE - выборки, вставки, изменения и удаления соответственно.
SELECT Запрос на выборку в общем случае имеет следующий синтаксис: SELECT имена полей FROM имена таблиц WHERE условие отбора GROUP BY имена полей для группировки ORDER BY имена полей, по которым осуществляется сортировка (ASC – в порядке возрастания, DESC – убывания) LIMIT начальная_запись, количество_записей
Некоторые из команд необязательны. В листинге был использован запрос «SELECT * FROM table» знак * обозначает все поля. Если мы захотим вывести таблицу сотрудников (см. выше) которая называется sotrudniki надо будет написать следующий запрос:
Выборка «SELECT * FROM sotrudniki» если нам нужны только поля name и dolznost, то запишем «SELECT sotrudniki.name, sotrudniki.dolznost FROM sotrudniki» или если используется только одна таблица «SELECT name, dolznost FROM sotrudniki» Возможно создание псевдонимов для таблиц и полей например: «SELECT s.name, s.dolznost FROM sotrudniki s» в операторе FROM после имени таблицы через пробел записан её псевдоним s и далее она используется уже по псевдониму, также возможны псевдонимы для полей: «SELECT name n, dolznost d FROM sotrudniki» или «SELECT s.name n, s.dolznost n FROM sotrudniki s»
Сортировка «SELECT name, dolznost FROM sotrudniki ORDER BY name» запрос отсортирует таблицу по фамилии сотрудников (если поле сортировки – числовое, то оно сортируется в порядке убывания или возрастания чисел, а если строковое - то в алфавитном порядке) «SELECT name, dolznost FROM sotrudniki ORDER BY zarplata» запрос отсортирует записи по зарплате (причем заметьте, что поле зарплата не выводится, поскольку в операторе SELECT указываются только для вывода, а сортировку и вообще любые действия можно производить с любым существующим полем) «SELECT name, dolznost, zarplata FROM sotrudniki ORDER BY zarplata DESC» то же самое но в порядке убывания и поле зарплаты будет доступно для вывода
Условия Кроме всего перечисленного можно выводить в запросе только записи удовлетворяющие какому-либо условию. «SELECT name, dolznost, zarplata FROM sotrudniki WHERE zarplata>=5000» или «SELECT name, dolznost, zarplata FROM sotrudniki WHERE dolznost=”слесарь”» также как и в PHP строки пишутся в кавычках. кроме того для строк возможно частичное совпадение «SELECT name, dolznost, zarplata FROM sotrudniki WHERE dolznost LIKE “%слес%”» этот запрос выберет те записи, в которых в поле dolznost встречается подстрока «слес» (заметьте, что LIKE, нестрогое соответствие пишется вместо знака «=») условие может быть применено к нескольким полям «SELECT name, dolznost, zarplata FROM sotrudniki WHERE dolznost LIKE “%слес%” AND zarplata<=5000» в условии применяются стандартные логические операторы AND, OR, NOT, скобки «(» «)» и тд.
Группировка Группировка означает, что повторяющиеся значения полей используемых в группировке будут объединены в одно, и станет возможным применить к ним некоторые статистические функции.
«SELECT name, dolznost, zarplata, COUNT(dolznost) dolzn_count FROM sotrudniki GROUP BY dolznost» Запрос возвратит количество записей, равное количеству неповторяющихся должностей, псевдоним dolzn_count возвратит количество сотрудников, с соответствующей должностью. ВНИМАНИЕ в данном запросе поле name не имеет смысла, поскольку одной должности соответствует несколько сотрудников и запрос выведет в этом поле первую попавшуюся фамилию, из списка фамилий соответствующих данной должности. Коме этого можно вычислить среднюю, минимальную, максимальную и сумму зарплат сотрудников определенной должности. например: AVG(zarplata) sr_zarplata – средняя зарплата в данной должности.
Ограничение Оператор LIMIT позволяет ограничивать количество выводимых запросом.
INSERT
Запрос позволяет добавлять записи в таблицу
INSERT INTO имя_таблицы(поле1, поле2,..) VALUES (значение1, значение2,..)
Возможно добавление не всех полей, а только указанных в запросе. В этом случае остальные поля заполнятся значениями по умолчанию, автоинкрементно или будет установлен NULL.
«INSERT INTO sotrudniki (name, dolznost, zarplata) VALUES (”Иванов”, ”Директор”, 6000)»
UPDATE
Позволяет редактировать одну или несколько записей, удовлетворяющих условию WHERE.
UPDATE имя_таблицы SET поле1=значение1, поле2=значение2,... WHERE условие
Для редактирования определенной записи удобно пользоваться уникальным полем ID.
«UPDATE sotrudniki SET zarplata=5999 WHERE name=”Иванов” AND dolznost=”Директор”»
DELETE
Запрос позволяет удалять одну или несколько записей из таблицы
DELETE FROM имя_таблицы WHERE условие
«DELETE FROM sotrudniki WHERE name=”Иванов”»
Если использовать DELETE без WHERE, то таблица будет очищена.
Нормализация таблиц
Возьмём в качестве примера таблицу:
Мы можем увидеть повторяющиеся элементы в поле должности, давайте вынесем эту информацию в отдельную таблицу dolznost:
а в предыдущей заменим название должности номером id таблицы dolznost получим таблицу sotrudniki:
Таким образом мы получаем возможность уменьшить размер таблицы. При количестве записей в несколько сот тысяч результат сокращения размеров таблицы получатся весьма существенным. Кроме этого сортировка и выборка по числовому полю осуществляется намного быстрей. Например запрос: «SELECT * FROM sotrudniki WHERE dolznost=4» выполнится намного быстрей чем «SELECT * FROM sotrudniki WHERE dolznost=”слесарь”»
Но главное преимущество заключается в удобном построении каталогов и иерархических структур. Процедура выноски избыточной информации из одной таблицы в другую называется нормализацией таблиц.
Связанные таблицы
В работе не удобно
оперировать цифрами или номерами идентификаторов, поэтому такие таблицы
связывают при помощи языка запросов
Для удобства таблицы изображают следующим образом:
Возьмем две наши таблицы из примера sotrudniki и dolznost видно, что они связаны по полю должность таким образом, что значению sotrudniki.id_dolznost соответствует dolznost.id Изобразим эту связь следующим образом:
Данная связь называется “Один ко многим”, это значит, что одной записи в таблице dolznost соответствует несколько записей в таблице sotrudniki. На языке запросов это выглядит следующим образом: «SELECT * FROM sotrudniki, dolznost WHERE sotrudniki.id_dolznost=dolznost.id» Результат:
Это значит, что мы приравниваем поля таблиц и таким образом связываем их по идентификатору должности. На самом деле второго поля id не будет, а будет поле id2. Чтобы избежать подобного недоразумения необходимо использовать псевдонимы для полей. Необходимо понять логику работы запроса. Если мы используем в запросе две таблицы, но не укажем условия отбора WHERE то результатом выполнения станет таблица со всеми полями этих двух таблиц и числом записей равным произведению записей этих таблиц, т.е. все возможные варианты. Значит в этом случае Иванов будет и директором и бухгалтером и мастером и слесарем. Получаем на одну фамилии четыре записи. Но если мы укажем, что нас интересует только запись, где sotrudni.id_dolznost=dolznost.id, т.е если в записи Иванов поле id_dolznost имеет значение 1 то из таблицы dolznost будет выбрана только та запись которая соответствует условию id=1 из поля должность этой записи мы получим значение «Директор». Необходимо отметить, что условию могут удовлетворять несколько записей, кроме этого в запросе можно использовать и другие условия при помощи логических операторов. Например, добавим в таблицу sotrudniki поле подразделение (цех). С целью нормализации в это поле будем заносить число которое равняется идентификатору в таблице podrazdelenie. Аналогично предыдущему примеру, получим:
Запрос будет следующим: «SELECT * FROM sotrudniki, dolznost, podrazdelenie WHERE sotrudniki.id_dolznost=dolznost.id AND sotrudniki.id_podrazdelenie=podrazdelenie.id» Здесь использована две связь «многие ко многим» для таблиц должность и сотрудники, поскольку в одном подразделении могут быть и мастер и слесарь, и в свою очередь слесари могут присутствовать в нескольких подразделениях. Подобная связь всегда осуществляется через третью таблицу.
Совокупность таблиц, объединенных связью называется базой данных.
|
|
|
Copyright © 2004 by inet-studio
|