An accumulated summary of some common grammars based on sql statements

1. When the value of a field 1 is expected to be displayed by other word values (record conversion), it can be achieved by the following statement:
case Type when '1' then 'ordinary channel' when '2' then 'high-end channel' end as Type
Where "Type" is the field name," 1 ", "2" is the field value

2. Return the form name of the user table in the database:
select name from table where type = 'u' and status > =2

3. Alphabetical order:
select * from table order by column name Collate Chinese_PRC_CS_AS_KS_WS
First, collate here is a clause that defines a collation that can be applied to a database definition or a column definition. Or apply to string expressions to apply collation projection.
The syntax is collate collation_name. The parameter collate_name is the name of the collation applied to an expression, column definition, or database definition.
The & # 8226; collation_name can just be specified as Windows_collation_name or SQL_collation_name.
1.Windows_collation_name is the collation name of the Windows collation. See Windows collation name.
2.SQL_collation_name is the collation name of the SQL collation. See SQL collation name.
Note: SQL SERVER collation use at ordinary times is not a lot, maybe a lot of beginners are strange, but there is a mistake you should be often encounter: SQL SERVER database, across the library multi-table join queries, if two different data repository by default character set, the system will return this error: "cannot solve equal to collation conflict of the operation."

4. Sorting by stroke of surname:
Select * From table Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
Note above.

5. Query the range of specified values:
1. stockname like '% [a zA - Z] %'
2. stockname like '[^ F - M]'
Among them
[] specifies the range of values
^ exclude the specified range

6. Random ordering of query results:
SELECT * FROM table Orders ORDER BY NEWID()

7. Returns all records common to both tables:
select tableA.a tableB.b from tableA inner join tableB as b ontableA.a= b.b
The usage of as in sql is not discussed here.

8. Wait time before executing the statement:

waitfor delay '00:00:05 ' 
select * from studentinfowaitfor time '23:08:00

9. Insert records into 1 table A, and the inserted records do not exist in A (judged by 1 field) :

insert into tableA (tracekey,muteSMS,CreateTime,traceuser,tracetime,traceSlot,traceduration) 
Select 'TRACE_TIMER',0,getdate(),mobileid,getdate(),'30','0' from tableB where corpid = 10001
and not exists (select traceuser from tableA ) and mobileid like '13' and len(mobileid) = 11