Основы SQL на примере MySQL
Вначале было бы уместно кратко описать, что представляют собой эти реляционные базы данных и почему. Это базы данных, состоящие из таблиц (отношений), каждая таблица состоит из записей (кортежей) данных, которые можно сравнить со строками в таблице. Столбцы в этой таблице являются атрибутами, определяющими значения записи таблицы. Каждая таблица должна иметь так называемую первичный ключ , т.е. идентификатор, на основе которого мы будем строить отношения с другими таблицами. Обычно мы определяем его как идентификатор записи . Это также позволяет более эффективно извлекать определенные записи из таблицы. Аналогичным первичному ключу является внешний ключ . Это идентификатор записи другой таблицы, имеющей отношение к этой записи. На основе первичногоа внешние ключи определяют отношения между таблицами. Например, у нас есть таблицы пользователей и таблицы сообщений . Каждый пользователь имеет уникальный идентификатор . По этой причине мы можем назначить пользователя, добавившего его, для каждого сообщения, добавив атрибут, такой как authorId , в таблицу сообщений , содержащую идентификатор создателя .
Реализация реляционной базы данных, которую мы будем использовать в этом посте, — это MySQL . Установка сервера достаточно проста. В связи с тем, что существует множество описаний того, как это сделать, я не буду здесь заострять на этом внимание. Я предполагаю, что у нас есть сервер MySQL, установленный локально на компьютере, и мы дали пароль безопасности для root , и мы можем запустить сервер с консоли.
$ mysql -u root -p
Нас попросят ввести пароль, а после ввода пароля откроется интерактивная командная строка MySQL . Мы узнаем его после приглашения mysql> .
SQL (язык структурированных запросов) — это язык запросов, используемый для связи с базой данных. Под общением мы понимаем загрузку, добавление, редактирование или удаление данных. Связь с резервным копированием данных осуществляется с помощью системы управления базой данных, в нашем случае это будет MySQL . Это система управления, которая вносит изменения или возвращает элементы базы данных, но мы используем язык SQL, чтобы посылать ей команды, что делать.
Новый пользователь базы данных
Для начала рекомендуется добавить нового пользователя, чтобы не создавать каждую новую базу данных с использованием root .
mysql> CREATE USER 'sebastian'@'localhost' IDENTIFIED BY 'pass123';
Query OK, 0 rows affected (0.22 sec)
Теперь мы можем проверить, что видят пользователи MySQL .
mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| sebstian | localhost |
+------------------+-----------+
6 rows in set (0.05 sec)
Нас не интересует начало этой таблицы, так как это серверные клиенты. Root и наш вновь созданный пользователь важны для нас . Теперь нам нужно добавить разрешение для пользователя, так как в этот момент он вряд ли может делать что-то еще.
mysql> GRANT ALL PRIVILEGES ON * . * TO 'sebastian'@'localhost';
mysql> FLUSH PRIVILEGES;
Эти две команды дадут Себастьяну все возможные разрешения. На этапе производства давать такие допуски будет недопустимо, но при обучении вполне допустимо .
Чтобы выйти из интерактивной консоли MySQL , просто введите команду выхода .
mysql> exit
Первая база данных и таблица
Давайте начнем с входа в MySQL под только что созданной учетной записью.
$ mysql -u sebsatian -p
Когда у нас уже есть авторизованный пользователь и мы можем использовать его для связи с базой данных (он дал права), давайте добавим первую базу данных, в которой все наши данные будут храниться в таблицах.
mysql> CREATE DATABASE sqlbasics;
Теперь мы можем проверить, какие базы данных есть у нашего вошедшего в систему пользователя.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| nodemysql |
| performance_schema |
| sqlbasics |
| sys |
+--------------------+
6 rows in set (0.00 sec)
Теперь, чтобы иметь возможность добавлять таблицы и записи, нам нужно выбрать, с какой базой данных мы хотим работать. В нашем случае это, конечно же, будет только что созданная база данных sqlbasics . Другие базы данных являются системными творениями, так что давайте оставим их в покое.
mysql> USE sqlbasics;
Теперь мы можем добавить первую таблицу, которая будет содержать записи наших пользователей. При создании таблицы мы сразу жестко заявляем, какие будут аргументы таблицы и какие значения эти аргументы смогут принимать. Такая негибкая модель является одной из основных особенностей реляционных баз данных. Все записи должны вписываться в эту хорошо охарактеризованную модель, чтобы их можно было добавить в таблицу.
Аргументы записи могут принимать различные типы, вот основные из них:
- INT - целые числовые значения;
- TINYINT — однобитное INT , обычно для указания логического значения , так как MySQL не имеет типа BOOL ;
- FLOAT - число с плавающей запятой, по умолчанию 4-битное;
- ДАТА - дата (без времени), отображаемая в формате ГГГГ-ММ-ДД;
- DATETIME - дата с отображением времени суток в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС;
- TIMESTAMP - дата и время с начала эпохи UNIX в секундах;
- CHAR - поле с символьным значением фиксированной длины от 1 до 255 байт;
- VARCHAR — поле с символьным значением переменной длины от 1 до 255 байт;
- ТЕКСТ — текстовое поле размером 65 535 байт или меньше для хранения длинных текстовых значений.
Для каждого определенного табличного аргумента, в дополнение к определению типа, мы также должны определить максимальную длину, которую можно ввести (мы указываем ее в круглых скобках после типа аргумента). Итак, наша первая таблица будет выглядеть так.
mysql> CREATE TABLE users(
-> id INT AUTO_INCREMENT,
-> name VARCHAR(50),
-> email VARCHAR(50),
-> password VARCHAR(100),
-> job VARCHAR(50),
-> register_date DATETIME DEFAULT CURRENT_TIMESTAMP,
-> PRIMARY KEY(id)
-> );
Эта запись говорит о том, что новая таблица будет называться пользователями и будет иметь аргументы: id , name , email , password и register_date . Параметр AUTO_INCREMENT для аргумента id означает, что каждой последующей записи будет присвоен последовательный целочисленный идентификатор . Однако PRIMART_KEY (id) назначает идентификатор записи в качестве идентификатора, на основе которого мы сможем быстро искать записи или создавать отношения с другими таблицами. DEFAULT CURENT_TIMESTAMP означает, что для поля register_date установлено значение по умолчанию .
Теперь мы можем проверить, есть ли наша таблица в базе данных.
mysql> SHOW TABLES;
+---------------------+
| Tables_in_sqlbasics |
+---------------------+
| users |
+---------------------+
1 row in set (0.02 sec)
Добавление записей в базу данных
Когда у нас есть база данных и таблица в ней, пришло время добавить первую запись.
mysql> INSERT INTO users (name, email, password, job) VALUES ('Sebastian', 'sebastian@mail.com', 'pass123', 'front-end developer');
Добавляем запись с помощью метода INSERT INTO , определяя таблицу, в которую хотим добавить нашу запись, затем в скобках определяем аргументы, которым будем присваивать данные. Наконец, мы должны ввести значения аргументов в следующие круглые скобки после параметра VELUES . Порядок имен аргументов и их значений должен совпадать. Поскольку register_date установлена по умолчанию, нам не нужно указывать ее.
Мы также можем добавить несколько записей одновременно.
mysql> INSERT INTO users (name, email, password, job) VALUES ('John', 'john@gmail.com', 'pass123', 'back-end developer'), ('Sam', 'sam@yahoo.com', 'pass123', 'designer');
Чтение записей из базы данных
Базовым способом чтения из базы данных является метод SELECT без определения аргументов (*), мы указываем только таблицу, из которой получаем данные.
mysql> SELECT * FROM users;
+----+-----------+--------------------+----------+---------------------+---------------------+
| id | name | email | password | job | register_date |
+----+-----------+--------------------+----------+---------------------+---------------------+
| 1 | Sebastian | sebastian@mail.com | pass123 | front-end developer | 2019-10-27 13:55:19 |
| 2 | John | john@gmail.com | pass123 | back-end developer | 2019-10-27 14:17:34 |
| 3 | Sam | sam@yahoo.com | pass123 | designer | 2019-10-27 14:17:34 |
+----+-----------+--------------------+----------+---------------------+---------------------+
3 rows in set (0.00 sec)
Также мы можем прочитать только те аргументы, которые нас интересуют, например:
mysql> SELECT name, job FROM users;
+-----------+---------------------+
| name | job |
+-----------+---------------------+
| Sebastian | front-end developer |
| John | back-end developer |
| Sam | designer |
+-----------+---------------------+
3 rows in set (0.00 sec)
Когда мы хотим сузить список полученных записей, мы можем добавить параметр WHERE в строку запроса, а затем определить его для получения. Помимо оператора уравнения, мы также можем использовать, например, знаки меньше и больше для числовых значений.
mysql> SELECT * FROM users WHERE job='designer';
+----+------+---------------+----------+----------+---------------------+
| id | name | email | password | job | register_date |
+----+------+---------------+----------+----------+---------------------+
| 3 | Sam | sam@yahoo.com | pass123 | designer | 2019-10-27 14:17:34 |
+----+------+---------------+----------+----------+---------------------+
1 row in set (0.01 sec)
Сортировка данных также может быть полезной. Например, отсортируем по именам в порядке возрастания.
mysql> SELECT * FROM users ORDER BY name ASC;
+----+-----------+----------------+----------+---------------------+---------------------+
| id | name | email | password | job | register_date |
+----+-----------+----------------+----------+---------------------+---------------------+
| 2 | John | john@gmail.com | pass123 | back-end developer | 2019-10-27 14:17:34 |
| 5 | Sam | sam@yahoo.com | pass123 | designer | 2019-10-27 14:52:52 |
| 1 | Sebastian | seba@gmail.com | pass123 | front-end developer | 2019-10-27 13:55:19 |
+----+-----------+----------------+----------+---------------------+---------------------+
3 rows in set (0.01 sec)
Мы также можем искать записи таблицы на основе значения в аргументах. Для этого используется метод LIKE , после чего мы говорим, что ищем (синтаксис похож на регулярное выражение).
mysql> SELECT * FROM users WHERE job LIKE '%end%';
+----+-----------+----------------+----------+---------------------+---------------------+
| id | name | email | password | job | register_date |
+----+-----------+----------------+----------+---------------------+---------------------+
| 1 | Sebastian | seba@gmail.com | pass123 | front-end developer | 2019-10-27 13:55:19 |
| 2 | John | john@gmail.com | pass123 | back-end developer | 2019-10-27 14:17:34 |
+----+-----------+----------------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)
Такая запись запроса говорит: вернуть все записи таблицы пользователей , где аргумент задания имеет значение 'end', до и после которого может быть любое количество символов.
Последний из основных параметров, связанных с отображением данных, — IN .
mysql> SELECT * FROM users WHERE id IN (1, 5)
+----+-----------+----------------+----------+---------------------+---------------------+
| id | name | email | password | job | register_date |
+----+-----------+----------------+----------+---------------------+---------------------+
| 1 | Sebastian | seba@gmail.com | pass123 | front-end developer | 2019-10-27 13:55:19 |
| 5 | Sam | sam@yahoo.com | pass123 | designer | 2019-10-27 14:52:52 |
+----+-----------+----------------+----------+---------------------+---------------------+
Таким образом, мы объявляем, что SQL должен возвращать записи, где id равен 1 или 5 .
Изменения в записях таблицы
Мы также можем внести изменения в существующие записи в таблице. Делаем это методом UPDATE , определяем после него, в какую таблицу хотим внести изменения и какой аргумент меняем. Чтобы не менять все записи в таблице, не забудьте уточнить свой запрос методом WHERE .
mysql> UPDATE users SET email = 'seba@gmail.com' WHERE id = 1;
Rows matched: 1 Changed: 1 Warnings: 0
Удаляем записи с помощью метода DELETE , здесь мы также должны указать, какую запись мы хотим удалить, чтобы случайно не удалить их все.
mysql> DELETE FROM users WHERE id = 3;
После наших изменений таблицы пользователей выглядят так. Обратите внимание, что оба наших предыдущих метода UPDATE и DELETE работали. Запись с id=3 удалена и почта для первой записи изменена.
mysql> SELECT * FROM users;
+----+-----------+----------------+----------+---------------------+---------------------+
| id | name | email | password | job | register_date |
+----+-----------+----------------+----------+---------------------+---------------------+
| 1 | Sebastian | seba@gmail.com | pass123 | front-end developer | 2019-10-27 13:55:19 |
| 2 | John | john@gmail.com | pass123 | back-end developer | 2019-10-27 14:17:34 |
+----+-----------+----------------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)
Отношения между базами данных
Основным преимуществом реляционных баз данных, помимо предопределенной модели данных, являются отношения. С помощью первичного ключа и внешнего ключа мы можем связать две базы данных, а именно записи из двух баз данных.
Во-первых, давайте определим новую таблицу сообщений , которая будет содержать user_id, связанный с записью таблицы пользователей .
mysql> CREATE TABLE posts(
-> id INT AUTO_INCREMENT,
-> user_id INT,
-> title VARCHAR(100),
-> body TEXT,
-> publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
-> PRIMARY KEY(id),
-> FOREIGN KEY (user_id) REFERENCES users(id)
-> );
posts также имеет PRIMARY KEY , который является его идентификатором . FOREIGN KEY (user_id) REFERENCES пользователь (id) говорит, что аргумент user_id совпадает с аргументом id таблицы пользователей .
Итак, давайте добавим несколько сообщений в таблицу сообщений .
mysql> INSERT INTO posts(user_id, title, body) VALUES (1, 'Post One', 'This is post one'),(5, 'Post Two', 'This is post two'),(5, 'Post Three', 'This is post three'),(5, 'Post Four', 'This is post four'),(2, 'Post Five', 'This is post five'),(1, 'Post Six', 'This is post six'),(2, 'Post Seven', 'This is post seven'),(1, 'Post Eight', 'This is post eight'),(5, 'Post Nine', 'This is post none');
Records: 9 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM posts;
+----+---------+------------+--------------------+---------------------+
| id | user_id | title | body | publish_date |
+----+---------+------------+--------------------+---------------------+
| 1 | 1 | Post One | This is post one | 2019-10-27 15:32:34 |
| 2 | 5 | Post Two | This is post two | 2019-10-27 15:32:34 |
| 3 | 5 | Post Three | This is post three | 2019-10-27 15:32:34 |
| 4 | 5 | Post Four | This is post four | 2019-10-27 15:32:34 |
| 5 | 2 | Post Five | This is post five | 2019-10-27 15:32:34 |
| 6 | 1 | Post Six | This is post six | 2019-10-27 15:32:34 |
| 7 | 2 | Post Seven | This is post seven | 2019-10-27 15:32:34 |
| 8 | 1 | Post Eight | This is post eight | 2019-10-27 15:32:34 |
| 9 | 5 | Post Nine | This is post none | 2019-10-27 15:32:34 |
+----+---------+------------+--------------------+---------------------+
Когда у нас есть две таблицы, из которых у нас есть внешний ключ в таблице сообщений , ссылающийся на идентификатор в таблице пользователей , мы можем связать поиск из двух таблиц в одну, используя JOIN . Мы сосредоточимся на INNER JOIN , другие варианты я отсылаю к документации.
mysql> SELECT users.name, posts.title, posts.body, posts.publish_date
-> FROM users
-> INNER JOIN posts
-> ON users.id = posts.user_id
-> ORDER BY posts.publish_date;
+-----------+------------+--------------------+---------------------+
| name | title | body | publish_date |
+-----------+------------+--------------------+---------------------+
| Sam | Post Two | This is post two | 2019-10-27 15:32:34 |
| Sam | Post Three | This is post three | 2019-10-27 15:32:34 |
| Sam | Post Four | This is post four | 2019-10-27 15:32:34 |
| Sebastian | Post One | This is post one | 2019-10-27 15:32:34 |
| Sam | Post Nine | This is post none | 2019-10-27 15:32:34 |
| Sebastian | Post Six | This is post six | 2019-10-27 15:32:34 |
| Sebastian | Post Eight | This is post eight | 2019-10-27 15:32:34 |
| John | Post Five | This is post five | 2019-10-27 15:32:34 |
| John | Post Seven | This is post seven | 2019-10-27 15:32:34 |
+-----------+------------+--------------------+---------------------+
В запросе мы заявляем, что хотим получить. users.name определяет, что мы ожидаем аргумент имени из таблицы пользователей . Следующие две строки сообщают, к какой таблице подключаться. Параметр ON определяет, какой аргумент в первой таблице ссылается на внешний ключ во второй. Последняя строка ORDER BY , как следует из названия, сортирует результаты.