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]
,