пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅ пїЅпїЅпїЅпїЅ
По вашему запросу ничего не найдено :(
Убедитесь, что запрос написан правильно, или посмотрите другие
наши статьи:
Написать SQL-код с несколькими условиями может быть не так просто, особенно если вам нужно выполнить целый ряд проверок.
Например, выражение альтернативы
if () else if () else {}
обрабатывает все условные операторы SQL. Если первое условие выполняется, то запрос прекращает свое выполнение и возвращает значение. Значение, которое было указано в
else
, возвращается только в случае, если ни одно условие не выполнилось.
В этой статье мы с вами рассмотрим:
Что такое оператор SQL CASE и как он работает
Как решить задачу с помощью оператора SQL CASE
Что означают такие немаловажные термины, такие как ORDER BY, LIMIT, OFFSET, LEFT JOIN и ALIAS.
Что такое оператор SQL CASE?
В программировании при заданном наборе условий для того, чтобы узнать, какой блок кода необходимо выполнить, вы используете условные операторы (
switch
или
if else
).
В SQL это можно сделать с помощью оператора CASE. Для того, чтобы выполнить блок кода условного оператора, ключевое слово CASE нужно использовать вместе с оператором WHEN. Чтобы вернуть результат выражения, вам понадобиться также оператор THEN. Если не выполняется ни одно из условий, то в игру вступает последний оператор ELSE для того, чтобы вернуть резервный результат.
Оператор SQL CASE имеет следующий синтаксис:
CASE
WHEN conditional_statement1 THEN result1
.
.
.
.
WHEN condition_statementN THEN resultN
ELSE result
END;
Синтаксис оператора SQL CASE
Когда вы используете оператор CASE, следующим должен быть WHEN, затем THEN и результат, который возвращается при выполнении первого условия. Если первое условие не выполняется, то проверяются следующие условия вплоть до n-го (или последнего) условия. Если они также не выполняются, то выполняется условие ELSE.
При этом здесь конструкция ELSE не является обязательной. В тех случаях, когда его нет, результат запроса просто возвращает NULL.
Задача
Здесь мы рассмотрим реальный пример, чтобы помочь вам научится решать задачи в SQL с помощью оператора CASE.
С этой задачей я столкнулся на Coderbyte. Это платформа для отработки задач по программированию. Ее было не так просто решить, и сейчас я разберу решение этой задачи пошагово.
В чем заключается задача?
Задача заключается в следующем: необходимо придумать такой SQL-запрос, который бы вернул из таблицы строку сотрудника с третьей по величине заработной платой.
Вам нужно построить запрос таким образом, чтобы найти этого сотрудника и вернуть его строку. Также вы должны заменить значение DivisionID соответствующим значением DivisionName из таблицы company_divisions. Затем, если значение ID есть в таблице и не равен NULL, то вам нужно заменить это значение на значение ManagerName.
Какую проблему решает оператор SQL CASE в этой задаче?
В этой задаче мы будем использовать оператор CASE для того, чтобы выполнить следующее:
Проверить, что ManagerID не равен NULL
Сопоставить ManagerID из таблицы company и ID из таблицы company и вернуть Name в качестве значения ManagerName
Сделать так, чтобы, если Name не возвращается, то в качестве значения ManagerName по умолчанию возвращается имя Susan Wall
Вот, что должно получиться:
ID
Name
DivisionName
ManagerName
Salary
222
Mark Red
Sales
Susan Wall
86000
А ниже представлены данные, которые вам нужны для решения этой задачи:
Таблица 1: company
ID
Name
DivisionID
ManagerID
Salary
356
Daniel smith
100
133
40000
122
Arnold Sully
101
null
60000
467
Lisa Roberts
100
null
80000
112
Mary Dial
105
467
65000
775
Dennis Front
103
null
90000
111
Larry Weis
104
35534
75000
222
Mark Red
102
133
86000
577
Robert Niger
105
12353
76000
133
Susan Wall
105
577
110000
Таблица 2: company_divisions
ID
DivisionName
100
Accounting
101
IT
102
Sales
103
Marketing
104
Engineering
105
Customer Support
Как решить задачу с помощью оператора SQL CASE?
Здесь мы рассмотрим пошаговый процесс решения этой задачи.
Шаг 1: получение строки сотрудника с третьей по величине заработной платой
Для начала нам нужно построить запрос, который вернет строку сотрудника с третьей по величине заработной платой. Вы можете сделать это следующим образом: упорядочить таблицу company по значениям заработной платы (так как именно это значение нас интересует) и выбрать третью строку сверху.
Выглядеть это будет примерно так:
SELECT *
FROM company
ORDER BY salary DESC limit 1 offset 2;
Выбор строки с третьим по величине значением заработной платы
Как и ожидалось, запрос вернул строку сотрудника с третьей по величине заработной платой.
ID
Name
DivisionID
ManagerID
Salary
222
Mark Red
102
133
86000
Итак, что же творится в этом запросе?
SELECT
: команда SELECT со звездочкой (*) (также известной как подстановочный знак) извлекает все столбцы из таблицы company.
ORDER BY
: команда ORDER BY упорядочивает столбцы в порядке возрастания или убывания. По умолчанию SQL упорядочивает по возрастанию (ASC), но мы упорядочим столбец заработной платы по убыванию (DESC). Мы делаем именно так, поскольку нам нужен убывающий список заработных плат, от самой высокой до самой низкой, то есть от 110 000 до 40 000.
limit
: команда limit ограничивает количество записей, которые будут возвращены, с помощью установленного значения. Так как нас интересует строка конкретного сотрудника, то мы ограничим вывод одной строкой и установим значение равное 1. Это гарантирует нам, что при каждом выполнении запроса мы будем получать только одну запись.
offset
: оператор offset помогает нам указать количество строк, которые необходимо пропустить, прежде чем возвращать строки из запроса. С помощью offset мы можем пропустить две строки с самыми высокими заработными платами (Susan Wall и Dennis Front) и вернуть строку с третьей по величине заработной платой (Mark Red).
Шаг 2: замена DivisionID на DivisionName
Теперь нам нужно изменить запрос так, чтобы он выбирал только нужные нам столбцы – ID, Name, ManagerID, DivisionName и Salary. И затем заменить столбец Division ID соответствующим столбцом DivisionName из таблицы company_divisions.
Вы можете сделать это так:
SELECT c.ID, c.Name, c.ManagerID, c.salary, cd.DivisionName
FROM company as c
LEFT JOIN company_divisions as cd ON c.DivisionId = cd.id
ORDER BY salary DESC limit 1 offset 2;
Выбор столбцов и объединение с таблицей company_division
Вот результат:
ID
Name
DivisionName
ManagerID
Salary
222
Mark Red
Sales
133
86000
Давайте обсудим, что же творится в запросе выше:
LEFT JOIN
: так как записи возвращаются с левой стороны (company), то мы сопоставляем их с правой стороны (company_division) с помощью
company_division.id
и
company.DivisionID
.
В случае, если была обнаружена совпадающая запись, то есть если ID из таблицы company также есть и в таблице company_division, то столбец DivisionName заполнятся фактическим значение из левого соединения (в нашем случае это Sales). Если записи нет, то ничего не возвращается.
as
(alias): alias используется для задания временного псевдонима таблицы. Таким образом, с учетом того, что у таблицы company есть псевдоним (c), вместо company.name мы можем использовать c.name. Использование таких псевдонимов помогает улучшить читаемость кода.
Шаг 3: Замена ManagerID на ManagerName
За основу возьмем результат запроса из шага 2. Мы будем использовать оператор CASE, который мы уже изучили, для того, чтобы добавить условия: равен ли ManagerID NULL и существует ли ManagerID.
В первую очередь нам нужно проверить, не является ли значение company.ManagerID
нулевым, и убедиться, что ID существует в таблице. Для этого мы применим оператор CASE.
CASE WHEN c.ManagerID IS NOT NULL
AND c.ManagerID = c.ID
Первый шаг оператора CASE
Вторая часть оператора CASE нам нужна для того, чтобы заменить столбец ManagerID на столбец ManagerName. Для этой цели мы будем использовать блок THEN, который мы уже изучили ранее:
CASE WHEN c.ManagerID IS NOT NULL
AND c.ManagerID = c.ID
THEN Name ELSE 'Susan Wall' END AS 'ManagerName'
Готовый оператор CASE для решения задачи
И наконец, мы должны добавить блок CASE в уже имеющийся фрагмент кода, который у нас получился на шаге 2. И теперь он будет выглядеть примерно так:
SELECT c.ID, c.Name, c.salary, cd.DivisionName
CASE WHEN c.ManagerID IS NOT NULL
AND c.ManagerID = c.ID
THEN Name ELSE 'Susan Wall' END AS 'ManagerName'
FROM company as c
LEFT JOIN company_divisions as cd ON c.DivisionId = cd.id
ORDER BY salary DESC limit 1 offset 2;
Оператор SQL CASE вместе с операторами SELECT и JOIN
Результат шага 3 – это то, что мы и хотели получить, а именно строку сотрудника с третьей по величине заработной платой.
ID
Name
DivisionName
ManagerName
Salary
222
Mark Red
Sales
Susan Wall
86000
Подводим итоги
Я надеюсь, что эта статья помогла понять, что такое оператор SQL CASE и как можно с его помощью решать реальные задачи.
Также мы рассмотрели и другие команды SQL, такие как SELECT, ORDER BY, LIMIT, OFFSET, LEFT JOIN и ALIAS.
Рассказываем как быстро и просто поднять свой NFS сервер на Ubuntu Linux Server 14-04.1, а также разберёмся с принципами работы протокола NFS и рассмотрим теорию.
Теория
Аббревиатура NFS расшифровывается как Need for Speed - Network File System. Это протокол для доступа к распределённым сетевым файловым системам, с помощью которого можно подмонтировать удалённые директории к своему серверу. Это позволяет использовать дисковое пространство другого сервера для хранения файлов и регулярно производить запись данных на него с нескольких серверов.
Протокол имеет клиент-серверную модель, то есть один сервер (ещё его называют “шара” от слова share), с установленным пакетом NFS, будет обеспечивать доступ к своим каталогам и файлам, а клиентские компьютеры будут подключаться к нему по сети. Закрепим прочитанное схемкой:
Обращения к серверу NFS осуществляются в виде пакетов протокола RPC (Remote Call Procedure), который позволяет выполнить различные функции или процедуры в другом сетевом пространстве, то есть на удалённом сервере.
Авторизация пользователей, которые подключаются к серверу осуществляется по IP-адресу, а также по специальным идентификаторам пользователя UID и группы GID. Это не лучший способ относительно безопасности хранимых файлов, в сравнении с классической моделью «логин/пароль». Зато, благодаря такой архитектуре и тому, что NFS использовал протокол UDP без установления сессии, он практически невосприимчив к сбоям сети и самих клиентских компьютеров. Так, при каком-либо сбое, передача файла просто приостановится, а когда связь будет налажена, то передача возобновиться без необходимости какой-либо перенастройки.
Настройка
Думаю, с теорией понятно, так что давайте переходить к практике. Как было сказано, все настройки будет проводить на Ubuntu 14.04.1
Прежде всего, на компьютер, который будет выступать в роли сервера NFS, нужно установить необходимые компоненты.
Итак, скачиваем пакет nfs-kernel-server, с помощью которого мы сможем раздать доступ (“расшарить”) директории. Для этого на будущем NFS сервере вводим команды:
sudo apt-get update
sudo apt-get install nfs-kernel-server
Теперь создаём собственно директорию к которой хотим раздать доступ. Стоит отметить, что можно также “расшарить” уже имеющиеся на сервере директории, но мы создадим новую:
sudo mkdir /var/nfs
Далее мы должны сделать так, чтобы владельцем директории /var/nfs и группе, к которой он принадлежит стали все пользователи в нашей системе. Для этого вводим на сервере команду:
sudo chown nobody:nogroup /var/nfs
Вводите эту команду только для тех директорий, которые создали сами, не надо вводить её для уже имеющихся директорий, например /home .
Следующим шагом необходимо изменить конфигурацию самого NFS, она лежит в файле /etc/exports, открываем его для редактирования любимым редактором:
sudo nano /etc/exports
Перед вами откроется конфигурационный файл с закомментированными строками, которые содержат примеры настройки для разных версий NFS.
Закомментированные – это те, в начале которых стоит символ #, и это значит, что параметры, указанные в них, не имеют силы.
Нам необходимо внести в этот файл следующие не закомментированные строки:
/var/nfs 10.10.0.10/24(rw,sync,no_subtree_check)
Где:
/var/nfs - Директория, которую мы хотим расшарить
10.10.0.10 - IP-адрес и маска клиентского компьютера, которому нужно раздать доступ к директории
rw - Разрешает клиенту читать (r) и записывать (w) файлы в директории
sync - Этот параметр заставляет NFS записывать изменения на диск перед ответом клиенту.
no_subtree_check - Данная опция отключает проверку того, что пользователь обращается именно к файлу в определённом подкаталоге. Если это проверка включена, то могут возникнуть проблемы, когда, например, название файла или подкаталога было изменено и пользователь попробует к ним обратиться.
После этого, нужно создать таблицу соответствия расшаренных директорий и клиентов, а затем запустить NFS сервис. Для этого вводим следующие команды:
sudo exportfs –a
sudo service nfs-kernel-server start
После выполненных действий расшаренные директории должны стать доступными для доступа с клиентов.
В продолжение нашей статьи про настройку Netflow на маршрутизаторах Mikrotik, сегодня мы расскажем про Ntopng — приложение, которое анализирует трафик в вашей сети. Устанавливать будем на CentOS 7.
Установка
Ntopng не доступен в дефолтных репозиториях CentOS 7, поэтому предварительно нам нужно будет выполнить определенные действия по их добавлению. Сперва, выполните команду по добавлению EPEL репозитория:
sudo yum install epel-release
Затем необходимо создать ntop репозиторий. Для этого нужно будет создать файл ntop.repo внутри директории /etc/yum.repos.d - для этого введите команду sudo nano /etc/yum.repos.d/ntop.repo.
В данный файл добавьте следующие строки:
[ntop]
name=ntop packages
baseurl=http://www.nmon.net/centos-stable/$releasever/$basearch/
enabled=1
gpgcheck=1
gpgkey=http://www.nmon.net/centos-stable/RPM-GPG-KEY-deri
[ntop-noarch]
name=ntop packages
baseurl=http://www.nmon.net/centos-stable/$releasever/noarch/
enabled=1
gpgcheck=1
gpgkey=http://www.nmon.net/centos-stable/RPM-GPG-KEY-deri
Для создания файла, конечно же, можно использовать любой текстовый редактор — не только nano. Но если хотите дотошно следовать инструкции, то, вероятно, сначала текстовый редактор придется установить с помощью команды yum install nano -y. После добавления нужных строк в файл сохраните изменения с помощью сочетания клавиш CTRL+O, и выйдите из файла командой CTRL+X.
Теперь переходим к непосредственно установке — выполните команду
sudo yum --enablerepo=epel install redis ntopng -y
. После этого просто соглашайтесь со всеми пунктами, и, спустя минут 5, все должно быть установлено.
Запуск сервисов и настройка ntopng
После установки ntopng, необходимо установить hiredis-devel пакет и запустить redis сервер до старта ntopng:
sudo yum --enablerepo=epel install hiredis-devel
Затем запускаем redis сервис и разрешаем ему автозапуск — и тоже самое делаем с ntopng.
sudo systemctl start redis.service
sudo systemctl enable redis.service
sudo systemctl start ntopng.service
sudo systemctl enable ntopng.service
Далее, проверим, работает ли ntopng командой sudo systemctl status ntopng.
Затем, превратим наш ntopng в бесплатную версию — для этого нужно отредактировать конфиг командой sudo nano /etc/ntopng/ntopng.conf и изменить строку:
-G=/var/tmp/ntopng.pid
на строку:
-G=/var/tmp/ntopng.pid
--community
После чего, сохраним и выйдем из файла и перезапустим ntopng:
sudo systemctl restart ntopng
Последний шаг — настроим фаерволл и перезагрузим его. Настройка заключается в разрешении порта 3000.
sudo firewall-cmd --permanent --add-port=3000/tcp
sudo firewall-cmd --reload
Первый запуск ntopng
Теперь осталось перейти по следующему адресу: http://yourhostip:3000. Логин и пароль по умолчанию — admin. Сразу после этого вам предложат изменить пароль.
Далее, вы увидите дэшборд, с разнообразной информацией, примерно как на скриншоте ниже:
Для понимания возможностей данного приложения — попробуйте посмотреть хосты, сети и прочие — в общем, попробуйте освоиться с функционалом.
Заключение
Всем спасибо за внимание, многие крупные вендоры сейчас уделяют особое внимание протоколу Netflow и придумывают различные сценарии применения. Как это может быть полезно именно для вас, пара примеров: после недельного анализа вашей сети вы поймете, что она была недостаточно сегментирована, или увидите какие-то подозрительные потоки. Дайте нам знать, если вам интересна более подробная настройка ntopng и софта, подобного ему — обязательно напишем про это статью! :).