SQLize Online / PHPize Online  /  SQLtest Online

; Добавление, изменение, удаление данных При работе с базой данных, а именно в момент реализации и тестирования, необходимо наполнить структуру таблиц базы данных, реализовать механизмы по редактированию данных, для этого применяются базовые команды языка DML: Insert, Update, Delete. Добавление данных Конструкция запроса на добавление данных: Действие PostgreSQL MySQL Одна запись за запрос Insert into <Table_Name> ({<Column_Name>[,]}) values ({<Input Values>[,]}); Множество записей за запрос Insert into <Table_Name> ({<Column_Name>[,]}) values {({<Input Values>[,]})[,]}; Описание ключевых слов: Insert into - добавить в какую-то таблицу; Values - после данного слова, перечисляются добавляемые строки; Input values - вводимые значения, заносятся в формате относительно типа данных столбца. Комментарии: 1. Ключевое столбец, если он автоинкрементен, в запросе - не участвует. СУБД автоматически присваивает значение в столбец; 2. Количество присваиваемых значений, совпадает по порядку и количеству с перечисленным количеством столбцов; 3. Столбцы могут быть записаны в любом порядке относительно запроса на создание таблицы; 4. Если столбец не обязателен для заполнения, то его можно не указывать в запросе; 5. Перед добавление данных, в дочернюю таблицу, в столбец внешнего ключа, необходимо ознакомиться со значениями первичного ключа дочерней таблицы через конструкцию Select * from <Parent_Table_Name>; 6. При ошибочном добавлении данных, а именно если срабатывает ограничение (Constraint): первичный (Pirmary key) или внешний (Foreign Key) ключ, значение автоинкрементного столбца, будет увеличено на +1. Примеры использования запросов: Добавление одной строки: Скрипт: Запрос на выборку данных: Результат: Добавление двух и более строк: Скрипт: Запрос на выборку данных: Результат: Изменение данных Конструкция запроса на изменение данных: Действие PostgreSQL MySQL Одна строка Update <Table_Name> set {<Column_Name> = [<Column_Name> <Action>] <Input Value>[,]} where <Key_Column> = <Key Value>; Множество строк Update <Table_Name> set {<Column_Name> = [<Column_Name> <Action>] <Input Value>[,]} where {<Column_Name> {=|<>|>=|<=|<|>|is [not] null} <Input Value> [{and|or}]}; Update <Table_Name> set {<Column_Name> = [<Column_Name> <Action>]<Input Value>[,]} where {<Key_Column> {=|<>|>=|<=|<|>} <Key Value> [{and|or}]}; Все строки Update <Table_Name> set {<Column_Name> = [<Column_Name> <Action>] <Input Value>}; - Описание ключевых слов: Set - слово обозначает, начало перечисления изменяемых полей и их новые значения; Action - подразумевает, любое действие с имеющимся значением в столбце, к примеру, к существующему числу прибавить новое значение или к существующей строке добавить подстроку; Where - слово обозначает область условия выполнения запроса, что бы эффект применялся к выборочным строкам; and - слово объединения условия в логическое "И". Запрос сработает тогда и только тогда, когда все перечисленные условия вернули истину; or - слово объединяет условия в логическое "ИЛИ". Запрос сработает тогда и только тогда, когда все или хотя бы одно условие вернуло истину; is [not] null - условие к полю, если оно пустое или не пустое. Комментарии: 1. В запросе, могут быть выборочно определены столбцы; 2. Каждое присваивание столбца с новым значением отделяется запятой; 3. Перед where запятая не ставится; 4. Перед изменением данных, во внешнем ключе, дочерней таблицы, необходимо ознакомиться со значениями суррогатного/первичного ключа родительской таблицы с помощью запроса select * from <Parent_Table_Name>; 5. Для PostgreSQL. Изменение данных в столбцах возможно не по ключевым столбцам, а по всем столбцам таблицы; 6. Для PostgreSQL. В случае отсутствия блока where, запрос будет применён ко всем записям таблицы; 7. Для MySQL. Изменение данных столбцах возможно только по ключевым столбцам, первичный или суррогатный ключ. Пример использования запросов: Изменение одной строки, одного атрибута. Вариант 1: Скрипт: Запрос на выборку данных: Результат: Изменение одной строки, одного атрибута. Вариант 2: Скрипт: Запрос на выборку данных: Результат: Изменение двух и более строк, двух и более атрибутов: Скрипт: Запрос на выборку данных: Результат: Изменение данных во всей таблице: Скрипт: Запрос: Результат: Удаление данных Конструкция запроса на удаление данных: Действие PostgreSQL MySQL Одна строка Delete from <Table_Name> where <Key_Column> = <Key Value>; Множество строк Delete from <Table_Name> where {<Column_Name> {=|<>|>=|<=|>|<|is [not] null} <Input Value> [{and|or}]}; Delete from <Table_Name> where {<Key_Column> {=|<>|>=|<=|>|<} <Key Value> [{and|or}]}; Все строки Delete from <Table_Name>; - Комментарии: 1. Удалять данные можно только из одной таблицы за один запрос; 2. Перед where, запятая не ставится; 3. После удаления записи, значение автоинкрементного столбца "пропадает" и не восстанавливается при добавлении новой строки; 4. Для PostgreSQL. Перед удалением данных, стоит ознакомится со значениями внешних ключей дочерней таблицы, с помощью запроса select * from <Child_Table_Name>, если удаляемое значение ключа, есть хотя бы в одной записи, одной дочерней таблицы, запись удалена не будет; 5. Для PostgreSQL. Если условие отсутствует то удалены будут все данные из таблицы; 6. Для MySQL. Условие для удаление записи строится только на основании ключевого столбца таблицы. Пример использования запросов: Удаление одной строки: Скрипт: Запрос на выборку данных: Результат: Удаление двух и более строк: Скрипт: Запрос на выборку данных: Результат: Удаление всех строк: Скрипт: Запрос на выборку данных: Результат: Принцип манипулирования данными при связи 1К1 (Первичные ключи) При реализации связи 1К1, при добавлении данных соблюдается следующий принцип: в первую очередь данные добавляются в родительскую таблицу, затем в псевдо-родительские таблицы. Пример скрипта реализации таблицы "Профили" (В первую очередь): Заполнение таблицы: Выборка данных: Пример скрипта реализации таблицы "Студенты" (Во вторую очередь): Заполнение таблицы: Выборка данных: Пример скрипта реализации таблицы "Преподаватели" (Во вторую очередь): Заполнение таблицы: Выборка данных: Если с добавление данных всё легко и просто, то с изменением и удалением данных ситуация сложнее. Если изменять, значения ключевых столбцов (В таблице "Профиль", столбец "Логины" в данном примере) или удаления записи , то произойдёт конфликт: Данная запись не может быть изменена или удалена, так как на основании неё созданы дочерние записи. Попытка изменить запись в родительской таблице: Скрипт: Результат: Попытка удаления записи из родительской таблицы: Скрипт: Результат: Решение: Изменение структуры таблиц, с добавлением функции каскадного изменения и удаления записи (Применяется строго в случае первичных ключей, а не суррогатных). Cascade - свойство объекта References, которое обеспечивает каскадное, последовательное изменение записей, внешних ключей в дочерних таблицах, в соответствии с изменениями первичных ключей родительских таблиц. Примечание. Данная ошибка в MySQL не возникает. При изменении или удалении родительской записи, СУБД не выдаст ошибку и спокойно удалит родительскую запись, а дочерние записи останутся не тронутыми. Конструкция реализации каскадной функции: Действие PostgreSQL Создание в структуре таблицы Create table [if not exists] <Table_Name> ( {<Column_Name> <Data type> [not] null references <Parent_Table_Name> (<Key_Column>) [{on {update|delete} cascade}][,]} ); Изменение структуры таблицы Alter table <Table_Name> {add constraint <Constraint_Name> foreign key (<Foreign_Column>) references <Parent_Table_Name> (<Key_Column>) [on {update|delete} cascade][,]}; Удаление каскадной функции Alter table <Table_Name> drop constraint <Constraint_Name>; Пример использования запроса: Вариант 1. Создание каскадной функции в структуре таблицы: Скрипт: Результат: Вариант 2. Изменение структуры таблицы, для добавления каскадной функции: Скрипт: Результат: После реализации каскадного изменения, теперь есть возможность производить изменение и удаление данных из родительской таблицы: Попытка изменения данных в родительской таблице: Скрипт: Результат "Профиль": Результат "Студент": Попытка удаления данных в родительской таблице: Скрипт: Результат "Профиль": Результат "Сотрудник": Важно! Данный подход, ни в коем случае нельзя применять ко всей структуре базы данных, так как если не будет предусмотрен механизм проверки возможности изменения или удаления данных, в лучшем случае, при изменении - данные перестанут быть достоверными, что можно будет исправить, в худшем случае, при удалении - одна единственная запись, может зацепить за собой и удалить практически все данные из всех таблиц базы данных. Выборка данных, Join, Union, сортировка данных СУБД, при чтении данных из таблиц базы данных, формирует информацию в виде двухмерного массива, где определены столбцы и строки. Для того, что бы "заставить" СУБД вывести определённое количество столбцов и строк, по пользовательским требованиям к преобразованию и форматированию, применяется запрос на выборку данных - Select. Select - ключевая команда при работе с языком SQL, которая обеспечивает вывод данных из структуры базы данных. Все данные, которые пользователь видит в приложении баз данных, выведены с помощью команды select. Выборка данных из одной таблицы Конструкция реализации запроса на выборку данных: Действие PostgreSQL MySQL Выборка всех столбцов Select * from <Table_Name> [as <Synonym>]; Выборка коллекции столбцов Select {[{<Table_Name>|<Synonym>}.]<Column_Name>[,]} from <Table_Name> [as <Synonym>]; Выборка столбцов с псевдонимами Select {[{<Table_Name>|<Synonym>}.]<Column_Name> [as "<Псевдоним>"][,]} from <Table_Name> [as <Synonym>]; Выборка конкатенируемых столбцов Select {{[||][{<Table_Name>|<Synonym>}.]<Column_Name>|'<Подстрока>'[||]}[as "<Псевдоним>"][,]} from <Table_Name> [as <Synonym>]; Select {concat({{[{<Table_Name>|<Synonym>}.]<Column_Name>|'<Подстрока>'},})|<Column_Name>|'<Подстрока>'} from <Table_Name> [as <Synonym>]; Выборка данных с сортировкой Select {*|{Collection|Concatination}[,][as "<Псевдоним>"]}} from <Table_Name> [as <Synonym>] [order by {<Column_Name>{ASC|DESC}[,]}]; Описание ключевых слов: * - обозначает, что из таблицы будут выбраны все имеющиеся столбцы из таблицы; Synonym - позволяет дать названию таблицы, синоним (Не влияет на структуру и описание самой таблицы); as - после данной команды идёт название, чаще всего логическое, столбца или группы столбцов, указанное в двойных кавычках; || - позволяют конкатенировать два столбца (или подстроки) в одно единое поле строкового типа данных; concat() - встроенная функция языка SQL, которая возвращает единый строковый столбец, в качестве формальных параметров выступают: столбцы и подстроки; order by - команды, после которых перечисляются поля по которым будет произведена сортировка данных; ASC - от английского ascending, производит сортировку столбца в прямом порядке от меньшего к большему; DESC - от английского descending, производит сортировку столбца в обратном порядке от большего к меньшему. Комментарии: 1. Между select и from обязательно должны быть указаны поля или *. Без этого запрос не отработает; 2. Между select и from, одно и тоже поле может быть указанно сколько угодно раз; 3. При конкатенации столбцов, если один из столбцов пустой, конкатенация вернёт null; 4. При конкатенации столбцов, если один из кортежей пустой, строка вернёт null; 5. При написании команды order by, если после названия столбца не указать ASC или DESC, по умолчанию запрос отсортирует указанный столбец в прямом порядке; 6. При написании команды order by, столбец указанный в сортировке, может отсутствовать в области определения столбцов между select и from. Пример использования запросов: Выборка всех полей: Скрипт: Результат: Выборка коллекции полей: Скрипт: Результат: Переименование полей: Скрипт: Результат: Конкатенация полей: Скрипт: Результат: Сортировка данных, прямой порядок: Скрипт: Результат: Сортировка данных, обратный порядок: Скрипт: Результат: Join. Выборка данных из двух и более таблиц При работе с таблицами базы данных, редким случаем является выборка данных только из одной таблицы (исключение родительские, справочные таблицы), чаще всего данные выбираются с из двух и более таблиц. Принцип такой выборки заключается в следующем: дочерняя таблица по правилам нормализации принимает данные из родительской таблицы - внешний ключ получает данные из суррогатного ключа, эти ключи являются целочисленными значениями (за исключением связи 1К1), но конечный пользователь, не должен, в приложении базы данных, видеть значения внешних ключей, он должен видеть данные которые соответствуют значению данного внешнего ключа из не ключевых атрибутов родительской таблицы. На иллюстрации ниже продемонстрирован данный принцип. Вывод данных без объединения: Вывод данных с объединением: Для реализации данного подхода, конструкция select обладает дополнительной командой, которая позволяет объединять данные таблиц, команда - join. Join on - специальная конструкция, которая обеспечивает объединение данных двух таблиц в одну результирующую таблицу, после ключевого слова on идет приравнивание поля суррогатного ключа с внешним ключом (в случае связи 1К1 сравнение двух первичных ключей). Конструкция реализации Join: Действие PostgreSQL MySQL Объединение Select {*|{Collection|Concatination}[,][as "<Псевдоним>"]}} from <Start_Table_Name> [as <Synonym>] [{join <Next_Table_Name> [as <Synonym>] on [{<Table_Name>|<Synonym>}.]<Key_Column> {=|<>} [{<Table_Name>|<Synonym>}.]<Key_Column>}] [{where <Key_Column> is [not] null [{and|or}]}] [order by {<Column_Name>{ASC|DESC}[,]}]; Описание ключевых слов: Start_Table_Name - название стартовой таблицы, с которой начинается объединение таблиц; Next_Table_Name - название следующей, присоединяемой, таблицы; join - команда объединения таблиц; on - после данной команды идёт указание сравнения ключевых столбцов; Key_Column - название столбцов, суррогатного, первичного или внешнего ключа. Комментарии: 1. При объединении данных, между select и from, можно указывать все поля, которые указаны в объединяемых таблицах; 2. Объединение таблиц строится строго по связям указанные в даталогической модели данных. Принцип объединения: ERD-Модель: 1. После From указывается стартовая таблица. Итого объединено - 1-а таблица, 2-а столбца: Скрипт: Результат: 2. После 1-ого join идёт 1-ая таблица для объединения. Итого объединено - 2-е таблицы, 6-ть столбцов: Скрипт: Результат: 3. После 2-ого join идёт 2-ая таблица для объединения. Итого объединено - 3-и таблицы, 11-ть столбцов. Скрипт: Результат: При описании связи, следует различать термины правая и левая таблица: Правая таблица - указана после join, левая таблица - указана перед join. Конструкция join имеет 8-мь вариантов описания, каждый из которых используется для различных результатов вывода: Исходные данные: [inner] join - позволяет вывести данные, которые сопоставлены из двух таблиц, при этом данные которые не сопоставлены между собой, выведены не будут; Множество: Скрипт: Результат: left join - позволяет вывести данные, которые сопоставлены из двух таблиц и не сопоставленные данные из левой таблицы, не сопоставленные данные из правой таблицы выведены не будут; Множество: Скрипт: Результат: left join (с нулевыми значениями) - позволяет вывести, только не сопоставленные данные из левой таблицы, сопоставленные данные и данные из правой таблицы выведены не будут; Множество: Скрипт: Результат: right join - позволяет вывести данные, которые сопоставлены из двух таблиц и не сопоставленные данные из правой таблицы, не сопоставленные данные из левой таблицы выведены не будут; Множество: Скрипт: Результат: right join (с нулевыми значениями) - позволяет вывести, только не сопоставленные данные из правой таблицы, сопоставленные данные и данные из левой таблицы выведены не будут;\ Множество: Скрипт: Результат: full outer join - позволяет вывести все данные: сопоставленные и не сопоставленные из правой, левой таблиц; Множество: Скрипт: Результат: full outer join (с нулевыми значениями) - позволяет вывести только не сопоставленные данные из правой и левой таблиц, сопоставленные данные выведены не будут; Множество: Скрипт: Результат: cross join - позволяет вывести полный перебор всех возможных сопоставлений данных между двумя таблицами, формируя своеобразную таблицу истинностей. Скрипт: Результат: Union. Объединение двух и более таблиц в одну единую с фиксированным количеством столбцов При объединении данных, через Join, количество результирующих столбцов суммируется с каждым новым объединением, но есть задачи в которых, при объединении данных, происходит аномалия, которая нарушает правило нормализации - ни одно поле не пусто. Возникает потребность в объединении данных из двух и более таблиц, в результирующую таблицу с равным количеством столбцов. Проект требования: Для данного решения, применяется команда Union. Union - позволяет объединять два и более запроса на выборку данных, в одну результирующую таблицу с равными по количеству и однотипными столбцами. Конструкция реализации Union: Действие PostgreSQL MySQL Объединение {Select {*|{Collection|Concatination}[,][as "<Псевдоним>"]}} from <Table_Name> <Join> <Where> <Order by> [Union [All]]}; Описание ключевых слов: Join - вместо слова указывается конструкция на объединение данных; Where - вместо слова указывается конструкция условий для ключевых столбцов; Order by - вместо слов указывается конструкция сортировки данных; Union - позволяет перебирать и перемешивать последовательно строки их двух и более select; Union All - позволяет сперва произвести полную выборку данных из select до union all, а только потом из select после union all. Комментарии: 1. Количество, типы данных и порядок полей, в каждом последующем Union, совпадает с количеством, типом данных и порядком, первого Select; 2. Псевдонимы столбцов применяются к первому select. Пример использования запросов: Скрипт: Результат: Использование нативного подхода к манипулированию данными При реализации приложений баз данных, классов, методов, модулей, можно применять подход прямого обращения запросов, на добавление, изменение и удаление данных в таблицах базы данных - нативный подход, т.е. обращение через прямые конструкции запросов. В данном подходе, на месте передаваемых значений, указывают подстроки, или переменные куда передаются данные в запрос, после чего приложение отправляет запрос СУБД на обработку. Ниже приведена иллюстрация показывающая принцип использования нативного подхода. Использование хранимых процедур для манипулирования данными При необходимости скрыть структуру таблиц и столбцов, в момент добавления данных в базу данных, можно применять подход, через хранимые процедуры. Реализовываются процедуры, с входными формальными параметрами, которые повторяют в зависимости от события - количество и типы данных столбцов, для максимально целостного и непротиворечивого ввода данных в базу данных, а именно: При добавлении данных - указываются формальные параметры, для всех столбцов, за исключением столбца суррогатного, автоинкрементного, ключа (в случае с первичным ключом поле указывается); При изменении данных - указываются формальные параметры, для всех столбцов (в случае использования, не суррогатного, а первичного ключа, для столбца первичного ключа, стоит указать два параметра: старое и новое значение); При удалении данных - указывается только формальный параметр для суррогатного или первичного ключа. Пример использования хранимых процедур при работе с данными: Добавление данных: Скрипт создания процедуры: Вызов хранимой процедуры: Результат: Изменение данных: Скрипт создания процедуры: Вызов хранимой процедуры: Результат: Удаление данных: Скрипт создания процедуры: Вызов хранимой процедуры: