TRANSACT

SQL Basics 1. Database query: SELECT Usage Select [TOP (Numeric] field list from Data table [WHERE Condition] [ORDER BY field] [ASC or DESC]

2. Add data: INSERT INTO Usage INSERT INTO Data Sheet (Field 1, Field 2, Field 3, ...) VALUES (Value of Field 1, Value of Field 2, Value of Field 3, ...)

3. Delete data: DELETE Usage Delete from Data Sheet [Where Conditions]

4. Update data: Update Update Datasheet Set Field 1 = Field Value 1, Field 2 = Field Value 2, ... [Where Condition]

5. Construction Data Sheet: CREATE TABLE Usage CREATE TABLE Data Table Name (Field 1 Name, Field 1 Type, Field 2 Name Field 2 Type, ...)

Access data table Common data Type: Text, Char (Number), Memo, Number, Int, Date / Time, Logical, OleObject

Auto Number Field Add Example: Create Table AAA (ID Int Id Id "NOT NULL, ABC VARCHAR (25) NULL

6. Change Data Sheet: ALTER TABLE Usage Add Field: ALTER TABLE Data Table Name Add Column Field Name Field Type

Delete field: ALTER TABLE Data Name DROP Column Field Name

7. Remove data table: Drop Table Usage

DROP TABLE Data Name

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

Alter Table [Table] ALTER [ID] Counter Constraint [table _P] primary key converts the ID column to the automatic number type, and set to the primary key

- 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) Value ("XXX", "XXXX") Value (SELECT stock_table2) --- value is a 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.