mysql--Data Development Classics and solutions

Data Development-Classic

• 1. Sort by last name stroke:

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 after password ') = same; otherwise different encrypt (' original password ') select Pwdencrypt (' original password ') Select Pwdcompare (' original password ', ' encrypted password ') = same;

• 3. Retrieve the fields from the table:

DECLARE @list varchar (@sql nvarchar) 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 the hard disk partition:

EXEC Master. Xp_fixeddrives

• 5. Compare the equality of A/b table:

if (select Checksum_agg (binary_checksum (*)) from A)     =    (select Checksum_agg (binary_checksum (*)) from B) print ' Equal ' elseprint ' not Equal '

• 6. Kill all the event Explorer processes:

DECLARE Hcforeach CURSOR GLOBAL for SELECT ' kill ' +rtrim (spid) from Master.dbo.sysprocessesWHERE program_name in (' SQL prof Iler ', N ' SQL Profiler ') EXEC sp_msforeach_worker '? '

• 7. Record Search:

Start to n records select top n * FROM table-------------------------------N to M Records (to 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

Example 1: A table with more than 10,000 records, the first field of the table RecID is the self-growth field, write an SQL statement, find the table of the 31st to 40th Records.
select top 10 recid from A where recid not in(select top 30 recid
From A) Analysis: If this writes a certain problem, if RecId has a logical index in the table.
select top 10 recid from A where……Is looked up from the index, and the back of select top RecId from A is looked up in the datasheet, so that the order in the index is likely to be inconsistent with the data table, which results in a query that is not the intended data.

Solution Solutions

1, with order by select top RecId from a order by ricid if the field is not self-growing, problem 2 will occur, and the condition is also added in that subquery: Select Top recid from a where reci d>-1 Example 2: The last record in the query table does not know how much data the table has, as well as the table structure. Set @s = ' SELECT top 1 * from T   where PID isn't in (select top ' + str (@count-1) + ' pid  from  t ') ' Print @s      ex EC  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 whe RE type = ' u ' and name = ' table name ') the same effect in both ways

• 11: View views, stored procedures, 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: Querying 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: Querying a table for fields and data types

Select Column_name,data_type from Information_schema.columnswhere table_name = ' table name '

• 15: Data manipulation between different server databases

--Create a linked server exec sp_addlinkedserver '   itsv ', ', ' SQLOLEDB ', ' Remote server name or IP address ' exec sp_addlinkedsrvlogin  ' itsv ', ' FAL Se ', 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--Remove linked server exec sp_dropserver  ' itsv ', ' droplogins ' when no longer in use

• – Connecting 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)--Raw cost surface select * into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

• – Import local tables to remote tables

Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name) Select *from Local surface

• – Update the local surface

Update bset 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 Bon 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 surface

--Update local table update bset b. column b=a. Column Bfrom 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 surface

SQL Server basic functions 1. String function length and parsing with 1,datalength (char_expr) returns a string that contains the number of characters, but does not contain the trailing space 2,substring (expression,start,length) to take a substring. The subscript of the string is from "1", start is the starting position, length is the string lengths, and the actual application takes Len (expression) to get its length 3,right (char_expr,int_expr) returns the int_expr character to the right of the string, Also use left in the opposite 4,isnull (check_expression, Replacement_value) If Check_expression is empty, return the value of Replacement_value, not empty, return check _expression character manipulation class 5,sp_addtype custom type For example: EXEC sp_addtype birthday, datetime, ' NULL ' 6,set nocount {On|off} so that the returned results do not contain information about the affected Information about the number of rows affected by the Transact-SQL statement. If some of the statements contained in a stored procedure do not return much of the actual data, this setting can significantly improve performance because of the significant reduction in network traffic. Set NOCOUNT settings are set at execution or runtime, not at parse time. When SET NOCOUNT is on, no count is returned (indicating the number of rows affected by the Transact-SQL statement).

 when SET NOCOUNT is OFF, return count common sense in SQL queries: up to how many tables or views can be followed from: 256 when an order by is encountered in an SQL statement, the query is sorted first , after fetching in SQL, the maximum capacity of a field is 8000, and for nvarchar (4000), because nvarchar is a Unicode code.