Summary of some common syntaxes based on SQL statements

Source: Internet
Author: User

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

5. query the range of the specified value:
1. stockname like '% [A-Za-Z] %'
2. stockname like '[^ F-M]'
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:

CopyCode The 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 code The 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.