SQL

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

Вставка

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

INSERT INTO <table>
  (column1, column2, column3...columnN)
VALUES
  (value1, value2, value3...valueN)

где:

columnN - имена колонок таблицы для вставки данных
valueN - значения соответствующих колонок.

Например

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 в простейшей форме. Подробнее о выборке будет ниже:

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) не очень нагляден, особенно когда колонок много, так как имена колонок находятся далеко от значений; при редактировании такого запроса часто бывают ошибки, например вставка данных не в ту колонку. Зато эта форма удобна для пакетной вставки и вставки данных из запроса выборки.

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

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

INSERT INTO <table> SET
   column1 = value1,
   column2 = value2,
   .....
   columnN = valueN

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

Например

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:

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

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

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() в примере выше) или выражения с полями текущей записи.

Например

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 в языках программирования. Обычно в этом условии используется значение поля автоинкремента как уникальный идентификатор записи:

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:

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

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

`DELETE`

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

DELETE FROM <table>

...как и UPDATE удаляет всё, если не указано иное. Поэтому перед тем, как нажать ENTER несколько раз смотрим, всё ли верно, используем WHERE и/или LIMIT.

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

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

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интаксис

В общем виде:

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 - ограничения с какой по какую запись выводить результирующую выборку

Например

SELECT * FROM person;
SELECT id, name, surname FROM person;
SELECT count(*) FROM person;

`ORDER BY`

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

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), операции сравнения и другие выражения.

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 позволяет получить только часть выборки, именно благодаря этой функции работает пагинация на подавляющем большинстве сайтов:

SELECT * FROM person LIMIT 1

выдает одну запись сначала выборки согласно сортировке

SELECT * FROM person LIMIT 1,2

выдает две записи начиная со второй записи (пропуская 1 запись от начала) выборки согласно сортировке. Таким образом, LIMIT N аналогичен LIMIT 0,N

SQL.md 16 KB

Vēsture Neapstrādāts

SQL

Вставка

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

Изменение

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

`DELETE`

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

Задание

`SELECT`

Cинтаксис

`ORDER BY`

`WHERE` и `LIMIT`

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

`DISTINCT`

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

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

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

SQL.md 16 KB Vēsture Neapstrādāts

SQL

Вставка

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

Изменение

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

DELETE

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

Задание

SELECT

Cинтаксис

ORDER BY

WHERE и LIMIT

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

DISTINCT

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

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

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

SQL.md 16 KB

Vēsture Neapstrādāts

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

`DELETE`

`SELECT`

`ORDER BY`

`WHERE` и `LIMIT`

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

`DISTINCT`

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

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

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