Перейти к содержанию

16.4 Управление данными SQL (Managing SQL Data)

Для сдачи экзамена Linux+/LPIC-1 необходимо уметь управлять данными внутри базы данных SQL. Внедрение и администрирование баз данных SQL — огромная тема, значительно выходящая за рамки этой книги. Тем не менее ниже приведены основные шаги по установке, а также показано, как добавлять информацию в базу данных и извлекать её. В этой части главы рассматриваются следующие темы:

  • Принципы работы баз данных
  • Установка MySQL
  • Управление данными на SQL-сервере

Начнём с обсуждения того, как работают базы данных SQL.

Принципы работы баз данных

База данных (database) — это набор информации, организованной таким образом, чтобы данные можно было быстро выбирать и извлекать по заданному поисковому запросу. За последние 20 лет базы данных стали основой торговли, коммуникаций и государственного управления во всём мире. Вместо огромных хранилищ бумажных документов базы данных позволяют хранить колоссальные объёмы информации в ничтожно малом физическом пространстве.

Службы баз данных работают по модели клиент/сервер. Как правило, клиентская и серверная части устанавливаются на одном сервере баз данных. Клиентское программное обеспечение затем устанавливается на клиентских системах, с которых необходимо обращаться к информации в базе данных. В Linux широко применяются две службы баз данных:

  • MySQL
  • PostgreSQL

Установив одну из этих служб, вы получаете всё необходимое для запуска, эксплуатации и администрирования базы данных с помощью языка SQL (Structured Query Language — язык структурированных запросов), являющегося стандартным языком доступа к базам данных и управления ими. SQL определяет операторы для выборки и обновления данных в базе — такие как SELECT, UPDATE, DELETE, INSERT и WHERE.

Обе указанные службы реализуют реляционные базы данных (relational databases), организованные иерархически. Реляционные базы данных специализированы для организации и хранения огромных объёмов данных, а также рассчитаны на высокую масштабируемость — способность расти вместе с потребностями организации.

Реляционная база данных организована с помощью полей, записей и таблиц. Поле (field) — это единичная единица информации. Запись (record) — полный набор полей, а таблица (table) — набор записей. Каждая таблица идентифицируется именем, например Customers. Каждая таблица содержит записи (каждая — отдельная строка), которые содержат одно или несколько полей, хранящих непосредственно данные базы. Например, предположим, что определена таблица Customers со следующими тремя записями:

Last           First           Address                       City           State           Zip
Tracy          Leah            1234 W. Longfellow            Bone           Idaho           83401
Morgan         Ken             3456 W. 100 S.                Rigby          Idaho           83442

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

SELECT Last FROM Customers

вернёт такие данные:

Last
Tracy
Morgan

Для управления данными в базе данных SQL можно использовать следующие команды:

  • SELECT — извлекает информацию из таблицы
  • UPDATE — изменяет информацию в таблице
  • DELETE — удаляет информацию из таблицы
  • INSERT INTO — добавляет новые данные в таблицу
  • CREATE TABLE — создаёт новую таблицу
  • ALTER TABLE — изменяет существующую таблицу
  • DROP TABLE — удаляет существующую таблицу

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

Рассмотрев эти основы, перейдём к установке базы данных SQL на вашу Linux-систему.

Установка MySQL

Несмотря на то что для Linux существует несколько различных служб баз данных SQL, в этой главе рассматривается использование MySQL Community Server. MySQL — это SQL-сервер баз данных с открытым исходным кодом, входящий в состав установочных носителей большинства дистрибутивов. Он также сравнительно прост в установке и быстром создании баз данных.

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

  • mysql-community-server
  • mysql-community-server-client
  • mysql-community-server-tools

Примечание

Конкретные устанавливаемые пакеты зависят от используемого дистрибутива Linux и версии MySQL.

После установки пакетов MySQL необходимо настроить таблицы привилегий (grant tables) сервера MySQL. Всё управление доступом в MySQL осуществляется внутри самой службы MySQL. Важно понимать, что MySQL имеет собственный уникальный набор учётных записей пользователей, определённых в таблицах привилегий: она не использует учётные записи, определённые в вашей Linux-системе. В базе данных MySQL для этого реализованы пять таблиц:

  • user — определяет, разрешено ли пользователю подключаться к серверу MySQL
  • db — определяет, к каким базам данных пользователь имеет доступ
  • host — определяет, с каких хостов разрешён доступ к конкретной базе данных
  • tables_priv — определяет привилегии доступа для заданной таблицы
  • columns_priv — определяет привилегии доступа к конкретным столбцам данных заданной таблицы

Перед использованием MySQL эти таблицы необходимо инициализировать. Для этого перейдите в каталог /usr/bin и выполните в командной строке оболочки команду mysql_install_db. После этого таблицы привилегий готовы к работе. Команду mysql_install_db достаточно выполнить один раз — сразу после первоначальной установки MySQL. Она создаёт две учётные записи пользователей MySQL: root и анонимного пользователя.

После создания таблиц привилегий необходимо запустить службу базы данных. Если в вашем дистрибутиве используется init, можно воспользоваться сценарием mysql init, расположенным в каталоге сценариев init. Затем с помощью команды insserv или chkconfig обеспечьте запуск службы базы данных при каждой загрузке системы. Если в вашем дистрибутиве используется systemd, применяйте команду systemctl для включения и запуска службы mysql.

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

openSUSE:/usr/bin # mysqladmin version
mysqladmin Ver 8.42 Distrib 5.6.12, for Linux on i686
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Server version          5.6.12
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysql/mysql.sock
Uptime:                 40 sec
Threads: 1 Questions: 2 Slow queries: 0 Opens: 70 Flush tables: 1
Open tables: 63 Queries per second avg: 0.050

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

openSUSE:~ # mysqlshow
+--------------------+
|      Databases     |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

Таблицы внутри любой из отображаемых баз данных можно также просматривать с помощью команды mysqlshow имя_таблицы.

Убедившись в нормальной работе MySQL, необходимо назначить пароли учётным записям пользователей MySQL. После выполнения mysql_install_db учётная запись базы данных root создана, но пароль ей не назначен. Чтобы исправить это, введите в командной строке оболочки mysqladmin –u root password 'ваш_новый_пароль'. Затем ограничьте доступ root к системе, на которой запущен MySQL, введя в командной строке оболочки mysqladmin –u root –h имя_хоста password пароль_root_mysql.

На этом сервер MySQL настроен и готов к работе. Теперь можно управлять данными SQL!

Управление данными на SQL-сервере

Для управления данными на сервере MySQL необходимо подключиться к нему с помощью какого-либо SQL-клиента. Существует большое разнообразие клиентов. Однако для сдачи экзамена Linux+/LPIC-1 лучше всего разобраться с клиентом MySQL для командной строки. Умея манипулировать данными SQL-сервера с помощью этой утилиты, вы легко освоите любые другие клиенты.

Клиент MySQL для командной строки запускается вводом mysql в командной строке оболочки. Синтаксис: mysql –h имя_хоста –u имя_пользователя –p. Например, для подключения к службе MySQL, запущенной на локальной Linux-системе от имени пользователя root, введите mysql –h localhost –u root –p. Это показано ниже:

openSUSE:/usr/bin # mysql -h localhost -u root –p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.12 openSUSE package
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

После подключения к базе данных вы можете выполнять любые поддерживаемые конструкции SQL-команд. Попрактикуемся в этом в следующем упражнении.

Упражнение 16-3. Управление данными SQL

В этом упражнении вы создадите новую базу данных customers и заполните её данными. Упражнение можно выполнить на виртуальной машине, прилагаемой к этой книге. Запустите снимок 16-3 для получения корректно настроенной среды.

Видео

Посмотрите видео «Упражнение 16-3» с демонстрацией выполнения этого задания.

Выполните следующие шаги (точки с запятой в командах должны оставаться):

  1. Переключитесь на root с помощью команды su–.
  2. Настройте таблицы привилегий сервера MySQL: перейдите в каталог /usr/bin и выполните команду mysql_install_db в командной строке оболочки.
  3. Запустите службу MySQL, введя в командной строке оболочки servicectl start mysql.
  4. Убедитесь в запуске сервера, введя команду mysqladmin version в командной строке оболочки.
  5. Назначьте пароль учётной записи root MySQL, введя в командной строке оболочки mysqladmin –u root password 'ваш_новый_пароль'.
  6. Подключитесь к серверу MySQL из командной строки, введя mysql –h localhost –u root –p. По запросу введите только что заданный пароль root.
  7. В приглашении mysql> введите CREATE DATABASE customers;. Должно появиться подтверждение создания базы данных.
  8. Для работы с новой базой данных введите в приглашении mysql> команду USE customers;.
    • first
    • last
    • phone
    • lastcontact

    Чтобы просмотреть таблицы, определённые по умолчанию в новой базе данных, введите в приглашении mysql> команду SHOW TABLES;. Таблицы не должны быть определены. На этом этапе задача несколько усложняется. Для создания таблицы необходимо использовать команду CREATE TABLE, при этом нужно также определить столбцы таблицы в самой команде. Поэтому сначала следует определить поля для каждой записи таблицы. Также необходимо решить, какой тип данных будет храниться в каждом поле и какова будет длина таблицы. В создаваемой таблице потребуется четыре столбца:

    Поля first и last будут содержать буквенные символы переменной длины. Поле phone будет содержать десять цифр с двумя дефисами (код_региона-префикс-номер). Столбец lastcontact будет содержать дату последней покупки каждого клиента.

  10. Создайте новую таблицу в базе данных, введя в приглашении mysql> команду:

    CREATE TABLE active (first VARCHAR(15), last VARCHAR(15), phone CHAR(12), lastcontact DATE);

  11. Убедитесь в корректном создании таблицы и столбцов, введя в приглашении mysql> команду DESCRIBE active;. Должна отобразиться созданная таблица active с её полями:

    mysql> DESCRIBE active;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| first       | varchar(15) | YES  |     | NULL    |       |
| last        | varchar(15) | YES  |     | NULL    |       |
| phone       | char(12)    | YES  |     | NULL    |       |
| lastcontact | date        | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

    Теперь, когда таблица определена, в неё можно вставлять данные.

  12. Чтобы добавить запись для клиента Robb Tracy, введите INSERT INTO active VALUES ('Robb','Tracy','801-756-5555','2015-07-01');.

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

    SELECT поле(я) FROM таблица [WHERE условия] [ORDER BY поле]

    Например, чтобы просмотреть все данные в таблице active, введите в приглашении mysql> команду SELECT * FROM active;. Вы должны увидеть следующие данные:

    mysql> SELECT * FROM active;
+-------+-------+--------------+-------------+
| first | last  | phone        | lastcontact |
+-------+-------+--------------+-------------+
| Robb  | Tracy | 801-756-5555 | 2015-07-01  |
+-------+-------+--------------+-------------+
1 row in set (0.01 sec)

Можно также ввести SELECT first,phone FROM active;, чтобы просмотреть только определённые поля каждой записи. Другой пример: SELECT * FROM active WHERE last='Tracy'; — для просмотра только записей, содержащих строку "Tracy" в поле last.

Данные из таблицы также можно удалять. Синтаксис:

DELETE FROM таблица WHERE условия

Например, чтобы удалить запись Robb Tracy из таблицы, введите в приглашении mysql> команду DELETE FROM active WHERE last='Tracy';. Однако перед удалением рекомендуется сначала выполнить эквивалентную команду SELECT, чтобы точно увидеть, что именно будет удалено. Например, сначала выполните SELECT FROM active WHERE last='Tracy';, а затем DELETE FROM active WHERE last='Tracy';.

Изменять существующие данные можно с помощью команды UPDATE. Синтаксис:

UPDATE имя_таблицы SET столбец = новое_значение WHERE условие

Для объединения результатов запроса SELECT по одному или нескольким столбцам можно использовать оператор GROUP BY. Он часто применяется совместно с функцией SUM(). Например, предположим, что таблица active содержит два дополнительных столбца quantity и custid и используется для регистрации отдельных заказов. В такой ситуации у каждого клиента может быть несколько записей в таблице — по одной для каждого заказа. Команда SELECT custid,SUM(quantity) AS "Total" FROM active GROUP BY custid; объединит по одному результату для каждого уникального идентификатора клиента, отображая суммарное приобретённое количество для каждого (в новом столбце с именем "Total").

Совет

Для объединения полей из двух разных таблиц можно также использовать команду JOIN.