2015-10-20 SQL2

SQL SERVER (ii)

Three. Insert, UPDATE, delete, add

3.1insert Insertion

INSERT into student (sno,sname,sage)

VALUES (' 1001 ' Zhang San ', ' 28 ')

INSERT INTO student

VALUES (' 1001 ' Zhang San ', ' 28 ', ' Women ')

3.2update modification

Update student set Sno=30,sname= ' John Doe ' where sage= ' 25 '--Modify the student table to let sage=25 sno change to 30,sname modified to John Doe

3.3delete Delete

Delete student where sno= ' 1002 '--delete data with a number equal to 1002

Drop table users--Delete tables users

SLETCT * from     (select Sno,row_number () up (order by Sno) as row from student) Studentwhere row between 6 and 10

3.4alter add

ALTER TABLE student

Add

Ssex int NULL

3.5 Complex queries

Select Sno,sname,avg (Sorce)

From student

GROUP BY Sno,sname

Having Count (*) >=4//Query The SQL statement of the student number, name and average score of at least four courses

Select distinct [name] from student//isolated name in student

Select COUNT (distinct name) from student//To isolate the number of name not duplicated in student

Four. Constraints

The difference between 4.1.UNIQUE constraints and PK:

A table can have only one PK, but there may be multiple unique constraints

The PK constraint defines that the primary key cannot be null, but unique can be a null value

Method: Click "Manage Indexes and Keys"---"Add"---"type" select "Unique key"---"column" Add multiple unique--close save

Role: When the multiple unique at the same time, will be error, the realization of the integrity of the entity.

4.2check constraints

Method: Design---Column name right----Manage CHECK constraints---Add---general expression---write expressions for the key values you want to constrain such as: Laborage>0 and laborage<=100

---"Close" save

Role: Constrain a key value, achieve domain integrity, data integrity

SQL statements:

creat table T1

(

Wage Money NOT NULL check (payroll between and 4000)

)

Five. Get the current time

Select year (GETDATE ())------//Get current years

Select Month (getdate ())------//Get the current month

Select Day (getdate ())---------//Get current days

Select year (birthday) from student//get birthday from the student table

SELECT * from employ where month (birthday) =8//Print out all information about employees who have birthdays in August

SELECT * from employ where year (GETDATE ())-year (birthday) >25//years (GETDATE ()) for current years, print out all information for employees older than 25

Select DATEADD (Yy,100,getdate ())//the day plus 100 years time, GETDATE () can also be replaced by a specific day, such as written: ' 2108/12/31 '

Select DATEADD (Mm,1,getdate ())//the day plus 1 month time

Select DATEADD (Dd,100,getdate ())//day plus 100 days

Select DateDiff (Yy,getdate (), ' 2108/12/31 ')//How many teenagers are there from 2108/12/31

Select DateDiff (Mm,getdate (), ' 2108/12/31 ')//How many months is the day from 2108/12/31

Six. IsNull

Select Sno,sage,ssex,isnull (Smame, ')//null sname shown in Kong

Select Title,content,isnull (categoryid,0) from news//null CategoryID shown with 0

Seven. Case Line judgment

Select Sno,sname,case

When (sno=1) then ' first '

When (sno=2) then ' second '

End as Biecheng

From student

SQL SERVER (iii)

I. Index

The index is divided into: Clustered index--------Each table can have only one clustered index typically a primary key

Nonclustered indexes-------Each table can have more than one nonclustered index

Note: By default, SQL Server defaults to a clustered index for the primary key, which can result in wasted resources.

CREATE INDEX: In the Indexes/Keys dialog box, click Add.

Select a new index from the selected primary/Unique key or index list.

Select Create as clustered in the grid, and select Yes from the drop-down list to the right of the property. Indexes are created in the database when the table is saved.

Two Stored Procedures

Pros: Compile at creation time, each time you execute a stored procedure that does not need to be compiled, and a generic SQL statement is compiled once per execution

So some of the complex logic's SQL recommendations are written in the stored procedure and some of the SQL recommendations that are often called are written in the stored procedure.

High security, can be set only some users have the right to use the specified stored procedure

SQL statements:

CREATE proc Storage Name

(

@name varchar (200),

@age int

)

As

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

View Result: Execute EXEC Store name ' Xudads ', 11

Change/delete stored procedure: Alter proc

Drop proc

Three. Triggers

A trigger is a special kind of stored procedure, which is the same as an event when the data is added, deleted, and changed at the same time.

Create a new trigger:

Create TRIGGER Trigger Name

On table name

After delete/* There are three kinds of insert,delete,update*/

As

Begin

SELECT * FROM deleted/* Delete the contents after deletion * *

End

GO

2015-10-20 SQL2