cheatsheets

SQL

← Назад к перечню шпаргалок

1. Запросы для поиска данных

SELECT используется для выбора данных из базы данных.

SELECT *
FROM table_name;

DISTINCT отфильтровывает повторяющиеся значения и возвращает строки указанного столбца.

SELECT DISTINCT column_name;

WHERE используется для фильтрации записей/строк.

SELECT column1, column2
FROM table_name
WHERE condition;
SELECT *
FROM table_name
WHERE condition1
  AND condition2;
SELECT *
FROM table_name
WHERE condition1
   OR condition2;
SELECT *
FROM table_name
WHERE NOT condition;
SELECT *
FROM table_name
WHERE condition1
  AND (condition2 OR condition3);
SELECT *
FROM table_name
WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

ORDER BY используется для сортировки набора результатов в порядке возрастания или убывания.

SELECT *
FROM table_name
ORDER BY column;
SELECT *
FROM table_name
ORDER BY column DESC;
SELECT *
FROM table_name
ORDER BY column1 ASC, column2 DESC;

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

SELECT TOP number columns_names
FROM table_name
WHERE condition;
SELECT TOP percent columns_names
FROM table_name
WHERE condition;
-- Not all database systems support SELECT TOP. The MySQL equivalent is the LIMIT clause
SELECT column_names
FROM table_name LIMIT
offset, count;

LIKE оператор, используемый в предложении WHERE для поиска определенного шаблона в столбце.

WHERE CustomerName LIKE
‘a%
’   -- Находит любые значения, которые начинаются с “a”
WHERE CustomerName LIKE
‘%a
’   -- Находит любые значения, которые заканчиваются на “a”
WHERE CustomerName LIKE
‘%or%
’ -- Находит любые значения, которые имеют “or” в любой позиции
WHERE CustomerName LIKE
‘_r%
’  -- Находит любые значения, имеющие букву “r” во второй позиции
WHERE CustomerName LIKE
‘a__%
’ -- Находит любые значения, начинающиеся с буквы “a” и имеющие длину не менее 3 символов
WHERE ContactName LIKE
‘a%o
’   -- Находит любые значения, которые начинаются с “a” и заканчиваются “о”

IN оператор, который позволяет указать несколько значений в предложении WHERE.

По сути, оператор IN является сокращением для нескольких условий OR.

SELECT column_names
FROM table_name
WHERE column_name IN (value1, value2, …);
SELECT column_names
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

BETWEEN оператор выбирает значения в заданном диапазоне включительно.

SELECT column_names
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
SELECT *
FROM Products
WHERE (column_name BETWEEN value1 AND value2)
  AND NOT column_name2 IN (value3, value4);
SELECT *
FROM Products
WHERE column_name BETWEEN #01 / 07 / 1999# AND #03 / 12 / 1999#;

NULL

SELECT *
FROM table_name
WHERE column_name IS NULL;
SELECT *
FROM table_name
WHERE column_name IS NOT NULL;

AS используются для присвоения временного имени таблице или столбцу.

SELECT column_name AS alias_name
FROM table_name;
SELECT column_name
FROM table_name AS alias_name;
SELECT column_name AS alias_name1, column_name2 AS alias_name2;
SELECT column_name1, column_name2 + ‘, ‘ + column_name3 AS alias_name;

UNION используется в SQL для объединения набора результатов двух или более операторов SELECT.

Каждый оператор SELECT в UNION должен иметь одинаковое количество столбцов.
Столбцы должны иметь похожие типы данных.
Столбцы в каждом операторе SELECT также должны быть в том же порядке.

SELECT columns_names
FROM table1
UNION
SELECT column_name
FROM table2;

Оператор UNION выбирает только отдельные значения, UNION ALL разрешает дубликаты.

INTERSECT используется для возврата записей, общих для двух операторов SELECT.

Обычно используется так же, как UNION.

SELECT columns_names
FROM table1
INTERSECT
SELECT column_name
FROM table2;

EXCEPT оператор, используемый для возврата всех записей в первом операторе SELECT, которые не найдены во втором операторе SELECT.

Обычно используется так же, как UNION выше.

SELECT columns_names
FROM table1
EXCEPT
SELECT column_name
FROM table2;

**ANY

ALL** оператор, используемый для проверки условий подзапроса, используемых в предложениях WHERE или HAVING.

• ANY возвращает значение true, если какие-либо значения подзапроса соответствуют условию.
• ALL возвращает значение true, если все значения подзапроса соответствуют условию.

SELECT columns_names
FROM table1
WHERE column_name operator (ANY|ALL) (SELECT column_name FROM table_name WHERE condition);

GROUP BY оператор, часто используемый с агрегатными функциями (COUNT, MAX, MIN, SUM, AVG) для группировки набора результатов по одному или нескольким столбцам.

SELECT column_name1, COUNT(column_name2)
FROM table_name
WHERE condition
GROUP BY column_name1
ORDER BY COUNT(column_name2) DESC;

HAVING оператор SQL является указателем на результат выполнения агрегатных функций.

Агрегатной функцией в языке SQL называется функция, возвращающая какое-либо одно значение по набору значений столбца.

Такими функциями являются:

• COUNT()
• MIN()
• MAX()
• AVG()
• SUM()

SELECT COUNT(column_name1), column_name2
FROM table
GROUP BY column_name2
HAVING COUNT(column_name1) > 5;

WITH запрос, который часто используется для извлечения иерархических данных.

WITH RECURSIVE cte AS (SELECT c0.*
                       FROM categories AS c0
                       WHERE id = 1 # Starting point
UNION ALL
SELECT c1.*
FROM categories AS c1
         JOIN cte ON c1.parent_category_id = cte.id )
SELECT *
FROM cte

2. Запросы для модификации данных

INSERT INTO используется для вставки новых записей/строк в таблицу.

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);
INSERT INTO table_name
VALUES (value1, value2 …);

UPDATE используется для изменения существующих записей в таблице.

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;
UPDATE table_name
SET column_name = value;

DELETE используется для удаления существующих записей/строк в таблице.

DELETE
FROM table_name
WHERE condition;
DELETE
* FROM table_name;

3. Считаем количество записей

COUNT возвращает количество вхождений

SELECT COUNT(DISTINCT column_name);

MIN() и MAX() возвращает наименьшее/наибольшее значение выбранного столбца.

SELECT MIN(column_names)
FROM table_name
WHERE condition;
SELECT MAX(column_names)
FROM table_name
WHERE condition;

AVG() возвращает среднее значение числового столбца.

SELECT AVG(column_name)
FROM table_name
WHERE condition;

SUM() возвращает общую сумму числового столбца.

SELECT SUM(column_name)
FROM table_name
WHERE condition;

4. Join запросы SQL

INNER JOIN возвращает записи, имеющие совпадающее значение в обеих таблицах.

SELECT column_names
FROM table1
         INNER JOIN table2 ON table1.column_name = table2.column_name;
SELECT table1.column_name1, table2.column_name2, table3.column_name3
FROM ((table1 INNER JOIN table2 ON relationship) INNER JOIN table3 ON relationship);

LEFT (OUTER) JOIN возвращает все записи из левой таблицы (1) и соответствующие записи из правой таблицы (2).

SELECT column_names
FROM table1
         LEFT JOIN table2 ON table1.column_name = table2.column_name;

RIGHT (OUTER) JOIN возвращает все записи из правой таблицы (2) и соответствующие записи из левой таблицы (1).

SELECT column_names
FROM table1
         RIGHT JOIN table2 ON table1.column_name = table2.column_name;
FULL (OUTER) JOIN
SELECT column_names
FROM table1
         FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

Self JOIN обычные джоины.

SELECT column_names
FROM table1 T1,
     table1 T2
WHERE condition;

5. Запросы на просмотр

CREATE создаёт представление.

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

SELECT получает представление.

SELECT *
FROM view_name;

DROP отказаться от представления.

DROP VIEW view_name;

6. Altering запросы

ADD добавить столбец.

ALTER TABLE table_name
    ADD column_name column_definition;

MODIFY изменить тип данных столбца.

ALTER TABLE table_name MODIFY column_name column_type;

DROP удалить столбец.

ALTER TABLE table_name DROP COLUMN column_name;

7. Создание таблицы sql

CREATE создаёт таблицу.

CREATE TABLE table_name
(
    column1 datatype,
    column2 datatype,
    column3 datatype,
    column4 datatype,
);

8. Обобщенное табличное выражение

WITH CTE_Name (column1, column2)
         AS
-- Define the CTE query.
         (SELECT column1, column2
          FROM Table1
          WHERE column1 IS NOT NULL)
-- Define the outer query referencing the CTE name.
SELECT *
FROM CTE_Name
GROUP BY column1, column2
ORDER BY column1, column2;
GO

star_border

-- Поиск текста в SP

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[Find_Text_In_SP]
@StringToSearch VARCHAR(100),
@StringToSearch2 VARCHAR(100) = '',
@StringToSearch3 VARCHAR(100) = '',
@Name VARCHAR(100) = ''

AS
	SET @StringToSearch = '%' +@StringToSearch + '%'
	SET @StringToSearch2 = '%' +@StringToSearch2 + '%'
	SET @StringToSearch3 = '%' +@StringToSearch3 + '%'
	SET @Name = '%' +@Name + '%'

SELECT ROUTINE_NAME, LEN(OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME))) AS SP_Length
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME)) LIKE @stringtosearch
  AND OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME)) LIKE @StringToSearch2
  AND OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME)) LIKE @StringToSearch3
  AND (ROUTINE_TYPE = 'PROCEDURE' OR ROUTINE_TYPE = 'FUNCTION')
  AND ROUTINE_NAME LIKE @Name
ORDER BY routine_name
    GO

OFFSET FETCH

Пропустить первые 10 строк из отсортированного набора результатов и вернуть остальные строки.

SELECT column1, column2
FROM table_name
ORDER BY column1
OFFSET 10 ROWS;

Пропустить первые 10 строк из отсортированного набора результатов и вернуть следующие 5 строк.

SELECT column1, column2
FROM table_name
ORDER BY column1
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

Переименование Table

exec sp_rename '[schema.old_table_name]', 'new_table_name'

Переименование Column

exec sp_rename 'table_name.[oldColumName]' , 'newColumName', 'COLUMN'

SCOPE_IDENTITY

-- возвращает последнее значение IDENTITY, вставленное в столбец IDENTITY в той же области
-- возвращает последнее значение идентификатора, сгенерированное для любой таблицы в текущем сеансе и текущей области
-- область видимости — это модуль; хранимая процедура, триггер, функция или пакет

SELECT SCOPE_IDENTITY()

Найти, какой таблице принадлежит ограничение

SELECT OBJECT_NAME(o.parent_object_id)
FROM sys.objects o
WHERE o.name = 'MyConstraintName'
  AND o.parent_object_id <> 0

TRY-CATCH

BEGIN TRY
BEGIN
TRANSACTION

    -- Do something here

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    DECLARE
@ErrorMessage NVARCHAR(4000),
        @ErrorSeverity INT,
        @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE();
RAISERROR
(
        @ErrorMessage,
        @ErrorSeverity,
        @ErrorState
    );

ROLLBACK TRANSACTION
END CATCH

Условия с переменными WHERE CLAUSE

-- using '=' operator
WHERE Column = IIF(@Variable IS NULL ,@Variable, Column)

-- using 'LIKE, IN, etc.'
WHERE (@Variable IS NULL OR Column LIKE '%' + @Variable + '%' )

Вставить разделенную запятой строку в таблицу

DECLARE
@String = '1, 4, 3'
DECLARE
@Tbl TABLE(ID INT);

INSERT INTO @Tbl (ID)
    (SELECT value
     FROM STRING_SPLIT(@String, ',')
     WHERE RTRIM(value) <> '');

UPDATE с JOIN

UPDATE Table1
SET Table1.Column = B.Column FROM Table1 A
    INNER JOIN Table2 B
ON A.ID = B.ID

DELETE с JOIN

DELETE
A
FROM Table1 A
INNER JOIN Table2 B
    ON B.Id = A.Id
WHERE A.Column = 1 AND B.Column = 2

UPDATE/INSERT identity column

SET
IDENTITY_INSERT YourTable ON

-- UPDATE/INSERT STATEMENT HERE

SET IDENTITY_INSERT YourTable OFF

Находим Foreign Key ограничения ссылок таблицы

SELECT OBJECT_NAME(f.parent_object_id)                    TableName,
       COL_NAME(fc.parent_object_id, fc.parent_column_id) ColName
FROM sys.foreign_keys AS f
         INNER JOIN
     sys.foreign_key_columns AS fc
     ON f.OBJECT_ID = fc.constraint_object_id
         INNER JOIN
     sys.tables t
     ON t.OBJECT_ID = fc.referenced_object_id
WHERE OBJECT_NAME(f.referenced_object_id) = 'Table_Name'

Парсим JSON файл в таблицу

-- JSON Data sample:
-- {
-- "label": "test ",
-- "value": 1
-- },
-- {
-- "label": "test2 ",
-- "value": 2
-- }

DECLARE
@tbl TABLE (id INT, label VARCHAR(500));

DECLARE
@json VARCHAR(max);

SELECT @json = BulkColumn
FROM OPENROWSET(BULK 'C:\jsonFile.json', SINGLE_CLOB) as j
    INSERT
INTO @tbl (id, label)
SELECT [value], label
FROM OPENJSON(@json)
WITH ([value] int, label nvarchar(max))

SELECT *
FROM @tbl

Добавляем FK в существующую колонку

ALTER TABLE [Table1]
    ADD CONSTRAINT FK_Table2_Id FOREIGN KEY (Table1_Id)
    REFERENCES Table2(Table2_Id);

Список всех пользовательских функций по типу

SELECT [Name], [Definition], [Type_desc]
FROM sys.sql_modules m
    INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE [Type_desc] like '%function%'

Обновляем и изменяем часть строки

UPDATE dbo.[Table]
SET Value = REPLACE(Value, '123\', '')
WHERE ID <=4

Генерируем случайные INT SQL

-- Создайте переменные для генерации случайных чисел
DECLARE
@Random INT;
DECLARE
@Upper INT;
DECLARE
@Lower INT

-- Это создаст случайное число от 1 до 999
SET @Lower = 1   -- Наименьшее случайное число
SET @Upper = 999 -- Самое большое случайное число
SELECT @Random = ROUND(((@Upper - @Lower - 1) * RAND() + @Lower), 0)
SELECT @Random

Создаем случайные ДАТЫ между двумя диапазонами

DECLARE
@FromDate DATE = '2019-09-01';
DECLARE
@ToDate DATE = '2019-12-31';

SELECT DATEADD(DAY, RAND(CHECKSUM(NEWID())) * (1 + DATEDIFF(DAY, @FromDate, @ToDate)), @FromDate)

Получаем список таблиц

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

Проверить, существует ли таблица в базе данных

IF
EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Table')
BEGIN
-- exists
END

Сгенерировать 6-значный уникальный номер

SELECT LEFT (CAST (RAND()*1000000000+999999 AS INT), 6) AS OTP

Ищем table name

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%%'

Поиск между двумя датами

-- convert to date to ignore time
SELECT *
FROM Table T
WHERE CONVERT(DATE, T.DateColumn) BETWEEN COALESCE(CONVERT(DATE, @DateFrom), CONVERT(DATE, T.DateColumn)) AND COALESCE(
        CONVERT(DATE, @DateTo), CONVERT(DATE, T.DateColumn))

Формат дат

-- Output: 21/03/2022
SELECT FORMAT(getdate(), 'dd/MM/yyyy ') as date

-- Output: 21/03/2022, 11:36:14
SELECT FORMAT(getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date

-- Output: Wednesday, March, 2022
SELECT FORMAT(getdate(), 'dddd, MMMM, yyyy') as date

-- Output: Mar 21 2022
SELECT FORMAT(getdate(), 'MMM dd yyyy') as date

-- Output: 03.21.22
SELECT FORMAT(getdate(), 'MM.dd.yy') as date

-- Output: 03-21-22
SELECT FORMAT(getdate(), 'MM-dd-yy') as date

-- Output: 11:36:14 AM
SELECT FORMAT(getdate(), 'hh:mm:ss tt') as date

-- Output: 03/21/2022
SELECT FORMAT(getdate(), 'd', 'us') as date

Триггеры

create trigger t1
    on table1 after insert
as
begin
insert into Audit
    (Column)
select 'Insert New Row with Key' + cast(t.Id as nvarchar(10)) + 'in table1'
from table1 t
where Id IN (select Id from inserted)
end
go

Найти все таблицы, содержащие столбец с указанным именем

SELECT c.name AS 'ColumnName', t.name AS 'TableName'
FROM sys.columns c
         JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%COLUMN_NAME%'
ORDER BY TableName, ColumnName;

Скрипт для создания отбрасываемых всех таблиц с префиксом

SELECT 'DROP TABLE ' + TABLE_NAME + ''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'PREFIX_%'

Скрипт для изменения таблиц, чтобы удалить все ограничения

DECLARE
@SQL varchar(4000)=''
SELECT @SQL =
       @SQL + 'ALTER TABLE ' + s.name + '.' + t.name + ' DROP CONSTRAINT [' + RTRIM(f.name) + '];' + CHAR(13)
FROM sys.Tables t
    INNER JOIN sys.foreign_keys f
ON f.parent_object_id = t.object_id
    INNER JOIN sys.schemas s ON s.schema_id = f.schema_id
WHERE t.name LIKE 'PREFIX_%'

-- EXEC (@SQL)

    PRINT @SQL

Cursor

Перебрать набор данных

-- two variables to hold product name and list price (gonna be used on the loop)
DECLARE
@product_name VARCHAR(MAX),
    @list_price DECIMAL;

-- defines the result set for the cursor
DECLARE
cursor_product CURSOR
FOR
SELECT product_name,
       list_price
FROM dbo.products;

-- open cursor
OPEN cursor_product;

-- fetch a row from the cursor into one or more variables
FETCH NEXT FROM cursor_product INTO
    @product_name,
    @list_price;

-- loop through the cursor
WHILE
@@FETCH_STATUS = 0
BEGIN
        -- use current product_name and list_price from current index of the cursor in the loop
        PRINT
@product_name + CAST(@list_price AS varchar);
        -- fetch next row from the cursor
FETCH NEXT FROM cursor_product INTO
    @product_name,
    @list_price;
END;

-- close cursor
CLOSE cursor_product;

-- deallocate the cursor to release it
DEALLOCATE
cursor_product;
Join Multiple Tables
SELECT comments.body,
       posts.title,
       users.first_name,
       users.last_name
FROM comments
         INNER JOIN posts on posts.id = comments.post_id
         INNER JOIN users on users.id = comments.user_id
ORDER BY posts.title;
Aggregate
Functions
SELECT COUNT(id)
FROM users;
SELECT MAX(age)
FROM users;
SELECT MIN(age)
FROM users;
SELECT SUM(age)
FROM users;
SELECT UCASE(first_name), LCASE(last_name)
FROM users;
Group By
SELECT age, COUNT(age)
FROM users
GROUP BY age;
SELECT age, COUNT(age)
FROM users
WHERE age > 20
GROUP BY age;
SELECT age, COUNT(age)
FROM users
GROUP BY age
HAVING count(age) >= 2;

https://uproger.com/shpargalki-sql-2023/

← Назад к перечню шпаргалок