SQL – большой обзор

Язык программирования SQL заслуженно относится к числу самых широко используемых. В этом нет ничего удивительного, так как он применяется для создания и управления базами данных, с которыми человек сталкивается буквально повсюду – при проверке электронной почты, переводе с карты или покупке в онлайн-магазине. Практически любое программное обеспечение прикладного характера, так или иначе, использует базы данных, а следовательно – и язык SQL.

Определение и назначение

SQL является языком программирования (другие названия – язык структурированных запросов, декларативный язык), который используется для создания, изменения и управления так называемыми реляционными базами данных. К последним относятся такие массивы информации, которые взаимосвязаны между собой, упорядочены и структурированы, другими словами – имеют формат таблицы.

SQL не является полноценным языком для программирования. Дело в том, что он не используется для непосредственного написания программного кода приложения. Но SQL активно применяется в функционировании внутреннего ПО веб-сайтов и онлайн-сервисов, обозначаемого как бэкенд.

Любой запрос пользователя в поисковике и почти любой обмен данными между пользователем и сервером практически всегда происходит с непосредственным использованием SQL.

История появления и становления

Модель реляционных или структурированных БД впервые была разработана и детально описана более полувека назад - в 1970 году. Еще через четыре года был разработан язык SQL. Первый прототип СУБД появился в 1986 году в результате разработок специалистов IBM. Полноценная реляционная база данных со встроенной системой управления выпущена тогда же компанией Relation Software, позже переименованной в Oracle.

Как работают запросы?

Чтобы понять правила практического использования функционала SQL, необходимо описать механизм работы запросов. Любой из них перемещается по цепочке от пользователя к таблице базы данных через нескольких посредников – клиента, систему управления и базу данных. Важно знать значения каждого из понятий:

• под клиентом понимается способ введения и формат запроса, например, слово или словосочетание в поисковой строке браузера;
• СУБД представляет собой комплекс программных средств для управления данными;
• база данных – это набор структурированной информации;
• таблица – место размещения (хранилище) данных.

Обычно таблицы связаны между собой ключами – специальными столбцами, которые логически объединяют разные кластеры информации. Ключи – это основной механизм баз данных, потому что ключи помогают быстро искать нужную информацию и формировать новые таблицы на основе уже существующих.

БД в SQL

Обязательным условием задействования языка структурирования запросов выступает наличие реляционной базы данных. Самый простой и наглядный пример последней – это обычные электронные таблицы Excel. Можно выделить несколько характеристик, обязательных для подобных БД:

• двухмерность;
• присутствие столбцов и строк;
• атрибут, под которым понимается определенный столбец с конкретным параметром, например, датой;
• домен, обозначающий диапазон допустимых значений атрибута (для имени – набор букв, для даты – несколько цифр, разделенных точками, и т.д.);
• кортеж (другие названия – запись, строка) – пронумерованная строка таблицы со значениями одного объекта;
• значение – компонент (элемент) таблицы, находящийся на пересечении строки и столбца;
• ключ – главный столбец (обычно первый) таблицы, который используется для взаимосвязи с другими таблицами базы данных.

Выделяют три вида ключей:

1. Первичный представляет собой артикул, индекс или идентификатор.
2. Потенциальный является другим уникальным значением, выступающим в качестве идентификатора.
3. Внешний – это столбец, играющий роль ссылки и объединяющий две разных таблицы. При этом любое значение внешнего ключа в одной таблице имеет обязательное соответствие первичному из второй.

Есть еще составной ключ – это когда в одной таблице находится несколько первичных ключей. Нужно это в тех ситуациях, когда уникальность элемента таблицы нужно проверять по нескольким параметрам – например, вы хотите найти в таблице не просто пиццу с грибами, а пиццу с грибами стоимостью с 990 рублей. Сложность использования составного ключа – в том, что вы обязательно должны заполнять все поля, которые указаны как ключевые. Создается составной ключ так же, как и первичный, только указываете сразу несколько полей:

create table test(field_1 int, field_2 text, field_3 bigint, primary key (field_1, field_3));

Система ключей в SQL – достаточно сложная на первый взгляд, но вы быстро привыкнете к ней, если будете постоянно работать с таблицами.

Операторы SQL

Важной частью языка выступают операторы, под которыми понимаются слова/символы, используемые для совершения различных действий с данными. Например, сортировки по возрастанию или убыванию. По функциональному назначению различают четыре группы операторов:

1. Язык определения (Data Definition Language, сокращенно - DDL). Предназначены для работы с целыми таблицами, то есть полноценными объектами. Например, при добавлении новых или удалении существующих данных. К числу DDL относятся: CREATE/ALTER и DROP, которые используются, соответственно, для создания/изменения/удаления объекта из БД.
2. Язык манипуляции (Data Manipulation Language, сокращенно - DML). Применяются для работы с содержимым таблицы, включая строки, атрибуты и значения. В их числе: SELECT/INSERT/UPDATE/DELETE – соответственно, выбор по условию/добавление новых/изменение существующих/удаление данных.
3. Язык разграничения доступа (Data Control Language, сокращенно - DCL). Предоставляют доступ пользователям для внесения изменений или получение данных из БД. Включают три оператора: GRANT/REVOKE и DENY, позволяющие предоставить доступ/произвести отзыв разрешения или вынести запрет в отношении уже выданного разрешения.
4. Язык контроля над транзакциями (Translation Control Language, сокращенно - TSL). Под транзакциями понимается набор последовательно осуществляемых команд. К этой группе операторов относятся такие: BEGIN/CVOMMIT/ROLLBACK/SAVE (к каждому добавляется слово TRANSACTION) – начало/изменение/отказ/промежуточное сохранение данных в ходе исполнения транзакции.

Задачи и разновидности СУБД

Эффективное использование возможностей SQL предполагает понимание задач, которые стоят перед СУБД. Современные системы управления БД способны выполнять широкий спектр функций:

• поддерживать язык базы данных;
• управлять структурированной информацией;
• управлять хранилищами оперативной памяти;
• совершать и контролировать транзакции;
• осуществлять резервное копирование БД и восстановление системы после сбоя.

Разработкой СУБД занимаются как крупные корпорации, например, Google, Amazon или Microsoft, так и специализированные компании с нишевым охватом.

Результатом стало появление нескольких разновидностей языка, часто называемых SQL-диалектами. Все они имеют общую основу, но и содержат определенные отличия, присущие только каждому конкретному проекту. Характеристики нескольких наиболее распространенных СУБД приведены в таблице.

Начало работы с SQL

Чтобы понять принципы работы SQL, достаточно просто немного разбираться в том, как функционирует обычный Excel. Также потребуется начальный уровень знания английского языка, необходимый для понимания основ синтаксиса, который представлен следующими командами:

• выбор данных –

  SELECT;

• откуда брать –

  FROM;

• добавление таблицы –

  JOIN;

• введение условия –

  WHERE;

• группировка и сортировка данных по определенному признаку –

  GROUP BY;

  и

  ORDER BY;

• ограничение на число результатов –

  LIMIT;

• окончание предложения –

  «;»

  .

Практически полностью идентичные команды используются для работы любых СУБД. Экран системы выглядит следующим образом.

Тренируемся на тренажере

SQL нужно постигать на практике. К счастью, для начала вам не нужно самостоятельно разворачивать sql-сервер или MySQL у себя на компьютере – можно воспользоваться онлайн-эмулятором базы данных. Выглядит он так:

Как видите, сама база и 3 таблицы в ней уже созданы. По центру – поле для ввода и поле для вывода. Пишем запрос в поле для ввода, жмем на «Run SQL» – получаем результат в поле для вывода.

Для начала освоим SELECT – введите и исполните следующую команду:

SELECT first_name, last_name FROM Customers;

SELECT – это оператор выбора, мы дали ему поля, которые нужно вывести. FROM – откуда брать информацию, мы указали, что хотим взять информацию из таблицы Customers. Результат:

SELECT – крайне мощная команда, и пользуются ей чаще всего. Поэтому вам нужно хотя бы приблизительно помнить дополнительные опции, которые к команде можно применять:

• выбрать все из таблицы –

  SELECT * FROM Customers;

• выбрать из таблицы все записи, у которых в столбце значится конкретное значение –

  SELECT * FROM Customers WHERE last_name = 'Doe';

• выбрать из таблицы все записи, у которых значение в столбце больше указанного. Аналогично работает со знаком «<» –

  SELECT * FROM Customers WHERE age > 25;

• логическое «И», логическое «ИЛИ» пишется как «OR» –

  SELECT * FROM Customers WHERE last_name = 'Doe' AND country = 'USA';

• более короткая и логичная версия, выбирающая одно из нескольких –

  SELECT first_name, country FROM Customers WHERE country IN ('USA', 'UK');

• выбрать из таблицы покупателей все имена-фамилии при условии, что в столбце страны стоит НЕ «USA» –

  SELECT first_name, last_name FROM Customers WHERE NOT country = 'USA';

• все вышеописанные операторы можно комбинировать –

  SELECT * FROM Customers WHERE (country = 'USA' OR country = 'UK') AND age < 26;

  
  
  
  
• отобрать уникальные значения. Будут взяты все страны всех покупателей, дубликаты стран будут удалены –

  SELECT DISTINCT country FROM Customers;

• то же, что и предыдущее, но вам покажут количество уникальных стран, а не их название –

  SELECT COUNT(DISTINCT country) FROM Customers;

• AS создает алиас (синоним) для названия колонки, то есть в выводе будет не «first_name : [имя покупателя]», а «name : [имя покупателя]» –

  SELECT first_name AS name FROM Customers;

• алиасам можно задавать паттерн через CONCAT (конкатенацию) – в данном случае имя и фамилия будут объединены через пробел –

  SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM Customers;

  
  
  
  
• взять два верхних –

  SELECT TOP 2 * FROM Customers;

• взять два верхних с отступом в 3 записи (то есть начать с 4-й) –

  SELECT first_name, last_name FROM Customers LIMIT 2 OFFSET 3;

• а вот тут начинается магия сложных запросов. То, что указано в скобках, исполняется в первую очередь. Что мы тут сделали: выбрали id и имена клиентов в таблице клиентов при условии, что эти же id клиентов записаны в таблице заказов. Проще говоря: выбрали всех клиентов, которые делали заказы –

  SELECT customer_id, first_name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders);

  
  
  
  
• выбрать всех работников, у которых пустота в поле емэйлов –

  SELECT * FROM Employee WHERE email IS NULL;

• выбрать всех покупателей, отсортировав их по имени –

  SELECT * FROM Customers ORDER BY first_name;

• посчитать количество вхождений стран в клиентах –

  SELECT country, COUNT(*) AS number FROM Customers GROUP BY country;

Это – не все возможности SELECT, но оставшиеся вы сами найдете в мануалах при необходимости, они используются намного реже.

Вторая команда, которую стоит рассмотреть – это JOIN, объединение таблиц:

SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer;

Сначала мы выбираем из клиентов id, имя и сумму чека, а затем объединяем эту информацию с данными из таблицы заказов, если id покупателя совпадает в таблицах заказов и клиентов.

Команды для работы с таблицами:

• создать новую базу данных –

  CREATE DATABASE my_db;

• создать новую базу данных только в том случае, если такой еще не существует –

  CREATE DATABASE IF NOT EXISTS my_db;

• показать все базы данных –

  SHOW DATABASES;

• переключиться на другую базу данных –

  USE my_db;

• создать новую таблицу с указанными колонками –

  CREATE TABLE Companies (id int, name varchar(50), address text, email varchar(50), phone varchar(10));

• уничтожить базу данных (Восстановить удаленную базу данных невозможно) –

  DROP DATABASE my_db;

• уничтожить таблицу (Восстановить удаленную таблицу невозможно) –

  DROP TABLE my_table;

• добавить колонку в таблицу –

  ALTER TABLE Customers ADD phone varchar(10);

• переименовать колонку в таблице –

  ALTER TABLE Customers RENAME COLUMN customer_id TO c_id;

• удалить колонку из таблицы –

  ALTER TABLE Customers DROP COLUMN age;

• создать бэкап базы данных на диске –

  BACKUP DATABASE my_db TO DISK = 'C:\my_db_backup.bak';

Наконец, последнее, без чего вы не сможете полноценно использовать базы данных – это изменение информации в них:

• вставить в таблицу новую запись со значениями –

  INSERT INTO Customers(customer_id, first_name, last_name, age, country) VALUES (5, 'Harry', 'Potter', 31, 'USA');

  
  
  
  
• обновить (перезаписать) информацию новым значением –

  UPDATE Customers SET first_name = 'Johnny' WHERE customer_id = 1;

• скопировать все данные из одной таблицы в другую –

  SELECT * INTO CustomersCopy FROM Customers;

• скопировать данные из двух таблиц в третью –

  SELECT Customers.customer_id, Customers.first_name, Orders.amount INTO CustomerOrders FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id;

• скопировать некоторые колонки из одной таблицы в другую –

  INSERT INTO OldCustomers(customer_id, age) SELECT customer_id, age FROM Customers;

• скопировать схему из одной таблицы в другую, то есть во второй таблице появятся пустые колонки –

  SELECT * INTO NewCustomers FROM Customers WHERE false;

Кто использует SQL?

Различные базы данных используются повсеместно. Особенно полезным умение самостоятельно работать с СУБД с помощью SQL ними окажется для следующих категорий специалистов:

1. Аналитики из разных сфер деятельности, работающие с большими объемами структурированной информации.
2. Продуктовые маркетологи, которые трудятся как в онлайн- так и офлайн-торговле.
3. Тестировщики и разработчики ПО, которым требуется оптимизировать и отладить приложения.
4. Руководство и менеджмент любого уровня.

Что почитать или посмотреть по теме?

1. Детальное руководство пользователя SQL на английском языке (SQL Tutorial).
2. Алан Бьюли «Изучаем SQL».
3. Энтони Молинаро «SQL. Сборник рецептов».
4. Полн Н. Вайнберг, Джеймс Р. Грофф, Эндрю Дж. Оппель «SQL. Полное руководство».
5. Аллен Тейлор «SQL для чайников».
6. К. Дж. Дейт «SQL и реляционная теория».
7. Крис Фиайли «SQL: Руководство по изучению языка».
8. Канал на YouTube «Основы SQL».
9. Видео ролик на YouTube «Индексы/Основы SQL».
10. Видео ролик на YouTube «Уроки SQL для начинающих».

FAQ

Что такое SQL?

Это язык структурированных запросов (или декларативный язык программирования), который используется для backend-разработки систем управления базами данных (СУБД).

Что такое СУБД?

Это набор программных инструментов и средств для хранения, обработки, изменения и анализа обширных массивов информации.

В чем заключаются основные задачи СУБД?

С помощью SQL и созданных с его помощью СУБД осуществляются самые разнообразные действия с базами данных – создание, пополнение, редактирование и т.д.

Где используются SQL и СУБД?

Сегодня разнообразные структурированные базы данных применяются практически повсеместно – в поисковых системах, в социальных сетях, в онлайн-магазинах и т.д.

Как освоить SQL?

Изучить принципы практического использования SQL и СУБД можно как самостоятельно, так и посредством посещения онлайн-курсов. Первый вариант позволяет сэкономить, второй обеспечивает эффективность обучения и оперативность получения нужных навыков и знаний.

Курсов по SQL очень мало, поскольку этот язык обычно учат по мануалам и на практике. Но один хороший курс все же есть, на Степике. На курсе вы за 30 часов обучения полностью освоите все распространенные и специфические команды, научитесь делать сложные и вложенные запросы. Курс – строго практической направленности, вам дадут 261 задание для выполнения.

Подведем итоги

1. Язык структурированных запросов SQL применяется для работы с объемными массивами информации, которая представлена в виде таблиц.
2. С помощью SQL создаются СУБД, которые используются как самыми крупными корпорациями, так и небольшими онлайн-сервисами или интернет-магазинами.
3. Для понимания принципов работы СУБД достаточно уметь пользоваться электронными таблицами Excel и иметь базовое знание английского языка.
4. Научиться применять SQL на практике можно как самостоятельно (посредством чтения самоучителей или изучения тематических ресурсов), так и на разнообразных учебных курсах.
5. Знание и умение пользоваться SQL и СУБД особенно пригодится аналитикам, маркетологам, менеджерам разного уровня, финансистам и экономистам.