MySQL для пользователя

Автор: Денис Колисниченко

Во всемирной Сети можно найти много информации о том, как настроить MySQL-сервер, но практически нет информации о том, как его использовать обыкновенному пользователю. Возможно, это из-за того, что приложения, которые базируются на MySQL, пишутся квалифицированными программистами, а самому пользователю и не нужно знать, что и как работает. Но знать это все же не помешает, например, вы хотите знать, как выполняется тот или иной SQL-запрос.

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

mysqlshow -p mysql 

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

Database: mysql 
+--------+ 
| Tables |
 +--------+ 
| db | 
| host | 
| user | 
+--------+ 

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

Таблица 1. Параметры программы mysqlshow

Параметр	Описание
--host=hostname	Задает имя хоста, к которому вы хотите подключиться
--port=port_number	Определяет номер порта для сервера MySQL
--socket=socket	Указывает сокет
--user=username	С помощью этого параметра можно указать нужное имя пользователя
-p	Запрашивает ввода пароля

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

Создать базу данных можно с помощью программы mysqladmin. Естественно, что использовать эту прогамму может только администратор, например,

mysqladmin -u admin -p create my_db

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

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

1. Cоздавать базы данных и таблицы
2. Добавлять информацию в таблицы
3. Удалять информацию
4. Модифицировать информацию
5. Получать нужные вам данные

Естественно, пользователь admin, кроме того, что должен существовать, должен обладать соответствующими правами. Каждый запрос 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).

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 (то есть AMOUNT = T_NO * TOVAR.PRICE)

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

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

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

INSERT INTO CLIENTS (FIO,ADDRESS,C_NO,PHONE,CITY)
VALUES ('Петров', 'Пушкина 9',2,'-','Кировоград');

С помощью INSERT мы можем добавлять данные в определенные поля, например, C_NO и FIO:

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 года Иванов И.П. (C_NO=1) заказал один (QUANTITY=1) Монитор LG (T_NO=1).

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

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

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

DELETE FROM CLIENTS
WHERE C_NO > 10;

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

DELETE FROM CLIETNS;

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

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

SELECT * FROM CLIENTS;

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

C_NO FIO ADDRESS CITY PHONE
1 Иванов И.П. Ленина 6 Кировоград 80522111111
1 Иванов И.П. Ленина 6 Кировоград 80522111111
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 DISTINCT CLIENTS.FIO
FROM CLIENTS, ORDERS
WHERE CLIENTS.C_NO = ODREDS.C_NO;

Оператор SELECT позволяет использовать вложенные запросы. Следующий оператор аналогичен предыдущему:

SELECT DISTINCT CLIENTS.FIO
FROM CLIENTS
WHERE CLIENTS.C_NO IN (SELECT C_NO
 FROM ORDERS);

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

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

SELECT COUNT(*) 
FROM CLIENTS;

SELECT MAX(PRICE)
FROM TOVAR;

SELECT SUM(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,
CITY 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. Предположим, что в первой таблице у нас есть два поля – NO и NAME – целого и символьного типов соответственно. Во второй таблице у нас есть поля с одинаковыми именами и типами. Определение внешнего ключа

FOREIGN KEY KEY_NAME (NO, NAME)
REFERENCES ANOTHER_TABLE (NAME, NO)

Некорректно, потому что типы полей NO и NAME не совпадают. Нужно использовать такое определение:

FOREIGN KEY KEY_NAME (NO, NAME)
REFERENCES ANOTHER_TABLE (NO, NAME)

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

Необязательные параметры ON DELETE и ON UPDATE определяют действие по обновлению информации в базе данных, при удалении информации из таблицы и при ее обновлении. Помните наш пример с клиентами? Я о том, что в таблице заказов есть поле C_NO (Client NO), значения которого должны быть в таблице клиентов. И в самом деле, как мы узнаем имя и прочие данные клиента с номером 99999, которого нет в таблице клиентов? Установив внешний ключ, мы связываем две таблицы по полю C_NO. Можно спокойно спать (я хотел сказать администрировать базу данных), до одного прекрасного момента, когда девушка-оператор удалит какого-нибудь клиента из таблицы клиентов. Что делать с записями в таблице заказов? С помощью параметра ON DELETE мы можем указать серверу реакцию на удаление таких данных:

ON DELETE OPTION

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

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

Параметр NO ACTION не разрешает удаление клиента до тех пор, пока но есть в связанной таблице. Это означает, что девушка-оператор должны сперва удалить всю информацию о заказах из таблицы заказов

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

Параметр SET NULL устанавливает значение NULL в качестве номера клиента, если тот удален из таблицы CLIENTS. Помните, что в нашем случае поле C_NO не допускает значения NULL! А как удалить поле? Стандартом SQL не предусмотрено удаление столбцов, но в MySQL мы все же можем это сделать:

ALTER TABLE CLIENTS
DROP ZIP;

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

DROP ORDERS;