SQL Server Database Syntax encyclopedia _mssql

--Data manipulation
SELECT-Retrieving data rows and columns from database tables
INSERT-Adding new data rows to a database table
Delete-deleting data rows from a database table
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 Deletes a domain from the database

--Data control
Grant-Granting User access
Deny--Deny user access
REVOKE--Unlock user access rights
--Transaction control
COMMIT--End current transaction
ROLLBACK--Abort current transaction
SET TRANSACTION--Defining the current transaction data access characteristics
--Programmed SQL
DECLARE--Set cursors for queries
Explan--Describes a data access plan for a query
Open--Retrieving query results opens a cursor
FETCH--Retrieves a row of query results
Close--Closing cursors
PREPARE-Preparing SQL statements for dynamic execution
Execute-Execute SQL statements Dynamically
DESCRIBE--Describe 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 
12 @z=3 
IF @x > @y 
print ' x > y '- -Print 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.08when 
job_level = '2' then e_wage*1.07when 
job_level = '3' Then c12>1.06 
Else e_wage*1.05 
End

--while CONTINUE break 
declare @x int @y int @c int 
1 @y=1 
3 
begin 
Print @x--Prints the value 
of the variable x 3 
Begin 
*@x + @y print 
@c--Print variable C value 1 end 
 
1 
1 End 

--waitfor 
--An example waits 1 hours 2 minutes 3 seconds before executing a SELECT statement 
WAITFOR delay' a' 
select * FROM Employee 
--an example wait until 8 minutes after 11 o'clock to execute the SELECT statement 
waitfortime 'a ' 
select * FROM Employee

select***

SELECT * (column name) from table_name (table name) where column_name operator value 
ex: (host) 
select * from Stock_information where St Ockid = 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) 
---------use wildcard characters only in WHERE clauses that use the LIKE keyword) 
or stockpath = ' Stock_path ' 
1000not 
 
stock*** = ' mans ' 
 
Stocknumber in (a)order 
by stockid Desc (ASC)---------Sort, desc-descending , asc-ascending 
1,2 ---------by column number 
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 *from table1, table2 
where table1.id *= table2.id--------left outer joins, some in table1 and no null representation in table2 
= * Table2.id--------Right outer connection 

select StockName from table1 
Union [ALL]-----Union merge query result set, all-keep Duplicate row 
Select StockName from Table2

insert***

INSERT INTO table_name (stock_name,stock_number) value (' xxx ', ' xxxx ') 
value (select StockName, Stocknumber from Stoc K_TABLE2)---Value is a SELECT statement

update***

UPDATE table_name Set stockname = "xxx" [Where Stockid = 3] 
stockname = default 
StockName = null 
4

delete***

3 
Truncate table_name-----------Delete all rows in the table, still preserve the integrity of the Table 
drop TABLE table_name---------------completely delete the table

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

ALTER TABLE Database.owner.table_name add column_name char (2) null ... 
SP_HELP table_name----Display 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 Stockna Me_default----Delete a stockname default constraint

----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 
- -highest-paid employee name use 
Pangu 
select E_name from 
employee 
where e_wage = 
(select max (e_wage) 
From employee)

----Date function----

Day ()--function returns the date value in Date_expression_r 
MONTH ()--function returns the month value in Date_expression_r year 
()--function returns Date_expression_r The year value 
DATEADD (,,) 
--function returns the date specified with the specified extra date interval 
DATEDIFF (,,)-- 
function returns two specified dates in datepart Aspects of the Difference 
datename (,)--the function returns the specified part of the date as a string 
DATEPART (,)--the function returns the specified part of the date as an integer value 
GETDATE ()--function with datetime The default format returns the current date and time of the system

----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 (,)--the function returns the name of the specified field in the table, which is the column name Datalength ()--the actual length of the data that the function returns 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 ()--the function returns the name of the server-side computer IdentIT Y ([, 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 ISDATE ()--function to judge the given 
Expression is a reasonable date ISNULL (,)--the function replaces the null value in the expression with the specified value IsNumeric ()--function to determine whether the given expression is a reasonable numeric NEWID ()--the function returns a value of uniqueidentifier type The Nullif (,)--nullif function returns a EXPRESSION_R1 
 if the null value is not equal when EXPRESSION_R1 is equal to EXPRESSION_R2