Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the SQLServer skills". In the daily operation, I believe many people have doubts about the SQLServer skills. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "what are the SQLServer skills?" Next, please follow the editor to study!

1. Resurrect a field (from 1 to n):

DECLARE @ I int

Set @ I = 0

Update Table1 Set @ I = @ I + 1 Magi Field1 = @ I

two。 Rank by grade

Update score sheet

Set a. Rank = (

Select Count (*) + 1

From score sheet b

Where a. The total score is < b. Total score

)

From score sheet a

3. Query external database

Select a.*

From OpenRowSet ('Microsoft.Jet.OLEDB.4.0','c:\ test.mdb';'admin';'',Table1) a

4. Query Excel files

Select *

From OpenDataSource ('Microsoft.Jet.OLEDB.4.0','Data Source= "c:\ test.xls"; User ID=Admin;Password=;Extended properties=Excel 8.0')... Sheet1 $

5. Specify a collation in a query

Select * From Table1 Order By Field1 COLLATE Chinese_PRC_BIN

Why specify a collation? See:

Http://www.delphibbs.com/delphibbs/dispq.asp?lid=1633985

For example, check to see if the specified user exists in the Pub_Users table in the database:

Select Count (*) From Pub_Users Where [UserName] = 'admin' And [PassWord] =' aaa' COLLATE Chinese_PRC_BIN

The default comparison is case-insensitive. If COLLATE Chinese_PRC_BIN is not added, then the password aaa and AAA are equivalent, which of course does not accord with the reality. Note that each condition specifies a collation, and in the above example, the user name is not case-sensitive.

A little trick of 6.Order By

Order By can specify the column order without specifying the column name, which is useful in the following example (note that the third column does not specify an alias)

Select a. ID. Name, (Select Count (*) From TableB b Where a.ID=b.PID) From TableA an Order By 3

At this point, the study of "what are the SQLServer skills" is over. I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.