Полезные sql запросы для lineage 2

Код:

DELETE * FROM items WHERE item_id=(ид того что удалить) AND owner_id=(ИД перса, у когорого удалить)

Код:

update etcitem set price=1 where price > 1;
update weapon set price=1 where price > 1;
update armor set price=1 where price > 1;

Код:

mysql> select a.name,»armor»,a.crystal_type,i.count,i.enchant_level from armor a, items i where a.item_id=i.item_id and i.owner_id=идчара union select a.name,»weapon»,a.crystal_type,i.count,i.enchant_level from weapon a, items i where a.item_id=i.item_id and i.owner_id=идчара union select a.name,»some»,a.crystal_type,i.count,i.enchant_level from etcitem a, items i where a.item_id=i.item_id and i.owner_id=идчара;

Код:

update etcitem set weight=1 where weight> 1;
update weapon set weight=1 where weight> 1;
update armor set weight=1 where weight > 1;

Код:

DELETE FROM `droplist` WHERE (`itemId`!=’57’) AND (`category`=’-1′)

Код:

UPDATE `droplist` SET `droplist`.`chance`=1000000 WHERE `droplist`.`itemId`=57;

Код:

DELETE FROM droplist WHERE itemId NOT IN (57,77,107);

Код:

UPDATE `npc` SET `patk` = `patk`*0.33 WHERE `type` = ‘L2RaidBoss’;
UPDATE `npc` SET `matk` = `matk`*0.33 WHERE `type` = ‘L2RaidBoss’;
UPDATE `npc` SET `pdef` = `pdef`*0.8 WHERE `type` = ‘L2RaidBoss’;
UPDATE `npc` SET `mdef` = `mdef`*0.8 WHERE `type` = ‘L2RaidBoss’;

Код:

UPDATE `npc` SET `patk` = `patk`*0.8 WHERE `type` = ‘L2Monster’;
UPDATE `npc` SET `matk` = `matk`*0.8 WHERE `type` = ‘L2Monster’;
UPDATE `npc` SET `pdef` = `pdef`*0.8 WHERE `type` = ‘L2Monster’;
UPDATE `npc` SET `mdef` = `mdef`*0.8 WHERE `type` = ‘L2Monster’;

Код:

UPDATE `npc` SET `patk` = `patk`*0.33 WHERE `type` = ‘L2Minion’;
UPDATE `npc` SET `matk` = `matk`*0.33 WHERE `type` = ‘L2Minion’;
UPDATE `npc` SET `pdef` = `pdef`*0.8 WHERE `type` = ‘L2Minion’;
UPDATE `npc` SET `mdef` = `mdef`*0.8 WHERE `type` = ‘L2Minion’;

Код:

UPDATE `npc` SET `patk` = `patk`*0.2 WHERE `type` = ‘L2GrandBoss’;
UPDATE `npc` SET `matk` = `matk`*0.2 WHERE `type` = ‘L2GrandBoss’;
UPDATE `npc` SET `pdef` = `pdef`*0.5 WHERE `type` = ‘L2GrandBoss’;
UPDATE `npc` SET `mdef` = `mdef`*0.5 WHERE `type` = ‘L2GrandBoss’;

Код:

UPDATE `droplist` SET `chance` = ‘5000’ WHERE `chance` <= ‘5000’;

Код:

UPDATE `spawnlist` SET `respawnDelay` = `respawn_min_delay`*3;
UPDATE `spawnlist` SET `respawnDelay` = `respawn_max_delay`*3;
UPDATE `spawnlist` SET `respawnDelay` = `respawnDelay`*3;
UPDATE `spawnlist` SET `respawnDelay` = `respawnDelay`*3;

Код:

UPDATE `raidboss_spawnlist` SET `respawn_min_delay` = ‘3600’;
UPDATE `raidboss_spawnlist` SET `respawn_max_delay` = ‘7200’;

Код:

DELETE FROM `droplist` WHERE `itemId` NOT IN (57,6662,6656,6660,6659,6658,6657,8191,6360,6361,6362,8627,8633,8723,8724,8725,8726,8727,8728,8729,8730,8731,8732,8733,8734,8735,8736,8737,8738,8739,8740,8741,8742,8743,8744,8745,8746,8747,8748,8749,8750,8751,8752,8753,8754,8755,8756,8757,8758,8759,8760,8761,8762,959,955,951,947,729,960,956,952,948,730,6577,6575,6573,6571,6569,6578,6576,6574,6572,6570,8166,8165,8164,8163,8162,8161,8176,8169,8168,8167,8175,8160,8159,8158,8172,8171,8170,1419,6665,6666,6667,6668,6669,6670,6671,6672,6661);

Регистрация: 25.10.2014

Возраст: 29

Сообщений: 11

Отблагодарили 2 раз(а)

Рейтинг мнений:

Под официальный сервер Lineage 2 подобной темы здесь на нашёл, а ведь может пригодиться.

Резервная копия данных

DECLARE @BACKUP_NAME_lin2clancomm VARCHAR(8000)
DECLARE @BACKUP_NAME_lin2comm VARCHAR(8000)
DECLARE @BACKUP_NAME_lin2db VARCHAR(8000)
DECLARE @BACKUP_NAME_lin2report VARCHAR(8000)
DECLARE @BACKUP_NAME_lin2user VARCHAR(8000)
DECLARE @BACKUP_NAME_lin2world VARCHAR(8000)
 
SET @BACKUP_NAME_lin2clancomm = ‘C:Backuplin2clancomm_’+CONVERT(VARCHAR, GETDATE(), 112)+’_’+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ‘:’, ‘_’)+’.BAK’
SET @BACKUP_NAME_lin2comm = ‘C:Backuplin2comm_’+CONVERT(VARCHAR, GETDATE(), 112)+’_’+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ‘:’, ‘_’)+’.BAK’
SET @BACKUP_NAME_lin2db = ‘C:Backuplin2db_’+CONVERT(VARCHAR, GETDATE(), 112)+’_’+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ‘:’, ‘_’)+’.BAK’
SET @BACKUP_NAME_lin2report = ‘C:Backuplin2report_’+CONVERT(VARCHAR, GETDATE(), 112)+’_’+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ‘:’, ‘_’)+’.BAK’
SET @BACKUP_NAME_lin2user = ‘C:Backuplin2user_’+CONVERT(VARCHAR, GETDATE(), 112)+’_’+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ‘:’, ‘_’)+’.BAK’
SET @BACKUP_NAME_lin2world = ‘C:Backuplin2world_’+CONVERT(VARCHAR, GETDATE(), 112)+’_’+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ‘:’, ‘_’)+’.BAK’
 
BACKUP DATABASE lin2clancomm TO DISK = @BACKUP_NAME_lin2clancomm
BACKUP DATABASE lin2comm TO DISK = @BACKUP_NAME_lin2comm
BACKUP DATABASE lin2db TO DISK = @BACKUP_NAME_lin2db
BACKUP DATABASE lin2report TO DISK = @BACKUP_NAME_lin2report
BACKUP DATABASE lin2user TO DISK = @BACKUP_NAME_lin2user
BACKUP DATABASE lin2world TO DISK = @BACKUP_NAME_lin2world
 

Code: SQL

Сброс штрафа на приём и вступление в клан

USE lin2world
UPDATE user_data SET pledge_ousted_time = 0 WHERE pledge_dismiss_time > 0
UPDATE user_data SET pledge_dismiss_time = 0 WHERE pledge_dismiss_time > 0
UPDATE user_data SET pledge_withdraw_time = 0 WHERE pledge_withdraw_time > 0
UPDATE Pledge SET oust_time = 0 WHERE oust_time > 0
UPDATE Pledge SET dismiss_reserved_time = 0 WHERE dismiss_reserved_time > 0
UPDATE Pledge SET alliance_withdraw_time = 0 WHERE alliance_withdraw_time > 0
UPDATE Pledge SET alliance_dismiss_time = 0 WHERE alliance_dismiss_time > 0
 

Code: SQL

На тот случай, если у игроков зависают скиллы

USE lin2world
UPDATE dbo.user_skill SET to_end_time = 0 WHERE to_end_time > 1000
DELETE FROM dbo.user_ActiveSkill
DELETE FROM dbo.user_item WHERE char_id = 0
 

Code: SQL

Персонажи создаются сразу 76 уровня

USE lin2world;
 
ALTER PROCEDURE dbo.lin_CreateChar
(
@char_name NVARCHAR(24),
@account_name NVARCHAR(24),
@account_id INT,
@pledge_id INT,
@builder TINYINT,
@gender TINYINT,
@race TINYINT,
@class TINYINT,
@world SMALLINT,
@xloc INT,
@yloc INT,
@zloc INT,
@HP FLOAT,
@MP FLOAT,
@SP INT,
@EXP INT,
@Lev TINYINT,
@align SMALLINT,
@PK INT,
@Duel INT,
@PKPardon INT,
@FaceIndex INT = 0,
@HairShapeIndex INT = 0,
@HairColorIndex INT = 0
)
AS
 
SET NOCOUNT ON
 
SET @char_name = RTRIM(@char_name)
DECLARE @char_id INT
SET @char_id = 0
 
 
IF @char_name LIKE N’ ‘
BEGIN
RAISERROR (‘Character name has space : name = [%s]’, 16, 1, @char_name)
RETURN -1
END
 
— check user_prohibit
IF EXISTS(SELECT char_name FROM user_prohibit (nolock) WHERE char_name = @char_name)
BEGIN
RAISERROR (‘Character name is prohibited: name = [%s]’, 16, 1, @char_name)
RETURN -1
END
 
DECLARE @user_prohibit_word nvarchar(20)
SELECT top 1 @user_prohibit_word = words FROM user_prohibit_word (nolock) WHERE @char_name LIKE ‘%’ + words + ‘%’
IF @user_prohibit_word IS NOT NULL
BEGIN
RAISERROR (‘Character name has prohibited word: name = [%s], word[%s]’, 16, 1, @char_name, @user_prohibit_word)
RETURN -1
END
 
— check reserved name
DECLARE @reserved_name nvarchar(50)
DECLARE @reserved_account_id INT
SELECT top 1 @reserved_name = char_name, @reserved_account_id = account_id FROM user_name_reserved (nolock) WHERE used = 0 AND char_name = @char_name
IF NOT @reserved_name IS NULL
BEGIN
IF NOT @reserved_account_id = @account_id
BEGIN
RAISERROR (‘Character name is reserved by other player: name = [%s]’, 16, 1, @char_name)
RETURN -1
END
END
 
IF @race>4
BEGIN
RAISERROR (‘Race overflow : = [%s]’, 16, 1, @char_name)
RETURN -1
END
 
IF @race=0 AND @class!=0 AND @class!=10
BEGIN
RAISERROR (‘Class Overflow for Human: = [%s]’, 16, 1, @class)
RETURN -1
END
 
IF @race=1 AND @class!=18 AND @class!=25
BEGIN
RAISERROR (‘Class Overflow for Elf: = [%s]’, 16, 1, @class)
RETURN -1
END
 
IF @race=2 AND @class!=31 AND @class!=38
BEGIN
RAISERROR (‘Class Overflow for DE: = [%s]’, 16, 1, @class)
RETURN -1
END
 
IF @race=3 AND @class!=44 AND @class!=49
BEGIN
RAISERROR (‘Class Overflow for Orc: = [%s]’, 16, 1, @class)
RETURN -1
END
 
IF @race=4 AND @class!=53
BEGIN
RAISERROR (‘Class Overflow for Dwarf: = [%s]’, 16, 1, @class)
RETURN -1
END
 
— insert user_data
INSERT INTO user_data
( char_name, account_name, account_id, pledge_id, builder, gender, race, class, subjob0_class,
world, xloc, yloc, zloc, HP, MP, max_hp, max_mp, SP, EXP, Lev, align, PK, PKpardon, duel, create_date, face_index, hair_shape_index, hair_color_index )
VALUES
(@char_name, @account_name, @account_id, @pledge_id, @builder, @gender, @race, @class, @class,
@world, @xloc, @yloc, @zloc, @HP, @MP, @HP, @MP, 90950592, 931850677, 76, @align, @PK, @Duel, @PKPardon, GETDATE(), @FaceIndex, @HairShapeIndex, @HairColorIndex)
 
IF (@@error = 0)
BEGIN
SET @char_id = @@IDENTITY
INSERT INTO quest (char_id) VALUES (@char_id)
END
 
SELECT @char_id
 
IF @char_id > 0
BEGIN
— make user_history
EXEC lin_InsertUserHistory @char_name, @char_id, 1, @account_name, NULL
IF NOT @reserved_name IS NULL
UPDATE user_name_reserved SET used = 1 WHERE char_name = @reserved_name
END
GO
 

Code: SQL

Добавить предмет всем игрокам

DECLARE @item_type INT;
DECLARE @amount INT;
SET @item_type = ’57’; — itemId
SET @amount = ‘1000000’; — количество
 
INSERT INTO user_item (char_id, item_type, amount, enchant, eroded, bless, ident, wished, warehouse)
SELECT char_id, @item_type, @amount, 0, 0, 0, 0, 0, 0
FROM user_data
 

Code: SQL

Заменить один предмет (9720) на другой (6656) у всех игроков

UPDATE user_item SET item_type = 6656 WHERE item_type = 9720
 

Code: SQL

Удалить ивентовые предметы

//тыквы
DELETE FROM user_item WHERE item_type=6389
DELETE FROM user_item WHERE item_type=6391
DELETE FROM user_item WHERE item_type=9390
 
//кубики
DELETE FROM user_item WHERE item_type=3887
DELETE FROM user_item WHERE item_type=3886
DELETE FROM user_item WHERE item_type=3885
DELETE FROM user_item WHERE item_type=3884
DELETE FROM user_item WHERE item_type=3883
DELETE FROM user_item WHERE item_type=3882
DELETE FROM user_item WHERE item_type=3888
DELETE FROM user_item WHERE item_type=3881
DELETE FROM user_item WHERE item_type=3880
DELETE FROM user_item WHERE item_type=3879
DELETE FROM user_item WHERE item_type=3878
DELETE FROM user_item WHERE item_type=3877
DELETE FROM user_item WHERE item_type=3876
DELETE FROM user_item WHERE item_type=3875
 
//сердца
DELETE FROM user_item WHERE item_type=4217
DELETE FROM user_item WHERE item_type=4216
DELETE FROM user_item WHERE item_type=4215
DELETE FROM user_item WHERE item_type=4214
DELETE FROM user_item WHERE item_type=4213
DELETE FROM user_item WHERE item_type=4212
DELETE FROM user_item WHERE item_type=4211
DELETE FROM user_item WHERE item_type=4210
DELETE FROM user_item WHERE item_type=4209
 
//медали
DELETE FROM user_item WHERE item_type=6401
DELETE FROM user_item WHERE item_type=6400
DELETE FROM user_item WHERE item_type=6399
DELETE FROM user_item WHERE item_type=6402
DELETE FROM user_item WHERE item_type=6393
DELETE FROM user_item WHERE item_type=6392
DELETE FROM user_item WHERE item_type=7058
 
//ng
DELETE FROM user_item WHERE item_type=5560
DELETE FROM user_item WHERE item_type=5561
DELETE FROM user_item WHERE item_type=5234
DELETE FROM user_item WHERE item_type=5283
DELETE FROM user_item WHERE item_type=4411
DELETE FROM user_item WHERE item_type=4412
DELETE FROM user_item WHERE item_type=4413
DELETE FROM user_item WHERE item_type=4414
DELETE FROM user_item WHERE item_type=4415
DELETE FROM user_item WHERE item_type=4416
DELETE FROM user_item WHERE item_type=4417
DELETE FROM user_item WHERE item_type=5956
DELETE FROM user_item WHERE item_type=5562
DELETE FROM user_item WHERE item_type=5563
DELETE FROM user_item WHERE item_type=5564
DELETE FROM user_item WHERE item_type=5565
DELETE FROM user_item WHERE item_type=5566
DELETE FROM user_item WHERE item_type=5583
DELETE FROM user_item WHERE item_type=5584
DELETE FROM user_item WHERE item_type=5585
DELETE FROM user_item WHERE item_type=5586
DELETE FROM user_item WHERE item_type=5555
DELETE FROM user_item WHERE item_type=5556
DELETE FROM user_item WHERE item_type=5557
DELETE FROM user_item WHERE item_type=5558
DELETE FROM user_item WHERE item_type=5559
 

Code: SQL

Удалить всех чаров

DELETE FROM user_surrender WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_subjob WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_sociality WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_skill_old WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_skill WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_recipe WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_punish WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_nobless WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_newbie WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_macroinfo WHERE user_macroinfo.macro_id IN (SELECT DISTINCT user_macro.macro_id
FROM user_macro JOIN user_macroinfo ON (user_macro.macro_id = user_macroinfo.macro_id)
WHERE user_macro.char_id IN(SELECT char_id FROM user_data))
DELETE FROM user_macro WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_log WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_journal WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_item WHERE char_id IN(SELECT char_id FROM user_data) AND warehouse BETWEEN 0 AND 1
DELETE FROM user_history WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_henna WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_friend WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_deleted WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_data_moved WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_comment WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_ActiveSkill WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM ssq_user_data WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM quest WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM olympiad_result WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM olympiad_match WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM nobless_achievements WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM char_pet WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM ch3_lotto_char WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM bookmark WHERE char_id IN(SELECT char_id FROM user_data)
DELETE FROM user_data WHERE char_id IN(SELECT char_id FROM user_data)
 

Code: SQL

Получить определённый итем у всех чаров

USE lin2world
SELECT ud.char_name, ui.amount, ud.create_date, ui.item_id, ui.char_id
FROM user_item ui, user_data ud
WHERE ui.char_id=ud.char_id AND ui.item_type=57
ORDER BY amount DESC
 

Code: SQL

Проверка итемов на две стопки

SELECT i1.char_id, u.char_name, i1.amount AS qty1, i2.amount AS qty2, i1.item_id
FROM user_item i1 (nolock), user_item i2 (nolock), user_data u (nolock)
WHERE i1.item_type=57 AND i2.item_type=57
AND i1.char_id=i2.char_id AND i1.char_id <> 0
AND i1.warehouse=i2.warehouse
AND i1.item_id < i2.item_id
AND u.char_id=i1.char_id
AND u.temp_delete_date IS NULL
ORDER BY i1.char_id
 

Code: SQL

Если у вас отсутствует база lin2log

USE [lin2log]
GO
/****** Object: Table [dbo].[log_insert] Script Date: 03/01/2007 23:51:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[log_insert](
[log_file] [nvarchar](255) COLLATE Korean_Wansung_CI_AS NOT NULL,
[log_table] [nvarchar](50) COLLATE Korean_Wansung_CI_AS NOT NULL,
[rowsprocessed] [INT] NOT NULL,
[log_year] [INT] NOT NULL,
[log_month] [INT] NOT NULL,
[log_day] [INT] NOT NULL,
[log_hour] [INT] NOT NULL,
[log_ip] [INT] NOT NULL,
[log_svr] [nvarchar](20) COLLATE Korean_Wansung_CI_AS NOT NULL,
[log_inout] [nvarchar](20) COLLATE Korean_Wansung_CI_AS NOT NULL,
[process_time] [INT] NULL,
[inserted] [INT] NULL
) ON [PRIMARY]
 

Code: SQL

Вайп

USE [lin2world]
EXEC sp_MSForEachTable ‘TRUNCATE TABLE ?’
 

Code: SQL

(с) RuleZzz, papko, KirillDE

Введение

Язык SQL очень прочно влился в жизнь бизнес-аналитиков и требования к кандидатам благодаря простоте, удобству и распространенности. Из собственного опыта могу сказать, что наиболее часто SQL используется для формирования выгрузок, витрин (с последующим построением отчетов на основе этих витрин) и администрирования баз данных. И поскольку повседневная работа аналитика неизбежно связана с выгрузками данных и витринами, навык написания SQL запросов может стать фактором, из-за которого кандидат или получит преимущество, или будет отсеян. Печальная новость в том, что не каждый может рассчитывать получить его на студенческой скамье. Хорошая новость в том, что в изучении SQL нет ничего сложного, это быстро, а синтаксис запросов прост и понятен. Особенно это касается тех, кому уже доводилось сталкиваться с более сложными языками.

Обучение SQL запросам я разделил на три части. Эта часть посвящена базовому синтаксису, который используется в 80-90% случаев. Следующие две части будут посвящены подзапросам, Join’ам и специальным операторам. Цель гайдов: быстро и на практике отработать синтаксис SQL, чтобы добавить его к арсеналу навыков.

Практика

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

Кликнуть здесь

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

Структура sql-запросов

Общая структура запроса выглядит следующим образом:

SELECT (‘столбцы или * для выбора всех столбцов; обязательно’)
FROM (‘таблица; обязательно’)
WHERE (‘условие/фильтрация, например, city = ‘Moscow’; необязательно’)
GROUP BY (‘столбец, по которому хотим сгруппировать данные; необязательно’)
HAVING (‘условие/фильтрация на уровне сгруппированных данных; необязательно’)
ORDER BY (‘столбец, по которому хотим отсортировать вывод; необязательно’)

Разберем структуру. Для удобства текущий изучаемый элемент в запроса выделяется CAPS’ом.

SELECT, FROM

SELECT, FROM — обязательные элементы запроса, которые определяют выбранные столбцы, их порядок и источник данных.

Выбрать все (обозначается как *) из таблицы Customers:

SELECT * FROM Customers

Выбрать столбцы CustomerID, CustomerName из таблицы Customers:

SELECT CustomerID, CustomerName FROM Customers

WHERE

WHERE — необязательный элемент запроса, который используется, когда нужно отфильтровать данные по нужному условию. Очень часто внутри элемента where используются IN / NOT IN для фильтрации столбца по нескольким значениям, AND / OR для фильтрации таблицы по нескольким столбцам.

Фильтрация по одному условию и одному значению:

select * from Customers
WHERE City = ‘London’

Фильтрация по одному условию и нескольким значениям с применением IN (включение) или NOT IN (исключение):

select * from Customers
where City IN (‘London’, ‘Berlin’)
select * from Customers
where City NOT IN (‘Madrid’, ‘Berlin’,’Bern’)

Фильтрация по нескольким условиям с применением AND (выполняются все условия) или OR (выполняется хотя бы одно условие) и нескольким значениям:

select * from Customers
where Country = ‘Germany’ AND City not in (‘Berlin’, ‘Aachen’) AND CustomerID > 15
select * from Customers
where City in (‘London’, ‘Berlin’) OR CustomerID > 4

GROUP BY

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

При использовании GROUP BY обязательно:

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

Группировка количества клиентов по городу:

select City, count(CustomerID) from Customers
GROUP BY City

Группировка количества клиентов по стране и городу:

select Country, City, count(CustomerID) from Customers
GROUP BY Country, City

Группировка продаж по ID товара с разными агрегатными функциями: количество заказов с данным товаром и количество проданных штук товара:

select ProductID, COUNT(OrderID), SUM(Quantity) from OrderDetails
GROUP BY ProductID

Группировка продаж с фильтрацией исходной таблицы. В данном случае на выходе будет таблица с количеством клиентов по городам Германии:

select City, count(CustomerID) from Customers
WHERE Country = ‘Germany’
GROUP BY City

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

select City, count(CustomerID) AS Number_of_clients from Customers
group by City

HAVING

HAVING — необязательный элемент запроса, который отвечает за фильтрацию на уровне сгруппированных данных (по сути, WHERE, но только на уровень выше).

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

select City, count(CustomerID) from Customers
group by City
HAVING count(CustomerID) >= 5

В случае с переименованным столбцом внутри HAVING можно указать как и саму агрегирующую конструкцию count(CustomerID), так и новое название столбца number_of_clients:

select City, count(CustomerID) as number_of_clients from Customers
group by City
HAVING number_of_clients >= 5

Пример запроса, содержащего WHERE и HAVING. В данном запросе сначала фильтруется исходная таблица по пользователям, рассчитывается количество клиентов по городам и остаются только те города, где количество клиентов не менее 5:

select City, count(CustomerID) as number_of_clients from Customers
WHERE CustomerName not in (‘Around the Horn’,’Drachenblut Delikatessend’)
group by City
HAVING number_of_clients >= 5

ORDER BY

ORDER BY — необязательный элемент запроса, который отвечает за сортировку таблицы.

Простой пример сортировки по одному столбцу. В данном запросе осуществляется сортировка по городу, который указал клиент:

select * from Customers
ORDER BY City

Осуществлять сортировку можно и по нескольким столбцам, в этом случае сортировка происходит по порядку указанных столбцов:

select * from Customers
ORDER BY Country, City

По умолчанию сортировка происходит по возрастанию для чисел и в алфавитном порядке для текстовых значений. Если нужна обратная сортировка, то в конструкции ORDER BY после названия столбца надо добавить DESC:

select * from Customers
order by CustomerID DESC

Обратная сортировка по одному столбцу и сортировка по умолчанию по второму:

select * from Customers
order by Country DESC, City

JOIN

JOIN — необязательный элемент, используется для объединения таблиц по ключу, который присутствует в обоих таблицах. Перед ключом ставится оператор ON.

Запрос, в котором соединяем таблицы Order и Customer по ключу CustomerID, при этом перед названиям столбца ключа добавляется название таблицы через точку:

select * from Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID

Нередко может возникать ситуация, когда надо промэппить одну таблицу значениями из другой. В зависимости от задачи, могут использоваться разные типы присоединений. INNER JOIN — пересечение, RIGHT/LEFT JOIN для мэппинга одной таблицы знаениями из другой,

select * from Orders
join Customers on Orders.CustomerID = Customers.CustomerID
where Customers.CustomerID >10

Внутри всего запроса JOIN встраивается после элемента from до элемента where, пример запроса:

Другие типы JOIN’ов можно увидеть на замечательной картинке ниже:

В следующей части подробнее поговорим о типах JOIN’ов и вложенных запросах.

При возникновении вопросов/пожеланий, всегда прошу обращаться!