Содержание

SQL

SQL – Structured Query Language (язык структурированных запросов), т.е. язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных, управляемой соответствующей системой управления базами данных.

Классификация SQL команд или что есть DML, DDL, DCL и TCL

Data Manipulation Language (работа со строками)

Приведенные ниже команды группы DML работают исключительно со строками и выполняются преимущественно клиентом:

INSERT — добавление строк(и);
SELECT — выборка строк(и);
UPDATE — изменение строк(и);
DELETE— удаление строк(и);

--Вставка строки в таблицу books
INSERT INTO books (title, author, `year`, `description`) 
	VALUES ('Лабиринт тайных книг', 'Флавия Эрметес', 2001, 'Книга о книгах');
 
--Выборка заголовка и описания книги с ID равным 5 из таблицы books.
SELECT title, description 
	FROM books 
		WHERE id=5;
 
--Изменение строки с ID равным 4.
UPDATE books 
	SET author = 'Петр Иваныч' 
		WHERE  id = 4;
 
--Удаление записи с ID равным 3.
DELETE FROM books 
	WHERE  id=3;

Data Definition Language (работа со структурой базы)

Команды DDL подразумевают под собой создание и организацию структуры как самой БД так и ее объектов. Со строками такая группа операторов не работает вовсе.

CREATE — создание объекта (например таблицы);
ALTER— изменение объекта (например добавление/изменение полей);
DROP — удаление объекта;

--Создание таблицы students с полями id, name, group. ID — первичный ключ. 
--Сравнение в utf8, движок MyISAM.
CREATE TABLE students (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL,
	`group` VARCHAR(255) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COMMENT='Студенты'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;
 
--Добавление в таблицу students поля "date birth" после поля ID с разрешением не заполненных значений.
ALTER TABLE `students`
	ADD COLUMN `date birth` DATE NULL AFTER `id`;
 
--Удаление таблицы studets.
DROP TABLE studets;

Data Control Language (работа с правами доступа)

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

GRANT — назначения прав пользователю;
DENY — явный запрет для пользователя;
REVOKE — отменить запрет или разрешение;

--Назначение права выборки из таблицы student пользователю new_user.
GRANT SELECT ON student TO new_user;
 
--Запрет права выборки из таблицы student пользователя new_user.
DENY SELECT ON student TO new_user;
 
--Отменить запрет.
REVOKE SELECT ON student FROM new_user;

Transaction Control Language (работа с транзакциями)

Группа команд по работе с транзакциями, в остальном — тема целой статьи.

BEGIN TRANSACTION — начать транзакцию;
COMMIT — принять изменения внесенные текущей транзакцией;
ROLLBACK — откат;

--Добавление новой строки в таблицу books. Принять изменения.
BEGIN TRANSACTION;
INSERT INTO books (title, author, YEAR, description) 
	VALUES ('Новая книга', 'Василий Ежиков', 2005, 'Книга о новом');
COMMIT WORK;
 
--Добавление новой строки в таблицу books. Удаление записи. Откатить все изменения.
BEGIN TRANSACTION;
INSERT INTO books (title, author, YEAR, description) 
	VALUES ('Новая книга', 'Василий Ежиков', 2005, 'Книга о новом');
DELETE FROM books WHERE id=3;
ROLLBACK WORK;

SELECT

SELECT *
FROM `table` AS `name`
LEFT JOIN `table2` AS `name2` ON `table`.`t2id` = `table2`.`id`
WHERE …
ORDER BY …

Обзор функций

математические функции

abs(x) - абсолютное значение;
ceil(x) - наименьшее целое, которое не меньше аргумента;
exp(x) - экспонента;
floor(x) - наибольшее целое, которое не больше аргумента;
ln(x) - натуральный логарифм;
power(x, y) - возводит x в степень y;
round(x [,y]) - округление x до y разрядов справа от десятичной точки. По умолчанию y равно 0;
sign(x) - возвращает -1 для отрицательных значений x и 1 для положительных;
sqrt(x) - квадратный корень;
trunc(x [,y]) - усекает x до у десятичных разрядов. Если у равно 0 (значение по умолчанию), то х усекается до целого числа. Если у меньше 0, от отбрасываются цифры слева от десятичной точки.

Тригонометрические функции работают с радианами:

acos(x) - арккосинус;
asin(x) - арксинус;
atan(x) - арктангенс;
cos(x) - косинус;
sin(x) - синус;
tan(x) - тангенс.

Работа со строками

ascii(string) - возвращает код первого символа, эта функция обратна функции CHR;
chr(x) - возвращает символ с номером х, в MySQL это функция char;
length(string) - возвращает длину строки;
lower(string) - понижает регистр букв;
upper(string) - повышает регистр букв;
ltrim(string1[, string2]) - удаляет слева из первой строки все символы встречающиеся во второй строке. Если вторая строка отсутствует, то удаляются пробелы. В MySQL второй аргумент не поддерживается;
rtrim(string1[, string2]) - аналогична функции ltrim, только удаление происходит справа;
trim(string) - удаляет пробелы с обоих концов строки;
lpad(string1, n[, string2]) - дополняет первую строку слева n символами из второй строки, при необходимости вторая строка дублируется. Если string2 не указана, то используется пробел;
rpad(string1, n[, string2]) - аналогична функции lpad, только присоединение происходит справа;
replace(string1, c1, c2) - заменяет все вхождения символа/подстроки c1 на c2. Для простого удаления всех вхождений c1, в качестве третьего аргумента надо указать пустую строку (''). В Oracle третий аргумент не обязателен, и по умолчанию равен пустой строке;
instr(string1, string2[, a][, b]) - возвращает b вхождение строки string2 в строке string1 начиная с позиции a. Если a отрицательно, то поиск происходит справа. По умолчанию a и b присваиваются значение 1. В MySQL последние два аргумента не поддерживаются. В PostgreSQL данной функции нет, однако ее реализация дана в документации, как раз для совместимости с Oracle;
substr(string, pos, len) - возвращает подстрку с позиции pos и длины len.

Пример:

UPDATE `table_name` 
	SET `field_name` = REPLACE(`field_name`, 'что_заменить', 'чем_заменить') 
		WHERE доп_условие_если_нужно;

Статистические

SQL COUNT

Функция SQL COUNT

Функция SQL COUNT возвращает количество записей в запросе. Синтаксис функции COUNT:

SELECT COUNT(expression) FROM TABLE WHERE predicates;

Функция COUNT принимает один из нескольких параметров:

* — означает то, что функция COUNT возвращает все записи в таблице;
column_name — функция COUNT возвращает количество записей конкретного столбца (только NOT NULL);
DISTINCT column_name — функция COUNT возвращает количество только разных записей конкретного столбца (только NOT NULL).

Примеры SQL COUNT

Напишем запрос SELECT COUNT, возвращающий количество записей в таблице users.

SELECT COUNT(*) FROM users;

Напишем запрос, возвращающий количество записей столбца User_ID в таблице users, дата которых равна «20.05.2012».

SELECT COUNT(User_ID) FROM users WHERE DATE = «20.05.2012»;

Теперь получим количество только разных записей столбца User_ID.

SELECT COUNT(DISTINCT User_ID) FROM users;

Функция SQL AVG

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

Рассмотрим пример. Допустим в таблице Price есть столбец Price_unit. В этой таблице содержатся 5 записей. Значения полей столбца Price_unit 3, 5, 14, 38 и 83. Выполним запрос, возвращающий среднее значение столбца Price_unit

SELECT AVG(Price_unit) AS PriceAvg FROM Price;

Результатом выполнения запроса будет

PriceAvg = 28,6