Получение, добавление, удаление и изменение данных в таблицах

Хорошо, БД мы создали, таблицы тоже, схему описали, осталось научиться работать с этими самыми данными.

Добавление данных

Для добавления данных в таблицу используется команда INSERT.

Синтаксис:

INSERT INTO название_таблицы (назание_первой_колнки, название_второй_колонки, ...)
VALUES (значение_первой_колонки, значение_второй_колонки, ...)

Пример:

INSERT INTO customers (
  id, first_name, last_name, phone, email, address, register_at
) VALUES (
  1, 'Ivan', 'Ivanov', 79156782211, '[e-mail]', 'Moscow, Lenina St. 1, 100', 1682334619
);

Из чего состоит запрос:

• INSERT INTO customers - выбираем в какую таблицу будет происходить вставка.

• (id, first_name, last_name, ...) - выбираем какие поля мы будем вносить

• VALUES (1, 'Ivan', 'Ivanov') - значения, которые будут добавлены, строго в с том порядке и кол-ве в каком они идут в предыдущем пункте

Генерация значений

В прошлой главе мы рассмотрели параметры AUTOINCREMENT и DEFAULT при создании новой таблицы. Для того, чтобы ими воспользоваться необходимо вместо значения передать NULL. Сделать это можно 2 спообами: указать внутри VALUES значение NULL для этого параметра или не учитывать эту колонку. Пример:

Создаем таблицу с PRIMARY KEY (помним, что если тип данных число, то оно будет AUTOINCREMENT)

CREATE TABLE [IF NOT EXISTS] название_таблицы (
  название_колонки INT,
  нащвание_другой_колонки VARCHAR
  PRIMARY KEY (название_колонки)
);

Вставляем данные

INSERT INTO название_таблицы (нащвание_другой_колонки) 
VALUES ("Пример");

Как видим, мы не стали указывать для колонки с первичным ключом значения, значит будет передано NULL, значит значение будет автоматиечски сгенерировано.

За свою практику я сталкивался с 2 проблемами такого подхода.

1. Медленная работа. Порой при очень большом кол-ве вставок в таблицу мы можем получить замедление скорости (Bottleneck) из-за того, что СУБД нужно посчитать какое значение должно достаться записи

2. Логика работы даной функции редко, но метко может ломаться и приходится потратить приличное кол-во сил на починку. А так как генерация значения не работает, то вставка в таблицу не предоставляется возможной и в итоге функционал нашего сервиса уходит полностью в отказ.

Вышеназванные проблемы стоит учитывать в том момент, когда это необходимо.

Если вы заранее можете предположить, что у вас ожидается большая скорость записи (от 10 000 записей в секунду), то лучше придумать свой вариант для расчета внутри кода.

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

Множественное добавление

Помимо одиночной вставки данных мы можем также вставлять несколько значений используя одну команду INSERT.

Синтаксис:

INSERT INTO название_таблицы (назание_первой_колнки, название_второй_колонки, ...) 
VALUES (значение_первой_колонки, значение_второй_колонки, ...),
  (значение_первой_колонки, значение_второй_колонки, ...),
  (значение_первой_колонки, значение_второй_колонки, ...),
  ...

Пример:

INSERT INTO customers (first_name, last_name, phone, email, address) 
VALUES ('Gleb', 'Voroncov', 79675605625, '[e-mail]', 'Nalch, bld. 2/В, appt. 11'),
  ('Maria', 'Golubeva', 79157788778, '[e-mail]', 'Naberezhnye Chelny, Os 423802 A/ya 45'),
  ('Denis', 'Pavlenko', 79210019471, '[e-mail]', 'Vladimir, Esenina S. Ul., bld. 32, appt. 6');

Получение данных

Для того, чтобы получать данные из конкретной таблицы необходимо использовать команду SEELCT

Синтаксис:

SELECT название_колонки, название_другой_колонки, ... FROM название_таблицы

Пример:

SELECT first_name, last_name, phone FROM customers

first_name	last_name	phone
Ivan	Ivanov	79156782211
Vladislav	Petrov	79991452521
Ivan	Vasilyev	79261009615

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

Кроме того мы можем с помощью специального символа, указать, что хотим получить вообще все колонки, которые есть

SELECT * FROM customers

id	first_name	last_name	phone	email	address	register_at
1	Ivan	Ivanov	79156782211	[e-mail]	Moscow, Lenina St. 1, 100	1682334619
2	Vladislav	Petrov	79991452521	[e-mail]	Ufa, 50 october years St. 9, 11	1682447158
3	Ivan	Vasilyev	79261009615	[e-mail]	Vladimir, Lenina St. 32, 78	1682447158
4	Gleb	Voroncov	79675605625	[e-mail]	Nalch, bld. 2/В, appt. 11	1682667535
5	Maria	Golubeva	79157788778	[e-mail]	Naberezhnye Chelny, Os 423802 A/ya 4	1682667536
6	Denis	Pavlenko	79210019471	[e-mail]	Vladimir, Esenina S. Ul., bld. 32, appt. 6	1682667537

условия

Помимо получения всех значений, мы можем также фильтровать нашу выборку с помощью ключевого слова WHERE.

SELECT названия_колонок FROM название_таблицы WHERE условие 

Всего существуют следущие условия:

• = - Отбираются значения равные указанному

SELECT * FROM customers WHERE first_name = 'Ivan'

id	first_name	last_name	phone	email	address	register_at
1	Ivan	Ivanov	79156782211	[e-mail]	Moscow, Lenina St. 1, 100	1682334619
3	Ivan	Vasilyev	79261009615	[e-mail]	Vladimir, Lenina St. 32, 78	1682447158

• > - Отбираются значения больше указанного

SELECT * FROM customers WHERE id > 3

id	first_name	last_name	phone	email	address	register_at
4	Gleb	Voroncov	79675605625	[e-mail]	Nalch, bld. 2/В, appt. 11	1682667535
5	Maria	Golubeva	79157788778	[e-mail]	Naberezhnye Chelny, Os 423802 A/ya 4	1682667536
6	Denis	Pavlenko	79210019471	[e-mail]	Vladimir, Esenina S. Ul., bld. 32, appt. 6	1682667537

• < - Отбираются значения меньше указанного

SELECT * FROM customers WHERE id < 4

id	first_name	last_name	phone	email	address	register_at
1	Ivan	Ivanov	79156782211	[e-mail]	Moscow, Lenina St. 1, 100	1682334619
2	Vladislav	Petrov	79991452521	[e-mail]	Ufa, 50 october years St. 9, 11	1682447158
3	Ivan	Vasilyev	79261009615	[e-mail]	Vladimir, Lenina St. 32, 78	1682447158

• >= - Отбираются значения больше или равные указанным

SELECT * FROM customers WHERE id >= 4

id	first_name	last_name	phone	email	address	register_at
4	Gleb	Voroncov	79675605625	[e-mail]	Nalch, bld. 2/В, appt. 11	1682667535
5	Maria	Golubeva	79157788778	[e-mail]	Naberezhnye Chelny, Os 423802 A/ya 4	1682667536
6	Denis	Pavlenko	79210019471	[e-mail]	Vladimir, Esenina S. Ul., bld. 32, appt. 6	1682667537

• <= - Отбираются значения меньше или равные указанным

SELECT * FROM customers WHERE id <= 4

id	first_name	last_name	phone	email	address	register_at
1	Ivan	Ivanov	79156782211	[e-mail]	Moscow, Lenina St. 1, 100	1682334619
2	Vladislav	Petrov	79991452521	[e-mail]	Ufa, 50 october years St. 9, 11	1682447158
3	Ivan	Vasilyev	79261009615	[e-mail]	Vladimir, Lenina St. 32, 78	1682447158
4	Gleb	Voroncov	79675605625	[e-mail]	Nalch, bld. 2/В, appt. 11	1682667535

• != - Отбираются значения не равные указанному

SELECT * FROM customers WHERE first_name != 'Ivan'

id	first_name	last_name	phone	email	address	register_at
2	Vladislav	Petrov	79991452521	[e-mail]	Ufa, 50 october years St. 9, 11	1682447158
4	Gleb	Voroncov	79675605625	[e-mail]	Nalch, bld. 2/В, appt. 11	1682667535
5	Maria	Golubeva	79157788778	[e-mail]	Naberezhnye Chelny, Os 423802 A/ya 4	1682667536
6	Denis	Pavlenko	79210019471	[e-mail]	Vladimir, Esenina S. Ul., bld. 32, appt. 6	1682667537

• BETWEEN - Отбираются значения, находящиеся между указанными

SELECT * FROM customers WHERE id BETWEEN 2 AND 4

id	first_name	last_name	phone	email	address	register_at
2	Vladislav	Petrov	79991452521	[e-mail]	Ufa, 50 october years St. 9, 11	1682447158
3	Ivan	Vasilyev	79261009615	[e-mail]	Vladimir, Lenina St. 32, 78	1682447158
4	Gleb	Voroncov	79675605625	[e-mail]	Nalch, bld. 2/В, appt. 11	1682667535

• IN - Отбираются значения, содержащиеся в указанных

SELECT * FROM customers WHERE id IN (1, 3, 6)

id	first_name	last_name	phone	email	address	register_at
1	Ivan	Ivanov	79156782211	[e-mail]	Moscow, Lenina St. 1, 100	1682334619
3	Ivan	Vasilyev	79261009615	[e-mail]	Vladimir, Lenina St. 32, 78	1682447158
6	Denis	Pavlenko	79210019471	[e-mail]	Vladimir, Esenina S. Ul., bld. 32, appt. 6	1682667537

• NOT IN - Отбираются значения, не содержащиеся в указанных

SELECT * FROM customers WHERE id NOT IN (1, 3, 6)

id	first_name	last_name	phone	email	address	register_at
2	Vladislav	Petrov	79991452521	[e-mail]	Ufa, 50 october years St. 9, 11	1682447158
4	Gleb	Voroncov	79675605625	[e-mail]	Nalch, bld. 2/В, appt. 11	1682667535
5	Maria	Golubeva	79157788778	[e-mail]	Naberezhnye Chelny, Os 423802 A/ya 4	1682667536

• LIKE - Отбираются значения, соответствующие образцу с использованием следующих метасимволов:

  • _ - обозначает любой одиночный символ. Работает по аналогии так же как и в резулярных выражениях синтаксис .

  • % - обозначает любое количество символов, включая ноль символов. Работает по аналогии так же как и в резулярных выражениях синтаксис .*

SELECT * FROM customers WHERE address LIKE 'Vladimir%' -- эквивалентно Ivan.*

id	first_name	last_name	phone	email	address	register_at
1	Ivan	Ivanov	79156782211	[e-mail]	Moscow, Lenina St. 1, 100	1682334619
3	Ivan	Vasilyev	79261009615	[e-mail]	Vladimir, Lenina St. 1, 100	1682447158

SELECT * FROM customers WHERE last_name LIKE '%ov' -- эквивалентно .*ov

id	first_name	last_name	phone	email	address	register_at
1	Ivan	Ivanov	79156782211	[e-mail]	Moscow, Lenina St. 1, 100	1682334619
2	Vladislav	Petrov	79991452521	[e-mail]	Ufa, 50 october years St. 9, 11	1682447158

-- 
SELECT * FROM customers WHERE address LIKE '%Lenina%' -- эквивалентно .*Lenina.*

id	first_name	last_name	phone	email	address	register_at
1	Ivan	Ivanov	79156782211	[e-mail]	Moscow, Lenina St. 1, 100	1682334619
2	Vladislav	Petrov	79991452521	[e-mail]	Ufa, 50 october years St. 9, 11	1682447158

Сортировка

Для сортировки данных используется ключевое слово ORDER BY с передачей колонки, по которой будет происходить сортировка и указанием порядке. Порядок задается с помощью ключего слова ASC - от меньшего к большему и DESC - от большего к меньшему.

Синтаксис:

SELECT колонка, другая_колонка 
FROM таблица ORDER BY другая колонка ASC -- ASC можно не указывать, так как он является порядком по умолчанию.

Пример:

SELECT * FROM items
ORDER BY price DESC

В данном примере мы получаем список всех товаров отсортированныз по убыванию цены.

Пагинация

Как часто бывает, данных в таблице может быть много и доставать все данные оттуда нам нет смысла, поэтому обычно достают данные по частям.

В SQL мы можем указать сколько элементов необхдимо пропустить помощью ключего слова OFFSET. Например, у нас есть запрос, который возвращает нам 600 строк, тогда мы указываем OFFSET 20, тогда первые 20 строк будут пропущены и запрос отобразит нам 580 элементов. Важно понимать, что пропуск идет не от элементов в таблице, а от конечного результата.

Кроме того, у нас нет необходимости в 580 элементах, например на сайте мы часто видим 20-30 элементов, а дальше нам предлагают посмотреть следующую страницу товаров. Для указания лимита выборки используется ключевое слово LIMIT с кол-вом элементов, которое необхдоимо получить, напрмер если нам нужно будет 30 элементов, то запись будет выглядеть как LIMIT 30.

Теперь завайте рассмотрим пример из реальной жизни. Наш интернет магазин на сайте хочет показывать покупателям 20 товаров и возможностью постраничного просмотра. Тогда запрос для получения 1 страницы с товарами будет выглядеть следующим образом:

SELECT * FROM itemms OFFSET 0 LIMIT 20 -- пропусакем 0 элементов, так как мы показываем самую первую страницу размером в 20 элементов

А когда пользователь выбирает 2 страницу, то нам необходимо выполнить запрос с новым отступом:

SELECT * FROM itemms OFFSET 20 LIMIT 20 -- пропусакем 20 элементов, так как первые 20 элементов уже смотрели

Изменение данных

Для того, чтобы изменить данные в таблице необходимо использовать команду UPDATE.

Синтаксис:

UPDATE название_таблицы SET название_колонки = значение

Примерер:

UPDATE customers SET register_at = CURRENT_TIMESTAMP -- обновляем всем пользователям дату регистрации на текущее время

Но обновлять все данные в таблице редко когда нам может понадобиться, поэтому операция UPDATE позволяет использовать условия с помощью WHERE.

Синтаксис:

UPDATE название_таблицы SET название_колонки = значение WHERE условие

Пример:

 -- обновляем номер телефона у пользователя с id = 3
UPDATE customers SET phone = 79216732988 WHERE id = 3

Удаление данных

Для удаленния данных из таблицы необходимо использовать команду DELETE.

Синтаксис:

DELETE FROM название_таблицы

Пример:

DELETE FROM orders -- удаляем все заказы

Опять таки, удалять все данные неудобно, поэтому мы можем и в этой команде использовать WHERE.

Синтаксис:

DELETE FROM название_таблицы WHERE условие

Пример:

-- удаляем заказ с id равным 1
DELETE FROM orders WHERE id = 1

Команда TRUNCATE

Операция TRUNCATE создана для удаления всех данных из таблиц как и команда DELETE без условия. Частый вопрос на собеседовании: Чем TRUNCATE отличается от DELETE ? Ответ: TRUNCATE - это DDL операция, а DELETE это DML операция. DML удаляет данные построчно, то есть сначала удаляется одна строка, потом вторая и так далее, в то время как DDL операция оперирует таблицей. По сути TRUNCATE удаляет таблицу полностью и создает заново.

Задание university

• Заполните таблицы данными с помощью файла dump.sql приложенного к данному уроку. Для этого введите команду в консоль sqlite3 путь/до/university < путь/до/dump.sql.

• Проверьте, что данные успешно импортированы.

• Добавьте новый факультет в таблицу faculties с названием Statistician и идентификатором равным 5. Назовите ваш скрипт insert_faculties.sql

• Создайте скрипт insert_subject2faculty.sql с запросом на добавление и добавьте предметы в таблицу subject2faculty для этого факультета:

  • mathematics

  • computer science

  • business

• Создайте скрипт insert_student2faculty.sql с запросом на добавление и добавьте студентов в таблицу student2faculty для этого факультета:

  • Trever Springer

  • Ruperto Skettles

  • Maribeth Camble

• Создайте скрипт select_teachers_older_30_less_40.sql с запросом на получение учителей из таблицы teachers моложе 40 лет, но старше 30 лет.

• Создайте скрипт select_y_subjects.sql с запросом на поллучение предметов из таблицы subjects названия которых заканчиваются на y.

Дополнительный материал

• Команда INSERT

• Команда SELECT

  • Фильтрация WHERE

  • Операторы фильтрации

  • сортировка ORDER BY

  • Диапазон выборки

• Команда UPDATE

• Команда DELETE