数据库语句相关技巧

复制一个表的数据到另一个表

当这两个表的结构相同且属于不同的数据库时，可采用插入的方法：
以两个库的location_map表为例：

INSERT INTO [GHNEWTEST.DB].[dbo].[R_STORAGE_LOCATION_MAP_T] SELECT 
[RACK_NO],
[PALLET_ID],
[STATE],
[FREEZE],
[CREATE_TIME] 
FROM [GZHTWarehouse.DB].[dbo].[R_STORAGE_LOCATION_MAP_T]

注意：选择原数据的表字段时要剔除掉 ID，因为 ID 无法复制，一般都是自增长的

为某个字段已存在的数据中添加内容

UPDATE C_STORAGE_LOCATION_BASE_T SET RACK_NO = 'A' + RACK_NO

此语句功能为RACK_NO的每个数据前加上字符 A
效果图

SUBSTRING用法

substring('abdcsef',1,3)
Sql Server中括号中数字‘1’表示截取的起始位置是从该字符串第一个字符开始，‘3’表示截取后得到的字符串长度为3个字符。
结果：
abd

修改某个字段已存在的数据的内容

UPDATE C_STORAGE_LOCATION_BASE_T SET RACK_NO = REPLACE(RACK_NO, 'A', 'B') 

此语句功能为将RACK_NO的每个数据中的 A 替换成 B
效果图

将一个表的数据插入到另一个表中

  INSERT INTO R_STORAGE_LOCATION_MAP_T (RACK_NO) 
  SELECT RACK_NO FROM C_STORAGE_LOCATION_BASE_T where ID>754

此语句功能为将base表中ID>754的RACK_NO数据插入到MAP表中

模糊查询

if (!string.IsNullOrWhiteSpace(partName))
 {
   sql.Append(" AND PART_NAME LIKE @PARTNAME ");
   dbParams.Add("@PARTNAME", SqlDbType.NVarChar,"%"+partName+"%");
 }

App.config相关说明

<add key="GH.DB" value="Data Source=.,1433;Network Library=DBMSSOCN;Initial Catalog=GZHTWarehouse.DB;User ID=sa;Password=12345;"/>

• Data Source=.中的 . 代表localhost
• 应用程序入口的main中的SqlServerString.SqlConnectString = "GH.DB";要和App.config中的key="GH.DB"一致

显示指定数据行数

加top("行数")即可，例：

SELECT top(20) * FROM C_KEYPART_BASE_T

即只显示KEYPART_BASE表的前20行数据

参数为list<string>查询

public ExecutionResult SearchByBoxNums(List<string> boxNums)
{
  string sql = null;
  ExecutionResult exeResult = null;
  string boxNo = "";
  foreach (var item in boxNums)
  {
     boxNo += "'" + item + "',";
  }
  boxNo = boxNo.Substring(0, boxNo.Length - 1);
  sql = string.Format(@"SELECT * FROM R_BOX_MAP_T where BOX_NO in ({0})", boxNo);
  DBParameter dbParams = dbParams = new DBParameter();
  exeResult = sqlHelper.ExecuteQueryDS(sql.ToString(), dbParams.GetParameters());
  return exeResult;
}

若参数为List<int>类型，则把foreach内改为boxNo += item + ",";

在DAO层做事务处理

将要处理的多张表单独写多个sql，然后进行统一拼接执行处理

public ExecutionResult UpdateQty(string ApplyNo, string PalletNo, string BoxNo, string KPN, string Batch, string count, string taskNo, string user)
        {
            ExecutionResult exeResult;
            exeResult = new ExecutionResult();
            DBParameter dbParams;
            dbParams = new DBParameter();
            StringBuilder sb;
            sb = new StringBuilder();
            SQLTransactionHelper trans;
            trans = new SQLTransactionHelper();

            sb.Append(" UPDATE R_MATERIAL_SHEET_T SET ISSUED_QTY=ISSUED_QTY+@QTY WHERE APPLY_NO=@APPLY_NO AND PART_NO=@KPN; ");

            sb.Append(@" INSERT INTO R_STORAGE_KEYPARTS_RECORD_T
           (OPERATION_ID, OPERATION_TYPE, PALLET_NO, BOX_NO , PART_NO , PART_BATCH_NUMBER,UNIT_QTY,CREATE_USER,CREATE_TIME)
           VALUES(@TASK_NO,'出库',@PALLET,@BOX,@KPN,@BATCH,@QTY,@USER,GETDATE()) ");

            dbParams.Add("@APPLY_NO", SqlDbType.NVarChar, ApplyNo);
            dbParams.Add("@TASK_NO", SqlDbType.NVarChar, taskNo);
            dbParams.Add("@PALLET", SqlDbType.NVarChar, PalletNo);
            dbParams.Add("@BOX", SqlDbType.NVarChar, BoxNo);
            dbParams.Add("@KPN", SqlDbType.NVarChar, KPN);
            dbParams.Add("@BATCH", SqlDbType.NVarChar, Batch);
            dbParams.Add("@QTY", SqlDbType.Int, int.Parse(count ?? "0"));
            dbParams.Add("@USER", SqlDbType.NVarChar, user);

            try
            {
                trans.BeginTransaction();
                trans.ExecuteUpdate(sb.ToString(), dbParams.GetParameters());
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                trans.EndTransaction();
            }
            return exeResult;
        }

用sql生成仓位基础数据

以生成贵航仓库2排×13列×29层储位为例

DECLARE @i int, @j int, @k int, @str varchar,@rackNo varchar(50) --i列号，j层号，k排号
 set @i=0
 set @j=0
 set @k=0
 set @rackNo=''
 WHILE @k<2
 begin
 set @i=0
 if @k=0
  begin set @str='A' end
 if @k=1
  begin set @str='B' end
 
WHILE @i<13
BEGIN
set @j=0
 WHILE @j < 29
 begin
 if (@i < 9)
 begin
  if(@j < 9) 
   begin set @rackNo = '0'+convert(varchar(50),(@k+1))+'0'+convert(varchar(50),(@i+1)) + '0' + convert(varchar(50),(@j+1)) end
  else
   begin set @rackNo = '0'+convert(varchar(50),(@k+1))+'0'+convert(varchar(50),(@i+1)) + convert(varchar(50),(@j+1)) end
 end
 
 else
 begin
   if(@j < 9) 
    begin set @rackNo = '0'+convert(varchar(50),(@k+1))+convert(varchar(50),(@i+1)) + '0' + convert(varchar(50),(@j+1)) end
  else
    begin set @rackNo = '0'+convert(varchar(50),(@k+1))+convert(varchar(50),(@i+1)) + convert(varchar(50),(@j+1)) end
 end
    BEGIN
    INSERT INTO C_STORAGE_LOCATION_BASE_T (STORAGE_ID,RACK_NO,ROW_NUMBER,COLUMN_NUMBER,FLOOR_NUMBER,CREATE_TIME) 
            VALUES  (@str, @rackNo, @k+1, @i+1, @j+1,GETDATE())  
    SET @j=@j+1   
    END 
    end
 SET @i=@i+1
END

set @k=@k+1
end

表结构为：

SOTRAGE_ID为了区分两个库设定了A库和B库

用list批量插入数据库表数据

public ExecutionResult InsertPlan(List<EquipmentPlanLog> value)
        {
            string sql = @"INSERT INTO C_EQUIPMENT_MAINTAIN_PLAN_T (
             EQIP_ID
            ,EQIP_NAME
            ,EXPECT_MAINTAIN_DATE
            ,PERSON
            ,EQ_STATE
            ,REASON
            ,REMARK
            ,CREATE_TIME)
            VALUES ";

            foreach (var item in value)
            {
                string str = " ('{0}','{1}','{2}','{3}','{4}','{5}','{6}',GETDATE()),";
                str = string.Format(str, item.EqipId, item.EqipName, item.MaintainDate, item.Person, item.EqState, item.Reason, item.Remark);
                sql = sql + str;
            }
            char[] chr = {','};
            sql = sql.TrimEnd(chr); //去掉sql语句最后的 “ , ”
            var exeResult = sqlHelper.ExecuteQueryDS(sql);
            return exeResult;
        }