Transact SQL common statement and functions
Transact SQL statement function ============================================ ============================ - Data operation select - Retrieve data lines from the database table and column INSERT - to the database table Add a new data line DELETE - Remove Data Row from Database Table - Update Database Table 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 - Create an Index Drop Index for Database Table - Remove Index Create Procedure from the Database - Create a Stored Procedure Drop Procedure - from Remove stored procedures in the database Create Trigger - Create a trigger DROP TRIGGER - Remove trigger Create Schema from the database - Add a new mode to the database Drop Schema - remove a mode from the database Create Domain - Create a data Value ALTER DOMAIN - Change Domain Definition DOMAIN - Delete a domain from the database - Data Control Grant - Grant User Access Deny - Reject User Access Revoke - Release User Access Permissions - Transaction Control Commital End Current Services Rollback - Statue Current Transaction SET Transaction - Defines Current Transaction Data Access Features - Programming SQL Declare - Setting Cursor EXPLAN for Query - To Query Description Data Access Plan Open - Retrieval Query Result Open a cursor FETCH - Retrieve a line of query results close - Close the cursor Prepare - Prepare SQL Statement Execute - Dynamically Perform SQL Statement Describe - Description Prepared Query - Partial Variable Declare @ID Char (10) --set @ID = '10010001' SELECT @ID = '10010001' --- global variable --- must be @@ start - IF else declare @x tent @Y int @z int search @X = 1 @Y = 2 @ z = 3 if @x> @Y print 'x> y' - Print string 'x> y' Else IF @
Y> @z print 'y> Z' - Case Print 'Z> Y' - Case Use Pangu Update Employee Set E_WAGE = Case When Job_level = '1' TEN E_WAGE * 1.08 When Job_LAGE * 1.08 When Job_LEVEL = '2' TEN E_WAGE * 1.07 When Job_level = '3' TEN E_WAGE * 1.05 ELSE E_WAGE * 1.05 End --While Continue Break Declare @X INT @Y INT @C INT SELECT @X = 1 @ Y = 1 While @X <3 Begin Print @X - Print Variables x value while @Y <3 begin select @c = 100 * @ x @yprint @c - printed variable C value select @y @Y 1 End select @x = @X 1 SELECT @Y = 1 End --waitfor - Example Waiting for 1 hour 2 minutes after 3 seconds to execute SELECT statement waitfor delay '01: 02: 03 'SELECT * from Employee - Example arrived until 11:00 in the evening to execute SELECT statement waitfor time '23: 08: 00 '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 Specifies Range) --------- can only use wildcards in the WHERE clause that use the LIKE keyword) 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) --------- Subqueries --------- Unless you can make sure that 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 stockname -------- Group by Packet table packet, specify the same value in the specified column HAVING Count (*) = 2 ------- Having Selected Group Select * from table1.id * = Table2.ID -------- The left external connection, there is Table1 and there is no table2 NULL indicates Table1.ID = * Table2.ID -------- Right Exterior connection Select stockName from table1 union [all] -------- UNION merge query result set, all-reserved repeat line SELECT stockname from table2 insert insert into table_name (Stock_name, Stock_number) value ( "xxx", "xxxx" value (select Stockname, Stocknumber from Stock_table2) ------- value for the select statement to 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 the rows in the table, still maintaining the integrity of the table drop table table_name - ------ completely delete the 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 Table Table_name (Name Char (20), Age Smallint, Lname Varchar (30)) Insert Into Table_Name Select -------- Realize the Delete Column (Create a new table)
ALTER TABLE TABLE_NAME DROP CONSTRAINT StockName_Default -------- Remove StockName's default Constrained Complete Function (Function) Conversion Function Convert (Data Type, Value, Format) Statistics AVG - Ask Average Count - Statistics Max - Ask for maximum min - Summary SUM - SUM - SUM - As Dept_avgwage from Employee Group by DePT_ID MAX - Some employee Name USE PANGU SELECT E_NAME FROM EMPLOYEE E_WAGE = SELECT MAX (E_WAGE) FROM EMPLOYEEEEEEEEEEEEEE) stdev () --stdev () function Returns the standard deviation of all data in the expression --stdevp () --stdevp () function Returns the overall standard deviation var () - VAR () function Returns all values of all values in the expression VARP () - Varp () function Returns the overall variant arithmetic function triangle function sin (float_expression) - Returns sinusoidal COS (FLOAT_EXPIPRESSION) in an arc-angle-return-return The cosine TAN (FLOAT_EXPRESSION) (FLOAT_EXPRESSION) (FLOAT_EXPRESSION) is returned to the arc-represented angle - Returns the remaining anti-triangular function asin (float_expression) in the arc indication - Return to the sinusoidal Float value The angle ACOS (FLOAT_EXPRESSION) - Returning the cosine is the angle ATAN (FLOAT_EXPIXPRESSION) of the float value - Return to the angle ATAN2 represented by the Float value (float_expression1, float_expression2) -------- Returns that the float_expression1 / float_expres-sion2 is expressed in an arc angle DegRees ------ Transfer the arc to an angle to return to the same data type as the expression can be ----- integer / money / real / float type radians (numeric_expression) ------ The angle converted to radians Return to the same data type as the expression. ----- integer / money / real / float type exp (float_expression) - Return Expression index log (float_expression) - Return to expressions Natural log value log10 (float_expression) - Return to the expression of 10-bottom log -cules SQRT (FLOAT_EXPRESSION) - Returns the square root of the expression CEILING (NuMeric_Expression) ------- Back> = Expression The data type returned by the minimum integer can be the same as the expression ------- Integer / Money / Real / Float type floor (numeric_expression) ------- Return <=
The data type of the minimum integer returned by the expression is the same as the expression. ----- integer / money / real / float type round (numeric_expression) ------- Return to Integer_EXPRESSION Four Return Return Data ------- Type and expression can be the same as an expression of the absolute value returned by the return expression in the INTEGER / MONEY / REAL / FLOAT type ABS (NuMeric_Expression) ------- For ----- Integer / Money / REAL / FLOAT Type Sign (Numeric_expression) ------- Test Parameter Live Number Returns 0 Zero Value 1 Positive or -1 Negative Data Types - ----- The same can be the same as the expression of the INTEGER / MONEY / REAL / FLOAT type PI () ------- Return value is π 3.1415926535897936 RAND ([Integer_Expression]) ------- Select [Integer_Expression] to make a seed worth 0-1 random floating point nutrient string functions asCII () ------ function return character express left-end characters ASCII code value char () -------- The function is used to convert the ASCII code to the character ------ If the ASCII code value of the input 0 ~ 255 is not entered between the ASCII code value char function returns a null value loWer () ------ function transforms all the strings to Small write upper () ------ function transforms all strings to uppercase STR () ------ function converts numeric data LTRIM () ------ function put strings The space of the head removes the RTRIM () ------ function removes the space of the string of the tail (), the subString () - function returns part of the string charIndex (), Patindex () - function Returns the start position of a specified substrunch in the string Soundex () ------ function returns a four-bit character code ------ Soundex function can be used to find sound similar strings but Soundex functions Numbers and Chinese characters only return 0 value Difference () ------ function returns from Soundex The difference between the two character expressions returned ------ 0 Two SoundEx functions return values The first character of the value is different ------ 1 Two SoundEx functions return value the same ---- 2 Two SoundEx functions return value for the first two characters of the same ------ 3 two SoundEx functions return values The first two or three characters of the same ------ 4 two SoundEx function return value completely the same quotename () ------ function returns string / ** // * select quotename ('ABC', '{') Quotename ('ABC') The result is {{abc} [abc] * / replicate () ------ function returns a string of repeating character_expression specified / ** // * SELECT Replicate ('ABC', 3) Replicate ('ABC' , -2) The resulting result is as follows () ------------------------------ "Returns Replaced characters of the specified substring String / ** // * SELECT Replace ('ABC123G', '123', 'DEF'
The resulting result is as follows the abcdefg * / space () ------ function Returns a blank string stuff () ------------ function replaces the string of the position length with another subside series Data Type Conversion Function Cast () Function Syntax As next () (as [length]) Convert () Function Syntax CONVERT () ([Length], "STYLE]) SELECT CAST (100 99 as char) Convert (Varchar (12), getdate ()) The result is as follows 199 JAN 15 2000 Date Function Day () ------ function Returns the Date_Expression date value month () ------ function Returns the month value in date_expression Year () ------ Function Returns the year value dateadd (,,) ----- function returns the new date Datediff generated by the specified date date Date Date Date Datediff (,) --- - Function Returns two specified dates Duality Datename (, ------ "Returns the specified section of the date of the date in the form of a string (, ------ function in the form of an integer value Returns the specified section of the date GetDate () ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----- Function Returns the value of the first non-NULL expression in many expressions Col_length (<'table_name ", <' colorn_name '> ---- function returns the length value col_name of the specified field in the table (, ---- Function Return to the name of the specified field in the table, namely the column name DATALENGTH () ---- function Returns the actual length DB_ID of the data of the data expression (['Database_name']) ------ function Return to the database No. DB_NAME (Database_ID) ------ Function Returns the name of the database host_id () ----- letter Number returns a server-side computer name HOST_NAME () ----- Function Returns the name of the server-side computer Name Identity ([SEED Increment]) [As Column_name]) --IDENTITY () function is only used in the SELECT INTO statement Insert an Identity Column lists in the new table / ** // * Select Identity (int, 1, 1) as column_name into newtable from oldable * / isdate () ---- function judgment is reasonable Date IsNull (, - function replacing the null value in the expression with the specified value to determine if the given expression is a reasonable value newID () ---- function returns a UniqueIdentifier type Numerical nullif (, ---- nullif function Returns NULL value when expression1 is equal to EXPRESSION2, returns to Xpression1 value Note: This article is transferred from: http://www.lemongtree.com/286.html.aspx, This explanation.
