MS SQL Server Database syntax

--Data manipulation

SELECT-Retrieve rows and columns of data from a database table

INSERT-Add new data rows to a database table

Delete-Deletes rows of data from a database table

Update--updating data in a database table

--Data definition

CREATE table--Creating a database table

DROP table--Deletes a table from the database

ALTER TABLE--modifying database table structure

Create View--Creates a view

Drop view-Removing views from the database

Create index--creates an index for a database table

Drop INDEX--deletes an index from the database

Create PROCEDURE--Creating 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--Adding a new schema to the database

Drop schema--Deletes a schema from the database

Create domain--creating a data domain

Alter domain--changing field definitions

Drop domain--deletes a field 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

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 stock*** = ' 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 *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----

----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)

----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 the value of years in Date_expression_r

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

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 a null value when EXPRESSION_R1 is equal to EXPRESSION_R2 and returns EXPRESSION_R1 if it is not equal