# SQL

Данное занятие посвящено основным манипуляциям с *данными* в SQL. То есть речь пойдет о **DML** (**Data Manipulation Language**), операциях вставки, 
удаления, изменения и выборки данных.

## Вставка

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

```mysql
INSERT INTO <table>
  (column1, column2, column3...columnN)
VALUES
  (value1, value2, value3...valueN)
```
где:
- **columnN** - имена колонок таблицы для вставки данных
- **valueN** - значения соответствующих колонок.

**Например**

```mysql
MariaDB [test]> INSERT INTO person (date_of_birth, name, surname, father_name) VALUES("2000-03-15", "Ivan", "Ivanoff", "Petrovich");
Query OK, 1 row affected (0.10 sec)
```

Для проверки используем **запрос выборки** `SELECT` в простейшей форме. Подробнее о выборке будет ниже:

```mysql
MariaDB [test]> SELECT * from person;
+-----------+---------------+------+---------+-------------+
| person_id | date_of_birth | name | surname | father_name |
+-----------+---------------+------+---------+-------------+
|         1 | 2000-03-15    | Ivan | Ivanoff | Petrovich   |
+-----------+---------------+------+---------+-------------+
1 row in set (0.00 sec)
```

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

Обычный синтаксис `INSERT INTO (columns) VALUES(values)` не очень нагляден, особенно когда колонок много, так как имена колонок находятся далеко от значений;
при редактировании такого запроса часто бывают ошибки, например вставка данных не в ту колонку. Зато эта форма удобна для пакетной вставки и вставки данных из 
запроса выборки.

### Вставка: альтернативный синтаксис.

Более наглядным является синтаксис:

```mysql
INSERT INTO <table> SET
   column1 = value1,
   column2 = value2,
   .....
   columnN = valueN
```
Данный синтаксис очень сильно похож на обычное присвоение в языках программирования.

**Например**
```mysql
MariaDB [test]> INSERT INTO person SET date_of_birth = "1999-05-25", name = 'Vasiliy', surname = 'Pupkin', father_name = 'Petrovich';
Query OK, 1 row affected (0.00 sec)
```

## Изменение

Для изменения данных используется запрос, похожий на альтернативную форму `INSERT INTO <table> SET`:

```mysql
UPDATE <table> SET
   column1 = value1,
   column2 = value2,
   .....
   columnN = valueN
```

Такая форма изменяет **все** записи в таблице, так как не указано условие, какие именно записи подлежат изменению. **Запомните это!!!!!**, так как это верный способ
испортить данные:

```mysql
MariaDB [test]> UPDATE person SET date_of_birth = NOW();
Query OK, 2 rows affected, 2 warnings (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 2

MariaDB [test]> SELECT * from person;
+-----------+---------------+---------+---------+-------------+
| person_id | date_of_birth | name    | surname | father_name |
+-----------+---------------+---------+---------+-------------+
|         1 | 2016-10-23    | Ivan    | Ivanoff | Petrovich   |
|         2 | 2016-10-23    | Vasiliy | Pupkin  | Petrovich   |
+-----------+---------------+---------+---------+-------------+
2 rows in set (0.00 sec)
```

Обратите внимание на даты. **SQL** позволяет не только вставлять литеральные значения, но и использовать внутренние функции (`NOW()` в примере выше) или выражения с
полями текущей записи.

**Например**
```mysql
MariaDB [test]> ALTER TABLE person ADD COLUMN fullname VARCHAR(255);
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> desc person;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| person_id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| date_of_birth | date             | YES  | MUL | NULL    |                |
| name          | varchar(64)      | YES  |     | NULL    |                |
| surname       | varchar(64)      | YES  |     | NULL    |                |
| father_name   | varchar(64)      | YES  |     | NULL    |                |
| fullname      | varchar(255)     | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

MariaDB [test]> UPDATE person SET fullname = CONCAT(name," ",father_name," ",surname); 
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [test]> SELECT * FROM person;
+-----------+---------------+---------+---------+-------------+--------------------------+
| person_id | date_of_birth | name    | surname | father_name | fullname                 |
+-----------+---------------+---------+---------+-------------+--------------------------+
|         1 | 2016-10-23    | Ivan    | Ivanoff | Petrovich   | Ivan Petrovich Ivanoff   |
|         2 | 2016-10-23    | Vasiliy | Pupkin  | Petrovich   | Vasiliy Petrovich Pupkin |
+-----------+---------------+---------+---------+-------------+--------------------------+
2 rows in set (0.00 sec)
```

### Условие изменения `WHERE`

Для того, что бы `UPDATE` изменял не все, а только нужные записи, в конце запроса добавляется конструкция `WHERE` и логическое условие, схожее с применяемыми 
в операторах `if` в языках программирования. Обычно в этом условии используется значение поля автоинкремента как уникальный идентификатор записи:

```mysql
MariaDB [test]> UPDATE person SET date_of_birth = '1999-05-25' WHERE person_id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> SELECT * from person;
+-----------+---------------+---------+---------+-------------+--------------------------+
| person_id | date_of_birth | name    | surname | father_name | fullname                 |
+-----------+---------------+---------+---------+-------------+--------------------------+
|         1 | 2016-10-23    | Ivan    | Ivanoff | Petrovich   | Ivan Petrovich Ivanoff   |
|         2 | 1999-05-25    | Vasiliy | Pupkin  | Petrovich   | Vasiliy Petrovich Pupkin |
+-----------+---------------+---------+---------+-------------+--------------------------+
2 rows in set (0.00 sec)
```

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

```mysql
UPDATE <table> SET
   column1 = value1,
   column2 = value2,
   .....
   columnN = valueN
WHERE
    <cond>
LIMIT
    1
```

`LIMIT 1` означает, что `UPDATE` должен изменить **не более** одной записи. Таким образом вы можете минимизировать последствия вашего запроса, если не уверены в том,
что записано в `WHERE` в качестве условия.

## `DELETE`

В общем случае:

```mysql
DELETE FROM <table>
```
...как и `UPDATE` удаляет всё, если не указано иное. Поэтому перед тем, как нажать **ENTER** несколько раз смотрим, всё ли верно, используем `WHERE` и/или `LIMIT`.

## **Автоинкремент** и **связность**

Для установки связи значения автоинкремента заносится в другую таблицу как обычный INT.

```mysql
MariaDB [test]> DESC phone;
+--------------+-----------------------------+------+-----+---------+----------------+
| Field        | Type                        | Null | Key | Default | Extra          |
+--------------+-----------------------------+------+-----+---------+----------------+
| phone_id     | int(10) unsigned            | NO   | PRI | NULL    | auto_increment |
| person_id    | int(10) unsigned            | YES  |     | NULL    |                |
| type         | enum('home','mobile','job') | YES  |     | NULL    |                |
| phone_number | varchar(16)                 | YES  |     | NULL    |                |
+--------------+-----------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [test]> ALTER TABLE phone MODIFY COLUMN type ENUM('home', 'mobile', 'job') DEFAULT 'mobile';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> DESC phone;
+--------------+-----------------------------+------+-----+---------+----------------+
| Field        | Type                        | Null | Key | Default | Extra          |
+--------------+-----------------------------+------+-----+---------+----------------+
| phone_id     | int(10) unsigned            | NO   | PRI | NULL    | auto_increment |
| person_id    | int(10) unsigned            | YES  |     | NULL    |                |
| type         | enum('home','mobile','job') | YES  |     | mobile  |                |
| phone_number | varchar(16)                 | YES  |     | NULL    |                |
+--------------+-----------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [test]> INSERT INTO phone SET person_id = 2, phone_number = '+380 577 102';
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> INSERT INTO phone SET person_id = 2, phone_number = '+380 577 103';
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> INSERT INTO phone SET person_id = 1, phone_number = '+380 577 104';
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> INSERT INTO phone SET person_id = 1, phone_number = '+380 577 101';
Query OK, 1 row affected (0.02 sec)

MariaDB [test]> select * from phone;
+----------+-----------+--------+--------------+
| phone_id | person_id | type   | phone_number |
+----------+-----------+--------+--------------+
|        1 |         2 | mobile | +380 577 102 |
|        2 |         2 | mobile | +380 577 103 |
|        3 |         1 | mobile | +380 577 104 |
|        4 |         1 | mobile | +380 577 101 |
+----------+-----------+--------+--------------+
4 rows in set (0.00 sec)
```

Таким образом на каждого человека по два телефонных номера.

## Задание

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

## `SELECT`

`SELECT` - самая мощная часть DML, и основная фича SQL

### Cинтаксис
В общем виде:
```mysql
SELECT
    <поле1>
    <поле2>
    <поле3>
    ...
FROM
    <table1>
    <table2>
    <table3>
    ...
WHERE
    <cond>
ORDER BY
    <поле1> ASC
    <поле2> DESC
LIMIT
    N,M
```

**где**

*<поле1>* - поля таблиц для вывода;

*table1* - таблицы для запроса;

`WHERE` *cond* - условия, что и по каким правилам включать в выборку;

`ORDER BY` - по каким полям сортировать

`LIMIT` - ограничения с какой по какую запись выводить результирующую выборку


**Например**
```mysql
SELECT * FROM person;
SELECT id, name, surname FROM person;
SELECT count(*) FROM person;
```

### `ORDER BY`

Сортировка. Может быть прямой (`ASC`) и обратной (`DESC`). Возможна сортировка по нескольким полям, в таком случае набор сортируется вначале по первым
полям, а при их совпадении - по последующим, аналогично сортировке слов в словаре. Для работы сортировки **не** требуется индекс, однако с ним на 
больших наборах данных выборка будет сделана намного быстрее.

```mysql
SELECT * FROM person ORDER by id;
SELECT * FROM person ORDER by date_of_birth;
SELECT * FROM person ORDER by father_name DESC;
SELECT id,name,surname,date_of_birth FROM person ORDER BY name;
SELECT id,name,surname,date_of_birth FROM person ORDER BY name, id DESC;
```
**Не обязательно** использовать имена полей в `ORDER BY`; можно использовать разные конструкции и функции Mysql, например `RAND()` для случайной выборки.

### `WHERE` и `LIMIT`

Как вы заметили выше, `WHERE` - очень мощный инструмент, который позволяет строить практически любые условия. Как и в языках программирования, возможно
использовать логические операции (`OR`, `AND`, `NOT`), операции сравнения и другие выражения.

```mysql
SELECT * FROM person WHERE date_of_birth > "2000-01-01";
SELECT * FROM person WHERE date_of_birth > "2000-01-01" AND name = 'Ivan';
SELECT * FROM person WHERE date_of_birth > "2000-01-01" OR  name IN ('Petr', 'Vasiliy');
SELECT * FROM person WHERE id % 2 = 0;
```

`LIMIT` позволяет получить только часть выборки, именно благодаря этой функции работает пагинация на подавляющем большинстве сайтов:
```mysql
SELECT * FROM person LIMIT 1 
```
выдает одну запись сначала выборки согласно сортировке
```mysql
SELECT * FROM person LIMIT 1,2 
```
выдает две записи начиная со второй записи (пропуская 1 запись от начала) выборки согласно сортировке. Таким образом, `LIMIT N` аналогичен `LIMIT 0,N`

### `DISTINCT`
Это слово применяется для удаления повторов в результирующем наборе:

```mysql
SELECT DISTINCT name FROM person;
SELECT DISTINCT name, surname FROM person;
```
Под повтором понимается полное совпадение всех полей набора. Обычно используется для устранения повторов в выборке из нескольких таблиц.

### Несколько таблиц (`CROSS JOIN`)

Можно сделать выборку сразу из нескольких таблиц и получить *декартово произведение*
```mysql
SELECT * FROM person, phone;
SELECT * FROM person CROSS JOIN phone
```
На каждую запись первой таблицы приходятся все записи второй таблицы. Если одна из таблиц пустая, то результирующий набор пуст. Количество записей
в результате равно произведению количеств записей в каждой из таблиц.

### Несколько таблиц и `WHERE` (`INNER JOIN`)
С помощью условия в `WHERE` можно сделать более осмысленный запрос. Например найти все телефоны пользователя с id = 1 через смежную таблицу связи
**многие-ко-многим**:


```mysql
SELECT * 
    FROM 
        person_to_phone, 
        phone 
    WHERE 
        person_id = 1 AND 
        person_to_phone.phone_id = phone.id;
```

Тоже самое через `INNER JOIN`:
```mysql
SELECT * 
    FROM 
        person_to_phone INNER JOIN phone ON (person_to_phone.phone_id = phone.id) 
    WHERE 
        person_id = 1;
```

Однако, если мы сделаем запрос для выборки всех людей с их номерами телефонов, мы получим *не всех людей*:
```mysql
SELECT * 
    FROM 
        person, 
        person_to_phone, 
        phone 
    WHERE 
        person.id = person_to_phone.person_id AND 
        person_to_phone.phone_id = phone.id;
```
...a только тех, у кого *есть номера телефонов*.

### Несколько таблиц, `RIGHT` и `LEFT` `JOIN` (`OUTER JOIN`)
Для решения вышеозначенной проблемы есть запросы `LEFT` и `RIGHT JOIN`:
```mysql
SELECT * 
    FROM 
        person LEFT JOIN person_to_phone ON (person.id = person_to_phone.person_id) 
               LEFT JOIN phone ON (person_to_phone.phone_id = phone.id);
```
Запрос выше показывает *всех* пользователей, даже тех, у кого телефонов *нет*. Недостающие поля заполняются `NULL`
```mysql
SELECT * 
    FROM 
        person RIGHT JOIN person_to_phone ON (person.id = person_to_phone.person_id) 
               RIGHT JOIN phone ON (person_to_phone.phone_id = phone.id);
```
Этот запрос, наоборот, показывает все телефоны, один из который не принадлежит никому из пользователей. 

### `GROUP BY` и агрегация
Нередко возникает задача, когда требуется не выводить все присоединенные записи, а произвести над ними определенные действия и вывести один результат
для каждой "родительской" записи. Например, подсчитаем количество телефонов у каждого пользователя

```mysql
SELECT
        person.id, 
        person.surname, 
        count(phone.id) 
    FROM 
        person 
            LEFT JOIN person_to_phone ON (person.id = person_to_phone.person_id) 
            LEFT JOIN phone ON (person_to_phone.phone_id = phone.id) 
    GROUP BY 
        person.id;
```

Так же мы можем получить все номера телефонов в удобном для `explode` виде:

```mysql
SELECT 
        person.id, 
        person.surname, 
        COUNT(phone.id), 
        GROUP_CONCAT(phone.phone) 
    FROM 
        person 
            LEFT JOIN person_to_phone ON (person.id = person_to_phone.person_id) 
            LEFT JOIN phone ON (person_to_phone.phone_id = phone.id) 
    GROUP BY 
        person.id;
```

Или подсчитать средний возраст пользователей тех или иных типов телефонов:

```mysql
SELECT 
        phone.phone_type, 
        AVG(TIMESTAMPDIFF(YEAR, person.date_of_birth, CURDATE())) AS averageAge
    FROM 
        phone 
            LEFT JOIN person_to_phone ON (phone.id = person_to_phone.phone_id) 
            LEFT JOIN person ON (person_to_phone.person_id = person.id) 
    GROUP BY 
        phone_type;
```

Для понимания, как именно работает `GROUP BY`, попробуйте его убрать и отсортировать выборку по полю, которое было в GROUP BY. Таким образом вы увидите
*все* записи сгруппированными благодаря сортировке. СУБД работает схожим образом.

### `AS` и древовидные структуры данных в реляционных СУБД.

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

В реляционных СУБД часто хранят древовидные структуры данных с помощью отношения **один-ко-многим** внутри одной таблицы. Для представления разных 
уровней дерева в одной и той же таблице используются псевдонимы:

```mysql
SELECT 
        c1.id, 
        c1.name, 
        c2.id, 
        c2.name, 
        c2.parent_id 
    FROM 
        category AS c1, 
        category AS c2 
    WHERE 
        c2.parent_id = c1.id 
    ORDER BY 
        c1.id, 
        c2.id
```

## Задание