. Одобрено лучшими российскими программистами

Подсоединение к серверу и отсоединение
от него

При подключении к серверу с помощью
mysql обычно нужно ввести
имя пользователя MySQL и, в
большинстве случаев, пароль. Если
сервер запущен не на том компьютере,
с которого вы вошли в систему,
необходимо также указать имя хоста.
Параметры соединения (а именно —
соответствующее имя хоста,
пользователя и пароль) вы сможете
узнать у администратора. Получив
соответствующие параметры,
подсоединиться к серверу можно
следующим образом:

shell> mysql -h host -u user -p
Enter password: ********

Символы ******** обозначают ваш пароль;
введите его, когда mysql
выведет на экран запрос Enter
password:.

Если все сработает, на экране должна
появиться следующая информация и
метка командной строки
mysql>:

shell> mysql -h host -u user -p
Enter password: ********

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 459 to server version: 3.22.20a-log

Type 'help' for help.

mysql>

Метка обозначает, что программа
mysql готова к вводу команд.

В некоторых вариантах установки MySQL
возможно подсоединение к
запущенному на локальном хосте
серверу без ввода имени
пользователя (пользователь anonymous).
Если ваша система настроена именно
так, подсоединиться к серверу вы
сможете, запустив mysql со
следующими параметрами:

shell> mysql

После установки соединения можно в
любой момент отключиться от
сервера, набрав в командной строке
mysql> команду
QUIT:

mysql> QUIT
Bye

Отсоединиться от сервера можно и
при помощи сочетания клавиш Control-D.

Большая часть приведенных ниже
примеров построена с учетом того,
что соединение с сервером уже
установлено. Это видно по наличию в
них командной строки
mysql>.

Использование SET

В команде INSERT мы обычно указываем отдельно столбцы и отдельно значения. Но есть способ записи, похожий на команду UPDATE: указывать столбцы и их значения через символ равенства. Такой способ может быть удобен, когда в таблице много полей и значений. Так сразу видно, в какое поле какое значение подставляется.

    INSERT INTO sale SET store_id=1, fruit_id=1, price=89, count=3;

Проверим, что числа вставились именно в те столбцы, которые мы указали:

    +----------+----------+--------+-------+
| store_id | fruit_id | price  | count |
+----------+----------+--------+-------+
|        1 |        3 |     60 |    20 |
|        2 |        2 |     80 |    10 |
|        3 |        1 |    120 |    12 |
|        1 |        1 |     89 |     3 |
+----------+----------+--------+-------+

Игнорирование ошибки при вставке

Продолжим предыдущий пример. Допустим, записи в нашу таблицу вставляет бэкенд-сервис приложения. Мы хотим, чтобы сервис попробовал вставить новую запись, но если такой первичный ключ уже существует — ничего не обновлял и просто шел выполнять другой код. Но при совпадении ключей MySQL выдаст ошибку, а код на бэкенде прервется и ее нужно будет обрабатывать в сервисе. Гораздо проще добавить к команде INSERT ключевое слово IGNORE, чтобы при совпадении первичного ключа MySQL не генерировал ошибку:

    INSERT IGNORE INTO stores VALUES (1, 'Магазин 10', 'Адрес 10');

Обратите внимание на сообщение, которое выдает MySQL, оно будет примерно таким: Query OK, 0 rows affected, 1 warning. Это значит, что запрос выполнился успешно, при этом он не затронул ни одну строки и есть одно предупреждение. Проверим результат: видим, что новых магазинов не появилось, а текущие не изменились.

    +----+------------------+--------------+
| id | name             | address      |
+----+------------------+--------------+
|  1 | Магазин 1        | Адрес 1      |
|  2 | Магазин 2        | Адрес 2      |
|  3 | Магазин 3        | Адрес 3      |
|  4 | Магазин 4        | NULL         |
+----+------------------+--------------+

Вставка нескольких строк

За один запрос можно добавлять сразу несколько элементов. Например, мы хотим добавить три новых фрукта. Вместо того, чтобы писать три отдельных команды INSERT, мы воспользуемся вот таким способом записи:

    INSERT INTO fruits VALUES (5, 'Киви'), (6, 'Виноград'), (7, 'Груша');

Посмотрим результат. Видим, что появилось сразу три новых записи:

    +----+------------------+
| id | name             |
+----+------------------+
|  1 | Яблоко           |
|  2 | Мандарин         |
|  3 | Банан            |
|  4 | Апельсин         |
|  5 | Киви             |
|  6 | Виноград         |
|  7 | Груша            |
+----+------------------+

Настройка Базы Данных

Создание Базы Данных

Мы вошли и приглашение mysql показано на экране. Сначала, давайте рассмотрим базы данных, которые у нас имеются в настоящий момент. Чтобы это сделать, мы используем команду SHOW DATABASES.

mysql>SHOW DATABASES;

+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.09 sec)

ВажноПожалуйста, запомните что команды MySQL должны оканчиваться точкой с запятой — ;

Вопреки тому факту, что тестовая база данных уже создана, мы собираемся создать нашу собственную. Базы данных создаются с использованием команды CREATE DATABASE. Мы создадим одну из них под названием gentoo.

mysql>CREATE DATABASE gentoo;

Query OK, 1 row affected (0.08 sec)

Ответ позволяет нам узнать, что команда была выполнена без ошибок. В этом случае, одна строка была изменена. Это является отсылкой к основной базе данных mysql, которая содержит список всех баз данных. Вам не нужно сильно беспокоиться о второстепенных деталях. Последнее число является характеристикой того, насколько быстро был выполнен запрос. Мы можем проверить, что база данных была создана, запустив команду SHOW DATABASES снова.

mysql>SHOW DATABASES;

+----------+
| Database |
+----------+
| gentoo   |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

В самом деле, наша база данных была создана. Для того чтобы работать с созданием таблиц для нашей новой базы данных gentoo, нам надо выбрать ее в качестве текущей базы данных. Чтобы это сделать, мы используем команду USE. Команда USE принимает имя базы данных, которую вы хотите использовать в качестве текущей. Другой возможностью является ее установка в командной строке после параметра -D. Давайте продолжим и переключимся к базе данных gentoo.

mysql>USE gentoo;

Database changed

Сейчас, текущей базой данных является созданная нами ранее база данных gentoo. Теперь, когда мы ей пользуемся, мы можем начать создавать таблицы и заполнять их информацией.

Работа с таблицами в MySQL

Создание таблицы

В структуру MySQL входят базы данных, таблицы, записи, и поля. Базы данных объединяют таблицы, таблицы объединяют записи, записи объединяют поля, которые содержат действительную информацию. Такая структура позволяет пользователям выбирать как они хотят обращаться к своей информации. На данный момент, мы разобрались с базами данных, теперь давайте поработаем с таблицами. Во-первых, таблицы могут быть перечислены, подобно базам данных, с использованием команды SHOW TABLES. Сейчас, в базе данных gentoo не имеется таблиц, как и показывает нам следующая команда:

mysql>SHOW TABLES;

Empty set (0.00 sec)

Это означает нам надо создать какие-либо таблицы. Чтобы это сделать, мы используем команду CREATE TABLE. Однако, эта команда достаточно отличается от простой команды CREATE DATABASE тем что принимает список аргументов. Формат команды следует ниже:

Код Синтаксис CREATE TABLE

    

table_name — это имя таблицы, которую мы хотим создать. В данном случае, давайте создадим таблицу с именем developers . Эта таблица будет хранить имя разработчика, email адрес и его должность.

field_name будет хранить имя поля. В этом случае мы имеем три требуемых имени: имя разработчика, email адрес, и должность.

field_data_type — это то, какой тип информации будет сохранен. Различные доступные форматы могут быть найдены по адресу MySQL Column Types Page . Для наших целей, мы будем использовать тип данных VARCHAR для всех наших полей. VARCHAR — это один из простейших типов данных, когда дело касается работы со строками.

size — это то, как много данных будет хранить одно поле. В нашем случае, мы будем использовать 128. Это означает, что поле будет иметь данные типа VARCHAR, которые занимают 128 байт. В настоящий момент, вы можете спокойно думать об этом как о 128 символах, хотя существует более техническое объяснение, которое вам предоставит сайт, указанный выше. Теперь, когда мы знаем как мы создадим таблицу, давайте это сделаем.

mysql>CREATE TABLE developers ( name VARCHAR(128), email VARCHAR(128), job VARCHAR(128));

Query OK, 0 rows affected (0.11 sec)

Похоже, наша таблица была успешно создана. Давайте проверим это командой SHOW TABLES:

mysql>SHOW TABLES;

+------------------+
| Tables_in_gentoo |
+------------------+
| developers       |
+------------------+
1 row in set (0.00 sec)

Да, наша таблица существует. Однако, она, по-видимому, не имеет никакой информации о типах полей, которые мы создали. Для этого, мы используем команду DESCRIBE (или, кратко, DESC), которая принимает имя таблицы в качестве своих аргументов. Давайте посмотрим что она нам даст для нашей таблицы developers.

mysql>DESCRIBE developers;

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(128) | YES  |     | NULL    |       |
| email | varchar(128) | YES  |     | NULL    |       |
| job   | varchar(128) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Показаны разные поля и их типы. Также показано несколько дополнительных атрибутов, которые находятся за рамками данного руководства. Пожалуйста, обратитесь за подробностями к Справочному Руководству MySQL . Теперь у нас есть таблица для работы с ней. Давайте продолжим и заполним ее.

Заполнение Базы Данных MySQL

Мы заполним таблицу (или добавим в нее данные), используя команду INSERT. Подобно команде CREATE TABLE, она также имеет особый формат:

Код Синтаксис INSERT

        

Эта команда используется для вставки записи в таблицу. table содержит таблицу MySQL, в которую мы хотим ввести информацию. Имя таблицы может сопровождаться списком столбцов для вставки данных, VALUES() хранит значения, которые вы хотите вставить в таблицу. Вы можете опустить список столбцов, если вы вставляете значение в каждый из них и если Вы пишете значения в том же порядке, в котором определены столбцы. В данном случае, мы хотим вставить данные в таблицу developers. Давайте добавим примеры записей:

mysql>INSERT INTO developers VALUES('Joe Smith', 'joesmith@gentoo.org', 'toolchain');

Query OK, 1 row affected (0.06 sec)
## (Если вы не знаете порядок столбцов в таблице или хотите вставить неполную запись)
mysql> INSERT INTO developers (job, name) VALUES('outsourced', 'Jane Doe');
Query OK, 1 row affected (0.01 sec)

В соответствии с полученным результатом, кажется, запись была вставлена правильно. Что если мы хотим ввести больше, чем просто одну запись? Это тот случай, когда команда LOAD DATA вступает в действие. Она загружает записи из файла, разделенного символами табуляции. Давайте это попробуем, отредактировав файл в домашнем каталоге пользователя и добавив в него записи. Мы назовем этот файл records.txt . Здесь приведен пример:

Код ~/records.txt

John Doe	johndoe@gentoo.org	portage
Chris White	chriswhite@gentoo.org	documentation
Sam Smith	samsmith@gentoo.org	amd64

ВажноУбедитесь, что вы знаете, с какими данными вы будете работать. Очень небезопасно использовать LOAD DATA, когда вы не уверены насчет содержимого файла!

Команда LOAD DATA имеет в каком-то смысле пространное определение, но здесь мы используем ее самую простую форму.

Код Синтаксис LOAD DATA

       

/path/to/filename — это каталог и имя файла, которые будут использоваться. table — это имя нашей таблицы. В этом случае, наш файл — ~/records.txt, а имя таблицы — developers.

mysql>LOAD DATA LOCAL INFILE ‘~/records.txt’ INTO TABLE developers;

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

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

Сработало хорошо. Однако, это просто вставляет записи, и не дает вам какого-либо контроля над MySQL. Множество веб-приложений используют скрипты sql для того чтобы настроить MySQL быстро и легко. Если вы хотите использовать скрипты sql, вам нужно запустить mysql в пакетном режиме, или использовать файл в качестве источника команд. Здесь приведен пример запуска mysql в пакетном режиме:

user $mysql -u root -h localhost -p < sqlfile

Как и в случае с LOAD DATA, убедитесь, что вы можете сказать что делает файл sqlfile. Невозможность этого может скомпрометировать вашу базу данных! Другим способом выполнения этого является использование команды source. Эта команда запустит команды из файла sql, находясь в интерактивном режиме mysql. Здесь показано как использовать sql file в качестве источника команд:

Если вы видите, что веб-приложение требует запуск sql файла, две команды выше могут быть использованы для выполнения данной задачи. Мы настроили нашу таблицу, как же нам проверить наши поля? Мы выполним это поиском в нашей таблице с помощью запросов.

Запросы к Таблицам MySQL

Запросы являются одной из основных функций любой базы данных SQL. Они помогают превратить данные в таблицах во что-то полезное. Большинство запросов выполняются командой SELECT . Команда SELECT — довольно сложна, и мы рассмотрим только три основных формы этой команды в данном документе.

Код Виды команды SELECT

     
   
     
     
   
     

Давайте-ка быстро рассмотрим первую форму команды. Она относительно проста и дает общее представление о вашей таблице. Мы продолжим и запустим ее, чтобы посмотреть какие данные у нас есть в настоящий момент.

mysql&gt;SELECT * FROM developers;

+-------------+-----------------------+----------------+
| name        | email                 | job            |
+-------------+-----------------------+----------------+
| Joe Smith   | joesmith@gentoo.org   | toolchain      |
| John Doe    | johndoe@gentoo.org    | portage        |
| Chris White | chriswhite@gentoo.org | documentation  |
| Sam Smith   | samsmith@gentoo.org   | amd64          |
| Jane Doe    | NULL                  | Outsourced job |
+-------------+-----------------------+----------------+
5 rows in set (0.00 sec)

Мы видим как данные, которые мы добавили с INSERT, так и вставленные LOAD DATA. Теперь, давайте предположим что мы просто хотим посмотреть запись для Chris White. Мы можем сделать это с помощью второй формы команды select, как показано ниже.

mysql&gt;SELECT * FROM developers WHERE name = ‘Chris White’;

+-------------+-----------------------+---------------+
| name        | email                 | job           |
+-------------+-----------------------+---------------+
| Chris White | chriswhite@gentoo.org | documentation |
+-------------+-----------------------+---------------+
1 row in set (0.08 sec)

Как предполагалось, выбрана отдельная запись, которую мы искали. Теперь, предположим, мы только хотели узнать должность и email адрес данной персоны, но не ее имя. Мы можем это выполнить с помощью третьей формы SELECT , как здесь и показано.

mysql&gt;SELECT email,job FROM developers WHERE name = ‘Chris White’;

+-----------------------+---------------+
| email                 | job           |
+-----------------------+---------------+
| chriswhite@gentoo.org | documentation |
+-----------------------+---------------+
1 row in set (0.04 sec)

Этот способ выбора намного легче в управлении, особенно с большими объемами информации, как мы увидим позже. А сейчас, будучи mysql пользователем root, мы обладаем неограниченными разрешениями делать с базой данных MySQL то, что мы захотим. В среде выполнения сервера, наличие пользователя с такими привилегиями может вызвать немало проблем. Для того, чтобы контролировать кто и что может делать с базами данных, мы установим привилегии.

Ввод запросов

Подсоединитесь к серверу, как было
описано выше. Таким образом никакая
база выбрана не будет, но это не
страшно. На данном этапе нам гораздо
важней разобраться с созданием
запросов, нежели сразу усложнять
себе жизнь созданием таблиц,
загрузкой в них данных и
извлечением их оттуда. В этом
разделе разъясняются основные
принципы ввода команд; на примере
нескольких запросов вы можете
поближе познакомиться с работой
mysql.

Ниже приведена простая команда,
запрашивающая у сервера информацию
об его версии и текущей дате.
Введите ее в командной строке
mysql&gt; и нажмите Enter:

mysql> SELECT VERSION(), CURRENT_DATE;

+--------------+--------------+
| VERSION()    | CURRENT_DATE |
+--------------+--------------+
| 3.22.20a-log | 1999-03-19   |
+--------------+--------------+
1 row in set (0.01 sec)
mysql>

Этот запрос иллюстрирует следующие
особенности mysql:

  • Команда обычно состоит из
    SQL-выражения, за которым следует
    точка с запятой. (Из этого правила
    есть и исключения — команды без
    точки с запятой. Одним из них
    является упомянутая выше команда
    QUIT, остальные мы
    рассмотрим позднее.)

  • Когда пользователь вводит
    команду, mysql отправляет
    ее серверу для выполнения и
    выводит на экран сначала
    результаты, а затем — новую строку
    mysql&gt;, что означает
    готовность к выполнению новых
    команд.

  • mysql выводит результаты работы
    запроса в виде таблицы (строк и
    столбцов). В первой строке этой
    таблицы содержатся заголовки
    столбцов, а в следующих строках —
    собственно результаты. Обычно
    заголовками столбцов становятся
    имена, полученные из таблиц базы.
    Если же извлекается не столбец
    таблицы, а значение выражения (как
    это происходит в приведенном выше
    примере), mysql дает
    столбцу имя запрашиваемого
    выражения.

  • mysql сообщает количество
    возвращаемых строк и время
    выполнения запроса, что позволяет
    в некоторой степени составить
    представление о
    производительности сервера. Эти
    значения обычно весьма
    впечатляют, так как представляют
    обычное (а не машинное время),
    кроме того, на них оказывает
    влияние загрузка сервера и
    скорость работы сети (для
    сокращения размеров листингов в
    остальных примерах этой главы
    строка «rows in set» удалена).

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

mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;

А это — еще один запрос. В нем
демонстрируется использование
mysql в качестве несложного
калькулятора:

mysql> SELECT SIN(PI()/4), (4+1)*5;
+-------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+-------------+---------+
|    0.707107 |      25 |
+-------------+---------+

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

mysql> SELECT VERSION(); SELECT NOW();
+--------------+
| VERSION()    |
+--------------+
| 3.22.20a-log |
+--------------+

+---------------------+
| NOW()               |
+---------------------+
| 1999-03-19 00:15:33 |
+---------------------+

Втискивать все команды в одну
строку совсем не обязательно, так
что создание длинных команд,
занимающих несколько строк, никаких
проблем не вызывает. Для
mysql признаком завершения
выражения является точка с запятой,
а не конец строки (другими словами,
mysql принимает команды без
форматирования: строки с командами
собираются, но не исполняются до тех
пор, пока программа не обнаружит
точку с запятой).

Вот пример несложного выражения,
занимающего несколько строк:

mysql> SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE;
+--------------------+--------------+
| USER()             | CURRENT_DATE |
+--------------------+--------------+
| joesmith@localhost | 1999-03-18   |
+--------------------+--------------+

Обратите внимание на то, как
изменилась метка командной строки
(с mysql&gt; на -&gt;)
после ввода первой строки этого
запроса. Таким образом программа mysql
показывает, что завершенного
выражения она пока что не получила и
ожидает его полного ввода. Эта метка
очень полезна, так как
предоставляет весьма ценную
информацию о состоянии программы. С
ее помощью всегда можно узнать, чего
ждет mysql.

Если вы решите отменить исполнение
набираемой команды, наберите
\c:

mysql> SELECT
    -> USER()
    -> \c
mysql>

Обратите внимание на метку: после
ввода команды \c она снова
принимает вид mysql&gt;, показывая, что
программа mysql перешла в
режим ожидания указаний.

В этой таблице приведены все
возможные варианта вида метки
командной строки и соответствующие
им состояния mysql:

МеткаЗначение
mysql>Ожидание новой команды.
    ->Ожидание следующей строки
многострочной команды.
    '>Ожидание следующей строки, сбор
строкового выражения,
начинающегося с одиночной
кавычки (‘'’).
    ">Ожидание следующей строки, сбор
строкового выражения,
начинающегося с двойной
кавычки (‘"’).

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

mysql> SELECT USER()
    ->

Если с вами произошло подобное (вы
думаете, что завершили команду, но
программа выдает только метку
-&gt;), то mysql,
вероятнее всего, ждет точки с
запятой. Не обратив внимание на
метку командной строки, можно
довольно долго ждать выполнения
команды, не понимая в чем дело. А
достаточно лишь поставить точку с
запятой, завершив команду, которую
mysql и выполнит:

mysql> SELECT USER()
    -> ;
+--------------------+
| USER()             |
+--------------------+
| joesmith@localhost |
+--------------------+

Метки ‘&gt; и «&gt;
используются при сборе строк. В MySQL
строки можно заключать как в
одинарные (‘’’), так и
в двойные (‘»’)
кавычки (можно, например, написать
‘hello’ или «goodbye»),
к тому же, mysql позволяет
вводить строковые выражения,
состоящие из нескольких строчек
текста. Метка ‘&gt; или
«&gt; обозначает, что вы
ввели строку, открывающуюся
символом кавычек ‘’’
или ‘»’, но еще не
ввели завершающую строковое
выражение закрывающую кавычку.

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

mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
    ">

Если ввести такую команду
SELECT, нажать Enter и
подождать результатов, ничего не
произойдет. Тут-то и нужно обратить
внимание на метку командной строки,
выглядящую вот так: «&gt;.
Это значит, что mysql ждет
ввода завершающей части строки.
(Теперь заметили ошибку в команде? В
строке «Smith нет
закрывающей кавычки.)

Что делать в этом случае? Проще
всего было бы отменить команду.
Однако теперь просто набрать
\c нельзя, так как
mysql примет эти символы за
часть собираемой строки! Вместо
этого нужно ввести закрывающие
кавычки (тем самым дав mysql
понять, что строка закончилась) и
лишь затем набрать \c:

mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
    "> "\c
mysql>

Метка командной строки снова примет
вид mysql&gt;, показывая
готовность mysql к выполнению команд.

Знать значение меток ‘&gt; и
«&gt; необходимо, так как
при вводе незавершенной строки все
последующие строки будут
игнорироваться mysql —
включая строку с командой
QUIT! Это может
основательно сбить с толку,
особенно если не знать, что для
отмены команды перед
соответствующей
последовательностью символов
необходимо поставить закрывающую
кавычку.

Структура таблиц для примеров

Все дальнейшие команды мы будем показывать на простом примере из трех таблиц. Допустим, мы — сеть фруктовых магазинов. У нас есть таблица со списком магазинов, таблица со списком товаров (фруктов) и таблица, в которой мы записываем какой фрукт в каком магазине продается и по какой цене.

С помощью команды Insert мы будем вставлять в эти таблицы новые данные разными способами. Итак, вот описание наших таблиц и начальные данные в них:

  • stores, таблица магазинов:
    +----+------------------+--------------+
| id | name             | address      |
+----+------------------+--------------+
|  1 | Магазин 1        | Адрес 1      |
|  2 | Магазин 2        | Адрес 2      |
|  3 | Магазин 3        | Адрес 3      |
+----+------------------+--------------+

  • fruits, таблица фруктов:
    +----+------------------+
| id | name             |
+----+------------------+
|  1 | Яблоко           |
|  2 | Мандарин         |
|  3 | Банан            |
+----+------------------+

  • sale, таблица наличия товаров и цены:
    +----------+----------+-------+-------+
| store_id | fruit_id | price | count |
+----------+----------+-------+-------+
|        1 |        3 |    60 |    20 |
|        2 |        2 |    80 |    10 |
|        3 |        1 |   120 |    12 |
+----------+----------+-------+-------+

Привилегии MySQL

Привилегии — это то, каким доступом обладают пользователи к базам данных, таблицам, почти ко всему. На данный момент в базе данных gentoo, учетная запись MySQL root — это единственная учетная запись, которая может получить к ней доступ, учитывая ее разрешения. Теперь, давайте создадим двух обычных пользователей, guest и admin, которые получат доступ к базе данных gentoo и будут работать с информацией, хранящейся в ней. Учетная запись guest будет ограниченной в правах. Все, что он сможет сделать, это получить информацию из базы данных, и только это. admin будет иметь те же самые права на управление, что и root, но только к базе данных gentoo (а не основным базам данных mysql). Перед тем как начать, давайте рассмотрим подробнее этот, в некотором смысле, упрощенный формат команды GRANT.

Creating users

The CREATE USER SQL statement will define users and set the authentication method, commonly by password but other plugins may be available.

An example CREATE USER command is:

Код CREATE USER Syntax

     

user is the name of the user and host is the hostname the user will be accessing from. In most cases, this will be localhost. To create our users for this example:

(admin)

mysql>CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';

(guest)

mysql>CREATE USER 'guest'@'localhost' IDENTIFIED BY 'password';

ВажноA host of ‘localhost’ does not mean DNS localhost (127.0.0.1) to MySQL. Instead, it refers to the UNIX socket connection and not TCP/IP.

Предоставление Привилегий Командой GRANT

Let’s have a closer look at this somewhat simplified format of the GRANT command.

Код Синтаксис команды GRANT

        

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

  • ALL — Дает полный контроль базы данных со всеми привилегиями
  • CREATE — Позволяет пользователям создавать таблицы
  • SELECT — Позволяет пользователям делать запросы к таблицам
  • INSERT — Позволяет пользователям вставлять данные в таблицу
  • SHOW DATABASES — Позволяет пользователям просматривать список баз данных
  • USAGE — Пользователь не имеет привилегий
  • GRANT OPTION — Позволяет пользователям предоставлять привилегии

ЗаметкаЕсли вы запустили MySQL чтобы передавать данные веб-приложению, CREATE, SELECT, INSERT (обсуждалось здесь же), DELETE и UPDATE (для получения дальнейшей информации посмотрите раздел Справочного Руководства MySQL — Синтаксис GRANT и REVOKE) — это единственные разрешения, которые вам, скорее всего, понадобятся. Большинство делает ошибку, предоставляя все разрешения, когда в этом нет действительной необходимости. Сверьтесь с разработчиками приложения, чтобы посмотреть, не вызовут ли такие разрешения проблемы в общей работе.

Для пользователя admin, подойдет уровень ALL. Для пользователя guest, SELECT будет достаточно для доступа только на чтение. database — это база данных, над которой пользователь, как мы того желаем, должен иметь эти разрешения. В этом примере, базой данных является gentoo. .* означает все таблицы. Если бы вы хотели, вы могли бы установить права доступа для каждой из таблиц. user — это имя пользователя, а host — имя хоста, с которого пользователь будет получать доступ. В большинстве случаев, это будет localhost. И наконец, password — это пароль пользователя. Учитывая эту информацию, давайте продолжим и создадим наших пользователей.

mysql>GRANT ALL ON gentoo.* TO 'admin'@'localhost' IDENTIFIED BY 'password';

mysql>GRANT SELECT ON gentoo.* TO 'guest'@'localhost' IDENTIFIED BY 'password';

Теперь, когда мы настроили наших пользователей, давайте их протестируем. Сначала, мы завершим работу mysql, введя quit в командной строке:

Теперь мы снова в консоли. Сейчас, когда пользователи созданы, давайте продолжим и посмотрим что они могут делать.

Проверка Пользовательских Разрешений

Сейчас мы должны попытаться войти в качестве пользователя guest. В настоящий момент, пользователь guest имеет только привилегии SELECT. Это, просто-напросто, сводится к способности поиска в базе данных, и ничему другому. Продолжайте и войдите как пользователь guest.

user $mysql -u guest -h localhost -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.25
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

Теперь мы должны протестировать пользовательские ограничения. Давайте переключимся к базе данных gentoo:

mysql>USE gentoo;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed

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

mysql&gt;CREATE TABLE test (test VARCHAR(20), foobar VARCHAR(2));

ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'

Как видите, эта функция не сработала, так как наш пользователь не имеет соответствующих прав доступа. Однако, одной из привилегий, которую мы предоставили, является выражение SELECT. Давайте попробуем:

mysql&gt;SELECT * FROM developers;

+-------------+-----------------------+----------------+
| name        | email                 | job            |
+-------------+-----------------------+----------------+
| Joe Smith   | joesmith@gentoo.org   | toolchain      |
| John Doe    | johndoe@gentoo.org    | portage        |
| Chris White | chriswhite@gentoo.org | documentation  |
| Sam Smith   | samsmith@gentoo.org   | amd64          |
| Jane Doe    | NULL                  | Outsourced job |
+-------------+-----------------------+----------------+
5 rows in set (0.00 sec)

Команда завершилась успешно, и мы мельком увидели, что могут делать пользовательские разрешения. Мы, однако же, также создали и учетную запись admin. Она была создана для того чтобы показать, что даже пользователи, которым предоставлены все права, все же могут иметь ограничения. Завершите работу MySQL и войдите под учетной записью admin.

user $mysql -u admin -h localhost -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.25
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

Для начала, мы попытаемся создать новую базу данных от учетной записи admin. Этот пользователь будет иметь права доступа схожие с учетной записью root в MySQL, и будет способен выполнить любые изменения выбранной базы данных gentoo. Это протестирует права доступа данного пользователя к главной базе данных MySQL. Вспомните, что ранее мы установили разрешения только для определенной базы данных.

mysql&gt;CREATE DATABASE gentoo2;

ERROR 1044: Access denied for user: 'admin@localhost' to database 'gentoo2'

В самом деле, пользователь admin не может создавать базы данных в основной базе данных MySQL, вопреки всем своим разрешениям к базе данных gentoo. Однако, мы все еще способны использовать учетную запись admin для изменения базы данных gentoo, как показано на этом примере вставки данных.

mysql>USE gentoo;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> INSERT INTO developers VALUES('Bob Simmons', 'bobsimmons@gentoo.org', 'python');
Query OK, 1 row affected (0.08 sec)

Пользователь admin может получить доступ к базе данных по желанию. Иногда, мы должны избавиться от пользовательских разрешений. Примером может быть все что угодно, от пользователя, вызывающего проблемы, до уволенного сотрудника. Давайте рассмотрим как отключить пользовательские разрешения командой REVOKE.

Удаление Прав Доступа Пользователя Командой REVOKE

Команда REVOKE позволяет нам запретить доступ пользователю. Мы можем запретить или все права на доступ, или определенные права. В действительности, формат весьма схож с командой GRANT .

Код Синтаксис REVOKE

     

Здесь, параметры объясняются в разделе команды GRANT . Однако же, в этом разделе, мы собираемся запретить пользователю доступ полностью. Давайте предположим, мы обнаружили, что учетная запись guest вызывает некоторые проблемы с безопасностью. Мы решаем отозвать все привилегии. Мы заходим под учетной записью root и делаем то, что необходимо.

mysql>REVOKE ALL ON gentoo.* FROM 'guest'@'localhost';

Query OK, 0 rows affected (0.00 sec)

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

Давайте теперь выйдем и попытаемся зайти в качестве пользователя guest.

user $mysql -u guest -h localhost -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.25
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

Хотя мы и были способны выполнить вход, наши права доступа к gentoo теперь исчезли.

mysql>USE gentoo;

ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'

И наш проблемный пользователь больше не может получить доступ к базе данных gentoo. Пожалуйста, заметьте, что пользователь все еще может выполнить вход. Это потому что он остается в основной базе данных MySQL. Давайте рассмотрим как полностью удалить учетную запись командой DELETE и взглянем на таблицу пользователей MySQL.

Удаление Учетных Записей с Использованием DELETE

Таблица пользователей MySQL является перечислением всех пользователей и информации о них. Убедитесь, что вы выполнили вход под root. Затем продолжайте и используйте основную базу данных MySQL.

DROP USER will delete the record in the user table and all privilege tables. Let’s go ahead and do that:

mysql>DROP USER 'guest'@'localhost';

Query OK, 1 row affected (0.07 sec)

Кажется, сработало на отлично. Давайте это протестируем, выйдя и попытаясь войти как пользователь guest.

user $mysql -u guest -h localhost -p

Enter password:
ERROR 1045: Access denied for user: 'guest@localhost' (Using password: YES)

Наш пользователь успешно удален!

Заключение

В то время как это руководство сфокусировано, в основном, на настройке MySQL из командной строки, доступно несколько альтернатив с графическим интерфейсом:

  • phpMyAdmin — Популярный инструмент администрирования MySQL, основанный на php.
  • mysqlnavigator — Интерфейс QT к MySQL.
  • gmyclient — MySQL клиент, основанный на GNOME.
  • knoda — клиент MySQL для KDE.

Это завершает вводное руководство к MySQL. Я надеюсь это дало вам лучшее понимание основ MySQL и настройки базы данных.


This page is based on a document formerly found on our main website gentoo.org.
The following people contributed to the original document: Chris White, Shyam Mani, Xavier Neys
They are listed here because wiki history does not allow for any external attribution. If you edit the wiki article, please do not add yourself here; your contributions are recorded on each article’s associated history page.

Базовое использование команды INSERT

Начнем с самого простого использования команды INSERT в MySQL. Добавим новый фрукт в справочник фруктов. Команда будет выглядеть так:

    INSERT INTO fruits VALUES (4, 'Апельсин');

Проверяем таблицу фруктов и видим, что появилась новая запись:

    +----+------------------+
| id | name             |
+----+------------------+
|  1 | Яблоко           |
|  2 | Мандарин         |
|  3 | Банан            |
|  4 | Апельсин         |
+----+------------------+


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

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

    INSERT INTO stores (id, name) VALUES (4, 'Магазин 4');

Обратите внимание, что в таблице stores у нас еще есть столбец с адресом, но мы его не указали. Посмотрим, что вставилось в таблицу:

    +----+------------------+--------------+
| id | name             | address      |
+----+------------------+--------------+
|  1 | Магазин 1        | Адрес 1      |
|  2 | Магазин 2        | Адрес 2      |
|  3 | Магазин 3        | Адрес 3      |
|  4 | Магазин 4        | NULL         |
+----+------------------+--------------+

Мы видим указанные нами значения, а в поле address вставилось значение NULL.

Запросы проекта ‘Близнецы’ (Twin Project)

3.7.1. Поиск нераспределенных близнецов
3.7.2. Вывод таблицы состояний пар близнецов

В Analytikerna и Lentus мы проводили сбор и
систематизацию данных в рамках
крупного исследовательского
проекта. Этот проект
разрабатывается совместно
Институтом экологической медицины
Karolinska Institutet, Стокгольм и отделением
клинических исследований в области
старения и психологии университета
Южной Калифорнии.

В проекте предусмотрен этап
опросов, на котором происходит
опрос по телефону всех проживающих
в Швеции близнецов старше 65 лет.
Близнецы, отвечающие критериям
отбора, переходят на следующий этап.
На этом этапе желающих участвовать
в проекте близнецов посещает врач с
медсестрой. Проводятся физические и
нейропсихологические исследования,
лабораторные анализы,
неврологическое обследование,
оценка психологического состояния,
а также собираются данные по
истории семьи. Кроме того,
осуществляется сбор информации о
медицинских и экологических
факторах риска.

Дополнительную информацию о
проекте вы можете получить по
адресу:
http://www.imm.ki.se/TWIN/TWINUKW.HTM

На поздних этапах
администрирование проекта
осуществляется с помощью
web-интерфейса, написанного на
Perl и MySQL.

Каждую ночь собранные во время
интервью данные заносятся в базу
данных MySQL.

Поиск нераспределенных близнецов

Этот запрос определяет, которые из
близнецов переходят во второй этап
проекта:

SELECT
        CONCAT(p1.id, p1.tvab) + 0 AS tvid,
        CONCAT(p1.christian_name, " ", p1.surname) AS Name,
        p1.postal_code AS Code,
        p1.city AS City,
        pg.abrev AS Area,
        IF(td.participation = "Aborted", "A", " ") AS A,
        p1.dead AS dead1,
        l.event AS event1,
        td.suspect AS tsuspect1,
        id.suspect AS isuspect1,
        td.severe AS tsevere1,
        id.severe AS isevere1,
        p2.dead AS dead2,
        l2.event AS event2,
        h2.nurse AS nurse2,
        h2.doctor AS doctor2,
        td2.suspect AS tsuspect2,
        id2.suspect AS isuspect2,
        td2.severe AS tsevere2,
        id2.severe AS isevere2,
        l.finish_date
FROM
        twin_project AS tp
        /* For Twin 1 */
        LEFT JOIN twin_data AS td ON tp.id = td.id
                  AND tp.tvab = td.tvab
        LEFT JOIN informant_data AS id ON tp.id = id.id
                  AND tp.tvab = id.tvab
        LEFT JOIN harmony AS h ON tp.id = h.id
                  AND tp.tvab = h.tvab
        LEFT JOIN lentus AS l ON tp.id = l.id
                  AND tp.tvab = l.tvab
        /* For Twin 2 */
        LEFT JOIN twin_data AS td2 ON p2.id = td2.id
                  AND p2.tvab = td2.tvab
        LEFT JOIN informant_data AS id2 ON p2.id = id2.id
                  AND p2.tvab = id2.tvab
        LEFT JOIN harmony AS h2 ON p2.id = h2.id
                  AND p2.tvab = h2.tvab
        LEFT JOIN lentus AS l2 ON p2.id = l2.id
                  AND p2.tvab = l2.tvab,
        person_data AS p1,
        person_data AS p2,
        postal_groups AS pg
WHERE
        /* p1 gets main twin and p2 gets his/her twin. */
        /* ptvab is a field inverted from tvab */
        p1.id = tp.id AND p1.tvab = tp.tvab AND
        p2.id = p1.id AND p2.ptvab = p1.tvab AND
        /* Just the sceening survey */
        tp.survey_no = 5 AND
        /* Skip if partner died before 65 but allow emigration (dead=9) */
        (p2.dead = 0 OR p2.dead = 9 OR
         (p2.dead = 1 AND
          (p2.death_date = 0 OR
           (((TO_DAYS(p2.death_date) - TO_DAYS(p2.birthday)) / 365)
            >= 65))))
        AND
        (
        /* Twin is suspect */
        (td.future_contact = 'Yes' AND td.suspect = 2) OR
        /* Twin is suspect - Informant is Blessed */
        (td.future_contact = 'Yes' AND td.suspect = 1
                                   AND id.suspect = 1) OR
        /* No twin - Informant is Blessed */
        (ISNULL(td.suspect) AND id.suspect = 1
                            AND id.future_contact = 'Yes') OR
        /* Twin broken off - Informant is Blessed */
        (td.participation = 'Aborted'
         AND id.suspect = 1 AND id.future_contact = 'Yes') OR
        /* Twin broken off - No inform - Have partner */
        (td.participation = 'Aborted' AND ISNULL(id.suspect)
                                      AND p2.dead = 0))
        AND
        l.event = 'Finished'
        /* Get at area code */
        AND SUBSTRING(p1.postal_code, 1, 2) = pg.code
        /* Not already distributed */
        AND (h.nurse IS NULL OR h.nurse=00 OR h.doctor=00)
        /* Has not refused or been aborted */
        AND NOT (h.status = 'Refused' OR h.status = 'Aborted'
        OR h.status = 'Died' OR h.status = 'Other')
ORDER BY
        tvid;

Дадим к этому запросу некоторые
пояснения:

  • CONCAT(p1.id, p1.tvab) + 0 AS tvid

    Сортируем по взаимосвязи
    id и tvab в
    числовом порядке. Прибавление
    нуля к результату заставляет MySQL
    обращаться с результатом как с
    числом.

  • Определяет пару близнецов.
    Является ключевым полем всех
    таблиц.

  • Определяет близнеца в паре.
    Принимает значение 1 или 2.

  • Отрицание tvab. Если
    значение tvab равно 1,
    значение этого поля — 2, и
    наоборот. Данное поле облегчает
    MySQL задачу оптимизации запроса и
    экономит время ввода данных.

Этот запрос иллюстрирует, помимо
всего прочего, сравнение значений
одной таблицы с помощью команды
JOIN (p1 и p2). В этом примере
таким образом проверяется, не умер
ли один из близнецов до достижения
им 65-летнего возраста. Если так и
произошло, строка не попадает в
список возвращаемых.

Все вышеприведенные поля имеются
во всех таблицах, в которых
хранится относящаяся к близнецам
информация. Ключевыми полями для
ускорения работы запросов
назначены id,tvab (во всех
таблицах), а также id,ptvab
(person_data).

На нашем рабочем компьютере (200МГц
UltraSPARC) этот запрос возвращает 150-200
строк, и его выполнение занимает
менее секунды.

Текущее количество строк в
таблицах, использовавшихся выше:

ТаблицаСтроки
person_data71074
lentus5291
twin_project5286
twin_data2012
informant_data663
harmony381
postal_groups100

Вывод таблицы состояний пар близнецов

Каждый опрос оканчивается кодом
состояния, называющимся
event («событие»).
Приведенный здесь запрос выводит
данные обо всех парах близнецов,
объединенные по полю event.
Таблица наглядно показывает, с
каким количеством пар близнецов
работа полностью завершена, а в
каком количестве пар работа с
одним близнецом завершена, а с
другим — нет и т.п.

SELECT
        t1.event,
        t2.event,
        COUNT(*)
FROM
        lentus AS t1,
        lentus AS t2,
        twin_project AS tp
WHERE
        /* We are looking at one pair at a time */
        t1.id = tp.id
        AND t1.tvab=tp.tvab
        AND t1.id = t2.id
        /* Just the sceening survey */
        AND tp.survey_no = 5
        /* This makes each pair only appear once */
        AND t1.tvab='1' AND t2.tvab='2'
GROUP BY
        t1.event, t2.event;

Вставка значения по умолчанию

Обычно, если в MySQL для столбца не указано значение, то команда INSERT INTO подставляет NULL. Но если при создании таблицы у этого столбца было указано значение по умолчанию (default), тогда подставится именно оно. В нашей таблице sale у столбца count значение по-умолчанию равно единице. Проверим это, вставив новую запись без указания этого столбца:

    INSERT INTO sale (store_id, fruit_id, price) VALUES(4, 7, 50);

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

    +----------+----------+--------+-------+
| store_id | fruit_id | price  | count |
+----------+----------+--------+-------+
|        1 |        3 |     60 |    20 |
|        2 |        2 |     80 |    10 |
|        3 |        1 |    120 |    12 |
|        1 |        1 |     89 |     3 |
|        2 |        4 | 128.78 |     5 |
|        4 |        7 |     50 |     1 |
+----------+----------+--------+-------+

Создаем базу MySQL в облаке

Прежде чем начать работать с командой Insert, нам нужна база данных MySQL. Чтобы не заниматься долгой установкой и настройкой, мы создадим управляемую БД на платформе Selectel. Если сервер MySQL у вас уже установлен, можете сразу переходить к следующему разделу.

Заходим в личный кабинет, в разделе «Облачная платформа» переходим к «Базам данных». Нажимаем кнопку «Создать кластер».

Создание базы данных в панели Selectel

На следующем экране выбираем параметры новой базы. Выбираем «СУБД» — MySQL. Далее необходимо выбрать конфигурацию сервера: нам будет достаточно 2 vCPU, 4 ГБ оперативной памяти и 32 ГБ диска. Обратите внимание на раздел «Сеть» — у вас должна быть выбрана публичная сеть, чтобы к базе данных можно было подключиться из интернета. Остальные параметры можно оставить по умолчанию.

Конфигурация для нового кластера БД

Немного подождем, пока сервер создается. Когда он перейдет в статус ACTIVE, выберите его и зайдите в раздел настроек. Сначала перейдем на вкладку «Пользователи» и создадим нового пользователя, который сможет подключаться к базе данных. Не забудьте записать имя пользователя и пароль, они будут нужны для подключения.

Создание пользователя

Затем перейдем на следующую вкладку — «Базы данных». Нам нужно создать базу, в которой мы будем работать. Для этого нажмем кнопку «Создать базу данных». Запишите название созданной БД, оно нам будет нужно для подключения.

Создание базы данных MySQL

Когда база создастся, нужно дать нашему пользователю права на нее. На этой же вкладке нажмем кнопку «Добавить» и выберем недавно созданного пользователя.

Добавление доступа к базе

Все, БД настроена и к ней можно подключаться. Чтобы узнать параметры подключения, перейдите на вкладку «Настройки» и там в самом низу в разделе «Подключение» указаны нужные параметры. Вы можете пользоваться консольным клиентом или любой графической утилитой. Мы будем показывать на примере консольного клиента, поэтому строка для подключения будет выглядеть вот так:

    mysql --host=109.71.10.30 \
      --port=6033 \
      --user=chrystal \
      --password \
      --database=db

После ввода этой команды консоль попросит нас ввести пароль. Вот и все, мы подключились к БД.

Использование mysql в пакетном
режиме

В предыдущих разделах было
показано, как использовать
mysql в интерактивном
режиме, вводя запросы и тут же
просматривая результаты. Запускать
mysql можно и в пакетном
режиме. Для этого нужно собрать все
команды в один файл и передать его
на исполнение mysql:

shell> mysql < batch-file

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

dos> mysql -e "source batch-file"

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

shell> mysql -h host -u user -p < batch-file
Enter password: ********

Работая с mysql таким
образом, вы, в сущности, создаете
сценарий и затем исполняете его.

Если нужно продолжать обработку
сценария даже при обнаружении в нем
ошибок, воспользуйтесь параметром
командной строки —force.

Зачем вообще нужны сценарии? Причин
тому несколько:

  • При необходимости частого
    (ежедневного или хотя бы
    еженедельного) запуска одного и
    того же запроса сценарий
    позволяет избавиться от
    многократного набора этого
    запроса.

  • Можно создавать новые запросы,
    подобные уже существующим, просто
    копируя и затем изменяя файлы
    сценариев.

  • Пакетный режим может пригодиться
    и при разработке особенно длинных
    запросов, а именно — многострочных
    команд или больших
    последовательностей команд. В
    таком деле одна допущенная ошибка
    может привести к необходимости
    повторного набора большого
    количества текста. Зато при
    работе со сценарием легко
    исправить ошибку и запустить
    запрос на повторное исполнение.

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

    shell> mysql < batch-file > mysql.out
    
  • Свой сценарий вы можете дать
    кому-нибудь еще, чтобы он тоже мог
    воспользоваться содержащимися в
    сценарии командами.

  • В некоторых случаях работать в
    интерактивном режиме просто не
    получается. И здесь без пакетного
    режима не обойтись.

По умолчанию при работе с
mysql в пакетном режиме
используется более сжатый формат
вывода результатов, чем при
интерактивной работе. В
интерактивном режиме результаты
работы запроса SELECT DISTINCT species FROM
pet выглядят так:

+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+

А в пакетном — вот так:

species
bird
cat
dog
hamster
snake

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

В командную строку mysql
можно включать и сценарии — при
помощи команды source:

mysql> source filename;

Вставка записи в определенные партиции

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

  • p0 для записей, у которых count от 0 до 100 
  • p1 для записей, у которых count от 101 до 150.

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

    INSERT INTO sale PARTITION (p0) VALUES (1, 9, 130, 10);

Кроме того, в одной команде мы можем вставлять данные сразу в несколько партиций. Это делается так:

    INSERT INTO sale PARTITION (p0, p1) VALUES (4, 8, 90, 10), (3, 7, 50, 120);

Вставка с выражением

Во всех предыдущих примерах мы указывали явное значение для столбца. Но в MySQL можно использовать арифметические выражения, например сумму, разность, умножение и так далее. Допустим, мы хотим добавить запись в таблицу sale о том что в магазине № 2 продаются апельсины по 137 рублей. Но потом мы решаем, что хотим сделать скидку 6%. Чтобы не высчитывать ее самим, мы можем использовать такой запрос:

    INSERT INTO sale VALUES (2, 4, 137*0.94, 5);

Мы умножаем 137 рублей на 0.94, это и есть скидка 6%. Проверим что у нас получилось:

    +----------+----------+--------+-------+
| store_id | fruit_id | price  | count |
+----------+----------+--------+-------+
|        1 |        3 |     60 |    20 |
|        2 |        2 |     80 |    10 |
|        3 |        1 |    120 |    12 |
|        1 |        1 |     89 |     3 |
|        2 |        4 | 128.78 |     1 |
+----------+----------+--------+-------+

Вставка или обновление при дубликате

В MySQL нельзя добавить строку в таблицу, у которой дублируется первичный ключ (primary key). В наших таблицах stores и fruits поле id — это первичный ключ. Поэтому если мы попробуем добавить в таблицу fruit значение с id=1, то MySQL выдаст ошибку.

Но существует похожая на INSERT команда — REPLACE, которая умеет перезаписывать значения. Она работает как INSERT и UPDATE одновременно: если в таблице еще нет записи с таким первичным ключом, команда создаст новую запись; а если уже есть — заменит ее.

    REPLACE INTO fruits VALUES (1, 'Лимон');

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

    +----+------------------+
| id | name             |
+----+------------------+
|  1 | Лимон            |
|  2 | Мандарин         |
|  3 | Банан            |
|  4 | Апельсин         |
|  5 | Киви             |
|  6 | Виноград         |
|  7 | Груша            |
|  8 | Ананас           |
|  9 | Вишня            |
+----+------------------+

Установка

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

Первый шаг – установить SQL

Мы будем использовать PostgreSQL (Postgres) достаточно распространенный SQL диалект. Для этого откроем страницу загрузки, выберем операционную систему (в моем случае Windows), и запустим установку. Если вы установите пароль для вашей базы данных, постарайтесь сразу не забыть его, он нам дальше понадобится. Поскольку наша база будет локальной, можете использовать простой пароль, например: admin.

Следующий шаг – установка pgAdmin

pgAdmin – это графический интерфейс пользователя (GUI – graphical user interface), который упрощает взаимодействие с базой данных PostgreSQL. Перейдите на страницу загрузки, выберите вашу операционную систему и следуйте указаниям (в статье используется Postgres 14 и pgAdmin 4 v6.3.).

После установки обоих компонентов открываем pgAdmin и нажимаем Add new server. На этом шаге установится соединение с существующим сервером, именно поэтому необходимо сначала установить Postgres. Я назвал свой сервер home и использовал пароль, указанный при установке.

Теперь всё готово к созданию таблиц. Давайте создадим набор таблиц, которые моделируют школу. Нам необходимы таблицы: ученики, классы, оценки. При создании модели данных необходимо учитывать, что в одном классе может быть много учеников, а у ученика может быть много оценок (такое отношение называется «один ко многим»).

Мы можем создать таблицы напрямую в pgAdmin, но вместо этого мы напишем код, который можно будет использовать в дальнейшем, например, для пересоздания таблиц. Для создания запроса, который создаст наши таблицы, нажимаем правой кнопкой мыши на postgres (пункт расположен в меню слева home Databases (1) postgres и далее выбираем Query Tool.

🐘 Руководство по SQL для начинающих. Часть 1: создание базы данных, таблиц и установка связей между таблицами

Начнем с создания таблицы классов (classrooms). Таблица будет простой: она будет содержать идентификатор id и имя учителя – teacher. Напишите следующий код в окне запроса (query tool) и запустите (run или F5).

        DROP TABLE IF EXISTS classrooms CASCADE;

CREATE TABLE classrooms (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    teacher VARCHAR(100)
);
    

В первой строке фрагмент DROP TABLE IF EXISTS classrooms удалит таблицу classrooms, если она уже существует. Важно учитывать, что Postgres, не позволит нам удалить таблицу, если она имеет связи с другими таблицами, поэтому, чтобы обойти это ограничение (constraint) в конце строки добавлен оператор CASCADE. CASCADE – автоматически удалит или изменит строки из зависимой таблицы, при внесении изменений в главную. В нашем случае нет ничего страшного в удалении таблицы, поскольку, если мы на это пошли, значит мы будем пересоздавать всё с нуля, и остальные таблицы тоже удалятся.

Добавление DROP TABLE IF EXISTS перед CREATE TABLE позволит нам систематизировать схему нашей базы данных и создать скрипты, которые будут очень удобны, если мы захотим внести изменения – например, добавить таблицу, изменить тип данных поля и т. д. Для этого нам просто нужно будет внести изменения в уже готовый скрипт и перезапустить его.

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

Также вы могли обратить внимание на четвертую строчку. Здесь мы определили, что колонка id является первичным ключом (primary key), что означает следующее: в каждой записи в таблице это поле должно быть заполнено и каждое значение должно быть уникальным. Чтобы не пришлось постоянно держать в голове, какое значение id уже было использовано, а какое – нет, мы написали GENERATED ALWAYS AS IDENTITY, этот приём является альтернативой синтаксису последовательности (CREATE SEQUENCE). В результате при добавлении записей в эту таблицу нам нужно будет просто добавить имя учителя.

И в пятой строке мы определили, что поле teacher имеет тип данных VARCHAR (строка) с максимальной длиной 100 символов. Если в будущем нам понадобится добавить в таблицу учителя с более длинным именем, нам придется либо использовать инициалы, либо изменять таблицу (alter table).

Теперь давайте создадим таблицу учеников (students). Новая таблица будет содержать: уникальный идентификатор (id), имя ученика (name), и внешний ключ (foreign key), который будет указывать (references) на таблицу классов.

        DROP TABLE IF EXISTS students CASCADE;

CREATE TABLE students (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100),
    classroom_id INT,
    CONSTRAINT fk_classrooms
        FOREIGN KEY(classroom_id)
        REFERENCES classrooms(id)
);
    

И снова мы перед созданием новой таблицы удаляем старую, если она существует, добавляем поле id, которое автоматически увеличивает своё значение и имя с типом данных VARCHAR (строка) и максимальной длиной 100 символов. Также в эту таблицу мы добавили колонку с идентификатором класса (classroom_id), и с седьмой по девятую строку установили, что ее значение указывает на колонку id в таблице классов (classrooms).

Мы определили, что classroom_id является внешним ключом. Это означает, что мы задали правила, по которым данные будут записываться в таблицу учеников (students). То есть Postgres на данном этапе не позволит нам вставить строку с данными в таблицу учеников (students), в которой указан идентификатор класса (classroom_id), не существующий в таблице classrooms. Например: у нас в таблице классов 10 записей (id с 1 до 10), система не даст нам вставить данные в таблицу учеников, у которых указан идентификатор класса 11 и больше.

Невозможно вставить данные, поскольку в таблице классов нет записи с id = 1
        INSERT INTO students
    (name, classroom_id)
VALUES
    ('Matt', 1);

/*
ERROR: insert or update on table "students" violates foreign
    key constraint "fk_classrooms"
DETAIL: Key (classroom_id)=(1) is not present in table
    "classrooms".
SQL state: 23503
*/
    

Теперь давайте добавим немного данных в таблицу классов (classrooms). Так как мы определили, что значение в поле id будет увеличиваться автоматически, нам нужно только добавить имена учителей.

        INSERT INTO classrooms
    (teacher)
VALUES
    ('Mary'),
    ('Jonah');

SELECT * FROM classrooms;

/*
 id | teacher
 -- | -------
  1 | Mary
  2 | Jonah
*/
    

Прекрасно! Теперь у нас есть записи в таблице классов, и мы можем добавить данные в таблицу учеников, а также установить нужные связи (с таблицей классов).

        INSERT INTO students
    (name, classroom_id)
 VALUES
    ('Adam', 1),
    ('Betty', 1),
    ('Caroline', 2);

SELECT * FROM students;

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
  2 | Betty    |            1
  3 | Caroline |            2
*/
    

Но что же случится, если у нас появится новый ученик, которому ещё не назначили класс? Неужели нам придется ждать, пока станет известно в каком он классе, и только после этого добавить его запись в базу данных?

Конечно же, нет. Мы установили внешний ключ, и он будет блокировать запись, поскольку ссылка на несуществующий id класса невозможна, но мы можем в качестве идентификатора класса (classroom_id) передать null. Это можно сделать двумя способами: указанием null при записи значений, либо просто передачей только имени.

        -- явно определим значение NULL
INSERT INTO students
    (name, classroom_id)
VALUES
    ('Dina', NULL);

-- неявно определим значение NULL
INSERT INTO students
    (name)
VALUES
    ('Evan');

SELECT * FROM students;

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
  2 | Betty    |            1
  3 | Caroline |            2
  4 | Dina     |       [null]
  5 | Evan     |       [null]
*/
    

И наконец, давайте заполним таблицу успеваемости. Этот параметр, как правило, формируется из нескольких составляющих – домашние задания, участие в проектах, посещаемость и экзамены. Мы будем использовать две таблицы. Таблица заданий (assignments), как понятно из названия, будет содержать данные о самих заданиях, и таблица оценок (grades), в которой мы будем хранить данные о том, как ученик выполнил эти задания.

        DROP TABLE IF EXISTS assignments CASCADE;
DROP TABLE IF EXISTS grades CASCADE;

CREATE TABLE assignments (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    category VARCHAR(20),
    name VARCHAR(200),
    due_date DATE,
    weight FLOAT
);

CREATE TABLE grades (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    assignment_id INT,
    score INT,
    student_id INT,
    CONSTRAINT fk_assignments
        FOREIGN KEY(assignment_id)
        REFERENCES assignments(id),
    CONSTRAINT fk_students
        FOREIGN KEY(student_id)
        REFERENCES students(id)
);
    

Вместо того чтобы вставлять данные вручную, давайте загрузим их с помощью CSV-файла. Вы можете скачать файл из этого репозитория или создать его самостоятельно. Имейте в виду, чтобы разрешить pgAdmin доступ к данным, вам может понадобиться расширить права доступа к папке (в моем случае – это папка db_data).

        COPY assignments(category, name, due_date, weight)
FROM 'C:/Users/mgsosna/Desktop/db_data/assignments.csv'
DELIMITER ','
CSV HEADER;
/*
COPY 5
Query returned successfully in 118 msec.
*/

COPY grades(assignment_id, score, student_id)
FROM 'C:/Users/mgsosna/Desktop/db_data/grades.csv'
DELIMITER ','
CSV HEADER;
/*
COPY 25
Query returned successfully in 64 msec.
*/
    

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

        SELECT
    c.teacher,
    a.category,
    ROUND(AVG(g.score), 1) AS avg_score
FROM
    students AS s
INNER JOIN classrooms AS c
    ON c.id = s.classroom_id
INNER JOIN grades AS g
    ON s.id = g.student_id
INNER JOIN assignments AS a
    ON a.id = g.assignment_id
GROUP BY
    1,
    2
ORDER BY
    3 DESC;

/*
 teacher | category  | avg_score
 ------- | --------- | ---------
 Jonah   |  project  |     100.0
 Jonah   |  homework |      94.0
 Jonah   |  exam     |      92.5
 Mary    |  homework |      78.3
 Mary    |  exam     |      76.0
 Mary    |  project  |      69.5
*/
    

Отлично! Мы установили, настроили и наполнили базу данных.

***

Итак, в этой статье мы научились:

  • создавать базу данных;
  • наполнять таблицы данными;
  • устанавливать связи между таблицами;

Теперь у нас всё готово, чтобы пробовать более сложные возможности SQL. Мы начнем с возможностей синтаксиса, которые, вероятно, вам еще не знакомы и которые откроют перед вами новые границы в написании SQL-запросов. Также мы разберем некоторый виды соединений таблиц (JOIN) и способы организации запросов в тех случаях, когда они занимают десятки или даже сотни строк.

В следующей части мы разберем:

  • виды фильтраций в запросах;
  • запросы с условиями типа if-else;
  • новые виды соединений таблиц;
  • функции для работы с массивами;

Вставка данных из другой таблицы

В качестве источника данных для вставки можно использовать другую таблицу. Предположим, у нас есть таблица fruits_new, где хранится список фруктов, которые мы заказали у поставщиков, но эти фрукты еще не продаются в наших магазинах. И вот наконец-то нам привезли два новых фрукта, которые мы так давно ждали. Но вместо того, чтобы переписывать эти названия в команду INSERT, мы можем просто использовать таблицу fruits_new и загружать данные из нее. Для этого нам понадобится оператор SELECT:

    INSERT INTO fruits
SELECT * FROM fruits_new
WHERE id = 8;

Обратите внимание, что мы не указываем названия столбцов: из какого взять и в какой вносить. Поэтому такой способ подойдет только если структура таблиц одинакова, или в таблице-источнике меньше столбцов. Чаще всего используется другой синтаксис, когда столбцы указаны явно:

    INSERT INTO fruits (name, id)
SELECT name, id FROM fruits_new
WHERE id = 9;


Проверим результат. После выполнения этих двух команд у нас должно появиться две новых строки:

    +----+------------------+
| id | name             |
+----+------------------+
|  1 | Яблоко           |
|  2 | Мандарин         |
|  3 | Банан            |
|  4 | Апельсин         |
|  5 | Киви             |
|  6 | Виноград         |
|  7 | Груша            |
|  8 | Ананас           |
|  9 | Вишня            |
+----+------------------+

Создание и удаление таблиц

Последнее обновление: 04.05.2018

Создание таблицы

Для создания таблиц используется команда CREATE TABLE. Эта команды применяет ряд операторов, которые определяют столбцы
таблицы и их атрибуты. Общий формальный синтаксис команды CREATE TABLE:

CREATE TABLE название_таблицы
(название_столбца1 тип_данных атрибуты_столбца1, 
 название_столбца2 тип_данных атрибуты_столбца2,
 ................................................
 название_столбцаN тип_данных атрибуты_столбцаN,
 атрибуты_уровня_таблицы
)

После команды CREATE TABLE идет название таблицы. Имя таблицы выполняет роль ее идентификатора в базе данных, поэтому оно должно быть
уникальным. Затем в скобках перечисляются названия столбцов, их типы данных и атрибуты. В самом конце можно определить атрибуты для всей таблицы.
Атрибуты столбцов, а также атрибуты таблицы указывать необязательно.

Создадим простейшую таблицу. Для этого выполним следующий скрипт:

CREATE DATABASE productsdb;

USE productsdb;

CREATE TABLE Customers
(
    Id INT,
    Age INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

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

Далее собственно идет создание таблицы, которая называется Customers. Она определяет четыре столбца: Id, Age, FirstName, LastName. Первые два столбца представляют идентификатор клиента и его возраст и имеют тип INT, то есть будут хранить числовые значения.
Следующие столбцы представляют имя и фамилию клиента и имеют тип VARCHAR(20), то есть представляют строку длиной не более 20 символов.
В данном случае для каждого столбца определены имя и тип данных, при этом атрибуты столбцов и таблицы в целом отсутствуют.

CREATE TABLE и создание таблиц в MySQL

И в результате выполнения этой команды будет создана база данных productsdb, в которой будет создана таблица Customers.

Переименование таблиц

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

RENAME TABLE старое_название TO новое_название;

Например, переименуем таблицу Customers в Clients:

RENAME TABLE Customers TO Clients;

Полное удаление данных

Для полного удаления данных, очистки таблицы применяется команда TRUNCATE TABLE. Например, очистим таблицу Clients:

TRUNCATE TABLE Clients;

Удаление таблиц

Для удаления таблицы из БД применяется команда DROP TABLE, после которой указывается название удаляемой таблицы. Например,
удалим таблицу Clients:

DROP TABLE Clients;