数据库常用的SQL语句及应用实例一、数据库常用命令Select ——从数据库对象中检索行update ——修改现有数据delete ——删除表中的行drop ——删除数据库中的表sp_who ——获取数据库当前活动用户的连接信息sp_help ——获取当前数据库中的数据库对象信息sp_helpdb ——获取数据库的相关信息（数据库名、总空间、剩余空间、设备使用情况、数据库选项等）sp_helpdevice ——获取数据库设备信息（设备名、物理路径、大小等）kill ——杀数据库连接sp_monitor ——获取数据库服务器从上次运行sp_monitor以来的CPU、IO等统计信息sp_dboption ——查看或设置数据选型sp_configure ——查看或设置数据库配置信息sp_spaceused ——查看数据库中某个表的记录数和占用的存储空间select name from sysobjects where type="U" order by name ——可以查出当前数据库中的所有表名，条件中的"U"表示用户表，其它的"P"表示存储过程，"TR"表示触发器，"V"表示视图，"R"表示规则select @@version ——从数据库中获取当前使用数据库的版本号sp_configure "memory Use" ——观察数据库内内存的分配情况select * from ManaClerk ——查询密码truncate table HisData1 ——清除数据空间二、数据库参数调整语句use mastergosp_configure "disable character set conversion",1gosp_configure 'max memory',320000gosp_configure 'number of locks',30000gosp_configure 'number of user connections',200gosp_configure 'number of devices',50gosp_configure 'procedure cache size',25000gosp_configure 'max online engines', 1gosp_cacheconfig 'default data cache','200M'go三、设置PSMS439数据库选项sp_dboption 'psms439', 'abort tran on', 'true'gosp_dboption 'psms439', 'select into/bulkcopy/pllsort', 'true' gosp_dboption 'psms439', 'trunc log on chkpt', 'true'go四、设置数据行锁语句use psms439goalter table CfgStation lock datarowsgoalter table CfgEquipment lock datarowsgoalter table CfgSignal lock datarowsgoalter table CfgWorkStation lock datarowsgoalter table CfgPort lock datarowsgoalter table CfgSamplerUnit lock datarowsgoalter table RunAlarmMsge lock datarowsgoalter table AlarmChange lock datarowsgoalter table ControlQueue lock datarowsgoalter table logrs lock datarowsgo五、为热点表创建命名缓存语句sp_cacheconfig 'CfgSignal_Cache','45M'gosp_bindcache CfgSignal_Cache, psms439, CfgSignalgosp_cacheconfig 'CfgStation_Cache','5M'gosp_bindcache CfgStation_Cache, psms439, CfgStation gosp_cacheconfig 'CfgEquipment_Cache','5M'gosp_bindcache CfgEquipment_Cache, psms439, CfgEquipmentgosp_cacheconfig 'CfgWorkStation_Cache','2M'gosp_bindcache CfgWorkStation_Cache, psms439, CfgWorkStationgosp_cacheconfig 'CfgSamplerUnit_Cache','5M'gosp_bindcache CfgSamplerUnit_Cache, psms439, CfgSamplerUnitgosp_cacheconfig 'RunAlarmMsge_Cache','5M'gosp_bindcache RunAlarmMsge_Cache, psms439, RunAlarmMsgegosp_cacheconfig 'AlarmChange_Cache','5M'gosp_bindcache AlarmChange_Cache, psms439, AlarmChangegosp_cacheconfig 'ControlQueue_Cache','2M'gosp_bindcache ControlQueue_Cache, psms439, ControlQueuegosp_cacheconfig 'logrs_Cache','45M'gosp_bindcache logrs_Cache, psms439, logrsgo六、数据库历史数据优化1、使用SqlDbx-NX.exe连接数据库后，执行SQL语句：IF EXISTS(SELECT 1 FROM sysobjects WHERE name='test' AND type='V') DROP VIEW testGOCREATE VIEW testASSELECTB.StationID,B.StationName,C.EquipmentID,C.EquipmentName,D.SignalID,D.SignalNa me,A.RecordTime,D.SpanTime,D.SpanValueFROM HisData A,CfgStation B,CfgEquipment C,CfgSignal DWHERE A.StationID=B.StationIDAND A.StationID=C.StationIDAND A.StationID=D.StationIDAND A.EquipmentID=C.EquipmentIDAND A.EquipmentID=D.EquipmentIDAND A.SignalID=D.SignalIDAND A.RecordTime<'2010-10-21' AND A.RecordTime>'2010-10-20'goSELECT DISTINCT StationID,StationName,EquipmentID,EquipmentName,SignalID,SignalName,SpanTim e,SpanValue,count(*) AS CntFROM testGROUP BY StationID,EquipmentID,SignalIDORDER BY Cnt DESCgoDROP VIEW testGO2、把数据导出excel表。

把数据量异常大的信号找出来进行分析。

3、按存储阀值进行分类，对历史数据进行分析(1)优化1/1/1①交流开关信号SELECT * FROM CfgSignal WHERE SignalName LIKE '交流开关%' AND SpanValue ='1/1/1'UPDATE CfgSignal SET SpanValue ='0/0/0' WHERE SignalName LIKE '交流开关%' AND SpanValue ='1/1/1'②整流屏－线电压SELECT * FROM CfgSignal WHERE SignalName LIKE '%线电压' AND EquipmentID/10000=22 AND SpanValue ='1/1/1'UPDATE CfgSignal SET SpanValue ='30/30/30' WHERE SignalName LIKE '%线电压' AND EquipmentID/10000=22③模块限流状态SELECT * FROM CfgSignal WHERE SignalName LIKE '%限流状态' AND EquipmentID/10000=22 AND SpanValue ='1/1/1'UPDATE CfgSignal SET SpanValue ='0/0/0' WHERE SignalName LIKE '%限流状态' AND EquipmentID/10000=22④模块开关状态SELECT * FROM CfgSignal WHERE SignalName LIKE '%模块%开关状态' AND EquipmentID/10000=22 AND SpanValue ='1/1/1'UPDATE CfgSignal SET SpanValue ='0/0/0' WHERE SignalName LIKE '%模块%开关状态' AND EquipmentID/10000=22⑤输入相电压SELECT * FROM CfgSignal WHERE SignalName LIKE '%输入相电压%' AND SignalName not LIKE '%限%' AND EquipmentID/10000=22 ANDSignalName NOT LIKE '%告警状态%' AND SpanValue ='1/1/1'UPDATE CfgSignal SET SpanValue ='30/30/30' WHERE SignalName LIKE '%输入相电压%' AND SignalName not LIKE '%限%' AND EquipmentID/10000=22 AND SignalName NOT LIKE '%告警状态%'⑥输入相电压上下限SELECT * FROM CfgSignal WHERE SignalName LIKE '%输入相电压%' AND SignalName LIKE '%限%' AND EquipmentID/10000=22 ANDSignalName NOT LIKE '%告警状态%' AND SpanValue ='1/1/1'UPDATE CfgSignal SET SpanValue ='0/0/0' WHERE SignalName LIKE '%输入相电压%' AND SignalName LIKE '%限%' AND EquipmentID/10000=22 AND SignalName NOT LIKE '%告警状态%'⑦模块限流状态SELECT * FROM CfgSignal WHERE SignalName LIKE '%模块%限流状态%' AND EquipmentID/10000=22 AND SpanValue ='1/1/1'UPDATE CfgSignal SET SpanValue ='0/0/0' WHERE SignalName LIKE '%模块%限流状态%' AND EquipmentID/10000=22⑧浮充均充状态SELECT * FROM CfgSignal WHERE SignalName LIKE '%浮%均%状态%' ANDEquipmentID/10000=22 AND SpanValue ='1/1/1'UPDATE CfgSignal SET SpanValue ='0/0/0' WHERE SignalName LIKE '%浮%均%状态%' AND EquipmentID/10000=22⑨输出开关状态SELECT * FROM CfgSignal WHERE SignalName LIKE '%输出开关%状态%' AND EquipmentID/10000=22 AND SignalName NOT LIKE '%断%'AND SpanValue ='1/1/1'UPDATE CfgSignal SET SpanValue ='0/0/0' WHERE SignalName LIKE '%输出开关%状态%' AND EquipmentID/10000=22AND SignalName NOT LIKE '%断%'(2)优化0.1/0.1/0.1①UPS输入相电压SELECT * FROM CfgSignal WHERE SignalName LIKE '%输入相电压%' AND SignalName not LIKE '%限%' AND SignalName not LIKE '%限%'AND SignalName NOT LIKE '%告警状态%' AND EquipmentID /10000=31 UPDATE CfgSignal SET SpanValue ='30/30/30' WHERE SignalName LIKE '%输入相电压%' AND SignalName not LIKE '%限%' AND SignalName not LIKE '%限%'AND SignalName NOT LIKE '%告警状态%' AND EquipmentID /10000=31 UPDATE CfgSignal SET SpanTime ='28800/28800/28800' WHERE SignalName LIKE '%输入相电压%' AND SignalName not LIKE '%限%' AND SignalName not LIKE '%限%' AND SignalName NOT LIKE '%告警状态%' AND EquipmentID /10000=31②交流输入SELECT * FROM CfgSignal WHERE (SignalName LIKE '%交流输入%U%' OR SignalName LIKE '%交流输入%电压%')AND SignalName NOT LIKE '%告警状态%' AND SignalName NOT LIKE '%限%' AND SignalName NOT LIKE '%缺相%'AND SignalName NOT LIKE '%高%' AND SignalName NOT LIKE '%低%' UPDATE CfgSignal SET SpanValue ='30/30/30',SpanTime ='28800/28800/28800' WHERE (SignalName LIKE '%交流输入%U%' OR SignalName LIKE '%交流输入%电压%')AND SignalName NOT LIKE '%告警状态%' AND SignalName NOT LIKE '%限%' AND SignalName NOT LIKE '%缺相%'AND SignalName NOT LIKE '%高%' AND SignalName NOT LIKE '%低%'③电源-模块%输出电压保护点SELECT * FROM CfgSignal WHERE SignalName LIKE '%模块%输出电压保护点%'AND EquipmentID/10000=22 AND SpanValue ='0.1/0.1/0.1'UPDATE CfgSignal SET SpanValue ='0/0/0' WHERE SignalName LIKE '%模块%输出电压保护点%' AND EquipmentID/10000=22④电源-总负载电源SELECT * FROM CfgSignal WHERE (SignalName LIKE '%负载总电流%' OR SignalName LIKE '%总负载电流%') AND EquipmentID/10000=22AND SpanValue ='0.1/0.1/0.1'UPDATE CfgSignal SET SpanValue ='5/5/5',SpanTime ='28800/28800/28800'WHERE (SignalName LIKE '%负载总电流%' OR SignalName LIKE '%总负载电流%') AND EquipmentID/10000=22⑤温度SELECT * FROM CfgSignal WHERE SignalName LIKE '%温度' AND EquipmentID /10000=51 AND SpanValue ='0.1/0.1/0.1'SELECT * FROM CfgSignal WHERE SignalName LIKE '%温度' AND EquipmentID /10000=43 AND SpanValue ='0.1/0.1/0.1'SELECT * FROM CfgSignal WHERE SignalName LIKE '%温度' AND EquipmentID /10000=22 AND SpanValue ='0.1/0.1/0.1'UPDATE CfgSignal SET SpanValue ='2/2/2',SpanTime ='28800/28800/28800' WHERE SignalName LIKE '%温度' AND EquipmentID /10000=51 AND SpanValue ='0.1/0.1/0.1' UPDATE CfgSignal SET SpanValue ='2/2/2',SpanTime ='28800/28800/28800' WHERE SignalName LIKE '%温度' AND EquipmentID /10000=43 AND SpanValue ='0.1/0.1/0.1' UPDATE CfgSignal SET SpanValue ='0/0/0',SpanTime ='28800/28800/28800' WHERE SignalName LIKE '%温度' AND EquipmentID /10000=22 AND SpanValue ='0.1/0.1/0.1'⑥湿度SELECT * FROM CfgSignal WHERE SignalName LIKE '%湿度%' AND EquipmentID /10000=51 AND SpanValue ='0.1/0.1/0.1'SELECT * FROM CfgSignal WHERE SignalName LIKE '%湿度%' AND EquipmentID /10000=22 AND SpanValue ='0.1/0.1/0.1'UPDATE CfgSignal SET SpanValue ='2/2/2',SpanTime ='28800/28800/28800' WHERE SignalName LIKE '%湿度%' AND EquipmentID /10000=51 AND SpanValue ='0.1/0.1/0.1'UPDATE CfgSignal SET SpanValue ='5/5/5',SpanTime ='28800/28800/28800' WHERE SignalName LIKE '%湿度%' AND EquipmentID /10000=22 AND SpanValue ='0.1/0.1/0.1'(3)优化1/1/0①模块开关、限流、均浮充状态上面已优化，总负载电流上面也已优化②模块控制状态SELECT * FROM CfgSignal WHERE SignalName LIKE '%模块%控制%状态%' AND EquipmentID /10000=22AND SignalName NOT LIKE '%告警%'UPDATE CfgSignal SET SpanValue ='0/0/0' WHERE SignalName LIKE '%模块%控制%状态%' AND EquipmentID /10000=22AND SignalName NOT LIKE '%告警%'③模块风扇状态SELECT * FROM CfgSignal WHERE SignalName LIKE '%模块%风扇%' AND EquipmentID /10000=22AND SignalName NOT LIKE '%告警%'UPDATE CfgSignal SET SpanValue ='0/0/0' WHERE SignalName LIKE '%模块%风扇%' AND EquipmentID /10000=22AND SignalName NOT LIKE '%告警%'(4)优化0/.1/0①频率SELECT * FROM CfgSignal WHERE SignalName LIKE '%频率%'SELECT * FROM CfgSignal WHERE SignalName LIKE '%频率%' AND (EquipmentID /10000=13 OR EquipmentID /10000=31)UPDATE CfgSignal SET SpanValue ='2/2/2' WHERE SignalName LIKE '%频率%' UPDATE CfgSignal SET SpanValue ='1/1/1' WHERE SignalName LIKE '%频率%' AND (EquipmentID /10000=13 OR EquipmentID /10000=31)(5)优化0/5/0①电源模块温度在优化0.1/0.1/0.1时已做了该部分优化。