The Transact of my own sort

transact_sql********************

--Statement function
--Data manipulation
SELECT-Retrieve rows and columns of data from a database table
INSERT-Add new data rows to a database table
Delete-Deletes rows of data from a database table
Update--updating data in a database table
--Data definition
CREATE table--Creating a database table
DROP table--Deletes a table from the database
ALTER TABLE--modifying database table structure
Create View--Creates a view
Drop view-Removing views from the database
Create index--creates an index for a database table
Drop INDEX--deletes an index from the database
Create PROCEDURE--Creating a stored procedure
Drop PROCEDURE--removing stored procedures from the database
Create TRIGGER--creating a trigger
Drop TRIGGER--removing triggers from the database
CREATE schema--Adding a new schema to the database
Drop schema--Deletes a schema from the database
Create domain--creating a data domain
Alter domain--changing field definitions
Drop domain--deletes a field from the database
--Data control
Grant-Granting User access
Deny--Deny user access
REVOKE--Unlock user access rights
--Transaction control
COMMIT--End current transaction
ROLLBACK--Abort current transaction
SET TRANSACTION--Defining the current transaction data access characteristics
--Programmed SQL
DECLARE--Set cursors for queries
Explan--Describes a data access plan for a query
Open--Retrieving query results opens a cursor
FETCH--Retrieves a row of query results
Close--Closing cursors
PREPARE-Preparing SQL statements for dynamic execution
Execute-Execute SQL statements Dynamically
DESCRIBE--Describe a prepared query

---local variables
DECLARE @id char (10)
--set @id = ' 10010001 '
Select @id = ' 10010001 '

---global variables
---must begin with @@

--if ELSE
DECLARE @x int @y int @z int
Select @x = 1 @y = 2 @z=3
If @x > @y
print ' x > y '--printing 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 @c int
Select @x = 1 @y=1
While @x < 3
Begin
Print @x--Prints the value of the variable x
While @y < 3
Begin
Select @c = 100*@x + @y
Print @c--printing the value of variable C
Select @y = @y + 1
End
Select @x = @x + 1
Select @y = 1
End

--waitfor
--An example waits 1 hours 2 minutes 3 seconds before executing the SELECT statement
WAITFOR DELAY ' 01:02:03 '
SELECT * FROM Employee
--for example, wait until 11 o'clock at 8 minutes before executing the 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 (NID)
StockName = ' Str_name '
StockName like '% find this% '
StockName like ' [a-za-z]% '---------([] Specify range of values)
StockName like ' [^f-m]% '---------(^ exclusion specified range)
---------only use wildcard characters in WHERE clauses that use the LIKE keyword)
or Stockpath = ' Stock_path '
or Stocknumber < 1000
and Stockindex = 24
Not stocksex = ' mans '
Stocknumber between and 100
Stocknumber in (10,20,30)
ORDER BY Stockid DESC (ASC)---------Sort, desc-descending, asc-ascending
Order by 1,2---------by column number
StockName = (select StockName from stock_information where Stockid = 4)
---------subquery
---------unless you can ensure that the inner select returns only the values of one row,
---------Otherwise, you should use an in qualifier in the outer WHERE clause
SELECT DISTINCT column_name FORM table_name---------DISTINCT specifies that unique column values are retrieved and not duplicated
Select Stocknumber, "Stocknumber +" = Stocknumber + from table_name
Select StockName, "stocknumber" = count (*) from table_name GROUP BY StockName
---------GROUP by groups the table by row, specifying that the column has the same value
Having count (*) = 2---------have the specified group selected

SELECT *
From Table1, table2
where Table1.id *= table2.id--------A left outer join, some in the Table1 and no null representation in Table2
Table1.id =* table2.id--------Right outer connection

Select StockName from table1
Union [ALL]-----Union merge query result set, all-keep duplicate rows
Select StockName from Table2

insert***

INSERT INTO table_name (stock_name,stock_number) value ("XXX", "xxxx")
Value (select StockName, stocknumber from 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 and still preserve the integrity of the table
DROP TABLE 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 has features
CREATE TABLE table_name (name char (), age smallint, lname varchar (30))
INSERT INTO table_name SELECT ...-----implement method to delete a column (Create a new table)
ALTER TABLE table_name DROP CONSTRAINT stockname_default Delete stockname default constraint----

function (///* Common functions * *) * * *

----Statistical function----
AVG--averaging
Count--Number of statistics
Max--Find the maximum value
Min--Find the minimum value
Sum--sum

--avg
Use Pangu
Select AVG (e_wage) as Dept_avgwage
From employee
GROUP BY dept_id

--max
--the highest-paid employee name
Use Pangu
Select E_name
From employee
where e_wage =
(select Max (e_wage)
From employee)

--stdev ()
--stdev () function returns the standard deviation of all data in an expression

--STDEVP ()
--STDEVP () function returns the overall standard deviation

--var ()
--var () function returns the statistical variance of all values in an expression

--varp ()
--varp () function returns the total variance number

----Arithmetic functions----

/*** Trigonometric ***/
Sin (float_expression)--Returns the sine of the angle in radians
COS (float_expression)--Returns the cosine of the angle in radians
TAN (float_expression)--Returns the tangent of the angle in radians
COT (float_expression)--Returns the cotangent of the angle in radians
/*** Inverse Trigonometric Functions ***/
ASIN (float_expression)--Returns the angle that the sine is the float value in radians
ACOS (float_expression)--Returns the angle in radians that the cosine is the float value
Atan (float_expression)--Returns the angle in radians that the tangent is a float value
ATAN2 (Float_expression1,float_expression2)
--Returns the tangent is the angle of the float_expression1/float_expres-sion2 in radians
DEGREES (numeric_expression)
--converting radians to angles returns the same data type as the expression can be
--integer/money/real/float type
RADIANS (numeric_expression)--Converts the angle to radians returns the same data type as the expression can be
--integer/money/real/float type
EXP (float_expression)--Returns the exponential value of an expression
LOG (float_expression)--Returns the natural value of an expression
LOG10 (float_expression)--Returns the 10-based pair value of an expression
SQRT (float_expression)--Returns the square root of an expression
/*** Approximate value function ***/
CEILING (numeric_expression)--the smallest integer that returns a >= expression returns the same data type as an expression
--integer/money/real/float type
FLOOR (numeric_expression)--the smallest integer that returns a <= expression returns the same data type as an expression
--integer/money/real/float type
ROUND (numeric_expression)--Returns the data returned with a rounded value of integer_expression precision
--the type is the same as an expression and can be integer/money/real/float type
ABS (numeric_expression)--Returns the absolute value of an expression that returns the same type of data as an expression
--integer/money/real/float type
SIGN (numeric_expression)--The positive sign of the test parameter returns the data type returned by a 00-value 1 positive or-1 negative number
--the same as an expression can be integer/money/real/float type
Pi ()--The return value is pi that is 3.1415926535897936
RAND ([integer_expression])--seed with an optional [integer_expression] is worth 0-1 random floating-point numbers


----String function----
ASCII ()--function returns the ASCII code value of the leftmost character of a character expression
CHAR ()--function is used to convert ASCII code to characters
--If you do not enter the ASCII code value between 0 ~ 255 The CHAR function returns a null value
LOWER ()--function converts a string to lowercase
UPPER ()--function converts a string to uppercase
STR ()--function converts numeric data to character data
LTRIM ()--function removes the space of the string header
RTRIM ()--function to remove the trailing space of the string
Left (), right (), SUBSTRING ()--function returns a partial string
CHARINDEX (), PATINDEX ()--function returns the starting position of a specified substring in a string
SOUNDEX ()--function returns a four-bit character code
The--soundex function can be used to find strings with similar sounds but the SOUNDEX function returns only 0 values for both numbers and characters
Difference ()--function returns the difference of the value of the two-character expression returned by the SOUNDEX function
--2 Soundex function returns the first character of the value is different
--12 Soundex function returns the same first character of the value
--22 Soundex function returns the same 12th character of the value
--32 Soundex function returns the same 123th character of the value
--42 Soundex function return values are exactly the same


QuoteName ()--function returns a string enclosed by a particular character
/*select QuoteName (' abc ', ' {') QuoteName (' abc ')
The results of the operation are as follows
----------------------------------{
{ABC} [abc]*/

REPLICATE ()--function returns a string that repeats character_expression a specified number of times
/*select replicate (' abc ', 3) replicate (' abc ',-2)
The results of the operation are as follows
----------- -----------
ABCABCABC null*/

REVERSE ()--the function reverses the character order of the specified string
Replace ()--function returns a string that is replaced with a specified substring
/*select replace (' abc123g ', ' 123 ', ' Def ')
The results of the operation are as follows
----------- -----------
abcdefg*/

Space ()--the function returns a blank string with a specified length
STUFF ()--the function replaces a substring with another substring at the specified position length


----data type conversion function----
The CAST () function syntax is as follows
CAST () (<expression> as <data_ type>[length])
The CONVERT () function syntax is as follows
CONVERT () (<data_ type>[length], <expression> [, Style])

Select CAST (100+99 as char) convert (varchar (), GETDATE ())
The results of the operation are as follows
------------------------------ ------------
199 15 2000.

----Date function----
Day ()--function returns the date value in Date_expression
MONTH ()--function returns the month value in Date_expression
Year ()--function returns the value of years in Date_expression
DATEADD (<datepart>,<number>,<date>)
--function returns a date with the specified date plus the specified extra date interval number
DATEDIFF (<datepart>,<number>,<date>)
--function returns the difference in datepart between two specified dates
Datename (<datepart> <date>)--function returns the specified part of a date as a string
DATEPART (<datepart> <date>)--function returns the specified part of a date as an integer value
GETDATE ()--function returns the current date and time of the system in datetime default format

----System Functions----
App_name ()--function returns the name of the currently executing application
COALESCE ()--function returns the value of the first non-null expression in many expressions
Col_length (< ' table_name ', < ' column_name ' >)--function returns the length value of the specified field in the table
Col_name (&LT;TABLE_ID&GT; <column_id>)--function returns the name of the specified field in the table, which is the column name
Datalength ()--function returns the actual length of data in a data expression
DB_ID ([' database_name '])--function returns the number of the database
Db_name (database_id)--function returns the name of the database
HOST_ID ()--function returns the name of the server-side computer
HOST_NAME ()--function returns the name of the server-side computer
IDENTITY (<data_type>[, Seed increment]) [as column_name])
The--identity () function is used to insert an IDENTITY column column into a new table only in the SELECT INTO statement
/*select identity (int, 1, 1) as column_name
Into newtable
From oldtable*/
ISDATE ()--function to determine whether the given expression is a reasonable date
ISNULL (&LT;CHECK_EXPRESSION&GT; <replacement_value>)--the function replaces the null value in an expression with a specified value
IsNumeric ()--function to determine whether the given expression is a reasonable value
NEWID ()--function returns a value of uniqueidentifier type
Nullif (&LT;EXPRESSION1&GT; <expression2>)
The--nullif function returns the value of a expression1 if the value is not equal when the expression1 is equal to Expression2