RM数据库研究（二）新建配置表以及常用sql语句

--SQL Server 2005不支持一条insert语句插入多条记录，2008是支持的。 --sql server 2008 r2正常,但是sql server 2005报错 --INSERT INTO [dbo].[HY_Items] ([id], [name], [remarks])VALUES (1,'赛利安',''),(2,'塔马斯','') /****** Script for SelectTopNRows command from SSMS  ******/ USE [redmoon] GO /****** Object:  Table [dbo].[HY_Items]    Script Date: 08/28/2024 14:37:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO if exists (select * from sysobjects where name='HY_Items')     drop table [dbo].[HY_Items] go -- ============================================= -- Author:      HY -- Create date: 2024年08月28日15:54:29 -- Description: 物品名列表 -- 115行 -- ============================================= CREATE TABLE [dbo].[HY_Items]( [id] [int] NOT NULL,--物品id [name] [varchar](32) NOT NULL,--物品名称 [remarks] [varchar](128) NOT NULL DEFAULT (''),--备注 PRIMARY KEY CLUSTERED  ( [id] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Script for SelectTopNRows command from SSMS  ******/ USE [redmoon] GO /****** Object:  Table [dbo].[HY_Maps]    Script Date: 08/28/2024 14:37:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO if exists (select * from sysobjects where name='HY_Maps')     drop table [dbo].[HY_Maps] go -- ============================================= -- Author:      HY -- Create date: 2024年08月28日15:54:29 -- Description: 地图名列表 -- 120行 -- ============================================= CREATE TABLE [dbo].[HY_Maps]( [id] [int] NOT NULL,--地图id,服务端口port=id＋10120 [name] [varchar](32) NOT NULL,--地图名称 [remarks] [varchar](128) NOT NULL DEFAULT (''),--备注 PRIMARY KEY CLUSTERED  ( [id] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO --查当前时间 2024-09-07 00:01:41.403 select getdate() --查角色发出的邮件 select * FROM [redmoon].[dbo].[tblMail1]WITH(NOLOCK) where [Sender]='0707' order by Time desc --查角色收到的邮件 select * FROM [redmoon].[dbo].[tblMail1]WITH(NOLOCK) where [Recipient]='juese1' order by Time desc select * FROM [redmoon].[dbo].[tblMail1]WITH(NOLOCK) order by Time desc SELECT [ID]       ,[ItemKind]       ,[ItemIndex]       ,[ItemDurability]       ,[Position]       ,[Map]       ,[X]       ,[Y]       ,[TileKind]       ,[GameID]       ,[WindowKind]       ,[WindowIndex]       ,[MiscTime]       ,[AttackGrade]       ,[StrengthGrade]       ,[SpiritGrade]       ,[DexterityGrade]       ,[PowerGrade]       ,[Grow_Plus_1]       ,[Grow_Plus_2]   FROM [redmoon].[dbo].[tblSpecialItem1]WITH(NOLOCK)where [ItemIndex]=200 and GameID='随缘1' order by id desc --查角色指定物品数量 200-生命水 53个 select count(1)  FROM [redmoon].[dbo].[tblSpecialItem1]WITH(NOLOCK)where [ItemIndex]=200 and GameID='随缘1' --后台工具发14个到物品栏后 67个 [MiscTime]=2024-09-06 22:57:11.353 --查角色指定物品数量 200-生命水  --juese1 300个 select count(1)  FROM [redmoon].[dbo].[tblSpecialItem1]WITH(NOLOCK)where [ItemIndex]=200 and GameID='juese1' update [redmoon].[dbo].[tblSpecialItem1] set GameID='juese1' where [GameID]='0348' update [redmoon].[dbo].[tblSpecialItem1] set GameID='juese2' where [GameID]='0349' update [redmoon].[dbo].[tblSpecialItem1] set GameID='juese3' where [GameID]='0350' update [redmoon].[dbo].[tblBillID] set [Password]='2024han' where [BillID]='han2024' --只能在测试环境执行，千万别在正式环境执行 update [redmoon].[dbo].[tblBillID] set [Password]='123456' --查询指定物品日志 62条记录 SELECT TOP 1000 [LogID]       ,[Time]       ,[LogKind]       ,[LogItemCount]       ,A.[ID]       ,[ItemKind]       ,[ItemIndex],B.name,B.[remarks]       ,[ItemDurability]       ,[Position]       ,[Map]       ,[X]       ,[Y]       ,[TileKind]       ,[GameID]       ,[WindowKind]       ,[WindowIndex]       ,[MiscTime]       ,[AttackGrade]       ,[StrengthGrade]       ,[SpiritGrade]       ,[DexterityGrade]       ,[PowerGrade]   FROM [redmoon].[dbo].[tblSpecialItemLog1]A WITH(NOLOCK) left join [redmoon].[dbo].[HY_Items] B WITH(NOLOCK) on A.[ItemIndex]=B.ID where A.[ItemIndex] in (168,193) --查指定物品 or 生化物品 SELECT TOP 1000 A.[ID]       ,[ItemKind]       ,[ItemIndex],B.name,B.[remarks]       ,[ItemDurability]       ,[Position]       ,[Map]       ,[X]       ,[Y]       ,[TileKind]       ,[GameID]       ,[WindowKind]       ,[WindowIndex]       ,[MiscTime]       ,[AttackGrade]       ,[StrengthGrade]       ,[SpiritGrade]       ,[DexterityGrade]       ,[PowerGrade]       ,[Grow_Plus_1]       ,[Grow_Plus_2]   FROM [redmoon].[dbo].[tblSpecialItem1]A WITH(NOLOCK) left join [redmoon].[dbo].[HY_Items] B WITH(NOLOCK) on A.[ItemIndex]=B.ID where A.[ItemIndex] in (168,193) or charindex('生化',B.name)>0

SELECT TOP 1000 [Version]       ,[GameID]       ,[BillID]       ,[Lvl]       ,[Face]       ,[Map],B.name,Map+10120 as port       ,[X]       ,[Y]       ,[TileKind]       ,[Item]       ,[Equipment]       ,[Skill]       ,[SpecialSkill]       ,[Strength]       ,[Spirit]       ,[Dexterity]       ,[Power]       ,[Fame]       ,[Experiment]       ,[HP]       ,[MP]       ,[SP]       ,[DP]       ,[Bonus]       ,[Money]       ,[QuickItem]       ,[QuickSkill]       ,[QuickSpecialSkill]       ,[BankMoney]       ,[BankItem]       ,[SETimer]       ,[PKTimer]       ,[Color1]       ,[Color2]       ,[PoisonUsedDate]       ,[LovePoint]       ,[ArmyHired]       ,[ArmyMarkIndex]       ,[Permission]       ,[BonusInitCount]       ,[StoryQuestState]       ,[QuestItem]       ,[SubQuestKind]       ,[SubQuestDone]       ,[SubQuestClientNPCID]       ,[SubQuestClientNPCFace]       ,[SubQuestClientNPCMap]       ,[SubQuestItem]       ,[SubQuestDestFace]       ,[SubQuestDestMap]       ,[SubQuestTimer]       ,[SubQuestGiftExperience]       ,[SubQuestGiftFame]       ,[SubQuestGiftItem]       ,[OPArmy]       ,[OPPKTimer]       ,[SurvivalEvent]       ,[SurvivalTime]       ,[Bonus2]       ,[SBonus]       ,[STotalBonus]       ,[PKPenaltyCount]       ,[PKPenaltyDecreaseTimer]       ,[SigMoney]       ,[BankSigMoney]       ,[BankItem2]       ,[TLETimer]   FROM [redmoon].[dbo].[tblGameID1] A WITH(NOLOCK) left join [redmoon].[dbo].[HY_Maps] B WITH(NOLOCK) on A.[Map]=B.ID --钻石ZSSL=2501 update [redmoon].[dbo].[tblGameIDRebirth] set ZS1=2500,ZS2=200,ZS3=201 where gameid='juese2' 角色属性 update [redmoon].[dbo].[tblGameID1] set [Lvl]=1,Map=47,[Equipment]='',[Experiment]=0,Bonus=2,[Money]=4866054,[BankMoney]=49965968,[Strength]=100,[Spirit]=200,[Dexterity]=300,[Power]=400 where gameid='juese2' --[Item]物品栏物品(不含特装和生化)0:3-50/1.   离开游戏后才会回写数据库 --[QuickItem]快捷栏物品(不含特装和生化)0:3-50/1.1:4-36/999. --[Equipment]装备栏物品(不含特装和生化) 1~1000级菲的装备栏物品： 84级 3-50意念项链 3-133黄金戒指+3 2-25青铜帽 2-4丝衬衫 2-18丝裤 2-62青铜靴 2-52青铜手套 2-74青铜腰带 91级 3-142珍珠戒指+2 708级 6-90智慧之泪 6-92智慧项链[智慧女神的祝福] 2-104C系列头盔 2-128C系列腰带 2-112C系列裤子 2-116C系列靴子[C系列长靴] 2-49月光保护具 2-100月光盾牌 2-59月光手套 1~1000级阿的装备栏物品： 370级 3-50意念项链 2-18丝裤 2-29迷斯利帽 3-222迪凡斯戒指+2 2-78迷斯利腰带 2-93迷斯利盾牌 2-56迷斯利手套 2-64银靴 --测试物品字典表 208~211、212~215、   update [redmoon].[dbo].[tblSpecialItem1] set itemIndex=id-78282+216 where id>=78282 and id<=78285   select * FROM [redmoon].[dbo].[tblSpecialItem1](nolock) where gameid='juese2'   --复制记录行 INSERT INTO [redmoon].[dbo].[tblSpecialItem1]  SELECT [ItemKind],202 as [ItemIndex],[ItemDurability],[Position],[Map],[X],[Y],[TileKind],[GameID],[WindowKind],1 as [WindowIndex],[MiscTime],[AttackGrade],[StrengthGrade],[SpiritGrade],[DexterityGrade],[PowerGrade] FROM [redmoon].[dbo].[tblSpecialItem1](nolock) where id=78283