Skip to content

12_MySQL_data_CRUD.md

Latest commit

 

History

History
executable file
·
436 lines (335 loc) · 17 KB

12_MySQL_data_CRUD.md

File metadata and controls

executable file
·
436 lines (335 loc) · 17 KB

MySQL, CRUD данных

Когда база данных создана и таблицы в ней созданы, когда все это сделано правильным пользователем с правильными правами и уровнем доступа, самое время заполнить таблицы данными. В работе с данными есть всего 4 действия: создание, получение, изменение и удаление данных. Для обозначения этих действий используется аббревиатура CRUD: Create, Read, Update, Delete.

Для экспериментов с данными нам понадобится таблица. К примеру, таблица слов из словаря:

mysql> DESC words;

+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| word   | varchar(100) | NO   |     |         |                |
| voc_id | int(11)      | NO   |     | 0       |                |
+--------+--------------+------+-----+---------+----------------+
3 rows in set (0.26 sec)

На всякий случай приведу команду создания этой таблицы:

CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(100) NOT NULL DEFAULT '',
  `voc_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

Не стоит забывать, что для просмотра структуры таблицы мы пользуемся запросами show create table word; и desc word;

CRUD данных - Create, добавление данных (INSERT)

Для добавления данных в таблицу используется оператор INSERT INTO. Оператор INSERT INTO бывает нескольких видов, и мы рассмотрим основные:

Simple insert

Простейший вариант вставки данных в таблицу выглядит следующим образом:

INSERT INTO words SET word = "Earth", voc_id = 1;

В данном примере мы вставляем запись в таблицу word, указывая конкретное значение для каждого столбца в виде пары ключ-значение (key-value pair). Ключом выступает название поля, значением - собственно информация, которую мы хотим поместить в записи в это поле. Данный вид вставки данных применяется для добавления одной записи.

Multiple insert

INSERT INTO words (word, voc_id) VALUES ("cat", 2), ("dog", 2), ("donkey", 2);

Код выше вставляет данные в последующих скобках в соответствующие столбцы, указанные в первых скобках. Таким образом, вначале необходимо перечислить столбцы (поля), в которые планируется вносить данные, а затем через запятую перечислить обернутые скобочками наборы данных для этих полей. Такой запрос позволяет добавлять несколько записей разом.

INSERT INTO words VALUES (30, "cat", 2), (31, "dog", 2), (32, "donkey", 2);

Данный вариант запроса INSERT INTO используется, если вы планируете заполнять все столбцы, а не только выбранные.

Insert from select

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

INSERT INTO words (word) SELECT word FROM words;

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

CRUD данных - Read, выборка данных (SELECT)

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

Минимальный возможный запрос выглядит так:

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+

Единственным обязательным ключевым словом в запросе select является слово select - выбрать.

После этого слова следует писать:

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

Чаще всего SELECT используется для работы с данными из таблиц, и для этого нужно указать эти самые таблицы. Таблицы, по которым осуществляется выборка, перечисляются после ключевого слова FROM:

SELECT * FROM books;

Приведенный выше запрос выбирает все поля (за это отвечает звездочка) из таблицы books.

Максимально подробная схема запроса select выглядит так:

SELECT
    <field1>,
    <field2>,
    <field3>
    ...
FROM
    <table1>,
    <table2>,
    <joins>,
    <views>,
    <temp_table>
    ...
WHERE
    <cond>
    
ORDER BY
    <field1> ASC
    <field3> DESC
GROUP BY
    <field 1>
HAVING
    <cond with aggr function>
LIMIT
    N,M

Ниже приведено текстовое описание основных элементов структуры запроса SELECT, более подробное описание с примерами будет приведено позже.

  1. После ключевого слова SELECT идет перечень полей таблиц, функций, вычисляемых из этих полей, констант, независимых от записей функций. Для указания всех полей исопльзуется звездочка. Этот пункт является единственным обязательным пунктом в запросе SELECT, остальные опциональны.
  2. Далее, после ключевого слова FROM следует перечень таблиц, представлений и временных таблиц, откуда ведется выборка. Таблицы могут быть просто перечислены, а могут быть присоединены к другим таблицам по описанным отдельно правилам, т.е. при помощи JOIN.
  3. Далее следует условие WHERE, пропускающее только те записи, которые удовлетворяют перечисленным в WHERE условиям. Все не прошедшие проверку записи отфильтровываются и не демонстрируются.
  4. После фильтра WHERE может следовать группировка записей.
  5. Группировка выполняется при помощи ключевых слов GROUP BY. Суть группировки в том, что записи могут объединяться по признаку или нескольким признакам в одну запись, которая несет в себе некую общую для всех записей группы информацию или результат обработки информации по всей группе. Конструкция GROUP BY может включать в себя ключевое слово HAVING, позволяющее фильтровать результаты группировки.
  6. Важно отметить, что группировка позволяет использовать аггрегатные функции как в HAVING, так и после SELECT.
  7. После группировки может иметь место сортировка записей при помощи ключевых слов ORDER BY. При группировке указывается поле или перечень полей, по которому необходимо отсортировать, также можно указать направление сортировки. По умолчанию осуществляется сортировка по возрастанию. Сортировка по убыванию делается при помощи ключевого слова descending или desc.
  8. В конце запроса возможно добавление ограничений на количество записей. Слово LIMIT и цифрой после указывает, сколько записей вы хотите видет в результате. Если после слова LIMIT добавить две цифры через запятую, вы увидите второе число - количество записей после пропущенного первого числа-количества записей, т.е. LIMIT 20, 5 пропустит 20 записей и покажет вам 5 следующих.

Практика по SELECT и INSERT

DISTINCT

mysql> select word, voc_id from words;

+--------+--------+
| word   | voc_id |
+--------+--------+
| cat    |      2 |
| dog    |      2 |
| donkey |      2 |
| cat    |      2 |
| dog    |      2 |
| donkey |      2 |
| cat    |      0 |
| dog    |      0 |
| donkey |      0 |
| cat    |      0 |
| dog    |      0 |
| donkey |      0 |
+--------+--------+
12 rows in set (0.00 sec)


mysql> select distinct word, voc_id from words;

+--------+--------+
| word   | voc_id |
+--------+--------+
| cat    |      2 |
| dog    |      2 |
| donkey |      2 |
| cat    |      0 |
| dog    |      0 |
| donkey |      0 |
+--------+--------+
6 rows in set (0.00 sec)

Условие distinct отбрасывает дубикаты в результате запроса, оставляя только уникальные записи.

WHERE

Теперь выберем все слова и несколько раз отфильтруем их при помощи where:

mysql> select * from words;

+----+--------+--------+
| id | word   | voc_id |
+----+--------+--------+
|  1 | cat    |      2 |
|  2 | dog    |      2 |
|  3 | donkey |      2 |
| 30 | cat    |      2 |
| 31 | dog    |      2 |
| 32 | donkey |      2 |
| 33 | cat    |      0 |
| 34 | dog    |      0 |
| 35 | donkey |      0 |
| 36 | cat    |      0 |
| 37 | dog    |      0 |
| 38 | donkey |      0 |
+----+--------+--------+
12 rows in set (0.00 sec)


mysql> select * from words where id > 5;

+----+--------+--------+
| id | word   | voc_id |
+----+--------+--------+
| 30 | cat    |      2 |
| 31 | dog    |      2 |
| 32 | donkey |      2 |
| 33 | cat    |      0 |
| 34 | dog    |      0 |
| 35 | donkey |      0 |
| 36 | cat    |      0 |
| 37 | dog    |      0 |
| 38 | donkey |      0 |
+----+--------+--------+
9 rows in set (0.00 sec)

mysql> select * from words where id < 5;

+----+--------+--------+
| id | word   | voc_id |
+----+--------+--------+
|  1 | cat    |      2 |
|  2 | dog    |      2 |
|  3 | donkey |      2 |
+----+--------+--------+
3 rows in set (0.00 sec)

Чуть больше фильтрации и перечисление полей:

mysql> select * from words where id between 30 and 34;

+----+--------+--------+
| id | word   | voc_id |
+----+--------+--------+
| 30 | cat    |      2 |
| 31 | dog    |      2 |
| 32 | donkey |      2 |
| 33 | cat    |      0 |
| 34 | dog    |      0 |
+----+--------+--------+
5 rows in set (0.01 sec)

GROUP BY

Группировать можно данные, которые повторяются в группах и не будут противоречить условиям группировки. При группировке можно и логично использовать аггрегатные функции. Аггрегатные функции - особые функции SQL, которые применяются либо ко всем записям в результате выборки, либо к группам. Count - одна из таких функций.

mysql> select voc_id from words group by voc_id;
+--------+
| voc_id |
+--------+
|      2 |
|      0 |
+--------+
2 rows in set (0.00 sec)

mysql> select voc_id, count(*) from words group by voc_id;

+--------+----------+
| voc_id | count(*) |
+--------+----------+
|      2 |        6 |
|      0 |        6 |
+--------+----------+
2 rows in set (0.00 sec)

GROUP BY HAVING

Ключевое слово HAVING добавляется только после GROUP BY с целью дополнительной фильтрации результатов запроса. WHERE фильтрует их до группировки, HAVING фильтрует сгруппированные.

insert into words set word = 'test', voc_id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> select voc_id, count(*) from words group by voc_id having count(*) > 6;
+--------+----------+
| voc_id | count(*) |
+--------+----------+
|      2 |        7 |
+--------+----------+

Используется аггрегатная функция count().

ORDER BY

mysql> select word, voc_id from words order by word;

+--------+--------+
| word   | voc_id |
+--------+--------+
| cat    |      2 |
| cat    |      2 |
| cat    |      0 |
| cat    |      0 |
| dog    |      2 |
| dog    |      2 |
| dog    |      0 |
| dog    |      0 |
| donkey |      2 |
| donkey |      2 |
| donkey |      0 |
| donkey |      0 |
+--------+--------+
12 rows in set (0.00 sec)

mysql> select word, voc_id from words order by word, voc_id;

+--------+--------+
| word   | voc_id |
+--------+--------+
| cat    |      0 |
| cat    |      0 |
| cat    |      2 |
| cat    |      2 |
| dog    |      0 |
| dog    |      0 |
| dog    |      2 |
| dog    |      2 |
| donkey |      0 |
| donkey |      0 |
| donkey |      2 |
| donkey |      2 |
+--------+--------+
12 rows in set (0.00 sec)

mysql> select word, voc_id from words order by word, voc_id desc;

+--------+--------+
| word   | voc_id |
+--------+--------+
| cat    |      2 |
| cat    |      2 |
| cat    |      0 |
| cat    |      0 |
| dog    |      2 |
| dog    |      2 |
| dog    |      0 |
| dog    |      0 |
| donkey |      2 |
| donkey |      2 |
| donkey |      0 |
| donkey |      0 |
+--------+--------+
12 rows in set (0.00 sec)

mysql> select word, voc_id from words order by 1, 2 desc;

+--------+--------+
| word   | voc_id |
+--------+--------+
| cat    |      2 |
| cat    |      2 |
| cat    |      0 |
| cat    |      0 |
| dog    |      2 |
| dog    |      2 |
| dog    |      0 |
| dog    |      0 |
| donkey |      2 |
| donkey |      2 |
| donkey |      0 |
| donkey |      0 |
+--------+--------+
12 rows in set (0.00 sec)

LIMIT и OFFSET

mysql> select word, voc_id from words order by 1, 2 desc limit 5;

+------+--------+
| word | voc_id |
+------+--------+
| cat  |      2 |
| cat  |      2 |
| cat  |      0 |
| cat  |      0 |
| dog  |      2 |
+------+--------+
5 rows in set (0.00 sec)

mysql> select word, voc_id from words order by 1, 2 desc limit 5, 2;

+------+--------+
| word | voc_id |
+------+--------+
| dog  |      2 |
| dog  |      0 |
+------+--------+
2 rows in set (0.00 sec)

Полезные ссылки

Типы данных

Аггрегатные функции

Домашка

Следующий урок