Concise common SQL command

This article is reproduced from: http://blog.9cbs.net/apple_001/68267.aspx

****************** TRANSACT_SQL *****************************

- Statement function - Data operation select - Retrieves Dark Data from Database Table and Column Insert - Add new data line to Database table DELETE - Remove Data Row Update from Database Table - Update Data in Database Table - - Data Definition Create Table - Create a Database Table Drop Table - Remove Table ALTER TABLE from the Database - Modify Database Table Structure Create View - Create a View Drop View - Remove View Create Index from the Database - for Database Table Create a Index Drop Index - Remove Index Create Procedure from the Database - Create a Stored Procedure Drop Procedure - Remove Storage Procedure from the Database Create Trigger - Create a trigger DRIGGER - Remove the trigger Create from the database Schema - Add a new mode to the database Drop Schema - Remove a mode from the database Create Domain - Create a Data Value ALTER DOMAIN - Change the Domain Definition DOMAIN - Delete a domain from the database - Data Control Grant - Grant user access deny - Reject user access REVOKE - Release user access - Transaction control commit - End current transaction Rollback - Statue Current Transaction SET Transaction - Define Current Services Data Access Features - Programming Sqldeclare - Set the cursor explan for the query Description Data Access Plan Open - Retrieval Query Result Open a Camper Fetch - Retrieve a line Query Results Close - Close the Cursor Prepare - Prepare SQL Statements Execute - Dynamic Execute SQL statement Describe - Description Prepared query

--- Local variable declare @id char (10) - set @ID = '10010001'select @ID =' 10010001 '

--- Global variable - must start with @@

- IF Elsedeclare @X INT @Y int @z intSelect @X = 1 @Y = 2 @ z = 3if @x> @yprint 'x> y' - print string 'x> Y'Else if @Y > @Z print 'y> Z'Else Print' Z> Y '

- Caseuset E_WAGE = Case When Job_level = '1' TEN E_WAGE * 1.08 When Job_Wage * 1.08 When Job_LEVEL = '2' TEN E_WAGE * 1.07 When Job_level = '3' TEN E_WAGE * 1.06 else E_WAGE * 1.05 END

--While Continue BreakDeclare @x int @y int @c @X @X = 1 @ y = 1WHILE @X <3 begin print @X - the value of print variable x While @Y <3 begin select @c = 100 * @ X @yprint @c - Value of the Cable Cable SELECT @Y = @Y 1 End Select @X = @X 1 SELECT @Y = 1 End - Waitfor - Case Waiting 1 hour 2 points 3 After seconds, the SELECT statement waitfor delay '01: 02: 03'select * from Employee - Examples waiting until 11:00 in the evening to perform SELECT statement waitfor Time '23: 08: 00'Select * from Employee

*** SELECT ***

select * (column names) from table_name (table) where column_name operator value ex :( host) select * from stock_information where stockid = str (nid) stockname = 'str_name' stockname like '% find this%' stockname like '[a -ZA-Z]% '--------- ([] Specified value range) Stockname Like' [^ fm]% '--------- (^ exclude specified range) ------- can only use wildcards in WHERE clauses using the LIKE key) or stockpath = 'stock_path' or stockNumber <1000 and stockindex = 24 Not stocksex = 'man' stockNumber Between 20 and 100 stocknumber in 10, 20, 30) Order by stockid desc (ASC) --------- Sort, Desc - Descending, ASC-Ascending Order By 1, 2 --------- BY list stockname = (Select stockname from stock_information where stockid = 4) -------- subquery --------- Unless you can make sure the inner SELECT returns a row, ------- - Otherwise, in the outer WHERE clause, use a IN Law Select Distinct Column_Name Form Table_Name --------- DistINCT specified to search the alone column value, do not repeat SELECT Stocknumber, "Stocknumber 10" = stockNumber 10 from table_name Select stockname, "stocknumber" = count (*) from table_name group by s TOCKNAME -------- Group By Put the table, specify the same value in the specified column HAVING Count (*) = 2 --------- Having Selected Group Select * from Table1, table2 where table1.id * = Table2.id -------- The left exterior connection, there is a Table1 in Table1 to indicate Table1.ID = * Table2.ID ------- - Right Exterior SELECT stockName from table1 union [all] ---- Union merge query result set, All-Reserved Duplicate line Select stockName from table2

*** insert ***

INSERT INTO TABLE_NAME (STOCK_NAME, Stock_Number) VALUES ("XXX", "XXXX") VALUES (SELECT Stock_Table2) --- value is SELECT statement *** update ***

Update table_name set stockname = "xxx" [Where stockid = 3] stockname = default stockname = null stocknumber = stockname 4

*** delete ***

Delete from table_name where stockid = 3 truncate table_name ----------- Delete all rows in the table, still keep the integrity of the table DROP TABLE_NAME --------------- Fully delete table

*** alter Table *** --- Modify the database table structure

Alter Table Database.OWNER.TABLE_NAME ADD Column_name Char (2) NULL ..... sp_help table_name ---- Display Table Existing Create Table Table_name (Name Char (20), Age Smallint, Lname Varchar (30)) INSERT INTO TABLE_NAME SELECT ......... - - Realize the method of deleting columns (Create a new table) ALTER TABLE TABLE_NAME DROP CONSTRAINT stockName_Default ---- Delete the stockname's default constraint *** function (/ * commonly used function*/)***

---- Statistical Function ---- AVG - Ask the average COUNT - Statistics Max - Search MIN - Summary SUM - Search

--AVGUSE PANGUSELECT AVG (E_WAGE) AS DEPT_AVGWAGEFROM EMPLOYEGROUP BY DEPT_ID

--MAX - Survey the highest payroll Name USE PANGUSELECT E_NAMEFROM EMPLOYEEWHERE E_WAGE = (SELECT MAX (E_WAGE) from EMPLOYEE)

--Stdev () - stdev () function Returns the standard deviation of all data in the expression

--Stdevp () - stdevp () function returns the overall standard difference

--VAR () - VAR () function returns a statistical variation of all values ​​in the expression

--VARP () - VARP () function Returns the total variation

---- Arithmetic function ----

/ *** Triangle function *** / sin (float_expression) - Returns sinusoidal COS (FLOAT_EXPRESSION) in an arc - Returns the cosine TAN (FLOAT_EXPIXPRESSION) in the arc-represented angle - Returns the angle represented by radians Orthodium COT (FLOAT_EXPRESSION) - Returns the remainder of the angle represented / *** Anti-triangular function *** / asin (float_expression) - Returns the sinusoidal is the angle ACOS (float_expression) indicated by the FLOAT value. - Returning the cosine is the angle ATAN (float_expression) of the float value - Return to the angle ATAN2 (float_expression1, float_expression2) of the float value - Return to orthode chitting is FLOAT_EXPRESSION1 / FLOAT_EXPRES-SION2 in the arc. Corner DegRees - Transform the arc into angle returns the same data type as the expression can be the same as the integer / money / real / float type radians (numeric_expression) - convert the angle to the arc return to the same data as the expression Types can be -integer / money / real / float type exp (float_expression) - Return Expression LOG (FLOAT_EXPRESSION) - Returns the natural log of log10 (float_expression) - Returns 10 For the end of the logarian SQRT (FLOAT_EXPRESSION) - Returns the square root of the expression / *** Nearly close to the similar value *** / ceiling (numeric_expression) - Return> = The minimum integer returned by the expression is the same as the expression To - ITEGER / MONEY / REAL / FLOAT Type Floor (Numeric_Expression) - Returns the data type returned by <= the minimum integer returned by the expression is the same as the expression of the same --integer / money / real / float type Round - Return to Integer_ Expression is the data type returned to the accuracy round-entered value. The same can be the same as the INTEGER / MONEY / REAL / FLOAT Type ABS (NuMeric_Expression) - The data type returned to the error is the same as the expression. Integer / Money / REAL / FLOAT Type Sign (NuMeric_Expression) - The positive and negative number of the test parameter returns 0 zero value 1 positive or -1 negative data type - the same can be INTEGER / MONEY / REAL / FLOAT Type PI () - Return value is π 3.1415926535897936rand ([Integer_Expression]) - Use optional [integer_expression] to do seed worth 0-1 random floating point numbers

---- String Function ---- ASCII () - Function Returns the ASCII code value of the character express left-end character Char () - Function is used to convert the ASCII code to the character - if not entered 0 ~ 255 The ASCII code value char function returns a NULL value LOWER () - function transforms all the strings to lowercase UPPER () - Function Converts the string to the uppercase STR () -Function to convert numeric data conversion For the character data Ltrim () - function removes the space of the string head removes the RTRIM () - function removes the space of the string of the string to Left (), substring (), substring () - Function Return to some strings Charindex (), Patindex () - Function Returns the start position of a specified substring in the string Soundex () - function Returns a four-bit character code - Soundex function can be used to find sound similar strings, but Soundex functions Return to numbers and Chinese characters to return 0 Values ​​DIFFERENCE () - Functions Returns the difference between the two character expressions returned by the SoundEx function - 0 two SoundEx functions return value different - 1 two SoundEx function returns the first character of the value of the same - 2 Two SoundEx functions return values ​​The first two characters of the value of the same - 3 Two SoundEx functions return values ​​The first two three characters of the value - 4 two SoundEx functions The return value is completely the same quotename () - function returns a string from a specific pen / * select quotename ('abc', '{') quotename ('ABC') running results --------- ------------------------- {{abc} [ABC] * /

Replicate () - Function Returns a string of repeating character_expression specified / * SELECT Replicate ('ABC', 3) Replicate ('ABC', -2) Run the results below ----------- ---------- Abcabcabc null * /

Reverse () - Function Run the specified string Character arrangement reverse the Replace () - function returns the string of the specified substring / * SELECT Replace ('ABC123G', '123', 'DEF') The results are as follows ----------- ----------- Abcdefg * /

The space () - function returns a blank string stuff () - function with a specified length, replacing a string of the position length with another subster

---- Data Type Conversion Function ---- Cast () Function Syntax As next () ( AS [Length]) Convert () function syntax is as follows () ( [LENGTH ], [, style])

SELECT CAST (100 99 As Char) Convert (VARCHAR (12), getdate ()) running results --------------------------- --- ------------ 199 Jan 15 2000

---- Date Function ---- DAY () - Function Returns the Date_Expression Date () - Function Returns the Month Value Values ​​in Date_Expression Year () - Function Returns the Year Value Dateadd in Date_Expression (, , ) - Function Returns the specified date Date plus the specified additional date period Number generated new date Datediff (, , ) - function returns two specified Dates Datename Datename (, ) - Functions in DatePart Datename (, ) - Function Returns in the form of integer values ​​in the form of a string Specify some of the date of the date at the getDate () function returns the current date and time of the system in DateTime ---- System Function ---- App_Name () - Function Returns the name of the currently executed application Co., Ltd. - Function Returns the value of the first non-NULL expression in many expressions col_length (<'Table_name'>, <'Column_Name'>) - Function Return to the length value col_name of the specified field in the table (, ) - Function Return to the name of the specified field in the table is the actual length DB_ID ([Database_name '] - function returns the data of the data of the data expression ([' database_name ']) - function Returns the number DB_NAME (Database_ID) - - Function Returns Name Host_ID () - Function Returns the name of the server-side computer Host_name () - Function Returns the name Identity ( [, SEED Increment]) [as column_name]) --IDENTITY The function is only used in the SELECT INTO statement to list one Identity Column to the new table / * Select Id, 1, 1) as column_name inTo newtable from oldtable * / isdate () - function judgment given Is the expression for a reasonable date? Isnull (, ) - The function replaces the NULL value in the expression with the specified value to determine if the given expression is a reasonable value newID () - function returns one The NULLIF (, ) - Nullif function returns a null value when EXPRESSION1 is equal to EXPRESSION2 equal to EXPRESSION 1. Returns the value of Expression1 when it is not equal.

Posted on August 08, 2004 12:05 am