SQL запросы для java серверов Lineage 2

[pareHbry6axa]

Как вставить запрос в базу?
Открываем Navicat'ом нашу базу. Жмём правую кнопку мыши » Console.
[Ссылки могут видеть только зарегистрированные пользователи. ]
Вводим запрос и жмём Enter.
[Ссылки могут видеть только зарегистрированные пользователи. ]

Все мобы не агрятся (подходит для RT, если вам не подходит, переделайте название колонки, то есть "agro" и значение (0))

UPDATE npc SET agro='0' WHERE type='L2Monster';

Изменяем начальную зону:

DELETE FROM char_templates WHERE ClassId=0;
DELETE FROM char_templates WHERE ClassId=10;
DELETE FROM char_templates WHERE ClassId=18;
DELETE FROM char_templates WHERE ClassId=25;
DELETE FROM char_templates WHERE ClassId=31;
DELETE FROM char_templates WHERE ClassId=38;
DELETE FROM char_templates WHERE ClassId=44;
DELETE FROM char_templates WHERE ClassId=49;
DELETE FROM char_templates WHERE ClassId=53;

INSERT INTO `char_templates` VALUES
(0, 'Human Fighter', 0, 40, 43, 30, 21, 11, 25, 4, 80, 6, 41, 300, 333, 33, 44, 33, 115, 81900, 140425, -124265, -1929, 0, 1.10, 1.188000, 9.0, 23.0, 1.10, 1.188000, 8.0, 23.5, 1147, 1146, 10, 2369, 5588),
(10, 'Human Mage', 0, 22, 27, 21, 41, 20, 39, 3, 54, 6, 41, 300, 333, 28, 40, 28, 120, 62500, 140425, -124265, -1929, 0, 1.01, 0.872640, 7.5, 22.8, 1.01, 0.872640, 6.5, 22.5, 425, 461, 6, 5588, 0),
(18, 'Elf Fighter', 1, 36, 36, 35, 23, 14, 26, 4, 80, 6, 41, 300, 333, 36, 46, 36, 125, 73000, 140425, -124265, -1929, 0, 1.15, 1.242000, 7.5, 24.0, 1.15, 1.242000, 7.5, 23.0, 1147, 1146, 10, 2369, 5588),
(25, 'Elf Mage', 1, 21, 25, 24, 37, 23, 40, 3, 54, 6, 41, 300, 333, 30, 41, 30, 122, 62400, 140425, -124265, -1929, 0, 1.04, 0.898560, 7.5, 24.0, 1.04, 0.898560, 7.5, 23.0, 425, 461, 6, 5588, 0),
(31, 'DE Fighter', 2, 41, 32, 34, 25, 12, 26, 4, 80, 6, 41, 300, 333, 35, 45, 35, 122, 69000, 140425, -124265, -1929, 0, 1.14, 1.231200, 7.5, 24.0, 1.14, 1.231200, 7.0, 23.5, 1147, 1146, 10, 2369, 5588),
(38, 'DE Mage', 2, 23, 24, 23, 44, 19, 37, 3, 54, 6, 41, 300, 333, 29, 41, 29, 122, 61000, 140425, -124265, -1929, 0, 1.14, 1.231200, 7.5, 24.0, 1.03, 0.889920, 7.0, 23.5, 425, 461, 6, 5588, 0),
(44, 'Orc Fighter', 3, 40, 47, 26, 18, 12, 27, 4, 80, 6, 41, 300, 333, 31, 42, 31, 117, 87000, 140425, -124265, -1929, 0, 1.06, 1.144800, 11.0, 28.0, 1.06, 1.144800, 7.0, 27.0, 1147, 1146, 2368, 2369, 5588),
(49, 'Orc Mage', 3, 27, 31, 24, 31, 15, 42, 3, 54, 6, 41, 300, 333, 30, 41, 30, 121, 68000, 140425, -124265, -1929, 0, 1.04, 0.898560, 7.0, 27.5, 1.04, 0.898560, 8.0, 25.5, 425, 461, 2368, 5588, 0),
(53, 'Dwarf Fighter', 4, 39, 45, 29, 20, 10, 27, 4, 80, 6, 41, 300, 333, 33, 43, 33, 115, 83000, 140425, -124265, -1929, 1, 1.09, 1.487196, 9.0, 18.0, 1.09, 1.487196, 5.0, 19.0, 1147, 1146, 10, 2370, 5588);

Начальная зона - Глудин:

REPLACE INTO char_templates VALUES (0, "Human Fighter", 0, 40, 43, 30, 21, 11, 25, 4, 72, 3, 47, 330, 213, 33, 44, 33, 115, 81900, -83063, 150791, -3133, 0, "1.1", "1.188", 9, 23, "1.1", "1.188", 8, "23.5", 34, 26, 68, 4222, 5588);
REPLACE INTO char_templates VALUES (18, "Elf Fighter", 1, 36, 36, 35, 23, 14, 26, 4, 72, 3, 47, 345, 249, 36, 46, 36, 125, 73000,  -83063, 150791, -3133, 0, "1.15", "1.242", "7.5", 24, "1.15", "1.242", "7.5", 23, 34, 26, 68, 4222, 5588);
REPLACE INTO char_templates VALUES (31, "DE Fighter", 2, 41, 32, 34, 25, 12, 26, 4, 72, 3, 47, 342, 226, 35, 45, 35, 122, 69000,  -83063, 150791, -3133, 0, "1.14", "1.2312", "7.5", 24, "1.14", "1.2312", 7, "23.5", 34, 26, 68, 4222, 5588);
REPLACE INTO char_templates VALUES (44,'Orc Fighter', 3, 40, 47, 26, 18, 12, 27, 4, 72, 2, 48, 318, 226, 31, 42, 31, 117, 87000,  -83063, 150791, -3133, 0, "1.06", "1.144800", 11.0, 28.0,1.06, "1.144800", 7.0, 27.0, 34, 26, 257, 0, 5588);
REPLACE INTO char_templates VALUES (53, "Dwarf Fighter", 4, 39, 45, 29, 20, 10, 27, 4, 72, 3, 48, 327, 203, 33, 43, 33, 115, 83000,  -83063, 150791, -3133, 1, "1.09", "1.487196", 9, 18, "1.09", "1.487196", 5, 19, 34, 26, 87, 4222, 5588);
REPLACE INTO char_templates VALUES (10, "Human Mage", 0, 22, 27, 21, 41, 20, 39, 2, 48, 7, 54, 303, 333, 28, 40, 28, 120, 62500,  -83063, 150791, -3133, 0, "1.01", "0.87264", "7.5", "22.8", "1.01", "0.87264", "6.5", "22.5", 1105, 1102, 177, 0, 5588);
REPLACE INTO char_templates VALUES (25, "Elf Mage", 1, 21, 25, 24, 37, 23, 40, 2, 48, 6, 54, 312, 386, 30, 41, 30, 122, 62400,  -83063, 150791, -3133, 0, "1.04", "0.89856", "7.5", 24, "1.04", "0.89856", "7.5", 23, 1105, 1102, 177, 0, 5588);
REPLACE INTO char_templates VALUES (38, "DE Mage", 2, 23, 24, 23, 44, 19, 37, 2, 48, 7, 53, 309, 316, 29, 41, 29, 122, 61000,  -83063, 150791, -3133, 0, "1.14", "1.2312", "7.5", 24, "1.03", "0.88992", 7, "23.5", 1105, 1102, 177, 0, 5588);
REPLACE INTO char_templates VALUES (49, "Orc Mage", 3, 27, 31, 24, 31, 15, 42, 2, 48, 4, 56, 312, 265, 30, 41, 30, 121, 68000,  -83063, 150791, -3133, 0, "1.04", "0.89856", 7, "27.5", "1.04", "0.89856", 8, "25.5", 1105, 1102, 257, 0, 5588);

Авторестарт гейм сервера (время измените, сейчас стоит "04:50:00") :

INSERT INTO global_tasks (task,type,last_activation,param1,param2,param3) VALUES ('restart','TYPE_GLOBAL_TASK','0','1','04:50:00','600');

Добавляем скилл всем новым чарам (за пример взят Weight Limit - 'ID класа которому даётся скилл', 'id скила', 'лвл скила', 'имя скила', ...) ) :

INSERT INTO skill_trees VALUES (0, 150, 1, "Weight Limit", 0, 1);   
INSERT INTO skill_trees VALUES (10, 150, 1, "Weight Limit", 0, 1);   
INSERT INTO skill_trees VALUES (18, 150, 1, "Weight Limit", 0, 1);   
INSERT INTO skill_trees VALUES (25, 150, 1, "Weight Limit", 0, 1);   
INSERT INTO skill_trees VALUES (31, 150, 1, "Weight Limit", 0, 1);   
INSERT INTO skill_trees VALUES (38, 150, 1, "Weight Limit", 0, 1);   
INSERT INTO skill_trees VALUES (44, 150, 1, "Weight Limit", 0, 1);   
INSERT INTO skill_trees VALUES (49, 150, 1, "Weight Limit", 0, 1);   
INSERT INTO skill_trees VALUES (53, 150, 1, "Weight Limit"", 0, 1);

Всем чарам при создании даётся лучшый NG шмот:

REPLACE INTO char_templates VALUES (0, "Human Fighter", 0, 40, 43, 30, 21, 11, 25, 4, 72, 3,   
47, 330, 213, 33, 44, 33, 115, 81900, -71338, 258271, -3104, 0, "1.1", "1.188", 9, 23, "1.1", "1.188", 8,   
"23.5", 34, 26, 68, 4222, 5588);   
REPLACE INTO char_templates VALUES (18, "Elf Fighter", 1, 36, 36, 35, 23, 14, 26, 4, 72, 3, 47, 345,   
249, 36, 46, 36, 125, 73000, 45978, 41196, -3440, 0, "1.15", "1.242", "7.5", 24, "1.15", "1.242", "7.5",   
23, 34, 26, 68, 4222, 5588);   
REPLACE INTO char_templates VALUES (31, "DE Fighter", 2, 41, 32, 34, 25, 12, 26, 4, 72, 3, 47, 342,   
226, 35, 45, 35, 122, 69000, 28377, 10916, -4224, 0, "1.14", "1.2312", "7.5", 24, "1.14", "1.2312", 7,   
"23.5", 34, 26, 68, 4222, 5588);   
REPLACE INTO char_templates VALUES (44,'Orc Fighter', 3, 40, 47, 26, 18, 12, 27, 4, 72, 2, 48, 318,   
226, 31, 42, 31, 117, 87000, -58192, -113408, -650, 0, "1.06", "1.144800", 11.0, 28.0,1.06, "1.144800",   
7.0, 27.0, 34, 26, 257, 0, 5588);   
REPLACE INTO char_templates VALUES (53, "Dwarf Fighter", 4, 39, 45, 29, 20, 10, 27, 4, 72, 3, 48,   
327, 203, 33, 43, 33, 115, 83000, 108512, -174026, -400, 1, "1.09", "1.487196", 9, 18, "1.09",   
"1.487196", 5, 19, 34, 26, 87, 4222, 5588);   
REPLACE INTO char_templates VALUES (10, "Human Mage", 0, 22, 27, 21, 41, 20, 39, 2, 48, 7, 54, 303,   
333, 28, 40, 28, 120, 62500, -90890, 248027, -3570, 0, "1.01", "0.87264", "7.5", "22.8", "1.01",   
"0.87264", "6.5", "22.5", 1105, 1102, 177, 0, 5588);   
REPLACE INTO char_templates VALUES (25, "Elf Mage", 1, 21, 25, 24, 37, 23, 40, 2, 48, 6, 54, 312,   
386, 30, 41, 30, 122, 62400, 46182, 41198, -3440, 0, "1.04", "0.89856", "7.5", 24, "1.04", "0.89856",   
"7.5", 23, 1105, 1102, 177, 0, 5588);   
REPLACE INTO char_templates VALUES (38, "DE Mage", 2, 23, 24, 23, 44, 19, 37, 2, 48, 7, 53, 309,   
316, 29, 41, 29, 122, 61000, 28295, 11063, -4224, 0, "1.14", "1.2312", "7.5", 24, "1.03", "0.88992", 7,   
"23.5", 1105, 1102, 177, 0, 5588);   
REPLACE INTO char_templates VALUES (49, "Orc Mage", 3, 27, 31, 24, 31, 15, 42, 2, 48, 4, 56, 312,   
265, 30, 41, 30, 121, 68000, -56682, -113730, -690, 0, "1.04", "0.89856", 7, "27.5", "1.04", "0.89856",   
8, "25.5", 1105, 1102, 257, 0, 5588);

Оставляем в дропе ТОЛЬКО адену:

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

Шанс дропа адены 100%:

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

Итемы в магазинах по 1 адене:

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

Убрать вес:

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 in (1674,1675,1676,1677,1678,1679,1680,1681,1682,1683 ,1684,1685,1686,1687,1688,1689,1690,1691,1692,1693 ,1694,1695,1696,1697,1698,1699,1700,1701,1702,1703 ,1704,1705,1706,1707,1708,1709,1710,1711,1712,1713 ,1714,1715,1716,1717,1718,1719,1720,1721,1722,1723 ,1724,1725,1726,1727,1728,1729,1730,1731,1732,1733 ,1734,1735,1736,1737,1738,1739,1740,1741,1742,1743 ,1744,1745,1746,1747,1748,1749,1750,1751,1752,1753 ,1754,1755,1756,1757,1758,1759,1760,1761,1762,1763 ,1764,1765,1766,1767,1768,1769,1770,1771,1772,1773 ,1774,1775,1776,1777,1778,1779,1780,1781,1782,1783 ,1784,1785,1864,1865,1866,1867,1868,1869,1870,1871 ,1872,1873,1874,1875,1876,1877,1878,1879,1880,1881 ,1882,1883,1884,1885,1886,1887,1888,1889,1890,1891 ,1892,1893,1894,1895,1896,1897,1898,1899,1900,1901 ,1902,1903,1904,1905,1906,1907,1908,1909,1910,1911 ,1912,1913,1914,1915,1916,1917,1918,1919,1920,1921 ,1922,1923,1924,1925,1926,1927,1928,1929,1930,1931 ,1932,1933,1934,1935,1936,1937,1938,1939,1940,1941 ,1942,1943,1944,1945,1946,1947,1948,1949,1950,1951 ,1952,1953,1954,1955,1956,1957,1958,1959,1960,1961 ,1962,1963,1964,1965,1966,1967,1968,1969,1970,1971 ,1972,1973,1974,1975,1976,1977,1978,1979,1980,1981 ,1982,1983,1984,1985,1986,1987,1988,1989,1990,1991 ,1992,1993,1994,1995,1996,1997,1998,1999,2000,2001 ,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011 ,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021 ,2022,2023,2024,2025,2026,2027,2028,2029,2030,2031 ,2032,2033,2034,2035,2036,2037,2038,2039,2040,2041 ,2042,2043,2044,2045,2046,2047,2048,2049,2050,2051 ,2052,2053,2054,2055,2056,2057,2058,2059,2060,2061 ,2062,2063,2064,2065,2066,2067,2068,2069,2070,2071 ,2072,2073,2074,2075,2076,2077,2078,2079,2080,2081 ,2082,2083,2084,2085,2086,2087,2088,2089,2090,2091 ,2092,2093,2094,2095,2096,2097,2098,2099,2100,2101 ,2102,2103,2104,2105,2106,2107,2108,2109,2110,2111 ,2112,2113,2114,2115,2116,2117,2118,2119,2120,2121 ,2122,2123,2124,2125,2126,2127,2128,2129,2130,2131 ,2132,2133,2134,2508,2917,2918,2919,2920,2921,2922 ,2923,2924,2925,2926,2927,2928,2929,2930,2931,2932 ,2933,2934,2935,2936,2937,2938,2939,2940,2941,2942 ,2943,2944,2945,2946,2947,2948,2949,2950,2951,2952 ,2953,2954,2955,2956,2957,2958,2959,2960,2961,2962 ,2963,2964,2965,2966,2967,2968,2969,3031,4039,4040 ,4041,4042,4043,4044,4045,4046,4047,4048,4049,4050 ,4051,4052,4053,4054,4055,4056,4057,4058,4059,4060 ,4061,4062,4063,4064,4065,4066,4067,4068,4069,4070 ,4071,4072,4073,4074,4075,4076,4077,4078,4079,4080 ,4081,4082,4083,4084,4085,4086,4087,4088,4089,4090 ,4091,4092,4093,4094,4095,4096,4097,4098,4099,4100 ,4101,4102,4103,4104,4105,4106,4107,4108,4109,4110 ,4111,4112,4113,4114,4115,4116,4117,4118,4119,4120 ,4121,4438,4439,5220,5478,5479,5480,5481,5482,5483 ,5484,5485,5486,5487,5488,5489,5490,5491,5492,5493 ,5494,5495,5496,5497,5498,5499,5500,5501,5502,5503 ,5504,5505,5506,5507,5508,5509,5510,5511,5512,5513 ,5514,5515,5516,5517,5518,5519,5520,5521,5522,5523 ,5524,5525,5526,5527,5528,5529,5530,5531,5532,5533 ,5534,5535,5536,5537,5538,5539,5540,5541,5542,5543 ,5544,5545,5546,5547,5548,5549,5550,5551,5552,5553 ,5554,5556,5557,5558,5559,5757,5758,5759,5760,5761 ,5762,5763,5764,6341,6342,6343,6344,6345,6346,6688 ,6689,6690,6691,6692,6693,6694,6695,6696,6697,6698 ,6699,6700,6701,6702,6703,6704,6705,6706,6707,6708 ,6709,6710,6711,6712,6713,6714,6904,6905,6906,6907 ,6908,6909,6910,6911,6912,6913,6914,6915,6916,7579 ,7684,7685,7686,7687,7688,7697,7698,8327,8328,8329 ,8330,8331,8332,8333,8334,8335,8336,8337,8338,8339 ,8340,8341,8342,8343,8344,8345,8346,8347,8348,8349 ,8351,8352,8353,8354,8355,8356,8357,8358,8359,8373 ,8374,8375,8376,8377,8378,8553,8658,8712,8713,8714 ,8715,8716,8717,8718,8719,8720,8721,8722,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,8764,8773 ,8777,8778,8781,8783,8784,8787,8868,8873,8874,8875 ,8876,9168,9169,9170,9530,9531,9532,9533,9534,9535 ,9536,9537,9538,9539,9540,9541,9542,9543,9544,9545 ,9573,9574,9575,9576,9616,9617,9618,9619,9620,9621 ,9622,9623,9624,9628,9629,9630,9631,9675,9693,9785 ,9991,9992,9993,10272,10397,10398,10399,10400,1040 1,10402,10403,10404,10405,10406,10407,10483,10484, 10485,10486,10546,10547);

Удаляем нпц (в том числе мобов) выше 51 лвл с респа (не удаляя их с сервака). ЛВЛ моба выставляет в конце запросов.

DELETE spawnlist FROM spawnlist, npc WHERE spawnlist.npc_templateid=npc.idTemplate AND npc.level<51;

Дроп бижи у эпик боссов:

INSERT INTO droplist VALUES ('29001', '6660', '1', '1', '12', '300000');
INSERT INTO droplist VALUES ('29006', '6662', '1', '1', '8', '300000');
INSERT INTO droplist VALUES ('29014', '6661', '1', '1', '9', '300000');
INSERT INTO droplist VALUES ('29022', '6659', '1', '1', '13', '1000000');
INSERT INTO droplist VALUES ('29020', '6658', '1', '1', '2', '1000000');
INSERT INTO droplist VALUES ('29019', '6656', '1', '1', '31', '1000000');
INSERT INTO droplist VALUES ('29028', '6657', '1', '1', '34', '1000000');

вpeмя pecпaвнa PeйдБoccoв:
мин.вpeмя - 1 чac
мaкc.вpeмя - 2 чaca

UPDATE `raidboss_spawnlist` SET `respawn_min_delay` = '3600';
UPDATE `raidboss_spawnlist` SET `respawn_max_delay` = '7200';

Ссылки по теме:
Уроки по sql
_http://sql-ex.ru/
Работа с SQL – Запросы
_http://kbyte.ru/articles/ShowArticle.aspx?id=47〈=ru
Книги по sql:
_http://books.kulichki.com/index.php?book=sql

(с)
P.S если была такая тема - удалите плз!

[Ankoman]

мб ктонить знает как сделать SQL запрос чтобы дать чару выбраный итем

[GraNIT]

Точный запрос не помню, но через таблицу ITEMS, если в инете запроса не найдешь - возьми скрипт для выдачи бонуса l2top: в скрипте будет написан запрос для выдачи бонуса и изменяй его под себя=)

[-Roman-]

Нафиг в кансоль лезть ?
Создаеш блакнотом обычный текстовик , пишеш в него запросы , сохроняеш , меняеш расширение txt на sql , в навикети выбераеш базу куда будеш заливать, тыкаеш Execute Batch File , выдераем наш текставик , мыкаем старт , если в запросе есть ошибка то навикет напишет где или в чем

[Ant1noob]

Цитата: Сообщение от -Roman- Нафиг в кансоль лезть ? Создаеш блакнотом обычный текстовик , пишеш в него запросы , сохроняеш , меняеш расширение txt на sql , в навикети выбераеш базу куда будеш заливать, тыкаеш Execute Batch File , выдераем наш текставик , мыкаем старт , если в запросе есть ошибка то навикет напишет где или в чем

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