程序设计 – Iawen's Blog

2015年8月14日-0001年11月30日

批量删除MS SQL中的表


//批量删除MS SQL中的表
DECLARE @tablename VARCHAR(100)
DECLARE @sql VARCHAR(500)
DECLARE cur_delete_table CURSOR READ_ONLY FORWARD_ONLY FOR

SELECT name FROM sysobjects WHERE name LIKE 'BSTemp%' AND type='U'
OPEN cur_delete_table
FETCH NEXT FROM cur_delete_table INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @sql='DROP TABLE '+@tablename
  EXEC (@sql)
  FETCH NEXT FROM cur_delete_table INTO @tablename
END
CLOSE cur_delete_table
DEALLOCATE cur_delete_table

如果是视图，则type=’V’，同时DROP TABLE改为DROP VIEW

2015年8月13日-0001年11月30日

oracle数据库还原,如何将dmp文件还原到oralce库

1、清理以前还原过的痕迹,如果我们在数据库曾经还原过,我们先来清理一下,痕迹,

//删除用户
DROP USER xxxx CASCADE;
//删除表空间
DROP TABLESPACE xxxx;
//删除数据库文件
(数据库所在路径)xxxxxx.dbf

继续阅读“oracle数据库还原,如何将dmp文件还原到oralce库”

2015年7月9日-0001年11月30日

MS SQL行转一列

MS SQL多行转列示例：


SELECT 
  CASE 
    WHEN DeptName='广州滨江东' THEN 'S49'
    WHEN DeptName='广州太古汇' THEN 'CS01'
    WHEN DeptName='广州天河城' THEN 'S48'
    WHEN DeptName='广州五月花' THEN 'CS05' 
  END sh_code,
  rtrim(convert(varchar(10),CARDID)) +'-' +rtrim(EmployeeName) AS staff_id,
  rtrim(convert(varchar(10),CARDID)) +'-' +rtrim(EmployeeName) AS staff_name,
  AddTime sign_date,
  [1] as in_time1, [3] as in_time2, [5] as in_time3, [7] as in_time4, 
  [2] as out_time1,[4] as out_time2,[6] as out_time3,[8] as out_time4,
  '' AS remark 
FROM 
(
  SELECT 
    row_number() over (partition by CardID, CONVERT(VARCHAR(10), AddTime, 120) order by id) AS ID, 
    CardID, EmployeeName, AttDateTime, DeptName,
    CONVERT(VARCHAR(10), AddTime, 120) AddTime
  FROM View_Staff_sgin
) AS SourceTable
 PIVOT ( MAX(AttDateTime) FOR ID IN ([1],[2],[3],[4],[5],[6],[7],[8]) ) AS PivotTable
 WHERE 
  AddTime ='2015-03-21'

最主要的地方是用到了PIVOT语法，另外就是结合row_number()函数。

不过，确实为这个打卡表的设计感到郁闷！

2015年7月8日-0001年11月30日

执行环境：
可以在SQLPLUS.EXE或者DOS（命令行）中执行，
DOS中可以执行时由于在oracle 8i 中安装目录\ora81\BIN被设置为全局路径，
该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。
oracle用java编写，我想SQLPLUS.EXE、EXP.EXE、IMP.EXE这俩个文件是被包装后的类文件。
SQLPLUS.EXE调用EXP.EXE、IMP.EXE他们所包裹的类，完成导入导出功能。继续阅读“oracle数据库的备份与还原（本地及远程操作）”

2015年7月4日2017年3月11日

MFC通过ADO连接Oracle数据库

程序的代码如下：
1、在”StdAfx.h”文件中导入库：

#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename ("EOF", "adoEOF")

2、在类里添加成员变量：

_ConnectionPtr m_pCnn;

3、连接数据库：

m_pCnn.CreateInstance(__uuidof(Connection));
try{
m_pCnn->ConnectionTimeout = 5;                          //设置连接时间
m_pCnn->Open(_bstr_t(sConn),strUserID,strPwd,adModeUnknown);
return true;
}
catch(_com_error e){
AfxMessageBox(e.Description());
return false;
}

在调试时如果出现以下错误：继续阅读“MFC通过ADO连接Oracle数据库”

2015年7月3日-0001年11月30日

MS SQL的服务器连接、远程同步数据

MS SQL的服务器连接的创建：


exec sp_addlinkedserver @server='服务器连接名',@SRVPRODUCT='连接的数据库名',@PROVIDER=N'SQLOLEDB',@DATASRC='POSSERVER DB的IP'
EXEC SP_ADDLINKEDSRVLOGIN '服务器连接名','FALSE',NULL,'登录名','登录密码'

从远程的MS SQL同步数据到本地：


INSERT INTO 数据库表名
SELECT *
FROM  OPENDATASOURCE(
  'SQLOLEDB',
  'Data Source=远程IP;User ID=用户名;Password=用户密码'
).数据库名.dbo.数据库表名

MS SQL数据的格式化导入与导出


bcp Beauty_Developer..LOADSTOCK_2_TMPTABLE format nul -f LOADSTOCK_2_TMPTABLE.fmt -t, -c -S10.8.0.228 -Usa -Psasa
bcp Beauty_Developer..LOADSTOCK_2_TMPTABLE in CS01.csv -f LOADSTOCK_2_TMPTABLE.fmt -t, -S10.8.0.228 -Usa -Psasa

2015年5月20日-0001年11月30日

Oracle的审计功能(简单记录)

切换权限用户：
SQLPLUS> connect / AS SYSDBA;


1、查看状态
SQLPLUS> show parameter audit;

2、修改状态（用户SYS）：
SQLPLUS> alter system set audit_trail=DB,Extended scope=spfile;

3、重启Oracle
SQLPLUS> shutdown normal;
SQLPLUS> startup;

4、查看审计状态（用户SYS）
SQLPLUS> show parameter audit;

5、设置审计（用户SYS）
SQLPLUS> audit DELETE, INSERT,SELECT, UPDATE on edms.t_audit_demo by access Whenever successful;

audit insert table by MFDB;
audit update table by MFDB;
audit delete table by MFDB;

6、关闭审计
SQLPLUS> noaudit DELETE, INSERT,SELECT, UPDATE on edms.t_audit_demo;

7、查询已经添加的审计
SELECT USER_NAME, AUDIT_OPTION, SUCCESS, FAILURE FROM DBA_STMT_AUDIT_OPTS;

8、查看记录
SELECT * FROM sys.aud$

2015年2月5日-0001年11月30日

[转载]关于mysql处理百万级以上的数据时如何提高其查询速度的方法

以下是网上流传比较广泛的30种SQL查询语句优化方法：

1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
     select id from t where num is null
     可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
     select id from t where num=0
继续阅读“[转载]关于mysql处理百万级以上的数据时如何提高其查询速度的方法”

2014年11月27日-0001年11月30日

C#获取硬件信息类


using System; 
using System.Runtime.InteropServices; 
using System.Management; 

namespace Hardware 
{ 
  public class MyClass
  {
    static void Main()
    {
      HardwareInfo  hardInfo = new HardwareInfo();
      Console.WriteLine("机器名: "+hardInfo.GetHostName());
      Console.WriteLine("CPU编号: "+hardInfo.GetCpuID());
      Console.WriteLine("第一块硬盘编号: "+hardInfo.GetHardDiskID());
      Console.WriteLine("MAC地址: "+hardInfo.GetMacAddress());
      return;
    }
    
  }

  public class HardwareInfo 
  { 
    //取机器名  
    public string GetHostName() 
    { 
        return System.Net.Dns.GetHostName(); 
    } 
    //取CPU编号 
    public String GetCpuID() 
    { 
        try 
        { 
            ManagementClass mc = new ManagementClass("Win32_Processor"); 
            ManagementObjectCollection moc = mc.GetInstances(); 

            String strCpuID = null; 
            foreach (ManagementObject mo in moc) 
            { 
                strCpuID = mo.Properties["ProcessorId"].Value.ToString(); 
                break; 
            } 
            return strCpuID; 
        } 
        catch 
        { 
            return ""; 
        } 

    }//end method 

    //取第一块硬盘编号 
    public String GetHardDiskID() 
    { 
        try 
        { 
            ManagementObjectSearcher searcher = new ManagementObjectSearcher("SELECT * FROM Win32_PhysicalMedia"); 
            String strHardDiskID = null; 
            foreach (ManagementObject mo in searcher.Get()) 
            { 
                strHardDiskID = mo["SerialNumber"].ToString().Trim(); 
                break; 
            } 
            return strHardDiskID; 
        } 
        catch 
        { 
            return ""; 
        } 
    }//end  

    public enum NCBCONST 
    { 
        NCBNAMSZ = 16,      /* absolute length of a net name         */ 
        MAX_LANA = 254,      /* lana's in range 0 to MAX_LANA inclusive   */ 
        NCBENUM = 0x37,      /* NCB ENUMERATE LANA NUMBERS            */ 
        NRC_GOODRET = 0x00,      /* good return                              */ 
        NCBRESET = 0x32,      /* NCB RESET                        */ 
        NCBASTAT = 0x33,      /* NCB ADAPTER STATUS                  */ 
        NUM_NAMEBUF = 30,      /* Number of NAME's BUFFER               */ 
    } 

    [StructLayout(LayoutKind.Sequential)] 
    public struct ADAPTER_STATUS 
    { 
        [MarshalAs(UnmanagedType.ByValArray, SizeConst = 6)] 
        public byte[] adapter_address; 
        public byte rev_major; 
        public byte reserved0; 
        public byte adapter_type; 
        public byte rev_minor; 
        public ushort duration; 
        public ushort frmr_recv; 
        public ushort frmr_xmit; 
        public ushort iframe_recv_err; 
        public ushort xmit_aborts; 
        public uint xmit_success; 
        public uint recv_success; 
        public ushort iframe_xmit_err; 
        public ushort recv_buff_unavail; 
        public ushort t1_timeouts; 
        public ushort ti_timeouts; 
        public uint reserved1; 
        public ushort free_ncbs; 
        public ushort max_cfg_ncbs; 
        public ushort max_ncbs; 
        public ushort xmit_buf_unavail; 
        public ushort max_dgram_size; 
        public ushort pending_sess; 
        public ushort max_cfg_sess; 
        public ushort max_sess; 
        public ushort max_sess_pkt_size; 
        public ushort name_count; 
    } 

    [StructLayout(LayoutKind.Sequential)] 
    public struct NAME_BUFFER 
    { 
        [MarshalAs(UnmanagedType.ByValArray, SizeConst = (int)NCBCONST.NCBNAMSZ)] 
        public byte[] name; 
        public byte name_num; 
        public byte name_flags; 
    } 

    [StructLayout(LayoutKind.Sequential)] 
    public struct NCB 
    { 
        public byte ncb_command; 
        public byte ncb_retcode; 
        public byte ncb_lsn; 
        public byte ncb_num; 
        public IntPtr ncb_buffer; 
        public ushort ncb_length; 
        [MarshalAs(UnmanagedType.ByValArray, SizeConst = (int)NCBCONST.NCBNAMSZ)] 
        public byte[] ncb_callname; 
        [MarshalAs(UnmanagedType.ByValArray, SizeConst = (int)NCBCONST.NCBNAMSZ)] 
        public byte[] ncb_name; 
        public byte ncb_rto; 
        public byte ncb_sto; 
        public IntPtr ncb_post; 
        public byte ncb_lana_num; 
        public byte ncb_cmd_cplt; 
        [MarshalAs(UnmanagedType.ByValArray, SizeConst = 10)] 
        public byte[] ncb_reserve; 
        public IntPtr ncb_event; 
    } 

    [StructLayout(LayoutKind.Sequential)] 
    public struct LANA_ENUM 
    { 
        public byte length; 
        [MarshalAs(UnmanagedType.ByValArray, SizeConst = (int)NCBCONST.MAX_LANA)] 
        public byte[] lana; 
    } 

    [StructLayout(LayoutKind.Auto)] 
    public struct ASTAT 
    { 
        public ADAPTER_STATUS adapt; 
        [MarshalAs(UnmanagedType.ByValArray, SizeConst = (int)NCBCONST.NUM_NAMEBUF)] 
        public NAME_BUFFER[] NameBuff; 
    } 
    public class Win32API 
    { 
        [DllImport("NETAPI32.DLL")] 
        public static extern char Netbios(ref NCB ncb); 
    } 

    public string GetMacAddress() 
    { 
        string addr = ""; 
        try 
        { 
            int cb; 
            ASTAT adapter; 
            NCB Ncb = new NCB(); 
            char uRetCode; 
            LANA_ENUM lenum; 

            Ncb.ncb_command = (byte)NCBCONST.NCBENUM; 
            cb = Marshal.SizeOf(typeof(LANA_ENUM)); 
            Ncb.ncb_buffer = Marshal.AllocHGlobal(cb); 
            Ncb.ncb_length = (ushort)cb; 
            uRetCode = Win32API.Netbios(ref Ncb); 
            lenum = (LANA_ENUM)Marshal.PtrToStructure(Ncb.ncb_buffer, typeof(LANA_ENUM)); 
            Marshal.FreeHGlobal(Ncb.ncb_buffer); 
            if (uRetCode != (short)NCBCONST.NRC_GOODRET) 
                return ""; 

            for (int i = 0; i < lenum.length; i++) 
            { 
                Ncb.ncb_command = (byte)NCBCONST.NCBRESET; 
                Ncb.ncb_lana_num = lenum.lana[i]; 
                uRetCode = Win32API.Netbios(ref Ncb); 
                if (uRetCode != (short)NCBCONST.NRC_GOODRET) 
                    return ""; 

                Ncb.ncb_command = (byte)NCBCONST.NCBASTAT; 
                Ncb.ncb_lana_num = lenum.lana[i]; 
                Ncb.ncb_callname[0] = (byte)'*'; 
                cb = Marshal.SizeOf(typeof(ADAPTER_STATUS)) + Marshal.SizeOf(typeof(NAME_BUFFER)) * (int)NCBCONST.NUM_NAMEBUF; 
                Ncb.ncb_buffer = Marshal.AllocHGlobal(cb); 
                Ncb.ncb_length = (ushort)cb; 
                uRetCode = Win32API.Netbios(ref Ncb); 
                adapter.adapt = (ADAPTER_STATUS)Marshal.PtrToStructure(Ncb.ncb_buffer, typeof(ADAPTER_STATUS)); 
                Marshal.FreeHGlobal(Ncb.ncb_buffer); 

                if (uRetCode == (short)NCBCONST.NRC_GOODRET) 
                { 
                  if (i > 0) addr += ":"; 
                  addr = string.Format("{0,2:X}:{1,2:X}:{2,2:X}:{3,2:X}:{4,2:X}:{5,2:X}", 
                  adapter.adapt.adapter_address[0], 
                  adapter.adapt.adapter_address[1], 
                  adapter.adapt.adapter_address[2], 
                  adapter.adapt.adapter_address[3], 
                  adapter.adapt.adapter_address[4], 
                  adapter.adapt.adapter_address[5]); 
                } 
            } 
        } 
        catch 
        { } 
        return addr.Replace(' ', '0'); 
    } 
  }
}

分类：程序设计