- Нулевое значение (NULL) — это значение поля, которое является пустым (не имеющего значения).
Чтобы запретить хранение null в столбце, используйте ключевые слова NOT NULL при создании таблицы. - Условие DEFAULT определяет знэчение по умолчанию — если при заполнении таблицы значение столбца не указано, он автоматически заполняется этим значением.
CREATE TABLE name_table (
str_name VARCHAR(10) NOT NULL,
str_name2 VARCHAR(6) NOT NULL,
str_name3 DEC(3,2) NOT NULL DEFAULT 1.00
);
CREATE - создание базы данных
CREATE DATABASE databasename
BACKUP DATABASE - полная резервная копия
BACKUP DATABASE databasename
TO DISK = 'filepath'
USE DATABASE - команда открывает базу данных для создания таблиц
CREATE TABLE - создание таблицы
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....
)
ALTER - добавление, удаление или изменение столбцов в существующей таблице
ALTER TABLE table_name
ADD column_name datatype
AUTO INCREMENT - автоматически генерирует уникальное число при вставке новой записи в таблицу
ALTER TABLE Persons AUTO_INCREMENT=100
DROP TABLE - удаление таблицы
CREATE INDEX - для более быстрого извлечения данных из базы данных, чем в других случаях
CREATE INDEX index_name
ON table_name (column1, column2, ...)
Ограничения (constraints) — это правила, которые используются для ограничения данных.
Ограничения могут устанавливаться как на уровне колонки, так и на уровне таблицы.
NOT NULL - столбец не может иметь нулевое значение
UNIQUE - все значения в столбце будут разными
PRIMARY KEY - уникально идентифицирует каждую строку в таблице
FOREIGN KEY - идентифицирует строку/запись в другой таблице
CHECK - все значения в столбце удовлетворяют определенному условию
DEFAULT - задает значение по умолчанию для столбца
INDEX - для быстрого создания и извлечения данных из базы данных
INSERT INTO - вставка новых записей в таблицу
INSERT INTO table_name
VALUES (value1, value2, value3, ...)
UPDATE - изменение существующих данных
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition
DELETE - удаление данных
DELETE FROM table_name WHERE condition
SELECT - используется для выбора данных из базы данных
SELECT column1, column2, ...
FROM table_name
- SELECT MIN() и MAX() - возвращает наименьшее/наибольшее значение выбранного столбца
SELECT MIN(column_name)/SELECT MAX(column_name) FROM table_name WHERE condition
- SELECT COUNT(), AVG() и SUM() - возвращает количество строк, соответствующих заданному критерию/среднее значение/общую сумму
SELECT COUNT(column_name)
FROM table_name
WHERE condition
- SELECT DISTINCT - для возврата только определенных значений
SELECT DISTINCT column1, column2, ...
FROM table_name
- SELECT INTO - копирует данные из одной таблицы в новую
SELECT column1, column2, column3, ...
INTO newtable
FROM oldtable
WHERE condition
- INSERT INTO SELECT - копирует данные из одной таблицы и вставляет их в другую таблицу
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition
CASE - проходит через условия и возвращает значение, когда выполняется первое условие
SELECT column1
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END
FROM table1
FROM - из какой таблицы(таблиц) извлекаем данные
WHERE - для извлечения только тех записей, которые удовлетворяют заданному условию
+ AND/OR/NOT - спользуются для фильтрации записей на основе более чем одного условия
SELECT column1, column2, ...
FROM table_name
WHERE condition AND condition2 AND condition3 ...
В выражении WHERE используются следующие операторы:
- BETWEEN - значения в заданном диапазоне
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2
- LIKE - поиск шаблона
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern
- IN - чтобы указать несколько возможных значений для столбца
SELECT column_name(s) FROM table_name
WHERE column_name IN (value1, value2, ...)
ORDER BY - сортируем данные в порядке возрастания или убывания
+ASC - по умолчанию(в порядке возрастания)
+DESC - по убыванию
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC
LIMIT - ограничиваем количество выводимых строк
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number
JOIN - объединение данных из двух или более таблиц
SELECT column_name(s)
FROM table1
INNER JOIN table2 / LEFT JOIN table2 / RIGHT JOIN table2 / FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
UNION - объединение результирующего набора из двух или более заявлений SELECT
SELECT column_name(s) FROM table1
UNION / UNION ALL
SELECT column_name(s) FROM table2
GROUP BY - группировать данные
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s)
HAVING - фильтровать данные, полученные на основе группировки
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s)
ANY - возвращает true, если какое-либо из значений подзапроса удовлетворяет условию
ALL - возвращает true, если все значения подзапроса удовлетворяют условию
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY / ALL
(SELECT column_name FROM table_name WHERE condition
EXISTS - для проверки наличия любой записи в подзапросе
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition)
SELECT column_name AS alias_name
FROM table_name
Вывод ограниченного набора строк
SELECT * FROM table
WHERE ROWNUM <= 5
Поиск по таблицам в существующей схеме / по всем доступным схемам
SELECT table_name FROM user_tables / all_tables
Поиск по полям и таблицам в существующей схеме / по всем доступным схемам
SELECT table_name FROM user_tab_columns / all_tab_columns
Поиск по всем объектам базы данных
SELECT * FROM all_objects
WHERE object_type = 'table'
Просмотр даты создания и изменения таблицы
SELECT object_name, created, last_ddl_time
Искать комментарии / привязать поиск к полям таблицы
SELECT * FROM all_tab_comments / all_col_comments
Выдача привилегий
GRAND select ON table_1 TO cheme_name or
GRAND all ON table_1 TO cheme_name
Отзыв привилегий
REVOKE select ON table_1 FROM cheme_name
Какие привилегии выданы
SELECT * FROM dba_tab_privs
Представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition
Удаление
DROP VIEW view_name
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
DATE - формат YYYY-MM-DD
DATETIME - формат: YYYY-MM-DD HH:MI:SS
SMALLDATETIME - формат: YYYY-MM-DD HH:MI:SS
TIMESTAMP - формат: уникальное число
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO
EXEC SelectAllCustomers
-- Однострочный комментарий
/* Многострочный комментарий */
- Функции для обработки текста
LEFT() отбирает символы в тексте слева
RIGHT() отбирает символы в тексте справа
MID() отбирает символы с середины текста
UCase() переводит символы в верхний регистр
LCase() переводит символы в нижний регистр
LTrim() удаляет все пустые символы слева от текста
RTrim() удаляет все пустые символы справа от текста
Trim() удаляет все пустые символы с обеих сторон текста
- Функции для обработкт чисел
SQR() возвращает корень квадратный указанного числа
ABS() возвращает абсолютное значение числа
EXP() возвращает экспоненту указанного числа
SIN() возвращает синус указанного угла
COS() возвращает косинус указанного угла
TAN() возвращает тангенс указанного угла
- Функции для обработки даты и времени
DatePart() возвращает часть даты: год, квартал, месяц, неделя, день, час, минуты, секунды
Year(), Month() возвращает год и месяц соответственно
Hour(), Minute(), Second() возвращает час, минуты и секунды указанной даты
WeekdayName() возвращает название дня недели
- Статические функции
COUNT() возвращает число строк в таблице или столбце
SUM() возвращает сумму значений в столбце
MIN() возвращает наименьшее значение в столбце
MAX() возвращает наибольшее значение в столбце
AVG() возвращает среднее значение в столбце
SELECT
Название функции (столбец для вычислений)
OVER (
> PARTITION BY столбец для группировки
> ORDER BY столбец для сортировки
> ROWS или RANGE выражение для ограничения строк в пределах группы
> )
-
Агрегатные функции
SUM – возвращает сумму значений в столбце
COUNT — вычисляет количество значений в столбце (значения NULL не учитываются)
AVG — определяет среднее значение в столбце
MAX — определяет максимальное значение в столбце
MIN — определяет минимальное значение в столбце -
Ранжирующие функции
ROW_NUMBER – функция возвращает номер строки и используется для нумерации
RANK — функция возвращает ранг каждой строки. В данном случае значения уже анализируются и, в случае нахождения одинаковых, возвращает одинаковый ранг с пропуском следующего значения
DENSE_RANK — функция возвращает ранг каждой строки. Но в отличие от функции RANK, она для одинаковых значений возвращает ранг, не пропуская следующий
NTILE – это функция, которая позволяет определить к какой группе относится текущая строка. Количество групп задается в скобках -
Функции смещения
LAG или LEAD – функция LAG обращается к данным из предыдущей строки окна, а LEAD к данным из следующей строки.
Функцию можно использовать для того, чтобы сравнивать текущее значение строки с предыдущим или следующим.
Имеет три параметра: столбец, значение которого необходимо вернуть, количество строк для смещения (по умолчанию 1), значение, которое необходимо вернуть если после смещения возвращается значение NULL
FIRST_VALUE или LAST_VALUE — с помощью функции можно получить первое и последнее значение в окне. В качестве параметра принимает столбец, значение которого необходимо вернуть -
Аналитические функции
CUME_DIST — вычисляет интегральное распределение (относительное положение) значений в окне
PERCENT_RANK — вычисляет относительный ранг строки в окне
PERCENTILE_CONT — вычисляет процентиль на основе постоянного распределения значения столбца. В качестве параметра принимает процентиль, который необходимо вычислить (в этой статье я рассказываю как посчитать медиану, благодаря этой функции)
PERCENTILE_DISC — вычисляет определенный процентиль для отсортированных значений в наборе данных. В качестве параметра принимает процентиль, который необходимо вычислить
Транзакция представляет собой набор команд выполняемых как единое целое.
Если в ходе транзакции не удается выполнить хотя бы одну операцию, то не выполняется ни одна операция.
Свойства транзакций ACID (Atomicity , Consistency , Isolation , Durability):
- Atomicity — Атомарность Атомарность гарантирует, что каждая транзакция будет выполнена полностью или не будет выполнена совсем. Не допускаются промежуточные состояния.
- Consistency — Согласованность Это свойство вытекает из предыдущего. Благодаря тому, что транзакция не допускает промежуточных результатов, база остается консистентной.
- Isolation — Изолированность Во время выполнения транзакции параллельные транзакции не должны оказывать влияния на её результат.
- Durability — Надёжность Если пользователь получил подтверждение от системы, что транзакция выполнена, он может быть уверен, что сделанные им изменения не будут отменены из-за какого-либо сбоя.
BEGIN | START TRANSACTION — запуск транзакции
COMMIT — сохранение изменений
ROLLBACK — отмена изменений
Индексы — это специальные поисковые таблицы (lookup tables), которые используются движком БД в целях более быстрого извлечения данных. Проще говоря, индекс — это указатель или ссылка на данные в таблице.
Индексы ускоряют работу инструкции SELECT и предложения WHERE, но замедляют работу инструкций UPDATE и INSERT. Индексы могут создаваться и удаляться, не оказывая никакого влияния на данные.
CREATE INDEX indexName ON tableName;
Защита учетной записи:
SET PASSWORD FOR 'root' @ 'localhost' = PASSWORD('b4dZ');
Создание нового пользователя:
CREATE USER name_user IDENTIFIED BY 'cl3sdy5';
Команда GRANT точно определяет, какие операции с таблицамии столбцами могут выполняться пользователями
GRANT SELECT ON table_name TO name_user;
Команда REVOKE лишает предоставленной привилегии
REVOKE SELECT ON table_name FROM name_user;
Примечание:
sqlfiddle поиграть с запросами можно здесь
часть 1 понимание джойнов сломано
часть 2 попытка альтернативной визуализации
Упражнения по SQL
Интерактивный учебник по SQL