Advanced usage of mysql, advanced usage of mysql_PHP tutorial

Advanced usage of mysql, advanced usage of mysql

1. Instructions: 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: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: create table tab_new like tab_old (use old table to create new table)

　B: create table tab_new as select col1,col2… from tab_old definition only

5. Description: Delete the new table

drop table tabname

6. Description: Add a column

Alter table tabname add column col type

Note: Once a column is 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)

Description: Delete primary key: Alter table tabname drop primary key(col)

8. Description: Create an 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 a view: create view viewname as select statement

Delete view: drop view viewname

10. Description: A few simple basic sql statements

Selection: select * from table1 where range

Insert: insert into table1(field1,field2) values(value1,value2)

Delete: delete from table1 where range

Update: update table1 set field1=value1 where range

Search: select * from table1 where field1 like ’%value1%’ ---like’s syntax is very sophisticated, check the information!

Sorting: select * from table1 order by field1,field2 [desc]

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: select min(field1) as minvalue from table1

11. Description: Several advanced query operators

A: UNION operator

The UNION operator derives a result table by combining two other result tables (such as 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, every row in the derived table comes from either TABLE1 or 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 any 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 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. Description: Use outer connection

A. left (outer) join:

Left outer join (left join): The result set includes the matching rows of the join table and all rows of 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 matching join rows of the join table and all rows of the right join table.

C: full/cross (outer) join:

Full outer join: not only includes matching rows of the symbolic connection table, but also includes all records in the two connected tables.

12. 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 grouping criteria)

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:

Detached database: sp_detach_db; Attached database: sp_attach_db followed by indicates that attachment requires a complete path name

14. How to modify the name of the database:

sp_renamedb 'old_name', 'new_name'

2. Improvement

1. Description: Copy the 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 SQlServer)

Method 2: select top 0 * into b from 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,f from b;

3. Description: Copy tables between databases (use absolute paths for specific data) (Access available)

　insert into b(a, b, c) select d,e,f from b in ‘specific database’ where condition

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

4. Description: Subquery (Table name 1: a Table name 2: b)

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

5. Description: Display the article, submitter 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)

　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 (SELECT a,b,c FROM a) T where t.a > 1;

8. Description: usage of between, between limits the query data range and includes boundary values, not between does not include

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between value 1 and value 2

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 main table that is not in the secondary table

　delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

11. Description: Four-table joint query question:

select * from a left inner join b on 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 advance

SQL: select * from schedule where datediff('minute',f start 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 field desc) a, table name b where b. Primary key field = a. Primary key field order by a. Sorting field

Specific implementation:

About database paging:

declare @start int,@end int

@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 )'

exec sp_executesql @sql

Note: top cannot be followed directly by a variable, 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 result if the top field is a logical index (the data in the logical index may be inconsistent with the data in the data table, and if it is in the index during the query, the index will be queried first)

14. Description: First 10 records

select top 10 * 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 and monthly hot-selling product analysis , ranking by subject scores, 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

　(select a from tableA) except (select a from tableB) except (select a from tableC)

17. Description: Take out 10 pieces of data randomly

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(id) from tablename group by col1,col2,...)

　2),select distinct * into temp from tablename

delete from tablename

insert into tablename select * from temp

Evaluation: This operation involves the movement of a large amount of data. This approach is not suitable for large-capacity data operations

　3), for example: when importing data into an external table, for some reasons only a part of it is imported the first time, but it is difficult to judge the specific location, so it can only be imported in full next time, which will produce a lot of data. Duplicate fields, how to delete duplicate fields

alter table tablename

　--Add an auto-increment column

　add column_b int identity(1,1)

　delete from tablename where column_b not in(

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 the type, vendor, and pcs fields, arranged by the type field. Case can easily implement multiple selections, similar to the case in select.

　select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type

Display results:

type vender pcs

Computer A 1

Computer A 1

Disc B 2

Disc A 2

Mobile phone B 3

Mobile phone C 3

23. Description: Initialize table table1

TRUNCATE TABLE table1

24. Instructions: Select records from 10 to 15

　select top 5 * from (select top 15 * from table order by id asc) table_alias order by id desc

3. Skills

1. The use of 1=1 and 1=2 is more commonly used in SQL statement combinations

　“where 1=1” means to select all “where 1=2” and not select all,

For example:

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 write it directly as

Error! Directory entry not found.

Set @strSQL = 'select count(*) as Total from [' @tblName '] where 1=1 ' @strWhere 2. Shrink the database

　--Rebuild index

DBCC REINDEX

DBCC INDEXDEFRAG

　--Shrink data and logs

DBCC SHRINKDB

DBCC SHRINKFILE

3. Compress database

dbcc shrinkdatabase(dbname)

4. Transfer the database to the new user with existing user rights

exec sp_change_users_login 'update_one','newname','oldname'

go

5. Check the backup set

RESTORE VERIFYONLY from disk='E:dvbbs.bak'

6. Repair 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 NOCOUNT ON

DECLARE @LogicalFileName sysname,

　@MaxMinutes INT,

　@NewSize INT

USE tablename -- the name of the database 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 @OriginalSize int

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

　(DummyColumn char (8000) not null)

DECLARE @Counter INT,

　@StartTime DATETIME,

　@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 sysfiles WHERE name = @LogicalFileName)

AND (@OriginalSize * 8 /1024) > @NewSize

BEGIN -- Outer loop.

SELECT @Counter = 0

WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))

BEGIN -- update

INSERT DummyTrans VALUES ('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 NOCOUNT OFF

8. Description: Change a table

exec sp_changeobjectowner 'tablename','dbo'

9. Store changes in all tables

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch

　@OldOwner as NVARCHAR(128),

　@NewOwner as NVARCHAR(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. Write data directly in SQL SERVER in a loop

declare @i int

set @i=1

while @i<30

begin

insert into test (userid) values(@i)

set @i=@i 1

end

Case:

There is a table below, which requires that all those who failed to pass will be corrected, and based on an increase of 0.1 each time, they will just pass:

　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

Data Development-Classic

1. Sort by strokes of last name:

Select * From TableName Order By CustomerName Collate 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--the same; otherwise not the same encrypt('original password')

select pwdencrypt('original password')

Select pwdcompare('original password', 'encrypted password') = 1--the same; otherwise not the same

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 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 to see if they 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 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:

From the beginning to N records

Select Top N * From table

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

N to M records (must have primary index ID)

　Select Top M-N * From table Where ID in (Select Top M ID From table) Order by ID Desc

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

N to end record

　Select Top N * From table Order by ID Desc

Case

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

　select top 10 recid from A where recid not 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 A where... is searched from the index, while the subsequent select top 30 recid from A is searched in the data table. In this way, the order in the index may be different from the order in the data table. Inconsistent, this will lead to the query being different from the originally intended data.

Solution

1. Use order by select top 30 recid from A order by ricid. If the field does not grow automatically, problems will occur

　2. Also add conditions to that subquery: select top 30 recid from A where recid>-1

Example 2: Query the last record in the table. I don’t know how much data there is in the table 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

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

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.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 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 certain table

select column_name,data_type from information_schema.columns

where table_name = 'table name'

15: Data operations between different server databases

　--Create a linked server

　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 table from ITSV.database name.dbo.table name

　--Delete the linked server when no longer in use

exec sp_dropserver 'ITSV ', 'droplogins '

　--Connect 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 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 '; 'username '; 'password ', database name.dbo.table name)

select *from local table

　--Update local table

update b

set b.Column A=a.Column A

from openrowset( 'SQLOLEDB ', 'sql server name '; 'user name '; 'password ', database name.dbo.table name) as a inner join local table b

on a.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.tablename')

　--Import the local table into the remote table

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

select * from local table

　--Update local table

update b

set b.Column B=a.Column B

FROM openquery(ITSV, 'SELECT * FROM database.dbo.tablename') 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 Functions

SQL Server Basic Functions

1. String function for length and analysis

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 ) gets its length

　3,right(char_expr,int_expr) returns the int_expr character on the right side of the string, and uses left to reverse it

　4, isnull( check_expression , replacement_value) If check_expression is empty, the value of replacement_value is returned. If it is not empty, the check_expression character operation class is returned

　5,Sp_addtype custom data type

For example: EXEC sp_addtype birthday, datetime, 'NULL'

6,set nocount {on|off}

Causes the returned results to exclude 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. The SET NOCOUNT setting is set at execution or run time, not at parse 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 the count

Common sense

In SQL query: 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, then take

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

SQLServer2000 synchronous replication technology implementation steps

1. Preparatory work

1. Both the publisher and subscriber create a windows user with the same name and set the same password as a valid access user to the published snapshot folder

　--Management Tools

　--Computer Management

　--Users and groups

--Right-click user

　--New user

　--Create a user (SynUser) who belongs to the administrator group to log in to windows

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

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

　--Right click on this 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 has all permissions to the folder

　--OK

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

Start--Program--Management Tool--Service

　--Right-click SQLSERVERAGENT

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

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

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

Enterprise Manager

--Right-click SQL instance--Properties

--Security--Authentication

　--Select "SQL Server and Windows"

　--OK

5. Register with each other on the publisher and subscriber

Enterprise Manager

--Right-click on the SQL Server group

　--New SQL Server registration...

--Next step--In 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 username and password (SynUser)

　--Next step--Select the SQL Server group, or create a new group

--Next step--Complete

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

(Configured on the connection side, for example, if configured on the subscribing server, enter the IP of the publishing server in the server name)

Start--Programs--Microsoft SQL Server--Client Network Utilities

--Alias--Add

--Select "tcp/ip" for the network library --Enter the SQL server name for the server alias

　--Connection parameters--Enter the SQL server ip address in the server name

　--If you have modified the SQL port, deselect "Dynamicly determine port" and enter the corresponding port number

2. Formal configuration

1. Configure publishing server

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

(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 step] Select the distribution server. You can choose to use the publishing server itself as the distribution server or other SQL servers (choose yourself)

　(3) [Next step] Set up snapshot folder

Use the default \servernamePub

　(4) [Next step] Custom 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 the distribution database name and location using the default values ​​

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

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

　(8) [Next step] Choose to register a subscriber

　(9) [Next step] Complete the configuration

2. Create publications

Publish servers B, C, D

(1) Select the [Create and Manage Releases] command

from the [Copy] submenu of the [Tools] menu

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

(3) Click [Next] in the prompt dialog box of [Create Publishing Wizard] and the system will pop up a dialog box. The content on the dialog box is copied from the three types. We now choose the first one, which is the default snapshot release (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 the database server running "SQL SERVER 2000"

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

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

　(6) Select a publishing name and description

(7) Custom publishing attributes The options provided by the wizard:

Yes I will customize data filtering, enable anonymous subscriptions and other custom attributes

No Create a release based on the specified method (customized method is recommended)

　(8)[Next step] Choose how to filter and publish

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

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

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

in [Subscriber]

Otherwise, a prompt will appear when requesting a subscription on the subscriber server: Changing the publication does not allow anonymous subscriptions

If you still need to subscribe anonymously, use the following solution

　[Enterprise Manager]->[Copy]->[Published Content]->[Properties]->[Subscription Options] Select to allow anonymous subscription requests

　2) If you choose anonymous subscription, the above prompt will not appear when configuring the subscriber server

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

　(11)[Next step] Complete the configuration

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

There is data

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

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

Requirements:

srv1.Library name..author adds records, then srv1.Library name..author records increase

The phone field of srv1.Library name..author is updated, then the corresponding field phone of srv1.Library name..author is updated

　--*/

　--Broad processing steps

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

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

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 create a job to regularly call the above synchronization stored procedure

Enterprise Manager

　--Management

　--SQL Server Agent

　--Right-click job

　--New job

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

　--"Step" item

　--New

　--Enter the step name in "Step Name"

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

　--"Database" selects the database to execute the command

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

　--OK

-- "Scheduling" item

　--New schedule

--Enter the schedule name in "Name"

　--Select your job execution schedule in "Scheduling Type"

　--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 "Auto-Start"--OK.

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

　--Create the following synchronization stored procedure in srv1

create proc p_process

as

　--Update modified data

update b set name=i.name,telphone=i.telphone

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)

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

MySQL index types include:

(1) Ordinary index

This is the most basic index, it has no restrictions. It can be created in the following ways:

◆Create index

CREATE INDEX indexName ON mytable(username(length)); If it is CHAR, VARCHAR type, length can be less than the actual length of the field; if it is BLOB and TEXT type, length must be specified, the same below.

◆Modify table structure

ALTER mytable ADD INDEX [indexName] ON (username(length)) ◆Specify

directly when creating the table

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); Syntax to delete index:

DROP INDEX [indexName] ON mytable;

(2) Unique index

It is similar to the previous ordinary index, except that the value of the index column must be unique, but null values ​​are allowed. In the case of a composite index, the combination of column values ​​must be unique. It can be created in the following ways:

◆Create index

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ◆Modify table structure

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ◆Specify

directly when creating the table

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

(3) Primary key index

It is a special unique index that does not allow null values. Generally, the primary key index is created at the same time when creating the table:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); Of course, you can also use the ALTER command. Remember: a table can only have one primary key.

(4) Combination index

To visually compare single-column indexes and combined indexes, add multiple fields to the table:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL ); In order to further extract the efficiency of MySQL, it is necessary to consider establishing a combined index . Just build name, city, age into an index:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); When creating the table, the usernname length is 16, and 10 is used here. This is because generally the name length will not exceed 10, which will speed up the index query, reduce the size of the index file, and improve the update speed of INSERT.

If you create single-column indexes on usernname, city, and age respectively, so that the table has three single-column indexes, the query efficiency will be very different from the above combined index, which is far lower than our combined index. . Although there are three indexes at this time, MySQL can only use the single-column index that it thinks is the most efficient.

Establishing such a combined index is actually equivalent to establishing the following three sets of combined indexes:

usernname,city,age usernname,city usernname Why is there no combined index like city, age? This is a result of the "leftmost prefix" of the MySQL composite index. The simple understanding is to only start the combination from the leftmost one. Not only queries containing these three columns will use this combined index, the following SQL will use this combined index:

SELECT * FROM mytable WHREE username="admin" AND city="Zhengzhou" SELECT * FROM mytable WHREE username="admin" The following ones will not be used:

SELECT * FROM mytable WHREE age=20 AND city="Zhengzhou" SELECT * FROM mytable WHREE city="Zhengzhou"

(5) Timing of establishing index

Here we have learned how to create an index, so under what circumstances do we need to create an index? Generally speaking, columns appearing in WHERE and JOIN need to be indexed, but this is not entirely true because MySQL only indexes <, <=, =, >, >=, BETWEEN, IN, and sometimes LIKE will use the index. For example:

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='Zhengzhou' At this time, you need to index city and age. Since the userame of the mytable table also appears in the JOIN clause, it is also necessary to index it.

I mentioned just now that LIKE only needs to be indexed at certain times. Because MySQL will not use the index when making queries starting with wildcard characters % and _. For example, the following sentence will use index:

SELECT * FROM mytable WHERE username like'admin%' and the next sentence will not be used:

SELECT * FROM mytable WHEREt Name like'�min' Therefore, you should pay attention to the above differences when using LIKE.

(6) Shortcomings of index

The above mentioned the benefits of using indexes, but excessive use of indexes will cause abuse. Therefore indexing will also have its disadvantages:

◆Although the index greatly improves the query speed, it also reduces the speed of updating the table, such as INSERT, UPDATE and DELETE on the table. Because when updating the table, MySQL not only needs to save the data, but also save the index file.

◆Creating index files will occupy disk space. Generally, this problem is not serious, but if you create multiple combined indexes on a large table, the index file will expand quickly.

Indexes are only one factor in improving efficiency. If your MySQL has a large amount of data tables, you need to spend time researching and building the best indexes or optimizing query statements.

(7) Things to note when using indexes

When using indexes, there are some tips and precautions:

◆The index will not contain columns with NULL values

As long as a column contains a NULL value, it will not be included in the index. As long as one column in the composite index contains a NULL value, then this column will be invalid for the composite index. Therefore, when designing the database, we should not let the default value of the field be NULL.

◆Use short index

Index into the string, a prefix length should be specified if possible. For example, if you have a CHAR(255) column, if most values ​​are unique within the first 10 or 20 characters, then do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.

◆Index column sorting

MySQL query only uses one index, so if the index has been used in the where clause, the columns in order by will not use the index. Therefore, do not use sorting operations when the default sorting of the database can meet the requirements; try not to include sorting of multiple columns. If necessary, it is best to create composite indexes for these columns.

◆like statement operation

Generally, the use of like operations is not encouraged. If it must be used, how to use it is also a problem. Like “�a%” will not use the index but like “aaa%” will.

◆Don’t operate on columns

select * from users where YEAR(adddate)<2007; will operate on each row, which will cause the index to fail and perform a full table scan, so we can change it to

select * from users where adddate<‘2007-01-01’;

◆Do not use NOT IN and <> operations

The above introduces the MySQL index types.

This article is compiled from the Internet. The specific source is unclear. I used the knowledge here to some extent in project development, so I will summarize it and organize everything used in one place.

http://www.bkjia.com/PHPjc/986428.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/986428.htmlTechArticleAdvanced usage of mysql, advanced usage of mysql 1. Description: Create database CREATE DATABASE database-name 2. Description: Delete Database drop database dbname 3. Description: Back up sql server...