Large collection of various SQL statements

Transact_ SQL small manual

********************

-- Sentence skill

-- Data Operations

Select -- retrieve data rows and columns from database tables

Insert -- add new data rows to the database table

Delete -- delete data rows from a database table

Update -- update data in the database table

-- Data Definition

Create Table -- create a database table

Drop table -- delete a table from a database

Alter table -- modify the database table structure

Create View -- create a view

Drop View -- delete a view from a database

Create index -- create an index for the database table

Drop index -- delete an index from a database

Create procedure -- create a stored procedure

Drop procedure -- delete a stored procedure from a database

Create trigger -- create a trigger

Drop trigger -- delete a trigger from a database

Create schema -- Add a new schema to the database

Drop schema -- delete a schema from the database

Create Domain -- create a Data Value Domain

Alter Domain -- change domain definition

Drop Domain -- delete a domain from the database

-- Data Control

Grant -- Grant the user access permission

Deny-Deny User Access

Revoke -- revoke User Access Permissions

-- Transaction control

Commit -- end the current transaction

Rollback -- abort the current transaction

SET transaction -- define the data access features of the current transaction

--ProgramSQL

Declare -- set the cursor for the query

Explain -- describe the data access plan for query

Open -- open a cursor for retrieving query results

Fetch -- retrieve a row of query results

Close -- close the cursor

Prepare -- prepare SQL statements for dynamic execution

Execute -- dynamically Execute SQL statements

Describe -- describe the prepared Query

--- Local variables

Declare @ ID char (10)

-- Set @ ID = '20140901'

Select @ ID = '000000'

--- Global variables

--- It must start

-- If else

Declare @ x int @ Y int @ Z int

Select @ x = 1 @ Y = 2 @ z = 3

If @ x> @ Y

Print 'x> y' -- print the 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 -- print the value of variable X

While @ Y <3

Begin

Select @ C = 100 * @ x + @ Y

Print @ C -- print the value of variable C

Select @ Y = @ Y + 1

End

Select @ x = @ x + 1

Select @ Y = 1

End

-- Waitfor

-- For example, the SELECT statement is executed after 1 hour, 2 minutes, and 3 seconds.

Waitfor delay '01: 02: 03'

Select * from employee

-- For example, the SELECT statement will not be executed until PM.

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] %' --------- ([] specifies the range of values)

Stockname like '[^ F-M] %' --------- (^ exclude specified range)

--------- Only wildcard characters can be used in the WHERE clause that uses 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 column number

Stockname = (select stockname from stock_information where stockid = 4)

--------- Subquery

--------- Unless the inner select clause can only return the value of one row,

--------- Otherwise, an in qualifier should be used in the outer WHERE clause.

Select distinct column_name form table_name --------- distinct specifies the unique column value to be retrieved, not repeated

Select stocknumber, "stocknumber + 10" = stocknumber + 10 from table_name

Select stockname, "stocknumber" = count (*) from table_name group by stockname

--------- Group by groups the table by row. The specified column has the same value.

Having count (*) = 2 --------- having: select the specified group.

Select *

From Table1, Table2

Where table1.id * = table2.id -------- left external connection, which exists in Table1 but not in Table2.

Table1.id = * table2.id -------- right external connection

Select stockname from Table1

Union [all] ----- Union merges query result sets, all-retained 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 maintain table integrity

Drop table table_name --------------- delete a table completely

* ** Alter table ** --- modify the database table structure

Alter table database. Owner. table_name add column_name char (2) null .....

Sp_help table_name ---- display existing features of a table

Create Table table_name (name char (20), age smallint, lname varchar (30 ))

Insert into table_name select ...... ----- to delete a column (create a new table)

Alter table table_name drop constraint stockname_default ---- Delete the default constraint of stockname

* ** Function (/* common functions */)***

---- Statistical functions ----

AVG -- average value

Count -- count

Max -- calculate the maximum value

Min -- Minimum value

Sum -- sum

-- AVG

Use pangu

Select AVG (e_wage) as dept_avgwage

From employee

Group by dept_id

-- Max

-- Name of the employee with the highest salary

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 the expression.

-- Stdevp ()

-- The stdevp () function returns the population standard deviation.

-- VAR ()

-- The VaR () function returns the statistical variation of all values in the expression.

-- Varp ()

-- Varp () function returns the total number of variations

---- Arithmetic functions ----

/*** Trigonometric function ***/

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 function ***/

Asin (float_expression) -- returns the angle in radians where the sine is the float value.

ACOs (float_expression) -- returns the angle in radians where the cosine is the float value.

Atan (float_expression) -- returns the angle in radians of the float value.

Atan2 (float_expression1, float_expression2)

-- Returns the radians of float_expression1/float_expres-sion2.

Degrees (numeric_expression)

-- Converts radians to degrees and returns data of the same type as the expressions.

-- Integer/money/real/Float Type

Radians (numeric_expression) -- converts degrees to radians and returns the same data type as the expression.

-- Integer/money/real/Float Type

Exp (float_expression) -- returns the exponent value of the expression.

Log (float_expression) -- returns the natural logarithm of the expression.

Log10 (float_expression) -- returns the base-10 logarithm of the expression.

SQRT (float_expression) -- returns the square root of the expression

/***** Approximate function ***/

Ceiling (numeric_expression) -- Return> = the minimum integer of the expression. The returned data type is the same as that of the expression.

-- Integer/money/real/Float Type

Floor (numeric_expression) -- returns the minimum integer of the <= expression. The returned data type is the same as that of the expression.

-- Integer/money/real/Float Type

Round (numeric_expression) -- returns the data returned from a rounding value with integer_expression as the precision.

-- The same type and expression can be integer/money/real/Float Type

ABS (numeric_expression) -- returns the absolute value of the expression. The returned data type is the same as that of the expression.

-- Integer/money/real/Float Type

Sign (numeric_expression) -- the positive and negative values of the test parameter return the data type returned by 0, zero, 1, positive, or-1 negative.

-- Same as the expression, it can be of the integer, money, real, or float type.

Pi () -- the return value is π, that is, 3.1415926535897936

Rand ([integer_expression]) -- use the optional [integer_expression] As the seed value to obtain a random floating point number between 0 and 1.

---- String function ----

ASCII () -- returns the ASCII value of the leftmost character of a character expression.

Char () -- function used to convert ASCII code to character

-- If no value is entered ~ The Char function returns a null value for the ASCII value between 255.

Lower () -- function converts all strings to lowercase letters

Upper () -- function converts all strings to uppercase

STR () -- function converts numeric data to numeric data

Ltrim () -- function removes spaces in the string Header

Rtrim () -- function removes spaces at the end of the string

Left (), right (), substring () -- The function returns some strings.

Charindex (), patindex () -- The function returns the starting position of a specified substring in the string.

Soundex () -- The function returns a four-digit escape code.

-- The soundex function can be used to find strings with similar sound. However, the soundex function returns only 0 values for numbers and Chinese characters.

Difference () -- the difference between the two character expressions returned by the soundex Function

-- 0 the first character returned by the two soundex functions is different

-- 1 the first character returned by the two soundex functions is the same

-- 2 two soundex functions return the same first and second characters.

-- 3 two soundex functions return the same first, second, and third characters.

-- 4 two soundex functions return identical values

Quotename () -- The function returns a string enclosed by a specific character.

/* Select quotename ('abc', '{') quotename ('abc ')

The running result is as follows:

----------------------------------{

{ABC} [ABC] */

Replicate () -- The function returns a string that repeats character_expression for a specified number of times.

/* Select replicate ('abc', 3) replicate ('abc',-2)

The running result is as follows:

----------------------

Abcabcabc null */

Reverse () -- The function reverses the character arrangement order of the specified string

Replace () -- returns the string with the specified substring replaced by the function.

/* Select Replace ('abc123g ', '123', 'def ')

The running result is as follows:

----------------------

Abcdefg */

Space () -- The function returns a blank string with a specified length.

Stuff () -- The function replaces a substring of the specified position length with another substring.

---- Data type conversion function ----

The syntax of the cast () function 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 (12), getdate ())

The running result is as follows:

------------------------------------------

199 Jan 15 2000

---- Date function ----

Day () -- The function returns the date value in date_expression.

Month () -- The function returns the month value in date_expression.

Year () -- returns the Year Value in date_expression.

Dateadd (<datepart>, <number>, <date>)

-- The function returns the new date generated by the specified date plus the specified additional date interval number.

Datediff (<datepart >,< number >,< date>)

-- The function returns the datepart differences between two specified dates.

Datename (<datepart>, <date>) -- The function returns the specified part of the date in the form of a string.

Datepart (<datepart >,< date>) -- The function returns the specified part of the date in the form of an integer.

Getdate () -- The function returns the current date and time of the system in the default format of datetime.

---- System functions ----

App_name () -- function returns the name of the currently executed application

Coalesce () -- function returns the value of the first non-null expression among multiple expressions.

Col_length (<'table _ name' >,< 'column _ name'>) -- returns the length value of the specified field in the table.

Col_name (<table_id >,< column_id>) -- the name of the specified field in the function return table is the column name.

Datalength () -- the actual length of the data returned by the function

Db_id (['database _ name']) -- number of the database returned by the function

Db_name (database_id) -- Name of the database returned by the function

Host_id () -- The function returns the name of the server computer.

Host_name () -- returns the name of the server computer.

Identity (<data_type> [, seed increment]) [as column_name])

-- The identity () function is used only in the select into statement to insert an identity column to the new table.

/* Select Identity (INT, 1, 1) as column_name

Into newtable

From oldtable */

Isdate () -- determines whether the given expression is a reasonable date.

Isnull (<check_expression >,< replacement_value>) -- The function replaces the null value in the expression with the specified value.

Isnumeric () -- The function determines whether the given expression is a reasonable value.

Newid () -- The function returns a value of the uniqueidentifier type.

Nullif (<expression1>, <expression2>)

-- The nullif function returns the value of expression1 if expression1 is equal to expression2. If the value is not equal, the return value is expression1.