SQL. Основные команды и операторы. Часть 1

SQL является стандартным языком для хранения, редактирования, удаления и извлечения данных в базах данных.

SQL расшифровывается как "структурированный язык запросов". SQL позволяет обращаться к базам данных и управлять ими. SQL стал стандартом американского Национального института стандартов (ANSI) в 1986, и международной организации по стандартизации (ISO) в 1987.

Самые используемые команды SQL

DML — язык изменения данных (Data Manipulation Language)

SELECT - Извлекает данные из базы данных;
UPDATE - обновляет данные в базе данных;
DELETE - Удаление данных из базы данных;
INSERT INTO - Вставка новых данных в базу данных;

DDL — язык определения данных (Data Definition Language)

CREATE DATABASE - создает новую базу данных;
ALTER DATABASE - изменяет базу данных;

CREATE TABLE - Создание новой таблицы;
ALTER TABLE - изменяет таблицу;
DROP TABLE - Удаление таблицы;

CREATE INDEX - создает индекс (ключ поиска);
DROP INDEX - Удаляет индекс.

DCL — язык управления данными (Data Control Language)

GRANT - Наделяет пользователя правами;
REVOKE - Отменяет права пользователя.

Ограничения (constraints)

Ограничения могут устанавливаться как на уровне колонки, так и на уровне таблицы.

Среди наиболее распространенных ограничений можно назвать следующие:

NOT NULL — колонка не может иметь нулевое значение;
DEFAULT — значение колонки по умолчанию;
UNIQUE — все значения колонки должны быть уникальными;
PRIMARY KEY — первичный или основной ключ, уникальный идентификатор записи в текущей таблице;
FOREIGN KEY — внешний ключ, уникальный идентификатор записи в другой таблице (таблице, связанной с текущей);
CHECK — все значения в колонке должны удовлетворять определенному условию;
INDEX — быстрая запись и извлечение данных.

Любое ограничение может быть удалено с помощью команды ALTER TABLE и DROP CONSTRAINT + название ограничения. Некоторые реализации предоставляют сокращения для удаления ограничений и возможность отключать ограничения вместо их удаления.

Целостность данных

В каждой СУБД существуют следующие категории целостности данных:

целостность объекта (Entity Integrity) — в таблице не должно быть дубликатов (двух и более строк с одинаковыми значениями);
целостность домена (Domain Integrity) — фильтрация значений по типу, формату или диапазону;
целостность ссылок (Referential integrity) — строки, используемые другими записями (строки, на которые в других записях имеются ссылки), не могут быть удалены;
целостность, определенная пользователем (User-Defined Integrity) — дополнительные правила.

Нормализация БД

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

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

Нормализация предполагает соблюдение нескольких форм. Форма — это формат структурирования БД. Существует три главных формы: первая, вторая и, соответственно, третья. Я не буду вдаваться в подробности об этих формах, при желании, вы без труда найдете необходимую информацию.

Типы данных

Каждая колонка, переменная и выражение в SQL имеют определенный тип данных (data type). Основные категории типов данных:

Точные числовые

Приблизительные числовые

Дата и время

Строковые символьные

Строковые символьные (юникод)

Бинарные

Смешанные

Синтаксис SQL

Логические операторы

Встроенных функций для чисел

AVG — вычисляет среднее значение;
SUM — вычисляет сумму значений;
MIN — вычисляет наименьшее значение;
MAX — вычисляет наибольшее значение;
COUNT — вычисляет количество записей в таблице.
ROUND — округляет число;
TRUNCATE — обрезает дробное число до указанного количества знаков после запятой;
CEILING — возвращает наименьшее целое число, которое больше или равно текущему значению;
FLOOR — возвращает наибольшее целое число, которое меньше или равно текущему значению;
POWER — возводит число в указанную степень;
SQRT — возвращает квадратный корень числа;
RAND — генерирует случайное число с плавающей точкой в диапазоне от 0 до 1.

Встроенных функций для строк

CONCAT — объединение строк;
LENGTH — возвращает количество символов в строке;
TRIM — удаляет пробелы в начале и конце строки;
SUBSTRING — извлекает подстроку из строки;
REPLACE — заменяет подстроку в строке;
LOWER — переводит символы строки в нижний регистр;
UPPER — переводит символы строки в верхний регистр и т.д.

Встроенные инструменты для работы с временем и датой

Возвращает текущую время и дату:

SELECT CURRENT_TIMESTAMP;

CURRENT_TIMESTAMP — это и выражение, и функция (CURRENT_TIMESTAMP()). Другая функция для получения текущей даты и времени — NOW().

Функции для разбора даты и времени:

DAYOFMONTH(date) — возвращает день месяца в виде числа
DAYOFWEEK(date) — возвращает день недели в виде числа
DAYOFYEAR(date) — возвращает номер дня в году
MONTH(date) — возвращает месяц
YEAR(date) — возвращает год
LAST_DAY(date) — возвращает последний день месяца в виде даты
HOUR(time) — возвращает час
MINUTE(time) — возвращает минуты
SECOND(time) — возвращает секунды и др.

Функции для манипулирования датами:

DATE_ADD(date, interval) — выполняет сложение даты и определенного временного интервала
DATE_SUB(date, interval) — выполняет вычитание из даты определенного временного интервала
DATEDIFF(date1, date2) — возвращает разницу в днях между двумя датами
TO_DAYS(date) — возвращает количество дней с 0-го дня года
TIME_TO_SEC(time) — возвращает количество секунд с полуночи и др.

Для форматирования даты и времени используются функции DATE_FORMAT(date, format) и TIME_FORMAT(date, format), соответственно.

Инструкция "SELECT"

Инструкция SELECT используется для выбора данных из базы данных.

Возвращаемые данные хранятся в результирующей таблице, называемой результирующим набором.

SELECT column1, column2, ...
FROM table_name

Где:

column1, column2 - имена столбцов;
table_name - имя таблицы.

1. Можно использовать синтаксис SELECT * FROM table_name.

По выполнению этого запроса выводится вся таблица полностью.

2. Можно использовать некоторое условие (condition). Например, для запроса:

SELECT * FROM table_name WHERE name <> 'example'

будут выводиться только те сроки таблицы table_name, поле name у которых не равно значению "example".

Операторы в WHERE

"=", равно - строгое равенство какому-то значению;
"<>" - не равно - строгое неравенство какому-то значению;
">", больше - больше какого-то значения (корректно работает только для числовых значений или единичных символов);
"<", меньше - меньше какого-то значения (корректно работает только для числовых значений или единичных символов);
">=", больше или равно - равно или больше какого-то значения (корректно работает только для числовых значений или единичных символов);
"<=", меньше или равно - равно или меньше какого-то значения (корректно работает только для числовых значений или единичных символов);
"BETWEEN", между - между каким-то инклюзивным диапазоном;
"LIKE", похоже - используется для поиска по какому-то шаблону;
"IN", в - используется для задания нескольких конечных значений;
"IS NULL" - используется для выбора данных, не имеющих значения (или имеющих значение "NULL").

NULL - это принятое в SQL обозначение пустого значения. Очевидно, что 0 не может быть пустым значением. Значение "NULL" означает, что значение пока не было задано или было удалено.

Кроме того, в операторе WHERE может находиться несколько условий поиска, между которыми могут операторы "AND", "OR" или "NOT".

AND - отображает те записи, для которых все условия выполняются;
OR - отображает те записи, для которых какое-либо из условий выполняется;
NOT - отображает те записи, для которых условие не выполняется.

Рассмотрим применение каждого оператора

Равно
SELECT * FROM table_name WHERE name = 'example'
Выводит из таблицы "table_name", только те строки, в которых значение столбца "name" принимает значение "example".
Не равно SELECT * FROM table_name WHERE name <> 'example'
Выводит из таблицы "table_name", только те строки, в которых значение столбца "name" принимает значение не "example".
Больше
SELECT * FROM table_name WHERE number > '1'
Выводит из таблицы "table_name", только те строки, в которых значение столбца "number" больше 1.
Меньше
SELECT * FROM table_name WHERE number < '1'
Выводит из таблицы "table_name", только те строки, в которых значение столбца "number" меньше 1.
Больше или равно SELECT * FROM table_name WHERE number >= '1'
Выводит из таблицы "table_name", только те строки, в которых значение столбца "number" больше или равно 1.
Меньше или равно SELECT * FROM table_name WHERE number <= '1'
Выводит из таблицы "table_name", только те строки, в которых значение столбца "number" меньше или равно 1.
BETWEEN SELECT * FROM table_name WHERE number BETWEEN '1' AND '3'
Выводит из таблицы "table_name", только те строки, в которых значение столбца "number" больше или равно 1 и меньше или равно 3.
Очевидно, что данный запрос можно заменить на запрос:
SELECT * FROM table_name WHERE number >= '1' AND number <= '3'
LIKE
SELECT * FROM FamilyMembers WHERE status LIKE '%ther%'
Выводит из таблицы "FamilyMembers ", только те строки, в которых значение столбца "status" содержит "ther" (например, "mother" и "father").
IN SELECT * FROM table_name WHERE number IN ( '1','3','5')
Выводит из таблицы "table_name", только те строки, в которых значение столбца "number" равно 1, 2 или 3.
IS NULL
SELECT * FROM table_name WHERE number IS NULL
Выводит из таблицы "table_name", только те строки, в которых значение столбца "number" не задано (или равно NULL). Очевидно, что можно заменить на
SELECT * FROM table_name WHERE number = NULL
Но использование IS NULL более наглядно.