SQL Server database syntax

-- Data Operations
SELECT -- retrieve data rows and columns from database tables
INSERT -- add new data rows to the database table
DELETE -- DELETE data rows from a database table
UPDATE -- UPDATE data in the database table
-- Data Definition
Create table -- CREATE a database TABLE
Drop table -- delete a TABLE from a 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 the database table
Drop index -- delete an INDEX from a database
Create procedure -- CREATE a stored PROCEDURE
Drop procedure -- delete a stored PROCEDURE from a database
Create trigger -- CREATE a TRIGGER
Drop trigger -- delete a TRIGGER from a database
Create schema -- Add a new SCHEMA to the database
Drop schema -- delete a SCHEMA from the database
Create domain -- CREATE a Data Value DOMAIN
Alter domain -- change DOMAIN definition
Drop domain -- delete a DOMAIN from the database

-- Data Control
GRANT -- GRANT the user access permission
DENY-DENY User Access
REVOKE -- REVOKE User Access Permissions
-- Transaction control
COMMIT -- end the current transaction
ROLLBACK -- abort the current transaction
Set transaction -- define the data access features of the current TRANSACTION
-- Programmatic SQL
DECLARE -- set the cursor for the query
Explain -- describe the data access plan for query
OPEN -- OPEN a cursor for retrieving query results
FETCH -- retrieve a row of query results
CLOSE -- CLOSE the cursor
PREPARE -- prepare SQL statements for dynamic execution
EXECUTE -- dynamically execute SQL statements
DESCRIBE -- DESCRIBE the prepared Query
--- Local variables
Declare @ id char (10)
-- Set @ id = '20140901'
Select @ id = '000000'

--- Global variables
--- It must start @

-- If else declare @ x int @ y int @ z int select @ x =1@ Y =2@ Z =3If @ x> @ y print 'x> y' -- print 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 =1While @ x <3Begin print @ x -- print the value of variable x while @ y <3Begin select @ c =100* @ X + @ y print @ c -- print the value of variable c select @ y = @ y +1End select @ x = @ x +1Select @ y =1End

-- WAITFOR -- for example, the SELECT statement waitfor delay is executed after 1 hour, 2 minutes, and 3 seconds'01:02:03'Select * from employee -- for example, the select statement waitfor time' is executed only after AM'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] % '--------- ([] specifies the value range) stockname like '[^ F-M] %' --------- (^ exclude specified range) --------- wildcards can only be used in the where clause that uses the like keyword) or stockpath = 'stock _ path' or stocknumber <1000And stockindex =24Not stock *** = 'man' stocknumber20And100Stocknumber in (10,20,30) Order by stockid desc (asc) --------- sort, desc-descending, asc-ascending order1,2--------- By column number stockname = (select stockname from stock_information where stockid =4)

--------- Subquery
--------- Unless the inner select clause can only return the value of one row,
--------- Otherwise, an in qualifier should be used in the outer where clause.

Select * from table1, table2 where table1.id * = table2.id -------- left external connection, table1 and table2 cannot use null to indicate table1.id = * table2.id -------- right external connection select stockname from table1 union [all] ----- union merge query result set, all-retain 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 =3Truncate table_name ----------- delete all rows in the table and keep table integrity. drop table table_name ------------- Delete table completely

* ** Alter table ** --- modify the database table structure

Alter table database. owner. table_name add column_name char (2) Null... sp_help table_name ---- displays the existing features of the table create table table_name (name char (20), Age smallint, lname varchar (30) Insert into table_name select ...... ----- implement the method of deleting a column (create a new table) alter table table_name drop constraint Stockname_default ---- Delete the default constraint of Stockname

---- Statistical functions ----

AVG -- average value
COUNT -- COUNT
MAX -- calculate the maximum value
MIN -- Minimum value
SUM -- SUM

-- AVG use pangu select avg (e_wage) as dept_avgWage from employee group by dept_id -- MAX -- calculate 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)

---- Date function ----

DAY () -- The function returns the date value MONTH () in date_expression_r -- the function returns the MONTH value YEAR () in date_expression_r -- the function returns the YEAR value DATEADD (,) in date_expression_r (,,) -- The function returns the new date DATEDIFF (,) generated by the specified date plus the specified additional date interval number. -- the function returns the DATENAME (,) Difference between the two specified dates in datepart (,) -- function returns the specified DATEPART (,) of a date in the form of a string -- function returns the specified part of the date in the form of an integer 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 executed application COALESCE () -- function returns the value of COL_LENGTH (<'table _ name'>, <'column _ name'>) -- The Length Value of the specified field in the function return table COL_NAME (,) -- the name of the specified field in the function return table, that is, the column name DATALENGTH () -- function returns the actual data length of the data expression DB_ID (['database _ name']) -- function returns the database ID DB_NAME (database_id) -- function returns the database name HOST_ID () -- function returns the server-side computer name HOST_NAME () -- function returns the server-side computer name IDENTITY ([, seed increment]) [AS column_name]) -- IDENTITY () the function is only used in the select into statement to insert an identity column to the ISDATE () in the new table-the function determines whether the given expression is a reasonable date ISNULL (,) -- The function replaces the NULL value in the expression with the specified value with ISNUMERIC () -- The function determines whether the given expression is a reasonable value NEWID () -- function returns a UNIQUEIDENTIFIER type value NULLIF (,) -- NULLIF function returns NULL if expression_r1 and expression_r2 are equal. Otherwise, expression_r1 is returned.