Transact_sql small manual ********************************* - Statement features - Data Operation SELECT - Retrieves Data Lines from Database Tables Add New Data Rows to Database Tables - Remove Data Rows from Database Tables - Update 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 - - Remove Index Create Procedure from the Database - Create a Stored Procedure Drop Procedure - Remove Storage Procedure from the Database Create Trigger - Create a trigger DROP TRIGGER - Remove the trigger Create Schema from the database - Add one to the database New Mode DROP Schema - Remove a mode from the database Create Domain - Create a data value domain ALTER DOMAIN - Change Domain Definition DOMAIN - Delete a domain from the database - Data Control Grant - Grant User Access Deny - Reject user access REVOKE - Unused user access - Transaction control commit - end current transaction Rollback - Stop current transaction SET Transaction - Define Current Services Data Access Features - Documentation Sqldeclare - Set Cursor for Query EXPLAN - To query the data access plan Open - Retrieve Query results Open a cursor fetch - Retrieve a line query result close - Close the cursor prepare - Prepare the SQL statement for dynamically - dynamically execute SQL statement Describe - Describe the prepared query - - Local variable declare @id char (10) - set @id = '10010001'select @ID =' 10010001 '--- Global variable --- must be @@ start - IF Elsedeclare @x int @y int @ z Intselect @X = 1 @Y = 2 @ z = 3iF @X> @Y print 'x> y' - Print string 'x> Y'Else if @Y> @z print' y> Z'Else Print 'z> y' --CASEuse panguupdate employeeset e_wage = case when job_level = '1' then e_wage * 1.08 when job_level = '2' then e_wage * 1.07 when job_level = '3' then e_wage * 1.06 else e_wage * 1.05 end - While Continue BreakDeclare @x int @X = 1 @ y = 1WHILE @X <3 begin print @X - Value while @
Y <3 Begin Select @c = 100 * @ x @Y print @c - Value of the print variable C 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 perform SELECT statement waitfor delay '01: 02: 03'select * from Employee - Case, etc., to execute the SELECT statement waitfor Time '23 after 11:00 pm. 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 Specifies Range) --------- You can only use wildcards in the WHERE clause 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) - ------ - Subqueries --------- Unless you can make sure that the inner SELECT returns only a row of values, ------- otherwise it should be used in the outer WHERE clause qualifier in select distinct column_name form table_name --------- distinct specified column values retrieved unique, not repeated select stocknumber, "stocknumber 10" = stocknumber 10 from table_name select stockname, "stocknumber" = count (*) from table_name group by stockname -------- Group by Packets the table, specify the same value in the specified column HAVING Count (*) = 2 -------- Having selection Specify group Select * from table1, table2 where table1.id * =
Table2.ID ------- Left external connection, there is no NULL in Table1 in Table1 to represent 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 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 table integrity DROP TABLE_NAME ------------ --- Full Remove Table *** ALTER TABLE *** --- Modify Database Table Structure ALTER TABLE DATABASE.OWNER.TABLE_NAME Add Column_name Char (2) NULL ..... sp_help table_name ---- Display Table Existing Features Create Table Table_Name (Name Char (20), Age Smallint, Lname Varchar (30)) Insert Into Table_name Select ......... ----- Implement a method of deleting columns (Create a new table) ALTER TABLE Table_name Drop Constrai NT stockname_default ---- Delete Stockname Default Constraint *** Function (/ * Common Function * /) *** ---- Statistical Function ---- AVG - Ask for average COUNT - Statistics Max - Seeking maximum min - Summary SUM - SUM - AVGUSE PANGUSELECT AVG (E_WAGE) AS DEPT_AVGWAGEFROM EMPLOYEGROUP BY DEPT_ID --MAX - Survival employee Name USE PANGUSELECT E_NAMEFROM EMPLOYEWHERE E_WAGE =
(SELECT MAX (E_WAGE) from Employee --stdev () - stdev () function Returns the standard difference 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 expression of the square root / *** approach to similar value *** / ceiling (numeric_expression) - Return> = The minimum integer returned by the expression can be the same as the expression - ITEGER / MONEY / REAL / FLOAT Type Floor (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 π 3.1415926535897936rand ([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 values of the first character - 1 two SO The first character of the undex function return value is 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) running results as follows ------------------- Abcabcabc null * / reverse ) - Function Run the specified string Character sequence reverse the resplace () - function returns the string of the specified substring / * SELECT Replace ('ABC123G', '123', 'DEF'
The resulting results are as follows ----------- ----------- Abcdefg * / space () - function returns a blank string stuff () - function with the specified length Replace the string of the string of position length with another substring ---- Data Type Conversion Function ---- Cast () Function Syntax Cast () (
