SQL 语句

自己写的一个多表查寻

SELECT t1.*,
       t2.ESSerialNumber [Serial_Number]
FROM
(
    SELECT pb.JobId,
           pb.FullPalletID,
           (
               SELECT sl.BoxSeq
               FROM dbo.SsccLog sl
               WHERE sl.PositionName = 'Cartoon'
                     AND sl.Jobid = pb.JobId
                     AND sl.FullSn = pb.ESSerialNumberB
           ) BoxSeq,
           (
               SELECT sl.BoxNumber
               FROM dbo.SsccLog sl
               WHERE sl.PositionName = 'Cartoon'
                     AND sl.Jobid = pb.JobId
                     AND sl.FullSn = pb.ESSerialNumberB
           ) BoxNum,
           pb.ESSerialNumberA [Carton_SSCC],
           pb.ESSerialNumberB [CartonID],
           sscc.sscc [Pallet_SSCC],
           sl.PalletSscc [PalletID]
    FROM dbo.PBSPalletBoxInfo pb, --表A
         dbo.SsccLog sl,          --表B  
         DGOV1.PhilipsShippingMatch.dbo.si_qr_realate_sscc sscc    --表C
    WHERE sl.Jobid = pb.JobId
          AND pb.FullPalletID = sl.BoxSeq
          AND pb.JobId IN
              (
                  SELECT JobId FROM dbo.PBSJob WHERE JobNumber = 'C04579-01'
              )
			 
          AND sl.PositionName = 'Pallet'
          AND sl.PalletSscc = SUBSTRING(sscc.sscc, 11, 9)
          AND sl.DisableDate IS NULL
          AND pb.DisableTime IS NULL
) AS t1 ,--表1
dbo.PBSEScanLog AS t2--表2
WHERE t1.JobId = t2.ESJobId
      AND t1.BoxSeq = t2.ESBoxSeq
      AND t2.ESDisabledDate IS NULL
ORDER BY t1.FullPalletID,
         t1.BoxSeq;

 

 

这个是插入不重复的数据到PBSReprint

  insert into PBSReprint (SerialNumber,MAC,MAC1,MAC2,MAC3,SecurityId,WPAPSK) 
  SELECT et.SerialNumber,et.MAC,et.MAC1,et.MAC2,et.MAC3,et.SecurityId,et.WPAPSK from @ExeclTb as et 
  WHERE et.SerialNumber not in( select SerialNumber from PBSReprint)


  INSERT INTO	dbo.TestResult
  (
      Mac
  )
SELECT Mac FROM dbo.Temp AS t1 WHERE t1.mac NOT IN(SELECT Mac FROM	 dbo.TestResult )

创建索引

CREATE [UNIQUE(唯一索引)] INDEX [索引名] ON [表名](列名 ASC ) 

CREATE  INDEX create_date_Index ON PackLog(create_date ASC) 

                                   索引名                         表名         列名        ASC

//链接数据库

osql -SIP  -U用户名 -P密码

//创建数据库

//那数据库要创建mdf和ldf两个文件

Create database MySchool

on primary

(

name ='MySchool',   ---这里是区分大小写的,这里的名字必须要与上面Create database ‘MySchool’一致.

filename='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MySchool.mdf',

size=5MB，    --默认大小

filegrowth=2MB， --增长方式

maxsize=100MB     --最大容量

)

 

log on

(

name= 'Myschool_log',

filename='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Myschool_log.ldf'

size=1MB，

filegrowth=10%

)

 

 

//建表

create table score

(

studentID int identity(1,1) primary key,

english int not null

)

//删除语句

drop table student  删除这张表,下面两个是删除表中的数据.

truncate table student   删除student表中的数据,但会还原自动增长列.列又从1开始计数.

truncate非常高效,数百万条数据只需几秒,而使用Delete则需要几个小时.

delete from student 删除student表中的数据,不会还原自动增长列.自动增长列还会继续上次继续计数.

//更新语句

update student set  age=50 //将age字段所有数据更新为50

update student  set age=50 where Name='刘备'  //将Name='刘备'的这条记录中的age字段更新为50.

------给studentID是1的英语成绩加10分

update score set english+=10 where studentID=1

 

 

 //多条件固定值查寻,结果不改变数据库内容.

select 

case P_TypeID             
when 1 then '朋友'         //value为1显示为朋友……
when 2 then '同学'         //其实是在对一列的所有内容进行操作.

 

when 3 then '同事'         //then 最后的value必须一致

 

else '陌生人'      //else 和C#语法一样可以没有

end  as '关系',   //将P_TypeID字段改为‘关系’

 

P_Name,P_CellPhone,P_HomePhone from  Phone_Num

 

 

//多条件区间范围查寻,结果不改变数据库内容.

select ID,Name,Sex,
case
when Age <15 then '未成年'
when Age >15 and Age<20 then '青春年华'
when Age >21 and Age <50 then '成年'
when Age >100 then '老妖精'
else '没出生'
end as '年龄'
,Address,Birthday  from MyClass