Нюансы
Меня часто спрашивают, а для кого эта статья? Но, поверьте, не всегда легко дать ответить: с одной стороны, есть ниндзя разработчики, которых сложно чем то удивить, а с другой - молодые падаваны. Но одно точно могу сказать - для читателя, которого интересует SQL, который способен дополнять свою богатую картину мелкими, но очень интересными деталями. В данной статье не будет километровых страниц sql-запроса, максимум 1, 2 строчки и только то, что встречается на мой взгляд редко. Но так как я хочу быть до конца откровенным, если Вы с sql на ты, статья покажется скучноватой. Все примеры в статье, за исключением первого и четвертого можно отнести к стандарту SQL-92.Данные
Для того, чтобы упростить нам жизнь, я накидал простую табличку с данными, на которой будут опробованы те или иные моменты и для краткости, я буду приводить результат эксперимента над ними. Все запросы я проверяю на PostgreSql.Скрипты и таблица с данными
CREATE TABLE goods(id bigint NOT NULL,
name character varying(127) NOT NULL,
description character varying(255) NOT NULL,
price numeric(16,2) NOT NULL,
articul character varying(20) NOT NULL,
act_time timestamp NOT NULL,
availability boolean NOT NULL,
CONSTRAINT pk_goods PRIMARY KEY (id));
INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (1, "Тапочки", "Мягкие", 100.00, "TR-75", {ts "2017-01-01 01:01:01.01"}, TRUE);
INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (2, "Подушка", "Белая", 200.00, "PR-75", {ts "2017-01-02 02:02:02.02"}, TRUE);
INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (3, "Одеяло", "Пуховое", 300.00, "ZR-75", {ts "2017-01-03 03:03:03.03"}, TRUE);
INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (4, "Наволочка", "Серая", 400.00, "AR-75", {ts "2017-01-04 04:04:04.04"}, FALSE);
INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (5, "Простынка", "Шелковая", 500.00, "BR-75", {ts "2017-01-05 05:05:05.05"}, FALSE);
Запросы
1. Двойные кавычки
И первое что у меня есть - это простой вопрос: Смогли бы Вы привести пример sql-запроса c использованием двойных кавычек? Да, не с одинарными, двойными?Пример с двойными кавычками
SELECT name "Имя товара" FROM goods
Я был очень удивлен, когда увидел это в первый раз. Если попробовать изменить двойные кавычки на одинарные, результат будет совершенно иной !
Может показаться, что это не очень полезный пример для реальной разработки. Для меня это не так. Теперь я его активно использую во всех своих sql-заготовках. Суть проста, когда возвращаешься через пол года к sql-запросу из 40 колонок, ой как спасает "нашенское" их название. Не смотря, что я не указал про SQL-92, в последней редакции упоминание про двойные кавычки имеются.
2. Псевдо таблица. SQL-92
Немного не точно, с точки зрения терминологии, но суть проста - таблица получающаяся в результате подзапроса в секции FROM. Пожалуй самый известный факт в этой статьеПсевдо таблица
SELECT mock.nickname "Прозвище", (CASE WHEN mock.huff THEN "Да" ELSE "Нет" END) "Обижается?" FROM (SELECT name AS nickname, availability AS huff FROM goods) mock
В нашем примере mock - это псевдо таблица (иногда называют виртуальной таблицей). Естественно, предназначены они вовсе не для того, чтобы переврать истинный смысл. Пример такой.
3. Конструктор блока данных. SQL-92
Звучит страшно, просто из-за того, что я не нашел хорошего перевода или интерпретации. И как всегда на примере легче объяснить:Пример конструктора блока данных
SELECT name "Имя товара", price "Цена" FROM (VALUES ("Тапочки", 100.00), ("Подушка", 200.00)) AS goods(name, price)
| Имя товара | Цена |
| Тапочки | 100.00 |
| Подушка | 200.00 |
В секции FROM используется ключевой слово VALUES , за которым в скобках данные, строка за строкой. Суть в том, что мы вообще не выбираем данные из какой-то таблицы, а просто создаем их налету, "называем" таблицей, именуем колонки и далее используем по своему усмотрению. Эта штука оказалось крайне полезной при тестировании разных кейсов sql-запроса, когда данных для некоторых таблиц нет (в Вашей локальной БД), а писать insert лень или иногда очень сложно, ввиду связанности таблиц и ограничений.
4. Время, Дата и Время-и-Дата
Наверное каждый сталкивался в запросах, с необходимостью указания времени, даты или даты-и-времени. Во многих СУБД поддерживаются литералы t, d и ts соответственно для работы с этими типами. Но проще объяснить на примере:Для литералов d и t все аналогично.Прошу прощение у читателя, что ввел в заблуждение, но все что сказано в пункте 4 не относится к языку SQL, а относится к возможностям предобработки запросов в JDBC.
5. Отрицание. SQL-92
Все мы знаем про оператор NOT , но очень часто забывают, что его можно применять как к группе предикатов так и к одиночной колонке:6. Сравнение блоков данных. SQL-92
В очередной раз прошу прощение за терминологию. Это один из любимых моих примеровПример сравнения блоков данных
SELECT * FROM goods WHERE (name, price, availability) = ("Наволочка", 400.00, FALSE) -- или его аналог SELECT * FROM goods WHERE name = "Наволочка" AND price = 400.00 AND availability = FALSE
Как видно из примера, сравнение блоков данных аналогично сравнению поэлементно значение_1 _block_1 = значение_1 _block_2, значение_2 _block_1 = значение_2 _block_2, значение_3 _block_1 = значение_3 _block_2 с использованием AND между ними.
7. Операторы сравнения с модификаторами ANY, SOME или ALL. SQL-92
Вот здесь требуется пояснение. Но как всегда, сначала примерЧто означает ALL в данном случае? А означает он то, что условию выборки удовлетворяют только те строки, идентификаторы которых (в нашем случае это 4 и 5), больше любого из найденных значений в подзапросе (1, 2 и 3). 4 больше чем 1 и чем 2 и чем 3. 5 аналогично. Что будет, если мы заменим ALL на ANY ?Что означает ANY в данном случае? А означает он то, что условию выборки удовлетворяют только те строки, идентификаторы которых (в нашем случае это 2, 3, 4 и 5), больше хотя бы одного из найденных значений в подзапросе (1, 2 и 3). Для себя я ассоциировал ALL с AND , а ANY с OR . SOME и ANY аналоги между собой.
8. Операторы работы с запросами/под запросами. SQL-92
Достаточно известно, что можно объединить 2 запроса между собой с помощью операторов UNION или UNION ALL . Этим пользуются часто. Но существуют еще 2 оператора EXCEPT и INTERSECT .Пример с EXCEPT
Собственно из первого множества значений исключаются данные второго множества.
Собственно происходит пересечение первого множества значений и второго множества.
На этом все, спасибо за Ваше внимание.
Редакция
N1. Спасибо streetflush за конструктивную критику. Внес статью информацию о том, что является стандартом языка, а что нет.N2. Исправлен пункт 4, с пояснение о том, что ts/d/t не являюься частью языка SQL. Спасибо за внимательность Melkij.
This SQL tutorial explains how to use the SQL INTERSECT operator with syntax and examples.
Description
The SQL INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
Intersect Query
Explanation: The INTERSECT query will return the records in the blue shaded area. These are the records that exist in both Dataset1 and Dataset2.
Each SQL statement within the SQL INTERSECT must have the same number of fields in the result sets with similar data types.
Syntax
The syntax for the INTERSECT operator in SQL is:
SELECT expression1, expression2, ... expression_n FROM tables INTERSECT SELECT expression1, expression2, ... expression_n FROM tables ;
Parameters or Arguments
expression1, expression2, expression_n The columns or calculations that you wish to retrieve. tables The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. WHERE conditions Optional. These are conditions that must be met for the records to be selected.Example - With Single Expression
The following is a SQL INTERSECT operator example that has one field with the same data type:
SELECT supplier_id FROM suppliers INTERSECT SELECT supplier_id FROM orders;
In this SQL INTERSECT example, if a supplier_id appeared in both the suppliers and orders table, it would appear in your result set.
Now, let"s complicate our example further by adding to the INTERSECT query.
SELECT supplier_id FROM suppliers WHERE supplier_id > 78 INTERSECT SELECT supplier_id FROM orders WHERE quantity <> 0;
In this example, the WHERE clauses have been added to each of the datasets. The first dataset has been filtered so that only records from the suppliers table where the supplier_id is greater than 78 are returned. The second dataset has been filtered so that only records from the orders quantity is not equal to 0.
Example - With Multiple Expressions
Next, let"s look at an example of how to use the INTERSECT operator in SQL to return more than one column.
SELECT contact_id, last_name, first_name FROM contacts WHERE last_name <> "Anderson" INTERSECT SELECT customer_id, last_name, first_name FROM customers WHERE customer_id < 50;
In this INTERSECT example, the query will return the records from the contacts table where the contact_id , last_name , and first_name values match the customer_id , last_name , and first_name value from the customers table.
There are WHERE conditions on each data set to further filter the results so that only records from the contacts are returned where the last_name is not Anderson . The records from the customers table are returned where the customer_id is less than 50.
Example - Using ORDER BY
SELECT supplier_id, supplier_name FROM suppliers WHERE supplier_id > 2000 INTERSECT SELECT company_id, company_name FROM companies WHERE company_id > 1000 ORDER BY 2;Since the column names are different between the two SELECT statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we"ve sorted the results by supplier_name / company_name in ascending order, as denoted by the ORDER BY 2 .
The supplier_name / company_name fields are in position #2 in the result set.
В этом учебном пособии вы узнаете, как использовать оператор EXCEPT в SQL Server
(Transact-SQL) с синтаксисом и примерами.
Описание
Оператор EXCEPT SQL Server (Transact-SQL) используется для возврата всех строк в первом операторе SELECT, которые не возвращаются вторым оператором SELECT. Каждая инструкция SELECT будет определять набор данных. Оператор EXCEPT будет извлекать все записи из первого набора данных, а затем удалять из результатов все записи из второго набора данных.
Запрос Except
Пояснение
: Запрос EXCEPT вернет записи в серой затененной области. Это записи, которые существуют в SELECT 1, а не в SELECT 2.
Каждый оператор SELECT в запросе EXCEPT должен иметь одинаковое количество полей в наборах результатов с похожими типами данных.
Синтаксис
Синтаксис оператора EXCEPT в SQL Server (Transact-SQL):
Параметры или аргументы
expressions
— столбцы или вычисления, которые вы хотите сравнить между двумя операторами SELECT. Они не должны быть одинаковыми полями в каждом из операторов SELECT, но соответствующие столбцы должны быть с похожими типами данных.
tables
— таблицы, из которых вы хотите получить записи. Должна быть хотя бы одна таблица, перечисленная в предложении FROM.
WHERE conditions
— необязательный. Условия, которые должны быть выполнены для выбранных записей.
Примечание
- В обоих операторах SELECT должно быть одинаковое количество выражений.
- Соответствующие столбцы в каждом из операторов SELECT должны иметь похожие типы данных.
- Оператор EXCEPT возвращает все записи из первого оператора SELECT, не входящего во второй оператор SELECT.
- Оператор EXCEPT в SQL Server эквивалентен оператору MINUS в Oracle.
Пример с одним выражением
Давайте рассмотрим пример оператора EXCEPT в SQL Server (Transact-SQL), который возвращает одно поле с тем же типом данных.
Например:
Transact-SQL
SELECT product_id FROM products EXCEPT SELECT product_id FROM inventory;
SELECT product_id FROM products SELECT product_id FROM inventory ; |
Этот пример оператора EXCEPT возвращает все значения product_id , которые находятся в таблице products , а не в таблице inventory . Это означает, что если значение product_id существует в таблице products и также существует в таблице inventory , значение product_id не будет отображаться в результатах запроса EXCEPT.
Пример с несколькими выражениями
Затем давайте рассмотрим пример запроса EXCEPT в SQL Server (Transact-SQL), который возвращает более одного столбца.
Например:
Transact-SQL
В этом примере запрос EXCEPT возвращает записи в таблице contacts с именем contact_id , last_name и first_name , которое не соответствует значению employee_id , last_name и first_name в таблице employees .
Как защитить электронную почту от спама?
На сегодняшний день спам является одним из главных источников распространения вирусов и вредоносного программного обеспечения. Каждый день, проверяя почту, мы сталкиваемся с рекламными, зазывными и прочими нежелательными письмами на своём почтовом ящике. Иногда они бывают настолько изощрёнными, что мы не задумываясь переходим по ссылке, выставленной в письме, хотя в большинстве случаев, чтобы вредоносное ПО попало на ваш компьютер достаточно просто открыть письмо.
Благодаря спаму, вы можете как потерять свои данные, заразить компьютер вирусами, так стать жертвой мошенников и потерять довольно крупные суммы денег.
Что же такое спам?
Спам — это массовая, неперсонифицированная, рассылка с использованием специальных программ, коммерческой, политической и иной рекламы или иного вида сообщений людям, не выразившим желания их получать.
Масса нежелательной корреспонденции - так называемый спам, становится большей и большей проблемой, связанной с электронными письмами. Особенно подвержены данной
проблеме владельцы адресов электронной почты, размещеных в сети, в частности, на веб-
сайтах и в статьях новостей.
Существуют специальные программы, предназначенные для сбора адресов из легко доступных источников, в целях их хранения в базах данных. Именно поэтому лучше
защитить свой адрес, представив его в такой форме, которая не может быть прочитана
программой. Кроме того, вы не должны предоставлять свой адрес электронной почты при
регистрации в различных интернет-сервисах. Для регистрации в таких сервисах можно
использовать другую учетную запись электронной почты, которая позже может стать
свалкой для спама.
На уровне домашнего ПК есть множество способов, как предотвратить получение тонны спам-писем:
1. Не заводите в интернете сомнительных знакомств. Некоторые личности, узнав Ваш адрес, будут подписывать его на всевозможные рассылки, и указать при регистрации на сайтах — просто так, из шалости (или вредности).
2. Прежде чем нажать какую-либо кнопку на сайте после ввода Вашего адреса (оставляя заказ в интернет-магазине, отправляя открытку и прочее), убедитесь, что нажатием этой кнопки Вы не выражаете свое согласие получать информацию от данного сайта. Иногда это написано мелким шрифтом в самой середине пользовательского соглашения или оформлено в виде малюсенькой галочки в дальнем углу страницы.
3. Не оставляйте Ваш адрес на сайтах в интернете(работа, гостевые книги, форумы, знакомства и прочее — там где Ваше сообщение с адресом может увидеть кто угодно).
4. Если же Вам необходимо это сделать — заведите отдельный, специальный ящик для этой цели и будьте готовы получать в него не только запрошенную информацию, но и горы спама.
5.Если же ваш адрес где-то засветился, или его вычислили путем подбора, и он попал в списки спаммеров, нужно бороться с тем спамом, который пытается прорваться в почтовый ящик. Практически на любом почтовом сервере или в почтовой программе есть возможность предварительно фильтровать письма, идущие к Вам в ящик, и отказываться от их получения.
Такой настройкой может быть:
— отказ получать письма, в полях Кому или Копия которых нет Вашего адреса;
— отказ получать письма, в теме которых нет кодового слова, придуманного Вами и сообщенного всем Вашим корреспондентам;
— согласие получать письма только с определенных адресов Белый список и т.д.
Защита электронной потчы компании
Сложнее, когда дело идёт о почте компании, независимо от количества её сотрудников. Без комплексного подхода к решению проблемы тут не обойтись.
Компания «Инфобезпека» предлагает следующие решения по защите электронной почты компании:
|
Еще один плагин для защиты вашего блога WordPress от спама в комментариях. На самом деле, про этот плагин и говорить много не нужно, вы его просто устанавливаете, активируете и все, забываете о нем.
