Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В процессе добавления записей в раздел библиотека SQLite. Я каким-то странным образом забыл опубликовать шестую часть темы работа с таблицами базы данных, в которой хотел рассказать про обновление и модификацию данных в таблицах баз данных под управлением SQLite. Из-за этого немного нарушится структура рубрики, так как это упущение я намерен все-таки исправить. Мы уже рассмотрели все возможные операции, которые можем совершать над таблицами базы данных SQLite и даже начали разговор про обеспечение целостности данных в базах данных. Поэтому мне осталось исправить свою невнимательность и рассказать о том, как изменять данные в таблицах базы данных на примере библиотеки SQLite3.

В этой записи мы с вами разберемся с синтаксисом операции изменения данных в таблицах и рассмотрим несколько примеров. Сперва будет пара простых примеров модификации данных в таблицах SQLite3. Затем мы посмотрим, как отфильтровать данные, перед тем, как будем их модифицировать. После чего мы с вами разберемся с тем, как изменить данные сразу в нескольких столбцах таблицы базы данных SQLite. Далее будет пример изменения значений во всех строках таблицы. И напоследок мы рассмотрим, как упорядчить ограничить количество строк для модификации данных.

Надеюсь, что публикация получилась понятной и написана она на доступном языке, но, как обычно, если есть какие-либо уточнения или что-то было непонятно — пишите в комментариях, обсудим вместе.

Изменение и модификация данных в таблицах базы данных SQLite3

Мы рассмотрели работу различных операторов манипуляции данными и операторов определения данных в базах данных SQLite, но при работе с таблицами базы данных SQLite мы можем еще изменять и модифицировать данные. Пожалуйста, не путайте операцию модификации данных в таблицах баз данных с операцией модификации таблиц, последняя выполняется при помощи команды ALTER.

Так же не стоит путать SQL команды с SQL операторами, среди русскоговорящих разработчиков принято заменять термин команда на термин оператор, хотя это и не одно и то же, если быть формально точным.  Модификация или обновления данных в базах данных SQLite выполняется командой UPDATE, хотя вы можете использовать словосочетание оператор UPDATE, большой ошибки не будет.

В SQLite можно реализовать каскадные операции по модификации данных, но об этом мы поговорим, когда будем рассматривать ограничения уровня таблицы в теме обеспечения целостности данных, сейчас нам нужно разобраться с тем, как выполняются обычные операции модификации данных в таблицах базы данных SQLite.

Синтаксис операции изменения данных в таблицах баз данных под управлением SQLite представлен на рисунке ниже. Заметим, что синтаксис модификации данных в таблицах SQLite поддерживает операции, позволяющие избежать конфликтов модификации, но об этом мы поговорим в специально отведенной теме.

Ключевое слово UPDATE говорит SQLite о том, что мы хотим изменить данные в таблице, далее нам нужно указать SQLite3 имя таблицы, данные которой мы будем модифицировать, в том случае когда у вас подключено несколько баз данных, вы можете использовать квалификатор или полное имя таблицы, который помимо имени таблицы содержит еще и имя базы данных этой таблицы.

После имени таблицы следует ключевое слово SET, которое позволяет указать имя столбца, в котором мы хотим изменить данные. После ключевого слова SET идет знак равно, после которого мы пишем новое значение, которое хотим поместить в базу данных. А далее самое интересное: клаузула WHERE (уточняющая фраза WHERE), с помощью которой мы задаем условия, которые позволят выбрать строки, в которых данные будут модифицированы.

Перед изменением данных в таблице SQLite позволяет эти данные упорядочить и ограничить количество строк, которые будут модифицированы. SQLite3 позволяет модифицировать данные сразу в нескольких столбцах таблицы при этом столбцы и их модфикационные значения разделяются запятой, а условия выбора строки для модификации следует после перечисления всех необходимых столбцов.

В базах данных SQLite в качестве нового значения не обязательно указывать какую-то конкретную строку или столбец, можно использовать сложные выражения и даже SQL запросы, например, вместо значения вы можете использовать команду SELECT, причем SELECT может быть сколь угодно длинным.

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

Простые примеры модификации данных в базах данных SQLite

Начнем с простых примеров модификации данных в таблицах баз данных SQLite. Для начала нам нужно создать таблицу при помощи команды CREATE:

[php]

CREATE TABLE table1 (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

age INTEGER NOT NULL,

pro TEXT NOT NULL,

sal REAL CHECK (sal>500)

);

[/php]

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

Давайте теперь добавим строки в таблицу, в SQLite для этого используется команда INSERT:

[php]

INSERT INTO table1 (name, age, pro, sal)

VALUES (‘Степан Морковин’, 45, ‘Программист’, 44432.20);

INSERT INTO table1 (name, age, pro, sal)

VALUES (‘Людмила Яковлева’, 21, ‘Бухгалтер’, 35232.20);

INSERT INTO table1 (name, age, pro, sal)

VALUES (‘Иван Афанасьев’, 28, ‘Инженер сопровождения’, 31032.15);

[/php]

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

Убедимся, что данные были добавлены в таблицу, сделав выборку данных из базы данных, воспользовавшись командой SELECT:

[php]

sqlite> SELECT * FROM table1;

1|Степан Морковин|45|Программист|44432.2

2|Людмила Яковлева|21|Бухгалтер|35232.2

3|Иван Афанасьев|28|Инженер сопровождения|31032.15

[/php]

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

[php]

UPDATE table1 SET name = ‘Людмила Иванова’ WHERE id = 2;

Убедимся, что модификация данных прошла успешно, выполнив команду SELECT:

sqlite> SELECT * FROM table1;

1|Степан Морковин|45|Программист|44432.2

2|Людмила Иванова|21|Бухгалтер|35232.2

3|Иван Афанасьев|28|Инженер сопровождения|31032.15

[/php]

Действительно, данные во второй строке таблицы были модифицированы, и мы получили значение «Людмила Иванова» вместо «Людмила Яковлева». Теперь напишем пример модификации данных в таблице базы данных SQLIte3 с использованием квалификатора. Обратите внимание: этот пример будет работать только в том случае, если вы будете работать сразу с несколькими базами данных:

[php]

UPDATE dbname.table1 SET name = ‘Людмила Иванова’ WHERE id = 2;

[/php]

Мы рассмотрели простые примеры модификации данные в базах данных под управлением SQLite, давайте перейдем к чему-нибудь более сложному.

Фильтрация данных перед их изменением в базах данных SQLite3

Иногда бывает нужно сделать фильтрацию данных перед тем, как внести изменения. Давайте посмотрим, как отфильтровать данные, а потом изменить данные в таблицах базы данных SQLite. Для этого добавим несколько строк в нашу таблицу:

[php]

INSERT INTO table1 (name, age, pro, sal)

VALUES (‘Алексей Мозгов’, 37, ‘Программист’, 40430.01);

INSERT INTO table1 (name, age, pro, sal)

VALUES (‘Анна Петрова’, 21, ‘Программист’, 37241.08);

INSERT INTO table1 (name, age, pro, sal)

VALUES (‘Анатолий Вассерман’, 58, ‘Директор’, 9999999.99);

[.php]

Допустим, программисты у нас хорошо работают, компания развивается и получает всё более сложные и интересные проекты, поэтому директор принял решение увеличить им всем зарплату на 5%, давайте посмотрим, как нам отфильтровать всех программистов и затем изменить данные о зарплате:

[php]

UPDATE table1 SET sal = (sal*1.05) WHERE pro = ‘Программист’;

[/php]

Убедимся в этом, воспользовавшись SQL запросом SELECT:

[php]

sqlite> select*from table1;

1|Степан Морковин|45|Программист|46653.81

2|Людмила Иванова|21|Бухгалтер|35232.2

3|Иван Афанасьев|28|Инженер сопровождения|31032.15

4|Алексей Мозгов|37|Программист|42451.5105

5|Анна Петрова|21|Программист|39103.134

6|Анатолий Вассерман|58|Директор|9999999.99

[/php]

Видим, что изменения в таблицы базы данных были внесены успешно и зарплата программистов действительно увеличилась на 5%, для нового значения мы использовали математическое выражение. Раз заказов стало больше, то и документооборот расширился, в компанию наняли двух бухгалтеров:

[php]

INSERT INTO table1 (name, age, pro, sal)

VALUES (‘Оксана Кулакова’, 29, ‘Бухгалтер’, 35232.20);

INSERT INTO table1 (name, age, pro, sal)

VALUES (‘Ирина Лобанова’, 41, ‘Бухгалтер’, 35232.20);

[/php]

Компания заботится о воспитании молодых кадров, поэтому было принято решение повысить зарплату на 3000 рублей всем сотрудникам, не достигшим 30 лет и с зарплатой меньше 40 000 рублей. Давайте внесем изменение в нашу таблицу базы данных под управлением SQLite3:

[php]

UPDATE table1 SET sal = (sal + 3000) WHERE age < 30 AND sal < 40000;

[/php]

Проверим результаты изменения данных в таблице командой SELECT:

[php]

SELECT * FROM table1;

1|Степан Морковин|45|Программист|46653.81

2|Людмила Иванова|21|Бухгалтер|38232.2

3|Иван Афанасьев|28|Инженер сопровождения|34032.15

4|Алексей Мозгов|37|Программист|42451.5105

5|Анна Петрова|21|Программист|42103.134

6|Анатолий Вассерман|58|Директор|9999999.99

7|Оксана Кулакова|29|Бухгалтер|38232.2

8|Ирина Лобанова|41|Бухгалтер|35232.2

[/php]

Изменения были внесены в таблицу успешно, а для фильтрации данных мы использовали два условия, задействовав SQL  оператор AND.

Модификация данных в нескольких столбцах базы данных SQLite

Мы можем модифицировать данные сразу в нескольких столбцах таблицы базы данных. Для такой модификации нужно перечислить столбцы и их новые значения через запятую, после чего сделать фильтрацию строк. Давайте реализуем пример модификации данных в нескольких столбцах. Хитрая Оксана Кулакова вышла замуж за директора и стала главным бухгалтером: она сменила фамилию, у нее изменилась должность и повысилась зарплата в три раза, модифицируем:

[php]

UPDATE table1 SET

sal = (sal*3),

name = ‘Оксана Вассерман’,

pro = ‘Главный Бухгалтер’

WHERE id=7;

[.php]

Убедимся в том, что данные в таблице модифицированы успешно:

[php]

sqlite> select*from table1;

7|Оксана Вассерман|29|Главный Бухгалтер|114696.6

[/php]

Мы модифицировали успешно данные в нескольких столбцах таблицы базы данных.

Изменение всех строк в таблице базы данных SQLite

Мы посмотрели уже несколько вариантов использования команды UPDATE и уже умеем по-разному изменять данные в строках таблицы базы данных SQLite, но бывают ситуации, когда нам нужно изменить значения во всех строках таблицы базы данных, давайте внесем такое изменение.

В стране кризис, партнеры компании сокращают издержки, уменьшается объем и директору не хватает денег «на хлеб». Поэтому, скрипя зубами и двигая извилиной, он принимает решение платить серую зарплату, переводя всех сотрудников на белый оклад 10 000 рублей и черную премию, давайте изменим данные во всех строках нашей таблицы:

[php]

UPDATE table1 SET sal = 10000;

[/php]

Убедимся в том, что значения всех строк таблицы были изменены:

[php]

sqlite> select * from table1;

1|Степан Морковин|45|Программист|10000.0

2|Людмила Иванова|21|Бухгалтер|10000.0

3|Иван Афанасьев|28|Инженер сопровождения|10000.0

4|Алексей Мозгов|37|Программист|10000.0

5|Анна Петрова|21|Программист|10000.0

6|Анатолий Вассерман|58|Директор|10000.0

7|Оксана Вассерман|29|Главный Бухгалтер|10000.0

8|Ирина Лобанова|41|Бухгалтер|10000.0

[/php]

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

Пример сортировки строк перед модификацией данных в таблице базы данных SQLite3

Когда мы делаем модификацию данных в таблице, мы можем сначала отсортировать данные, а потом их удалить, но сортировка данных не имела бы смысла без ограничения количества строк перед их модификацией. За сортировку строк отвечает клаузула ORDER BY, за ограничение строк отвечает клаузула LIMIT.

Кризис усиливается, денег не хватает… Директор видит, что в его компании развелось этих программистов и решает оптимизировать штат, уволив самого старого, так как не видит в нем перспективы. Естественно, он не знает, как зовут программиста и не знает под каким номером программист записан в базе данных. Давайте реализуем данную операцию модификации данных при помощи сортировки и ограничения:

[php]

UPDATE table1 SET

name = ‘Уволен’,

age = 0,

pro = ‘Уволен’,

sal = 0

WHERE pro = ‘Программист’

ORDER BY age DESC LIMIT 1;

[/php]

Мы модифицировали строку таблицы базы данных, но перед этим SQLite3 выполнила сортировку данных и затем ограничила количество модифицируемых строк до одной.

Мы рассмотрели различные способы модификации и изменения данных и строк в таблицах базы данных SQLite и на этом закончили тему работа с таблицами в базах данных SQLite.