SQL Statement Collection

SQL Statement Collection

--statement function
--data Operation
SELECT--Retrieve data rows and columns from a database table
INSERT--Add new data rows to a database table
Delete--delete data rows from a database table
UPDATE-- Update data in a database table
--Data definition
CREATE table--Creates a database table
drop table--Deletes a table from the 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 a database table
Drop INDEX-delete index from database
Create PROCEDURE-Create a stored procedure
Drop PROCEDURE--delete a stored procedure from a database
Create TRIGGER--Creating a trigger
drop TRIGGER--deleting a trigger from the database
Create SCHEMA-- Add a new schema to the Database
drop schema-delete a schema from the database
Create domain--Creates a data domain
Alter domain--change field definition
DROP domains-- Remove a domain from the database
--Data Control
Grant--Grant user access
Deny--Deny user access
REVOKE--Unblock user access
--transaction control
COMMIT--End current transaction
ROLLBACK--Abort current transaction
Set TRANSACTION--Define current transactional data access characteristics
--Programmatic SQL
DECLARE--set cursor for query
Explan--Describe data access plan for query
Open--Retrieve query result opens a cursor
FETCH--Retrieves a row of query results
Close--close cursor
PREPARE--Prepare SQL statement for dynamic execution
Execute--Dynamically Execute SQL statements
DESCRIBE--Describe the prepared query

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

---global variables
---must start with @@

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

--waitfor
--Wait 1 hours, 2 minutes, 3 seconds before executing the SELECT statement
WAITFOR DELAY ' 01:02:03 '
SELECT * FROM Employee
--Example wait until 11 o'clock 8 minutes after the SELECT statement is executed
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 a range of values)
StockName like ' [^f-m]% '---------(^ exclude specified range)
---------can only use wildcard characters in the WHERE clause using the LIKE keyword)
or Stockpath = ' Stock_path '
or Stocknumber < 1000
and Stockindex = 24
Not stocksex = "man"
Stocknumber between 100
Stocknumber in (10,20,30)
ORDER BY Stockid DESC (ASC)---------Sort, desc-descending, asc-ascending
Order BY----------by column number
StockName = (select StockName from stock_information where Stockid = 4)
---------Sub-query
---------unless you can ensure that the inner select returns only one row of values,
---------Otherwise, an in qualifier should be used 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 + ten" = Stocknumber + from table_name
Select StockName, "stocknumber" = count (*) from table_name GROUP BY StockName
---------GROUP BY grouping tables by rows with the same values in the specified column
Have count (*) = 2---------Having the specified group selected

SELECT *
From Table1, table2
where Table1.id *= table2.id--------left outer join, Table1 in some and not NULL in table2
Table1.id =* table2.id--------Right external connection

Select StockName from table1
Union [ALL]-----Union merge query result set, all-preserves 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 maintain the integrity of the table
DROP TABLE table_name---------------completely delete tables

ALTER table***---Modify database table structure

ALTER TABLE Database.owner.table_name add column_name char (2) null ...
SP_HELP table_name----Show the table already features
CREATE TABLE table_name (name char), age smallint, lname varchar (30))
INSERT INTO table_name SELECT ...-----implement a method to delete a column (Create a new table)
ALTER TABLE table_name DROP CONSTRAINT stockname_default----Delete the default constraint for StockName

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

----Statistical functions----
AVG--averaging
Count--Number of statistics
Max--Max 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 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 ()
The--stdev () function returns the standard deviation of all data in an expression

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

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

--varp ()
The--varp () function returns the total number of variants

----Arithmetic function----

/*** Trigonometric ***/
Sin (float_expression)--Returns the sine of the angle in radians
COS (float_expression)--Returns the cosine of a corner in radians
TAN (float_expression)--Returns the tangent of a corner in radians
COT (float_expression)--Returns the cotangent of the angle in radians
/*** Inverse trigonometric Function ***/
ASIN (float_expression)--return sine is the angle in radians of the float value
ACOS (float_expression)--return cosine is the angle in radians of the float value
ATAN (float_expression)--return tangent is the angle in radians of a float value
ATAN2 (Float_expression1,float_expression2)
--return 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 to be
--integer/money/real/float type
RADIANS (numeric_expression)-Converts the angle to radians to return the same data type as the expression to
--integer/money/real/float type
EXP (float_expression)--Returns the exponential value of an expression
LOG (float_expression)--Returns the natural pair of values of an expression
LOG10 (float_expression)--Returns a 10-based pair of values for an expression
SQRT (float_expression)--Returns the square root of an expression
/*** approximation function ***/
CEILING (numeric_expression)--the smallest integer that returns the >= expression returns the same data type as the expression can be
--integer/money/real/float type
Floor (numeric_expression)--the smallest integer that returns the <= expression returns the same data type as the expression can be
--integer/money/real/float type
ROUND (numeric_expression)-Returns the data returned with a rounding value of integer_expression precision
--the same type as the expression can be integer/money/real/float type
ABS (numeric_expression)--the absolute value of the return expression returns the same data type as the expression can be
--integer/money/real/float type
Sign (numeric_expression)--the positive and negative number of the test parameter returns a 00 value of 1 positive or 1 negative data type returned
--the same as the expression can be integer/money/real/float type
Pi ()--The return value is π, which is 3.1415926535897936
RAND ([integer_expression])-a random floating-point number worth 0-1 with any chosen [integer_expression] Seed

----String function----
ASCII ()--function returns the ASCII value of the leftmost character of the character expression
CHAR ()--function is used to convert ASCII code to characters
--If you do not enter an ASCII value between 0 and 255, the CHAR function returns a null value
LOWER ()--function converts all strings to lowercase
UPPER ()--function converts all strings to uppercase
STR ()--function converts numeric data to character data
LTRIM ()--function to remove whitespace from the head of a string
RTRIM ()--function to remove whitespace from the trailing of a string
Left (), right (), SUBSTRING ()--function returns a partial string
CHARINDEX (), PATINDEX ()--function returns the beginning of the occurrence 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 kanji
Difference ()--function returns the difference of the value of the two-character expression returned by the SOUNDEX function
--2 Soundex The first character of the return value of a function is different
--12 Soundex function The first character of the return value is the same
--The 12th character of the return value of the 22 Soundex function is the same
--The 123th character of the return value of the 32 Soundex function is the same
--42 Soundex function return values are exactly the same

QUOTENAME ()--function returns a string enclosed by a specific character
/*select QuoteName (' abc ', ' {') QuoteName (' abc ')
The operation results 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 operation results are as follows
----------- -----------
ABCABCABC null*/

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

Space ()--function returns a blank string with a specified length
STUFF ()--function specifies a substring of the position length with another substring substitution string

----A 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 operation results are 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 ()--function returns Date_ Year value in Expression
DATEADD (<datepart>,<number>,<date>)
--function returns the specified date, plus the specified extra date interval number New Date generated
DATEDIFF (<datepart>,<number>,<date>)
--function returns the difference in datepart for two specified dates
Datename ( <datepart>, <date>)--function returns the specified part of the date as a string
datepart (<datepart>, <date>)-- function returns the specified part of the date as an integer value
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 executing application
COALESCE ()--function returns the value of the first non-null expression in a number of expressions
Col_length (< ' table_name ';, < ' column_name ' >)--function returns the length value of the specified field in the table
Col_name (<TABLE_ID>, <column_id>)--function returns the name of the specified field in the table is the column name
Datalength ()--function returns the actual length of data in the 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 only in a SELECT INTO statement to insert an IDENTITY column into a new table
/*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 (<CHECK_EXPRESSION>, <replacement_value>)--the function replaces the null value in an expression with the specified value
IsNumeric ()--function to determine whether the given expression is a reasonable value
NEWID ()--function returns a value of type uniqueidentifier
Nullif (<EXPRESSION1>, <expression2>)
The--nullif function returns the value of expression1 when the null value is not equal when expression1 is equal to Expression2

SQL Statement Collection