SQL Flashcards
Что такое «SQL»?
SQL, Structured query language («язык структурированных запросов») — формальный непроцедурный язык программирования, применяемый для создания, модификации и управления данными в произвольной реляционной базе данных, управляемой соответствующей системой управления базами данных (СУБД).
Формальный - множество конечных слов (строк, цепочек) над конечным алфавитом. Различают языки естественные, на которых общаются люди, и искусственные (или формальные).
Непроцедурный - каждый оператор выдает результат, соответствующий запрашиваемому выходу. Ответственность за то, как добиться соответствующего выхода, лежит, в значительной степени, на моторе (движке) базы данных.
Что такое Реляционная Модель?
Реляционная модель была разработана в конце 1960-х годов Е.Ф.Коддом .
Реляционная база данных (RDBMS) - это тип базы данных, в которой данные хранятся в виде таблиц (реляций) с явными связями между ними. Каждая таблица состоит из строк (записей) и столбцов (полей), а связи между таблицами устанавливаются через общие поля.
Реляционная база данных это упорядоченная информация связанная между собой определенными отношениями
В реляционных базах данных информация хранится в виде связанных между собой таблиц.(one-to-many/many-to-many/one-to-one)
Основные принципы реляционных баз данных:
- все данные на концептуальном уровне представляются в виде объектов, заданных в виде строк и столбцов, называемых отношением, более распространенное название – таблица;
- в пересечение строки и столбца таблицы можно занести только одно значение;
- все операции выполняются над целыми отношениями и результатом этих операций является отношение.
База данных, в том числе и реляционная, используется для формального описания некоторой предметной области реального мира, например, склада, учебного процесса и пр. Обязательным этапом перед созданием базы данных является ее проектирование.
СУБД - “Надстройка” над БД, позволяет работать в БД, и предоставляет дополнительный функционал
Зачем в таблице primary key? Что это такое?
Первичный ключ (primary key) - один или несколько столбцов таблицы, которые уникальным образом определяют один ее элемент.
1) Чтобы соблюсти принцип Integrity: таблица не может содержать 2 полностью одинаковых ряда.
2) комбинация NOT NULL и UNIQUE constraints. Помечает каждую запись в базе данных уникальным значением.
Primary key (первичный ключ) - это столбец или группа столбцов в таблице базы данных, который уникально идентифицирует каждую запись в этой таблице. Он используется для обеспечения уникальности идентификации записей и для связи данных между таблицами.
Ключ это столбец или набор столбцов по которым гарантированно можно отличить строки друг от друга, т.е. ключ идентифицирует каждую строку таблицы. По ключу мы можем обратиться к конкретной строке данных в таблице.
country_name VARCHAR(40) NOT NULL UNIQUE, => ALTER TABLE countries ADD PRIMARY KEY (country_name); also: ADD CONSTRAINT pk_student PRIMARY KEY (name,birth_date);
Зачем в таблице foreign key?
Foreign key (внешний ключ) - это столбец или группа столбцов в таблице базы данных, который ссылается на первичный ключ другой таблицы. Внешний ключ используется для создания связей между таблицами в базе данных.
2) С его помощью создаётся many-to-one relation.
3) Поддерживает referential actions при UPDATE / DELETE:
CASCADE:
SET NULL
SET DEFAULT
RESTRICT
NO ACTION
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(60)
department_id INT,
[ CONSTRAINT fk_department ] FOREIGN KEY (department_id)
REFERENCES departments(id) );
ALTER TABLE employees
ADD FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE employees DROP FOREIGN KEY fk_department;
Что такое one-to-one?
One-to-one это отношение между двумя сущностями, где каждый экземпляр одной сущности связан только с одним экземпляром другой сущности, и наоборот. То есть на каждой стороне может быть только одна связь с другой стороной.
Например, каждый человек имеет только один паспорт, и каждый паспорт относится только к одному человеку.
Что такое many-to-many?
Many-to-many это отношение между двумя сущностями, где каждый экземпляр одной сущности связан с несколькими экземплярами другой сущности, и наоборот. То есть на каждой стороне может быть несколько связей с другой стороной.
Например, ученики могут посещать несколько курсов, и каждый курс может иметь несколько студентов.
Всегда моделируются через третью ( связующую ) таблицу
Что такое one-to-many?
One-to-many это отношение между двумя сущностями, где одна сущность связана с несколькими экземплярами другой сущности. То есть на стороне “один” может быть только один экземпляр, но на стороне “многие” может быть несколько связанных экземпляров.
Например, у компании может быть много сотрудников, но каждый сотрудник связан только с одной компанией.
Что такое identifying ralationship?
Identifying relationship - это отношение между двумя сущностями, где первичный ключ одной сущности используется в качестве внешнего ключа в другой сущности для установления связи между ними. Это означает, что связь между этими сущностями определяется по первичному ключу одной из них. Например, в связи между заказом и его деталями, идентифицирующим отношением будет использоваться первичный ключ заказа как внешний ключ для деталей заказа. Если заказ удаляется, все его детали также будут удалены, поскольку они зависят от него и не могут существовать без него.
Когда ФК становится ПК или частью составного ПК в таблице.
Почему SQL - декларативный язык?
SQL (Structured Query Language) является декларативным языком, потому что пользователь описывает, что нужно получить в результирующей выборке, а не как это сделать. Вместо того, чтобы программируя указывать каждый шаг выполнения запроса, пользователь SQL описывает данные, которые нужно выбрать, и СУБД сама выбирает наиболее эффективный способ выполнения запроса.
Таким образом, SQL скрывает от пользователя множество деталей и позволяет сконцентрироваться на описании необходимых данных. Это упрощает написание запросов и повышает эффективность работы с данными.
Какие базовые типы данных есть в SQL?
- INTEGER
- FLOAT
- DECIMAL(20, 2): 20 - precision / 2 - scale
- VARCHAR(10): 10- max length.
- BOOLEAN
- BIGINT
- NUMERIC
Что такое DDL? Какие операции в него входят? Рассказать про них.
DDL (Data Definition Language) - это подмножество языка SQL, которое используется для определения структуры базы данных и ее объектов, таких как таблицы, индексы, представления, хранимые процедуры и другие.
Операторы определения данных:
* CREATE создает объект БД (базу, таблицу, представление, пользователя и т. д.);
* ALTER изменяет объект;
- - ADD / MODIFY / DROP COLUMN | rename: CHANGE old new;
* DROP удаляет объект;
* TRUNCATE удаляет таблицу и создает ее пустую заново, но если в таблице были foreigh key, то создать таблицу не получится,
rollback после TRUNCATE невозможен.
NB: DDL команды транзакционны - разработчик не контролирует транзакцию, она открывается и закрывается сама.
Проконтролировать создание базы данных можно с помощью оператора SHOW DATABASES, SHOW TABLES кроме пользовательских таблиц отображает также и служебные таблицы.
DESCRIBE table_name – показывает таблицу
DELETE FROM <table_name> - Удаление всех данных из таблицы</table_name>
Проверка на уже существующие базы данных:
CREATE DATABASE IF NOT EXIST имя_базы_данных;
Перед созданием таблицы необходимо выбрать базу данных, в которую таблица будет записана. Это делается с помощью оператора USE: USE имя_базы_данных.
Для того, чтобы посмотреть описание созданной таблицы можно воспользоваться оператором DESCRIBE: DESCRIBE Users;
Что такое ограничения (constraints)? Какие вы знаете?
Ограничения ( CONSTRAINT ) – это ключевые слова, которые помогают установить правила размещения данных в базе. Ограничения используются для обеспечения целостности данных и предотвращения некорректных операций с ними.
~~~
CONSTRAINT pk_employee PRIMARY KEY (department_id, employee_id)
CONSTRAINT uq_id_last_name UNIQUE (personal_id, last_name)
ALTER TABLE employees DROP INDEX uq_id_last_name;
~~~
NOT NULL указывает, что значение не может быть пустым.
UNIQUE - определяет, что значения в поле должны быть уникальными.
PRIMARY KEY - определяет одно или несколько полей в таблице как уникальные идентификаторы строк.
PRIMARY KEY набор полей (1 или более), значения которых образуют уникальную комбинацию и используются для однозначной идентификации записи в таблице. Для таблицы может быть создано только одно такое ограничение. Данное ограничение используется для обеспечения целостности сущности, которая описана таблицей. Первичные ключи не могут позволить значений NULL
Ключевые особенности PRIMARY KEY в SQL:
Каждая таблица может иметь только один PRIMARY KEY.
Поля, определенные как PRIMARY KEY, не могут содержать NULL значения.
Значения в полях, определенных как PRIMARY KEY, должны быть уникальными.
Поля, определенные как PRIMARY KEY, обычно связаны с другими таблицами с помощью FOREIGN KEY.
Пример:
~~~
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL
);
~~~
FOREIGN KEY защищает от действий, которые могут нарушить связи между таблицами.
FOREIGN KEY в одной таблице указывает на PRIMARY KEY в другой. Поэтому данное ограничение нацелено на то, чтобы не было записей FOREIGN KEY, которым не отвечают записи PRIMARY KEY.
Пример:
~~~
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT NOT NULL,
order_date DATE NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
~~~
CHECK позволяет установить свое условие, которому должно удовлетворять значение вводимое в таблицу, прежде чем оно будет принято.
CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT NOT NULL, order_date DATE NOT NULL, total_amount DECIMAL(10,2) NOT NULL, CHECK (total_amount >= 0) );
DEFAULT устанавливает значение по умолчанию, если значения не предоставлено (name VARCHAR(20) DEFAULT ‘noname’).
Вложенный запрос (подзапрос, внутренний запрос).
Вложенный запрос (подзапрос, внутренний запрос) – это запрос внутри другого запроса SQL.
Вложенный запрос используется для выборки данных, которые будут использоваться в условии отбора записей основного запроса. Его применяют для:
* сравнения выражения с результатом вложенного запроса;
* определения того, включено ли выражение в результаты вложенного запроса;
* проверки того, выбирает ли запрос определенные строки.
Что такое DML? Какие операции в него входят? Рассказать про них.
Операторы манипуляции данными (Data Manipulation Language, DML):
SELECT выбирает данные, удовлетворяющие заданным условиям,
~~~
SELECT [DISTINCT | ALL] поля_таблиц
FROM список_таблиц
[WHERE условия_на_ограничения_строк]
[GROUP BY условия_группировки]
[HAVING условия_на_ограничения_строк_после_группировки по агрегатным функциям]
[ORDER BY порядок_сортировки [ASC | DESC]]
[LIMIT ограничение_количества_записей]
~~~
INSERT добавляет новые данные
Общая структура запроса с оператором INSERT INSERT INTO имя_таблицы [(поле_таблицы, ...)] VALUES (значение_поля_таблицы, ...) | SELECT поле_таблицы, ... FROM имя_таблицы ...
UPDATE изменяет существующие данные
UPDATE FamilyMembers
SET member_name = "Andie Anthony" что делаем WHERE member_name = "Andie Quincey" куда делаем UPDATE Payments SET unit_price = unit_price * 2
DELETE удаляет данные;
~~~
DELETE Reservations, Rooms FROM Reservations
JOIN Rooms ON Reservations.room_id = Rooms.id
WHERE Rooms.has_kitchen = false;
~~~
TRUNCATE TABLE имя_таблицы;
Что такое TCL? Какие операции в него входят? Рассказать про них.
Операторы управления транзакциями (Transaction Control Language, TCL):
COMMIT подтверждает все изменения, внесенные в текущую транзакцию, и закрепляет их в базе данных.
~~~
DELETE FROM developers
WHERE SPECIALTY = ‘C++’;
COMMIT;
~~~
ROLLBACK откатывает все изменения, внесенные в текущую транзакцию, и возвращает базу данных к состоянию, которое было до начала транзакции.
SAVEPOINT создает точку сохранения внутри текущей транзакции, которую можно использовать для отката части изменений.
некая засечка в важных местах транзакции
SET TRANSACTION устанавливает свойства текущей транзакции, такие как уровень изоляции и режим блокировки.
BEGIN TRANSACTION - начинает новую транзакцию.
COMMIT TRANSACTION - подтверждает изменения и закрывает текущую транзакцию.
ROLLBACK TRANSACTION - откатывает все изменения и закрывает текущую транзакцию.
Команды управление транзакциями используются только для DML команд: INSERT, UPDATE, DELETE.
TRUNCATE не используется когда в таблице есть внешние ключи надо использовать делит
Что такое Транзакция?
Транзакция - это единица работы с базой данных, которая позволяет выполнять несколько операций базы данных как одну логическую единицу, гарантируя при этом целостность данных и контроль одновременного доступа к ним. Если все операции выполняются успешно, то изменения сохраняются, если же происходит ошибка, то все изменения откатываются и база данных возвращается в исходное состояние.
Транзакция - набор команд, которые выполняются поочередно
Если все выполнены - транзакция считается успешной, если нет, то транзакция откатывается назад.
Что такое 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;
the order of evaluation of the statements in a quiery
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
Нюансы работы с NULL в SQL. Как проверить поле на NULL?
NULL - специальное значение (псевдозначение), которое может быть записано в поле таблицы базы данных. NULL соответствует понятию «пустое поле», то есть «поле, не содержащее никакого значения».
Поэтому NULL не равно ни логическому значению FALSE, ни пустой строке, ни 0.
NULL означает отсутствие, неизвестность информации. Значение NULL не является значением в полном смысле слова: по определению оно означает отсутствие значения и не принадлежит ни одному типу данных. Поэтому NULL не равно ни логическому значению FALSE, ни пустой строке, ни 0. При сравнении NULL с любым значением будет получен результат NULL, а не FALSE и не 0. Более того, NULL не равно NULL!
1) При любой операции с NULL получится NULL (a + null = null)
2) При сравнении NULL с любым значением будет получен NULL
3) в агрегатных функциях значения null игнорируются
4)В логических операциях (OR) может вернуться true / false :
(NULL = 1) OR TRUE equals to TRUE
5) можно использовать COALISE:
SELECT id, name, COALESCE(age, 0) as age, grade FROM students;
6) можно задать constraint NOT NULL
Проверить на null: IS NULL, IS NOT NULL, сравнение с помощью “=” вернёт NULL.
Что такое JOIN? Виды Join’ов?
JOIN предназначен для обеспечения выборки данных из двух и более таблиц на основе общей колонки и включения этих данных в результирующую таблицу. Prymary key из одной таблицы должен ссылаться на Foreigh key другой таблицы.
Первую таблицу называют Source , Вторую таблицу Target
NATURAL JOIN - это тип JOIN в SQL, который автоматически определяет, какие столбцы в двух таблицах соответствуют друг другу и выполняет JOIN на основе этих соответствий. Он использует все столбцы, имеющие одинаковые имена и типы данных в обеих таблицах.
- (INNER) JOIN* Результатом объединения таблиц являются записи, общие для левой и правой таблиц. Порядок таблиц для оператора не важен, поскольку оператор является симметричным.
~~~
SELECT table1.col_name1 [AS name1], tableN.col_name2 [AS nameN]
FROM table1 [AS a]
[type_of_join] JOIN table2 [AS b]
ON table1.id = table2.id;
[type_of_join] JOIN table3
ON table2.col_name_table2 = table3.col_name_table3;
LIMIT 10, 5;
~~~ - LEFT (OUTER) JOIN. Производит выбор всех записей первой таблицы и соответствующих им записей второй таблицы. Если записи во второй таблице не найдены, то вместо них подставляется пустой результат (NULL). Порядок таблиц для оператора важен, поскольку оператор не является симметричным.
- RIGHT (OUTER) JOIN с операндами, расставленными в обратном порядке. Порядок таблиц для оператора важен, поскольку оператор не является симметричным.
- FULL (OUTER) JOIN. Результатом объединения таблиц являются все записи, которые присутствуют в таблицах. Порядок таблиц для оператора не важен, поскольку
оператор является симметричным. - CROSS JOIN (декартово произведение) При выборе каждая строка одной таблицы объединяется с каждой строкой второй таблицы, давая тем самым все возможные сочетания строк двух таблиц. Порядок таблиц для оператора не важен, поскольку оператор является симметричным.
NB: вместо JOIN можно применять WHERE:
SELECT table1.col_name1, table2.col_name2
FROM table1 [AS a], table2 [AS b]
WHERE table1.id = table2.id;
* SELF JOIN - это операция объединения таблицы самой с собой в SQL.
JOIN - это оператор в SQL, который используется для объединения данных из двух или более таблиц на основе соответствующих значений в одном или нескольких столбцах. JOIN позволяет объединять данные из разных таблиц в один результат запроса.
В SQL существует несколько видов JOIN:
INNER JOIN - возвращает только те строки, которые имеют соответствующие значения в обеих таблицах.
LEFT JOIN (или LEFT OUTER JOIN) - возвращает все строки из левой таблицы и только те строки из правой таблицы, которые имеют соответствующие значения. Если значения в правой таблице отсутствуют, то возвращается NULL.
RIGHT JOIN (или RIGHT OUTER JOIN) - возвращает все строки из правой таблицы и только те строки из левой таблицы, которые имеют соответствующие значения. Если значения в левой таблице отсутствуют, то возвращается NULL.
FULL JOIN (или FULL OUTER JOIN) - возвращает все строки из обеих таблиц, включая те строки, которые не имеют соответствующих значений в другой таблице. Если значения отсутствуют в одной из таблиц, то возвращается NULL.
CROSS JOIN (или CARTESIAN JOIN) - возвращает комбинацию всех строк из каждой таблицы. Этот тип JOIN не требует условия соответствия.
Выбор определенного типа JOIN зависит от того, какие данные вы хотите получить в результате запроса и какие соответствия существуют между таблицами.
Отличия JOIN ON и JOIN USING
JOIN ON и JOIN USING - это разновидности оператора объединения таблиц в SQL запросах.
JOIN ON используется для объединения таблиц по условию, которое указывается после ключевого слова ON.
JOIN USING также используется для объединения таблиц, но вместо условия указывается название столбца, который имеет одинаковые значения в обеих таблицах. Это позволяет более коротко и ясно записать запрос.
Примеры:
JOIN ON: SELECT * FROM table1 JOIN table2 ON table1.id = table2.id
JOIN USING: SELECT * FROM table1 JOIN table2 USING (id)
Оба оператора JOIN ON и JOIN USING выполняют объединение таблиц в SQL запросах, но каждый из них может быть более удобным в определенных ситуациях.
Используйте JOIN ON, когда:
- Вам нужно объединить таблицы по сложному условию, которое не может быть выражено одним столбцом, например, по нескольким столбцам или с использованием функций
- Вам нужно управлять порядком выполнения запроса и выбором записей для объединения, когда условие объединения не является простым
Используйте JOIN USING, когда:
- Вам нужно объединить таблицы по столбцу, имеющему одинаковые имена и типы данных в обеих таблицах
- Вам нужно написать более читаемый и краткий код запроса, так как оператор JOIN USING позволяет избежать повторения одного и того же столбца в условии объединения
Что лучше использовать join или подзапросы? Почему?
Обычно лучше использовать JOIN, поскольку в большинстве случаев он более понятен и лучше оптимизируется СУБД (но 100% этого гарантировать нельзя). Так же JOIN имеет заметное преимущество над подзапросами в случае, когда список выбора SELECT содержит столбцы более чем из одной таблицы.
Подзапросы лучше использовать в случаях, когда нужно вычислять агрегатные значения и использовать их для сравнений во внешних запросах.
Что делает UNION?
UNION применяется для объединения результатов нескольких SQL-запросов в единую таблицу, состоящую из схожих записей. Оба запроса должны возвращать одинаковое число столбцов и совместимые типы данных в соответствующих столбцах.
UNION сам по себе не гарантирует порядок записей. В случаях, когда требуется определенный порядок, необходимо использовать ORDER BY.
Разница между UNION и UNION ALL заключается в том, что UNION будет пропускать дубликаты записей, тогда как UNION ALL будет включать дубликаты записей.
SELECT name FROM teachers
UNION [ALL] / INTERSECT / MINUS (EXCEPT)
SELECT name FROM administrative_staff
Для того, чтобы UNION корректно сработал нужно: чтобы результирующие таблицы каждого из SQL запросов имели одинаковое число столбцов, с одним и тем же типом данных и в той же самой последовательности.
