Summary of common operation and methods of MSSQL

1. When installing SQL or SP patches, the system prompts for a pending installation operation, which requires a workaround to restart:

Locate HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager in the registry, and delete the PendingFileRenameOperations

2. Backup RESTORE Database

Backups: Backup Database [MyDB] to DISK = N ' d:\ db backup \mydb.bak ' with Noformat, noinit, NAME = n ' mydb-full database backup ', SKIP, Norewind, Nounload, STATS = 10

revert: Restore database [News] from DISK = N ' d:\ db backup \100209\mydb.bak ' with FILE = 1, nounload, STATS = ten, Replace,move ' test ' To ' D:\database\test.mdf ', move ' test_log ' to ' D:\database\test.ldf '

Note: With the following parameters to be separated by commas, can be set to overwrite the restore, and restore the path parameters, specific parameters view MSDN

3. Shrinking or compressing a database

--Rebuilding the index DBCC REINDEX DBCC INDEXDEFRAG

--shrink data and log DBCC SHRINKDB DBCC SHRINKFILE

--Compressed database DBCC SHRINKDATABASE (dbname)

4. Determine if there is a table or stored procedure or field, and then delete

Table: IF EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Table name] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1)

DROP TABLE [dbo]. [Table name]

Stored procedure: IF EXISTS (SELECT * from DBO. SYSOBJECTS WHERE ID = object_id (N ' [dbo].[ Stored procedure name] ') and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)

DROP PROCEDURE [dbo]. [Stored procedure name]

Field: If Exists (SELECT * from syscolumns where id=object_id (' table name ') and name= ' field name ')

ALTER TABLE table name drop column field name

5. Basic SQL statements

Select: SELECT * FROM table1 where range

Insert: INSERT INTO table1 (field1,field2) VALUES (value1,value2)

Delete: Delete from table1 where range

Updated: Update table1 set field1=value1 where range

Find: SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle, check the information!

Sort: SELECT * FROM table1 ORDER by FIELD1,FIELD2 [DESC]

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

Min: select min (field1) as MinValue from table1

6. Some tips

1). copy table (copy structure only, source table name: A new table name: b)

Law one: SELECT * into B from a where 1<>1

Law II: SELECT top 0 * into B from a

2). copy table (copy data, source table name: A target table name: b)

Insert into B (A, B, c) select d,e,f from B;

3). Copy of table across databases (use absolute path for specific data) (Access available)

Insert into B (A, B, c) Select d,e,f from B in ' specific database ' where condition

4). Create and place the current result set into a staging table

SELECT * from A to B

5). A SQL statement to take care of database paging

Select Top b.* from (select Top 20 primary key field, sort field from table name order by sort field desc) A, table name B where B. primary key field = A. primary key field order by a. Sort field

6). Randomly remove 10 data

Select Top * FROM tablename ORDER by NEWID ()

7). Random Selection of records

Select NEWID ()

8). Delete Duplicate records

Delete from TableName where ID not in (the Select Max (ID) from tablename GROUP by Col1,col2,...)

7. Common system functions 1) Conversion function

1. ASCII ()

Returns the ASCII value of the leftmost character of a character expression. In the ASCII () function, a string of pure numbers is not "enclosed", but a string with other characters must be used in ' surround ', or an error will occur.

2, CHAR ()

Converts an ASCII code to a character. If you do not enter an ASCII value between 0 and 255, CHAR () returns NULL.

3, LOWER () and Upper ()

LOWER () converts all strings to lowercase, UPPER () capitalizes all strings.

4. STR ()

Converts numeric data to character data.

STR (<float_expression>[,length[, <decimal>])

length specifies the size of the returned string, and decimal specifies the number of decimal digits to return. If the length is not specified, the default value is ten, and the decimal default is 0.

Returns null when length or decimal is a negative value;

When length is less than the number of digits to the left of the decimal point (including the sign bit), the length of * is returned;

Obey length first, then take decimal;

When the number of string bits returned is less than length, the left side complements the space.

5, CAST (<expression> as <data_ type>[length])

6. CONVERT (<data_ type>[length], <expression> [, Style])

1) data_type The data type defined for the SQL Server system, the user-defined data type cannot be used here.

2) length is used to specify how long the data is, and the default value is 30.

3) converts a char or varchar type to an integer type such as int or samllint, and the result must be a numeric value with a plus or minus sign.

4) The text type is converted to char or varchar type up to 8,000 characters, that is, the char or varchar data type is the maximum length.

5) The data stored by the image type is converted to binary or varbinary type, up to a maximum of 8,000 characters.

6) Convert integer value to Money or smallmoney type, processed in the currency unit of the defined country, such as RMB, USD, GBP, etc.

7) A bit type conversion converts a non-0 value to 1 and is still stored as a bit type.

8) attempts to convert to a different length of data type will truncate the conversion value and display "+" after the value is converted to identify the truncation occurred.

9) Use the style option of the CONVERT () function to display the date and time in a different format. The style is the conversion style number that is provided by the SQL Server system when converting Datatime and smalldatetime data to strings, and different style numbers have different output formats.

2) String processing function

1. LTRIM () Remove the space at the head of the string.

2. RTRIM () Remove the space at the trailing end of the string.

3. Left (<CHARACTER_EXPRESSION>, <integer_expression>) returns integer_expression characters from character_expression.

4. Right (<CHARACTER_EXPRESSION>, <integer_expression>) returns integer_expression characters from character_expression.

5, SUBSTRING (<EXPRESSION>, <starting_ position>, length) returns the section from the left of the string starting_ position characters character length characters.

6, CHARINDEX () returns the beginning of the occurrence of a specified substring in a string. CHARINDEX (< ' substring_expression ';, <expression>) where substring _expression is the character expression to look for, expressions Can be a string or a column-name expression. If no substring is found, a value of 0 is returned. This function cannot be used with text and image data types.

7, PATINDEX () returns the beginning of the occurrence of a specified substring in a string. PATINDEX (< '%substring _expression% ';, <column_ name>) where the substring expression must have a percent semicolon "%" or the return value is 0. Unlike the CHARINDEX function, wildcard characters can be used in substrings of the PATINDEX function, and this function can be used with char, VARCHAR, and text data types.

8, QUOTENAME () returns the string enclosed by a specific character. QUOTENAME (< ' character_expression ' >[, quote_ character]) where Quote_ character the character used to enclose the string, the default value is "[]".

9, REPLICATE () returns a string that repeats character_expression the specified number of times. REPLICATE (character_expression integer_expression) returns null if the Integer_expression value is negative.

10, REVERSE () reverses the character arrangement of the specified string. REVERSE (<character_expression>) where character_expression can be a string, a constant, or a column's value.

11, replace () returns the string that was replaced by the specified substring. REPLACE (<STRING_EXPRESSION1>, <string_expression2>, <string_expression3>) with String_expression3 Replace the substring string_expression2 in the string_expression1.

12, Space () returns a blank string with the specified length. SPACE (<integer_expression>) returns null if the Integer_expression value is negative.

13, STUFF () specifies the position, length of the substring with another substring substitution string. STUFF (<character_expression1>, <start_ Position>, <length>,<character_expression2>)

A null value is returned if the starting position is negative or the length value is negative, or the starting position is greater than the length of character_expression1. If length is greater than start_ position in Character_expression1 to the right length, Character_expression1 retains only the first character.

3) Date function

1, Day (date_expression) returns the date value in the Date_expression

2, month (date_expression) returns the months value in Date_expression

3, year (date_expression) returns the years value in Date_expression

4, DATEADD (<DATEPART>, <number>, <date>) returns the new date specified by date plus the specified extra date interval number.

5, DATEDIFF (<DATEPART>, <date1>, <date2>) returns the difference in DatePart of two specified dates, that is, Date2 exceeds the date1 gap value, The result value is an integer value with a positive sign.

6, Datename (<DATEPART>, <date>) returns the specified part of the date as a string in this section. specified by DatePart.

7, DATEPART (<DATEPART>, <date>) returns the specified part of the date as an integer value. This section is specified by DatePart.

DATEPART (dd, date) is equivalent to day (date)

DATEPART (mm, date) equals month (date)

DATEPART (yy, date) is equivalent to year (date)

8, GETDATE () returns the current date and time of the system in the default format of datetime

Summary of common operation and methods of MSSQL