Complete collection of MYSQL classic statements - Development

　1. Sort by surname strokes:
　 Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //From least to most
　 2. Database encryption:
　 select encrypt('original password')
　 select pwdencrypt('original password')
　 select pwdcompare('original password','encrypted password') = 1--the same; otherwise different encrypt('original password')
　select pwdencrypt('original password')
　select pwdcompare('original password','encrypted Password') = 1--the same; otherwise different
　3. Retrieve the fields in the table:
　declare @list varchar(1000),
　@sql nvarchar(1000)
　select @list=@list+','+b. name from sysobjects a,syscolumns b where a.id=b.id and a.name='Table A'
　set @sql='select '+right(@list,len(@list)-1)+' from table A'
　exec (@sql)
　4. Check the hard disk partition:
　EXEC master.. (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:
　　Starting 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: A table has more than 10,000 records. The first field of the table, RecID, is an auto-increasing field. Write a SQL statement to find out 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 arise, 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, so the order in the index may be inconsistent with that in the data table, which results in What is queried is not the original desired data.
　Solution
　1. Use order by select top 30 recid from A order by ricid. If the field is not auto-increasing, problems will arise
　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 the current database Select Name from sysobjects where xtype='u' and status>=0
where id in (select id from sysobjects where type = 'u' and name = 'table name')
　The effect of the two methods is the same
　11: View the views, stored procedures, and functions related to a certain 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 link 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 used in the future
　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 * into table from openrowset( 'SQLOLEDB ', 'sql server name'; 'Username'; '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.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

The above is the content of the development chapter of MYSQL classic statements. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!