Sql и базы данных
 Скачать 1.44 Mb.
|
|
3. Что такое TCL? Какие операции в него входят? Рассказать про них. операторы управления транзакциями (Transaction Control Language, TCL): ● COMMIT Применяется для завершения транзакции и сохранения изменений в базе данных, DELETE FROM developers WHERE SPECIALTY = 'C++'; COMMIT; ● ROLLBACK откатывает все изменения, сделанные в контексте текущей транзакции, ● SAVEPOINT Создаёт точку к которой группа транзакций может откатиться, разбивает транзакцию на более мелкие. SET TRANSACTION Применяется для установки параметров доступа к данным в текущей транзакции указать, что транзакция предназначена только для чтения, то мы должны использовать следующий запрос: SET TRANSACTION READ ONLY; Команды управление транзакциями используются только для DML команд: INSERT, UPDATE, DELETE транкейт не используется когда в таблице есть внешние ключи надо использовать делит 4. Что такое DCL? Какие операции в него входят? Рассказать про них. операторы определения доступа к данным (Data Control Language, DCL): ● GRANT предоставляет пользователю (группе) разрешения на определенные операции с объектом, ● REVOKE отзывает ранее выданные разрешения, ● DENY задает запрет, имеющий приоритет над разрешением; -- Предоставление права чтения таблицы students пользователю alex. GRANT SELECT ON students TO alex; -- Запрет права выборки из таблицы orders пользователя alex. DENY SELECT ON orders TO alex; -- Отменить запрет. REVOKE SELECT ON total FROM piter; 5. Нюансы работы с NULL в SQL. Как проверить поле на NULL? NULL - специальное значение (псевдозначение), которое может быть записано в поле таблицы базы данных. NULL соответствует понятию «пустое поле», то есть «поле, не содержащее никакого значения». NULL означает отсутствие, неизвестность информации. Значение NULL не является значением в полном смысле слова: по определению оно означает отсутствие значения и не принадлежит ни одному типу данных. Поэтому NULL не равно ни логическому значению FALSE , ни пустой строке, ни 0. При сравнении NULL с любым значением будет получен результат NULL, а не FALSE и не 0. Более того, NULL не равно NULL! команды: IS NULL, IS NOT NULL 6. Виды Join’ов? JOIN - оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Предназначен для обеспечения выборки данных из двух таблиц и включения этих данных в один результирующий набор. Особенностями операции соединения являются следующее: в схему таблицы-результата входят столбцы обеих исходных таблиц (таблиц-операндов), то есть схема результата является «сцеплением» схем операндов; каждая строка таблицы-результата является «сцеплением» строки из одной таблицы- операнда со строкой второй таблицы-операнда; при необходимости соединения не двух, а нескольких таблиц, операция соединения применяется несколько раз (последовательно). SELECT поля_таблиц FROM таблица_1 [INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_2 ON условие_соединения [[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_n ON условие_соединения] https://habr.com/ru/post/450528/ LEFT [OUTER] SELECT поля_таблиц FROM левая_таблица LEFT JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ Получение всех данных из левой таблицы, соединённых с соответствующими данными из правой INNER JOIN, но дополнительно мы добавляем null для строк из первой таблицы, для которой ничего не нашлось во второй RIGHT [OUTER] SELECT поля_таблиц FROM левая_таблица RIGHT JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ Получение всех данных из правой таблицы, соединённых с соответствующими данными из левой это INNER JOIN + null для строк из второй таблицы, для которой ничего не нашлось в первой LEFT [OUTER] правая_таблица.ключ IS NULL SELECT поля_таблиц FROM левая_таблица LEFT JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ WHERE правая_таблица.ключ IS NULL RIGHT [OUTER] левая_таблица.ключ IS NULL SELECT поля_таблиц FROM левая_таблица RIGHT JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ WHERE левая_таблица.ключ IS NULL Получение данных, относящихся только к правой таблице INNER SELECT поля_таблиц FROM левая_таблица INNER JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ тот же самый CROSS JOIN, у которого оставлены только те элементы, которые удовлетворяют условию, записанному в конструкции "ON". FULL OUTER SELECT поля_таблиц FROM левая_таблица FULL OUTER JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ FULL OUTER Без совместных данных SELECT поля_таблиц FROM левая_таблица FULL OUTER JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ WHERE левая_таблица.ключ IS NULL OR правая_таблица.ключ IS NULL CROSS JOIN декартово произведение При выборе каждая строка одной таблицы объединяется с каждой строкой второй таблицы, давая тем самым все возможные сочетания строк двух таблиц. Порядок таблиц для оператора не важен, поскольку оператор является симметричным. 7. Что лучше использовать join или подзапросы? Почему? Обычно лучше использовать JOIN, поскольку в большинстве случаев он более понятен и лучше оптимизируется СУБД (но 100% этого гарантировать нельзя). Так же JOIN имеет заметное преимущество над подзапросами в случае, когда список выбора SELECT содержит столбцы более чем из одной таблицы. Подзапросы лучше использовать в случаях, когда нужно вычислять агрегатные значения и использовать их для сравнений во внешних запросах. 8. Что делает UNION? Объединяет запросы в одну таблицу SELECT поля_таблиц FROM список_таблиц UNION [ ALL ] SELECT поля_таблиц FROM список_таблиц ; UNION по умолчанию убирает повторения в результирующей таблице. Для отображения с повторением есть необязательный параметр ALL. Не путайте операции объединения запросов с операциями объединения таблиц. Для этого служит оператор JOIN. Не путайте операции объединения запросов с подзапросами. Подзапросы выполняются для связанных таблиц. Объединение таблиц оператором UNION выполняется для таблиц никак не связанных, но со схожей структурой. SELECT DISTINCT Goods good_name AS name FROM Goods UNION SELECT DISTINCT FamilyMembers member_name AS name FROM FamilyMembers ; Скидывает все значения в один столбец (сначала первая таблица, потом вторая) Для того, чтобы UNION корректно сработал нужно: чтобы результирующие таблицы каждого из SQL запросов имели одинаковое число столбцов, с одним и тем же типом данных и в той же самой последовательности. 9. Чем WHERE отличается от HAVING ( ответа про то что используются в разных частях запроса - недостаточно)? Отличие HAVING от WHERE: -WHERE — сначала выбираются записи по условию, а затем могут быть сгруппированы, отсортированы и т.д. Это ограничивающее выражение. Оно выполняется до того, как будет получен результат операции. -HAVING — сначала группируются записи, а затем выбираются по условию, при этом, в отличие от WHERE, в нём можно использовать значения агрегатных функций - Выражения WHERE используются вместе с операциями SELECT, UPDATE, DELETE, в то время как HAVING только с SELECT и предложением GROUP BY. То есть, использовать WHERE в запросах с агрегатными функциями нельзя, для этого и был введен HAVING. 10. Что такое ORDER BY? SELECT поля_таблиц FROM список_таблиц ORDER BY столбец_1 [ ASC | DESC ][, столбец_n [ ASC | DESC ]]; Правило сортировки применяется только к тому столбцу, за которым оно следует. 11. Что такое GROUP BY? Иногда требуется узнать информацию не о самих объектах, а об определенных группах, которые они образуют. Для этого используется оператор GROUP BY и агрегатные функции. SELECT family_member , SUM ( unit_price * amount ) FROM Payments GROUP BY family_member ; При использовании GROUP BY все значения NULL считаются равными Агрегатные функции применяются для значений, не равных NULL. Исключением является функция COUNT() SUM(поле_таблицы) Возвращает сумму значений AVG(поле_таблицы) Возвращает среднее значение COUNT(поле_таблицы) Возвращает количество записей MIN(поле_таблицы) Возвращает минимальное значение MAX(поле_таблицы) Возвращает максимальное значение 12. Что такое DISTINCT? Оператор SQL DISTINCT используется для указания на то, что следует работать только с уникальными значениями столбца. Может использоваться с агрегатными функциями SELECT COUNT(DISTINCT Singer) AS CountOfSingers FROM Artists 13. Что такое LIMIT? SELECT поля_выборки FROM список_таблиц LIMIT [ количество_пропущенных_записей ,] количество_записей_для_вывода ; когда необходимо сделать отступ от начала таблицы, предназначена конструкция OFFSET FETCH Для того, чтобы вывести строки с 3 по 5, нужно использовать такой запрос: SELECT * FROM Company LIMIT 2 , 3 ; 14. Что такое EXISTS? EXISTS берет подзапрос, как аргумент, и оценивает его как TRUE, если подзапрос возвращает какие-либо записи и FALSE, если нет. CREATE DATABASE IF NOT EXIST имя_базы_данных ; DROP DATABASE IF EXIST имя_базы_данных ; Обычно предикат EXISTS используется в зависимых (коррелирующих) подзапросах. Этот вид подзапроса имеет внешнюю ссылку, связанную со значением в основном запросе. Результат подзапроса может зависеть от этого значения и должен оцениваться отдельно для каждой строки запроса, в котором содержится данный подзапрос. Поэтому предикат EXISTS может иметь разные значения для разных строк основного запроса Найти тех производителей портативных компьютеров, которые также производят принтеры: 1. SELECT DISTINCT maker 2. FROM Product AS lap_product 3. WHERE type = 'laptop' AND 4. NOT EXISTS ( SELECT maker 5. FROM Product 6. WHERE type = 'printer' AND 7. maker = lap_product.maker 8. ) ; 15. Расскажите про операторы IN, BETWEEN, LIKE. [NOT] IN — позволяет узнать входит ли проверяемое значение столбца в список определённых значений SELECT * FROM Salespeople WHERE city IN ( 'Barcelona', 'London' ); SELECT * FROM Salespeople WHERE city = 'Barcelona' OR city = 'London'; [NOT] BETWEEN min AND max — позволяет узнать расположено ли проверяемое значение столбца в интервале между min и max. BETWEEN может работать с символьными полями в терминах эквивалентов ASCII. Это означает что вы можете использовать BETWEEN чтобы выбирать ряд значений из упорядоченных по алфавиту значений. SELECT * FROM Salespeople WHERE ( comm BETWEEN .10, AND .12 ) AND NOT comm IN ( .10, .12 ); SELECT * FROM Customers WHERE cname BETWEEN 'A' AND 'G'; [ NOT] LIKE шаблон [ESCAPE символ] — позволяет узнать соответствует ли строка (только CHAR или VARCHAR) определённому шаблону. В качестве условия используются символы трафаретные символы (wildkards) Трафаретные символы: - символ подчеркивания (_), который можно применять вместо любого единичного символа в проверяемом значении - символ процента (%) заменяет последовательность любых символов (число символов в последовательности может быть от 0 и более) в проверяемом значении. ESCAPE- символ используется для экранирования трафаретных символов. Например, вы хотите получить идентификаторы задач, прогресс которых равен 3%: SELECT job_id FROM Jobs WHERE progress LIKE '3!%' ESCAPE '!' ; 16. Что делает оператор MERGE? Какие у него есть ограничения? MERGE позволяет осуществить слияние данных одной таблицы с данными другой таблицы. При слиянии таблиц проверяется условие, и если оно истинно, то выполняется UPDATE, а если нет - INSERT. При этом изменять поля таблицы в секции UPDATE, по которым идет связывание двух таблиц, нельзя. Является командой DML. MERGE INTO table_name USING table_reference ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 …] WHEN NOT MATCHED THEN INSERT (column1 [, column2 …]) VALUES (value1 [, value2 …]); MERGE INTO person p USING ( SELECT tabn, name , age FROM person1) p1 ON (p.tabn = p1.tabn) WHEN MATCHED THEN UPDATE SET p.age = p1.age WHEN NOT MATCHED THEN INSERT (p.tabn, p. name , p.age) VALUES (p1.tabn, p1. name , p1.age) 17. Какие агрегатные функции вы знаете? Агрегатных функции - функции, которые берут группы значений и сводят их к одиночному значению. SQL предоставляет несколько агрегатных функций: - COUNT - производит подсчет записей, удовлетворяющих условию запроса; - SUM - вычисляет арифметическую сумму всех значений колонки; - AVG - вычисляет среднее арифметическое всех значений; - MAX - определяет наибольшее из всех выбранных значений; - MIN - определяет наименьшее из всех выбранных значений - SUBSTRING (выражение, начальная позиция, длина) позволяет извлечь из выражения его часть заданной длины, начиная от заданной начальной позиции - STRING_AGG - конкатенирует строки - STRING_SPLIT выполняет операцию, обратную STRING_AGG. Она принимает на входе символьную строку и разбивает её на подстроки по заданному вторым параметром разделителю - LOWER (строковое выражение) и UPPER(строковое выражение) преобразуют все символы аргумента, соответственно, к нижнему и верхнему регистру В чем разница между COUNT(*) и COUNT({column})? COUNT (*) подсчитывает количество записей в таблице, не игнорируя значение NULL, поскольку эта функция оперирует записями, а не столбцами. COUNT ({column}) подсчитывает количество значений в {column}. При подсчете количества значений столбца эта форма функции COUNT не принимает во внимание значение NULL. 18. Что такое ограничения (constraints)? Какие вы знаете Когда вы создаете таблицу ( или, когда вы ее изменяете ), вы можете помещать ограничение на значения которые могут быть введены: CREATE TABLE < table name > (< column name > < column constraint >, < column name > < data type > < column constraint > ... < table constraint > ( < column name > [, < column name > ])... ); ● PRIMARY KEY - набор полей (1 или более), значения которых образуют уникальную комбинацию и используются для однозначной идентификации записи в таблице. Для таблицы может быть создано только одно такое ограничение. Данное ограничение используется для обеспечения целостности сущности, которая описана таблицей. Первичные ключи не могут позволить значений NULL ● CHECK позволяет установить свое условие, которому должно удовлетворять значение вводимое в таблицу, прежде чем оно будет принято. ● UNIQUE обеспечивает отсутствие дубликатов в столбце или наборе столбцов. ● FOREIGN KEY защищает от действий, которые могут нарушить связи между таблицами. FOREIGN KEY в одной таблице указывает на PRIMARY KEY в другой. Поэтому данное ограничение нацелено на то, чтобы не было записей FOREIGN KEY, которым не отвечают записи PRIMARY KEY. [CONSTRAINT имя_ограничения] FOREIGN KEY (столбец1, столбец2, ... столбецN) REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, ... столбец_главной_таблицыN) [ON DELETE действие] [ON UPDATE действие] - NOT NULL Какие отличия между ограничениями PRIMARY и UNIQUE? По умолчанию ограничение PRIMARY создает кластерный индекс на столбце, а UNIQUE - некластерный. Другим отличием является то, что PRIMARY не разрешает NULL записей, в то время как UNIQUE разрешает одну (а в некоторых СУБД несколько) NULL запись. Кластерный индекс — это древовидная структура данных, при которой значения индекса хранятся вместе с данными, им соответствующими. И индексы, и данные при такой организации упорядочены. При добавлении новой строки в таблицу, она дописывается не в конец файла*, не в конец плоского списка, а в нужную ветку древовидной структуры, соответствующую ей по сортировке. Может ли значение в столбце, на который наложено ограничение FOREIGN KEY, равняться NULL? Может, если на данный столбец не наложено ограничение NOT NULL. 19. Что такое суррогатные ключи? Суррога́тный ключ — понятие теории реляционных баз данных. Это дополнительное служебное поле, добавленное к уже имеющимся информационным полям таблицы, единственное предназначение которого — служить первичным ключом. Дайте определение терминам «простой», «составной» (composite), «потенциальный» (candidate) и «альтернативный» (alternate) ключ. Простой ключ состоит из одного атрибута (поля). Составной - из двух и более. Потенциальный ключ - простой или составной ключ, который уникально идентифицирует каждую запись набора данных. При этом потенциальный ключ должен обладать критерием неизбыточности: при удалении любого из полей набор полей перестает уникально идентифицировать запись. Термин «candidate» подразумевает, что все такие ключи конкурируют за почётную роль «первичного ключа» (primary key), а оставшиеся назначаются «альтернативными ключами» (alternate keys). Из множества всех потенциальных ключей набора данных выбирают первичный ключ, все остальные ключи называют альтернативными. Первичный ключ (primary key) в реляционной модели данных один из потенциальных ключей отношения, выбранный в качестве основного ключа (ключа по умолчанию). Если в отношении имеется единственный потенциальный ключ, он является и первичным ключом. Если потенциальных ключей несколько, один из них выбирается в качестве первичного, а другие называют «альтернативными». В качестве первичного обычно выбирается тот из потенциальных ключей, который наиболее удобен. Поэтому в качестве первичного ключа, как правило, выбирают тот, который имеет наименьший размер (физического хранения) и/или включает наименьшее количество атрибутов. Другой критерий выбора первичного ключа — сохранение его уникальности со временем. Поэтому в качестве первичного ключа стараются выбирать такой потенциальный ключ, который с наибольшей вероятностью никогда не утратит уникальность. Что такое «внешний ключ» (foreign key)? |