# 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;
```
**Не обязательно** использовать имена полей в `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`

### `GROUP BY` и агрегация

### `DISTINCT`

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

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

### Несколько таблиц, `RIGHT` и `LEFT` `JOIN` (`OUTER JOIN`)