Select Blog ********************************** - Statement Function of "" - Statement Features from Myclife - Data Operation SELECT - Retrieves Data Lines from Database Tables Add New Data Rows to Database Tables - Delete Dark Update 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 for Database Table 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 Drop Trigger - Remove the trigger from the database Create Schema - to the database Add a new mode DROP Schema - Remove a mode from the database Create Domain - Create a data value field ALTER DOMAIN - Change the domain definition DROP DOMAIN - Delete a domain from the database - Data Control Grant - Grant User Access Permissions deny - Reject user access REVOKE - Release user access - Transaction control commit - end current transaction Rollback - Abort current transaction SET Transaction - Define Current Services Data Access Features - Documentation SQL Declare - To query Set the Cursor Explan - For the Query Description Data Access Plan Open - Retrieve Query Result Open a Camper Fetch - Retrieve a row Query Results Close - Close the Cursor Prepare - Prepare the SQL Statement Execute - Dynamically Perform SQL Statements Describe - Description Prepare Query - Partial Variable Declare @id Char (10) --set @ID = '10010001' SELECT @ID = '10010001' --- Global Variables - The beginning of @@ - -IF else declare @x int @y int @z int @ Select @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 pangu update employee set 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 Break DecLare @x int @y int @ @X @X = 1 @ y = 1 while @X <3 begin print @x - Print Variables x Value While @Y <3 Begin SELECT @C = 100 * @ x @yprint @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 points after 3 seconds after executing SELECT statement waitfor delay '01: 02: 03 'SELECT * FROM EMPLOYEE - Example arrived until 11:00 in the evening, SELECT statement waitfor time '23: 08: 00 'SELECT * from Employee *** select *** select * (column name) from table_name (Table name) Where column_name Operator Value EX : (Host) Select * from stock_information where stockid = str_name 'stockname ='% Find this% 'stockname Like' [A-ZA-Z]% '--------- ( [] Scope of the specified value) Stockname Like '--------- (^ exclude specified range) --------- can only use the LIKE keyword WHERE Using wildcards) or stockpath = 'stock_path' OR stockNumber <1000 and stockindex = 24 Not stocksex = 'man' stocknumber between 20 and 100 stocknumber in (10, 20) 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) ------- - Subquerry --------- Unless the inner SELECT only returns a row of values, --------- Otherwise, in the outer WHERE clause Select DISTINCT COLUMN_NAME FORM TABLE_NAME --------- DistINct Specifies to retrieve alone column values, do not repeat SELE CT Stocknumber, "Stocknumber 10" = stockNumber 10 from table_name Select stockname, "stocknumber" = count (*) from table_name group by stockname ------- Group by Packet table, specified column Having the same value HAVING Count (*) = 2 --------- Having Selected Group Select * from table1, table2 where table1.id * = Table2.ID ------- Left External connection, there is no null2 in Table1 to indicate Table1.ID = * Table2.ID -------- Right Exterior connection Select stockname from table1 union [all] ----- UNION merge query results 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 ------- -------- Completely delete 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 Table Table_name (Name Char (20), Age Smallint, Lname Varchar (30)) Insert Into Table_name Select ......... - --- Implement Delete Columns (Created New table) ALTER TABLE TABLE_NAME DROP CONSTRAINT StockName_DEFAULT ---- Delete StockName's default *** function (/ * Common Function * /) *** ---- Statistical Function ---- AVG - Summary COUNT - Statistics Max - Ask Max MIN - Summary SUM - Summer SUM - AVG Use Pangu SELECT AVG (E_WAGE) AS DEPT_AVGWAGE FROM EMPLOYEE GROUP BY DEPT_ID --MAX - Some employee Name USE Pangu SELECT E_NAME FROM EMPLOYEE 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 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 --integer / Money / REAL / FLOAT class 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 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 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 - 3 Two SoundEx functions return the first two or three characters The same - 4 two SoundEx functions return value exactly the same quotename () - function returns a string of string from a particular character / * select quotename ('abc', '{') quotename ('ABC') running results - ------------------------------- {{abc} [ABC] * / replicate () - function returns one Repeating Character_EXPRESSION specified strings / * select replicate ('abc', 3) Replicate ('ABC', -2) running results as follows ------------------- - ABCABCABC NULL * / REVERSE () - Function Randide Sequence Sequence of Specified Strings Upside Down Replace () - Function Returns String / * SELECT Replace ('ABC123G', '123' The 'DEF') The result is as follows --------------------- ABCDEFG * / SPACE () - Function Returns a blank string stuff with a specified length ( ) - The function is replaced by another sub-string to specify the substring of position length ---- Data Type Conversion Function ---- Cast () Function Syntax Cast () (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 value Month () - - Function Returns the month value year () - function returns the year value dateadd in date_expression (,
,
) - Function Returns the New Date Datediff generated by Number during the specified date Date plus the specified extra date period.
,
,
) - Function Returns two specified dates Dual DatePart Datename (
,
) - The function returns the specified section DatePart in the form of a string.
,
- Function Returns the specified section of the date in the form of an integer value getDate () - function returns the system's current date and time in the default format of DateTime ---- System Function ---- App_Name () - Function Return The name of the currently executed application Coalesce () - function returns the value of the first non-NULL expression in many expressions col_length (<'Table_Name'>, <'Column_name'>) - Function Returns the specified field in the table Length value col_name
,
) - Function Returns Name of the specified field in the table is the actual length DB_ID ([Database_name '] - function returns the actual length DB_ID 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 Host_name () - Function Returns Name Identity (
[, SEED Increment]) [As column_name]) --IDENTITY () function is only used in the SELECT INTO statement to list a Identity Column to a new table / * Select Id, 1, 1) as column_name Into newTable From oldtable * / isdate () - Function determines if the given expression is reasonable date isnull
,
- Function Replaces the NULL value in the expression with the specified value to determine if the given expression is a reasonable numerical NEWID () - function returns a NULLIF of a UNIQUEIDENTIFIER type.
,
) - Nullif Function Returns NULL in Expression1 and Expression2 Returns the value of Expression1 or above is my collection, the author, I don't remember.
