Ms 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 = 3

If @ 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 = 1

While @ x <3

Begin

Print @ x -- print the value of variable x

While @ y <3

Begin

Select @ c = 100 * @ x + @ y

Print @ c -- print the value of variable c

Select @ y = @ y + 1

End

Select @ x = @ x + 1

Select @ y = 1

End

-- WAITFOR

-- For example, the SELECT statement is executed after 1 hour, 2 minutes, and 3 seconds.

Waitfor delay '01: 02: 03'

Select * from employee

-- For example, the SELECT statement will not be executed until PM.

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] %' --------- ([] specifies the range of values)

Stockname like '[^ F-M] %' --------- (^ exclude specified range)

--------- Only wildcard characters can be used in the where clause that uses the like keyword)

Or stockpath = 'stock _ Path'

Or stocknumber: <1000

And stockindex = 24

Not stock *** = '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 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, which exists in table1 but not in table2.

Table1.id = * table2.id -------- right external connection

Select stockname from table1

Union [all] ----- union merges query result sets, all-retained 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 maintain table integrity

Drop table table_name --------------- delete a 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 ---- display existing features of a table

Create table table_name (name char (20), age smallint, lname varchar (30 ))

Insert into table_name select ...... ----- to delete 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

-- 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 in date_expression_r.

MONTH () -- The function returns the MONTH value in date_expression_r.

YEAR () -- returns the YEAR Value in date_expression_r.

DATEADD (,,)

-- The function returns the new date generated by the specified date plus the specified additional date interval number.

DATEDIFF (,,)

-- The function returns the datepart differences between two specified dates.

DATENAME (,) -- The function returns the specified part of the date in the form of a string

DATEPART (,) -- The 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 the first non-NULL expression among multiple expressions.

COL_LENGTH (<'table _ name' >,< 'column _ name'>) -- returns the length value of the specified field in the table.

COL_NAME (,) -- Name of the specified field in the function return table, that is, the column name.

DATALENGTH () -- the actual length of the data returned by the function

DB_ID (['database _ name']) -- number of the database returned by the function

DB_NAME (database_id) -- Name of the database returned by the function

HOST_ID () -- The function returns the name of the server computer.

HOST_NAME () -- returns the name of the server computer.

IDENTITY ([, seed increment]) [AS column_name])

-- The IDENTITY () function is used only in the select into statement to insert an identity column to the new table.

ISDATE () -- determines whether the given expression is a reasonable date.

ISNULL (,) -- The function replaces the NULL value in the expression with the specified value.

ISNUMERIC () -- The function determines whether the given expression is a reasonable value.

NEWID () -- The function returns a value of the UNIQUEIDENTIFIER type.

NULLIF (,)

-- If the NULLIF function is equal to expression_r1 and expression_r2, NULL is returned. If not, expression_r1 is returned.