Инструменты пользователя

Инструменты сайта


db_sql

Назад

SQL — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. А SQL-запрос это команда(ы) для, соответственно создания, модификации и управления данными.

Типы запросов данных

  • SELECT – выбрать строки из таблиц;
  • INSERT – добавить строки в таблицу;
  • UPDATE – изменить строки в таблице;
  • DELETE – удалить строки в таблице;

Каждый из этих запросов имеет различные операторы и функции, которые используются для того, чтобы произвести какие-то действия с данными. Запрос SELECT имеет самое большое количество опций. Существуют также дополнительные типы запросов, используемых вместе с SELECT, типа JOIN и UNION.

SELECT

SELECT * FROM `userlist`

INSERT

INSERT INTO  `userlist` (`id_user` ,`name` ,`pass` ,`email`)VALUES ('4',  'Silas',  '123qwe',  'silas@yandex.ru')

UPDATE

UPDATE  `userlist` SET  `pass` =  '123asd', `email` =  'silas2000@yandex.ru' WHERE `id_user` =2

DELETE

DELETE FROM `userlist` WHERE `id_user` = 3 

WHERE

С помощью WHERE-параметра пользователь определяет, какие блоки данных из приведенных в списке FROM таблиц появятся в результате запроса. За ключевым словом WHERE следует перечень условий поиска, определяющих те строки, которые должны быть выбраны при выполнении запроса. Существует пять основных типов условий поиска (или предикатов):

  • Сравнение: сравниваются результаты вычисления одного выражения с результатами вычисления другого.
  • Диапазон: проверяется, попадает ли результат вычисления выражения в заданный диапазон значений.
  • Принадлежность множеству: проверяется, принадлежит ли результат вычислений выражения заданному множеству значений.
  • Соответствие шаблону: проверяется, отвечает ли некоторое строковое значение заданному шаблону.
  • Значение NULL: проверяется, содержит ли данный столбец определитель NULL (неизвестное значение).

Сравнение

В языке SQL можно использовать следующие операторы сравнения:

= равенство
<меньше
>больше
< =меньше или равно
>=больше или равно
<>не равно

Показать все операции отпуска товаров объемом больше 20

SELECT * FROM Сделка
    WHERE Количество>20
 

Более сложные предикаты могут быть построены с помощью логических операторов AND, OR или NOT, а также скобок, используемых для определения порядка вычисления выражения. Вычисление выражения в условиях выполняется по следующим правилам:

  * **AND** - И
  * **OR** - ИЛИ
  * **NOT** - НЕ

* Выражение вычисляется слева направо.
* Первыми вычисляются подвыражения в скобках.
* Операторы NOT выполняются до выполнения операторов AND и OR.
* Операторы AND выполняются до выполнения операторов OR.

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

Вывести список товаров, цена которых больше или равна 100 и меньше или равна 150.

SELECT Название, Цена
    FROM Товар
    WHERE Цена>=100 AND Цена<=150

Вывести список клиентов из Москвы или из Самары.

SELECT Фамилия, ГородКлиента
    FROM Клиент
    WHERE ГородКлиента="Москва" OR ГородКлиента="Самара"

Диапазон (BETWEEN)

Оператор BETWEEN используется для поиска значения внутри некоторого интервала, определяемого своими минимальным и максимальным значениями. При этом указанные значения включаются в условие поиска.

Вывести список товаров, цена которых лежит в диапазоне от 100 до 150

SELECT Название, Цена
    FROM Товар
    WHERE Цена BETWEEN 100 AND 150

При использовании отрицания NOT BETWEEN требуется, чтобы проверяемое значение лежало вне границ заданного диапазона.

Вывести список товаров, цена которых не лежит в диапазоне от 100 до 150.

SELECT Товар.Название, Товар.Цена
    FROM Товар
    WHERE Товар.Цена NOT BETWEEN 100 AND 150
 
Или (что эквивалентно)
 
SELECT Товар.Название, Товар.Цена
   FROM Товар
   WHERE (Товар.Цена<100) OR (Товар.Цена>150)

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

Проверка:

А BETWEEN В AND С 

полностью эквивалентна сравнению

(А >= В) AND (А <= C)

Принадлежность множеству

Оператор IN используется для сравнения некоторого значения со списком заданных значений, при этом проверяется, соответствует ли результат вычисления выражения одному из значений в предоставленном списке. При помощи оператора IN может быть достигнут тот же результат, что и в случае применения оператора OR, однако оператор IN выполняется быстрее.

Вывести список клиентов из Москвы или из Самары

SELECT Фамилия, ГородКлиента
   FROM Клиент
   WHERE ГородКлиента IN ("Москва", "Самара")

NOT IN используется для отбора любых значений, кроме тех, которые указаны в представленном списке.

Вывести список клиентов, проживающих не в Москве и не в Самаре.

SELECT Фамилия, ГородКлиента
    FROM Клиент 
    WHERE ГородКлиента 
	  NOT IN ("Москва","Самара")

Соответствие шаблону

С помощью оператора LIKE можно выполнять сравнение выражения с заданным шаблоном, в котором допускается использование символов-заменителей:

  • Символ % – вместо этого символа может быть подставлено любое количество произвольных символов.
  • Символ _ заменяет один символ строки.
  • [] – вместо символа строки будет подставлен один из возможных символов, указанный в этих ограничителях.
  • [^] – вместо соответствующего символа строки будут подставлены все символы, кроме указанных в ограничителях.

Найти клиентов, у которых в номере телефона вторая цифра – 4.

SELECT Клиент.Фамилия, Клиент.Телефон
    FROM Клиент
    WHERE Клиент.Телефон LIKE "_4%"

Найти клиентов, у которых в номере телефона вторая цифра – 2 или 4.

SELECT Клиент.Фамилия, Клиент.Телефон
    FROM Клиент
    WHERE Клиент.Телефон LIKE "_[2,4]%"

Найти клиентов, у которых в номере телефона вторая цифра 2, 3 или 4.

SELECT Клиент.Фамилия, Клиент.Телефон
    FROM Клиент
    WHERE Клиент.Телефон LIKE "_[2-4]%"

Найти клиентов, у которых в фамилии встречается слог «ро».

SELECT Клиент.Фамилия
    FROM Клиент
    WHERE Клиент.Фамилия LIKE "%ро%"

Значение NULL

Оператор IS NULL используется для сравнения текущего значения со значением NULL – специальным значением, указывающим на отсутствие любого значения. NULL – это не то же самое, что знак пробела (пробел – допустимый символ) или ноль (0 – допустимое число). NULL отличается и от строки нулевой длины (пустой строки).

Найти сотрудников, у которых нет телефона (поле Телефон не содержит никакого значения).

SELECT Фамилия, Телефон
    FROM Клиент 
    WHERE Телефон IS NULL

Выборка клиентов, у которых есть телефон (поле Телефон содержит какое-либо значение).

SELECT Клиент.Фамилия, Клиент.Телефон
    FROM Клиент
    WHERE Клиент.Телефон IS NOT NULL

Предложение ORDER BY

В общем случае строки в результирующей таблице SQL-запроса никак не упорядочены. Однако их можно требуемым образом отсортировать, для чего в оператор SELECT помещается фраза ORDER BY, которая сортирует данные выходного набора в заданной последовательности. Сортировка может выполняться по нескольким полям, в этом случае они перечисляются за ключевым словом ORDER BY через запятую. Способ сортировки задается ключевым словом, указываемым в рамках параметра ORDER BY следом за названием поля, по которому выполняется сортировка. По умолчанию реализуется сортировка по возрастанию. Явно она задается ключевым словом ASC.Для выполнения сортировки в обратной последовательности необходимо после имени поля, по которому она выполняется, указать ключевое слово DESC.Фраза ORDER BY позволяет упорядочить выбранные записи в порядке возрастания или убывания значений любого столбца или комбинации столбцов, независимо от того, присутствуют эти столбцы в таблице результата или нет. Фраза ORDER BY всегда должна быть последним элементом в операторе SELECT.

Вывести список клиентов в алфавитном порядке.

SELECT Клиент.Фамилия, Клиент.Фирма 
    FROM Клиент
    ORDER BY Клиент.Фамилия

Вывести список фирм и клиентов. Названия фирм упорядочить в алфавитном порядке, имена клиентов в каждой фирме отсортировать в обратном порядке.

SELECT Клиент.Фирма, Клиент.Фамилия 
    FROM Клиент
    ORDER BY Клиент.Фирма, Клиент.Фамилия DESC

DISTINCT

DISTINCT - получить уникальные строки (отличающиеся друг от друга любым отображаемым полем). Работает чуть быстрее.

SELECT DISTINCT column_name FROM TABLE_NAME

GROUP BY - сгруппировать по какому-либо признаку, при этом можно использовать агрегатные функции SUM, AVG, MAX и т.д.

Дано:

1
2
3
1
2
3

С помощью ORDER BY получим сортировку

1
1
2
2
3
3

С помощью DISTINCT уникальные значения

1
2
3

А с помощью GROUP BY можно посчитать кол-во вхождений

SELECT value, COUNT(*) FROM table_test
value COUNT(*)
1      2
2      2
3      2

GROUP BY

База для работы

GROUP BY — используется для объединения строк с общими значениями в элементы меньшего набора строк.

В группировки есть 2 вида полей:

  • Группирующие
  • Агрегирование (функции)

При группировки данных важно помнить что вывод данных зависит не только от SELECTA а и от GROUP BY так как в GROUP BY передается поля по каким группировать.

Пример:Вывести сотрудников по количеству детей

SELECT COUNT(*) AS [Сотрудники], p.kol  AS [Количество детей] 
FROM persona AS p
GROUP BY p.kol

WITH ROLLUP

Начиная с MySQL 4.1.1, конструкцию GROUP BY предусматривает модификатор WITH ROLLUP, который добавляет несколько дополнительных строк к итоговому выводу. Эти строки представляют итоговые операции высшего уровня (суперагрегатные).

Пример запроса:

SELECT REP, CUST , SUM(AMOUNT)
FROM ORDERS
GROUP BY REP, CUST WITH ROLLUP

HAVING

HAVING используется вместе с GROUP BY для того чтобы выбирать только определенные группы строк данных, которые удовлетворяют указанному условию.

Пример: Какова средняя стоимость заказа для каждого служащего из числа тех, у которых общая стоимость заказов превышает $30 ООО

SELECT REP, AVG (AMOUNT),SUM(AMOUNT)
FROM ORDERS
GROUP BY REP
HAVING SUM(AMOUNT) > 30000; 

HAVING исключает все группы, в которых общая стоимость заказа не превышает $30000. И наконец, предложение SELECT вычисляет среднюю стоимость заказа для каждой из оставшихся групп и генерирует таблицу результатов запроса.

На практике условие отбора предложения НAVING всегда должно включать в себя как минимум одну статистическую функцию. Если это не так, значит, условие отбора можно переместить в предложение WHERE и применить к отдельным строкам. Простейший способ выяснить, должно ли условие отбора находиться в предложении WHERE или НА VING, - вспомнить, как применяются эти предложения:

  • предложение WНERE применяется к отдельным строкам, поэтому выражения, содержащиеся в нем, должны быть вычислимы для отдельных строк;
  • предложение НAVING применяется к группам строк, поэтому выражения,содержащиеся в нем, должны быть вычислимы для групп строк.

Агрегатные функции

Агрегатные функции могут использоваться как в сочетании с предложением GROUP BY, так и без него.

  • COUNT - выдаёт количество строк или не-NULL значений полей, которые выбрал запрос.
  • SUM - выдаёт арифметическую сумму всех выбранных значений данного поля.
  • AVG - выдаёт усреднение всех выбранных значений данного поля.
  • MAX - выдаёт наибольшее из всех выбранных значений данного поля.
  • MIN - выдаёт наименьшее из всех выбранных значений данного поля.

получить сумму за каждый месяц

SELECT MONTH(k.dat) AS [Месяц], SUM( k.summa) AS [Сумма]
FROM  kassa AS  k
GROUP BY MONTH(k.dat) 

Вывести Фамилию Имя и сумму зарплаты за 10 месяц подчиненных Главный инженер

SELECT p.fam, s.dol,SUM( k.summa)
FROM  kassa AS  k ,struk AS s, persona AS p
WHERE  p.struk = s.id   AND   p.id = k.pers AND MONTH(k.dat)=10 AND s.p_id=101
GROUP BY p.fam, s.dol

Вывести сумму всех плотежей у кого оклад >4000

SELECT p.fam, SUM( k.summa) AS [Сумма выплат],COUNT(*) AS [количество платежей]
FROM  kassa AS  k ,struk AS s, persona AS p
WHERE  p.struk = s.id   AND   p.id = k.pers  AND  s.oklad>4000 
GROUP BY p.fam

Вывести сумму выплат и количество платежей больше 2-х и чтобы средняя сумма выплат была больше 1500

SELECT p.fam, SUM( k.summa) AS [Сумма выплат],COUNT(*) AS [количество платежей], MAX(k.summa) AS[Максимальный платеж], AVG(k.summa) AS [средняя сумма]
FROM  kassa AS  k , persona AS p
WHERE   p.id = k.pers
GROUP BY p.fam
 HAVING  AVG(k.summa)>1500 AND COUNT(*)>2

UNION

Оператор UNION позволяет объединять результаты нескольких инструкций SELECT в одну таблицу. У результирующих наборов, объединяемых с помощью оператора UNION, должна быть одинаковая структура. Они должны содержать одинаковое количество столбцов, а в соответствующих столбцах результирующих наборов должны быть совместимые типы данных.

(SELECT a,b
FROM tab)
 
UNION 
 
(SELECT x,y
FROM tab2)

Пример Вывести сумму выплаченных денег и не выплаченных денег

SELECT X.FAM AS [Фамилия], SUM(X.S) AS [Сумма не выплачиных денег], SUM(X.F) AS [Сумма выплаченых денег]
FROM
 
(SELECT P.FAM, SUM(K.SUMMA) AS S, 0 AS F
FROM PERSONA P, KASSA K
WHERE P.ID = K.PERS AND K.FLAG = 0
GROUP BY P.FAM
 
UNION
 
SELECT P.FAM, 0, SUM(K.SUMMA)
FROM PERSONA P, KASSA K
WHERE P.ID = K.PERS AND K.FLAG = 1
GROUP BY P.FAM) AS X
 
 
GROUP BY X.FAM

Подзапрос

Подзапросы, внутренние или вложенные запросы – есть не что иное, как запрос внутри запроса. Обычно, подзапрос используется в конструкции WHERE. И, в большинстве случаев, подзапрос используется, когда вы можете получить значение с помощью запроса, но не знаете конкретного результата.

Подзапросы могут применяется и в разделе SELECT

Показать среднею зарплату по фирме

SELECT x.dol, x.oklad,(SELECT avg(s.oklad) FROM struk s)
FROM struk x

Показать зарплату больше средней по фирме

SELECT x.dol, x.oklad,(SELECT avg(s.oklad) FROM struk s)
FROM struk x
WHERE x.oklad>(SELECT avg(s.oklad) FROM struk s)

Получаем месяц с максимальными количеством платежей

SELECT MONTH (k.dat),COUNT(*)
FROM  persona AS p, kassa AS k
WHERE p.id = k.pers 
GROUP BY MONTH (k.dat)
HAVING COUNT (*) = 
	(
		SELECT MAX(y)
		FROM 
			(
			SELECT  MONTH(dat) AS x, COUNT(*) AS y 
			FROM kassa
			GROUP BY MONTH (dat)
			)
	)

Join

Ключевое слово join в SQL используется при построении select выражений. Инструкция Join позволяет объединить колонки из нескольких таблиц в одну.

Для пояснений будут использоваться следующие таблицы:

Люди, проживающие в городах (таблица Person)

NameCityId
Андрей1
Леонид2
Сергей1
Григорий4

Города (таблица City)

Id Name
1 Москва
2 Санкт-Петербург
3 Казань

INNER JOIN

Объединяет две таблицы, где каждая строка обеих таблиц в точности соответствует условию. Если для строки одной таблицы не найдено соответствия в другой таблице, строка не включается в набор.

SELECT * 
FROM Person
INNER JOIN City ON Person.CityId = City.Id
Person.NamePerson.CityIdCity.IdCity.Name
Андрей 1 1 Москва
Леонид 2 2 Санкт-Петербург
Сергей 1 1 Москва

<note tip>Выбор по первичному ключу и индексу положительно сказывается на скорости выборки.</note>

LEFT OUTER JOIN

К левой таблице присоединяются все записи из правой, соответствующие условию (по правилам inner join), плюс все не вошедшие записи из левой таблицы, поля правой таблицы заполняются значениями NULL.

SELECT * 
FROM Person
LEFT OUTER JOIN City ON Person.CityId = City.Id
Person.NamePerson.CityIdCity.IdCity.Name
Андрей11Москва
Леонид 22Санкт-Петербург
Сергей 11Москва
Григорий4NULLNULL

RIGHT OUTER JOIN

Аналогично left outer join, но применяется для правой таблицы.
К правой таблице присоединяются все записи из левой, соответствующие условию (по правилам inner join), плюс все не вошедшие записи из правой таблицы, поля левой таблицы заполняются значениями NULL.

SELECT * 
FROM Person
RIGHT OUTER JOIN City ON Person.CityId = City.Id
Person.NamePerson.CityIdCity.IdCity.Name
Андрей11Москва
Сергей11Москва
Леонид22Санкт-Петербург
NULLNULL3Казань

FULL OUTER JOIN

К левой таблице присоединяются все записи из правой, соответствующие условию (по правилам inner join), плюс все не вошедшие записи из правой таблицы, поля левой таблицы заполняются значениями NULL и плюс все не вошедшие записи из левой таблицы, поля правой таблицы заполняются значениями NULL

SELECT * 
FROM Person
FULL OUTER JOIN City ON Person.CityId = City.Id
Person.Name Person.CityId City.Id City.Name
Андрей11Москва
Сергей 11Москва
Леонид22Санкт-Петербург
NULLNULL3Казань
Григорий4NULLNULL

USING

SELECT ORDER_NUM , AMOUNT , DESCRIPTION
FROM ORDERS JOIN PRODUCTS
USING (MFR, PRODUCT} ;

Эквивалент такой записи

SELECT ORDER_NUM, AMOUNT, DESCRI PTION
FROM ORDERS JOIN PRODUCTS
ON ORDERS.MFR = PRODUCTS.MFR
AND ORDERS.PRODUCT = PRODUCTS.PRODUCT; 

EXISTS

Оператор EXISTS берет под запрос, как аргумент, и оценивает его как верно и неверно.

Проверяем если город Москва в таблице City.Name если есть то выведет все поля с таблицы City иначе не чего не выведет.

SELECT * 
FROM  City AS s
WHERE  EXISTS (
      SELECT *
      FROM  City
      WHERE City.Name = 'Москва'
 )

Выражение CASE

Функция CASE проверяет истинность набора условий и в зависимости от результата проверки может возвращать тот или иной результат. Эта функция принимает следующую форму:

CASE
    WHEN условие_1 THEN результат_1
    WHEN условие_2 THEN результат_2
    .................................
    WHEN условие_N THEN услов

Возьмем для примера следующую таблицу Products:

CREATE TABLE Products
(
    Id INT AUTO_INCREMENT PRIMARY KEY,
    ProductName VARCHAR(30) NOT NULL,
    Manufacturer VARCHAR(20) NOT NULL,
    ProductCount INT DEFAULT 0,
    Price DECIMAL NOT NULL
);

Выполним запрос к этой таблице и используем функцию CASE:

SELECT ProductName, ProductCount, 
CASE
    WHEN ProductCount = 1 
        THEN 'Товар заканчивается'чивается'
    WHEN ProductCount = 2 
        THEN 'Мало товара'
    WHEN ProductCount = 3 
        THEN 'Есть в наличии'
    ELSE 'Мн

Выражение COALESCE

Функция COALESCE принимает список значений и возвращает первое из них, которое не равно NULL:

 COALESCE(выражение_1, выражение_2, выражение_3)

Например, выберем из таблицы Clients пользователей и в контактах у них определим либо телефон, либо электронный адрес, если они не равны NULL:

SELECT FirstName, LastName,
        COALESCE(Phone, Email, 'не определено') as Contact
FROM Clients;

То есть в данном случае возвращается телефон, если он определен. Если он не определен, то возвращается электронный адрес. Если и электронный адрес не определен, то возвращается строка «не определено».

Триггер

Понятие триггер является относительно простым. С любым событием, вызывающим изменение содержимого таблицы, пользователь может связать сопутствующее действие (триггер), которое СУБД должна выполнять при каждом возникновении события. Тремя такими событиями, запускающими триггеры, являются попытки изменить содержимое таблицы инструкциями INSERT, DELETE и UPDATE

delimiter //
DROP TRIGGER IF EXISTS `updateUser`//
CREATE TRIGGER `updateUser`
 
BEFORE UPDATE ON `t_user` FOR EACH ROW BEGIN 
--Если мы обнавили поле c_role_id удаляем все связи из таблицы t_role_access где c_user_id = old.c_id
IF new.c_role_id != old.c_role_id THEN BEGIN
DELETE FROM `t_role_access` WHERE `t_role_access`.`c_user_id` = old.c_id;
END;
END IF;
 
--Если мы обнавили поле c_role_id удаляем все связи из таблицы t_organization_request
IF new.c_role_id != old.c_role_id THEN BEGIN
DELETE FROM `t_organization_request` 
WHERE `t_organization_request`.`c_user_to_id`._user_from_id` = old.c_id 
OR `t_organization_request`.`c_user_to_id` = old.c_id;
END;
END IF;
 
END//
 
delimiter ;

Правила Удаления и обновления

RESTRICT

запрещает удаление строки из родительской таблицы, если строка имеет потомков. Инструкция DELETE, пытающаяся удалить такую строку, отвергается, и выдается сообщение об ошибке. Таким образом, из родительской таблицы можно удалять только строки, не имеющие потомков

CASCADE

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

SET NULL

определяет, что при удалении родительской строки внешним ключам во всех ее дочерних строках автоматически присваивается значение NULL.

SET DEFAULT

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