Transact-SQL 常用语句（1）

1. 创建数据库

Create database test

 

2. 删除数据库

Drop database test

 

3. 创建数据表

Use test
Go
Create table Table1 (ID int, Name nvarchar(10))

 

4. 删除数据表

Drop table Table1

 

5. 删除数据表里的内容，而非删除整张表（包括列名）

Delete table_name

 

6. 往已存在的表里添加列

Alter table Table1
Add Subject int

 

7. 更改已有列的属性

ALTER TABLE table_name
ALTER COLUMN column_name
[type_name] [NULL | NOT NULL] [COLLATE collation_name]

Alter table Table1
Alter column Subject varchar(20) null

COLLATE collation_name

The column collation (for character-based data types) tomodify.
Collations define three settings: a code page used to store non-
Unicode character data types, the sort order for non-Unicode
character data types, and the sort order for Unicode data types.
Collations are reviewed later on in the chapter in the section
“Collation Basics.”

insert into yct1 select 2, 'yct'||rownum from dual connect by rownum < 6;

 

8. 删除已有列
Alter table table_name
Drop column column_name

 

9. 增加标识列
Alter table table_name
Add column_name int identity(1,1)    /* 标识从1 开始，每次增加1.  Identity(2,3) 即为从2开始，每次增加3

use test
Go
create table test (ID int identity(1,1), Name nvarchar(10))

insert into dbo.test ([Name] )
values ('shift1’)

/* 下面四行运行几遍就多几行记录

declare @a nvarchar(4)
set @a=@@IDENTITY+1
insert into dbo.test ([Name] )
values ('shift'+@a)

运行结果：
ID  Name
1 shift1
2 shift2
3 shift3
4 shift4

10. 关于datetime类型与 datetime2(number)类型

Datetime:   2010-05-08 15:50:32.717
Datetime2(0) : 2010-05-08 15:50:32
Datetime2(4): 2010-05-08 15:50:32.3130   /*小数点后四位

11. 强制类型转换
Cast (要转换的) as type

 

12. Top 与 Order by
 
Select Top 100 * from dbo.dimension001
Order by DT desc
13. Group By 与 Group By All

SELECT OrderDate,
SUM(TotalDue) TotalDueByOrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'
GROUP BY OrderDate

SELECT OrderDate,
SUM(TotalDue) TotalDueByOrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'
GROUP BY ALL OrderDate

By adding the ALL keyword after GROUP BY, all row values are used in the grouping, even if they were not qualified to appear via the WHERE clause.

14. Group By … Having…

SELECT s.Name,
COUNT(w.WorkOrderID) Cnt
FROM Production.ScrapReason s
INNER JOIN Production.WorkOrder w ON
s.ScrapReasonID = w.ScrapReasonID
GROUP BY s.Name
HAVING COUNT(*)>50

 

15. Distinct： 在Select或AggregateCalculation时，去除重复值

SELECT DISTINCT HireDate
FROM HumanResources.Employee

SELECT AVG(DISTINCT ListPrice)  /* This returns the unique set of price points first, and then averages them (although
the difference doesn’t end up being that large):
FROM Production.Product

 

 

16. Using column Aliases:  AS ‘别名’或者是   Column_Name后直接跟‘别名’

SELECT Color AS 'Grouped Color',
AVG(DISTINCT ListPrice) AS 'Average Distinct List Price',
AVG(ListPrice) 'Average List Price'
FROM Production.Product
GROUP BY Color

 

 

17. Using select to create a script

SELECT column_name + ' IS NULL AND '       /* a column in the INFORMATION_SCHEMA.columns view.
FROM INFORMATION_SCHEMA.columns      /* a system view
WHERE table_name = 'Employee'                   /* a column in the system view
ORDER BY ORDINAL_POSITION 

 

 

18. Convert ：格式转换

Convert ( type, Variable_Name/Column_Name)

 

19. Creating a Comma-Delimited List Using SELECT

ID Name
2 Shift2
3 Shift3
4 Shift4
5 Shift5
6 Shift6
7 Shift7

Use Test
Go

Declare @a varchar(100)
Set @a = ‘’

Select @a = @a+ [Name] + ’,’
From dbo.test1

Select @a

Return:
Shift2,Shift3,Shift4,Shift5,Shift6,Shift7,

20. INTO

Select column_name, …..
Into newtable_name
From table_name        /* 如果没有下面的Where语句，则<newtable_name>被创建且里面内容为Select后的内容
Where 1=2                 /* 包括此Where语句，则新创建的表里没有内容，只有空表被创建