SQL Server basic syntax example (3)

Your current location: homepage tutorial Programming Development mssql database SQLServer basic syntax instance application (3) SQLServer basic syntax instance application (3) thanks to 3lian8 delivery time: Source: triple tutorial 3. Develop applications 1. sort by surname strokes: Select * FromTableNameOrderByCustomerName

Your current location: Home> tutorial> programming and development> mssql database> SQLServer basic syntax instance application (3) SQLServer basic syntax instance application (3) thanks to 3lian8 delivery time: Source: triple tutorial 3. Develop applications 1. sort By surname strokes: Select * From TableName Order By CustomerName

Your current location: Home> tutorial> Programming & Development> mssql database> SQL Server basic syntax instance application (3)

SQL Server basic syntax example (3)

Thanks for the 3lian8 delivery time: Source: Sanlian tutorial

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

27

28

29

30

31

32

33

34

35

36

37

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 row name)

Select * from a Order By [Name] Collate Chinese_PRC_Stroke_ci_as

/*

ID Name

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

3. Wang Wu

Sun Qi

1 Zhang San

2 Li Si

4 Zhao Liu

(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, different

3. Retrieve the fields in the table:

Declare @ list varchar (1000 ),

@ SQL nvarchar (1000)

Select @ list = @ list + ',' + B. name from sysobjects a, syscolumns B where a. id = B. id and a. name = 'table'

Set @ SQL = 'select' + right (@ list, len (@ list)-1) + 'from table'

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

18

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)

=

(Select checksum_sum( binary_checksum (*) from B)

Print 'Equality'

Else

Print 'unequal'

/*

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)

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

Related Articles

[Back to triple homepage] [back to mssql database]/[join triple collection]

,