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

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

`DISTINCT`

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

SELECT DISTINCT name FROM person;
SELECT DISTINCT name, surname FROM person;

Под повтором понимается полное совпадение всех полей набора. Обычно используется для устранения повторов в выборке из нескольких таблиц.

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

Можно сделать выборку сразу из нескольких таблиц и получить декартово произведение

SELECT * FROM person, phone;
SELECT * FROM person CROSS JOIN phone

На каждую запись первой таблицы приходятся все записи второй таблицы. Если одна из таблиц пустая, то результирующий набор пуст. Количество записей в результате равно произведению количеств записей в каждой из таблиц.

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

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

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

Тоже самое через INNER JOIN:

SELECT * 
    FROM 
        person_to_phone INNER JOIN phone ON (person_to_phone.phone_id = phone.id) 
    WHERE 
        person_id = 1;

Однако, если мы сделаем запрос для выборки всех людей с их номерами телефонов, мы получим не всех людей:

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:

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

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` и агрегация

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

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 виде:

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;

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

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 позволяет сделать псевдоним для поля выборки. Так же его можно использовать и для таблиц. Это особо полезно, когда нужно воспользоваться одной и той же таблицей в выборке несколько раз.

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

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

Индексы.

А как же индексы? Индексы не обязательны, почти все запросы на сортировку и/или фильтрацию работают одинаково успешно с ними и без них. Однако на мало-мальски крупных объемах данных скорость выполнения запросов сильно падает. Зачастую создать индекс и сделать запрос быстрее чем сделать этот же запрос без индекса.

SQL.md 23 KB Permalink Cronologia Originale

SQL

Вставка

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

Изменение

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

DELETE

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

Задание

SELECT

Cинтаксис

ORDER BY

WHERE и LIMIT

DISTINCT

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

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

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

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

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

Индексы.

Задание

SQL.md 23 KB

Permalink Cronologia Originale

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

`DELETE`

`SELECT`

`ORDER BY`

`WHERE` и `LIMIT`

`DISTINCT`

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

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

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

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

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