🖥 Как проектировать запросы на изменение данных sql

При проектировании запросов на изменение данных в SQL необходимо следовать нескольким рекомендациям:

Сделайте резервную копию данных перед выполнением запроса на изменение данных. Это поможет избежать потери данных в случае ошибки.

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

Используйте оператор SET для задания новых значений столбцов в таблице.

Например:

UPDATE orders
SET order_status = 'Completed'
WHERE order_id = 12345;

Здесь мы обновляем столбец order_status таблицы orders, устанавливая значение Completed для заказа с order_id равным 12345.

Обратите внимание на условие WHERE, которое определяет, какие строки будут изменены. Если условие не указано, запрос обновит все строки в таблице.

Если вы хотите изменить несколько столбцов в таблице, используйте оператор SET для каждого столбца, например:

UPDATE orders
SET order_status = 'Completed',
order_total = 100.00
WHERE order_id = 12345;
Здесь мы обновляем значения столбцов order_status и order_total таблицы orders для заказа с order_id равным 12345.

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

@sqlhub

🖥 Большая шпарглака по psql

Полезные советы с примерами.

▪Шпаргалка

@sqlhub

🖥 Задача: Выбрать самую актуальную запись с учетом статуса (успешная / отмененная транзакция) и временной метки

Задачу часто спрашивают на собеседованиях в формулировке “как удалить дубли / копии строк”, и решить ее можно несколькими способами. Я привык мыслить в терминах историзации данных в Хранилище, и удаление мне ни к чему, поэтому для решения задачи я воспользуюсь ранжирующей функцией ROWNUMBER().

SQL Fiddle (Oracle 11g R2): http://sqlfiddle.com/#!4/ad305/1

with decoded as (
select
"transaction_id"
,"is_successful"
,"ts"
,decode("is_successful", 'true', 0, 'false', 1, 2) as "order_is_successful"
from transactions
),
ordered as (
select
"transaction_id"
,"is_successful"
,"ts"
,row_number() over(partition by "transaction_id" order by "order_is_successful" asc, "ts" desc) as rn
from decoded
)
select
"transaction_id"
,"is_successful"
,"ts"
from ordered
where rn = 1
;

Результат

Пишите свое решение в комментариях👇

@sqlhub

🖥 Изучаем базы данных

Большой сборник теоретического материала по работе с базами данных.

▪основы: http://phpclub.ru/mysql/doc/tutorial.html

▪отношения между таблицами в БД: http://jtest.ru/bazyi-dannyix/sql-dlya-nachinayushhix-chast-3.html

▪внешние ключи: http://denis.in.ua/foreign-keys-in-mysql.htm

▪большой учебник по SQL: http://www.pyramidin.narod.ru/rusql/index.htm

▪сборник запросов на все случаи жизни (англ): http://www.artfulsoftware.com/infotree/queries.php

▪таблицы отличий в диалектах SQL в разных СУБД (англ): http://en.wikibooks.org/wiki/SQL_dialects_reference

▪манга-учебник про SQL в картинках: http://www.nostarch.com/mg_databases.htm

👉 делитесь с коллегами полезным материалом

@sqlhub

🖥 Подстановочные знаки (wildcards) в SQL

Подстановочный знак (wildcard) в SQL используется с оператором LIKE для замены одного или набора символов в строке. Например:

SELECT *

FROM Customers

WHERE last_name LIKE 'R%';

Здесь % (означает ноль или более символов) является подстановочным знаком (wildcard). Мы выбираем клиентов, чья фамилия (last_name) начинается с буквы R, за которой следует ноль или более символов.

▪Читать дальше

@sqlhub

🖥 Аналитические функции при сохранении всех строк выборки

Речь пойдет о так называемых analytic functions, которые оперируют над партициями данных (окна, windows), возвращая результат для каждой строки. В отличие от aggregate functions, “схлопывающих” строки, оконные функции оставляют все строки выборки.

Окно определяется спецификацией (выражение OVER) и основывается на трех основных концепциях:

▪Разбиение строк на группы (выражение PARTITION BY)

▪Порядок сортировки строк в каждой группе (выражение ORDER BY)

▪Рамки, которые определяют ограничения по количеству строк относительно каждой строки (выражение ROWS)

Таких функций существует немало, от аналитических: всем известные SUM, AVG, COUNT, менее известные LAG, LEAD, CUMEDIST, и до ранжирующих: RANK, ROWNUMBER, NTILE. Я же приведу несколько простых примеров часто встречающихся запросов:

▪Ко всем транзакциям пользователя вывести дату первой покупки

▪К каждой транзакции добавить дату предыдущей транзакции пользователя

▪Показать сумму покупок пользователя нарастающим итогом

▪Присвоить всем транзакциям пользователя / продавца / отделения порядковый номер

SQL Fiddle: http://sqlfiddle.com/#!17/ee00f/13

Решения:

select
salesid
,dateid
,sellerid
,buyerid
,qty
,first_value(dateid) over (partition by buyerid order by dateid) as first_purchase_dt
,lag(dateid) over (partition by buyerid order by dateid) as previous_purchase_dt
,sum(qty) over (partition by buyerid order by dateid rows between unbounded preceding and current row) as moving_qty
,row_number() over (partition by buyerid order by dateid) as order_number
from winsales
;

Пишите свое решение в комментариях👇

@sqlhub

🖥 Полезная шпаргалка по связке Python + MySQL

▪Создание БД и таблиц
▪Внесение, чтение, обновление и удаление данных

#doc #python #sql #mysql

@sqlhub

🖥 Ограничение DEFAULT в SQL

Ограничение DEFAULT используется для установки значений по умолчанию при попытке вставить пустое (NULL) значение в столбец. Например:

CREATE TABLE Colleges (
college_id INT PRIMARY KEY,
college_code VARCHAR(20),
college_country VARCHAR(20) DEFAULT 'Japan'
);

Здесь значением по умолчанию для столбца college_country является Japan.

Если мы попытаемся сохранить значение NULL в столбце college_country, то значением станет Japan. Например:

-- Вставляем значение 'Japan' в столбец college_country
INSERT INTO Colleges (college_id, college_code)
VALUES (1, 'ARP76');

-- Вставляем значение 'UAE' в столбец college_country
INSERT INTO Colleges (college_id, college_code, college_country)
VALUES (2, 'JWS89', 'UAE');

Ограничение DEFAULT с ALTER TABLE
Мы также можем добавить ограничение DEFAULT к существующему столбцу с помощью оператора ALTER TABLE. Например:

SQL Server

ALTER TABLE Colleges
ADD CONSTRAINT country_default
DEFAULT 'Japan' FOR college_country;
PostgreSQL

ALTER TABLE Colleges
ALTER COLUMN college_code SET DEFAULT 'Japan';
MySQL

ALTER TABLE Colleges
ALTER college_country SET DEFAULT 'Japan';
Oracle

ALTER TABLE Colleges
MODIFY college_country DEFAULT 'Japan';

Здесь значением по умолчанию для столбца college_country является Japan, если кто-то попытается вставить NULL.

Удалить ограничение DEFAULT

Мы можем удалить ограничение DEFAULT, используя оператор DROP. Например:

SQL Server, PostgreSQL, Oracle

ALTER TABLE Colleges
ALTER COLUMN college_country DROP DEFAULT;
MySQL

ALTER TABLE Colleges
ALTER college_country DROP DEFAULT;

Здесь мы удаляем ограничение DEFAULT из столбца college_country.

@sqlhub

🖥 SQL Server Tools

Полезные инструменты разработки и администрирования для Microsoft SQL Server

1. Common-Info
Cкрипты общего назначения для просмотра состояния и настроек инстанса и др

2. Data-Model-Info
Просмотр модели данных и других связанных объектов

3. Databases-Info
Информация о базах данных

4. Statistics
Информация о статистиках, вопросах производительности и их анализе

5. Indexes
Информация об индексах, вопросах производительности и их анализе

6. File-Groups
Информация о файловых группах

7. Partitioned-Tables-and-Indexes
Секционирование таблиц и индексов

8. Perfomance
Производительность и оптимизация запросов, настроек сервера и поиск узких мест

9. Backup
Резервное копирование и восстановление данных

10. Maintenance
Обслуживание баз данных и сервера

11. 1С-Extended-Settings
Инструмент для поддержки произвольных индексов, изменение существующих объектов, сжатия таблиц и индексов, файловых групп и прочего для баз 1С:Предприятия

12. BCP
Работа с утилитой Bulk Insert Programm (BCP)

13. Bulk-Operations
Раздел с полезной информацией об операциях массовой модицикации данных

14. AlwaysOn
О работе с группами высокой доступности AlwaysOn, настройке WSFC и др.

15. Diagnostics
Диагностика работы SQL Server

16. FullText-Search
Полнотекстовый поиск и все что с ним связано

17. TempDB
Все что связано с TempDB и временными таблицами

18. Monitoring
Мониторинг стабильности и производительности

19. Internals
Информация о внутренностях работы СУБД

20. SSAS
Подраздел об SQL Server Analysis Services

21. PowerShell
Работа со SQL Server из PowerShell

22. Linux
Работа со SQL Server под Linux

23. Integration
Информация о средствах интеграции SQL Server с другими системами и наоборот.

24. Jobs
Информация о заданиях и их настройке.

25. Trace-Flags
Флаги трассировки.

26. Replication-And-High-Availability
Информация о механизмах репликации данных, высокой доступности и отказоустойчивости.

27. Track-Data-Changes
Механизмы отслеживания изменения данных.

28. Security
Безопасность, управление доступом и связанные темы.

29. SQLCLR
Материалы по созданию расширений для SQL Server на базе SQLCLR

@sqlhub

🧰 ts-sql-query — инструмент для построения типобезопасных SQL-запросов. Это что-то вроде QueryDSL/JOOQ в Java или LINQ в .NET, только для TypeScript с поддержкой MariaDB, MySql, Oracle, PostgreSql, Sqlite и SqlServer. 

🔗 Документация & Репозиторий

🖥 Полезные инструменты для защиты от SQL и NoSQL инъекций

1.SuIP.biz
Обнаружение уязвимости для SQL-инъекций в режиме онлайн с помощью sup.biz и поддержка баз данных MySQL, Oracle, PostgreSQL, Microsoft SQL, IBM DB2, Firebird, Sybase.

SQLMap поможет протестировать сервис на все 6 методов инъекции.

2.Тест на уязвимость SQL-инъекции онлайн c Hacker Target
Еще один онлайн-инструмент Hacker Target на основе SQLMap для поиска уязвимости bind & error против GET-запроса HTTP.

3. Netsparker
Netsparker готов просканировать уровень веб-безопасности предприятий: он делает даже больше, чем просто тест на уязвимость SQL. Человек также может интегрировать приложения для автоматизации веб-безопасности.

Пользователь может проверить индекс уязвимости сайта, который прошел сканирование от Netsparker.

4. Vega
Vega – это сканер безопасности с открытым исходным кодом, который может быть установлен на Linux, OS X и Windows.

Vega написан на Java, он имеет графический интерфейс.

Не только SQLi: Vega можно использовать для тестирования на многие другие типы уязвимостей, такие как:

Инъекция XML/Shell/URL;
Directory listing;
Remote file includes;
XSS.
Vega выглядит многообещающим бесплатным сканером безопасности сети.

5. SQLMap
SQLMap – это один из популярных инструментов тестирования с открытым исходным кодом на выполнение SQL-инъекций в системе управления реляционными базами данных.

Sqlmap проводит перечисление пользователей, паролей, хэшей, баз данных и поддерживает полный дамп таблиц базы данных.

Если пользователь использует Kali Linux, то он может применить SQLMap, не устанавливая его дополнительно.

6.SQL Injection Scanner
Онлайн сканер для проведения пентестинга, который использует OWASP ZAP. Есть две версии – упрощенная (бесплатная) и полная (нужно зарегистрироваться).

7.Appspider
Appspider, разработанный Rapid7, — это динамическое решение для тестирования безопасности приложений на обход защиты и более чем 95 типов атак.

Уникальная функция Appspider под названием «vulnerability validator» позволяет разработчику воспроизвести уязвимость в режиме реального времени.

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

8. Acunetix
Acunetix – это готовый к работе сканер уязвимостей веб-приложений, которому доверяют более 4000 компаний по всему миру. Не только сканирование SQLi: инструмент способен найти более 6000 других уязвимостей.

Каждая находка классифицируется, и показываются потенциальные корректировки системы безопасности: поэтому пользователь всегда знает, что нужно сделать, чтобы исправить ситуацию к лучшему. Кроме того, человек может интегрироваться с системой CI/CD и SDLC, поэтому каждый риск безопасности идентифицируется и фиксируется до того, как приложение будет развернуто.

9. Wapiti
Wapiti – это сканер уязвимостей на основе Рython. Он поддерживает большое количество инструментов для обнаружения следующих атак:

Sql и XPath;
CRLS и XSS;
Shellshock;
File disclosure;
Server-side request forgery;
Command execution.
Он поддерживает конечную точку HTTP/HTTPS, несколько типов аутентификации, такие как Basic, Digest, NTLM и Kerberos. У пользователя есть возможность создавать отчеты о сканировании в формате HTML, XML, JSON и TXT.

10. Scant3r
Scant3r – это «легкий сканер», основанный на Python.

Он ищет возможность проведения атак XSS, SQLi, RCE, SSTI в заголовках и параметрах URL-адресов.

@sqlhub