原文:
T-Sql（一）简单语法
Sql Server是鄙人学习的第一种数据库，对Sql Server有一种特别的情感，下面就说一下Sql Server的简单语法，适用初学者。
1,创建数据库 create database
create
database
My_FrirstCreate
--
创建数据库
go
use
My_FrirstCreate
--
连接数据库
go
2,创建表 create table
create
table
dbo.Students
--
创建表（数据类型，是否NULL）
(StudentID
int
primary
key
not
null
, Name
varchar
(
25
)
not
null
, Scores
int
null
)
go
3,插入数据 insert
insert
dbo.Students(StudentID,Name,Scores)
--
插入数据
values
(
100204201
,
'
张三
'
,
50
)
go
insert
dbo.Students
values
(
100204202
,
'
李四
'
,
null
)
go
insert
into
table1
--
利用insert，select向表里插数据
select
ID,Name,Date
from
table2
where
Name
=
"张三";
go
4,使用select，into创建新表
select
{列名}
--
使用select，into创建新表
into
新表名
from
旧表；
5,更新,删除数据 update delete
update
dbo.Students
--
更新数据
set
Scores
=
70
where
StudentID
=
100204202
go
delete
from
Students
where
Name
=
'
张三
'
6,改变字段的属性
alter
table
Produce.Product
--
改变字段的属性
alter
column
Name
char
(
50
)
not
null
7,数据类型转换
print
cast
(
'
2011-12-12
'
as
datetime
)
--
cast类型转换
print
convert
(
datetime
,
getdate
())
--
convert类型转换
8,like查询语法
--
检索名称以‘hl’开头的信息
select
t.ProductKey,t.ModelName
from
dbo.DimProduct t
where
t.ModelName
like
'
hl%
'
;
--
检索名称以‘hl’结尾的信息
select
t.ProductKey,t.ModelName
from
dbo.DimProduct t
where
t.ModelName
like
'
%hl
'
;
--
检索名称类似‘hl’的信息
select
t.ProductKey,t.ModelName
from
dbo.DimProduct t
where
t.ModelName
like
'
%hl%
'
;
9,条件查询语法
--
每种颜色有多种件产品：
select
COUNT
(
*
)
from
dbo.DimProduct;
select
*
from
dbo.DimProduct
where
Color
=
'
black
'
;
select
count
(
*
)
from
dbo.DimProduct
where
Color
=
'
black
'
;
--
分组：
select
color
from
dbo.DimProduct;
select
color,
COUNT
(
*
)
from
dbo.DimProduct
group
by
Color;
--
商品库中：相同颜色产品数量大于50的商品颜色
select
color,
COUNT
(
*
)
from
dbo.DimProduct
group
by
Color
having
count
(
*
)
>=
50
;
select
*
from
dbo.DimProduct
order
by
Color
asc
;
select
color,
COUNT
(
*
)
from
dbo.DimProduct
group
by
Color
having
count
(
*
)
>=
50
order
by
COUNT
(
*
)
asc
;
select
color,
COUNT
(
*
)
from
dbo.DimProduct
group
by
Color
having
count
(
*
)
>=
50
order
by
COUNT
(
*
)
desc
;
--
商品库中：1998生产的，相同颜色产品数量大于5的商品颜色
select
color,
COUNT
(
*
)
from
dbo.DimProduct
where
YEAR
(StartDate)
=
1998
group
by
Color
having
count
(
*
)
>=
50
order
by
COUNT
(
*
)
desc
;
select
color,
count
(
*
)
from
dbo.DimProduct t
where
YEAR
(t.StartDate)
>
1998
group
by
color
having
COUNT
(
*
)
>
50
order
by
COUNT
(
*
)
desc
;
10,联接join语法
select
m.LoginID
as
ManagerLoginID,e.
*
--
左联接
from
HumanResources.Employee e
left
join
HumanResources.Employee m
on
m.employeeID
=
e.ManagerID
select
m.LoginID
as
ManagerLoginID,e.
*
--
右联接
from
HumanResources.Employee e
right
join
HumanResources.Employee m
on
m.employeeID
=
e.ManagerID
本文只是简单的介绍下T-Sql语法，复杂的语法将下面的文章讲解...
