SQL Sever Statements (cont. 2)

Date Time

Set the default value or binding in the Date Type field to GETDATE () and do not set the new field to automatically get the current server time

In the current time plus xx years xx month xx days

Select Datedd (Yy,100,getdate ()) plus 100 years at the current time

Select Datedd (Mm,5,getdate ()) plus 5 months at the current time

Select Datedd (Dd,34,getdate ()) plus 34 days at the current time

Federated Query: exports data from two tables by filtering out the information used

Select UI. Userid,ui. Username,ui. Qq,ui. Reaname,ss. Scoreid,ss. Chinese--Query content

From UserInfo UI left join Stuscore SS on-two table name alias

Ui. Useid=ss. Userid

Left join and right join are not enough to be based on the table on/off

Inner joins are available on both sides

more than three table ideas : First of the two tables to make a joint query, the result of a table name, in the third table to do a joint query

Constraints:

Control field within a certain range of values, to avoid dirty data;

Design "A field right-click Add constraint" modified expression such as age expression age>=0 and age<=120

Save after modification

Add

Way One:

INSERT into parenet values (7, ' dsffs ', ' Hang-up ')-Add field parenet table fields in order, primary key not empty with empty fields (but also must be added)

Way two:
Insert into Parent (userid,mmname) VALUES (8, ' Dsaf ')--such as adding only two fields Useri and mmname others not added is empty;

Modify:

Update:

Update Parent set mmname= ' ddd ', fathername= ' Fdgs ' where parentid=6

Delete:

Delete Parent where parentid=6

Copy data from one table to another table:

Insert into A (userid,ammname,afname) Select Useid, mmname,fathername from Parent

Index:

Clustered index: such as ID, coherent (a table with only one clustered index generally does not change the primary key)

Nonclustered index: incoherent, multiple ...

Indexing can speed up the query, but not all established, only the number of fields to query, otherwise it will produce a lot of index fragments, need to clean up regularly;

Stored procedures:

Stored procedures are written in the database, the benefits: high efficiency, high safety factor (prevent SQL injection)

1) basic Syntax

Eg:

Create proc Proccategoryselect

(

@name varchar (200),

@age int

)

As

SELECT * from Category where [name][email protected] and [email protected]

1) View results exec proccategoryselect ' xudads ', 11

2) Change / Delete stored procedure

ALTER proc

Drop proc

　

View

Trigger

Database Restore and Backup

Scheduled Jobs (Backup)

SQL Sever Statements (cont. 2)