****************** Transact_sql ******************* - Statement Features - Data Operation SELECT - - Retrieve data lines from the database table and column INSERT - Add a new data line to the database table DELETE - Remove Data Rows from the Database Table - Update Data Data in Database Table - Data Definition CREATE TABLE - Create a database Table DROP TABLE - Remove Table from the Database - Modify Database Table Structure CREATE VIEW - Create a View Drop View - Create a View Create INDEX from the Database - Create an Index Drop Index for Database Table - from Database Delete Index Create Procedure - Create a Stored Procedure Drop Procedure - Remove Storage Procedure from 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 - Delete a mode from the database Create Domain - Create a data value field 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 Commit - End Current Transaction Rollback - Status Current Transaction SET Transaction - Defines Current Transaction Data Access Features - Programming SQL Declare - Settings Cursor Explan for Query - - To query the data access plan open - Retrieve Query results Open a cursor fetch - Retrieve a row query result close - Close the cursor prepare - Prepare the SQL statement for dynamically - Download SQL statement DESCR IBE - Description Preparationful query --- Local variable declare @id char (10) --set @ID = '10010001' SELECT @ID = '10010001' --- Global Variable --- must be @@ start - -IF else declare @x int @X = 1 @ = 2 @ z = 3 if @x> @yprint 'x> y' - Print string 'x> y' Else IF @Y> @z print 'y> Z' Else Print 'z> Y' --case use pin = case when job_level = '1' TENE E_WAGE * 1.08 When Job_LAGE * 1.08 WHEN JOB_LAVEL = '2' TEN E_WAGE * 1.07 When Job_LAGE * 1.06 Else 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 - Value of the Variable X While @Y <3 Begin Select @c = 100 * @ x @yprint @c - Valect @Y = @Y 1 End Select @ X = @X 1 SELECT @Y = 1 End --waitfor - Waiting for 1 hour 2 points and 3 seconds after executing SELECT statement waitfor delay '01: 02: 03 'SELECT * from Employee - Example arrived at night 11 I only executed SELECT statement waitfor Time '23:08:00 'Select * from Employee *** Select * *** select * (column name) from table_name Operator Value EX: (Host) from Table_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 Scope) Stockname Like '--------- (^ excluded specified range) --------- can only use wildcards in WHERE clauses using 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 - Descendment, ASC - Ascending Order By 1, 2 --------- BY List STOCKNAME = (SEL Ect 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, specify the same value in the specified column HAVING Count (*) =
2 ------- Having Selected Group Select * from table1, table2 where table1.id * = Table2.ID -------- There are Table1 in Table1 in Table1. No NULL indicates table1.id = * table2.id -------- Right Exterior connection Select stockname from table1 union [all] ----- UNION merge query result set, all-reserved repeating 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 *** 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 ---------- - Remove all rows in the table, still keep the integrity of the table DROP 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 ......... --- Implement the method of deleting columns (Create a new table) ALTER TABLE TABLE_NAME DROP CONSTRAINT stockName_Default ---- Delete Stockname Default Constrained * ** Function (/ * Common Function * /) *** ---- Statistical Function ---- AVG - Sign Average COUNT - Statistics MAX - Search MIN - Summary SUM - Squi --avg Use Pangu SELECT AVG (E_WAGE) AS DEPT_AVGWAGE FROM EMPLOYEE GROUP BY DEPT_ID --MAX - Survival employee Name Use Pangu SELECT E_NAME FROM EMPLOYE WHERE E_WAGE =
(SELECT MAX (E_WAGE) FROM EMPLOYEEE) --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 the 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 a cosine TAN (FLOAT_EXPIXPRESSION) in an arc-represented angle - Returns the correct COT (FLOAT_EXPIXPRESSION) of the angle represented by the arc - return The remainder of the arc indication / *** Anti-triangle function *** / asin (float_expression) - Returns the sinusoidal is the angle ACOS (float_expression) of the Float value - Returning the cosine is the FLOAT value in an arc indication The angle ATAN (FLOAT_EXPRESSION) - Returns the angle ATAN2 (float_expression1, float_expression2) in which the float value is represented - Returns orthosis is FLOAT_EXPRESSION1 / FLOAT_EXPRES-SONPRESSION1 / FLOAT_EXPRES-SION2 in the arc-demonstrated angle degRees (numeric_expression) - convert the arc conversion To return to the same data type as the expression, the same data type is -INTEGER / MONEY / REAL / FLOAT Type Radians (NuMeric_Expression) - Convert angle to radians Return to express the same data type as --integer / Money / Real / Float type exp (float_expression) - Returns an exponent value log (float_expression) - Returns the natural log 10 (FLOAT_EXPRESSION) - Returns the expression of 10-bottomless sqRT (float_expression) - - Return to the square root of the expression / *** All similar value function *** / ceiling (numeric_expression) - Back> = The minimum integer returned by the expression can be the same as the expression of the expression. - Numeric_Expression - Return <=
The data type of the minimum integer returned to the expression can be the same as the expression (NuMeric_Expression) - Returns the data-type and expressions returned by Integer_Expression-return data-type For Integer / Money / REAL / FLOAT Type ABS (NuMERIC_EXPIPRESSION) - The data type returned by the return expression is the same as the expression of the expression as the expression of -integer / money / real / float type Sign (Numeric_expression) - test parameters The positive and negative number returns 0 zero value 1 positive or -1 negative data type - the same can be used for Integer / Money / REAL / FLOAT type PI () - return value is π 3.1415926535897936 Rand ([integer_expression] ) - Use optional [integer_expression] to make seeds worth 0-1, random floating point numbers ---- ASCII () - Function Returns the ASCII code value of the left-end character of the character expression The char () - function is used to convert the ASCII code to a character - if the ASCII code value of the 0 ~ 255 is not entered between 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 converts numeric data to character data Ltrim () - function removes the space of the string head to the RTRIM () - function put the character string tail Space removes left (), right (), substring () - function Returns part of the string charIndex (), PatIndex () - Function Returns the start position of a specified substring in the string Soundex () - The function returns a four-bit character code - Soundex function can be used to find sound similar strings, but the SoundEx function returns only 0 Value Difference () - function returns the value of the two character expressions returned by the SoundEx function. Difference - 0 two SoundEx functions return The first character of the value is different - the first character of the two SoundEx functions The first character of the value is the same - 2 Two SoundEx functions return the first two characters of the value of the same - 3 two Soundex functions return the value of the first Two or three characters The same - 4 Two SoundEx functions Return Values Exactly the same quotename () - function returns a string from a specific pen / * select quotename ('ABC', '{') quotename ('ABC') The results are as follows -------------------------------- {[ABC] * / replicate () - function returns A string of repeating character_expression specified / * select replicate ('ABC', 3) Replicate ('ABC', -2) Run the results are as follows ------------------ --- Abcabcabc null * / reverse () - The function will reverse the character string of the specified string to reverse the REPLACE () - function returns the string of the specified substring / * SELECT Replace ('ABC123G', '123 ',' DEF '
