Глава 17. Установка и настройка MySQL. Связка Apache + PHP + MySQL.

Ни один серьезный интернет-проект нельзя построить без использования баз данных. Большинство провайдеров предоставляет хостинг вместе с одним из серверов баз данных. Самым популярным из таких серверов считается MySQL, получивший широкое распространение благодаря своей простоте. Здесь я не буду ни рассматривать технические характеристики MySQL, ни сравнивать его с другими серверами баз данных (InterBase Server, IBM DB/2, Oracle) — достаточно сказать, что InterBase Server или Oracle более масштабируемы и поэтому лучше подходят для организации распределенной системы обработки информации, но для обычного интернет-проекта MySQL подходит практически идеально. В этой главе я опишу его установку, настройку и использование.

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

Прежде всего нужно установить пакеты, необходимые для работы MySQL. У меня MySQL версии 4.0.15, поэтому я установил такие пакеты (номера версий у вас, возможно, будут другими, и я их обозначил символами «x»):

♦ MySQL_GPL-4.x.x.

♦ MySQL_GPL-client-4.x.x.

♦ MySQL_GPL-shared-libs-4.x.

♦ MySQL_GPL-bench-4.x.x.

♦ MySQL_GPL-resolveip-4.x.x.

После установки пакетов нужно создать системную базу данных mysql, содержащую таблицы db, host и user. Скорее всего, она уже создана, но, чтобы окончательно убедиться в этом, введите команду:

# mysql_install_db.

17.1.1. Назначение пароля суперпользователя.

Учетные записи и пароли всех пользователей, которые имеют право работать с сервером, содержатся в таблице user. Сразу же после создания базы mysql в эту таблицу внесен только один пользователь — root. По умолчанию он не имеет пароля. Этот пароль нужно установить немедленно: не нужно объяснять, как это важно для безопасности системы. Для изменения пароля запустите сервер командой:

# safe_mysqld &

Эта команда запустит сервер в режиме демона и освободит консоль. Если все пакеты были установлены правильно, вы увидите сообщение:

Mysql: ready for connections.

Затем введите команду:

$ mysql -u root mysql.

Эта команда запускает клиент MySQL (MySQL-монитор) и соединяется с сервером от имени пользователя root, даже если вы работаете под другой учетной записью. Последний аргумент указывает базу данных, которую требуется открыть.

SQL-запросы можно набирать в строке приглашения MySQL-монитора, заканчивая ввод точкой с запятой или командой \g (go), а можно редактировать в текстовом редакторе, введя команду \е (edit). Список команд MySQL-монитора можно получить по команде \h (help).

Измените пароль суперпользователя с помощью следующего запроса:

UPDATE user SET Password=PASSWORD('новый_пароль') WHERE.

User='root';

Как вы заметили, это обычный SQL-запрос, обновляющий поле Password таблицы user для пользователя root. При вводе запроса обратите внимание на регистр названий полей: сервер MySQL различает прописные и строчные буквы!

Теперь нужно, чтобы MySQL принял изменения. Для этого выполните еще один запрос SQL:

FLUSH PRIVILEGES;

Завершите сеанс работы с MySQL-монитором, введя команду \q (quit). В следующий раз зарегистрироваться на сервере без пароля вы уже не сможете. Теперь нужно запускать MySQL-монитор следующей командой:

$ mysql -u root -p mysql.

Ключ -p запросит при регистрации пароль. Имейте в виду: забытый пароль нельзя восстановить. Единственный выход из этого положения — удалить каталог /var/lib/mysql/mysql и создать базу mysql заново командой mysql_install_db.

Для принятия изменений можно также использовать программу mysqladmin с аргументом reload. Вызвать программу можно так:

$ mysqladmin -p reload.

Параметр -p вам обязательно нужно использовать, так как вы только что установили пароль для пользователя root.

Установите права доступа к сценарию /etc/rc.d/init.d/mysqld:

# chmod +x /etc/rc.d/init.d/mysqld.

Теперь можете перезапустить сервер командой.

# /etc/rc.d/init.d/mysqld restart.

17.1.2. Автозапуск сервера MySQL.

Последнее, что вам осталось сделать — это добавить сервер MySQL в автозапуск. С этой целью перейдите в каталог /etc/rc.d/rc3.d и создайте символическую ссылку на файл /etc/rc.d/init.d/mysql:

# ln -s S14mysql /etc/rc.d/init.d/mysql.

Префикс S14 определяет очередность запуска сервера mysqld. В данном случае он запустится после сервисов network (S10) и portmap (S11). У вас эти значения могут быть другими.

17.1.3. Пользователи сервера MySQL и их права.

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

GRANT ALL PRIVILEGES ON *.* TO admin@localhost IDENTIFIED BY.

'пароль' WITH GRANT OPTION;

Введенный вами запрос создаст пользователя admin, который будет иметь право выполнять любые операции со всеми базами данных. Этот пользователь будет иметь право подключаться к серверу с компьютера localhost, используя пароль.

Маска *.* определяет, к каким базам данных и таблицам имеет право подключаться тот или иной пользователь. Первая звездочка определяет базу, а вторая — таблицу. Если вам нужно, чтобы пользователь admin имел право подключаться с любого узла, используйте знак процента вместо имени компьютера:

GRANT ALL PRIVILEGES ON *.* TO admin@"%" IDENTIFIED BY.

'пароль' WITH GRANT OPTION;

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

GRANT CREATE,DROP,SELECT,INSERT,UPDATE,DELETE,INDEX ON.

User.* TO user@% IDENTIFIED BY 'пароль';

Этот запрос позволяет пользователю user выполнять все операции с его базой данных.

Полный список полномочий представлен в таблице 17.1.

Полномочия пользователей сервера MySQL Таблица 17.1.

Полномочия	Описание
SELECT, INSERT, UPDATE, DELETE	Право просматривать, добавлять, модифицировать, удалять данные в таблицах базы данных
INDEX	Право производить операции с индексами таблиц
REFERENCES	Право работать со ссылками в базах данных и таблицах
CREATE, DROP	Право создавать и удалять таблицы и базы данных
GRANT, ALTER	Право определять полномочия
RELOAD, SHUTDOWN, PROCESS	Право перезагружать, останавливать сервер и просматривать все процессы (подключения)

Если запрос GRANT у вас не работает, то вы можете внести пользователя непосредственно в таблицу user базы данных mysql. Структура таблицы user выглядит следующим образом:

Host User Password Select_priv Insert_priv Update_priv.

Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv.

Process_priv File_priv.

Поля Host, User, Password — это, соответственно, узел, из которого пользователь может получить доступ, имя и пароль пользователя. Все остальные поля задают полномочия. Если выполнение какой-нибудь операции разрешено пользователю, значение поля должно быть равным «Y», в противном случае — «N».

Например, нам нужно создать пользователя admin, который должен иметь все полномочия. Это можно сделать с помощью такого запроса SQL:

INSERT INTO user(Host,User,Password,Select_priv,Insert_priv,

Update_priv, Delete_priv, Create_priv, Drop_priv,

Reload_priv, Shutdown_priv, Process_priv, File_priv).

VALUES ('localhost', 'admin', password('4td561s12'),

'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

Для создания обыкновенного пользователя используйте следующий запрос:

INSERT INTO user(Host, User, Password, Select_priv,

Insert_priv, Update_priv, Delete_priv) VALUES('%',

'user', password('123456') , 'Y' , 'Y' , 'Y' , ' Y');

17.2. Клиентская часть MySQL.

Удобной программой для просмотра структуры базы данных является mysqlshow. Введите следующую команду:

$ mysqlshow -p mysql.

В ответ вы увидите список таблиц, которые находятся в базе данных mysql.

Программа mysqlshow может вызываться с дополнительными ключами, перечисленными в таблице 17.2.

Ключи программы mysqlshow Таблица 17.2.

Ключ	Назначение
--host=имя_узла	Задает имя узла, где работает сервер MySQL, к которому вы хотите подключиться
--port=номер_порта	Определяет номер порта для сервера MySQL
--socket=сокет	Указывает сокет
--user=имя_пользователя	Указывает, под каким именем зарегистрироваться на сервере MySQL
-p	Запрашивает ввод пароля

Для самих же операций с данными используется программа mysql. Она понимает те же ключи, что и mysqlshow, и много других, среди которых очень полезный ключ -s. Я рекомендую вам всегда его использовать. Этот ключ подавляет большинство ненужных сообщений, выводимых MySQL-клиентом, что существенно повышает производительность на медленных линиях связи.

17.3. Установка PHP и настройка связки Apache+PHP+MySQL.

Сейчас мы произведем не только установку PHP, которая не вызывает особых проблем, но и настройку связки Apache+PHP+ MySQL. Эту связку, очень полезную при создании веб-проектов, можно настроить двумя способами. Первый — использование программ, которые входят в состав дистрибутива и, как правило, устанавливаются из пакетов RPM.

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

17.3.1. Первый способ: из пакетов RPM.

Первую часть связки в дистрибутивах Red Hat выше 7.2 и Mandrake выше 8.1 настраивать не нужно: все настраивается во время установки системы. Поэтому вы можете сразу приступить к тестированию связки Apache+PHP (листинг 17.1).

Убедитесь, что сервер Apache установлен и корректно функционирует:

$ lynx http://localhost.

Текстовый браузер lynx должен отобразить стартовую страницу Apache. После успешной проверки работы сервера остановите его командой:

# /etc/init.d/httpd stop.

Проверьте наличие библиотеки gd — она необходима для работы с графикой в PHP:

$ rpm -qa | grep gd.

$ rpm -ihv gd-1.8.4-4.i386.rpm.

Загрузить последнюю версию PHP 5 можно по адресу http://www.php.net/downloads.php.

Скачайте ( www.php.net/downloads-php) и установите пакет php, если вы его еще не установили. Вам также понадобится пакет php-mysql, обеспечивающий поддержку сервера MySQL языком PHP, и модуль Apache, обеспечивающий поддержку PHP (пакет mod_php).

Затем в файле httpd.conf раскомментируйте следующую строчку. После этого файлы с расширением .php будут правильно обрабатываться сервером:

AddType application/x-httpd-php4 .php.

Теперь можно проверять правильность настройки двух компонент связки: Apache и PHP.

Напомню, что в большинстве современных серверных дистрибутивов сервер Apache уже установлен вместе с модулем mod_php, поэтому выполнять вышеизложенные шаги совсем необязательно, достаточно только проверить корректность работы модуля mod_php.

Для проверки работы модуля mod_php создайте тестовый файл test.php с таким содержимым:

Листинг 17.1. Файл test.php.

<?

 phpinfo();

?>

Этот файл сохраните в каталоге DocumentRoot сервера Apache. Обычно это каталог /var/www/html. Затем запустите любой браузер и введите адрес http://localhost/test.php. Вы должны увидеть в окне браузера сведения о PHP, сервере Apache и других компонентах и библиотеках (рис. 17.1).

Рис. 17.1. Функция phpinfo().

Функция phpinfo() очень информативна: внимательно изучив информацию, которую она предоставляет, вы много узнаете о своем веб-сервере.

Теперь немного настроим PHP. С помощью функции phpinfo() узнайте, где расположен инициализационный файл PHP. Обычно он называется php.ini и находится в каталоге /etc. Откройте этот файл в любом текстовом редакторе и раскомментируйте следующую строку, убедившись, что в вашей системе есть файл mysql.so (он устанавливается при установке php-mysql):

Extension=mysql.so.

После этого перейдите в секцию MySQL файла php.ini и установите параметры сервера MySQL по умолчанию:

Mysql.default_port =

Mysql.default_socket =

Mysql.default_host = localhost.

Mysql.default_user =

Mysql.default_password =

Эти параметры будут использоваться при установлении соединения с сервером, если в функциях PHP они не будут явно указаны. Никогда не указывайте пользователя root (а тем более его пароль) в качестве пользователя по умолчанию!

Теперь можно приступить к настройке сервера MySQL. Имеет смысл использовать версию MySQL не ниже 3.23, поскольку в этой версии появилась нормальная поддержка транзакций. Если вы устанавливаете MySQL версии 3.23 или выше, то установите еще пакет mod_auth_mysql, обеспечивающий базовую аутентификацию для сервера Apache с использованием таблиц MySQL.

При добавлении сервера MySQL в сценарии загрузки (/etc/rc.d/) обратите внимание на то, что сервер MySQL должен быть запущен ПЕРЕД сервером Apache.

17.3.2. Тестируем созданную конфигурацию.

Теперь можно проверить работу всей связки Apache + PHP+MySQL. С этой целью создайте небольшой тестовый файл mysql_test.php в каталоге DocumentRoot ( /var/www/html):

Листинг 17.2. Файл mysql_test.php.

<?

 // Используется имя пользователя root и пароль password.

 if(!mysql_connect("localhost","root","password")).

 {

  echo "He могу соединиться с сервером\n";

  echo mysql_error();

  exit;

 }

 echo "Работает!".

?>

Как вы уже успели догадаться, если в окне браузера вы увидите слово «Работает!», значит, вы все сделали правильно.

17.3.3. Второй способ: из исходных текстов.

У этого способа есть свои преимущества. Во-первых, у вас появится возможность использовать самые последние версии серверов Apache, MySQL и интерпретатора PHP, которых нет в составе даже самого нового дистрибутива Linux. Во-вторых, вы сами сможете контролировать процесс сборки и включать поддержку необходимых вам функций, исключив такую ситуацию, когда, например, разработчики пакетов RPM при сборке интерпретатора PHP забыли включить поддержку сервера MySQL, Мне попадался такой пакет php: функции mysql_connect() в нем просто не было.

Скачайте из Интернета последние версии Apache, MySQL и PHP. Предварительно удалите из системы старые версии, если они были установлены. После загрузки распакуйте исходные тексты в каталог /src.

Сначала установите сервер MySQL. С этой целью перейдите в каталог с исходными текстами MySQL и введите следующие команды (первая команда включает поддержку по умолчанию кодировки koi8-r):

# ./configure --with-charset=koi8_ru.

# make.

# make install.

Затем аналогично установите Apache. Для получения информации обо всех возможных ключах сценария configure введите команду configure --help.

После этого распакуйте PHP и соберите его следующим образом:

# ./configure --with-mysgl --with-apache=../apache_2.0.0 --with-mod_charset.

# make.

# make install.

Первая команда конфигурирует интерпретатор PHP для работы с сервером баз данных MySQL и веб-сервером Apache, Естественно, вы должны правильно указать путь к исходным текстам Apache с помощью ключа --with-apache.

Затем вернитесь в каталог, содержащий исходные тексты Apache, и введите команду:

# ./configure --activate-module=src/modules/php4/libphp4.a.

Перед этим нужно убедиться в существовании файла libphp4.a (если php собрался успешно, этот файл должен существовать). Если сценарий configure успешно завершил свою работу, введите команды make и make install.

Проверить, подключился ли модуль libphp, вы можете после установки сервера с помощью команды:

# httpd -l.

В списке модулей должен быть модуль libphp4.c, а также модуль mod_charset.c — его вы подключили при первой сборке. После этого можно отредактировать файл /etc/php.ini и установить пароль для пользователя root сервера MySQL (не путайте пользователя root всей системы с пользователем root сервера MySQL!).

Теперь только остается добавить запуск серверов в сценарии автозагрузки системы. Напомню, что сервер MySQL должен запускаться до сервера Apache.

17.4. Защита сервера MySQL.

По умолчанию для файла сокета mysql.sock, который используется соединениями сервера MySQL, установлены права доступа 0777. Это означает, что кто угодно может удалить этот файл. Если данный файл будет удален во время работы сервера, то ни один пользователь не сможет подключиться к серверу MySQL. Установите «бит прилипчивости» (sticky-бит) для каталога /var/lib/mysql, чтобы предотвратить удаление файлов из этого каталога:

# chmod +t /var/lib/mysql.

Из соображений безопасности рекомендуется удалить базу данных test, которая создается при установке сервера и используется для его проверки:

# mysqladmin drop test -p.

Не забудьте также удалить запись, соответствующую базе данных test, из таблицы db базы данных mysql:

# mysql -u root mysql -p.

Mysql> DELETE * FROM Db WHERE Db="test";

Mysql> DELETE * FROM Db WHERE Db="test\_%";

Mysql> quit.

17.5. Введение в язык SQL.

17.5.1. Общие понятия.

Если вы раньше работали с какой-нибудь СУБД (Система Управления Базой Данных), вы смело можете пропустить этот пункт — ничего нового для себя вы не прочитаете. Но если вы сталкиваетесь с СУБД впервые, без основных терминов вам не обойтись.

♦ Поле (field) — это неделимый элемент данных в БД. Поле имеет имя и тип. Подробнее о типах полей мы поговорим чуть позже.

♦ Запись (record) — набор полей, содержащих связанную информацию. Например, запись с полями C_No, C_Name и C_Address содержит информацию о клиенте — его номер, имя и адрес.

♦ Таблица (table) — это набор записей одинаковой структуры. Если у нас есть запись структуры C_No, C_Name, C_Address, то все записи в таблице Clients будут иметь такую структуру.

♦ База данных (database) — это совокупность связанных таблиц. Например, в одной таблице может храниться информация о клиенте, а в другой — информация о заказе, который сделал клиент.

♦ Индекс (index) — используется для быстрого поиска нужной записи в базе данных. Обычно поиск производится по значению одного поля или по значению нескольких полей.

♦ Первичный индекс (index) — управляет порядком отображения записей в таблице. Поле первичного индекса должно быть уникальным, то есть в одной таблице не должно быть двух записей, в которых это поле принимает одно и то же значение. В нашей таблице Clients, очевидно, первичный индекс должен строиться по полю C_No — по номеру клиента, который должен быть уникален.

♦ Вторичный индекс (secondary index) — в отличие от первичного индекса может строиться по нескольким полям и не обязан быть уникальным. Вторичные индексы используются для связывания таблиц. Индексы также называются ключами.

♦ Запрос (query) — оператор, выбирающий записи и поля, удовлетворяющий заданному условию, из одной или нескольких таблиц.

17.5.2. Краткий практический курс SQL.

Как работает сервер SQL? Клиент посылает запрос, в котором указывает, какую информацию хочет получить от сервера или какую операцию с данными собирается выполнить. В ответ сервер посылает клиенту ответ, в котором указывает, выполнил ли сервер его запрос, и, если выполнил, сообщает результат запроса.

Для описания запросов клиента был разработан целый язык — SQL (Structured Query Language, Структурированный язык запросов). С помощью запросов SQL вы можете:

♦ Создавать базы данных и таблицы,

♦ Добавлять информацию в таблицы.

♦ Удалять информацию,

♦ Модифицировать информацию.

♦ Получать нужные вам данные.

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

Перед началом работы с SQL вам нужно интуитивно понимать, что такое база данных. Если вы имеете представление о ней, можете смело пропустить этот абзац. База данных состоит из таблиц, как книга MS Excel состоит из листов. Каждая таблица состоит из записей, а каждая запись — из полей. Каждое поле имеет свой домен, то есть тип данных, которые можно записать в это поле. Поле типа INT может содержать только целые числа, а поле типа CHAR — строки.

Вот теперь можно приступать к созданию новой базы данных. Для создания баз данных и таблиц в языке SQL обычно используется запрос CREATE. В случае с MySQL для создания базы нужно использовать программу mysqladmin:

$ mysqladmin -u admin -p create sklad.

Естественно, пользователь admin должен существовать и обладать соответствующими правами. Откройте созданную базу:

$ mysql -u admin -p sklad.

Каждый запрос MySQL должен заканчиваться точкой с запятой. Если вы введете SELECT * FROM test, клиент mysql будет ждать ввода точки с запятой:

->

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

SELECT *

FROM S.

WHERE Q > 10.

В программе mysql можно записать так:

SELECT * FROM S WHERE Q > 10.

Теперь создадим три таблицы — Товар, Клиенты и Заказы.

CREATE TABLE CLIENTS (

 C_NO    int      NOT NULL,

 FIO     char(40) NOT NULL,

 ADDRESS char(30) NOT NULL,

 CITY    char(15) NOT NULL,

 PHONE   char(11) NOT NULL.

);

Таблица CLIENTS содержит поля C_NO (номер клиента), FIO (Фамилия, Имя, Отчество), Адрес, Город и Телефон. Все эти поля не могут содержать пустого значения (NOT NULL).

Примечание. Большинство серверов не требуют явного указания NOT NULL, но при этом значение по умолчанию может быть разным: одни сервер инициализируют столбцы значением NULL, а другие — NOT NULL, Поэтому лучше явно указать NOT NULL.

CREATE TABLE TOVAR (

 T_NO  int          NOT NULL,

 DSEC  char(40)     NOT NULL,

 PRICE numeric(9,2) NOT NULL,

 QTY   numeric(9,2) NOT NULL.

);

Эта таблица будет содержать данные о товарах. Тип numeric (9,2) означает, что 9 знаков отводятся под целую часть и два — под дробную, QTY — это количество товара на складе.

CREATE TABLE ORDERS (

 O_NO     int          NOT NULL,

 DATE     date         NOT NULL,

 C_NO     int          NOT NULL,

 T_NO     int          NOT NULL,

 QUANTITY numeric(9,2) NOT NULL,

 AMOUNT   numeric(9,2) NOT NULL.

);

Эта таблица содержит сведения о заказах — номер заказа (O_NO), дату заказа (DATE), номер клиента (C_NO), номер товара (T_NO), количество (QUANTITY) и стоимость заказа AMOUNT.

Теперь добавим данные в наши таблицы. Добавить данные можно с помощью оператора INSERT:

INSERT INTO CLIENTS.

VALUES (1,'Иванов К.П.', 'Ленина 6', 'Кировоград','80522111111');

Добавляемые значения должны соответствовать тому порядку, в котором поля перечислены в операторе CREATE. Если вы хотите добавлять информацию в другом порядке, то вы должны указать этот порядок в операторе INSERT:

INSERT INTO CLIENTS (FIO,ADDRESS,C_NO,PHONE,CITY).

VALUES ('Петров', 'Пушкина 9',2,'-','Кировоград');

С помощью INSERT мы можем устанавливать значения только некоторых полей:

INSERT INTO CLIENTS (C_NO, FIO) VALUES (1,'Петров');

В нашем примере этот запрос выполнен не будет, поскольку все остальные поля равны NULL (пустое значение), а наша таблица пустых значений не допускает.

Добавим данные в таблицу TOVAR:

INSERT INTO TOVAR.

VALUES (1,'Монитор LG',550.74);

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

Добавить дату в поле DATE можно с помощью функции TO_DATE:

INSERT INTO ORDERS.

VALUES (1,TO_DATE('01/01/02,'DD/MM/YY'), 1,1,1,550.74);

Данная запись означает, что первого января 2002 года Иванов И.П. (С_NO = 1) заказал один (QUANTITY = 1) монитор LG (T_NO = l).

Предположим, что нам нужно обновить запись, например, клиент Иванов переехал в другой город. Это делается так:

UPDATE CLIENTS SET CITY = 'Киев' WHERE C_NO = 1;

Теперь удалим всех клиентов, номера которых превышают 10:

DELETE FROM CLIENTS.

WHERE C_NO >10;

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

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

Например, для вывода всех записей из таблицы CLIENTS, введите:

SELECT * FROM CLIENTS;

В результате вы получите такой ответ от сервера:

C_NO FIO ADDRESS CITY PHONE.

1 Иванов И.П. Ленина 6 Кировоград 80522111111.

1 Иванов И.П. Ленина 6 Кировоград 805221111]1.

2 Петров В.К. Пушкина 9 Кировоград 80522112111.

Обратите внимание на первые две записи — они одинаковые. Теоретически добавление одинаковых записей возможно — мы ведь не указали первичный ключ таблицы. Если вы хотите исключить одинаковые записи из ответа сервера (но не из таблицы!), введите запрос:

SELECT DISTINCT *

FROM CLIENTS;

Если вы хотите вывести только некоторые поля, то запрос должен выглядеть так:

SELECT DISTINCT FIO, PHONE.

FROM CLIENTS;

Теперь займемся усложнением наших запросов. Выведем все товары, цена которых превышает 500 рублей.

SELECT *

FROM TOVAR.

WHERE PRICE > 500;

Вы можете использовать другие операторы отношений: <,>, = ,<>,

Если ваша компания обслуживает несколько однофамильцев и вы хотите вывести информацию обо всех Ивановых, используйте шаблон LIKE:

SELECT *

FROM CLIENTS.

WHERE FIO LIKE '%Иванов%';

Запрос читается так: вывести всю информацию о клиентах, фамилия которых похожа на 'Иванов'.

Следующие два оператора эквивалентны:

SELECT *

FROM TOVAR.

WHERE (PRICE > 100) AND (PRICE < 200);

И оператор.

SELECT *

FROM TOVAR.

WHERE PRICE BETWEEN 100 AND 200;

Если вы хотите выбрать данные из разных таблиц, перед именем поля нужно указывать имя таблицы. Следующий запрос выведет имена всех клиентов, которые хотя бы раз покупали у нас товар:

SELECT DISTINCT CLIENTS.FIO.

FROM CLIENTS, ORDERS.

WHERE CLIENTS.C_NO = ODREDS.C_NO;

При работе с оператором SELECT вам доступно несколько полезных функций, вычисляющих количество элементов (COUNT), сумму элементов (SUM), максимальное и минимальное значение (МАХ и MIN), а также среднее значение (AVG).

Следующие операторы выведут, соответственно, количество записей в таблице CLIENTS и самый дорогой товар на складе:

SELECT COUNT(*).

FROM CLIENTS;

SELECT MAX(PRICE).

FROM TOVAR;

Оператор SELECT позволяет группировать возвращаемые значения. Например, клиент Иванов (C_NO=1) несколько раз заказывал у нас какой-то товар. Значит, его номер встречается в таблице ORDERS несколько раз.

Выведем имена всех клиентов, а также сумму заказа каждого клиента.

SELECT CLIENTS.FIO, SUM(ORDERS.AMOUNT) AS TOTALSUM.

FROM CLIENTS, ORDERS.

WHERE CLIENTS.C_NO = ORDERS.C_NO.

GROUP BY ORDERS.C_NO;

Группировку выполняет оператор GROUP BY, который является частью оператора SELECT. Оператор GROUP BY можно ограничить с помощью HAVING. Этот оператор используется для отбора строк, возвращаемых GROUP BY. HAVING можно считать аналогом WHERE, но только для GROUP BY:

HAVING <условие>

Например, нас интересуют только клиенты, которые заказали товаров на общую сумму, превышающую 1000.

SELECT CLIENTS.FIO, SUM(ORDERS.AMOUNT) AS TOTALSUM.

FROM CLIENTS, ORDERS.

WHERE CLIENTS.C_NO = ORDERS.C_NO.

GROUP BY ORDERS.C_NO.

HAVING TOTALSUM > 1000;

Примечание. В этом запросе мы использовали псевдоним столбца TOTALSUM. В некоторых вариантах SQL для определения псевдонима не нужно писать служебное слово AS, а другие требуют применение знака равенства: SUM(ORDERS.AMOUNT) TOTALSUM. Или. TOTALSUM = SUM(ORDERS.AMOUNT).

Пока мы не установили первичный ключ, сортировка нашей таблицы не выполняется. Записи будут отображены в порядке их занесения в таблицу. Для сортировки по полю C_NO результата вывода таблицы CLIENTS используется следующий запрос (сама таблица при этом не сортируется):

SELECT *

FROM CLIENTS.

ORDER BY C_NO;

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

1 Сидоров Егорова 11 Кировоград 80522345111.

У нас получилось, что один и тот же номер сопоставлен разным клиентам. Тогда как определить, кто из них заказал монитор LG? Чтобы избежать подобной путаницы, нужно использовать первичные ключи;

ALTER TABLE CUSTOMER.

ADD PRIMARY KEY (C_NO);

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

Создать первичный ключ можно проще — при создании таблицы. Это делается так:

CREATE TABLE CLIENTS (

 C_NO    int      NOT NULL,

 FIO     char(40) NOT NULL,

 ADDRESS char(30) NOT NULL,

 CITV    char(15) NOT NULL,

 PHONE   char(11) NOT NULL,

 PRIMARY KEY (C_NO);

);

Таблица ORDERS содержит сведения о заказах. По полю C_NO этой таблицы идентифицируется заказчик. Предположим, что в таблицу ORDERS кто-то ввел значение, которого нет в таблице CLIENTS. Кто же заказал товар? Нам нужно не допустить подобной ситуации, поэтому введите следующий запрос на создание внешнего ключа:

ALTER TABLE ORDERS.

ADD FOREIGN KEY(C_NO) REFERENCES CLIENTS;

Введенные в таблицу ORDERS номера клиентов C_NO должны существовать в таблице CLIENTS. Аналогично нужно добавить внешний ключ по полю T_NO. Эта возможность называется декларативной целостностью.

Команда ALTER используется не только для добавления ключей. Она предназначена для реорганизации таблицы в целом. Вы хотите добавить еще одно поле? Или установить список допустимых значений для каждого из полей? Все это можно сделать с помощью команды ALTER:

ALTER TABLE CLIENTS ADD ZIP char(6) NULL;

Этот запрос добавляет в таблицу CLIENTS новое поле ZIP типа char. Обратите внимание, что вы не можете добавить новое поле со значением NOT NULL в таблицу, в которой уже есть данные.

Наша компания работает с клиентами только из Киева и Кировограда, поэтому целесообразно ввести список допустимых значений для таблицы CLIENTS:

ALTER TABLE CLIENTS.

ADD CONSTRAINT INVALID_STATE SHECK (CITY IN.

('Кировоград','Киев'));

Вам уже надоело работать с этой базой данных? Тогда с помощью запроса DISCONNECT отключитесь от нее, и, используя запрос CONNECT, подключитесь к другой базе данных. В некоторых вариантах SQL запрос DISCONNECT не работает, а вместо CONNECT нужно использовать оператор USE.

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

CREATE TABLE T (

/* Описания полей таблицы */

FOREIGN KEY KEY_NAME (LIST).

REFERENCES ANOTHER_TABLE ((LIST2)).

[ON DELETE OPTION].

[ON UPDATE OPTION].

);

Здесь KEY_NAME — это имя ключа. Оно не является обязательным, но я очень рекомендую его указывать: если вы не укажете имя ключа, то потом не сможете его удалить.

LIST — это список полей, входящих во внешний ключ. Список разделяется запятыми.

ANOTHER_TABLE — это другая таблица, по которой устанавливается внешний ключ, а необязательный элемент LIST2 — это список полей этой другой таблицы. Типы полей в списке LIST должны совпадать с типами полей в списке LIST2.

Необязательные параметры ON DELETE и ON UPDATE определяют действия, которые нужно произвести при удалении информации из таблицы и при ее обновлении. Например, нельзя так просто удалить клиента из таблицы клиентов, если в таблице заказов присутствуют записи его заказов: нарушится целостность базы. С помощью параметра ON DELETE мы можем указать серверу реакцию на удаление таких данных:

ON DELETE OPTION.

Параметр OPTION может принимать одно их четырех значений: CASCADE, NO ACTION, SET DEFAULT, SET NULL.

Значение CASCADE означает, что номер удаляемого клиента будет удален из всех связанных таблиц. Например, если вы удалите клиента с номером 10 из таблицы клиентов, то из таблицы заказов будут удалены все заказы этого клиента.

Опция NO ACTION не разрешает удаление клиента до тех пор, пока его номер присутствует в связанной таблице. В нашем случае это означает, что сначала должны быть удалены все заказы клиента с номером 10.

С помощью опции SET_DEFAULT вы можете указать значение по умолчанию. Например, если вы укажете SET DEFAULT 1, то при удалении клиента с любым номером его заказы будут приписываться клиенту с номером 1, который, разумеется, всегда есть в таблице CLIENTS.

Опция SET NULL устанавливает значение NULL в качестве номера клиента, если тот удален из таблицы CLIENTS. В нашем примере это значение не допускается.

Две страницы назад мы добавили поле ZIP. А как его удалить? Стандартом SQL не предусмотрено удаление столбцов, но в MySQL мы все же можем это сделать:

ALTER TABLE CLIENTS DROP ZIP;

Как удалить все записи? Очень просто:

DELETE * FROM ORDERS;

Удалить таблицу еще проще:

DROP ORDERS;

В первом случае вы не удаляете таблицу: файл таблицы все еще остается на диске. Вы удалили только содержимое таблицы. Во втором случае вы полностью удаляете таблицу.

Естественно, удалить таблицу можно только при наличии соответствующих прав.

Напоследок рассмотрим два полезных примера. Предположим, что нам нужно установить ограничение на количество товара, которое можно продать клиенту. Допустим, в данный момент у нас нет такого количества товара на складе, следовательно, мы не можем оформить заказ. Ограничение данного типа можно определить с помощью запроса:

CREATE ASSERTION LIMIT.

CHECK (ORDERS.QUANTITY <= TOVARS.QTY);

Установить минимальное количество для заказа можно так:

CREATE TABLE ORDERS (

 /* Определение полей */

 FOREIGN KEY KEY1 (C_NO).

 REFERENCES CLIENTS.

 ON DELETE NO ACTION.

 CHECK (QUANTITY >= 1).

);

Следующий запрос устанавливает минимальный размер заказа, если таблица уже существует:

CREATE ASSERTION LIMIT.

CHECK (QUANTITY >=1);