Summary of some common syntaxes based on SQL statements

1. When the value of a field needs to be displayed through other word values (record conversion), the following statement can be used:
Case Type when '1' then' normal 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 table name in the database:
Select name from table where type = 'U' and status> = 2

3. sort by the first letter of the alphabet:
Select * from table order by column name Collate Chinese_PRC_CS_AS_KS_WS
First, here, collate is a clause that defines sorting rules and can be applied to database or column definitions, or to string expressions to apply sorting rule projection.
The syntax is collate collation_name. The collate_name parameter is the name of the sorting rule applied to expressions, column definitions, or database definitions.
• Collation_name can only be the specified Windows_collation_name or SQL _collation_name.
1. Windows_collation_name is the name of a Windows sorting rule. See Windows sorting rule name.
2. SQL _collation_name is the name of the SQL sorting rule. See SQL sorting rule name.
Note:SQL server sorting rules are usually not used many times and may be unfamiliar to many beginners. However, when you encounter an SQL SERVER database that supports cross-database, multi-table join queries, if the default Character Set of the two databases is different, the system will return the following error: "The equal to Operation sorting rule conflict cannot be resolved."

4. sort by strokes of the Last Name:
Select * From table Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
Note.

5. query the range of the specified value:
1. stockname like '% [a-zA-Z] %'
2. stockname like '[^ F-M]'
Where
[]Value Range
^Exclude specified range

6. random sorting of query results:
SELECT * FROM table Orders order by newid ()

7. Return all records of the two tables:
Select tableA. a tableB. B from tableA inner join tableB as B ontableA. a = B. B
Here we will not comment on the usage of as in SQL.

8. Execute the statement after the wait time:
Copy codeThe Code is as follows:
Waitfor delay '00: 00: 05'
Select * from studentinfowaitfor time '23: 08: 00

9. Insert A record to Table A, and the inserted record does not exist in Table A (determined by A field ):
Copy codeThe Code is as follows:
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