SQL Statement Encyclopedia

SQL Statement Encyclopedia

--Statement function
--Data manipulation
SELECT--Retrieve data rows and columns from a database table
INSERT--Adds a new row of data to the database table
Delete--delete rows of data from a database table
Update-Updates data in a database table
--Data definition
CREATE table--Creating a database table
DROP table--delete tables from the database
ALTER TABLE--Modify the database table structure
Create View-Creates a view
Drop View-Delete a view from the database
Create index--creates an index for a database table
Drop INDEX--Remove the indexes from the database
Create PROCEDURE--Creating a stored procedure
Drop PROCEDURE--delete a stored procedure from the database
Create TRIGGER--creating a trigger
Drop TRIGGER--delete a trigger from the database
CREATE Schema--Add a new schema to the database
Drop schema--delete a schema from the database
Create domain--creating a data domain
Alter domain--change field definition
Drop domain--delete a field from the database
--Data control
Grant--Granting user access
Deny--Deny user access
REVOKE--Remove user access rights
--Transaction control
COMMIT--End the current transaction
ROLLBACK--Abort the current transaction
SET TRANSACTION--Define the current transactional data access characteristics
--Programmatic SQL
DECLARE--Setting cursors for queries
Explan--describing data access plans for queries
Open--Retrieving query results opens a cursor
FETCH--Retrieves a row of query results
Close--Closes the cursor
PREPARE-Preparing SQL statements for dynamic execution
Execute--Execute SQL statements Dynamically
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_ The year value in Expression
DATEADD (<datepart>,<number>,<date>)  
--function returns date specified Plus the specified extra date interval number produces a new date
DATEDIFF (<datepart>,<number>,<date>)
--function returns two specified date in datepart Aspects of the Difference
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

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

Reserved words in SQL

Action Add aggregate All
Alter after and AS
ASC AVG Avg_row_length Auto_increment
Between bigint bit binary
BLOB bool both by
Cascade Case Char Character
Change Check checksum column
Columns Comment Constraint create
Cross Current_date Current_time Current_timestamp
Data database Databases Date
DateTime Day Day_hour Day_minute
Day_second DayOfMonth DayOfWeek DayOfYear
Dec decimal Default delayed
Delay_key_write Delete desc describe
Distinct Distinctrow Double drop
End Else Escape escaped
Enclosed enum explain exists
Fields File First float
FLOAT4 FLOAT8 Flush Foreign
From-for-full function
Global Grant Grants Group
Having heap high_priority hour
Hour_minute Hour_second hosts identified
Ignore in index infile
Inner Insert insert_id int
Integer interval int1 int2
Int3 int4 int8 into
If is ISAM join
Key keys Kill last_insert_id
Leading left length like
Lines Limit load Local
Lock logs Long Longblob
Longtext low_priority Max Max_Rows
Match Mediumblob Mediumtext Mediumint
Middleint min_rows minute Minute_second
Modify Month MonthName MyISAM
Natural numeric No Not
Null on Optimize option
Optionally or order outer
outfile Pack_keys Partial password
Precision Primary Procedure Process
Processlist Privileges Read Real
References Reload RegExp Rename
Replace restrict returns revoke
Rlike Row Rows Second
Select Set Show shutdown
smallint Soname Sql_big_tables sql_big_selects
Sql_low_priority_updates Sql_log_off sql_log_update Sql_select_limit
Sql_small_result Sql_big_result sql_warnings Straight_join
Starting Status String table
Tables temporary terminated text
Then time timestamp Tinyblob
Tinytext tinyint trailing to
Type use using unique
Unlock unsigned update usage
Values varchar variables varying
varbinary with Write when
Where year Year_month Zerofill

SQL statement Daquan