PHP/SQL.md

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)

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

Задание

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