SQL Server basic syntax example (3)

3. Develop applications

1. sort by strokes of the Last Name:

Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as // From less to more

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 28 29 30 31 32 33 34 35 36 38 39 40 41

--> Test data: [a] if object_id ('[a]') is not null drop table [a] go create table [a] ([ID] int, [Name] varchar (6) insert [a] select 1, 'zhang san' union all select 2, 'Li si' union all select 3, 'wang wu' union all select 4, 'zhao liu' union all select 5, 'Sun 7' --> query statement (sort by surname strokes) select * from a Order By [Name] Collate Chinese_PRC_Stroke_ci_as/* ID Name ----------- ------ 3 Wang Wu 5 sun 7 1 Zhang 3 2 LI 4 4 Zhao 6 (5 rows affected) */2. database encryption: select encrypt ('original password') select pwdencrypt ('original password') select pwdcompare ('original password', 'encrypted password') = 1 -- same; otherwise, different encrypt ('original password') select pwdencrypt ('original password') select pwdcompare ('original password', 'encrypted password') = 1 -- same; otherwise, they are different. 3. retrieve the table fields: declare @ list varchar (1000), @ SQL nvarchar (1000) select @ list = @ list + ',' + B. name from sysobjects a, syscolumns B where. id = B. id and. name = 'table a' set @ SQL = 'select' + right (@ list, len (@ list)-1) + 'from table a' exec (@ SQL) 4. view hard disk partitions:

EXEC master .. xp_fixeddrives

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

EXEC master .. xp_fixeddrives/* drive MB available space ----- ----------- C 168 D 130379 E 57714 (3 rows affected) */5. Compare whether tables A and B are equal:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 19 20 21 22

--> Test data: [a] if object_id ('[a]') is not null drop table [a] go create table [a] ([ID] int, [Name] varchar (6) insert [a] select 1, 'zhang san' union all select 2, 'Li si' union all select 3, 'wang wu' union all select 4, 'zhao liu' union all select 5, 'Sun 7' --> test data [B] select * into [B] from [a] if (select checksum_agg (binary_checksum (*)) from A) = (select checksum_print (binary_checksum (*) from B) print 'equals 'else print' not equal '/* equal */

6. Record Search:

Starting with N records

Select Top N * From table

-------------------------------

N to M records (primary index ID required)

Select Top M-N * From table Where ID in (Select Top m id From Table) Order by ID Desc

----------------------------------

N to the end record

Select Top N * From Table Order by ID Desc

7. Obtain all user tables in the current database.

Select Name from sysobjects where xtype = 'U' and status> = 0

8. Obtain all fields of a table.

Select name from syscolumns where id = object_id ('table name ')

Select name from syscolumns where id in (select id from sysobjects where type = 'U' and name = 'table name ')

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

Select name from syscolumns where id = object_id ('A') select name from syscolumns where id in (select id from sysobjects where type = 'U' and name = 'A ') /* name ---------------------------------- ID Name (2 rows affected) name ------------------------------------- ID Name (2 rows affected )*/

The two methods have the same effect.

9: View views, stored procedures, and functions related to a table

Select a. * from sysobjects a, syscomments B where a. id = B. id and B. text like '% table name %'

10: view all stored procedures in the current database

Select name as stored procedure name from sysobjects where xtype = 'P'

11: Query all databases created by the user

Select * from master .. sysdatabases D where sid not in (select sid from master .. syslogins where name = 'sa ')

Or

Select dbid, name AS DB_NAME from master .. sysdatabases where sid <> 0x01

12: query the fields and Data Types of a table

Select column_name, data_type from information_schema.columns

Where table_name = 'table name'

13. Data operations between databases on different servers

-- Create a linked server

Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'

Exec sp_add1_srvlogin 'itsv', 'false', null, 'username', 'Password'

-- Query example

Select * from ITSV. Database Name. dbo. Table Name

-- Import example

Select * into table from ITSV. Database Name. dbo. Table Name

-- Delete the linked server when it is no longer in use

Exec sp_dropserver 'itsv', 'droplogins'

-- Connect to remote/LAN data (openrowset/openquery/opendatasource)

14. openrowset

-- Query example

Select * from openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table name)

-- Generate a local table

Select * into table from openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table name)

-- Import a local table to a remote table

Insert openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table name)

Select * from local table

-- Update local table

Update B

Set B. Column A = a. Column

From openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table Name) as a inner join local Table B

On a. column1 = B. column1

-- Create a connection for openquery usage

-- First create a connection to create a linked server

Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'

-- Query

Select *

FROM openquery (ITSV, 'select * FROM database. dbo. Table name ')

-- Import a local table to a remote table

Insert openquery (ITSV, 'select * FROM database. dbo. Table name ')

Select * from local table

-- Update local table

Update B

Set B. Column B = a. Column B

FROM openquery (ITSV, 'select * FROM database. dbo. Table name') as

Inner join local table B on a. Column A = B. Column