SQL Server basic syntax example (3)

Source: Internet
Author: User
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]

,

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: info-contact@alibabacloud.com 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.