SQL Server statement query manual

Table creation:

Create Table [dB. DBO]. tablename

(Stud_id int constraint constraintname1 not null primary key,

Name nvarchar (5) not null,

Birthday datetime,

Gender nchar (1 ),

Telcode char (12 ),

Zipcode char (6) constraint constraintname2 check (zipcode like [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] ]),

Deptcode tinyint constraint constraintname3 check (deptcode <100 ),

Salary money default 260

)

 

Delete table:
Drop table tablename;

Modify Table:

Alter table [dB. DBO.] tablename

Add column_name nvarchar (20) not null default 'liaihua ';

Alter table [dB. DBO.] tablename

Drop column column_name;

Alter table [dB. DBO.] tablename

Modify column_name columntype;

 

Insert data:

Insert into tablename [(column1, column2, column3…)] Values (value1, value2, value3 ....);

Insert into tablename [(column1, column2, column3…)] Select column1, column2, column3... From tablename2 [where…]

 

Update Data:

Update tablename set column1 = value1, column2 = value2, column3 = value3 ..... Where...

 

Delete data:

Delete from tablename where ......

 

Query data:

Select [All | distinct] column1, column2, column3 ..... From tablename1 [, Table2, table3. ..]

[Where…]

[Group by column_name]

[Having...]

[Order by column_name [DESC | ASC]

 

Common set functions:

Sum (); AVG (); min (); max (); count ();

 

SQL Server supports five constraints: Default constraint (default constraint), primary key constraint (primary key constraint), check constraint (check constraint), foreign key constraint (foreign key constraint), and unique constraint;

 

Constraint creation:

Create Table tablename

(Column1 type (null | not null)

[[Constraint constraintname] ----- if the constraint name is not written, the system automatically adds

{

Primary Key [clustered | nonclustered]

| Unique [clustered | nonclustered]

| [Foreign key] References ref_table [(ref_column)]

| Default constraintexpression

}

] [, Column2…]

)

 

Delete constraints:

Alter table tablename

Drop constraint constraintname;

 

Create View:

Create view [dB. DBO.] viewname

[(Column1, column2, column3...)]

As

Selectstatement ----- SQL query statement

[With check option]

 

Modify View:

Alter view [dB. DBO.] viewname

[Column1, column2, column3...]

As

Selectstatement ----- SQL query statement

[With check option]

 

Modify data using a view:

Update viewname set column1 = value1, column2 = value2...

Insert into viewname (column1, column2, column3 ...) Values (value1, value2, value3 .....);

Delete from viewname where...

 

Create indexes: (Multiple non-clustered indexes can be created, and only one clustered index can be created)

Create [unique] [clustered | nonclustered] index indexname

On [[database.]. Owner.] {tablename | viewname} (column1, (column2, column3 ...))

[

[Fillfactor = fillfactor]

[[,] Ingore_dup_key]

[[,] Drop_existing]

[[,] Statistics_norecompute]

[[,] Sort_in_tempdb]

]

[On filegroup]

 

Create unique clustered index myindex on mytable (mycolumn) on primary;

 

Delete An index:

Drop index [owner.] tablename. indexname [, [owner.] tablename. indexname...]

 

Add a foreign key:

Alter table tablename add constraint fk_cust_id foreign key (cust_id) References tablename2 (cust_id) Where fk_cust_id is the name of the foreign key constraint

 

Create a stored procedure:

Create proc [edure] [owner.] procedurename

[{@ Parametername parametertype}

[= Defaultvalue] [Output]

]

[

{Recompile | encryption | recompile, encryption}]

[For replication]

As

SQL _statement

 

Instance:

Create proc DBO. myprocedure

@ Startdate datetime, @ enddate datetime, @ country varchar (20) = 'China'

As

If (startdate is null or enddate is null or country is null)

Begin

Raiserror ('null value are valid', 5, 5)

Return

End

Select * From DBO. mytable

Where orderdate between @ startdate and @ enddate

And shipcountry = @ country

 

 

Delete stored procedure:

Drop proc [owner.] procedurename [,.... N]

 

Create a trigger:

Create trigger [owner.] triggername

On [owner.] Table | View

{For | after | instead of} {[insert] [, delete] [, update]}

[With encryption]

As

If Update (column_name) [{and | or} Update (column)…]

SQL _statement

 

Instance:

Create trigger mytrigger

On mytable

For update

As

If Update (column1)

Begin

Raiserror ('unanthorized', 10, 1)

Rollback transaction

End

 

Create trigger mytrigger

On mytable

For Delete

As

Delete from mytable2 inner join deleted on mytable2.bookid = deleted. bookid

 

 

This article: SQL Server statement query manual

Last one: Kidnapping Baidu Google Spider to quickly update snapshot next: Get the ID of the last inserted record in SQL Server 2000