SQL Server (timestamp 2018/5/31 22:07:36)

Usage of 1.NULL __2018-4-13

• Usage of ISNULL ()
• Note the difference between SET ANSI_NULLS on/off;

• Perform the following two comparison results:

  SELECT  location,*FROM  mfsysproduct WHERE location IS NULL;    SELECT  location,*FROM  mfsysproduct WHERE location = ‘‘;   SELECT  location,* FROM mfsysproduct where location !=‘‘;   SELECT  location,* FROM mfsysproduct WHERE ISNULL(location,‘‘)!=‘‘;

2.GROUP by & have

    SELECT nextevent,COUNT(1) FROM mfworkstatus WHERE workorderno NOT IN     (SELECT workorderno FROM mfworkorder) GROUP BY nextevent;        SELECT nextevent,COUNT(1) FROM mfworkstatus WHERE workorderno NOT IN    (SELECT workorderno FROM mfworkorder) GROUP BY nextevent HAVING  COUNT(*)>100

The difference between 3.SET quoted_identifier off/on

    SET QUOTED_IDENTIFIER ON    SELECT * FROM "USER" WHERE a=‘netasp‘         SET QUOTED_IDENTIFIER ON    SELECT * FROM [USER] WHERE a=‘netasp‘         SET QUOTED_IDENTIFIER OFF    SELECT * FROM [USER]    WHERE a="netasp" s        SET QUOTED_IDENTIFIER OFF    SELECT * FROM [USER]    WHERE a= ‘netasp‘

When creating a new table with a user name in the database, it's often a bit of a hassle, because user is a keyword in sql,
But some of the above statements do not error. Another concept: The identifier is the middle bracket in SQL [].
When the SET QUOTED_IDENTIFIER value is on, the characters inside the double quotation mark are treated as database objects.
That is, the double quotation mark "" and the identifier [] effect are the same, and they all represent that the referenced character is a database object. Single quotation marks ' represent the bounds of a string.
When set Quotde_identifier off, double quotes are interpreted as the bounds of a string, and the effect of single quotes is similar.
This means that the double quotation mark "cannot be used as an identifier, but it can be as a character boundary, and the effect of single quotation marks is the same."
A summary can be made: When SET QUOTED_IDENTIFIER on "" Equals [] represents the database object;
When SET QUOTED_IDENTIFIER OFF "" Equals "means a string boundary;
And here's the double quote "is not two single quotes ' together, is shift+" hit out, beginners may make such a mistake.

Usage of 4.CHARINDEX and substring

SELECT TOP 5 Areatype,CHARINDEX(‘itpacking‘,AreaType,2) FROM sfctemparea WHERE CHARINDEX(‘waitpacking‘,AreaType)>0 ;SELECT TOP 5 charindex(‘-‘,AreaType) FROM SfcTempArea WHERE CHARINDEX(‘waitpacking‘,AreaType)>0 ;select top 5 CHARINDEX(‘-‘,AreaType)+1,len(AreaType) as [Last Name] from SfcTempArea WHERE CHARINDEX(‘waitpacking‘,AreaType)>0 ;select top 5 substring(AreaType,charindex(‘-‘,AreaType)+1,len(AreaType)) as [Last Name] from SfcTempArea WHERE CHARINDEX(‘waitpacking‘,AreaType)>0 ;

The use of 5.isnumeric,left,right

select ISNUMERIC(‘164‘)select ISNUMERIC(LEFT(‘q2qqq222222‘,1))select ISNUMERIC(RIGHT(‘q11q1‘,2))

6. About the intent of the constraint prefix in SQL (PK, UK, DF, CK, FK)

1. Primary key
   Constraint pk_ Field primary key (field),
2. Unique constraint
   Constraint uk_ field unique key (field),
3. Default Constraints
   Constrint df_ field default (' Default value ') for field,
4. CHECK constraints
   Constraint ck_ field check (constraint. such as: Len (field) >1),
5. Primary foreign key relationships
   Constraint Fk_ Main Table _ from table foreign (foreign key field) references Main Table (primary table primary key field)

7. Querying all triggers in the database

select name from sysobjects where xtype=‘TR‘ --所有触发器  select name from sysobjects where xtype=‘P‘ --所有存储过程  select name from sysobjects where xtype=‘V‘ --所有视图  select name from sysobjects where xtype=‘U‘ --所有表   //以上为SqlServer用法    Select object_name From user_objects Where object_type=‘TRIGGER‘;  --所有触发器  Select object_name From user_objects Where object_type=‘PROCEDURE‘;  --所有存储过程  Select object_name From user_objects Where object_type=‘VIEW‘;  --所有视图  Select object_name From user_objects Where object_type=‘TABLE‘; --所有表  Select object_name From user_objects Where object_type=‘SEQUENCE‘; --序列    

8. Trigger query data Update in database

if not exists(select 1 from deleted d join inserted i on d.ID=i.ID and d.col1=1 and i.col1=2)//if not exists 如果不存在 //（..）这里该是验证更新，更新前的id等于更新后的id,并且更新前col的值是1，更新后的值是2

9. RAISERROR of stored procedure Code fragments

RAISERROR (            N‘This is message %s %d.‘,   -- Message text,            10,                          -- Severity,            1,                           -- State,            N‘number‘,                   -- First argument.            5                            -- Second argument.           ); --The message text returned is: This is message number 5.--Severity, 用户定义的与该消息关联的严重级别。（这个很重要）   任何用户都可以指定 0 到 18 之间的严重级别。   [0,10]的闭区间内，不会跳到catch；   如果是[11,19],则跳到catch；   如果[20,无穷)，则直接终止数据库连接；         --State, 如果在多个位置引发相同的用户定义错误，        则针对每个位置使用唯一的状态号有助于找到引发错误的代码段。        介于 1 至 127 之间的任意整数。（state 默认值为1）

If @type = ' mesuser '--mes login account detection BEGIN IF EXISTS (SELECT *                                    From Euser (nolock) WHERE logonname = @parameter1 and [DISABLED] = ' 1 ') BEGIN SET @errorstr = N ' The account has been cancelled, please confirm!                ' RAISERROR (@errorstr, 16,1) RETURN 101 END                 SELECT @EXPIREDATE = [ExpireDate] from Euser (nolock) WHERE logonname = @parameter1 IF @EXPIREDATE < GETDATE () BEGIN SET @errorstr = N ' The expiration date of the account has been reached, please Confirm!                ' RAISERROR (@errorstr, 16,1) RETURN 101 END --if EXISTS (SELECT *--from Euser--WHERE logonname = @para Meter1 [Email protectED] Account--and [PASSWORD] = @parameter2) [email protected] Password--B                                Egin IF not EXISTS (SELECT * from Mfstationuser WHERE logonname = @parameter1) BEGIN SET @errorstr = N ' The account does not have the right to use MES Limit, please confirm!                              From database table: Mfstationuser ' RAISERROR (@errorstr, 16,1) RETURN 101     END ELSE BEGIN SELECT * FROM     Euser WHERE logonname = @parameter1 END-- END--else--BEGIN--SET @errorstr = N ' account or password error, please check!            '--RAISERROR (@errorstr, 16,1)--RETURN 101--END END

10.SQL Service Date, time calculation timestamp 2018/5/31 14:01:53

--往前/后计算日期（YY\MM\DD）SELECT CONVERT (VARCHAR(30), DATEADD(MM,-1, GETDATE()), 23)   //这个--结果2018-04-30SELECT CONVERT(VARCHAR(10),GETDATE(),112),CONVERT(VARCHAR(10),GETDATE(),120)--结果20180521  2018-05-21SELECT GETDATE()--2018-05-31 16:22:06.233SELECT CONVERT (VARCHAR(30), DATEADD(dd,-1, GETDATE()), 23)--2018-05-30SELECT CONVERT (VARCHAR(30), GETDATE(),120)--2018-05-31 16:37:13SELECT CONVERT (VARCHAR(30), GETDATE(),23)     //***这个--2018-05-31SELECT CONVERT (VARCHAR(10), GETDATE(),120)--2018-05-31

SQL Server (timestamp 2018/5/31 22:07:36)