Mysql Sql statement comments

1. Description: Create a database CREATE DATABASE database-name
2. Description: Delete database drop database dbname
3. Description: Back up sql
 server
---
 Create a device for backing up data
USE
 master
EXEC sp_addumpdevice'disk','testBack','c:\mssql7backup\MyNwind_1.dat'
---
 Start backup BACKUPDATABASE pubsTO testBack
4. Description: Create a new table create table tabname (col1
 type1 [not null]
 [primary key],col2
 type2 [not null],..)
Create a new table based on an existing table:
A: create table tab_newlike tab_old
 (Use the old table to create a new table)
B: create table tab_newas select col1,col2…from tab_old
 definition only
5. Description: Delete the new table drop table tabname
6. Description: Add a column Alter table tabnameadd column col
 type
Note: Once a column is added, it cannot be deleted. In DB2, the data type cannot be changed after a column is added. The only thing that can be changed is to increase the length of the varchar type.
7. Description: Add primary key: Alter table tabname add primary key (col)
Description: Delete the primary key: Alter table tabname drop primary key (col)
8. Description: Create an index: create [unique]index idxnameon tabname(col…)
Delete an index: drop index idxname
Note: The index cannot be changed. If you want to change it, you must delete it and rebuild it.
9. Description: Create a view: create view viewnameas select statement
Delete a view: drop view viewname
10. Description: Several simple basic SQL statements Selection: select * from table1 where Range insertion: insert into table1 (field1, field2) values ​​(value1, value2)
Delete: delete from table1where Range update: update table1set field1=value1where Range search: select *from table1where field1like '%value1%'---The syntax of like is very subtle, check the information!
Sort: select * from table1 order by field1,field2
 [desc]
Total: select count as totalcountfrom table1
Sum: select sum(field1)as sumvaluefrom table1
Average: select avg(field1)as avgvaluefrom table1
Max: select max(field1)as maxvaluefrom table1
Minimum: select min(field1) as minvaluefrom table1
11. Description: Several advanced query operators A: UNION operator The UNION operator derives a result table by combining two other result tables (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in the tables. When ALL is used with UNION (that is, UNION ALL), duplicate rows are not eliminated. In both cases, each row of the derived table comes either from TABLE1 or from TABLE2.
B: EXCEPT operator The EXCEPT operator derives a result table by including all rows that are in TABLE1 but not in TABLE2 and eliminating all duplicate rows. When ALL is used with EXCEPT (EXCEPT ALL), duplicate rows are not eliminated.
C: INTERSECT operator The INTERSECT operator derives a result table by including only the rows that are present in both TABLE1 and TABLE2 and eliminating any duplicate rows. When ALL is used with INTERSECT (INTERSECT ALL), duplicate rows are not eliminated.
Note: The query result rows using the operator must be consistent.
12. Description: Use outer join A, left (outer) join:
Left outer join (left join): The result set includes both matching rows of the joined table and all rows of the left joined table.
SQL: select aa,
 ab, ac, bc, bd, bf from aLEFT OUT JOIN bON aa
 = bc
B: right (outer) join:
Right outer join (right join): The result set includes both the matching join rows of the join table and all rows of the right join table.
C: full/cross (outer) join:
Full outer join: includes not only the matching rows of the symbolic join table, but also all the records in the two joined tables.
12. Group by:
 Once a table is grouped, only group-related information can be obtained after querying it.
 Group-related information: (Statistical information) count, sum, max, min, avg (grouping criteria)
  When grouping in SQL Server: you cannot use text, ntext, or image fields as the grouping basis in the select statistics function, and cannot put them together with ordinary fields;
13. Operate on the database:
 Detach the database:
 sp_detach_db; Attach database: sp_attach_db followed by the full path name. 14. How to change the name of the database:
sp_renamedb 'old_name', 'new_name'
2. Improvement 1. Description: Copy table (only copy the structure, source table name: a
 New table name: b) (Access available)
Method 1: select * into b from a where 1<>1 (only for SQL Server)
Method 2: select top 0
 * into bfrom a
2. Description: Copy table (copy data, source table name: a
 Target table name: b) (Access available)
insert into b(a,
 b, c) select d,e,ffrom b;
3. Description: Copying tables across databases (using absolute paths for specific data)
 (Access available)
insert into b(a,
 b, c) select d,e,ffrom bin 'specific database' where condition example: ..from bin '"&Server.MapPath(".")&"\data.mdb"
 &"' where..
4. Description: Subquery (table name 1: a
 Table name 2: b)
select a,b,cfrom awhere aIN (select dfrom b
 ) or: select a,b,cfrom awhere aIN (1,2,3)
5. Description: Display the article, submitter and last reply time select a.title,a.username,b.adddatefrom table a,(select max(adddate)
 adddate from table where table.title=a.title)
 b
6. Description: Outer join query (table name 1: a
 Table name 2: b)
select aa,
 ab, ac, bc, bd, bf from aLEFT OUT JOIN bON aa
 = bc
7. Description: Online view query (table name 1: a
 )
select *from (SELECT a,b,cFROM a)
 Where is it
 > 1;
8. Note: Usage of between: between includes boundary values ​​when limiting the query data range, and not between does not include select *from table1where time between time1and time2
select a,b,c,from table1where anot between value1and value2
9. Description: How to use in select * from table1 where a
 [not]in ('value 1', 'value 2', 'value 4', 'value 6')
10. Description: Two related tables, delete the information in the primary table that does not exist in the secondary table delete from table1 where not exists
 ( select * from table2 where table1.field1=table2.field1
 )
11. Note: Four-table joint query problem:
select *from aleft inner join bon aa=b.bright inner join con aa=c.cinner join don aa=d.dwhere .....
12. Description: Schedule reminder five minutes in advance SQL: select * from schedule where datediff('minute',f start time,getdate())>5
13. Description: a sql
 Statement to solve database paging select top 10
 b.* from (select top 20
 Primary key field, sort field from table name order by sort field desc)
 a, table name b where b. primary key field = a. primary key field order by a. sort field specific implementation:
About database paging:
 declare @startint,@end int
 @sql 
 nvarchar(600)
 set @sql='select top'+str(@end-@start+1)+'+from Twhere ridnot in(select top'+str(@str-1)+'Ridfrom Twhere Rid>-1)'
 exec sp_executesql
 @sql
Note: A variable cannot be directly followed by top, so in actual applications only this type of special processing is performed. Rid is an identification column. This is very helpful if there are specific fields after top. This can avoid inconsistencies in the actual table after the query results if the top field is a logical index (the data in the logical index may be inconsistent with the data table, and if it is in the index during the query, the index will be queried first)
14. Description: Select top 10 for the first 10 records
 * form table1 where range 15, description: select all the information of the record with the largest a in each group of data with the same b value (similar usage can be used for monthly forum rankings, monthly hot-selling product analysis, ranking by subject grades, etc.)
select a,b,cfrom tablename
 ta where a=(select max(a)from tablename
 tb where tb.b=ta.b)
16. Description: Include all rows in TableA but not in TableB and TableC and eliminate all duplicate rows to derive a result table (select afrom tableA
 ) except (select afrom tableB) except (select afrom tableC)
17. Description: Randomly extract 10 data points, select top 10
 * from tablenameorder by newid()
18. Description: Randomly select records select newid()
19. Description: Delete duplicate records 1), delete from tablenamewhere idnot in (select max(id)from tablenamegroup by col1,col2,...)
2), select distinct * into temp from tablename
 delete from tablename
 insert into tablenameselect *from temp
evaluate:
 This operation involves the movement of a large amount of data, and is not suitable for large-capacity data operations. 3) For example, when importing data in an external table, for some reason only a part of it is imported for the first time, but it is difficult to determine the specific location, so the whole data can only be imported next time, which will generate many duplicate fields. How to delete duplicate fields? alter table tablename
--Add an auto-increment column add column_bint identity(1,1)
 delete from tablenamewhere column_bnot in(
select max(column_b) from tablenamegroup by column1,column2,...)
alter table tablenamedrop column column_b
20. Description: List all table names in the database select name from sysobjectswhere type='U' //
 U stands for user 21. Description: List all column names in the table select name from syscolumnswhere id=object_id('TableName')
22. Description: List the type, vender, and pcs fields, arranged by the type field. Case can easily implement multiple selections, similar to the case in select.
select type,sum(case venderwhen 'A' then pcselse 0end),sum(case venderwhen 'C' then pcselse 0end),sum(case venderwhen 'B' then pcselse 0end)FROM tablenamegroup by type
Display results:
type
 vender pcs
Computer A 1
Computer A 1
Disc B 2
Disc A 2
Mobile phone B 3
Mobile C 3
23. Description: Initialize table table1
TRUNCATE TABLE table1
24. Description: Select records from 10 to 15 select top 5
 * from (select top 15
 * from table order by idasc)
 table_alias order by iddesc
Tip 1: Use of 1=1 and 1=2. “where 1=1” is often used in SQL statement combinations.
 It means select all "where 1=2" and unselect all.
like:
if
 @strWhere != ''
begin
set @strSQL
 = 'select
 count(*) as Total from [' +
 @tblName + ']
 where ' +
 @strWhere
end
else
begin
set @strSQL
 = 'select
 count(*) as Total from [' +
 @tblName + ']'
end
We can just write it as an error! Directory entry not found.
set @strSQL
 = 'select
 count(*) as Total from [' +
 @tblName + ']
 where 1=1 stable'+
 @strWhere 2. Shrink the database--Rebuild the index DBCC
 REINDEX
DBCC
 INDEXDEFRAG
--Shrink data and log DBCC
 SHRINKDB
DBCC
 SHRINKFILE
3. Compress database dbcc
 shrinkdatabase(dbname)
4. Transfer the database to the new user with existing user permissions exec sp_change_users_login'update_one','newname','oldname'
go
5. Check the backup set RESTORE
 VERIFYONLY from disk='E:\dvbbs.bak'
6. Repair the database ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC
 CHECKDB('dvbbs',repair_allow_data_loss)WITH TABLOCK
GO
ALTER DATABASE [dvbbs]SET MULTI_USER
GO
7. Log clearing SET NOCOUNTON
DECLARE @LogicalFileName
 sysname,
 @MaxMinutesINT,
 @NewSizeINT
USE
 tablename --
 The database name to be operated SELECT @LogicalFileName
 = 'tablename_log',--
 Log file name @MaxMinutes
 = 10, --
 Limit on time allowed to wrap log.
 @NewSize
 = 1 --
 The size of the log file you want to set (M)
Setup
 /initialize
DECLARE @OriginalSizeint
SELECT @OriginalSize
 = size
 FROM sysfiles
 WHERE name =
 @LogicalFileName
SELECT 'Original
 Size of ' +
 db_name() + '
 LOG is ' +
 CONVERT(VARCHAR(30),@OriginalSize)
 + '
 8K pages or ' +
 CONVERT(VARCHAR(30),(@OriginalSize*8/1024))
 + 'MB'
 FROM sysfiles
 WHERE name =
 @LogicalFileName
CREATE TABLE DummyTrans
 (DummyColumnchar (8000) not null)
DECLARE @Counter INT,
 @StartTime
 DATE, TIME,
 @TruncLog VARCHAR(255)
SELECT @StartTime
 = GETDATE(),
 @TruncLog
 = 'BACKUP
 LOG ' +
 db_name() + '
 WITH TRUNCATE_ONLY'
DBCC
 SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
--
 Wrap the log if necessary.
WHILE
 @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) --
 time has not expired
 AND @OriginalSize
 = (SELECT size FROM sysfilesWHERE name =
 @LogicalFileName) 
 AND (@OriginalSize
 * 8 /1024) > @NewSize 
 BEGIN --
 Outer loop.
SELECT @Counter
 = 0
 WHILE 
 ((@Counter < @OriginalSize / 16) AND (@Counter
 < 50000))
 BEGIN --
 update
 INSERT DummyTransVALUES ('Fill
 Log')DELETE DummyTrans
 SELECT @Counter
 = @Counter + 1
 END
 EXEC (@TruncLog)
 END
SELECT 'Final
 Size of ' +
 db_name() + '
 LOG is ' +
 CONVERT(VARCHAR(30),size)
 + '
 8K pages or ' +
 CONVERT(VARCHAR(30),(size*8/1024))
 + 'MB'
 FROM sysfiles
 WHERE name =
 @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNTOFF
8. Description: Change a table exec sp_changeobjectowner'tablename','dbo'
9. Store changes to all tables CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwneras NVARCHAR(128),
@NewOwneras NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObjectCURSOR FOR
select 'Name' =name,
  'Owner' =
 user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObjectINTO @Name,
 @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN  
if
 @Owner=@OldOwner
begin
  set @OwnerName
 = @OldOwner + '.' +
 rtrim(@Name)
  exec sp_changeobjectowner
 @OwnerName, @NewOwner
end
--
 select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObjectINTO @Name,
 @Owner
END
close curObject
deallocate curObject
GO
10. SQL
 Directly loop and write data in SERVER declare @iint
set @i=1
while
 @i<30
begin
  insert into test
 (userid) values(@i)
  set @i=@i+1
end
Examples:
The following table requires that all the scores that have not passed the exam should be increased by 0.1 each time to make them pass:
 Name score
 Zhangshan
 80
 Lishi   
 59
 Wangwu   
 50
 Songquan
 69
while((select min(score)from tb_table)<60)
begin
update tb_tableset score
 =score*1.01
where score<60
if 
 (select min(score)from tb_table)>60
 break
 else
  continue
end
Data Development - Classic 1. Sort by the number of strokes of the surname:
Select *From TableNameOrder By CustomerNameCollate Chinese_PRC_Stroke_ci_as
 //From less to more 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 not the same 3. Retrieve the fields in the table:
declare @listvarchar(1000),
@sql
 nvarchar(1000)
select @list=@list+','+b.name from sysobjects
 a,syscolumns b where a.id=b.idand a.name='Table A'
set @sql='select
 '+right(@list,len(@list)-1)+'
 from Table A'
exec (@sql)
4. Check the hard disk partition:
EXEC master..xp_fixeddrives
5. Compare whether A and B are equal:
if
 (select checksum_agg(binary_checksum(*))from A)
   =
  (select checksum_agg(binary_checksum(*))from B)
print 'equal'
else
print'Not equal'
6. Kill all profiler processes:
DECLARE hcforeachCURSOR GLOBAL FOR SELECT 'kill
 '+RTRIM(spid)FROM master.dbo.sysprocesses
WHERE program_nameIN('SQL
 profiler',N'SQL
 Profiler')
EXEC sp_msforeach_worker '?'
7. Record Search:
Select Top N from the beginning to N records
 * From table-------------------------------
N to M records (with primary index ID)
Select Top MN
 * From table Where IDin (Select Top M
 ID From table)Order by ID Desc
----------------------------------
N to the end of the record Select Top N
 * From table Order by IDDesc
Case 1: A table has more than 10,000 records. The first field of the table, RecID, is an auto-increment field. Write an SQL statement to find the 31st to 40th records in the table.
 select top 10
 recid from Awhere recidnot in(select top 30
 recid from A)
Analysis: If written like this, some problems will occur if recid has a logical index in the table.
 select top 10
 recid from Awhere... is to search from the index, and the following select top 30
 recid from A is searched in the data table. Since the order in the index may be inconsistent with that in the data table, the data found may not be the data you originally wanted.
Solution 1,
 Use order by select top 30
 recid from Aorder by ricid
 If the field is not self-increasing, problem 2 will occur.
 Add the condition to that subquery: select top 30
 recid from Awhere recid>-1
Example 2: Query the last record in a table, but do not know how much data there is in the table or what the table structure is.
set @s
 = 'select
 top 1 * from T where pid not in (select top ' +
 str(@count-1)
 + '
 pid from T)'
print
 @s exec sp_executesql 
 @s
9: Get all user tables in the current database select Name from sysobjectswhere xtype='u' and status>=0
10: Get all the fields of a table select name from syscolumnswhere id=object_id('table name')
select name from syscolumnswhere idin (select idfrom sysobjectswhere type
 = 'u' and name = 'table name')
The two methods have the same effect 11: View views, stored procedures, and functions related to a table select a.*from sysobjects
 a, syscomments b where a.id
 = b.id and b.textlike '%table name%'
12: View all stored procedures in the current database select name as stored procedure name from sysobjects where xtype='P'
13: 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
14: Query the fields and data types of a table select column_name,data_typefrom information_schema.columns
where table_name
 = 'Table name'
15: Data operations between different server databases--Create linked servers exec sp_addlinkedserver 'ITSV
 ','
 ','SQLOLEDB
 ','Remote server name or IP address'
exec sp_addlinkedsrvlogin 'ITSV
 ','false
 ',null,'Username','Password'
--Query example select *from ITSV.database name.dbo.table name --Import example select *into tablefrom ITSV.database name.dbo.table name --Delete the linked server when it is no longer used exec sp_dropserver 'ITSV
 ','droplogins
 '
--Connect to remote/LAN data (openrowset/openquery/opendatasource)
--1. openrowset
--Query example select *from openrowset('SQLOLEDB
 ','sql server name';'user name';'password',database name.dbo.table name)
--Generate a local table select *into table from openrowset('SQLOLEDB
 ','sql server name';'user name';'password',database name.dbo.table name)
-- Import the local table into the remote table insert openrowset('SQLOLEDB
 ','sql server name';'user name';'password',database name.dbo.table name)
select *from local table--update local table update b
set b.columnA=a.columnA
 from openrowset('SQLOLEDB
 ','sql server name';'user name';'password',database name.dbo.table name)as ainner join local table b
on a.column1=b.column1
--Openquery usage requires the creation of a connection--First create a connection to create a linked server exec sp_addlinkedserver 'ITSV
 ','
 ','SQLOLEDB
 ','Remote server name or IP address'
--Query select *
FROM openquery(ITSV, 'SELECT
 * FROM database.dbo.table name')
-- Import the local table into the 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 a
Inner join local table b on a. Column A = b. Column A
--3. opendatasource/openrowset
SELECT *
FROM opendatasource('SQLOLEDB
 ', 'Data
 Source=ip/ServerName;User ID=Login name;Password=Password' ).test.dbo.roy_ta
--Import the local table into the remote table insert opendatasource('SQLOLEDB
 ', 'Data
 Source=ip/ServerName;User ID=login name;Password=password').Database.dbo.Table name select *from local table SQL
 Server Basic Function SQL
 Server basic functions 1. String function length and analysis 1, datalength (Char_expr)
 Returns the number of characters in the string, but does not include the trailing space 2, substring (expression, start, length)
 Take a substring. The subscript of the string starts from "1", start is the starting position, and length is the length of the string. In practical applications, len(expression) is used to obtain its length 3, right(char_expr, int_expr)
 Returns the int_expr character on the right side of the string, and uses left to do the opposite 4, isnull (
 check_expression, replacement_value) If check_expression is empty, it returns the value of replacement_value. If it is not empty, it returns check_expression. Character operation class 5, Sp_addtype
 Custom data types, for example: EXEC sp_addtype
 birthday, datetime, 'NULL'
6,set nocount
 {on|off}
Causes the returned results to not include information about the number of rows affected by a Transact-SQL statement. If the stored procedure contains statements that do not return much actual data, this setting can significantly improve performance by significantly reducing network traffic. SETNOCOUNT
 Settings are set at execution or runtime, not at analysis time.
SET NOCOUNT
 When ON, a count (indicating the number of rows affected by the Transact-SQL statement) is not returned.
SET NOCOUNT
 When OFF, the count is returned. Common sense in SQL queries: the maximum number of tables or views that can be followed by from: 256
When Order by appears in the SQL statement, when querying, sort first and then retrieve. In SQL, the maximum capacity of a field is 8000, and for nvarchar(4000), since nvarchar is a Unicode code.
SQL Server 2000 synchronous replication technology implementation steps 1:
 Preparation 1. Create a Windows user with the same name and password for both the publishing server and the subscription server as the valid access user for publishing snapshot folders - Administrative Tools - Computer Management - Users and Groups - Right-click the user - New User - Create a user (SynUser) who is affiliated with the administrator group to log in to Windows
2. On the publishing server, create a new shared directory as the storage directory for the published snapshot files, and do the following:
My Computer - D:\
 Create a new directory named: PUB
--Right click on the newly created directory --Properties --Sharing --Select "Share this folder"
--Set specific user permissions through the "Permissions" button to ensure that the user (SynUser) created in the first step
 Have all permissions to the folder--Confirm 3. Set the startup user of the SQL Agent (SQLSERVERAGENT) service (both the publish/subscribe server should do this)
Start--Programs--Administrative Tools--Services--Right-click SQLSERVERAGENT
--Properties--Login--Select "This Account"
--Enter or select the Windows login user name created in the first step (SynUser)
--Enter the user's password in "Password" 4. Set SQL
 Server authentication mode, to solve the permission problem when connecting (both publish and subscribe servers have this setting)
Enterprise Manager - right click SQL instance - properties - security - authentication - select "SQL
 Server and Windows
--Confirm 5. Register Enterprise Manager on both the publisher and subscriber servers --Right-click SQL
 Server Group--Create SQL
 Server Registration...
--Next--In the Available Servers, enter the remote server name you want to register--Add--Next--Connect using, select the second "SQL
 Server Authentication"
--Next--Enter username and password (SynUser)
--Next--Select SQL
 Server group, you can also create a new group--Next--Finish 6. For those who can only use IP but not computer name, register a server alias for it (this step is not used in the implementation)
 (Configured on the connection side, for example, if configured on the subscriber server, the server name is the IP address of the publisher server)
Start--Programs--Microsoft
 SQL Server--Client Network Utility--Alias--Add--Network Library select "tcp/ip"--Server Alias ​​enter the SQL server name--Connection Parameters--Enter the SQL server IP address in the server name--If you have modified the SQL port, uncheck "Dynamically determine the port" and enter the corresponding port number. 2.
 Formal configuration 1. Configure the publishing server Open Enterprise Manager and perform the following steps on the publishing servers (B, C, D):
(1)
 Select [Configure Publishing, Subscriptions, and Distribution] from the [Replication] submenu in the [Tools] pull-down menu. The Configure Publishing and Distribution Wizard appears (2)
 [Next] Select the distribution server. You can choose to use the publishing server itself as the distribution server or other sql server (select yourself)
(3)
 [Next] Set the snapshot folder to use the default \\servername\Pub
(4)
 [Next] Custom configuration You can choose: Yes, let me set the distribution database properties Enable the publishing server or set publishing settings No, use the following default settings (recommended)
(5)
 [Next] Set the distribution database name and location to the default values ​​(6)
 [Next] Enable the publishing server and select the server to be published (7)
 [Next] Select the database and publishing type to be published (8)
 [Next] Select Register Subscription Server (9)
 [Next] Complete the configuration. 2. Create a publication on servers B, C, and D. (1) Select the [Create and Manage Publications] command from the [Replication] submenu of the [Tools] menu. (2) Select the database where you want to create a publication, and then click [Create Publication].
(3) Click [Next] in the prompt dialog box of [Create Publication Wizard] and a dialog box will pop up. The contents on the dialog box are three types of copying. We now select the first one, which is the default snapshot release (you can check the help for the other two)
(4) Click [Next] and the system will ask you to specify the type of database server that can subscribe to the publication.
SQLSERVER allows data replication between different databases such as orACLE or ACCESS.
But here we choose to run "SQL
 SERVER 2000" database server (5) Click [Next] and a dialog box will pop up to define the article, that is, to select the table to be published. Note:
 If you selected transactional publishing, you can only select tables with primary keys in this step. (6) Select the publication name and description. (7) Customize the publication properties wizard to provide the following options:
Yes I will customize the data filtering, enable anonymous subscriptions and other custom properties No Create a publication according to the specified method (custom method is recommended)
(8)[Next step]
 Select the method to filter the release (9) [Next]
 You can choose whether to allow anonymous subscriptions. 1) If you choose signed subscriptions, you need to add a subscriber method on the publisher:
 [Tools]->[Replication]->[Configure properties of publication, subscription servers and distribution]->[Subscription Server] Add otherwise a prompt will appear when requesting a subscription on the subscription server: Change publication does not allow anonymous subscriptions. If anonymous subscriptions are still required, use the following solution [Enterprise Manager]->[Replication]->[Publication Content]->[Properties]->[Subscription Options]
 Select Allow anonymous subscription requests 2) If anonymous subscription is selected, the above prompt will not appear when configuring the subscription server (10) [Next]
 Set the snapshot agent schedule (11) [Next]
 After the configuration is completed, the database for creating the publication becomes a shared database with data srv1. Library name..author with fields: id, name, phone,
srv2. library name..author has fields: id, name, telphone, adress
Require:
If srv1.library name..author adds a record, the phone field of srv1.library name..author is updated, and the corresponding telphone field of srv1.library name..author is updated. --*/
--General processing steps--1. Create a connection server on srv1 so that you can operate srv2 in srv1 and achieve synchronization exec sp_addlinkedserver'srv2','','SQLOLEDB','srv2's sql instance name or ip'
exec sp_addlinkedsrvlogin'srv2','false',null,'username','password'
go
--2. On both computers srv1 and srv2, start msdtc (Distributed Transaction Processing Service) and set it to start automatically. My Computer - Control Panel - Administrative Tools - Services - Right-click Distributed Transaction Coordinator - Properties - Start - and set the startup type to Automatic Start
--Then create a job to call the above synchronization storage procedure regularly. Enterprise Manager--Management--SQL
 Server Agent - right click on the job - create a new job - enter the job name in the "General" item - "Step" item - create - enter the step name in "Step Name" - select "Transact-SQL" in "Type"
 Script (TSQL)"
--Select the database to execute the command in "Database" --Enter the statement to be executed in "Command":
 exec p_process
--Confirm--"Schedule" item--New schedule--Enter the schedule name in "Name"--Select your job execution schedule in "Schedule Type"--If you select "Recurring"
-- Click "Change" to set your schedule and then SQL
 Start the Agent service and set it to start automatically, otherwise your job will not be executed. Setting method:
My Computer - Control Panel - Administrative Tools - Services - Right-click SQLSERVERAGENT - Properties - Startup Type - Select "Automatic Startup" - OK.
--3. Method 2 for implementing synchronization: scheduled synchronization -- Create the following synchronization storage procedure create proc in srv1
 p_process
as
--Update the modified data update bset name=i.name,telphone=i.telphone
from srv2.library name.dbo.author
 b,author i
where b.id=i.idand
(b.name <>
 i.name or b.telphone
 <> i.telphone)
--Insert the newly added data insert srv2.library name.dbo.author(id,name,telphone)
select id,name,telphonefrom author
 i
where not exists(
select * from srv2.library name.dbo.authorwhere id=i.id)
--Delete deleted data (if necessary)
delete b
from srv2.library name.dbo.author
 b
where not exists(
select * from author where id=b.id)
go