TRANSACT

xiaoxiao2021-04-08  4

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 () ( AS [Length]) Convert ( The 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 Year () - Function in Date_Expression 'Dateadd (, , , , , Date>) - Function Returns the Date Date plus the new date Dated Idiff (, , ) - function returns two specified date DatePart from the specified date date Date Date Datename (, ) - Function Returns the specified section of the date in the form of a string DatePart (, ) - Function Returns the specified section of the date in the form of an integer value GetDate () - Function Returns the current date and time in DateTime's default format ---- System Function ---- App_Name () - Function Returns the name of the currently executed application Coalesce () - Function Returns a number of expressions The first non-NULL expression value col_length (<'Table_name'>, <'Column_Name'>) - Function Returns the length value col_name (, ) - Function Returns The name of the specified field is the column name Datal ENGTH () - Function Returns the actual length DB_ID of data of data expressions DB_ID (['Database_name']) - Function Returns the number DB_NAME (Database_ID) - Function Returns the Name Host_ID () - Function Return to the server The name of the computer host_name () - Function Returns the name of the server-side computer ( [, seed increment]) [as column_name]) --IDENTITY () function is only used in the Select INTO statement for inserting an Identity Column Listing to the new table / * Select Id, 1, 1) as colortable * / isdate () - Function determines if the given expression is 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 a NULLIFIF () NULLIF () NULLIF (

转载请注明原文地址: http://www.9cbs.com/read-132852.html

New Post(0)