What are the skills of SQLServer
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!
