Full Set of SQL Server statements)

 

-- Sentence skill

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

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

3. Data Control
Grant -- Grant the user access permission
Deny-Deny User Access
Revoke -- revoke User Access Permissions

4. Transaction Control
Commit -- end the current transaction
Rollback -- abort the current transaction
SET transaction -- define the data access features of the current transaction

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

6. Local Variables
Declare @ ID char (10)
-- Set @ ID = '20140901'
Select @ ID = '000000'

7. Global Variables
--- It must start @

8. If statement
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'

9. Case statement
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

10. While continue break statement
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

11. waitfor statement
-- 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

 

12. SELECT statement

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

13. insert statements

Insert into table_name (stock_name, stock_number) value ("XXX", "XXXX ")
Value (select stockname, stocknumber from stock_table2) --- value is a SELECT statement

14. Update statement

Update table_name set stockname = "XXX" [where stockid = 3]
Stockname = default
Stockname = NULL
Stocknumber = stockname + 4

15. Delete statement

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

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

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

18. String functions
ASCII () -- returns the ASCII value of the leftmost character in the character expression.
char () -- function is used to convert an ascii code to a character
-- if 0 ~ is not input ~ The Char function returns a null value between 255 and.
lower () -- The function converts all strings to lowercase.
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 () -- function returns some strings
charindex (), patindex () -- 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 search for sound-like strings. However, the soundex function returns only 0 values for numbers and Chinese characters.
difference () -- The function returns the difference between the two character expressions returned by the soundex function
-- 0 the first character of the two soundex functions return different values
-- 1 the first one of the two soundex function return values same character
-- 2 the first and second characters of the return values of two soundex functions are the same
-- 3 the first and second characters of the return values of two soundex functions are the same
-- 4 the return values of two soundex Functions identical

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.

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

20. Date functions ----
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.

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

22. mathematical functions

1. Absolute Value
S: Select ABS (-1) Value
O: Select ABS (-1) value from dual

2. INTEGER (large)
S: Select ceiling (-1.001) Value
O: Select Ceil (-1.001) value from dual

3. Round (small)
S: Select floor (-1.001) Value
O: Select floor (-1.001) value from dual

4. Round (truncation)
S: Select cast (-1.002 as INT) Value
O: Select trunc (-1.002) value from dual

5. Rounding
S: Select round (1.23456, 4) value 1.23460
O: Select round (1.23456, 4) value from dual 1.2346

6. e is the base power
S: Select exp (1) Value 2.7182818284590451
O: Select exp (1) value from dual 2.71828182

7. Take the base logarithm of E.
S: Select log (1, 2.7182818284590451) value 1
O: Select Ln (2.7182818284590451) value from dual; 1

8. Use 10 as the base logarithm.
S: Select log10 (10) value 1
O: Select log (10, 10) value from dual; 1

9. Square
S: Select square (4) value 16
O: Select power (4, 2) value from dual 16

10. Take the square root
S: Select SQRT (4) value 2
O: Select SQRT (4) value from dual 2

11. Evaluate the base power of any number
S: Select power (3, 4) value 81
O: Select power (3, 4) value from dual 81

12. Random Number acquisition
S: Select rand () Value
O: select Sys. dbms_random.value (0, 1) value from dual;

13. Get the symbol
S: Select sign (-8) Value-1
O: Select sign (-8) value from dual-1
---------- Mathematical functions

14. Circumference Rate
S: Select Pi () value 3.1415926535897931
O: Unknown

15. Sin, cos, and Tan parameters are in radians.
For example, select sin (PI ()/2) value to get 1 (sqlserver)

16. asin, ACOs, atan, atan2 return radians

17. radian angle Interchange (, Oracle unknown)
Degrees: Radian-> Angle
Radians: Angle-> radian

--------- Comparison of Values

18. Calculate the maximum value of the Set
S: Select max (value) value from
(Select 1 Value
Union
Select-2 value
Union
Select 4 value
Union
Select 3 value)

O: Select greatest (1,-2, 4, 3) value from dual

19. Minimum value of the Set
S: select Min (value) value from
(Select 1 Value
Union
Select-2 value
Union
Select 4 value
Union
Select 3 value)

O: Select least (1,-2, 4, 3) value from dual

20. How to Handle null values (replace null with 10 in F2)
S: Select F1, isnull (F2, 10) value from TBL
O: Select F1, nvl (F2, 10) value from TBL

-------- Comparison of Values

21. Search for the character serial number
S: Select ASCII ('A') Value
O: Select ASCII ('A') value from dual

22. calculate characters from the serial number
S: Select char (97) Value
O: Select CHR (97) value from dual

23. Connection
S: Select '11' + '22' + '33' Value
O: Select Concat ('11', '22') | 33 value from dual

23. substring position -- return 3
S: Select charindex ('s ', 'sdsq', 2) Value
O: Select instr ('sdsq', 's', 2) value from dual

23. Position of the fuzzy substring -- 2 is returned. If % is removed from the parameter, 7 is returned.
S: Select patindex ('% d % Q %', 'sdsfasdqe ') Value
O: Oracle didn't find it, but instr can throw a glance at the issue via the fourth haze? BR> select instr ('sdsfasdqe ', 'sd', 1, 2) value from dual returns 6

24. Seek substrings
S: Select substring ('abcd', 2, 2) Value
O: Select substr ('abc', 2, 2) value from dual

25. Return aijklmnef as a substring
S: select stuff ('abcdef', 2, 3, 'ijklmn ') Value
O: Select Replace ('abcdef', 'bcd', 'ijklmn ') value from dual

26. replace all substrings
S: No
O: Select translate ('fasdbfasegas', 'fa ', 'my') value from dual

27. Length
S: Len, datalength
O: Length

28. case-insensitive lower, upper

29. uppercase letters
S: No
O: Select initcap ('abcd DSAF df') value from dual

30. Left fill space (the first parameter of lpad is space, which is the same as the space function)
S: Select space (10) + 'abc' Value
O: Select lpad ('abc', 14) value from dual

31. Right fill space (the first parameter of rpad is space, which is the same as the space function)
S: Select 'abc' + space (10) Value
O: Select rpad ('abcd', 14) value from dual

32. Delete Spaces
S: ltrim, rtrim
O: ltrim, rtrim, trim

33. Duplicate string
S: Select replicate ('abcd', 2) Value
O: No

34. Comparison of pronunciation similarity (the two words return the same value and have the same pronunciation)
S: Select soundex ('Smith '), soundex ('smy ')
O: Select soundex ('Smith '), soundex ('smy') from dual
Comparison of soundex with select difference ('smithers', 'smythers ') in sqlserver
Returns 0-4, 4 for homophone, 1 for maximum

23. Date Functions

35. system time
S: Select getdate () Value
O: Select sysdate value from dual

36. days before and after
Directly add or subtract from an integer

37. Calculate the date
S: Select convert (char (10), getdate (), 20) Value
O: Select trunc (sysdate) value from dual
Select to_char (sysdate, 'yyyy-mm-dd') value from dual

38. Time
S: Select convert (char (8), getdate (), 108) Value
O: Select to_char (sysdate, 'hh24: mm: ss') value from dual

39. Take other parts of date and time
S: datepart and datename functions (determined by the first parameter)
O: The second parameter of the to_char function is determined.

Parameter --------------------------------- The following table needs to be supplemented.
Year YY, yyyy
Quarter QQ, Q (quarter)
Month mm, M (m o is invalid)
Dayofyear dy, y (O table week)
Day DD, D (d o is invalid)
Week wk, ww (WK o is invalid)
Weekday DW (unclear O)
Hour HH, hh12, hh24 (hh12, hh24 S is invalid)
Minute Mi, n (n o is invalid)
Second SS, S (s o is invalid)
Millisecond MS (invalid O)
----------------------------------------------

40. Last day of the month
S: Unknown
O: Select last_day (sysdate) value from dual

41. A day of the week (such as Sunday)
S: Unknown
O: Select next_day (sysdate, 7) vaule from dual;

42. String Conversion time
S: Can be directly converted to or select cast ('2017-09-08 'As datetime) Value
O: Select to_date ('2017-01-05 22:09:38 ', 'yyyy-mm-dd hh24-mi-ss') vaule from dual;

43. Calculate the difference of a part of the two dates (for example, seconds)
S: Select datediff (SS, getdate (), getdate () + 12.3) Value
O: Subtract directly with two dates (for example, d1-d2 = 12.3)
Select (d1-d2) * 24*60*60 vaule from dual;

44. Calculate a new date (for example, minute) based on the difference value)
S: Select dateadd (MI, 8, getdate () Value
O: Select sysdate + 8/60/24 vaule from dual;

45. Time in different time zones
S: Unknown
O: Select new_time (sysdate, 'ydt ', 'gmt') vaule from dual;

----- Time Zone parameter. Beijing must be in the east 8 zone of ydt -------
Ast ADT Standard Atlantic time
Bst bdt Standard Time
Standard Time for central CST CDT
Est EDT standard Eastern Time
GMT Standard Time
Hst hdt Alaska-Hawaii Standard Time
Standard Time for mst mdt Mountainous Areas
NST standard time for new Finland
Pst PDT Pacific Standard Time
Yst ydt Yukon Standard Time