SQL statement highlights
- 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 denies - Reject user access REVOKE - Remove user access - Transaction control commit - End current transaction Rollback - Abort current transaction SET Transaction - Define Current Services Data Access Features - Programming SQL Declare - Set the Cursor Explan for Query Data Access Plan Open - Retrieve Query Result Open a Camper Fetch - Retrieve a line Query Results Close - Close the Cursor prepare - Prepare the SQL statement for dynamic SQL statements Execute - Dynamically perform SQL statement Describe - Description Preparationful query --- Local variable declare @id char (10) --set @ID = '10010001' SELECT @ID = '10010001' --- global variable --- must @@ start - IF elsedeclare @x int @X = 1 @ = 2 @ z = 3if @ X> @yprint 'x> y' - Print string 'x> Y'Else if @Y> @z print' y> Z 'Else Print' Z> Y '- Case Use Pangu Update 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.05end --WHILE CONTINUE BREAKdeclare @x int @y int @c intselect @x = 1 @ y = 1WHILE @X <3 Begin Print @X - Value of the Variable X While @Y <3 Begin Select @C = 100 * @ x @Y print @c - Value Select @ Y = @Y 1 end select @x = @X 1 SELECT @Y =
Heend - Waitfor - Example Waiting for 1 hour 2 minutes after 3 seconds to perform 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 * from Employee *** Select *** SELECT * (column name) from table_name "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 range) STOCKNAME LIKE '[^ fm]% '--------- (^ exclude specified 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 stock_information where stockid = 4) -------- - child query http://www.acnow.net/OyiPsXSPAIC --------- Unless you can ensure that the inner SELECT returns a row of values, e --------- Distinct Specifies to retrieve 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 Packets the table, specify the same value in the specified column HAVING Count (*) = 2 ------- Having Selected Group Select * from 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 outside 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 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 ......... --- Implement the delete column (create a new table ) ALTER TABLE TABLE_NAME DROP CONSTRAINT Stockname_Default ---- Delete Stockname Default Constraint *** Function (/ * Common Function * /) *** ---- Statistical Function ---- AVG - Summary Count COUNT - Statistics Max - Ask Maximum MIN - Summary SUM - Summer SUM - Ask - AVGUSE PANGUSELECT AVG (E_WAGE) AS DEPT_AVGWAGEFROM EMPLOYEGROUP BY DEPT_ID - MAX - Solving Sales Subsidic Name Use Pangu SELECT E_NAME FROM EMPLOYEE WHERE e_wage =
C (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 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) with arc-represented angle - Returns the correct COT (FLOAT_EXPIPRESSION) in the arc-represented angle - return After the angle representing the angle / *** Anti-triangle function *** / asin (float_expression) - Returns the sinusoidal is the angle ACOS (FLOAT_EXPIPRESSION) of the float value - Return the cosine is the radoat value The angle ATAN (FLOAT_EXPRESSION) - Returns the angle ATAN2 (float_expression1, float_expression2) of the float value - Returning is the angle deformation of FLOAT_EXPRESSION1 / FLOAT_EXPRES-SION2 (Numeric_expression) - Put the radia Convert to an angle return to the same data type as the expression can be to -integer / money / real / float type radians (numeric_expression) - convert the angle to radians to return to the same data type as the same data type can be --integer / Money / REAL / FLOAT type exp (float_expression) - Returns the expression of the exponent value log (float_expression) - Returns the natural log of log10 (float_expression) - Return to the expression of 10-bottom-to-values SQRT (Float_Expression) - Return to the expression of the square root / *** Near the similar value *** / ceiling (numeric_expression) - Return> = The minimum integer returned by the expression can be the same as the expression as the expression of -integer / Money / REAL / Float type FLOOR (Numeric_Expression) - Returns <=
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 C --Soundex function can be used to find sound similar strings, but the SoundEx function returns only 0 Values for numbers and Chinese characters. The function returns two character expressions returned by the SoundEx function. Value Difference - 0 Two SoundEx Functions Return Values Different - 1 Two SoundEx Functions Return Value The first character of the value - 2 Two SoundEx functions The first two characters of the value of the value - -3 The first two three characters of the two SoundEx function return values are the same - 4 two SoundEx functions Return Values Exactly the same quotename () - function returns a string from a particular character 括 / * 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 are as follows ------- ---- ----------- Abcabcabc null * / reverse () - Function The character arrangement of the specified string is reversed the resplace () - function returns the character that replaced the specified substring String / * SELECT Replace ('ABC123G', '123', 'DEF') operation results are as follows --------------------- Abcdefg * / space () - - Function Returns a blank string stuff () - function replacing the string with another sub-string - Data Type Conversion Function ---- Cast () Function Syntax Cast () (
