mysql sql statement collection

1. Instructions: Create database

CREATE DATABASE database-name

2. Instructions: Delete database

drop database dbname

3. Description: Back up sql server

--- Create a device for backup data

USE master

EXEC sp_addumpdevice 'disk' , 'testBack ', 'c:mssql7backupMyNwind_1.dat'

--- Start backup

BACKUP DATABASE pubs TO 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:createtabletab_new liketab_old (use old table to create new table)

<p><code> B： create table tab_new as select col1,col2… from tab_old definition only

5. Description: Delete new table

drop table tabname

6. Instructions: Add a column

Alter table tabname addcolumn col type

Note: Column Once added, it cannot be deleted. In DB2, the data type cannot be changed after the 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)

<p><code> Description: Delete primary key: Alter table tabname drop primary key(col)

8. Description: Create index: create[unique] index idxname on tabname(col….)

Delete 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 view: create view viewname as select statement

Delete view: drop view viewname

10 , Description: A few simple basic sql statements

Select: select* from table1 where Range

Insert: insert intotable1 (field1,field2) values(value1,value2)

delete:delete from table1 where range

update: update table1 set field1=value1 where Range

Find: select * from table1 where field1 like '%value1%' -- The syntax of -like is very exquisite, check the information!

Sort: select* from table1 orderby field1,field2 [desc ]

<p><code> Total count: select count as totalcount from table1

Sum: select sum(field1) as sumvalue from table1

Average: select avg(field1) as avgvalue from table1

Maximum: select max(field1) as maxvalue from table1

Minimum: selectmin (field1) as minvalue From bTable1

11, Description: Several high -level query operating words

A:

Union Union

Calculation symbols For example, TABLE1 and TABLE2) and derive a resulting table by eliminating any duplicate rows in the table. When ALL is used with UNION

(i.e.

UNION ALL), duplicate rows are not eliminated. In both cases, every row in the derived table comes from either TABLE1 or TABLE2.

B: EXCEPT Operator EXCEPT The 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 INTERSECT The operator derives a result table by including only 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: Several query result lines using operator words must be consistent. 12. Instructions: Use outer join A, left (

outer

)

join

: left outer join (left join) : Result set number Includes matching rows from the join table, as well as all rows from the left join table. SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT

JOIN

b ON a.a = b.c B: right(

outer

)

join

:Right outer join (right join): The result set includes both the matching join rows of the join table and the right join table of all rows. C: full/cross(

outer

)

join

: Full outer join: not only includes matching rows of the symbolic join table, Also includes two All records in the joined table.

12. Grouping:

Group by

:

A table, once the grouping is completed, only group-related information can be obtained after querying. group related information: (statistical information) count,sum,max,

min

,avg

Standards for grouping)<p><code>

When grouping in SQL Server: Fields of text, ntext, and image types cannot be used as the grouping basis

The fields in the selecte statistical function cannot be placed together with ordinary fields;

13. Operate the database:

<p><code> Detach the database: sp_detach_db; Attach the database: sp_attach_db followed by the instructions that the attachment requires a complete path name 14. How to modify the name of the database:

sp_renamedb

'old_name'

,

'new_name'

2. Improvement1. Description: Copy table (only copy structure, source table name: a new table name: b) (Access available) Method 1: select* into b

from

a where 1<>1 (only for SQlServer) Method 2: select top0 *

into

b

from

a2. Description: Copy table (copy data, source table name: a target table name: b) (Access available) insert into b(a, b, c)

select

d,e,f

from🎜 🎜b;🎜🎜🎜🎜3. Description: Copy of tables across databases (specific Data uses absolute path) (Access available)🎜🎜

insert into b(a, b, c) select d,e,f from b in 'Specific database' where Conditions

Example: ..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..

<p><code> 4. Description: Subquery (Table name 1: a Table name 2: b)

selecta,b,c froma wherea IN(select d from b ) or: select a,b,c from a where a IN (1,2,3)

5. Description : Display the article, author and last reply time

select a.title,a.username,b.adddate from 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)

<p><code> select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7. Description: Online view Query (table name 1: a)

select* from(SELECTa,b,c FROMa) T where t.a > 1;

8. Description: The usage of between, between includes boundary values ​​when limiting the query data range, not between does not include

select * fro table1 where a not between Value 1 and

Value 2

9. Instructions: How to use inselect* from table1 where a [

not

] in('value1','value2','value4','value6')

10. Description: Two related tables, delete the main table delete from table1 where notexists ( select *

from

table2

where

table1.field1 =table2.field1 )11. Description: Four table joint query problem: select * from a left inner joinb

on<p><code> a.a=b.b

right

inner join c on a.a=c.c inner join d on a.a=d.d where ..... 12. Description: Schedule reminder five minutes in advanceSQL: select * from Schedulewhere datediff('minute'

, starts with f Time, getdate())>5

13. Description: One sql statement to complete database paging

select top 10 b.* from (select top 20 Primary key field, sorting field

from

table name

order

by sorting fielddesc) a, table name b where b. Primary key field = a. Primary key field order by a. Sorting field Specific implementation: About database paging: <p><code> declare @start int,@end int

<p><code> @sql nvarchar(600)

set @sql='select top'+str(@end-@start+1)+'+from T where rid not in( select top'+str(@str-1)+'Rid from T where Rid>-1)'

<p><code> exec sp_executesql @sql

Note: A variable cannot be directly followed after top, so in practical applications this is the only way to perform special processing. Rid is an identification column. If there are specific fields after top, this is very beneficial. Because this can avoid the inconsistency in the actual table after the query results if the fields of top are logically indexed (the data in the logical index may be inconsistent with the data in the data table, and if the query is in the index, the first Query index)

<p><code> 14. Description: The first 10 records

select top 10 * form table1 where Range

<p><code> 15. Description: Select the b value in each group All the information corresponding to the largest record in the same data (similar usage can be used for monthly forum rankings, monthly hot-selling product analysis, ranking by subject performance, etc.)

select a,b,c from 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

(selecta fromtableA : C)17. Instructions: Take out 10 randomly Data select top 10 * from tablename order by newid()

18. Description: Randomly select records

select

newid ()19. Description: Delete duplicate records 1),delete from tablename

where

id

not

in (

select

max *

into

temp from tablename delete from tablename insert into tablename select * from temp

<p><code> Rating: This This kind of operation involves the movement of a large amount of data. This approach is not suitable for large-volume data operations3), for example: importing data into an external table, for some reasons only part of it is imported the first time, but it is very It is difficult to determine the specific location, so you can only import them all next time, which will produce a lot of duplicate fields. How to delete duplicate fields? add column_b int identity(1,1)

delete from tablename where column_b

notin(select max(column_b) from tablename

group by

column1,column2,...)

alter table tablename drop

column column_b

20. Description: List all table names in the database

select name from sysobjects where type='U' // U represents user

21. Description: List all column names in the table

select name from syscolumns where id=object_id('TableName')

22. Description: List type, vendor, pcs fields, Arrange by type field , case can easily implement multiple selections, similar to case in select .

select type,sum(case vender when 'A' then pcs else 0 end), summary (case vender when typeDisplay results: type vender pcsComputer A 1Computer A 1Disc B 2Disc A 2Mobile B 3 Mobile C 3 23. Description: Initialize table table1TRUNCATETABLEtable124. Description: Select records from 10 to 15

select

top5 *

from (

select top

15 * from

table order

by id

asc) table_alias

order by

id desc 3. Skills

The use of 1, 1=1, 1=2 is often used in SQL statement combinations

"where 1=1" means to select all " where 1=2" Unselect all, such as: if @strWhere !=''beginset@strSQL = 'select count (*) as Total from ['+ @tblName + '] where '+ @strWhere

end

else

beginset @strSQL = 'select count(*) as Total from ['

+ @tblName + ']'

end

We can write directly as

Error! Catalog entry not found. set@strSQL = 'select count(*) as Total from ['+ @tblName + '] where 1=1 stabilize'

+ @strWhere 2. Shrink the database

--Rebuild index

DBCC REINDEX

DBCC INDEXDEFRAG--Shrink data and logsDBCC SHRINKDB

DBCC SHRINK FILE

3. Compress database +

,

' oldname'

go5. Check the backup setRESTORE VERIFYONLY from

disk=

'E:dvbbs.bak'

<p><code> 6. Repair the database

<p><code> ALTER

DATABASE

[dvbbs]

SET

SINGLE_USER

GO

DBCC CHECKDB(

'dvbbs'

,repair_allow _data_loss)

WITH

TABLOCK

GO ALTERDATABASE[dvbbs] SETMULTI_USER

GO

7. Log clearing

SET NOCOUNT ON

DECLARE

​@LogicalFileName sysname,

@MaxMinutes INT,

@NewSize

INT

<p><code>USE tablename --The name of the database to be operated

SELECT @LogicalFileName = 'tablename_log', --The name of the log file

@MaxMinute s = 10, - - Limit on time allowed to wrap log.

@NewSize = 1 --The size of the log file you want to set (M)

Setup / initialize

DECLARE@ Original Size WHEREname

= @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= @LogicalFileNameCREATE TABLE

DummyTrans

(DummyColumn

char

(8000) not null)

DECLARE@Counter INT

,

@StartTime DATETIME, @TruncLog V ARCHAR

(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

sysfiles

WHERE

name

= @LogicalFileName)

AND (@OriginalSize * 8 /1024) &gt ; @NewSize BEGIN-- Outer loop.SELECT@Counter = 0

WHILE ((@Counter < @O rawSize / 16) AND ( 'Fill Log'

) DELETE DummyTrans

SELECT@Counter = @Counter + 1

END

EXEC(@Trunc Log)

ENDSELECT 'Final Size of '+ db_name() + ' LOG is '+ CONVERT

(VARCHAR(30), size

) + ' 8K pages or'

FROMsysfiles

WHERE name

= @LogicalFileNameDROPTABLEDummyTrans

SET NOCOUNT OFF8. Description : Change a tableexecsp_changeobjectowner 'tablename',

'dbo'9. Store and change all tablesCREATEPROCED UREdbo.User_ChangeObjectOwnerBatch @OldOwner

asNVARCHAR(128),@NewOwner

asNVARCHAR(128)AS

DECLARE @Name as

NVARCHAR(128)

DECLARE @Owner   as NVARCHAR(128)

DECLARE @OwnerName   as NVARCHAR(128)

DECLARE curObject CURSOR FOR

select 'Name'    = name,

   'Owner'    = user_name(uid)

from sysobjects

where user_name(uid)=@OldOwner

order by name

OPEN   curObject

FETCH NEXT FROM curObject INTO @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 curObject INTO @Name, @Owner

END

close curObject

deallocate curObject

GO

10、SQL SERVER中直接循环写入数据

declare @i int

set @i=1

while @i<30

begin

    insert into test (userid) values(@i)

    set @i=@i+1

end

案例：

有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好及格:

 Name     score

 Zhangshan 80

 Lishi       59

 Wangwu      50

 Songquan 69

while((select min(score) from tb_table)<60)

begin

update tb_table set score =score*1.01

where score<60

if  (select min(score) from tb_table)>60

  break

 else

    continue

end

数据开发-经典

1.按姓氏笔画排序:

Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多

2.数据库加密:

select encrypt('原始密码')

select pwdencrypt('原始密码')

select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码')

select pwdencrypt('原始密码')

select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同

3.取回表中字段:

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 A'

set @sql='select '+right(@list,len(@list)-1)+ ' from table A'

exec(@sql)

4. View hard disk partition:

EXEC master..xp_fixeddrives

5. Compare tables A and B Is it equal:

if (select checksum_agg(binary_checksum(*)) fromA)

<p><code> =

(select checksum_agg(binary_checksum (*)) from B)

print 'equal'

else

print 'not equal'

6. Kill Remove all event detectors Process:

DECLARE hcforeach CURSOR GLOBAL FOR SELECT'kill '+RTRIM(spid) FROM master.dbo.sysprocesses

WHERE program_name IN('SQL profiler',N'SQL Profiler')

EXEC sp_msforeach_worker '?'

7. Record search :

Start to N records

Select Top N * From Table

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

N to M records (must have primary index ID)

Select Top M-N * From TableWhere ID in(SelectTopM ID FromTable) OrderbyID Desc

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

N to end record

Select Top N * From TableOrderbyID Desc

Case

For example 1: There are more than 10,000 records in a table. The first field of the table, RecID, is an auto-increment field. Write a SQL statement to find the 31st to 40th records of the table.

<p><code> select top 10 recid from A where recid not in( select top 30 recid from A)

Analysis: If written like this, some problems will arise, if recid has a logical index in the table.

<p><code> select top 10 recid from A where... is searched from the index, and the following select top 30 recids from A searches in the data table, so because the order in the index may be inconsistent with that in the data table, the query will not result in the originally intended data.

<p><code> solution

1, use order by select top 30 recid from A order by ricid if the field is not auto-increasing , there will be a problem

2, also add conditions in that subquery: select top 30 recid from A where recid>-1

Example 2: Query the last record in the table. We don’t know how much data the table contains and the table structure.

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 sysobjects where xtype='u' and status>=0

10： Get all fields of a table

<p><code> selectnamefromsyscolumns whereid=object_id('table name')

select namefrom syscolumns where id in(selectid from sysobjects where type = 'u' and name = 'table name')

<p><code> The effect of the two methods is the same

11: View the views, stored procedures, and functions related to a certain table

select a. a.id = b.id and b.text like '%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 userselect

*

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 <> 0x0114: Query Fields and data types of a certain table select

column_name,data_type

from

information_schema.columns

where table_name = 'table name'

15: Different server databases Data operations between --Create linked server

exec

sp_addlinkedserver

'ITSV '

,

' ', 'SQLOLEDB ' , 'remote server name or ip address'exec sp_addlinkedsrvlogin 'ITSV '

,

'false ',null, 'username' , 'password' --Query exampleselect

*

from

ITSV.Database name.dbo.Table name

--Import exampleselect

*

into

Table

From sITSV. Database name.dbo. Table name -delete the link server Exec SP_DropServer

'ITSV ',

'droplogins' --Connect remote/LAN data (openrowset/openquery/opendatasource)--1, openrowset

--Query example

select *

from openrowset(

'SQLOLEDB ', 'sql server name'; 'username'; 'password', database name.dbo.table name)--Generate Local table select *

intotable

from openrowset( 'SQLOLEDB ', 'sql server name'; 'username'; 'Password' 数据, database name.dbo. Table name)

🎜🎜🎜 🎜🎜🎜🎜 🎜🎜🎜🎜 🎜🎜🎜🎜 🎜🎜🎜🎜 🎜🎜🎜🎜 🎜🎜🎜🎜 🎜🎜🎜🎜 🎜🎜🎜🎜 🎜🎜🎜🎜 🎜🎜🎜🎜 🎜🎜🎜🎜 🎜🎜🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 ” name'🎜🎜; 🎜🎜'username'🎜🎜; 🎜🎜'password'🎜🎜, database name.dbo.table name)🎜🎜🎜🎜select🎜🎜*🎜🎜from🎜🎜local table🎜🎜🎜 🎜- -Update local table 🎜🎜🎜🎜update🎜 🎜b🎜🎜🎜🎜set🎜 🎜b.Column A=a.Column A🎜🎜<p><code> from openrowset( 'SQLOLEDB ', 'sql server name'; 'username'; 'password' ,database name.dbo.table name)asa innerjoinlocal table b

ona.column1=b.column1

--openquery usage requires creating 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')

--put local table Import remote table

insert openquery(ITSV, 'SELECT * FROM database.dbo.tablename')

select * from local table

- -Update local table

update b

set b. Column B = a. table name'

)

asa innerjoin 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 tableinsert

opendatasource(

'SQLOLEDB '

,

'Data Source=ip/ServerName;User ID=Login name;Password= Password').Database.dbo.Table nameselect* from

Local table

SQL Server basic functionsSQL Server basic functions

1.Character String function length and analysis use

1, datalength(Char_expr) returns the string containing the number of characters, but does not include the following spaces

2,

substring

(expression,start,length) takes the substring , the subscript of the string is 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_exprth character on the right side of the string, and uses

left<p><code> to do the opposite 4,isnull( check_expression , replacement_value ) If check_expression is empty, return the value of replacement_value, which is not empty , return the check_expression character operation class

5, Sp_addtype custom data type 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 the 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. SET NOCOUNT settings are set at execution or run time, not at analysis time. When

SET NOCOUNT is ON

, no count (indicating the number of rows affected by the Transact-SQL statement) is returned. When

SET NOCOUNT is OFF

, return count

Common senseIn SQL query: The maximum number of tables or views that can be followed by

from

: 256

<p><code> appears in the SQL statement Order by

, when querying, sort first, then take

In SQL, the maximum capacity of a field is 8000, and for nvarchar (4000), since nvarchar is Unicode code.

SQLServer2000 synchronous replication technology implementation steps

<p><code> 1. Preparatory work

1. Create a windows user with the same name on both the publisher and subscriber servers, and set the same password as the publisher Valid access users for snapshot folders

--Admin Tools

--Computer Management

--Users and Groups

--Right-click user

--New user

--Create a Windows user (SynUser) belonging to the administrator group

2. On the publishing server, create a new shared directory, As the storage directory for published snapshot files, operate:

My Computer--D: Create a new directory named: PUB

--right click on this newly created directory

-- Properties--Share

--Select "Share this folder"

--Set specific user permissions through the "Permissions" button to ensure that the user (SynUser) created in the first step has the right to All permissions of the folder

--Confirm

3. Set the startup user of the SQL Agent (SQLSERVERAGENT) service (this setting is done for both publish/subscribe servers)

Start--Program-- Management Tools--Services

--right-click SQLSERVERAGENT

--Properties--Login--Select "This Account"

--Enter or select the windows login username created in the first step ( SynUser)

--Enter the user's password in "Password"

4. Set the SQL Server authentication mode to solve the permission problem when connecting (publish/subscribe servers do this setting)

<p><code> Enterprise Manager

--right-click on the SQL instance--Properties

--Security--Authentication

--Select "SQL Server and Windows"

--OK

5. Register each other on the publisher and subscriber

Enterprise Manager

--right-click the SQL Server group

--New SQL Server registration...

--Next step --Among the available servers, enter the name of the remote server you want to register --Add

--Next step--Connect and use, select the second "SQL Server Authentication"

--Next step- -Enter the username and password (SynUser)

--Next step--Select the SQL Server group, you can also create a new group

--Next step--Complete

6. For those who can only use IP, if the computer name cannot be used, register a server alias for it (this step is not used in the implementation)

<p><code> (configured on the connection side, for example, if configured on the subscriber server, enter the server name Publish server IP)

Start--Program--Microsoft SQL Server--Client Network Utility

--Alias--Add

--Network library select "tcp/ip "--Enter the SQL server name for the server alias

--Connection parameters--Enter the SQL server IP address for the server name

--If you modify the SQL port, deselect "Dynamicly determine the port", and Enter the corresponding port number

<p><code> 2. Formal configuration

<p><code> 1. Configure the publishing server

Open the Enterprise Manager and perform the following steps on the publishing server (B, C, D):

<p><code> (1) Select [Configure Publishing, Subscriber and Distribution] from the [Copy] submenu of the [Tools] drop-down menu to display the Configuration Publishing and Distribution Wizard

(2) [Next] Select the distribution server to choose to publish The server itself acts as a distribution server or other sql server (choose yourself)

(3) [Next step] Set the snapshot folder

<p><code> using the default \servernamePub

(4) [Next step] Customize Configuration

You can choose: Yes, let me set the distribution database properties to enable the publisher or set the publishing settings

No, use the following default settings (recommended)

(5) [Next step] Set up the distribution database The name and location adopt the default values ​​

(6) [Next step] Enable the publishing server to select as the publishing server

(7) [Next step] Select the database and publication type that need to be published

(8 ) [Next step] Choose to register the subscriber server

(9) [Next step] Complete the configuration

2. Create the publication

<p><code> on publishing servers B, C and D

(1) Select the [Create and Manage Publications] command from the [Copy] submenu of the [Tools] menu

(2) Select the database where you want to create a publication, and click [Create Publication]

(3) In Click [Next] in the prompt dialog box of [Create Publishing Wizard] and a dialog box will pop up. The content on the dialog box is copied from the three types. We now choose the first one, which is the default snapshot publication (you can check the help for the other two)

(4) Click [Next] The system requires specifying 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 the database server "SQL SERVER 2000"

(5) Click [Next] and the system will pop up a dialog box to define the article, that is, select the article to be published. Table

Note: If you selected transaction publishing earlier, you can only select tables with primary keys in this step

(6) Select the publication name and description

<p><code> (7) The options provided by the custom publication properties wizard:

<p><code> is where I will customize data filtering, enable anonymous subscriptions and or other custom properties

Whether to create publications according to the specified method (customized method is recommended)

(8)[Next step] Select the method of filtering publications

(9)[Next step] You can choose whether to allow anonymous subscription

1) If you choose signature subscription, you need to add a subscriber on the publisher

Method: [Tools]->[Copy]->[Configure publication, subscriber and distribution properties]-> Add

<p><code> to [Subscriber] otherwise a prompt will appear when requesting a subscription on the subscriber: Change the publication to not allow anonymous subscriptions

If you still need anonymous subscriptions, use the following solution

[Enterprise Management [Requester]->[Copy]->[Publish content]->[Properties]->[Subscription options] Select to allow anonymous request for subscription

2) If anonymous subscription is selected, there is no need to configure the subscriber when configuring the subscriber. The above prompt will appear

(10)[Next step] Set up snapshot agent scheduling

(11)[Next step] Complete the configuration

When the creation of the publication is completed, the database for the publication is also created. It becomes a shared database

with data

srv1. Library name..author has fields: id,name,phone,

srv2. Library name..author has fields :id,name,telphone,adress

Requirements:

srv1.Library name..author adds a record, then srv1.Library name..author record increases

srv1.Library name. If the phone field of .author is updated, the corresponding field phone of srv1.library name..author is updated

--*/

--rough processing steps

--1. Create a connection on srv1 Server to operate srv2 in srv1 to achieve synchronization

exec sp_addlinkedserver 'srv2','','SQLOLEDB', 'sql instance name of srv2 or ip'

exec sp_addlinkedsrvlogin 'srv2','false',null,'username' ,'password'

go

--2. In the two computers srv1 and srv2, start msdtc (distributed transaction processing service) and set it to automatically start

. My computer--Control Panel--Administrative Tools--Services--right-click Distributed Transaction Coordinator--Properties--Startup--and set the startup type to automatic startup

go

--then Just create a job and call the above synchronous processing stored procedure regularly

Enterprise Manager

--Management

--SQL Server Agent

--Right-click the job

-- Create a new job

--Enter the job name in the "General" item

--"Step" item

--New

--Enter the step name in the "Step Name"

--Select "Transact-SQL Script (TSQL)" in "Type"

--Select the database to execute the command in "Database"

--Enter the statement to be executed in "Command": exec p_process

--Confirm

--"Scheduling" item

--New schedule

--Enter the schedule name in "Name"

--Select you in "Scheduling Type" Job execution schedule

--If you select "Recurring"

--Click "Change" to set your schedule

Then start the SQL 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" Start"--OK.

--3. Method 2 to implement synchronization, scheduled synchronization

--Create the following synchronization stored procedure in srv1

createproc p_process

as

--Update modified data

update b set name=i.name,telph/code>

from srv2 .Library name.dbo.author b,author i

where b.id=i.id and

(b.name <> i.name or b.telphone <> i.telphone)

--Insert new data

insert srv2.Library name.dbo.author(id,name, telphone)

select id,name,telphone from author i

where not exists(

select * from srv2.library name.dbo.author where id=i.id)

--Deleted Deleted data (if necessary)

delete b

from srv2. library name.dbo.author b

where notexists(

select * from author where id=b.id)

go

The above has introduced a collection of mysql sql statements, including various aspects. I hope it will be helpful to friends who are interested in PHP tutorials.