Transact_ SQL small manual (large set of various SQL statements)

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

-- 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

-- Programmatic SQL

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.