Shulou(Shulou.com)06/01 Report--

This article will explain in detail the classic cases of MySQL-- data development. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Data Development-Classic

1. Sort by last name strokes:

Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as / / from less to more

two。 Database encryption:

Select encrypt ('original password') select pwdencrypt ('original password') select pwdcompare ('original password', 'encrypted password') = 1mi-same; otherwise different encrypt ('original password') select pwdencrypt ('original password') select pwdcompare ('original password', 'encrypted password') = 1Mel-same; otherwise it is different

3. Retrieve the fields in the table:

Declare @ list varchar (1000), @ sql nvarchar (1000) select @ list=@list+','+b.name from sysobjects a journal syscolumns b where a.id=b.id and a.nameplates' table A'set @ sql='select'+ right (@ list,len (@ list)-1) + 'from table A' exec (@ sql)

4. View the hard drive partition:

EXEC master..xp_fixeddrives

5. Compare the equality of table B of AMagna:

6. Kill all profiler processes:

DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill' + RTRIM (spid) FROM master.dbo.sysprocessesWHERE program_name IN ('SQL profiler',N'SQL Profiler') EXEC sp_msforeach_worker'?'

7. Record search:

Select Top N * From table from beginning to N records-N to M records (with primary index ID) Select Top MmurN * 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

For example, case 1: a table has more than 10, 000 records, and the first field RecID of the table is a self-growing 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 you write like this, there will be some problems if recid has a logical index in the table.

Select top 10 recid from A where... Is to look up from the index, while the following select top 30 recid from A looks in the data table, so because the order in the index may not be consistent with that in the data table, the query does not get the data you want to get.

Solution

1, use order by select top 30 recid from An order by ricid if the field is not self-growing, there will be a problem 2, add conditions in that subquery: select top 30 recid from A where recid >-1 example 2: query the last entry in the table, do not know how much data this table has, 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 the 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') has the same effect

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: query all databases created by users

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_type from information_schema.columnswhere table_name = 'table name'

15: data manipulation 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 sample select * from ITSV. Database name. Dbo. Table name-Import sample select * into table from ITSV. Database name. Dbo. Table name-delete linked servers exec sp_dropserver 'ITSV', 'droplogins' when not used in the future

-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)-Health cost table select * into table from openrowset ('SQLOLEDB', 'sql server name'; 'user name'; 'password', database name. Dbo. Table name)

-import the local surface into the remote table

Insert openrowset ('SQLOLEDB', 'sql server name'; 'user name'; 'password', database name. Dbo. Table name) select * from local table

-updating the local surface

Update bset b. List Atrea. Column A from openrowset ('SQLOLEDB', 'sql server name'; 'user name'; 'password', database name. Dbo. Table name) as an inner join local table bon a.column1=b.column1

-openquery usage requires you to create a connection

-- first create a connection to create a link 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 the local table update bset b. Column Baua. Column BFROM openquery (ITSV, 'SELECT * FROM database. Dbo. Table name') as an inner join local surface b on a. List Abelib. Column A

-3. Opendatasource/openrowset

SELECT * FROM opendatasource ('SQLOLEDB', 'Data Source=ip/ServerName;User ID= login; Password= password'). Test.dbo.roy_ta-- imports the local table into the remote table insert opendatasource ('SQLOLEDB', 'Data Source=ip/ServerName;User ID= login; Password= password'). Database. Dbo. Table name select * from local surface SQL Server basic function 1. The string function length and analysis returns the string containing the number of characters with 1 expression (Char_expr), but does not contain the following space 2 expression,start,length substring. The subscript of the string is from "1", start is the starting position, and length is the string length. In practical application, len (expression) takes its length 3L right (char_expr,int_expr) returns the int _ expr character on the right side of the string. Also use left on the contrary 4 replacement_value null (check_expression, replacement_value) if check_expression is empty, return the value of replacement_value, not empty, then return the check_expression character operation class 5 EXEC sp_addtype birthday spaceaddtype custom type, such as: EXEC sp_addtype birthday, datetime, 'NULL'6,set nocount {on | off} so that the returned results do not contain information about the number of rows affected by the Transact-SQL statement. If some statements contained in the stored procedure do not return a lot of actual data, this setting can significantly improve performance because of a significant reduction in network traffic. The SET NOCOUNT setting is set at execution or run time, not at parse time. When SET NOCOUNT is ON, no count is returned (representing the number of rows affected by the Transact-SQL statement). When SET NOCOUNT is OFF, return count common sense in the SQL query: from can follow the maximum number of tables or views: 256in the SQL statement Order by, query, first sort, and then take in SQL, the maximum capacity of a field is 8000, and for nvarchar (4000), because nvarchar is Unicode code. This is the end of the classic case of MySQL-- data development. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.