Что такое SQL: синтаксис, запросы, отличие от MySQL
SQL (расшифровка Structured Query Language) — это язык структурированных запросов, который используется для работы с базами данных. Он позволяет создавать, изменять и управлять данными. SQL используется для выполнения различных операций: выборка данных, вставка новых записей, обновление существующих записей и удаление данных.
Виды запросов SQL (ниже подробно разберем их на примерах и командах):
- SELECT: Используется для выборки данных из таблицы или нескольких таблиц. Этот запрос позволяет получить информацию из базы данных на основе определенных условий.
- INSERT: Используется для добавления новых записей в таблицу базы данных.
- UPDATE: Используется для обновления существующих записей в таблице.
- DELETE: Используется для удаления записей из таблицы базы данных.
- CREATE: Используется для создания новой таблицы, представления или базы данных.
- DROP: Используется для удаления таблицы, представления или базы данных.
- JOIN: Используется для объединения данных из нескольких таблиц на основе определенных условий.
- WHERE: Используется для установки условий отбора при выполнении запросов.
- GROUP BY: Используется для группировки данных по определенному столбцу.
- ORDER BY: Используется для сортировки результатов по определенному столбцу.
- HAVING: Используется для установки условий для группированных данных.
Чем отличается SQL от MySQL?
MySQL — это одна из популярных реляционных баз данных, которая поддерживает SQL для работы с данными. Таким образом, можно сказать, что MySQL — это реализация базы данных, которая использует SQL в качестве языка запросов для взаимодействия с базой данных.
То есть, SQL является языком запросов, а MySQL — это конкретная система управления базами данных, которая поддерживает этот язык. MySQL предлагает множество дополнительных функций, производительность и инструменты для управления базами данных, что делает ее популярным выбором для множества веб-приложений и других систем, требующих хранения данных.
Запросы в SQL
С данным можно сделать 4 действия (и их комбинация приводит к новым операциям):
- CREATE — создание
- READ — чтение
- UPDATE — обновление
- DELETE — удаление
Как создать новую таблицу?
Для этого есть команда: CREATE TABLE therenametable ();
therenametable — это название таблицы и может быть любым
Типы данных в SQL
Типы данных в SQL — числа, строки и дата
У каждого типа есть много разных разновидностей. Это необходимо, например, чтобы определить объем памяти, который будет элемент занимать. Чаще всего для числа используется тип TUNYINT (size от -128 до 127). BIT (site от 1-64). BOOL (BOOLEAN) — либо 0, либо 1. INTEGER (INT). FLOAT (p) — плавающая точка.
Команды SQL
INSERT INTO — положить в таблицу (в ячейку) значение. Например, команда INSERT INTO workers (name, age) VALUES (‘Boris’, 20);
Также мы можем прочитать те данные, которые положили, с помощью команды SELECT. Например, все атрибуты: SELECT * FROM workers; Вместо звездочки мы можем указать конкретные элементы (например, name).
ALTER TABLE .. — положить в таблицу. Например, добавим новую колонку в нашу таблицу:
Команда ALTER TABLE workers ADD COLUMN same_attr tinytext;
Еще пример ALTER TABLE workers ADD COLUMN position tinytext; (создается колонка position)
А если захотим удалить этот столбец, то команда ALTER TABLE workers DROP same_attr;
Команда для переименовывания столбца:
ALTER TABLE workers RENAME COLUMN some_data TO info;
Еще команда, чтобы присвоить элементам NULL:
ALTER TABLE workers MODIFY COLUMN name tinytext NULL;
или откатить обратно ALTER TABLE workers MODIFY COLUMN name tinytext NOT NULL; (но при этом в колонке не должно быть NULL элементов). Через MODIFY мы также можем менять тип данных или уникальность (UNIQUE);
Добавить новый элемент в определенную колонку (остальное NULL)
INSERT INFO workers (start_work_at) VALUES (‘2022-10-12’);
Чтобы удалить строку, используем команду: DELETE FROM workers WHERE name IS NULL;
Также можем удалить конкретный элемент (пользователя). DELETE FROM workers WHERE name = ‘BORIS’;
Если вдруг необходимо обновить у элемента данные, используем команду UPDATE:
UPDATE workers SET name = ‘BORIS’ WHERE name = ‘IVAN’; — мы находим элемент с именем «IVAN» и меняем его на «BORIS»;
Также у таблиц должен быть элемент ID — это уникальный номер строки. Задается он (если его нет) следующим образом id int PRIMARY KEY AUTO_INCREMENT. Где последнее означает, что каждому новому объекту будет новый номер. А если добавляется новый элемент после того, как удалили старый, то его ID будет всё равно уникальным.
Как связать 2 таблицы в SQL по столбцу ID?
Ниже пример команд по созданию 2 таблиц. Сначала создаем ту, на которую будем ссылаться из последней. Связывающий элемент ID из первой таблицы. Для связки по ID используем команду во второй таблице FOREIGN KEY (position_id) REFERENCES positions(id).
Create table IF NOT EXISTS positions (
id int PRIMARY KEY AUTO_INCREMENT,
title tinytext
);
Create table IF NOT EXISTS workers (
id int PRIMARY KEY AUTO_INCREMENT,
name tinytext NOT NULL,
age int,
is_married bool DEFAULT false,
position_id int,
FOREIGN KEY (position_id) REFERENCES positions(id)
);
Как объединить таблицы по определенному столбцу?
Для этого используем Join. Ниже разберем на примере:
У нас есть 2 таблицы:
- workers
- positions
Задача: прикрепляем к таблице workers все данные из таблицы positions. При этом сопоставляем колонку position_id и id из соответствующих таблиц.
Решение. Используем команду:
SELECT * FROM workers JOIN positions ON workers.position_id = positions.id;
Теперь, допустим, задача изменилась. Нам нужны только данные из колонок name и title (но они в разных таблицах).
Решается всё просто:
SELECT name, title FROM workers JOIN positions ON workers.position_id = positions.id;
Таким образом, выводим переменные/колонки «name», «title» из таблицы workers и positions по одинаковой колонке position_id в таблице workers и id в таблице positions. Если более точно, то мы к таблице workers (name) присоединили title из таблицы positions.
Важно: все строки, которые не сопоставились, будут откинуты. Т.е. мы в итоге получаем только те данные, где были совпадения по колонкам. Если же нам нужно полноценно дописать к первой таблице вторую, то используем LEFT JOIN.
Команда: SELECT * FROM workers LEFT JOIN positions ON workers.position_id = positions.id;
Таким образом, левая таблица будет исходной, к которой добавляем найденные данные из правой.
А если нам нужно за основу взять правую таблицу, то используем RIGHT JOIN:
Команда SELECT * FROM workers RIGHT JOIN positions ON workers.position_id = positions.id;
Ну и последний сценарий, когда нам нужно не терять строки. Стандартно такой опции нет, но есть лайфхак через UNION:
SELECT * FROM workers LEFT JOIN positions ON workers.position_id = positions.id
UNION
SELECT * FROM workers RIGHT JOIN positions ON workers.position_id = positions.id;
Что такое alias (алиас) в SQL?
Что еще важно помнить? Команда не сработает, если у нас одинаковые названия колонок. Например, если в двух таблицах мы хотим вывести колонку title, то будет некорректно использовать команду:
SELECT title, title FROM workers JOIN positions ON workers.position_id = positions.id;
Как быть в этой ситуации? Используем alias. Т.е. как бы подменяем название колонок. Для удобства рекомендуется использовать первую букву из названия исходной таблицы. Но если запустить команду так, выйдет ошибка:
SELECT w.title, p.title FROM workers JOIN positions ON workers.position_id = positions.id;
Необходимо обозначить и название таблиц
SELECT w.title, p.title FROM workers AS w JOIN positions AS p ON w.position_id = p.id;
Вы также можете опустить AS в данных конструкциях.
Как получить уникальные значения из колонки?
Ситуаций множество, когда такое может потребоваться. Например, если у нас на сайте есть фильтр и необходимо пользователю дать список для выбора из уникальных. Используем команду:
SELECT DISTINCT age FROM workers;
Используем AND, OR, NOT для выборок и их конкретизации
Например, нам нужно вывести пользователей старше 30 лет и женатых. Используется команда:
SELECT * FROM workers WHERE age > 20 and is_married=1;
И еще несколько разных сценариев
SELECT * FROM workers WHERE age > 20 and age < 25;
А если использовать OR (ИЛИ), то выведем до 20 и после 25 (а между ними будет отсутствовать).
SELECT * FROM workers WHERE age > 20 or age < 25;
А командой NOT исключаем данные, которые не нужны:
SELECT * FROM workers WHERE NOT age = 20;
Сортировка
SELECT * FROM workers ORDER BY age ASC; Сортировка по возрастанию
SELECT * FROM workers ORDER BY age DESC; Сортировка по убыванию
Также мы можем получить необходимое кол-во. Например, самого старшего сотрудника:
SELECT * FROM workers ORDER BY age DESC LIMIT 1;
Сценариев, где это потребуется, множество. Например, получить ТОП 10 самых продаваемых товаров или просматриваемых страниц.
Что еще?
Есть еще min или max. Команда выглядит следующим образом SELECT MAX(age) FROM workers;
Главное отличие от сортировки, которую выше разбирали, что возвратится только один элемент, а не вся строка.
Можно делать подсчет количества через COUNT (name); Выведет количество строк с именем. Но при этом, если будут элементы с нулями, то данные участвовать в подсчете не будут. Можно через SUM подсчитать количество в колонке. А через AVG получить среднее из колонки.
LIKE. Используется командой SELECT * FROM workers WHERE name LIKE ‘Boris’; Похоже на значение равно, но отличие в том, что мы можем получить часть данных. Для этого используем команду LIKE ‘%or%’. Где процент это как звездочка (любое значение). Можно еще задать и нужное количество символов, для этого используется нижнее подчеркивание (одно подчеркивание = один символ): LIKE (‘Bo___’);
IN. Используется командой SELECT * FROM workers WHERE age IN (18, 20, 22); Выведет данные, которые соответствуют из колонки age 18, 20, 22.
BETWEEN. Похоже на IN, но тут мы задаем диапазон. Используется командой SELECT * FROM workers BETWEEN age 18 AND 22; Выведет данные, которые соответствуют из колонки age от 18 до 22. В отличии от IN, сюда попадает и 19, и 21.
GROUP BY. Используется для группировки и в совокупности с другими данными. Например: SELECT COUNT (ID), age FROM workers GROUP BY age; Таким образом получим количество сотрудников определенного возраста.
EXISTS DATA. Например, ниже команда, где мы берем выборку из таблицы workers и в ней делаем снова выборку командой EXISTS, чтобы получить данные из другой таблицы по нашим запросам. Т.е. условие получения department_id из связанной таблицы positions, где id совпадают с основной таблицей workers. Команда: SELECT * FROM workers WHERE EXISTS (SELECT departamen_id from positions WHERE workers.position_id=positions.id AND departament_id=2).
ANY и SOME также могут решить задачу как EXISTS. Команда: SELECT * FROM workers WHERE position_id = ANY (SELECT id FROM positions WHERE departament_id = 2); Т.е. под ANY подпадает то значение, какое выбираем в скобках после ANY. Т.к. мы сравниваем position_id, то его сопоставить нам нужно с id (т.к. по ним этим id мы сопоставляем таблицы). А далее добавляем условие через WHERE: что для выборки нам нужно соответствие departament_id = 2. SOME работает аналогично.
Есть команда INSERT INTO, которая позволяет вытащить/скопировать конкретные данные из таблицы или вообще копировать таблицу в новую. INSERT INTO new_workers SELECT * FROM workers WHERE age > 24; Таким образом, в таблицу new_workers мы скопируем всех сотрудников старше 24. Для копирования важно, чтобы структура по колонкам у таблиц была идентичная. Можно копировать и без условий (1 в 1). И, естественно, таблица уже должна быть создана.
Отношение объектов в базе данных SQL
Виды отношений:
- one to one — один к одному. Например, товар привязан только к одной категории;
- one to many — один ко многим. Одна категория, много товаров;
- many to many — многие ко многим. Это когда мы делаем товар во многих категориях. А у категорий множество товаров. Либо теги;
- sql injection.
Ниже пример таблицы many to many. У нас есть таблица workers и projects. И мы делаем третью project_worker (название формируем из исходных таблиц в единственном числе и по алфавиту). И далее сопоставляем ID. У проекта может быть несколько разработчиков, а у разработчика несколько проектов.
0 комментариев