Linux: Полное руководство — страница 64 из 98

По умолчанию для файла сокета 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);

Глава 18.