Полезные Sql запросы для Lineage сервера

[Twain]

1. Удаление всех вещей с определённым ID у определнного персонажа.

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

2. Продажа всего по 1 адене.

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

3. Показ всех вещей определённого чара.

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=идчара;

4. Убирает вес.

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

5. Дропаются только адены.

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

6. Шанс дропа аден 100% вместо 70%.

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

7. Удаляем всё, кроме вещей с ID 57, 77, 107.

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

8. Нубы в лучшем No grade...

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", 33, 27, 219, 68, 625);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, 33, 27, 219, 68, 625);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", 33, 27, 219, 68, 625);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, 33, 27, 219, 257, 625);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, 33, 27, 219, 68, 625);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, 68, 625);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, 68, 625);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, 68, 625);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, 177, 257, 625);

9.Удаляет из дроплиста куски, ресурсы и т.д

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);

10.Нубы в ТОП

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);

 

[zefi]

В шестом запросе первая буква потерялась

 

[Twain]

@zefi,
Исправил, спасибо что заметил.