Organize the SQL statements you need to use in SQL Server during the period of your work

Some days before the SQL where to write to where, a mess, today tidied up a bit, to make a backup (although the blog has been eight months, but today is the first time to publish blog, so nervous AH ~ ~)

--2014.08.27 SQL statements

1: Enter database
use [Database name]
Eg:use [Dev]

2: CREATE TABLE
create table name (

)
eg:
create table AtblTest1 (
[id "int identity (1) primary key,--increase each time from 1 onwards
[key] varchar unique,
[unit1 "varchar ( ),

--message text,

--time datetime

--[count] int,
)

3: Manipulate existing tables/fields

--Modifying field data types
ALTER TABLE name ALTER COLUMN field type

--delete field constraint what error
ALTER table name DROP CONSTRAINT DEFAULT constraint name
GO
ALTER table table name DROP column field name
GO

--Get the default constraint name
Select Name
From sysobjects
where id= (select Cdefault
From syscolumns
Where name= ' field name ' and id = (SELECT ID
From sysobjects
WHERE name = ' table name '))

--Add Field
ALTER TABLE name Add field name fields type

--null characters are not allowed:
ALTER TABLE name Add new Field field type not NULL

--Allow null characters:
ALTER TABLE name Add new Field field type NULL

--Modify Fields
EXEC sp_rename ' table name. Original column name ', ' new column name ', ' column '

--Modify Table name
exec sp_rename ' old table name ', ' New table name '

eg
exec sp_rename ' atblTest1 ', ' atbltest '
exec sp_rename ' atbltest.unit1 ', ' Unit ', ' column '
ALTER TABLE atbltest ALTER COLUMN [Unit] varchar (200)
ALTER TABLE atbltransactions add isinsertaccounting bit
ALTER TABLE atblmembers add Accountuserid int
ALTER TABLE atblaccounts Add pendingbalance Money

4: Add Data
Insert into table name (column name 1, column name 2, column name 3) values (data 1, data 2, data 3)

Eg:insert into atbltest values (' Lobcheckfee ', 3, ' dollars ')

5: Change data
Update table name set column name = column name data where id= identity data

Eg:update atbltest Set [Unit] = ' dollar ' WHERE [id]=1

6: Create a trigger (it's incoherent to send an example)

create trigger Trigger name    --Create trigger name
on trigger table              --Create a trigger in this table
for Update                    --because which Events (INSERT, update,delete)
as             & nbsp                  --what happens after the event is triggered
if Update (This table field)          --If you modify the xx field
begin
SQL action thrown
end

Eg: (mainly if the number of Ablleads table Quantityonhand changes, the process of change will be stored in another table: 1-->0)
Create Trigger Trquantityonhand
On Atblleads
For Update
As
If Update (Quantityonhand)
Begin
declare @QuantityOnHandOld int, @QuantityOnHandNew int, @LeadID int;

Select @QuantityOnHandNew =quantityonhand, @LeadID =leadid from inserted;

Select @QuantityOnHandOld =quantityonhand from deleted;

if (@QuantityOnHandOld! = @QuantityOnHandNew)
Begin
INSERT into Atbltrigger ([Type],[time],[result],[leadid])
VALUES (' Quantityonhand ', GETDATE (), CONVERT (varchar, @QuantityOnHandOld) + '-a ' +convert (varchar,@ quantityonhandnew), @LeadID);
End
End

7: Delete Trigger

if (object_id (' Trigger name ') is not null)
Drop TRIGGER Trigger Name

if (object_id (' Trquantityonhand ') is not null)
Drop Trigger Trquantityonhand

8: Paging the table Data
select * From

select *, Row_number () Over
(

" Span style= "font-size:15px;" > a regular field name (ID)
) as rownum
from Table name
) DATA
where Data.rownum > Start Page * Number of columns per page and data.rownum< start Page number * column per page + number of columns per page

Eg: data from page sixth is shown in 600-610
SELECT * FROM
(
SELECT *, Row_number () over
(
ORDER BY
Leadid
) as RowNum
From Atblleads
) DATA
where Data.rownum>60*10 and data.rownum<60*10+10

9: Delete Table
DROP table Name

-----Drop the table if it exists------------------------------
if exists (select 1
From sysobjects
WHERE id = object_id (' Table name ')
and type = ' U ')
DROP table Name
Go

--eg:
if exists (select 1
From sysobjects
WHERE id = object_id (' [dbo].[ Db3_indexingdetails] ')
and type = ' U ')
drop table [dbo]. [Db3_indexingdetails]
Go

10: Control the number of SQL modifications

eg
BEGIN Tran
Update atblorders Set orderstatus = 2 where Ebayorderid = ' 131235536269-0 '
if (@ @ROWCOUNT >1) [email protected] @ROWCOUNT is the number of rows affected
Begin rollback Tran
print ' SQL exceeds the specified number of affected rows and will not execute the statement '
End
else if (@ @ROWCOUNT =0)
Begin
print ' Failed to execute '
End
Else
Begin COMMIT Tran
print ' Congratulations on your completion '
End

11: Get the number of rows
Select COUNT (*) from your table name

12: Get column names and detailed data
SELECT * from syscolumns where id = object_id (' Table name ')

Eg:select * from syscolumns where id = object_id (' atblusers ')

13: Get the number of column names
Select COUNT (*) from syscolumns where id=object_id (' Your table name ')

14: Read all table names in the library
Select name from sysobjects where xtype= ' u '

15: Read all column names for the specified table
Select name from syscolumns where id= (select Max (id) from sysobjects where xtype= ' u ' and name= ' table name ')

16: Get field type
Select T.name from sysobjects o,syscolumns c,systypes t
where O.id=c.id and C.usertype=t.usertype and o.name= ' table name ' and c.name= ' column name '

17:select statement to distinguish the case of query data

--case insensitive (not differentiated by default)
Select Top Ten * from atblusers where [password]= ' sunflower134 ' COLLATE chinese_prc_ci_as

eg:--case-sensitive for a field
Select Top Ten * from atblusers where [password]= ' sunflower134 ' COLLATE chinese_prc_cs_as and Status=1

18: Data Query

eg
Select Transactionid,accountid,transactamount,transactdate,transacttype,
Transactamount = 0 Then 0 when transactamount > 0 then Transactamount END,--(Added column display field)
Debit = Transactamount = 0 Then 0 when transactamount < 0 then-transactamount END
from [atblaccounting]
WHERE [AccountID] = the ORDER by transactdate Desc, Transacttype desc

Select Sum (transactamount) from atblaccounting where accountid=54

Select TOP $ * from atblaccounting ORDER by TransactionID DESC

Select B.isinsertaccounting, * from Atblaccounting a
INNER JOIN Atbltransactions B on a.ebayorderid=b.ebayorderid and a.itemid = B.itemid

Select COUNT (*) from Atbluserebayauthtoken

19: Delete all data in the table
DELETE from table name

20: Query SQL Server for detailed version information
SELECT @ @VERSION

The big night, most of these SQL has been written, so the example of SQL has not been tested again.

Organize the SQL statements you need to use in SQL Server during the period of your work