Как тестировщик программных продуктов, вы также должны выполнять тестирование баз данных (БД), которое требует знания принципов работы SQL. Кроме того, от вас требуется писать SQL-запросы для получения, обновления и вставки данных в БД. Поэтому изучить SQL важно не только для администраторов баз данных и разработчиков, но и для профессионалов в области тестирования.
Эта статья написана специально для начинающих, не имеющих предварительных знаний по SQL. Мы начнем с основ работы с базами данных. После этого перейдем к концепциям SQL. И, наконец, рассмотрим некоторые широко используемые команды SQL.
После прочтения этой статьи вы сможете проверить свои знания с помощью нашего обширного списка часто задаваемых вопросов на собеседовании по СУБД для тестировщика и 10 задач для подготовки к собеседованию по SQL-запросам.
Друзья, поддержите нас вступлением в наш телеграм канал QaRocks. Там много туториалов, задач по автоматизации и книг по QA.
Содержание
- Основы баз данных
- Основы SQL
- Команды SQL
- Оператор SELECT
- Основные операторы SQL
- Операторы JOIN
- Вложенные запросы
Основы баз данных
Базы данных и СУБД
База данных – это организованный и систематизированный набор данных, который хранится и получается из компьютерной системы. Организации используют базы данных в качестве метода хранения, управления и извлечения информации в реальном времени.
СУБД расшифровывается как “система управления базами данных”. Это программное обеспечение, предназначенное для поддержки и доступа к базе данных. Оно позволяет пользователю получать доступ к данным, создавать, удалять и обновлять данные в БД.
СУБД определяет правила для манипулирования и проверки данных. Мы используем БД, когда имеется большой объем данных, важна безопасность данных или когда нескольким пользователям необходим одновременный доступ к данным.
РСУБД – это расширенная версия базовой СУБД. Эта система управления базами данных использует реляционную модель для своих баз данных, то есть позволяет создавать реляционные базы данных.
Реляционная база данных – это БД, в которой пользователь может хранить связанные данные в нескольких таблицах. Таблицы связываются друг с другом с помощью установления связей между этими таблицами, что позволяет эффективно получать доступ к данным.
Таблицы
В РСУБД для хранения данных используются таблицы. Таблица представляет собой совокупность связанных данных, представленных в виде строк и столбцов.
Строка представляет собой набор полей, которые в итоге образуют запись в базе данных. Строки также называют записями.
В БД столбец представляет значения одного и того же типа. Столбец также называют атрибутом.
Основы SQL
Что такое SQL?
SQL (Structured Query Language) расшифровывается как “язык структурированных запросов”. Это язык программирования, который используется для запроса информации из базы данных. SQL можно использовать для управления и обмена данными в реляционной системе управления базами данных. Кроме того, пользователи могут выполнять такие действия, как вставка, удаление, выборка и т.п. в БД.
Программирование на SQL широко распространено, поскольку с помощью этого языка мы можем создавать, хранить и управлять данными в базе данных. Более того, SQL может быть встроен в другие языки с помощью библиотек и модулей SQL.
Типы данных SQL
Рассмотрим основные типы данных в SQL:
| Тип данных | Описание |
| VARCHAR(size) | Символьные данные различной длины. Длина ограничивается заданным размером |
| CHAR(size) | Символьные данные фиксированной длины |
| BINARY(size) | Двоичные данные фиксированной длины |
| NUMBER(a,b) | Числовые данные переменной длины, где a – количество цифр до запятой, b – количество цифр после запятой |
| DATE | Значения даты и времени |
| LONG | Символьные данные переменной длины до 2 гигабайт |
| CLOB | Символьные данные объемом до 4 гигабайт |
| RAW | Необработанные двоичные данные |
| BLOB | Двоичные данные объемом до 4 гигабайт |
| BFILE | Используется для сохранения двоичных данных, которые хранятся во внешнем файле объемом до 4 гигабайт |
| ROWID | Создает псевдостолбец, который, в свою очередь, образует уникальный идентификатор адреса конкретной строки в таблице в формате base-64 |
Операторы SQL
Арифметические операторы
Примечание. Операнды в SQL – это значения или выражения, которые используются в операциях или функциях. Они могут представлять собой числа, переменные, строки или другие типы данных. Операнды служат входными данными для выполнения операций и вычислений.
| Оператор | Описание |
|---|---|
| + | Сложение значений операндов |
| – | Вычитание значений операндов |
| * | Умножение значений операндов |
| / | Деление значений операндов |
| % | Возвращает целочисленный остаток при делении. Например, 9 % 5 = 2, поскольку остаток от деления 9 на 5 равен 2 |
Операторы сравнения
В таблице ниже, если условие выполняется, то возвращается значение “True” типа Boolean.
| Оператор | Описание |
|---|---|
| = | Проверяет равенство значений операндов |
| != | Проверяет неравенство значений операндов |
| > | Проверяет, больше ли левый операнд, чем правый |
| < | Проверяет, больше ли правый операнд, чем левый |
| >= | Проверяет, больше или равен левый операнд правому операнду |
| <= | Проверяет, больше или равен правый операнд левому операнду |
| !> | Проверяет, не больше ли правый операнд, чем левый |
| !< | Проверяет, не меньше ли левый операнд, чем правый |
Логические операторы
| Оператор | Описание |
|---|---|
| OR | Возвращает true, если хотя бы один из операндов истинен. Если оба операнда ложны, возвращается false |
| AND | Возвращает true, если оба операнда истинны. Если один или оба операнда ложны, возвращает false |
| NOT | Возвращает true, если условие ложно, и false, если условие истинно |
Команды SQL
Создать базу данных
Запрос CREATE DATABASE используется для создания новой базы данных SQL. Ниже приведен синтаксис:
CREATE DATABASE database_name;
Удалить базу данных
Запрос DROP DATABASE используется для удаления существующей базы данных SQL и всех данных, содержащихся в ней. Синтаксис:
DROP DATABASE database_name;
Переименовать базу данных
Запрос RENAME DATABASE используется для переименования существующей базы данных. Синтаксис:
RENAME DATABASE old_database_name TO new_database_name;
Выбор базы данных
Запрос SELECT DATABASE используется для выбора полей в существующей базе данных. Символ * используется для выбора всех полей в БД. Синтаксис:
SELECT * FROM database_name;
Создать таблицу
Запрос CREATE TABLE используется для создания новой таблицы. Ниже приведен синтаксис SQL-запроса на создание таблицы:
CREATE TABLE <table_name> (<Field><Datatype><(Width)><constraint>,..);
Удалить таблицу
Запрос DROP TABLE используется для удаления существующей таблицы. Синтаксис:
DROP TABLE <table_name>;
Переименовать таблицу
Запрос RENAME используется для изменения имени существующей таблицы. Ниже приведен синтаксис:
RENAME old_table_name TO new_table_name;
TRUNCATE TABLE
Запрос TRUNCATE TABLE удаляет все данные из таблицы, но не удаляет саму таблицу. Будьте осторожны при использовании этого запроса, так как он приводит к потере всех данных, хранящихся в таблице. Синтаксис:
TRUNCATE TABLE <table_name>;
SELECT * INTO
Этот запрос используется для копирования таблицы в другую таблицу в той же базе данных. Синтаксис:
SELECT * INTO <required_table> FROM <source_table>;
ALTER TABLE
Команда ALTER может использоваться для выполнения различных манипуляций с базой данных. Ниже представлены примеры запросов с использованием команды ALTER.
Добавление столбца в таблицу
Синтаксис:
ALTER TABLE <table_name> ADD <field_name> <Type> <(width)>;
Пример:
ALTER TABLE department ADD dep_name varchar (20);
Изменение атрибутов данных в поле таблицы
Синтаксис:
ALTER TABLE <table_name> MODIFY (<column_name> <new_data_type>);
Пример:
ALTER TABLE department MODIFY dep_name varchar (15);
Удаление поля таблицы
Синтаксис:
ALTER TABLE <table_name> DROP COLUMN <column_name>;
Пример:
ALTER TABLE department DROP column address;
Переименование столбца
Синтаксис:
ALTER TABLE <table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;
Пример:
ALTER TABLE department RENAME COLUMN dep_id to department_id;
Установка ограничений в SQL
| Ограничение | Описание |
|---|---|
| NOT NULL | Указывает, что столбец не может быть пустым |
| UNIQUE | Указывает, что каждая строка в столбце должна иметь уникальные значения |
| PRIMARY KEY | PRIMARY KEY (первичный ключ) – это особое поле в таблице, которое определяет столбец или набор столбцов, однозначно идентифицирующих строку. Также гарантирует, что в столбце или комбинации столбцов не будет дублирующихся или нулевых (NULL) значений |
| FOREIGN KEY | Это столбец (столбцы), который ссылается на столбец (столбцы) другой таблицы |
| CHECK | Задает условие, которому должны удовлетворять все записи в столбце таблицы |
Оператор INSERT
Команда INSERT в SQL используется для вставки данных в базу данных. Давайте рассмотрим это на примерах.
Вставка в таблицу записей, заданных пользователем
Синтаксис:
INSERT INTO <table_name> VALUES ( val1,val2 …);
Пример:
INSERT INTO department VALUES(20, 'HR', 'DELHI');
Вставка значений в определенный столбец таблицы
Синтаксис:
INSERT INTO <Table_Name> (column_name1, column_name2, column_name3,..) VALUES (val1, val2, val3,…..);
Пример:
INSERT INTO department (dept_id , dep_name) VALUES(20, 'finance');
Оператор SELECT
SELECT *
Оператор SELECT в SQL используется для выбора определенных данных из таблиц БД и отображения их пользователю. Ниже приведен синтаксис для получения всех полей таблицы:
SELECT * FROM table_name;
SELECT DISTINCT
Этот SQL-запрос используется для выбора всех уникальных значений, хранящихся в указанной таблице базы данных. Синтаксис:
SELECT DISTINCT column_name FROM table_name;
SELECT COUNT
SELECT COUNT показывает количество строк в указанной таблице, удовлетворяющих условию, заданному пользователем. Условие для запроса может быть задано оператором WHERE. Если нет строк, удовлетворяющих заданному условию, возвращается 0.
Синтаксис:
SELECT COUNT (column_name) FROM table_name WHERE condition;
Пример:
SELECT COUNT (dep_sal) FROM department WHERE dep_sal>10000;
SELECT TOP
Запрос с SELECT TOP используется для выбора определенного количества записей из таблицы. Оператор TOP выводит верхние записи, соответствующие заданному условию. Например, если мы ограничим выбор четырьмя записями, а записей, подходящих условию, десять, то будут выведены первые четыре записи.
Синтаксис:
SELECT TOP number_of_records FROM table_name WHERE condition;
Пример:
SELECT TOP 4 FROM department WHERE dep_sal>10000;
SELECT AS
Команда AS используется для изменения имени столбца или таблицы на псевдоним. Заданный псевдоним существует только на время выполнения запроса.
Синтаксис:
SELECT column_name AS new_column_name FROM table_name;
Пример:
SELECT dep_id AS department_id FROM department;
SELECT IN
Команда IN применяется для указания нескольких значений в части запроса, использующей команду WHERE.
Синтаксис:
SELECT column_name FROM table_name WHERE column_name IN (val1, val2, …);
Пример:
SELECT dep_name FROM department WHERE dep_id IN (1020, 1044);
SELECT DATE
Запрос с командой GETDATE () используется для возврата текущей даты и времени в базе данных, формат которых следующий: YYYY-MM-DD hh:mm:ss.mmm.
SELECT GETDATE();
SELECT SUM
Команда SUM используется для возврата общей суммы значений указанного числового столбца.
Синтаксис:
SELECT SUM column_name FROM table_name WHERE condition;
Пример:
SELECT SUM dep_sal FROM department WHERE dep_sal>10000;
SELECT NULL
Запрос SELECT NULL используется для выбора всех записей с нулевыми значениями.
Синтаксис:
SELECT column_name FROM table_name WHERE condition IS NULL;
Пример:
SELECT dep_sal FROM department WHERE dep_sal>10000 IS NULL;
Основные операторы SQL
WHERE
Оператор WHERE в SQL используется для установки условий выборки данных. Этот оператор позволяет извлекать только те записи, которые соответствуют определенному условию.
Синтаксис:
SELECT column_name FROM table_name WHERE condition;
Пример:
SELECT dep_sal FROM department WHERE dep_sal>10000;
AND
Оператор AND в SQL используется для выбора записей на основе нескольких условий. Он возвращает значения, если оба указанных условия истинны.
Синтаксис:
SELECT column_name FROM table_name WHERE condition1 AND condition2 …;
Пример:
SELECT dep_sal FROM department WHERE dep_sal>10000 AND dep_name=HR;
OR
Этот оператор используется для выбора записей по более чем одному условию. OR возвращает значения, если хотя бы одно из условий истинно.
SELECT column_name FROM table_name WHERE condition1 OR condition2 …;
Пример:
SELECT dep_sal FROM department WHERE dep_sal>10000 OR dep_name=HR;
GROUP BY
Запрос GROUP BY используется в SQL для группировки строк в соответствии с указанным условием или указанным именем столбца. GROUP BY обычно комбинируется с такими функциями, как MAX, IN, AVG, SUM, COUNT.
Синтаксис:
SELECT column_name FROM table_name WHERE condition GROUP BY column_name;
Пример:
SELECT COUNT(dep_sal) FROM department GROUP BY dep_name;
HAVING
Оператор HAVING в SQL используется, когда мы хотим указать условие для выбора записей с использованием таких функций, как MAX, IN, AVG, SUM, COUNT, так как с этими функциями нельзя использовать оператор WHERE. Оператор HAVING всегда следует после оператора GROUP BY и перед оператором ORDER BY.
Синтаксис:
SELECT column_name FROM table_name GROUP BY column_name HAVING condition;
Пример:
SELECT COUNT (dep_id) FROM department GROUP BY dep_name HAVING dep_sal>10000;
ORDER BY
Оператор ORDER BY используется для сортировки выбранных данных по возрастанию или убыванию. ORDER BY позволяет упорядочить результаты запроса по определенным столбцам в базе данных. Мы можем указать, что нужно отсортировать данные по возрастанию (ASC – от англ. ascend) или по убыванию (DESC – от англ. descend).
Синтаксис:
SELECT column_name FROM table_name ORDER BY column_name ASC/DESC;
Пример:
SELECT dep_name FROM department ORDER BY dep_id ASC;
UPDATE
Оператор UPDATE используется в SQL для изменения или модификации любых существующих полей/записей в таблице.
Синтаксис:
UPDATE table_name SET column_name1 = val1, column_name2 = val2… WHERE condition;
Пример:
UPDATE department SET dep_name="travel" WHERE dep_id=1020;
DELETE
Оператор DELETE используется для удаления из таблицы всех существующих полей/записей, соответствующих параметрам запроса.
Синтаксис:
DELETE FROM table_name WHERE condition;
Пример:
DELETE FROM department WHERE dep_name="Travel";
UNION
SQL-оператор UNION используется для объединения результатов двух или более SELECT-запросов в один набор данных. Другими словами, оператор UNION позволяет объединить данные из нескольких SELECT-запросов в один результат. Это полезно, когда мы хотим объединить данные из двух или более таблиц или представлений в один набор данных.
Синтаксис:
SELECT column_name FROM table_name1 UNION SELECT column_name FROM table_name2;
Пример:
SELECT dep_name FROM department UNION SELECT emp_name FROM employee;
INTERSECT
Оператор INTERSECT используется для возврата тех значений, которые являются общими для обоих наборов данных. Этот оператор будет работать только в том случае, если оба пересекаемых набора данных имеют схожие типы данных.
Синтаксис:
SELECT column_name FROM table_name1 INTERSECT SELECT column_name FROM table_name2;
Пример:
SELECT dep_name FROM department INTERSECT SELECT emp_dep FROM employee;
EXCEPT
Оператор EXCEPT используется в SQL между двумя запросами SELECT. Этот оператор выбирает те строки, которые присутствуют в первом SELECT-запросе, но отсутствуют во втором. Иначе говоря, EXCEPT позволяет нам найти различия между двумя наборами данных. Если у нас есть два запроса, SELECT1 и SELECT2, оператор EXCEPT вернет только те строки, которые присутствуют в SELECT1, но отсутствуют в SELECT2.
Синтаксис:
SELECT column_name FROM table_name EXCEPT SELECT column_name FROM table_name;
Пример:
SELECT dep_name FROM department EXCEPT SELECT emp_dep FROM employee;
Операторы JOIN
Простыми словами, команда SQL JOIN используется для объединения строк или целых таблиц на основе общего столбца между ними. Если у нас есть две таблицы, A и B, и у них есть общий столбец, мы можем объединить их, чтобы получить общий набор данных.
INNER JOIN
Команда INNER JOIN используется для объенидения данных из двух таблиц на основе общего столбца между ними. Если у нас есть два набора данных, A и B, оператор INNER JOIN вернет только те записи, которые имеют общие значения в общем столбце.
Синтаксис:
SELECT column_name FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name;
Пример:
SELECT dep_name FROM department INNER JOIN employee ON department.dep_name = employee.emp_dep;
LEFT JOIN
Оператор LEFT JOIN объединяет данные из двух таблиц на основе общего столбца, при этом включая все записи из левой таблицы и только те записи из правой таблицы, которые имеют совпадающие значения в общем столбце.
Синтаксис:
SELECT column_name FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name = table_name2.column_name;
Пример:
SELECT dep_name FROM department LEFT JOIN employee ON department.dep_name = employee.emp_dep;
RIGHT JOIN
Оператор RIGHT JOIN аналогичен LEFT JOIN, но для объединения используются записи из таблицы 2 (правой таблицы) и общих записей из обеих таблиц.
Синтаксис:
SELECT column_name FROM table_name2 RIGHT JOIN table_name1 ON table_name2.column_name = table_name1.column_name;
Пример:
SELECT emp_name FROM employee RIGHT JOIN department ON department.dep_name = employee.emp_dep;
FULL OUTER JOIN
Оператор FULL OUTER JOIN используется для объединения всех записей из обеих таблиц, когда между ними есть общие записи. Таким образом, оператор FULL OUTER JOIN позволяет объединить данные из двух таблиц, включая все записи из обеих таблиц, а также только те записи, которые имеют совпадающие значения в общем столбце.
Синтаксис:
SELECT column_name FROM table_name1 FULL OUTER JOIN table_name2 ON table_name1.column_name = table_name2.column_name;
Пример:
SELECT dep_name FROM department FULL OUTER JOIN employee ON department.dep_name = employee.emp_dep;
SELF JOIN
Оператор SELF JOIN используется для объединения таблицы с самой собой путем создания временной таблицы.
SELECT column_name FROM table1 table_alias1, table2 table_alias2 WHERE condition;
CROSS JOIN
CROSS JOIN используется для объединения двух таблиц. При этом каждая строка одной таблицы сцепляется с каждой строкой другой таблицы. Результат запроса с CROSS JOIN обычно называют декартовым произведением.
Синтаксис:
SELECT * FROM table_name1 CROSS JOIN table_name2;
Пример:
SELECT * FROM department CROSS JOIN employee;
Для закрепления материала по JOIN-запросам предлагаем вам пройти тест из 10 вопросов на собеседовании по SQL JOIN с ответами и примерами.
Вложенные запросы
Вложенный запрос, как следует из названия, представляет собой запрос внутри другого запроса. Такие запросы отделяются круглыми скобками и работают с такими операторами, как WHERE, HAVING, WITH, FROM и т. д.
Вложенные запросы выполняются первыми, и результат, полученный после их выполнения, используется для выполнения внешнего или основного запроса.
Синтаксис:
SELECT column_name FROM table_name WHERE condition OPERATOR (SELECT column_name FROM table_name WHERE condition);
Запрос, присутствующий внутри предложения WHERE, называется подзапросом.
Пример:
SELECT dep_name FROM department WHERE dep_name in (SELECT dep_name FROM department WHERE dep_sal>1000);
Заключение
Наше пособие по по SQL для тестировщиков подошло к концу. Надеемся, что собранные нами материалы помогут вам быстро освоить концепции SQL. Если у вас есть вопросы или предложения по этой теме, вы можете оставить свои комментарии в статье, а также ознакомиться с другими статьями по теме SQL на нашем сайте.
Перевод статьи «SQL For Testers».