Transact-SQL quick Check Manual

Basic knowledge of SQL
1. Database query: Select usage
Select [Top (value)] Field list from data table [Where condition] [order BY field] [ASC or DESC]

2. Add data: Insert into usage
Insert into Data table (field 1, Field 2, Field 3,...) Values (the value of the field 1, the value of the field 2, the value of the field 3,...)

3. Delete data: Delete usage
Delete from data table [Where condition]

4. Update data:update usage
Update data table Set Field 1 = field value 1, field 2 = field value 2, ... [Where condition]

5. Data table: CREATE TABLE usage
Create Table datasheet name (field 1 name, field 1 type, field 2 Name field 2 Type, ...)

Access data tables Common data types: Text,char (number), Memo,number,int,date/time,logical,oleobject

AutoNumber field Add Example: Create TABLE AAA (ID int identity (1, 1) not null,abc varchar null)

6. Change datasheet: Alter table usage
Add Field: Alter table data table name add Column field name segment type

Delete field: Alter table data table name Drop Column field name

7. Delete data table: Drop table usage

Drop Table Data table name

transact_sql********************

ALTER TABLE [TABLE] alter [ID] Counter Constraint [table _p] Primary key changes the ID column to an AutoNumber type and sets the primary key

--Statement functionality
--Data manipulation
SELECT-retrieving data rows and columns from database tables
INSERT-Adding new data rows to database tables
Delete-deleting data rows from database tables
UPDATE - Update data in a database table
--Data definition
CREATE table--Create a database table
drop table-Delete a table from a database
ALTER TABLE--Modify database table structure
Create VIEW-- Create a View
Drop view-Remove views from database
CREATE INDEX--creates an index for a database table
Drop INDEX-delete index from database
Create PROCEDURE-Creates a stored procedure
Drop PROCEDURE--removing stored procedures from the database
Create TRIGGER-Creating a trigger
Drop TRIGGER Removing triggers 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 range
alter domain-changing domain definition
DROP domain To delete a domain from the database
--Data Control
Grant--Grant user access
Deny--Deny user access
REVOKE--Unlock user access
--transaction control
COMMIT--End current transaction
ROLLBACK--Abort the current transaction
SET TRANSACTION--Define the current transaction data access characteristics
--Programmatic SQL
DECLARE--Set cursors for queries
Explan--Describe data access plans for queries
Open--Retrieving query results opening a cursor
FETCH-Retrieving a row of query results
Close--closing cursors
PREPARE-Preparing SQL statements for dynamic execution
Execute -Dynamically executing SQL Statement
DESCRIBE--describing a prepared query

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

---global variables
---must begin with @@

--if ELSE
DECLARE @x int @y int @z int
Select @x = 1 @y = 2 @z=3
If @x > @y
print ' x > y '--printing 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--printing the value of variable C
Select @y = @y + 1
End
Select @x = @x + 1
Select @y = 1
End

--waitfor
--An example waits 1 hours 2 minutes 3 seconds before executing the SELECT statement
WAITFOR DELAY ' 01:02:03 '
SELECT * FROM Employee
--for example, wait until 11 o'clock at 8 minutes before executing the SELECT statement
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 range of values)
StockName like ' [^f-m]% '---------(^ exclusion specified range)
---------only use wildcard characters in WHERE clauses that use the LIKE keyword)
or Stockpath = ' Stock_path '
or Stocknumber < 1000
and Stockindex = 24
Not stocksex = ' mans '
Stocknumber between 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 you can ensure that the inner select returns only the values of one row,
---------Otherwise, you should use an in qualifier 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 +" = Stocknumber + from table_name
Select StockName, "stocknumber" = count (*) from table_name GROUP BY StockName
---------GROUP by groups the table by row, specifying that the column has the same value
Having count (*) = 2---------have the specified group selected

SELECT *
From Table1, table2
where Table1.id *= table2.id--------A left outer join, some in the Table1 and no null representation in Table2
Table1.id =* table2.id--------Right outer connection

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

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

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

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

----Statistical function----
AVG--averaging
Count--Number of statistics
Max--Find the maximum 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 highest-paid employee name
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 an expression

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

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

--varp ()
--varp () function returns the total variance number

----Arithmetic functions----

/*** Trigonometric ***/
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 Functions ***/
ASIN (float_expression)--Returns the angle that the sine is the float value in radians
ACOS (float_expression)--Returns the angle in radians that the cosine is the float value
Atan (float_expression)--Returns the angle in radians that the tangent is a float value
ATAN2 (Float_expression1,float_expression2)
--Returns the 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 can be
--integer/money/real/float type
RADIANS (numeric_expression)--Converts the angle to radians returns the same data type as the expression can be
--integer/money/real/float type
EXP (float_expression)--Returns the exponential value of an expression
LOG (float_expression)--Returns the natural value of an expression
LOG10 (float_expression)--Returns the 10-based pair value of an expression
SQRT (float_expression)--Returns the square root of an expression
/*** Approximate value function ***/
CEILING (numeric_expression)--the smallest integer that returns a >= expression returns the same data type as an expression
--integer/money/real/float type
FLOOR (numeric_expression)--the smallest integer that returns a <= expression returns the same data type as an expression
--integer/money/real/float type
ROUND (numeric_expression)--Returns the data returned with a rounded value of integer_expression precision
--the type is the same as an expression and can be integer/money/real/float type
ABS (numeric_expression)--Returns the absolute value of an expression that returns the same type of data as an expression
--integer/money/real/float type
SIGN (numeric_expression)--The positive sign of the test parameter returns the data type returned by a 00-value 1 positive or-1 negative number
--the same as an expression can be integer/money/real/float type
Pi ()--The return value is pi that is 3.1415926535897936
RAND ([integer_expression])--seed with an optional [integer_expression] is worth 0-1 random floating-point numbers

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

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

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

Space ()--the function returns a blank string with a specified length
STUFF ()--the function replaces a substring with another substring at the specified position length

----data type conversion function----
The CAST () function syntax is as follows
CAST () ( as [length])
The CONVERT () function syntax is as follows
CONVERT () ( [length], [, Style])

Select CAST (100+99 as char) convert (varchar (), GETDATE ())
The results of the operation are as follows
------------------------------ ------------
199 15 2000.

----Date function----
Day ()--function returns the date value in Date_expression
MONTH ()--function returns the month value in Date_expression
Year ()--function returns the value of years in Date_expression
DATEADD ( , , )
--function returns a date with the specified date plus the specified extra date interval number
DATEDIFF ( , , )
--function returns the difference in datepart between two specified dates
Datename ( , )--function returns the specified part of a date as a string
DATEPART ( , )--function returns the specified part of a date as an integer value
GETDATE ()--function returns the current date and time of the system in datetime default format

----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 many expressions
Col_length (< ' table_name ', < ' column_name ' >)--function returns the length value of the specified field in the table
Col_name (

)--function returns the name of the specified field in the table, which is the column name
Datalength ()--function returns the actual length of data in a 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 ( [, Seed increment]) [as column_name])
The--identity () function is used to insert an IDENTITY column column into a new table only in the SELECT INTO statement
/*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 ( , )--the function replaces the null value in an expression with a specified value
IsNumeric ()--function to determine whether the given expression is a reasonable value
NEWID ()--function returns a value of uniqueidentifier type
Nullif ( , )
The--nullif function returns the value of a expression1 if the value is not equal when the expression1 is equal to Expression2

,