Large collection of various SQL statements

Source: Internet
Author: User
Tags natural logarithm create domain

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.