T-Sql（一）简单语法-白红宇

　Sql Server是鄙人学习的第一种数据库，对Sql Server有一种特别的情感，下面就说一下Sql Server的简单语法，适用初学者。

　　1,创建数据库create database

create database My_FrirstCreate           --创建数据库gouse 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)goinsert dbo.Students   values(100204202,'李四',null)goinsert into table1              --利用insert，select向表里插数据      select ID,Name,Date from table2where Name="张三";go

　　4,使用select，into创建新表

select{列名}       --使用select，into创建新表into 新表名from 旧表；

　　5,更新,删除数据update delete

update dbo.Students         --更新数据   set Scores=70   where StudentID=100204202godelete 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.ModelNamefrom dbo.DimProduct twhere t.ModelName like 'hl%';--检索名称以‘hl’结尾的信息select t.ProductKey,t.ModelNamefrom dbo.DimProduct twhere t.ModelName like '%hl';--检索名称类似‘hl’的信息select t.ProductKey,t.ModelNamefrom dbo.DimProduct twhere 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.DimProductgroup by Color;--商品库中：相同颜色产品数量大于50的商品颜色select color,COUNT(*) from dbo.DimProductgroup by Colorhaving count(*) >= 50;select * from dbo.DimProductorder by Color asc;select color,COUNT(*) from dbo.DimProductgroup by Colorhaving count(*) >= 50order by COUNT(*) asc;select color,COUNT(*) from dbo.DimProductgroup by Colorhaving count(*) >= 50order by COUNT(*) desc;--商品库中：1998生产的，相同颜色产品数量大于5的商品颜色select color,COUNT(*) from dbo.DimProductwhere YEAR(StartDate)=1998group by Colorhaving count(*) >= 50order by COUNT(*) desc;select color,count(*) from dbo.DimProduct twhere YEAR(t.StartDate)>1998group by colorhaving COUNT(*)>50order by COUNT(*) desc;

　　10,联接join语法

select m.LoginID as ManagerLoginID,e.*       --左联接from HumanResources.Employee eleft join HumanResources.Employee mon m.employeeID = e.ManagerIDselect m.LoginID as ManagerLoginID,e.*       --右联接from HumanResources.Employee eright join HumanResources.Employee mon m.employeeID = e.ManagerID

　　本文只是简单的介绍下T-Sql语法，复杂的语法将下面的文章讲解...

本文转自田园里的蟋蟀博客园博客，原文链接：http://www.cnblogs.com/xishuai/archive/2013/05/06/3062802.html，如需转载请自行联系原作者