****************** TRANSACT_SQL *****************************
- Sentence function
- Data operation
SELECT - Retrieves data lines and columns from the database table
INSERT - Add new data to the database table
Delete - Remove Data Row from Database Table
Update - Update the data in the database table
- Data definition
CREATE TABLE - Create a database table
DROP TABLE - Remove the table from the database
Alter Table - Modify Database Table Structure
CREATE VIEW - Create a view
DROP View - Remove View from the Database
CREATE INDEX - Create an index for database tables
Drop Index - Remove Index from the Database
CREATE Procedure - Create a stored procedure
Drop Procedure - Remove stored procedures from the database
CREATE TRIGGER - Create a trigger
DROP TRIGGER - Remove the trigger from the database
Create Schema - Add a new mode to the database
DROP Schema - Remove a mode from the database
Create Domain - Create a data value domain
Alter Domain - Change the domain definition
DROP DOMAIN - Remove a domain from the database
- Data Control
Grant - Grant user access
Deny - Reject user access
Revoke - Release User Access Permissions
- transaction control
Commit - End current transaction
Rollback - abort current transaction
SET Transaction - Defines the current transaction data access feature
- Documentation SQL
Declare - Set the cursor for the query
Explan - Describe the data access plan for the query
Open - Retrieval Query Open a cursor
FETCH - Retrieve a line of query results
Close - Close Cursor
Prepare - Prepare SQL statements for dynamic
Execute - Dynamically perform SQL statements
Describe - Description ready for query
--- Local variable
Declare @id char (10)
--set @ID =? '
SELECT @ID =? '
--- Global variable
--- 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' - Print Strings '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' TEN E_WAGE * 1.08
When Job_level = '2' TEN 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 - Value of the variable x
While @Y <3borgin
SELECT @c = 100 * @ x @Y
Print @c - value of printing variable C
SELECT @Y = @Y 1
end
SELECT @X = @X 1
SELECT @Y = 1
end
--Waitfor
- Example Waiting for 1 hour 2 minutes and 3 seconds to execute the SELECT statement
Waitfor delay '01: 02: 03 '
SELECT * from Employee
- Example waiting until 11:00 pm 8:00 on the SELECT statement
WAITFOR TIME '23: 08: 00 '
SELECT * from Employee
*** SELECT ***
SELECT * (column name) from 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]%' --------- ([] Specified value range)
Stockname Like '[^ f-M]%' --------- (^ excluded specified range)
--------- You can only use wildcards in WHERE clauses using the LIKE key)
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 list
Stockname = (Select stockname from stock_information where stockid = 4)
--------- Subproof
--------- Unless you can make sure that the inner SELECT returns only a row of values,
--------- Otherwise, in the outer WHERE clause, one IN limit
Select Distinct Column_name form Table_name --------- DistINCT specifies that you have retrieved alone values, do not repeat
Select stocknumber, "stocknumber 10" = stocknumber 10 from table_name
Select stockname, "stocknumber" = count (*) from table_name group by stockname
------- Group by Packets the table, and has the same value in the specified column.
Having count (*) = 2 --------- Having Selected Group
SELECT *
From Table1, Table2
WHERE TABLE1.ID * = Table2.ID ------- The left external connection, there is no NULL in Table2 in Table1.
Table1.id = * Table2.ID -------- Right external connection
Select stockname from table1Union [all] ----- UNION merger query result set, all-reserved repetition line
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 ----------- Remove all rows in the table, still maintain the integrity of the table
Drop Table Table_name --------------- completely deleted table
*** alter Table *** --- Modify the database table structure
Alter Table Database.OWNER.TABLE_NAME Add Column_name char (2) NULL .....
Sp_help Table_name ---- Display Table Existing
Create Table Table_name (Name Char (20), Age Smallint, Lname Varchar (30))
INSERT INTO TABLE_NAME SELECT ......... --- Realize the method of deleting columns (create a new table)
ALTER TABLE TABLE_NAME DROP CONSTRAINT Stockname_Default ---- Delete Stockname Default Constraint
*** Function (/ * Common function * /) ***
---- Statistical function ----
AVG - seek average
Count - Statistics
MAX - seek maximum
Min - seeking minimum
Sum - seeking
--Avg
Use Pangu
SELECT AVG (E_WAGE) AS DePt_AVGWAGE
From Employee
GROUP BY DEPT_ID
--Max
- Solving the highest number of employees
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 ()
--Stdevp () function returns the overall standard difference
--VAR ()
--VAR () function returns a statistical variation of all values in the expression
--VARP ()
--VARP () function returns the number of overall variations
---- Arithmetic function ----
/ *** Triangle function *** /
SIN (FLOAT_EXPRESSION) - Returns sinusoidal in the angle represented by radians
COS (FLOAT_EXPRESSION) - Returns the cosine in the angle represented by radians
Tan (float_expression) - Returns the orthose of the angle represented by the arc
COT (FLOAT_EXPRESSION) - Returns the remainder of the arc indication
/ *** Anti-triangle function *** /
Asin (FLOAT_EXPRESSION) - Returns the sinusoidal angle indicated by the float value
ACOT_EXPRESSION - Returns the cosine is the angle represented by the Float value
Atan (float_expression) - Returns the angle ATAN2 represented by the float value indicated by the arc value (float_expression1, float_expression2)
- Returns orthodied is the angle indicated by float_expression1 / float_expres-sion2
Degrees (numeric_expression)
- Transition of radians to an angle return to the same data type as the expression can be
--Integer / Money / REAL / FLOAT type
Radians (numeric_expression) - Convert angle to radians and return to express the same data type as
--Integer / Money / REAL / FLOAT type
Exp (Float_expression) - Returns the exponential value of the expression
Log (float_expression) - Returns the natural logar of expression
LOG10 (FLOAT_EXPRESSION) - Returns the alignment of 10 for 10
SQRT (float_expression) - Returns the square root of the expression
/ *** Near similar value function *** /
CEILING (Numeric_Expression) - Return> = The minimum integer returned by the expression is the same as the expression as the expression.
--Integer / Money / REAL / FLOAT type
FLOOR (Numeric_Expression) - Returns the data type returned by <= minimum integer returns to the same as the expression
--Integer / Money / REAL / FLOAT type
Round (numeric_expression) - Returns the data returned by the four household value of integer_expression as accuracy
- Types can be used for Integer / Money / REAL / FLOAT type
ABS (Numeric_Expression) - The data type returned by the back expression is the same as the expression as the expression.
--Integer / Money / REAL / FLOAT type
Sign (numeric_expression) - The positive and negative number of the test parameter returns 0 zero value 1 positive or -1 negative data type returned
- Types with expressions can be used for Integer / Money / REAL / FLOAT
Pi () - return value π 3.1415926535897936
RAND ([Integer_Expression]) - Use optional [integer_expression] to make seeds worth 0-1, random floating point
---- String Function ----
ASCII () - Function Returns the ASCII code value of the left-end character character expression
CHAR () - function is used to convert ASCII code to characters
- If the ASCII code value of the 0 ~ 255 is not entered between 0 ~ 255, a NULL value will be returned.
Lower () - Function Converts the string to lowercase
Upper () - function transforms all strings to uppercase
STR () - function converts numeric data into character data
Ltrim () - function removes the space of the character string head
RTRIM () - function removes the space of the string tail
Left (), right (), substring () - function returns some string
Charindex (), Patindex () - Function Returns the start position of a specified substrunch in a string
Soundex () - Function Returns a four-bit character code
- Soundex function can be used to find sound similar strings, but the SoundEx function returns 0 values for numbers and Chinese characters.
Difference () - Function Returns the difference in the value of the two character expressions returned by the SoundEx function
--0 The first character of the two SoundEx functions returned
--1 Two SoundEx functions The first character of the return value is the same
--2 Two SoundEx functions return value for the first two characters
--3 Two SoundEx functions return values for the same two or three characters
--4 Two SoundEx functions return values identical
Quotename () - Function returns a string that is enclosed by a particular character
/ * SELECT Quotename ('ABC', '{') Quotename ('ABC') running results
---------------------------------- {
{abc} [ABC] * /
Replicate () - Function Returns a string of repeating character_expression specified
/ * SELECT Replicate ('ABC', 3) Replicate ('ABC', -2)
The result of the operation is as follows
----------- -----------
Abcabcabc null * /
Reverse () - Function The character arrangement of the specified string is reversed
Replace () - Function Return String of the specified substring
/ * SELECT Replace ('ABC123G', hurd ',' DEF ')
The result of the operation is as follows
----------- -----------
Abcdefg * /
Space () - function returns a blank string with a specified length
The stuff () - function replaces the string of the position length with another substring string
---- Data Type Conversion Function ----
Cast () function syntax is as follows
CAST () (
Convert () function syntax is as follows
Convert () (
SELECT CAST (100 99 As Char) Convert (VARCHAR (12), getDate ())
The result of the operation is as follows
------------------------------------------
199 Jan 15 2000
---- Date function ----
Day () - Function Returns the date value in the DATE_EXPRESSION
Month () - Function Returns the month value in Date_Expression
Year () - function returns the year value in Date_Expression
Dateadd (
- Function Returns the New Date Generated by Number Generated by Specified Date Date plus Number
Datediff (
- Function Returns two specified dates from DATEPART
Datename (
DatePart (
GetDate () - Function Returns the current date and time of the system at the default format of DateTime
---- System Function ----
App_name () - Function Returns the name of the currently executed application
Coalesce () - Function Returns the value of the first non-NULL expression in many expressions
COL_LENGTH (<'Table_name'>, <'Column_name'>) - Function Returns the length value of the specified field in the table
COL_NAME (
DATALEENGTH () - function returns the actual length of data of data expressions
DB_ID (['Database_name'] - Function Returns the number of the database
DB_NAME (Database_ID) - Function Returns Name of Database
Host_ID () - Function Returns the name of the server computer computer
Host_name () - Function Returns the name Identity (
--IDENTITY () function is only used in the SELECT INTO statement to insert an Identity Column to list a new table
/ * SELECT IDENTITY (Int, 1, 1) as column_name
INTO NewTable
From oldtable * /
Isdate () - function judgment is a reasonable date for a given expression
Isnull (
ISNUMERIC () - Function determines if the given expression is a reasonable value
NewID () - function returns a NE value of a UniqueIdentifier type
Nullif (
- Nullif Function Returns the value of Expression1 when Expression1 is equal to Expression2 equal to EXPRESSION2.
